From a30d8358d6be2b1e9e02dcc72e85eba736215123 Mon Sep 17 00:00:00 2001 From: ioguix Date: Fri, 28 Dec 2007 16:21:25 +0000 Subject: [PATCH] fix duplicate constraints in the constraints page --- classes/database/Postgres73.php | 65 ++++++++++++++++++++++++++++++++- classes/database/Postgres74.php | 54 ++++++++++++++++++++++++--- classes/database/Postgres82.php | 58 ++++++++++++++--------------- tblproperties.php | 4 +- 4 files changed, 142 insertions(+), 39 deletions(-) diff --git a/classes/database/Postgres73.php b/classes/database/Postgres73.php index 9bf26a09..9e3d789a 100644 --- a/classes/database/Postgres73.php +++ b/classes/database/Postgres73.php @@ -4,7 +4,7 @@ * A class that implements the DB interface for Postgres * Note: This class uses ADODB and returns RecordSets. * - * $Id: Postgres73.php,v 1.184 2007/12/28 15:39:45 ioguix Exp $ + * $Id: Postgres73.php,v 1.185 2007/12/28 16:21:25 ioguix Exp $ */ // @@@ THOUGHT: What about inherits? ie. use of ONLY??? @@ -1739,6 +1739,67 @@ class Postgres73 extends Postgres72 { // Constraint functions + /** + * Returns a list of all constraints on a table + * @param $table The table to find rules for + * @return A recordset + */ + function getConstraints($table) { + $this->clean($table); + + /* This query finds all foreign key and check constraints in the pg_constraint + * table, and unions that with all indexes that are the basis for unique or + * primary key constraints. */ + $sql = " + SELECT conname, consrc, contype, indkey, indisclustered FROM ( + SELECT + conname, + CASE WHEN contype='f' THEN + pg_catalog.pg_get_constraintdef(oid) + ELSE + 'CHECK (' || consrc || ')' + END AS consrc, + contype, + conrelid AS relid, + NULL AS indkey, + FALSE AS indisclustered + FROM + pg_catalog.pg_constraint + WHERE + contype IN ('f', 'c') + UNION ALL + SELECT + pc.relname, + NULL, + CASE WHEN indisprimary THEN + 'p' + ELSE + 'u' + END, + pi.indrelid, + indkey, + pi.indisclustered + FROM + pg_catalog.pg_class pc, + pg_catalog.pg_index pi + WHERE + pc.oid=pi.indexrelid + AND EXISTS ( + SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c + ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) + WHERE d.classid = pc.tableoid AND d.objid = pc.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p') + ) + ) AS sub + WHERE relid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='{$table}' + AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace + WHERE nspname='{$this->_schema}')) + ORDER BY + 1 + "; + + return $this->selectSet($sql); + } + /** * Returns a list of all constraints on a table, * including constraint name, definition, related col and referenced namespace, @@ -1746,7 +1807,7 @@ class Postgres73 extends Postgres72 { * @param $table the table where we are looking for fk * @return a recordset */ - function getConstraints($table) { + function getConstraintsWithFields($table) { global $data; $data->clean($table); diff --git a/classes/database/Postgres74.php b/classes/database/Postgres74.php index 08b4e024..0b87f0ad 100644 --- a/classes/database/Postgres74.php +++ b/classes/database/Postgres74.php @@ -4,7 +4,7 @@ * A class that implements the DB interface for Postgres * Note: This class uses ADODB and returns RecordSets. * - * $Id: Postgres74.php,v 1.70 2007/12/12 04:11:10 xzilla Exp $ + * $Id: Postgres74.php,v 1.71 2007/12/28 16:21:25 ioguix Exp $ */ include_once('./classes/database/Postgres73.php'); @@ -149,6 +149,48 @@ class Postgres74 extends Postgres73 { // Constraint functions + /** + * Returns a list of all constraints on a table + * @param $table The table to find rules for + * @return A recordset + */ + function getConstraints($table) { + $this->clean($table); + + // This SQL is greatly complicated by the need to retrieve + // index clustering information for primary and unique constraints + $sql = "SELECT + pc.conname, + pg_catalog.pg_get_constraintdef(pc.oid, true) AS consrc, + pc.contype, + CASE WHEN pc.contype='u' OR pc.contype='p' THEN ( + SELECT + indisclustered + FROM + pg_catalog.pg_depend pd, + pg_catalog.pg_class pl, + pg_catalog.pg_index pi + WHERE + pd.refclassid=pc.tableoid + AND pd.refobjid=pc.oid + AND pd.objid=pl.oid + AND pl.oid=pi.indexrelid + ) ELSE + NULL + END AS indisclustered + FROM + pg_catalog.pg_constraint pc + WHERE + pc.conrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='{$table}' + AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace + WHERE nspname='{$this->_schema}')) + ORDER BY + 1 + "; + + return $this->selectSet($sql); + } + /** * Returns a list of all constraints on a table, * including constraint name, definition, related col and referenced namespace, @@ -156,7 +198,7 @@ class Postgres74 extends Postgres73 { * @param $table the table where we are looking for fk * @return a recordset */ - function getConstraints($table) { + function getConstraintsWithFields($table) { global $data; $data->clean($table); @@ -602,7 +644,7 @@ class Postgres74 extends Postgres73 { return -5; } - $funcname = $newname; + $funcname = $newname; } // Alter the owner, if necessary @@ -615,7 +657,7 @@ class Postgres74 extends Postgres73 { $this->rollbackTransaction(); return -6; } - } + } } @@ -665,9 +707,9 @@ class Postgres74 extends Postgres73 { $minvalue, $maxvalue, $startvalue, $cachevalue, $cycledvalue); if ($status != 0) return $status; - + /* $schema not supported in pg74 */ - + // if name != seqname, sequence has been renamed in parent $sequence = ($seqrs->fields['seqname'] == $name) ? $seqrs->fields['seqname'] : $name; $this->clean($increment); diff --git a/classes/database/Postgres82.php b/classes/database/Postgres82.php index 66a0be38..5615a663 100644 --- a/classes/database/Postgres82.php +++ b/classes/database/Postgres82.php @@ -3,7 +3,7 @@ /** * PostgreSQL 8.2 support * - * $Id: Postgres82.php,v 1.9 2007/10/30 19:23:19 xzilla Exp $ + * $Id: Postgres82.php,v 1.10 2007/12/28 16:21:25 ioguix Exp $ */ include_once('./classes/database/Postgres81.php'); @@ -11,7 +11,7 @@ include_once('./classes/database/Postgres81.php'); class Postgres82 extends Postgres81 { var $major_version = 8.2; - + // Array of allowed index types var $typIndexes = array('BTREE', 'RTREE', 'GIST', 'GIN', 'HASH'); @@ -30,7 +30,7 @@ class Postgres82 extends Postgres81 { 'schema' => array('CREATE', 'USAGE', 'ALL PRIVILEGES'), 'tablespace' => array('CREATE', 'ALL PRIVILEGES') ); - + // List of characters in acl lists and the privileges they // refer to. var $privmap = array( @@ -46,7 +46,7 @@ class Postgres82 extends Postgres81 { 'C' => 'CREATE', 'T' => 'TEMPORARY', 'c' => 'CONNECT' - ); + ); /** * Constructor @@ -57,7 +57,7 @@ class Postgres82 extends Postgres81 { } // Help functions - + function getHelpPages() { include_once('./help/PostgresDoc82.php'); return $this->help_page; @@ -70,9 +70,9 @@ class Postgres82 extends Postgres81 { */ function getDatabases($currentdatabase = NULL) { global $conf, $misc; - + $server_info = $misc->getServerInfo(); - + if (isset($conf['owned_only']) && $conf['owned_only'] && !$this->isSuperUser($server_info['username'])) { $username = $server_info['username']; $this->clean($username); @@ -93,9 +93,9 @@ class Postgres82 extends Postgres81 { $sql = "SELECT pdb.datname AS datname, pr.rolname AS datowner, pg_encoding_to_char(encoding) AS datencoding, (SELECT description FROM pg_catalog.pg_shdescription pd WHERE pdb.oid=pd.objoid) AS datcomment, (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=pdb.dattablespace) AS tablespace, - pg_catalog.pg_database_size(pdb.oid) as dbsize - FROM pg_catalog.pg_database pdb LEFT JOIN pg_catalog.pg_roles pr ON (pdb.datdba = pr.oid) - WHERE true + pg_catalog.pg_database_size(pdb.oid) as dbsize + FROM pg_catalog.pg_database pdb LEFT JOIN pg_catalog.pg_roles pr ON (pdb.datdba = pr.oid) + WHERE true {$where} {$clause} {$orderby}"; @@ -121,13 +121,13 @@ class Postgres82 extends Postgres81 { $this->clean($newName); $this->clean($newOwner); $this->clean($comment); - + $status = $this->beginTransaction(); if ($status != 0) { $this->rollbackTransaction(); return -1; } - + if ($dbName != $newName) { $status = $this->alterDatabaseRename($dbName, $newName); if ($status != 0) { @@ -142,7 +142,7 @@ class Postgres82 extends Postgres81 { return -2; } - if (trim($comment) != '' ) { + if (trim($comment) != '' ) { $status = $this->setComment('DATABASE', $dbName, '', $comment); if ($status != 0) { $this->rollbackTransaction(); @@ -164,7 +164,7 @@ class Postgres82 extends Postgres81 { } // Tablespace functions - + /** * Retrieves information for all tablespaces * @param $all Include all tablespaces (necessary when moving objects back to the default space) @@ -172,17 +172,17 @@ class Postgres82 extends Postgres81 { */ function getTablespaces($all = false) { global $conf; - + $sql = "SELECT spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, spclocation, (SELECT description FROM pg_catalog.pg_shdescription pd WHERE pg_tablespace.oid=pd.objoid) AS spccomment FROM pg_catalog.pg_tablespace"; - + if (!$conf['show_system'] && !$all) { $sql .= " WHERE spcname NOT LIKE 'pg\\\\_%'"; } - + $sql .= " ORDER BY spcname"; - + return $this->selectSet($sql); } @@ -192,16 +192,16 @@ class Postgres82 extends Postgres81 { */ function getTablespace($spcname) { $this->clean($spcname); - + $sql = "SELECT spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, spclocation, (SELECT description FROM pg_catalog.pg_shdescription pd WHERE pg_tablespace.oid=pd.objoid) AS spccomment FROM pg_catalog.pg_tablespace WHERE spcname='{$spcname}'"; - + return $this->selectSet($sql); } - + // Constraints methods - + /** * Returns a list of all constraints on a table, * including constraint name, definition, related col and referenced namespace, @@ -209,11 +209,11 @@ class Postgres82 extends Postgres81 { * @param $table the table where we are looking for fk * @return a recordset */ - function getConstraints($table) { + function getConstraintsWithFields($table) { global $data; $data->clean($table); - + // get the max number of col used in a constraint for the table $sql = "SELECT DISTINCT max(SUBSTRING(array_dims(c.conkey) FROM E'^\\\[.*:(.*)\\\]$')) as nb @@ -227,11 +227,11 @@ class Postgres82 extends Postgres81 { $rs = $this->selectSet($sql); if ($rs->EOF) $max_col = 0; - else $max_col = $rs->fields['nb']; + else $max_col = $rs->fields['nb']; $sql = ' SELECT - c.contype, c.conname, pg_catalog.pg_get_constraintdef(c.oid, true) AS consrc, + c.contype, c.conname, pg_catalog.pg_get_constraintdef(c.oid, true) AS consrc, ns1.nspname as p_schema, r1.relname as p_table, ns2.nspname as f_schema, r2.relname as f_table, f1.attname as p_field, f2.attname as f_field, pg_catalog.obj_description(c.oid, \'pg_constraint\') AS constcomment @@ -247,19 +247,19 @@ class Postgres82 extends Postgres81 { LEFT JOIN ( pg_catalog.pg_class AS r2 JOIN pg_catalog.pg_namespace AS ns2 ON (r2.relnamespace=ns2.oid) ) ON (c.confrelid=r2.oid) - LEFT JOIN pg_catalog.pg_attribute AS f2 ON + LEFT JOIN pg_catalog.pg_attribute AS f2 ON (f2.attrelid=r2.oid AND ((c.confkey[1]=f2.attnum AND c.conkey[1]=f1.attnum)'; for ($i = 2; $i <= $rs->fields['nb']; $i++) $sql.= "OR (c.confkey[$i]=f2.attnum AND c.conkey[$i]=f1.attnum)"; $sql .= sprintf(")) - WHERE + WHERE r1.relname = '%s' AND ns1.nspname='%s' ORDER BY 1", $table, $this->_schema); return $this->selectSet($sql); } - + // Capabilities function hasSharedComments() {return true;} function hasCreateTableLikeWithConstraints() {return true;} diff --git a/tblproperties.php b/tblproperties.php index 20ecc813..d5643009 100644 --- a/tblproperties.php +++ b/tblproperties.php @@ -3,7 +3,7 @@ /** * List tables in a database * - * $Id: tblproperties.php,v 1.90 2007/12/28 15:28:57 ioguix Exp $ + * $Id: tblproperties.php,v 1.91 2007/12/28 16:21:25 ioguix Exp $ */ // Include application functions @@ -459,7 +459,7 @@ // Get columns $attrs = $data->getTableAttributes($_REQUEST['table']); // Get Pk & Constraints - $ck = $data->getConstraints($_REQUEST['table']); + $ck = $data->getConstraintsWithFields($_REQUEST['table']); // Show comment if any if ($tdata->fields['relcomment'] !== null) -- 2.39.5