5

Benutzerdefinierte Typen und Funktionen

5.1 5.1.1

Theorie Benutzerdefinierte Typen

F¨ ur jedes Quentchen Daten in der Datenbank muß ein Datentyp festgelegt sein, der angibt, wie die Information repr¨asentiert wird und welche Operationen darauf angewendet werden k¨onnen. Beim Aufbau einer Datenbank kann man sich vorstellen, daß zum Beispiel f¨ ur die Repr¨asentation von Altersangaben Integer eingesetzt werden, f¨ ur Geldbetr¨age Decimal(15,2) und f¨ ur Winkel Double. Meistens hat man dabei bestimmte Ziele im Hinterkopf, wie diese Daten eingesetzt werden k¨onnen, d.h. welche Operationen darauf angewendet werden d¨ urfen. So macht es zum Beispiel Sinn, zwei Geldbetr¨age zusammenzuaddieren oder voneinander abzuziehen. Es ist aber wahrscheinlich nicht besonders sinnvoll zwei Geldbetr¨age miteinander zu multiplizieren oder gar ein Alter und einen Geldbetrag zu vergleichen. In DB2 k¨onnen solche Einschr¨ankungen definiert werden, die dann vom System u ¨ berwacht werden. Mit den Schl¨ usselw¨ortern distinct type k¨onnen vom Benutzer neue Datentypen definiert werden. Dieser neue Datentyp benutzt zwar die Darstellung eines vom System zur Verf¨ ugung gestellten Datentyps (Basistyp), wird aber trotzdem als davon verschiedener Datentyp angesehen (daher das Schl¨ usselwort distinct). Hier ein paar Beispiele: CREATE CREATE CREATE CREATE

DISTINCT DISTINCT DISTINCT DISTINCT

TYPE TYPE TYPE TYPE

Geschlecht AS Char(1) WITH COMPARISONS; Geld AS Decimal(15,2) WITH COMPARISONS; Winkel AS Double WITH COMPARISONS; Video AS Blob(100M);

Der Ausdruck WITH COMPARISONS erlaubt den Vergleich zweier Instanzen des neuen Datentyps mit den Vergleichsoperatoren =, = und . Dieser Ausdruck muß angegeben werden, wenn es sich bei dem Basistyp nicht um ein large object handelt (wie zum Beispiel Blob (Binary Large OBject)). Mit der Drop-Anweisung kann ein benutzerdefinierter Typ wieder verworfen werden. Dies geht allerdings nur, wenn er an keiner Stelle mehr im Datenbanksystem verwendet wird. Beispiele f¨ ur das L¨oschen: DROP DISTINCT TYPE Geld; DROP DISTINCT TYPE Video; Es werden automatisch zwei Castingfunktionen bereitgestellt, um Werte zwischen dem benutzerdefinierten Typ und dem Basistyp konvertieren zu k¨onnen. Der Name der Funktion die einen Wert des Basistyps in einen Wert des neuen Typs umwandelt hat den gleichen Namen wie der neue Typ. Um den Wert 1234.50 in einen Geldbetrag zu wandeln gibt es zwei M¨oglichkeiten: Geld(1234.50) CAST(1234.50 AS Geld) 42

Die Funktionen des Basistyps (außer Casting und Vergleich) sind nicht direkt auf den neuen Typ anwendbar. Da ein Datentyp ohne weitere Funktionen wahrscheinlich nicht sehr sinnvoll ist, kommen wir zum n¨achsten Punkt dieses Teils, den benutzerdefinierten Funktionen. 5.1.2

Benutzerdefinierte Funktionen

Bei benutzerdefinierten Funktionen wird grunds¨atzlich zwischen zwei verschiedenen Varianten unterschieden, den abgeleiteten Funktionen (sourced functions) und den externen Funktionen (external functions). Auf diese zwei Varianten soll im folgenden im Detail eingegangen werden. Abgeleitete Funktionen Eine abgeleitete Funktion ist eine neue Funktion, die auf einer Funktion aufbaut die bereits existiert. Diese existierende Funktion wird auch Ursprungsfunktion (source function) genannt. Beim Aufruf einer abgeleiteten Funktion werden die Argumente in den Basistyp umgewandelt, die Ursprungsfunktion des Basistyps wird aufgerufen und das Ergebnis wird wieder in den benutzerdefinierten Typ umgewandelt. Abbildung 19 zeigt ein Beispiel f¨ ur den Datentyp Geld und Addition. Aufruf der Ursprungsfunktion "+"(Decimal(15,2),Decimal(15,2))

Decimal(15,2)

Casten der Argumente

Casten des Ergebnisses

"+"(Geld, Geld)

Geld

Abbildung 19: Aufruf einer abgeleiteten Funktion Abbildung 20 zeigt die Syntax der create function Anweisung um eine abgeleitete Funktion zu deklarieren. Die einzelnen Teile dieser Anweisung haben folgende Bedeutung: 1. function-name: der Name der Funktion. Es k¨onnen arithmetische Operatoren wie “+” oder “*” k¨onnen als Namen angegeben werden, m¨ ussen aber in doppelten Anf¨ uhrungszeichen eingefaßt werden. 2. (datatype, ...): hier m¨ ussen die Datentypen der Funktionsargumente angegeben werden. Die Klammern m¨ ussen auch angegeben, falls keine Argumente vorhanden sind. 3. RETURNS Klausel: gibt den Datentyp des R¨ uckgabewerts an. 4. SPECIFIC Klausel: hiermit k¨onnen verschiedene Instanzen von namensgleichen Funktionen angelegt werden (wird von uns nicht ben¨otigt). 43

Abbildung 20: CREATE FUNCTION Anweisung (f¨ ur abgeleitete Funktionen) 5. SOURCE Klausel: hier wird die Ursprungsfunktion angegeben. Dabei kann die Ursprungsfunktion auf drei verschiedene Arten angegeben werden: (a) Durch den Funktionsnamen ohne Argumente (dies geht nur bei benutzerdefinierten Ursprungsfunktionen). (b) Durch den Namen der Instanz (SPECIFIC), wird von uns nicht benutzt. (c) Durch die Signatur (Name und Datentypen der Argumente) der Ursprungsfunktion). Hier ein paar Beispiele, welche Funktionen f¨ ur den benutzerdefinierten Typ Geld angelegt werden k¨onnten: CREATE FUNCTION "+"(Geld, Geld) RETURNS Geld SOURCE "+"(Decimal(), Decimal()); CREATE FUNCTION "-"(Geld, Geld) RETURNS Geld SOURCE "-"(Decimal(), Decimal()); CREATE FUNCTION avg(Geld) RETURNS Geld SOURCE avg(Decimal()); Externe Funktionen Eine externe Funktion ist eine Funktion, die in einer Hostsprache (wie z.B. C) und nicht in SQL implementiert wird. Auf diese Weise k¨onnen neue Funktionen f¨ ur Basistypen definiert werden oder eben beliebige Funktionen f¨ ur benutzerdefinierte 44

Typen. Durch die Installation von externen Funktionen in einer Datenbank wird diese allen Applikationen (selbst interaktiven SQL-Anfragen) zur Verf¨ ugung gestellt, d.h. diese Funktion muß nicht jedesmal neu implementiert werden.

Abbildung 21: CREATE FUNCTION Anweisung (f¨ ur externe Funktionen) Abbildung 21 zeigt das Syntaxdiagramm f¨ ur einen create function Aufruf f¨ ur externe Funktionen. Die einzelnen Teile der Abbildung haben folgende Bedeutung: 1. function-name: der Name der Funktion. ¨ 2. (datatype, ...): Datentypen der Funktionsargumente. Beim Ubergeben der Argumente an das C-Programm werden die SQL-Datentypen in die entsprechenden ¨ C-Datentypen umgewandelt. Eine Ubersicht der Datentypumwandlung ist in den Abbildungen 22 und 23 zu finden. Dabei ist zu beachten, daß keine Argumente vom Typ Decimal an ein C-Programm u ussen vorher ¨bergeben werden k¨onnen. Diese m¨ in einen Double oder Char Wert umgewandelt werden. 3. RETURNS Klausel: gibt den Datentyp des R¨ uckgabewerts an. Dabei werden die Typen nach Abbildungen 22 und 23 umgewandelt. Falls ein Decimal-Wert zur¨ uckge45

Abbildung 22: Umwandlung von SQL-Datentypen in C-Typen (Teil 1) geben werden soll, muß dieser aus einem anderen Typ umgewandelt werden, also z.B. RETURNS Decimal(3,1) CAST FROM Double. 4. SPECIFIC Klausel: Name der Instanz (wird von uns nicht benutzt). 5. EXTERNAL NAME Klausel: definiert die Funktion als extern. Außerdem wird hier angegeben, wo das Datenbanksystem die u uhr¨bersetzte C-Funktion findet. Die ausf¨ lichste Form dieser Klausel gibt den vollen Pfad der ausf¨ uhrbaren Bin¨ardatei an, gefolgt von einem Ausrufezeichen (!), gefolgt von dem Namen der Funktion in dieser Datei. Der Ausdruck EXTERNAL NAME /home/helmer/dbfns/bin/mortgage!payment gibt an, daß die Funktion payment in der Datei /home/helmer/dbfns/bin/mortgage verwendet werden soll. Es k¨onnen mehrere Funktionen in der gleichen Datei enthalten sein und diese Funktionen m¨ ussen auch nicht den gleichen Namen wie die zugeh¨orige SQL-Funktion besitzen (d.h. der Name in der CREATE FUNCTION Klausel kann verschieden von dem Namen in der EXTERNAL NAME Klausel sein). 6. VARIANT oder NOT VARIANT: eine der beiden M¨oglichkeiten muß spezifiziert werden. NOT VARIANT bedeutet, daß die Funktion bei einem Aufruf mit den gleichen Argumenten immer das gleiche Ergebnis liefert. VARIANT bedeutet, daß die Funktion mit den gleichen Argumenten verschiedene Ergebnisse liefern kann (z.B. Ergebnisse die auf Zufallszahlen beruhen). 7. EXTERNAL ACTION oder NO EXTERNAL ACTION: auch hier muß wieder eine der beiden M¨oglichkeiten spezifiziert werden. NO EXTERNAL ACTION bedeutet, daß beim Aufruf der Funktion keinerlei Operationen außerhalb des Datenbanksystems stattfinden. Mit EXTERNAL ACTION gibt man an, daß die Funktionen Operationen außerhalb der Datenbank anst¨oßt (z.B. eine Mail schicken, etwas in eine Datei schreiben). 8. FENCED oder NOT FENCED: Bei einer Angabe von FENCED l¨auft die Funktion in einem eigenen Adressbereich (verschieden von dem Adressbereich des Datenbankservers). NOT FENCED-Funktionen laufen im gleichen Adressbereich wie die Datenbank (was 46

Abbildung 23: Umwandlung von SQL-Datentypen in C-Typen (Teil 2) bei Abst¨ urzen zu u uhren kann). Nur ein Benutzer mit Datenbankad¨ blen Effekten f¨ ministratorrechten kann eine Funktion NOT FENCED deklarieren. 9. NULL CALL oder NOT NULL CALL: spezifiziert was bei Aufrufen einer Funktion mit NULL-Argumenten passiert. NULL CALL sagt aus, daß NULL-Werte direkt an die Funktion u us sich selbst um diesen Sonderfall ¨bergeben werden, d.h. die Funktion m¨ k¨ ummern. Bei einer Angabe von NOT NULL CALL wird die Funktion erst gar nicht aufgerufen, es wird sofort das Ergebnis NULL zur¨ uckgeliefert. 10. LANGUAGE C: gibt an, daß die externe Funktion in C geschrieben wurde (oder zumindestens in einer Programmiersprache mit den gleichen Link-Konventionen). 47

11. PARAMETER STYLE DB2SQL: legt die Konventionen f¨ ur den Aufruf einer externen Funktion fest. Wir benutzen den Modus DB2SQL. 12. NO SQL: sagt aus, daß in der externen Funktion keine SQL-Aufrufe vorhanden sein d¨ urfen. (Zur Zeit d¨ urfen externe Funktionen keine Datenbankzugriffe ausf¨ uhren. 13. SCRATCHPAD oder NO SCRATCHPAD: bei einer Angabe von SCRATCHPAD wird Speicher allokiert, der zwischen den Aufrufen der Funktion erhalten bleibt. 14. FINAL CALL oder NO FINAL CALL: Bei einer Angabe von FINAL CALL wird die Funktion nach Beendigung des SQL-Ausdrucks in dem sie aufgerufen wurde noch ein letztes Mal mit einem speziellen Parameter noch einmal aufgerufen. (Auf diese Weise kann z.B. der Speicher f¨ ur das SCRATCHPAD wieder freigegeben werden.) Nachdem nun das Einbinden einer externen Funktion in SQL besprochen wurde, soll nun gezeigt werden, wie die Schnittstelle der C-Funktion aussehen muß. Alle Parameter sind Zeiger auf Speicher der vom Datenbanksystem verwaltet wird. Funktionsname ( SQL Eingabeparameter Rueckgabewert Eingabe Nullindikatoren Rueckgabe Nullindikator SQLSTATE SQL Funktionsname Instanzname Fehlermeldung Scratchpad Final Call Indikator );

/* /* /* /* /* /* /* /* /* /*

Eingabe Ausgabe Eingabe Ausgabe Ausgabe Eingabe Eingabe Ausgabe Eingabe Eingabe

*/ */ */ */ */ */ */ */ */ */

1. Die ersten n Parameter sind Zeiger auf die n Parameter der SQL-Funktion. Die Datentypen wurden dabei nach Tabelle 22 und 23 umgewandelt. F¨ ur jeden Datentyp gibt es einen symbolischen Namen (aus der Headerdatei sqludf.h), der vor dem CDatentyp bevorzugt verwendet werden sollte. 2. Der n¨achste Parameter ist ein Zeiger auf einen Speicherbereich in den das Ergebnis geschrieben werden sollte. Hier wird auch der C-Datentyp verwendet, die Umwandlung in den SQL-Datentyp wird vom Datenbanksystem u ¨ bernommen. 3. Die n¨achsten n Parameter sind die Nullindikatoren f¨ ur die Eingabeparameter. Jeder dieser Parameter ist ein Zeiger auf einen Wert vom Typ short, der gleich 0 ist, wenn der zugeh¨orige Parameter nicht gleich NULL ist und gleich -1 ist, wenn der zugeh¨orige Parameter gleich NULL ist. Diese Parameter existieren immer, selbst wenn die Funktion mit NOT NULL CALL deklariert wurde. 4. Der n¨achste Parameter ist der Nullindikator f¨ ur den R¨ uckgabewert. Die Konventionen entsprechen denen f¨ ur die Eingabenullindikatoren. 48

5. In SQLSTATE sollte der Zustand nach Ausf¨ uhrung der Funktion angegeben werden. Bei SQLSTATE handelt es sich um einen nullterminierten char[6] String, der mit “00000” initialisiert wird. Die C-Funktion muß SQLSTATE nur ¨andern, falls beim Ablauf der Funktion Unregelm¨aßigkeiten aufgetreten sind. Die Codes “01H00” bis “01H99” sind f¨ ur benutzerdefinierte Warnungen reserviert, die Codes “38600” bis “38999” f¨ ur benutzerdefinierte Fehler. 6. Der n¨achste Parameter gibt in einem String den vollen Namen der SQL-Funktion an. Auf diese Weise k¨onnen verschiedene SQL-Funktionen durch eine C-Funktion realisiert werden (falls dies wirklich erw¨ unscht ist). 7. Hier steht der Instanzname der Funktion, wie er in der SPECIFIC Klausel angegeben wurde. 8. Der n¨achste Parameter ist ein Zeiger auf einen Speicherbereich von 70 Characters in dem eine Fehlermeldung abgelegt werden kann. 9. Der n¨achste Parameter existiert nur, falls die Funktion mit der Option SCRATCHPAD deklariert wurde. Dieser Parameter ist ein Zeiger vom Typ struct sqludf scratchpad* der auf einen Speicherbereich verweist, dessen Inhalt zwischen Funktionsaufrufen erhalten bleibt. Detailiert sieht der Typ folgendermaßen aus: struct sqludf_scratchpad { unsigned long length; char data[100]; };

/* Laenge des Datenbereichs */ /* initialisiert mit 0 */

Die Daten in einem “Scratchpad” werden nur w¨ahrend der Abarbeitung einer SQLAnweisung erhalten, nicht zwischen Aufrufen verschiedener SQL-Anweisungen. Falls eine Funktion einer SQL-Anweisung mehrmals (an verschiedenen Stellen) aufgerufen wird, bekommt jede ihr eigenes “Scratchpad”. 10. Der n¨achste Parameter (ein Zeiger auf einen Wert vom Typ long) existiert nur, falls die Funktion mit der Option FINAL CALL deklariert wurde. Beim ersten Aufruf ist der Parameter auf -1 gesetzt, beim “final call”, dem letzten Aufruf nach Beendigung des SQL-Ausdrucks, auf 1. Bei allen anderen Aufrufen ist der Parameter auf 0 gesetzt. Bei der Implementierung einer benutzerdefinierten Funktion sollten folgenden Details beachtet werden: • Die Routine sollte ablaufinvariant (reentrant) sein. • Falls die Routine dynamisch Speicher allokiert, sollte dieser vor Beendigung wieder freigegeben werden (einzige Ausnahme ist der SCRATCHPAD Speicher). • Der R¨ ucksprung aus der Routine sollte mit dem return Befehl durchgef¨ uhrt werden. Auf keinen Fall sollte mit exit ausgestiegen werden. 49

• Die Routine sollte nicht versuchen etwas u ¨ber stdin zu lesen (scanf) oder u ¨ber stdout auszugeben (printf). Eine benutzerdefinierte Funktion kann mit dem DROP-Kommando wieder gel¨oscht werden. Dieser Befehl entfernt nur den SQL-Teil der Funktion, die Bin¨ardatei steht weiterhin in dem Verzeichnis in der sie vorher auch stand. Eine Funktion kann nicht gel¨oscht werden, wenn sie zum L¨oschzeitpunkt noch in Views, Constraints, Triggern oder als Ursprungsfunktion verwendet wird. Abbildung 24 zeigt die Syntax des DROP-Befehls.

Abbildung 24: DROP FUNCTION Anweisung

5.1.3

Large Objects

Beim Entwurf eines eigenen Datentyps kann es vorkommen, daß keiner der Basistypen geeignet ist, da der eigene Datentyp etwas komplexer aufgebaut ist. Hier kommen die sogenannten Large Objects (LOB) ins Spiel, in denen beliebige Daten (bis zu einer maximalen Speicherobergrenze) gespeichert werden k¨onnen. Dabei werden drei verschiedene Varianten unterschieden: 1. BLOB (Binary Large OBject): Ein BLOB-Datentyp kann bis zu 2 Gigabyte (2 31 − 1 Bytes) an bin¨aren Daten speichern. 2. CLOB (Character Large OBject): Ein CLOB-Datentyp kann bis zu 2 Gigabyte an Ein-Byte-Characterdaten speichern (z.B. ASCII). 3. DBCLOB (Double-Byte Character Large OBject): Ein DBCLOB-Datentyp kann bis zu 2 Gigabyte an Zwei-Byte-Characterdaten speichern (z.B. Unicode). Um LOB-Daten in einer Datenbank zu speichern, gibt man als Typ eines Attributs einfach einen der LOB-Datentypen an. Abbildung 25 zeigt, wie man Attribute mit LOBDatentypen definiert. Bei der Definition eines LOB-Datentyps m¨ ussen zwei verschiedene Optionen spezifiziert werden. Einmal muß zwischen LOGGED/NOT LOGGED unterschieden werden. Bei der Stan¨ dardeinstellung LOGGED werden die Anderungen in diesem Attribut mitprotokolliert. Bei 50

Abbildung 25: Definition eines LOB-Attributs ¨ der Einstellung NOT LOGGED werden Anderungen nicht mitprotokolliert. Dies ist eine Frage von Performance vs. Sicherheit. Zum anderen muß zwischen COMPACT/NOT COMPACT unterschieden werden. Bei COMPACT nehmen die Daten den kleinstm¨oglichen Platz auf der Platte ein. Bei NOT COMPACT wird etwas mehr Speicher allokiert, was Updates bei denen das Objekt w¨achst beschleunigt. Hier ein paar Beispiele f¨ ur die Verwendung von LOB-Datentypen: CREATE TABLE (Absender Empfaenger Ankunft Mitteilung

Phonemail Varchar(18), Varchar(18), Timestamp, Blob(10M) NOT LOGGED COMPACT);

CREATE TABLE (Name MatrNr Adresse AkademGrad Abschluss Bild Arbeit

Absolvent Varchar(30), Integer, Varchar(150), Varchar(50), Date, Blob(500K) NOT LOGGED COMPACT, Clob(5M) NOT LOGGED COMPACT);

5.2 5.2.1

Versuch4: Ein eigener Datentyp Laufenlassen des einfu ¨hrendes Beispiels

¨ In Ihrem Verzeichnis/auf der Webseite finden Sie die Datei udf.c. Ubersetzen Sie dieses Programm mit einem C-Compiler und und binden Sie die Funktion in SQL in ihre Datenbank ein. (Hinweise dazu finden Sie in der Datei READMEUDF.)

51

5.2.2

Ein eigener Datentyp

Implementieren Sie einen eigenen Datentyp coordinates in dem die Koordinaten eines Ortes abgespeichert werden k¨onnen. Speichern Sie die Koordinaten in einem Large Object ab. Implementieren Sie f¨ ur den Umgang mit den Koordinatenobjekten folgende Funktionen: • eine Castfunktion double, double → coordinates. • eine Funktion zur Ausgabe der Koordination, d.h. eine Funktion coordinates → char (Beispiel f¨ ur eine m¨ogliche Ausgabe: 24◦ 250 4500 N/13◦ 590 1200 W) • eine Funktion zur Berechnung von Abst¨anden (Formeln: siehe Embedded SQL-Teil) Kopieren Sie zum Testen der Funktionen die gesamte Relation Stadt um, dabei sollen die Attribute L¨ange und Breite durch ein Attribut vom Typ coordinates ersetzt werden.

52

Literatur [1] D. Chamberlin. Using the New DB2. Morgan Kaufmann, San Francisco, 1996. [2] D. Chamberlin. A Complete Guide to DB2. Morgan Kaufmann, San Francisco, 1998. [3] M. Morrison, J. Morrison, and A. Keys. Integrating Web Sites and Databases. Communications of the ACM, Vol. 45, No. 9, pp. 81-86, 2002

53