CakePHP 2.x DataSource ODBC · PDO · iSeriesAccess · DB2/400 (tested with V5R1). ALPHA / UNSTABLE.

Odbc.php

<?php

/**
 * ODBC for DBO. Tested with AS400 V5R1 and iSeriesAccess unixodbc
 *
 * PHP versions 4 and 5
 *
 * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
 * Copyright 2005-2009, Cake Software Foundation, Inc. (http://cakefoundation.org)
 *
 * 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
 * @package       datasources
 * @subpackage    datasources.models.datasources.dbo
 * @since         CakePHP Datasources v 0.1
 * @license       MIT License (http://www.opensource.org/licenses/mit-license.php)
 */

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

/**
 * Short description for class.
 *
 * Long description for class
 *
 * @package       cake
 * @subpackage    cake.cake.libs.model.datasources.dbo
 */
class Odbc extends DboSource {

/**
 * Driver description
 *
 * @var string
 */
	public $description = "ODBC DBO Driver";

/**
 * Database keyword used to assign aliases to identifiers.
 *
 * @var string
 */
	public $alias = "";

/**
 * Table/column starting quote
 *
 * @var string
 */
	public $startQuote = "";

/**
 * Table/column end quote
 *
 * @var string
 */
	public $endQuote = "";

/**
 * Columns
 *
 * @var array
 */
	//var $columns = array();

   private $columns = array('primary_key' => array('name' => 'int(11) DEFAULT NULL auto_increment'),
					'string' => array('type' => 'varchar', 'limit' => '255'),
					'char' => array('type' => 'char', 'limit' => '255'),
					'varchar' => array('type' => 'varchar', 'limit' => '255'),
					'text' => array('type' => 'text'),
					'integer' => array('type' => 'int', 'limit' => '11'),
					'smallint' => array('type' => 'int', 'limit' => '6'),
					'float' => array('type' => 'float'),
					'numeric' => array('type' => 'numeric'),
					'decimal' => array('type' => 'numeric'),
					'datetime' => array('type' => 'datetime', 'format' => 'Y-m-d h:i:s', 'formatter' => 'date'),
					'timestamp' => array('type' => 'datetime', 'format' => 'Y-m-d h:i:s', 'formatter' => 'date'),
					'time' => array('type' => 'time', 'format' => 'h:i:s', 'formatter' => 'date'),
					'date' => array('type' => 'date', 'format' => 'd/m/Y', 'formatter' => 'date'),
					'binary' => array('type' => 'blob'),
					'boolean' => array('type' => 'tinyint', 'limit' => '1'));

/**
 * Whether or not to cache the results of DboSource::name() and DboSource::conditions()
 * into the memory cache.  Set to false to disable the use of the memory cache.
 *
 * @var boolean.
 */
	public $cacheMethods = true;

/**
* 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() {
		$this->config;
		$this->connected = false;
		try {
			$flags = array(
				PDO::ATTR_PERSISTENT => $this->config['persistent'],
				PDO::ATTR_EMULATE_PREPARES => true,
			);
			if (!empty($this->config['encoding'])) {
				//$flags[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $config['encoding'];
			}
			$this->_connection = new PDO(
				"odbc:{$this->config['driver']}", null, null, $flags
			);
			$this->_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$this->connected = true;
		} catch (PDOException $e) {
			throw new MissingConnectionException(array('class' => $e->getMessage()));
		}
		return $this->connected;
	}

/**
 * Check if the ODBC extension is installed/loaded
 *
 * @return boolean
 */
	public function enabled() {
		return in_array('odbc', PDO::getAvailableDrivers());
	}

/**
 * Returns an array of sources (tables) in the database.
 *
 * @return array Array of tablenames in the database
 */
	function listSources() {
		$cache = parent::listSources();
		if ($cache != null) {
			return $cache;
		}
		
		$result = $this->_connection->query("select * from qsys2.systables where TABLE_SCHEMA = '".$this->config['database']."'");
		$tables = array_map('trim', $result->fetchAll(PDO::FETCH_COLUMN, 0));
		unset($result);
		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();
		//$sql = 'SELECT * FROM '.$this->config['database'].'.'.$this->fullTableName($model, false);
		
		$sql = "select column_name, data_type, length, numeric_scale from qsys2.syscolumns where table_schema = '".$this->config['database']."' and table_name = '".$this->fullTableName($model, false)."'";
		
		$fields = array();
		try{
			$result = $this->_connection->query($sql);
		} catch (PDOException $e) {
			debug($e->getMessage());
		}
		if ($result instanceof PDOStatement == false) {
			return $fields;
		}		
		$rows = $result->fetchAll(PDO::FETCH_ASSOC);	
	
		if (!is_array($rows)) {
			return $fields;
		}
		unset($result);
		
		//$fields['id'] = $this->columns['integer'];
		
		foreach ($rows as $row) {		
			//debug($this->columns);			
			$cols = array_keys($row);	
			$row['DATA_TYPE'] = strtolower(trim($row['DATA_TYPE']));			
			$fields[$row['COLUMN_NAME']] = $this->columns[$row['DATA_TYPE']];						
		}
		//debug($fields);
		$this->_cacheDescription($model->tablePrefix . $model->table, $fields);
	
		return $fields;
	}

/**
 * Enter description here...
 *
 * @param unknown_type $results
 */
 

	public function resultSet(&$results) {
		$this->map = array();
		$clean = substr($results->queryString, strpos($results->queryString, " ") + 1);
		$clean = substr($clean, 0, strpos($clean, ' FROM') - strlen($clean));
		$parts = explode(", ", $clean);
		foreach ($parts as $key => $value) {
			list($table, $name) = pluginSplit($value, false, 0);
			if (!$table && strpos($name, $this->virtualFieldSeparator) !== false) {
				$name = substr(strrchr($name, " "), 1);
				
			}
		  	$this->map[$key] = array($table, $name, "VAR_STRING");
		}
				
	}

	

/**
 * Fetches the next row from the current result set
 *
 * @return unknown
 */

	/**
 * Fetches the next row from the current result set
 *
 * @return mixed array with results fetched and mapped to column names or false if there is no results left to fetch
 */
	public function fetchResult() {
	    
		if ($row = $this->_result->fetch()) {
			$resultRow = array();
			foreach ($this->map as $col => $meta) {
				list($table, $column, $type) = $meta;
				if (strpos($column,'COUNT(')!== false) {
					$column = 'count';
				}
				$resultRow[$table][$column] = trim($row[$col]);
				if ($type === 'boolean' && !is_null($row[$col])) {
					$resultRow[$table][$column] = $this->boolean($resultRow[$table][$column]);
				}
			}
			
			return $resultRow;
		}
		$this->_result->closeCursor();
		return false;
	}

/**
 * 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 = 0, $offset = null, $table = "") {	
		$statment = "";
		
		if ($limit == 0) {
			return null;	
		} else {
			
			if ($offset > 0) {
				$statment .= "and (RRN(".$table.") > ".$offset.") ";
			}		
			$statment .= "FETCH FIRST ".$limit." ROWS ONLY OPTIMIZE FOR ".$limit." ROWS";
			
			//debug($statment);
			return $statment;
		}
	}

	/**
 * Returns an SQL calculation, i.e. COUNT() or MAX()
 *
 * @param model $model
 * @param string $func Lowercase name of SQL function, i.e. 'count' or 'max'
 * @param array $params Function parameters (any values must be quoted manually)
 * @return string An SQL calculation function
 */
	public function calculate($model, $func, $params = array()) {
		$params = (array)$params;
		
		switch (strtolower($func)) {
			case 'count':
				if (!isset($params[0])) {
					$params[0] = '*';
				}
				if (!isset($params[1])) {
					$params[1] = 'count';
				}
				if (is_object($model) && $model->isVirtualField($params[0])){
					$arg = $this->__quoteFields($model->getVirtualField($params[0]));				
				} else {
					$arg = $this->name($params[0]);
				}
				return "COUNT($arg) $params[1]";
			case 'max':
			case 'min':
				if (!isset($params[1])) {
					$params[1] = $params[0];
				}
				if (is_object($model) && $model->isVirtualField($params[0])) {
					$arg = $this->__quoteFields($model->getVirtualField($params[0]));
				} else {
					$arg = $this->name($params[0]);
				}
				return strtoupper($func) . '(' . $arg . ') ' . $this->name($params[1]);
			break;
		}
	}
	
	public function introspectType($value) {  	
		if (intval($value) == $value) {
		   return 'integer';
		} else {
		   return parent::introspectType($value);
		}
	}

/**
 * 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 data
 */
	public function value($data, $column = null) {
		if (is_array($data) && !empty($data)) {
			return array_map(
				array(&$this, 'value'),
				$data, array_fill(0, count($data), $column)
			);
		} elseif (is_object($data) && isset($data->type, $data->value)) {
			if ($data->type == 'identifier') {
				return $this->name($data->value);
			} elseif ($data->type == 'expression') {
				return $data->value;
			}
		} elseif (in_array($data, array('{$__cakeID__$}', '{$__cakeForeignKey__$}'), true)) {
			return $data;
		}

		if ($data === null || (is_array($data) && empty($data))) {
			return 'NULL';
		}
		
		if (empty($column)) {			
			$column = $this->introspectType($data);
			//debug($column." ".$data);
		}
		
		switch ($column) {
			case 'binary':
				return $this->_connection->quote($data, PDO::PARAM_LOB);
			break;
			case 'boolean':
				return $this->_connection->quote($this->boolean($data, true), PDO::PARAM_BOOL);
			break;
			case 'smallint':
			case 'integer':
			case 'int':
			case 'numeric': {
				return "$data";
				break;
			}
			case 'string':
			case 'text':
				if ((is_int($data) || $data === '0') || (
					is_numeric($data) && strpos($data, ',') === false &&
					$data[0] != '0' && strpos($data, 'e') === false)
				) {
					//return $data;
				}
				return "'$data'";
			default:
				if ($data === '') {
					return 'NULL';
				}
				if (is_float($data)) {
					return sprintf('%F', $data);
				}
				if ((is_int($data) || $data === '0') || (
					is_numeric($data) && strpos($data, ',') === false &&
					$data[0] != '0' && strpos($data, 'e') === false)
				) {
					//return $data;
				}
				return "'$data'";
			break;
		}
	}

/**
 * Builds and generates an SQL statement from an array.	 Handles final clean-up before conversion.
 *
 * @param array $query An array defining an SQL query
 * @param Model $model The model object which initiated the query
 * @return string An executable SQL statement
 * @see DboSource::renderStatement()
 */
	public function buildStatement($query, $model) {
		$query = array_merge(array('offset' => null, 'joins' => array()), $query);
		if (!empty($query['joins'])) {
			$count = count($query['joins']);
			for ($i = 0; $i < $count; $i++) {
				if (is_array($query['joins'][$i])) {
					$query['conditions'][] = $query['joins'][$i]['conditions'];
				}
			}
		}		
				
		if (isset($query['conditions'][$this->fullTableName($model, false).'.id'])) {
			$query['conditions'][$this->fullTableName($model, false).'.id'] = intval($query['conditions'][$this->fullTableName($model, false).'.id']);
		}		
	
		return $this->renderStatement('select', array(
			'conditions' => $this->conditions($query['conditions'], true, true, $model),
			//'fields' => "RRN(".$query['table'].") ID, ".implode(', ', $query['fields']),
			'fields' => implode(', ', $query['fields']),						
			'table' => $this->config['database'].".".$query['table'],			
			'alias' => $this->alias . $this->name($query['alias']),
			'order' => $this->order($query['order'], 'ASC', $model),
			'limit' => $this->limit($query['limit'], $query['offset'], $query['table']),
			'joins' => implode(' ', $query['joins']),
			'group' => $this->group($query['group'], $model)
		));	
	}
	
	public function update(Model $model, $fields = array(), $values = null, $conditions = null) {
		if ($values == null) {
			$combined = $fields;
		} else {
			$combined = array_combine($fields, $values);
		}
		
		//debug($conditions);

		$fields = implode(', ', $this->_prepareUpdateFields($model, $combined, empty($conditions)));
		$joins = "";
		$alias = $this->fullTableName($model);
		$table = $this->config['database'].".".$this->fullTableName($model);
						
		$conditions = $this->_matchRecords($model, $conditions);
		
		if ($conditions === false) {
			return false;
		}
		$query = compact('table', 'alias', 'joins', 'fields', 'conditions');		
		
		if (!$this->execute($this->renderStatement('update', $query))) {
			$model->onError();
			return false;
		}
		return true;
	}
	
	
	
/**
 * Renders a final SQL JOIN statement
 *
 * @param array $data
 * @return string
 */
	public function renderJoinStatement($data) {
		extract($data);
		return trim(", {$table} {$alias}");
	}

/**
 * Generates the fields list of an SQL query.
 *
 * @param Model $model
 * @param string $alias Alias table name
 * @param mixed $fields
 * @param boolean $quote If false, returns fields array unquoted
 * @return array
 */
	public function fields(Model $model, $alias = null, $fields = array(), $quote = true) {
		if (empty($fields) && !$model->schema(true)) {
			$fields = '*';
		}				
		
		return parent::fields($model, $alias, $fields, $quote);
		
	}

}