<?php // vim:ts=4:sw=4:noet
/*====================================================================== 
Copyright (C) 2006-2009, Walter Doekes
This file is part of KjwLib-php45.
 
KjwLib-php45 is free software: you can redistribute it and/or modify 
it under the terms of the GNU General Public License as published by 
the Free Software Foundation, either version 3 of the License, or 
(at your option) any later version. 

KjwLib-php45 is distributed in the hope that it will be useful, 
but WITHOUT ANY WARRANTY; without even the implied warranty of 
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
GNU General Public License for more details. 

You should have received a copy of the GNU General Public License 
along with KjwLib-php45.  If not, see <http://www.gnu.org/licenses/>. 
======================================================================*/

require_once('KjwObject.php');

/** @defgroup sql Database (SQL) handling
 * 
 * Classes and functions for database connectivity.
 */

/** @ingroup sql
 *
 * KjwSql abstract class. Use derived classes that actually have a database
 * backend.
 */
class KjwSql extends KjwObject {
	var $_sqltype;		/**< SQL server type (mysql, pgsql, mssql, etc). */
	var $_hostname;		/**< Server ip/hostname. */
	var $_portnum;		/**< Port number. */
	var $_username;		/**< User name. */
	var $_password;		/**< Password. */
	var $_database;		/**< Database name. */
	var $_debug_query_count; /**< A query counter. Used for debugging purposes only. */
	var $_last_query;	/**< The last executed query, stored for debug purposes. */
	
	/**
	 * Constructor.
     *
	 * @param $sqltype A string identifier specifying the SQL server brand/make.
	 * @param $hostname Server name or IP.
	 * @param $portnum Port number of 0 if default.
	 * @param $username User name.
	 * @param $password Password.
	 * @param $database Database name.
	 */
	function KjwSql($sqltype, $hostname, $portnum, $username, $password, $database) {
		parent::KjwObject();
		$this->_sqltype = $sqltype;
		$this->_hostname = $hostname;
		$this->_portnum = $portnum;
		$this->_username = $username;
		$this->_password = $password;
		$this->_database = $database;
		$this->_debug_query_count = 0;
		if (KJW_DEBUG)
			$this->_last_query = array();
		else
			$this->_last_query = '';
	}

	/* [INHERITED DOCS] */
	function destroy() {
		$this->disconnect(); // Hope for succes.
		parent::destroy();
	}

	/**
	 * Get the sql type.
	 *
	 * @return Type of sql server as a string.
	 */
	function getType() {
		return $this->_sqltype;
	}

	/**
	 * Connect to the SQL server. 
	 *
	 * @return True.
	 * @todo Put timing on connect, so we don't have to wait 60secs on firewalled server.
	 */
	function connect() {
		return $this->notImplemented();
	}

	/**
	 * Disconnect from the SQL server.
	 *
	 * @return Success boolean.
	 */
	function disconnect() {
		return $this->notImplemented();
	}

	/**
	 * Begin a transaction / atomic operation.
	 */
	function beginWork() {
		return $this->execute('BEGIN WORK');
	}

	/**
	 * Commit a running transaction / atomic operation.
	 */
	function commitWork() {
		return $this->execute('COMMIT');
	}

	/**
	 * Revert/ignore all from the running transaction / atomic operation.
	 */
	function rollbackWork() {
		return $this->execute('ROLLBACK');
	}
	
	/**
	 * Execute a query.
	 *
	 * @param $query The query to execute.
	 * @return Returns a boolean specifying success or a KjwResult object on a select/show/describe/explain.
	 */
	function execute($query) {
		$this->_last_query = $query;
		return $this->notImplemented();
	}

	/**
	 * Get an entire array from a select query.
	 * You'll want to use this instead of execute() when you're using templates
	 * to show a couple of rows.
	 *
	 * @param $query Everything after the SELECT keyword.
	 * @param $max (optional) At most $max rows, use -1 to get all.
	 * @param $skip (optional) Skip $skip rows.
	 * @return An array with zero or more elements or false on error.
	 */
	function selectAll($query, $max = -1, $skip = 0) {
		$result = $this->execute("SELECT $query");
		if (!is_bool($result)) {
			$ret = array();
			$result->seekAbs($skip);
			while (($row = $result->getNext()) && $max--)
				array_push($ret, $row);
			$result->destroy();
			return $ret;
		} elseif ($result === false) {
			return false;
		}
		$this->croak('Database Data Transfer Failure', 'Execute returned \'true\'. Query was: ' . $this->_getLastQuery(), 2);
	}

	/**
	 * Get a single value (atom) from a database. We'll croak if there is more
	 * than one value or less than one.
	 *
	 * @param $query Everything after the SELECT keyword.
	 * @return An atomic value or false on error. You need to cast this yourself, unfortunately.
	 */
	function selectAtom($query) {
		$result = $this->execute("SELECT $query");
		if (!is_bool($result) && $result->size() === 1) {
			$arr = $result->getNext();
			$result->destroy();
			if (sizeof($arr) === 1)
				return array_shift($arr);
		} elseif ($result === false) {
			return false;
		}
		$this->croak('Database Data Transfer Failure', 'Atom: result count != 1. Query was: ' . $this->_getLastQuery(), 2);
	}

	/**
	 * Get a single value (atom) from a database or null if there is no result.
	 * We'll croak if there is more than one value.
	 *
	 * @param $query Everything after the SELECT keyword.
	 * @return An atomic value or false on error. You need to cast this yourself, unfortunately, unless it's null.
	 */
	function selectAtomOrNull($query) {
		$result = $this->execute("SELECT $query");
		if (!is_bool($result)) {
			if ($result->size() === 0) {
				$result->destroy();
				return null;
			} elseif ($result->size() === 1) {
				$arr = $result->getNext();
				$result->destroy();
				if (sizeof($arr) === 1)
					return array_shift($arr);
			}
		} elseif ($result === false) {
			return false;
		}
		$this->croak('Database Data Transfer Failure', 'AtomOrNull: result count > 1. Query was: ' . $this->_getLastQuery(), 2);
	}

	/**
	 * Get a single row from a database. We'll croak if there is more than
	 * one value or less than one.
	 *
	 * @param $query Everything after the SELECT keyword.
	 * @return A single row as an array or false on error.
	 */
	function selectAtomRow($query) {
		$result = $this->execute("SELECT $query");
		if (!is_bool($result) && $result->size() === 1) {
		 	$arr = $result->getNext();
			$result->destroy();
			return $arr;
		} elseif ($result === false) {
			return false;
		}
		$this->croak('Database Data Transfer Failure', 'AtomRow, result count != 1. Query was: ' . $this->_getLastQuery(), 2);
	}

	/**
	 * Get a single row from a database or null if there is no result.
	 * We'll croak if there is more than one value.
	 *
	 * @param $query Everything after the SELECT keyword.
	 * @return A single row as an array or null.
	 */
	function selectAtomRowOrNull($query) {
		$result = $this->execute("SELECT $query");
		if (!is_bool($result)) {
			if ($result->size() === 0) {
				$result->destroy();
				return null;
			} elseif ($result->size() === 1) {
		 		$arr = $result->getNext();
				$result->destroy();
				return $arr;
			}
		} elseif ($result === false) {
			return false;
		}
		$this->croak('Database Data Transfer Failure', 'AtomRowOrNull, result count > 1. Query was: ' . $this->_getLastQuery(), 2);
	}

	/**
	 * Makes sure that any variable is quoted like its correct type should be.
	 * Integers and booleans are returned as integers. Strings are returned as
	 * quoted strings with all quotes escaped.
	 * If the escaping mechanism is not known, all <32 and >127 characters REMOVED,
	 * otherwise the escaping for the specific database make is used.
	 * 
	 * @param $mixed The variable that needs to be returned as a safe entity.
	 * @return A quoted/escaped entity.
	 */
	function safeQuote($mixed) {
		if (is_string($mixed))
			return "'" . preg_replace("/[\x01-\x1f]/", '', str_replace("\x00", '', $mixed)) . "'";
		if (is_int($mixed) or is_float($mixed))
			return (double)$mixed;
		if (is_bool($mixed))
			return $mixed ? 1 : 0;
		if (is_null($mixed))
			return 'NULL';
		if (is_array($mixed) || is_object($mixed)) {
			$ret = array();
			foreach($mixed as $k => $v)
				$ret[$this->nameQuote($k)] = $this->safeQuote($v);
			return $ret;
		}
		return $this->croak('Library Component Error', 'Sql safeQuote fell through switch. Mixed parameter is "'
				. $mixed . '" as string, type is ' . gettype($mixed) . '.', 1);
	}

	/**
	 * Quote a name for an object (column or table). For MySQL this would
	 * be by surrounding the name with backticks.
	 *
	 * @param $string An identifier (column or table name) to be quoted.
	 * @return The quoted identifier.
	 */
	function nameQuote($string) {
		return $this->notImplemented();
	}

	/**
	 * A safe insert function.
	 *
	 * @param $table The table into which the arguments in $args should be inserted.
	 * @param $args An associative array of key value pairs to be inserted. If the value
	 *   is an array, its first element will be used verbatim (e.g.\ for array('NOW()')).
	 * @return Success boolean.
	 */
	function insertArray($table, $args) {
		assert(is_array($args));
		if (is_string($table)) {
			$table = $this->nameQuote($table);
		} elseif (is_array($table)) {
			foreach ($table as $k => $v)
				$table[$k] = $this->nameQuote($v);
			$table = implode('.', $table);
		} else {
			assert(0);
		}
		$keys = array();
		$values = array();
		foreach ($args as $k => $v) {
			$keys[] = $this->nameQuote($k);
			if (is_array($v)) {
				$values[] = $v[0]; // Special.. no quoting.
			} else {
				$values[] = $this->safeQuote($v);
			}
		}
			
		$query = "INSERT INTO $table ("
				. implode(', ', $keys) . ") VALUES ("
				. implode(', ', $values) . ")";
		return $this->execute($query);
	}
	
	/**
	 * A safe update function.
	 *
	 * @param $table The table where rows should be updated with the arguments in $args.
	 * @param $args An associative array of key value pairs to be inserted. If the value
	 *   is an array, its first element will be used verbatim (e.g.\ for array('NOW()')).
	 * @param $where An optional where-clause (without the "WHERE "). It's your duty
	 *   to make this clause SQL safe.
	 * @return Success boolean.
	 * @note Be sure to escape funky column names in the $where clause, the $table argument
	 *   is escaped by us.
	 */
	function updateArray($table, $args, $where = null) {
		assert(is_array($args) && is_string($where));
		if (is_string($table)) {
			$table = $this->nameQuote($table);
		} elseif (is_array($table)) {
			foreach ($table as $k => $v)
				$table[$k] = $this->nameQuote($v);
			$table = implode('.', $table);
		} else {
			assert(0);
		}
		$setArgs = array();
		foreach($args AS $k => $v) {
			if (is_array($v)) {
				$setArgs[] = $this->nameQuote($k) . ' = ' . $v[0]; // Special.. no quoting.
			} else {
				$setArgs[] = $this->nameQuote($k) . ' = ' . $this->safeQuote($v);
			}
		}		
		return $this->execute(
			"UPDATE $table SET " . implode(', ', $setArgs) . ($where !== null ? " WHERE $where" : '')
		);
	}
	
	/**
	 * How many rows were affected (touched) by the last INSERT, UPDATE, REPLACE or DELETE query.
	 * It's unspecified if an UPDATE count includes updates that weren't actual changes.
	 * I.e. either the WHERE-match-count is returned, or only the subset where the update was a change.
	 *
	 * @return The number of rows affected.
	 * @note Don't try to be funny and do SELECT or whatever queries before calling this.
	 */
	function affectedRows() {
		return $this->notImplemented();
	}
	
	/**
	 * Returns the id of the last INSERT clause that uses an IDENTITY/SEQUENCE/AUTO_INCREMENT
	 * value.
     *
	 * @return The new id.
	 * @note Don't try to be funny and do SELECT or whatever queries before calling this.
	 */
	function insertId() {
		return $this->notImplemented();
	}

	/**
	 * Returns an associative array with the word and nameQuoted value pairs. Use this when
	 * you need many nameQuoted values, for instance in a CREATE TABLE statement.
	 *
	 * @param $words A string of delimiter-separated words.
	 * @param $delimiter Optional delimiter, space by default.
	 * @return An associative array with nameQuote'd values.
	 */
	function qw($words, $delimiter = ' ') {
		$ret = array();
		$words = explode($delimiter, $words);
		foreach ($words as $word)
			$ret[$word] = $this->nameQuote($word);
		return $ret;
	}

	/**
	 * Return an array with statistical information about this KjwSql object.
	 *
	 * @return An associative array with key `query_count`.
	 */
	function debugStat() {
		return array('query_count' => $this->_debug_query_count);
	}

	/**
	 * [PRIVATE] Returns the last query used.
	 *
	 * @return A string or an array of strings.
	 */
	function _getLastQuery() {
		if (KJW_DEBUG)
			return end($this->_last_query);
		return $this->_last_query;
	}

	/**
	 * [PRIVATE] Store the last executed query internally.
	 *
	 * @param $query The query string to store.
	 */
	function _setLastQuery($query) {
		if (KJW_DEBUG) {
			array_push($this->_last_query, $query);
			$this->trace($query, 'DEBUG');
		} else {
			$this->_last_query = $query;
		}
		++$this->_debug_query_count;
	}
}


/**
 * Create a KjwSql object from an URL-style connection string.
 * Example: pgsql://username:password@host:port/database.
 *
 * @param $url The URL-style connection string.
 * @param $create_new Normally, you get an already existing object
 *                    if you called this with the same URL as before.
 *                    If you want a new object, set $create_new to
 *                    true.
 * @return A KjwSql object or null if uncreatable.
 * @note There may be underlying hacks in the subclasses to ensure that
 *       you're working on the right database. Some database client
 *       implementations reuse connections to the same named host
 *       automatically.
 * @note If you get an KjwSql object, there is no connection yet. The
 *       connection is established when needed (on an explicit call to
 *       connect() or the first execute() or safeQuote() or whatever).
 */
function kjwsql_from_url($url, $create_new = false) {
	static $sqls = array();
	if ($create_new || !isset($sqls[$url])) {
		$ua = parse_url($url);
		$port = (int)@$ua['port'];
		switch (@$ua['scheme']) {
//		case 'csvsql': require_once(dirname(__FILE__) . '/KjwCsvSql.php');
//			
		case 'mysql': require_once(dirname(__FILE__) . '/KjwMySql.php');
			$sqls[$url] = new KjwMySql(@$ua['host'], $port, @$ua['user'], @$ua['pass'], substr(@$ua['path'], 1)); break;
		case 'mssql': require_once(dirname(__FILE__) . '/KjwMsSql.php');
			$sqls[$url] = new KjwMsSql(@$ua['host'], $port, @$ua['user'], @$ua['pass'], substr(@$ua['path'], 1)); break;
		case 'pgsql': require_once(dirname(__FILE__) . '/KjwPgSql.php');
			$sqls[$url] = new KjwPgSql(@$ua['host'], $port, @$ua['user'], @$ua['pass'], substr(@$ua['path'], 1)); break;
		case 'xmlsql': require_once(dirname(__FILE__) . '/KjwXmlSql.php');
			$sqls[$url] = new KjwXmlSql(@$ua['host'], $port, @$ua['user'], @$ua['pass'], substr(@$ua['path'], 1)); break;
		default: trigger_error('Unknown SQL type: ' . @$ua['scheme'], E_USER_ERROR);
			return null;
		}
	}
	return $sqls[$url];
}

?>