Hey Matt and Jay.

I'm really liking the new SQL Logger plugin and database structure. It allows for a LOT more meaningful data collection and analysis. I was relatively easily able to extract energy usage trends for my iMeter Solo device to determine the attached refrigerator's peak energy usage times.

One thing that would be very convenient would be the creation of a database view for each device and variable table so that one could select against either the actual table (using the device/variable ID) or the view into the same table using the device/variable name. This would make it a lot more convenient to quickly write SQL queries and would be more consistent with the multiple ways of referencing devices and variables within the IOM.

In any case, you guys do great work. Can't wait to see what you introduce in the next release.

Thanks for the feedback. There is quite a bit I'd like to add to the SQL Logger. One could create a plugin action, for example, that queries the device history for all thermostats and saves them in to comma delimited files. Or a query to determine average/high/low temperatures from a temperature sensor over a range of time.

The database views is a good idea. So your idea is to add views that allow selection on device/variable names instead of ID, which means we would need a device and variable ID/name mapping table as well, correct?

Any ideas for other views that might be useful?

matt (support) wrote:So your idea is to add views that allow selection on device/variable names instead of ID, which means we would need a device and variable ID/name mapping table as well, correct?

Well, I guess one could be created. However, what I had in mind wouldn't require any mapping table. My understanding is that SQLite3 supports the creation of read-only table views. So, for example, instead of finding the maximum temperature from yesterday using the SQL statement

Code: Select all

SELECT ts, MAX(temperatureinput1)
FROM device_history_1652127487
WHERE ts BETWEEN '2012-05-26 00:00:00' AND '2012-05-27 00:00:00';

one could do the same thing with the SQL statement

Code: Select all

SELECT ts, MAX(temperatureinput1)
FROM device_history_Main_Thermostat
WHERE ts BETWEEN '2012-05-26 00:00:00' AND '2012-05-27 00:00:00';

The plugin could create the view immediately after the table was created using

Code: Select all

CREATE VIEW device_history_<modified device name> AS SELECT h.* FROM device_history_<device ID> h;

If the device name changes, the view would have to be dropped and re-created with the new device name.

However, I think I know where you're going with the device ID-to-device name mapping table and I like that idea too! In fact, that'd probably be better than a separate view for each device_history_*/variable_history_* table as one could use the actual device name in SQL statements rather than the character-set-limited device name version that would be required for view names.

Any ideas for other views that might be useful?

I like both of those examples you provided. The CSV output idea could be very useful, and not just for thermostats. Perhaps a view that shows the top 5 (or 10) most frequently turned on lights/devices? I'll ponder on it some more and post again if I can think of other useful data that could be gleaned from the database. It does strike me, though, that it could be possible to calculate historical device energy usage from the device history tables. I might investigate that further for my little eTrack setup.

I thought of an idea...

Update the SQL Logger plugin to include the capability to create a SQL Log device that contains, as status values, some number of recent records of log data from a user selectable device, variable, or event log tables. (Of course, there might need to be some method to prevent SQL Logger from creating a device table for its own SQL Logger device, to prevent a "logging loop"). This would be exceptionally useful, especially for control pages (and would address the frequent request for the ability to view event logs within the Indigo Touch app without some creative hacking). For advanced users, instead of just specifying a single table from which to draw recent records, the user could enter their own SQL statement.

The main problem with putting event logs in Indigo Touch is that there are no multi-line text widgets at the moment in control pages. Getting the data there is not really the issue.

I'm not sure that having states with large amounts of data associated with them is a particularly good/useful idea. Something to think about.

jay (support) wrote:I'm not sure that having states with large amounts of data associated with them is a particularly good/useful idea.

Agreed. Though the length could always be limited to, say, 128 characters per state. Keeping data in a device state might also be an easily accessible way to make available pre-formed query results too, esp. for people who don't know any SQL.