From e11b0b4b51829b956c3087c0f945a37082d17dd2 Mon Sep 17 00:00:00 2001 From: Robert Sesek Date: Mon, 1 Aug 2005 22:34:16 +0000 Subject: [PATCH] r336: Smart query caching is done --- docs/schema_changes.sql | 15 ++- search.php | 185 +++++++++++++++++++++++++---------- templates/search.tpl | 2 +- templates/search_results.tpl | 6 ++ 4 files changed, 156 insertions(+), 52 deletions(-) diff --git a/docs/schema_changes.sql b/docs/schema_changes.sql index ed43aed..37c26af 100644 --- a/docs/schema_changes.sql +++ b/docs/schema_changes.sql @@ -15,4 +15,17 @@ CREATE TABLE `vote` ( PRIMARY KEY (`bugid`) ); -ALTER TABLE `language` DROP `filename`; \ No newline at end of file +ALTER TABLE `language` DROP `filename`; + +CREATE TABLE `search` ( + `userid` INT(10) UNSIGNED NOT NULL, + `dateline` INT(20) UNSIGNED NOT NULL, + `query` MEDIUMTEXT NOT NULL, + `ids` MEDIUMTEXT NOT NULL +); + +ALTER TABLE `search` ADD PRIMARY KEY(`userid`); + +ALTER TABLE `search` ADD `orderby` MEDIUMTEXT NOT NULL; + +ALTER TABLE `search` ADD `hilight` VARCHAR(255) NOT NULL; \ No newline at end of file diff --git a/search.php b/search.php index 957b27a..e37c05b 100644 --- a/search.php +++ b/search.php @@ -45,7 +45,7 @@ if (empty($_REQUEST['do'])) // ################################################################### -if ($_REQUEST['do'] == 'results') +if ($_REQUEST['do'] == 'process') { // ------------------------------------------------------------------- // parse out our product/component/version @@ -227,7 +227,7 @@ if ($_REQUEST['do'] == 'results') // ------------------------------------------------------------------- // do the search - $search = $db->query(" + $query = " SELECT bug.*, comment.commentid, user1.displayname AS firstreport, user2.displayname AS lastpost @@ -245,8 +245,9 @@ if ($_REQUEST['do'] == 'results') AND !bug.hidden AND !comment.hidden" : "") . " GROUP BY bug.bugid - $sortclause" - ); + $sortclause"; + + $search = $db->query($query); $numrows = $db->num_rows($search); @@ -255,74 +256,158 @@ if ($_REQUEST['do'] == 'results') $message->error('no results found'); } - while ($bug = $db->fetch_array($search)) + while ($result = $db->fetch_array($search)) { - $bug['bgcolour'] = $bugsys->datastore['status']["$bug[status]"]['color']; - $bug['product'] = $bugsys->datastore['product']["$bug[productid]"]['title']; - $bug['version'] = $bugsys->datastore['version']["$bug[versionid]"]['version']; - $bug['status'] = $bugsys->datastore['status']["$bug[status]"]['status']; - $bug['resolution'] = $bugsys->datastore['resolution']["$bug[resolution]"]['resolution']; - $bug['lastposttime'] = (($bug['hiddendisplay']) ? $bug['hiddenlastposttime'] : $bug['lastposttime']); - $bug['lastpost'] = (($bug['hiddendisplay']) ? $bug['hiddenlastpost'] : $bug['lastpost']); - $bug['lastposttime'] = $datef->format($bugsys->options['dateformat'], $bug['lastposttime']); - $bug['urladd'] = "&hilight=$hilight"; - eval('$bugs .= "' . $template->fetch('trackerhome_bits') . '";'); + $ids[] = $result['bugid']; + $results[] = $result; } - eval('$template->flush("' . $template->fetch('search_results') . '");'); + if ($bugsys->userinfo['userid']) + { + $db->query(" + REPLACE INTO " . TABLE_PREFIX . "search + (userid, dateline, query, ids, orderby, hilight) + VALUES + (" . $bugsys->userinfo['userid'] . ", + " . TIMENOW . ", '" . $bugsys->escape($query) . "', + '" . implode(',', $ids) . "', '" . $bugsys->escape($sortclause) . "', + '" . $bugsys->escape($hilight) . "' + )" + ); + } + + $justprocess = true; + + $_REQUEST['do'] = 'results'; } // ################################################################### if ($_REQUEST['do'] == 'search') { - $pcv_select = construct_pcv_select('radio', '--'); + if ($bugsys->userinfo['userid'] AND !$bugsys->in['new']) + { + if ($cachedsearch = $db->query_first("SELECT * FROM " . TABLE_PREFIX . "search WHERE userid = " . $bugsys->userinfo['userid'])) + { + $_REQUEST['do'] = 'results'; + } + else + { + $newsearch = true; + } + } + else + { + $newsearch = true; + } - // ------------------------------------------------------------------- - // custom fields - $fields = construct_custom_fields($bug, true, true); - - $bugsys->debug(count($fields) % 2); - $i = 1; - foreach ($fields AS $field) + if ($newsearch) { - $bugsys->debug("i = $i"); - if ($i == 1) + $pcv_select = construct_pcv_select('radio', '--'); + + // ------------------------------------------------------------------- + // custom fields + $fields = construct_custom_fields($bug, true, true); + + $bugsys->debug(count($fields) % 2); + $i = 1; + foreach ($fields AS $field) { - $left = $field; + $bugsys->debug("i = $i"); + if ($i == 1) + { + $left = $field; + } + else if ($i == 2) + { + $right = $field; + eval('$customfields .= "' . $template->fetch('customfield_bit') . '";'); + $i = 0; + } + $i++; } - else if ($i == 2) + + if ((count($fields) % 2) != 0) { - $right = $field; + $bugsys->debug('no modulo for you'); + $right = ''; eval('$customfields .= "' . $template->fetch('customfield_bit') . '";'); - $i = 0; } - $i++; + + // ------------------------------------------------------------------- + // built-in fields + $select['severity'] = construct_datastore_select('severity', 'severity', 'severityid', 0, true); + $select['priority'] = construct_datastore_select('priority', 'priority', 'priorityid', 0, true); + $select['status'] = construct_datastore_select('status', 'status', 'statusid', 0, true); + $select['resolution'] = construct_datastore_select('resolution', 'resolution', 'resolutionid', 0, true); + + $select['dev'] = ''; + foreach ($bugsys->datastore['assignto'] AS $dev) + { + $value = $dev['userid']; + $label = construct_user_display($dev, false); + eval('$select[dev] .= "' . $template->fetch('selectoption') . '";'); + } + + eval('$template->flush("' . $template->fetch('search') . '");'); } - - if ((count($fields) % 2) != 0) +} + +// ################################################################### + +if ($_REQUEST['do'] == 'results') +{ + $show['cached'] = false; + if ($bugsys->userinfo['userid'] AND !$justprocess) { - $bugsys->debug('no modulo for you'); - $right = ''; - eval('$customfields .= "' . $template->fetch('customfield_bit') . '";'); + $search = $cachedsearch; + if ($search['dateline'] < TIMENOW - 900 OR $bugsys->in['rerun']) + { + $search = $db->query($search['query']); + while ($bug = $db->fetch_array($search)) + { + $ids[] = $bug['bugid']; + $results[] = $bug; + } + $db->query("UPDATE " . TABLE_PREFIX . "search SET ids = '" . implode(',', $ids) . "', dateline = " . TIMENOW . " WHERE userid = " . $bugsys->userinfo['userid']); + } + else + { + $search = $db->query(" + SELECT bug.*, user1.displayname AS firstreport, + user2.displayname AS lastpost + FROM " . TABLE_PREFIX . "bug AS bug + LEFT JOIN " . TABLE_PREFIX . "user AS user1 + ON (bug.userid = user1.userid) + LEFT JOIN " . TABLE_PREFIX . "user AS user2 + ON (bug.lastpostby = user2.userid) + WHERE bug.bugid IN ($search[ids])" . ($search['orderby'] ? " + ORDER BY $search[orderby]" : '') + ); + while ($bug = $db->fetch_array($search)) + { + $results[] = $bug; + } + } + $show['cached'] = true; + $hilight = $search['hilight']; } - // ------------------------------------------------------------------- - // built-in fields - $select['severity'] = construct_datastore_select('severity', 'severity', 'severityid', 0, true); - $select['priority'] = construct_datastore_select('priority', 'priority', 'priorityid', 0, true); - $select['status'] = construct_datastore_select('status', 'status', 'statusid', 0, true); - $select['resolution'] = construct_datastore_select('resolution', 'resolution', 'resolutionid', 0, true); - - $select['dev'] = ''; - foreach ($bugsys->datastore['assignto'] AS $dev) + foreach ($results AS $bug) { - $value = $dev['userid']; - $label = construct_user_display($dev, false); - eval('$select[dev] .= "' . $template->fetch('selectoption') . '";'); + $bug['bgcolour'] = $bugsys->datastore['status']["$bug[status]"]['color']; + $bug['product'] = $bugsys->datastore['product']["$bug[productid]"]['title']; + $bug['version'] = $bugsys->datastore['version']["$bug[versionid]"]['version']; + $bug['status'] = $bugsys->datastore['status']["$bug[status]"]['status']; + $bug['resolution'] = $bugsys->datastore['resolution']["$bug[resolution]"]['resolution']; + $bug['lastposttime'] = (($bug['hiddendisplay']) ? $bug['hiddenlastposttime'] : $bug['lastposttime']); + $bug['lastpost'] = (($bug['hiddendisplay']) ? $bug['hiddenlastpost'] : $bug['lastpost']); + $bug['lastposttime'] = $datef->format($bugsys->options['dateformat'], $bug['lastposttime']); + $bug['urladd'] = "&hilight=$hilight"; + eval('$bugs .= "' . $template->fetch('trackerhome_bits') . '";'); } - - eval('$template->flush("' . $template->fetch('search') . '");'); + + eval('$template->flush("' . $template->fetch('search_results') . '");'); } /*=====================================================================*\ diff --git a/templates/search.tpl b/templates/search.tpl index 3846713..4ca095e 100644 --- a/templates/search.tpl +++ b/templates/search.tpl @@ -10,7 +10,7 @@ $header
Any fields left blank or unselected will be excluded from the search query
- + diff --git a/templates/search_results.tpl b/templates/search_results.tpl index f5a0175..77532bf 100644 --- a/templates/search_results.tpl +++ b/templates/search_results.tpl @@ -7,6 +7,12 @@ $headinclude $header + +
This search has been cached for your convenience. You can perform a new search by clicking here.
+
Alternatively, you can re-run this search by clicking here.
+
+ +
-- 2.43.5
ID