Command.php 27.1 KB
Newer Older
w  
Qiang Xue committed
1 2 3
<?php
/**
 * @link http://www.yiiframework.com/
Qiang Xue committed
4
 * @copyright Copyright (c) 2008 Yii Software LLC
w  
Qiang Xue committed
5 6 7
 * @license http://www.yiiframework.com/license/
 */

Qiang Xue committed
8
namespace yii\db;
w  
Qiang Xue committed
9

10
use Yii;
Qiang Xue committed
11
use yii\base\NotSupportedException;
12
use yii\caching\Cache;
Qiang Xue committed
13

w  
Qiang Xue committed
14
/**
w  
Qiang Xue committed
15
 * Command represents a SQL statement to be executed against a database.
w  
Qiang Xue committed
16
 *
Qiang Xue committed
17
 * A command object is usually created by calling [[Connection::createCommand()]].
Qiang Xue committed
18
 * The SQL statement it represents can be set via the [[sql]] property.
w  
Qiang Xue committed
19
 *
Qiang Xue committed
20
 * To execute a non-query SQL (such as INSERT, DELETE, UPDATE), call [[execute()]].
Qiang Xue committed
21
 * To execute a SQL statement that returns result data set (such as SELECT),
Qiang Xue committed
22
 * use [[queryAll()]], [[queryRow()]], [[queryColumn()]], [[queryScalar()]], or [[query()]].
Qiang Xue committed
23 24 25
 * For example,
 *
 * ~~~
Qiang Xue committed
26
 * $users = $connection->createCommand('SELECT * FROM tbl_user')->queryAll();
Qiang Xue committed
27
 * ~~~
w  
Qiang Xue committed
28
 *
Qiang Xue committed
29
 * Command supports SQL statement preparation and parameter binding.
Qiang Xue committed
30 31
 * Call [[bindValue()]] to bind a value to a SQL parameter;
 * Call [[bindParam()]] to bind a PHP variable to a SQL parameter.
w  
Qiang Xue committed
32
 * When binding a parameter, the SQL statement is automatically prepared.
Qiang Xue committed
33
 * You may also call [[prepare()]] explicitly to prepare a SQL statement.
w  
Qiang Xue committed
34
 *
Qiang Xue committed
35 36 37 38 39 40 41 42 43 44 45 46
 * Command also supports building SQL statements by providing methods such as [[insert()]],
 * [[update()]], etc. For example,
 *
 * ~~~
 * $connection->createCommand()->insert('tbl_user', array(
 *     'name' => 'Sam',
 *     'age' => 30,
 * ))->execute();
 * ~~~
 *
 * To build SELECT SQL statements, please use [[QueryBuilder]] instead.
 *
Qiang Xue committed
47 48
 * @property string $sql the SQL statement to be executed
 *
w  
Qiang Xue committed
49
 * @author Qiang Xue <qiang.xue@gmail.com>
w  
Qiang Xue committed
50
 * @since 2.0
w  
Qiang Xue committed
51
 */
w  
Qiang Xue committed
52
class Command extends \yii\base\Component
w  
Qiang Xue committed
53
{
Qiang Xue committed
54 55 56
	/**
	 * @var Connection the DB connection that this command is associated with
	 */
Qiang Xue committed
57
	public $db;
Qiang Xue committed
58
	/**
Qiang Xue committed
59
	 * @var \PDOStatement the PDOStatement object that this command is associated with
Qiang Xue committed
60
	 */
w  
Qiang Xue committed
61 62
	public $pdoStatement;
	/**
Qiang Xue committed
63
	 * @var mixed the default fetch mode for this command.
w  
Qiang Xue committed
64 65 66
	 * @see http://www.php.net/manual/en/function.PDOStatement-setFetchMode.php
	 */
	public $fetchMode = \PDO::FETCH_ASSOC;
Qiang Xue committed
67 68 69 70 71
	/**
	 * @var string the SQL statement that this command represents
	 */
	private $_sql;
	/**
Qiang Xue committed
72
	 * @var array the parameter log information (name=>value)
Qiang Xue committed
73
	 */
Qiang Xue committed
74
	private $_params = array();
Qiang Xue committed
75

w  
Qiang Xue committed
76
	/**
Qiang Xue committed
77
	 * Returns the SQL statement for this command.
w  
Qiang Xue committed
78 79
	 * @return string the SQL statement to be executed
	 */
Qiang Xue committed
80
	public function getSql()
w  
Qiang Xue committed
81
	{
w  
Qiang Xue committed
82
		return $this->_sql;
w  
Qiang Xue committed
83 84 85 86
	}

	/**
	 * Specifies the SQL statement to be executed.
87
	 * The previous SQL execution (if any) will be cancelled, and [[params]] will be cleared as well.
88
	 * @param string $sql the SQL statement to be set.
w  
Qiang Xue committed
89
	 * @return Command this command instance
w  
Qiang Xue committed
90
	 */
91
	public function setSql($sql)
w  
Qiang Xue committed
92
	{
93
		if ($sql !== $this->_sql) {
Qiang Xue committed
94
			$this->cancel();
95
			$this->_sql = $this->db->quoteSql($sql);
96 97
			$this->_params = array();
		}
w  
Qiang Xue committed
98 99 100
		return $this;
	}

Qiang Xue committed
101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133
	/**
	 * Returns the raw SQL by inserting parameter values into the corresponding placeholders in [[sql]].
	 * Note that the return value of this method should mainly be used for logging purpose.
	 * It is likely that this method returns an invalid SQL due to improper replacement of parameter placeholders.
	 * @return string the raw SQL
	 */
	public function getRawSql()
	{
		if ($this->_params === array()) {
			return $this->_sql;
		} else {
			$params = array();
			foreach ($this->_params as $name => $value) {
				if (is_string($value)) {
					$params[$name] = $this->db->quoteValue($value);
				} elseif ($value === null) {
					$params[$name] = 'NULL';
				} else {
					$params[$name] = $value;
				}
			}
			if (isset($params[1])) {
				$sql = '';
				foreach (explode('?', $this->_sql) as $i => $part) {
					$sql .= (isset($params[$i]) ? $params[$i] : '') . $part;
				}
				return $sql;
			} else {
				return strtr($this->_sql, $params);
			}
		}
	}

w  
Qiang Xue committed
134 135 136 137 138 139
	/**
	 * Prepares the SQL statement to be executed.
	 * For complex SQL statement that is to be executed multiple times,
	 * this may improve performance.
	 * For SQL statement with binding parameters, this method is invoked
	 * automatically.
Qiang Xue committed
140
	 * @throws Exception if there is any DB error
w  
Qiang Xue committed
141 142 143
	 */
	public function prepare()
	{
w  
Qiang Xue committed
144
		if ($this->pdoStatement == null) {
145
			$sql = $this->getSql();
w  
Qiang Xue committed
146
			try {
Qiang Xue committed
147
				$this->pdoStatement = $this->db->pdo->prepare($sql);
Qiang Xue committed
148
			} catch (\Exception $e) {
149
				Yii::error($e->getMessage() . "\nFailed to prepare SQL: $sql", __METHOD__);
Qiang Xue committed
150
				$errorInfo = $e instanceof \PDOException ? $e->errorInfo : null;
151
				throw new Exception($e->getMessage(), $errorInfo, (int)$e->getCode());
w  
Qiang Xue committed
152 153 154 155 156 157
			}
		}
	}

	/**
	 * Cancels the execution of the SQL statement.
Qiang Xue committed
158
	 * This method mainly sets [[pdoStatement]] to be null.
w  
Qiang Xue committed
159 160 161
	 */
	public function cancel()
	{
w  
Qiang Xue committed
162
		$this->pdoStatement = null;
w  
Qiang Xue committed
163 164 165 166
	}

	/**
	 * Binds a parameter to the SQL statement to be executed.
Qiang Xue committed
167
	 * @param string|integer $name parameter identifier. For a prepared statement
w  
Qiang Xue committed
168
	 * using named placeholders, this will be a parameter name of
Qiang Xue committed
169
	 * the form `:name`. For a prepared statement using question mark
w  
Qiang Xue committed
170 171 172 173
	 * placeholders, this will be the 1-indexed position of the parameter.
	 * @param mixed $value Name of the PHP variable to bind to the SQL statement parameter
	 * @param integer $dataType SQL data type of the parameter. If null, the type is determined by the PHP type of the value.
	 * @param integer $length length of the data type
Qiang Xue committed
174 175
	 * @param mixed $driverOptions the driver-specific options
	 * @return Command the current command being executed
w  
Qiang Xue committed
176 177 178 179 180
	 * @see http://www.php.net/manual/en/function.PDOStatement-bindParam.php
	 */
	public function bindParam($name, &$value, $dataType = null, $length = null, $driverOptions = null)
	{
		$this->prepare();
Qiang Xue committed
181
		if ($dataType === null) {
Qiang Xue committed
182
			$this->pdoStatement->bindParam($name, $value, $this->getPdoType($value));
Qiang Xue committed
183
		} elseif ($length === null) {
w  
Qiang Xue committed
184
			$this->pdoStatement->bindParam($name, $value, $dataType);
Qiang Xue committed
185
		} elseif ($driverOptions === null) {
w  
Qiang Xue committed
186
			$this->pdoStatement->bindParam($name, $value, $dataType, $length);
Qiang Xue committed
187
		} else {
w  
Qiang Xue committed
188
			$this->pdoStatement->bindParam($name, $value, $dataType, $length, $driverOptions);
Qiang Xue committed
189
		}
Qiang Xue committed
190
		$this->_params[$name] =& $value;
w  
Qiang Xue committed
191 192 193 194 195
		return $this;
	}

	/**
	 * Binds a value to a parameter.
Qiang Xue committed
196
	 * @param string|integer $name Parameter identifier. For a prepared statement
w  
Qiang Xue committed
197
	 * using named placeholders, this will be a parameter name of
Qiang Xue committed
198
	 * the form `:name`. For a prepared statement using question mark
w  
Qiang Xue committed
199 200 201
	 * placeholders, this will be the 1-indexed position of the parameter.
	 * @param mixed $value The value to bind to the parameter
	 * @param integer $dataType SQL data type of the parameter. If null, the type is determined by the PHP type of the value.
Qiang Xue committed
202
	 * @return Command the current command being executed
w  
Qiang Xue committed
203 204 205 206 207
	 * @see http://www.php.net/manual/en/function.PDOStatement-bindValue.php
	 */
	public function bindValue($name, $value, $dataType = null)
	{
		$this->prepare();
Qiang Xue committed
208
		if ($dataType === null) {
Qiang Xue committed
209
			$this->pdoStatement->bindValue($name, $value, $this->getPdoType($value));
Qiang Xue committed
210
		} else {
w  
Qiang Xue committed
211
			$this->pdoStatement->bindValue($name, $value, $dataType);
Qiang Xue committed
212
		}
Qiang Xue committed
213
		$this->_params[$name] = $value;
w  
Qiang Xue committed
214 215 216 217 218
		return $this;
	}

	/**
	 * Binds a list of values to the corresponding parameters.
Qiang Xue committed
219
	 * This is similar to [[bindValue()]] except that it binds multiple values at a time.
w  
Qiang Xue committed
220 221
	 * Note that the SQL data type of each value is determined by its PHP type.
	 * @param array $values the values to be bound. This must be given in terms of an associative
Qiang Xue committed
222
	 * array with array keys being the parameter names, and array values the corresponding parameter values,
223 224 225
	 * e.g. `array(':name'=>'John', ':age'=>25)`. By default, the PDO type of each value is determined
	 * by its PHP type. You may explicitly specify the PDO type by using an array: `array(value, type)`,
	 * e.g. `array(':name'=>'John', ':profile'=>array($profile, \PDO::PARAM_LOB))`.
w  
Qiang Xue committed
226
	 * @return Command the current command being executed
w  
Qiang Xue committed
227 228 229
	 */
	public function bindValues($values)
	{
Qiang Xue committed
230
		if (!empty($values)) {
Qiang Xue committed
231 232
			$this->prepare();
			foreach ($values as $name => $value) {
233 234 235 236
				if (is_array($value)) {
					$type = $value[1];
					$value = $value[0];
				} else {
Qiang Xue committed
237
					$type = $this->getPdoType($value);
238 239
				}
				$this->pdoStatement->bindValue($name, $value, $type);
Qiang Xue committed
240 241 242
				$this->_params[$name] = $value;
			}
		}
w  
Qiang Xue committed
243 244 245
		return $this;
	}

246
	/**
Qiang Xue committed
247 248 249
	 * Determines the PDO type for the give PHP data value.
	 * @param mixed $data the data whose PDO type is to be determined
	 * @return integer the PDO type
250 251
	 * @see http://www.php.net/manual/en/pdo.constants.php
	 */
Qiang Xue committed
252
	private function getPdoType($data)
253 254 255 256 257
	{
		static $typeMap = array(
			'boolean' => \PDO::PARAM_BOOL,
			'integer' => \PDO::PARAM_INT,
			'string' => \PDO::PARAM_STR,
Qiang Xue committed
258
			'resource' => \PDO::PARAM_LOB,
259 260
			'NULL' => \PDO::PARAM_NULL,
		);
Qiang Xue committed
261
		$type = gettype($data);
262 263 264
		return isset($typeMap[$type]) ? $typeMap[$type] : \PDO::PARAM_STR;
	}

w  
Qiang Xue committed
265 266
	/**
	 * Executes the SQL statement.
Qiang Xue committed
267
	 * This method should only be used for executing non-query SQL statement, such as `INSERT`, `DELETE`, `UPDATE` SQLs.
w  
Qiang Xue committed
268 269
	 * No result set will be returned.
	 * @return integer number of rows affected by the execution.
Qiang Xue committed
270
	 * @throws Exception execution failed
w  
Qiang Xue committed
271
	 */
272
	public function execute()
w  
Qiang Xue committed
273
	{
274
		$sql = $this->getSql();
Qiang Xue committed
275

Qiang Xue committed
276
		$rawSql = $this->getRawSql();
Qiang Xue committed
277

Qiang Xue committed
278
		Yii::trace("Executing SQL: $rawSql", __METHOD__);
Qiang Xue committed
279

Qiang Xue committed
280 281 282 283
		if ($sql == '') {
			return 0;
		}

Qiang Xue committed
284
		try {
285
			$token = "SQL: $sql";
Qiang Xue committed
286
			if ($this->db->enableProfiling) {
287
				Yii::beginProfile($token, __METHOD__);
Qiang Xue committed
288
			}
w  
Qiang Xue committed
289 290

			$this->prepare();
291
			$this->pdoStatement->execute();
w  
Qiang Xue committed
292
			$n = $this->pdoStatement->rowCount();
w  
Qiang Xue committed
293

Qiang Xue committed
294
			if ($this->db->enableProfiling) {
295
				Yii::endProfile($token, __METHOD__);
Qiang Xue committed
296
			}
w  
Qiang Xue committed
297
			return $n;
Qiang Xue committed
298
		} catch (\Exception $e) {
Qiang Xue committed
299
			if ($this->db->enableProfiling) {
300
				Yii::endProfile($token, __METHOD__);
Qiang Xue committed
301 302
			}
			$message = $e->getMessage();
Qiang Xue committed
303

Qiang Xue committed
304
			Yii::error("$message\nFailed to execute SQL: $rawSql", __METHOD__);
Qiang Xue committed
305

Qiang Xue committed
306
			$errorInfo = $e instanceof \PDOException ? $e->errorInfo : null;
307
			throw new Exception($message, $errorInfo, (int)$e->getCode());
w  
Qiang Xue committed
308 309 310 311 312
		}
	}

	/**
	 * Executes the SQL statement and returns query result.
Qiang Xue committed
313 314 315
	 * This method is for executing a SQL query that returns result set, such as `SELECT`.
	 * @return DataReader the reader object for fetching the query result
	 * @throws Exception execution failed
w  
Qiang Xue committed
316
	 */
317
	public function query()
w  
Qiang Xue committed
318
	{
319
		return $this->queryInternal('');
w  
Qiang Xue committed
320 321 322
	}

	/**
Qiang Xue committed
323 324 325 326
	 * Executes the SQL statement and returns ALL rows at once.
	 * @param mixed $fetchMode the result fetch mode. Please refer to [PHP manual](http://www.php.net/manual/en/function.PDOStatement-setFetchMode.php)
	 * for valid fetch modes. If this parameter is null, the value set in [[fetchMode]] will be used.
	 * @return array all rows of the query result. Each array element is an array representing a row of data.
w  
Qiang Xue committed
327
	 * An empty array is returned if the query results in nothing.
Qiang Xue committed
328
	 * @throws Exception execution failed
w  
Qiang Xue committed
329
	 */
330
	public function queryAll($fetchMode = null)
w  
Qiang Xue committed
331
	{
332
		return $this->queryInternal('fetchAll', $fetchMode);
w  
Qiang Xue committed
333 334 335 336
	}

	/**
	 * Executes the SQL statement and returns the first row of the result.
Qiang Xue committed
337 338 339 340 341
	 * This method is best used when only the first row of result is needed for a query.
	 * @param mixed $fetchMode the result fetch mode. Please refer to [PHP manual](http://www.php.net/manual/en/function.PDOStatement-setFetchMode.php)
	 * for valid fetch modes. If this parameter is null, the value set in [[fetchMode]] will be used.
	 * @return array|boolean the first row (in terms of an array) of the query result. False is returned if the query
	 * results in nothing.
Qiang Xue committed
342
	 * @throws Exception execution failed
w  
Qiang Xue committed
343
	 */
344
	public function queryRow($fetchMode = null)
w  
Qiang Xue committed
345
	{
346
		return $this->queryInternal('fetch', $fetchMode);
w  
Qiang Xue committed
347 348 349 350
	}

	/**
	 * Executes the SQL statement and returns the value of the first column in the first row of data.
Qiang Xue committed
351
	 * This method is best used when only a single value is needed for a query.
Qiang Xue committed
352
	 * @return string|boolean the value of the first column in the first row of the query result.
Qiang Xue committed
353
	 * False is returned if there is no value.
Qiang Xue committed
354
	 * @throws Exception execution failed
w  
Qiang Xue committed
355
	 */
356
	public function queryScalar()
w  
Qiang Xue committed
357
	{
358
		$result = $this->queryInternal('fetchColumn', 0);
w  
Qiang Xue committed
359
		if (is_resource($result) && get_resource_type($result) === 'stream') {
w  
Qiang Xue committed
360
			return stream_get_contents($result);
Qiang Xue committed
361
		} else {
w  
Qiang Xue committed
362
			return $result;
w  
Qiang Xue committed
363
		}
w  
Qiang Xue committed
364 365 366 367
	}

	/**
	 * Executes the SQL statement and returns the first column of the result.
Qiang Xue committed
368 369 370
	 * This method is best used when only the first column of result (i.e. the first element in each row)
	 * is needed for a query.
	 * @return array the first column of the query result. Empty array is returned if the query results in nothing.
Qiang Xue committed
371
	 * @throws Exception execution failed
w  
Qiang Xue committed
372
	 */
373
	public function queryColumn()
w  
Qiang Xue committed
374
	{
375
		return $this->queryInternal('fetchAll', \PDO::FETCH_COLUMN);
w  
Qiang Xue committed
376 377 378
	}

	/**
Qiang Xue committed
379
	 * Performs the actual DB query of a SQL statement.
w  
Qiang Xue committed
380
	 * @param string $method method of PDOStatement to be called
Qiang Xue committed
381 382
	 * @param mixed $fetchMode the result fetch mode. Please refer to [PHP manual](http://www.php.net/manual/en/function.PDOStatement-setFetchMode.php)
	 * for valid fetch modes. If this parameter is null, the value set in [[fetchMode]] will be used.
w  
Qiang Xue committed
383
	 * @return mixed the method execution result
Qiang Xue committed
384
	 * @throws Exception if the query causes any problem
w  
Qiang Xue committed
385
	 */
386
	private function queryInternal($method, $fetchMode = null)
w  
Qiang Xue committed
387
	{
Qiang Xue committed
388
		$db = $this->db;
389
		$sql = $this->getSql();
Qiang Xue committed
390
		$rawSql = $this->getRawSql();
Qiang Xue committed
391

Qiang Xue committed
392
		Yii::trace("Querying SQL: $rawSql", __METHOD__);
393

Qiang Xue committed
394
		/** @var $cache \yii\caching\Cache */
395
		if ($db->enableQueryCache && $method !== '') {
396
			$cache = is_string($db->queryCache) ? Yii::$app->getComponent($db->queryCache) : $db->queryCache;
Qiang Xue committed
397 398
		}

399
		if (isset($cache) && $cache instanceof Cache) {
Qiang Xue committed
400 401 402 403
			$cacheKey = $cache->buildKey(array(
				__CLASS__,
				$db->dsn,
				$db->username,
Qiang Xue committed
404
				$rawSql,
Qiang Xue committed
405
			));
Qiang Xue committed
406
			if (($result = $cache->get($cacheKey)) !== false) {
407
				Yii::trace('Query result served from cache', __METHOD__);
w  
Qiang Xue committed
408 409 410 411
				return $result;
			}
		}

Qiang Xue committed
412
		try {
413
			$token = "SQL: $sql";
Qiang Xue committed
414
			if ($db->enableProfiling) {
415
				Yii::beginProfile($token, __METHOD__);
Qiang Xue committed
416
			}
w  
Qiang Xue committed
417 418

			$this->prepare();
419
			$this->pdoStatement->execute();
w  
Qiang Xue committed
420

Qiang Xue committed
421
			if ($method === '') {
w  
Qiang Xue committed
422
				$result = new DataReader($this);
Qiang Xue committed
423
			} else {
Qiang Xue committed
424 425 426
				if ($fetchMode === null) {
					$fetchMode = $this->fetchMode;
				}
w  
Qiang Xue committed
427 428
				$result = call_user_func_array(array($this->pdoStatement, $method), (array)$fetchMode);
				$this->pdoStatement->closeCursor();
w  
Qiang Xue committed
429 430
			}

Qiang Xue committed
431
			if ($db->enableProfiling) {
432
				Yii::endProfile($token, __METHOD__);
Qiang Xue committed
433
			}
w  
Qiang Xue committed
434

435
			if (isset($cache, $cacheKey) && $cache instanceof Cache) {
436
				$cache->set($cacheKey, $result, $db->queryCacheDuration, $db->queryCacheDependency);
437
				Yii::trace('Saved query result in cache', __METHOD__);
Qiang Xue committed
438
			}
w  
Qiang Xue committed
439 440

			return $result;
Qiang Xue committed
441
		} catch (\Exception $e) {
Qiang Xue committed
442
			if ($db->enableProfiling) {
443
				Yii::endProfile($token, __METHOD__);
Qiang Xue committed
444 445
			}
			$message = $e->getMessage();
Qiang Xue committed
446
			Yii::error("$message\nCommand::$method() failed: $rawSql", __METHOD__);
Qiang Xue committed
447
			$errorInfo = $e instanceof \PDOException ? $e->errorInfo : null;
448
			throw new Exception($message, $errorInfo, (int)$e->getCode());
w  
Qiang Xue committed
449 450
		}
	}
Qiang Xue committed
451 452 453 454 455 456

	/**
	 * Creates an INSERT command.
	 * For example,
	 *
	 * ~~~
Qiang Xue committed
457 458 459
	 * $connection->createCommand()->insert('tbl_user', array(
	 *     'name' => 'Sam',
	 *     'age' => 30,
Qiang Xue committed
460 461 462 463 464 465 466 467 468 469 470
	 * ))->execute();
	 * ~~~
	 *
	 * The method will properly escape the column names, and bind the values to be inserted.
	 *
	 * Note that the created command is not executed until [[execute()]] is called.
	 *
	 * @param string $table the table that new rows will be inserted into.
	 * @param array $columns the column data (name=>value) to be inserted into the table.
	 * @return Command the command object itself
	 */
Qiang Xue committed
471
	public function insert($table, $columns)
Qiang Xue committed
472
	{
Qiang Xue committed
473
		$params = array();
Qiang Xue committed
474
		$sql = $this->db->getQueryBuilder()->insert($table, $columns, $params);
Qiang Xue committed
475 476 477
		return $this->setSql($sql)->bindValues($params);
	}

Qiang Xue committed
478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502
	/**
	 * Creates a batch INSERT command.
	 * For example,
	 *
	 * ~~~
	 * $connection->createCommand()->batchInsert('tbl_user', array('name', 'age'), array(
	 *     array('Tom', 30),
	 *     array('Jane', 20),
	 *     array('Linda', 25),
	 * ))->execute();
	 * ~~~
	 *
	 * Not that the values in each row must match the corresponding column names.
	 *
	 * @param string $table the table that new rows will be inserted into.
	 * @param array $columns the column names
	 * @param array $rows the rows to be batch inserted into the table
	 * @return Command the command object itself
	 */
	public function batchInsert($table, $columns, $rows)
	{
		$sql = $this->db->getQueryBuilder()->batchInsert($table, $columns, $rows);
		return $this->setSql($sql);
	}

Qiang Xue committed
503 504 505 506 507
	/**
	 * Creates an UPDATE command.
	 * For example,
	 *
	 * ~~~
Qiang Xue committed
508 509
	 * $connection->createCommand()->update('tbl_user', array(
	 *     'status' => 1,
Qiang Xue committed
510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525
	 * ), 'age > 30')->execute();
	 * ~~~
	 *
	 * The method will properly escape the column names and bind the values to be updated.
	 *
	 * Note that the created command is not executed until [[execute()]] is called.
	 *
	 * @param string $table the table to be updated.
	 * @param array $columns the column data (name=>value) to be updated.
	 * @param mixed $condition the condition that will be put in the WHERE part. Please
	 * refer to [[Query::where()]] on how to specify condition.
	 * @param array $params the parameters to be bound to the command
	 * @return Command the command object itself
	 */
	public function update($table, $columns, $condition = '', $params = array())
	{
Qiang Xue committed
526
		$sql = $this->db->getQueryBuilder()->update($table, $columns, $condition, $params);
Qiang Xue committed
527 528 529 530 531 532 533 534
		return $this->setSql($sql)->bindValues($params);
	}

	/**
	 * Creates a DELETE command.
	 * For example,
	 *
	 * ~~~
Qiang Xue committed
535
	 * $connection->createCommand()->delete('tbl_user', 'status = 0')->execute();
Qiang Xue committed
536 537 538 539 540 541 542 543 544 545 546 547 548 549
	 * ~~~
	 *
	 * The method will properly escape the table and column names.
	 *
	 * Note that the created command is not executed until [[execute()]] is called.
	 *
	 * @param string $table the table where the data will be deleted from.
	 * @param mixed $condition the condition that will be put in the WHERE part. Please
	 * refer to [[Query::where()]] on how to specify condition.
	 * @param array $params the parameters to be bound to the command
	 * @return Command the command object itself
	 */
	public function delete($table, $condition = '', $params = array())
	{
550
		$sql = $this->db->getQueryBuilder()->delete($table, $condition, $params);
Qiang Xue committed
551 552 553 554 555 556 557 558 559 560
		return $this->setSql($sql)->bindValues($params);
	}


	/**
	 * Creates a SQL command for creating a new DB table.
	 *
	 * The columns in the new table should be specified as name-definition pairs (e.g. 'name'=>'string'),
	 * where name stands for a column name which will be properly quoted by the method, and definition
	 * stands for the column type which can contain an abstract DB type.
Qiang Xue committed
561
	 * The method [[QueryBuilder::getColumnType()]] will be called
Qiang Xue committed
562 563 564 565 566 567 568 569 570 571 572 573 574
	 * to convert the abstract column types to physical ones. For example, `string` will be converted
	 * as `varchar(255)`, and `string not null` becomes `varchar(255) not null`.
	 *
	 * If a column is specified with definition only (e.g. 'PRIMARY KEY (name, type)'), it will be directly
	 * inserted into the generated SQL.
	 *
	 * @param string $table the name of the table to be created. The name will be properly quoted by the method.
	 * @param array $columns the columns (name=>definition) in the new table.
	 * @param string $options additional SQL fragment that will be appended to the generated SQL.
	 * @return Command the command object itself
	 */
	public function createTable($table, $columns, $options = null)
	{
Qiang Xue committed
575
		$sql = $this->db->getQueryBuilder()->createTable($table, $columns, $options);
Qiang Xue committed
576 577 578 579 580 581 582 583 584 585 586
		return $this->setSql($sql);
	}

	/**
	 * Creates a SQL command for renaming a DB table.
	 * @param string $table the table to be renamed. The name will be properly quoted by the method.
	 * @param string $newName the new table name. The name will be properly quoted by the method.
	 * @return Command the command object itself
	 */
	public function renameTable($table, $newName)
	{
Qiang Xue committed
587
		$sql = $this->db->getQueryBuilder()->renameTable($table, $newName);
Qiang Xue committed
588 589 590 591 592 593 594 595 596 597
		return $this->setSql($sql);
	}

	/**
	 * Creates a SQL command for dropping a DB table.
	 * @param string $table the table to be dropped. The name will be properly quoted by the method.
	 * @return Command the command object itself
	 */
	public function dropTable($table)
	{
Qiang Xue committed
598
		$sql = $this->db->getQueryBuilder()->dropTable($table);
Qiang Xue committed
599 600 601 602 603 604 605 606 607 608
		return $this->setSql($sql);
	}

	/**
	 * Creates a SQL command for truncating a DB table.
	 * @param string $table the table to be truncated. The name will be properly quoted by the method.
	 * @return Command the command object itself
	 */
	public function truncateTable($table)
	{
Qiang Xue committed
609
		$sql = $this->db->getQueryBuilder()->truncateTable($table);
Qiang Xue committed
610 611 612 613 614 615 616 617 618 619 620 621 622 623
		return $this->setSql($sql);
	}

	/**
	 * Creates a SQL command for adding a new DB column.
	 * @param string $table the table that the new column will be added to. The table name will be properly quoted by the method.
	 * @param string $column the name of the new column. The name will be properly quoted by the method.
	 * @param string $type the column type. [[\yii\db\QueryBuilder::getColumnType()]] will be called
	 * to convert the give column type to the physical one. For example, `string` will be converted
	 * as `varchar(255)`, and `string not null` becomes `varchar(255) not null`.
	 * @return Command the command object itself
	 */
	public function addColumn($table, $column, $type)
	{
Qiang Xue committed
624
		$sql = $this->db->getQueryBuilder()->addColumn($table, $column, $type);
Qiang Xue committed
625 626 627 628 629 630 631 632 633 634 635
		return $this->setSql($sql);
	}

	/**
	 * Creates a SQL command for dropping a DB column.
	 * @param string $table the table whose column is to be dropped. The name will be properly quoted by the method.
	 * @param string $column the name of the column to be dropped. The name will be properly quoted by the method.
	 * @return Command the command object itself
	 */
	public function dropColumn($table, $column)
	{
Qiang Xue committed
636
		$sql = $this->db->getQueryBuilder()->dropColumn($table, $column);
Qiang Xue committed
637 638 639 640 641 642 643 644 645 646 647 648
		return $this->setSql($sql);
	}

	/**
	 * Creates a SQL command for renaming a column.
	 * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method.
	 * @param string $oldName the old name of the column. The name will be properly quoted by the method.
	 * @param string $newName the new name of the column. The name will be properly quoted by the method.
	 * @return Command the command object itself
	 */
	public function renameColumn($table, $oldName, $newName)
	{
Qiang Xue committed
649
		$sql = $this->db->getQueryBuilder()->renameColumn($table, $oldName, $newName);
Qiang Xue committed
650 651 652 653 654 655 656 657 658 659 660 661 662 663
		return $this->setSql($sql);
	}

	/**
	 * Creates a SQL command for changing the definition of a column.
	 * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method.
	 * @param string $column the name of the column to be changed. The name will be properly quoted by the method.
	 * @param string $type the column type. [[\yii\db\QueryBuilder::getColumnType()]] will be called
	 * to convert the give column type to the physical one. For example, `string` will be converted
	 * as `varchar(255)`, and `string not null` becomes `varchar(255) not null`.
	 * @return Command the command object itself
	 */
	public function alterColumn($table, $column, $type)
	{
Qiang Xue committed
664
		$sql = $this->db->getQueryBuilder()->alterColumn($table, $column, $type);
Qiang Xue committed
665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681
		return $this->setSql($sql);
	}

	/**
	 * Creates a SQL command for adding a foreign key constraint to an existing table.
	 * The method will properly quote the table and column names.
	 * @param string $name the name of the foreign key constraint.
	 * @param string $table the table that the foreign key constraint will be added to.
	 * @param string $columns the name of the column to that the constraint will be added on. If there are multiple columns, separate them with commas.
	 * @param string $refTable the table that the foreign key references to.
	 * @param string $refColumns the name of the column that the foreign key references to. If there are multiple columns, separate them with commas.
	 * @param string $delete the ON DELETE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL
	 * @param string $update the ON UPDATE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL
	 * @return Command the command object itself
	 */
	public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null)
	{
Qiang Xue committed
682
		$sql = $this->db->getQueryBuilder()->addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete, $update);
Qiang Xue committed
683 684 685 686 687 688 689 690 691 692 693
		return $this->setSql($sql);
	}

	/**
	 * Creates a SQL command for dropping a foreign key constraint.
	 * @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted by the method.
	 * @param string $table the table whose foreign is to be dropped. The name will be properly quoted by the method.
	 * @return Command the command object itself
	 */
	public function dropForeignKey($name, $table)
	{
Qiang Xue committed
694
		$sql = $this->db->getQueryBuilder()->dropForeignKey($name, $table);
Qiang Xue committed
695 696 697 698 699 700 701 702 703 704 705 706 707 708
		return $this->setSql($sql);
	}

	/**
	 * Creates a SQL command for creating a new index.
	 * @param string $name the name of the index. The name will be properly quoted by the method.
	 * @param string $table the table that the new index will be created for. The table name will be properly quoted by the method.
	 * @param string $columns the column(s) that should be included in the index. If there are multiple columns, please separate them
	 * by commas. The column names will be properly quoted by the method.
	 * @param boolean $unique whether to add UNIQUE constraint on the created index.
	 * @return Command the command object itself
	 */
	public function createIndex($name, $table, $columns, $unique = false)
	{
Qiang Xue committed
709
		$sql = $this->db->getQueryBuilder()->createIndex($name, $table, $columns, $unique);
Qiang Xue committed
710 711 712 713 714 715 716 717 718 719 720
		return $this->setSql($sql);
	}

	/**
	 * Creates a SQL command for dropping an index.
	 * @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
	 * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
	 * @return Command the command object itself
	 */
	public function dropIndex($name, $table)
	{
Qiang Xue committed
721
		$sql = $this->db->getQueryBuilder()->dropIndex($name, $table);
Qiang Xue committed
722 723
		return $this->setSql($sql);
	}
Qiang Xue committed
724 725 726 727 728 729 730 731 732 733 734 735 736

	/**
	 * Creates a SQL command for resetting the sequence value of a table's primary key.
	 * The sequence will be reset such that the primary key of the next new row inserted
	 * will have the specified value or 1.
	 * @param string $table the name of the table whose primary key sequence will be reset
	 * @param mixed $value the value for the primary key of the next new row inserted. If this is not set,
	 * the next new row's primary key will have a value 1.
	 * @return Command the command object itself
	 * @throws NotSupportedException if this is not supported by the underlying DBMS
	 */
	public function resetSequence($table, $value = null)
	{
Qiang Xue committed
737
		$sql = $this->db->getQueryBuilder()->resetSequence($table, $value);
Qiang Xue committed
738 739 740 741 742 743 744 745 746 747 748 749 750
		return $this->setSql($sql);
	}

	/**
	 * Builds a SQL command for enabling or disabling integrity check.
	 * @param boolean $check whether to turn on or off the integrity check.
	 * @param string $schema the schema name of the tables. Defaults to empty string, meaning the current
	 * or default schema.
	 * @return Command the command object itself
	 * @throws NotSupportedException if this is not supported by the underlying DBMS
	 */
	public function checkIntegrity($check = true, $schema = '')
	{
Qiang Xue committed
751
		$sql = $this->db->getQueryBuilder()->checkIntegrity($check, $schema);
Qiang Xue committed
752 753
		return $this->setSql($sql);
	}
w  
Qiang Xue committed
754
}