CMssqlCommandBuilder

方法详情

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() 方法

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

createCountCommand() 方法

public CDbCommand createCountCommand(CDbTableSchema $table, CDbCriteria $criteria)
$table	CDbTableSchema	the table metadata
$criteria	CDbCriteria	the query criteria
{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

createDeleteCommand() 方法

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

createFindCommand() 方法

public CDbCommand createFindCommand(CDbTableSchema $table, CDbCriteria $criteria)
$table	CDbTableSchema	the table metadata
$criteria	CDbCriteria	the query criteria
{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

createUpdateCommand() 方法

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

createUpdateCounterCommand() 方法

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

findOrdering() 方法

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

getLastInsertID() 方法

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

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

joinOrdering() 方法

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

reverseDirection() 方法

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

rewriteLimitOffsetSql() 方法

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.

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