<?php
/**
 * IBM DB2 for DBO
 *
 * This file supports IBM DB2 and Cloudscape (aka Apache Derby,
 * Sun Java DB) using the native ibm_db2 extension:
 * http://pecl.php.net/package/ibm_db2
 *
 * PHP versions 4 and 5
 *
 * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
 * Copyright 2007, Cake Software Foundation, Inc.
 *
 * Licensed under The MIT License
 * Redistributions of files must retain the above copyright notice.
 *
 * @copyright     Copyright 2005-2009, Cake Software Foundation, Inc. (http://cakefoundation.org)
 * @link          http://cakephp.org CakePHP(tm) Project
 * @since         CakePHP Datasources v 0.1
 * @license       MIT License (http://www.opensource.org/licenses/mit-license.php)
 */

App::uses('DboSource', 'Model/Datasource');

/**
 * IBM DB2 for DBO
 *
 * This file supports IBM DB2 and Cloudscape (aka Apache Derby,
 * Sun Java DB) using the native ibm_db2 extension:
 * http://pecl.php.net/package/ibm_db2
 *
 */

class DboDb2 extends DboSource {

/**
 * A short description of the type of driver.
 *
 * @var string
 */
	public $description = 'IBM DB2 DBO Driver';

/**
 * The start quote in which database column and table names should be wrapped.
 *
 * @var string
 */
	public $startQuote = '';

/**
 * The end quote in which database column and table names should be wrapped.
 *
 * @var string
 */
	public $endQuote = '';

/**
 * An array of base configuration settings to be used if settings are not
 * provided, i.e. default host, port, and connection method.
 *
 * @var array
 */
	protected $_baseConfig = array(
		'persistent' => true,
		'login' => 'db2inst1',
		'password' => '',
		'database' => 'cake',
		'schema' => '',
		'hostname' => '127.0.0.1',
		'port' => '50001',
		'encoding' => true,
		'cataloged' => true,
		'autocommit' => true
	);

/**
 * An array that maps Cake column types to database native column types.
 * The mapped information can include a reference to a function that should
 * be used to format the data, as well as a string that defines the
 * formatting according to that function.
 *
 * @var array
 */
	public $columns = array(
		'primary_key' => array('name' => 'not null generated by default as identity (start with 1, increment by 1)'),
		'string' => array('name' => 'varchar', 'limit' => '255'),
		'text' => array('name' => 'clob'),
		'integer' => array('name' => 'integer', 'limit' => '10', 'formatter' => 'intval'),
		'float' => array('name' => 'double', 'formatter' => 'floatval'),
		'datetime' => array('name' => 'timestamp', 'format' => 'Y-m-d-H.i.s', 'formatter' => 'date'),
		'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d-H.i.s', 'formatter' => 'date'),
		'time' => array('name' => 'time', 'format' => 'H.i.s', 'formatter' => 'date'),
		'date' => array('name' => 'date', 'format' => 'Y-m-d', 'formatter' => 'date'),
		'binary' => array('name' => 'blob'),
		'boolean' => array('name' => 'smallint', 'limit' => '1')
	);

/**
 * A map for every result mapping tables to columns
 *
 * @var array result -> ( table -> column )
 */
	protected $_resultMap = array();

/**
 * Connects to the database using options in the given configuration array.
 *
 * @return boolean True if the database could be connected, else false
 */
	public function connect() {
		$config = $this->config;
		$connect = 'db2_connect';
		if ($config['persistent']) {
			$connect = 'db2_pconnect';
		}
		$this->connected = false;

		if ($config['cataloged']) {
			$this->connection = $connect($config['database'], $config['login'], $config['password']);
		} else {
			$connString = sprintf(
				"DRIVER={IBM DB2 ODBC DRIVER};DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;",
				$config['database'],
				$config['hostname'],
				$config['port'],
				$config['login'],
				$config['password']
			);
			$this->connection = db2_connect($connString, '', '');
		}

		if ($this->connection) {
			$this->connected = true;
		}

		if ($config['schema'] !== '') {
			$this->_execute('SET CURRENT SCHEMA = ' . $config['schema']);
		}
		return $this->connected;
	}

/**
 * Check that the DB2 extension is installed/loaded
 *
 * @return boolean
 */
	public function enabled() {
		return extension_loaded('ibm_db2');
	}
/**
 * Disconnects from database.
 *
 * @return boolean True if the database could be disconnected, else false
 */
	public function disconnect() {
		@db2_free_result($this->results);
		$this->connected = !@db2_close($this->connection);
		return !$this->connected;
	}

/**
 * Executes given SQL statement.  We should use prepare / execute to allow the
 * database server to reuse its access plan and increase the efficiency
 * of your database access
 *
 * @param string $sql SQL statement
 * @return resource Result resource identifier
 */
	protected function _execute($sql) {
		// get result from db
		$result = db2_exec($this->connection, $sql);

		if (!is_bool($result)) {
			// build table/column map for this result
			$map = array();
			$numFields = db2_num_fields($result);
			$index = 0;
			$j = 0;
			$offset = 0;

			while ($j < $numFields) {
				$columnName = strtolower(db2_field_name($result, $j));
				$tmp = strpos($sql, '.' . $columnName, $offset);
				$tableName = substr($sql, $offset, ($tmp - $offset));
				$tableName = substr($tableName, strrpos($tableName, ' ') + 1);
				$map[$index++] = array($tableName, $columnName);
				$j++;
				$offset = strpos($sql, ' ', $tmp);
			}

			$this->_resultMap[$result] = $map;
		}

		return $result;
	}

/**
 * Returns an array of all the tables in the database.
 * Should call parent::listSources twice in the method:
 * once to see if the list is cached, and once to cache
 * the list if not.
 *
 * @return array Array of tablenames in the database
 */
	public function listSources() {
		$cache = parent::listSources();

		if ($cache != null) {
			return $cache;
		}
		$result = db2_tables($this->connection);
		$tables = array();

		while (db2_fetch_row($result)) {
			$tables[] = strtolower(db2_result($result, 'TABLE_NAME'));
		}
		parent::listSources($tables);
		return $tables;
	}

/**
 * Returns an array of the fields in given table name.
 *
 * @param Model $model Model object to describe
 * @return array Fields in table. Keys are name and type
 */
	public function &describe($model) {
		$cache = parent::describe($model);

		if ($cache != null) {
			return $cache;
		}
		$fields = array();
		$result = db2_columns($this->connection, '', '', strtoupper($this->fullTableName($model)));

		while (db2_fetch_row($result)) {
			$fields[strtolower(db2_result($result, 'COLUMN_NAME'))] = array(
				'type' => $this->column(strtolower(db2_result($result, 'TYPE_NAME'))),
				'null' => db2_result($result, 'NULLABLE'),
				'default' => db2_result($result, 'COLUMN_DEF'),
				'length' => db2_result($result, 'COLUMN_SIZE')
			);
		}
		$this->_cacheDescription($model->tablePrefix . $model->table, $fields);
		return $fields;
	}

/**
 * Returns a quoted name of $data for use in an SQL statement.
 *
 * @param string $data Name (table.field) to be prepared for use in an SQL statement
 * @return string Quoted for MySQL
 */
	public function name($data) {
		return $data;
	}

/**
 * Returns a quoted and escaped string of $data for use in an SQL statement.
 *
 * @param string $data String to be prepared for use in an SQL statement
 * @param string $column The column into which this data will be inserted
 * @return string Quoted and escaped
 * @todo Add logic that formats/escapes data based on column type
 */
	public function value($data, $column = null, $safe = false) {
		$parent = parent::value($data, $column, $safe);

		if ($parent != null) {
			return $parent;
		}

		if ($data === null || (is_array($data) && empty($data))) {
			return 'NULL';
		}

		if ($data === '') {
			return "''";
		}

		switch ($column) {
			case 'boolean':
				$data = $this->boolean((bool)$data);
			break;
			case 'integer':
				$data = intval($data);
			break;
			default:
				$data = str_replace("'", "''", $data);
			break;
		}

		if ($column === 'integer' || $column === 'float') {
			return $data;
		}
		return "'" . $data . "'";
	}

/**
 * Not sure about this one, MySQL needs it but does ODBC?  Safer just to leave it
 * Translates between PHP boolean values and MySQL (faked) boolean values
 *
 * @param mixed $data Value to be translated
 * @return mixed Converted boolean value
 */
	public function boolean($data) {
		if ($data === true || $data === false) {
			if ($data === true) {
				return 1;
			}
			return 0;
		} else {
			if (intval($data !== 0)) {
				return true;
			}
			return false;
		}
	}

/**
 * Begins a transaction.  Returns true if the transaction was
 * started successfully, otherwise false.
 *
 * @param unknown_type $model
 * @return boolean True on success, false on fail
 * (i.e. if the database/model does not support transactions).
 */
	public function begin(Model $model) {
		if (parent::begin($model)) {
			if (db2_autocommit($this->connection, DB2_AUTOCOMMIT_OFF)) {
				$this->_transactionStarted = true;
				return true;
			}
		}
		return false;
	}

/**
 * Commit a transaction
 *
 * @param unknown_type $model
 * @return boolean True on success, false on fail
 * (i.e. if the database/model does not support transactions,
 * or a transaction has not started).
 */
	public function commit(Model $model) {
		if (parent::commit($model)) {
			if (db2_commit($this->connection)) {
				$this->_transactionStarted = false;
				db2_autocommit($this->connection, DB2_AUTOCOMMIT_ON);
				return true;
			}
		}
		return false;
	}

/**
 * Rollback a transaction
 *
 * @param unknown_type $model
 * @return boolean True on success, false on fail
 * (i.e. if the database/model does not support transactions,
 * or a transaction has not started).
 */
	public function rollback(Model $model) {
		if (parent::rollback($model)) {
			$this->_transactionStarted = false;
			db2_autocommit($this->connection, DB2_AUTOCOMMIT_ON);
			return db2_rollback($this->connection);
		}
		return false;
	}

/**
 * Removes Identity (primary key) column from update data before returning to parent
 *
 * @param Model $model
 * @param array $fields
 * @param array $values
 * @return array
 */
	public function update(Model $model, $fields = array(), $values = array()) {
		foreach ($fields as $i => $field) {
			if ($field == $model->primaryKey) {
				unset ($fields[$i]);
				unset ($values[$i]);
				break;
			}
		}
		return parent::update($model, $fields, $values);
	}

/**
 * Returns a formatted error message from previous database operation.
 * DB2 distinguishes between statement and connnection errors so we
 * must check for both.
 *
 * @return string Error message with error number
 */
	public function lastError() {
		if (db2_stmt_error()) {
			return db2_stmt_error() . ': ' . db2_stmt_errormsg();
		} elseif (db2_conn_error()) {
			return db2_conn_error() . ': ' . db2_conn_errormsg();
		}
		return null;
	}

/**
 * Returns number of affected rows in previous database operation. If no previous operation exists,
 * this returns false.
 *
 * @return integer Number of affected rows
 */
	public function lastAffected() {
		if ($this->_result) {
			return db2_num_rows($this->_result);
		}
		return null;
	}

/**
 * Returns number of rows in previous resultset. If no previous resultset exists,
 * this returns false.
 *
 * @return integer Number of rows in resultset
 */
	public function lastNumRows() {
		if ($this->_result) {
			return db2_num_rows($this->_result);
		}
		return null;
	}

/**
 * Returns the ID generated from the previous INSERT operation.
 *
 * @param unknown_type $source
 * @return in
 */
	public function lastInsertId($source = null) {
		$data = $this->fetchRow(sprintf('SELECT SYSIBM.IDENTITY_VAL_LOCAL() AS ID FROM %s FETCH FIRST ROW ONLY', $source));

		if ($data && isset($data[0]['id'])) {
			return $data[0]['id'];
		}
		return null;
	}

/**
 * Returns a limit statement in the correct format for the particular database.
 *
 * @param integer $limit Limit of results returned
 * @param integer $offset Offset from which to start results
 * @return string SQL limit/offset statement
 */
	public function limit($limit, $offset = null) {
		if ($limit) {
			$rt = '';

			// If limit is not in the passed value already, add a limit clause.
			if (!strpos(strtolower($limit), 'limit') || strpos(strtolower($limit), 'limit') === 0) {
				$rt = sprintf('FETCH FIRST %d ROWS ONLY', $limit);
			}

			// TODO: Implement paging with the offset.  This could get hairy.
			/*
			WITH WHOLE AS
			(SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY,
			ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS RN
			FROM EMPLOYEE)
			SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY, RN
			FROM WHOLE
			WHERE RN BETWEEN 10 AND 15
			*/

			/*
			if ($offset) {
				$rt .= ' ' . $offset . ',';
			}

			$rt .= ' ' . $limit;
			*/

			return $rt;
		}
		return null;
	}

/**
 * Converts database-layer column types to basic types
 *
 * @param string $real Real database-layer column type (i.e. "varchar(255)")
 * @return string Abstract column type (i.e. "string")
 */
	public function column($real) {
		if (is_array($real)) {
			$col = $real['name'];

			if (isset($real['limit'])) {
				$col .= '(' . $real['limit'] . ')';
			}
			return $col;
		}
		$col = str_replace(')', '', $real);
		$limit = null;
		if (strpos($col, '(') !== false) {
			list($col, $limit) = explode('(', $col);
		}

		if (in_array($col, array('date', 'time', 'datetime', 'timestamp'))) {
			return $col;
		}

		if ($col === 'smallint') {
			return 'boolean';
		}

		if (strpos($col, 'char') !== false) {
			return 'string';
		}

		if (strpos($col, 'clob') !== false) {
			return 'text';
		}

		if (strpos($col, 'blob') !== false || $col === 'image') {
			return 'binary';
		}

		if (in_array($col, array('double', 'real', 'decimal'))) {
			return 'float';
		}
		return 'text';
	}

/**
 * Maps a result set to an array so that returned fields are
 * grouped by model.  Any calculated fields, or fields that
 * do not correspond to a particular model belong under array
 * key 0.
 *
 * 1. Gets the column headers
 * {{{
 * Post.id
 * Post.title
 *
 *  [0] => Array
 *       (
 *           [0] => Post
 *           [1] => id
 *       )
 *
 *  [1] => Array
 *      (
 *          [0] => Post
 *          [1] => title
 *      )
 * }}}
 * @param unknown_type $results
 */
	public function resultSet(&$results, $sql = null) {
		$this->results =& $results;
		$this->map = $this->_resultMap[$this->results];
	}

/**
 * Fetches the next row from the current result set
 * Maps the records in the $result property to the map
 * created in resultSet().
 *
 * 2. Gets the actual values.
 *
 * @return unknown
 */
	public function fetchResult() {
		if ($row = db2_fetch_array($this->results)) {
			$resultRow = array();
			$i = 0;

			foreach ($row as $index => $field) {
				$table = $this->map[$index][0];
				$column = strtolower($this->map[$index][1]);
				$resultRow[$table][$column] = $row[$index];
				$i++;
			}
			return $resultRow;
		}
		return false;
	}
}
