Clean the Database?

[BarryT]

Hi,

please implend a "reset" button to clean all values/history of the database, without deleting the devices/hardware.
I just want to start over, for example the power usage and the gas, but it is not possible to just "reset" the database values without a sql manager..

We going moved over to another home in December, and as so far i get new (slim) meters, i just want to start over with the power usage, gas, to get a better monitor of the usage etc..

Would be great!

[Egregius]

Just use a sql manager. How many times will you need that?
If often, create some php page that deletes entrys from database, not that hard.

This script removes values from some tables for devices that aren't used or are older than one day:

Code: Select all

<?php 
$db = new SQLite3('/volume1/@appstore/domoticz/var/domoticz.db');
$clean = strftime("%G-%m-%d %k:%M:%S",time()-86400);
$tables = array('LightingLog','Meter','Meter_Calendar','MultiMeter','MultiMeter_Calendar','Percentage','Percentage_Calendar','Rain','Rain_Calendar','Temperature','Temperature_Calendar','UV','UV_Calendar','Wind','Wind_Calendar');
foreach($tables as $table) {
    $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<br/>";
    }
    $query=$db->exec("DELETE FROM $table WHERE Date < '$clean'");
    if ($query) {
        $rows=$db->changes();
        if($rows>0) echo $rows." rows removed from $table<br/>";
    }
}

$sql = 'VACUUM;';
if(!$result = $db->exec($sql)){ die('There was an error running the query [' . $db->error . ']');}

Gives output like this:

Code: Select all

2940 rows removed from Meter
4165 rows removed from Temperature

[BarryT]

Just use a sql manager. How many times will you need that?
If often, create some php page that deletes entrys from database, not that hard.

This script removes values from some tables for devices that aren't used or are older than one day:

Code: Select all

<?php 
$db = new SQLite3('/volume1/@appstore/domoticz/var/domoticz.db');
$clean = strftime("%G-%m-%d %k:%M:%S",time()-86400);
$tables = array('LightingLog','Meter','Meter_Calendar','MultiMeter','MultiMeter_Calendar','Percentage','Percentage_Calendar','Rain','Rain_Calendar','Temperature','Temperature_Calendar','UV','UV_Calendar','Wind','Wind_Calendar');
foreach($tables as $table) {
    $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<br/>";
    }
    $query=$db->exec("DELETE FROM $table WHERE Date < '$clean'");
    if ($query) {
        $rows=$db->changes();
        if($rows>0) echo $rows." rows removed from $table<br/>";
    }
}

$sql = 'VACUUM;';
if(!$result = $db->exec($sql)){ die('There was an error running the query [' . $db->error . ']');}

Gives output like this:

Code: Select all

2940 rows removed from Meter
4165 rows removed from Temperature

Hmm, i installed nginx with sqlite3...

I'm getting, Fatal error: Class 'SQLite3' not found in /var/www/html/clean.php on line 2

[ben53252642]

I'm getting, Fatal error: Class 'SQLite3' not found in /var/www/html/clean.php on line 2

I solved that error on my system with: apt-get install php-sqlite3

Egregius thanks for the script!