SQL Logger Questions

[Perry The Cynic]

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

[nsheldon]

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.

[Perry The Cynic]

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

[nsheldon]

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.

[matt (support)]

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

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."

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

Not currently.

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.

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.

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.

[Perry The Cynic]

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

[nsheldon]

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.

[matt (support)]

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.

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.

[Perry The Cynic]

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

[Perry The Cynic]

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