Thread: 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; } }
Re: postgresql primarykeys foreignkeys tablenames screenfieldlength
From
"Christopher Kings-Lynne"
Date:
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) >
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) > > > >