Thiago F. Pappacena has proposed merging
~pappacena/launchpad:snap-pillar-db-indexes into launchpad:db-devel with
~pappacena/launchpad:snap-pillar-db as a prerequisite.
Commit message:
Snap privacy indexes and CHECK constraint validation
Requested reviews:
Launchpad code reviewers (launchpad-reviewers)
For more details, see:
https://code.launchpad.net/~pappacena/launchpad/+git/launchpad/+merge/398359
--
Your team Launchpad code reviewers is requested to review the proposed merge of
~pappacena/launchpad:snap-pillar-db-indexes into launchpad:db-devel.
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 000..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 =
