Postgres Rulez Zeitregeln

- WebmasterWork »
- Knowledge base »
- Software »
- Postgres Rulez Zeitregeln »

Lieber Besucher, herzlich willkommen bei: WebmasterWork. Falls dies dein erster Besuch auf dieser Seite ist, lese dir bitte die Hilfe durch. Dort wird dir die Bedienung dieser Seite näher erläutert. Darüber hinaus solltest du dich registrieren, um alle Funktionen dieser Seite nutzen zu können. Benutze das Registrierungsformular, um dich zu registrieren oder informiere dich ausführlich über den Registrierungsvorgang. Falls du dich bereits zu einem früheren Zeitpunkt registriert hast, kannst du dich hier anmelden.

nocturne

Kaiser

Dabei seit: 06.01.2005

Beiträge: 1 197

Donnerstag, 20. Mai 2010, 14:25

Hallo und willkommen zu einem Tutorial zu PostgreSQL.
Zeitregel mit PostgreSQL
Erklärung
Das ist ein Tutorial für fachlich korrekte Datenbankänderungen. Konkret sollen in zwei Datumsangaben einer Tabelle die Werte verglichen werden und im Problemfall jegliche Änderungen mit einer definierten Fehlermeldung abgebrochen werden.
Das ist kein Tutorial für Trigger auf Datenbankebene (eine Validierung ist keine Trigger-Aufgabe).

Einführung
Hier bei WMW ist die übliche Datenbank die MySQL Datenbank, das ist auch gut so. Ein paar Entwickler kommen in das Verlangen einen validen und authentischen Datenstand zu haben. Dieses Tutorial ist, theoretisch, auch auf MySQL anwendbar, jedoch primär auf PostGreSQL ausgerichtet.
Ich werde ein paar fachbegriffe verwenden, nicht um euch zu ärgern, sondern weil es international standartisierte Begriffe sind.
Beispiel
Wir haben zum Beispiel eine Tabelle 'veranstaltungen'.
Diese Tabelle hat 4 Felder.
1. veranstaltung_id vom typ integer als Primärer schlüssel
2. name vom typ VARCHAR
3. zeit_start vom typ TIMESTAMP
4. zeit_ende vom typ TIMESTAMP

Jetzt soll verhindert werden dass ein Benutzer eine Veranstaltung, mit einem zeit_ende nach seinem zeit_start speichert!

Abgrenzung
Wir nutzen die "RULES" und die "FUNCTIONS" von Postgres.
In den "RULES" erfolgt die Authentifizierung des Datenbestandes.
In den "FUNCTIONS" erzeugen wir eine Server-Fehlermeldung.

Technische Voraussetzungen
Es ist wichtig, dass wir auf die Datenbank schreiben können und dass die DBSprache plpgsql erstellt wurde (createlang plpgsql).

Ausgangssitouation
Wir sollten die Zieltabelle(z.bsp. veranstaltungen) bereits erstellt haben.

Erstellen der FUNCTION
Eine Funktion kann überall aufgerufen werden, in where-klauseln, order-by-klauseln, usw. und wird deshalb im Schema der Datenbank erstellt.
Sie soll einen Fehlertext übergeben bekommen der das Problem beschreibt. Diese funktion ist 'nur' ein einzeiler, aber sehr wichtig.

	MySQL-Abfrage(n)
1 2 3	CREATE OR REPLACE FUNCTION "exception" (varchar) RETURNS boolean AS 'BEGIN RAISE EXCEPTION '%',$1; END;' LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100;

Weil ich niemanden dumm sterben lassen will, hier eine kleine Erklärung:
"exception" - ist der Name der Funktion/Prozedur.
(varchar) - ist der Typ des ersten parameters.
RETURNS boolean - ist der Rückgabewert, wir brauchen ihn jedoch gar nicht!
BEGIN - markiert den Begin der pl-Anweisungen.
RAISE EXCEPTION '%',$1 - Wirft einen Ausnahmefall(Exception) mit dem inhalt (%) des ersten Parameters ($1, unser varchar).
END - markiert das Ende der pl-Anweisungen.
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100; - beschreibt den Umgang (mit) der Funktion, nichts Großartiges also!

Eine pl-Anweisung bedeutet nichts anderes als eine pascal-Anweisung, es freuen sich hier Delphi/Pascal-Freunde also zu recht! Es geht übrigends auch mit anderen Sprachen wie c, java usw. (php leider noch nicht).
Haben wir die Funktion also erstellt können wir mit

	MySQL-Abfrage(n)
1	SELECT exception('wie bitte?');

die Ausführung abbrechen.

Erstellen des RULES
Ein Rule wird an die Datenbankaktion einer Tabelle gebunden und damit automatisch bei Berührung der Tabelle aktiviert.
Sollte also jemand ein INSERT durchführen wollen wir das RULE ausführen.

MySQL-Abfrage(n)

1
2
3

CREATE RULE "startNachEnde" AS ON INSERT TO "veranstaltung"
WHERE new.zeit_start > new.zeit_ende
DO (SELECT exception('Veranstaltungsende muss vor Veranstaltungsstart liegen!'::character varying) AS exception;);

Damit nicht genug, der alte Hase weis genau: Auch bei UPDATE wollen wir das RULE ausführen.

MySQL-Abfrage(n)

1
2
3

CREATE RULE "startNachEnde2" AS ON UPDATE TO "veranstaltung"
WHERE new.zeit_start > new.zeit_ende
DO (SELECT exception('Veranstaltungsende muss vor Veranstaltungsstart liegen!'::character varying) AS exception;);

Wieder lasse ich niemanden dumm sterben.
"startNachEnde2" - bezeichnet den Namen des RULE's.
ON UPDATE TO "veranstaltung" - bedeutet wörtlich übersetzt "Bei Änderung an 'veranstaltung' ..."
WHERE new.zeit_start > new.zeit_ende - ist die Where-Klausel der Anweisung
SELECT exception('...') AS ...; - ist das Ausführen der Fehlermeldung

Fazit
Es gibt auch komplexere Anwendungsfälle die dieses Tutorial bei weitem überschreiten.
Hier ist ein Rule-Beispiel für erfahrene Postgresser:

MySQL-Abfrage(n)

CREATE OR REPLACE RULE "IllegalTimeRange" AS ON UPDATE TO "public"."event"
WHERE (
	SELECT COUNT(*) FROM "public"."event"
    WHERE NEW.eventroomid=eventroomid 
    AND NEW.eventid <> eventid
    AND(NEW.timewhen >= timewhen AND NEW.timeuntil <= timeuntil 
    	OR NEW.timewhen <= timewhen AND NEW.timeuntil >= timeuntil
	    OR NEW.timeuntil - interval '1 second' BETWEEN timewhen AND timeuntil
    	OR NEW.timewhen BETWEEN timewhen AND timeuntil - interval '1 second'
    )
    
) > 0
DO SELECT exception('err.EventCollision');

Bei fragen stehe ich immer zur Verfügung, euer
nocturne.

nocturne

Kaiser

Dabei seit: 06.01.2005

Beiträge: 1 197

Mittwoch, 2. Juni 2010, 10:56

Es gibt sehr wohl ein eine Sprache für PL/PHP die heist plphp. Wenn jemand zuviel Zeit hat kann er gerne seine Erfahrungen posten.

MySQL-Abfrage(n)

CREATE FUNCTION plphp_max (integer, integer) RETURNS integer AS '
	if ( !isset($args[0]) ){
			if ( !isset($args[1]) ){
						return;
			} else return $args[1];
	}
	if (!isset($args[1])){
		return $args[0];
	}
	if ($args[0] > $args[1]){
		return $args[0];
	} else return $args[1];
 ' LANGUAGE 'plphp';