6,6 → 6,9 |
/** |
* 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()}. |
21,25 → 24,25 |
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 |
53,7 → 56,7 |
* @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 |
61,15 → 64,15 |
* @var string |
*/ |
protected $_rightQuote = ''; |
|
|
/* Status properties */ |
|
|
/** |
* Database connection |
* @var PDO |
*/ |
protected $_connection; |
|
|
/** |
* Last success value of the database operation |
* @var boolean |
81,7 → 84,7 |
* @var array |
*/ |
protected $_lastError; |
|
|
/** |
* Last result of the database operation |
* @var array |
94,7 → 97,7 |
* @var string |
*/ |
protected $_lastInsertId = ''; |
|
|
/** |
* Creates a new <code>Database</code> instance. |
* |
117,27 → 120,38 |
{ |
$this->_dsn = $dsn; |
} |
|
|
if ($username !== null) |
{ |
$this->_username = $username; |
} |
|
|
if ($password !== null) |
{ |
$this->_password = $password; |
} |
|
|
if ($options) |
{ |
$this->_options = $options; |
} |
|
$this->_connection = |
new PDO($this->_dsn, $this->_username, $this->_password, $this->_options); |
} |
|
|
/** |
* @return PDO |
*/ |
public function getConnection () |
{ |
if ($this->_connection === null) |
{ |
$this->_connection = |
new PDO($this->_dsn, $this->_username, $this->_password, $this->_options); |
} |
|
return $this->_connection; |
} |
|
/** |
* Initiates a transaction |
* |
* @return bool |
145,9 → 159,9 |
*/ |
public function beginTransaction() |
{ |
return $this->_connection->beginTransaction(); |
return $this->connection->beginTransaction(); |
} |
|
|
/** |
* Rolls back a transaction |
* |
156,9 → 170,9 |
*/ |
public function rollBack() |
{ |
return $this->_connection->rollBack(); |
return $this->connection->rollBack(); |
} |
|
|
/** |
* Commits a transaction |
* |
167,9 → 181,9 |
*/ |
public function commit() |
{ |
return $this->_connection->commit(); |
return $this->connection->commit(); |
} |
|
|
/** |
* Prepares a statement for execution with the database |
* @param string $query |
176,9 → 190,9 |
*/ |
public function prepare($query, array $driver_options = array()) |
{ |
return $this->_connection->prepare($query, $driver_options); |
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. |
189,7 → 203,7 |
{ |
return $this->_lastInsertId; |
} |
|
|
/** |
* Escapes a database name so that it can be used in a query. |
* |
202,7 → 216,7 |
{ |
return $this->_leftQuote . $name . $this->_rightQuote; |
} |
|
|
/** |
* Determines if an array is associative (has not all integer keys). |
* |
218,7 → 232,7 |
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. |
244,10 → 258,10 |
{ |
$quotedColumn = $this->_leftQuote . $column . $this->_rightQuote; |
} |
|
|
$value = $value . ' AS ' . $quotedColumn; |
} |
|
|
return $array; |
} |
|
258,15 → 272,15 |
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. |
293,31 → 307,31 |
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 |
* |
340,18 → 354,18 |
{ |
return ''; |
} |
|
|
if ($this->_isAssociativeArray($where)) |
{ |
$where = $this->_escapeValueArray($where, $suffix); |
} |
|
|
$where = '(' . implode(') AND (', $where) . ')'; |
} |
|
|
return ' WHERE ' . $where; |
} |
|
|
return ''; |
} |
|
380,7 → 394,7 |
{ |
$columns = array('*'); |
} |
|
|
if (is_array($columns)) |
{ |
if ($this->_isAssociativeArray($columns)) |
409,7 → 423,7 |
{ |
$order = 'ORDER BY ' . implode(', ', $order); |
} |
|
|
$query .= " $order"; |
} |
|
417,11 → 431,11 |
{ |
$query .= " LIMIT $limit"; |
} |
|
|
$stmt = $this->prepare($query); |
|
$params = array(); |
|
|
if (is_array($where) && $this->_isAssociativeArray($where)) |
{ |
/* FIXME: Export and reuse this */ |
432,7 → 446,7 |
{ |
reset($condition); |
$condition = $condition[key($condition)]; |
|
|
if (is_array($condition)) |
{ |
foreach (self::_expand($condition, $column) as $param_index => $param_name) |
456,16 → 470,16 |
'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( |
474,7 → 488,7 |
'_lastResult' => $result |
)); |
} |
|
|
return $result; |
} |
|
488,7 → 502,7 |
*/ |
protected function _setLastInsertId($name = null) |
{ |
return ($this->_lastInsertId = $this->_connection->lastInsertId($name)); |
return ($this->_lastInsertId = $this->connection->lastInsertId($name)); |
} |
|
/** |
502,7 → 516,7 |
{ |
return ($this->_lastInsertId = ''); |
} |
|
|
/** |
* Updates one or more records |
* |
520,12 → 534,12 |
{ |
throw new InvalidArgumentException('No table specified'); |
} |
|
|
if (is_array($tables)) |
{ |
$tables = implode(', ', $tables); |
} |
|
|
if (!$updates) |
{ |
throw new InvalidArgumentException('No values specified'); |
532,7 → 546,7 |
} |
|
$params = array(); |
|
|
if ($this->_isAssociativeArray($updates)) |
{ |
foreach ($updates as $key => $condition) |
540,14 → 554,14 |
$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) |
556,7 → 570,7 |
{ |
reset($condition); |
$condition = $condition[key($condition)]; |
|
|
if (is_array($condition)) |
{ |
foreach (self::_expand($condition, $column) as $param_index => $param_name) |
580,18 → 594,18 |
'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( |
600,10 → 614,10 |
'_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 |
638,15 → 652,15 |
{ |
$cols = ''; |
} |
|
|
/* DEBUG */ |
if (defined('DEBUG') && DEBUG > 2) |
{ |
debug(array('values' => $values)); |
} |
|
|
$params = array(); |
|
|
if (is_array($values)) |
{ |
if ($this->_isAssociativeArray($values)) |
655,9 → 669,9 |
{ |
$params[":{$key}"] = $condition; |
} |
|
|
$values = $this->_escapeValueArray($values); |
|
|
$cols = ''; |
$values = 'SET ' . implode(', ', $values); |
} |
670,16 → 684,16 |
$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) |
{ |
688,15 → 702,15 |
'params' => $params |
)); |
} |
|
|
$success =& $this->_lastSuccess; |
$success = $stmt->execute($params); |
|
|
$errorInfo =& $this->_lastError; |
$errorInfo = $stmt->errorInfo(); |
|
|
$this->_setLastInsertId(); |
|
|
$result =& $this->_lastResult; |
$result = $stmt->fetchAll(); |
|
709,10 → 723,10 |
'_lastResult' => $result |
)); |
} |
|
|
return $success; |
} |
|
|
/** |
* Retrieves all rows from a table |
* |
727,16 → 741,16 |
/* 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); |
753,7 → 767,7 |
{ |
$result = $stmt->fetchAll(); |
} |
|
|
return $result; |
} |
|
773,18 → 787,18 |
{ |
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) |
793,7 → 807,7 |
{ |
reset($condition); |
$condition = $condition[key($condition)]; |
|
|
if (is_array($condition)) |
{ |
foreach (self::_expand($condition, $column) as $param_index => $param_name) |
817,16 → 831,16 |
'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( |
835,7 → 849,7 |
'_lastResult' => $result |
)); |
} |
|
|
return $success; |
} |
} |