Review: Approve db
Diff comments: > diff --git a/database/schema/patch-2210-26-3.sql > b/database/schema/patch-2210-26-3.sql > new file mode 100644 > index 0000000..54c78c7 > --- /dev/null > +++ b/database/schema/patch-2210-26-3.sql > @@ -0,0 +1,122 @@ > +-- 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; > + > +-- OCIRecipe privacy model is based only on ownership, similarly to Archives. > +ALTER TABLE OCIRecipe > + ADD COLUMN information_type integer, > + ADD COLUMN access_policy integer, > + ADD COLUMN access_grants integer[]; > + > +COMMENT ON COLUMN OCIRecipe.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 OCIRecipeSubscription ( > + id serial PRIMARY KEY, > + recipe integer NOT NULL REFERENCES OCIRecipe(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 OCIRecipeSubscription IS 'Person subscription for OCI > recipe.'; > +COMMENT ON COLUMN OCIRecipeSubscription.person IS > + 'The person who subscribed to the OCI recipe.'; > +COMMENT ON COLUMN OCIRecipeSubscription.recipe IS > + 'The OCI recipe to which the person subscribed.'; > +COMMENT ON COLUMN OCIRecipeSubscription.date_created IS > + 'When the subscription was created.'; > +COMMENT ON COLUMN OCIRecipeSubscription.subscribed_by IS > + 'The person performing the action of subscribing someone to the OCI > recipe.'; > + > +CREATE UNIQUE INDEX ocirecipesubscription__person_recipe__key s/person_recipe/recipe__person/ > + ON OCIRecipeSubscription(recipe, person); > + > +CREATE INDEX ocirecipesubscription__person__idx > + ON OCIRecipeSubscription(person); > + > +CREATE INDEX ocirecipesubscription__subscribed_by__idx > + ON OCIRecipeSubscription(subscribed_by); > + > +ALTER TABLE AccessArtifact > + ADD COLUMN ocirecipe integer REFERENCES OCIRecipe; > + > + > +ALTER TABLE AccessArtifact DROP CONSTRAINT has_artifact; > +ALTER TABLE AccessArtifact > + ADD CONSTRAINT has_artifact CHECK ( > + (null_count(ARRAY[bug, branch, gitrepository, snap, specification, > ocirecipe]) = 5)) NOT VALID; > + > + > +CREATE OR REPLACE FUNCTION ocirecipe_denorm_access(ocirecipe_id integer) > + RETURNS void LANGUAGE plpgsql AS > +$$ > +DECLARE > + info_type integer; > +BEGIN > + SELECT > + -- information type: 1 = public > + COALESCE(ocirecipe.information_type, 1) > + INTO info_type > + FROM ocirecipe WHERE id = ocirecipe_id; > + > + UPDATE OCIRecipe > + SET access_policy = policies[1], access_grants = grants > + FROM > + build_access_cache( > + (SELECT id FROM accessartifact WHERE ocirecipe = > ocirecipe_id), > + info_type) > + AS (policies integer[], grants integer[]) > + WHERE id = ocirecipe_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; > + IF artifact_row.ocirecipe IS NOT NULL THEN > + PERFORM ocirecipe_denorm_access(artifact_row.ocirecipe); > + 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, specifications and ocirecipe.'; > + > +-- A trigger to handle ocirecipe.information_type changes. > +CREATE OR REPLACE FUNCTION ocirecipe_maintain_access_cache_trig() RETURNS > trigger > + LANGUAGE plpgsql AS $$ > +BEGIN > + PERFORM ocirecipe_denorm_access(NEW.id); > + RETURN NULL; > +END; > +$$; > + > +CREATE TRIGGER ocirecipe_maintain_access_cache > + AFTER INSERT OR UPDATE OF information_type ON OCIRecipe > + FOR EACH ROW EXECUTE PROCEDURE ocirecipe_maintain_access_cache_trig(); > + > +INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 26, 3); -- https://code.launchpad.net/~pappacena/launchpad/+git/launchpad/+merge/397167 Your team Launchpad code reviewers is subscribed to branch ~pappacena/launchpad:ocirecipe-private-dbpatch. _______________________________________________ Mailing list: https://launchpad.net/~launchpad-reviewers Post to : [email protected] Unsubscribe : https://launchpad.net/~launchpad-reviewers More help : https://help.launchpad.net/ListHelp
