MySQL persistence kludge

Tutorials & Examples
1

In my early setup of OH2 I chose JDBC MYSQL persistence because I wanted to be able to save and restore string values (so no rrdj4) and wanted a little bit of history for my rules that use changedsince and other functions that rely on some historical data being present.

I’m also a lazy man and don’t want to have to maintain a persist file that is smarter than

* : strategy = everyChange, restoreOnStartup

so I realized the other day how ridiculously huge my DB is (persisting Sonos group xml every time it changes is a ridiculous amount of text). I know I am going to have to abandon mysql eventually (I don’t have any interest in charting) and go with mapdb for the restoreOnStartup and rrd4j for at least my numerical history. But until then I wrote a handy script that I have in a rule that runs nightly to trim the database down to the last 5 items for each item.

The Script:

#!/bin/bash

while read -a row
do
		mysql -u<username> -p<password> -DOpenHAB -se "delete from item${row[0]} where time <= (select time from (select time from item${row[0]} order by time desc limit 1 offset 5) foo )"

done < <(mysql -uopenhab -phabopen -DOpenHAB -se "select LPAD(itemid, 4, '0') as ItemId from items")

I’ve saved that as /etc/openhab2/scripts/cleandb.sh, chmodded it a+x and made sure it was owned by the openhab user and group.

then a simple rule:

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
rule "Nightly Update"
when
        Time cron "0 0 * * * ?"
then	
	executeCommandLine("/etc/openhab2/scripts/cleandb.sh")	
end

which will keep my database around 300kb, which seems a bit more reasonable.

Note:
Obviously use at your own risk, this may accidentally wipe your whole db or cause your mother to call asking for tech support.

2 Likes