Indigo Domotics

https://www.dropbox.com/s/31ufcxostzs6cbm/showSQLData-v-0-9-1.zip?dl=1 (always the most recent version in the first post)

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     


Hi Karl,

Great idea for a plugin. Thanks for yet another contribution!

version 0.3.1

added:
- you can select 1..5 states to be displayed in a row (up from 1)
- select * for all states
- select at which ID the output should start, besides # of records

fixed:
some spelling

Karl

If you don't mind a recommendation: just keep updating the links to the downloads in the first post - that way, people don't have to search through all the posts in the thread to find the latest link.

version 0.5.1 posted

ADDED
- supports selection of variables (in addition to devices/states)
- supports postGRE database, select in plugin menue/Configuration.. supports also non standard installation of postGRE.. you need to supply the prostGRES command string for non standard implementations
- removes devices that have no states from in the menus, i.e. have no entries in SQL data base
- added error control for bad selections and error response from SQLite/postGRE
- added a "status" field in the memue to show if things are working, what to do next etc.

FIXED
some formats, texts ..

don't know what else would be missing.


Karl

Hi karl,
Thanks for this - very useful for me now you have added in Postgre support

Couple of things:

• Your Url in the 1st post isn't shown as a link - it is missing the equals sign
• Got an SQL error looking for sensorvalue.ui it is stored in the table as sensorvalue_ui

Your Url in the 1st post isn't shown as a link - it is missing the equals sign
Got an SQL error looking for sensorvalue.ui it is stored in the table as sensorvalue_ui

fixed the link.

the plugin takes the state name and then looks for that in the data base. Do I need to replace "." by "_" in general or is it just for the .ui state?

Karl

added/fixed:

replaced ANY "." in a state name with "_" in the SQL query.

Karl

Thanks for fixing

version 0.6.1 is posted:


as requested:

added
you can now:
- print to either file or indigo-log
- define an action in an action group and select the device/states there
- use an action script (or call it from a plugin(*)) to create the output
and
- added some more error control, i.e. bad input, and database locked error (will retry after 0.5 seconds)


Karl

(*) don't know why you would like to do that, but it works

Hi Karl,
Brilliant - Thanks for the latest update

The Action addition is really useful and also the writing to the file.
I haven't tried invoking it from a script or plugin.

At the risk of adding to your workload , would it be possible to specify a CSV format for the output file - maybe specifying the separator and enclosing string field in double quotes. I could then import the results into Numbers.

Thanks again for you work on this.

new version posted (0.7.2)


added:
- you can now specify a separator. Highly recommend to use ";"
- the sql output out is now available in 2 variables for the first line returned (a) the value of the first state and (b) the whole first line(*)

by specifying a separator ";" you can easily import the output into excel the only character that should appear is a ";" and thats unlikely for numbers...

with the variables containing the values you can then use them in a script or trigger (e.g. trigger if variable SQLValueOutput > 5)

Karl

(* see screen shot)

Hi Karl,
I have tried to import a csv file created by the plugin using semi-colon as a separator but OS X Numbers doesn't handle it correctly.

I can't spend any more time on looking at the problem this evening (currently 19:15 in the UK) but will take a look at it further tomorrow

Just wanted to say thanks for the ongoing changes

you can use this in the script version
"separator" : "\t"
"printFile":"/users/youruserid/documents/sql.txt"

In the menu input type command-v to enter a tab character.

That create a file slq.txt which works with OSX-NUMBERS

in excel tab comma ; etc all work fine

Karl

trick to get the previous value of a device/state into the variable SQLValueOutput use:

"numberOfRecords":"2"
and
"id":""

that will get the last 2 records from SQL and the and the plugin will pick the first dataline to populate the variable which is the previous value of the device/state

Karl