DbRep - Reporting und Management von DbLog-Datenbankinhalten

DbRep
Zweck / Funktion
Reporting und Management von DbLog-Datenbankinhalten
Allgemein
Typ	Hilfsmodul
Details
Dokumentation	EN / DE
Support (Forum)	Sonstiges
Modulname	93_DbRep.pm
Ersteller	DS_Starter (Forum /Wiki)
Wichtig: sofern vorhanden, gilt im Zweifel immer die (englische) Beschreibung in der commandref!

Zweck und Einsatz des Moduls

Zweck des Moduls ist es, den Inhalt von DbLog-Datenbanken nach bestimmten Kriterien zu durchsuchen, zu managen, das Ergebnis hinsichtlich verschiedener Aggregationen auszuwerten und als Readings darzustellen. Die Abgrenzung der zu berücksichtigenden Datenbankinhalte erfolgt durch die Angabe von Device, Reading und die Zeitgrenzen für Auswertungsbeginn bzw. Auswertungsende.

Alle Datenbankoperationen werden nichtblockierend ausgeführt. Die Ausführungszeit der (SQL)-Hintergrundoperationen kann optional ebenfalls als Reading bereitgestellt werden (siehe Attribute). Alle vorhandenen Readings werden vor einer neuen Operation gelöscht. Durch das Attribut "readingPreventFromDel" kann eine Komma separierte Liste von Readings angegeben werden die nicht gelöscht werden sollen.

Zur Zeit werden folgende Operationen unterstützt:

• Selektion aller Datensätze innerhalb einstellbarer Zeitgrenzen.
• Darstellung der Datensätze einer Device/Reading-Kombination innerhalb einstellbarer Zeitgrenzen.
• Selektion der Datensätze unter Verwendung von dynamisch berechneter Zeitgrenzen zum Ausführungszeitpunkt.
• Berechnung der Anzahl von Datensätzen einer Device/Reading-Kombination unter Berücksichtigung von Zeitgrenzen und verschiedenen Aggregationen.
• Die Berechnung von Summen- , Differenz- , Maximum- , Minimum- und Durchschnittswerten von numerischen Readings in Zeitgrenzen und verschiedenen Aggregationen.
• Löschung von Datensätzen. Die Eingrenzung der Löschung kann durch Device und/oder Reading sowie fixer oder dynamisch berechneter Zeitgrenzen zum Ausführungszeitpunkt erfolgen.
• Export von Datensätzen in ein File im CSV-Format.
• Import von Datensätzen aus File im CSV-Format.
• Umbenennen von Device-Namen in Datenbanksätzen
• automatisches Umbenennen (Autorename) von Device-Namen in Datenbanksätzen und DbRep-Definitionen nach FHEM "rename" Befehl (siehe DbRep-Agent)
• Ausführen von beliebigen Benutzer spezifischen SQL-Kommandos
• Backups der FHEM-Datenbank im laufenden Betrieb erstellen (MySQL, SQLite)
• senden des Dumpfiles zu einem FTP-Server nach dem Backup
• Restore von SQLite-Dumps und MySQL serverSide-Backups
• Optimierung der angeschlossenen Datenbank (optimizeTables, vacuum)
• Ausgabe der existierenden Datenbankprozesse (MySQL)
• leeren der current-Tabelle
• Auffüllen der current-Tabelle mit einem (einstellbaren) Extrakt der history-Tabelle
• Bereinigung sequentiell aufeinander folgender Datensätze (sequentielle Dublettenbereinigung)

Zur Aktivierung der Funktion "Autorename" wird dem definierten DbRep-Device mit dem Attribut "role" die Rolle "Agent" zugewiesen. Die Standardrolle nach Definition ist "Client". Mehr ist dazu im Abschnitt DbRep-Agent beschrieben.

DbRep stellt dem Nutzer einen UserExit zur Verfügung. Über diese Schnittstelle kann der Nutzer in Abhängigkeit von frei definierbaren Reading/Value-Kombinationen (Regex) eigenen Code zur Ausführung bringen. Diese Schnittstelle arbeitet unabhängig von einer Eventgenerierung. Weitere Informationen dazu ist unter Attribut "userExitFn" beschrieben.

FHEM-Forum: Modul 93_DbRep - Reporting und Management von Datenbankinhalten (DbLog)

Voraussetzungen und Abgrenzungen

Das Modul setzt den Einsatz einer oder mehrerer DBLog-Instanzen voraus (bisher getestet mit MySQL und SQLite). Es werden die Zugangsdaten dieser Datenbankdefinition aus der Konfiguration des entsprechenden DbLog-Device genutzt. Es werden nur Inhalte der Tabelle "history" berücksichtigt (Ausnahme Kommando "sqlCmd").

Überblick welche anderen Perl-Module DbRep verwendet:

POSIX

Time::HiRes

Time::Local

Scalar::Util

DBI

Blocking (FHEM-Modul)

Time::HiRes

Encode

Aus Performancegründen sollte zusätzlich folgender Index erstellt werden:

 CREATE INDEX Report_Idx ON `history` (TIMESTAMP, READING) USING BTREE;

Wenn nicht vorhanden das DBI-Modul installieren (z.B. unter Debian):

sudo apt-get install libdbi-perl

Definition

 define <name> DbRep <Name der DbLog-instanz>

• <Name der DbLog-Instanz>: Es wird der Name der auszuwertenden DBLog-Datenbankdefinition angegeben, NICHT die Datenbank selbst.

Set

Siehe Commandref

Get

Die Get-Kommandos von DbRep dienen dazu eine Reihe von Metadaten der verwendeten Datenbankinstanz abzufragen. Dies sind zum Beispiel eingestellte Serverparameter, Servervariablen, Datenbankstatus- und Tabelleninformationen. Die verfügbaren get-Funktionen sind von dem verwendeten Datenbanktyp abhängig. So ist für SQLite z.Zt. nur "svrinfo" verfügbar. Die Funktionen liefern nativ sehr viele Ausgabewerte die über über funktionsspezifische Attribute abgrenzbar sind. Der Filter ist als kommaseparierte Liste anzuwenden. Dabei kann SQL-Wildcard (%) verwendet werden.

Hinweis:
Nach der Ausführung einer get-Funktion in der Detailsicht einen Browserrefresh durchführen um die Ergebnisse zu sehen !

• dbstatus : listet globale Informationen zum MySQL Serverstatus (z.B. Informationen zum Cache, Threads, Bufferpools, etc. ). Es werden zunächst alle verfügbaren Informationen berichtet. Mit dem Attribut "showStatus" kann die Ergebnismenge eingeschränkt werden, um nur gewünschte Ergebnisse abzurufen. Detailinformationen zur Bedeutung der einzelnen Readings sind hier verfügbar.

Bespiel:

get <name> dbstatus

attr <name> showStatus %uptime%,%qcache%

Es werden nur Readings erzeugt die im Namen "uptime" und "qcache" enthalten

• dbvars : zeigt die globalen Werte der MySQL Systemvariablen. Enthalten sind zum Beispiel Angaben zum InnoDB-Home, dem Datafile-Pfad, Memory- und Cache-Parameter, usw. Die Ausgabe listet zunächst alle verfügbaren Informationen auf. Mit dem Attribut "showVariables" kann die Ergebnismenge eingeschränkt werden um nur gewünschte Ergebnisse abzurufen. Weitere Informationen zur Bedeutung der ausgegebenen Variablen sind hier verfügbar.

Bespiel:

get <name> dbvars

attr <name> showVariables %version%,%query_cache%

Es werden nur Readings erzeugt die im Namen "version" und "query_cache" enthalten

• procinfo : listet die existierenden Datenbank-Prozesse in einer Tabelle auf (nur MySQL).

Typischerweise werden nur die Prozesse des Verbindungsusers (angegeben in DbLog-Konfiguration) ausgegeben. Sollen alle Prozesse angezeigt werden, ist dem User das globale Recht "PROCESS" einzuräumen. Für bestimmte SQL-Statements wird seit MariaDB 5.3 ein Fortschrittsreporting (Spalte "PROGRESS") ausgegeben. Zum Beispiel kann der Abarbeitungsgrad bei der Indexerstellung verfolgt werden. Weitere Informationen sind hier verfügbar.

• svrinfo : allgemeine Datenbankserver-Informationen wie z.B. die DBMS-Version, Serveradresse und Port usw. Die Menge der Listenelemente ist vom Datenbanktyp abhängig. Mit dem Attribut "showSvrInfo" kann die Ergebnismenge eingeschränkt werden. Weitere Erläuterungen zu den gelieferten Informationen sind hier zu finden.

Bespiel:

get <name> svrinfo

attr <name> showSvrInfo %SQL_CATALOG_TERM%,%NAME%

Es werden nur Readings erzeugt die im Namen "SQL_CATALOG_TERM" und "NAME" enthalten

• tableinfo : ruft Detailinformationen der in einem MySQL-Schema angelegten Tabellen ab. Die ausgewerteten Schemata sind abhängig von den Rechten des verwendeten Datenbankusers (default: das DB-Schema der current/history-Tabelle). Mit dem Attribut "showTableInfo" können die Ergebnisse eingeschränkt werden. Erläuterungen zu den erzeugten Readings sind hier zu finden.

Bespiel:

get <name> tableinfo

attr <name> showTableInfo current,history

Es werden nur Information der Tabellen current,history angezeigt

Attribute

Über die modulspezifischen Attribute wird die Abgrenzung der Auswertung und die Aggregation der Werte gesteuert.

Hinweis zur SQL-Wildcard Verwendung:
Innerhalb der Attribut-Werte für "device" und "reading" können SQL-Wildcard "%" angegeben werden. Das Zeichen "_" wird als SQL-Wildcard nicht unterstützt. Dabei ist "%" = beliebig viele Zeichen.
Dies gilt für alle Funktionen außer "insert", "importFromFile" und "deviceRename".
Die Funktion "insert" erlaubt nicht dass die genannten Attribute das Wildcard "%" enthalten. Character "_" wird als normales Zeichen gewertet.
In Ergebnis-Readings wird das Wildcardzeichen "%" durch "/" ersetzt um die Regeln für erlaubte Zeichen in Readings einzuhalten.

• aggregation  : Zusammenfassung der Device/Reading-Selektionen in Stunden,Tages,Kalenderwochen,Kalendermonaten oder "no". Liefert z.B. die Anzahl der DB-Einträge am Tag (countEntries), Summation von Differenzwerten eines Readings (sumValue), usw. Mit Aggregation "no" (default) erfolgt keine Zusammenfassung in einem Zeitraum sondern die Ausgabe ergibt alle Werte eines Device/Readings zwischen den definierten Zeiträumen.

• allowDeletion  : schaltet die Löschfunktion des Moduls frei

• device  : Abgrenzung der DB-Selektionen auf ein bestimmtes Device

• diffAccept  : gilt für Funktion diffValue. diffAccept legt fest bis zu welchem Schwellenwert eine berechnete positive Werte-Differenz zwischen zwei unmittelbar aufeinander folgenden Datensätzen akzeptiert werden soll (Standard ist 20). Damit werden fehlerhafte DB-Einträge mit einem unverhältnismäßig hohen Differenzwert von der Berechnung ausgeschlossen und verfälschen nicht das Ergebnis. Sollten Schwellenwertüberschreitungen vorkommen, wird das Reading "diff-overrun_limit-<diffLimit>" erstellt. (<diffLimit> wird dabei durch den aktuellen Attributwert ersetzt) Es enthält eine Liste der relevanten Wertepaare. Mit verbose 3 werden diese Datensätze ebenfalls im Logfile protokolliert.

Beispiel einer Ausgabe im Logfile beim Überschreiten von diffAccept=10:

DbRep Rep.STP5000.etotal -> data ignored while calc diffValue due to threshold overrun (diffAccept = 10): 2016-04-09 08:50:50 0.0340 -> 2016-04-09 12:42:01 13.3440

Der erste Datensatz mit einem Wert von 0.0340 ist untypisch gering zum nächsten Wert 13.3440 und führt zu einem zu hohen Differenzwert.

Es ist zu entscheiden ob der Datensatz gelöscht, ignoriert, oder das Attribut diffAccept angepasst werden sollte.

• disable  : deaktiviert das Modul

• dumpComment : User-Kommentar. Er wird im Kopf des durch den Befehl "dumpMyQL clientSide" erzeugten Dumpfiles eingetragen.

• dumpDirLocal : Zielverzeichnis für die Erstellung von Dumps mit "dumpMySQL clientSide". default: "{global}{modpath}/log/" auf dem FHEM-Server.

Ebenfalls werden in diesem Verzeichnis alte Backup-Files durch die interne Versionsverwaltung von "dumpMySQL" gesucht und gelöscht wenn die gefundene Anzahl den Attributwert "dumpFilesKeep" überschreitet. Das Attribut dient auch dazu ein lokal gemountetes Verzeichnis "dumpDirRemote" DbRep bekannt zu machen.

• dumpDirRemote : Zielverzeichnis für die Erstellung von Dumps mit "dumpMySQL serverSide". default: das Home-Dir des MySQL-Servers auf dem MySQL-Host

• dumpMemlimit - erlaubter Speicherverbrauch für SQL-Script zur Generierungszeit (default: 100000 Zeichen). Bitte den Parameter anpassen, falls es zu Speicherengpässen und damit verbundenen Performanceproblemen kommen sollte.

• dumpSpeed - Anzahl der abgerufenen Zeilen aus der Quelldatenbank (default: 10000) pro Select durch "dumpMySQL ClientSide". Dieser Parameter hat direkten Einfluß auf die Laufzeit und den Ressourcenverbrauch zur Laufzeit.

• dumpFilesKeep : Es wird die angegeben Anzahl Dumpfiles im Dumpdir gelassen (default: 3). Sind mehr (ältere) Dumpfiles vorhanden, werden diese gelöscht nachdem ein neuer Dump erfolgreich erstellt wurde. Das globale Attribut "archivesort" wird berücksichtigt.

• executeAfterDump : Es kann ein FHEM-Kommando angegeben werden welches nach dem Dump ausgeführt werden soll. Funktionen sind in {} einzuschließen.

Beispiel:

attr <DbRep-device> executeAfterDump set og_gz_westfenster off;

attr <DbRep-device> executeAfterDump {adump ("<DbRep-device>")}

"adump" ist eine in 99_myUtils definierte Funktion.

    sub adump {
        my ($name) = @_;
        my $hash = $defs{$name};
        # die eigene Funktion, z.B.
        Log3($name, 3, "DbRep $name -> Dump ist beendet");
     
        return;
    }

• executeBeforeDump : Es kann ein FHEM-Kommando angegeben werden welches vor dem Dump ausgeführt werden soll. Funktionen sind in {} einzuschließen.

Beispiel:

attr <DbRep-device> executeBeforeDump set og_gz_westfenster on;

attr <DbRep-device> executeBeforeDump {bdump ("<DbRep-device>")}

"bdump" ist eine in 99_myUtils definierte Funktion.

    sub bdump {
        my ($name) = @_;
        my $hash = $defs{$name};
        # die eigene Funktion, z.B.
        Log3($name, 3, "DbRep $name -> Dump startet");
     
        return;
    }

• expimpfile  : Pfad/Dateiname für Export/Import in/aus einem File.

• limit : begrenzt die Anzahl der resultierenden Datensätze im select-Statement von "fetchrows" (default 1000). Diese Limitierung soll eine Überlastung der Browsersession und ein blockieren von FHEMWEB verhindern. Bei Bedarf entsprechend ändern bzw. die Selektionskriterien (Zeitraum der Auswertung) anpassen.

• optimizeTablesBeforeDump - wenn "1", wird vor dem Datenbankdump eine Tabellenoptimierung ausgeführt (default: 0). Dadurch verlängert sich die Laufzeit des Dump.

Hinweis

Die Tabellenoptimierung führt zur Sperrung der Tabellen und damit zur Blockierung von FHEM falls DbLog nicht im asynchronen Modus (DbLog-Attribut "asyncMode") betrieben wird !

• reading  : Abgrenzung der DB-Selektionen auf ein bestimmtes Reading

• readingNameMap  : der Name des ausgewerteten Readings wird mit diesem String für die Anzeige überschrieben

• readingPreventFromDel  : Komma separierte Liste von Readings die vor einer neuen Operation nicht gelöscht werden sollen

• role  : die Rolle des DbRep-Device. Standard ist "Client". Die Rolle "Agent" ist im Abschnitt DbRep-Agent beschrieben.

• showproctime  : wenn gesetzt, zeigt das Reading "sql_processing_time" die benötigte Abarbeitungszeit (in Sekunden) für die SQL-Ausführung der durchgeführten Funktion. Dabei wird nicht ein einzelnes SQl-Statement, sondern die Summe aller notwendigen SQL-Abfragen innerhalb der jeweiligen Funktion betrachtet.

• showStatus  : grenzt die Ergebnismenge des Befehls "get ... dbstatus" ein. Es können SQL-Wildcard (%) verwendet werden.

Bespiel:

attr <device> showStatus %uptime%,%qcache%

Es werden nur Readings erzeugt die im Namen "uptime" und "qcache" enthalten

• showVariables  : grenzt die Ergebnismenge des Befehls "get ... dbvars" ein. Es können SQL-Wildcard (%) verwendet werden.

Bespiel:

attr <device> showVariables %version%,%query_cache%

Es werden nur Readings erzeugt die im Namen "version" und "query_cache" enthalten

• showSvrInfo  : grenzt die Ergebnismenge des Befehls "get ... svrinfo" ein. Es können SQL-Wildcard (%) verwendet werden.

Bespiel:

attr <device> showSvrInfo %SQL_CATALOG_TERM%,%NAME%

Es werden nur Readings erzeugt die im Namen "SQL_CATALOG_TERM" und "NAME" enthalten

• showTableInfo  : grenzt die Ergebnismenge des Befehls "get ... tableinfo" ein. Es können SQL-Wildcard (%) verwendet werden.

Bespiel:

attr <device> showTableInfo current,history

Es werden nur Information der Tabellen "current" und "history" angezeigt

• sqlResultFormat : legt die Formatierung des Ergebnisses des Kommandos "set ... sqlCmd" fest. Mögliche Optionen sind:

• separated - die Ergebniszeilen werden als einzelne Readings fortlaufend generiert. (default)

• mline - das Ergebnis wird als Mehrzeiler im Reading SqlResult dargestellt. Feldtrenner ist "|".

• sline - das Ergebnis wird als Singleline im Reading SqlResult dargestellt. Feldtrenner ist "|", Satztrenner ist"]|[".

• table - das Ergebnis wird als Tabelle im Reading SqlResult dargestellt.

• json - erzeugt das Reading SqlResult als JSON-kodierten Hash. Jedes Hash-Element (Ergebnissatz) setzt sich aus der laufenden Nummer des Datensatzes (Key) und dessen Wert zusammen.

Die Weiterverarbeitung des Ergebnisses kann z.B. mit der folgenden userExitFn in 99_myUtils.pm erfolgen:

                sub resfromjson {
                  my ($name,$reading,$value) = @_;
                  my $hash   = $defs{$name};

                  if ($reading eq "SqlResult") {
                    # nur Reading SqlResult enthält JSON-kodierte Daten
                    my $data = decode_json($value);
        	      
        		    foreach my $k (keys(%$data)) {
        		      
        			  # ab hier eigene Verarbeitung für jedes Hash-Element 
        		      # z.B. Ausgabe jedes Element welches "Cam" enthält
        		      my $ke = $data->{$k};
        		      if($ke =~ m/Cam/i) {
        		        my ($res1,$res2) = split("\\|", $ke);
                        Log3($name, 1, "$name - extract element $k by userExitFn: ".$res1." ".$res2);
        		      }
        	        }
                  }
                return;
                }

• timestamp_begin  : der zeitliche Beginn für die Datenselektion (*)

• timestamp_end  : das zeitliche Ende für die Datenselektion. Wenn nicht gesetzt wird immer die aktuelle Datum/Zeit-Kombi für das Ende der Selektion eingesetzt. (*)

(*) Das Format von Timestamp ist wie in DbLog "YYYY-MM-DD HH:MM:SS". Für die Attribute "timestamp_begin", "timestamp_end" kann ebenso eine der folgenden Eingaben verwendet werden:

• current_year_begin  : belegt das timestamp-Attribut dynamisch mit "<aktuelles Jahr>-01-01 00:00:00"
• current_year_end  : belegt das timestamp-Attribut dynamisch mit "<aktuelles Jahr>-12-31 23:59:59"
• previous_year_begin  : belegt das timestamp-Attribut dynamisch mit "<voriges Jahr>-01-01 00:00:00"
• previous_year_end  : belegt das timestamp-Attribut dynamisch mit "<voriges Jahr>-12-31 23:59:59"
• current_month_begin  : "aktueller Monat erster Tag 00:00:00"
• current_month_end  : "aktueller Monat letzter Tag 23:59:59"
• previous_month_begin  : "Vormonat erster Tag 00:00:00"
• previous_month_end  : "Vormonat letzter Tag 23:59:59"
• current_week_begin  : "erster Tag der akt. Woche 00:00:00"
• current_week_end  : "letzter Tag der akt. Woche 23:59:59"
• previous_week_begin  : "erster Tag Vorwoche 00:00:00"
• previous_week_end  : "letzter Tag Vorwoche 23:59:59"
• current_day_begin  : "aktueller Tag 00:00:00"
• current_day_end  : "aktueller Tag 23:59:59"
• previous_day_begin  : "Vortag 00:00:00"
• previous_day_end  : "Vortag 23:59:59"
• current_hour_begin  : "aktuelle Stunde:00:00"
• current_hour_end  : "aktuelle Stunde:59:59"
• previous_hour_begin  : "vorherige Stunde:00:00"
• previous_hour_end  : "vorherige Stunde:59:59"

Natürlich sollte man immer darauf achten dass timestamp_begin < timestamp_end ist.

Hinweis

Wird das Attribut "timeDiffToNow" gesetzt, werden die evtentuell gesetzten Attribute "timestamp_begin" bzw. "timestamp_end" gelöscht.

Das Setzen von "timestamp_begin" bzw. "timestamp_end" bedingt die Löschung von Attribut "timeDiffToNow", wenn gesetzt.

• timeDiffToNow  : der Selektionsbeginn wird auf den Zeitpunkt "aktuelle Zeit - timeDiffToNow" gesetzt (in Sekunden). Dadurch werden immer die letzten <timeDiffToNow>-Sekunden berücksichtigt (z.b. 86400 wenn immer die letzten 24 Stunden in die Selektion eingehen sollen). Die Timestampermittlung erfolgt dynamisch zum Ausführungszeitpunkt.

• timeOlderThan  : das Selektionsende wird auf den Zeitpunkt "aktuelle Zeit - timeOlderThan" gesetzt (in Sekunden). Dadurch werden alle Datensätze bis zu dem Zeitpunkt "<aktuelle Zeit> - <timeOlderThan>" berücksichtigt (z.b. wenn auf 86400 gesetzt werden alle Datensätze die älter als ein Tag sind berücksichtigt). Die Timestampermittlung erfolgt dynamisch zum Ausführungszeitpunkt.

• timeout  : das Attribut setzt den Timeout-Wert für die Blocking-Call Routinen (Standard 86400) in Sekunden

• userExitFn  : stellt eine Schnittstelle zur Ausführung eigenen Usercodes zur Verfügung

Um die Schnittstelle zu aktivieren, wird zunächst die aufzurufende Subroutine in 99_myUtls.pm nach folgendem Muster erstellt:

sub UserFunction {
 my ($name,$reading,$value) = @_;
 my $hash = $defs{$name};
 ...
 # z.B. übergebene Daten loggen
 Log3 $name, 1, "UserExitFn $name called - transfered parameter are Reading: $reading, Value: $value " ;
 ...

return;
}

Die Aktivierung der Schnittstelle erfolgt durch Setzen des Funktionsnames im Attribut.

Optional kann ein Reading:Value Regex als Argument angegeben werden. Wird kein Regex angegeben, sind alle Wertekombinationen wahr (entspricht .*:.*).

Beispiel:

attr <device> userExitFn UserFunction .*:.*

"UserFunction" ist die Subroutine in 99_myUtils.pm.

Grundsätzlich arbeitet die Schnittstelle OHNE Eventgenerierung. Sofern das Attribut gesetzt ist, erfolgt Die Regexprüfung NACH der Erstellung eines Readings. Ist die Prüfung WAHR, wird die angegebene Funktion aufgerufen. Zur Weiterverarbeitung werden der aufgerufenenen Funktion die folgenden Variablen übergeben:

• $name - der Name des DbRep-Devices
• $reading - der Namen des erstellen Readings
• $value - der Wert des Readings

Readings

Abhängig von der ausgeführten DB-Operation werden die Ergebnisse in entsrechnden Readings dargestellt. Zu Beginn einer neuen Operation werden alle alten Readings einer vorangegangenen Operation gelöscht um den Verbleib unpassender bzw. ungültiger Readings zu vermeiden.

Zusätzlich werden folgende Readings erzeugt:

• state  : enthält den aktuellen Status der Auswertung. Wenn Warnungen auftraten (state = Warning) vergleiche Readings "diff-overrun_limit-<diffLimit>" und "not_enough_data_in_period"

• errortext  : Grund eines Fehlerstatus

• background_processing_time : die gesamte Prozesszeit die im Hintergrund/Blockingcall verbraucht wird

• sql_processing_time  : der Anteil der Prozesszeit die für alle SQL-Statements der ausgeführten Operation verbraucht wird

• diff-overrun_limit-<diffLimit> : enthält eine Liste der Wertepaare die eine durch das Attribut "diffAccept" festgelegte Differenz <diffLimit> (Standard: 20) überschreiten. Gilt für Funktion "diffValue".

• less_data_in_period  : enthält eine Liste der Zeitperioden in denen nur ein einziger Datensatz gefunden wurde. Die Differenzberechnung berücksichtigt den letzten Wert der Vorperiode. Gilt für Funktion "diffValue".

DbRep Agent - automatisches Ändern von Device-Namen in Datenbanken und DbRep-Definitionen nach FHEM "rename"

Mit dem Attribut "role" wird die Rolle des DbRep-Device festgelegt. Die Standardrolle ist "Client". Mit der Änderung der Rolle in "Agent" wird das Device veranlasst auf Umbenennungen von Geräten in der FHEM Installation zu reagieren.

Durch den DbRep-Agenten werden folgende Features aktiviert wenn ein Gerät in FHEM mit "rename" umbenannt wird:

• in der dem DbRep-Agenten zugeordneten Datenbank (Internal Database) wird nach Datensätzen mit dem alten Gerätenamen gesucht und dieser Gerätename in allen betroffenen Datensätzen in den neuen Namen geändert.

• in dem DbRep-Agenten zugeordneten DbLog-Device wird in der Definition das alte durch das umbenannte Device ersetzt. Dadurch erfolgt ein weiteres Logging des umbenannten Device in der Datenbank.

• in den existierenden DbRep-Definitionen vom Typ "Client" wird ein evtl. gesetztes Attribut "device = alter Devicename" in "device = neuer Devicename" geändert. Dadurch werden Auswertungsdefinitionen bei Geräteumbenennungen automatisch konsistent gehalten.

Mit der Änderung in einen Agenten sind folgende Restriktionen verbunden, die mit dem Setzen des Attributes "role = Agent" eingeschaltet und geprüft werden:

• es kann nur einen Agenten pro Datenbank in der FHEM-Installation geben. Ist mehr als eine Datenbank mit DbLog definiert, können ebenso viele DbRep-Agenten eingerichtet werden

• mit der Umwandlung in einen Agenten wird nur noch das Set-Komando "renameDevice" verfügbar sein sowie nur ein eingeschränkter Satz von DbRep-spezifischen Attributen zugelassen. Wird ein DbRep-Device vom bisherigen Typ "Client" in einen Agenten geändert, werden evtl. gesetzte und nun nicht mehr zugelassene Attribute glöscht.

Die Aktivitäten wie Datenbankänderungen bzw. Änderungen an anderen DbRep-Definitionen werden im Logfile mit verbose=3 protokolliert. Damit die renameDevice-Funktion bei großen Datenbanken nicht in ein timeout läuft, sollte das Attribut "timeout" entsprechend dimensioniert werden. Wie alle Datenbankoperationen des Moduls wird auch das Autorename nonblocking ausgeführt.

Beispiel für die Definition eines DbRep-Device als Agent:

define Rep.Agent DbRep LogDB
attr Rep.Agent devStateIcon connected:10px-kreis-gelb .*disconnect:10px-kreis-rot .*done:10px-kreis-gruen
attr Rep.Agent icon security
attr Rep.Agent role Agent
attr Rep.Agent room DbLog
attr Rep.Agent showproctime 1
attr Rep.Agent stateFormat { ReadingsVal("$name","state", undef) eq "running" ? "renaming" : ReadingsVal("$name","state", undef). " » ProcTime: ".ReadingsVal("$name","sql_processing_time", undef)." sec"}
attr Rep.Agent timeout 86400

Praxisbeispiele / Hinweise und Lösungsansätze für verschiedene Aufgaben

Definieren eines DbRep-Devices

Das DbRep-Device wird bei der Definition mit der DbLog-Instanz verbunden, in deren angeschlossener Datenbank später die Auswertungen und Operationen stattfinden sollen. Es ist also nicht die Datenbank selbst, sondern das vorher definierte DbLog-Device anzugeben. Die Definition erfolgt z.B. durch:

define Rep.Energy DbRep LogDB       #LogDB ist das zu verbindende DbLog-Device

Bei der Definition werden die Zugangsdaten aus der DbLog-Instanz gelesen und das DbRep-Device mit der Datenbank verbunden. Nach der Definition ist der Status "initialized". Nach ca. 5 Sekunden wechselt er nach "connected" sofern die Verbindung zur Datenbank erfolgreich war. Zu welcher Datanbank das DbRep-Device sich verbunden hat, zeigt das Internal DATABASE.

Damit ist das DbRep-Device grundsätzlich einsatzbereit, aber noch nicht praxistauglich. Werden keine weiteren Eingrenzungen angegeben, kann mit dem so definierten Device mit dem Befehl

set Rep.Energy countEntries

die gesamte Anzahl der Datensätze in der Datenbank ermittelt werden. Für eine weitere Verwendung sind weitere Attribute zu setzen. Um die Funktionen von "Rep.Energy" nur auf z.B. Datensätze in der Datenbank anzuwenden die "STP_5000" im Feld "DEVICE" enthalten, wird das Attribut:

attr Rep.Energy device STP_5000

gesetzt. Weitere Begrenzungen der gerätespezifischen Selektion erfolgt durch das Attribut "reading". So wird durch

attr Rep.Energy reading etotal

festgelegt, dass sich die (allermeisten) Operationen auf die Kombination aus dem "device STP_5000" und dem "reading etotal" beziehen. Eine zeitliche Eingrenzung der Ergebnisse erfolgt durch die Attribute "timeDiffToNow", "timeOlderThan", "timestamp_begin", "timestamp_end". In dem Beispiel sollen sich die Selektionsergebnisse immer auf die letzten 120 Minuten beziehen. Dazu wird das Attribut

attr Rep.Energy timeDiffToNow 7200    #Der Wert für timeDiffToNow ist in Sekunden anzugeben

gesetzt. Es wird dynamisch bei jeder Operation "<Selektionsbeginn> = <aktuelle Zeit> - 3600s" berechnet und die Datensätze bis zu <aktuelle Zeit> berücksichtigt. Die gesamten Datenbankoperationen und teilweise auch Auswertungen von Selektionen erfolgt mit Blockingcall im Hintergrund. Der Timeout für die Operationen ist per Default auf 86400 Sekunden gesetzt. Über das Attribut "timeout" kann es den eigenen Bedingungen angepasst werden. In dem Beispiel wird timeout auf 300s geändert um auch bei sehr großen Selektionen und Auswertungen nicht in einen timeout zu laufen.

attr Rep.Energy timeout 300

Um auch die Verarbeitungszeiten im Hintergrund als Reading anzeigen zu lassen wird mit mit dem Attribut

attr Rep.Energy showproctime 1

erreicht. Mit diesen Einstellungen ist das Device für den konfiguriert und man kann sich zum Beispiel mit

set Rep.Energy fetchrows

die Datensätze mit der Gesamtenergierzeugung "etotal" des Wechselrichters "STP_5000" die in den letzten 2 Stunden in die DB geschrieben wurden. Nachdem der Befehl in der Gerätedetailsicht ausgeführt wurde, wechselt der state im DeviceOverview auf "running". Sobald im DeviceOverview "done" angezeigt wird sieht man die Ergebnisse nach einem Browserrefresh. Bei der Ausführung einer erneuten Operation werden alle Readings gelöscht. Sollen bestimmte Readings davon ausgenommen werden, kann dem Attribut eine kommaseparierte Liste von zu schützenden Readings übergeben werden.

Allgemein wird empfohlen sich für jede Aufgabe eine separates DbRep-Device anzulegen und entsprechend zu konfigurieren anstatt die Einstellungen ständig den neuen Aufgaben anzupassen. Um den Prozess zu vereinfachen, kann das einmal angelegte Device für eine neue Selektionsaufgabe (zum Beispiel die Datensätze eines SMA Energymeters anzuzeigen bzw. auszuwerten) auf ein neues DbRep-Device kopiert

copy Rep.Energy Rep.SMAMeter

und entsprechend angepasst werden.

Hinweis zur Eventgenerierung:
Je nach genutzter Funktion können sehr viele Readings als Ergebnis generiert werden. Zum Beispiel können mit "fetchrows", wobei jedes Reading einer selektierte Zeile aus der Datenbank entspricht, durchaus mehrere hundert Readings entstehen. Sollte man nicht durch die Attribute "event-on-update-reading" bzw. "event-on-change-reading" die Eventerzeugung auf nur relevante Readings begrenzt haben, können in diesem Fall ebenso viele Events enstehen die das System entsprechend belasten können.
Deswegen wird empfohlen die Eventerzeugung sofort auf z.B. "state" zu begrenzen.

Datensätze (Devices) von einer Datenbank in eine andere umziehen (Export/Import)

Zweck

Oft ist es so, dass man zunächst eine DbLog-Datenbank anlegt um alle zu loggenden Events dort aufzuzeichnen. Mit zunehmender Größe bzw. mit dem Wunsch nach einer höheren Strukturierung werden weitere DbLog-Instanzen angelegt. Die bereits geschriebenen Datensätze eines Gerätes müssen nun in eine andere Datenbank verlagert werden, weil das Device nunmehr in dieser Datenbank geloggt und ausgewertet werden soll.

Für das Beispielszenario gilt folgendes:

• Quelldatenbank aus der das Device entfernt werden soll ist "fhem" mit der DbLog-Instanz "LogDB"
• Zieldatenbank ist "fhemshort" mit der DbLog-Instanz "LogDBShort"
• das umzuziehende Device ist "MelderCP1"
• das definierte DbRep-Device ist "Rep.MelderCP1"

Vorbereitung

Zunächst wird ein DbRep-Device definiert (falls es noch nicht existiert) und für den Verwendungszweck vorbereitet (zur Definition siehe Definieren eines DbRep-Devices).

Das Attribut "reading" bzw. eventuell gesetzte Attribute zur Zeiteingrenzung werden gelöscht, damit alle Datensätze des Devices erfasst werden können. Für den bevorstehenden Export wird mit dem Attribut "expimpFile" der (beschreibbare) Pfad und Dateiname festgelegt, hier im Beispiel ist es "/media/sf_Backup_FHEM/meldercp1.txt".

Anzahl Einträge von MelderCP1

Mit

set Rep.MelderCP1 countEntries

kann man sich nun einen Überblick verschaffen wieviel Datensätze in der Quelldatenbank für "MelderCP1" enthalten sind und wieviel auch exportiert werden müssen. In dem Beispiel sind es, wie in dem Screenshot zu sehen, 1144 Datensätze.

Falls noch nicht geschehen, wird in der bisherigen DbLog-Definition das zu loggende Device entfernt und in der neuen DbLog-Definition aufgenommen damit nun keine neuen Datensätze mehr für "MelderCP1" in die bisherige Datenbank geschrieben werden. Hierzu siehe die Commandref zu DbLog.

Export

Nun werden die Datensätze des Devices "MelderCP1" mit dem folgenden set-Kommando in die Datei "/media/sf_Backup_FHEM/meldercp1.txt" exportiert.

set Rep.MelderCP1 exportToFile

Nach dem Export sollte natürlich im Reading von "Rep.MelderCP1" die gleiche Anzahl von exportierten Datensätzen ausgegeben werden wie vorher mit "countEntries" ermittelt wurde (siehe Screenshot).

Anzahl exportierter Dataensätze

In der Export-Datei sind nun die Datensätze im CVS-Format enthalten.

Ein Ausschnitt:

............
"2016-07-04 17:32:03","MelderCP1","FS20","on-old-for-timer 60","state","on-old-for-timer 60",""
"2016-07-05 07:47:50","MelderCP1","FS20","on-old-for-timer 60","state","on-old-for-timer 60",""
"2016-07-05 08:27:57","MelderCP1","FS20","on-old-for-timer 60","state","on-old-for-timer 60",""
"2016-07-05 08:28:25","MelderCP1","FS20","on-old-for-timer 60","state","on-old-for-timer 60",""
"2016-07-05 10:23:42","MelderCP1","FS20","on-old-for-timer 60","state","on-old-for-timer 60",""
"2016-07-05 10:27:35","MelderCP1","FS20","on-old-for-timer 60","state","on-old-for-timer 60",""
"2016-07-05 17:26:30","MelderCP1","FS20","on-old-for-timer 60","state","on-old-for-timer 60",""
"2016-07-06 07:46:31","MelderCP1","FS20","on-old-for-timer 60","state","on-old-for-timer 60",""
"2016-07-06 11:24:04","MelderCP1","FS20","on-old-for-timer 60","state","on-old-for-timer 60",""
"2016-07-06 11:25:43","MelderCP1","FS20","on-old-for-timer 60","state","on-old-for-timer 60",""
"2016-07-06 11:26:52","MelderCP1","FS20","on-old-for-timer 60","state","on-old-for-timer 60",""
...........

In der bisherigen Datenbank "fhem" können die Datensätze nun gelöscht werden. Dazu versehen wir das DbRep-Device "Rep.MelderCP1" mit dem Attribut "allowDeletion" um die Löschfunktion des Moduls freizuschalten. Nun werden die vorher exportierten Datensätze gelöscht mit:

Anzahl gelöschter Datensätze

set Rep.MelderCP1 delEntries

Auch jetzt ist wieder sicherzustellen dass in der Ausgabe der gelöschten Rows dieselbe Anzahl (1144) der exportierten Datensätze enthalten ist.

Import

Im nächsten Schritt werden die exportierten Daten in die neue Datenbank importiert. Dazu wird in unserem DbRep-Device "Rep.MelderCP1" zunächst das "allowDeletion"-Attribut sicherheitshalber gelöscht. Zur Umstellung des DbRep-Devices auf die neue Datenbank wird die Definition von "Rep.MelderCP1" editiert und dort die neue DbLog-Instanz "LogDBShort" angegeben.

modify Rep.MelderCP1 LogDBShort

Nach einem kurzen Moment wird der state von "Rep.MelderCP1" sich wieder von "initialized" auf "connected" ändern sofern die Umstellung funktioniert hat. Mit einem Vergleichslauf durch

set Rep.MelderCP1 countEntries

wird ersichtlich, dass sich in dieser Datenbank noch keine (oder vllt. bereits neu geloggte) Einträge von "MelderCP1" befinden.

Anzahl Einträge von MelderCP1 in neuer Datenbank nach Umstellung

Nun kann der Import erfolgen. Mit

set Rep.MelderCP1 importFromFile

werden die Daten aus der immer noch im Attribut "expimpFile" hinterlegten Datei "/media/sf_Backup_FHEM/meldercp1.txt" in die Datenbank "fhemshort" importiert. Dieser Vorgang wird als Transaktion ausgeführt. Es werden immer alle Daten oder, im Falle eines Fehlers, KEINE Daten importiert. Damit ist sichergestellt dass der Datenimport immer einen definierten Zustand hat. Sollte der Datenimport sehr umfangreiche Datensätze enthalten und somit schon absehbar sein dass der voreingestellte timeout-Wert von 86400 Sekunden nicht ausreichen wird, kann man vorsorglich das Attribut "timeout" höher setzen, z.B. auf das Doppelte).

Hier in dem Beispiel sind es lediglich 1144 Datensätze die sehr schnell importiert werden. Somit ist keine Anpassung des timeout-Parameters notwendig. Auch nach dem Import wird wieder die Anzahl der verarbeiteten Dataensätze ausgegeben. Sie sollte natürlich ebenfalls mit den vorab ermittelten Zahlen der exportierten Datensätze übereinstimmen.

Anzahl importierter Datensätze

Abschluss

Nach dem erfolgreichen Import sollte das Attribut "expimpFile" wieder entfernt werden damit man nicht versehentlich einen erneuten Import durchführt. Das verwendete Rep.MelderCP1 kann nun wieder mit Zeitbegrenzungsattributen usw. versehen werden um die ursprüngliche Verwendung wieder herzustellen.

Die dargestellte Prozedur stellt einen Komplettumzug eines Devices dar, kann jedoch durch die Angabe der bekannten Attribute auch auf bestimmte Readings in der Datenbank oder Zeitgrenzen eingeschränkt werden. Dadurch würden nur bestimmte Datensätze exportiert und wieder importiert werden. Weiterhin sollte man daran denken die vorhandenen Auswertungsszenarien mit DbRep-Devices bzw. SVG-Diagrammen usw. auch auf die neue Datanbank umzustellen.

Ermittlung und Darstellung der täglichen Energieerzeugung eines Wechselrichters

Gegeben sei dass die Energiedaten eines Wechselrichters (STP_5000) mit SMAUtils in Verbindung mit SBFSpot in die Datenbank geschrieben werden. Neben vielen anderen Werten liefert SMAUtils die Tageserzeugung in kWh als Reading "etoday". Der Wert dieses Readings wird alle paar Minuten in der Datenbank gespeichert.

Zur Auswertung wird ein DbRep-Device angelegt wie unter Definieren eines DbRep-Devices) beschrieben (z.B. Rep.etoday.STP_5000).

Rep.STP_5000 konfiguriert

Es soll die täglich erzeugte Energiemenge beginnend ab dem 01.10.2016 0 Uhr bis zum 13. Oktober angezeigt werden.

attr Rep.etoday.STP_5000 timestamp_begin 2016-10-01 00:00:00
attr Rep.etoday.STP_5000 timestamp_end 2016-10-13 23:59:59

Da es sich um eine tägliche Aggregation handelt (es soll pro Auswertung der Tag von 00:00:00 bis 23:59:59 betrachtet werden) und die Hintergrundverarbeitungszeit dargestellt werden soll, wird eingestellt:

attr Rep.etoday.STP_5000 aggregation day
attr Rep.etoday.STP_5000 showproctime 1

Die Eingrenzung der auszuwertenden Devices und der dazu gehörigen Readings wird durch die entsprechenden Attribute gewährleistet. Es soll nur das Device "STP_5000" mit dem Reading "etoday" berücksichtigt werden. Dazu stellen wir ein:

attr Rep.etoday.STP_5000 reading etoday
attr Rep.etoday.STP_5000 device STP_5000

DbRep ist nun grundsätzlich zur Auswertung konfiguriert (siehe Screenshot).

Zur Selektion und Berechnung von numerischen Daten stehen die Funktionen average-, max-, min-, sum- und diffValue zur Verfügung. Bei den in diesem Beispiel verwendeten Daten handelt es sich um einen täglich neu erzeugten und über 24 Stunden stetig steigenden Wert. Demnach ist für diese Art Daten die Funktion "maxValue" geeignet. Sie gibt den maximalen Wert des Readings im eingestellten Aggregationszeitraum (day) aus.

Rep.STP_5000 maxValue

Die Berechnung wird ausgeführt durch:

set Rep.etoday.STP_5000 maxValue

Danach kurzer Zeit ist die Berechnung erfolgt (state=done in DeviceOverview). Nach einem Browserrefresh in der Detailansicht sind die erzeugten Readings sichtbar.

Jedes Reading hat einen bestimmten Aufbau. Die Funktion "maxValue" erzeugt Readings folgenden Aufbaus.

2016-10-07_18-19-03__STP_5000__etoday__MAX__2016-10-07

Zunächst wird der Timestring "2016-10-07_18-19-03" ausgegeben der in diesem Kontext den höchsten (d.h. letzten) Wert von "etotal" an dem Tag markiert. Bei dem Wechselrichter ist es damit auch der Zeitpunkt zu dem die Energieerzeugung eingestellt wurde (in dem Fall 07.10.2016 um 18:19:03).

Anmerkung: Die Notation des Timestrings wurde so gewählt, um die Regeln für in Readings erlaubte Zeichen einzuhalten. Wenn keine auswertbaren Daten vorliegen und berechnet werden konnten, werden Readings mit "-" ausgegeben. Im Beispiel sind es die Tagesaggregate 10.10.-13.10. da diese Tage in der Zukunft liegen.

Danach folgt im Reading die Angabe des Devices (STP_5000), des augewerteten Readings (etoday) und der Funktion die dieses Ergebnis ermittelt hat (MAX für maxValue). Die letzte Angabe, hier "2016-10-07" definiert den Auswertungszeitraum. In dem Beispiel ist es der 07.10.2016. Würde die Berechnung mit aggregation=week durchgeführt werden, würde man als an dieser Stelle den Auswertungszeitraum "week_39" erhalten, also die Kalenderwoche 39.

Um die Auswertungsergebniss etwas benutzerfreundlicher zu gestalten, steht das Attribut "readingNameMap" zur Verfügung.

Rep.STP_5000 maxValue mit readingNameMap

Im Beispiel setzen wir es auf:

attr Rep.etoday.STP_5000 readingNameMap Tageserzeugung_kWh

Ein erneuter maxValue-Lauf erzeugt Readings des folgenden Aufbaus:

2016-10-01_18-03-13__Tageserzeugung_kWh__2016-10-01   4.9870

(regelmäßiges) löschen von Datenbanksätzen

Dieses DbRep-Device dient dazu einmalig oder verbunden mit einem AT-Device Einträge aus einer Datenbank zu löschen. Zunächst wird das Device wie im Abschnitt "Definieren eines DbRep-Devices" beschrieben definiert. Dadurch wird das DbRep-Device mit einem DbLog-Device assoziiert und mit der Datenbank des DbLogs verbunden. Alle weiteren Operationen werden mit dieser Datenbank durchgeführt. In diesem Beispiel heißt das DbRep-Device "Rep.Del.DbShort" und das DbLog-Device "LogDBShort".

In diesem Beispiel sollen alle Datensätze gelöscht werden die älter als 180 Tage sind. Die Löschfunktion des Moduls ist standardmäßig ausgeschaltet und muß per Attribut enabled werden.

Dazu werden die Attribute

Rep.Del.DbShort konfiguriert

 attr Rep.Del.DbShort timeOlderThan 5552000
 attr Rep.Del.DbShort allowDeletion 1

gesetzt. Die Zeitangabe erfolgt in Sekunden.

Der Löschvorgang wird mit

 set Rep.Del.DbShort delEntries

gestartet. Nach Abschluß der Datenlöschung wird die Anzahl der deleted rows als Reading ausgegeben und auch im Log mit verbose=3 geschrieben.

Müssen sehr viele Datensätze gelöscht werden könnte nach 86400 Sekunden der Standardtimeout erreicht werden und der Vorgang mit "error" enden. In diesem Fall ist der timout mit dem Attribut:

 attr Rep.Del.DbShort timeout <timeout in Sekunden>

entsprechend angepasst werden.

Ein fhem.cfg Eintrag für das beschriebene Beispieldevice sieht folgendermaßen aus:

define Rep.Del.DbShort DbRep LogDBShort
attr Rep.Del.DbShort allowDeletion 1
attr Rep.Del.DbShort comment löschen aller Einträge in LogDBShort älter als 180 Tage
attr Rep.Del.DbShort devStateIcon connected:10px-kreis-gelb .*disconnect:10px-kreis-rot .*done:10px-kreis-gruen
attr Rep.Del.DbShort event-on-update-reading state
attr Rep.Del.DbShort room DbLog
attr Rep.Del.DbShort timeOlderThan 15552000

Die Löschung der relevanten Datensätze kann weiterhin über Attribute "device" bzw. "reading" kann selektiv auf bestimmte Device- und/oder Readingloggings beschränkt werden. Wird keine Zeitbeschränkung (z.B. durch "timeOlderThan") vorgenommen, erfolgt die Löschung aller Datensätze der durch "device" bzw. "reading" spezifizierten Datenbankinhalte.

Als Argument der Attribute "device" bzw. "reading" kann SQL-Wildcard "%" verwendet werden.

%     substituiert ein oder mehrere Zeichen
_     Platzhalter für ein einzelnes Zeichen

Beispiel:

 attr Rep.Del.DbShort device %5000

Mit dieser Spezifikation werden alle Devices gelöscht die auf "5000" enden, also z.B. STP_5000 und MySTP_5000.

Ein einfaches AT kann dazu dienen den Löschjob über das Device Rep.Del.DbShort regelmäßig alle x-Stunden/Minuten auszuführen. Dadurch werden nicht mehr benötigte Datenbankeinträge automatisiert und nicht FHEM blockierend im Hintergrund entfernt.

define At.Del.DbShort at +*23:45:00 set Rep.Del.DbShort delEntries
attr At.Del.DbShort room DbLog

Auffinden von alten Devicenamen in der DB und versenden/loggen einer Negativliste

Wenn eine Datenbank längere Zeit gelaufen ist, Devices in FHEM hinzugefügt, geändert oder gelöscht wurden oder auch das DEF des DbLog-Devices angepasst wurde, befinden sich aller Wahrscheinlichkeit nach nicht mehr gewünschte/gebrauchte Datensätze von nicht mehr bestehenden Devices in der Datenbank.

Dieses Beispiel soll einen Weg zeigen, wie man mit Hilfe der DbRep-sqlCmd Funktion (ab Version 4.14.0) alle in der DB enthältenen Devicenamen ermitteln und mit einer Positivliste vergleichen kann. Die Negativliste der nicht gewünschten Devices wird im Logfile ausgegeben bzw. mit TelegramBot versendet.

Zunächst wird das Device wieder wie im Abschnitt "Definieren eines DbRep-Devices" beschrieben definiert. Dadurch wird das DbRep-Device mit einem DbLog-Device assoziiert und mit der Datenbank des DbLog-Device verbunden. In diesem Beispiel heißt das DbRep-Device "Report".

Nach der Definition setzen wir die Attribute:

attr Report event-on-update-reading state
attr Report sqlResultFormat separated

Es werden nun nur noch Events für "state" generiert. Das Attribut "sqlResultFormat = separated" bewirkt, dass das Ergebnis des SQL-Statements zeilenweise in Einzelreadings zur Verfügung gestellt wird.

Nun kann getestet werden, ob die Abfrage grundsätzlich funktioniert. Dazu wird das benutzerspezifische Kommando "sqlCmd" verwendet.

set Report sqlCmd select device, count(*) from history group by DEVICE;

Es sollte nun eine entsprechende Anzahl von Readings erzeugt werden (Browserrefresh), die als Wert eine Kombination aus <Device>|<Anzahl Datensätze in DB> enthalten wie im nebenstehenden Screenshot ersichtlich.

sqlCmd ausgeführt

Wenn das Ergebnis wie gewünscht vorliegt, wird nun die Benutzerschnittstelle aktiviert.

attr Report userExitFn NaDevs .*:.*

"NaDevs" ist die Funktion in 99_myUtils.pm die aufgerufen werden soll. Die Angabe des Regex ".*:.*" ist optional. Nähere Informationen zur Funktionsweise sind in der Commandref zu finden.

Die "Positivliste" der in der Datenbank erlaubten Devices wird für das Beispiel in dem Attribut "comment" für den späteren Vergleich als String angegeben. Es ist natürlich auch z.B. eine Ableitung der im DEF-Regex des zugeordneten DbLog-Devices enthaltenen Device-Definitionen vorstellbar, sodass immer der aktuelle Stand dieser Definition als Grundlage für den Vergleich herangezogen wird.

attr Report comment (sysmon|MyWetter|SMA_Energymeter|STP_5000|Cam.*)

Somit werden die Devices "sysmon", "MyWetter", "SMA_Energymeter", "STP_5000" und alle Cam-Devices als in der DB erlaubt definiert. Alle anderen gefundenen Devices sollen in einer Negativliste aufgeführt werden.

Im weiteren Schritt wird die aufzurufende Funktion in der vorhandenen 99_myUtils.pm ergänzt.

############################################################################
# $Id: myUtilsTemplate.pm 7570 2015-01-14 18:31:44Z rudolfkoenig $
#
# Save this file as 99_myUtils.pm, and create your own functions in the new
# file. They are then available in every Perl expression.

package main;

use strict;
use warnings;

sub
myUtils_Initialize($$)
{
  my ($hash) = @_;
}

# Enter you functions below _this_ line.

my @dna;

...

######################################################
########    Not allowed Devs in DB ermitteln
########          UserExitFn in DbRep           
######################################################
sub NaDevs {
 my ($name,$reading,$value) = @_;
 my $hash   = $defs{$name};
 
 # DB Namen ermitteln
 my $dbconn = $hash->{dbloghash}{dbconn};
 my $db = (split("=",(split(";",$dbconn))[0]))[1];
 
 # Liste der erlaubten Devices aus Attribut "comment" lesen
 my $adevs = AttrVal($name, "comment","-");
 
 if($reading eq "state" && $value eq "running") {
     # der Select wurde gestartet
     Log3 $name, 1, "UserExitFn called by $name - new Select has been startet. Allowed devices in database are: $adevs"; 
     @dna=();
     return;
 }
 
 my $d = (split("\\|",$value))[0];
 
 # das Selektionsergebnis bewerten und ggf. dem Ergebnisarray hinzufügen
 if ( $reading =~ m/SqlResultRow.*/ && $d !~ m/$adevs/) {
   push(@dna,$d."\n")
 }
 
 if ($reading eq "state" && $value eq "done") {
     # Ergebnis versenden bzw. loggen
     Log3 $name, 1, "UserExitFn called by $name - Devices not allowd found in $db: @dna"; 
     fhem("set teleBot message Devices are not allowed found in $db: \n @dna");
 }
 
return;
}

....
1;

Die Schnittstelle übergibt der aufgerufenen Funktion den Namen des DbRep-Devices, den Namen des erstellten Readings und dessen Wert. Der Aufruf erfolgt nach jeder Readingserstellung. Die Schnittstelle arbeitet OHNE Eventgenerierung bzw. benötigt keine Events. Über den Namen des DbRep-Devices kann auf dessen Hash zugegriffen werden bzw. über $hash->{dbloghash}{...} ebenfalls auf den Hash des angeschlossenen DbLog-Devices.

In der Funktion "NaDevs" wird der Device-Teilstring des erzeugten Readings mit dem Wertevorrat aus dem "comment"-Attribut verglichen und bei einem negativen Ergebnis der Negativliste @dna hinzugefügt.

Nach Abschluss der Operation (signalisiert durch Reading "state = done") wird die erzeugte Liste im Logfile ausgegeben bzw. ein TelgramBot-Device versendet.

Größe der FHEM-Datenbank ermitteln

Zunächst wird das Device wie im Abschnitt "Definieren eines DbRep-Devices" beschrieben definiert. Dadurch wird das DbRep-Device mit einem DbLog-Device assoziiert und mit der Datenbank des DbLogs verbunden. Alle weiteren Operationen werden mit dieser Datenbank durchgeführt. In diesem Beispiel heißt das DbRep-Device "Rep.Fhem.Size".

Um die Größe der Datenbank beziehungsweise der Tabellen "history" und "current" zu ermitteln steht das Kommando:

get Rep.Fhem.Size tableinfo

 get Rep.Fhem.Size tableinfo  (MySQL, PostgreSQL)
 get Rep.Fhem.Size svrinfo    (SQLite)

zur Verfügung.

Mit diesem Befehl werden sehr viele Informationen bezüglich der Tabellen des FHEM-Schemas (MySQL) als Readings ausgegeben.

Um nur die relevanten bzw. interessierenden Selektionsergebnisse auszugeben, kann mit dem Attribut showTableInfo (MySQL, PostgreSQL) bzw. showSvrInfo (SQLite) eine kommaseparierte Liste der relevanten Tabellen angegeben werden.

attr Rep.Fhem.Size showTableInfo %history%,%current%    (MySQL, PostgreSQL)

So wie in diesem Beispiel gesetzt, werden nur Informationen der Tabellen "history" und "current" ausgegeben. Diese Ausgabe ich bereits recht übersichtlich, kann aber durch die Verwendung des Attributs suppressReading weiter eingegrenzt werden.

Beispiele:

attr Rep.Fhem.Size suppressReading ^(?!.*INFO_history.data_index_length_MB).*$
attr Rep.Fhem.Size suppressReading ^(?!.*(INFO_history.data_index_length_MB)|(state)).*$
attr Rep.Fhem.Size suppressReading ^(?!.*(INFO_history.data_index_length_MB)|(background_processing_time)|(state)).*$

Mit diesen Attributwerten wird nur das Reading "INFO_history.data_index_length_MB" oder aber "INFO_history.data_index_length_MB" und "state" bzw. "INFO_history.data_index_length_MB", "state" und "background_processing_time" dargestellt.

Die Datenbankgröße (MB) ist je nach DB-Typ in den Readings:

INFO_current.data_index_lenth_MB
INFO_history.data_index_lenth_MB
SQLITE_FILE_SIZE_MB

enhalten.

Wird z.B. über ein AT dieser Befehl regelmäßig durch Rep.Fhem.Size ausgeführt und die Ergebnisse wiederum in DbLog gespeichert kann die Größenentwicklung der Datenbank mittels SVG-Diagrammen dargestellt werden.

get Rep.Fhem.Size tableinfo

Reading von DbRep nach Dummy übertragen

In dem Beispiel werden alle erzeugten Readings des DbRep-Devices in den Dummy übertragen und heißen dann genauso.

define Dum.Rep dummy
attr Dum.Rep room DbLog


define N.Dum.Rep notify Rep.SMAEM:(\d).*Grid.* { fhem "setreading Dum.Rep ".(split(":",$EVTPART0))[0]." $EVTPART1"}
attr N.Dum.Rep room DbLog

Soll im Dummy ein Reading mit eigenem Namen gefüllt werden, sieht das Notify so aus:

define N.Dum.Rep notify Rep.SMAEM:(\d).*Grid.* { fhem "setreading Dum.Rep DeinReading"." $EVTPART1"}

datenbankgestützte Erstellung der Energiebilanz einer SMA PV-Anlage mit Überschußeinspeisung

Hier geht's zum Beitrag.

Summe aller Einschaltzeiten eines Gerätes

Hier geht's zum Beitrag.

Welche Device/Reading-Kombinationen gibt es in der Datenbank ?

Wenn eine Datenbank längere Zeit gelaufen ist und die zu loggenden Devices bzw. Readings immer mal wieder geändert wurden, wächst der Wunsch einen Überblick über die in der DB enthaltenen Devices/Reading-Kombinationen zu erhalten. Dadurch können wiederum gezielt Auswertungen auf eine bestimmte Device/Reading-Kombination durchgeführt werden.

Um dieses Ziel zu erreichen, wird ein DbRep-Device erstellt welches für die Verwendung eines User-Command eingerichtet wird. Je nach dem gewünschten Ergebnis wird eines der nachfolgenden SQL-Kommandos verwendet.

Selektion aller Device/Reading-Kombinationen in der Datenbank:

select device, reading, count(*) from history group by DEVICE, READING

Nur die geloggten Devices in der Datenbank reporten:

select device, count(*) from history group by DEVICE

Zur Einrichtung wird zunächst das DbRep-Device definiert:

define Rep.LogDB.sqlResult DbRep LogDB

Dabei ist "LogDB" nicht die Datenbank, sondern das DbLog-Device mit dem das DbRep-Device assoziiert und derüber mit der Datenbank verbunden wird !

Um die Ausgabe des späteren Ergebnisses zu formatieren wird das Attribut "sqlResultFormat" auf "table" gesetzt. Dadurch wird das Selektergebnis als Tabelle mit den Spalten Device, Reading und der Anzahl der enthaltenen Datensätze der jeweiligen Kombination erzeugt.

attr Rep.LogDB.sqlResult sqlResultFormat table

Natürlich kann auch ein anderes Ausgabeformat gewählt werden wenn gewünscht oder benötigt. Bei großen Ergebnismengen ist zur Erhöhung der Übersichtlichkeit das Attribut "sqlResultFormat = separated" wahrscheinlich eher geeignet.

Das Attribut stateFormat wird entsprechend gesetzt um nach einem erfolgten Selektionslauf die erzeugte Tabelle in der Raumansicht bzw. im DeviceOverview anzuzeigen:

 attr Rep.LogDB.sqlResult stateFormat { if (defined($defs{$name}{READINGS}{SqlResult})) {
                                            ReadingsVal($name,"SqlResult",undef);
                                        } else {
                                            ReadingsVal($name,"state",undef);
                                        } 
                                      }                                        

Damit ist das DbRep-Device vorbereitet und der Auswertungslauf kann gestartet werden mit:

set Rep.LogDB.sqlResult sqlCmd select device, reading, count(*) from history group by DEVICE, READING;

bzw. wenn nur die in der Datenbank enthaltenen Devices zu erhalten:

set Rep.LogDB.sqlResult sqlCmd select device, count(*) from history group by DEVICE;

set Rep.LogDB.sqlResult sqlCmd select device, count(*) from history group by DEVICE;

Nebenstehender Screenshot zeigt das Ergebnis der Auswertung aller in der Datenbank enthalteten Devices und deren Anzahl von Datensätzen.

Zum leichteren Nachnutzung hier noch die Raw-Definition des fertigen DbRep-Devices:

defmod Rep.LogDB.sqlResult DbRep LogDB
attr Rep.LogDB.sqlResult aggregation no
attr Rep.LogDB.sqlResult comment Device zum freien Report mit sqlCmd.\
- Auswahl von Statements -\
\
Device/Reading-Kombinationen in der Datenbank:\
select device, reading, count(*) from history group by DEVICE, READING\
\
geloggte Devices in der Datenbank:\
select device, count(*) from history group by DEVICE\

attr Rep.LogDB.sqlResult devStateIcon connected:10px-kreis-gelb .*disconnect:10px-kreis-rot .*done:10px-kreis-gruen
attr Rep.LogDB.sqlResult disable 0
attr Rep.LogDB.sqlResult event-on-update-reading state
attr Rep.LogDB.sqlResult group Datenbankauswertungen
attr Rep.LogDB.sqlResult room DbLog
attr Rep.LogDB.sqlResult showproctime 1
attr Rep.LogDB.sqlResult sqlResultFormat table
attr Rep.LogDB.sqlResult stateFormat { if (defined($defs{$name}{READINGS}{SqlResult})) {\
   ReadingsVal($name,"SqlResult",undef);;\
  } else {\
   ReadingsVal($name,"state",undef);;\
  } \
}
attr Rep.LogDB.sqlResult timeout 1000
attr Rep.LogDB.sqlResult verbose 2

Die current-Tabelle mit einem Extrakt der history-Tabelle füllen (tableCurrentFillup)

Diese Funktion erweitert die Möglichkeiten der Verwendung der current-Tabelle des DbLog-Devices. In den meisten Fällen wird im DbLog das Attribut "DbLogType = Current/History" gesetzt sein um bei der Erstellung von SVG's entsprechende Vorschläge in der DropDown-Liste des Editors zur Verfügung zu haben.

Bei dieser Betriebsart werden in der current-Tabelle neu auftretende Events von Device/Reading-Kombinationen eingetragen, sofern sie noch nicht enthalten sind. Werden neue Geräte definiert, erscheinen deren Events in der current-Tabelle, auch wenn man später nicht benötigte Readings mit den Attributen "event-on-change-reading" oder "event-on-update-reading" ausgrenzt. Die einmal gespeicherten Werte bleiben in der current Tabelle erhalten. Sicherlich wird deswegen der eine oder andere User den Inhalt der current Tabelle löschen um wieder eine leere und saubere current-Tabelle zu haben. Das führt dann natürlich dazu, dass nur die ab diesem Zeitpunkt auftretenden Events in der Vorschlagsliste aufgenommen werden und nur selten auftretende Device/Reading-Kombinationen erst nach längerer Zeit wieder in der Tabelle current gespeichert werden.

Darüber hinaus möchte man vielleicht nur für SVG-Diagramme relevante Device/Reading-Einträge in der Vorschagsliste vorfinden oder dort nur Einträge vorhalten, die in den letzten drei Monaten aufgetreten sind. Die Beweggründe können sehr vielfältig sein. Um diesem Wunsch zu entsprechen, gibt es im DbRep die Funktion "set <DbRep-Device> tableCurrentFillup", die eng mit dem dem DbLog-Attribut "DbLogType = SampleFill/History" zusammenarbeitet.

Nachfolgend wird an einem Beispiel erläutert, welche Konfigurationen vorgenommen werden können, um aus der history-Tabelle alle vorhandenen Device/Reading-Kombinationen zu extrahieren und sie zur Verwendung als SVG-Vorschlagsliste in die current-Tabelle einzufügen.

Für das Beispiel wird folgendes angenommen:

• es gibt ein definiertes DbLog-Device "LogDB" welches in die Datenbank "fhem" loggt
• das DbRep-Device zur Ausführung der Funktion wird "Rep.FillCurr.fhem" genannt
• es sollen alle in der history-Tabelle vorkommenden Device/Reading-Kombinationen extrahiert und in die current-Tabelle eingetragen werden (Einschränkungen auf bestimmte Devices / Readings oder zeitliche Abgrenzungen werden nicht vorgenommen, aber es wird darauf hingewiesen, wie es gemacht werden kann)

a) Anlegen des DbRep-Devices

Die Definition des DbRep-Devices erfolgt unter Angabe des zu verbindenden DbLog-Devices (nicht der Datenbank selbst):

define Rep.FillCurr.fhem DbRep LogDB

In dem so definierten neuen Device sind die Attribute zu setzen, die für die beabsichtigte Funktion wichtig sind. Da dieses Device auch für die Löschung der in der current-Tabelle gespeicherten Datensätze verwendet wird, muss das Attribut "allowDeletion = 1" gesetzt werden.

attr Rep.FillCurr.fhem allowDeletion 1

Ebenfalls wichtig ist die Erzeugung eines Events sobald der Löschvorgang der current-Tabelle abgeschlossen ist. Dazu wird im Beispiel "event-on-update-reading" verwendet:

attr Rep.FillCurr.fhem event-on-update-reading state,--DELETED_ROWS_CURRENT-- 

set Rep.FillCurr.fhem tableCurrentPurge

Für die Funktion nicht wichtig, aber durchaus interessant zu wissen wie lange die Laufzeit der Funktionen ist, wird noch das Attribut "showproctime = 1" gesetzt. Damit werden die Readings "background_processing_time" und "sql_processing_time" erzeugt welche die jeweilige Bearbeitungszeit in Sekunden darstellen.

attr Rep.FillCurr.fhem showproctime 1

Mit dem so vorbereiteten Device können schon die Funktionen "tableCurrentPurge" und "tableCurrentFillup" manuell getestet werden. Mit dem Kommando:

set Rep.FillCurr.fhem tableCurrentPurge

wird der gesamte aktuelle Inhalt der current-Tabelle gelöscht. Wie im nebenstehenden Screenshot sichtbar, wurden in dem Beispiel 170 Datensätze gelöscht und dazu rund 30ms benötigt. Mit Abschluß der Operation wird der Delete-Event erzeugt:

2018-01-05 13:38:14.596 DbRep Rep.FillCurr.fhem --DELETED_ROWS_CURRENT--: 170

set Rep.FillCurr.fhem tableCurrentFillup

Dieser Event wird später in einem Notify für die automatisierte Current-Auffüllung verwendet. Eine Zählug der current-EInträge mit "set Rep.FillCurr.fhem countEntries current" ergibt "-", d.h. der Löschbefehl war erfolgreich. Im jetzigen Zustand würde bei der Erstellung eines SVG keine DropDown-Liste angezeigt werden und statt dessen die Felder im SVG-Editor frei bearbeitbar sein, weil die current-Tabelle keine Werte enthält. Gleichermaßen kann bereits die Auffüllung der current-Tabelle getestet werden mit dem Befehl:

set Rep.FillCurr.fhem tableCurrentFillup

Je nach Größe der Datenbank kann diese Operation einige Zeit in Anspruch nehmen. Da dieser Befehl wie bei DbRep üblich non-blocking abgearbeitet wird, hat die Laufzeit keinen Einfluss auf die sonstige FHEM-Verfügbarkeit.

set Rep.FillCurr.fhem tableCurrentFillup

Wie bereits erwähnt, wird in dem Beispiel die gesamte history-Tabelle ausgewertet. Sollen zum Beispiel nur die letzten 90 Tage zur Auswertung herangezogen werden, kann das Attribut "timeDiffToNow = d:90" gesetzt werden.

Weitere Eingrenzungen bzgl. der auszuwertenden Devices bzw. Readings können über die Attribute "device" und "reading" vorgenommen werden. Die Syntax für die genannten Attribute und deren Auswirkung entnehme bitte der commandref.

In unserem Beispiel wurde die current-Tabelle mit 170 Datensätzen aufgefüllt. Dazu wurden 291 Sekunden benötigt (die Tabelle history enthält insgesamt rund 11 Mio. Einträge)

Wird jetzt ein neues SVG erstellt, erscheinen alle in selektierten Device/Reading-Kombinationen alphabetisch sortiert als DopDown-Liste im SVG-Editor.

b) Konfiguration des DbLog-Devices

In dem bestehen DbLog-Device "LogDB" ist zur Vorbereitung lediglich das Attribut "DbLogType = SampleFill/History" zu setzen:

attr LogDB DbLogType SampleFill/History

Entgegen der Arebeitsweise mit "Current/History" wird die current-Tabelle nicht mehr durch die Logging-Engine aktiv mit jedem relevanten Event gefüllt, sondern kann durch externe Tools, in dem Fall dem DbRep-Device, beschrieben werden. Die current-Tabelle wird aber, ob leer oder gefüllt, bei der Erstellung eines SVG-Devices ausgewertet und in Abhängigeit des Ergebnisses eine DropDown-Liste zur Verfügung gestellt, oder frei editierbare Felder angeboten falls die current-Tabelle leer ist.

c) Definition der at/notify-Devices zum automatisierten Ablauf

Die Löschung des Inhaltes der current-Tabelle und die erneute Ausffüllung soll natürlich autmatisiert regelmäßig erfolgen. Zu diesem Zweck wird ein at-Device (At.LogDB.currentPurge) und ein notify-Device angelegt. Das AT-Device soll alle 8 Stunden und 15 Minuten das Löschen des current-Inhaltes steuern und nach Abschluß der Löschung soll getriggert durch das "--DELETED_ROWS_CURRENT--"-Event die Neubefüllung der current-Tabelle veranlasst werden.

Zur Definition dieser Devices gibt es nicht viel zu erläutern. Die entsprechende Syntax kann in der commandref zu at bzw. notify nachgelesen werden.

at-Device:

define At.LogDB.currentPurge at +*08:15:00 set Rep.FillCurr.fhem tableCurrentPurge

notify-Device:

define N.LogDB.Fillup notify Rep.FillCurr.fhem:.*DELETED_ROWS_CURRENT.*  sleep 5;set Rep.FillCurr.fhem tableCurrentFillup

Das nichtblockierende FHEM-sleep (sleep gefolgt von einem weiteren Befehl) wird lediglich dazu verwendet, um zwischen der Delete- und Auffüll-Operation eine kleine Pause einzufügen.

d) Arbeitsweise und Raw-Definitionen

Durch das "At.LogDB.currentPurge"-Device wird alle 8 Stunden und 15 Minute ein Löschlauf der current-Tabelle gestartet. Dadurch wird dem darauf folgenden Auffüllprozess immer eine saubere leere current-Tabelle zur Vefügung gestellt. Nach dem Löschlauf wird durch das "N.LogDB.Fillup"-Device, getriggert durch den Event "Rep.FillCurr.fhem:.*DELETED_ROWS_CURRENT.*", die erneute Auffüllung der current-Tabelle gestartet. Die Daten werden aus der histrory-Tabelle gelesen, wobei der Zeitraum der Selektion und die zu betrachtenden Devices / Readings durch die Zeit-Attribute bzw. device / reading-Attribute des DbRep-Devices "Rep.FillCurr.fhem" individuell beeinflußt werden können.

Hilfreich ist es auch, wenn das DbLog-Device "LogDB" im asynchronen Modus betrieben wird.

Abschließend sind hier noch die Raw-Definitionen der beteiligten Devices (außer LogDB) angehängt, um das Beispiel leicht nachvollziehbar zu gestalten.

DbRep-Device "Rep.FillCurr.fhem":

defmod Rep.FillCurr.fhem DbRep LogDB
attr Rep.FillCurr.fhem allowDeletion 1
attr Rep.FillCurr.fhem comment Current Fillup für DB fhem
attr Rep.FillCurr.fhem devStateIcon connected:10px-kreis-gelb .*disconnect:10px-kreis-rot .*done:10px-kreis-gruen
attr Rep.FillCurr.fhem event-on-update-reading state,--DELETED_ROWS_CURRENT--
attr Rep.FillCurr.fhem icon icoTool
attr Rep.FillCurr.fhem room DbLog
attr Rep.FillCurr.fhem showproctime 1
attr Rep.FillCurr.fhem verbose 3

at-Device "At.LogDB.currentPurge":

defmod At.LogDB.currentPurge at +*08:15:00 set Rep.FillCurr.fhem tableCurrentPurge
attr At.LogDB.currentPurge comment Zeitgesteuertes Löschen der Current Tabelle. Nach dem Löschen wird \
Eventgesteuert (Notify N.LogDB.Fillup) ein tableCurrentFillup gestartet.
attr At.LogDB.currentPurge room DbLog

notify-Device "N.LogDB.Fillup":

defmod N.LogDB.Fillup notify Rep.FillCurr.fhem:.*DELETED_ROWS_CURRENT.*  sleep 5;;set Rep.FillCurr.fhem tableCurrentFillup
attr N.LogDB.Fillup disable 1
attr N.LogDB.Fillup room DbLog
attr N.LogDB.Fillup verbose 2

Backup/Restore einer SQLite Datenbank im laufenden Betrieb (ab DbRep-Version 7.4.0)

In diesem Beitrag wird erläutert, wie man mit DbRep ein Backup der SQLite-Datenbank erstellen kann, welche Möglichkeiten es dabei gibt und wie man eine Datenbank aus einem Backup wieder herstellen kann. Die Backup-Funktion nutzt die SQLite Online Backup API und ermöglicht es, konsistente Backups der SQLite-DB in laufenden Betrieb zu erstellen ohne die Datenbank schließen zu müssen.

Neben dem eigentlichen Backup kann optional einstellt werden, dass:

• vor dem Backup eine Datenbankoptimierung (Vacuum) ausgeführt werden soll. Dadurch wird Plattenplatz freigegeben sofern möglich.
• vor und nach dem Backup kann ein FHEM-Kommando oder eine Perl-Routine ausgeführt werden (eine Perl-Routine ist in {} einzuschließen)
• das erstellte Dumpfile kann über FTP(S) an einen FTP-Server übertragen werden
• über die interne Versionsverwaltung kann festgelegt werden, wieviele erstellte Backups auf dem Datenträger erhalten bleiben sollen (default: 3)

Für die Erläuterung des Beispiels soll ein DbRep-Device erstellt werden, welches:

• die SQLite Datenbank im Betrieb (Online) sichert
• vor dem Dump einen Vacuum-Lauf durchführt
• das erstellte Dumpfile auf einen FTP-Server überträgt
• 2 Versionen (Dumpfiles) nach einem erfolgreichen Backuplauf im Dumpverzeichnis verbleiben sollen

1. Anlegen des DbRep-Devices

Das anzulegende Device wird sowohl für das (regelmäßige) Backup als auch für einen eventuellen Restor verwendet. Die Definition des DbRep-Devices erfolgt unter Angabe des zu verbindenden DbLog-Devices (nicht der Datenbank selbst):

define Rep.SQLite DbRep LogSQLITE

Ist das Device definiert, verbindet es sich zu der Datenbank und wechselt in den state "connected", sofern die Verbindung erfolgreich verlief. Prinzipiell funktiert der hier beschriebene Backup-Prozess mit einem im synchronous betriebenen DbLog-Device, aber es ist dringend angeraten das DbLog-Device (LogSQLITE) in den asynchronen Modus umzuschalte. Dadurch werden Blockierungen von FHEM und Verluste von Daten verhindert.

2. Einstellungen des DbRep-Devices (Attribute)

Für die gewünschte Funktion müssen in diesem neu definierten Device einige relevante Attribute gesetzt werden.

a) dumpDirLocal

Das zu erstellende Backupfile wird per default im log-Verzeichnis ./log (meist /opt/fhem/log) des FHEM-Rechners gespeichert. Um es an einer anderen Stelle zu speichern kann das Attribut "dumpDirLocal" gesetzt werdden. Dieses Verzeichnis kann sich auf dem lokalen Datenträger befinden, oder ein per NFS gemountetes Verzeichnis sein. In jedem Fall muss der User unter dem FHEM läuft Schreibrechte auf dieses Verzeichnis besitzen.

Für das Beispiel sollen die Dump-Files auf einen NFS-Mount einer Synology Diskstation gespeichert werden. Dazu wurde ein Mount in /etc/fstab erstellt um den Pfad "/sds1/backup" lokal verfügbar zu machen:

sds1.<fqdn>:/volume1/ApplicationBackup /sds1/backup nfs auto,defaults,tcp,intr 0 0

In diesem Verzeichnis exsitiert das Directory "dumps_FHEM", in dem die Backup-Files gespeichert werden sollen. Das Attribut wird entsprechend gesetzt auf:

attr Rep.SQLite dumpDirLocal /sds1/backup/dumps_FHEM

b) dumpFilesKeep

Dieser Parameter stellt die Anzahl der aufzubewahrenden Backup-Files ein. Es werden immer die "x" neuesten bzw. letzten Files im Verzeichnis gehalten, wobei nur die zu der jeweiligen Datenbank gehörenden Files betrachtet werden. Die Zugehörigkeit des Dump-Files zur Quelldatenbank wird anhand des Filenamens ermittelt, der sich zusammensetzt aus <DB-Name ohne Endung>_<Erstellungsdatum und Uhrzeit>.sqlitebkp

Beispiel: fhem_2018_01_12_17_15.sqlitebkp

Ist dieses Attribut nicht gesetzt, werden die 3 neuesten Backup-Files im Verzeichnis behalten. Um nur 2 Files zu behalten wird das Attribut gesetz:

attr Rep.SQLite dumpFilesKeep 2

c) Aktionen vor und nach dem Backup ausführen

Vor und nach der Dump-Ausführung kann ein FHEM-Kommando bzw. Perl-Befehle ausgeführt werden. Perl-Befehle müssen in {} eingeschlossen werden. Für das Beispiel soll vor dem Backup die Verbindung des DbLog-Devices LogSQLITE von der Datenbank getrennt werden. Dadurch werden keine neuen Daten in die Datenbank geschrieben. Wenn das DbLog-Device LogSQLITE im asynchronen Modus wie empfohlen betrieben wird, tritt auch kein Datenverlust ein da die zu loggenden Daten im Cache verbleiben bis die Verbindung zur DB wieder hergestellt wird.

Um die Verbindung zu trennen wird ein "set LogSQLITE reopen <Zeit in Sekunden>" verwendet. Die Zeitspanne wird sehr großzügig gewählt und soll sicherstellen dass innerhalb dieser Zeit das Backup abgeschlossen ist. Nach dem Backup wird sofort ein "set LogSQLITE reopen" ausgeführt, was die Verbindung des DbLog-Devices LogSQLITE zur Datenbank unmittelbat wieder öffnet.

attr Rep.SQLite dumpFilesKeep executeBeforeProc set LogSQLITE reopen 3600
attr Rep.SQLite dumpFilesKeep executeAfterProc set LogSQLITE reopen

d) vor dem Backup Datenbank verkleinern (vacuum)

Diese optionale Funktion wird durch das Attribut "optimizeTablesBeforeDump" eingeschaltet. Dadurch die Vacuum-Funktion wird Platz innerhalb der Datenbank freigegeben der durch Löschvorgänge entstanden ist und dadurch die Größe des Datenbankfiles verringert.

attr Rep.SQLite optimizeTablesBeforeDump 1

e) das Dump-File nach dem Backup zum FTP-Server übertragen

DbRep bietet die Möglichkeit, das erstellte Dump-File per FTP oder verschlüsselt per FTP(S) zum Server zu übertragen. Dazu gibt es einen Satz von Attributen um dem Device alle notwendigen Angaben für den FTP-Transfer zur Verfügung zu stellen. Es gibt dafür folgende Attribute:

• ftpUse : FTP Transfer nach dem Dump wird eingeschaltet (bzw. ftpUseSSL mit SSL Verschlüsselung)
• ftpUser : User zur Anmeldung am FTP-Server, default: anonymous
• ftpPwd : Passwort des FTP-Users, default nicht gesetzt
• ftpDir : Verzeichnis auf dem FTP-Server in welches das File übertragen werden soll (default: FTP-root)
• ftpPort : FTP-Port, default: 21
• ftpServer : Name oder IP-Adresse des FTP-Servers
• ftpTimeout : Timeout für die FTP-Verbindung in Sekunden (default: 30)
• ftpPassive : setzen wenn passives FTP verwendet werden soll
• ftpDebug : Debugging des FTP Verkehrs zur Fehlersuche

Gemäß dieser Beschreibung und dem Ziel dieses Beispiels werden die für FTP-Transfer relevanten Attribute wie folgt gesetzt:

attr Rep.SQLite ftpDir /ftp                # Subdirectory ftp unter FTP-root als Zielverzeichnis
attr Rep.SQLite ftpPwd ftpftp1
attr Rep.SQLite ftpServer sds1.<fqdn>
attr Rep.SQLite ftpUse 1
attr Rep.SQLite ftpUser ftpuser

Der FTP-Server muss natürlich vorab eingerichtet und funktionsfähig sein. Sollten beim FTP-Transfer Fehler auftreten, kann das Attribut ftpDebug = 1 gesetzt werden um entsprechende Ausgaben zur Analyse des Problems im Log zu erhalten.

Fertig definiertes Device Rep.SQLite

f) Hilfsattribute

Für die Funktion nicht relevant aber informativ ist das Attribut "showproctime = 1". Ist das Attribut gesetzt, wird das Reading "background_processing_time" angelegt. Es enthält nach der Ausführung die verbrauchte Prozesszeit.

Das fertig konfigurierte Device hier als RAW-Definition zur einfachen Nachnutzung. Die Angaben sind natürlich anzupassen:

define Rep.SQLite DbRep LogSQLITE
attr Rep.SQLite devStateIcon connected:10px-kreis-gelb .*disconnect:10px-kreis-rot .*done:10px-kreis-gruen
attr Rep.SQLite dumpDirLocal /sds1/backup/dumps_FHEM
attr Rep.SQLite dumpFilesKeep 2
attr Rep.SQLite event-on-update-reading state
attr Rep.SQLite executeAfterProc set LogSQLITE reopen
attr Rep.SQLite executeBeforeProc set LogSQLITE reopen 3600
attr Rep.SQLite ftpDir /ftp
attr Rep.SQLite ftpPwd ftpftp1
attr Rep.SQLite ftpServer sds1.<fqdn>
attr Rep.SQLite ftpUse 1
attr Rep.SQLite ftpUser ftpuser
attr Rep.SQLite optimizeTablesBeforeDump 1
attr Rep.SQLite room DbLog
attr Rep.SQLite showproctime 1
attr Rep.SQLite verbose 3

3. Backup durchführen

Mit dem wie beschrieben eingerichteten DbRep-Device kann das Buckup gestartet werden mit:

set Rep.SQLite dumpSQLite

LogSQLITE ist geschlossen bis ...

Zu Beginn des Vorgangs wird sofort die Verbindung des DbLog-Devices zur Datenbank getrennt. Ein laufendes Backup kann mit "set Rep.SQLite cancelDump" abgebrochen werden. Das laufende Backup wird im state angezeigt mit "SQLite Dump is running - be patient and see Logfile !". Dieser Satz ist ernst gemeint, wobei der Dump über die Online-API sehr schnell arbeitet. Im Logfile mit (mindestens) verbose 3 werden die relevanten Informationen zur Verfügung gestellt:

2018.01.13 08:35:10.745 3: DbRep Rep.SQLite - ################################################################           
2018.01.13 08:35:10.746 3: DbRep Rep.SQLite - ###                    New SQLite dump                       ###
2018.01.13 08:35:10.747 3: DbRep Rep.SQLite - ################################################################
2018.01.13 08:35:10.748 3: DbRep Rep.SQLite - execute command before dump: 'set LogSQLITE reopen 3600' 
2018.01.13 08:35:10.883 2: DbLog LogSQLITE: Connection closed until 09:35:10 (3600 seconds).
2018.01.13 08:35:10.917 3: DbRep Rep.SQLite - Size of database /opt/fhem/fhem.db before optimize (MB): 2554
2018.01.13 08:35:10.918 3: DbRep Rep.SQLite - VACUUM database /opt/fhem/fhem.db....
2018.01.13 08:44:04.216 3: DbRep Rep.SQLite - Size of database /opt/fhem/fhem.db after optimize (MB): 2554
2018.01.13 08:44:04.280 3: DbRep Rep.SQLite - Starting dump of database 'fhem.db'
2018.01.13 08:45:03.810 3: DbRep Rep.SQLite - Size of backupfile: 2553.64 MB
2018.01.13 08:45:04.579 3: DbRep Rep.SQLite - FTP: transferring /sds1/backup/dumps_FHEM/fhem_2018_01_13_08_44.sqlitebkp
2018.01.13 08:45:48.838 3: DbRep Rep.SQLite - FTP: fhem_2018_01_13_08_44.sqlitebkp transferred successfully to sds1.myds.me into dir /ftp
2018.01.13 08:45:48.844 3: DbRep Rep.SQLite - Deleting old dumpfile 'fhem_2018_01_13_08_13.sqlitebkp' 
2018.01.13 08:45:49.273 3: DbRep Rep.SQLite - Finished backup of database fhem - total time used: 638 seconds
2018.01.13 08:45:49.358 2: DbRep Rep.SQLite - command after dump message: "Reopen executed." 
2018.01.13 08:45:49.370 3: DbRep Rep.SQLite - Database dump finished successfully. 

das Backup läuft

Setzt man das Attribut "ftpDebug=1", erhält man im Log zusätzliche Informationen zum FTP-Transfer:

2018.01.13 08:50:22.385 3: DbRep Rep.SQLite - Size of backupfile: 2553.82 MB
Net::FTP>>> Net::FTP(2.79)
Net::FTP>>>   Exporter(5.72)
Net::FTP>>>   Net::Cmd(2.30)
Net::FTP>>>   IO::Socket::INET(1.35)
Net::FTP>>>     IO::Socket(1.38)
Net::FTP>>>       IO::Handle(1.35)
Net::FTP=GLOB(0xb12ed20)<<< 220 SDS1 FTP server ready.
Net::FTP=GLOB(0xb12ed20)>>> USER ftpuser
Net::FTP=GLOB(0xb12ed20)<<< 331 Password required for ftpuser.
Net::FTP=GLOB(0xb12ed20)>>> PASS ....
Net::FTP=GLOB(0xb12ed20)<<< 230 User ftpuser logged in, access restrictions apply.
Net::FTP=GLOB(0xb12ed20)>>> TYPE I
Net::FTP=GLOB(0xb12ed20)<<< 200 Type set to I.
Net::FTP=GLOB(0xb12ed20)>>> CWD /ftp
Net::FTP=GLOB(0xb12ed20)<<< 250 CWD command successful.
2018.01.13 08:50:22.880 3: DbRep Rep.SQLite - FTP: transferring /sds1/backup/dumps_FHEM/fhem_2018_01_13_08_49.sqlitebkp
Net::FTP=GLOB(0xb12ed20)>>> PORT 192,168,2,45,145,42
Net::FTP=GLOB(0xb12ed20)<<< 200 PORT command successful.
Net::FTP=GLOB(0xb12ed20)>>> ALLO 2677867520
Net::FTP=GLOB(0xb12ed20)<<< 202 ALLO command ignored.
Net::FTP=GLOB(0xb12ed20)>>> STOR fhem_2018_01_13_08_49.sqlitebkp
Net::FTP=GLOB(0xb12ed20)<<< 150 Opening BINARY mode data connection for 'fhem_2018_01_13_08_49.sqlitebkp'.
Net::FTP=GLOB(0xb12ed20)<<< 226 Transfer complete.
2018.01.13 08:51:06.859 3: DbRep Rep.SQLite - FTP: fhem_2018_01_13_08_49.sqlitebkp transferred successfully to sds1.myds.me into dir /ftp

Backup ist erfolgreich abgeschlossen

In vorliegenden Beispiel wird nach einem erfolgreichen Backup im state "Warning - dump finished, but command after dump message appeared" angezeigt. Diese Warnung rührt daher, dass das Reopen-Kommando eine Rückkehrinfo mitteilt, die als Problem gewertet und im Reading "afterdump_message" ausgegeben wird. In diesem Fall ist es nur eine Information.

In den erstellten Readings wird zusammengetragen welches File mit welcher Größe ertsellt wurde, welche alten Files gelöscht wurden, Informationen zum FTP-Transfer und welche Zeit der Gesamtprozess benötgt hat.

Das so vorbereitete Backup kann nun z.B. täglich oder auch mit einer wesentlich kürzeren Wiederholungsperiode (alle x Stunden) über ein at-Device eingeplant werden:

define At.SQLite.Dump at *23:52:00 set Rep.SQLite dumpSQLite

4. Restore

Sollte es einmal zur Korruption des Datenbankfiles kommen (database disk image is malformed) und Reparaturversuche erfolglos bleiben, kann ein Restore die einzigste oder vielleicht auch einfachste Möglichkeit sein die Datenbank wieder herzustellen.

Das kann mit dem angelegten DbRep-Device im laufenden Betrieb geschehen. Auch hier ist wieder wichtig, dass das DbLog-Device LogSQLITE im asynchronen Modus betrieben wird und die Reopen-Zeit (siehe Einstellung Attribut "executeBeforeProc") sehr großzügig eingestellt ist.

Zum Restore gibt es den Befehl "set ... restoreSQLite <File>".

Auswahlmenü Files für Restore

Im FHEMWEB öffnet sich dazu eine DropDown-Liste die alle vorhandenen und zur Quelldatenbank passenden Dumpfiles auflistet. Das herzustellende File

kann so bequem ausgewählt werden. Die Dumpfiles müssen sich in dem Verzeichnis befinden, welches durch das Attribut "dumpDirLocal" festgelegt wurde (default (./log).

Restore läuft

Der restore wird demzufolge gestartet mit:

set Rep.SQLite restoreSQLite <File>

Wieder wird zu Beginn des Vorgangs das verbundene DbLog-Device von der Datenbank abgekoppelt und nach dem Restore wieder automatisch verbunden. Eine Datenbankoptimierung bzw. FTP-Transfer wird bei diesem Vorgang natürlich nicht ausgeführt.

Das Log zeigt den Prozessverlauf:

2018.01.13 09:21:55.435 3: DbRep Rep.SQLite - ################################################################
2018.01.13 09:21:55.437 3: DbRep Rep.SQLite - ###             New database Restore/Recovery                ###
2018.01.13 09:21:55.437 3: DbRep Rep.SQLite - ################################################################
2018.01.13 09:21:55.438 3: DbRep Rep.SQLite - execute command before restore: 'set LogSQLITE reopen 3600' 
2018.01.13 09:21:55.448 2: DbLog LogSQLITE: Connection closed until 10:21:55 (3600 seconds).
2018.01.13 09:21:55.477 3: DbRep Rep.SQLite - Starting restore of database 'fhem.db'
2018.01.13 09:30:12.533 3: DbRep Rep.SQLite - Restore of /sds1/backup/dumps_FHEM/fhem_2018_01_13_08_49.sqlitebkp into 'fhem.db' finished - total time used: 497 seconds.
2018.01.13 09:30:12.685 2: DbRep Rep.SQLite - command after restore message: "Reopen executed." 
2018.01.13 09:30:12.700 3: DbRep Rep.SQLite - Database restore finished successfully.

Der Restore wurde erfolgreich abgeschlossen und die Verbindung des DbLog-Device LogSQLITE zur Datenbank wiederhergestellt. Das Logging wird nahtlos fortgeführt.

Restore erfolgreich abgeschlossen

Es ist natürlich zu beachten, dass die Daten zwischen dem Erstellungszeitpunkt des eingespielten Backups und der aktuellen Zeit verloren sind ! Die bestehende (korrupte) Datenbank wird überschrieben. Deswegen ist es ratsam immer ein aktuelles (zeitnahes) Backup zu haben um den Datenverlust möglichst gering zu halten.