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();
Anmerkungen zu diesem Code:
- 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.
- Ob man den Trigger wirklich auch für UPDATE-Befehle braucht oder nicht, hängt von der Anwendung ab. Mir ist kein sinnvoller Grund bekannt, die ID eines bestehenden Datensatzes zu ändern, also könnte man das "OR UPDATE" auch weglassen.
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. Hierzu habe ich zuerstm einmal Informationen gesammelt. In der Doku zu Annotationen steht grundsätzlich, wie man in Hibernate den ID-Generator auswählt. Dazu gibt es in Kapitel "2.2.3 Mapping Identifier Properties" die Beschreibung der Generatoren, die der JPA-Standard unterstützt. Darüber hinaus kann man in Hibernate aber auch noch weitergehende Generatoren benutzen, die in "2.4.2.1 Generators" beschrieben werden. Dort ist erklärt, daß man in GenericGenerator entweder eine der Hibernate "generator strategies" angeben kann oder einen FQCN einer Generatorklasse, die IdentifierGenerator implementiert. Solche Generatorklassen können auch die Enhanced Identifier Generators sein, die erst in Zukunft in Hibernate zum Einsatz kommen sollen.
Hier fängt es nun an, interessant zu werden: Man kann sich die vorhandenen Implementationen dieses Interfaces ansehen und eine eigene, neue Implementation erzeugen, die direkt macht, was wir wollen. Diese ist dann in einer ganz normalen Hibernate-Konfiguration über Ihren FQCN zu benutzen wie die Standard-Generatoren auch. Die Frage an dieser Stelle ist, welche der vorhandenen Generator-Klassen man am besten als Basis benutzt.
Meine obigen Überlegungen bei der Auswahl der Implementation meiner Lösung gingen ja erst mal davon aus, daß die Lösung komplett in der Datenbank stecken soll. Damit fallen einige der Implementationen wie Hi/Lo oder UUID aus. Letztlich habe ich dann (in PostgreSQL) eine Sequence benutzt. Also würde sich anbieten, den SequenceGenerator als Basis zu nehmen. Der hat allerdings das Problem, daß er auf Plattformen, die keine Sequences kennen, gar nicht geht. :-( Eine Lösung könnte sein, eine ganz eigene Klasse zu schreiben, die dann automatisch zwischen verschiedenen Lösungen auswählt. Da fiel mir noch ein anderer Weg auf: Die Klasse SequenceStyleGenerator ist zwar (zur Zeit) noch nicht offiziell eine der Haupt-Generatorstrategien, aber sie ist in Hibernate bereits enthalten: Sie benutzt eine Sequence und falls es eine solche nicht gibt, eine besondere Tabelle mit einem Eintrag. Also sollte die beste Lösung sein, diese portable Klasse als Basis zu benutzen und dann für die Datenbanken, die ich von Hand optimieren will, zusätzliche Trigger einzusetzen.
-- ThomasBayen