<?php
/**
 * This file contains the iwp_mysql base class and supporting global functions / definitions
 *
 * @version $Id$
 * @author Gwilym <gwilym.evans@interspire.com>
 *
 * @package IWP
 * @subpackage IWP_API
 */

/**
 * This class is basically just an extension of the shared MySQLDb so that we can easily add IWP-specific code with less chance of conflicts.
 *
 * @package IWP
 * @subpackage IWP_API
 */
class iwp_mysql extends MySQLDb
{
	/**
	 * 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 iwp_mysql Returns the instantiated object
	 **/
	public static function getInstance ()
	{
		if(!isset(self::$Instance)){
			$classname =  __CLASS__;
			self::$Instance = new $classname;
		}
		return self::$Instance;
	}

	/**
	 * Synchronise a many-to-many association table with the provided data, deleting any relationships not specified and adding any that aren't in the database.
	 *
	 * @param string $tableName Name of the table in the database. No table prefixes are added.
	 * @param string $leftColumn
	 * @param string|integer $leftValue
	 * @param string $rightColumn
	 * @param array $rightValues
	 *
	 * @return boolean Returns false if the operation failed, otherwise true.
	 */
	public function SynchroniseAssociations ($tableName, $leftColumn, $leftValue, $rightColumn, $rightValues)
	{
		$sql = sprintf("SELECT DISTINCT `%s` AS `right` FROM `%s` WHERE `%s` = '%s' ORDER BY `%s`", $rightColumn, $tableName, $leftColumn, $leftValue, $rightColumn);
		$resource = $this->Query($sql);

		$leftValue = $this->Quote($leftValue);

		if (!$resource) {
			return false;
		}

		//	check the database list against our provided list
		while ($row = $this->Fetch($resource)) {
			$rightValue = $row['right'];
			$index = array_search($rightValue, $rightValues);
			if ($index === false) {
				//	if the value fetched from the database is not in our supplied list of values, remove it from the database
				$sql = sprintf("DELETE FROM `%s` WHERE `%s` = '%s' AND `%s` = '%s'", $tableName, $leftColumn, $leftValue, $rightColumn, $this->Quote($rightValue));
				$this->Query($sql);
			} else {
				//	otherwise, remove it from that list so that we don't add it at the end of this process
				unset($rightValues[$index]);
			}
		}

		//	add any remaining items on our provided list to the database
		foreach ($rightValues as $rightValue) {
			$sql = sprintf("INSERT INTO `%s` (`%s`, `%s`) VALUES ('%s', '%s')", $tableName, $leftColumn, $rightColumn, $leftValue, $this->Quote($rightValue));
			$this->Query($sql);
		}

		return true;
	}

	/**
	 * ReplaceQuery
	 * Takes an array of fields and values and constructs a replace query (this will delete and re-insert any duplicate pkey records)
	 *
	 * @param String $table The name of the table to replace a row into
	 * @param Array $replaces List of fields as the keys and field values as the array values
	 *
	 * @return Mixed Replaced id (int) or false (bool) for failure
	 *
	 */
	public function ReplaceQuery($table, $replaces)
	{
		foreach ($replaces as $field=>$value){
			$fieldList[] = $field;
			$valueList[] = $this->Quote($value);
		}

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

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

	/**
	 * InsertOnDuplicateKeyUpdateQuery
	 * Takes an array of fields and values and constructs an INSERT ... ON DUPLICATE KEY UPDATE query (attempts to insert but otherwise updates if a duplicate pkey was found)
	 *
	 * @param String $table The name of the table to replace a row into
	 * @param Array $inserts List of fields as the keys and field values as the array values to insert with.
	 * @param Array $updates Optional. List of fields as the keys and field values as the array values to update with if a pkey duplicate is found. If this is not specified, the $inserts array will be used.
	 *
	 * @return Mixed Inserted id (int) or false (bool) for failure
	 *
	 */
	public function InsertOnDuplicateKeyUpdateQuery ($table, $inserts, $updates = null) {
		foreach ($inserts as $field=>$value) {
			$fieldList[] = $field;
			$valueList[] = $this->Quote($value);
		}

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

		if (!is_null($updates)) {
			//	switch to the specified update values
			foreach ($updates as $field=>$value) {
				$fieldList[] = $field;
				$valueList[] = $this->Quote($value);
			}
		}

		$first = true;
		foreach ($fieldList as $index => $field) {
			if ($first) {
				$first = false;
			} else {
				$query .= ",";
			}
			$value = $valueList[$index];	//	already quoted above
			$query .= "`". $field ."`='". $value ."'";
		}

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

	/**
	 * In PHP, connect is OK to call multiple times as it will internally re-use connections with the same details.
	 * But there's no built-in way of knowing if the connection was fresh or re-used. On a fresh connection, we want to
	 * call statements to set our character set and time zone. So, like PHP, we store a list of connection resources.
	 * If the PHP resource handlers do not match, we know we have a fresh connection and can do an 'on connect'.
	 *
	 * @var resource
	 */
	private static $_connections = array();

	/**
	* Connect
	* This function will connect to the database based on the details passed in. This is extended from the base class to automatically set utf8 on our connection for IWP.
	*
	* @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.
	*/
	public function Connect ($hostname='', $username='', $password='', $databasename='')
	{
		$connection = parent::Connect($hostname, $username, $password, $databasename);
		$key = "$hostname\t$username\t$password\t$databasename";
		$lastconnection = @self::$_connections[$key];

		if ($connection && $connection !== $lastconnection) {
			//	this is out database 'on connect', we know we have a fresh database connection here
			//	set our default character set and timezone values
			self::$_connections[$key] = $connection;
			$this->Query("SET NAMES 'utf8'");

			$offset = date('P');
			$this->Query("SET time_zone = '". $offset ."'");
		}
		return $connection;
	}
}