From 36ab0b9c2075e7207e2a4c551901e9b2d2c533ad Mon Sep 17 00:00:00 2001 From: chriskl Date: Mon, 8 Sep 2003 09:26:17 +0000 Subject: [PATCH] allow editing/deleting rows by oid again. guards are in place against duplicate oids. skip indexes that are functional or partial. prefer primary keys, unique keys and then oids. fix for < 7.3. xhtml fixes from nicola. new translation string :( --- BUGS | 4 -- HISTORY | 3 + classes/database/BaseDB.php | 41 ++++++++++++-- classes/database/Postgres.php | 20 +++++-- classes/database/Postgres73.php | 20 +++++-- lang/english.php | 3 +- lang/recoded/english.php | 3 +- tables.php | 97 +++++++++++++++++++-------------- 8 files changed, 127 insertions(+), 64 deletions(-) diff --git a/BUGS b/BUGS index bc3f654c..e69de29b 100644 --- a/BUGS +++ b/BUGS @@ -1,4 +0,0 @@ -* Get table comments for pre-7.3 -* make getrowidentifier prefer unique indexes that don't allow nulls, - and don't use partial indexes -* Use explicit session names diff --git a/HISTORY b/HISTORY index de036528..3e9fdccd 100644 --- a/HISTORY +++ b/HISTORY @@ -43,6 +43,9 @@ Features: * Spanish update from Martin Marques * Trad. Chinese update from Chih-Hsin Lee * Save selects as views +* Russian update from Step +* Slovak update from Andrej +* Can now safely edit row based on OID. Guards are in place against duplicate OIDs. Bug Fixes: * Lots of NULL value in table dump fixes (XML format changed slightly) diff --git a/classes/database/BaseDB.php b/classes/database/BaseDB.php index da5d63df..e21d131c 100644 --- a/classes/database/BaseDB.php +++ b/classes/database/BaseDB.php @@ -4,7 +4,7 @@ * A class that implements the DB interface for Postgres * Note: This class uses ADODB and returns RecordSets. * - * $Id: BaseDB.php,v 1.27 2003/09/01 03:15:43 chriskl Exp $ + * $Id: BaseDB.php,v 1.28 2003/09/08 09:26:17 chriskl Exp $ */ include_once('classes/database/ADODB_base.php'); @@ -25,7 +25,24 @@ class BaseDB extends ADODB_base { */ function deleteRow($table, $key) { if (!is_array($key)) return -1; - else return $this->delete($table, $key); + else { + // Begin transaction. We do this so that we can ensure only one row is + // deleted + $status = $this->beginTransaction(); + if ($status != 0) { + $this->rollbackTransaction(); + return -1; + } + + $status = $this->delete($table, $key); + if ($status != 0 || $this->conn->Affected_Rows() != 1) { + $this->rollbackTransaction(); + return -2; + } + + // End transaction + return $this->endTransaction(); + } } /** @@ -67,8 +84,24 @@ class BaseDB extends ADODB_base { } else $sql .= " AND \"{$k}\"='{$v}'"; } - } - return $this->execute($sql); + } + + // Begin transaction. We do this so that we can ensure only one row is + // edited + $status = $this->beginTransaction(); + if ($status != 0) { + $this->rollbackTransaction(); + return -1; + } + + $status = $this->execute($sql); + if ($status != 0 || $this->conn->Affected_Rows() != 1) { + $this->rollbackTransaction(); + return -2; + } + + // End transaction + return $this->endTransaction(); } } diff --git a/classes/database/Postgres.php b/classes/database/Postgres.php index 83f39fe1..db0686fa 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.145 2003/09/08 04:35:17 chriskl Exp $ + * $Id: Postgres.php,v 1.146 2003/09/08 09:26:17 chriskl Exp $ */ // @@@ THOUGHT: What about inherits? ie. use of ONLY??? @@ -313,14 +313,22 @@ class Postgres extends BaseDB { $status = $this->beginTransaction(); if ($status != 0) return -1; - $sql = "SELECT indrelid, indkey FROM pg_index WHERE indisprimary AND indrelid=(SELECT oid FROM pg_class WHERE relname='{$table}')"; + // Get the first primary or unique index (sorting primary keys first) that + // is NOT a partial index. + $sql = "SELECT indrelid, indkey FROM pg_index WHERE indisunique AND indrelid=(SELECT oid FROM pg_class + WHERE relname='{$table}') AND indpred='' AND indproc='-' ORDER BY indisprimary DESC LIMIT 1"; $rs = $this->selectSet($sql); - // If none, return an empty array. We can't search for an OID column - // as there's no guarantee that it will be unique. - if ($rs->recordCount() == 0) { + // If none, check for an OID column. Even though OIDs can be duplicated, the edit and delete row + // functions check that they're only modiying a single row. Otherwise, return empty array. + if ($rs->recordCount() == 0) { + // Check for OID column + $temp = array(); + if ($this->hasObjectID($table)) { + $temp = array('oid'); + } $this->endTransaction(); - return array(); + return $temp; } // Otherwise find the names of the keys else { diff --git a/classes/database/Postgres73.php b/classes/database/Postgres73.php index 2c885e04..fa13f9e5 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.62 2003/09/03 05:46:20 chriskl Exp $ + * $Id: Postgres73.php,v 1.63 2003/09/08 09:26:18 chriskl Exp $ */ // @@@ THOUGHT: What about inherits? ie. use of ONLY??? @@ -242,16 +242,24 @@ class Postgres73 extends Postgres72 { $status = $this->beginTransaction(); if ($status != 0) return -1; + // Get the first primary or unique index (sorting primary keys first) that + // is NOT a partial index. $sql = "SELECT indrelid, indkey FROM pg_catalog.pg_index WHERE indisunique AND indrelid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='{$table}' AND - relnamespace=(SELECT oid FROM pg_catalog.pg_namespace WHERE nspname='{$this->_schema}'))"; + relnamespace=(SELECT oid FROM pg_catalog.pg_namespace WHERE nspname='{$this->_schema}')) + AND indpred='' AND indproc='-' ORDER BY indisprimary DESC LIMIT 1"; $rs = $this->selectSet($sql); - // If none, return an empty array. We can't search for an OID column - // as there's no guarantee that it will be unique. - if ($rs->recordCount() == 0) { + // If none, check for an OID column. Even though OIDs can be duplicated, the edit and delete row + // functions check that they're only modiying a single row. Otherwise, return empty array. + if ($rs->recordCount() == 0) { + // Check for OID column + $temp = array(); + if ($this->hasObjectID($table)) { + $temp = array('oid'); + } $this->endTransaction(); - return array(); + return $temp; } // Otherwise find the names of the keys else { diff --git a/lang/english.php b/lang/english.php index 54c0ca6f..4c1f5f58 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.102 2003/09/08 03:00:12 chriskl Exp $ + * $Id: english.php,v 1.103 2003/09/08 09:26:17 chriskl Exp $ */ // Language and character set @@ -118,6 +118,7 @@ $lang['strinstatement'] = 'In statement:'; $lang['strinvalidparam'] = 'Invalid script parameters.'; $lang['strnodata'] = 'No rows found.'; + $lang['strrownotunique'] = 'No unique identifier for this row.'; // Tables $lang['strtable'] = 'Table'; diff --git a/lang/recoded/english.php b/lang/recoded/english.php index 8b79df16..72a2bea9 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.54 2003/09/08 03:00:13 chriskl Exp $ + * $Id: english.php,v 1.55 2003/09/08 09:26:17 chriskl Exp $ */ // Language and character set @@ -118,6 +118,7 @@ $lang['strinstatement'] = 'In statement:'; $lang['strinvalidparam'] = 'Invalid script parameters.'; $lang['strnodata'] = 'No rows found.'; + $lang['strrownotunique'] = 'No unique identifier for this row.'; // Tables $lang['strtable'] = 'Table'; diff --git a/tables.php b/tables.php index 750eb3b7..7ef542a5 100644 --- a/tables.php +++ b/tables.php @@ -3,7 +3,7 @@ /** * List tables in a database * - * $Id: tables.php,v 1.35 2003/09/05 01:50:27 chriskl Exp $ + * $Id: tables.php,v 1.36 2003/09/08 09:26:17 chriskl Exp $ */ // Include application functions @@ -39,8 +39,7 @@ if ($data->hasWithoutOIDs()) { echo "{$lang['stroptions']}\n"; echo "WITHOUT OIDS\n"; - echo "\n"; + (isset($_REQUEST['withoutoids']) ? ' checked="checked"' : ''), " />WITHOUT OIDS\n"; } echo "\n"; echo "

\n"; @@ -74,8 +73,8 @@ echo "

\n"; // Output table header - echo "\n"; - echo ""; + echo "
{$lang['strfield']}{$lang['strtype']}{$lang['strlength']}{$lang['strnotnull']}{$lang['strdefault']}
\n"; + echo "\n"; for ($i = 0; $i < $_REQUEST['fields']; $i++) { if (!isset($_REQUEST['field'][$i])) $_REQUEST['field'][$i] = ''; @@ -83,28 +82,28 @@ if (!isset($_REQUEST['default'][$i])) $_REQUEST['default'][$i] = ''; echo ""; echo ""; + htmlspecialchars($_REQUEST['field'][$i]), "\" />"; echo ""; echo ""; - echo "\n"; + echo ""; echo ""; + htmlspecialchars($_REQUEST['default'][$i]), "\" />\n"; } echo "
{$lang['strfield']}{$lang['strtype']}{$lang['strlength']}{$lang['strnotnull']}{$lang['strdefault']}
", $i + 1, ". _maxNameLen}\" value=\"", - htmlspecialchars($_REQUEST['field'][$i]), "\">
\n"; @@ -209,14 +208,14 @@ echo "\n"; echo ""; echo "f['attname']), "]\"", - isset($_REQUEST['show'][$attrs->f['attname']]) ? ' checked' : '', " />"; + isset($_REQUEST['show'][$attrs->f['attname']]) ? ' checked="checked"' : '', " />"; echo "", $misc->printVal($attrs->f['attname']), ""; echo "", $misc->printVal($attrs->f['type']), ""; echo ""; // Output null box if the column allows nulls (doesn't look at CHECKs or ASSERTIONS) if (!$attrs->f['attnotnull']) echo "f['attname']}]\"", - isset($_REQUEST['nulls'][$attrs->f['attname']]) ? ' checked' : '', " />"; + isset($_REQUEST['nulls'][$attrs->f['attname']]) ? ' checked="checked"' : '', " />"; else echo " "; echo "", $localData->printField("values[{$attrs->f['attname']}]", @@ -303,14 +302,14 @@ htmlspecialchars($attrs->f['type']), "\" />"; echo "\n"; echo "\n\n"; echo ""; // Output null box if the column allows nulls (doesn't look at CHECKs or ASSERTIONS) if (!$attrs->f['attnotnull']) echo "f['attname']), "]\"", - isset($_REQUEST['nulls'][$attrs->f['attname']]) ? ' checked' : '', " />"; + isset($_REQUEST['nulls'][$attrs->f['attname']]) ? ' checked="checked"' : '', " />"; else echo " "; echo "", $localData->printField("values[{$attrs->f['attname']}]", @@ -366,10 +365,10 @@ echo "

", sprintf($lang['strconfemptytable'], $misc->printVal($_REQUEST['table'])), "

\n"; echo "\n"; - echo "\n"; - echo "\n"; + echo "\n"; + echo "\n"; echo $misc->form; - echo " \n"; + echo " \n"; echo "
\n"; } else { @@ -396,15 +395,15 @@ echo "

", sprintf($lang['strconfdroptable'], $misc->printVal($_REQUEST['table'])), "

\n"; echo "
\n"; - echo "\n"; - echo "\n"; + echo "\n"; + echo "\n"; echo $misc->form; // Show cascade drop option if supportd if ($localData->hasDropBehavior()) { - echo "

{$lang['strcascade']}

\n"; + echo "

{$lang['strcascade']}

\n"; } - echo "\n"; - echo "\n"; + echo "\n"; + echo "\n"; echo "
\n"; } else { @@ -437,6 +436,7 @@ $rs = &$localData->browseRow($_REQUEST['table'], $key); echo "
\n"; + $error = true; if ($rs->recordCount() == 1 && $attrs->recordCount() > 0) { echo "\n"; @@ -464,8 +464,8 @@ htmlspecialchars($attrs->f['type']), "\" />"; echo "\n"; echo ""; + isset($_REQUEST['nulls'][$attrs->f['attname']]) ? ' checked="checked"' : '', " />"; } else echo " "; @@ -487,8 +487,14 @@ $attrs->moveNext(); } echo "
\n"; echo "\n"; // Output null box if the column allows nulls (doesn't look at CHECKs or ASSERTIONS) @@ -475,7 +475,7 @@ $_REQUEST['nulls'][$attrs->f['attname']] = 'on'; } echo "f['attname']}]\"", - isset($_REQUEST['nulls'][$attrs->f['attname']]) ? ' checked' : '', " />

\n"; + $error = false; + } + elseif ($rs->recordCount() != 1) { + echo "

{$lang['strrownotunique']}

\n"; + } + else { + echo "

{$lang['strinvalidparam']}

\n"; } - else echo "

{$lang['strinvalidparam']}

\n"; echo "\n"; echo "\n"; @@ -498,7 +504,8 @@ echo "\n"; echo "\n"; echo "\n"; - echo "

\n"; + echo "

"; + if (!$error) echo "\n"; echo "

\n"; echo "
\n"; } @@ -510,8 +517,10 @@ $_POST['format'], $_POST['types'], unserialize($_POST['key'])); if ($status == 0) doBrowse($lang['strrowupdated']); + elseif ($status == -2) + doEditRow(true, $lang['strrownotunique']); else - doEditRow($lang['strrowupdatedbad']); + doEditRow(true, $lang['strrowupdatedbad']); } } @@ -546,7 +555,9 @@ $status = $localData->deleteRow($_POST['table'], unserialize($_POST['key'])); if ($status == 0) doBrowse($lang['strrowdeleted']); - else + elseif ($status == -2) + doBrowse($lang['strrownotunique']); + else doBrowse($lang['strrowdeletedbad']); } @@ -614,7 +625,7 @@ $key_str .= urlencode("key[{$v}]") . '=' . urlencode($rs->f[$v]); } if ($has_nulls) { - echo " \n \n"; + echo " \n"; } else { echo "href}&sortkey=", urlencode($_REQUEST['sortkey']), "&sortdir=", urlencode($_REQUEST['sortdir']), @@ -682,22 +693,24 @@ if ($tables->recordCount() > 0) { echo "\n"; - echo ""; - echo "\n"; + echo "\n\t\n\t\n"; + echo "\t\n\n"; $i = 0; while (!$tables->EOF) { $id = (($i % 2) == 0 ? '1' : '2'); - echo "\n"; - echo "\n"; - echo "\n\t\n"; + echo "\t\n"; + echo "\t\n"; - echo "\n"; - echo "\n"; - echo "\n"; - echo "\n"; + echo "\t\n"; echo "\n"; $tables->moveNext(); -- 2.39.5
{$lang['strtable']}{$lang['strowner']}{$lang['stractions']}
{$lang['strtable']}{$lang['strowner']}{$lang['stractions']}
", $misc->printVal($tables->f[$data->tbFields['tbname']]), "", $misc->printVal($tables->f[$data->tbFields['tbowner']]), "href}&table=", + echo "
", $misc->printVal($tables->f[$data->tbFields['tbname']]), "", $misc->printVal($tables->f[$data->tbFields['tbowner']]), "href}&table=", urlencode($tables->f[$data->tbFields['tbname']]), "\">{$lang['strbrowse']}href}&table=", + echo "\thref}&table=", urlencode($tables->f[$data->tbFields['tbname']]), "\">{$lang['strselect']}href}&table=", + echo "\thref}&table=", urlencode($tables->f[$data->tbFields['tbname']]), "\">{$lang['strinsert']}href}&table=", + echo "\thref}&table=", urlencode($tables->f[$data->tbFields['tbname']]), "\">{$lang['strproperties']}href}&table=", + echo "\thref}&table=", + urlencode($tables->f[$data->tbFields['tbname']]), "\">{$lang['strempty']}href}&table=", urlencode($tables->f[$data->tbFields['tbname']]), "\">{$lang['strdrop']}