Schema.php 10.6 KB
Newer Older
1
<?php
2

3 4 5 6 7 8 9 10 11 12 13 14
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

namespace yii\db\pgsql;

use yii\db\TableSchema;
use yii\db\ColumnSchema;

/**
Qiang Xue committed
15
 * Schema is the class for retrieving metadata from a PostgreSQL database
16
 * (version 9.x and above).
17 18 19 20
 *
 * @author Gevik Babakhani <gevikb@gmail.com>
 * @since 2.0
 */
21 22
class Schema extends \yii\db\Schema
{
23

24
	/**
25
	 * The default schema used for the current session.
Qiang Xue committed
26
	 * @var string
27
	 */
28
	public $defaultSchema = 'public';
29 30

	/**
Qiang Xue committed
31
	 * @var array mapping from physical column types (keys) to abstract
32 33
	 * column types (values)
	 */
Alexander Makarov committed
34
	public $typeMap = [
Qiang Xue committed
35 36
		'abstime' => self::TYPE_TIMESTAMP,
		'bit' => self::TYPE_STRING,
37
		'bool' => self::TYPE_BOOLEAN,
Qiang Xue committed
38 39 40 41 42 43 44 45 46
		'boolean' => self::TYPE_BOOLEAN,
		'box' => self::TYPE_STRING,
		'character' => self::TYPE_STRING,
		'bytea' => self::TYPE_BINARY,
		'char' => self::TYPE_STRING,
		'cidr' => self::TYPE_STRING,
		'circle' => self::TYPE_STRING,
		'date' => self::TYPE_DATE,
		'real' => self::TYPE_FLOAT,
47
		'decimal' => self::TYPE_DECIMAL,
Qiang Xue committed
48 49 50
		'double precision' => self::TYPE_DECIMAL,
		'inet' => self::TYPE_STRING,
		'smallint' => self::TYPE_SMALLINT,
51
		'int4' => self::TYPE_INTEGER,
Qiang Xue committed
52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74
		'integer' => self::TYPE_INTEGER,
		'bigint' => self::TYPE_BIGINT,
		'interval' => self::TYPE_STRING,
		'json' => self::TYPE_STRING,
		'line' => self::TYPE_STRING,
		'macaddr' => self::TYPE_STRING,
		'money' => self::TYPE_MONEY,
		'name' => self::TYPE_STRING,
		'numeric' => self::TYPE_STRING,
		'oid' => self::TYPE_BIGINT, // should not be used. it's pg internal!
		'path' => self::TYPE_STRING,
		'point' => self::TYPE_STRING,
		'polygon' => self::TYPE_STRING,
		'text' => self::TYPE_TEXT,
		'time without time zone' => self::TYPE_TIME,
		'timestamp without time zone' => self::TYPE_TIMESTAMP,
		'timestamp with time zone' => self::TYPE_TIMESTAMP,
		'time with time zone' => self::TYPE_TIMESTAMP,
		'unknown' => self::TYPE_STRING,
		'uuid' => self::TYPE_STRING,
		'bit varying' => self::TYPE_STRING,
		'character varying' => self::TYPE_STRING,
		'xml' => self::TYPE_STRING
Alexander Makarov committed
75
	];
76

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

86 87 88 89 90
	/**
	 * Resolves the table name and schema name (if any).
	 * @param TableSchema $table the table metadata object
	 * @param string $name the table name
	 */
Qiang Xue committed
91 92
	protected function resolveTableNames($table, $name)
	{
93
		$parts = explode('.', str_replace('"', '', $name));
94
		if (isset($parts[1])) {
95 96
			$table->schemaName = $parts[0];
			$table->name = $parts[1];
97
		} else {
98 99
			$table->name = $parts[0];
		}
100
		if ($table->schemaName === null) {
101
			$table->schemaName = $this->defaultSchema;
102
		}
103
	}
104

105 106 107 108 109 110
	/**
	 * Quotes a table name for use in a query.
	 * A simple table name has no schema prefix.
	 * @param string $name table name
	 * @return string the properly quoted table name
	 */
Qiang Xue committed
111 112
	public function quoteSimpleTableName($name)
	{
113 114 115
		return strpos($name, '"') !== false ? $name : '"' . $name . '"';
	}

116 117 118 119 120
	/**
	 * Loads the metadata for the specified table.
	 * @param string $name table name
	 * @return TableSchema|null driver dependent table metadata. Null if the table does not exist.
	 */
Qiang Xue committed
121 122
	public function loadTableSchema($name)
	{
123 124
		$table = new TableSchema();
		$this->resolveTableNames($table, $name);
125
		if ($this->findColumns($table)) {
126
			$this->findConstraints($table);
127
			return $table;
gevik committed
128 129
		} else {
			return null;
130
		}
131
	}
132

133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154
	/**
	 * Determines the PDO type for the given PHP data value.
	 * @param mixed $data the data whose PDO type is to be determined
	 * @return integer the PDO type
	 * @see http://www.php.net/manual/en/pdo.constants.php
	 */
	public function getPdoType($data)
	{
		// php type => PDO type
		static $typeMap = [
			// https://github.com/yiisoft/yii2/issues/1115
			// Cast boolean to integer values to work around problems with PDO casting false to string '' https://bugs.php.net/bug.php?id=33876
			'boolean' => \PDO::PARAM_INT,
			'integer' => \PDO::PARAM_INT,
			'string' => \PDO::PARAM_STR,
			'resource' => \PDO::PARAM_LOB,
			'NULL' => \PDO::PARAM_NULL,
		];
		$type = gettype($data);
		return isset($typeMap[$type]) ? $typeMap[$type] : \PDO::PARAM_STR;
	}

155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172
	/**
	 * 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 = '')
	{
		if ($schema === '') {
			$schema = $this->defaultSchema;
		}
		$sql = <<<EOD
SELECT table_name, table_schema FROM information_schema.tables
WHERE table_schema=:schema AND table_type='BASE TABLE'
EOD;
		$command = $this->db->createCommand($sql);
		$command->bindParam(':schema', $schema);
		$rows = $command->queryAll();
Alexander Makarov committed
173
		$names = [];
174 175 176 177 178 179 180 181 182 183
		foreach ($rows as $row) {
			if ($schema === $this->defaultSchema) {
				$names[] = $row['table_name'];
			} else {
				$names[] = $row['table_schema'] . '.' . $row['table_name'];
			}
		}
		return $names;
	}

184 185 186 187
	/**
	 * Collects the foreign key column details for the given table.
	 * @param TableSchema $table the table metadata
	 */
Qiang Xue committed
188 189
	protected function findConstraints($table)
	{
190

191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215
		$tableName = $this->quoteValue($table->name);
		$tableSchema = $this->quoteValue($table->schemaName);

		//We need to extract the constraints de hard way since:
		//http://www.postgresql.org/message-id/26677.1086673982@sss.pgh.pa.us

		$sql = <<<SQL
select 
	(select string_agg(attname,',') attname from pg_attribute where attrelid=ct.conrelid and attnum = any(ct.conkey)) as columns,
	fc.relname as foreign_table_name,
	fns.nspname as foreign_table_schema,
	(select string_agg(attname,',') attname from pg_attribute where attrelid=ct.confrelid and attnum = any(ct.confkey)) as foreign_columns
from
	pg_constraint ct 
	inner join pg_class c on c.oid=ct.conrelid
	inner join pg_namespace ns on c.relnamespace=ns.oid
	left join pg_class fc on fc.oid=ct.confrelid
	left join pg_namespace fns on fc.relnamespace=fns.oid
	
where
	ct.contype='f'
	and c.relname={$tableName}
	and ns.nspname={$tableSchema}
SQL;

216
		$constraints = $this->db->createCommand($sql)->queryAll();
217
		foreach ($constraints as $constraint) {
218 219
			$columns = explode(',', $constraint['columns']);
			$fcolumns = explode(',', $constraint['foreign_columns']);
220
			if ($constraint['foreign_table_schema'] !== $this->defaultSchema) {
Qiang Xue committed
221
				$foreignTable = $constraint['foreign_table_schema'] . '.' . $constraint['foreign_table_name'];
222
			} else {
Qiang Xue committed
223
				$foreignTable = $constraint['foreign_table_name'];
224
			}
Alexander Makarov committed
225
			$citem = [$foreignTable];
226
			foreach ($columns as $idx => $column) {
227
				$citem[$fcolumns[$idx]] = $column;
228 229
			}
			$table->foreignKeys[] = $citem;
230 231 232
		}
	}

233 234 235 236 237
	/**
	 * Collects the metadata of table columns.
	 * @param TableSchema $table the table metadata
	 * @return boolean whether the table exists in the database
	 */
Qiang Xue committed
238 239
	protected function findColumns($table)
	{
240 241 242 243 244 245 246 247 248 249 250 251 252 253 254
		$tableName = $this->db->quoteValue($table->name);
		$schemaName = $this->db->quoteValue($table->schemaName);
		$sql = <<<SQL
SELECT 
	current_database() as table_catalog,
	d.nspname AS table_schema,        
        c.relname AS table_name,
        a.attname AS column_name,
        t.typname AS data_type,
        a.attlen AS character_maximum_length,
        pg_catalog.col_description(c.oid, a.attnum) AS column_comment,
        a.atttypmod AS modifier,
        a.attnotnull = false AS is_nullable,	
        CAST(pg_get_expr(ad.adbin, ad.adrelid) AS varchar) AS column_default,
        coalesce(pg_get_expr(ad.adbin, ad.adrelid) ~ 'nextval',false) AS is_autoinc,
255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281
        array_to_string((select array_agg(enumlabel) from pg_enum where enumtypid=a.atttypid)::varchar[],',') as enum_values,
	CASE atttypid
		 WHEN 21 /*int2*/ THEN 16
		 WHEN 23 /*int4*/ THEN 32
		 WHEN 20 /*int8*/ THEN 64
		 WHEN 1700 /*numeric*/ THEN
		      CASE WHEN atttypmod = -1
			   THEN null
			   ELSE ((atttypmod - 4) >> 16) & 65535
			   END
		 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
		 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
		 ELSE null
	  END   AS numeric_precision,
	  CASE 
	    WHEN atttypid IN (21, 23, 20) THEN 0
	    WHEN atttypid IN (1700) THEN            
		CASE 
		    WHEN atttypmod = -1 THEN null       
		    ELSE (atttypmod - 4) & 65535
		END
	       ELSE null
	  END AS numeric_scale,
	CAST(
             information_schema._pg_char_max_length(information_schema._pg_truetypid(a, t), information_schema._pg_truetypmod(a, t))
             AS numeric
	) AS size,
282
	a.attnum = any (ct.conkey) as is_pkey
283 284 285 286 287
FROM
	pg_class c
	LEFT JOIN pg_attribute a ON a.attrelid = c.oid
	LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
	LEFT JOIN pg_type t ON a.atttypid = t.oid
288 289
	LEFT JOIN pg_namespace d ON d.oid = c.relnamespace
	LEFT join pg_constraint ct on ct.conrelid=c.oid and ct.contype='p'
290 291 292 293 294 295 296 297
WHERE
	a.attnum > 0
	and c.relname = {$tableName}
	and d.nspname = {$schemaName}
ORDER BY
	a.attnum;
SQL;

gevik committed
298
		$columns = $this->db->createCommand($sql)->queryAll();
gsd committed
299 300 301
		if (empty($columns)) {
			return false;
		}
302 303
		foreach ($columns as $column) {
			$column = $this->loadColumnSchema($column);
304
			$table->columns[$column->name] = $column;
305
			if ($column->isPrimaryKey === true) {
306
				$table->primaryKey[] = $column->name;
307
				if ($table->sequenceName === null && preg_match("/nextval\\('\"?\\w+\"?'(::regclass)?\\)/", $column->defaultValue) === 1) {
Alexander Makarov committed
308
					$table->sequenceName = preg_replace(['/nextval/', '/::/', '/regclass/', '/\'\)/', '/\(\'/'], '', $column->defaultValue);
309 310
				}
			}
311
		}
312
		return true;
313 314 315 316 317 318 319
	}

	/**
	 * Loads the column information into a [[ColumnSchema]] object.
	 * @param array $info column information
	 * @return ColumnSchema the column schema object
	 */
Qiang Xue committed
320 321
	protected function loadColumnSchema($info)
	{
322 323 324 325 326 327
		$column = new ColumnSchema();
		$column->allowNull = $info['is_nullable'];
		$column->autoIncrement = $info['is_autoinc'];
		$column->comment = $info['column_comment'];
		$column->dbType = $info['data_type'];
		$column->defaultValue = $info['column_default'];
Alexander Makarov committed
328
		$column->enumValues = explode(',', str_replace(["''"], ["'"], $info['enum_values']));
329
		$column->unsigned = false; // has no meaning in PG
330
		$column->isPrimaryKey = $info['is_pkey'];
331
		$column->name = $info['column_name'];
332 333 334 335
		$column->precision = $info['numeric_precision'];
		$column->scale = $info['numeric_scale'];
		$column->size = $info['size'];

336
		if (isset($this->typeMap[$column->dbType])) {
337
			$column->type = $this->typeMap[$column->dbType];
338
		} else {
339 340 341
			$column->type = self::TYPE_STRING;
		}
		$column->phpType = $this->getColumnPhpType($column);
342 343
		return $column;
	}
gevik committed
344
}