Tagesarchiv für den 5. August 2011

Datenbank-unabhängiges SQL

Freitag, den 5. August 2011

Beim Durchstöbern meiner RSS-Feeds bin ich eben auf die Meldung von Icinga gestoßen, wonach diese künftig zur Abstraktion der Datenbankabfragen Doctrine einsetzen, statt für jede unstützte Datenbank eigene SQL-Befehle zu pflegen.

Der Hintergrund ist folgender: möchte man seine Daten beispielsweise sowohl in einer MySQL- als auch in einer Oracle-Datenbank speichern können, müssen beide Datenbanksysteme häufig mit unterschiedlichen SQL-Statements angesprochen werden. Das liegt darin begründet, dass jeder Datenbankhersteller sein eigenes Süppchen kocht - verschiedene Standards (SQL-92, SQL-99) werden zwar relativ breit unterstützt, aber manchmal hört es mit der Kompatibilität schon auf, wenn man nur einen Datum-Wert in einem Statement verwenden möchte. Sowas wird übrigens gerne auch als Vendor Lock-In bezeichnet.

Ein Ansatz ist eben, die eigentlichen Zugriffe durch die Verwendung eines Objektpersistenz-Frameworks zu abstrahieren. Der Vorteil ist ganz klar eine ordentliche Trennung der verschiedenen Funktionsbereiche und eine saubere Klassenstruktur. Ein Nachteil dabei kann aber sein, dass man auf die Performance durch Optimierung der Abfragen vielleicht keinen Einfluss hat (kommt sehr auf den jeweiligen Einzelfall an).

Ein anderer Weg wäre, für alle zu unterstützenden SQL-Dialekte eigene SQL-Statements zu pflegen. Davon rate ich aber entschieden ab: zum Testen muss die komplette Anwendung somit auf allen unterstützten Datenbanksystemen vollständig geprüft werden, der Pflegeaufwand ist sehr hoch und entsprechend auch sehr fehleranfällig.

Bei LiveConfig standen wir vor einer ganzen Weile vor genau dem selben Problem. Es sollen verschiedene Datenbanksysteme unterstützt werden (vorerst SQLite und MySQL, später PostgreSQL und vielleicht auch Oracle), während aber nicht für jedes DBMS eigene SQLs gepflegt werden sollen. Doctrine kam nicht in Frage (schließlich entwickeln wir in C/C++), und besonders viele leichtgewichtige C/C++-Persistenz-Frameworks auf SQL-Basis scheint’s nicht zu geben.

Wir haben daher einen völlig anderen Ansatz verfolgt. Das war zwar mit einem recht hohen Initial-Aufwand verbunden, zahlt sich aber (unserer Ansicht nach) auf Dauer aus: die Entwicklung eines eigenen SQL-Übersetzers. Man muss sich das so vorstellen: die Anwendung “spricht” einen relativ generischen SQL-Dialekt (im Groben und Ganzen ein SQL99), und bevor ein Statement zur Datenbank gesendet wird, wird es in den entsprechenden Ziel-Dialekt übersetzt.

Beispiel: es sollen die Datensätze 11 bis 15 einer Tabelle abgerufen werden. In MySQL macht man das mit

SELECT a, b, c FROM table WHERE b=1 ORDER BY a LIMIT 5 OFFSET 11

während man mit Oracle (als Extrembeispiel) etwas mehr tricksen muss:

SELECT a, b, c FROM (SELECT a, b, c, rownum AS limit_rownum FROM (SELECT a, b, c FROM table WHERE b=1 ORDER BY a)) WHERE limit_rownum BETWEEN 11 AND 15

Unsere Lösung wird in Form einer eigenen C-Bibliothek in den Code eingebunden und stellt eine datenbankunabhängige API zur Verfügung. Die Bibliothek kümmert sich dabei selber um das Laden der notwendigen Datenbank-Treiber (libmysqlclient, OCI, …); somit weiß man auf Anwendungs-Ebene im Grunde auch gar nicht, auf welchem System die Anfragen nun tatsächlich ausgeführt werden.

In unserem Fall würde die Anwendung für o.g. SQL-Anfrage also folgendes Statement verwenden:

SELECT a, b, c FROM table WHERE b=:1 ORDER BY a LIMIT :2 OFFSET :3

Intern wird dieses dann automatisch in den gewünschten Ziel-Dialekt (s.o.) übersetzt. Jede Anfrage wird dabei als Prepared Statement abgearbeitet, welches durch unsere Bibliothek intern auch noch zur Wiederverwendung gecached wird. Die Vorteile liegen auf der Hand:

  • die Übersetzung in den Ziel-Dialekt muss während der gesamten Laufzeit nur ein einziges Mal durchgeführt werden
  • auch die Datenbank muss das Statement nur ein einziges mal parsen
  • da alle Parameter über Variablen gebunden sind, sind SQL Injections praktisch unmöglich

Das Ergebnis: eine unschlagbare Performance und gleichzeitig voller Einfluss auf den Aufbau der SQL-Statements. Die Bibliothek lässt sich isoliert wunderbar testen (für alle SQL-Anfragetypen haben wir entsprechende checklib Unit Tests geschrieben), und auch für andere Projekte prima recyclen.

Der Hauptaufwand bestand in der Entwicklung des entsprechenden SQL-Parsers (hier auf Basis von lex und yacc) sowie der möglichst vollständigen Unit-Tests. Natürlich wird nur ein Bruchteil des kompletten SQL-Sprachumfangs abgedeckt, aber eben alles, was unsere Anwendungen benötigen. Als letztes “Leckerli” können wir natürlich auch zentral alle SQL-Anfragen loggen, und diese Logs analysieren (z.B. die Häufigkeit der Statements auswerten, oder fertig übersetzte Statements zur Optimierung der Tabellen-Indizes verwenden).

Für Perl bin ich vor Jahren mal über SQLFairy gestoßen, was scheinbar einen ähnlichen Ansatz verfolgt. Eine andere (etwas schwergewichtigere) Alternative wäre übrigens ODBC.