Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Fetching contributors…

Cannot retrieve contributors at this time

338 lines (319 sloc) 11.363 kb
<?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 &copy; 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>
* @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;
}
elseif($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>
* </ul>
*
* @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 ? (int)$limit : -1;
$offset = $offset!==null ? (int)$offset : -1;
if ($limit > 0 && $offset <= 0) //just limit
$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);
elseif($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 string 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 CDbCriteria 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).')';
}
}
Jump to Line
Something went wrong with that request. Please try again.