Indigo Domotics

I have some devices that generate a lot of entries in the SQL data base (20,000/day). Indigo currently only offers to prune the database for ALL devices together, i.e. delete ALL entries older than..

It would be nice to have an option to
(1) select if a device or variable should be tracked in SQLlogger (e.g. some status variables get updated frequently, but there is no need to save the info into SQLogger)
(2) prune INDIVIDUAL devices/states

Or if there is a SQL statement that could do that (at least # 2), I would be a happy user.

Thanks

Karl

found http://sqlitebrowser.org/. that seems to a nice job at listing the entries and you also can change entries and delete records

QUESTION:
- I should stop INDIGO server when I change the database outside indigo and after the changes restart indigo server?
- can I just delete a single table for a device .. i.e. there is not other meta structure on top of the tables for the devices/ variables?

there seem to be only tables for the devices and variables, nothing else..


before I start deleting I like to get some feedback


thanks

Karl

Your best bet is to selectively delete data or truncate the tables of data as you desire (could be in a re-occurring script, of course). You have seen the tables are labeled by ID, so you can easily craft a SQL statement to delete prior to a given timestamp:

DELETE FROM [tablename] WHERE ts < 'YOUR TIMESTAMP HERE'

Change the timestamp to a TSQL data calculation based on the current date if you like to delete items older than X days/months/etc. If you want to clear the table completely, no need to put any qualifications on the ts column.

Note that I believe SQLite recommends using their VACUUM statement to reclaim / reorder space after a large delete, but not 100% certain on that recommendation as I am not a SQLite expert. My understanding is that it is roughly equivalent to the Compact & Repair from MS Access.

Adam

Unfortunately I would shutdown the Indigo Server first. You could also just stop the SQLogger plugin, but there isn't an API to automate/script that.

Once stopped note you can via the command line drop the tables. Something like this (or like Adam's suggested SQL above):

Code: Select all

cd /Library/Application\ Support/Perceptive\ Automation/Indigo\ 6/Logs/
sqlite3 indigo_history.sqlite "DROP TABLE TABLE_NAME_HERE;"

found http://sqlitebrowser.org/. that seems to at list list the entries nicely.

I've been happy with Sequel Pro if you want to try out more than one option and see what you like best... this is kind of like text editors, though, everyone has a different preference.

I was about to follow up, but Matt beat me to it, that SQLite doesn't handle multiple connections to a database all too well -- so like Matt said, do it without Indigo accessing the database.

Adam

ok thanks.. tried to delete a single record and that worked fine.. rest is the fun part to make it automated..
and yes, I am doing a full backup every 6 hours.. (superduper from shirt pocket).

Karl