|
Dimedis::Sql - SQL/DBI Interface für datenbankunabhängige Applikationen |
Dimedis::Sql - SQL/DBI Interface für datenbankunabhängige Applikationen
use Dimedis::Sql;
# Konstruktor und Initialisierung my $sqlh = new Dimedis::Sql ( ... ); $sqlh->install ( ... );
# Ausführung elementarer Kommandos zur Datenein-/ausgabe my $seq_id = $sqlh->insert ( ... ); my $modified = $sqlh->update ( ... ); my $blob_sref = $sqlh->blob_read ( ... );
# Handling mehrerer Datenbanken $sqlh->use_db ( ...) my $db_prefix = $sqlh->db_prefix ( ...)
# direkte Ausführung von SQL Statements my $modified = $sqlh->do ( ... ); my $href = $sqlh->get ( ... ); my @row = $sqlh->get ( ... );
# Generierung von datenbankspezifischem SQL Code my ($from, $where) = $sqlh->outer_join ( ... ); my $cond = $sqlh->cmpi ( ... ); my $where = $sqlh->contains ( ... );
# Kompatibilitätsprüfung my $feature_href = $sqlh->get_features;
Dieses Modul erleichtert die Realisierung datenbankunabhängiger Applikationen. Die Schnittstelle gliedert sich in drei Kategorien:
Es gibt einige Voraussetzungen für erfolgreiche datenbankunabhängige Programmierung mit diesem Modul.
Besonderheiten der unterschiedlichen Datenbankplattformen und wie Dimedis::Sql damit umgeht, können der Dokumentation des entsprechenden Datenbanktreibers (Dimedis::SqlDriver::*) entnommen werden.
YYYYMMDDHH:MM:SS
Bei der Verwendung des Moduls unter Windows ist folgendes grundsätzlich zu beachten: beim Umgang mit Binärdateien unter Windows ist es erforderlich, daß sämtlicher File I/O im 'binmode' durchführt wird, d.h. die für die entsprechenden Filehandles muß die Perl Funktion binmode aufgerufen werden.
Dimedis::Sql ruft grundsätzlich für alle Filehandles binmode auf, auch wenn diese vom Benutzer übergeben wurden. Dies stellt kein Problem dar, wenn in vom Benutzer übergebene Filehandles noch nichts geschrieben bzw. gelesen wurde.
Wenn Filehandles übergeben werden, die bereits für I/O verwendet wurden, führt dies zu undefinierten Zuständen, wenn diese nicht bereits vorher mit binmode behandelt wurden. Deshalb müssen Filehandles, die vor der Übergabe an Dimedis::Sql bereits verwendet werden sollen, unbedingt sofort nach dem Öffnen mit binmode in den Binärmodus versetzt werden.
Alle Methoden erzeugen im Fehlerfall eine Exception mit der Perl croak Funktion. Die Fehlermeldung hat folgenden Aufbau:
"$method\t$message"
Dabei enthält $method den vollständigen Methodennamen und $message eine detailliertere Fehlermeldung (z.B. $DBI::errstr, wenn es sich um einen SQL Fehler handelt).
DBI bietet ein Feature an, mit dem einmal ausgeführte SQL Statements intern gecached werden. Bei einem gecachten Statement entfällt der Aufwand für das 'prepare'. Dies kann (insbesondere im Kontext persistenter Perl Umgebungen) erhebliche Performancevorteile bringen, allerdings auf Kosten des Speicherverbrauchs.
Grundsätzlich benutzt Dimedis::Sql wo möglich dieses Caching Feature. Es gibt aber Gründe, es nicht zu verwenden. Wenn es nicht möglich ist, alle Parameter eines Statements mit Parameter Binding zu übergeben, sollte das resultierende Statement nicht gecached werden. Der eingebettete Parameter würde mit gecached werden. Die Wahrscheinlichkeit aber, daß dieses Statement genau so noch einmal abgesetzt wird, ist extrem gering. Dafür wird aber viel Speicher verbraucht, weil das gecachte Statement bis zur Prozeßbeendung im Speicher verbleibt. Zudem gibt es bei den verschiedenen Datenbanken eine Begrenzung der gleichzeitig offenen Statement-Handles.
Bei einigen Methoden und beim Konstruktor gibt es deshalb einen cache Parameter, um die Verwendung des Caches zu steuern.
Der cache Parameter gibt an, das DBI Statement Caching verwendet werden soll, oder nicht. In der Regel erkennt Dimedis::Sql selbständig, ob das Statement cachebar ist oder nicht: wenn keine params zwecks Parameter Binding übergeben wurden, so wird das Statement nicht gecached, weil davon ausgegangen wird, daß entsprechende Parameter im SQL Befehlstext direkt eingebettet sind, was ein Caching des SQL Befehls sinnlos macht. Andernfalls cached Dimedis::Sql das Statement immer.
Über den cache Parameter kann der Anwender das Verhalten selbst steuern. Falls cache => 0 beim Erzeugen der Dimedis::Sql Instanz angegeben wurde, ist das Caching immer abgeschaltet, unabhängig von den oben beschriebenen Bedingungen. ACHTUNG: derzeit unterstützen nicht alle Dimedis::SqlDriver dieses Feature (sowohl das globale Abschalten des Caches, als auch das Einstellen pro Methodenaufruf). $sqlh->get_features gibt hierüber Auskunft. Wenn der cache Parameter nicht unterstützt wird, so ist nicht definiert, ob mit oder ohne Cache gearbeitet wird.
Unter Perl 5.8.0 unterstützt Dimedis::Sql auch Unicode. Beim Konstruktor muß dazu das utf8 Attribut gesetzt werden. Dimedis::Sql konvertiert damit alle Daten (außer Blobs) ggf. in das UTF8 Format, wenn die Daten nicht bereits in UTF8 vorliegen.
Alle gelesenen Daten erhalten das Perl eigene UTF8 Flag gesetzt, d.h. es wird vorausgesetzt, daß alle in der Datenbank gespeicherten Daten auch im UTF8 Format vorliegen. Solange Dimedis::Sql stets im UTF8 Modus betrieben, ist das auch gewährleistet. Eine Mischung von UTF8- und nicht-UTF8-Daten ist nicht möglich und führt zu fehlerhaft codierten Daten.
Der UTF8 Support ist datenbankabhängig (derzeit unterstützt von MySQL und Oracle). Das get_features Hash hat einen Eintrag utf8, der angibt, ob die Datenbank UTF8 unterstützt, oder nicht.
Leer-Strings werden von den Datenbanksystemen unterschiedlich behandelt. Einige konvertieren sie stets zu NULL Spalten, andere können zwischen NULL und Leer-String korrekt unterscheiden.
Zur Erfüllung eines minimalen Konsens werden alle Leerstrings von den Dimedis::Sql Methoden zu undef bzw. NULL konvertiert, so daß es grundsätzlich keine Leerstrings gibt, sondern nur NULL Spalten bzw. undef Werte (NULL wird in DBI durch undef repräsentiert).
my $sqlh = new Dimedis::Sql (
dbh => $dbh
[, debug => {0 | 1} ]
[, cache => {0 | 1} ]
[, serial_write => {0 | 1} ]
[, utf8 => {0 | 1] ]
);
Der Konstruktor erkennt anhand des übergebenen DBI Handles die Datenbankarchitektur und lädt das entsprechende Dimedis::SqlDriver Modul für diese Datenbank, welches die übrigen Methoden implementiert.
Wenn der debug Parameter gesetzt ist, werden Debugging Informationen auf STDERR geschrieben. Es gibt keine Unterscheidung in unterschiedliche Debugging Levels. Generell werden alle ausgeführten SQL Statements ausgegeben sowie zusätzliche spezifische Debugging Informationen, je nach verwendeter Funktion.
Über den cache Parameter kann das DBI Caching von prepared Statements gesteuert werden. Wenn hier 0 übergeben wird, werden Statements grundsätzlich nie gecached (auch wenn bei einigen Statements lokal explizit cache => 1 gesetzt wurde. So kann das Caching bei Problemen sehr leich an zentraler Stelle abgeschaltet werden. Default ist eingeschaltetes Caching.
Der serial_write Parameter gibt an, ob explizite Werte für serial Spalten angegeben werden dürfen. Per Default ist dies verboten.
Der utf8 Parameter schaltet das Dimedis::Sql Handle in den UTF8 Modus. Siehe das Kapitel UNICODE SUPPORT.
Parameter für eine like Suche können nicht via Parameter Binding übergeben werden (zumindest Sybase unterstützt dies nicht).
Es gibt einige Attribute des $sqlh Handles, die direkt verwendet werden können:
Oracle Informix Sybase
$sqlh->install
Diese Methode muß nur einmal bei der Installation der Applikation aufgerufen werden. Sie erstellt in der Datenbank Objekte, die von dem entsprechenden datenbankabhängigen SqlDriver benötigt werden.
Es ist möglich, daß ein SqlDriver keine Objekte in der Datenbank benötigt, dann ist seine install Methode leer. Trotzdem muß diese Methode immer bei der Installation der Applikation einmal aufgerufen werden.
my $seq_id = $sqlh->insert (
table => "table_name",
data => {
col_j => $val_j,
...
},
type => {
col_i => 'serial',
col_j => 'date',
col_k => 'clob',
col_l => 'blob',
...
}
);
Die insert Methode fügt einen Datensatz in die angegebene Tabelle ein. Der Rückgabewert ist dabei eine evtl. beim Insert generierte Primary Key ID.
Die einzelnen Werte der Spalten werden in dem data Hash übergeben. Dabei entsprechen die Schlüssel des Hashs den Spaltennamen der Tabelle, deren Namen mit dem type Parameter übergeben wird. SQL NULL Werte werden mit dem Perl Wert undef abgebildet.
Das type Hash typisiert alle Spalten, die keine String oder Number Spalten sind. Hier sind folgende Werte erlaubt:
Der serial Datentyp darf nur einmal pro Insert vorkommen.
Um eine serial Spalte mit den automatisch generierten Wert zu setzen, muß im data Hash hierfür undef übergeben werden. Wenn eine serial Spalte auf einen fixen Wert gesetzt werden soll, so muß im data Hash der entsprechende Wert übergeben werden.
Beispiel:
my $id = $sqlh->insert (
table => 'users',
data => {
id => undef,
nickname => 'foo'
},
type => {
id => 'serial'
}
);
In diesem Beispiel wird ein Datensatz in die Tabelle 'users' eingefügt, die eine serial Spalte enthält. Die Spalte 'nickname' wird im type Hash nicht erwähnt, da es sich hierbei um eine CHAR Spalte handelt.
Zusätzlich gilt folgende Einschränkung für BLOBs:
- die Verarbeitung von BLOBS ist nur möglich, wenn
eine serial Spalte mit angegeben ist
Beispiel:
Hier wird ein Blob aus einer Datei heraus eingefügt:
my $id = $sqlh->insert (
table => 'users',
data => {
id => undef,
nickname => 'foo',
photo => '/tmp/uploadfile'
},
type => {
id => 'serial',
photo => 'blob'
}
);
Hier wird dieselbe Datei eingefügt, nur diesmal wird sie vorher in den Speicher eingelesen, und dann aus dem Speicher heraus in die Datenbank eingefügt (Übergabe als Skalarreferenz):
open (FILE, '/tmp/uploadfile')
or die "can't open /tmp/uploadfile';
binmode FILE;
my $image;
{ local $/ = undef; $image = <FILE> };
close FILE;
my $id = $sqlh->insert (
table => 'users',
data => {
id => undef,
nickname => 'foo',
photo => \$image
},
type => {
id => 'serial',
photo => 'blob'
}
);
my $modified = $sqlh->update (
table => "table_name",
data => {
col_j => $val_j,
...
},
type => {
col_j => 'date',
col_k => 'clob',
col_l => 'blob',
...
},
where => "where clause"
[, params => [ $where_par_n, ... ] ]
[, cache => 1|0 ]
);
Die update Methode führt ein Update auf der angegebenen Tabelle durch. Dabei werden Tabellenname, Daten und Typinformationen wie bei der insert Methode übergeben. Zusätzlich wird mit dem where Parameter die WHERE Klausel für das Update angegeben, wobei optional mit dem params Parameter Platzhalter Variablen für die where Klausel übergeben werden können. Das Wort 'where' darf in dem where Parameter nicht enthalten sein.
Der Rückgabewert ist die Anzahl der von dem UPDATE veränderten Datensätze. Wenn nur BLOB Spalten upgedated werden, ist der Rückgabewert nicht spezifiziert und kann je nach verwendeter Datenbankarchitektur variieren.
Der cache Parameter wird im Kapitel CACHING VON SQL BEFEHLEN beschrieben.
Zusätzlich zu den Einschränkungen der insert Methode muß noch folgendes beachtet werden:
Diese Einschränkung wird u.U. in Zukunft aufgehoben.
Beispiel:
In diesem Beispiel wird eine Blob Spalte upgedated, aus einer Datei heraus. Der where Parameter selektiert genau eine Zeile über die id Spalte der Tabelle. Der Wert der Spalte wird über Parameter Binding übergeben.
$sqlh->update (
table => 'users',
data => {
photo => '/tmp/uploadfile'
},
type => {
photo => 'blob'
},
where => 'id = ?',
params => [ $id ]
);
my $blob_sref = $sqlh->blob_read (
table => "table_name",
col => "blob_column_name",
where => "where clause"
[, params => [ $par_j, ... ] ]
[, filename => "absolute_path" ]
[, filehandle => "filehandle reference" ]
);
Mit der blob_read Methode wird ein einzelner Blob (oder Clob) gelesen und als Skalarreferenz zurückgegeben. Dabei werden Tabellennamen, Spaltenname sowie die WHERE Klausel zum Selektieren der richtigen Zeile als Parameter übergeben.
Wenn der optionale Parameter filename gegeben ist, wird der Blob nicht als Skalarreferenz zurückgegeben, sondern stattdessen in die entsprechende Datei geschrieben und undef zurückgegeben.
Wenn filehandle angegeben ist, wird das Blob in diese Filehandle Referenz geschrieben und undef zurückgegeben. Die mit dem Filehandle verbundene Datei wird nicht geschlossen.
filehandle und filename dürfen nicht gleichzeitig angegeben werden.
Beispiel:
In diesem Beispiel wird ein Blob in eine Variable eingelesen:
my $blob_sref = $sqlh->blob_read (
table => "users",
col => "photo",
where => "id=?",
params => [$id],
);
Dasselbe Blob wird nun auf STDOUT ausgegeben, beispielsweise um ein GIF Bild an einen Browser auszuliefern (binmode für die Win32 Kompatibilität nicht vergessen!):
binmode STDOUT; print "Content-type: image/gif\n\n";
$sqlh->blob_read (
table => "users",
col => "photo",
where => "id=?",
params => [$id],
filehandle => \*STDOUT
);
my $modified = $sqlh->do (
sql => "SQL Statement",
[, params => [ $par_j, ... ] ]
[, cache => 0|1 ]
);
Mit der do Methode wird ein vollständiges SQL Statement ausgeführt, d.h. ohne weitere Bearbeitung an DBI durchgereicht. Optionale Platzhalter Parameter des SQL Statements werden dabei mit dem params Parameter übergeben.
Der cache Parameter wird im Kapitel CACHING VON SQL BEFEHLEN beschrieben.
Der Rückgabewert ist die Anzahl der von dem UPDATE veränderten Datensätze.
my $href =
my @row = $sqlh->get (
sql => "SQL Statement",
[, params => [ $par_j, ... ] ]
[, cache => 0|1 ]
);
Die get Methode ermöglicht das einfache Auslesen einer Datenbankzeile mittels eines vollständigen SELECT Statements, d.h. das SQL Statement wird ohne weitere Bearbeitung an DBI durchgereicht. Optionale Platzhalter Parameter werden dabei mit dem params Parameter übergeben.
Im Scalar-Kontext aufgerufen, wird eine Hashreferenz mit Spalte => Wert zurückgegeben. Im Listen-Kontext wird die Zeile als Liste zurückgegeben.
Wenn das SELECT Statement mehr als eine Zeile liefert, wird nur die erste Zeile zurückgeliefert und die restlichen verworfen. Eine Verarbeitung von Ergebnismengen kann also mit der get Methode nicht durchgeführt werden.
Der cache Parameter wird im Kapitel CACHING VON SQL BEFEHLEN beschrieben.
my ($from, $where) = $sqlh->left_outer_join (
komplexe, teilweise verschachtelte Liste,
Beschreibung siehe unten
);
Diese Methode liefert gültige Inhalte von FROM und WHERE Klauseln zurück (ohne die Schlüsselwörte 'FROM' und 'WHERE'), die für die jeweilige Datenbankplattform einen Left Outer Join realisieren. Für die WHERE Klausel wird immer eine gültige Bedingung zurückgeliefert, sie kann also gefahrlos mit ``... AND $where'' in ein SELECT Statement eingebunden werden, ohne abzufragen, ob sich der Outer Join überhaupt in der WHERE Condition auswirkt.
Es wird eine Liste von Parametern erwartet, die einem der folgenden Schemata genügen muß (es werden zwei Fälle von Joins unterschieden). Unter der Parameterzeile ist zum besseren Verständnis jeweils die Umsetzung für Informix und Oracle angedeutet.
(Es gibt noch einen weiteren Outer Join Fall, der von Dimedis::Sql aber nicht unterstützt wird, da nicht alle Datenbankplattformen diesen umsetzen können. Dabei handelt es sich um einen Simple Join, der als gesamtes gegen die linke Tabelle left outer gejoined werden soll.)
("tableA A", ["tableB B"], "A.x = B.x" )
Ifx: A, outer B Ora: A.x = B.x (+)
Dies war ein Spezialfall des folgenden, es können also beliebig viele Tabellen jeweils mit A outer gejoined werden:
("tableA A", ["tableB B"], "A.x = B.x",
["tableC C"], "A.y = C.y",
["tableD D"], "A.z = D.z", ... )
Ifx: A, outer B, outer C Ora: A.x = B.x (+) and A.y = C.y (+) and A.z = D.z (+) ...
("tableA A",
[ "tableB B", [ "tableC C" ], "B.y = C.y AND expr(c)" ],
"A.x = B.x")
Ifx: A, outer (B, outer C)
Ora: A.x = B.x (+) and B.y = C.y (+)
and expr(c (+) )
- die Angabe einer Tabelle erfolgt nach dem Schema
"Tabelle[ Alias]"
Alle Spaltenbezeichner in den Bedinungen müssen den Alias
verwenden (bzw. den Tabellennamen, wenn der Alias
weggelassen wurde).
- zu einem Left Outer Join gehören immer drei Bestandteile:
1. linke Tabelle (deren Inhalt vollständig bleibt)
2. rechte Tabelle (in der fehlende Eintrage mit NULL
gefüllt werden)
3. Join Bedingung
Die Parameterliste nimmt sie in genau dieser Reihenfolge
auf, wobei die jeweils rechte Tabelle eines Outer Joins
in eckigen Klammern steht:
LeftTable, [ OuterRightTable ], Condition
Dabei können im Fall I OuterRightTable und Condition
beliebig oft auftreten, um die outer Joins dieser Tabellen
gegen die LeftTable zu formulieren.
Im Fall II erfolgt die Verschachtelung nach demselben
Schema. Die OuterRightTable wird in diesem Fall zur
LeftTable für den inneren Outer Join.
- wenn zusätzliche Spaltenbedingungen für eine rechte
Tabelle gelten sollen, so müssen diese an die Outer
Join Bedingung angehängt werden, in der die Tabelle
auch tatsächlich die rechte Tabelle darstellt.
Im Fall II z.B. könnten sie theoretisch auch bei der
Bedingung eines inneren Joins angegeben werden, das
darf aber nicht geschehen, da die Tabelle im inneren
Join als LeftTable fungiert. Dies führt dann je nach
Datenbankplattform nicht zu dem gewünschten Resultat.
Falsch:
"A", ["B", ["C"], "B.y = C.y and B.foo=42"], "A.x = B.x"
Richtig:
"A", ["B", ["C"], "B.y = C.y"], "A.x = B.x and B.foo=42"
my $cond = $sqlh->cmpi (
col => "column_name",
val => "column value (with wildcards)",
op => 'like' | '=' | '!='
);
Die cmpi Methode gibt eine SQL Bedingung zurück, die case insensitive ist. Dabei gibt col den Namen der Spalte an und val den Wert der Spalte (evtl. mit den SQL Wildcards % und ?, wenn der Operator like verwendet wird). Der Wert muß ein einfaches String Literal sein, ohne umschließende Anführungszeichen. Andere Ausdrücke sind nicht erlaubt.
Der op Parameter gibt den Vergleichsoperator an, der verwendet werden soll.
Die cmpi Methode berücksichtigt eine mit setlocale() eingestellte Locale.
my $cond = $sqlh->contains (
col => "column name",
vals => [ "val1", ..., "valN" ],
[ logic_op => 'and' | 'or', ]
search_op => 'sub'
);
Die contains Methode generiert eine SQL Bedingung, die eine Volltextsuche realisiert. Hierbei werden entsprechende datenbankspezifischen Erweiterungen genutzt, die eine effeziente Volltextsuche ermöglichen (Oracle Context Cartridge, Informix Excalibur Text Datablade).
col gibt die Spalte an, über die gesucht werden soll. vals zeigt auf die Zeichenkette(n), nach der/denen gesucht werden soll (ohne Wildcards). Wenn mit vals mehrere Werte übergeben werden, muß auch logic_op gesetzt sein, welches bestimmt, ob die Suche mit 'and' oder 'or' verknüpft werden soll.
Mit search_op können unterschiedliche Varianten der Volltextsuche spezifiert werden. Z.Zt. kann hier nur 'sub' angegeben werden, um anzuzeigen, daß eine Teilwortsuche durchgeführt werden soll.
Wenn eine Datenbank keine Volltextsuche umsetzen kann, wird undef zurückgegeben.
$sqlh->use_db (
db => "database_name"
);
Diese Methode wechselt auf der aktuellen Datenbankconnection zu einer anderen Datenbank. Der Name der Datenbank wird mit dem db Parameter übergeben.
$sqlh->db_prefix (
db => "database_name"
);
Diese Methode liefert den Datenbanknamen zusammen mit dem datenbankspezifischen Tabellen-Delimiter zurück. Der zurückgegebene Wert kann direkt in einem SQL Statement zur vollständigen Qualifikation einer Tabelle verwendet werden, die in einer anderen Datenbank liegt.
Beispiel:
my $db_prefix = $sqlh->db_prefix ( db => 'test' );
$sqlh->do (
sqlh => 'update ${db_prefix}foo set bla=42'
);
Hier wird die Tabelle 'foo' in der Datenbank 'test' upgedated.
my $feature_href = $sqlh->get_features;
Diese Methode gibt eine Hashreferenz zurück, die folgenden Aufbau hat und beschreibt, welche Dimedis::Sql Features von der aktuell verwendeten Datenbankarchitektur unterstützt werden:
$feature_href = {
serial => 1|0,
blob_read => 1|0,
blob_write => 1|0,
left_outer_join => {
simple => 1|0,
nested => 1|0
},
cmpi => 1|0,
contains => 1|0,
use_db => 1|0,
cache_control => 1|0,
utf8 => 1|0,
};
Sollten dem $feature_href Schlüssel fehlen, so ist das gleichbedeutend mit einem Setzen auf 0, d.h. das entsprechende Feature wird nicht unterstützt.
'cache_control' meint die Möglichkeit, bei $sqlh->insert und $sqlg->update mit dem Parameter 'cache' zu steuern, ob intern mit Statement Caching gearbeitet werden soll, oder nicht.
Joern Reder, joern@dimedis.de
Copyright (c) 1999 dimedis GmbH, All Rights Reserved
perl(1), Dimedis::SqlDriver::Oracle(3pm), Dimedis::SqlDriver::Informix(3pm)
|
Dimedis::Sql - SQL/DBI Interface für datenbankunabhängige Applikationen |