Attachment 299092 Details for Bug 437912 – Patch to speed up FlagList() stored function some (v2)

[patch] Patch to speed up FlagList() stored function some (v2)

flag_list_changes.patch (text/plain), 4.39 KB, created by David Lawrence on 2008-03-26 03:33:40 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: David Lawrence

Created: 2008-03-26 03:33:40 UTC

Size: 4.39 KB

patch

obsolete

>? patches
>Index: Bugzilla/Search.pm
>===================================================================
>RCS file: /cvs/qa/rh_bugzilla_3/Bugzilla/Search.pm,v
>retrieving revision 1.20
>diff -u -r1.20 Search.pm
>--- Bugzilla/Search.pm	17 Mar 2008 21:48:07 -0000	1.20
>+++ Bugzilla/Search.pm	26 Mar 2008 03:27:52 -0000
>@@ -1133,7 +1133,7 @@
>                                  title => "Blocked By" },
>         itmaxscore          => { name => $dbh->sql_it_max_score() . " AS itmaxscore", 
>                                  title => "IT Max Score" },
>-        flags               => { name => $dbh->sql_flag_list(Bugzilla->user->groups_as_string) . " AS flags", 
>+        flags               => { name => $dbh->sql_flag_list() . " AS flags", 
>                                  title => "Flags" },
>         # REDHAT EXTENSION END 406371
>     };
>Index: Bugzilla/DB/Mysql.pm
>===================================================================
>RCS file: /cvs/qa/rh_bugzilla_3/Bugzilla/DB/Mysql.pm,v
>retrieving revision 1.9
>diff -u -r1.9 Mysql.pm
>--- Bugzilla/DB/Mysql.pm	25 Mar 2008 19:51:48 -0000	1.9
>+++ Bugzilla/DB/Mysql.pm	26 Mar 2008 03:27:53 -0000
>@@ -221,8 +221,9 @@
> }
> 
> sub sql_flag_list {
>-    my ($self, $group_ids_string) = @_;
>-    return "FLagList(bugs.bug_id,'$group_ids_string')";
>+    my ($self) = @_;
>+    my $isinsider = Bugzilla->user->in_group(Bugzilla->params->{insidergroup}) ? 1 : 0;
>+    return "FLagList(bugs.bug_id," . Bugzilla->user->id . ", $isinsider)";
> }
> 
> sub sql_dependson_list {
>Index: redhat/bugs-functions.mysql
>===================================================================
>RCS file: /cvs/qa/rh_bugzilla_3/redhat/bugs-functions.mysql,v
>retrieving revision 1.2
>diff -u -r1.2 bugs-functions.mysql
>--- redhat/bugs-functions.mysql	17 Mar 2008 17:05:30 -0000	1.2
>+++ redhat/bugs-functions.mysql	26 Mar 2008 03:27:54 -0000
>@@ -44,52 +44,24 @@
> 
> --
> -- Stored function that returns a comma delimited list of flags for a bug_id
>--- Usage: SELECT bugs.bug_id, FlagList(bugs.bug_id, group_id_list) FROM bugs WHERE ...
>+-- Usage: SELECT bugs.bug_id, FlagList(bugs.bug_id, user_id, isinsider) FROM bugs WHERE ...
> -- Params: bugs.bug_id INTEGER (current bug id)
>---         group_id_list TEXT (comma delimited string of group ids user belongs to)
>+--         user_id INTEGER (user_id of current user)
>+--         isinsider INTEGER (1 or 0 depending on whether user is in isinsider group)
> --
> 
> DROP FUNCTION IF EXISTS FlagList //
>-CREATE FUNCTION FlagList (bug_id INTEGER, group_id_list TEXT) RETURNS TEXT DETERMINISTIC MODIFIES SQL DATA 
>+CREATE FUNCTION FlagList (bid INTEGER, uid INTEGER, isinsider INTEGER) RETURNS TEXT DETERMINISTIC MODIFIES SQL DATA
> BEGIN
>   DECLARE outtext TEXT DEFAULT '';
>-  DECLARE foundPos TINYINT UNSIGNED;
>-  DECLARE delim VARCHAR(10) DEFAULT ',';
>-  DECLARE element TEXT;
>-
>-  IF NOT group_id_list THEN
>-    SET group_id_list = "''";
>-  END IF;
>-
>-  DROP TEMPORARY TABLE IF EXISTS tmpGroupIds;
>-  CREATE TEMPORARY TABLE tmpGroupIds (
>-    value INTEGER NOT NULL 
>-  ) ENGINE = memory;
>-
>-  SET foundPos = INSTR(group_id_list,delim);
>-  WHILE foundPos <> 0 DO
>-    SET element = SUBSTRING(group_id_list, 1, foundPos-1);
>-    SET group_id_list = TRIM(LEADING CONCAT(element,delim) FROM group_id_list);
>-
>-    INSERT INTO tmpGroupIds (value) VALUES (element);
>-
>-    SET foundPos = INSTR(group_id_list,delim);
>-  END WHILE;
>-
>-  IF group_id_list <> '' THEN
>-    INSERT INTO tmpGroupIds (value) VALUES (group_id_list);
>-  END IF;
>-
>---- FIXME: Need to figure out how to properly include attachment flags in this list
>---- Some attachments are private to the insider group and those flags should be filtered
>---- based on if the user is in that group or not. We just skip them for now.
> 
>   SELECT GROUP_CONCAT(DISTINCT CONCAT(flagtypes.name, flags.status) SEPARATOR ', ') INTO outtext
>-  FROM   flags, flagtypes
>+  FROM   flags LEFT JOIN attachments ON flags.attach_id = attachments.attach_id, flagtypes
>   WHERE  flags.type_id = flagtypes.id
>-         AND flags.attach_id IS NULL
>-         AND (flagtypes.view_group_id IS NULL OR flagtypes.view_group_id IN (SELECT value FROM tmpGroupIds))
>-         AND flags.bug_id = bug_id;
>+         AND (attachments.attach_id IS NULL OR attachments.isprivate = 0 OR isinsider = 1)
>+         AND (flagtypes.view_group_id IS NULL OR flagtypes.view_group_id
>+              IN (SELECT group_id FROM user_group_map WHERE isbless = 0 AND user_id = uid))
>+         AND flags.bug_id = bid;
> 
>   RETURN outtext;
> END //

Flags:

tfu: review+

Actions: View | Diff

Attachments on bug 437912: 298957 | 299092