Launchpad itself

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

  1. update-max-heat
  2. Merge into 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
=== added file 'database/schema/patch-2207-99-0.sql'
--- database/schema/patch-2207-99-0.sql 1970-01-01 00:00:00 +0000
+++ database/schema/patch-2207-99-0.sql 2010-02-26 13:30:40 +0000
@@ -0,0 +1,17 @@
1-- Copyright 2010 Canonical Ltd. This software is licensed under the
2-- GNU Affero General Public License version 3 (see the file LICENSE).
3
4SET client_min_messages=ERROR;
5
6-- product, project, distribution, distributionsourcepackage
7
8CREATE TRIGGER bug_max_heat_on_update_t
9AFTER UPDATE ON bug
10FOR EACH ROW EXECUTE PROCEDURE bug_update_max_heat_on_update();
11
12CREATE TRIGGER bugtask_max_heat_on_insert_update_t
13AFTER INSERT OR UPDATE ON bugtask
14FOR EACH ROW EXECUTE PROCEDURE bugtask_update_max_heat_on_insert_update();
15
16
17INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 99, 0);
018
=== modified file 'database/schema/trusted.sql'
--- database/schema/trusted.sql 2010-02-05 12:17:56 +0000
+++ database/schema/trusted.sql 2010-02-26 13:30:40 +0000
@@ -1480,3 +1480,125 @@
1480 RETURN NULL; -- Ignored - this is an AFTER trigger1480 RETURN NULL; -- Ignored - this is an AFTER trigger
1481 END;1481 END;
1482 $$;1482 $$;
1483
1484CREATE OR REPLACE FUNCTION bug_update_max_heat_on_update() RETURNS trigger
1485 SECURITY DEFINER LANGUAGE plpgsql AS
1486 $$
1487 BEGIN
1488 IF OLD.heat != NEW.heat THEN
1489 UPDATE product
1490 SET max_bug_heat =
1491 (SELECT MAX(bug.heat)
1492 FROM bugtask, bug
1493 WHERE bugtask.bug = bug.id AND
1494 bug.id = NEW.id AND
1495 bugtask.product = product.id)
1496 FROM bugtask
1497 WHERE bugtask.product = product.id;
1498
1499 UPDATE project
1500 SET max_bug_heat =
1501 (SELECT MAX(bug.heat)
1502 FROM bugtask, bug
1503 WHERE bugtask.bug = bug.id AND
1504 bug.id = NEW.id AND
1505 bugtask.product = product.id AND
1506 product.project = project.id)
1507 FROM bugtask, product
1508 WHERE bugtask.product = product.id AND
1509 product.project = project.id;
1510
1511 UPDATE distribution
1512 SET max_bug_heat =
1513 (SELECT MAX(bug.heat)
1514 FROM bugtask, bug
1515 WHERE bugtask.bug = bug.id AND
1516 bug.id = NEW.id AND
1517 bugtask.distribution = distribution.id)
1518 FROM bugtask
1519 WHERE bugtask.distribution = distribution.id;
1520
1521 UPDATE distributionsourcepackage
1522 SET max_bug_heat =
1523 (SELECT MAX(bug.heat)
1524 FROM bugtask, bug
1525 WHERE bugtask.bug = bug.id AND
1526 bug.id = NEW.id AND
1527 bugtask.sourcepackagename = distributionsourcepackage.id)
1528 FROM bugtask
1529 WHERE bugtask.sourcepackagename = distributionsourcepackage.id;
1530
1531 END IF;
1532 RETURN NULL;
1533 END;
1534 $$;
1535
1536CREATE OR REPLACE FUNCTION bugtask_update_max_heat_on_insert_update() RETURNS trigger
1537 SECURITY DEFINER LANGUAGE plpgsql AS
1538 $$
1539 BEGIN
1540 IF OLD.product != NEW.product THEN
1541 UPDATE product
1542 SET max_bug_heat =
1543 (SELECT MAX(bug.heat)
1544 FROM bugtask, bug
1545 WHERE bugtask.bug = bug.id AND
1546 bugtask.product = product.id)
1547 FROM bugtask
1548 WHERE bugtask.product = NEW.product;
1549
1550 UPDATE product
1551 SET max_bug_heat =
1552 (SELECT MAX(bug.heat)
1553 FROM bugtask, bug
1554 WHERE bugtask.bug = bug.id AND
1555 bugtask.product = product.id)
1556 FROM bugtask
1557 WHERE bugtask.product = OLD.product;
1558
1559 UPDATE project
1560 SET max_bug_heat =
1561 (SELECT MAX(bug.heat)
1562 FROM bugtask, bug
1563 WHERE bugtask.bug = bug.id AND
1564 bugtask.product = product.id AND
1565 product.project = project.id)
1566 FROM bugtask, product
1567 WHERE bugtask.product = NEW.product AND
1568 product.project = project.id;
1569
1570 UPDATE project
1571 SET max_bug_heat =
1572 (SELECT MAX(bug.heat)
1573 FROM bugtask, bug
1574 WHERE bugtask.bug = bug.id AND
1575 bugtask.product = product.id AND
1576 product.project = project.id)
1577 FROM bugtask, product
1578 WHERE bugtask.product = OLD.product AND
1579 product.project = project.id;
1580
1581 ELSIF OLD.sourcepackagename != NEW.sourcepackagename THEN
1582
1583 UPDATE distributionsourcepackage
1584 SET max_bug_heat =
1585 (SELECT MAX(bug.heat)
1586 FROM bugtask, bug
1587 WHERE bugtask.bug = bug.id AND
1588 bugtask.sourcepackagename = distributionsourcepackage.id)
1589 FROM bugtask
1590 WHERE bugtask.sourcepackagename = NEW.sourcepackagename;
1591
1592 UPDATE distributionsourcepackage
1593 SET max_bug_heat =
1594 (SELECT MAX(bug.heat)
1595 FROM bugtask, bug
1596 WHERE bugtask.bug = bug.id AND
1597 bugtask.sourcepackagename = distributionsourcepackage.id)
1598 FROM bugtask
1599 WHERE bugtask.sourcepackagename = OLD.sourcepackagename;
1600
1601 END IF;
1602 RETURN NULL;
1603 END;
1604 $$;

Subscribers

People subscribed via source and target branches

to status/vote changes: