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