<?php
/* $Id$ */
namespace Mammut\DB\Postgre;

(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\DB;
use Mammut\Date\Date;
use Mammut\DB\Sql\Expression\SQLFunction;
use Mammut\DB\Model\ViewInfo;

/**
 * DB implementation for PostgreSQL server
 * 
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB\Adapter\PostgreSQL
 */
class PostGre extends \Mammut\DB\DB {
	const _VERSION_ = '1.1.0.0';
	
	/**
	 *
	 * @var Resource
	 */
	private $db = false;
	private $lastResult = NULL;
	/**
	 *
	 * @var iCache
	 */
	private $cache = NULL;

	/**
	 * 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 SQLException on connection errors
	 */
	public function __construct($cstring, $user, $password, array $options = array(), iCache $cache = NULL) {
		if(!extension_loaded('pgsql'))
			throw new \Mammut\Exception\ExtensionException('pgsql');
		$pers = in_array(DB::OPT_PERSISTENT, $options);

		$this->cache = $cache;
		
		if(preg_match('#([a-zA-Z0-9\.-]+)(:[0-9]+){0,1}/([a-zA-Z0-9_\.-]+)#', $cstring, $match)) {
			$dbscm = explode(".", $match[3]);
			$con_st = 'host=' . $match[1];
			if($match[2])
				$con_st .= ' port=' . (int) $match[2];
			$con_st .= ' dbname=' . $dbscm[0];
			$con_st .= ' user=' . $user;
			$con_st .= ' password=' . $password;
			
			$this->db = $pers ? pg_pconnect($con_st) : pg_connect($con_st);
			if(!empty($dbscm[1]))
				pg_query($this->db, 'SET search_path TO ' . $dbscm[1]);
		}
		else
			throw new \InvalidArgumentException('$cstring is invalid: ' . $cstring);
		
		if($this->db == false)
			throw new DBException('connection failed');
		
		$this->dialect = new \Mammut\DB\Sql\Dialect\Postgresql($this->db);
		
		$versions = pg_version($this->db);
		if(version_compare($versions['server'], '9.0', '>='))
			pg_query($this->db, 'SET bytea_output = "escape";');
	}

	public function getServer() {
		return pg_host($this->db);
	}

	public function setDatabase($name) {
		$dbscm = explode(".", $name);
		$result = pg_query($this->db, '\connect ' . pg_escape_string($dbscm[0]));
		if(!empty($dbscm[1]))
			pg_query($this->db, 'SET search_path TO ' . $dbscm[1]);
		return $result ? true : false;
	}

	public function getDatabase() {
		return pg_dbname($this->db);
	}

	public function getSchema() {
		$result = pg_query($this->db, "SELECT current_schema() AS s");
		$val = pg_fetch_object($result)->s;
		pg_free_result($result);
		$this->qcount++;
		return $val;
	}

	public function isTransactionSupported() {
		return true;
	}

	public function setAutocommit($doAutoCommit) {
		// don't now if we should do something here
	}

	public function startTransaction() {
		pg_query($this->db, "BEGIN");
		$this->qcount++;
		if($st = pg_last_error($this->db))
			throw new SQLException($st, $query);
	}

	public function commit() {
		pg_query($this->db, "COMMIT");
		$this->qcount++;
		if($st = pg_last_error($this->db))
			throw new SQLException($st, $query);
	}

	public function rollback() {
		pg_query($this->db, "ROLLBACK");
		$this->qcount++;
		if($st = pg_last_error($this->db))
			throw new SQLException($st, $query);
	}

	/**
	 * (non-PHPdoc)
	 *
	 * @see \Mammut\DB\DB::newQuery()
	 */
	public function newQuery($type) {
		switch($type) {
			case iQuery::Q_DELETE:
				return new \Mammut\DB\Postgre\Query\Delete();
			case iQuery::Q_INSERT:
				return new \Mammut\DB\Postgre\Query\Insert();
			case iQuery::Q_SELECT:
				return new \Mammut\DB\Postgre\Query\Select();
			case iQuery::Q_UPDATE:
				return new \Mammut\DB\Postgre\Query\Update();
		}
		throw new InvalidArgumentException($type);
	}

	public function query($query, $limit = -1, $skip = 0) {
		$query = $this->checkQuery($query);
		$this->logDebug('sending new query: ' . $query);
		
		if($limit >= 0)
			$query .= ' LIMIT ' . $limit;
		if($skip > 0)
			$query .= ' OFFSET ' . $skip;
		
		$this->lastResult = pg_query($this->db, $query);
		$this->qcount++;
		if($st = pg_last_error($this->db))
			throw new SQLException($st, $query);
		if (pg_num_fields($this->lastResult) == 0) {
			$num = pg_affected_rows($this->lastResult);
			// TODO: find a way to close the result
			return $num;
		}
		return new Result($this->lastResult);
	}

	public function getInsertId() {
		$result = pg_query("SELECT lastval() AS v");
		$val = pg_fetch_object($result)->v;
		pg_free_result($result);
		return $val;
	}

	public function getAffectedRowCount() {
		return pg_affected_rows($this->lastResult);
	}

	/**
	 * creates a new statement which can be executed later
	 *
	 * @param string $query        	
	 * @return Statement
	 */
	public function prepareStatement($query, $limit = -1, $skip = 0) {
		$query = $this->checkQuery($query);
		return new Statement($this, $query, $limit, $skip);
	}

	public function getArray($query) {
		$query = $this->checkQuery($query);
		$result = pg_query($this->db, $query);
		if($st = pg_last_error($this->db))
			throw new SQLException($st, $query);
		$row = pg_fetch_assoc($result);
		$fieldcount = pg_num_fields($result);
		
		for($i = 0; $i < $fieldcount; $i++) {
			$type = strtolower(pg_field_type($result, $i));
			$name = pg_field_name($result, $i);
			$row[$name] = self::convertDBData($type, $row[$name]);
		}
		pg_free_result($result);
		return $row;
	}

	public function getRow($query) {
		$query = $this->checkQuery($query);
		$result = pg_query($this->db, $query);
		if($st = pg_last_error($this->db))
			throw new SQLException($st, $query);
		$row = pg_fetch_row($result);
		$fieldcount = pg_num_fields($result);
		
		foreach($row as $key=>$val) {
			$type = strtolower(pg_field_type($result, $key));
			$row[$key] = self::convertDBData($type, $row[$key]);
		}
		pg_free_result($result);
		return $row;
	}

	public function getObject($query, $class = false, $param = array()) {
		$query = $this->checkQuery($query);
		$result = pg_query($this->db, $query);
		if($class)
			$data = empty($param) ? pg_fetch_object($result, false, $class) : pg_fetch_object($result, false, $class, $param);
		else
			$data = pg_fetch_object($result);
		$fieldcount = pg_num_fields($result);
		
		if(!is_object($data)) // if no data was found, do not convert non-existing data
			return $data;
		
		for($i = 0; $i < $fieldcount; $i++) {
			$type = strtolower(pg_field_type($result, $i));
			$name = pg_field_name($result, $i);
			$data->$name = self::convertDBData($type, $data->$name);
		}
		pg_free_result($result);
		return $data;
	}

	public static function convertDBData($type, $data) {
		if(is_null($data))
			return $data;
		switch($type) {
			case 'int2':
			case 'int4':
			case 'int8':
				return (int) $data;
			case 'date':
				return new Date($data);
			case 'timestamp':
				return new \DateTime($data);
			case 'bool':
				return ($data == 't');
			case 'blob':
			case 'bytea':
				return pg_unescape_bytea($data);
			default:
			break;
		}
		return $data;
	}

	public function tableList() {
		$scm = $this->getSchema();
		$result = false;
		$query = "SELECT table_name FROM information_schema.tables WHERE table_schema = '{$scm}'";
		$result = pg_query($this->db, $query);
		$this->qcount++;
		if($st = pg_last_error($this->db))
			throw new SQLException($st, $query);
		if($result) {
			$tables = array();
			while($next = pg_fetch_row($result))
				$tables[] = $next[0];
			return $tables;
		}
		else
			return false;
	}

// 	public function createTable(TableInfo $info) {
// 		$sql = PostGreUtil::createTableSQL($this, $info, $this->getSchema());
// 		$result = pg_query($this->db, $sql);
// 		$this->qcount++;
// 		if($st = pg_last_error($this->db))
// 			throw new SQLException($st, $sql);
// 		$this->tables = false;
// 	}

	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);
	}

// 	public function dropTable($table) {
// 		$scm = $this->getSchema();
// 		if($table instanceof Table)
// 			$table = $table->getName();
// 		elseif($table instanceof TableInfo)
// 			$table = $table->getName();
// 		parent::dropTable([$scm, $table]);
// 	}
	
// 	public function createView(ViewInfo $info) {
// 		$this->dialect->getDdl()->getCreateViewSQL($info);
// 	}

// 	public function dropView($table) {
// 		$scm = $this->getSchema();
// 		if($table instanceof Table)
// 			$table = $table->getName();
// 		elseif($table instanceof TableInfo)
// 		$table = $table->getName();
// 		parent::dropView([$scm, $table]);
// 	}
	
	/**
	 * 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 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 ? '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->getDialect()->getFunctionSQL($value);
		$q = $addQuotes ? '\'' : '';
		if($value instanceof \Mammut\IO\File) {
			$filename = $value->getPath();
			$content = file_get_contents($filename);
			return "'" . pg_escape_bytea($this->db, $content) . "'::bytea"; // TODO: fix $addQuotes
		}
		if($value instanceof \DateTime)
			return  $q . $value->format('Y-m-d H:i:s') . $q;
		return $q . pg_escape_string($this->db, $value) . $q; // ansi syntax
	}

	public function getServerVersion() {
		$info = pg_version($this->db);
		return $info['server'];
	}
}