[Sommaire] [Imprimer]

   1  <?php
   2  /*
   3   *  $Id: OCI8TableInfo.php,v 1.13 2006/01/06 00:02:38 sethr Exp $
   4   *
   5   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
   6   * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
   7   * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
   8   * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
   9   * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  10   * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  11   * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  12   * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  13   * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  14   * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  15   * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  16   *
  17   * This software consists of voluntary contributions made by many individuals
  18   * and is licensed under the LGPL. For more information please see
  19   * <http://creole.phpdb.org>.
  20   */
  21   
  22  require_once 'creole/metadata/TableInfo.php';
  23  
  24  /**
  25   * Oracle (OCI8) implementation of TableInfo.
  26   * 
  27   * @author    David Giffin <david@giffin.org>
  28   * @author    Hans Lellelid <hans@xmpl.org>
  29   * @version   $Revision$
  30   * @package   creole.drivers.oracle.metadata
  31   */
  32  class OCI8TableInfo extends TableInfo {
  33      
  34      private $schema;
  35          
  36      public function __construct(OCI8DatabaseInfo $database, $name)
  37      {
  38          $this->schema = strtoupper( $database->getSchema() );
  39          parent::__construct($database, $name);
  40          $this->name = strtoupper( $this->name );
  41      }
  42      
  43      /** Loads the columns for this table. */
  44      protected function initColumns() 
  45      {
  46          
  47          include_once 'creole/metadata/ColumnInfo.php';
  48          include_once 'creole/drivers/oracle/OCI8Types.php';
  49          
  50  
  51          // To get all of the attributes we need, we'll actually do 
  52          // two separate queries.  The first gets names and default values
  53          // the second will fill in some more details.
  54          
  55          $sql = "
  56              SELECT column_name
  57                  , data_type
  58                  , data_precision
  59                  , data_length
  60                  , data_default
  61                  , nullable 
  62                  , data_scale
  63              FROM  all_tab_columns
  64              WHERE table_name = '{$this->name}'
  65                  AND OWNER = '{$this->schema}'";
  66  
  67          $statement = @oci_parse($this->conn->getResource(),$sql);
  68          $success = @oci_execute($statement,OCI_DEFAULT);
  69          if (!$success) {
  70              throw new SQLException("Could Not Get Columns");
  71          }
  72  
  73          while ( $statement && $row = oci_fetch_array( $statement
  74              , OCI_ASSOC + OCI_RETURN_NULLS ) ) {
  75              $row = array_change_key_case($row, CASE_LOWER);
  76              $this->columns[$row['column_name']] = new ColumnInfo( $this
  77                  , $row['column_name']
  78                  , OCI8Types::getType($row['data_type'])
  79                  , $row['data_type']
  80                  , $row['data_length']
  81                  , $row['data_precision']
  82                  , $row['data_scale']
  83                  , $row['nullable']
  84                  , $row['data_default']
  85              );
  86          }
  87                  
  88          $this->colsLoaded = true;
  89      }
  90      
  91      /** Loads the primary key information for this table. */
  92      protected function initPrimaryKey()
  93      {
  94          include_once 'creole/metadata/PrimaryKeyInfo.php';
  95          
  96          // columns have to be loaded first
  97          if (!$this->colsLoaded) $this->initColumns();
  98          
  99  
 100          // Primary Keys Query
 101          $sql = "SELECT a.owner, a.table_name,
 102                              a.constraint_name, a.column_name
 103                          FROM all_cons_columns a, all_constraints b
 104                          WHERE b.constraint_type = 'P'
 105                          AND a.constraint_name = b.constraint_name
 106                          AND b.table_name = '{$this->name}'
 107              AND b.owner = '{$this->schema}'
 108              ";
 109  
 110  
 111          $statement = @oci_parse($this->conn->getResource(),$sql);
 112          $success = @oci_execute($statement,OCI_DEFAULT);
 113          if (!$success) {
 114              throw new SQLException("Could Not Get Primary Keys");
 115          }
 116  
 117          while ( $statement && $row = oci_fetch_assoc( $statement )) {
 118              $row = array_change_key_case($row,CASE_LOWER);
 119  
 120              $name = $row['column_name'];
 121  
 122              if (!isset($this->primaryKey)) {
 123                  $this->primaryKey = new PrimaryKeyInfo($name);
 124              }
 125  
 126              $this->primaryKey->addColumn($this->columns[$name]);
 127          }
 128          
 129          $this->pkLoaded = true;
 130      }
 131      
 132      /** Loads the indexes for this table. */
 133      protected function initIndexes() {
 134      
 135          include_once 'creole/metadata/IndexInfo.php';    
 136  
 137          // columns have to be loaded first
 138          if (!$this->colsLoaded) $this->initColumns();        
 139  
 140          // Indexes
 141          $sql = "SELECT
 142              allind.index_name,
 143              allind.table_name,
 144              allind.index_type,
 145              allind.uniqueness,
 146              indcol.column_name
 147              FROM all_indexes allind INNER JOIN all_ind_columns indcol
 148                  ON allind.owner = indcol.index_owner
 149                  AND allind.index_name = indcol.index_name
 150              WHERE allind.table_owner = '{$this->schema}'
 151              AND allind.table_name = '{$this->name}'
 152              AND allind.index_name NOT IN (SELECT
 153                      constraint_name
 154                      FROM all_constraints
 155                      WHERE constraint_type = 'P')
 156              ORDER BY allind.index_name,
 157                  indcol.column_position";
 158  
 159          $statement = @oci_parse($this->conn->getResource(),$sql);
 160          $success = @oci_execute($statement,OCI_DEFAULT);
 161          if (!$success) {
 162              throw new SQLException("Could Not Get Primary Keys");
 163          }
 164  
 165  
 166          // Loop through the returned results, grouping the same key_name together
 167          // adding each column for that key.
 168  
 169          while ( $statement && $row = oci_fetch_assoc( $statement )) {
 170              $row = array_change_key_case($row,CASE_LOWER);
 171  
 172              $name = $row['index_name'];
 173              $index_col_name = $row['column_name'];
 174  
 175              if (!isset($this->indexes[$name])) {
 176                  $this->indexes[$name] = new IndexInfo($name);
 177              }
 178  
 179              $this->indexes[$name]->addColumn($this->columns[ $index_col_name ]);
 180          }
 181          
 182                  
 183          $this->indexesLoaded = true;
 184      }
 185      
 186      /** Load foreign keys */
 187      protected function initForeignKeys() {
 188          
 189          include_once 'creole/metadata/ForeignKeyInfo.php';    
 190  
 191          // columns have to be loaded first
 192          if (!$this->colsLoaded) $this->initColumns();        
 193          
 194          // Foreign keys
 195          // TODO resolve cross schema references
 196          // use all_cons... to do so, however, very slow queries then
 197          // optimizations are very ugly
 198          $sql                    = "
 199              SELECT a.owner AS local_owner
 200                  , a.table_name AS local_table
 201                  , c.column_name AS local_column
 202                  , a.constraint_name AS foreign_key_name
 203                  , b.owner AS foreign_owner
 204                  , b.table_name AS foreign_table
 205                  , d.column_name AS foreign_column
 206                  , b.constraint_name AS foreign_constraint_name
 207                  , a.delete_rule AS on_delete
 208              FROM user_constraints a
 209                  , user_constraints b
 210                  , user_cons_columns c
 211                  , user_cons_columns d
 212              WHERE a.r_constraint_name = b.constraint_name
 213                  AND c.constraint_name = a.constraint_name
 214                  AND d.constraint_name = b.constraint_name
 215                  AND a.r_owner = b.owner
 216                  AND a.constraint_type='R'
 217                  AND a.table_name = '{$this->name}'
 218                  AND a.owner = '{$this->schema}'
 219          ";
 220  
 221          $statement = @oci_parse($this->conn->getResource(),$sql);
 222          $success = @oci_execute($statement,OCI_DEFAULT);
 223          if (!$success) {
 224              throw new SQLException("Could Not Get Primary Keys");
 225          }
 226  
 227          // Loop through the returned results, grouping the same key_name
 228          // together adding each column for that key.
 229  
 230          while ( $statement && $row = oci_fetch_assoc( $statement )) {
 231              $row = array_change_key_case($row,CASE_LOWER);
 232  
 233              $name = $row['foreign_key_name'];            
 234  
 235              $foreignTable = $this->database->getTable($row['foreign_table']);
 236              $foreignColumn = $foreignTable->getColumn($row['foreign_column']);
 237  
 238              $localTable   = $this->database->getTable($row['local_table']);    
 239              $localColumn   = $localTable->getColumn($row['local_column']);
 240  
 241              if (!isset($this->foreignKeys[$name])) {
 242                  $this->foreignKeys[$name] = new ForeignKeyInfo($name);
 243              }
 244  
 245              switch ( $row[ 'on_delete' ] )
 246              {
 247                  case 'CASCADE':
 248                      $onDelete    = ForeignKeyInfo::CASCADE;
 249                      break;
 250  
 251                  case 'SET NULL':
 252                      $onDelete    = ForeignKeyInfo::SETNULL;
 253                      break;
 254  
 255                  default:
 256                  case 'NO ACTION':
 257                      $onDelete    = ForeignKeyInfo::NONE;
 258                      break;
 259              }
 260  
 261              // addReference( local, foreign, onDelete, onUpdate )
 262              // Oracle doesn't support 'on update'
 263              $this->foreignKeys[ $name ]->addReference(
 264                  $localColumn
 265                  , $foreignColumn
 266                  , $onDelete
 267              );
 268          }
 269          
 270          $this->fksLoaded = true;
 271      }
 272      
 273  }