Merge lp:~edwin-grubbs/launchpad/bug-615654-registry-jobqueue-schema into lp:launchpad/db-devel

[Edwin Grubbs]

This branch adds the PersonTransferJob and QuestionJob tables.

The PersonTransferJob table will be used immediately to reduce or eliminate timeouts when adding lots of members to a team. In the future, the table will also be used for jobs where two person objects are merged.

The QuestionJob will be used to queue notifications about changes to the question object.

Should these jobs use new database users and have their own cronjobs? It seems like it would be much simpler to have a single cronjob run all the jobs.

[Robert Collins (lifeless)]

Quick thoughts:
 - for question emails, perhaps a single 'emailjob' would be better, containing in it any email we want to send rather than question specific emails.
 - PTJ seems sensible to me, though I thought we'd eliminated timeouts there already; could you link a recent oops for me to peruse (and its bug)? thanks.
 - I don't see that we need new db users and cron jobs for this, though we do need one cronjob per queue AIUI today.

[Stuart Bishop (stub)]

I'm happy with the names. These relations have so far often been named by the tables they are joining (BranchJob, ArchiveJob) as with the job_type column, they can be used for several different types of task. Although we are not consistent - ApportJob should be BlobJob for instance.

We want a unique database user per script. If you have one script processing multiple types of jobs, that is one database user.

We will need some indexes in case these tables grow:

CREATE INDEX PersonTransferJob__minor_person__idx ON PersonTransferJob(minor_person);
CREATE INDEX PersonTransferJob__major_person__idx ON PersonTransferJob(major_person);
CREATE INDEX QuestionJob__question__idx ON QuestionJob(question);

review: Approve (db)

[Edwin Grubbs (edwin-grubbs)]

Thanks for the review. Is a new user created in the db just by adding an entry to security.cfg, or do I need to do something else?

[Stuart Bishop (stub)]

Just the entry in security.cfg - that takes care of everything at your end.

=== modified file 'database/schema/comments.sql'
--- database/schema/comments.sql	2010-09-16 01:27:57 +0000
+++ database/schema/comments.sql	2010-09-17 03:13:45 +0000
@@ -1272,6 +1272,22 @@
 COMMENT ON COLUMN PersonNotification.subject IS 'The subject of the mail to be sent.';
 COMMENT ON COLUMN PersonNotification.date_emailed IS 'When this notification was emailed to the relevant people.';
 
+-- PersonTransferJob
+
+COMMENT ON TABLE PersonTransferJob IS 'Contains references to jobs for adding team members or merging person entries.';
+COMMENT ON COLUMN PersonTransferJob.job IS 'A reference to a row in the Job table that has all the common job details.';
+COMMENT ON COLUMN PersonTransferJob.job_type IS 'The type of job, like add-member notification or merge persons.';
+COMMENT ON COLUMN PersonTransferJob.json_data IS 'Data that is specific to the type of job, normally stores text to append to email notifications.';
+COMMENT ON COLUMN PersonTransferJob.minor_person IS 'The person that is being added is a new member or being merged into another person.';
+COMMENT ON COLUMN PersonTransferJob.major_person IS 'The team receiving a new member or the person that another person is merged into.';
+
+-- QuestionJob
+
+COMMENT ON TABLE QuestionJob IS 'Contains references to jobs regarding questions.';
+COMMENT ON COLUMN QuestionJob.job IS 'A reference to a row in the Job table that has all the common job details.';
+COMMENT ON COLUMN QuestionJob.job_type IS 'The type of job, such as new-answer-notification.';
+COMMENT ON COLUMN QuestionJob.json_data IS 'Data that is specific to the type of job, normally stores text to append to email notifications.';
+COMMENT ON COLUMN QuestionJob.question IS 'The newly added question message.';
 
 -- Bounty
 COMMENT ON TABLE Bounty IS 'A set of bounties for work to be done by the open source community. These bounties will initially be offered only by Canonical, but later we will create the ability for people to offer the bounties themselves, using us as a clearing house.';

=== added file 'database/schema/patch-2208-99-0.sql'
--- database/schema/patch-2208-99-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2208-99-0.sql	2010-09-17 03:13:45 +0000
@@ -0,0 +1,27 @@
+SET client_min_messages=ERROR;
+
+/* PersonTransferJob can handle jobs adding a member to a team
+ * or merging to person objects.
+ */
+CREATE TABLE PersonTransferJob (
+    id           SERIAL PRIMARY KEY,
+    job          INTEGER NOT NULL UNIQUE REFERENCES Job(id),
+    job_type     INTEGER NOT NULL,
+    minor_person INTEGER NOT NULL REFERENCES Person(id),
+    major_person INTEGER NOT NULL REFERENCES Person(id),
+    json_data    text
+);
+
+CREATE TABLE QuestionJob (
+    id        SERIAL PRIMARY KEY,
+    job       INTEGER NOT NULL UNIQUE REFERENCES Job(id),
+    job_type  INTEGER NOT NULL,
+    question  INTEGER NOT NULL REFERENCES Question(id),
+    json_data text
+);
+
+CREATE INDEX PersonTransferJob__minor_person__idx ON PersonTransferJob(minor_person);
+CREATE INDEX PersonTransferJob__major_person__idx ON PersonTransferJob(major_person);
+CREATE INDEX QuestionJob__question__idx ON QuestionJob(question);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 99, 0);

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2010-09-10 12:59:49 +0000
+++ database/schema/security.cfg	2010-09-17 03:13:45 +0000
@@ -214,6 +214,7 @@
 public.packaging                        = SELECT, INSERT, UPDATE, DELETE
 public.personlanguage                   = SELECT, INSERT, UPDATE, DELETE
 public.personlocation                   = SELECT, INSERT, UPDATE, DELETE
+public.persontransferjob                = SELECT, INSERT, UPDATE, DELETE
 public.personnotification               = SELECT, INSERT, UPDATE, DELETE
 public.pillarname                       = SELECT, INSERT, DELETE
 public.poexportrequest                  = SELECT, INSERT, UPDATE, DELETE
@@ -229,6 +230,7 @@
 public.project                          = SELECT
 public.projectbounty                    = SELECT, INSERT, UPDATE
 public.questionbug                      = SELECT, INSERT, DELETE
+public.questionjob                      = SELECT, INSERT, UPDATE, DELETE
 public.questionmessage                  = SELECT, INSERT
 public.questionreopening                = SELECT, INSERT, UPDATE
 public.question                         = SELECT, INSERT, UPDATE