Re: postgresql primarykeys foreignkeys tablenames screenfieldlength

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



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@xxxxxxxxxxxxxx
> [mailto:pgsql-php-owner@xxxxxxxxxxxxxx]On Behalf Of Kevin Gordon
> Sent: Tuesday, 21 January 2003 2:58 PM
> To: pgsql-php@xxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxx)
>



[Index of Archives]     [Postgresql General]     [Postgresql Admin]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Backpacking]     [Postgresql Jobs]

  Powered by Linux