CMssqlCommandBuilder

Method Details

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 CDbCrireria checkCriteria(CMssqlTableSchema $table, CDbCriteria $criteria)
$table	CMssqlTableSchema	table schema
$criteria	CDbCriteria	criteria
{return}	CDbCrireria	the modified criteria

Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#309 (show)
protected function checkCriteria($table, $criteria)
{
    if ($criteria->offset > 0 && $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#325 (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#32 (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#46 (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;
            }
            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 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#219 (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)>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;
}

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#275 (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#293 (show)
protected function reverseDirection($orders)
{
    foreach($orders as $column => $direction)
        $orders[$column] = strtolower(trim($direction))==='desc' ? 'ASC' : 'DESC';
    return $orders;
}
rewriteLimitOffsetSql() method

protected sql rewriteLimitOffsetSql(string $sql, integer $limit, integer $offset)
$sql	string	sql query
$limit	integer	$limit > 0
$offset	integer	$offset > 0
{return}	sql	modified sql query applied with limit and offset.

Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#200 (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