rev |
line source |
jbe@396
|
1 BEGIN;
|
jbe@396
|
2
|
jbe@396
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@420
|
4 SELECT * FROM (VALUES ('3.0.1', 3, 0, 1))
|
jbe@396
|
5 AS "subquery"("string", "major", "minor", "revision");
|
jbe@396
|
6
|
jbe@410
|
7 CREATE TABLE "issue_order_in_admission_state" (
|
jbe@400
|
8 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@410
|
9 "order_in_area" INT4,
|
jbe@410
|
10 "order_in_unit" INT4 );
|
jbe@396
|
11
|
jbe@410
|
12 COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"';
|
jbe@398
|
13
|
jbe@410
|
14 COMMENT ON COLUMN "issue_order_in_admission_state"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
|
jbe@410
|
15 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last';
|
jbe@410
|
16 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last';
|
jbe@396
|
17
|
jbe@396
|
18 CREATE VIEW "issue_supporter_in_admission_state" AS
|
jbe@396
|
19 SELECT DISTINCT
|
jbe@410
|
20 "area"."unit_id",
|
jbe@396
|
21 "issue"."area_id",
|
jbe@396
|
22 "issue"."id" AS "issue_id",
|
jbe@396
|
23 "supporter"."member_id",
|
jbe@396
|
24 "direct_interest_snapshot"."weight"
|
jbe@396
|
25 FROM "issue"
|
jbe@410
|
26 JOIN "area" ON "area"."id" = "issue"."area_id"
|
jbe@396
|
27 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
|
jbe@396
|
28 JOIN "direct_interest_snapshot"
|
jbe@396
|
29 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
|
jbe@396
|
30 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
|
jbe@396
|
31 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
|
jbe@396
|
32 WHERE "issue"."state" = 'admission'::"issue_state";
|
jbe@396
|
33
|
jbe@396
|
34 COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area';
|
jbe@396
|
35
|
jbe@411
|
36 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';
|
jbe@411
|
37 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
|
jbe@411
|
38 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)';
|
jbe@411
|
39 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';
|
jbe@411
|
40
|
jbe@411
|
41 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
|
jbe@411
|
42 RETURNS VOID
|
jbe@411
|
43 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@411
|
44 DECLARE
|
jbe@411
|
45 "issue_row" "issue"%ROWTYPE;
|
jbe@411
|
46 "policy_row" "policy"%ROWTYPE;
|
jbe@411
|
47 "dimension_v" INTEGER;
|
jbe@411
|
48 "vote_matrix" INT4[][]; -- absolute votes
|
jbe@411
|
49 "matrix" INT8[][]; -- defeat strength / best paths
|
jbe@411
|
50 "i" INTEGER;
|
jbe@411
|
51 "j" INTEGER;
|
jbe@411
|
52 "k" INTEGER;
|
jbe@411
|
53 "battle_row" "battle"%ROWTYPE;
|
jbe@411
|
54 "rank_ary" INT4[];
|
jbe@411
|
55 "rank_v" INT4;
|
jbe@411
|
56 "initiative_id_v" "initiative"."id"%TYPE;
|
jbe@411
|
57 BEGIN
|
jbe@411
|
58 PERFORM "require_transaction_isolation"();
|
jbe@411
|
59 SELECT * INTO "issue_row"
|
jbe@411
|
60 FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@411
|
61 SELECT * INTO "policy_row"
|
jbe@411
|
62 FROM "policy" WHERE "id" = "issue_row"."policy_id";
|
jbe@411
|
63 SELECT count(1) INTO "dimension_v"
|
jbe@411
|
64 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
|
jbe@411
|
65 -- Create "vote_matrix" with absolute number of votes in pairwise
|
jbe@411
|
66 -- comparison:
|
jbe@411
|
67 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
|
jbe@411
|
68 "i" := 1;
|
jbe@411
|
69 "j" := 2;
|
jbe@411
|
70 FOR "battle_row" IN
|
jbe@411
|
71 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
|
jbe@411
|
72 ORDER BY
|
jbe@411
|
73 "winning_initiative_id" NULLS FIRST,
|
jbe@411
|
74 "losing_initiative_id" NULLS FIRST
|
jbe@411
|
75 LOOP
|
jbe@411
|
76 "vote_matrix"["i"]["j"] := "battle_row"."count";
|
jbe@411
|
77 IF "j" = "dimension_v" THEN
|
jbe@411
|
78 "i" := "i" + 1;
|
jbe@411
|
79 "j" := 1;
|
jbe@411
|
80 ELSE
|
jbe@411
|
81 "j" := "j" + 1;
|
jbe@411
|
82 IF "j" = "i" THEN
|
jbe@411
|
83 "j" := "j" + 1;
|
jbe@411
|
84 END IF;
|
jbe@411
|
85 END IF;
|
jbe@411
|
86 END LOOP;
|
jbe@411
|
87 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
|
jbe@411
|
88 RAISE EXCEPTION 'Wrong battle count (should not happen)';
|
jbe@411
|
89 END IF;
|
jbe@411
|
90 -- Store defeat strengths in "matrix" using "defeat_strength"
|
jbe@411
|
91 -- function:
|
jbe@411
|
92 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
|
jbe@411
|
93 "i" := 1;
|
jbe@411
|
94 LOOP
|
jbe@411
|
95 "j" := 1;
|
jbe@411
|
96 LOOP
|
jbe@411
|
97 IF "i" != "j" THEN
|
jbe@411
|
98 "matrix"["i"]["j"] := "defeat_strength"(
|
jbe@411
|
99 "vote_matrix"["i"]["j"],
|
jbe@411
|
100 "vote_matrix"["j"]["i"]
|
jbe@411
|
101 );
|
jbe@411
|
102 END IF;
|
jbe@411
|
103 EXIT WHEN "j" = "dimension_v";
|
jbe@411
|
104 "j" := "j" + 1;
|
jbe@411
|
105 END LOOP;
|
jbe@411
|
106 EXIT WHEN "i" = "dimension_v";
|
jbe@411
|
107 "i" := "i" + 1;
|
jbe@411
|
108 END LOOP;
|
jbe@411
|
109 -- Find best paths:
|
jbe@411
|
110 "i" := 1;
|
jbe@411
|
111 LOOP
|
jbe@411
|
112 "j" := 1;
|
jbe@411
|
113 LOOP
|
jbe@411
|
114 IF "i" != "j" THEN
|
jbe@411
|
115 "k" := 1;
|
jbe@411
|
116 LOOP
|
jbe@411
|
117 IF "i" != "k" AND "j" != "k" THEN
|
jbe@411
|
118 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
|
jbe@411
|
119 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
|
jbe@411
|
120 "matrix"["j"]["k"] := "matrix"["j"]["i"];
|
jbe@411
|
121 END IF;
|
jbe@411
|
122 ELSE
|
jbe@411
|
123 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
|
jbe@411
|
124 "matrix"["j"]["k"] := "matrix"["i"]["k"];
|
jbe@411
|
125 END IF;
|
jbe@411
|
126 END IF;
|
jbe@411
|
127 END IF;
|
jbe@411
|
128 EXIT WHEN "k" = "dimension_v";
|
jbe@411
|
129 "k" := "k" + 1;
|
jbe@411
|
130 END LOOP;
|
jbe@411
|
131 END IF;
|
jbe@411
|
132 EXIT WHEN "j" = "dimension_v";
|
jbe@411
|
133 "j" := "j" + 1;
|
jbe@411
|
134 END LOOP;
|
jbe@411
|
135 EXIT WHEN "i" = "dimension_v";
|
jbe@411
|
136 "i" := "i" + 1;
|
jbe@411
|
137 END LOOP;
|
jbe@411
|
138 -- Determine order of winners:
|
jbe@411
|
139 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
|
jbe@411
|
140 "rank_v" := 1;
|
jbe@411
|
141 LOOP
|
jbe@411
|
142 "i" := 1;
|
jbe@411
|
143 LOOP
|
jbe@411
|
144 IF "rank_ary"["i"] ISNULL THEN
|
jbe@411
|
145 "j" := 1;
|
jbe@411
|
146 LOOP
|
jbe@411
|
147 IF
|
jbe@411
|
148 "i" != "j" AND
|
jbe@411
|
149 "rank_ary"["j"] ISNULL AND
|
jbe@411
|
150 ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR
|
jbe@411
|
151 -- tie-breaking by "id"
|
jbe@411
|
152 ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND
|
jbe@411
|
153 "j" < "i" ) )
|
jbe@411
|
154 THEN
|
jbe@411
|
155 -- someone else is better
|
jbe@411
|
156 EXIT;
|
jbe@411
|
157 END IF;
|
jbe@411
|
158 "j" := "j" + 1;
|
jbe@411
|
159 IF "j" = "dimension_v" + 1 THEN
|
jbe@411
|
160 -- noone is better
|
jbe@411
|
161 "rank_ary"["i"] := "rank_v";
|
jbe@411
|
162 EXIT;
|
jbe@411
|
163 END IF;
|
jbe@411
|
164 END LOOP;
|
jbe@411
|
165 EXIT WHEN "j" = "dimension_v" + 1;
|
jbe@411
|
166 END IF;
|
jbe@411
|
167 "i" := "i" + 1;
|
jbe@411
|
168 IF "i" > "dimension_v" THEN
|
jbe@411
|
169 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
|
jbe@411
|
170 END IF;
|
jbe@411
|
171 END LOOP;
|
jbe@411
|
172 EXIT WHEN "rank_v" = "dimension_v";
|
jbe@411
|
173 "rank_v" := "rank_v" + 1;
|
jbe@411
|
174 END LOOP;
|
jbe@411
|
175 -- write preliminary results:
|
jbe@411
|
176 "i" := 2; -- omit status quo with "i" = 1
|
jbe@411
|
177 FOR "initiative_id_v" IN
|
jbe@411
|
178 SELECT "id" FROM "initiative"
|
jbe@411
|
179 WHERE "issue_id" = "issue_id_p" AND "admitted"
|
jbe@411
|
180 ORDER BY "id"
|
jbe@411
|
181 LOOP
|
jbe@411
|
182 UPDATE "initiative" SET
|
jbe@411
|
183 "direct_majority" =
|
jbe@411
|
184 CASE WHEN "policy_row"."direct_majority_strict" THEN
|
jbe@411
|
185 "positive_votes" * "policy_row"."direct_majority_den" >
|
jbe@411
|
186 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
|
jbe@411
|
187 ELSE
|
jbe@411
|
188 "positive_votes" * "policy_row"."direct_majority_den" >=
|
jbe@411
|
189 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
|
jbe@411
|
190 END
|
jbe@411
|
191 AND "positive_votes" >= "policy_row"."direct_majority_positive"
|
jbe@411
|
192 AND "issue_row"."voter_count"-"negative_votes" >=
|
jbe@411
|
193 "policy_row"."direct_majority_non_negative",
|
jbe@411
|
194 "indirect_majority" =
|
jbe@411
|
195 CASE WHEN "policy_row"."indirect_majority_strict" THEN
|
jbe@411
|
196 "positive_votes" * "policy_row"."indirect_majority_den" >
|
jbe@411
|
197 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
|
jbe@411
|
198 ELSE
|
jbe@411
|
199 "positive_votes" * "policy_row"."indirect_majority_den" >=
|
jbe@411
|
200 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
|
jbe@411
|
201 END
|
jbe@411
|
202 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
|
jbe@411
|
203 AND "issue_row"."voter_count"-"negative_votes" >=
|
jbe@411
|
204 "policy_row"."indirect_majority_non_negative",
|
jbe@411
|
205 "schulze_rank" = "rank_ary"["i"],
|
jbe@411
|
206 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
|
jbe@411
|
207 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
|
jbe@411
|
208 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
|
jbe@411
|
209 "reverse_beat_path" = "matrix"[1]["i"] >= 0,
|
jbe@411
|
210 "eligible" = FALSE,
|
jbe@411
|
211 "winner" = FALSE,
|
jbe@411
|
212 "rank" = NULL -- NOTE: in cases of manual reset of issue state
|
jbe@411
|
213 WHERE "id" = "initiative_id_v";
|
jbe@411
|
214 "i" := "i" + 1;
|
jbe@411
|
215 END LOOP;
|
jbe@411
|
216 IF "i" != "dimension_v" + 1 THEN
|
jbe@411
|
217 RAISE EXCEPTION 'Wrong winner count (should not happen)';
|
jbe@411
|
218 END IF;
|
jbe@411
|
219 -- take indirect majorities into account:
|
jbe@411
|
220 LOOP
|
jbe@411
|
221 UPDATE "initiative" SET "indirect_majority" = TRUE
|
jbe@411
|
222 FROM (
|
jbe@411
|
223 SELECT "new_initiative"."id" AS "initiative_id"
|
jbe@411
|
224 FROM "initiative" "old_initiative"
|
jbe@411
|
225 JOIN "initiative" "new_initiative"
|
jbe@411
|
226 ON "new_initiative"."issue_id" = "issue_id_p"
|
jbe@411
|
227 AND "new_initiative"."indirect_majority" = FALSE
|
jbe@411
|
228 JOIN "battle" "battle_win"
|
jbe@411
|
229 ON "battle_win"."issue_id" = "issue_id_p"
|
jbe@411
|
230 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
|
jbe@411
|
231 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
|
jbe@411
|
232 JOIN "battle" "battle_lose"
|
jbe@411
|
233 ON "battle_lose"."issue_id" = "issue_id_p"
|
jbe@411
|
234 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
|
jbe@411
|
235 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
|
jbe@411
|
236 WHERE "old_initiative"."issue_id" = "issue_id_p"
|
jbe@411
|
237 AND "old_initiative"."indirect_majority" = TRUE
|
jbe@411
|
238 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
|
jbe@411
|
239 "battle_win"."count" * "policy_row"."indirect_majority_den" >
|
jbe@411
|
240 "policy_row"."indirect_majority_num" *
|
jbe@411
|
241 ("battle_win"."count"+"battle_lose"."count")
|
jbe@411
|
242 ELSE
|
jbe@411
|
243 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
|
jbe@411
|
244 "policy_row"."indirect_majority_num" *
|
jbe@411
|
245 ("battle_win"."count"+"battle_lose"."count")
|
jbe@411
|
246 END
|
jbe@411
|
247 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
|
jbe@411
|
248 AND "issue_row"."voter_count"-"battle_lose"."count" >=
|
jbe@411
|
249 "policy_row"."indirect_majority_non_negative"
|
jbe@411
|
250 ) AS "subquery"
|
jbe@411
|
251 WHERE "id" = "subquery"."initiative_id";
|
jbe@411
|
252 EXIT WHEN NOT FOUND;
|
jbe@411
|
253 END LOOP;
|
jbe@411
|
254 -- set "multistage_majority" for remaining matching initiatives:
|
jbe@411
|
255 UPDATE "initiative" SET "multistage_majority" = TRUE
|
jbe@411
|
256 FROM (
|
jbe@411
|
257 SELECT "losing_initiative"."id" AS "initiative_id"
|
jbe@411
|
258 FROM "initiative" "losing_initiative"
|
jbe@411
|
259 JOIN "initiative" "winning_initiative"
|
jbe@411
|
260 ON "winning_initiative"."issue_id" = "issue_id_p"
|
jbe@411
|
261 AND "winning_initiative"."admitted"
|
jbe@411
|
262 JOIN "battle" "battle_win"
|
jbe@411
|
263 ON "battle_win"."issue_id" = "issue_id_p"
|
jbe@411
|
264 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
|
jbe@411
|
265 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
|
jbe@411
|
266 JOIN "battle" "battle_lose"
|
jbe@411
|
267 ON "battle_lose"."issue_id" = "issue_id_p"
|
jbe@411
|
268 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
|
jbe@411
|
269 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
|
jbe@411
|
270 WHERE "losing_initiative"."issue_id" = "issue_id_p"
|
jbe@411
|
271 AND "losing_initiative"."admitted"
|
jbe@411
|
272 AND "winning_initiative"."schulze_rank" <
|
jbe@411
|
273 "losing_initiative"."schulze_rank"
|
jbe@411
|
274 AND "battle_win"."count" > "battle_lose"."count"
|
jbe@411
|
275 AND (
|
jbe@411
|
276 "battle_win"."count" > "winning_initiative"."positive_votes" OR
|
jbe@411
|
277 "battle_lose"."count" < "losing_initiative"."negative_votes" )
|
jbe@411
|
278 ) AS "subquery"
|
jbe@411
|
279 WHERE "id" = "subquery"."initiative_id";
|
jbe@411
|
280 -- mark eligible initiatives:
|
jbe@411
|
281 UPDATE "initiative" SET "eligible" = TRUE
|
jbe@411
|
282 WHERE "issue_id" = "issue_id_p"
|
jbe@411
|
283 AND "initiative"."direct_majority"
|
jbe@411
|
284 AND "initiative"."indirect_majority"
|
jbe@411
|
285 AND "initiative"."better_than_status_quo"
|
jbe@411
|
286 AND (
|
jbe@411
|
287 "policy_row"."no_multistage_majority" = FALSE OR
|
jbe@411
|
288 "initiative"."multistage_majority" = FALSE )
|
jbe@411
|
289 AND (
|
jbe@411
|
290 "policy_row"."no_reverse_beat_path" = FALSE OR
|
jbe@411
|
291 "initiative"."reverse_beat_path" = FALSE );
|
jbe@411
|
292 -- mark final winner:
|
jbe@411
|
293 UPDATE "initiative" SET "winner" = TRUE
|
jbe@411
|
294 FROM (
|
jbe@411
|
295 SELECT "id" AS "initiative_id"
|
jbe@411
|
296 FROM "initiative"
|
jbe@411
|
297 WHERE "issue_id" = "issue_id_p" AND "eligible"
|
jbe@411
|
298 ORDER BY
|
jbe@411
|
299 "schulze_rank",
|
jbe@411
|
300 "id"
|
jbe@411
|
301 LIMIT 1
|
jbe@411
|
302 ) AS "subquery"
|
jbe@411
|
303 WHERE "id" = "subquery"."initiative_id";
|
jbe@411
|
304 -- write (final) ranks:
|
jbe@411
|
305 "rank_v" := 1;
|
jbe@411
|
306 FOR "initiative_id_v" IN
|
jbe@411
|
307 SELECT "id"
|
jbe@411
|
308 FROM "initiative"
|
jbe@411
|
309 WHERE "issue_id" = "issue_id_p" AND "admitted"
|
jbe@411
|
310 ORDER BY
|
jbe@411
|
311 "winner" DESC,
|
jbe@411
|
312 "eligible" DESC,
|
jbe@411
|
313 "schulze_rank",
|
jbe@411
|
314 "id"
|
jbe@411
|
315 LOOP
|
jbe@411
|
316 UPDATE "initiative" SET "rank" = "rank_v"
|
jbe@411
|
317 WHERE "id" = "initiative_id_v";
|
jbe@411
|
318 "rank_v" := "rank_v" + 1;
|
jbe@411
|
319 END LOOP;
|
jbe@411
|
320 -- set schulze rank of status quo and mark issue as finished:
|
jbe@411
|
321 UPDATE "issue" SET
|
jbe@411
|
322 "status_quo_schulze_rank" = "rank_ary"[1],
|
jbe@411
|
323 "state" =
|
jbe@411
|
324 CASE WHEN EXISTS (
|
jbe@411
|
325 SELECT NULL FROM "initiative"
|
jbe@411
|
326 WHERE "issue_id" = "issue_id_p" AND "winner"
|
jbe@411
|
327 ) THEN
|
jbe@411
|
328 'finished_with_winner'::"issue_state"
|
jbe@411
|
329 ELSE
|
jbe@411
|
330 'finished_without_winner'::"issue_state"
|
jbe@411
|
331 END,
|
jbe@411
|
332 "closed" = "phase_finished",
|
jbe@411
|
333 "phase_finished" = NULL
|
jbe@411
|
334 WHERE "id" = "issue_id_p";
|
jbe@411
|
335 RETURN;
|
jbe@411
|
336 END;
|
jbe@411
|
337 $$;
|
jbe@411
|
338
|
jbe@436
|
339 ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4;
|
jbe@436
|
340
|
jbe@436
|
341 ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
|
jbe@436
|
342 ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
|
jbe@436
|
343 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
|
jbe@436
|
344 ( "first_preference_votes" ISNULL AND
|
jbe@436
|
345 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
|
jbe@436
|
346 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
|
jbe@436
|
347 "schulze_rank" ISNULL AND
|
jbe@436
|
348 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
|
jbe@436
|
349 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
|
jbe@436
|
350 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
|
jbe@436
|
351
|
jbe@436
|
352 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
|
jbe@436
|
353 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
|
jbe@436
|
354 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
|
jbe@436
|
355
|
jbe@436
|
356 -- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL; -- should not be necessary
|
jbe@436
|
357 ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL;
|
jbe@436
|
358
|
jbe@436
|
359 ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN;
|
jbe@436
|
360
|
jbe@436
|
361 ALTER TABLE "vote" ADD
|
jbe@436
|
362 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
|
jbe@436
|
363 CHECK ("grade" > 0 OR "first_preference" ISNULL);
|
jbe@436
|
364
|
jbe@436
|
365 COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.';
|
jbe@436
|
366
|
jbe@436
|
367 INSERT INTO "temporary_transaction_data" ("key", "value")
|
jbe@436
|
368 VALUES ('override_protection_triggers', TRUE::TEXT);
|
jbe@436
|
369
|
jbe@436
|
370 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
|
jbe@436
|
371 FROM (
|
jbe@436
|
372 SELECT
|
jbe@436
|
373 "vote"."initiative_id",
|
jbe@436
|
374 "vote"."member_id",
|
jbe@436
|
375 CASE WHEN "vote"."grade" > 0 THEN
|
jbe@436
|
376 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
|
jbe@436
|
377 ELSE NULL
|
jbe@436
|
378 END AS "first_preference"
|
jbe@436
|
379 FROM "vote"
|
jbe@436
|
380 JOIN "initiative" -- NOTE: due to missing index on issue_id
|
jbe@436
|
381 ON "vote"."issue_id" = "initiative"."issue_id"
|
jbe@436
|
382 JOIN "vote" AS "agg"
|
jbe@436
|
383 ON "initiative"."id" = "agg"."initiative_id"
|
jbe@436
|
384 AND "vote"."member_id" = "agg"."member_id"
|
jbe@436
|
385 GROUP BY "vote"."initiative_id", "vote"."member_id"
|
jbe@436
|
386 ) AS "subquery"
|
jbe@436
|
387 WHERE "vote"."initiative_id" = "subquery"."initiative_id"
|
jbe@436
|
388 AND "vote"."member_id" = "subquery"."member_id";
|
jbe@436
|
389
|
jbe@436
|
390 DELETE FROM "temporary_transaction_data"
|
jbe@436
|
391 WHERE "key" = 'override_protection_triggers';
|
jbe@436
|
392
|
jbe@436
|
393 UPDATE "initiative"
|
jbe@436
|
394 SET "first_preference_votes" = coalesce("subquery"."sum", 0)
|
jbe@436
|
395 FROM (
|
jbe@436
|
396 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
|
jbe@436
|
397 FROM "vote" JOIN "direct_voter"
|
jbe@436
|
398 ON "vote"."issue_id" = "direct_voter"."issue_id"
|
jbe@436
|
399 AND "vote"."member_id" = "direct_voter"."member_id"
|
jbe@436
|
400 WHERE "vote"."first_preference"
|
jbe@436
|
401 GROUP BY "vote"."initiative_id"
|
jbe@436
|
402 ) AS "subquery"
|
jbe@436
|
403 WHERE "initiative"."admitted"
|
jbe@436
|
404 AND "initiative"."id" = "subquery"."initiative_id";
|
jbe@436
|
405
|
jbe@436
|
406 -- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0)
|
jbe@436
|
407 -- to avoid future data loss when executing "clean_issue" to delete voting data:
|
jbe@436
|
408 INSERT INTO "battle" (
|
jbe@436
|
409 "issue_id",
|
jbe@436
|
410 "winning_initiative_id",
|
jbe@436
|
411 "losing_initiative_id",
|
jbe@436
|
412 "count"
|
jbe@436
|
413 ) SELECT
|
jbe@436
|
414 "battle_view"."issue_id",
|
jbe@436
|
415 "battle_view"."winning_initiative_id",
|
jbe@436
|
416 "battle_view"."losing_initiative_id",
|
jbe@436
|
417 "battle_view"."count"
|
jbe@436
|
418 FROM (
|
jbe@436
|
419 SELECT
|
jbe@436
|
420 "issue"."id" AS "issue_id",
|
jbe@436
|
421 "winning_initiative"."id" AS "winning_initiative_id",
|
jbe@436
|
422 "losing_initiative"."id" AS "losing_initiative_id",
|
jbe@436
|
423 sum(
|
jbe@436
|
424 CASE WHEN
|
jbe@436
|
425 coalesce("better_vote"."grade", 0) >
|
jbe@436
|
426 coalesce("worse_vote"."grade", 0)
|
jbe@436
|
427 THEN "direct_voter"."weight" ELSE 0 END
|
jbe@436
|
428 ) AS "count"
|
jbe@436
|
429 FROM "issue"
|
jbe@436
|
430 LEFT JOIN "direct_voter"
|
jbe@436
|
431 ON "issue"."id" = "direct_voter"."issue_id"
|
jbe@436
|
432 JOIN "battle_participant" AS "winning_initiative"
|
jbe@436
|
433 ON "issue"."id" = "winning_initiative"."issue_id"
|
jbe@436
|
434 JOIN "battle_participant" AS "losing_initiative"
|
jbe@436
|
435 ON "issue"."id" = "losing_initiative"."issue_id"
|
jbe@436
|
436 LEFT JOIN "vote" AS "better_vote"
|
jbe@436
|
437 ON "direct_voter"."member_id" = "better_vote"."member_id"
|
jbe@436
|
438 AND "winning_initiative"."id" = "better_vote"."initiative_id"
|
jbe@436
|
439 LEFT JOIN "vote" AS "worse_vote"
|
jbe@436
|
440 ON "direct_voter"."member_id" = "worse_vote"."member_id"
|
jbe@436
|
441 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
|
jbe@436
|
442 WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner')
|
jbe@436
|
443 AND "winning_initiative"."id" != "losing_initiative"."id"
|
jbe@436
|
444 -- NOTE: comparisons with status-quo are intentionally omitted to mark
|
jbe@436
|
445 -- issues that were counted prior LiquidFeedback Core v2.0.0
|
jbe@436
|
446 GROUP BY
|
jbe@436
|
447 "issue"."id",
|
jbe@436
|
448 "winning_initiative"."id",
|
jbe@436
|
449 "losing_initiative"."id"
|
jbe@436
|
450 ) AS "battle_view"
|
jbe@436
|
451 LEFT JOIN "battle"
|
jbe@436
|
452 ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id"
|
jbe@436
|
453 AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id"
|
jbe@436
|
454 WHERE "battle" ISNULL;
|
jbe@436
|
455
|
jbe@436
|
456 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
|
jbe@436
|
457 RETURNS VOID
|
jbe@436
|
458 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@436
|
459 DECLARE
|
jbe@436
|
460 "area_id_v" "area"."id"%TYPE;
|
jbe@436
|
461 "unit_id_v" "unit"."id"%TYPE;
|
jbe@436
|
462 "member_id_v" "member"."id"%TYPE;
|
jbe@436
|
463 BEGIN
|
jbe@436
|
464 PERFORM "require_transaction_isolation"();
|
jbe@436
|
465 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@436
|
466 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
|
jbe@436
|
467 -- override protection triggers:
|
jbe@436
|
468 INSERT INTO "temporary_transaction_data" ("key", "value")
|
jbe@436
|
469 VALUES ('override_protection_triggers', TRUE::TEXT);
|
jbe@436
|
470 -- delete timestamp of voting comment:
|
jbe@436
|
471 UPDATE "direct_voter" SET "comment_changed" = NULL
|
jbe@436
|
472 WHERE "issue_id" = "issue_id_p";
|
jbe@436
|
473 -- delete delegating votes (in cases of manual reset of issue state):
|
jbe@436
|
474 DELETE FROM "delegating_voter"
|
jbe@436
|
475 WHERE "issue_id" = "issue_id_p";
|
jbe@436
|
476 -- delete votes from non-privileged voters:
|
jbe@436
|
477 DELETE FROM "direct_voter"
|
jbe@436
|
478 USING (
|
jbe@436
|
479 SELECT
|
jbe@436
|
480 "direct_voter"."member_id"
|
jbe@436
|
481 FROM "direct_voter"
|
jbe@436
|
482 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
|
jbe@436
|
483 LEFT JOIN "privilege"
|
jbe@436
|
484 ON "privilege"."unit_id" = "unit_id_v"
|
jbe@436
|
485 AND "privilege"."member_id" = "direct_voter"."member_id"
|
jbe@436
|
486 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
|
jbe@436
|
487 "member"."active" = FALSE OR
|
jbe@436
|
488 "privilege"."voting_right" ISNULL OR
|
jbe@436
|
489 "privilege"."voting_right" = FALSE
|
jbe@436
|
490 )
|
jbe@436
|
491 ) AS "subquery"
|
jbe@436
|
492 WHERE "direct_voter"."issue_id" = "issue_id_p"
|
jbe@436
|
493 AND "direct_voter"."member_id" = "subquery"."member_id";
|
jbe@436
|
494 -- consider delegations:
|
jbe@436
|
495 UPDATE "direct_voter" SET "weight" = 1
|
jbe@436
|
496 WHERE "issue_id" = "issue_id_p";
|
jbe@436
|
497 PERFORM "add_vote_delegations"("issue_id_p");
|
jbe@436
|
498 -- mark first preferences:
|
jbe@436
|
499 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
|
jbe@436
|
500 FROM (
|
jbe@436
|
501 SELECT
|
jbe@436
|
502 "vote"."initiative_id",
|
jbe@436
|
503 "vote"."member_id",
|
jbe@436
|
504 CASE WHEN "vote"."grade" > 0 THEN
|
jbe@436
|
505 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
|
jbe@436
|
506 ELSE NULL
|
jbe@436
|
507 END AS "first_preference"
|
jbe@436
|
508 FROM "vote"
|
jbe@436
|
509 JOIN "initiative" -- NOTE: due to missing index on issue_id
|
jbe@436
|
510 ON "vote"."issue_id" = "initiative"."issue_id"
|
jbe@436
|
511 JOIN "vote" AS "agg"
|
jbe@436
|
512 ON "initiative"."id" = "agg"."initiative_id"
|
jbe@436
|
513 AND "vote"."member_id" = "agg"."member_id"
|
jbe@436
|
514 GROUP BY "vote"."initiative_id", "vote"."member_id"
|
jbe@436
|
515 ) AS "subquery"
|
jbe@436
|
516 WHERE "vote"."issue_id" = "issue_id_p"
|
jbe@436
|
517 AND "vote"."initiative_id" = "subquery"."initiative_id"
|
jbe@436
|
518 AND "vote"."member_id" = "subquery"."member_id";
|
jbe@436
|
519 -- finish overriding protection triggers (avoids garbage):
|
jbe@436
|
520 DELETE FROM "temporary_transaction_data"
|
jbe@436
|
521 WHERE "key" = 'override_protection_triggers';
|
jbe@436
|
522 -- materialize battle_view:
|
jbe@436
|
523 -- NOTE: "closed" column of issue must be set at this point
|
jbe@436
|
524 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
jbe@436
|
525 INSERT INTO "battle" (
|
jbe@436
|
526 "issue_id",
|
jbe@436
|
527 "winning_initiative_id", "losing_initiative_id",
|
jbe@436
|
528 "count"
|
jbe@436
|
529 ) SELECT
|
jbe@436
|
530 "issue_id",
|
jbe@436
|
531 "winning_initiative_id", "losing_initiative_id",
|
jbe@436
|
532 "count"
|
jbe@436
|
533 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
|
jbe@436
|
534 -- set voter count:
|
jbe@436
|
535 UPDATE "issue" SET
|
jbe@436
|
536 "voter_count" = (
|
jbe@436
|
537 SELECT coalesce(sum("weight"), 0)
|
jbe@436
|
538 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
|
jbe@436
|
539 )
|
jbe@436
|
540 WHERE "id" = "issue_id_p";
|
jbe@436
|
541 -- calculate "first_preference_votes":
|
jbe@436
|
542 UPDATE "initiative"
|
jbe@436
|
543 SET "first_preference_votes" = coalesce("subquery"."sum", 0)
|
jbe@436
|
544 FROM (
|
jbe@436
|
545 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
|
jbe@436
|
546 FROM "vote" JOIN "direct_voter"
|
jbe@436
|
547 ON "vote"."issue_id" = "direct_voter"."issue_id"
|
jbe@436
|
548 AND "vote"."member_id" = "direct_voter"."member_id"
|
jbe@436
|
549 WHERE "vote"."first_preference"
|
jbe@436
|
550 GROUP BY "vote"."initiative_id"
|
jbe@436
|
551 ) AS "subquery"
|
jbe@436
|
552 WHERE "initiative"."issue_id" = "issue_id_p"
|
jbe@436
|
553 AND "initiative"."admitted"
|
jbe@436
|
554 AND "initiative"."id" = "subquery"."initiative_id";
|
jbe@436
|
555 -- copy "positive_votes" and "negative_votes" from "battle" table:
|
jbe@436
|
556 UPDATE "initiative" SET
|
jbe@436
|
557 "positive_votes" = "battle_win"."count",
|
jbe@436
|
558 "negative_votes" = "battle_lose"."count"
|
jbe@436
|
559 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
|
jbe@436
|
560 WHERE
|
jbe@436
|
561 "battle_win"."issue_id" = "issue_id_p" AND
|
jbe@436
|
562 "battle_win"."winning_initiative_id" = "initiative"."id" AND
|
jbe@436
|
563 "battle_win"."losing_initiative_id" ISNULL AND
|
jbe@436
|
564 "battle_lose"."issue_id" = "issue_id_p" AND
|
jbe@436
|
565 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
|
jbe@436
|
566 "battle_lose"."winning_initiative_id" ISNULL;
|
jbe@436
|
567 END;
|
jbe@436
|
568 $$;
|
jbe@436
|
569
|
jbe@396
|
570 COMMIT;
|