Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 425 lines (398 sloc) 13.575 kb
55bdd95 removed carriage returns from files.
qiang.xue authored
1 <?php
2 /**
3 * CPgsqlSchema class file.
4 *
5 * @author Qiang Xue <qiang.xue@gmail.com>
6 * @link http://www.yiiframework.com/
4be0af6 changed copyright year.
qiang.xue authored
7 * @copyright Copyright &copy; 2008-2011 Yii Software LLC
55bdd95 removed carriage returns from files.
qiang.xue authored
8 * @license http://www.yiiframework.com/license/
9 */
10
11 /**
12 * CPgsqlSchema is the class for retrieving metadata information from a PostgreSQL database.
13 *
14 * @author Qiang Xue <qiang.xue@gmail.com>
15 * @version $Id$
16 * @package system.db.schema.pgsql
17 * @since 1.0
18 */
19 class CPgsqlSchema extends CDbSchema
20 {
21 const DEFAULT_SCHEMA='public';
3d984fb * Added query builder methods for building database schema manipulation ...
qiang.xue authored
22
23 /**
24 * @var array the abstract column types mapped to physical column types.
25 * @since 1.1.6
26 */
27 public $columnTypes=array(
28 'pk' => 'serial NOT NULL PRIMARY KEY',
29 'string' => 'character varying (255)',
30 'text' => 'text',
31 'integer' => 'integer',
32 'float' => 'double precision',
33 'decimal' => 'numeric',
f3b41a9 Carsten Brandt Fixed datetime column type for postgresql
cebe authored
34 'datetime' => 'timestamp',
3d984fb * Added query builder methods for building database schema manipulation ...
qiang.xue authored
35 'timestamp' => 'timestamp',
36 'time' => 'time',
37 'date' => 'date',
38 'binary' => 'bytea',
39 'boolean' => 'boolean',
28c370f (Fixes issue 2493)
qiang.xue authored
40 'money' => 'decimal(19,4)',
3d984fb * Added query builder methods for building database schema manipulation ...
qiang.xue authored
41 );
42
55bdd95 removed carriage returns from files.
qiang.xue authored
43 private $_sequences=array();
44
45 /**
46 * Quotes a table name for use in a query.
3325e62 CDbConnection::quoteColumnName and quoteTableName will properly quote ta...
qiang.xue authored
47 * A simple table name does not schema prefix.
72b9745 Updated PHPDoc @param parameter names
mdomba authored
48 * @param string $name table name
55bdd95 removed carriage returns from files.
qiang.xue authored
49 * @return string the properly quoted table name
3325e62 CDbConnection::quoteColumnName and quoteTableName will properly quote ta...
qiang.xue authored
50 * @since 1.1.6
55bdd95 removed carriage returns from files.
qiang.xue authored
51 */
3325e62 CDbConnection::quoteColumnName and quoteTableName will properly quote ta...
qiang.xue authored
52 public function quoteSimpleTableName($name)
55bdd95 removed carriage returns from files.
qiang.xue authored
53 {
54 return '"'.$name.'"';
55 }
56
57 /**
7c9033c * Added CDbSchema::resetSequence() and checkIntegrity()
qiang.xue authored
58 * Resets the sequence value of a table's primary key.
59 * The sequence will be reset such that the primary key of the next new row inserted
60 * will have the specified value or 1.
72b9745 Updated PHPDoc @param parameter names
mdomba authored
61 * @param CDbTableSchema $table the table schema whose primary key sequence will be reset
62 * @param mixed $value the value for the primary key of the next new row inserted. If this is not set,
7c9033c * Added CDbSchema::resetSequence() and checkIntegrity()
qiang.xue authored
63 * the next new row's primary key will have a value 1.
64 * @since 1.1
65 */
66 public function resetSequence($table,$value=null)
67 {
68 if($table->sequenceName!==null)
69 {
70 $seq='"'.$table->sequenceName.'"';
71 if(strpos($seq,'.')!==false)
72 $seq=str_replace('.','"."',$seq);
73 if($value===null)
74 $value="(SELECT COALESCE(MAX(\"{$table->primaryKey}\"),0) FROM {$table->rawName}) + 1";
75 else
76 $value=(int)$value;
77 $this->getDbConnection()->createCommand("SELECT SETVAL('$seq', $value, false)")->execute();
78 }
79 }
80
81 /**
82 * Enables or disables integrity check.
72b9745 Updated PHPDoc @param parameter names
mdomba authored
83 * @param boolean $check whether to turn on or off the integrity check.
84 * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
7c9033c * Added CDbSchema::resetSequence() and checkIntegrity()
qiang.xue authored
85 * @since 1.1
86 */
87 public function checkIntegrity($check=true,$schema='')
88 {
89 $enable=$check ? 'ENABLE' : 'DISABLE';
90 $tableNames=$this->getTableNames($schema);
91 $db=$this->getDbConnection();
92 foreach($tableNames as $tableName)
93 {
94 $tableName='"'.$tableName.'"';
95 if(strpos($tableName,'.')!==false)
96 $tableName=str_replace('.','"."',$tableName);
97 $db->createCommand("ALTER TABLE $tableName $enable TRIGGER ALL")->execute();
98 }
99 }
100
101 /**
3325e62 CDbConnection::quoteColumnName and quoteTableName will properly quote ta...
qiang.xue authored
102 * Loads the metadata for the specified table.
72b9745 Updated PHPDoc @param parameter names
mdomba authored
103 * @param string $name table name
55bdd95 removed carriage returns from files.
qiang.xue authored
104 * @return CDbTableSchema driver dependent table metadata.
105 */
3325e62 CDbConnection::quoteColumnName and quoteTableName will properly quote ta...
qiang.xue authored
106 protected function loadTable($name)
55bdd95 removed carriage returns from files.
qiang.xue authored
107 {
108 $table=new CPgsqlTableSchema;
7c16ad7 merge from 1.0
qiang.xue authored
109 $this->resolveTableNames($table,$name);
55bdd95 removed carriage returns from files.
qiang.xue authored
110 if(!$this->findColumns($table))
111 return null;
112 $this->findConstraints($table);
113
d2b5331 (Fixes issue 1809)
qiang.xue authored
114 if(is_string($table->primaryKey) && isset($this->_sequences[$table->rawName.'.'.$table->primaryKey]))
115 $table->sequenceName=$this->_sequences[$table->rawName.'.'.$table->primaryKey];
86497b8 merge from 1.0
qiang.xue authored
116 else if(is_array($table->primaryKey))
117 {
118 foreach($table->primaryKey as $pk)
119 {
d2b5331 (Fixes issue 1809)
qiang.xue authored
120 if(isset($this->_sequences[$table->rawName.'.'.$pk]))
86497b8 merge from 1.0
qiang.xue authored
121 {
d2b5331 (Fixes issue 1809)
qiang.xue authored
122 $table->sequenceName=$this->_sequences[$table->rawName.'.'.$pk];
86497b8 merge from 1.0
qiang.xue authored
123 break;
124 }
125 }
126 }
55bdd95 removed carriage returns from files.
qiang.xue authored
127
128 return $table;
129 }
130
131 /**
132 * Generates various kinds of table names.
72b9745 Updated PHPDoc @param parameter names
mdomba authored
133 * @param CPgsqlTableSchema $table the table instance
134 * @param string $name the unquoted table name
55bdd95 removed carriage returns from files.
qiang.xue authored
135 */
7c16ad7 merge from 1.0
qiang.xue authored
136 protected function resolveTableNames($table,$name)
55bdd95 removed carriage returns from files.
qiang.xue authored
137 {
138 $parts=explode('.',str_replace('"','',$name));
139 if(isset($parts[1]))
140 {
141 $schemaName=$parts[0];
142 $tableName=$parts[1];
143 }
144 else
145 {
146 $schemaName=self::DEFAULT_SCHEMA;
147 $tableName=$parts[0];
148 }
149
150 $table->name=$tableName;
151 $table->schemaName=$schemaName;
152 if($schemaName===self::DEFAULT_SCHEMA)
153 $table->rawName=$this->quoteTableName($tableName);
154 else
155 $table->rawName=$this->quoteTableName($schemaName).'.'.$this->quoteTableName($tableName);
156 }
157
158 /**
159 * Collects the table column metadata.
72b9745 Updated PHPDoc @param parameter names
mdomba authored
160 * @param CPgsqlTableSchema $table the table metadata
55bdd95 removed carriage returns from files.
qiang.xue authored
161 * @return boolean whether the table exists in the database
162 */
163 protected function findColumns($table)
164 {
165 $sql=<<<EOD
166 SELECT a.attname, LOWER(format_type(a.atttypid, a.atttypmod)) AS type, d.adsrc, a.attnotnull, a.atthasdef
167 FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
168 WHERE a.attnum > 0 AND NOT a.attisdropped
169 AND a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table
170 AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = :schema))
171 ORDER BY a.attnum
172 EOD;
173 $command=$this->getDbConnection()->createCommand($sql);
174 $command->bindValue(':table',$table->name);
175 $command->bindValue(':schema',$table->schemaName);
176
177 if(($columns=$command->queryAll())===array())
178 return false;
179
180 foreach($columns as $column)
181 {
182 $c=$this->createColumn($column);
183 $table->columns[$c->name]=$c;
184
185 if(stripos($column['adsrc'],'nextval')===0 && preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i',$column['adsrc'],$matches))
186 {
187 if(strpos($matches[1],'.')!==false || $table->schemaName===self::DEFAULT_SCHEMA)
d2b5331 (Fixes issue 1809)
qiang.xue authored
188 $this->_sequences[$table->rawName.'.'.$c->name]=$matches[1];
55bdd95 removed carriage returns from files.
qiang.xue authored
189 else
d2b5331 (Fixes issue 1809)
qiang.xue authored
190 $this->_sequences[$table->rawName.'.'.$c->name]=$table->schemaName.'.'.$matches[1];
e1ee519 (Fixes issue 1770)
qiang.xue authored
191 $c->autoIncrement=true;
55bdd95 removed carriage returns from files.
qiang.xue authored
192 }
193 }
194 return true;
195 }
196
197 /**
198 * Creates a table column.
72b9745 Updated PHPDoc @param parameter names
mdomba authored
199 * @param array $column column metadata
55bdd95 removed carriage returns from files.
qiang.xue authored
200 * @return CDbColumnSchema normalized column metadata
201 */
202 protected function createColumn($column)
203 {
204 $c=new CPgsqlColumnSchema;
205 $c->name=$column['attname'];
206 $c->rawName=$this->quoteColumnName($c->name);
207 $c->allowNull=!$column['attnotnull'];
208 $c->isPrimaryKey=false;
209 $c->isForeignKey=false;
210
211 $c->init($column['type'],$column['atthasdef'] ? $column['adsrc'] : null);
212
213 return $c;
214 }
215
216 /**
217 * Collects the primary and foreign key column details for the given table.
72b9745 Updated PHPDoc @param parameter names
mdomba authored
218 * @param CPgsqlTableSchema $table the table metadata
55bdd95 removed carriage returns from files.
qiang.xue authored
219 */
220 protected function findConstraints($table)
221 {
222 $sql=<<<EOD
223 SELECT conname, consrc, contype, indkey FROM (
224 SELECT
225 conname,
226 CASE WHEN contype='f' THEN
227 pg_catalog.pg_get_constraintdef(oid)
228 ELSE
229 'CHECK (' || consrc || ')'
230 END AS consrc,
231 contype,
232 conrelid AS relid,
233 NULL AS indkey
234 FROM
235 pg_catalog.pg_constraint
236 WHERE
237 contype IN ('f', 'c')
238 UNION ALL
239 SELECT
240 pc.relname,
241 NULL,
242 CASE WHEN indisprimary THEN
243 'p'
244 ELSE
245 'u'
246 END,
247 pi.indrelid,
248 indkey
249 FROM
250 pg_catalog.pg_class pc,
251 pg_catalog.pg_index pi
252 WHERE
253 pc.oid=pi.indexrelid
254 AND EXISTS (
255 SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c
256 ON (d.refclassid = c.tableoid AND d.refobjid = c.oid)
257 WHERE d.classid = pc.tableoid AND d.objid = pc.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p')
258 )
259 ) AS sub
260 WHERE relid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table
261 AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace
262 WHERE nspname=:schema))
263 EOD;
264 $command=$this->getDbConnection()->createCommand($sql);
265 $command->bindValue(':table',$table->name);
266 $command->bindValue(':schema',$table->schemaName);
267 foreach($command->queryAll() as $row)
268 {
269 if($row['contype']==='p') // primary key
270 $this->findPrimaryKey($table,$row['indkey']);
271 else if($row['contype']==='f') // foreign key
272 $this->findForeignKey($table,$row['consrc']);
273 }
274 }
275
276 /**
277 * Collects primary key information.
72b9745 Updated PHPDoc @param parameter names
mdomba authored
278 * @param CPgsqlTableSchema $table the table metadata
279 * @param string $indices pgsql primary key index list
55bdd95 removed carriage returns from files.
qiang.xue authored
280 */
281 protected function findPrimaryKey($table,$indices)
282 {
3aa81fa merge from 1.0.
qiang.xue authored
283 $indices=implode(', ',preg_split('/\s+/',$indices));
55bdd95 removed carriage returns from files.
qiang.xue authored
284 $sql=<<<EOD
285 SELECT attnum, attname FROM pg_catalog.pg_attribute WHERE
286 attrelid=(
287 SELECT oid FROM pg_catalog.pg_class WHERE relname=:table AND relnamespace=(
288 SELECT oid FROM pg_catalog.pg_namespace WHERE nspname=:schema
289 )
290 )
291 AND attnum IN ({$indices})
292 EOD;
293 $command=$this->getDbConnection()->createCommand($sql);
294 $command->bindValue(':table',$table->name);
295 $command->bindValue(':schema',$table->schemaName);
296 foreach($command->queryAll() as $row)
297 {
298 $name=$row['attname'];
299 if(isset($table->columns[$name]))
300 {
301 $table->columns[$name]->isPrimaryKey=true;
302 if($table->primaryKey===null)
303 $table->primaryKey=$name;
304 else if(is_string($table->primaryKey))
305 $table->primaryKey=array($table->primaryKey,$name);
306 else
307 $table->primaryKey[]=$name;
308 }
309 }
310 }
311
312 /**
313 * Collects foreign key information.
72b9745 Updated PHPDoc @param parameter names
mdomba authored
314 * @param CPgsqlTableSchema $table the table metadata
315 * @param string $src pgsql foreign key definition
55bdd95 removed carriage returns from files.
qiang.xue authored
316 */
317 protected function findForeignKey($table,$src)
318 {
319 $matches=array();
320 $brackets='\(([^\)]+)\)';
321 $pattern="/FOREIGN\s+KEY\s+{$brackets}\s+REFERENCES\s+([^\(]+){$brackets}/i";
255f0a6 merge from 1.0
qiang.xue authored
322 if(preg_match($pattern,str_replace('"','',$src),$matches))
55bdd95 removed carriage returns from files.
qiang.xue authored
323 {
324 $keys=preg_split('/,\s+/', $matches[1]);
255f0a6 merge from 1.0
qiang.xue authored
325 $tableName=$matches[2];
55bdd95 removed carriage returns from files.
qiang.xue authored
326 $fkeys=preg_split('/,\s+/', $matches[3]);
327 foreach($keys as $i=>$key)
328 {
329 $table->foreignKeys[$key]=array($tableName,$fkeys[$i]);
330 if(isset($table->columns[$key]))
331 $table->columns[$key]->isForeignKey=true;
332 }
333 }
334 }
7c16ad7 merge from 1.0
qiang.xue authored
335
336 /**
337 * Returns all table names in the database.
72b9745 Updated PHPDoc @param parameter names
mdomba authored
338 * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
ef2ca77 added PHPDoc param where needed
mdomba authored
339 * If not empty, the returned table names will be prefixed with the schema name.
7c16ad7 merge from 1.0
qiang.xue authored
340 * @return array all table names in the database.
341 */
342 protected function findTableNames($schema='')
343 {
344 if($schema==='')
345 $schema=self::DEFAULT_SCHEMA;
346 $sql=<<<EOD
347 SELECT table_name, table_schema FROM information_schema.tables
3c62111 (Fixes issue 642)
qiang.xue authored
348 WHERE table_schema=:schema AND table_type='BASE TABLE'
7c16ad7 merge from 1.0
qiang.xue authored
349 EOD;
350 $command=$this->getDbConnection()->createCommand($sql);
351 $command->bindParam(':schema',$schema);
352 $rows=$command->queryAll();
353 $names=array();
354 foreach($rows as $row)
355 {
356 if($schema===self::DEFAULT_SCHEMA)
357 $names[]=$row['table_name'];
358 else
7c9033c * Added CDbSchema::resetSequence() and checkIntegrity()
qiang.xue authored
359 $names[]=$row['table_schema'].'.'.$row['table_name'];
7c16ad7 merge from 1.0
qiang.xue authored
360 }
361 return $names;
362 }
3d984fb * Added query builder methods for building database schema manipulation ...
qiang.xue authored
363
364 /**
365 * Builds a SQL statement for renaming a DB table.
366 * @param string $table the table to be renamed. The name will be properly quoted by the method.
367 * @param string $newName the new table name. The name will be properly quoted by the method.
368 * @return string the SQL statement for renaming a DB table.
369 * @since 1.1.6
370 */
371 public function renameTable($table, $newName)
372 {
373 return 'ALTER TABLE ' . $this->quoteTableName($table) . ' RENAME TO ' . $this->quoteTableName($newName);
374 }
375
376 /**
377 * Builds a SQL statement for adding a new DB column.
378 * @param string $table the table that the new column will be added to. The table name will be properly quoted by the method.
379 * @param string $column the name of the new column. The name will be properly quoted by the method.
380 * @param string $type the column type. The {@link getColumnType} method will be invoked to convert abstract column type (if any)
381 * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL.
382 * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
383 * @return string the SQL statement for adding a new column.
384 * @since 1.1.6
385 */
386 public function addColumn($table, $column, $type)
387 {
388 $type=$this->getColumnType($type);
389 $sql='ALTER TABLE ' . $this->quoteTableName($table)
390 . ' ADD COLUMN ' . $this->quoteColumnName($column) . ' '
391 . $this->getColumnType($type);
392 return $sql;
393 }
394
395 /**
396 * Builds a SQL statement for changing the definition of a column.
397 * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method.
398 * @param string $column the name of the column to be changed. The name will be properly quoted by the method.
399 * @param string $type the new column type. The {@link getColumnType} method will be invoked to convert abstract column type (if any)
400 * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL.
401 * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
402 * @return string the SQL statement for changing the definition of a column.
403 * @since 1.1.6
404 */
405 public function alterColumn($table, $column, $type)
406 {
407 $type=$this->getColumnType($type);
408 $sql='ALTER TABLE ' . $this->quoteTableName($table) . ' ALTER COLUMN '
409 . $this->quoteColumnName($column) . ' TYPE ' . $this->getColumnType($type);
410 return $sql;
411 }
412
413 /**
414 * Builds a SQL statement for dropping an index.
415 * @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
d2d2daa swap the parameter order of createIndex/dropIndex.
qiang.xue authored
416 * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
3d984fb * Added query builder methods for building database schema manipulation ...
qiang.xue authored
417 * @return string the SQL statement for dropping an index.
418 * @since 1.1.6
419 */
d2d2daa swap the parameter order of createIndex/dropIndex.
qiang.xue authored
420 public function dropIndex($name, $table)
3d984fb * Added query builder methods for building database schema manipulation ...
qiang.xue authored
421 {
422 return 'DROP INDEX '.$this->quoteTableName($name);
423 }
55bdd95 removed carriage returns from files.
qiang.xue authored
424 }
Something went wrong with that request. Please try again.