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