1: <?php
2: /**
3: * CMsCommandBuilder class file.
4: *
5: * @author Qiang Xue <qiang.xue@gmail.com>
6: * @author Christophe Boulain <Christophe.Boulain@gmail.com>
7: * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
8: * @link http://www.yiiframework.com/
9: * @copyright 2008-2013 Yii Software LLC
10: * @license http://www.yiiframework.com/license/
11: */
12:
13: /**
14: * CMssqlCommandBuilder provides basic methods to create query commands for tables for Mssql Servers.
15: *
16: * @author Qiang Xue <qiang.xue@gmail.com>
17: * @author Christophe Boulain <Christophe.Boulain@gmail.com>
18: * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
19: * @package system.db.schema.mssql
20: */
21: class CMssqlCommandBuilder extends CDbCommandBuilder
22: {
23: /**
24: * Creates a COUNT(*) command for a single table.
25: * Override parent implementation to remove the order clause of criteria if it exists
26: * @param CDbTableSchema $table the table metadata
27: * @param CDbCriteria $criteria the query criteria
28: * @param string $alias the alias name of the primary table. Defaults to 't'.
29: * @return CDbCommand query command.
30: */
31: public function createCountCommand($table,$criteria,$alias='t')
32: {
33: $criteria->order='';
34: return parent::createCountCommand($table, $criteria,$alias);
35: }
36:
37: /**
38: * Creates a SELECT command for a single table.
39: * Override parent implementation to check if an orderby clause if specified when querying with an offset
40: * @param CDbTableSchema $table the table metadata
41: * @param CDbCriteria $criteria the query criteria
42: * @param string $alias the alias name of the primary table. Defaults to 't'.
43: * @return CDbCommand query command.
44: */
45: public function createFindCommand($table,$criteria,$alias='t')
46: {
47: $criteria=$this->checkCriteria($table,$criteria);
48: return parent::createFindCommand($table,$criteria,$alias);
49:
50: }
51:
52: /**
53: * Creates an UPDATE command.
54: * Override parent implementation because mssql don't want to update an identity column
55: * @param CDbTableSchema $table the table metadata
56: * @param array $data list of columns to be updated (name=>value)
57: * @param CDbCriteria $criteria the query criteria
58: * @throws CDbException if no columns are being updated
59: * @return CDbCommand update command.
60: */
61: public function createUpdateCommand($table,$data,$criteria)
62: {
63: $criteria=$this->checkCriteria($table,$criteria);
64: $fields=array();
65: $values=array();
66: $bindByPosition=isset($criteria->params[0]);
67: $i=0;
68: foreach($data as $name=>$value)
69: {
70: if(($column=$table->getColumn($name))!==null)
71: {
72: if ($table->sequenceName !== null && $column->isPrimaryKey === true) continue;
73: if ($column->dbType === 'timestamp') continue;
74: if($value instanceof CDbExpression)
75: {
76: $fields[]=$column->rawName.'='.$value->expression;
77: foreach($value->params as $n=>$v)
78: $values[$n]=$v;
79: }
80: elseif($bindByPosition)
81: {
82: $fields[]=$column->rawName.'=?';
83: $values[]=$column->typecast($value);
84: }
85: else
86: {
87: $fields[]=$column->rawName.'='.self::PARAM_PREFIX.$i;
88: $values[self::PARAM_PREFIX.$i]=$column->typecast($value);
89: $i++;
90: }
91: }
92: }
93: if($fields===array())
94: throw new CDbException(Yii::t('yii','No columns are being updated for table "{table}".',
95: array('{table}'=>$table->name)));
96: $sql="UPDATE {$table->rawName} SET ".implode(', ',$fields);
97: $sql=$this->applyJoin($sql,$criteria->join);
98: $sql=$this->applyCondition($sql,$criteria->condition);
99: $sql=$this->applyOrder($sql,$criteria->order);
100: $sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset);
101:
102: $command=$this->getDbConnection()->createCommand($sql);
103: $this->bindValues($command,array_merge($values,$criteria->params));
104:
105: return $command;
106: }
107:
108: /**
109: * Creates a DELETE command.
110: * Override parent implementation to check if an orderby clause if specified when querying with an offset
111: * @param CDbTableSchema $table the table metadata
112: * @param CDbCriteria $criteria the query criteria
113: * @return CDbCommand delete command.
114: */
115: public function createDeleteCommand($table,$criteria)
116: {
117: $criteria=$this->checkCriteria($table, $criteria);
118: return parent::createDeleteCommand($table, $criteria);
119: }
120:
121: /**
122: * Creates an UPDATE command that increments/decrements certain columns.
123: * Override parent implementation to check if an orderby clause if specified when querying with an offset
124: * @param CDbTableSchema $table the table metadata
125: * @param CDbCriteria $counters the query criteria
126: * @param array $criteria counters to be updated (counter increments/decrements indexed by column names.)
127: * @return CDbCommand the created command
128: * @throws CException if no counter is specified
129: */
130: public function createUpdateCounterCommand($table,$counters,$criteria)
131: {
132: $criteria=$this->checkCriteria($table, $criteria);
133: return parent::createUpdateCounterCommand($table, $counters, $criteria);
134: }
135:
136: /**
137: * This is a port from Prado Framework.
138: *
139: * Overrides parent implementation. Alters the sql to apply $limit and $offset.
140: * The idea for limit with offset is done by modifying the sql on the fly
141: * with numerous assumptions on the structure of the sql string.
142: * The modification is done with reference to the notes from
143: * http://troels.arvin.dk/db/rdbms/#select-limit-offset
144: *
145: * <code>
146: * SELECT * FROM (
147: * SELECT TOP n * FROM (
148: * SELECT TOP z columns -- (z=n+skip)
149: * FROM tablename
150: * ORDER BY key ASC
151: * ) AS FOO ORDER BY key DESC -- ('FOO' may be anything)
152: * ) AS BAR ORDER BY key ASC -- ('BAR' may be anything)
153: * </code>
154: *
155: * <b>Regular expressions are used to alter the SQL query. The resulting SQL query
156: * may be malformed for complex queries.</b> The following restrictions apply
157: *
158: * <ul>
159: * <li>
160: * In particular, <b>commas</b> should <b>NOT</b>
161: * be used as part of the ordering expression or identifier. Commas must only be
162: * used for separating the ordering clauses.
163: * </li>
164: * <li>
165: * In the ORDER BY clause, the column name should NOT be be qualified
166: * with a table name or view name. Alias the column names or use column index.
167: * </li>
168: * <li>
169: * No clauses should follow the ORDER BY clause, e.g. no COMPUTE or FOR clauses.
170: * </li>
171: * </ul>
172: *
173: * @param string $sql SQL query string.
174: * @param integer $limit maximum number of rows, -1 to ignore limit.
175: * @param integer $offset row offset, -1 to ignore offset.
176: * @return string SQL with limit and offset.
177: *
178: * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
179: */
180: public function applyLimit($sql, $limit, $offset)
181: {
182: $limit = $limit!==null ? (int)$limit : -1;
183: $offset = $offset!==null ? (int)$offset : -1;
184: if ($limit > 0 && $offset <= 0) //just limit
185: $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);
186: elseif($limit > 0 && $offset > 0)
187: $sql = $this->rewriteLimitOffsetSql($sql, $limit,$offset);
188: return $sql;
189: }
190:
191: /**
192: * Rewrite sql to apply $limit > and $offset > 0 for MSSQL database.
193: * See http://troels.arvin.dk/db/rdbms/#select-limit-offset
194: * @param string $sql sql query
195: * @param integer $limit $limit > 0
196: * @param integer $offset $offset > 0
197: * @return string modified sql query applied with limit and offset.
198: *
199: * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
200: */
201: protected function rewriteLimitOffsetSql($sql, $limit, $offset)
202: {
203: $fetch = $limit+$offset;
204: $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $fetch", $sql);
205: $ordering = $this->findOrdering($sql);
206: $originalOrdering = $this->joinOrdering($ordering, '[__outer__]');
207: $reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering), '[__inner__]');
208: $sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner__] {$reverseOrdering}) as [__outer__] {$originalOrdering}";
209: return $sql;
210: }
211:
212: /**
213: * Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx
214: *
215: * @param string $sql $sql
216: * @return array ordering expression as key and ordering direction as value
217: *
218: * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
219: */
220: protected function findOrdering($sql)
221: {
222: if(!preg_match('/ORDER BY/i', $sql))
223: return array();
224: $matches=array();
225: $ordering=array();
226: preg_match_all('/(ORDER BY)[\s"\[](.*)(ASC|DESC)?(?:[\s"\[]|$|COMPUTE|FOR)/i', $sql, $matches);
227: if(count($matches)>1 && count($matches[2]) > 0)
228: {
229: $parts = explode(',', $matches[2][0]);
230: foreach($parts as $part)
231: {
232: $subs=array();
233: if(preg_match_all('/(.*)[\s"\]](ASC|DESC)$/i', trim($part), $subs))
234: {
235: if(count($subs) > 1 && count($subs[2]) > 0)
236: {
237: $name='';
238: foreach(explode('.', $subs[1][0]) as $p)
239: {
240: if($name!=='')
241: $name.='.';
242: $name.='[' . trim($p, '[]') . ']';
243: }
244: $ordering[$name] = $subs[2][0];
245: }
246: //else what?
247: }
248: else
249: $ordering[trim($part)] = 'ASC';
250: }
251: }
252:
253: // replacing column names with their alias names
254: foreach($ordering as $name => $direction)
255: {
256: $matches = array();
257: $pattern = '/\s+'.str_replace(array('[',']'), array('\[','\]'), $name).'\s+AS\s+(\[[^\]]+\])/i';
258: preg_match($pattern, $sql, $matches);
259: if(isset($matches[1]))
260: {
261: $ordering[$matches[1]] = $ordering[$name];
262: unset($ordering[$name]);
263: }
264: }
265:
266: return $ordering;
267: }
268:
269: /**
270: * @param array $orders ordering obtained from findOrdering()
271: * @param string $newPrefix new table prefix to the ordering columns
272: * @return string concat the orderings
273: *
274: * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
275: */
276: protected function joinOrdering($orders, $newPrefix)
277: {
278: if(count($orders)>0)
279: {
280: $str=array();
281: foreach($orders as $column => $direction)
282: $str[] = $column.' '.$direction;
283: $orderBy = 'ORDER BY '.implode(', ', $str);
284: return preg_replace('/\s+\[[^\]]+\]\.(\[[^\]]+\])/i', ' '.$newPrefix.'.\1', $orderBy);
285: }
286: }
287:
288: /**
289: * @param array $orders original ordering
290: * @return array ordering with reversed direction.
291: *
292: * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
293: */
294: protected function reverseDirection($orders)
295: {
296: foreach($orders as $column => $direction)
297: $orders[$column] = strtolower(trim($direction))==='desc' ? 'ASC' : 'DESC';
298: return $orders;
299: }
300:
301:
302: /**
303: * Checks if the criteria has an order by clause when using offset/limit.
304: * Override parent implementation to check if an orderby clause if specified when querying with an offset
305: * If not, order it by pk.
306: * @param CMssqlTableSchema $table table schema
307: * @param CDbCriteria $criteria criteria
308: * @return CDbCriteria the modified criteria
309: */
310: protected function checkCriteria($table, $criteria)
311: {
312: if ($criteria->offset > 0 && $criteria->order==='')
313: {
314: $criteria->order=is_array($table->primaryKey)?implode(',',$table->primaryKey):$table->primaryKey;
315: }
316: return $criteria;
317: }
318:
319: /**
320: * Generates the expression for selecting rows with specified composite key values.
321: * @param CDbTableSchema $table the table schema
322: * @param array $values list of primary key values to be selected within
323: * @param string $prefix column prefix (ended with dot)
324: * @return string the expression for selection
325: */
326: protected function createCompositeInCondition($table,$values,$prefix)
327: {
328: $vs=array();
329: foreach($values as $value)
330: {
331: $c=array();
332: foreach($value as $k=>$v)
333: $c[]=$prefix.$table->columns[$k]->rawName.'='.$v;
334: $vs[]='('.implode(' AND ',$c).')';
335: }
336: return '('.implode(' OR ',$vs).')';
337: }
338: }
339: