Clean database for Domoticz

Clean database for Domoticz

I have had my Domoticz for quite some time and I noticed that the database was quite large. Even so large that the backup of that database by Domoticz itself caused it to crash. So I researched how to keep the database size down to a normal level (for reference, it was well over 650 MB in size). When I first tried to do anything to the database, I found it was corrupt. Fixing that is a whole different story which you can read here.

The Domoticz database is a SQLite3 database. You can easily open it if you have SQLite3 installed on your pi (or whatever you run Domoticz on). If you haven’t:

sudo apt-get install sqlite3

After that you can open the database by going to your Domoticz directory and executing this:

sqlite3 domoticz.db

You can verify if it is open by checking for example all tables in it:

.tables

To check whether your database is corrupt you can use:

PRAGMA integrity_check;

To exit SQLite3 you use:

.exit

As it turns out, Domoticz is not always cleaning up properly. It leaves records in the database without a parent. Also old records remain in there, even if you have set a limit in the settings tab. What I have done is taken a script I found on on the forum and updated it with some extra tables. I run this script every morning through crontab:

 /usr/bin/php /home/pi/domoticz/scripts/my_own/cleanup_db.php

The updated script:

<?php
echo "<xmp>";
echo "Cleaning up tables.\n\n";
#
# Open the database
#
$db = new SQLite3('/home/pi/domoticz/domoticz.db');
#
# Your cut off date
# 86400 seconds is one day
#
$clean = strftime("%G-%m-%d %k:%M:%S",time()-86400);
#
# All the tables I could find
#
$tables = array('MultiMeter','MultiMeter_Calendar','Percentage',
'Percentage_Calendar','Rain','Rain_Calendar','Temperature',
'Temperature_Calendar','UV','UV_Calendar','Wind','Wind_Calendar',
'Meter','Meter_Calendar','Fan','Fan_Calendar','LightingLog');
#
# Loop through all tables to clean up
#
foreach ($tables as $table) {
  echo "Table: $table\n";
  $query=$db->exec("DELETE FROM $table WHERE DeviceRowID not in
(select ID from DeviceStatus where Used = 1)");
  if ($query) {
    $rows = $db->changes();
    if ($rows>0)
      echo $rows." rows removed from $table\n";
  }
  $query=$db->exec("DELETE FROM $table WHERE Date < '$clean'");
  if ($query) {
    $rows=$db->changes();
    if ($rows>0)
      echo $rows." rows removed from $table\n";
   }
}
#
# Remove the empty space from the database
#
echo "\nStarting VACUUM\n";
$sql = 'VACUUM;';
if (!$result = $db->exec($sql)) {
  die('There was an error running
the query [' . $db->error . ']');
}
echo "Done VACUUM\n";
echo "</xmp>\n";
?>

 

Remember to check the database location and your retention in the script. This script deletes everything older than a day. If your database is large and you run this the first time, be prepared for something that could take more than 1 hour. Subsequent times should be minimal. Don’t abort!!! because you could break your database. For me, the database went from 693 MB to 6 MB. The first couple of times I ran the script on the command prompt to make sure it ran ok. Please remember to make a copy of your database BEFORE you test the script.

2 thoughts on “Clean database for Domoticz

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s