selectively pruning SQLogger

Posted on
Mon Oct 13, 2014 11:16 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

selectively pruning SQLogger

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

Posted on
Mon Oct 13, 2014 1:11 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: selectively pruning SQLogger

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
Attachments
Screen Shot 2014-10-13 at 2.14.16 PM.png
Screen Shot 2014-10-13 at 2.14.16 PM.png (12.15 KiB) Viewed 2735 times
Last edited by kw123 on Mon Oct 13, 2014 1:59 pm, edited 1 time in total.

Posted on
Mon Oct 13, 2014 1:39 pm
RogueProeliator offline
User avatar
Posts: 2501
Joined: Nov 13, 2012
Location: Baton Rouge, LA

Re: selectively pruning SQLogger

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

Posted on
Mon Oct 13, 2014 1:41 pm
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: selectively pruning SQLogger

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;"

Image

Posted on
Mon Oct 13, 2014 1:43 pm
RogueProeliator offline
User avatar
Posts: 2501
Joined: Nov 13, 2012
Location: Baton Rouge, LA

Re: selectively pruning SQLogger

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

Posted on
Mon Oct 13, 2014 1:58 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: selectively pruning SQLogger

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

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 3 guests