#!/bin/bash
# Bereinigung der SQLLite DB wegen überfüllung mit vorheriger Kopie
ht3dir=/home/pi/HT3/sw/var/
database="$ht3dir"databases/HT3_db.sqlite 
backupdir="$ht3dir"backup/

service apache2 stop
service ht3_logger stop

sqlite3 -header -csv "$database" "SELECT * FROM heizgeraet WHERE Local_date_time <strftime('%Y.%m.%d','now','-0 days');" >"$backupdir"heizgeraet_`date '+%Y%m%d'`.csv
sqlite3 "$database" "DELETE FROM heizgeraet WHERE Local_date_time <strftime('%Y.%m.%d','now','-0 days');"

sqlite3 -header -csv "$database" "SELECT * FROM heizkreis1 WHERE Local_date_time <strftime('%Y.%m.%d','now','-0 days');" >"$backupdir"heizkreis1_`date '+%Y%m%d'`.csv
sqlite3 "$database" "DELETE FROM heizkreis1 WHERE Local_date_time <strftime('%Y.%m.%d','now','-0 days');"

sqlite3 -header -csv "$database" "SELECT * FROM heizkreis2 WHERE Local_date_time <strftime('%Y.%m.%d','now','-0 days');" >"$backupdir"heizkreis2_`date '+%Y%m%d'`.csv
sqlite3 "$database" "DELETE FROM heizkreis2 WHERE Local_date_time <strftime('%Y.%m.%d','now','-0 days');"

sqlite3 -header -csv "$database" "SELECT * FROM warmwasser WHERE Local_date_time <strftime('%Y.%m.%d','now','-0 days');" >"$backupdir"warmwasser_`date '+%Y%m%d'`.csv
sqlite3 "$database" "DELETE FROM warmwasser WHERE Local_date_time <strftime('%Y.%m.%d','now','-0 days');"

sqlite3 -header -csv "$database" "SELECT * FROM solar WHERE Local_date_time <strftime('%Y.%m.%d','now','-0 days');" >"$backupdir"solar_`date '+%Y%m%d'`.csv
sqlite3 "$database" "DELETE FROM solar WHERE Local_date_time <strftime('%Y.%m.%d','now','-0 days');"

sqlite3 "$database" "DELETE FROM rrdtool_infos WHERE Local_date_time <strftime('%Y.%m.%d','now','-0 days');"
sqlite3 "$database" "DELETE FROM sysdatetime WHERE Local_date_time <strftime('%Y.%m.%d','now','-0 days');"

service ht3_logger start
service apache2 start