QueryBuilder.php 5.77 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\mysql;
w  
Qiang Xue committed
9

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

w  
Qiang Xue committed
13
/**
Qiang Xue committed
14
 * QueryBuilder is the query builder for MySQL databases.
w  
Qiang Xue committed
15 16 17 18
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @since 2.0
 */
Qiang Xue committed
19
class QueryBuilder extends \yii\db\QueryBuilder
w  
Qiang Xue committed
20 21
{
	/**
Qiang Xue committed
22
	 * @var array mapping from abstract column types (keys) to physical column types (values).
w  
Qiang Xue committed
23
	 */
Qiang Xue committed
24
	public $typeMap = array(
Qiang Xue committed
25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
		Schema::TYPE_PK => 'int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY',
		Schema::TYPE_STRING => 'varchar(255)',
		Schema::TYPE_TEXT => 'text',
		Schema::TYPE_SMALLINT => 'smallint(6)',
		Schema::TYPE_INTEGER => 'int(11)',
		Schema::TYPE_BIGINT => 'bigint(20)',
		Schema::TYPE_FLOAT => 'float',
		Schema::TYPE_DECIMAL => 'decimal',
		Schema::TYPE_DATETIME => 'datetime',
		Schema::TYPE_TIMESTAMP => 'timestamp',
		Schema::TYPE_TIME => 'time',
		Schema::TYPE_DATE => 'date',
		Schema::TYPE_BINARY => 'blob',
		Schema::TYPE_BOOLEAN => 'tinyint(1)',
		Schema::TYPE_MONEY => 'decimal(19,4)',
Qiang Xue committed
40
	);
w  
Qiang Xue committed
41 42 43 44

	/**
	 * 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
45
	 * @param string $oldName the old name of the column. The name will be properly quoted by the method.
w  
Qiang Xue committed
46 47
	 * @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.
48
	 * @throws Exception
w  
Qiang Xue committed
49
	 */
Qiang Xue committed
50
	public function renameColumn($table, $oldName, $newName)
w  
Qiang Xue committed
51
	{
Qiang Xue committed
52 53
		$quotedTable = $this->db->quoteTableName($table);
		$row = $this->db->createCommand('SHOW CREATE TABLE ' . $quotedTable)->queryRow();
Qiang Xue committed
54
		if ($row === false) {
55
			throw new Exception("Unable to find column '$oldName' in table '$table'.");
Qiang Xue committed
56
		}
w  
Qiang Xue committed
57
		if (isset($row['Create Table'])) {
w  
Qiang Xue committed
58
			$sql = $row['Create Table'];
Qiang Xue committed
59
		} else {
w  
Qiang Xue committed
60 61 62
			$row = array_values($row);
			$sql = $row[1];
		}
w  
Qiang Xue committed
63 64
		if (preg_match_all('/^\s*`(.*?)`\s+(.*?),?$/m', $sql, $matches)) {
			foreach ($matches[1] as $i => $c) {
Qiang Xue committed
65
				if ($c === $oldName) {
Qiang Xue committed
66
					return "ALTER TABLE $quotedTable CHANGE "
Qiang Xue committed
67 68
						. $this->db->quoteColumnName($oldName) . ' '
						. $this->db->quoteColumnName($newName) . ' '
Qiang Xue committed
69
						. $matches[2][$i];
w  
Qiang Xue committed
70 71 72 73
				}
			}
		}
		// try to give back a SQL anyway
Qiang Xue committed
74
		return "ALTER TABLE $quotedTable CHANGE "
Qiang Xue committed
75 76
			. $this->db->quoteColumnName($oldName) . ' '
			. $this->db->quoteColumnName($newName);
w  
Qiang Xue committed
77 78 79 80 81 82 83 84 85 86
	}

	/**
	 * 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
87 88
		return 'ALTER TABLE ' . $this->db->quoteTableName($table)
			. ' DROP FOREIGN KEY ' . $this->db->quoteColumnName($name);
w  
Qiang Xue committed
89
	}
Qiang Xue committed
90 91 92 93 94 95 96 97 98

	/**
	 * Creates a SQL statement for resetting the sequence value of a table's primary key.
	 * The sequence will be reset such that the primary key of the next new row inserted
	 * will have the specified value or 1.
	 * @param string $tableName the name of the table whose primary key sequence will be reset
	 * @param mixed $value the value for the primary key of the next new row inserted. If this is not set,
	 * the next new row's primary key will have a value 1.
	 * @return string the SQL statement for resetting sequence
Qiang Xue committed
99
	 * @throws InvalidParamException if the table does not exist or there is no sequence associated with the table.
Qiang Xue committed
100 101 102
	 */
	public function resetSequence($tableName, $value = null)
	{
Qiang Xue committed
103
		$table = $this->db->getTableSchema($tableName);
Qiang Xue committed
104
		if ($table !== null && $table->sequenceName !== null) {
Qiang Xue committed
105
			$tableName = $this->db->quoteTableName($tableName);
Qiang Xue committed
106 107
			if ($value === null) {
				$key = reset($table->primaryKey);
Qiang Xue committed
108
				$value = $this->db->createCommand("SELECT MAX(`$key`) FROM $tableName")->queryScalar() + 1;
Qiang Xue committed
109 110 111 112 113
			} else {
				$value = (int)$value;
			}
			return "ALTER TABLE $tableName AUTO_INCREMENT=$value";
		} elseif ($table === null) {
Qiang Xue committed
114
			throw new InvalidParamException("Table not found: $tableName");
Qiang Xue committed
115
		} else {
Qiang Xue committed
116
			throw new InvalidParamException("There is not sequence associated with table '$tableName'.'");
Qiang Xue committed
117 118 119 120 121 122 123 124 125 126 127 128 129
		}
	}

	/**
	 * Builds a SQL statement for enabling or disabling integrity check.
	 * @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.
	 * @return string the SQL statement for checking integrity
	 */
	public function checkIntegrity($check = true, $schema = '')
	{
		return 'SET FOREIGN_KEY_CHECKS=' . ($check ? 1 : 0);
	}
Qiang Xue committed
130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164

	/**
	 * 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
	 */
	public function batchInsert($table, $columns, $rows)
	{
		$values = array();
		foreach ($rows as $row) {
			$vs = array();
			foreach ($row as $value) {
				$vs[] = is_string($value) ? $this->db->quoteValue($value) : $value;
			}
			$values[] = $vs;
		}

		return 'INSERT INTO ' . $this->db->quoteTableName($table)
			. ' (' . implode(', ', $columns) . ') VALUES ('
			. implode(', ', $values) . ')';
	}
w  
Qiang Xue committed
165
}