<?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/>. 
======================================================================*/

if (!function_exists('sybase_connect'))
	trigger_error('sybase_* functions not defined. Install php*-sybase.', E_USER_ERROR);
require_once(dirname(__FILE__) . '/KjwSql.php');
require_once(dirname(__FILE__) . '/KjwArrayResultSet.php');
require_once(dirname(__FILE__) . '/KjwMsResultSet.php');

/** @ingroup sql
 *
 * KjwMsSql is the mssql implementation of the KjwSql abstract class.
 * Uses the KJW_DIE_ON_TRANSIENT_ERROR define.
 *
 * @todo Currently, you need to use executeLowLevel if you're using USE <new_database>.
 *       Should fix so execute "sees" it as well, and update own $database.
 */
class KjwMsSql extends KjwSql {
	var $_dbconn;		/**< Database resource */
	var $_quotedDbname;	/**< Database name, quoted */
	var $_insertId;		/**< Insert ID / Identity ID of last query (insert/update). */
	var $_affectedRows;	/**< Count of rows affected with last query (insert/update). */
	var $_messages;		/**< Server error messages */

	/**
	 * Construct a KjwMsSql object.
     *
	 * @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 KjwMsSql($hostname, $portnum, $username, $password, $database) {
		parent::KjwSql('mssql', $hostname, $portnum ? $portnum : 1433, $username, $password, $database);
		$this->_dbconn = null; // null = unconnected, false = failed connect
		$this->_quotedDbname = $this->nameQuote($database);
		$this->_insertId = $this->_affectedRows = null;
		$this->_messages = array();

		$this->_compatMode = 'unknown';
		if (function_exists('sybase_set_message_handler')
				&& function_exists('sybase_min_client_severity')
				&& function_exists('sybase_min_server_severity')) {
			$this->_compatMode = 'sybase-ct';
		} else if (function_exists('sybase_min_message_severity')) {
			$this->_compatMode = 'sybase-db';
		}

		if ($this->_compatMode == 'sybase-ct') {
			sybase_min_client_severity(0);
			sybase_min_server_severity(0);
		} else if ($this->_compatMode == 'sybase-db') {
			sybase_min_message_severity(15); // you may need to tweak this
		}
	}

	/* [INHERITED DOCS] */
	function destroy() {
		$this->disconnect();
		parent::destroy();
	}
	
	/* [INHERITED DOCS] */
	function connect() {
		if ($this->_dbconn)
			return true;
		$this->_dbconn = false; // If we fail, we'll be false instead of null.
		// Freetds and/or the MsSql server refuse connections sometimes..
		// We'll attempt to circumvent this by reconnecting after a small wait.
		// Note that we have the connect timeout set to 10 seconds in the freetds.conf.
		// (If it were longer, the N retries could become a very long wait.)
		for ($i = 1; ; ++$i) {
			if (($res = @sybase_connect($this->_hostname . ':' . $this->_portnum, $this->_username, $this->_password, 'KjwApp')) != false)
				break;
			if ($i == 5 && $res == false)  {
				global $kjw_suppressed_error;
				$this->setError("Connection to database (temporarily) failed after {$i}th try; host={$this->_hostname}:{$this->_portnum}, "
						. "user={$this->_username}, db={$this->_database}. "
						. $kjw_suppressed_error);
				if (KJW_DIE_ON_TRANSIENT_ERROR)
					$this->croak('Database Connection Failure', $this->getError(), 2);
				return false;
			}
			usleep(max(500000 << $i, 8000000));
		}
		// Woot, success!
		switch ($this->_compatMode) {
		case 'sybase-ct':
			// Set our message handler to catch all messages
			if (@sybase_set_message_handler(array(&$this, '_messageHandler'), $res) == false) {
				global $kjw_suppressed_error;
				$this->setError("sybase_set_message_handler failed: $kjw_suppressed_error ({$this->_hostname}:{$this->_portnum})");
				if (KJW_DIE_ON_TRANSIENT_ERROR)
					$this->croak('Database Connection Failure', $this->getError(), 2);
				return false;
			}
			break;
		default:
			// Set lots of settings to be compatible with sybase-ct
			$settings = array('quoted_identifier on', 'arithabort off', 'numeric_roundabort off',
					'ansi_warnings on', 'ansi_padding on', 'ansi_nulls on', 'concat_null_yields_null on',
					'cursor_close_on_commit off', 'implicit_transactions off', 'language us_english',
					'dateformat mdy', 'datefirst 7', 'transaction isolation level read committed');
			foreach ($settings as $setting) {
				if (@sybase_query("SET $setting") === false)
					$this->trace("Setting 'SET $setting' failed.", 'WARNING');
			}
		}

		// Select database
		if (@sybase_select_db($this->nameQuote($this->_database), $res) == false) {
			$this->setError($this->getLastMessage());
			if (KJW_DIE_ON_TRANSIENT_ERROR)
				$this->croak('Database Connection Failure', $this->getError(), 2);
			@sybase_close($res);
			return false;
		}
		$this->_dbconn = $res;
		return true;
	}

	/* [INHERITED DOCS] */
	function disconnect() {
		if (!$this->_dbconn)
			return true;
		if ($ret = @sybase_close($this->_dbconn))
			$this->_dbconn = null;
		return $ret;
	}
	
	/* [INHERITED DOCS] */
	function beginWork() {
		// BEGIN WORK does not work on MsSql.
		return $this->execute('BEGIN TRANSACTION');
	}

	/* [INHERITED DOCS] */
	function execute($query) {
		// Part 1, initialize
		if (!$this->_execute_1($query))
			return false;
		// Part 2, preprocess
		list($query, $is_insert_update, $is_select) = $this->_execute_2($query);
		// Part 3, execute
		if (($sybase_result = $this->_execute_3($query)) === false)
			return false;
		// Part 4, postprocess
		return $this->_execute_4($sybase_result, $is_insert_update, $is_select);
	}

	/**
	 * A variant on the execute() method that does not reset/reselect the
	 * database and does not set the last_insert_id or error messages.
	 * The normal execute does extra stuff to keep the behaviour compatible
	 * with the KjwSql standard. This one does not. You'll have to live
	 * with sybase_* and T-SQL specific weirdness.
	 *
	 * If you use "USE <database>" statements in your queries, you want to
	 * use this one. *Note* *that* *you* *cannot* *use* *multiple* *KjwSql*
	 * *objects* *safely* when you're using this function.
	 * This one is perfect for executing a batch of create
	 * statements.
	 *
	 * @param $query The query to execute.
	 * @return A success boolean, no resultset is ever returned.
	 */
	function executeLowLevel($query) {
		// You can definately not trust these values.
		$this->_insertId = $this->_affectedRows = null;
		// Part 1, initialize
		if (!$this->_execute_1($query))
			return false;
		// Part 3, execute
		$sybase_result = $this->_execute_3($query);
		if (is_bool($sybase_result))
			return $sybase_result;
		sybase_free_result($sybase_result);
		return true;
	}

	/**
	 * Low level execute method internals, part 1.
	 *
	 * @param $query The query to execute.
	 * @return Whether we're connected to the database, boolean.
	 */
	function _execute_1($query) {
		$this->_setLastQuery($query);
		if ($this->_dbconn === null)
			$this->connect(); // Auto-connect once.
		if ($this->_dbconn === false)
			return false;
		return true;
	}

	/**
	 * Low level execute method internals, part 2.
	 *
	 * @param $query The query to execute.
	 * @return An updated query string, whether the query is an insert/update and whether the
	 *         query is a select.
	 */
	function _execute_2($query) {
		// If query is an insert/update, we add code to select @@IDENTITY/@@ROWCOUNT.
		if (preg_match('!^\s*(insert|update)\s+!i', $query)) {
			$this->_insertId = $this->_affectedRows = null;
			$is_insert_update = true;
			$is_select = false;
			// Some databases require a BEGIN TRANSACTION around the @@ROWCOUNT as well... haven't found out why.
			$query = "BEGIN TRANSACTION ; $query ; SELECT @@ROWCOUNT AS rc, @@IDENTITY AS id ; COMMIT";
		} elseif (preg_match('!^\s*select\s+!i', $query)) {
			$this->_insertId = $this->_affectedRows = null;
			$is_insert_update = false;
			$is_select = true;
		} else {
			$is_insert_update = $is_select = false;
		}

		// As seen in bugs: http://bugs.php.net/bug.php?id=36639 and http://bugs.php.net/bug.php?id=42544
		// All MSSQL links started with the same arguments, share the same link.
		// Therefore we have to reselect the database and we have to reinitialize the message handler.
		return array("USE {$this->_quotedDbname} ; $query", $is_insert_update, $is_select);
	}

	/**
	 * Low level execute method internals, part 3.
	 *
	 * @param $query The query to execute.
	 * @return A sybase result resource, this can be a boolean as well.
	 */
	function _execute_3($query) {
		// Run it.
		$this->_clearLastMessage();
		// Again for the same-link-different-object-problem, ensure the right message handler is
		// initialized.
		if ($this->_compatMode == 'sybase-ct')
			// (Don't suppress errors, this function should work by now.)
			sybase_set_message_handler(array(&$this, '_messageHandler'), $this->_dbconn);

		if (($sybase_result = @sybase_query($query, $this->_dbconn)) === false) {
			if ($this->_compatMode != 'sybase-ct') // non-sybase-ct does not call _messageHandler
				array_push($this->_messages, sybase_get_last_message());
			$errmsg = $this->getLastMessage();
			/*if (($sybase_result = sybase_query('SELECT @@ERROR AS error', $this->_dbconn)) !== false) {
				$row = sybase_fetch_assoc($sybase_result);
				$errmsg .= ' [' . array_shift($row) . ']';
			}*/
			$errmsg .= "\n   Query was: " . $this->_getLastQuery();
			$this->croak('Database Data Transfer Failure', "sybase_query failed: $errmsg", 3);
			return false;
		}
		return $sybase_result;
	}

	/**
	 * Low level execute method internals, part 4.
	 *
	 * @param $sybase_result A sybase resource.
	 * @param $is_insert_update True if the query was an insert or update statement.
	 * @param $is_select True if the query was a select statement.
	 * @return A KjwSqlResultSet (KjwSqlMsResultSet).
	 */
	function _execute_4($sybase_result, $is_insert_update, $is_select) {
		if (is_bool($sybase_result)) {
			// Ms Sql returns true instead of an empty result on empty select.
			if ($is_select)
				return new KjwArrayResultSet(array());
			return true;
		} elseif ($is_insert_update && sybase_num_rows($sybase_result) == 1) {
			$row = sybase_fetch_assoc($sybase_result);
			sybase_free_result($sybase_result);
			$this->_affectedRows = array_shift($row);
			if (is_integer($this->_affectedRows))
				$this->_affectedRows = (int)$this->_affectedRows;
			$this->_insertId = array_shift($row);
			if (is_integer($this->_insertId))
				$this->_insertId = (int)$this->_insertId;
			return true;
		}
		return new KjwMsResultSet($sybase_result);
	}

	/* [INHERITED DOCS] */
	function safeQuote($mixed) {
		if (is_string($mixed))
			// Notes:
			// - Can't find docs on exact escaping. We'll do s/'/''/g and s/\0//g.
			// - We usually use UTF8.. this is converted to UCS2 if we use NVARCHARs.
			//   Return N'string' instead of 'string'.
			return "N'" . str_replace("'", "''", str_replace("\x00", "", $mixed)) . "'";
		return parent::safeQuote($mixed);
	}

	/* [INHERITED DOCS] */
	function nameQuote($string) {
		return '[' . str_replace(']', ']]', preg_replace("/[\x01-\x1f]/", '',
				str_replace("\x00", '', $string))) . ']';
	}

	/* [INHERITED DOCS] */
	function affectedRows() {
		// sybase_affected_rows($this->_dbconn) does *not* work with MsSql or something..
		// SELECT @@ROWCOUNT *only* works within the same execute statement
		//if ($this->_affectedRows === null)
		//	$this->_affectedRows = $this->selectAtom('@@ROWCOUNT');
		return $this->_affectedRows;
	}
	
	/* [INHERITED DOCS] */
	function insertId() {
		//return $this->selectAtom('@@IDENTITY');
		return $this->_insertId;
	}

	/**
	 * Returns the last info/error messages as a string.
	 *
	 * @return A message.
	 */
	function getLastMessage() {
		return implode("\n     ", $this->_messages);
	}

	/**
	 * Clear stored messages. Used internally only.
	 */
	function _clearLastMessage() {
		$this->_messages = array();
	}

	/**
	 * Message handler. We need to use this because the sybase_get_last_message stores only
	 * the latest message in a series of messages.
	 * Could be semi-private.. no one should reference it by name, but others should call it
	 * because it is a callback.
	 *
	 * sybase_min_client_severity and sybase_min_server_severity decide which messages
	 * get passed to this callback.
	 *
	 * @param $message The error number.
	 * @param $severity The error severity (integer, likely 10 or above).
	 * @param $state Some kind of state (what\?).
	 * @param $lineNumber Line number.
	 * @param $description The error message as a string.
	 */
	function _messageHandler($message, $severity, $state, $lineNumber, $description) {
		array_push($this->_messages, "$description [err=$message, line=$lineNumber, sev=$severity, stat=$state]");
	}
}

?>