1 | <?php |
||
2 | /** |
||
3 | * @link http://www.yiiframework.com/ |
||
4 | * @copyright Copyright (c) 2008 Yii Software LLC |
||
5 | * @license http://www.yiiframework.com/license/ |
||
6 | */ |
||
7 | |||
8 | namespace yii\db; |
||
9 | |||
10 | use yii\base\InvalidArgumentException; |
||
11 | use yii\base\NotSupportedException; |
||
12 | use yii\db\conditions\ConditionInterface; |
||
13 | use yii\db\conditions\HashCondition; |
||
14 | use yii\helpers\StringHelper; |
||
15 | |||
16 | /** |
||
17 | * QueryBuilder builds a SELECT SQL statement based on the specification given as a [[Query]] object. |
||
18 | * |
||
19 | * SQL statements are created from [[Query]] objects using the [[build()]]-method. |
||
20 | * |
||
21 | * QueryBuilder is also used by [[Command]] to build SQL statements such as INSERT, UPDATE, DELETE, CREATE TABLE. |
||
22 | * |
||
23 | * For more details and usage information on QueryBuilder, see the [guide article on query builders](guide:db-query-builder). |
||
24 | * |
||
25 | * @property-write string[] $conditionClasses Map of condition aliases to condition classes. For example: |
||
26 | * ```php ['LIKE' => yii\db\condition\LikeCondition::class] ``` . |
||
27 | * @property-write string[] $expressionBuilders Array of builders that should be merged with the pre-defined |
||
28 | * ones in [[expressionBuilders]] property. |
||
29 | * |
||
30 | * @author Qiang Xue <[email protected]> |
||
31 | * @since 2.0 |
||
32 | */ |
||
33 | class QueryBuilder extends \yii\base\BaseObject |
||
34 | { |
||
35 | /** |
||
36 | * The prefix for automatically generated query binding parameters. |
||
37 | */ |
||
38 | const PARAM_PREFIX = ':qp'; |
||
39 | |||
40 | /** |
||
41 | * @var Connection the database connection. |
||
42 | */ |
||
43 | public $db; |
||
44 | /** |
||
45 | * @var string the separator between different fragments of a SQL statement. |
||
46 | * Defaults to an empty space. This is mainly used by [[build()]] when generating a SQL statement. |
||
47 | */ |
||
48 | public $separator = ' '; |
||
49 | /** |
||
50 | * @var array the abstract column types mapped to physical column types. |
||
51 | * This is mainly used to support creating/modifying tables using DB-independent data type specifications. |
||
52 | * Child classes should override this property to declare supported type mappings. |
||
53 | */ |
||
54 | public $typeMap = []; |
||
55 | |||
56 | /** |
||
57 | * @var array map of query condition to builder methods. |
||
58 | * These methods are used by [[buildCondition]] to build SQL conditions from array syntax. |
||
59 | * @deprecated since 2.0.14. Is not used, will be dropped in 2.1.0. |
||
60 | */ |
||
61 | protected $conditionBuilders = []; |
||
62 | /** |
||
63 | * @var array map of condition aliases to condition classes. For example: |
||
64 | * |
||
65 | * ```php |
||
66 | * return [ |
||
67 | * 'LIKE' => yii\db\condition\LikeCondition::class, |
||
68 | * ]; |
||
69 | * ``` |
||
70 | * |
||
71 | * This property is used by [[createConditionFromArray]] method. |
||
72 | * See default condition classes list in [[defaultConditionClasses()]] method. |
||
73 | * |
||
74 | * In case you want to add custom conditions support, use the [[setConditionClasses()]] method. |
||
75 | * |
||
76 | * @see setConditionClasses() |
||
77 | * @see defaultConditionClasses() |
||
78 | * @since 2.0.14 |
||
79 | */ |
||
80 | protected $conditionClasses = []; |
||
81 | /** |
||
82 | * @var string[]|ExpressionBuilderInterface[] maps expression class to expression builder class. |
||
83 | * For example: |
||
84 | * |
||
85 | * ```php |
||
86 | * [ |
||
87 | * yii\db\Expression::class => yii\db\ExpressionBuilder::class |
||
88 | * ] |
||
89 | * ``` |
||
90 | * This property is mainly used by [[buildExpression()]] to build SQL expressions form expression objects. |
||
91 | * See default values in [[defaultExpressionBuilders()]] method. |
||
92 | * |
||
93 | * |
||
94 | * To override existing builders or add custom, use [[setExpressionBuilder()]] method. New items will be added |
||
95 | * to the end of this array. |
||
96 | * |
||
97 | * To find a builder, [[buildExpression()]] will check the expression class for its exact presence in this map. |
||
98 | * In case it is NOT present, the array will be iterated in reverse direction, checking whether the expression |
||
99 | * extends the class, defined in this map. |
||
100 | * |
||
101 | * @see setExpressionBuilders() |
||
102 | * @see defaultExpressionBuilders() |
||
103 | * @since 2.0.14 |
||
104 | */ |
||
105 | protected $expressionBuilders = []; |
||
106 | |||
107 | |||
108 | /** |
||
109 | * Constructor. |
||
110 | * @param Connection $connection the database connection. |
||
111 | * @param array $config name-value pairs that will be used to initialize the object properties |
||
112 | */ |
||
113 | 2596 | public function __construct($connection, $config = []) |
|
114 | { |
||
115 | 2596 | $this->db = $connection; |
|
116 | 2596 | parent::__construct($config); |
|
117 | 2596 | } |
|
118 | |||
119 | /** |
||
120 | * {@inheritdoc} |
||
121 | */ |
||
122 | 2596 | public function init() |
|
123 | { |
||
124 | 2596 | parent::init(); |
|
125 | |||
126 | 2596 | $this->expressionBuilders = array_merge($this->defaultExpressionBuilders(), $this->expressionBuilders); |
|
127 | 2596 | $this->conditionClasses = array_merge($this->defaultConditionClasses(), $this->conditionClasses); |
|
128 | 2596 | } |
|
129 | |||
130 | /** |
||
131 | * Contains array of default condition classes. Extend this method, if you want to change |
||
132 | * default condition classes for the query builder. See [[conditionClasses]] docs for details. |
||
133 | * |
||
134 | * @return array |
||
135 | * @see conditionClasses |
||
136 | * @since 2.0.14 |
||
137 | */ |
||
138 | 2596 | protected function defaultConditionClasses() |
|
139 | { |
||
140 | return [ |
||
141 | 2596 | 'NOT' => 'yii\db\conditions\NotCondition', |
|
142 | 'AND' => 'yii\db\conditions\AndCondition', |
||
143 | 'OR' => 'yii\db\conditions\OrCondition', |
||
144 | 'BETWEEN' => 'yii\db\conditions\BetweenCondition', |
||
145 | 'NOT BETWEEN' => 'yii\db\conditions\BetweenCondition', |
||
146 | 'IN' => 'yii\db\conditions\InCondition', |
||
147 | 'NOT IN' => 'yii\db\conditions\InCondition', |
||
148 | 'LIKE' => 'yii\db\conditions\LikeCondition', |
||
149 | 'NOT LIKE' => 'yii\db\conditions\LikeCondition', |
||
150 | 'OR LIKE' => 'yii\db\conditions\LikeCondition', |
||
151 | 'OR NOT LIKE' => 'yii\db\conditions\LikeCondition', |
||
152 | 'EXISTS' => 'yii\db\conditions\ExistsCondition', |
||
153 | 'NOT EXISTS' => 'yii\db\conditions\ExistsCondition', |
||
154 | ]; |
||
155 | } |
||
156 | |||
157 | /** |
||
158 | * Contains array of default expression builders. Extend this method and override it, if you want to change |
||
159 | * default expression builders for this query builder. See [[expressionBuilders]] docs for details. |
||
160 | * |
||
161 | * @return array |
||
162 | * @see expressionBuilders |
||
163 | * @since 2.0.14 |
||
164 | */ |
||
165 | 2596 | protected function defaultExpressionBuilders() |
|
166 | { |
||
167 | return [ |
||
168 | 2596 | 'yii\db\Query' => 'yii\db\QueryExpressionBuilder', |
|
169 | 'yii\db\PdoValue' => 'yii\db\PdoValueBuilder', |
||
170 | 'yii\db\Expression' => 'yii\db\ExpressionBuilder', |
||
171 | 'yii\db\conditions\ConjunctionCondition' => 'yii\db\conditions\ConjunctionConditionBuilder', |
||
172 | 'yii\db\conditions\NotCondition' => 'yii\db\conditions\NotConditionBuilder', |
||
173 | 'yii\db\conditions\AndCondition' => 'yii\db\conditions\ConjunctionConditionBuilder', |
||
174 | 'yii\db\conditions\OrCondition' => 'yii\db\conditions\ConjunctionConditionBuilder', |
||
175 | 'yii\db\conditions\BetweenCondition' => 'yii\db\conditions\BetweenConditionBuilder', |
||
176 | 'yii\db\conditions\InCondition' => 'yii\db\conditions\InConditionBuilder', |
||
177 | 'yii\db\conditions\LikeCondition' => 'yii\db\conditions\LikeConditionBuilder', |
||
178 | 'yii\db\conditions\ExistsCondition' => 'yii\db\conditions\ExistsConditionBuilder', |
||
179 | 'yii\db\conditions\SimpleCondition' => 'yii\db\conditions\SimpleConditionBuilder', |
||
180 | 'yii\db\conditions\HashCondition' => 'yii\db\conditions\HashConditionBuilder', |
||
181 | 'yii\db\conditions\BetweenColumnsCondition' => 'yii\db\conditions\BetweenColumnsConditionBuilder', |
||
182 | ]; |
||
183 | } |
||
184 | |||
185 | /** |
||
186 | * Setter for [[expressionBuilders]] property. |
||
187 | * |
||
188 | * @param string[] $builders array of builders that should be merged with the pre-defined ones |
||
189 | * in [[expressionBuilders]] property. |
||
190 | * @since 2.0.14 |
||
191 | * @see expressionBuilders |
||
192 | */ |
||
193 | 6 | public function setExpressionBuilders($builders) |
|
194 | { |
||
195 | 6 | $this->expressionBuilders = array_merge($this->expressionBuilders, $builders); |
|
196 | 6 | } |
|
197 | |||
198 | /** |
||
199 | * Setter for [[conditionClasses]] property. |
||
200 | * |
||
201 | * @param string[] $classes map of condition aliases to condition classes. For example: |
||
202 | * |
||
203 | * ```php |
||
204 | * ['LIKE' => yii\db\condition\LikeCondition::class] |
||
205 | * ``` |
||
206 | * |
||
207 | * @since 2.0.14.2 |
||
208 | * @see conditionClasses |
||
209 | */ |
||
210 | public function setConditionClasses($classes) |
||
211 | { |
||
212 | $this->conditionClasses = array_merge($this->conditionClasses, $classes); |
||
213 | } |
||
214 | |||
215 | /** |
||
216 | * Generates a SELECT SQL statement from a [[Query]] object. |
||
217 | * |
||
218 | * @param Query $query the [[Query]] object from which the SQL statement will be generated. |
||
219 | * @param array $params the parameters to be bound to the generated SQL statement. These parameters will |
||
220 | * be included in the result with the additional parameters generated during the query building process. |
||
221 | * @return array the generated SQL statement (the first array element) and the corresponding |
||
222 | * parameters to be bound to the SQL statement (the second array element). The parameters returned |
||
223 | * include those provided in `$params`. |
||
224 | */ |
||
225 | 1658 | public function build($query, $params = []) |
|
226 | { |
||
227 | 1658 | $query = $query->prepare($this); |
|
228 | |||
229 | 1658 | $params = empty($params) ? $query->params : array_merge($params, $query->params); |
|
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||
230 | |||
231 | $clauses = [ |
||
232 | 1658 | $this->buildSelect($query->select, $params, $query->distinct, $query->selectOption), |
|
233 | 1658 | $this->buildFrom($query->from, $params), |
|
234 | 1658 | $this->buildJoin($query->join, $params), |
|
235 | 1658 | $this->buildWhere($query->where, $params), |
|
236 | 1658 | $this->buildGroupBy($query->groupBy), |
|
237 | 1658 | $this->buildHaving($query->having, $params), |
|
238 | ]; |
||
239 | |||
240 | 1658 | $sql = implode($this->separator, array_filter($clauses)); |
|
241 | 1658 | $sql = $this->buildOrderByAndLimit($sql, $query->orderBy, $query->limit, $query->offset); |
|
242 | |||
243 | 1658 | if (!empty($query->orderBy)) { |
|
244 | 263 | foreach ($query->orderBy as $expression) { |
|
245 | 263 | if ($expression instanceof ExpressionInterface) { |
|
246 | 263 | $this->buildExpression($expression, $params); |
|
247 | } |
||
248 | } |
||
249 | } |
||
250 | 1658 | if (!empty($query->groupBy)) { |
|
251 | 26 | foreach ($query->groupBy as $expression) { |
|
252 | 26 | if ($expression instanceof ExpressionInterface) { |
|
253 | 26 | $this->buildExpression($expression, $params); |
|
254 | } |
||
255 | } |
||
256 | } |
||
257 | |||
258 | 1658 | $union = $this->buildUnion($query->union, $params); |
|
259 | 1658 | if ($union !== '') { |
|
260 | 24 | $sql = "($sql){$this->separator}$union"; |
|
261 | } |
||
262 | |||
263 | 1658 | $with = $this->buildWithQueries($query->withQueries, $params); |
|
264 | 1658 | if ($with !== '') { |
|
265 | 8 | $sql = "$with{$this->separator}$sql"; |
|
266 | } |
||
267 | |||
268 | 1658 | return [$sql, $params]; |
|
269 | } |
||
270 | |||
271 | /** |
||
272 | * Builds given $expression |
||
273 | * |
||
274 | * @param ExpressionInterface $expression the expression to be built |
||
275 | * @param array $params the parameters to be bound to the generated SQL statement. These parameters will |
||
276 | * be included in the result with the additional parameters generated during the expression building process. |
||
277 | * @return string the SQL statement that will not be neither quoted nor encoded before passing to DBMS |
||
278 | * @throws InvalidArgumentException when $expression building is not supported by this QueryBuilder. |
||
279 | * @see ExpressionBuilderInterface |
||
280 | * @see expressionBuilders |
||
281 | * @since 2.0.14 |
||
282 | * @see ExpressionInterface |
||
283 | */ |
||
284 | 1901 | public function buildExpression(ExpressionInterface $expression, &$params = []) |
|
285 | { |
||
286 | 1901 | $builder = $this->getExpressionBuilder($expression); |
|
287 | |||
288 | 1901 | return $builder->build($expression, $params); |
|
289 | } |
||
290 | |||
291 | /** |
||
292 | * Gets object of [[ExpressionBuilderInterface]] that is suitable for $expression. |
||
293 | * Uses [[expressionBuilders]] array to find a suitable builder class. |
||
294 | * |
||
295 | * @param ExpressionInterface $expression |
||
296 | * @return ExpressionBuilderInterface |
||
297 | * @throws InvalidArgumentException when $expression building is not supported by this QueryBuilder. |
||
298 | * @since 2.0.14 |
||
299 | * @see expressionBuilders |
||
300 | */ |
||
301 | 1907 | public function getExpressionBuilder(ExpressionInterface $expression) |
|
302 | { |
||
303 | 1907 | $className = get_class($expression); |
|
304 | |||
305 | 1907 | if (!isset($this->expressionBuilders[$className])) { |
|
306 | foreach (array_reverse($this->expressionBuilders) as $expressionClass => $builderClass) { |
||
307 | if (is_subclass_of($expression, $expressionClass)) { |
||
308 | $this->expressionBuilders[$className] = $builderClass; |
||
309 | break; |
||
310 | } |
||
311 | } |
||
312 | |||
313 | if (!isset($this->expressionBuilders[$className])) { |
||
314 | throw new InvalidArgumentException('Expression of class ' . $className . ' can not be built in ' . get_class($this)); |
||
315 | } |
||
316 | } |
||
317 | |||
318 | 1907 | if ($this->expressionBuilders[$className] === __CLASS__) { |
|
319 | return $this; |
||
320 | } |
||
321 | |||
322 | 1907 | if (!is_object($this->expressionBuilders[$className])) { |
|
323 | 1853 | $this->expressionBuilders[$className] = new $this->expressionBuilders[$className]($this); |
|
324 | } |
||
325 | |||
326 | 1907 | return $this->expressionBuilders[$className]; |
|
327 | } |
||
328 | |||
329 | /** |
||
330 | * Creates an INSERT SQL statement. |
||
331 | * For example, |
||
332 | * ```php |
||
333 | * $sql = $queryBuilder->insert('user', [ |
||
334 | * 'name' => 'Sam', |
||
335 | * 'age' => 30, |
||
336 | * ], $params); |
||
337 | * ``` |
||
338 | * The method will properly escape the table and column names. |
||
339 | * |
||
340 | * @param string $table the table that new rows will be inserted into. |
||
341 | * @param array|Query $columns the column data (name => value) to be inserted into the table or instance |
||
342 | * of [[yii\db\Query|Query]] to perform INSERT INTO ... SELECT SQL statement. |
||
343 | * Passing of [[yii\db\Query|Query]] is available since version 2.0.11. |
||
344 | * @param array $params the binding parameters that will be generated by this method. |
||
345 | * They should be bound to the DB command later. |
||
346 | * @return string the INSERT SQL |
||
347 | */ |
||
348 | 562 | public function insert($table, $columns, &$params) |
|
349 | { |
||
350 | 562 | list($names, $placeholders, $values, $params) = $this->prepareInsertValues($table, $columns, $params); |
|
351 | 553 | return 'INSERT INTO ' . $this->db->quoteTableName($table) |
|
352 | 553 | . (!empty($names) ? ' (' . implode(', ', $names) . ')' : '') |
|
353 | 553 | . (!empty($placeholders) ? ' VALUES (' . implode(', ', $placeholders) . ')' : $values); |
|
354 | } |
||
355 | |||
356 | /** |
||
357 | * Prepares a `VALUES` part for an `INSERT` SQL statement. |
||
358 | * |
||
359 | * @param string $table the table that new rows will be inserted into. |
||
360 | * @param array|Query $columns the column data (name => value) to be inserted into the table or instance |
||
361 | * of [[yii\db\Query|Query]] to perform INSERT INTO ... SELECT SQL statement. |
||
362 | * @param array $params the binding parameters that will be generated by this method. |
||
363 | * They should be bound to the DB command later. |
||
364 | * @return array array of column names, placeholders, values and params. |
||
365 | * @since 2.0.14 |
||
366 | */ |
||
367 | 765 | protected function prepareInsertValues($table, $columns, $params = []) |
|
368 | { |
||
369 | 765 | $schema = $this->db->getSchema(); |
|
370 | 765 | $tableSchema = $schema->getTableSchema($table); |
|
371 | 765 | $columnSchemas = $tableSchema !== null ? $tableSchema->columns : []; |
|
372 | 765 | $names = []; |
|
373 | 765 | $placeholders = []; |
|
374 | 765 | $values = ' DEFAULT VALUES'; |
|
375 | 765 | if ($columns instanceof Query) { |
|
376 | 70 | list($names, $values, $params) = $this->prepareInsertSelectSubQuery($columns, $schema, $params); |
|
377 | } else { |
||
378 | 705 | foreach ($columns as $name => $value) { |
|
379 | 699 | $names[] = $schema->quoteColumnName($name); |
|
380 | 699 | $value = isset($columnSchemas[$name]) ? $columnSchemas[$name]->dbTypecast($value) : $value; |
|
381 | |||
382 | 699 | if ($value instanceof ExpressionInterface) { |
|
383 | 204 | $placeholders[] = $this->buildExpression($value, $params); |
|
384 | 689 | } elseif ($value instanceof \yii\db\Query) { |
|
385 | list($sql, $params) = $this->build($value, $params); |
||
386 | $placeholders[] = "($sql)"; |
||
387 | } else { |
||
388 | 699 | $placeholders[] = $this->bindParam($value, $params); |
|
389 | } |
||
390 | } |
||
391 | } |
||
392 | 750 | return [$names, $placeholders, $values, $params]; |
|
393 | } |
||
394 | |||
395 | /** |
||
396 | * Prepare select-subquery and field names for INSERT INTO ... SELECT SQL statement. |
||
397 | * |
||
398 | * @param Query $columns Object, which represents select query. |
||
399 | * @param \yii\db\Schema $schema Schema object to quote column name. |
||
400 | * @param array $params the parameters to be bound to the generated SQL statement. These parameters will |
||
401 | * be included in the result with the additional parameters generated during the query building process. |
||
402 | * @return array array of column names, values and params. |
||
403 | * @throws InvalidArgumentException if query's select does not contain named parameters only. |
||
404 | * @since 2.0.11 |
||
405 | */ |
||
406 | 70 | protected function prepareInsertSelectSubQuery($columns, $schema, $params = []) |
|
407 | { |
||
408 | 70 | if (!is_array($columns->select) || empty($columns->select) || in_array('*', $columns->select)) { |
|
409 | 15 | throw new InvalidArgumentException('Expected select query object with enumerated (named) parameters'); |
|
410 | } |
||
411 | |||
412 | 55 | list($values, $params) = $this->build($columns, $params); |
|
413 | 55 | $names = []; |
|
414 | 55 | $values = ' ' . $values; |
|
415 | 55 | foreach ($columns->select as $title => $field) { |
|
416 | 55 | if (is_string($title)) { |
|
417 | 55 | $names[] = $schema->quoteColumnName($title); |
|
418 | } elseif (preg_match('/^(.*?)(?i:\s+as\s+|\s+)([\w\-_\.]+)$/', $field, $matches)) { |
||
419 | $names[] = $schema->quoteColumnName($matches[2]); |
||
420 | } else { |
||
421 | 55 | $names[] = $schema->quoteColumnName($field); |
|
422 | } |
||
423 | } |
||
424 | |||
425 | 55 | return [$names, $values, $params]; |
|
426 | } |
||
427 | |||
428 | /** |
||
429 | * Generates a batch INSERT SQL statement. |
||
430 | * |
||
431 | * For example, |
||
432 | * |
||
433 | * ```php |
||
434 | * $sql = $queryBuilder->batchInsert('user', ['name', 'age'], [ |
||
435 | * ['Tom', 30], |
||
436 | * ['Jane', 20], |
||
437 | * ['Linda', 25], |
||
438 | * ]); |
||
439 | * ``` |
||
440 | * |
||
441 | * Note that the values in each row must match the corresponding column names. |
||
442 | * |
||
443 | * The method will properly escape the column names, and quote the values to be inserted. |
||
444 | * |
||
445 | * @param string $table the table that new rows will be inserted into. |
||
446 | * @param array $columns the column names |
||
447 | * @param array|\Generator $rows the rows to be batch inserted into the table |
||
448 | * @param array $params the binding parameters. This parameter exists since 2.0.14 |
||
449 | * @return string the batch INSERT SQL statement |
||
450 | */ |
||
451 | 57 | public function batchInsert($table, $columns, $rows, &$params = []) |
|
452 | { |
||
453 | 57 | if (empty($rows)) { |
|
454 | 6 | return ''; |
|
455 | } |
||
456 | |||
457 | 54 | $schema = $this->db->getSchema(); |
|
458 | 54 | if (($tableSchema = $schema->getTableSchema($table)) !== null) { |
|
459 | 48 | $columnSchemas = $tableSchema->columns; |
|
460 | } else { |
||
461 | 6 | $columnSchemas = []; |
|
462 | } |
||
463 | |||
464 | 54 | $values = []; |
|
465 | 54 | foreach ($rows as $row) { |
|
466 | 48 | $vs = []; |
|
467 | 48 | foreach ($row as $i => $value) { |
|
468 | 48 | if (isset($columns[$i], $columnSchemas[$columns[$i]])) { |
|
469 | 33 | $value = $columnSchemas[$columns[$i]]->dbTypecast($value); |
|
470 | } |
||
471 | 48 | if (is_string($value)) { |
|
472 | 32 | $value = $schema->quoteValue($value); |
|
473 | 29 | } elseif (is_float($value)) { |
|
474 | // ensure type cast always has . as decimal separator in all locales |
||
475 | 2 | $value = StringHelper::floatToString($value); |
|
476 | 29 | } elseif ($value === false) { |
|
477 | 6 | $value = 0; |
|
478 | 29 | } elseif ($value === null) { |
|
479 | 16 | $value = 'NULL'; |
|
480 | 20 | } elseif ($value instanceof ExpressionInterface) { |
|
481 | 12 | $value = $this->buildExpression($value, $params); |
|
482 | } |
||
483 | 48 | $vs[] = $value; |
|
484 | } |
||
485 | 48 | $values[] = '(' . implode(', ', $vs) . ')'; |
|
486 | } |
||
487 | 54 | if (empty($values)) { |
|
488 | 6 | return ''; |
|
489 | } |
||
490 | |||
491 | 48 | foreach ($columns as $i => $name) { |
|
492 | 44 | $columns[$i] = $schema->quoteColumnName($name); |
|
493 | } |
||
494 | |||
495 | 48 | return 'INSERT INTO ' . $schema->quoteTableName($table) |
|
496 | 48 | . ' (' . implode(', ', $columns) . ') VALUES ' . implode(', ', $values); |
|
497 | } |
||
498 | |||
499 | /** |
||
500 | * Creates an SQL statement to insert rows into a database table if |
||
501 | * they do not already exist (matching unique constraints), |
||
502 | * or update them if they do. |
||
503 | * |
||
504 | * For example, |
||
505 | * |
||
506 | * ```php |
||
507 | * $sql = $queryBuilder->upsert('pages', [ |
||
508 | * 'name' => 'Front page', |
||
509 | * 'url' => 'http://example.com/', // url is unique |
||
510 | * 'visits' => 0, |
||
511 | * ], [ |
||
512 | * 'visits' => new \yii\db\Expression('visits + 1'), |
||
513 | * ], $params); |
||
514 | * ``` |
||
515 | * |
||
516 | * The method will properly escape the table and column names. |
||
517 | * |
||
518 | * @param string $table the table that new rows will be inserted into/updated in. |
||
519 | * @param array|Query $insertColumns the column data (name => value) to be inserted into the table or instance |
||
520 | * of [[Query]] to perform `INSERT INTO ... SELECT` SQL statement. |
||
521 | * @param array|bool $updateColumns the column data (name => value) to be updated if they already exist. |
||
522 | * If `true` is passed, the column data will be updated to match the insert column data. |
||
523 | * If `false` is passed, no update will be performed if the column data already exists. |
||
524 | * @param array $params the binding parameters that will be generated by this method. |
||
525 | * They should be bound to the DB command later. |
||
526 | * @return string the resulting SQL. |
||
527 | * @throws NotSupportedException if this is not supported by the underlying DBMS. |
||
528 | * @since 2.0.14 |
||
529 | */ |
||
530 | public function upsert($table, $insertColumns, $updateColumns, &$params) |
||
531 | { |
||
532 | throw new NotSupportedException($this->db->getDriverName() . ' does not support upsert statements.'); |
||
533 | } |
||
534 | |||
535 | /** |
||
536 | * @param string $table |
||
537 | * @param array|Query $insertColumns |
||
538 | * @param array|bool $updateColumns |
||
539 | * @param Constraint[] $constraints this parameter recieves a matched constraint list. |
||
540 | * The constraints will be unique by their column names. |
||
541 | * @return array |
||
542 | * @since 2.0.14 |
||
543 | */ |
||
544 | 191 | protected function prepareUpsertColumns($table, $insertColumns, $updateColumns, &$constraints = []) |
|
545 | { |
||
546 | 191 | if ($insertColumns instanceof Query) { |
|
547 | 40 | list($insertNames) = $this->prepareInsertSelectSubQuery($insertColumns, $this->db->getSchema()); |
|
548 | } else { |
||
549 | 151 | $insertNames = array_map([$this->db, 'quoteColumnName'], array_keys($insertColumns)); |
|
550 | } |
||
551 | 191 | $uniqueNames = $this->getTableUniqueColumnNames($table, $insertNames, $constraints); |
|
552 | 191 | $uniqueNames = array_map([$this->db, 'quoteColumnName'], $uniqueNames); |
|
553 | 191 | if ($updateColumns !== true) { |
|
554 | 62 | return [$uniqueNames, $insertNames, null]; |
|
555 | } |
||
556 | |||
557 | 129 | return [$uniqueNames, $insertNames, array_diff($insertNames, $uniqueNames)]; |
|
558 | } |
||
559 | |||
560 | /** |
||
561 | * Returns all column names belonging to constraints enforcing uniqueness (`PRIMARY KEY`, `UNIQUE INDEX`, etc.) |
||
562 | * for the named table removing constraints which did not cover the specified column list. |
||
563 | * The column list will be unique by column names. |
||
564 | * |
||
565 | * @param string $name table name. The table name may contain schema name if any. Do not quote the table name. |
||
566 | * @param string[] $columns source column list. |
||
567 | * @param Constraint[] $constraints this parameter optionally recieves a matched constraint list. |
||
568 | * The constraints will be unique by their column names. |
||
569 | * @return string[] column list. |
||
570 | */ |
||
571 | 191 | private function getTableUniqueColumnNames($name, $columns, &$constraints = []) |
|
572 | { |
||
573 | 191 | $schema = $this->db->getSchema(); |
|
574 | 191 | if (!$schema instanceof ConstraintFinderInterface) { |
|
575 | return []; |
||
576 | } |
||
577 | |||
578 | 191 | $constraints = []; |
|
579 | 191 | $primaryKey = $schema->getTablePrimaryKey($name); |
|
580 | 191 | if ($primaryKey !== null) { |
|
581 | 187 | $constraints[] = $primaryKey; |
|
582 | } |
||
583 | 191 | foreach ($schema->getTableIndexes($name) as $constraint) { |
|
584 | 190 | if ($constraint->isUnique) { |
|
585 | 190 | $constraints[] = $constraint; |
|
586 | } |
||
587 | } |
||
588 | 191 | $constraints = array_merge($constraints, $schema->getTableUniques($name)); |
|
589 | // Remove duplicates |
||
590 | $constraints = array_combine(array_map(function (Constraint $constraint) { |
||
591 | 191 | $columns = $constraint->columnNames; |
|
592 | 191 | sort($columns, SORT_STRING); |
|
593 | 191 | return json_encode($columns); |
|
594 | 191 | }, $constraints), $constraints); |
|
595 | 191 | $columnNames = []; |
|
596 | // Remove all constraints which do not cover the specified column list |
||
597 | $constraints = array_values(array_filter($constraints, function (Constraint $constraint) use ($schema, $columns, &$columnNames) { |
||
598 | 191 | $constraintColumnNames = array_map([$schema, 'quoteColumnName'], $constraint->columnNames); |
|
599 | 191 | $result = !array_diff($constraintColumnNames, $columns); |
|
600 | 191 | if ($result) { |
|
601 | 176 | $columnNames = array_merge($columnNames, $constraintColumnNames); |
|
602 | } |
||
603 | 191 | return $result; |
|
604 | 191 | })); |
|
605 | 191 | return array_unique($columnNames); |
|
606 | } |
||
607 | |||
608 | /** |
||
609 | * Creates an UPDATE SQL statement. |
||
610 | * |
||
611 | * For example, |
||
612 | * |
||
613 | * ```php |
||
614 | * $params = []; |
||
615 | * $sql = $queryBuilder->update('user', ['status' => 1], 'age > 30', $params); |
||
616 | * ``` |
||
617 | * |
||
618 | * The method will properly escape the table and column names. |
||
619 | * |
||
620 | * @param string $table the table to be updated. |
||
621 | * @param array $columns the column data (name => value) to be updated. |
||
622 | * @param array|string $condition the condition that will be put in the WHERE part. Please |
||
623 | * refer to [[Query::where()]] on how to specify condition. |
||
624 | * @param array $params the binding parameters that will be modified by this method |
||
625 | * so that they can be bound to the DB command later. |
||
626 | * @return string the UPDATE SQL |
||
627 | */ |
||
628 | 154 | public function update($table, $columns, $condition, &$params) |
|
629 | { |
||
630 | 154 | list($lines, $params) = $this->prepareUpdateSets($table, $columns, $params); |
|
631 | 154 | $sql = 'UPDATE ' . $this->db->quoteTableName($table) . ' SET ' . implode(', ', $lines); |
|
632 | 154 | $where = $this->buildWhere($condition, $params); |
|
633 | 154 | return $where === '' ? $sql : $sql . ' ' . $where; |
|
634 | } |
||
635 | |||
636 | /** |
||
637 | * Prepares a `SET` parts for an `UPDATE` SQL statement. |
||
638 | * @param string $table the table to be updated. |
||
639 | * @param array $columns the column data (name => value) to be updated. |
||
640 | * @param array $params the binding parameters that will be modified by this method |
||
641 | * so that they can be bound to the DB command later. |
||
642 | * @return array an array `SET` parts for an `UPDATE` SQL statement (the first array element) and params (the second array element). |
||
643 | * @since 2.0.14 |
||
644 | */ |
||
645 | 289 | protected function prepareUpdateSets($table, $columns, $params = []) |
|
646 | { |
||
647 | 289 | $tableSchema = $this->db->getTableSchema($table); |
|
648 | 289 | $columnSchemas = $tableSchema !== null ? $tableSchema->columns : []; |
|
649 | 289 | $sets = []; |
|
650 | 289 | foreach ($columns as $name => $value) { |
|
651 | |||
652 | 289 | $value = isset($columnSchemas[$name]) ? $columnSchemas[$name]->dbTypecast($value) : $value; |
|
653 | 289 | if ($value instanceof ExpressionInterface) { |
|
654 | 174 | $placeholder = $this->buildExpression($value, $params); |
|
655 | } else { |
||
656 | 148 | $placeholder = $this->bindParam($value, $params); |
|
657 | } |
||
658 | |||
659 | 289 | $sets[] = $this->db->quoteColumnName($name) . '=' . $placeholder; |
|
660 | } |
||
661 | 289 | return [$sets, $params]; |
|
662 | } |
||
663 | |||
664 | /** |
||
665 | * Creates a DELETE SQL statement. |
||
666 | * |
||
667 | * For example, |
||
668 | * |
||
669 | * ```php |
||
670 | * $sql = $queryBuilder->delete('user', 'status = 0'); |
||
671 | * ``` |
||
672 | * |
||
673 | * The method will properly escape the table and column names. |
||
674 | * |
||
675 | * @param string $table the table where the data will be deleted from. |
||
676 | * @param array|string $condition the condition that will be put in the WHERE part. Please |
||
677 | * refer to [[Query::where()]] on how to specify condition. |
||
678 | * @param array $params the binding parameters that will be modified by this method |
||
679 | * so that they can be bound to the DB command later. |
||
680 | * @return string the DELETE SQL |
||
681 | */ |
||
682 | 457 | public function delete($table, $condition, &$params) |
|
683 | { |
||
684 | 457 | $sql = 'DELETE FROM ' . $this->db->quoteTableName($table); |
|
685 | 457 | $where = $this->buildWhere($condition, $params); |
|
686 | |||
687 | 457 | return $where === '' ? $sql : $sql . ' ' . $where; |
|
688 | } |
||
689 | |||
690 | /** |
||
691 | * Builds a SQL statement for creating a new DB table. |
||
692 | * |
||
693 | * The columns in the new table should be specified as name-definition pairs (e.g. 'name' => 'string'), |
||
694 | * where name stands for a column name which will be properly quoted by the method, and definition |
||
695 | * stands for the column type which can contain an abstract DB type. |
||
696 | * The [[getColumnType()]] method will be invoked to convert any abstract type into a physical one. |
||
697 | * |
||
698 | * If a column is specified with definition only (e.g. 'PRIMARY KEY (name, type)'), it will be directly |
||
699 | * inserted into the generated SQL. |
||
700 | * |
||
701 | * For example, |
||
702 | * |
||
703 | * ```php |
||
704 | * $sql = $queryBuilder->createTable('user', [ |
||
705 | * 'id' => 'pk', |
||
706 | * 'name' => 'string', |
||
707 | * 'age' => 'integer', |
||
708 | * ]); |
||
709 | * ``` |
||
710 | * |
||
711 | * @param string $table the name of the table to be created. The name will be properly quoted by the method. |
||
712 | * @param array $columns the columns (name => definition) in the new table. |
||
713 | * @param string|null $options additional SQL fragment that will be appended to the generated SQL. |
||
714 | * @return string the SQL statement for creating a new DB table. |
||
715 | */ |
||
716 | 240 | public function createTable($table, $columns, $options = null) |
|
717 | { |
||
718 | 240 | $cols = []; |
|
719 | 240 | foreach ($columns as $name => $type) { |
|
720 | 240 | if (is_string($name)) { |
|
721 | 240 | $cols[] = "\t" . $this->db->quoteColumnName($name) . ' ' . $this->getColumnType($type); |
|
722 | } else { |
||
723 | 240 | $cols[] = "\t" . $type; |
|
724 | } |
||
725 | } |
||
726 | 240 | $sql = 'CREATE TABLE ' . $this->db->quoteTableName($table) . " (\n" . implode(",\n", $cols) . "\n)"; |
|
727 | |||
728 | 240 | return $options === null ? $sql : $sql . ' ' . $options; |
|
729 | } |
||
730 | |||
731 | /** |
||
732 | * Builds a SQL statement for renaming a DB table. |
||
733 | * @param string $oldName the table to be renamed. The name will be properly quoted by the method. |
||
734 | * @param string $newName the new table name. The name will be properly quoted by the method. |
||
735 | * @return string the SQL statement for renaming a DB table. |
||
736 | */ |
||
737 | 2 | public function renameTable($oldName, $newName) |
|
738 | { |
||
739 | 2 | return 'RENAME TABLE ' . $this->db->quoteTableName($oldName) . ' TO ' . $this->db->quoteTableName($newName); |
|
740 | } |
||
741 | |||
742 | /** |
||
743 | * Builds a SQL statement for dropping a DB table. |
||
744 | * @param string $table the table to be dropped. The name will be properly quoted by the method. |
||
745 | * @return string the SQL statement for dropping a DB table. |
||
746 | */ |
||
747 | 109 | public function dropTable($table) |
|
748 | { |
||
749 | 109 | return 'DROP TABLE ' . $this->db->quoteTableName($table); |
|
750 | } |
||
751 | |||
752 | /** |
||
753 | * Builds a SQL statement for adding a primary key constraint to an existing table. |
||
754 | * @param string $name the name of the primary key constraint. |
||
755 | * @param string $table the table that the primary key constraint will be added to. |
||
756 | * @param string|array $columns comma separated string or array of columns that the primary key will consist of. |
||
757 | * @return string the SQL statement for adding a primary key constraint to an existing table. |
||
758 | */ |
||
759 | 12 | public function addPrimaryKey($name, $table, $columns) |
|
760 | { |
||
761 | 12 | if (is_string($columns)) { |
|
762 | 8 | $columns = preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY); |
|
763 | } |
||
764 | |||
765 | 12 | foreach ($columns as $i => $col) { |
|
766 | 12 | $columns[$i] = $this->db->quoteColumnName($col); |
|
767 | } |
||
768 | |||
769 | 12 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ADD CONSTRAINT ' |
|
770 | 12 | . $this->db->quoteColumnName($name) . ' PRIMARY KEY (' |
|
771 | 12 | . implode(', ', $columns) . ')'; |
|
772 | } |
||
773 | |||
774 | /** |
||
775 | * Builds a SQL statement for removing a primary key constraint to an existing table. |
||
776 | * @param string $name the name of the primary key constraint to be removed. |
||
777 | * @param string $table the table that the primary key constraint will be removed from. |
||
778 | * @return string the SQL statement for removing a primary key constraint from an existing table. |
||
779 | */ |
||
780 | 6 | public function dropPrimaryKey($name, $table) |
|
781 | { |
||
782 | 6 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) |
|
783 | 6 | . ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name); |
|
784 | } |
||
785 | |||
786 | /** |
||
787 | * Builds a SQL statement for truncating a DB table. |
||
788 | * @param string $table the table to be truncated. The name will be properly quoted by the method. |
||
789 | * @return string the SQL statement for truncating a DB table. |
||
790 | */ |
||
791 | 21 | public function truncateTable($table) |
|
792 | { |
||
793 | 21 | return 'TRUNCATE TABLE ' . $this->db->quoteTableName($table); |
|
794 | } |
||
795 | |||
796 | /** |
||
797 | * Builds a SQL statement for adding a new DB column. |
||
798 | * @param string $table the table that the new column will be added to. The table name will be properly quoted by the method. |
||
799 | * @param string $column the name of the new column. The name will be properly quoted by the method. |
||
800 | * @param string $type the column type. The [[getColumnType()]] method will be invoked to convert abstract column type (if any) |
||
801 | * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. |
||
802 | * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'. |
||
803 | * @return string the SQL statement for adding a new column. |
||
804 | */ |
||
805 | 9 | public function addColumn($table, $column, $type) |
|
806 | { |
||
807 | 9 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) |
|
808 | 9 | . ' ADD ' . $this->db->quoteColumnName($column) . ' ' |
|
809 | 9 | . $this->getColumnType($type); |
|
810 | } |
||
811 | |||
812 | /** |
||
813 | * Builds a SQL statement for dropping a DB column. |
||
814 | * @param string $table the table whose column is to be dropped. The name will be properly quoted by the method. |
||
815 | * @param string $column the name of the column to be dropped. The name will be properly quoted by the method. |
||
816 | * @return string the SQL statement for dropping a DB column. |
||
817 | */ |
||
818 | public function dropColumn($table, $column) |
||
819 | { |
||
820 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) |
||
821 | . ' DROP COLUMN ' . $this->db->quoteColumnName($column); |
||
822 | } |
||
823 | |||
824 | /** |
||
825 | * Builds a SQL statement for renaming a column. |
||
826 | * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method. |
||
827 | * @param string $oldName the old name of the column. The name will be properly quoted by the method. |
||
828 | * @param string $newName the new name of the column. The name will be properly quoted by the method. |
||
829 | * @return string the SQL statement for renaming a DB column. |
||
830 | */ |
||
831 | public function renameColumn($table, $oldName, $newName) |
||
832 | { |
||
833 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) |
||
834 | . ' RENAME COLUMN ' . $this->db->quoteColumnName($oldName) |
||
835 | . ' TO ' . $this->db->quoteColumnName($newName); |
||
836 | } |
||
837 | |||
838 | /** |
||
839 | * Builds a SQL statement for changing the definition of a column. |
||
840 | * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method. |
||
841 | * @param string $column the name of the column to be changed. The name will be properly quoted by the method. |
||
842 | * @param string $type the new column type. The [[getColumnType()]] method will be invoked to convert abstract |
||
843 | * column type (if any) into the physical one. Anything that is not recognized as abstract type will be kept |
||
844 | * in the generated SQL. For example, 'string' will be turned into 'varchar(255)', while 'string not null' |
||
845 | * will become 'varchar(255) not null'. |
||
846 | * @return string the SQL statement for changing the definition of a column. |
||
847 | */ |
||
848 | 1 | public function alterColumn($table, $column, $type) |
|
849 | { |
||
850 | 1 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' CHANGE ' |
|
851 | 1 | . $this->db->quoteColumnName($column) . ' ' |
|
852 | 1 | . $this->db->quoteColumnName($column) . ' ' |
|
853 | 1 | . $this->getColumnType($type); |
|
854 | } |
||
855 | |||
856 | /** |
||
857 | * Builds a SQL statement for adding a foreign key constraint to an existing table. |
||
858 | * The method will properly quote the table and column names. |
||
859 | * @param string $name the name of the foreign key constraint. |
||
860 | * @param string $table the table that the foreign key constraint will be added to. |
||
861 | * @param string|array $columns the name of the column to that the constraint will be added on. |
||
862 | * If there are multiple columns, separate them with commas or use an array to represent them. |
||
863 | * @param string $refTable the table that the foreign key references to. |
||
864 | * @param string|array $refColumns the name of the column that the foreign key references to. |
||
865 | * If there are multiple columns, separate them with commas or use an array to represent them. |
||
866 | * @param string|null $delete the ON DELETE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL |
||
867 | * @param string|null $update the ON UPDATE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL |
||
868 | * @return string the SQL statement for adding a foreign key constraint to an existing table. |
||
869 | */ |
||
870 | 14 | public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null) |
|
871 | { |
||
872 | 14 | $sql = 'ALTER TABLE ' . $this->db->quoteTableName($table) |
|
873 | 14 | . ' ADD CONSTRAINT ' . $this->db->quoteColumnName($name) |
|
874 | 14 | . ' FOREIGN KEY (' . $this->buildColumns($columns) . ')' |
|
875 | 14 | . ' REFERENCES ' . $this->db->quoteTableName($refTable) |
|
876 | 14 | . ' (' . $this->buildColumns($refColumns) . ')'; |
|
877 | 14 | if ($delete !== null) { |
|
878 | 8 | $sql .= ' ON DELETE ' . $delete; |
|
879 | } |
||
880 | 14 | if ($update !== null) { |
|
881 | 8 | $sql .= ' ON UPDATE ' . $update; |
|
882 | } |
||
883 | |||
884 | 14 | return $sql; |
|
885 | } |
||
886 | |||
887 | /** |
||
888 | * Builds a SQL statement for dropping a foreign key constraint. |
||
889 | * @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted by the method. |
||
890 | * @param string $table the table whose foreign is to be dropped. The name will be properly quoted by the method. |
||
891 | * @return string the SQL statement for dropping a foreign key constraint. |
||
892 | */ |
||
893 | 7 | public function dropForeignKey($name, $table) |
|
894 | { |
||
895 | 7 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) |
|
896 | 7 | . ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name); |
|
897 | } |
||
898 | |||
899 | /** |
||
900 | * Builds a SQL statement for creating a new index. |
||
901 | * @param string $name the name of the index. The name will be properly quoted by the method. |
||
902 | * @param string $table the table that the new index will be created for. The table name will be properly quoted by the method. |
||
903 | * @param string|array $columns the column(s) that should be included in the index. If there are multiple columns, |
||
904 | * separate them with commas or use an array to represent them. Each column name will be properly quoted |
||
905 | * by the method, unless a parenthesis is found in the name. |
||
906 | * @param bool $unique whether to add UNIQUE constraint on the created index. |
||
907 | * @return string the SQL statement for creating a new index. |
||
908 | */ |
||
909 | 10 | public function createIndex($name, $table, $columns, $unique = false) |
|
910 | { |
||
911 | 10 | return ($unique ? 'CREATE UNIQUE INDEX ' : 'CREATE INDEX ') |
|
912 | 10 | . $this->db->quoteTableName($name) . ' ON ' |
|
913 | 10 | . $this->db->quoteTableName($table) |
|
914 | 10 | . ' (' . $this->buildColumns($columns) . ')'; |
|
915 | } |
||
916 | |||
917 | /** |
||
918 | * Builds a SQL statement for dropping an index. |
||
919 | * @param string $name the name of the index to be dropped. The name will be properly quoted by the method. |
||
920 | * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method. |
||
921 | * @return string the SQL statement for dropping an index. |
||
922 | */ |
||
923 | 8 | public function dropIndex($name, $table) |
|
924 | { |
||
925 | 8 | return 'DROP INDEX ' . $this->db->quoteTableName($name) . ' ON ' . $this->db->quoteTableName($table); |
|
926 | } |
||
927 | |||
928 | /** |
||
929 | * Creates a SQL command for adding an unique constraint to an existing table. |
||
930 | * @param string $name the name of the unique constraint. |
||
931 | * The name will be properly quoted by the method. |
||
932 | * @param string $table the table that the unique constraint will be added to. |
||
933 | * The name will be properly quoted by the method. |
||
934 | * @param string|array $columns the name of the column to that the constraint will be added on. |
||
935 | * If there are multiple columns, separate them with commas. |
||
936 | * The name will be properly quoted by the method. |
||
937 | * @return string the SQL statement for adding an unique constraint to an existing table. |
||
938 | * @since 2.0.13 |
||
939 | */ |
||
940 | 12 | public function addUnique($name, $table, $columns) |
|
941 | { |
||
942 | 12 | if (is_string($columns)) { |
|
943 | 8 | $columns = preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY); |
|
944 | } |
||
945 | 12 | foreach ($columns as $i => $col) { |
|
946 | 12 | $columns[$i] = $this->db->quoteColumnName($col); |
|
947 | } |
||
948 | |||
949 | 12 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ADD CONSTRAINT ' |
|
950 | 12 | . $this->db->quoteColumnName($name) . ' UNIQUE (' |
|
951 | 12 | . implode(', ', $columns) . ')'; |
|
952 | } |
||
953 | |||
954 | /** |
||
955 | * Creates a SQL command for dropping an unique constraint. |
||
956 | * @param string $name the name of the unique constraint to be dropped. |
||
957 | * The name will be properly quoted by the method. |
||
958 | * @param string $table the table whose unique constraint is to be dropped. |
||
959 | * The name will be properly quoted by the method. |
||
960 | * @return string the SQL statement for dropping an unique constraint. |
||
961 | * @since 2.0.13 |
||
962 | */ |
||
963 | 6 | public function dropUnique($name, $table) |
|
964 | { |
||
965 | 6 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) |
|
966 | 6 | . ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name); |
|
967 | } |
||
968 | |||
969 | /** |
||
970 | * Creates a SQL command for adding a check constraint to an existing table. |
||
971 | * @param string $name the name of the check constraint. |
||
972 | * The name will be properly quoted by the method. |
||
973 | * @param string $table the table that the check constraint will be added to. |
||
974 | * The name will be properly quoted by the method. |
||
975 | * @param string $expression the SQL of the `CHECK` constraint. |
||
976 | * @return string the SQL statement for adding a check constraint to an existing table. |
||
977 | * @since 2.0.13 |
||
978 | */ |
||
979 | 6 | public function addCheck($name, $table, $expression) |
|
980 | { |
||
981 | 6 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ADD CONSTRAINT ' |
|
982 | 6 | . $this->db->quoteColumnName($name) . ' CHECK (' . $this->db->quoteSql($expression) . ')'; |
|
983 | } |
||
984 | |||
985 | /** |
||
986 | * Creates a SQL command for dropping a check constraint. |
||
987 | * @param string $name the name of the check constraint to be dropped. |
||
988 | * The name will be properly quoted by the method. |
||
989 | * @param string $table the table whose check constraint is to be dropped. |
||
990 | * The name will be properly quoted by the method. |
||
991 | * @return string the SQL statement for dropping a check constraint. |
||
992 | * @since 2.0.13 |
||
993 | */ |
||
994 | 6 | public function dropCheck($name, $table) |
|
995 | { |
||
996 | 6 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) |
|
997 | 6 | . ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name); |
|
998 | } |
||
999 | |||
1000 | /** |
||
1001 | * Creates a SQL command for adding a default value constraint to an existing table. |
||
1002 | * @param string $name the name of the default value constraint. |
||
1003 | * The name will be properly quoted by the method. |
||
1004 | * @param string $table the table that the default value constraint will be added to. |
||
1005 | * The name will be properly quoted by the method. |
||
1006 | * @param string $column the name of the column to that the constraint will be added on. |
||
1007 | * The name will be properly quoted by the method. |
||
1008 | * @param mixed $value default value. |
||
1009 | * @return string the SQL statement for adding a default value constraint to an existing table. |
||
1010 | * @throws NotSupportedException if this is not supported by the underlying DBMS. |
||
1011 | * @since 2.0.13 |
||
1012 | */ |
||
1013 | public function addDefaultValue($name, $table, $column, $value) |
||
1014 | { |
||
1015 | throw new NotSupportedException($this->db->getDriverName() . ' does not support adding default value constraints.'); |
||
1016 | } |
||
1017 | |||
1018 | /** |
||
1019 | * Creates a SQL command for dropping a default value constraint. |
||
1020 | * @param string $name the name of the default value constraint to be dropped. |
||
1021 | * The name will be properly quoted by the method. |
||
1022 | * @param string $table the table whose default value constraint is to be dropped. |
||
1023 | * The name will be properly quoted by the method. |
||
1024 | * @return string the SQL statement for dropping a default value constraint. |
||
1025 | * @throws NotSupportedException if this is not supported by the underlying DBMS. |
||
1026 | * @since 2.0.13 |
||
1027 | */ |
||
1028 | public function dropDefaultValue($name, $table) |
||
1029 | { |
||
1030 | throw new NotSupportedException($this->db->getDriverName() . ' does not support dropping default value constraints.'); |
||
1031 | } |
||
1032 | |||
1033 | /** |
||
1034 | * Creates a SQL statement for resetting the sequence value of a table's primary key. |
||
1035 | * The sequence will be reset such that the primary key of the next new row inserted |
||
1036 | * will have the specified value or the maximum existing value +1. |
||
1037 | * @param string $table the name of the table whose primary key sequence will be reset |
||
1038 | * @param array|string|null $value the value for the primary key of the next new row inserted. If this is not set, |
||
1039 | * the next new row's primary key will have the maximum existing value +1. |
||
1040 | * @return string the SQL statement for resetting sequence |
||
1041 | * @throws NotSupportedException if this is not supported by the underlying DBMS |
||
1042 | */ |
||
1043 | public function resetSequence($table, $value = null) |
||
1044 | { |
||
1045 | throw new NotSupportedException($this->db->getDriverName() . ' does not support resetting sequence.'); |
||
1046 | } |
||
1047 | |||
1048 | /** |
||
1049 | * Execute a SQL statement for resetting the sequence value of a table's primary key. |
||
1050 | * Reason for execute is that some databases (Oracle) need several queries to do so. |
||
1051 | * The sequence is reset such that the primary key of the next new row inserted |
||
1052 | * will have the specified value or the maximum existing value +1. |
||
1053 | * @param string $table the name of the table whose primary key sequence is reset |
||
1054 | * @param array|string|null $value the value for the primary key of the next new row inserted. If this is not set, |
||
1055 | * the next new row's primary key will have the maximum existing value +1. |
||
1056 | * @throws NotSupportedException if this is not supported by the underlying DBMS |
||
1057 | * @since 2.0.16 |
||
1058 | */ |
||
1059 | 35 | public function executeResetSequence($table, $value = null) |
|
1060 | { |
||
1061 | 35 | $this->db->createCommand()->resetSequence($table, $value)->execute(); |
|
1062 | 35 | } |
|
1063 | |||
1064 | /** |
||
1065 | * Builds a SQL statement for enabling or disabling integrity check. |
||
1066 | * @param bool $check whether to turn on or off the integrity check. |
||
1067 | * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema. |
||
1068 | * @param string $table the table name. Defaults to empty string, meaning that no table will be changed. |
||
1069 | * @return string the SQL statement for checking integrity |
||
1070 | * @throws NotSupportedException if this is not supported by the underlying DBMS |
||
1071 | */ |
||
1072 | public function checkIntegrity($check = true, $schema = '', $table = '') |
||
1073 | { |
||
1074 | throw new NotSupportedException($this->db->getDriverName() . ' does not support enabling/disabling integrity check.'); |
||
1075 | } |
||
1076 | |||
1077 | /** |
||
1078 | * Builds a SQL command for adding comment to column. |
||
1079 | * |
||
1080 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the method. |
||
1081 | * @param string $column the name of the column to be commented. The column name will be properly quoted by the method. |
||
1082 | * @param string $comment the text of the comment to be added. The comment will be properly quoted by the method. |
||
1083 | * @return string the SQL statement for adding comment on column |
||
1084 | * @since 2.0.8 |
||
1085 | */ |
||
1086 | 2 | public function addCommentOnColumn($table, $column, $comment) |
|
1087 | { |
||
1088 | 2 | return 'COMMENT ON COLUMN ' . $this->db->quoteTableName($table) . '.' . $this->db->quoteColumnName($column) . ' IS ' . $this->db->quoteValue($comment); |
|
1089 | } |
||
1090 | |||
1091 | /** |
||
1092 | * Builds a SQL command for adding comment to table. |
||
1093 | * |
||
1094 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the method. |
||
1095 | * @param string $comment the text of the comment to be added. The comment will be properly quoted by the method. |
||
1096 | * @return string the SQL statement for adding comment on table |
||
1097 | * @since 2.0.8 |
||
1098 | */ |
||
1099 | 1 | public function addCommentOnTable($table, $comment) |
|
1100 | { |
||
1101 | 1 | return 'COMMENT ON TABLE ' . $this->db->quoteTableName($table) . ' IS ' . $this->db->quoteValue($comment); |
|
1102 | } |
||
1103 | |||
1104 | /** |
||
1105 | * Builds a SQL command for adding comment to column. |
||
1106 | * |
||
1107 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the method. |
||
1108 | * @param string $column the name of the column to be commented. The column name will be properly quoted by the method. |
||
1109 | * @return string the SQL statement for adding comment on column |
||
1110 | * @since 2.0.8 |
||
1111 | */ |
||
1112 | 2 | public function dropCommentFromColumn($table, $column) |
|
1113 | { |
||
1114 | 2 | return 'COMMENT ON COLUMN ' . $this->db->quoteTableName($table) . '.' . $this->db->quoteColumnName($column) . ' IS NULL'; |
|
1115 | } |
||
1116 | |||
1117 | /** |
||
1118 | * Builds a SQL command for adding comment to table. |
||
1119 | * |
||
1120 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the method. |
||
1121 | * @return string the SQL statement for adding comment on column |
||
1122 | * @since 2.0.8 |
||
1123 | */ |
||
1124 | 1 | public function dropCommentFromTable($table) |
|
1125 | { |
||
1126 | 1 | return 'COMMENT ON TABLE ' . $this->db->quoteTableName($table) . ' IS NULL'; |
|
1127 | } |
||
1128 | |||
1129 | /** |
||
1130 | * Creates a SQL View. |
||
1131 | * |
||
1132 | * @param string $viewName the name of the view to be created. |
||
1133 | * @param string|Query $subQuery the select statement which defines the view. |
||
1134 | * This can be either a string or a [[Query]] object. |
||
1135 | * @return string the `CREATE VIEW` SQL statement. |
||
1136 | * @since 2.0.14 |
||
1137 | */ |
||
1138 | 5 | public function createView($viewName, $subQuery) |
|
1139 | { |
||
1140 | 5 | if ($subQuery instanceof Query) { |
|
1141 | 5 | list($rawQuery, $params) = $this->build($subQuery); |
|
1142 | 5 | array_walk( |
|
1143 | 5 | $params, |
|
1144 | 5 | function (&$param) { |
|
1145 | 5 | $param = $this->db->quoteValue($param); |
|
1146 | 5 | } |
|
1147 | ); |
||
1148 | 5 | $subQuery = strtr($rawQuery, $params); |
|
1149 | } |
||
1150 | |||
1151 | 5 | return 'CREATE VIEW ' . $this->db->quoteTableName($viewName) . ' AS ' . $subQuery; |
|
1152 | } |
||
1153 | |||
1154 | /** |
||
1155 | * Drops a SQL View. |
||
1156 | * |
||
1157 | * @param string $viewName the name of the view to be dropped. |
||
1158 | * @return string the `DROP VIEW` SQL statement. |
||
1159 | * @since 2.0.14 |
||
1160 | */ |
||
1161 | 7 | public function dropView($viewName) |
|
1162 | { |
||
1163 | 7 | return 'DROP VIEW ' . $this->db->quoteTableName($viewName); |
|
1164 | } |
||
1165 | |||
1166 | /** |
||
1167 | * Converts an abstract column type into a physical column type. |
||
1168 | * |
||
1169 | * The conversion is done using the type map specified in [[typeMap]]. |
||
1170 | * The following abstract column types are supported (using MySQL as an example to explain the corresponding |
||
1171 | * physical types): |
||
1172 | * |
||
1173 | * - `pk`: an auto-incremental primary key type, will be converted into "int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY" |
||
1174 | * - `bigpk`: an auto-incremental primary key type, will be converted into "bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY" |
||
1175 | * - `upk`: an unsigned auto-incremental primary key type, will be converted into "int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY" |
||
1176 | * - `char`: char type, will be converted into "char(1)" |
||
1177 | * - `string`: string type, will be converted into "varchar(255)" |
||
1178 | * - `text`: a long string type, will be converted into "text" |
||
1179 | * - `smallint`: a small integer type, will be converted into "smallint(6)" |
||
1180 | * - `integer`: integer type, will be converted into "int(11)" |
||
1181 | * - `bigint`: a big integer type, will be converted into "bigint(20)" |
||
1182 | * - `boolean`: boolean type, will be converted into "tinyint(1)" |
||
1183 | * - `float``: float number type, will be converted into "float" |
||
1184 | * - `decimal`: decimal number type, will be converted into "decimal" |
||
1185 | * - `datetime`: datetime type, will be converted into "datetime" |
||
1186 | * - `timestamp`: timestamp type, will be converted into "timestamp" |
||
1187 | * - `time`: time type, will be converted into "time" |
||
1188 | * - `date`: date type, will be converted into "date" |
||
1189 | * - `money`: money type, will be converted into "decimal(19,4)" |
||
1190 | * - `binary`: binary data type, will be converted into "blob" |
||
1191 | * |
||
1192 | * If the abstract type contains two or more parts separated by spaces (e.g. "string NOT NULL"), then only |
||
1193 | * the first part will be converted, and the rest of the parts will be appended to the converted result. |
||
1194 | * For example, 'string NOT NULL' is converted to 'varchar(255) NOT NULL'. |
||
1195 | * |
||
1196 | * For some of the abstract types you can also specify a length or precision constraint |
||
1197 | * by appending it in round brackets directly to the type. |
||
1198 | * For example `string(32)` will be converted into "varchar(32)" on a MySQL database. |
||
1199 | * If the underlying DBMS does not support these kind of constraints for a type it will |
||
1200 | * be ignored. |
||
1201 | * |
||
1202 | * If a type cannot be found in [[typeMap]], it will be returned without any change. |
||
1203 | * @param string|ColumnSchemaBuilder $type abstract column type |
||
1204 | * @return string physical column type. |
||
1205 | */ |
||
1206 | 261 | public function getColumnType($type) |
|
1207 | { |
||
1208 | 261 | if ($type instanceof ColumnSchemaBuilder) { |
|
1209 | 82 | $type = $type->__toString(); |
|
1210 | } |
||
1211 | |||
1212 | 261 | if (isset($this->typeMap[$type])) { |
|
1213 | 186 | return $this->typeMap[$type]; |
|
1214 | 177 | } elseif (preg_match('/^(\w+)\((.+?)\)(.*)$/', $type, $matches)) { |
|
1215 | 97 | if (isset($this->typeMap[$matches[1]])) { |
|
1216 | 97 | return preg_replace('/\(.+\)/', '(' . $matches[2] . ')', $this->typeMap[$matches[1]]) . $matches[3]; |
|
1217 | } |
||
1218 | 138 | } elseif (preg_match('/^(\w+)\s+/', $type, $matches)) { |
|
1219 | 97 | if (isset($this->typeMap[$matches[1]])) { |
|
1220 | 97 | return preg_replace('/^\w+/', $this->typeMap[$matches[1]], $type); |
|
1221 | } |
||
1222 | } |
||
1223 | |||
1224 | 78 | return $type; |
|
1225 | } |
||
1226 | |||
1227 | /** |
||
1228 | * @param array $columns |
||
1229 | * @param array $params the binding parameters to be populated |
||
1230 | * @param bool $distinct |
||
1231 | * @param string|null $selectOption |
||
1232 | * @return string the SELECT clause built from [[Query::$select]]. |
||
1233 | */ |
||
1234 | 2069 | public function buildSelect($columns, &$params, $distinct = false, $selectOption = null) |
|
1235 | { |
||
1236 | 2069 | $select = $distinct ? 'SELECT DISTINCT' : 'SELECT'; |
|
1237 | 2069 | if ($selectOption !== null) { |
|
1238 | $select .= ' ' . $selectOption; |
||
1239 | } |
||
1240 | |||
1241 | 2069 | if (empty($columns)) { |
|
1242 | 1626 | return $select . ' *'; |
|
1243 | } |
||
1244 | |||
1245 | 895 | foreach ($columns as $i => $column) { |
|
1246 | 895 | if ($column instanceof ExpressionInterface) { |
|
1247 | 70 | if (is_int($i)) { |
|
1248 | 10 | $columns[$i] = $this->buildExpression($column, $params); |
|
1249 | } else { |
||
1250 | 70 | $columns[$i] = $this->buildExpression($column, $params) . ' AS ' . $this->db->quoteColumnName($i); |
|
1251 | } |
||
1252 | 880 | } elseif ($column instanceof Query) { |
|
1253 | list($sql, $params) = $this->build($column, $params); |
||
1254 | $columns[$i] = "($sql) AS " . $this->db->quoteColumnName($i); |
||
1255 | 880 | } elseif (is_string($i) && $i !== $column) { |
|
1256 | 93 | if (strpos($column, '(') === false) { |
|
1257 | 46 | $column = $this->db->quoteColumnName($column); |
|
1258 | } |
||
1259 | 93 | $columns[$i] = "$column AS " . $this->db->quoteColumnName($i); |
|
1260 | 839 | } elseif (strpos($column, '(') === false) { |
|
1261 | 687 | if (preg_match('/^(.*?)(?i:\s+as\s+|\s+)([\w\-_\.]+)$/', $column, $matches)) { |
|
1262 | $columns[$i] = $this->db->quoteColumnName($matches[1]) . ' AS ' . $this->db->quoteColumnName($matches[2]); |
||
1263 | } else { |
||
1264 | 895 | $columns[$i] = $this->db->quoteColumnName($column); |
|
1265 | } |
||
1266 | } |
||
1267 | } |
||
1268 | |||
1269 | 895 | return $select . ' ' . implode(', ', $columns); |
|
1270 | } |
||
1271 | |||
1272 | /** |
||
1273 | * @param array $tables |
||
1274 | * @param array $params the binding parameters to be populated |
||
1275 | * @return string the FROM clause built from [[Query::$from]]. |
||
1276 | */ |
||
1277 | 2101 | public function buildFrom($tables, &$params) |
|
1278 | { |
||
1279 | 2101 | if (empty($tables)) { |
|
1280 | 668 | return ''; |
|
1281 | } |
||
1282 | |||
1283 | 1493 | $tables = $this->quoteTableNames($tables, $params); |
|
1284 | |||
1285 | 1493 | return 'FROM ' . implode(', ', $tables); |
|
1286 | } |
||
1287 | |||
1288 | /** |
||
1289 | * @param array $joins |
||
1290 | * @param array $params the binding parameters to be populated |
||
1291 | * @return string the JOIN clause built from [[Query::$join]]. |
||
1292 | * @throws Exception if the $joins parameter is not in proper format |
||
1293 | */ |
||
1294 | 2069 | public function buildJoin($joins, &$params) |
|
1295 | { |
||
1296 | 2069 | if (empty($joins)) { |
|
1297 | 2053 | return ''; |
|
1298 | } |
||
1299 | |||
1300 | 146 | foreach ($joins as $i => $join) { |
|
1301 | 146 | if (!is_array($join) || !isset($join[0], $join[1])) { |
|
1302 | throw new Exception('A join clause must be specified as an array of join type, join table, and optionally join condition.'); |
||
1303 | } |
||
1304 | // 0:join type, 1:join table, 2:on-condition (optional) |
||
1305 | 146 | list($joinType, $table) = $join; |
|
1306 | 146 | $tables = $this->quoteTableNames((array)$table, $params); |
|
1307 | 146 | $table = reset($tables); |
|
1308 | 146 | $joins[$i] = "$joinType $table"; |
|
1309 | 146 | if (isset($join[2])) { |
|
1310 | 146 | $condition = $this->buildCondition($join[2], $params); |
|
1311 | 146 | if ($condition !== '') { |
|
1312 | 146 | $joins[$i] .= ' ON ' . $condition; |
|
1313 | } |
||
1314 | } |
||
1315 | } |
||
1316 | |||
1317 | 146 | return implode($this->separator, $joins); |
|
1318 | } |
||
1319 | |||
1320 | /** |
||
1321 | * Quotes table names passed. |
||
1322 | * |
||
1323 | * @param array $tables |
||
1324 | * @param array $params |
||
1325 | * @return array |
||
1326 | */ |
||
1327 | 1493 | private function quoteTableNames($tables, &$params) |
|
1328 | { |
||
1329 | 1493 | foreach ($tables as $i => $table) { |
|
1330 | 1493 | if ($table instanceof Query) { |
|
1331 | 18 | list($sql, $params) = $this->build($table, $params); |
|
1332 | 18 | $tables[$i] = "($sql) " . $this->db->quoteTableName($i); |
|
1333 | 1493 | } elseif (is_string($i)) { |
|
1334 | 160 | if (strpos($table, '(') === false) { |
|
1335 | 139 | $table = $this->db->quoteTableName($table); |
|
1336 | } |
||
1337 | 160 | $tables[$i] = "$table " . $this->db->quoteTableName($i); |
|
1338 | 1421 | } elseif (strpos($table, '(') === false) { |
|
1339 | 1410 | if ($tableWithAlias = $this->extractAlias($table)) { // with alias |
|
1340 | 53 | $tables[$i] = $this->db->quoteTableName($tableWithAlias[1]) . ' ' . $this->db->quoteTableName($tableWithAlias[2]); |
|
1341 | } else { |
||
1342 | 1493 | $tables[$i] = $this->db->quoteTableName($table); |
|
1343 | } |
||
1344 | } |
||
1345 | } |
||
1346 | |||
1347 | 1493 | return $tables; |
|
1348 | } |
||
1349 | |||
1350 | /** |
||
1351 | * @param string|array $condition |
||
1352 | * @param array $params the binding parameters to be populated |
||
1353 | * @return string the WHERE clause built from [[Query::$where]]. |
||
1354 | */ |
||
1355 | 2178 | public function buildWhere($condition, &$params) |
|
1356 | { |
||
1357 | 2178 | $where = $this->buildCondition($condition, $params); |
|
1358 | |||
1359 | 2178 | return $where === '' ? '' : 'WHERE ' . $where; |
|
1360 | } |
||
1361 | |||
1362 | /** |
||
1363 | * @param array $columns |
||
1364 | * @return string the GROUP BY clause |
||
1365 | */ |
||
1366 | 2069 | public function buildGroupBy($columns) |
|
1367 | { |
||
1368 | 2069 | if (empty($columns)) { |
|
1369 | 2059 | return ''; |
|
1370 | } |
||
1371 | 29 | foreach ($columns as $i => $column) { |
|
1372 | 29 | if ($column instanceof ExpressionInterface) { |
|
1373 | 5 | $columns[$i] = $this->buildExpression($column); |
|
1374 | 29 | } elseif (strpos($column, '(') === false) { |
|
1375 | 29 | $columns[$i] = $this->db->quoteColumnName($column); |
|
1376 | } |
||
1377 | } |
||
1378 | |||
1379 | 29 | return 'GROUP BY ' . implode(', ', $columns); |
|
1380 | } |
||
1381 | |||
1382 | /** |
||
1383 | * @param string|array $condition |
||
1384 | * @param array $params the binding parameters to be populated |
||
1385 | * @return string the HAVING clause built from [[Query::$having]]. |
||
1386 | */ |
||
1387 | 2069 | public function buildHaving($condition, &$params) |
|
1388 | { |
||
1389 | 2069 | $having = $this->buildCondition($condition, $params); |
|
1390 | |||
1391 | 2069 | return $having === '' ? '' : 'HAVING ' . $having; |
|
1392 | } |
||
1393 | |||
1394 | /** |
||
1395 | * Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL. |
||
1396 | * @param string $sql the existing SQL (without ORDER BY/LIMIT/OFFSET) |
||
1397 | * @param array $orderBy the order by columns. See [[Query::orderBy]] for more details on how to specify this parameter. |
||
1398 | * @param int $limit the limit number. See [[Query::limit]] for more details. |
||
1399 | * @param int $offset the offset number. See [[Query::offset]] for more details. |
||
1400 | * @return string the SQL completed with ORDER BY/LIMIT/OFFSET (if any) |
||
1401 | */ |
||
1402 | 1363 | public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset) |
|
1403 | { |
||
1404 | 1363 | $orderBy = $this->buildOrderBy($orderBy); |
|
1405 | 1363 | if ($orderBy !== '') { |
|
1406 | 228 | $sql .= $this->separator . $orderBy; |
|
1407 | } |
||
1408 | 1363 | $limit = $this->buildLimit($limit, $offset); |
|
1409 | 1363 | if ($limit !== '') { |
|
1410 | 108 | $sql .= $this->separator . $limit; |
|
1411 | } |
||
1412 | |||
1413 | 1363 | return $sql; |
|
1414 | } |
||
1415 | |||
1416 | /** |
||
1417 | * @param array $columns |
||
1418 | * @return string the ORDER BY clause built from [[Query::$orderBy]]. |
||
1419 | */ |
||
1420 | 2069 | public function buildOrderBy($columns) |
|
1421 | { |
||
1422 | 2069 | if (empty($columns)) { |
|
1423 | 2005 | return ''; |
|
1424 | } |
||
1425 | 356 | $orders = []; |
|
1426 | 356 | foreach ($columns as $name => $direction) { |
|
1427 | 356 | if ($direction instanceof ExpressionInterface) { |
|
1428 | 13 | $orders[] = $this->buildExpression($direction); |
|
1429 | } else { |
||
1430 | 356 | $orders[] = $this->db->quoteColumnName($name) . ($direction === SORT_DESC ? ' DESC' : ''); |
|
1431 | } |
||
1432 | } |
||
1433 | |||
1434 | 356 | return 'ORDER BY ' . implode(', ', $orders); |
|
1435 | } |
||
1436 | |||
1437 | /** |
||
1438 | * @param int $limit |
||
1439 | * @param int $offset |
||
1440 | * @return string the LIMIT and OFFSET clauses |
||
1441 | */ |
||
1442 | 486 | public function buildLimit($limit, $offset) |
|
1443 | { |
||
1444 | 486 | $sql = ''; |
|
1445 | 486 | if ($this->hasLimit($limit)) { |
|
1446 | 28 | $sql = 'LIMIT ' . $limit; |
|
1447 | } |
||
1448 | 486 | if ($this->hasOffset($offset)) { |
|
1449 | 3 | $sql .= ' OFFSET ' . $offset; |
|
1450 | } |
||
1451 | |||
1452 | 486 | return ltrim($sql); |
|
1453 | } |
||
1454 | |||
1455 | /** |
||
1456 | * Checks to see if the given limit is effective. |
||
1457 | * @param mixed $limit the given limit |
||
1458 | * @return bool whether the limit is effective |
||
1459 | */ |
||
1460 | 1603 | protected function hasLimit($limit) |
|
1461 | { |
||
1462 | 1603 | return ($limit instanceof ExpressionInterface) || ctype_digit((string)$limit); |
|
1463 | } |
||
1464 | |||
1465 | /** |
||
1466 | * Checks to see if the given offset is effective. |
||
1467 | * @param mixed $offset the given offset |
||
1468 | * @return bool whether the offset is effective |
||
1469 | */ |
||
1470 | 1603 | protected function hasOffset($offset) |
|
1471 | { |
||
1472 | 1603 | return ($offset instanceof ExpressionInterface) || ctype_digit((string)$offset) && (string)$offset !== '0'; |
|
1473 | } |
||
1474 | |||
1475 | /** |
||
1476 | * @param array $unions |
||
1477 | * @param array $params the binding parameters to be populated |
||
1478 | * @return string the UNION clause built from [[Query::$union]]. |
||
1479 | */ |
||
1480 | 1658 | public function buildUnion($unions, &$params) |
|
1481 | { |
||
1482 | 1658 | if (empty($unions)) { |
|
1483 | 1658 | return ''; |
|
1484 | } |
||
1485 | |||
1486 | 24 | $result = ''; |
|
1487 | |||
1488 | 24 | foreach ($unions as $i => $union) { |
|
1489 | 24 | $query = $union['query']; |
|
1490 | 24 | if ($query instanceof Query) { |
|
1491 | 24 | list($unions[$i]['query'], $params) = $this->build($query, $params); |
|
1492 | } |
||
1493 | |||
1494 | 24 | $result .= 'UNION ' . ($union['all'] ? 'ALL ' : '') . '( ' . $unions[$i]['query'] . ' ) '; |
|
1495 | } |
||
1496 | |||
1497 | 24 | return trim($result); |
|
1498 | } |
||
1499 | |||
1500 | /** |
||
1501 | * @param array $withs of configurations for each WITH query |
||
1502 | * @param array $params the binding parameters to be populated |
||
1503 | * @return string compiled WITH prefix of query including nested queries |
||
1504 | * @see Query::withQuery() |
||
1505 | * @since 2.0.35 |
||
1506 | */ |
||
1507 | 2069 | public function buildWithQueries($withs, &$params) |
|
1508 | { |
||
1509 | 2069 | if (empty($withs)) { |
|
1510 | 2069 | return ''; |
|
1511 | } |
||
1512 | |||
1513 | 10 | $recursive = false; |
|
1514 | 10 | $result = []; |
|
1515 | |||
1516 | 10 | foreach ($withs as $i => $with) { |
|
1517 | 10 | if ($with['recursive']) { |
|
1518 | 5 | $recursive = true; |
|
1519 | } |
||
1520 | |||
1521 | 10 | $query = $with['query']; |
|
1522 | 10 | if ($query instanceof Query) { |
|
1523 | 10 | list($with['query'], $params) = $this->build($query, $params); |
|
1524 | } |
||
1525 | |||
1526 | 10 | $result[] = $with['alias'] . ' AS (' . $with['query'] . ')'; |
|
1527 | } |
||
1528 | |||
1529 | 10 | return 'WITH ' . ($recursive ? 'RECURSIVE ' : '') . implode (', ', $result); |
|
1530 | } |
||
1531 | |||
1532 | /** |
||
1533 | * Processes columns and properly quotes them if necessary. |
||
1534 | * It will join all columns into a string with comma as separators. |
||
1535 | * @param string|array $columns the columns to be processed |
||
1536 | * @return string the processing result |
||
1537 | */ |
||
1538 | 49 | public function buildColumns($columns) |
|
1539 | { |
||
1540 | 49 | if (!is_array($columns)) { |
|
1541 | 40 | if (strpos($columns, '(') !== false) { |
|
1542 | return $columns; |
||
1543 | } |
||
1544 | |||
1545 | 40 | $rawColumns = $columns; |
|
1546 | 40 | $columns = preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY); |
|
1547 | 40 | if ($columns === false) { |
|
1548 | throw new InvalidArgumentException("$rawColumns is not valid columns."); |
||
1549 | } |
||
1550 | } |
||
1551 | 49 | foreach ($columns as $i => $column) { |
|
1552 | 49 | if ($column instanceof ExpressionInterface) { |
|
1553 | $columns[$i] = $this->buildExpression($column); |
||
1554 | 49 | } elseif (strpos($column, '(') === false) { |
|
1555 | 49 | $columns[$i] = $this->db->quoteColumnName($column); |
|
1556 | } |
||
1557 | } |
||
1558 | |||
1559 | 49 | return implode(', ', $columns); |
|
1560 | } |
||
1561 | |||
1562 | /** |
||
1563 | * Parses the condition specification and generates the corresponding SQL expression. |
||
1564 | * @param string|array|ExpressionInterface $condition the condition specification. Please refer to [[Query::where()]] |
||
1565 | * on how to specify a condition. |
||
1566 | * @param array $params the binding parameters to be populated |
||
1567 | * @return string the generated SQL expression |
||
1568 | */ |
||
1569 | 2188 | public function buildCondition($condition, &$params) |
|
1570 | { |
||
1571 | 2188 | if (is_array($condition)) { |
|
1572 | 1664 | if (empty($condition)) { |
|
1573 | 5 | return ''; |
|
1574 | } |
||
1575 | |||
1576 | 1664 | $condition = $this->createConditionFromArray($condition); |
|
1577 | } |
||
1578 | |||
1579 | 2188 | if ($condition instanceof ExpressionInterface) { |
|
1580 | 1769 | return $this->buildExpression($condition, $params); |
|
1581 | } |
||
1582 | |||
1583 | 2152 | return (string)$condition; |
|
1584 | } |
||
1585 | |||
1586 | /** |
||
1587 | * Transforms $condition defined in array format (as described in [[Query::where()]] |
||
1588 | * to instance of [[yii\db\condition\ConditionInterface|ConditionInterface]] according to |
||
1589 | * [[conditionClasses]] map. |
||
1590 | * |
||
1591 | * @param string|array $condition |
||
1592 | * @return ConditionInterface |
||
1593 | * @see conditionClasses |
||
1594 | * @since 2.0.14 |
||
1595 | */ |
||
1596 | 1664 | public function createConditionFromArray($condition) |
|
1597 | { |
||
1598 | 1664 | if (isset($condition[0])) { // operator format: operator, operand 1, operand 2, ... |
|
1599 | 1119 | $operator = strtoupper(array_shift($condition)); |
|
1600 | 1119 | if (isset($this->conditionClasses[$operator])) { |
|
1601 | 1007 | $className = $this->conditionClasses[$operator]; |
|
1602 | } else { |
||
1603 | 127 | $className = 'yii\db\conditions\SimpleCondition'; |
|
1604 | } |
||
1605 | /** @var ConditionInterface $className */ |
||
1606 | 1119 | return $className::fromArrayDefinition($operator, $condition); |
|
1607 | } |
||
1608 | |||
1609 | // hash format: 'column1' => 'value1', 'column2' => 'value2', ... |
||
1610 | 1072 | return new HashCondition($condition); |
|
1611 | } |
||
1612 | |||
1613 | /** |
||
1614 | * Creates a condition based on column-value pairs. |
||
1615 | * @param array $condition the condition specification. |
||
1616 | * @param array $params the binding parameters to be populated |
||
1617 | * @return string the generated SQL expression |
||
1618 | * @deprecated since 2.0.14. Use `buildCondition()` instead. |
||
1619 | */ |
||
1620 | public function buildHashCondition($condition, &$params) |
||
1621 | { |
||
1622 | return $this->buildCondition(new HashCondition($condition), $params); |
||
1623 | } |
||
1624 | |||
1625 | /** |
||
1626 | * Connects two or more SQL expressions with the `AND` or `OR` operator. |
||
1627 | * @param string $operator the operator to use for connecting the given operands |
||
1628 | * @param array $operands the SQL expressions to connect. |
||
1629 | * @param array $params the binding parameters to be populated |
||
1630 | * @return string the generated SQL expression |
||
1631 | * @deprecated since 2.0.14. Use `buildCondition()` instead. |
||
1632 | */ |
||
1633 | public function buildAndCondition($operator, $operands, &$params) |
||
1634 | { |
||
1635 | array_unshift($operands, $operator); |
||
1636 | return $this->buildCondition($operands, $params); |
||
1637 | } |
||
1638 | |||
1639 | /** |
||
1640 | * Inverts an SQL expressions with `NOT` operator. |
||
1641 | * @param string $operator the operator to use for connecting the given operands |
||
1642 | * @param array $operands the SQL expressions to connect. |
||
1643 | * @param array $params the binding parameters to be populated |
||
1644 | * @return string the generated SQL expression |
||
1645 | * @throws InvalidArgumentException if wrong number of operands have been given. |
||
1646 | * @deprecated since 2.0.14. Use `buildCondition()` instead. |
||
1647 | */ |
||
1648 | public function buildNotCondition($operator, $operands, &$params) |
||
1649 | { |
||
1650 | array_unshift($operands, $operator); |
||
1651 | return $this->buildCondition($operands, $params); |
||
1652 | } |
||
1653 | |||
1654 | /** |
||
1655 | * Creates an SQL expressions with the `BETWEEN` operator. |
||
1656 | * @param string $operator the operator to use (e.g. `BETWEEN` or `NOT BETWEEN`) |
||
1657 | * @param array $operands the first operand is the column name. The second and third operands |
||
1658 | * describe the interval that column value should be in. |
||
1659 | * @param array $params the binding parameters to be populated |
||
1660 | * @return string the generated SQL expression |
||
1661 | * @throws InvalidArgumentException if wrong number of operands have been given. |
||
1662 | * @deprecated since 2.0.14. Use `buildCondition()` instead. |
||
1663 | */ |
||
1664 | public function buildBetweenCondition($operator, $operands, &$params) |
||
1665 | { |
||
1666 | array_unshift($operands, $operator); |
||
1667 | return $this->buildCondition($operands, $params); |
||
1668 | } |
||
1669 | |||
1670 | /** |
||
1671 | * Creates an SQL expressions with the `IN` operator. |
||
1672 | * @param string $operator the operator to use (e.g. `IN` or `NOT IN`) |
||
1673 | * @param array $operands the first operand is the column name. If it is an array |
||
1674 | * a composite IN condition will be generated. |
||
1675 | * The second operand is an array of values that column value should be among. |
||
1676 | * If it is an empty array the generated expression will be a `false` value if |
||
1677 | * operator is `IN` and empty if operator is `NOT IN`. |
||
1678 | * @param array $params the binding parameters to be populated |
||
1679 | * @return string the generated SQL expression |
||
1680 | * @throws Exception if wrong number of operands have been given. |
||
1681 | * @deprecated since 2.0.14. Use `buildCondition()` instead. |
||
1682 | */ |
||
1683 | public function buildInCondition($operator, $operands, &$params) |
||
1684 | { |
||
1685 | array_unshift($operands, $operator); |
||
1686 | return $this->buildCondition($operands, $params); |
||
1687 | } |
||
1688 | |||
1689 | /** |
||
1690 | * Creates an SQL expressions with the `LIKE` operator. |
||
1691 | * @param string $operator the operator to use (e.g. `LIKE`, `NOT LIKE`, `OR LIKE` or `OR NOT LIKE`) |
||
1692 | * @param array $operands an array of two or three operands |
||
1693 | * |
||
1694 | * - The first operand is the column name. |
||
1695 | * - The second operand is a single value or an array of values that column value |
||
1696 | * should be compared with. If it is an empty array the generated expression will |
||
1697 | * be a `false` value if operator is `LIKE` or `OR LIKE`, and empty if operator |
||
1698 | * is `NOT LIKE` or `OR NOT LIKE`. |
||
1699 | * - An optional third operand can also be provided to specify how to escape special characters |
||
1700 | * in the value(s). The operand should be an array of mappings from the special characters to their |
||
1701 | * escaped counterparts. If this operand is not provided, a default escape mapping will be used. |
||
1702 | * You may use `false` or an empty array to indicate the values are already escaped and no escape |
||
1703 | * should be applied. Note that when using an escape mapping (or the third operand is not provided), |
||
1704 | * the values will be automatically enclosed within a pair of percentage characters. |
||
1705 | * @param array $params the binding parameters to be populated |
||
1706 | * @return string the generated SQL expression |
||
1707 | * @throws InvalidArgumentException if wrong number of operands have been given. |
||
1708 | * @deprecated since 2.0.14. Use `buildCondition()` instead. |
||
1709 | */ |
||
1710 | public function buildLikeCondition($operator, $operands, &$params) |
||
1711 | { |
||
1712 | array_unshift($operands, $operator); |
||
1713 | return $this->buildCondition($operands, $params); |
||
1714 | } |
||
1715 | |||
1716 | /** |
||
1717 | * Creates an SQL expressions with the `EXISTS` operator. |
||
1718 | * @param string $operator the operator to use (e.g. `EXISTS` or `NOT EXISTS`) |
||
1719 | * @param array $operands contains only one element which is a [[Query]] object representing the sub-query. |
||
1720 | * @param array $params the binding parameters to be populated |
||
1721 | * @return string the generated SQL expression |
||
1722 | * @throws InvalidArgumentException if the operand is not a [[Query]] object. |
||
1723 | * @deprecated since 2.0.14. Use `buildCondition()` instead. |
||
1724 | */ |
||
1725 | public function buildExistsCondition($operator, $operands, &$params) |
||
1726 | { |
||
1727 | array_unshift($operands, $operator); |
||
1728 | return $this->buildCondition($operands, $params); |
||
1729 | } |
||
1730 | |||
1731 | /** |
||
1732 | * Creates an SQL expressions like `"column" operator value`. |
||
1733 | * @param string $operator the operator to use. Anything could be used e.g. `>`, `<=`, etc. |
||
1734 | * @param array $operands contains two column names. |
||
1735 | * @param array $params the binding parameters to be populated |
||
1736 | * @return string the generated SQL expression |
||
1737 | * @throws InvalidArgumentException if wrong number of operands have been given. |
||
1738 | * @deprecated since 2.0.14. Use `buildCondition()` instead. |
||
1739 | */ |
||
1740 | public function buildSimpleCondition($operator, $operands, &$params) |
||
1741 | { |
||
1742 | array_unshift($operands, $operator); |
||
1743 | return $this->buildCondition($operands, $params); |
||
1744 | } |
||
1745 | |||
1746 | /** |
||
1747 | * Creates a SELECT EXISTS() SQL statement. |
||
1748 | * @param string $rawSql the subquery in a raw form to select from. |
||
1749 | * @return string the SELECT EXISTS() SQL statement. |
||
1750 | * @since 2.0.8 |
||
1751 | */ |
||
1752 | 76 | public function selectExists($rawSql) |
|
1753 | { |
||
1754 | 76 | return 'SELECT EXISTS(' . $rawSql . ')'; |
|
1755 | } |
||
1756 | |||
1757 | /** |
||
1758 | * Helper method to add $value to $params array using [[PARAM_PREFIX]]. |
||
1759 | * |
||
1760 | * @param string|null $value |
||
1761 | * @param array $params passed by reference |
||
1762 | * @return string the placeholder name in $params array |
||
1763 | * |
||
1764 | * @since 2.0.14 |
||
1765 | */ |
||
1766 | 1778 | public function bindParam($value, &$params) |
|
1767 | { |
||
1768 | 1778 | $phName = self::PARAM_PREFIX . count($params); |
|
1769 | 1778 | $params[$phName] = $value; |
|
1770 | |||
1771 | 1778 | return $phName; |
|
1772 | } |
||
1773 | |||
1774 | /** |
||
1775 | * Extracts table alias if there is one or returns false |
||
1776 | * @param $table |
||
1777 | * @return bool|array |
||
1778 | * @since 2.0.24 |
||
1779 | */ |
||
1780 | 1398 | protected function extractAlias($table) |
|
1781 | { |
||
1782 | 1398 | if (preg_match('/^(.*?)(?i:\s+as|)\s+([^ ]+)$/', $table, $matches)) { |
|
1783 | 53 | return $matches; |
|
1784 | } |
||
1785 | |||
1786 | 1358 | return false; |
|
1787 | } |
||
1788 | } |
||
1789 |