AUTO_INCREMENT mit PostgreSQL #
In jedem besseren Datenbanksystem gibt es eine Methode, um Schlüsselfelder automatisch zu erzeugen. Das bedeutet, daß ich mehrere Datensätze in die Datenbank einfügen kann und jeder dieser Sätze einen eindeutigen (zumeist numerischen) ID-Wert zugeordnet bekommt. Auf diese Art kann ich später jeden einzelnen Datensatz genau identifizieren. Man kann die Erzeugung eines solchen eindeutigen Schlüssels der Applikation überlassen oder dies in der Datenbank machen. Letzteres ist in vielen Fällen komfortabler und weniger fehleranfällig.
Der SQL-Standard benutzt für dieses Problem sogenannte Sequences. Dennoch haben sich verschiedene Methoden und Syntaxen durchgesetzt, mit dem Problem umzugehen. HSQLDB
benutzt IDENTITY, MySQL hat AUTO_INCREMENT und PostgreSQL nimmt SERIAL.
welche Lösungen gibt es in verschiedenen Datenbanken #
Leider ist die Semantik dieser verschiedenen Lösungen überall leicht unterschiedlich:
- IDENTITY in HSQLDB (Doku) erlaubt es nicht, Werte selber anzugeben. Die ID wird IMMER erzeugt. IDENTITY impliziert immer UNIQUE.
- SERIAL in PostgreSQL (Doku) erlaubt es, eigene Werte anzugeben. Allerdings wird die zugrundeliegende Sequence davon nicht verändert, so daß man also in die Situation kommen kann, wo die SERIAL-Sequenz einen Wert liefert, den man vorher bereits manuell gesetzt hat. Konsequenterweise ist dann auch UNIQUE nicht automatisch impliziert (kann aber natürlich angefügt werden).
- AUTO_INCREMENT in MySQL (Doku) lässt die Angabe von eigenen Werten zu. Ein automatisch generierter Wert ist aber garantiert immer höher als der höchste bisherige Wert. Diese Semantik erscheint mir als die logischste. -- ThomasBayen
.
Wo liegt das Problem? (Was ist denn nun am besten) #
Für meine (ThomasBayen) Bibliothek (LugFramework) suche ich einen ID-Generator, der folgende Eigenschaften hat:
- Mehrbenutzerfähig
- unbedingtes Verhindern von doppelten Werten
- direktes Einfügen von eigenen Datensätzen in die Datenbank (also ohne meine Bibliothek) sollte keine Inkonsistenzen erzeugen (können)
- Es muss möglich sein, IDs bereits vorzugeben - ansonsten ist die Replikation einer Datenbank (bzw. auch das Kopieren von Teilen davon) mit vorhandenen Fremdschlüsseln nicht möglich.
- Betrieb auf mehreren Datenbanksystemen möglich (ich unterstütze zur Zeit HSQL, MySQL und PostgreSQL)
- Konfiguration in JavaHibernate möglich
- (wer weitere interessante Punkte hat, darf die hier gerne anfügen...)
Meine Überlegungen ergeben, daß ich eigentlich genau das möchte, was MySQL mit AUTO_INCREMENT macht. Dummerweise benutzt mein Projekt im Moment PostgreSQL, so daß ich das dort implementieren möchte.
Umsetzung für PostgreSQL #
Nach eingehendem Studium der PostgreSQL Dokumentation ist meine Vorstellung, daß man am einfachsten SERIAL Spalten benutzt und diese dann mittels eines Triggers anpasst. Ich müsste also immer dann, wenn ein ID-Wert bereits vorgegeben wird, nachsehen, ob ich die zugrundeliegende Sequence ggf. auf einen höheren Wert setzen muss, damit weitere, automatisch erzeugte IDs immer größer sind als die manuell eingefügte. Wird kein ID-Wert vorgegeben (was der Normalfall sein sollte), wird der Trigger gar nicht ausgeführt, dann kommt die normale SERIAL-Logik zum Zuge. Auf diese Art wird im Normalfall der Zugriff nicht groß ausgebremst.
Eine Alternative könnte sein, daß man auf die Sequence ganz verzichtet und beim Einfügen immer ein MAX() ausführt. Laut der MySQL-Dokumentation wird das dort so gemacht. Ich denke jedoch, daß das von der Performance her nicht optimal ist. (Im übrigen denke ich, daß da auch die Doku nicht die Wahrheit sagt und intern bestimmt eine Art Sequence-Zähler benutzt wird.)
Einen Trigger setzt man mit dem CREATE TRIGGER-Befehl. Dieser ruft dann eine vorher definierte Funktion auf, die man einer von mehreren integrierten Sprachen, am einfachsten in PL/pgSQL, implementiert. In der Doku steht auch ein Beispiel für einen Trigger in PL/pgSQL.
Diesen Trigger habe ich nun folgendermassen implementiert:
-- DROP LANGUAGE IF EXISTS plpgsql CASCADE;
-- CREATE LANGUAGE plpgsql;
CREATE TABLE test(
id serial unique,
name char(20)
);
CREATE FUNCTION test_id_trigger() RETURNS TRIGGER AS $$
BEGIN
IF NEW.id IS NOT NULL THEN
IF NEW.id > currval('test_id_seq') THEN
PERFORM setval('test_id_seq',NEW.id,true);
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER test_id_trigger BEFORE INSERT OR UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE test_id_trigger();
Obwohl in der Dokumentation steht, daß der CREATE LANGUAGE Befehl in der Standardinstallation für PL/pgSQL nicht nötig ist, war es das doch. Wahrscheinlich waren die Debian Maintainer da mal wieder vorsichtiger als die Upstream-Autoren.
Den Namen der Sequence ("test_id_seq") habe ich mit pgAdmin3 aus der Datenbank geholt. Er wird automatisch durch SERIAL erzeugt und man muss ihn ansonsten raten.
Ab PostgreSQL 9.x gibt es für den "CREATE TRIGGER" Befehl eine "WHEN Condition". Damit kann man den Fall, daß die übergebene ID NULL ist schon abfangen, bevor die Funktion überhaupt aufgerufen wird. Obige Lösung per IF-Befehl innerhalb der Funktion habe ich mit der Version 8.4 gemacht.
-- ThomasBayen
Umsetzung für HSQLDB #
Auch HSQLDB erlaubt die Verwendung von Triggern, allerdings habe ich das bisher (noch) nicht umgesetzt.
Übertragung in Hibernate #
Wünschenswert wäre, wenn man in Hibernate eine eigene Generator-Klasse hätte, so daß man den hier vorgestellten ID-Generator genauso auswählen kann, wie die anderen, in Hibernate vorgegebenen, Varianten. -- ThomasBayen