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; |
||
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://secure.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|false|null 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 $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 $dependency the cache dependency associated with the cached query result. |
||
123 | * @return $this the command object itself |
||
124 | */ |
||
125 | 55 | public function cache($duration = null, $dependency = null) |
|
126 | { |
||
127 | 55 | $this->queryCacheDuration = $duration === null ? $this->db->queryCacheDuration : $duration; |
|
128 | 55 | $this->queryCacheDependency = $dependency; |
|
129 | 55 | return $this; |
|
130 | } |
||
131 | |||
132 | /** |
||
133 | * Disables query cache for this command. |
||
134 | * @return $this the command object itself |
||
135 | */ |
||
136 | 5 | public function noCache() |
|
137 | { |
||
138 | 5 | $this->queryCacheDuration = -1; |
|
139 | 5 | return $this; |
|
140 | } |
||
141 | |||
142 | /** |
||
143 | * Returns the SQL statement for this command. |
||
144 | * @return string the SQL statement to be executed |
||
145 | */ |
||
146 | 2399 | public function getSql() |
|
147 | { |
||
148 | 2399 | 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 | 2450 | public function setSql($sql) |
|
162 | { |
||
163 | 2450 | if ($sql !== $this->_sql) { |
|
164 | 2450 | $this->cancel(); |
|
165 | 2450 | $this->reset(); |
|
166 | 2450 | $this->_sql = $this->db->quoteSql($sql); |
|
167 | } |
||
168 | |||
169 | 2450 | 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 | 45 | public function setRawSql($sql) |
|
184 | { |
||
185 | 45 | if ($sql !== $this->_sql) { |
|
186 | 45 | $this->cancel(); |
|
187 | 45 | $this->reset(); |
|
188 | 45 | $this->_sql = $sql; |
|
189 | } |
||
190 | |||
191 | 45 | 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 | 2417 | public function getRawSql() |
|
201 | { |
||
202 | 2417 | if (empty($this->params)) { |
|
203 | 2053 | return $this->_sql; |
|
204 | } |
||
205 | 1901 | $params = []; |
|
206 | 1901 | foreach ($this->params as $name => $value) { |
|
207 | 1901 | if (is_string($name) && strncmp(':', $name, 1)) { |
|
208 | 23 | $name = ':' . $name; |
|
209 | } |
||
210 | 1901 | if (is_string($value) || $value instanceof Expression) { |
|
211 | 1623 | $params[$name] = $this->db->quoteValue((string)$value); |
|
212 | 1266 | } elseif (is_bool($value)) { |
|
213 | 36 | $params[$name] = ($value ? 'TRUE' : 'FALSE'); |
|
214 | 1257 | } elseif ($value === null) { |
|
215 | 351 | $params[$name] = 'NULL'; |
|
216 | 1198 | } elseif (!is_object($value) && !is_resource($value)) { |
|
217 | 1901 | $params[$name] = $value; |
|
218 | } |
||
219 | } |
||
220 | 1901 | if (!isset($params[1])) { |
|
221 | return preg_replace_callback('#(:\w+)#', function($matches) use ($params) { |
||
222 | 1896 | $m = $matches[1]; |
|
223 | 1896 | return isset($params[$m]) ? $params[$m] : $m; |
|
224 | 1896 | }, $this->_sql); |
|
225 | } |
||
226 | 5 | $sql = ''; |
|
227 | 5 | foreach (explode('?', $this->_sql) as $i => $part) { |
|
228 | 5 | $sql .= (isset($params[$i]) ? $params[$i] : '') . $part; |
|
229 | } |
||
230 | |||
231 | 5 | 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 $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 | 2379 | public function prepare($forRead = null) |
|
245 | { |
||
246 | 2379 | if ($this->pdoStatement) { |
|
247 | 68 | $this->bindPendingParams(); |
|
248 | 68 | return; |
|
249 | } |
||
250 | |||
251 | 2379 | $sql = $this->getSql(); |
|
252 | 2379 | if ($sql === '') { |
|
253 | 3 | return; |
|
254 | } |
||
255 | |||
256 | 2379 | if ($this->db->getTransaction()) { |
|
257 | // master is in a transaction. use the same connection. |
||
258 | 33 | $forRead = false; |
|
259 | } |
||
260 | 2379 | if ($forRead || $forRead === null && $this->db->getSchema()->isReadQuery($sql)) { |
|
261 | 2312 | $pdo = $this->db->getSlavePdo(); |
|
262 | } else { |
||
263 | 996 | $pdo = $this->db->getMasterPdo(); |
|
264 | } |
||
265 | |||
266 | try { |
||
267 | 2379 | $this->pdoStatement = $pdo->prepare($sql); |
|
268 | 2378 | $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, (int) $e->getCode(), $e); |
|
273 | } catch (\Throwable $e) { |
||
274 | $message = $e->getMessage() . "\nFailed to prepare SQL: $sql"; |
||
275 | throw new Exception($message, null, (int) $e->getCode(), $e); |
||
276 | } |
||
277 | 2378 | } |
|
278 | |||
279 | /** |
||
280 | * Cancels the execution of the SQL statement. |
||
281 | * This method mainly sets [[pdoStatement]] to be null. |
||
282 | */ |
||
283 | 2450 | public function cancel() |
|
284 | { |
||
285 | 2450 | $this->pdoStatement = null; |
|
286 | 2450 | } |
|
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 $dataType SQL data type of the parameter. If null, the type is determined by the PHP type of the value. |
||
296 | * @param int $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://secure.php.net/manual/en/function.PDOStatement-bindParam.php |
||
300 | */ |
||
301 | 5 | public function bindParam($name, &$value, $dataType = null, $length = null, $driverOptions = null) |
|
302 | { |
||
303 | 5 | $this->prepare(); |
|
304 | |||
305 | 5 | if ($dataType === null) { |
|
306 | 5 | $dataType = $this->db->getSchema()->getPdoType($value); |
|
307 | } |
||
308 | 5 | if ($length === null) { |
|
309 | 5 | $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 | 5 | $this->params[$name] = &$value; |
|
316 | |||
317 | 5 | 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 | 2378 | protected function bindPendingParams() |
|
325 | { |
||
326 | 2378 | foreach ($this->pendingParams as $name => $value) { |
|
327 | 1864 | $this->pdoStatement->bindValue($name, $value[0], $value[1]); |
|
328 | } |
||
329 | 2378 | $this->pendingParams = []; |
|
330 | 2378 | } |
|
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 $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://secure.php.net/manual/en/function.PDOStatement-bindValue.php |
||
342 | */ |
||
343 | 10 | public function bindValue($name, $value, $dataType = null) |
|
344 | { |
||
345 | 10 | if ($dataType === null) { |
|
346 | 10 | $dataType = $this->db->getSchema()->getPdoType($value); |
|
347 | } |
||
348 | 10 | $this->pendingParams[$name] = [$value, $dataType]; |
|
349 | 10 | $this->params[$name] = $value; |
|
350 | |||
351 | 10 | 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 | 2450 | public function bindValues($values) |
|
366 | { |
||
367 | 2450 | if (empty($values)) { |
|
368 | 2091 | return $this; |
|
369 | } |
||
370 | |||
371 | 1911 | $schema = $this->db->getSchema(); |
|
372 | 1911 | foreach ($values as $name => $value) { |
|
373 | 1911 | if (is_array($value)) { // TODO: Drop in Yii 2.1 |
|
374 | 5 | $this->pendingParams[$name] = $value; |
|
375 | 5 | $this->params[$name] = $value[0]; |
|
376 | 1906 | } elseif ($value instanceof PdoValue) { |
|
377 | 122 | $this->pendingParams[$name] = [$value->getValue(), $value->getType()]; |
|
378 | 122 | $this->params[$name] = $value->getValue(); |
|
379 | } else { |
||
380 | 1906 | $type = $schema->getPdoType($value); |
|
381 | 1906 | $this->pendingParams[$name] = [$value, $type]; |
|
382 | 1911 | $this->params[$name] = $value; |
|
383 | } |
||
384 | } |
||
385 | |||
386 | 1911 | return $this; |
|
387 | } |
||
388 | |||
389 | /** |
||
390 | * Executes the SQL statement and returns query result. |
||
391 | * This method is for executing a SQL query that returns result set, such as `SELECT`. |
||
392 | * @return DataReader the reader object for fetching the query result |
||
393 | * @throws Exception execution failed |
||
394 | */ |
||
395 | 25 | public function query() |
|
396 | { |
||
397 | 25 | return $this->queryInternal(''); |
|
398 | } |
||
399 | |||
400 | /** |
||
401 | * Executes the SQL statement and returns ALL rows at once. |
||
402 | * @param int $fetchMode the result fetch mode. Please refer to [PHP manual](https://secure.php.net/manual/en/function.PDOStatement-setFetchMode.php) |
||
403 | * for valid fetch modes. If this parameter is null, the value set in [[fetchMode]] will be used. |
||
404 | * @return array all rows of the query result. Each array element is an array representing a row of data. |
||
405 | * An empty array is returned if the query results in nothing. |
||
406 | * @throws Exception execution failed |
||
407 | */ |
||
408 | 2109 | public function queryAll($fetchMode = null) |
|
409 | { |
||
410 | 2109 | return $this->queryInternal('fetchAll', $fetchMode); |
|
411 | } |
||
412 | |||
413 | /** |
||
414 | * Executes the SQL statement and returns the first row of the result. |
||
415 | * This method is best used when only the first row of result is needed for a query. |
||
416 | * @param int $fetchMode the result fetch mode. Please refer to [PHP manual](https://secure.php.net/manual/en/pdostatement.setfetchmode.php) |
||
417 | * for valid fetch modes. If this parameter is null, the value set in [[fetchMode]] will be used. |
||
418 | * @return array|false the first row (in terms of an array) of the query result. False is returned if the query |
||
419 | * results in nothing. |
||
420 | * @throws Exception execution failed |
||
421 | */ |
||
422 | 730 | public function queryOne($fetchMode = null) |
|
423 | { |
||
424 | 730 | return $this->queryInternal('fetch', $fetchMode); |
|
425 | } |
||
426 | |||
427 | /** |
||
428 | * Executes the SQL statement and returns the value of the first column in the first row of data. |
||
429 | * This method is best used when only a single value is needed for a query. |
||
430 | * @return string|int|null|false the value of the first column in the first row of the query result. |
||
431 | * False is returned if there is no value. |
||
432 | * @throws Exception execution failed |
||
433 | */ |
||
434 | 544 | public function queryScalar() |
|
435 | { |
||
436 | 544 | $result = $this->queryInternal('fetchColumn', 0); |
|
437 | 539 | if (is_resource($result) && get_resource_type($result) === 'stream') { |
|
438 | 21 | return stream_get_contents($result); |
|
439 | } |
||
440 | |||
441 | 531 | return $result; |
|
442 | } |
||
443 | |||
444 | /** |
||
445 | * Executes the SQL statement and returns the first column of the result. |
||
446 | * This method is best used when only the first column of result (i.e. the first element in each row) |
||
447 | * is needed for a query. |
||
448 | * @return array the first column of the query result. Empty array is returned if the query results in nothing. |
||
449 | * @throws Exception execution failed |
||
450 | */ |
||
451 | 133 | public function queryColumn() |
|
452 | { |
||
453 | 133 | return $this->queryInternal('fetchAll', \PDO::FETCH_COLUMN); |
|
454 | } |
||
455 | |||
456 | /** |
||
457 | * Creates an INSERT command. |
||
458 | * |
||
459 | * For example, |
||
460 | * |
||
461 | * ```php |
||
462 | * $connection->createCommand()->insert('user', [ |
||
463 | * 'name' => 'Sam', |
||
464 | * 'age' => 30, |
||
465 | * ])->execute(); |
||
466 | * ``` |
||
467 | * |
||
468 | * The method will properly escape the column names, and bind the values to be inserted. |
||
469 | * |
||
470 | * Note that the created command is not executed until [[execute()]] is called. |
||
471 | * |
||
472 | * @param string $table the table that new rows will be inserted into. |
||
473 | * @param array|\yii\db\Query $columns the column data (name => value) to be inserted into the table or instance |
||
474 | * of [[yii\db\Query|Query]] to perform INSERT INTO ... SELECT SQL statement. |
||
475 | * Passing of [[yii\db\Query|Query]] is available since version 2.0.11. |
||
476 | * @return $this the command object itself |
||
477 | */ |
||
478 | 558 | public function insert($table, $columns) |
|
479 | { |
||
480 | 558 | $params = []; |
|
481 | 558 | $sql = $this->db->getQueryBuilder()->insert($table, $columns, $params); |
|
482 | |||
483 | 543 | return $this->setSql($sql)->bindValues($params); |
|
484 | } |
||
485 | |||
486 | /** |
||
487 | * Creates a batch INSERT command. |
||
488 | * |
||
489 | * For example, |
||
490 | * |
||
491 | * ```php |
||
492 | * $connection->createCommand()->batchInsert('user', ['name', 'age'], [ |
||
493 | * ['Tom', 30], |
||
494 | * ['Jane', 20], |
||
495 | * ['Linda', 25], |
||
496 | * ])->execute(); |
||
497 | * ``` |
||
498 | * |
||
499 | * The method will properly escape the column names, and quote the values to be inserted. |
||
500 | * |
||
501 | * Note that the values in each row must match the corresponding column names. |
||
502 | * |
||
503 | * Also note that the created command is not executed until [[execute()]] is called. |
||
504 | * |
||
505 | * @param string $table the table that new rows will be inserted into. |
||
506 | * @param array $columns the column names |
||
507 | * @param array|\Generator $rows the rows to be batch inserted into the table |
||
508 | * @return $this the command object itself |
||
509 | */ |
||
510 | 45 | public function batchInsert($table, $columns, $rows) |
|
511 | { |
||
512 | 45 | $table = $this->db->quoteSql($table); |
|
513 | $columns = array_map(function ($column) { |
||
514 | 45 | return $this->db->quoteSql($column); |
|
515 | 45 | }, $columns); |
|
516 | |||
517 | 45 | $params = []; |
|
518 | 45 | $sql = $this->db->getQueryBuilder()->batchInsert($table, $columns, $rows, $params); |
|
519 | |||
520 | 45 | $this->setRawSql($sql); |
|
521 | 45 | $this->bindValues($params); |
|
522 | |||
523 | 45 | return $this; |
|
524 | } |
||
525 | |||
526 | /** |
||
527 | * Creates a command to insert rows into a database table if |
||
528 | * they do not already exist (matching unique constraints), |
||
529 | * or update them if they do. |
||
530 | * |
||
531 | * For example, |
||
532 | * |
||
533 | * ```php |
||
534 | * $sql = $queryBuilder->upsert('pages', [ |
||
535 | * 'name' => 'Front page', |
||
536 | * 'url' => 'http://example.com/', // url is unique |
||
537 | * 'visits' => 0, |
||
538 | * ], [ |
||
539 | * 'visits' => new \yii\db\Expression('visits + 1'), |
||
540 | * ], $params); |
||
541 | * ``` |
||
542 | * |
||
543 | * The method will properly escape the table and column names. |
||
544 | * |
||
545 | * @param string $table the table that new rows will be inserted into/updated in. |
||
546 | * @param array|Query $insertColumns the column data (name => value) to be inserted into the table or instance |
||
547 | * of [[Query]] to perform `INSERT INTO ... SELECT` SQL statement. |
||
548 | * @param array|bool $updateColumns the column data (name => value) to be updated if they already exist. |
||
549 | * If `true` is passed, the column data will be updated to match the insert column data. |
||
550 | * If `false` is passed, no update will be performed if the column data already exists. |
||
551 | * @param array $params the parameters to be bound to the command. |
||
552 | * @return $this the command object itself. |
||
553 | * @since 2.0.14 |
||
554 | */ |
||
555 | 129 | public function upsert($table, $insertColumns, $updateColumns = true, $params = []) |
|
556 | { |
||
557 | 129 | $sql = $this->db->getQueryBuilder()->upsert($table, $insertColumns, $updateColumns, $params); |
|
558 | |||
559 | 129 | return $this->setSql($sql)->bindValues($params); |
|
560 | } |
||
561 | |||
562 | /** |
||
563 | * Creates an UPDATE command. |
||
564 | * |
||
565 | * For example, |
||
566 | * |
||
567 | * ```php |
||
568 | * $connection->createCommand()->update('user', ['status' => 1], 'age > 30')->execute(); |
||
569 | * ``` |
||
570 | * |
||
571 | * or with using parameter binding for the condition: |
||
572 | * |
||
573 | * ```php |
||
574 | * $minAge = 30; |
||
575 | * $connection->createCommand()->update('user', ['status' => 1], 'age > :minAge', [':minAge' => $minAge])->execute(); |
||
576 | * ``` |
||
577 | * |
||
578 | * The method will properly escape the column names and bind the values to be updated. |
||
579 | * |
||
580 | * Note that the created command is not executed until [[execute()]] is called. |
||
581 | * |
||
582 | * @param string $table the table to be updated. |
||
583 | * @param array $columns the column data (name => value) to be updated. |
||
584 | * @param string|array $condition the condition that will be put in the WHERE part. Please |
||
585 | * refer to [[Query::where()]] on how to specify condition. |
||
586 | * @param array $params the parameters to be bound to the command |
||
587 | * @return $this the command object itself |
||
588 | */ |
||
589 | 131 | public function update($table, $columns, $condition = '', $params = []) |
|
590 | { |
||
591 | 131 | $sql = $this->db->getQueryBuilder()->update($table, $columns, $condition, $params); |
|
592 | |||
593 | 131 | return $this->setSql($sql)->bindValues($params); |
|
594 | } |
||
595 | |||
596 | /** |
||
597 | * Creates a DELETE command. |
||
598 | * |
||
599 | * For example, |
||
600 | * |
||
601 | * ```php |
||
602 | * $connection->createCommand()->delete('user', 'status = 0')->execute(); |
||
603 | * ``` |
||
604 | * |
||
605 | * or with using parameter binding for the condition: |
||
606 | * |
||
607 | * ```php |
||
608 | * $status = 0; |
||
609 | * $connection->createCommand()->delete('user', 'status = :status', [':status' => $status])->execute(); |
||
610 | * ``` |
||
611 | * |
||
612 | * The method will properly escape the table and column names. |
||
613 | * |
||
614 | * Note that the created command is not executed until [[execute()]] is called. |
||
615 | * |
||
616 | * @param string $table the table where the data will be deleted from. |
||
617 | * @param string|array $condition the condition that will be put in the WHERE part. Please |
||
618 | * refer to [[Query::where()]] on how to specify condition. |
||
619 | * @param array $params the parameters to be bound to the command |
||
620 | * @return $this the command object itself |
||
621 | */ |
||
622 | 452 | public function delete($table, $condition = '', $params = []) |
|
623 | { |
||
624 | 452 | $sql = $this->db->getQueryBuilder()->delete($table, $condition, $params); |
|
625 | |||
626 | 452 | return $this->setSql($sql)->bindValues($params); |
|
627 | } |
||
628 | |||
629 | /** |
||
630 | * Creates a SQL command for creating a new DB table. |
||
631 | * |
||
632 | * The columns in the new table should be specified as name-definition pairs (e.g. 'name' => 'string'), |
||
633 | * where name stands for a column name which will be properly quoted by the method, and definition |
||
634 | * stands for the column type which can contain an abstract DB type. |
||
635 | * The method [[QueryBuilder::getColumnType()]] will be called |
||
636 | * to convert the abstract column types to physical ones. For example, `string` will be converted |
||
637 | * as `varchar(255)`, and `string not null` becomes `varchar(255) not null`. |
||
638 | * |
||
639 | * If a column is specified with definition only (e.g. 'PRIMARY KEY (name, type)'), it will be directly |
||
640 | * inserted into the generated SQL. |
||
641 | * |
||
642 | * @param string $table the name of the table to be created. The name will be properly quoted by the method. |
||
643 | * @param array $columns the columns (name => definition) in the new table. |
||
644 | * @param string $options additional SQL fragment that will be appended to the generated SQL. |
||
645 | * @return $this the command object itself |
||
646 | */ |
||
647 | 236 | public function createTable($table, $columns, $options = null) |
|
648 | { |
||
649 | 236 | $sql = $this->db->getQueryBuilder()->createTable($table, $columns, $options); |
|
650 | |||
651 | 236 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
652 | } |
||
653 | |||
654 | /** |
||
655 | * Creates a SQL command for renaming a DB table. |
||
656 | * @param string $table the table to be renamed. The name will be properly quoted by the method. |
||
657 | * @param string $newName the new table name. The name will be properly quoted by the method. |
||
658 | * @return $this the command object itself |
||
659 | */ |
||
660 | 10 | public function renameTable($table, $newName) |
|
661 | { |
||
662 | 10 | $sql = $this->db->getQueryBuilder()->renameTable($table, $newName); |
|
663 | |||
664 | 10 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
665 | } |
||
666 | |||
667 | /** |
||
668 | * Creates a SQL command for dropping a DB table. |
||
669 | * @param string $table the table to be dropped. The name will be properly quoted by the method. |
||
670 | * @return $this the command object itself |
||
671 | */ |
||
672 | 109 | public function dropTable($table) |
|
673 | { |
||
674 | 109 | $sql = $this->db->getQueryBuilder()->dropTable($table); |
|
675 | |||
676 | 109 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
677 | } |
||
678 | |||
679 | /** |
||
680 | * Creates a SQL command for truncating a DB table. |
||
681 | * @param string $table the table to be truncated. The name will be properly quoted by the method. |
||
682 | * @return $this the command object itself |
||
683 | */ |
||
684 | 23 | public function truncateTable($table) |
|
685 | { |
||
686 | 23 | $sql = $this->db->getQueryBuilder()->truncateTable($table); |
|
687 | |||
688 | 23 | return $this->setSql($sql); |
|
689 | } |
||
690 | |||
691 | /** |
||
692 | * Creates a SQL command for adding a new DB column. |
||
693 | * @param string $table the table that the new column will be added to. The table name will be properly quoted by the method. |
||
694 | * @param string $column the name of the new column. The name will be properly quoted by the method. |
||
695 | * @param string $type the column type. [[\yii\db\QueryBuilder::getColumnType()]] will be called |
||
696 | * to convert the give column type to the physical one. For example, `string` will be converted |
||
697 | * as `varchar(255)`, and `string not null` becomes `varchar(255) not null`. |
||
698 | * @return $this the command object itself |
||
699 | */ |
||
700 | 9 | public function addColumn($table, $column, $type) |
|
701 | { |
||
702 | 9 | $sql = $this->db->getQueryBuilder()->addColumn($table, $column, $type); |
|
703 | |||
704 | 9 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
705 | } |
||
706 | |||
707 | /** |
||
708 | * Creates a SQL command for dropping a DB column. |
||
709 | * @param string $table the table whose column is to be dropped. The name will be properly quoted by the method. |
||
710 | * @param string $column the name of the column to be dropped. The name will be properly quoted by the method. |
||
711 | * @return $this the command object itself |
||
712 | */ |
||
713 | public function dropColumn($table, $column) |
||
714 | { |
||
715 | $sql = $this->db->getQueryBuilder()->dropColumn($table, $column); |
||
716 | |||
717 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
||
718 | } |
||
719 | |||
720 | /** |
||
721 | * Creates a SQL command for renaming a column. |
||
722 | * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method. |
||
723 | * @param string $oldName the old name of the column. The name will be properly quoted by the method. |
||
724 | * @param string $newName the new name of the column. The name will be properly quoted by the method. |
||
725 | * @return $this the command object itself |
||
726 | */ |
||
727 | public function renameColumn($table, $oldName, $newName) |
||
728 | { |
||
729 | $sql = $this->db->getQueryBuilder()->renameColumn($table, $oldName, $newName); |
||
730 | |||
731 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
||
732 | } |
||
733 | |||
734 | /** |
||
735 | * Creates a SQL command for changing the definition of a column. |
||
736 | * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method. |
||
737 | * @param string $column the name of the column to be changed. The name will be properly quoted by the method. |
||
738 | * @param string $type the column type. [[\yii\db\QueryBuilder::getColumnType()]] will be called |
||
739 | * to convert the give column type to the physical one. For example, `string` will be converted |
||
740 | * as `varchar(255)`, and `string not null` becomes `varchar(255) not null`. |
||
741 | * @return $this the command object itself |
||
742 | */ |
||
743 | 4 | public function alterColumn($table, $column, $type) |
|
744 | { |
||
745 | 4 | $sql = $this->db->getQueryBuilder()->alterColumn($table, $column, $type); |
|
746 | |||
747 | 4 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
748 | } |
||
749 | |||
750 | /** |
||
751 | * Creates a SQL command for adding a primary key constraint to an existing table. |
||
752 | * The method will properly quote the table and column names. |
||
753 | * @param string $name the name of the primary key constraint. |
||
754 | * @param string $table the table that the primary key constraint will be added to. |
||
755 | * @param string|array $columns comma separated string or array of columns that the primary key will consist of. |
||
756 | * @return $this the command object itself. |
||
757 | */ |
||
758 | 4 | public function addPrimaryKey($name, $table, $columns) |
|
759 | { |
||
760 | 4 | $sql = $this->db->getQueryBuilder()->addPrimaryKey($name, $table, $columns); |
|
761 | |||
762 | 4 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
763 | } |
||
764 | |||
765 | /** |
||
766 | * Creates a SQL command for removing a primary key constraint to an existing table. |
||
767 | * @param string $name the name of the primary key constraint to be removed. |
||
768 | * @param string $table the table that the primary key constraint will be removed from. |
||
769 | * @return $this the command object itself |
||
770 | */ |
||
771 | 4 | public function dropPrimaryKey($name, $table) |
|
772 | { |
||
773 | 4 | $sql = $this->db->getQueryBuilder()->dropPrimaryKey($name, $table); |
|
774 | |||
775 | 4 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
776 | } |
||
777 | |||
778 | /** |
||
779 | * Creates a SQL command for adding a foreign key constraint to an existing table. |
||
780 | * The method will properly quote the table and column names. |
||
781 | * @param string $name the name of the foreign key constraint. |
||
782 | * @param string $table the table that the foreign key constraint will be added to. |
||
783 | * @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. |
||
784 | * @param string $refTable the table that the foreign key references to. |
||
785 | * @param string|array $refColumns the name of the column that the foreign key references to. If there are multiple columns, separate them with commas. |
||
786 | * @param string $delete the ON DELETE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL |
||
787 | * @param string $update the ON UPDATE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL |
||
788 | * @return $this the command object itself |
||
789 | */ |
||
790 | 6 | public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null) |
|
791 | { |
||
792 | 6 | $sql = $this->db->getQueryBuilder()->addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete, $update); |
|
793 | |||
794 | 6 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
795 | } |
||
796 | |||
797 | /** |
||
798 | * Creates a SQL command for dropping a foreign key constraint. |
||
799 | * @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted by the method. |
||
800 | * @param string $table the table whose foreign is to be dropped. The name will be properly quoted by the method. |
||
801 | * @return $this the command object itself |
||
802 | */ |
||
803 | 7 | public function dropForeignKey($name, $table) |
|
804 | { |
||
805 | 7 | $sql = $this->db->getQueryBuilder()->dropForeignKey($name, $table); |
|
806 | |||
807 | 7 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
808 | } |
||
809 | |||
810 | /** |
||
811 | * Creates a SQL command for creating a new index. |
||
812 | * @param string $name the name of the index. The name will be properly quoted by the method. |
||
813 | * @param string $table the table that the new index will be created for. The table name will be properly quoted by the method. |
||
814 | * @param string|array $columns the column(s) that should be included in the index. If there are multiple columns, please separate them |
||
815 | * by commas. The column names will be properly quoted by the method. |
||
816 | * @param bool $unique whether to add UNIQUE constraint on the created index. |
||
817 | * @return $this the command object itself |
||
818 | */ |
||
819 | 14 | public function createIndex($name, $table, $columns, $unique = false) |
|
820 | { |
||
821 | 14 | $sql = $this->db->getQueryBuilder()->createIndex($name, $table, $columns, $unique); |
|
822 | |||
823 | 14 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
824 | } |
||
825 | |||
826 | /** |
||
827 | * Creates a SQL command for dropping an index. |
||
828 | * @param string $name the name of the index to be dropped. The name will be properly quoted by the method. |
||
829 | * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method. |
||
830 | * @return $this the command object itself |
||
831 | */ |
||
832 | 5 | public function dropIndex($name, $table) |
|
833 | { |
||
834 | 5 | $sql = $this->db->getQueryBuilder()->dropIndex($name, $table); |
|
835 | |||
836 | 5 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
837 | } |
||
838 | |||
839 | /** |
||
840 | * Creates a SQL command for adding an unique constraint to an existing table. |
||
841 | * @param string $name the name of the unique constraint. |
||
842 | * The name will be properly quoted by the method. |
||
843 | * @param string $table the table that the unique constraint will be added to. |
||
844 | * The name will be properly quoted by the method. |
||
845 | * @param string|array $columns the name of the column to that the constraint will be added on. |
||
846 | * If there are multiple columns, separate them with commas. |
||
847 | * The name will be properly quoted by the method. |
||
848 | * @return $this the command object itself. |
||
849 | * @since 2.0.13 |
||
850 | */ |
||
851 | 4 | public function addUnique($name, $table, $columns) |
|
852 | { |
||
853 | 4 | $sql = $this->db->getQueryBuilder()->addUnique($name, $table, $columns); |
|
854 | |||
855 | 4 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
856 | } |
||
857 | |||
858 | /** |
||
859 | * Creates a SQL command for dropping an unique constraint. |
||
860 | * @param string $name the name of the unique constraint to be dropped. |
||
861 | * The name will be properly quoted by the method. |
||
862 | * @param string $table the table whose unique constraint is to be dropped. |
||
863 | * The name will be properly quoted by the method. |
||
864 | * @return $this the command object itself. |
||
865 | * @since 2.0.13 |
||
866 | */ |
||
867 | 4 | public function dropUnique($name, $table) |
|
868 | { |
||
869 | 4 | $sql = $this->db->getQueryBuilder()->dropUnique($name, $table); |
|
870 | |||
871 | 4 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
872 | } |
||
873 | |||
874 | /** |
||
875 | * Creates a SQL command for adding a check constraint to an existing table. |
||
876 | * @param string $name the name of the check constraint. |
||
877 | * The name will be properly quoted by the method. |
||
878 | * @param string $table the table that the check constraint will be added to. |
||
879 | * The name will be properly quoted by the method. |
||
880 | * @param string $expression the SQL of the `CHECK` constraint. |
||
881 | * @return $this the command object itself. |
||
882 | * @since 2.0.13 |
||
883 | */ |
||
884 | 3 | public function addCheck($name, $table, $expression) |
|
885 | { |
||
886 | 3 | $sql = $this->db->getQueryBuilder()->addCheck($name, $table, $expression); |
|
887 | |||
888 | 3 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
889 | } |
||
890 | |||
891 | /** |
||
892 | * Creates a SQL command for dropping a check constraint. |
||
893 | * @param string $name the name of the check constraint to be dropped. |
||
894 | * The name will be properly quoted by the method. |
||
895 | * @param string $table the table whose check constraint is to be dropped. |
||
896 | * The name will be properly quoted by the method. |
||
897 | * @return $this the command object itself. |
||
898 | * @since 2.0.13 |
||
899 | */ |
||
900 | 3 | public function dropCheck($name, $table) |
|
901 | { |
||
902 | 3 | $sql = $this->db->getQueryBuilder()->dropCheck($name, $table); |
|
903 | |||
904 | 3 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
905 | } |
||
906 | |||
907 | /** |
||
908 | * Creates a SQL command for adding a default value constraint to an existing table. |
||
909 | * @param string $name the name of the default value constraint. |
||
910 | * The name will be properly quoted by the method. |
||
911 | * @param string $table the table that the default value constraint will be added to. |
||
912 | * The name will be properly quoted by the method. |
||
913 | * @param string $column the name of the column to that the constraint will be added on. |
||
914 | * The name will be properly quoted by the method. |
||
915 | * @param mixed $value default value. |
||
916 | * @return $this the command object itself. |
||
917 | * @since 2.0.13 |
||
918 | */ |
||
919 | 2 | public function addDefaultValue($name, $table, $column, $value) |
|
920 | { |
||
921 | 2 | $sql = $this->db->getQueryBuilder()->addDefaultValue($name, $table, $column, $value); |
|
922 | |||
923 | 2 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
924 | } |
||
925 | |||
926 | /** |
||
927 | * Creates a SQL command for dropping a default value constraint. |
||
928 | * @param string $name the name of the default value constraint to be dropped. |
||
929 | * The name will be properly quoted by the method. |
||
930 | * @param string $table the table whose default value constraint is to be dropped. |
||
931 | * The name will be properly quoted by the method. |
||
932 | * @return $this the command object itself. |
||
933 | * @since 2.0.13 |
||
934 | */ |
||
935 | 2 | public function dropDefaultValue($name, $table) |
|
936 | { |
||
937 | 2 | $sql = $this->db->getQueryBuilder()->dropDefaultValue($name, $table); |
|
938 | |||
939 | 2 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
940 | } |
||
941 | |||
942 | /** |
||
943 | * Creates a SQL command for resetting the sequence value of a table's primary key. |
||
944 | * The sequence will be reset such that the primary key of the next new row inserted |
||
945 | * will have the specified value or the maximum existing value +1. |
||
946 | * @param string $table the name of the table whose primary key sequence will be reset |
||
947 | * @param mixed $value the value for the primary key of the next new row inserted. If this is not set, |
||
948 | * the next new row's primary key will have the maximum existing value +1. |
||
949 | * @return $this the command object itself |
||
950 | * @throws NotSupportedException if this is not supported by the underlying DBMS |
||
951 | */ |
||
952 | 35 | public function resetSequence($table, $value = null) |
|
953 | { |
||
954 | 35 | $sql = $this->db->getQueryBuilder()->resetSequence($table, $value); |
|
955 | |||
956 | 35 | return $this->setSql($sql); |
|
957 | } |
||
958 | |||
959 | /** |
||
960 | * Executes a db command resetting the sequence value of a table's primary key. |
||
961 | * Reason for execute is that some databases (Oracle) need several queries to do so. |
||
962 | * The sequence is 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 is 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 | * @throws NotSupportedException if this is not supported by the underlying DBMS |
||
968 | * @since 2.0.16 |
||
969 | */ |
||
970 | 35 | public function executeResetSequence($table, $value = null) |
|
971 | { |
||
972 | 35 | return $this->db->getQueryBuilder()->executeResetSequence($table, $value); |
|
973 | } |
||
974 | |||
975 | /** |
||
976 | * Builds a SQL command for enabling or disabling integrity check. |
||
977 | * @param bool $check whether to turn on or off the integrity check. |
||
978 | * @param string $schema the schema name of the tables. Defaults to empty string, meaning the current |
||
979 | * or default schema. |
||
980 | * @param string $table the table name. |
||
981 | * @return $this the command object itself |
||
982 | * @throws NotSupportedException if this is not supported by the underlying DBMS |
||
983 | */ |
||
984 | 4 | public function checkIntegrity($check = true, $schema = '', $table = '') |
|
985 | { |
||
986 | 4 | $sql = $this->db->getQueryBuilder()->checkIntegrity($check, $schema, $table); |
|
987 | |||
988 | 4 | return $this->setSql($sql); |
|
989 | } |
||
990 | |||
991 | /** |
||
992 | * Builds a SQL command for adding comment to column. |
||
993 | * |
||
994 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the method. |
||
995 | * @param string $column the name of the column to be commented. The column name will be properly quoted by the method. |
||
996 | * @param string $comment the text of the comment to be added. The comment will be properly quoted by the method. |
||
997 | * @return $this the command object itself |
||
998 | * @since 2.0.8 |
||
999 | */ |
||
1000 | 2 | public function addCommentOnColumn($table, $column, $comment) |
|
1001 | { |
||
1002 | 2 | $sql = $this->db->getQueryBuilder()->addCommentOnColumn($table, $column, $comment); |
|
1003 | |||
1004 | 2 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
1005 | } |
||
1006 | |||
1007 | /** |
||
1008 | * Builds a SQL command for adding comment to table. |
||
1009 | * |
||
1010 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the method. |
||
1011 | * @param string $comment the text of the comment to be added. The comment will be properly quoted by the method. |
||
1012 | * @return $this the command object itself |
||
1013 | * @since 2.0.8 |
||
1014 | */ |
||
1015 | public function addCommentOnTable($table, $comment) |
||
1016 | { |
||
1017 | $sql = $this->db->getQueryBuilder()->addCommentOnTable($table, $comment); |
||
1018 | |||
1019 | return $this->setSql($sql); |
||
1020 | } |
||
1021 | |||
1022 | /** |
||
1023 | * Builds a SQL command for dropping comment from column. |
||
1024 | * |
||
1025 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the method. |
||
1026 | * @param string $column the name of the column to be commented. The column name will be properly quoted by the method. |
||
1027 | * @return $this the command object itself |
||
1028 | * @since 2.0.8 |
||
1029 | */ |
||
1030 | 2 | public function dropCommentFromColumn($table, $column) |
|
1031 | { |
||
1032 | 2 | $sql = $this->db->getQueryBuilder()->dropCommentFromColumn($table, $column); |
|
1033 | |||
1034 | 2 | return $this->setSql($sql)->requireTableSchemaRefresh($table); |
|
1035 | } |
||
1036 | |||
1037 | /** |
||
1038 | * Builds a SQL command for dropping comment from table. |
||
1039 | * |
||
1040 | * @param string $table the table whose column is to be commented. The table name will be properly quoted by the method. |
||
1041 | * @return $this the command object itself |
||
1042 | * @since 2.0.8 |
||
1043 | */ |
||
1044 | public function dropCommentFromTable($table) |
||
1045 | { |
||
1046 | $sql = $this->db->getQueryBuilder()->dropCommentFromTable($table); |
||
1047 | |||
1048 | return $this->setSql($sql); |
||
1049 | } |
||
1050 | |||
1051 | /** |
||
1052 | * Creates a SQL View. |
||
1053 | * |
||
1054 | * @param string $viewName the name of the view to be created. |
||
1055 | * @param string|Query $subquery the select statement which defines the view. |
||
1056 | * This can be either a string or a [[Query]] object. |
||
1057 | * @return $this the command object itself. |
||
1058 | * @since 2.0.14 |
||
1059 | */ |
||
1060 | 5 | public function createView($viewName, $subquery) |
|
1061 | { |
||
1062 | 5 | $sql = $this->db->getQueryBuilder()->createView($viewName, $subquery); |
|
1063 | |||
1064 | 5 | return $this->setSql($sql)->requireTableSchemaRefresh($viewName); |
|
1065 | } |
||
1066 | |||
1067 | /** |
||
1068 | * Drops a SQL View. |
||
1069 | * |
||
1070 | * @param string $viewName the name of the view to be dropped. |
||
1071 | * @return $this the command object itself. |
||
1072 | * @since 2.0.14 |
||
1073 | */ |
||
1074 | 7 | public function dropView($viewName) |
|
1075 | { |
||
1076 | 7 | $sql = $this->db->getQueryBuilder()->dropView($viewName); |
|
1077 | |||
1078 | 7 | return $this->setSql($sql)->requireTableSchemaRefresh($viewName); |
|
1079 | } |
||
1080 | |||
1081 | /** |
||
1082 | * Executes the SQL statement. |
||
1083 | * This method should only be used for executing non-query SQL statement, such as `INSERT`, `DELETE`, `UPDATE` SQLs. |
||
1084 | * No result set will be returned. |
||
1085 | * @return int number of rows affected by the execution. |
||
1086 | * @throws Exception execution failed |
||
1087 | */ |
||
1088 | 968 | public function execute() |
|
1089 | { |
||
1090 | 968 | $sql = $this->getSql(); |
|
1091 | 968 | list($profile, $rawSql) = $this->logQuery(__METHOD__); |
|
1092 | |||
1093 | 968 | if ($sql == '') { |
|
1094 | 10 | return 0; |
|
1095 | } |
||
1096 | |||
1097 | 963 | $this->prepare(false); |
|
1098 | |||
1099 | try { |
||
1100 | 963 | $profile and Yii::beginProfile($rawSql, __METHOD__); |
|
1101 | |||
1102 | 963 | $this->internalExecute($rawSql); |
|
1103 | 956 | $n = $this->pdoStatement->rowCount(); |
|
1104 | |||
1105 | 956 | $profile and Yii::endProfile($rawSql, __METHOD__); |
|
1106 | |||
1107 | 956 | $this->refreshTableSchema(); |
|
1108 | |||
1109 | 956 | return $n; |
|
1110 | 34 | } catch (Exception $e) { |
|
1111 | 34 | $profile and Yii::endProfile($rawSql, __METHOD__); |
|
1112 | 34 | throw $e; |
|
1113 | } |
||
1114 | } |
||
1115 | |||
1116 | /** |
||
1117 | * Logs the current database query if query logging is enabled and returns |
||
1118 | * the profiling token if profiling is enabled. |
||
1119 | * @param string $category the log category. |
||
1120 | * @return array array of two elements, the first is boolean of whether profiling is enabled or not. |
||
1121 | * The second is the rawSql if it has been created. |
||
1122 | */ |
||
1123 | 2376 | protected function logQuery($category) |
|
1124 | { |
||
1125 | 2376 | if ($this->db->enableLogging) { |
|
1126 | 2376 | $rawSql = $this->getRawSql(); |
|
1127 | 2376 | Yii::info($rawSql, $category); |
|
1128 | } |
||
1129 | 2376 | if (!$this->db->enableProfiling) { |
|
1130 | 11 | return [false, isset($rawSql) ? $rawSql : null]; |
|
1131 | } |
||
1132 | |||
1133 | 2376 | return [true, isset($rawSql) ? $rawSql : $this->getRawSql()]; |
|
1134 | } |
||
1135 | |||
1136 | /** |
||
1137 | * Performs the actual DB query of a SQL statement. |
||
1138 | * @param string $method method of PDOStatement to be called |
||
1139 | * @param int $fetchMode the result fetch mode. Please refer to [PHP manual](https://secure.php.net/manual/en/function.PDOStatement-setFetchMode.php) |
||
1140 | * for valid fetch modes. If this parameter is null, the value set in [[fetchMode]] will be used. |
||
1141 | * @return mixed the method execution result |
||
1142 | * @throws Exception if the query causes any problem |
||
1143 | * @since 2.0.1 this method is protected (was private before). |
||
1144 | */ |
||
1145 | 2315 | protected function queryInternal($method, $fetchMode = null) |
|
1146 | { |
||
1147 | 2315 | list($profile, $rawSql) = $this->logQuery('yii\db\Command::query'); |
|
1148 | |||
1149 | 2315 | if ($method !== '') { |
|
1150 | 2310 | $info = $this->db->getQueryCacheInfo($this->queryCacheDuration, $this->queryCacheDependency); |
|
1151 | 2310 | if (is_array($info)) { |
|
1152 | /* @var $cache \yii\caching\CacheInterface */ |
||
1153 | 10 | $cache = $info[0]; |
|
1154 | 10 | $cacheKey = $this->getCacheKey($method, $fetchMode, ''); |
|
1155 | 10 | $result = $cache->get($cacheKey); |
|
1156 | 10 | if (is_array($result) && isset($result[0])) { |
|
1157 | 10 | Yii::debug('Query result served from cache', 'yii\db\Command::query'); |
|
1158 | 10 | return $result[0]; |
|
1159 | } |
||
1160 | } |
||
1161 | } |
||
1162 | |||
1163 | 2315 | $this->prepare(true); |
|
1164 | |||
1165 | try { |
||
1166 | 2314 | $profile and Yii::beginProfile($rawSql, 'yii\db\Command::query'); |
|
1167 | |||
1168 | 2314 | $this->internalExecute($rawSql); |
|
1169 | |||
1170 | 2309 | if ($method === '') { |
|
1171 | 25 | $result = new DataReader($this); |
|
1172 | } else { |
||
1173 | 2304 | if ($fetchMode === null) { |
|
1174 | 2136 | $fetchMode = $this->fetchMode; |
|
1175 | } |
||
1176 | 2304 | $result = call_user_func_array([$this->pdoStatement, $method], (array) $fetchMode); |
|
1177 | 2304 | $this->pdoStatement->closeCursor(); |
|
1178 | } |
||
1179 | |||
1180 | 2309 | $profile and Yii::endProfile($rawSql, 'yii\db\Command::query'); |
|
1181 | 28 | } catch (Exception $e) { |
|
1182 | 28 | $profile and Yii::endProfile($rawSql, 'yii\db\Command::query'); |
|
1183 | 28 | throw $e; |
|
1184 | } |
||
1185 | |||
1186 | 2309 | if (isset($cache, $cacheKey, $info)) { |
|
1187 | 10 | $cache->set($cacheKey, [$result], $info[1], $info[2]); |
|
1188 | 10 | Yii::debug('Saved query result in cache', 'yii\db\Command::query'); |
|
1189 | } |
||
1190 | |||
1191 | 2309 | return $result; |
|
1192 | } |
||
1193 | |||
1194 | /** |
||
1195 | * Returns the cache key for the query. |
||
1196 | * |
||
1197 | * @param string $method method of PDOStatement to be called |
||
1198 | * @param int $fetchMode the result fetch mode. Please refer to [PHP manual](https://secure.php.net/manual/en/function.PDOStatement-setFetchMode.php) |
||
1199 | * for valid fetch modes. |
||
1200 | * @return array the cache key |
||
1201 | * @since 2.0.16 |
||
1202 | */ |
||
1203 | 10 | protected function getCacheKey($method, $fetchMode, $rawSql) |
|
1204 | { |
||
1205 | 10 | $params = $this->params; |
|
1206 | 10 | ksort($params); |
|
1207 | return [ |
||
1208 | 10 | __CLASS__, |
|
1209 | 10 | $method, |
|
1210 | 10 | $fetchMode, |
|
1211 | 10 | $this->db->dsn, |
|
1212 | 10 | $this->db->username, |
|
1213 | 10 | $this->getSql(), |
|
1214 | 10 | json_encode($params), |
|
1215 | ]; |
||
1216 | } |
||
1217 | |||
1218 | /** |
||
1219 | * Marks a specified table schema to be refreshed after command execution. |
||
1220 | * @param string $name name of the table, which schema should be refreshed. |
||
1221 | * @return $this this command instance |
||
1222 | * @since 2.0.6 |
||
1223 | */ |
||
1224 | 256 | protected function requireTableSchemaRefresh($name) |
|
1225 | { |
||
1226 | 256 | $this->_refreshTableName = $name; |
|
1227 | 256 | return $this; |
|
1228 | } |
||
1229 | |||
1230 | /** |
||
1231 | * Refreshes table schema, which was marked by [[requireTableSchemaRefresh()]]. |
||
1232 | * @since 2.0.6 |
||
1233 | */ |
||
1234 | 956 | protected function refreshTableSchema() |
|
1235 | { |
||
1236 | 956 | if ($this->_refreshTableName !== null) { |
|
1237 | 251 | $this->db->getSchema()->refreshTableSchema($this->_refreshTableName); |
|
1238 | } |
||
1239 | 956 | } |
|
1240 | |||
1241 | /** |
||
1242 | * Marks the command to be executed in transaction. |
||
1243 | * @param string|null $isolationLevel The isolation level to use for this transaction. |
||
1244 | * See [[Transaction::begin()]] for details. |
||
1245 | * @return $this this command instance. |
||
1246 | * @since 2.0.14 |
||
1247 | */ |
||
1248 | 5 | protected function requireTransaction($isolationLevel = null) |
|
1249 | { |
||
1250 | 5 | $this->_isolationLevel = $isolationLevel; |
|
1251 | 5 | return $this; |
|
1252 | } |
||
1253 | |||
1254 | /** |
||
1255 | * Sets a callable (e.g. anonymous function) that is called when [[Exception]] is thrown |
||
1256 | * when executing the command. The signature of the callable should be: |
||
1257 | * |
||
1258 | * ```php |
||
1259 | * function (\yii\db\Exception $e, $attempt) |
||
1260 | * { |
||
1261 | * // return true or false (whether to retry the command or rethrow $e) |
||
1262 | * } |
||
1263 | * ``` |
||
1264 | * |
||
1265 | * The callable will recieve a database exception thrown and a current attempt |
||
1266 | * (to execute the command) number starting from 1. |
||
1267 | * |
||
1268 | * @param callable $handler a PHP callback to handle database exceptions. |
||
1269 | * @return $this this command instance. |
||
1270 | * @since 2.0.14 |
||
1271 | */ |
||
1272 | 5 | protected function setRetryHandler(callable $handler) |
|
1273 | { |
||
1274 | 5 | $this->_retryHandler = $handler; |
|
1275 | 5 | return $this; |
|
1276 | } |
||
1277 | |||
1278 | /** |
||
1279 | * Executes a prepared statement. |
||
1280 | * |
||
1281 | * It's a wrapper around [[\PDOStatement::execute()]] to support transactions |
||
1282 | * and retry handlers. |
||
1283 | * |
||
1284 | * @param string|null $rawSql the rawSql if it has been created. |
||
1285 | * @throws Exception if execution failed. |
||
1286 | * @since 2.0.14 |
||
1287 | */ |
||
1288 | 2375 | protected function internalExecute($rawSql) |
|
1289 | { |
||
1290 | 2375 | $attempt = 0; |
|
1291 | 2375 | while (true) { |
|
1292 | try { |
||
1293 | if ( |
||
1294 | 2375 | ++$attempt === 1 |
|
1295 | 2375 | && $this->_isolationLevel !== false |
|
1296 | 2375 | && $this->db->getTransaction() === null |
|
1297 | ) { |
||
1298 | 5 | $this->db->transaction(function () use ($rawSql) { |
|
1299 | 5 | $this->internalExecute($rawSql); |
|
1300 | 5 | }, $this->_isolationLevel); |
|
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||
1301 | } else { |
||
1302 | 2375 | $this->pdoStatement->execute(); |
|
1303 | } |
||
1304 | 2368 | break; |
|
1305 | 56 | } catch (\Exception $e) { |
|
1306 | 56 | $rawSql = $rawSql ?: $this->getRawSql(); |
|
1307 | 56 | $e = $this->db->getSchema()->convertException($e, $rawSql); |
|
1308 | 56 | if ($this->_retryHandler === null || !call_user_func($this->_retryHandler, $e, $attempt)) { |
|
1309 | 56 | throw $e; |
|
1310 | } |
||
1311 | } |
||
1312 | } |
||
1313 | 2368 | } |
|
1314 | |||
1315 | /** |
||
1316 | * Resets command properties to their initial state. |
||
1317 | * |
||
1318 | * @since 2.0.13 |
||
1319 | */ |
||
1320 | 2450 | protected function reset() |
|
1321 | { |
||
1322 | 2450 | $this->_sql = null; |
|
1323 | 2450 | $this->pendingParams = []; |
|
1324 | 2450 | $this->params = []; |
|
1325 | 2450 | $this->_refreshTableName = null; |
|
1326 | 2450 | $this->_isolationLevel = false; |
|
1327 | 2450 | $this->_retryHandler = null; |
|
1328 | 2450 | } |
|
1329 | } |
||
1330 |