514 Zeilen
13 KiB
PHP
514 Zeilen
13 KiB
PHP
<?php
|
|
/**
|
|
* Project:
|
|
* Contenido Content Management System
|
|
*
|
|
* Description:
|
|
* PDO MySQL database driver
|
|
*
|
|
* NOTE: Is still in development state, don't use it!
|
|
*
|
|
* Requirements:
|
|
* @con_php_req 5
|
|
*
|
|
*
|
|
* @package Contenido database
|
|
* @version 0.1.1
|
|
* @author Murat Purc <murat@purc.de>
|
|
* @copyright four for business AG <www.4fb.de>
|
|
* @license http://www.contenido.org/license/LIZENZ.txt
|
|
* @link http://www.4fb.de
|
|
* @link http://www.contenido.org
|
|
* @since file available since contenido release 4.8.15
|
|
*
|
|
* {@internal
|
|
* created 2011-02-28
|
|
* modified 2011-03-13, Murat Purc, Cleanup and documentation.
|
|
* modified 2011-04-22, Murat Purc, Connect to DB server without database and more
|
|
* readable connection settings.
|
|
*
|
|
* $Id$:
|
|
* }}
|
|
*
|
|
*/
|
|
|
|
if (!defined('CON_FRAMEWORK')) {
|
|
die('Illegal call');
|
|
}
|
|
|
|
|
|
class DB_Sql extends DB_Sql_Abstract
|
|
{
|
|
|
|
/**
|
|
* PDO connection
|
|
* @var PDO
|
|
*/
|
|
public $Query_ID;
|
|
|
|
/**
|
|
* PDO statement
|
|
* @var PDOStatement
|
|
*/
|
|
public $Link_ID;
|
|
|
|
protected $_aDataTypes = array(
|
|
PDO::PARAM_BOOL => 'bool',
|
|
PDO::PARAM_NULL => 'null',
|
|
PDO::PARAM_INT => 'int',
|
|
PDO::PARAM_STR => 'string',
|
|
PDO::PARAM_LOB => 'blob',
|
|
PDO::PARAM_STMT => 'statement'
|
|
);
|
|
|
|
|
|
/**
|
|
* Constructor.
|
|
*
|
|
* @param array $options Optional assoziative options
|
|
*/
|
|
public function __construct(array $options = array())
|
|
{
|
|
$options = array_merge($options, array(
|
|
'type' => 'pdo_mysql',
|
|
));
|
|
parent::__construct($options);
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::_connect()
|
|
*/
|
|
protected function _connect()
|
|
{
|
|
$aCon = $this->_aDbCfg['connection'];
|
|
if (!isset($aCon['host']) || !isset($aCon['user']) || !isset($aCon['password'])) {
|
|
$this->halt('MySQL _connect() Connection settings not complete');
|
|
return null;
|
|
}
|
|
|
|
$dsn = 'mysql:';
|
|
if (isset($aCon['database'])) {
|
|
$dsn .= 'dbname=' . $aCon['database'] . ';';
|
|
}
|
|
$dsn .= 'host=' . $aCon['host'];
|
|
|
|
if (!isset($aCon['driver_options']) || !is_array($aCon['driver_options'])) {
|
|
$aCon['driver_options'] = array();
|
|
}
|
|
|
|
try {
|
|
// Create a new PDO connection
|
|
$dbh = new PDO($dsn, $aCon['user'], $aCon['password'], $aCon['driver_options']);
|
|
} catch (PDOException $e) {
|
|
$this->Errno = $e->getCode();
|
|
$this->Error = $e->getMessage();
|
|
}
|
|
|
|
if (!$dbh) {
|
|
$this->halt('PDO_MySQL _connect() Failed');
|
|
return null;
|
|
}
|
|
|
|
if (isset($aCon['database'])) {
|
|
$this->Database = $aCon['database'];
|
|
}
|
|
$this->User = $aCon['user'];
|
|
|
|
return $dbh;
|
|
}
|
|
|
|
|
|
/**
|
|
* Discard the query result
|
|
*/
|
|
public function free()
|
|
{
|
|
if ($this->Query_ID) {
|
|
$this->Query_ID->closeCursor();
|
|
unset($this->Query_ID);
|
|
}
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::_query()
|
|
*/
|
|
protected function _query($sQuery)
|
|
{
|
|
$this->Query_ID = $this->Link_ID->query($sQuery);
|
|
$this->Row = 0;
|
|
$this->Errno = $this->_getErrorNumber();
|
|
$this->Error = $this->_getErrorMessage();
|
|
if (!$this->Query_ID) {
|
|
$this->halt($sQuery);
|
|
}
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::next_record()
|
|
*/
|
|
public function next_record()
|
|
{
|
|
$this->Record = $this->Query_ID->fetch(PDO::FETCH_BOTH);
|
|
$this->Row += 1;
|
|
$this->Errno = $this->_getErrorNumber();
|
|
$this->Error = $this->_getErrorMessage();
|
|
$stat = is_array($this->Record);
|
|
if (!$stat && $this->Auto_Free) {
|
|
$this->free();
|
|
}
|
|
return $stat;
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::seek()
|
|
*/
|
|
public function seek($pos = 0)
|
|
{
|
|
throw new Exception('seek not supported');
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::lock()
|
|
*/
|
|
public function lock($table, $mode = 'write')
|
|
{
|
|
if ($this->_bNolock == true) {
|
|
return true;
|
|
}
|
|
$query = 'LOCK TABLES ';
|
|
if (is_array($table)) {
|
|
while (list ($key, $value) = each($table)) {
|
|
if (!is_int($key)) {
|
|
// texts key are "read", "read local", "write", "low priority write"
|
|
$query .= "$value $key, ";
|
|
} else {
|
|
$query .= "$value $mode, ";
|
|
}
|
|
}
|
|
$query = substr($query, 0, -2);
|
|
} else {
|
|
$query .= "$table $mode";
|
|
}
|
|
$res = $this->query($query);
|
|
if (!$res) {
|
|
$this->halt('lock() failed.');
|
|
return 0;
|
|
}
|
|
return $res;
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::unlock()
|
|
*/
|
|
public function unlock()
|
|
{
|
|
if ($this->_bNolock == true) {
|
|
return true;
|
|
}
|
|
|
|
$res = $this->query('UNLOCK TABLES');
|
|
if (!$res) {
|
|
$this->halt('unlock() failed.');
|
|
}
|
|
return $res;
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::affected_rows()
|
|
*/
|
|
public function affected_rows()
|
|
{
|
|
return ($this->Query_ID) ? $this->Query_ID->rowCount() : 0;
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::num_rows()
|
|
*/
|
|
public function num_rows()
|
|
{
|
|
if ($this->Query_ID) {
|
|
// clone statement and get count by using fetchAll
|
|
$stmt = clone $this->Query_ID;
|
|
$res = $stmt->fetchAll();
|
|
return (is_array($res)) ? count($stmt->fetchAll()) : 0;
|
|
} else {
|
|
return 0;
|
|
}
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::num_fields()
|
|
*/
|
|
public function num_fields()
|
|
{
|
|
return count($this->Record / 2);
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::nextid()
|
|
*/
|
|
public function nextid($seq_name)
|
|
{
|
|
$this->connect();
|
|
|
|
if ($this->lock($this->Seq_Table)) {
|
|
/* get sequence number (locked) and increment */
|
|
$q = sprintf("SELECT nextid FROM `%s` WHERE seq_name = '%s'", $this->Seq_Table, $seq_name);
|
|
$stmt = $this->Link_ID->query($q);
|
|
$res = ($stmt) ? $stmt->fetch(PDO::FETCH_BOTH) : null;
|
|
|
|
/* No current value, make one */
|
|
if (!is_array($res)) {
|
|
$currentid = 0;
|
|
$q = sprintf("INSERT INTO `%s` VALUES('%s', %s)", $this->Seq_Table, $seq_name, $currentid);
|
|
$stmt = $this->Link_ID->query($q);
|
|
} else {
|
|
$currentid = $res['nextid'];
|
|
}
|
|
$nextid = $currentid + 1;
|
|
$q = sprintf("UPDATE `%s` set nextid = '%s' WHERE seq_name = '%s'", $this->Seq_Table, $nextid, $seq_name);
|
|
$stmt = $this->Link_ID->query($q);
|
|
$this->unlock();
|
|
} else {
|
|
$this->halt('Cannot lock ' . $this->Seq_Table . ' - has it been created?');
|
|
return 0;
|
|
}
|
|
return $nextid;
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::disconnect()
|
|
*/
|
|
public function disconnect()
|
|
{
|
|
$this->_debug("Debug: Disconnecting $this->Link_ID...");
|
|
// Destroy the PDO and PDOStatement object
|
|
$this->_removeConnection($this->Link_ID);
|
|
$this->Link_ID = null;
|
|
$this->Query_ID = null;
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::_metaData()
|
|
*/
|
|
protected function _metaData($table = '', $full = false)
|
|
{
|
|
$count = 0;
|
|
$id = 0;
|
|
$res = array();
|
|
|
|
/*
|
|
* Due to compatibility problems with Table we changed the behavior
|
|
* of metadata();
|
|
* depending on $full, metadata returns the following values:
|
|
*
|
|
* - full is false (default):
|
|
* $result[]:
|
|
* [0]["table"] table name
|
|
* [0]["name"] field name
|
|
* [0]["type"] field type
|
|
* [0]["len"] field length
|
|
* [0]["flags"] field flags
|
|
*
|
|
* - full is true
|
|
* $result[]:
|
|
* ["num_fields"] number of metadata records
|
|
* [0]["table"] table name
|
|
* [0]["name"] field name
|
|
* [0]["type"] field type
|
|
* [0]["len"] field length
|
|
* [0]["flags"] field flags
|
|
* ["meta"][field name] index of field named "field name"
|
|
* This last one could be used if you have a field name, but no index.
|
|
* Test: if (isset($result['meta']['myfield'])) { ...
|
|
*/
|
|
|
|
// if no $table specified, assume that we are working with a query
|
|
// result
|
|
if ($table) {
|
|
$this->connect();
|
|
$stmt = $this->Link_ID->query(sprintf("DESCRIBE `%s`", $table));
|
|
if (!$stmt) {
|
|
$this->halt('Metadata query failed.');
|
|
return false;
|
|
}
|
|
} else {
|
|
$stmt = $this->Query_ID;
|
|
if (!$stmt) {
|
|
$this->halt('No query specified.');
|
|
return false;
|
|
}
|
|
}
|
|
|
|
// loop thru the result and collect meta data
|
|
$res = array();
|
|
while ($rs = $stmt->fetch(PDO::FETCH_ASSOC)) {
|
|
$field = $this->_getFieldTypeDetails($rs['Type']);
|
|
$item = array();
|
|
$item['table'] = $table;
|
|
$item['name'] = $rs['Field'];
|
|
$item['type'] = $field['type'];
|
|
$item['len'] = $field['size'];
|
|
$item['flags'] = null; // @todo detect field flags
|
|
$res[$count] = $item;
|
|
if ($full) {
|
|
$res['meta'][$item['name']] = $count;
|
|
}
|
|
$count++;
|
|
}
|
|
if ($full) {
|
|
$res['num_fields'] = $count;
|
|
}
|
|
|
|
unset($stmt);
|
|
|
|
return $res;
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::escape()
|
|
*/
|
|
public function escape($sString)
|
|
{
|
|
$sResult = '';
|
|
$sResult = str_replace("'", "''", $sString);
|
|
// @todo adapt pdo quote method to own requirements
|
|
# if ($this->connect()) {
|
|
# $sResult = $this->Link_ID->quote($sString);
|
|
# }
|
|
return $sResult;
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::_tableNames()
|
|
*/
|
|
protected function _tableNames()
|
|
{
|
|
$return = array();
|
|
|
|
$stmt = $this->Link_ID->query('SHOW TABLES');
|
|
$i = 0;
|
|
while ($rs = $stmt->fetch(PDO::FETCH_NUM)) {
|
|
$return[$i]['table_name'] = $rs[0];
|
|
$return[$i]['tablespace_name'] = $this->Database;
|
|
$return[$i]['database'] = $this->Database;
|
|
$i ++;
|
|
}
|
|
|
|
unset($stmt);
|
|
|
|
return $return;
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::_serverInfo()
|
|
*/
|
|
protected function _serverInfo()
|
|
{
|
|
$arr['description'] = $this->Link_ID->getAttribute(PDO::ATTR_SERVER_INFO);
|
|
$arr['version'] = $this->Link_ID->getAttribute(PDO::ATTR_SERVER_VERSION);
|
|
return $arr;
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::_getErrorMessage()
|
|
*/
|
|
protected function _getErrorMessage()
|
|
{
|
|
$err = null;
|
|
if ($this->Query_ID) {
|
|
$err = $this->Query_ID->errorInfo();
|
|
} elseif ($this->Link_ID) {
|
|
$err = $this->Link_ID->errorInfo();
|
|
}
|
|
if (null !== $err && (int) $err[0] > 0) {
|
|
return $err[2];
|
|
}
|
|
}
|
|
|
|
|
|
/**
|
|
* @see DB_Sql_Abstract::_getErrorNumber()
|
|
*/
|
|
protected function _getErrorNumber()
|
|
{
|
|
$err = null;
|
|
if ($this->Query_ID) {
|
|
$err = $this->Query_ID->errorCode();
|
|
} elseif ($this->Link_ID) {
|
|
$err = $this->Link_ID->errorCode();
|
|
}
|
|
if (null !== $err && (int) $err[0] > 0) {
|
|
return $err[0];
|
|
}
|
|
}
|
|
|
|
|
|
protected function _getFieldTypeDetails($field)
|
|
{
|
|
$ret = array('type' => null, 'size' => null);
|
|
if (!$field) {
|
|
return $ret;
|
|
}
|
|
if (preg_match('/^([a-z].*)\(([0-9].*)\)/', $field, $matches)) {
|
|
$ret = array('type' => $matches[1], 'size' => (int) $matches[2]);
|
|
} else {
|
|
$ret['type'] = $field;
|
|
}
|
|
return $ret;
|
|
}
|
|
|
|
/**
|
|
* This method festches the current result set and returns the it as object or
|
|
* null if no result set is left. If optional param $sClassName is set, the
|
|
* result object is an instance of class $sClassName.
|
|
*
|
|
* @param string sClassName Optional the classname to instantiate.
|
|
* @return object|null
|
|
*/
|
|
public function getResultObject($sClassName = null)
|
|
{
|
|
$oResult = null;
|
|
|
|
if ($this->Link_ID && $this->Query_ID) {
|
|
if ($rs = $this->Query_ID->fetch(PDO::FETCH_ASSOC)) {
|
|
if ($sClassName == null) {
|
|
$oResult = (object) $rs;
|
|
} else {
|
|
$oResult = new $sClassName();
|
|
foreach ($rs as $key => $value) {
|
|
$oResult->{$key} = $value;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return $oResult;
|
|
}
|
|
|
|
public function getServerInfo() {
|
|
return '';
|
|
}
|
|
|
|
public function getClientEncoding() {
|
|
return '';
|
|
}
|
|
|
|
}
|