diff options
Diffstat (limited to 'framework/db/schema/mssql/CMssqlCommandBuilder.php')
| -rw-r--r-- | framework/db/schema/mssql/CMssqlCommandBuilder.php | 337 |
1 files changed, 337 insertions, 0 deletions
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).')'; + } +} |