Schema.php 8.14 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 11
use yii\db\TableSchema;
use yii\db\ColumnSchema;
Qiang Xue committed
12

w  
Qiang Xue committed
13
/**
Qiang Xue committed
14
 * Schema is the class for retrieving metadata from a MySQL database (version 4.1.x and 5.x).
w  
Qiang Xue committed
15 16
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
w  
Qiang Xue committed
17
 * @since 2.0
w  
Qiang Xue committed
18
 */
Qiang Xue committed
19
class Schema extends \yii\db\Schema
w  
Qiang Xue committed
20
{
Qiang Xue committed
21
	/**
Qiang Xue committed
22
	 * @var array mapping from physical column types (keys) to abstract column types (values)
Qiang Xue committed
23
	 */
Alexander Makarov committed
24
	public $typeMap = [
Qiang Xue committed
25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
		'tinyint' => self::TYPE_SMALLINT,
		'bit' => self::TYPE_SMALLINT,
		'smallint' => self::TYPE_SMALLINT,
		'mediumint' => self::TYPE_INTEGER,
		'int' => self::TYPE_INTEGER,
		'integer' => self::TYPE_INTEGER,
		'bigint' => self::TYPE_BIGINT,
		'float' => self::TYPE_FLOAT,
		'double' => self::TYPE_FLOAT,
		'real' => self::TYPE_FLOAT,
		'decimal' => self::TYPE_DECIMAL,
		'numeric' => self::TYPE_DECIMAL,
		'tinytext' => self::TYPE_TEXT,
		'mediumtext' => self::TYPE_TEXT,
		'longtext' => self::TYPE_TEXT,
		'text' => self::TYPE_TEXT,
		'varchar' => self::TYPE_STRING,
		'string' => self::TYPE_STRING,
		'char' => self::TYPE_STRING,
		'datetime' => self::TYPE_DATETIME,
		'year' => self::TYPE_DATE,
		'date' => self::TYPE_DATE,
		'time' => self::TYPE_TIME,
		'timestamp' => self::TYPE_TIMESTAMP,
		'enum' => self::TYPE_STRING,
Alexander Makarov committed
50
	];
Qiang Xue committed
51

w  
Qiang Xue committed
52 53
	/**
	 * Quotes a table name for use in a query.
Qiang Xue committed
54
	 * A simple table name has no schema prefix.
w  
Qiang Xue committed
55 56 57 58 59
	 * @param string $name table name
	 * @return string the properly quoted table name
	 */
	public function quoteSimpleTableName($name)
	{
w  
Qiang Xue committed
60
		return strpos($name, "`") !== false ? $name : "`" . $name . "`";
w  
Qiang Xue committed
61 62 63 64
	}

	/**
	 * Quotes a column name for use in a query.
Qiang Xue committed
65
	 * A simple column name has no prefix.
w  
Qiang Xue committed
66 67 68 69 70
	 * @param string $name column name
	 * @return string the properly quoted column name
	 */
	public function quoteSimpleColumnName($name)
	{
w  
Qiang Xue committed
71
		return strpos($name, '`') !== false || $name === '*' ? $name : '`' . $name . '`';
w  
Qiang Xue committed
72 73
	}

Qiang Xue committed
74
	/**
Qiang Xue committed
75
	 * Creates a query builder for the MySQL database.
Qiang Xue committed
76 77 78 79
	 * @return QueryBuilder query builder instance
	 */
	public function createQueryBuilder()
	{
Qiang Xue committed
80
		return new QueryBuilder($this->db);
Qiang Xue committed
81 82
	}

w  
Qiang Xue committed
83 84 85
	/**
	 * Loads the metadata for the specified table.
	 * @param string $name table name
Qiang Xue committed
86
	 * @return TableSchema driver dependent table metadata. Null if the table does not exist.
w  
Qiang Xue committed
87
	 */
w  
Qiang Xue committed
88
	protected function loadTableSchema($name)
w  
Qiang Xue committed
89
	{
w  
Qiang Xue committed
90
		$table = new TableSchema;
w  
Qiang Xue committed
91 92
		$this->resolveTableNames($table, $name);

w  
Qiang Xue committed
93
		if ($this->findColumns($table)) {
w  
Qiang Xue committed
94 95
			$this->findConstraints($table);
			return $table;
Qiang Xue committed
96 97
		} else {
			return null;
w  
Qiang Xue committed
98 99 100 101
		}
	}

	/**
Qiang Xue committed
102
	 * Resolves the table name and schema name (if any).
Qiang Xue committed
103
	 * @param TableSchema $table the table metadata object
Qiang Xue committed
104
	 * @param string $name the table name
w  
Qiang Xue committed
105 106 107 108
	 */
	protected function resolveTableNames($table, $name)
	{
		$parts = explode('.', str_replace('`', '', $name));
w  
Qiang Xue committed
109
		if (isset($parts[1])) {
w  
Qiang Xue committed
110 111
			$table->schemaName = $parts[0];
			$table->name = $parts[1];
112
			$table->fullName = $table->schemaName . '.' . $table->name;
Qiang Xue committed
113
		} else {
114
			$table->fullName = $table->name = $parts[0];
w  
Qiang Xue committed
115 116 117 118
		}
	}

	/**
Qiang Xue committed
119 120 121
	 * Loads the column information into a [[ColumnSchema]] object.
	 * @param array $info column information
	 * @return ColumnSchema the column schema object
w  
Qiang Xue committed
122
	 */
Qiang Xue committed
123
	protected function loadColumnSchema($info)
w  
Qiang Xue committed
124
	{
Qiang Xue committed
125
		$column = new ColumnSchema;
w  
Qiang Xue committed
126

Qiang Xue committed
127 128 129 130
		$column->name = $info['Field'];
		$column->allowNull = $info['Null'] === 'YES';
		$column->isPrimaryKey = strpos($info['Key'], 'PRI') !== false;
		$column->autoIncrement = stripos($info['Extra'], 'auto_increment') !== false;
Qiang Xue committed
131 132
		$column->comment = $info['Comment'];

Qiang Xue committed
133

Qiang Xue committed
134
		$column->dbType = $info['Type'];
Qiang Xue committed
135 136
		$column->unsigned = strpos($column->dbType, 'unsigned') !== false;

Qiang Xue committed
137
		$column->type = self::TYPE_STRING;
Qiang Xue committed
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 165 166 167
		if (preg_match('/^(\w+)(?:\(([^\)]+)\))?/', $column->dbType, $matches)) {
			$type = $matches[1];
			if (isset($this->typeMap[$type])) {
				$column->type = $this->typeMap[$type];
			}
			if (!empty($matches[2])) {
				if ($type === 'enum') {
					$values = explode(',', $matches[2]);
					foreach ($values as $i => $value) {
						$values[$i] = trim($value, "'");
					}
					$column->enumValues = $values;
				} else {
					$values = explode(',', $matches[2]);
					$column->size = $column->precision = (int)$values[0];
					if (isset($values[1])) {
						$column->scale = (int)$values[1];
					}
					if ($column->size === 1 && ($type === 'tinyint' || $type === 'bit')) {
						$column->type = 'boolean';
					} elseif ($type === 'bit') {
						if ($column->size > 32) {
							$column->type = 'bigint';
						} elseif ($column->size === 32) {
							$column->type = 'integer';
						}
					}
				}
			}
		}
Qiang Xue committed
168

169
		$column->phpType = $this->getColumnPhpType($column);
Qiang Xue committed
170 171 172 173 174 175

		if ($column->type !== 'timestamp' || $info['Default'] !== 'CURRENT_TIMESTAMP') {
			$column->defaultValue = $column->typecast($info['Default']);
		}

		return $column;
Qiang Xue committed
176 177
	}

w  
Qiang Xue committed
178
	/**
Qiang Xue committed
179
	 * Collects the metadata of table columns.
Qiang Xue committed
180
	 * @param TableSchema $table the table metadata
w  
Qiang Xue committed
181
	 * @return boolean whether the table exists in the database
182
	 * @throws \Exception if DB query fails
w  
Qiang Xue committed
183
	 */
w  
Qiang Xue committed
184
	protected function findColumns($table)
w  
Qiang Xue committed
185
	{
186
		$sql = 'SHOW FULL COLUMNS FROM ' . $this->quoteSimpleTableName($table->name);
187 188 189 190 191 192 193 194 195 196
		try {
			$columns = $this->db->createCommand($sql)->queryAll();
		} catch (\Exception $e) {
			$previous = $e->getPrevious();
			if ($previous instanceof \PDOException && $previous->getCode() == '42S02') {
				// table does not exist
				return false;
			}
			throw $e;
		}
Qiang Xue committed
197
		foreach ($columns as $info) {
Qiang Xue committed
198
			$column = $this->loadColumnSchema($info);
Qiang Xue committed
199 200
			$table->columns[$column->name] = $column;
			if ($column->isPrimaryKey) {
Qiang Xue committed
201
				$table->primaryKey[] = $column->name;
Qiang Xue committed
202
				if ($column->autoIncrement) {
w  
Qiang Xue committed
203 204 205 206 207
					$table->sequenceName = '';
				}
			}
		}
		return true;
w  
Qiang Xue committed
208 209 210
	}

	/**
211
	 * Gets the CREATE TABLE sql string.
Qiang Xue committed
212
	 * @param TableSchema $table the table metadata
213
	 * @return string $sql the result of 'SHOW CREATE TABLE'
w  
Qiang Xue committed
214
	 */
215
	protected function getCreateTableSql($table)
w  
Qiang Xue committed
216
	{
217
		$row = $this->db->createCommand('SHOW CREATE TABLE ' . $this->quoteSimpleTableName($table->name))->queryOne();
Qiang Xue committed
218 219 220 221 222 223
		if (isset($row['Create Table'])) {
			$sql = $row['Create Table'];
		} else {
			$row = array_values($row);
			$sql = $row[1];
		}
224 225 226 227 228 229 230 231 232 233
		return $sql;
	}

	/**
	 * Collects the foreign key column details for the given table.
	 * @param TableSchema $table the table metadata
	 */
	protected function findConstraints($table)
	{
		$sql = $this->getCreateTableSql($table);
Qiang Xue committed
234

w  
Qiang Xue committed
235
		$regexp = '/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+([^\(^\s]+)\s*\(([^\)]+)\)/mi';
Qiang Xue committed
236 237 238 239
		if (preg_match_all($regexp, $sql, $matches, PREG_SET_ORDER)) {
			foreach ($matches as $match) {
				$fks = array_map('trim', explode(',', str_replace('`', '', $match[1])));
				$pks = array_map('trim', explode(',', str_replace('`', '', $match[3])));
Alexander Makarov committed
240
				$constraint = [str_replace('`', '', $match[2])];
Qiang Xue committed
241 242
				foreach ($fks as $k => $name) {
					$constraint[$name] = $pks[$k];
w  
Qiang Xue committed
243
				}
Qiang Xue committed
244
				$table->foreignKeys[] = $constraint;
w  
Qiang Xue committed
245 246 247 248
			}
		}
	}

249 250 251 252 253 254 255 256 257 258 259 260 261 262
	/**
	 * Returns all unique indexes for the given table.
	 * Each array element is of the following structure:
	 *
	 * ~~~
	 * [
	 *	 'IndexName1' => ['col1' [, ...]],
	 *	 'IndexName2' => ['col2' [, ...]],
	 * ]
	 * ~~~
	 *
	 * @param TableSchema $table the table metadata
	 * @return array all unique indexes for the given table.
	 */
263
	public function findUniqueIndexes($table)
264 265 266 267 268 269 270
	{
		$sql = $this->getCreateTableSql($table);
		$uniqueIndexes = [];

		$regexp = '/UNIQUE KEY\s+([^\(^\s]+)\s*\(([^\)]+)\)/mi';
		if (preg_match_all($regexp, $sql, $matches, PREG_SET_ORDER)) {
			foreach ($matches as $match) {
271 272
				$indexName = str_replace('`', '', $match[1]);
				$indexColumns = array_map('trim', explode(',', str_replace('`', '', $match[2])));
273 274 275 276 277 278
				$uniqueIndexes[$indexName] = $indexColumns;
			}
		}
		return $uniqueIndexes;
	}

w  
Qiang Xue committed
279 280 281
	/**
	 * Returns all table names in the database.
	 * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
282
	 * @return array all table names in the database. The names have NO schema name prefix.
w  
Qiang Xue committed
283
	 */
284
	protected function findTableNames($schema = '')
w  
Qiang Xue committed
285
	{
Qiang Xue committed
286 287 288
		$sql = 'SHOW TABLES';
		if ($schema !== '') {
			$sql .= ' FROM ' . $this->quoteSimpleTableName($schema);
w  
Qiang Xue committed
289
		}
Qiang Xue committed
290
		return $this->db->createCommand($sql)->queryColumn();
w  
Qiang Xue committed
291 292
	}
}