liquid_feedback_core
view update/core-update.v2.0.2-v2.0.3.sql @ 386:0b0bfcf2e60c
Added tag v2.2.3 for changeset e474e9e1240a
| author | jbe |
|---|---|
| date | Sat Mar 23 13:11:08 2013 +0100 (2013-03-23) |
| parents | 2571bb3bdf56 |
| children |
line source
1 BEGIN;
3 -- update version number:
4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
5 SELECT * FROM (VALUES ('2.0.3', 2, 0, 3))
6 AS "subquery"("string", "major", "minor", "revision");
8 -- update function "calculate_ranks"("issue"."id")
9 -- regarding setting "eligible"=FALSE for non-eligible admitted initiatives
10 -- and regarding "rank" ordering:
11 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
12 RETURNS VOID
13 LANGUAGE 'plpgsql' VOLATILE AS $$
14 DECLARE
15 "issue_row" "issue"%ROWTYPE;
16 "policy_row" "policy"%ROWTYPE;
17 "dimension_v" INTEGER;
18 "vote_matrix" INT4[][]; -- absolute votes
19 "matrix" INT8[][]; -- defeat strength / best paths
20 "i" INTEGER;
21 "j" INTEGER;
22 "k" INTEGER;
23 "battle_row" "battle"%ROWTYPE;
24 "rank_ary" INT4[];
25 "rank_v" INT4;
26 "done_v" INTEGER;
27 "winners_ary" INTEGER[];
28 "initiative_id_v" "initiative"."id"%TYPE;
29 BEGIN
30 SELECT * INTO "issue_row"
31 FROM "issue" WHERE "id" = "issue_id_p"
32 FOR UPDATE;
33 SELECT * INTO "policy_row"
34 FROM "policy" WHERE "id" = "issue_row"."policy_id";
35 SELECT count(1) INTO "dimension_v"
36 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
37 -- Create "vote_matrix" with absolute number of votes in pairwise
38 -- comparison:
39 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
40 "i" := 1;
41 "j" := 2;
42 FOR "battle_row" IN
43 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
44 ORDER BY
45 "winning_initiative_id" NULLS LAST,
46 "losing_initiative_id" NULLS LAST
47 LOOP
48 "vote_matrix"["i"]["j"] := "battle_row"."count";
49 IF "j" = "dimension_v" THEN
50 "i" := "i" + 1;
51 "j" := 1;
52 ELSE
53 "j" := "j" + 1;
54 IF "j" = "i" THEN
55 "j" := "j" + 1;
56 END IF;
57 END IF;
58 END LOOP;
59 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
60 RAISE EXCEPTION 'Wrong battle count (should not happen)';
61 END IF;
62 -- Store defeat strengths in "matrix" using "defeat_strength"
63 -- function:
64 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
65 "i" := 1;
66 LOOP
67 "j" := 1;
68 LOOP
69 IF "i" != "j" THEN
70 "matrix"["i"]["j"] := "defeat_strength"(
71 "vote_matrix"["i"]["j"],
72 "vote_matrix"["j"]["i"]
73 );
74 END IF;
75 EXIT WHEN "j" = "dimension_v";
76 "j" := "j" + 1;
77 END LOOP;
78 EXIT WHEN "i" = "dimension_v";
79 "i" := "i" + 1;
80 END LOOP;
81 -- Find best paths:
82 "i" := 1;
83 LOOP
84 "j" := 1;
85 LOOP
86 IF "i" != "j" THEN
87 "k" := 1;
88 LOOP
89 IF "i" != "k" AND "j" != "k" THEN
90 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
91 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
92 "matrix"["j"]["k"] := "matrix"["j"]["i"];
93 END IF;
94 ELSE
95 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
96 "matrix"["j"]["k"] := "matrix"["i"]["k"];
97 END IF;
98 END IF;
99 END IF;
100 EXIT WHEN "k" = "dimension_v";
101 "k" := "k" + 1;
102 END LOOP;
103 END IF;
104 EXIT WHEN "j" = "dimension_v";
105 "j" := "j" + 1;
106 END LOOP;
107 EXIT WHEN "i" = "dimension_v";
108 "i" := "i" + 1;
109 END LOOP;
110 -- Determine order of winners:
111 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
112 "rank_v" := 1;
113 "done_v" := 0;
114 LOOP
115 "winners_ary" := '{}';
116 "i" := 1;
117 LOOP
118 IF "rank_ary"["i"] ISNULL THEN
119 "j" := 1;
120 LOOP
121 IF
122 "i" != "j" AND
123 "rank_ary"["j"] ISNULL AND
124 "matrix"["j"]["i"] > "matrix"["i"]["j"]
125 THEN
126 -- someone else is better
127 EXIT;
128 END IF;
129 IF "j" = "dimension_v" THEN
130 -- noone is better
131 "winners_ary" := "winners_ary" || "i";
132 EXIT;
133 END IF;
134 "j" := "j" + 1;
135 END LOOP;
136 END IF;
137 EXIT WHEN "i" = "dimension_v";
138 "i" := "i" + 1;
139 END LOOP;
140 "i" := 1;
141 LOOP
142 "rank_ary"["winners_ary"["i"]] := "rank_v";
143 "done_v" := "done_v" + 1;
144 EXIT WHEN "i" = array_upper("winners_ary", 1);
145 "i" := "i" + 1;
146 END LOOP;
147 EXIT WHEN "done_v" = "dimension_v";
148 "rank_v" := "rank_v" + 1;
149 END LOOP;
150 -- write preliminary results:
151 "i" := 1;
152 FOR "initiative_id_v" IN
153 SELECT "id" FROM "initiative"
154 WHERE "issue_id" = "issue_id_p" AND "admitted"
155 ORDER BY "id"
156 LOOP
157 UPDATE "initiative" SET
158 "direct_majority" =
159 CASE WHEN "policy_row"."direct_majority_strict" THEN
160 "positive_votes" * "policy_row"."direct_majority_den" >
161 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
162 ELSE
163 "positive_votes" * "policy_row"."direct_majority_den" >=
164 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
165 END
166 AND "positive_votes" >= "policy_row"."direct_majority_positive"
167 AND "issue_row"."voter_count"-"negative_votes" >=
168 "policy_row"."direct_majority_non_negative",
169 "indirect_majority" =
170 CASE WHEN "policy_row"."indirect_majority_strict" THEN
171 "positive_votes" * "policy_row"."indirect_majority_den" >
172 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
173 ELSE
174 "positive_votes" * "policy_row"."indirect_majority_den" >=
175 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
176 END
177 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
178 AND "issue_row"."voter_count"-"negative_votes" >=
179 "policy_row"."indirect_majority_non_negative",
180 "schulze_rank" = "rank_ary"["i"],
181 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
182 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
183 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
184 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
185 "eligible" = FALSE,
186 "winner" = FALSE
187 WHERE "id" = "initiative_id_v";
188 "i" := "i" + 1;
189 END LOOP;
190 IF "i" != "dimension_v" THEN
191 RAISE EXCEPTION 'Wrong winner count (should not happen)';
192 END IF;
193 -- take indirect majorities into account:
194 LOOP
195 UPDATE "initiative" SET "indirect_majority" = TRUE
196 FROM (
197 SELECT "new_initiative"."id" AS "initiative_id"
198 FROM "initiative" "old_initiative"
199 JOIN "initiative" "new_initiative"
200 ON "new_initiative"."issue_id" = "issue_id_p"
201 AND "new_initiative"."indirect_majority" = FALSE
202 JOIN "battle" "battle_win"
203 ON "battle_win"."issue_id" = "issue_id_p"
204 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
205 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
206 JOIN "battle" "battle_lose"
207 ON "battle_lose"."issue_id" = "issue_id_p"
208 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
209 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
210 WHERE "old_initiative"."issue_id" = "issue_id_p"
211 AND "old_initiative"."indirect_majority" = TRUE
212 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
213 "battle_win"."count" * "policy_row"."indirect_majority_den" >
214 "policy_row"."indirect_majority_num" *
215 ("battle_win"."count"+"battle_lose"."count")
216 ELSE
217 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
218 "policy_row"."indirect_majority_num" *
219 ("battle_win"."count"+"battle_lose"."count")
220 END
221 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
222 AND "issue_row"."voter_count"-"battle_lose"."count" >=
223 "policy_row"."indirect_majority_non_negative"
224 ) AS "subquery"
225 WHERE "id" = "subquery"."initiative_id";
226 EXIT WHEN NOT FOUND;
227 END LOOP;
228 -- set "multistage_majority" for remaining matching initiatives:
229 UPDATE "initiative" SET "multistage_majority" = TRUE
230 FROM (
231 SELECT "losing_initiative"."id" AS "initiative_id"
232 FROM "initiative" "losing_initiative"
233 JOIN "initiative" "winning_initiative"
234 ON "winning_initiative"."issue_id" = "issue_id_p"
235 AND "winning_initiative"."admitted"
236 JOIN "battle" "battle_win"
237 ON "battle_win"."issue_id" = "issue_id_p"
238 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
239 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
240 JOIN "battle" "battle_lose"
241 ON "battle_lose"."issue_id" = "issue_id_p"
242 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
243 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
244 WHERE "losing_initiative"."issue_id" = "issue_id_p"
245 AND "losing_initiative"."admitted"
246 AND "winning_initiative"."schulze_rank" <
247 "losing_initiative"."schulze_rank"
248 AND "battle_win"."count" > "battle_lose"."count"
249 AND (
250 "battle_win"."count" > "winning_initiative"."positive_votes" OR
251 "battle_lose"."count" < "losing_initiative"."negative_votes" )
252 ) AS "subquery"
253 WHERE "id" = "subquery"."initiative_id";
254 -- mark eligible initiatives:
255 UPDATE "initiative" SET "eligible" = TRUE
256 WHERE "issue_id" = "issue_id_p"
257 AND "initiative"."direct_majority"
258 AND "initiative"."indirect_majority"
259 AND "initiative"."better_than_status_quo"
260 AND (
261 "policy_row"."no_multistage_majority" = FALSE OR
262 "initiative"."multistage_majority" = FALSE )
263 AND (
264 "policy_row"."no_reverse_beat_path" = FALSE OR
265 "initiative"."reverse_beat_path" = FALSE );
266 -- mark final winner:
267 UPDATE "initiative" SET "winner" = TRUE
268 FROM (
269 SELECT "id" AS "initiative_id"
270 FROM "initiative"
271 WHERE "issue_id" = "issue_id_p" AND "eligible"
272 ORDER BY
273 "schulze_rank",
274 "vote_ratio"("positive_votes", "negative_votes"),
275 "id"
276 LIMIT 1
277 ) AS "subquery"
278 WHERE "id" = "subquery"."initiative_id";
279 -- write (final) ranks:
280 "rank_v" := 1;
281 FOR "initiative_id_v" IN
282 SELECT "id"
283 FROM "initiative"
284 WHERE "issue_id" = "issue_id_p" AND "admitted"
285 ORDER BY
286 "winner" DESC,
287 "eligible" DESC,
288 "schulze_rank",
289 "vote_ratio"("positive_votes", "negative_votes"),
290 "id"
291 LOOP
292 UPDATE "initiative" SET "rank" = "rank_v"
293 WHERE "id" = "initiative_id_v";
294 "rank_v" := "rank_v" + 1;
295 END LOOP;
296 -- set schulze rank of status quo and mark issue as finished:
297 UPDATE "issue" SET
298 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
299 "state" =
300 CASE WHEN EXISTS (
301 SELECT NULL FROM "initiative"
302 WHERE "issue_id" = "issue_id_p" AND "winner"
303 ) THEN
304 'finished_with_winner'::"issue_state"
305 ELSE
306 'finished_without_winner'::"issue_state"
307 END,
308 "ranks_available" = TRUE
309 WHERE "id" = "issue_id_p";
310 RETURN;
311 END;
312 $$;
314 -- set "eligible" to FALSE for all non-eligible
315 -- but admitted initiatives of closed issues:
316 UPDATE "initiative" SET "eligible" = FALSE WHERE "eligible" ISNULL AND "rank" NOTNULL;
318 -- NOTE: "rank" ordering of initiatives of closed issues are
319 -- not changed, as v2.0.2 should not be in productive use yet.
321 COMMIT;