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