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

PgSQLTableInfo.php

Go to the documentation of this file.

<?php
/*
 *  $Id: PgSQLTableInfo.php,v 1.24 2004/04/09 20:12:12 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 PgSQLTableInfo extends TableInfo {
        
    protected function initColumns() {
        
        include_once 'creole/metadata/ColumnInfo.php';
        include_once 'creole/drivers/pgsql/PgSQLTypes.php';
        
        // Get any default values for columns        
        $result = pg_query($this->dblink, "SELECT d.adnum as num, d.adsrc as def from pg_attrdef d, pg_class c where d.adrelid=c.oid and c.relname='".$this->name."' order by d.adnum");
        
        if (!$result) {
            throw new SQLException("Could not get defaults for columns in table: " . $this->name, pg_last_error($this->dblink));
        }
        
        $defaults = array();
        while($row = pg_fetch_assoc($result)) {
            // [HL] for now I am going to not add default
            // values that are nextval(...) sequence values.
            // We need to resolve on a larger level whether these should
            // be returned.  Maybe instead indicating that these columns are 
            // sequences would be appropriate...
            if (!preg_match('/^nextval\(/', $row['def'])) {
                $defaults[ $row['num'] ] = $row['def'];
            }
        }
        
        // Get the columns, types, etc.
        // based on SQL from ADOdb
        $result = pg_query($this->dblink, "SELECT    a.attname,
                                    t.typname,
                                    a.attlen,
                                    a.atttypmod,
                                    a.attnotnull,
                                    a.atthasdef,
                                    a.attnum,
                                    CAST(
                                         CASE WHEN t.typtype = 'd' THEN
                                           CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
                                                WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
                                                ELSE null END
                                         ELSE
                                           CASE WHEN a.atttypid IN (21, 23, 20) THEN 0
                                                WHEN a.atttypid IN (1700) THEN (a.atttypmod - 4) & 65535
                                                ELSE null END
                                         END
                                         AS int) AS numeric_scale
                            FROM     pg_class c, 
                                    pg_attribute a,
                                    pg_type t 
                            WHERE    relkind = 'r' AND 
                                    c.relname='".$this->name."' AND 
                                    a.attnum > 0 AND 
                                    a.atttypid = t.oid AND 
                                    a.attrelid = c.oid 
                            ORDER BY a.attnum");
        
        if (!$result) {
            throw new SQLException("Could not list fields for table: " . $this->name, pg_last_error($this->dblink));
        }
            
        while($row = pg_fetch_assoc($result)) {
            $name = $row['attname'];
            $type = $row['typname'];
            $size = $row['attlen'];
            $scale = $row['numeric_scale'];
            if ($size <= 0) {
                // maxlen for varchar is 4 larger than actual max length
                $size = $row['atttypmod'] - 4; 
                if ($size <= 0) {
                    $size = null;
                }
            }
                        
            $is_nullable = ($row['attnotnull'] == 't' ? true : false);
            $default = ($row['atthasdef'] == 't' && isset( $defaults[ $row['attnum'] ]) ? $defaults[ $row['attnum'] ] : null);
            $this->columns[$name] = new ColumnInfo($this, $name, PgSQLTypes::getType($type), $type, $size, $scale, $is_nullable, $default);
        }
        
        $this->colsLoaded = true;
    }
    
    protected function initForeignKeys()
    {
        include_once 'creole/metadata/ForeignKeyInfo.php';
                
        $result = pg_query($this->dblink, "SELECT  tgargs
                            FROM    pg_trigger
                            WHERE   tgrelid = (select oid from pg_class where relname='".$this->name."')
                                    AND tgfoid = (select oid from pg_proc where proname='RI_FKey_check_ins')");
        if (!$result) {
            throw new SQLException("Could not list foreign keys for table: " . $this->name, pg_last_error($this->dblink));
        }
        
        // tgargs ($row[0]) looks like this:
        // _fk_1\000book\000publisher\000UNSPECIFIED\000publisher_id\000publisher_id\000
        //  name?   table     references     ?           key              fkey                                
        //    0       1           2          3            4                 5
        
        while($row = pg_fetch_row($result)) {
            
            $parts = explode('\000', $row[0]);
            $name = $parts[0];
            $local_table = $parts[1];
            $foreign_table = $parts[2];
            $local_column = $parts[4];
            $foreign_column = $parts[5];
            
            $foreignTable = $this->database->getTable($foreign_table);
            $foreignColumn = $foreignTable->getColumn($foreign_column);

            $localTable   = $this->database->getTable($local_table);    
            $localColumn   = $localTable->getColumn($local_column);
            
            if (!isset($this->foreignKeys[$name])) {
                $this->foreignKeys[$name] = new ForeignKeyInfo($name);
            }
            $this->foreignKeys[$name]->addReference($localColumn, $foreignColumn);
        }
                
        $this->fksLoaded = true;
    }
    
    protected function initIndexes()
    {
        include_once 'creole/metadata/IndexInfo.php';

        // columns have to be loaded first
        if (!$this->colsLoaded) $this->initColumns();
        
        // FIXME -- try this out!
        // then figure out if we need to add any information
        // to our index object to accommodate more complex backends
        
        $result = pg_query($this->dblink, "SELECT c.relname as tablename, c.oid, c2.relname as indexname,

                            i.indisprimary, i.indisunique, pg_catalog.pg_get_indexdef(i.indexrelid) FROM

                            pg_catalog.pg_class c,

                            pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.oid = i.indrelid AND

                            i.indexrelid = c2.oid AND c.relname = ".$this->name." ORDER BY i.indisprimary DESC, i.indisunique DESC,

                            c2.relname");
        

        if (!$result) {
            throw new SQLException("Could not list indexes keys for table: " . $this->name, pg_last_error($this->dblink));
        }
        
        while($row = pg_fetch_assoc($result)) {
            $name = $row["indexname"];
            if (!isset($this->indexes[$name])) {
                $this->indexes[$name] = new IndexInfo($name);
            }
            $this->indexes[$name]->addColumn($this->columns[ $name ]);            
        }
        
        $this->indexesLoaded = true;
    }
    
    protected function initPrimaryKey() {

        include_once 'creole/metadata/PrimaryKeyInfo.php';
        
        
        // columns have to be loaded first
        if (!$this->colsLoaded) $this->initColumns();
        
        // Primary Keys
        $result = pg_query($this->dblink, "select ta.attname, ia.attnum
                                            from pg_attribute ta, pg_attribute ia, pg_class c, pg_index i
                                            where c.relname = '".$this->name."_pkey'
                                                AND c.oid = i.indexrelid
                                                AND ia.attrelid = i.indexrelid
                                                AND ta.attrelid = i.indrelid
                                                AND ta.attnum = i.indkey[ia.attnum-1]
                                            ORDER BY ia.attnum");
        
        if (!$result) {
            throw new SQLException("Could not list primary keys for table: " . $this->name, pg_last_error($this->dblink));
        }
        
        // Loop through the returned results, grouping the same key_name together
        // adding each column for that key.
                    
        while($row = pg_fetch_assoc($result)) {
            $name = $row["attname"];
            if (!isset($this->primaryKey)) {
                $this->primaryKey = new PrimaryKeyInfo($name);
            }
            $this->primaryKey->addColumn($this->columns[ $name ]);
        }            
        
        $this->pkLoaded = true;
    }
    
}

---

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