liquid_feedback_core
view update/core-update.v1.4.0-v1.5.0.sql @ 178:184b023a5b1d
Set voting results for finished issues in update script to v1.5.0
author | jbe |
---|---|
date | Wed Jun 08 20:47:19 2011 +0200 (2011-06-08) |
parents | 9975ce3f20ee |
children | fb2394116b39 |
line source
1 SELECT "calculate_ranks"("id") FROM "issue_with_ranks_missing";
3 BEGIN;
5 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
6 SELECT * FROM (VALUES ('1.5.0_devel', 1, 5, -1))
7 AS "subquery"("string", "major", "minor", "revision");
9 ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT;
10 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
12 CREATE TABLE "rendered_member_statement" (
13 PRIMARY KEY ("member_id", "format"),
14 "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
15 "format" TEXT,
16 "content" TEXT NOT NULL );
18 COMMENT ON TABLE "rendered_member_statement" IS 'This table may be used by frontends to cache "rendered" member statements (e.g. HTML output generated from wiki text)';
20 ALTER TABLE "policy" ADD COLUMN "direct_majority_num" INT4 NOT NULL DEFAULT 1;
21 ALTER TABLE "policy" ADD COLUMN "direct_majority_den" INT4 NOT NULL DEFAULT 2;
22 ALTER TABLE "policy" ADD COLUMN "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE;
23 ALTER TABLE "policy" ADD COLUMN "direct_majority_positive" INT4 NOT NULL DEFAULT 0;
24 ALTER TABLE "policy" ADD COLUMN "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0;
25 ALTER TABLE "policy" ADD COLUMN "indirect_majority_num" INT4 NOT NULL DEFAULT 1;
26 ALTER TABLE "policy" ADD COLUMN "indirect_majority_den" INT4 NOT NULL DEFAULT 2;
27 ALTER TABLE "policy" ADD COLUMN "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE;
28 ALTER TABLE "policy" ADD COLUMN "indirect_majority_positive" INT4 NOT NULL DEFAULT 0;
29 ALTER TABLE "policy" ADD COLUMN "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0;
30 ALTER TABLE "policy" ADD COLUMN "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE;
31 ALTER TABLE "policy" ADD COLUMN "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE;
33 UPDATE "policy" SET
34 "direct_majority_num" = "majority_num",
35 "direct_majority_den" = "majority_den",
36 "direct_majority_strict" = "majority_strict",
37 "indirect_majority_num" = "majority_num",
38 "indirect_majority_den" = "majority_den",
39 "indirect_majority_strict" = "majority_strict";
41 ALTER TABLE "policy" DROP COLUMN "majority_num";
42 ALTER TABLE "policy" DROP COLUMN "majority_den";
43 ALTER TABLE "policy" DROP COLUMN "majority_strict";
45 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
46 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
47 COMMENT ON COLUMN "policy"."direct_majority_strict" IS 'If TRUE, then the direct majority must be strictly greater than "direct_majority_num"/"direct_majority_den", otherwise it may also be equal.';
48 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
49 COMMENT ON COLUMN "policy"."direct_majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be attainable as winner';
50 COMMENT ON COLUMN "policy"."indirect_majority_num" IS 'Numerator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner';
51 COMMENT ON COLUMN "policy"."indirect_majority_den" IS 'Denominator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner';
52 COMMENT ON COLUMN "policy"."indirect_majority_strict" IS 'If TRUE, then the indirect majority must be strictly greater than "indirect_majority_num"/"indirect_majority_den", otherwise it may also be equal.';
53 COMMENT ON COLUMN "policy"."indirect_majority_positive" IS 'Absolute number of votes in favor of the winner neccessary in a beat path to the status quo for an initaitive to be attainable as winner';
54 COMMENT ON COLUMN "policy"."indirect_majority_non_negative" IS 'Absolute number of sum of votes in favor and abstentions in a beat path to the status quo for an initiative to be attainable as winner';
55 COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'Causes initiatives with "reverse_beat_path" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."reverse_beat_path". This option ensures both that a winning initiative is never tied in a (weak) condorcet paradox with the status quo and a winning initiative always beats the status quo directly with a simple majority.';
56 COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'Causes initiatives with "multistage_majority" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."multistage_majority". This disqualifies initiatives which could cause an instable result. An instable result in this meaning is a result such that repeating the ballot with same preferences but with the winner of the first ballot as status quo would lead to a different winner in the second ballot. If there are no direct majorities required for the winner, or if in direct comparison only simple majorities are required and "no_reverse_beat_path" is true, then results are always stable and this flag does not have any effect on the winner (but still affects the "eligible" flag of an "initiative").';
58 ALTER TABLE "area" DROP COLUMN "autoreject_weight";
60 DROP VIEW "open_issue";
61 DROP VIEW "issue_with_ranks_missing";
63 ALTER TABLE "issue" DROP COLUMN "vote_now";
64 ALTER TABLE "issue" DROP COLUMN "vote_later";
65 ALTER TABLE "issue" ADD COLUMN "status_quo_schulze_rank" INT4;
67 CREATE VIEW "open_issue" AS
68 SELECT * FROM "issue" WHERE "closed" ISNULL;
70 COMMENT ON VIEW "open_issue" IS 'All open issues';
72 CREATE VIEW "issue_with_ranks_missing" AS
73 SELECT * FROM "issue"
74 WHERE "fully_frozen" NOTNULL
75 AND "closed" NOTNULL
76 AND "ranks_available" = FALSE;
78 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
80 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
81 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
82 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
84 ALTER TABLE "initiative" DROP COLUMN "agreed";
85 ALTER TABLE "initiative" ADD COLUMN "direct_majority" BOOLEAN;
86 ALTER TABLE "initiative" ADD COLUMN "indirect_majority" BOOLEAN;
87 ALTER TABLE "initiative" ADD COLUMN "schulze_rank" INT4;
88 ALTER TABLE "initiative" ADD COLUMN "better_than_status_quo" BOOLEAN;
89 ALTER TABLE "initiative" ADD COLUMN "worse_than_status_quo" BOOLEAN;
90 ALTER TABLE "initiative" ADD COLUMN "reverse_beat_path" BOOLEAN;
91 ALTER TABLE "initiative" ADD COLUMN "multistage_majority" BOOLEAN;
92 ALTER TABLE "initiative" ADD COLUMN "eligible" BOOLEAN;
93 ALTER TABLE "initiative" ADD COLUMN "winner" BOOLEAN;
95 ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
96 ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null";
97 ALTER TABLE "initiative" DROP CONSTRAINT "non_agreed_initiatives_cant_get_a_rank";
99 ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
100 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
101 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
102 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
103 "schulze_rank" ISNULL AND
104 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
105 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
106 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
107 ALTER TABLE "initiative" ADD CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo"));
108 ALTER TABLE "initiative" ADD CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
109 "eligible" = FALSE OR
110 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") );
111 ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE);
112 ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1);
113 ALTER TABLE "initiative" ADD CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE);
114 ALTER TABLE "initiative" ADD CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank");
116 COMMENT ON COLUMN "initiative"."direct_majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"';
117 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
118 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
119 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
120 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
121 COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there is a beat path (may include ties), from this initiative to the status quo';
122 COMMENT ON COLUMN "initiative"."multistage_majority" IS 'TRUE, if either (a) this initiative has no better rank than the status quo, or (b) there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X';
123 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"';
124 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
125 COMMENT ON COLUMN "initiative"."rank" IS 'Unique ranking for all "admitted" initiatives per issue; lower rank is better; a winner always has rank 1, but rank 1 does not imply that an initiative is winner; initiatives with "direct_majority" AND "indirect_majority" always have a better (lower) rank than other initiatives';
127 ALTER TABLE "battle" DROP CONSTRAINT "battle_pkey";
128 ALTER TABLE "battle" ALTER COLUMN "issue_id" SET NOT NULL;
129 ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK (
130 "winning_initiative_id" != "losing_initiative_id" OR
131 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
132 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) );
134 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
135 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
136 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
138 ALTER TABLE "suggestion" ADD COLUMN "draft_id" INT8;
139 -- more later
141 ALTER TABLE "suggestion" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;
142 ALTER TABLE "suggestion" ADD COLUMN "formatting_engine" TEXT;
143 ALTER TABLE "suggestion" RENAME COLUMN "description" TO "content";
145 DROP TRIGGER "update_text_search_data" ON "suggestion";
147 CREATE TRIGGER "update_text_search_data"
148 BEFORE INSERT OR UPDATE ON "suggestion"
149 FOR EACH ROW EXECUTE PROCEDURE
150 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
151 "name", "content");
153 COMMENT ON COLUMN "suggestion"."draft_id" IS 'Draft, which the author has seen when composing the suggestion; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
155 CREATE TABLE "rendered_suggestion" (
156 PRIMARY KEY ("suggestion_id", "format"),
157 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
158 "format" TEXT,
159 "content" TEXT NOT NULL );
161 COMMENT ON TABLE "rendered_suggestion" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';
163 DROP VIEW "area_member_count";
165 ALTER TABLE "membership" DROP COLUMN "autoreject";
167 ALTER TABLE "interest" DROP COLUMN "autoreject";
168 ALTER TABLE "interest" DROP COLUMN "voting_requested";
170 ALTER TABLE "supporter" DROP CONSTRAINT "supporter_initiative_id_fkey";
171 ALTER TABLE "supporter" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;
173 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
175 ALTER TABLE "direct_interest_snapshot" DROP COLUMN "voting_requested";
176 ALTER TABLE "direct_voter" DROP COLUMN "autoreject";
178 DROP TRIGGER "default_for_draft_id" ON "supporter";
179 DROP FUNCTION "supporter_default_for_draft_id_trigger"();
181 CREATE FUNCTION "default_for_draft_id_trigger"()
182 RETURNS TRIGGER
183 LANGUAGE 'plpgsql' VOLATILE AS $$
184 BEGIN
185 IF NEW."draft_id" ISNULL THEN
186 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
187 WHERE "initiative_id" = NEW."initiative_id";
188 END IF;
189 RETURN NEW;
190 END;
191 $$;
193 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
194 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
195 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
196 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
198 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
199 COMMENT ON TRIGGER "default_for_draft_id" ON "suggestion" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';
200 COMMENT ON TRIGGER "default_for_draft_id" ON "supporter" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';
202 CREATE VIEW "area_member_count" AS
203 SELECT
204 "area"."id" AS "area_id",
205 count("member"."id") AS "direct_member_count",
206 coalesce(
207 sum(
208 CASE WHEN "member"."id" NOTNULL THEN
209 "membership_weight"("area"."id", "member"."id")
210 ELSE 0 END
211 )
212 ) AS "member_weight"
213 FROM "area"
214 LEFT JOIN "membership"
215 ON "area"."id" = "membership"."area_id"
216 LEFT JOIN "privilege"
217 ON "privilege"."unit_id" = "area"."unit_id"
218 AND "privilege"."member_id" = "membership"."member_id"
219 AND "privilege"."voting_right"
220 LEFT JOIN "member"
221 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
222 AND "member"."active"
223 GROUP BY "area"."id";
225 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
227 CREATE VIEW "battle_participant" AS
228 SELECT "initiative"."id", "initiative"."issue_id"
229 FROM "issue" JOIN "initiative"
230 ON "issue"."id" = "initiative"."issue_id"
231 WHERE "initiative"."admitted"
232 UNION ALL
233 SELECT NULL, "id" AS "issue_id"
234 FROM "issue";
236 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
238 CREATE OR REPLACE VIEW "battle_view" AS
239 SELECT
240 "issue"."id" AS "issue_id",
241 "winning_initiative"."id" AS "winning_initiative_id",
242 "losing_initiative"."id" AS "losing_initiative_id",
243 sum(
244 CASE WHEN
245 coalesce("better_vote"."grade", 0) >
246 coalesce("worse_vote"."grade", 0)
247 THEN "direct_voter"."weight" ELSE 0 END
248 ) AS "count"
249 FROM "issue"
250 LEFT JOIN "direct_voter"
251 ON "issue"."id" = "direct_voter"."issue_id"
252 JOIN "battle_participant" AS "winning_initiative"
253 ON "issue"."id" = "winning_initiative"."issue_id"
254 JOIN "battle_participant" AS "losing_initiative"
255 ON "issue"."id" = "losing_initiative"."issue_id"
256 LEFT JOIN "vote" AS "better_vote"
257 ON "direct_voter"."member_id" = "better_vote"."member_id"
258 AND "winning_initiative"."id" = "better_vote"."initiative_id"
259 LEFT JOIN "vote" AS "worse_vote"
260 ON "direct_voter"."member_id" = "worse_vote"."member_id"
261 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
262 WHERE "issue"."closed" NOTNULL
263 AND "issue"."cleaned" ISNULL
264 AND (
265 "winning_initiative"."id" != "losing_initiative"."id" OR
266 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
267 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
268 GROUP BY
269 "issue"."id",
270 "winning_initiative"."id",
271 "losing_initiative"."id";
273 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative (or status-quo) to another initiative (or status-quo); Used to fill "battle" table';
275 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
276 ( "issue_id_p" "issue"."id"%TYPE )
277 RETURNS VOID
278 LANGUAGE 'plpgsql' VOLATILE AS $$
279 DECLARE
280 "member_id_v" "member"."id"%TYPE;
281 BEGIN
282 DELETE FROM "direct_interest_snapshot"
283 WHERE "issue_id" = "issue_id_p"
284 AND "event" = 'periodic';
285 DELETE FROM "delegating_interest_snapshot"
286 WHERE "issue_id" = "issue_id_p"
287 AND "event" = 'periodic';
288 DELETE FROM "direct_supporter_snapshot"
289 WHERE "issue_id" = "issue_id_p"
290 AND "event" = 'periodic';
291 INSERT INTO "direct_interest_snapshot"
292 ("issue_id", "event", "member_id")
293 SELECT
294 "issue_id_p" AS "issue_id",
295 'periodic' AS "event",
296 "member"."id" AS "member_id"
297 FROM "issue"
298 JOIN "area" ON "issue"."area_id" = "area"."id"
299 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
300 JOIN "member" ON "interest"."member_id" = "member"."id"
301 JOIN "privilege"
302 ON "privilege"."unit_id" = "area"."unit_id"
303 AND "privilege"."member_id" = "member"."id"
304 WHERE "issue"."id" = "issue_id_p"
305 AND "member"."active" AND "privilege"."voting_right";
306 FOR "member_id_v" IN
307 SELECT "member_id" FROM "direct_interest_snapshot"
308 WHERE "issue_id" = "issue_id_p"
309 AND "event" = 'periodic'
310 LOOP
311 UPDATE "direct_interest_snapshot" SET
312 "weight" = 1 +
313 "weight_of_added_delegations_for_interest_snapshot"(
314 "issue_id_p",
315 "member_id_v",
316 '{}'
317 )
318 WHERE "issue_id" = "issue_id_p"
319 AND "event" = 'periodic'
320 AND "member_id" = "member_id_v";
321 END LOOP;
322 INSERT INTO "direct_supporter_snapshot"
323 ( "issue_id", "initiative_id", "event", "member_id",
324 "informed", "satisfied" )
325 SELECT
326 "issue_id_p" AS "issue_id",
327 "initiative"."id" AS "initiative_id",
328 'periodic' AS "event",
329 "supporter"."member_id" AS "member_id",
330 "supporter"."draft_id" = "current_draft"."id" AS "informed",
331 NOT EXISTS (
332 SELECT NULL FROM "critical_opinion"
333 WHERE "initiative_id" = "initiative"."id"
334 AND "member_id" = "supporter"."member_id"
335 ) AS "satisfied"
336 FROM "initiative"
337 JOIN "supporter"
338 ON "supporter"."initiative_id" = "initiative"."id"
339 JOIN "current_draft"
340 ON "initiative"."id" = "current_draft"."initiative_id"
341 JOIN "direct_interest_snapshot"
342 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
343 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
344 AND "event" = 'periodic'
345 WHERE "initiative"."issue_id" = "issue_id_p";
346 RETURN;
347 END;
348 $$;
350 CREATE OR REPLACE FUNCTION "create_snapshot"
351 ( "issue_id_p" "issue"."id"%TYPE )
352 RETURNS VOID
353 LANGUAGE 'plpgsql' VOLATILE AS $$
354 DECLARE
355 "initiative_id_v" "initiative"."id"%TYPE;
356 "suggestion_id_v" "suggestion"."id"%TYPE;
357 BEGIN
358 PERFORM "lock_issue"("issue_id_p");
359 PERFORM "create_population_snapshot"("issue_id_p");
360 PERFORM "create_interest_snapshot"("issue_id_p");
361 UPDATE "issue" SET
362 "snapshot" = now(),
363 "latest_snapshot_event" = 'periodic',
364 "population" = (
365 SELECT coalesce(sum("weight"), 0)
366 FROM "direct_population_snapshot"
367 WHERE "issue_id" = "issue_id_p"
368 AND "event" = 'periodic'
369 )
370 WHERE "id" = "issue_id_p";
371 FOR "initiative_id_v" IN
372 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
373 LOOP
374 UPDATE "initiative" SET
375 "supporter_count" = (
376 SELECT coalesce(sum("di"."weight"), 0)
377 FROM "direct_interest_snapshot" AS "di"
378 JOIN "direct_supporter_snapshot" AS "ds"
379 ON "di"."member_id" = "ds"."member_id"
380 WHERE "di"."issue_id" = "issue_id_p"
381 AND "di"."event" = 'periodic'
382 AND "ds"."initiative_id" = "initiative_id_v"
383 AND "ds"."event" = 'periodic'
384 ),
385 "informed_supporter_count" = (
386 SELECT coalesce(sum("di"."weight"), 0)
387 FROM "direct_interest_snapshot" AS "di"
388 JOIN "direct_supporter_snapshot" AS "ds"
389 ON "di"."member_id" = "ds"."member_id"
390 WHERE "di"."issue_id" = "issue_id_p"
391 AND "di"."event" = 'periodic'
392 AND "ds"."initiative_id" = "initiative_id_v"
393 AND "ds"."event" = 'periodic'
394 AND "ds"."informed"
395 ),
396 "satisfied_supporter_count" = (
397 SELECT coalesce(sum("di"."weight"), 0)
398 FROM "direct_interest_snapshot" AS "di"
399 JOIN "direct_supporter_snapshot" AS "ds"
400 ON "di"."member_id" = "ds"."member_id"
401 WHERE "di"."issue_id" = "issue_id_p"
402 AND "di"."event" = 'periodic'
403 AND "ds"."initiative_id" = "initiative_id_v"
404 AND "ds"."event" = 'periodic'
405 AND "ds"."satisfied"
406 ),
407 "satisfied_informed_supporter_count" = (
408 SELECT coalesce(sum("di"."weight"), 0)
409 FROM "direct_interest_snapshot" AS "di"
410 JOIN "direct_supporter_snapshot" AS "ds"
411 ON "di"."member_id" = "ds"."member_id"
412 WHERE "di"."issue_id" = "issue_id_p"
413 AND "di"."event" = 'periodic'
414 AND "ds"."initiative_id" = "initiative_id_v"
415 AND "ds"."event" = 'periodic'
416 AND "ds"."informed"
417 AND "ds"."satisfied"
418 )
419 WHERE "id" = "initiative_id_v";
420 FOR "suggestion_id_v" IN
421 SELECT "id" FROM "suggestion"
422 WHERE "initiative_id" = "initiative_id_v"
423 LOOP
424 UPDATE "suggestion" SET
425 "minus2_unfulfilled_count" = (
426 SELECT coalesce(sum("snapshot"."weight"), 0)
427 FROM "issue" CROSS JOIN "opinion"
428 JOIN "direct_interest_snapshot" AS "snapshot"
429 ON "snapshot"."issue_id" = "issue"."id"
430 AND "snapshot"."event" = "issue"."latest_snapshot_event"
431 AND "snapshot"."member_id" = "opinion"."member_id"
432 WHERE "issue"."id" = "issue_id_p"
433 AND "opinion"."suggestion_id" = "suggestion_id_v"
434 AND "opinion"."degree" = -2
435 AND "opinion"."fulfilled" = FALSE
436 ),
437 "minus2_fulfilled_count" = (
438 SELECT coalesce(sum("snapshot"."weight"), 0)
439 FROM "issue" CROSS JOIN "opinion"
440 JOIN "direct_interest_snapshot" AS "snapshot"
441 ON "snapshot"."issue_id" = "issue"."id"
442 AND "snapshot"."event" = "issue"."latest_snapshot_event"
443 AND "snapshot"."member_id" = "opinion"."member_id"
444 WHERE "issue"."id" = "issue_id_p"
445 AND "opinion"."suggestion_id" = "suggestion_id_v"
446 AND "opinion"."degree" = -2
447 AND "opinion"."fulfilled" = TRUE
448 ),
449 "minus1_unfulfilled_count" = (
450 SELECT coalesce(sum("snapshot"."weight"), 0)
451 FROM "issue" CROSS JOIN "opinion"
452 JOIN "direct_interest_snapshot" AS "snapshot"
453 ON "snapshot"."issue_id" = "issue"."id"
454 AND "snapshot"."event" = "issue"."latest_snapshot_event"
455 AND "snapshot"."member_id" = "opinion"."member_id"
456 WHERE "issue"."id" = "issue_id_p"
457 AND "opinion"."suggestion_id" = "suggestion_id_v"
458 AND "opinion"."degree" = -1
459 AND "opinion"."fulfilled" = FALSE
460 ),
461 "minus1_fulfilled_count" = (
462 SELECT coalesce(sum("snapshot"."weight"), 0)
463 FROM "issue" CROSS JOIN "opinion"
464 JOIN "direct_interest_snapshot" AS "snapshot"
465 ON "snapshot"."issue_id" = "issue"."id"
466 AND "snapshot"."event" = "issue"."latest_snapshot_event"
467 AND "snapshot"."member_id" = "opinion"."member_id"
468 WHERE "issue"."id" = "issue_id_p"
469 AND "opinion"."suggestion_id" = "suggestion_id_v"
470 AND "opinion"."degree" = -1
471 AND "opinion"."fulfilled" = TRUE
472 ),
473 "plus1_unfulfilled_count" = (
474 SELECT coalesce(sum("snapshot"."weight"), 0)
475 FROM "issue" CROSS JOIN "opinion"
476 JOIN "direct_interest_snapshot" AS "snapshot"
477 ON "snapshot"."issue_id" = "issue"."id"
478 AND "snapshot"."event" = "issue"."latest_snapshot_event"
479 AND "snapshot"."member_id" = "opinion"."member_id"
480 WHERE "issue"."id" = "issue_id_p"
481 AND "opinion"."suggestion_id" = "suggestion_id_v"
482 AND "opinion"."degree" = 1
483 AND "opinion"."fulfilled" = FALSE
484 ),
485 "plus1_fulfilled_count" = (
486 SELECT coalesce(sum("snapshot"."weight"), 0)
487 FROM "issue" CROSS JOIN "opinion"
488 JOIN "direct_interest_snapshot" AS "snapshot"
489 ON "snapshot"."issue_id" = "issue"."id"
490 AND "snapshot"."event" = "issue"."latest_snapshot_event"
491 AND "snapshot"."member_id" = "opinion"."member_id"
492 WHERE "issue"."id" = "issue_id_p"
493 AND "opinion"."suggestion_id" = "suggestion_id_v"
494 AND "opinion"."degree" = 1
495 AND "opinion"."fulfilled" = TRUE
496 ),
497 "plus2_unfulfilled_count" = (
498 SELECT coalesce(sum("snapshot"."weight"), 0)
499 FROM "issue" CROSS JOIN "opinion"
500 JOIN "direct_interest_snapshot" AS "snapshot"
501 ON "snapshot"."issue_id" = "issue"."id"
502 AND "snapshot"."event" = "issue"."latest_snapshot_event"
503 AND "snapshot"."member_id" = "opinion"."member_id"
504 WHERE "issue"."id" = "issue_id_p"
505 AND "opinion"."suggestion_id" = "suggestion_id_v"
506 AND "opinion"."degree" = 2
507 AND "opinion"."fulfilled" = FALSE
508 ),
509 "plus2_fulfilled_count" = (
510 SELECT coalesce(sum("snapshot"."weight"), 0)
511 FROM "issue" CROSS JOIN "opinion"
512 JOIN "direct_interest_snapshot" AS "snapshot"
513 ON "snapshot"."issue_id" = "issue"."id"
514 AND "snapshot"."event" = "issue"."latest_snapshot_event"
515 AND "snapshot"."member_id" = "opinion"."member_id"
516 WHERE "issue"."id" = "issue_id_p"
517 AND "opinion"."suggestion_id" = "suggestion_id_v"
518 AND "opinion"."degree" = 2
519 AND "opinion"."fulfilled" = TRUE
520 )
521 WHERE "suggestion"."id" = "suggestion_id_v";
522 END LOOP;
523 END LOOP;
524 RETURN;
525 END;
526 $$;
528 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
529 RETURNS VOID
530 LANGUAGE 'plpgsql' VOLATILE AS $$
531 DECLARE
532 "area_id_v" "area"."id"%TYPE;
533 "unit_id_v" "unit"."id"%TYPE;
534 "member_id_v" "member"."id"%TYPE;
535 BEGIN
536 PERFORM "lock_issue"("issue_id_p");
537 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
538 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
539 -- delete delegating votes (in cases of manual reset of issue state):
540 DELETE FROM "delegating_voter"
541 WHERE "issue_id" = "issue_id_p";
542 -- delete votes from non-privileged voters:
543 DELETE FROM "direct_voter"
544 USING (
545 SELECT
546 "direct_voter"."member_id"
547 FROM "direct_voter"
548 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
549 LEFT JOIN "privilege"
550 ON "privilege"."unit_id" = "unit_id_v"
551 AND "privilege"."member_id" = "direct_voter"."member_id"
552 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
553 "member"."active" = FALSE OR
554 "privilege"."voting_right" ISNULL OR
555 "privilege"."voting_right" = FALSE
556 )
557 ) AS "subquery"
558 WHERE "direct_voter"."issue_id" = "issue_id_p"
559 AND "direct_voter"."member_id" = "subquery"."member_id";
560 -- consider delegations:
561 UPDATE "direct_voter" SET "weight" = 1
562 WHERE "issue_id" = "issue_id_p";
563 PERFORM "add_vote_delegations"("issue_id_p");
564 -- set voter count and mark issue as being calculated:
565 UPDATE "issue" SET
566 "state" = 'calculation',
567 "closed" = now(),
568 "voter_count" = (
569 SELECT coalesce(sum("weight"), 0)
570 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
571 )
572 WHERE "id" = "issue_id_p";
573 -- materialize battle_view:
574 -- NOTE: "closed" column of issue must be set at this point
575 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
576 INSERT INTO "battle" (
577 "issue_id",
578 "winning_initiative_id", "losing_initiative_id",
579 "count"
580 ) SELECT
581 "issue_id",
582 "winning_initiative_id", "losing_initiative_id",
583 "count"
584 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
585 -- copy "positive_votes" and "negative_votes" from "battle" table:
586 UPDATE "initiative" SET
587 "positive_votes" = "battle_win"."count",
588 "negative_votes" = "battle_lose"."count"
589 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
590 WHERE
591 "battle_win"."issue_id" = "issue_id_p" AND
592 "battle_win"."winning_initiative_id" = "initiative"."id" AND
593 "battle_win"."losing_initiative_id" ISNULL AND
594 "battle_lose"."issue_id" = "issue_id_p" AND
595 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
596 "battle_lose"."winning_initiative_id" ISNULL;
597 END;
598 $$;
600 DROP FUNCTION "array_init_string"(INTEGER);
601 DROP FUNCTION "square_matrix_init_string"(INTEGER);
603 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
604 RETURNS VOID
605 LANGUAGE 'plpgsql' VOLATILE AS $$
606 DECLARE
607 "issue_row" "issue"%ROWTYPE;
608 "policy_row" "policy"%ROWTYPE;
609 "dimension_v" INTEGER;
610 "vote_matrix" INT4[][]; -- absolute votes
611 "matrix" INT8[][]; -- defeat strength / best paths
612 "i" INTEGER;
613 "j" INTEGER;
614 "k" INTEGER;
615 "battle_row" "battle"%ROWTYPE;
616 "rank_ary" INT4[];
617 "rank_v" INT4;
618 "done_v" INTEGER;
619 "winners_ary" INTEGER[];
620 "initiative_id_v" "initiative"."id"%TYPE;
621 BEGIN
622 SELECT * INTO "issue_row"
623 FROM "issue" WHERE "id" = "issue_id_p"
624 FOR UPDATE;
625 SELECT * INTO "policy_row"
626 FROM "policy" WHERE "id" = "issue_row"."policy_id";
627 SELECT count(1) INTO "dimension_v"
628 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
629 -- Create "vote_matrix" with absolute number of votes in pairwise
630 -- comparison:
631 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
632 "i" := 1;
633 "j" := 2;
634 FOR "battle_row" IN
635 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
636 ORDER BY
637 "winning_initiative_id" NULLS LAST,
638 "losing_initiative_id" NULLS LAST
639 LOOP
640 "vote_matrix"["i"]["j"] := "battle_row"."count";
641 IF "j" = "dimension_v" THEN
642 "i" := "i" + 1;
643 "j" := 1;
644 ELSE
645 "j" := "j" + 1;
646 IF "j" = "i" THEN
647 "j" := "j" + 1;
648 END IF;
649 END IF;
650 END LOOP;
651 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
652 RAISE EXCEPTION 'Wrong battle count (should not happen)';
653 END IF;
654 -- Store defeat strengths in "matrix" using "defeat_strength"
655 -- function:
656 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
657 "i" := 1;
658 LOOP
659 "j" := 1;
660 LOOP
661 IF "i" != "j" THEN
662 "matrix"["i"]["j"] := "defeat_strength"(
663 "vote_matrix"["i"]["j"],
664 "vote_matrix"["j"]["i"]
665 );
666 END IF;
667 EXIT WHEN "j" = "dimension_v";
668 "j" := "j" + 1;
669 END LOOP;
670 EXIT WHEN "i" = "dimension_v";
671 "i" := "i" + 1;
672 END LOOP;
673 -- Find best paths:
674 "i" := 1;
675 LOOP
676 "j" := 1;
677 LOOP
678 IF "i" != "j" THEN
679 "k" := 1;
680 LOOP
681 IF "i" != "k" AND "j" != "k" THEN
682 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
683 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
684 "matrix"["j"]["k"] := "matrix"["j"]["i"];
685 END IF;
686 ELSE
687 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
688 "matrix"["j"]["k"] := "matrix"["i"]["k"];
689 END IF;
690 END IF;
691 END IF;
692 EXIT WHEN "k" = "dimension_v";
693 "k" := "k" + 1;
694 END LOOP;
695 END IF;
696 EXIT WHEN "j" = "dimension_v";
697 "j" := "j" + 1;
698 END LOOP;
699 EXIT WHEN "i" = "dimension_v";
700 "i" := "i" + 1;
701 END LOOP;
702 -- Determine order of winners:
703 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
704 "rank_v" := 1;
705 "done_v" := 0;
706 LOOP
707 "winners_ary" := '{}';
708 "i" := 1;
709 LOOP
710 IF "rank_ary"["i"] ISNULL THEN
711 "j" := 1;
712 LOOP
713 IF
714 "i" != "j" AND
715 "rank_ary"["j"] ISNULL AND
716 "matrix"["j"]["i"] > "matrix"["i"]["j"]
717 THEN
718 -- someone else is better
719 EXIT;
720 END IF;
721 IF "j" = "dimension_v" THEN
722 -- noone is better
723 "winners_ary" := "winners_ary" || "i";
724 EXIT;
725 END IF;
726 "j" := "j" + 1;
727 END LOOP;
728 END IF;
729 EXIT WHEN "i" = "dimension_v";
730 "i" := "i" + 1;
731 END LOOP;
732 "i" := 1;
733 LOOP
734 "rank_ary"["winners_ary"["i"]] := "rank_v";
735 "done_v" := "done_v" + 1;
736 EXIT WHEN "i" = array_upper("winners_ary", 1);
737 "i" := "i" + 1;
738 END LOOP;
739 EXIT WHEN "done_v" = "dimension_v";
740 "rank_v" := "rank_v" + 1;
741 END LOOP;
742 -- write preliminary results:
743 "i" := 1;
744 FOR "initiative_id_v" IN
745 SELECT "id" FROM "initiative"
746 WHERE "issue_id" = "issue_id_p" AND "admitted"
747 ORDER BY "id"
748 LOOP
749 UPDATE "initiative" SET
750 "direct_majority" =
751 CASE WHEN "policy_row"."direct_majority_strict" THEN
752 "positive_votes" * "policy_row"."direct_majority_den" >
753 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
754 ELSE
755 "positive_votes" * "policy_row"."direct_majority_den" >=
756 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
757 END
758 AND "positive_votes" >= "policy_row"."direct_majority_positive"
759 AND "issue_row"."voter_count"-"negative_votes" >=
760 "policy_row"."direct_majority_non_negative",
761 "indirect_majority" =
762 CASE WHEN "policy_row"."indirect_majority_strict" THEN
763 "positive_votes" * "policy_row"."indirect_majority_den" >
764 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
765 ELSE
766 "positive_votes" * "policy_row"."indirect_majority_den" >=
767 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
768 END
769 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
770 AND "issue_row"."voter_count"-"negative_votes" >=
771 "policy_row"."indirect_majority_non_negative",
772 "schulze_rank" = "rank_ary"["i"],
773 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
774 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
775 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
776 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
777 "winner" = FALSE
778 WHERE "id" = "initiative_id_v";
779 "i" := "i" + 1;
780 END LOOP;
781 IF "i" != "dimension_v" THEN
782 RAISE EXCEPTION 'Wrong winner count (should not happen)';
783 END IF;
784 -- take indirect majorities into account:
785 LOOP
786 UPDATE "initiative" SET "indirect_majority" = TRUE
787 FROM (
788 SELECT "new_initiative"."id" AS "initiative_id"
789 FROM "initiative" "old_initiative"
790 JOIN "initiative" "new_initiative"
791 ON "new_initiative"."issue_id" = "issue_id_p"
792 AND "new_initiative"."indirect_majority" = FALSE
793 JOIN "battle" "battle_win"
794 ON "battle_win"."issue_id" = "issue_id_p"
795 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
796 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
797 JOIN "battle" "battle_lose"
798 ON "battle_lose"."issue_id" = "issue_id_p"
799 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
800 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
801 WHERE "old_initiative"."issue_id" = "issue_id_p"
802 AND "old_initiative"."indirect_majority" = TRUE
803 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
804 "battle_win"."count" * "policy_row"."indirect_majority_den" >
805 "policy_row"."indirect_majority_num" *
806 ("battle_win"."count"+"battle_lose"."count")
807 ELSE
808 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
809 "policy_row"."indirect_majority_num" *
810 ("battle_win"."count"+"battle_lose"."count")
811 END
812 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
813 AND "issue_row"."voter_count"-"battle_lose"."count" >=
814 "policy_row"."indirect_majority_non_negative"
815 ) AS "subquery"
816 WHERE "id" = "subquery"."initiative_id";
817 EXIT WHEN NOT FOUND;
818 END LOOP;
819 -- set "multistage_majority" for remaining matching initiatives:
820 UPDATE "initiative" SET "multistage_majority" = TRUE
821 FROM (
822 SELECT "losing_initiative"."id" AS "initiative_id"
823 FROM "initiative" "losing_initiative"
824 JOIN "initiative" "winning_initiative"
825 ON "winning_initiative"."issue_id" = "issue_id_p"
826 AND "winning_initiative"."admitted"
827 JOIN "battle" "battle_win"
828 ON "battle_win"."issue_id" = "issue_id_p"
829 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
830 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
831 JOIN "battle" "battle_lose"
832 ON "battle_lose"."issue_id" = "issue_id_p"
833 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
834 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
835 WHERE "losing_initiative"."issue_id" = "issue_id_p"
836 AND "losing_initiative"."admitted"
837 AND "winning_initiative"."schulze_rank" <
838 "losing_initiative"."schulze_rank"
839 AND "battle_win"."count" > "battle_lose"."count"
840 AND (
841 "battle_win"."count" > "winning_initiative"."positive_votes" OR
842 "battle_lose"."count" < "losing_initiative"."negative_votes" )
843 ) AS "subquery"
844 WHERE "id" = "subquery"."initiative_id";
845 -- mark eligible initiatives:
846 UPDATE "initiative" SET "eligible" = TRUE
847 WHERE "issue_id" = "issue_id_p"
848 AND "initiative"."direct_majority"
849 AND "initiative"."indirect_majority"
850 AND "initiative"."better_than_status_quo"
851 AND (
852 "policy_row"."no_multistage_majority" = FALSE OR
853 "initiative"."multistage_majority" = FALSE )
854 AND (
855 "policy_row"."no_reverse_beat_path" = FALSE OR
856 "initiative"."reverse_beat_path" = FALSE );
857 -- mark final winner:
858 UPDATE "initiative" SET "winner" = TRUE
859 FROM (
860 SELECT "id" AS "initiative_id"
861 FROM "initiative"
862 WHERE "issue_id" = "issue_id_p" AND "eligible"
863 ORDER BY "schulze_rank", "id"
864 LIMIT 1
865 ) AS "subquery"
866 WHERE "id" = "subquery"."initiative_id";
867 -- write (final) ranks:
868 "rank_v" := 1;
869 FOR "initiative_id_v" IN
870 SELECT "id"
871 FROM "initiative"
872 WHERE "issue_id" = "issue_id_p" AND "admitted"
873 ORDER BY
874 "winner" DESC,
875 ("direct_majority" AND "indirect_majority") DESC,
876 "schulze_rank",
877 "id"
878 LOOP
879 UPDATE "initiative" SET "rank" = "rank_v"
880 WHERE "id" = "initiative_id_v";
881 "rank_v" := "rank_v" + 1;
882 END LOOP;
883 -- set schulze rank of status quo and mark issue as finished:
884 UPDATE "issue" SET
885 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
886 "state" =
887 CASE WHEN EXISTS (
888 SELECT NULL FROM "initiative"
889 WHERE "issue_id" = "issue_id_p" AND "winner"
890 ) THEN
891 'finished_with_winner'::"issue_state"
892 ELSE
893 'finished_without_winner'::"issue_state"
894 END,
895 "ranks_available" = TRUE
896 WHERE "id" = "issue_id_p";
897 RETURN;
898 END;
899 $$;
901 CREATE OR REPLACE FUNCTION "check_issue"
902 ( "issue_id_p" "issue"."id"%TYPE )
903 RETURNS VOID
904 LANGUAGE 'plpgsql' VOLATILE AS $$
905 DECLARE
906 "issue_row" "issue"%ROWTYPE;
907 "policy_row" "policy"%ROWTYPE;
908 BEGIN
909 PERFORM "lock_issue"("issue_id_p");
910 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
911 -- only process open issues:
912 IF "issue_row"."closed" ISNULL THEN
913 SELECT * INTO "policy_row" FROM "policy"
914 WHERE "id" = "issue_row"."policy_id";
915 -- create a snapshot, unless issue is already fully frozen:
916 IF "issue_row"."fully_frozen" ISNULL THEN
917 PERFORM "create_snapshot"("issue_id_p");
918 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
919 END IF;
920 -- eventually close or accept issues, which have not been accepted:
921 IF "issue_row"."accepted" ISNULL THEN
922 IF EXISTS (
923 SELECT NULL FROM "initiative"
924 WHERE "issue_id" = "issue_id_p"
925 AND "supporter_count" > 0
926 AND "supporter_count" * "policy_row"."issue_quorum_den"
927 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
928 ) THEN
929 -- accept issues, if supporter count is high enough
930 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
931 -- NOTE: "issue_row" used later
932 "issue_row"."state" := 'discussion';
933 "issue_row"."accepted" := now();
934 UPDATE "issue" SET
935 "state" = "issue_row"."state",
936 "accepted" = "issue_row"."accepted"
937 WHERE "id" = "issue_row"."id";
938 ELSIF
939 now() >= "issue_row"."created" + "issue_row"."admission_time"
940 THEN
941 -- close issues, if admission time has expired
942 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
943 UPDATE "issue" SET
944 "state" = 'canceled_issue_not_accepted',
945 "closed" = now()
946 WHERE "id" = "issue_row"."id";
947 END IF;
948 END IF;
949 -- eventually half freeze issues:
950 IF
951 -- NOTE: issue can't be closed at this point, if it has been accepted
952 "issue_row"."accepted" NOTNULL AND
953 "issue_row"."half_frozen" ISNULL
954 THEN
955 IF
956 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
957 THEN
958 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
959 -- NOTE: "issue_row" used later
960 "issue_row"."state" := 'verification';
961 "issue_row"."half_frozen" := now();
962 UPDATE "issue" SET
963 "state" = "issue_row"."state",
964 "half_frozen" = "issue_row"."half_frozen"
965 WHERE "id" = "issue_row"."id";
966 END IF;
967 END IF;
968 -- close issues after some time, if all initiatives have been revoked:
969 IF
970 "issue_row"."closed" ISNULL AND
971 NOT EXISTS (
972 -- all initiatives are revoked
973 SELECT NULL FROM "initiative"
974 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
975 ) AND (
976 -- and issue has not been accepted yet
977 "issue_row"."accepted" ISNULL OR
978 NOT EXISTS (
979 -- or no initiatives have been revoked lately
980 SELECT NULL FROM "initiative"
981 WHERE "issue_id" = "issue_id_p"
982 AND now() < "revoked" + "issue_row"."verification_time"
983 ) OR (
984 -- or verification time has elapsed
985 "issue_row"."half_frozen" NOTNULL AND
986 "issue_row"."fully_frozen" ISNULL AND
987 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
988 )
989 )
990 THEN
991 -- NOTE: "issue_row" used later
992 IF "issue_row"."accepted" ISNULL THEN
993 "issue_row"."state" := 'canceled_revoked_before_accepted';
994 ELSIF "issue_row"."half_frozen" ISNULL THEN
995 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
996 ELSE
997 "issue_row"."state" := 'canceled_after_revocation_during_verification';
998 END IF;
999 "issue_row"."closed" := now();
1000 UPDATE "issue" SET
1001 "state" = "issue_row"."state",
1002 "closed" = "issue_row"."closed"
1003 WHERE "id" = "issue_row"."id";
1004 END IF;
1005 -- fully freeze issue after verification time:
1006 IF
1007 "issue_row"."half_frozen" NOTNULL AND
1008 "issue_row"."fully_frozen" ISNULL AND
1009 "issue_row"."closed" ISNULL AND
1010 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
1011 THEN
1012 PERFORM "freeze_after_snapshot"("issue_id_p");
1013 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
1014 END IF;
1015 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1016 -- close issue by calling close_voting(...) after voting time:
1017 IF
1018 "issue_row"."closed" ISNULL AND
1019 "issue_row"."fully_frozen" NOTNULL AND
1020 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
1021 THEN
1022 PERFORM "close_voting"("issue_id_p");
1023 -- calculate ranks will not consume much time and can be done now
1024 PERFORM "calculate_ranks"("issue_id_p");
1025 END IF;
1026 END IF;
1027 RETURN;
1028 END;
1029 $$;
1031 COMMIT;
1033 BEGIN;
1035 UPDATE "initiative" SET
1036 "direct_majority" = "rank" NOTNULL,
1037 "indirect_majority" = "rank" NOTNULL,
1038 "schulze_rank" = "rank",
1039 "better_than_status_quo" = "rank" NOTNULL,
1040 "worse_than_status_quo" = "rank" ISNULL,
1041 "reverse_beat_path" = "rank" ISNULL,
1042 "multistage_majority" = "rank" ISNULL,
1043 "eligible" = "rank" NOTNULL,
1044 "winner" = ("rank" = 1)
1045 FROM "issue"
1046 WHERE "issue"."id" = "initiative"."issue_id"
1047 AND "issue"."state" IN ('finished_without_winner', 'finished_with_winner')
1048 AND "initiative"."admitted";
1050 UPDATE "issue" SET "status_quo_schulze_rank" = "subquery"."rank"
1051 FROM (
1052 SELECT
1053 "issue"."id" AS "issue_id",
1054 COALESCE(max("initiative"."rank") + 1) AS "rank"
1055 FROM "issue" JOIN "initiative"
1056 ON "issue"."id" = "initiative"."issue_id"
1057 WHERE "issue"."state" IN ('finished_without_winner', 'finished_with_winner')
1058 AND "initiative"."admitted"
1059 GROUP BY "issue"."id"
1060 ) AS "subquery"
1061 WHERE "issue"."id" = "subquery"."issue_id";
1063 CREATE FUNCTION "update__set_remaining_ranks"("issue_id_p" "issue"."id"%TYPE)
1064 RETURNS VOID
1065 LANGUAGE 'plpgsql' AS $$
1066 DECLARE
1067 "rank_v" INT4;
1068 "initiative_id_v" INT4;
1069 BEGIN
1070 SELECT "status_quo_schulze_rank" INTO "rank_v"
1071 FROM "issue" WHERE "id" = "issue_id_p";
1072 FOR "initiative_id_v" IN
1073 SELECT "id" FROM "initiative"
1074 WHERE "issue_id" = "issue_id_p" AND "admitted" AND "rank" ISNULL
1075 ORDER BY "vote_ratio"("positive_votes", "negative_votes") DESC
1076 LOOP
1077 UPDATE "initiative" SET
1078 "schulze_rank" = "rank_v" + 1,
1079 "rank" = "rank_v"
1080 WHERE "id" = "initiative_id_v";
1081 "rank_v" := "rank_v" + 1;
1082 END LOOP;
1083 RETURN;
1084 END;
1085 $$;
1087 SELECT "update__set_remaining_ranks"("id") FROM "issue"
1088 WHERE "state" IN ('finished_without_winner', 'finished_with_winner');
1090 DROP FUNCTION "update__set_remaining_ranks"("issue"."id"%TYPE);
1092 UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id"
1093 FROM (
1094 SELECT DISTINCT ON ("suggestion"."id")
1095 "suggestion"."id" AS "suggestion_id",
1096 "draft"."id" AS "draft_id"
1097 FROM "suggestion" JOIN "draft"
1098 ON "suggestion"."initiative_id" = "draft"."initiative_id"
1099 WHERE "draft"."created" <= "suggestion"."created"
1100 ORDER BY "suggestion"."id", "draft"."created" DESC
1101 ) AS "subquery"
1102 WHERE "suggestion"."id" = "subquery"."suggestion_id";
1104 COMMIT;
1106 ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;