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

[Thiago F. Pappacena]

Snap privacy indexes and CHECK constraint validation

Unmerged commits

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

Snap privacy indexes and CHECK constraint validation

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

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

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

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

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

Fixing ambiguity in snap_denorm_access

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

Adding Snap.information_type column

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

Adding SnapSubscription model

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

Adding access_policy and access_grants caches to Snap table

ea33cf9... by Thiago F. Pappacena on 2021-02-03

Adding Snap.project for private Snaps' optional pillars

diff --git a/database/schema/patch-2210-26-1.sql b/database/schema/patch-2210-26-1.sql
index 5511d91..90fdbf3 100644
--- a/database/schema/patch-2210-26-1.sql
+++ b/database/schema/patch-2210-26-1.sql
@@ -18,8 +18,8 @@ COMMENT ON COLUMN Snap.information_type IS
 
 CREATE TABLE SnapSubscription (
     id serial PRIMARY KEY,
-    person integer NOT NULL REFERENCES Person(id),
     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)
 );
@@ -41,17 +41,17 @@ CREATE UNIQUE INDEX snapsubscription__person_snap__key
 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;
 
-CREATE UNIQUE INDEX accessartifact__snap__key
-    ON AccessArtifact(snap) WHERE snap IS NOT NULL;
-
 
 ALTER TABLE AccessArtifact DROP CONSTRAINT has_artifact;
 ALTER TABLE AccessArtifact
     ADD CONSTRAINT has_artifact CHECK (
-    (null_count(ARRAY[bug, branch, gitrepository, snap, specification]) = 4));
+    (null_count(ARRAY[bug, branch, gitrepository, snap, specification]) = 4)) NOT VALID;
 
 
 CREATE OR REPLACE FUNCTION snap_denorm_access(snap_id integer)
@@ -60,17 +60,15 @@ $$
 DECLARE
     info_type integer;
 BEGIN
-    -- XXX pappacena 2021-002-12: Once we finish filling "information_type" and
+    -- 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
-        CASE snap.information_type
-            WHEN NULL THEN
-                -- information type: 1 = public; 5 = proprietary
-                CASE WHEN snap.private THEN 5 ELSE 1 END
-            ELSE
-                snap.information_type
-            END
+        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;
 
diff --git a/database/schema/patch-2210-26-2.sql b/database/schema/patch-2210-26-2.sql
 new file mode 100644
index 0000000..ab6e0a3
--- /dev/null
+++ b/database/schema/patch-2210-26-2.sql
@@ -0,0 +1,14 @@
+-- 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;
+
+CREATE INDEX snap__project__idx ON Snap(project) WHERE project IS NOT NULL;
+
+CREATE UNIQUE INDEX accessartifact__snap__key
+    ON AccessArtifact(snap) WHERE snap IS NOT NULL;
+
+ALTER TABLE AccessArtifact VALIDATE CONSTRAINT has_artifact;
+
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 26, 2);
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'))