IndigoSqlClient -- logging changes to SQLite or PostgreSQL

Posted on
Mon Apr 14, 2008 10:59 am
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

IndigoSqlClient -- logging changes to SQLite or PostgreSQL

IndigoSqlClient

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 [b]not compatible[b] 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 DETAILS ON DEPRECATED INDIGOSQLCLIENT:

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. IndigoSqlClient is a seperate process that Indigo Server can automatically launch (and terminate) that hooks into all device state and variable value change messages from the Indigo Server. This allows Indigo to integrate with other applications or systems, and allows for historical data tracking. You can, for example, use PHP to dynamically generate graphs or charts of device states (like temperature) stored in a PostgreSQL database.

By default, OS X 10.5 and higher includes the libraries needed to use SQLite, which makes configuring SQLite simple.

Although more complicated, we have also put together basic instructions for users needing to use PostgreSQL.

Posted on
Wed May 21, 2008 9:36 pm
Stephan offline
Posts: 7
Joined: Aug 05, 2007

Re: IndigoSqlClient -- logging changes to SQLite or PostgreS

support wrote:
By default, OS X 10.5 and higher includes the libraries needed to use SQLite, which makes configuring SQLite simple.

Matt,
what about Mac OS 10.4? How can I activate SQLite for Mac OS 10.4?

Posted on
Thu May 22, 2008 1:53 pm
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: IndigoSqlClient -- logging changes to SQLite or PostgreS

You will have to install python version 2.5 or greater for SQLite support on OS X 10.4. I've never done that, so I don't have step-by-step instructions. You might just want to try PostgreSQL if you are running 10.4. I have gotten that working before.

Regards,
Matt

Posted on
Fri Jan 21, 2011 4:00 pm
hwalters offline
Posts: 10
Joined: Sep 01, 2005
Location: Cottage Grove, Oregon

Re: IndigoSqlClient -- logging changes to SQLite or PostgreS

Has anyone succeeded in getting IndigoSqlClient to work under OSX 10.4?

I have installed Python 2.7.1 which includes SQLite3, but get the following error recorded in the log:

Jan 21, 2011 10:59:06 AM
Starting Indigo Server version 4.1.15
Loading attachments
.
.
SQLite client authenticated
SQLite client connected to Indigo Server
SQLite exception trying to load python sql module: No module named sqlite3
SQLite see IndigoSqlClient ReadMe.html for installation instructions
SQLite client disconnected

Posted on
Fri Jan 21, 2011 4:48 pm
jay (support) offline
Site Admin
User avatar
Posts: 18200
Joined: Mar 19, 2008
Location: Austin, Texas

Re: IndigoSqlClient -- logging changes to SQLite or PostgreS

The default python (/usr/bin/python) is probably still getting started. Try setting the $PATH variable in your .profile to point to the new python command. (you'll probably have to logout/back in for it to take effect)

Jay (Indigo Support)
Twitter | Facebook | LinkedIn

Posted on
Fri Jan 21, 2011 7:17 pm
hwalters offline
Posts: 10
Joined: Sep 01, 2005
Location: Cottage Grove, Oregon

Re: IndigoSqlClient -- logging changes to SQLite or PostgreS

Jay,
Here is my .bash_profile:

# Setting PATH for Python 2.7
# The orginal version is saved in .bash_profile.pysave
PATH="/Library/Frameworks/Python.framework/Versions/2.7/bin:${PATH}"
export PATH

It was installed yesterday and the computer restarted twice since then.
When I start python from my home directory, this is what I see:

Hanks-Computer:~ henrywalters$ python
Python 2.7.1 (r271:86882M, Nov 30 2010, 09:39:13)
[GCC 4.0.1 (Apple Inc. build 5494)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>>help()

The sqlite3 module(s) seem to be present:

help> modules sqlite3
Here is a list of matching modules. Enter any module name to get more help.
sqlite3
sqlite3.dbapi2
sqlite3.dump
sqlite3.test
sqlite3.test.dbapi
sqlite3.test.dump
sqlite3.test.factory
sqlite3.test.hooks
sqlite3.test.py25tests
sqlite3.test.regression
sqlite3.test.transactions
sqlite3.test.types
sqlite3.test.userfunctions
_sqlite3

help>

Thanks,
Hank

Posted on
Mon Jan 24, 2011 3:41 pm
jay (support) offline
Site Admin
User avatar
Posts: 18200
Joined: Mar 19, 2008
Location: Austin, Texas

Re: IndigoSqlClient -- logging changes to SQLite or PostgreS

So, apparently IndigoServer is running before the user's default shell is run (thus setting the new path). The only other thing I can think of is to link /usr/bin/python to the one at /Library/Frameworks/Python.framework/Versions/2.7/bin/ (be sure and keep the old /usr/bin/python in case linking doesn't work out). The only trick, of course, is if something else on your system requires the older version of python it might break. YMMV.

Jay (Indigo Support)
Twitter | Facebook | LinkedIn

Posted on
Mon Jan 24, 2011 7:04 pm
hwalters offline
Posts: 10
Joined: Sep 01, 2005
Location: Cottage Grove, Oregon

Re: IndigoSqlClient -- logging changes to SQLite or PostgreS

Jay,

Thanks for taking the time to work this issue.

I tried the obvious, and disabled "Open at login" for Indigo, then restarted the computer. "python" and "sqlite3" in my /usr/bin/ directory both link to the new version of python I installed recently. Then I started Indigo server, but got the same error message about being unable to load the python sql module. I also tried using python 2.5 and got the same results.

Hank

Posted on
Tue Jan 25, 2011 11:19 pm
jay (support) offline
Site Admin
User avatar
Posts: 18200
Joined: Mar 19, 2008
Location: Austin, Texas

Re: IndigoSqlClient -- logging changes to SQLite or PostgreS

Open a terminal window and type in the following command:

Code: Select all
ps -axwww | grep Python


and paste the results into a reply.

Jay (Indigo Support)
Twitter | Facebook | LinkedIn

Posted on
Tue Jan 25, 2011 11:54 pm
hwalters offline
Posts: 10
Joined: Sep 01, 2005
Location: Cottage Grove, Oregon

Re: IndigoSqlClient -- logging changes to SQLite or PostgreS

Here's what happened:

Last login: Tue Jan 25 15:53:09 on ttyp1
Welcome to Darwin!
Hanks-Computer:~ henrywalters$ ps -axwww | grep Python
6937 p1 R+ 0:00.00 grep Python
Hanks-Computer:~ henrywalters$

Thanks,
Hank

Posted on
Wed Jan 26, 2011 12:05 am
hwalters offline
Posts: 10
Joined: Sep 01, 2005
Location: Cottage Grove, Oregon

Re: IndigoSqlClient -- logging changes to SQLite or PostgreS

I tried it again with lower case for python with the following result:

Hanks-Computer:~ henrywalters$ ps -axwww | grep python
5594 ?? S 0:53.58 python /Library/Application Support/Perceptive Automation/Indigo 4/IndigoWebServer/IndigoWebServer.py -i1176 -w8176 -cfn
7000 p1 R+ 0:00.00 grep python
Hanks-Computer:~ henrywalters$

Posted on
Wed Jan 26, 2011 8:05 am
jay (support) offline
Site Admin
User avatar
Posts: 18200
Joined: Mar 19, 2008
Location: Austin, Texas

Re: IndigoSqlClient -- logging changes to SQLite or PostgreS

Try changing the first line of the IndigoSqlClient.py file to:

Code: Select all
#!/usr/bin/python


and restart the server.

Jay (Indigo Support)
Twitter | Facebook | LinkedIn

Posted on
Wed Jan 26, 2011 3:18 pm
hwalters offline
Posts: 10
Joined: Sep 01, 2005
Location: Cottage Grove, Oregon

Re: IndigoSqlClient -- logging changes to SQLite or PostgreS

No luck!

The line WAS #! usr/bin/env Python
now reads #! usr/bin/python

I logged out and shut the computer down, then restarted and got the following log file:
-----------------------------------------------------------------------------------
Jan 26, 2011 1:02:41 PM
Starting Indigo Server version 4.1.15
Loading attachments
"device target attachment.scpt" script loaded
"group target attachment.scpt" script loaded
"growl attachment.scpt" script loaded
"iTunes attachment.scpt" script loaded
"scenes attachment.scpt" script loaded
"time stamp attachment.scpt" script loaded
Bonjour registered "Indigo - My House"
Indigo Cocoa client authenticated
SQLite client authenticated
WebServer client authenticated
WebServer started on port 8176 -- authentication disabled
SQLite client connected to Indigo Server
SQLite exception trying to load python sql module: No module named sqlite3
SQLite see IndigoSqlClient ReadMe.html for installation instructions
SQLite client disconnected
WebServer started on port 8176 -- authentication disabled
SQLite client connected to Indigo Server
SQLite exception trying to load python sql module: No module named sqlite3
SQLite see IndigoSqlClient ReadMe.html for installation instructions
SQLite client disconnected

Jan 26, 2011 1:02:47 PM
Trigger Action Start_Timer_Script
info Timer script heartbeat
Connected to SmartHome PowerLinc USB E
PowerLinc address 0A.A4.93, firmware version 2.13
PowerLinc standalone operation disabled
Indigo directly controlling automation logic
-----------------------------------------------------------------------------------

I also tried doing the same thing to the IndigoWebServer.py file and got the same result.


Hank

Posted on
Thu Jan 27, 2011 8:57 am
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: IndigoSqlClient -- logging changes to SQLite or PostgreS

After your edit to the top of the IndigoSqlClient.py file, what does this return from the Terminal (after Indigo starts):

Code: Select all
ps -axwww | grep python

Image

Posted on
Thu Jan 27, 2011 10:57 am
hwalters offline
Posts: 10
Joined: Sep 01, 2005
Location: Cottage Grove, Oregon

Re: IndigoSqlClient -- logging changes to SQLite or PostgreS

Looks like this:

Last login: Wed Jan 26 13:20:52 on console
Welcome to Darwin!
Hanks-Computer:~ henrywalters$ ps -axwww | grep python
364 ?? S 3:04.35 /usr/bin/python /Library/Application Support/Perceptive Automation/Indigo 4/IndigoWebServer/IndigoWebServer.py -i1176 -w8176 -cfn
4827 p1 R+ 0:00.00 grep python
Hanks-Computer:~ henrywalters$

Thanks,
Hank

Who is online

Users browsing this forum: No registered users and 3 guests