View unanswered posts | View active topics It is currently Sat Apr 19, 2014 4:29 pm



This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 
 IndigoSqlClient -- configuring for SQLite 
Author Message
Site Admin
User avatar

Joined: Jan 27, 2003
Posts: 13210
Location: Texas
Post 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.


Mon Apr 14, 2008 10:58 am
Profile WWW

Joined: Apr 10, 2006
Posts: 240
Location: Lower Hudson Valley
Post 
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.


Fri Apr 18, 2008 2:04 pm
Profile
Site Admin
User avatar

Joined: Jan 27, 2003
Posts: 13210
Location: Texas
Post 
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


Fri Apr 18, 2008 3:55 pm
Profile WWW

Joined: Apr 10, 2006
Posts: 240
Location: Lower Hudson Valley
Post 
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.


Sat Apr 19, 2008 10:27 am
Profile
User avatar

Joined: Oct 18, 2006
Posts: 97
Location: Mas
Post 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

_________________
www.powerhs.com


Fri Apr 15, 2011 6:02 am
Profile WWW
Site Admin
User avatar

Joined: Jan 27, 2003
Posts: 13210
Location: Texas
Post 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


Fri Apr 15, 2011 10:03 am
Profile WWW
User avatar

Joined: Oct 18, 2006
Posts: 97
Location: Mas
Post Re: IndigoSqlClient -- configuring for SQLite
"I'd try deleting the SQL database file"
That worked thank you.

_________________
www.powerhs.com


Fri Apr 15, 2011 12:41 pm
Profile WWW
Display posts from previous:  Sort by  
This topic is locked, you cannot edit posts or make further replies.   [ 7 posts ] 

Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group.   Template designed by STSoftware.