<?php
/* $Id$ */
namespace Mammut\DB\MSSQL;

(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use Mammut\Exception\ImplementationException;
use Mammut\DB\DBException;
use Mammut\DB\SQLException;
use Mammut\DB\Model\TableInfo;
use Mammut\DB\Model\ColumnInfo;

/**
 * New microsoft api implementation
 *
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB\Adapter\MSSQL
 */
class MSSQLv2 extends MSSQLBase {
	private $last_affected_rows = 0;

	public function __construct($cstring, $user, $password, array $options = array(), \Mammut\Cache\iCache $cache = NULL) {
		$pers = in_array(self::OPT_PERSISTENT, $options);
		
		if(!extension_loaded('sqlsrv'))
			throw new \Mammut\Exception\ExtensionException('sqlsrv');
		$dbname = '';
		// adress,port style
		if(preg_match('#^([a-zA-Z0-9\.-]+)(,[0-9]+){0,1}/([a-zA-Z0-9_-]+)$#', $cstring, $match)) {
			$conInfo = array("UID" => $user,"PWD" => $password,"Database" => $match[3], "CharacterSet" => "UTF-8");
			$this->dbi = sqlsrv_connect($match[1], $conInfo);
			if($this->dbi === false)
				throw new DBException('connection failed: ' . $this->_getLastErrors());
			$dbname = $match[3];
		}
		// instance naming
		elseif(preg_match('#^([a-zA-Z0-9\._-]+)(\\\\[a-zA-Z0-9]+){0,1}/([a-zA-Z0-9_-]+)$#', $cstring, $match)) {
			$conInfo = array("UID" => $user,"PWD" => $password,"Database" => $match[3], "CharacterSet" => "UTF-8");
			$this->dbi = sqlsrv_connect($match[1] . $match[2], $conInfo);
			if($this->dbi === false)
				throw new DBException('connection failed: ' . $this->_getLastErrors());
			$dbname = $match[3];
		}
		else
			throw new \UnexpectedValueException('$cstring is invalid: ' . $cstring);

		$this->dialect = new \Mammut\DB\Sql\Dialect\SqlServer($this->dbi);
		$this->setDatabase($dbname);
		
		// set some server-dependend things to default styles
		sqlsrv_query($this->dbi, 'SET DATEFORMAT ymd'); // yyyy-mm-dd date format
		sqlsrv_query($this->dbi, 'SET IMPLICIT_TRANSACTIONS OFF');
		sqlsrv_configure('WarningsReturnAsErrors', 0);
		$this->logDebug('db connection successfull');
	}

	private function _getLastErrors() {
		$errmsg = array();
		$errorlist = sqlsrv_errors(SQLSRV_ERR_ERRORS);
		if(!is_array($errorlist))
			return false;
		foreach($errorlist as $error)
			$errmsg[] = '(' . $error['code'] . ': ' . $error['message'] . ')';
		if(count($errmsg) == 0)
			return false;
		return implode(' ', $errmsg);
	}

	public function close() {
		sqlsrv_close($this->dbi);
		parent::close();
	}

	public function getServer() {
		$info = sqlsrv_server_info($this->dbi);
		return $info['SQLServerName'];
	}

	public function setDatabase($name) {
		if(!sqlsrv_query($this->dbi, "USE [$name]"))
			throw new DBException('error while setting database to ' . $name . ': ' . $this->_getLastErrors());
	}

	public function startTransaction() {
		sqlsrv_begin_transaction($this->dbi);
	}

	public function commit() {
		sqlsrv_commit($this->dbi);
	}

	public function rollback() {
		sqlsrv_rollback($this->dbi);
	}

	public function query($query, $limit = -1, $skip = 0) {
		$query = $this->checkQuery($query);
		$limit = intval($limit);
		$skip = intval($skip);
		
		if($limit >= 0) {
			if(preg_match('#^\s*SELECT\s+(.*)#i', $query, $match))
				$query = 'SELECT TOP ' . ($limit + $skip) . ' ' . $match[1];
		}
		
		$this->logTrace('sending new query: ' . $query);
		
		$this->qcount++;
		$result = sqlsrv_query($this->dbi, $query, array(), array("Scrollable" => SQLSRV_CURSOR_STATIC));
		if(($e = $this->_getLastErrors()) !== false)
			throw new SQLException($e, $query, 4711);
		
		if(is_resource($result)) {
			$this->last_affected_rows = sqlsrv_num_rows($result);
			if($skip > 0) {
				$this->last_affected_rows = ($skip < $this->last_affected_rows) ? ($this->last_affected_rows - $skip) : 0;
				sqlsrv_fetch($result, SQLSRV_SCROLL_ABSOLUTE, $skip - 1);
			}
			$result = new Result($this->dbi, $result, $skip, 2);
			if ($result->getColumnCount() == 0) {
				// no columns -> INSERT/UPDATE/DELETE
				$affected = $this->getAffectedRowCount();
				$result->close();
				if ($affected >= 0) // affected rows -> return number
					return $affected;
				// system command
				return true;
			}
			return $result;
		}
		else {
			$this->last_affected_rows = sqlsrv_rows_affected($result);
			return $result;
		}
	}

	public function executeP($query, array $param = array()) {
		$query = $this->checkQuery($query);
		$this->logTrace('execute new stmt: ' . $query);
		
		$this->qcount++;
		$result = sqlsrv_query($this->dbi, $query, $param);
		if(($e = $this->_getLastErrors()) !== false)
			throw new SQLException($e, $query, 4711);
		$count = sqlsrv_rows_affected($result);
		sqlsrv_free_stmt($result);
		return $count;
	}

	public function prepareStatement($query, $limit = -1, $skip = 0) {
		$query = $this->checkQuery($query);
		$stmt = new StmtV2($this->dbi, $query, $limit, $skip);
		return $stmt;
	}

	public function getObject($query, $class = false, $param = array()) {
		$query = $this->checkQuery($query);
		$this->qcount++;
		$result = sqlsrv_query($this->dbi, $query);
		if(($e = $this->_getLastErrors()) !== false)
			throw new SQLException($e, $query, 4711);
		
		$this->last_affected_rows = sqlsrv_rows_affected($result);
		
		// fetch result
		if($class) {
			if(!class_exists($class))
				throw new \BadMethodCallException('class ' . $class . ' is not defined');
			$data = sqlsrv_fetch_object($result, $class, count($param) > 0 ? $param : NULL, SQLSRV_SCROLL_NEXT);
		}
		else
			$data = sqlsrv_fetch_object($result, NULL, NULL, SQLSRV_SCROLL_NEXT);
		
		return $data;
	}

	public function getArray($query) {
		$query = $this->checkQuery($query);
		$this->qcount++;
		$result = sqlsrv_query($this->dbi, $query);
		if(($e = $this->_getLastErrors()) !== false)
			throw new SQLException($e, $query, 4711);
		
		$this->last_affected_rows = sqlsrv_rows_affected($result);
		// fetch result
		$data = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);
		return $data;
	}

	public function getRow($query) {
		$query = $this->checkQuery($query);
		$this->qcount++;
		$result = sqlsrv_query($this->dbi, $query);
		if(($e = $this->_getLastErrors()) !== false)
			throw new SQLException($e, $query, 4711);
		
		$this->last_affected_rows = sqlsrv_rows_affected($result);
		// fetch result
		$data = sqlsrv_fetch_array($result, SQLSRV_FETCH_NUMERIC);
		
		return $data;
	}

	public function getAffectedRowCount() {
		return $this->last_affected_rows;
	}

	public function tableList() {
		$result = false;
		$this->qcount++;
		$sql = 'SELECT Table_name AS "Table name" FROM INFORMATION_SCHEMA.TABLES  WHERE Table_type = \'BASE TABLE\' AND (Objectproperty (Object_id(Table_name), \'IsMsShipped\') = 0 OR Objectproperty (Object_id(Table_name), \'IsMsShipped\') IS NULL)';
		$result = sqlsrv_query($this->dbi, $sql);
		if(($e = $this->_getLastErrors()) !== false)
			throw new SQLException($e, $sql, 4711);
		if(is_resource($result)) {
			$tables = array();
			while($next = sqlsrv_fetch_array($result, SQLSRV_FETCH_NUMERIC)) {
				$tables[] = $next[0];
			}
			return $tables;
		}
		else
			return false;
	}
	
	public function getServerVersion() {
		$info = sqlsrv_server_info($this->dbi);
		return $info['SQLServerVersion'];
	}
}