Domoticz database

0
3722
Domoticz tutorial database

The Domoticz database

Domoticz DatabaseDomoticz saves everything in his own database. This is an SQLite database placed inside the Domoticz folder domoticz.db.
Normally you have nothing to do with this file, You only have to back up and keep it safe. The Domoticz database cleans itself.
But it also makes backups, very handy but the backups are still located on the memory card, a script to make a backup of the database and also other files and transfer it to a file share can you find here.

So this page is mentioned only for advanced users or people who have a database problem.

The database file can be opened with SQLite Database Browser which is free at http://sqlitebrowser.org.
It’s available for Mac, Windows and Linux.

Contents

Domoticz Database size and growth

Some users complained in the past that there was no free space left. The reason was a growing log file or a growing database file. So check your settings at Setup > Settings > Log History
A high number means more records means a bigger database file. Experiment for yourself what you need and monitor the size. Do you need the historical data of lot’s of devices for 300 days? Most of the times the answer is no, of course not.

Cleanup database with queries

Because my production system is now a real production system, but in the past, I have experimented a lot. Bought devices that are already gone But over the time I had some records in my database of devices I didn’t own anymore.
with the SQLLitebrowser I was able to check records of: ‘MultiMeter’,’MultiMeter_Calendar’,’Percentage’,’Percentage_Calendar’,’Rain’,’Rain_Calendar’,’Temperature’,’UV’,’UV_Calendar’,’Wind’,’Wind_Calendar’

tip: always to a select query before you actually do a delete statement like the one below:

DELETE FROM MultiMeter WHERE DeviceRowID not in (select ID from DeviceStatus where Used = 1)

Recover your malformed database

Log in with SSH and fire the commands below:

1
2
3
4
5
6
7
8
cd domoticz
sqlite3 domoticz.db
.mode insert
.output dump.sql
.dump
.exit
sqlite3 -init dump.sql repaired.db
mv repaired.db domoticz.db

Separately if you want to check a database to see if it’s corrupt you can use these commands:

1
2
3
sqlite3 domoticz.db
pragma integrity_check;
.exit

or another approach:

1
2
3
4
5
6
7
8
# Remove first line of dumped:
tail dump.sql -n +2 > dump1.sql
# Make back-up of original just in case:
mv domoticz.db domoticz.bak.db
# Import into fresh
database: sqlite3 domoticz.db < dump1.sql
# Clean-up the temporary files:
rm dump*.sql