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