.
*/
use PDO;
use PDOException;
/**
* Class Database Class - Extend PHP's native PDO class
* to provide database access with logging, etc.
*/
class Database {
/** @var Database Implement the singleton pattern */
private static $instance;
/** @var PDO Native PHP database driver */
private static $pdo;
/** @var array Keep a log of all the SQL statements that we execute */
private static $log;
/**
* Prevent instantiation via new Database
*/
private function __construct() {
self::$log = array();
}
/**
* Begin a transaction.
*
* @return bool
*/
public static function beginTransaction() {
return self::$pdo->beginTransaction();
}
/**
* Commit this transaction.
*
* @return bool
*/
public static function commit() {
return self::$pdo->commit();
}
/**
* Disconnect from the server, so we can connect to another one
*
* @return void
*/
public static function disconnect() {
self::$pdo = null;
}
/**
* Implement the singleton pattern, using a static accessor.
*
* @param string $DBHOST
* @param string $DBPORT
* @param string $DBNAME
* @param string $DBUSER
* @param string $DBPASS
*
* @throws \Exception
*/
public static function createInstance($DBHOST, $DBPORT, $DBNAME, $DBUSER, $DBPASS) {
if (self::$pdo instanceof PDO) {
throw new \Exception('Database::createInstance() can only be called once.');
}
// Create the underlying PDO object
self::$pdo = new PDO(
(substr($DBHOST, 0, 1) === '/' ?
"mysql:unix_socket={$DBHOST};dbname={$DBNAME}" :
"mysql:host={$DBHOST};dbname={$DBNAME};port={$DBPORT}"
),
$DBUSER, $DBPASS,
array(
PDO::ATTR_ERRMODE => 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");
self::$instance = new self;
}
/**
* We don't access $instance directly, only via query(), exec() and prepare()
*
* @return Database
*
* @throws \Exception
*/
public static function getInstance() {
if (self::$pdo instanceof PDO) {
return self::$instance;
} else {
throw new \Exception('createInstance() must be called before getInstance().');
}
}
/**
* Are we currently connected to a database?
*
* @return boolean
*/
public static function isConnected() {
return self::$pdo instanceof PDO;
}
/**
* Log the details of a query, for debugging and analysis.
*
* @param string $query
* @param integer $rows
* @param double $microtime
* @param string[] $bind_variables
*
* @return void
*/
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'];
} 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(Filter::escapeHtml($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;
}
}
/**
* Determine the number of queries executed, for the page statistics.
*
* @return integer
*/
public static function getQueryCount() {
return count(self::$log);
}
/**
* Convert the query log into an HTML table.
*
* @return string
*/
public static function getQueryLog() {
$html = '| # | Query | Rows | Time (ms) |
|---|
' . implode('', self::$log) . '
';
self::$log = array();
return $html;
}
/**
* Determine the most recently created value of an AUTO_INCREMENT field.
*
* @return string
*/
public static function lastInsertId() {
return self::$pdo->lastInsertId();
}
/**
* Quote a string for embedding in a MySQL statement.
*
* The native quote() function does not convert PHP nulls to DB nulls
*
* @param $string
*
* @return string
*
* @deprecated We should use bind-variables instead.
*/
public static function quote($string) {
if (is_null($string)) {
return 'NULL';
} else {
return self::$pdo->quote($string, PDO::PARAM_STR);
}
}
/**
* Execute an SQL statement, and log the result.
*
* @param string $sql The SQL statement to execute
*
* @return integer The number of rows affected by this SQL query
*/
public static function exec($sql) {
$sql = str_replace('##', WT_TBLPREFIX, $sql);
$start = microtime(true);
$rows = self::$pdo->exec($sql);
$end = microtime(true);
self::logQuery($sql, $rows, $end - $start, array());
return $rows;
}
/**
* Prepare an SQL statement for execution.
*
* @param $sql
*
* @return Statement
* @throws \Exception
*/
public static function prepare($sql) {
if (!self::$pdo instanceof PDO) {
throw new \Exception("No Connection Established");
}
$sql = str_replace('##', WT_TBLPREFIX, $sql);
return new Statement(self::$pdo->prepare($sql));
}
/**
* Roll back this transaction.
*
* @return bool
*/
public static function rollBack() {
return self::$pdo->rollBack();
}
/**
* Run a series of scripts to bring the database schema up to date.
*
* @param string $schema_dir
* @param string $schema_name
* @param integer $target_version
*
* @return void
* @throws \Exception
*/
public static function updateSchema($schema_dir, $schema_name, $target_version) {
try {
$current_version = (int) Site::getPreference($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;
}
// During installation, the current version is set to a special value of
// -1 (v1.2.5 to v1.2.7) or -2 (v1.3.0 onwards). This indicates that the tables have
// been created, and we are already at the latest version.
switch ($current_version) {
case -1:
// Due to a bug in webtrees 1.2.5 - 1.2.7, the setup value of "-1"
// wasn't being updated.
$current_version = 12;
Site::setPreference($schema_name, $current_version);
break;
case -2:
// Because of the above bug, we now set the version to -2 during setup.
$current_version = $target_version;
Site::setPreference($schema_name, $current_version);
break;
}
// Update the schema, one version at a time.
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) Site::getPreference($schema_name);
if ($current_version != $next_version) {
throw new \Exception("Internal error while updating {$schema_name} to {$next_version}");
}
}
}
}