Wie verwalten Sie SQL-Abfragen

Die beste Vorgehensweise für Sie hängt davon ab, wie Sie sich Ihre Daten zugreifen. Es gibt drei Ansätze, die Sie ergreifen können:

• Gespeicherte Prozeduren verwenden
• Halten Sie die Abfragen in den code (aber alle Ihre Abfragen in Funktionen und alles fix zu verwenden PDO Parameter, wie bereits erwähnt)
• Verwenden Sie ein ORM-tool

Wenn Sie möchten, übergeben Sie Ihre eigenen raw-SQL, um die Datenbank-engine dann gespeicherte Prozeduren wäre der Weg zu gehen, wenn alles, was Sie tun möchten ist, bekommen die raw-SQL aus Ihrem PHP-code, aber halten Sie es relativ unverändert. Die gespeicherten Prozeduren vs raw SQL-Debatte ist ein bisschen von einem Heiligen Krieg, aber K. Scott Allen, macht ein sehr guter Punkt - wenn auch ein Wegwerf-einmal - in einem Artikel über Versionsverwaltung von Datenbanken:

Zweitens, gespeicherte Prozeduren haben, die gefallenen aus der Gunst in meinen Augen. Ich kam von der WinDNA Schule der Indoktrination, der sagte gespeicherten Prozeduren verwendet werden sollte, die ganze Zeit. Heute sehe ich gespeicherte Prozeduren als eine API-Schicht für die Datenbank. Das ist gut, wenn Sie eine API-Schicht auf der Datenbank-Ebene, aber ich sehe viele Anwendungen den Verarbeitungsaufwand, der Erstellung und Pflege einer extra-API-Schicht, die Sie nicht brauchen. In diesen Anwendungen gespeicherte Prozeduren sind eher eine Belastung als ein Vorteil.

Neige ich dazu zu neigen, die nicht mithilfe von gespeicherten Prozeduren. Ich habe gearbeitet an Projekten, wo die DB hat eine API ausgesetzt, die durch gespeicherte Prozeduren, aber gespeicherte Prozeduren können zu verhängen einige Einschränkungen auf Ihre eigenen, und diese Projekte haben alle, in unterschiedlichem Ausmaß, verwendet, die dynamisch generiert werden, rohen SQL-code zum Zugriff auf die DB.

Dass eine API-Schicht auf der DB gibt bessere Abgrenzung der Zuständigkeiten zwischen der DB team und das Dev-team auf Kosten einiger Flexibilität, die Sie haben würden, wenn die Abfrage, die in gehalten wurde, der code, aber PHP-Projekte sind weniger wahrscheinlich, um ansehnliche genug teams profitieren von dieser Abgrenzung.

Konzeptionell, sollten Sie wahrscheinlich Ihre Datenbank versioniert. Praktisch gesprochen, jedoch sind Sie viel eher nur Ihre code versioniert, als Sie sind, um Ihre Datenbank versioniert. Sie werden wahrscheinlich ändern Sie Ihre Abfragen, wenn Sie änderungen an Ihrem code, aber wenn Sie ändern sich die Abfragen in gespeicherten Prozeduren gespeichert, gegen die Datenbank, dann werden Sie wahrscheinlich nicht überprüfen diese, wenn Sie überprüfen Sie den code, und Sie verlieren viele der Vorteile bei der Versionsverwaltung für einen bedeutenden Bereich der Anwendung.

Unabhängig davon, ob oder nicht Sie beschließen, nicht gespeicherte Prozeduren verwenden, jedoch sollten Sie zumindest gewährleisten, dass jedes Datenbank-operation gespeichert ist, in eine unabhängige Funktion, sondern als Einbettung in die jeweils von Ihrer Seite Skripte - im wesentlichen eine API-Schicht für die DB, die ist gepflegt und versioniert mit Ihrem code. Wenn Sie mit gespeicherten Prozeduren, dies effektiv bedeutet, Sie haben zwei API-Schichten für die DB, eine mit dem code und eines mit der DB, die Sie fühlen sich vielleicht unnötig kompliziert Dinge, wenn Ihr Projekt keine eigenen teams. Ich sicherlich tun.

Wenn das Problem ist ein code, der Sauberkeit, gibt es Möglichkeiten, um code mit SQL-verklemmt in der es mehr vorzeigbar, und die UserManager-Klasse angezeigt, unten ist ein guter Weg, um zu starten - die Klasse enthält nur Abfragen, welche sich auf der 'user' - Tabelle, wird jede Abfrage hat seine eigene Methode in der Klasse und die Abfragen sind eingerückt in die prepare-Anweisungen formatiert und als würden Sie formatieren Sie Sie in einer gespeicherten Prozedur.

//UserManager.php:

class UserManager
{
    function getUsers()
    {
        $pdo = new PDO(...);
        $stmt = $pdo->prepare('
            SELECT       u.userId as id,
                         u.userName,
                         g.groupId,
                         g.groupName
            FROM         user u
            INNER JOIN   group g
            ON           u.groupId = g.groupId
            ORDER BY     u.userName, g.groupName
        ');
        //iterate over result and prepare return value
    }

    function getUser($id) {
        //db code here
    }
}

//index.php:
require_once("UserManager.php");
$um = new UserManager;
$users = $um->getUsers();
foreach ($users as $user) echo $user['name'];

Jedoch, wenn Ihre Abfragen sind ganz ähnlich, aber Sie haben große Anzahl von Permutationen, die in der Abfrage Bedingungen wie kompliziert paging, Sortieren, filtern, etc, ein Objekt/Relational-mapper-tool ist wahrscheinlich der Weg zu gehen, obwohl der Prozess der überarbeitung Ihrer vorhandenen code zu nutzen, das tool kann ganz schön kompliziert sein.

Wenn Sie entscheiden, zu untersuchen, ORM-tools, sollten Sie schauen,Treiben, die ActiveRecord-Komponente von Yii, oder der König-daddy PHP ORM, Lehre. Jeder dieser gibt Ihnen die Möglichkeit, programmgesteuert erstellen von Abfragen auf Ihre Datenbank mit allen Arten von komplizierten Logik. Lehre ist die voll ausgestattete, so dass Sie auf die Vorlage Ihrer Datenbank mit Dinge wie die Nested-Set-Baum-Muster aus der box.

In Bezug auf Leistung, sind die gespeicherten Prozeduren der Schnellste, aber in der Regel nicht viel über raw-sql. ORM-Werkzeuge kann erhebliche Auswirkungen auf die Leistung in einer Reihe von Möglichkeiten, die - ineffizient-oder redundante Abfragen, große Datei-E /a beim laden der ORM-Bibliotheken, die auf jede Anforderung, die dynamische SQL-Generierung für jede Abfrage... all diese Dinge einen Einfluss haben kann, aber die Verwendung von ein ORM-tool kann drastisch erhöhen die Leistung zur Verfügung, um Sie mit einer viel geringeren Menge an code, als die Erstellung Ihrer eigenen DB-layer mit manuellen Abfragen.

Gary Richardson hat absolut Recht, obwohl, wenn Sie weiterhin SQL verwenden in Ihrem code, den Sie sollten immer mit PDO die prepared-Anweisungen zu verarbeiten, die Parameter, unabhängig davon, ob Sie eine Abfrage oder eine gespeicherte Prozedur. Die Bereinigung der input erfolgt für Sie per PDO.

//optional
$attrs = array(PDO::ATTR_PERSISTENT => true);

//create the PDO object
$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "pass", $attrs);

//also optional, but it makes PDO raise exceptions instead of 
//PHP errors which are far more useful for debugging
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('INSERT INTO venue(venueName, regionId) VALUES(:venueName, :regionId)');
$stmt->bindValue(":venueName", "test");
$stmt->bindValue(":regionId", 1);

$stmt->execute();

$lastInsertId = $pdo->lastInsertId();
var_dump($lastInsertId);

Einschränkung: vorausgesetzt, die ID ist 1, das obige Skript wird die Ausgabe string(1) "1". PDO->lastInsertId() gibt die ID als string, unabhängig davon, ob die eigentliche Spalte ist ein integer ist oder nicht. Dies wird wahrscheinlich nie ein problem sein für Sie als PHP führt casting von strings in Ganzzahlen automatisch.

Folgende Ausgabe wird bool(true):

//regular equality test
var_dump($lastInsertId == 1); 

aber wenn Sie code haben, davon aus, dass der Wert ein integer sein, wie is_int oder PHP "wirklich 100% gleich" Betreiber:

var_dump(is_int($lastInsertId));
var_dump($lastInsertId === 1);

könnten Sie führen Sie in einige Probleme.

Edit: Einige gute Diskussion, die auf gespeicherten Prozeduren hier

Ich hatte zu bereinigen, ein Projekt, welches viele (doppelte/ähnliche) Abfragen, gespickt mit injection-Schwachstellen.
Die ersten Schritte, die ich nahm waren mit Platzhalter und label jede Abfrage mit dem Objekt/Methode und Quelle-Zeile der Abfrage erstellt wurde.
(Legen Sie die PHP-Konstanten METHODE und LINIE in einen SQL-Kommentar-Zeile)

Es sah so etwas wie dieses:

-- @Line:151 "userclass" gezeigt::getuser():

SELECT * FROM USERS;

Protokollierung sämtlicher Abfragen für kurze Zeit versorgt mich mit einige Ansatzpunkte auf, die Abfragen Zusammenführen. (Und wo!)

Verwenden Sie ein ORM-Paket, jede halbwegs anständige Paket ermöglicht es Ihnen,

1. Bekommen einfache Ergebnis-sets
2. Halten Sie Ihre komplexen SQL-nahe Datenmodell

Wenn Sie sehr komplexe SQL -, dann Ansichten sind auch schön, machen es mehr vorzeigbar zu den verschiedenen Schichten Ihrer Anwendung.

Gibt es einige Bibliotheken, wie MDB2 von PEAR machen, dass die Abfrage etwas einfacher und sicherer.

Leider, Sie können ein bisschen wortreich einrichten, und Sie müssen manchmal passieren Ihnen die gleichen Informationen zweimal. Ich habe MDB2 in ein paar Projekte, und ich neigte dazu, schreiben eine dünne Schicht um ihn herum, vor allem für die Angabe der Typen der Felder. Ich in der Regel ein Objekt, das weiß, dass zu einer bestimmten Tabelle und deren Spalten, und dann eine Hilfsfunktion, die in füllt Feldtypen für mich, wenn ich Sie nennen würde, MDB2-Abfrage-Funktion.

Zum Beispiel:

function MakeTableTypes($TableName, $FieldNames)
{
    $Types = array();

    foreach ($FieldNames as $FieldName => $FieldValue)
    {
        $Types[] = $this->Tables[$TableName]['schema'][$FieldName]['type'];
    }

    return $Types;
}

Offensichtlich dieses Objekt hat eine Karte von Tabelle Namen -> schemas, die es kennt, und nur Auszüge die Typen der Felder, die Sie angeben, und gibt ein passenden array-Typs geeignet für den Einsatz mit einem MDB2-Abfrage.

MDB2 (und ähnliche Bibliotheken), dann Griff die parameter-substitution für Sie, also für update/insert-Abfragen, die Sie gerade bauen Sie ein hash/Karte von Spalte name Wert, und die 'autoExecute' - Funktionen zum erstellen und ausführen der jeweiligen Abfrage.

Beispiel:

function UpdateArticle($Article)
{
    $Types = $this->MakeTableTypes($table_name, $Article);

    $res = $this->MDB2->extended->autoExecute($table_name,
        $Article,
        MDB2_AUTOQUERY_UPDATE,
        'id = '.$this->MDB2->quote($Article['id'], 'integer'),
        $Types);
}

und MDB2 wird die Abfrage erstellen, auf der Flucht alles richtig, etc.

Ich würde empfehlen, für die Messung der Leistung mit MDB2 obwohl, wie es zieht in ein gutes Stück von code, der möglicherweise Probleme verursachen, wenn Sie nicht mit einer PHP-accelerator.

Wie gesagt, die setup-Aufwand scheint entmutigend auf den ersten, aber sobald es fertig ist, können Sie die Abfragen einfacher/mehr symbolisch zu schreiben und (vor allem) ändern. Ich denke, MDB2 sollten wissen ein bisschen mehr über Ihr schema, was würde simpify einige der Häufig verwendeten API-Aufrufe, aber können Sie reduzieren das ärgernis, das durch die Kapselung des Schemas selbst, wie ich oben erwähnte, und die einfache-accessor-Funktionen generieren, dass die arrays MDB2 muss zum ausführen dieser Abfragen.

Natürlich können Sie nur flach SQL-queries als string mit der query () - Funktion, wenn Sie wollen, so sind Sie nicht gezwungen, zu wechseln auf die volle 'MDB2-Weg" - Sie können es ausprobieren, Stück für Stück, und sehen, ob Sie es hassen oder nicht.

Verwenden Sie ein ORM-framework wie QCodo - können Sie leicht ordnen Sie Ihre vorhandene Datenbank