PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_OBJ,
PDO::ATTR_CASE=>PDO::CASE_LOWER,
PDO::ATTR_AUTOCOMMIT=>true
)
);
self::$pdo->exec("SET NAMES UTF8");
// Assign the singleton
self::$instance=new self;
}
// We don't access this directly, only via query(), exec() and prepare()
public static function getInstance() {
if (self::$pdo instanceof PDO) {
return self::$instance;
} else {
trigger_error('WT_DB::createInstance() must be called before WT_DB::getInstance().', E_USER_ERROR);
}
}
public static function isConnected() {
return (self::$pdo instanceof PDO);
}
//////////////////////////////////////////////////////////////////////////////
// LOGGING
// Keep a log of the statements executed using this connection
//////////////////////////////////////////////////////////////////////////////
private static $log=array();
// Add an entry to the log
public static function logQuery($query, $rows, $microtime, $bind_variables) {
if (WT_DEBUG_SQL) {
// Full logging
// Trace
$trace=debug_backtrace();
array_shift($trace);
array_shift($trace);
foreach ($trace as $n=>$frame) {
if (isset($frame['file']) && isset($frame['line'])) {
$trace[$n]=basename($frame['file']).':'.$frame['line'].' '.$frame['function'].'('./*implode(',', $frame['args']).*/')';
} else {
unset($trace[$n]);
}
}
$stack=''.(count(self::$log)+1).'';
// Bind variables
$query2='';
foreach ($bind_variables as $key=>$value) {
if (is_null($value)) {
$bind_variables[$key]='[NULL]';
}
}
foreach (str_split(htmlspecialchars($query)) as $char) {
if ($char=='?') {
$query2.=''.$char.'';
} else {
$query2.=$char;
}
}
// Highlight embedded literal strings.
if (preg_match('/[\'"]/', $query)) {
$query2=''.$query2.'';
}
// Highlight slow queries
$microtime*=1000; // convert to milliseconds
if ($microtime>1000) {
$microtime=sprintf('%.3f', $microtime);
} elseif ($microtime>100) {
$microtime=sprintf('%.3f', $microtime);
} elseif ($microtime>1) {
$microtime=sprintf('%.3f', $microtime);
} else {
$microtime=sprintf('%.3f', $microtime);
}
self::$log[]="| {$stack} | {$query2} | {$rows} | {$microtime} |
";
} else {
// Just log query count for statistics
self::$log[]=true;
}
}
// Total number of queries executed, for the page statistics
public static function getQueryCount() {
return count(self::$log);
}
// Display the query log as a table, for debugging
public static function getQueryLog() {
$html='| # | Query | Rows | Time (ms) |
|---|
'.implode('', self::$log).'
';
self::$log=array();
return $html;
}
//////////////////////////////////////////////////////////////////////////////
// INTERROGATE DATA DICTIONARY
//////////////////////////////////////////////////////////////////////////////
public static function table_exists($table) {
global $DBNAME;
switch (self::$pdo->getAttribute(PDO::ATTR_DRIVER_NAME)) {
case 'mysql':
// Mysql 4.x does not support the information schema
default:
// Catch-all for other databases
try {
WT_DB::prepare("SELECT 1 FROM {$table}")->fetchOne();
return true;
} catch (PDOException $ex) {
return false;
}
}
}
public static function column_exists($table, $column) {
global $DBNAME;
switch (self::$pdo->getAttribute(PDO::ATTR_DRIVER_NAME)) {
case 'mysql':
// Mysql 4.x does not support the information schema
default:
// Catch-all for other databases
try {
WT_DB::prepare("SELECT {$column} FROM {$table}")->fetchOne();
return true;
} catch (PDOException $ex) {
return false;
}
}
}
//////////////////////////////////////////////////////////////////////////////
// FUNCTIONALITY ENHANCEMENTS
//////////////////////////////////////////////////////////////////////////////
// The native quote() function does not convert PHP nulls to DB nulls
public static function quote($string, $parameter_type=PDO::PARAM_STR) {
if (is_null($string)) {
return 'NULL';
} else {
return self::$pdo->quote($string, $parameter_type);
}
}
// Add logging to query()
public static function query($statement, $parameter_type= PDO::PARAM_STR) {
$statement=str_replace('##', WT_TBLPREFIX, $statement);
$start=microtime(true);
$result=self::$pdo->query($statement, $parameter_type);
$end=microtime(true);
self::logQuery($statement, count($result), $end-$start, array());
return $result;
}
// Add logging to exec()
public static function exec($statement) {
$statement=str_replace('##', WT_TBLPREFIX, $statement);
$start=microtime(true);
$result=self::$pdo->exec($statement);
$end=microtime(true);
self::logQuery($statement, $result, $end-$start, array());
return $result;
}
// Add logging/functionality to prepare()
public static function prepare($statement) {
if (!self::$pdo instanceof PDO) {
throw new PDOException("No Connection Established");
}
$statement=str_replace('##', WT_TBLPREFIX, $statement);
return new WT_DBStatement(self::$pdo->prepare($statement));
}
// Map all other functions onto the base PDO object
public function __call($function, $params) {
return call_user_func_array(array(self::$pdo, $function), $params);
}
//////////////////////////////////////////////////////////////////////////////
// Create/update tables, indexes, etc.
//////////////////////////////////////////////////////////////////////////////
public static function updateSchema($schema_dir, $schema_name, $target_version) {
try {
$current_version=(int)get_site_setting($schema_name);
} catch (PDOException $e) {
// During initial installation, this table won't exist.
// It will only be a problem if we can't subsequently create it.
$current_version=0;
}
while ($current_version<$target_version) {
$next_version=$current_version+1;
require $schema_dir.'db_schema_'.$current_version.'_'.$next_version.'.php';
// The updatescript should update the version or throw an exception
$current_version=(int)get_site_setting($schema_name);
if ($current_version!=$next_version) {
die("Internal error while updating {$schema_name} to {$next_version}");
}
}
}
}
class WT_DBStatement {
//////////////////////////////////////////////////////////////////////////////
// CONSTRUCTION
// Decorate a PDOStatement object.
// See http://en.wikipedia.org/wiki/Decorator_pattern
//////////////////////////////////////////////////////////////////////////////
private $pdostatement=null;
// Keep track of calls to execute(), so we can do it automatically
private $executed=false;
// Keep a copy of the bind variables, for logging
private $bind_variables=array();
// Our constructor just takes a copy of the object to be decorated
public function __construct(PDOStatement $statement) {
$this->pdostatement=$statement;
}
// Need this function to load BLOB values from streams
public function bindParam($num, &$value, $type) {
$this->pdostatement->bindParam($num, $value, $type);
return $this;
}
//////////////////////////////////////////////////////////////////////////////
// FLUENT INTERFACE
// Add automatic calling of execute() and closeCursor()
// See http://en.wikipedia.org/wiki/Fluent_interface
//////////////////////////////////////////////////////////////////////////////
public function __call($function, $params) {
switch ($function) {
case 'closeCursor':
$this->executed=false;
// no break;
case 'bindColumn':
case 'bindParam':
case 'bindValue':
// TODO: bind variables need to be stored in $this->bind_variables so we can log them
case 'setAttribute':
case 'setFetchMode':
// Functions that return no values become fluent
call_user_func_array(array($this->pdostatement, $function), $params);
return $this;
case 'execute':
if ($this->executed) {
trigger_error('WT_DBStatement::execute() called twice.', E_USER_ERROR);
} else {
if ($params) {
$this->bind_variables=$params[0];
foreach ($params[0] as &$param) {
if ($param===false) {
// For consistency, otherwise true=>'1' and false=>''
$param=0;
}
}
}
$start=microtime(true);
$result=call_user_func_array(array($this->pdostatement, $function), $params);
$end=microtime(true);
$this->executed=!preg_match('/^(insert|delete|update|create|alter) /i', $this->pdostatement->queryString);
WT_DB::logQuery($this->pdostatement->queryString, $this->pdostatement->rowCount(), $end-$start, $this->bind_variables);
return $this;
}
case 'fetch':
case 'fetchColumn':
case 'fetchObject':
case 'fetchAll':
// Automatically execute the query
if (!$this->executed) {
$this->execute();
$this->executed=true;
}
// no break;
default:
return call_user_func_array(array($this->pdostatement, $function), $params);
}
}
//////////////////////////////////////////////////////////////////////////////
// FUNCTIONALITY ENHANCEMENTS
//////////////////////////////////////////////////////////////////////////////
// Fetch one row, and close the cursor. e.g. SELECT * FROM foo WHERE pk=bar
public function fetchOneRow($fetch_style=PDO::FETCH_OBJ) {
if (!$this->executed) {
$this->execute();
}
$row=$this->pdostatement->fetch($fetch_style);
$this->pdostatement->closeCursor();
$this->executed=false;
return $row ? $row : null;
}
// Fetch one value and close the cursor. e.g. SELECT MAX(foo) FROM bar
public function fetchOne($default=null) {
if (!$this->executed) {
$this->execute();
}
$row=$this->pdostatement->fetch(PDO::FETCH_NUM);
$this->pdostatement->closeCursor();
$this->executed=false;
return is_array($row) ? $row[0] : $default;
}
// Fetch two columns, and return an associative array of col1=>col2
public function fetchAssoc() {
if (!$this->executed) {
$this->execute();
}
$rows=array();
while ($row=$this->pdostatement->fetch(PDO::FETCH_NUM)) {
$rows[$row[0]]=$row[1];
}
$this->pdostatement->closeCursor();
$this->executed=false;
return $rows;
}
// Fetch all the first column, as an array
public function fetchOneColumn() {
if (!$this->executed) {
$this->execute();
}
$list=array();
while ($row=$this->pdostatement->fetch(PDO::FETCH_NUM)) {
$list[]=$row[0];
}
$this->pdostatement->closeCursor();
$this->executed=false;
return $list;
}
}