© Luis Louro, 123RF

Datenbanken modernen Zuschnitts wie PostgreSQL bieten es Programmierern an, einen Teil ihrer Prozeduren direkt in die Datenbank zur Arbeit zu schicken. Das spart nicht nur jede Menge Datentransfers, sondern vereinfacht auch die Anwendungsstrukturen und macht sie sicherer.

Moderne Datenbanken taugen nicht nur als Ablageplatz für mehr oder minder umfangreiche Daten, auf die eine externe SQL-Anwendung zugreift. Features wie Stored Procedures, auch User Defined Functions (UDF) genannt, und Trigger verlagern wichtige Teile der Anwendungslogik an eine zentrale Stelle: in die Datenbank selbst. Dies bringt die Anwendung näher an die Daten und schafft für andere Applikationen ein zentrales Interface. Der Artikel will am Beispiel PostgreSQL 9.1 [1] zeigen, welche Möglichkeiten sich daraus in der Praxis ergeben. Als Grundlage der locker skizzierten Beispiele dient ein einfaches Shopsystem.

Gespeicherte Funktionen

Stored Procedures bieten dem Programmierer die Möglichkeit, eigene Programme direkt in den Kern der Datenbank einzulagern und dort laufen zu lassen. Teile der Anwendungslogik in der Datenbank auszuführen bringt Vorteile:

• Es entfällt der Transfer großer Datenmengen von der Datenbank zur Applikation und zurück.
• Die Daten liegen nur einmal im Speicher – innerhalb der Datenbank.
• Die Anwendung gerät schlanker und schneller. Der Gewinn fällt umso höher aus, je mehr Daten die Stored Procedure verarbeitet. Bei Performanceproblemen lässt sich der Datenbankserver durch einen stärkeren ersetzen, wovon alle Clients profitieren.
• Es fällt leichter, das Frontend auszutauschen, wenn bereits die Datenbank wesentliche Teile der Anwendungslogik realisiert.

Den Vorteilen steht der Nachteil gegenüber, dass sich der Programmierer von der einmal gewählten Datenbank abhängig macht, wenn er den Pfad von Standard-SQL verlässt. Diesen Umstand sollte man aber nicht zu hoch hängen, da kompakte und performante Datenbankanwendungen in der Praxis sowieso selten portabel sind.

Bunte Vielfalt an Sprachen

Weil SQL als Abfragesprache nicht sonderlich mächtig ist und zum Beispiel keine Schleifen und Variablen kennt, bietet PostgreSQL eine Reihe von komfortablen (Hoch-)Sprachen an, mit denen der Programmierer Stored Procedures verfassen kann. Die lassen sich grob in zwei Gruppen einteilen:

• Trusted Languages wie PL/SQL, PL/pgSQL [2], PL/Perl [3] oder PL/Python: Im Rahmen des Sicherheitskonzeptes von PostgreSQL darf jeder Datenbank-User Stored Procedures in diesen Sprachen komfortabel entwickeln und in die Datenbank übertragen. Er bleibt mit seinem Code jedoch effektiv in der Datenbank eingeschlossen. Die Sprachen bieten keine Möglichkeiten, auf das Filesystem zuzugreifen, externe Programme aufzurufen oder Netzwerkverbindungen zu öffnen.
• Untrusted Languages wie PL/Sh (führt ein Shellskript aus), PL/PerlU oder PL/PythonU. Das jeweilige “U” steht für die Untrusted-Versionen der Sprachen: Sie gestatten auf das Filesystem des Datenbankservers zuzugreifen und externe Programme aufzurufen, das Netzwerkverbindungen zu öffnen oder das Nachladen von Modulen.

Bei der Wahl der Sprache sollte der Entwickler im Blick behalten, dass er sich mit einer Untrusted Language nicht nur Security-Probleme bereiten kann, sondern gegebenenfalls auch das Transaktionsmanagement der Datenbank aushebelt: Eine mit einer Untrusted Language verschickte E-Mail wird die Datenbank kaum zurückholen können, wenn die Transaktion mit einem »ROLLBACK« endet.

PostgreSQL bietet eine modulare Schnittstelle, mit deren Hilfe Anwender neue Sprachen selbst definieren und einbinden können. Auf diese Weise hat die Datenbank inzwischen neben PLV8 (V8 Javascript Engine) und PL/Lolcode auch eine Reihe weiterer Sprachen gelernt. Die folgenden Beispielen beschränken sich jedoch auf PL/pgSQL. Diese Sprache ist sowohl sicher (trusted) als auch in allen neueren PostgreSQL-Versionen per se installiert und aktiv. Ältere Versionen enthalten die Sprache zwar auch, der Admin muss sie jedoch mittels »CREATE LANGUAGE« erst aktivieren.

Trigger ruft Stored Procedure auf

Datenbanktrigger lösen bei Änderungen in der Datenbank eine Aktion aus. Bei den SQL-Befehlen »INSERT« , »UPDATE« und »DELETE« können Trigger jeweils entweder pro Datensatz (per Row) oder pro Befehl (per Statement) anschlagen. Soll ein Kunde im Shopsystem zum Beispiel ab der dritten Bestellung als Stammkunde gelten, lässt sich das über den in Abbildung 1 definierten Trigger realisieren. Der Dialog zeigt die Ausgaben der Datenbank nicht an.

Aus dem ersten Select-Statement ist ersichtlich, dass das Feld »stammkunde« vom Typ »BOOLEAN« ist. »Kunde1« ist zu diesem Zeitpunkt noch kein Stammkunde und hat noch keine Bestellung getätigt – die entsprechende Tabelle ist leer. Das dritte SQL-Statement erstellt die PL/PgSQL-Procedure »stammkunde()« . Wird der danach mit »CREATE TRIGGER« angelegte Trigger aktiv, ruft er genau die Stored Procedure auf.

Beim Anlegen des Triggers ist das Besondere, dass dieser als »AFTER INSERT« definiert ist. Daher “sieht” die ausführende Prozedur den neu eingefügten Datensatz bereits. Im letzten Drittel von Abbildung 1 ist zu sehen, wie nach dem dritten eingefügten Datensatz (Bestellvorgang) das Feld »stammkunde« auf »t« (true) wechselt, da die Bedingung »> 2« erfüllt ist.

Abbildung 1: Im Beispiel wird der definierte Trigger immer dann aktiv, wenn ein Kunde bestellt. Die jedes Mal aufgerufene Stored Procedure zählt die Transaktionen und ändert beim dritten Mal den Kundenstatus.

Einen Lagerbestand überwachen

Trigger lassen sich auch kaskadieren: In Listing 1 verringert jede Bestellung via Trigger in der Tabelle »lager« den Bestand. Ein Eintrag in der Tabelle »bestellung« löst dazu bei jedem Bestellvorgang einen Trigger aus (Zeilen 12 bis 14). Die zum Trigger gehörende Prozedur »lagerentnahme()« verringert die Lagermenge des bestellten Artikel um die bestellte Menge (Zeile 7). Vorher soll das Beispiel noch prüfen, ob im Lager auch genügend Bestand vorhanden ist, um die Bestellung zu bedienen (Zeile 3). Außerdem wird die entsprechende Tabellenzeile gleich blockiert, damit nicht eine gleichartige parallele Transaktion den Bestand ebenfalls verringern kann.

Da kein Lager unerschöpflich ist, sorgt Listing 2 für dessen Bestückung. Sinkt der Warenbestand unter ein in der Tabelle »artikel« definiertes Minimum (»min_lagermenge« ), platziert Zeile 7 in die Tabelle »zu_bestellen« einen Eintrag und löst damit die Bestellung einer ebenfalls in »artikel« definierten Mindestbestellmenge beim Lieferanten aus.

01 CREATE or REPLACE function lagerentnahme() RETURNS TRIGGER AS $$
02 BEGIN
03   IF (SELECT bestand FROM lager WHERE artikel_id=NEW.artikel_id FOR UPDATE) < NEW.menge THEN
04     RAISE EXCEPTION 'Nicht genuegend Bestand im Lager';
05   END IF;
06
07   UPDATE lager SET bestand = bestand - NEW.menge
08   WHERE artikel_id = NEW.artikel_id;
09   RETURN NEW;
10 END; $$LANGUAGE plpgsql;
11
12 CREATE TRIGGER trg_lagerentnahme
13   AFTER INSERT ON bestellung
14   FOR EACH ROW EXECUTE PROCEDURE lagerentnahme();

01 CREATE OR REPLACE function bestellen() RETURNS TRIGGER AS $$
02 BEGIN
03   IF (NEW.bestand < (SELECT min_lagermenge FROM artikel WHERE id=NEW.artikel_id)
04     -- pruefen, dass nicht schon eine Bestellung ausgeloest wurde
05     AND NOT (SELECT EXISTS(select 1 FROM zu_bestellen WHERE artikel_id = NEW.artikel_id FOR UPDATE)))
06   THEN
07     INSERT INTO zu_bestellen (
08       artikel_id,
09       menge
10     )
11     SELECT
12       NEW.artikel_id,
13       (SELECT min_bestellmenge FROM artikel WHERE id = NEW.artikel_id);
14   END IF;
15 RETURN NEW;
16 END; $$LANGUAGE plpgsql;
17
18 CREATE TRIGGER trg_bestellen AFTER INSERT ON lager FOR EACH ROW EXECUTE PROCEDURE bestellen();

Funktioniert der Trigger?

Jetzt kann sich der Entwickler daran machen, die eben gesetzten Trigger und Stored Procedures zu testen. Zuerst verschafft er sich Gewissheit über die Ausgangslage des Shopsystems. Auf »SELECT min_lagermenge, min_bestellmenge FROM artikel WHERE id = 1;« antwortet es mit

min_lagermenge | min_bestellmenge
----------------+------------------
 10 | 20

Der Artikel mit der Nummer 1 sollte also mindestens zehnmal vorrätig sein. »SELECT bestand FROM lager WHERE artikel_id = 1;« dagegen zeigt den Ist-Stand:

bestand
---------
 11

Und »SELECT menge FROM zu_bestellen WHERE artikel_id = 1;« stellt klar, dass keine Nachbestellung läuft:

menge
-------
(0 rows)

Nun möge ein fiktiver Kunde auftauchen, der zwei Stück von Artikel 1 bestellt:

shop=# INSERT INTO bestellung (kunde, datum, artikel, menge) VALUES (1, current_date, 1, 2);

Dank des in Listing 1 definierten Triggers verringert sich gleichzeitig der Lagerbestand um zwei Stück. Dies löst wiederum den Trigger »trg_bestellen« der Tabelle »lager« (Listing 2) aus, da die Mindestlagermenge jetzt unterschritten ist und es keinen Eintrag in der »zu_bestellen« -Tabelle für diesen Artikel gibt. Mit »SELECT menge FROM zu_bestellen WHERE artikel_id = 1;« und der Antwort

menge
-------
 20
(1 row)

ist bewiesen, dass das Lager gerade eine Nachbestellung angefordert hat.

Writeable Common Table Expression

Die Tabelle »zu_bestellen« könnte nun regelmäßig ein Mitarbeiter oder ein Cronjob per Datenbankclient auslesen:

WITH nachbestellt AS (
 DELETE FROM zu_bestellen RETURNING *
) SELECT artikel_id, menge FROM nachbestellt;

Diesen Befehl genauer zu studieren lohnt sich, denn er nutzt das wenig bekannte Feature “Writeable Common Table Expression” (WCTE): Der erste Teil »WITH Tabellenname AS ()« erzeugt die dort benannte Zwischentabelle, die später referenzierbar ist. Der konkrete Fall löscht Einträge und bildet daraus die Tabelle »nachbestellt« , aus der im zweiten Teil alle Einträge selektieren werden. Auch wenn in solch einer WCTE zwei oder mehr Zwischenschritte folgen – die Datenbank führt die WCTE atomar aus, also in einem einzigen Schritt und damit innerhalb einer Transaktion.

Der Client darf diese Tabelle wie erwähnt auslesen, zum Beispiel eine E-Mail mit der Nachbestellung verschicken und dann die Löschung »COMMIT« -ten. Alternativ könnte ein Trigger auf der »zu_bestellen« -Tabelle die Nachbestellung anzetteln. Das würde allerdings bei jeder Kundenorder passieren und nicht gebündelt wie beim Ansatz per Cronjob.

Die Beispiele bisher haben ihre Trigger mit jeweils einer eigenen Funktion in der Sprache PL/pgSQL implementiert. Wenn es sinnvoll erscheint, kann der Programmierer beliebig viele Trigger pro Tabelle definieren, diese starten in alphabetischer Reihenfolge und sind zudem kaskadierbar.

Eingebettete Sicherheit

Datenbankfunktionen können sogar für Sicherheit sorgen – für Webanwendungen angesichts von SQL-Injections ein zentrales Thema. So sind Stored Procedures in der Lage, einem Webuser Zugriffe auf alle Tabellen gänzlich zu verbieten und ausschließlich Funktionen bereitzustellen, welche die für die Webanwendung nötigen Interaktionen mit der Datenbank abbilden. Passend zum Shopsystem-Beispiel legt folgender Code einen User mit sehr wenig Privilegien an:

CREATE USER webuser NOSUPERUSER;
REVOKE ALL ON DATABASE shop FROM webuser;
REVOKE ALL ON SCHEMA public FROM webuser;
GRANT USAGE ON SCHEMA public TO webuser;
GRANT EXECUTE ON FUNCTION select_bestellung(int) TO webuser;

»webuser« darf nur eine Funktion ausführen. Listing 3 zeigt deren Implementierung in reinem SQL, also ohne PL/pgSQL-Syntax (Zeile 6). Der Zusatz »SECURITY DEFINER« in derselben Zeile gibt an, dass PostegreSQL diese Funktion mit den Rechten des Erstellers ausführt: Hat der Benutzer, der die Funktion erstellt, das Recht, die Tabelle »bestellung« abzufragen, erbt es der ausführende User beim Ausführen der Funktion. Der darf deshalb auf die genannte Tabelle zugreifen – freilich nur mit dieser Funktion. Abbildung 2 zeigt ein entsprechendes Experiment als »webuser« .

Wichtig: Diese Vorgehensweise verhindert wirksam, dass ein Angreifer auf beliebige Daten Zugriff erlangt. SQL-Injections an sich verhindert der Mechanismus jedoch nicht! Dies kann nur eine weitere Sicherheitsschicht leisten.

01 CREATE OR REPLACE FUNCTION select_bestellung(id int)
02   RETURNS TABLE(datum date, artikel integer, menge integer) AS $$
03   SELECT datum, artikel, menge
04   FROM bestellung
05   WHERE kunde=$1
06 $$ LANGUAGE SQL SECURITY DEFINER;

Abbildung 2: Der »webuser« darf nur mit der Funktion »select_bestellung()« auf die Datenbank zugreifen.

Kauf mich!

Zurück zum konkreten Beispiel: Natürlich soll der Shop auch etwas verkaufen können. Dafür fehlt ihm noch eine Funktion, über die Kunden ihre Bestellung in Richtung Datenbank absetzen. Listing 4 zeigt sie: Zeile 7 prüft die übergebenen Daten, ob die Menge nicht kleiner 1 ausfällt. Ist die Menge plausibel, übertragen die Zeilen 10 und 11 die Bestelldaten in die Datenbank. Zeile 14 deklariert die ganze Prozedur wieder als gesicherte – die Webshop-Anwendung gestattet nur noch über die exakt definierte Schnittstelle den Zugang zur Datenbank.

01 CREATE OR REPLACE function kunde_bestellt(
02   kunde int,
03   artikel int,
04   menge int
05 ) RETURNS TEXT AS $$
06 BEGIN
07   IF (menge < 1) THEN
08     RAISE EXCEPTION 'gehts noch?';
09   END IF;
10   INSERT INTO bestellung (kunde, artikel, menge)
11     VALUES ($1, $2, $3);
12   RETURN 'Danke fuer Ihre Bestellung';
13 END;
14 $$ LANGUAGE PLPGSQL SECURITY DEFINER;
15
16 GRANT EXECUTE ON function
17   kunde_bestellt(int, int, int) TO webuser;

Vorsicht sollten Programmierer walten lassen, wenn eine gespeicherte Funktion einen oder mehrere Parameter übergeben erhält und diese innerhalb ihres Funktionskörpers in »WHERE« -Bedingungen verwendet. Dann kann der Planner diese Parameter natürlich vorher nicht kennen, was unter Umständen dazu führt, dass er einen Performance-mäßig eher ungünstigen Ausführungsplan wählt, um für jeden möglichen Fall gerüstet zu sein.

Wer einen Verdacht in dieser Hinsicht hegt, sollte mit dem »AUTO_EXPLAIN« -Modul und der Einstellung »auto_explain.log_nested_statements« einen detaillierten Ausführungsplan im Serverlog erzeugen – auch für die Vorgänge, die innerhalb seiner Stored Procedures ablaufen. Als Abhilfe kann man innerhalb der Stored Procedure dynamisch weitere Anfragen zusammenstellen und per »EXECUTE« ausführen. Das zwingt den Planner dazu, den Plan mit den aktuellen Daten neu und optimiert zu erstellen.

Viele Möglichkeiten

Neben dem Bedienen von Triggern und dem Hochziehen einer sicheren Zugriffsschicht, wie es der Artikel zeigt, eignen sich Stored Procedures auch hierzu:

• Sie prüfen über »CHECK« -Constraints Einträge in Tabellen auf inhaltliche Richtigkeit.
• Sie beschleunigen mit funktionalen Indexen Inhalte, die auf der Berechnung eines Feldinhalts beruhen.

Trigger sorgen ihrerseits dafür, dass die Datenbank selbsttätig (Routine-)Arbeiten auslöst. Dazu kann auch gehören, exakte Feldinhalte zu erzwingen. Entsteht zum Beispiel die Notwendigkeit, dass Artikelnummern aus Ziffern und Großbuchstaben zu bestehen haben, kann dies eine »CHECK« -Condition garantieren und ein Before-Trigger bei »INSERT« und »UPDATE« praktisch umsetzen. Stored Procedures und Trigger, die Minenarbeiter moderner Datenbanken, sind sich für keinen harten Job zu Schade. (jk)

01 create or replace
02 TRIGGER update_job_history
03   AFTER UPDATE OF job_id, department_id ON employees
04   FOR EACH ROW
05 BEGIN
06   add_job_history(:old.employee_id, :old.hire_date, sysdate, :old.job_id, :old.department_id);
07 END;

01 create or replace
02 PROCEDURE add_job_history
03   (  p_emp_id          job_history.employee_id%type
04    , p_start_date      job_history.start_date%type
05    , p_end_date        job_history.end_date%type
06    , p_job_id          job_history.job_id%type
07    , p_department_id   job_history.department_id%type
08    )
09 IS
10 BEGIN
11   INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id)
12     VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
13 END add_job_history;