QueryBuilder.php 34 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

Qiang Xue committed
10
use yii\base\NotSupportedException;
w  
Qiang Xue committed
11

w  
Qiang Xue committed
12
/**
Qiang Xue committed
13
 * QueryBuilder builds a SELECT SQL statement based on the specification given as a [[Query]] object.
w  
Qiang Xue committed
14
 *
Qiang Xue committed
15
 * QueryBuilder can also be used to build SQL statements such as INSERT, UPDATE, DELETE, CREATE TABLE,
Qiang Xue committed
16 17
 * from a [[Query]] object.
 *
w  
Qiang Xue committed
18 19 20
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @since 2.0
 */
Qiang Xue committed
21
class QueryBuilder extends \yii\base\Object
w  
Qiang Xue committed
22
{
23 24 25 26 27
	/**
	 * The prefix for automatically generated query binding parameters.
	 */
	const PARAM_PREFIX = ':qp';

Qiang Xue committed
28 29 30
	/**
	 * @var Connection the database connection.
	 */
Qiang Xue committed
31
	public $db;
Qiang Xue committed
32 33 34 35 36
	/**
	 * @var string the separator between different fragments of a SQL statement.
	 * Defaults to an empty space. This is mainly used by [[build()]] when generating a SQL statement.
	 */
	public $separator = " ";
Qiang Xue committed
37 38 39 40 41 42
	/**
	 * @var array the abstract column types mapped to physical column types.
	 * This is mainly used to support creating/modifying tables using DB-independent data type specifications.
	 * Child classes should override this property to declare supported type mappings.
	 */
	public $typeMap = array();
w  
Qiang Xue committed
43

Qiang Xue committed
44 45
	/**
	 * Constructor.
Qiang Xue committed
46
	 * @param Connection $connection the database connection.
Qiang Xue committed
47
	 * @param array $config name-value pairs that will be used to initialize the object properties
Qiang Xue committed
48
	 */
Qiang Xue committed
49
	public function __construct($connection, $config = array())
w  
Qiang Xue committed
50
	{
Qiang Xue committed
51
		$this->db = $connection;
Qiang Xue committed
52
		parent::__construct($config);
w  
Qiang Xue committed
53 54
	}

Qiang Xue committed
55
	/**
Qiang Xue committed
56 57
	 * Generates a SELECT SQL statement from a [[Query]] object.
	 * @param Query $query the [[Query]] object from which the SQL statement will be generated
58 59
	 * @return array the generated SQL statement (the first array element) and the corresponding
	 * parameters to be bound to the SQL statement (the second array element).
Qiang Xue committed
60
	 */
w  
Qiang Xue committed
61 62
	public function build($query)
	{
63
		$params = $query->params;
Qiang Xue committed
64
		$clauses = array(
Qiang Xue committed
65 66
			$this->buildSelect($query->select, $query->distinct, $query->selectOption),
			$this->buildFrom($query->from),
67 68
			$this->buildJoin($query->join, $params),
			$this->buildWhere($query->where, $params),
Qiang Xue committed
69
			$this->buildGroupBy($query->groupBy),
70 71
			$this->buildHaving($query->having, $params),
			$this->buildUnion($query->union, $params),
Qiang Xue committed
72
			$this->buildOrderBy($query->orderBy),
Qiang Xue committed
73
			$this->buildLimit($query->limit, $query->offset),
Qiang Xue committed
74
		);
75
		return array(implode($this->separator, array_filter($clauses)), $params);
w  
Qiang Xue committed
76 77 78
	}

	/**
Qiang Xue committed
79
	 * Creates an INSERT SQL statement.
Qiang Xue committed
80 81 82 83
	 * For example,
	 *
	 * ~~~
	 * $sql = $queryBuilder->insert('tbl_user', array(
Qiang Xue committed
84 85
	 *	 'name' => 'Sam',
	 *	 'age' => 30,
Qiang Xue committed
86
	 * ), $params);
Qiang Xue committed
87 88
	 * ~~~
	 *
Qiang Xue committed
89 90
	 * The method will properly escape the table and column names.
	 *
w  
Qiang Xue committed
91
	 * @param string $table the table that new rows will be inserted into.
resurtm committed
92
	 * @param array $columns the column data (name => value) to be inserted into the table.
Qiang Xue committed
93 94
	 * @param array $params the binding parameters that will be generated by this method.
	 * They should be bound to the DB command later.
95
	 * @return string the INSERT SQL
w  
Qiang Xue committed
96
	 */
Qiang Xue committed
97
	public function insert($table, $columns, &$params)
w  
Qiang Xue committed
98
	{
99 100 101 102 103
		if (($tableSchema = $this->db->getTableSchema($table)) !== null) {
			$columnSchemas = $tableSchema->columns;
		} else {
			$columnSchemas = array();
		}
w  
Qiang Xue committed
104 105 106
		$names = array();
		$placeholders = array();
		foreach ($columns as $name => $value) {
Qiang Xue committed
107
			$names[] = $this->db->quoteColumnName($name);
w  
Qiang Xue committed
108 109 110 111 112
			if ($value instanceof Expression) {
				$placeholders[] = $value->expression;
				foreach ($value->params as $n => $v) {
					$params[$n] = $v;
				}
Qiang Xue committed
113
			} else {
114 115
				$phName = self::PARAM_PREFIX . count($params);
				$placeholders[] = $phName;
Qiang Xue committed
116
				$params[$phName] = !is_array($value) && isset($columnSchemas[$name]) ? $columnSchemas[$name]->typecast($value) : $value;
w  
Qiang Xue committed
117 118 119
			}
		}

Qiang Xue committed
120
		return 'INSERT INTO ' . $this->db->quoteTableName($table)
w  
Qiang Xue committed
121 122 123 124
			. ' (' . implode(', ', $names) . ') VALUES ('
			. implode(', ', $placeholders) . ')';
	}

Qiang Xue committed
125 126 127 128 129 130 131 132 133 134 135 136
	/**
	 * Generates a batch INSERT SQL statement.
	 * For example,
	 *
	 * ~~~
	 * $connection->createCommand()->batchInsert('tbl_user', array('name', 'age'), array(
	 *     array('Tom', 30),
	 *     array('Jane', 20),
	 *     array('Linda', 25),
	 * ))->execute();
	 * ~~~
	 *
137
	 * Note that the values in each row must match the corresponding column names.
Qiang Xue committed
138 139 140 141 142 143 144
	 *
	 * @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 string the batch INSERT SQL statement
	 * @throws NotSupportedException if this is not supported by the underlying DBMS
	 */
145
	public function batchInsert($table, $columns, $rows)
Qiang Xue committed
146 147 148 149 150
	{
		throw new NotSupportedException($this->db->getDriverName() . ' does not support batch insert.');

	}

w  
Qiang Xue committed
151
	/**
Qiang Xue committed
152
	 * Creates an UPDATE SQL statement.
Qiang Xue committed
153 154 155 156 157
	 * For example,
	 *
	 * ~~~
	 * $params = array();
	 * $sql = $queryBuilder->update('tbl_user', array(
Qiang Xue committed
158
	 *	 'status' => 1,
Qiang Xue committed
159 160 161
	 * ), 'age > 30', $params);
	 * ~~~
	 *
Qiang Xue committed
162 163
	 * The method will properly escape the table and column names.
	 *
w  
Qiang Xue committed
164
	 * @param string $table the table to be updated.
resurtm committed
165
	 * @param array $columns the column data (name => value) to be updated.
166
	 * @param array|string $condition the condition that will be put in the WHERE part. Please
Qiang Xue committed
167
	 * refer to [[Query::where()]] on how to specify condition.
Qiang Xue committed
168 169
	 * @param array $params the binding parameters that will be modified by this method
	 * so that they can be bound to the DB command later.
170
	 * @return string the UPDATE SQL
w  
Qiang Xue committed
171
	 */
172
	public function update($table, $columns, $condition, &$params)
w  
Qiang Xue committed
173
	{
174 175 176 177 178 179
		if (($tableSchema = $this->db->getTableSchema($table)) !== null) {
			$columnSchemas = $tableSchema->columns;
		} else {
			$columnSchemas = array();
		}

w  
Qiang Xue committed
180 181 182
		$lines = array();
		foreach ($columns as $name => $value) {
			if ($value instanceof Expression) {
Qiang Xue committed
183
				$lines[] = $this->db->quoteColumnName($name) . '=' . $value->expression;
w  
Qiang Xue committed
184 185 186
				foreach ($value->params as $n => $v) {
					$params[$n] = $v;
				}
Qiang Xue committed
187
			} else {
188 189
				$phName = self::PARAM_PREFIX . count($params);
				$lines[] = $this->db->quoteColumnName($name) . '=' . $phName;
Qiang Xue committed
190
				$params[$phName] = !is_array($value) && isset($columnSchemas[$name]) ? $columnSchemas[$name]->typecast($value) : $value;
w  
Qiang Xue committed
191 192
			}
		}
w  
Qiang Xue committed
193

194 195 196
		$sql = 'UPDATE ' . $this->db->quoteTableName($table) . ' SET ' . implode(', ', $lines);
		$where = $this->buildWhere($condition, $params);
		return $where === '' ? $sql : $sql . ' ' . $where;
w  
Qiang Xue committed
197 198 199
	}

	/**
Qiang Xue committed
200
	 * Creates a DELETE SQL statement.
Qiang Xue committed
201 202 203 204 205 206
	 * For example,
	 *
	 * ~~~
	 * $sql = $queryBuilder->delete('tbl_user', 'status = 0');
	 * ~~~
	 *
Qiang Xue committed
207 208
	 * The method will properly escape the table and column names.
	 *
w  
Qiang Xue committed
209
	 * @param string $table the table where the data will be deleted from.
210
	 * @param array|string $condition the condition that will be put in the WHERE part. Please
Qiang Xue committed
211
	 * refer to [[Query::where()]] on how to specify condition.
212 213
	 * @param array $params the binding parameters that will be modified by this method
	 * so that they can be bound to the DB command later.
214
	 * @return string the DELETE SQL
w  
Qiang Xue committed
215
	 */
216
	public function delete($table, $condition, &$params)
w  
Qiang Xue committed
217
	{
Qiang Xue committed
218
		$sql = 'DELETE FROM ' . $this->db->quoteTableName($table);
219 220
		$where = $this->buildWhere($condition, $params);
		return $where === '' ? $sql : $sql . ' ' . $where;
w  
Qiang Xue committed
221 222
	}

w  
Qiang Xue committed
223 224 225
	/**
	 * Builds a SQL statement for creating a new DB table.
	 *
resurtm committed
226
	 * The columns in the new  table should be specified as name-definition pairs (e.g. 'name' => 'string'),
w  
Qiang Xue committed
227 228
	 * 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
229
	 * The [[getColumnType()]] method will be invoked to convert any abstract type into a physical one.
w  
Qiang Xue committed
230 231 232 233
	 *
	 * If a column is specified with definition only (e.g. 'PRIMARY KEY (name, type)'), it will be directly
	 * inserted into the generated SQL.
	 *
Qiang Xue committed
234 235 236 237
	 * For example,
	 *
	 * ~~~
	 * $sql = $queryBuilder->createTable('tbl_user', array(
Qiang Xue committed
238 239 240
	 *	 'id' => 'pk',
	 *	 'name' => 'string',
	 *	 'age' => 'integer',
Qiang Xue committed
241 242 243
	 * ));
	 * ~~~
	 *
w  
Qiang Xue committed
244
	 * @param string $table the name of the table to be created. The name will be properly quoted by the method.
resurtm committed
245
	 * @param array $columns the columns (name => definition) in the new table.
w  
Qiang Xue committed
246 247 248 249 250 251
	 * @param string $options additional SQL fragment that will be appended to the generated SQL.
	 * @return string the SQL statement for creating a new DB table.
	 */
	public function createTable($table, $columns, $options = null)
	{
		$cols = array();
w  
Qiang Xue committed
252 253
		foreach ($columns as $name => $type) {
			if (is_string($name)) {
Qiang Xue committed
254
				$cols[] = "\t" . $this->db->quoteColumnName($name) . ' ' . $this->getColumnType($type);
Qiang Xue committed
255
			} else {
w  
Qiang Xue committed
256
				$cols[] = "\t" . $type;
Qiang Xue committed
257
			}
w  
Qiang Xue committed
258
		}
Qiang Xue committed
259
		$sql = "CREATE TABLE " . $this->db->quoteTableName($table) . " (\n" . implode(",\n", $cols) . "\n)";
w  
Qiang Xue committed
260 261 262 263 264
		return $options === null ? $sql : $sql . ' ' . $options;
	}

	/**
	 * Builds a SQL statement for renaming a DB table.
Qiang Xue committed
265
	 * @param string $oldName the table to be renamed. The name will be properly quoted by the method.
w  
Qiang Xue committed
266 267 268
	 * @param string $newName the new table name. The name will be properly quoted by the method.
	 * @return string the SQL statement for renaming a DB table.
	 */
Qiang Xue committed
269
	public function renameTable($oldName, $newName)
w  
Qiang Xue committed
270
	{
Qiang Xue committed
271
		return 'RENAME TABLE ' . $this->db->quoteTableName($oldName) . ' TO ' . $this->db->quoteTableName($newName);
w  
Qiang Xue committed
272 273 274 275 276 277 278 279 280
	}

	/**
	 * Builds a SQL statement for dropping a DB table.
	 * @param string $table the table to be dropped. The name will be properly quoted by the method.
	 * @return string the SQL statement for dropping a DB table.
	 */
	public function dropTable($table)
	{
Qiang Xue committed
281
		return "DROP TABLE " . $this->db->quoteTableName($table);
w  
Qiang Xue committed
282
	}
283 284 285 286 287 288 289 290
	
	/**
	 * Builds a SQL statement for adding a primary key constraint to an existing table.
	 * @param string $name the name of the primary key constraint.
	 * @param string $table the table that the primary key constraint will be added to.
	 * @param string|array $columns comma separated string or array of columns that the primary key will consist of.
	 * @return string the SQL statement for adding a primary key constraint to an existing table.
	 */
291
	public function addPrimaryKey($name, $table, $columns)
292
	{
293
		if (is_string($columns)) {
Alexander Makarov committed
294
			$columns = preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY);
295
		}
Alexander Makarov committed
296 297 298

		foreach ($columns as $i => $col) {
			$columns[$i] = $this->db->quoteColumnName($col);
299 300
		}
		
301 302
		return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ADD CONSTRAINT '
			. $this->db->quoteColumnName($name) . '  PRIMARY KEY ('
303
			. implode(', ', $columns). ' )';
Alexander Makarov committed
304
	}
305 306
	
	/**
307 308 309 310
	 * Builds a SQL statement for removing a primary key constraint to an existing table.
	 * @param string $name the name of the primary key constraint to be removed.
	 * @param string $table the table that the primary key constraint will be removed from.
	 * @return string the SQL statement for removing a primary key constraint from an existing table.	 *
311
	 */
312
	public function dropPrimaryKey($name, $table)
313
	{
314 315
		return 'ALTER TABLE ' . $this->db->quoteTableName($table)
			. ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name);
Alexander Makarov committed
316
	}
w  
Qiang Xue committed
317 318 319 320 321 322 323

	/**
	 * Builds a SQL statement for truncating a DB table.
	 * @param string $table the table to be truncated. The name will be properly quoted by the method.
	 * @return string the SQL statement for truncating a DB table.
	 */
	public function truncateTable($table)
w  
Qiang Xue committed
324
	{
Qiang Xue committed
325
		return "TRUNCATE TABLE " . $this->db->quoteTableName($table);
w  
Qiang Xue committed
326 327 328 329 330 331
	}

	/**
	 * Builds a SQL statement 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.
Qiang Xue committed
332
	 * @param string $type the column type. The [[getColumnType()]] method will be invoked to convert abstract column type (if any)
w  
Qiang Xue committed
333 334 335 336 337 338
	 * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL.
	 * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
	 * @return string the SQL statement for adding a new column.
	 */
	public function addColumn($table, $column, $type)
	{
Qiang Xue committed
339 340
		return 'ALTER TABLE ' . $this->db->quoteTableName($table)
			. ' ADD ' . $this->db->quoteColumnName($column) . ' '
w  
Qiang Xue committed
341 342
			. $this->getColumnType($type);
	}
w  
Qiang Xue committed
343

w  
Qiang Xue committed
344 345 346 347 348 349 350 351
	/**
	 * Builds a SQL statement 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 string the SQL statement for dropping a DB column.
	 */
	public function dropColumn($table, $column)
	{
Qiang Xue committed
352 353
		return "ALTER TABLE " . $this->db->quoteTableName($table)
			. " DROP COLUMN " . $this->db->quoteColumnName($column);
w  
Qiang Xue committed
354 355 356 357 358
	}

	/**
	 * Builds a SQL statement for renaming a column.
	 * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method.
Qiang Xue committed
359
	 * @param string $oldName the old name of the column. The name will be properly quoted by the method.
w  
Qiang Xue committed
360 361 362
	 * @param string $newName the new name of the column. The name will be properly quoted by the method.
	 * @return string the SQL statement for renaming a DB column.
	 */
Qiang Xue committed
363
	public function renameColumn($table, $oldName, $newName)
w  
Qiang Xue committed
364
	{
Qiang Xue committed
365 366 367
		return "ALTER TABLE " . $this->db->quoteTableName($table)
			. " RENAME COLUMN " . $this->db->quoteColumnName($oldName)
			. " TO " . $this->db->quoteColumnName($newName);
w  
Qiang Xue committed
368 369 370 371 372 373
	}

	/**
	 * Builds a SQL statement 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.
Qiang Xue committed
374 375 376 377
	 * @param string $type the new column type. The [[getColumnType()]] method will be invoked to convert abstract
	 * column type (if any) into the physical one. Anything that is not recognized as abstract type will be kept
	 * in the generated SQL. For example, 'string' will be turned into 'varchar(255)', while 'string not null'
	 * will become 'varchar(255) not null'.
w  
Qiang Xue committed
378 379 380 381
	 * @return string the SQL statement for changing the definition of a column.
	 */
	public function alterColumn($table, $column, $type)
	{
Qiang Xue committed
382 383 384
		return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' CHANGE '
			. $this->db->quoteColumnName($column) . ' '
			. $this->db->quoteColumnName($column) . ' '
w  
Qiang Xue committed
385 386 387 388 389 390 391 392
			. $this->getColumnType($type);
	}

	/**
	 * Builds a SQL statement 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.
Qiang Xue committed
393 394
	 * @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 or use an array to represent them.
w  
Qiang Xue committed
395
	 * @param string $refTable the table that the foreign key references to.
Qiang Xue committed
396 397
	 * @param string|array $refColumns the name of the column that the foreign key references to.
	 * If there are multiple columns, separate them with commas or use an array to represent them.
w  
Qiang Xue committed
398 399 400 401 402 403
	 * @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 string the SQL statement for adding a foreign key constraint to an existing table.
	 */
	public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null)
	{
Qiang Xue committed
404 405
		$sql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
			. ' ADD CONSTRAINT ' . $this->db->quoteColumnName($name)
Qiang Xue committed
406
			. ' FOREIGN KEY (' . $this->buildColumns($columns) . ')'
Qiang Xue committed
407
			. ' REFERENCES ' . $this->db->quoteTableName($refTable)
Qiang Xue committed
408
			. ' (' . $this->buildColumns($refColumns) . ')';
Qiang Xue committed
409
		if ($delete !== null) {
w  
Qiang Xue committed
410
			$sql .= ' ON DELETE ' . $delete;
Qiang Xue committed
411 412
		}
		if ($update !== null) {
w  
Qiang Xue committed
413
			$sql .= ' ON UPDATE ' . $update;
Qiang Xue committed
414
		}
w  
Qiang Xue committed
415 416 417 418 419 420 421 422 423 424 425
		return $sql;
	}

	/**
	 * Builds a SQL statement 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 string the SQL statement for dropping a foreign key constraint.
	 */
	public function dropForeignKey($name, $table)
	{
Qiang Xue committed
426 427
		return 'ALTER TABLE ' . $this->db->quoteTableName($table)
			. ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name);
w  
Qiang Xue committed
428 429 430 431 432 433
	}

	/**
	 * Builds a SQL statement 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.
Qiang Xue committed
434 435 436
	 * @param string|array $columns the column(s) that should be included in the index. If there are multiple columns,
	 * separate them with commas or use an array to represent them. Each column name will be properly quoted
	 * by the method, unless a parenthesis is found in the name.
w  
Qiang Xue committed
437 438 439
	 * @param boolean $unique whether to add UNIQUE constraint on the created index.
	 * @return string the SQL statement for creating a new index.
	 */
Qiang Xue committed
440
	public function createIndex($name, $table, $columns, $unique = false)
w  
Qiang Xue committed
441 442
	{
		return ($unique ? 'CREATE UNIQUE INDEX ' : 'CREATE INDEX ')
Qiang Xue committed
443 444
			. $this->db->quoteTableName($name) . ' ON '
			. $this->db->quoteTableName($table)
Qiang Xue committed
445
			. ' (' . $this->buildColumns($columns) . ')';
w  
Qiang Xue committed
446 447 448 449 450 451 452 453 454 455
	}

	/**
	 * Builds a SQL statement 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 string the SQL statement for dropping an index.
	 */
	public function dropIndex($name, $table)
	{
Qiang Xue committed
456
		return 'DROP INDEX ' . $this->db->quoteTableName($name) . ' ON ' . $this->db->quoteTableName($table);
w  
Qiang Xue committed
457 458
	}

w  
Qiang Xue committed
459
	/**
Qiang Xue committed
460
	 * Creates a SQL statement for resetting the sequence value of a table's primary key.
w  
Qiang Xue committed
461 462
	 * The sequence will be reset such that the primary key of the next new row inserted
	 * will have the specified value or 1.
Qiang Xue committed
463
	 * @param string $table the name of the table whose primary key sequence will be reset
464
	 * @param array|string $value the value for the primary key of the next new row inserted. If this is not set,
w  
Qiang Xue committed
465
	 * the next new row's primary key will have a value 1.
Qiang Xue committed
466 467
	 * @return string the SQL statement for resetting sequence
	 * @throws NotSupportedException if this is not supported by the underlying DBMS
w  
Qiang Xue committed
468 469 470
	 */
	public function resetSequence($table, $value = null)
	{
Qiang Xue committed
471
		throw new NotSupportedException($this->db->getDriverName() . ' does not support resetting sequence.');
w  
Qiang Xue committed
472 473 474
	}

	/**
Qiang Xue committed
475
	 * Builds a SQL statement for enabling or disabling integrity check.
w  
Qiang Xue committed
476 477
	 * @param boolean $check whether to turn on or off the integrity check.
	 * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
resurtm committed
478
	 * @param string $table the table name. Defaults to empty string, meaning that no table will be changed.
Qiang Xue committed
479 480
	 * @return string the SQL statement for checking integrity
	 * @throws NotSupportedException if this is not supported by the underlying DBMS
w  
Qiang Xue committed
481
	 */
resurtm committed
482
	public function checkIntegrity($check = true, $schema = '', $table = '')
w  
Qiang Xue committed
483
	{
Qiang Xue committed
484
		throw new NotSupportedException($this->db->getDriverName() . ' does not support enabling/disabling integrity check.');
w  
Qiang Xue committed
485 486 487 488
	}

	/**
	 * Converts an abstract column type into a physical column type.
Qiang Xue committed
489
	 * The conversion is done using the type map specified in [[typeMap]].
Qiang Xue committed
490
	 * The following abstract column types are supported (using MySQL as an example to explain the corresponding
w  
Qiang Xue committed
491
	 * physical types):
Qiang Xue committed
492
	 *
Qiang Xue committed
493
	 * - `pk`: an auto-incremental primary key type, will be converted into "int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY"
494
	 * - `bigpk`: an auto-incremental primary key type, will be converted into "bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY"
Qiang Xue committed
495 496 497 498 499 500 501 502 503 504 505 506 507 508
	 * - `string`: string type, will be converted into "varchar(255)"
	 * - `text`: a long string type, will be converted into "text"
	 * - `smallint`: a small integer type, will be converted into "smallint(6)"
	 * - `integer`: integer type, will be converted into "int(11)"
	 * - `bigint`: a big integer type, will be converted into "bigint(20)"
	 * - `boolean`: boolean type, will be converted into "tinyint(1)"
	 * - `float``: float number type, will be converted into "float"
	 * - `decimal`: decimal number type, will be converted into "decimal"
	 * - `datetime`: datetime type, will be converted into "datetime"
	 * - `timestamp`: timestamp type, will be converted into "timestamp"
	 * - `time`: time type, will be converted into "time"
	 * - `date`: date type, will be converted into "date"
	 * - `money`: money type, will be converted into "decimal(19,4)"
	 * - `binary`: binary data type, will be converted into "blob"
w  
Qiang Xue committed
509 510
	 *
	 * If the abstract type contains two or more parts separated by spaces (e.g. "string NOT NULL"), then only
Qiang Xue committed
511
	 * the first part will be converted, and the rest of the parts will be appended to the converted result.
w  
Qiang Xue committed
512
	 * For example, 'string NOT NULL' is converted to 'varchar(255) NOT NULL'.
Qiang Xue committed
513
	 *
514 515 516 517 518
	 * For some of the abstract types you can also specify a length or precision constraint
	 * by prepending it in round brackets directly to the type.
	 * For example `string(32)` will be converted into "varchar(32)" on a MySQL database.
	 * If the underlying DBMS does not support these kind of constraints for a type it will
	 * be ignored.
519
	 *
Qiang Xue committed
520
	 * If a type cannot be found in [[typeMap]], it will be returned without any change.
w  
Qiang Xue committed
521 522 523
	 * @param string $type abstract column type
	 * @return string physical column type.
	 */
Qiang Xue committed
524 525
	public function getColumnType($type)
	{
w  
Qiang Xue committed
526 527
		if (isset($this->typeMap[$type])) {
			return $this->typeMap[$type];
528
		} elseif (preg_match('/^(\w+)\((.+?)\)(.*)$/', $type, $matches)) {
529 530 531
			if (isset($this->typeMap[$matches[1]])) {
				return preg_replace('/\(.+\)/', '(' . $matches[2] . ')', $this->typeMap[$matches[1]]) . $matches[3];
			}
Qiang Xue committed
532
		} elseif (preg_match('/^(\w+)\s+/', $type, $matches)) {
Qiang Xue committed
533 534
			if (isset($this->typeMap[$matches[1]])) {
				return preg_replace('/^\w+/', $this->typeMap[$matches[1]], $type);
Qiang Xue committed
535 536 537 538 539 540
			}
		}
		return $type;
	}

	/**
Qiang Xue committed
541
	 * @param array $columns
Qiang Xue committed
542 543
	 * @param boolean $distinct
	 * @param string $selectOption
Qiang Xue committed
544 545
	 * @return string the SELECT clause built from [[query]].
	 */
Qiang Xue committed
546
	public function buildSelect($columns, $distinct = false, $selectOption = null)
w  
Qiang Xue committed
547
	{
Qiang Xue committed
548 549 550
		$select = $distinct ? 'SELECT DISTINCT' : 'SELECT';
		if ($selectOption !== null) {
			$select .= ' ' . $selectOption;
w  
Qiang Xue committed
551
		}
w  
Qiang Xue committed
552

w  
Qiang Xue committed
553 554 555 556
		if (empty($columns)) {
			return $select . ' *';
		}

557 558 559 560 561
		foreach ($columns as $i => $column) {
			if (is_object($column)) {
				$columns[$i] = (string)$column;
			} elseif (strpos($column, '(') === false) {
				if (preg_match('/^(.*?)(?i:\s+as\s+|\s+)([\w\-_\.]+)$/', $column, $matches)) {
Qiang Xue committed
562
					$columns[$i] = $this->db->quoteColumnName($matches[1]) . ' AS ' . $this->db->quoteColumnName($matches[2]);
563
				} else {
Qiang Xue committed
564
					$columns[$i] = $this->db->quoteColumnName($column);
w  
Qiang Xue committed
565 566 567 568
				}
			}
		}

Qiang Xue committed
569 570 571 572 573
		if (is_array($columns)) {
			$columns = implode(', ', $columns);
		}

		return $select . ' ' . $columns;
w  
Qiang Xue committed
574 575
	}

Qiang Xue committed
576
	/**
Qiang Xue committed
577
	 * @param array $tables
Qiang Xue committed
578 579
	 * @return string the FROM clause built from [[query]].
	 */
Qiang Xue committed
580
	public function buildFrom($tables)
w  
Qiang Xue committed
581
	{
Qiang Xue committed
582
		if (empty($tables)) {
Qiang Xue committed
583 584 585
			return '';
		}

586 587
		foreach ($tables as $i => $table) {
			if (strpos($table, '(') === false) {
588
				if (preg_match('/^(.*?)(?i:\s+as|)\s+([^ ]+)$/', $table, $matches)) { // with alias
Qiang Xue committed
589
					$tables[$i] = $this->db->quoteTableName($matches[1]) . ' ' . $this->db->quoteTableName($matches[2]);
590
				} else {
Qiang Xue committed
591
					$tables[$i] = $this->db->quoteTableName($table);
Qiang Xue committed
592 593 594 595 596 597
				}
			}
		}

		if (is_array($tables)) {
			$tables = implode(', ', $tables);
w  
Qiang Xue committed
598 599
		}

Qiang Xue committed
600
		return 'FROM ' . $tables;
w  
Qiang Xue committed
601
	}
w  
Qiang Xue committed
602

Qiang Xue committed
603
	/**
Qiang Xue committed
604
	 * @param string|array $joins
605
	 * @param array $params the binding parameters to be populated
Qiang Xue committed
606
	 * @return string the JOIN clause built from [[query]].
Qiang Xue committed
607
	 * @throws Exception if the $joins parameter is not in proper format
Qiang Xue committed
608
	 */
609
	public function buildJoin($joins, &$params)
w  
Qiang Xue committed
610 611 612 613
	{
		if (empty($joins)) {
			return '';
		}
w  
Qiang Xue committed
614

w  
Qiang Xue committed
615
		foreach ($joins as $i => $join) {
Qiang Xue committed
616 617 618 619 620 621
			if (is_object($join)) {
				$joins[$i] = (string)$join;
			} elseif (is_array($join) && isset($join[0], $join[1])) {
				// 0:join type, 1:table name, 2:on-condition
				$table = $join[1];
				if (strpos($table, '(') === false) {
622
					if (preg_match('/^(.*?)(?i:\s+as|)\s+([^ ]+)$/', $table, $matches)) { // with alias
Qiang Xue committed
623 624 625
						$table = $this->db->quoteTableName($matches[1]) . ' ' . $this->db->quoteTableName($matches[2]);
					} else {
						$table = $this->db->quoteTableName($table);
w  
Qiang Xue committed
626
					}
Qiang Xue committed
627 628 629
				}
				$joins[$i] = $join[0] . ' ' . $table;
				if (isset($join[2])) {
630
					$condition = $this->buildCondition($join[2], $params);
Qiang Xue committed
631
					if ($condition !== '') {
632
						$joins[$i] .= ' ON ' . $condition;
w  
Qiang Xue committed
633 634
					}
				}
Qiang Xue committed
635 636
			} else {
				throw new Exception('A join clause must be specified as an array of join type, join table, and optionally join condition.');
w  
Qiang Xue committed
637 638
			}
		}
w  
Qiang Xue committed
639

Qiang Xue committed
640
		return implode($this->separator, $joins);
w  
Qiang Xue committed
641 642
	}

Qiang Xue committed
643
	/**
Qiang Xue committed
644
	 * @param string|array $condition
645
	 * @param array $params the binding parameters to be populated
Qiang Xue committed
646 647
	 * @return string the WHERE clause built from [[query]].
	 */
648
	public function buildWhere($condition, &$params)
w  
Qiang Xue committed
649
	{
650
		$where = $this->buildCondition($condition, $params);
Qiang Xue committed
651
		return $where === '' ? '' : 'WHERE ' . $where;
w  
Qiang Xue committed
652 653
	}

Qiang Xue committed
654
	/**
Qiang Xue committed
655
	 * @param array $columns
Qiang Xue committed
656
	 * @return string the GROUP BY clause
Qiang Xue committed
657
	 */
Qiang Xue committed
658
	public function buildGroupBy($columns)
w  
Qiang Xue committed
659
	{
Qiang Xue committed
660
		return empty($columns) ? '' : 'GROUP BY ' . $this->buildColumns($columns);
w  
Qiang Xue committed
661 662
	}

Qiang Xue committed
663
	/**
Qiang Xue committed
664
	 * @param string|array $condition
665
	 * @param array $params the binding parameters to be populated
Qiang Xue committed
666 667
	 * @return string the HAVING clause built from [[query]].
	 */
668
	public function buildHaving($condition, &$params)
w  
Qiang Xue committed
669
	{
670
		$having = $this->buildCondition($condition, $params);
Qiang Xue committed
671
		return $having === '' ? '' : 'HAVING ' . $having;
w  
Qiang Xue committed
672 673
	}

Qiang Xue committed
674
	/**
Qiang Xue committed
675
	 * @param array $columns
Qiang Xue committed
676 677
	 * @return string the ORDER BY clause built from [[query]].
	 */
Qiang Xue committed
678
	public function buildOrderBy($columns)
w  
Qiang Xue committed
679
	{
Qiang Xue committed
680
		if (empty($columns)) {
w  
Qiang Xue committed
681 682
			return '';
		}
Qiang Xue committed
683 684 685 686
		$orders = array();
		foreach ($columns as $name => $direction) {
			if (is_object($direction)) {
				$orders[] = (string)$direction;
687
			} else {
Qiang Xue committed
688
				$orders[] = $this->db->quoteColumnName($name) . ($direction === Query::SORT_DESC ? ' DESC' : '');
Qiang Xue committed
689 690
			}
		}
Qiang Xue committed
691 692

		return 'ORDER BY ' . implode(', ', $orders);
w  
Qiang Xue committed
693 694
	}

Qiang Xue committed
695
	/**
Qiang Xue committed
696 697
	 * @param integer $limit
	 * @param integer $offset
Qiang Xue committed
698 699
	 * @return string the LIMIT and OFFSET clauses built from [[query]].
	 */
Qiang Xue committed
700
	public function buildLimit($limit, $offset)
w  
Qiang Xue committed
701
	{
w  
Qiang Xue committed
702
		$sql = '';
Qiang Xue committed
703 704
		if ($limit !== null && $limit >= 0) {
			$sql = 'LIMIT ' . (int)$limit;
w  
Qiang Xue committed
705
		}
Qiang Xue committed
706 707
		if ($offset > 0) {
			$sql .= ' OFFSET ' . (int)$offset;
w  
Qiang Xue committed
708 709
		}
		return ltrim($sql);
w  
Qiang Xue committed
710 711
	}

Qiang Xue committed
712
	/**
Qiang Xue committed
713
	 * @param array $unions
714
	 * @param array $params the binding parameters to be populated
Qiang Xue committed
715 716
	 * @return string the UNION clause built from [[query]].
	 */
717
	public function buildUnion($unions, &$params)
w  
Qiang Xue committed
718
	{
w  
Qiang Xue committed
719 720 721 722
		if (empty($unions)) {
			return '';
		}
		foreach ($unions as $i => $union) {
Qiang Xue committed
723
			if ($union instanceof Query) {
724 725
				// save the original parameters so that we can restore them later to prevent from modifying the query object
				$originalParams = $union->params;
726
				$union->addParams($params);
727 728
				list ($unions[$i], $params) = $this->build($union);
				$union->params = $originalParams;
w  
Qiang Xue committed
729 730 731
			}
		}
		return "UNION (\n" . implode("\n) UNION (\n", $unions) . "\n)";
w  
Qiang Xue committed
732
	}
Qiang Xue committed
733 734 735 736 737 738 739

	/**
	 * Processes columns and properly quote them if necessary.
	 * It will join all columns into a string with comma as separators.
	 * @param string|array $columns the columns to be processed
	 * @return string the processing result
	 */
740
	public function buildColumns($columns)
Qiang Xue committed
741
	{
742 743 744 745 746
		if (!is_array($columns)) {
			if (strpos($columns, '(') !== false) {
				return $columns;
			} else {
				$columns = preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY);
Qiang Xue committed
747
			}
748 749 750 751 752
		}
		foreach ($columns as $i => $column) {
			if (is_object($column)) {
				$columns[$i] = (string)$column;
			} elseif (strpos($column, '(') === false) {
Qiang Xue committed
753
				$columns[$i] = $this->db->quoteColumnName($column);
Qiang Xue committed
754 755 756 757
			}
		}
		return is_array($columns) ? implode(', ', $columns) : $columns;
	}
758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784


	/**
	 * Parses the condition specification and generates the corresponding SQL expression.
	 * @param string|array $condition the condition specification. Please refer to [[Query::where()]]
	 * on how to specify a condition.
	 * @param array $params the binding parameters to be populated
	 * @return string the generated SQL expression
	 * @throws \yii\db\Exception if the condition is in bad format
	 */
	public function buildCondition($condition, &$params)
	{
		static $builders = array(
			'AND' => 'buildAndCondition',
			'OR' => 'buildAndCondition',
			'BETWEEN' => 'buildBetweenCondition',
			'NOT BETWEEN' => 'buildBetweenCondition',
			'IN' => 'buildInCondition',
			'NOT IN' => 'buildInCondition',
			'LIKE' => 'buildLikeCondition',
			'NOT LIKE' => 'buildLikeCondition',
			'OR LIKE' => 'buildLikeCondition',
			'OR NOT LIKE' => 'buildLikeCondition',
		);

		if (!is_array($condition)) {
			return (string)$condition;
785
		} elseif (empty($condition)) {
786 787 788 789 790 791 792 793 794 795 796
			return '';
		}
		if (isset($condition[0])) { // operator format: operator, operand 1, operand 2, ...
			$operator = strtoupper($condition[0]);
			if (isset($builders[$operator])) {
				$method = $builders[$operator];
				array_shift($condition);
				return $this->$method($operator, $condition, $params);
			} else {
				throw new Exception('Found unknown operator in query: ' . $operator);
			}
resurtm committed
797
		} else { // hash format: 'column1' => 'value1', 'column2' => 'value2', ...
798 799 800 801 802 803 804 805 806
			return $this->buildHashCondition($condition, $params);
		}
	}

	private function buildHashCondition($condition, &$params)
	{
		$parts = array();
		foreach ($condition as $column => $value) {
			if (is_array($value)) { // IN condition
807
				$parts[] = $this->buildInCondition('IN', array($column, $value), $params);
808 809 810 811 812 813
			} else {
				if (strpos($column, '(') === false) {
					$column = $this->db->quoteColumnName($column);
				}
				if ($value === null) {
					$parts[] = "$column IS NULL";
814 815 816 817 818
				} elseif ($value instanceof Expression) {
					$parts[] = "$column=" . $value->expression;
					foreach ($value->params as $n => $v) {
						$params[$n] = $v;
					}
819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839
				} else {
					$phName = self::PARAM_PREFIX . count($params);
					$parts[] = "$column=$phName";
					$params[$phName] = $value;
				}
			}
		}
		return count($parts) === 1 ? $parts[0] : '(' . implode(') AND (', $parts) . ')';
	}

	private function buildAndCondition($operator, $operands, &$params)
	{
		$parts = array();
		foreach ($operands as $operand) {
			if (is_array($operand)) {
				$operand = $this->buildCondition($operand, $params);
			}
			if ($operand !== '') {
				$parts[] = $operand;
			}
		}
840
		if (!empty($parts)) {
841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859
			return '(' . implode(") $operator (", $parts) . ')';
		} else {
			return '';
		}
	}

	private function buildBetweenCondition($operator, $operands, &$params)
	{
		if (!isset($operands[0], $operands[1], $operands[2])) {
			throw new Exception("Operator '$operator' requires three operands.");
		}

		list($column, $value1, $value2) = $operands;

		if (strpos($column, '(') === false) {
			$column = $this->db->quoteColumnName($column);
		}
		$phName1 = self::PARAM_PREFIX . count($params);
		$params[$phName1] = $value1;
Qiang Xue committed
860
		$phName2 = self::PARAM_PREFIX . count($params);
861 862 863 864 865 866 867 868 869 870 871 872 873 874 875
		$params[$phName2] = $value2;

		return "$column $operator $phName1 AND $phName2";
	}

	private function buildInCondition($operator, $operands, &$params)
	{
		if (!isset($operands[0], $operands[1])) {
			throw new Exception("Operator '$operator' requires two operands.");
		}

		list($column, $values) = $operands;

		$values = (array)$values;

Alexander Kochetov committed
876
		if (empty($values) || $column === array()) {
877 878 879 880 881 882 883 884 885 886 887 888 889 890
			return $operator === 'IN' ? '0=1' : '';
		}

		if (count($column) > 1) {
			return $this->buildCompositeInCondition($operator, $column, $values, $params);
		} elseif (is_array($column)) {
			$column = reset($column);
		}
		foreach ($values as $i => $value) {
			if (is_array($value)) {
				$value = isset($value[$column]) ? $value[$column] : null;
			}
			if ($value === null) {
				$values[$i] = 'NULL';
891 892 893 894 895
			} elseif ($value instanceof Expression) {
				$values[$i] = $value->expression;
				foreach ($value->params as $n => $v) {
					$params[$n] = $v;
				}
896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929
			} else {
				$phName = self::PARAM_PREFIX . count($params);
				$params[$phName] = $value;
				$values[$i] = $phName;
			}
		}
		if (strpos($column, '(') === false) {
			$column = $this->db->quoteColumnName($column);
		}

		if (count($values) > 1) {
			return "$column $operator (" . implode(', ', $values) . ')';
		} else {
			$operator = $operator === 'IN' ? '=' : '<>';
			return "$column$operator{$values[0]}";
		}
	}

	protected function buildCompositeInCondition($operator, $columns, $values, &$params)
	{
		$vss = array();
		foreach ($values as $value) {
			$vs = array();
			foreach ($columns as $column) {
				if (isset($value[$column])) {
					$phName = self::PARAM_PREFIX . count($params);
					$params[$phName] = $value[$column];
					$vs[] = $phName;
				} else {
					$vs[] = 'NULL';
				}
			}
			$vss[] = '(' . implode(', ', $vs) . ')';
		}
Qiang Xue committed
930 931 932 933 934
		foreach ($columns as $i => $column) {
			if (strpos($column, '(') === false) {
				$columns[$i] = $this->db->quoteColumnName($column);
			}
		}
935 936 937 938 939 940 941 942 943 944 945 946 947
		return '(' . implode(', ', $columns) . ") $operator (" . implode(', ', $vss) . ')';
	}

	private function buildLikeCondition($operator, $operands, &$params)
	{
		if (!isset($operands[0], $operands[1])) {
			throw new Exception("Operator '$operator' requires two operands.");
		}

		list($column, $values) = $operands;

		$values = (array)$values;

948
		if (empty($values)) {
949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971
			return $operator === 'LIKE' || $operator === 'OR LIKE' ? '0=1' : '';
		}

		if ($operator === 'LIKE' || $operator === 'NOT LIKE') {
			$andor = ' AND ';
		} else {
			$andor = ' OR ';
			$operator = $operator === 'OR LIKE' ? 'LIKE' : 'NOT LIKE';
		}

		if (strpos($column, '(') === false) {
			$column = $this->db->quoteColumnName($column);
		}

		$parts = array();
		foreach ($values as $value) {
			$phName = self::PARAM_PREFIX . count($params);
			$params[$phName] = $value;
			$parts[] = "$column $operator $phName";
		}

		return implode($andor, $parts);
	}
w  
Qiang Xue committed
972
}