Schema.php 5.35 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 24 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 50 51 52 53 54 55 56 57 58
{
	/**
	 * @var array mapping from physical column types (keys) to abstract column types (values)
	 */
	public $typeMap = array(
		'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,
	);

	/**
	 * 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 131 132 133 134 135
			$id = (int)$key['id'];
			if (!isset($table->foreignKeys[$id])) {
				$table->foreignKeys[$id] = array($key['table'], $key['from'] => $key['to']);
			} 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 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177
		if (preg_match('/^(\w+)(?:\(([^\)]+)\))?/', $column->dbType, $matches)) {
			$type = $matches[1];
			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
	}
}