"standard-konformes" SQL für DB-Abstraktion

[Andreas Korthaus]

Hi!

Entwickele gerade an einem Projekt in dem ich PHP/PEAR zur DB-Abstraktion verwenden möchte. Die Abfragen sollen zumindest unter MySQL und PostgreSQL laufen. Da habe ich erstmal eien spezielle Frage:

Ich verwende z.B. so eien Abfrage in MySQL:

SELECT
  *
FROM
  table as t1
LEFT JOIN
  table2 as t2
ON
  t1.feld = t2.feld

Wenn ich das jetzt auslese kann ich direkt auf alle Felder zugreifen, also

ich bekomme einen Assoziativen Array von der DB zurück, der direkt alle Spaltennamen beider Tabellen "index" enthält.

array("feld1" => "inhalt",
      "feld2" => "inhalt",
      "feld3" => "inhalt"
)
...

Das heißt ich merke hinterher gar nicht mehr welches Feld aus welcher Tabelle kommt.

Jetzt frage ich mich nur, wie standard-konform das ist, oder besser - funktioniert das so auch in PostgreSQL und anderen DBMS?

Kennt Ihr vielleicht noch ein paar typische "Stolpersteine" wenn man eine Anwendung mit Datenbank-Abstraktion programmieren will? Speziell MySQL und PostgreSQL betreffend? Wie sieht das aus mit Transaktionen?  COMMIT und END kennen wohl beide, aber welchen Tabellentreiber würdet Ihr für mySQL(3.23.54) empfehlen?

Viele Grüße
Andreas

[Andreas Korthaus]

Hi!

Kennt Ihr vielleicht noch ein paar typische "Stolpersteine" wenn man eine Anwendung mit Datenbank-Abstraktion programmieren will? Speziell MySQL und PostgreSQL betreffend? Wie sieht das aus mit Transaktionen?  COMMIT und END kennen wohl beide, aber welchen Tabellentreiber würdet Ihr für mySQL(3.23.54) empfehlen?

kleine Korrektur, ich meinte beide kennen BEGIN/COMMIT/ROLLBACK. In MySQL gibt es IMHO Berkeley_db-Tabellen und InnoDB-Tabellen die Transaktionen können, nur welchen der beiden Treiber sollte man verwenden?

Grüße
Andreas

[Philipp Hasenfratz]

Halihallo Andreas

Das heißt ich merke hinterher gar nicht mehr welches Feld aus welcher Tabelle kommt.

Ja...? - Das ist auch richtig so, denn jeder Query gibt dir eine ResultSet zurück,
was ebenfalls wider eine Tabelle ist. Der Query ist nur eine Abbildungsvorschrift und
es ist streng genommen nur ein "Spezialfall", wenn ein Feld auch äquivalent in der
Ergebnisrelation abgebildet wird. Eine Zuordnung zu einer früheren Bezeichnung ist
eigentlich falsch (obwohl eigentlich manchmal, wie bei dir jetzt, wünschenswert).

Jetzt frage ich mich nur, wie standard-konform das ist, oder besser - funktioniert das so auch in PostgreSQL und anderen DBMS?

Die Eigenschaft von oben (nicht auf Tabelle rückschliessbar)? - Ja, das ist so.

Kennt Ihr vielleicht noch ein paar typische "Stolpersteine" wenn man eine Anwendung mit Datenbank-Abstraktion programmieren will?

Was, oder wohl besser: Wie weit verstehst du eine Datenbank-Abstraktion? - Was möchtest
du haben und wie willst du es umsetzen?

---

Ich schreibe auch seit langem an einer... Habe das selbe Problem wie du oben
beschreibst; die Unmöglichkeit Felder der Ergebnisrelation wieder einer Tabelle zu-
zuordnen. Es geht darum, einen Query auszuführen, jedoch das ResultSet mit Read/Write
=> Schreibzugriffe auf direkt abgebildete Attribute werden als UPDATE zu der
entsprechenden Tabelle "weitergeleitet". Um die Attribute Tabellen zuzuordnen muss man
leider die ganzen Meta-Informationen der Tabellen auslesen.

Viele Grüsse

Philipp

[Andreas Korthaus]

Hi Philipp!

Das heißt ich merke hinterher gar nicht mehr welches Feld aus welcher Tabelle kommt.

Ja...? - Das ist auch richtig so, denn jeder Query gibt dir eine ResultSet zurück,
was ebenfalls wider eine Tabelle ist. Der Query ist nur eine Abbildungsvorschrift und
es ist streng genommen nur ein "Spezialfall", wenn ein Feld auch äquivalent in der
Ergebnisrelation abgebildet wird. Eine Zuordnung zu einer früheren Bezeichnung ist
eigentlich falsch (obwohl eigentlich manchmal, wie bei dir jetzt, wünschenswert).

Ich will gar nicht auf die Tabelle schließen. Mir geht es erstmal um die Query und dessen Auswertung an sich.

Ich zeige mal was ich z.B. mache(PHP+PEAR):

$sql = "
        SELECT
          *
        FROM
          ".DB_PREFIX."events as events
        LEFT JOIN
          ".DB_PREFIX."event_types as types
        ON events.event_typ = types.event_typ_id
        WHERE
          events.group_id = '".$user->getVar('group_id')."'
        ";

print_r($db->getAll($sql));

Jetzt bekomem ich z.B. so einen Array:

array(
    '1' => array(
             'id' = 123,
             'name' = andreas,
             'typ' = a
           )
    '2' => array(
             'id' = 456,
             'name' = philipp,
             'typ' = b
           )
)

Hierbei stammt z.B. 'id' aus Tabelle 'events' und 'typ'aus der Tabelle 'event_types'.

Genau das ist es was ich haben will, denn so kann ich das ganze sehr schön an ein Template übergeben und dort einfach ausgeben.

Aber kann das jede DB so machen?

Was sicher funktioniert ist:

SELECT
          events.id as id,
          events.name as name,
          types.typ
        FROM
          ".DB_PREFIX."events as events
        LEFT JOIN
          ".DB_PREFIX."event_types as types
        ON events.event_typ = types.event_typ_id
        WHERE
          events.group_id = '".$user->getVar('group_id')."'
        ";

Nur ist das bei mir noch ne Ecke komplexer, daher würde ich gerne da drauf verzichten. Die Frage ist halt, wenn ich mehr als eine Tabelle in der Abfrage verwende, muss ich dann alle Felder einzelnd mit einem Alias versehen, damit sie auch z.B. in PostgreSQL korrekt (in Form eines solchen Array wie oben) ausgegeben werden können? Und was wenn ich nur einen gleichen Feldnamen in 2 Tabelen habe, dann geht es wohl nicht anders, oder?

Viele Grüße
Andreas

[Philipp Hasenfratz]

Halihallo Andreas

Hierbei stammt z.B. 'id' aus Tabelle 'events' und 'typ'aus der Tabelle 'event_types'.
Genau das ist es was ich haben will, denn so kann ich das ganze sehr schön an ein Template übergeben und dort einfach ausgeben.
Aber kann das jede DB so machen?

Nun ja, so sieht es auf jeden Fall der Standard vor... Das sollte IMHO bei jeder
Datenbank so geschehen; Probleme gibt's IMHO erst, wenn eben zwei Attribute denselben
Namen haben.

Was sicher funktioniert ist:

Wobei das "AS" auch DB-spezifisch ist, just in case.

Nur ist das bei mir noch ne Ecke komplexer, daher würde ich gerne da drauf verzichten. Die Frage ist halt, wenn ich mehr als eine Tabelle in der Abfrage verwende, muss ich dann alle Felder einzelnd mit einem Alias versehen, damit sie auch z.B. in PostgreSQL korrekt (in Form eines solchen Array wie oben) ausgegeben werden können?

Nein, das brauchst du IMHO nicht. Zugegeben, ich weiss es nicht mit Sicherheit, bin
jedoch davon überzeugt, dass es auch bei jeder anderen Datenbank so funktioniert.

Und was wenn ich nur einen gleichen Feldnamen in 2 Tabelen habe, dann geht es wohl nicht anders, oder?

Dann gibt's Probleme, ja. Das werden ggf. die DBMS auch als Fehler melden.

Viele Grüsse

Philipp

[Andreas Korthaus]

Hi Philipp!

Nun ja, so sieht es auf jeden Fall der Standard vor... Das sollte IMHO bei jeder
Datenbank so geschehen; Probleme gibt's IMHO erst, wenn eben zwei Attribute denselben
Namen haben.

Ja, das ist ja auch logisch. In dem Fall muss ich es dann mit Alias("AS") lösen.

Wobei das "AS" auch DB-spezifisch ist, just in case.

Das ist schlecht. Wie soll ich das sonst machen, wenn ich 2 Tabellen habe die einen gleichen Spaltennamen haben? Ich versuche das weitgehend zu vermeiden wobei das ja nicht gerade der Sinn von Relationen ist, aber ich nenne die ID(Primärschlüssel) in der 1. Tabelle aus diesem Grund sicherheitshalber auch event_id, und aus der 2. dann typ_id. Wie würdest Du sowas sonst machen?

Viele Grüße
Andreas

[Philipp Hasenfratz]

Halihallo Andreas

Wobei das "AS" auch DB-spezifisch ist, just in case.

Das ist schlecht. Wie soll ich das sonst machen, wenn ich 2 Tabellen habe die einen gleichen Spaltennamen haben? Ich versuche das weitgehend zu vermeiden wobei das ja nicht gerade der Sinn von Relationen ist, aber ich nenne die ID(Primärschlüssel) in der 1. Tabelle aus diesem Grund sicherheitshalber auch event_id, und aus der 2. dann typ_id. Wie würdest Du sowas sonst machen?

Halt, halt. Ich wollte nicht sagen, dass es dies in anderen DBMS nicht gibt; sondern
nur anmerken, dass die Syntax anders aussieht. Du sprichst von einem DB-Layer und
von MySQL, PostgreSQL und evtl. noch andere; deshalb habe ich dich darauf hingewiesen.

SELECT B.foo_id, A.bar_id FROM foo A, bar B

gibt's z. B. auch.

Viele Grüsse

Philipp

[Andreas Korthaus]

Hi!

Halt, halt. Ich wollte nicht sagen, dass es dies in anderen DBMS nicht gibt; sondern
nur anmerken, dass die Syntax anders aussieht.

Das ist fast genauso schlimm wie "gibts nicht", denn sowas verhindert eine wirkliche Abstraktion von der Datenbank. Ich denke ich muss PEAR::DB mal etwas näher unter die Lupe nehmen, denn wechen Sinn hätte so ein Abstraktions-Layer, wenn man damit keinen wirklich halbwegs global verwendbaren SQL-Code schreiben könnte! Aber zur Not gibt es da ja noch ein Package, das SEL-Querys aus ich glaube irgenwelchen Arrays die man übergibt generiert, da sollte das vielleicht besser funkitonieren

Du sprichst von einem DB-Layer und
von MySQL, PostgreSQL und evtl. noch andere; deshalb habe ich dich darauf hingewiesen.

Ja, wie ist das denn jetzt? Kann ich bei den genannten eine gemeinsame Syntax für Alias verwenden?

SELECT B.foo_id, A.bar_id FROM foo A, bar B

gibt's z. B. auch.

Ja, aber ich will im Code mich nicht um verschiedene Datenbank-abhängigen SQL-Versionen kümmern müssen!

Grüße
Andreas

[Philipp Hasenfratz]

Halihallo Andreas

Halt, halt. Ich wollte nicht sagen, dass es dies in anderen DBMS nicht gibt; sondern
nur anmerken, dass die Syntax anders aussieht.
Das ist fast genauso schlimm wie "gibts nicht", denn sowas verhindert eine wirkliche Abstraktion von der Datenbank.

Ja, das ist leider wahr. Darüber bin ich genauso unglücklich.

Ich denke ich muss PEAR::DB mal etwas näher unter die Lupe nehmen, denn wechen Sinn hätte so ein Abstraktions-Layer, wenn man damit keinen wirklich halbwegs global verwendbaren SQL-Code schreiben könnte!

Nun, es geht ja darum eine genau spezifizierte Schnittstelle zu erstellen, um den
Umgang mit den Datenbanken einheitlich zu lösen und nicht für jeden DatenbankManager
ein anderes Modul oder was auch immer laden zu müssen. Was PEAR::DB und DBI für Perl
nicht können, ist eine unabhängige SQL-Syntax zu definieren und umzusetzen...

Aber zur Not gibt es da ja noch ein Package, das SEL-Querys aus ich glaube irgenwelchen Arrays die man übergibt generiert, da sollte das vielleicht besser funkitonieren

Ja, ich habe davon gelesen. Über den Namen kann man dann auf ein bestimmter Query
schliessen, welche wie ein Template funktionieren... Glaube, das habe ich mal auf-
geschnappt. Theoretisch müsste man dann für jeden SQL-Dialekt-"Kategorien" einen
eigenen Query definieren...

Du sprichst von einem DB-Layer und
von MySQL, PostgreSQL und evtl. noch andere; deshalb habe ich dich darauf hingewiesen.

Ja, wie ist das denn jetzt? Kann ich bei den genannten eine gemeinsame Syntax für Alias verwenden?

PostgreSQL und MySQL verwenden beide die " table AS alias " Syntax. Probleme gibt's
z. B. IMHO mit MS SQL Server.

SELECT B.foo_id, A.bar_id FROM foo A, bar B
gibt's z. B. auch.
Ja, aber ich will im Code mich nicht um verschiedene Datenbank-abhängigen SQL-Versionen kümmern müssen!

Naja, bei PostgreSQL und MySQL geht's ja schon. Und wer was anderes will, muss halt
ändern... leider...

Viele Grüsse

Philipp

[Matti Maekitalo]

use Mosche;

Entwickele gerade an einem Projekt in dem ich PHP/PEAR zur DB-Abstraktion verwenden möchte. Die Abfragen sollen zumindest unter MySQL und PostgreSQL laufen. Da habe ich erstmal eien spezielle Frage:

ich bekomme einen Assoziativen Array von der DB zurück, der direkt alle Spaltennamen beider Tabellen "index" enthält.

Ich habe noch nie mit PHP gearbeitet, habe allerdings die selbe Ausgangslage wie du (lokal PostGreSQL, im Einsatz MySQL). Ich lasse mir (in Perl) von meinem Datenbank-handle keinen Hash (assoziativen Array) zurückliegern, sondern eine Liste. Wenn du die Spaltennamen nicht _noch einmal_ explizit benötigst (da du sie durch dein Select sowieso schon kennst, und dadurch die Reihenfolge auch bekannt ist), könntest du dieses Problem so vermeiden.

Kennt Ihr vielleicht noch ein paar typische "Stolpersteine" wenn man eine Anwendung mit Datenbank-Abstraktion programmieren will? Speziell MySQL und PostgreSQL betreffend? Wie sieht das aus mit Transaktionen?  COMMIT und END kennen wohl beide, aber welchen Tabellentreiber würdet Ihr für mySQL(3.23.54) empfehlen?

Ich habe bei mir keine großen Fallen entdeckt, nur ein paar Inkonsistenzen was LIMIT und OFFSET angeht (die in der Kurzform (nur diese kennt Pg) jeweils anders herum verwendet werden).

Eine Frage nur so nebenbei: stimmt es, daß MySQL kein Boolean kennt? Danach habe ich nämlich fast verzweifelt gesucht, und bin jetzt auf integer (0, 1) umgestiegen, was allerdings nicht so optimal ist.

use Tschoe qw(Matti);

[Andreas Korthaus]

Hallo!

Ich habe noch nie mit PHP gearbeitet, habe allerdings die selbe Ausgangslage wie du (lokal PostGreSQL, im Einsatz MySQL).

Bei mir ist es umgekehrt, der Server auf dem das mal laufen soll wird PostgreSQL verwenden, aber bis ich das da installieren kann habe ich auf dem dafür vorgesehenen Server nur MySQL zur Verfügung, außerdem soll die Software auch MySQL "beherrschen" falls es auch mal mit MySQL laufen soll, was ich nicht hoffe, vor allem wegen Transaktionssicherheit.

Ich lasse mir (in Perl) von meinem Datenbank-handle keinen Hash (assoziativen Array) zurückliegern, sondern eine Liste.

Was ist der Unterschied zw. einem Hash und einer "liste"? Ich verwende auch keinen Array wie hier beschreiben, sondern ich lasse mir ein Objekt zurückgeben, im Prinzip nur weiß ich die Syntax lieber mag. Naja, dank PEAR geht das alles in PHP ganz praktisch, vor allem zusammen mit meiner Template-Engine, mal ein Code-Beispiel:

name.php:
---------

$sql = "
        SELECT
          *
        FROM
          ".DB_PREFIX."events as events
        LEFT JOIN
          ".DB_PREFIX."event_types as types
        ON events.event_typ = types.event_typ_id
        WHERE
          events.group_id = '".$user->getVar('group_id')."'
        ";

$tpl->assign('data', $db->getAll($sql));
$tpl->display('name.tpl');

name.tpl:
---------
<html>
<table>
{foreach from=$data item="row"}
  <tr>
    <td>{$row->event_id}</td>
    <td>{$row->name}</td>
    <td>{$row->typ}</td>
  </tr>
{foreach}
</table>
</html>

das ist wirklich praktisch!

Wenn du die Spaltennamen nicht _noch einmal_ explizit benötigst (da du sie durch dein Select sowieso schon kennst, und dadurch die Reihenfolge auch bekannt ist), könntest du dieses Problem so vermeiden.

Durch Trennung von PHP-Script und Templates wird das ganze doch wieder etwas schwieriger, im Template ist es schon sehr angenehm wenn ich mit {$row->name} zugreifen kann, als irgendwelche Zahlen oder sowas. Daher ist es auch gut so wie es ist, nur wollte ich mal abklären wie PostgreSQL damit umgehen.

Ich habe bei mir keine großen Fallen entdeckt, nur ein paar Inkonsistenzen was LIMIT und OFFSET angeht (die in der Kurzform (nur diese kennt Pg) jeweils anders herum verwendet werden).

Danke für den Tipp. MUss ich mal gucken, aber das wäre ein bedeutendes Problem, ich hoffe dass PEAR das aus der Welt räumt, aber das bezweifele ich irgendwie. Wäre aber doof wenn ich jedesmal bei der Verwendung von LIMIT vorher in den Scripten den DB-Typ testen müsste... eigenlich sollte PEAR::DB(DB-Abstraktions-Layer, Teil von PEAR einer PHP-Klassenbibliothek) sich um sowas kümmern.

Eine Frage nur so nebenbei: stimmt es, daß MySQL kein Boolean kennt? Danach habe ich nämlich fast verzweifelt gesucht, und bin jetzt auf integer (0, 1) umgestiegen, was allerdings nicht so optimal ist.

Soweit ich weiß kann MySQL das nicht, das nervt mich auch. Zumal ich dann das Ergebnis der DB-Abfrage teilweise noch "überarbeiten" muss. Ich verwende hierfür in MySQL den Datentyp SET und stelle nur '0' und '1' zur Auswahl, keine Ahnung ob TINYINT vielleicht besser/schneller wäre, vielleicht kennt ja jemand anders hier eine gute Möglichkeit?

Viele Grüße
Andreas

[Philipp Hasenfratz]

Halihallo Andreas

Ich lasse mir (in Perl) von meinem Datenbank-handle keinen Hash (assoziativen Array) zurückliegern, sondern eine Liste.
Was ist der Unterschied zw. einem Hash und einer "liste"?

Ein Hash ist ein assoziatives Array, als Index dient also ein frei festlegbarer String.
Bei einem Array (Perl-Sprachgebrauch) handelt es sich um ein Array, auf dessen Elemente
ausschliesslich über den vom "System" gegebenen numerischen Index zugetriffen werden
kann. Bei PHP ist das ja vermischt, oder?

Eine Frage nur so nebenbei: stimmt es, daß MySQL kein Boolean kennt? Danach habe ich nämlich fast verzweifelt gesucht, und bin jetzt auf integer (0, 1) umgestiegen, was allerdings nicht so optimal ist.
Soweit ich weiß kann MySQL das nicht, das nervt mich auch.

Ja, http://www.mysql.de/doc/de/Column_types.html Boolean gibt's net...

Zumal ich dann das Ergebnis der DB-Abfrage teilweise noch "überarbeiten" muss. Ich verwende hierfür in MySQL den Datentyp SET und stelle nur '0' und '1' zur Auswahl, keine Ahnung ob TINYINT vielleicht besser/schneller wäre, vielleicht kennt ja jemand anders hier eine gute Möglichkeit?

IMHO ist TINYINT und SET die einzigen zwei vernünftigen "Workarounds". Alles andere
verbraucht mehr Speicher, oder müsste als CHAR(1) gespeichert werden... Mit Zahlen
kann MySQL bekanntlich gut umgehen und SET wird intern auch als solche behandelt.
Beide brauchen nur ein Byte und selbst wenn es BOOLEAN gäbe, würde er auch ein Byte
beanspruchen (ein Zusammenziehen mehrerer BOOLEANS wäre unsinnvoll).

Viele Grüsse

Philipp

[Andreas Korthaus]

Hi!

Ein Hash ist ein assoziatives Array, als Index dient also ein frei festlegbarer String.
Bei einem Array (Perl-Sprachgebrauch) handelt es sich um ein Array, auf dessen Elemente
ausschliesslich über den vom "System" gegebenen numerischen Index zugetriffen werden
kann.

Ist Liste das was in PHP ein numerischer Array ist? Ich habe von PERL nur behalten dass ein Hash etwa einem assoziativen Array in PHP entspricht, und ein Array eben einem numerischen Array in PHP, aber jetzt wo Du's sagst, letztere wurden glaue ich auch Liste genannt ;-)

Bei PHP ist das ja vermischt, oder?

In PHP gibt es entweder:

$my_num_array = array(1,5,'s');
print $mein_num_array[1]; // gibt aus '5'

oder:

$my_assoc_array = array('a' => 1, 'x' => 5, 'bla' => 's');
print $mein_assoc_array['x']; // gibt aus '5'

und auch Mischungen aus beiden.

Grüße
Andreas

[Philipp Hasenfratz]

Halihallo Andreas

Ist Liste das was in PHP ein numerischer Array ist?

Ja, ein Array ist bei PHP ein numerisches Array. Der Hash ein assoziatives Array...

Ich habe von PERL nur behalten dass ein Hash etwa einem assoziativen Array in PHP entspricht, und ein Array eben einem numerischen Array in PHP, aber jetzt wo Du's sagst, letztere wurden glaue ich auch Liste genannt ;-)

Genau so.

Bei PHP ist das ja vermischt, oder?
In PHP gibt es entweder:
$my_num_array = array(1,5,'s');
print $mein_num_array[1]; // gibt aus '5'
oder:
$my_assoc_array = array('a' => 1, 'x' => 5, 'bla' => 's');
print $mein_assoc_array['x']; // gibt aus '5'

und auch Mischungen aus beiden.

Männo, wie unschön PHP doch ist :-))
Naja, es mag zwar so einfacher sein, aber so können numerische Arrays in PHP nur sehr
ineffizient verwaltet und verarbeitet werden... Intern muss dann ja jedes Array
zwingend als assoziatives Array gespeichert werden :-(
just a thought...

Viele Grüsse

Philipp

[Matti Maekitalo]

use Mosche;

Ich habe bei mir keine großen Fallen entdeckt, nur ein paar Inkonsistenzen was LIMIT und OFFSET angeht (die in der Kurzform (nur diese kennt Pg) jeweils anders herum verwendet werden).

Danke für den Tipp. MUss ich mal gucken, aber das wäre ein bedeutendes Problem, ich hoffe dass PEAR das aus der Welt räumt, aber das bezweifele ich irgendwie. Wäre aber doof wenn ich jedesmal bei der Verwendung von LIMIT vorher in den Scripten den DB-Typ testen müsste... eigenlich sollte PEAR::DB(DB-Abstraktions-Layer, Teil von PEAR einer PHP-Klassenbibliothek) sich um sowas kümmern.

Ich habe nochmal nachgeschaut:

select kal_id from kalender ORDER BY kal_id LIMIT 3,4;

ist meine Abfrage. Mysql hat OFFSET 3 und Limit 4, PostgreSQL andersherum. Eine lange Abfrage in der Form
SELECT kal_id from kalender ORDER by kal_id LIMIT 3 OFFSET 4;
kann ich nur bei Postgres zum laufen bringen, Mysql gibt mir hier einen Fehler (obwohl es laut Doc (http://www.mysql.com/doc/en/SELECT.html) so gehen sollte.

Das steckt allerdings so tief in der DB (bzw. SQL), daß ich bezweifle, dass PEAR  da was macht. Aber: Probieren kostet (meistens) nichts :-).

use Tschoe qw(Matti);