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

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

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

Qiang Xue committed
29 30 31
	/**
	 * @var Connection the database connection.
	 */
Qiang Xue committed
32
	public $db;
Qiang Xue committed
33 34 35 36 37
	/**
	 * @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
38 39 40 41 42 43
	/**
	 * @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
44

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

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

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

Qiang Xue committed
114
		return 'INSERT INTO ' . $this->db->quoteTableName($table)
w  
Qiang Xue committed
115 116 117 118
			. ' (' . implode(', ', $names) . ') VALUES ('
			. implode(', ', $placeholders) . ')';
	}

Qiang Xue committed
119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138
	/**
	 * 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();
	 * ~~~
	 *
	 * 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 string the batch INSERT SQL statement
	 * @throws NotSupportedException if this is not supported by the underlying DBMS
	 */
139
	public function batchInsert($table, $columns, $rows)
Qiang Xue committed
140 141 142 143 144
	{
		throw new NotSupportedException($this->db->getDriverName() . ' does not support batch insert.');

	}

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

182 183 184
		$sql = 'UPDATE ' . $this->db->quoteTableName($table) . ' SET ' . implode(', ', $lines);
		$where = $this->buildWhere($condition, $params);
		return $where === '' ? $sql : $sql . ' ' . $where;
w  
Qiang Xue committed
185 186 187
	}

	/**
Qiang Xue committed
188
	 * Creates a DELETE SQL statement.
Qiang Xue committed
189 190 191 192 193 194
	 * For example,
	 *
	 * ~~~
	 * $sql = $queryBuilder->delete('tbl_user', 'status = 0');
	 * ~~~
	 *
Qiang Xue committed
195 196
	 * The method will properly escape the table and column names.
	 *
w  
Qiang Xue committed
197
	 * @param string $table the table where the data will be deleted from.
Qiang Xue committed
198 199
	 * @param mixed $condition the condition that will be put in the WHERE part. Please
	 * refer to [[Query::where()]] on how to specify condition.
200 201
	 * @param array $params the binding parameters that will be modified by this method
	 * so that they can be bound to the DB command later.
202
	 * @return string the DELETE SQL
w  
Qiang Xue committed
203
	 */
204
	public function delete($table, $condition, &$params)
w  
Qiang Xue committed
205
	{
Qiang Xue committed
206
		$sql = 'DELETE FROM ' . $this->db->quoteTableName($table);
207 208
		$where = $this->buildWhere($condition, $params);
		return $where === '' ? $sql : $sql . ' ' . $where;
w  
Qiang Xue committed
209 210
	}

w  
Qiang Xue committed
211 212 213
	/**
	 * Builds a SQL statement for creating a new DB table.
	 *
resurtm committed
214
	 * The columns in the new  table should be specified as name-definition pairs (e.g. 'name' => 'string'),
w  
Qiang Xue committed
215 216
	 * 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
217
	 * The [[getColumnType()]] method will be invoked to convert any abstract type into a physical one.
w  
Qiang Xue committed
218 219 220 221
	 *
	 * 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
222 223 224 225
	 * For example,
	 *
	 * ~~~
	 * $sql = $queryBuilder->createTable('tbl_user', array(
Qiang Xue committed
226 227 228
	 *	 'id' => 'pk',
	 *	 'name' => 'string',
	 *	 'age' => 'integer',
Qiang Xue committed
229 230 231
	 * ));
	 * ~~~
	 *
w  
Qiang Xue committed
232
	 * @param string $table the name of the table to be created. The name will be properly quoted by the method.
resurtm committed
233
	 * @param array $columns the columns (name => definition) in the new table.
w  
Qiang Xue committed
234 235 236 237 238 239
	 * @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
240 241
		foreach ($columns as $name => $type) {
			if (is_string($name)) {
Qiang Xue committed
242
				$cols[] = "\t" . $this->db->quoteColumnName($name) . ' ' . $this->getColumnType($type);
Qiang Xue committed
243
			} else {
w  
Qiang Xue committed
244
				$cols[] = "\t" . $type;
Qiang Xue committed
245
			}
w  
Qiang Xue committed
246
		}
Qiang Xue committed
247
		$sql = "CREATE TABLE " . $this->db->quoteTableName($table) . " (\n" . implode(",\n", $cols) . "\n)";
w  
Qiang Xue committed
248 249 250 251 252
		return $options === null ? $sql : $sql . ' ' . $options;
	}

	/**
	 * Builds a SQL statement for renaming a DB table.
Qiang Xue committed
253
	 * @param string $oldName the table to be renamed. The name will be properly quoted by the method.
w  
Qiang Xue committed
254 255 256
	 * @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
257
	public function renameTable($oldName, $newName)
w  
Qiang Xue committed
258
	{
Qiang Xue committed
259
		return 'RENAME TABLE ' . $this->db->quoteTableName($oldName) . ' TO ' . $this->db->quoteTableName($newName);
w  
Qiang Xue committed
260 261 262 263 264 265 266 267 268
	}

	/**
	 * 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
269
		return "DROP TABLE " . $this->db->quoteTableName($table);
w  
Qiang Xue committed
270
	}
271 272 273 274 275 276 277 278
	
	/**
	 * 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.
	 */
279
	public function addPrimaryKey($name, $table, $columns)
280
	{
281
		if (is_string($columns)) {
282
			$columns=preg_split('/\s*,\s*/',$columns,-1,PREG_SPLIT_NO_EMPTY);
283 284 285
		}
		
		foreach ($columns as $i=>$col) {
286
			$columns[$i]=$this->db->quoteColumnName($col);
287 288
		}
		
289 290
		return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ADD CONSTRAINT '
			. $this->db->quoteColumnName($name) . '  PRIMARY KEY ('
291 292 293 294
			. implode(', ', $columns). ' )';
	}	
	
	/**
295 296 297 298
	 * 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.	 *
299
	 */
300
	public function dropPrimarykey($name, $table)
301
	{
302 303 304
		return 'ALTER TABLE ' . $this->db->quoteTableName($table)
			. ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name);
		
305
	}	
w  
Qiang Xue committed
306 307 308 309 310 311 312

	/**
	 * 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
313
	{
Qiang Xue committed
314
		return "TRUNCATE TABLE " . $this->db->quoteTableName($table);
w  
Qiang Xue committed
315 316 317 318 319 320
	}

	/**
	 * 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
321
	 * @param string $type the column type. The [[getColumnType()]] method will be invoked to convert abstract column type (if any)
w  
Qiang Xue committed
322 323 324 325 326 327
	 * 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
328 329
		return 'ALTER TABLE ' . $this->db->quoteTableName($table)
			. ' ADD ' . $this->db->quoteColumnName($column) . ' '
w  
Qiang Xue committed
330 331
			. $this->getColumnType($type);
	}
w  
Qiang Xue committed
332

w  
Qiang Xue committed
333 334 335 336 337 338 339 340
	/**
	 * 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
341 342
		return "ALTER TABLE " . $this->db->quoteTableName($table)
			. " DROP COLUMN " . $this->db->quoteColumnName($column);
w  
Qiang Xue committed
343 344 345 346 347
	}

	/**
	 * 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
348
	 * @param string $oldName the old name of the column. The name will be properly quoted by the method.
w  
Qiang Xue committed
349 350 351
	 * @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
352
	public function renameColumn($table, $oldName, $newName)
w  
Qiang Xue committed
353
	{
Qiang Xue committed
354 355 356
		return "ALTER TABLE " . $this->db->quoteTableName($table)
			. " RENAME COLUMN " . $this->db->quoteColumnName($oldName)
			. " TO " . $this->db->quoteColumnName($newName);
w  
Qiang Xue committed
357 358 359 360 361 362
	}

	/**
	 * 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
363 364 365 366
	 * @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
367 368 369 370
	 * @return string the SQL statement for changing the definition of a column.
	 */
	public function alterColumn($table, $column, $type)
	{
Qiang Xue committed
371 372 373
		return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' CHANGE '
			. $this->db->quoteColumnName($column) . ' '
			. $this->db->quoteColumnName($column) . ' '
w  
Qiang Xue committed
374 375 376 377 378 379 380 381
			. $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
382 383
	 * @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
384
	 * @param string $refTable the table that the foreign key references to.
Qiang Xue committed
385 386
	 * @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
387 388 389 390 391 392
	 * @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
393 394
		$sql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
			. ' ADD CONSTRAINT ' . $this->db->quoteColumnName($name)
Qiang Xue committed
395
			. ' FOREIGN KEY (' . $this->buildColumns($columns) . ')'
Qiang Xue committed
396
			. ' REFERENCES ' . $this->db->quoteTableName($refTable)
Qiang Xue committed
397
			. ' (' . $this->buildColumns($refColumns) . ')';
Qiang Xue committed
398
		if ($delete !== null) {
w  
Qiang Xue committed
399
			$sql .= ' ON DELETE ' . $delete;
Qiang Xue committed
400 401
		}
		if ($update !== null) {
w  
Qiang Xue committed
402
			$sql .= ' ON UPDATE ' . $update;
Qiang Xue committed
403
		}
w  
Qiang Xue committed
404 405 406 407 408 409 410 411 412 413 414
		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
415 416
		return 'ALTER TABLE ' . $this->db->quoteTableName($table)
			. ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name);
w  
Qiang Xue committed
417 418 419 420 421 422
	}

	/**
	 * 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
423 424 425
	 * @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
426 427 428
	 * @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
429
	public function createIndex($name, $table, $columns, $unique = false)
w  
Qiang Xue committed
430 431
	{
		return ($unique ? 'CREATE UNIQUE INDEX ' : 'CREATE INDEX ')
Qiang Xue committed
432 433
			. $this->db->quoteTableName($name) . ' ON '
			. $this->db->quoteTableName($table)
Qiang Xue committed
434
			. ' (' . $this->buildColumns($columns) . ')';
w  
Qiang Xue committed
435 436 437 438 439 440 441 442 443 444
	}

	/**
	 * 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
445
		return 'DROP INDEX ' . $this->db->quoteTableName($name) . ' ON ' . $this->db->quoteTableName($table);
w  
Qiang Xue committed
446 447
	}

w  
Qiang Xue committed
448
	/**
Qiang Xue committed
449
	 * Creates a SQL statement for resetting the sequence value of a table's primary key.
w  
Qiang Xue committed
450 451
	 * 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
452
	 * @param string $table the name of the table whose primary key sequence will be reset
w  
Qiang Xue committed
453 454
	 * @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.
Qiang Xue committed
455 456
	 * @return string the SQL statement for resetting sequence
	 * @throws NotSupportedException if this is not supported by the underlying DBMS
w  
Qiang Xue committed
457 458 459
	 */
	public function resetSequence($table, $value = null)
	{
Qiang Xue committed
460
		throw new NotSupportedException($this->db->getDriverName() . ' does not support resetting sequence.');
w  
Qiang Xue committed
461 462 463
	}

	/**
Qiang Xue committed
464
	 * Builds a SQL statement for enabling or disabling integrity check.
w  
Qiang Xue committed
465 466
	 * @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
467
	 * @param string $table the table name. Defaults to empty string, meaning that no table will be changed.
Qiang Xue committed
468 469
	 * @return string the SQL statement for checking integrity
	 * @throws NotSupportedException if this is not supported by the underlying DBMS
w  
Qiang Xue committed
470
	 */
resurtm committed
471
	public function checkIntegrity($check = true, $schema = '', $table = '')
w  
Qiang Xue committed
472
	{
Qiang Xue committed
473
		throw new NotSupportedException($this->db->getDriverName() . ' does not support enabling/disabling integrity check.');
w  
Qiang Xue committed
474 475 476 477
	}

	/**
	 * Converts an abstract column type into a physical column type.
Qiang Xue committed
478
	 * The conversion is done using the type map specified in [[typeMap]].
Qiang Xue committed
479
	 * The following abstract column types are supported (using MySQL as an example to explain the corresponding
w  
Qiang Xue committed
480
	 * physical types):
Qiang Xue committed
481
	 *
Qiang Xue committed
482 483 484 485 486 487 488 489 490 491 492 493 494 495 496
	 * - `pk`: an auto-incremental primary key type, will be converted into "int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY"
	 * - `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
497 498
	 *
	 * If the abstract type contains two or more parts separated by spaces (e.g. "string NOT NULL"), then only
Qiang Xue committed
499
	 * the first part will be converted, and the rest of the parts will be appended to the converted result.
w  
Qiang Xue committed
500
	 * For example, 'string NOT NULL' is converted to 'varchar(255) NOT NULL'.
Qiang Xue committed
501
	 *
502 503 504 505 506
	 * 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.
507
	 *
Qiang Xue committed
508
	 * If a type cannot be found in [[typeMap]], it will be returned without any change.
w  
Qiang Xue committed
509 510 511
	 * @param string $type abstract column type
	 * @return string physical column type.
	 */
Qiang Xue committed
512 513
	public function getColumnType($type)
	{
w  
Qiang Xue committed
514 515
		if (isset($this->typeMap[$type])) {
			return $this->typeMap[$type];
516
		} elseif (preg_match('/^(\w+)\((.+?)\)(.*)$/', $type, $matches)) {
517 518 519
			if (isset($this->typeMap[$matches[1]])) {
				return preg_replace('/\(.+\)/', '(' . $matches[2] . ')', $this->typeMap[$matches[1]]) . $matches[3];
			}
Qiang Xue committed
520
		} elseif (preg_match('/^(\w+)\s+/', $type, $matches)) {
Qiang Xue committed
521 522
			if (isset($this->typeMap[$matches[1]])) {
				return preg_replace('/^\w+/', $this->typeMap[$matches[1]], $type);
Qiang Xue committed
523 524 525 526 527 528
			}
		}
		return $type;
	}

	/**
Qiang Xue committed
529
	 * @param array $columns
Qiang Xue committed
530 531
	 * @param boolean $distinct
	 * @param string $selectOption
Qiang Xue committed
532 533
	 * @return string the SELECT clause built from [[query]].
	 */
Qiang Xue committed
534
	public function buildSelect($columns, $distinct = false, $selectOption = null)
w  
Qiang Xue committed
535
	{
Qiang Xue committed
536 537 538
		$select = $distinct ? 'SELECT DISTINCT' : 'SELECT';
		if ($selectOption !== null) {
			$select .= ' ' . $selectOption;
w  
Qiang Xue committed
539
		}
w  
Qiang Xue committed
540

w  
Qiang Xue committed
541 542 543 544
		if (empty($columns)) {
			return $select . ' *';
		}

545 546 547 548 549
		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
550
					$columns[$i] = $this->db->quoteColumnName($matches[1]) . ' AS ' . $this->db->quoteColumnName($matches[2]);
551
				} else {
Qiang Xue committed
552
					$columns[$i] = $this->db->quoteColumnName($column);
w  
Qiang Xue committed
553 554 555 556
				}
			}
		}

Qiang Xue committed
557 558 559 560 561
		if (is_array($columns)) {
			$columns = implode(', ', $columns);
		}

		return $select . ' ' . $columns;
w  
Qiang Xue committed
562 563
	}

Qiang Xue committed
564
	/**
Qiang Xue committed
565
	 * @param array $tables
Qiang Xue committed
566 567
	 * @return string the FROM clause built from [[query]].
	 */
Qiang Xue committed
568
	public function buildFrom($tables)
w  
Qiang Xue committed
569
	{
Qiang Xue committed
570
		if (empty($tables)) {
Qiang Xue committed
571 572 573
			return '';
		}

574 575 576
		foreach ($tables as $i => $table) {
			if (strpos($table, '(') === false) {
				if (preg_match('/^(.*?)(?i:\s+as\s+|\s+)(.*)$/i', $table, $matches)) { // with alias
Qiang Xue committed
577
					$tables[$i] = $this->db->quoteTableName($matches[1]) . ' ' . $this->db->quoteTableName($matches[2]);
578
				} else {
Qiang Xue committed
579
					$tables[$i] = $this->db->quoteTableName($table);
Qiang Xue committed
580 581 582 583 584 585
				}
			}
		}

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

Qiang Xue committed
588
		return 'FROM ' . $tables;
w  
Qiang Xue committed
589
	}
w  
Qiang Xue committed
590

Qiang Xue committed
591
	/**
Qiang Xue committed
592
	 * @param string|array $joins
593
	 * @param array $params the binding parameters to be populated
Qiang Xue committed
594
	 * @return string the JOIN clause built from [[query]].
Qiang Xue committed
595
	 * @throws Exception if the $joins parameter is not in proper format
Qiang Xue committed
596
	 */
597
	public function buildJoin($joins, &$params)
w  
Qiang Xue committed
598 599 600 601
	{
		if (empty($joins)) {
			return '';
		}
w  
Qiang Xue committed
602

w  
Qiang Xue committed
603
		foreach ($joins as $i => $join) {
Qiang Xue committed
604 605 606 607 608 609 610 611 612 613
			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) {
					if (preg_match('/^(.*?)(?i:\s+as\s+|\s+)(.*)$/', $table, $matches)) { // with alias
						$table = $this->db->quoteTableName($matches[1]) . ' ' . $this->db->quoteTableName($matches[2]);
					} else {
						$table = $this->db->quoteTableName($table);
w  
Qiang Xue committed
614
					}
Qiang Xue committed
615 616 617
				}
				$joins[$i] = $join[0] . ' ' . $table;
				if (isset($join[2])) {
618
					$condition = $this->buildCondition($join[2], $params);
Qiang Xue committed
619
					if ($condition !== '') {
620
						$joins[$i] .= ' ON ' . $condition;
w  
Qiang Xue committed
621 622
					}
				}
Qiang Xue committed
623 624
			} 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
625 626
			}
		}
w  
Qiang Xue committed
627

Qiang Xue committed
628
		return implode($this->separator, $joins);
w  
Qiang Xue committed
629 630
	}

Qiang Xue committed
631
	/**
Qiang Xue committed
632
	 * @param string|array $condition
633
	 * @param array $params the binding parameters to be populated
Qiang Xue committed
634 635
	 * @return string the WHERE clause built from [[query]].
	 */
636
	public function buildWhere($condition, &$params)
w  
Qiang Xue committed
637
	{
638
		$where = $this->buildCondition($condition, $params);
Qiang Xue committed
639
		return $where === '' ? '' : 'WHERE ' . $where;
w  
Qiang Xue committed
640 641
	}

Qiang Xue committed
642
	/**
Qiang Xue committed
643
	 * @param array $columns
Qiang Xue committed
644
	 * @return string the GROUP BY clause
Qiang Xue committed
645
	 */
Qiang Xue committed
646
	public function buildGroupBy($columns)
w  
Qiang Xue committed
647
	{
Qiang Xue committed
648
		return empty($columns) ? '' : 'GROUP BY ' . $this->buildColumns($columns);
w  
Qiang Xue committed
649 650
	}

Qiang Xue committed
651
	/**
Qiang Xue committed
652
	 * @param string|array $condition
653
	 * @param array $params the binding parameters to be populated
Qiang Xue committed
654 655
	 * @return string the HAVING clause built from [[query]].
	 */
656
	public function buildHaving($condition, &$params)
w  
Qiang Xue committed
657
	{
658
		$having = $this->buildCondition($condition, $params);
Qiang Xue committed
659
		return $having === '' ? '' : 'HAVING ' . $having;
w  
Qiang Xue committed
660 661
	}

Qiang Xue committed
662
	/**
Qiang Xue committed
663
	 * @param array $columns
Qiang Xue committed
664 665
	 * @return string the ORDER BY clause built from [[query]].
	 */
Qiang Xue committed
666
	public function buildOrderBy($columns)
w  
Qiang Xue committed
667
	{
Qiang Xue committed
668
		if (empty($columns)) {
w  
Qiang Xue committed
669 670
			return '';
		}
Qiang Xue committed
671 672 673 674
		$orders = array();
		foreach ($columns as $name => $direction) {
			if (is_object($direction)) {
				$orders[] = (string)$direction;
675
			} else {
Qiang Xue committed
676
				$orders[] = $this->db->quoteColumnName($name) . ($direction === Query::SORT_DESC ? ' DESC' : '');
Qiang Xue committed
677 678
			}
		}
Qiang Xue committed
679 680

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

Qiang Xue committed
683
	/**
Qiang Xue committed
684 685
	 * @param integer $limit
	 * @param integer $offset
Qiang Xue committed
686 687
	 * @return string the LIMIT and OFFSET clauses built from [[query]].
	 */
Qiang Xue committed
688
	public function buildLimit($limit, $offset)
w  
Qiang Xue committed
689
	{
w  
Qiang Xue committed
690
		$sql = '';
Qiang Xue committed
691 692
		if ($limit !== null && $limit >= 0) {
			$sql = 'LIMIT ' . (int)$limit;
w  
Qiang Xue committed
693
		}
Qiang Xue committed
694 695
		if ($offset > 0) {
			$sql .= ' OFFSET ' . (int)$offset;
w  
Qiang Xue committed
696 697
		}
		return ltrim($sql);
w  
Qiang Xue committed
698 699
	}

Qiang Xue committed
700
	/**
Qiang Xue committed
701
	 * @param array $unions
702
	 * @param array $params the binding parameters to be populated
Qiang Xue committed
703 704
	 * @return string the UNION clause built from [[query]].
	 */
705
	public function buildUnion($unions, &$params)
w  
Qiang Xue committed
706
	{
w  
Qiang Xue committed
707 708 709 710
		if (empty($unions)) {
			return '';
		}
		foreach ($unions as $i => $union) {
Qiang Xue committed
711
			if ($union instanceof Query) {
712
				$union->addParams($params);
Qiang Xue committed
713
				$unions[$i] = $this->build($union);
714
				$params = $union->params;
w  
Qiang Xue committed
715 716 717
			}
		}
		return "UNION (\n" . implode("\n) UNION (\n", $unions) . "\n)";
w  
Qiang Xue committed
718
	}
Qiang Xue committed
719 720 721 722 723 724 725

	/**
	 * 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
	 */
726
	public function buildColumns($columns)
Qiang Xue committed
727
	{
728 729 730 731 732
		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
733
			}
734 735 736 737 738
		}
		foreach ($columns as $i => $column) {
			if (is_object($column)) {
				$columns[$i] = (string)$column;
			} elseif (strpos($column, '(') === false) {
Qiang Xue committed
739
				$columns[$i] = $this->db->quoteColumnName($column);
Qiang Xue committed
740 741 742 743
			}
		}
		return is_array($columns) ? implode(', ', $columns) : $columns;
	}
744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770


	/**
	 * 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;
771
		} elseif (empty($condition)) {
772 773 774 775 776 777 778 779 780 781 782
			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
783
		} else { // hash format: 'column1' => 'value1', 'column2' => 'value2', ...
784 785 786 787 788 789 790 791 792
			return $this->buildHashCondition($condition, $params);
		}
	}

	private function buildHashCondition($condition, &$params)
	{
		$parts = array();
		foreach ($condition as $column => $value) {
			if (is_array($value)) { // IN condition
793
				$parts[] = $this->buildInCondition('in', array($column, $value), $params);
794 795 796 797 798 799
			} else {
				if (strpos($column, '(') === false) {
					$column = $this->db->quoteColumnName($column);
				}
				if ($value === null) {
					$parts[] = "$column IS NULL";
800 801 802 803 804
				} elseif ($value instanceof Expression) {
					$parts[] = "$column=" . $value->expression;
					foreach ($value->params as $n => $v) {
						$params[$n] = $v;
					}
805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825
				} 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;
			}
		}
826
		if (!empty($parts)) {
827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861
			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);
		$phName2 = self::PARAM_PREFIX . count($params);
		$params[$phName1] = $value1;
		$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
862
		if (empty($values) || $column === array()) {
863 864 865 866 867 868 869 870 871 872 873 874 875 876
			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';
877 878 879 880 881
			} elseif ($value instanceof Expression) {
				$values[$i] = $value->expression;
				foreach ($value->params as $n => $v) {
					$params[$n] = $v;
				}
882 883 884 885 886 887 888 889 890 891 892 893 894 895 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 930 931 932 933
			} 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)
	{
		foreach ($columns as $i => $column) {
			if (strpos($column, '(') === false) {
				$columns[$i] = $this->db->quoteColumnName($column);
			}
		}
		$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) . ')';
		}
		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;

934
		if (empty($values)) {
935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957
			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
958
}