1
-- Copyright 2011 Canonical Ltd. This software is licensed under the
2
-- GNU Affero General Public License version 3 (see the file LICENSE).
4
SET client_min_messages=ERROR;
6
CREATE OR REPLACE FUNCTION update_transitively_private(
8
_root_branch int = NULL,
9
_root_transitively_private boolean = NULL) RETURNS VOID
10
LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO PUBLIC AS
13
root_transitively_private boolean := _root_transitively_private;
14
root_branch int := _root_branch;
16
IF root_transitively_private IS NULL THEN
17
-- We can't just trust the transitively_private flag of the
18
-- branch we are stacked on, as if we are updating multiple
19
-- records they will be updated in an indeterminate order.
20
-- We need a recursive query.
21
UPDATE Branch SET transitively_private = (
22
WITH RECURSIVE stacked_branches AS (
24
top_branch.id, top_branch.stacked_on, top_branch.private
25
FROM Branch AS top_branch
26
WHERE top_branch.id = start_branch
29
sub_branch.id, sub_branch.stacked_on, sub_branch.private
30
FROM stacked_branches, Branch AS sub_branch
32
stacked_branches.stacked_on = sub_branch.id
33
AND stacked_branches.stacked_on != start_branch
34
-- Shortcircuit. No need to recurse if already private.
35
AND stacked_branches.private IS FALSE
39
WHERE private IS TRUE)
40
WHERE Branch.id = start_branch
41
RETURNING transitively_private INTO root_transitively_private;
42
root_branch := start_branch;
44
-- Now we have calculated the correct transitively_private flag
47
transitively_private = GREATEST(private, root_transitively_private)
48
WHERE id = root_branch;
51
-- Recurse to branches stacked on this one.
52
PERFORM update_transitively_private(
53
start_branch, id, GREATEST(private, root_transitively_private))
54
FROM Branch WHERE stacked_on = root_branch AND id != start_branch;
58
COMMENT ON FUNCTION update_transitively_private(int, int, boolean) IS
59
'A branch is transitively private if it is private or is stacked on any transitively private branches.';
61
CREATE OR REPLACE FUNCTION maintain_transitively_private() RETURNS TRIGGER
62
LANGUAGE plpgsql VOLATILE AS
65
IF TG_OP = 'UPDATE' THEN
66
IF (NEW.stacked_on IS NOT DISTINCT FROM OLD.stacked_on
67
AND NEW.private IS NOT DISTINCT FROM OLD.private) THEN
71
PERFORM update_transitively_private(NEW.id);
76
COMMENT ON FUNCTION maintain_transitively_private() IS
77
'Trigger maintaining the Branch transitively_private column';
79
CREATE TRIGGER maintain_branch_transitive_privacy_t
80
AFTER INSERT OR UPDATE ON Branch
82
EXECUTE PROCEDURE maintain_transitively_private();
84
INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 87, 1);