Merge lp:~james-w/launchpad/archive-job-db into lp:launchpad/db-devel

[James Westby]

Add an ArchiveJob table.

Hi,

This adds an ArchiveJob, like BranchJob and BugJob.

The first use is for copy archive jobs, that currently aren't jobs
but should be. I expect there will be more uses in the future.

Thanks,

James

[James Westby (james-w)]

You can see the model classes at

  https://code.edge.launchpad.net/~james-w/launchpad/copy-archive-job/+merge/28439

with some more description of the aim of this job type.

Thanks,

James

[Stuart Bishop (stub)]

Generally fine.

patch-2207-64-0.sql

I think the job foreign key constraint should be ON DELETE CASCADE to make job garbage collection a little easier:

job integer NOT NULL CONSTRAINT archivejob__job__fk REFERENCES job ON DELETE CASCADE

(the archive foriegn key constraint should not be).

[Stuart Bishop (stub)]

review: Approve (db)

[Stuart Bishop (stub)]

Oh... please fix the security.cfg declarations. The [write] section is deprecated per the comments. Please add explicit permissions to users.

[James Westby (james-w)]

On Fri, 25 Jun 2010 07:41:28 -0000, Stuart Bishop <email address hidden> wrote:
> Oh... please fix the security.cfg declarations. The [write] section is deprecated per the comments. Please add explicit permissions to users.

I was just cargo-culting, so I'm not surprised it is wrong.

I guess the other permission is ok?

I don't know what explicit permissions other than that would be needed.

Currently these jobs would be added by a script run by an admin on
request, and then we will add a cronscript to process them. If you can
tell me what users/permission might be needed for that then I can add
them now, otherwise I will just leave the default in and work with the
Soyuz team on making other changes needed once we have something to test
on dogfood.

Thanks,

James

[Stuart Bishop (stub)]

No problems - the permissions can be added when the cronscript or whatever processes the jobs is written.

[James Westby (james-w)]

Hi Stuart,

Could you give this another look over as I have made the changes you suggested?

Thanks,

James

[Stuart Bishop (stub)]

Looks good

review: Approve (db)

=== modified file 'database/sampledata/current-dev.sql'
--- database/sampledata/current-dev.sql	2010-07-28 14:36:32 +0000
+++ database/sampledata/current-dev.sql	2010-07-28 17:45:50 +0000
@@ -791,6 +791,9 @@
 
 
 
+
+
+
 SET SESSION AUTHORIZATION DEFAULT;
 
 ALTER TABLE account DISABLE TRIGGER ALL;
@@ -2207,6 +2210,13 @@
 ALTER TABLE archivedependency ENABLE TRIGGER ALL;
 
 
+ALTER TABLE archivejob DISABLE TRIGGER ALL;
+
+
+
+ALTER TABLE archivejob ENABLE TRIGGER ALL;
+
+
 ALTER TABLE packagesetgroup DISABLE TRIGGER ALL;
 
 

=== modified file 'database/sampledata/current.sql'
--- database/sampledata/current.sql	2010-07-28 14:36:32 +0000
+++ database/sampledata/current.sql	2010-07-28 17:45:50 +0000
@@ -791,6 +791,9 @@
 
 
 
+
+
+
 SET SESSION AUTHORIZATION DEFAULT;
 
 ALTER TABLE account DISABLE TRIGGER ALL;
@@ -2164,6 +2167,13 @@
 ALTER TABLE archivedependency ENABLE TRIGGER ALL;
 
 
+ALTER TABLE archivejob DISABLE TRIGGER ALL;
+
+
+
+ALTER TABLE archivejob ENABLE TRIGGER ALL;
+
+
 ALTER TABLE packagesetgroup DISABLE TRIGGER ALL;
 
 

=== modified file 'database/schema/comments.sql'
--- database/schema/comments.sql	2010-07-23 19:47:16 +0000
+++ database/schema/comments.sql	2010-07-28 17:45:50 +0000
@@ -29,6 +29,14 @@
 COMMENT ON COLUMN ApportJob.job_type IS 'The type of job (enumeration value). Allows us to query the database for a given subset of ApportJobs.';
 COMMENT ON COLUMN ApportJob.json_data IS 'A JSON struct containing data for the job.';
 
+-- ArchiveJob
+
+COMMENT ON TABLE ArchiveJob is 'Contains references to jobs to be run against Archives.';
+COMMENT ON COLUMN ArchiveJob.archive IS 'The archive on which the job is to be run.';
+COMMENT ON COLUMN ArchiveJob.job_type IS 'The type of job (enumeration value). Allows us to query the database for a given subset of ArchiveJobs.';
+COMMENT ON COLUMN ArchiveJob.json_data IS 'A JSON struct containing data for the job.';
+
+
 -- Branch
 COMMENT ON TABLE Branch IS 'Bzr branch';
 COMMENT ON COLUMN Branch.registrant IS 'The user that registered the branch.';

=== added file 'database/schema/patch-2207-64-0.sql'
--- database/schema/patch-2207-64-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2207-64-0.sql	2010-07-28 17:45:50 +0000
@@ -0,0 +1,27 @@
+-- Copyright 2009 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+-- The schema patch required for adding archive jobs, the first being
+-- creation of copy archives.
+
+-- The `ArchiveJob` table captures the data required for an archive job.
+
+CREATE TABLE ArchiveJob (
+    id serial PRIMARY KEY,
+    -- FK to the `Job` record with the "generic" data about this archive
+    -- job.
+    job integer NOT NULL CONSTRAINT archivejob__job__fk REFERENCES job ON DELETE CASCADE,
+    -- FK to the associated `Archive` record.
+    archive integer NOT NULL CONSTRAINT archivejob__archive__fk REFERENCES archive,
+    -- The particular type of archive job
+    job_type integer NOT NULL,
+    -- JSON data for use by the job
+    json_data text
+);
+
+ALTER TABLE ArchiveJob ADD CONSTRAINT archivejob__job__key UNIQUE (job);
+CREATE INDEX archivejob__archive__job_type__idx ON ArchiveJob(archive, job_type);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 64, 0);

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2010-07-28 14:36:32 +0000
+++ database/schema/security.cfg	2010-07-28 17:45:50 +0000
@@ -82,6 +82,7 @@
 public.apportjob                        = SELECT, INSERT, UPDATE, DELETE
 public.archive                          = SELECT, INSERT, UPDATE
 public.archiveauthtoken                 = SELECT, INSERT, UPDATE
+public.archivejob                       = SELECT, INSERT, UPDATE, DELETE
 public.archivesubscriber                = SELECT, INSERT, UPDATE
 public.archivearch                      = SELECT, INSERT, UPDATE, DELETE
 public.archivedependency                = SELECT, INSERT, DELETE