Odbc datasource CakePHP for Throughbred odbc connection. UNSTABLE

Odbc.php

<?php
/**
 * ODBC for DBO
 *
 * 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();

	var $columns = array('primary_key' => array('name' => 'int(11) DEFAULT NULL auto_increment'),
						'string' => array('name' => 'varchar', 'limit' => '255'),
						'text' => array('name' => 'text'),
						'integer' => array('name' => 'int', 'limit' => '11'),
						'float' => array('name' => 'float'),
						'datetime' => array('name' => 'datetime', 'format' => 'Y-m-d h:i:s', 'formatter' => 'date'),
						'timestamp' => array('name' => 'datetime', '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' => '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 ODLQTBLE");
		$tables = array_map('trim', $result->fetchAll(PDO::FETCH_COLUMN, 1));
		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->fullTableName($model, false);
		$fields = array();
		try{
			$result = $this->_connection->query($sql);
		} catch (PDOException $e) {
			debug($e->getMessage());
		}
		if ($result instanceof PDOStatement == false) {
			return $fields;
		}
		$row = $result->fetch(PDO::FETCH_ASSOC);
		if (!is_array($row)) {
			return $fields;
		}
		unset($result);
		$cols = array_keys($row);
		foreach ($cols as $column) {
			$fields[$column] = array('type' => 'text', 'null' => true);
		}
		$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, $offset = null) {
		return null;
	}

	/**
 * 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) AS '$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 . ') AS ' . $this->name($params[1]);
			break;
		}
	}

/**
 * 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);
		}

		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 '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'];
				}
			}
		}
		return parent::buildStatement($query, $model);
	}

/**
 * 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);
	}
///**
// * Creates a WHERE clause by parsing given conditions array.  Used by DboSource::conditions().
// *
// * @param array $conditions Array or string of conditions
// * @param boolean $quoteValues If true, values should be quoted
// * @param Model $model A reference to the Model instance making the query
// * @return string SQL fragment
// */
//	public function conditionKeysToString($conditions, $quoteValues = true, $model = null) {
//		$out = array();
//		$data = $columnType = null;
//		$bool = array('and', 'or', 'not', 'and not', 'or not', 'xor', '||', '&&');
//
//		foreach ($conditions as $key => $value) {
//			$join = ' AND ';
//			$not = null;
//
//			if (is_array($value)) {
//				$valueInsert = (
//					!empty($value) &&
//					(substr_count($key, '?') === count($value) || substr_count($key, ':') === count($value))
//				);
//			}
//
//			if (is_numeric($key) && empty($value)) {
//				continue;
//			} elseif (is_numeric($key) && is_string($value)) {
//				$out[] = $not . $this->_quoteFields($value);
//			} elseif ((is_numeric($key) && is_array($value)) || in_array(strtolower(trim($key)), $bool)) {
//				if (in_array(strtolower(trim($key)), $bool)) {
//					$join = ' ' . strtoupper($key) . ' ';
//				} else {
//					$key = $join;
//				}
//				$value = $this->conditionKeysToString($value, $quoteValues, $model);
//
//				if (strpos($join, 'NOT') !== false) {
//					if (strtoupper(trim($key)) === 'NOT') {
//						$key = 'AND ' . trim($key);
//					}
//					$not = 'NOT ';
//				}
//
//				if (empty($value[1])) {
//					if ($not) {
//						$out[] = $not . '(' . $value[0] . ')';
//					} else {
//						$out[] = $value[0] ;
//					}
//				} else {
//					$out[] = '(' . $not . '(' . implode(') ' . strtoupper($key) . ' (', $value) . '))';
//				}
//			} else {
//				if (is_object($value) && isset($value->type)) {
//					if ($value->type === 'identifier') {
//						$data .= $this->name($key) . ' = ' . $this->name($value->value);
//					} elseif ($value->type === 'expression') {
//						if (is_numeric($key)) {
//							$data .= $value->value;
//						} else {
//							$data .= $this->name($key) . ' LIKE ' . $value->value;
//						}
//					}
//				} elseif (is_array($value) && !empty($value) && !$valueInsert) {
//					$keys = array_keys($value);
//					if ($keys === array_values($keys)) {
//						$count = count($value);
//						if ($count === 1) {
//							$data = $this->_quoteFields($key) . ' LIKE ';
//							$close = false;
//						} else {
//							$data = $this->_quoteFields($key) . ' IN (';
//						}
//						if ($quoteValues) {
//							if (is_object($model)) {
//								$columnType = $model->getColumnType($key);
//							}
//							$data .= implode(', ', $this->value($value, $columnType));
//						}
//						if (!empty($close)) {
//							$data .= ')';
//						}
//					} else {
//						$ret = $this->conditionKeysToString($value, $quoteValues, $model);
//						if (count($ret) > 1) {
//							$data = '(' . implode(') AND (', $ret) . ')';
//						} elseif (isset($ret[0])) {
//							$data = $ret[0];
//						}
//					}
//				} elseif (is_numeric($key) && !empty($value)) {
//					$data = $this->_quoteFields($value);
//				} else {
//					$data = $this->_parseKey($model, trim($key), $value);
//				}
//
//				if ($data != null) {
//					$out[] = $data;
//					$data = null;
//				}
//			}
//		}
//		return $out;
//	}
//
///**
// * Extracts a Model.field identifier and an SQL condition operator from a string, formats
// * and inserts values, and composes them into an SQL snippet.
// *
// * @param Model $model Model object initiating the query
// * @param string $key An SQL key snippet containing a field and optional SQL operator
// * @param mixed $value The value(s) to be inserted in the string
// * @return string
// */
//	protected function _parseKey($model, $key, $value) {
//		$operatorMatch = '/^(((' . implode(')|(', $this->_sqlOps);
//		$operatorMatch .= ')\\x20?)|<[>=]?(?![^>]+>)\\x20?|[>=!]{1,3}(?!<)\\x20?)/is';
//		$bound = (strpos($key, '?') !== false || (is_array($value) && strpos($key, ':') !== false));
//
//		if (strpos($key, ' ') === false) {
//			$operator = 'LIKE';
//		} else {
//			list($key, $operator) = explode(' ', trim($key), 2);
//
//			if (!preg_match($operatorMatch, trim($operator)) && strpos($operator, ' ') !== false) {
//				$key = $key . ' ' . $operator;
//				$split = strrpos($key, ' ');
//				$operator = substr($key, $split);
//				$key = substr($key, 0, $split);
//			}
//		}
//
//		$virtual = false;
//		if (is_object($model) && $model->isVirtualField($key)) {
//			$key = $this->_quoteFields($model->getVirtualField($key));
//			$virtual = true;
//		}
//
//		$type = is_object($model) ? $model->getColumnType($key) : null;
//		$null = $value === null || (is_array($value) && empty($value));
//
//		if (strtolower($operator) === 'not') {
//			$data = $this->conditionKeysToString(
//				array($operator => array($key => $value)), true, $model
//			);
//			return $data[0];
//		}
//
//		$value = $this->value($value, $type);
//
//		if (!$virtual && $key !== '?') {
//			$isKey = (strpos($key, '(') !== false || strpos($key, ')') !== false);
//			$key = $isKey ? $this->_quoteFields($key) : $this->name($key);
//		}
//
//		if ($bound) {
//			return String::insert($key . ' ' . trim($operator), $value);
//		}
//
//		if (!preg_match($operatorMatch, trim($operator))) {
//			$operator .= ' LIKE';
//		}
//		$operator = trim($operator);
//
//		if (is_array($value)) {
//			$value = implode(', ', $value);
//
//			switch ($operator) {
//				case '=':
//					$operator = 'IN';
//				break;
//				case '!=':
//				case '<>':
//					$operator = 'NOT IN';
//				break;
//			}
//			$value = "({$value})";
//		} elseif ($null || $value === 'NULL') {
//			switch ($operator) {
//				case '=':
//					$operator = 'IS';
//				break;
//				case '!=':
//				case '<>':
//					$operator = 'IS NOT';
//				break;
//			}
//		}
//		if ($virtual) {
//			return "({$key}) {$operator} {$value}";
//		}
//		return "{$key} {$operator} {$value}";
//	}

}

Interesting. Is this for CakePHP 1.3 or 2.x? Thanks

@sebastienbarre This is for 2.x you might want to take a took at https://gist.github.com/shrimpwagon/5207702 though as my version is customized for thoroughbred

a DATABASE_CONFIG example would be nice to have in the comments at the top of the code page... I am unclear on how this code is suppsed to get:
$this->_connection = new PDO("odbc:{$this->config['driver']}", null, null, $flags);
filled out correctly... I had to put in:
$this->_connection = new PDO("odbc:MSSQLServer", $this->config['login'], $this->config['password'], $flags);
to get the initial connection... have not progressed past that yet... but I do get my odbc mssql connection. that's a start.

@ceeram, with some minor changes, this works perfect for my cake 2.3 app.
It's selecting ok, but i'm having problems on inserting/updating. I might use 2.x Model instead of my 2.3.
Which 2.x was this script made for?