Skip to content

Instantly share code, notes, and snippets.

@PhrozenByte
Created January 18, 2016 18:36
Show Gist options
  • Save PhrozenByte/c7f67d8fc32eb4b2689b to your computer and use it in GitHub Desktop.
Save PhrozenByte/c7f67d8fc32eb4b2689b to your computer and use it in GitHub Desktop.
MPDO - a "magical" extension to PHP's PDO with features, a programmer could miss sorely when using PDO.
<?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]';
}
}
}
}
<?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);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment