Versions
Look up a class, method, property or event

CMssqlCommandBuilder

Package system.db.schema.mssql
Inheritance class CMssqlCommandBuilder » CDbCommandBuilder » CComponent
Source Code framework/db/schema/mssql/CMssqlCommandBuilder.php
CMssqlCommandBuilder provides basic methods to create query commands for tables for Mssql Servers.

Public Properties

Hide inherited properties

PropertyTypeDescriptionDefined By
dbConnection CDbConnection database connection. CDbCommandBuilder
schema CDbSchema the schema for this command builder. CDbCommandBuilder

Protected Properties

Hide inherited properties

PropertyTypeDescriptionDefined By
integerPrimaryKeyDefaultValue string Returns default value of the integer/serial primary key. Default value means that the next CDbCommandBuilder

Public Methods

Hide inherited methods

MethodDescriptionDefined By
__call() Calls the named method which is not a class method. CComponent
__construct() CDbCommandBuilder
__get() Returns a property value, an event handler list or a behavior based on its name. CComponent
__isset() Checks if a property value is null. CComponent
__set() Sets value of a component property. CComponent
__unset() Sets a component property to be null. CComponent
applyCondition() Alters the SQL to apply WHERE clause. CDbCommandBuilder
applyGroup() Alters the SQL to apply GROUP BY. CDbCommandBuilder
applyHaving() Alters the SQL to apply HAVING. CDbCommandBuilder
applyJoin() Alters the SQL to apply JOIN clause. CDbCommandBuilder
applyLimit() This is a port from Prado Framework. CMssqlCommandBuilder
applyOrder() Alters the SQL to apply ORDER BY. CDbCommandBuilder
asa() Returns the named behavior object. CComponent
attachBehavior() Attaches a behavior to this component. CComponent
attachBehaviors() Attaches a list of behaviors to the component. CComponent
attachEventHandler() Attaches an event handler to an event. CComponent
bindValues() Binds parameter values for an SQL command. CDbCommandBuilder
canGetProperty() Determines whether a property can be read. CComponent
canSetProperty() Determines whether a property can be set. CComponent
createColumnCriteria() Creates a query criteria with the specified column values. CDbCommandBuilder
createCountCommand() Creates a COUNT(*) command for a single table. CMssqlCommandBuilder
createCriteria() Creates a query criteria. CDbCommandBuilder
createDeleteCommand() Creates a DELETE command. CMssqlCommandBuilder
createFindCommand() Creates a SELECT command for a single table. CMssqlCommandBuilder
createInCondition() Generates the expression for selecting rows of specified primary key values. CDbCommandBuilder
createInsertCommand() Creates an INSERT command. CDbCommandBuilder
createMultipleInsertCommand() Creates a multiple INSERT command. CDbCommandBuilder
createPkCondition() Generates the expression for selecting rows of specified primary key values. CDbCommandBuilder
createPkCriteria() Creates a query criteria with the specified primary key. CDbCommandBuilder
createSearchCondition() Generates the expression for searching the specified keywords within a list of columns. CDbCommandBuilder
createSqlCommand() Creates a command based on a given SQL statement. CDbCommandBuilder
createUpdateCommand() Creates an UPDATE command. CMssqlCommandBuilder
createUpdateCounterCommand() Creates an UPDATE command that increments/decrements certain columns. CMssqlCommandBuilder
detachBehavior() Detaches a behavior from the component. CComponent
detachBehaviors() Detaches all behaviors from the component. CComponent
detachEventHandler() Detaches an existing event handler. CComponent
disableBehavior() Disables an attached behavior. CComponent
disableBehaviors() Disables all behaviors attached to this component. CComponent
enableBehavior() Enables an attached behavior. CComponent
enableBehaviors() Enables all behaviors attached to this component. CComponent
evaluateExpression() Evaluates a PHP expression or callback under the context of this component. CComponent
getDbConnection() Returns database connection. CDbCommandBuilder
getEventHandlers() Returns the list of attached event handlers for an event. CComponent
getLastInsertID() Returns the last insertion ID for the specified table. CDbCommandBuilder
getSchema() Returns the schema for this command builder. CDbCommandBuilder
hasEvent() Determines whether an event is defined. CComponent
hasEventHandler() Checks whether the named event has attached handlers. CComponent
hasProperty() Determines whether a property is defined. CComponent
raiseEvent() Raises an event. CComponent

Protected Methods

Hide inherited methods

MethodDescriptionDefined By
checkCriteria() Checks if the criteria has an order by clause when using offset/limit. CMssqlCommandBuilder
composeMultipleInsertCommand() Creates a multiple INSERT command. CDbCommandBuilder
createCompositeInCondition() Generates the expression for selecting rows with specified composite key values. CMssqlCommandBuilder
ensureTable() Checks if the parameter is a valid table schema. CDbCommandBuilder
findOrdering() Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx CMssqlCommandBuilder
getIntegerPrimaryKeyDefaultValue() Returns default value of the integer/serial primary key. Default value means that the next CDbCommandBuilder
joinOrdering() CMssqlCommandBuilder
reverseDirection() CMssqlCommandBuilder
rewriteLimitOffsetSql() Rewrite sql to apply $limit > and $offset > 0 for MSSQL database. CMssqlCommandBuilder

Method Details

applyLimit() method
public string applyLimit(string $sql, integer $limit, integer $offset)
$sql string SQL query string.
$limit integer maximum number of rows, -1 to ignore limit.
$offset integer row offset, -1 to ignore offset.
{return} string SQL with limit and offset.
Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#180 (show)
public function applyLimit($sql$limit$offset)
{
    
$limit $limit!==null ? (int)$limit : -1;
    
$offset $offset!==null ? (int)$offset : -1;
    if (
$limit && $offset <= 0//just limit
        
$sql preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit"$sql);
    elseif(
$limit && $offset 0)
        
$sql $this->rewriteLimitOffsetSql($sql$limit,$offset);
    return 
$sql;
}

This is a port from Prado Framework.

Overrides parent implementation. Alters the sql to apply $limit and $offset. The idea for limit with offset is done by modifying the sql on the fly with numerous assumptions on the structure of the sql string. The modification is done with reference to the notes from http://troels.arvin.dk/db/rdbms/#select-limit-offset

SELECT * FROM ( SELECT TOP n * FROM ( SELECT TOP z columns -- (z=n+skip) FROM tablename ORDER BY key ASC ) AS FOO ORDER BY key DESC -- ('FOO' may be anything) ) AS BAR ORDER BY key ASC -- ('BAR' may be anything)

Regular expressions are used to alter the SQL query. The resulting SQL query may be malformed for complex queries. The following restrictions apply

  • In particular, commas should NOT be used as part of the ordering expression or identifier. Commas must only be used for separating the ordering clauses.
  • In the ORDER BY clause, the column name should NOT be be qualified with a table name or view name. Alias the column names or use column index.
  • No clauses should follow the ORDER BY clause, e.g. no COMPUTE or FOR clauses.

checkCriteria() method
protected CDbCriteria checkCriteria(CMssqlTableSchema $table, CDbCriteria $criteria)
$table CMssqlTableSchema table schema
$criteria CDbCriteria criteria
{return} CDbCriteria the modified criteria
Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#310 (show)
protected function checkCriteria($table$criteria)
{
    if (
$criteria->offset && $criteria->order==='')
    {
        
$criteria->order=is_array($table->primaryKey)?implode(',',$table->primaryKey):$table->primaryKey;
    }
    return 
$criteria;
}

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.

createCompositeInCondition() method
protected string createCompositeInCondition(CDbTableSchema $table, array $values, string $prefix)
$table CDbTableSchema the table schema
$values array list of primary key values to be selected within
$prefix string column prefix (ended with dot)
{return} string the expression for selection
Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#326 (show)
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).')';
}

Generates the expression for selecting rows with specified composite key values.

createCountCommand() method
public CDbCommand createCountCommand(CDbTableSchema $table, CDbCriteria $criteria, string $alias='t')
$table CDbTableSchema the table metadata
$criteria CDbCriteria the query criteria
$alias string the alias name of the primary table. Defaults to 't'.
{return} CDbCommand query command.
Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#31 (show)
public function createCountCommand($table,$criteria,$alias='t')
{
    
$criteria->order='';
    return 
parent::createCountCommand($table$criteria,$alias);
}

Creates a COUNT(*) command for a single table. Override parent implementation to remove the order clause of criteria if it exists

createDeleteCommand() method
public CDbCommand createDeleteCommand(CDbTableSchema $table, CDbCriteria $criteria)
$table CDbTableSchema the table metadata
$criteria CDbCriteria the query criteria
{return} CDbCommand delete command.
Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#115 (show)
public function createDeleteCommand($table,$criteria)
{
    
$criteria=$this->checkCriteria($table$criteria);
    return 
parent::createDeleteCommand($table$criteria);
}

Creates a DELETE command. Override parent implementation to check if an orderby clause if specified when querying with an offset

createFindCommand() method
public CDbCommand createFindCommand(CDbTableSchema $table, CDbCriteria $criteria, string $alias='t')
$table CDbTableSchema the table metadata
$criteria CDbCriteria the query criteria
$alias string the alias name of the primary table. Defaults to 't'.
{return} CDbCommand query command.
Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#45 (show)
public function createFindCommand($table,$criteria,$alias='t')
{
    
$criteria=$this->checkCriteria($table,$criteria);
    return 
parent::createFindCommand($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

createUpdateCommand() method
public CDbCommand createUpdateCommand(CDbTableSchema $table, array $data, CDbCriteria $criteria)
$table CDbTableSchema the table metadata
$data array list of columns to be updated (name=>value)
$criteria CDbCriteria the query criteria
{return} CDbCommand update command.
Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#61 (show)
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 an UPDATE command. Override parent implementation because mssql don't want to update an identity column

createUpdateCounterCommand() method
public CDbCommand createUpdateCounterCommand(CDbTableSchema $table, CDbCriteria $counters, array $criteria)
$table CDbTableSchema the table metadata
$counters CDbCriteria the query criteria
$criteria array counters to be updated (counter increments/decrements indexed by column names.)
{return} CDbCommand the created command
Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#130 (show)
public function createUpdateCounterCommand($table,$counters,$criteria)
{
    
$criteria=$this->checkCriteria($table$criteria);
    return 
parent::createUpdateCounterCommand($table$counters$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

findOrdering() method
protected array findOrdering(string $sql)
$sql string $sql
{return} array ordering expression as key and ordering direction as value
Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#220 (show)
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)>&& 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) > && 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;
}

Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx

joinOrdering() method
protected string joinOrdering(array $orders, string $newPrefix)
$orders array ordering obtained from findOrdering()
$newPrefix string new table prefix to the ordering columns
{return} string concat the orderings
Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#276 (show)
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);
    }
}

reverseDirection() method
protected array reverseDirection(array $orders)
$orders array original ordering
{return} array ordering with reversed direction.
Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#294 (show)
protected function reverseDirection($orders)
{
    foreach(
$orders as $column => $direction)
        
$orders[$column] = strtolower(trim($direction))==='desc' 'ASC' 'DESC';
    return 
$orders;
}

rewriteLimitOffsetSql() method
protected string rewriteLimitOffsetSql(string $sql, integer $limit, integer $offset)
$sql string sql query
$limit integer $limit > 0
$offset integer $offset > 0
{return} string modified sql query applied with limit and offset.
Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#201 (show)
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;
}

Rewrite sql to apply $limit > and $offset > 0 for MSSQL database. See http://troels.arvin.dk/db/rdbms/#select-limit-offset

Total 1 comment

#11662 report it
jeremy at 2013/01/25 08:53am
ApplyLimit() - long-standing issue

see (old) Yii issue 1501 for long discussion and various patches of applyLimit()

http://code.google.com/p/yii/issues/detail?id=1501

esp. note solution in comment 7 of this ticket. I'm running that solution for the past year and it works fine.

Leave a comment

Please to leave your comment.