Merge ~pappacena/launchpad:snap-pillar-db into launchpad:db-devel

[Thiago F. Pappacena]

Adding Snap.project for private Snaps' optional pillar

Related code changes here: https://code.launchpad.net/~pappacena/launchpad/+git/launchpad/+merge/397458.
Some columns created here have index creation and CHECK constraint validation in this other MP: https://code.launchpad.net/~pappacena/launchpad/+git/launchpad/+merge/398361

[Colin Watson (cjwatson)]

I'm OK with this, but I wonder if it's worth folding the necessary AccessArtifact-related changes into this patch as well? See database/schema/archive/patch-2209-61-0.sql for an example from the last time we extended this sort of thing.

Adding William for a second DB review.

review: Approve (db)

f2abd3c... by Thiago F. Pappacena on 2021-02-04

Adding access_policy and access_grants caches to Snap table

[Thiago F. Pappacena (pappacena)]

I've pushed the changes to AccessArtifact similar to those ones used for GitRepository, with a couple of assumptions:

- Assuming information_type 1 (PUBLIC) when snap.private is false; and 5 (PROPRIETARY) when it's true
- Updating Snap.access_policy and Snap.access_grants when either "private" or "product" columns changes

9c49e1b... by Thiago F. Pappacena on 2021-02-09

Adding SnapSubscription model

08fb5e5... by Thiago F. Pappacena on 2021-02-12

Adding Snap.information_type column

[Thiago F. Pappacena (pappacena)]

Pushed the requested changes

0510967... by Thiago F. Pappacena on 2021-02-12

Fixing ambiguity in snap_denorm_access

[Colin Watson (cjwatson)]

review: Approve (db)

[William Grant (wgrant)]

Thanks, just a couple of small things.

review: Approve (db)

bcdb89b... by Thiago F. Pappacena on 2021-02-19

Minor adjustments, and moving large index/constraint creation to another patch

a44145f... by Thiago F. Pappacena on 2021-02-19

Skipping personmerge sanity checks on SnapSubscription (while we dont have this entity in place)

[Thiago F. Pappacena (pappacena)]

I think all comments were addressed here.

I'll open another MP with index creation and CHECK constraints validations on existing tables, so we run it separately (and with CONCURRENT on index creation).

[William Grant (wgrant)]

review: Approve (db)

diff --git a/database/schema/patch-2210-26-1.sql b/database/schema/patch-2210-26-1.sql
 new file mode 100644
index 0000000..90fdbf3
--- /dev/null
+++ b/database/schema/patch-2210-26-1.sql
@@ -0,0 +1,130 @@
+-- Copyright 2021 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+ALTER TABLE Snap
+    ADD COLUMN information_type integer,
+    ADD COLUMN project integer REFERENCES product,
+    ADD COLUMN access_policy integer,
+    ADD COLUMN access_grants integer[];
+
+COMMENT ON COLUMN Snap.private IS
+    '(DEPRECATED; use Snap.information_type) Whether or not this snap is private.';
+COMMENT ON COLUMN Snap.project IS
+    'The project which is the pillar for this Snap';
+COMMENT ON COLUMN Snap.information_type IS
+    'Enum describing what type of information is stored, such as type of private or security related data, and used to determine to apply an access policy.';
+
+CREATE TABLE SnapSubscription (
+    id serial PRIMARY KEY,
+    snap integer NOT NULL REFERENCES Snap(id),
+    person integer NOT NULL REFERENCES Person(id),
+    date_created timestamp without time zone DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') NOT NULL,
+    subscribed_by integer NOT NULL REFERENCES Person(id)
+);
+
+COMMENT ON TABLE SnapSubscription IS 'Person subscription for Snap recipes.';
+COMMENT ON COLUMN SnapSubscription.person IS
+    'The person who subscribed to the Snap.';
+COMMENT ON COLUMN SnapSubscription.snap IS
+    'The Snap recipe to which the person subscribed.';
+COMMENT ON COLUMN SnapSubscription.date_created IS
+    'When the subscription was created.';
+COMMENT ON COLUMN SnapSubscription.subscribed_by IS
+    'The person performing the action of subscribing someone to the Snap.';
+
+
+CREATE UNIQUE INDEX snapsubscription__person_snap__key
+    ON SnapSubscription(snap, person);
+
+CREATE INDEX snapsubscription__person__idx
+    ON SnapSubscription(person);
+
+CREATE INDEX snapsubscription__subscribed_by__idx
+    ON SnapSubscription(subscribed_by);
+
+ALTER TABLE AccessArtifact
+    ADD COLUMN snap integer REFERENCES snap;
+
+
+ALTER TABLE AccessArtifact DROP CONSTRAINT has_artifact;
+ALTER TABLE AccessArtifact
+    ADD CONSTRAINT has_artifact CHECK (
+    (null_count(ARRAY[bug, branch, gitrepository, snap, specification]) = 4)) NOT VALID;
+
+
+CREATE OR REPLACE FUNCTION snap_denorm_access(snap_id integer)
+    RETURNS void LANGUAGE plpgsql AS
+$$
+DECLARE
+    info_type integer;
+BEGIN
+    -- XXX pappacena 2021-02-12: Once we finish filling "information_type" and
+    -- deprecate the usage of "public" column at code level, we will be able to
+    -- drop the "private" column usage here.
+    SELECT
+        COALESCE(
+            snap.information_type,
+            -- information type: 1 = public; 5 = proprietary
+            CASE WHEN snap.private THEN 5 ELSE 1 END
+        )
+    INTO info_type
+    FROM snap WHERE id = snap_id;
+
+    UPDATE Snap
+        SET access_policy = policies[1], access_grants = grants
+        FROM
+            build_access_cache(
+                (SELECT id FROM accessartifact WHERE snap = snap_id),
+                info_type)
+            AS (policies integer[], grants integer[])
+        WHERE id = snap_id;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION accessartifact_denorm_to_artifacts(artifact_id integer)
+    RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    artifact_row accessartifact%ROWTYPE;
+BEGIN
+    SELECT * INTO artifact_row FROM accessartifact WHERE id = artifact_id;
+    IF artifact_row.bug IS NOT NULL THEN
+        PERFORM bug_flatten_access(artifact_row.bug);
+    END IF;
+    IF artifact_row.branch IS NOT NULL THEN
+        PERFORM branch_denorm_access(artifact_row.branch);
+    END IF;
+    IF artifact_row.gitrepository IS NOT NULL THEN
+        PERFORM gitrepository_denorm_access(artifact_row.gitrepository);
+    END IF;
+    IF artifact_row.snap IS NOT NULL THEN
+        PERFORM snap_denorm_access(artifact_row.snap);
+    END IF;
+    IF artifact_row.specification IS NOT NULL THEN
+        PERFORM specification_denorm_access(artifact_row.specification);
+    END IF;
+    RETURN;
+END;
+$$;
+
+COMMENT ON FUNCTION accessartifact_denorm_to_artifacts(artifact_id integer) IS
+    'Denormalize the policy access and artifact grants to bugs, branches, git repositories, snaps, and specifications.';
+
+-- A trigger to handle snap.{private,information_type,project} changes.
+CREATE OR REPLACE FUNCTION snap_maintain_access_cache_trig() RETURNS trigger
+    LANGUAGE plpgsql AS $$
+BEGIN
+    PERFORM snap_denorm_access(NEW.id);
+    RETURN NULL;
+END;
+$$;
+
+CREATE TRIGGER snap_maintain_access_cache
+    AFTER INSERT OR UPDATE OF private, information_type, project ON Snap
+    FOR EACH ROW EXECUTE PROCEDURE snap_maintain_access_cache_trig();
+
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 26, 1);
diff --git a/database/schema/security.cfg b/database/schema/security.cfg
index bf4b81c..e343a5f 100644
--- a/database/schema/security.cfg
+++ b/database/schema/security.cfg
@@ -302,6 +302,7 @@ public.snapbuild                        = SELECT, INSERT, UPDATE, DELETE
 public.snapbuildjob                     = SELECT, INSERT, UPDATE, DELETE
 public.snapfile                         = SELECT, INSERT, UPDATE, DELETE
 public.snapjob                          = SELECT, INSERT, UPDATE, DELETE
+public.snapsubscription                 = SELECT, INSERT, UPDATE, DELETE
 public.snappydistroseries               = SELECT, INSERT, UPDATE, DELETE
 public.snappyseries                     = SELECT, INSERT, UPDATE, DELETE
 public.sourcepackageformatselection     = SELECT
@@ -2246,6 +2247,7 @@ type=user
 
 [person-merge-job]
 groups=script
+public.accesspolicyartifact             = SELECT
 public.accessartifactgrant              = SELECT, UPDATE, DELETE
 public.accesspolicy                     = SELECT, UPDATE, DELETE
 public.accesspolicygrant                = SELECT, UPDATE, DELETE
@@ -2363,6 +2365,7 @@ public.signedcodeofconduct              = SELECT, UPDATE
 public.snap                             = SELECT, UPDATE
 public.snapbase                         = SELECT, UPDATE
 public.snapbuild                        = SELECT, UPDATE
+public.snapsubscription                 = SELECT, UPDATE, DELETE
 public.snappyseries                     = SELECT, UPDATE
 public.sourcepackagename                = SELECT
 public.sourcepackagepublishinghistory   = SELECT, UPDATE
diff --git a/lib/lp/registry/personmerge.py b/lib/lp/registry/personmerge.py
index 7873a61..50e0f91 100644
--- a/lib/lp/registry/personmerge.py
+++ b/lib/lp/registry/personmerge.py
@@ -917,6 +917,10 @@ def merge_people(from_person, to_person, reviewer, delete=False):
     _mergeSnap(cur, from_person, to_person)
     skip.append(('snap', 'owner'))
 
+    # XXX pappacena 2021-02-18: add tests for this once we have
+    # SnapSubscription model in place.
+    skip.append(('snapsubscription', 'person'))
+
     _mergeOCIRecipe(cur, from_person, to_person)
     skip.append(('ocirecipe', 'owner'))