Merge lp:~intellectronica/launchpad/update-max-heat into lp:launchpad/db-devel

Proposed by Eleanor Berger
Status: Rejected
Rejected by: Eleanor Berger
Proposed branch: lp:~intellectronica/launchpad/update-max-heat
Merge into: lp:launchpad/db-devel
Diff against target: 151 lines (+139/-0)
2 files modified
database/schema/patch-2207-99-0.sql (+17/-0)
database/schema/trusted.sql (+122/-0)
To merge this branch: bzr merge lp:~intellectronica/launchpad/update-max-heat
Reviewer Review Type Date Requested Status
Stuart Bishop (community) db Needs Fixing
Review via email: mp+20204@code.launchpad.net
To post a comment you must log in.
Revision history for this message
Eleanor Berger (intellectronica) wrote :

This branch adds a trigger which updates the max_bug_heat column on bug targets.

Revision history for this message
Stuart Bishop (stub) wrote :

When BugTasks are created or retargetted, the maximums need to be recalculated too (both for the old target and the new target). I don't think we delete BugTasks so we don't need to cope with that case.

The Distribution.max_bug_heat needs to include bugs linked to a distroseries (Only one of BugTask.distroseries and BugTask.distribution can be set).

review: Needs Fixing (db)
Revision history for this message
Stuart Bishop (stub) wrote :

Similarly, only one of product and productseries is ever set.

Revision history for this message
Eleanor Berger (intellectronica) wrote :

Getting rid of this for now. After taking into consideration all those additional rules, a trigger doesn't really make much sense.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added file 'database/schema/patch-2207-99-0.sql'
2--- database/schema/patch-2207-99-0.sql 1970-01-01 00:00:00 +0000
3+++ database/schema/patch-2207-99-0.sql 2010-02-26 13:30:40 +0000
4@@ -0,0 +1,17 @@
5+-- Copyright 2010 Canonical Ltd. This software is licensed under the
6+-- GNU Affero General Public License version 3 (see the file LICENSE).
7+
8+SET client_min_messages=ERROR;
9+
10+-- product, project, distribution, distributionsourcepackage
11+
12+CREATE TRIGGER bug_max_heat_on_update_t
13+AFTER UPDATE ON bug
14+FOR EACH ROW EXECUTE PROCEDURE bug_update_max_heat_on_update();
15+
16+CREATE TRIGGER bugtask_max_heat_on_insert_update_t
17+AFTER INSERT OR UPDATE ON bugtask
18+FOR EACH ROW EXECUTE PROCEDURE bugtask_update_max_heat_on_insert_update();
19+
20+
21+INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 99, 0);
22
23=== modified file 'database/schema/trusted.sql'
24--- database/schema/trusted.sql 2010-02-05 12:17:56 +0000
25+++ database/schema/trusted.sql 2010-02-26 13:30:40 +0000
26@@ -1480,3 +1480,125 @@
27 RETURN NULL; -- Ignored - this is an AFTER trigger
28 END;
29 $$;
30+
31+CREATE OR REPLACE FUNCTION bug_update_max_heat_on_update() RETURNS trigger
32+ SECURITY DEFINER LANGUAGE plpgsql AS
33+ $$
34+ BEGIN
35+ IF OLD.heat != NEW.heat THEN
36+ UPDATE product
37+ SET max_bug_heat =
38+ (SELECT MAX(bug.heat)
39+ FROM bugtask, bug
40+ WHERE bugtask.bug = bug.id AND
41+ bug.id = NEW.id AND
42+ bugtask.product = product.id)
43+ FROM bugtask
44+ WHERE bugtask.product = product.id;
45+
46+ UPDATE project
47+ SET max_bug_heat =
48+ (SELECT MAX(bug.heat)
49+ FROM bugtask, bug
50+ WHERE bugtask.bug = bug.id AND
51+ bug.id = NEW.id AND
52+ bugtask.product = product.id AND
53+ product.project = project.id)
54+ FROM bugtask, product
55+ WHERE bugtask.product = product.id AND
56+ product.project = project.id;
57+
58+ UPDATE distribution
59+ SET max_bug_heat =
60+ (SELECT MAX(bug.heat)
61+ FROM bugtask, bug
62+ WHERE bugtask.bug = bug.id AND
63+ bug.id = NEW.id AND
64+ bugtask.distribution = distribution.id)
65+ FROM bugtask
66+ WHERE bugtask.distribution = distribution.id;
67+
68+ UPDATE distributionsourcepackage
69+ SET max_bug_heat =
70+ (SELECT MAX(bug.heat)
71+ FROM bugtask, bug
72+ WHERE bugtask.bug = bug.id AND
73+ bug.id = NEW.id AND
74+ bugtask.sourcepackagename = distributionsourcepackage.id)
75+ FROM bugtask
76+ WHERE bugtask.sourcepackagename = distributionsourcepackage.id;
77+
78+ END IF;
79+ RETURN NULL;
80+ END;
81+ $$;
82+
83+CREATE OR REPLACE FUNCTION bugtask_update_max_heat_on_insert_update() RETURNS trigger
84+ SECURITY DEFINER LANGUAGE plpgsql AS
85+ $$
86+ BEGIN
87+ IF OLD.product != NEW.product THEN
88+ UPDATE product
89+ SET max_bug_heat =
90+ (SELECT MAX(bug.heat)
91+ FROM bugtask, bug
92+ WHERE bugtask.bug = bug.id AND
93+ bugtask.product = product.id)
94+ FROM bugtask
95+ WHERE bugtask.product = NEW.product;
96+
97+ UPDATE product
98+ SET max_bug_heat =
99+ (SELECT MAX(bug.heat)
100+ FROM bugtask, bug
101+ WHERE bugtask.bug = bug.id AND
102+ bugtask.product = product.id)
103+ FROM bugtask
104+ WHERE bugtask.product = OLD.product;
105+
106+ UPDATE project
107+ SET max_bug_heat =
108+ (SELECT MAX(bug.heat)
109+ FROM bugtask, bug
110+ WHERE bugtask.bug = bug.id AND
111+ bugtask.product = product.id AND
112+ product.project = project.id)
113+ FROM bugtask, product
114+ WHERE bugtask.product = NEW.product AND
115+ product.project = project.id;
116+
117+ UPDATE project
118+ SET max_bug_heat =
119+ (SELECT MAX(bug.heat)
120+ FROM bugtask, bug
121+ WHERE bugtask.bug = bug.id AND
122+ bugtask.product = product.id AND
123+ product.project = project.id)
124+ FROM bugtask, product
125+ WHERE bugtask.product = OLD.product AND
126+ product.project = project.id;
127+
128+ ELSIF OLD.sourcepackagename != NEW.sourcepackagename THEN
129+
130+ UPDATE distributionsourcepackage
131+ SET max_bug_heat =
132+ (SELECT MAX(bug.heat)
133+ FROM bugtask, bug
134+ WHERE bugtask.bug = bug.id AND
135+ bugtask.sourcepackagename = distributionsourcepackage.id)
136+ FROM bugtask
137+ WHERE bugtask.sourcepackagename = NEW.sourcepackagename;
138+
139+ UPDATE distributionsourcepackage
140+ SET max_bug_heat =
141+ (SELECT MAX(bug.heat)
142+ FROM bugtask, bug
143+ WHERE bugtask.bug = bug.id AND
144+ bugtask.sourcepackagename = distributionsourcepackage.id)
145+ FROM bugtask
146+ WHERE bugtask.sourcepackagename = OLD.sourcepackagename;
147+
148+ END IF;
149+ RETURN NULL;
150+ END;
151+ $$;

Subscribers

People subscribed via source and target branches

to status/vote changes: