MPDO - a "magical" extension to PHP's PDO with features, a programmer could miss sorely when using PDO.

MPDO.php

<?php
namespace PhrozenByte\MPDO;

use PDO;
use PhrozenByte\MPDOStatement;
use RuntimeException;

/**
 * The MPDO class
 *
 * Represents a connection between PHP and a database server. The "M" in `MPDO`
 * stands for "Magic": {@see MPDO} is {@see PDO} with just some additional
 * "magic", i.e. features a programmer could miss sorely when using {@see PDO}.
 * Basically {@see MPDO} behaves exactly like {@see PDO}, refer to
 * {@see MPDO::prepareMagic()} to get an idea of what we mean with "magic".
 *
 * @author  Daniel Rudolf <http://daniel-rudolf.de>
 * @link    http://daniel-rudolf.de/oss/MPDO
 * @license GNU LGPL version 3.0 <http://opensource.org/licenses/LGPL-3.0>
 * @version 1.0
 */
class MPDO extends PDO
{
    /**
     * Represents an array as parameter data type
     *
     * @var int
     */
    const PARAM_ARRAY = 1000;

    /**
     * Represents an array of boolean values as parameter data type
     *
     * @see PDO::PARAM_BOOL
     *
     * @var int
     */
    const PARAM_ARRAY_BOOL = 1000 + PDO::PARAM_BOOL;

    /**
     * Represents an array of SQL NULL values as parameter data type
     *
     * @see PDO::PARAM_NULL
     *
     * @var int
     */
    const PARAM_ARRAY_NULL = 1000 + PDO::PARAM_NULL;

    /**
     * Represents an array of SQL INTEGER values as parameter data type
     *
     * @see PDO::PARAM_INT
     *
     * @var int
     */
    const PARAM_ARRAY_INT = 1000 + PDO::PARAM_INT;

    /**
     * Represents an array of SQL CHAR, VARCHAR, or other string values as
     * parameter data type
     *
     * @see PDO::PARAM_STR
     *
     * @var int
     */
    const PARAM_ARRAY_STR = 1000 + PDO::PARAM_STR;

    /**
     * Represents an array of SQL large object values as parameter data type
     *
     * @see PDO::PARAM_LOB
     *
     * @var int
     */
    const PARAM_ARRAY_LOB = 1000 + PDO::PARAM_LOB;

    /**
     * Represents an array of recordsets as parameter data type
     *
     * @see PDO::PARAM_STMT
     *
     * @var int
     */
    const PARAM_ARRAY_STMT = 1000 + PDO::PARAM_STMT;

    /**
     * Creates a MPDO instance representing a connection to a database
     *
     * Creates a MPDO instance to represent a connection to the requested
     * database. A MPDO instance differs to a regular PDO instance by the
     * {@see magicPrepare()} method and the use of {@see MPDOStatement}
     * statements. The error mode defaults to {@see PDO::ERRMODE_EXCEPTION},
     * the statement fetch mode to {@see PDO::FETCH_ASSOC}.
     *
     * @see PDO::__construct()
     *
     * @param string $dsn      The Data Source Name, or DSN, contains the
     *     information required to connect to the database.
     * @param string $username The user name for the DSN string. This argument
     *     is optional for some PDO drivers.
     * @param string $password The password for the DSN string. This argument
     *     is optional for some PDO drivers.
     * @param array  $options  A `key=>value` array of driver-specific
     *     connection options.
     */
    public function __construct($dsn, $username = null, $password = null, array $options = array())
    {
        $options += array(
            static::ATTR_ERRMODE => static::ERRMODE_EXCEPTION,
            static::ATTR_DEFAULT_FETCH_MODE => static::FETCH_ASSOC,
            static::ATTR_STATEMENT_CLASS => array('\PhrozenByte\MPDO\MPDOStatement', array($this))
        );
        parent::__construct($dsn, $username, $password, $options);
    }

    /**
     * Prepares a statement for execution with some additional "magic" and
     * returns a statement object
     *
     * Prepares an SQL statement to be executed by the
     * {@see MPDOStatement::execute()} method. Unlike {@see prepare()}, this
     * method allows you to use both named and question mark parameter markers
     * within the same SQL statement. Your markers don't have to represent data
     * literals, i.e. it's legal to pass arrays as parameter values. For
     * example, you can bind multiple values to a single parameter in the
     * `IN()` clause of an SQL statement. In order to avoid unexpected
     * behaviour, you must specify this parameter to be of the type
     * {@see MPDO::PARAM_ARRAY} (or `MPDO::PARAM_ARRAY_*`). You can achieve
     * this by passing the `$parameterDataTypes` argument, a associative
     * array, whose key corresponds to the parameter marker and value to the
     * desired data type. You're not limited to array data types, you can also
     * use the `$parameterDataTypes` argument as replacement for calling
     * {@see MPDOStatement::bindValue()}. The `$parameterDataTypes` argument
     * is conclusive, i.e. calling the {@see MPDOStatement::bindColumn()},
     * {@see MPDOStatement::bindParam()} or {@see MPDOStatement::bindValue()}
     * methods will throw an exception when using this parameter. Please note
     * that a single parameter of the type {@see MPDO::PARAM_ARRAY} (or
     * `MPDO::PARAM_ARRAY_*`) must always be executed with the exact same
     * number of values. If you want to change the number of values, you must
     * prepare the statement once again. You can achieve this by calling
     * {@see MPDOStatement::prepareMagic()}.
     *
     * @see MPDOStatement::execute()
     *
     * @param string     $statement          This must be a valid SQL
     *     statement template for the target database server.
     * @param array      $parameters         An array of values with as many
     *     elements as there are bound parameters in the SQL statement being
     *     executed. You cannot bind more values than specified; if more keys
     *     exist in `$parameters` than in the specified SQL statement,
     *     {@see MPDOStatement::execute()} will throw an exception.
     * @param array|null $parameterDataTypes A associative array describing
     *     the data types of bound parameters.
     * @param array      $driverOptions      This array holds one or more
     *     `key=>value` pairs to set attribute values for the
     *     {@see MPDOStatement} object that this method returns.
     *
     * @return MPDOStatement The prepared statement object.
     *
     * @throws RuntimeException|PDOException Thrown when the SQL statement
     *     could not be prepared.
     */
    public function prepareMagic(
        $statement,
        array $parameters = null,
        array $parameterDataTypes = null,
        array $driverOptions = array()
    ) {
        $magicStatement = $statement;
        $magicParameters = $magicParameterDataTypes = array();

        $parameterOrder = $multiValueParameters = array();

        if (!empty($parameters)) {
            $markerNum = 0;

            $callback = function ($matches) use (
                $statement,
                $parameters,
                $parameterDataTypes,
                &$magicParameters,
                &$magicParameterDataTypes,
                &$parameterOrder,
                &$multiValueParameters,
                &$markerNum
            ) {
                $marker = ($matches[1][0] === ':') ? substr($matches[1], 1) : $markerNum++;
                if (!array_key_exists($marker, $parameters)) {
                    // numeric params are 1-based (for whatever reason...)
                    throw new RuntimeException(
                        'Missing parameter for marker "' . (is_int($marker) ? ($marker + 1) : $marker)  . '" '
                        . ' in "' . $statement . '"'
                    );
                }

                $value = $parameters[$marker];
                $dataType = isset($parameterDataTypes[$marker]) ? $parameterDataTypes[$marker] : null;

                // remember actual parameter order
                $parameterOrder[] = $marker;

                $result = '?';
                if (is_array($value)) {
                    // throw a nicer exception to prevent PHP from throwing
                    // a unspecific "Array to string conversion" error
                    if (!static::isArrayParam($dataType)) {
                        $expectedDataType = ($dataType !== null) ? static::getDataTypeName($dataType) : 'string';
                        throw new RuntimeException(
                            'Invalid value of parameter "' . $marker . '": Data type mismatch: '
                            . 'Expecting ' . $expectedDataType . ', array given'
                        );
                    }

                    // remember number of elements for later value validation
                    $multiValueParameters[$marker] = count($value);

                    // flatten multi-value parameter data types and remember them
                    $dataType = ($dataType !== static::PARAM_ARRAY) ? ($dataType - static::PARAM_ARRAY) : null;
                    $magicParameterDataTypes = array_merge(
                        $magicParameterDataTypes,
                        array_fill(0, $multiValueParameters[$marker], $dataType)
                    );

                    // flatten multi-value parameter values and remember them for later execution
                    $magicParameters = array_merge($magicParameters, array_values($value));

                    // add markers for any additional value
                    $result .= str_repeat(',?', $multiValueParameters[$marker] - 1);
                } else {
                    // remember parameter data type
                    $magicParameterDataTypes[] = $dataType;

                    // remember parameter value for later execution
                    $magicParameters[] = $value;
                }

                return $result;
            };

            // replace all named parameter markers with a numeric pendant and
            // replace multi-value parameter markers with the appropriate number of parameter markers
            $magicStatement = preg_replace_callback('/(\?|:[a-zA-Z0-9_]+)/', $callback, $magicStatement);
        }

        // let PDO::prepare() prepare the statement
        $instance = parent::prepare($magicStatement, $driverOptions);
        $instance->magic($statement, $parameterDataTypes, $parameterOrder, $multiValueParameters);

        $instance->prepareExecution($magicParameters);
        if ($parameterDataTypes !== null) {
            $instance->setParamDataTypes($magicParameterDataTypes);
        }

        return $instance;
    }

    /**
     * Checks if the given parameter data type is an array data type
     *
     * @param int $dataType Data type of an paramater using one of the
     *     `MPDO::PARAM_*` (and `PDO::PARAM_*`) constants.
     *
     * @return bool Returns `TRUE` if the given data type is an array data
     *     type or `FALSE` otherwise.
     */
    public static function isArrayParam($dataType)
    {
        switch ($dataType) {
            case static::PARAM_ARRAY:
            case static::PARAM_ARRAY_STR:
            case static::PARAM_ARRAY_INT:
            case static::PARAM_ARRAY_BOOL:
            case static::PARAM_ARRAY_NULL:
            case static::PARAM_ARRAY_LOB:
            case static::PARAM_ARRAY_STMT:
                return true;
        }

        return false;
    }

    /**
     * Returns a string representation of the given parameter data type
     *
     * @param int $dataType Data type of an paramater using one of the
     *     `MPDO::PARAM_*` (and `PDO::PARAM_*`) constants.
     *
     * @return string String representation of the given parameter data type,
     *     mainly based on the return values of the `gettype()` function.
     */
    public static function getDataTypeName($dataType)
    {
        if (static::isArrayParam($dataType)) {
            return 'array';
        } else {
            switch ($dataType) {
                case static::PARAM_STR:
                    return 'string';

                case static::PARAM_INT:
                    return 'integer';

                case static::PARAM_BOOL:
                    return 'boolean';

                case static::PARAM_NULL:
                    return 'NULL';

                case static::PARAM_LOB:
                    return 'LOB';

                case static::PARAM_STMT:
                    return 'statement';

                default:
                    return '[unknown]';
            }
        }
    }
}

MPDOStatement.php

<?php
namespace PhrozenByte\MPDO;

use PDO;
use PDOStatement;
use PhrozenByte\MPDO\MPDO;
use RuntimeException;

/**
 * The MPDOStatement class
 *
 * Represents a prepared statement and, after the statement is executed, an
 * associated result set. The "M" in `MPDOStatement` stands for "Magic".
 * Please refer to the {@see MPDO} class for more information.
 *
 * @see MPDO
 *
 * @author  Daniel Rudolf <http://daniel-rudolf.de>
 * @link    http://daniel-rudolf.de/oss/MPDO
 * @license GNU LGPL version 3.0 <http://opensource.org/licenses/LGPL-3.0>
 * @version 1.0
 */
class MPDOStatement extends PDOStatement
{
    /**
     * The MPDO instance which created this prepared statement
     *
     * @var MPDO
     */
    protected $pdo;

    /**
     * The unmodified query string passed as `$statement` argument to
     * the {@see MPDO::prepareMagic()} method; used for {@see prepareMagic()}
     *
     * @var string|null
     */
    protected $originalQueryString;

    /**
     * The unmodified array passed as `$parameterDataTypes` argument to
     * the {@see MPDO::prepareMagic()} method; used for {@see prepareMagic()}
     *
     * @var int[]|null
     */
    protected $originalParameterDataTypes;

    /**
     * Provided that {@see MPDO::prepareMagic()} instanced this prepared
     * statement, this variable holds the elements bound to this statement for
     * later execution
     *
     * @var array|null
     */
    protected $parameters;

    /**
     * The order of parameter markers can differ between an `$parameters`
     * argument and their usage in the SQL statement. This array specifies the
     * order in which they are used in the SQL statement to sort `$parameters`
     * arrays appropriate.
     *
     * @var array
     */
    protected $parameterOrder = array();

    /**
     * Provided that {@see MPDO::prepareMagic()} instanced this prepared
     * statement, this variable holds the expected number of values of each
     * multi-value parameter bound to this statement
     *
     * @var int[]|null
     */
    protected $multiValueParameters = array();

    /**
     * A associative array describing the data types of bound parameters;
     * may be FALSE when {@see bindColumn()}, {@see bindParam()} or
     * {@see bindValue()} has been called previously
     *
     * @var int[]|null|false
     */
    protected $parameterDataTypes;

    /**
     * Creates a MPDOStatement instance representing a prepared SQL statement
     *
     * @param MPDO $pdo The MPDO instance which created this prepared statement.
     */
    protected function __construct(MPDO $pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * Stores various data to realize the "magic" of a MPDOStatement
     *
     * @see MPDO::prepareMagic()
     * @see prepareMagic()
     *
     * @param string $originalQueryString        The unmodified query string
     *     passed as `$statement` argument to the {@see MPDO::prepareMagic()}
     *     method.
     * @param array  $originalParameterDataTypes The unmodified array passed
     *     as `$parameterDataTypes` argument to the {@see MPDO::prepareMagic()}
     *     method.
     * @param array  $parameterOrder             The order of parameter markers
     *     can differ between an `$parameters` argument and their usage in the
     *     SQL statement. This array specifies the order in which they are
     *     used in the SQL statement to sort `$parameters` arrays appropriate.
     * @param array  $multiValueParameters       The expected number of values
     *     of each multi-value parameter bound to this statement.
     *
     * @return void
     */
    protected function magic(
        $originalQueryString,
        array $originalParameterDataTypes,
        array $parameterOrder,
        array $multiValueParameters
    ) {
        $this->originalQueryString = $originalQueryString;
        $this->originalParameterDataTypes = $originalParameterDataTypes;
        $this->parameterOrder = $parameterOrder;
        $this->multiValueParameters = $multiValueParameters;
    }

    /**
     * Stores the initial elements bound to this statement for later execution
     *
     * @see execute()
     *
     * @param array $parameters The elements bound to this statement for later
     *     execution.
     *
     * @return void
     */
    protected function prepareExecution(array $parameters)
    {
        $this->parameters = $parameters;
    }

    /**
     * Specifies a explicit data type for bound parameters
     *
     * This method is some kind of shortcut to {@see bindValue()}, allowing
     * to specify the data type for multiple parameters at once. The use of
     * this method is conclusive, i.e. you can't combine this method with a
     * call of {@see bindColumn()}, {@see bindParam()} or {@see bindValue()}.
     *
     * @param array $parameterDataTypes A associative array, whose key
     *     corresponds to the parameter marker and value to the desired data
     *     type of this parameter.
     *
     * @throws RuntimeException Thrown when the data type of one or more
     *     parameters have been specified already using one of the
     *     {@see bindColumn()}, {@see bindParam()} or {@see bindValue()}
     *     methods.
     */
    public function setParamDataTypes(array $parameterDataTypes)
    {
        if ($this->parameterDataTypes === false) {
            throw new RuntimeException('The data type of one or more parameters have been specified already');
        }
        $this->parameterDataTypes = $parameterDataTypes;
    }

    /**
     * Executes a prepared statement
     *
     * Execute the prepared statement. If you have prepared this statement
     * using {@see MPDO::prepareMagic()}, the first execution will use the
     * parameter values which you have passed there. Otherwise you have to pass
     * an array of parameter values.
     *
     * @param array|null $parameters An array of values with as many elements
     *     as there are bound parameters in the SQL statement. You cannot bind
     *     more values than specified; if more keys exist in `$parameters`
     *     than in the SQL statement, this method will throw an exception.
     *
     * @return bool Returns `TRUE` on success or `FALSE` on failure.
     *
     * @throws RuntimeException|PDOException Thrown when the SQL statement
     *     could not be prepared.
     */
    public function execute(array $parameters = null)
    {
        if ($parameters === null) {
            // fallback to parameters passed to MPDO::prepareMagic()
            if ($this->parameters !== null) {
                $parameters = $this->parameters;
                $this->parameters = null;
            }
        } elseif ($this->originalQueryString !== null) {
            // prepare $parameters the same way MPDO::prepareMagic() does
            $originalParameters = $parameters;
            $parameters = array();

            foreach ($parameterOrder as $marker) {
                if (!isset($originalParameters[$marker])) {
                    throw new RuntimeException('Missing value for parameter marker "' . $marker . '"');
                }

                $value = $originalParameters[$marker];
                $isArray = isset($this->multiValueParameters[$marker]);

                $dataType = null;
                if (isset($this->originalParameterDataTypes[$marker])) {
                    $dataType = $this->originalParameterDataTypes[$marker];
                }


                // throw a nicer exception to prevent PHP from throwing
                // a unspecific "Array to string conversion" error
                if ($isArray xor is_array($value)) {
                    if ($isArray) {
                        $expectedDataType = 'array';
                        $givenDataType = is_object($value) ? get_class($value) : gettype($value);
                    } else {
                        $className = get_class($this->pdo);
                        $expectedDataType = ($dataType !== null) ? $className::getDataTypeName($dataType) : 'string';
                        $givenDataType = 'array';
                    }
                    throw new RuntimeException(
                        'Invalid value of parameter "' . $marker . '": Data type mismatch: '
                        . 'Expecting ' . $expectedDataType . ', ' . $givenDataType . ' given'
                    );
                }

                if ($isArray) {
                    if (count($value) !== $this->multiValueParameters[$marker]) {
                        throw new RuntimeException(
                            'Invalid value of parameter "' . $marker . '": Expecting array with '
                            . $this->multiValueParameters[$marker] . ' values, ' . count($value) . ' given; '
                            . 'Try preparing a new statement using the '
                            . get_called_class() . '::prepareMagic() method'
                        );
                    }

                    // remember multi-value parameter values
                    $parameters = array_merge($parameters, array_values($value));
                } else {
                    // remember single parameter value
                    $parameters[] = $value;
                }
            }
        }

        // bind params by data type
        if (($this->parameterDataTypes !== null) && ($this->parameterDataTypes !== false)) {
            // test for numeric parameter markers
            // numeric params are 1-based (for whatever reason...)
            reset($parameters);
            $markerNum = ((key($parameters) === 0) || (key($parameters) === '0')) ? 1 : null;

            foreach ($parameters as $marker => $value) {
                $bindAs = ($markerNum !== null) ? $markerNum++ : $marker;
                $dataType = isset($this->parameterDataTypes[$marker]) ? $this->parameterDataTypes[$marker] : null;

                parent::bindValue($bindAs, $value, $dataType);
            }

            return parent::execute();
        } else {
            return parent::execute($parameters);
        }
    }

    /**
     * Returns a new statement object by preparing this statement again
     *
     * @see MPDO::prepare()
     *
     * @param array $driverOptions This array holds one or more `key=>value`
     *     pairs to set attribute values for the {@see MPDOStatement} object
     *     that this method returns.
     *
     * @return MPDOStatement New prepared statement object.
     *
     * @throws RuntimeException Thrown when trying to prepare a magic
     *     statement; use {@see prepareMagic()} instead.
     */
    public function prepare(array $driverOptions = array())
    {
        if ($this->originalQueryString !== null) {
            throw new RuntimeException('You can\'t pepare a magic statement as non-magic statement');
        }

        return $this->pdo->prepare($this->queryString, $driverOptions);
    }

    /**
     * Returns a new magic statement object by preparing this statement again
     *
     * @see MPDO::prepareMagic()
     *
     * @param array $parameters    An array of values with as many elements as
     *     there are bound parameters in the SQL statement being executed. You
     *     cannot bind more values than specified; if more keys exist in
     *     `$parameters` than in this SQL statement, {@see execute()} will
     *     throw an exception.
     * @param array $driverOptions This array holds one or more `key=>value`
     *     pairs to set attribute values for the {@see MPDOStatement} object
     *     that this method returns.
     *
     * @return MPDOStatement New prepared statement object.
     */
    public function prepareMagic(array $parameters = null, array $driverOptions = array())
    {
        $queryString = $this->originalQueryString;
        $parameterDataTypes = $this->originalParameterDataTypes;

        if ($queryString === null) {
            $queryString = $this->queryString;
            $parameterDataTypes = null;
        }

        return $this->pdo->prepareMagic($queryString, $parameters, $parameterDataTypes, $driverOptions);
    }

    /**
     * @see PDOStatement::bindColumn()
     */
    public function bindColumn($column, &$param, $type = null, $maxlen = null, $driverdata = null)
    {
        if (($this->parameterDataTypes !== null) && ($this->parameterDataTypes !== false)) {
            throw new RuntimeException('All parameters have been bound conclusivly already');
        }
        $this->parameterDataTypes = false;
        return parent::bindColumn($column, $param, $type, $maxlen, $driverdata);
    }

    /**
     * @see PDOStatement::bindParam()
     */
    public function bindParam($parameter, &$variable, $data_type = PDO::PARAM_STR, $length = null, $driver_options = null)
    {
        if (($this->parameterDataTypes !== null) && ($this->parameterDataTypes !== false)) {
            throw new RuntimeException('All parameters have been bound conclusivly already');
        }
        $this->parameterDataTypes = false;
        return parent::bindParam($parameter, $variable, $data_type, $length, $driver_options);
    }

    /**
     * @see PDOStatement::bindValue()
     */
    public function bindValue($parameter, $value, $data_type = PDO::PARAM_STR)
    {
        if (($this->parameterDataTypes !== null) && ($this->parameterDataTypes !== false)) {
            throw new RuntimeException('All parameters have been bound conclusivly already');
        }
        $this->parameterDataTypes = false;
        return parent::bindValue($parameter, $value, $data_type);
    }
}