CMssqlCommandBuilder

Package	system.db.schema.mssql
Inheritance	class CMssqlCommandBuilder » CDbCommandBuilder » CComponent
Source Code	framework/db/schema/mssql/CMssqlCommandBuilder.php

Method Details

public string applyJoin(string $sql, string $join)
$sql	string	the SQL statement to be altered
$join	string	the JOIN clause (starting with join type, such as INNER JOIN)
{return}	string	the altered SQL statement

Alters the SQL to apply JOIN clause. Overrides parent implementation to comply with the DELETE command syntax required when multiple tables are referenced.

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.

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.

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

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

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

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.

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

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

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

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.

Creates a SELECT command for a single table. Override parent implementation to check if an orderby clause if specified when querying with an offset

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.

Creates an UPDATE command. Override parent implementation because mssql don't want to update an identity column

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

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

protected array findOrdering(string $sql)
$sql	string	$sql
{return}	array	ordering expression as key and ordering direction as value

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

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

protected array reverseDirection(array $orders)
$orders	array	original ordering
{return}	array	ordering with reversed direction.

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.

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