SQL Logger Questions

Forum rules

This is a legacy forum which is locked for new topics. New topics should be started in one of the other forums under Extending Indigo

Posted on
Tue May 14, 2013 10:02 am
Perry The Cynic offline
Posts: 836
Joined: Apr 07, 2008

SQL Logger Questions

I've just looked at your SQL Logger plugin for the first time (using Postgres, if that matters), and I've got a bunch of questions. (Surprised? :-))

What is the typeval column in the eventlog_history?

Does the logger write any device configuration to the database? I.e. is the state of the Indigo device database reflected there? Any data that lets me translate id numbers to device names?

Any way to just get some device state logs, instead of all or none?

Any way to put all the device states into one table (or at least all devices of the same type)? Giving each temperature sensor its own table makes it rather unpleasant to write a query for all temperatures in the house.

Any way for my plugin to use your plugin's connection to write its own data to the database? Any way for my plugin to query the database?

Is there a supported way to use the SQL module (short of ripping a copy out of your plugin and sticking it into mine)?

Nice plugin, by the way. :-)

Cheers
-- perry

Posted on
Tue May 14, 2013 10:37 am
nsheldon offline
Posts: 2469
Joined: Aug 09, 2010
Location: CA

Re: SQL Logger Questions

Perry The Cynic wrote:
Any way to just get some device state logs, instead of all or none?

Any way to put all the device states into one table (or at least all devices of the same type)? Giving each temperature sensor its own table makes it rather unpleasant to write a query for all temperatures in the house.

SQL views may work well for these. You might be able to use the CREATE VIEW command to create a view that shows exactly what you want (e.g. all device states all in one view), then all you'd have to do is select against that view rather than compose a complex SQL statement every time you wanted to know the state of a given device. I haven't tried it myself yet, but it sounds clever anyway. :-D

Posted on
Tue May 14, 2013 12:02 pm
Perry The Cynic offline
Posts: 836
Joined: Apr 07, 2008

Re: SQL Logger Questions

SQL views may work well for these. You might be able to use the CREATE VIEW command to create a view that shows exactly what you want (e.g. all device states all in one view), then all you'd have to do is select against that view rather than compose a complex SQL statement every time you wanted to know the state of a given device. I haven't tried it myself yet, but it sounds clever anyway.

I want a query for "show me all temperature readings in the house between 12pm and 1pm," in a way that doesn't require me editing my SQL statements every time I add another thermostat. I don't think that's possible given the current schema, which seems designed to support queries for single devices, but not across devices.

Cheers
-- perry

Posted on
Tue May 14, 2013 2:48 pm
nsheldon offline
Posts: 2469
Joined: Aug 09, 2010
Location: CA

Re: SQL Logger Questions

This bit of hackish SQL worked for me when selecting against the "eventlog_history" table created by SQL Logger (I'm using SQLite, so I'm assuming PostgreSQL has very similar functions to the ones I'm using here).

Code: Select all
SELECT DATETIME(ts, 'localtime') "Date and Time",
  REPLACE(message, ' temperature changed to ', ' ') "Thermostat and Temperature"
FROM eventlog_history
WHERE typestr is 'Received INSTEON'
  AND message LIKE '%temperature%'
  AND ts > DATETIME('2013-05-14 12:00:00', 'utc')
  AND ts < DATETIME('2013-05-14 13:00:00', 'utc')

That returned the following output:

Code: Select all
Date and Time          Thermostat and Temperature
2013-05-14 12:00:58    "Main Thermostat" 74.0
2013-05-14 12:14:10    "Main Thermostat" 73.0
2013-05-14 12:19:37    "Main Thermostat" 74.0
2013-05-14 12:27:18    "Main Thermostat" 75.0
2013-05-14 12:31:41    "Main Thermostat" 74.0
2013-05-14 12:41:39    "Main Thermostat" 73.0
2013-05-14 12:42:48    "Main Thermostat" 74.0
2013-05-14 12:45:00    "Main Thermostat" 73.0
2013-05-14 12:46:06    "Main Thermostat" 74.0

I only have 1 thermostat, but that SQL would work against any number of thermostats. As I'm sure you've noticed, that SQL makes a number of assumptions about the type of thermostat (INSTEON) and event log message format (<some thermostat name> temperature changed to <temperature>). If any of those assumptions were false, the output would be, um, unexpected. :-)

Posted on
Tue May 14, 2013 3:04 pm
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: SQL Logger Questions

Perry The Cynic wrote:
What is the typeval column in the eventlog_history?

That is an enum of one of the following:
Code: Select all
   kMessageType_Application = 0,
   kMessageType_Error = 1,
   kMessageType_Debug = 3,
   kMessageType_Custom =  5,
   kMessageType_ReceviedX10 = 6,
   kMessageType_ReceviedRF,
   kMessageType_ReceviedINSTEON,
   kMessageType_SentOffline,
   kMessageType_SentX10,
   kMessageType_SentINSTEON,
   kMessageType_Email_Deleted,
   kMessageType_Email_Scanned,
   kMessageType_Email_Sent,
   kMessageType_PowerFailure,
   kMessageType_InterfaceFailure,
   kMessageType_TriggerAction,
   kMessageType_TimeDateAction,
   kMessageType_ActionGroup,
   kMessageType_Script,
   kMessageType_Updated

Perry The Cynic wrote:
Does the logger write any device configuration to the database? I.e. is the state of the Indigo device database reflected there? Any data that lets me translate id numbers to device names?

Device states are written to the device specific tables whenever there is a state change (and sometimes probably a bit more frequently than is needed). Additionally, on plugin launch it writes all devices to their tables to provide an accurate "base."

Perry The Cynic wrote:
Any way to just get some device state logs, instead of all or none?

Not currently.

Perry The Cynic wrote:
Any way to put all the device states into one table (or at least all devices of the same type)? Giving each temperature sensor its own table makes it rather unpleasant to write a query for all temperatures in the house.

You could create a plugin that maintains a temperature collection device type, then query on it. :-)

Perry The Cynic wrote:
Any way for my plugin to use your plugin's connection to write its own data to the database? Any way for my plugin to query the database?

You can query it (SQLite or PostgreSQL) , but if I remember correctly Indigo has the database locked for writing so you won't be able to write to it. You can write to it indirectly of course using the temperature collection device idea above.

Perry The Cynic wrote:
Is there a supported way to use the SQL module (short of ripping a copy out of your plugin and sticking it into mine)?

No, but the bpgsql.py file we didn't write (it has a MIT/BSD style license) and you have our permission to use the indigosql.py file in your plugins if you would like.

Image

Posted on
Thu May 16, 2013 9:58 am
Perry The Cynic offline
Posts: 836
Joined: Apr 07, 2008

Re: SQL Logger Questions

Thanks for all the answers.

The biggest missing feature (for me) is that Indigo does not write and maintain a table that represents the Indigo device database - basically, a mapping from device numbers to names and types. Without that, I need to track this metadata outside the database, which is brittle and annoying.

Oh, and I get the occasional message in the Indigo log:
modifying column motion type of table device_history_795968412 ("KI-SH Kitchen Front Shades") from boolean to TEXT

Should I worry about this? (The motion state of shades is a number, but it's usually zero until the shades actually start moving. Is there some heuristic where Indigo guesses that it's boolean when it's only seen zero so far?)

Cheers
-- perry

Posted on
Thu May 16, 2013 10:07 am
nsheldon offline
Posts: 2469
Joined: Aug 09, 2010
Location: CA

Re: SQL Logger Questions

Perry The Cynic wrote:
The biggest missing feature (for me) is that Indigo does not write and maintain a table that represents the Indigo device database...

I'll second that sentiment, though I can understand not wanting to duplicate the Indigo XML database into whatever database the SQL Logger is logging as that would then require maintenance of 2 device/system databases instead of just one.

Posted on
Fri May 17, 2013 3:46 pm
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: SQL Logger Questions

Perry The Cynic wrote:
The biggest missing feature (for me) is that Indigo does not write and maintain a table that represents the Indigo device database - basically, a mapping from device numbers to names and types. Without that, I need to track this metadata outside the database, which is brittle and annoying.

Are you executing this logic from an Indigo plugin? If so, then you should be able to easily lookup the device ID / name using the IOM. If not, then you could look it up using the indigohost command line. Something like:

Code: Select all
indigohost -e 'return indigo.devices[1368716444].name'

It isn't super efficient since under-the-hood it has to start a new Indigo client and connect to the server, but it works.

Perry The Cynic wrote:
Oh, and I get the occasional message in the Indigo log:
modifying column motion type of table device_history_795968412 ("KI-SH Kitchen Front Shades") from boolean to TEXT

Should I worry about this? (The motion state of shades is a number, but it's usually zero until the shades actually start moving. Is there some heuristic where Indigo guesses that it's boolean when it's only seen zero so far?)

What type of device is this? If it is a plugin device, then the problem is probably that the plugin is updating the device state 'motion' to a python string instead of a number. You'll see the log message above if a plugin updates a state value to a different type than what is currently has.

Image

Posted on
Fri May 17, 2013 4:46 pm
Perry The Cynic offline
Posts: 836
Joined: Apr 07, 2008

Re: SQL Logger Questions

Are you executing this logic from an Indigo plugin? If so, then you should be able to easily lookup the device ID / name using the IOM. If not, then you could look it up using the indigohost command line. Something like:[...]

Of course I could. But casual data analysis is usually done through pure sql, either from an interactive sql browser or by hooking up something like Gnuplot or Mathematica to the sql source. For that, it would be extremely handy if the Indigo device database was available as a table in the database, rather than through some other API; and for all device states to be merged into one table set. (In your case, you'd want one device table, one values table, and one glue table, with the existing device_history_nnn tables becoming views.)

The existing mechanism is serviceable for its core need (generating graphs from individual device data, and filtering the event log). It's a pain for exploration: What was the lowest temperature ever recorded in or around my house? Which window has been open the longest during last March? I have all the data to answer these questions, but without a merged state table and a device table, I can't write sql queries for them without it becoming impossibly tedious... or without writing a custom Python program for each question.

Anyway, it's not a big deal for me. I'm just giving you first-impression feedback.

Cheers
-- perry

Posted on
Fri May 17, 2013 4:49 pm
Perry The Cynic offline
Posts: 836
Joined: Apr 07, 2008

Re: SQL Logger Questions

What type of device is this? If it is a plugin device, then the problem is probably that the plugin is updating the device state 'motion' to a python string instead of a number. You'll see the log message above if a plugin updates a state value to a different type than what is currently has.

Never mind that; it turns out my Devices.xml had the wrong data type for the motion state (boolean instead of number... which the plugin apparently decided should be TEXT).

(The device is a custom Lutron shade controller. Motion is the Lutron feedback indicator of movement - how many seconds the controller thinks it will take to get there. Rather useful, actually. Also undocumented. :-))

Cheers
-- perry

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 2 guests