From 899ff20491c3cc578cbc9fd13b1f1351d3038907 Mon Sep 17 00:00:00 2001 From: soranzo Date: Fri, 14 May 2004 01:16:13 +0000 Subject: [PATCH] Bryan Encina's new patch for view wizard: 7.3 support and bug fixes --- classes/database/Postgres.php | 4 +- classes/database/Postgres71.php | 4 +- classes/database/Postgres73.php | 71 ++++++++++++++++++++++++++++++++- classes/database/Postgres74.php | 45 +-------------------- lang/english.php | 3 +- lang/recoded/english.php | 3 +- views.php | 19 +++++---- 7 files changed, 93 insertions(+), 56 deletions(-) diff --git a/classes/database/Postgres.php b/classes/database/Postgres.php index 93f91471..052211e7 100755 --- a/classes/database/Postgres.php +++ b/classes/database/Postgres.php @@ -4,7 +4,7 @@ * A class that implements the DB interface for Postgres * Note: This class uses ADODB and returns RecordSets. * - * $Id: Postgres.php,v 1.201 2004/05/12 15:30:00 chriskl Exp $ + * $Id: Postgres.php,v 1.202 2004/05/14 01:16:14 soranzo Exp $ */ // @@@ THOUGHT: What about inherits? ie. use of ONLY??? @@ -127,6 +127,8 @@ class Postgres extends BaseDB { '~' => 'i', '!~' => 'i', '~*' => 'i', '!~*' => 'i', 'IS NULL' => 'p', 'IS NOT NULL' => 'p', 'IN' => 'x', 'NOT IN' => 'x'); + //Supported join operations for use with view wizard + var $joinOps = array('INNER JOIN' => 'INNER JOIN'); /** * Constructor * @param $conn The database connection diff --git a/classes/database/Postgres71.php b/classes/database/Postgres71.php index 5f2843d8..81e1d856 100644 --- a/classes/database/Postgres71.php +++ b/classes/database/Postgres71.php @@ -4,7 +4,7 @@ * A class that implements the DB interface for Postgres * Note: This class uses ADODB and returns RecordSets. * - * $Id: Postgres71.php,v 1.55 2004/05/09 09:10:04 chriskl Exp $ + * $Id: Postgres71.php,v 1.56 2004/05/14 01:16:14 soranzo Exp $ */ // @@@ THOUGHT: What about inherits? ie. use of ONLY??? @@ -48,6 +48,8 @@ class Postgres71 extends Postgres { var $selectOps = array('=' => 'i', '!=' => 'i', '<' => 'i', '>' => 'i', '<=' => 'i', '>=' => 'i', 'LIKE' => 'i', 'NOT LIKE' => 'i', 'ILIKE' => 'i', 'NOT ILIKE' => 'i', '~' => 'i', '!~' => 'i', '~*' => 'i', '!~*' => 'i', 'IS NULL' => 'p', 'IS NOT NULL' => 'p', 'IN' => 'x', 'NOT IN' => 'x'); + //Supported join operations for use with view wizard + var $joinOps = array('INNER JOIN' => 'INNER JOIN', 'LEFT JOIN' => 'LEFT JOIN', 'RIGHT JOIN' => 'RIGHT JOIN', 'FULL JOIN' => 'FULL JOIN'); /** * Constructor diff --git a/classes/database/Postgres73.php b/classes/database/Postgres73.php index 805d3a4c..45a63651 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.102 2004/05/12 22:40:49 soranzo Exp $ + * $Id: Postgres73.php,v 1.103 2004/05/14 01:16:14 soranzo Exp $ */ // @@@ THOUGHT: What about inherits? ie. use of ONLY??? @@ -938,6 +938,75 @@ class Postgres73 extends Postgres72 { } // Constraint functions + /** + * A function for getting all linking fields on the foreign keys based on the table names + * @param $table array of table names + * @return an array of linked tables and fields + */ + function &getLinkingKeys($arrTables) { + $this->arrayClean($arrTables); + $maxDimension = 1; + + // Properly quote the tables + $tables = "'" . implode("', '", $arrTables) . "'"; + + //7.3 requires some workarounds since ANY doesn't support arrays + $sql = " + SELECT DISTINCT + array_dims(pc.conkey) AS arr_dim, + pgc1.relname AS p_table + FROM + pg_catalog.pg_constraint AS pc, + pg_catalog.pg_class AS pgc1 + WHERE + pc.contype = 'f' + AND (pc.conrelid = pgc1.relfilenode OR pc.confrelid = pgc1.relfilenode) + AND pgc1.relname IN ($tables) + "; + + //parse our output for find the highest dimension of foreign keys since pc.conkey is stored in an array + $rs = $this->selectSet($sql); + while (!$rs->EOF) { + $arrData = explode(':', $rs->fields['arr_dim']); + $tmpDimension = intval(substr($arrData[1], 0, strlen($arrData[1] - 1))); + $maxDimension = $tmpDimension > $maxDimension ? $tmpDimension : $maxDimension; + $rs->MoveNext(); + } + + //we know the highest index for foreign keys thaat conkey goes up to, expand for us in an IN query + $cons_str = '( (pfield.attnum = conkey[1] AND cfield.attnum = confkey[1]) '; + for ($i = 2; $i <= $maxDimension; $i++) { + $cons_str .= "OR (pfield.attnum = conkey[{$i}] AND cfield.attnum = confkey[{$i}]) "; + } + $cons_str .= ') '; + + $sql = " + SELECT + pgc1.relname AS p_table, + pgc2.relname AS f_table, + pfield.attname AS p_field, + cfield.attname AS f_field + FROM + pg_catalog.pg_constraint AS pc, + pg_catalog.pg_class AS pgc1, + pg_catalog.pg_class AS pgc2, + pg_catalog.pg_attribute AS pfield, + pg_catalog.pg_attribute AS cfield + WHERE + pc.contype = 'f' + AND pc.conrelid = pgc1.relfilenode + AND pc.confrelid = pgc2.relfilenode + AND pfield.attrelid = pc.conrelid + AND cfield.attrelid = pc.confrelid + AND $cons_str + AND pgc1.relname IN ($tables) + AND pgc2.relname IN ($tables) + AND pgc1.relnamespace = (SELECT oid FROM pg_catalog.pg_namespace + WHERE nspname='{$this->_schema}') + "; + + return $this->selectSet($sql); + } /** * A helper function for getConstraints that translates diff --git a/classes/database/Postgres74.php b/classes/database/Postgres74.php index 45e45029..a9e1f9ec 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.29 2004/05/12 22:40:49 soranzo Exp $ + * $Id: Postgres74.php,v 1.30 2004/05/14 01:16:14 soranzo Exp $ */ include_once('./classes/database/Postgres73.php'); @@ -195,49 +195,6 @@ class Postgres74 extends Postgres73 { // Constraint functions - /** - * A function for getting all linking columns on the foreign keys based on the table names - * @param $tables Array of table names - * @return A recordset of (constrained table, referenced table, constrained column, referenced column) - * @return -1 $tables isn't an array - */ - function &getLinkingKeys($tables) { - if (!is_array($tables)) return -1; - - $this->arrayClean($tables); - // Properly quote the tables list - $tables_list = "'" . implode("', '", $tables) . "'"; - - // ct = constrained table, rt = referenced table - // cc = constrained column, rc = referenced column - $sql = "SELECT - ct.relname AS p_table, - rt.relname AS f_table, - cc.attname AS p_field, - rc.attname AS f_field - FROM - pg_catalog.pg_constraint c, - pg_catalog.pg_class ct, - pg_catalog.pg_class rt, - pg_catalog.pg_attribute cc, - pg_catalog.pg_attribute rc - WHERE - c.contype = 'f' - AND c.conrelid = ct.relfilenode - AND c.confrelid = rt.relfilenode - AND cc.attrelid = c.conrelid - AND rc.attrelid = c.confrelid - AND cc.attnum = ANY (c.conkey) - AND rc.attnum = ANY (c.confkey) - AND ct.relname IN ($tables_list) - AND rt.relname IN ($tables_list) - AND ct.relnamespace = (SELECT oid FROM pg_catalog.pg_namespace - WHERE nspname='{$this->_schema}') - "; - - return $this->selectSet($sql); - } - /** * Returns a list of all constraints on a table * @param $table The table to find rules for diff --git a/lang/english.php b/lang/english.php index 86f2d6e0..3b98cd77 100755 --- a/lang/english.php +++ b/lang/english.php @@ -4,7 +4,7 @@ * English language file for phpPgAdmin. Use this as a basis * for new translations. * - * $Id: english.php,v 1.141 2004/05/09 08:35:55 chriskl Exp $ + * $Id: english.php,v 1.142 2004/05/14 01:16:14 soranzo Exp $ */ // Language and character set @@ -294,6 +294,7 @@ $lang['strviewname'] = 'View name'; $lang['strviewneedsname'] = 'You must give a name for your view.'; $lang['strviewneedsdef'] = 'You must give a definition for your view.'; + $lang['strviewneedsfields'] = 'Please select the fields you want selected in your view.'; $lang['strviewcreated'] = 'View created.'; $lang['strviewcreatedbad'] = 'View creation failed.'; $lang['strconfdropview'] = 'Are you sure you want to drop the view "%s"?'; diff --git a/lang/recoded/english.php b/lang/recoded/english.php index 4a1806a1..1d3d2567 100644 --- a/lang/recoded/english.php +++ b/lang/recoded/english.php @@ -4,7 +4,7 @@ * English language file for phpPgAdmin. Use this as a basis * for new translations. * - * $Id: english.php,v 1.94 2004/05/09 08:35:55 chriskl Exp $ + * $Id: english.php,v 1.95 2004/05/14 01:16:15 soranzo Exp $ */ // Language and character set @@ -294,6 +294,7 @@ $lang['strviewname'] = 'View name'; $lang['strviewneedsname'] = 'You must give a name for your view.'; $lang['strviewneedsdef'] = 'You must give a definition for your view.'; + $lang['strviewneedsfields'] = 'Please select the fields you want selected in your view.'; $lang['strviewcreated'] = 'View created.'; $lang['strviewcreatedbad'] = 'View creation failed.'; $lang['strconfdropview'] = 'Are you sure you want to drop the view "%s"?'; diff --git a/views.php b/views.php index 92a13d9f..99a8f06b 100644 --- a/views.php +++ b/views.php @@ -3,7 +3,7 @@ /** * Manage views in a database * - * $Id: views.php,v 1.32 2004/05/12 22:40:49 soranzo Exp $ + * $Id: views.php,v 1.33 2004/05/14 01:16:13 soranzo Exp $ */ // Include application functions @@ -186,7 +186,7 @@ $rsLinkKeys = $data->getLinkingKeys($_POST['formTables']); // Update tblcount if we have more foreign keys than tables (perhaps in the case of composite foreign keys) - $tblCount = $rsLinkKeys->recordCount() > $tblCount ? $rsLinkKeys->recordCount() : $tblCount; + $linkCount = $rsLinkKeys->recordCount() > $tblCount ? $rsLinkKeys->recordCount() : $tblCount; // Get fieldnames for ($i = 0; $i < $tblCount; $i++) { @@ -218,11 +218,10 @@ echo "\n\n\n
\n"; // Output the Linking keys combo boxes - $arrLinkOperators = array('INNER JOIN' => 'INNER JOIN', 'LEFT JOIN' => 'LEFT JOIN', 'RIGHT JOIN' => 'RIGHT JOIN'); echo "\n"; echo ""; $rowClass = 'data1'; - for ($i = 0; $i <= $tblCount; $i++) { + for ($i = 0; $i < $linkCount; $i++) { // Initialise variables if (!isset($formLink[$i]['operator'])) $formLink[$i]['operator'] = 'INNER JOIN'; echo "\n\n\n"; $rowClass = $rowClass == 'data1' ? 'data2' : 'data1'; @@ -366,7 +365,9 @@ global $data, $lang; // Check that they've given a name and fields they want to select - if (!strlen($_POST['formView']) || !isset($_POST['formFields']) || !count($_POST['formFields']) ) doSetParamsCreate($lang['strviewneedsdef']); + + if (!strlen($_POST['formView']) ) doSetParamsCreate($lang['strviewneedsname']); + else if (!isset($_POST['formFields']) || !count($_POST['formFields']) ) doSetParamsCreate($lang['strviewneedsfields']); else { $selTables = implode(', ', $_POST['formTables']); $selFields = implode(', ', $_POST['formFields']); @@ -396,7 +397,11 @@ if ( (!in_array($curLink, $arrJoined) && in_array($tbl1, $arrUsedTbls)) || !count($arrJoined) ) { - $linkFields .= strlen($linkFields) ? "{$curLink['operator']} $tbl2 ON ({$curLink['leftlink']} = {$curLink['rightlink']}) " : "$tbl1 {$curLink['operator']} $tbl2 ON ({$curLink['leftlink']} = {$curLink['rightlink']}) "; + //make sure for multi-column foreign keys that we use a table alias tables joined to more than once + //this can (and should be) more optimized for multi-column foreign keys + $adj_tbl2 = in_array($tbl2, $arrUsedTbls) ? "$tbl2 AS alias_ppa_" . mktime() : $tbl2; + + $linkFields .= strlen($linkFields) ? "{$curLink['operator']} $adj_tbl2 ON ({$curLink['leftlink']} = {$curLink['rightlink']}) " : "$tbl1 {$curLink['operator']} $adj_tbl2 ON ({$curLink['leftlink']} = {$curLink['rightlink']}) "; $arrJoined[] = $curLink; if (!in_array($tbl1, $arrUsedTbls) ) $arrUsedTbls[] = $tbl1; if (!in_array($tbl2, $arrUsedTbls) ) $arrUsedTbls[] = $tbl2; -- 2.39.5
{$lang['strviewlink']}
\n"; @@ -238,7 +237,7 @@ } echo GUI::printCombo($arrFields, "formLink[$i][leftlink]", true, $curLeftLink, false ); - echo GUI::printCombo($arrLinkOperators, "formLink[$i][operator]", true, $formLink[$i]['operator']); + echo GUI::printCombo($data->joinOps, "formLink[$i][operator]", true, $formLink[$i]['operator']); echo GUI::printCombo($arrFields, "formLink[$i][rightlink]", true, $curRightLink, false ); echo "