5 Benutzerdefinierte Typen und Funktionen

5
Benutzerdefinierte Typen und Funktionen
5.1
5.1.1
Theorie
Benutzerdefinierte Typen
Für jedes Quentchen Daten in der Datenbank muß ein Datentyp festgelegt sein, der angibt,
wie die Information repräsentiert wird und welche Operationen darauf angewendet werden können. Beim Aufbau einer Datenbank kann man sich vorstellen, daß zum Beispiel
für die Repräsentation von Altersangaben Integer eingesetzt werden, für Geldbeträge
Decimal(15,2) und für Winkel Double. Meistens hat man dabei bestimmte Ziele im
Hinterkopf, wie diese Daten eingesetzt werden können, d.h. welche Operationen darauf
angewendet werden dürfen. So macht es zum Beispiel Sinn, zwei Geldbeträge zusammenzuaddieren oder voneinander abzuziehen. Es ist aber wahrscheinlich nicht besonders
sinnvoll zwei Geldbeträge miteinander zu multiplizieren oder gar ein Alter und einen
Geldbetrag zu vergleichen.
In DB2 können solche Einschränkungen definiert werden, die dann vom System überwacht
werden. Mit den Schlüsselwörtern distinct type können vom Benutzer neue Datentypen
definiert werden. Dieser neue Datentyp benutzt zwar die Darstellung eines vom System zur
Verfügung gestellten Datentyps (Basistyp), wird aber trotzdem als davon verschiedener
Datentyp angesehen (daher das Schlüsselwort 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ür das Löschen:
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önnen. 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öglichkeiten:
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ächsten Punkt dieses Teils, den benutzerdefinierten
Funktionen.
5.1.2
Benutzerdefinierte Funktionen
Bei benutzerdefinierten Funktionen wird grundsätzlich 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ür 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önnen arithmetische Operatoren wie
“+” oder “*” können als Namen angegeben werden, müssen aber in doppelten
Anführungszeichen eingefaßt werden.
2. (datatype, ...): hier müssen die Datentypen der Funktionsargumente angegeben
werden. Die Klammern müssen auch angegeben, falls keine Argumente vorhanden
sind.
3. RETURNS Klausel: gibt den Datentyp des Rückgabewerts an.
4. SPECIFIC Klausel: hiermit können verschiedene Instanzen von namensgleichen
Funktionen angelegt werden (wird von uns nicht benötigt).
43
Abbildung 20: CREATE FUNCTION Anweisung (für 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ür den benutzerdefinierten Typ Geld angelegt
werden könnten:
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önnen neue Funktionen für Basistypen definiert werden oder eben beliebige Funktionen für benutzerdefinierte
44
Typen. Durch die Installation von externen Funktionen in einer Datenbank wird diese allen Applikationen (selbst interaktiven SQL-Anfragen) zur Verfügung gestellt, d.h. diese
Funktion muß nicht jedesmal neu implementiert werden.
Abbildung 21: CREATE FUNCTION Anweisung (für externe Funktionen)
Abbildung 21 zeigt das Syntaxdiagramm für einen create function Aufruf für externe
Funktionen. Die einzelnen Teile der Abbildung haben folgende Bedeutung:
1. function-name: der Name der Funktion.
2. (datatype, ...): Datentypen der Funktionsargumente. Beim Übergeben der Argumente an das C-Programm werden die SQL-Datentypen in die entsprechenden
C-Datentypen umgewandelt. Eine Übersicht 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 übergeben werden können. Diese müssen vorher
in einen Double oder Char Wert umgewandelt werden.
3. RETURNS Klausel: gibt den Datentyp des Rückgabewerts an. Dabei werden die Typen nach Abbildungen 22 und 23 umgewandelt. Falls ein Decimal-Wert zurückge45
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 übersetzte C-Funktion findet. Die ausführlichste Form dieser Klausel gibt den vollen Pfad der ausführbaren Binärdatei 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önnen mehrere Funktionen in der gleichen Datei enthalten sein und diese Funktionen müssen auch nicht den gleichen Namen wie die
zugehörige 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öglichkeiten 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öglichkeiten 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ößt (z.B. eine Mail schicken, etwas in eine Datei schreiben).
8. FENCED oder NOT FENCED: Bei einer Angabe von FENCED läuft 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ürzen zu üblen Effekten führen kann). Nur ein Benutzer mit Datenbankadministratorrechten 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 übergeben werden, d.h. die Funktion müs sich selbst um diesen Sonderfall
kümmern. Bei einer Angabe von NOT NULL CALL wird die Funktion erst gar nicht
aufgerufen, es wird sofort das Ergebnis NULL zurückgeliefert.
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ür 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ürfen. (Zur Zeit dürfen externe Funktionen keine Datenbankzugriffe ausführen.
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ür 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ür jeden Datentyp
gibt es einen symbolischen Namen (aus der Headerdatei sqludf.h), der vor dem CDatentyp bevorzugt verwendet werden sollte.
2. Der nächste 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 übernommen.
3. Die nächsten n Parameter sind die Nullindikatoren für die Eingabeparameter. Jeder
dieser Parameter ist ein Zeiger auf einen Wert vom Typ short, der gleich 0 ist,
wenn der zugehörige Parameter nicht gleich NULL ist und gleich -1 ist, wenn der
zugehörige Parameter gleich NULL ist. Diese Parameter existieren immer, selbst
wenn die Funktion mit NOT NULL CALL deklariert wurde.
4. Der nächste Parameter ist der Nullindikator für den Rückgabewert. Die Konventionen entsprechen denen für die Eingabenullindikatoren.
48
5. In SQLSTATE sollte der Zustand nach Ausführung 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 ändern, falls beim
Ablauf der Funktion Unregelmäßigkeiten aufgetreten sind. Die Codes “01H00” bis
“01H99” sind für benutzerdefinierte Warnungen reserviert, die Codes “38600” bis
“38999” für benutzerdefinierte Fehler.
6. Der nächste Parameter gibt in einem String den vollen Namen der SQL-Funktion
an. Auf diese Weise können verschiedene SQL-Funktionen durch eine C-Funktion
realisiert werden (falls dies wirklich erwünscht ist).
7. Hier steht der Instanzname der Funktion, wie er in der SPECIFIC Klausel angegeben
wurde.
8. Der nächste Parameter ist ein Zeiger auf einen Speicherbereich von 70 Characters
in dem eine Fehlermeldung abgelegt werden kann.
9. Der nächste 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ährend 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ächste 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ücksprung aus der Routine sollte mit dem return Befehl durchgeführt werden.
Auf keinen Fall sollte mit exit ausgestiegen werden.
49
• Die Routine sollte nicht versuchen etwas über stdin zu lesen (scanf) oder über
stdout auszugeben (printf).
Eine benutzerdefinierte Funktion kann mit dem DROP-Kommando wieder gelöscht werden.
Dieser Befehl entfernt nur den SQL-Teil der Funktion, die Binärdatei steht weiterhin in
dem Verzeichnis in der sie vorher auch stand. Eine Funktion kann nicht gelöscht werden,
wenn sie zum Löschzeitpunkt 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önnen. 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ären 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üssen zwei verschiedene Optionen spezifiziert
werden. Einmal muß zwischen LOGGED/NOT LOGGED unterschieden werden. Bei der Standardeinstellung LOGGED werden die Änderungen in diesem Attribut mitprotokolliert. Bei
50
Abbildung 25: Definition eines LOB-Attributs
der Einstellung NOT LOGGED werden Änderungen 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öglichen Platz auf
der Platte ein. Bei NOT COMPACT wird etwas mehr Speicher allokiert, was Updates bei
denen das Objekt wächst beschleunigt. Hier ein paar Beispiele für 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 einführendes Beispiels
In Ihrem Verzeichnis/auf der Webseite finden Sie die Datei udf.c. Übersetzen 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önnen. Speichern Sie die Koordinaten in einem Large Object
ab. Implementieren Sie für 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ür eine mögliche Ausgabe: 24◦ 250 4500 N/13◦ 590 1200 W)
• eine Funktion zur Berechnung von Abständen (Formeln: siehe Embedded SQL-Teil)
Kopieren Sie zum Testen der Funktionen die gesamte Relation Stadt um, dabei sollen die
Attribute Länge 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