Oracle Datenbank 23c - if [not] exists DDL Syntax
Ein Ausflug in die Vergangenheit
Diejenigen, die mich schon länger kennen, wissen, dass ich meine "Oracle"-Karriere als Entwickler und hauptsächlich mit PL/SQL und Oracle Forms begonnen habe. Eine der Hauptaufgaben, die ich zusammen mit einem Kollegen erledigte, war unter anderem die Erstellung von Datenmodellen.
Diese Datenmodelle wurden für alle Arten von Anwendungen und Kunden erstellt. Ein Teil der Aufgabe bestand darin, alle Skripte zu erstellen, um die Oracle-Datenbankschemata einzurichten. Später, als die Anwendung zu neuen Versionen weiterentwickelt wurde, mussten wir das Datenmodell von Zeit zu Zeit ändern. Hinzufügen von Tabellen, Ändern von Spalten, Löschen von Objekten, Löschen von Daten, ...
Sicherlich kann man Edition-based Redefinition verwenden oder mit entsprechenden Tools Datenbanken zwischen Entwicklung und Produktion vergleichen und Skripte erstellen (lassen), die nur die Unterschiede enthalten. Aber die Welt ist komplizierter. Die Anwendung wird eben mehrmals mit unterschiedlichen Versionen bei den Kunden installiert. Aufgrund von Fixes, Patches, Releases sieht das Datenmodell nicht bei allen Kunden gleich aus.
Der Arbeitsaufwand für die Erstellung hunderter verschiedener Skripte für jede Art von Unterschied ist unvorstellbar. Die Lösung damals war: Es gibt ein gemeinsames Skript, das von einem Release zum nächsten verwendet werden kann und alle Änderungen aufnimmt, also alles, was sich jemals in einem Patch oder Hotfix an Änderungen befunden hat (ca. die Arbeit mehrerer Entwickler von zwei Jahren).
Das Skript hat also beim Deployment versucht, Tabellen oder Indizes zu erstellen, die vielleicht schon existierten, oder es hat versucht, Tabellen zu löschen, die nicht mehr existierten, da sie zuvor durch einen Patch oder Hotfix bereits gelöscht wurden.
Durch diese Vorgehensweise war die Logdatei voll von ORA-Fehlern mit "... does already exist" usw. Für das Installationsteam war es furchtbar, die Protokolle zu kontrollieren. Wurde etwas wie beabsichtigt hinzugefügt/geändert/gelöscht oder ist der Fehler überspringbar?
Also ist zum Beispiel ein "Objekt existiert nicht"-Fehler in Ordnung (da versucht wurde, ein bereits gelöschtes Objekt zu löschen) oder handelt es sich um einen echten Fehler (weil versucht wurde, eine Spalte zu einer Tabelle hinzuzufügen, die nicht existiert)? Es wurden mit zunehmender Automatisierung immer mehr Skripte erstellt, um ORA-Fehler durch Kommentare "NOORAERR-" zu ersetzen, damit die "das Ding ist in Ordnung"-Fehler übersprungen werden können.
Gott sei Dank hat Oracle dies nun für die neue Oracle 23c Datenbank geändert. Oracle hat eine "if [not] exists"-Klausel für DDL zur Syntax hinzugefügt. Dies kann helfen, viele Stunden Arbeit für all die zuvor erwähnten Prüfungen zu vermeiden – aber leider nicht alle, wie wir im Detail sehen werden.
Test 1
Benutzer erstellen
Mit der bereits verfügbaren 23c Developer Edition VM können wir einige Tests durchführen, zum Beispiel das Erstellen eines Benutzers. Zuerst erstellen wir den Benutzer und versuchen danach, ihn erneut mit der alten und der neuen Syntax zu erstellen. Außerdem probieren wir, den (bereits existierenden) Benutzer mit einem anderen Passwort als zuvor anzulegen und uns mit der Datenbank zu verbinden.
create user my_user identified by <*>;
create user my_user identified by <*>;
create user my_user if not exists identified by <*>;
create user my_user if not exists identified by <*>;
Das Ergebnis auf der Datenbank 23c:
Wie wir sehen können, wird der Benutzer mit der ersten, der klassischen Anweisung erstellt. Mit der neuen Syntax erhalten wir keine Fehlermeldung, wenn wir versuchen, den Benutzer (ggfs. mit einem anderen Passwort) erneut zu erstellen. Dass die gesamte Anweisung verworfen wird, sehen wir an der identified by-Klausel, gefolgt von einem Versuch, sich mit dem Benutzer zu verbinden (und dem sich somit unterscheidenden Passwort).
Das "neue" Passwort wird nicht gesetzt, da der connect nur mit dem zuerst verwendeten Passwort "oracle" funktioniert (der Fehler ORA-01045 ist in Ordnung - wir haben dem Benutzer noch keine Rechte gegranted). Also wird bei der "Ausführung" überhaupt nichts gemacht und lediglich die Meldung "user created" ausgegeben. Das ist, was ich erwartet habe, aber man sollte sich bewusst sein, wenn das neue Passwort für diesen Benutzer verwendet werden soll, muss es zusätzlich (als alter user) in allfälligen Skripten geändert werden.
Test 2
Benutzer droppen
In unserem zweiten Test werden wir einen Benutzer löschen. Zuerst löschen wir ihn mit der alten Syntax und dann versuchen wir, ihn erneut mit der alten und der neuen Syntax zu löschen.
drop user my_user1;
drop user if exists my_user1;
Wird er mit der neuen Syntax gelöscht, sollte kein Fehler auftreten, auch wenn der Benutzer nicht mehr vorhanden ist.
Test 3
Erstellen einer Tabelle
Für den dritten Test verwenden wir den im ersten Testszenario erstellten Benutzer und werden prüfen, was passiert, wenn wir eine Tabelle in diesem Schema erstellen und in einem folgenden Test auch ändern (die dazu benötigten Rechte sind dem Benutzer in der Zwischenzeit gegranted worden). Wir führen also den folgenden Code aus
create table my_sales (txt varchar2(20));
create table if not exists my_sales (txt varchar2(20));
und schauen uns das Ergebnis an:
Nun wollen wir die Tabelle nochmal erstellen, allerdings mit anderen Spalten.
create table my_sales (txt varchar2(40), txt1 varchar2(10));
create table if not exists my_sales (txt varchar2(40), txt1 varchar2(10));
Wir erwarten nun eine Fehlermeldung ORA-00955 bei der Verwendung der alten Syntax und ein Table created bei der Verwendung der if not exists Syntax.
Ein anschliessendes describe der Tabelle zeigt uns den Zustand nach dem ersten Create.
Test 4
Alter Table
Da uns nun in der Tabelle eine Spalte fehlt (und die erste Spalte eine andere Länge beim Datentyp hat), müssen wir die Differenzen beseitigen.
Kleines Rätsel vorab: Tippt doch mal, welches der folgenden Statements überhaupt ausführbar ist und welches Fehler wirft, wenn es (ggfs. mehrfach) ausgeführt wird? (Hinweis, wir starten immer im Zustand nach Test 3) Was ist eure Erwartungshaltung? Sollten alle Statements funktionieren?
- Statement 1: alter table my_sales add txt1 varchar2(10);
- Statement 2: alter table my_sales add if not exists txt1 varchar2(10);
- Statement 3: alter table if exists my_sales add txt1 varchar2(10);
- Statement 4: alter table if exists my_sales add if not exists txt1 varchar2(10);
- Statement 5: alter table if exists my_sales_history add txt1 varchar2(10);
Starten wir mit Statement 1 – einer klassischen Syntax. Zu erwarten ist, dass es beim ersten Mal funktioniert und beim zweiten Mal einen Fehler ORA-01430 wirft, da die Spalte txt1 dann schon vorhanden ist.
Gut, das war nun wirklich keine Überraschung, aber kommen wir zum Statement 2 mit der if not exists Klausel für die Tabellenspalte (alter table my_sales add if not exists txt1 varchar2(10);)
Dieses Statement wirft leider einen Fehler und die Dokumentation zeigt auch, warum. Es gibt keine if [not] exists Klausel für diesen Teil des Statements. Somit wird auch Statement 4 nicht funktionieren. Aber wie sieht es mit Statement 3 (alter table if exists my_sales add txt1 varchar2(10);) aus?
Funktioniert also – wirft aber bei einem zweiten Durchlauf, wenn die Tabelle existiert, auch einen Fehler ORA-01430, weil die Spalte schon vorhanden ist. Bleibt also noch Statement 5 (alter table if exists my_sales add txt1 varchar2(10);) – ein alter table auf einer nicht existierenden Tabelle. Hier passiert nichts, außer der Meldung, dass die Tabelle gealtered wurde.
Kommen wir also zu unserem
Test 5
Modify/Drop/Rename Columns
Wie beim add einer Spalte ist auch hier die neue Syntax nur beim "table", aber nicht beim "column" Teil erlaubt. D.h.
alter table if exists my_sales modify column txt varchar2(40);
funktioniert, während
alter table my_sales modify if exists txt varchar2(40);
nicht zulässig ist.
Wir sehen das gleiche Verhalten auch beim Droppen einer Spalte.
Was denkt Ihr über dieses Verhalten? Einerseits kann es Spalten mit dem Namen "if" geben, was Oracle vielleicht dazu veranlasst, die Klausel in einer alter table-Anweisung für Spalten nicht zu verwenden.
Andererseits könnte Oracle, wenn die gesamte Syntax überprüft, (beim Parsen des Statements) herausfinden, ob die Spalte IF heißt oder ob dem IF eine (NOT) EXISTS-Anweisung folgt.
Wenn Sie einer Tabelle (Spalte) Beschränkungen (Fremd-/Einzelschlüssel) hinzufügen/ändern/entfernen wollen, gibt es übrigens auch keine if (not) exists Klausel.
Sehen wir uns aber noch weitere Objekte an.
Test 6
Views erstellen
Bei views erwartet man neben der bekannten Syntax auch die if [not] exists clause.
create view if not exists my_view as select upper(txt) uptxt from my_sales;
create view my_view as select upper(txt) uptxt from my_sales;
Während man das Statement mit if not exists mehrfach ausführen kann, wirft das Statement in der alten Syntax einen ORA-00955.
Vollständigkeitshalber sei noch erwähnt, ein "create or replace" Statement (von Views, Prozeduren, Packages, Funktionen,...) macht natürlich in Kombination mit if [not] exists keinen Sinn. Die Datenbank fängt das entsprechend auch ab und macht nicht einfach ein "create or replace".
create or replace view if not exists my_view as select upper(txt) uptxt from my_sales;
create or replace view my_view as select upper(txt) uptxt from my_sales;
Der ORA-11541 weist einen auf diesen kleinen Fauxpax entsprechend hin.
Zusammenfassung
Für sehr viele Statements funktioniert die neue if (not) exists-Syntax sehr gut. Leider funktioniert sie nicht für das Hinzufügen, Umbenennen oder Ändern von Tabellenspalten, Constraints, usw.. Es gibt also meiner Meinung nach noch einige offene Punkte, deren Umsetzung ich mir an dieser Stelle wünschen würde.
Da eine Anweisung nur ausgeführt wird, wenn das if (not) exists erfolgreich validiert wird, kann es auch vorkommen, dass ein Benutzer, eine Tabelle oder allgemeiner, ein Objekt, nicht den Status hat, den er/sie/es haben sollte. Einfach, weil schon ein Eintrag im Data Dictionary für das entsprechende Objekt vorliegt. Zur Verwirrung kann ein "Table altered" z.B. auch dann führen, wenn die Tabelle überhaupt nicht existiert, aber mit if exists geprüft wurde.
Es liegt trotz der Vereinfachungen also immer noch in der Verantwortung der Benutzer/Entwickler, fehlerfreie Skripte für Migrationen zu schreiben. Wir können mit der neuen Syntax zwar nicht jeden "unnötigen" Fehler von vornherein beseitigen, aber zumindest gibt es eine größere Anzahl von Fehlermeldungen, die zukünftig nicht mehr in den Installationsprotokollen auftauchen werden.
Bei Twitter gab es übrigens eine Diskussion zwischen Usern und dem Produktmanagement von Oracle. Die einen sagten, dass es schön wäre, eine unterschiedliche Meldung für verschiedene Zustände zu erhalten. Als Beispiel wurde genannt, dass eine create table if not exists-Anweisung nicht "table created" auslösen sollte, sondern eine "statement skipped, table exists"-Meldung (ohne ORA-Fehler), um den Unterschied zu sehen. Die anderen (das Produktmanagement) waren der Meinung, die jetzige Umsetzung reicht, da es "nur" darum geht, "unnötige" ORA- Fehlermeldungen in den Logs beim Deployment zu vermeiden.
Ich würde mich darüber freuen, wenn ihr in den Kommentaren euren Standpunkt zu diesem Thema hinterlassen würdet.
Kommentare
|
Ohne Frage sind die neuen Möglichkeiten von IF (NOT) EXISTS praktisch um beschriebene Datenmodellskripte zu vereinfachen. Allerdings würde ich mir auch Rückmeldungen der Datenbank wünschen, die auch darauf eingehen, ob das IF (NOT) EXISTS jetzt zutraf oder das Statement verworfen wurde. Rückmeldungen a lá "Table created.", obwohl das CREATE-Statement verworfen wurde sind schlicht falsch. Diese Inkonsistenz muss beseitigt werden.