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

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

flag_list_slow.patch (text/plain), 4.08 KB, created by David Lawrence on 2008-03-24 21:55:37 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: David Lawrence

Created: 2008-03-24 21:55:37 UTC

Size: 4.08 KB

patch

obsolete

>Index: Bugzilla/DB/Mysql.pm
>===================================================================
>RCS file: /cvs/qa/rh_bugzilla_3/Bugzilla/DB/Mysql.pm,v
>retrieving revision 1.8
>diff -u -r1.8 Mysql.pm
>--- Bugzilla/DB/Mysql.pm	19 Mar 2008 22:48:28 -0000	1.8
>+++ Bugzilla/DB/Mysql.pm	24 Mar 2008 21:52:52 -0000
>@@ -221,8 +221,8 @@
> }
> 
> sub sql_flag_list {
>-    my ($self, $group_ids_string) = @_;
>-    return "FLagList(bugs.bug_id,'$group_ids_string')";
>+    my ($self) = @_;
>+    return "FLagList(bugs.bug_id, " . Bugzilla->user->id . ")";
> }
> 
> 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	24 Mar 2008 21:52:52 -0000
>@@ -50,46 +50,53 @@
> --
> 
> 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, gid 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.
>+---  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
>+---  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;
> 
>   SELECT GROUP_CONCAT(DISTINCT CONCAT(flagtypes.name, flags.status) SEPARATOR ', ') INTO outtext
>   FROM   flags, 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 (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:

dkl: review? (tfu)

Actions: View | Diff

Attachments on bug 437912: 298957 | 299092