indigo utilities discussion

Posted on
Mon Jan 08, 2018 5:14 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: indigo utilities discussion

Yes that’s an option.
But then you get ts twice.
Can do that if you prefer.
Would be nice to have do
ts-local and * but not ts
Have not found how to do that



Sent from my iPhone using Tapatalk

Posted on
Mon Jan 08, 2018 5:51 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: indigo utilities discussion

is this better?
Code: Select all
   utilities                       print SQL data for device 375541516/wind sensor states: ['*']
   SQL command=                    /usr/bin/sqlite3  -header -column '/Library/Application Support/Perceptive Automation/Indigo 7/logs/indigo_history.sqlite' "SELECT id, strftime('%Y-%m-%d-%H:%M:%S',ts,'localtime') as 'ts_local' , *  from device_history_375541516 WHERE ID > 0 ORDER by id  LIMIT 5;"
   SQL-OUTPUT:                     
id          ts_local             id          ts                   onoffstate  avgspeed    batterylevel  directiondegrees  directiontext  display     gust        lastupdated  signalstrength  temperature  type        windchill
----------  -------------------  ----------  -------------------  ----------  ----------  ------------  ----------------  -------------  ----------  ----------  -----------  --------------  -----------  ----------  ----------
2           2014-06-28-11:45:32  2           2014-06-28 16:45:32  False                                                                                                                                                         
3           2014-06-28-11:45:48  3           2014-06-28 16:45:48              0           0             0                                            0                        0               0            0           0         
4           2014-06-28-11:45:58  4           2014-06-28 16:45:58              4.9         10            0                 N              4 mph       2.7         2014/06/28   5               0            2           0         
5           2014-06-28-11:46:12  5           2014-06-28 16:46:12              3.4         10            0                 N              3 mph       2.7         2014/06/28   5               0            2           0         
6           2014-06-28-11:46:40  6           2014-06-28 16:46:40              3.4         10            0                 N              3 mph       6.9         2014/06/28   5               0            2           0         


Karl

Posted on
Mon Jan 08, 2018 6:58 pm
Colorado4Wheeler offline
User avatar
Posts: 2794
Joined: Jul 20, 2009
Location: Colorado

Re: indigo utilities discussion

Hey, anything you do in regards to getting the system date displayed is A-OK with me Karl! That looks awesome, thank you!!

My Modest Contributions to Indigo:

HomeKit Bridge | Device Extensions | Security Manager | LCD Creator | Room-O-Matic | Smart Dimmer | Scene Toggle | Powermiser | Homebridge Buddy

Check Them Out Here

Posted on
Mon Jan 08, 2018 7:06 pm
Colorado4Wheeler offline
User avatar
Posts: 2794
Joined: Jul 20, 2009
Location: Colorado

Re: indigo utilities discussion

Oh and there isn't a way to exclude a column from * through a query. The alternative would be to copy it to another table and report that (SELECT * INTO plugin_output and then DROP COLUMN) or to do a quick field check beforehand so that when the user selects * you interpret that as looping through all fields except TS. I'm an SQL developer and have been down this path before.

My Modest Contributions to Indigo:

HomeKit Bridge | Device Extensions | Security Manager | LCD Creator | Room-O-Matic | Smart Dimmer | Scene Toggle | Powermiser | Homebridge Buddy

Check Them Out Here

Posted on
Mon Jan 08, 2018 7:14 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: indigo utilities discussion

It is easier to do this on the result in python


Sent from my iPhone using Tapatalk

Posted on
Mon Jan 08, 2018 9:59 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: indigo utilities discussion

this https://www.dropbox.com/s/7q2wgce1eexukjk/utilities-v-1.12.10.zip?dl=1
will produce this:
Code: Select all
   SQL command=                    /usr/bin/sqlite3  -header -column '/Library/Application Support/Perceptive Automation/Indigo 7/logs/indigo_history.sqlite' "SELECT strftime('%Y-%m-%d-%H:%M:%S',ts,'localtime') as 'ts_local' , *  from device_history_375541516 WHERE ID > 0 ORDER by id  LIMIT 10;"
   SQL-OUTPUT:                     
id          ts_local             onoffstate  avgspeed    batterylevel  directiondegrees  directiontext  display     gust        lastupdated  signalstrength  temperature  type        windchill
----------  -------------------  ----------  ----------  ------------  ----------------  -------------  ----------  ----------  -----------  --------------  -----------  ----------  ----------
2           2014-06-28-11:45:32  False                                                                                                                                                         
3           2014-06-28-11:45:48              0           0             0                                            0                        0               0            0           0         
4           2014-06-28-11:45:58              4.9         10            0                 N              4 mph       2.7         2014/06/28   5               0            2           0         
5           2014-06-28-11:46:12              3.4         10            0                 N              3 mph       2.7         2014/06/28   5               0            2           0         
6           2014-06-28-11:46:40              3.4         10            0                 N              3 mph       6.9         2014/06/28   5               0            2           0         
7           2014-06-28-11:46:54              3.4         10            0                 N              3 mph       5.6         2014/06/28   5               0            2           0         
8           2014-06-28-11:47:08              4.7         10            0                 N              4 mph       3.6         2014/06/28   5               0            2           0         
9           2014-06-28-11:47:22              4.7         10            0                 N              4 mph       4.3         2014/06/28   5               0            2           0         
10          2014-06-28-11:47:36              4.7         10            0                 N              4 mph       5.4         2014/06/28   5               0            2           0         
11          2014-06-28-11:48:04              4.9         10            0                 N              4 mph       6           2014/06/28   5               0            2           0         

   utilities                       record id# 0  2           2014-06-28-11:45:32  False                                                                                                                                                         


for sqlite

is this ok ? if yes will post to GitHub

Karl

reformatted the output in python (drop col 2, swap col 0,1)

Posted on
Mon Jan 08, 2018 10:10 pm
Colorado4Wheeler offline
User avatar
Posts: 2794
Joined: Jul 20, 2009
Location: Colorado

Re: indigo utilities discussion

Genius! Looks awesome!

My Modest Contributions to Indigo:

HomeKit Bridge | Device Extensions | Security Manager | LCD Creator | Room-O-Matic | Smart Dimmer | Scene Toggle | Powermiser | Homebridge Buddy

Check Them Out Here

Posted on
Tue Jan 09, 2018 7:09 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: indigo utilities discussion

posted the final version on GitHub. Had to add code for "no header and/or no separator"
The trick to drop the regular TS column does not work if there are no columns in the output and no separator to id the columns (one of them is ok)

This is only for sqlite db and selection =*.
Time was already local for selection with specific columns and all of postgres

Karl

Posted on
Tue Jan 09, 2018 3:00 pm
Colorado4Wheeler offline
User avatar
Posts: 2794
Joined: Jul 20, 2009
Location: Colorado

Re: indigo utilities discussion

LOVE! Thanks Karl, it makes the report a lot more useful for me!

Code: Select all
   SQL-OUTPUT:                     
id          ts_local             sensorvalue
----------  -------------------  -----------
1           2018-01-08-09:16:30  335.0     
2           2018-01-08-09:17:11  335.0     
3           2018-01-08-11:13:08  18.0       
4           2018-01-08-11:13:31  376.0     
5           2018-01-08-11:17:34  15.0       
6           2018-01-08-11:18:03  371.0     
7           2018-01-08-11:30:01  25.0       
8           2018-01-08-11:30:31  362.0     
9           2018-01-08-12:33:42  29.0       
10          2018-01-08-13:31:51  378.0     
11          2018-01-08-13:32:13  14.0       
12          2018-01-08-14:32:51  229.0     
13          2018-01-08-15:33:28  238.0     
14          2018-01-08-16:34:06  221.0     
15          2018-01-08-17:34:45  214.0     
16          2018-01-08-18:35:23  216.0     
17          2018-01-08-20:36:40  217.0     
18          2018-01-08-21:37:20  218.0     
19          2018-01-08-22:38:02  219.0     
20          2018-01-08-23:12:44  0.0       
21          2018-01-09-08:00:18  392.0     
22          2018-01-09-09:00:19  186.0     
23          2018-01-09-10:00:56  81.0       
24          2018-01-09-11:01:35  22.0       
25          2018-01-09-12:02:16  26.0       
26          2018-01-09-13:02:52  21.0     

My Modest Contributions to Indigo:

HomeKit Bridge | Device Extensions | Security Manager | LCD Creator | Room-O-Matic | Smart Dimmer | Scene Toggle | Powermiser | Homebridge Buddy

Check Them Out Here

Posted on
Tue Jan 09, 2018 8:23 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: indigo utilities discussion

Glad it works for you.


Sent from my iPhone using Tapatalk

Posted on
Mon Jan 29, 2018 11:40 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: indigo utilities discussion

released v 1.14.11

added option to print all indigo store plugins sorted with latest release date
looks like this:
Code: Select all
   Plugin Name                     web page -----------------------------------   Date released   Version
   piBeacon                        https://www.indigodomo.com/pluginstore/59/     Jan. 29, 2018   7.80.201 
   Sensibo                         https://www.indigodomo.com/pluginstore/146/    Jan. 29, 2018   0.1.5     
   NuVo Grand Concerto             https://www.indigodomo.com/pluginstore/66/     Jan. 28, 2018   2.0.2     
   FindFriendsMini                 https://www.indigodomo.com/pluginstore/139/    Jan. 27, 2018   0.4.9     
   Multitool                       https://www.indigodomo.com/pluginstore/145/    Jan. 25, 2018   1.0.14   
   fingscan                        https://www.indigodomo.com/pluginstore/72/     Jan. 23, 2018   7.24.21   
   Announcements                   https://www.indigodomo.com/pluginstore/141/    Jan. 19, 2018   0.4.0     
   Shairport Sync                  https://www.indigodomo.com/pluginstore/144/    Jan. 18, 2018   1.0.0     
   Eversolar Monitor               https://www.indigodomo.com/pluginstore/143/    Jan. 17, 2018   1.0.1     
   NetworkOwl                      https://www.indigodomo.com/pluginstore/142/    Jan. 16, 2018   1.6.1     
   Mac Apps                        https://www.indigodomo.com/pluginstore/92/     Jan. 14, 2018   0.0.6   
...
 



Karl

Posted on
Thu Feb 08, 2018 8:39 am
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: indigo utilities discussion

Whilst you have an option in utilities to output device names and ids to the log, it would be awesome if you could output the same to a postgres table as well, ie something with 2 columns, device_id, device_name? Or even do a generic one, so has columns of id, name and type, where type is device or variable?

Posted on
Thu Feb 08, 2018 8:49 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: indigo utilities discussion

You mean creating a table in the indigo Postgres database? I would shy away from that. Indigo owns the dB. I don’t know what indigo would do if another tables is created.




Sent from my iPhone using Tapatalk

Posted on
Thu Feb 08, 2018 9:07 am
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: indigo utilities discussion

Yes I did... although indigo owns the database indigo_history or whatever its been called, what about creating in the postgres db, or the username one?

Posted on
Thu Feb 08, 2018 9:28 am
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: indigo utilities discussion

Or I guess I could run it in a script, with a quick hack of asomethign found online, something along these lines?

import psycopg2

conn = psycopg2.connect(database = "userid", user = "postgres", password = "", host = "127.0.0.1", port = "5432")
cur = conn.cursor()
cur.execute('''CREATE TABLE DEVICES
(DEVICE_ID INT PRIMARY KEY NOT NULL,
DEVICE_NAME TEXT NOT NULL);''')
conn.commit()

for dev in indigo.devices:
cur.execute("INSERT INTO COMPANY (DEVICE_ID,DECIVE_NAME) \
VALUES (dev.id,dev.name )");

as as starting point?

Page 7 of 11 1 ... 4, 5, 6, 7, 8, 9, 10, 11

Who is online

Users browsing this forum: No registered users and 1 guest