TaffyDB und serverseitige Daten synchronisieren

TaffyDB zeigt, wo die Reise hingeht: Mit der JavaScript Schnittstelle wird die Datenhaltung von Web-Applikationen in den Browser gehievt. Wer auf die serverseitige Speicherung nicht verzichten kann oder will, bekommt hier einen Eindruck, wie leicht TaffyDB mit jQuery zusammen als Datenbank-Interface für relationale Datenbanken werden kann.

TaffyDB ist eine CRUD (Create, Read, Update, Delete) Schnittstelle, die JavaScript Objekte zu Kollektionen zusammenfasst und sie komfortabel verwalten und durchsuchen lässt. Die Operationen erinnern namentlich stark an SQL, so dass die Einarbeitungszeit für altgediente Freunde relationaler Datenbanken auf ein Nichts minimiert. Die Daten sind flüchtig, also verloren, sobald die aktuelle Seite neu geladen oder verlassen wird. Dauerhafte Speicherung erzeugt man nur mit entsprechendem Zusatz, etwa Google Gears und/oder der Übertragung in eine Datenbank auf dem Server (z.B. MySQL oder CouchDB).

Eben diese Anbindung an eine – in diesem Beispiel – relationale Datenbank, erreicht man mit einer Handvoll Ajax und vordefinierten Hooks in TaffyDB selbst in Form von drei Methoden, die auf die Ereignisse Insert, Update und Remove reagieren. Den Ajax-Teil realisieren wir in jQuery, jedes vergleichbare JavaScript Bibliothek erledigt dies auf gleich einfache Weise. Im Beispiel sieht das wie folgt aus:


<script src="jquery-1.3.2.min.js" type="text/javascript"></script>
<script src="taffy-min.js" type="text/javascript"></script>
<script type="text/javascript"><!--
var records = new TAFFY([])
records.onInsert = function (r) {
jQuery.ajax({
type: "POST",
url: "save.php",
data: "action=insert&b="+r.a,
success: function(msg){
alert( "Data Saved: " + msg );
}
});
};
records.onRemove = function (r) {
jQuery.ajax({
type: "POST",
url: "save.php",
data: "action=remove&b="+r.a,
success: function(msg){
alert( "Data deleted: " + msg );
}
});
};
// --></script>
Daten:
<input id="d" maxlength="30" name="d" size="30" type="text" value="zyx" />
<input onclick="records.insert({a:document.getElementById("d").value})" name="add" type="button" value="add" />
<input onclick="records.remove({a:document.getElementById("d").value})" name="remove" type="button" value="remove" />
<input onclick="records.forEach(function (f,n) {alert(f.a)});" name="show" type="button" value="show" />

Im JavaScript Teil definieren wir zuerst eine leere Kollektion durch die Instanziierung der Klasse TAFFY. Die Kollektion dient uns als Sammelbecken für Daten, die wir gemeinsam vorhalten und beispielsweise durchsuchen wollen – im relationalen Denkbild entspricht dies einer Tabelle. An dieser Stelle lassen sich schon Objekte vordefinieren, die im Folgenden jedoch nicht mit synchronisiert werden. Darüber hinaus belegen wir die Hooks zum Einfügen (onInsert) und Löschen (onRemove) mit Ajax Aufrufen. Damit werden Änderungen an der Kollektion Datensatz für Datensatz mit dem Bestand auf dem Server synchronisiert – namentlich durch Verarbeitung des Datensatzes im Skript save.php. Pro forma quittieren wir jede erfolgreiche Synchronisierung mit einem alert().

Der HTML Teil besteht aus einem Eingabefeld und drei Knöpfen, die den Inhalt des Textfeldes als Objekt an die Taffy Kollektion anhängen oder daraus entfernen. Der letzte Knopf gibt uns nach und nach alle Objekte der Kollektion aus.

Beim Drücken des add-Knopfes wird die Methode insert der Kollektion aufgerufen. Dies erweitert die Kollektion und sorgt für den Aufruf des Hooks onInsert, der die Daten darüber hinaus zum Server schickt. Der remove Knopf reagiert analog. Sicherheit der Synchronisation wird mit dieser einfachen Version des Skriptes nicht erreicht!

Serverseitig muss die Verarbeitung der Daten, die als POST Parameter daherkommen, durch das Skript save.php geschehen, die im Folgenden dargestellt ist.

< ?php
if($_POST['b'] != ""){
$fehler = "";
$sqldb = new SQLiteDatabase('test.sqlite2', 0666, $fehler);
if($_POST['action'] == "insert"){
$sqldb->queryExec("INSERT INTO d (b) VALUES ('".$_POST['b']."');", $fehler);
} elseif ($_POST['action'] == "remove"){
$sqldb->queryExec("DELETE FROM d WHERE b='".$_POST['b']."';", $fehler);
}
echo $_POST['b'];
}?>

Der Einfachheit halber werden Daten in einer SQLite v2 Datenbank hinterlegt. Je nach Ausprägung des Parameters action wird der (nicht leere) String b in die Datenbank eingefügt oder daraus gelöscht. Das Löschen beachtet dabei nicht, dass ein gleichlautender String vorab in der Tabelle vorhanden sein kann. Beim Löschen eines der Datensätze würden beide entfernt werden.

Verschlüsselung für Google Calendar

Google ist ein zweischneidiges Schwert. Die kostenosen Services GMail, Docs&Sheets, die App Engine und etliche weitere, sind zweifelsfrei mehr als brauchbar. Mit zunehmendem Maß an möglicherweise sensiblen Daten, Sie dem Suchmaschinenriesen überlassen, sollte auch das gesunde Misstrauen steigen, was damit geschieht.

Um sicher zu gehen, dass Google trotz seines Grundsatzes „Don’t do evil“ nichts Ungewolltes mit Ihren Kontakten, Mails, Terminen etc anfängt, empfiehlt sich der Aufwand, die Daten unkenntlich zu machen. Dies ist bei Google Calendar besonders einfach. Die Kalenderapplikation lässt sich via API mit unterschiedlichen Clients ansprechen, seien es fertige Programme wie Mozillas Sunbird/Thunderbird oder Programmierschnittstellen, beispielsweise zu PHP. Das folgende Beispiel zeigt die Anpassungen an der Mozilla Thunderbird Erweiterung „Provider for Google Calendar“, die Ihre Termine für Googles Augen wie Müll aussehen lässt, ohne Sie in Ihrer täglichen Handhabe einzuschränken. Voraussetzung für das Beispiel ist, dass sowohl Thunderbird als auch die Erweiterungen Lightning und „Google Provider“ installiert sind und ein Google-Kalender eingebunden ist (siehe Installationsanleitung , siehe Abbildung 1).

Implementierung

Die Suche nach den zentralen Stellen im Code von Google Provider zu Ver- und Entschlüsseln führt uns in die JavaScript-Datei calGoogleUtils.js im Unterordner profile/extensions/{id}/js, genauer gesagt zu den Funktionen ItemToXMLEntry und XMLEntryToItem. Mit der ersten Funktion wird ein internes Kalenderobjekt zum Transport zu Google in ein XML Format verpackt. Die zweite Funktion ist das Gegenstück zur Umwandlung in ein Objekt. Exemplarisch kodieren wir hier lediglich den Titel eines Eintrages. Sinnvoll ist dieses Vorgehen aber auch für den Ort und die Beschreibung (siehe Abbildung 2). Aus der Zeile

entry.title = aItem.title;

in ItemToXMLEntry wird dann beispielsweise

entry.title = "{enc}"+Base64.encode(aItem.title);

Wir wählen hier im Beispiel eine simple – und nicht besonders sichere – Base64 Kodierung. Eine Implementierung dieses Algorithmus findet sich im Link weiter unten. Die Klasse kann einfach am Ende der Datei calGoogleUtils.js angefügt werden. Der Phantasie des Entwicklers ist hier jedoch keine Grenze gesetzt, so dass auch bekannte Verschlüsselungsvertreter wie AES oder Blowfish schnell zum Einsatz kommen können. Eine Ressourcenliste zu JavaScript-Implementierungen dieser Algorithmen ist nebenstehend zusammengefasst. Da wir die Verschlüsselung auch rückgängig machen wollen, sind an dieser Stelle Einweg-Algorithmen wie MD5 oder SHA ausgeschlossen.

Wenn Sie das obige Beispiel unverändert einsetzen, sieht ein beispielhafter Termin wie etwa

"Dies ist ein Testtermin"

für Google und alle Kalenderbenutzer, die sich direkt auf der Webseite von Google Calendar einloggen, so aus (siehe auch Abbildung 3):

{enc}RGllcyBpc3QgZWluIFRlc3RzdHRlcm1pbg==

Das Präfix {enc} ist nicht zwingend notwendig. Es ist an dieser Stelle nur eingefügt, weil wir beim Dekodieren unterscheiden wollen, ob ein Termin verschlüsselt ist oder nicht.

In der Funktion XMLEntryToItem machen wir dazu aus der Zeile

item.title = aXMLEntry.title.(@type == 'text');

das folgende if-Konstrukt:

var decodeString = aXMLEntry.title.(@type == 'text');
if (decodeString.substring(0,5) == "{enc}") {
item.title = Base64.decode(decodeString.substring(5));
} else {
item.title = decodeString;
}

Nicht verschlüsselte Termine werden somit unverarbeitetet in Thunderbird angezeigt (aber mit obiger Änderung beim nächsten Speichern verschlüsselt. Eine geeignete Anpassung beim Verschlüsseln verschafft Abhilfe.). Kodierte Termine landen allerdings genauso im Klartext auf Ihrem Bildschirm.

Unser Beispiel lässt viel Platz für Erweiterungen. Base64 ist nicht zu empfehlen, wenn Sie die Verschlüsselungsanforderung ernst nehmen. Besser geeignet sind oben genannte Algorithmen. Wenn Sie darüber hinaus die verschlüsselten Termine in Ihrem Google Calendar durchsuchen wollen, ergibt sich die Zusatzanforderung nach einem zeichenbasierten Algorithmus. Dies könnte der Fall sein, wenn Sie den Online-Terminspeicher mit unterschiedlichen Applikationen ansprechen (siehe PHP API). Ein solcher Vertreter ist etwa die XOR-Verschlüsselung (Vernam Cipher).

Links

Performance der SCD2-Lösung

Der Einsatz der von mir beschriebenen Lösung zur „Emulation“ von Slowly Changing Dimensions macht nur dann Sinn, wenn der Durchsatz von Daten akzeptabel hoch ist. Um das zu überprüfen, habe ich einige Performancetests auf MySQL-Basis gemacht.

Erzeugen von Datasets

Zuerst benötigen wir Daten, die wir dann per Bulkload in unsere Schattentabelle laden können. Dazu erstellen wir mit einem einfachen PHP-Skript randomisierte Datensätze. Der Quellcode für das PHP-Skript ist an diesen Artikel angehängt. Ergebnis nach Ausführung ist eine Reihe von Dateien von jeweils n Datensätzen – mit dem Namen n.txt.

Standardmäßig habe ich die folgenden Datenmengen erstellt: 100,500,1000,5000,10000,50000,100000. Für die Darstellung der Ergebnisse und die Zeitmessung sind noch weitere Dateien hinzugekommen.

Testreihe 1 – Laden der Dateien nach MySQL

Die Dateien werden dann nacheinander in die Datenbank geladen. Dies geschieht über den Befehl

LOAD DATA INFILE './n.txt' INTO TABLE cds_schatten FIELDS TERMINATED BY ';' LINES TERMINATED BY 'rn';

Nach einem Load werden die Daten wieder entfernt.

Die Ergebnisse sind ernüchternd. Ohne zusätzliche Strukturen wie Indizes verhundertfacht sich die Ausführungszeit bei der Verzehnfachung der Testdatenmenge. Abhilfe schafft ein kombinierter UNIQUE-Index über die Felder ean und gueltig_bis (in genau dieser Reihenfolge) in der Tabelle cds. Dieser Index bringt zwei Vorteile: Zum einen wird dadurch sichergestellt, dass es pro EAN keine zwei gültigen Datensätze gibt, da gültige Datensätze ja immer denselben Wert in der Zukunft haben. Zum anderen wird dadurch das Attribut ean über den Index auswertbar – eine Eigenheit von MySQL ist, dass bei kombinierten Indizes Anfragen auf die zuerst definierten Indexteile davon profitieren. Mehr zu den MySQL-Index-Eigenschaften unter nebenstehendem Link.

Testreihe 2 – Laden der Dateien in Tabellen mit Indizes

Das Vorgehen bei dieser zweiten Testreihe bleibt natürlich das gleiche. Der Index verfehlt seine Wirkung nicht. Die Zeit zum Laden der Daten steigt nun proportional zur Datenmenge. Der Durchsatz liegt bei auswertbaren Datenmengen gleichbleibend hoch bei über 5000 Rows pro Sekunde. Alle Ergebnisse sind im nebenstehende PDF tabellarisch und grafisch zusammengefasst.

Slowly Changing Dimensions Typ 2 mit MySQL

Über das folgende Problem bin ich neulich bei dem Aufbau eines DWH gestolpert. Eine Datenbank-Tabelle wird per Bulkload befüllt, soll aber als Slowly Changing Dimension (SCD) vom Typ 2 historisiert werden. Der Bulkload wird per Perlskript angestoßen, das nicht unter der eigenen Hoheit liegt und somit nicht mit eigener Logik ausgestattet werden kann. Die Lösungsidee ist einfach: nur mit Bordmitteln der Datenbank eine SCD Typ2 emulieren…
Gegeben ist die folgende, historisierte Dimensionstabelle – in unserem Beispiel in MySQL 5.0.51 hinterlegt:

CREATE TABLE IF NOT EXISTS `cds` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`ean` varchar(13) collate latin1_general_ci NOT NULL,
`md5` varchar(32) collate latin1_general_ci NOT NULL,
`titel` varchar(200) collate latin1_general_ci default NULL,
`interpret` varchar(200) collate latin1_general_ci default NULL,
`jahr` bigint(4) default NULL,
`gueltig_ab` datetime NOT NULL,
`gueltig_bis` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

Die Tabelle besitzt zwei unterschiedliche Schlüssel: ID ist ein technischer Schlüssel, der über AUTO_INCREMENT hochgezählt wird. Die Spalte EAN, die für Handelsware gebräuchliche European Article Number, ist der Fachschlüssel eines jeden Datensatzes. Neben diesen beiden Schlüsseln existiert noch die Hilfsspalte MD5, in der die fachlich relevanten Spalten, in diesem Fall der Titel, der Interpret und das Erscheinungsjahr, gehasht werden. Mehr zu dem Sinn der Hashspalte, wenn wir zu den Historisierungsmechanismen kommen.

Erzeugen einer Schattentabelle

Um die SCD2 emulieren zu können, benötigen wir einen Schatten zur Zieltabelle: Wir erzeugen eine zweite Tabelle ‚cds_schatten‘, die strukturell ähnlich zu ihrem Vorbild ist, nur die Historisierungsfelder und die ID entfallen. Die Schattentabelle hat also das Format, das wir von der Bulkload-Datei erwarten.

DROP TABLE IF EXISTS `cds_schatten`;
CREATE TABLE IF NOT EXISTS `cds_schatten` (
`ean` varchar(13) collate latin1_general_ci NOT NULL,
`md5` varchar(32) collate latin1_general_ci NOT NULL,
`titel` varchar(200) collate latin1_general_ci default NULL,
`interpret` varchar(200) collate latin1_general_ci default NULL,
`jahr` int(11) default NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Als Storage Engine wählen wir BLACKHOLE. Datensätze, die wir in diese Tabelle schreiben, wollen wir nämlich nicht permanent behalten. Wir benötigen sie nur, um die eigentliche Zieltabelle ‚cds‘ zu befüllen.

Implementierung der Historisierung

Die Historisierung wird nach dem folgenden Schema vorgenommen: Ein neuer Datensatz wird anhand des fachlichen Schlüssels, der EAN, als Version eines vorhandenen Satzes erkannt. Die Speicherung macht aber nur dann Sinn, wenn sich die Inhalte eines der fachlichen Attribute verändert hat, sprich, wenn sich die Hashes der beiden Versionen unterscheiden. Wir laden den Datensatz also zuerst in die Schattentabelle und schauen dann nach, ob sich gemäß dem oben definierten Schema die Notwendigkeit ergibt, den neuen Datensatz in die eigentliche Zieltabelle zu übertragen. Das realisieren wir über einen einfachen BEFORE INSERT Trigger:

DROP TRIGGER IF EXISTS `test`.`bi_cds_schatten`;
DELIMITER //
CREATE TRIGGER `test`.`bi_cds_schatten` BEFORE INSERT ON `test`.`cds_schatten`
FOR EACH ROW BEGIN
DECLARE hash VARCHAR(32);
SELECT md5 INTO hash FROM cds WHERE ean=NEW.ean AND gueltig_bis>NOW();
IF ((hash IS NULL) OR (hash<>NEW.md5)) THEN
UPDATE cds SET gueltig_bis=NOW() WHERE ean=NEW.ean AND gueltig_bis>NOW();
INSERT INTO cds (ean,md5,titel,interpret,jahr,gueltig_ab,gueltig_bis)
VALUES (NEW.ean,NEW.md5,NEW.titel,NEW.interpret,NEW.jahr,NOW(),21001231);
END IF;
END
//
DELIMITER ;

Für jeden Eintrag in die Schattentabelle wird dann der Trigger aufgerufen und trägt in Folge dessen eventuell Daten in die Zieltabelle ein.
Wir definieren im Trigger zuerst eine Variable hash. Als nächstes prüfen wir per SELECT, ob passend zu der neu eingetragenen EAN ein Gegenstück in der Zieltabelle existiert (ean=NEW.ean), das auch zurzeit gültig ist (gueltig_bis>NOW()). Das Ergebnis dieser Anfrage speichern wir in der Variable hash. Für den Inhalt von hash gibt es dann zwei Möglichkeiten: entweder steht darin ein 32-stelliger, alphanumerischer MD5-Wert, d.h. die Suchanfrage hat ergeben, dass es eine alte, aber gültige Version zu der aktuellen EAN gibt, oder der Wert der Variable ist NULL, d.h. es gab keine Vorgängerversion.
Folglich vergleichen wir den Variablenwert mit dem neu einzutragenen MD5-Wert. Ist der Hashwert ungleich (hash<>NEW.md5) oder von vornherein NULL, bedeutet das, dass wir den neuen Datensatz in die Zieltabelle eintragen müssen, ansonsten ist die alte Version unverändert. Dazu terminieren wir zuerst die Gültigkeit der alten Version per UPDATE-Befehl und tragen die neuen Daten ein – inklusive Historisierungsfelder.
Nach Ausführung des Triggers wird der Datensatz in der Schattentabelle nicht permanent gespeichert, sondern verworfen – ein angenehmer Effekt der BLACKHOLE Storage Engine.

Fazit
Die vorgestellte Lösung besitzt einige charmante Effekte: Zum einen müssen wir uns beim Eintragen der Daten nicht um die Befüllung der Historisierungsfelder und die Terminierung alter Versionen interessieren, dies übernimmt der Trigger. Zum zweiten müssen wir uns dank der Wahl der richtigen Storage Engine nicht um die Entsorgung der Inhalte in der Schattentabelle kümmern und zum dritten müssen wir für diesen Weg keinerlei Änderungen an der Zieltabelle selbst vornehmen.
Die Nachteile, die sich in Bezug auf die Performance ergeben, sind schwer zu bemessen. Sicherlich existieren Reibungsverluste durch die Ausführung des Triggers bei den Einträgen. Dem gegenüber stehen jedoch Gewinne bei der Erstellung klassischer ETL-Jobs, in denen die richtige Befüllung der SCD implementiert ist.
Dieses Konzept sollte sich prinzipiell auch auf andere DBMS übertragen lassen. Nicht überall existiert jedoch ein Konstrukt wie die BLACKHOLE Storage Engine, so dass man die Daten in der Schattentabelle eventuell händisch wieder löschen muss.

MySQL: Referentielle Integrität in MyISAM

Ein Blick in die Roadmap von MySQL verrät: Ab MySQL 6 soll die referentielle Integrität in allen Storage Engines gewährleistet werden können. Wer nicht solange wartet kann oder will, hat ab MySQL 5.0 die Chance, diese Funktionalität über Trigger nachzubilden.

Referentielle Integrität garantiert, dass in einer Gruppe von Tabellen, die semantisch miteinander in Beziehung stehen, keine verwaisten Einträge entstehen. Am konkreten Beispiel bedeutet das:

Wir haben eine Tabelle User, in der wir Benutzerdaten einer Blog-Community speichern. Darin enthalten sind Benutzername, Email-Adresse, Datum des Beitritts zur Community usw. Eine weitere Tabelle nimmt die Blogeinträge der User auf, also deren Überschrift, den Text und den Zeitpunkt des Eintrags. Gemäß der Normalisierung existiert in der Tabelle der Blogeinträge nur ein Verweis zur ID des Users (im Attribut autor), Abfragen wie die folgende müssen Userdaten somit via Join aus beiden Tabellen holen.

SELECT u.name, b.titel, b.text
FROM user u, blogbeitrag b
WHERE b.autor=u.uid AND b.bid = "123"

Beendet nun ein Benutzer seine Mitgliedschaft in der Community und wird der Datensatz aus der Tabelle Users gelöscht, erscheint für seine Blogeinträge kein Autorenname mehr. Blogeinträge werden zu so genannten Waisen. In der Praxis lassen sich solch unangenehmen Situation vermeiden, indem man nullwertige Referenzen filtert.

Abhilfe in diesem oben genannten Fall schafft ein Fremdschlüssel, der die referentielle Integrität gewährleistet. Das Attribut u.uid aus obigen Beispiel, also der Primärschlüssel der Usertabelle – hier die so genannte Elterntabelle-, wird in der Blogeintragstabelle – in diesem Fall als Kindtabelle bezeichnet – im Attribut b.autor referenziert. In MySQL ist das bis zur Version 5.1 nur in der InnoDB Storage Engine möglich.

Trigger

Um referentielle Integrität für eine MyISAM Tabelle in MySQL nachzubilden, benötigt man vier Trigger, jeweils zwei für die Eltern- und Kindtabelle. Die Trigger überprüfen jede Datenbankoperation, für die sie definiert sind – also INSERT-, UPDATE- und DELETE-Anweisungen -, vor ihrer Ausführung. Als Prüfung ist eine wechselseitige Abfrage nötig, das heißt die Trigger der Kindtabelle führen ein SELECT auf die Elterntabelle aus und umgekehrt. Gesucht wird die Anzahl der semantisch abhängigen Datensätze. Kommt der Trigger zu dem Ergebnis, dass Integritätsbedingungen durch die anstehende Operation verletzt werden, wird ein Fehler erzeugt. Da MySQL derzeit noch über kein entsprechendes Gegenstück zu dem SIGNAL aus Oracle oder ähnlichen Mechanismen anderer Datenbanksysteme verfügt, mit dem der Benutzer einen Systemfehler hervorrufen kann, muss man hierbei zu einem kleinen Trick greifen:

Durch eine fehlerhafte SQL-Anweisung verursacht man einen MySQL-Fehler, der nicht nur die Ausführung des Triggers beendet, sondern auch die ursprüngliche Datenbankoperation verhindert. Schöpfen kann man dabei aus dem reichhaltigen Fundus der MySQL-Server-Fehler, die im Anhang B des Handbuchs aufgelistet sind. Welchen Fehler man wählt, ist egal; nur muss man im Nachhinein noch wissen, dass man eben diesen Fehler, der einem beim Debugging begegnet, zweckentfremdet wurde.

Trigger nutzen wie Stored Procedures das Semikolon als Zeilenendzeichen, das standardmäßig auch alle anderen Datenbankoperationen beendet. Damit es zu keiner Fehlermeldung bei der Triggererstellung kommt, ersetzen wir den Standard durch eine doppelte Raute (##):

DELIMITER ##

Danach erzeugen wird zuerst die Trigger für die Kindtabelle:

CREATE TRIGGER bi_blogeintrag BEFORE INSERT ON blogeintrag FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT COUNT(uid) INTO cnt FROM user WHERE uid=NEW.autor;
IF cnt=0 THEN
SELECT * INTO cnt FROM DUAL;
END IF;
END ##

Das bi_* im Triggernamen steht für BEFORE INSERT und dient nur der schnellen Erkennung der Funktionalität. Der Name spielt für die Funktion keine Rolle und kann frei gewählt werden. Die Anzahl der abhängigen Datensätze speichern wir in cnt, um es gleich danach wieder abzufragen. Wurden keine Datensätze gefunden, erzeugen wir mit dem zweiten SELECT den Fehler #1096 – No table used. Der INSERT-Trigger stellt dadurch sicher, dass jeder Eintrag in der Kindtabelle einen korrespondierenden Eintrag in der Elterntabelle hat. Neben dem INSERT-Trigger brauchen wir für die Kindtabelle noch einen Trigger, der Updates überprüft – für den Fall, dass der Autor eines Eintrags verändert werden soll:

CREATE TRIGGER bu_blogeintrag BEFORE UPDATE ON blogeintrag FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT COUNT(uid) INTO cnt FROM user WHERE uid=NEW.autor;
IF cnt=0 THEN
SELECT * INTO cnt FROM DUAL;
END IF;
END ##

Für die Elterntabelle müssen andersartige Trigger definiert werden: jeweils einer für die Operationen UPDATE und einer für DELETE. Der Aufbau ist analog zu den Triggern der Kindtabelle.

CREATE TRIGGER bu_parent BEFORE UPDATE ON parent FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT COUNT(bid) INTO cnt FROM blogeintrag WHERE b=OLD.uid;
IF cnt>0 THEN
SELECT * INTO cnt FROM DUAL;
END IF;
END ##

Last but not least:

CREATE TRIGGER bd_parent BEFORE DELETE ON parent FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT COUNT(bid) INTO cnt FROM blogeintrag WHERE bid=OLD.uid;
IF cnt>0 THEN
SELECT * INTO cnt FROM DUAL;
END IF;
END ##

Fazit

Die Triggerlösung ist eine gangbare Alternative zu Fremdschlüsseln für die Einhaltung referentieller Integrität, solange MySQL 5.2 noch nicht veröffentlicht ist. Nichtsdestotrotz ist dies nur eine Übergangslösung, da das Nachprogrammieren von Kernfuktionalitäten selten an die Performance der eigentlichen Lösung heranreicht. Wie groß der Leistungsunterschied wirklich ist, werden jedoch erst spätere Tests zeigen.