Merge lp:~gmb/launchpad/add-bugheatjob-table into lp:launchpad/db-devel

[Graham Binns]

Add a BugHeatJob table to the database.

[Graham Binns (gmb)]

This branch adds a DB patch to create a BugHeatJob table. Bug heat calculation jobs will use this table to work out which bug they need to update.

[Jonathan Lange (jml)]

Looks good to me. I think it's a Good Thing to follow the branch job pattern here: more code re-use potential.

Also, maybe this will make it easier to use jobs for other things in the bugs app.

review: Approve (db)

[Stuart Bishop (stub)]

The id column needs to be of type 'serial PRIMARY KEY', not 'integer PRIMARY KEY'.

Otherwise the DB patch is fine. patch-2207-28-0.sql.

I wouldn't mind rejecting this though, as we appear to be building our own messaging system instead of using a messaging system. Other job tables are for longer running tasks where we need to store state during the run - we might need them even if we switched from a poll based notification to a pull based using RabbitMQ or similar. The use cases for BugJob I can imagine are just using this table as a queue.

review: Approve (db)

[Graham Binns (gmb)]

On Tue, Jan 19, 2010 at 03:26:36AM -0000, Stuart Bishop wrote:
> Review: Approve db
> The id column needs to be of type 'serial PRIMARY KEY', not 'integer PRIMARY KEY'.
>
> Otherwise the DB patch is fine. patch-2207-28-0.sql.

Thanks.

> I wouldn't mind rejecting this though, as we appear to be building our
> own messaging system instead of using a messaging system. Other job
> tables are for longer running tasks where we need to store state
> during the run - we might need them even if we switched from a poll
> based notification to a pull based using RabbitMQ or similar. The use
> cases for BugJob I can imagine are just using this table as a queue.

I agree. However, time constraints for getting this working (it turns
out that we need it RSN for both bug heat and fixing checkwatches) mean
that we need to go ahead with the less perfect solution.

The sooner we can get to using RabbitMQ, the better.

=== modified symlink 'bzrplugins/builder'
=== target was u'../sourcecode/bzr-builder'
=== modified file 'database/schema/comments.sql'
--- database/schema/comments.sql	2010-01-13 05:55:29 +0000
+++ database/schema/comments.sql	2010-01-19 15:26:20 +0000
@@ -163,6 +163,12 @@
 COMMENT ON COLUMN BugBranch.whiteboard IS 'Additional information about the status of the bugfix in this branch.';
 COMMENT ON COLUMN BugBranch.registrant IS 'The person who linked the bug to the branch.';
 
+-- BugJob
+COMMENT ON TABLE BugJob IS 'Contains references to jobs to be run against Bugs.';
+COMMENT ON COLUMN BugJob.bug IS 'The bug on which the job is to be run.';
+COMMENT ON COLUMN BugJob.job_type IS 'The type of job (enumeration value). Allows us to query the database for a given subset of BugJobs.';
+COMMENT ON COLUMN BugJob.json_data IS 'A JSON struct containing data for the job.';
+
 -- BugNomination
 COMMENT ON TABLE BugNomination IS 'A bug nominated for fixing in a distroseries or productseries';
 COMMENT ON COLUMN BugNomination.bug IS 'The bug being nominated.';

=== added file 'database/schema/patch-2207-28-0.sql'
--- database/schema/patch-2207-28-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2207-28-0.sql	2010-01-19 15:26:20 +0000
@@ -0,0 +1,11 @@
+SET client_min_messages=ERROR;
+
+CREATE TABLE BugJob(
+    id serial NOT NULL PRIMARY KEY,
+    job integer NOT NULL REFERENCES Job(id),
+    bug integer NOT NULL REFERENCES Bug(id),
+    job_type integer NOT NULL,
+    json_data text
+);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 28, 0)