Package | system.db.schema.mssql |
---|---|
Inheritance | class CMssqlCommandBuilder » CDbCommandBuilder » CComponent |
Since | 1.0.4 |
Version | $Id$ |
Source Code | framework/db/schema/mssql/CMssqlCommandBuilder.php |
Property | Type | Description | Defined By |
---|---|---|---|
dbConnection | CDbConnection | database connection. | CDbCommandBuilder |
schema | CDbSchema | the schema for this command builder. | CDbCommandBuilder |
Method | Description | Defined By |
---|---|---|
__call() | Calls the named method which is not a class method. | CComponent |
__construct() | CDbCommandBuilder | |
__get() | Returns a property value, an event handler list or a behavior based on its name. | CComponent |
__isset() | Checks if a property value is null. | CComponent |
__set() | Sets value of a component property. | CComponent |
__unset() | Sets a component property to be null. | CComponent |
applyCondition() | Alters the SQL to apply WHERE clause. | CDbCommandBuilder |
applyGroup() | Alters the SQL to apply GROUP BY. | CDbCommandBuilder |
applyHaving() | Alters the SQL to apply HAVING. | CDbCommandBuilder |
applyJoin() | Alters the SQL to apply JOIN clause. | CDbCommandBuilder |
applyLimit() | This is a port from Prado Framework. | CMssqlCommandBuilder |
applyOrder() | Alters the SQL to apply ORDER BY. | CDbCommandBuilder |
asa() | Returns the named behavior object. | CComponent |
attachBehavior() | Attaches a behavior to this component. | CComponent |
attachBehaviors() | Attaches a list of behaviors to the component. | CComponent |
attachEventHandler() | Attaches an event handler to an event. | CComponent |
bindValues() | Binds parameter values for an SQL command. | CDbCommandBuilder |
canGetProperty() | Determines whether a property can be read. | CComponent |
canSetProperty() | Determines whether a property can be set. | CComponent |
createColumnCriteria() | Creates a query criteria with the specified column values. | CDbCommandBuilder |
createCountCommand() | Creates a COUNT(*) command for a single table. | CMssqlCommandBuilder |
createCriteria() | Creates a query criteria. | CDbCommandBuilder |
createDeleteCommand() | Creates a DELETE command. | CMssqlCommandBuilder |
createFindCommand() | Creates a SELECT command for a single table. | CMssqlCommandBuilder |
createInCondition() | Generates the expression for selecting rows of specified primary key values. | CDbCommandBuilder |
createInsertCommand() | Creates an INSERT command. | CDbCommandBuilder |
createPkCondition() | Generates the expression for selecting rows of specified primary key values. | CDbCommandBuilder |
createPkCriteria() | Creates a query criteria with the specified primary key. | CDbCommandBuilder |
createSearchCondition() | Generates the expression for searching the specified keywords within a list of columns. | CDbCommandBuilder |
createSqlCommand() | Creates a command based on a given SQL statement. | CDbCommandBuilder |
createUpdateCommand() | Creates an UPDATE command. | CMssqlCommandBuilder |
createUpdateCounterCommand() | Creates an UPDATE command that increments/decrements certain columns. | CMssqlCommandBuilder |
detachBehavior() | Detaches a behavior from the component. | CComponent |
detachBehaviors() | Detaches all behaviors from the component. | CComponent |
detachEventHandler() | Detaches an existing event handler. | CComponent |
disableBehavior() | Disables an attached behavior. | CComponent |
disableBehaviors() | Disables all behaviors attached to this component. | CComponent |
enableBehavior() | Enables an attached behavior. | CComponent |
enableBehaviors() | Enables all behaviors attached to this component. | CComponent |
getDbConnection() | Returns database connection. | CDbCommandBuilder |
getEventHandlers() | Returns the list of attached event handlers for an event. | CComponent |
getLastInsertID() | Returns the last insertion ID for the specified table. | CMssqlCommandBuilder |
getSchema() | Returns the schema for this command builder. | CDbCommandBuilder |
hasEvent() | Determines whether an event is defined. | CComponent |
hasEventHandler() | Checks whether the named event has attached handlers. | CComponent |
hasProperty() | Determines whether a property is defined. | CComponent |
raiseEvent() | Raises an event. | CComponent |
Method | Description | Defined By |
---|---|---|
checkCriteria() | Checks if the criteria has an order by clause when using offset/limit. | CMssqlCommandBuilder |
createCompositeInCondition() | Generates the expression for selecting rows with specified composite key values. | CDbCommandBuilder |
ensureTable() | Checks if the parameter is a valid table schema. | CDbCommandBuilder |
findOrdering() | Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx | CMssqlCommandBuilder |
joinOrdering() | CMssqlCommandBuilder | |
reverseDirection() | CMssqlCommandBuilder | |
rewriteLimitOffsetSql() | Rewrite sql to apply $limit > and $offset > 0 for MSSQL database. | CMssqlCommandBuilder |
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. |
public function applyLimit($sql, $limit, $offset)
{
$limit = $limit!==null ? intval($limit) : -1;
$offset = $offset!==null ? intval($offset) : -1;
if ($limit > 0 && $offset <= 0) //just limit
$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);
else if($limit > 0 && $offset > 0)
$sql = $this->rewriteLimitOffsetSql($sql, $limit,$offset);
return $sql;
}
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
protected CDbCrireria checkCriteria(CMssqlTableSchema $table, CDbCriteria $criteria)
| ||
$table | CMssqlTableSchema | table schema |
$criteria | CDbCriteria | criteria |
{return} | CDbCrireria | the modified criteria |
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.
$table | CDbTableSchema | the table metadata |
$criteria | CDbCriteria | the query criteria |
{return} | CDbCommand | query command. |
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
$table | CDbTableSchema | the table metadata |
$criteria | CDbCriteria | the query criteria |
{return} | CDbCommand | delete command. |
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
$table | CDbTableSchema | the table metadata |
$criteria | CDbCriteria | the query criteria |
{return} | CDbCommand | query command. |
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
$table | CDbTableSchema | the table metadata |
$data | array | list of columns to be updated (name=>value) |
$criteria | CDbCriteria | the query criteria |
{return} | CDbCommand | update command. |
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
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 |
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
protected array findOrdering(string $sql)
| ||
$sql | string | |
{return} | array | ordering expression as key and ordering direction as value |
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 http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx
public mixed getLastInsertID(CDbTableSchema $table)
| ||
$table | CDbTableSchema | the table metadata |
{return} | mixed | last insertion id. Null is returned if no sequence name. |
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
protected string joinOrdering(array $orders)
| ||
$orders | array | ordering obtained from findOrdering() |
{return} | string | concat the orderings |
protected function joinOrdering($orders)
{
if(count($orders)>0)
{
$str=array();
foreach($orders as $column => $direction)
$str[] = $column.' '.$direction;
return 'ORDER BY '.implode(', ', $str);
}
}
protected array reverseDirection(array $orders)
| ||
$orders | array | original ordering |
{return} | array | ordering with reversed direction. |
protected function reverseDirection($orders)
{
foreach($orders as $column => $direction)
$orders[$column] = strtolower(trim($direction))==='desc' ? 'ASC' : 'DESC';
return $orders;
}
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. |
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 http://troels.arvin.dk/db/rdbms/#select-limit-offset
Signup or Login in order to comment.