Suche Tipps für MySQL -> Oracle

[Donald]

Hallo,

ich stehe vor der Aufgabe, dass meine PHP Anwendung neben MySQL in Zukunft auch Oracle unterstützen soll. Ich habe bereits alle Datenbank-Funktionen in ein externes Include ausgelagert, und würde dieses dann auch für Oracle anpassen. So kann die Anwendung später bei der Einrichtung entweder MySQL oder Oracle verwenden.

Jetzt mein Problem:
Die SQL-Anweisungen setze ich natürlich nicht in diesem Include zusammen. Ich habe in meiner Anwendung Oft diesen Fall:

$SQL = "SELECT * FROM tbltesttabelle 
        WHERE LastActivity > '2010-03-27 23:59:59'
        ORDER BY LastActivity 
        LIMIT 10";
$db = new classDBOpen($SQL);
while($ROW = $db->fetchObject()){
    // do domething with the records
} 

In diesem Beispiel habe ich zwei der bisher identifizierten Probleme eingebaut:

1. Das Datum muss bei Oracle immer mit TO_DATE() übergeben werden. Alle Datums-Query's müssten angepasst werden. Oder doch nicht?

2. Oracle kennt keine LIMIT Anweisung. Stattdessen soll ROWNUM verwendet werden. Laut diversen Google-Ergebnissen klappt das aber nicht in Verbindung mit ORDER BY, weil dann nicht die sortierten Ergebnisse limitiert werden. Alle Lösungen bauen den Query komplett um Ich kann nicht die ganze Anwendung doppelt anpassen.

Ich hab mir mal Datenbank-Frameworks angesehen, die angeblich von DB Unterschieden abschirmen sollen. Sie scheinen aber keine Lösung für meine Probleme darzustellen. Oder hab ich was übersehen?

Ich bin bereit, alle betroffenen Querys in der Anwendung umzubauen, aber ist das nötig? Weiterhin möchte ich dann nicht jeweils zwei Query's hinterlegen müssen. Am besten wäre eine Notation für alle Fälle.

Eine Idee:

$SQL = "SELECT * FROM tbltesttabelle 
        WHERE LastActivity > " . DBDate("2010-03-27 23:59:59") . " 
        ORDER BY LastActivity " . DBLimit(10); 

Hier würde ich Datumsangaben und eine LIMIT Klausel per Funktion anbauen. Für Oracle macht DBDate() dann eben den TO_DATE() hin und für MySQL nicht. Aber wie mache ich das mit LIMIT?

Bin gerade etwas Ratlos und verfluche Oracle immer öfter...

Donald

[thomas_w]

Bin gerade etwas Ratlos und verfluche Oracle immer öfter...
Donald

Könnte auch damit zu tun haben, dass sich MySQL nicht an den SQL-Standard hält (siehe LIMIT) und deshalb ein Wechsel auf eine andere DB schwierig ist.

Aber am Besten schaust Du mal nach den diversen Migrations Tutorial von MySQL nach ORACLE und anderst herum. Da werden zu den meisten Thema auch Lösung aufgezeigt.

EDIT
Eventuell wäre PDO auch ein Thema.

Grüße
Thomas

[Donald]

Hi Thomas,

danke für deine Antwort. Die Lösungen kenn ich schon, aber ich müsste eben fast alle Queries in meinem Code anpassen. Und dafür suche ich eine Lösung. Zum Beispiel was das Datum betrifft, soll es eine Möglichkeit geben das Oracle so beizubringen dass es Datumsangaben immer mit Datum='YYYY-MM-DD HH:MM:SS' akzeptiert ohne TO_DATE() zu benötigen. Aber wie? Und kann ich vorhandene LIMIT Klauseln etwa durch automatisiertes umstellen (PHP soll den Query für Oracle anpassen) weiterhin verwenden?

Grüße,

Donald

[thomas_w]

Im Zend Framework wird das LIMIT für ORACLE so umgesetzt:

zend/db/adapter/oracle.php (Beispiel ZF 1.7.2)

        /**
         * Oracle does not implement the LIMIT clause as some RDBMS do.
         * We have to simulate it with subqueries and ROWNUM.
         * Unfortunately because we use the column wildcard "*",
         * this puts an extra column into the query result set.
         */
        $limit_sql = "SELECT z2.*
            FROM (
                SELECT ROWNUM AS zend_db_rownum, z1.*
                FROM (
                    " . $sql . "
                ) z1
            ) z2
            WHERE z2.zend_db_rownum BETWEEN " . ($offset+1) . " AND " . ($offset+$count);
        return $limit_sql; 

mit dem Datum könnte vielleicht der Weg über Prepare $stmt->bindParam() hilfreich sein. Habe es aber nicht probiert.

Grüße
Thomas

[Donald]

Hi Thomas,

vielen Dank für den ZEND Part. Das ist die Grundlage für meine Routine. Sie erkennt eine MySQL LIMIT Klausel (LIMIT Count oder LIMIT Offset, Count) und wandelt das in Oracle verträgliches um. Was denkt Ihr drüber? Ist da ein Fehler drinnen?

function ConvertOracleLimit($SQL) {
    // find LIMIT position
    $Pos = strripos($SQL, "limit ");
    if ($Pos === FALSE) { 
        return $SQL; // no LIMIT clause found, no need to do something
    }
    
    // find a following comma position (max. 8 chars later)
    $PosComma = stripos($SQL, ",", $Pos);
    if ($PosComma > $Pos + 6 + 8) {
        $PosComma = FALSE; // too far away
    }
    
    // get min and count values
    if ($PosComma === FALSE) {
        // using no offset
        $MinRow = 1;
        $Count = intval(substr($SQL, $Pos + 5, 6));
        
        $PosRestBlank = stripos($SQL, " ", $Pos + 7);
        if ($PosRestBlank === FALSE) { $PosRestBlank = strlen($SQL); }
        $PosRestLF = stripos($SQL, " ", $Pos + 7);
        if ($PosRestLF === FALSE) { $PosRestLF = strlen($SQL); }

    } else {
        // using offset
        $MinRow = intval(substr($SQL, $Pos + 5, $PosComma - $Pos - 5));
        $Count = intval(substr($SQL, $PosComma + 1, 5));
        
        $PosRestBlank = stripos($SQL, " ", $PosComma + 2);
        if ($PosRestBlank === FALSE) { $PosRestBlank = strlen($SQL); }
        $PosRestLF = stripos($SQL, " ", $PosComma + 2);
        if ($PosRestLF === FALSE) { $PosRestLF = strlen($SQL); }

    }
    
    // choose nearest end of limit (linefeed or a blank char)
    if ($PosRestBlank < $PosRestLF) {
        $PosRest = $PosRestBlank;
    } else {
        $PosRest = $PosRestLF;
    }

    // remove original limit clause
    $SQL = substr($SQL, 0, $Pos) . substr($SQL, $PosRest);
    
    // cover SQL by oracle specific limit variant
    $limit_sql = "SELECT z2.* FROM ( 
                        SELECT ROWNUM AS db_rownum, z1.* FROM ( 
                        $SQL
                        ) z1 
                  ) z2 
                  WHERE z2.db_rownum BETWEEN " . ($MinRow) . " AND " . ($MinRow+$Count); 
    return $limit_sql;
} 

Ich werd das noch etwas schöner machen müssen, aber soweit klappt das hier schonmal ganz gut.

Donald

EDIT:
Mir fehlt nur noch eine Idee, wie ich verhindern kann das dass auch bei einem String wie "I accept no limit at all" greift. Das ist natürlich schwer...

[thomas_w]

EDIT:
Mir fehlt nur noch eine Idee, wie ich verhindern kann das dass auch bei einem String wie "I accept no limit at all" greift. Das ist natürlich schwer...

Stellt sich mir die Frage, was dass in einem SQL-String zu suchen hat, aber ein gültiger SQL-Befehl mit LIMIT sollte "SELECT", "UPDATE" oder "DELETE" enthalten. Darauf kannst Du den String ja durchsuchen, sonst ist es kein SQL-Befehl, bei dem LIMIT vorkommen kann.

Grüße
Thomas

[erc]

Mir fehlt nur noch eine Idee, wie ich verhindern kann das dass auch bei einem String wie "I accept no limit at all" greift. Das ist natürlich schwer...

Das einfachste ist von hinten prüfen, LIMIT steht immer an letzter Stelle. Ausnahme sind Subqueries, aber die bekommst du definitiv so nicht umgewandelt.

[nikosch]

Sie erkennt eine MySQL LIMIT Klausel (LIMIT Count oder LIMIT Offset, Count) und wandelt das in Oracle verträgliches um. Was denkt Ihr drüber?

Eigentlich ganz einfach: Du musst das machen bevor Du Queryplatzhalter durch konkrete Werte überschreibst.

[Donald]

Hallo,

Rückwärts suchen ist einfach zu realisieren. Einfach gleich zu beginn das "limit " nicht mit stripos() sondern mit strripos() suchen. Das sucht von hinten kommend.

$Pos = strripos($SQL, "limit "); 

Ich werd das in der geposteten Routine anpassen...

Ich habe meine Anwendung nach LIMIT durchsucht und finde keinen Query der LIMIT nicht am Ende hätte (also nicht in Subquery's). Evtl. könnte ich mit dieser Einschränkung leben...

Donald

[Donald]

Hm,

jetzt ist das Datum noch ein Problem. Ich habe auch viele Datumsberechnungen in den Query's. Funktionen wie DATE_ADD, DATE_SUB oder auch DATE_FORMAT. Da gibts wohl keinerlei Kompatibilität, oder?

Donald

[thomas_w]

Ja, da hat MySQL eine Menge praktischer Dinge erfunden, dass sich aber so nicht im SQL-Standard wieder findet und somit auch anderen Datenbanken nicht bekannt ist. In ORACLE könntest Du für jede fehlende MySQL-Funktion eine PL/SQL Funktion bauen, die diese MySQL-Funktion nachbildet. Dann könnten die bestehenden SQL-Befehle so bleiben, andererseits musst Du die Abfragen für ORACLE umbauen.

DATE_FORMAT läßt sich in Oracle mit TO_CHAR() und TO_DATE() nachbilden.

Grüße
Thomas