From 78af2b4630ccde4b03659a259f739f83ae0de9ec Mon Sep 17 00:00:00 2001 From: rojaro <> Date: Mon, 13 Jul 2009 14:02:17 +0000 Subject: - ActiveRecord Port from Yii ... not perfect but works ^^ --- framework/Db/Schema/TDbColumnSchema.php | 145 +++++ framework/Db/Schema/TDbCommandBuilder.php | 656 +++++++++++++++++++++ framework/Db/Schema/TDbCriteria.php | 166 ++++++ framework/Db/Schema/TDbExpression.php | 49 ++ framework/Db/Schema/TDbSchema.php | 193 ++++++ framework/Db/Schema/TDbTableSchema.php | 76 +++ framework/Db/Schema/mssql/TMssqlColumnSchema.php | 57 ++ framework/Db/Schema/mssql/TMssqlCommandBuilder.php | 303 ++++++++++ framework/Db/Schema/mssql/TMssqlPdoAdapter.php | 74 +++ framework/Db/Schema/mssql/TMssqlSchema.php | 312 ++++++++++ framework/Db/Schema/mssql/TMssqlTableSchema.php | 35 ++ framework/Db/Schema/mysql/TMysqlColumnSchema.php | 46 ++ framework/Db/Schema/mysql/TMysqlSchema.php | 205 +++++++ framework/Db/Schema/mysql/TMysqlTableSchema.php | 26 + framework/Db/Schema/oci/TOciColumnSchema.php | 56 ++ framework/Db/Schema/oci/TOciCommandBuilder.php | 122 ++++ framework/Db/Schema/oci/TOciSchema.php | 278 +++++++++ framework/Db/Schema/oci/TOciTableSchema.php | 28 + framework/Db/Schema/pgsql/TPgsqlColumnSchema.php | 58 ++ framework/Db/Schema/pgsql/TPgsqlSchema.php | 284 +++++++++ framework/Db/Schema/pgsql/TPgsqlTableSchema.php | 27 + framework/Db/Schema/sqlite/TSqliteColumnSchema.php | 35 ++ .../Db/Schema/sqlite/TSqliteCommandBuilder.php | 43 ++ framework/Db/Schema/sqlite/TSqliteSchema.php | 134 +++++ 24 files changed, 3408 insertions(+) create mode 100755 framework/Db/Schema/TDbColumnSchema.php create mode 100755 framework/Db/Schema/TDbCommandBuilder.php create mode 100755 framework/Db/Schema/TDbCriteria.php create mode 100755 framework/Db/Schema/TDbExpression.php create mode 100755 framework/Db/Schema/TDbSchema.php create mode 100755 framework/Db/Schema/TDbTableSchema.php create mode 100755 framework/Db/Schema/mssql/TMssqlColumnSchema.php create mode 100755 framework/Db/Schema/mssql/TMssqlCommandBuilder.php create mode 100755 framework/Db/Schema/mssql/TMssqlPdoAdapter.php create mode 100755 framework/Db/Schema/mssql/TMssqlSchema.php create mode 100755 framework/Db/Schema/mssql/TMssqlTableSchema.php create mode 100755 framework/Db/Schema/mysql/TMysqlColumnSchema.php create mode 100755 framework/Db/Schema/mysql/TMysqlSchema.php create mode 100755 framework/Db/Schema/mysql/TMysqlTableSchema.php create mode 100755 framework/Db/Schema/oci/TOciColumnSchema.php create mode 100755 framework/Db/Schema/oci/TOciCommandBuilder.php create mode 100755 framework/Db/Schema/oci/TOciSchema.php create mode 100755 framework/Db/Schema/oci/TOciTableSchema.php create mode 100755 framework/Db/Schema/pgsql/TPgsqlColumnSchema.php create mode 100755 framework/Db/Schema/pgsql/TPgsqlSchema.php create mode 100755 framework/Db/Schema/pgsql/TPgsqlTableSchema.php create mode 100755 framework/Db/Schema/sqlite/TSqliteColumnSchema.php create mode 100755 framework/Db/Schema/sqlite/TSqliteCommandBuilder.php create mode 100755 framework/Db/Schema/sqlite/TSqliteSchema.php (limited to 'framework/Db/Schema') diff --git a/framework/Db/Schema/TDbColumnSchema.php b/framework/Db/Schema/TDbColumnSchema.php new file mode 100755 index 00000000..f2c4faaa --- /dev/null +++ b/framework/Db/Schema/TDbColumnSchema.php @@ -0,0 +1,145 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbExpression'); + +/** + * TDbColumnSchema class describes the column meta data of a database table. + * + * @author Qiang Xue + * @version $Id: TDbColumnSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema + * @since 1.0 + */ +class TDbColumnSchema extends TComponent +{ + /** + * @var string name of this column (without quotes). + */ + public $name; + /** + * @var string raw name of this column. This is the quoted name that can be used in SQL queries. + */ + public $rawName; + /** + * @var boolean whether this column can be null. + */ + public $allowNull; + /** + * @var string the DB type of this column. + */ + public $dbType; + /** + * @var string the PHP type of this column. + */ + public $type; + /** + * @var mixed default value of this column + */ + public $defaultValue; + /** + * @var integer size of the column. + */ + public $size; + /** + * @var integer precision of the column data, if it is numeric. + */ + public $precision; + /** + * @var integer scale of the column data, if it is numeric. + */ + public $scale; + /** + * @var boolean whether this column is a primary key + */ + public $isPrimaryKey; + /** + * @var boolean whether this column is a foreign key + */ + public $isForeignKey; + + + /** + * Initializes the column with its DB type and default value. + * This sets up the column's PHP type, size, precision, scale as well as default value. + * @param string the column's DB type + * @param mixed the default value + */ + public function init($dbType, $defaultValue) + { + $this->dbType=$dbType; + $this->extractType($dbType); + $this->extractLimit($dbType); + if($defaultValue!==null) + $this->extractDefault($defaultValue); + } + + /** + * Extracts the PHP type from DB type. + * @param string DB type + */ + protected function extractType($dbType) + { + if(stripos($dbType,'int')!==false) + $this->type='integer'; + else if(stripos($dbType,'bool')!==false) + $this->type='boolean'; + else if(preg_match('/(real|floa|doub)/i',$dbType)) + $this->type='double'; + else + $this->type='string'; + } + + /** + * Extracts size, precision and scale information from column's DB type. + * @param string the column's DB type + */ + protected function extractLimit($dbType) + { + if(strpos($dbType,'(') && preg_match('/\((.*)\)/',$dbType,$matches)) + { + $values=explode(',',$matches[1]); + $this->size=$this->precision=(int)$values[0]; + if(isset($values[1])) + $this->scale=(int)$values[1]; + } + } + + /** + * Extracts the default value for the column. + * The value is typecasted to correct PHP type. + * @param mixed the default value obtained from metadata + */ + protected function extractDefault($defaultValue) + { + $this->defaultValue=$this->typecast($defaultValue); + } + + /** + * Converts the input value to the type that this column is of. + * @param mixed input value + * @return mixed converted value + */ + public function typecast($value) + { + if(gettype($value)===$this->type || $value===null || $value instanceof TDbExpression) + return $value; + if($value==='') + return $this->type==='string' ? '' : null; + switch($this->type) + { + case 'integer': return (integer)$value; + case 'boolean': return (boolean)$value; + case 'double': return (double)$value; + case 'string': return (string)$value; + default: return $value; + } + } +} diff --git a/framework/Db/Schema/TDbCommandBuilder.php b/framework/Db/Schema/TDbCommandBuilder.php new file mode 100755 index 00000000..7c25888d --- /dev/null +++ b/framework/Db/Schema/TDbCommandBuilder.php @@ -0,0 +1,656 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbSchema'); +prado::using('System.Db.Schema.TDbCriteria'); + +/** + * TDbCommandBuilder provides basic methods to create query commands for tables. + * + * @author Qiang Xue + * @version $Id: TDbCommandBuilder.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema + * @since 1.0 + */ +class TDbCommandBuilder extends TComponent +{ + const PARAM_PREFIX=':yp'; + + private $_schema; + private $_connection; + + /** + * @param TDbSchema the schema for this command builder + */ + public function __construct($schema) + { + $this->_schema=$schema; + $this->_connection=$schema->getDbConnection(); + } + + /** + * @return TDbConnection database connection. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * @return TDbSchema the schema for this command builder. + */ + public function getSchema() + { + return $this->_schema; + } + + /** + * Returns the last insertion ID for the specified table. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @return mixed last insertion id. Null is returned if no sequence name. + */ + public function getLastInsertID($table) + { + $this->ensureTable($table); + if($table->sequenceName!==null) + return $this->_connection->getLastInsertID($table->sequenceName); + else + return null; + } + + /** + * Creates a SELECT command for a single table. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param TDbCriteria the query criteria + * @return TDbCommand query command. + */ + public function createFindCommand($table,$criteria) + { + $this->ensureTable($table); + $select=is_array($criteria->select) ? implode(', ',$criteria->select) : $criteria->select; + $sql="SELECT {$select} FROM {$table->rawName}"; + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + $sql=$this->applyGroup($sql,$criteria->group); + $sql=$this->applyHaving($sql,$criteria->having); + $sql=$this->applyOrder($sql,$criteria->order); + $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset); + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,$criteria->params); + return $command; + } + + /** + * Creates a COUNT(*) command for a single table. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param TDbCriteria the query criteria + * @return TDbCommand query command. + */ + public function createCountCommand($table,$criteria) + { + $this->ensureTable($table); + $criteria->select='COUNT(*)'; + return $this->createFindCommand($table,$criteria); + } + + /** + * Creates a DELETE command. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param TDbCriteria the query criteria + * @return TDbCommand delete command. + */ + public function createDeleteCommand($table,$criteria) + { + $this->ensureTable($table); + $sql="DELETE FROM {$table->rawName}"; + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + $sql=$this->applyGroup($sql,$criteria->group); + $sql=$this->applyHaving($sql,$criteria->having); + $sql=$this->applyOrder($sql,$criteria->order); + $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset); + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,$criteria->params); + return $command; + } + + /** + * Creates an INSERT command. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array data to be inserted (column name=>column value). If a key is not a valid column name, the corresponding value will be ignored. + * @return TDbCommand insert command + */ + public function createInsertCommand($table,$data) + { + $this->ensureTable($table); + $fields=array(); + $values=array(); + $placeholders=array(); + $i=0; + foreach($data as $name=>$value) + { + if(($column=$table->getColumn($name))!==null && ($value!==null || $column->allowNull)) + { + $fields[]=$column->rawName; + if($value instanceof TDbExpression) + $placeholders[]=(string)$value; + else + { + $placeholders[]=self::PARAM_PREFIX.$i; + $values[self::PARAM_PREFIX.$i]=$column->typecast($value); + $i++; + } + } + } + $sql="INSERT INTO {$table->rawName} (".implode(', ',$fields).') VALUES ('.implode(', ',$placeholders).')'; + $command=$this->_connection->createCommand($sql); + + foreach($values as $name=>$value) + $command->bindValue($name,$value); + + return $command; + } + + /** + * Creates an UPDATE command. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array list of columns to be updated (name=>value) + * @param TDbCriteria the query criteria + * @return TDbCommand update command. + */ + public function createUpdateCommand($table,$data,$criteria) + { + $this->ensureTable($table); + $fields=array(); + $values=array(); + $bindByPosition=isset($criteria->params[0]); + $i=0; + foreach($data as $name=>$value) + { + if(($column=$table->getColumn($name))!==null) + { + if($value instanceof TDbExpression) + $fields[]=$column->rawName.'='.(string)$value; + else if($bindByPosition) + { + $fields[]=$column->rawName.'=?'; + $values[]=$column->typecast($value); + } + else + { + $fields[]=$column->rawName.'='.self::PARAM_PREFIX.$i; + $values[self::PARAM_PREFIX.$i]=$column->typecast($value); + $i++; + } + } + } + if($fields===array()) + throw new TDbException('No columns are being updated for table "{0}".', + $table->name); + $sql="UPDATE {$table->rawName} SET ".implode(', ',$fields); + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + $sql=$this->applyOrder($sql,$criteria->order); + $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset); + + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,array_merge($values,$criteria->params)); + + return $command; + } + + /** + * Creates an UPDATE command that increments/decrements certain columns. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param TDbCriteria the query criteria + * @param array counters to be updated (counter increments/decrements indexed by column names.) + * @return TDbCommand the created command + * @throws CException if no counter is specified + */ + public function createUpdateCounterCommand($table,$counters,$criteria) + { + $this->ensureTable($table); + $fields=array(); + foreach($counters as $name=>$value) + { + if(($column=$table->getColumn($name))!==null) + { + $value=(int)$value; + if($value<0) + $fields[]="{$column->rawName}={$column->rawName}-".(-$value); + else + $fields[]="{$column->rawName}={$column->rawName}+".$value; + } + } + if($fields!==array()) + { + $sql="UPDATE {$table->rawName} SET ".implode(', ',$fields); + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + $sql=$this->applyOrder($sql,$criteria->order); + $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset); + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,$criteria->params); + return $command; + } + else + throw new TDbException('No counter columns are being updated for table "{0}".', + $table->name); + } + + /** + * Creates a command based on a given SQL statement. + * @param string the explicitly specified SQL statement + * @param array parameters that will be bound to the SQL statement + * @return TDbCommand the created command + */ + public function createSqlCommand($sql,$params=array()) + { + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,$params); + return $command; + } + + /** + * Alters the SQL to apply JOIN clause. + * @param string the SQL statement to be altered + * @param string the JOIN clause (starting with join type, such as INNER JOIN) + * @return string the altered SQL statement + */ + public function applyJoin($sql,$join) + { + if($join!=='') + return $sql.' '.$join; + else + return $sql; + } + + /** + * Alters the SQL to apply WHERE clause. + * @param string the SQL statement without WHERE clause + * @param string the WHERE clause (without WHERE keyword) + * @return string the altered SQL statement + */ + public function applyCondition($sql,$condition) + { + if($condition!=='') + return $sql.' WHERE '.$condition; + else + return $sql; + } + + /** + * Alters the SQL to apply ORDER BY. + * @param string SQL statement without ORDER BY. + * @param string column ordering + * @return string modified SQL applied with ORDER BY. + */ + public function applyOrder($sql,$orderBy) + { + if($orderBy!=='') + return $sql.' ORDER BY '.$orderBy; + else + return $sql; + } + + /** + * Alters the SQL to apply LIMIT and OFFSET. + * Default implementation is applicable for PostgreSQL, MySQL and SQLite. + * @param string SQL query string without LIMIT and OFFSET. + * @param integer maximum number of rows, -1 to ignore limit. + * @param integer row offset, -1 to ignore offset. + * @return string SQL with LIMIT and OFFSET + */ + public function applyLimit($sql,$limit,$offset) + { + if($limit>=0) + $sql.=' LIMIT '.(int)$limit; + if($offset>0) + $sql.=' OFFSET '.(int)$offset; + return $sql; + } + + /** + * Alters the SQL to apply GROUP BY. + * @param string SQL query string without GROUP BY. + * @param string GROUP BY + * @return string SQL with GROUP BY. + */ + public function applyGroup($sql,$group) + { + if($group!=='') + return $sql.' GROUP BY '.$group; + else + return $sql; + } + + /** + * Alters the SQL to apply HAVING. + * @param string SQL query string without HAVING + * @param string HAVING + * @return string SQL with HAVING + * @since 1.0.1 + */ + public function applyHaving($sql,$having) + { + if($having!=='') + return $sql.' HAVING '.$having; + else + return $sql; + } + + /** + * Binds parameter values for an SQL command. + * @param TDbCommand database command + * @param array values for binding (integer-indexed array for question mark placeholders, string-indexed array for named placeholders) + */ + public function bindValues($command, $values) + { + if(($n=count($values))===0) + return; + if(isset($values[0])) // question mark placeholders + { + for($i=0;$i<$n;++$i) + $command->bindValue($i+1,$values[$i]); + } + else // named placeholders + { + foreach($values as $name=>$value) + { + if($name[0]!==':') + $name=':'.$name; + $command->bindValue($name,$value); + } + } + } + + /** + * Creates a query criteria. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param mixed query condition or criteria. + * If a string, it is treated as query condition (the WHERE clause); + * If an array, it is treated as the initial values for constructing a {@link TDbCriteria} object; + * Otherwise, it should be an instance of {@link TDbCriteria}. + * @param array parameters to be bound to an SQL statement. + * This is only used when the first parameter is a string (query condition). + * In other cases, please use {@link TDbCriteria::params} to set parameters. + * @return TDbCriteria the created query criteria + * @throws CException if the condition is not string, array and TDbCriteria + */ + public function createCriteria($condition='',$params=array()) + { + if(is_array($condition)) + $criteria=new TDbCriteria($condition); + else if($condition instanceof TDbCriteria) + $criteria=clone $condition; + else + { + $criteria=new TDbCriteria; + $criteria->condition=$condition; + $criteria->params=$params; + } + return $criteria; + } + + /** + * Creates a query criteria with the specified primary key. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param mixed primary key value(s). Use array for multiple primary keys. For composite key, each key value must be an array (column name=>column value). + * @param mixed query condition or criteria. + * If a string, it is treated as query condition; + * If an array, it is treated as the initial values for constructing a {@link TDbCriteria}; + * Otherwise, it should be an instance of {@link TDbCriteria}. + * @param array parameters to be bound to an SQL statement. + * This is only used when the second parameter is a string (query condition). + * In other cases, please use {@link TDbCriteria::params} to set parameters. + * @return TDbCriteria the created query criteria + */ + public function createPkCriteria($table,$pk,$condition='',$params=array()) + { + $this->ensureTable($table); + $criteria=$this->createCriteria($condition,$params); + if(!is_array($pk)) // single key + $pk=array($pk); + if(is_array($table->primaryKey) && !isset($pk[0]) && $pk!==array()) // single composite key + $pk=array($pk); + $condition=$this->createInCondition($table,$table->primaryKey,$pk); + if($criteria->condition!=='') + $criteria->condition=$condition.' AND ('.$criteria->condition.')'; + else + $criteria->condition=$condition; + + return $criteria; + } + + /** + * Generates the expression for selecting rows of specified primary key values. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array list of primary key values to be selected within + * @param string column prefix (ended with dot). If null, it will be the table name + * @return string the expression for selection + */ + public function createPkCondition($table,$values,$prefix=null) + { + $this->ensureTable($table); + return $this->createInCondition($table,$table->primaryKey,$values,$prefix); + } + + /** + * Creates a query criteria with the specified column values. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array column values that should be matched in the query (name=>value) + * @param mixed query condition or criteria. + * If a string, it is treated as query condition; + * If an array, it is treated as the initial values for constructing a {@link TDbCriteria}; + * Otherwise, it should be an instance of {@link TDbCriteria}. + * @param array parameters to be bound to an SQL statement. + * This is only used when the second parameter is a string (query condition). + * In other cases, please use {@link TDbCriteria::params} to set parameters. + * @return TDbCriteria the created query criteria + */ + public function createColumnCriteria($table,$columns,$condition='',$params=array()) + { + $this->ensureTable($table); + $criteria=$this->createCriteria($condition,$params); + $bindByPosition=isset($criteria->params[0]); + $conditions=array(); + $values=array(); + $i=0; + foreach($columns as $name=>$value) + { + if(($column=$table->getColumn($name))!==null) + { + if($value!==null) + { + if($bindByPosition) + { + $conditions[]=$table->rawName.'.'.$column->rawName.'=?'; + $values[]=$value; + } + else + { + $conditions[]=$table->rawName.'.'.$column->rawName.'='.self::PARAM_PREFIX.$i; + $values[self::PARAM_PREFIX.$i]=$value; + $i++; + } + } + else + $conditions[]=$table->rawName.'.'.$column->rawName.' IS NULL'; + } + else + throw new TDbException('Table "{0}" does not have a column named "{1}".', + $table->name,$name); + } + $criteria->params=array_merge($values,$criteria->params); + if(isset($conditions[0])) + { + if($criteria->condition!=='') + $criteria->condition=implode(' AND ',$conditions).' AND ('.$criteria->condition.')'; + else + $criteria->condition=implode(' AND ',$conditions); + } + return $criteria; + } + + /** + * Generates the expression for searching the specified keywords within a list of columns. + * The search expression is generated using the 'LIKE' SQL syntax. + * Every word in the keywords must be present and appear in at least one of the columns. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array list of column names for potential search condition. + * @param mixed search keywords. This can be either a string with space-separated keywords or an array of keywords. + * @param string optional column prefix (with dot at the end). If null, the table name will be used as the prefix. + * @param boolean whether the search is case-sensitive. Defaults to true. This parameter + * has been available since version 1.0.4. + * @return string SQL search condition matching on a set of columns. An empty string is returned + * if either the column array or the keywords are empty. + */ + public function createSearchCondition($table,$columns,$keywords,$prefix=null,$caseSensitive=true) + { + $this->ensureTable($table); + if(!is_array($keywords)) + $keywords=preg_split('/\s+/u',$keywords,-1,PREG_SPLIT_NO_EMPTY); + if(empty($keywords)) + return ''; + if($prefix===null) + $prefix=$table->rawName.'.'; + $conditions=array(); + foreach($columns as $name) + { + if(($column=$table->getColumn($name))===null) + throw new TDbException('Table "{0}" does not have a column named "{0}".', + $table->name,$name); + $condition=array(); + foreach($keywords as $keyword) + { + if($caseSensitive) + $condition[]=$prefix.$column->rawName.' LIKE '.$this->_connection->quoteValue('%'.$keyword.'%'); + else + $condition[]='LOWER('.$prefix.$column->rawName.') LIKE LOWER('.$this->_connection->quoteValue('%'.$keyword.'%').')'; + } + $conditions[]=implode(' AND ',$condition); + } + return '('.implode(' OR ',$conditions).')'; + } + + /** + * Generates the expression for selecting rows of specified primary key values. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param mixed the column name(s). It can be either a string indicating a single column + * or an array of column names. If the latter, it stands for a composite key. + * @param array list of key values to be selected within + * @param string column prefix (ended with dot). If null, it will be the table name + * @return string the expression for selection + * @since 1.0.4 + */ + public function createInCondition($table,$columnName,$values,$prefix=null) + { + if(($n=count($values))<1) + return '0=1'; + + $this->ensureTable($table); + + if($prefix===null) + $prefix=$table->rawName.'.'; + + $db=$this->_connection; + + if(is_array($columnName) && count($columnName)===1) + $columnName=reset($columnName); + + if(is_string($columnName)) // simple key + { + if(!isset($table->columns[$columnName])) + throw new TDbException('Table "{0}" does not have a column named "{1}".', + $table->name, $columnName); + $column=$table->columns[$columnName]; + + foreach($values as &$value) + { + $value=$column->typecast($value); + if(is_string($value)) + $value=$db->quoteValue($value); + } + if($n===1) + return $prefix.$column->rawName.($values[0]===null?' IS NULL':'='.$values[0]); + else + return $prefix.$column->rawName.' IN ('.implode(', ',$values).')'; + } + else if(is_array($columnName)) // composite key: $values=array(array('pk1'=>'v1','pk2'=>'v2'),array(...)) + { + foreach($columnName as $name) + { + if(!isset($table->columns[$name])) + throw new TDbException('Table "{0}" does not have a column named "{1}".', + $table->name, $name); + + for($i=0;$i<$n;++$i) + { + if(isset($values[$i][$name])) + { + $value=$table->columns[$name]->typecast($values[$i][$name]); + if(is_string($value)) + $values[$i][$name]=$db->quoteValue($value); + else + $values[$i][$name]=$value; + } + else + throw new TDbException('The value for the column "{1}" is not supplied when querying the table "{0}".', + $table->name,$name); + } + } + if(count($values)===1) + { + $entries=array(); + foreach($values[0] as $name=>$value) + $entries[]=$prefix.$table->columns[$name]->rawName.($value===null?' IS NULL':'='.$value); + return implode(' AND ',$entries); + } + + return $this->createCompositeInCondition($table,$values,$prefix); + } + else + throw new TDbException('Column name must be either a string or an array.'); + } + + /** + * Generates the expression for selecting rows with specified composite key values. + * @param TDbTableSchema the table schema + * @param array list of primary key values to be selected within + * @param string column prefix (ended with dot) + * @return string the expression for selection + * @since 1.0.4 + */ + protected function createCompositeInCondition($table,$values,$prefix) + { + $keyNames=array(); + foreach(array_keys($values[0]) as $name) + $keyNames[]=$prefix.$table->columns[$name]->rawName; + $vs=array(); + foreach($values as $value) + $vs[]='('.implode(', ',$value).')'; + return '('.implode(', ',$keyNames).') IN ('.implode(', ',$vs).')'; + } + + /** + * Checks if the parameter is a valid table schema. + * If it is a string, the corresponding table schema will be retrieved. + * @param mixed table schema ({@link TDbTableSchema}) or table name (string). + * If this refers to a valid table name, this parameter will be returned with the corresponding table schema. + * @throws TDbException if the table name is not valid + * @since 1.0.4 + */ + protected function ensureTable(&$table) + { + if(is_string($table) && ($table=$this->_schema->getTable($tableName=$table))===null) + throw new TDbException('Table "{0}" does not exist.', + $tableName); + } +} diff --git a/framework/Db/Schema/TDbCriteria.php b/framework/Db/Schema/TDbCriteria.php new file mode 100755 index 00000000..3dab036a --- /dev/null +++ b/framework/Db/Schema/TDbCriteria.php @@ -0,0 +1,166 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * TDbCriteria represents a query criteria, such as conditions, ordering by, limit/offset. + * + * @author Qiang Xue + * @version $Id: TDbCriteria.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package system.db.schema + * @since 1.0 + */ +class TDbCriteria +{ + /** + * @var mixed the columns being selected. This refers to the SELECT clause in an SQL + * statement. The property can be either a string (column names separated by commas) + * or an array of column names. Defaults to '*', meaning all columns. + */ + public $select='*'; + /** + * @var string query condition. This refers to the WHERE clause in an SQL statement. + * For example, age>31 AND team=1. + */ + public $condition=''; + /** + * @var array list of query parameter values indexed by parameter placeholders. + * For example, array(':name'=>'Dan', ':age'=>31). + */ + public $params=array(); + /** + * @var integer maximum number of records to be returned. If less than 0, it means no limit. + */ + public $limit=-1; + /** + * @var integer zero-based offset from where the records are to be returned. If less than 0, it means starting from the beginning. + */ + public $offset=-1; + /** + * @var string how to sort the query results. This refers to the ORDER BY clause in an SQL statement. + */ + public $order=''; + /** + * @var string how to group the query results. This refers to the GROUP BY clause in an SQL statement. + * For example, 'projectID, teamID'. + */ + public $group=''; + /** + * @var string how to join with other tables. This refers to the JOIN clause in an SQL statement. + * For example, 'LEFT JOIN users ON users.id=authorID'. + */ + public $join=''; + /** + * @var string the condition to be applied with GROUP-BY clause. + * For example, 'SUM(revenue)<50000'. + * @since 1.0.1 + */ + public $having=''; + + /** + * Constructor. + * @param array criteria initial property values (indexed by property name) + */ + public function __construct($data=array()) + { + foreach($data as $name=>$value) + $this->$name=$value; + } + + /** + * Merges with another criteria. + * In general, the merging makes the resulting criteria more restrictive. + * For example, if both criterias have conditions, they will be 'AND' together. + * Also, the criteria passed as the parameter takes precedence in case + * two options cannot be merged (e.g. LIMIT, OFFSET). + * @param TDbCriteria the criteria to be merged with. + * @param boolean whether to use 'AND' to merge condition and having options. + * If false, 'OR' will be used instead. Defaults to 'AND'. This parameter has been + * available since version 1.0.6. + * @since 1.0.5 + */ + public function mergeWith($criteria,$useAnd=true) + { + $and=$useAnd ? 'AND' : 'OR'; + if(is_array($criteria)) + $criteria=new self($criteria); + if($this->select!==$criteria->select) + { + if($this->select==='*') + $this->select=$criteria->select; + else if($criteria->select!=='*') + { + $select1=is_string($this->select)?preg_split('/\s*,\s*/',trim($this->select),-1,PREG_SPLIT_NO_EMPTY):$this->select; + $select2=is_string($criteria->select)?preg_split('/\s*,\s*/',trim($criteria->select),-1,PREG_SPLIT_NO_EMPTY):$criteria->select; + $this->select=array_merge($select1,array_diff($select2,$select1)); + } + } + + if($this->condition!==$criteria->condition) + { + if($this->condition==='') + $this->condition=$criteria->condition; + else if($criteria->condition!=='') + $this->condition="({$this->condition}) $and ({$criteria->condition})"; + } + + if($this->params!==$criteria->params) + $this->params=array_merge($this->params,$criteria->params); + + if($criteria->limit>0) + $this->limit=$criteria->limit; + + if($criteria->offset>=0) + $this->offset=$criteria->offset; + + if($this->order!==$criteria->order) + { + if($this->order==='') + $this->order=$criteria->order; + else if($criteria->order!=='') + $this->order.=', '.$criteria->order; + } + + if($this->group!==$criteria->group) + { + if($this->group==='') + $this->group=$criteria->group; + else if($criteria->group!=='') + $this->group.=', '.$criteria->group; + } + + if($this->join!==$criteria->join) + { + if($this->join==='') + $this->join=$criteria->join; + else if($criteria->join!=='') + $this->join.=' '.$criteria->join; + } + + if($this->having!==$criteria->having) + { + if($this->having==='') + $this->having=$criteria->having; + else if($criteria->having!=='') + $this->having="({$this->having}) $and ({$criteria->having})"; + } + } + + /** + * @return array the array representation of the criteria + * @since 1.0.6 + */ + public function toArray() + { + $result=array(); + foreach(array('select', 'condition', 'params', 'limit', 'offset', 'order', 'group', 'join', 'having') as $name) + $result[$name]=$this->$name; + return $result; + } +} diff --git a/framework/Db/Schema/TDbExpression.php b/framework/Db/Schema/TDbExpression.php new file mode 100755 index 00000000..86b9a1ef --- /dev/null +++ b/framework/Db/Schema/TDbExpression.php @@ -0,0 +1,49 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * TDbExpression represents a DB expression that does not need escaping. + * TDbExpression is mainly used in {@link CActiveRecord} as attribute values. + * When inserting or updating a {@link CActiveRecord}, attribute values of + * type TDbExpression will be directly put into the corresponding SQL statement + * without escaping. A typical usage is that an attribute is set with 'NOW()' + * expression so that saving the record would fill the corresponding column + * with the current DB server timestamp. + * + * @author Qiang Xue + * @version $Id: TDbExpression.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package system.db.schema + * @since 1.0.2 + */ +class TDbExpression extends TComponent +{ + /** + * @var string the DB expression + */ + public $expression; + + /** + * Constructor. + * @param string the DB expression + */ + public function __construct($expression) + { + $this->expression=$expression; + } + + /** + * String magic method + * @return string the DB expression + */ + public function __toString() + { + return $this->expression; + } +} \ No newline at end of file diff --git a/framework/Db/Schema/TDbSchema.php b/framework/Db/Schema/TDbSchema.php new file mode 100755 index 00000000..6837b7e5 --- /dev/null +++ b/framework/Db/Schema/TDbSchema.php @@ -0,0 +1,193 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbCommandBuilder'); + +/** + * TDbSchema is the base class for retrieving metadata information. + * + * @author Qiang Xue + * @version $Id: TDbSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema + * @since 1.0 + */ +abstract class TDbSchema extends TComponent +{ + private $_tableNames=array(); + private $_tables=array(); + private $_connection; + private $_builder; + private $_cacheExclude=array(); + + /** + * Creates a table instance representing the metadata for the named table. + * @return TDbTableSchema driver dependent table metadata, null if the table does not exist. + */ + abstract protected function createTable($name); + + /** + * Constructor. + * @param TDbConnection database connection. + */ + public function __construct($conn) + { + $conn->setActive(true); + $this->_connection=$conn; + foreach($conn->schemaCachingExclude as $name) + $this->_cacheExclude[$name]=true; + } + + /** + * @return TDbConnection database connection. The connection is active. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * Obtains the metadata for the named table. + * @param string table name + * @return TDbTableSchema table metadata. Null if the named table does not exist. + */ + public function getTable($name) + { + if(isset($this->_tables[$name])) + return $this->_tables[$name]; + else if(!isset($this->_cacheExclude[$name]) && ($duration=$this->_connection->schemaCachingDuration)>0 && ($cache=prado::getApplication()->getCache())!==null) + { + $key='prado:dbschema'.$this->_connection->connectionString.':'.$this->_connection->username.':'.$name; + if(($table=$cache->get($key))===false) + { + $table=$this->createTable($name); + $cache->set($key,$table,$duration); + } + return $this->_tables[$name]=$table; + } + else + return $this->_tables[$name]=$this->createTable($name); + } + + /** + * Returns the metadata for all tables in the database. + * @param string the schema of the tables. Defaults to empty string, meaning the current or default schema. + * @return array the metadata for all tables in the database. + * Each array element is an instance of {@link TDbTableSchema} (or its child class). + * The array keys are table names. + * @since 1.0.2 + */ + public function getTables($schema='') + { + $tables=array(); + foreach($this->getTableNames($schema) as $name) + $tables[$name]=$this->getTable($name); + return $tables; + } + + /** + * Returns all table names in the database. + * @param string the schema of the tables. Defaults to empty string, meaning the current or default schema. + * If not empty, the returned table names will be prefixed with the schema name. + * @return array all table names in the database. + * @since 1.0.2 + */ + public function getTableNames($schema='') + { + if(!isset($this->_tableNames[$schema])) + $this->_tableNames[$schema]=$this->findTableNames($schema); + return $this->_tableNames[$schema]; + } + + /** + * @return TDbCommandBuilder the SQL command builder for this connection. + */ + public function getCommandBuilder() + { + if($this->_builder!==null) + return $this->_builder; + else + return $this->_builder=$this->createCommandBuilder(); + } + + /** + * Refreshes the schema. + * This method resets the loaded table metadata and command builder + * so that they can be recreated to reflect the change of schema. + */ + public function refresh() + { + $this->_tables=array(); + $this->_builder=null; + } + + /** + * Quotes a table name for use in a query. + * @param string table name + * @return string the properly quoted table name + */ + public function quoteTableName($name) + { + return "'".$name."'"; + } + + /** + * Quotes a column name for use in a query. + * @param string column name + * @return string the properly quoted column name + */ + public function quoteColumnName($name) + { + return '"'.$name.'"'; + } + + /** + * Compares two table names. + * The table names can be either quoted or unquoted. This method + * will consider both cases. + * @param string table name 1 + * @param string table name 2 + * @return boolean whether the two table names refer to the same table. + */ + public function compareTableNames($name1,$name2) + { + $name1=str_replace(array('"','`',"'"),'',$name1); + $name2=str_replace(array('"','`',"'"),'',$name2); + if(($pos=strrpos($name1,'.'))!==false) + $name1=substr($name1,$pos+1); + if(($pos=strrpos($name2,'.'))!==false) + $name2=substr($name2,$pos+1); + return $name1===$name2; + } + + /** + * Creates a command builder for the database. + * This method may be overridden by child classes to create a DBMS-specific command builder. + * @return TDbCommandBuilder command builder instance + */ + protected function createCommandBuilder() + { + return new TDbCommandBuilder($this); + } + + /** + * Returns all table names in the database. + * This method should be overridden by child classes in order to support this feature + * because the default implemenation simply throws an exception. + * @param string the schema of the tables. Defaults to empty string, meaning the current or default schema. + * If not empty, the returned table names will be prefixed with the schema name. + * @return array all table names in the database. + * @since 1.0.2 + */ + protected function findTableNames($schema='') + { + throw new TDbException('{0} does not support fetching all table names.', + get_class($this)); + } +} diff --git a/framework/Db/Schema/TDbTableSchema.php b/framework/Db/Schema/TDbTableSchema.php new file mode 100755 index 00000000..eaeda1b4 --- /dev/null +++ b/framework/Db/Schema/TDbTableSchema.php @@ -0,0 +1,76 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * TDbTableSchema is the base class for representing the metadata of a database table. + * + * It may be extended by different DBMS driver to provide DBMS-specific table metadata. + * + * TDbTableSchema provides the following information about a table: + *
    + *
  • {@link name}
    • + *
  • {@link rawName}
    • + *
  • {@link columns}
    • + *
  • {@link primaryKey}
    • + *
  • {@link foreignKeys}
    • + *
  • {@link sequenceName}
    • + *
+ * + * @author Qiang Xue + * @version $Id: TDbTableSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package system.db.schema + * @since 1.0 + */ +class TDbTableSchema extends TComponent +{ + /** + * @var string name of this table. + */ + public $name; + /** + * @var string raw name of this table. This is the quoted version of table name with optional schema name. It can be directly used in SQLs. + */ + public $rawName; + /** + * @var string|array primary key name of this table. If composite key, an array of key names is returned. + */ + public $primaryKey; + /** + * @var string sequence name for the primary key. Null if no sequence. + */ + public $sequenceName; + /** + * @var array foreign keys of this table. The array is indexed by column name. Each value is an array of foreign table name and foreign column name. + */ + public $foreignKeys=array(); + /** + * @var array column metadata of this table. Each array element is a TDbColumnSchema object, indexed by column names. + */ + public $columns=array(); + + /** + * Gets the named column metadata. + * This is a convenient method for retrieving a named column even if it does not exist. + * @param string column name + * @return TDbColumnSchema metadata of the named column. Null if the named column does not exist. + */ + public function getColumn($name) + { + return isset($this->columns[$name]) ? $this->columns[$name] : null; + } + + /** + * @return array list of column names + */ + public function getColumnNames() + { + return array_keys($this->columns); + } +} diff --git a/framework/Db/Schema/mssql/TMssqlColumnSchema.php b/framework/Db/Schema/mssql/TMssqlColumnSchema.php new file mode 100755 index 00000000..0c5c6ac4 --- /dev/null +++ b/framework/Db/Schema/mssql/TMssqlColumnSchema.php @@ -0,0 +1,57 @@ + + * @author Christophe Boulain + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbColumnSchema'); + +/** + * TMssqlColumnSchema class describes the column meta data of a MSSQL table. + * + * @author Qiang Xue + * @author Christophe Boulain + * @version $Id: TMssqlColumnSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.mssql + * @since 1.0.4 + */ +class TMssqlColumnSchema extends TDbColumnSchema +{ + /** + * Extracts the PHP type from DB type. + * @param string DB type + */ + protected function extractType($dbType) + { + if(strpos($dbType,'bigint')!==false || strpos($dbType,'float')!==false || strpos($dbType,'real')!==false) + $this->type='double'; + else if(strpos($dbType,'int')!==false || strpos($dbType,'smallint')!==false || strpos($dbType,'tinyint')) + $this->type='integer'; + else if(strpos($dbType,'bit')!==false) + $this->type='boolean'; + else + $this->type='string'; + } + + protected function extractDefault($defaultValue) + { + if($this->dbType==='timestamp' ) + $this->defaultValue=null; + else + parent::extractDefault(str_replace(array('(',')',"'"), '', $defaultValue)); + } + + /** + * Extracts size, precision and scale information from column's DB type. + * We do nothing here, since sizes and precisions have been computed before. + * @param string the column's DB type + */ + protected function extractLimit($dbType) + { + } +} diff --git a/framework/Db/Schema/mssql/TMssqlCommandBuilder.php b/framework/Db/Schema/mssql/TMssqlCommandBuilder.php new file mode 100755 index 00000000..f0b5c7f4 --- /dev/null +++ b/framework/Db/Schema/mssql/TMssqlCommandBuilder.php @@ -0,0 +1,303 @@ + + * @author Christophe Boulain + * @author Wei Zhuo + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using ('System.Db.schame.TDbCommandBuilder'); + +/** + * TMssqlCommandBuilder provides basic methods to create query commands for tables for Mssql Servers. + * + * @author Qiang Xue + * @author Christophe Boulain + * @author Wei Zhuo + * @version $Id: TMssqlCommandBuilder.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package system.db.schema.mssql + * @since 1.0.4 + */ +class TMssqlCommandBuilder extends TDbCommandBuilder +{ + /** + * Returns the last insertion ID for the specified table. + * Override parent implemantation since PDO mssql driver does not provide this method + * @param TDbTableSchema the table metadata + * @return mixed last insertion id. Null is returned if no sequence name. + */ + public function getLastInsertID($table) + { + if($table->sequenceName!==null) + return $this->getDbConnection()->createCommand('SELECT SCOPE_IDENTITY()')->queryScalar(); + else + return null; + } + + /** + * Creates a COUNT(*) command for a single table. + * Override parent implementation to remove the order clause of criteria if it exists + * @param TDbTableSchema the table metadata + * @param TDbCriteria the query criteria + * @return TDbCommand query command. + */ + public function createCountCommand($table,$criteria) + { + $criteria->order=''; + return parent::createCountCommand($table, $criteria); + } + + /** + * Creates a SELECT command for a single table. + * Override parent implementation to check if an orderby clause if specified when querying with an offset + * @param TDbTableSchema the table metadata + * @param TDbCriteria the query criteria + * @return TDbCommand query command. + */ + public function createFindCommand($table,$criteria) + { + $criteria=$this->checkCriteria($table,$criteria); + return parent::createFindCommand($table,$criteria); + + } + + /** + * Creates an UPDATE command. + * Override parent implementation because mssql don't want to update an identity column + * @param TDbTableSchema the table metadata + * @param array list of columns to be updated (name=>value) + * @param TDbCriteria the query criteria + * @return TDbCommand update command. + */ + public function createUpdateCommand($table,$data,$criteria) + { + $criteria=$this->checkCriteria($table,$criteria); + $fields=array(); + $values=array(); + $bindByPosition=isset($criteria->params[0]); + foreach($data as $name=>$value) + { + if(($column=$table->getColumn($name))!==null) + { + if ($table->sequenceName !== null && $column->isPrimaryKey === true) continue; + if($value instanceof TDbExpression) + $fields[]=$column->rawName.'='.(string)$value; + else if($bindByPosition) + { + $fields[]=$column->rawName.'=?'; + $values[]=$column->typecast($value); + } + else + { + $fields[]=$column->rawName.'=:'.$name; + $values[':'.$name]=$column->typecast($value); + } + } + } + if($fields===array()) + throw new TDbException('No columns are being updated for table "{0}".', + $table->name); + $sql="UPDATE {$table->rawName} SET ".implode(', ',$fields); + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + $sql=$this->applyOrder($sql,$criteria->order); + $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset); + + $command=$this->getDbConnection()->createCommand($sql); + $this->bindValues($command,array_merge($values,$criteria->params)); + + return $command; + } + + /** + * Creates a DELETE command. + * Override parent implementation to check if an orderby clause if specified when querying with an offset + * @param TDbTableSchema the table metadata + * @param TDbCriteria the query criteria + * @return TDbCommand delete command. + */ + public function createDeleteCommand($table,$criteria) + { + $criteria=$this->checkCriteria($table, $criteria); + return parent::createDeleteCommand($table, $criteria); + } + + /** + * Creates an UPDATE command that increments/decrements certain columns. + * Override parent implementation to check if an orderby clause if specified when querying with an offset + * @param TDbTableSchema the table metadata + * @param TDbCriteria the query criteria + * @param array counters to be updated (counter increments/decrements indexed by column names.) + * @return TDbCommand the created command + * @throws CException if no counter is specified + */ + public function createUpdateCounterCommand($table,$counters,$criteria) + { + $criteria=$this->checkCriteria($table, $criteria); + return parent::createUpdateCounterCommand($table, $counters, $criteria); + } + + /** + * This is a port from Prado Framework. + * + * Overrides parent implementation. Alters the sql to apply $limit and $offset. + * The idea for limit with offset is done by modifying the sql on the fly + * with numerous assumptions on the structure of the sql string. + * The modification is done with reference to the notes from + * http://troels.arvin.dk/db/rdbms/#select-limit-offset + * + * + * SELECT * FROM ( + * SELECT TOP n * FROM ( + * SELECT TOP z columns -- (z=n+skip) + * FROM tablename + * ORDER BY key ASC + * ) AS FOO ORDER BY key DESC -- ('FOO' may be anything) + * ) AS BAR ORDER BY key ASC -- ('BAR' may be anything) + * + * + * Regular expressions are used to alter the SQL query. The resulting SQL query + * may be malformed for complex queries. The following restrictions apply + * + *
    + *
  • + * In particular, commas should NOT + * be used as part of the ordering expression or identifier. Commas must only be + * used for separating the ordering clauses. + *
    • + *
  • + * In the ORDER BY clause, the column name should NOT be be qualified + * with a table name or view name. Alias the column names or use column index. + *
    • + *
  • + * No clauses should follow the ORDER BY clause, e.g. no COMPUTE or FOR clauses. + *
    • + * + * @param string SQL query string. + * @param integer maximum number of rows, -1 to ignore limit. + * @param integer row offset, -1 to ignore offset. + * @return string SQL with limit and offset. + * + * @author Wei Zhuo + */ + public function applyLimit($sql, $limit, $offset) + { + $limit = $limit!==null ? intval($limit) : -1; + $offset = $offset!==null ? intval($offset) : -1; + if ($limit > 0 && $offset <= 0) //just limit + $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql); + else if($limit > 0 && $offset > 0) + $sql = $this->rewriteLimitOffsetSql($sql, $limit,$offset); + return $sql; + } + + /** + * Rewrite sql to apply $limit > and $offset > 0 for MSSQL database. + * See http://troels.arvin.dk/db/rdbms/#select-limit-offset + * @param string sql query + * @param integer $limit > 0 + * @param integer $offset > 0 + * @return sql modified sql query applied with limit and offset. + * + * @author Wei Zhuo + */ + protected function rewriteLimitOffsetSql($sql, $limit, $offset) + { + $fetch = $limit+$offset; + $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $fetch", $sql); + $ordering = $this->findOrdering($sql); + + $orginalOrdering = $this->joinOrdering($ordering); + $reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering)); + $sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner top table__] {$reverseOrdering}) as [__outer top table__] {$orginalOrdering}"; + return $sql; + } + + /** + * Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx + * + * @param string $sql + * @return array ordering expression as key and ordering direction as value + * + * @author Wei Zhuo + */ + protected function findOrdering($sql) + { + if(!preg_match('/ORDER BY/i', $sql)) + return array(); + $matches=array(); + $ordering=array(); + preg_match_all('/(ORDER BY)[\s"\[](.*)(ASC|DESC)?(?:[\s"\[]|$|COMPUTE|FOR)/i', $sql, $matches); + if(count($matches)>1 && count($matches[2]) > 0) + { + $parts = explode(',', $matches[2][0]); + foreach($parts as $part) + { + $subs=array(); + if(preg_match_all('/(.*)[\s"\]](ASC|DESC)$/i', trim($part), $subs)) + { + if(count($subs) > 1 && count($subs[2]) > 0) + { + $ordering[$subs[1][0]] = $subs[2][0]; + } + //else what? + } + else + $ordering[trim($part)] = 'ASC'; + } + } + return $ordering; + } + + /** + * @param array ordering obtained from findOrdering() + * @return string concat the orderings + * + * @author Wei Zhuo + */ + protected function joinOrdering($orders) + { + if(count($orders)>0) + { + $str=array(); + foreach($orders as $column => $direction) + $str[] = $column.' '.$direction; + return 'ORDER BY '.implode(', ', $str); + } + } + + /** + * @param array original ordering + * @return array ordering with reversed direction. + * + * @author Wei Zhuo + */ + protected function reverseDirection($orders) + { + foreach($orders as $column => $direction) + $orders[$column] = strtolower(trim($direction))==='desc' ? 'ASC' : 'DESC'; + return $orders; + } + + + /** + * Checks if the criteria has an order by clause when using offset/limit. + * Override parent implementation to check if an orderby clause if specified when querying with an offset + * If not, order it by pk. + * @param TMssqlTableSchema table schema + * @param TDbCriteria criteria + * @return TDbCrireria the modified criteria + */ + protected function checkCriteria($table, $criteria) + { + if ($criteria->offset > 0 && $criteria->order==='') + { + $criteria->order=is_array($table->primaryKey)?implode(',',$table->primaryKey):$table->primaryKey; + } + return $criteria; + } +} diff --git a/framework/Db/Schema/mssql/TMssqlPdoAdapter.php b/framework/Db/Schema/mssql/TMssqlPdoAdapter.php new file mode 100755 index 00000000..c9d4597f --- /dev/null +++ b/framework/Db/Schema/mssql/TMssqlPdoAdapter.php @@ -0,0 +1,74 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * This is an extension of default PDO class for mssql driver only + * It provides some missing functionalities of pdo driver + * @author Christophe Boulain + * @version $Id: TMssqlPdoAdapter.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package system.db.schema.mssql + * @since 1.0.4 + */ +class TMssqlPdoAdapter extends PDO +{ + /** + * Get the last inserted id value + * MSSQL doesn't support sequence, so, argument is ignored + * + * @param string sequence name. Defaults to null + * @return int last inserted id + */ + public function lastInsertId ($sequence=NULL) + { + return $this->query('SELECT SCOPE_IDENTITY()')->fetchColumn(); + } + + /** + * Begin a transaction + * + * Is is necessary to override pdo's method, as mssql pdo drivers + * does not support transaction + * + * @return boolean + */ + public function beginTransaction () + { + $this->exec('BEGIN TRANSACTION'); + return true; + } + + /** + * Commit a transaction + * + * Is is necessary to override pdo's method, as mssql pdo drivers + * does not support transaction + * + * @return boolean + */ + public function commit () + { + $this->exec('COMMIT TRANSACTION'); + return true; + } + + /** + * Rollback a transaction + * + * Is is necessary to override pdo's method, ac mssql pdo drivers + * does not support transaction + * + * @return boolean + */ + public function rollBack () + { + $this->exec('ROLLBACK TRANSACTION'); + return true; + } +} diff --git a/framework/Db/Schema/mssql/TMssqlSchema.php b/framework/Db/Schema/mssql/TMssqlSchema.php new file mode 100755 index 00000000..063e8994 --- /dev/null +++ b/framework/Db/Schema/mssql/TMssqlSchema.php @@ -0,0 +1,312 @@ + + * @author Christophe Boulain + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbSchema'); + +/** + * TMssqlSchema is the class for retrieving metadata information from a MS SQL Server database. + * + * @author Qiang Xue + * @author Christophe Boulain + * @version $Id: TMssqlSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.mssql + * @since 1.0.4 + */ +class TMssqlSchema extends TDbSchema +{ + const DEFAULT_SCHEMA='dbo'; + + + /** + * Quotes a table name for use in a query. + * @param string table name + * @return string the properly quoted table name + */ + public function quoteTableName($name) + { + if (strpos($name,'.')===false) + return '['.$name.']'; + $names=explode('.',$name); + foreach ($names as &$n) + $n = '['.$n.']'; + return implode('.',$names); + } + + /** + * Quotes a column name for use in a query. + * @param string column name + * @return string the properly quoted column name + */ + public function quoteColumnName($name) + { + return '['.$name.']'; + } + + /** + * Compares two table names. + * The table names can be either quoted or unquoted. This method + * will consider both cases. + * @param string table name 1 + * @param string table name 2 + * @return boolean whether the two table names refer to the same table. + */ + public function compareTableNames($name1,$name2) + { + $name1=str_replace(array('[',']'),'',$name1); + $name1=str_replace(array('[',']'),'',$name2); + return parent::compareTableNames(strtolower($name1),strtolower($name2)); + } + + /** + * Creates a table instance representing the metadata for the named table. + * @return CMysqlTableSchema driver dependent table metadata. Null if the table does not exist. + */ + protected function createTable($name) + { + $table=new TMssqlTableSchema; + $this->resolveTableNames($table,$name); + //if (!in_array($table->name, $this->tableNames)) return null; + $table->primaryKey=$this->findPrimaryKey($table); + $table->foreignKeys=$this->findForeignKeys($table); + if($this->findColumns($table)) + { + return $table; + } + else + return null; + } + + /** + * Generates various kinds of table names. + * @param CMysqlTableSchema the table instance + * @param string the unquoted table name + */ + protected function resolveTableNames($table,$name) + { + $parts=explode('.',str_replace(array('[',']'),'',$name)); + if(($c=count($parts))==3) + { + // Catalog name, schema name and table name provided + $table->catalogName=$parts[0]; + $table->schemaName=$parts[1]; + $table->name=$parts[2]; + $table->rawName=$this->quoteTableName($table->catalogName).'.'.$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name); + } + elseif ($c==2) + { + // Only schema name and table name provided + $table->name=$parts[1]; + $table->schemaName=$parts[0]; + $table->rawName=$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name); + } + else + { + // Only the name given, we need to get at least the schema name + //if (empty($this->_schemaNames)) $this->findTableNames(); + $table->name=$parts[0]; + $table->schemaName=self::DEFAULT_SCHEMA; + $table->rawName=$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name); + } + } + + /** + * Gets the primary key column(s) details for the given table. + * @param TMssqlTableSchema table + * @return mixed primary keys (null if no pk, string if only 1 column pk, or array if composite pk) + */ + protected function findPrimaryKey($table) + { + $kcu='INFORMATION_SCHEMA.KEY_COLUMN_USAGE'; + $tc='INFORMATION_SCHEMA.TABLE_CONSTRAINTS'; + if (isset($table->catalogName)) + { + $kcu=$table->catalogName.'.'.$kcu; + $tc=$table->catalogName.'.'.$tc; + } + + $sql = <<quoteTableName($kcu)} k + LEFT JOIN {$this->quoteTableName($tc)} c + ON k.table_name = c.table_name + AND k.constraint_name = c.constraint_name + WHERE c.constraint_type ='PRIMARY KEY' + AND k.table_name = :table + AND k.table_schema = :schema +EOD; + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $table->name); + $command->bindValue(':schema', $table->schemaName); + $primary=$command->queryColumn(); + switch (count($primary)) + { + case 0: // No primary key on table + $primary=null; + break; + case 1: // Only 1 primary key + $primary=$primary[0]; + break; + } + return $primary; + } + + /** + * Gets foreign relationship constraint keys and table name + * @param TMssqlTableSchema table + * @return array foreign relationship table name and keys. + */ + protected function findForeignKeys($table) + { + $rc='INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS'; + $kcu='INFORMATION_SCHEMA.KEY_COLUMN_USAGE'; + if (isset($table->catalogName)) + { + $kcu=$table->catalogName.'.'.$kcu; + $rc=$table->catalogName.'.'.$rc; + } + + //From http://msdn2.microsoft.com/en-us/library/aa175805(SQL.80).aspx + $sql = <<quoteTableName($rc)} RC + JOIN {$this->quoteTableName($kcu)} KCU1 + ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG + AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA + AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME + JOIN {$this->quoteTableName($kcu)} KCU2 + ON KCU2.CONSTRAINT_CATALOG = + RC.UNIQUE_CONSTRAINT_CATALOG + AND KCU2.CONSTRAINT_SCHEMA = + RC.UNIQUE_CONSTRAINT_SCHEMA + AND KCU2.CONSTRAINT_NAME = + RC.UNIQUE_CONSTRAINT_NAME + AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION + WHERE KCU1.TABLE_NAME = :table +EOD; + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $table->name); + $fkeys=array(); + foreach($command->queryAll() as $info) + { + $fkeys[$info['FK_COLUMN_NAME']]=array($info['UQ_TABLE_NAME'],$info['UQ_COLUMN_NAME'],); + + } + return $fkeys; + } + + + /** + * Collects the table column metadata. + * @param CMysqlTableSchema the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + $where=array(); + $where[]="TABLE_NAME='".$table->name."'"; + if (isset($table->catalogName)) + $where[]="TABLE_CATALOG='".$table->catalogName."'"; + if (isset($table->schemaName)) + $where[]="TABLE_SCHEMA='".$table->schemaName."'"; + $sql="SELECT *, columnproperty(object_id(table_schema+'.'+table_name), column_name, 'IsIdentity') as IsIdentity ". + "FROM INFORMATION_SCHEMA.COLUMNS WHERE ".join(' AND ',$where); + if (($columns=$this->getDbConnection()->createCommand($sql)->queryAll())===array()) + return false; + + foreach($columns as $column) + { + $c=$this->createColumn($column); + if (is_array($table->primaryKey)) + $c->isPrimaryKey=in_array($c->name, $table->primaryKey); + else + $c->isPrimaryKey=strcasecmp($c->name,$table->primaryKey)===0; + + $c->isForeignKey=isset($table->foreignKeys[$c->name]); + $table->columns[$c->name]=$c; + if ($column['IsIdentity']==1 && $table->sequenceName===null) + $table->sequenceName=''; + + } + return true; + } + + /** + * Creates a table column. + * @param array column metadata + * @return TDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new TMssqlColumnSchema; + $c->name=$column['COLUMN_NAME']; + $c->rawName=$this->quoteColumnName($c->name); + $c->allowNull=$column['IS_NULLABLE']=='YES'; + if ($column['NUMERIC_PRECISION_RADIX']!==null) + { + // We have a numeric datatype + $c->size=$c->precision=$column['NUMERIC_PRECISION']!==null?(int)$column['NUMERIC_PRECISION']:null; + $c->scale=$column['NUMERIC_SCALE']!==null?(int)$column['NUMERIC_SCALE']:null; + } + elseif ($column['DATA_TYPE']=='image' || $column['DATA_TYPE']=='text') + $c->size=$c->precision=null; + else + $c->size=$c->precision=($column['CHARACTER_MAXIMUM_LENGTH']!== null)?(int)$column['CHARACTER_MAXIMUM_LENGTH']:null; + + $c->init($column['DATA_TYPE'],$column['COLUMN_DEFAULT']); + return $c; + } + + /** + * Returns all table names in the database. + * @return array all table names in the database. + * @since 1.0.4 + */ + protected function findTableNames($schema='') + { + if($schema==='') + $schema=self::DEFAULT_SCHEMA; + $sql=<<getDbConnection()->createCommand($sql); + $command->bindParam(":schema", $schema); + $rows=$command->queryAll(); + $names=array(); + foreach ($rows as $row) + { + if ($schema == self::DEFAULT_SCHEMA) + $names[]=$row['TABLE_NAME']; + else + $names[]=$schema.'.'.$row['TABLE_SCHEMA'].'.'.$row['TABLE_NAME']; + } + + return $names; + } + + /** + * Creates a command builder for the database. + * This method overrides parent implementation in order to create a MSSQL specific command builder + * @return TDbCommandBuilder command builder instance + */ + protected function createCommandBuilder() + { + return new TMssqlCommandBuilder($this); + } +} diff --git a/framework/Db/Schema/mssql/TMssqlTableSchema.php b/framework/Db/Schema/mssql/TMssqlTableSchema.php new file mode 100755 index 00000000..ed1b7c8a --- /dev/null +++ b/framework/Db/Schema/mssql/TMssqlTableSchema.php @@ -0,0 +1,35 @@ + + * @author Christophe Boulain + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.TDbTableSchema'); + +/** + * TMssqlTableSchema represents the metadata for a MSSQL table. + * + * @author Qiang Xue + * @author Christophe Boulain + * @version $Id: TMssqlTableSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package system.Db.schema.mssql + * @since 1.0.4 + */ +class TMssqlTableSchema extends TDbTableSchema +{ + /** + * @var string name of the catalog (database) that this table belongs to. + * Defaults to null, meaning no schema (or the current database). + */ + public $catalogName; + /** + * @var string name of the schema that this table belongs to. + * Defaults to null, meaning no schema (or the current database owner). + */ + public $schemaName; +} diff --git a/framework/Db/Schema/mysql/TMysqlColumnSchema.php b/framework/Db/Schema/mysql/TMysqlColumnSchema.php new file mode 100755 index 00000000..d3bb230c --- /dev/null +++ b/framework/Db/Schema/mysql/TMysqlColumnSchema.php @@ -0,0 +1,46 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbColumnSchema'); + +/** + * TMysqlColumnSchema class describes the column meta data of a MySQL table. + * + * @author Qiang Xue + * @version $Id: TMysqlColumnSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.mysql + * @since 1.0 + */ +class TMysqlColumnSchema extends TDbColumnSchema +{ + /** + * Extracts the PHP type from DB type. + * @param string DB type + */ + protected function extractType($dbType) + { + if(strpos($dbType,'bigint')!==false || strpos($dbType,'float')!==false || strpos($dbType,'double')!==false) + $this->type='double'; + else if(strpos($dbType,'bool')!==false || $dbType==='tinyint(1)') + $this->type='boolean'; + else if(strpos($dbType,'int')!==false || strpos($dbType,'bit')!==false) + $this->type='integer'; + else + $this->type='string'; + } + + protected function extractDefault($defaultValue) + { + if($this->dbType==='timestamp' && $defaultValue==='CURRENT_TIMESTAMP') + $this->defaultValue=null; + else + parent::extractDefault($defaultValue); + } +} diff --git a/framework/Db/Schema/mysql/TMysqlSchema.php b/framework/Db/Schema/mysql/TMysqlSchema.php new file mode 100755 index 00000000..8061e7a8 --- /dev/null +++ b/framework/Db/Schema/mysql/TMysqlSchema.php @@ -0,0 +1,205 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbSchema'); +prado::using('System.Db.Schema.mysql.TMysqlTableSchema'); +prado::using('System.Db.Schema.mysql.TMysqlColumnSchema'); + +/** + * TMysqlSchema is the class for retrieving metadata information from a MySQL database (version 4.1.x and 5.x). + * + * @author Qiang Xue + * @version $Id: TMysqlSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.mysql + * @since 1.0 + */ +class TMysqlSchema extends TDbSchema +{ + private $_tableNames; + private $_schemaNames; + + /** + * Quotes a table name for use in a query. + * @param string table name + * @return string the properly quoted table name + */ + public function quoteTableName($name) + { + return '`'.$name.'`'; + } + + /** + * Quotes a column name for use in a query. + * @param string column name + * @return string the properly quoted column name + */ + public function quoteColumnName($name) + { + return '`'.$name.'`'; + } + + /** + * Compares two table names. + * The table names can be either quoted or unquoted. This method + * will consider both cases. + * @param string table name 1 + * @param string table name 2 + * @return boolean whether the two table names refer to the same table. + */ + public function compareTableNames($name1,$name2) + { + return parent::compareTableNames(strtolower($name1),strtolower($name2)); + } + + /** + * Creates a table instance representing the metadata for the named table. + * @return TMysqlTableSchema driver dependent table metadata. Null if the table does not exist. + */ + protected function createTable($name) + { + $table=new TMysqlTableSchema; + $this->resolveTableNames($table,$name); + + if($this->findColumns($table)) + { + $this->findConstraints($table); + return $table; + } + else + return null; + } + + /** + * Generates various kinds of table names. + * @param TMysqlTableSchema the table instance + * @param string the unquoted table name + */ + protected function resolveTableNames($table,$name) + { + $parts=explode('.',str_replace('`','',$name)); + if(isset($parts[1])) + { + $table->schemaName=$parts[0]; + $table->name=$parts[1]; + $table->rawName=$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name); + } + else + { + $table->name=$parts[0]; + $table->rawName=$this->quoteTableName($table->name); + } + } + + /** + * Collects the table column metadata. + * @param TMysqlTableSchema the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + $sql='SHOW COLUMNS FROM '.$table->rawName; + try + { + $columns=$this->getDbConnection()->createCommand($sql)->queryAll(); + } + catch(Exception $e) + { + return false; + } + foreach($columns as $column) + { + $c=$this->createColumn($column); + $table->columns[$c->name]=$c; + if($c->isPrimaryKey) + { + if($table->primaryKey===null) + $table->primaryKey=$c->name; + else if(is_string($table->primaryKey)) + $table->primaryKey=array($table->primaryKey,$c->name); + else + $table->primaryKey[]=$c->name; + if(strpos(strtolower($column['Extra']),'auto_increment')!==false) + $table->sequenceName=''; + } + } + return true; + } + + /** + * Creates a table column. + * @param array column metadata + * @return TDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new TMysqlColumnSchema; + $c->name=$column['Field']; + $c->rawName=$this->quoteColumnName($c->name); + $c->allowNull=$column['Null']==='YES'; + $c->isPrimaryKey=strpos($column['Key'],'PRI')!==false; + $c->isForeignKey=false; + $c->init($column['Type'],$column['Default']); + return $c; + } + + /** + * @return float server version. + */ + protected function getServerVersion() + { + $version=$this->getDbConnection()->getAttribute(PDO::ATTR_SERVER_VERSION); + $digits=array(); + preg_match('/(\d+)\.(\d+)\.(\d+)/', $version, $digits); + return floatval($digits[1].'.'.$digits[2].$digits[3]); + } + + /** + * Collects the foreign key column details for the given table. + * @param TMysqlTableSchema the table metadata + */ + protected function findConstraints($table) + { + $row=$this->getDbConnection()->createCommand('SHOW CREATE TABLE '.$table->rawName)->queryRow(); + $matches=array(); + $regexp='/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+([^\(^\s]+)\s*\(([^\)]+)\)/mi'; + foreach($row as $sql) + { + if(preg_match_all($regexp,$sql,$matches,PREG_SET_ORDER)) + break; + } + $foreign = array(); + foreach($matches as $match) + { + $keys=array_map('trim',explode(',',str_replace('`','',$match[1]))); + $fks=array_map('trim',explode(',',str_replace('`','',$match[3]))); + foreach($keys as $k=>$name) + { + $table->foreignKeys[$name]=array(str_replace('`','',$match[2]),$fks[$k]); + if(isset($table->columns[$name])) + $table->columns[$name]->isForeignKey=true; + } + } + } + + /** + * Returns all table names in the database. + * @return array all table names in the database. + * @since 1.0.2 + */ + protected function findTableNames($schema='') + { + if($schema==='') + return $this->getDbConnection()->createCommand('SHOW TABLES')->queryColumn(); + $names=$this->getDbConnection()->createCommand('SHOW TABLES FROM '.$this->quoteTableName($schema))->queryColumn(); + foreach($names as &$name) + $name=$schema.'.'.$name; + return $names; + } +} diff --git a/framework/Db/Schema/mysql/TMysqlTableSchema.php b/framework/Db/Schema/mysql/TMysqlTableSchema.php new file mode 100755 index 00000000..8bed3314 --- /dev/null +++ b/framework/Db/Schema/mysql/TMysqlTableSchema.php @@ -0,0 +1,26 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ +prado::using('System.Db.Schema.TDbTableSchema'); +/** + * TMysqlTableSchema represents the metadata for a MySQL table. + * + * @author Qiang Xue + * @version $Id: TMysqlTableSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.mysql + * @since 1.0 + */ +class TMysqlTableSchema extends TDbTableSchema +{ + /** + * @var string name of the schema (database) that this table belongs to. + * Defaults to null, meaning no schema (or the current database). + */ + public $schemaName; +} diff --git a/framework/Db/Schema/oci/TOciColumnSchema.php b/framework/Db/Schema/oci/TOciColumnSchema.php new file mode 100755 index 00000000..d609d14d --- /dev/null +++ b/framework/Db/Schema/oci/TOciColumnSchema.php @@ -0,0 +1,56 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbColumnSchema'); + +/** + * TOciColumnSchema class describes the column meta data of a Oracle table. + * + * @author Ricardo Grana + * @version $Id: TOciColumnSchema.php + * @package System.Db.Schema.oci + * @since 1.0.5 + */ +class TOciColumnSchema extends TDbColumnSchema +{ + /** + * Extracts the PHP type from DB type. + * @param string DB type + */ + protected function extractOraType($dbType){ + if(strpos($dbType,'FLOAT')!==false) return 'double'; + + if ((strpos($dbType,'NUMBER')!==false) or + (strpos($dbType,'INTEGER')!==false)) + { + if(strpos($dbType,'(') && preg_match('/\((.*)\)/',$dbType,$matches)) + { + $values=explode(',',$matches[1]); + if(isset($values[1]) and (((int)$values[1]) > 0)) + return 'double'; + else return 'integer'; + } + }else{ + return 'string'; + } + } + protected function extractType($dbType) + { + $this->type=$this->extractOraType($dbType); + } + + protected function extractDefault($defaultValue) + { + if(strpos($dbType,'timestamp')!==false) + $this->defaultValue=null; + else + parent::extractDefault($defaultValue); + } +} diff --git a/framework/Db/Schema/oci/TOciCommandBuilder.php b/framework/Db/Schema/oci/TOciCommandBuilder.php new file mode 100755 index 00000000..807b7271 --- /dev/null +++ b/framework/Db/Schema/oci/TOciCommandBuilder.php @@ -0,0 +1,122 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbCommandBuilder'); + +/** + * TOciCommandBuilder provides basic methods to create query commands for tables. + * + * @author Ricardo Grana + * @version $Id: TOciCommandBuilder.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.oci + * @since 1.0.5 + */ +class TOciCommandBuilder extends TDbCommandBuilder +{ + /** + * @var integer the last insertion ID + */ + public $returnID; + + /** + * Returns the last insertion ID for the specified table. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @return mixed last insertion id. Null is returned if no sequence name. + */ + public function getLastInsertID($table) + { + return $this->returnID; + } + + /** + * Alters the SQL to apply LIMIT and OFFSET. + * Default implementation is applicable for PostgreSQL, MySQL and SQLite. + * @param string SQL query string without LIMIT and OFFSET. + * @param integer maximum number of rows, -1 to ignore limit. + * @param integer row offset, -1 to ignore offset. + * @return string SQL with LIMIT and OFFSET + */ + public function applyLimit($sql,$limit,$offset) + { + if (($limit < 0) and ($offset < 0)) return $sql; + + $filters = array(); + if($offset>0){ + $filters[] = 'rowNumId >= '.(int)$offset; + } + + if($limit>=0){ + $filters[]= 'rownum <= '.(int)$limit; + } + + if (count($filters) > 0){ + $filter = implode(' and ', $filters); + $filter= " WHERE ".$filter; + }else{ + $filter = ''; + } + + + $sql = <<column value). If a key is not a valid column name, the corresponding value will be ignored. + * @return TDbCommand insert command + */ + public function createInsertCommand($table,$data) + { + $this->ensureTable($table); + $fields=array(); + $values=array(); + $placeholders=array(); + foreach($data as $name=>$value) + { + if(($column=$table->getColumn($name))!==null && ($value!==null || $column->allowNull)) + { + $fields[]=$column->rawName; + if($value instanceof TDbExpression) + $placeholders[]=(string)$value; + else + { + $placeholders[]=':'.$name; + $values[':'.$name]=$column->typecast($value); + } + } + } + + $sql="INSERT INTO {$table->rawName} (".implode(', ',$fields).') VALUES ('.implode(', ',$placeholders).')'; + + if(is_string($table->primaryKey)) + { + $sql.=" RETURNING ".$table->primaryKey." INTO :RETURN_ID"; + $command=$this->getDbConnection()->createCommand($sql); + $command->bindParam(':RETURN_ID', $this->returnID, PDO::PARAM_INT, 12); + $table->sequenceName='RETURN_ID'; + } + else + $command=$this->getDbConnection()->createCommand($sql); + + foreach($values as $name=>$value) + $command->bindValue($name,$value); + + return $command; + } +} diff --git a/framework/Db/Schema/oci/TOciSchema.php b/framework/Db/Schema/oci/TOciSchema.php new file mode 100755 index 00000000..50de0626 --- /dev/null +++ b/framework/Db/Schema/oci/TOciSchema.php @@ -0,0 +1,278 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbSchema'); + +/** + * TOciSchema is the class for retrieving metadata information from a PostgreSQL database. + * + * @author Ricardo Grana + * @version $Id: TOciSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.oci + * @since 1.0.5 + */ +class TOciSchema extends TDbSchema +{ + private $_defaultSchema = ''; + private $_sequences=array(); + + /** + * Quotes a table name for use in a query. + * @param string table name + * @return string the properly quoted table name + */ + public function quoteTableName($name) + { + return $name; + } + + /** + * Quotes a column name for use in a query. + * @param string column name + * @return string the properly quoted column name + */ + public function quoteColumnName($name) + { + return $name; + } + + /** + * Creates a command builder for the database. + * This method may be overridden by child classes to create a DBMS-specific command builder. + * @return TDbCommandBuilder command builder instance + */ + protected function createCommandBuilder() + { + return new TOciCommandBuilder($this); + } + + /** + * @param string default schema. + */ + public function setDefaultSchema($schema) + { + $this->_defaultSchema=$schema; + } + + /** + * @return string default schema. + */ + public function getDefaultSchema() + { + if (!strlen($this->_defaultSchema)) + { + $this->setDefaultSchema(strtoupper($this->getDbConnection()->username)); + } + + return $this->_defaultSchema; + } + + /** + * @param string table name with optional schema name prefix, uses default schema name prefix is not provided. + * @return array tuple as ($schemaName,$tableName) + */ + protected function getSchemaTableName($table) + { + $table = strtoupper($table); + if(count($parts= explode('.', str_replace('"','',$table))) > 1) + return array($parts[0], $parts[1]); + else + return array($this->getDefaultSchema(),$parts[0]); + } + + /** + * Creates a table instance representing the metadata for the named table. + * @return TDbTableSchema driver dependent table metadata. + */ + protected function createTable($name) + { + $table=new TOciTableSchema; + $this->resolveTableNames($table,$name); + + if(!$this->findColumns($table)) + return null; + $this->findConstraints($table); + + return $table; + } + + /** + * Generates various kinds of table names. + * @param TOciTableSchema the table instance + * @param string the unquoted table name + */ + protected function resolveTableNames($table,$name) + { + $parts=explode('.',str_replace('"','',$name)); + if(isset($parts[1])) + { + $schemaName=$parts[0]; + $tableName=$parts[1]; + } + else + { + $schemaName=$this->getDefaultSchema(); + $tableName=$parts[0]; + } + + $table->name=$tableName; + $table->schemaName=$schemaName; + if($schemaName===$this->getDefaultSchema()) + $table->rawName=$this->quoteTableName($tableName); + else + $table->rawName=$this->quoteTableName($schemaName).'.'.$this->quoteTableName($tableName); + } + + /** + * Collects the table column metadata. + * @param TOciTableSchema the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + list($schemaName,$tableName) = $this->getSchemaTableName($table->name); + + $sql=<< 0 then ',' || a.data_scale else '' end + || ')' + when data_type = 'DATE' then '' + else '(' || to_char(a.data_length) || ')' + end as data_type, + a.nullable, a.data_default, + ( SELECT D.constraint_type + FROM ALL_CONS_COLUMNS C + inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name + WHERE C.OWNER = B.OWNER + and C.table_name = B.object_name + and C.column_name = A.column_name + and D.constraint_type = 'P') as Key +FROM ALL_TAB_COLUMNS A +inner join ALL_OBJECTS B ON b.owner = a.owner and ltrim(B.OBJECT_NAME) = ltrim(A.TABLE_NAME) +WHERE + a.owner = '{$schemaName}' + and b.object_type = 'TABLE' + and b.object_name = '{$tableName}' +ORDER by a.column_id +EOD; + + $command=$this->getDbConnection()->createCommand($sql); + + if(($columns=$command->queryAll())===array()){ + return false; + } + + foreach($columns as $column) + { + $c=$this->createColumn($column); + + $table->columns[$c->name]=$c; + if($c->isPrimaryKey) + { + if($table->primaryKey===null) + $table->primaryKey=$c->name; + else if(is_string($table->primaryKey)) + $table->primaryKey=array($table->primaryKey,$c->name); + else + $table->primaryKey[]=$c->name; + } + } + return true; + } + + /** + * Creates a table column. + * @param array column metadata + * @return TDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new TOciColumnSchema; + $c->name=$column['COLUMN_NAME']; + $c->rawName=$this->quoteColumnName($c->name); + $c->allowNull=$column['NULLABLE']==='Y'; + $c->isPrimaryKey=strpos($column['KEY'],'P')!==false; + $c->isForeignKey=false; + $c->init($column['DATA_TYPE'],$column['DATA_DEFAULT']); + + return $c; + } + + /** + * Collects the primary and foreign key column details for the given table. + * @param TOciTableSchema the table metadata + */ + protected function findConstraints($table) + { + $sql=<< 'P' + order by d.constraint_name, c.position +EOD; + $command=$this->getDbConnection()->createCommand($sql); + foreach($command->queryAll() as $row) + { + if($row['constraint_type']==='R') // foreign key + { + $name = $row["COLUMN_NAME"]; + $table->foreignKeys[$name]=array($row["TABLE_REF"], array($row["COLUMN_REF"])); + if(isset($table->columns[$name])) + $table->columns[$name]->isForeignKey=true; + } + + } + } + + + /** + * Returns all table names in the database. + * @return array all table names in the database. + */ + protected function findTableNames($schema='') + { + if($schema==='') + { + $sql=<<getDbConnection()->createCommand($sql); + } + else + { + $sql=<<getDbConnection()->createCommand($sql); + $command->bindParam(':schema',$schema); + } + + $rows=$command->queryAll(); + $names=array(); + foreach($rows as $row) + { + if($schema===$this->getDefaultSchema()) + $names[]=$row['table_name']; + else + $names[]=$row['schema_name'].'.'.$row['table_name']; + } + return $names; + } +} diff --git a/framework/Db/Schema/oci/TOciTableSchema.php b/framework/Db/Schema/oci/TOciTableSchema.php new file mode 100755 index 00000000..9fe73624 --- /dev/null +++ b/framework/Db/Schema/oci/TOciTableSchema.php @@ -0,0 +1,28 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbTableSchame'); + +/** + * TOciTableSchema represents the metadata for a Ora table. + * + * @author Ricardo Grana + * @version $Id: TOciTableSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.oci + * @since 1.0.5 + */ +class TOciTableSchema extends TDbTableSchema +{ + /** + * @var string name of the schema (database) that this table belongs to. + * Defaults to null, meaning no schema (or the current database). + */ + public $schemaName; +} diff --git a/framework/Db/Schema/pgsql/TPgsqlColumnSchema.php b/framework/Db/Schema/pgsql/TPgsqlColumnSchema.php new file mode 100755 index 00000000..3677cd44 --- /dev/null +++ b/framework/Db/Schema/pgsql/TPgsqlColumnSchema.php @@ -0,0 +1,58 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbColumnSchema'); + +/** + * TPgsqlColumnSchema class describes the column meta data of a PostgreSQL table. + * + * @author Qiang Xue + * @version $Id: TPgsqlColumnSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.pgsql + * @since 1.0 + */ +class TPgsqlColumnSchema extends TDbColumnSchema +{ + /** + * Extracts the PHP type from DB type. + * @param string DB type + */ + protected function extractType($dbType) + { + if(strpos($dbType,'integer')!==false || strpos($dbType,'oid')===0) + $this->type='integer'; + else if(strpos($dbType,'bool')!==false) + $this->type='boolean'; + else if(preg_match('/(real|float|double)/',$dbType)) + $this->type='double'; + else + $this->type='string'; + } + + /** + * Extracts the default value for the column. + * The value is typecasted to correct PHP type. + * @param mixed the default value obtained from metadata + */ + protected function extractDefault($defaultValue) + { + if($defaultValue==='true') + $this->defaultValue=true; + else if($defaultValue==='false') + $this->defaultValue=false; + else if(strpos($defaultValue,'nextval')===0) + $this->defaultValue=null; + else if(preg_match('/\'(.*)\'::/',$defaultValue,$matches)) + $this->defaultValue=$this->typecast(str_replace("''","'",$matches[1])); + else if(preg_match('/^-?\d+(\.\d*)?$/',$defaultValue,$matches)) + $this->defaultValue=$this->typecast($defaultValue); + // else is null + } +} diff --git a/framework/Db/Schema/pgsql/TPgsqlSchema.php b/framework/Db/Schema/pgsql/TPgsqlSchema.php new file mode 100755 index 00000000..5f009208 --- /dev/null +++ b/framework/Db/Schema/pgsql/TPgsqlSchema.php @@ -0,0 +1,284 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbSchema'); + +/** + * TPgsqlSchema is the class for retrieving metadata information from a PostgreSQL database. + * + * @author Qiang Xue + * @version $Id: TPgsqlSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.pgsql + * @since 1.0 + */ +class TPgsqlSchema extends TDbSchema +{ + const DEFAULT_SCHEMA='public'; + private $_sequences=array(); + + /** + * Quotes a table name for use in a query. + * @param string table name + * @return string the properly quoted table name + */ + public function quoteTableName($name) + { + return '"'.$name.'"'; + } + + /** + * Creates a table instance representing the metadata for the named table. + * @return TDbTableSchema driver dependent table metadata. + */ + protected function createTable($name) + { + $table=new TPgsqlTableSchema; + $this->resolveTableNames($table,$name); + if(!$this->findColumns($table)) + return null; + $this->findConstraints($table); + + if(is_string($table->primaryKey) && isset($this->_sequences[$table->primaryKey])) + $table->sequenceName=$this->_sequences[$table->primaryKey]; + + return $table; + } + + /** + * Generates various kinds of table names. + * @param TPgsqlTableSchema the table instance + * @param string the unquoted table name + */ + protected function resolveTableNames($table,$name) + { + $parts=explode('.',str_replace('"','',$name)); + if(isset($parts[1])) + { + $schemaName=$parts[0]; + $tableName=$parts[1]; + } + else + { + $schemaName=self::DEFAULT_SCHEMA; + $tableName=$parts[0]; + } + + $table->name=$tableName; + $table->schemaName=$schemaName; + if($schemaName===self::DEFAULT_SCHEMA) + $table->rawName=$this->quoteTableName($tableName); + else + $table->rawName=$this->quoteTableName($schemaName).'.'.$this->quoteTableName($tableName); + } + + /** + * Collects the table column metadata. + * @param TPgsqlTableSchema the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + $sql=<< 0 AND NOT a.attisdropped + AND a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table + AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = :schema)) +ORDER BY a.attnum +EOD; + $command=$this->getDbConnection()->createCommand($sql); + $command->bindValue(':table',$table->name); + $command->bindValue(':schema',$table->schemaName); + + if(($columns=$command->queryAll())===array()) + return false; + + foreach($columns as $column) + { + $c=$this->createColumn($column); + $table->columns[$c->name]=$c; + + if(stripos($column['adsrc'],'nextval')===0 && preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i',$column['adsrc'],$matches)) + { + if(strpos($matches[1],'.')!==false || $table->schemaName===self::DEFAULT_SCHEMA) + $this->_sequences[$c->name]=$matches[1]; + else + $this->_sequences[$c->name]=$table->schemaName.'.'.$matches[1]; + } + } + return true; + } + + /** + * Creates a table column. + * @param array column metadata + * @return TDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new TPgsqlColumnSchema; + $c->name=$column['attname']; + $c->rawName=$this->quoteColumnName($c->name); + $c->allowNull=!$column['attnotnull']; + $c->isPrimaryKey=false; + $c->isForeignKey=false; + + $c->init($column['type'],$column['atthasdef'] ? $column['adsrc'] : null); + + return $c; + } + + /** + * Collects the primary and foreign key column details for the given table. + * @param TPgsqlTableSchema the table metadata + */ + protected function findConstraints($table) + { + $sql=<<getDbConnection()->createCommand($sql); + $command->bindValue(':table',$table->name); + $command->bindValue(':schema',$table->schemaName); + foreach($command->queryAll() as $row) + { + if($row['contype']==='p') // primary key + $this->findPrimaryKey($table,$row['indkey']); + else if($row['contype']==='f') // foreign key + $this->findForeignKey($table,$row['consrc']); + } + } + + /** + * Collects primary key information. + * @param TPgsqlTableSchema the table metadata + * @param string pgsql primary key index list + */ + protected function findPrimaryKey($table,$indices) + { + $indices=implode(', ',preg_split('/\s+/',$indices)); + $sql=<<getDbConnection()->createCommand($sql); + $command->bindValue(':table',$table->name); + $command->bindValue(':schema',$table->schemaName); + foreach($command->queryAll() as $row) + { + $name=$row['attname']; + if(isset($table->columns[$name])) + { + $table->columns[$name]->isPrimaryKey=true; + if($table->primaryKey===null) + $table->primaryKey=$name; + else if(is_string($table->primaryKey)) + $table->primaryKey=array($table->primaryKey,$name); + else + $table->primaryKey[]=$name; + } + } + } + + /** + * Collects foreign key information. + * @param TPgsqlTableSchema the table metadata + * @param string pgsql foreign key definition + */ + protected function findForeignKey($table,$src) + { + $matches=array(); + $brackets='\(([^\)]+)\)'; + $pattern="/FOREIGN\s+KEY\s+{$brackets}\s+REFERENCES\s+([^\(]+){$brackets}/i"; + if(preg_match($pattern,str_replace('"','',$src),$matches)) + { + $keys=preg_split('/,\s+/', $matches[1]); + $tableName=$matches[2]; + $fkeys=preg_split('/,\s+/', $matches[3]); + foreach($keys as $i=>$key) + { + $table->foreignKeys[$key]=array($tableName,$fkeys[$i]); + if(isset($table->columns[$key])) + $table->columns[$key]->isForeignKey=true; + } + } + } + + /** + * Returns all table names in the database. + * @return array all table names in the database. + * @since 1.0.2 + */ + protected function findTableNames($schema='') + { + if($schema==='') + $schema=self::DEFAULT_SCHEMA; + $sql=<<getDbConnection()->createCommand($sql); + $command->bindParam(':schema',$schema); + $rows=$command->queryAll(); + $names=array(); + foreach($rows as $row) + { + if($schema===self::DEFAULT_SCHEMA) + $names[]=$row['table_name']; + else + $names[]=$row['schema_name'].'.'.$row['table_name']; + } + return $names; + } +} diff --git a/framework/Db/Schema/pgsql/TPgsqlTableSchema.php b/framework/Db/Schema/pgsql/TPgsqlTableSchema.php new file mode 100755 index 00000000..9c14da28 --- /dev/null +++ b/framework/Db/Schema/pgsql/TPgsqlTableSchema.php @@ -0,0 +1,27 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbTableSchema'); + +/** + * TPgsqlTable represents the metadata for a PostgreSQL table. + * + * @author Qiang Xue + * @version $Id: TPgsqlTableSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.pgsql + * @since 1.0 + */ +class TPgsqlTableSchema extends TDbTableSchema +{ + /** + * @var string name of the schema that this table belongs to. + */ + public $schemaName; +} diff --git a/framework/Db/Schema/sqlite/TSqliteColumnSchema.php b/framework/Db/Schema/sqlite/TSqliteColumnSchema.php new file mode 100755 index 00000000..ca6858eb --- /dev/null +++ b/framework/Db/Schema/sqlite/TSqliteColumnSchema.php @@ -0,0 +1,35 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbColumnSchema'); + +/** + * TSqliteColumnSchema class describes the column meta data of a SQLite table. + * + * @author Qiang Xue + * @version $Id: TSqliteColumnSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.sqlite + * @since 1.0 + */ +class TSqliteColumnSchema extends TDbColumnSchema +{ + /** + * Extracts the default value for the column. + * The value is typecasted to correct PHP type. + * @param mixed the default value obtained from metadata + */ + protected function extractDefault($defaultValue) + { + if($this->type==='string') // PHP 5.2.6 adds single quotes while 5.2.0 doesn't + $this->defaultValue=trim($defaultValue,"'\""); + else + $this->defaultValue=$this->typecast($defaultValue); + } +} diff --git a/framework/Db/Schema/sqlite/TSqliteCommandBuilder.php b/framework/Db/Schema/sqlite/TSqliteCommandBuilder.php new file mode 100755 index 00000000..69ffd8a8 --- /dev/null +++ b/framework/Db/Schema/sqlite/TSqliteCommandBuilder.php @@ -0,0 +1,43 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbCommandBuilder'); + +/** + * TSqliteCommandBuilder provides basic methods to create query commands for SQLite tables. + * + * @author Qiang Xue + * @version $Id: TSqliteCommandBuilder.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.sqlite + * @since 1.0 + */ +class TSqliteCommandBuilder extends TDbCommandBuilder +{ + /** + * Generates the expression for selecting rows with specified composite key values. + * This method is overridden because SQLite does not support the default + * IN expression with composite columns. + * @param TDbTableSchema the table schema + * @param array list of primary key values to be selected within + * @param string column prefix (ended with dot) + * @return string the expression for selection + * @since 1.0.4 + */ + protected function createCompositeInCondition($table,$values,$prefix) + { + $keyNames=array(); + foreach(array_keys($values[0]) as $name) + $keyNames[]=$prefix.$table->columns[$name]->rawName; + $vs=array(); + foreach($values as $value) + $vs[]=implode("||','||",$value); + return implode("||','||",$keyNames).' IN ('.implode(', ',$vs).')'; + } +} diff --git a/framework/Db/Schema/sqlite/TSqliteSchema.php b/framework/Db/Schema/sqlite/TSqliteSchema.php new file mode 100755 index 00000000..0e3d4f9a --- /dev/null +++ b/framework/Db/Schema/sqlite/TSqliteSchema.php @@ -0,0 +1,134 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbSchema'); +prado::using('System.Db.Schema.TDbTableSchema'); +prado::using('System.Db.Schema.sqlite.TSqliteColumnSchema'); +prado::using('System.Db.Schema.sqlite.TSqliteCommandBuilder'); + +/** + * TSqliteSchema is the class for retrieving metadata information from a SQLite (2/3) database. + * + * @author Qiang Xue + * @version $Id: TSqliteSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.sqlite + * @since 1.0 + */ +class TSqliteSchema extends TDbSchema +{ + /** + * Returns all table names in the database. + * @param string the schema of the tables. This is not used for sqlite database. + * @return array all table names in the database. + * @since 1.0.2 + */ + protected function findTableNames($schema='') + { + $sql="SELECT DISTINCT tbl_name FROM sqlite_master WHERE tbl_name<>'sqlite_sequence'"; + return $this->getDbConnection()->createCommand($sql)->queryColumn(); + } + + /** + * Creates a command builder for the database. + * @return TSqliteCommandBuilder command builder instance + */ + protected function createCommandBuilder() + { + return new TSqliteCommandBuilder($this); + } + + /** + * Creates a table instance representing the metadata for the named table. + * @return TDbTableSchema driver dependent table metadata. Null if the table does not exist. + */ + protected function createTable($name) + { + $db=$this->getDbConnection(); + + $table=new TDbTableSchema; + $table->name=$name; + $table->rawName=$this->quoteTableName($name); + + if($this->findColumns($table)) + { + $this->findConstraints($table); + return $table; + } + else + return null; + } + + /** + * Collects the table column metadata. + * @param TDbTableSchema the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + $sql="PRAGMA table_info({$table->rawName})"; + $columns=$this->getDbConnection()->createCommand($sql)->queryAll(); + if(empty($columns)) + return false; + + foreach($columns as $column) + { + $c=$this->createColumn($column); + $table->columns[$c->name]=$c; + if($c->isPrimaryKey) + { + if($table->primaryKey===null) + $table->primaryKey=$c->name; + else if(is_string($table->primaryKey)) + $table->primaryKey=array($table->primaryKey,$c->name); + else + $table->primaryKey[]=$c->name; + } + } + if(is_string($table->primaryKey) && !strncasecmp($table->columns[$table->primaryKey]->dbType,'int',3)) + $table->sequenceName=''; + + return true; + } + + /** + * Collects the foreign key column details for the given table. + * @param TDbTableSchema the table metadata + */ + protected function findConstraints($table) + { + $foreignKeys=array(); + $sql="PRAGMA foreign_key_list({$table->rawName})"; + $keys=$this->getDbConnection()->createCommand($sql)->queryAll(); + foreach($keys as $key) + { + $column=$table->columns[$key['from']]; + $column->isForeignKey=true; + $foreignKeys[$key['from']]=array($key['table'],$key['to']); + } + $table->foreignKeys=$foreignKeys; + } + + /** + * Creates a table column. + * @param array column metadata + * @return TDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new TSqliteColumnSchema; + $c->name=$column['name']; + $c->rawName=$this->quoteColumnName($c->name); + $c->allowNull=!$column['notnull']; + $c->isPrimaryKey=$column['pk']!=0; + $c->isForeignKey=false; + $c->init(strtolower($column['type']),$column['dflt_value']); + return $c; + } +} -- cgit v1.2.3