diff options
Diffstat (limited to 'framework/Testing/Data/Schema/TDbCommandBuilder.php')
| -rwxr-xr-x | framework/Testing/Data/Schema/TDbCommandBuilder.php | 656 |
1 files changed, 656 insertions, 0 deletions
diff --git a/framework/Testing/Data/Schema/TDbCommandBuilder.php b/framework/Testing/Data/Schema/TDbCommandBuilder.php new file mode 100755 index 00000000..7a196439 --- /dev/null +++ b/framework/Testing/Data/Schema/TDbCommandBuilder.php @@ -0,0 +1,656 @@ +<?php +/** + * TDbCommandBuilder class file. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Testing.Data.Schema.TDbSchema'); +prado::using('System.Testing.Data.Schema.TDbCriteria'); + +/** + * TDbCommandBuilder provides basic methods to create query commands for tables. + * + * @author Qiang Xue <qiang.xue@gmail.com> + * @version $Id: TDbCommandBuilder.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Testing.Data.Schema + * @since 1.0 + */ +class TDbCommandBuilder extends TComponent +{ + const PARAM_PREFIX=':yp'; + + private $_schema; + private $_connection; + + /** + * @param TDbSchema the schema for this command builder + */ + public function __construct($schema) + { + $this->_schema=$schema; + $this->_connection=$schema->getDbConnection(); + } + + /** + * @return TDbConnection database connection. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * @return TDbSchema the schema for this command builder. + */ + public function getSchema() + { + return $this->_schema; + } + + /** + * Returns the last insertion ID for the specified table. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @return mixed last insertion id. Null is returned if no sequence name. + */ + public function getLastInsertID($table) + { + $this->ensureTable($table); + if($table->sequenceName!==null) + return $this->_connection->getLastInsertID($table->sequenceName); + else + return null; + } + + /** + * Creates a SELECT command for a single table. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param TDbCriteria the query criteria + * @return TDbCommand query command. + */ + public function createFindCommand($table,$criteria) + { + $this->ensureTable($table); + $select=is_array($criteria->select) ? implode(', ',$criteria->select) : $criteria->select; + $sql="SELECT {$select} FROM {$table->rawName}"; + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + $sql=$this->applyGroup($sql,$criteria->group); + $sql=$this->applyHaving($sql,$criteria->having); + $sql=$this->applyOrder($sql,$criteria->order); + $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset); + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,$criteria->params); + return $command; + } + + /** + * Creates a COUNT(*) command for a single table. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param TDbCriteria the query criteria + * @return TDbCommand query command. + */ + public function createCountCommand($table,$criteria) + { + $this->ensureTable($table); + $criteria->select='COUNT(*)'; + return $this->createFindCommand($table,$criteria); + } + + /** + * Creates a DELETE command. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param TDbCriteria the query criteria + * @return TDbCommand delete command. + */ + public function createDeleteCommand($table,$criteria) + { + $this->ensureTable($table); + $sql="DELETE FROM {$table->rawName}"; + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + $sql=$this->applyGroup($sql,$criteria->group); + $sql=$this->applyHaving($sql,$criteria->having); + $sql=$this->applyOrder($sql,$criteria->order); + $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset); + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,$criteria->params); + return $command; + } + + /** + * Creates an INSERT command. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array data to be inserted (column name=>column value). If a key is not a valid column name, the corresponding value will be ignored. + * @return TDbCommand insert command + */ + public function createInsertCommand($table,$data) + { + $this->ensureTable($table); + $fields=array(); + $values=array(); + $placeholders=array(); + $i=0; + foreach($data as $name=>$value) + { + if(($column=$table->getColumn($name))!==null && ($value!==null || $column->allowNull)) + { + $fields[]=$column->rawName; + if($value instanceof TDbExpression) + $placeholders[]=(string)$value; + else + { + $placeholders[]=self::PARAM_PREFIX.$i; + $values[self::PARAM_PREFIX.$i]=$column->typecast($value); + $i++; + } + } + } + $sql="INSERT INTO {$table->rawName} (".implode(', ',$fields).') VALUES ('.implode(', ',$placeholders).')'; + $command=$this->_connection->createCommand($sql); + + foreach($values as $name=>$value) + $command->bindValue($name,$value); + + return $command; + } + + /** + * Creates an UPDATE command. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array list of columns to be updated (name=>value) + * @param TDbCriteria the query criteria + * @return TDbCommand update command. + */ + public function createUpdateCommand($table,$data,$criteria) + { + $this->ensureTable($table); + $fields=array(); + $values=array(); + $bindByPosition=isset($criteria->params[0]); + $i=0; + foreach($data as $name=>$value) + { + if(($column=$table->getColumn($name))!==null) + { + if($value instanceof TDbExpression) + $fields[]=$column->rawName.'='.(string)$value; + else if($bindByPosition) + { + $fields[]=$column->rawName.'=?'; + $values[]=$column->typecast($value); + } + else + { + $fields[]=$column->rawName.'='.self::PARAM_PREFIX.$i; + $values[self::PARAM_PREFIX.$i]=$column->typecast($value); + $i++; + } + } + } + if($fields===array()) + throw new TDbException('No columns are being updated for table "{0}".', + $table->name); + $sql="UPDATE {$table->rawName} SET ".implode(', ',$fields); + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + $sql=$this->applyOrder($sql,$criteria->order); + $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset); + + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,array_merge($values,$criteria->params)); + + return $command; + } + + /** + * Creates an UPDATE command that increments/decrements certain columns. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param TDbCriteria the query criteria + * @param array counters to be updated (counter increments/decrements indexed by column names.) + * @return TDbCommand the created command + * @throws CException if no counter is specified + */ + public function createUpdateCounterCommand($table,$counters,$criteria) + { + $this->ensureTable($table); + $fields=array(); + foreach($counters as $name=>$value) + { + if(($column=$table->getColumn($name))!==null) + { + $value=(int)$value; + if($value<0) + $fields[]="{$column->rawName}={$column->rawName}-".(-$value); + else + $fields[]="{$column->rawName}={$column->rawName}+".$value; + } + } + if($fields!==array()) + { + $sql="UPDATE {$table->rawName} SET ".implode(', ',$fields); + $sql=$this->applyJoin($sql,$criteria->join); + $sql=$this->applyCondition($sql,$criteria->condition); + $sql=$this->applyOrder($sql,$criteria->order); + $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset); + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,$criteria->params); + return $command; + } + else + throw new TDbException('No counter columns are being updated for table "{0}".', + $table->name); + } + + /** + * Creates a command based on a given SQL statement. + * @param string the explicitly specified SQL statement + * @param array parameters that will be bound to the SQL statement + * @return TDbCommand the created command + */ + public function createSqlCommand($sql,$params=array()) + { + $command=$this->_connection->createCommand($sql); + $this->bindValues($command,$params); + return $command; + } + + /** + * Alters the SQL to apply JOIN clause. + * @param string the SQL statement to be altered + * @param string the JOIN clause (starting with join type, such as INNER JOIN) + * @return string the altered SQL statement + */ + public function applyJoin($sql,$join) + { + if($join!=='') + return $sql.' '.$join; + else + return $sql; + } + + /** + * Alters the SQL to apply WHERE clause. + * @param string the SQL statement without WHERE clause + * @param string the WHERE clause (without WHERE keyword) + * @return string the altered SQL statement + */ + public function applyCondition($sql,$condition) + { + if($condition!=='') + return $sql.' WHERE '.$condition; + else + return $sql; + } + + /** + * Alters the SQL to apply ORDER BY. + * @param string SQL statement without ORDER BY. + * @param string column ordering + * @return string modified SQL applied with ORDER BY. + */ + public function applyOrder($sql,$orderBy) + { + if($orderBy!=='') + return $sql.' ORDER BY '.$orderBy; + else + return $sql; + } + + /** + * Alters the SQL to apply LIMIT and OFFSET. + * Default implementation is applicable for PostgreSQL, MySQL and SQLite. + * @param string SQL query string without LIMIT and OFFSET. + * @param integer maximum number of rows, -1 to ignore limit. + * @param integer row offset, -1 to ignore offset. + * @return string SQL with LIMIT and OFFSET + */ + public function applyLimit($sql,$limit,$offset) + { + if($limit>=0) + $sql.=' LIMIT '.(int)$limit; + if($offset>0) + $sql.=' OFFSET '.(int)$offset; + return $sql; + } + + /** + * Alters the SQL to apply GROUP BY. + * @param string SQL query string without GROUP BY. + * @param string GROUP BY + * @return string SQL with GROUP BY. + */ + public function applyGroup($sql,$group) + { + if($group!=='') + return $sql.' GROUP BY '.$group; + else + return $sql; + } + + /** + * Alters the SQL to apply HAVING. + * @param string SQL query string without HAVING + * @param string HAVING + * @return string SQL with HAVING + * @since 1.0.1 + */ + public function applyHaving($sql,$having) + { + if($having!=='') + return $sql.' HAVING '.$having; + else + return $sql; + } + + /** + * Binds parameter values for an SQL command. + * @param TDbCommand database command + * @param array values for binding (integer-indexed array for question mark placeholders, string-indexed array for named placeholders) + */ + public function bindValues($command, $values) + { + if(($n=count($values))===0) + return; + if(isset($values[0])) // question mark placeholders + { + for($i=0;$i<$n;++$i) + $command->bindValue($i+1,$values[$i]); + } + else // named placeholders + { + foreach($values as $name=>$value) + { + if($name[0]!==':') + $name=':'.$name; + $command->bindValue($name,$value); + } + } + } + + /** + * Creates a query criteria. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param mixed query condition or criteria. + * If a string, it is treated as query condition (the WHERE clause); + * If an array, it is treated as the initial values for constructing a {@link TDbCriteria} object; + * Otherwise, it should be an instance of {@link TDbCriteria}. + * @param array parameters to be bound to an SQL statement. + * This is only used when the first parameter is a string (query condition). + * In other cases, please use {@link TDbCriteria::params} to set parameters. + * @return TDbCriteria the created query criteria + * @throws CException if the condition is not string, array and TDbCriteria + */ + public function createCriteria($condition='',$params=array()) + { + if(is_array($condition)) + $criteria=new TDbCriteria($condition); + else if($condition instanceof TDbCriteria) + $criteria=clone $condition; + else + { + $criteria=new TDbCriteria; + $criteria->condition=$condition; + $criteria->params=$params; + } + return $criteria; + } + + /** + * Creates a query criteria with the specified primary key. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param mixed primary key value(s). Use array for multiple primary keys. For composite key, each key value must be an array (column name=>column value). + * @param mixed query condition or criteria. + * If a string, it is treated as query condition; + * If an array, it is treated as the initial values for constructing a {@link TDbCriteria}; + * Otherwise, it should be an instance of {@link TDbCriteria}. + * @param array parameters to be bound to an SQL statement. + * This is only used when the second parameter is a string (query condition). + * In other cases, please use {@link TDbCriteria::params} to set parameters. + * @return TDbCriteria the created query criteria + */ + public function createPkCriteria($table,$pk,$condition='',$params=array()) + { + $this->ensureTable($table); + $criteria=$this->createCriteria($condition,$params); + if(!is_array($pk)) // single key + $pk=array($pk); + if(is_array($table->primaryKey) && !isset($pk[0]) && $pk!==array()) // single composite key + $pk=array($pk); + $condition=$this->createInCondition($table,$table->primaryKey,$pk); + if($criteria->condition!=='') + $criteria->condition=$condition.' AND ('.$criteria->condition.')'; + else + $criteria->condition=$condition; + + return $criteria; + } + + /** + * Generates the expression for selecting rows of specified primary key values. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array list of primary key values to be selected within + * @param string column prefix (ended with dot). If null, it will be the table name + * @return string the expression for selection + */ + public function createPkCondition($table,$values,$prefix=null) + { + $this->ensureTable($table); + return $this->createInCondition($table,$table->primaryKey,$values,$prefix); + } + + /** + * Creates a query criteria with the specified column values. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array column values that should be matched in the query (name=>value) + * @param mixed query condition or criteria. + * If a string, it is treated as query condition; + * If an array, it is treated as the initial values for constructing a {@link TDbCriteria}; + * Otherwise, it should be an instance of {@link TDbCriteria}. + * @param array parameters to be bound to an SQL statement. + * This is only used when the second parameter is a string (query condition). + * In other cases, please use {@link TDbCriteria::params} to set parameters. + * @return TDbCriteria the created query criteria + */ + public function createColumnCriteria($table,$columns,$condition='',$params=array()) + { + $this->ensureTable($table); + $criteria=$this->createCriteria($condition,$params); + $bindByPosition=isset($criteria->params[0]); + $conditions=array(); + $values=array(); + $i=0; + foreach($columns as $name=>$value) + { + if(($column=$table->getColumn($name))!==null) + { + if($value!==null) + { + if($bindByPosition) + { + $conditions[]=$table->rawName.'.'.$column->rawName.'=?'; + $values[]=$value; + } + else + { + $conditions[]=$table->rawName.'.'.$column->rawName.'='.self::PARAM_PREFIX.$i; + $values[self::PARAM_PREFIX.$i]=$value; + $i++; + } + } + else + $conditions[]=$table->rawName.'.'.$column->rawName.' IS NULL'; + } + else + throw new TDbException('Table "{0}" does not have a column named "{1}".', + $table->name,$name); + } + $criteria->params=array_merge($values,$criteria->params); + if(isset($conditions[0])) + { + if($criteria->condition!=='') + $criteria->condition=implode(' AND ',$conditions).' AND ('.$criteria->condition.')'; + else + $criteria->condition=implode(' AND ',$conditions); + } + return $criteria; + } + + /** + * Generates the expression for searching the specified keywords within a list of columns. + * The search expression is generated using the 'LIKE' SQL syntax. + * Every word in the keywords must be present and appear in at least one of the columns. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param array list of column names for potential search condition. + * @param mixed search keywords. This can be either a string with space-separated keywords or an array of keywords. + * @param string optional column prefix (with dot at the end). If null, the table name will be used as the prefix. + * @param boolean whether the search is case-sensitive. Defaults to true. This parameter + * has been available since version 1.0.4. + * @return string SQL search condition matching on a set of columns. An empty string is returned + * if either the column array or the keywords are empty. + */ + public function createSearchCondition($table,$columns,$keywords,$prefix=null,$caseSensitive=true) + { + $this->ensureTable($table); + if(!is_array($keywords)) + $keywords=preg_split('/\s+/u',$keywords,-1,PREG_SPLIT_NO_EMPTY); + if(empty($keywords)) + return ''; + if($prefix===null) + $prefix=$table->rawName.'.'; + $conditions=array(); + foreach($columns as $name) + { + if(($column=$table->getColumn($name))===null) + throw new TDbException('Table "{0}" does not have a column named "{0}".', + $table->name,$name); + $condition=array(); + foreach($keywords as $keyword) + { + if($caseSensitive) + $condition[]=$prefix.$column->rawName.' LIKE '.$this->_connection->quoteValue('%'.$keyword.'%'); + else + $condition[]='LOWER('.$prefix.$column->rawName.') LIKE LOWER('.$this->_connection->quoteValue('%'.$keyword.'%').')'; + } + $conditions[]=implode(' AND ',$condition); + } + return '('.implode(' OR ',$conditions).')'; + } + + /** + * Generates the expression for selecting rows of specified primary key values. + * @param mixed the table schema ({@link TDbTableSchema}) or the table name (string). + * @param mixed the column name(s). It can be either a string indicating a single column + * or an array of column names. If the latter, it stands for a composite key. + * @param array list of key values to be selected within + * @param string column prefix (ended with dot). If null, it will be the table name + * @return string the expression for selection + * @since 1.0.4 + */ + public function createInCondition($table,$columnName,$values,$prefix=null) + { + if(($n=count($values))<1) + return '0=1'; + + $this->ensureTable($table); + + if($prefix===null) + $prefix=$table->rawName.'.'; + + $db=$this->_connection; + + if(is_array($columnName) && count($columnName)===1) + $columnName=reset($columnName); + + if(is_string($columnName)) // simple key + { + if(!isset($table->columns[$columnName])) + throw new TDbException('Table "{0}" does not have a column named "{1}".', + $table->name, $columnName); + $column=$table->columns[$columnName]; + + foreach($values as &$value) + { + $value=$column->typecast($value); + if(is_string($value)) + $value=$db->quoteValue($value); + } + if($n===1) + return $prefix.$column->rawName.($values[0]===null?' IS NULL':'='.$values[0]); + else + return $prefix.$column->rawName.' IN ('.implode(', ',$values).')'; + } + else if(is_array($columnName)) // composite key: $values=array(array('pk1'=>'v1','pk2'=>'v2'),array(...)) + { + foreach($columnName as $name) + { + if(!isset($table->columns[$name])) + throw new TDbException('Table "{0}" does not have a column named "{1}".', + $table->name, $name); + + for($i=0;$i<$n;++$i) + { + if(isset($values[$i][$name])) + { + $value=$table->columns[$name]->typecast($values[$i][$name]); + if(is_string($value)) + $values[$i][$name]=$db->quoteValue($value); + else + $values[$i][$name]=$value; + } + else + throw new TDbException('The value for the column "{1}" is not supplied when querying the table "{0}".', + $table->name,$name); + } + } + if(count($values)===1) + { + $entries=array(); + foreach($values[0] as $name=>$value) + $entries[]=$prefix.$table->columns[$name]->rawName.($value===null?' IS NULL':'='.$value); + return implode(' AND ',$entries); + } + + return $this->createCompositeInCondition($table,$values,$prefix); + } + else + throw new TDbException('Column name must be either a string or an array.'); + } + + /** + * Generates the expression for selecting rows with specified composite key values. + * @param TDbTableSchema the table schema + * @param array list of primary key values to be selected within + * @param string column prefix (ended with dot) + * @return string the expression for selection + * @since 1.0.4 + */ + protected function createCompositeInCondition($table,$values,$prefix) + { + $keyNames=array(); + foreach(array_keys($values[0]) as $name) + $keyNames[]=$prefix.$table->columns[$name]->rawName; + $vs=array(); + foreach($values as $value) + $vs[]='('.implode(', ',$value).')'; + return '('.implode(', ',$keyNames).') IN ('.implode(', ',$vs).')'; + } + + /** + * Checks if the parameter is a valid table schema. + * If it is a string, the corresponding table schema will be retrieved. + * @param mixed table schema ({@link TDbTableSchema}) or table name (string). + * If this refers to a valid table name, this parameter will be returned with the corresponding table schema. + * @throws TDbException if the table name is not valid + * @since 1.0.4 + */ + protected function ensureTable(&$table) + { + if(is_string($table) && ($table=$this->_schema->getTable($tableName=$table))===null) + throw new TDbException('Table "{0}" does not exist.', + $tableName); + } +} |