Merge ~cjwatson/launchpad:db-oci-recipe-build-job into launchpad:db-devel

[Colin Watson]

Add OCIRecipeBuildJob table

[Tom Wardill (twom)]

review: Approve (db)

[Kristian Glass (doismellburning)]

review: Approve

36cb183... by Colin Watson on 2020-03-24

Add OCIRecipeBuildJob (job, job_type) index

This will be useful for job deletion in future.

[William Grant (wgrant)]

review: Approve (db)

diff --git a/database/schema/patch-2210-08-7.sql b/database/schema/patch-2210-08-7.sql
new file mode 100644
index 0000000..47ea635
--- /dev/null
+++ b/database/schema/patch-2210-08-7.sql
@@ -0,0 +1,22 @@
+-- Copyright 2020 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+CREATE TABLE OCIRecipeBuildJob (
+    job integer PRIMARY KEY REFERENCES Job ON DELETE CASCADE NOT NULL,
+    build integer REFERENCES ocirecipebuild NOT NULL,
+    job_type integer NOT NULL,
+    json_data jsonb NOT NULL
+);
+
+COMMENT ON TABLE OCIRecipeBuildJob IS 'Contains references to jobs that are executed for a build of an OCI recipe.';
+COMMENT ON COLUMN OCIRecipeBuildJob.job IS 'A reference to a Job row that has all the common job details.';
+COMMENT ON COLUMN OCIRecipeBuildJob.build IS 'The OCI recipe build that this job is for.';
+COMMENT ON COLUMN OCIRecipeBuildJob.job_type IS 'The type of a job, such as a registry push.';
+COMMENT ON COLUMN OCIRecipeBuildJob.json_data IS 'Data that is specific to a particular job type.';
+
+CREATE INDEX ocirecipebuildjob__build__job_type__job__idx
+    ON OCIRecipeBuildJob (build, job_type, job);
+CREATE INDEX ocirecipebuildjob__job__job_type__idx
+    ON OCIRecipeBuildJob (job, job_type);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 08, 7);
diff --git a/database/schema/security.cfg b/database/schema/security.cfg
index 91b8407..f5cca8d 100644
--- a/database/schema/security.cfg
+++ b/database/schema/security.cfg
@@ -249,6 +249,7 @@ public.ocipushrule                      = SELECT, INSERT, UPDATE, DELETE
 public.ocirecipe                        = SELECT, INSERT, UPDATE, DELETE
 public.ocirecipearch                    = SELECT, INSERT, DELETE
 public.ocirecipebuild                   = SELECT, INSERT, UPDATE, DELETE
+public.ocirecipebuildjob                = SELECT, INSERT, UPDATE, DELETE
 public.ociregistrycredentials           = SELECT, INSERT, UPDATE, DELETE
 public.officialbugtag                   = SELECT, INSERT, UPDATE, DELETE
 public.openidconsumerassociation        = SELECT, INSERT, UPDATE, DELETE
@@ -1000,6 +1001,7 @@ public.ociproject                             = SELECT
 public.ociprojectname                         = SELECT
 public.ocirecipe                              = SELECT
 public.ocirecipebuild                         = SELECT, UPDATE
+public.ocirecipebuildjob                      = SELECT, INSERT
 public.openididentifier                       = SELECT
 public.packageset                             = SELECT
 public.packagesetgroup                        = SELECT
@@ -1433,6 +1435,9 @@ public.milestonetag                     = SELECT
 public.ociproject                       = SELECT
 public.ociprojectname                   = SELECT
 public.ociprojectseries                 = SELECT
+public.ocirecipe                        = SELECT, UPDATE
+public.ocirecipebuild                   = SELECT, UPDATE
+public.ocirecipebuildjob                = SELECT, INSERT, UPDATE
 public.openididentifier                 = SELECT
 public.packagecopyjob                   = SELECT, INSERT
 public.packagediff                      = SELECT, INSERT, UPDATE, DELETE