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

Subversion Repositories PHPX

(root)/trunk/Db/Database.php - Rev 27 → 29