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