Subversion-Projekte lars-tiefland.cakephp

<?php

/* SVN FILE: $Id: dbo_db2.php 7945 2008-12-19 02:16:01Z gwoo $ */

/**

 * 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://www.cakephp.org)

 * Copyright 2007, Cake Software Foundation, Inc.

 *

 * Licensed under The MIT License

 * Redistributions of files must retain the above copyright notice.

 *

 * @filesource

 * @copyright     Copyright 2005-2008, Cake Software Foundation, Inc. (http://www.cakefoundation.org)

 * @link          http://www.cakefoundation.org/projects/info/cakephp CakePHP(tm) Project

 * @package       cake

 * @subpackage    cake.cake.libs.model.datasources.dbo

 * @since         CakePHP(tm) v 0.10.5.1790

 * @version       $Revision: 7945 $

 * @modifiedby    $LastChangedBy: gwoo $

 * @lastmodified  $Date: 2008-12-18 18:16:01 -0800 (Thu, 18 Dec 2008) $

 * @license       http://www.opensource.org/licenses/mit-license.php The MIT License

 */

/**

 * 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

 *

 * @package       cake

 * @subpackage    cake.cake.libs.model.datasources.dbo

 */

class DboDb2 extends DboSource {

/**

 * A short description of the type of driver.

 *

 * @var string

 */

	var $description = 'IBM DB2 DBO Driver';

/**

 * The start quote in which database column and table names should be wrapped.

 *

 * @var string

 */

	var $startQuote = '';

/**

 * The end quote in which database column and table names should be wrapped.

 *

 * @var string

 */

	var $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

 */

	var $_baseConfig = array(

		'persistent' 	=> true,

		'login' 		=> 'db2inst1',

		'password' 		=> '',

		'database' 		=> 'cake',

		'schema'		=> '',

		'hostname'		=> '127.0.0.1',

		'port'			=> '50001',

		'encoding'		=> 'UTF-8',

		'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

 */

	var $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 )

 */

	var $_resultMap = array();

/**

 * Connects to the database using options in the given configuration array.

 *

 * @return boolean True if the database could be connected, else false

 */

	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;

	}

/**

 * Disconnects from database.

 *

 * @return boolean True if the database could be disconnected, else false

 */

	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

 * @access 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

 */

	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

 */

	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

 */

	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

 */

	function value($data, $column = null, $safe = false) {

		$parent = parent::value($data, $column, $safe);

		if ($parent != null) {

			return $parent;

		}

		if ($data === null) {

			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

 */

	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).

 */

	function begin(&$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).

 */

	function commit(&$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).

 */

	function rollback(&$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

 */

	function update(&$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

 */

	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

 */

	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

 */

	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

 */

	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

 */

	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")

 */

	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

 */

	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

 */

	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;

	}

}

?>