1 | <?php |
||
2 | /** |
||
3 | * @link https://www.yiiframework.com/ |
||
4 | * @copyright Copyright (c) 2008 Yii Software LLC |
||
5 | * @license https://www.yiiframework.com/license/ |
||
6 | */ |
||
7 | |||
8 | namespace yii\db; |
||
9 | |||
10 | use Yii; |
||
11 | use yii\base\Component; |
||
12 | use yii\base\NotSupportedException; |
||
13 | |||
14 | /** |
||
15 | * Command represents a SQL statement to be executed against a database. |
||
16 | * |
||
17 | * A command object is usually created by calling [[Connection::createCommand()]]. |
||
18 | * The SQL statement it represents can be set via the [[sql]] property. |
||
19 | * |
||
20 | * To execute a non-query SQL (such as INSERT, DELETE, UPDATE), call [[execute()]]. |
||
21 | * To execute a SQL statement that returns a result data set (such as SELECT), |
||
22 | * use [[queryAll()]], [[queryOne()]], [[queryColumn()]], [[queryScalar()]], or [[query()]]. |
||
23 | * |
||
24 | * For example, |
||
25 | * |
||
26 | * ```php |
||
27 | * $users = $connection->createCommand('SELECT * FROM user')->queryAll(); |
||
28 | * ``` |
||
29 | * |
||
30 | * Command supports SQL statement preparation and parameter binding. |
||
31 | * Call [[bindValue()]] to bind a value to a SQL parameter; |
||
32 | * Call [[bindParam()]] to bind a PHP variable to a SQL parameter. |
||
33 | * When binding a parameter, the SQL statement is automatically prepared. |
||
34 | * You may also call [[prepare()]] explicitly to prepare a SQL statement. |
||
35 | * |
||
36 | * Command also supports building SQL statements by providing methods such as [[insert()]], |
||
37 | * [[update()]], etc. For example, the following code will create and execute an INSERT SQL statement: |
||
38 | * |
||
39 | * ```php |
||
40 | * $connection->createCommand()->insert('user', [ |
||
41 | * 'name' => 'Sam', |
||
42 | * 'age' => 30, |
||
43 | * ])->execute(); |
||
44 | * ``` |
||
45 | * |
||
46 | * To build SELECT SQL statements, please use [[Query]] instead. |
||
47 | * |
||
48 | * For more details and usage information on Command, see the [guide article on Database Access Objects](guide:db-dao). |
||
49 | * |
||
50 | * @property string $rawSql The raw SQL with parameter values inserted into the corresponding placeholders in |
||
51 | * [[sql]]. |
||
52 | * @property string $sql The SQL statement to be executed. |
||
53 | * |
||
54 | * @author Qiang Xue <[email protected]> |
||
55 | * @since 2.0 |
||
56 | */ |
||
57 | class Command extends Component |
||
58 | { |
||
59 | /** |
||
60 | * @var Connection the DB connection that this command is associated with |
||
61 | */ |
||
62 | public $db; |
||
63 | /** |
||
64 | * @var \PDOStatement the PDOStatement object that this command is associated with |
||
65 | */ |
||
66 | public $pdoStatement; |
||
67 | /** |
||
68 | * @var int the default fetch mode for this command. |
||
69 | * @see https://www.php.net/manual/en/pdostatement.setfetchmode.php |
||
70 | */ |
||
71 | public $fetchMode = \PDO::FETCH_ASSOC; |
||
72 | /** |
||
73 | * @var array the parameters (name => value) that are bound to the current PDO statement. |
||
74 | * This property is maintained by methods such as [[bindValue()]]. It is mainly provided for logging purpose |
||
75 | * and is used to generate [[rawSql]]. Do not modify it directly. |
||
76 | */ |
||
77 | public $params = []; |
||
78 | /** |
||
79 | * @var int the default number of seconds that query results can remain valid in cache. |
||
80 | * Use 0 to indicate that the cached data will never expire. And use a negative number to indicate |
||
81 | * query cache should not be used. |
||
82 | * @see cache() |
||
83 | */ |
||
84 | public $queryCacheDuration; |
||
85 | /** |
||
86 | * @var \yii\caching\Dependency the dependency to be associated with the cached query result for this command |
||
87 | * @see cache() |
||
88 | */ |
||
89 | public $queryCacheDependency; |
||
90 | |||
91 | /** |
||
92 | * @var array pending parameters to be bound to the current PDO statement. |
||
93 | * @since 2.0.33 |
||
94 | */ |
||
95 | protected $pendingParams = []; |
||
96 | |||
97 | /** |
||
98 | * @var string the SQL statement that this command represents |
||
99 | */ |
||
100 | private $_sql; |
||
101 | /** |
||
102 | * @var string name of the table, which schema, should be refreshed after command execution. |
||
103 | */ |
||
104 | private $_refreshTableName; |
||
105 | /** |
||
106 | * @var string|null|false the isolation level to use for this transaction. |
||
107 | * See [[Transaction::begin()]] for details. |
||
108 | */ |
||
109 | private $_isolationLevel = false; |
||
110 | /** |
||
111 | * @var callable a callable (e.g. anonymous function) that is called when [[\yii\db\Exception]] is thrown |
||
112 | * when executing the command. |
||
113 | */ |
||
114 | private $_retryHandler; |
||
115 | |||
116 | |||
117 | /** |
||
118 | * Enables query cache for this command. |
||
119 | * @param int|null $duration the number of seconds that query result of this command can remain valid in the cache. |
||
120 | * If this is not set, the value of [[Connection::queryCacheDuration]] will be used instead. |
||
121 | * Use 0 to indicate that the cached data will never expire. |
||
122 | * @param \yii\caching\Dependency|null $dependency the cache dependency associated with the cached query result. |
||
123 | * @return $this the command object itself |
||
124 | */ |
||
125 | 35 | public function cache($duration = null, $dependency = null) |
|
126 | { |
||
127 | 35 | $this->queryCacheDuration = $duration === null ? $this->db->queryCacheDuration : $duration; |
|
128 | 35 | $this->queryCacheDependency = $dependency; |
|
129 | 35 | return $this; |
|
130 | } |
||
131 | |||
132 | /** |
||
133 | * Disables query cache for this command. |
||
134 | * @return $this the command object itself |
||
135 | */ |
||
136 | 3 | public function noCache() |
|
137 | { |
||
138 | 3 | $this->queryCacheDuration = -1; |
|
139 | 3 | return $this; |
|
140 | } |
||
141 | |||
142 | /** |
||
143 | * Returns the SQL statement for this command. |
||
144 | * @return string the SQL statement to be executed |
||
145 | */ |
||
146 | 1648 | public function getSql() |
|
147 | { |
||
148 | 1648 | return $this->_sql; |
|
149 | } |
||
150 | |||
151 | /** |
||
152 | * Specifies the SQL statement to be executed. The SQL statement will be quoted using [[Connection::quoteSql()]]. |
||
153 | * The previous SQL (if any) will be discarded, and [[params]] will be cleared as well. See [[reset()]] |
||
154 | * for details. |
||
155 | * |
||
156 | * @param string $sql the SQL statement to be set. |
||
157 | * @return $this this command instance |
||
158 | * @see reset() |
||
159 | * @see cancel() |
||
160 | */ |
||
161 | 1679 | public function setSql($sql) |
|
162 | { |
||
163 | 1679 | if ($sql !== $this->_sql) { |
|
164 | 1679 | $this->cancel(); |
|
165 | 1679 | $this->reset(); |
|
166 | 1679 | $this->_sql = $this->db->quoteSql($sql); |
|
167 | } |
||
168 | |||
169 | 1679 | return $this; |
|
170 | } |
||
171 | |||
172 | /** |
||
173 | * Specifies the SQL statement to be executed. The SQL statement will not be modified in any way. |
||
174 | * The previous SQL (if any) will be discarded, and [[params]] will be cleared as well. See [[reset()]] |
||
175 | * for details. |
||
176 | * |
||
177 | * @param string $sql the SQL statement to be set. |
||
178 | * @return $this this command instance |
||
179 | * @since 2.0.13 |
||
180 | * @see reset() |
||
181 | * @see cancel() |
||
182 | */ |
||
183 | 31 | public function setRawSql($sql) |
|
184 | { |
||
185 | 31 | if ($sql !== $this->_sql) { |
|
186 | 31 | $this->cancel(); |
|
187 | 31 | $this->reset(); |
|
188 | 31 | $this->_sql = $sql; |
|
189 | } |
||
190 | |||
191 | 31 | return $this; |
|
192 | } |
||
193 | |||
194 | /** |
||
195 | * Returns the raw SQL by inserting parameter values into the corresponding placeholders in [[sql]]. |
||
196 | * Note that the return value of this method should mainly be used for logging purpose. |
||
197 | * It is likely that this method returns an invalid SQL due to improper replacement of parameter placeholders. |
||
198 | * @return string the raw SQL with parameter values inserted into the corresponding placeholders in [[sql]]. |
||
199 | */ |
||
200 | 1658 | public function getRawSql() |
|
201 | { |
||
202 | 1658 | if (empty($this->params)) { |
|
203 | 1456 | return $this->_sql; |
|
204 | } |
||
205 | 1189 | $params = []; |
|
206 | 1189 | foreach ($this->params as $name => $value) { |
|
207 | 1189 | if (is_string($name) && strncmp(':', $name, 1)) { |
|
208 | 15 | $name = ':' . $name; |
|
209 | } |
||
210 | 1189 | if (is_string($value) || $value instanceof Expression) { |
|
211 | 937 | $params[$name] = $this->db->quoteValue((string)$value); |
|
212 | 935 | } elseif (is_bool($value)) { |
|
213 | 29 | $params[$name] = ($value ? 'TRUE' : 'FALSE'); |
|
214 | 928 | } elseif ($value === null) { |
|
215 | 341 | $params[$name] = 'NULL'; |
|
216 | 871 | } elseif (!is_object($value) && !is_resource($value)) { |
|
217 | 871 | $params[$name] = $value; |
|
218 | } |
||
219 | } |
||
220 | 1189 | if (!isset($params[1])) { |
|
221 | return preg_replace_callback('#(:\w+)#', function($matches) use ($params) { |
||
222 | 1186 | $m = $matches[1]; |
|
223 | 1186 | return isset($params[$m]) ? $params[$m] : $m; |
|
224 | 1186 | }, $this->_sql); |
|
225 | } |
||
226 | 3 | $sql = ''; |
|
227 | 3 | foreach (explode('?', $this->_sql) as $i => $part) { |
|
228 | 3 | $sql .= (isset($params[$i]) ? $params[$i] : '') . $part; |
|
229 | } |
||
230 | |||
231 | 3 | return $sql; |
|
232 | } |
||
233 | |||
234 | /** |
||
235 | * Prepares the SQL statement to be executed. |
||
236 | * For complex SQL statement that is to be executed multiple times, |
||
237 | * this may improve performance. |
||
238 | * For SQL statement with binding parameters, this method is invoked |
||
239 | * automatically. |
||
240 | * @param bool|null $forRead whether this method is called for a read query. If null, it means |
||
241 | * the SQL statement should be used to determine whether it is for read or write. |
||
242 | * @throws Exception if there is any DB error |
||
243 | */ |
||
244 | 1636 | public function prepare($forRead = null) |
|
245 | { |
||
246 | 1636 | if ($this->pdoStatement) { |
|
247 | 60 | $this->bindPendingParams(); |
|
248 | 60 | return; |
|
249 | } |
||
250 | |||
251 | 1636 | $sql = $this->getSql(); |
|
252 | 1636 | if ($sql === '') { |
|
253 | 3 | return; |
|
254 | } |
||
255 | |||
256 | 1636 | if ($this->db->getTransaction()) { |
|
257 | // master is in a transaction. use the same connection. |
||
258 | 23 | $forRead = false; |
|
259 | } |
||
260 | 1636 | if ($forRead || $forRead === null && $this->db->getSchema()->isReadQuery($sql)) { |
|
261 | 1579 | $pdo = $this->db->getSlavePdo(true); |
|
262 | } else { |
||
263 | 748 | $pdo = $this->db->getMasterPdo(); |
|
264 | } |
||
265 | |||
266 | try { |
||
267 | 1636 | $this->pdoStatement = $pdo->prepare($sql); |
|
268 | 1635 | $this->bindPendingParams(); |
|
269 | 5 | } catch (\Exception $e) { |
|
270 | 5 | $message = $e->getMessage() . "\nFailed to prepare SQL: $sql"; |
|
271 | 5 | $errorInfo = $e instanceof \PDOException ? $e->errorInfo : null; |
|
272 | 5 | throw new Exception($message, $errorInfo, $e->getCode(), $e); |
|
273 | } catch (\Throwable $e) { |
||
274 | $message = $e->getMessage() . "\nFailed to prepare SQL: $sql"; |
||
275 | throw new Exception($message, null, $e->getCode(), $e); |
||
276 | } |
||
277 | 1635 | } |
|
278 | |||
279 | /** |
||
280 | * Cancels the execution of the SQL statement. |
||
281 | * This method mainly sets [[pdoStatement]] to be null. |
||
282 | */ |
||
283 | 1679 | public function cancel() |
|
284 | { |
||
285 | 1679 | $this->pdoStatement = null; |
|
286 | 1679 | } |
|
287 | |||
288 | /** |
||
289 | * Binds a parameter to the SQL statement to be executed. |
||
290 | * @param string|int $name parameter identifier. For a prepared statement |
||
291 | * using named placeholders, this will be a parameter name of |
||
292 | * the form `:name`. For a prepared statement using question mark |
||
293 | * placeholders, this will be the 1-indexed position of the parameter. |
||
294 | * @param mixed $value the PHP variable to bind to the SQL statement parameter (passed by reference) |
||
295 | * @param int|null $dataType SQL data type of the parameter. If null, the type is determined by the PHP type of the value. |
||
296 | * @param int|null $length length of the data type |
||
297 | * @param mixed $driverOptions the driver-specific options |
||
298 | * @return $this the current command being executed |
||
299 | * @see https://www.php.net/manual/en/function.PDOStatement-bindParam.php |
||
300 | */ |
||
301 | 3 | public function bindParam($name, &$value, $dataType = null, $length = null, $driverOptions = null) |
|
302 | { |
||
303 | 3 | $this->prepare(); |
|
304 | |||
305 | 3 | if ($dataType === null) { |
|
306 | 3 | $dataType = $this->db->getSchema()->getPdoType($value); |
|
307 | } |
||
308 | 3 | if ($length === null) { |
|
309 | 3 | $this->pdoStatement->bindParam($name, $value, $dataType); |
|
310 | } elseif ($driverOptions === null) { |
||
311 | $this->pdoStatement->bindParam($name, $value, $dataType, $length); |
||
312 | } else { |
||
313 | $this->pdoStatement->bindParam($name, $value, $dataType, $length, $driverOptions); |
||
314 | } |
||
315 | 3 | $this->params[$name] = &$value; |
|
316 | |||
317 | 3 | return $this; |
|
318 | } |
||
319 | |||
320 | /** |
||
321 | * Binds pending parameters that were registered via [[bindValue()]] and [[bindValues()]]. |
||
322 | * Note that this method requires an active [[pdoStatement]]. |
||
323 | */ |
||
324 | 1635 | protected function bindPendingParams() |
|
325 | { |
||
326 | 1635 | foreach ($this->pendingParams as $name => $value) { |
|
327 | 1168 | $this->pdoStatement->bindValue($name, $value[0], $value[1]); |
|
328 | } |
||
329 | 1635 | $this->pendingParams = []; |
|
330 | 1635 | } |
|
331 | |||
332 | /** |
||
333 | * Binds a value to a parameter. |
||
334 | * @param string|int $name Parameter identifier. For a prepared statement |
||
335 | * using named placeholders, this will be a parameter name of |
||
336 | * the form `:name`. For a prepared statement using question mark |
||
337 | * placeholders, this will be the 1-indexed position of the parameter. |
||
338 | * @param mixed $value The value to bind to the parameter |
||
339 | * @param int|null $dataType SQL data type of the parameter. If null, the type is determined by the PHP type of the value. |
||
340 | * @return $this the current command being executed |
||
341 | * @see https://www.php.net/manual/en/function.PDOStatement-bindValue.php |
||
342 | */ |
||
343 | 6 | public function bindValue($name, $value, $dataType = null) |
|
344 | { |
||
345 | 6 | if ($dataType === null) { |
|
346 | 6 | $dataType = $this->db->getSchema()->getPdoType($value); |
|
347 | } |
||
348 | 6 | $this->pendingParams[$name] = [$value, $dataType]; |
|
349 | 6 | $this->params[$name] = $value; |
|
350 | |||
351 | 6 | return $this; |
|
352 | } |
||
353 | |||
354 | /** |
||
355 | * Binds a list of values to the corresponding parameters. |
||
356 | * This is similar to [[bindValue()]] except that it binds multiple values at a time. |
||
357 | * Note that the SQL data type of each value is determined by its PHP type. |
||
358 | * @param array $values the values to be bound. This must be given in terms of an associative |
||
359 | * array with array keys being the parameter names, and array values the corresponding parameter values, |
||
360 | * e.g. `[':name' => 'John', ':age' => 25]`. By default, the PDO type of each value is determined |
||
361 | * by its PHP type. You may explicitly specify the PDO type by using a [[yii\db\PdoValue]] class: `new PdoValue(value, type)`, |
||
362 | * e.g. `[':name' => 'John', ':profile' => new PdoValue($profile, \PDO::PARAM_LOB)]`. |
||
363 | * @return $this the current command being executed |
||
364 | */ |
||
365 | 1679 | public function bindValues($values) |
|
366 | { |
||
367 | 1679 | if (empty($values)) { |
|
368 | 1480 | return $this; |
|
369 | } |
||
370 | |||
371 | 1197 | $schema = $this->db->getSchema(); |
|
372 | 1197 | foreach ($values as $name => $value) { |
|
373 | 1197 | if (is_array($value)) { // TODO: Drop in Yii 2.1 |
|
374 | 3 | $this->pendingParams[$name] = $value; |
|
375 | 3 | $this->params[$name] = $value[0]; |
|
376 | 1194 | } elseif ($value instanceof PdoValue) { |
|
377 | 108 | $this->pendingParams[$name] = [$value->getValue(), $value->getType()]; |
|
378 | 108 | $this->params[$name] = $value->getValue(); |
|
379 | } else { |
||
380 | 1194 | if (version_compare(PHP_VERSION, '8.1.0') >= 0) { |
|
381 | if ($value instanceof \BackedEnum) { |
||
0 ignored issues
–
show
|
|||
382 | $value = $value->value; |
||
383 | } elseif ($value instanceof \UnitEnum) { |
||
0 ignored issues
–
show
The type
UnitEnum was not found. Maybe you did not declare it correctly or list all dependencies?
The issue could also be caused by a filter entry in the build configuration.
If the path has been excluded in your configuration, e.g. filter:
dependency_paths: ["lib/*"]
For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths ![]() |
|||
384 | $value = $value->name; |
||
385 | } |
||
386 | } |
||
387 | 1194 | $type = $schema->getPdoType($value); |
|
388 | 1194 | $this->pendingParams[$name] = [$value, $type]; |
|
389 | 1194 | $this->params[$name] = $value; |
|
390 | } |
||
391 | } |
||
392 | |||
393 | 1197 | return $this; |
|
394 | } |
||
395 | |||
396 | /** |
||
397 | * Executes the SQL statement and returns query result. |
||
398 | * This method is for executing a SQL query that returns result set, such as `SELECT`. |
||
399 | * @return DataReader the reader object for fetching the query result |
||
400 | * @throws Exception execution failed |
||
401 | */ |
||
402 | 15 | public function query() |
|
403 | { |
||
404 | 15 | return $this->queryInternal(''); |
|
405 | } |
||
406 | |||
407 | /** |
||
408 | * Executes the SQL statement and returns ALL rows at once. |
||
409 | * @param int|null $fetchMode the result fetch mode. Please refer to [PHP manual](https://www.php.net/manual/en/function.PDOStatement-setFetchMode.php) |
||
410 | * for valid fetch modes. If this parameter is null, the value set in [[fetchMode]] will be used. |
||
411 | * @return array all rows of the query result. Each array element is an array representing a row of data. |
||
412 | * An empty array is returned if the query results in nothing. |
||
413 | * @throws Exception execution failed |
||
414 | */ |
||
415 | 1422 | public function queryAll($fetchMode = null) |
|
416 | { |
||
417 | 1422 | return $this->queryInternal('fetchAll', $fetchMode); |
|
418 | } |
||
419 | |||
420 | /** |
||
421 | * Executes the SQL statement and returns the first row of the result. |
||
422 | * This method is best used when only the first row of result is needed for a query. |
||
423 | * @param int|null $fetchMode the result fetch mode. Please refer to [PHP manual](https://www.php.net/manual/en/pdostatement.setfetchmode.php) |
||
424 | * for valid fetch modes. If this parameter is null, the value set in [[fetchMode]] will be used. |
||
425 | * @return array|false the first row (in terms of an array) of the query result. False is returned if the query |
||
426 | * results in nothing. |
||
427 | * @throws Exception execution failed |
||
428 | */ |
||
429 | 518 | public function queryOne($fetchMode = null) |
|
430 | { |
||
431 | 518 | return $this->queryInternal('fetch', $fetchMode); |
|
432 | } |
||
433 | |||
434 | /** |
||
435 | * Executes the SQL statement and returns the value of the first column in the first row of data. |
||
436 | * This method is best used when only a single value is needed for a query. |
||
437 | * @return string|int|null|false the value of the first column in the first row of the query result. |
||
438 | * False is returned if there is no value. |
||
439 | * @throws Exception execution failed |
||
440 | */ |
||
441 | 371 | public function queryScalar() |
|
442 | { |
||
443 | 371 | $result = $this->queryInternal('fetchColumn', 0); |
|
444 | 368 | if (is_resource($result) && get_resource_type($result) === 'stream') { |
|
445 | 21 | return stream_get_contents($result); |
|
446 | } |
||
447 | |||
448 | 360 | return $result; |
|
449 | } |
||
450 | |||
451 | /** |
||
452 | * Executes the SQL statement and returns the first column of the result. |
||
453 | * This method is best used when only the first column of result (i.e. the first element in each row) |
||
454 | * is needed for a query. |
||
455 | * @return array the first column of the query result. Empty array is returned if the query results in nothing. |
||
456 | * @throws Exception execution failed |
||
457 | */ |
||
458 | 93 | public function queryColumn() |
|
459 | { |
||
460 | 93 | return $this->queryInternal('fetchAll', \PDO::FETCH_COLUMN); |
|
461 | } |
||
462 | |||
463 | /** |
||
464 | * Creates an INSERT command. |
||
465 | * |
||
466 | * For example, |
||
467 | * |
||
468 | * ```php |
||
469 | * $connection->createCommand()->insert('user', [ |
||
470 | * 'name' => 'Sam', |
||
471 | * 'age' => 30, |
||
472 | * ])->execute(); |
||
473 | * ``` |
||
474 | * |
||
475 | * The method will properly escape the column names, and bind the values to be inserted. |
||
476 | * |
||
477 | * Note that the created command is not executed until [[execute()]] is called. |
||
478 | * |
||
479 | * @param string $table the table that new rows will be inserted into. |
||
480 | * @param array|\yii\db\Query $columns the column data (name => value) to be inserted into the table or instance |
||
481 | * of [[yii\db\Query|Query]] to perform INSERT INTO ... SELECT SQL statement. |
||
482 | * Passing of [[yii\db\Query|Query]] is available since version 2.0.11. |
||
483 | * @return $this the command object itself |
||
484 | */ |
||
485 | 452 | public function insert($table, $columns) |
|
486 | { |
||
487 | 452 | $params = []; |
|
488 | 452 | $sql = $this->db->getQueryBuilder()->insert($table, $columns, $params); |
|
489 | |||
490 | 443 | return $this->setSql($sql)->bindValues($params); |
|
491 | } |
||
492 | |||
493 | /** |
||
494 | * Creates a batch INSERT command. |
||
495 | * |
||
496 | * For example, |
||
497 | * |
||
498 | * ```php |
||
499 | * $connection->createCommand()->batchInsert('user', ['name', 'age'], [ |
||
500 | * ['Tom', 30], |
||
501 | * ['Jane', 20], |
||
502 | * ['Linda', 25], |
||
503 | * ])->execute(); |
||
504 | * ``` |
||
505 | * |
||
506 | * The method will properly escape the column names, and quote the values to be inserted. |
||
507 | * |
||
508 | * Note that the values in each row must match the corresponding column names. |
||
509 | * |
||
510 | * Also note that the created command is not executed until [[execute()]] is called. |
||
511 | * |
||
512 | * @param string $table the table that new rows will be inserted into. |
||
513 | * @param array $columns the column names |
||
514 | * @param array|\Generator $rows the rows to be batch inserted into the table |
||
515 | * @return $this the command object itself |
||
516 | */ |
||
517 | 31 | public function batchInsert($table, $columns, $rows) |
|
518 | { |
||
519 | 31 | $table = $this->db->quoteSql($table); |
|
520 | $columns = array_map(function ($column) { |
||
521 | 31 | return $this->db->quoteSql($column); |
|
522 | 31 | }, $columns); |
|
523 | |||
524 | 31 | $params = []; |
|
525 | 31 | $sql = $this->db->getQueryBuilder()->batchInsert($table, $columns, $rows, $params); |
|
526 | |||
527 | 31 | $this->setRawSql($sql); |
|
528 | 31 | $this->bindValues($params); |
|
529 | |||
530 | 31 | return $this; |
|
531 | } |
||
532 | |||
533 | /** |
||
534 | * Creates a command to insert rows into a database table if |
||
535 | * they do not already exist (matching unique constraints), |
||
536 | * or update them if they do. |
||
537 | * |
||
538 | * For example, |
||
539 | * |
||
540 | * ```php |
||
541 | * $sql = $queryBuilder->upsert('pages', [ |
||
542 | * 'name' => 'Front page', |
||
543 | * 'url' => 'https://example.com/', // url is unique |
||
544 | * 'visits' => 0, |
||
545 | * ], [ |
||
546 | * 'visits' => new \yii\db\Expression('visits + 1'), |
||
547 | * ], $params); |
||
548 | * ``` |
||
549 | * |
||
550 | * The method will properly escape the table and column names. |
||
551 | * |
||
552 | * @param string $table the table that new rows will be inserted into/updated in. |
||
553 | * @param array|Query $insertColumns the column data (name => value) to be inserted into the table or instance |
||
554 | * of [[Query]] to perform `INSERT INTO ... SELECT` SQL statement. |
||
555 | * @param array|bool $updateColumns the column data (name => value) to be updated if they already exist. |
||
556 | * If `true` is passed, the column data will be updated to match the insert column data. |
||
557 | * If `false` is passed, no update will be performed if the column data already exists. |
||
558 | * @param array $params the parameters to be bound to the command. |
||
559 | * @return $this the command object itself. |
||
560 | * @since 2.0.14 |
||
561 | */ |
||
562 | 71 | public function upsert($table, $insertColumns, $updateColumns = true, $params = []) |
|
563 | { |
||
564 | 71 | $sql = $this->db->getQueryBuilder()->upsert($table, $insertColumns, $updateColumns, $params); |
|
565 | |||
566 | 71 | return $this->setSql($sql)->bindValues($params); |
|
567 | } |
||
568 | |||
569 | /** |
||
570 | * Creates an UPDATE command. |
||
571 | * |
||
572 | * For example, |
||
573 | * |
||
574 | * ```php |
||
575 | * $connection->createCommand()->update('user', ['status' => 1], 'age > 30')->execute(); |
||
576 | * ``` |
||
577 | * |
||
578 | * or with using parameter binding for the condition: |
||
579 | * |
||
580 | * ```php |
||
581 | * $minAge = 30; |
||
582 | * $connection->createCommand()->update('user', ['status' => 1], 'age > :minAge', [':minAge' => $minAge])->execute(); |
||
583 | * ``` |
||
584 | * |
||
585 | * The method will properly escape the column names and bind the values to be updated. |
||
586 | * |
||
587 | * Note that the created command is not executed until [[execute()]] is called. |
||
588 | * |
||
589 | * @param string $table the table to be updated. |
||
590 | * @param array $columns the column data (name => value) to be updated. |
||
591 | * @param string|array $condition the condition that will be put in the WHERE part. Please |
||
592 | * refer to [[Query::where()]] on how to specify condition. |
||
593 | * @param array $params the parameters to be bound to the command |
||
594 | * @return $this the command object itself |
||
595 | */ |
||
596 | 97 | public function update($table, $columns, $condition = '', $params = []) |
|
597 | { |
||
598 | 97 | $sql = $this->db->getQueryBuilder()->update($table, $columns, $condition, $params); |
|
599 | |||
600 | 97 | return $this->setSql($sql)->bindValues($params); |
|
601 | } |
||
602 | |||
603 | /** |
||
604 | * Creates a DELETE command. |
||
605 | * |
||
606 | * For example, |
||
607 | * |
||
608 | * ```php |
||
609 | * $connection->createCommand()->delete('user', 'status = 0')->execute(); |
||
610 | * ``` |
||
611 | * |
||
612 | * or with using parameter binding for the condition: |
||
613 | * |
||
614 | * ```php |
||
615 | * $status = 0; |
||
616 | * $connection->createCommand()->delete('user', 'status = :status', [':status' => $status])->execute(); |
||
617 | * ``` |
||
618 | * |
||
619 | * The method will properly escape the table and column names. |
||
620 | * |
||
621 | * Note that the created command is not executed until [[execute()]] is called. |
||
622 | * |
||
623 | * @param string $table the table where the data will be deleted from. |
||
624 | * @param string|array $condition the condition that will be put in the WHERE part. Please |
||
625 | * refer to [[Query::where()]] on how to specify condition. |
||
626 | * @param array $params the parameters to be bound to the command |
||
627 | * @return $this the command object itself |
||
628 | */ |
||
629 | 384 | public function delete($table, $condition = '', $params = []) |
|
630 | { |
||
631 | 384 | $sql = $this->db->getQueryBuilder()->delete($table, $condition, $params); |
|
632 | |||
633 | 384 | return $this->setSql($sql)->bindValues($params); |
|
634 | } |
||
635 | |||
636 | /** |
||
637 | * Creates a SQL command for creating a new DB table. |
||
638 | * |
||
639 | * The columns in the new table should be specified as name-definition pairs (e.g. 'name' => 'string'), |
||
640 | * where name stands for a column name which will be properly quoted by the method, and definition |
||
641 | * stands for the column type which must contain an abstract DB type. |
||
642 | * |
||
643 | * The method [[QueryBuilder::getColumnType()]] will be called |
||
644 | * to convert the abstract column types to physical ones. For example, `string` will be converted |
||
645 | * as `varchar(255)`, and `string not null` becomes `varchar(255) not null`. |
||
646 | * |
||
647 | * If a column is specified with definition only (e.g. 'PRIMARY KEY (name, type)'), it will be directly |
||
648 | * inserted into the generated SQL. |
||
649 | * |
||
650 | * Example usage: |
||
651 | * ```php |
||
652 | * Yii::$app->db->createCommand()->createTable('post', [ |
||
653 | * 'id' => 'pk', |
||
654 | * 'title' => 'string', |
||
655 | * 'text' => 'text', |
||
656 | * 'column_name double precision null default null', |
||
657 | * ]); |
||
658 | * ``` |
||
659 | * |
||
660 | * @param string $table the name of the table to be created. The name will be properly quoted by the method. |
||
661 | * @param array $columns the columns (name => definition) in the new table. |
||
662 | * @param string|null $options additional SQL fragment that will be appended to the generated SQL. |
||
663 | * @return $this the command object itself |
||
664 | */ |
||
665 | 161 | public function createTable($table, $columns, $options = null) |
|
666 | { |
||
667 | 161 | $sql = $this->db->getQueryBuilder()->createTable($table, $columns, $options); |
|
668 | |||
669 | 161 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
670 | } |
||
671 | |||
672 | /** |
||
673 | * Creates a SQL command for renaming a DB table. |
||
674 | * @param string $table the table to be renamed. The name will be properly quoted by the method. |
||
675 | * @param string $newName the new table name. The name will be properly quoted by the method. |
||
676 | * @return $this the command object itself |
||
677 | */ |
||
678 | 6 | public function renameTable($table, $newName) |
|
679 | { |
||
680 | 6 | $sql = $this->db->getQueryBuilder()->renameTable($table, $newName); |
|
681 | |||
682 | 6 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
683 | } |
||
684 | |||
685 | /** |
||
686 | * Creates a SQL command for dropping a DB table. |
||
687 | * @param string $table the table to be dropped. The name will be properly quoted by the method. |
||
688 | * @return $this the command object itself |
||
689 | */ |
||
690 | 51 | public function dropTable($table) |
|
691 | { |
||
692 | 51 | $sql = $this->db->getQueryBuilder()->dropTable($table); |
|
693 | |||
694 | 51 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
695 | } |
||
696 | |||
697 | /** |
||
698 | * Creates a SQL command for truncating a DB table. |
||
699 | * @param string $table the table to be truncated. The name will be properly quoted by the method. |
||
700 | * @return $this the command object itself |
||
701 | */ |
||
702 | 13 | public function truncateTable($table) |
|
703 | { |
||
704 | 13 | $sql = $this->db->getQueryBuilder()->truncateTable($table); |
|
705 | |||
706 | 13 | return $this->setSql($sql); |
|
707 | } |
||
708 | |||
709 | /** |
||
710 | * Creates a SQL command for adding a new DB column. |
||
711 | * @param string $table the table that the new column will be added to. The table name will be properly quoted by the method. |
||
712 | * @param string $column the name of the new column. The name will be properly quoted by the method. |
||
713 | * @param string $type the column type. [[\yii\db\QueryBuilder::getColumnType()]] will be called |
||
714 | * to convert the given column type to the physical one. For example, `string` will be converted |
||
715 | * as `varchar(255)`, and `string not null` becomes `varchar(255) not null`. |
||
716 | * @return $this the command object itself |
||
717 | */ |
||
718 | 7 | public function addColumn($table, $column, $type) |
|
719 | { |
||
720 | 7 | $sql = $this->db->getQueryBuilder()->addColumn($table, $column, $type); |
|
721 | |||
722 | 7 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
723 | } |
||
724 | |||
725 | /** |
||
726 | * Creates a SQL command for dropping a DB column. |
||
727 | * @param string $table the table whose column is to be dropped. The name will be properly quoted by the method. |
||
728 | * @param string $column the name of the column to be dropped. The name will be properly quoted by the method. |
||
729 | * @return $this the command object itself |
||
730 | */ |
||
731 | public function dropColumn($table, $column) |
||
732 | { |
||
733 | $sql = $this->db->getQueryBuilder()->dropColumn($table, $column); |
||
734 | |||
735 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
||
736 | } |
||
737 | |||
738 | /** |
||
739 | * Creates a SQL command for renaming a column. |
||
740 | * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method. |
||
741 | * @param string $oldName the old name of the column. The name will be properly quoted by the method. |
||
742 | * @param string $newName the new name of the column. The name will be properly quoted by the method. |
||
743 | * @return $this the command object itself |
||
744 | */ |
||
745 | public function renameColumn($table, $oldName, $newName) |
||
746 | { |
||
747 | $sql = $this->db->getQueryBuilder()->renameColumn($table, $oldName, $newName); |
||
748 | |||
749 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
||
750 | } |
||
751 | |||
752 | /** |
||
753 | * Creates a SQL command for changing the definition of a column. |
||
754 | * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method. |
||
755 | * @param string $column the name of the column to be changed. The name will be properly quoted by the method. |
||
756 | * @param string $type the column type. [[\yii\db\QueryBuilder::getColumnType()]] will be called |
||
757 | * to convert the give column type to the physical one. For example, `string` will be converted |
||
758 | * as `varchar(255)`, and `string not null` becomes `varchar(255) not null`. |
||
759 | * @return $this the command object itself |
||
760 | */ |
||
761 | 2 | public function alterColumn($table, $column, $type) |
|
762 | { |
||
763 | 2 | $sql = $this->db->getQueryBuilder()->alterColumn($table, $column, $type); |
|
764 | |||
765 | 2 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
766 | } |
||
767 | |||
768 | /** |
||
769 | * Creates a SQL command for adding a primary key constraint to an existing table. |
||
770 | * The method will properly quote the table and column names. |
||
771 | * @param string $name the name of the primary key constraint. |
||
772 | * @param string $table the table that the primary key constraint will be added to. |
||
773 | * @param string|array $columns comma separated string or array of columns that the primary key will consist of. |
||
774 | * @return $this the command object itself. |
||
775 | */ |
||
776 | 2 | public function addPrimaryKey($name, $table, $columns) |
|
777 | { |
||
778 | 2 | $sql = $this->db->getQueryBuilder()->addPrimaryKey($name, $table, $columns); |
|
779 | |||
780 | 2 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
781 | } |
||
782 | |||
783 | /** |
||
784 | * Creates a SQL command for removing a primary key constraint to an existing table. |
||
785 | * @param string $name the name of the primary key constraint to be removed. |
||
786 | * @param string $table the table that the primary key constraint will be removed from. |
||
787 | * @return $this the command object itself |
||
788 | */ |
||
789 | 2 | public function dropPrimaryKey($name, $table) |
|
790 | { |
||
791 | 2 | $sql = $this->db->getQueryBuilder()->dropPrimaryKey($name, $table); |
|
792 | |||
793 | 2 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
794 | } |
||
795 | |||
796 | /** |
||
797 | * Creates a SQL command for adding a foreign key constraint to an existing table. |
||
798 | * The method will properly quote the table and column names. |
||
799 | * @param string $name the name of the foreign key constraint. |
||
800 | * @param string $table the table that the foreign key constraint will be added to. |
||
801 | * @param string|array $columns the name of the column to that the constraint will be added on. If there are multiple columns, separate them with commas. |
||
802 | * @param string $refTable the table that the foreign key references to. |
||
803 | * @param string|array $refColumns the name of the column that the foreign key references to. If there are multiple columns, separate them with commas. |
||
804 | * @param string|null $delete the ON DELETE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL |
||
805 | * @param string|null $update the ON UPDATE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL |
||
806 | * @return $this the command object itself |
||
807 | */ |
||
808 | 4 | public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null) |
|
809 | { |
||
810 | 4 | $sql = $this->db->getQueryBuilder()->addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete, $update); |
|
811 | |||
812 | 4 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
813 | } |
||
814 | |||
815 | /** |
||
816 | * Creates a SQL command for dropping a foreign key constraint. |
||
817 | * @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted by the method. |
||
818 | * @param string $table the table whose foreign is to be dropped. The name will be properly quoted by the method. |
||
819 | * @return $this the command object itself |
||
820 | */ |
||
821 | 5 | public function dropForeignKey($name, $table) |
|
822 | { |
||
823 | 5 | $sql = $this->db->getQueryBuilder()->dropForeignKey($name, $table); |
|
824 | |||
825 | 5 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
826 | } |
||
827 | |||
828 | /** |
||
829 | * Creates a SQL command for creating a new index. |
||
830 | * @param string $name the name of the index. The name will be properly quoted by the method. |
||
831 | * @param string $table the table that the new index will be created for. The table name will be properly quoted by the method. |
||
832 | * @param string|array $columns the column(s) that should be included in the index. If there are multiple columns, please separate them |
||
833 | * by commas. The column names will be properly quoted by the method. |
||
834 | * @param bool $unique whether to add UNIQUE constraint on the created index. |
||
835 | * @return $this the command object itself |
||
836 | */ |
||
837 | 12 | public function createIndex($name, $table, $columns, $unique = false) |
|
838 | { |
||
839 | 12 | $sql = $this->db->getQueryBuilder()->createIndex($name, $table, $columns, $unique); |
|
840 | |||
841 | 12 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
842 | } |
||
843 | |||
844 | /** |
||
845 | * Creates a SQL command for dropping an index. |
||
846 | * @param string $name the name of the index to be dropped. The name will be properly quoted by the method. |
||
847 | * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method. |
||
848 | * @return $this the command object itself |
||
849 | */ |
||
850 | 3 | public function dropIndex($name, $table) |
|
851 | { |
||
852 | 3 | $sql = $this->db->getQueryBuilder()->dropIndex($name, $table); |
|
853 | |||
854 | 3 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
855 | } |
||
856 | |||
857 | /** |
||
858 | * Creates a SQL command for adding an unique constraint to an existing table. |
||
859 | * @param string $name the name of the unique constraint. |
||
860 | * The name will be properly quoted by the method. |
||
861 | * @param string $table the table that the unique constraint will be added to. |
||
862 | * The name will be properly quoted by the method. |
||
863 | * @param string|array $columns the name of the column to that the constraint will be added on. |
||
864 | * If there are multiple columns, separate them with commas. |
||
865 | * The name will be properly quoted by the method. |
||
866 | * @return $this the command object itself. |
||
867 | * @since 2.0.13 |
||
868 | */ |
||
869 | 2 | public function addUnique($name, $table, $columns) |
|
870 | { |
||
871 | 2 | $sql = $this->db->getQueryBuilder()->addUnique($name, $table, $columns); |
|
872 | |||
873 | 2 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
874 | } |
||
875 | |||
876 | /** |
||
877 | * Creates a SQL command for dropping an unique constraint. |
||
878 | * @param string $name the name of the unique constraint to be dropped. |
||
879 | * The name will be properly quoted by the method. |
||
880 | * @param string $table the table whose unique constraint is to be dropped. |
||
881 | * The name will be properly quoted by the method. |
||
882 | * @return $this the command object itself. |
||
883 | * @since 2.0.13 |
||
884 | */ |
||
885 | 2 | public function dropUnique($name, $table) |
|
886 | { |
||
887 | 2 | $sql = $this->db->getQueryBuilder()->dropUnique($name, $table); |
|
888 | |||
889 | 2 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
890 | } |
||
891 | |||
892 | /** |
||
893 | * Creates a SQL command for adding a check constraint to an existing table. |
||
894 | * @param string $name the name of the check constraint. |
||
895 | * The name will be properly quoted by the method. |
||
896 | * @param string $table the table that the check constraint will be added to. |
||
897 | * The name will be properly quoted by the method. |
||
898 | * @param string $expression the SQL of the `CHECK` constraint. |
||
899 | * @return $this the command object itself. |
||
900 | * @since 2.0.13 |
||
901 | */ |
||
902 | 1 | public function addCheck($name, $table, $expression) |
|
903 | { |
||
904 | 1 | $sql = $this->db->getQueryBuilder()->addCheck($name, $table, $expression); |
|
905 | |||
906 | 1 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
907 | } |
||
908 | |||
909 | /** |
||
910 | * Creates a SQL command for dropping a check constraint. |
||
911 | * @param string $name the name of the check constraint to be dropped. |
||
912 | * The name will be properly quoted by the method. |
||
913 | * @param string $table the table whose check constraint is to be dropped. |
||
914 | * The name will be properly quoted by the method. |
||
915 | * @return $this the command object itself. |
||
916 | * @since 2.0.13 |
||
917 | */ |
||
918 | 1 | public function dropCheck($name, $table) |
|
919 | { |
||
920 | 1 | $sql = $this->db->getQueryBuilder()->dropCheck($name, $table); |
|
921 | |||
922 | 1 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
923 | } |
||
924 | |||
925 | /** |
||
926 | * Creates a SQL command for adding a default value constraint to an existing table. |
||
927 | * @param string $name the name of the default value constraint. |
||
928 | * The name will be properly quoted by the method. |
||
929 | * @param string $table the table that the default value constraint will be added to. |
||
930 | * The name will be properly quoted by the method. |
||
931 | * @param string $column the name of the column to that the constraint will be added on. |
||
932 | * The name will be properly quoted by the method. |
||
933 | * @param mixed $value default value. |
||
934 | * @return $this the command object itself. |
||
935 | * @since 2.0.13 |
||
936 | */ |
||
937 | public function addDefaultValue($name, $table, $column, $value) |
||
938 | { |
||
939 | $sql = $this->db->getQueryBuilder()->addDefaultValue($name, $table, $column, $value); |
||
940 | |||
941 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
||
942 | } |
||
943 | |||
944 | /** |
||
945 | * Creates a SQL command for dropping a default value constraint. |
||
946 | * @param string $name the name of the default value constraint to be dropped. |
||
947 | * The name will be properly quoted by the method. |
||
948 | * @param string $table the table whose default value constraint is to be dropped. |
||
949 | * The name will be properly quoted by the method. |
||
950 | * @return $this the command object itself. |
||
951 | * @since 2.0.13 |
||
952 | */ |
||
953 | public function dropDefaultValue($name, $table) |
||
954 | { |
||
955 | $sql = $this->db->getQueryBuilder()->dropDefaultValue($name, $table); |
||
956 | |||
957 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
||
958 | } |
||
959 | |||
960 | /** |
||
961 | * Creates a SQL command for resetting the sequence value of a table's primary key. |
||
962 | * The sequence will be reset such that the primary key of the next new row inserted |
||
963 | * will have the specified value or the maximum existing value +1. |
||
964 | * @param string $table the name of the table whose primary key sequence will be reset |
||
965 | * @param mixed $value the value for the primary key of the next new row inserted. If this is not set, |
||
966 | * the next new row's primary key will have the maximum existing value +1. |
||
967 | * @return $this the command object itself |
||
968 | * @throws NotSupportedException if this is not supported by the underlying DBMS |
||
969 | */ |
||
970 | 25 | public function resetSequence($table, $value = null) |
|
971 | { |
||
972 | 25 | $sql = $this->db->getQueryBuilder()->resetSequence($table, $value); |
|
973 | |||
974 | 25 | return $this->setSql($sql); |
|
975 | } |
||
976 | |||
977 | /** |
||
978 | * Executes a db command resetting the sequence value of a table's primary key. |
||
979 | * Reason for execute is that some databases (Oracle) need several queries to do so. |
||
980 | * The sequence is reset such that the primary key of the next new row inserted |
||
981 | * will have the specified value or the maximum existing value +1. |
||
982 | * @param string $table the name of the table whose primary key sequence is reset |
||
983 | * @param mixed $value the value for the primary key of the next new row inserted. If this is not set, |
||
984 | * the next new row's primary key will have the maximum existing value +1. |
||
985 | * @throws NotSupportedException if this is not supported by the underlying DBMS |
||
986 | * @since 2.0.16 |
||
987 | */ |
||
988 | 25 | public function executeResetSequence($table, $value = null) |
|
989 | { |
||
990 | 25 | return $this->db->getQueryBuilder()->executeResetSequence($table, $value); |
|
991 | } |
||
992 | |||
993 | /** |
||
994 | * Builds a SQL command for enabling or disabling integrity check. |
||
995 | * @param bool $check whether to turn on or off the integrity check. |
||
996 | * @param string $schema the schema name of the tables. Defaults to empty string, meaning the current |
||
997 | * or default schema. |
||
998 | * @param string $table the table name. |
||
999 | * @return $this the command object itself |
||
1000 | * @throws NotSupportedException if this is not supported by the underlying DBMS |
||
1001 | */ |
||
1002 | 4 | public function checkIntegrity($check = true, $schema = '', $table = '') |
|
1003 | { |
||
1004 | 4 | $sql = $this->db->getQueryBuilder()->checkIntegrity($check, $schema, $table); |
|
1005 | |||
1006 | 4 | return $this->setSql($sql); |
|
1007 | } |
||
1008 | |||
1009 | /** |
||
1010 | * Builds a SQL command for adding comment to column. |
||
1011 | * |
||
1012 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the method. |
||
1013 | * @param string $column the name of the column to be commented. The column name will be properly quoted by the method. |
||
1014 | * @param string $comment the text of the comment to be added. The comment will be properly quoted by the method. |
||
1015 | * @return $this the command object itself |
||
1016 | * @since 2.0.8 |
||
1017 | */ |
||
1018 | 2 | public function addCommentOnColumn($table, $column, $comment) |
|
1019 | { |
||
1020 | 2 | $sql = $this->db->getQueryBuilder()->addCommentOnColumn($table, $column, $comment); |
|
1021 | |||
1022 | 2 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
1023 | } |
||
1024 | |||
1025 | /** |
||
1026 | * Builds a SQL command for adding comment to table. |
||
1027 | * |
||
1028 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the method. |
||
1029 | * @param string $comment the text of the comment to be added. The comment will be properly quoted by the method. |
||
1030 | * @return $this the command object itself |
||
1031 | * @since 2.0.8 |
||
1032 | */ |
||
1033 | public function addCommentOnTable($table, $comment) |
||
1034 | { |
||
1035 | $sql = $this->db->getQueryBuilder()->addCommentOnTable($table, $comment); |
||
1036 | |||
1037 | return $this->setSql($sql); |
||
1038 | } |
||
1039 | |||
1040 | /** |
||
1041 | * Builds a SQL command for dropping comment from column. |
||
1042 | * |
||
1043 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the method. |
||
1044 | * @param string $column the name of the column to be commented. The column name will be properly quoted by the method. |
||
1045 | * @return $this the command object itself |
||
1046 | * @since 2.0.8 |
||
1047 | */ |
||
1048 | 2 | public function dropCommentFromColumn($table, $column) |
|
1049 | { |
||
1050 | 2 | $sql = $this->db->getQueryBuilder()->dropCommentFromColumn($table, $column); |
|
1051 | |||
1052 | 2 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
1053 | } |
||
1054 | |||
1055 | /** |
||
1056 | * Builds a SQL command for dropping comment from table. |
||
1057 | * |
||
1058 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the method. |
||
1059 | * @return $this the command object itself |
||
1060 | * @since 2.0.8 |
||
1061 | */ |
||
1062 | public function dropCommentFromTable($table) |
||
1063 | { |
||
1064 | $sql = $this->db->getQueryBuilder()->dropCommentFromTable($table); |
||
1065 | |||
1066 | return $this->setSql($sql); |
||
1067 | } |
||
1068 | |||
1069 | /** |
||
1070 | * Creates a SQL View. |
||
1071 | * |
||
1072 | * @param string $viewName the name of the view to be created. |
||
1073 | * @param string|Query $subquery the select statement which defines the view. |
||
1074 | * This can be either a string or a [[Query]] object. |
||
1075 | * @return $this the command object itself. |
||
1076 | * @since 2.0.14 |
||
1077 | */ |
||
1078 | 3 | public function createView($viewName, $subquery) |
|
1079 | { |
||
1080 | 3 | $sql = $this->db->getQueryBuilder()->createView($viewName, $subquery); |
|
1081 | |||
1082 | 3 | return $this->setSql($sql)->requireTableSchemaRefresh($viewName); |
|
1083 | } |
||
1084 | |||
1085 | /** |
||
1086 | * Drops a SQL View. |
||
1087 | * |
||
1088 | * @param string $viewName the name of the view to be dropped. |
||
1089 | * @return $this the command object itself. |
||
1090 | * @since 2.0.14 |
||
1091 | */ |
||
1092 | 5 | public function dropView($viewName) |
|
1093 | { |
||
1094 | 5 | $sql = $this->db->getQueryBuilder()->dropView($viewName); |
|
1095 | |||
1096 | 5 | return $this->setSql($sql)->requireTableSchemaRefresh($viewName); |
|
1097 | } |
||
1098 | |||
1099 | /** |
||
1100 | * Executes the SQL statement. |
||
1101 | * This method should only be used for executing non-query SQL statement, such as `INSERT`, `DELETE`, `UPDATE` SQLs. |
||
1102 | * No result set will be returned. |
||
1103 | * @return int number of rows affected by the execution. |
||
1104 | * @throws Exception execution failed |
||
1105 | */ |
||
1106 | 724 | public function execute() |
|
1107 | { |
||
1108 | 724 | $sql = $this->getSql(); |
|
1109 | 724 | list($profile, $rawSql) = $this->logQuery(__METHOD__); |
|
1110 | |||
1111 | 724 | if ($sql == '') { |
|
1112 | 6 | return 0; |
|
1113 | } |
||
1114 | |||
1115 | 721 | $this->prepare(false); |
|
1116 | |||
1117 | try { |
||
1118 | 721 | $profile and Yii::beginProfile($rawSql, __METHOD__); |
|
1119 | |||
1120 | 721 | $this->internalExecute($rawSql); |
|
1121 | 718 | $n = $this->pdoStatement->rowCount(); |
|
1122 | |||
1123 | 718 | $profile and Yii::endProfile($rawSql, __METHOD__); |
|
1124 | |||
1125 | 718 | $this->refreshTableSchema(); |
|
1126 | |||
1127 | 718 | return $n; |
|
1128 | 18 | } catch (Exception $e) { |
|
1129 | 18 | $profile and Yii::endProfile($rawSql, __METHOD__); |
|
1130 | 18 | throw $e; |
|
1131 | } |
||
1132 | } |
||
1133 | |||
1134 | /** |
||
1135 | * Logs the current database query if query logging is enabled and returns |
||
1136 | * the profiling token if profiling is enabled. |
||
1137 | * @param string $category the log category. |
||
1138 | * @return array array of two elements, the first is boolean of whether profiling is enabled or not. |
||
1139 | * The second is the rawSql if it has been created. |
||
1140 | */ |
||
1141 | 1633 | protected function logQuery($category) |
|
1142 | { |
||
1143 | 1633 | if ($this->db->enableLogging) { |
|
1144 | 1633 | $rawSql = $this->getRawSql(); |
|
1145 | 1633 | Yii::info($rawSql, $category); |
|
1146 | } |
||
1147 | 1633 | if (!$this->db->enableProfiling) { |
|
1148 | 7 | return [false, isset($rawSql) ? $rawSql : null]; |
|
1149 | } |
||
1150 | |||
1151 | 1633 | return [true, isset($rawSql) ? $rawSql : $this->getRawSql()]; |
|
1152 | } |
||
1153 | |||
1154 | /** |
||
1155 | * Performs the actual DB query of a SQL statement. |
||
1156 | * @param string $method method of PDOStatement to be called |
||
1157 | * @param int|null $fetchMode the result fetch mode. Please refer to [PHP manual](https://www.php.net/manual/en/function.PDOStatement-setFetchMode.php) |
||
1158 | * for valid fetch modes. If this parameter is null, the value set in [[fetchMode]] will be used. |
||
1159 | * @return mixed the method execution result |
||
1160 | * @throws Exception if the query causes any problem |
||
1161 | * @since 2.0.1 this method is protected (was private before). |
||
1162 | */ |
||
1163 | 1580 | protected function queryInternal($method, $fetchMode = null) |
|
1164 | { |
||
1165 | 1580 | list($profile, $rawSql) = $this->logQuery('yii\db\Command::query'); |
|
1166 | |||
1167 | 1580 | if ($method !== '') { |
|
1168 | 1577 | $info = $this->db->getQueryCacheInfo($this->queryCacheDuration, $this->queryCacheDependency); |
|
1169 | 1577 | if (is_array($info)) { |
|
1170 | /* @var $cache \yii\caching\CacheInterface */ |
||
1171 | 6 | $cache = $info[0]; |
|
1172 | 6 | $cacheKey = $this->getCacheKey($method, $fetchMode, ''); |
|
1173 | 6 | $result = $cache->get($cacheKey); |
|
1174 | 6 | if (is_array($result) && array_key_exists(0, $result)) { |
|
1175 | 6 | Yii::debug('Query result served from cache', 'yii\db\Command::query'); |
|
1176 | 6 | return $result[0]; |
|
1177 | } |
||
1178 | } |
||
1179 | } |
||
1180 | |||
1181 | 1580 | $this->prepare(true); |
|
1182 | |||
1183 | try { |
||
1184 | 1579 | $profile and Yii::beginProfile($rawSql, 'yii\db\Command::query'); |
|
1185 | |||
1186 | 1579 | $this->internalExecute($rawSql); |
|
1187 | |||
1188 | 1576 | if ($method === '') { |
|
1189 | 15 | $result = new DataReader($this); |
|
1190 | } else { |
||
1191 | 1573 | if ($fetchMode === null) { |
|
1192 | 1441 | $fetchMode = $this->fetchMode; |
|
1193 | } |
||
1194 | 1573 | $result = call_user_func_array([$this->pdoStatement, $method], (array) $fetchMode); |
|
1195 | 1573 | $this->pdoStatement->closeCursor(); |
|
1196 | } |
||
1197 | |||
1198 | 1576 | $profile and Yii::endProfile($rawSql, 'yii\db\Command::query'); |
|
1199 | 22 | } catch (Exception $e) { |
|
1200 | 22 | $profile and Yii::endProfile($rawSql, 'yii\db\Command::query'); |
|
1201 | 22 | throw $e; |
|
1202 | } |
||
1203 | |||
1204 | 1576 | if (isset($cache, $cacheKey, $info)) { |
|
1205 | 6 | $cache->set($cacheKey, [$result], $info[1], $info[2]); |
|
1206 | 6 | Yii::debug('Saved query result in cache', 'yii\db\Command::query'); |
|
1207 | } |
||
1208 | |||
1209 | 1576 | return $result; |
|
1210 | } |
||
1211 | |||
1212 | /** |
||
1213 | * Returns the cache key for the query. |
||
1214 | * |
||
1215 | * @param string $method method of PDOStatement to be called |
||
1216 | * @param int $fetchMode the result fetch mode. Please refer to [PHP manual](https://www.php.net/manual/en/function.PDOStatement-setFetchMode.php) |
||
1217 | * for valid fetch modes. |
||
1218 | * @return array the cache key |
||
1219 | * @since 2.0.16 |
||
1220 | */ |
||
1221 | 6 | protected function getCacheKey($method, $fetchMode, $rawSql) |
|
1222 | { |
||
1223 | 6 | $params = $this->params; |
|
1224 | 6 | ksort($params); |
|
1225 | return [ |
||
1226 | 6 | __CLASS__, |
|
1227 | 6 | $method, |
|
1228 | 6 | $fetchMode, |
|
1229 | 6 | $this->db->dsn, |
|
1230 | 6 | $this->db->username, |
|
1231 | 6 | $this->getSql(), |
|
1232 | 6 | json_encode($params), |
|
1233 | ]; |
||
1234 | } |
||
1235 | |||
1236 | /** |
||
1237 | * Marks a specified table schema to be refreshed after command execution. |
||
1238 | * @param string $name name of the table, which schema should be refreshed. |
||
1239 | * @return $this this command instance |
||
1240 | * @since 2.0.6 |
||
1241 | */ |
||
1242 | 173 | protected function requireTableSchemaRefresh($name) |
|
1243 | { |
||
1244 | 173 | $this->_refreshTableName = $name; |
|
1245 | 173 | return $this; |
|
1246 | } |
||
1247 | |||
1248 | /** |
||
1249 | * Refreshes table schema, which was marked by [[requireTableSchemaRefresh()]]. |
||
1250 | * @since 2.0.6 |
||
1251 | */ |
||
1252 | 718 | protected function refreshTableSchema() |
|
1253 | { |
||
1254 | 718 | if ($this->_refreshTableName !== null) { |
|
1255 | 170 | $this->db->getSchema()->refreshTableSchema($this->_refreshTableName); |
|
1256 | } |
||
1257 | 718 | } |
|
1258 | |||
1259 | /** |
||
1260 | * Marks the command to be executed in transaction. |
||
1261 | * @param string|null $isolationLevel The isolation level to use for this transaction. |
||
1262 | * See [[Transaction::begin()]] for details. |
||
1263 | * @return $this this command instance. |
||
1264 | * @since 2.0.14 |
||
1265 | */ |
||
1266 | 3 | protected function requireTransaction($isolationLevel = null) |
|
1267 | { |
||
1268 | 3 | $this->_isolationLevel = $isolationLevel; |
|
1269 | 3 | return $this; |
|
1270 | } |
||
1271 | |||
1272 | /** |
||
1273 | * Sets a callable (e.g. anonymous function) that is called when [[Exception]] is thrown |
||
1274 | * when executing the command. The signature of the callable should be: |
||
1275 | * |
||
1276 | * ```php |
||
1277 | * function (\yii\db\Exception $e, $attempt) |
||
1278 | * { |
||
1279 | * // return true or false (whether to retry the command or rethrow $e) |
||
1280 | * } |
||
1281 | * ``` |
||
1282 | * |
||
1283 | * The callable will recieve a database exception thrown and a current attempt |
||
1284 | * (to execute the command) number starting from 1. |
||
1285 | * |
||
1286 | * @param callable $handler a PHP callback to handle database exceptions. |
||
1287 | * @return $this this command instance. |
||
1288 | * @since 2.0.14 |
||
1289 | */ |
||
1290 | 3 | protected function setRetryHandler(callable $handler) |
|
1291 | { |
||
1292 | 3 | $this->_retryHandler = $handler; |
|
1293 | 3 | return $this; |
|
1294 | } |
||
1295 | |||
1296 | /** |
||
1297 | * Executes a prepared statement. |
||
1298 | * |
||
1299 | * It's a wrapper around [[\PDOStatement::execute()]] to support transactions |
||
1300 | * and retry handlers. |
||
1301 | * |
||
1302 | * @param string|null $rawSql the rawSql if it has been created. |
||
1303 | * @throws Exception if execution failed. |
||
1304 | * @since 2.0.14 |
||
1305 | */ |
||
1306 | 1632 | protected function internalExecute($rawSql) |
|
1307 | { |
||
1308 | 1632 | $attempt = 0; |
|
1309 | 1632 | while (true) { |
|
1310 | try { |
||
1311 | if ( |
||
1312 | 1632 | ++$attempt === 1 |
|
1313 | 1632 | && $this->_isolationLevel !== false |
|
1314 | 1632 | && $this->db->getTransaction() === null |
|
1315 | ) { |
||
1316 | 3 | $this->db->transaction(function () use ($rawSql) { |
|
1317 | 3 | $this->internalExecute($rawSql); |
|
1318 | 3 | }, $this->_isolationLevel); |
|
1319 | } else { |
||
1320 | 1632 | $this->pdoStatement->execute(); |
|
1321 | } |
||
1322 | 1629 | break; |
|
1323 | 36 | } catch (\Exception $e) { |
|
1324 | 36 | $rawSql = $rawSql ?: $this->getRawSql(); |
|
1325 | 36 | $e = $this->db->getSchema()->convertException($e, $rawSql); |
|
1326 | 36 | if ($this->_retryHandler === null || !call_user_func($this->_retryHandler, $e, $attempt)) { |
|
1327 | 36 | throw $e; |
|
1328 | } |
||
1329 | } |
||
1330 | } |
||
1331 | 1629 | } |
|
1332 | |||
1333 | /** |
||
1334 | * Resets command properties to their initial state. |
||
1335 | * |
||
1336 | * @since 2.0.13 |
||
1337 | */ |
||
1338 | 1679 | protected function reset() |
|
1339 | { |
||
1340 | 1679 | $this->_sql = null; |
|
1341 | 1679 | $this->pendingParams = []; |
|
1342 | 1679 | $this->params = []; |
|
1343 | 1679 | $this->_refreshTableName = null; |
|
1344 | 1679 | $this->_isolationLevel = false; |
|
1345 | 1679 | } |
|
1346 | } |
||
1347 |
The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g.
excluded_paths: ["lib/*"]
, you can move it to the dependency path list as follows:For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths