launchpad-reviewers team mailing list archive

[Robert Collins <robertc@xxxxxxxxxxxxxxxxx>]

Robert Collins has proposed merging lp:~lifeless/launchpad/bug-618406 into lp:launchpad/db-devel.

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~lifeless/launchpad/bug-618406/+merge/55306

Add a bugtask heat column which mirrors bug.heat allowing in-bugtask sorting of heat based queries.
-- 
https://code.launchpad.net/~lifeless/launchpad/bug-618406/+merge/55306
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~lifeless/launchpad/bug-618406 into lp:launchpad/db-devel.

=== modified file 'database/schema/comments.sql'
--- database/schema/comments.sql	2011-03-15 12:43:01 +0000
+++ database/schema/comments.sql	2011-03-29 10:31:36 +0000
@@ -271,6 +271,7 @@
 COMMENT ON COLUMN BugTask.date_left_closed IS 'The date when this bug last transitioned out of a CLOSED status.';
 COMMENT ON COLUMN BugTask.date_milestone_set IS 'The date when this bug was targed to the milestone that is currently set.';
 COMMENT ON COLUMN BugTask.heat_rank IS 'The heat bin in which this bugtask appears, as a value from the BugTaskHeatRank enumeration.';
+COMMENT ON COLUMN BugTask.heat IS 'The relevance of this bug. This value is computed periodically using bug_affects_person and other bug values.';
 
 
 -- BugNotification

=== added file 'database/schema/patch-2208-59-0.sql'
--- database/schema/patch-2208-59-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2208-59-0.sql	2011-03-29 10:31:36 +0000
@@ -0,0 +1,38 @@
+-- Copyright 2009 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+-- Add bugtask heat denormalisation for sort efficiency. We will enforce non-null subsequently;
+
+ALTER TABLE bugtask ADD COLUMN heat integer;
+
+UPDATE bugtask SET heat=bug.heat FROM bug WHERE bug.id=bugtask.bug;
+
+ALTER TABLE bugtask ALTER COLUMN heat SET NOT NULL;
+ALTER TABLE bugtask ALTER COLUMN heat SET DEFAULT 0;
+
+-- Primary use case is 'sort by heat in a context'; for no context we can use
+-- the bug.heat column & index.
+
+-- contexts - product, productseries, distro sourcepackage, distroseries sourcepackage, distro and distroseries.
+-- product context
+CREATE INDEX bugtask__product__heat__idx ON bugtask USING btree (product, heat) WHERE product IS NOT NULL;
+-- productseries context
+CREATE INDEX bugtask__productseries__heat__idx ON bugtask USING btree (productseries, heat) WHERE productseries IS NOT NULL;
+-- distribution context (handles distribution and distribution source package queries)
+CREATE INDEX bugtask__distribution_sourcepackage__heat__idx ON bugtask USING btree (distribution, sourcepackagename, heat) WHERE distribution IS NOT NULL;
+-- distroseries context (handles series and series source package queries)
+CREATE INDEX bugtask__distroseries_sourcepackage__heat__idx ON bugtask USING btree (distroseries, sourcepackagename, heat) WHERE distroseries IS NOT NULL;
+
+-- may wish to drop these indices as superceded by the above; if so should
+-- CLUSTER the distribution_sourcepackagename index.
+-- DROP INDEX bugtask__distribution__sourcepackagename__idx
+-- DROP INDEX bugtask__distroseries__sourcepackagename__idx
+
+-- When a bug is changed we copy the heat; brand new bugs will not have their
+-- heat copied until they are recalculated (but equally brand new bugs have 0
+-- heat).
+CREATE TRIGGER bug_to_bugtask_heat AFTER UPDATE ON bug FOR EACH ROW EXECUTE PROCEDURE bug_update_heat_copy_to_bugtask();
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 59, 0);

=== modified file 'database/schema/trusted.sql'
--- database/schema/trusted.sql	2011-03-01 17:07:13 +0000
+++ database/schema/trusted.sql	2011-03-29 10:31:36 +0000
@@ -1954,6 +1954,20 @@
     return int(total_heat)
 $$;
 
+CREATE OR REPLACE FUNCTION bug_update_heat_copy_to_bugtask()
+RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
+$$
+BEGIN
+    IF NEW.heat != OLD.heat THEN
+        UPDATE bugtask SET heat=NEW.heat WHERE bugtask.bug=NEW.id;
+    END IF;
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+COMMENT ON FUNCTION bug_update_heat_copy_to_bugtask() IS
+'Copies bug heat to bugtasks when the bug is changed. Runs on UPDATE only because INSERTs do not have bugtasks at the point of insertion.';
+
 -- This function is not STRICT, since it needs to handle
 -- dateexpected when it is NULL.
 CREATE OR REPLACE FUNCTION milestone_sort_key(