Turais

FHEM Logging in eine SQL-Datenbank

johannes -

Das File Logging in FHEM ist meiner Meinung nach wirklich nur für kleine Logging-Aufgaben oder für die schnelle Fehlerfindung geeignet. Deshalb hier eine kleine Beschreibung wie man das File-Logging auf einem FHEM-System auf eine SQL-Datenbank umstellt und so sein ganzes System performanter gestaltet. Nicht nur das betrachten der Log-Dateien, sondern auch das Backuppen geht gerade bei einer SQLite-Datenbank sehr einfach.

Entscheidung

Als erstes muss entschieden werden, was für eine Datenbank man sich installieren will. Wenn man nur FHEM Relevantes loggen will, eignet sich eine SQLite Datenbank perfekt fürs loggen. Denn die SQLite Datenbank bietet mehrere Vorteile.

Backups der SQLite

Da die SQLite Datenbank nur aus einem Datenbank-File besteht, lässt sich dieses sehr einfach Backuppen - indem man es einfach kopiert. Weiterhin kann man die Datenbank auch einfach editieren, z.B. mit einem SQLite-Editor wie: DB Browser for SQLite - sqlitebrowser. Den gibt es für Windows, Linux und MAC OS.

SQLite-Browser

Download links:

  • Windows einfach die sqlitebrowser exe herunterladen und installieren
  • MacOS X die dmg datei herunterladen und installieren
Linux:

Es gibt ein PPA zum installieren für Ubuntu Systeme. SqliteBrowser Stable PPA

Zum installieren geht man wie folgt vor:

sudo add-apt-repository ppa:linuxgndu/sqlitebrowser

Den Anweisungen folgen. Es sollte folgendes erscheinen:

gpg: keyring `/tmp/tmpg54_5rmz/secring.gpg' created
gpg: keyring `/tmp/tmpg54_5rmz/pubring.gpg' created
gpg: requesting key 7F2583EA from hkp server keyserver.ubuntu.com
gpg: /tmp/tmpg54_5rmz/trustdb.gpg: trustdb created
gpg: key 7F2583EA: public key "Launchpad PPA for Gajj GNDU" imported
gpg: Total number processed: 1
gpg:               imported: 1  (RSA: 1)
OK

Danach noch ein:
sudo apt-get update

Und jetzt ein:
sudo apt-get install sqlitebrowser

SQLite Datenbank installieren

Installiert wird die SQLite-Datenbank und die benötigten Perl-Connections mit:
sudo apt-get install sqlite3 libdbi-perl libdbd-sqlite3-perl

In das fhem Verzeichnis wechseln mit: cd /opt/fhem
Jetzt die Datenbank erstellen mit dem Befehl sudo sqlite3 fhemsql3.db anlegen.

Datenbank Schema anlegen: (siehe: SQL DB Creats FHEM)

CREATE TABLE current (TIMESTAMP TIMESTAMP, DEVICE varchar(32), TYPE varchar(32), EVENT varchar(512), READING varchar(32), VALUE varchar(32), UNIT varchar(32));
CREATE TABLE history (TIMESTAMP TIMESTAMP, DEVICE varchar(32), TYPE varchar(32), EVENT varchar(512), READING varchar(32), VALUE varchar(32), UNIT varchar(32));
CREATE INDEX Search_Idx ON `history` (DEVICE, READING, TIMESTAMP);

Copy-Pasten und Enter.
mit .exit die Datenbank verlassen.

Jetzt noch die Rechte ändern:
sudo chown fhem:root fhemsql3.db

sudo chmod 664 fhemsql3.db

FHEM-Datenbank Verbindung einstellen

Erstellt euch ein File configDB.conf in dem ihr folgendes reinschreibt:

%dbconfig= (
  connection => "SQLite:dbname=/opt/fhem/fhemsql3.db",
  user => "",
  password => ""
);
Logging in der fhem.cfg einstellen

Ich stoppe vorher immer FHEM mit:

sudo service fhem stop

Dann in der fhem.cfg:

define <NAME> DbLog <ORTUNDNAMECONFIGFILE> <REGULAREXPRESSION>

Beispiel:
define DBLog_Arbeitszimmer_Wandthermostat_Max DbLog ./configDB.conf Arbeitszimmer_Wandthermostat_Max:temperature:.*

FHEM starten

sudo service fhem start

Testen

In eurer Weboberfläche solltet ihr jetzt unter dem entsprechendem DBLog-Eintrag in etwa folgendes sehen:

state connected 2015-09-01 13:14:14

Datenbank mit dem SQLite Browser betrachten

Im SqliteBrowser auf:

Open Database -> Datenbank File
Dann geht ihr auf den Tab: "Browse Data" in die Table history.

Will man sich jetzt nur ein bestimmtes Device anzeigen lassen, geht das wie folgt:

SELECT * FROM history WHERE DEVICE='Arbeitszimmer_Wandthermostat_Max';

Wenn man sich nur das Datum und den Wert ausgeben lassen will:

SELECT TIMESTAMP, VALUE FROM history WHERE DEVICE='Arbeitszimmer_Wandthermostat_Max';

Weitere SQL-Befehle findet man auf: w3schools.com SQL Tutorial

Anpassen der *.gplot Dateien

Damit das Plotting wieder funktioniert solltet ihr eure *.gplot Dateien ändern, diese liegen in:

/opt/fhem/www/gplot

entweder ihr erstellt einen neuen Plot über die Weboberfläche, oder ihr editiert das File. Ändert dazu z.B. folgende Zeile:

Aus
#FileLog 4:Arbeitszimmer_Wandthermostat_Max.temperature\x3a::

wird:
#DbLog Arbeitszimmer_Wandthermostat_Max:temperature

Falls es Fragen und Anregungen gibt - gerne ab damit in die Kommentare :)

Anmerkung 21.05.16

Auch das SQLite Logging ist in FHEM nicht optimal gestaltet. Ich habe leider zur Zeit keine Zeit mich in den relevanten FHEM Source Code einzuarbeiten. Aber wenn jemand mal Zeit hat das ganze umzubauen in eine sinnvolle Datenbankstruktur, waere es cool wenn er mir bescheid sagt. Dann update ich hier meine Seite.

Meine Meinung sollte es in etwa so aussehen:

  • Table für Devices
  • Table für Events
  • ... etc. für alle redundanten Einträge eine extra Tabelle

z.B.: CREATE TABLE IF NOT EXISTS devices (id INTEGER PRIMARY KEY, device TEXT UNIQUE);

Die Insert-Methode sollte dann zuerst das Device hinzufügen:
INSERT OR IGNORE INTO devices(device) VALUES(...);

Dann den Rest einfügen und am Schluss einen Log Eintrag generieren in der Log-Table:
INSERT INTO logs(timestamp, ..., device_id, ..) VALUES(..., ..., (SELECT id FROM devices WHERE devices.device=...), ...);

Das da oben ist natürlich kein gültiger SQL-Befehl, und er muss noch durch die restlichen Tabellennamen etc. angepasst werden.

Aber damit würde man eine effiziente Datenbrankstruktur bekommen. Das Log würde nicht mit redundanten Informationen gespamt werden etc.

Weiterhin sollte der TimeStamp als INTEGER abgelegt werden und nicht als String. Falls mir mal wirklich langweilig ist kümmere ich mich mal darum.