diff options
Diffstat (limited to 'framework/db/schema')
24 files changed, 4972 insertions, 0 deletions
diff --git a/framework/db/schema/CDbColumnSchema.php b/framework/db/schema/CDbColumnSchema.php new file mode 100644 index 0000000..642094b --- /dev/null +++ b/framework/db/schema/CDbColumnSchema.php @@ -0,0 +1,148 @@ +<?php +/** + * CDbColumnSchema class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CDbColumnSchema class describes the column meta data of a database table. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CDbColumnSchema.php 3558 2012-02-09 17:39:04Z alexander.makarow $ + * @package system.db.schema + * @since 1.0 + */ +class CDbColumnSchema extends CComponent +{ + /** + * @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; + /** + * @var boolean whether this column is auto-incremental + * @since 1.1.7 + */ + public $autoIncrement=false; + + + /** + * 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 $dbType the column's DB type + * @param mixed $defaultValue 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 $dbType DB type + */ + protected function extractType($dbType) + { + if(stripos($dbType,'int')!==false && stripos($dbType,'unsigned 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 $dbType 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 $defaultValue 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 $value input value + * @return mixed converted value + */ + public function typecast($value) + { + if(gettype($value)===$this->type || $value===null || $value instanceof CDbExpression) + return $value; + if($value==='' && $this->allowNull) + return $this->type==='string' ? '' : null; + switch($this->type) + { + case 'string': return (string)$value; + case 'integer': return (integer)$value; + case 'boolean': return (boolean)$value; + case 'double': + default: return $value; + } + } +} diff --git a/framework/db/schema/CDbCommandBuilder.php b/framework/db/schema/CDbCommandBuilder.php new file mode 100644 index 0000000..bde4d06 --- /dev/null +++ b/framework/db/schema/CDbCommandBuilder.php @@ -0,0 +1,735 @@ +<?php +/** + * CDbCommandBuilder class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CDbCommandBuilder provides basic methods to create query commands for tables. + * + * @property CDbConnection $dbConnection Database connection. + * @property CDbSchema $schema The schema for this command builder. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CDbCommandBuilder.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema + * @since 1.0 + */ +class CDbCommandBuilder extends CComponent +{ + const PARAM_PREFIX=':yp'; + + private $_schema; + private $_connection; + + /** + * @param CDbSchema $schema the schema for this command builder + */ + public function __construct($schema) + { + $this->_schema=$schema; + $this->_connection=$schema->getDbConnection(); + } + + /** + * @return CDbConnection database connection. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * @return CDbSchema the schema for this command builder. + */ + public function getSchema() + { + return $this->_schema; + } + + /** + * Returns the last insertion ID for the specified table. + * @param mixed $table the table schema ({@link CDbTableSchema}) 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 $table the table schema ({@link CDbTableSchema}) or the table name (string). + * @param CDbCriteria $criteria the query criteria + * @param string $alias the alias name of the primary table. Defaults to 't'. + * @return CDbCommand query command. + */ + public function createFindCommand($table,$criteria,$alias='t') + { + $this->ensureTable($table); + $select=is_array($criteria->select) ? implode(', ',$criteria->select) : $criteria->select; + if($criteria->alias!='') + $alias=$criteria->alias; + $alias=$this->_schema->quoteTableName($alias); + + // issue 1432: need to expand * when SQL has JOIN + if($select==='*' && !empty($criteria->join)) + { + $prefix=$alias.'.'; + $select=array(); + foreach($table->getColumnNames() as $name) + $select[]=$prefix.$this->_schema->quoteColumnName($name); + $select=implode(', ',$select); + } + + $sql=($criteria->distinct ? 'SELECT DISTINCT':'SELECT')." {$select} FROM {$table->rawName} $alias"; + $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 $table the table schema ({@link CDbTableSchema}) or the table name (string). + * @param CDbCriteria $criteria the query criteria + * @param string $alias the alias name of the primary table. Defaults to 't'. + * @return CDbCommand query command. + */ + public function createCountCommand($table,$criteria,$alias='t') + { + $this->ensureTable($table); + if($criteria->alias!='') + $alias=$criteria->alias; + $alias=$this->_schema->quoteTableName($alias); + + if(!empty($criteria->group) || !empty($criteria->having)) + { + $select=is_array($criteria->select) ? implode(', ',$criteria->select) : $criteria->select; + if($criteria->alias!='') + $alias=$criteria->alias; + $sql=($criteria->distinct ? 'SELECT DISTINCT':'SELECT')." {$select} FROM {$table->rawName} $alias"; + $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="SELECT COUNT(*) FROM ($sql) sq"; + } + else + { + if(is_string($criteria->select) && stripos($criteria->select,'count')===0) + $sql="SELECT ".$criteria->select; + else if($criteria->distinct) + { + if(is_array($table->primaryKey)) + { + $pk=array(); + foreach($table->primaryKey as $key) + $pk[]=$alias.'.'.$key; + $pk=implode(', ',$pk); + } + else + $pk=$alias.'.'.$table->primaryKey; + $sql="SELECT COUNT(DISTINCT $pk)"; + } + else + $sql="SELECT COUNT(*)"; + $sql.=" FROM {$table->rawName} $alias"; + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + } + + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,$criteria->params); + return $command; + } + + /** + * Creates a DELETE command. + * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string). + * @param CDbCriteria $criteria the query criteria + * @return CDbCommand 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 $table the table schema ({@link CDbTableSchema}) or the table name (string). + * @param array $data data to be inserted (column name=>column value). If a key is not a valid column name, the corresponding value will be ignored. + * @return CDbCommand 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 CDbExpression) + { + $placeholders[]=$value->expression; + foreach($value->params as $n=>$v) + $values[$n]=$v; + } + else + { + $placeholders[]=self::PARAM_PREFIX.$i; + $values[self::PARAM_PREFIX.$i]=$column->typecast($value); + $i++; + } + } + } + if($fields===array()) + { + $pks=is_array($table->primaryKey) ? $table->primaryKey : array($table->primaryKey); + foreach($pks as $pk) + { + $fields[]=$table->getColumn($pk)->rawName; + $placeholders[]='NULL'; + } + } + $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 $table the table schema ({@link CDbTableSchema}) or the table name (string). + * @param array $data list of columns to be updated (name=>value) + * @param CDbCriteria $criteria the query criteria + * @return CDbCommand 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 CDbExpression) + { + $fields[]=$column->rawName.'='.$value->expression; + foreach($value->params as $n=>$v) + $values[$n]=$v; + } + 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 CDbException(Yii::t('yii','No columns are being updated for table "{table}".', + array('{table}'=>$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 $table the table schema ({@link CDbTableSchema}) or the table name (string). + * @param array $counters counters to be updated (counter increments/decrements indexed by column names.) + * @param CDbCriteria $criteria the query criteria + * @return CDbCommand 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 CDbException(Yii::t('yii','No counter columns are being updated for table "{table}".', + array('{table}'=>$table->name))); + } + + /** + * Creates a command based on a given SQL statement. + * @param string $sql the explicitly specified SQL statement + * @param array $params parameters that will be bound to the SQL statement + * @return CDbCommand 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 $sql the SQL statement to be altered + * @param string $join 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 $sql the SQL statement without WHERE clause + * @param string $condition 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 SQL statement without ORDER BY. + * @param string $orderBy 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 SQL query string without LIMIT and OFFSET. + * @param integer $limit maximum number of rows, -1 to ignore limit. + * @param integer $offset 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 SQL query string without GROUP BY. + * @param string $group 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 SQL query string without HAVING + * @param string $having HAVING + * @return string SQL with HAVING + */ + public function applyHaving($sql,$having) + { + if($having!='') + return $sql.' HAVING '.$having; + else + return $sql; + } + + /** + * Binds parameter values for an SQL command. + * @param CDbCommand $command database command + * @param array $values 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 $condition 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 CDbCriteria} object; + * Otherwise, it should be an instance of {@link CDbCriteria}. + * @param array $params 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 CDbCriteria::params} to set parameters. + * @return CDbCriteria the created query criteria + * @throws CException if the condition is not string, array and CDbCriteria + */ + public function createCriteria($condition='',$params=array()) + { + if(is_array($condition)) + $criteria=new CDbCriteria($condition); + else if($condition instanceof CDbCriteria) + $criteria=clone $condition; + else + { + $criteria=new CDbCriteria; + $criteria->condition=$condition; + $criteria->params=$params; + } + return $criteria; + } + + /** + * Creates a query criteria with the specified primary key. + * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string). + * @param mixed $pk 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 $condition 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 CDbCriteria}; + * Otherwise, it should be an instance of {@link CDbCriteria}. + * @param array $params 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 CDbCriteria::params} to set parameters. + * @param string $prefix column prefix (ended with dot). If null, it will be the table name + * @return CDbCriteria the created query criteria + */ + public function createPkCriteria($table,$pk,$condition='',$params=array(),$prefix=null) + { + $this->ensureTable($table); + $criteria=$this->createCriteria($condition,$params); + if($criteria->alias!='') + $prefix=$this->_schema->quoteTableName($criteria->alias).'.'; + 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,$prefix); + 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 $table the table schema ({@link CDbTableSchema}) or the table name (string). + * @param array $values list of primary key values to be selected within + * @param string $prefix 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 $table the table schema ({@link CDbTableSchema}) or the table name (string). + * @param array $columns column values that should be matched in the query (name=>value) + * @param mixed $condition 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 CDbCriteria}; + * Otherwise, it should be an instance of {@link CDbCriteria}. + * @param array $params parameters to be bound to an SQL statement. + * This is only used when the third parameter is a string (query condition). + * In other cases, please use {@link CDbCriteria::params} to set parameters. + * @param string $prefix column prefix (ended with dot). If null, it will be the table name + * @return CDbCriteria the created query criteria + */ + public function createColumnCriteria($table,$columns,$condition='',$params=array(),$prefix=null) + { + $this->ensureTable($table); + $criteria=$this->createCriteria($condition,$params); + if($criteria->alias!='') + $prefix=$this->_schema->quoteTableName($criteria->alias).'.'; + $bindByPosition=isset($criteria->params[0]); + $conditions=array(); + $values=array(); + $i=0; + if($prefix===null) + $prefix=$table->rawName.'.'; + foreach($columns as $name=>$value) + { + if(($column=$table->getColumn($name))!==null) + { + if(is_array($value)) + $conditions[]=$this->createInCondition($table,$name,$value,$prefix); + else if($value!==null) + { + if($bindByPosition) + { + $conditions[]=$prefix.$column->rawName.'=?'; + $values[]=$value; + } + else + { + $conditions[]=$prefix.$column->rawName.'='.self::PARAM_PREFIX.$i; + $values[self::PARAM_PREFIX.$i]=$value; + $i++; + } + } + else + $conditions[]=$prefix.$column->rawName.' IS NULL'; + } + else + throw new CDbException(Yii::t('yii','Table "{table}" does not have a column named "{column}".', + array('{table}'=>$table->name,'{column}'=>$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 $table the table schema ({@link CDbTableSchema}) or the table name (string). + * @param array $columns list of column names for potential search condition. + * @param mixed $keywords search keywords. This can be either a string with space-separated keywords or an array of keywords. + * @param string $prefix optional column prefix (with dot at the end). If null, the table name will be used as the prefix. + * @param boolean $caseSensitive whether the search is case-sensitive. Defaults to true. + * @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 CDbException(Yii::t('yii','Table "{table}" does not have a column named "{column}".', + array('{table}'=>$table->name,'{column}'=>$name))); + $condition=array(); + foreach($keywords as $keyword) + { + $keyword='%'.strtr($keyword,array('%'=>'\%', '_'=>'\_')).'%'; + 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 $table the table schema ({@link CDbTableSchema}) or the table name (string). + * @param mixed $columnName 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 $values list of key values to be selected within + * @param string $prefix column prefix (ended with dot). If null, it will be the table name + * @return string the expression for selection + */ + 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 CDbException(Yii::t('yii','Table "{table}" does not have a column named "{column}".', + array('{table}'=>$table->name, '{column}'=>$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 CDbException(Yii::t('yii','Table "{table}" does not have a column named "{column}".', + array('{table}'=>$table->name, '{column}'=>$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 CDbException(Yii::t('yii','The value for the column "{column}" is not supplied when querying the table "{table}".', + array('{table}'=>$table->name,'{column}'=>$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 CDbException(Yii::t('yii','Column name must be either a string or an array.')); + } + + /** + * Generates the expression for selecting rows with specified composite key values. + * @param CDbTableSchema $table the table schema + * @param array $values list of primary key values to be selected within + * @param string $prefix column prefix (ended with dot) + * @return string the expression for selection + */ + 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 table schema ({@link CDbTableSchema}) or table name (string). + * If this refers to a valid table name, this parameter will be returned with the corresponding table schema. + * @throws CDbException if the table name is not valid + */ + protected function ensureTable(&$table) + { + if(is_string($table) && ($table=$this->_schema->getTable($tableName=$table))===null) + throw new CDbException(Yii::t('yii','Table "{table}" does not exist.', + array('{table}'=>$tableName))); + } +}
\ No newline at end of file diff --git a/framework/db/schema/CDbCriteria.php b/framework/db/schema/CDbCriteria.php new file mode 100644 index 0000000..88741b7 --- /dev/null +++ b/framework/db/schema/CDbCriteria.php @@ -0,0 +1,607 @@ +<?php +/** + * CDbCriteria class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CDbCriteria represents a query criteria, such as conditions, ordering by, limit/offset. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CDbCriteria.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema + * @since 1.0 + */ +class CDbCriteria extends CComponent +{ + const PARAM_PREFIX=':ycp'; + /** + * @var integer the global counter for anonymous binding parameters. + * This counter is used for generating the name for the anonymous parameters. + */ + public static $paramCount=0; + /** + * @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 boolean whether to select distinct rows of data only. If this is set true, + * the SELECT clause would be changed to SELECT DISTINCT. + */ + public $distinct=false; + /** + * @var string query condition. This refers to the WHERE clause in an SQL statement. + * For example, <code>age>31 AND team=1</code>. + */ + public $condition=''; + /** + * @var array list of query parameter values indexed by parameter placeholders. + * For example, <code>array(':name'=>'Dan', ':age'=>31)</code>. + */ + 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, <code>'projectID, teamID'</code>. + */ + public $group=''; + /** + * @var string how to join with other tables. This refers to the JOIN clause in an SQL statement. + * For example, <code>'LEFT JOIN users ON users.id=authorID'</code>. + */ + public $join=''; + /** + * @var string the condition to be applied with GROUP-BY clause. + * For example, <code>'SUM(revenue)<50000'</code>. + */ + public $having=''; + /** + * @var mixed the relational query criteria. This is used for fetching related objects in eager loading fashion. + * This property is effective only when the criteria is passed as a parameter to the following methods of CActiveRecord: + * <ul> + * <li>{@link CActiveRecord::find()}</li> + * <li>{@link CActiveRecord::findAll()}</li> + * <li>{@link CActiveRecord::findByPk()}</li> + * <li>{@link CActiveRecord::findAllByPk()}</li> + * <li>{@link CActiveRecord::findByAttributes()}</li> + * <li>{@link CActiveRecord::findAllByAttributes()}</li> + * <li>{@link CActiveRecord::count()}</li> + * </ul> + * The property value will be used as the parameter to the {@link CActiveRecord::with()} method + * to perform the eager loading. Please refer to {@link CActiveRecord::with()} on how to specify this parameter. + * @since 1.1.0 + */ + public $with; + /** + * @var string the alias name of the table. If not set, it means the alias is 't'. + */ + public $alias; + /** + * @var boolean whether the foreign tables should be joined with the primary table in a single SQL. + * This property is only used in relational AR queries for HAS_MANY and MANY_MANY relations. + * + * When this property is set true, only a single SQL will be executed for a relational AR query, + * even if the primary table is limited and the relationship between a foreign table and the primary + * table is many-to-one. + * + * When this property is set false, a SQL statement will be executed for each HAS_MANY relation. + * + * When this property is not set, if the primary table is limited or paginated, + * a SQL statement will be executed for each HAS_MANY relation. + * Otherwise, a single SQL statement will be executed for all. + * + * @since 1.1.4 + */ + public $together; + /** + * @var string the name of the AR attribute whose value should be used as index of the query result array. + * Defaults to null, meaning the result array will be zero-based integers. + * @since 1.1.5 + */ + public $index; + /** + * @var mixed scopes to apply + * + * This property is effective only when passing criteria to + * the one of the following methods: + * <ul> + * <li>{@link CActiveRecord::find()}</li> + * <li>{@link CActiveRecord::findAll()}</li> + * <li>{@link CActiveRecord::findByPk()}</li> + * <li>{@link CActiveRecord::findAllByPk()}</li> + * <li>{@link CActiveRecord::findByAttributes()}</li> + * <li>{@link CActiveRecord::findAllByAttributes()}</li> + * <li>{@link CActiveRecord::count()}</li> + * </ul> + * + * Can be set to one of the following: + * <ul> + * <li>One scope: $criteria->scopes='scopeName';</li> + * <li>Multiple scopes: $criteria->scopes=array('scopeName1','scopeName2');</li> + * <li>Scope with parameters: $criteria->scopes=array('scopeName'=>array($params));</li> + * <li>Multiple scopes with parameters: $criteria->scopes=array('scopeName1'=>array($params1),'scopeName2'=>array($params2));</li> + * <li>Multiple scopes with the same name: array(array('scopeName'=>array($params1)),array('scopeName'=>array($params2)));</li> + * </ul> + * @since 1.1.7 + */ + public $scopes; + + /** + * Constructor. + * @param array $data criteria initial property values (indexed by property name) + */ + public function __construct($data=array()) + { + foreach($data as $name=>$value) + $this->$name=$value; + } + + /** + * Remaps criteria parameters on unserialize to prevent name collisions. + * @since 1.1.9 + */ + public function __wakeup() + { + $map=array(); + $params=array(); + foreach($this->params as $name=>$value) + { + $newName=self::PARAM_PREFIX.self::$paramCount++; + $map[$name]=$newName; + $params[$newName]=$value; + } + $this->condition=strtr($this->condition,$map); + $this->params=$params; + } + + /** + * Appends a condition to the existing {@link condition}. + * The new condition and the existing condition will be concatenated via the specified operator + * which defaults to 'AND'. + * The new condition can also be an array. In this case, all elements in the array + * will be concatenated together via the operator. + * This method handles the case when the existing condition is empty. + * After calling this method, the {@link condition} property will be modified. + * @param mixed $condition the new condition. It can be either a string or an array of strings. + * @param string $operator the operator to join different conditions. Defaults to 'AND'. + * @return CDbCriteria the criteria object itself + */ + public function addCondition($condition,$operator='AND') + { + if(is_array($condition)) + { + if($condition===array()) + return $this; + $condition='('.implode(') '.$operator.' (',$condition).')'; + } + if($this->condition==='') + $this->condition=$condition; + else + $this->condition='('.$this->condition.') '.$operator.' ('.$condition.')'; + return $this; + } + + /** + * Appends a search condition to the existing {@link condition}. + * The search condition and the existing condition will be concatenated via the specified operator + * which defaults to 'AND'. + * The search condition is generated using the SQL LIKE operator with the given column name and + * search keyword. + * @param string $column the column name (or a valid SQL expression) + * @param string $keyword the search keyword. This interpretation of the keyword is affected by the next parameter. + * @param boolean $escape whether the keyword should be escaped if it contains characters % or _. + * When this parameter is true (default), the special characters % (matches 0 or more characters) + * and _ (matches a single character) will be escaped, and the keyword will be surrounded with a % + * character on both ends. When this parameter is false, the keyword will be directly used for + * matching without any change. + * @param string $operator the operator used to concatenate the new condition with the existing one. + * Defaults to 'AND'. + * @param string $like the LIKE operator. Defaults to 'LIKE'. You may also set this to be 'NOT LIKE'. + * @return CDbCriteria the criteria object itself + */ + public function addSearchCondition($column,$keyword,$escape=true,$operator='AND',$like='LIKE') + { + if($keyword=='') + return $this; + if($escape) + $keyword='%'.strtr($keyword,array('%'=>'\%', '_'=>'\_', '\\'=>'\\\\')).'%'; + $condition=$column." $like ".self::PARAM_PREFIX.self::$paramCount; + $this->params[self::PARAM_PREFIX.self::$paramCount++]=$keyword; + return $this->addCondition($condition, $operator); + } + + /** + * Appends an IN condition to the existing {@link condition}. + * The IN condition and the existing condition will be concatenated via the specified operator + * which defaults to 'AND'. + * The IN condition is generated by using the SQL IN operator which requires the specified + * column value to be among the given list of values. + * @param string $column the column name (or a valid SQL expression) + * @param array $values list of values that the column value should be in + * @param string $operator the operator used to concatenate the new condition with the existing one. + * Defaults to 'AND'. + * @return CDbCriteria the criteria object itself + */ + public function addInCondition($column,$values,$operator='AND') + { + if(($n=count($values))<1) + return $this->addCondition('0=1',$operator); // 0=1 is used because in MSSQL value alone can't be used in WHERE + if($n===1) + { + $value=reset($values); + if($value===null) + return $this->addCondition($column.' IS NULL'); + $condition=$column.'='.self::PARAM_PREFIX.self::$paramCount; + $this->params[self::PARAM_PREFIX.self::$paramCount++]=$value; + } + else + { + $params=array(); + foreach($values as $value) + { + $params[]=self::PARAM_PREFIX.self::$paramCount; + $this->params[self::PARAM_PREFIX.self::$paramCount++]=$value; + } + $condition=$column.' IN ('.implode(', ',$params).')'; + } + return $this->addCondition($condition,$operator); + } + + /** + * Appends an NOT IN condition to the existing {@link condition}. + * The NOT IN condition and the existing condition will be concatenated via the specified operator + * which defaults to 'AND'. + * The NOT IN condition is generated by using the SQL NOT IN operator which requires the specified + * column value to be among the given list of values. + * @param string $column the column name (or a valid SQL expression) + * @param array $values list of values that the column value should not be in + * @param string $operator the operator used to concatenate the new condition with the existing one. + * Defaults to 'AND'. + * @return CDbCriteria the criteria object itself + * @since 1.1.1 + */ + public function addNotInCondition($column,$values,$operator='AND') + { + if(($n=count($values))<1) + return $this; + if($n===1) + { + $value=reset($values); + if($value===null) + return $this->addCondition($column.' IS NOT NULL'); + $condition=$column.'!='.self::PARAM_PREFIX.self::$paramCount; + $this->params[self::PARAM_PREFIX.self::$paramCount++]=$value; + } + else + { + $params=array(); + foreach($values as $value) + { + $params[]=self::PARAM_PREFIX.self::$paramCount; + $this->params[self::PARAM_PREFIX.self::$paramCount++]=$value; + } + $condition=$column.' NOT IN ('.implode(', ',$params).')'; + } + return $this->addCondition($condition,$operator); + } + + /** + * Appends a condition for matching the given list of column values. + * The generated condition will be concatenated to the existing {@link condition} + * via the specified operator which defaults to 'AND'. + * The condition is generated by matching each column and the corresponding value. + * @param array $columns list of column names and values to be matched (name=>value) + * @param string $columnOperator the operator to concatenate multiple column matching condition. Defaults to 'AND'. + * @param string $operator the operator used to concatenate the new condition with the existing one. + * Defaults to 'AND'. + * @return CDbCriteria the criteria object itself + */ + public function addColumnCondition($columns,$columnOperator='AND',$operator='AND') + { + $params=array(); + foreach($columns as $name=>$value) + { + if($value===null) + $params[]=$name.' IS NULL'; + else + { + $params[]=$name.'='.self::PARAM_PREFIX.self::$paramCount; + $this->params[self::PARAM_PREFIX.self::$paramCount++]=$value; + } + } + return $this->addCondition(implode(" $columnOperator ",$params), $operator); + } + + /** + * Adds a comparison expression to the {@link condition} property. + * + * This method is a helper that appends to the {@link condition} property + * with a new comparison expression. The comparison is done by comparing a column + * with the given value using some comparison operator. + * + * The comparison operator is intelligently determined based on the first few + * characters in the given value. In particular, it recognizes the following operators + * if they appear as the leading characters in the given value: + * <ul> + * <li><code><</code>: the column must be less than the given value.</li> + * <li><code>></code>: the column must be greater than the given value.</li> + * <li><code><=</code>: the column must be less than or equal to the given value.</li> + * <li><code>>=</code>: the column must be greater than or equal to the given value.</li> + * <li><code><></code>: the column must not be the same as the given value. + * Note that when $partialMatch is true, this would mean the value must not be a substring + * of the column.</li> + * <li><code>=</code>: the column must be equal to the given value.</li> + * <li>none of the above: the column must be equal to the given value. Note that when $partialMatch + * is true, this would mean the value must be the same as the given value or be a substring of it.</li> + * </ul> + * + * Note that any surrounding white spaces will be removed from the value before comparison. + * When the value is empty, no comparison expression will be added to the search condition. + * + * @param string $column the name of the column to be searched + * @param mixed $value the column value to be compared with. If the value is a string, the aforementioned + * intelligent comparison will be conducted. If the value is an array, the comparison is done + * by exact match of any of the value in the array. If the string or the array is empty, + * the existing search condition will not be modified. + * @param boolean $partialMatch whether the value should consider partial text match (using LIKE and NOT LIKE operators). + * Defaults to false, meaning exact comparison. + * @param string $operator the operator used to concatenate the new condition with the existing one. + * Defaults to 'AND'. + * @param boolean $escape whether the value should be escaped if $partialMatch is true and + * the value contains characters % or _. When this parameter is true (default), + * the special characters % (matches 0 or more characters) + * and _ (matches a single character) will be escaped, and the value will be surrounded with a % + * character on both ends. When this parameter is false, the value will be directly used for + * matching without any change. + * @return CDbCriteria the criteria object itself + * @since 1.1.1 + */ + public function compare($column, $value, $partialMatch=false, $operator='AND', $escape=true) + { + if(is_array($value)) + { + if($value===array()) + return $this; + return $this->addInCondition($column,$value,$operator); + } + else + $value="$value"; + + if(preg_match('/^(?:\s*(<>|<=|>=|<|>|=))?(.*)$/',$value,$matches)) + { + $value=$matches[2]; + $op=$matches[1]; + } + else + $op=''; + + if($value==='') + return $this; + + if($partialMatch) + { + if($op==='') + return $this->addSearchCondition($column,$value,$escape,$operator); + if($op==='<>') + return $this->addSearchCondition($column,$value,$escape,$operator,'NOT LIKE'); + } + else if($op==='') + $op='='; + + $this->addCondition($column.$op.self::PARAM_PREFIX.self::$paramCount,$operator); + $this->params[self::PARAM_PREFIX.self::$paramCount++]=$value; + + return $this; + } + + /** + * Adds a between condition to the {@link condition} property. + * + * The new between condition and the existing condition will be concatenated via + * the specified operator which defaults to 'AND'. + * If one or both values are empty then the condition is not added to the existing condition. + * This method handles the case when the existing condition is empty. + * After calling this method, the {@link condition} property will be modified. + * @param string $column the name of the column to search between. + * @param string $valueStart the beginning value to start the between search. + * @param string $valueEnd the ending value to end the between search. + * @param string $operator the operator used to concatenate the new condition with the existing one. + * Defaults to 'AND'. + * @return CDbCriteria the criteria object itself + * @since 1.1.2 + */ + public function addBetweenCondition($column,$valueStart,$valueEnd,$operator='AND') + { + if($valueStart==='' || $valueEnd==='') + return $this; + + $paramStart=self::PARAM_PREFIX.self::$paramCount++; + $paramEnd=self::PARAM_PREFIX.self::$paramCount++; + $this->params[$paramStart]=$valueStart; + $this->params[$paramEnd]=$valueEnd; + $condition="$column BETWEEN $paramStart AND $paramEnd"; + + if($this->condition==='') + $this->condition=$condition; + else + $this->condition='('.$this->condition.') '.$operator.' ('.$condition.')'; + return $this; + } + + /** + * 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 mixed $criteria the criteria to be merged with. Either an array or CDbCriteria. + * @param boolean $useAnd whether to use 'AND' to merge condition and having options. + * If false, 'OR' will be used instead. Defaults to 'AND'. + */ + 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($criteria->alias!==null) + $this->alias=$criteria->alias; + + if($this->order!==$criteria->order) + { + if($this->order==='') + $this->order=$criteria->order; + else if($criteria->order!=='') + $this->order=$criteria->order.', '.$this->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})"; + } + + if($criteria->distinct>0) + $this->distinct=$criteria->distinct; + + if($criteria->together!==null) + $this->together=$criteria->together; + + if($criteria->index!==null) + $this->index=$criteria->index; + + if(empty($this->scopes)) + $this->scopes=$criteria->scopes; + else if(!empty($criteria->scopes)) + { + $scopes1=(array)$this->scopes; + $scopes2=(array)$criteria->scopes; + foreach($scopes1 as $k=>$v) + { + if(is_integer($k)) + $scopes[]=$v; + else if(isset($scopes2[$k])) + $scopes[]=array($k=>$v); + else + $scopes[$k]=$v; + } + foreach($scopes2 as $k=>$v) + { + if(is_integer($k)) + $scopes[]=$v; + else if(isset($scopes1[$k])) + $scopes[]=array($k=>$v); + else + $scopes[$k]=$v; + } + $this->scopes=$scopes; + } + + if(empty($this->with)) + $this->with=$criteria->with; + else if(!empty($criteria->with)) + { + $this->with=(array)$this->with; + foreach((array)$criteria->with as $k=>$v) + { + if(is_integer($k)) + $this->with[]=$v; + else if(isset($this->with[$k])) + { + $excludes=array(); + foreach(array('joinType','on') as $opt) + { + if(isset($this->with[$k][$opt])) + $excludes[$opt]=$this->with[$k][$opt]; + if(isset($v[$opt])) + $excludes[$opt]= ($opt==='on' && isset($excludes[$opt]) && $v[$opt]!==$excludes[$opt]) ? + "($excludes[$opt]) AND $v[$opt]" : $v[$opt]; + unset($this->with[$k][$opt]); + unset($v[$opt]); + } + $this->with[$k]=new self($this->with[$k]); + $this->with[$k]->mergeWith($v,$useAnd); + $this->with[$k]=$this->with[$k]->toArray(); + if (count($excludes)!==0) + $this->with[$k]=CMap::mergeArray($this->with[$k],$excludes); + } + else + $this->with[$k]=$v; + } + } + } + + /** + * @return array the array representation of the criteria + */ + public function toArray() + { + $result=array(); + foreach(array('select', 'condition', 'params', 'limit', 'offset', 'order', 'group', 'join', 'having', 'distinct', 'scopes', 'with', 'alias', 'index', 'together') as $name) + $result[$name]=$this->$name; + return $result; + } +} diff --git a/framework/db/schema/CDbExpression.php b/framework/db/schema/CDbExpression.php new file mode 100644 index 0000000..5fc8316 --- /dev/null +++ b/framework/db/schema/CDbExpression.php @@ -0,0 +1,61 @@ +<?php +/** + * CDbExpression class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CDbExpression represents a DB expression that does not need escaping. + * CDbExpression is mainly used in {@link CActiveRecord} as attribute values. + * When inserting or updating a {@link CActiveRecord}, attribute values of + * type CDbExpression 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. + * + * Starting from version 1.1.1, one can also specify parameters to be bound + * for the expression. For example, if the expression is 'LOWER(:value)', then + * one can set {@link params} to be <code>array(':value'=>$value)</code>. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CDbExpression.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema + */ +class CDbExpression extends CComponent +{ + /** + * @var string the DB expression + */ + public $expression; + /** + * @var array list of parameters that should be bound for this expression. + * The keys are placeholders appearing in {@link expression}, while the values + * are the corresponding parameter values. + * @since 1.1.1 + */ + public $params=array(); + + /** + * Constructor. + * @param string $expression the DB expression + * @param array $params parameters + */ + public function __construct($expression,$params=array()) + { + $this->expression=$expression; + $this->params=$params; + } + + /** + * 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/CDbSchema.php b/framework/db/schema/CDbSchema.php new file mode 100644 index 0000000..9cd7e15 --- /dev/null +++ b/framework/db/schema/CDbSchema.php @@ -0,0 +1,563 @@ +<?php +/** + * CDbSchema class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CDbSchema is the base class for retrieving metadata information. + * + * @property CDbConnection $dbConnection Database connection. The connection is active. + * @property array $tables The metadata for all tables in the database. + * Each array element is an instance of {@link CDbTableSchema} (or its child class). + * The array keys are table names. + * @property array $tableNames All table names in the database. + * @property CDbCommandBuilder $commandBuilder The SQL command builder for this connection. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CDbSchema.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema + * @since 1.0 + */ +abstract class CDbSchema extends CComponent +{ + /** + * @var array the abstract column types mapped to physical column types. + * @since 1.1.6 + */ + public $columnTypes=array(); + + private $_tableNames=array(); + private $_tables=array(); + private $_connection; + private $_builder; + private $_cacheExclude=array(); + + /** + * Loads the metadata for the specified table. + * @param string $name table name + * @return CDbTableSchema driver dependent table metadata, null if the table does not exist. + */ + abstract protected function loadTable($name); + + /** + * Constructor. + * @param CDbConnection $conn database connection. + */ + public function __construct($conn) + { + $this->_connection=$conn; + foreach($conn->schemaCachingExclude as $name) + $this->_cacheExclude[$name]=true; + } + + /** + * @return CDbConnection database connection. The connection is active. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * Obtains the metadata for the named table. + * @param string $name table name + * @param boolean $refresh if we need to refresh schema cache for a table. + * Parameter available since 1.1.9 + * @return CDbTableSchema table metadata. Null if the named table does not exist. + */ + public function getTable($name,$refresh=false) + { + if($refresh===false && isset($this->_tables[$name])) + return $this->_tables[$name]; + else + { + if($this->_connection->tablePrefix!==null && strpos($name,'{{')!==false) + $realName=preg_replace('/\{\{(.*?)\}\}/',$this->_connection->tablePrefix.'$1',$name); + else + $realName=$name; + + // temporarily disable query caching + if($this->_connection->queryCachingDuration>0) + { + $qcDuration=$this->_connection->queryCachingDuration; + $this->_connection->queryCachingDuration=0; + } + + if(!isset($this->_cacheExclude[$name]) && ($duration=$this->_connection->schemaCachingDuration)>0 && $this->_connection->schemaCacheID!==false && ($cache=Yii::app()->getComponent($this->_connection->schemaCacheID))!==null) + { + $key='yii:dbschema'.$this->_connection->connectionString.':'.$this->_connection->username.':'.$name; + $table=$cache->get($key); + if($refresh===true || $table===false) + { + $table=$this->loadTable($realName); + if($table!==null) + $cache->set($key,$table,$duration); + } + $this->_tables[$name]=$table; + } + else + $this->_tables[$name]=$table=$this->loadTable($realName); + + if(isset($qcDuration)) // re-enable query caching + $this->_connection->queryCachingDuration=$qcDuration; + + return $table; + } + } + + /** + * Returns the metadata for all tables in the database. + * @param string $schema 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 CDbTableSchema} (or its child class). + * The array keys are table names. + */ + public function getTables($schema='') + { + $tables=array(); + foreach($this->getTableNames($schema) as $name) + { + if(($table=$this->getTable($name))!==null) + $tables[$name]=$table; + } + return $tables; + } + + /** + * Returns all table names in the database. + * @param string $schema 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. + */ + public function getTableNames($schema='') + { + if(!isset($this->_tableNames[$schema])) + $this->_tableNames[$schema]=$this->findTableNames($schema); + return $this->_tableNames[$schema]; + } + + /** + * @return CDbCommandBuilder 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() + { + if(($duration=$this->_connection->schemaCachingDuration)>0 && $this->_connection->schemaCacheID!==false && ($cache=Yii::app()->getComponent($this->_connection->schemaCacheID))!==null) + { + foreach(array_keys($this->_tables) as $name) + { + if(!isset($this->_cacheExclude[$name])) + { + $key='yii:dbschema'.$this->_connection->connectionString.':'.$this->_connection->username.':'.$name; + $cache->delete($key); + } + } + } + $this->_tables=array(); + $this->_tableNames=array(); + $this->_builder=null; + } + + /** + * Quotes a table name for use in a query. + * If the table name contains schema prefix, the prefix will also be properly quoted. + * @param string $name table name + * @return string the properly quoted table name + * @see quoteSimpleTableName + */ + public function quoteTableName($name) + { + if(strpos($name,'.')===false) + return $this->quoteSimpleTableName($name); + $parts=explode('.',$name); + foreach($parts as $i=>$part) + $parts[$i]=$this->quoteSimpleTableName($part); + return implode('.',$parts); + + } + + /** + * Quotes a simple table name for use in a query. + * A simple table name does not schema prefix. + * @param string $name table name + * @return string the properly quoted table name + * @since 1.1.6 + */ + public function quoteSimpleTableName($name) + { + return "'".$name."'"; + } + + /** + * Quotes a column name for use in a query. + * If the column name contains prefix, the prefix will also be properly quoted. + * @param string $name column name + * @return string the properly quoted column name + * @see quoteSimpleColumnName + */ + public function quoteColumnName($name) + { + if(($pos=strrpos($name,'.'))!==false) + { + $prefix=$this->quoteTableName(substr($name,0,$pos)).'.'; + $name=substr($name,$pos+1); + } + else + $prefix=''; + return $prefix . ($name==='*' ? $name : $this->quoteSimpleColumnName($name)); + } + + /** + * Quotes a simple column name for use in a query. + * A simple column name does not contain prefix. + * @param string $name column name + * @return string the properly quoted column name + * @since 1.1.6 + */ + public function quoteSimpleColumnName($name) + { + return '"'.$name.'"'; + } + + /** + * Compares two table names. + * The table names can be either quoted or unquoted. This method + * will consider both cases. + * @param string $name1 table name 1 + * @param string $name2 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); + if($this->_connection->tablePrefix!==null) + { + if(strpos($name1,'{')!==false) + $name1=$this->_connection->tablePrefix.str_replace(array('{','}'),'',$name1); + if(strpos($name2,'{')!==false) + $name2=$this->_connection->tablePrefix.str_replace(array('{','}'),'',$name2); + } + return $name1===$name2; + } + + /** + * Resets the sequence value of a table's primary key. + * The sequence will be reset such that the primary key of the next new row inserted + * will have the specified value or 1. + * @param CDbTableSchema $table the table schema whose primary key sequence will be reset + * @param mixed $value the value for the primary key of the next new row inserted. If this is not set, + * the next new row's primary key will have a value 1. + * @since 1.1 + */ + public function resetSequence($table,$value=null) + { + } + + /** + * Enables or disables integrity check. + * @param boolean $check whether to turn on or off the integrity check. + * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema. + * @since 1.1 + */ + public function checkIntegrity($check=true,$schema='') + { + } + + /** + * Creates a command builder for the database. + * This method may be overridden by child classes to create a DBMS-specific command builder. + * @return CDbCommandBuilder command builder instance + */ + protected function createCommandBuilder() + { + return new CDbCommandBuilder($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 implementation simply throws an exception. + * @param string $schema 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. + */ + protected function findTableNames($schema='') + { + throw new CDbException(Yii::t('yii','{class} does not support fetching all table names.', + array('{class}'=>get_class($this)))); + } + + /** + * Converts an abstract column type into a physical column type. + * The conversion is done using the type map specified in {@link columnTypes}. + * These abstract column types are supported (using MySQL as example to explain the corresponding + * physical types): + * <ul> + * <li>pk: an auto-incremental primary key type, will be converted into "int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY"</li> + * <li>string: string type, will be converted into "varchar(255)"</li> + * <li>text: a long string type, will be converted into "text"</li> + * <li>integer: integer type, will be converted into "int(11)"</li> + * <li>boolean: boolean type, will be converted into "tinyint(1)"</li> + * <li>float: float number type, will be converted into "float"</li> + * <li>decimal: decimal number type, will be converted into "decimal"</li> + * <li>datetime: datetime type, will be converted into "datetime"</li> + * <li>timestamp: timestamp type, will be converted into "timestamp"</li> + * <li>time: time type, will be converted into "time"</li> + * <li>date: date type, will be converted into "date"</li> + * <li>binary: binary data type, will be converted into "blob"</li> + * </ul> + * + * If the abstract type contains two or more parts separated by spaces (e.g. "string NOT NULL"), then only + * the first part will be converted, and the rest of the parts will be appended to the conversion result. + * For example, 'string NOT NULL' is converted to 'varchar(255) NOT NULL'. + * @param string $type abstract column type + * @return string physical column type. + * @since 1.1.6 + */ + public function getColumnType($type) + { + if(isset($this->columnTypes[$type])) + return $this->columnTypes[$type]; + else if(($pos=strpos($type,' '))!==false) + { + $t=substr($type,0,$pos); + return (isset($this->columnTypes[$t]) ? $this->columnTypes[$t] : $t).substr($type,$pos); + } + else + return $type; + } + + /** + * Builds a SQL statement for creating a new DB table. + * + * The columns in the new table should be specified as name-definition pairs (e.g. 'name'=>'string'), + * where name stands for a column name which will be properly quoted by the method, and definition + * stands for the column type which can contain an abstract DB type. + * The {@link getColumnType} method will be invoked to convert any abstract type into a physical one. + * + * If a column is specified with definition only (e.g. 'PRIMARY KEY (name, type)'), it will be directly + * inserted into the generated SQL. + * + * @param string $table the name of the table to be created. The name will be properly quoted by the method. + * @param array $columns the columns (name=>definition) in the new table. + * @param string $options additional SQL fragment that will be appended to the generated SQL. + * @return string the SQL statement for creating a new DB table. + * @since 1.1.6 + */ + public function createTable($table, $columns, $options=null) + { + $cols=array(); + foreach($columns as $name=>$type) + { + if(is_string($name)) + $cols[]="\t".$this->quoteColumnName($name).' '.$this->getColumnType($type); + else + $cols[]="\t".$type; + } + $sql="CREATE TABLE ".$this->quoteTableName($table)." (\n".implode(",\n",$cols)."\n)"; + return $options===null ? $sql : $sql.' '.$options; + } + + /** + * Builds a SQL statement for renaming a DB table. + * @param string $table the table to be renamed. The name will be properly quoted by the method. + * @param string $newName the new table name. The name will be properly quoted by the method. + * @return string the SQL statement for renaming a DB table. + * @since 1.1.6 + */ + public function renameTable($table, $newName) + { + return 'RENAME TABLE ' . $this->quoteTableName($table) . ' TO ' . $this->quoteTableName($newName); + } + + /** + * Builds a SQL statement for dropping a DB table. + * @param string $table the table to be dropped. The name will be properly quoted by the method. + * @return string the SQL statement for dropping a DB table. + * @since 1.1.6 + */ + public function dropTable($table) + { + return "DROP TABLE ".$this->quoteTableName($table); + } + + /** + * Builds a SQL statement for truncating a DB table. + * @param string $table the table to be truncated. The name will be properly quoted by the method. + * @return string the SQL statement for truncating a DB table. + * @since 1.1.6 + */ + public function truncateTable($table) + { + return "TRUNCATE TABLE ".$this->quoteTableName($table); + } + + /** + * Builds a SQL statement for adding a new DB column. + * @param string $table the table that the new column will be added to. The table name will be properly quoted by the method. + * @param string $column the name of the new column. The name will be properly quoted by the method. + * @param string $type the column type. The {@link getColumnType} method will be invoked to convert abstract column type (if any) + * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. + * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'. + * @return string the SQL statement for adding a new column. + * @since 1.1.6 + */ + public function addColumn($table, $column, $type) + { + return 'ALTER TABLE ' . $this->quoteTableName($table) + . ' ADD ' . $this->quoteColumnName($column) . ' ' + . $this->getColumnType($type); + } + + /** + * Builds a SQL statement for dropping a DB column. + * @param string $table the table whose column is to be dropped. The name will be properly quoted by the method. + * @param string $column the name of the column to be dropped. The name will be properly quoted by the method. + * @return string the SQL statement for dropping a DB column. + * @since 1.1.6 + */ + public function dropColumn($table, $column) + { + return "ALTER TABLE ".$this->quoteTableName($table) + ." DROP COLUMN ".$this->quoteColumnName($column); + } + + /** + * Builds a SQL statement for renaming a column. + * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method. + * @param string $name the old name of the column. The name will be properly quoted by the method. + * @param string $newName the new name of the column. The name will be properly quoted by the method. + * @return string the SQL statement for renaming a DB column. + * @since 1.1.6 + */ + public function renameColumn($table, $name, $newName) + { + return "ALTER TABLE ".$this->quoteTableName($table) + . " RENAME COLUMN ".$this->quoteColumnName($name) + . " TO ".$this->quoteColumnName($newName); + } + + /** + * Builds a SQL statement for changing the definition of a column. + * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method. + * @param string $column the name of the column to be changed. The name will be properly quoted by the method. + * @param string $type the new column type. The {@link getColumnType} method will be invoked to convert abstract column type (if any) + * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. + * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'. + * @return string the SQL statement for changing the definition of a column. + * @since 1.1.6 + */ + public function alterColumn($table, $column, $type) + { + return 'ALTER TABLE ' . $this->quoteTableName($table) . ' CHANGE ' + . $this->quoteColumnName($column) . ' ' + . $this->quoteColumnName($column) . ' ' + . $this->getColumnType($type); + } + + /** + * Builds a SQL statement for adding a foreign key constraint to an existing table. + * The method will properly quote the table and column names. + * @param string $name the name of the foreign key constraint. + * @param string $table the table that the foreign key constraint will be added to. + * @param string $columns the name of the column to that the constraint will be added on. If there are multiple columns, separate them with commas. + * @param string $refTable the table that the foreign key references to. + * @param string $refColumns the name of the column that the foreign key references to. If there are multiple columns, separate them with commas. + * @param string $delete the ON DELETE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL + * @param string $update the ON UPDATE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL + * @return string the SQL statement for adding a foreign key constraint to an existing table. + * @since 1.1.6 + */ + public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete=null, $update=null) + { + $columns=preg_split('/\s*,\s*/',$columns,-1,PREG_SPLIT_NO_EMPTY); + foreach($columns as $i=>$col) + $columns[$i]=$this->quoteColumnName($col); + $refColumns=preg_split('/\s*,\s*/',$refColumns,-1,PREG_SPLIT_NO_EMPTY); + foreach($refColumns as $i=>$col) + $refColumns[$i]=$this->quoteColumnName($col); + $sql='ALTER TABLE '.$this->quoteTableName($table) + .' ADD CONSTRAINT '.$this->quoteColumnName($name) + .' FOREIGN KEY ('.implode(', ', $columns).')' + .' REFERENCES '.$this->quoteTableName($refTable) + .' ('.implode(', ', $refColumns).')'; + if($delete!==null) + $sql.=' ON DELETE '.$delete; + if($update!==null) + $sql.=' ON UPDATE '.$update; + return $sql; + } + + /** + * Builds a SQL statement for dropping a foreign key constraint. + * @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted by the method. + * @param string $table the table whose foreign is to be dropped. The name will be properly quoted by the method. + * @return string the SQL statement for dropping a foreign key constraint. + * @since 1.1.6 + */ + public function dropForeignKey($name, $table) + { + return 'ALTER TABLE '.$this->quoteTableName($table) + .' DROP CONSTRAINT '.$this->quoteColumnName($name); + } + + /** + * Builds a SQL statement for creating a new index. + * @param string $name the name of the index. The name will be properly quoted by the method. + * @param string $table the table that the new index will be created for. The table name will be properly quoted by the method. + * @param string $column the column(s) that should be included in the index. If there are multiple columns, please separate them + * by commas. Each column name will be properly quoted by the method, unless a parenthesis is found in the name. + * @param boolean $unique whether to add UNIQUE constraint on the created index. + * @return string the SQL statement for creating a new index. + * @since 1.1.6 + */ + public function createIndex($name, $table, $column, $unique=false) + { + $cols=array(); + $columns=preg_split('/\s*,\s*/',$column,-1,PREG_SPLIT_NO_EMPTY); + foreach($columns as $col) + { + if(strpos($col,'(')!==false) + $cols[]=$col; + else + $cols[]=$this->quoteColumnName($col); + } + return ($unique ? 'CREATE UNIQUE INDEX ' : 'CREATE INDEX ') + . $this->quoteTableName($name).' ON ' + . $this->quoteTableName($table).' ('.implode(', ',$cols).')'; + } + + /** + * Builds a SQL statement for dropping an index. + * @param string $name the name of the index to be dropped. The name will be properly quoted by the method. + * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method. + * @return string the SQL statement for dropping an index. + * @since 1.1.6 + */ + public function dropIndex($name, $table) + { + return 'DROP INDEX '.$this->quoteTableName($name).' ON '.$this->quoteTableName($table); + } +} diff --git a/framework/db/schema/CDbTableSchema.php b/framework/db/schema/CDbTableSchema.php new file mode 100644 index 0000000..b70a2c8 --- /dev/null +++ b/framework/db/schema/CDbTableSchema.php @@ -0,0 +1,78 @@ +<?php +/** + * CDbTableSchema class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CDbTableSchema 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. + * + * CDbTableSchema provides the following information about a table: + * <ul> + * <li>{@link name}</li> + * <li>{@link rawName}</li> + * <li>{@link columns}</li> + * <li>{@link primaryKey}</li> + * <li>{@link foreignKeys}</li> + * <li>{@link sequenceName}</li> + * </ul> + * + * @property array $columnNames List of column names. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CDbTableSchema.php 3426 2011-10-25 00:01:09Z alexander.makarow $ + * @package system.db.schema + * @since 1.0 + */ +class CDbTableSchema extends CComponent +{ + /** + * @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 CDbColumnSchema 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 $name column name + * @return CDbColumnSchema 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/CMssqlColumnSchema.php b/framework/db/schema/mssql/CMssqlColumnSchema.php new file mode 100644 index 0000000..88dc654 --- /dev/null +++ b/framework/db/schema/mssql/CMssqlColumnSchema.php @@ -0,0 +1,72 @@ +<?php +/** + * CMssqlColumnSchema class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @author Christophe Boulain <Christophe.Boulain@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CMssqlColumnSchema class describes the column meta data of a MSSQL table. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @author Christophe Boulain <Christophe.Boulain@gmail.com> + * @version $Id: CMssqlColumnSchema.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.mssql + */ +class CMssqlColumnSchema extends CDbColumnSchema +{ + /** + * Extracts the PHP type from DB type. + * @param string $dbType DB type + */ + protected function extractType($dbType) + { + if(strpos($dbType,'float')!==false || strpos($dbType,'real')!==false) + $this->type='double'; + else if(strpos($dbType,'bigint')===false && (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'; + } + + /** + * Extracts the default value for the column. + * The value is typecasted to correct PHP type. + * @param mixed $defaultValue the default value obtained from metadata + */ + 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 $dbType the column's DB type + */ + protected function extractLimit($dbType) + { + } + + /** + * Converts the input value to the type that this column is of. + * @param mixed $value input value + * @return mixed converted value + */ + public function typecast($value) + { + if($this->type==='boolean') + return $value ? 1 : 0; + else + return parent::typecast($value); + } +} diff --git a/framework/db/schema/mssql/CMssqlCommandBuilder.php b/framework/db/schema/mssql/CMssqlCommandBuilder.php new file mode 100644 index 0000000..affaa7a --- /dev/null +++ b/framework/db/schema/mssql/CMssqlCommandBuilder.php @@ -0,0 +1,337 @@ +<?php +/** + * CMsCommandBuilder class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @author Christophe Boulain <Christophe.Boulain@gmail.com> + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CMssqlCommandBuilder provides basic methods to create query commands for tables for Mssql Servers. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @author Christophe Boulain <Christophe.Boulain@gmail.com> + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + * @version $Id: CMssqlCommandBuilder.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.mssql + */ +class CMssqlCommandBuilder extends CDbCommandBuilder +{ + /** + * Creates a COUNT(*) command for a single table. + * Override parent implementation to remove the order clause of criteria if it exists + * @param CDbTableSchema $table the table metadata + * @param CDbCriteria $criteria the query criteria + * @param string $alias the alias name of the primary table. Defaults to 't'. + * @return CDbCommand query command. + */ + public function createCountCommand($table,$criteria,$alias='t') + { + $criteria->order=''; + return parent::createCountCommand($table, $criteria,$alias); + } + + /** + * 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 CDbTableSchema $table the table metadata + * @param CDbCriteria $criteria the query criteria + * @param string $alias the alias name of the primary table. Defaults to 't'. + * @return CDbCommand query command. + */ + public function createFindCommand($table,$criteria,$alias='t') + { + $criteria=$this->checkCriteria($table,$criteria); + return parent::createFindCommand($table,$criteria,$alias); + + } + + /** + * Creates an UPDATE command. + * Override parent implementation because mssql don't want to update an identity column + * @param CDbTableSchema $table the table metadata + * @param array $data list of columns to be updated (name=>value) + * @param CDbCriteria $criteria the query criteria + * @return CDbCommand update command. + */ + public function createUpdateCommand($table,$data,$criteria) + { + $criteria=$this->checkCriteria($table,$criteria); + $fields=array(); + $values=array(); + $bindByPosition=isset($criteria->params[0]); + $i=0; + foreach($data as $name=>$value) + { + if(($column=$table->getColumn($name))!==null) + { + if ($table->sequenceName !== null && $column->isPrimaryKey === true) continue; + if ($column->dbType === 'timestamp') continue; + if($value instanceof CDbExpression) + { + $fields[]=$column->rawName.'='.$value->expression; + foreach($value->params as $n=>$v) + $values[$n]=$v; + } + 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 CDbException(Yii::t('yii','No columns are being updated for table "{table}".', + array('{table}'=>$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 CDbTableSchema $table the table metadata + * @param CDbCriteria $criteria the query criteria + * @return CDbCommand 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 CDbTableSchema $table the table metadata + * @param CDbCriteria $counters the query criteria + * @param array $criteria counters to be updated (counter increments/decrements indexed by column names.) + * @return CDbCommand 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 + * + * <code> + * 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) + * </code> + * + * <b>Regular expressions are used to alter the SQL query. The resulting SQL query + * may be malformed for complex queries.</b> The following restrictions apply + * + * <ul> + * <li> + * In particular, <b>commas</b> should <b>NOT</b> + * be used as part of the ordering expression or identifier. Commas must only be + * used for separating the ordering clauses. + * </li> + * <li> + * 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. + * </li> + * <li> + * No clauses should follow the ORDER BY clause, e.g. no COMPUTE or FOR clauses. + * </li> + * + * @param string $sql SQL query string. + * @param integer $limit maximum number of rows, -1 to ignore limit. + * @param integer $offset row offset, -1 to ignore offset. + * @return string SQL with limit and offset. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + */ + 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 sql query + * @param integer $limit $limit > 0 + * @param integer $offset $offset > 0 + * @return sql modified sql query applied with limit and offset. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + */ + 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, '[__outer__]'); + $reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering), '[__inner__]'); + $sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner__] {$reverseOrdering}) as [__outer__] {$orginalOrdering}"; + return $sql; + } + + /** + * Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx + * + * @param string $sql $sql + * @return array ordering expression as key and ordering direction as value + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + */ + 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) + { + $name=''; + foreach(explode('.', $subs[1][0]) as $p) + { + if($name!=='') + $name.='.'; + $name.='[' . trim($p, '[]') . ']'; + } + $ordering[$name] = $subs[2][0]; + } + //else what? + } + else + $ordering[trim($part)] = 'ASC'; + } + } + + // replacing column names with their alias names + foreach($ordering as $name => $direction) + { + $matches = array(); + $pattern = '/\s+'.str_replace(array('[',']'), array('\[','\]'), $name).'\s+AS\s+(\[[^\]]+\])/i'; + preg_match($pattern, $sql, $matches); + if(isset($matches[1])) + { + $ordering[$matches[1]] = $ordering[$name]; + unset($ordering[$name]); + } + } + + return $ordering; + } + + /** + * @param array $orders ordering obtained from findOrdering() + * @param string $newPrefix new table prefix to the ordering columns + * @return string concat the orderings + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + */ + protected function joinOrdering($orders, $newPrefix) + { + if(count($orders)>0) + { + $str=array(); + foreach($orders as $column => $direction) + $str[] = $column.' '.$direction; + $orderBy = 'ORDER BY '.implode(', ', $str); + return preg_replace('/\s+\[[^\]]+\]\.(\[[^\]]+\])/i', ' '.$newPrefix.'.\1', $orderBy); + } + } + + /** + * @param array $orders original ordering + * @return array ordering with reversed direction. + * + * @author Wei Zhuo <weizhuo[at]gmail[dot]com> + */ + 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 CMssqlTableSchema $table table schema + * @param CDbCriteria $criteria criteria + * @return CDbCrireria 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; + } + + /** + * Generates the expression for selecting rows with specified composite key values. + * @param CDbTableSchema $table the table schema + * @param array $values list of primary key values to be selected within + * @param string $prefix column prefix (ended with dot) + * @return string the expression for selection + */ + protected function createCompositeInCondition($table,$values,$prefix) + { + $vs=array(); + foreach($values as $value) + { + $c=array(); + foreach($value as $k=>$v) + $c[]=$prefix.$table->columns[$k]->rawName.'='.$v; + $vs[]='('.implode(' AND ',$c).')'; + } + return '('.implode(' OR ',$vs).')'; + } +} diff --git a/framework/db/schema/mssql/CMssqlPdoAdapter.php b/framework/db/schema/mssql/CMssqlPdoAdapter.php new file mode 100644 index 0000000..aca6343 --- /dev/null +++ b/framework/db/schema/mssql/CMssqlPdoAdapter.php @@ -0,0 +1,75 @@ +<?php +/** + * CMssqlPdo class file + * + * @author Christophe Boulain <Christophe.Boulain@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 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 <Christophe.Boulain@gmail.com> + * @version $Id: CMssqlPdoAdapter.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.mssql + */ +class CMssqlPdoAdapter extends PDO +{ + /** + * Get the last inserted id value + * MSSQL doesn't support sequence, so, argument is ignored + * + * @param string|null sequence name. Defaults to null + * @return integer last inserted id + */ + public function lastInsertId ($sequence=NULL) + { + $value=$this->query('SELECT SCOPE_IDENTITY()')->fetchColumn(); + $value=preg_replace('/[,.]0+$/', '', $value); // issue 2312 + return strtr($value,array(','=>'','.'=>'')); + } + + /** + * 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/CMssqlSchema.php b/framework/db/schema/mssql/CMssqlSchema.php new file mode 100644 index 0000000..89c4d8a --- /dev/null +++ b/framework/db/schema/mssql/CMssqlSchema.php @@ -0,0 +1,424 @@ +<?php +/** + * CMssqlSchema class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @author Christophe Boulain <Christophe.Boulain@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CMssqlSchema is the class for retrieving metadata information from a MS SQL Server database. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @author Christophe Boulain <Christophe.Boulain@gmail.com> + * @version $Id: CMssqlSchema.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.mssql + */ +class CMssqlSchema extends CDbSchema +{ + const DEFAULT_SCHEMA='dbo'; + + /** + * @var array the abstract column types mapped to physical column types. + * @since 1.1.6 + */ + public $columnTypes=array( + 'pk' => 'int IDENTITY PRIMARY KEY', + 'string' => 'varchar(255)', + 'text' => 'text', + 'integer' => 'int', + 'float' => 'float', + 'decimal' => 'decimal', + 'datetime' => 'datetime', + 'timestamp' => 'timestamp', + 'time' => 'time', + 'date' => 'date', + 'binary' => 'binary', + 'boolean' => 'bit', + ); + + /** + * Quotes a table name for use in a query. + * A simple table name does not schema prefix. + * @param string $name table name + * @return string the properly quoted table name + * @since 1.1.6 + */ + public function quoteSimpleTableName($name) + { + return '['.$name.']'; + } + + /** + * Quotes a column name for use in a query. + * A simple column name does not contain prefix. + * @param string $name column name + * @return string the properly quoted column name + * @since 1.1.6 + */ + public function quoteSimpleColumnName($name) + { + return '['.$name.']'; + } + + /** + * Compares two table names. + * The table names can be either quoted or unquoted. This method + * will consider both cases. + * @param string $name1 table name 1 + * @param string $name2 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); + return parent::compareTableNames(strtolower($name1),strtolower($name2)); + } + + /** + * Resets the sequence value of a table's primary key. + * The sequence will be reset such that the primary key of the next new row inserted + * will have the specified value or 1. + * @param CDbTableSchema $table the table schema whose primary key sequence will be reset + * @param mixed $value the value for the primary key of the next new row inserted. If this is not set, + * the next new row's primary key will have a value 1. + * @since 1.1.6 + */ + public function resetSequence($table,$value=null) + { + if($table->sequenceName!==null) + { + $db=$this->getDbConnection(); + if($value===null) + $value=$db->createCommand("SELECT MAX(`{$table->primaryKey}`) FROM {$table->rawName}")->queryScalar(); + $value=(int)$value; + $name=strtr($table->rawName,array('['=>'',']'=>'')); + $db->createCommand("DBCC CHECKIDENT ('$name', RESEED, $value)")->execute(); + } + } + + private $_normalTables=array(); // non-view tables + /** + * Enables or disables integrity check. + * @param boolean $check whether to turn on or off the integrity check. + * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema. + * @since 1.1.6 + */ + public function checkIntegrity($check=true,$schema='') + { + $enable=$check ? 'CHECK' : 'NOCHECK'; + if(!isset($this->_normalTables[$schema])) + $this->_normalTables[$schema]=$this->findTableNames($schema,false); + $db=$this->getDbConnection(); + foreach($this->_normalTables[$schema] as $tableName) + { + $tableName=$this->quoteTableName($tableName); + $db->createCommand("ALTER TABLE $tableName $enable CONSTRAINT ALL")->execute(); + } + } + + /** + * Loads the metadata for the specified table. + * @param string $name table name + * @return CMssqlTableSchema driver dependent table metadata. Null if the table does not exist. + */ + protected function loadTable($name) + { + $table=new CMssqlTableSchema; + $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 CMssqlTableSchema $table the table instance + * @param string $name 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 CMssqlTableSchema $table 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 = <<<EOD + SELECT k.column_name field_name + FROM {$this->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 CMssqlTableSchema $table 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 = <<<EOD + SELECT + KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME' + , KCU1.TABLE_NAME AS 'FK_TABLE_NAME' + , KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME' + , KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION' + , KCU2.CONSTRAINT_NAME AS 'UQ_CONSTRAINT_NAME' + , KCU2.TABLE_NAME AS 'UQ_TABLE_NAME' + , KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME' + , KCU2.ORDINAL_POSITION AS 'UQ_ORDINAL_POSITION' + FROM {$this->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 CMssqlTableSchema $table the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + $columnsTable="INFORMATION_SCHEMA.COLUMNS"; + $where=array(); + $where[]="TABLE_NAME='".$table->name."'"; + if (isset($table->catalogName)) + { + $where[]="TABLE_CATALOG='".$table->catalogName."'"; + $columnsTable = $table->catalogName.'.'.$columnsTable; + } + if (isset($table->schemaName)) + $where[]="TABLE_SCHEMA='".$table->schemaName."'"; + + $sql="SELECT *, columnproperty(object_id(table_schema+'.'+table_name), column_name, 'IsIdentity') as IsIdentity ". + "FROM ".$this->quoteTableName($columnsTable)." 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 ($c->autoIncrement && $table->sequenceName===null) + $table->sequenceName=$table->name; + } + return true; + } + + /** + * Creates a table column. + * @param array $column column metadata + * @return CDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new CMssqlColumnSchema; + $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->autoIncrement=$column['IsIdentity']==1; + + $c->init($column['DATA_TYPE'],$column['COLUMN_DEFAULT']); + return $c; + } + + /** + * Returns all table names in the database. + * @param string $schema 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. + * @param boolean $includeViews whether to include views in the result. Defaults to true. + * @return array all table names in the database. + */ + protected function findTableNames($schema='',$includeViews=true) + { + if($schema==='') + $schema=self::DEFAULT_SCHEMA; + if($includeViews) + $condition="TABLE_TYPE in ('BASE TABLE','VIEW')"; + else + $condition="TABLE_TYPE='BASE TABLE'"; + $sql=<<<EOD +SELECT TABLE_NAME, TABLE_SCHEMA FROM [INFORMATION_SCHEMA].[TABLES] +WHERE TABLE_SCHEMA=:schema AND $condition +EOD; + $command=$this->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 CDbCommandBuilder command builder instance + */ + protected function createCommandBuilder() + { + return new CMssqlCommandBuilder($this); + } + + /** + * Builds a SQL statement for renaming a DB table. + * @param string $table the table to be renamed. The name will be properly quoted by the method. + * @param string $newName the new table name. The name will be properly quoted by the method. + * @return string the SQL statement for renaming a DB table. + * @since 1.1.6 + */ + public function renameTable($table, $newName) + { + return "sp_rename '$table', '$newName'"; + } + + /** + * Builds a SQL statement for renaming a column. + * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method. + * @param string $name the old name of the column. The name will be properly quoted by the method. + * @param string $newName the new name of the column. The name will be properly quoted by the method. + * @return string the SQL statement for renaming a DB column. + * @since 1.1.6 + */ + public function renameColumn($table, $name, $newName) + { + return "sp_rename '$table.$name', '$newName', 'COLUMN'"; + } + + /** + * Builds a SQL statement for changing the definition of a column. + * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method. + * @param string $column the name of the column to be changed. The name will be properly quoted by the method. + * @param string $type the new column type. The {@link getColumnType} method will be invoked to convert abstract column type (if any) + * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. + * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'. + * @return string the SQL statement for changing the definition of a column. + * @since 1.1.6 + */ + public function alterColumn($table, $column, $type) + { + $type=$this->getColumnType($type); + $sql='ALTER TABLE ' . $this->quoteTableName($table) . ' ALTER COLUMN ' + . $this->quoteColumnName($column) . ' ' + . $this->getColumnType($type); + return $sql; + } +} diff --git a/framework/db/schema/mssql/CMssqlTableSchema.php b/framework/db/schema/mssql/CMssqlTableSchema.php new file mode 100644 index 0000000..6f0d137 --- /dev/null +++ b/framework/db/schema/mssql/CMssqlTableSchema.php @@ -0,0 +1,32 @@ +<?php +/** + * CMssqlTableSchema class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @author Christophe Boulain <Christophe.Boulain@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CMssqlTableSchema represents the metadata for a MSSQL table. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @author Christophe Boulain <Christophe.Boulain@gmail.com> + * @version $Id: CMssqlTableSchema.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.mssql + */ +class CMssqlTableSchema extends CDbTableSchema +{ + /** + * @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/CMysqlColumnSchema.php b/framework/db/schema/mysql/CMysqlColumnSchema.php new file mode 100644 index 0000000..7ffb03d --- /dev/null +++ b/framework/db/schema/mysql/CMysqlColumnSchema.php @@ -0,0 +1,72 @@ +<?php +/** + * CMysqlColumnSchema class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CMysqlColumnSchema class describes the column meta data of a MySQL table. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CMysqlColumnSchema.php 3204 2011-05-05 21:36:32Z alexander.makarow $ + * @package system.db.schema.mysql + * @since 1.0 + */ +class CMysqlColumnSchema extends CDbColumnSchema +{ + /** + * Extracts the PHP type from DB type. + * @param string $dbType DB type + */ + protected function extractType($dbType) + { + if(strncmp($dbType,'enum',4)===0) + $this->type='string'; + else if(strpos($dbType,'float')!==false || strpos($dbType,'double')!==false) + $this->type='double'; + else if(strpos($dbType,'bool')!==false) + $this->type='boolean'; + else if(strpos($dbType,'int')===0 && strpos($dbType,'unsigned')===false || preg_match('/(bit|tinyint|smallint|mediumint)/',$dbType)) + $this->type='integer'; + else + $this->type='string'; + } + + /** + * Extracts the default value for the column. + * The value is typecasted to correct PHP type. + * @param mixed $defaultValue the default value obtained from metadata + */ + protected function extractDefault($defaultValue) + { + if($this->dbType==='timestamp' && $defaultValue==='CURRENT_TIMESTAMP') + $this->defaultValue=null; + else + parent::extractDefault($defaultValue); + } + + /** + * Extracts size, precision and scale information from column's DB type. + * @param string $dbType the column's DB type + */ + protected function extractLimit($dbType) + { + if (strncmp($dbType, 'enum', 4)===0 && preg_match('/\((.*)\)/',$dbType,$matches)) + { + $values = explode(',', $matches[1]); + $size = 0; + foreach($values as $value) + { + if(($n=strlen($value)) > $size) + $size=$n; + } + $this->size = $this->precision = $size-2; + } + else + parent::extractLimit($dbType); + } +}
\ No newline at end of file diff --git a/framework/db/schema/mysql/CMysqlSchema.php b/framework/db/schema/mysql/CMysqlSchema.php new file mode 100644 index 0000000..4048b4b --- /dev/null +++ b/framework/db/schema/mysql/CMysqlSchema.php @@ -0,0 +1,309 @@ +<?php +/** + * CMysqlSchema class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CMysqlSchema is the class for retrieving metadata information from a MySQL database (version 4.1.x and 5.x). + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CMysqlSchema.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.mysql + * @since 1.0 + */ +class CMysqlSchema extends CDbSchema +{ + /** + * @var array the abstract column types mapped to physical column types. + * @since 1.1.6 + */ + public $columnTypes=array( + 'pk' => 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY', + 'string' => 'varchar(255)', + 'text' => 'text', + 'integer' => 'int(11)', + 'float' => 'float', + 'decimal' => 'decimal', + 'datetime' => 'datetime', + 'timestamp' => 'timestamp', + 'time' => 'time', + 'date' => 'date', + 'binary' => 'blob', + 'boolean' => 'tinyint(1)', + 'money' => 'decimal(19,4)', + ); + + /** + * Quotes a table name for use in a query. + * A simple table name does not schema prefix. + * @param string $name table name + * @return string the properly quoted table name + * @since 1.1.6 + */ + public function quoteSimpleTableName($name) + { + return '`'.$name.'`'; + } + + /** + * Quotes a column name for use in a query. + * A simple column name does not contain prefix. + * @param string $name column name + * @return string the properly quoted column name + * @since 1.1.6 + */ + public function quoteSimpleColumnName($name) + { + return '`'.$name.'`'; + } + + /** + * Compares two table names. + * The table names can be either quoted or unquoted. This method + * will consider both cases. + * @param string $name1 table name 1 + * @param string $name2 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)); + } + + /** + * Resets the sequence value of a table's primary key. + * The sequence will be reset such that the primary key of the next new row inserted + * will have the specified value or 1. + * @param CDbTableSchema $table the table schema whose primary key sequence will be reset + * @param mixed $value the value for the primary key of the next new row inserted. If this is not set, + * the next new row's primary key will have a value 1. + * @since 1.1 + */ + public function resetSequence($table,$value=null) + { + if($table->sequenceName!==null) + { + if($value===null) + $value=$this->getDbConnection()->createCommand("SELECT MAX(`{$table->primaryKey}`) FROM {$table->rawName}")->queryScalar()+1; + else + $value=(int)$value; + $this->getDbConnection()->createCommand("ALTER TABLE {$table->rawName} AUTO_INCREMENT=$value")->execute(); + } + } + + /** + * Enables or disables integrity check. + * @param boolean $check whether to turn on or off the integrity check. + * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema. + * @since 1.1 + */ + public function checkIntegrity($check=true,$schema='') + { + $this->getDbConnection()->createCommand('SET FOREIGN_KEY_CHECKS='.($check?1:0))->execute(); + } + + /** + * Loads the metadata for the specified table. + * @param string $name table name + * @return CMysqlTableSchema driver dependent table metadata. Null if the table does not exist. + */ + protected function loadTable($name) + { + $table=new CMysqlTableSchema; + $this->resolveTableNames($table,$name); + + if($this->findColumns($table)) + { + $this->findConstraints($table); + return $table; + } + else + return null; + } + + /** + * Generates various kinds of table names. + * @param CMysqlTableSchema $table the table instance + * @param string $name 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 CMysqlTableSchema $table 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($c->autoIncrement) + $table->sequenceName=''; + } + } + return true; + } + + /** + * Creates a table column. + * @param array $column column metadata + * @return CDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new CMysqlColumnSchema; + $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']); + $c->autoIncrement=strpos(strtolower($column['Extra']),'auto_increment')!==false; + + 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 CMysqlTableSchema $table 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; + } + 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. + * @param string $schema 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. + */ + 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; + } + + /** + * Builds a SQL statement for renaming a column. + * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method. + * @param string $name the old name of the column. The name will be properly quoted by the method. + * @param string $newName the new name of the column. The name will be properly quoted by the method. + * @return string the SQL statement for renaming a DB column. + * @since 1.1.6 + */ + public function renameColumn($table, $name, $newName) + { + $db=$this->getDbConnection(); + $row=$db->createCommand('SHOW CREATE TABLE '.$db->quoteTableName($table))->queryRow(); + if($row===false) + throw new CDbException(Yii::t('yii','Unable to find "{column}" in table "{table}".',array('{column}'=>$name,'{table}'=>$table))); + if(isset($row['Create Table'])) + $sql=$row['Create Table']; + else + { + $row=array_values($row); + $sql=$row[1]; + } + if(preg_match_all('/^\s*`(.*?)`\s+(.*?),?$/m',$sql,$matches)) + { + foreach($matches[1] as $i=>$c) + { + if($c===$name) + { + return "ALTER TABLE ".$db->quoteTableName($table) + . " CHANGE ".$db->quoteColumnName($name) + . ' '.$db->quoteColumnName($newName).' '.$matches[2][$i]; + } + } + } + + // try to give back a SQL anyway + return "ALTER TABLE ".$db->quoteTableName($table) + . " CHANGE ".$db->quoteColumnName($name).' '.$newName; + } + + /** + * Builds a SQL statement for dropping a foreign key constraint. + * @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted by the method. + * @param string $table the table whose foreign is to be dropped. The name will be properly quoted by the method. + * @return string the SQL statement for dropping a foreign key constraint. + * @since 1.1.6 + */ + public function dropForeignKey($name, $table) + { + return 'ALTER TABLE '.$this->quoteTableName($table) + .' DROP FOREIGN KEY '.$this->quoteColumnName($name); + } +} diff --git a/framework/db/schema/mysql/CMysqlTableSchema.php b/framework/db/schema/mysql/CMysqlTableSchema.php new file mode 100644 index 0000000..9950598 --- /dev/null +++ b/framework/db/schema/mysql/CMysqlTableSchema.php @@ -0,0 +1,26 @@ +<?php +/** + * CMysqlTableSchema class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CMysqlTableSchema represents the metadata for a MySQL table. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CMysqlTableSchema.php 2799 2011-01-01 19:31:13Z qiang.xue $ + * @package system.db.schema.mysql + * @since 1.0 + */ +class CMysqlTableSchema extends CDbTableSchema +{ + /** + * @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/COciColumnSchema.php b/framework/db/schema/oci/COciColumnSchema.php new file mode 100644 index 0000000..bad57c3 --- /dev/null +++ b/framework/db/schema/oci/COciColumnSchema.php @@ -0,0 +1,66 @@ +<?php +/** + * COciColumnSchema class file. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * COciColumnSchema class describes the column meta data of a Oracle table. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @version $Id: COciColumnSchema.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.oci + */ +class COciColumnSchema extends CDbColumnSchema +{ + /** + * Extracts the PHP type from DB type. + * @param string $dbType DB type + * @return string + */ + protected function extractOraType($dbType){ + if(strpos($dbType,'FLOAT')!==false) return 'double'; + + if (strpos($dbType,'NUMBER')!==false || 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 'double'; + } + else + return 'string'; + } + + /** + * Extracts the PHP type from DB type. + * @param string $dbType DB type + */ + protected function extractType($dbType) + { + $this->type=$this->extractOraType($dbType); + } + + /** + * Extracts the default value for the column. + * The value is typecasted to correct PHP type. + * @param mixed $defaultValue the default value obtained from metadata + */ + protected function extractDefault($defaultValue) + { + if(stripos($defaultValue,'timestamp')!==false) + $this->defaultValue=null; + else + parent::extractDefault($defaultValue); + } +} diff --git a/framework/db/schema/oci/COciCommandBuilder.php b/framework/db/schema/oci/COciCommandBuilder.php new file mode 100644 index 0000000..4c041be --- /dev/null +++ b/framework/db/schema/oci/COciCommandBuilder.php @@ -0,0 +1,125 @@ +<?php +/** + * COciCommandBuilder class file. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * COciCommandBuilder provides basic methods to create query commands for tables. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @version $Id: COciCommandBuilder.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.oci + */ +class COciCommandBuilder extends CDbCommandBuilder +{ + /** + * @var integer the last insertion ID + */ + public $returnID; + + /** + * Returns the last insertion ID for the specified table. + * @param mixed $table the table schema ({@link CDbTableSchema}) 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 SQL query string without LIMIT and OFFSET. + * @param integer $limit maximum number of rows, -1 to ignore limit. + * @param integer $offset 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 = <<<EOD + WITH USER_SQL AS ({$sql}), + PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL) + SELECT * + FROM PAGINATION + {$filter} +EOD; + + return $sql; + } + + /** + * Creates an INSERT command. + * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string). + * @param array $data data to be inserted (column name=>column value). If a key is not a valid column name, the corresponding value will be ignored. + * @return CDbCommand 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 CDbExpression) + { + $placeholders[]=$value->expression; + foreach($value->params as $n=>$v) + $values[$n]=$v; + } + 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).')'; + + if(is_string($table->primaryKey) && ($column=$table->getColumn($table->primaryKey))!==null && $column->type!=='string') + { + $sql.=' RETURNING '.$column->rawName.' 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; + } +}
\ No newline at end of file diff --git a/framework/db/schema/oci/COciSchema.php b/framework/db/schema/oci/COciSchema.php new file mode 100644 index 0000000..90ccaa9 --- /dev/null +++ b/framework/db/schema/oci/COciSchema.php @@ -0,0 +1,350 @@ +<?php +/** + * COciSchema class file. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * COciSchema is the class for retrieving metadata information from an Oracle database. + * + * @property string $defaultSchema Default schema. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @version $Id: COciSchema.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.oci + */ +class COciSchema extends CDbSchema +{ + private $_defaultSchema = ''; + + /** + * @var array the abstract column types mapped to physical column types. + * @since 1.1.6 + */ + public $columnTypes=array( + 'pk' => 'NUMBER(10) NOT NULL PRIMARY KEY', + 'string' => 'VARCHAR2(255)', + 'text' => 'CLOB', + 'integer' => 'NUMBER(10)', + 'float' => 'NUMBER', + 'decimal' => 'NUMBER', + 'datetime' => 'TIMESTAMP', + 'timestamp' => 'TIMESTAMP', + 'time' => 'TIMESTAMP', + 'date' => 'DATE', + 'binary' => 'BLOB', + 'boolean' => 'NUMBER(1)', + 'money' => 'NUMBER(19,4)', + ); + + /** + * Quotes a table name for use in a query. + * A simple table name does not schema prefix. + * @param string $name table name + * @return string the properly quoted table name + * @since 1.1.6 + */ + public function quoteSimpleTableName($name) + { + return '"'.$name.'"'; + } + + /** + * Quotes a column name for use in a query. + * A simple column name does not contain prefix. + * @param string $name column name + * @return string the properly quoted column name + * @since 1.1.6 + */ + public function quoteSimpleColumnName($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 CDbCommandBuilder command builder instance + */ + protected function createCommandBuilder() + { + return new COciCommandBuilder($this); + } + + /** + * @param string $schema 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 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]); + } + + /** + * Loads the metadata for the specified table. + * @param string $name table name + * @return CDbTableSchema driver dependent table metadata. + */ + protected function loadTable($name) + { + $table=new COciTableSchema; + $this->resolveTableNames($table,$name); + + if(!$this->findColumns($table)) + return null; + $this->findConstraints($table); + + return $table; + } + + /** + * Generates various kinds of table names. + * @param COciTableSchema $table the table instance + * @param string $name 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 COciTableSchema $table the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + $schemaName=$table->schemaName; + $tableName=$table->name; + + $sql=<<<EOD +SELECT a.column_name, a.data_type || + case + when data_precision is not null + then '(' || a.data_precision || + case when a.data_scale > 0 then ',' || a.data_scale else '' end + || ')' + when data_type = 'DATE' then '' + when data_type = 'NUMBER' 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' or b.object_type = 'VIEW') + 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; + $table->sequenceName=''; + $c->autoIncrement=true; + } + } + return true; + } + + /** + * Creates a table column. + * @param array $column column metadata + * @return CDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new COciColumnSchema; + $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 COciTableSchema $table the table metadata + */ + protected function findConstraints($table) + { + $sql=<<<EOD + SELECT D.constraint_type as CONSTRAINT_TYPE, C.COLUMN_NAME, C.position, D.r_constraint_name, + E.table_name as table_ref, f.column_name as column_ref, + C.table_name + FROM ALL_CONS_COLUMNS C + inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name + left join ALL_constraints E on E.OWNER = D.r_OWNER and E.constraint_name = D.r_constraint_name + left join ALL_cons_columns F on F.OWNER = E.OWNER and F.constraint_name = E.constraint_name and F.position = c.position + WHERE C.OWNER = '{$table->schemaName}' + and C.table_name = '{$table->name}' + and D.constraint_type <> '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"], $row["COLUMN_REF"]); + if(isset($table->columns[$name])) + $table->columns[$name]->isForeignKey=true; + } + + } + } + + /** + * Returns all table names in the database. + * @param string $schema 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. + */ + protected function findTableNames($schema='') + { + if($schema==='') + { + $sql=<<<EOD +SELECT table_name, '{$schema}' as table_schema FROM user_tables +EOD; + $command=$this->getDbConnection()->createCommand($sql); + } + else + { + $sql=<<<EOD +SELECT object_name as table_name, owner as table_schema FROM all_objects +WHERE object_type = 'TABLE' AND owner=:schema +EOD; + $command=$this->getDbConnection()->createCommand($sql); + $command->bindParam(':schema',$schema); + } + + $rows=$command->queryAll(); + $names=array(); + foreach($rows as $row) + { + if($schema===$this->getDefaultSchema() || $schema==='') + $names[]=$row['TABLE_NAME']; + else + $names[]=$row['TABLE_SCHEMA'].'.'.$row['TABLE_NAME']; + } + return $names; + } + + /** + * Builds a SQL statement for renaming a DB table. + * @param string $table the table to be renamed. The name will be properly quoted by the method. + * @param string $newName the new table name. The name will be properly quoted by the method. + * @return string the SQL statement for renaming a DB table. + * @since 1.1.6 + */ + public function renameTable($table, $newName) + { + return 'ALTER TABLE ' . $this->quoteTableName($table) . ' RENAME TO ' . $this->quoteTableName($newName); + } + + /** + * Builds a SQL statement for changing the definition of a column. + * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method. + * @param string $column the name of the column to be changed. The name will be properly quoted by the method. + * @param string $type the new column type. The {@link getColumnType} method will be invoked to convert abstract column type (if any) + * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. + * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'. + * @return string the SQL statement for changing the definition of a column. + * @since 1.1.6 + */ + public function alterColumn($table, $column, $type) + { + $type=$this->getColumnType($type); + $sql='ALTER TABLE ' . $this->quoteTableName($table) . ' MODIFY ' + . $this->quoteColumnName($column) . ' ' + . $this->getColumnType($type); + return $sql; + } + + /** + * Builds a SQL statement for dropping an index. + * @param string $name the name of the index to be dropped. The name will be properly quoted by the method. + * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method. + * @return string the SQL statement for dropping an index. + * @since 1.1.6 + */ + public function dropIndex($name, $table) + { + return 'DROP INDEX '.$this->quoteTableName($name); + } +} diff --git a/framework/db/schema/oci/COciTableSchema.php b/framework/db/schema/oci/COciTableSchema.php new file mode 100644 index 0000000..8892745 --- /dev/null +++ b/framework/db/schema/oci/COciTableSchema.php @@ -0,0 +1,25 @@ +<?php +/** + * COciTableSchema class file. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * COciTableSchema represents the metadata for a Oracle table. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @version $Id: COciTableSchema.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.oci + */ +class COciTableSchema extends CDbTableSchema +{ + /** + * @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/CPgsqlColumnSchema.php b/framework/db/schema/pgsql/CPgsqlColumnSchema.php new file mode 100644 index 0000000..45b6f56 --- /dev/null +++ b/framework/db/schema/pgsql/CPgsqlColumnSchema.php @@ -0,0 +1,58 @@ +<?php +/** + * CPgsqlColumnSchema class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CPgsqlColumnSchema class describes the column meta data of a PostgreSQL table. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CPgsqlColumnSchema.php 2799 2011-01-01 19:31:13Z qiang.xue $ + * @package system.db.schema.pgsql + * @since 1.0 + */ +class CPgsqlColumnSchema extends CDbColumnSchema +{ + /** + * Extracts the PHP type from DB type. + * @param string $dbType DB type + */ + protected function extractType($dbType) + { + if(strpos($dbType,'[')!==false || strpos($dbType,'char')!==false || strpos($dbType,'text')!==false) + $this->type='string'; + else if(strpos($dbType,'bool')!==false) + $this->type='boolean'; + else if(preg_match('/(real|float|double)/',$dbType)) + $this->type='double'; + else if(preg_match('/(integer|oid|serial|smallint)/',$dbType)) + $this->type='integer'; + else + $this->type='string'; + } + + /** + * Extracts the default value for the column. + * The value is typecasted to correct PHP type. + * @param mixed $defaultValue 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/CPgsqlSchema.php b/framework/db/schema/pgsql/CPgsqlSchema.php new file mode 100644 index 0000000..bff95e0 --- /dev/null +++ b/framework/db/schema/pgsql/CPgsqlSchema.php @@ -0,0 +1,424 @@ +<?php +/** + * CPgsqlSchema class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CPgsqlSchema is the class for retrieving metadata information from a PostgreSQL database. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CPgsqlSchema.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.pgsql + * @since 1.0 + */ +class CPgsqlSchema extends CDbSchema +{ + const DEFAULT_SCHEMA='public'; + + /** + * @var array the abstract column types mapped to physical column types. + * @since 1.1.6 + */ + public $columnTypes=array( + 'pk' => 'serial NOT NULL PRIMARY KEY', + 'string' => 'character varying (255)', + 'text' => 'text', + 'integer' => 'integer', + 'float' => 'double precision', + 'decimal' => 'numeric', + 'datetime' => 'time', + 'timestamp' => 'timestamp', + 'time' => 'time', + 'date' => 'date', + 'binary' => 'bytea', + 'boolean' => 'boolean', + 'money' => 'decimal(19,4)', + ); + + private $_sequences=array(); + + /** + * Quotes a table name for use in a query. + * A simple table name does not schema prefix. + * @param string $name table name + * @return string the properly quoted table name + * @since 1.1.6 + */ + public function quoteSimpleTableName($name) + { + return '"'.$name.'"'; + } + + /** + * Resets the sequence value of a table's primary key. + * The sequence will be reset such that the primary key of the next new row inserted + * will have the specified value or 1. + * @param CDbTableSchema $table the table schema whose primary key sequence will be reset + * @param mixed $value the value for the primary key of the next new row inserted. If this is not set, + * the next new row's primary key will have a value 1. + * @since 1.1 + */ + public function resetSequence($table,$value=null) + { + if($table->sequenceName!==null) + { + $seq='"'.$table->sequenceName.'"'; + if(strpos($seq,'.')!==false) + $seq=str_replace('.','"."',$seq); + if($value===null) + $value="(SELECT COALESCE(MAX(\"{$table->primaryKey}\"),0) FROM {$table->rawName}) + 1"; + else + $value=(int)$value; + $this->getDbConnection()->createCommand("SELECT SETVAL('$seq', $value, false)")->execute(); + } + } + + /** + * Enables or disables integrity check. + * @param boolean $check whether to turn on or off the integrity check. + * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema. + * @since 1.1 + */ + public function checkIntegrity($check=true,$schema='') + { + $enable=$check ? 'ENABLE' : 'DISABLE'; + $tableNames=$this->getTableNames($schema); + $db=$this->getDbConnection(); + foreach($tableNames as $tableName) + { + $tableName='"'.$tableName.'"'; + if(strpos($tableName,'.')!==false) + $tableName=str_replace('.','"."',$tableName); + $db->createCommand("ALTER TABLE $tableName $enable TRIGGER ALL")->execute(); + } + } + + /** + * Loads the metadata for the specified table. + * @param string $name table name + * @return CDbTableSchema driver dependent table metadata. + */ + protected function loadTable($name) + { + $table=new CPgsqlTableSchema; + $this->resolveTableNames($table,$name); + if(!$this->findColumns($table)) + return null; + $this->findConstraints($table); + + if(is_string($table->primaryKey) && isset($this->_sequences[$table->rawName.'.'.$table->primaryKey])) + $table->sequenceName=$this->_sequences[$table->rawName.'.'.$table->primaryKey]; + else if(is_array($table->primaryKey)) + { + foreach($table->primaryKey as $pk) + { + if(isset($this->_sequences[$table->rawName.'.'.$pk])) + { + $table->sequenceName=$this->_sequences[$table->rawName.'.'.$pk]; + break; + } + } + } + + return $table; + } + + /** + * Generates various kinds of table names. + * @param CPgsqlTableSchema $table the table instance + * @param string $name 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 CPgsqlTableSchema $table the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + $sql=<<<EOD +SELECT a.attname, LOWER(format_type(a.atttypid, a.atttypmod)) AS type, d.adsrc, a.attnotnull, a.atthasdef +FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum +WHERE a.attnum > 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[$table->rawName.'.'.$c->name]=$matches[1]; + else + $this->_sequences[$table->rawName.'.'.$c->name]=$table->schemaName.'.'.$matches[1]; + $c->autoIncrement=true; + } + } + return true; + } + + /** + * Creates a table column. + * @param array $column column metadata + * @return CDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new CPgsqlColumnSchema; + $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 CPgsqlTableSchema $table the table metadata + */ + protected function findConstraints($table) + { + $sql=<<<EOD +SELECT conname, consrc, contype, indkey FROM ( + SELECT + conname, + CASE WHEN contype='f' THEN + pg_catalog.pg_get_constraintdef(oid) + ELSE + 'CHECK (' || consrc || ')' + END AS consrc, + contype, + conrelid AS relid, + NULL AS indkey + FROM + pg_catalog.pg_constraint + WHERE + contype IN ('f', 'c') + UNION ALL + SELECT + pc.relname, + NULL, + CASE WHEN indisprimary THEN + 'p' + ELSE + 'u' + END, + pi.indrelid, + indkey + FROM + pg_catalog.pg_class pc, + pg_catalog.pg_index pi + WHERE + pc.oid=pi.indexrelid + AND EXISTS ( + SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c + ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) + WHERE d.classid = pc.tableoid AND d.objid = pc.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p') + ) +) AS sub +WHERE relid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table + AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace + WHERE nspname=:schema)) +EOD; + $command=$this->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 CPgsqlTableSchema $table the table metadata + * @param string $indices pgsql primary key index list + */ + protected function findPrimaryKey($table,$indices) + { + $indices=implode(', ',preg_split('/\s+/',$indices)); + $sql=<<<EOD +SELECT attnum, attname FROM pg_catalog.pg_attribute WHERE + attrelid=( + SELECT oid FROM pg_catalog.pg_class WHERE relname=:table AND relnamespace=( + SELECT oid FROM pg_catalog.pg_namespace WHERE nspname=:schema + ) + ) + AND attnum IN ({$indices}) +EOD; + $command=$this->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 CPgsqlTableSchema $table the table metadata + * @param string $src 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. + * @param string $schema 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. + */ + protected function findTableNames($schema='') + { + if($schema==='') + $schema=self::DEFAULT_SCHEMA; + $sql=<<<EOD +SELECT table_name, table_schema FROM information_schema.tables +WHERE table_schema=:schema AND table_type='BASE TABLE' +EOD; + $command=$this->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['table_schema'].'.'.$row['table_name']; + } + return $names; + } + + /** + * Builds a SQL statement for renaming a DB table. + * @param string $table the table to be renamed. The name will be properly quoted by the method. + * @param string $newName the new table name. The name will be properly quoted by the method. + * @return string the SQL statement for renaming a DB table. + * @since 1.1.6 + */ + public function renameTable($table, $newName) + { + return 'ALTER TABLE ' . $this->quoteTableName($table) . ' RENAME TO ' . $this->quoteTableName($newName); + } + + /** + * Builds a SQL statement for adding a new DB column. + * @param string $table the table that the new column will be added to. The table name will be properly quoted by the method. + * @param string $column the name of the new column. The name will be properly quoted by the method. + * @param string $type the column type. The {@link getColumnType} method will be invoked to convert abstract column type (if any) + * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. + * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'. + * @return string the SQL statement for adding a new column. + * @since 1.1.6 + */ + public function addColumn($table, $column, $type) + { + $type=$this->getColumnType($type); + $sql='ALTER TABLE ' . $this->quoteTableName($table) + . ' ADD COLUMN ' . $this->quoteColumnName($column) . ' ' + . $this->getColumnType($type); + return $sql; + } + + /** + * Builds a SQL statement for changing the definition of a column. + * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method. + * @param string $column the name of the column to be changed. The name will be properly quoted by the method. + * @param string $type the new column type. The {@link getColumnType} method will be invoked to convert abstract column type (if any) + * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. + * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'. + * @return string the SQL statement for changing the definition of a column. + * @since 1.1.6 + */ + public function alterColumn($table, $column, $type) + { + $type=$this->getColumnType($type); + $sql='ALTER TABLE ' . $this->quoteTableName($table) . ' ALTER COLUMN ' + . $this->quoteColumnName($column) . ' TYPE ' . $this->getColumnType($type); + return $sql; + } + + /** + * Builds a SQL statement for dropping an index. + * @param string $name the name of the index to be dropped. The name will be properly quoted by the method. + * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method. + * @return string the SQL statement for dropping an index. + * @since 1.1.6 + */ + public function dropIndex($name, $table) + { + return 'DROP INDEX '.$this->quoteTableName($name); + } +} diff --git a/framework/db/schema/pgsql/CPgsqlTableSchema.php b/framework/db/schema/pgsql/CPgsqlTableSchema.php new file mode 100644 index 0000000..39e0334 --- /dev/null +++ b/framework/db/schema/pgsql/CPgsqlTableSchema.php @@ -0,0 +1,25 @@ +<?php +/** + * CPgsqlTable class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CPgsqlTable represents the metadata for a PostgreSQL table. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CPgsqlTableSchema.php 2799 2011-01-01 19:31:13Z qiang.xue $ + * @package system.db.schema.pgsql + * @since 1.0 + */ +class CPgsqlTableSchema extends CDbTableSchema +{ + /** + * @var string name of the schema that this table belongs to. + */ + public $schemaName; +} diff --git a/framework/db/schema/sqlite/CSqliteColumnSchema.php b/framework/db/schema/sqlite/CSqliteColumnSchema.php new file mode 100644 index 0000000..ae51434 --- /dev/null +++ b/framework/db/schema/sqlite/CSqliteColumnSchema.php @@ -0,0 +1,33 @@ +<?php +/** + * CSqliteColumnSchema class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CSqliteColumnSchema class describes the column meta data of a SQLite table. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CSqliteColumnSchema.php 2799 2011-01-01 19:31:13Z qiang.xue $ + * @package system.db.schema.sqlite + * @since 1.0 + */ +class CSqliteColumnSchema extends CDbColumnSchema +{ + /** + * Extracts the default value for the column. + * The value is typecasted to correct PHP type. + * @param mixed $defaultValue 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(strcasecmp($defaultValue,'null') ? $defaultValue : null); + } +} diff --git a/framework/db/schema/sqlite/CSqliteCommandBuilder.php b/framework/db/schema/sqlite/CSqliteCommandBuilder.php new file mode 100644 index 0000000..c4bf75a --- /dev/null +++ b/framework/db/schema/sqlite/CSqliteCommandBuilder.php @@ -0,0 +1,40 @@ +<?php +/** + * CSqliteCommandBuilder class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CSqliteCommandBuilder provides basic methods to create query commands for SQLite tables. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CSqliteCommandBuilder.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.sqlite + * @since 1.0 + */ +class CSqliteCommandBuilder extends CDbCommandBuilder +{ + /** + * 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 CDbTableSchema $table the table schema + * @param array $values list of primary key values to be selected within + * @param string $prefix column prefix (ended with dot) + * @return string the expression for selection + */ + 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/CSqliteSchema.php b/framework/db/schema/sqlite/CSqliteSchema.php new file mode 100644 index 0000000..c1c9d76 --- /dev/null +++ b/framework/db/schema/sqlite/CSqliteSchema.php @@ -0,0 +1,287 @@ +<?php +/** + * CSqliteSchema class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * CSqliteSchema is the class for retrieving metadata information from a SQLite (2/3) database. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: CSqliteSchema.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.sqlite + * @since 1.0 + */ +class CSqliteSchema extends CDbSchema +{ + /** + * @var array the abstract column types mapped to physical column types. + * @since 1.1.6 + */ + public $columnTypes=array( + 'pk' => 'integer PRIMARY KEY AUTOINCREMENT NOT NULL', + 'string' => 'varchar(255)', + 'text' => 'text', + 'integer' => 'integer', + 'float' => 'float', + 'decimal' => 'decimal', + 'datetime' => 'datetime', + 'timestamp' => 'timestamp', + 'time' => 'time', + 'date' => 'date', + 'binary' => 'blob', + 'boolean' => 'tinyint(1)', + 'money' => 'decimal(19,4)', + ); + + /** + * Resets the sequence value of a table's primary key. + * The sequence will be reset such that the primary key of the next new row inserted + * will have the specified value or 1. + * @param CDbTableSchema $table the table schema whose primary key sequence will be reset + * @param mixed $value the value for the primary key of the next new row inserted. If this is not set, + * the next new row's primary key will have a value 1. + * @since 1.1 + */ + public function resetSequence($table,$value=null) + { + if($table->sequenceName!==null) + { + if($value===null) + $value=$this->getDbConnection()->createCommand("SELECT MAX(`{$table->primaryKey}`) FROM {$table->rawName}")->queryScalar(); + else + $value=(int)$value-1; + try + { + // it's possible sqlite_sequence does not exist + $this->getDbConnection()->createCommand("UPDATE sqlite_sequence SET seq='$value' WHERE name='{$table->name}'")->execute(); + } + catch(Exception $e) + { + } + } + } + + /** + * Enables or disables integrity check. + * @param boolean $check whether to turn on or off the integrity check. + * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema. + * @since 1.1 + */ + public function checkIntegrity($check=true,$schema='') + { + // SQLite doesn't enforce integrity + return; + } + + /** + * Returns all table names in the database. + * @param string $schema the schema of the tables. This is not used for sqlite database. + * @return array all table names in the database. + */ + 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 CSqliteCommandBuilder command builder instance + */ + protected function createCommandBuilder() + { + return new CSqliteCommandBuilder($this); + } + + /** + * Loads the metadata for the specified table. + * @param string $name table name + * @return CDbTableSchema driver dependent table metadata. Null if the table does not exist. + */ + protected function loadTable($name) + { + $table=new CDbTableSchema; + $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 CDbTableSchema $table 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=''; + $table->columns[$table->primaryKey]->autoIncrement=true; + } + + return true; + } + + /** + * Collects the foreign key column details for the given table. + * @param CDbTableSchema $table 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 column metadata + * @return CDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new CSqliteColumnSchema; + $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; + } + + /** + * Builds a SQL statement for truncating a DB table. + * @param string $table the table to be truncated. The name will be properly quoted by the method. + * @return string the SQL statement for truncating a DB table. + * @since 1.1.6 + */ + public function truncateTable($table) + { + return "DELETE FROM ".$this->quoteTableName($table); + } + + /** + * Builds a SQL statement for dropping a DB column. + * Because SQLite does not support dropping a DB column, calling this method will throw an exception. + * @param string $table the table whose column is to be dropped. The name will be properly quoted by the method. + * @param string $column the name of the column to be dropped. The name will be properly quoted by the method. + * @return string the SQL statement for dropping a DB column. + * @since 1.1.6 + */ + public function dropColumn($table, $column) + { + throw new CDbException(Yii::t('yii', 'Dropping DB column is not supported by SQLite.')); + } + + /** + * Builds a SQL statement for renaming a column. + * Because SQLite does not support renaming a DB column, calling this method will throw an exception. + * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method. + * @param string $name the old name of the column. The name will be properly quoted by the method. + * @param string $newName the new name of the column. The name will be properly quoted by the method. + * @return string the SQL statement for renaming a DB column. + * @since 1.1.6 + */ + public function renameColumn($table, $name, $newName) + { + throw new CDbException(Yii::t('yii', 'Renaming a DB column is not supported by SQLite.')); + } + + /** + * Builds a SQL statement for adding a foreign key constraint to an existing table. + * Because SQLite does not support adding foreign key to an existing table, calling this method will throw an exception. + * @param string $name the name of the foreign key constraint. + * @param string $table the table that the foreign key constraint will be added to. + * @param string $columns the name of the column to that the constraint will be added on. If there are multiple columns, separate them with commas. + * @param string $refTable the table that the foreign key references to. + * @param string $refColumns the name of the column that the foreign key references to. If there are multiple columns, separate them with commas. + * @param string $delete the ON DELETE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL + * @param string $update the ON UPDATE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL + * @return string the SQL statement for adding a foreign key constraint to an existing table. + * @since 1.1.6 + */ + public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete=null, $update=null) + { + throw new CDbException(Yii::t('yii', 'Adding a foreign key constraint to an existing table is not supported by SQLite.')); + } + + /** + * Builds a SQL statement for dropping a foreign key constraint. + * Because SQLite does not support dropping a foreign key constraint, calling this method will throw an exception. + * @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted by the method. + * @param string $table the table whose foreign is to be dropped. The name will be properly quoted by the method. + * @return string the SQL statement for dropping a foreign key constraint. + * @since 1.1.6 + */ + public function dropForeignKey($name, $table) + { + throw new CDbException(Yii::t('yii', 'Dropping a foreign key constraint is not supported by SQLite.')); + } + + /** + * Builds a SQL statement for changing the definition of a column. + * Because SQLite does not support altering a DB column, calling this method will throw an exception. + * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method. + * @param string $column the name of the column to be changed. The name will be properly quoted by the method. + * @param string $type the new column type. The {@link getColumnType} method will be invoked to convert abstract column type (if any) + * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. + * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'. + * @return string the SQL statement for changing the definition of a column. + * @since 1.1.6 + */ + public function alterColumn($table, $column, $type) + { + throw new CDbException(Yii::t('yii', 'Altering a DB column is not supported by SQLite.')); + } + + /** + * Builds a SQL statement for dropping an index. + * @param string $name the name of the index to be dropped. The name will be properly quoted by the method. + * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method. + * @return string the SQL statement for dropping an index. + * @since 1.1.6 + */ + public function dropIndex($name, $table) + { + return 'DROP INDEX '.$this->quoteTableName($name); + } +} |