postgresql primarykeys foreignkeys tablenames screenfieldlength - Mailing list pgsql-php

Any suggestions or improvements to the following code would be much
appreciated. Please send me an email:

    function kgtables ()
        {
        if ($this->connection)
            {
            $tablelist = "";
            $sql = "SELECT " .
                        "ic.relname " .
                    "FROM " .
                        "pg_class ic " .
                    "WHERE " .
                        "ic.relname not like 'pg%' " .
                        "AND ic.relname not like '%pk' " .
                        "AND ic.relname not like '%idx' ";
            $tablelist = pg_query ($this->connection, $sql);
            $this->num_tables = pg_num_rows($tablelist);
            for ($i=0; $i < $this->num_tables; $i++)
                {
                $r = pg_fetch_row($tablelist);
                $obj->{$i + 1} = $r[0];
                }
            pg_free_result ($tablelist);
            return $obj;
            }
        else
            {
            echo 'Error: failed to name the tables <br />';
            return 0;
            }
        }

    function kgprimarykeys ($tablename = "")
        {
        if ( $this->connection && $tablename != "" )
            {
            $keylist = "";
            $sql = "SELECT " .
                        "ic.relname AS index_name, " .
                        "bc.relname AS tab_name, " .
                        "ta.attname AS column_name, " .
                        "i.indisunique AS unique_key, " .
                        "i.indisprimary AS primary_key " .
                    "FROM " .
                        "pg_class bc, " .
                        "pg_class ic, " .
                        "pg_index i, " .
                        "pg_attribute ta, " .
                        "pg_attribute ia " .
                    "WHERE " .
                        "bc.oid = i.indrelid " .
                        "AND ic.oid = i.indexrelid " .
                        "AND ia.attrelid = i.indexrelid " .
                        "AND ta.attrelid = bc.oid " .
                        "AND bc.relname = '" . $tablename . "' " .
                        "AND ta.attrelid = i.indrelid " .
                        "AND ta.attnum = i.indkey[ia.attnum-1] " .
                    "ORDER BY " .
                        "index_name, tab_name, column_name";
            $keylist = pg_query ($this->connection, $sql);
            $this->num_primarykeys = pg_num_rows($keylist);
            $j = 1;
            for ($i=0; $i < $this->num_primarykeys; $i++)
                {
                $r = pg_fetch_row($keylist);
//                echo "Primary Key: $r[0], $r[1], $r[2], $r[3], $r[4], </br>";
                if ( $r[4] == TRUE)
                    {
                    $obj->{$j} = $r[2];
                    $j++;
                    }
                }
            pg_free_result ($keylist);
            return $obj;
            }
        else
            {
            echo 'Error: failed to name the primary keys in ' . $tablename . '<br
/>';
            return 0;
            }
        }

    function kgforeignkeys ($tablename = "" )
        {
        if ( $this->connection && $tablename != "" )
            {
            $keylist = "";
            $sql = "SELECT conname,
  pg_catalog.pg_get_constraintdef(oid) as condef
                        FROM pg_catalog.pg_constraint r
                        WHERE r.conrelid = (SELECT c.oid
                            FROM pg_catalog.pg_class c
                            LEFT JOIN pg_catalog.pg_namespace n
                            ON n.oid = c.relnamespace
                            WHERE pg_catalog.pg_table_is_visible(c.oid)
                                  AND c.relname ~ '^" . $tablename . "$' )
                        AND r.contype = 'f'";

            $keylist = pg_query ($this->connection, $sql);
            $num_rows = pg_num_rows($keylist);
            for ($i=0; $i < $num_rows; $i++)
                {
                $r = pg_fetch_row($keylist);
//                echo "Field: $r[0], $r[1] </br>";
                $phrase = split("\(|\)", $r[1]);
                echo "Phrase: $phrase[0], $phrase[1], $phrase[2], $phrase[3],
$phrase[4] </br>";
//                $obj->{$i} = $len[1];
                $kgArr[$i][0][0] = $tablename;
                $word1 = split(",", $phrase[1]);
                echo (count($word1));
                echo "Word: $word1[0]; $word1[1] </br>";
                for ($j=1; $j <= count($word1); $j++)
                    {
                    $kgArr[$i][0][$j] = trim($word1[$j - 1]);
                    }
                $kgArr[$i][1][0] = trim(Substr($phrase[2], strrpos($phrase[2], "
")));
                $word2 = split(",", $phrase[3]);
                echo (count($word2));
                echo "Word: $word2[0]; $word2[1] </br>";
                for ($j=1; $j <= count($word2); $j++)
                    {
                    $kgArr[$i][1][$j] = trim($word2[$j - 1]);
                    }
                }
            pg_free_result ($keylist);
            return $kgArr;
            }
        else
            {
            echo 'Error: failed to obtain the foreign keys in ' . $tablename .
'<br />';
            return 0;
            }
        }

    function kgfield_length ($field_name = "", $tablename = "" )
        {
        if ( $this->connection && $tablename != "" )
            {
            $keylist = "";
            $sql = "SELECT a.attname, pg_catalog.format_type(a.atttypid,
a.atttypmod)
                        FROM pg_catalog.pg_attribute a
                        WHERE a.attrelid = (SELECT c.oid
                            FROM pg_catalog.pg_class c
                            LEFT JOIN pg_catalog.pg_namespace n
                            ON n.oid = c.relnamespace
                            WHERE pg_catalog.pg_table_is_visible(c.oid)
                                  AND c.relname ~ '^" . $tablename . "$' )
                        AND a.attnum > 0
                        AND NOT a.attisdropped
                        ORDER BY a.attnum";
            $keylist = pg_query ($this->connection, $sql);
            $this->num_fields = pg_num_rows($keylist);
            for ($i=0; $i < $this->num_fields; $i++)
                {
                $tempLen = 0;
                $r = pg_fetch_row($keylist);
//                echo "Field: $r[0], $r[1] </br>";
                $len = split("\(|\)", $r[1]);
                echo "Length: $len[0], $len[1], $len[2], $len[3] </br>";
                if (trim($len[0]) == "character" || trim($len[0]) == "character
varying")
                    {
                    $tempLen = $len[1];
                    }
                else
                    {
                    switch (trim($len[0]))
                        {
                        case "text":
                            $tempLen = 64;
                            break;
                        case "boolean":
                            $tempLen = 1;
                            break;
                        case "smallint":
                            $tempLen = 6;
                            break;
                        case "integer":
                            $tempLen = 10;
                            break;
                        case "bigint":
                            $tempLen = 18;
                            break;
                        case "timestamp without time zone":
                            $tempLen = 20;
                            break;
                        case "timestamp with time zone":
                            $tempLen = 32;
                            break;
                        case "interval":
                            $tempLen = 10;
                            break;
                        case "date":
                            $tempLen = 10;
                            break;
                        case "numeric":
                            $tempLen = trim(Substr($len[1], 0, strpos($len[1], ",")));
                            echo $tempLen;
                            break;
                        case "decimal":
                            $tempLen = trim(Substr($len[1], 0, strpos($len[1], ",")));
                            break;
                        case "real":
                            $tempLen = 10;
                            break;
                        case "double precision":
                            $tempLen = 20;
                            break;
                        }
                    }
                $obj->{$r[0]} = $tempLen;
                }
            pg_free_result ($keylist);
            return $obj;
            }
        else
            {
            echo 'Error: failed to obtain the field length in ' . $tablename .
'<br />';
            return 0;
            }
        }