Merge ~cjwatson/launchpad:db-oci-push-rule into launchpad:db-devel

[Colin Watson]

Add OCIRegistryCredentials and OCIPushRule tables

[Tom Wardill (twom)]

review: Approve

[William Grant (wgrant)]

It might be worth noting that the "make use of" restriction is just on creating an OCIPushRule.

I'll take your word for it that "image name" is the appropriate term.

review: Approve (db)

500cd21... by Colin Watson on 2020-03-20

Clarify "make use of" for OCIRegistryCredentials

[Colin Watson (cjwatson)]

<cjwatson> tomwardill: do you have any opinion on William's half-question about image_name in https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/380948 ?
<tomwardill> image_name seems an appropriate variable name
<cjwatson> OK, good
<tomwardill> I feel like it's something that would be understood by an end user of this
<SpecialK|Canon> +1

diff --git a/database/schema/patch-2210-08-6.sql b/database/schema/patch-2210-08-6.sql
new file mode 100644
index 0000000..62c5d55
--- /dev/null
+++ b/database/schema/patch-2210-08-6.sql
@@ -0,0 +1,38 @@
+-- Copyright 2019 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 OCIRegistryCredentials (
+    id serial PRIMARY KEY,
+    owner integer NOT NULL REFERENCES Person,
+    url text NOT NULL,
+    credentials jsonb NOT NULL
+);
+
+CREATE INDEX ociregistrycredentials__owner__idx
+    ON OCIRegistryCredentials (owner);
+
+COMMENT ON TABLE OCIRegistryCredentials IS 'Credentials for pushing to an OCI registry.';
+COMMENT ON COLUMN OCIRegistryCredentials.owner IS 'The owner of these credentials.  Only the owner is entitled to create push rules using them.';
+COMMENT ON COLUMN OCIRegistryCredentials.url IS 'The registry URL.';
+COMMENT ON COLUMN OCIRegistryCredentials.credentials IS 'Encrypted credentials for pushing to the registry.';
+
+CREATE TABLE OCIPushRule (
+    id serial PRIMARY KEY,
+    recipe integer NOT NULL REFERENCES OCIRecipe,
+    registry_credentials integer NOT NULL REFERENCES OCIRegistryCredentials,
+    image_name text NOT NULL
+);
+
+CREATE UNIQUE INDEX ocipushrule__recipe__registry_credentials__image_name__key
+    ON OCIPushRule (recipe, registry_credentials, image_name);
+CREATE INDEX ocipushrule__registry_credentials__idx
+    ON OCIPushRule (registry_credentials);
+
+COMMENT ON TABLE OCIPushRule IS 'A rule for pushing builds of an OCI recipe to a registry.';
+COMMENT ON COLUMN OCIPushRule.recipe IS 'The recipe for which the rule is defined.';
+COMMENT ON COLUMN OCIPushRule.registry_credentials IS 'The registry credentials to use.';
+COMMENT ON COLUMN OCIPushRule.image_name IS 'The intended name of the image on the registry.';
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 08, 6);