Subversion Repositories PHPX

<?php

namespace PointedEars\PHPX\Db;

require_once __DIR__ . '/../global.inc';

/**

 * Generic database model class using PDO (PHP Data Objects)

 *

 * @property-read PDO $connection

 *   Database connection.  Established on read access to this

 *   property if not yet established.

 * @property-read array $lastError

 *   Last error information of the database operation.

 *   See {@link PDOStatement::errorInfo()}.

 * @property-read string $lastInsertId

 *   ID of the last inserted row, or the last value from a sequence object,

 *   depending on the underlying driver. May not be supported by all databases.

 * @property-read array $lastResult

 *   Last result of the database operation

 * @property-read boolean $lastSuccess

 *   Last success value of the database operation

 * @author Thomas Lahn

 */

class Database extends \PointedEars\PHPX\AbstractModel

{

  /* Access properties */

  /**

   * DSN of the database

   * @var string

   */

  protected $_dsn = '';

  /**

   * Username to access the database

   * @var string

   */

  protected $_username;

  /**

   * Password to access the database

   * @var string

   */

  protected $_password;

  /**

   * PDO driver-specific options

   * @var array

   */

  protected $_options = array();

  /**

   * Database-specific string to use for quoting a name or value

   * left-hand side (for security reasons and to prevent a name

   * from being parsed as a keyword).

   * @var string

   */

  protected $_leftQuote = '';

  /**

   * Database-specific string to use for quoting a name or value

   * left-hand side (for security reasons and to prevent a name

   * from being parsed as a keyword).

   * @var string

   */

  protected $_rightQuote = '';

  /* Status properties */

  /**

   * Database connection

   * @var PDO

   */

  protected $_connection;

  /**

   * Last success value of the database operation

   * @var boolean

   */

  protected $_lastSuccess;

  /**

   * Last error information of the database operation

   * @var array

   */

  protected $_lastError;

  /**

   * Last result of the database operation

   * @var array

   */

  protected $_lastResult;

  /**

  * ID of the last inserted row, or the last value from a sequence object,

  * depending on the underlying driver. May not be supported by all databases.

  * @var string

  */

  protected $_lastInsertId = '';

  /**

   * Creates a new <code>Database</code> instance.

   *

   * Each of the parameters is optional and can also be given

   * by a protected property where the parameter name is preceded

   * by <code>_</code>.  Parameter values overwrite the default

   * property values.  It is recommended to use default property

   * values of inheriting classes except for small applications

   * and testing purposes.

   *

   * @param string $dsn

   * @param string $username

   * @param string $password

   * @param array $options

   * @see PDO::__construct()

   */

  public function __construct ($dsn = '', $username = null,

    $password = null, array $options = array())

  {

    if ($dsn !== '')

    {

      $this->_dsn = $dsn;

    }

    if ($username !== null)

    {

      $this->_username = $username;

    }

    if ($password !== null)

    {

      $this->_password = $password;

    }

    if ($options)

    {

      $this->_options = $options;

    }

  }

  /**

   * Reads the connection configuration for this database

   * from the configuration file, application/.config

   *

   * There must be an INI section named "database:" followed

   * by the value of the <code>$_dbname</code> property

   * containing keys and values for the properties of the

   * <code>Database</code> instance.  Except for the key

   * <code>dbname</code>, which allows for aliases, all

   * keys are ignored if the corresponding properties

   * were set.  That is, definitions in the class file

   * override those in the configuration file.

   *

   * @return array|boolean

   *   The configuration array if the configuration

   *   file could be read, <code>false</code> otherwise.

   */

  public function readConfig ()

  {

    /* FIXME: Configuration file path should not be hardcoded */

    $config = parse_ini_file('application/.config', true);

    if ($config !== false)

    {

      $section = 'database:' . $this->_dbname;

      if (isset($config[$section]))

      {

        $dbconfig = $config[$section];

        $options = array(

          'host', 'port', 'dbname', 'username', 'password', 'charset'

        );

        foreach ($options as $key)

        {

          $property = "_$key";

          if (isset($dbconfig[$key])

               && ($key == 'dbname'

                   || (property_exists($this, $property)

                       && $this->$property === null)))

          {

            $this->$property = $dbconfig[$key];

          }

        }

        return $config[$section];

      }

    }

    return $config;

  }

  /**

   * @return PDO

   */

  public function getConnection ()

  {

    if ($this->_connection === null)

    {

      $this->_connection =

        new \PDO($this->_dsn, $this->_username, $this->_password, $this->_options);

    }

    return $this->_connection;

  }

  /**

   * Creates a database according to the specified parameters

   *

   * Should be overwritten and called by inheriting classes.

   *

   * @param string $dsn

   *   Connection DSN (required; must not include the database

   *   name).

   * @param string $username = null

   *   Connection username.  The default is specified by the

   *   <code>$_username</code> property.  Note that creating

   *   the database usually requires a user with more privileges

   *   than the one accessing the database or its tables.

   * @param string $password = null

   *   Connection password.  The default is specified by the

   *   <code>$_password</code> property.

   * @param array? $options = null

   *   Connection options.  The default is specified by the

   *   <code>$_options</code> property.

   * @param string $spec = null

   *   Additional database specifications, like character encoding

   *   and collation.

   * @param boolean $force = false

   *   If a true-value, the database will be attempted to be

   *   created even if there is a database of the name specified

   *   by the <code>$_dbname</code> property.

   * @return int

   *   The number of rows affected by the CREATE DATABASE statement.

   * @see PDO::__construct()

   * @see PDO::exec()

   */

  public function create ($dsn, $username = null, $password = null,

    array $options = null, $dbspec = null, $force = false)

  {

    $connection = new \PDO($dsn,

      $username !== null ? $username : $this->_username,

      $password !== null ? $password : $this->_password,

      $options !== null ? $options : $this->_options);

    $query = 'CREATE DATABASE'

           . (!$force ? ' IF NOT EXISTS' : '')

           . ' ' . $this->escapeName($this->_dbname)

           . ($dbspec ? ' ' . $dbspec : '');

    return $connection->exec($query);

  }

  /**

   * Maps column meta-information to a column definition.

   *

   * Should be overwritten and called by inheriting classes.

   *

   * @todo

   * @param array $value

   * @param string $column_name

   * @return string

   */

  protected function _columnDef (array $data, $column_name)

  {

    $def = (isset($data['unsigned']) && $data['unsigned'] ? 'UNSIGNED ' : '')

         . $data['type']

         . (isset($data['not_null']) && $data['not_null'] ? ' NOT NULL'                     : ' NULL')

         . (isset($data['default'])  && $data['default']  ? " DEFAULT {$data['default']}"   : '')

         . (isset($data['auto_inc']) && $data['auto_inc'] ? ' AUTO_INCREMENT'               : '')

         . (isset($data['unique'])   && $data['unique']   ? ' UNIQUE KEY'                   : '')

         . (isset($data['primary'])  && $data['primary']  ? ' PRIMARY KEY'                  : '')

         . (isset($data['comment'])  && $data['comment']  ? " COMMENT '{$data['comment']}'" : '');

    return $this->escapeName($column_name) . ' ' . $def;

  }

  /**

   * Creates a database table according to the specified parameters

   *

   * @todo

   * @param string $name

   * @param array $columns

   * @param array $options = null

   * @return bool

   * @see PDOStatement::execute()

   */

  public function createTable ($name, array $columns, array $options = null)

  {

    $class = \get_class($this);

    $query = 'CREATE TABLE '

      . $this->escapeName($name)

      . '('

      . array_map(array($this, '_columnDef'), $columns, array_keys($columns)) . ')';

    $stmt = $this->prepare($query);

    /* DEBUG */

    if (defined('DEBUG') && DEBUG > 1)

    {

      debug(array(

        'query'  => $query,

      ));

    }

    $success =& $this->_lastSuccess;

    $success =  $stmt->execute();

    $errorInfo =& $this->_lastError;

    $errorInfo =  $stmt->errorInfo();

    $this->_resetLastInsertId();

    $result =& $this->_lastResult;

    $result =  $stmt->fetchAll();

    if (defined('DEBUG') && DEBUG > 1)

    {

      debug(array(

        '_lastSuccess' => $success,

        '_lastError'    => $errorInfo,

        '_lastResult'  => $result

      ));

    }

    return $success;

  }

  /**

   * Initiates a transaction

   *

   * @return bool

   * @see PDO::beginTransaction()

   */

  public function beginTransaction()

  {

    return $this->connection->beginTransaction();

  }

  /**

   * Rolls back a transaction

   *

   * @return bool

   * @see PDO::rollBack()

   */

  public function rollBack()

  {

    return $this->connection->rollBack();

  }

  /**

   * Commits a transaction

   *

   * @return bool

   * @see PDO::commit()

   */

  public function commit()

  {

    return $this->connection->commit();

  }

  /**

   * Prepares a statement for execution with the database

   * @param string $query

   */

  public function prepare($query, array $driver_options = array())

  {

    return $this->connection->prepare($query, $driver_options);

  }

  /**

   * Returns the ID of the last inserted row, or the last value from

   * a sequence object, depending on the underlying driver.

   *

   * @return int

   */

  public function getLastInsertId()

  {

    return $this->_lastInsertId;

  }

  /**

   * Returns the date of last modification of this database or

   * one of its tables.

   *

   * To be overridden by inheriting classes.

   *

   * @param string $table (optional)

   *   Table name.  If not provided, all tables of this database

   *   are considered.

   * @return int|null

   *   Timestamp of last modification, or <code>null</code> if

   *   unavailable.

   */

  public function getLastModified ($table = null)

  {

    return null;

  }

  /**

   * Escapes a database name so that it can be used in a query.

   *

   * @param string $name

   *   The name to be escaped

   * @return string

   *   The escaped name

   */

  public function escapeName($name)

  {

    return $this->_leftQuote . $name . $this->_rightQuote;

  }

  /**

   * Determines if an array is associative (has not all integer keys).

   *

   * @author

   *   Algorithm courtesy of squirrel, <http://stackoverflow.com/a/5969617/855543>.

   * @param array $a

   * @return boolean

   *   <code>true</code> if <var>$a</var> is associative,

   *   <code>false</code> otherwise

   */

  protected function _isAssociativeArray(array $a)

  {

    for (reset($a); is_int(key($a)); next($a));

    return !is_null(key($a));

  }

  /**

   * Escapes an associative array so that its string representation can be used

   * as list with table or column aliases in a query.

   *

   * This method does not actually escape anything; it only inserts the

   * 'AS' keyword.  It should be overridden by inheriting methods.

   *

   * NOTE: This method intentionally does not check whether the array actually

   * is associative.

   *

   * @param array &$array

   *   The array to be escaped

   * @return array

   *   The escaped array

   */

  protected function _escapeAliasArray(array &$array)

  {

    foreach ($array as $column => &$value)

    {

      $quotedColumn = $column;

      if (strpos($column, $this->_leftQuote) === false

         && strpos($column, $this->_rightQuote) === false)

      {

        $quotedColumn = $this->_leftQuote . $column . $this->_rightQuote;

      }

      $value = $value . ' AS ' . $quotedColumn;

    }

    return $array;

  }

  /**

   * @param array $a

   * @param string $prefix

   */

  private static function _expand(array $a, $prefix)

  {

    $a2 = array();

    foreach ($a as $key => $value)

    {

      $a2[] = ':' . $prefix . ($key + 1);

    }

    return $a2;

  }

  /**

   * Escapes an associative array so that its string representation can be used

   * as value list in a query.

   *

   * This method should be overridden by inheriting classes to escape

   * column names as fitting for the database schema they support.  It is

   * strongly recommended that the overriding methods call this method with

   * an appropriate <var>$escape</var> parameter, pass all other parameters

   * on unchanged, and return its return value.

   *

   * NOTE: Intentionally does not check whether the array actually is associative!

   *

   * @param array &$array

   *   The array to be escaped

   * @param string $suffix

   *   The string to be appended to the column name for the value placeholder.

   *   The default is the empty string.

   * @param array $escape

   *   The strings to use left-hand side (index 0) and right-hand side (index 1)

   *   of the column name.  The default is the empty string, respectively.

   * @return array

   *   The escaped array

   */

  protected function _escapeValueArray(array &$array, $suffix = '')

  {

    $result = array();

    foreach ($array as $column => $value)

    {

      $op = '=';

      $placeholder = ":{$column}";

      if (is_array($value) && $this->_isAssociativeArray($value))

      {

        reset($value);

        $op = ' ' . key($value) . ' ';

        $value = $value[key($value)];

      }

      if (is_array($value))

      {

        $placeholder = '(' . implode(', ', self::_expand($value, $column)) . ')';

      }

      $result[] = $this->_leftQuote . $column . $this->_rightQuote . "{$op}{$placeholder}{$suffix}";

    }

    return $result;

  }

  /**

   * Constructs the WHERE part of a query

   *

   * @param string|array $where

   *   Condition

   * @param string $suffix

   *   The string to be appended to the column name for the value placeholder,

   *   passed on to {@link Database::_escapeValueArray()}.  The default is

   *   the empty string.

   * @return string

   * @see Database::_escapeValueArray()

   */

  protected function _where($where, $suffix = '')

  {

    if (!is_null($where))

    {

      if (is_array($where))

      {

        if (count($where) < 1)

        {

          return '';

        }

        if ($this->_isAssociativeArray($where))

        {

          $where = $this->_escapeValueArray($where, $suffix);

        }

        $where = '(' . implode(') AND (', $where) . ')';

      }

      return ' WHERE ' . $where;

    }

    return '';

  }

  /**

   * Selects data from one or more tables; the resulting records are stored

   * in the <code>result</code> property and returned as an associative array,

   * where the keys are the column (alias) names.

   *

   * @param string|array[string] $tables Table(s) to select from

   * @param string|array[string] $columns Column(s) to select from (optional)

   * @param string|array $where Condition (optional)

   * @param string $order Sort order (optional)

   *   If provided, MUST start with ORDER BY or GROUP BY

   * @param string $limit Limit (optional)

   * @param int $fetch_style

   *   The mode that should be used for {@link PDOStatement::fetchAll()}.

   *   The default is {@link PDO::FETCH_ASSOC}.

   * @return array

   * @see Database::prepare()

   * @see PDOStatement::fetchAll()

   */

  public function select($tables, $columns = null, $where = null,

    $order = null, $limit = null, $fetch_style = \PDO::FETCH_ASSOC)

  {

    if (is_null($columns))

    {

      $columns = array('*');

    }

    if (is_array($columns))

    {

      if ($this->_isAssociativeArray($columns))

      {

        $columns = $this->_escapeAliasArray($columns);

      }

      $columns = implode(', ', $columns);

    }

    if (is_array($tables))

    {

      if ($this->_isAssociativeArray($columns))

      {

        $columns = $this->_escapeAliasArray($columns);

      }

      $tables = implode(', ', $tables);

    }

    $query = "SELECT {$columns} FROM {$tables}" . $this->_where($where);

    if (!is_null($order))

    {

      if (is_array($order))

      {

        $order = 'ORDER BY ' . implode(', ', $order);

      }

      $query .= " $order";

    }

    if (!is_null($limit))

    {

      $query .= " LIMIT $limit";

    }

    $stmt = $this->prepare($query);

    $params = array();

    if (is_array($where) && $this->_isAssociativeArray($where))

    {

      /* FIXME: Export and reuse this */

      foreach ($where as $column => $condition)

      {

        /* TODO: Also handle function calls as keys */

        if (is_array($condition) && $this->_isAssociativeArray($condition))

        {

          reset($condition);

          $condition = $condition[key($condition)];

          if (is_array($condition))

          {

            foreach (self::_expand($condition, $column) as $param_index => $param_name)

            {

              $params[$param_name] = $condition[$param_index];

            }

          }

        }

        else

        {

          $params[":{$column}"] = $condition;

        }

      }

    }

    /* DEBUG */

    if (defined('DEBUG') && DEBUG > 1)

    {

      debug(array(

        'query'  => $query,

        'params' => $params

      ));

    }

    $success =& $this->_lastSuccess;

    $success =  $stmt->execute($params);

    $errorInfo =& $this->_lastError;

    $errorInfo =  $stmt->errorInfo();

    $result =& $this->_lastResult;

    $result =  $stmt->fetchAll($fetch_style);

    if (defined('DEBUG') && DEBUG > 1)

    {

      debug(array(

        '_lastSuccess' => $success,

        '_lastError'   => $errorInfo,

        '_lastResult'  => $result

      ));

    }

    return $result;

  }

  /**

   * Sets and returns the ID of the last inserted row, or the last value from

   * a sequence object, depending on the underlying driver.

   *

   * @param string $name

   *   Name of the sequence object from which the ID should be returned.

   * @return string

   */

  protected function _setLastInsertId($name = null)

  {

    return ($this->_lastInsertId = $this->connection->lastInsertId($name));

  }

  /**

   * Resets the the ID of the last inserted row, or the last value from

   * a sequence object, depending on the underlying driver.

   *

   * @return string

   *   The default value

   */

  protected function _resetLastInsertId()

  {

    return ($this->_lastInsertId = '');

  }

  /**

   * Updates one or more records

   *

   * @param string|array $tables

   *   Table name

   * @param array $updates

   *   Associative array of column-value pairs

   * @param array|string $where

   *   Only the records matching this condition are updated

   * @return bool

   * @see PDOStatement::execute()

   */

  public function update ($tables, array $updates, $where = null)

  {

    if (!$tables)

    {

      throw new InvalidArgumentException('No table specified');

    }

    if (is_array($tables))

    {

      $tables = implode(', ', $tables);

    }

    if (!$updates)

    {

      throw new InvalidArgumentException('No values specified');

    }

    $params = array();

    if ($this->_isAssociativeArray($updates))

    {

      foreach ($updates as $key => $condition)

      {

        $params[":{$key}"] = $condition;

      }

    }

    $updates = implode(', ', $this->_escapeValueArray($updates));

    /* TODO: Should escape table names with escapeName(), but what about aliases? */

    $query = "UPDATE {$tables} SET {$updates}" . $this->_where($where, '2');

    $stmt = $this->prepare($query);

    if (is_array($where) && $this->_isAssociativeArray($where))

    {

      foreach ($where as $column => $condition)

      {

        if (is_array($condition) && $this->_isAssociativeArray($condition))

        {

          reset($condition);

          $condition = $condition[key($condition)];

          if (is_array($condition))

          {

            foreach (self::_expand($condition, $column) as $param_index => $param_name)

            {

              $params[$param_name] = $condition[$param_index];

            }

          }

        }

        else

        {

          $params[":{$column}2"] = $condition;

        }

      }

    }