Class TMssqlCommandBuilder

Description

TMssqlCommandBuilder provides specifics methods to create limit/offset query commands for MSSQL servers.

  • author: Wei Zhuo <weizho[at]gmail[dot]com>
  • version: $Id: TDbCommandBuilder.php 1863 2007-04-12 12:43:49Z wei $
  • since: 3.1

Located in /Data/Common/Mssql/TMssqlCommandBuilder.php (line 24)

TComponent
   |
   --TDbCommandBuilder
      |
      --TMssqlCommandBuilder
Method Summary
string applyLimitOffset (string $sql, [integer $limit = -1], [integer $offset = -1])
array findOrdering (string $sql)
integer getLastInsertID ()
string joinOrdering (array $orders)
array reverseDirection (array $orders)
sql rewriteLimitOffsetSql (string $sql, integer $limit, integer $offset)
Methods
applyLimitOffset (line 82)

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

  1.  SELECT FROM (
  2.   SELECT TOP n FROM (
  3.     SELECT TOP z columns      -- (z=n+skip)
  4.     FROM tablename
  5.     ORDER BY key ASC
  6.   AS FOO ORDER BY key DESC -- ('FOO' may be anything)
  7.  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.

  • return: SQL with limit and offset.
  • access: public
string applyLimitOffset (string $sql, [integer $limit = -1], [integer $offset = -1])
  • string $sql: SQL query string.
  • integer $limit: maximum number of rows, -1 to ignore limit.
  • integer $offset: row offset, -1 to ignore offset.

Redefinition of:
TDbCommandBuilder::applyLimitOffset()
Alters the sql to apply $limit and $offset. Default implementation is applicable for PostgreSQL, MySQL and SQLite.
findOrdering (line 119)

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

  • return: ordering expression as key and ordering direction as value
  • access: protected
array findOrdering (string $sql)
  • string $sql
getLastInsertID (line 30)

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

  • return: last insert id, null if none is found.
  • access: public
integer getLastInsertID ()

Redefinition of:
TDbCommandBuilder::getLastInsertID()
Iterate through all the columns and returns the last insert id of the first column that has a sequence or serial.
joinOrdering (line 151)
  • return: concat the orderings
  • access: protected
string joinOrdering (array $orders)
  • array $orders: ordering obtained from findOrdering()
reverseDirection (line 166)
  • return: ordering with reversed direction.
  • access: protected
array reverseDirection (array $orders)
  • array $orders: original ordering
rewriteLimitOffsetSql (line 101)

Rewrite sql to apply $limit > and $offset > 0 for MSSQL database.

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

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

Inherited Methods

Inherited From TDbCommandBuilder

TDbCommandBuilder::__construct()
TDbCommandBuilder::applyCriterias()
TDbCommandBuilder::applyLimitOffset()
TDbCommandBuilder::applyOrdering()
TDbCommandBuilder::bindArrayValues()
TDbCommandBuilder::bindColumnValues()
TDbCommandBuilder::createCommand()
TDbCommandBuilder::createCountCommand()
TDbCommandBuilder::createDeleteCommand()
TDbCommandBuilder::createFindCommand()
TDbCommandBuilder::createInsertCommand()
TDbCommandBuilder::createUpdateCommand()
TDbCommandBuilder::getColumnBindings()
TDbCommandBuilder::getDbConnection()
TDbCommandBuilder::getInsertFieldBindings()
TDbCommandBuilder::getLastInsertID()
TDbCommandBuilder::getPdoType()
TDbCommandBuilder::getSearchCondition()
TDbCommandBuilder::getSearchExpression()
TDbCommandBuilder::getSelectFieldList()
TDbCommandBuilder::getTableInfo()
TDbCommandBuilder::hasIntegerKey()
TDbCommandBuilder::setDbConnection()
TDbCommandBuilder::setTableInfo()

Inherited From TComponent

TComponent::addParsedObject()
TComponent::attachEventHandler()
TComponent::canGetProperty()
TComponent::canSetProperty()
TComponent::createdOnTemplate()
TComponent::detachEventHandler()
TComponent::evaluateExpression()
TComponent::evaluateStatements()
TComponent::getEventHandlers()
TComponent::getSubProperty()
TComponent::hasEvent()
TComponent::hasEventHandler()
TComponent::hasProperty()
TComponent::raiseEvent()
TComponent::setSubProperty()
TComponent::__call()
TComponent::__get()
TComponent::__set()
TComponent::__sleep()
TComponent::__wakeup()

Documentation generated on Mon, 25 Jun 2012 14:38:43 +0200 by phpDocumentor 1.4.3