Guruprasad has proposed merging ~lgp171188/launchpad:merge-db-stable into launchpad:master.
Commit message: Merge db-stable 14771964f7 Add the VulnerabilitySubscription table Requested reviews: Launchpad code reviewers (launchpad-reviewers) For more details, see: https://code.launchpad.net/~lgp171188/launchpad/+git/launchpad/+merge/427425 -- Your team Launchpad code reviewers is requested to review the proposed merge of ~lgp171188/launchpad:merge-db-stable into launchpad:master.
diff --git a/database/schema/patch-2211-02-0.sql b/database/schema/patch-2211-02-0.sql new file mode 100644 index 0000000..8f7d622 --- /dev/null +++ b/database/schema/patch-2211-02-0.sql @@ -0,0 +1,112 @@ +-- Copyright 2022 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 Vulnerability + ADD COLUMN access_policy integer, + ADD COLUMN access_grants integer[]; + +CREATE TABLE VulnerabilitySubscription ( + id serial PRIMARY KEY, + person integer REFERENCES Person NOT NULL, + vulnerability integer REFERENCES Vulnerability NOT NULL, + date_created timestamp without time zone DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') NOT NULL, + subscribed_by integer REFERENCES Person NOT NULL +); + +COMMENT ON TABLE VulnerabilitySubscription IS 'Person subscription for Vulnerabilities.'; +COMMENT ON COLUMN VulnerabilitySubscription.person IS 'The person subscribing to the vulnerability.'; +COMMENT ON COLUMN VulnerabilitySubscription.vulnerability IS 'The vulnerability being subscribed to.'; +COMMENT ON COLUMN VulnerabilitySubscription.date_created IS 'The date when the subscription was created.'; +COMMENT ON COLUMN VulnerabilitySubscription.subscribed_by IS 'The person who created the subscription.'; + +CREATE UNIQUE INDEX vulnerabilitysubscription__person__vulnerability__key + ON VulnerabilitySubscription (person, vulnerability); + +CREATE INDEX vulnerabilitysubscription__vulnerability__idx + ON VulnerabilitySubscription (vulnerability); + +CREATE INDEX vulnerabilitysubscription__subscribed_by__idx + ON VulnerabilitySubscription (subscribed_by); + +ALTER TABLE AccessArtifact + ADD COLUMN vulnerability integer REFERENCES Vulnerability; + + +ALTER TABLE AccessArtifact DROP CONSTRAINT has_artifact; +ALTER TABLE AccessArtifact + ADD CONSTRAINT has_artifact CHECK ( + (null_count(ARRAY[bug, branch, gitrepository, snap, specification, ocirecipe, vulnerability]) = 6)) NOT VALID; + + +CREATE OR REPLACE FUNCTION vulnerability_denorm_access(vulnerability_id integer) + RETURNS void LANGUAGE plpgsql AS +$$ +DECLARE + info_type integer; +BEGIN + SELECT Vulnerability.information_type INTO info_type + FROM Vulnerability where id = vulnerability_id; + + UPDATE Vulnerability + SET access_policy = policies[1], access_grants = grants + FROM + build_access_cache( + (SELECT id FROM accessartifact WHERE vulnerability = vulnerability_id), + info_type) + AS (policies integer[], grants integer[]) + WHERE id = vulnerability_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; + IF artifact_row.vulnerability IS NOT NULL THEN + PERFORM vulnerability_denorm_access(artifact_row.vulnerability); + 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, ocirecipes, and vulnerabilities.'; + +-- A trigger to handle vulnerability.information_type changes. +CREATE OR REPLACE FUNCTION vulnerability_maintain_access_cache_trig() RETURNS trigger + LANGUAGE plpgsql as $$ +BEGIN + PERFORM vulnerability_denorm_access(NEW.id); + RETURN NULL; +END; +$$; + +CREATE TRIGGER vulnerability_maintain_access_cache + AFTER INSERT OR UPDATE OF information_type ON Vulnerability + FOR EACH ROW EXECUTE PROCEDURE vulnerability_maintain_access_cache_trig(); + +INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 02, 0); diff --git a/database/schema/security.cfg b/database/schema/security.cfg index 56eb404..a7f801b 100644 --- a/database/schema/security.cfg +++ b/database/schema/security.cfg @@ -92,6 +92,7 @@ public.valid_keyid(text) = EXECUTE public.valid_name(text) = EXECUTE public.valid_regexp(text) = EXECUTE public.version_sort_key(text) = EXECUTE +public.vulnerability_denorm_access(integer) = EXECUTE # BugSummary trigger functions and helpers. public.bug_summary_dec(bugsummary) = public.bug_summary_inc(bugsummary) = @@ -355,6 +356,7 @@ public.vote = SELECT, INSERT, UPDATE public.votecast = SELECT, INSERT public.vulnerability = SELECT, INSERT, UPDATE, DELETE public.vulnerabilityactivity = SELECT, INSERT, UPDATE, DELETE +public.vulnerabilitysubscription = SELECT, INSERT, UPDATE, DELETE public.webhook = SELECT, INSERT, UPDATE, DELETE public.webhookjob = SELECT, INSERT, UPDATE, DELETE public.wikiname = SELECT, INSERT, UPDATE, DELETE @@ -2173,6 +2175,7 @@ public.snapsubscription = SELECT, UPDATE, DELETE public.specification = SELECT public.specificationsubscription = SELECT, DELETE public.teamparticipation = SELECT +public.vulnerabilitysubscription = SELECT, UPDATE, DELETE type=user [upgrade-branches] @@ -2466,6 +2469,7 @@ public.vote = SELECT, UPDATE public.votecast = SELECT, UPDATE public.vulnerability = SELECT, UPDATE public.vulnerabilityactivity = SELECT, UPDATE +public.vulnerabilitysubscription = SELECT, UPDATE, DELETE public.webhook = SELECT, UPDATE public.wikiname = SELECT, UPDATE public.xref = SELECT, UPDATE diff --git a/lib/lp/registry/personmerge.py b/lib/lp/registry/personmerge.py index 9dd3dba..66b19b3 100644 --- a/lib/lp/registry/personmerge.py +++ b/lib/lp/registry/personmerge.py @@ -893,6 +893,24 @@ def _mergeOCIRecipeSubscription(cur, from_id, to_id): ) +def _mergeVulnerabilitySubscription(cur, from_id, to_id): + # Update only the VulnerabilitySubscription that will not conflict. + cur.execute(''' + UPDATE VulnerabilitySubscription + SET person=%(to_id)d + WHERE person=%(from_id)d AND vulnerability NOT IN + ( + SELECT vulnerability + FROM VulnerabilitySubscription + WHERE person = %(to_id)d + ) + ''' % vars()) + # and delete those left over. + cur.execute(''' + DELETE FROM VulnerabilitySubscription WHERE person=%(from_id)d + ''' % vars()) + + def _mergeCharmRecipe(cur, from_person, to_person): # This shouldn't use removeSecurityProxy. recipes = getUtility(ICharmRecipeSet).findByOwner(from_person) @@ -1162,6 +1180,9 @@ def merge_people(from_person, to_person, reviewer, delete=False): _mergeCharmRecipe(cur, from_id, to_id) skip.append(("charmrecipe", "owner")) + _mergeVulnerabilitySubscription(cur, from_id, to_id) + skip.append(('vulnerabilitysubscription', 'person')) + # Sanity check. If we have a reference that participates in a # UNIQUE index, it must have already been handled by this point. # We can tell this by looking at the skip list.
_______________________________________________ Mailing list: https://launchpad.net/~launchpad-reviewers Post to : launchpad-reviewers@lists.launchpad.net Unsubscribe : https://launchpad.net/~launchpad-reviewers More help : https://help.launchpad.net/ListHelp