Home | Classes | Modules | Annotated | Hierarchy | Functions | Files

OCI8TableInfo.php

Go to the documentation of this file.

<?php
/*
 *  $Id: OCI8TableInfo.php,v 1.8 2004/03/20 04:16:50 hlellelid Exp $
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
 * and is licensed under the LGPL. For more information please see
 * <http://creole.phpdb.org>.
 */
 
require_once 'creole/metadata/TableInfo.php';

class OCI8TableInfo extends TableInfo {
    
    private $schema;
        
    public function __construct(OCI8DatabaseInfo $database, $name)
    {
        $this->schema = $database->getSchema();
        parent::__construct($database, $name);
    }
    
    protected function initColumns() 
    {
        
        include_once 'creole/metadata/ColumnInfo.php';
        include_once 'creole/drivers/oracle/OCI8Types.php';
        

        // To get all of the attributes we need, we'll actually do 
        // two separate queries.  The first gets names and default values
        // the second will fill in some more details.
        
        $sql = "SELECT  column_name,
                data_type,
                data_precision,
                data_length,
                data_default,
                nullable
            FROM    all_tab_columns
            WHERE   table_name = upper('{$this->name}')
                AND OWNER = '{$this->schema}'";

        $statement = @OCIParse($this->dblink,$sql);
                $success = @OCIExecute($statement,OCI_DEFAULT);
                if (!$success) {
            throw new SQLException("Could Not Get Columns");
                }

        $row = array();
        while (OCIFetchInto($statement, $row, OCI_ASSOC)) {
            $row = array_change_key_case($row,CASE_LOWER);
            // FIXME -- add support for precision/scale from query above (replace 'null' param w/ precision value).
            $this->columns[$row['column_name']] = new ColumnInfo($this, $row['column_name'], OCI8Types::getType($row['data_type']), $row['data_type'], $row['data_length'], null, $row['nullable'], $row['data_default']);
        }
                
        $this->colsLoaded = true;
    }
    
    protected function initPrimaryKey()
    {
        include_once 'creole/metadata/PrimaryKeyInfo.php';
        
        // columns have to be loaded first
        if (!$this->colsLoaded) $this->initColumns();
        

        // Primary Keys Query
        $sql = "SELECT a.owner, a.table_name,
                            a.constraint_name, a.column_name
                        FROM all_cons_columns a, all_constraints b
                        WHERE b.constraint_type = 'P'
                        AND a.constraint_name = b.constraint_name
                        AND b.table_name = '{$this->name}'
            AND b.owner = '{$this->schema}'
            ";


        $statement = @OCIParse($this->dblink,$sql);
        $success = @OCIExecute($statement,OCI_DEFAULT);
        if (!$success) {
            throw new SQLException("Could Not Get Primary Keys");
        }

        $row = array();
        while (OCIFetchInto($statement, $row, OCI_ASSOC)) {            
            $row = array_change_key_case($row,CASE_LOWER);

            $name = $row['column_name'];

            if (!isset($this->primaryKey)) {
                $this->primaryKey = new PrimaryKeyInfo($name);
            }

            $this->primaryKey->addColumn($this->columns[$name]);
        }
        
        $this->pkLoaded = true;
    }
    
    protected function initIndexes() {
    
        include_once 'creole/metadata/IndexInfo.php';    

        // columns have to be loaded first
        if (!$this->colsLoaded) $this->initColumns();        

        // Indexes
        $sql = "SELECT
            index_name,
            table_name,
            index_type,
            uniqueness
            FROM all_indexes
            WHERE table_owner = '{$this->schema}'
            AND table_name = '{$this->name}'
            AND index_name NOT IN (SELECT
                    constraint_name
                    FROM all_constraints
                    WHERE constraint_type = 'P')";

        $statement = @OCIParse($this->dblink,$sql);
        $success = @OCIExecute($statement,OCI_DEFAULT);
        if (!$success) {
            throw new SQLException("Could Not Get Primary Keys");
        }


        // Loop through the returned results, grouping the same key_name together
        // adding each column for that key.

        $row = array();
        while (OCIFetchInto($statement, $row, OCI_ASSOC)) {
            $row = array_change_key_case($row,CASE_LOWER);

            $name = $row['index_name'];            

            if (!isset($this->indexes[$name])) {
                $this->indexes[$name] = new IndexInfo($name);
            }

            $this->indexes[$name]->addColumn($this->columns[ $name ]);
        }
        
                
        $this->indexesLoaded = true;
    }
    
    protected function initForeignKeys() {
        
        include_once 'creole/metadata/ForeignKeyInfo.php';    

        // columns have to be loaded first
        if (!$this->colsLoaded) $this->initColumns();        
        
        // Foreign keys
        $sql = "SELECT a.owner AS local_owner,
                a.table_name AS local_table,
                c.column_name AS local_column,
                a.constraint_name AS foreign_key_name,
                b.owner AS foreign_owner,
                b.table_name AS foreign_table,
                d.column_name AS foreign_column,
                b.constraint_name AS foreign_constraint_name
            FROM all_constraints a, all_constraints b,
                all_cons_columns c, all_cons_columns d
            WHERE a.r_constraint_name = b.constraint_name
                AND c.constraint_name = a.constraint_name
                AND d.constraint_name = b.constraint_name
                AND a.r_owner = b.owner
                AND a.constraint_type='R'
                            AND a.table_name = '{$this->name}'
                            AND a.owner = '{$this->schema}' ";


        $statement = @OCIParse($this->dblink,$sql);
        $success = @OCIExecute($statement,OCI_DEFAULT);
        if (!$success) {
            throw new SQLException("Could Not Get Primary Keys");
        }



        // Loop through the returned results, grouping the same key_name together
        // adding each column for that key.

        $row = array();
        while (OCIFetchInto($statement, $row, OCI_ASSOC)) {
            $row = array_change_key_case($row,CASE_LOWER);

            $name = $row['foreign_key_name'];            

            $foreignTable = $this->database->getTable($row['foreign_table']);
            $foreignColumn = $foreignTable->getColumn($row['foreign_column']);


            $localTable   = $this->database->getTable($row['local_table']);    
            $localColumn   = $localTable->getColumn($row['local_column']);


            if (!isset($this->foreignKeys[$name])) {
                $this->foreignKeys[$name] = new ForeignKeyInfo($name);
            }
            $this->foreignKeys[$name]->addReference($localColumn,$foreignColumn);
        }
        
        $this->fksLoaded = true;
    }
    
}

---

This file is part of the Creole[php5] library.