Check Constraints mit Views/Trigger simulieren?

2005/09/13 by admin

Gestern war ich in Berlin im Rahmen der eZ Roadshow unterwegs. eZ.no tourt zur Zeit durch Deutschland, Details gibt es unter http://ez.no/roadshow2005. In ingesamt fünf Städten gibt es eine Business orientierte Nachmittagveranstaltung ein ein Community-Event am Abend.

In den Slides für das Abendevent zeigten wir, wie man Views missbrauchen kann, um Check Constraints zu simulieren.

Um ehrlich zu sein, war ich sehr dankbar darüber wieder einmal alte PHP-Hasen zu treffen. Sandro Zic erschien im eZ Pinguinkostüm. Eigentlich ist das gelogen, denn es war zwar Sandro da, aber er hört nach seiner Hochzeit nicht mehr auf den Namen Zic. Und einer, der auf keiner PHP-Veranstaltung fehlen darf, präsentierte sich mit seinem Klappfahrrad, was in der Bahn kostenlos mitfahren darf: Lukas Smith.

Ich weiß nicht, wie ich zu der Ehre kam, aber ich durfte MySQL 5.0 präsentieren am Abend. Ralf Gebhard, Pre- und Postsales im deutschsprachigen Raum, hatte die Folien vorbereitet. An einer Stelle hatte mit Ralf überrascht: “Views können anstatt Constrain Checks verwendet werden”. Für mich war es neu, hier die Aufarbeitung.

Basiswissen: Views im Allgemeinen

Einer der Teilnehmer am Event konnte eine Frage nicht zurückhalten: “Wozu Views?”. Bei MySQL gibt es nur eine Antwort darauf: weil die Kunden es sich so wünschen!

Views sind nichts anderes als Sichten auf Basistabellen. Die Sicht zeigt das Ergebnis einer SELECT-Anfrage und wird über einen eigenen Namen, quasi einen Alias für die SELECT-Anfrage angesprochen. Jede Sicht verhält sich gegenüber dem Anwender fast wie eine normale Basistabelle: wird die Sicht abgefragt, ermittelt die Datenbank im Hintergrund alle Daten, die zur Sicht gehören und wendet die Anfrage auf diese Menge an.

Vor dem Hintergrund des Aliasgedanken verwundert es nicht, daß die Syntax zu Definition einer View nicht besonders kompliziert ist.


CREATE [or REPLACE]
[ALGORITHM = 
   {MERGE | TEMPTABLE | UNDEFINED}]
VIEW view_name
[(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

Damit ist die Frage jedoch noch nicht geklört, wofür Sichten benutzt werden können. Sichten “verstecken” eine SELECT-Anfrage vor dem Benutzer. Diese Aussage ergibt nach einer kleinen Umformulierung einen neuen Sinn: Sichten kapseln eine SELECT-Anfrage. Wird in einer Programmiersprache eine Kapselung eingesetzt, dann werden Details über einen Dienst vor dem versteckt, der den Dienst benutzt. Derjenige, der den Dienst aufruft wird von den Details der Implementierung befreit und greift auf den Dienst über eine definierte Schnittstelle zu. Auf eine Datenbank übertragen bedeutet dies, daß eine Sicht einen wohldefinierten auf in der Datenbank angelegte Daten implementiert. Wer sich dies in Erinnerung ruft, der versteht alle anderen Punkte auf der Vortragsfolie:

Views können effektive Kopien der Basistabellen sein
Views können ein Synonym für einen zu langen
Tabellennamen darstellen
Views können eingesetzt werden, um Spalten auszublenden
Views können eingesetzt werden, um Spalten über Access
Control Lists (ACL) anzuzeigen

Ich vermisse in dieser Liste noch ein interessantes Anwendungsbeispiel:

Views können benutzt werden, um eine Anwendung mit angepassten “Basistabellen” zu betreiben

Manchmal kommt es vor, daß man eine Anwendung einsetzt, die nicht Open Source ist. Falls dieses Unglück passiert und die Anwendung nur wenig Anpassungsmöglichkeiten bietet, dann ist es z.B. denkbar daß bestimmte Tabellennamen vorrausgesetzt werden. In solch einer Tabelle könnten sich Daten befinden, die nicht alle Nutzer der Anwendung einsehen dürfen. Der Datenbankadministrator kann in diesem Fall das Problem mit einer View lösen:

CREATE VIEW vorgegebener_name AS SELECT * FROM basistabelle WHERE vertraulichkeit in ("gering", "mittel").

Schritt 1: Updatable View

Codd, ein Vordenker für Relationale Datenbanken, schlägt vor, daß jede Sicht, die theoretisch aktualisert werden kann auch praktisch aktualisierbar sein soll. Anwender können in eine Sicht, die aktualiserbar ist neue Daten einfügen und vorhandene Daten aktualisieren. MySQL versucht die Forderung von Codd weitgehend zu erfüllen. Schau wir mal was geht.

Das erste Beispiel ist einfach. Eine Tabelle “table1” wird mit fünf Datensätzen befüllt und eine Sicht “view_simple” wird definiert. Um die Tabelle “table1” zu füllen, verwende ich nicht fünf INSERT-Anweisungen sondern eine Datenbankprozedur. Eine handliche Variante, um auch größere Datenmengen zu erzeugen und eine gute Gelegenheit ein anderes 5.0-Feature zu zeigen, welches ebenfalls auf den Vortragsfolien diskutiert wird.


mysql> SELECT VERSION();
+-----------------------+
| VERSION()             |
+-----------------------+
| 5.0.13-beta-debug-log |
+-----------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE table1(
  pk_column BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  col1 VARCHAR(65524) DEFAULT NULL,
  PRIMARY KEY(pk_column)
);
mysql> delimiter //
mysql> CREATE PROCEDURE proc_fill_table1() DETERMINISTIC
  BEGIN
   DECLARE i INTEGER;
   SET i = 0;
   WHILE i < 5 DO 
     INSERT INTO table1(col1) VALUES (CONCAT('Aber Hallo ', i));
     SET i = i + 1;
   END WHILE;
  END //
mysql> delimiter ;
mysql> CALL proc_fill_table1();
mysql> SELECT * FROM table1;
mysql> CREATE VIEW view_simple AS SELECT * FROM table1 WHERE col1 > 'Aber Hallo 3';
mysql> SELECT * FROM view_simple;

Jetzt kommt der spannende Teil.


mysql> INSERT INTO view_simple (col1) VALUES ('Aber Hallo 6');
mysql> INSERT INTO view_simple (col1) VALUES ('Aber Hallo 1');
mysql>  SELECT * FROM view_simple;
mysql>  SELECT * FROM table1;

Schritt 2: WITH CHECK OPTION

Wer das Beispiel nacharbeitet wird sehen, daß MySQL zwei neue Datensätz in die Tabelle “table1” eingefügt hat. Das demonstriert das Feature der Updatable Views, der aktualisierbaren Sichten. Doch wo ist die Kapselung geblieben? Ein Nutzer, der nur die Sicht “view_simple” bearbeiten darf, die auf einer SELECT-Anfrage mit der Bedingung “col1 > ‘Aber Hallo 4′” basiert, darf neue Datensätze einfügen, die er nicht auslesen kann. Der Anwender kann einen Datenbestand verändern auf den der niht einmal Lesezugriff hat. Um dies zu verhindern, kann man bei der Definition einer Sicht den [WITH [CASCADED | LOCAL] CHECK OPTION] Teil aus der Syntax verwenden.


mysql> CREATE OR REPLACE VIEW view_simple AS SELECT * FROM table1 WHERE col1 > 'Aber Hallo 3' WITH CHECK OPTION;

Die Definition der Sicht wird verfeinert und MySQL wird mittel WITH CHECK OPTION angewiesen zu prüfen ob die Aktualisierung der Sicht im Gegensatz zu der sie definierenden SELECT-Anweisung steht. Bei jeder Aktualisierung prüft der MySQL-Server ob die Bedingung “col1 > ‘Aber Hallo 3′” eingehalten wird. Ist dies nicht der Fall, weist MySQL die Aktualisierung mit einer Fehlermeldung zurück.


mysql> INSERT INTO view_simple (col1) VALUES ('Aber Hallo 1');
ERROR 1369 (HY000): CHECK OPTION failed 'ulf.view_simple'

Die Schlüsselwörter CASCADED und LOCAL in [WITH [CASCADED | LOCAL] CHECK OPTION] beschreiben die Suchtiefe für Bedingungen, die geprüft werden müssen bei einer Aktualisierung. Gibt man LOCAL an, dann werden nur die Bedingungen der Sicht beachtet, die aktualisert wird. Basiert die Sicht selbst auf einer Sicht und möchte man, daß auch die Bedingungen dieser zugrunde liegenden Sichten beachtet werden, dann ist CASCADED anzugeben. Falls keines der Schlüsselwörter angegeben wird und nur WITH CHECK OPTION benutzt wird, wie dies im Beispielt gemacht wird, dann verwendet MySQL die Regeln für CASCADED.

Die Simulation von Check Constraints ist bestimmt kein Haupteinsatzgebiet für Trigger, aber es ist ein interessanter Ansatz zur Simulation dieses Features, das der MySQL-Server in Version 5.0 noch nicht bietet.

Alternative: Trigger?

Eine weitere Variante des Themas sind Trigger. Trigger zählen ebenfalls zu den 5.0 Neuerungen. Auch sie können verwendet werden, um Check Constraints nachzuahmen. Unterm Strich ist der Workaround jedoch alles andere als perfekt und empfehlenswert. Das erste Codebeispiel verändert den eingefügten Wert, um die Bedingungen eines simulierten Check Constraints zu wahren. Das zweite Beispiel arbeitet mit dem Holzhammer und erzeugt eine wenig aussagekräftige Fehlermeldung beim Versuch Daten in die Tabelle “table1” einzufügen. Wie gesagt: nicht elegant, nicht empfehlenswert, aber eine theoretische Option.


CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt


mysql> delimiter //
mysql> CREATE TRIGGER trigger_check_col1 BEFORE INSERT ON table1 FOR EACH ROW BEGIN 
  IF NEW.col1 < = "Aber Hallo 3" THEN 
    NEW.col1 = NULL
  END IF; 
END //
mysql> delimiter ;


mysql> delimiter //
mysql> CREATE TRIGGER trigger_check_col1 BEFORE INSERT ON table1 FOR EACH ROW BEGIN 
  IF NEW.col1 < = "Aber Hallo 3" THEN 
    INSERT INTO check_contraint_table1_col1_violation(undefined_column) VALUES (NULL);
  END IF; 
END //
mysql> delimiter ;

Fazit

Trigger und Views können das Fehlen von Check Constraints nicht verdecken. Es gibt “Hacks”, die möglicherweise für den einen oder anderen eine Lösung darstellen, aber letztlich bleibt MySQL dabei – je nach Version – mal mehr mal weniger Validierungsaufgaben in die Applikation zu verschieben.

Ulf Wendel