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