= 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|http://www.hsqldb.org] benutzt [IDENTITY|http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#N10BEF], MySQL hat [AUTO_INCREMENT|http://dev.mysql.com/doc/refman/5.1/de/example-auto-increment.html] und PostgreSQL nimmt [SERIAL|http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-SERIAL].



== welche Lösungen gibt es in verschiedenen Datenbanken ==

Leider ist die Semantik dieser verschiedenen Lösungen überall leicht unterschiedlich:

* '''IDENTITY''' in HSQLDB ([Doku|http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#N10BEF]) erlaubt es nicht, Werte selber anzugeben. Die ID wird IMMER erzeugt. IDENTITY impliziert immer UNIQUE.
* '''SERIAL''' in PostgreSQL ([Doku|http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-SERIAL]) 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|http://dev.mysql.com/doc/refman/5.1/de/example-auto-increment.html]) 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

Darüber hinaus gibt es aber noch eine ganze Menge weiterer möglicher Herangehensweisen an das Problem. Insbesondere wer an grosse Datenbanken mit sehr vielen Benutzern denkt, die womöglich über einen Cluster von mehreren Servern verteilt sind und/oder eine optimale Performance erzielen will, merkt irgendwann, daß die Erzeugung von guten IDs nicht immer trivial ist. Einige sehr interessante Strategien muss man daher in der Applikation implementieren. Dies ist z.B. in JavaHibernate gemacht worden. Eine gute Übersicht gibt es in der [Hibernate Dokumentation hierzu|http://docs.jboss.org/hibernate/core/3.3/reference/en/html/mapping.html#mapping-declaration-id].



== 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...)''

Wenn man direktes Einfügen erlauben will, muss die gesamte ID-Logik zwangsläufig in der Datenbank stecken. Das bedeutet, man muss entweder mit den o.a. Eigenschaften oder mit Triggern arbeiten. Leider ist bei der Verwendung von Triggern die Plattformunabhängigkeit nicht mehr so ganz einfach herzustellen.

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|http://www.postgresql.org/docs/8.4/interactive/index.html] ist meine Vorstellung, daß man am einfachsten [SERIAL|http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-SERIAL] Spalten benutzt und diese dann mittels eines [Triggers|http://www.postgresql.org/docs/8.4/interactive/trigger-definition.html] 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|http://www.postgresql.org/docs/8.4/interactive/sql-createtrigger.html]-Befehl. Dieser ruft dann eine vorher definierte Funktion auf, die man einer von mehreren integrierten Sprachen, am einfachsten in [PL/pgSQL|http://www.postgresql.org/docs/8.4/interactive/plpgsql.html], implementiert. In der Doku steht auch ein [Beispiel für einen Trigger in PL/pgSQL|http://www.postgresql.org/docs/8.4/interactive/plpgsql-trigger.html].

DiesenBisher>> <<Trigger >>habe ich <<nundiesen>> <<folgendermassenTrigger>> <<implementiert:

{{{
--noch>> <<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ötigimplementiert. <<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|http://hsqldb.org/doc/2.0/guide/triggers-chapt.html], 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

----
[{Tag Datenbank}]