WebSVN - PHPX - Diff - Rev 32 and 33 - /trunk/Db/Database.php


<?php

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

/**
 * Generic database model class using PDO (PHP Data Objects)
 *
 * @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 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 = '';
 
  public function __construct()
  {
    $this->_connection =
      new PDO($this->_dsn, $this->_username, $this->_password, $this->_options);
  }
 
  /**
   * 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;
  }
 
  /**
   * 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))
    {
      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}"] = $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 $values
   *   Associative array of column-value pairs
   * @param array|string $where
   *   Only the records matching this condition are updated
   * @return bool
   */
  public function update($tables, $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;
        }
      }
    }

    /* 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();
   
    $this->_resetLastInsertId();
   
    $result =& $this->_lastResult;
    $result =  $stmt->fetchAll();
   
    if (defined('DEBUG') && DEBUG > 1)
    {
      debug(array(
        '_lastSuccess' => $success,
        '_lastError'    => $errorInfo,
        '_lastResult'  => $result
      ));
    }
   
    return $success;
  }
 
  /**
   * Inserts a record into a table.<p>The AUTO_INCREMENT value of the inserted
   * row, if any (> 0), is stored in the {@link $lastInsertId} property of
   * the <code>Database</code> instance.</p>
   *
   * @param string $table
   *   Table name
   * @param array|string $values
   *   Associative array of column-value pairs, indexed array,
   *   or comma-separated list of values.  If <var>$values</var> is not
   *   an associative array, <var>$cols</var> must be passed if the
   *   values are not in column order (see below).
   * @param array|string $cols
   *   Indexed array, or comma-separated list of column names.
   *   Needs only be passed if <var>$values</var> is not an associative array
   *   and the values are not in column order (default: <code>null</code>);
   *   is ignored otherwise.  <strong>You SHOULD NOT rely on column order.</strong>
   * @return bool
   *   <code>true</code> if successful, <code>false</code> otherwise
   * @see PDOStatement::execute()
   */
  public function insert($table, $values, $cols = null)
  {
    if ($cols != null)
    {
      $cols = ' ('
            . (is_array($cols)
                ? implode(', ', array_map(array($this, 'escapeName'), $cols))
                : $cols) . ')';
    }
    else
    {
      $cols = '';
    }
 
    /* DEBUG */
    if (defined('DEBUG') && DEBUG > 2)
    {
      debug(array('values' => $values));
    }
 
    $params = array();
   
    if (is_array($values))
    {
      if ($this->_isAssociativeArray($values))
      {
        foreach ($values as $key => $condition)
        {
          $params[":{$key}"] = $condition;
        }
       
        $values = $this->_escapeValueArray($values);
       
        $cols = '';
        $values = 'SET ' . implode(', ', $values);
      }
      else
      {
        foreach ($values as &$value)
        {
          if (is_string($value))
          {
            $value = "'" . $value . "'";
          }
        }
         
        $values = ' VALUES (' . implode(', ', $values) . ')';
      }
    }
 
    /* TODO: Should escape table names with escapeName(), but what about aliases? */
    $query = "INSERT INTO {$table} {$cols} {$values}";
 
    $stmt = $this->prepare($query);
 
      /* 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();
   
    $this->_setLastInsertId();
   
    $result =& $this->_lastResult;
    $result =  $stmt->fetchAll();

    if (defined('DEBUG') && DEBUG > 1)
    {
      debug(array(
        '_lastSuccess'  => $success,
        '_lastError'    => $errorInfo,
        '_lastInsertId' => $this->_lastInsertId,
        '_lastResult'   => $result
      ));
    }
   
    return $success;
  }
   
  /**
   * Retrieves all rows from a table
   *
   * @param int[optional] $fetch_style
   * @param int[optional] $column_index
   * @param array[optional] $ctor_args
   * @return array
   * @see PDOStatement::fetchAll()
   */
  public function fetchAll($table, $fetch_style = null, $column_index = null, array $ctor_args = null)
  {
    /* NOTE: Cannot use table name as statement parameter */
    $stmt = $this->prepare("SELECT * FROM $table");
    $this->_lastSuccess = $stmt->execute();
 
    $this->_lastError = $stmt->errorInfo();
   
    $result =& $this->_lastResult;
   
    if (is_null($fetch_style))
    {
      $fetch_style = PDO::FETCH_ASSOC;
    }
   
    if (!is_null($ctor_args))
    {
      $result = $stmt->fetchAll($fetch_style, $column_index, $ctor_args);
    }
    else if (!is_null($column_index))
    {
      $result = $stmt->fetchAll($fetch_style, $column_index);
    }
    else if (!is_null($fetch_style))
    {
      $result = $stmt->fetchAll($fetch_style);
    }
    else
    {
      $result = $stmt->fetchAll();
    }
 
    return $result;
  }

  /**
   * Deletes one or more records
   *
   * @param string|array $tables
   *   Table name(s)
   * @param array|string $where
   *   Only the records matching this condition are deleted
   * @return bool
   * @see PDOStatement::execute()
   */
  public function delete($tables, $where = null)
  {
    if (!$tables)
    {
      throw new InvalidArgumentException('No table specified');
    }
         
    if (is_array($tables))
    {
      $tables = implode(', ', $tables);
    }
     
    $params = array();
   
    $query = "DELETE FROM {$tables}" . $this->_where($where);
   
    $stmt = $this->prepare($query);
   
    if ($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}"] = $condition;
        }
      }
    }

    /* DEBUG */
    if (defined('DEBUG') && DEBUG > 1)
    {
      debug(array(
        'query'  => $query,
        'params' => $params
      ));
    }
   
    $success =& $this->_lastSuccess;
    $success =  $stmt->execute($params);
   
    $result =& $this->_lastResult;
    $result =  $stmt->fetchAll();
   
    $errorInfo =& $this->_lastError;
    $errorInfo =  $stmt->errorInfo();
   
    if (defined('DEBUG') && DEBUG > 1)
    {
      debug(array(
        '_lastSuccess' => $success,
        '_lastError'   => $errorInfo,
        '_lastResult'  => $result
      ));
    }
   
    return $success;
  }
}

Rev 32	Rev 33
1	<?php	1	<?php
2		2
3	require_once __DIR__ . '/../global.inc';	3	require_once __DIR__ . '/../global.inc';
4		-
5	require_once __DIR__ . '/../AbstractModel.php';	4	require_once __DIR__ . '/../AbstractModel.php';
6		5
7	/**	6	/**
8	* Generic database model class using PDO (PHP Data Objects)	7	* Generic database model class using PDO (PHP Data Objects)
9	*	8	*
10	* @property-read array $lastError	9	* @property-read array $lastError
11	* Last error information of the database operation.	10	* Last error information of the database operation.
12	* See {@link PDOStatement::errorInfo()}.	11	* See {@link PDOStatement::errorInfo()}.
13	* @property-read string $lastInsertId	12	* @property-read string $lastInsertId
14	* ID of the last inserted row, or the last value from a sequence object,	13	* ID of the last inserted row, or the last value from a sequence object,
15	* depending on the underlying driver. May not be supported by all databases.	14	* depending on the underlying driver. May not be supported by all databases.
16	* @property-read array $lastResult	15	* @property-read array $lastResult
17	* Last result of the database operation	16	* Last result of the database operation
18	* @property-read boolean $lastSuccess	17	* @property-read boolean $lastSuccess
19	* Last success value of the database operation	18	* Last success value of the database operation
20	* @author Thomas Lahn	19	* @author Thomas Lahn
21	*/	20	*/
22	class Database extends AbstractModel	21	class Database extends AbstractModel
23	{	22	{
24	/* Access properties */	23	/* Access properties */
25		24
26	/**	25	/**
27	* DSN of the database	26	* DSN of the database
28	* @var string	27	* @var string
29	*/	28	*/
30	protected $_dsn = '';	29	protected $_dsn = '';
31		30
32	/**	31	/**
33	* Username to access the database	32	* Username to access the database
34	* @var string	33	* @var string
35	*/	34	*/
36	protected $_username;	35	protected $_username;
37		36
38	/**	37	/**
39	* Password to access the database	38	* Password to access the database
40	* @var string	39	* @var string
41	*/	40	*/
42	protected $_password;	41	protected $_password;
43		42
44	/**	43	/**
45	* PDO driver-specific options	44	* PDO driver-specific options
46	* @var array	45	* @var array
47	*/	46	*/
48	protected $_options = array();	47	protected $_options = array();
49		48
50	/**	49	/**
51	* Database-specific string to use for quoting a name or value	50	* Database-specific string to use for quoting a name or value
52	* left-hand side (for security reasons and to prevent a name	51	* left-hand side (for security reasons and to prevent a name
53	* from being parsed as a keyword).	52	* from being parsed as a keyword).
54	* @var string	53	* @var string
55	*/	54	*/
56	protected $_leftQuote = '';	55	protected $_leftQuote = '';
57		56
58	/**	57	/**
59	* Database-specific string to use for quoting a name or value	58	* Database-specific string to use for quoting a name or value
60	* left-hand side (for security reasons and to prevent a name	59	* left-hand side (for security reasons and to prevent a name
61	* from being parsed as a keyword).	60	* from being parsed as a keyword).
62	* @var string	61	* @var string
63	*/	62	*/
64	protected $_rightQuote = '';	63	protected $_rightQuote = '';
65		64
66	/* Status properties */	65	/* Status properties */
67		66
68	/**	67	/**
69	* Database connection	68	* Database connection
70	* @var PDO	69	* @var PDO
71	*/	70	*/
72	protected $_connection;	71	protected $_connection;
73		72
74	/**	73	/**
75	* Last success value of the database operation	74	* Last success value of the database operation
76	* @var boolean	75	* @var boolean
77	*/	76	*/
78	protected $_lastSuccess;	77	protected $_lastSuccess;
79		78
80	/**	79	/**
81	* Last error information of the database operation	80	* Last error information of the database operation
82	* @var array	81	* @var array
83	*/	82	*/
84	protected $_lastError;	83	protected $_lastError;
85		84
86	/**	85	/**
87	* Last result of the database operation	86	* Last result of the database operation
88	* @var array	87	* @var array
89	*/	88	*/
90	protected $_lastResult;	89	protected $_lastResult;
91		90
92	/**	91	/**
93	* ID of the last inserted row, or the last value from a sequence object,	92	* ID of the last inserted row, or the last value from a sequence object,
94	* depending on the underlying driver. May not be supported by all databases.	93	* depending on the underlying driver. May not be supported by all databases.
95	* @var string	94	* @var string
96	*/	95	*/
97	protected $_lastInsertId = '';	96	protected $_lastInsertId = '';
98		97
99	public function __construct()	98	public function __construct()
100	{	99	{
101	$this->_connection =	100	$this->_connection =
102	new PDO($this->_dsn, $this->_username, $this->_password, $this->_options);	101	new PDO($this->_dsn, $this->_username, $this->_password, $this->_options);
103	}	102	}
104		103
105	/**	104	/**
106	* Initiates a transaction	105	* Initiates a transaction
107	*	106	*
108	* @return bool	107	* @return bool
109	* @see PDO::beginTransaction()	108	* @see PDO::beginTransaction()
110	*/	109	*/
111	public function beginTransaction()	110	public function beginTransaction()
112	{	111	{
113	return $this->_connection->beginTransaction();	112	return $this->_connection->beginTransaction();
114	}	113	}
115		114
116	/**	115	/**
117	* Rolls back a transaction	116	* Rolls back a transaction
118	*	117	*
119	* @return bool	118	* @return bool
120	* @see PDO::rollBack()	119	* @see PDO::rollBack()
121	*/	120	*/
122	public function rollBack()	121	public function rollBack()
123	{	122	{
124	return $this->_connection->rollBack();	123	return $this->_connection->rollBack();
125	}	124	}
126		125
127	/**	126	/**
128	* Commits a transaction	127	* Commits a transaction
129	*	128	*
130	* @return bool	129	* @return bool
131	* @see PDO::commit()	130	* @see PDO::commit()
132	*/	131	*/
133	public function commit()	132	public function commit()
134	{	133	{
135	return $this->_connection->commit();	134	return $this->_connection->commit();
136	}	135	}
137		136
138	/**	137	/**
139	* Prepares a statement for execution with the database	138	* Prepares a statement for execution with the database
140	* @param string $query	139	* @param string $query
141	*/	140	*/
142	public function prepare($query, array $driver_options = array())	141	public function prepare($query, array $driver_options = array())
143	{	142	{
144	return $this->_connection->prepare($query, $driver_options);	143	return $this->_connection->prepare($query, $driver_options);
145	}	144	}
146		145
147	/**	146	/**
148	* Returns the ID of the last inserted row, or the last value from	147	* Returns the ID of the last inserted row, or the last value from
149	* a sequence object, depending on the underlying driver.	148	* a sequence object, depending on the underlying driver.
150	*	149	*
151	* @return int	150	* @return int
152	*/	151	*/
153	public function getLastInsertId()	152	public function getLastInsertId()
154	{	153	{
155	return $this->_lastInsertId;	154	return $this->_lastInsertId;
156	}	155	}
157		156
158	/**	157	/**
159	* Escapes a database name so that it can be used in a query.	158	* Escapes a database name so that it can be used in a query.
160	*	159	*
161	* @param string $name	160	* @param string $name
162	* The name to be escaped	161	* The name to be escaped
163	* @return string	162	* @return string
164	* The escaped name	163	* The escaped name
165	*/	164	*/
166	public function escapeName($name)	165	public function escapeName($name)
167	{	166	{
168	return $this->_leftQuote . $name . $this->_rightQuote;	167	return $this->_leftQuote . $name . $this->_rightQuote;
169	}	168	}
170		169
171	/**	170	/**
172	* Determines if an array is associative (has not all integer keys).	171	* Determines if an array is associative (has not all integer keys).
173	*	172	*
174	* @author	173	* @author
175	* Algorithm courtesy of squirrel, <http://stackoverflow.com/a/5969617/855543>.	174	* Algorithm courtesy of squirrel, <http://stackoverflow.com/a/5969617/855543>.
176	* @param array $a	175	* @param array $a
177	* @return boolean	176	* @return boolean
178	* <code>true</code> if <var>$a</var> is associative,	177	* <code>true</code> if <var>$a</var> is associative,
179	* <code>false</code> otherwise	178	* <code>false</code> otherwise
180	*/	179	*/
181	protected function _isAssociativeArray(array $a)	180	protected function _isAssociativeArray(array $a)
182	{	181	{
183	for (reset($a); is_int(key($a)); next($a));	182	for (reset($a); is_int(key($a)); next($a));
184	return !is_null(key($a));	183	return !is_null(key($a));
185	}	184	}
186		185
187	/**	186	/**
188	* Escapes an associative array so that its string representation can be used	187	* Escapes an associative array so that its string representation can be used
189	* as list with table or column aliases in a query.	188	* as list with table or column aliases in a query.
190	*	189	*
191	* This method does not actually escape anything; it only inserts the	190	* This method does not actually escape anything; it only inserts the
192	* 'AS' keyword. It should be overridden by inheriting methods.	191	* 'AS' keyword. It should be overridden by inheriting methods.
193	*	192	*
194	* NOTE: This method intentionally does not check whether the array actually	193	* NOTE: This method intentionally does not check whether the array actually
195	* is associative.	194	* is associative.
196	*	195	*
197	* @param array &$array	196	* @param array &$array
198	* The array to be escaped	197	* The array to be escaped
199	* @return array	198	* @return array
200	* The escaped array	199	* The escaped array
201	*/	200	*/
202	protected function _escapeAliasArray(array &$array)	201	protected function _escapeAliasArray(array &$array)
203	{	202	{
204	foreach ($array as $column => &$value)	203	foreach ($array as $column => &$value)
205	{	204	{
206	$quotedColumn = $column;	205	$quotedColumn = $column;
207	if (strpos($column, $this->_leftQuote) === false	206	if (strpos($column, $this->_leftQuote) === false
208	&& strpos($column, $this->_rightQuote) === false)	207	&& strpos($column, $this->_rightQuote) === false)
209	{	208	{
210	$quotedColumn = $this->_leftQuote . $column . $this->_rightQuote;	209	$quotedColumn = $this->_leftQuote . $column . $this->_rightQuote;
211	}	210	}
212		211
213	$value = $value . ' AS ' . $quotedColumn;	212	$value = $value . ' AS ' . $quotedColumn;
214	}	213	}
215		214
216	return $array;	215	return $array;
217	}	216	}
218		217
219	/**	218	/**
220	* @param array $a	219	* @param array $a
221	* @param string $prefix	220	* @param string $prefix
222	*/	221	*/
223	private static function _expand(array $a, $prefix)	222	private static function _expand(array $a, $prefix)
224	{	223	{
225	$a2 = array();	224	$a2 = array();
226		225
227	foreach ($a as $key => $value)	226	foreach ($a as $key => $value)
228	{	227	{
229	$a2[] = ':' . $prefix . ($key + 1);	228	$a2[] = ':' . $prefix . ($key + 1);
230	}	229	}
231		230
232	return $a2;	231	return $a2;
233	}	232	}
234		233
235	/**	234	/**
236	* Escapes an associative array so that its string representation can be used	235	* Escapes an associative array so that its string representation can be used
237	* as value list in a query.	236	* as value list in a query.
238	*	237	*
239	* This method should be overridden by inheriting classes to escape	238	* This method should be overridden by inheriting classes to escape
240	* column names as fitting for the database schema they support. It is	239	* column names as fitting for the database schema they support. It is
241	* strongly recommended that the overriding methods call this method with	240	* strongly recommended that the overriding methods call this method with
242	* an appropriate <var>$escape</var> parameter, pass all other parameters	241	* an appropriate <var>$escape</var> parameter, pass all other parameters
243	* on unchanged, and return its return value.	242	* on unchanged, and return its return value.
244	*	243	*
245	* NOTE: Intentionally does not check whether the array actually is associative!	244	* NOTE: Intentionally does not check whether the array actually is associative!
246	*	245	*
247	* @param array &$array	246	* @param array &$array
248	* The array to be escaped	247	* The array to be escaped
249	* @param string $suffix	248	* @param string $suffix
250	* The string to be appended to the column name for the value placeholder.	249	* The string to be appended to the column name for the value placeholder.
251	* The default is the empty string.	250	* The default is the empty string.
252	* @param array $escape	251	* @param array $escape
253	* The strings to use left-hand side (index 0) and right-hand side (index 1)	252	* The strings to use left-hand side (index 0) and right-hand side (index 1)
254	* of the column name. The default is the empty string, respectively.	253	* of the column name. The default is the empty string, respectively.
255	* @return array	254	* @return array
256	* The escaped array	255	* The escaped array
257	*/	256	*/
258	protected function _escapeValueArray(array &$array, $suffix = '')	257	protected function _escapeValueArray(array &$array, $suffix = '')
259	{	258	{
260	$result = array();	259	$result = array();
261		260
262	foreach ($array as $column => $value)	261	foreach ($array as $column => $value)
263	{	262	{
264	$op = '=';	263	$op = '=';
265	$placeholder = ":{$column}";	264	$placeholder = ":{$column}";
266		265
267	if (is_array($value) && $this->_isAssociativeArray($value))	266	if (is_array($value) && $this->_isAssociativeArray($value))
268	{	267	{
269	reset($value);	268	reset($value);
270	$op = ' ' . key($value) . ' ';	269	$op = ' ' . key($value) . ' ';
271		270
272	$value = $value[key($value)];	271	$value = $value[key($value)];
273	}	272	}
274		273
275	if (is_array($value))	274	if (is_array($value))
276	{	275	{
277	$placeholder = '(' . implode(',', self::_expand($value, $column)) . ')';	276	$placeholder = '(' . implode(', ', self::_expand($value, $column)) . ')';
278	}	277	}
279		278
280	$result[] = $this->_leftQuote . $column . $this->_rightQuote . "{$op}{$placeholder}{$suffix}";	279	$result[] = $this->_leftQuote . $column . $this->_rightQuote . "{$op}{$placeholder}{$suffix}";
281	}	280	}
282		281
283	return $result;	282	return $result;
284	}	283	}
285		284
286	/**	285	/**
287	* Constructs the WHERE part of a query	286	* Constructs the WHERE part of a query
288	*	287	*
289	* @param string\|array $where	288	* @param string\|array $where
290	* Condition	289	* Condition
291	* @param string $suffix	290	* @param string $suffix
292	* The string to be appended to the column name for the value placeholder,	291	* The string to be appended to the column name for the value placeholder,
293	* passed on to {@link Database::_escapeValueArray()}. The default is	292	* passed on to {@link Database::_escapeValueArray()}. The default is
294	* the empty string.	293	* the empty string.
295	* @return string	294	* @return string
296	* @see Database::_escapeValueArray()	295	* @see Database::_escapeValueArray()
297	*/	296	*/
298	protected function _where($where, $suffix = '')	297	protected function _where($where, $suffix = '')
299	{	298	{
300	if (!is_null($where))	299	if (!is_null($where))
301	{	300	{
302	if (is_array($where))	301	if (is_array($where))
303	{	302	{
304	if (count($where) < 1)	303	if (count($where) < 1)
305	{	304	{
306	return '';	305	return '';
307	}	306	}
308		307
309	if ($this->_isAssociativeArray($where))	308	if ($this->_isAssociativeArray($where))
310	{	309	{
311	$where = $this->_escapeValueArray($where, $suffix);	310	$where = $this->_escapeValueArray($where, $suffix);
312	}	311	}
313		312
314	$where = '(' . implode(') AND (', $where) . ')';	313	$where = '(' . implode(') AND (', $where) . ')';
315	}	314	}
316		315
317	return ' WHERE ' . $where;	316	return ' WHERE ' . $where;
318	}	317	}
319		318
320	return '';	319	return '';
321	}	320	}
322		321
323	/**	322	/**
324	* Selects data from one or more tables; the resulting records are stored	323	* Selects data from one or more tables; the resulting records are stored
325	* in the <code>result</code> property and returned as an associative array,	324	* in the <code>result</code> property and returned as an associative array,
326	* where the keys are the column (alias) names.	325	* where the keys are the column (alias) names.
327	*	326	*
328	* @param string\|array[string] $tables Table(s) to select from	327	* @param string\|array[string] $tables Table(s) to select from
329	* @param string\|array[string] $columns Column(s) to select from (optional)	328	* @param string\|array[string] $columns Column(s) to select from (optional)
330	* @param string\|array $where Condition (optional)	329	* @param string\|array $where Condition (optional)
331	* @param string $order Sort order (optional)	330	* @param string $order Sort order (optional)
332	* If provided, MUST start with ORDER BY or GROUP BY	331	* If provided, MUST start with ORDER BY or GROUP BY
333	* @param string $limit Limit (optional)	332	* @param string $limit Limit (optional)
334	* @param int $fetch_style	333	* @param int $fetch_style
335	* The mode that should be used for {@link PDOStatement::fetchAll()}.	334	* The mode that should be used for {@link PDOStatement::fetchAll()}.
336	* The default is {@link PDO::FETCH_ASSOC}.	335	* The default is {@link PDO::FETCH_ASSOC}.
337	* @return array	336	* @return array
338	* @see Database::prepare()	337	* @see Database::prepare()
339	* @see PDOStatement::fetchAll()	338	* @see PDOStatement::fetchAll()
340	*/	339	*/
341	public function select($tables, $columns = null, $where = null,	340	public function select($tables, $columns = null, $where = null,
342	$order = null, $limit = null, $fetch_style = PDO::FETCH_ASSOC)	341	$order = null, $limit = null, $fetch_style = PDO::FETCH_ASSOC)
343	{	342	{
344	if (is_null($columns))	343	if (is_null($columns))
345	{	344	{
346	$columns = array('*');	345	$columns = array('*');
347	}	346	}
348		347
349	if (is_array($columns))	348	if (is_array($columns))
350	{	349	{
351	if ($this->_isAssociativeArray($columns))	350	if ($this->_isAssociativeArray($columns))
352	{	351	{
353	$columns = $this->_escapeAliasArray($columns);	352	$columns = $this->_escapeAliasArray($columns);
354	}	353	}
355		354
356	$columns = implode(',', $columns);	355	$columns = implode(', ', $columns);
357	}	356	}
358		357
359	if (is_array($tables))	358	if (is_array($tables))
360	{	359	{
361	if ($this->_isAssociativeArray($columns))	360	if ($this->_isAssociativeArray($columns))
362	{	361	{
363	$columns = $this->_escapeAliasArray($columns);	362	$columns = $this->_escapeAliasArray($columns);
364	}	363	}
365		364
366	$tables = implode(',', $tables);	365	$tables = implode(', ', $tables);
367	}	366	}
368		367
369	$query = "SELECT {$columns} FROM {$tables}" . $this->_where($where);	368	$query = "SELECT {$columns} FROM {$tables}" . $this->_where($where);
370		369
371	if (!is_null($order))	370	if (!is_null($order))
372	{	371	{
373	if (is_array($order))	372	if (is_array($order))
374	{	373	{
375	$order = 'ORDER BY ' . implode(',', $order);	374	$order = 'ORDER BY ' . implode(', ', $order);
376	}	375	}
377		376
378	$query .= " $order";	377	$query .= " $order";
379	}	378	}
380		379
381	if (!is_null($limit))	380	if (!is_null($limit))
382	{	381	{
383	$query .= " LIMIT $limit";	382	$query .= " LIMIT $limit";
384	}	383	}
385		384
386	$stmt = $this->prepare($query);	385	$stmt = $this->prepare($query);
387		386
388	$params = array();	387	$params = array();
389		388
390	if (is_array($where) && $this->_isAssociativeArray($where))	389	if (is_array($where) && $this->_isAssociativeArray($where))
391	{	390	{
392	foreach ($where as $column => $condition)	391	foreach ($where as $column => $condition)
393	{	392	{
394	if (is_array($condition) && $this->_isAssociativeArray($condition))	393	if (is_array($condition) && $this->_isAssociativeArray($condition))
395	{	394	{
396	reset($condition);	395	reset($condition);
397	$condition = $condition[key($condition)];	396	$condition = $condition[key($condition)];
398		397
399	if (is_array($condition))	398	if (is_array($condition))
400	{	399	{
401	foreach (self::_expand($condition, $column) as $param_index => $param_name)	400	foreach (self::_expand($condition, $column) as $param_index => $param_name)
402	{	401	{
403	$params[$param_name] = $condition[$param_index];	402	$params[$param_name] = $condition[$param_index];
404	}	403	}
405	}	404	}
406	}	405	}
407	else	406	else
408	{	407	{
409	$params[":{$column}"] = $condition;	408	$params[":{$column}"] = $condition;
410	}	409	}
411	}	410	}
412	}	411	}
413		412
414	/* DEBUG */	413	/* DEBUG */
415	if (defined('DEBUG') && DEBUG > 1)	414	if (defined('DEBUG') && DEBUG > 1)
416	{	415	{
417	debug(array(	416	debug(array(
418	'query' => $query,	417	'query' => $query,
419	'params' => $params	418	'params' => $params
420	));	419	));
421	}	420	}
422		421
423	$success =& $this->_lastSuccess;	422	$success =& $this->_lastSuccess;
424	$success = $stmt->execute($params);	423	$success = $stmt->execute($params);
425		424
426	$errorInfo =& $this->_lastError;	425	$errorInfo =& $this->_lastError;
427	$errorInfo = $stmt->errorInfo();	426	$errorInfo = $stmt->errorInfo();
428		427
429	$result =& $this->_lastResult;	428	$result =& $this->_lastResult;
430	$result = $stmt->fetchAll($fetch_style);	429	$result = $stmt->fetchAll($fetch_style);
431		430
432	if (defined('DEBUG') && DEBUG > 1)	431	if (defined('DEBUG') && DEBUG > 1)
433	{	432	{
434	debug(array(	433	debug(array(
435	'_lastSuccess' => $success,	434	'_lastSuccess' => $success,
436	'_lastError' => $errorInfo,	435	'_lastError' => $errorInfo,
437	'_lastResult' => $result	436	'_lastResult' => $result
438	));	437	));
439	}	438	}
440		439
441	return $result;	440	return $result;
442	}	441	}
443		442
444	/**	443	/**
445	* Sets and returns the ID of the last inserted row, or the last value from	444	* Sets and returns the ID of the last inserted row, or the last value from
446	* a sequence object, depending on the underlying driver.	445	* a sequence object, depending on the underlying driver.
447	*	446	*
448	* @param string $name	447	* @param string $name
449	* Name of the sequence object from which the ID should be returned.	448	* Name of the sequence object from which the ID should be returned.
450	* @return string	449	* @return string
451	*/	450	*/
452	protected function _setLastInsertId($name = null)	451	protected function _setLastInsertId($name = null)
453	{	452	{
454	return ($this->_lastInsertId = $this->_connection->lastInsertId($name));	453	return ($this->_lastInsertId = $this->_connection->lastInsertId($name));
455	}	454	}
456		455
457	/**	456	/**
458	* Resets the the ID of the last inserted row, or the last value from	457	* Resets the the ID of the last inserted row, or the last value from
459	* a sequence object, depending on the underlying driver.	458	* a sequence object, depending on the underlying driver.
460	*	459	*
461	* @return string	460	* @return string
462	* The default value	461	* The default value
463	*/	462	*/
464	protected function _resetLastInsertId()	463	protected function _resetLastInsertId()
465	{	464	{
466	return ($this->_lastInsertId = '');	465	return ($this->_lastInsertId = '');
467	}	466	}
468		467
469	/**	468	/**
470	* Updates one or more records	469	* Updates one or more records
471	*	470	*
472	* @param string\|array $tables	471	* @param string\|array $tables
473	* Table name	472	* Table name
474	* @param array $values	473	* @param array $values
475	* Associative array of column-value pairs	474	* Associative array of column-value pairs
476	* @param array\|string $where	475	* @param array\|string $where
477	* Only the records matching this condition are updated	476	* Only the records matching this condition are updated
478	* @return bool	477	* @return bool
479	*/	478	*/
480	public function update($tables, $updates, $where = null)	479	public function update($tables, $updates, $where = null)
481	{	480	{
482	if (!$tables)	481	if (!$tables)
483	{	482	{
484	throw new InvalidArgumentException('No table specified');	483	throw new InvalidArgumentException('No table specified');
485	}	484	}
486		485
487	if (is_array($tables))	486	if (is_array($tables))
488	{	487	{
489	$tables = implode(',', $tables);	488	$tables = implode(', ', $tables);
490	}	489	}
491		490
492	if (!$updates)	491	if (!$updates)
493	{	492	{
494	throw new InvalidArgumentException('No values specified');	493	throw new InvalidArgumentException('No values specified');
495	}	494	}
496		495
497	$params = array();	496	$params = array();
498		497
499	if ($this->_isAssociativeArray($updates))	498	if ($this->_isAssociativeArray($updates))
500	{	499	{

Subversion Repositories PHPX

(root)/trunk/Db/Database.php - Rev 32 → 33