Schema.php 5.36 KB
Newer Older
Qiang Xue committed
1 2 3
<?php
/**
 * @link http://www.yiiframework.com/
Qiang Xue committed
4
 * @copyright Copyright (c) 2008 Yii Software LLC
Qiang Xue committed
5 6 7
 * @license http://www.yiiframework.com/license/
 */

Qiang Xue committed
8
namespace yii\db\sqlite;
Qiang Xue committed
9

Qiang Xue committed
10 11
use yii\db\TableSchema;
use yii\db\ColumnSchema;
Qiang Xue committed
12 13

/**
Qiang Xue committed
14
 * Schema is the class for retrieving metadata from a SQLite (2/3) database.
Qiang Xue committed
15 16 17 18
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @since 2.0
 */
Qiang Xue committed
19
class Schema extends \yii\db\Schema
Qiang Xue committed
20 21 22 23
{
	/**
	 * @var array mapping from physical column types (keys) to abstract column types (values)
	 */
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 52 53 54 55 56 57 58

	/**
	 * Creates a query builder for the MySQL database.
	 * This method may be overridden by child classes to create a DBMS-specific query builder.
	 * @return QueryBuilder query builder instance
	 */
	public function createQueryBuilder()
	{
Qiang Xue committed
59
		return new QueryBuilder($this->db);
Qiang Xue committed
60 61 62 63 64 65 66 67 68 69 70
	}

	/**
	 * 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.
	 * If not empty, the returned table names will be prefixed with the schema name.
	 * @return array all table names in the database.
	 */
	protected function findTableNames($schema = '')
	{
		$sql = "SELECT DISTINCT tbl_name FROM sqlite_master WHERE tbl_name<>'sqlite_sequence'";
Qiang Xue committed
71
		return $this->db->createCommand($sql)->queryColumn();
Qiang Xue committed
72 73 74 75 76
	}

	/**
	 * Loads the metadata for the specified table.
	 * @param string $name table name
Qiang Xue committed
77
	 * @return TableSchema driver dependent table metadata. Null if the table does not exist.
Qiang Xue committed
78 79 80 81 82 83 84 85 86
	 */
	protected function loadTableSchema($name)
	{
		$table = new TableSchema;
		$table->name = $name;

		if ($this->findColumns($table)) {
			$this->findConstraints($table);
			return $table;
Qiang Xue committed
87 88
		} else {
			return null;
Qiang Xue committed
89 90 91 92 93
		}
	}

	/**
	 * Collects the table column metadata.
Qiang Xue committed
94
	 * @param TableSchema $table the table metadata
Qiang Xue committed
95 96 97 98
	 * @return boolean whether the table exists in the database
	 */
	protected function findColumns($table)
	{
Qiang Xue committed
99
		$sql = "PRAGMA table_info(" . $this->quoteSimpleTableName($table->name) . ')';
Qiang Xue committed
100
		$columns = $this->db->createCommand($sql)->queryAll();
Qiang Xue committed
101 102 103 104
		if (empty($columns)) {
			return false;
		}

Qiang Xue committed
105 106
		foreach ($columns as $info) {
			$column = $this->loadColumnSchema($info);
Qiang Xue committed
107 108
			$table->columns[$column->name] = $column;
			if ($column->isPrimaryKey) {
Qiang Xue committed
109
				$table->primaryKey[] = $column->name;
Qiang Xue committed
110 111
			}
		}
Qiang Xue committed
112
		if (count($table->primaryKey) === 1 && !strncasecmp($table->columns[$table->primaryKey[0]]->dbType, 'int', 3)) {
Qiang Xue committed
113
			$table->sequenceName = '';
Qiang Xue committed
114
			$table->columns[$table->primaryKey[0]]->autoIncrement = true;
Qiang Xue committed
115 116 117 118 119 120 121
		}

		return true;
	}

	/**
	 * Collects the foreign key column details for the given table.
Qiang Xue committed
122
	 * @param TableSchema $table the table metadata
Qiang Xue committed
123 124 125
	 */
	protected function findConstraints($table)
	{
Qiang Xue committed
126
		$sql = "PRAGMA foreign_key_list(" . $this->quoteSimpleTableName($table->name) . ')';
Qiang Xue committed
127
		$keys = $this->db->createCommand($sql)->queryAll();
Qiang Xue committed
128
		foreach ($keys as $key) {
129 130
			$id = (int)$key['id'];
			if (!isset($table->foreignKeys[$id])) {
Alexander Makarov committed
131
				$table->foreignKeys[$id] = [$key['table'], $key['from'] => $key['to']];
132 133 134 135
			} else {
				// composite FK
				$table->foreignKeys[$id][$key['from']] = $key['to'];
			}
Qiang Xue committed
136 137 138 139
		}
	}

	/**
Qiang Xue committed
140 141 142
	 * Loads the column information into a [[ColumnSchema]] object.
	 * @param array $info column information
	 * @return ColumnSchema the column schema object
Qiang Xue committed
143
	 */
Qiang Xue committed
144
	protected function loadColumnSchema($info)
Qiang Xue committed
145
	{
Qiang Xue committed
146 147 148 149
		$column = new ColumnSchema;
		$column->name = $info['name'];
		$column->allowNull = !$info['notnull'];
		$column->isPrimaryKey = $info['pk'] != 0;
Qiang Xue committed
150

Qiang Xue committed
151
		$column->dbType = $info['type'];
Qiang Xue committed
152 153
		$column->unsigned = strpos($column->dbType, 'unsigned') !== false;

Qiang Xue committed
154
		$column->type = self::TYPE_STRING;
Qiang Xue committed
155
		if (preg_match('/^(\w+)(?:\(([^\)]+)\))?/', $column->dbType, $matches)) {
156
			$type = strtolower($matches[1]);
Qiang Xue committed
157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177
			if (isset($this->typeMap[$type])) {
				$column->type = $this->typeMap[$type];
			}

			if (!empty($matches[2])) {
				$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';
					}
				}
			}
		}
178
		$column->phpType = $this->getColumnPhpType($column);
Qiang Xue committed
179

Qiang Xue committed
180
		$value = $info['dflt_value'];
Qiang Xue committed
181 182 183 184 185
		if ($column->type === 'string') {
			$column->defaultValue = trim($value, "'\"");
		} else {
			$column->defaultValue = $column->typecast(strcasecmp($value, 'null') ? $value : null);
		}
Qiang Xue committed
186 187

		return $column;
Qiang Xue committed
188 189
	}
}