BVA-System

Heute möchte ich meine kleine Datenbanksystem-Unterschied-Serie zum Abschluss bringen.  Als letztes geht es um einen kleinen SQL-Befehl, der im Gegensatz zu den anderen Unterschieden, im alltäglichen Betrieb benötigt wird. Es geht um die Frage:

Wie kann ich die Datensatz-ID des zuletzt gespeicherten Datensatzes erhalten?

Fügt man in eine beliebige Tabelle mit Hilfe eines „INSERT INTO …“-Befehls einen Datensatz hinzu, so weiß man im Normalfall nicht, welcher Wert per Autoincrement/Trigger für das ID-Feld generiert wurde. Diesen Wert benötigt man allerdings, wenn weitere Daten in einer abhängigen Tabelle (foreign keys) gespeichert werden sollen.

Eine Lösung wäre, das man den gerade eingefügten Datensatz wieder selektiert und das ID-Feld ausliest. Dies ist aber nicht notwendig, da alle 3 Datenbanksysteme für diesen Fall eine einfachere Lösung bieten.

MySQL

MySQL besitzt eine Funktion, mit der man die zuletzt hinzugefügte ID erfragen kann. Der Funktionsaufruf ist per SELECT-Befehl möglich und sieht wie folgt aus:

SELECT LAST_INSERT_ID() AS ID

Wichtig ist hier, das der Befehl gleich nach dem INSERT aufgerufen wird, da sich der Wert bei einem weiteren INSERT ändert.

Firebird

Bei Firebird kann man den zur Datenbanktabelle gehörenden Generatorwert erfragen. Es ist ein wenig aufwendiger als bei MySQL, da man für unterschiedliche Tabellen unterschiedliche Generatoren abfragen muss. Die Abfrage sieht wie folgt aus:

SELECT gen_id(GEN_BVA_CREATIONTREE, 0) AS ID from rdb$Database

Vorteil ist hier, das der Generatorwert jederzeit abgefragt werden kann. Auch ohne INSERT kann der aktuelle Wert des Generators abgefragt werden.

Oracle

Mit einer ähnlichen Abfrage kommt man bei Oracle zu seinem ID Wert.  Sie sieht wie folgt aus:

SELECT GEN_BVA_CREATIONTREE.CURRVAL AS ID FROM DUAL

Im letzten Teil war bereits für Firebird und Oracle ein Datenbanktrigger enthalten, der die Aufgabe hatte, das Auto-ID Feld zu füllen. Der Vollständigkeit halber möchte ich heute einen einfachen Trigger für alle 3 Datenbanksysteme vorstellen. Da die syntaktischen Unterschiede zwischen den Triggern nicht größer sein könnten, werde ich die 3 SQL-Befehle relativ unkommentiert im Raum stehen lassen. Die Möglichkeiten des einzelnen Datenbanksystems sind in den jeweiligen Dokumentationen besser erfasst. Bevor ich jetzt auf die 3 Datenbanksysteme eingehe, möchte ich aber kurz erläutern was ein Trigger ist.

Was ist ein Trigger?

Ein Datenbanktrigger ist eine Datenbankfunktionalität, die in eigentlich jedem größeren Datenbanksystem integiert ist. Man kann sich einen Trigger wie ein kleines Programm vorstellen, das bei bestimmten Datenbankaktionen ausgeführt wird. Beispielsweise kann vor dem Einfügen eines Datensatzes geprüft werden, ob die Daten valide sind. Oder es wird ein Trigger erstellt, der das Erstellungsdatum des Datensatzes speichert.

Genau so ein Trigger soll mein Beispiel für den Vergleich zwischen MySQL,Firebird und Oracle sein. In der Tabelle bva_image speichert der Trigger in dem Feld mod_date das aktuelle Systemdatum ab, sobald ein Datensatz in der Tabelle hinzugefügt wird.

Trigger unter MySQL

CREATE TRIGGER  bva_image_insert_timestamp
 BEFORE INSERT on bva_image
 FOR EACH ROW
  SET NEW.mod_date = NOW();

Trigger unter Firebird

CREATE TRIGGER bva_image_insert_timestamp FOR bva_image
 BEFORE INSERT
 AS
 BEGIN
  NEW.mod_date = CURRENT_TIMESTAMP;
 END

Trigger unter Oracle

CREATE OR REPLACE TRIGGER IMAGE_INSERT_TIMESTAMP
 BEFORE INSERT ON BVA_IMAGE
 FOR EACH ROW
 BEGIN
  :new.mod_date := SYSDATE;        
 END;

Im zweiten Teil meiner kleinen Serie soll es nun um die Definition eines Auto-ID Feldes gehen. Ein Auto-ID Feld ist ein Datenbankfeld, welches automatisch, beim Einfügen eines neuen Datensatzes, gefüllt wird. Meist wird für diese Zwecke ein Zahlenfeld genutzt. Der erste Datensatz erhält die ID 1, der zweite die 2, der dritte die 3 usw. Benötigt werden die Auto-ID Felder recht häufig, da mit ihnen Verknüpfungen zu anderen Datenbanktabellen, so genannte „Foreign Keys“, hergestellt werden können.

Auto-ID Feld unter MySQL

Wie einfach es ist, unter MySQL ein Auto-ID Feld zu erzeugen, hatte ich bereits im Teil 1 erwähnt. Man muss nur bei der Erzeugung einer Tabelle beim gewünschten Feld das Schlüsselwort AUTO_INCREMENT hinzufügen. Als Datenbankentwickler freut man sich sicher, das man so schnell fertig ist.  Aber man stößt sehr schnell an eine Grenze, wenn man komplexere Sachen umsetzen möchte. Zum Beispiel ist es nicht ohne weiteres möglich, eine eindeutige ID über mehrere Tabellen zu definieren.

Auto-ID Feld unter Firebird

Unter Firebird sind mehrere Einzelschritte notwendig, um ein Auto-ID Feld zu definieren. Als erstes wird logischerweise wie in Teil 1 bereits beschrieben die Tabelle angelegt. Als zweiten Schritt erzeugt man sich einen sogenannten Generator, der die jeweils zuletzt genutzte ID speichert. Die Erzeugung eines Generators erfolgt mit folgendem SQL-Befehl:

CREATE GENERATOR GEN_BVA_CREATIONTREE

Als nächstes muss der Generator mit einem Wert initialisiert werden. Im Beispiel wird der Generator mit dem Wert 0 initialisiert.

SET GENERATOR GEN_BVA_CREATIONTREE TO 0

Als letztes und wichtigstes Element wird ein Trigger benötigt, der vor dem Einfügen eines Datensatzes ausgeführt wird. Der Trigger erhöht den Generatorwert um 1 und trägt den Wert in das gewünsche Auto-ID-Feld der Tabelle ein.

CREATE TRIGGER ID_TRIGGER_BVA_CREATIONTREE FOR BVA_CREATIONTREE
 BEFORE INSERT
 AS
 BEGIN
  NEW.ID = GEN_ID(GEN_BVA_CREATIONTREE,1);
 END

Hier ist es möglich über mehrere Tabellen einen eindeutigen Index zu erzeugen. Dafür muss man in den jeweiligen Triggern den gleichen Generator nutzen.

Auto-ID Feld unter Oracle

Oracle arbeitet prinzipiell nach dem gleichen Konzept wie Firebird. Allerdings heißt der Generator bei Oracle Sequenz. Außerdem entfällt der Initialisierungsschritt, da die Initialisierung bereits bei der Erstellung der Sequenz erfolgt. Die Befehlsfolge für Oracle sieht wie folgt aus:

CREATE SEQUENCE GEN_BVA_CREATIONTREE
 START WITH 1
 INCREMENT BY 1
 NOMAXVALUE
 NOCACHE
 ORDER;

CREATE OR REPLACE TRIGGER ID_TRIGGER_BVA_CREATIONTREE
 BEFORE INSERT ON BVA_CREATIONTREE
 FOR EACH ROW
 BEGIN
  SELECT GEN_BVA_CREATIONTREE.nextval INTO :new.id FROM DUAL;
 END;
/

Vor einiger Zeit schrieb ich, das eine Datenbankverbindung per ODBC den Vorteil hat, das sie für jedes Datenbanksystem funktioniert, welches ODBC unterstützt. Aktuell, nach der Entwicklung des Administrationstools, muss ich diese Aussage leicht korrigieren. Richtig ist, das die Standard SQL-Befehle, wie SELECT, INSERT, UPDATE usw, mit jedem Datenbanksystem genutzt werden können. Nutzt man allerdings speziellere Dinge wie zum Beispiel SQL-Befehle zum Anlegen von Datenbanktabellen, dann unterscheidet sich die Syntax von Datenbanksystem zu Datenbanksystem.

Da ich gerade beim Schreiben der Scripte zum Erstellen der BVASystem-Datenbankstruktur für MySQL, Firebird und Oracle bin, möchte ich heute eine kleine Serie starten, welche die Unterschiede zwischen diesen 3 Datenbanksystemen zusammenfasst. Dabei werde ich allerdings nur auf die Unterschiede eingehen, die mir im Zusammenhang mit dem BVASystem aufgefallen sind.

Starten möchte ich heute mit den unterschiedlichen Syntax des SQL-Befehls „CREATE TABLE“. Im folgendem werde ich an der Tabelle „bva_creationtree“ zeigen, wo die  Unterschiede zwischen den Datenbanksystemen liegen.

CREATE TABLE unter MySQL

Richtig bequem finde ich bei MySQL die Möglichkeit ein Auto-ID Feld zu generieren. Hierfür muss einfach neben das gewünschte Feld das Schlüsselwort „AUTO_INCREMENT“ geschrieben werden. Bei Firebird und Oracle werden die Auto-ID Felder über eine Kombination aus Generator/Sequenz und Trigger gelöst. Damit dieser Blog nicht zu unübersichtlich wird, werde ich im nächsten Teil noch einmal ausführlich auf das Thema eingehen.

Als zweite Besonderheit bei MySQL fällt auf, das am Ende des Befehles eine „Engine“ ausgewählt werden kann. MySQL unterstützt unterschiedliche Speicherengines. Je nach Einsatzzweck unterscheiden sich die Speicherengines in ihrer Performance. Da mir die Transaktionssicherheit recht wichtig ist, nutze ich die langsamere InnoDB-Engine.

Der komplette SQL-Befehl für die bva_creationtree Tabelle sieht wie folgt aus:

CREATE TABLE bva_creationtree (
 id INT NOT NULL AUTO_INCREMENT,
 parent_id INT ,
 caption VARCHAR(10)  NOT NULL,
 PRIMARY KEY (id),
 INDEX fk_bva_creationtree (parent_id ASC),
 CONSTRAINT fk_bva_creationtree
   FOREIGN KEY (parent_id)
   REFERENCES bva_creationtree (id)
) ENGINE = InnoDB

CREATE TABLE unter Firebird

Schaut man sich den gleichen SQL-Befehl für Firebird an, so sieht er auf dem ersten Blick relativ ähnlich aus.

CREATE TABLE bva_creationtree (
 id INT NOT NULL,
 parent_id INT,
 caption VARCHAR(10) NOT NULL,
 PRIMARY KEY (id),
 CONSTRAINT fk_creationtree
   FOREIGN KEY (parent_id)
   REFERENCES bva_creationtree (id)
 )

Auf dem zweiten Blick fällt dann auf, das für den Fremdschlüssel „fk_creationtree“ kein zusätzlicher Index definiert wurde. Der zusätzliche Index ist bei Firebird nicht notwendig, da er mit dem Constraint zusammen anglegt wird. Das Auto-ID Feld „id“ muss wie bereits beschrieben manuell angelegt werden. Ansonsten gibt es keine weiteren Unterschiede.

CREATE TABLE unter Oracle

Größere syntaktische Unterschiede gibt es bei Oracle. Als erstes fällt auf, das sich die Datentypbezeichnungen unterscheiden: NUMBER(38) anstelle von INT, VARCHAR2() anstelle von VARCHAR() und INT anstelle von FLOAT. Bequem ist, das einfache Primärschlüssel oder auch Constraints direkt hinter der Felddefinition angegeben werden können. Alternativ können sie aber  beispielsweise auch per „CONSTRAINT pk_creationtree PRIMARY KEY (id)“ angelegt werden.

Nach dem Ende des eigentlichen Create Table SQL-Befehlt gibt man bei Oracle an, in welchem Tablespace die Daten der Tabelle gespeichert werden sollen und wie die Tabelle sich verhalten soll, wenn sie zusätzlichen Speicher benötigt. Die dort getätigten Einstellungen haben Auswirkungen auf die Performance. Speichert man große Datenmengen in einer Tabelle, ist es sinnvoll sie in größeren Schritten zu vergrößern, da die Vergrößerung dann nicht mit jedem neuen Datensatz durchgeführt werden muss.

Der vollständige SQL Befehl für die Beispieltabelle sieht wie folgt aus:

CREATE TABLE bva_creationtree (
 id NUMBER(38) NOT NULL PRIMARY KEY,
 parent_id NUMBER(38),    
 caption VARCHAR2(10) NOT NULL,
 CONSTRAINT fk_creationtree
   FOREIGN KEY (parent_id)
   REFERENCES bva_creationtree (id)  
 )
 TABLESPACE bva
 STORAGE
  ( INITIAL     1M
    NEXT         1M
    PCTINCREASE 0
    MINEXTENTS     1
    MAXEXTENTS     UNLIMITED
  )