git.sur5r.net Git - bacula/bacula/blob - gui/baculum/framework/Data/Common/Oracle/TOracleMetaData.php

   1 <?php
   2 /**
   3  * TOracleMetaData class file.
   4  *
   5  * @author Marcos Nobre <marconobre[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.Oracle
  10  */
  11
  12 /**
  13  * Load the base TDbMetaData class.
  14  */
  15 Prado::using('System.Data.Common.TDbMetaData');
  16 Prado::using('System.Data.Common.Oracle.TOracleTableInfo');
  17 Prado::using('System.Data.Common.Oracle.TOracleTableColumn');
  18
  19 /**
  20  * TOracleMetaData loads Oracle database table and column information.
  21  *
  22  * @author Marcos Nobre <marconobre[at]gmail[dot]com>
  23  * @package System.Data.Common.Oracle
  24  * @since 3.1
  25  */
  26 class TOracleMetaData extends TDbMetaData
  27 {
  28         private $_defaultSchema = 'system';
  29
  30
  31         /**
  32          * @return string TDbTableInfo class name.
  33          */
  34         protected function getTableInfoClass()
  35         {
  36                 return 'TOracleTableInfo';
  37         }
  38
  39         /**
  40          * @param string default schema.
  41          */
  42         public function setDefaultSchema($schema)
  43         {
  44                 $this->_defaultSchema=$schema;
  45         }
  46
  47         /**
  48          * @return string default schema.
  49          */
  50         public function getDefaultSchema()
  51         {
  52                 return $this->_defaultSchema;
  53         }
  54
  55         /**
  56          * @param string table name with optional schema name prefix, uses default schema name prefix is not provided.
  57          * @return array tuple as ($schemaName,$tableName)
  58          */
  59         protected function getSchemaTableName($table)
  60         {
  61                 if(count($parts= explode('.', str_replace('"','',$table))) > 1)
  62                         return array($parts[0], $parts[1]);
  63                 else
  64                         return array($this->getDefaultSchema(),$parts[0]);
  65         }
  66
  67         /**
  68          * Get the column definitions for given table.
  69          * @param string table name.
  70          * @return TOracleTableInfo table information.
  71          */
  72         protected function createTableInfo($table)
  73         {
  74                 list($schemaName,$tableName) = $this->getSchemaTableName($table);
  75
  76                 // This query is made much more complex by the addition of the 'attisserial' field.
  77                 // The subquery to get that field checks to see if there is an internally dependent
  78                 // sequence on the field.
  79                 $sql =
  80 <<<EOD
  81                 SELECT
  82                         a.COLUMN_ID,
  83                         LOWER(a.COLUMN_NAME) as attname,
  84                         a.DATA_TYPE || DECODE( a.DATA_TYPE, 'NUMBER', '('||a.DATA_PRECISION||','||DATA_SCALE||')' , '') as type,
  85                         a.DATA_LENGTH as atttypmod,
  86                         DECODE(a.NULLABLE, 'Y', '0', '1') as attnotnull,
  87                         DECODE(a.DEFAULT_LENGTH, NULL, '0', '1') as atthasdef,
  88                         DATA_DEFAULT as adsrc,
  89                         '0' AS attisserial
  90                 FROM
  91                         ALL_TAB_COLUMNS a
  92                 WHERE
  93                         TABLE_NAME = '{$tableName}'
  94                         AND OWNER = '{$schemaName}'
  95                 ORDER BY a.COLUMN_ID
  96 EOD;
  97                 $this->getDbConnection()->setActive(true);
  98                 $this->getDbConnection()->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
  99                 $command = $this->getDbConnection()->createCommand($sql);
 100                 //$command->bindValue(':table', $tableName);
 101                 //$command->bindValue(':schema', $schemaName);
 102                 $tableInfo = $this->createNewTableInfo($schemaName, $tableName);
 103                 $index=0;
 104                 foreach($command->query() as $col)
 105                 {
 106                         $col['index'] = $index++;
 107                         $this->processColumn($tableInfo, $col);
 108                 }
 109                 if($index===0)
 110                         throw new TDbException('dbmetadata_invalid_table_view', $table);
 111                 return $tableInfo;
 112         }
 113
 114         /**
 115          * @param string table schema name
 116          * @param string table name.
 117          * @return TOracleTableInfo
 118          */
 119         protected function createNewTableInfo($schemaName,$tableName)
 120         {
 121                 $info['SchemaName'] = $this->assertIdentifier($schemaName);
 122                 $info['TableName']      = $this->assertIdentifier($tableName);
 123                 $info['IsView']         = false;
 124                 if($this->getIsView($schemaName,$tableName)) $info['IsView'] = true;
 125                 list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName);
 126                 $class = $this->getTableInfoClass();
 127                 return new $class($info,$primary,$foreign);
 128         }
 129
 130         /**
 131          * @param string table name, schema name or column name.
 132          * @return string a valid identifier.
 133          * @throws TDbException when table name contains a double quote (").
 134          */
 135         protected function assertIdentifier($name)
 136         {
 137                 if(strpos($name, '"')!==false)
 138                 {
 139                         $ref = 'http://www.oracle.com';
 140                         throw new TDbException('dbcommon_invalid_identifier_name', $name, $ref);
 141                 }
 142                 return $name;
 143         }
 144
 145         /**
 146          * @param string table schema name
 147          * @param string table name.
 148          * @return boolean true if the table is a view.
 149          */
 150         protected function getIsView($schemaName,$tableName)
 151         {
 152                 $this->getDbConnection()->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
 153                 $sql =
 154 <<<EOD
 155                 select  OBJECT_TYPE
 156                 from    ALL_OBJECTS
 157                 where   OBJECT_NAME = '{$tableName}'
 158                 and     OWNER = '{$schemaName}'
 159 EOD;
 160                 $this->getDbConnection()->setActive(true);
 161                 $command = $this->getDbConnection()->createCommand($sql);
 162                 //$command->bindValue(':schema',$schemaName);
 163                 //$command->bindValue(':table', $tableName);
 164                 return intval($command->queryScalar() === 'VIEW');
 165         }
 166
 167         /**
 168          * @param TOracleTableInfo table information.
 169          * @param array column information.
 170          */
 171         protected function processColumn($tableInfo, $col)
 172         {
 173                 $columnId = strtolower($col['attname']); //use column name as column Id
 174
 175                 //$info['ColumnName']   = '"'.$columnId.'"'; //quote the column names!
 176                 $info['ColumnName']     = $columnId; //NOT quote the column names!
 177                 $info['ColumnId']               = $columnId;
 178                 $info['ColumnIndex']    = $col['index'];
 179                 if(! (bool)$col['attnotnull'] ) $info['AllowNull'] = true;
 180                 if(in_array($columnId, $tableInfo->getPrimaryKeys())) $info['IsPrimaryKey'] = true;
 181                 if($this->isForeignKeyColumn($columnId, $tableInfo)) $info['IsForeignKey'] = true;
 182                 if( (int)$col['atttypmod'] > 0 ) $info['ColumnSize'] =  $col['atttypmod']; // - 4;
 183                 if( (bool)$col['atthasdef'] ) $info['DefaultValue'] = $col['adsrc'];
 184                 //
 185                 // For a while Oracle Tables has no  associated AutoIncrement Triggers
 186                 //
 187                 /*
 188                 if( $col['attisserial'] )
 189                 {
 190                         if(($sequence = $this->getSequenceName($tableInfo, $col['adsrc']))!==null)
 191                         {
 192                                 $info['SequenceName'] = $sequence;
 193                                 unset($info['DefaultValue']);
 194                         }
 195                 }
 196                 */
 197                 $matches = array();
 198                 if(preg_match('/\((\d+)(?:,(\d+))?+\)/', $col['type'], $matches))
 199                 {
 200                         $info['DbType'] = preg_replace('/\(\d+(?:,\d+)?\)/','',$col['type']);
 201                         if($this->isPrecisionType($info['DbType']))
 202                         {
 203                                 $info['NumericPrecision'] = intval($matches[1]);
 204                                 if(count($matches) > 2)
 205                                         $info['NumericScale'] = intval($matches[2]);
 206                         }
 207                         else
 208                                 $info['ColumnSize'] = intval($matches[1]);
 209                 }
 210                 else
 211                         $info['DbType'] = $col['type'];
 212                 $tableInfo->Columns[$columnId] = new TOracleTableColumn($info);
 213         }
 214
 215         /**
 216          * @return string serial name if found, null otherwise.
 217          */
 218         protected function getSequenceName($tableInfo,$src)
 219         {
 220                 $matches = array();
 221                 if(preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i',$src,$matches))
 222                 {
 223                         if(is_int(strpos($matches[1], '.')))
 224                                 return $matches[1];
 225                         else
 226                                 return $tableInfo->getSchemaName().'.'.$matches[1];
 227                 }
 228         }
 229
 230         /**
 231          * @return boolean true if column type if "numeric", "interval" or begins with "time".
 232          */
 233         protected function isPrecisionType($type)
 234         {
 235                 $type = strtolower(trim($type));
 236                 return $type==='number'; // || $type==='interval' || strpos($type, 'time')===0;
 237         }
 238
 239         /**
 240          * Gets the primary and foreign key column details for the given table.
 241          * @param string schema name
 242          * @param string table name.
 243          * @return array tuple ($primary, $foreign)
 244          */
 245         protected function getConstraintKeys($schemaName, $tableName)
 246         {
 247                 $this->getDbConnection()->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
 248 //              select decode( a.CONSTRAINT_TYPE, 'P', 'PRIMARY KEY (', 'FOREIGN KEY (' )||b.COLUMN_NAME||')' as consrc,
 249                 $sql =
 250 <<<EOD
 251                 select b.COLUMN_NAME as consrc,
 252                            a.CONSTRAINT_TYPE as contype
 253                 from ALL_CONSTRAINTS a, ALL_CONS_COLUMNS b
 254                 where (a.constraint_name = b.constraint_name AND a.table_name = b.table_name AND a.owner = b.owner)
 255                 and       a.TABLE_NAME = '{$tableName}'
 256                 and   a.OWNER = '{$schemaName}'
 257                 and   a.CONSTRAINT_TYPE in ('P','R')
 258 EOD;
 259                 $this->getDbConnection()->setActive(true);
 260                 $command = $this->getDbConnection()->createCommand($sql);
 261                 //$command->bindValue(':table', $tableName);
 262                 //$command->bindValue(':schema', $schemaName);
 263                 $primary = array();
 264                 $foreign = array();
 265                 foreach($command->query() as $row)
 266                 {
 267                         switch( strtolower( $row['contype'] ) )
 268                         {
 269                                 case 'p':
 270                                         $primary = array_merge( $primary, array(strtolower( $row['consrc'] )) );
 271                                         /*
 272                                         $arr = $this->getPrimaryKeys($row['consrc']);
 273                                         $primary = array_merge( $primary, array(strtolower( $arr[0] )) );
 274                                         */
 275                                         break;
 276                                 case 'r':
 277                                         $foreign = array_merge( $foreign, array(strtolower( $row['consrc'] )) );
 278                                         /*
 279                                         // if(($fkey = $this->getForeignKeys($row['consrc']))!==null)
 280                                         $fkey = $this->getForeignKeys( $row['consrc'] );
 281                                         $foreign = array_merge( $foreign, array(strtolower( $fkey )) );
 282                                         */
 283                                         break;
 284                         }
 285                 }
 286                 return array($primary,$foreign);
 287         }
 288
 289         /**
 290          * Gets the primary key field names
 291          * @param string Oracle primary key definition
 292          * @return array primary key field names.
 293          */
 294         protected function getPrimaryKeys($src)
 295         {
 296                 $matches = array();
 297                 if(preg_match('/PRIMARY\s+KEY\s+\(([^\)]+)\)/i', $src, $matches))
 298                         return preg_split('/,\s+/',$matches[1]);
 299                 return array();
 300         }
 301
 302         /**
 303          * Gets foreign relationship constraint keys and table name
 304          * @param string Oracle foreign key definition
 305          * @return array foreign relationship table name and keys, null otherwise
 306          */
 307         protected function getForeignKeys($src)
 308         {
 309                 $matches = array();
 310                 $brackets = '\(([^\)]+)\)';
 311                 $find = "/FOREIGN\s+KEY\s+{$brackets}\s+REFERENCES\s+([^\(]+){$brackets}/i";
 312                 if(preg_match($find, $src, $matches))
 313                 {
 314                         $keys = preg_split('/,\s+/', $matches[1]);
 315                         $fkeys = array();
 316                         foreach(preg_split('/,\s+/', $matches[3]) as $i => $fkey)
 317                                 $fkeys[$keys[$i]] = $fkey;
 318                         return array('table' => str_replace('"','',$matches[2]), 'keys' => $fkeys);
 319                 }
 320         }
 321
 322         /**
 323          * @param string column name.
 324          * @param TOracleTableInfo table information.
 325          * @return boolean true if column is a foreign key.
 326          */
 327         protected function isForeignKeyColumn($columnId, $tableInfo)
 328         {
 329                 foreach($tableInfo->getForeignKeys() as $fk)
 330                 {
 331                         if( $fk==$columnId )
 332                         //if(in_array($columnId, array_keys($fk['keys'])))
 333                                 return true;
 334                 }
 335                 return false;
 336         }
 337
 338         /**
 339          * Returns all table names in the database.
 340          * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
 341          * If not empty, the returned table names will be prefixed with the schema name.
 342          * @return array all table names in the database.
 343          */
 344         public function findTableNames($schema='')
 345         {
 346                 if($schema==='')
 347                 {
 348                         $sql=<<<EOD
 349 SELECT table_name, '{$schema}' as table_schema FROM user_tables
 350 EOD;
 351                         $command=$this->getDbConnection()->createCommand($sql);
 352                 }
 353                 else
 354                 {
 355                         $sql=<<<EOD
 356 SELECT object_name as table_name, owner as table_schema FROM all_objects
 357 WHERE object_type = 'TABLE' AND owner=:schema
 358 EOD;
 359                         $command=$this->getDbConnection()->createCommand($sql);
 360                         $command->bindParam(':schema',$schema);
 361                 }
 362
 363                 $rows=$command->queryAll();
 364                 $names=array();
 365                 foreach($rows as $row)
 366                 {
 367                         if($schema===$this->getDefaultSchema() || $schema==='')
 368                                 $names[]=$row['TABLE_NAME'];
 369                         else
 370                                 $names[]=$row['TABLE_SCHEMA'].'.'.$row['TABLE_NAME'];
 371                 }
 372                 return $names;
 373         }
 374 }