rev |
line source |
jbe@383
|
1 BEGIN;
|
jbe@383
|
2
|
jbe@383
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@383
|
4 SELECT * FROM (VALUES ('2.2.2', 2, 2, 2))
|
jbe@383
|
5 AS "subquery"("string", "major", "minor", "revision");
|
jbe@383
|
6
|
jbe@383
|
7 CREATE TABLE "internal_session_store" (
|
jbe@383
|
8 PRIMARY KEY ("backend_pid", "key"),
|
jbe@383
|
9 "backend_pid" INT4,
|
jbe@383
|
10 "key" TEXT,
|
jbe@383
|
11 "value" TEXT NOT NULL );
|
jbe@383
|
12
|
jbe@383
|
13 COMMENT ON TABLE "internal_session_store" IS 'Table to store session variables; shall be emptied before a transaction is committed';
|
jbe@383
|
14
|
jbe@383
|
15 COMMENT ON COLUMN "internal_session_store"."backend_pid" IS 'Value returned by function pg_backend_pid()';
|
jbe@383
|
16
|
jbe@383
|
17 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
|
jbe@383
|
18 RETURNS TRIGGER
|
jbe@383
|
19 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@383
|
20 DECLARE
|
jbe@383
|
21 "issue_id_v" "issue"."id"%TYPE;
|
jbe@383
|
22 "issue_row" "issue"%ROWTYPE;
|
jbe@383
|
23 BEGIN
|
jbe@383
|
24 IF EXISTS (
|
jbe@383
|
25 SELECT NULL FROM "internal_session_store"
|
jbe@383
|
26 WHERE "backend_pid" = pg_backend_pid()
|
jbe@383
|
27 AND "key" = 'override_protection_triggers'
|
jbe@383
|
28 AND "value" = TRUE::TEXT
|
jbe@383
|
29 ) THEN
|
jbe@383
|
30 RETURN NULL;
|
jbe@383
|
31 END IF;
|
jbe@383
|
32 IF TG_OP = 'DELETE' THEN
|
jbe@383
|
33 "issue_id_v" := OLD."issue_id";
|
jbe@383
|
34 ELSE
|
jbe@383
|
35 "issue_id_v" := NEW."issue_id";
|
jbe@383
|
36 END IF;
|
jbe@383
|
37 SELECT INTO "issue_row" * FROM "issue"
|
jbe@383
|
38 WHERE "id" = "issue_id_v" FOR SHARE;
|
jbe@383
|
39 IF (
|
jbe@383
|
40 "issue_row"."closed" NOTNULL OR (
|
jbe@383
|
41 "issue_row"."state" = 'voting' AND
|
jbe@383
|
42 "issue_row"."phase_finished" NOTNULL
|
jbe@383
|
43 )
|
jbe@383
|
44 ) THEN
|
jbe@383
|
45 IF
|
jbe@383
|
46 TG_RELID = 'direct_voter'::regclass AND
|
jbe@383
|
47 TG_OP = 'UPDATE'
|
jbe@383
|
48 THEN
|
jbe@383
|
49 IF
|
jbe@383
|
50 OLD."issue_id" = NEW."issue_id" AND
|
jbe@383
|
51 OLD."member_id" = NEW."member_id" AND
|
jbe@383
|
52 OLD."weight" = NEW."weight"
|
jbe@383
|
53 THEN
|
jbe@383
|
54 RETURN NULL; -- allows changing of voter comment
|
jbe@383
|
55 END IF;
|
jbe@383
|
56 END IF;
|
jbe@383
|
57 RAISE EXCEPTION 'Tried to modify data after voting has been closed.';
|
jbe@383
|
58 END IF;
|
jbe@383
|
59 RETURN NULL;
|
jbe@383
|
60 END;
|
jbe@383
|
61 $$;
|
jbe@383
|
62
|
jbe@383
|
63 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
|
jbe@383
|
64 RETURNS VOID
|
jbe@383
|
65 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@383
|
66 DECLARE
|
jbe@383
|
67 "area_id_v" "area"."id"%TYPE;
|
jbe@383
|
68 "unit_id_v" "unit"."id"%TYPE;
|
jbe@383
|
69 "member_id_v" "member"."id"%TYPE;
|
jbe@383
|
70 BEGIN
|
jbe@383
|
71 PERFORM "require_transaction_isolation"();
|
jbe@383
|
72 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@383
|
73 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
|
jbe@383
|
74 -- override protection triggers:
|
jbe@383
|
75 DELETE FROM "internal_session_store";
|
jbe@383
|
76 INSERT INTO "internal_session_store" ("backend_pid", "key", "value")
|
jbe@383
|
77 VALUES (pg_backend_pid(), 'override_protection_triggers', TRUE::TEXT);
|
jbe@383
|
78 -- delete timestamp of voting comment:
|
jbe@383
|
79 UPDATE "direct_voter" SET "comment_changed" = NULL
|
jbe@383
|
80 WHERE "issue_id" = "issue_id_p";
|
jbe@383
|
81 -- delete delegating votes (in cases of manual reset of issue state):
|
jbe@383
|
82 DELETE FROM "delegating_voter"
|
jbe@383
|
83 WHERE "issue_id" = "issue_id_p";
|
jbe@383
|
84 -- delete votes from non-privileged voters:
|
jbe@383
|
85 DELETE FROM "direct_voter"
|
jbe@383
|
86 USING (
|
jbe@383
|
87 SELECT
|
jbe@383
|
88 "direct_voter"."member_id"
|
jbe@383
|
89 FROM "direct_voter"
|
jbe@383
|
90 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
|
jbe@383
|
91 LEFT JOIN "privilege"
|
jbe@383
|
92 ON "privilege"."unit_id" = "unit_id_v"
|
jbe@383
|
93 AND "privilege"."member_id" = "direct_voter"."member_id"
|
jbe@383
|
94 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
|
jbe@383
|
95 "member"."active" = FALSE OR
|
jbe@383
|
96 "privilege"."voting_right" ISNULL OR
|
jbe@383
|
97 "privilege"."voting_right" = FALSE
|
jbe@383
|
98 )
|
jbe@383
|
99 ) AS "subquery"
|
jbe@383
|
100 WHERE "direct_voter"."issue_id" = "issue_id_p"
|
jbe@383
|
101 AND "direct_voter"."member_id" = "subquery"."member_id";
|
jbe@383
|
102 -- consider delegations:
|
jbe@383
|
103 UPDATE "direct_voter" SET "weight" = 1
|
jbe@383
|
104 WHERE "issue_id" = "issue_id_p";
|
jbe@383
|
105 PERFORM "add_vote_delegations"("issue_id_p");
|
jbe@383
|
106 -- finish overriding protection triggers (mandatory, as pids may be reused):
|
jbe@383
|
107 DELETE FROM "internal_session_store";
|
jbe@383
|
108 -- materialize battle_view:
|
jbe@383
|
109 -- NOTE: "closed" column of issue must be set at this point
|
jbe@383
|
110 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
jbe@383
|
111 INSERT INTO "battle" (
|
jbe@383
|
112 "issue_id",
|
jbe@383
|
113 "winning_initiative_id", "losing_initiative_id",
|
jbe@383
|
114 "count"
|
jbe@383
|
115 ) SELECT
|
jbe@383
|
116 "issue_id",
|
jbe@383
|
117 "winning_initiative_id", "losing_initiative_id",
|
jbe@383
|
118 "count"
|
jbe@383
|
119 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
|
jbe@383
|
120 -- set voter count:
|
jbe@383
|
121 UPDATE "issue" SET
|
jbe@383
|
122 "voter_count" = (
|
jbe@383
|
123 SELECT coalesce(sum("weight"), 0)
|
jbe@383
|
124 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
|
jbe@383
|
125 )
|
jbe@383
|
126 WHERE "id" = "issue_id_p";
|
jbe@383
|
127 -- copy "positive_votes" and "negative_votes" from "battle" table:
|
jbe@383
|
128 UPDATE "initiative" SET
|
jbe@383
|
129 "positive_votes" = "battle_win"."count",
|
jbe@383
|
130 "negative_votes" = "battle_lose"."count"
|
jbe@383
|
131 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
|
jbe@383
|
132 WHERE
|
jbe@383
|
133 "battle_win"."issue_id" = "issue_id_p" AND
|
jbe@383
|
134 "battle_win"."winning_initiative_id" = "initiative"."id" AND
|
jbe@383
|
135 "battle_win"."losing_initiative_id" ISNULL AND
|
jbe@383
|
136 "battle_lose"."issue_id" = "issue_id_p" AND
|
jbe@383
|
137 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
|
jbe@383
|
138 "battle_lose"."winning_initiative_id" ISNULL;
|
jbe@383
|
139 END;
|
jbe@383
|
140 $$;
|
jbe@383
|
141
|
jbe@383
|
142 COMMIT;
|