IndigoSqlClient -- configuring for SQLite

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

IndigoSqlClient -- configuring for SQLite

IndigoSqlClient configuration for SQLite

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 configuring SQLite is easier than PostgreSQL, if you have a specific need for PostreSQL then you will want to read about how to use PostgreSQL and Indigo.

System Requirements
  • Mac OS X 10.5 or greater
  • Indigo Server 2.5 or greater
Installation and Configuration Instructions

I. Installing SQLite. SQLite is included in OS X 10.5's python install, so no special installation is needed.

II. Open configuration file to set your SQLite connection settings:
Code: Select all
/Library/Application Support/Perceptive Automation/Indigo 2/IndigoSqlClient/IndigoSqlClient.conf

Make sure the database_type specified in the file is sqlite. The database name specified (sqlite_db) is the name of the database file that will be used inside the IndigoSqlClient folder.

III. Turn on the Log Device state history to SQLite or PostgreSQL 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.

Posted on
Fri Apr 18, 2008 2:04 pm
CharlieParker offline
Posts: 241
Joined: Apr 10, 2006
Location: Lower Hudson Valley

(No subject)

Work great, thanks.

Is there a reason the the timestamp is UTC, other than that's all you can do with SQLite's DEFAULT? It's not really a problem just a big rethink. Much of what I'm trying to do with Indigo is inherently (or at least though process wise) based on local time.

Posted on
Fri Apr 18, 2008 3:55 pm
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

(No subject)

Hi Charlie,

I'd prefer to store all the timestamps using UTC so they remain correct across any timezone differences. You can, however, easily convert the UTC to local time inside your SQL queries:
Code: Select all
datetime(ts,'localtime')

I believe PostgreSQL actually stores the timestamp in UTC as well, it just automatically converts it to local time when you query it. With SQLite you have to explicitly convert it in the query yourself like above.

Regards,
Matt

Posted on
Sat Apr 19, 2008 10:27 am
CharlieParker offline
Posts: 241
Joined: Apr 10, 2006
Location: Lower Hudson Valley

(No subject)

support wrote:
Hi Charlie,

I'd prefer to store all the timestamps using UTC so they remain correct across any timezone differences. You can, however, easily convert the UTC to local time inside your SQL queries:
Code: Select all
datetime(ts,'localtime')

I believe PostgreSQL actually stores the timestamp in UTC as well, it just automatically converts it to local time when you query it. With SQLite you have to explicitly convert it in the query yourself like above.

Regards,
Matt


Of course, I was just doing a SELECT *, didn't give it any thought, sorry and thanks.

Posted on
Fri Apr 15, 2011 6:02 am
peppop offline
User avatar
Posts: 104
Joined: Oct 18, 2006
Location: Mas

Re: IndigoSqlClient -- configuring for SQLite

I cant get this to work. Help.
Code: Select all
Apr 15, 2011 8:02:12 AM
  WebServer           internal server error "http://192.168.1.120:8000/usageReporter/" from 192.168.1.12
  WebServer           

Request Headers:
  AUTHORIZATION: Digest username="powerhs", realm="Indigo Control Server", nonce="f5cc0c44fa3ae88d4c4ea6ad75539e53", uri="/usageReporter/", response="9490ffa3b4ed97e873e4b973a3d72b56", algorithm="MD5", cnonce="0aee668979ba1958a97c491e5e99e4de", nc=00000003, qop="auth"
  REFERER: http://192.168.1.120:8000/
  HOST: 192.168.1.120:8000
  ACCEPT: application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5
  USER-AGENT: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_7; en-us) AppleWebKit/533.20.25 (KHTML, like Gecko) Version/5.0.4 Safari/533.20.27
  CONNECTION: keep-alive
  Remote-Addr: 192.168.1.12
  ACCEPT-LANGUAGE: en-us
  ACCEPT-ENCODING: gzip, deflate

Traceback (most recent call last):
  File "/Library/Application Support/Perceptive Automation/Indigo 4/IndigoWebServer/cherrypy/_cprequest.py", line 609, in respond
    cherrypy.response.body = self.handler()
  File "/Library/Application Support/Perceptive Automation/Indigo 4/IndigoWebServer/cherrypy/_cpdispatch.py", line 25, in __call__
    return self.callable(*self.args, **self.kwargs)
  File "/Library/Application Support/Perceptive Automation/Indigo 4/IndigoWebServer/plugins/usageReporter/reqhandler.py", line 57, in index
    thePage = self.getReportForDay()
  File "/Library/Application Support/Perceptive Automation/Indigo 4/IndigoWebServer/plugins/usageReporter/reqhandler.py", line 94, in getReportForDay
    cur.execute(u"select distinct(dev_name) from device_history_basic where date(ts, 'localtime') = date(?) order by dev_name", (unicode(inDate),))
DatabaseError: database disk image is malformed

  WebServer           undefined page requested "http://192.168.1.120:8000/usageReporter/css/global4.css" from 192.168.1.12
  WebServer           undefined page requested "http://192.168.1.120:8000/usageReporter/css/indigo4.css" from 192.168.1.12

Posted on
Fri Apr 15, 2011 10:03 am
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: IndigoSqlClient -- configuring for SQLite

The error doesn't look like it is coming from the IndigoSqlClient, but rather is coming from the usageReporter plugin (plugin to the IndigoWebServer):

Code: Select all
  File "/Library/Application Support/Perceptive Automation/Indigo 4/IndigoWebServer/plugins/usageReporter/reqhandler.py", line 94, in getReportForDay
    cur.execute(u"select distinct(dev_name) from device_history_basic where date(ts, 'localtime') = date(?) order by dev_name", (unicode(inDate),))
DatabaseError: database disk image is malformed


The above looks to me like the SQL database file is either corrupt, doesn't exist, or that particular table (device_history_basic) doesn't exist yet in the database.

Are there any Event Log error messages shown when the SQL client starts up, or when you change a device state (which would cause the SQL client to write to the databas)?

I'd try deleting the SQL database file (not Indigo database file!) and stop/starting Indigo Server to get the SQL client to re-create it.

Image

Posted on
Fri Apr 15, 2011 12:41 pm
peppop offline
User avatar
Posts: 104
Joined: Oct 18, 2006
Location: Mas

Re: IndigoSqlClient -- configuring for SQLite

"I'd try deleting the SQL database file"
That worked thank you.

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 2 guests