| rev |
line source |
|
jbe@67
|
1 BEGIN;
|
|
jbe@67
|
2
|
|
jbe@67
|
3
|
|
jbe@67
|
4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
|
jbe@67
|
5 SELECT * FROM (VALUES ('1.2.5', 1, 2, 5))
|
|
jbe@67
|
6 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@67
|
7
|
|
jbe@67
|
8
|
|
jbe@67
|
9 CREATE FUNCTION "share_row_lock_issue_trigger"()
|
|
jbe@67
|
10 RETURNS TRIGGER
|
|
jbe@67
|
11 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@67
|
12 BEGIN
|
|
jbe@67
|
13 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
|
|
jbe@67
|
14 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
|
|
jbe@67
|
15 END IF;
|
|
jbe@67
|
16 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
|
jbe@67
|
17 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
|
|
jbe@67
|
18 RETURN NEW;
|
|
jbe@67
|
19 ELSE
|
|
jbe@67
|
20 RETURN OLD;
|
|
jbe@67
|
21 END IF;
|
|
jbe@67
|
22 END;
|
|
jbe@67
|
23 $$;
|
|
jbe@67
|
24
|
|
jbe@67
|
25 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
|
|
jbe@67
|
26
|
|
jbe@67
|
27
|
|
jbe@67
|
28 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
|
|
jbe@67
|
29 RETURNS TRIGGER
|
|
jbe@67
|
30 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@67
|
31 BEGIN
|
|
jbe@67
|
32 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
|
|
jbe@67
|
33 PERFORM NULL FROM "issue"
|
|
jbe@67
|
34 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@67
|
35 WHERE "initiative"."id" = OLD."initiative_id"
|
|
jbe@67
|
36 FOR SHARE OF "issue";
|
|
jbe@67
|
37 END IF;
|
|
jbe@67
|
38 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
|
|
jbe@67
|
39 PERFORM NULL FROM "issue"
|
|
jbe@67
|
40 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@67
|
41 WHERE "initiative"."id" = NEW."initiative_id"
|
|
jbe@67
|
42 FOR SHARE OF "issue";
|
|
jbe@67
|
43 RETURN NEW;
|
|
jbe@67
|
44 ELSE
|
|
jbe@67
|
45 RETURN OLD;
|
|
jbe@67
|
46 END IF;
|
|
jbe@67
|
47 END;
|
|
jbe@67
|
48 $$;
|
|
jbe@67
|
49
|
|
jbe@67
|
50 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
|
|
jbe@67
|
51
|
|
jbe@67
|
52
|
|
jbe@67
|
53 CREATE TRIGGER "share_row_lock_issue"
|
|
jbe@67
|
54 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
|
|
jbe@67
|
55 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@67
|
56 "share_row_lock_issue_trigger"();
|
|
jbe@67
|
57
|
|
jbe@67
|
58 CREATE TRIGGER "share_row_lock_issue"
|
|
jbe@67
|
59 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
|
|
jbe@67
|
60 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@67
|
61 "share_row_lock_issue_trigger"();
|
|
jbe@67
|
62
|
|
jbe@67
|
63 CREATE TRIGGER "share_row_lock_issue"
|
|
jbe@67
|
64 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
|
|
jbe@67
|
65 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@67
|
66 "share_row_lock_issue_trigger"();
|
|
jbe@67
|
67
|
|
jbe@67
|
68 CREATE TRIGGER "share_row_lock_issue_via_initiative"
|
|
jbe@67
|
69 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
|
|
jbe@67
|
70 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@67
|
71 "share_row_lock_issue_via_initiative_trigger"();
|
|
jbe@67
|
72
|
|
jbe@67
|
73 CREATE TRIGGER "share_row_lock_issue"
|
|
jbe@67
|
74 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
|
|
jbe@67
|
75 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@67
|
76 "share_row_lock_issue_trigger"();
|
|
jbe@67
|
77
|
|
jbe@67
|
78 CREATE TRIGGER "share_row_lock_issue"
|
|
jbe@67
|
79 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
|
|
jbe@67
|
80 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@67
|
81 "share_row_lock_issue_trigger"();
|
|
jbe@67
|
82
|
|
jbe@67
|
83 CREATE TRIGGER "share_row_lock_issue"
|
|
jbe@67
|
84 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
|
|
jbe@67
|
85 FOR EACH ROW EXECUTE PROCEDURE
|
|
jbe@67
|
86 "share_row_lock_issue_trigger"();
|
|
jbe@67
|
87
|
|
jbe@67
|
88 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
|
|
jbe@67
|
89 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
|
|
jbe@67
|
90 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
|
|
jbe@67
|
91 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
|
|
jbe@67
|
92 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
|
|
jbe@67
|
93 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
|
|
jbe@67
|
94 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
|
|
jbe@67
|
95
|
|
jbe@67
|
96
|
|
jbe@67
|
97 CREATE FUNCTION "lock_issue"
|
|
jbe@67
|
98 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@67
|
99 RETURNS VOID
|
|
jbe@67
|
100 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@67
|
101 BEGIN
|
|
jbe@67
|
102 LOCK TABLE "member" IN SHARE MODE;
|
|
jbe@67
|
103 LOCK TABLE "membership" IN SHARE MODE;
|
|
jbe@67
|
104 LOCK TABLE "policy" IN SHARE MODE;
|
|
jbe@67
|
105 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
|
|
jbe@67
|
106 -- NOTE: The row-level exclusive lock in combination with the
|
|
jbe@67
|
107 -- share_row_lock_issue(_via_initiative)_trigger functions (which
|
|
jbe@67
|
108 -- acquire a row-level share lock on the issue) ensure that no data
|
|
jbe@67
|
109 -- is changed, which could affect calculation of snapshots or
|
|
jbe@67
|
110 -- counting of votes. Table "delegation" must be table-level-locked,
|
|
jbe@67
|
111 -- as it also contains issue- and global-scope delegations.
|
|
jbe@67
|
112 LOCK TABLE "delegation" IN SHARE MODE;
|
|
jbe@67
|
113 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@67
|
114 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@67
|
115 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@67
|
116 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@67
|
117 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
|
|
jbe@67
|
118 RETURN;
|
|
jbe@67
|
119 END;
|
|
jbe@67
|
120 $$;
|
|
jbe@67
|
121
|
|
jbe@67
|
122 COMMENT ON FUNCTION "lock_issue"
|
|
jbe@67
|
123 ( "issue"."id"%TYPE )
|
|
jbe@67
|
124 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
|
|
jbe@67
|
125
|
|
jbe@67
|
126
|
|
jbe@67
|
127 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
|
|
jbe@67
|
128 RETURNS VOID
|
|
jbe@67
|
129 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@67
|
130 BEGIN
|
|
jbe@67
|
131 LOCK TABLE "member" IN SHARE MODE;
|
|
jbe@67
|
132 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
|
|
jbe@67
|
133 LOCK TABLE "area" IN EXCLUSIVE MODE;
|
|
jbe@67
|
134 LOCK TABLE "membership" IN SHARE MODE;
|
|
jbe@67
|
135 DELETE FROM "member_count";
|
|
jbe@67
|
136 INSERT INTO "member_count" ("total_count")
|
|
jbe@67
|
137 SELECT "total_count" FROM "member_count_view";
|
|
jbe@67
|
138 UPDATE "area" SET
|
|
jbe@67
|
139 "direct_member_count" = "view"."direct_member_count",
|
|
jbe@67
|
140 "member_weight" = "view"."member_weight",
|
|
jbe@67
|
141 "autoreject_weight" = "view"."autoreject_weight"
|
|
jbe@67
|
142 FROM "area_member_count" AS "view"
|
|
jbe@67
|
143 WHERE "view"."area_id" = "area"."id";
|
|
jbe@67
|
144 RETURN;
|
|
jbe@67
|
145 END;
|
|
jbe@67
|
146 $$;
|
|
jbe@67
|
147
|
|
jbe@67
|
148 CREATE OR REPLACE FUNCTION "create_snapshot"
|
|
jbe@67
|
149 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@67
|
150 RETURNS VOID
|
|
jbe@67
|
151 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@67
|
152 DECLARE
|
|
jbe@67
|
153 "initiative_id_v" "initiative"."id"%TYPE;
|
|
jbe@67
|
154 "suggestion_id_v" "suggestion"."id"%TYPE;
|
|
jbe@67
|
155 BEGIN
|
|
jbe@67
|
156 PERFORM "lock_issue"("issue_id_p");
|
|
jbe@67
|
157 PERFORM "create_population_snapshot"("issue_id_p");
|
|
jbe@67
|
158 PERFORM "create_interest_snapshot"("issue_id_p");
|
|
jbe@67
|
159 UPDATE "issue" SET
|
|
jbe@67
|
160 "snapshot" = now(),
|
|
jbe@67
|
161 "latest_snapshot_event" = 'periodic',
|
|
jbe@67
|
162 "population" = (
|
|
jbe@67
|
163 SELECT coalesce(sum("weight"), 0)
|
|
jbe@67
|
164 FROM "direct_population_snapshot"
|
|
jbe@67
|
165 WHERE "issue_id" = "issue_id_p"
|
|
jbe@67
|
166 AND "event" = 'periodic'
|
|
jbe@67
|
167 ),
|
|
jbe@67
|
168 "vote_now" = (
|
|
jbe@67
|
169 SELECT coalesce(sum("weight"), 0)
|
|
jbe@67
|
170 FROM "direct_interest_snapshot"
|
|
jbe@67
|
171 WHERE "issue_id" = "issue_id_p"
|
|
jbe@67
|
172 AND "event" = 'periodic'
|
|
jbe@67
|
173 AND "voting_requested" = TRUE
|
|
jbe@67
|
174 ),
|
|
jbe@67
|
175 "vote_later" = (
|
|
jbe@67
|
176 SELECT coalesce(sum("weight"), 0)
|
|
jbe@67
|
177 FROM "direct_interest_snapshot"
|
|
jbe@67
|
178 WHERE "issue_id" = "issue_id_p"
|
|
jbe@67
|
179 AND "event" = 'periodic'
|
|
jbe@67
|
180 AND "voting_requested" = FALSE
|
|
jbe@67
|
181 )
|
|
jbe@67
|
182 WHERE "id" = "issue_id_p";
|
|
jbe@67
|
183 FOR "initiative_id_v" IN
|
|
jbe@67
|
184 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
|
|
jbe@67
|
185 LOOP
|
|
jbe@67
|
186 UPDATE "initiative" SET
|
|
jbe@67
|
187 "supporter_count" = (
|
|
jbe@67
|
188 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@67
|
189 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@67
|
190 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@67
|
191 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@67
|
192 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@67
|
193 AND "di"."event" = 'periodic'
|
|
jbe@67
|
194 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@67
|
195 AND "ds"."event" = 'periodic'
|
|
jbe@67
|
196 ),
|
|
jbe@67
|
197 "informed_supporter_count" = (
|
|
jbe@67
|
198 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@67
|
199 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@67
|
200 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@67
|
201 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@67
|
202 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@67
|
203 AND "di"."event" = 'periodic'
|
|
jbe@67
|
204 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@67
|
205 AND "ds"."event" = 'periodic'
|
|
jbe@67
|
206 AND "ds"."informed"
|
|
jbe@67
|
207 ),
|
|
jbe@67
|
208 "satisfied_supporter_count" = (
|
|
jbe@67
|
209 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@67
|
210 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@67
|
211 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@67
|
212 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@67
|
213 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@67
|
214 AND "di"."event" = 'periodic'
|
|
jbe@67
|
215 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@67
|
216 AND "ds"."event" = 'periodic'
|
|
jbe@67
|
217 AND "ds"."satisfied"
|
|
jbe@67
|
218 ),
|
|
jbe@67
|
219 "satisfied_informed_supporter_count" = (
|
|
jbe@67
|
220 SELECT coalesce(sum("di"."weight"), 0)
|
|
jbe@67
|
221 FROM "direct_interest_snapshot" AS "di"
|
|
jbe@67
|
222 JOIN "direct_supporter_snapshot" AS "ds"
|
|
jbe@67
|
223 ON "di"."member_id" = "ds"."member_id"
|
|
jbe@67
|
224 WHERE "di"."issue_id" = "issue_id_p"
|
|
jbe@67
|
225 AND "di"."event" = 'periodic'
|
|
jbe@67
|
226 AND "ds"."initiative_id" = "initiative_id_v"
|
|
jbe@67
|
227 AND "ds"."event" = 'periodic'
|
|
jbe@67
|
228 AND "ds"."informed"
|
|
jbe@67
|
229 AND "ds"."satisfied"
|
|
jbe@67
|
230 )
|
|
jbe@67
|
231 WHERE "id" = "initiative_id_v";
|
|
jbe@67
|
232 FOR "suggestion_id_v" IN
|
|
jbe@67
|
233 SELECT "id" FROM "suggestion"
|
|
jbe@67
|
234 WHERE "initiative_id" = "initiative_id_v"
|
|
jbe@67
|
235 LOOP
|
|
jbe@67
|
236 UPDATE "suggestion" SET
|
|
jbe@67
|
237 "minus2_unfulfilled_count" = (
|
|
jbe@67
|
238 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@67
|
239 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@67
|
240 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@67
|
241 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@67
|
242 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@67
|
243 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@67
|
244 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@67
|
245 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@67
|
246 AND "opinion"."degree" = -2
|
|
jbe@67
|
247 AND "opinion"."fulfilled" = FALSE
|
|
jbe@67
|
248 ),
|
|
jbe@67
|
249 "minus2_fulfilled_count" = (
|
|
jbe@67
|
250 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@67
|
251 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@67
|
252 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@67
|
253 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@67
|
254 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@67
|
255 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@67
|
256 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@67
|
257 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@67
|
258 AND "opinion"."degree" = -2
|
|
jbe@67
|
259 AND "opinion"."fulfilled" = TRUE
|
|
jbe@67
|
260 ),
|
|
jbe@67
|
261 "minus1_unfulfilled_count" = (
|
|
jbe@67
|
262 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@67
|
263 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@67
|
264 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@67
|
265 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@67
|
266 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@67
|
267 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@67
|
268 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@67
|
269 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@67
|
270 AND "opinion"."degree" = -1
|
|
jbe@67
|
271 AND "opinion"."fulfilled" = FALSE
|
|
jbe@67
|
272 ),
|
|
jbe@67
|
273 "minus1_fulfilled_count" = (
|
|
jbe@67
|
274 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@67
|
275 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@67
|
276 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@67
|
277 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@67
|
278 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@67
|
279 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@67
|
280 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@67
|
281 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@67
|
282 AND "opinion"."degree" = -1
|
|
jbe@67
|
283 AND "opinion"."fulfilled" = TRUE
|
|
jbe@67
|
284 ),
|
|
jbe@67
|
285 "plus1_unfulfilled_count" = (
|
|
jbe@67
|
286 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@67
|
287 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@67
|
288 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@67
|
289 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@67
|
290 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@67
|
291 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@67
|
292 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@67
|
293 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@67
|
294 AND "opinion"."degree" = 1
|
|
jbe@67
|
295 AND "opinion"."fulfilled" = FALSE
|
|
jbe@67
|
296 ),
|
|
jbe@67
|
297 "plus1_fulfilled_count" = (
|
|
jbe@67
|
298 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@67
|
299 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@67
|
300 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@67
|
301 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@67
|
302 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@67
|
303 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@67
|
304 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@67
|
305 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@67
|
306 AND "opinion"."degree" = 1
|
|
jbe@67
|
307 AND "opinion"."fulfilled" = TRUE
|
|
jbe@67
|
308 ),
|
|
jbe@67
|
309 "plus2_unfulfilled_count" = (
|
|
jbe@67
|
310 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@67
|
311 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@67
|
312 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@67
|
313 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@67
|
314 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@67
|
315 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@67
|
316 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@67
|
317 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@67
|
318 AND "opinion"."degree" = 2
|
|
jbe@67
|
319 AND "opinion"."fulfilled" = FALSE
|
|
jbe@67
|
320 ),
|
|
jbe@67
|
321 "plus2_fulfilled_count" = (
|
|
jbe@67
|
322 SELECT coalesce(sum("snapshot"."weight"), 0)
|
|
jbe@67
|
323 FROM "issue" CROSS JOIN "opinion"
|
|
jbe@67
|
324 JOIN "direct_interest_snapshot" AS "snapshot"
|
|
jbe@67
|
325 ON "snapshot"."issue_id" = "issue"."id"
|
|
jbe@67
|
326 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@67
|
327 AND "snapshot"."member_id" = "opinion"."member_id"
|
|
jbe@67
|
328 WHERE "issue"."id" = "issue_id_p"
|
|
jbe@67
|
329 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
|
jbe@67
|
330 AND "opinion"."degree" = 2
|
|
jbe@67
|
331 AND "opinion"."fulfilled" = TRUE
|
|
jbe@67
|
332 )
|
|
jbe@67
|
333 WHERE "suggestion"."id" = "suggestion_id_v";
|
|
jbe@67
|
334 END LOOP;
|
|
jbe@67
|
335 END LOOP;
|
|
jbe@67
|
336 RETURN;
|
|
jbe@67
|
337 END;
|
|
jbe@67
|
338 $$;
|
|
jbe@67
|
339
|
|
jbe@67
|
340 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
|
|
jbe@67
|
341 RETURNS VOID
|
|
jbe@67
|
342 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@67
|
343 DECLARE
|
|
jbe@67
|
344 "issue_row" "issue"%ROWTYPE;
|
|
jbe@67
|
345 "member_id_v" "member"."id"%TYPE;
|
|
jbe@67
|
346 BEGIN
|
|
jbe@67
|
347 PERFORM "lock_issue"("issue_id_p");
|
|
jbe@67
|
348 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@67
|
349 DELETE FROM "delegating_voter"
|
|
jbe@67
|
350 WHERE "issue_id" = "issue_id_p";
|
|
jbe@67
|
351 DELETE FROM "direct_voter"
|
|
jbe@67
|
352 WHERE "issue_id" = "issue_id_p"
|
|
jbe@67
|
353 AND "autoreject" = TRUE;
|
|
jbe@67
|
354 DELETE FROM "direct_voter" USING "member"
|
|
jbe@67
|
355 WHERE "direct_voter"."member_id" = "member"."id"
|
|
jbe@67
|
356 AND "direct_voter"."issue_id" = "issue_id_p"
|
|
jbe@67
|
357 AND "member"."active" = FALSE;
|
|
jbe@67
|
358 UPDATE "direct_voter" SET "weight" = 1
|
|
jbe@67
|
359 WHERE "issue_id" = "issue_id_p";
|
|
jbe@67
|
360 PERFORM "add_vote_delegations"("issue_id_p");
|
|
jbe@67
|
361 FOR "member_id_v" IN
|
|
jbe@67
|
362 SELECT "interest"."member_id"
|
|
jbe@67
|
363 FROM "interest"
|
|
jbe@67
|
364 LEFT JOIN "direct_voter"
|
|
jbe@67
|
365 ON "interest"."member_id" = "direct_voter"."member_id"
|
|
jbe@67
|
366 AND "interest"."issue_id" = "direct_voter"."issue_id"
|
|
jbe@67
|
367 LEFT JOIN "delegating_voter"
|
|
jbe@67
|
368 ON "interest"."member_id" = "delegating_voter"."member_id"
|
|
jbe@67
|
369 AND "interest"."issue_id" = "delegating_voter"."issue_id"
|
|
jbe@67
|
370 WHERE "interest"."issue_id" = "issue_id_p"
|
|
jbe@67
|
371 AND "interest"."autoreject" = TRUE
|
|
jbe@67
|
372 AND "direct_voter"."member_id" ISNULL
|
|
jbe@67
|
373 AND "delegating_voter"."member_id" ISNULL
|
|
jbe@67
|
374 UNION SELECT "membership"."member_id"
|
|
jbe@67
|
375 FROM "membership"
|
|
jbe@67
|
376 LEFT JOIN "interest"
|
|
jbe@67
|
377 ON "membership"."member_id" = "interest"."member_id"
|
|
jbe@67
|
378 AND "interest"."issue_id" = "issue_id_p"
|
|
jbe@67
|
379 LEFT JOIN "direct_voter"
|
|
jbe@67
|
380 ON "membership"."member_id" = "direct_voter"."member_id"
|
|
jbe@67
|
381 AND "direct_voter"."issue_id" = "issue_id_p"
|
|
jbe@67
|
382 LEFT JOIN "delegating_voter"
|
|
jbe@67
|
383 ON "membership"."member_id" = "delegating_voter"."member_id"
|
|
jbe@67
|
384 AND "delegating_voter"."issue_id" = "issue_id_p"
|
|
jbe@67
|
385 WHERE "membership"."area_id" = "issue_row"."area_id"
|
|
jbe@67
|
386 AND "membership"."autoreject" = TRUE
|
|
jbe@67
|
387 AND "interest"."autoreject" ISNULL
|
|
jbe@67
|
388 AND "direct_voter"."member_id" ISNULL
|
|
jbe@67
|
389 AND "delegating_voter"."member_id" ISNULL
|
|
jbe@67
|
390 LOOP
|
|
jbe@67
|
391 INSERT INTO "direct_voter"
|
|
jbe@67
|
392 ("member_id", "issue_id", "weight", "autoreject") VALUES
|
|
jbe@67
|
393 ("member_id_v", "issue_id_p", 1, TRUE);
|
|
jbe@67
|
394 INSERT INTO "vote" (
|
|
jbe@67
|
395 "member_id",
|
|
jbe@67
|
396 "issue_id",
|
|
jbe@67
|
397 "initiative_id",
|
|
jbe@67
|
398 "grade"
|
|
jbe@67
|
399 ) SELECT
|
|
jbe@67
|
400 "member_id_v" AS "member_id",
|
|
jbe@67
|
401 "issue_id_p" AS "issue_id",
|
|
jbe@67
|
402 "id" AS "initiative_id",
|
|
jbe@67
|
403 -1 AS "grade"
|
|
jbe@67
|
404 FROM "initiative" WHERE "issue_id" = "issue_id_p";
|
|
jbe@67
|
405 END LOOP;
|
|
jbe@67
|
406 PERFORM "add_vote_delegations"("issue_id_p");
|
|
jbe@67
|
407 UPDATE "issue" SET
|
|
jbe@67
|
408 "closed" = now(),
|
|
jbe@67
|
409 "voter_count" = (
|
|
jbe@67
|
410 SELECT coalesce(sum("weight"), 0)
|
|
jbe@67
|
411 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
|
|
jbe@67
|
412 )
|
|
jbe@67
|
413 WHERE "id" = "issue_id_p";
|
|
jbe@67
|
414 UPDATE "initiative" SET
|
|
jbe@67
|
415 "positive_votes" = "vote_counts"."positive_votes",
|
|
jbe@67
|
416 "negative_votes" = "vote_counts"."negative_votes",
|
|
jbe@67
|
417 "agreed" = CASE WHEN "majority_strict" THEN
|
|
jbe@67
|
418 "vote_counts"."positive_votes" * "majority_den" >
|
|
jbe@67
|
419 "majority_num" *
|
|
jbe@67
|
420 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
|
|
jbe@67
|
421 ELSE
|
|
jbe@67
|
422 "vote_counts"."positive_votes" * "majority_den" >=
|
|
jbe@67
|
423 "majority_num" *
|
|
jbe@67
|
424 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
|
|
jbe@67
|
425 END
|
|
jbe@67
|
426 FROM
|
|
jbe@67
|
427 ( SELECT
|
|
jbe@67
|
428 "initiative"."id" AS "initiative_id",
|
|
jbe@67
|
429 coalesce(
|
|
jbe@67
|
430 sum(
|
|
jbe@67
|
431 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
|
|
jbe@67
|
432 ),
|
|
jbe@67
|
433 0
|
|
jbe@67
|
434 ) AS "positive_votes",
|
|
jbe@67
|
435 coalesce(
|
|
jbe@67
|
436 sum(
|
|
jbe@67
|
437 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
|
|
jbe@67
|
438 ),
|
|
jbe@67
|
439 0
|
|
jbe@67
|
440 ) AS "negative_votes"
|
|
jbe@67
|
441 FROM "initiative"
|
|
jbe@67
|
442 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
|
|
jbe@67
|
443 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
|
|
jbe@67
|
444 LEFT JOIN "direct_voter"
|
|
jbe@67
|
445 ON "direct_voter"."issue_id" = "initiative"."issue_id"
|
|
jbe@67
|
446 LEFT JOIN "vote"
|
|
jbe@67
|
447 ON "vote"."initiative_id" = "initiative"."id"
|
|
jbe@67
|
448 AND "vote"."member_id" = "direct_voter"."member_id"
|
|
jbe@67
|
449 WHERE "initiative"."issue_id" = "issue_id_p"
|
|
jbe@67
|
450 AND "initiative"."admitted" -- NOTE: NULL case is handled too
|
|
jbe@67
|
451 GROUP BY "initiative"."id"
|
|
jbe@67
|
452 ) AS "vote_counts",
|
|
jbe@67
|
453 "issue",
|
|
jbe@67
|
454 "policy"
|
|
jbe@67
|
455 WHERE "vote_counts"."initiative_id" = "initiative"."id"
|
|
jbe@67
|
456 AND "issue"."id" = "initiative"."issue_id"
|
|
jbe@67
|
457 AND "policy"."id" = "issue"."policy_id";
|
|
jbe@67
|
458 -- NOTE: "closed" column of issue must be set at this point
|
|
jbe@67
|
459 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
|
jbe@67
|
460 INSERT INTO "battle" (
|
|
jbe@67
|
461 "issue_id",
|
|
jbe@67
|
462 "winning_initiative_id", "losing_initiative_id",
|
|
jbe@67
|
463 "count"
|
|
jbe@67
|
464 ) SELECT
|
|
jbe@67
|
465 "issue_id",
|
|
jbe@67
|
466 "winning_initiative_id", "losing_initiative_id",
|
|
jbe@67
|
467 "count"
|
|
jbe@67
|
468 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
|
|
jbe@67
|
469 END;
|
|
jbe@67
|
470 $$;
|
|
jbe@67
|
471
|
|
jbe@67
|
472 CREATE OR REPLACE FUNCTION "check_issue"
|
|
jbe@67
|
473 ( "issue_id_p" "issue"."id"%TYPE )
|
|
jbe@67
|
474 RETURNS VOID
|
|
jbe@67
|
475 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@67
|
476 DECLARE
|
|
jbe@67
|
477 "issue_row" "issue"%ROWTYPE;
|
|
jbe@67
|
478 "policy_row" "policy"%ROWTYPE;
|
|
jbe@67
|
479 "voting_requested_v" BOOLEAN;
|
|
jbe@67
|
480 BEGIN
|
|
jbe@67
|
481 PERFORM "lock_issue"("issue_id_p");
|
|
jbe@67
|
482 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@67
|
483 -- only process open issues:
|
|
jbe@67
|
484 IF "issue_row"."closed" ISNULL THEN
|
|
jbe@67
|
485 SELECT * INTO "policy_row" FROM "policy"
|
|
jbe@67
|
486 WHERE "id" = "issue_row"."policy_id";
|
|
jbe@67
|
487 -- create a snapshot, unless issue is already fully frozen:
|
|
jbe@67
|
488 IF "issue_row"."fully_frozen" ISNULL THEN
|
|
jbe@67
|
489 PERFORM "create_snapshot"("issue_id_p");
|
|
jbe@67
|
490 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@67
|
491 END IF;
|
|
jbe@67
|
492 -- eventually close or accept issues, which have not been accepted:
|
|
jbe@67
|
493 IF "issue_row"."accepted" ISNULL THEN
|
|
jbe@67
|
494 IF EXISTS (
|
|
jbe@67
|
495 SELECT NULL FROM "initiative"
|
|
jbe@67
|
496 WHERE "issue_id" = "issue_id_p"
|
|
jbe@67
|
497 AND "supporter_count" > 0
|
|
jbe@67
|
498 AND "supporter_count" * "policy_row"."issue_quorum_den"
|
|
jbe@67
|
499 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
|
|
jbe@67
|
500 ) THEN
|
|
jbe@67
|
501 -- accept issues, if supporter count is high enough
|
|
jbe@67
|
502 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
|
|
jbe@67
|
503 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
|
|
jbe@67
|
504 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
|
|
jbe@67
|
505 WHERE "id" = "issue_row"."id";
|
|
jbe@67
|
506 ELSIF
|
|
jbe@67
|
507 now() >= "issue_row"."created" + "issue_row"."admission_time"
|
|
jbe@67
|
508 THEN
|
|
jbe@67
|
509 -- close issues, if admission time has expired
|
|
jbe@67
|
510 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
|
|
jbe@67
|
511 UPDATE "issue" SET "closed" = now()
|
|
jbe@67
|
512 WHERE "id" = "issue_row"."id";
|
|
jbe@67
|
513 END IF;
|
|
jbe@67
|
514 END IF;
|
|
jbe@67
|
515 -- eventually half freeze issues:
|
|
jbe@67
|
516 IF
|
|
jbe@67
|
517 -- NOTE: issue can't be closed at this point, if it has been accepted
|
|
jbe@67
|
518 "issue_row"."accepted" NOTNULL AND
|
|
jbe@67
|
519 "issue_row"."half_frozen" ISNULL
|
|
jbe@67
|
520 THEN
|
|
jbe@67
|
521 SELECT
|
|
jbe@67
|
522 CASE
|
|
jbe@67
|
523 WHEN "vote_now" * 2 > "issue_row"."population" THEN
|
|
jbe@67
|
524 TRUE
|
|
jbe@67
|
525 WHEN "vote_later" * 2 > "issue_row"."population" THEN
|
|
jbe@67
|
526 FALSE
|
|
jbe@67
|
527 ELSE NULL
|
|
jbe@67
|
528 END
|
|
jbe@67
|
529 INTO "voting_requested_v"
|
|
jbe@67
|
530 FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@67
|
531 IF
|
|
jbe@67
|
532 "voting_requested_v" OR (
|
|
jbe@67
|
533 "voting_requested_v" ISNULL AND
|
|
jbe@67
|
534 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
|
|
jbe@67
|
535 )
|
|
jbe@67
|
536 THEN
|
|
jbe@67
|
537 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
|
|
jbe@67
|
538 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
|
|
jbe@67
|
539 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
|
|
jbe@67
|
540 WHERE "id" = "issue_row"."id";
|
|
jbe@67
|
541 END IF;
|
|
jbe@67
|
542 END IF;
|
|
jbe@67
|
543 -- close issues after some time, if all initiatives have been revoked:
|
|
jbe@67
|
544 IF
|
|
jbe@67
|
545 "issue_row"."closed" ISNULL AND
|
|
jbe@67
|
546 NOT EXISTS (
|
|
jbe@67
|
547 -- all initiatives are revoked
|
|
jbe@67
|
548 SELECT NULL FROM "initiative"
|
|
jbe@67
|
549 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
|
|
jbe@67
|
550 ) AND (
|
|
jbe@67
|
551 NOT EXISTS (
|
|
jbe@67
|
552 -- and no initiatives have been revoked lately
|
|
jbe@67
|
553 SELECT NULL FROM "initiative"
|
|
jbe@67
|
554 WHERE "issue_id" = "issue_id_p"
|
|
jbe@67
|
555 AND now() < "revoked" + "issue_row"."verification_time"
|
|
jbe@67
|
556 ) OR (
|
|
jbe@67
|
557 -- or verification time has elapsed
|
|
jbe@67
|
558 "issue_row"."half_frozen" NOTNULL AND
|
|
jbe@67
|
559 "issue_row"."fully_frozen" ISNULL AND
|
|
jbe@67
|
560 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
|
|
jbe@67
|
561 )
|
|
jbe@67
|
562 )
|
|
jbe@67
|
563 THEN
|
|
jbe@67
|
564 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
|
|
jbe@67
|
565 UPDATE "issue" SET "closed" = "issue_row"."closed"
|
|
jbe@67
|
566 WHERE "id" = "issue_row"."id";
|
|
jbe@67
|
567 END IF;
|
|
jbe@67
|
568 -- fully freeze issue after verification time:
|
|
jbe@67
|
569 IF
|
|
jbe@67
|
570 "issue_row"."half_frozen" NOTNULL AND
|
|
jbe@67
|
571 "issue_row"."fully_frozen" ISNULL AND
|
|
jbe@67
|
572 "issue_row"."closed" ISNULL AND
|
|
jbe@67
|
573 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
|
|
jbe@67
|
574 THEN
|
|
jbe@67
|
575 PERFORM "freeze_after_snapshot"("issue_id_p");
|
|
jbe@67
|
576 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
|
|
jbe@67
|
577 END IF;
|
|
jbe@67
|
578 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
|
jbe@67
|
579 -- close issue by calling close_voting(...) after voting time:
|
|
jbe@67
|
580 IF
|
|
jbe@67
|
581 "issue_row"."closed" ISNULL AND
|
|
jbe@67
|
582 "issue_row"."fully_frozen" NOTNULL AND
|
|
jbe@67
|
583 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
|
|
jbe@67
|
584 THEN
|
|
jbe@67
|
585 PERFORM "close_voting"("issue_id_p");
|
|
jbe@67
|
586 END IF;
|
|
jbe@67
|
587 END IF;
|
|
jbe@67
|
588 RETURN;
|
|
jbe@67
|
589 END;
|
|
jbe@67
|
590 $$;
|
|
jbe@67
|
591
|
|
jbe@67
|
592
|
|
jbe@67
|
593 DROP FUNCTION "global_lock"();
|
|
jbe@67
|
594
|
|
jbe@67
|
595
|
|
jbe@67
|
596 COMMIT;
|