4. Objektrelationales Typsystem

4. Objektrelationales Typsystem
Inhalt und Lernziele
4. Objektrelationales Typsystem
Inhalt (u.a.):
•
•
•
•
•
•
•
Basisdatentypen
Einzigartige Typen (Distinct Types)
Kollektionstypen
Strukturierte Typen
Benutzerdefinierte Typen
Typhierarchien
Typumwandlungen
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
114
4. Objektrelationales Typsystem
Basisdatentypen in SQL
Basisdatentypen SQL
Vordefinierte Basisdatentypen im SQL-Standard:
•
•
•
•
•
•
•
•
•
•
•
Wahrheitswert: BOOLEAN
ganze Zahl: SMALLINT, INTEGER, BIGINT
Festkommazahl: DECIMAL(p,q), NUMERIC(p,q)
Fließkommazahl: REAL, DOUBLE PRECISION, FLOAT(p)
alphanumerische Zeichenkette: CHAR(p), VARCHAR(p), CLOB, NCLOB
binäre Zeichenkette: BLOB
Datum: DATE
Zeit: TIME
Zeitstempel: TIMESTAMP
Zeitintervall: INTERVAL
XML-Wert: XML
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
115
4. Objektrelationales Typsystem
Basisdatentypen in SQL
LOBs (Large Objekts)
CLOB Character Large Object, alphanumerische Zeichenkette bestehdn aus 1-ByteZeichen
NCLOB National Character Large Object, Speicherung langer Unicode-Zeichenketten
BLOB Binary Large Object, binäre Zeichenketten
• maximale Längen deutlich über den sonstigen Typen für Zeichenketten, in IBM
DB2 z.B. 2 GB
• Optional Einschränkung der maximalen Länge möglich
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
116
4. Objektrelationales Typsystem
Basisdatentypen in SQL
Beispiel (DB2):
create table studenten
(
matrikelnummer Char(7),
name
Varchar(30),
adresse
Varchar(200),
grad
Varchar(30),
photo
Blob( 5M ) not logged compact, -- Bild
abschlusstag
Date,
abschlussarbeit Clob( 500K ) not logged compact -- Text
);
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
117
4. Objektrelationales Typsystem
Basisdatentypen in SQL
Unterschied zwischen Lobs und gewöhnlichen Datentypen
1. Die Daten von LOBs werden in separaten Speicherbereichen abgelegt. Innerhalb
der Tabelle wird ein Deskriptor gespeichert, der zur Lokalisation des LOB dient.
2. LOBs können in Anwendungsprogrammen durch sogenannte Lokatoren manipuliert weden. Dies entspricht ungefähr einem Zeiger in den Speicherbereich für
LOBs.
3. Attribute, die auf einem LOB-Datentyp basieren, können vom Logging ausgenommen werden.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
118
4. Objektrelationales Typsystem
Basisdatentypen in SQL
Typische Einschränkungen bei LOBs
1. Keine Verwendung in Prädikaten möglich, die direkte Vergleiche beinhalten. Verwendung bei LIKE ist erlaubt (als linker Operand).
2. Keine Verwendung in einem Kontext, der implizit Vergleiche erfordert, z.B.: SELECT
DISTINCT, COUNT DISTINCT, MAX, MIN, GROUP BY, ORDER BY, PRIMARY KEY, FOREIGN
KEY.
3. Keine Kombination durch Mengenoperatin möglich: INTERSECT, UNION, EXCEPT.
4. In der Regel keine Replikation möglich.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
119
4. Objektrelationales Typsystem
Basisdatentypen in SQL
XML
XML-Wert kann sein:
•
•
•
•
XML-Element
Wald von XML-Elementen
Geschachtelte XML-Elemente
XML-Dokument
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
120
4. Objektrelationales Typsystem
Einzigartige Typen
Einzigartige Typen (Distinct Types)
Beim Aufbau einer Datenbank entscheidet man sich häufig dafür, einen vordefinierten
Datentyp in bestimmter Weise zu verwenden, z.B.:
• Integer für Altersangaben
• Decimal(8,2) für Geldbeträge
• Double für Winkel
Problem:
☞ Mit den vordefinierten Operationen ist u.U. keine sinnvolle Interpretation für die
repräsentierten Werte verbunden.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
121
4. Objektrelationales Typsystem
Einzigartige Typen
Erzeugung einzigartiger Typen
• ORDBS bieten die Möglichkeit, für eine spezialisierte Verwendung von Daten eigene Typen zu erzeugen, sogenannte einzigartige Typen (distinct types).
• siehe auch SQL-99 (core level)
• Jeder der einzigartigen Typen hat mit einem der vordefinierten Typen seine interne
Darstellung gemeinsam.
• Der vordefinierte Typ heißt in diesem Fall Quell- bzw. Basistyp.
• Abgesehen von der internen Darstellung wird der einzigartige Typ als separater
Datentyp betrachtet, der von allen anderen Typen verschieden ist.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
122
4. Objektrelationales Typsystem
Einzigartige Typen
SQL-Definition einzigartiger Typen
SQL-Standard:
CREATE TYPE typname AS Basistyp [FINAL]
Beispiele für IBM DB2:
CREATE DISTINCT TYPE Geschlecht AS char(1) WITH COMPARISONS;
CREATE DISTINCT TYPE Geld AS decimal(8,2) WITH COMPARISONS;
CREATE DISTINCT TYPE Geometrie.Winkel AS Double WITH COMPARISONS;
CREATE DISTINCT TYPE Song AS Blob(300M);
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
123
4. Objektrelationales Typsystem
Einzigartige Typen
Bemerkungen zur Definiton
• Eine Instanz eines einzigartigen Typs wird nur mit einer Instanz des desselben
Typs als vergleichbar betrachtet.
• Der Zusatz WITH COMPARISONS ist notwendig, wenn der Quelltyp kein LOB-Typ ist.
• Für solche einzigartigen Typen stehen die üblichen Vergleichsoperatoren zur
Verfügung.
Die Definition der Vergleichsoperatoren ist identisch mit der des Basistyps.
• Die Verwendung eines solchen einzigartigen Typs bei ORDER BY, GROUP BY und
DISTINCT ist möglich. Auch können Indexe auf Basis solch eines Typs definiert
werden.
• Da für LOB-Typen keine Vergleiche unterstützt werden, läßt man dort WITH
COMPARISONS weg.
• Hier auch kein ORDER BY, GROUP BY, DISTINCT und auch keine Indexe.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
124
4. Objektrelationales Typsystem
Einzigartige Typen
Verwendung
Einen einzigartigen Typ kann man wie einen vordefinierten Typ bei der Definition einer
Tabelle verwenden:
CREATE TABLE angestellte (
angnr
char(5),
abtnr
char(5),
name
varchar(30),
geschlecht Geschlecht,
gehalt
Geld );
Mit der DROP-Anweisung werden einzigartigen Typen gelöscht (DB2):
DROP DISTINCT TYPE Video;
DROP DISTINCT TYPE Geometrie.Winkel;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
125
4. Objektrelationales Typsystem
Einzigartige Typen
Casting-Funktionen
Bei Erzeugung eines einzigartigen Typs werden automatisch zwei Casting Funktionen
angelegt:
• Eine Funktion f : Basistyp −→ DistinctT ype
Diese Funktion trägt den Namen des einzigartigen Typs.
• Eine Funktion f −1 : DistinctT ype −→ Basistyp
Diese Funktion trägt den Namen des Basistyp.
Beispiel: Für den einzigartigen Typ
CREATE DISTINCT TYPE alter IS integer;
werden die Funktionen mit den folgenden Signaturen erzeugt:
• alter( integer )
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
126
4. Objektrelationales Typsystem
Einzigartige Typen
• integer( alter )
Konsequenz: Sind alter1 und alter2 vom Typ alter, dann gilt:
• alter1 + alter2 ist nicht erlaubt, aber
• alter( integer(alter1) + integer(alter2) ) ist erlaubt.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
127
4. Objektrelationales Typsystem
Einzigartige Typen
Bemerkungen:
• Die Casting-Funktionen sind sehr effizient.
• Ist mit dem einzigartigen Typ implizit durch den Basistyp eine Länge oder Genauigkeit verbunden, so wird dies bei der Umwandlung berücksichtigt.
• Die Casting-Funktionen können entweder durch ihren Namen oder durch Anwendung eines CAST-Ausdruckes angesprochen werden.
alter(14)
CAST(14 as alter)
integer(alter1)
CAST(alter1 as integer )
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
128
4. Objektrelationales Typsystem
Einzigartige Typen
Zuweisung einzigartiger Typen
Zuweisung heißt hier:
• Eintragung eines neuen Wertes in eine Datenbanktabelle (Input): UPDATE, INSERT
INTO
• Übertragung eines Wertes von einer Datenbanktabelle in eine Wirtsvariable (Output): SELECT
Für die vordefinierten Datentypen finden hier u.U. implizite Konvertierungen (Coercions) statt.
Wie laufen Zuweisungen ab, wenn einzigartige Typen beteiligt sind?
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
129
4. Objektrelationales Typsystem
Einzigartige Typen
1. Zuweisung vordefinierter Typ T an einzigartigen Typ DT bei Input
Nur möglich, falls:
• T der Basistyp von DT ist oder
• T in den Basistyp von DT gemäß der Konversionspfade konvertiert werden
kann.
Die Konversion findet implizit statt.
2. Zuweisung einzigartiger Typ DT an vordefinierten Typ T bei Input
Nur möglich, falls T der Basistyp von DT ist (implizite Konversion).
3. Zuweisung einzigartiger Typ DT an vordefinierten Typ T bei Output
Implizite Zuweisung in zwei Schritten:
(a) Konversion DT in den Basistyp von DT
(b) Konversion des Basistyps von DT nach T nach den üblichen Regeln (falls
möglich).
Beispiel: Es seien gegeben
• CREATE DISTINCT TYPE Winkel AS double;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
130
4. Objektrelationales Typsystem
Einzigartige Typen
• Tabelle dreiecke mit einem Attribut ecke vom Typ Winkel
• Hostvariable: double v;
Sind die folgenden Zuweisungen zulässig?
EXEC SQL SELECT ecke INTO :v FROM dreiecke WHERE farbe = ’rot’;
EXEC SQL INSERT INTO dreiecke( farbe, ecke ) VALUES (’blau’, :v);
UPDATE dreiecke SET ecke = 45.5 WHERE farbe = ’gruen’;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
131
4. Objektrelationales Typsystem
Einzigartige Typen
Quellenbasierte UDFs
An einen einzigartigen Typ werden vom Basistyp nur die Vergleichsoperatoren (falls
vorhanden) vererbt:
•
•
•
•
siehe oben: alter1 + alter2 ist nicht erlaubt
Muß man nun immer Casting verwenden? Nein!
Man kann gezielt Semantik vom Basistyp auf den einzigartigen Typ übertragen.
Dies erfolgt mit sogenannten quellenbasierten Funktionen.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
132
4. Objektrelationales Typsystem
Einzigartige Typen
• Eine quellenbasierte Funktion ist eine neue UDF, die auf einer bereits existierenden
Funktion (Quellenfunktion) basiert.
• Wird die quellenbasierte Funktion aufgerufen, dann werden
1. die Argumente des Aufrufs in die Parametertypen der Quellenfunktion konvertiert
2. die Quellenfunktion wird aufgerufen und
3. das Ergebis des Aufrufs wird in den Ergebnistyp der neuen UDF konvertiert.
Quellenfunktion
+( double, double )
2. Ausführen der Quellenfuktion
1. Konvertieren
der Argumente
double
3. Konvertieren des
Ergenisses der
Quellenfunktion
+(Gewicht, Gewicht)
Gewicht
quellenbasierte Funktion
Ergebnis
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
133
4. Objektrelationales Typsystem
Einzigartige Typen
Deklaration einer quellenbasierten Funktion:
•
•
•
•
erfolgt mit CREATE FUNKTION
erlaubte bekannte Klauseln: RETURNS, SPECIFIC
Neue Klausel: SOURCE
In der SOURCE-Klausel wir die Quellenfunktion in einer der folgenden Varianten angegeben:
– Funktionsname ohne Parameter
– Spezifischer Name
– Signatur
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
134
4. Objektrelationales Typsystem
Einzigartige Typen
Beispiele:
CREATE DISTINCT TYPE Geld AS Decimal(8,2);
CREATE FUNCTION "+"( Geld, Geld ) RETURNS Geld
SOURCE "+"( Decimal(), Decimal() );
CREATE FUNCTION "-"( Geld, Geld ) RETURNS Geld
SOURCE "-"( Decimal(), Decimal() );
CREATE FUNCTION "*"( Integer, Geld ) RETURNS Geld
SOURCE "*"( Integer, Decimal() );
CREATE FUNCTION avg( Geld ) RETURNS Geld
SOURCE avg( Decimal() );
CREATE DISTINCT TYPE Artikel AS Clob( 1M );
CREATE FUNCTION posstr( Artikel, Varchar() ) RETURNS Integer
SOURCE posstr( Clob(), Varchar() );
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
135
4. Objektrelationales Typsystem
Einzigartige Typen
UDFs mit einzigartigen Typen
• Man kann natürlich über ein reines Vererben von Funktionen des Basistyps an den
einzigartigen Typ hinausgehen.
• Hierzu verwenden wir natürlich externe UDFs.
• Die Ausdruckskraft von einzigartigen Typen und externen UDFs wird am besten
deutlich, wenn beides zusammen benutzt wird.
Beispiel: Ein internationaler Konzern stellt Produkte in Deutschland und den USA her.
Die Länge der amerikanischen Produkte wird in Fuß gemessen, die der deutschen in
Meter.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
136
4. Objektrelationales Typsystem
CREATE DISTINCT TYPE Fuss
AS double WITH COMPARISONS;
Einzigartige Typen
CREATE DISTINCT TYPE Meter
AS double WITH COMPARISONS;
• Für die Produkte existieren jeweils separate Tabellen.
CREATE TABLE us_produkte (
name
varchar(20),
groesse Fuss );
CREATE TABLE de_produkte (
name
varchar(20),
groesse Meter );
• Das System stellt Casting-Funktion zwischen den einzigartigen Typen und ihren
Basistypen bereit.
• Es liefert aber keine Konvertierung zwischen Fuss und Meter. Hierfür werden UDFs
definiert.
CREATE FUNCTION fuss( Meter )
RETURNS Fuss
... ;
CREATE FUNCTION meter( Fuss )
RETURNS Meter
... ;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
137
4. Objektrelationales Typsystem
void SQL_API_FN fuss (
double * meterIn,
double * fussOut,
... )
{
*fussOut =
*meterIn * 3.28;
...
}
Einzigartige Typen
void SQL_API_FN meter (
double * fussIn,
double * meterOut,
... )
{
*meterOut =
*fussIn / 3.28;
...
}
• Der Konzern möchte zwei länderspezifische Kataloge herausgeben,
• die aber alle Produkte enthalten.
• Hierzu werden Views definiert.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
138
4. Objektrelationales Typsystem
CREATE VIEW
us_katalog( name, laenge, land )
AS SELECT name, laenge, ’US’
FROM us_produkte
UNION ALL
SELECT name, fuss(laenge), ’DE’
FROM de_produkte;
Einzigartige Typen
CREATE VIEW
de_katalog( name, laenge, land )
AS SELECT name, laenge, ’DE’
FROM de_produkte
UNION ALL
SELECT name, meter(laenge), ’US’
FROM us_produkte;
• Die Verkäufer können dann auf einfache Weise Abfragen auf den länderspezifischen Katalogen durchführen,
• und sehen dabei alle Artikel in der gewohnten Weise.
SELECT *
FROM us_katalog
WHERE laenge < fuss(5);
SELECT *
FROM de_katalog
WHERE laenge < meter(3);
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
139
4. Objektrelationales Typsystem
Kollektionstypen
Kollektionstypen
• Typkonstruktoren ermöglichen die Konstruktion neuer Datentypen: konstruierte
Datentypen
• Beliebig komplexe Datentypen durch geschachtelte Anwendung von Typkonstruktoren
• Grundlage für Darstellung und Erzeugung komplexer Werte
Übliche Typkonstruktoren für Kollektionstypen:
•
•
•
•
•
ROW
ARRAY
MULTISET
SET
LIST
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
140
4. Objektrelationales Typsystem
Kollektionstypen
Oracle VArray
•
•
•
•
•
VARRAY: Feld mit Indexzugriff
Maximale Grösse, wird bei Typdefinition festgelegt
variable aktuelle Größe, erstmalige Festlegung bei Instanziierung
Zugriff durch ganzzahligen Index zwischen 1 und aktueller Feldgröße
Elemente haben alle den gleichen Basistyp, auch komplexe Typen als Basistyp
möglich
• Volle Funktionalität von Varrays nur in PL/SQL
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
141
4. Objektrelationales Typsystem
Kollektionstypen
Varray: Typdefinition und Verwendung
Allgemein:
create type typname as varray(maxlaenge ) of basistyp
Beispiel:
create type t_zeitreihe as varray(30) of number;
create table zrtable (
id number not null primary key,
zeitreihe t_zeitreihe not null
);
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
142
4. Objektrelationales Typsystem
Kollektionstypen
Varray: Konstruktoren
Erzeugung einer Instanz eines Varray-Typs: Der Typname dient gleichzeitig als Konstruktor mit beliebiger Stelligkeit.
Beispiel:
insert into zrtable
values(1, t_zeitreihe(1,2,3,4));
insert into zrtable
values(2,t_zeitreihe(1,0,-1,3,4,5,6,7));
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
143
4. Objektrelationales Typsystem
Kollektionstypen
Varrays und Nullwerte
Varray
t zeitreihe(17,24,32)
t zeitreihe(17,null,32)
t zeitreihe(17,null)
t zeitreihe(17)
t zeitreihe(null)
t zeitreihe()
null
Kardinalität
3
3
2
1
1
0
null
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
144
4. Objektrelationales Typsystem
Kollektionstypen
Selektion und Belegung bei Varrays
• Zugriff auf komplette Kollektion wie üblich über Attributname
select id, zeitreihe
from zrtable
• Selektion und Belegung einzelner Arrayelemente nur mit Hilfe von PL/SQLAnweisungen
• Übliche Vorgehensweise: Definition spezieller UDFs bzw. spezieller Methoden
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
145
4. Objektrelationales Typsystem
Kollektionstypen
Wichtige PL/SQL-Funktionen für Varrays
• (i) liefert das i-te Element
• COUNT ermittelt die Kardinalität
• EXTEND bzw. EXTEND(n) vergrößert das Array um 1 bzw. n Elemente, vorbelegt mit
null
• LIMIT liefert die maximal zulässige Größe für ein Array
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
146
4. Objektrelationales Typsystem
Kollektionstypen
Nutzung der Tabellenfunktion für Varray
• Für ein Varray va liefert table(va) eine einspaltige Relation mit den Inhalten des
Varrays als Zeilen.
• Dies kann man z.B. für die ad-hoc Bestimmung der Länge eines Varrays in einer
Abfrage nutzen.
Beispiele:
-- Ausgabe aller Varray-IDs mit zugehoeriger Arraylaenge
select id, (select count(*) from table(zeitreihe)) as anz
from zrtable;
-- Selektion der Varrays mit einer Laenge > 5
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
147
4. Objektrelationales Typsystem
Kollektionstypen
select id
from zrtable
where (select count(*) from table(zeitreihe)) > 5;
-- Alle Elemente aller Varrays
select zr.id, t.*
from zrtable zr, table(zr.zeitreihe) t;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
148
4. Objektrelationales Typsystem
Kollektionstypen
Nutzung von UDFs
• Definition von User Defined Functions mit spezifischer Semantik
• Nutzung der UDFs innerhalb von Anfragen oder anderen UDFs
☞ Beispiele
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
149