<?php
Library::import('recess.database.pdo.IPdoDataSourceProvider');
Library::import('recess.database.pdo.RecessType');
/**
* MySql Data Source Provider
*
* @author Kris Jordan <hide@address.com>
* @copyright 2008, 2009 Kris Jordan
* @package Recess PHP Framework
* @license MIT
* @link http://www.recessframework.org/
*/
class MysqlDataSourceProvider implements IPdoDataSourceProvider {
protected static $mysqlToRecessMappings;
protected static $recessToMysqlMappings;
protected $pdo = null;
/**
* Initialize with a reference back to the PDO object.
*
* @param PDO $pdo
*/
function init(PDO $pdo) {
$this->pdo = $pdo;
}
/**
* List the tables in a data source.
* @return array(string) The tables in the data source ordered alphabetically.
*/
function getTables() {
$results = $this->pdo->query('SHOW TABLES');
$tables = array();
foreach($results as $result) {
$tables[] = $result[0];
}
sort($tables);
return $tables;
}
/**
* List the column names of a table alphabetically.
* @param string $table Table whose columns to list.
* @return array(string) Column names sorted alphabetically.
*/
function getColumns($table) {
try {
$results = $this->pdo->query('SHOW COLUMNS FROM ' . $table . ';');
} catch(Exception $e) {
return array();
}
$columns = array();
foreach($results as $result) {
$columns[] = $result['Field'];
}
sort($columns);
return $columns;
}
/**
* Retrieve the a table's RecessTableDescriptor.
*
* @param string $table Name of table.
* @return RecessTableDescriptor
*/
function getTableDescriptor($table) {
Library::import('recess.database.pdo.RecessTableDescriptor');
$tableDescriptor = new RecessTableDescriptor();
$tableDescriptor->name = $table;
try {
$results = $this->pdo->query('SHOW COLUMNS FROM ' . $table . ';');
$tableDescriptor->tableExists = true;
} catch (PDOException $e) {
$tableDescriptor->tableExists = false;
return $tableDescriptor;
}
foreach($results as $result) {
$tableDescriptor->addColumn(
$result['Field'],
$this->getRecessType($result['Type']),
$result['Null'] == 'NO' ? false : true,
$result['Key'] == 'PRI' ? true : false,
$result['Default'] == null ? '' : $result['Default'],
$result['Extra'] == 'auto_increment' ? array('autoincrement' => true) : array());
}
return $tableDescriptor;
}
function getRecessType($mysqlType) {
if(strtolower($mysqlType) == 'tinyint(1)')
return RecessType::BOOLEAN;
if( ($parenPos = strpos($mysqlType,'(')) !== false ) {
$mysqlType = substr($mysqlType,0,$parenPos);
}
if( ($spacePos = strpos($mysqlType,' '))) {
$mysqlType = substr($mysqlType(0,$spacePos));
}
$mysqlType = strtolower(rtrim($mysqlType));
$mysqlToRecessMappings = MysqlDataSourceProvider::getMysqlToRecessMappings();
if(isset($mysqlToRecessMappings[$mysqlType])) {
return $mysqlToRecessMappings[$mysqlType];
} else {
return RecessType::STRING;
}
}
static function getMysqlToRecessMappings() {
if(!isset(self::$mysqlToRecessMappings)) {
self::$mysqlToRecessMappings = array(
'enum' => RecessType::STRING,
'binary' => RecessType::STRING,
'varbinary' => RecessType::STRING,
'varchar' => RecessType::STRING,
'char' => RecessType::STRING,
'national' => RecessType::STRING,
'text' => RecessType::TEXT,
'tinytext' => RecessType::TEXT,
'mediumtext' => RecessType::TEXT,
'longtext' => RecessType::TEXT,
'set' => RecessType::TEXT,
'blob' => RecessType::BLOB,
'tinyblob' => RecessType::BLOB,
'mediumblob' => RecessType::BLOB,
'longblob' => RecessType::BLOB,
'int' => RecessType::INTEGER,
'integer' => RecessType::INTEGER,
'tinyint' => RecessType::INTEGER,
'smallint' => RecessType::INTEGER,
'mediumint' => RecessType::INTEGER,
'bigint' => RecessType::INTEGER,
'bit' => RecessType::INTEGER,
'bool' => RecessType::BOOLEAN,
'boolean' => RecessType::BOOLEAN,
'float' => RecessType::FLOAT,
'double' => RecessType::FLOAT,
'decimal' => RecessType::STRING,
'dec' => RecessType::STRING,
'year' => RecessType::INTEGER,
'date' => RecessType::DATE,
'datetime' => RecessType::DATETIME,
'timestamp' => RecessType::TIMESTAMP,
'time' => RecessType::TIME,
);
}
return self::$mysqlToRecessMappings;
}
static function getRecessToMysqlMappings() {
if(!isset(self::$recessToMysqlMappings)) {
self::$recessToMysqlMappings = array(
RecessType::BLOB => 'BLOB',
RecessType::BOOLEAN => 'TINYINT(1)',
RecessType::DATE => 'DATE',
RecessType::DATETIME => 'DATETIME',
RecessType::FLOAT => 'FLOAT',
RecessType::INTEGER => 'INTEGER',
RecessType::STRING => 'VARCHAR(255)',
RecessType::TEXT => 'TEXT',
RecessType::TIME => 'TIME',
RecessType::TIMESTAMP => 'TIMESTAMP',
);
}
return self::$recessToMysqlMappings;
}
/**
* Drop a table from MySql database.
*
* @param string $table Name of table.
*/
function dropTable($table) {
return $this->pdo->exec('DROP TABLE ' . $table);
}
/**
* Empty a table from MySql database.
*
* @param string $table Name of table.
*/
function emptyTable($table) {
return $this->pdo->exec('DELETE FROM ' . $table);
}
/**
* Given a Table Definition, return the CREATE TABLE SQL statement
* in the MySQL's syntax.
*
* @param RecessTableDescriptor $tableDescriptor
*/
function createTableSql(RecessTableDescriptor $definition) {
$sql = 'CREATE TABLE ' . $definition->name;
$mappings = MysqlDataSourceProvider::getRecessToMysqlMappings();
$columnSql = null;
foreach($definition->getColumns() as $column) {
if(isset($columnSql)) { $columnSql .= ', '; }
$columnSql .= "\n\t" . $column->name . ' ' . $mappings[$column->type];
if($column->isPrimaryKey) {
$columnSql .= ' NOT NULL';
if(isset($column->options['autoincrement'])) {
$columnSql .= ' AUTO_INCREMENT';
}
$columnSql .= ' PRIMARY KEY';
}
}
$columnSql .= "\n";
return $sql . ' (' . $columnSql . ')';
}
/**
* Sanity check and semantic sugar from higher level
* representation of table pushed down to the RDBMS
* representation of the table.
*
* @param string $table
* @param RecessTableDescriptor $descriptor
*/
function cascadeTableDescriptor($table, RecessTableDescriptor $descriptor) {
$sourceDescriptor = $this->getTableDescriptor($table);
if(!$sourceDescriptor->tableExists) {
$descriptor->tableExists = false;
return $descriptor;
}
$sourceColumns = $sourceDescriptor->getColumns();
$errors = array();
foreach($descriptor->getColumns() as $column) {
if(isset($sourceColumns[$column->name])) {
if($column->isPrimaryKey && !$sourceColumns[$column->name]->isPrimaryKey) {
$errors[] = 'Column "' . $column->name . '" is not the primary key in table ' . $table . '.';
}
if($sourceColumns[$column->name]->type != $column->type) {
$errors[] = 'Column "' . $column->name . '" type "' . $column->type . '" does not match database column type "' . $sourceColumns[$column->name]->type . '".';
}
} else {
$errors[] = 'Column "' . $column->name . '" does not exist in table ' . $table . '.';
}
}
if(!empty($errors)) {
throw new RecessException(implode(' ', $errors), get_defined_vars());
} else {
return $sourceDescriptor;
}
}
/**
* Fetch all returns columns typed as Recess expects:
* i.e. Dates become Unix Time Based and TinyInts are converted to Boolean
*
* TODO: Refactor this into the query code so that MySql does the type conversion
* instead of doing it slow and manually in PHP.
*
* @param PDOStatement $statement
* @return array fetchAll() of statement
*/
function fetchAll(PDOStatement $statement) {
try {
$columnCount = $statement->columnCount();
$manualFetch = false;
$booleanColumns = array();
$dateColumns = array();
$timeColumns = array();
for($i = 0 ; $i < $columnCount; $i++) {
$meta = $statement->getColumnMeta($i);
if(isset($meta['native_type'])) {
switch($meta['native_type']) {
case 'TIMESTAMP': case 'DATETIME': case 'DATE':
$dateColumns[] = $meta['name'];
break;
case 'TIME':
$timeColumns[] = $meta['name'];
break;
}
} else {
if($meta['len'] == 1) {
$booleanColumns[] = $meta['name'];
}
}
}
if( !empty($booleanColumns) ||
!empty($datetimeColumns) ||
!empty($dateColumns) ||
!empty($timeColumns)) {
$manualFetch = true;
}
} catch(PDOException $e) {
return $statement->fetchAll();
}
if(!$manualFetch) {
return $statement->fetchAll();
} else {
$results = array();
while($result = $statement->fetch()) {
foreach($booleanColumns as $column) {
$result->$column = $result->$column == 1;
}
foreach($dateColumns as $column) {
$result->$column = strtotime($result->$column);
}
foreach($timeColumns as $column) {
$result->$column = strtotime('1970-01-01 ' . $result->$column);
}
$results[] = $result;
}
return $results;
}
}
function getStatementForBuilder(SqlBuilder $builder, $action, PdoDataSource $source) {
$criteria = $builder->getCriteria();
$builderTable = $builder->getTable();
$tableDescriptors = array();
foreach($criteria as $criterion) {
$table = $builderTable;
$column = $criterion->column;
if(strpos($column,'.') !== false) {
$parts = explode('.', $column);
$table = $parts[0];
$column = $parts[1];
}
if(!isset($tableDescriptors[$table])) {
$tableDescriptors[$table] = $source->getTableDescriptor($table)->getColumns();
}
if(isset($tableDescriptors[$table][$column])) {
switch($tableDescriptors[$table][$column]->type) {
case RecessType::DATETIME: case RecessType::TIMESTAMP:
if(is_int($criterion->value)) {
$criterion->value = date('Y-m-d H:i:s', $criterion->value);
} else {
$criterion->value = null;
}
break;
case RecessType::DATE:
$criterion->value = date('Y-m-d', $criterion->value);
break;
case RecessType::TIME:
$criterion->value = date('H:i:s', $criterion->value);
break;
case RecessType::BOOLEAN:
$criterion->value = $criterion->value == true ? 1 : 0;
break;
case RecessType::INTEGER:
if(!is_numeric($criterion->value)) {
$criterion->value = null;
} else {
$criterion->value = (int)$criterion->value;
}
break;
case RecessType::FLOAT:
if(!is_numeric($criterion->value)) {
$criterion->value = null;
}
break;
}
}
}
$sql = $builder->$action();
$statement = $source->prepare($sql);
$arguments = $builder->getPdoArguments();
foreach($arguments as &$argument) {
// Begin workaround for PDO's poor numeric binding
$queryParameter = $argument->getQueryParameter();
if(is_numeric($queryParameter)) { continue; }
// End Workaround
// Ignore parameters that aren't used in this $action (i.e. assignments in select)
$param = $argument->getQueryParameter();
if(''===$param || strpos($sql, $param) === false) { continue; }
$statement->bindValue($param, $argument->value);
}
return $statement;
}
/**
* @param SqlBuilder $builder
* @param string $action
* @param PdoDataSource $source
* @return boolean
*/
function executeSqlBuilder(SqlBuilder $builder, $action, PdoDataSource $source) {
return $this->getStatementForBuilder($builder, $action, $source)->execute();
}
}
?>