Merge lp:~cjwatson/launchpad/db-process-accepted-bugs-job into lp:launchpad/db-devel

[Colin Watson]

Add ProcessAcceptedBugsJob table.

== Summary ==

Bug 745799: Some uploads can't be accepted within the timeout, principally because they have to close lots of bugs and dealing with structural subscriptions is very slow.

== Proposed fix ==

I describe my proposed fix in detail in https://bugs.launchpad.net/launchpad/+bug/745799/comments/20. We aren't likely to be able to solve the structural subscription problem any time soon, and uploads can close arbitrarily large numbers of bugs, so in order to get rid of the queue script in Launchpad (the only remaining purpose of which is to work around timeouts with processing some uploads) we need to move bug processing to run asynchronously.

This is the DB part of this change.

== Pre-implementation notes ==

http://irclogs.ubuntu.com/2012/08/04/%23launchpad-dev.html#t01:49

== LOC Rationale ==

+46, depending on how you want to count DB changes. Even with the associated code changes, this will still be dwarfed by removing the queue script (-1956).

[Benji York (benji)]

The code in this branch is perfect in every way.

review: Approve (code)

[Colin Watson (cjwatson)]

Sorry, I hadn't actually meant to ask for a code review but couldn't withdraw the request once I'd made it :-)

[Stuart Bishop (stub)]

This is fine.

It would be nice to drop the id column and just use job as the primary key, but I understand that we probably have existing code that relies on id and the table should be small in any case.

review: Approve (db)

=== modified file 'database/sampledata/current-dev.sql'
--- database/sampledata/current-dev.sql	2012-08-03 19:21:19 +0000
+++ database/sampledata/current-dev.sql	2012-08-16 15:24:31 +0000
@@ -762,9 +762,6 @@
 
 
 
-
-
-
 SET SESSION AUTHORIZATION DEFAULT;
 
 ALTER TABLE account DISABLE TRIGGER ALL;
@@ -3535,6 +3532,13 @@
 ALTER TABLE bugsubscriptionfilterimportance ENABLE TRIGGER ALL;
 
 
+ALTER TABLE bugsubscriptionfilterinformationtype DISABLE TRIGGER ALL;
+
+
+
+ALTER TABLE bugsubscriptionfilterinformationtype ENABLE TRIGGER ALL;
+
+
 ALTER TABLE bugsubscriptionfiltermute DISABLE TRIGGER ALL;
 
 
@@ -9013,6 +9017,13 @@
 ALTER TABLE potranslation ENABLE TRIGGER ALL;
 
 
+ALTER TABLE processacceptedbugsjob DISABLE TRIGGER ALL;
+
+
+
+ALTER TABLE processacceptedbugsjob ENABLE TRIGGER ALL;
+
+
 ALTER TABLE productjob DISABLE TRIGGER ALL;
 
 

=== modified file 'database/sampledata/current.sql'
--- database/sampledata/current.sql	2012-08-03 19:21:19 +0000
+++ database/sampledata/current.sql	2012-08-16 15:24:31 +0000
@@ -762,9 +762,6 @@
 
 
 
-
-
-
 SET SESSION AUTHORIZATION DEFAULT;
 
 ALTER TABLE account DISABLE TRIGGER ALL;
@@ -3469,6 +3466,13 @@
 ALTER TABLE bugsubscriptionfilterimportance ENABLE TRIGGER ALL;
 
 
+ALTER TABLE bugsubscriptionfilterinformationtype DISABLE TRIGGER ALL;
+
+
+
+ALTER TABLE bugsubscriptionfilterinformationtype ENABLE TRIGGER ALL;
+
+
 ALTER TABLE bugsubscriptionfiltermute DISABLE TRIGGER ALL;
 
 
@@ -8936,6 +8940,13 @@
 ALTER TABLE potranslation ENABLE TRIGGER ALL;
 
 
+ALTER TABLE processacceptedbugsjob DISABLE TRIGGER ALL;
+
+
+
+ALTER TABLE processacceptedbugsjob ENABLE TRIGGER ALL;
+
+
 ALTER TABLE productjob DISABLE TRIGGER ALL;
 
 

=== modified file 'database/schema/comments.sql'
--- database/schema/comments.sql	2012-07-31 20:35:05 +0000
+++ database/schema/comments.sql	2012-08-16 15:24:31 +0000
@@ -715,6 +715,14 @@
 COMMENT ON COLUMN PreviewDiff.target_revision_id IS 'The target branch revision_id used to generate this diff.';
 
 
+-- ProcessAcceptedBugsJob
+COMMENT ON TABLE ProcessAcceptedBugsJob IS 'Contains references to jobs for modifying bugs in response to accepting package uploads.';
+COMMENT ON COLUMN ProcessAcceptedBugsJob.job IS 'The Job related to this ProcessAcceptedBugsJob.';
+COMMENT ON COLUMN ProcessAcceptedBugsJob.distroseries IS 'The DistroSeries of the accepted upload.';
+COMMENT ON COLUMN ProcessAcceptedBugsJob.sourcepackagerelease IS 'The SourcePackageRelease of the accepted upload.';
+COMMENT ON COLUMN ProcessAcceptedBugsJob.json_data IS 'A JSON struct containing data for the job.';
+
+
 -- Product
 COMMENT ON TABLE Product IS 'Product: a DOAP Product. This table stores core information about an open source product. In Launchpad, anything that can be shipped as a tarball would be a product, and in some cases there might be products for things that never actually ship, depending on the project. For example, most projects will have a ''website'' product, because that allows you to file a Malone bug against the project website. Note that these are not actual product releases, which are stored in the ProductRelease table.';
 COMMENT ON COLUMN Product.owner IS 'The Product owner would typically be the person who created this product in Launchpad. But we will encourage the upstream maintainer of a product to become the owner in Launchpad. The Product owner can edit any aspect of the Product, as well as appointing people to specific roles with regard to the Product. Also, the owner can add a new ProductRelease and also edit Rosetta POTemplates associated with this product.';

=== added file 'database/schema/patch-2209-27-2.sql'
--- database/schema/patch-2209-27-2.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-27-2.sql	2012-08-16 15:24:31 +0000
@@ -0,0 +1,13 @@
+-- Copyright 2012 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 ProcessAcceptedBugsJob (
+    job integer PRIMARY KEY REFERENCES Job ON DELETE CASCADE UNIQUE NOT NULL,
+    distroseries integer REFERENCES DistroSeries NOT NULL,
+    sourcepackagerelease integer REFERENCES SourcePackageRelease NOT NULL,
+    json_data text
+);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 27, 2);

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2012-08-06 03:47:42 +0000
+++ database/schema/security.cfg	2012-08-16 15:24:31 +0000
@@ -266,6 +266,7 @@
 public.polloption                       = SELECT, INSERT, UPDATE, DELETE
 public.potexport                        = SELECT
 public.previewdiff                      = SELECT, INSERT, UPDATE, DELETE
+public.processacceptedbugsjob           = SELECT, INSERT, UPDATE, DELETE
 public.productjob                       = SELECT, INSERT, UPDATE, DELETE
 public.productrelease                   = SELECT, INSERT, UPDATE, DELETE
 public.productreleasefile               = SELECT, INSERT, DELETE
@@ -1521,6 +1522,7 @@
 [process_accepted]
 type=user
 groups=queued
+public.processacceptedbugsjob           = SELECT, INSERT
 
 [session]
 type=user