indigo utilities discussion

Got one of those... helped produce a better resolution, didnt really help with performance.

yes Time Machine is no good for sql backups.
fix has worked in 30% of the cases.
looks like your db is toast. need to start from scratch.

I would start with postgres app . Very easy to install and mine has had no probs for 18 months and the db is now at 25GB

use the backup facility!!

Karl

I got a time machine backup from 1st Oct that was 2.2Gb and ran a db fix on it on the old mac, which took 2 hours, and generated one of the dump files at 5Gb, but in the end created a fixed version of only 960Mb and thankfully IT WORKED!

It opened up fine on the new machine, so I think I can now retire the old machine entirely.

Now to migrate to postgres *assuming I can.

Is this normal start up details, i.e. is it working fine?


12 Oct 2017, 17:20:56
Enabling plugin "SQL Logger 2.0.5"
Starting plugin "SQL Logger 2.0.5" (pid 70231)
Started plugin "SQL Logger 2.0.5"
SQL Logger connected to /Library/Application Support/Perceptive Automation/Indigo 7/Logs/indigo_history.sqlite
SQL Logger updating all device tables
SQL Logger creating table device_history_1962767071 for "006 - Temperature"
SQL Logger creating table device_history_746252985 for "014 - Temperature"
SQL Logger creating table device_history_1033222892 for "015 - Temperature"
SQL Logger creating table device_history_121028030 for "018 - Temperature"
SQL Logger creating table device_history_1478549967 for "Alexa Hue Bridge One"
SQL Logger creating table device_history_1123353824 for "Apple TV IP"
SQL Logger creating table device_history_455762455 for "Bedroom Dot Unifi"
SQL Logger creating table device_history_803348098 for "Bedroom Lights"

Found this..

viewtopic.php?f=168&t=13713&p=118546&hilit=migrate+to+postgres#p118546

Will have a look at that tonight.

That only works if you have a working SQLite. And if you / a plugin ever added or removed states in devices that will make it nearly impossible as the # of states is not constant etc.

After a week of trying I started from scratch. In Postgres.


Sent from my iPhone using Tapatalk

just want to brag a little..

had a postgres DataBase issue and used the dump file that indigoUtilies creates every night. ... and I could load the database from last night and it actually worked.. back up and running..

so make your daily backup file (i.e. sql dump )


Karl

posted v - 1.12.7

added/ changed:
the postgres database dump backup now adds:
1. creates a zip file instead of plain dump file (23 GB database --> 31 GB dump file --> 2.8GB zip file)
2. keeps 2 versions in the indigo utilities directory ( postgresBackup.zip and postgresBackup.zip-1)
3. shows in the log file basic steps how to restore ( tested and works)

suggestion to run this action early in the morning.. With a database size of 23 GB it takes about 10 minutes to finish on a 2008 Mac Pro


Karl

Not sure how I've gone this long without using SQL Logger and your utilities but I have and I feel like a heel for it too

Have you considered adding an option to translate the SQL timestamp to local time? Even a configuration option that would allow the user to just add or subtract hours to get it local, it's odd seeing the reports in the SQLIte DB. Then that could be used in the query to change the time to an easier-to-read stamp.

Awesome utility though, I was just about to write an SQL query manager plugin when I stumbled across this gem! Thanks Karl!

That should be easy to add.
Glad you like it.


Sent from my iPhone using Tapatalk

Awesome, I look forward to playing with that feature!

On a side note, creating a query action always results in the following error on the callback when selecting a device:

Code: Select all

Jan 8, 2018 at 10:27:17 AM
   utilities Error                 Error in plugin execution UiAction:

Traceback (most recent call last):
TypeError: pickDeviceCALLBACK() takes at most 3 arguments (4 given)


I was just about to write an SQL query manager plugin

Yes please

i have that error fixed.. q on the local time. which database are you using. lite or postgres?

my understanding:

postgres stores local time, sqlite stores UTC. The plugin converts for sqlite to local with:

Code: Select all

 /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'),temperature from device_history_375541516 WHERE ID > 0 ORDER by id  LIMIT 5;"


that should do it. I have been running on postgres for > 1 year now and don't have recent sqlite db.

you can simply test it: open terminal copy and past the command (replace id with a valid id and pick a sensor that has temperature ) run it w and wo the " ,'localtime'" to check if there is a difference
like this:

Code: Select all

$/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'),temperature from device_history_375541516 WHERE ID > 0 ORDER by id  LIMIT 5;"
id          strftime('%Y-%m-%d-%H:%M:%S',ts,'localtime')  temperature
----------  --------------------------------------------  -----------
2           2014-06-28-11:45:32                                     
3           2014-06-28-11:45:48                           0         
4           2014-06-28-11:45:58                           0         
5           2014-06-28-11:46:12                           0         
6           2014-06-28-11:46:40                           0         
$ /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),temperature from device_history_375541516 WHERE ID > 0 ORDER by id  LIMIT 5;"
id          strftime('%Y-%m-%d-%H:%M:%S',ts)  temperature
----------  --------------------------------  -----------
2           2014-06-28-16:45:32                         
3           2014-06-28-16:45:48               0         
4           2014-06-28-16:45:58               0         
5           2014-06-28-16:46:12               0         
6           2014-06-28-16:46:40               0 

I only have an old sqlite db to test

and here the fixed version ( action error) .. will post later on GitHub
https://www.dropbox.com/s/ayym3ixmab3joun/utilities-v-1.12.9.zip?dl=1

Karl

Here are my results from the terminal, the updated version didn't incorporate this change I presume since the query still returns the UTC on my SQLite:

Code: Select all

HOUSE:Logs administrator$ /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'), * from device_history_135129331 WHERE ID > 0 ORDER by id  LIMIT 5;"
id          strftime('%Y-%m-%d-%H:%M:%S',ts,'localtime')  id          ts                   sensorvalue  sensorvalue_ui
----------  --------------------------------------------  ----------  -------------------  -----------  --------------
1           2018-01-08-09:16:30                           1           2018-01-08 16:16:30  18.0         18.0 °C     
2           2018-01-08-09:17:11                           2           2018-01-08 16:17:11  18.0         18.0 °C     
3           2018-01-08-11:30:47                           3           2018-01-08 18:30:47  18.7         18.7 °C     
HOUSE:Logs administrator$



I changed it slightly so the column names weren't formulaic:

Code: Select all

HOUSE:Logs administrator$ /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 'local_ts', * from device_history_135129331 WHERE ID > 0 ORDER by id  LIMIT 5;"
id          local_ts             id          ts                   sensorvalue  sensorvalue_ui
----------  -------------------  ----------  -------------------  -----------  --------------
1           2018-01-08-09:16:30  1           2018-01-08 16:16:30  18.0         18.0 °C     
2           2018-01-08-09:17:11  2           2018-01-08 16:17:11  18.0         18.0 °C     
3           2018-01-08-11:30:47  3           2018-01-08 18:30:47  18.7         18.7 °C


actually it shows the different time stamp stamp and local and UTC.. so is it not correct?
switch debug on, you should see the sql statement printed in the log

I did the debug and got the same result BUT I saw on the debug the notice about using * and not getting the local time. If you look at the query I did you can still use * AND the local time on your query. That being said, once I selected just a single state it works.

Another suggestion: if you put "AS 'ts-local' or something after that formula it will give it THAT name rather than the formula name as the header. That query I did you can see where I used * and renamed the query header.

Code: Select all

   SQL-OUTPUT:                     
id          strftime('%Y-%m-%d-%H:%M:%S',ts,'localtime')  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