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 https://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#296 (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.

createCountCommand() method

public CDbCommand createCountCommand(CDbTableSchema $table, CDbCriteria $criteria)
$table	CDbTableSchema	the table metadata
$criteria	CDbCriteria	the query criteria
{return}	CDbCommand	query command.

Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#46 (show)
public function createCountCommand($table,$criteria)
{
    $criteria->order='';
    return parent::createCountCommand($table, $criteria);
}

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#124 (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)
$table	CDbTableSchema	the table metadata
$criteria	CDbCriteria	the query criteria
{return}	CDbCommand	query command.

Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#59 (show)
public function createFindCommand($table,$criteria)
{
    $criteria=$this->checkCriteria($table,$criteria);
    return parent::createFindCommand($table,$criteria);

}

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#74 (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;
            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#139 (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
{return}	array	ordering expression as key and ordering direction as value

Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#229 (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)
                {
                    $ordering[$subs[1][0]] = $subs[2][0];
                }
                //else what?
            }
            else
                $ordering[trim($part)] = 'ASC';
        }
    }
    return $ordering;
}

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

getLastInsertID() method

public mixed getLastInsertID(CDbTableSchema $table)
$table	CDbTableSchema	the table metadata
{return}	mixed	last insertion id. Null is returned if no sequence name.

Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#31 (show)
public function getLastInsertID($table)
{
    if($table->sequenceName!==null)
        return $this->getDbConnection()->createCommand('SELECT SCOPE_IDENTITY()')->queryScalar();
    else
        return null;
}

Returns the last insertion ID for the specified table. Override parent implemantation since PDO mssql driver does not provide this method

joinOrdering() method

protected string joinOrdering(array $orders)
$orders	array	ordering obtained from findOrdering()
{return}	string	concat the orderings

Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#263 (show)
protected function joinOrdering($orders)
{
    if(count($orders)>0)
    {
        $str=array();
        foreach($orders as $column => $direction)
            $str[] = $column.' '.$direction;
        return 'ORDER BY '.implode(', ', $str);
    }
}
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#280 (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	> 0
$offset	integer	> 0
{return}	sql	modified sql query applied with limit and offset.

Source Code: framework/db/schema/mssql/CMssqlCommandBuilder.php#209 (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);
    $reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering));
    $sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner top table__] {$reverseOrdering}) as [__outer top table__] {$orginalOrdering}";
    return $sql;
}

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