<?php
/**
* Include the base database class.
*/
require_once(dirname(__FILE__).'/db.php');

if (!function_exists('mysqli_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
*/
class MySQLDb extends Db
{

	/**
	 * MySQL uses ` to escape table/database names
	 *
	 * @var String
	 */
	public $EscapeChar = '`';

	/**
	* This flag is checked when Query is called to see which mode to run the query in.
	* Calling the UnbufferedQuery function sets this flag to true, then lets the main Query function handle the rest.
	*
	* @see UnbufferedQuery
	* @see Query
	*
	* @var Bool Defaults to false (don't run in unbuffered mode).
	*/
	public $_unbuffered_query = false;

	/**
	 * @var bool
	 */
	public $disable_foreignkeychecks = false;

	/**
	* 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
	*/
	public function __construct($hostname='', $username='', $password='', $databasename='')
	{
	    parent::__construct();
		if ($hostname && $username && $databasename) {
			$this->Connect($hostname, $username, $password, $databasename);
		}
	}

	/**
	* 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 Bool|mysqli Returns the resource if the connection is successful. If anything is missing or incorrect, this will return false.
	*/
	public function Connect($hostname='', $username='', $password='', $databasename='')
	{
		if (empty($hostname) && empty($username) && empty($password) && empty($databasename)) {
			$hostname = $this->_hostname;
			$username = $this->_username;
			$password = $this->_password;
			$databasename = $this->_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;
		}

		if ($this->_retry && $this->connection instanceof mysqli) {
			$this->Disconnect($this->connection);
		}

		$connection_result = @mysqli_connect($hostname, $username, $password);
		if (!$connection_result) {
			$this->SetError(mysqli_connect_error());
			return false;
		}
		$this->connection = &$connection_result;

		$db_result = @mysqli_select_db($connection_result, $databasename);
		if (!$db_result) {
			$this->SetError("Unable to select database '$databasename': " . mysqli_error($connection_result));
			return false;
		}
		$this->_hostname = $hostname;
		$this->_username = $username;
		$this->_password = $password;
		$this->_databasename = $databasename;

		// SET MySQL specific values
		$mysql_set = [];

		if ($this->sql_mode) {
			$mysql_set['mode'] = "SESSION sql_mode = '" . $this->Quote($this->sql_mode) . "'";
		}

		// Set the character set if we have one
		if ($this->charset) {
			//$this->Query('SET NAMES '.$this->charset);
			//$this->Query("SET CHARACTER SET " .$this->charset);
			@mysqli_set_charset($connection_result, $this->charset);
		}

		if ($this->collate) {
			//$this->Query("ALTER DATABASE " .$this->_databasename ." COLLATE ". $this->collate);
			$mysql_set[] = "collation_connection = '" . $this->Quote($this->collate) . "'";
		}

		// Do we have a timezone? Set it
		if ($this->timezone) {
			//$this->Query("SET time_zone = '".$this->timezone."'");
			$mysql_set[] = "time_zone = '" . $this->Quote($this->timezone) . "'";
		}

		if ($this->disable_foreignkeychecks) {
			$mysql_set[] = 'foreign_key_checks = 0';
		}

		if (count($mysql_set)) {
			$this->Query('SET ' . implode(', ', $mysql_set));
		}

		return $this->connection;
	}

	/**
	* Disconnect
	* This function will disconnect from the database handler passed in.
	*
	* @param ?mysqli $resource Resource to disconnect from
	*
	* @see SetError
	*
	* @return Bool If the resource passed in is not valid, this will return false. Otherwise it returns the status from pg_close.
	*/
    public function Disconnect($resource=null)
	{
		if ($resource === null) {
			$this->SetError('Resource is a null object');
			return false;
		}
		if (!$resource instanceof mysqli) {
            $this->SetError('Resource is not valid: '.var_export($resource, true));
			return false;
		}
		$close_success = mysqli_close($resource);
		if ($close_success) {
			$this->connection = null;
		}
		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 mysqli_result|Bool Returns false if the query is empty or if there is no result. Otherwise returns the result of the query.
	*/
	public function Query($query='')
	{
		// if we're retrying a query, we have to kill the old connection and grab it again.
		// if we don't, we get a cached connection which won't work.
		if ($this->_retry) {
			$this->Connect();
		}

        // Trim query
		$query = trim($query);

		if (!$query) {
			$this->_retry = false;
			$this->SetError('Query passed in is empty');
			return false;
		}

		if (!$this->connection) {
			$this->_retry = false;
			$this->SetError('No valid connection');
			return false;
		}

		if (!empty($this->TablePrefix)) {
			$query = str_replace("[|PREFIX|]", $this->TablePrefix, $query);
		} else {
			$query = str_replace("[|PREFIX|]", '', $query);
		}

		$this->NumQueries++;

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

		if (!$this->_unbuffered_query) {
			$result = mysqli_query($this->connection, $query);
		} else {
			$result = mysqli_query($this->connection, $query, MYSQLI_USE_RESULT);
			$this->_unbuffered_query = false;
		}

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

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

		if (!empty($this->QueryLog)) {
			if ($this->_retry) {
				$this->LogQuery("*** Retry *** Result type: " . gettype($result) . "; value: " . var_export($result, true) . "\t" . $query);
			} else {
				$this->LogQuery("Result type: " . gettype($result) . "; value: " . var_export($result, true) . "\t" . $query);
			}
		}

		if (!$result) {
			$error = mysqli_error($this->connection);
			$errno = mysqli_errno($this->connection);

			if (!empty($this->ErrorLog)) {
				$this->LogError($query, $error);
			}

			$this->SetError($error, E_USER_ERROR, $query);

			// we've already retried? don't try again.
			// or if the error is not '2006', then don't bother going any further.
			if ($this->_retry || $errno !== 2006) {
				$this->_retry = false;
				return false;
			}

			// error 2006 is 'server has gone away'
			// http://dev.mysql.com/doc/refman/5.0/en/error-messages-client.html
			if ($errno === 2006) {
				$this->_retry = true;
				return $this->Query($query);
			}
		}

		// make sure we set the 'retry' flag back to false if we are returning a result set.
		$this->_retry = false;
		return $result;
	}

	/**
	* UnbufferedQuery
	* Runs a query in 'unbuffered' mode which means that the whole result set isn't loaded in to memory before returning it.
	* Calling this function sets a flag in the class to say run the query in unbuffered mode, then uses Query to handle the rest.
	*
	* @param String $query The query to run in unbuffered mode.
	*
	* @see _unbuffered_query
	* @see Query
	*
	* @return Mixed Returns the result from the Query function.
	*/
	public function UnbufferedQuery($query='')
	{
		$this->_unbuffered_query = true;
		return $this->Query($query);
	}

	/**
	* Fetch
	* This function will fetch a result from the result set passed in.
	*
	* @param ?mysqli_result $result The result from calling Query. Returns an associative array (not an indexed based one)
	*
	* @see Query
	* @see SetError
	* @see StripslashesArray
	*
	* @return Array|False|Null Returns false if the result is empty. Otherwise returns the next result.
	*/
	public function Fetch($result = null)
	{
		if ($result === null) {
			$this->SetError('Resource is a null object');
			return false;
		}

		if (!$result instanceof mysqli_result) {
			$this->SetError('Resource is not valid: '.var_export($result, true));
			return false;
		}

        return mysqli_fetch_assoc($result);
	}

	/**
	* NextId
	* Fetches the next id from the sequence passed in
	*
	* @param Bool|String $seq Sequence Name to fetch the next id for.
	* @param String $idcolumn The name of the column for the id field. By default this is 'id'.
	*
	* @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
	*/
	public function NextId($seq='', $idcolumn='id')
	{
		if (!$seq) {
			return false;
		}
		$query = 'UPDATE '.$seq.' SET ' . $idcolumn . '=LAST_INSERT_ID(' . $idcolumn . '+1)';
		$result = $this->Query($query);
		if (!$result) {
			return false;
		}
		return mysqli_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 Fields to search against. This can be an array or a single field.
	* @param String $searchstring String to search for against the fields
	* @param Bool $booleanmode In MySQL, is this search in boolean mode ?
	*
	* @return Mixed Returns false if either fields or searchstring aren't present, otherwise returns a string to append to an sql statement.
	*/
	public function FullText($fields=null, $searchstring=null, $booleanmode=false)
	{
		if ($fields === null || $searchstring === null) {
			return false;
		}
		if (is_array($fields)) {
			$fields = implode(',', $fields);
		}
		if ($booleanmode) {
			$query = 'MATCH ('.$fields.') AGAINST (\''.$this->Quote($this->CleanFullTextString($searchstring)).'\' IN BOOLEAN MODE)';
		} else {
			$query = 'MATCH ('.$fields.') AGAINST (\''.$this->Quote($searchstring).'\')';
		}
		return $query;
	}

	/**
	 * CleanFullTextString
	 * Cleans and properly formats an incoming search query in to a string MySQL will love to perform fulltext queries on.
	 * For example, the and/or words are replaced with correct boolean mode formats, phrases are supported.
	 *
	 * @param String $searchstring The string you wish to clean.
	 * @return String The formatted string
	 */
	public function CleanFullTextString($searchstring)
	{
		$searchstring = strtolower($searchstring);
		$searchstring = str_replace("%", "\\%", $searchstring);
		$searchstring = preg_replace("#\*{2,}#s", "*", $searchstring);
		$searchstring = preg_replace("#([\[\]\|\.\,:])#s", " ", $searchstring);
		$searchstring = preg_replace("#\s+#s", " ", $searchstring);

		$words = [];

		// Does this search string contain one or more phrases?
		$quoted_string = false;
		if (strpos($searchstring, "\"") !== false) {
			$quoted_string = true;
		}
		$in_quote = false;
		$searchstring = explode("\"", $searchstring);
		foreach ($searchstring as $phrase) {
			$phrase = trim($phrase);
			if ($phrase != '') {
				if ($in_quote == true) {
					$words[] = "\"$phrase\"";
				} else {
					$split_words = preg_split("#\s{1,}#", $phrase, -1);
					if (!is_array($split_words)) {
						continue;
					}

					foreach ($split_words as $word) {
						if (!$word) {
							continue;
						}
						$words[] = trim($word);
					}
				}
			}
			if ($quoted_string) {
				$in_quote = !$in_quote;
			}
		}
		$searchstring = ''; // Reset search string
		$boolean = '';
		$first_boolean = '';
		foreach ($words as $k => $word) {
			if ($word == "or") {
				$boolean = "";
			} elseif ($word == "and") {
				$boolean = "+";
			} elseif ($word == "not") {
				$boolean = "-";
			} else {
				$searchstring .= " ".$boolean.$word;
				$boolean = '';
			}
			if ($k == 0) {
				if ($boolean == "-") {
					$first_boolean = "+";
				} else {
					$first_boolean = $boolean;
				}
			}
		}
		return $first_boolean.trim($searchstring);
	}

	/**
	* 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
	*/
	public function AddLimit($offset=0, $numtofetch=0)
	{
		$offset = intval($offset);
		$numtofetch = intval($numtofetch);

		if ($offset < 0) {
			$offset = 0;
		}
		if ($numtofetch <= 0) {
			$numtofetch = 10;
		}
		return ' LIMIT '.$offset.', '.$numtofetch;
	}

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

        if (!$resource instanceof mysqli_result) {
            $this->SetError('Resource is not valid: '.var_export($resource, true));
            return false;
        }

		mysqli_free_result($resource);

		return true;
	}

	/**
	* CountResult
	* Returns the number of rows returned for the resource passed in
	*
	* @param String|mysqli_result $resource The result from calling Query
	*
	* @see Query
	* @see SetError
	*
	* @return Int|False Number of rows from the result, returns false when resource is null
	*/
	public function CountResult($resource='')
	{
		if (empty($resource)) {
			$this->SetError('Resource is a null object');
			return false;
		}

		if (!$resource instanceof mysqli_result) {
			$resource = $this->Query($resource);
		}

		return mysqli_num_rows($resource);
	}

	/**
	* Concat
	* Concatentates multiple strings together. This method is mysql specific. It doesn't matter how many arguments you pass in, it will 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.
	*/
	public function Concat()
	{
		$num_args = func_num_args();
		if ($num_args < 1) {
			return func_get_arg(0);
		}
		$all_args = func_get_args();
		return 'CONCAT('.implode(',', $all_args).')';
	}

	/**
	* Quote
	* Quotes the string ready for database queries. Runs mysql_escape_string or mysql_real_escape_string depending on the php version.
	*
	* @param Mixed $string Variable you want to quote ready for database entry.
	*
	* @return Mixed $string with quotes applied to it appropriately
	*/
	public function Quote($string='')
	{
		if (is_string($string) || is_numeric($string) || is_null($string)) {
			return empty($string) ? '' : @mysqli_real_escape_string($this->connection, $string);
		} elseif (is_array($string)) {
			return array_map([$this, 'Quote'], $string);
		} elseif (is_bool($string)) {
			return (int) $string;
		} else {
			trigger_error("Invalid type passed to DB quote ".gettype($string), E_USER_ERROR);
			return false;
		}
	}

	/**
	* LastId
	*
	* Returns the last insert id
	*
	* @param String $seq
	*
	* @return Int|String Returns mysql_insert_id from the database.
	*/
	public function LastId($seq='')
	{
		return mysqli_insert_id($this->connection);
	}

	/**
	* CheckSequence
	*
	* Checks to make sure a sequence doesn't have multiple entries.
	*
	* @param String $seq
	*
	* @return Bool Returns true if there is exactly 1 entry in the sequence table, otherwise returns false.
	*/
	public function CheckSequence($seq='')
	{
		if (!$seq) {
			return false;
		}
		$query = "SELECT COUNT(*) AS count FROM " . $seq;
		$count = $this->FetchOne($query, 'count');
		if ($count == 1) {
			return true;
		}
		return false;
	}

	/**
	* ResetSequence
	*
	* Resets a sequence to a new id.
	*
	* @param String $seq The sequence name to reset.
	* @param Int $newid The new id to set the sequence to.
	*
	* @return Bool Returns true if the sequence is reset, otherwise false.
	*/
	public function ResetSequence($seq='', $newid=0)
	{
		if (!$seq) {
			return false;
		}

		if ($newid <= 0) {
			return false;
		}

		$query = "TRUNCATE TABLE " . $seq;
		$result = $this->Query($query);
		if (!$result) {
			return false;
		}

		// since a sequence table only has one field, we don't care what the fieldname is.
		$query = "INSERT INTO " . $seq . " VALUES (" . $newid . ")";
		$result = $this->Query($query);
		if (!$result) {
			return false;
		}

		return $this->CheckSequence($seq);
	}

	/**
	* 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.
	*/
	public function OptimizeTable($tablename='')
	{
		if (!$tablename) {
			return false;
		}
		$query = "OPTIMIZE TABLE " . $tablename;
		return $this->Query($query);
	}

	/**
	* NumAffected
	*
	* Returns the number of affected rows on success, and -1 if the last query failed.
	*
	* @param Mixed $null Placeholder for postgres compatability
	*
	* @return String|Int
	*/
	public function NumAffected($null=null)
	{
		return mysqli_affected_rows($this->connection);
	}

}
