TEIL 2 Objektorientierte Erweiterungen im SQL:1999-Standard 왘 Große Objekte (Large Objects – LOBs) 왘 Typkonstruktoren 왘 Benutzerdefinierte Datentypen 왘 Kollektionen im SQL:200x-Standard 왘 SQLJ-Standard 3 Große Objekte (Large Objects – LOBs) Charakteristisch für Datenbankanwendungen in den frühen neunziger Jahren des zwanzigsten Jahrhunderts war, daß die zur Verfügung stehenden Standarddatentypen (wie CHAR und VARCHAR) in den meisten Fällen gereicht haben, um die Daten der Anwendung darzustellen. Die Lage hat sich signifikant geändert, seitdem die Notwendigkeit existiert, Photos (der Mitarbeiter), Bilder (der angebotenen Produkte) und ähnliche Objekte in Datenbanken zu speichern. Für alle oben genannten gilt, daß sie sehr groß sind und dementsprechend viel Speicherplatz brauchen. Tabelle 3.1 zeigt eine Liste von Objekten, die heutzutage als Datenbankobjekte gespeichert werden, und den Umfang des Speichers, der für ihre Speicherung notwendig ist. Alle in Tabelle 3.1 aufgelisteten Daten werden große Objekte genannt, was der Übersetzung des englischen Begriffs large objects (LOBs) entspricht. Objekt Größe Text ca. 30-40 KByte pro Seite Kleines Bild ca. 30-40 KByte Grosses Bild ca. 200-3.000 KByte Farbbild ca. 20-40 MByte Röntgenbild ca. 40-60 MByte Video ca. 1 GByte pro Stunde High Resolution Video ca. 3 GByte pro Stunde Bei den Arbeiten am SQL:1999-Standard wurde die Bedeutung von großen Objekten erkannt, und das Ergebnis war, daß LOBs im Standard berücksichtigt wurden. Die Eigenschaft von LOBs ist, daß sie große Objekte (bis mehrere Gigabytes Größe) speichern können. Zu den LOBs gehören drei Datentypen: 왘 CLOB – character large objects bzw. große textuelle Objekte 왘 BLOB – binary large objects bzw. große binäre Objekte 왘 NCLOB – national character large objects Der Datentyp CLOB speichert sehr große Textdateien, während BLOB für die Speicherung sehr großer binärer Dateien zur Verfügung steht. Neben den »Grund«-Datentypen BLOB und CLOB unterstützt der SQL:1999Standard auch den Datentyp NCLOB. Dieser Datentyp ist das Synonym für CLOB-Objekte, die zu einem (vordefinierten) nationalen Zeichensatz gehören. (Deutsche Texte, zu denen Umlaute gehören, sind ein typisches Beispiel für Instanzen des NCLOB-Datentyps.) Tab. 3.1: Liste von Objekten mit ihrer durchschnittlichen Größe Große Objekte (Large Objects – LOBs) Hinweis Das Bedürfnis, große Objekte durch entsprechende Datentypen definieren zu können, existiert schon seit einigen Jahren, und die entsprechende Unterstützung durch den Standard ist relativ spät gekommen. Aus diesem Grund haben die meisten Hersteller relationaler Datenbanksysteme lange vor dem Erscheinen des SQL:1999-Standards Datentypen für große Objekte implementiert. Die Namen dieser Datentypen sind dementsprechend bei unterschiedlichen Herstellern verschieden und entsprechen nicht unbedingt den Namen, die der SQL:1999-Standard verwendet. Oracle unterstützt seit langem die Datentypen LONG und LONG RAW. Der Datentyp LONG speichert textuelle Daten bis zu einer Größe von 2 GByte, während LONG RAW für die Speicherung von großen binären Daten zur Verfügung steht. Microsoft SQL Server unterstützt seit der ersten Version die Datentypen TEXT und IMAGE, die den Oracle Datentypen LONG und LONG RAW (in dieser Reihenfolge) entsprechen. Schließlich unterstützt INFORMIX zwei ähnliche Datentypen namens TEXT und BYTE. Inzwischen haben alle wichtige Datenbankhersteller oben genannte LOB-Typen implementiert. 3.1 LOB-Eigenschaften Alle Objekte, die als LOBS definiert werden, haben in SQL:1999 folgende Eigenschaften: 왘 Objekte werden direkt von der Datenbank verwaltet. 왘 Die maximale Größe eines Objektes kann explizit bei der Spaltendefinition (in KByte, MByte oder GByte) angegeben werden. Große Objekte vom Typ CLOB, BLOB oder NCLOB werden genauso wie alle anderen Datenbankobjekte vom Datenbanksystem verwaltet. Das heißt, daß das Datenbanksystem für die Erstellung und Löschung solcher Objekte verantwortlich ist. Zusätzlich dazu muß das System die entsprechenden Funktionen zur Verfügung stellen, mit denen die Bearbeitung (das Lesen und Schreiben) dieser Objekte möglich ist. Obwohl LOBS Datenbankobjekte sind, werden sie wegen ihrer Größe nicht unbedingt zusammen mit anderen Objekten einer Datenbank gespeichert. Generell gilt, daß sie entweder in Datenbankspeicherbereichen wie alle andere Datenbankobjekte oder in (Datenbank-)Dateien gespeichert werden. Diese beiden Möglichkeiten der Speicherung haben ihre Vor- und Nachteile: Die Speicherung der großen Objekte in einer Datei kann die Performance des Systems verbessern, weil diese Objekte für die schnelle Bearbeitung anderer Objekte hinderlich sein können, besonders wenn es sich um sehr große Objekte (in Megabyte- und Gigabyte-Größe) handelt. Auf der anderen Seite können Objekte, die in einer Datei gespeichert sind, nicht so bearbeitet werden wie beliebige alphanumerische oder numerische Werte, die direkt in der Datenbank gespeichert sind. Das heißt, daß das Lesen und Schreiben der in Dateien gespeicherten großen Objekte vielen Einschränkungen unterworfen ist. Der SQL:1999-Standard ermöglicht die explizite Angabe der LOB-Größe bei der Definition der Spalte. Die Angaben K, M und G spezifizieren KByte, MByte und GByte in dieser Reihenfolge (siehe Beispiel 3.1). 58 Große Objekte (Large Objects – LOBs) Beispiel 3.1 CREATE TABLE buch_tabelle (isbn CHAR(13), titel VARCHAR(100), inhaltsverzeichnis CLOB(20K), buch_datei CLOB(20M), begleit_film BLOB(2G)); Beispiel 3.1 erstellt eine Tabelle, die zwei Spalten mit Standarddatentypen (isbn und titel) hat, und drei Spalten, die als große Objekte definiert sind. Das Inhaltsverzeichnis und das ganze Buch (buch_datei) sind als Textdateien mit der maximalen Größe von 20 KByte bzw. 20 MByte definiert, während der zum Buch begleitende Film (begleit_film) als großes binäres Objekt definiert ist. Bei der Verwendung des NCLOB-Datentyps spielt die sogenannte COLLATEAngabe eine wichtige Rolle. Bei COLLATE handelt es sich um die Angabe der Regelmenge, die die Ordnung der verwendeten Zeichen festlegt. Dies ist auch notwendig, weil nationale Zeichensätze spezielle Zeichen enthalten, die dem standardmäßigen Zeichensatz nicht bekannt sind. Neben der Ordnung der Standardzeichen hat der Benutzer die Möglichkeit, eigene Ordnungen der Zeichen zu definieren. (Genauso können unterschiedliche Implementierungen verschiedene Ordnungen der Zeichen festlegen.) Beispiel 3.2 zeigt eine ähnliche Tabelle wie in 3.1, diesmal mit nationalen Zeichen. Beispiel 3.2 CREATE TABLE buch_deutsch (isbn CHAR(13), titel VARCHAR(100), inhaltsverzeichnis NCLOB(20K) COLLATE mein_satz1, buch_datei NCLOB(20M) COLLATE mein_satz2, begleit_film BLOB(2G)); Im Unterschied zu Beispiel 3.1 verwendet Beispiel 3.2 nationale Zeichensätze für die Definition der Spalten inhaltsverzeichnis und buch_datei, wobei die Zeichen nach zwei verschiedenen Kriterien sortiert sind. (Die Ordnungskriterien sind in den Dateien main_satz1 und mein_satz2 definiert.) 3.2 Einschränkungen bezüglich LOBs Große Objekte können in vielen Fällen genauso behandelt werden wie alle anderen Spaltenwerte, die mit Hilfe von Standarddatentypen (CHAR, INT usw.) definiert sind. Wegen ihrer Größe gibt es aber einige Unterschiede bei der Behandlung von Werten der Standarddatentypen und von LOB-Werten. Alle LOBEinschränkungen haben ihre Berechtigung, obwohl sie auf den ersten Blick nicht unbedingt als selbstverständlich erscheinen. Der SQL:1999-Standard erlaubt nicht die Verwendung von LOBs: 왘 mit den Vergleichsoperatoren < (kleiner als) und > (größer als) 왘 bei der Definition einer UNIQUE-Integritätsregel 59 Große Objekte (Large Objects – LOBs) 왘 in der GROUP BY- bzw. ORDER BY-Klausel 왘 im Zusammenhang mit den UNION-, INTERSECT- und EXCEPT-Operatoren 왘 als Join-Spalte Für die meisten der oben angegebenen Einschränkungen gilt, daß sie wegen der Größe von LOBS notwendig sind. Falls man als Beispiel die zweite Einschränkung betrachtet, ist es offensichtlich, daß ein großes Objekt niemals in der Praxis als Primärschlüssel verwendet werden soll, weil eine wichtige Regel beim Datenbankdesign besagt, daß Primärschlüssel generell so kurz wie möglich sein sollen. 3.3 LOB-Funktionen Zu den Funktionen, die auf LOBS angewendet werden können, gehören: 왘 CAST 왘 Zuweisung 왘 Vergleich 왘 diverse skalare Funktionen 왘 diverse Mengenfunktionen 왘 Konkatenation Zusätzlich zu den oben angegebenen Funktionen können große Objekte auch mit dem LIKE-Prädikat (innerhalb einer SELECT-Anweisung) benutzt werden. Hinweis In diesem Abschnitt werden wir alle oben genannten Funktionen mit Hilfe des BLOB-Datentyps darstellen. Ihre Verwendung auf CLOB-Objekte ist analog. Weil die Konkatenation zweier BLOBS genauso wie die Konkatenation anderer Zeichenketten (mit Hilfe des »||«-Operators) durchgeführt wird, werden wir sie nicht weiter erläutern. 3.3.1 Der CAST-Operator Der CAST-Operator ist ein Operator, der einen skalaren Wert (Quelle) in einen anderen skalaren Wert eines anderen Datentyps (Ziel) konvertiert. Ein BLOBObjekt kann: 왘 in ein anderes BLOB-Objekt 왘 in einen benutzerdefinierten Datentyp bzw. einen Referenztyp (siehe Kapitel 5) umgewandelt werden. Bei der Umwandlung eines BLOB-Objektes in ein anderes BLOB-Objekt wird das Zielobjekt identisch dem Quellobjekt sein, falls die Länge des Zielobjektes großer oder gleich der Länge des Quellobjektes ist. Falls die Länge des Zielobjektes kleiner ist, wird der passende Teil des Quellobjektes übertragen und das System meldet eine Warnung. Diese Warnung sollte im Programm abgefangen werden. Der Code für die Warnung ist standardisiert und steht dem Programm über die Variable SQLSTATE zur Verfügung. Der standardisierte Wert ist 01004. 60 Große Objekte (Large Objects – LOBs) Bei der Umwandlung eines BLOB-Objektes in einen benutzerdefinierten Datentyp wird vom System vorausgesetzt, daß eine (benutzerdefinierte) Umwandlungsfunktion existiert, deren Parameter dem Quellobjekt entspricht. Das Ergebnis dieser Umwandlung ist der Wert, dessen Typ dem Rückgabetyp der Umwandlungsfunktion entspricht. Für die Beschreibung der benutzerdefinierten Typen siehe Kapitel 5. Beispiel 3.3 CAST (12345 AS BLOB) In Beispiel 3.3 wird eine Ganzzahlkonstante in ein BLOB-Objekt umgewandelt. Weil zu erwarten ist, daß die Länge des Zielobjektes >=5 ist, wird die ganze Konstante übertragen. 3.3.2 Zuweisung Bei der Zuweisung kann entweder ein BLOB-Objekt einer binären Zeichenkette oder umgekehrt zugewiesen werden. (Die Zuweisung wird byteweise von links nach rechts durchgeführt.) Bei der Zuweisung eines BLOB-Objektes zu einer binären Zeichenkette wird zuerst überprüft, ob die binäre Zeichenkette das ganze BLOB-Objekt aufnehmen kann. Falls die Aufnahme des ganzen BLOB-Objektes möglich ist, wird die Übertragung durchgeführt, während im anderen Fall das gekürzte Quellobjekt übertragen und die entsprechende Warnung ausgegeben wird. Bei der Zuweisung einer binären Zeichenkette zu eine Spalte vom Typ BLOB wird das Ziel der Länge der Quelle angepaßt, indem es entweder bis auf die Länge der binären Zeichenkette gekürzt oder auf die geeignete maximale Länge gesetzt wird. (Das hängt davon ab, welche der beiden Längen geringer ist.) Danach werden die Werte byteweise zugewiesen. Bei der Zuweisung eines BLOB-Objektes mit dem Wert NULL bleibt das Zielobjekt unverändert. 3.3.3 Vergleich Der SQL:1999-Standard unterstützt nur zwei Vergleichsoperationen auf BLOBS: 왘 gleich (=) 왘 ungleich (<>) Der Vergleich zweier BLOB-Objekte wird genauso wie bei der Zuweisung byteweise von links nach rechts durchgeführt. 3.3.4 Skalare Funktionen Der SQL:1999-Standard unterstützt drei skalare Funktionen, die ein BLOBObjekt liefern: 왘 SUBSTRING 왘 OVERLAY 왘 TRIM 61 Große Objekte (Large Objects – LOBs) Zusätzlich dazu werden drei Funktionen, die auf BLOB-Objekte angewendet werden können und eine Ganzzahl bzw. den NULL-Wert liefern, unterstützt: 왘 POSITION 왘 NULLIF 왘 LENGTH Die SUBSTRING-Funktion nimmt ein BLOB-Objekt als Eingabe und liefert eine Teilkette von Oktets als Ausgabe. Sie enthält immer drei Parameter, wobei der erste ein BLOB-Objekt darstellt und die anderen beiden je eine Ganzzahl spezifizieren. Der zweite Parameter kennzeichnet den Anfang und der dritte das Ende der Teilkette. Beispiel 3.4 SUBSTRING (begleit_film FROM 1 TO 1000) --liefert die ersten tausend Oktetwerte Die OVERLAY-Funktion enthält bis zu vier Parameter, wobei die ersten beiden BLOB-Objekte darstellen und die letzten beiden Ganzzahlen sind. Diese Funktion ersetzt den Teil des ersten BLOB-Objektes durch das zweite BLOB-Objekt ab der Stelle, die durch den dritten Parameter spezifiziert wird. Der vierte Parameter ist optional und spezifiziert die Anzahl von Oktets, die im ersten BLOB-Objekt ersetzt werden sollen. (Falls dieser Parameter ausgelassen wird, wird die Anzahl der ersetzten Oktets mit der Anzahl der Oktets im zweiten Parameter identisch sein.) Die TRIM-Funktion umfaßt zwei Parameter, wobei beide Parameter BLOB-Objekte sind. Diese Funktion entfernt alle im ersten Parameter angegebenen Oktetwerte aus dem zweiten Parameter. Abhängig von der Angabe wird das Entfernen am Anfang (die Angabe LEADING), am Ende (die Angabe TRAILING) oder sowohl am Anfang als auch am Ende (die Angabe BOTH) des BLOB-Objektes durchgeführt. Die POSITION-Funktion enthält zwei Parameter, die beide BLOB-Objekte sind. Der Wert des ersten Parameters wird als Teilkette im zweiten BLOB-Objekt gesucht, und der Rückgabewert der Funktion ist diejenige Ganzzahl, die die Position des ersten Parameters innerhalb des zweiten spezifiziert. Falls das erste BLOB-Objekt keine Teilkette des zweiten Objekts darstellt, ist das Ergebnis 0. Die NULLIF-Funktion liefert den NULL-Wert, falls die beiden Parameter identische BLOB-Objekte darstellen. Anderenfalls wird das erste BLOB-Objekt als Ergebnis geliefert. Die LENGTH-Funktion ist eine generische Funktion mit einem Parameter, der ein BLOB-Objekt ist. Das Ergebnis dieser Funktion hängt von der Ausprägung ab: Falls sie als BIT_LENGTH-Funktion verwendet wird, liefert sie die Anzahl von Bits des BLOB-Objektes. Die zwei weiteren Formen dieser Funktion – CHAR_LENGTH und OCTET_LENGTH – haben identische Bedeutung; sie liefern die Anzahl von Oktetwerten eines BLOB-Objektes als Ergebnis. Hinweis 62 Alle oben beschriebenen Funktionen können selbstverständlich statt BLOBObjekten auch andere Zeichenketten (einzeln oder paarweise) als Parameter haben. Große Objekte (Large Objects – LOBs) 3.4 LOBs und Anwenderprogramme Wir haben im vorherigen Abschnitt gesehen, daß gerade für LOBS gewisse Einschränkungen wegen ihrer Größe gelten, die für andere Datentypen nicht existieren. Ähnlich sieht es bei der Verwendung von LOBS in Datenbankanwendungen aus. Wegen ihrer Größe können folgende Probleme auftreten, falls ein LOB in einem Anwendungsprogramm verwendet werden soll: 왘 Enorme Speicherplatzmengen können notwendig sein, falls ein LOB-Wert gespeichert werden soll. 왘 Bei der Pufferung eines LOBS ist es durchaus möglich, daß ein ausreichend großer zusammenhängender Puffer im Speicher nicht zur Verfügung steht. 왘 Anwendungen brauchen manchmal nur Teile eines LOBS. Das heißt, daß mehrere Anfragen in Bezug auf dasselbe LOB benutzt werden müssen. Alle diese Gründe haben dazu geführt, daß Anwendungsprogramme, die z.B. in ESQL/C oder SQLJ geschrieben sind, die Daten nicht direkt in einer Host-Variablen speichern, sondern den sogenannten Lokator verwenden. (Host-Variablen sind ausführlich in Kapitel 1 beschrieben.) Ein Lokator ist ein vier Byte großer Wert, der in einer Host-Variablen gespeichert ist. Dieser Wert wird von Programmen verwendet, um einen LOB-Wert zu referenzieren. Um einen Lokator zu verwenden, muß die Lokator-Variable deklariert und diese dann auf den aktuellen Wert des entsprechenden LOBS gesetzt werden. Beispiel 3.5 zeigt die Deklaration eines Lokators und seine Zuweisung an einen LOB-Wert mit Hilfe von ESQL/C. Beispiel 3.5 EXEC SQL BEGIN DECLARE SECTION; SQL TYPE IS CLOB AS LOCATOR inhalts_lokator SQL TYPE IS BLOB AS LOCATOR film_lokator; EXEC SQL END DECLARE SECTION; EXEC SQL SELECT inhaltsverzeichnis, begleit_film INTO :inhalts_lokator, :film_lokator FROM buch WHERE isbn = '3-8273-1889-0'; In Beispiel 3.5 wurden zuerst zwei Lokatoren definiert; der eine für die CLOBSpalte inhaltsverzeichnis und der andere für die BLOB-Spalte begleit_film. Wie aus der ersten SQL-Anweisung ersichtlich, werden CLOB- und BLOB-Lokator getrennt voneinander deklariert. In der zweiten Anweisung wird eine SELECT-Anweisung mit der INTO-Klausel verwendet, um die beiden großen Objekte der Anwendung zur Verfügung zu stellen. Wie aus dem Deklarationsteil des Beispiels 3.5 ersichtlich, werden beim SQL:1999-Standard verschiedene Arten von Lokatoren benutzt, abhängig vom Typ des großen Objektes: 왘 CLOB-Lokator für textuelle große Objekte (SQL TYPE IS CLOB AS LOCATOR) 왘 BLOB-Lokator für binäre große Objekte (SQL TYPE IS BLOB AS LOCATOR) 왘 NCLOB-Lokator für textuelle große Objekte, die auf einem nationalen Zeichensatz basieren (SQL TYPE IS NCLOB AS LOCATOR) 63 Große Objekte (Large Objects – LOBs) Die Verbindung zwischen einem Lokator und dem entsprechenden Wert kann explizit oder implizit aufgelöst werden. Die implizite Auflösung der Verbindung wird nach der Beendigung des Programms, in dem der Lokator deklariert ist, durchgeführt. Bei der expliziten Auflösung der Verbindung muß die FREE-Anweisung verwendet werden. Die Syntax dieser Anweisung ist: FREE LOCATOR lokator_name; 3.4.1 HOLD-Lokator Ein Lokator kann entweder gültig (valid) oder ungültig (invalid) sein. Nach der Deklaration eines Lokators wird dieser als gültig gekennzeichnet, und er bleibt gültig bis zur Beendigung der Transaktion, in der er generiert wurde. Damit ist der Gültigkeitsbereich eines Lokators auf eine einzige Transaktion eingeschränkt, was unter Umständen eine große Einschränkung für die Programmierung bedeuten kann. Um diese Einschränkung zu beheben, führt der SQL:1999Standard den Begriff des HOLD-Lokators ein. Ein HOLD-Lokator ist ein Lokator, dessen Wert nach erfolgreicher Beendigung der Transaktion, in der er zuletzt gesetzt wurde, erhalten bleibt. Die Definition eines solchen Lokators erfolgt mit Hilfe der HOLD LOCATOR-Anweisung. (Dies gilt nur, falls die Transaktion, in der der Lokator generiert wird, erfolgreich beendet wurde.) Beispiel 3.6 zeigt die Verwendung eines HOLD-Lokators. Beispiel 3.6 ................................ EXEC SQL SELECT inhaltsverzeichnis, begleit_film INTO :inhalts_locator, :film_locator FROM buch WHERE isbn = '3-8273-1889-0'; HOLD LOCATOR :film_locator; ……………………………. EXEC SQL COMMIT; ……………………………. EXEC SQL INSERT INTO buch VALUES(………, :film_locator); In Beispiel 3.6 wird der BLOB-Lokator, der zur Spalte begleit_film gehört, als HOLD-Lokator deklariert. Durch diese Deklaration wird das System seine Gültigkeit nach der Beendigung der Transaktion (mit der COMMIT-Anweisung), in der er generiert wurde, aufrechterhalten. Dementsprechend kann die anschließende INSERT-Anweisung erfolgreich ausgeführt werden. Hinweis 64 Alle gültigen Lokatoren werden als ungültig markiert, falls die Transaktion durch eine ROLLBACK-Anweisung beendet wird (d.h. falls eine Transaktion mit einem Fehler abgeschlossen wird). Große Objekte (Large Objects – LOBs) 3.5 Zusammenfassung In diesem Kapitel sind alle vom SQL:1999-Standard unterstützten große Objekten (LOBS): 왘 CLOB 왘 BLOB 왘 NCLOB beschrieben. Wegen ihrer Größe werden LOBS in den Anwenderprogrammen nicht direkt in Host-Variablen gespeichert, sondern mit Hilfe von Lokatoren verwendet. Ein Lokator wird von Programmen verwendet, um einen LOB-Wert zu referenzieren. Basierend auf den drei standardisierten Typen für große Objekte existieren drei entsprechende Lokatorarten: 왘 CLOB-Lokator für textuelle große Objekte 왘 BLOB-Lokator für binäre große Objekte 왘 NCLOB-Lokator für textuelle große Objekte, die auf einem nationalen Zeichensatz basieren Das nächste Kapitel beschreibt die im SQL:1999-Standard definierten Typkonstruktoren. 65