SQLite Datenbank mit FHEM

RaspberryPi und SQLite
Quelle: https://randomnerdtutorials.com/sqlite-database-on-a-raspberry-pi

Einleitung

Wer kennt das nicht, die Log Files der FHEM Installation wachsen ins unermessliche.
Man verliert den Überblick, es wird chaotisch, fressen Platz auf dem Raspberry Pi *, Raspbian müllt sich voll und droht zu ersaufen, und man würde sich irgendwie doch eine kleine Datenbank dahinter wünschen. Eine Quelle der Inspiration ist immer das FHEM Wiki.
Welche Überlegungen müsste man sich hier denn machen?
Ein Ansatz wäre SQLite Datenbank mit FHEM zu betreiben, oder?

  1. Was ist SQLite überhaupt?
  2. Und wie zum Henker installiere ich das überhaupt?
  3. Wo speichere ich die Datenbank?
  4. Wie erstelle ich denn die Datenbank?
  5. Kann ich die Datenbank in mein FHEM einbinden?
  6. Gibt es Wege auf die Datenbank zuzugreifen?
  7. Kann ich die Datenbank auch sichern?
  8. Wie räume ich die Datenbank denn auf?

Zur ersten Frage gibt es folgendes Statement.
1. Die Englische Version der Beschreibung hat mir am meisten zugesagt um diese Frage zu beantworten:


SQLite is an embedded SQL database engine that provides a lightweight disk-based database.  It doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language.
SQLite suits well for IoT Devices as it is self-contained, serverless,  and requires no configuration. On top of this, it is free for use for any purpose.

SQLite Datenbank mit FHEM – SQLite Schnellstart

SQLite Logo

Also, fangen wir mal an mit der alles entscheidenen Frage.
2. Wie installiere ich SQLite auf Raspbian?

# Vorsicht, updatet Pakete und ggf. Kernel
sudo apt-get update
sudo apt-get upgrade

# Installation des Pakets
sudo apt-get install sqlite3 libdbi-perl libdbd-sqlite3-perl

Anhand diesem Befehl wird die version SQLite3 auf deinem Raspbian installiert und kann sofort genutzt werden.

Die Datenbank habe ich direkt in meinem FHEM Ordner /opt/fhem/fhem.db gesichert. Somit wäre Punkt 3. auch erschlagen.
Aber Stop, wieso da?
Um ehrlich zu sein, hatte ich vergessen einen Ordner anzulegen, wäre auch kein Problem die DB in einen unterordner anzulegen und dort zu betreiben.

Um eine SQLite Datenbank auf Raspbian zu erstellen benötigt Ihr folgenden Code.
Zum 4. könnt Ihr auf der bash folgenden Code eingeben

# Das komplette Prozedere
# Benutzer wechseln
su - fhem
<passwort eingeben>

# Ordner erstellen und hinein wechseln
mkdir /opt/fhem/fhemdb
cd /opt/fhem/fhemdb

# Datenbank erstellen und verbinden
sqlite3 fhemdb.db

# Notwendige Tabellen für FHEM erstellen
CREATE TABLE 'history' (TIMESTAMP TIMESTAMP, DEVICE varchar(64), TYPE varchar(64), EVENT varchar(512), READING varchar(64), VALUE varchar(128), UNIT varchar(32));

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

.exit

# Berechtigungen einstellen
chmod 666 /opt/fhem/fhem.db

FHEM Integration

FHEM Logo
Quelle: http://fhem.de/fhem_DE.html

So, nun habe ich alles gemacht was du gesagt hast. Wie bekomme ich das ganze nun in FHEM integriert?
5. In FHEM wird die dblog Funktion über eine Konfigurationsdatei definiert. In dieser Datei wird der Pfad zur unserer vorher erstellten Datebank definiert. Diese sollte wie folgt aussehen

# wir sind immernoch mit dem fhem Benutzer angemeldet
nano /opt/fhem/fhemdb/fhemdb.conf

# einfügen des folgenden Codes für die conf Datei
%dbconfig= (
  connection => "SQLite:dbname=/opt/fhem/fhemdb/fhemdb.db",
  user => "",
  password => ""
);
# Wenn die Datenbank mit einem Benutzer und Passwort gesichert werden soll, kannst Du diese Daten ebenfalls hier bei "user" und "password" hinterlegen.

Wir sind ziemlich auf der Zielgeraden!
Nun definieren wir wie gewohnt über das Webinterface das Device.
Aber Achtung!
Wir definieren nun das jedes Reading in die Datenbank loggt, ausnahmslos. Deshalb solltet Ihr euch auch über die obigen Punkte Gedanken machen. Trotz allem gebe ich euch ein Beispiel wie Ihr nur bestimmte Readings in die Datenbank loggt.

# Alle Reading werden in die DB geloggt
define logdb DbLog ./fhemdb/fhemdb.conf .*:.*

# Nur bestimmte Readings werden in die DB geloggt, einfach erweiterbar durch eure Readings von eurer Umgebung
define logdb DbLog ./fhemdb/fhemdb.conf .*:(temperature|humidity).*

# Hinweis: Wir benutzen zweimal den gleichen Namen des Device, damit ihr keine Probleme bekommt, falls Ihr beides probiert ;-)

# Damit nun auch die Tabellen gefüllt werden die wir angelegt haben, müssen wir die Attribute setzen für das zuvor angelegte Device
attr DbLog DbLogType Current/History

Sodele, wir haben die Datenbank angelegt, haben alle Einstellungen in FHEM durchgeführt und haben sie sogar eingebunden. Wie ich einen Plot erstelle, erkläre ich ein anderes mal
Heute gehts um den reinen technischen Teil der Datenbank, und dessen Wartung etc.

Also weiter mit dem 6. Punkt
Wie greife ich überhaupt auf eine SQLite Datenbank zu?
Prinzipiell bewegt sich bei einer SQLite Datenbank alles über die CLI. Eine schöne GUI hierfür habe ich noch gar nicht gesucht. Auf dem CLI Weg war ich bisher immer schneller.

Zur Syntax:
Bei SQLite fängt jeder Administrative Befehl mit einem . in Worten Punkt an.
Ein Beispiel: Zum aufrufen der Hilfe wird folgender genutzt

sqlite> .help
.archive ...           Manage SQL archives: ".archive --help" for details
.auth ON|OFF           Show authorizer callbacks
.backup ?DB? FILE      Backup DB (default "main") to FILE
                         Add "--append" to open using appendvfs.
.bail on|off           Stop after hitting an error.  Default OFF
.binary on|off         Turn binary output on or off.  Default OFF
.cd DIRECTORY          Change the working directory to DIRECTORY
.changes on|off        Show number of rows changed by SQL
.check GLOB            Fail if output since .testcase does not match
.clone NEWDB           Clone data into NEWDB from the existing database
.databases             List names and files of attached databases
.dbconfig ?op? ?val?   List or change sqlite3_db_config() options
.dbinfo ?DB?           Show status information about the database
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo on|off           Turn command echo on or off
.eqp on|off|full       Enable or disable automatic EXPLAIN QUERY PLAN
.excel                 Display the output of next command in a spreadsheet
.exit                  Exit this program
.expert                EXPERIMENTAL. Suggest indexes for specified queries
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.headers on|off        Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.imposter INDEX TABLE  Create imposter table TABLE on index INDEX
.indexes ?TABLE?       Show names of all indexes
                         If TABLE specified, only show indexes for tables
                         matching LIKE pattern TABLE.
.iotrace FILE          Enable I/O diagnostic logging to FILE
.limit ?LIMIT? ?VAL?   Display or change the value of an SQLITE_LIMIT
.lint OPTIONS          Report potential schema issues. Options:
                         fkey-indexes     Find missing foreign key indexes
.load FILE ?ENTRY?     Load an extension library
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         ascii    Columns/rows delimited by 0x1F and 0x1E
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by "|"
                         quote    Escape answers as for SQL
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.once (-e|-x|FILE)     Output for the next SQL command only to FILE
                         or invoke system text editor (-e) or spreadsheet (-x)
                         on the output.
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
                         The --new option starts with an empty file
                         Other options: --readonly --append --zip
.output ?FILE?         Send output to FILE or stdout
.print STRING...       Print literal STRING
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.save FILE             Write in-memory database into FILE
.scanstats on|off      Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?      Show the CREATE statements matching PATTERN
                          Add --indent for pretty-printing
.selftest ?--init?     Run tests defined in the SELFTEST table
.separator COL ?ROW?   Change the column separator and optionally the row
                         separator for both the output mode and .import
.session CMD ...       Create or control sessions
.sha3sum ?OPTIONS...?  Compute a SHA3 hash of database content
.shell CMD ARGS...     Run CMD ARGS... in a system shell
.show                  Show the current values for various settings
.stats ?on|off?        Show stats or turn stats on or off
.system CMD ARGS...    Run CMD ARGS... in a system shell
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.testcase NAME         Begin redirecting output to 'testcase-out.txt'
.timeout MS            Try opening locked tables for MS milliseconds
.timer on|off          Turn SQL timer on or off
.trace FILE|off        Output each SQL statement as it is run
.vfsinfo ?AUX?         Information about the top-level VFS
.vfslist               List all available VFSes
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
                         Negative values right-justify
sqlite>

Easy oder? Und alles in Betrieb auf unserem kleinen Rasperry Pi mit Raspbian!
Nun könnt Ihr hier einfach ableiten was Ihr wissen wollt. Datenbanken? Tabellen? Alles kein Problem!

# Öffnen unserer Datenbank
.open /opt/fhem/fhemdb/fhemdb.db

# Auflisten aller angehängten Datenbanken
.databases

# Auflisten aller Datenbanken
.tables

Und so weiter…

Datenbank Sicherung

Wie sicherst Du deine Datenbank Alex?
Ich hab den einfachsten Weg gewählt. Hierfür nutze ich die Backup Funktion von FHEM selbst. Hierfür habe ich ein “at” definiert das mir den Backup befehl in der Nacht ausführt.
Ein großer Vorteil hierbei, der komplette FHEM Ordner wird mitgesichert, inklusive unserer Datenbank!
Und wenn Dein Platz wie auf meinem Raspberry Pi * rar ist, kannst du das ganze mit meiner Anleitung in die Google Drive Cloud * hochladen.
Wenn Dir der Speicherplatz in der Google Drive Cloud zu wenig ist, kannst Du mit meinem Code für den G-Suite
Basistarif: 74QM6VD7WKWKT4V *
oder
der Code für den
G-Suite Businesstarif: E3T64V3XYDT7H3J *
20% auf den aktuellen Preis sparen!
Jetzt zugreifen!

Vorgehensweise

Nun der Ablauf zum Sichern der kompletten FHEM Umgebung.
Hier seht Ihr mein Backup Shell Script und den Crontab Eintrag der jede Nacht läuft. Das Log hierfür schreibe ich ebenfalls, damit ich kontrollieren kann falls was schief läuft!

# !/bin/bash

/home/pi/gdrive/gdrive upload `ls -rt /opt/fhem/backup/*.tar.gz | tail -1` &> /var/log/googledrivebackups.log
30 0 * * * /home/pi/fhemBackup.sh >/dev/null 2>>&1

Kommen wir zum letzten Punkt 8. in der Anleitung. Wie räume ich die Datenbank denn überhaupt auf? Wieso muss ich das überhaupt? Ist das wichtig?
Um es Dir kurz zu erklären.

  • Wir sichern die Datenbank durch FHEM.
  • Es werden alle Elemente in die Datenbank geloggt.
  • Es besteht nur begrenzter Platz
  • Die Datenbank wächst stündlich

Da wir nun die wichtigsten Bulletpoints notiert haben, wissen wir was zu tun ist.
–> Ein SQL Script über cron laufen lassen das meine SQL Datenbank aufräumt!

Da mein Raspberry Pi im Moment auf Eis liegt weil die Hardware den Löffel abgegeben hat, schreibe ich das Dokument fertig sobald die Hardware wieder läuft 🙂
Danach gehts weiter mit SQLite Datenbank mit FHEM!

Infrastruktur läuft wieder

So, mein Netzteil ist erneuert und die Raspberry Pi Infrastruktur mit FHEM ist wieder am Start.
Im nächsten Abschnitt könnt Ihr das über crontab gesteuerte SQL Script sowie den Inhalt des SQL Scripts sehen.

# crontab Eintrag
0 22 * * 0-6 /opt/fhem/fhemdb/dbWartung.sh 2>&1
# !/bin/bash
sqlite3 /opt/fhem/fhemdb/fhemdb.db <<EOS
	delete from history where timestamp between (Select min(timestamp) from history limit 1) and (Select date('now','-14 day'));
	vacuum;
EOS

Kurz erklärt was wir hier tun:

  • Einloggen auf der Datenbank fhemdb.db
  • <<EOS sagt das nun eine “Multiline variable” kommt. In unserem Fall das SQL Script
  • Das SQL Script löscht alle Daten in der “history” Tabelle die älter sind als 14 Tage
  • “vacuum” ruft den aufräum’ Prozess von SQLite auf
  • EOS sagt das die Variable hier Endet
Vorsicht! Kopiert nicht einfach Scripte auf euer System ohne zu Wissen was diese tun!

Ich würde sagen, das wars!
Wir haben eine lauffähige gewartete FHEM mit SQLite und dblog Umgebung.

Herzlichen Dank fürs lesen, und wenns euch gefallen hat so gebt mir doch einen Daumen hoch!

Euer Alex

4 0

Alex

veganer, ehemann, informatiker, techniker, sportler, teamleiter, chef, bastler, feuer macher, microsofti, appler, linuxer, natur liebhaber, wein interessiert

Das könnte Dich auch interessieren …

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Ich akzeptiere