Vorgefertigte Prozeduren und Trigger sind mächtige Funktionen, die Abfragen in MariaDB beschleunigen.

MariaDB gehört zu den beliebtesten Open-Source-Datenbanken zur Entwicklung einfacher Web-Anwendungen. Die kleine, schnelle Datenbank kommt in vielen Projekten zum Einsatz und läuft selbst auf einem Raspberry Pi ohne Probleme. Dabei beherrscht MariaDB mittlerweile Funktionen, die sonst nur große kommerzielle Vertreter wie Oracle mitbringen. Um es nicht allzu theoretisch zu gestalten, dient im Folgenden das Erfassen von Messdaten, aus denen eine Software automatisch Statistiken erzeugt, als Beispiel für die Fähigkeiten.

Für das Experiment mit Prozeduren benötigen Sie zunächst eine Datenbank und eine Quelle für die Testdaten. Mit den Kommandos aus Listing 1 installieren Sie die Datenbank und alle nötigen Tools auf einem Raspberry Pi. Als Datenquelle kommt der Sensor BMP280 [1] zum Einsatz, der über die I²C-Schnittstelle mit dem RasPi verbunden ist.

$ sudo apt update
$ sudo apt upgrade
$ sudo apt install mariadb-server python3-smbus i2c-tools python3-pip
$ sudo pip3 install adafruit-circuitpython-bmp280
$ sudo pip3 install adafruit-blinka
$ pip3 install mariadb

Die Schnittstelle aktivieren Sie mit dem Tool Raspi-config unter 3 Interface Options | P5 I2C. Der BMP280 misst Luftdruck und Temperatur. Die Tabelle “BMP280 anschließen” zeigt, wie Sie den Baustein mit dem Raspberry Pi verbinden. Beachten Sie, dass Sie SDO auf 3,3 Volt legen, damit der Sensor die richtige I²C-Adresse verwendet. Mit dem Tool I2cdetect überprüfen Sie, ob die Verbindung steht (Listing 2). Der BMP280 meldet sich unter der Adresse 0x77.

$ i2cdetect -y 1
     0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f
00:          -- -- -- -- -- -- -- -- -- -- -- -- --
[...]
60: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
70: -- -- -- -- -- -- -- 77

Um auf dem Raspberry Pi die Datenbank und einen Datenbankbenutzer anzulegen, verwenden Sie die Befehle aus Listing 3. Listing 4 zeigt den Befehl, mit dem Sie das SQL-Skript anwenden und zugleich die Befehle, mit denen Sie den Zugriff von außen erlauben. Das letzte Kommando startet den Server neu, damit dieser die Änderungen übernimmt. Damit sind die Vorbereitungen beendet und die Datenbank steht bereit.

CREATE USER 'sensor'@'%' identified by 'sensor123!';
CREATE DATABASE sensor;
GRANT ALL PRIVILEGES ON sensor . * TO 'sensor'@'%';
FLUSH PRIVILEGES;

sudo mysql < create-user.sql
cd /etc/mysql
sudo echo "bind-address = 0.0.0.0" | sudo tee -a mariadb.conf.d/50-server.cnf
sudo systemctl restart mariadb

Gutes Werkzeug

Wie immer im Leben sind Geschick und Wissen nur die halbe Miete. Um effizient mit MariaDB zu arbeiten, benötigen Sie ein gutes Werkzeug, das Ihnen die Arbeit erleichtert. Es gibt viele Tools, die hier zur Auswahl stehen. Der Autor verwendet aktuell gern DBeaver [2]. Das Tool steht kostenlos für Windows, MacOS und Linux bereit. Für Linux gibt es unterschiedliche Varianten, unter anderem ein Paket für den RasPi.

Darüber hinaus gibt es ein Paket für die moderne und sehr mächtige Paketverwaltung Snap. Falls Sie tatsächlich kein Paket finden sollten, das zu Ihrem Betriebssystem passt, laden Sie den Quellcode aus Github [3] herunter. Ein Blick in diesen zeigt, dass die Entwickler noch fleißig an dem Projekt arbeiten. Es besteht also keine Gefahr, dass sie Bugs nicht mehr fixen.

DBeaver unterstützt von Haus aus über 80 Datenbanksysteme und bietet extrem viele Funktionen für den Entwickler. Zusätzlich sind die typischen Funktionen für den Administrator von Datenbanksystemen mit dabei. Bei Bedarf steuern Sie MariaDB klassisch von der Kommandozeile aus. Es fällt aber gerade Einsteigern erheblich einfacher, Prozeduren mit DBeaver zu erstellen und zu bearbeiten.

Um mit einer Datenbank zu arbeiten, legen Sie zuerst in DBeaver eine Verbindung an. Klicken Sie dazu oben links auf das Icon mit dem Stecker und wählen Sie MariaDB aus. Abbildung 1 zeigt die für das Beispiel nötigen Werte. Achten Sie darauf, die IP-Adresse Ihres RasPis einzutragen. Das Passwort lautet sensor123!. Mit Verbindung testen… überprüfen Sie, ob die eingegebenen Werte in Ordnung sind. Ein Klick auf Fertigstellen speichert die Verbindung zur Datenbank.

Abbildung 1: Mit DBeaver stellen Sie eine Verbindung zur Datenbank auf dem RasPi her.

Legen Sie nun über DBeaver eine neue Tabelle für die Temperaturmesswerte an. Klicken Sie sich dazu im Baum auf der rechten Seite bis auf die Ebene der Tabellen durch (Database Navigator | sensor | Tables). Ein Rechtsklick auf Tables öffnet ein Kontextmenü, in dem Sie Anlegen Tabelle auswählen (Abbildung 2).

Abbildung 2: Mit DBeaver erstellen Sie relativ schnell und einfach eine Tabelle für die Messwerte.

Zum Speichern der Tabelle klicken Sie auf Save. Das Programm zeigt Ihnen noch einmal das SQL-Statement, das es gegen die Datenbank ausführt. Das hat den Vorteil, dass Sie es noch einmal kontrollieren können. Der Button Persistieren speichert die Tabelle in der Datenbank.

Legen Sie jetzt noch eine zweite Tabelle (statistics) mit den Spalten min_value (float), max_value (float) und average an. Wollen Sie die beiden Tabellen von Hand anlegen, nutzen Sie Listing 5 und Listing 6. In Letzterem finden Sie am Ende eine Zeile, die alle Spalten mit jeweils dem Wert 1 füllt. Listing 7 zeigt, wie Sie die Skripte anwenden.

USE sensor
CREATE TABLE temperature (
  id int(11) NOT NULL AUTO_INCREMENT,
  time timestamp NULL DEFAULT current_timestamp(),
  value float DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=456 DEFAULT CHARSET=utf8mb4;

USE sensor;
CREATE TABLE statistics (
  min_value float DEFAULT NULL,
  max_value float DEFAULT NULL,
  avg_value float DEFAULT NULL,
  s_stmp timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO statistics (min_value, max_value, avg_value) VALUES(1,1,1);

$ sudo mysql < create-table-temperatures.sql
$ sudo mysql < create-table-statistics.sql

Daten sammeln

Zum Auslesen der Daten aus dem Baustein kommt das Python-Skript bmp280.py zum Einsatz, das die Messwerte auch in die Datenbank schreibt (Listing 8). Sie führen es mit dem Aufruf python3 bmp280.py aus. Um es nicht für jede Messung manuell starten zu müssen, richten Sie einen Cron-Eintrag dafür ein (Listing 9). Eine genaue Anleitung, wie Cron arbeitet, finden Sie im Netz [4].

#! /usr/bin/env python3
import board
import adafruit_bmp280
import mariadb
import sys
i2c = board.I2C()
sensor = adafruit_bmp280.Adafruit_BMP280_I2C(i2c)
USER="sensor"
PASS="sensor123!"
ADDR="192.168.3.119"
PORT=3306
DBNM="sensor"
SQLCMD="INSERT INTO temperature (value) VALUES (%s)"
try:
  conn = mariadb.connect(
    user=USER,
    password=PASS,
    host=ADDR,
    port=PORT,
    database=DBNM
  )
except mariadb.Error as e:
  print(f"Error connecting to MariaDB Platform: {e}")
  sys.exit(1)
sql=SQLCMD % format(sensor.temperature)
cur = conn.cursor()
cur.execute(sql)
conn.commit()
conn.close()

echo "* * * * * python3 /home/pi/bmp280.py" | crontab

Prozedur und Trigger

Bevor Sie das Ganze jedoch endgültig starten, lohnt es sich, die Datenbank noch etwas zu erweitern, und zwar um Stored Procedures (vorgefertigte Prozeduren). Dabei speichern Sie innerhalb der Datenbank kleine Programme, die Sie später in Verbindung mit SQL-Kommandos auslösen. Auf diese Weise lassen sich die Funktionen von reinem SQL erweitern. Um eine Prozedur zu starten, verwenden Sie das Schlüsselwort CALL, gefolgt vom Namen der Prozedur und gegebenenfalls den Parametern in Klammern.

Standardmäßig sieht die Software alle Parameter der Prozedur als reine Input-Parameter an. Wollen Sie das ändern, verwenden Sie in der Liste der Parameter eines der drei Schlüsselworte IN, OUT und INOUT.

Innerhalb von Prozeduren dürfen Sie Variablen benutzen. Diese erzeugen Sie mit dem Kommando DECLARE, gefolgt von Variablennamen und dem Datentyp: DECLARE Name INT;. Um einen Wert in der Variablen abzulegen, setzen Sie das Kommando SELECT Wert INTO Name; ab.

Den Wert einer Variablen fragen Sie mit SELECT ab (SELECT Name;). Beachten Sie, dass die Software immer nur die erste Variable ausgibt. Auch Zeichenketten lassen sich auf diese Art ausgeben (SELECT 'Hallo Welt';).

Das Kommando, um Programmcode nur unter bestimmten Bedingungen auszuführen, heißt – Sie ahnen es schon – IF. Zum If-Kommando gehören traditionell THEN, ELSE und END IF. Um eine Schleife innerhalb der Prozedur zu erzeugen, nutzen Sie FOR. Den Block der Schleife kennzeichnen Sie mit DO und END FOR. Eine Übersicht über die Befehle finden Sie auf der MariaDB-Homepage [5].

Trigger ermöglichen es, Prozeduren aufgrund von Ereignissen in der Datenbank auszuführen. Bei Ereignissen handelt es sich etwa um das Einfügen, Löschen oder Aktualisieren von Datensätzen. Der Trigger ist für denjenigen, der das Ereignis auslöst, vollkommen transparent.

Ein gutes Beispiel für einen Trigger liefert das Befüllen einer Audit-Tabelle. Jedes Mal, wenn ein Wert sich ändert, etwa in einer Tabelle von Gehältern, löst der Trigger aus und erzeugt über eine Prozedur einen zusätzlichen Eintrag in der entsprechenden Audit-Tabelle. So lässt sich nachvollziehen, wer wann ein Gehalt angepasst hat (und ob dabei alles mit rechten Dingen zuging).

Zu guter Letzt lohnt es sich, einen Blick auf den SQL-Modus zu werfen. Diesen stellen Sie mit der Anweisung SQL_MODE=ORACLE so ein, dass die Datenbank die SQL-Syntax von Oracle unterstützt. Damit stehen dann Funktionen bereit, die es so nur in PL/SQL gibt. Eine Übersicht der vorhandenen PL/SQL-Kommandos in der MariaDB finden Sie in der Dokumentation [7].

Ein praktisches Feature im Oracle-Modus ist das Exception Handling von PL/SQL, das es ermöglicht, auf Laufzeitfehler innerhalb des Programms zu reagieren. Aktuell gibt es die Ausnahmen TOO_MANY_ROWS, NO_DATA_FOUND und DUP_VAL_ON_INDEX.

Zusammenspiel

Was machen Sie nun mit Triggern und Prozeduren? Im Beispiel fehlt beim Speichern der Messdaten etwa Folgendes: Es gibt weder einen Zeitpunkt zu den Messwerten noch einen Schlüssel, um einen Wert eindeutig zu machen. Sie benötigen also einen Trigger, der die fehlenden Daten vor dem Einfügen in die Tabelle ergänzt. Dazu verwenden Sie das Schlüsselwort NEW, das in diesem Kontext die neu anzulegenden Daten repräsentiert.

Um einen Trigger zu erstellen oder zu bearbeiten, bietet DBeaver ein gut zu bedienendes Frontend an. Um den Trigger anzulegen, navigieren Sie im Baum zu Databases | sensor | Tables | statistics | Triggers und wählen Anlegen Trigger. Der Trigger erstellt eine einfache Statistik (Listing 10).

CREATE DEFINER=`sensor`@`%` TRIGGER statistics
AFTER INSERT
ON temperature FOR EACH ROW
BEGIN
  DECLARE v_min float;
  DECLARE v_max float;
  DECLARE v_avg float;
    SELECT MAX(value) FROM temperature t INTO v_max;
    SELECT MIN(value) FROM temperature t INTO v_min;
    SELECT AVG(value) FROM temperature t INTO v_avg;
    UPDATE statistics
      SET min_value = v_min,
          max_value = v_max,
          avg_value = v_avg;
END

Das Auslagern der Statistik in eine gesonderte Tabelle bringt bei großen Datenbeständen und vielen Zugriffen enorme Vorteile in Bezug auf die Geschwindigkeit, da die Datenbank nicht immer den kompletten Datenbestand heranzuziehen braucht, um einen Mittelwert auszugeben.

Vorteile

Der Einsatz von Stored Procedures bietet viele Vorteile. Neben den zahlreichen Möglichkeiten, die kleine Programme und Trigger in der Datenbank bieten, können Sie je nach Aufgabenstellung viel schnellere SQL-Abfragen aufbauen. Damit umgehen Sie etwa zeitaufwendige Sub-Selects. Es stellt auch kein Problem dar, die komplette Business-Logik innerhalb der Datenbank abzubilden. Das bietet viele Vorteile bei Anwendungen, die intensiv mit den Daten arbeiten müssen.

Neben den bereits genannten Vorteilen spielt der Sicherheitsaspekt eine wichtige Rolle: Anwendungen, die über Stored Procedures mit der Datenbank kommunizieren, sind immun gegen die gefürchteten SQL-Injection-Attacken. Ein Angreifer kann über die Prozedur keine SQL-Fragmente ausführen.

Fazit

Es bietet fast nur Vorteile, Programmcode innerhalb der Datenbank auszuführen. Der einzige Nachteil liegt darin, dass Sie eine zusätzliche Komponente mit in der Anwendung haben, die Sie warten müssen. Allerdings muss sich zumindest ein Entwickler im Team in dieses Thema hineinarbeiten.

Wenn Sie die freie Datenbank MariaDB in einem Projekt einsetzen wollen, ist es auf jeden Fall sinnvoll, sich mit dem Thema Prozeduren auseinanderzusetzen. Sie arbeiten an vielen Stellen einfach schneller als traditionelles SQL und bieten viel bessere Möglichkeiten, um mit den Daten umzugehen. (cla)