<?php
/* $Id$ */
namespace Mammut\DB;

(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use Mammut\Exception\ImplementationException;
use Mammut\Date\Date;
use Mammut\Date\Time;
use Mammut\IO\iInput;
use Mammut\IO\iOutput;
use Mammut\Log\Logger;
use Mammut\DB\Model\TableInfo;
use Mammut\DB\Model\ViewInfo;
use Mammut\DB\Sql\iSql;
use Mammut\DB\Sql\Query;
use Mammut\DB\Sql\Select;
use Mammut\DB\Sql\Expression\SQLFunction;

/**
 * Base class for the mammut database abstraction framework.
 *
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB
 */
abstract class DB extends \Mammut\StrictObject {
	const _VERSION_ = '1.1.0.0';
	const OPT_PERSISTENT = 1010;
	const OPT_SECURE = 1011;
	const OPT_STORE_QUERIES = 1012;
	const OPT_CLIENT_ENCODUING = 1013;
	const DIALECT_ANSI = 'ANSI';
	const DIALECT_DB2 = 'DB2';
	const DIALECT_MSSQL = 'MSSQL';
	const DIALECT_MYSQL = 'MYSQL';
	const DIALECT_ORACLE = 'ORA';
	const DIALECT_PGSQL = 'PGSQL';
	const JOIN_INNER = 'inner';
	const JOIN_OUTER_L = 'outer_l';
	const JOIN_OUTER_R = 'outer_r';
	protected $tables = NULL;
	protected $qcount = 0;
	protected $options = array();
	protected $qstore = array();
	protected $doQStore = false;
	
	/**
	 *
	 * @var Logger a logger object
	 */
	protected static $LOG = NULL;
	
	/**
	 *
	 * @var \Mammut\DB\SQL\Dialect\iDialect
	 */
	protected $dialect;

	protected function __construct() {
		if(is_null(self::$LOG) && class_exists('\\Mammut\\Logger\\Logger'))
			self::setLogger(${'\\Mammut\\Logger\\Logger'}::getInstance(get_class($this)));
	}
	
	// ###### utility methods #####
	

	/**
	 * Sets the database logger
	 */
	public static function setLogger(&$logger) {
		self::$LOG = &$logger;
	}

	public static function getLoggger() {
		return self::$LOG;
	}

	/**
	 * Messages are only redirected on TRACE level and defined DEBUG constant
	 * @param mixed $message
	 * @param \Exception $th
	 */
	protected function logTrace($message, $th = null) {
		if (!defined('DEBUG'))
			return;
		$log = self::getLoggger();
		if(is_object($log) && method_exists($log, 'trace'))
			$log->trace(get_class($this).': '.$message, $th);
	}

	/**
	 * Messages are only redirected on DEBUG level and defined DEBUG constant
	 * @param mixed $message
	 * @param \Exception $th
	 */
	protected function logDebug($message, $th = null) {
		if (!defined('DEBUG'))
			return;
		$log = self::getLoggger();
		if(is_object($log) && method_exists($log, 'debug'))
			$log->debug(get_class($this).': '.$message, $th);
	}

	protected function logInfo($message, $th = null) {
		$log = self::getLoggger();
		if(is_object($log) && method_exists($log, 'info'))
			$log->info(get_class($this).': '.$message, $th);
	}

	protected function logWarning($message, $th = null) {
		$log = self::getLoggger();
		if(is_object($log) && method_exists($log, 'warn'))
			$log->warn(get_class($this).': '.$message, $th);
	}

	protected function logError($message, $th = null) {
		$log = self::getLoggger();
		if(is_object($log) && method_exists($log, 'error'))
			$log->error(get_class($this).': '.$message, $th);
	}

	protected function logFatal($message, $th = null) {
		$log = self::getLoggger();
		if(is_object($log) && method_exists($log, 'fatal'))
			$log->fatal(get_class($this).': '.$message, $th);
	}

	public function flushCache() {
		$this->tables = NULL;
	}

	/**
	 * creates a new database object, specified by a dsn and additional options
	 * dsn has the following format:
	 *
	 * basedriver:cstring
	 *
	 * basedriver needs to be one of the supported driver (mysql, mysqli, pdo)
	 * cstring is database specific:
	 * pdo: the pdo connection string
	 * mysql/mysqli: a string in the form user:password@hostname[:port]\database
	 *
	 * @param string $dsn
	 *        	connection string
	 * @param array $options
	 *        	array with the connection options
	 * @return DB a new DB connection object
	 */
	public static function newInstance($dsn, $user, $password, array $options = array()) {
		list($type, $cstring) = explode(':', $dsn, 2);
		
		$files = scandir(__DIR__);
		foreach($files as $file) {
			if(is_dir(__DIR__ . __DS__ . $file) && (substr_count($file, 'Exception') == 0)) {
				if(strtolower($file) == strtolower($type)) {
					$type = $file;
					break;
				}
			}
		}
		
		$typeMap = [
			'db2' => 'DB2',
			'mssql' => 'MSSQL',
			'mysql' => 'MySQLi',
			'mysqli' => 'MySQLi',
			'oracle' => 'Oracle',
			'pdo' => 'PDO',
			'postgre' => 'PostGre',
			'sqlite' => 'SQLite',
			'sqlsrv' => 'MSSQL',
		];
		
		// map "simple" names to classes if needed
		if (key_exists($type, $typeMap))
			$type = $typeMap[$type];
		unset($typeMap);
		
		$classname = '\\Mammut\\DB\\' . $type . '\\' . $type;
		if($classname == '\\Mammut\\DB\\\\')
			throw new \InvalidArgumentException('No driver selected');
		
		if(class_exists($classname, true))
			return new $classname($cstring, $user, $password, $options);
		else
			throw new \InvalidArgumentException('No driver class "'.$classname.'" found for ' . $type);
	}

	/**
	 * optional overwriteable hook, called to shut down the database
	 */
	public function close() {
		$this->logDebug('query count: ' . $this->qcount);
	}

	/**
	 *
	 * @return string the database server on network based databases, false if no server exists
	 */
	public function getServer() {
		return false;
	}

	/**
	 * selects the active database (if supported by the backend)
	 *
	 * @param $name string
	 *        	database name
	 * @return void
	 */
	abstract public function setDatabase($name);

	/**
	 *
	 * @return string the name of the active database (if supported)
	 */
	abstract public function getDatabase();

	public function isTransactionSupported() {
		return false;
	}
	
	// ###### transaction support ######
	/**
	 * Enable/disable automatic commiting of queries
	 *
	 * @param boolean $doAutoCommit
	 *        	true to enable automatic commits (no transaction handling)
	 * @throws ImplementationException if the database does not support transactions
	 */
	public function setAutocommit($doAutoCommit) {
		throw new ImplementationException('not supported');
	}

	public function startTransaction() {
		throw new ImplementationException('not supported');
	}

	public function commit() {
		throw new ImplementationException('not supported');
	}

	public function rollback() {
		throw new ImplementationException('not supported');
	}

	/**
	 *
	 * @return \Mammut\DB\Sql\Insert
	 */
	public function insert($table = null) {
		return new \Mammut\DB\Sql\Insert($table);
	}

	/**
	 *
	 * @return \Mammut\DB\Sql\Select
	 */
	public function select($table = null) {
		return new \Mammut\DB\Sql\Select($table);
	}

	/**
	 *
	 * @return \Mammut\DB\Sql\Update
	 */
	public function update($table = null) {
		return new \Mammut\DB\Sql\Update($table);
	}

	/**
	 *
	 * @return \Mammut\DB\Sql\Delete
	 */
	public function delete($table = null) {
		return new \Mammut\DB\Sql\Delete($table);
	}

	protected function checkQuery($query) {
		if($query instanceof Query) {
			$sql = $query->getSql($this->dialect);
			if ($query instanceof Select && !$query->getTarget())
				$sql .= ' '.$this->dialect->getDummyTableSQL();
			$query = $sql;
		}
		return $query;
	}

	/**
	 * sends a query to the database system
	 *
	 * @param string $sql
	 *        	sql query
	 * @return Result the result set
	 */
	abstract public function query($query, $limit = -1, $skip = 0);

	/**
	 * sends multiple queries to the database system
	 *
	 * @param array $sql
	 *        	sql querys
	 * @param boolean $noresult
	 *        	fetching results, which improves performance
	 * @return array the results of the query
	 */
	public function multiquery(array $queries, $noresult = false) {
		$result = array();
		foreach($queries as $q) {
			$r = $this->query($q);
			if(!$noresult) {
				$result[] = array('result' => $r,'affected' => $this->getAffectedRowCount());
			}
		}
		return $result;
	}

	/**
	 * executes a result free prepared statement
	 *
	 * @param string $query
	 *        	the prepared statement
	 * @param array $qparam
	 *        	the parameters
	 * @return number the number of rows effected by the statement
	 */
	public function executeP($query, array $qparam = array()) {
		$stmt = $this->prepareStatement($query);
		$stmt->execute($qparam);
		$c = $stmt->getRowCount();
		$stmt->close();
		return $c;
	}

	/**
	 * prepare a new statement which can be executed later
	 *
	 * @param string $query        	
	 * @return \Mammut\DB\Statement
	 */
	public function prepareStatement($query, $limit = -1, $skip = 0) {
		$query = $this->checkQuery($query);
		return new Statement($this, $query, $limit = -1, $skip = 0);
	}

	/**
	 * creates a view emulation instance for databases which dosent understand views.
	 * should be overwritten on databases which support views
	 *
	 * @return \Mammut\DB\View a object which represents the view
	 */
	public function createViewInstance($name, $query) {
		return new View($this, $name, $query);
	}

	public function getObject($query, $class = false, $param = array()) {
		$result = $this->query($query);
		$obj = empty($param) ? $result->fetchObject($class, $param) : $result->fetchObject($class, $param);
		$result->close();
		return $obj;
	}

	public function getArray($query) {
		$result = $this->query($query);
		$row = &$result->fetchArray();
		$result->close();
		return $row;
	}

	public function getRow($query) {
		$result = $this->query($query);
		$row = &$result->fetchRow();
		$result->close();
		return $row;
	}

	public function getObjectP($query, array $qparam = array(), $class = false, $param = array()) {
		$stmt = $this->prepareStatement($query);
		$stmt->execute($qparam);
		$obj = $stmt->fetchObject($class, $param);
		$stmt->close();
		return $obj;
	}

	public function getArrayP($query, array $qparam = array()) {
		$stmt = $this->prepareStatement($query);
		$stmt->execute($qparam);
		$row = $stmt->fetchArray();
		$stmt->close();
		return $row;
	}

	public function getRowP($query, array $qparam = array()) {
		$stmt = $this->prepareStatement($query);
		$stmt->execute($qparam);
		$row = $stmt->fetchRow();
		$stmt->close();
		return $row;
	}

	public function getObjectList($query, $class = false, $param = array(), $limit = -1, $skip = 0) {
		$result = $this->query($query, $limit, $skip);
		$data = array();
		while($next = $result->fetchObject($class, $param))
			$data[] = $next;
		$result->close();
		return $data;
	}

	public function getArrayList($query, $limit = -1, $skip = 0) {
		$result = $this->query($query, $limit, $skip);
		$data = array();
		while($next = $result->fetchArray())
			$data[] = $next;
		$result->close();
		return $data;
	}

	public function getRowList($query, $limit = -1, $skip = 0) {
		$result = $this->query($query, $limit, $skip);
		$data = array();
		while($next = $result->fetchRow())
			$data[] = $next;
		$result->close();
		return $data;
	}

	public function getObjectListP($query, array $qparam = array(), $class = false, $param = array(), $limit = -1, $skip = 0) {
		$stmt = $this->prepareStatement($query);
		$stmt->execute($qparam);
		$data = array();
		while($next = $stmt->fetchObject($class, $param))
			$data[] = $next;
		$stmt->close();
		return $data;
	}

	public function getArrayListP($query, array $qparam = array(), $limit = -1, $skip = 0) {
		$stmt = $this->prepareStatement($query);
		$stmt->execute($qparam);
		$data = array();
		while($next = $stmt->fetchArray())
			$data[] = $next;
		$stmt->close();
		return $data;
	}

	public function getRowListP($query, array $qparam = array(), $limit = -1, $skip = 0) {
		$stmt = $this->prepareStatement($query);
		$stmt->execute($qparam);
		$data = array();
		while($next = $stmt->fetchRow())
			$data[] = $next;
		$stmt->close();
		return $data;
	}
	
	/**
	 * Returns the number of columns selected by the query
	 * @param mixed $query
	 * @return int
	 */
	public function getRowCount($query) {
		$result = $this->query($query);
		$count = $result->getRowCount();
		$result->close();
		return $count;
	}

	/**
	 *
	 * @return number last generated id
	 */
	abstract public function getInsertId();

	/**
	 *
	 * @return int number of rows affected by the last query
	 */
	abstract public function getAffectedRowCount();

	/**
	 * Creates a new Table object that represents the table given.
	 *
	 * @param string $name
	 *        	name of the table
	 * @return \Mammut\DB\Table a table object
	 * @throws \Mammut\DB\DBException if table dosen't exist
	 */
	abstract public function table($name);

	/**
	 * Creates a list of all existing tables
	 *
	 * @param $removePrefix boolean
	 *        	to true if the database prefix should be removed from table names
	 * @return array list of tables found
	 */
	abstract public function tableList();

	/**
	 * checks if a table exists
	 *
	 * @param $table string|TableInfo|Table
	 *        	name/infoobject of the table
	 * @return boolean true if the table exists
	 */
	public function tableExists($table) {
		if($table instanceof \Mammut\DB\Table)
			$table = $table->getName();
		elseif($table instanceof \Mammut\DB\Model\TableInfo)
			$table = $table->getName();
		$tables = $this->tableList();
		return in_array($table, $tables);
	}

	/**
	 * Creates a table defined by an TableInfo object
	 *
	 * @param $info \Mammut\DB\Model\TableInfo        	
	 * @return void
	 */
	public function createTable(\Mammut\DB\Model\TableInfo $info) {
		$this->tables = false;
		$sql = $this->dialect->getDdl()->getCreateTableSQL($info);
		if (is_array($sql)) {
			foreach($sql as $s)
				$this->query($s);
		}
		else
			$this->query($sql);
	}

	/**
	 * removes a table defined by an tableinfo object or name
	 *
	 * @param $table mixed
	 *        	name or object or tableinfo object
	 * @param $addPrefix boolean
	 *        	the database table prefix should be added
	 * @return void
	 */
	public function dropTable($table) {
		$this->tables = false;
		if($table instanceof \Mammut\DB\Table)
			$table = $table->getName();
		elseif($table instanceof \Mammut\DB\Model\TableInfo)
			$table = $table->getName();
			// 		$table = strtolower($table);
		$sql = $this->getDialect()->getDdl()->getDropTableSQL($table);
		$this->query($sql);
	}

	/**
	 * Creates a view defined by an ViewInfo object
	 *
	 * @param $info \Mammut\DB\Model\ViewInfo        	
	 * @return void
	 */
	public function createView(\Mammut\DB\Model\ViewInfo $info) {
		$this->tables = false;
		$sql = $this->dialect->getDdl()->getCreateViewSQL($info);
		if (is_array($sql)) {
			foreach($sql as $s)
				$this->query($s);
		}
		else
			$this->query($sql);
	}

	public function dropView($view) {
		$this->tables = false;
		if($view instanceof \Mammut\DB\Table)
			$view = $view->getName();
		elseif($view instanceof \Mammut\DB\View)
			$view = $view->getName();
		elseif($view instanceof \Mammut\DB\Model\ViewInfo)
			$view = $view->getName();
			// 		$view = strtolower($view);
		$sql = $this->getDialect()->getDdl()->getDropViewSQL($view);
		$this->query($sql);
	}
	
	// db date <-> uts methods
	/**
	 * converts a datetime column value to a unix timestap
	 */
	abstract public function dateCol2uts($date);

	/**
	 * converts a uts integer to the datetime format of this database
	 */
	abstract public function uts2dateCol($uts);
	
	// db date <-> isodate (yyyy-mm-dd) methods
	/**
	 * converts a datetime column value to a ISO 8601 date
	 */
	public function dateCol2iso($date) {
		date('Y-m-d', $this->dateCol2uts($date));
	}

	/**
	 * converts a ISO 8601 date to the datetime format of this database
	 */
	public function iso2dateCol($iso) {
	}
	
	// db datetime <-> uts methods
	/**
	 * converts a datetime column
	 */
	abstract public function datetimeCol2uts($datetime);

	/**
	 * converts a uts integer to the datetime format of this database
	 */
	abstract public function uts2datetimeCol($uts);
	
	// db datetime <-> ISO 8601 datetime methods
	/**
	 * converts a datetime column
	 */
	public function datetimeCol2iso($datetime) {
		return date('Y-m-d H:i:s', $this->datetimeCol2uts($datetime));
	}

	/**
	 * converts a iso stringto the datetime format of this database
	 */
	public function iso2datetimeCol($iso) {
		if(preg_match('#([0-9]{2,4})-([0-9]{2,4})-([0-9]{2,4})\s+([0-9]{2,4}):([0-9]{2,4}):([0-9]{2,4})#', $iso, $match)) {
			return $this->uts2datetimeCol(mktime($match[4], $match[5], $match[6], $match[2], $match[3], $match[1]));
		}
		else
			throw InvalidArgumentException('invalid iso format');
	}

	/**
	 * escapes a string to be used as text in an sql statement.
	 *
	 * @param string $name
	 *        	input string
	 * @return string escaped string
	 */
	public function escapeString($text) {
		if(is_numeric($text))
			return $text;
		return $this->dialect->escapeString($text);
	}

	/**
	 * escapes a string to be used as a table name.
	 * that means escaping all special chars and quoting
	 *
	 * @param string $name
	 *        	name of the table
	 * @return string escaped and quoted name
	 */
	public function escapeTableName($name) {
		return $this->dialect->quoteIdent($name);
	}

	/**
	 * escapes a string to be used as a column name.
	 * that means escaping all special chars and quoting
	 *
	 * @param string $name
	 *        	name of the column
	 * @return string escaped and quoted name
	 */
	public function escapeColumnName($name) {
		return $this->dialect->quoteIdent($name);
	}

	/**
	 * escapes a value to be used in a sql query.
	 * that means escaping all special chars and quoting the content.
	 * returns the command if the value is a MTDBSQLFunction object
	 *
	 * @param mixed $value
	 *        	value which should be escaped
	 * @param boolean $addQuotes
	 *        	add quotes if needed
	 * @return string escaped and quoted value
	 */
	public function escapeValue($value) {
		if(is_null($value))
			return 'NULL';
		if(is_bool($value))
			return $value ? 'TRUE' : 'FALSE';
		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->dialect->getFunctionSQL($value);
		$q = $addQuotes ? '\'' : '';
		if($value instanceof \DateTime)
			return $q . $value->format('Y-m-d H:i:s') . $q;
		if($value instanceof \Mammut\IO\File) {
			$filename = $value->getPath();
			$content = file_get_contents($filename);
			return $q . $this->dialect->escapeString($content) . $q;
		}
		return $q . $this->dialect->escapeString($value) . $q; // ansi syntax
	}

	public function getQueryCount() {
		return $this->qcount;
	}

	/**
	 * Rename a table
	 *
	 * @param mixed $oldName        	
	 * @param mixed $newName        	
	 */
	public function renameTable($oldName, $newName) {
		$this->logTrace("renaming table {$oldName} to {$newName}");
		$sql = $this->dialect->getDdl()->getRenameTableSQL($oldName, $newName);
		$this->query($sql);
		$this->tables = false;
	}

	/**
	 *
	 * @return \Mammut\DB\SQL\Dialect\iDialect
	 */
	public function getDialect() {
		return $this->dialect;
	}

	public function getServerVersion() {
		return -1;
	}

	/**
	 * Returns the maximum bytes of an blob block written at once (default: 32k).
	 */
	protected function getMaxBlobWriteSize() {
		return 1024 * 32;
	}

	/**
	 * builds the server specific variant of the append-binary query
	 *
	 * @param string $tablename        	
	 * @param array $rowKey        	
	 * @param string $blobfield        	
	 * @return string the SQL
	 */
	protected function buildBlobAddSQL($tablename, array $rowKey, $blobfield) {
		$table = $this->escapeTableName($tablename);
		$field = $this->escapeColumnName($blobfield);
		
		$where = array();
		foreach($rowKey as $key=>$val)
			$where[] = $this->escapeColumnName($key) . '=' . $this->escapeValue($val);
		
		$sql = "UPDATE {$table} SET {$field}=CONCAT({$field},?) WHERE " . implode(' AND ', $where);
		return $sql;
	}

	protected function buildBlobReadSQL($tablename, array $rowKey, $blobfield, $start, $size) {
		$table = $this->escapeTableName($tablename);
		$field = $this->escapeColumnName($blobfield);
		
		$where = array();
		foreach($rowKey as $key=>$val)
			$where[] = $this->escapeColumnName($key) . '=' . $this->escapeValue($val);
		
		$sql = "SELECT {$field} AS binval FROM {$table} WHERE " . implode(' AND ', $where);
		return $sql;
	}

	/**
	 * Write data from an datasource into a table.
	 * If the row specified dosen't exist, it will be created.
	 *
	 * @param string $tablename        	
	 * @param array $rowKey        	
	 * @param string $blobfield        	
	 * @param iOutput $output
	 *        	the datasource
	 * @throws \RuntimeException if the key is not unique
	 */
	public function writeBlob($tablename, array $rowKey, $blobfield, iOutput $output) {
		$result = $this->table($tablename)->select($rowKey);
		if($result->getRowCount() > 3) {
			$result->close();
			throw new \RuntimeException("duplicate datasets for key");
		}
		$exists = ($result->getRowCount() == 1);
		if(!$exists)
			$this->table($tablename)->insert($rowKey);
		
		$closeOnExit = !$output->isOpen();
		if($closeOnExit) {
			$output->open();
			$closeOnExit = true;
		}
		
		// we do not use appendBlobChunk because we want to reuse the pstmt
		$sql = $this->buildBlobAddSQL($tablename, $rowKey, $blobfield);
		$stmt = $this->prepareStatement($sql);
		while(!$output->isEOF()) {
			$buffer = $output->read($this->getMaxBlobWriteSize());
			$stmt->execute(array($buffer));
		}
		$stmt->close();
		
		if($closeOnExit)
			$output->close();
	}

	/**
	 * Adds a blob chunk to an existing blob field
	 *
	 * @param string $tablename        	
	 * @param array $rowKey        	
	 * @param string $blobfield        	
	 * @param string $data        	
	 * @throws \RuntimeException if no row with the given key exists
	 */
	public function appendBlobChunk($tablename, array $rowKey, $blobfield, $data) {
		$result = $this->table($tablename)->select($rowKey);
		if($result->getRowCount() != 1) {
			$result->close();
			throw new \RuntimeException("invalid key");
		}
		
		$stmt = $this->prepareStatement($this->buildBlobAddSQL($tablename, $rowKey, $blobfield));
		$stmt->execute(array($buffer));
		$stmt->close();
	}

	public function readBlob($tablename, array $rowKey, $blobfield, iInput $input, $chunksize = 1024) {
		$result = $this->table($tablename)->select($rowKey, false, array($blobfield));
		if($result->getRowCount() != 1) {
			$result->close();
			throw new \RuntimeException("not (only) one dataset for key");
		}
		$store = $result->fetchRow();
		$result->close();
		$input->write($store[0]);
	}

	public function storeQuery($sql, $param = false, $delay = -1) {
		if(!$this->doQStore)
			return;
		$q = new \stdClass();
		$q->query = $sql;
		$q->param = $param;
		$q->delay = $delay * 1000; // in milli sec
		$q->timestamp = microtime(true);
		ob_start();
		debug_print_backtrace();
		$q->backtrace = ob_get_clean();
		$this->qstore[] = $q;
	}

	public function isQueryStoring() {
		return $this->doQStore;
	}

	public function getQueryArchive() {
		return $this->qstore;
	}
}