Schema.php 5.19 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 129 130 131 132 133
		foreach ($keys as $key) {
			$table->foreignKeys[] = array($key['table'], $key['from'] => $key['to']);
		}
	}

	/**
Qiang Xue committed
134 135 136
	 * Loads the column information into a [[ColumnSchema]] object.
	 * @param array $info column information
	 * @return ColumnSchema the column schema object
Qiang Xue committed
137
	 */
Qiang Xue committed
138
	protected function loadColumnSchema($info)
Qiang Xue committed
139
	{
Qiang Xue committed
140 141 142 143
		$column = new ColumnSchema;
		$column->name = $info['name'];
		$column->allowNull = !$info['notnull'];
		$column->isPrimaryKey = $info['pk'] != 0;
Qiang Xue committed
144

Qiang Xue committed
145
		$column->dbType = $info['type'];
Qiang Xue committed
146 147
		$column->unsigned = strpos($column->dbType, 'unsigned') !== false;

Qiang Xue committed
148
		$column->type = self::TYPE_STRING;
Qiang Xue committed
149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171
		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';
					}
				}
			}
		}
Qiang Xue committed
172
		$column->phpType = $this->getColumnPhpType($this->type);
Qiang Xue committed
173

Qiang Xue committed
174
		$value = $info['dflt_value'];
Qiang Xue committed
175 176 177 178 179
		if ($column->type === 'string') {
			$column->defaultValue = trim($value, "'\"");
		} else {
			$column->defaultValue = $column->typecast(strcasecmp($value, 'null') ? $value : null);
		}
Qiang Xue committed
180 181

		return $column;
Qiang Xue committed
182 183
	}
}