From ffed251517cce0de23d9c60362d0223cab0ee76e Mon Sep 17 00:00:00 2001 From: =?utf8?q?Leonardo=20S=C3=A1piras?= Date: Fri, 27 Aug 2010 01:47:53 +0200 Subject: [PATCH] Add feature: Browsing on Foreign Keys, in both pure xhtml and ajax done during GSoC 2010, with mentoring and some help from ioguix. --- CREDITS | 3 +- HISTORY | 5 +- TODO | 2 - display.php | 255 +++++++++++++++++++++++++++++++------- js/display.js | 78 ++++++++++++ themes/default/global.css | 22 ++++ 6 files changed, 319 insertions(+), 46 deletions(-) create mode 100644 js/display.js diff --git a/CREDITS b/CREDITS index 6bd8f4d3..28538a6f 100644 --- a/CREDITS +++ b/CREDITS @@ -69,6 +69,7 @@ Contributors - Tomasz Pala - Ivan Zolotukhin - Kristoffer `spq` Janke +- Leonardo Augusto Sapiras (Improve phpPgAdmin ergonomy during the GSoC 2010, with JGuillaume 'ioguix' De Rorthais as mentor) Third Party Libraries @@ -80,4 +81,4 @@ Corporate Sponsors - SpikeSource (www.spikesource.com) - Slony support - Google Summer of Code (http://code.google.com/soc/2006/pgsql/appinfo.html?csaid=DB096D908B948D89) - phpPgAdmin Improvements - Google Summer of Code (http://code.google.com/soc/2007/postgres/appinfo.html?csaid=E89B3D5E2DC4170A) - Full Text Search in PostgreSQL GUI Tools - +- Google Summer of Code (http://code.google.com/p/google-summer-of-code-2010-postgresql/we_dont_have_the_complete_link_yet) - Improve phpPgAdmin ergonomy diff --git a/HISTORY b/HISTORY index 6e8a03bc..a5f757a5 100644 --- a/HISTORY +++ b/HISTORY @@ -19,13 +19,16 @@ Features * Add ability to create indexes concurrently * Allow user to logicaly group their server under custom named node in the browser tree * New theme and a theme switcher on the introduction page +* Auto refresh Locks page +* Auto refresh Processes page +* Link in the bottom of the page to go to top of page +* Browsing on Foreign Keys (When browsing a table, clicking on a FK value, jump to the PK row) Bugs * Fix problems with query tracking on overly long queries * Ensure pg_dump paths are valid * Fix problems with table names containing quotes * Fix autocompletion support for cross-schema objects -* Fix several problems for object names containing html charecters Translations * Czech (Marek Cernocky) diff --git a/TODO b/TODO index cc2912d7..7b6bd8d8 100644 --- a/TODO +++ b/TODO @@ -88,8 +88,6 @@ Tables * Allow PK and UNIQUE and FKs during create table (Jawed) * When adding a column or creating a table, prevent size on non-size types (eg. integer(2)). You can find these by looking at format_type in the postgresql source code. -* When browsing a table, clicking on a FK value should jump to the - PK row. (ioguix) * Add WITH storage_parameter option to create table [8.2] * Add last vacuum and analyze information from statistics tables [8.2] * Restrict operators (from $selectOps array) to appropriate types (ie. no LIKE for int4 fields) diff --git a/display.php b/display.php index c16fe01b..f47c1b4f 100644 --- a/display.php +++ b/display.php @@ -246,6 +246,175 @@ } } + + /* build & return the FK information data structure + * used when deciding if a field should have a FK link or not*/ + function &getFKInfo() { + global $data, $misc, $lang; + + // Get the foreign key(s) information from the current table + $fkey_information = array('byconstr' => array(), 'byfield' => array()); + + if (isset($_REQUEST['table'])) { + $constraints = $data->getConstraintsWithFields($_REQUEST['table']); + if ($constraints->recordCount() > 0) { + + /* build the common parts of the url for the FK */ + $fk_return_url = "{$misc->href}&subject=table&table=". urlencode($_REQUEST['table']); + if (isset($_REQUEST['page'])) $fk_return_url .= "&page=" . urlencode($_REQUEST['page']); + if (isset($_REQUEST['query'])) $fk_return_url .= "&query=" . urlencode($_REQUEST['query']); + if (isset($_REQUEST['search_path'])) $fk_return_url .= "&search_path=" . urlencode($_REQUEST['search_path']); + + /* yes, we double urlencode fk_return_url so parameters here don't + * overwrite real one when included in the final url */ + $fkey_information['common_url'] = $misc->getHREF('schema') .'&subject=table&return_url=display.php?' + . urlencode($fk_return_url) .'&return_desc='. urlencode($lang['strback']); + + /* build the FK constraints data structure */ + while (!$constraints->EOF) { + $constr =& $constraints->fields; + if ($constr['contype'] == 'f') { + + if (!isset($fkey_information['byconstr'][$constr['conid']])) { + $fkey_information['byconstr'][$constr['conid']] = array ( + 'url_data' => 'table='. urlencode($constr['f_table']) .'&schema='. urlencode($constr['f_schema']), + 'fkeys' => array(), + 'consrc' => $constr['consrc'] + ); + } + + $fkey_information['byconstr'][$constr['conid']]['fkeys'][$constr['p_field']] = $constr['f_field']; + + if (!isset($fkey_information['byfield'][$constr['p_field']])) + $fkey_information['byfield'][$constr['p_field']] = array(); + + $fkey_information['byfield'][$constr['p_field']][] = $constr['conid']; + } + $constraints->moveNext(); + } + } + } + + return $fkey_information; + } + + /* Print table header cells + * @param $sortLink must be urlencoded already + * */ + function printTableHeaderCells(&$rs, $sortLink, $withOid) { + global $misc, $data, $conf; + $j = 0; + + foreach ($rs->fields as $k => $v) { + + if (($k == $data->id) && ( !($withOid && $conf['show_oids']) )) { + $j++; + continue; + } + $finfo = $rs->fetchField($j); + + if ($sortLink === false) { + echo "", $misc->printVal($finfo->name), "\n"; + } + else { + echo "", + $misc->printVal($finfo->name), "\n"; + } + $j++; + } + + reset($rs->fields); + } + + /* Print data-row cells */ + function printTableRowCells(&$rs, &$fkey_information, $withOid) { + global $data, $misc, $conf; + $j = 0; + + if (!isset($_REQUEST['strings'])) $_REQUEST['strings'] = 'collapsed'; + + foreach ($rs->fields as $k => $v) { + $finfo = $rs->fetchField($j++); + + if (($k == $data->id) && ( !($withOid && $conf['show_oids']) )) continue; + elseif ($v !== null && $v == '') echo " "; + else { + echo ""; + + if (($v !== null) && isset($fkey_information['byfield'][$k])) { + foreach ($fkey_information['byfield'][$k] as $conid) { + + $query_params = $fkey_information['byconstr'][$conid]['url_data']; + + foreach ($fkey_information['byconstr'][$conid]['fkeys'] as $p_field => $f_field) { + $query_params .= '&'. urlencode("fkey[{$f_field}]") .'='. urlencode($rs->fields[$p_field]); + } + + /* $fkey_information['common_url'] is already urlencoded */ + $query_params .= '&'. $fkey_information['common_url']; + echo "
"; + echo ""; + echo "icon('ForeignKey')."\" style=\"vertical-align:middle;\" alt=\"[fk]\" title=\"" + . htmlentities($fkey_information['byconstr'][$conid]['consrc']) + ."\" />"; + echo ""; + echo "
"; + } + echo $misc->printVal($v, $finfo->type, array('null' => true, 'clip' => ($_REQUEST['strings']=='collapsed'), 'class' => 'fk_value')); + } else { + echo $misc->printVal($v, $finfo->type, array('null' => true, 'clip' => ($_REQUEST['strings']=='collapsed'))); + } + echo ""; + } + } + } + + /* Print the FK row, used in ajax requests */ + function doBrowseFK() { + global $data, $misc, $lang; + + $ops = array(); + foreach($_REQUEST['fkey'] as $x => $y) { + $ops[$x] = '='; + } + $query = $data->getSelectSQL($_REQUEST['table'], array(), $_REQUEST['fkey'], $ops); + $_REQUEST['query'] = $query; + + $fkinfo =& getFKInfo(); + + $max_pages = 1; + // Retrieve page from query. $max_pages is returned by reference. + $rs = $data->browseQuery('SELECT', $_REQUEST['table'], $_REQUEST['query'], + null, null, 1, 1, $max_pages); + + echo "\"[delete]\"icon('Delete') ."\" />\n"; + echo "
"; + + if (is_object($rs) && $rs->recordCount() > 0) { + /* we are browsing a referenced table here + * we should show OID if show_oids is true + * so we give true to withOid in functions bellow + * as 3rd paramter */ + + echo ""; + printTableHeaderCells($rs, false, true); + echo ""; + echo "\n"; + printTableRowCells($rs, $fkinfo, true); + echo "\n"; + echo "
\n"; + } + else + echo $lang['strnodata']; + + echo "
"; + + exit; + } /** * Displays requested data @@ -269,6 +438,16 @@ } $misc->printTrail(isset($subject) ? $subject : 'database'); + + /* This code is used when browsing FK in pure-xHTML (without js) */ + if (isset($_REQUEST['fkey'])) { + $ops = array(); + foreach($_REQUEST['fkey'] as $x => $y) { + $ops[$x] = '='; + } + $query = $data->getSelectSQL($_REQUEST['table'], array(), $_REQUEST['fkey'], $ops); + $_REQUEST['query'] = $query; + } if (isset($object)) { if (isset($_REQUEST['query'])) { @@ -314,7 +493,9 @@ isset($_REQUEST['query']) ? $_REQUEST['query'] : null, $_REQUEST['sortkey'], $_REQUEST['sortdir'], $_REQUEST['page'], $conf['max_rows'], $max_pages); - + + $fkey_information =& getFKInfo(); + // Build strings for GETs $gets = $misc->href; if (isset($object)) $gets .= "&" . urlencode($subject) . '=' . urlencode($object); @@ -336,8 +517,9 @@ if (is_object($rs) && $rs->recordCount() > 0) { // Show page navigation $misc->printPages($_REQUEST['page'], $max_pages, "display.php?page=%s&{$gets}&{$getsort}&nohistory=t&strings=" . urlencode($_REQUEST['strings'])); - echo "\n"; - + + echo "
\n"; + // Check that the key is actually in the result set. This can occur for select // operations where the key fields aren't part of the select. XXX: We should // be able to support this, somehow. @@ -353,31 +535,11 @@ if (sizeof($key) > 0) echo "\n"; - $j = 0; - foreach ($rs->fields as $k => $v) { - if (isset($object) && $k == $data->id && !$conf['show_oids']) { - $j++; - continue; - } - $finfo = $rs->fetchField($j); - // Display column headers with sorting options, unless we're PostgreSQL - // 7.0 and it's a non-TABLE mode - if ($type != 'TABLE') { - echo "\n"; - } - else { - echo "\n"; - } - $j++; - } - + /* we show OIDs only if we are in TABLE or SELECT type browsing */ + printTableHeaderCells($rs, $gets, isset($object)); + echo "\n"; - + $i = 0; reset($rs->fields); while (!$rs->EOF) { @@ -406,21 +568,15 @@ urlencode($_REQUEST['page']), "&{$key_str}&{$gets}&{$getsort}\">{$lang['strdelete']}\n"; } } - $j = 0; - foreach ($rs->fields as $k => $v) { - $finfo = $rs->fetchField($j++); - if (isset($_REQUEST['table']) && $k == $data->id && !$conf['show_oids']) continue; - elseif ($v !== null && $v == '') echo ""; - else { - echo ""; - } - } + + print printTableRowCells($rs, $fkey_information, isset($object)); + echo "\n"; $rs->moveNext(); $i++; } - echo "
{$lang['stractions']}", $misc->printVal($finfo->name), "", - $misc->printVal($finfo->name), "
 ", - $misc->printVal($v, $finfo->type, array('null' => true, 'clip' => ($_REQUEST['strings']=='collapsed'))), "
\n"; + echo "\n"; + echo "

", $rs->recordCount(), " {$lang['strrows']}

\n"; // Show page navigation $misc->printPages($_REQUEST['page'], $max_pages, "display.php?page=%s&{$gets}&{$getsort}&strings=" . urlencode($_REQUEST['strings'])); @@ -450,7 +606,7 @@ // Create report if (isset($_REQUEST['query']) && ($subject !== 'report') && $conf['show_reports'] && isset($rs) && is_object($rs) && $rs->recordCount() > 0) echo "\t
  • href}&action=create&report_sql=", - urlencode($_REQUEST['query']), "&paginate=", urlencode($_REQUEST['paginate']), "\">{$lang['strcreatereport']}
    • \n"; + urlencode($_REQUEST['query']), "&paginate=", (isset($_REQUEST['paginate'])? urlencode($_REQUEST['paginate']):'f'), "\">{$lang['strcreatereport']}\n"; // Create view and download if (isset($_REQUEST['query']) && isset($rs) && is_object($rs) && $rs->recordCount() > 0) { @@ -475,10 +631,25 @@ "\">{$lang['strrefresh']}\n"; echo "\n"; } - + + + /* shortcuts: this function exit the script for ajax purpose */ + if ($action == 'dobrowsefk') { + doBrowseFK(); + } + + $scripts = "\n"; + $scripts .= ""; + + $scripts .= "\n"; + // If a table is specified, then set the title differently if (isset($_REQUEST['subject']) && isset($_REQUEST[$_REQUEST['subject']])) - $misc->printHeader($lang['strtables']); + $misc->printHeader($lang['strtables'], $scripts); else $misc->printHeader($lang['strqueryresults']); @@ -498,7 +669,7 @@ break; case 'confdelrow': doDelRow(true); - break; + break; default: doBrowse(); break; diff --git a/js/display.js b/js/display.js new file mode 100644 index 00000000..f9180c67 --- /dev/null +++ b/js/display.js @@ -0,0 +1,78 @@ +$(document).ready(function() { + + /* init some needed tags and values */ + + $('table#data').wrap('
    '); + $('#fkcontainer').append('
    '); + + jQuery.ppa = { + root: $('#root'), + }; + + $("a.fk").live('click', function (event) { + /* make the cursor being a waiting cursor */ + $('body').css('cursor','wait'); + + query = $.ajax({ + type: 'GET', + dataType: 'html', + data: {action:'dobrowsefk'}, + url: $(this).attr('href'), + cache: false, + context: $(this), + contentType: 'application/x-www-form-urlencoded', + success: function(answer) { + pdiv = this.closest('div.fk'); + divclass = this.attr('class').split(' ')[1]; + + /* if we are clicking on a FK from the original table + (level 0), we are using the #root div as parent-div */ + if (pdiv[0].id == 'fkcontainer') { + /* computing top position, which is the topid as well */ + var top = this.position().top + 2 + this.height(); + /* if the requested top position is different than + the previous topid position of #root, empty and position it */ + if (top != jQuery.ppa.root.topid) + jQuery.ppa.root.empty() + .css({ + left: (pdiv.position().left) +'px', + top: top + 'px' + }) + /* this "topid" allows to track if we are + opening a FK from the same line in the original table */ + .topid = top; + + pdiv = jQuery.ppa.root; + + /* Remove equal rows in the root div */ + jQuery.ppa.root.children('.'+divclass).remove(); + } + else { + /* Remove equal rows in the pdiv */ + pdiv.children('div.'+divclass).remove(); + } + + /* creating the data div */ + newdiv = $('
    ').html(answer); + + /* appending it to the level-1 div */ + pdiv.append(newdiv); + }, + + error: function() { + this.closest('div.fk').append('

    '+Display.errmsg+'

    '); + }, + + complete: function () { + $('body').css('cursor','auto'); + } + }); + + return false; // do not refresh the page + }); + + $(".fk_delete").live('click', function (event) { + $(this).closest('div').remove(); + return false; // do not refresh the page + }); +}); diff --git a/themes/default/global.css b/themes/default/global.css index fd762a47..5fee3295 100644 --- a/themes/default/global.css +++ b/themes/default/global.css @@ -466,3 +466,25 @@ pre.error border-left: 1px dotted #999; font-size: smaller; } + +div.fk { + background: white; + border:1px solid black; + margin-left: 20px; +} + +div#fkcontainer { + margin: 0; + position: relative; + width: 100%; + background: none; + border:0px; +} + +div#root{ + position: absolute; +} + +div.fk_value { + display:inline-block; +} -- 2.39.5