From 20802d55dad915c0f6d0e63d4fe9513c6f1708ed Mon Sep 17 00:00:00 2001 From: Robert Sesek Date: Sat, 27 May 2006 17:52:23 +0000 Subject: [PATCH] r844: Removing the complex joins for bugs by caching user information on --- docs/changes.txt | 1 + docs/schema_changes.sql | 6 ++++++ docs/todo.txt | 1 - docs/update_bug_names.php | 27 +++++++++++++++++++++++++++ editcomment.php | 12 ++++++++++-- editreport.php | 10 ++++++---- includes/api_bug.php | 5 ++++- index.php | 17 +++++------------ newreport.php | 17 ++++++++++------- templates/trackerhome_bits.tpl | 2 +- 10 files changed, 70 insertions(+), 28 deletions(-) create mode 100644 docs/update_bug_names.php diff --git a/docs/changes.txt b/docs/changes.txt index 136688c..8b8279f 100644 --- a/docs/changes.txt +++ b/docs/changes.txt @@ -6,6 +6,7 @@ - Removed potential implode() warnings in showreport.php under PHP5 - When logging out, you will be redirected to the page you were previously viewing - Rewrote the logging mechanism +- Usernames are now cached in the database for bug reports to remove the need to do complex joins at runtime 1.0.2 ================== diff --git a/docs/schema_changes.sql b/docs/schema_changes.sql index eeacdb6..a5bc203 100644 --- a/docs/schema_changes.sql +++ b/docs/schema_changes.sql @@ -1 +1,7 @@ ## SVN $Id$ + +ALTER TABLE bug ADD username varchar(255) NOT NULL; + +ALTER TABLE bug ADD lastpostbyname varchar(255) NOT NULL; + +ALTER TABLE bug ADD hiddenlastpostbyname varchar(255) NOT NULL; diff --git a/docs/todo.txt b/docs/todo.txt index 846779b..3e27406 100755 --- a/docs/todo.txt +++ b/docs/todo.txt @@ -9,7 +9,6 @@ BUGTRACK 1.1 CORE SYSTEM CHANGES ---------------------------------------- - Switch to ISSO APIs -- Cache user information into bug reports so we don't have to do mondo-joins ---------------------------------------- BUG REPORTING/EDITING diff --git a/docs/update_bug_names.php b/docs/update_bug_names.php new file mode 100644 index 0000000..9e1a3a7 --- /dev/null +++ b/docs/update_bug_names.php @@ -0,0 +1,27 @@ +query("SELECT userid, displayname FROM " . TABLE_PREFIX . "user"); +while ($user = $db->fetch_array($users)) +{ + $userlist["$user[userid]"] = $user['displayname']; +} + +$bugs = $db->query("SELECT * FROM " . TABLE_PREFIX . "bug"); +while ($bug = $db->fetch_array($bugs)) +{ + $db->query(" + UPDATE " . TABLE_PREFIX . "bug + SET username = '" . $db->escape_string($userlist["$bug[userid]"]) . "', + lastpostbyname = '" . $db->escape_string($userlist["$bug[lastpostby]"]) . "', + hiddenlastpostbyname = '" . $db->escape_string($userlist["$bug[hiddenlastpostby]"]) . "' + WHERE bugid = $bug[bugid] + "); +} + +?> \ No newline at end of file diff --git a/editcomment.php b/editcomment.php index 95feaee..4a867e0 100644 --- a/editcomment.php +++ b/editcomment.php @@ -81,11 +81,19 @@ if ($_POST['do'] == 'update') $log->add_data(true, $commentapi->objdata, array('comment', 'hidden'), false, 'comment'); $log->add_data(false, $commentapi->values, array('comment', 'hidden'), false, 'comment'); - $lastgood = $db->query_first("SELECT * FROM " . TABLE_PREFIX . "comment WHERE bugid = $bug[bugid] AND !hidden ORDER BY dateline DESC"); + $lastgood = $db->query_first(" + SELECT comment.* AS comment, user.displayname AS username + FROM " . TABLE_PREFIX . "comment + LEFT JOIN " . TABLE_PREFIX . "user AS user + ON (user.userid = comment.userid) + WHERE bugid = $bug[bugid] + AND !hidden ORDER BY dateline DESC + "); $db->query(" UPDATE " . TABLE_PREFIX . "bug SET hiddenlastposttime = $lastgood[dateline], - hiddenlastpostby = $lastgood[userid] + hiddenlastpostby = $lastgood[userid], + hiddenlastpostbyname = '" . $db->escape_string($lastgood['username']) . "' WHERE bugid = $bug[bugid]" ); diff --git a/editreport.php b/editreport.php index c41715d..381bee4 100644 --- a/editreport.php +++ b/editreport.php @@ -126,10 +126,12 @@ if ($_POST['do'] == 'update') $comment->set('comment', $commenttext); $comment->insert(); - $bugapi->set('lastposttime', $comment->values['dateline']); - $bugapi->set('lastpostby', $bugsys->userinfo['userid']); - $bugapi->set('hiddenlastposttime', $comment->values['dateline']); - $bugapi->set('hiddenlastpostby', $bugsys->userinfo['userid']); + $bugapi->set('lastposttime', $comment->values['dateline']); + $bugapi->set('lastpostby', $bugsys->userinfo['userid']); + $bugapi->set('lastpostbyname', $bugsys->userinfo['displayname']); + $bugapi->set('hiddenlastposttime', $comment->values['dateline']); + $bugapi->set('hiddenlastpostby', $bugsys->userinfo['userid']); + $bugapi->set('hiddenlastpostbyname', $bugsys->userinfo['displayname']); if (!((can_perform('caneditown', $bug['productid']) AND $bugsys->userinfo['userid'] == $bug['userid']) OR (can_perform('caneditother', $bug['productid']) AND $bugsys->userinfo['userid'] != $bug['userid']))) { diff --git a/includes/api_bug.php b/includes/api_bug.php index 9f07233..67bacd1 100644 --- a/includes/api_bug.php +++ b/includes/api_bug.php @@ -43,6 +43,7 @@ class BugAPI extends API var $fields = array( 'bugid' => array(TYPE_UINT, REQ_AUTO, 'verify_nozero'), 'userid' => array(TYPE_UINT, REQ_NO), + 'username' => array(TYPE_STR, REQ_NO), 'dateline' => array(TYPE_UINT, REQ_SET), 'productid' => array(TYPE_UINT, REQ_YES, ':self'), 'componentid' => array(TYPE_UINT, REQ_NO, ':self'), @@ -59,8 +60,10 @@ class BugAPI extends API 'initialreport' => array(TYPE_UINT, REQ_NO), 'lastposttime' => array(TYPE_UINT, REQ_NO), 'lastpostby' => array(TYPE_UINT, REQ_NO), + 'lastpostbyname' => array(TYPE_STR, REQ_NO), 'hiddenlastposttime' => array(TYPE_UINT, REQ_NO), - 'hiddenlastpostby' => array(TYPE_UINT, REQ_NO) + 'hiddenlastpostby' => array(TYPE_UINT, REQ_NO), + 'hiddenlastpostbyname' => array(TYPE_STR, REQ_NO) ); /** diff --git a/index.php b/index.php index 201d58b..b210bfe 100644 --- a/index.php +++ b/index.php @@ -52,17 +52,10 @@ $pagination->total = $count['count']; $pagination->split_pages(); $bugs_fetch = $db->query(" - SELECT bug.*, user1.displayname AS firstreport, user2.displayname AS lastpost, user3.displayname AS hiddenlastpost - 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) - LEFT JOIN " . TABLE_PREFIX . "user AS user3 - ON (bug.hiddenlastpostby = user3.userid) - WHERE bug.productid IN (" . fetch_on_bits('canviewbugs') . ") + SELECT * FROM " . TABLE_PREFIX . "bug + WHERE productid IN (" . fetch_on_bits('canviewbugs') . ") AND (!hidden OR (hidden AND productid IN (" . fetch_on_bits('canviewhidden') . "))) - ORDER BY bug." . ((can_perform('canviewhidden')) ? "lastposttime" : "hiddenlastposttime") . " DESC + ORDER BY " . (can_perform('canviewhidden') ? "lastposttime" : "hiddenlastposttime") . " DESC LIMIT " . $pagination->fetch_limit($pagination->page - 1) . ", " . $pagination->perpage ); @@ -79,8 +72,8 @@ while ($bug = $db->fetch_array($bugs_fetch)) $bug['hiddendisplay'] = ((!can_perform('canviewhidden', $bug['productid']) AND $bug['hiddenlastposttime']) ? true : false); - $bug['lastposttime'] = (($bug['hiddendisplay']) ? $bug['hiddenlastposttime'] : $bug['lastposttime']); - $bug['lastpost'] = (($bug['hiddendisplay']) ? $bug['hiddenlastpost'] : $bug['lastpost']); + $bug['lastposttime'] = ($bug['hiddendisplay'] ? $bug['hiddenlastposttime'] : $bug['lastposttime']); + $bug['lastpost'] = ($bug['hiddendisplay'] ? $bug['hiddenlastpostbyname'] : $bug['lastpostbyname']); $bug['lastposttime'] = $datef->format($bugsys->options['dateformat'], $bug['lastposttime']); diff --git a/newreport.php b/newreport.php index ea89eaa..05dee84 100755 --- a/newreport.php +++ b/newreport.php @@ -52,6 +52,7 @@ if ($_POST['do'] == 'insert') $comment = new CommentAPI($bugsys); $bug->set('userid', $bugsys->userinfo['userid']); + $bug->set('username', $bugsys->userinfo['displayname']); $bug->set('summary', $bugsys->in['summary']); $bug->set('severity', $bugsys->in['severity']); @@ -101,14 +102,16 @@ if ($_POST['do'] == 'insert') $comment->insert(); $bug = new BugAPI($bugsys); // need to destroy because update will think the insert fields need to be changed, too - $bug->set('bugid', $comment->values['bugid']); + $bug->set('bugid', $comment->values['bugid']); $bug->set_condition(); - $bug->set('dateline', $comment->values['dateline']); - $bug->set('initialreport', $comment->insertid); - $bug->set('lastposttime', $comment->values['dateline']); - $bug->set('lastpostby', $bugsys->userinfo['userid']); - $bug->set('hiddenlastposttime', $comment->values['dateline']); - $bug->set('hiddenlastpostby', $bugsys->userinfo['userid']); + $bug->set('dateline', $comment->values['dateline']); + $bug->set('initialreport', $comment->insertid); + $bug->set('lastposttime', $comment->values['dateline']); + $bug->set('lastpostby', $bugsys->userinfo['userid']); + $bug->set('lastpostbyname', $bugsys->userinfo['displayname']); + $bug->set('hiddenlastposttime', $comment->values['dateline']); + $bug->set('hiddenlastpostby', $bugsys->userinfo['userid']); + $bug->set('hiddenlastpostbyname', $bugsys->userinfo['displayname']); $bug->update(); $message->redirect($lang->string('The bug has been added to the database.'), ($bugsys->in['submit_reload'] == '' ? "showreport.php?bugid=" . $bug->values['bugid'] : 'newreport.php')); diff --git a/templates/trackerhome_bits.tpl b/templates/trackerhome_bits.tpl index 78c0fb2..700313c 100644 --- a/templates/trackerhome_bits.tpl +++ b/templates/trackerhome_bits.tpl @@ -4,7 +4,7 @@ $bug[bugid]
$bug[summary]
-
$bug[firstreport]
 +
$bug[username]
$bug[product]
-- 2.22.5