CMssqlCommandBuilder
鍖 | system.db.schema.mssql |
---|---|
缁ф壙 | class CMssqlCommandBuilder » CDbCommandBuilder » CComponent |
鐗堟湰 | $Id: CMssqlCommandBuilder.php 3515 2011-12-28 12:29:24Z mdomba $ |
婧愮爜 | framework/db/schema/mssql/CMssqlCommandBuilder.php |
鍏叡灞炴
灞炴 | 绫诲瀷 | 鎻忚堪 | 瀹氫箟鍦 |
---|---|---|---|
dbConnection | CDbConnection | database connection. | CDbCommandBuilder |
schema | CDbSchema | the schema for this command builder. | CDbCommandBuilder |
鍏叡鏂规硶
鏂规硶 | 鎻忚堪 | 瀹氫箟鍦 |
---|---|---|
__call() | 濡傛灉绫讳腑娌℃湁璋冪殑鏂规硶鍚嶏紝鍒欒皟鐢ㄨ繖涓柟娉曘 | CComponent |
__construct() | CDbCommandBuilder | |
__get() | 杩斿洖涓涓睘鎬у笺佷竴涓簨浠跺鐞嗙▼搴忓垪琛ㄦ垨涓涓涓哄悕绉般 | CComponent |
__isset() | 妫鏌ヤ竴涓睘鎬ф槸鍚︿负null銆 | CComponent |
__set() | 璁剧疆涓涓粍浠剁殑灞炴у笺 | CComponent |
__unset() | 璁剧疆涓涓粍浠剁殑灞炴т负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() | 杩斿洖杩欎釜鍚嶅瓧鐨勮涓哄璞° | CComponent |
attachBehavior() | 闄勫姞涓涓涓哄埌缁勪欢銆 | CComponent |
attachBehaviors() | 闄勫姞涓涓涓哄垪琛ㄥ埌缁勪欢銆 | CComponent |
attachEventHandler() | 涓轰簨浠堕檮鍔犱竴涓簨浠跺鐞嗙▼搴忋 | CComponent |
bindValues() | Binds parameter values for an SQL command. | CDbCommandBuilder |
canGetProperty() | 纭畾灞炴ф槸鍚﹀彲璇汇 | CComponent |
canSetProperty() | 纭畾灞炴ф槸鍚﹀彲鍐欍 | CComponent |
createColumnCriteria() | Creates a query criteria with the specified column values. | CDbCommandBuilder |
createCountCommand() | 涓轰竴涓〃鍒涘缓COUNT(*)璇彞銆 | CMssqlCommandBuilder |
createCriteria() | Creates a query criteria. | CDbCommandBuilder |
createDeleteCommand() | 鍒涘缓DELETE璇彞銆 | CMssqlCommandBuilder |
createFindCommand() | 涓轰竴涓〃鍒涘缓SELECT璇彞銆 | 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() | 鍒涘缓UPDATE璇彞銆 | CMssqlCommandBuilder |
createUpdateCounterCommand() | 鍒涘缓閫掑鎴栭掑噺鐗瑰畾鍒楃殑UPDATE璇彞銆 | CMssqlCommandBuilder |
detachBehavior() | 浠庣粍浠朵腑鍒嗙涓涓涓恒 | CComponent |
detachBehaviors() | 浠庣粍浠朵腑鍒嗙鎵鏈夎涓恒 | CComponent |
detachEventHandler() | 鍒嗙涓涓瓨鍦ㄧ殑浜嬩欢澶勭悊绋嬪簭銆 | CComponent |
disableBehavior() | 绂佺敤涓涓檮鍔犺涓恒 | CComponent |
disableBehaviors() | 绂佺敤缁勪欢闄勫姞鐨勬墍鏈夎涓恒 | CComponent |
enableBehavior() | 鍚敤涓涓檮鍔犺涓恒 | CComponent |
enableBehaviors() | 鍚敤缁勪欢闄勫姞鐨勬墍鏈夎涓恒 | CComponent |
evaluateExpression() | 璁$畻涓涓狿HP琛ㄨ揪寮忥紝鎴栨牴鎹粍浠朵笂涓嬫枃鎵ц鍥炶皟銆 | CComponent |
getDbConnection() | 杩斿洖database connection. | CDbCommandBuilder |
getEventHandlers() | 杩斿洖涓涓簨浠剁殑闄勫姞澶勭悊绋嬪簭鍒楄〃銆 | CComponent |
getLastInsertID() | Returns the last insertion ID for the specified table. | CDbCommandBuilder |
getSchema() | 杩斿洖the schema for this command builder. | CDbCommandBuilder |
hasEvent() | 纭畾涓涓簨浠舵槸鍚﹀畾涔夈 | CComponent |
hasEventHandler() | 妫鏌ヤ簨浠舵槸鍚︽湁闄勫姞鐨勫鐞嗙▼搴忋 | CComponent |
hasProperty() | 纭畾灞炴ф槸鍚﹁瀹氫箟銆 | CComponent |
raiseEvent() | 鍙戣捣涓涓簨浠躲 | CComponent |
鍙椾繚鎶ゆ柟娉
鏂规硶 | 鎻忚堪 | 瀹氫箟鍦 |
---|---|---|
checkCriteria() | 妫鏌ヤ娇鐢╫ffset鎴杔imit鐨勬煡璇㈡潯浠舵槸鍚︽湁order by瀛愬彞銆 | CMssqlCommandBuilder |
createCompositeInCondition() | 鐢熸垚鏍规嵁鎸囧畾绗﹀悎閿奸夋嫨琛岀殑琛ㄨ揪寮忋 | CMssqlCommandBuilder |
ensureTable() | Checks if the parameter is a valid table schema. | CDbCommandBuilder |
findOrdering() | 鍩轰簬绠鍖栬娉曪細http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx | CMssqlCommandBuilder |
joinOrdering() | CMssqlCommandBuilder | |
reverseDirection() | CMssqlCommandBuilder | |
rewriteLimitOffsetSql() | 涓篗SSQL鏁版嵁搴撻噸鍐橲QL浠ュ簲鐢$limit > 0鍜$offset > 0銆 | CMssqlCommandBuilder |
鏂规硶璇︾粏
public string applyLimit(string $sql, integer $limit, integer $offset)
| ||
$sql | string | SQL鏌ヨ瀛楃涓 |
$limit | integer | 鏈澶ц鏁帮紝鑻ヤ负-1鍒欏拷鐣imit銆 |
$offset | integer | 琛屽亸绉伙紝鑻ヤ负-1鍒欏拷鐣ffset銆 |
{return} | string | 甯︽湁limit鍜宱ffset鐨凷QL銆 |
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.
閲嶅啓鐖剁被鐨勫疄鐜般傛敼鍙楽QL浠ュ簲鐢$limit鍜$offset銆
搴旂敤甯︽湁offset鐨刲imit鏄牴鎹甋QL璇彞缁撴瀯鐨
璁稿鍋囪閫氳繃鍔ㄦ佷慨鏀筍QL瀹炵幇鐨勩
鏍规嵁涓嬮潰鍦板潃鐨勯檮娉ㄥ畬鎴愪慨鏀癸細
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 CDbCrireria checkCriteria(CMssqlTableSchema $table, CDbCriteria $criteria)
| ||
$table | CMssqlTableSchema | 琛╯chema |
$criteria | CDbCriteria | 鏌ヨ鏉′欢 |
{return} | CDbCrireria | 淇敼鍚庣殑鏌ヨ鏉′欢 |
protected function checkCriteria($table, $criteria)
{
if ($criteria->offset > 0 && $criteria->order==='')
{
$criteria->order=is_array($table->primaryKey)?implode(',',$table->primaryKey):$table->primaryKey;
}
return $criteria;
}
妫鏌ヤ娇鐢╫ffset鎴杔imit鐨勬煡璇㈡潯浠舵槸鍚︽湁order by瀛愬彞銆 閲嶅啓鐖剁被鐨勫疄鐜颁互妫鏌ュ甫鏈塷ffset鐨勬煡璇腑order by瀛愬彞鏄惁宸叉寚瀹氥 鑻ユ湭鎸囧畾order by瀛愬彞锛屽垯鎸変富閿帓搴忋
protected string createCompositeInCondition(CDbTableSchema $table, array $values, string $prefix)
| ||
$table | CDbTableSchema | 琛╯chema |
$values | array | 瑕侀夋嫨鐨勪富閿煎垪琛 |
$prefix | string | 鍒楀墠缂锛堜互.缁撴潫锛 |
{return} | string | 閫夋嫨鐨勮〃杈惧紡銆 |
protected function createCompositeInCondition($table,$values,$prefix)
{
$vs=array();
foreach($values as $value)
{
$c=array();
foreach($value as $k=>$v)
$c[]=$prefix.$table->columns[$k]->rawName.'='.$v;
$vs[]='('.implode(' AND ',$c).')';
}
return '('.implode(' OR ',$vs).')';
}
鐢熸垚鏍规嵁鎸囧畾绗﹀悎閿奸夋嫨琛岀殑琛ㄨ揪寮忋
public CDbCommand createCountCommand(CDbTableSchema $table, CDbCriteria $criteria, string $alias='t')
| ||
$table | CDbTableSchema | 琛ㄥ厓鏁版嵁銆 |
$criteria | CDbCriteria | 鏌ヨ鏉′欢銆 |
$alias | string | 涓昏〃鐨勫埆鍚嶃傞粯璁や负鈥榯鈥欍 |
{return} | CDbCommand | 鏌ヨ璇彞銆 |
public function createCountCommand($table,$criteria,$alias='t')
{
$criteria->order='';
return parent::createCountCommand($table, $criteria,$alias);
}
涓轰竴涓〃鍒涘缓COUNT(*)璇彞銆 閲嶅啓鐖剁被鐨勫疄鐜颁互绉婚櫎鏉′欢閲岀殑order瀛愬彞锛堝鏋滃畠瀛樺湪锛夈
$table | CDbTableSchema | 琛ㄥ厓鏁版嵁 |
$criteria | CDbCriteria | 鏌ヨ鏉′欢 |
{return} | CDbCommand | delete璇彞銆 |
public function createDeleteCommand($table,$criteria)
{
$criteria=$this->checkCriteria($table, $criteria);
return parent::createDeleteCommand($table, $criteria);
}
鍒涘缓DELETE璇彞銆 閲嶅啓鐖剁被鐨勫疄鐜颁互妫鏌ュ甫鏈塷ffset鐨勬煡璇腑order by瀛愬彞鏄惁宸叉寚瀹氥
public CDbCommand createFindCommand(CDbTableSchema $table, CDbCriteria $criteria, string $alias='t')
| ||
$table | CDbTableSchema | 琛ㄥ厓鏁版嵁銆 |
$criteria | CDbCriteria | 鏌ヨ鏉′欢銆 |
$alias | string | 涓昏〃鐨勫埆鍚嶃傞粯璁や负鈥榯鈥欍 |
{return} | CDbCommand | 鏌ヨ璇彞銆 |
public function createFindCommand($table,$criteria,$alias='t')
{
$criteria=$this->checkCriteria($table,$criteria);
return parent::createFindCommand($table,$criteria,$alias);
}
涓轰竴涓〃鍒涘缓SELECT璇彞銆 閲嶅啓鐖剁被鐨勫疄鐜颁互妫鏌ュ甫鏈塷ffset鐨勬煡璇腑order by瀛愬彞鏄惁宸叉寚瀹氥
$table | CDbTableSchema | 琛ㄥ厓鏁版嵁銆 |
$data | array | 瑕佹洿鏂扮殑鍒楋紙鍚=>鍊硷級 |
$criteria | CDbCriteria | 鏌ヨ鏉′欢 |
{return} | CDbCommand | update璇彞銆 |
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;
foreach($value->params as $n=>$v)
$values[$n]=$v;
}
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;
}
鍒涘缓UPDATE璇彞銆 閲嶅啓鐖剁被鐨勫疄鐜板洜涓篗SSQL涓嶅笇鏈涙洿鏂版爣璇嗗垪銆
public CDbCommand createUpdateCounterCommand(CDbTableSchema $table, CDbCriteria $counters, array $criteria)
| ||
$table | CDbTableSchema | 琛ㄥ厓鏁版嵁 |
$counters | CDbCriteria | 鏌ヨ鏉′欢 |
$criteria | array | 瑕佹洿鏂扮殑璁℃暟鍣紙鍒楀悕涓虹储寮曠殑閫掑鎴栭掑噺璁℃暟鍣級銆 |
{return} | CDbCommand | update璇彞銆 |
public function createUpdateCounterCommand($table,$counters,$criteria)
{
$criteria=$this->checkCriteria($table, $criteria);
return parent::createUpdateCounterCommand($table, $counters, $criteria);
}
鍒涘缓閫掑鎴栭掑噺鐗瑰畾鍒楃殑UPDATE璇彞銆 閲嶅啓鐖剁被鐨勫疄鐜颁互妫鏌ュ甫鏈塷ffset鐨勬煡璇腑order by瀛愬彞鏄惁宸叉寚瀹氥
protected array findOrdering(string $sql)
| ||
$sql | string | $sql |
{return} | array | 鎺掑簭琛ㄨ揪寮忎綔涓洪敭鍙婃帓搴忔柟鍚戜綔涓哄笺 |
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)
{
$name='';
foreach(explode('.', $subs[1][0]) as $p)
{
if($name!=='')
$name.='.';
$name.='[' . trim($p, '[]') . ']';
}
$ordering[$name] = $subs[2][0];
}
//else what?
}
else
$ordering[trim($part)] = 'ASC';
}
}
// replacing column names with their alias names
foreach($ordering as $name => $direction)
{
$matches = array();
$pattern = '/\s+'.str_replace(array('[',']'), array('\[','\]'), $name).'\s+AS\s+(\[[^\]]+\])/i';
preg_match($pattern, $sql, $matches);
if(isset($matches[1]))
{
$ordering[$matches[1]] = $ordering[$name];
unset($ordering[$name]);
}
}
return $ordering;
}
鍩轰簬绠鍖栬娉曪細http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx
protected string joinOrdering(array $orders, string $newPrefix)
| ||
$orders | array | 浠巉indOrdering鑾峰緱鐨刼rder淇℃伅 |
$newPrefix | string | 鍔犲湪鎺掑簭鍒楃殑鏂拌〃鍓嶇紑銆 |
{return} | string | 杩炴帴鍚庣殑order琛ㄨ揪寮忋 |
protected function joinOrdering($orders, $newPrefix)
{
if(count($orders)>0)
{
$str=array();
foreach($orders as $column => $direction)
$str[] = $column.' '.$direction;
$orderBy = 'ORDER BY '.implode(', ', $str);
return preg_replace('/\s+\[[^\]]+\]\.(\[[^\]]+\])/i', ' '.$newPrefix.'.\1', $orderBy);
}
}
protected array reverseDirection(array $orders)
| ||
$orders | array | 鍘熷order淇℃伅 |
{return} | array | 鍙嶈浆鏂瑰悜鐨刼rder淇℃伅銆 |
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鏌ヨ |
$limit | integer | $limit > 0 |
$offset | integer | $offset > 0 |
{return} | sql | 搴旂敤limit鍜宱ffset鐨勪慨鏀瑰悗SQL鏌ヨ |
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, '[__outer__]');
$reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering), '[__inner__]');
$sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner__] {$reverseOrdering}) as [__outer__] {$orginalOrdering}";
return $sql;
}
涓篗SSQL鏁版嵁搴撻噸鍐橲QL浠ュ簲鐢$limit > 0鍜$offset > 0銆 鍙傝http://troels.arvin.dk/db/rdbms/#select-limit-offset