QueryBuilder.php 31 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
	}

	/**
	 * 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
278
	{
Qiang Xue committed
279
		return "TRUNCATE TABLE " . $this->db->quoteTableName($table);
w  
Qiang Xue committed
280 281 282 283 284 285
	}

	/**
	 * 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
286
	 * @param string $type the column type. The [[getColumnType()]] method will be invoked to convert abstract column type (if any)
w  
Qiang Xue committed
287 288 289 290 291 292
	 * 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
293 294
		return 'ALTER TABLE ' . $this->db->quoteTableName($table)
			. ' ADD ' . $this->db->quoteColumnName($column) . ' '
w  
Qiang Xue committed
295 296
			. $this->getColumnType($type);
	}
w  
Qiang Xue committed
297

w  
Qiang Xue committed
298 299 300 301 302 303 304 305
	/**
	 * 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
306 307
		return "ALTER TABLE " . $this->db->quoteTableName($table)
			. " DROP COLUMN " . $this->db->quoteColumnName($column);
w  
Qiang Xue committed
308 309 310 311 312
	}

	/**
	 * 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
313
	 * @param string $oldName the old name of the column. The name will be properly quoted by the method.
w  
Qiang Xue committed
314 315 316
	 * @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
317
	public function renameColumn($table, $oldName, $newName)
w  
Qiang Xue committed
318
	{
Qiang Xue committed
319 320 321
		return "ALTER TABLE " . $this->db->quoteTableName($table)
			. " RENAME COLUMN " . $this->db->quoteColumnName($oldName)
			. " TO " . $this->db->quoteColumnName($newName);
w  
Qiang Xue committed
322 323 324 325 326 327
	}

	/**
	 * 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
328 329 330 331
	 * @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
332 333 334 335
	 * @return string the SQL statement for changing the definition of a column.
	 */
	public function alterColumn($table, $column, $type)
	{
Qiang Xue committed
336 337 338
		return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' CHANGE '
			. $this->db->quoteColumnName($column) . ' '
			. $this->db->quoteColumnName($column) . ' '
w  
Qiang Xue committed
339 340 341 342 343 344 345 346
			. $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
347 348
	 * @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
349
	 * @param string $refTable the table that the foreign key references to.
Qiang Xue committed
350 351
	 * @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
352 353 354 355 356 357
	 * @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
358 359
		$sql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
			. ' ADD CONSTRAINT ' . $this->db->quoteColumnName($name)
Qiang Xue committed
360
			. ' FOREIGN KEY (' . $this->buildColumns($columns) . ')'
Qiang Xue committed
361
			. ' REFERENCES ' . $this->db->quoteTableName($refTable)
Qiang Xue committed
362
			. ' (' . $this->buildColumns($refColumns) . ')';
Qiang Xue committed
363
		if ($delete !== null) {
w  
Qiang Xue committed
364
			$sql .= ' ON DELETE ' . $delete;
Qiang Xue committed
365 366
		}
		if ($update !== null) {
w  
Qiang Xue committed
367
			$sql .= ' ON UPDATE ' . $update;
Qiang Xue committed
368
		}
w  
Qiang Xue committed
369 370 371 372 373 374 375 376 377 378 379
		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
380 381
		return 'ALTER TABLE ' . $this->db->quoteTableName($table)
			. ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name);
w  
Qiang Xue committed
382 383 384 385 386 387
	}

	/**
	 * 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
388 389 390
	 * @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
391 392 393
	 * @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
394
	public function createIndex($name, $table, $columns, $unique = false)
w  
Qiang Xue committed
395 396
	{
		return ($unique ? 'CREATE UNIQUE INDEX ' : 'CREATE INDEX ')
Qiang Xue committed
397 398
			. $this->db->quoteTableName($name) . ' ON '
			. $this->db->quoteTableName($table)
Qiang Xue committed
399
			. ' (' . $this->buildColumns($columns) . ')';
w  
Qiang Xue committed
400 401 402 403 404 405 406 407 408 409
	}

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

w  
Qiang Xue committed
413
	/**
Qiang Xue committed
414
	 * Creates a SQL statement for resetting the sequence value of a table's primary key.
w  
Qiang Xue committed
415 416
	 * 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
417
	 * @param string $table the name of the table whose primary key sequence will be reset
w  
Qiang Xue committed
418 419
	 * @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
420 421
	 * @return string the SQL statement for resetting sequence
	 * @throws NotSupportedException if this is not supported by the underlying DBMS
w  
Qiang Xue committed
422 423 424
	 */
	public function resetSequence($table, $value = null)
	{
Qiang Xue committed
425
		throw new NotSupportedException($this->db->getDriverName() . ' does not support resetting sequence.');
w  
Qiang Xue committed
426 427 428
	}

	/**
Qiang Xue committed
429
	 * Builds a SQL statement for enabling or disabling integrity check.
w  
Qiang Xue committed
430 431
	 * @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.
Qiang Xue committed
432 433
	 * @return string the SQL statement for checking integrity
	 * @throws NotSupportedException if this is not supported by the underlying DBMS
w  
Qiang Xue committed
434 435 436
	 */
	public function checkIntegrity($check = true, $schema = '')
	{
Qiang Xue committed
437
		throw new NotSupportedException($this->db->getDriverName() . ' does not support enabling/disabling integrity check.');
w  
Qiang Xue committed
438 439 440 441
	}

	/**
	 * Converts an abstract column type into a physical column type.
Qiang Xue committed
442
	 * The conversion is done using the type map specified in [[typeMap]].
Qiang Xue committed
443
	 * The following abstract column types are supported (using MySQL as an example to explain the corresponding
w  
Qiang Xue committed
444
	 * physical types):
Qiang Xue committed
445
	 *
Qiang Xue committed
446 447 448 449 450 451 452 453 454 455 456 457 458 459 460
	 * - `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
461 462
	 *
	 * If the abstract type contains two or more parts separated by spaces (e.g. "string NOT NULL"), then only
Qiang Xue committed
463
	 * the first part will be converted, and the rest of the parts will be appended to the converted result.
w  
Qiang Xue committed
464
	 * For example, 'string NOT NULL' is converted to 'varchar(255) NOT NULL'.
Qiang Xue committed
465 466
	 *
	 * If a type cannot be found in [[typeMap]], it will be returned without any change.
w  
Qiang Xue committed
467 468 469
	 * @param string $type abstract column type
	 * @return string physical column type.
	 */
Qiang Xue committed
470 471
	public function getColumnType($type)
	{
w  
Qiang Xue committed
472 473
		if (isset($this->typeMap[$type])) {
			return $this->typeMap[$type];
Qiang Xue committed
474
		} elseif (preg_match('/^(\w+)\s+/', $type, $matches)) {
Qiang Xue committed
475 476
			if (isset($this->typeMap[$matches[1]])) {
				return preg_replace('/^\w+/', $this->typeMap[$matches[1]], $type);
Qiang Xue committed
477 478 479 480 481 482
			}
		}
		return $type;
	}

	/**
Qiang Xue committed
483
	 * @param array $columns
Qiang Xue committed
484 485
	 * @param boolean $distinct
	 * @param string $selectOption
Qiang Xue committed
486 487
	 * @return string the SELECT clause built from [[query]].
	 */
Qiang Xue committed
488
	public function buildSelect($columns, $distinct = false, $selectOption = null)
w  
Qiang Xue committed
489
	{
Qiang Xue committed
490 491 492
		$select = $distinct ? 'SELECT DISTINCT' : 'SELECT';
		if ($selectOption !== null) {
			$select .= ' ' . $selectOption;
w  
Qiang Xue committed
493
		}
w  
Qiang Xue committed
494

w  
Qiang Xue committed
495 496 497 498
		if (empty($columns)) {
			return $select . ' *';
		}

499 500 501 502 503
		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
504
					$columns[$i] = $this->db->quoteColumnName($matches[1]) . ' AS ' . $this->db->quoteColumnName($matches[2]);
505
				} else {
Qiang Xue committed
506
					$columns[$i] = $this->db->quoteColumnName($column);
w  
Qiang Xue committed
507 508 509 510
				}
			}
		}

Qiang Xue committed
511 512 513 514 515
		if (is_array($columns)) {
			$columns = implode(', ', $columns);
		}

		return $select . ' ' . $columns;
w  
Qiang Xue committed
516 517
	}

Qiang Xue committed
518
	/**
Qiang Xue committed
519
	 * @param array $tables
Qiang Xue committed
520 521
	 * @return string the FROM clause built from [[query]].
	 */
Qiang Xue committed
522
	public function buildFrom($tables)
w  
Qiang Xue committed
523
	{
Qiang Xue committed
524
		if (empty($tables)) {
Qiang Xue committed
525 526 527
			return '';
		}

528 529 530
		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
531
					$tables[$i] = $this->db->quoteTableName($matches[1]) . ' ' . $this->db->quoteTableName($matches[2]);
532
				} else {
Qiang Xue committed
533
					$tables[$i] = $this->db->quoteTableName($table);
Qiang Xue committed
534 535 536 537 538 539
				}
			}
		}

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

Qiang Xue committed
542
		return 'FROM ' . $tables;
w  
Qiang Xue committed
543
	}
w  
Qiang Xue committed
544

Qiang Xue committed
545
	/**
Qiang Xue committed
546
	 * @param string|array $joins
547
	 * @param array $params the binding parameters to be populated
Qiang Xue committed
548
	 * @return string the JOIN clause built from [[query]].
Qiang Xue committed
549
	 * @throws Exception if the $joins parameter is not in proper format
Qiang Xue committed
550
	 */
551
	public function buildJoin($joins, &$params)
w  
Qiang Xue committed
552 553 554 555
	{
		if (empty($joins)) {
			return '';
		}
w  
Qiang Xue committed
556

w  
Qiang Xue committed
557
		foreach ($joins as $i => $join) {
Qiang Xue committed
558 559 560 561 562 563 564 565 566 567
			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
568
					}
Qiang Xue committed
569 570 571
				}
				$joins[$i] = $join[0] . ' ' . $table;
				if (isset($join[2])) {
572
					$condition = $this->buildCondition($join[2], $params);
Qiang Xue committed
573
					if ($condition !== '') {
574
						$joins[$i] .= ' ON ' . $condition;
w  
Qiang Xue committed
575 576
					}
				}
Qiang Xue committed
577 578
			} 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
579 580
			}
		}
w  
Qiang Xue committed
581

Qiang Xue committed
582
		return implode($this->separator, $joins);
w  
Qiang Xue committed
583 584
	}

Qiang Xue committed
585
	/**
Qiang Xue committed
586
	 * @param string|array $condition
587
	 * @param array $params the binding parameters to be populated
Qiang Xue committed
588 589
	 * @return string the WHERE clause built from [[query]].
	 */
590
	public function buildWhere($condition, &$params)
w  
Qiang Xue committed
591
	{
592
		$where = $this->buildCondition($condition, $params);
Qiang Xue committed
593
		return $where === '' ? '' : 'WHERE ' . $where;
w  
Qiang Xue committed
594 595
	}

Qiang Xue committed
596
	/**
Qiang Xue committed
597
	 * @param array $columns
Qiang Xue committed
598
	 * @return string the GROUP BY clause
Qiang Xue committed
599
	 */
Qiang Xue committed
600
	public function buildGroupBy($columns)
w  
Qiang Xue committed
601
	{
Qiang Xue committed
602
		return empty($columns) ? '' : 'GROUP BY ' . $this->buildColumns($columns);
w  
Qiang Xue committed
603 604
	}

Qiang Xue committed
605
	/**
Qiang Xue committed
606
	 * @param string|array $condition
607
	 * @param array $params the binding parameters to be populated
Qiang Xue committed
608 609
	 * @return string the HAVING clause built from [[query]].
	 */
610
	public function buildHaving($condition, &$params)
w  
Qiang Xue committed
611
	{
612
		$having = $this->buildCondition($condition, $params);
Qiang Xue committed
613
		return $having === '' ? '' : 'HAVING ' . $having;
w  
Qiang Xue committed
614 615
	}

Qiang Xue committed
616
	/**
Qiang Xue committed
617
	 * @param array $columns
Qiang Xue committed
618 619
	 * @return string the ORDER BY clause built from [[query]].
	 */
Qiang Xue committed
620
	public function buildOrderBy($columns)
w  
Qiang Xue committed
621
	{
Qiang Xue committed
622
		if (empty($columns)) {
w  
Qiang Xue committed
623 624
			return '';
		}
Qiang Xue committed
625 626 627 628
		$orders = array();
		foreach ($columns as $name => $direction) {
			if (is_object($direction)) {
				$orders[] = (string)$direction;
629
			} else {
Qiang Xue committed
630
				$orders[] = $this->db->quoteColumnName($name) . ($direction === Query::SORT_DESC ? ' DESC' : '');
Qiang Xue committed
631 632
			}
		}
Qiang Xue committed
633 634

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

Qiang Xue committed
637
	/**
Qiang Xue committed
638 639
	 * @param integer $limit
	 * @param integer $offset
Qiang Xue committed
640 641
	 * @return string the LIMIT and OFFSET clauses built from [[query]].
	 */
Qiang Xue committed
642
	public function buildLimit($limit, $offset)
w  
Qiang Xue committed
643
	{
w  
Qiang Xue committed
644
		$sql = '';
Qiang Xue committed
645 646
		if ($limit !== null && $limit >= 0) {
			$sql = 'LIMIT ' . (int)$limit;
w  
Qiang Xue committed
647
		}
Qiang Xue committed
648 649
		if ($offset > 0) {
			$sql .= ' OFFSET ' . (int)$offset;
w  
Qiang Xue committed
650 651
		}
		return ltrim($sql);
w  
Qiang Xue committed
652 653
	}

Qiang Xue committed
654
	/**
Qiang Xue committed
655
	 * @param array $unions
656
	 * @param array $params the binding parameters to be populated
Qiang Xue committed
657 658
	 * @return string the UNION clause built from [[query]].
	 */
659
	public function buildUnion($unions, &$params)
w  
Qiang Xue committed
660
	{
w  
Qiang Xue committed
661 662 663 664
		if (empty($unions)) {
			return '';
		}
		foreach ($unions as $i => $union) {
Qiang Xue committed
665
			if ($union instanceof Query) {
666
				$union->addParams($params);
Qiang Xue committed
667
				$unions[$i] = $this->build($union);
668
				$params = $union->params;
w  
Qiang Xue committed
669 670 671
			}
		}
		return "UNION (\n" . implode("\n) UNION (\n", $unions) . "\n)";
w  
Qiang Xue committed
672
	}
Qiang Xue committed
673 674 675 676 677 678 679

	/**
	 * 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
	 */
680
	public function buildColumns($columns)
Qiang Xue committed
681
	{
682 683 684 685 686
		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
687
			}
688 689 690 691 692
		}
		foreach ($columns as $i => $column) {
			if (is_object($column)) {
				$columns[$i] = (string)$column;
			} elseif (strpos($column, '(') === false) {
Qiang Xue committed
693
				$columns[$i] = $this->db->quoteColumnName($column);
Qiang Xue committed
694 695 696 697
			}
		}
		return is_array($columns) ? implode(', ', $columns) : $columns;
	}
698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724


	/**
	 * 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;
725
		} elseif (empty($condition)) {
726 727 728 729 730 731 732 733 734 735 736
			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
737
		} else { // hash format: 'column1' => 'value1', 'column2' => 'value2', ...
738 739 740 741 742 743 744 745 746
			return $this->buildHashCondition($condition, $params);
		}
	}

	private function buildHashCondition($condition, &$params)
	{
		$parts = array();
		foreach ($condition as $column => $value) {
			if (is_array($value)) { // IN condition
747
				$parts[] = $this->buildInCondition('in', array($column, $value), $params);
748 749 750 751 752 753
			} else {
				if (strpos($column, '(') === false) {
					$column = $this->db->quoteColumnName($column);
				}
				if ($value === null) {
					$parts[] = "$column IS NULL";
754 755 756 757 758
				} elseif ($value instanceof Expression) {
					$parts[] = "$column=" . $value->expression;
					foreach ($value->params as $n => $v) {
						$params[$n] = $v;
					}
759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779
				} 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;
			}
		}
780
		if (!empty($parts)) {
781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815
			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
816
		if (empty($values) || $column === array()) {
817 818 819 820 821 822 823 824 825 826 827 828 829 830
			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';
831 832 833 834 835
			} elseif ($value instanceof Expression) {
				$values[$i] = $value->expression;
				foreach ($value->params as $n => $v) {
					$params[$n] = $v;
				}
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 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887
			} 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;

888
		if (empty($values)) {
889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911
			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
912
}