README

Pdoqt is a small wrapper around PDO that makes it simple to run simple queries, and possible to run complex ones. It offers two kinds of methods: some that execute queries, and some that help you build queries. It doesn't prevent you from building or running invalid SQL; rather, it's designed to encourage a consistent and clear style of writing queries in PHP.

Pdoqt is written in a literate style. Thanks to docco, the same prose and code can be read in the source, the README, or in html.

namespace Pdoqt;

class Pdoqt {
    use \Defphp\Ex;

    private $conn;

    public function __construct (\PDO $connection) {
        $this->conn = $connection;
    }

    public function getConn (): \PDO {
        return $this->conn;
    }

The first thing to do is to create an error-checking wrapper around the query method. In my experience, it's usually expected that database statements execute successfully. An error with them is truly an exception and suitable for the try/catch treatment.

While we're at it, we can combine parameterized statements and parameterless statements in a single method.

    public function q (string $query, array $params = null): \PDOStatement {
        if (isset($params)) {
            $stmt = $this->conn->prepare($query);
            $stmt->execute($params);
        } else {
            $stmt = $this->conn->query($query);
        }

        if ($stmt === false) {
            $msg = "Error executing query $query";
            if (isset($params)) {
                $msg .= " w/ params " . json_encode($params);
            }
            $msg .= ": " . implode(" ", $this->conn->errorInfo());
            static::ex('DbQueryError', $msg, self::ERR_QUERY);
        }

        return $stmt;
    }

Such a simple wrapper already makes for some fairly easy-to-read code, e.g.:

try {
    $db->q("CREATE TABLE foo (id INT NOT NULL, name TEXT NOT NULL)");
    $db->q("INSERT INTO foo (id, name) VALUES (?, ?)", [1, "Foo One"]);
    $foo = $db->q("SELECT * FROM foo WHERE id = :id", [":id" => 1])->fetch();
    echo json_encode($foo) . "\n";
} catch (\Exception $e) {
    error_log($e);
    exit($e->getCode());
}

It also has the advantage of being very general: you can use it to run any statement that your database server handles.

In most cases though, we want to run select queries. Let's write a method that does just that:

    public function fetchAll (
        string $query,
        array $params = null,
        string $indexBy = null
    ): array {
        $stmt = $this->q($query, $params);

        $rows = [];
        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
            if (isset($indexBy)) {
                $this->indexRowByCol($rows, $row, $indexBy);
            } else {
                $rows[] = $row;
            }
        }

        return $rows;
    }

Notice that fetchAll takes the same arguments as q. This makes the codebase easier to read (repeated patterns are easier to recognize). Well, almost. There's an optional third argument to fetchAll that lets you index the result set by one of the columns, e.g.

$q = "INSERT INTO foo (name) VALUES ('Herp', 'Derp') RETURNING name";
$rows = $db->fetchAll($q, null, "name");
$derp = $rows["Derp"];

It only works if the index column has no null or duplicate values; typically you'd use this for id columns.

    private function indexRowByCol (array &$index, array $row, string $col) {
        if (!isset($row[$col])) {
            static::ex('DbIdx,DbIdxMissingCol',
                "No value at column '$col' for row " . json_encode($row),
                self::ERR_IDX_MISSING_COL);
        }
        if (isset($index[$row[$col]])) {
            static::ex('DbIdx,DbIdxDuplicateCol',
                "column $col isn't unique; found duplicate value " .
                    json_encode($row[$col]) . " in row " . json_encode($row),
                self::ERR_IDX_DUPLICATE_COL);
        }
        $index[$row[$col]] = $row;
    }

It's inneficient to lookup the index for every row, but then again so is fetching an entire result set into an array. fetchAll should only be used for small result sets. When expecting a lot of data, POD's fetch can be called directly to process rows one at a time, e.g.

 $stmt = $db->q("SELECT * FROM foobar");
 while ($row = $stmt->fetch()) {
     // do something with $row
 }

When you expect to get a single row (e.g. when querying by id), you can use the fetchOne method, which is just fetchAll plus sanity checks:

    public function fetchOne (string $query, array $params = null): array {
        $rows = $this->fetchAll($query, $params);
        if (count($rows) != 1) {
            $msg = "Expected exactly one row but got " . count($rows) .
                " from query $query";
            if (isset($params)) {
                $msg .= " w/ params " . json_encode($params);
            }
            if (count($rows)) {
                static::ex('DbUnexpectedRows,DbTooManyRows',
                    $msg,
                    self::ERR_TOO_MANY_ROWS);
            } else {
                static::ex('DbUnexpectedRows,DbNoRows,NotFound',
                    $msg,
                    self::ERR_NO_ROWS);
            }
        }

        return reset($rows);
    }

For insert, update, and delete queries, it's often useful to get a count of the affected rows.

    public function nAffected (string $query, array $params = null): int {
        return $this->q($query, $params)->rowCount();
    }

Calling something like

$n = $db->nAffected("DELETE FROM foo WHERE id = ?", [1]);

doesn't save much typing over e.g.

$n = $db->q("DELETE FROM foo WHERE id = ?", [1])->rowCount();

but it can be easier to read. It follows the same pattern as fetchAll and fetchOne.

Having these wrappers around query execution doesn't help with building queries, but it does encourage a consistent style of interaction with the database. Because we want to be writing SQL directly, we can't avoid concatenating strings. The best we can do is to give ourselves tools to do it conveniently, and to rely on codestyle / patterns of writing to keep things neat and clear.

One such tool is a helper that escapes column names. It always needs to be done; we might as well have a consistent way of doing it.

    /**
     * Escape column names for use in queries.
     * For example:
     *
     *      $cols = ['a', 'b', 'c'];
     *      $q = "INSERT INTO t (" . $this->cs($cols) . ") VALUES ...";
     *
     * If an element's key is not numeric, its key is used as the column name.
     * That is, the following gives the same result as the above example:
     *
     *      $cols = ['a', 'b' => 'foo', 'c'];
     *      $q = "INSERT INTO t (" . $this->cs($cols) . ") VALUES ...";
     */
    public function cs (array $cols): string {
        $cs = [];
        foreach ($cols as $k => $v) {
            $col = is_numeric($k) ? $v : $k;
            $cs[] = '"' . strtr($col, ['"' => '""']) . '"';
        }

        return implode(', ', $cs);
    }

This next tool is to help us write INSERT statements. Typically, when you're setting out to insert records into the database, you'll have them in key-value arrays. This method turns those arrays into a VALUES literal:

    /**
     * Generate parameters and their placeholders for use in VALUES commands.
     * For example,
     *
     *      $foobars = [
     *          ['foo' => 1, 'bar' => 2],
     *          ['foo' => 1, 'bar' => 3],
     *      ];
     *      $cols = ['foo', 'bar'];
     *      list ($vals, $params) = $this->vals($foobars, $cols);
     *      $q = "INSERT INTO foobars (" . $this->cs($cols) . ") VALUES " . $vals .
     *          " RETURNING id";
     *      $this->fetchAll($q, $params);
     *
     * Constant values can be set by using a key => value syntax for $cols.
     * That is, the following gives the same result as the above example:
     *
     *      $foobars = [
     *          ['bar' => 2],
     *          ['bar' => 3],
     *      ];
     *      $cols = ['foo' => 1, 'bar'];
     *      list ($vals, $params) = $this->vals($foobars, $cols);
     *      $q = "INSERT INTO foobars (" . $this->cs($cols) . ") VALUES " . $vals .
     *          " RETURNING id";
     *      $this->fetchAll($q, $params);
     */
    public function vals (array $rows, array $cols): array {
        $vals = [];
        $params = [];

        foreach ($rows as $row) {
            $vs = [];
            foreach ($cols as $k => $c) {
                if (is_numeric($k)) {
                    $vs[] = '?';
                    $params[] = isset($row[$c]) ? $row[$c] : null;
                } else {
                    $vs[] = $c;
                }
            }
            $vals[] = '(' . implode(', ', $vs) . ')';
        }

        return [implode(",\n", $vals), $params];
    }

That's all we need for now. You're welcome to add more tools to this kit along with style examples as you come accross recurring patterns.

    const ERR_QUERY             = 1969001;
    const ERR_IDX_MISSING_COL   = 1969002;
    const ERR_IDX_DUPLICATE_COL = 1969003;
    const ERR_TOO_MANY_ROWS     = 1969004;
    const ERR_NO_ROWS           = 1969005;
}

/* This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this
 * file, You can obtain one at http://mozilla.org/MPL/2.0/. */

wraugh / pdoqt

Maintainers

Details

README