QueryBuilder.php 4.89 KB
Newer Older
w  
Qiang Xue committed
1 2 3 4 5
<?php
/**
 * QueryBuilder class file.
 *
 * @link http://www.yiiframework.com/
Qiang Xue committed
6
 * @copyright Copyright &copy; 2008 Yii Software LLC
w  
Qiang Xue committed
7 8 9
 * @license http://www.yiiframework.com/license/
 */

Qiang Xue committed
10
namespace yii\db\mysql;
w  
Qiang Xue committed
11

Qiang Xue committed
12
use yii\db\Exception;
Qiang Xue committed
13
use yii\base\InvalidCallException;
Qiang Xue committed
14

w  
Qiang Xue committed
15
/**
Qiang Xue committed
16
 * QueryBuilder is the query builder for MySQL databases.
w  
Qiang Xue committed
17 18 19 20
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @since 2.0
 */
Qiang Xue committed
21
class QueryBuilder extends \yii\db\QueryBuilder
w  
Qiang Xue committed
22 23
{
	/**
Qiang Xue committed
24
	 * @var array mapping from abstract column types (keys) to physical column types (values).
w  
Qiang Xue committed
25
	 */
Qiang Xue committed
26
	public $typeMap = array(
Qiang Xue committed
27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
		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
42
	);
w  
Qiang Xue committed
43 44 45 46

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

	/**
	 * 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)
	{
89 90
		return 'ALTER TABLE ' . $this->connection->quoteTableName($table)
			. ' DROP FOREIGN KEY ' . $this->connection->quoteColumnName($name);
w  
Qiang Xue committed
91
	}
Qiang Xue committed
92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131

	/**
	 * 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
	 * @throws InvalidCallException if the table does not exist or there is no sequence associated with the table.
	 */
	public function resetSequence($tableName, $value = null)
	{
		$table = $this->connection->getTableSchema($tableName);
		if ($table !== null && $table->sequenceName !== null) {
			$tableName = $this->connection->quoteTableName($tableName);
			if ($value === null) {
				$key = reset($table->primaryKey);
				$value = $this->connection->createCommand("SELECT MAX(`$key`) FROM $tableName")->queryScalar() + 1;
			} else {
				$value = (int)$value;
			}
			return "ALTER TABLE $tableName AUTO_INCREMENT=$value";
		} elseif ($table === null) {
			throw new InvalidCallException("Table not found: $tableName");
		} else {
			throw new InvalidCallException("There is not sequence associated with table '$tableName'.'");
		}
	}

	/**
	 * 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);
	}
w  
Qiang Xue committed
132
}