this function is now part of the general indigo-utilities plugin: http://forums.indigodomo.com/viewtopic.php?f=164&t=14825&p=103362#p103362
This utility can be used to quickly show data for devices and states from the indigo default SQL database in the log file.(*)
it can be used when you like a quick answer to
- when was the device/state (last) updated
- what was the data yesterday
- are there any gaps
etc.
steps :
1. download plugin
2. install
3. select from plugin menu Variable or device /&state and number of records to show in the log file.
In addition to selecting device/states in the plugin menu you can also define an showSQLdata action to create the output - the action selection menu is identical
Some other features:
- the output will be send to either the log file or a file you define in the plugin/menu or action
- you can define a separator recommend to use ";" or tab. If you like to use tab as separator you can enter "tab" into the separator field
- you can specify headline yes/no
- the value of the first state in the first line returned will be shown in variable: SQLoutput/SQLValueOutput
- the first line of the out will be shown in variable: SQLoutput/SQLLineOutput
- You can also use a script in an action to select the devices/ states etc and create the output
- you can select a condition for state#0 equal or not equal to a string (e.g. "on"). With this you could find e.g. the first or last entry that has that value (use id ="" or id=0 for last / first)
- Code: Select all
plug = indigo.server.getPlugin("com.karlwachs.showSQLData")
if not plug.isEnabled(): return
## print variable
plug.executeAction("printSQLaction" , props ={
"printFile": "" ##/users/karlwachs/documents/sql" ## eeither "" -> to logfile or print ot file --> /users/yourid/documents/filename
, "header": "yes" ## either "yes" or "no"
, "separator": ";" ## either "" or ";" or "\t" for tab or "tab"
, "devOrVar": "var" ## dev or var
, "variable": "EnergyToday2" ##variable name or variable-id
, "id": "0" ## first id to print
, "numberOfRecords":"2"
} )
## print device/states
plug.executeAction("printSQLaction" , props ={
"printFile": "/users/karlwachs/documents/sql" ## either "" -> to logfile or print ot file --> /users/yourid/documents/filename
, "header": "yes" ## either "yes" or "no"
, "separator": ""
, "devOrVar": "dev" ## dev or var
, "device": "volvo" ## device name or device-id
, "id": "" ## first id to print, if "": print the last numberOfRecords records, if =0: print first numberOfRecords
, "numberOfRecords":"10" ## # of records to be printed
, "state0": "Signal" ## can be name of state or *
, "state0Condition":"eq" ## can be "eq" or "ne" or "any" or ""
, "state0Value" :"-55" ## can be any string but not ""
, "state1": "Temperature" ## name of 2. state or ""
, "state2": "Presence" ## ...
, "state3": "MotherAssociated"
, "state4": ""
} )
Karl
example outputs:
- Code: Select all
with separator =";" and print to file:
SQL-OUTPUT:
9; 2015-09-12-12:01:27; -74; 27.45; 2; KM-front
with separator ="" and print to log and postgres:
SQL-OUTPUT:
id | to_char | signal | temperature | presence | motherassociated
----+---------------------+--------+-------------+----------+------------------
9 | 2015-09-12-12:01:27 | -74 | 27.45 | 2 | KM-front
(1 row)
with separator="" and sqlite
SQL command= /usr/bin/sqlite3 -header -column '/Library/Application Support/Perceptive Automation/Indigo 6/logs/indigo_history.sqlite' "SELECT id, strftime('%Y-%m-%d-%H:%M:%S',ts,'localtime'),Temperature,Signal,SecsSinceLastMsg from device_history_1040551329 WHERE ID > 15 LIMIT 2;"
SQL-OUTPUT:
id strftime('%Y-%m-%d-%H:%M:%S',ts,'localtime') temperature signal secssincelastmsg
---------- -------------------------------------------- ----------- ---------- ----------------
16 2015-07-22-21:44:11 -41 123
17 2015-07-22-21:44:11 -41 0
===
for experts only: if your data base has a non sorted id column you can switch on orderBy id in the config menu. Indigo default is that that column is sequential auto increment .. sorted.
here the screen shots:
1. print the last record where state "signal" has value = -55
creates this output:
- Code: Select all
id strftime('%Y-%m-%d-%H:%M:%S',ts,'localtime') signal temperature
---------- -------------------------------------------- ---------- -----------
3378 2015-07-23-08:09:21 -55 26.36
2. and this would print the first 10 records with signal value = "-60" creates this output:
- Code: Select all
id strftime('%Y-%m-%d-%H:%M:%S',ts,'localtime') signal temperature
---------- -------------------------------------------- ---------- -----------
467 2015-07-22-22:47:33 -60 24.89
468 2015-07-22-22:47:33 -60 24.89
469 2015-07-22-22:47:33 -60 24.89
470 2015-07-22-22:47:33 -60 24.89
471 2015-07-22-22:47:33 -60 24.89
472 2015-07-22-22:47:33 -60 24.89
473 2015-07-22-22:47:47 -60 24.89
474 2015-07-22-22:47:50 -60 24.89
475 2015-07-22-22:47:50 -60 24.89
476 2015-07-22-22:48:12 -60 24.89