git.sur5r.net Git - bacula/bacula/blob - gui/baculum/framework/Data/Common/Pgsql/TPgsqlMetaData.php

   1 <?php
   2 /**
   3  * TPgsqlMetaData class file.
   4  *
   5  * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
   6  * @link https://github.com/pradosoft/prado
   7  * @copyright Copyright &copy; 2005-2016 The PRADO Group
   8  * @license https://github.com/pradosoft/prado/blob/master/COPYRIGHT
   9  * @package System.Data.Common.Pgsql
  10  */
  11
  12 /**
  13  * Load the base TDbMetaData class.
  14  */
  15 Prado::using('System.Data.Common.TDbMetaData');
  16 Prado::using('System.Data.Common.Pgsql.TPgsqlTableInfo');
  17
  18 /**
  19  * TPgsqlMetaData loads PostgreSQL database table and column information.
  20  *
  21  * @author Wei Zhuo <weizho[at]gmail[dot]com>
  22  * @package System.Data.Common.Pgsql
  23  * @since 3.1
  24  */
  25 class TPgsqlMetaData extends TDbMetaData
  26 {
  27         private $_defaultSchema = 'public';
  28
  29         /**
  30          * @return string TDbTableInfo class name.
  31          */
  32         protected function getTableInfoClass()
  33         {
  34                 return 'TPgsqlTableInfo';
  35         }
  36
  37         /**
  38          * Quotes a table name for use in a query.
  39          * @param string $name table name
  40          * @return string the properly quoted table name
  41          */
  42         public function quoteTableName($name)
  43         {
  44                 return parent::quoteTableName($name, '"', '"');
  45         }
  46
  47         /**
  48          * Quotes a column name for use in a query.
  49          * @param string $name column name
  50          * @return string the properly quoted column name
  51          */
  52         public function quoteColumnName($name)
  53         {
  54                 return parent::quoteColumnName($name, '"', '"');
  55         }
  56
  57         /**
  58          * Quotes a column alias for use in a query.
  59          * @param string $name column alias
  60          * @return string the properly quoted column alias
  61          */
  62         public function quoteColumnAlias($name)
  63         {
  64                 return parent::quoteColumnAlias($name, '"', '"');
  65         }
  66
  67         /**
  68          * @param string default schema.
  69          */
  70         public function setDefaultSchema($schema)
  71         {
  72                 $this->_defaultSchema=$schema;
  73         }
  74
  75         /**
  76          * @return string default schema.
  77          */
  78         public function getDefaultSchema()
  79         {
  80                 return $this->_defaultSchema;
  81         }
  82
  83         /**
  84          * @param string table name with optional schema name prefix, uses default schema name prefix is not provided.
  85          * @return array tuple as ($schemaName,$tableName)
  86          */
  87         protected function getSchemaTableName($table)
  88         {
  89                 if(count($parts= explode('.', str_replace('"','',$table))) > 1)
  90                         return array($parts[0], $parts[1]);
  91                 else
  92                         return array($this->getDefaultSchema(),$parts[0]);
  93         }
  94
  95         /**
  96          * Get the column definitions for given table.
  97          * @param string table name.
  98          * @return TPgsqlTableInfo table information.
  99          */
 100         protected function createTableInfo($table)
 101         {
 102                 list($schemaName,$tableName) = $this->getSchemaTableName($table);
 103
 104                 // This query is made much more complex by the addition of the 'attisserial' field.
 105                 // The subquery to get that field checks to see if there is an internally dependent
 106                 // sequence on the field.
 107                 $sql =
 108 <<<EOD
 109                 SELECT
 110                         a.attname,
 111                         pg_catalog.format_type(a.atttypid, a.atttypmod) as type,
 112                         a.atttypmod,
 113                         a.attnotnull, a.atthasdef, adef.adsrc,
 114                         (
 115                                 SELECT 1 FROM pg_catalog.pg_depend pd, pg_catalog.pg_class pc
 116                                 WHERE pd.objid=pc.oid
 117                                 AND pd.classid=pc.tableoid
 118                                 AND pd.refclassid=pc.tableoid
 119                                 AND pd.refobjid=a.attrelid
 120                                 AND pd.refobjsubid=a.attnum
 121                                 AND pd.deptype='i'
 122                                 AND pc.relkind='S'
 123                         ) IS NOT NULL AS attisserial
 124
 125                 FROM
 126                         pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_attrdef adef
 127                         ON a.attrelid=adef.adrelid
 128                         AND a.attnum=adef.adnum
 129                         LEFT JOIN pg_catalog.pg_type t ON a.atttypid=t.oid
 130                 WHERE
 131                         a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table
 132                                 AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE
 133                                 nspname = :schema))
 134                         AND a.attnum > 0 AND NOT a.attisdropped
 135                 ORDER BY a.attnum
 136 EOD;
 137                 $this->getDbConnection()->setActive(true);
 138                 $command = $this->getDbConnection()->createCommand($sql);
 139                 $command->bindValue(':table', $tableName);
 140                 $command->bindValue(':schema', $schemaName);
 141                 $tableInfo = $this->createNewTableInfo($schemaName, $tableName);
 142                 $index=0;
 143                 foreach($command->query() as $col)
 144                 {
 145                         $col['index'] = $index++;
 146                         $this->processColumn($tableInfo, $col);
 147                 }
 148                 if($index===0)
 149                         throw new TDbException('dbmetadata_invalid_table_view', $table);
 150                 return $tableInfo;
 151         }
 152
 153         /**
 154          * @param string table schema name
 155          * @param string table name.
 156          * @return TPgsqlTableInfo
 157          */
 158         protected function createNewTableInfo($schemaName,$tableName)
 159         {
 160                 $info['SchemaName'] = $this->assertIdentifier($schemaName);
 161                 $info['TableName'] = $this->assertIdentifier($tableName);
 162                 if($this->getIsView($schemaName,$tableName))
 163                         $info['IsView'] = true;
 164                 list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName);
 165                 $class = $this->getTableInfoClass();
 166                 return new $class($info,$primary,$foreign);
 167         }
 168
 169         /**
 170          * @param string table name, schema name or column name.
 171          * @return string a valid identifier.
 172          * @throws TDbException when table name contains a double quote (").
 173          */
 174         protected function assertIdentifier($name)
 175         {
 176                 if(strpos($name, '"')!==false)
 177                 {
 178                         $ref = 'http://www.postgresql.org/docs/7.4/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS';
 179                         throw new TDbException('dbcommon_invalid_identifier_name', $name, $ref);
 180                 }
 181                 return $name;
 182         }
 183
 184         /**
 185          * @param string table schema name
 186          * @param string table name.
 187          * @return boolean true if the table is a view.
 188          */
 189         protected function getIsView($schemaName,$tableName)
 190         {
 191                 $sql =
 192 <<<EOD
 193                 SELECT count(c.relname) FROM pg_catalog.pg_class c
 194                 LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
 195                 WHERE (n.nspname=:schema) AND (c.relkind = 'v'::"char") AND c.relname = :table
 196 EOD;
 197                 $this->getDbConnection()->setActive(true);
 198                 $command = $this->getDbConnection()->createCommand($sql);
 199                 $command->bindValue(':schema',$schemaName);
 200                 $command->bindValue(':table', $tableName);
 201                 return intval($command->queryScalar()) === 1;
 202         }
 203
 204         /**
 205          * @param TPgsqlTableInfo table information.
 206          * @param array column information.
 207          */
 208         protected function processColumn($tableInfo, $col)
 209         {
 210                 $columnId = $col['attname']; //use column name as column Id
 211
 212                 $info['ColumnName'] = '"'.$columnId.'"'; //quote the column names!
 213                 $info['ColumnId'] = $columnId;
 214                 $info['ColumnIndex'] = $col['index'];
 215                 if(!$col['attnotnull'])
 216                         $info['AllowNull'] = true;
 217                 if(in_array($columnId, $tableInfo->getPrimaryKeys()))
 218                         $info['IsPrimaryKey'] = true;
 219                 if($this->isForeignKeyColumn($columnId, $tableInfo))
 220                         $info['IsForeignKey'] = true;
 221
 222                 if($col['atttypmod'] > 0)
 223                         $info['ColumnSize'] =  $col['atttypmod'] - 4;
 224                 if($col['atthasdef'])
 225                         $info['DefaultValue'] = $col['adsrc'];
 226                 if($col['attisserial'] || substr($col['adsrc'],0,8) === 'nextval(')
 227                 {
 228                         if(($sequence = $this->getSequenceName($tableInfo, $col['adsrc']))!==null)
 229                         {
 230                                 $info['SequenceName'] = $sequence;
 231                                 unset($info['DefaultValue']);
 232                         }
 233                 }
 234                 $matches = array();
 235                 if(preg_match('/\((\d+)(?:,(\d+))?+\)/', $col['type'], $matches))
 236                 {
 237                         $info['DbType'] = preg_replace('/\(\d+(?:,\d+)?\)/','',$col['type']);
 238                         if($this->isPrecisionType($info['DbType']))
 239                         {
 240                                 $info['NumericPrecision'] = intval($matches[1]);
 241                                 if(count($matches) > 2)
 242                                         $info['NumericScale'] = intval($matches[2]);
 243                         }
 244                         else
 245                                 $info['ColumnSize'] = intval($matches[1]);
 246                 }
 247                 else
 248                         $info['DbType'] = $col['type'];
 249
 250                 $tableInfo->Columns[$columnId] = new TPgsqlTableColumn($info);
 251         }
 252
 253         /**
 254          * @return string serial name if found, null otherwise.
 255          */
 256         protected function getSequenceName($tableInfo,$src)
 257         {
 258                 $matches = array();
 259                 if(preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i',$src,$matches))
 260                 {
 261                         if(is_int(strpos($matches[1], '.')))
 262                                 return $matches[1];
 263                         else
 264                                 return $tableInfo->getSchemaName().'.'.$matches[1];
 265                 }
 266         }
 267
 268         /**
 269          * @return boolean true if column type if "numeric", "interval" or begins with "time".
 270          */
 271         protected function isPrecisionType($type)
 272         {
 273                 $type = strtolower(trim($type));
 274                 return $type==='numeric' || $type==='interval' || strpos($type, 'time')===0;
 275         }
 276
 277         /**
 278          * Gets the primary and foreign key column details for the given table.
 279          * @param string schema name
 280          * @param string table name.
 281          * @return array tuple ($primary, $foreign)
 282          */
 283         protected function getConstraintKeys($schemaName, $tableName)
 284         {
 285                 $sql =
 286 <<<EOD
 287         SELECT conname, consrc, contype, indkey, indisclustered FROM (
 288                         SELECT
 289                                         conname,
 290                                         CASE WHEN contype='f' THEN
 291                                                         pg_catalog.pg_get_constraintdef(oid)
 292                                         ELSE
 293                                                         'CHECK (' || consrc || ')'
 294                                         END AS consrc,
 295                                         contype,
 296                                         conrelid AS relid,
 297                                         NULL AS indkey,
 298                                         FALSE AS indisclustered
 299                         FROM
 300                                         pg_catalog.pg_constraint
 301                         WHERE
 302                                         contype IN ('f', 'c')
 303                         UNION ALL
 304                         SELECT
 305                                         pc.relname,
 306                                         NULL,
 307                                         CASE WHEN indisprimary THEN
 308                                                         'p'
 309                                         ELSE
 310                                                         'u'
 311                                         END,
 312                                         pi.indrelid,
 313                                         indkey,
 314                                         pi.indisclustered
 315                         FROM
 316                                         pg_catalog.pg_class pc,
 317                                         pg_catalog.pg_index pi
 318                         WHERE
 319                                         pc.oid=pi.indexrelid
 320                                         AND EXISTS (
 321                                                         SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c
 322                                                         ON (d.refclassid = c.tableoid AND d.refobjid = c.oid)
 323                                                         WHERE d.classid = pc.tableoid AND d.objid = pc.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p')
 324                         )
 325         ) AS sub
 326         WHERE relid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table
 327                                         AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace
 328                                         WHERE nspname=:schema))
 329         ORDER BY
 330                         1
 331 EOD;
 332                 $this->getDbConnection()->setActive(true);
 333                 $command = $this->getDbConnection()->createCommand($sql);
 334                 $command->bindValue(':table', $tableName);
 335                 $command->bindValue(':schema', $schemaName);
 336                 $primary = array();
 337                 $foreign = array();
 338                 foreach($command->query() as $row)
 339                 {
 340                         switch($row['contype'])
 341                         {
 342                                 case 'p':
 343                                         $primary = $this->getPrimaryKeys($tableName, $schemaName, $row['indkey']);
 344                                         break;
 345                                 case 'f':
 346                                         if(($fkey = $this->getForeignKeys($row['consrc']))!==null)
 347                                                 $foreign[] = $fkey;
 348                                         break;
 349                         }
 350                 }
 351                 return array($primary,$foreign);
 352         }
 353
 354         /**
 355          * Gets the primary key field names
 356          * @param string pgsql primary key definition
 357          * @return array primary key field names.
 358          */
 359         protected function getPrimaryKeys($tableName, $schemaName, $columnIndex)
 360         {
 361                 $index = join(', ', explode(' ', $columnIndex));
 362                 $sql =
 363 <<<EOD
 364                 SELECT attnum, attname FROM pg_catalog.pg_attribute WHERE
 365                 attrelid=(
 366                         SELECT oid FROM pg_catalog.pg_class WHERE relname=:table AND relnamespace=(
 367                                 SELECT oid FROM pg_catalog.pg_namespace WHERE nspname=:schema
 368                         )
 369                 )
 370                                 AND attnum IN ({$index})
 371 EOD;
 372                 $command = $this->getDbConnection()->createCommand($sql);
 373                 $command->bindValue(':table', $tableName);
 374                 $command->bindValue(':schema', $schemaName);
 375 //              $command->bindValue(':columnIndex', join(', ', explode(' ', $columnIndex)));
 376                 $primary = array();
 377                 foreach($command->query() as $row)
 378                 {
 379                                                 $primary[] = $row['attname'];
 380                 }
 381
 382                 return $primary;
 383         }
 384
 385         /**
 386          * Gets foreign relationship constraint keys and table name
 387          * @param string pgsql foreign key definition
 388          * @return array foreign relationship table name and keys, null otherwise
 389          */
 390         protected function getForeignKeys($src)
 391         {
 392                 $matches = array();
 393                 $brackets = '\(([^\)]+)\)';
 394                 $find = "/FOREIGN\s+KEY\s+{$brackets}\s+REFERENCES\s+([^\(]+){$brackets}/i";
 395                 if(preg_match($find, $src, $matches))
 396                 {
 397                         $keys = preg_split('/,\s+/', $matches[1]);
 398                         $fkeys = array();
 399                         foreach(preg_split('/,\s+/', $matches[3]) as $i => $fkey)
 400                                 $fkeys[$keys[$i]] = $fkey;
 401                         return array('table' => str_replace('"','',$matches[2]), 'keys' => $fkeys);
 402                 }
 403         }
 404
 405         /**
 406          * @param string column name.
 407          * @param TPgsqlTableInfo table information.
 408          * @return boolean true if column is a foreign key.
 409          */
 410         protected function isForeignKeyColumn($columnId, $tableInfo)
 411         {
 412                 foreach($tableInfo->getForeignKeys() as $fk)
 413                 {
 414                         if(in_array($columnId, array_keys($fk['keys'])))
 415                                 return true;
 416                 }
 417                 return false;
 418         }
 419
 420         /**
 421          * Returns all table names in the database.
 422          * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
 423          * If not empty, the returned table names will be prefixed with the schema name.
 424          * @return array all table names in the database.
 425          */
 426         public function findTableNames($schema='public')
 427         {
 428                 if($schema==='')
 429                         $schema=self::DEFAULT_SCHEMA;
 430                 $sql=<<<EOD
 431 SELECT table_name, table_schema FROM information_schema.tables
 432 WHERE table_schema=:schema AND table_type='BASE TABLE'
 433 EOD;
 434                 $command=$this->getDbConnection()->createCommand($sql);
 435                 $command->bindParam(':schema',$schema);
 436                 $rows=$command->queryAll();
 437                 $names=array();
 438                 foreach($rows as $row)
 439                 {
 440                         if($schema===self::DEFAULT_SCHEMA)
 441                                 $names[]=$row['table_name'];
 442                         else
 443                                 $names[]=$row['table_schema'].'.'.$row['table_name'];
 444                 }
 445                 return $names;
 446         }
 447 }
 448