Quiq::Sql - Klasse zur Generierung von SQL
Quiq::Hash
Das Programm
use Quiq::Sql; my $sql = Quiq::Sql->new('Oracle'); my $stmt = $sql->createTable('person', ['per_id',type=>'INTEGER',primaryKey=>1], ['per_vorname',type=>'STRING(30)'], ['per_nachname',type=>'STRING(30)',notNull=>1], ); print $stmt,"\n";
generiert das CREATE TABLE Statement
CREATE TABLE person ( per_id NUMBER PRIMARY KEY, per_vorname STRING2(30), per_nachname STRING2(30) NOT NULL )
(man beachte die Abbildung der Kolumnentypen)
Die Klasse unterstützt die Entwicklung von portablen Datenbankanwendungen, d.h. Anwendungen, die unter mehreren DBMSen lauffähig sind, indem sie Methoden zur Verfügung stellt, die zum DBMS den passenden SQL-Code erzeugen.
Folgende DBMSe werden von der Klasse unterstützt:
Oracle PostgreSQL SQLite MySQL
Name des DBMS.
$sql = $class->new($dbms); $sql = $class->new($dbms,$version);
Instantiiere SQL-Objekt und liefere eine Referenz auf dieses Objekt zurück.
$name = $sql->dbms;
$namesA | @names = $this->dbmsNames;
Liefere folgende Liste von DBMS-Namen (in dieser Reihenfolge):
Oracle PostgreSQL SQLite MySQL Access MSSQL
($oracle,$postgresql,$sqlite,$mysql,$access,$mssql) = $self->dbmsTestVector;
Liefere einen Vektor von boolschen Werten, von denen genau einer den Wert "wahr" besitzt, und zwar der, der dem DBMS entspricht, auf den das Objekt instantiiert ist.
Die Methode ist für Programmcode nützlich, der DBMS-spezifische Unterscheidungen macht. Der Code braucht dann lediglich auf den Wert einer Variable prüfen
if ($oracle) ...
statt einen umständlichen und fehleranfälligen Stringvergleich durchzuführen
if ($dbms eq 'Oracle') ...
$bool = $class->isOracle;
$bool = $class->isPostgreSQL;
$bool = $class->isSQLite;
$bool = $class->isMySQL;
$bool = $class->isAccess;
$bool = $class->isMSSQL;
($stmt1,@arr) = $this->split($stmt);
Zerlege $stmt in den Statement-Rumpf und eine Liste von Stringliteral-Werten und liefere diese beiden Bestandteile zurück.
Statement-Rumpf und Stringliteralwerte können unabhängig bearbeitet und anschließend mit sprintf() wieder zusammengefügt werden.
Zerlege Statement in Bestandteile:
$stmt1 = "SELECT 'a', 'b' FROM x WHERE x = 'c' AND y = 'd''e'"; ($stmt2,@arr) = $class->split($stmt1); => "SELECT '%s', '%s' FROM x WHERE x = '%s' AND y = '%s''%s'" ('a','b','c','d','e')
Füge Bestandteile wieder zusammen:
$stmt2 = sprintf($stmt2,@arr); => "SELECT 'a', 'b' FROM x WHERE x = 'c' AND y = 'd''e'"
$stmtResolved = $sql->resolve($stmt,@vals);
Ersetze die Platzhalter ? in SQL-Statement $stmt durch die Werte @val und liefere das resultierende Statement zurück.
Diese Methode ist für Debugging-Zwecke nützlich, wenn mit Platzhaltern gearbeitet wird, aber man das aufgelöste Statement sehen möchte.
$stmt = 'SELECT * FROM t WHERE x = ? AND y > ?'; @vals = (47,11); $stmtResolved = $sql->resolve($stmt,@vals); => "SELECT * FROM t WHERE x = '47' AND y > '11'"
$newStmt = $sql->removeSelectClause($stmt);
Entferne die Select-Klausel am Anfang von Select-Statement $stmt und liefere das resultierende Statement zurück.
Als Select-Klausel wird alles vom Beginn des Statement bis zur FROM-Klausel angesehen.
$newStmt = $sql->removeOrderByClause($stmt);
Entferne die Order By-Klausel vom Ende des Select-Statement $stmt und liefere das resultierende Statement zurück.
Als Select-Klausel wird alles von der ORDER BY-Klausel bis zum Ende des Statment angesehen.
$name = $sql->checkName($name); $sql->checkName(\$name);
Prüfe und manipuliere Bezeichner $name, so dass er den Konventionen des DBMS entspricht.
Oracle
Ist der Bezeichner länger als 30 Zeichen, kürze ihn auf 29 Zeichen und ersetze das 30. Zeichen durch #.
$script = $class->stmtListToScript(@stmt)
Erzeuge aus einer Liste von SQL-Statements ein einzelnes Skript, das von einem Client-Programm wie SQL*Plus, psql bzw. mysql ausgeführt werden kann.
Die SQL-Statements bzw. SQL-Kommentare in @stmt haben am Ende weder Newline noch Semikolon. Diese Methode fügt sie hinzu und konkateniert alle Statements zu einer Zeichenkette.
Folgende Manipulationen werden vorgenommen:
SQL-Statements erhalten am Ende ein Semikolon und ein Newline.
Kommentare erhalten am Ende ein Newline und werden von den umgebenden SQL-Statements abgesetzt, indem vor und nach ihnen eine Leerzeile eingefügt wird.
Diverse Details werden unterschieden (siehe EXAMPLES).
So verhält es sich im Detail:
$script = Quiq::Sql->stmtListToScript( '-- TEXT1', 'STMT1', "STMT2\n...', "STMT3 (\n....\n)", '-- TEXT2', 'STMT4', '-- eof', );
wird zu:
-- TEXT1 Kommentar am Anfang => danach "\n\n" STMT1; einzeiliges Statement => danach ";\n\n") STMT2 mehrzeilges Statement => danach "\n;\n") ... ; STMT3 ( mehrzeiles Statement mit ) => danach ";\n") ... ); -- TEXT2 innerer Kommentar => davor "\n", danach "\n\n" STMT4; (wie einzeiliges Statement oben) -- eof Kommentar am Ende, nach einzeiligem Statement => davor nichts, danach "\n"
@commands | $commandA = $sql->commands;
Methoden für die portable Spezifikation von Kolumnen-Datentypen.
$dbmsType = $sql->dataType($portableType); ($dbmsType,$args) = $sql->dataType($portableType);
Wandele den portablen Datentyp $portableType in den entsprechenden DBMS-spezifischen Typ und liefere diesen zurück. Im Skalarkontext liefere den Typbezeichner einschließlich etwaiger Argumente, im Listkontext liefere Typ und Argumente getrennt.
Typ-Abbildung
Portabel Oracle PostgreSQL SQLite MySQL ---------- ---------- ---------- --------- ---------- STRING VARCHAR2 VARCHAR TEXT VARCHAR TEXT CLOB TEXT TEXT LONGTEXT INTEGER NUMBER NUMERIC INTEGER (TINY|SMALL|MEDIUM|BIG)INT REAL NUMBER NUMERIC REAL DECIMAL DATETIME TIMESTAMP TIMESTAMP TIMESTAMP TIMESTAMP BLOB BLOB BYTEA BLOB LONGBLOB
VARCHAR2 kann bei Oracle max 4000 Zeichen lang sein
Einige Konvertierungen im Falle von Oracle:
$type = $sql->dataType('STRING'); # => 'VARCHAR2' $type = $sql->dataType('STRING(20)'); # => 'VARCHAR2(20)' ($type,$args) = $sql->dataType('STRING'); # => ('VARCHAR2','') ($type,$args) = $sql->dataType('STRING(20)'); # => ('VARCHAR2','(20)') ($type,$args) = $sql->dataType('DATETIME'); # => ('TIMESTAMP','(0)')
$colDef = $sql->columnDef(@colDef); $colDef = $sql->columnDef($portableType,@colDef);
Generiere aus der portablen Kolumnen-Spezifikation @colDef eine DBMS-spezifische Kolumnen-Definition, die als Zeichenkette nach dem Kolumnennamen in ein CREATE TABLE oder ALTER TABLE Statement eingesetzt werden kann, und liefere diese zurück.
Die Methode wird von den Methoden createTable() und addColumn() genutzt.
Die Kolumnen-Spezifikation @colDef besteht aus einer nicht-leeren Aufzählung von folgenden Schlüssel/Wert-Paaren:
Defaultwert der Kolumne.
Kolumne ist kein Pflichtfeld. Diese explizite Setzung wird bei MySQL gebraucht, wenn ein TIMESTAMP-Feld nicht '0000-00-00 00:00:00' als Defaultwert erhalten soll.
Kolumne ist Pflichtfeld.
Das DBMS erzeugt beim Einfügen eines Datensatzes einen eindeutigen Wert (SQLite und MySQL).
Kolumne ist Primärschlüsselkolumne.
Portabler Kolumnentyp.
Kolumnentyp für Oracle.
Kolumnentyp für PostgreSQL.
Kolumnentyp für SQLite.
Kolumnentyp für MySQL.
Der Kolumnentyp ist für eine Kolumnenspezifikation zwingend. Er wird als portabler Typ (type=>$type) oder als DBMS-spezifischer Typ (<dbms>Type=>$type) angegeben. Ist beides angegeben, hat der DBMS-spezifische Typ Vorrang.
Das Attribut autoIncrement ist nicht portabel, es ist
Portabler Typ wird verwendet, wenn nichts anderes für das DBMS angegeben ist:
$sql = Quiq::Sql->new('Oracle'); $type = $sql->columnDef( type => 'STRING(20)', ); ==> 'VARCHAR2(20)'
DBMS-Typ wird verwendet, wenn angegeben:
$sql = Quiq::Sql->new('Oracle'); $type = $sql->columnDef( type => 'INTEGER(5)', oracleType => 'NUMBER(5)', ); ==> 'NUMBER(5)'
$stmt = $sql->comment($text);
Setze an den Anfang jeder Zeile in $text die Zeichenfolge '-- ' und liefert das Resultat zurück.
Whitespace am Ende wird entfernt, d.h. der SQL-Kommentar endet wie die SQL-Statements per Default nicht mit einem Newline.
Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna
wird zu
-- Lorem ipsum dolor sit amet, consetetur sadipscing -- elitr, sed diam nonumy eirmod tempor invidunt ut -- labore et dolore magna
@stmt = $class->setDateFormat; @stmt = $class->setDateFormat($format);
Setze als Default-Datumsformat $format. Ist $format nicht angegeben, setzte iso-Format.
Folgende Datumsformate sind definiert:
YYYY-MM-DD HH:MM:SS
(iso) ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF'
PostgreSQL
(iso) SET datestyle TO iso, ymd
SQLite
unbekannt
MySQL
@stmt = $class->setNumberFormat; @stmt = $class->setNumberFormat($format);
Setze als Default-Zahlenformat $format. Ist $format nicht angegeben, setzte angloamerikanisches Format.
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'
$class->setSchema($schema);
ALTER SESSION SET CURRENT_SCHEMA = <schema>
SET search_path TO <schema>
Anstelle eines einzelnen Schema können mehrere Schemata, mit Komma getrennt, aufgezählt werden.
Die Setzung sollte sofort mit COMMIT bestätigt werden, da sie im Falle eines ROLLBACK sonst verfällt.
<leer>
SQLite hat das Konzept mehrerer Schemata, von denen eins das Default-Schema ist, nicht.
Bei einer SQLite-Datenbank gibt es per Default keinen Schema-Präfix, dieser wird erst durch ATTACH einer Datenbank eingeführt.
Ein Tabellenname ohne Schema wird immer über allen Attachten Datenbanken aufgelöst. Die zuerst hinzugefügte Tabelle ist der Dafault.
USE <schema>
$stmt = $class->setSearchPath(@schemas);
<not implemented>
SET search_path TO SCHEMA, ...
$stmt = $class->setEncoding($charset);
Werte für $charset:
iso-8859-1 utf-8
SET client_encoding TO <charset>
$stmt = $class->lockTable($table);
LOCK TABLE <table> IN EXCLUSIVE MODE NOWAIT
nicht implementiert
$stmt = $class->createUser($name,$password,@opt);
Name des Default-Tablespace
Name des Temporary-Tablespace
$stmt = $class->createSchema($name);
$stmt = $class->dropSchema($name);
($schema,$table) = $class->splitTableName($name); ($schema,$table) = $class->splitTableName($name,$sloppy);
splitTablename()
Zerlege den Tabellennamen $name in die Komponenten $schema und $table. Besitzt der Tabellennamen keinen Schema-Präfix, wird eine Exception geworfen. Dies geschieht nicht, wenn der Parameter $sloppy gesetzt und wahr ist. In dem Fall wird keine Exception geworfen, sondern als Schemaname undef geliefert.
undef
$stmt = $sql->createTable($table, [$colName,@colDef], ... @opt, );
Name des Tablespace, in dem die Tabelle erzeugt wird (Oracle und PostgreSQL).
Tabellentyp bei MySQL: 'InnoDb', 'MyISAM'.
Generiere ein CREATE TABLE Statement und liefere dieses zurück.
Für jede Kolumne wird ihr Name $colName und ihr Typ.
Der Kolumnentyp wird als portabler Typ (type=>$type) oder als DBMS-spezifischer Typ (<dbms>Type=>$type) angegeben. Ist beides angegeben, hat der DBMS-spezifische Typ Priorität. Für die portablen Typen siehe Methode columnType().
Alle weiteren Angaben in @colOpts sind optional.
Folgende Kolumnen-Optionen sind definiert:
Das DBMS erzeugt beim Einfügen eines Datensatzes einen eindeutigen Wert (nicht Oracle und PostgreSQL, diese haben das Konzept der Sequenz).
MySQL-Tabellen werden per Default als InnoDB-Tabellen erzeugt und erhalten als Zusatz die Angabe "TYPE = InnoDB". Der Tabellentyp kann mit der Option -tableType abweichend gesetzt werden.
Die Typ-Attribute type und <dbms>Type werden von columnTypeSpec() in den DBMS-Typ umgewandelt.
$stmt = $sql->dropTable($table);
$stmt = $sql->analyzeTable($table);
$table = $sql->legalizeTablename($table);
Legalisiere Tabellennamen durch Quotierung, wenn dieser Sonderzeichen enthält. Dies geschieht bei MySQL durch Backticks, z.B. bei Tabellen, deren Name einen Bindestrich enthält:
Meine-Tabelle -> `Meine-Tabelle` Mein-Schema.Meine-Tabelle -> `Meine-Schema`.`Meine-Tabelle`
Für die anderen DBMSe ist das Feature aktuell nicht implementiert, d.h. es wird immer der unveränderte Tabellenname zurückgegeben.
$stmt = $sql->addColumn($table,$column,@colDef);
Erzeuge SQL-Statement, das der Tabelle $table die Kolumne $column mit der Spezifikation @colDef hinzufügt. Die portable Kolumnen-Spezifikation @colDef wird von Methode columnDef() in die DBMS-spezifische Zeichenkette gewandelt.
PostgreSQL Syntax
ALTER TABLE table ADD COLUMN column type ...
Oracle Syntax
ALTER TABLE table ADD (column type ...)
SQLite Syntax
MySQL Syntax
Die Punkte stehen für zusätzliche optionale Kolumnen-Angaben, wie "DEFAULT expr", "NOT NULL", "PRIMARY KEY" usw.
$stmt = $sql->dropColumn($table,$column);
Erzeuge SQL-Statement, das aus der Tabelle $table die Kolumne $column entfernt.
Oracle, PostgreSQL, MySQL Syntax
ALTER TABLE table DROP COLUMN column
Eine Kolumne kann nicht entfernt werden (geprüft 3.6.13)
$stmt = $sql->modifyColumn($table,$column,$property=>$value);
Erzeuge SQL-Statement, das in Tabelle $table die Kolumne $column modifiziert. Verändert wird die Eigenschaft $property auf Wert $value.
NULL
ALTER TABLE t ALTER COLUMN c DROP NOT NULL
ALTER TABLE t MODIFY c NULL
NOT NULL scheint nicht ohne Kenntnis des Kolumnentyps manipuliert werden zu können (5.1.41).
Eine Kolumne kann nicht modifiziert werden (geprüft 3.6.22)
NOT NULL
ALTER TABLE t ALTER COLUMN c SET NOT NULL
ALTER TABLE t MODIFY COLUMN c NULL
TYPE
ALTER TABLE <t> MODIFY COLUMN <c> <type>
$stmt = $sql->renameColumn($table,$oldName,$newName)
Erzeuge SQL-Statement, das in Tabelle $table die Kolumne $oldName in $newName umbenennt.
Syntax
ALTER TABLE t RENAME COLUMN c1 TO c2
$stmt = $sql->addPrimaryKeyConstraint($tableName,\@colNames,@opt);
Name des Constraint.
Constraint-Verletzende Datensätze werden in Tabelle $tableName protokollliert (nur Oracle).
Name des Tablespace, in dem der Index erzeugt wird (Oracle und PostgreSQL).
ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <CONSTRAINT_NAME> PRIMARY KEY (<TABLE_COLUMNS>) USING INDEX TABLESPACE <TABLESPACE_NAME> EXCEPTIONS INTO <EXCEPTION_TABLE_NAME>
ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <CONSTRAINT_NAME> PRIMARY KEY (<TABLE_COLUMNS>) USING INDEX TABLESPACE <TABLESPACE_NAME>
$stmt = $sql->addForeignKeyConstraint($tableName,\@tableCols, $refTableName,@opt);
Constraint-Fehler wird verzögert gemeldet.
Constraint wird erzeugt, ist aber abgeschaltet.
Legt fest, was bei Löschung des Parent-Datensatzes passieren soll.
Liste der Kolumnen in der referenzierten Tabelle. Bei MySQL müssen die referenzierten Kolumnen aufgezählt werden, auch wenn ein Primary Key auf der referenzierten Tabelle definiert ist.
ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <CONSTRAINT_NAME> FOREIGN KEY (<TABLE_COLUMNS>) REFERENCES <REF_TABLE_NAME> ON DELETE <ACTION> DEFERRABLE INITIALLY DEFERRED EXCEPTIONS INTO <EXCEPTION_TABLE_NAME> DISABLE
ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <CONSTRAINT_NAME> FOREIGN KEY (<TABLE_COLUMNS>) REFERENCES <REF_TABLE_NAME> ON DELETE <ACTION> DEFERRABLE INITIALLY DEFERRED
ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <CONSTRAINT_NAME> FOREIGN KEY (<TABLE_COLUMNS>) REFERENCES <REF_TABLE_NAME> (REF_TABLE_COLUMNS) ON DELETE <ACTION>
$stmt = $sql->addNotNullConstraint($tableName,$colName,@opt);
Name des Constraint (nicht PostgreSQL).
ALTER TABLE <TABLE_NAME> MODIFY ( <COLUMN NAME> CONSTRAINT <CONSTRAINT_NAME> NOT NULL EXCEPTIONS INTO <EXCEPTION_TABLE_NAME> )
ALTER TABLE <TABLE_NAME> ALTER COLUMN <COLUMN_NAME> SET NOT NULL
$stmt = $sql->addCheckConstraint($tableName,$clause,@opt);
ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <CONSTRAINT_NAME> CHECK (<CHECK_CLAUSE>) EXCEPTIONS INTO <EXCEPTION_TABLE_NAME>
ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <CONSTRAINT_NAME> CHECK (<CHECK_CLAUSE>)
$stmt = $sql->addUniqueConstraint($tableName,\@colNames,@opt);
Liefere ein SQL-Statement zur Erzeugung eines UNIQUE-Constraint auf Tabelle $tableName über den Kolumnen @colNames und liefere dieses zurück.
ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <CONSTRAINT_NAME> UNIQUE (<TABLE_COLUMNS>) USING INDEX TABLESPACE <TABLESPACE_NAME> EXCEPTIONS INTO <EXCEPTION_TABLE_NAME>
ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <CONSTRAINT_NAME> UNIQUE (<TABLE_COLUMNS>) USING INDEX TABLESPACE <TABLESPACE_NAME>
$indexName = $sql->indexName($table,\@colNames);
$stmt = $sql->createIndex($tableName,\@colNames,@opt);
Name des Index.
Statement für Unique Index.
Generiere ein CREATE INDEX Statement und liefere dieses zurück.
CREATE [UNIQUE] INDEX <INDEX_NAME> ON <TABLE_NAME> (<TABLE_COLUMNS>) TABLESPACE <TABLESPACE_NAME>
CREATE [UNIQUE] INDEX <INDEX_NAME> ON <TABLE_NAME> (<TABLE_COLUMNS>)
$stmt = $sql->dropIndex($tableName,\@colNames);
Generiere ein DROP INDEX Statement und liefere dieses zurück.
DROP INDEX <INDEX_NAME>
@stmt = $sql->createSequence($name,@opt);
Lasse die Sequenz mit Startwert $n beginnen.
Generiere Statements zur Erzeugung von Sequenz $name und liefere diese zurück.
Unter Oracle und PostgreSQL, die das Konzept der Sequenz haben, wird ein CREATE SEQUENCE Statement generiert.
Unter MySQL und SQLite, die das Konzept der Sequenz nicht haben, wird eine Tabelle (CREATE TABLE) mit Autoinkrement-Kolumne zur Simulation einer Sequenz erzeugt. Ist die Option -startWith angegeben, wird zusätzlich ein INSERT-Statement generiert.
$stmt = $sql->dropSequence($name);
Generiere SQL-Statement zum Löschen von Sequenz $name und liefere dieses zurück.
Unter Oracle und PostgreSQL, die das Konzept der Sequenz haben, wird ein DROP SEQUENCE Statement generiert.
Unter MySQL und SQLite, die das Konzept der Sequenz nicht haben, wird ein DROP TABLE Statement generiert.
@stmt = $sql->setSequence($name,$n);
Generiere SQL-Statements zum Setzen von Sequenz $name auf Wert $n.
Unter Oracle wird die Sequenz gedroppt und neu erzeugt.
Unter PostgreSQL wird der Wert mit ALTER SEQUENCE gesetzt.
Unter MySQL und SQLite, die das Konzept der Sequenz nicht haben, wird die Sequenz-Tabelle geleert und der Wert als neuer Datensatz hinzugefügt. Einschränkung: Der Sequenzwert kann hochgesetzt, aber nicht verringert werden!
$stmt = $sql->createFunction($name,$body,@opt);
Generiere "OR REPLACE" Klausel.
Generiere "RETURNS $type" Klausel.
CREATE OR REPLACE FUNCTION <name>() RETURNS <returns> AS $SQL$ <body> $SQL$ LANGUAGE plpgsql
<name> kann Schema enthalten
$stmt = $sql->dropFunction($name);
DROP FUNCTION <name>() CASCADE
$stmt = $sql->createTrigger($table,$name,$when,$event,$level, $body,@opt); $stmt = $sql->createTrigger($table,$name,$when,$event,$level, -execute => $proc,@opt);
Generiere "OR REPLACE" Klausel (Oracle).
Generiere "EXECUTE PROCEDURE $proc()" Klausel.
$stmt = $sql->createTrigger( '<table>', '<name>', 'before', 'insert|update', 'row', -replace => 1,' <body> ' ); CREATE OR REPLACE TRIGGER <name> BEFORE INSERT OR UPDATE ON <table> FOR EACH ROW <body>
Oracle-Trigger können eine Prozedur können einen Trigger-Body definieren.
$stmt = $sql->createTrigger( '<table>', '<name>', 'before', 'insert|update', 'row', -execute => '<proc>', ); CREATE TRIGGER <name> BEFORE INSERT OR UPDATE ON <table> FOR EACH ROW EXECUTE PROCEDURE <proc>()
Trigger können eine Prozedur aufrufen (-execute=>$proc) aber keinen Trigger-Body definieren.
Keine Klausel "OR REPLACE" bei Triggern (-replace=>1 wird ignoriert)
$stmt = $sql->dropTrigger($name);
DROP TRIGGER <name>
$stmt = $sql->enableTrigger($table,$tigger);
ALTER TABLE <table> ENABLE TRIGGER <trigger>
$stmt = $sql->disableTrigger($table,$tigger);
ALTER TABLE <table> DISABLE TRIGGER <trigger>
$stmt = $sql->createView($viewName,$selectStmt);
Generiere ein CREATE VIEW Statement und liefere dieses zurück.
$stmt = $sql->dropView($viewName);
Generiere ein DROP VIEW Statement und liefere dieses zurück.
$stmt = $sql->grant($objType,$objName,$privs,$roles);
Generiere ein GRANT-Statement und liefere dieses zurück.
PostgreSQL GRANT auf Tabelle
$stmt = $sql->grant('TABLE','tab1','ALL','PUBLIC');
generiert
GRANT ALL ON TABLE tab1 TO PUBLIC
$stmt = $sql->grantUser($userName,$privs);
Oracle GRANT für Benutzer
$stmt = $sql->grantUser('user1','connect, resource, dba');
GRANT connect, resource, dba TO user1
$stmt = $sql->begin;
$stmt = $sql->commit;
$stmt = $sql->rollback;
$stmt = $sql->select($stmt,@opt); $stmt = $sql->select($table,@opt); $stmt = $sql->select(@opt);
Ersetze im SELECT-Statement den Platzhalter "__$name__" durch $value. Mehrere Name/Wert-Kombinationen können angegeben werden.
Setze Kommentar mit dem ein- oder mehrzeiligen Text $text an den Anfang des Statement.
Generiere eine SELECT-Klausel aus den Ausdrücken @selectExpr. Die Ausdrücke werden mit Komma separiert. Ist kein Select-Ausdrück spezifiziert, wird '*' angenommen.
Platzhalter: %SELECT%
Generiere "SELECT DISTINCT" statement.
Schlüsselwort "DISTINCT" wird in %SELECT%-Platzhalter mit eingsetzt.
Setze im Statement hinter das Schlüsselwort SELECT einen Hint, d.h. einen Kommentar in der Form /*+ ... */. (nur Oracle)
hint wird in %SELECT%-Platzhalter mit eingsetzt.
Generiere eine FROM-Klausel aus den Ausdrücken @fromExpr. Die Ausdrücke werden mit Komma separiert. Die FROM-Klausel ist bei Oracle eine Pflichtangabe.
Platzhalter: %FROM%
-from ist die Defaultoption, d.h. ist als erster Parameter keine Option angegeben, werden die folgenden Parameter als Tabellennamen interpretiert.
Generiere eine WHERE-Klausel aus den Ausdrücken @whereExpr. Die Ausdrücke werden mit 'AND' separiert.
Platzhalter: %WHERE%
Generiere eine GROUP BY-Klausel aus den Ausdrücken @groupExpr. Die Ausdrücke werden mit Komma separiert.
Platzhalter: %GROUPBY%
Generiere eine HAVING-Klausel aus den Ausdrücken @havingExpr. Die Ausdrücke werden mit Komma separiert.
Platzhalter: %HAVING%
Generiere eine ORDER BY-Klausel aus den Ausdrücken @orderExpr. Die Ausdrücke werden mit Komma separiert.
Platzhalter: %ORDERBY%
Generiere eine LIMIT-Klausel.
Platzhalter: %LIMIT%
Generiere eine OFFSET-Klausel.
Platzhalter: %OFFSET%
Liefere $stmt als Statement. Enthält $stmt Platzhalter, werden diese durch die entsprechenden Komponenten ersetzt (noch nicht implementiert).
Konstruiere ein SELECT-Statement aus den Parametern und liefere dieses zurück.
Ist das erste Argument keine Option und enthält es Whitespace, wird es als SQL-Statement interpretiert. Enthält es kein Whitespace, wird es als Tabellenname interpretiert.
Besonderheiten
Oracle: FROM-Klausel
Bei Oracle ist die FROM-Klausel eine Pflichtangabe, fehlt sie, wird "FROM dual" generiert.
Oracle: LIMIT und OFFSET
Oracle unterstützt weder LIMIT noch OFFSET.
Im Falle von Oracle wird keine LIMIT-Klausel generiert, sondern die WHERE-Klausel um "ROWNUM <= $n" erweitert.
Ist im Falle von Oracle OFFSET angegeben, wird eine Exception ausgelöst.
FROM-Aliase
Bei PostgreSQL ist ein FROM-Alias zwingend erforderlich, wenn die FROM-Klausel ein Ausdruck ist statt ein Tabellenname, z.B.
... FROM (<SELECT_STMT>) AS x ...
Bei Oracle ist ein Alias in dem Fall nicht erforderlich, kann aber angegeben werden. Ein FROM-Alias wird bei Oracle aber Iltnicht> mit "AS" eingeleitet. Das "AS" muss weggelassen werden.
SELECT ohne Option mit einem Argument
$stmt = $sql->select('x'); => SELECT * FROM x
SELECT ohne Option mit mehreren Argumenten
$stmt = $sql->select('x',vorname=>'Elli',nachname=>'Pirelli'); => SELECT * FROM x WHERE vorname = 'Elli' AND nachname = 'Pirelli'
SELECT mit Statement-Platzhaltern
$stmt = $sql->select(" SELECT * FROM x WHERE vorname = '__VORNAME__' AND nachname = '__NACHNAME__' ", -args => VORNAME => 'Elli', NACHNAME => 'Pirelli' ); => SELECT * FROM x WHERE vorname = 'Elli' AND nachname = 'Pirelli'
SELECT mit Statement-Muster
my $select = <<'__SQL__'; SELECT %SELECT% FROM station sta LEFT JOIN parameter par ON par_station_id = sta_id __SQL__ $stmt = $sql->select( -stmt => $select, -select => qw/sta_id sta_name par_id par_name/, -orderBy => qw/sta_name par_name/, ); => SELECT sta_id, sta_name, par_id, par_name FROM station sta LEFT JOIN parameter par ON par_station_id = sta_id ORDER BY sta_name, par_name
$stmt = $sql->insert($table,$row); $stmt = $sql->insert($table,%keyVal); $stmt = $sql->insert($table,@keyVal); $stmt = $sql->insert($table,\@keys,\@vals);
Generiere ein INSERT-Statement für Tabelle $table mit den Kolumnen und Werten %keyVal bzw. @keyVal bzw. @keys,@vals und liefere dieses zurück.
Schlüssel/Wert-Paare ohne Wert (Leerstring, undef) werden ausgelassen. Damit ist sichergestellt, dass der Defaultwert der Kolumne verwendet wird, wenn einer auf der Datenbank deklariert ist.
Ist der Kolumnenwert eine String-Referenz, wird der Wert ohne Anführungsstriche in das Statement eingesetzt. Auf diese Weise können per SQL berechnete Werte (Expressions) eingesetzt werden.
Ist die Liste der Schlüssel/Wert-Paare leer oder sind alle Werte leer, wird ein Null-Statement (Leerstring) geliefert.
Normales INSERT, Schlüssel/Wert-Paare
$stmt = $sql->insert('person', per_id => 10, per_vorname => 'Hanno', per_nachname => 'Seitz', per_geburtstag => undef, ); => INSERT INTO person ( per_id, per_vorname, per_nachname, ) VALUES ( '10', 'Hanno', 'Seitz', )
Normales Insert, Schlüssel und Werte als getrennte Listen
@keys = qw/per_id per_vorname per_nachname per_geburtstag/; @vals = (10,'Hanno','Seitz',undef); $stmt = $sql->insert('person',\@keys,\@vals); => INSERT INTO person ( per_id, per_vorname, per_nachname, ) VALUES ( '10', 'Hanno', 'Seitz', )
INSERT mit berechnetem Kolumnenwert
$stmt = $sql->insert('objekt', obj_id => 4711, obj_letzteaenderung => \'SYSDATE', ); => INSERT INTO objekt ( obj_id, obj_letzteaenderung ) VALUES ( '4711', SYSDATE )
Null-Statements
$stmt = $sql->insert('person'); => '' $stmt = $sql->insert('person', per_id => '', per_vorname => '', per_nachname => '', per_geburtstag => '', ); => ''
INSERT mit Platzhaltern
$stmt = $sql->insert('person', per_id => \'?', per_vorname => \'?', per_nachname => \'?', per_geburtstag => \'?', ); INSERT INTO person ( per_id, per_vorname, per_nachname, per_geburtstag ) VALUES ( ?, ?, ?, ? )
$stmt = $sql->insertMulti($table,\@keys,[ [@vals1], [@vals2], ... ] );
Generiere ein INSERT-Statement für Tabelle $table mit den Kolumnen @keys und den Datensätzen @records. @records ist eine Liste von Arrays mit gleich vielen Elementen wie @keys.
$stmt = $sql->insertMulti('person', [qw/per_id per_vorname per_nachname per_geburtstag/],[ [qw/1 Linus Seitz 2002-11-11/], [qw/2 Hanno Seitz 2000-04-07/], [qw/3 Emily Philippi 1997-05-05/], ] ); => INSERT INTO person (per_id, per_vorname, per_nachname, per_geburtstag) VALUES ('1', 'Linus', 'Seitz', '2002-11-11'), ('2', 'Hanno', 'Seitz', '2000-04-07') ('3', 'Emily', 'Philippi', '1997-05-05')
$stmt = $sql->update($table,@keyVal,-where,@where);
$stmt = $sql->update('person', per_geburtstag => '7.4.2000', -where,per_id => 4711, );
$stmt = $sql->delete($table,@opt,@where);
Füge Hint (Oracle) in Statement ein.
!= < <= = > >= (NOT) BETWEEN (NOT) IN (NOT) LIKE
Implement. ------------ AVG COUNT MAX MIN SUM Oracle PostgreSQL SQLite MySQL ------------ ------------ ------------ ------------ GROUP_CONCAT TOTAL
Implement. Oracle PostgreSQL SQLite MySQL ------------- ------------ ------------ ------------ ------------ ABS COALESCE GLOB IFNULL HEX LENGTH LOWER LOWER LOWER LOWER LTRIM MAX MIN NULLIF QUOTE RANDOM RANDOMBLOB REPLACE ROUND RTRIM SUBSTR SUBSTR SUBSTR SUBSTR TRIM TYPEOF UPPER UPPER UPPER UPPER CAT || ||
$sqlExpr = $sql->opFunc($op,@args);
Generiere Funktionsaufruf "<OP>(<EXPR1>, <EXPR2>, ...)" und liefere diesen zurück.
Diese Methode wird zur Generierung von portablen Funktionsausdrücken wie UPPER, LOWER, MIN, MAX, SUBSTR, etc. benutzt.
$sqlExpr = $sql->opRel($op,$arg);
Generiere Ausdruck "<OP> <EXPR>" und liefere diesen zurück.
$sqlExpr = $sql->opAS($op,$arg,$name);
Generiere Ausdruck "<EXPR> AS <NAME>" und liefere diesen zurück.
$sqlExpr = $sql->opBETWEEN($op,$arg1,$arg2);
Generiere Ausdruck "BETWEEN <EXPR1> AND <EXPR2>" und liefere diesen zurück.
$sqlExpr = $sql->opCASEXPR($op,@args);
Für diff() implementiert, funktioniert aber nicht.
$sqlExpr = $sql->opCAST($op,$dataType,$arg);
$sqlExpr = $sql->opIN($op,@arr);
Generiere Ausdruck "IN (VAL1, VAL2, ...)" und liefere diesen zurück.
$sqlExpr = $sql->keyExpr($expr);
Generiere einen SQL Bezeichner-Ausdruck zum portablen Ausdruck $expr und liefere den generierten Ausdruck zurück.
Ein Bezeichner-Ausdruck ist ein Ausdruck, wie er in der Select-Liste und auf der linken Seite von WHERE-Klausel-Bedingungen vorkommt. Er zeichnet sich dadurch aus, dass seine elementaren Komponenten ungequotete Bezeichner sind (und keine Werte).
Einfacher Bezeichner
$sql->keyExpr('per_id'); ==> "per_id"
Ausdruck als Zeichenkette (nicht empfohlen, da nicht portabel)
$sql->keyExpr('UPPER(per_nachname)'); ==> "UPPER(per_nachname)"
Portabler Ausdruck
$sql->keyExpr(['UPPER',['per_nachname']]); ==> "UPPER(per_nachname)"
Portabler Ausdruck mit Stringreferenz (wird String-Literal)
$sql->keyExpr(['UPPER',[\'Ratlos']]); ==> "UPPER('Ratlos')"
$sqlExpr = $sql->valExpr($expr);
Generiere einen SQL Wert-Ausdruck zum portablen Ausdruck $expr und liefere den generierten Ausdruck zurück.
Ein Wert-Ausdruck ist ein Ausdruck, wie er auf der rechten Seite von WHERE-Klausel-Bedingungen vorkommt. Er zeichnet sich dadurch aus, dass seine elementaren Komponenten Literale, keine Bezeichner sind.
Literal
$sql->valExpr('Kai Nelust'); ==> "'Kai Nelust'"
Ausdruck
$sql->valExpr(\'USERNAME'); ==> "USERNAME"
$sql->valExpr(['UPPER','Kai Nelust']); ==> "UPPER('Kai Nelust')"
Portabler Ausdruck mit Stringreferenz (wird Identifier-Ausdruck)
$sql->valExpr(['LOWER',\'USERNAME']); ==> "LOWER(USERNAME)"
$sqlExpr = $sql->whereExpr($expr);
Generiere die rechte Seite eines WHERE-Ausdrucks zum portablen Ausdruck $expr und liefere den generierten Ausdruck zurück.
Der Ausdruck besteht aus einem Operator gefolgt von einem Wert-Ausdruck.
$sql->whereExpr('Kai Nelust'); ==> "= 'Kai Nelust'"
$sql->whereExpr(\'USERNAME'); ==> "= USERNAME"
$sql->whereExpr(['!=',['UPPER','Kai Nelust']]); ==> "!= UPPER('Kai Nelust')"
$sql->whereExpr(['!=',['LOWER',\'USERNAME']]); ==> "!= LOWER(USERNAME)"
$str = $sql->expr($type,$op,@args);
$literal = $sql->stringLiteral($str); $literal = $sql->stringLiteral($str,$default);
Verdoppele alle in $str enthaltenen einfachen Anführungsstriche, fasse den gesamten String in einfache Anführungsstriche ein und liefere das Resultat zurück.
Ist der String leer ('' oder undef) liefere einen Leerstring (kein leeres String-Literal!). Ist $default angegeben, liefere diesen Wert.
Anmerkung: PostgreSQL erlaubt aktuell Escape-Sequenzen in String-Literalen. Wir behandeln diese nicht. Escape-Sequenzen sollten in postgresql.conf abgeschaltet werden mit der Setzung:
standard_conforming_strings = on
Eingebettete Anführungsstriche:
$sel->stringLiteral('Sie hat's'); => "'Sie hat''s'"
Leerstring, wenn kein Wert:
$sel->stringLiteral(''); => ""
'NULL', wenn kein Wert:
$sel->stringLiteral('','NULL'); => "NULL"
$selectClause = $sql->selectClause(@select);
$fromClause = $sql->fromClause(@from);
Wandele die Liste von From-Elementen, @from, in eine FROM-Klausel und liefere diese zurück.
Die Elemente werden mit Komma als Trennzeichen konkateniert und folgendermaßen behandelt:
Eine Zeichenkette wird nicht verändert.
Es wird ein FROM-Alias erzeugt. Dieser hat entweder den Aufbau "expr AS alias" oder "fromExpr alias", abhängig vom DBMS. Oracle akzeptiert "fromExpr AS alias" nicht.
$where = $sql->whereClause(@where);
Wenn +null in @where vorkommt, werden alle folgenden leeren Bedingungen nicht übergangen.
$set = $sql->setClause(@keyVal);
$str = $sql->exists(@opt,@select);
Bedingung, unter der die EXISTS-Klausel gilt. Ist $bool falsch, liefert die Methode eine leere Liste (Array-Kontext) oder einen Leerstring (Skalar-Kontext), d.h. die Klausel kann in der Verarbeitung ignoriert werden.
Liefere NOT EXISTS Klausel.
$str = $sql->notExists(@select);
$stmt = $sql->diff( $keyCol, $fromClause, [$type,$col1,$col2,$col2Expr], ... @where, @selOpts );
$tab = $db->diff( 't.id', "de_ticket t LEFT OUTER JOIN spielgemeinschaftanteil s\n". 'ON t.id = s.spielid*65536+1', ['N','t.subscription'=>'s.dauerschein'], ['N','t.product_id'=>'s.spielgemeinschaftid', \'CASE %C WHEN 9685 THEN 24 WHEN 9684 THEN 26 WHEN 9687 THEN 28 END', ], 't.product_ticket_type'=>'LOTTERY_CLUB_TICKET', -limit => 100, );
1.152
Frank Seitz, http://fseitz.de/
Copyright (C) 2019 Frank Seitz
This code is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
To install Quiq, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Quiq
CPAN shell
perl -MCPAN -e shell install Quiq
For more information on module installation, please visit the detailed CPAN module installation guide.