database

Database plugin, mit Unterstützung für SQLite3 und MySQL.

Verwenden Sie dieses Plugin, um Itemwerte in einer Datenbank zu speichern. Es unterstützt verschiedene Datenbanken, die eine Python DB API 2 <http://www.python.org/dev/peps/pep-0249/>`_ Implementierung bereitstellen (z. B. SQLite welches bereits mit Python oder MySQL gebundeled ist, und über das Implementierungsmodul verwendet wird).

Konfiguration

Die Informationen zur Konfiguration des Plugins sind unter Plugin ‚database‘ Konfiguration beschrieben.

Wichtig

Falls mehrere Instanzen des Plugins konfiguriert werden sollen, ist darauf zu achten, dass für eine der Instanzen KEIN instance Attribut konfiguriert werden darf, da sonst die Systemdaten nicht gespeichert werden und Abfragen aus dem Admin Interface und der smartVISU ins Leere laufen und Fehlermeldungen produzieren.

Standarmäßig schreibt das Plugin vor dem Beenden von SmarthomeNG alle am Plugin registrierten Items nochmal mit aktuellem Wert in die Datenbank. Die kann durch Setzen des Item Attributes database_write_on_shutdown: False unterdrückt werden. Ein typischer Anwendungsfall sind zum Beispiel monoton steigende Werte wie Zählerstände, die selten geschrieben werden und für die doppelte Einträge durch smarthomeNG Neustarts störend in Datenbank und optionalen Plots in einer Visualisierung sind.

Web Interface

Das database Plugin verfügt über ein Webinterface, mit dessen Hilfe die Items die das Plugin nutzen übersichtlich dargestellt werden.

Aufruf des Webinterfaces

Das Plugin kann aus dem Admin Interface aufgerufen werden. Dazu auf der Seite Plugins in der entsprechenden Zeile das Icon in der Spalte Web Interface anklicken.

Außerdem kann das Webinterface direkt über http://smarthome.local:8383/database bzw. http://smarthome.local:8383/database_<Instanz> aufgerufen werden.

Das Web Interface verfügt über 3 Tabs, sowie Informationen und Buttons im Kopfbereich. Im Kopfbereich werden Informationen zum Zustand des Plugins und zur verwendeten Datenbank angezeigt.

Database Items

Auf diesem Tab werden die Items angezeigt, für welche Daten in der Datenbank gespeichert werden.

Durch einen einen Klick auf den CSV Button in der Zeile des Items, wird ein Download der gespeicherten Daten zu dem Item erzeugt.

Auf dem Tab wird als Wert der letzte in der Datenbank gespeicherte Wert angezeigt. Um eine Historie zu sehen, muss rechts in der Zeile des Items auf den Button mit der Lupe geklickt werden.

Auf der Detail-Seite wird die Liste der gespeicherten Werte zu einem Tag angezeigt. Der anzuzeigende Tag kann rechts im Kalender gewählt werden.

Zu jedem Wert wird angezeigt, wann er gespeichert wurde und für welche Dauer er gültig war. Beim aktuellen Wert wird als Dauer None angezeigt, da der Wert noch gültig ist und die Dauer daher unbekannt ist.

Durch einen Klick auf den Button Übersicht, kann zur Standard Anzeige des Tabs Database Items zurück gekehrt werden.

Plugin-API

Auf diesem Tab werden die öffentlichen Funktionen des Plugins angezeigt, die z.B. in Logiken genutzt werden können. Diese Informationen sind auch in dieser Dokumentation auf der Seite mit den Konfigurationsdaten vorhanden.

Verwaiste Items

Dieses Tab wird nur angezeigt, wenn in der Datenbank verwaiste Items vorhanden sind. Verwaiste Items sind Items, zu denen Informationen in der Datenbank gespeichert sind, zu denen es aber im Item Tree von SmartHomeNG keine Entsprechungen gibt, also kein Item mit dem gleichen Pfad, welches für das database Plugin konfiguriert ist.

Wenn die Daten zu den verwaisten Items nicht mehr benötigt werden, können diese durch klicken des Buttons Datenbank-Cleanup starten gelöscht werden.

Sollen einige Daten erhalten bleiben, so müssen die Items dazu vorher in SmartHomeNG (wieder) konfiguriert werden.

Export von Daten

Das Plugin verfügt über zwei Möglichkeiten, um Daten zu exportieren. Wobei die Zweite (SQL Dump) nur bei Verwendung von SQLite3 zur Verfügung steht.

Der Export wird gestartet, indem einer der beiden Buttons im Kopfbereich des Plugins geklickt wird. Anschließend wird auf dem System auf dem SmartHomeNG läuft, lokal ein Export der Daten erzeugt und anschließend herunter geladen. Während die Erzeugung es Exports läuft, wird im Browser ein leeres Fenster angezeigt. Das Fenster muss bis zum Abschluss des Exports geöffnet bleiben. Der Export kann, je nach Datenbank Größe, bis zu über einer Stunde dauern. Nach Abschluss des Exports wird die Datei herunter geladen und im Fenster wird wieder das Web Interface des database Plugins angezeigt.

CSV Dump

Durch einen Klick auf den Button CSV Dump wird ein vollständiger Dump der in der Datenbank gespeicherten Informationen erzeugt und im Browser runter geladen.

Die Daten in der heruntergeladenen Datei haben folgende Struktur:

item_id;item_name;time;duration;val_str;val_num;val_bool;changed;time_date;changed_date
3;wohnung.kochen.kochfeldg.ma;1606258889619;17998;;217.0;1;1606258947266;2020-11-25 00:01:29.619000;2020-11-25 00:02:27.266000
3;wohnung.kochen.kochfeldg.ma;1606258907617;17993;;216.0;1;1606258947266;2020-11-25 00:01:47.617000;2020-11-25 00:02:27.266000
3;wohnung.kochen.kochfeldg.ma;1606258925610;5996;;217.0;1;1606258947266;2020-11-25 00:02:05.610000;2020-11-25 00:02:27.266000
3;wohnung.kochen.kochfeldg.ma;1606258931606;18006;;216.0;1;1606259007370;2020-11-25 00:02:11.606000;2020-11-25 00:03:27.370000
3;wohnung.kochen.kochfeldg.ma;1606258949612;5993;;217.0;1;1606259007370;2020-11-25 00:02:29.612000;2020-11-25 00:03:27.370000
3;wohnung.kochen.kochfeldg.ma;1606258955605;30001;;216.0;1;1606259007370;2020-11-25 00:02:35.605000;2020-11-25 00:03:27.370000
3;wohnung.kochen.kochfeldg.ma;1606258985606;53991;;217.0;1;1606259067523;2020-11-25 00:03:05.606000;2020-11-25 00:04:27.523000
3;wohnung.kochen.kochfeldg.ma;1606259039597;24006;;216.0;1;1606259067523;2020-11-25 00:03:59.597000;2020-11-25 00:04:27.523000
3;wohnung.kochen.kochfeldg.ma;1606259063603;11984;;217.0;1;1606259127224;2020-11-25 00:04:23.603000;2020-11-25 00:05:27.224000

Es handelt sich hierbei um einen reinen Dump der Daten, nicht um ein Abbild der Datenbank Struktur.

SQL Dump

Im Gegensatz zum CSV Dump, wird bei einem SQL Dump die vollständige Datenbank (Daten und Struktur) herunter geladen. Diese Funktion steht allerdings nur bei Nutzung einer SQLite3 Datenbank zur Verfügung.

Die heruntergeladene Datei hat dabei folgendes Format:

BEGIN TRANSACTION;
CREATE TABLE database_version(version NUMERIC, updated BIGINT, rollout TEXT, rollback TEXT);
INSERT INTO "database_version" VALUES(1,1518289184830,'CREATE TABLE log (time BIGINT, item_id INTEGER, duration BIGINT, val_str TEXT, val_num REAL, val_bool BOOLEAN, changed BIGINT);','DROP TABLE log;');
INSERT INTO "database_version" VALUES(2,1518289184835,'CREATE TABLE item (id INTEGER, name varchar(255), time BIGINT, val_str TEXT, val_num REAL, val_bool BOOLEAN, changed BIGINT);','DROP TABLE item;');
INSERT INTO "database_version" VALUES(3,1518289184840,'CREATE UNIQUE INDEX log_item_id_time ON log (item_id, time);','DROP INDEX log_item_id_time;');
INSERT INTO "database_version" VALUES(4,1518289184845,'CREATE INDEX log_item_id_changed ON log (item_id, changed);','DROP INDEX log_item_id_changed;');
INSERT INTO "database_version" VALUES(5,1518289184849,'CREATE UNIQUE INDEX item_id ON item (id);','DROP INDEX item_id;');
INSERT INTO "database_version" VALUES(6,1518289184854,'CREATE INDEX item_name ON item (name);','DROP INDEX item_name;');
CREATE TABLE item (id INTEGER, name varchar(255), time BIGINT, val_str TEXT, val_num REAL, val_bool BOOLEAN, changed BIGINT);
INSERT INTO "item" VALUES(3,'wohnung.kochen.kochfeldg.ma',1669554322161,NULL,202.0,1,1669554363596);

...

INSERT INTO "log" VALUES(1669557938064,101,NULL,NULL,527.0,1,1669557938992);
INSERT INTO "log" VALUES(1669557928298,105,NULL,NULL,230.0,1,1669557939008);
INSERT INTO "log" VALUES(1669557928356,107,NULL,NULL,227.0,1,1669557939032);
INSERT INTO "log" VALUES(1669557906685,1446,NULL,'1.45',NULL,1,1669557939063);
INSERT INTO "log" VALUES(1669557906694,1447,NULL,'1.45',NULL,1,1669557939071);
CREATE UNIQUE INDEX log_item_id_time ON log (item_id, time);
CREATE INDEX log_item_id_changed ON log (item_id, changed);
CREATE UNIQUE INDEX item_id ON item (id);
CREATE INDEX item_name ON item (name);
COMMIT;

Das herunter geladene SQL Skript kann in eine leere Datenbank importiert werden. Dieses kann zum Beispiel zum Verkleinern des Datenbank Datei nach dem Löschen einer größeren Menge von Daten genutzt werden.

Aufbau der Datenbank

Das Plugin erzeugt und verwendet zwei Tabellen in der Datenbank:

• Table item - Die Tabelle beinhaltet alle Items und ihren letzten bekannten Wert

• Table log - Die Tabelle listet alle historischen Werte der Items auf

Die item Tabelle enthält die folgenden Spalten:

• Column id - Eine eindeutige Kennung die für jedes neue Item inkrementiert wird

• Column name - Der ItemName

• Column time - Ein UNIX Zeitstempel in eine Auflösung von Mikrosekunden

• Column val_str - Der Itemwert als Zeichenkette wenn das Item den Typ str hat

• Column val_num - Der Itemwert als Zahl, wenn das Item den Typ num hat

• Column val_bool - Der Itemwert als Wahrheitswert, das Item den Typ bool oder num hat

• Column changed - Ein UNIX Zeitstempel (in einer Auflösung von Mikrosekunden) der letzen Änderung

Die log Tabelle enthält die folgenden Spalten:

• Column time - Ein UNIX Zeitstempel in eine Auflösung von Mikrosekunden

• Column item_id - Eine Referenz auf eine eindeutige Kennung eines Items in der Tabelle item

• Column duration - Die Dauer in Mikrosekunden (NULL = Wert aktuell aktiv, Dauer noch offen)

• Column val_str - Der Itemwert als Zeichenkette wenn das Item den Typ str hat

• Column val_num - Der Itemwert als Zahl, wenn das Item den Typ num hat

• Column val_bool - Der Itemwert als Wahrheitswert, das Item den Typ bool oder num hat

• Column changed - Ein UNIX Zeitstempel (in einer Auflösung von Mikrosekunden) der letzen Änderung

• Column val_quality - Datenqualitätsflag: 0 = normaler Messwert (Standard), 1 = keine Daten verfügbar (Lücke)

Fehlende Messwerte (Datenlücken)

Wenn ein Gerät (z. B. ein Wechselrichter) zeitweise nicht erreichbar ist, behält das Item in SmartHomeNG seinen letzten Wert. Ohne weiteres würde diese Zeitspanne fälschlicherweise als „Wert unverändert“ in der Datenbank gespeichert — mit Auswirkungen auf Mittelwerte und Energieberechnungen.

Ab Schemaversion 7 unterstützt das Plugin explizite Datenlücken über die Methoden item.db_mark_invalid() und item.db_mark_valid(), die beim Start des Plugins automatisch auf allen registrierten Items verfügbar gemacht werden.

Verwendung im Plugin:

# Verbindung verloren — Lücke öffnen
sh.solar.power.db_mark_invalid(caller='solar_plugin', source='connection_lost')

# Verbindung wiederhergestellt — Lücke schließen, dann neuen Wert setzen
sh.solar.power.db_mark_valid(caller='solar_plugin', source='connection_restored')
sh.solar.power(new_value, 'solar_plugin')

Lückeneinträge (val_quality = 1) werden bei Aggregationsabfragen (avg, sum, integrate, on, min, max) ausgeschlossen, sodass Mittelwerte und Energieberechnungen nur echte Messwerte berücksichtigen. In Rohwertabfragen (raw) erscheinen sie als NULL-Werte, damit Visualisierungen Lücken darstellen können.

Es gibt zwei Möglichkeiten, die Anzahl der Datensätze pro Item zu begrenzen: Durch die Angabe des Item Attributs database_maxage wird das maximale Alter der Einträge eines Items begrenzt. Standardmäßig werden Werte, deren Zeitstempel älter ist als die angegebene Zeitspanne, regelmäßig aus der Datenbank gelöscht. Alternativ können ältere Werte statt gelöscht zu werden auch zu einem Wert pro Kompaktierungsintervall verdichtet werden, siehe unten „Kompaktierung statt Löschen“.

Kompaktierung statt Löschen (database_maxage_action)

Standardmäßig löscht database_maxage alte Werte ersatzlos. Über das Item Attribut database_maxage_action kann stattdessen festgelegt werden, dass alte Rohwerte zu jeweils einem Wert pro Kompaktierungsintervall verdichtet werden, statt komplett zu verschwinden. So bleibt zum Beispiel der Verlauf eines Werts über Jahre hinweg als Tagesmittelwert erhalten, während die minütlichen Rohdaten irgendwann gelöscht werden.

Die Kompaktierung läuft im selben Turnus wie das bisherige Löschen (Parameter removeold_cycle) und arbeitet sich - genau wie das Löschen - in begrenzten Schritten durch alte Daten (Parameter max_aggregate_intervals), um die Datenbank nicht mit einem einzigen großen Durchlauf zu blockieren.

Item Attribute

database_maxage_action

Legt fest, was mit Werten passiert, die älter als database_maxage sind. Standardwert ist delete (bisheriges Verhalten). Jeder andere Wert ersetzt die Rohwerte eines Intervalls durch einen berechneten Wert:

Wert	Bedeutung	Gültig für Typ
delete	Werte löschen (Standard)	beliebig
avg	Zeitgewichteter Mittelwert	num, bool
sum	Summe der Werte	num, bool
min	Minimalwert	num, bool
max	Maximalwert	num, bool
integrate	Diskretes Integral über der Zeit	num, bool
on	Prozentzahl der Werte > 0 (zeitgewichtet)	bool, str
countall	Anzahl der Rohwerte im Intervall	beliebig
first	Ältester Rohwert im Intervall, unverändert	beliebig
last	Neuester Rohwert im Intervall, unverändert	beliebig

Die Funktionen avg, sum, min, max, integrate, on und countall entsprechen den gleichnamigen Funktionen aus dem Abschnitt „Datenbankfunktionen für Einzelauswertungen“ weiter oben. first und last gibt es dort nicht - sie behalten den tatsächlich gespeicherten Wert bei, statt etwas zu berechnen, und sind deshalb die einzigen Funktionen, die auch für Items vom Typ str sinnvoll funktionieren (z.B. um den letzten Status-Text eines Tages zu behalten, statt nur die Anzahl der Statuswechsel zu zählen).

Ist ein Wert für den Typ des Items ungültig (z.B. sum bei einem str-Item, dessen numerische Spalte immer leer ist), wird dies beim Start als Fehler geloggt und für dieses Item automatisch auf delete zurückgefallen.

database_maxage_interval

Größe eines Kompaktierungsintervalls, im gleichen Format wie cycle/autotimer (z.B. 24h, 30m - kein d-Suffix für Tage). Nur relevant, wenn database_maxage_action nicht delete ist. Ohne Angabe gilt der Plugin-Parameter default_maxage_interval.

Beide Attribute haben bewusst keinen Standardwert direkt am Item-Attribut, damit die Plugin-Parameter default_maxage_action/default_maxage_interval als Fallback greifen können, wenn ein Item nur database_maxage setzt, aber keine eigene Aktion/Intervall konfiguriert.

Beispiel

solar:
    leistung:
        type: num
        database: true
        database_maxage: 90
        database_maxage_action: avg
        database_maxage_interval: 24h

Werte, die älter als 90 Tage sind, werden zu einem Tagesmittelwert zusammengefasst statt gelöscht zu werden.

Minimum/Maximum als eigenständige Items (database.min / database.max)

database_maxage_action liefert bewusst nur einen Wert pro Intervall - wer zusätzlich zum Mittelwert auch Minimum und/oder Maximum eines Intervalls dauerhaft speichern möchte, kann dafür die mitgelieferten Structs database.min und database.max nutzen. Sie legen jeweils ein eigenes Kind-Item an (db_min bzw. db_max), das den Wert des übergeordneten Items live mitschreibt und unabhängig mit database_maxage_action: min bzw. max kompaktiert wird:

solar:
    leistung:
        type: num
        database: true
        database_maxage: 90
        database_maxage_action: avg
        struct:
          - database.min
          - database.max

Dadurch entstehen solar.leistung.db_min und solar.leistung.db_max als vollwertige, eigenständig geloggte Items.

Bemerkung

Die Structs kopieren database_maxage/database_maxage_interval nicht vom übergeordneten Item - beide Kind-Items nutzen die gleichen Plugin-Parameter (default_maxage/default_maxage_interval) wie jedes andere Item auch, das diese Attribute nicht selbst setzt. Setzt das übergeordnete Item einen abweichenden, expliziten Wert für database_maxage (wie im Beispiel oben, 90), muss dieser bei Bedarf zusätzlich lokal auf den Kind-Items gesetzt werden:

solar:
    leistung:
        ...
        struct:
          - database.min
          - database.max

        db_min:
            database_maxage: 90
        db_max:
            database_maxage: 90

Datenbankfunktionen für Datenreihen/Plots

Nachfolgende Tabelle zeigt die implementierten Datenbankfunktionen für Plots. Die Funktionen werden dabei auf die verfügbaren Datenbankwerte eines bestimmten Intervalls, definiert mit t_start und t_end, ausgeführt und liefern Datenreihen zurück.

Funktion	Bedeutung
avg	Mittelwert
integrate	Diskretes Integral der Werte über der Zeit
differentiate	Diskretes Differential der Werte über der Zeit
diff	Differenz zu dem vorherigen Wert, funktioniert nur bei Monotonie
duration	Dauer des Wertes
count	Anzahl aller Werte, die eine bestimmte Bedingung erfüllen
countall	Anzahl aller Werte
min	Minimalwert
max	Maximalwert
on	Prozentzahl der Werte > 0
sum	Summe der Werte
raw	Rohwerte ohne Berechnung

Über das SmartVisu Widget plot.period können die genannten Datenbankfunktionen genutzt werden, um Plots der Werte zu erstellen. Beispiele finden sich in der SmartVisu Dokumentation unter plot.period.

Datenbankfunktionen für Einzelauswertungen

Das Plugin stellt außerdem Funktionen bereit, um Berechnungen über alle Werte innerhalb eines definierten Intervalls (t_start und t_end) zu machen und als genau ein Wert zurückzugeben. Diese Funktionen können dann z.B. aus Logiken heraus verwendet werden.

Folgende Funktionen werden hier unterstützt:

Funktion	Bedeutung
avg	Mittelwert
integrate	Diskretes Integral über der Zeit
count	Anzahl aller Werte, die eine bestimmte Bedingung erfüllen
countall	Anzahl aller Werte
min	Minimalwert
max	Maximalwert
diff	Differenz, funktioniert nur bei Monotonie
on	Prozentzahl der Werte > 0
sum	Summe aller Werte
raw	Rohwerte

Beispiele:

Integral aller Werte der letzten Woche, z.B. um Leistungen zu einem Verbrauch aufzuintegrieren

item.db('integrate','1w')

Differenz der Datenbank zwischen heute und vor einem Jahr:

item.db('diff','365d', 'now')

Datenqualität und Datenlücken

Wenn eine Datenquelle (z. B. ein Wechselrichter oder ein Cloud-Dienst) zeitweise nicht erreichbar ist, behält das Item in SmartHomeNG seinen letzten bekannten Wert. Ohne besondere Maßnahmen würde diese Zeitspanne fälschlicherweise als „Wert unverändert“ in der Datenbank gespeichert, was Mittelwerte und Energieberechnungen verfälscht.

Ab Schemaversion 7 unterstützt das Plugin explizite Datenlücken. Jeder Eintrag in der log-Tabelle trägt jetzt eine Spalte val_quality:

Wert	Bedeutung
0	Normaler, gültiger Messwert (Standard; alle alten Zeilen).
1	Keine Daten verfügbar (Lücke). Alle val_* Spalten sind NULL. Wird bei Aggregationen ausgeschlossen.

item.db_mark_invalid()

Öffnet eine Datenlücke in der Datenbank für dieses Item.

Das Plugin injiziert diese Methode auf allen registrierten Items in parse_item(). Sie wird vom Datenquellen-Plugin aufgerufen, wenn die Verbindung zur Datenquelle unterbrochen wird.

Der Python-Wert des Items bleibt dabei unverändert — die Methode wirkt ausschließlich auf den Datenbankpuffer.

# Beispiel: im solar-Plugin, wenn die Verbindung verloren geht
sh.solar.leistung.db_mark_invalid(caller='solar_plugin', source='connection_lost')

Parameter:

caller:

Optionaler Bezeichner des Aufrufers (erscheint im Log).

source:

Optionale Quellbeschreibung (erscheint im Log).

item.db_mark_valid()

Schließt eine offene Datenlücke für dieses Item.

Wird aufgerufen, wenn die Verbindung zur Datenquelle wiederhergestellt wird. Die offene Lücke erhält die berechnete Dauer zugewiesen. Anschließend sollte der neue Messwert normal gesetzt werden.

# Beispiel: im solar-Plugin, wenn die Verbindung wieder besteht
sh.solar.leistung.db_mark_valid(caller='solar_plugin', source='connection_restored')
sh.solar.leistung(neuer_wert, 'solar_plugin')

Parameter:

caller:

Optionaler Bezeichner des Aufrufers.

source:

Optionale Quellbeschreibung.

Auswirkung auf Abfragen

Einträge mit val_quality = 1 werden bei folgenden Funktionen automatisch ausgeschlossen:

avg, sum, integrate, on, min, max

Bei Rohwertabfragen (raw) erscheinen Lückeneinträge als NULL, damit Visualisierungen die Unterbrechung als echte Lücke darstellen können.

Implizite Revalidierung

Trifft ein neuer Messwert über update_item() ein, während für das Item noch eine offene Datenlücke besteht, schließt das Plugin die Lücke automatisch — ohne dass db_mark_valid() vorher explizit aufgerufen werden muss. Die Lückendauer wird dabei korrekt ab dem Öffnungszeitpunkt der Lücke berechnet, nicht ab der letzten regulären Wertänderung.

Das bedeutet: Wenn ein Gerät nach einem Verbindungsabbruch wieder Werte liefert, genügt es, den neuen Messwert direkt zu setzen. db_mark_valid() ist dann optional und muss nur explizit aufgerufen werden, wenn die Lücke geschlossen werden soll, bevor der erste neue Messwert bekannt ist.