liquid_feedback_core
view update/core-update.v2.2.5-v3.0.0.sql @ 491:aa94c7dbb20f
New function "get_initiatives_for_notification"
| author | jbe |
|---|---|
| date | Sun Apr 03 19:42:09 2016 +0200 (2016-04-03) |
| parents | 399dc1a86398 |
| children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('3.0.0', 3, 0, 0))
5 AS "subquery"("string", "major", "minor", "revision");
7 CREATE TABLE "issue_order_in_admission_state" (
8 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
9 "order_in_area" INT4,
10 "order_in_unit" INT4 );
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"';
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';
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';
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';
18 CREATE VIEW "issue_supporter_in_admission_state" AS
19 SELECT DISTINCT
20 "area"."unit_id",
21 "issue"."area_id",
22 "issue"."id" AS "issue_id",
23 "supporter"."member_id",
24 "direct_interest_snapshot"."weight"
25 FROM "issue"
26 JOIN "area" ON "area"."id" = "issue"."area_id"
27 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
28 JOIN "direct_interest_snapshot"
29 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
30 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
31 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
32 WHERE "issue"."state" = 'admission'::"issue_state";
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';
36 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';
37 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
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)';
39 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';
41 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
42 RETURNS VOID
43 LANGUAGE 'plpgsql' VOLATILE AS $$
44 DECLARE
45 "issue_row" "issue"%ROWTYPE;
46 "policy_row" "policy"%ROWTYPE;
47 "dimension_v" INTEGER;
48 "vote_matrix" INT4[][]; -- absolute votes
49 "matrix" INT8[][]; -- defeat strength / best paths
50 "i" INTEGER;
51 "j" INTEGER;
52 "k" INTEGER;
53 "battle_row" "battle"%ROWTYPE;
54 "rank_ary" INT4[];
55 "rank_v" INT4;
56 "initiative_id_v" "initiative"."id"%TYPE;
57 BEGIN
58 PERFORM "require_transaction_isolation"();
59 SELECT * INTO "issue_row"
60 FROM "issue" WHERE "id" = "issue_id_p";
61 SELECT * INTO "policy_row"
62 FROM "policy" WHERE "id" = "issue_row"."policy_id";
63 SELECT count(1) INTO "dimension_v"
64 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
65 -- Create "vote_matrix" with absolute number of votes in pairwise
66 -- comparison:
67 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
68 "i" := 1;
69 "j" := 2;
70 FOR "battle_row" IN
71 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
72 ORDER BY
73 "winning_initiative_id" NULLS FIRST,
74 "losing_initiative_id" NULLS FIRST
75 LOOP
76 "vote_matrix"["i"]["j"] := "battle_row"."count";
77 IF "j" = "dimension_v" THEN
78 "i" := "i" + 1;
79 "j" := 1;
80 ELSE
81 "j" := "j" + 1;
82 IF "j" = "i" THEN
83 "j" := "j" + 1;
84 END IF;
85 END IF;
86 END LOOP;
87 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
88 RAISE EXCEPTION 'Wrong battle count (should not happen)';
89 END IF;
90 -- Store defeat strengths in "matrix" using "defeat_strength"
91 -- function:
92 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
93 "i" := 1;
94 LOOP
95 "j" := 1;
96 LOOP
97 IF "i" != "j" THEN
98 "matrix"["i"]["j"] := "defeat_strength"(
99 "vote_matrix"["i"]["j"],
100 "vote_matrix"["j"]["i"]
101 );
102 END IF;
103 EXIT WHEN "j" = "dimension_v";
104 "j" := "j" + 1;
105 END LOOP;
106 EXIT WHEN "i" = "dimension_v";
107 "i" := "i" + 1;
108 END LOOP;
109 -- Find best paths:
110 "i" := 1;
111 LOOP
112 "j" := 1;
113 LOOP
114 IF "i" != "j" THEN
115 "k" := 1;
116 LOOP
117 IF "i" != "k" AND "j" != "k" THEN
118 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
119 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
120 "matrix"["j"]["k"] := "matrix"["j"]["i"];
121 END IF;
122 ELSE
123 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
124 "matrix"["j"]["k"] := "matrix"["i"]["k"];
125 END IF;
126 END IF;
127 END IF;
128 EXIT WHEN "k" = "dimension_v";
129 "k" := "k" + 1;
130 END LOOP;
131 END IF;
132 EXIT WHEN "j" = "dimension_v";
133 "j" := "j" + 1;
134 END LOOP;
135 EXIT WHEN "i" = "dimension_v";
136 "i" := "i" + 1;
137 END LOOP;
138 -- Determine order of winners:
139 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
140 "rank_v" := 1;
141 LOOP
142 "i" := 1;
143 LOOP
144 IF "rank_ary"["i"] ISNULL THEN
145 "j" := 1;
146 LOOP
147 IF
148 "i" != "j" AND
149 "rank_ary"["j"] ISNULL AND
150 ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR
151 -- tie-breaking by "id"
152 ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND
153 "j" < "i" ) )
154 THEN
155 -- someone else is better
156 EXIT;
157 END IF;
158 "j" := "j" + 1;
159 IF "j" = "dimension_v" + 1 THEN
160 -- noone is better
161 "rank_ary"["i"] := "rank_v";
162 EXIT;
163 END IF;
164 END LOOP;
165 EXIT WHEN "j" = "dimension_v" + 1;
166 END IF;
167 "i" := "i" + 1;
168 IF "i" > "dimension_v" THEN
169 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
170 END IF;
171 END LOOP;
172 EXIT WHEN "rank_v" = "dimension_v";
173 "rank_v" := "rank_v" + 1;
174 END LOOP;
175 -- write preliminary results:
176 "i" := 2; -- omit status quo with "i" = 1
177 FOR "initiative_id_v" IN
178 SELECT "id" FROM "initiative"
179 WHERE "issue_id" = "issue_id_p" AND "admitted"
180 ORDER BY "id"
181 LOOP
182 UPDATE "initiative" SET
183 "direct_majority" =
184 CASE WHEN "policy_row"."direct_majority_strict" THEN
185 "positive_votes" * "policy_row"."direct_majority_den" >
186 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
187 ELSE
188 "positive_votes" * "policy_row"."direct_majority_den" >=
189 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
190 END
191 AND "positive_votes" >= "policy_row"."direct_majority_positive"
192 AND "issue_row"."voter_count"-"negative_votes" >=
193 "policy_row"."direct_majority_non_negative",
194 "indirect_majority" =
195 CASE WHEN "policy_row"."indirect_majority_strict" THEN
196 "positive_votes" * "policy_row"."indirect_majority_den" >
197 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
198 ELSE
199 "positive_votes" * "policy_row"."indirect_majority_den" >=
200 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
201 END
202 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
203 AND "issue_row"."voter_count"-"negative_votes" >=
204 "policy_row"."indirect_majority_non_negative",
205 "schulze_rank" = "rank_ary"["i"],
206 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
207 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
208 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
209 "reverse_beat_path" = "matrix"[1]["i"] >= 0,
210 "eligible" = FALSE,
211 "winner" = FALSE,
212 "rank" = NULL -- NOTE: in cases of manual reset of issue state
213 WHERE "id" = "initiative_id_v";
214 "i" := "i" + 1;
215 END LOOP;
216 IF "i" != "dimension_v" + 1 THEN
217 RAISE EXCEPTION 'Wrong winner count (should not happen)';
218 END IF;
219 -- take indirect majorities into account:
220 LOOP
221 UPDATE "initiative" SET "indirect_majority" = TRUE
222 FROM (
223 SELECT "new_initiative"."id" AS "initiative_id"
224 FROM "initiative" "old_initiative"
225 JOIN "initiative" "new_initiative"
226 ON "new_initiative"."issue_id" = "issue_id_p"
227 AND "new_initiative"."indirect_majority" = FALSE
228 JOIN "battle" "battle_win"
229 ON "battle_win"."issue_id" = "issue_id_p"
230 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
231 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
232 JOIN "battle" "battle_lose"
233 ON "battle_lose"."issue_id" = "issue_id_p"
234 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
235 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
236 WHERE "old_initiative"."issue_id" = "issue_id_p"
237 AND "old_initiative"."indirect_majority" = TRUE
238 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
239 "battle_win"."count" * "policy_row"."indirect_majority_den" >
240 "policy_row"."indirect_majority_num" *
241 ("battle_win"."count"+"battle_lose"."count")
242 ELSE
243 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
244 "policy_row"."indirect_majority_num" *
245 ("battle_win"."count"+"battle_lose"."count")
246 END
247 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
248 AND "issue_row"."voter_count"-"battle_lose"."count" >=
249 "policy_row"."indirect_majority_non_negative"
250 ) AS "subquery"
251 WHERE "id" = "subquery"."initiative_id";
252 EXIT WHEN NOT FOUND;
253 END LOOP;
254 -- set "multistage_majority" for remaining matching initiatives:
255 UPDATE "initiative" SET "multistage_majority" = TRUE
256 FROM (
257 SELECT "losing_initiative"."id" AS "initiative_id"
258 FROM "initiative" "losing_initiative"
259 JOIN "initiative" "winning_initiative"
260 ON "winning_initiative"."issue_id" = "issue_id_p"
261 AND "winning_initiative"."admitted"
262 JOIN "battle" "battle_win"
263 ON "battle_win"."issue_id" = "issue_id_p"
264 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
265 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
266 JOIN "battle" "battle_lose"
267 ON "battle_lose"."issue_id" = "issue_id_p"
268 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
269 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
270 WHERE "losing_initiative"."issue_id" = "issue_id_p"
271 AND "losing_initiative"."admitted"
272 AND "winning_initiative"."schulze_rank" <
273 "losing_initiative"."schulze_rank"
274 AND "battle_win"."count" > "battle_lose"."count"
275 AND (
276 "battle_win"."count" > "winning_initiative"."positive_votes" OR
277 "battle_lose"."count" < "losing_initiative"."negative_votes" )
278 ) AS "subquery"
279 WHERE "id" = "subquery"."initiative_id";
280 -- mark eligible initiatives:
281 UPDATE "initiative" SET "eligible" = TRUE
282 WHERE "issue_id" = "issue_id_p"
283 AND "initiative"."direct_majority"
284 AND "initiative"."indirect_majority"
285 AND "initiative"."better_than_status_quo"
286 AND (
287 "policy_row"."no_multistage_majority" = FALSE OR
288 "initiative"."multistage_majority" = FALSE )
289 AND (
290 "policy_row"."no_reverse_beat_path" = FALSE OR
291 "initiative"."reverse_beat_path" = FALSE );
292 -- mark final winner:
293 UPDATE "initiative" SET "winner" = TRUE
294 FROM (
295 SELECT "id" AS "initiative_id"
296 FROM "initiative"
297 WHERE "issue_id" = "issue_id_p" AND "eligible"
298 ORDER BY
299 "schulze_rank",
300 "id"
301 LIMIT 1
302 ) AS "subquery"
303 WHERE "id" = "subquery"."initiative_id";
304 -- write (final) ranks:
305 "rank_v" := 1;
306 FOR "initiative_id_v" IN
307 SELECT "id"
308 FROM "initiative"
309 WHERE "issue_id" = "issue_id_p" AND "admitted"
310 ORDER BY
311 "winner" DESC,
312 "eligible" DESC,
313 "schulze_rank",
314 "id"
315 LOOP
316 UPDATE "initiative" SET "rank" = "rank_v"
317 WHERE "id" = "initiative_id_v";
318 "rank_v" := "rank_v" + 1;
319 END LOOP;
320 -- set schulze rank of status quo and mark issue as finished:
321 UPDATE "issue" SET
322 "status_quo_schulze_rank" = "rank_ary"[1],
323 "state" =
324 CASE WHEN EXISTS (
325 SELECT NULL FROM "initiative"
326 WHERE "issue_id" = "issue_id_p" AND "winner"
327 ) THEN
328 'finished_with_winner'::"issue_state"
329 ELSE
330 'finished_without_winner'::"issue_state"
331 END,
332 "closed" = "phase_finished",
333 "phase_finished" = NULL
334 WHERE "id" = "issue_id_p";
335 RETURN;
336 END;
337 $$;
339 COMMIT;