<?php
// Copyright 2011 Toby Zerner, Simon Zerner
// This file is part of esoTalk. Please see the included license file for usage information.

if (!defined("IN_ESOTALK")) exit;

/**
 * The database class handles the database connection and the running of queries against the database.
 *
 * @package esoTalk
 */
class ETDatabase extends ETPluggable {


/**
 * An instance of the PDO connection object.
 * @var PDO
 */
protected $pdoConnection;


/**
 * An instance of the database structure class.
 * @var ETDatabaseStructure
 */
protected $structure;


/**
 * The database host.
 * @var string
 */
protected $host;


/**
 * The database port.
 * @var string
 */
protected $port;


/**
 * The database user.
 * @var string
 */
protected $user;


/**
 * The database password.
 * @var string
 */
protected $password;


/**
 * The database name.
 * @var string
 */
protected $dbName;


/**
 * An array of connection options to use when creating a PDO connection.
 * @var array
 */
protected $connectionOptions = array();


/**
 * The database table prefix.
 * @var string
 */
public $tablePrefix;


/**
 * Whether or not we are currently in the middle of a database transaction.
 * @var bool
 */
protected $inTransaction = false;


/**
 * An array of queries that have been run.
 * @var array
 */
public $queries = array();


/**
 * Get an instance of the database structure class.
 *
 * @return ETDatabaseStructure An instance of the database structure class.
 */
public function structure()
{
	if (!$this->structure) $this->structure = ETFactory::make("databaseStructure");
	return $this->structure;
}


/**
 * Create a new instance of the SQL query class.
 *
 * @return ETSQLQuery A new instance of the SQL query class.
 */
public function SQL()
{
	return ETFactory::make("sqlQuery");
}


/**
 * Get the PDO connection to the database, creating a new one if it does not already exist.
 *
 * @return PDO The PDO connection.
 */
public function connection()
{
	if (!$this->pdoConnection) {
		$dsn = "mysql:host=".$this->host.($this->port ? ";port=".$this->port : "").";dbname=".$this->dbName;
		$this->pdoConnection = @new PDO($dsn, $this->user, $this->password, $this->connectionOptions);
	}
	return $this->pdoConnection;
}


/**
 * Fetches the version of the database engine in use.
 *
 * @return string The database engine version.
 */
public function getVersion()
{
	return $this->query("SELECT VERSION()")->result();
}


/**
 * Initialize the database class with database details. These details will be used when a PDO connection
 * is made.
 *
 * @param string $host The database host.
 * @param string $user The database user.
 * @param string $password The database password.
 * @param string $dbName The database name.
 * @param string $tablePrefix The database table prefix.
 * @param array $connectionOptions An array of connection options to use when making the PDO connection.
 * @return void
 */
public function init($host, $user, $password, $dbName, $tablePrefix = "", $connectionOptions = array(), $port = null)
{
	$this->pdoConnection = null;

	$this->host = $host;
	$this->port = $port;
	$this->user = $user;
	$this->password = $password;
	$this->dbName = $dbName;
	$this->tablePrefix = $tablePrefix;
	$this->connectionOptions = $connectionOptions;

	// We don't use PDO's prepared statements as they have some flaws. We emulate parameter binding behaviour
	// (see ETSQLQuery and escapeValue()), and then send direct queries.
	$this->connectionOptions[PDO::MYSQL_ATTR_DIRECT_QUERY] = true;
}


/**
 * Close the connection to the database.
 *
 * @return void
 */
public function close()
{
	$this->commitTransaction();
	unset($this->pdoConnection);
}


/**
 * Begin a database transaction.
 *
 * @return void
 */
public function beginTransaction()
{
	$this->connection()->beginTransaction();
	$this->inTransaction = true;
}


/**
 * Roll back a database transaction.
 *
 * @return void
 */
public function rollbackTransaction()
{
	if (!$this->inTransaction) return;
	$this->connection()->rollback();
	$this->inTransaction = false;
}


/**
 * Commit a database transaction.
 *
 * @return void
 */
public function commitTransaction()
{
	if (!$this->inTransaction) return;
	$this->connection()->commit();
	$this->inTransaction = false;
}


/**
 * Get the ID of the last record inserted into the database.
 *
 * @return string The last insert ID.
 */
public function lastInsertId()
{
	return $this->query("SELECT LAST_INSERT_ID()")->result();
}


/**
 * Escape a value so that it is safe to use in an SQL query.
 *
 * @param mixed $value The value to escape.
 * @param int $dataType Explicit data type for the value using PDO::PARAM_* constants. If null,
 * 		the type of $value will be used.
 * @return mixed The escaped value.
 */
public function escapeValue($value, $dataType = null)
{
	// If the value is a raw SQL query object, don't sanitize it.
	if ($value instanceof ETSQLRaw) return $value;

	// If the value is an array, escape each element individually and return a comma-separated string.
	if (is_array($value)) {
		foreach ($value as &$v) $v = $this->escapeValue($v, $dataType);
    	return implode(",", $value);
	}

	// If no data type was specified, work it out based on the variable content.
	if ($dataType === null) {
		if ($value === true or $value === false) $dataType = PDO::PARAM_BOOL;
		elseif ($value === null) $dataType = PDO::PARAM_NULL;
		elseif (is_int($value)) $dataType = PDO::PARAM_INT;
		else $dataType = PDO::PARAM_STR;
	}

	// Now escape the value according to the data type.
	switch ($dataType) {
		case PDO::PARAM_BOOL:
			return $value ? "1" : "0";

		case PDO::PARAM_NULL:
			return "NULL";

		case PDO::PARAM_INT:
			$value = (int)$value;
			return $value ? (string)$value : "0";

		default:
			$value = str_replace(array('\\', "\0", "\n", "\r", "'", '"', "\x1a"), array('\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z'), $value);
			return "'".$value."'";
	}
}


/**
 * Run a query against the database.
 *
 * @param string $query The query to run.
 * @return ETSQLResult|bool An SQL result, or false if the query was unsuccessful.
 */
public function query($query)
{
	// If the query is empty, don't bother proceeding.
	if (!$query) return false;

	// Get the database connection.
	$connection = $this->connection();

	$this->trigger("beforeQuery", array(&$query));
	$this->queries[] = $query;

	// Execute the query.
	$statement = $connection->query($query);

	// Was there an error?
	if (!$statement) {
		$error = $connection->errorInfo();
		throw new Exception("SQL Error (".$error[0].", ".$error[1]."): ".$error[2]."<br><br><pre>".$this->highlightQueryErrors($query, $error[2])."</pre>");
	}

	// Set up a new ETSQLResult object with the result statement.
	$result = ETFactory::make("sqlResult", $statement);

	$this->trigger("afterQuery", array($result));

	return $result;
}


/**
 * Find anything in single quotes in the error and make it red in the query (just to make debugging a bit
 * easier!)
 *
 * @param string $query The SQL query that failed.
 * @param string $error The error string that was returned by the connection.
 * @return string The SQL query with the guessed "errorous" parts highlighted.
 */
protected function highlightQueryErrors($query, $error)
{
	$query = sanitizeHTML($query);
	preg_match("/'(.+?)'/", $error, $matches);
	if (!empty($matches[1])) $query = str_replace($matches[1], "<span class='highlight'>{$matches[1]}</span>", $query);
	return $query;
}

}
