launchpad-reviewers team mailing list archive
-
launchpad-reviewers team
-
Mailing list archive
-
Message #03949
[Merge] lp:~benji/launchpad/bug-697735 into lp:launchpad
Benji York has proposed merging lp:~benji/launchpad/bug-697735 into lp:launchpad.
Requested reviews:
Launchpad code reviewers (launchpad-reviewers)
Related bugs:
Bug #697735 in Launchpad itself: "ValueError raised submitting bogus query string"
https://bugs.launchpad.net/launchpad/+bug/697735
For more details, see:
https://code.launchpad.net/~benji/launchpad/bug-697735/+merge/64535
Bug 697735 is about OOPS reports that are generated when
zope.publisher.browser tries to apply a type conversion (e.g.,
?foo:int=7) and the conversion raises an exception. Generating an error
is fine, but we don't want OOPS reports logged when they occur.
This branch adds a marker interface that can be applied to an exception
that signals the OOPS reporting mechanism that no report should be
logged. Originally I added an underscore-prefixed attribute to
exceptions that shouldn't be recorded, but Gary suggested the
improvement of using a marker interface.
The several tests were added to
lib/canonical/launchpad/webapp/tests/test_errorlog.py cover the new
behavior.
A fair bit of mostly whitespace lint was fixed in
./lib/canonical/launchpad/webapp/interfaces.py as well.
The only slightly suboptimal part of this branch is the need to monkey
patch zope.publisher.browser (see lib/lp_sitecustomize.py) so that we
could wrap the conversion functions with a try/except to mark
ValueErrors as non-oops-report-worthy. Gary and I discussed this
aspect of the branch and felt like it was a reasonable compromise.
--
https://code.launchpad.net/~benji/launchpad/bug-697735/+merge/64535
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~benji/launchpad/bug-697735 into lp:launchpad.
=== removed file 'database/schema/patch-2208-63-0.sql'
--- database/schema/patch-2208-63-0.sql 2011-06-05 07:13:43 +0000
+++ database/schema/patch-2208-63-0.sql 1970-01-01 00:00:00 +0000
@@ -1,597 +0,0 @@
--- Copyright 2011 Canonical Ltd. This software is licensed under the
--- GNU Affero General Public License version 3 (see the file LICENSE).
-
-SET client_min_messages=ERROR;
-
-CREATE TABLE BugSummary(
- -- Slony needs a primary key and there are no natural candidates.
- id serial PRIMARY KEY,
- count INTEGER NOT NULL default 0,
- product INTEGER REFERENCES Product ON DELETE CASCADE,
- productseries INTEGER REFERENCES ProductSeries ON DELETE CASCADE,
- distribution INTEGER REFERENCES Distribution ON DELETE CASCADE,
- distroseries INTEGER REFERENCES DistroSeries ON DELETE CASCADE,
- sourcepackagename INTEGER REFERENCES SourcePackageName ON DELETE CASCADE,
- viewed_by INTEGER, -- No REFERENCES because it is trigger maintained.
- tag TEXT,
- status INTEGER NOT NULL,
- milestone INTEGER REFERENCES Milestone ON DELETE CASCADE,
- CONSTRAINT bugtask_assignment_checks CHECK (
- CASE
- WHEN product IS NOT NULL THEN
- productseries IS NULL
- AND distribution IS NULL
- AND distroseries IS NULL
- AND sourcepackagename IS NULL
- WHEN productseries IS NOT NULL THEN
- distribution IS NULL
- AND distroseries IS NULL
- AND sourcepackagename IS NULL
- WHEN distribution IS NOT NULL THEN
- distroseries IS NULL
- WHEN distroseries IS NOT NULL THEN
- TRUE
- ELSE
- FALSE
- END)
-);
-
----- Bulk load into the table - after this it is maintained by trigger. Timed
--- at 2-3 minutes on staging.
--- basic theory: each bug *task* has some unary dimensions (like status) and
--- some N-ary dimensions (like contexts [sourcepackage+distro, distro only], or
--- subscriptions, or tags). For N-ary dimensions we record the bug against all
--- positions in that dimension.
--- Some tasks aggregate into the same dimension - e.g. two different source
--- packages tasks in Ubuntu. At the time of writing we only want to count those
--- once ( because we have had user confusion when two tasks of the same bug are
--- both counted toward portal aggregates). So we add bug.id distinct.
--- We don't map INCOMPLETE to INCOMPLETE_WITH_RESPONSE - instead we'll let that
--- migration happen separately.
--- So the rules the code below should be implementing are:
--- once for each task in a different target
--- once for each subscription (private bugs) (left join subscribers conditionally on privacy)
--- once for each sourcepackage name + one with sourcepackagename=NULL (two queries unioned)
--- once for each tag + one with tag=NULL (two queries unioned)
--- bugs with duplicateof non null are excluded because we exclude them from all our aggregates.
-INSERT INTO bugsummary (
- count, product, productseries, distribution, distroseries,
- sourcepackagename, viewed_by, tag, status, milestone)
-WITH
- -- kill dupes
- relevant_bug AS (SELECT * FROM bug where duplicateof is NULL),
-
- -- (bug.id, tag) for all bug-tag pairs plus (bug.id, NULL) for all bugs
- bug_tags AS (
- SELECT relevant_bug.id, NULL::text AS tag FROM relevant_bug
- UNION
- SELECT relevant_bug.id, tag
- FROM relevant_bug INNER JOIN bugtag ON relevant_bug.id=bugtag.bug),
- -- (bug.id, NULL) for all public bugs + (bug.id, viewer) for all
- -- (subscribers+assignee) on private bugs
- bug_viewers AS (
- SELECT relevant_bug.id, NULL::integer AS person
- FROM relevant_bug WHERE NOT relevant_bug.private
- UNION
- SELECT relevant_bug.id, assignee AS person
- FROM relevant_bug
- INNER JOIN bugtask ON relevant_bug.id=bugtask.bug
- WHERE relevant_bug.private and bugtask.assignee IS NOT NULL
- UNION
- SELECT relevant_bug.id, bugsubscription.person
- FROM relevant_bug INNER JOIN bugsubscription
- ON bugsubscription.bug=relevant_bug.id WHERE relevant_bug.private),
-
- -- (bugtask.(bug, product, productseries, distribution, distroseries,
- -- sourcepackagename, status, milestone) for all bugs + the same with
- -- sourcepackage squashed to NULL)
- tasks AS (
- SELECT
- bug, product, productseries, distribution, distroseries,
- sourcepackagename, status, milestone
- FROM bugtask
- UNION
- SELECT DISTINCT ON (
- bug, product, productseries, distribution, distroseries,
- sourcepackagename, milestone)
- bug, product, productseries, distribution, distroseries,
- NULL::integer as sourcepackagename,
- status, milestone
- FROM bugtask where sourcepackagename IS NOT NULL)
-
- -- Now combine
- SELECT
- count(*), product, productseries, distribution, distroseries,
- sourcepackagename, person, tag, status, milestone
- FROM relevant_bug
- INNER JOIN bug_tags ON relevant_bug.id=bug_tags.id
- INNER JOIN bug_viewers ON relevant_bug.id=bug_viewers.id
- INNER JOIN tasks on tasks.bug=relevant_bug.id
- GROUP BY
- product, productseries, distribution, distroseries,
- sourcepackagename, person, tag, status, milestone;
-
--- Need indices for FK CASCADE DELETE to find any FK easily
-CREATE INDEX bugsummary__distribution__idx ON BugSummary (distribution)
- WHERE distribution IS NOT NULL;
-
-CREATE INDEX bugsummary__distroseries__idx ON BugSummary (distroseries)
- WHERE distroseries IS NOT NULL;
-
-CREATE INDEX bugsummary__viewed_by__idx ON BugSummary (viewed_by)
- WHERE viewed_by IS NOT NULL;
-
-CREATE INDEX bugsummary__product__idx ON BugSummary (product)
- WHERE product IS NOT NULL;
-
-CREATE INDEX bugsummary__productseries__idx ON BugSummary (productseries)
- WHERE productseries IS NOT NULL;
-
--- can only have one fact row per set of dimensions
-CREATE UNIQUE INDEX bugsummary__dimensions__unique ON bugsummary (
- status,
- COALESCE(product, (-1)),
- COALESCE(productseries, (-1)),
- COALESCE(distribution, (-1)),
- COALESCE(distroseries, (-1)),
- COALESCE(sourcepackagename, (-1)),
- COALESCE(viewed_by, (-1)),
- COALESCE(milestone, (-1)),
- COALESCE(tag, ('')));
-
--- While querying is tolerably fast with the base dimension indices,
--- we want snappy:
--- Distribution bug counts
-CREATE INDEX bugsummary__distribution_count__idx
-ON BugSummary (distribution)
-WHERE sourcepackagename IS NULL AND tag IS NULL;
-
--- Distribution wide tag counts
-CREATE INDEX bugsummary__distribution_tag_count__idx
-ON BugSummary (distribution)
-WHERE sourcepackagename IS NULL AND tag IS NOT NULL;
-
--- Everything (counts)
-CREATE INDEX bugsummary__status_count__idx
-ON BugSummary (status)
-WHERE sourcepackagename IS NULL AND tag IS NULL;
-
--- Everything (tags)
-CREATE INDEX bugsummary__tag_count__idx
-ON BugSummary (status)
-WHERE sourcepackagename IS NULL AND tag IS NOT NULL;
-
-
---
--- Functions exist here for pathalogical reasons.
---
--- They can't go in trusted.sql at the moment, because trusted.sql is
--- run against an empty database. If these functions where in there,
--- it would fail because they use BugSummary table as a useful
--- composite type.
--- I suspect we will need to leave these function definitions in here,
--- and move them to trusted.sql after the baseline SQL script contains
--- the BugSummary table definition.
-
--- We also considered switching from one 'trusted.sql' to two files -
--- pre_patch.sql and post_patch.sql. But that doesn't gain us much
--- as the functions need to be declared before the triggers can be
--- created. It would work, but we would still need stub 'forward
--- declarations' of the functions in here, with the functions recreated
--- with the real implementation in post_patch.sql.
-
-CREATE OR REPLACE FUNCTION bug_summary_inc(d bugsummary) RETURNS VOID
-LANGUAGE plpgsql AS
-$$
-BEGIN
- -- Shameless adaption from postgresql manual
- LOOP
- -- first try to update the row
- UPDATE BugSummary SET count = count + 1
- WHERE
- product IS NOT DISTINCT FROM d.product
- AND productseries IS NOT DISTINCT FROM d.productseries
- AND distribution IS NOT DISTINCT FROM d.distribution
- AND distroseries IS NOT DISTINCT FROM d.distroseries
- AND sourcepackagename IS NOT DISTINCT FROM d.sourcepackagename
- AND viewed_by IS NOT DISTINCT FROM d.viewed_by
- AND tag IS NOT DISTINCT FROM d.tag
- AND status IS NOT DISTINCT FROM d.status
- AND milestone IS NOT DISTINCT FROM d.milestone;
- IF found THEN
- RETURN;
- END IF;
- -- not there, so try to insert the key
- -- if someone else inserts the same key concurrently,
- -- we could get a unique-key failure
- BEGIN
- INSERT INTO BugSummary(
- count, product, productseries, distribution,
- distroseries, sourcepackagename, viewed_by, tag,
- status, milestone)
- VALUES (
- 1, d.product, d.productseries, d.distribution,
- d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
- d.status, d.milestone);
- RETURN;
- EXCEPTION WHEN unique_violation THEN
- -- do nothing, and loop to try the UPDATE again
- END;
- END LOOP;
-END;
-$$;
-
-COMMENT ON FUNCTION bug_summary_inc(bugsummary) IS
-'UPSERT into bugsummary incrementing one row';
-
-CREATE OR REPLACE FUNCTION bug_summary_dec(bugsummary) RETURNS VOID
-LANGUAGE SQL AS
-$$
- -- We own the row reference, so in the absence of bugs this cannot
- -- fail - just decrement the row.
- UPDATE BugSummary SET count = count - 1
- WHERE
- product IS NOT DISTINCT FROM $1.product
- AND productseries IS NOT DISTINCT FROM $1.productseries
- AND distribution IS NOT DISTINCT FROM $1.distribution
- AND distroseries IS NOT DISTINCT FROM $1.distroseries
- AND sourcepackagename IS NOT DISTINCT FROM $1.sourcepackagename
- AND viewed_by IS NOT DISTINCT FROM $1.viewed_by
- AND tag IS NOT DISTINCT FROM $1.tag
- AND status IS NOT DISTINCT FROM $1.status
- AND milestone IS NOT DISTINCT FROM $1.milestone;
- -- gc the row (perhaps should be garbo but easy enough to add here:
- DELETE FROM bugsummary
- WHERE
- count=0
- AND product IS NOT DISTINCT FROM $1.product
- AND productseries IS NOT DISTINCT FROM $1.productseries
- AND distribution IS NOT DISTINCT FROM $1.distribution
- AND distroseries IS NOT DISTINCT FROM $1.distroseries
- AND sourcepackagename IS NOT DISTINCT FROM $1.sourcepackagename
- AND viewed_by IS NOT DISTINCT FROM $1.viewed_by
- AND tag IS NOT DISTINCT FROM $1.tag
- AND status IS NOT DISTINCT FROM $1.status
- AND milestone IS NOT DISTINCT FROM $1.milestone;
- -- If its not found then someone else also dec'd and won concurrently.
-$$;
-
-COMMENT ON FUNCTION bug_summary_inc(bugsummary) IS
-'UPSERT into bugsummary incrementing one row';
-
-
-CREATE OR REPLACE FUNCTION bug_row(bug_id integer)
-RETURNS bug LANGUAGE SQL STABLE AS
-$$
- SELECT * FROM Bug WHERE id=$1;
-$$;
-COMMENT ON FUNCTION bug_row(integer) IS
-'Helper for manually testing functions requiring a bug row as input. eg. SELECT * FROM bugsummary_tags(bug_row(1))';
-
-
-CREATE OR REPLACE FUNCTION bugsummary_viewers(BUG_ROW bug)
-RETURNS SETOF bugsubscription LANGUAGE SQL STABLE AS
-$$
- SELECT *
- FROM BugSubscription
- WHERE
- bugsubscription.bug=$1.id
- AND $1.private IS TRUE;
-$$;
-
-COMMENT ON FUNCTION bugsummary_viewers(bug) IS
-'Return (bug, viewer) for all viewers if private, nothing otherwise';
-
-
-CREATE OR REPLACE FUNCTION bugsummary_tags(BUG_ROW bug)
-RETURNS SETOF bugtag LANGUAGE SQL STABLE AS
-$$
- SELECT * FROM BugTag WHERE BugTag.bug = $1.id
- UNION ALL
- SELECT NULL::integer, $1.id, NULL::text;
-$$;
-
-COMMENT ON FUNCTION bugsummary_tags(bug) IS
-'Return (bug, tag) for all tags + (bug, NULL::text)';
-
-
-CREATE OR REPLACE FUNCTION bugsummary_tasks(BUG_ROW bug)
-RETURNS SETOF bugtask LANGUAGE plpgsql STABLE AS
-$$
-DECLARE
- bt bugtask%ROWTYPE;
- r record;
-BEGIN
- bt.bug = BUG_ROW.id;
-
- -- One row only for each target permutation - need to ignore other fields
- -- like date last modified to deal with conjoined masters and multiple
- -- sourcepackage tasks in a distro.
- FOR r IN
- SELECT
- product, productseries, distribution, distroseries,
- sourcepackagename, status, milestone
- FROM BugTask WHERE bug=BUG_ROW.id
- UNION
- SELECT
- product, productseries, distribution, distroseries,
- NULL, status, milestone
- FROM BugTask WHERE bug=BUG_ROW.id AND sourcepackagename IS NOT NULL
- LOOP
- bt.product = r.product;
- bt.productseries = r.productseries;
- bt.distribution = r.distribution;
- bt.distroseries = r.distroseries;
- bt.sourcepackagename = r.sourcepackagename;
- bt.status = r.status;
- bt.milestone = r.milestone;
- RETURN NEXT bt;
- END LOOP;
-END;
-$$;
-
-COMMENT ON FUNCTION bugsummary_tasks(bug) IS
-'Return all tasks for the bug + all sourcepackagename tasks again with the sourcepackagename squashed';
-
-
-CREATE OR REPLACE FUNCTION bugsummary_locations(BUG_ROW bug)
-RETURNS SETOF bugsummary LANGUAGE plpgsql AS
-$$
-BEGIN
- IF BUG_ROW.duplicateof IS NOT NULL THEN
- RETURN;
- END IF;
- RETURN QUERY
- SELECT
- CAST(NULL AS integer) AS id,
- CAST(1 AS integer) AS count,
- product, productseries, distribution, distroseries,
- sourcepackagename, person AS viewed_by, tag, status, milestone
- FROM bugsummary_tasks(BUG_ROW) AS tasks
- JOIN bugsummary_tags(BUG_ROW) AS bug_tags ON TRUE
- LEFT OUTER JOIN bugsummary_viewers(BUG_ROW) AS bug_viewers ON TRUE;
-END;
-$$;
-
-COMMENT ON FUNCTION bugsummary_locations(bug) IS
-'Calculate what BugSummary rows should exist for a given Bug.';
-
-
-CREATE OR REPLACE FUNCTION summarise_bug(BUG_ROW bug) RETURNS VOID
-LANGUAGE plpgsql VOLATILE AS
-$$
-DECLARE
- d bugsummary%ROWTYPE;
-BEGIN
- -- Grab a suitable lock before we start calculating bug summary data
- -- to avoid race conditions. This lock allows SELECT but blocks writes.
- LOCK TABLE BugSummary IN ROW EXCLUSIVE MODE;
- FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP
- PERFORM bug_summary_inc(d);
- END LOOP;
-END;
-$$;
-
-COMMENT ON FUNCTION summarise_bug(bug) IS
-'AFTER summarise a bug row into bugsummary.';
-
-
-CREATE OR REPLACE FUNCTION unsummarise_bug(BUG_ROW bug) RETURNS VOID
-LANGUAGE plpgsql VOLATILE AS
-$$
-DECLARE
- d bugsummary%ROWTYPE;
-BEGIN
- -- Grab a suitable lock before we start calculating bug summary data
- -- to avoid race conditions. This lock allows SELECT but blocks writes.
- LOCK TABLE BugSummary IN ROW EXCLUSIVE MODE;
- FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP
- PERFORM bug_summary_dec(d);
- END LOOP;
-END;
-$$;
-
-COMMENT ON FUNCTION unsummarise_bug(bug) IS
-'AFTER unsummarise a bug row from bugsummary.';
-
-
-CREATE OR REPLACE FUNCTION bug_maintain_bug_summary() RETURNS TRIGGER
-LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO public AS
-$$
-BEGIN
- -- There is no INSERT logic, as a bug will not have any summary
- -- information until BugTask rows have been attached.
- IF TG_OP = 'UPDATE' THEN
- IF OLD.duplicateof IS DISTINCT FROM NEW.duplicateof
- OR OLD.private IS DISTINCT FROM NEW.private THEN
- PERFORM unsummarise_bug(OLD);
- PERFORM summarise_bug(NEW);
- END IF;
-
- ELSIF TG_OP = 'DELETE' THEN
- PERFORM unsummarise_bug(OLD);
- END IF;
-
- RETURN NULL; -- Ignored - this is an AFTER trigger
-END;
-$$;
-
-COMMENT ON FUNCTION bug_maintain_bug_summary() IS
-'AFTER trigger on bug maintaining the bugs summaries in bugsummary.';
-
-
-CREATE OR REPLACE FUNCTION bugtask_maintain_bug_summary() RETURNS TRIGGER
-LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO public AS
-$$
-BEGIN
- -- This trigger only works if we are inserting, updating or deleting
- -- a single row per statement.
-
- -- Unlike bug_maintain_bug_summary, this trigger does not have access
- -- to the old bug when invoked as an AFTER trigger. To work around this
- -- we install this trigger as both a BEFORE and an AFTER trigger.
- IF TG_OP = 'INSERT' THEN
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(NEW.bug));
- ELSE
- PERFORM summarise_bug(bug_row(NEW.bug));
- END IF;
- RETURN NEW;
-
- ELSIF TG_OP = 'DELETE' THEN
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(OLD.bug));
- ELSE
- PERFORM summarise_bug(bug_row(OLD.bug));
- END IF;
- RETURN OLD;
-
- ELSE
- IF (OLD.product IS DISTINCT FROM NEW.product
- OR OLD.productseries IS DISTINCT FROM NEW.productseries
- OR OLD.distribution IS DISTINCT FROM NEW.distribution
- OR OLD.distroseries IS DISTINCT FROM NEW.distroseries
- OR OLD.sourcepackagename IS DISTINCT FROM NEW.sourcepackagename
- OR OLD.status IS DISTINCT FROM NEW.status
- OR OLD.milestone IS DISTINCT FROM NEW.milestone) THEN
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(OLD.bug));
- IF OLD.bug <> NEW.bug THEN
- PERFORM unsummarise_bug(bug_row(NEW.bug));
- END IF;
- ELSE
- PERFORM summarise_bug(bug_row(OLD.bug));
- IF OLD.bug <> NEW.bug THEN
- PERFORM summarise_bug(bug_row(NEW.bug));
- END IF;
- END IF;
- END IF;
- RETURN NEW;
- END IF;
-END;
-$$;
-
-COMMENT ON FUNCTION bugtask_maintain_bug_summary() IS
-'Both BEFORE & AFTER trigger on bugtask maintaining the bugs summaries in bugsummary.';
-
-
-CREATE OR REPLACE FUNCTION bugsubscription_maintain_bug_summary()
-RETURNS TRIGGER LANGUAGE plpgsql VOLATILE
-SECURITY DEFINER SET search_path TO public AS
-$$
-BEGIN
- -- This trigger only works if we are inserting, updating or deleting
- -- a single row per statement.
- IF TG_OP = 'INSERT' THEN
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(NEW.bug));
- ELSE
- PERFORM summarise_bug(bug_row(NEW.bug));
- END IF;
- RETURN NEW;
- ELSIF TG_OP = 'DELETE' THEN
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(OLD.bug));
- ELSE
- PERFORM summarise_bug(bug_row(OLD.bug));
- END IF;
- RETURN OLD;
- ELSE
- IF (OLD.person IS DISTINCT FROM NEW.person
- OR OLD.bug IS DISTINCT FROM NEW.bug) THEN
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(OLD.bug));
- IF OLD.bug <> NEW.bug THEN
- PERFORM unsummarise_bug(bug_row(NEW.bug));
- END IF;
- ELSE
- PERFORM summarise_bug(bug_row(OLD.bug));
- IF OLD.bug <> NEW.bug THEN
- PERFORM summarise_bug(bug_row(NEW.bug));
- END IF;
- END IF;
- END IF;
- RETURN NEW;
- END IF;
-END;
-$$;
-
-COMMENT ON FUNCTION bugsubscription_maintain_bug_summary() IS
-'AFTER trigger on bugsubscription maintaining the bugs summaries in bugsummary.';
-
-
-CREATE OR REPLACE FUNCTION bugtag_maintain_bug_summary() RETURNS TRIGGER
-LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO public AS
-$$
-BEGIN
- IF TG_OP = 'INSERT' THEN
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(NEW.bug));
- ELSE
- PERFORM summarise_bug(bug_row(NEW.bug));
- END IF;
- RETURN NEW;
- ELSIF TG_OP = 'DELETE' THEN
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(OLD.bug));
- ELSE
- PERFORM summarise_bug(bug_row(OLD.bug));
- END IF;
- RETURN OLD;
- ELSE
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(OLD.bug));
- IF OLD.bug <> NEW.bug THEN
- PERFORM unsummarise_bug(bug_row(NEW.bug));
- END IF;
- ELSE
- PERFORM summarise_bug(bug_row(OLD.bug));
- IF OLD.bug <> NEW.bug THEN
- PERFORM summarise_bug(bug_row(NEW.bug));
- END IF;
- END IF;
- RETURN NEW;
- END IF;
-END;
-$$;
-
-COMMENT ON FUNCTION bugtag_maintain_bug_summary() IS
-'AFTER trigger on bugtag maintaining the bugs summaries in bugsummary.';
-
-
--- we need to maintain the summaries when things change. Each variable the
--- population script above uses needs to be accounted for.
-
--- bug: duplicateof, private (not INSERT because a task is needed to be included in summaries.
-CREATE TRIGGER bug_maintain_bug_summary_trigger
-AFTER UPDATE OR DELETE ON bug
-FOR EACH ROW EXECUTE PROCEDURE bug_maintain_bug_summary();
-
--- bugtask: target, status, milestone
-CREATE TRIGGER bugtask_maintain_bug_summary_before_trigger
-BEFORE INSERT OR UPDATE OR DELETE ON bugtask
-FOR EACH ROW EXECUTE PROCEDURE bugtask_maintain_bug_summary();
-
-CREATE TRIGGER bugtask_maintain_bug_summary_after_trigger
-AFTER INSERT OR UPDATE OR DELETE ON bugtask
-FOR EACH ROW EXECUTE PROCEDURE bugtask_maintain_bug_summary();
-
--- bugsubscription: existence
-CREATE TRIGGER bugsubscription_maintain_bug_summary_before_trigger
-BEFORE INSERT OR UPDATE OR DELETE ON bugsubscription
-FOR EACH ROW EXECUTE PROCEDURE bugsubscription_maintain_bug_summary();
-
-CREATE TRIGGER bugsubscription_maintain_bug_summary_after_trigger
-AFTER INSERT OR UPDATE OR DELETE ON bugsubscription
-FOR EACH ROW EXECUTE PROCEDURE bugsubscription_maintain_bug_summary();
-
--- bugtag: existence
-CREATE TRIGGER bugtag_maintain_bug_summary_before_trigger
-BEFORE INSERT OR UPDATE OR DELETE ON bugtag
-FOR EACH ROW EXECUTE PROCEDURE bugtag_maintain_bug_summary();
-
-CREATE TRIGGER bugtag_maintain_bug_summary_after_trigger
-AFTER INSERT OR UPDATE OR DELETE ON bugtag
-FOR EACH ROW EXECUTE PROCEDURE bugtag_maintain_bug_summary();
-
-INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 63, 0);
=== removed file 'database/schema/patch-2208-63-1.sql'
--- database/schema/patch-2208-63-1.sql 2011-06-09 12:55:22 +0000
+++ database/schema/patch-2208-63-1.sql 1970-01-01 00:00:00 +0000
@@ -1,460 +0,0 @@
--- Copyright 2011 Canonical Ltd. This software is licensed under the
--- GNU Affero General Public License version 3 (see the file LICENSE).
-
-SET client_min_messages=ERROR;
-
-CREATE OR REPLACE FUNCTION bug_summary_inc(d bugsummary) RETURNS VOID
-LANGUAGE plpgsql AS
-$$
-BEGIN
- -- Shameless adaption from postgresql manual
- LOOP
- -- first try to update the row
- UPDATE BugSummary SET count = count + 1
- WHERE
- ((d.product IS NULL AND product IS NULL)
- OR product = d.product)
- AND ((d.productseries IS NULL AND productseries IS NULL)
- OR productseries = d.productseries)
- AND ((d.distribution IS NULL AND distribution IS NULL)
- OR distribution = d.distribution)
- AND ((d.distroseries IS NULL AND distroseries IS NULL)
- OR distroseries = d.distroseries)
- AND ((d.sourcepackagename IS NULL AND sourcepackagename IS NULL)
- OR sourcepackagename = d.sourcepackagename)
- AND ((d.viewed_by IS NULL AND viewed_by IS NULL)
- OR viewed_by = d.viewed_by)
- AND ((d.tag IS NULL AND tag IS NULL)
- OR tag = d.tag)
- AND ((d.status IS NULL AND status IS NULL)
- OR status = d.status)
- AND ((d.milestone IS NULL AND milestone IS NULL)
- OR milestone = d.milestone);
- IF found THEN
- RETURN;
- END IF;
- -- not there, so try to insert the key
- -- if someone else inserts the same key concurrently,
- -- we could get a unique-key failure
- BEGIN
- INSERT INTO BugSummary(
- count, product, productseries, distribution,
- distroseries, sourcepackagename, viewed_by, tag,
- status, milestone)
- VALUES (
- 1, d.product, d.productseries, d.distribution,
- d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
- d.status, d.milestone);
- RETURN;
- EXCEPTION WHEN unique_violation THEN
- -- do nothing, and loop to try the UPDATE again
- END;
- END LOOP;
-END;
-$$;
-
-COMMENT ON FUNCTION bug_summary_inc(bugsummary) IS
-'UPSERT into bugsummary incrementing one row';
-
-CREATE OR REPLACE FUNCTION bug_summary_dec(bugsummary) RETURNS VOID
-LANGUAGE SQL AS
-$$
- -- We own the row reference, so in the absence of bugs this cannot
- -- fail - just decrement the row.
- UPDATE BugSummary SET count = count - 1
- WHERE
- (($1.product IS NULL AND product IS NULL)
- OR product = $1.product)
- AND (($1.productseries IS NULL AND productseries IS NULL)
- OR productseries = $1.productseries)
- AND (($1.distribution IS NULL AND distribution IS NULL)
- OR distribution = $1.distribution)
- AND (($1.distroseries IS NULL AND distroseries IS NULL)
- OR distroseries = $1.distroseries)
- AND (($1.sourcepackagename IS NULL AND sourcepackagename IS NULL)
- OR sourcepackagename = $1.sourcepackagename)
- AND (($1.viewed_by IS NULL AND viewed_by IS NULL)
- OR viewed_by = $1.viewed_by)
- AND (($1.tag IS NULL AND tag IS NULL)
- OR tag = $1.tag)
- AND (($1.status IS NULL AND status IS NULL)
- OR status = $1.status)
- AND (($1.milestone IS NULL AND milestone IS NULL)
- OR milestone = $1.milestone);
- -- gc the row (perhaps should be garbo but easy enough to add here:
- DELETE FROM bugsummary
- WHERE
- count=0
- AND (($1.product IS NULL AND product IS NULL)
- OR product = $1.product)
- AND (($1.productseries IS NULL AND productseries IS NULL)
- OR productseries = $1.productseries)
- AND (($1.distribution IS NULL AND distribution IS NULL)
- OR distribution = $1.distribution)
- AND (($1.distroseries IS NULL AND distroseries IS NULL)
- OR distroseries = $1.distroseries)
- AND (($1.sourcepackagename IS NULL AND sourcepackagename IS NULL)
- OR sourcepackagename = $1.sourcepackagename)
- AND (($1.viewed_by IS NULL AND viewed_by IS NULL)
- OR viewed_by = $1.viewed_by)
- AND (($1.tag IS NULL AND tag IS NULL)
- OR tag = $1.tag)
- AND (($1.status IS NULL AND status IS NULL)
- OR status = $1.status)
- AND (($1.milestone IS NULL AND milestone IS NULL)
- OR milestone = $1.milestone);
- -- If its not found then someone else also dec'd and won concurrently.
-$$;
-
-
--- bad comment fixup
-COMMENT ON FUNCTION bug_summary_dec(bugsummary) IS
-'UPSERT into bugsummary incrementing one row';
-
-CREATE OR REPLACE FUNCTION ensure_bugsummary_temp_journal() RETURNS VOID
-LANGUAGE plpgsql VOLATILE AS
-$$
-DECLARE
-BEGIN
- CREATE TEMPORARY TABLE bugsummary_temp_journal (
- LIKE bugsummary ) ON COMMIT DROP;
- ALTER TABLE bugsummary_temp_journal ALTER COLUMN id DROP NOT NULL;
- -- For safety use a unique index.
- CREATE UNIQUE INDEX bugsummary__temp_journal__dimensions__unique ON bugsummary_temp_journal (
- status,
- COALESCE(product, (-1)),
- COALESCE(productseries, (-1)),
- COALESCE(distribution, (-1)),
- COALESCE(distroseries, (-1)),
- COALESCE(sourcepackagename, (-1)),
- COALESCE(viewed_by, (-1)),
- COALESCE(milestone, (-1)),
- COALESCE(tag, ('')));
-EXCEPTION
- WHEN duplicate_table THEN
- NULL;
-END;
-$$;
-
-COMMENT ON FUNCTION ensure_bugsummary_temp_journal() IS
-'Create a temporary table bugsummary_temp_journal if it does not exist.';
-
-
-CREATE OR REPLACE FUNCTION bug_summary_temp_journal_dec(d bugsummary) RETURNS VOID
-LANGUAGE plpgsql AS
-$$
-BEGIN
- -- We own the row reference, so in the absence of bugs this cannot
- -- fail - just decrement the row.
- UPDATE BugSummary_Temp_Journal SET count = count - 1
- WHERE
- ((d.product IS NULL AND product IS NULL)
- OR product = d.product)
- AND ((d.productseries IS NULL AND productseries IS NULL)
- OR productseries = d.productseries)
- AND ((d.distribution IS NULL AND distribution IS NULL)
- OR distribution = d.distribution)
- AND ((d.distroseries IS NULL AND distroseries IS NULL)
- OR distroseries = d.distroseries)
- AND ((d.sourcepackagename IS NULL AND sourcepackagename IS NULL)
- OR sourcepackagename = d.sourcepackagename)
- AND ((d.viewed_by IS NULL AND viewed_by IS NULL)
- OR viewed_by = d.viewed_by)
- AND ((d.tag IS NULL AND tag IS NULL)
- OR tag = d.tag)
- AND ((d.status IS NULL AND status IS NULL)
- OR status = d.status)
- AND ((d.milestone IS NULL AND milestone IS NULL)
- OR milestone = d.milestone);
- IF found THEN
- RETURN;
- END IF;
- -- not there, so try to insert the key
- INSERT INTO BugSummary_Temp_Journal(
- count, product, productseries, distribution,
- distroseries, sourcepackagename, viewed_by, tag,
- status, milestone)
- VALUES (
- -1, d.product, d.productseries, d.distribution,
- d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
- d.status, d.milestone);
- RETURN;
-END;
-$$;
-
-COMMENT ON FUNCTION bug_summary_temp_journal_dec(bugsummary) IS
-'UPSERT into bugsummary_temp_journal decrementing one row';
-
-CREATE OR REPLACE FUNCTION bug_summary_temp_journal_inc(d bugsummary) RETURNS VOID
-LANGUAGE plpgsql AS
-$$
-BEGIN
- -- first try to update the row
- UPDATE BugSummary_Temp_Journal SET count = count + 1
- WHERE
- ((d.product IS NULL AND product IS NULL)
- OR product = d.product)
- AND ((d.productseries IS NULL AND productseries IS NULL)
- OR productseries = d.productseries)
- AND ((d.distribution IS NULL AND distribution IS NULL)
- OR distribution = d.distribution)
- AND ((d.distroseries IS NULL AND distroseries IS NULL)
- OR distroseries = d.distroseries)
- AND ((d.sourcepackagename IS NULL AND sourcepackagename IS NULL)
- OR sourcepackagename = d.sourcepackagename)
- AND ((d.viewed_by IS NULL AND viewed_by IS NULL)
- OR viewed_by = d.viewed_by)
- AND ((d.tag IS NULL AND tag IS NULL)
- OR tag = d.tag)
- AND ((d.status IS NULL AND status IS NULL)
- OR status = d.status)
- AND ((d.milestone IS NULL AND milestone IS NULL)
- OR milestone = d.milestone);
- IF found THEN
- RETURN;
- END IF;
- -- not there, so try to insert the key
- INSERT INTO BugSummary_Temp_Journal(
- count, product, productseries, distribution,
- distroseries, sourcepackagename, viewed_by, tag,
- status, milestone)
- VALUES (
- 1, d.product, d.productseries, d.distribution,
- d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
- d.status, d.milestone);
- RETURN;
-END;
-$$;
-
-COMMENT ON FUNCTION bug_summary_temp_journal_inc(bugsummary) IS
-'UPSERT into bugsummary incrementing one row';
-
-CREATE OR REPLACE FUNCTION bug_summary_flush_temp_journal() RETURNS VOID
-LANGUAGE plpgsql VOLATILE AS
-$$
-DECLARE
- d bugsummary%ROWTYPE;
-BEGIN
- -- may get called even though no summaries were made (for simplicity in the
- -- callers)
- PERFORM ensure_bugsummary_temp_journal();
- FOR d IN SELECT * FROM bugsummary_temp_journal LOOP
- IF d.count < 0 THEN
- PERFORM bug_summary_dec(d);
- ELSIF d.count > 0 THEN
- PERFORM bug_summary_inc(d);
- END IF;
- END LOOP;
- DELETE FROM bugsummary_temp_journal;
-END;
-$$;
-
-COMMENT ON FUNCTION bug_summary_flush_temp_journal() IS
-'flush the temporary bugsummary journal into the bugsummary table';
-
-CREATE OR REPLACE FUNCTION unsummarise_bug(BUG_ROW bug) RETURNS VOID
-LANGUAGE plpgsql VOLATILE AS
-$$
-DECLARE
- d bugsummary%ROWTYPE;
-BEGIN
- PERFORM ensure_bugsummary_temp_journal();
- FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP
- PERFORM bug_summary_temp_journal_dec(d);
- END LOOP;
-END;
-$$;
-
-CREATE OR REPLACE FUNCTION summarise_bug(BUG_ROW bug) RETURNS VOID
-LANGUAGE plpgsql VOLATILE AS
-$$
-DECLARE
- d bugsummary%ROWTYPE;
-BEGIN
- PERFORM ensure_bugsummary_temp_journal();
- FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP
- PERFORM bug_summary_temp_journal_inc(d);
- END LOOP;
-END;
-$$;
-
--- fixed to summarise less often and use the journal.
-CREATE OR REPLACE FUNCTION bugsubscription_maintain_bug_summary()
-RETURNS TRIGGER LANGUAGE plpgsql VOLATILE
-SECURITY DEFINER SET search_path TO public AS
-$$
-BEGIN
- -- This trigger only works if we are inserting, updating or deleting
- -- a single row per statement.
- IF TG_OP = 'INSERT' THEN
- IF NOT (bug_row(NEW.bug)).private THEN
- -- Public subscriptions are not aggregated.
- RETURN NEW;
- END IF;
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(NEW.bug));
- ELSE
- PERFORM summarise_bug(bug_row(NEW.bug));
- END IF;
- PERFORM bug_summary_flush_temp_journal();
- RETURN NEW;
- ELSIF TG_OP = 'DELETE' THEN
- IF NOT (bug_row(OLD.bug)).private THEN
- -- Public subscriptions are not aggregated.
- RETURN OLD;
- END IF;
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(OLD.bug));
- ELSE
- PERFORM summarise_bug(bug_row(OLD.bug));
- END IF;
- PERFORM bug_summary_flush_temp_journal();
- RETURN OLD;
- ELSE
- IF (OLD.person IS DISTINCT FROM NEW.person
- OR OLD.bug IS DISTINCT FROM NEW.bug) THEN
- IF TG_WHEN = 'BEFORE' THEN
- IF (bug_row(OLD.bug)).private THEN
- -- Public subscriptions are not aggregated.
- PERFORM unsummarise_bug(bug_row(OLD.bug));
- END IF;
- IF OLD.bug <> NEW.bug AND (bug_row(NEW.bug)).private THEN
- -- Public subscriptions are not aggregated.
- PERFORM unsummarise_bug(bug_row(NEW.bug));
- END IF;
- ELSE
- IF (bug_row(OLD.bug)).private THEN
- -- Public subscriptions are not aggregated.
- PERFORM summarise_bug(bug_row(OLD.bug));
- END IF;
- IF OLD.bug <> NEW.bug AND (bug_row(NEW.bug)).private THEN
- -- Public subscriptions are not aggregated.
- PERFORM summarise_bug(bug_row(NEW.bug));
- END IF;
- END IF;
- END IF;
- PERFORM bug_summary_flush_temp_journal();
- RETURN NEW;
- END IF;
-END;
-$$;
-
--- fixed to use the journal
-CREATE OR REPLACE FUNCTION bugtag_maintain_bug_summary() RETURNS TRIGGER
-LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO public AS
-$$
-BEGIN
- IF TG_OP = 'INSERT' THEN
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(NEW.bug));
- ELSE
- PERFORM summarise_bug(bug_row(NEW.bug));
- END IF;
- PERFORM bug_summary_flush_temp_journal();
- RETURN NEW;
- ELSIF TG_OP = 'DELETE' THEN
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(OLD.bug));
- ELSE
- PERFORM summarise_bug(bug_row(OLD.bug));
- END IF;
- PERFORM bug_summary_flush_temp_journal();
- RETURN OLD;
- ELSE
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(OLD.bug));
- IF OLD.bug <> NEW.bug THEN
- PERFORM unsummarise_bug(bug_row(NEW.bug));
- END IF;
- ELSE
- PERFORM summarise_bug(bug_row(OLD.bug));
- IF OLD.bug <> NEW.bug THEN
- PERFORM summarise_bug(bug_row(NEW.bug));
- END IF;
- END IF;
- PERFORM bug_summary_flush_temp_journal();
- RETURN NEW;
- END IF;
-END;
-$$;
-
--- fixed to use the journal
-CREATE OR REPLACE FUNCTION bug_maintain_bug_summary() RETURNS TRIGGER
-LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO public AS
-$$
-BEGIN
- -- There is no INSERT logic, as a bug will not have any summary
- -- information until BugTask rows have been attached.
- IF TG_OP = 'UPDATE' THEN
- IF OLD.duplicateof IS DISTINCT FROM NEW.duplicateof
- OR OLD.private IS DISTINCT FROM NEW.private THEN
- PERFORM unsummarise_bug(OLD);
- PERFORM summarise_bug(NEW);
- END IF;
-
- ELSIF TG_OP = 'DELETE' THEN
- PERFORM unsummarise_bug(OLD);
- END IF;
-
- PERFORM bug_summary_flush_temp_journal();
- RETURN NULL; -- Ignored - this is an AFTER trigger
-END;
-$$;
-
--- fixed to use the journal
-CREATE OR REPLACE FUNCTION bugtask_maintain_bug_summary() RETURNS TRIGGER
-LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO public AS
-$$
-BEGIN
- -- This trigger only works if we are inserting, updating or deleting
- -- a single row per statement.
-
- -- Unlike bug_maintain_bug_summary, this trigger does not have access
- -- to the old bug when invoked as an AFTER trigger. To work around this
- -- we install this trigger as both a BEFORE and an AFTER trigger.
- IF TG_OP = 'INSERT' THEN
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(NEW.bug));
- ELSE
- PERFORM summarise_bug(bug_row(NEW.bug));
- END IF;
- PERFORM bug_summary_flush_temp_journal();
- RETURN NEW;
-
- ELSIF TG_OP = 'DELETE' THEN
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(OLD.bug));
- ELSE
- PERFORM summarise_bug(bug_row(OLD.bug));
- END IF;
- PERFORM bug_summary_flush_temp_journal();
- RETURN OLD;
-
- ELSE
- IF (OLD.product IS DISTINCT FROM NEW.product
- OR OLD.productseries IS DISTINCT FROM NEW.productseries
- OR OLD.distribution IS DISTINCT FROM NEW.distribution
- OR OLD.distroseries IS DISTINCT FROM NEW.distroseries
- OR OLD.sourcepackagename IS DISTINCT FROM NEW.sourcepackagename
- OR OLD.status IS DISTINCT FROM NEW.status
- OR OLD.milestone IS DISTINCT FROM NEW.milestone) THEN
- IF TG_WHEN = 'BEFORE' THEN
- PERFORM unsummarise_bug(bug_row(OLD.bug));
- IF OLD.bug <> NEW.bug THEN
- PERFORM unsummarise_bug(bug_row(NEW.bug));
- END IF;
- ELSE
- PERFORM summarise_bug(bug_row(OLD.bug));
- IF OLD.bug <> NEW.bug THEN
- PERFORM summarise_bug(bug_row(NEW.bug));
- END IF;
- END IF;
- END IF;
- PERFORM bug_summary_flush_temp_journal();
- RETURN NEW;
- END IF;
-END;
-$$;
-
-
-INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 63, 1);
=== removed file 'database/schema/patch-2208-63-2.sql'
--- database/schema/patch-2208-63-2.sql 2011-06-09 11:50:58 +0000
+++ database/schema/patch-2208-63-2.sql 1970-01-01 00:00:00 +0000
@@ -1,13 +0,0 @@
--- Copyright 2011 Canonical Ltd. This software is licensed under the
--- GNU Affero General Public License version 3 (see the file LICENSE).
-
-SET client_min_messages=ERROR;
-
-CREATE INDEX bugsummary__milestone__idx
-ON BugSummary(milestone) WHERE milestone IS NOT NULL;
-
-
-CREATE INDEX bugsummary__full__idx
-ON BugSummary(status, product, productseries, distribution, distroseries, sourcepackagename, viewed_by, milestone, tag);
-
-INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 63, 2);
=== removed file 'database/schema/patch-2208-63-3.sql'
--- database/schema/patch-2208-63-3.sql 2011-06-09 22:27:34 +0000
+++ database/schema/patch-2208-63-3.sql 1970-01-01 00:00:00 +0000
@@ -1,55 +0,0 @@
--- Copyright 2011 Canonical Ltd. This software is licensed under the
--- GNU Affero General Public License version 3 (see the file LICENSE).
-
-SET client_min_messages=ERROR;
-
-
--- Create a journal for BugSummary updates.
--- This is a separate DB patch as the table needs to be created and
--- added to replication before triggers are created, and we want to
--- do this live. We discussed not replicating this table, but this
--- would break our ability to failover to a new master.
-
-CREATE TABLE BugSummaryJournal (
- id serial PRIMARY KEY,
- count INTEGER NOT NULL default 0,
- product INTEGER REFERENCES Product ON DELETE CASCADE,
- productseries INTEGER REFERENCES ProductSeries ON DELETE CASCADE,
- distribution INTEGER REFERENCES Distribution ON DELETE CASCADE,
- distroseries INTEGER REFERENCES DistroSeries ON DELETE CASCADE,
- sourcepackagename INTEGER REFERENCES SourcePackageName ON DELETE CASCADE,
- viewed_by INTEGER,
- tag TEXT,
- status INTEGER NOT NULL,
- milestone INTEGER REFERENCES Milestone ON DELETE CASCADE);
-
--- Fat index for fast lookups
-CREATE INDEX bugsummaryjournal__full__idx ON BugSummaryJournal (
- status, product, productseries, distribution, distroseries,
- sourcepackagename, viewed_by, milestone, tag);
-
--- Indexes for fast deletions.
-CREATE INDEX bugsummaryjournal__viewed_by__idx
- ON BugSummaryJournal(viewed_by) WHERE viewed_by IS NOT NULL;
-CREATE INDEX bugsummaryjournal__milestone__idx
- ON BugSummaryJournal(milestone) WHERE milestone IS NOT NULL;
-
-
--- Combined view so we don't have to manually collate rows from both tables.
--- Note that we flip the sign of the id column of BugSummaryJournal to avoid
--- clashes. This is enough to keep Storm happy as it never needs to update
--- this table, and there are no other suitable primary keys.
--- We don't SUM() rows here to ensure PostgreSQL has the most hope of
--- generating good query plans when we query this view.
-CREATE OR REPLACE VIEW CombinedBugSummary AS (
- SELECT
- id, count, product, productseries, distribution, distroseries,
- sourcepackagename, viewed_by, tag, status, milestone
- FROM BugSummary
- UNION ALL
- SELECT
- -id as id, count, product, productseries, distribution, distroseries,
- sourcepackagename, viewed_by, tag, status, milestone
- FROM BugSummaryJournal);
-
-INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 63, 3);
=== removed file 'database/schema/patch-2208-63-4.sql'
--- database/schema/patch-2208-63-4.sql 2011-06-09 20:46:15 +0000
+++ database/schema/patch-2208-63-4.sql 1970-01-01 00:00:00 +0000
@@ -1,171 +0,0 @@
--- Copyright 2011 Canonical Ltd. This software is licensed under the
--- GNU Affero General Public License version 3 (see the file LICENSE).
-
-SET client_min_messages=ERROR;
-
-CREATE OR REPLACE FUNCTION bugsummary_journal_ins(d bugsummary)
-RETURNS VOID
-LANGUAGE plpgsql AS
-$$
-BEGIN
- IF d.count <> 0 THEN
- INSERT INTO BugSummaryJournal (
- count, product, productseries, distribution,
- distroseries, sourcepackagename, viewed_by, tag,
- status, milestone)
- VALUES (
- d.count, d.product, d.productseries, d.distribution,
- d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
- d.status, d.milestone);
- END IF;
-END;
-$$;
-
-COMMENT ON FUNCTION bugsummary_journal_ins(bugsummary) IS
-'Add an entry into BugSummaryJournal';
-
-
-CREATE OR REPLACE FUNCTION bugsummary_rollup_journal() RETURNS VOID
-LANGUAGE plpgsql VOLATILE
-SECURITY DEFINER SET search_path TO public AS
-$$
-DECLARE
- d bugsummary%ROWTYPE;
- max_id integer;
-BEGIN
- -- Lock so we don't content with other invokations of this
- -- function. We can happily lock the BugSummary table for writes
- -- as this function is the only thing that updates that table.
- -- BugSummaryJournal remains unlocked so nothing should be blocked.
- LOCK TABLE BugSummary IN ROW EXCLUSIVE MODE;
-
- SELECT MAX(id) INTO max_id FROM BugSummaryJournal;
-
- FOR d IN
- SELECT
- NULL as id,
- SUM(count),
- product,
- productseries,
- distribution,
- distroseries,
- sourcepackagename,
- viewed_by,
- tag,
- status,
- milestone
- FROM BugSummaryJournal
- WHERE id <= max_id
- GROUP BY
- product, productseries, distribution, distroseries,
- sourcepackagename, viewed_by, tag, status, milestone
- HAVING sum(count) <> 0
- LOOP
- IF d.count < 0 THEN
- PERFORM bug_summary_dec(d);
- ELSIF d.count > 0 THEN
- PERFORM bug_summary_inc(d);
- END IF;
- END LOOP;
-
- DELETE FROM BugSummaryJournal WHERE id <= max_id;
-END;
-$$;
-
-CREATE OR REPLACE FUNCTION bug_summary_dec(bugsummary) RETURNS VOID
-LANGUAGE SQL AS
-$$
- -- We own the row reference, so in the absence of bugs this cannot
- -- fail - just decrement the row.
- UPDATE BugSummary SET count = count + $1.count
- WHERE
- product IS NOT DISTINCT FROM $1.product
- AND productseries IS NOT DISTINCT FROM $1.productseries
- AND distribution IS NOT DISTINCT FROM $1.distribution
- AND distroseries IS NOT DISTINCT FROM $1.distroseries
- AND sourcepackagename IS NOT DISTINCT FROM $1.sourcepackagename
- AND viewed_by IS NOT DISTINCT FROM $1.viewed_by
- AND tag IS NOT DISTINCT FROM $1.tag
- AND status IS NOT DISTINCT FROM $1.status
- AND milestone IS NOT DISTINCT FROM $1.milestone;
- -- gc the row (perhaps should be garbo but easy enough to add here:
- DELETE FROM bugsummary
- WHERE
- count=0
- AND product IS NOT DISTINCT FROM $1.product
- AND productseries IS NOT DISTINCT FROM $1.productseries
- AND distribution IS NOT DISTINCT FROM $1.distribution
- AND distroseries IS NOT DISTINCT FROM $1.distroseries
- AND sourcepackagename IS NOT DISTINCT FROM $1.sourcepackagename
- AND viewed_by IS NOT DISTINCT FROM $1.viewed_by
- AND tag IS NOT DISTINCT FROM $1.tag
- AND status IS NOT DISTINCT FROM $1.status
- AND milestone IS NOT DISTINCT FROM $1.milestone;
- -- If its not found then someone else also dec'd and won concurrently.
-$$;
-
-CREATE OR REPLACE FUNCTION bug_summary_inc(d bugsummary) RETURNS VOID
-LANGUAGE plpgsql AS
-$$
-BEGIN
- -- Shameless adaption from postgresql manual
- LOOP
- -- first try to update the row
- UPDATE BugSummary SET count = count + d.count
- WHERE
- product IS NOT DISTINCT FROM d.product
- AND productseries IS NOT DISTINCT FROM d.productseries
- AND distribution IS NOT DISTINCT FROM d.distribution
- AND distroseries IS NOT DISTINCT FROM d.distroseries
- AND sourcepackagename IS NOT DISTINCT FROM d.sourcepackagename
- AND viewed_by IS NOT DISTINCT FROM d.viewed_by
- AND tag IS NOT DISTINCT FROM d.tag
- AND status IS NOT DISTINCT FROM d.status
- AND milestone IS NOT DISTINCT FROM d.milestone;
- IF found THEN
- RETURN;
- END IF;
- -- not there, so try to insert the key
- -- if someone else inserts the same key concurrently,
- -- we could get a unique-key failure
- BEGIN
- INSERT INTO BugSummary(
- count, product, productseries, distribution,
- distroseries, sourcepackagename, viewed_by, tag,
- status, milestone)
- VALUES (
- d.count, d.product, d.productseries, d.distribution,
- d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
- d.status, d.milestone);
- RETURN;
- EXCEPTION WHEN unique_violation THEN
- -- do nothing, and loop to try the UPDATE again
- END;
- END LOOP;
-END;
-$$;
-
-COMMENT ON FUNCTION bugsummary_rollup_journal() IS
-'Collate and migrate rows from BugSummaryJournal to BugSummary';
-
-CREATE OR REPLACE FUNCTION bug_summary_flush_temp_journal() RETURNS VOID
-LANGUAGE plpgsql VOLATILE AS
-$$
-DECLARE
- d bugsummary%ROWTYPE;
-BEGIN
- -- may get called even though no summaries were made (for simplicity in the
- -- callers)
- PERFORM ensure_bugsummary_temp_journal();
- FOR d IN SELECT * FROM bugsummary_temp_journal LOOP
- PERFORM bugsummary_journal_ins(d);
- END LOOP;
- TRUNCATE bugsummary_temp_journal;
-END;
-$$;
-
-COMMENT ON FUNCTION bug_summary_flush_temp_journal() IS
-'flush the temporary bugsummary journal into the bugsummaryjournal table';
-
-
-INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 63, 4);
=== modified file 'lib/canonical/launchpad/webapp/errorlog.py'
--- lib/canonical/launchpad/webapp/errorlog.py 2011-06-01 19:23:24 +0000
+++ lib/canonical/launchpad/webapp/errorlog.py 2011-06-14 11:48:38 +0000
@@ -40,6 +40,7 @@
IErrorReport,
IErrorReportEvent,
IErrorReportRequest,
+ IUnloggedException,
)
from canonical.launchpad.webapp.opstats import OpStats
from canonical.launchpad.webapp.vhosts import allvhosts
@@ -372,7 +373,18 @@
notify(ErrorReportEvent(entry))
return entry
- def _isIgnoredException(self, strtype, request=None):
+ def _isIgnoredException(self, strtype, request=None, exception=None):
+ """Should the given exception generate an OOPS or be ignored?
+
+ Exceptions will be ignored if they
+ - are specially tagged as being ignorable by having the marker
+ interface IUnloggedException
+ - are of a type included in self._ignored_exceptions, or
+ - were requested with an off-site REFERRER header and are of a
+ type included in self._ignored_exceptions_for_offsite_referer
+ """
+ if IUnloggedException.providedBy(exception):
+ return True
if strtype in self._ignored_exceptions:
return True
if strtype in self._ignored_exceptions_for_offsite_referer:
@@ -409,7 +421,7 @@
tb_text = None
strtype = str(getattr(info[0], '__name__', info[0]))
- if self._isIgnoredException(strtype, request):
+ if self._isIgnoredException(strtype, request, info[1]):
return
if not isinstance(info[2], basestring):
=== modified file 'lib/canonical/launchpad/webapp/interfaces.py'
--- lib/canonical/launchpad/webapp/interfaces.py 2011-04-27 16:01:06 +0000
+++ lib/canonical/launchpad/webapp/interfaces.py 2011-06-14 11:48:38 +0000
@@ -178,7 +178,8 @@
class ILink(ILinkData):
"""An object that represents a link in a menu.
- The attributes name, url and linked may be set by the menus infrastructure.
+ The attributes name, url and linked may be set by the menus
+ infrastructure.
"""
name = Attribute("The name of this link in Python data structures.")
@@ -262,6 +263,7 @@
(object_url_requested_for, broken_link_in_chain)
)
+
# XXX kiko 2007-02-08: this needs reconsideration if we are to make it a truly
# generic thing. The problem lies in the fact that half of this (user, login,
# time zone, developer) is actually useful inside webapp/, and the other half
@@ -307,6 +309,7 @@
connection blows up.
'''
+
#
# Request
#
@@ -406,6 +409,7 @@
class CookieAuthLoggedInEvent:
implements(ILoggedInEvent)
+
def __init__(self, request, login):
self.request = request
self.login = login
@@ -413,6 +417,7 @@
class CookieAuthPrincipalIdentifiedEvent:
implements(IPrincipalIdentifiedEvent)
+
def __init__(self, principal, request, login):
self.principal = principal
self.request = request
@@ -421,6 +426,7 @@
class BasicAuthLoggedInEvent:
implements(ILoggedInEvent, IPrincipalIdentifiedEvent)
+
def __init__(self, request, login, principal):
# these one from ILoggedInEvent
self.login = login
@@ -436,6 +442,7 @@
class LoggedOutEvent:
implements(ILoggedOutEvent)
+
def __init__(self, request):
self.request = request
@@ -527,6 +534,7 @@
you're using right now.
""")
+
class AccessLevel(DBEnumeratedType):
"""The level of access any given principal has."""
use_template(OAuthPermission, exclude='UNAUTHORIZED')
@@ -553,10 +561,10 @@
class BrowserNotificationLevel:
"""Matches the standard logging levels."""
- DEBUG = logging.DEBUG # A debugging message
- INFO = logging.INFO # simple confirmation of a change
- WARNING = logging.WARNING # action will not be successful unless you ...
- ERROR = logging.ERROR # the previous action did not succeed, and why
+ DEBUG = logging.DEBUG # debugging message
+ INFO = logging.INFO # simple confirmation of a change
+ WARNING = logging.WARNING # action will not be successful unless you ...
+ ERROR = logging.ERROR # the previous action did not succeed, and why
ALL_LEVELS = (DEBUG, INFO, WARNING, ERROR)
@@ -646,6 +654,10 @@
"""
+class IUnloggedException(Interface):
+ """An exception that should not be logged in an OOPS report (marker)."""
+
+
class IErrorReportEvent(IObjectEvent):
"""A new error report has been created."""
@@ -673,6 +685,7 @@
description=u"""an identifier for the exception, or None if no
exception has occurred""")
+
#
# Batch Navigation
#
@@ -720,12 +733,12 @@
# Database policies
#
-MAIN_STORE = 'main' # The main database.
+MAIN_STORE = 'main' # The main database.
ALL_STORES = frozenset([MAIN_STORE])
-DEFAULT_FLAVOR = 'default' # Default flavor for current state.
-MASTER_FLAVOR = 'master' # The master database.
-SLAVE_FLAVOR = 'slave' # A slave database.
+DEFAULT_FLAVOR = 'default' # Default flavor for current state.
+MASTER_FLAVOR = 'master' # The master database.
+SLAVE_FLAVOR = 'slave' # A slave database.
class IDatabasePolicy(Interface):
@@ -856,7 +869,6 @@
request = Attribute("The request the event is about")
-
class StartRequestEvent:
"""An event fired once at the start of requests.
=== modified file 'lib/canonical/launchpad/webapp/tests/test_errorlog.py'
--- lib/canonical/launchpad/webapp/tests/test_errorlog.py 2011-06-01 19:35:22 +0000
+++ lib/canonical/launchpad/webapp/tests/test_errorlog.py 2011-06-14 11:48:38 +0000
@@ -41,7 +41,10 @@
OopsLoggingHandler,
ScriptRequest,
)
-from canonical.launchpad.webapp.interfaces import NoReferrerError
+from canonical.launchpad.webapp.interfaces import (
+ IUnloggedException,
+ NoReferrerError,
+ )
from canonical.testing import reset_logging
from lp.app.errors import (
GoneError,
@@ -50,6 +53,7 @@
from lp.services.log.uniquefileallocator import UniqueFileAllocator
from lp.services.osutils import remove_tree
from lp.testing import TestCase
+from lp_sitecustomize import customize_get_converter
UTC = pytz.timezone('UTC')
@@ -967,7 +971,7 @@
self.assertIs(None, self.error_utility.getLastOopsReport())
-class Test404Oops(testtools.TestCase):
+class TestOopsIgnoring(testtools.TestCase):
def test_offsite_404_ignored(self):
# A request originating from another site that generates a NotFound
@@ -990,6 +994,78 @@
request = dict()
self.assertTrue(utility._isIgnoredException('NotFound', request))
+ def test_marked_exception_is_ignored(self):
+ # If an exception has been marked as ignorable, then it is ignored.
+ utility = ErrorReportingUtility()
+ exception = Exception()
+ directlyProvides(exception, IUnloggedException)
+ self.assertTrue(
+ utility._isIgnoredException('RuntimeError', exception=exception))
+
+ def test_unmarked_exception_generates_oops(self):
+ # If an exception has not been marked as ignorable, then it is not.
+ utility = ErrorReportingUtility()
+ exception = Exception()
+ self.assertFalse(
+ utility._isIgnoredException('RuntimeError', exception=exception))
+
+
+class TestWrappedParameterConverter(testtools.TestCase):
+ """Make sure URL parameter type conversions don't generate OOPS reports"""
+
+ def test_return_value_untouched(self):
+ # When a converter succeeds, its return value is passed through the
+ # wrapper untouched.
+
+ class FauxZopePublisherBrowserModule:
+ def get_converter(self, type_):
+ def the_converter(value):
+ return 'converted %r to %s' % (value, type_)
+ return the_converter
+
+ module = FauxZopePublisherBrowserModule()
+ customize_get_converter(module)
+ converter = module.get_converter('int')
+ self.assertEqual("converted '42' to int", converter('42'))
+
+ def test_value_errors_marked(self):
+ # When a ValueError is raised by the wrapped converter, the exception
+ # is marked with IUnloggedException so the OOPS machinery knows that a
+ # report should not be logged.
+
+ class FauxZopePublisherBrowserModule:
+ def get_converter(self, type_):
+ def the_converter(value):
+ raise ValueError
+ return the_converter
+
+ module = FauxZopePublisherBrowserModule()
+ customize_get_converter(module)
+ converter = module.get_converter('int')
+ try:
+ converter(42)
+ except ValueError, e:
+ self.assertTrue(IUnloggedException.providedBy(e))
+
+ def test_other_errors_not_marked(self):
+ # When an exception other than ValueError is raised by the wrapped
+ # converter, the exception is not marked with IUnloggedException an
+ # OOPS report will be created.
+
+ class FauxZopePublisherBrowserModule:
+ def get_converter(self, type_):
+ def the_converter(value):
+ raise RuntimeError
+ return the_converter
+
+ module = FauxZopePublisherBrowserModule()
+ customize_get_converter(module)
+ converter = module.get_converter('int')
+ try:
+ converter(42)
+ except RuntimeError, e:
+ self.assertFalse(IUnloggedException.providedBy(e))
+
def test_suite():
return unittest.TestLoader().loadTestsFromName(__name__)
=== modified file 'lib/lp_sitecustomize.py'
--- lib/lp_sitecustomize.py 2011-04-05 12:41:25 +0000
+++ lib/lp_sitecustomize.py 2011-06-14 11:48:38 +0000
@@ -16,12 +16,15 @@
)
from bzrlib.branch import Branch
+from canonical.launchpad.webapp.interfaces import IUnloggedException
from lp.services.log import loglevels
from lp.services.log.logger import LaunchpadLogger
from lp.services.log.mappingfilter import MappingFilter
from lp.services.log.nullhandler import NullHandler
from lp.services.mime import customizeMimetypes
+from zope.interface import alsoProvides
from zope.security import checker
+import zope.publisher.browser
def add_custom_loglevels():
@@ -136,6 +139,33 @@
silence_transaction_logger()
+def customize_get_converter(zope_publisher_browser=zope.publisher.browser):
+ """URL parameter conversion errors shouldn't generate an OOPS report.
+
+ This injects (monkey patches) our wrapper around get_converter so improper
+ use of parameter type converters (like http://...?foo=bar:int) won't
+ generate OOPS reports.
+ """
+
+ original_get_converter = zope_publisher_browser.get_converter
+
+ def get_converter(*args, **kws):
+ """Get a type converter but turn off OOPS reporting if it fails."""
+ converter = original_get_converter(*args, **kws)
+
+ def wrapped_converter(v):
+ try:
+ return converter(v)
+ except ValueError, e:
+ # Mark the exception as not being OOPS-worthy.
+ alsoProvides(e, IUnloggedException)
+ raise
+
+ return wrapped_converter
+
+ zope_publisher_browser.get_converter = get_converter
+
+
def main(instance_name):
# This is called by our custom buildout-generated sitecustomize.py
# in parts/scripts/sitecustomize.py. The instance name is sent to
@@ -161,3 +191,4 @@
checker.BasicTypes[grouper] = checker._iteratorChecker
silence_warnings()
customize_logger()
+ customize_get_converter()
Follow ups
