PHP Class Prado\Data\Common\Mssql\TMssqlCommandBuilder

Since: 3.1

Inheritance: extends Prado\Data\Common\TDbCommandBuilder

Afficher le fichier Open project: pradosoft/prado

Méthodes publiques

Méthode	Description
applyLimitOffset ( $sql, $limit, $offset ) : string	Overrides parent implementation. Alters the sql to apply $limit and $offset.
getLastInsertID ( ) : integer	Overrides parent implementation. Uses "SELECT @@Identity".

Méthodes protégées

Méthode	Description
findOrdering ( string $sql ) : array	Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx
joinOrdering ( $orders ) : string
reverseDirection ( $orders ) : array
rewriteLimitOffsetSql ( $sql, integer $limit, integer $offset ) : sql	Rewrite sql to apply $limit > and $offset > 0 for MSSQL database.

Method Details

applyLimitOffset() public méthode

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.

public applyLimitOffset ( $sql, $limit, $offset ) : string
Résultat	string	SQL with limit and offset.

findOrdering() protected méthode

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

protected findOrdering ( string $sql ) : array
$sql	string
Résultat	array	ordering expression as key and ordering direction as value

getLastInsertID() public méthode

Overrides parent implementation. Uses "SELECT @@Identity".

public getLastInsertID ( ) : integer
Résultat	integer	last insert id, null if none is found.

joinOrdering() protected méthode

protected joinOrdering ( $orders ) : string
Résultat	string	concat the orderings

reverseDirection() protected méthode

protected reverseDirection ( $orders ) : array
Résultat	array	ordering with reversed direction.

rewriteLimitOffsetSql() protected méthode

See http://troels.arvin.dk/db/rdbms/#select-limit-offset

protected rewriteLimitOffsetSql ( $sql, integer $limit, integer $offset ) : sql
$limit	integer	> 0
$offset	integer	> 0
Résultat	sql	modified sql query applied with limit and offset.