UPDATE: The IndigoSqlClient has been replaced in Indigo 5.1.2 by the SQL Logger Plugin, which provides additional functionality and several improvements. This forum thread has been locked, so please post any questions about the SQL Logger Plugin in a new thread.
Note that the old IndigoSqlClient client will be removed by the installer, and the old database format (table names and rows defined in the tables) is not compatible with the new plugin. If you are using the old IndigoSqlClient then please read over the documentation on the new plugin so you can make adjustments to your scripts/SQL to be compatible with the new plugin.
PREVIOUS CONFIGURATION DETAILS ON DEPRECATED SQL CLIENT:
The IndigoSqlClient can be used to efficiently save Indigo Device state changes, Variable value changes, and Event Log messages to either a SQLite database or a PostgreSQL database. This allows Indigo to integrate with other applications or systems, and allows for historical data tracking. Although this is a powerful and requested feature, it isn't for the non-technical. If you don't know what a PostgreSQL database is, then you should read some online tutorials before deciding if you need this functionality.
Configuring and using SQLite is easier than PostgreSQL, so if you do not have a specific need for PostreSQL, then you might want to read about how to use SQLite and Indigo.
System Requirements
- Mac OS X 10.4.11 or greater
- PostgreSQL 8.3.x or greater
- Indigo Server 2.5 or greater
I. Install PostgreSQL. PostgreSQLForMac.com is a good place to download a binary installer so you don't have to compile the project yourself. I downloaded the unified installer then ran the Server package, which includes both the server and some useful client/admin tools. I also recommend installing pgAdmin3, which is a very good database admin tool that runs on OS X (and several other platforms).
For installation of PostgreSQL, I followed the general instructions on Russ Brook's site. But they are a bit old and there were some steps I believe you can skip and other steps that are missing. Here is what I did (your mileage may vary -- I'm not a PostgreSQL expert!):
. . 1. After running the Server package installer, I added the path to its bin folder to my .bash_profile file:
- Code: Select all
echo 'export PATH=$PATH:/Library/PostgreSQL8/bin' >> ~/.bash_profile
. . 2. Next we need to set the default character encoding to UTF-8. Indigo requires that your database use UTF-8 encoding, so if you skip this it may not work. You may not have to do this on Leopard, but it won't hurt to do it regardless. First we delete the database:
- Code: Select all
sudo rm -R /Library/PostgreSQL8/data
And then recreate it using UTF-8:
- Code: Select all
sudo mkdir /Library/PostgreSQL8/data
sudo chown postgres:postgres /Library/PostgreSQL8/data
sudo -u postgres initdb -E utf8 --locale=en_US.UTF-8 /Library/PostgreSQL8/data
Now all new databases will use UTF-8. Lastly, I had problems with the log folder and file not having the correct owner which prevented the postgres server from starting. The owner should be postgres, which is the user automatically created by the PostgreSQL installer. I fixed it by doing:
- Code: Select all
sudo chown postgres:postgres /Library/PostgreSQL8/log
sudo chown postgres:postgres /Library/PostgreSQL8/log/PostgreSQL8.log
. . 3. You should now be able to start the server with the Service Manager application installed in your /Applications/PostgreSQL/ folder. You can see if your server is up and running by accessing it via the command line psql tool:
- Code: Select all
psql -U postgres
Note that once the IndigoSqlClient starts and connects to the PostgreSQL database you will not be able to stop or restart PostgreSQL via the Service Manager application. You must first stop the Indigo Server, which will stop the IndigoSqlClient process.
II. Open configuration file to set your PostgreSQL connection settings:
- Code: Select all
/Library/Application Support/Perceptive Automation/Indigo 2/IndigoSqlClient/IndigoSqlClient.conf
Change the database_type specified in the file to postgres. Presuming you installed the PostgreSQL server on the same Mac as your Indigo Server, leave the host as 127.0.0.1. The default username after running the installer above will be postgres and the password will be blank. You can leave the database name as-is, or give it the name of a database you have already created.
IndigoSqlClient, when it is started, will automatically create the database if it doesn't exist, and will automatically create the database tables.
III. Turn on the <em>Log Device state history to SQLite or PostgreSQL</em> checkbox inside the Start Local Server... dialog (available via the Indigo 2->Start Local Server... menu item).
Usage and Testing
When Indigo Server starts, it will now launch the IndigoSqlClient and begin logging Device state changes, Variable value changes, and optionally Event log messages. Check the Event Log window for any error messages from the IndigoSqlClient process.
Note the client does not attempt to purge old data from the database at any point, so you'll want to manually delete old entries occasionally.