<?php
/**
* This file handles mysql database connections, queries, procedures etc.
* Most functions are overridden from the base object.
*
* @version     $Id$
* @author Chris <chris@interspire.com>
*
* @package Db
* @subpackage MySQLDb
*/

/**
* Include the base database class.
*/

if (!function_exists('mysql_connect')) {
	die("Your PHP installation does not have MySQL support. Please enable MySQL support in PHP or ask your web host to do so for you.");
}
/**
* This is the class for the MySQL database system.
*
* @package Db
* @subpackage MySQLDb
*/

require_once(dirname(__FILE__) .'/db.php');

class MySQLDb extends db {

	public $LastId;

	public $hostname = '';
	public $username = '';
	public $password = '';
	public $databasename = '';

	static $currentDatabaseName = '';
	/*
	 * This is used when there is a commonly known error and we need to set our own clearer error message.
	 */
	public $ErrorDesc = '';

	public $StoreQueryList = false;

	public $ErrorCallback = '';


	/**
	* Constructor
	* Sets up the database connection.
	* Can pass in the hostname, username, password and database name if you want to.
	* If you don't it will set up the base class, then you'll have to call Connect yourself.
	*
	* @param String $hostname Name of the server to connect to.
	* @param String $username Username to connect to the server with.
	* @param String $password Password to connect with.
	* @param String $databasename Database name to connect to.
	*
	* @see Connect
	* @see GetError
	*
	* @return mixed Returns false if no connection can be made - the error can be fetched by the Error() method. Returns the connection result if it can be made. Will return Null if you don't pass in the connection details.
	*/
	function __construct($hostname='', $username='', $password='', $databasename='') {
		if ($hostname && $username && $databasename) {
			$connection = $this->Connect($hostname, $username, $password, $databasename);
			return $connection;
		}
		return null;
	}


	/**
	* Connect
	* This function will connect to the database based on the details passed in.
	*
	* @param String $hostname Name of the server to connect to.
	* @param String $username Username to connect to the server with.
	* @param String $password Password to connect with.
	* @param String $databasename Database name to connect to.
	*
	* @see SetError
	*
	* @return False|Resource Returns the resource if the connection is successful. If anything is missing or incorrect, this will return false.
	*/
	function Connect($hostname='', $username='', $password='', $databasename='') {
		if ($hostname == '') {
			$this->SetError('No server name to connect to');
			return false;
		}

		if ($username == '') {
			$this->SetError('No username name to connect to server ' . $hostname. ' with');
			return false;
		}

		if ($databasename == '') {
			$this->SetError('No database name to connect to');
			return false;
		}

		$connection_result = @mysql_connect($hostname, $username, $password);
		if (!$connection_result) {
			$this->SetError(mysql_error());
			$this->SetErrorMsgFromErrorNum(mysql_error(), mysql_errno());
			return false;
		}
		$this->connection = &$connection_result;

		$db_result = $this->SelectDatabase($databasename);

		if (!$db_result) {
			$this->SetError('Unable to select database \'' . $databasename . '\': ' . mysql_error($this->connection));
			$this->SetErrorMsgFromErrorNum();
			return false;
		}

		$this->hostname = $hostname;
		$this->username = $username;
		$this->databasename = $databasename;
		$this->password = $password;

		return $this->connection;
	}

	public function SelectDatabase($databasename=null){
		if(is_null($databasename)){
			$databasename = $this->databasename;
		}
		$result = @mysql_select_db($databasename, $this->connection);
		if(!$result){
			return $result;
		}

		self::$currentDatabaseName = $databasename;
		return $result;
	}

	/**
	 * Instance
	 * This static variable holds the current instance of this object being loaded.
	 * So using the getInstance function anywhere will return the very same instance.
	 *
	 * @var object Instance
	 */
	public static $Instance;

	/**
	 * getInstance
	 * This is a static function that sets up the class instance and stores it to the static variable. It will then return that instantiation in the future.
	 *
	 * @return object Returns the instantiated object
	 **/
	public static function getInstance(){
		if(!isset(self::$Instance)){
			$classname =  __CLASS__;
			self::$Instance = new $classname;
		}
		return self::$Instance;
	}


	/**
	* Disconnect
	* This function will disconnect from the database handler passed in.
	*
	* @param String $resource Resource to disconnect from
	*
	* @see SetError
	*
	* @return Boolean If the resource passed in is not valid, this will return false. Otherwise it returns the status from pg_close.
	*/
	function Disconnect($resource=null) {
		if (is_null($resource)) {
			$this->SetError('Resource is a null object');
			return false;
		}
		if (!is_resource($resource)) {
			$this->SetError('Resource ' . $resource . ' is not really a resource');
			return false;
		}
		$close_success = mysql_close($resource);
		return $close_success;
	}

	/**
	* Query
	* This function will run a query against the database and return the result of the query.
	*
	* @param String $query The query to run.
	*
	* @see LogQuery
	* @see SetError
	*
	* @return Mixed Returns false if the query is empty or if there is no result. Otherwise returns the result of the query.
	*/
	function Query($query='') {

		if(self::$currentDatabaseName != $this->databasename){
			$this->SelectDatabase();
		}

		$this->NumQueries++;
		if(!isset($GLOBALS['DBNumQueries'])){
			$GLOBALS['DBNumQueries'] = 0;
		}
		$GLOBALS['DBNumQueries']++;
		$this->LastQuery = $query;

		if (!$query) {
			$this->SetError('Query passed in is empty');
			return false;
		}
		if (!$this->connection) {
			$this->SetError('No valid connection');
			return false;
		}
		if ($this->TablePrefix !== null) {
			$query = str_replace("[|PREFIX|]", $this->TablePrefix, $query);
		}

		if ($this->TimeLog !== null || $this->StoreQueryList ==  true || $this->QueryLogByDateDir) {
			$timestart = $this->GetTime();
		}

		$result = mysql_query($query, $this->connection);

		$this->LastId = $this->LastId();

		if ($this->TimeLog !== null || $this->QueryLogByDateDir) {
			$timeend = $this->GetTime();

			if ($this->TimeLog !== null) {
				$this->TimeQuery($query, $timestart, $timeend);
			}
		}

		if($this->StoreQueryList) {
			if(!isset($timeend)) {
				$timeend = $this->GetTime();
			}
			$this->QueryList[] = array(
				"Query" => $query,
				"ExecutionTime" => $timeend-$timestart
			);
		}

		$this->LogQuery("Result type: " . gettype($result) . "; value: " . $result . "\t" . $query);

		if (!$result) {
			$callback_valid = false;
			$error = mysql_error($this->connection);

			if (is_string($this->ErrorCallback)) {
				$callback_valid = function_exists($this->ErrorCallback);
			} elseif (is_array($this->ErrorCallback)) {
				$callback_valid = method_exists($this->ErrorCallback[0], $this->ErrorCallback[1]);
			}

			if ($this->ErrorCallback !== null && $callback_valid && !$this->_InErrorCallback) {
				$this->_InErrorCallback = true;
				call_user_func($this->ErrorCallback, $error, $query);
				$this->_InErrorCallback = false;
			}
			if ($this->ErrorLog !== null) {
				$this->LogError($query, $error);
			}
			$this->SetError($error);

			if ($this->QueryLogByDateDir) {
				$this->LogQueryByDate($query, ($timeend - $timestart), mysql_error($this->connection), debug_backtrace());
			}

			$this->SetErrorMsgFromErrorNum();
			return false;
		}else{
			if ($this->QueryLogByDateDir) {
				$this->LogQueryByDate($query, ($timeend - $timestart), '', debug_backtrace());
			}
		}
		return $result;
	}

	function Affected(){
		return mysql_affected_rows($this->connection);
	}

	/**
	* Fetch
	* This function will fetch a result from the result set passed in.
	*
	* @param String $resource The result from calling Query. Returns an associative array (not an indexed based one)
	*
	* @see Query
	* @see SetError
	* @see StripslashesArray
	*
	* @return Mixed Returns false if the result is empty. Otherwise returns the next result.
	*/
	function Fetch($resource=null) {
		if (is_null($resource)) {
			$this->SetError('Resource is a null object');
			return false;
		}
		if (!is_resource($resource)) {
			$this->SetError('Resource ' . $resource . ' is not really a resource');
			return false;
		}
		return (get_magic_quotes_runtime()) ? $this->stripslashes_array(mysql_fetch_assoc($resource)) : mysql_fetch_assoc($resource);
	}

	function GetCountQuery($query){
		return $this->FetchOne($query);
	}

	/**
	* This function takes a string query and runs the Query() function, then returns the result of the FirstResult() function.
	*
	* @param string $query A query to run
	* @return string The first field value
	*/

	function FetchOne($query){
		$q = $this->Query($query);
		return $this->FirstResult($q);
	}

	/**
	 * Fetches a mysql result as an array of objects based on the $query
	 * given. If a second argument is set to true, then the array will be
	 * reduced to a single element. This is faster and less problematic
	 * than using a regex such as /LIMIT\s*([0-9]+,)?[0-9]+/ to check for
	 * a limit clause especially in cases where a LIMIT 1 is passed when a
	 * LIMIT 10 is expected. If a class name is passed, then that class
	 * will beinstantiated with the passed class parameters and populated
	 * from the resulting object set.
	 *
	 * @return Mixed
	 * @param  String  $query       The query to use.
	 * @param  Boolean $reduce      Whether or not to reduce the result
	 *                              to a single object.
	 * @param  String  $className   The class to instantiate and fill.
	 * @param  Array   $classParams An array of parameters to pass to the
	 *                              class upon instantiation.
	 */
	public function FetchAll($query, $reduce = false, $className = null, $classParams = array())
	{
		$arr = array();
		$res = $this->Query($query);

		if (!$res) {
			return false;
		}

		// either fill a specified class or just a stdClass
		if ($className && $className !== 'stdClass') {
			while ($row = mysql_fetch_object($res, $className, $classParams)) {
				$arr[] = $row;
			}
		} else {
			while ($row = mysql_fetch_object($res)) {
				$arr[] = $row;
			}
		}

		if (!count($arr)) {
			return false;
		}

		if ($reduce) {
			return $arr[0];
		}

		return $arr;
	}

	/**
	 * Returns the number of found rows that were retrieved in the last query.
	 * If it was not a select query, then it will return 0.
	 *
	 * @return Integer
	 */
	public function FoundRows()
	{
		$foundRows = $this->FetchAll('SELECT FOUND_ROWS() AS num_rows;', true);
		$foundRows = (int) $foundRows->num_rows;

		return $foundRows;
	}

	function WhereCsv($field, $value){
		$field = '`' . $field . '`';
		$value = $this->Quote($value);
		$query = " (" . $field . "='".$value."' OR " . $field . " LIKE '%,".$value."' OR " . $field . " LIKE '".$value.",%' OR " . $field . " LIKE '%,".$value.",%')";
		return $query;
	}

	function FirstResult($resource=null){

		if (is_null($resource)) {
			$this->SetError('Resource is a null object');
			return false;
		}
		if (!is_resource($resource)) {
			$this->SetError('Resource ' . $resource . ' is not really a resource');
			return false;
		}

		$return = @mysql_result($resource,0,0);
		if($return || $return === '0'){
			return $return;
		}else{
			return '';
		}

	}

	function Select($table,$field,$where='1=1'){
		$query = $this->Query("select ".$field." from ".$this->TablePrefix.$table." where ".$where);
		return $this->FirstResult($query);
	}

	function Increment($table,$field,$where){
		return $this->Query("update ".$this->TablePrefix.$table." set `".$field."`=`".$field."`+1 where ".$where);
	}

	function AddIndex($table,$fields,$type='BTREE'){

		if(is_array($fields)){
			$_fields = '`' . implode("`,`", $fields). '`';
		}else{
			$_fields = '`' .$fields. '`';
		}
		if($type == 'BTREE'){
			return $this->Query('ALTER TABLE `'.$table.'` ADD INDEX ('.$_fields .')');
		}elseif($type == 'FULLTEXT'){
			return $this->Query('ALTER TABLE `'.$table.'` ADD FULLTEXT ('.$_fields .')');
		}else{
			return 'Invalid Index Type';
		}
	}


	function TableExists($table)
	{
		$query = "SHOW TABLES LIKE '".$table."'";
		$result = $this->FetchOne($query);
		// If there is a table with this name already we don't need to run this
		// part of the upgrade script

		// We have to convert the name to lower case before comparing since
		// if MySQL is on windows the tables are converted to lowercase
		// when they are returned from our sql statement
		if (strtolower($result) == strtolower($table)) {
			return true;
		} else {
			return false;
		}
	}

	function SetErrorMsgFromErrorNum($msg=null,$num=null){
		$msg = ($msg == null) ? mysql_error($this->connection) : $msg;
		$num = ($num == null) ? mysql_errno($this->connection) : $num;

		$return = '';

		if ($num == 1045 && (strpos($msg, 'using password: NO') !== false)){
			$return = 'Access to your MySQL server was denied. Usually this is because your username was wrong or because you didn\'t supply a password.';
		}elseif($num == 1045){
			$return = 'Access to your MySQL server was denied. Usually this is because your username and/or password were incorrect.';
		}elseif($num == 1049){
			$return = 'The MySQL database name you entered doesn\'t exist.';
		}elseif($num == 2005){
			$return = 'Unable to connect to the database. The MySQL Server you entered is incorrect.';
		}

		if(strlen($return) < 1){
			$return = 'An unknown error occurred. MySQL Said: '. $msg;
		}

		$this->ErrorDesc = $return;
	}

	function GetErrorDesc(){
		return $this->ErrorDesc;
	}

	/**
	* This function contructs an ALTER TABLE query for adding a new column and returns a MySQL resource for it
	*
	* @param string $table The full table name that the column is to be added to
	* @param string $field The fieldname that the column should have
	* @param string $type The type of field, can be: bool/boolean, int, float, text, longtext, datetime or varchar
	* @param string $default Any default value for the field, if it is to be set to NULL it must be a string and not a PHP NULL.
	* @param string $notNull Whether or not 'NOT NULL' should be added to the query.
	* @return resource
	*/

	function AddColumn($table, $field, $type='varchar', $default='', $notNull=' NOT NULL'){

		if($default != '' && strpos(strtolower($default), 'default') === false){
			if($default == 'NULL'){
				$default = "DEFAULT NULL";
			}else{
				$default = "DEFAULT '".$default."'";
			}
		}

		switch($type){
			case 'bool':
			case 'boolean':
				$_type = 'INT(11) '.$default.$notNull;
			case 'int':
				$_type = 'INT(11) '.$default.$notNull;
				break;
			case 'float':
				$_type = 'float '.$default.$notNull;
				break;
			case 'text':
				$_type = 'text ';
				break;
			case 'longtext':
				$_type = 'longtext ';
				break;
			case 'datetime':
				$_type = 'datetime '.$default.$notNull;
				break;
			case 'varchar':
			default:
				$_type = 'VARCHAR(255) '.$default.$notNull;
		}

		return $this->Query('ALTER TABLE '.$table.' ADD `'.$field.'` '.$_type);
	}

	/**
	* This function contructs an ALTER TABLE query for removing a column from a table and returns a MySQL resource for it
	*
	* @param string $table The name of the table to have the column removed from.
	* @param string $field The name of the column to drop.
	*
	* @return resource
	*/
	function RemoveColumn($table,$field){
		return $this->Query('ALTER TABLE '.$table.' DROP COLUMN `'.$field.'`');
	}

	/**
	* An helper function to quickly call Query() and Fetch().
	*
	* @param string $query A SELECT query to run against the database.
	*
	* @return array The first row returned from the query passed in.
	*/

	function FetchQuery($query){
		return $this->Fetch($this->Query($query));
	}

	/**
	* A helper function quickly retrieve all data rows from a SELECT query.
	* This function takes a string and calls Query(), then loops over Fetch() and returns an array of rows.
	*
	* @param string $query The SELECT query to execute and return the data for
	*
	* @return array An array of data rows from the database
	*/

	function FetchQueryAll($query){
		$return = array();

		if(is_resource($query)) {
			$result = &$query;
		} else {
			$result = $this->Query($query);
		}

		while(($row = $this->Fetch($result))){
			$return[] = $row;
		}
		return $return;
	}

	/**
	* NextId
	* Fetches the next id from the sequence passed in
	*
	* @param string Sequence Name to fetch the next id for.
	*
	* @see Query
	*
	* @return mixed Returns false if there is no sequence name or if it can't fetch the next id. Otherwise returns the next id
	*/
	function NextId($sequencename=false) {
		if (!$sequencename) {
			$result = $this->LastId();
			if($result){
				return (int)$result+1;
			}
		}
		return false;
	}

	/**
	* LastId
	* Fetches the id of the last INSERT query run on the Database
	*
	* @see Query
	* @see NextId
	*
	* @return mixed Returns false if there was no last insert, or integer for the last id
	*/
	function LastId() {
		return mysql_insert_id($this->connection);
	}


	/**
	* FullText
	* Fulltext works out how to handle full text searches. Returns an sql statement to append to enable full text searching.
	*
	* @param mixed Fields to search against. This can be an array or a single field.
	* @param string String to search for against the fields
	*
	* @return mixed Returns false if either fields or searchstring aren't present, otherwise returns a string to append to an sql statement.
	*/
	function FullText($fields=null, $searchstring=null, $booleanMode=true) {
		if (is_null($fields) or is_null($searchstring)) return false;
		if (is_array($fields)) {
			$fields = implode(',', $fields);
		}
		$query = 'MATCH (' . $fields . ') AGAINST (\'' . $this->Quote($searchstring) . '\'  ';

		if($booleanMode){
			$query .= 'IN BOOLEAN MODE';
		}

		$query .= ')';

		return $query;
	}


	/**
	* AddLimit
	* This function creates the SQL to add a limit clause to an sql statement.
	*
	* @param Int $offset Where to start fetching the results
	* @param Int $numtofetch Number of results to fetch
	*
	* @return String The string to add to the end of the sql statement
	*/
	function AddLimit($offset=0, $numtofetch=0) {
		if ($offset < 0) {
			$offset = 0;
		}
		if ($numtofetch <= 0) {
			$numtofetch = 10;
		}
		$query = ' LIMIT ' . $offset . ', ' . $numtofetch;
		return $query;
	}

	/**
	* FreeResult
	* Frees the result from memory.
	*
	* @param String $resource The result resource you want to free up.
	*
	* @return Boolean Whether freeing the result worked or not.
	*/
	function FreeResult($resource = null) {
		if (is_null($resource)) {
			$this->SetError('Resource is a null object');
			return false;
		}
		if (!is_resource($resource)) {
			$this->SetError('Resource ' . $resource . ' is not really a resource');
			return false;
		}
		$result = mysql_free_result($resource);
		return $result;
	}

	/**
	* CountResult
	* Returns the number of rows returned for the resource passed in
	*
	* @param String $resource The result from calling Query
	*
	* @see Query
	* @see SetError
	*
	* @return Int Number of rows from the result
	*/
	function CountResult($resource = null) {
		if (is_null($resource)) {
			$this->SetError('Resource is a null object');
			return false;
		}
		if (!is_resource($resource)) {
			$this->SetError('Resource ' . $resource . ' is not really a resource');
			return false;
		}
		$count = mysql_num_rows($resource);
		return $count;
	}

	/**
	* Concat
	* Concatentates multiple strings together. This method is mysql specific. It doesn't matter how many arguments you pass in, it should handle them all.
	* If you pass in one argument, it will return it straight away.
	* Otherwise, it will use the mysql specific CONCAT function to put everything together and return the new string.
	*
	* @return String Returns the new string with all of the arguments concatenated together.
	*
	* @return string
	*/
	function Concat() {
		$num_args = func_num_args();
		if ($num_args < 1) {
			return func_get_arg(0);
		}
		$all_args = func_get_args();
		$returnstring = 'CONCAT(' . implode(',', $all_args) . ')';
		return $returnstring;
	}

	/**
	* Quote
	* Quotes the string ready for database queries. Runs mysql_escape_string or mysql_real_escape_string depending on the php version.
	*
	* @param String $string String you want to quote ready for database entry.
	*
	* @return String String with quotes!
	*/
	function Quote($string='') {
		if(function_exists('mysql_real_escape_string')){
			return mysql_real_escape_string($string);
		}else{
			return mysql_escape_string($string);
		}
	}

	/**
	 * InsertQuery
	 * Takes an array of fields and values and constructs an insert query
	 *
	 * @param String The name of the table to insert a row into
	 * @param Array List of fields as the keys and field values as the array values
	 *
	 * @return Mixed Inserted id (int) or false (bool) for failure
	 *
	 */
	function InsertQuery($table,$inserts){

		foreach ($inserts as $field=>$value){
			$fieldList[] = $field;
			$valueList[] = $this->Quote($value);
		}

		$query = "INSERT INTO ".$this->TablePrefix.$table;
		$query .= " (`";
		$query .= implode('`,`',$fieldList);
		$query .= "`) VALUES (";

		foreach($valueList as $key=>$value) {
			if($value === 'NOW()') {
				$valueList[$key] = 'NOW()';
			} else {
				$valueList[$key] = "'" . $value ."'";
			}
		}

		$query .= implode(',', $valueList);
		$query .= ")";

		if($this->Query($query)){
			return $this->LastId;
		}else{
			return false;
		}
	}

	/**
	 * UpdateQuery
	 * Takes an array of fields and values and constructs an insert query
	 *
	 * @param String The name of the table to insert a row into
	 * @param Array List of fields as the keys and field values as the array values
	 * @param String Where clause to determine what rows are needed to be updated
	 * @param Int Max number of rows to be updated
	 *
	 * @return Bool True for it worked, false for any errors
	 *
	 */
	function UpdateQuery($table,$updates,$where,$limit=null){
		// init vars
		$query = '';

		// loop through fields
		foreach ($updates as $field=>$value){
			if(is_null($value)){
				$updateList[] = '`'.$field."`=NULL";
			}else{
				$updateList[] = '`'.$field."`='".$this->Quote($value)."'";
			}
		}

		$query = "UPDATE ".$this->TablePrefix.$table;
		$query .= " SET ".implode(',',$updateList);
		$query .= " WHERE ".$where;
		if(is_numeric($limit) && $limit !== null){
			$query .= " LIMIT ".(int)$limit;
		}

		if($this->Query($query)){
			return true;
		}else{
			return false;
		}
	}
	/**
	* OptimizeTable
	*
	* Runs "optimize" over the tablename passed in. This is useful to keep the database reasonably speedy.
	*
	* @param String $tablename The tablename to optimize.
	*
	* @see Query
	*
	* @return Mixed If no tablename is passed in, this returns false straight away. Otherwise it calls Query and returns the result from that.
	*/
	function OptimizeTable($tablename='')
	{
		if (!$tablename) {
			return false;
		}
		$query = "OPTIMIZE TABLE " . $tablename;
		return $this->Query($query);
	}

	function SetTablePrefix($prefix){
		$this->TablePrefix = $prefix;
	}

	/**
	 * ColumnExists
	 *
	 * Checks to see if a column exists on a table in the current database.
	 *
	 * @param String $table The name of the table to check.
	 * @param String $columns The name of the column to check.
	 * @return Bool Returns true if the column exists in the table, otherwise false.
	 */
	function ColumnExists ($table, $columns)
	{
		$result = true;
		if (!is_array($columns)) {
			$columns = array($columns);
		}

		foreach ($columns as $column) {
			$query = 'SHOW COLUMNS FROM ' . $table . " LIKE '" . $column . "'";

			$result = $this->Query($query);
			$row = $this->Fetch($result);

			if ($row['Field'] != $column) {
				$result = false;
				break;
			}
		}

		return $result;
	}
}