Schema.php 6.99 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];
Qiang Xue committed
112
		} else {
w  
Qiang Xue committed
113 114 115 116 117
			$table->name = $parts[0];
		}
	}

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

Qiang Xue committed
126 127 128 129
		$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
130 131
		$column->comment = $info['Comment'];

Qiang Xue committed
132

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

Qiang Xue committed
136
		$column->type = self::TYPE_STRING;
Qiang Xue committed
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 165 166
		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
167

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

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

		return $column;
Qiang Xue committed
175 176
	}

w  
Qiang Xue committed
177
	/**
Qiang Xue committed
178
	 * Collects the metadata of table columns.
Qiang Xue committed
179
	 * @param TableSchema $table the table metadata
w  
Qiang Xue committed
180
	 * @return boolean whether the table exists in the database
181
	 * @throws \Exception if DB query fails
w  
Qiang Xue committed
182
	 */
w  
Qiang Xue committed
183
	protected function findColumns($table)
w  
Qiang Xue committed
184
	{
185
		$sql = 'SHOW FULL COLUMNS FROM ' . $this->quoteSimpleTableName($table->name);
186 187 188 189 190 191 192 193 194 195
		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
196
		foreach ($columns as $info) {
Qiang Xue committed
197
			$column = $this->loadColumnSchema($info);
Qiang Xue committed
198 199
			$table->columns[$column->name] = $column;
			if ($column->isPrimaryKey) {
Qiang Xue committed
200
				$table->primaryKey[] = $column->name;
Qiang Xue committed
201
				if ($column->autoIncrement) {
w  
Qiang Xue committed
202 203 204 205 206
					$table->sequenceName = '';
				}
			}
		}
		return true;
w  
Qiang Xue committed
207 208 209 210
	}

	/**
	 * Collects the foreign key column details for the given table.
Qiang Xue committed
211
	 * @param TableSchema $table the table metadata
w  
Qiang Xue committed
212 213 214
	 */
	protected function findConstraints($table)
	{
215
		$row = $this->db->createCommand('SHOW CREATE TABLE ' . $this->quoteSimpleTableName($table->name))->queryOne();
Qiang Xue committed
216 217 218 219 220 221 222
		if (isset($row['Create Table'])) {
			$sql = $row['Create Table'];
		} else {
			$row = array_values($row);
			$sql = $row[1];
		}

w  
Qiang Xue committed
223
		$regexp = '/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+([^\(^\s]+)\s*\(([^\)]+)\)/mi';
Qiang Xue committed
224 225 226 227
		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
228
				$constraint = [str_replace('`', '', $match[2])];
Qiang Xue committed
229 230
				foreach ($fks as $k => $name) {
					$constraint[$name] = $pks[$k];
w  
Qiang Xue committed
231
				}
Qiang Xue committed
232
				$table->foreignKeys[] = $constraint;
w  
Qiang Xue committed
233 234 235 236 237 238 239
			}
		}
	}

	/**
	 * 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.
240
	 * @return array all table names in the database. The names have NO schema name prefix.
w  
Qiang Xue committed
241 242 243
	 */
	protected function findTableNames($schema = '')
	{
Qiang Xue committed
244 245 246
		$sql = 'SHOW TABLES';
		if ($schema !== '') {
			$sql .= ' FROM ' . $this->quoteSimpleTableName($schema);
w  
Qiang Xue committed
247
		}
Qiang Xue committed
248
		return $this->db->createCommand($sql)->queryColumn();
w  
Qiang Xue committed
249 250
	}
}