<?php
/* $Id$ */
namespace Mammut\DB\Oracle;

(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use Mammut\Exception\ExtensionException;
use Mammut\Exception\ImplementationException;
use Mammut\DB\DBException;
use Mammut\DB\SQLException;
use Mammut\DB\Model\TableInfo;
use Mammut\DB\Sql\Expression\SQLFunction;
use Mammut\Cache\iCache;

/**
 * DB implementation for Oracle SQL server
 * 
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB\Adapter\Oracle
 */
class Oracle extends \Mammut\DB\DB {
	const _VERSION_ = '1.1.0.0';
	
	/**
	 *
	 * @var Resource
	 */
	private $db = false;
	private $lastResult = NULL;
	private $autoCommit;

	/**
	 * Create a new database connection
	 *
	 * @param string $cstring
	 *        	database cinnection info [host[:port]]/[database[.schema]]
	 * @param string $user
	 *        	the username for the database
	 * @param string $password
	 *        	the password for the connection
	 * @param array $options
	 *        	optional options, see MDFB constains
	 * @throws ExtensionException if the pgsql extension is missing
	 * @throws InvalidArgumentException if the cstring is invalid
	 * @throws DBException on connection errors
	 */
	public function __construct($cstring, $user, $password, array $options, iCache $cache = NULL) {
		if(!extension_loaded('oci8'))
			throw new \Mammut\Exception\ExtensionException('oci8');
		$pers = in_array(self::OPT_PERSISTENT, $options);
		
		$this->dialect = new \Mammut\DB\Sql\Dialect\Oracle();
		
		if(preg_match('#^([a-zA-Z0-9\.-]+)$#', $cstring, $match)) // connect via network name
			$this->db = $pers ? oci_pconnect($user, $password, $cstring, 'AL32UTF8') : oci_connect($user, $password, $cstring, 'AL32UTF8');
		elseif(preg_match('#^[/]{0,2}([a-zA-Z0-9\.-]+)(:[0-9]+){0,1}/([a-zA-Z0-9_\.-]+$)#', $cstring, $match)) // connect via easy connect
			$this->db = $pers ? oci_pconnect($user, $password, $cstring, 'AL32UTF8') : oci_connect($user, $password, $cstring, 'AL32UTF8');
		else
			throw new \InvalidArgumentException('$cstring is invalid: ' . $cstring);
		
		if($this->db == false)
			throw new DBException('connection failed: ' . self::buildErrorString(oci_error()));
		// set ISO format as default, as used by other DBs
		$this->quickExec("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'");
		// use common version with . as decimal separator
		$this->quickExec("ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'");
	}

	public function close() {
		oci_close($this->db);
		parent::close();
	}

	protected function quickExec($query) {
		$query = $this->checkQuery($query);
		
		$stid = oci_parse($this->db, $query);
		$result = oci_execute($stid);
		$errors = oci_error($stid);
		oci_free_statement($stid);
		if(!$result) {
			throw new SQLException($this->buildErrorString($errors), $query);
		}
		return $result;
	}

	public static function buildErrorString(array $input) {
		$st = '';
		if(isset($input[0]))
			foreach($input as $error)
				$st .= "{$error['code']}: {$error['message']} @ {$error['offset']} \n";
		else
			$st .= "{$input['code']}: {$input['message']} @ {$input['offset']} \n";
		return $st;
	}

	public function setDatabase($name) {
		throw new ImplementationException("Not implemented");
	}

	public function getDatabase() {
		$result = $this->query("SELECT sys_context( 'userenv', 'current_schema' ) AS s FROM dual");
		$o = $result->fetchObject();
		$result->close();
		if(is_object($o))
			return $o->S;
		return '?';
	}

	public function isTransactionSupported() {
		return true;
	}

	public function setAutocommit($doAutoCommit) {
		$this->autoCommit = $doAutoCommit;
	}

	public function startTransaction() {
		throw new ImplementationException("Not implemented");
	}

	public function commit() {
		oci_commit($this->db);
	}

	public function rollback() {
		oci_rollback($this->db);
	}

	public function query($query, $limit = -1, $skip = 0) {
		$query = $this->checkQuery($query);
		$this->logDebug('sending new query: ' . $query);
		$stid = oci_parse($this->db, $query);
		$ok = oci_execute($stid);
		$errors = oci_error($stid);
		if(!$ok)
			throw new SQLException(self::buildErrorString($errors), $query);
		
		$result = new Result($stid);
		return $result;
	}

	public function getInsertId() {
		throw new ImplementationException("Not implemented");
	}

	public function getAffectedRowCount() {
		throw new ImplementationException("Not implemented");
	}

	public function prepareStatement($query, $limit = -1, $skip = 0) {
		$query = $this->checkQuery($query);
		return new Statement($this->db, $query);
	}

	public function getArray($query) {
		$query = $this->checkQuery($query);
		$this->logDebug('sending new query: ' . $query);
		$stid = oci_parse($this->db, $query);
		$ok = oci_execute($stid);
		$errors = oci_error($stid);
		
		if(!$ok)
			throw new SQLException(self::buildErrorString($errors), $query);
		
		$result = new Result($stid);
		$data = $result->fetchArray();
		$result->close();
		return $data;
	}

	public function getRow($query) {
		$query = $this->checkQuery($query);
		$this->logDebug('sending new query: ' . $query);
		$stid = oci_parse($this->db, $query);
		$ok = oci_execute($stid);
		$errors = oci_error($stid);
		
		if(!$ok)
			throw new SQLException(self::buildErrorString($errors), $query);
		
		$result = new Result($stid);
		$data = $result->fetchRow();
		$result->close();
		return $data;
	}

	public function tableList() {
		$stid = oci_parse($this->db, 'SELECT table_name FROM user_tables');
		oci_execute($stid);
		$errors = oci_error($stid);
		if($errors !== false && count($errors) > 0) {
			oci_free_statement($stid);
			$st = '';
			if(is_array($errors))
				foreach($errors as $error)
					$st .= "{$error['code']}: {$error['message']} @ {$error['offset']} \n";
			else
				$st = $errors;
			throw new SQLException($st, 'SELECT table_name FROM user_tables');
		}
		$tables = array();
		while(($row = oci_fetch_row($stid)))
			$tables[] = $row[0];
		return $tables;
	}

	public function createTable(TableInfo $info) {
		$sqls = OracleUtil::createTableSQL($this, $info);
		foreach($sqls as $sql) {
			echo "\n---------------\n" . $sql . "\n---------------\n";
			$stid = oci_parse($this->db, $sql);
			$result = oci_execute($stid);
			$errors = oci_error($stid);
			oci_free_statement($stid);
			if(!$result) {
				echo "-- MESSAGES\n";
				var_dump($this->getArray("SELECT * FROM ALL_ERRORS"));
				echo "--\n";
				throw new SQLException(self::buildErrorString($errors), $sql);
			}
		}
		oci_commit($this->db);
		$this->tables = false;
	}

	public function createView(\Mammut\DB\Model\ViewInfo $info) {
		$sql = $this->dialect->getDdl()->getCreateViewSQL($info);
		$this->query($sql);
	}
	
	public function table($table) {
		if($table instanceof \Mammut\DB\Model\TableInfo)
			$table = $table->getName();
		if(array_search($table, $this->tableList()) === false)
			throw new DBException('table "' . $table . '" dosen\'t exist');
		return new Table($this, $table, $addPrefix ? $this->getPrefix() : false);
	}

	/**
	 * converts a datetime column value to a unix timestap
	 */
	public function dateCol2uts($date) {
		if(preg_match('#([0-9]{1,4})[./-]([0-9]{1,2})[./-]([0-9]{1,2})#', $date, $match)) {
			$t = mktime(0, 0, 0, $match[2], $match[3], $match[1]);
			return $t;
		}
		else
			return false;
	}

	/**
	 * converts a uts integer to the datetime format of this database
	 */
	public function uts2dateCol($uts) {
		return date('Y-m-d', $uts);
	}

	/**
	 * converts a datetime column
	 */
	public function datetimeCol2uts($datetime) {
		if(preg_match('#([0-9]{1,4})[./-]([0-9]{1,4})[./-]([0-9]{1,4})\s+([0-9]{1,2})[:.]([0-9]{1,2})[:.]([0-9]{1,2})#', $datetime, $match)) {
			$t = mktime($match[4], $match[5], $match[6], $match[2], $match[3], $match[1]);
			return $t;
		}
		else
			return false;
	}

	/**
	 * converts a uts integer to the datetime format of this database
	 */
	public function uts2datetimeCol($uts) {
		return 'to_date(\'' . date('Y-m-d H:i:s', $uts) . '\')';
	}

	public function escapeValue($value, $addQuotes = true) {
		if(is_null($value))
			return 'NULL';
		if(is_bool($value))
			return $value ? '1' : '0';
		if(is_int($value) || is_long($value))
			return (int) $value;
		if(is_float($value) || is_double($value))
			return $value;
		if($value instanceof SQLFunction)
			return $this->getDialect()->getFunctionSQL($value);
		$q = $addQuotes ? '\'' : '';
		if($value instanceof \DateTime)
			return 'to_date(\'' . $value->format('Y-m-d H:i:s') . '\')';
		
		$value = str_replace(array('\\'), array('\\\\'), (string) $value);
		return $q . $value . $q; // ansi syntax */
	}

	public function getServer() {
		$obj = $this->getObject('SELECT sys_context(\'USERENV\',\'SERVER_HOST\') AS "name" FROM dual');
		if(is_object($obj))
			return $obj->name;
		return false;
	}

	public function getServerVersion() {
		return oci_server_version($this->db);
	}
}