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

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
Qiang Xue committed
58
	 * @return string the generated SQL statement
Qiang Xue committed
59
	 */
w  
Qiang Xue committed
60 61
	public function build($query)
	{
Qiang Xue committed
62
		$clauses = array(
Qiang Xue committed
63 64
			$this->buildSelect($query->select, $query->distinct, $query->selectOption),
			$this->buildFrom($query->from),
65 66
			$this->buildJoin($query->join, $query->params),
			$this->buildWhere($query->where, $query->params),
Qiang Xue committed
67
			$this->buildGroupBy($query->groupBy),
68 69
			$this->buildHaving($query->having, $query->params),
			$this->buildUnion($query->union, $query->params),
Qiang Xue committed
70
			$this->buildOrderBy($query->orderBy),
Qiang Xue committed
71
			$this->buildLimit($query->limit, $query->offset),
Qiang Xue committed
72 73
		);
		return implode($this->separator, array_filter($clauses));
w  
Qiang Xue committed
74 75 76
	}

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

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

Qiang Xue committed
118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137
	/**
	 * 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
	 */
138
	public function batchInsert($table, $columns, $rows)
Qiang Xue committed
139 140 141 142 143
	{
		throw new NotSupportedException($this->db->getDriverName() . ' does not support batch insert.');

	}

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

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

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

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

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

	/**
	 * 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
268
		return "DROP TABLE " . $this->db->quoteTableName($table);
w  
Qiang Xue committed
269
	}
270 271 272 273 274 275 276 277
	
	/**
	 * 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.
	 */
278
	public function addPrimaryKey($name, $table, $columns)
279
	{
280
		if (is_string($columns)) {
Alexander Makarov committed
281
			$columns = preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY);
282
		}
Alexander Makarov committed
283 284 285

		foreach ($columns as $i => $col) {
			$columns[$i] = $this->db->quoteColumnName($col);
286 287
		}
		
288 289
		return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ADD CONSTRAINT '
			. $this->db->quoteColumnName($name) . '  PRIMARY KEY ('
290
			. implode(', ', $columns). ' )';
Alexander Makarov committed
291
	}
292 293
	
	/**
294 295 296 297
	 * 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.	 *
298
	 */
299
	public function dropPrimaryKey($name, $table)
300
	{
301 302
		return 'ALTER TABLE ' . $this->db->quoteTableName($table)
			. ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name);
Alexander Makarov committed
303
	}
w  
Qiang Xue committed
304 305 306 307 308 309 310

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

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

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

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

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

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

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

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

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

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

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

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

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

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

		return $select . ' ' . $columns;
w  
Qiang Xue committed
560 561
	}

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

572 573 574
		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
575
					$tables[$i] = $this->db->quoteTableName($matches[1]) . ' ' . $this->db->quoteTableName($matches[2]);
576
				} else {
Qiang Xue committed
577
					$tables[$i] = $this->db->quoteTableName($table);
Qiang Xue committed
578 579 580 581 582 583
				}
			}
		}

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

Qiang Xue committed
586
		return 'FROM ' . $tables;
w  
Qiang Xue committed
587
	}
w  
Qiang Xue committed
588

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

w  
Qiang Xue committed
601
		foreach ($joins as $i => $join) {
Qiang Xue committed
602 603 604 605 606 607 608 609 610 611
			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
612
					}
Qiang Xue committed
613 614 615
				}
				$joins[$i] = $join[0] . ' ' . $table;
				if (isset($join[2])) {
616
					$condition = $this->buildCondition($join[2], $params);
Qiang Xue committed
617
					if ($condition !== '') {
618
						$joins[$i] .= ' ON ' . $condition;
w  
Qiang Xue committed
619 620
					}
				}
Qiang Xue committed
621 622
			} 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
623 624
			}
		}
w  
Qiang Xue committed
625

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

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

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

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

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

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

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

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

	/**
	 * 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
	 */
724
	public function buildColumns($columns)
Qiang Xue committed
725
	{
726 727 728 729 730
		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
731
			}
732 733 734 735 736
		}
		foreach ($columns as $i => $column) {
			if (is_object($column)) {
				$columns[$i] = (string)$column;
			} elseif (strpos($column, '(') === false) {
Qiang Xue committed
737
				$columns[$i] = $this->db->quoteColumnName($column);
Qiang Xue committed
738 739 740 741
			}
		}
		return is_array($columns) ? implode(', ', $columns) : $columns;
	}
742 743 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


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

	private function buildHashCondition($condition, &$params)
	{
		$parts = array();
		foreach ($condition as $column => $value) {
			if (is_array($value)) { // IN condition
791
				$parts[] = $this->buildInCondition('in', array($column, $value), $params);
792 793 794 795 796 797
			} else {
				if (strpos($column, '(') === false) {
					$column = $this->db->quoteColumnName($column);
				}
				if ($value === null) {
					$parts[] = "$column IS NULL";
798 799 800 801 802
				} elseif ($value instanceof Expression) {
					$parts[] = "$column=" . $value->expression;
					foreach ($value->params as $n => $v) {
						$params[$n] = $v;
					}
803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823
				} 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;
			}
		}
824
		if (!empty($parts)) {
825 826 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
			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
860
		if (empty($values) || $column === array()) {
861 862 863 864 865 866 867 868 869 870 871 872 873 874
			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';
875 876 877 878 879
			} elseif ($value instanceof Expression) {
				$values[$i] = $value->expression;
				foreach ($value->params as $n => $v) {
					$params[$n] = $v;
				}
880 881 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
			} 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;

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