Re: postgresql primarykeys foreignkeys tablenames - Mailing list pgsql-php

Many thanks Chris for your suggestion re phppgadmin. The code very
interesting. I could not find any code re foreign keys but a good source
of examples and techique. My code works ok. Just thought it may be
improved. Thanks again.
Kevin

On Tue, 2003-01-21 at 20:25, Christopher Kings-Lynne wrote:
> I suggest looking at the source code for postgres's JDBC driver or WebDB
> (http://phpgpadmin.sourceforge.net/).
>
> Chris
>
> > -----Original Message-----
> > From: pgsql-php-owner@postgresql.org
> > [mailto:pgsql-php-owner@postgresql.org]On Behalf Of Kevin Gordon
> > Sent: Tuesday, 21 January 2003 2:58 PM
> > To: pgsql-php@postgresql.org
> > Subject: [PHP] postgresql primarykeys foreignkeys tablenames
> > screenfieldlength
> >
> >
> > 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;
> >             }
> >         }
> >
> >
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
>