liquid_feedback_core
changeset 339:aecc4f182602
Update script to v2.2.0
author | jbe |
---|---|
date | Thu Feb 21 01:27:47 2013 +0100 (2013-02-21) |
parents | 705c29b0ed44 |
children | 29163d72fe9a |
files | update/core-update.v2.1.0-v2.2.0.sql |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v2.1.0-v2.2.0.sql Thu Feb 21 01:27:47 2013 +0100 1.3 @@ -0,0 +1,1761 @@ 1.4 +BEGIN; 1.5 + 1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.7 + SELECT * FROM (VALUES ('2.2.0', 2, 2, 0)) 1.8 + AS "subquery"("string", "major", "minor", "revision"); 1.9 + 1.10 +DROP VIEW "issue_with_ranks_missing"; 1.11 +DROP VIEW "open_issue"; -- recreated later 1.12 +DROP VIEW "event_seen_by_member"; -- recreated later 1.13 +DROP VIEW "selected_event_seen_by_member"; -- recreated later 1.14 +ALTER TABLE "issue" DROP CONSTRAINT "valid_state"; 1.15 +ALTER TABLE "issue" DROP COLUMN "ranks_available"; 1.16 +ALTER TABLE "event" DROP CONSTRAINT "event_state_check"; 1.17 +ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_issue_state_changed"; -- recreated later 1.18 +ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft"; -- recreated later 1.19 +ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_suggestion_creation"; -- recreated later 1.20 +ALTER TYPE "issue_state" RENAME TO "issue_state_old"; 1.21 +CREATE TYPE "issue_state" AS ENUM ( 1.22 + 'admission', 'discussion', 'verification', 'voting', 1.23 + 'canceled_revoked_before_accepted', 1.24 + 'canceled_issue_not_accepted', 1.25 + 'canceled_after_revocation_during_discussion', 1.26 + 'canceled_after_revocation_during_verification', 1.27 + 'canceled_no_initiative_admitted', 1.28 + 'finished_without_winner', 'finished_with_winner'); 1.29 +ALTER TABLE "issue" ALTER COLUMN "state" DROP DEFAULT; 1.30 +ALTER TABLE "issue" ALTER COLUMN "state" TYPE "issue_state" USING "state"::text::"issue_state"; 1.31 +ALTER TABLE "event" ALTER COLUMN "state" TYPE "issue_state" USING "state"::text::"issue_state"; 1.32 +DROP TYPE "issue_state_old"; 1.33 +ALTER TABLE "issue" ALTER COLUMN "state" SET DEFAULT 'admission'; 1.34 +ALTER TABLE "issue" ADD CONSTRAINT "valid_state" 1.35 + CHECK (( 1.36 + ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR 1.37 + ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR 1.38 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR 1.39 + ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL) 1.40 + ) AND ( 1.41 + ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR 1.42 + ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR 1.43 + ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR 1.44 + ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR 1.45 + ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR 1.46 + ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR 1.47 + ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR 1.48 + ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR 1.49 + ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR 1.50 + ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR 1.51 + ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") 1.52 + )); 1.53 +ALTER TABLE "issue" ADD COLUMN "phase_finished" TIMESTAMPTZ; 1.54 +COMMENT ON COLUMN "issue"."phase_finished" IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set'; 1.55 +ALTER TABLE "issue" ADD CONSTRAINT "phase_finished_only_when_not_closed" 1.56 + CHECK ("phase_finished" ISNULL OR "closed" ISNULL); 1.57 +ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_issue_state_changed" CHECK ( 1.58 + "event" != 'issue_state_changed' OR ( 1.59 + "member_id" ISNULL AND 1.60 + "issue_id" NOTNULL AND 1.61 + "state" NOTNULL AND 1.62 + "initiative_id" ISNULL AND 1.63 + "draft_id" ISNULL AND 1.64 + "suggestion_id" ISNULL )); 1.65 +ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK ( 1.66 + "event" NOT IN ( 1.67 + 'initiative_created_in_new_issue', 1.68 + 'initiative_created_in_existing_issue', 1.69 + 'initiative_revoked', 1.70 + 'new_draft_created' 1.71 + ) OR ( 1.72 + "member_id" NOTNULL AND 1.73 + "issue_id" NOTNULL AND 1.74 + "state" NOTNULL AND 1.75 + "initiative_id" NOTNULL AND 1.76 + "draft_id" NOTNULL AND 1.77 + "suggestion_id" ISNULL )); 1.78 +ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_suggestion_creation" CHECK ( 1.79 + "event" != 'suggestion_created' OR ( 1.80 + "member_id" NOTNULL AND 1.81 + "issue_id" NOTNULL AND 1.82 + "state" NOTNULL AND 1.83 + "initiative_id" NOTNULL AND 1.84 + "draft_id" ISNULL AND 1.85 + "suggestion_id" NOTNULL )); 1.86 +CREATE VIEW "open_issue" AS 1.87 + SELECT * FROM "issue" WHERE "closed" ISNULL; 1.88 +COMMENT ON VIEW "open_issue" IS 'All open issues'; 1.89 +CREATE VIEW "event_seen_by_member" AS 1.90 + SELECT 1.91 + "member"."id" AS "seen_by_member_id", 1.92 + CASE WHEN "event"."state" IN ( 1.93 + 'voting', 1.94 + 'finished_without_winner', 1.95 + 'finished_with_winner' 1.96 + ) THEN 1.97 + 'voting'::"notify_level" 1.98 + ELSE 1.99 + CASE WHEN "event"."state" IN ( 1.100 + 'verification', 1.101 + 'canceled_after_revocation_during_verification', 1.102 + 'canceled_no_initiative_admitted' 1.103 + ) THEN 1.104 + 'verification'::"notify_level" 1.105 + ELSE 1.106 + CASE WHEN "event"."state" IN ( 1.107 + 'discussion', 1.108 + 'canceled_after_revocation_during_discussion' 1.109 + ) THEN 1.110 + 'discussion'::"notify_level" 1.111 + ELSE 1.112 + 'all'::"notify_level" 1.113 + END 1.114 + END 1.115 + END AS "notify_level", 1.116 + "event".* 1.117 + FROM "member" CROSS JOIN "event" 1.118 + LEFT JOIN "issue" 1.119 + ON "event"."issue_id" = "issue"."id" 1.120 + LEFT JOIN "membership" 1.121 + ON "member"."id" = "membership"."member_id" 1.122 + AND "issue"."area_id" = "membership"."area_id" 1.123 + LEFT JOIN "interest" 1.124 + ON "member"."id" = "interest"."member_id" 1.125 + AND "event"."issue_id" = "interest"."issue_id" 1.126 + LEFT JOIN "supporter" 1.127 + ON "member"."id" = "supporter"."member_id" 1.128 + AND "event"."initiative_id" = "supporter"."initiative_id" 1.129 + LEFT JOIN "ignored_member" 1.130 + ON "member"."id" = "ignored_member"."member_id" 1.131 + AND "event"."member_id" = "ignored_member"."other_member_id" 1.132 + LEFT JOIN "ignored_initiative" 1.133 + ON "member"."id" = "ignored_initiative"."member_id" 1.134 + AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 1.135 + WHERE ( 1.136 + "supporter"."member_id" NOTNULL OR 1.137 + "interest"."member_id" NOTNULL OR 1.138 + ( "membership"."member_id" NOTNULL AND 1.139 + "event"."event" IN ( 1.140 + 'issue_state_changed', 1.141 + 'initiative_created_in_new_issue', 1.142 + 'initiative_created_in_existing_issue', 1.143 + 'initiative_revoked' ) ) ) 1.144 + AND "ignored_member"."member_id" ISNULL 1.145 + AND "ignored_initiative"."member_id" ISNULL; 1.146 +COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support, but ignoring members "notify_level"'; 1.147 +CREATE VIEW "selected_event_seen_by_member" AS 1.148 + SELECT 1.149 + "member"."id" AS "seen_by_member_id", 1.150 + CASE WHEN "event"."state" IN ( 1.151 + 'voting', 1.152 + 'finished_without_winner', 1.153 + 'finished_with_winner' 1.154 + ) THEN 1.155 + 'voting'::"notify_level" 1.156 + ELSE 1.157 + CASE WHEN "event"."state" IN ( 1.158 + 'verification', 1.159 + 'canceled_after_revocation_during_verification', 1.160 + 'canceled_no_initiative_admitted' 1.161 + ) THEN 1.162 + 'verification'::"notify_level" 1.163 + ELSE 1.164 + CASE WHEN "event"."state" IN ( 1.165 + 'discussion', 1.166 + 'canceled_after_revocation_during_discussion' 1.167 + ) THEN 1.168 + 'discussion'::"notify_level" 1.169 + ELSE 1.170 + 'all'::"notify_level" 1.171 + END 1.172 + END 1.173 + END AS "notify_level", 1.174 + "event".* 1.175 + FROM "member" CROSS JOIN "event" 1.176 + LEFT JOIN "issue" 1.177 + ON "event"."issue_id" = "issue"."id" 1.178 + LEFT JOIN "membership" 1.179 + ON "member"."id" = "membership"."member_id" 1.180 + AND "issue"."area_id" = "membership"."area_id" 1.181 + LEFT JOIN "interest" 1.182 + ON "member"."id" = "interest"."member_id" 1.183 + AND "event"."issue_id" = "interest"."issue_id" 1.184 + LEFT JOIN "supporter" 1.185 + ON "member"."id" = "supporter"."member_id" 1.186 + AND "event"."initiative_id" = "supporter"."initiative_id" 1.187 + LEFT JOIN "ignored_member" 1.188 + ON "member"."id" = "ignored_member"."member_id" 1.189 + AND "event"."member_id" = "ignored_member"."other_member_id" 1.190 + LEFT JOIN "ignored_initiative" 1.191 + ON "member"."id" = "ignored_initiative"."member_id" 1.192 + AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 1.193 + WHERE ( 1.194 + ( "member"."notify_level" >= 'all' ) OR 1.195 + ( "member"."notify_level" >= 'voting' AND 1.196 + "event"."state" IN ( 1.197 + 'voting', 1.198 + 'finished_without_winner', 1.199 + 'finished_with_winner' ) ) OR 1.200 + ( "member"."notify_level" >= 'verification' AND 1.201 + "event"."state" IN ( 1.202 + 'verification', 1.203 + 'canceled_after_revocation_during_verification', 1.204 + 'canceled_no_initiative_admitted' ) ) OR 1.205 + ( "member"."notify_level" >= 'discussion' AND 1.206 + "event"."state" IN ( 1.207 + 'discussion', 1.208 + 'canceled_after_revocation_during_discussion' ) ) ) 1.209 + AND ( 1.210 + "supporter"."member_id" NOTNULL OR 1.211 + "interest"."member_id" NOTNULL OR 1.212 + ( "membership"."member_id" NOTNULL AND 1.213 + "event"."event" IN ( 1.214 + 'issue_state_changed', 1.215 + 'initiative_created_in_new_issue', 1.216 + 'initiative_created_in_existing_issue', 1.217 + 'initiative_revoked' ) ) ) 1.218 + AND "ignored_member"."member_id" ISNULL 1.219 + AND "ignored_initiative"."member_id" ISNULL; 1.220 +COMMENT ON VIEW "selected_event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests, support and members "notify_level"'; 1.221 + 1.222 +ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3); 1.223 +COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key'; 1.224 + 1.225 +CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"() 1.226 + RETURNS TRIGGER 1.227 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.228 + BEGIN 1.229 + IF NEW."state" != OLD."state" THEN 1.230 + INSERT INTO "event" ("event", "issue_id", "state") 1.231 + VALUES ('issue_state_changed', NEW."id", NEW."state"); 1.232 + END IF; 1.233 + RETURN NULL; 1.234 + END; 1.235 + $$; 1.236 + 1.237 +CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"() 1.238 + RETURNS TRIGGER 1.239 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.240 + DECLARE 1.241 + "issue_id_v" "issue"."id"%TYPE; 1.242 + "issue_row" "issue"%ROWTYPE; 1.243 + BEGIN 1.244 + IF TG_OP = 'DELETE' THEN 1.245 + "issue_id_v" := OLD."issue_id"; 1.246 + ELSE 1.247 + "issue_id_v" := NEW."issue_id"; 1.248 + END IF; 1.249 + SELECT INTO "issue_row" * FROM "issue" 1.250 + WHERE "id" = "issue_id_v" FOR SHARE; 1.251 + IF "issue_row"."closed" NOTNULL THEN 1.252 + IF 1.253 + TG_RELID = 'direct_voter'::regclass AND 1.254 + TG_OP = 'UPDATE' 1.255 + THEN 1.256 + IF 1.257 + OLD."issue_id" = NEW."issue_id" AND 1.258 + OLD."member_id" = NEW."member_id" AND 1.259 + OLD."weight" = NEW."weight" 1.260 + THEN 1.261 + RETURN NULL; -- allows changing of voter comment 1.262 + END IF; 1.263 + END IF; 1.264 + RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.'; 1.265 + ELSIF 1.266 + "issue_row"."state" = 'voting' AND 1.267 + "issue_row"."phase_finished" NOTNULL 1.268 + THEN 1.269 + IF TG_RELID = 'vote'::regclass THEN 1.270 + RAISE EXCEPTION 'Tried to modify data after voting has been closed.'; 1.271 + END IF; 1.272 + END IF; 1.273 + RETURN NULL; 1.274 + END; 1.275 + $$; 1.276 + 1.277 +CREATE OR REPLACE VIEW "battle_view" AS 1.278 + SELECT 1.279 + "issue"."id" AS "issue_id", 1.280 + "winning_initiative"."id" AS "winning_initiative_id", 1.281 + "losing_initiative"."id" AS "losing_initiative_id", 1.282 + sum( 1.283 + CASE WHEN 1.284 + coalesce("better_vote"."grade", 0) > 1.285 + coalesce("worse_vote"."grade", 0) 1.286 + THEN "direct_voter"."weight" ELSE 0 END 1.287 + ) AS "count" 1.288 + FROM "issue" 1.289 + LEFT JOIN "direct_voter" 1.290 + ON "issue"."id" = "direct_voter"."issue_id" 1.291 + JOIN "battle_participant" AS "winning_initiative" 1.292 + ON "issue"."id" = "winning_initiative"."issue_id" 1.293 + JOIN "battle_participant" AS "losing_initiative" 1.294 + ON "issue"."id" = "losing_initiative"."issue_id" 1.295 + LEFT JOIN "vote" AS "better_vote" 1.296 + ON "direct_voter"."member_id" = "better_vote"."member_id" 1.297 + AND "winning_initiative"."id" = "better_vote"."initiative_id" 1.298 + LEFT JOIN "vote" AS "worse_vote" 1.299 + ON "direct_voter"."member_id" = "worse_vote"."member_id" 1.300 + AND "losing_initiative"."id" = "worse_vote"."initiative_id" 1.301 + WHERE "issue"."state" = 'voting' 1.302 + AND "issue"."phase_finished" NOTNULL 1.303 + AND ( 1.304 + "winning_initiative"."id" != "losing_initiative"."id" OR 1.305 + ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR 1.306 + ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) ) 1.307 + GROUP BY 1.308 + "issue"."id", 1.309 + "winning_initiative"."id", 1.310 + "losing_initiative"."id"; 1.311 + 1.312 +DROP VIEW "timeline"; 1.313 +DROP VIEW "timeline_issue"; 1.314 +DROP VIEW "timeline_initiative"; 1.315 +DROP VIEW "timeline_draft"; 1.316 +DROP VIEW "timeline_suggestion"; 1.317 +DROP TYPE "timeline_event"; 1.318 + 1.319 +DROP TRIGGER "share_row_lock_issue" ON "initiative"; 1.320 +DROP TRIGGER "share_row_lock_issue" ON "interest"; 1.321 +DROP TRIGGER "share_row_lock_issue" ON "supporter"; 1.322 +DROP TRIGGER "share_row_lock_issue_via_initiative" ON "opinion"; 1.323 +DROP TRIGGER "share_row_lock_issue" ON "direct_voter"; 1.324 +DROP TRIGGER "share_row_lock_issue" ON "delegating_voter"; 1.325 +DROP TRIGGER "share_row_lock_issue" ON "vote"; 1.326 +DROP FUNCTION "share_row_lock_issue_trigger"(); 1.327 + 1.328 +CREATE FUNCTION "require_transaction_isolation"() 1.329 + RETURNS VOID 1.330 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.331 + BEGIN 1.332 + IF 1.333 + current_setting('transaction_isolation') NOT IN 1.334 + ('repeatable read', 'serializable') 1.335 + THEN 1.336 + RAISE EXCEPTION 'Insufficient transaction isolation level'; 1.337 + END IF; 1.338 + RETURN; 1.339 + END; 1.340 + $$; 1.341 + 1.342 +CREATE FUNCTION "dont_require_transaction_isolation"() 1.343 + RETURNS VOID 1.344 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.345 + BEGIN 1.346 + IF 1.347 + current_setting('transaction_isolation') IN 1.348 + ('repeatable read', 'serializable') 1.349 + THEN 1.350 + RAISE WARNING 'Unneccessary transaction isolation level: %', 1.351 + current_setting('transaction_isolation'); 1.352 + END IF; 1.353 + RETURN; 1.354 + END; 1.355 + $$; 1.356 + 1.357 +CREATE OR REPLACE FUNCTION "check_activity"() 1.358 + RETURNS VOID 1.359 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.360 + DECLARE 1.361 + "system_setting_row" "system_setting"%ROWTYPE; 1.362 + BEGIN 1.363 + PERFORM "dont_require_transaction_isolation"(); 1.364 + SELECT * INTO "system_setting_row" FROM "system_setting"; 1.365 + IF "system_setting_row"."member_ttl" NOTNULL THEN 1.366 + UPDATE "member" SET "active" = FALSE 1.367 + WHERE "active" = TRUE 1.368 + AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE; 1.369 + END IF; 1.370 + RETURN; 1.371 + END; 1.372 + $$; 1.373 + 1.374 +CREATE OR REPLACE FUNCTION "calculate_member_counts"() 1.375 + RETURNS VOID 1.376 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.377 + BEGIN 1.378 + PERFORM "require_transaction_isolation"(); 1.379 + DELETE FROM "member_count"; 1.380 + INSERT INTO "member_count" ("total_count") 1.381 + SELECT "total_count" FROM "member_count_view"; 1.382 + UPDATE "unit" SET "member_count" = "view"."member_count" 1.383 + FROM "unit_member_count" AS "view" 1.384 + WHERE "view"."unit_id" = "unit"."id"; 1.385 + UPDATE "area" SET 1.386 + "direct_member_count" = "view"."direct_member_count", 1.387 + "member_weight" = "view"."member_weight" 1.388 + FROM "area_member_count" AS "view" 1.389 + WHERE "view"."area_id" = "area"."id"; 1.390 + RETURN; 1.391 + END; 1.392 + $$; 1.393 + 1.394 +CREATE VIEW "remaining_harmonic_supporter_weight" AS 1.395 + SELECT 1.396 + "direct_interest_snapshot"."issue_id", 1.397 + "direct_interest_snapshot"."event", 1.398 + "direct_interest_snapshot"."member_id", 1.399 + "direct_interest_snapshot"."weight" AS "weight_num", 1.400 + count("initiative"."id") AS "weight_den" 1.401 + FROM "issue" 1.402 + JOIN "direct_interest_snapshot" 1.403 + ON "issue"."id" = "direct_interest_snapshot"."issue_id" 1.404 + AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event" 1.405 + JOIN "initiative" 1.406 + ON "issue"."id" = "initiative"."issue_id" 1.407 + AND "initiative"."harmonic_weight" ISNULL 1.408 + JOIN "direct_supporter_snapshot" 1.409 + ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id" 1.410 + AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event" 1.411 + AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id" 1.412 + AND ( 1.413 + "direct_supporter_snapshot"."satisfied" = TRUE OR 1.414 + coalesce("initiative"."admitted", FALSE) = FALSE 1.415 + ) 1.416 + GROUP BY 1.417 + "direct_interest_snapshot"."issue_id", 1.418 + "direct_interest_snapshot"."event", 1.419 + "direct_interest_snapshot"."member_id", 1.420 + "direct_interest_snapshot"."weight"; 1.421 +COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"'; 1.422 + 1.423 +CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS 1.424 + SELECT 1.425 + "initiative"."issue_id", 1.426 + "initiative"."id" AS "initiative_id", 1.427 + "initiative"."admitted", 1.428 + sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num", 1.429 + "remaining_harmonic_supporter_weight"."weight_den" 1.430 + FROM "remaining_harmonic_supporter_weight" 1.431 + JOIN "initiative" 1.432 + ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id" 1.433 + AND "initiative"."harmonic_weight" ISNULL 1.434 + JOIN "direct_supporter_snapshot" 1.435 + ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id" 1.436 + AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event" 1.437 + AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id" 1.438 + AND ( 1.439 + "direct_supporter_snapshot"."satisfied" = TRUE OR 1.440 + coalesce("initiative"."admitted", FALSE) = FALSE 1.441 + ) 1.442 + GROUP BY 1.443 + "initiative"."issue_id", 1.444 + "initiative"."id", 1.445 + "initiative"."admitted", 1.446 + "remaining_harmonic_supporter_weight"."weight_den"; 1.447 +COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"'; 1.448 + 1.449 +CREATE FUNCTION "set_harmonic_initiative_weights" 1.450 + ( "issue_id_p" "issue"."id"%TYPE ) 1.451 + RETURNS VOID 1.452 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.453 + DECLARE 1.454 + "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE; 1.455 + "i" INT4; 1.456 + "count_v" INT4; 1.457 + "summand_v" FLOAT; 1.458 + "id_ary" INT4[]; 1.459 + "weight_ary" FLOAT[]; 1.460 + "min_weight_v" FLOAT; 1.461 + BEGIN 1.462 + PERFORM "require_transaction_isolation"(); 1.463 + UPDATE "initiative" SET "harmonic_weight" = NULL 1.464 + WHERE "issue_id" = "issue_id_p"; 1.465 + LOOP 1.466 + "min_weight_v" := NULL; 1.467 + "i" := 0; 1.468 + "count_v" := 0; 1.469 + FOR "weight_row" IN 1.470 + SELECT * FROM "remaining_harmonic_initiative_weight_summands" 1.471 + WHERE "issue_id" = "issue_id_p" 1.472 + AND ( 1.473 + coalesce("admitted", FALSE) = FALSE OR NOT EXISTS ( 1.474 + SELECT NULL FROM "initiative" 1.475 + WHERE "issue_id" = "issue_id_p" 1.476 + AND "harmonic_weight" ISNULL 1.477 + AND coalesce("admitted", FALSE) = FALSE 1.478 + ) 1.479 + ) 1.480 + ORDER BY "initiative_id" DESC, "weight_den" DESC 1.481 + -- NOTE: non-admitted initiatives placed first (at last positions), 1.482 + -- latest initiatives treated worse in case of tie 1.483 + LOOP 1.484 + "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; 1.485 + IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN 1.486 + "i" := "i" + 1; 1.487 + "count_v" := "i"; 1.488 + "id_ary"["i"] := "weight_row"."initiative_id"; 1.489 + "weight_ary"["i"] := "summand_v"; 1.490 + ELSE 1.491 + "weight_ary"["i"] := "weight_ary"["i"] + "summand_v"; 1.492 + END IF; 1.493 + END LOOP; 1.494 + EXIT WHEN "count_v" = 0; 1.495 + "i" := 1; 1.496 + LOOP 1.497 + "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3); 1.498 + IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN 1.499 + "min_weight_v" := "weight_ary"["i"]; 1.500 + END IF; 1.501 + "i" := "i" + 1; 1.502 + EXIT WHEN "i" > "count_v"; 1.503 + END LOOP; 1.504 + "i" := 1; 1.505 + LOOP 1.506 + IF "weight_ary"["i"] = "min_weight_v" THEN 1.507 + UPDATE "initiative" SET "harmonic_weight" = "min_weight_v" 1.508 + WHERE "id" = "id_ary"["i"]; 1.509 + EXIT; 1.510 + END IF; 1.511 + "i" := "i" + 1; 1.512 + END LOOP; 1.513 + END LOOP; 1.514 + UPDATE "initiative" SET "harmonic_weight" = 0 1.515 + WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL; 1.516 + END; 1.517 + $$; 1.518 +COMMENT ON FUNCTION "set_harmonic_initiative_weights" 1.519 + ( "issue"."id"%TYPE ) 1.520 + IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue'; 1.521 + 1.522 +CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_population_snapshot" 1.523 + ( "issue_id_p" "issue"."id"%TYPE, 1.524 + "member_id_p" "member"."id"%TYPE, 1.525 + "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE ) 1.526 + RETURNS "direct_population_snapshot"."weight"%TYPE 1.527 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.528 + DECLARE 1.529 + "issue_delegation_row" "issue_delegation"%ROWTYPE; 1.530 + "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE; 1.531 + "weight_v" INT4; 1.532 + "sub_weight_v" INT4; 1.533 + BEGIN 1.534 + PERFORM "require_transaction_isolation"(); 1.535 + "weight_v" := 0; 1.536 + FOR "issue_delegation_row" IN 1.537 + SELECT * FROM "issue_delegation" 1.538 + WHERE "trustee_id" = "member_id_p" 1.539 + AND "issue_id" = "issue_id_p" 1.540 + LOOP 1.541 + IF NOT EXISTS ( 1.542 + SELECT NULL FROM "direct_population_snapshot" 1.543 + WHERE "issue_id" = "issue_id_p" 1.544 + AND "event" = 'periodic' 1.545 + AND "member_id" = "issue_delegation_row"."truster_id" 1.546 + ) AND NOT EXISTS ( 1.547 + SELECT NULL FROM "delegating_population_snapshot" 1.548 + WHERE "issue_id" = "issue_id_p" 1.549 + AND "event" = 'periodic' 1.550 + AND "member_id" = "issue_delegation_row"."truster_id" 1.551 + ) THEN 1.552 + "delegate_member_ids_v" := 1.553 + "member_id_p" || "delegate_member_ids_p"; 1.554 + INSERT INTO "delegating_population_snapshot" ( 1.555 + "issue_id", 1.556 + "event", 1.557 + "member_id", 1.558 + "scope", 1.559 + "delegate_member_ids" 1.560 + ) VALUES ( 1.561 + "issue_id_p", 1.562 + 'periodic', 1.563 + "issue_delegation_row"."truster_id", 1.564 + "issue_delegation_row"."scope", 1.565 + "delegate_member_ids_v" 1.566 + ); 1.567 + "sub_weight_v" := 1 + 1.568 + "weight_of_added_delegations_for_population_snapshot"( 1.569 + "issue_id_p", 1.570 + "issue_delegation_row"."truster_id", 1.571 + "delegate_member_ids_v" 1.572 + ); 1.573 + UPDATE "delegating_population_snapshot" 1.574 + SET "weight" = "sub_weight_v" 1.575 + WHERE "issue_id" = "issue_id_p" 1.576 + AND "event" = 'periodic' 1.577 + AND "member_id" = "issue_delegation_row"."truster_id"; 1.578 + "weight_v" := "weight_v" + "sub_weight_v"; 1.579 + END IF; 1.580 + END LOOP; 1.581 + RETURN "weight_v"; 1.582 + END; 1.583 + $$; 1.584 + 1.585 +CREATE OR REPLACE FUNCTION "create_population_snapshot" 1.586 + ( "issue_id_p" "issue"."id"%TYPE ) 1.587 + RETURNS VOID 1.588 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.589 + DECLARE 1.590 + "member_id_v" "member"."id"%TYPE; 1.591 + BEGIN 1.592 + PERFORM "require_transaction_isolation"(); 1.593 + DELETE FROM "direct_population_snapshot" 1.594 + WHERE "issue_id" = "issue_id_p" 1.595 + AND "event" = 'periodic'; 1.596 + DELETE FROM "delegating_population_snapshot" 1.597 + WHERE "issue_id" = "issue_id_p" 1.598 + AND "event" = 'periodic'; 1.599 + INSERT INTO "direct_population_snapshot" 1.600 + ("issue_id", "event", "member_id") 1.601 + SELECT 1.602 + "issue_id_p" AS "issue_id", 1.603 + 'periodic'::"snapshot_event" AS "event", 1.604 + "member"."id" AS "member_id" 1.605 + FROM "issue" 1.606 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.607 + JOIN "membership" ON "area"."id" = "membership"."area_id" 1.608 + JOIN "member" ON "membership"."member_id" = "member"."id" 1.609 + JOIN "privilege" 1.610 + ON "privilege"."unit_id" = "area"."unit_id" 1.611 + AND "privilege"."member_id" = "member"."id" 1.612 + WHERE "issue"."id" = "issue_id_p" 1.613 + AND "member"."active" AND "privilege"."voting_right" 1.614 + UNION 1.615 + SELECT 1.616 + "issue_id_p" AS "issue_id", 1.617 + 'periodic'::"snapshot_event" AS "event", 1.618 + "member"."id" AS "member_id" 1.619 + FROM "issue" 1.620 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.621 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 1.622 + JOIN "member" ON "interest"."member_id" = "member"."id" 1.623 + JOIN "privilege" 1.624 + ON "privilege"."unit_id" = "area"."unit_id" 1.625 + AND "privilege"."member_id" = "member"."id" 1.626 + WHERE "issue"."id" = "issue_id_p" 1.627 + AND "member"."active" AND "privilege"."voting_right"; 1.628 + FOR "member_id_v" IN 1.629 + SELECT "member_id" FROM "direct_population_snapshot" 1.630 + WHERE "issue_id" = "issue_id_p" 1.631 + AND "event" = 'periodic' 1.632 + LOOP 1.633 + UPDATE "direct_population_snapshot" SET 1.634 + "weight" = 1 + 1.635 + "weight_of_added_delegations_for_population_snapshot"( 1.636 + "issue_id_p", 1.637 + "member_id_v", 1.638 + '{}' 1.639 + ) 1.640 + WHERE "issue_id" = "issue_id_p" 1.641 + AND "event" = 'periodic' 1.642 + AND "member_id" = "member_id_v"; 1.643 + END LOOP; 1.644 + RETURN; 1.645 + END; 1.646 + $$; 1.647 + 1.648 +CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_interest_snapshot" 1.649 + ( "issue_id_p" "issue"."id"%TYPE, 1.650 + "member_id_p" "member"."id"%TYPE, 1.651 + "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE ) 1.652 + RETURNS "direct_interest_snapshot"."weight"%TYPE 1.653 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.654 + DECLARE 1.655 + "issue_delegation_row" "issue_delegation"%ROWTYPE; 1.656 + "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE; 1.657 + "weight_v" INT4; 1.658 + "sub_weight_v" INT4; 1.659 + BEGIN 1.660 + PERFORM "require_transaction_isolation"(); 1.661 + "weight_v" := 0; 1.662 + FOR "issue_delegation_row" IN 1.663 + SELECT * FROM "issue_delegation" 1.664 + WHERE "trustee_id" = "member_id_p" 1.665 + AND "issue_id" = "issue_id_p" 1.666 + LOOP 1.667 + IF NOT EXISTS ( 1.668 + SELECT NULL FROM "direct_interest_snapshot" 1.669 + WHERE "issue_id" = "issue_id_p" 1.670 + AND "event" = 'periodic' 1.671 + AND "member_id" = "issue_delegation_row"."truster_id" 1.672 + ) AND NOT EXISTS ( 1.673 + SELECT NULL FROM "delegating_interest_snapshot" 1.674 + WHERE "issue_id" = "issue_id_p" 1.675 + AND "event" = 'periodic' 1.676 + AND "member_id" = "issue_delegation_row"."truster_id" 1.677 + ) THEN 1.678 + "delegate_member_ids_v" := 1.679 + "member_id_p" || "delegate_member_ids_p"; 1.680 + INSERT INTO "delegating_interest_snapshot" ( 1.681 + "issue_id", 1.682 + "event", 1.683 + "member_id", 1.684 + "scope", 1.685 + "delegate_member_ids" 1.686 + ) VALUES ( 1.687 + "issue_id_p", 1.688 + 'periodic', 1.689 + "issue_delegation_row"."truster_id", 1.690 + "issue_delegation_row"."scope", 1.691 + "delegate_member_ids_v" 1.692 + ); 1.693 + "sub_weight_v" := 1 + 1.694 + "weight_of_added_delegations_for_interest_snapshot"( 1.695 + "issue_id_p", 1.696 + "issue_delegation_row"."truster_id", 1.697 + "delegate_member_ids_v" 1.698 + ); 1.699 + UPDATE "delegating_interest_snapshot" 1.700 + SET "weight" = "sub_weight_v" 1.701 + WHERE "issue_id" = "issue_id_p" 1.702 + AND "event" = 'periodic' 1.703 + AND "member_id" = "issue_delegation_row"."truster_id"; 1.704 + "weight_v" := "weight_v" + "sub_weight_v"; 1.705 + END IF; 1.706 + END LOOP; 1.707 + RETURN "weight_v"; 1.708 + END; 1.709 + $$; 1.710 + 1.711 +CREATE OR REPLACE FUNCTION "create_interest_snapshot" 1.712 + ( "issue_id_p" "issue"."id"%TYPE ) 1.713 + RETURNS VOID 1.714 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.715 + DECLARE 1.716 + "member_id_v" "member"."id"%TYPE; 1.717 + BEGIN 1.718 + PERFORM "require_transaction_isolation"(); 1.719 + DELETE FROM "direct_interest_snapshot" 1.720 + WHERE "issue_id" = "issue_id_p" 1.721 + AND "event" = 'periodic'; 1.722 + DELETE FROM "delegating_interest_snapshot" 1.723 + WHERE "issue_id" = "issue_id_p" 1.724 + AND "event" = 'periodic'; 1.725 + DELETE FROM "direct_supporter_snapshot" 1.726 + USING "initiative" -- NOTE: due to missing index on issue_id 1.727 + WHERE "initiative"."issue_id" = "issue_id_p" 1.728 + AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id" 1.729 + AND "direct_supporter_snapshot"."event" = 'periodic'; 1.730 + INSERT INTO "direct_interest_snapshot" 1.731 + ("issue_id", "event", "member_id") 1.732 + SELECT 1.733 + "issue_id_p" AS "issue_id", 1.734 + 'periodic' AS "event", 1.735 + "member"."id" AS "member_id" 1.736 + FROM "issue" 1.737 + JOIN "area" ON "issue"."area_id" = "area"."id" 1.738 + JOIN "interest" ON "issue"."id" = "interest"."issue_id" 1.739 + JOIN "member" ON "interest"."member_id" = "member"."id" 1.740 + JOIN "privilege" 1.741 + ON "privilege"."unit_id" = "area"."unit_id" 1.742 + AND "privilege"."member_id" = "member"."id" 1.743 + WHERE "issue"."id" = "issue_id_p" 1.744 + AND "member"."active" AND "privilege"."voting_right"; 1.745 + FOR "member_id_v" IN 1.746 + SELECT "member_id" FROM "direct_interest_snapshot" 1.747 + WHERE "issue_id" = "issue_id_p" 1.748 + AND "event" = 'periodic' 1.749 + LOOP 1.750 + UPDATE "direct_interest_snapshot" SET 1.751 + "weight" = 1 + 1.752 + "weight_of_added_delegations_for_interest_snapshot"( 1.753 + "issue_id_p", 1.754 + "member_id_v", 1.755 + '{}' 1.756 + ) 1.757 + WHERE "issue_id" = "issue_id_p" 1.758 + AND "event" = 'periodic' 1.759 + AND "member_id" = "member_id_v"; 1.760 + END LOOP; 1.761 + INSERT INTO "direct_supporter_snapshot" 1.762 + ( "issue_id", "initiative_id", "event", "member_id", 1.763 + "draft_id", "informed", "satisfied" ) 1.764 + SELECT 1.765 + "issue_id_p" AS "issue_id", 1.766 + "initiative"."id" AS "initiative_id", 1.767 + 'periodic' AS "event", 1.768 + "supporter"."member_id" AS "member_id", 1.769 + "supporter"."draft_id" AS "draft_id", 1.770 + "supporter"."draft_id" = "current_draft"."id" AS "informed", 1.771 + NOT EXISTS ( 1.772 + SELECT NULL FROM "critical_opinion" 1.773 + WHERE "initiative_id" = "initiative"."id" 1.774 + AND "member_id" = "supporter"."member_id" 1.775 + ) AS "satisfied" 1.776 + FROM "initiative" 1.777 + JOIN "supporter" 1.778 + ON "supporter"."initiative_id" = "initiative"."id" 1.779 + JOIN "current_draft" 1.780 + ON "initiative"."id" = "current_draft"."initiative_id" 1.781 + JOIN "direct_interest_snapshot" 1.782 + ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" 1.783 + AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" 1.784 + AND "event" = 'periodic' 1.785 + WHERE "initiative"."issue_id" = "issue_id_p"; 1.786 + RETURN; 1.787 + END; 1.788 + $$; 1.789 + 1.790 +CREATE OR REPLACE FUNCTION "create_snapshot" 1.791 + ( "issue_id_p" "issue"."id"%TYPE ) 1.792 + RETURNS VOID 1.793 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.794 + DECLARE 1.795 + "initiative_id_v" "initiative"."id"%TYPE; 1.796 + "suggestion_id_v" "suggestion"."id"%TYPE; 1.797 + BEGIN 1.798 + PERFORM "require_transaction_isolation"(); 1.799 + PERFORM "create_population_snapshot"("issue_id_p"); 1.800 + PERFORM "create_interest_snapshot"("issue_id_p"); 1.801 + UPDATE "issue" SET 1.802 + "snapshot" = coalesce("phase_finished", now()), 1.803 + "latest_snapshot_event" = 'periodic', 1.804 + "population" = ( 1.805 + SELECT coalesce(sum("weight"), 0) 1.806 + FROM "direct_population_snapshot" 1.807 + WHERE "issue_id" = "issue_id_p" 1.808 + AND "event" = 'periodic' 1.809 + ) 1.810 + WHERE "id" = "issue_id_p"; 1.811 + FOR "initiative_id_v" IN 1.812 + SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" 1.813 + LOOP 1.814 + UPDATE "initiative" SET 1.815 + "supporter_count" = ( 1.816 + SELECT coalesce(sum("di"."weight"), 0) 1.817 + FROM "direct_interest_snapshot" AS "di" 1.818 + JOIN "direct_supporter_snapshot" AS "ds" 1.819 + ON "di"."member_id" = "ds"."member_id" 1.820 + WHERE "di"."issue_id" = "issue_id_p" 1.821 + AND "di"."event" = 'periodic' 1.822 + AND "ds"."initiative_id" = "initiative_id_v" 1.823 + AND "ds"."event" = 'periodic' 1.824 + ), 1.825 + "informed_supporter_count" = ( 1.826 + SELECT coalesce(sum("di"."weight"), 0) 1.827 + FROM "direct_interest_snapshot" AS "di" 1.828 + JOIN "direct_supporter_snapshot" AS "ds" 1.829 + ON "di"."member_id" = "ds"."member_id" 1.830 + WHERE "di"."issue_id" = "issue_id_p" 1.831 + AND "di"."event" = 'periodic' 1.832 + AND "ds"."initiative_id" = "initiative_id_v" 1.833 + AND "ds"."event" = 'periodic' 1.834 + AND "ds"."informed" 1.835 + ), 1.836 + "satisfied_supporter_count" = ( 1.837 + SELECT coalesce(sum("di"."weight"), 0) 1.838 + FROM "direct_interest_snapshot" AS "di" 1.839 + JOIN "direct_supporter_snapshot" AS "ds" 1.840 + ON "di"."member_id" = "ds"."member_id" 1.841 + WHERE "di"."issue_id" = "issue_id_p" 1.842 + AND "di"."event" = 'periodic' 1.843 + AND "ds"."initiative_id" = "initiative_id_v" 1.844 + AND "ds"."event" = 'periodic' 1.845 + AND "ds"."satisfied" 1.846 + ), 1.847 + "satisfied_informed_supporter_count" = ( 1.848 + SELECT coalesce(sum("di"."weight"), 0) 1.849 + FROM "direct_interest_snapshot" AS "di" 1.850 + JOIN "direct_supporter_snapshot" AS "ds" 1.851 + ON "di"."member_id" = "ds"."member_id" 1.852 + WHERE "di"."issue_id" = "issue_id_p" 1.853 + AND "di"."event" = 'periodic' 1.854 + AND "ds"."initiative_id" = "initiative_id_v" 1.855 + AND "ds"."event" = 'periodic' 1.856 + AND "ds"."informed" 1.857 + AND "ds"."satisfied" 1.858 + ) 1.859 + WHERE "id" = "initiative_id_v"; 1.860 + FOR "suggestion_id_v" IN 1.861 + SELECT "id" FROM "suggestion" 1.862 + WHERE "initiative_id" = "initiative_id_v" 1.863 + LOOP 1.864 + UPDATE "suggestion" SET 1.865 + "minus2_unfulfilled_count" = ( 1.866 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.867 + FROM "issue" CROSS JOIN "opinion" 1.868 + JOIN "direct_interest_snapshot" AS "snapshot" 1.869 + ON "snapshot"."issue_id" = "issue"."id" 1.870 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.871 + AND "snapshot"."member_id" = "opinion"."member_id" 1.872 + WHERE "issue"."id" = "issue_id_p" 1.873 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.874 + AND "opinion"."degree" = -2 1.875 + AND "opinion"."fulfilled" = FALSE 1.876 + ), 1.877 + "minus2_fulfilled_count" = ( 1.878 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.879 + FROM "issue" CROSS JOIN "opinion" 1.880 + JOIN "direct_interest_snapshot" AS "snapshot" 1.881 + ON "snapshot"."issue_id" = "issue"."id" 1.882 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.883 + AND "snapshot"."member_id" = "opinion"."member_id" 1.884 + WHERE "issue"."id" = "issue_id_p" 1.885 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.886 + AND "opinion"."degree" = -2 1.887 + AND "opinion"."fulfilled" = TRUE 1.888 + ), 1.889 + "minus1_unfulfilled_count" = ( 1.890 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.891 + FROM "issue" CROSS JOIN "opinion" 1.892 + JOIN "direct_interest_snapshot" AS "snapshot" 1.893 + ON "snapshot"."issue_id" = "issue"."id" 1.894 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.895 + AND "snapshot"."member_id" = "opinion"."member_id" 1.896 + WHERE "issue"."id" = "issue_id_p" 1.897 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.898 + AND "opinion"."degree" = -1 1.899 + AND "opinion"."fulfilled" = FALSE 1.900 + ), 1.901 + "minus1_fulfilled_count" = ( 1.902 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.903 + FROM "issue" CROSS JOIN "opinion" 1.904 + JOIN "direct_interest_snapshot" AS "snapshot" 1.905 + ON "snapshot"."issue_id" = "issue"."id" 1.906 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.907 + AND "snapshot"."member_id" = "opinion"."member_id" 1.908 + WHERE "issue"."id" = "issue_id_p" 1.909 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.910 + AND "opinion"."degree" = -1 1.911 + AND "opinion"."fulfilled" = TRUE 1.912 + ), 1.913 + "plus1_unfulfilled_count" = ( 1.914 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.915 + FROM "issue" CROSS JOIN "opinion" 1.916 + JOIN "direct_interest_snapshot" AS "snapshot" 1.917 + ON "snapshot"."issue_id" = "issue"."id" 1.918 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.919 + AND "snapshot"."member_id" = "opinion"."member_id" 1.920 + WHERE "issue"."id" = "issue_id_p" 1.921 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.922 + AND "opinion"."degree" = 1 1.923 + AND "opinion"."fulfilled" = FALSE 1.924 + ), 1.925 + "plus1_fulfilled_count" = ( 1.926 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.927 + FROM "issue" CROSS JOIN "opinion" 1.928 + JOIN "direct_interest_snapshot" AS "snapshot" 1.929 + ON "snapshot"."issue_id" = "issue"."id" 1.930 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.931 + AND "snapshot"."member_id" = "opinion"."member_id" 1.932 + WHERE "issue"."id" = "issue_id_p" 1.933 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.934 + AND "opinion"."degree" = 1 1.935 + AND "opinion"."fulfilled" = TRUE 1.936 + ), 1.937 + "plus2_unfulfilled_count" = ( 1.938 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.939 + FROM "issue" CROSS JOIN "opinion" 1.940 + JOIN "direct_interest_snapshot" AS "snapshot" 1.941 + ON "snapshot"."issue_id" = "issue"."id" 1.942 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.943 + AND "snapshot"."member_id" = "opinion"."member_id" 1.944 + WHERE "issue"."id" = "issue_id_p" 1.945 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.946 + AND "opinion"."degree" = 2 1.947 + AND "opinion"."fulfilled" = FALSE 1.948 + ), 1.949 + "plus2_fulfilled_count" = ( 1.950 + SELECT coalesce(sum("snapshot"."weight"), 0) 1.951 + FROM "issue" CROSS JOIN "opinion" 1.952 + JOIN "direct_interest_snapshot" AS "snapshot" 1.953 + ON "snapshot"."issue_id" = "issue"."id" 1.954 + AND "snapshot"."event" = "issue"."latest_snapshot_event" 1.955 + AND "snapshot"."member_id" = "opinion"."member_id" 1.956 + WHERE "issue"."id" = "issue_id_p" 1.957 + AND "opinion"."suggestion_id" = "suggestion_id_v" 1.958 + AND "opinion"."degree" = 2 1.959 + AND "opinion"."fulfilled" = TRUE 1.960 + ) 1.961 + WHERE "suggestion"."id" = "suggestion_id_v"; 1.962 + END LOOP; 1.963 + END LOOP; 1.964 + RETURN; 1.965 + END; 1.966 + $$; 1.967 + 1.968 +CREATE OR REPLACE FUNCTION "set_snapshot_event" 1.969 + ( "issue_id_p" "issue"."id"%TYPE, 1.970 + "event_p" "snapshot_event" ) 1.971 + RETURNS VOID 1.972 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.973 + DECLARE 1.974 + "event_v" "issue"."latest_snapshot_event"%TYPE; 1.975 + BEGIN 1.976 + PERFORM "require_transaction_isolation"(); 1.977 + SELECT "latest_snapshot_event" INTO "event_v" FROM "issue" 1.978 + WHERE "id" = "issue_id_p" FOR UPDATE; 1.979 + UPDATE "issue" SET "latest_snapshot_event" = "event_p" 1.980 + WHERE "id" = "issue_id_p"; 1.981 + UPDATE "direct_population_snapshot" SET "event" = "event_p" 1.982 + WHERE "issue_id" = "issue_id_p" AND "event" = "event_v"; 1.983 + UPDATE "delegating_population_snapshot" SET "event" = "event_p" 1.984 + WHERE "issue_id" = "issue_id_p" AND "event" = "event_v"; 1.985 + UPDATE "direct_interest_snapshot" SET "event" = "event_p" 1.986 + WHERE "issue_id" = "issue_id_p" AND "event" = "event_v"; 1.987 + UPDATE "delegating_interest_snapshot" SET "event" = "event_p" 1.988 + WHERE "issue_id" = "issue_id_p" AND "event" = "event_v"; 1.989 + UPDATE "direct_supporter_snapshot" SET "event" = "event_p" 1.990 + FROM "initiative" -- NOTE: due to missing index on issue_id 1.991 + WHERE "initiative"."issue_id" = "issue_id_p" 1.992 + AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id" 1.993 + AND "direct_supporter_snapshot"."event" = "event_v"; 1.994 + RETURN; 1.995 + END; 1.996 + $$; 1.997 + 1.998 +DROP FUNCTION "freeze_after_snapshot"("issue"."id"%TYPE); 1.999 +DROP FUNCTION "manual_freeze"("issue"."id"%TYPE); 1.1000 + 1.1001 +CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations" 1.1002 + ( "issue_id_p" "issue"."id"%TYPE, 1.1003 + "member_id_p" "member"."id"%TYPE, 1.1004 + "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE ) 1.1005 + RETURNS "direct_voter"."weight"%TYPE 1.1006 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1007 + DECLARE 1.1008 + "issue_delegation_row" "issue_delegation"%ROWTYPE; 1.1009 + "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE; 1.1010 + "weight_v" INT4; 1.1011 + "sub_weight_v" INT4; 1.1012 + BEGIN 1.1013 + PERFORM "require_transaction_isolation"(); 1.1014 + "weight_v" := 0; 1.1015 + FOR "issue_delegation_row" IN 1.1016 + SELECT * FROM "issue_delegation" 1.1017 + WHERE "trustee_id" = "member_id_p" 1.1018 + AND "issue_id" = "issue_id_p" 1.1019 + LOOP 1.1020 + IF NOT EXISTS ( 1.1021 + SELECT NULL FROM "direct_voter" 1.1022 + WHERE "member_id" = "issue_delegation_row"."truster_id" 1.1023 + AND "issue_id" = "issue_id_p" 1.1024 + ) AND NOT EXISTS ( 1.1025 + SELECT NULL FROM "delegating_voter" 1.1026 + WHERE "member_id" = "issue_delegation_row"."truster_id" 1.1027 + AND "issue_id" = "issue_id_p" 1.1028 + ) THEN 1.1029 + "delegate_member_ids_v" := 1.1030 + "member_id_p" || "delegate_member_ids_p"; 1.1031 + INSERT INTO "delegating_voter" ( 1.1032 + "issue_id", 1.1033 + "member_id", 1.1034 + "scope", 1.1035 + "delegate_member_ids" 1.1036 + ) VALUES ( 1.1037 + "issue_id_p", 1.1038 + "issue_delegation_row"."truster_id", 1.1039 + "issue_delegation_row"."scope", 1.1040 + "delegate_member_ids_v" 1.1041 + ); 1.1042 + "sub_weight_v" := 1 + 1.1043 + "weight_of_added_vote_delegations"( 1.1044 + "issue_id_p", 1.1045 + "issue_delegation_row"."truster_id", 1.1046 + "delegate_member_ids_v" 1.1047 + ); 1.1048 + UPDATE "delegating_voter" 1.1049 + SET "weight" = "sub_weight_v" 1.1050 + WHERE "issue_id" = "issue_id_p" 1.1051 + AND "member_id" = "issue_delegation_row"."truster_id"; 1.1052 + "weight_v" := "weight_v" + "sub_weight_v"; 1.1053 + END IF; 1.1054 + END LOOP; 1.1055 + RETURN "weight_v"; 1.1056 + END; 1.1057 + $$; 1.1058 + 1.1059 +CREATE OR REPLACE FUNCTION "add_vote_delegations" 1.1060 + ( "issue_id_p" "issue"."id"%TYPE ) 1.1061 + RETURNS VOID 1.1062 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1063 + DECLARE 1.1064 + "member_id_v" "member"."id"%TYPE; 1.1065 + BEGIN 1.1066 + PERFORM "require_transaction_isolation"(); 1.1067 + FOR "member_id_v" IN 1.1068 + SELECT "member_id" FROM "direct_voter" 1.1069 + WHERE "issue_id" = "issue_id_p" 1.1070 + LOOP 1.1071 + UPDATE "direct_voter" SET 1.1072 + "weight" = "weight" + "weight_of_added_vote_delegations"( 1.1073 + "issue_id_p", 1.1074 + "member_id_v", 1.1075 + '{}' 1.1076 + ) 1.1077 + WHERE "member_id" = "member_id_v" 1.1078 + AND "issue_id" = "issue_id_p"; 1.1079 + END LOOP; 1.1080 + RETURN; 1.1081 + END; 1.1082 + $$; 1.1083 + 1.1084 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 1.1085 + RETURNS VOID 1.1086 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1087 + DECLARE 1.1088 + "area_id_v" "area"."id"%TYPE; 1.1089 + "unit_id_v" "unit"."id"%TYPE; 1.1090 + "member_id_v" "member"."id"%TYPE; 1.1091 + BEGIN 1.1092 + PERFORM "require_transaction_isolation"(); 1.1093 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 1.1094 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.1095 + -- delete timestamp of voting comment: 1.1096 + UPDATE "direct_voter" SET "comment_changed" = NULL 1.1097 + WHERE "issue_id" = "issue_id_p"; 1.1098 + -- delete delegating votes (in cases of manual reset of issue state): 1.1099 + DELETE FROM "delegating_voter" 1.1100 + WHERE "issue_id" = "issue_id_p"; 1.1101 + -- delete votes from non-privileged voters: 1.1102 + DELETE FROM "direct_voter" 1.1103 + USING ( 1.1104 + SELECT 1.1105 + "direct_voter"."member_id" 1.1106 + FROM "direct_voter" 1.1107 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 1.1108 + LEFT JOIN "privilege" 1.1109 + ON "privilege"."unit_id" = "unit_id_v" 1.1110 + AND "privilege"."member_id" = "direct_voter"."member_id" 1.1111 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 1.1112 + "member"."active" = FALSE OR 1.1113 + "privilege"."voting_right" ISNULL OR 1.1114 + "privilege"."voting_right" = FALSE 1.1115 + ) 1.1116 + ) AS "subquery" 1.1117 + WHERE "direct_voter"."issue_id" = "issue_id_p" 1.1118 + AND "direct_voter"."member_id" = "subquery"."member_id"; 1.1119 + -- consider delegations: 1.1120 + UPDATE "direct_voter" SET "weight" = 1 1.1121 + WHERE "issue_id" = "issue_id_p"; 1.1122 + PERFORM "add_vote_delegations"("issue_id_p"); 1.1123 + -- materialize battle_view: 1.1124 + -- NOTE: "closed" column of issue must be set at this point 1.1125 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.1126 + INSERT INTO "battle" ( 1.1127 + "issue_id", 1.1128 + "winning_initiative_id", "losing_initiative_id", 1.1129 + "count" 1.1130 + ) SELECT 1.1131 + "issue_id", 1.1132 + "winning_initiative_id", "losing_initiative_id", 1.1133 + "count" 1.1134 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 1.1135 + -- set voter count: 1.1136 + UPDATE "issue" SET 1.1137 + "voter_count" = ( 1.1138 + SELECT coalesce(sum("weight"), 0) 1.1139 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 1.1140 + ) 1.1141 + WHERE "id" = "issue_id_p"; 1.1142 + -- copy "positive_votes" and "negative_votes" from "battle" table: 1.1143 + UPDATE "initiative" SET 1.1144 + "positive_votes" = "battle_win"."count", 1.1145 + "negative_votes" = "battle_lose"."count" 1.1146 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 1.1147 + WHERE 1.1148 + "battle_win"."issue_id" = "issue_id_p" AND 1.1149 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 1.1150 + "battle_win"."losing_initiative_id" ISNULL AND 1.1151 + "battle_lose"."issue_id" = "issue_id_p" AND 1.1152 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 1.1153 + "battle_lose"."winning_initiative_id" ISNULL; 1.1154 + END; 1.1155 + $$; 1.1156 + 1.1157 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) 1.1158 + RETURNS VOID 1.1159 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1160 + DECLARE 1.1161 + "issue_row" "issue"%ROWTYPE; 1.1162 + "policy_row" "policy"%ROWTYPE; 1.1163 + "dimension_v" INTEGER; 1.1164 + "vote_matrix" INT4[][]; -- absolute votes 1.1165 + "matrix" INT8[][]; -- defeat strength / best paths 1.1166 + "i" INTEGER; 1.1167 + "j" INTEGER; 1.1168 + "k" INTEGER; 1.1169 + "battle_row" "battle"%ROWTYPE; 1.1170 + "rank_ary" INT4[]; 1.1171 + "rank_v" INT4; 1.1172 + "done_v" INTEGER; 1.1173 + "winners_ary" INTEGER[]; 1.1174 + "initiative_id_v" "initiative"."id"%TYPE; 1.1175 + BEGIN 1.1176 + PERFORM "require_transaction_isolation"(); 1.1177 + SELECT * INTO "issue_row" 1.1178 + FROM "issue" WHERE "id" = "issue_id_p"; 1.1179 + SELECT * INTO "policy_row" 1.1180 + FROM "policy" WHERE "id" = "issue_row"."policy_id"; 1.1181 + SELECT count(1) INTO "dimension_v" 1.1182 + FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 1.1183 + -- Create "vote_matrix" with absolute number of votes in pairwise 1.1184 + -- comparison: 1.1185 + "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); 1.1186 + "i" := 1; 1.1187 + "j" := 2; 1.1188 + FOR "battle_row" IN 1.1189 + SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 1.1190 + ORDER BY 1.1191 + "winning_initiative_id" NULLS LAST, 1.1192 + "losing_initiative_id" NULLS LAST 1.1193 + LOOP 1.1194 + "vote_matrix"["i"]["j"] := "battle_row"."count"; 1.1195 + IF "j" = "dimension_v" THEN 1.1196 + "i" := "i" + 1; 1.1197 + "j" := 1; 1.1198 + ELSE 1.1199 + "j" := "j" + 1; 1.1200 + IF "j" = "i" THEN 1.1201 + "j" := "j" + 1; 1.1202 + END IF; 1.1203 + END IF; 1.1204 + END LOOP; 1.1205 + IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 1.1206 + RAISE EXCEPTION 'Wrong battle count (should not happen)'; 1.1207 + END IF; 1.1208 + -- Store defeat strengths in "matrix" using "defeat_strength" 1.1209 + -- function: 1.1210 + "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); 1.1211 + "i" := 1; 1.1212 + LOOP 1.1213 + "j" := 1; 1.1214 + LOOP 1.1215 + IF "i" != "j" THEN 1.1216 + "matrix"["i"]["j"] := "defeat_strength"( 1.1217 + "vote_matrix"["i"]["j"], 1.1218 + "vote_matrix"["j"]["i"] 1.1219 + ); 1.1220 + END IF; 1.1221 + EXIT WHEN "j" = "dimension_v"; 1.1222 + "j" := "j" + 1; 1.1223 + END LOOP; 1.1224 + EXIT WHEN "i" = "dimension_v"; 1.1225 + "i" := "i" + 1; 1.1226 + END LOOP; 1.1227 + -- Find best paths: 1.1228 + "i" := 1; 1.1229 + LOOP 1.1230 + "j" := 1; 1.1231 + LOOP 1.1232 + IF "i" != "j" THEN 1.1233 + "k" := 1; 1.1234 + LOOP 1.1235 + IF "i" != "k" AND "j" != "k" THEN 1.1236 + IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN 1.1237 + IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN 1.1238 + "matrix"["j"]["k"] := "matrix"["j"]["i"]; 1.1239 + END IF; 1.1240 + ELSE 1.1241 + IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN 1.1242 + "matrix"["j"]["k"] := "matrix"["i"]["k"]; 1.1243 + END IF; 1.1244 + END IF; 1.1245 + END IF; 1.1246 + EXIT WHEN "k" = "dimension_v"; 1.1247 + "k" := "k" + 1; 1.1248 + END LOOP; 1.1249 + END IF; 1.1250 + EXIT WHEN "j" = "dimension_v"; 1.1251 + "j" := "j" + 1; 1.1252 + END LOOP; 1.1253 + EXIT WHEN "i" = "dimension_v"; 1.1254 + "i" := "i" + 1; 1.1255 + END LOOP; 1.1256 + -- Determine order of winners: 1.1257 + "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); 1.1258 + "rank_v" := 1; 1.1259 + "done_v" := 0; 1.1260 + LOOP 1.1261 + "winners_ary" := '{}'; 1.1262 + "i" := 1; 1.1263 + LOOP 1.1264 + IF "rank_ary"["i"] ISNULL THEN 1.1265 + "j" := 1; 1.1266 + LOOP 1.1267 + IF 1.1268 + "i" != "j" AND 1.1269 + "rank_ary"["j"] ISNULL AND 1.1270 + "matrix"["j"]["i"] > "matrix"["i"]["j"] 1.1271 + THEN 1.1272 + -- someone else is better 1.1273 + EXIT; 1.1274 + END IF; 1.1275 + IF "j" = "dimension_v" THEN 1.1276 + -- noone is better 1.1277 + "winners_ary" := "winners_ary" || "i"; 1.1278 + EXIT; 1.1279 + END IF; 1.1280 + "j" := "j" + 1; 1.1281 + END LOOP; 1.1282 + END IF; 1.1283 + EXIT WHEN "i" = "dimension_v"; 1.1284 + "i" := "i" + 1; 1.1285 + END LOOP; 1.1286 + "i" := 1; 1.1287 + LOOP 1.1288 + "rank_ary"["winners_ary"["i"]] := "rank_v"; 1.1289 + "done_v" := "done_v" + 1; 1.1290 + EXIT WHEN "i" = array_upper("winners_ary", 1); 1.1291 + "i" := "i" + 1; 1.1292 + END LOOP; 1.1293 + EXIT WHEN "done_v" = "dimension_v"; 1.1294 + "rank_v" := "rank_v" + 1; 1.1295 + END LOOP; 1.1296 + -- write preliminary results: 1.1297 + "i" := 1; 1.1298 + FOR "initiative_id_v" IN 1.1299 + SELECT "id" FROM "initiative" 1.1300 + WHERE "issue_id" = "issue_id_p" AND "admitted" 1.1301 + ORDER BY "id" 1.1302 + LOOP 1.1303 + UPDATE "initiative" SET 1.1304 + "direct_majority" = 1.1305 + CASE WHEN "policy_row"."direct_majority_strict" THEN 1.1306 + "positive_votes" * "policy_row"."direct_majority_den" > 1.1307 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 1.1308 + ELSE 1.1309 + "positive_votes" * "policy_row"."direct_majority_den" >= 1.1310 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 1.1311 + END 1.1312 + AND "positive_votes" >= "policy_row"."direct_majority_positive" 1.1313 + AND "issue_row"."voter_count"-"negative_votes" >= 1.1314 + "policy_row"."direct_majority_non_negative", 1.1315 + "indirect_majority" = 1.1316 + CASE WHEN "policy_row"."indirect_majority_strict" THEN 1.1317 + "positive_votes" * "policy_row"."indirect_majority_den" > 1.1318 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 1.1319 + ELSE 1.1320 + "positive_votes" * "policy_row"."indirect_majority_den" >= 1.1321 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 1.1322 + END 1.1323 + AND "positive_votes" >= "policy_row"."indirect_majority_positive" 1.1324 + AND "issue_row"."voter_count"-"negative_votes" >= 1.1325 + "policy_row"."indirect_majority_non_negative", 1.1326 + "schulze_rank" = "rank_ary"["i"], 1.1327 + "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 1.1328 + "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 1.1329 + "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], 1.1330 + "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, 1.1331 + "eligible" = FALSE, 1.1332 + "winner" = FALSE, 1.1333 + "rank" = NULL -- NOTE: in cases of manual reset of issue state 1.1334 + WHERE "id" = "initiative_id_v"; 1.1335 + "i" := "i" + 1; 1.1336 + END LOOP; 1.1337 + IF "i" != "dimension_v" THEN 1.1338 + RAISE EXCEPTION 'Wrong winner count (should not happen)'; 1.1339 + END IF; 1.1340 + -- take indirect majorities into account: 1.1341 + LOOP 1.1342 + UPDATE "initiative" SET "indirect_majority" = TRUE 1.1343 + FROM ( 1.1344 + SELECT "new_initiative"."id" AS "initiative_id" 1.1345 + FROM "initiative" "old_initiative" 1.1346 + JOIN "initiative" "new_initiative" 1.1347 + ON "new_initiative"."issue_id" = "issue_id_p" 1.1348 + AND "new_initiative"."indirect_majority" = FALSE 1.1349 + JOIN "battle" "battle_win" 1.1350 + ON "battle_win"."issue_id" = "issue_id_p" 1.1351 + AND "battle_win"."winning_initiative_id" = "new_initiative"."id" 1.1352 + AND "battle_win"."losing_initiative_id" = "old_initiative"."id" 1.1353 + JOIN "battle" "battle_lose" 1.1354 + ON "battle_lose"."issue_id" = "issue_id_p" 1.1355 + AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" 1.1356 + AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" 1.1357 + WHERE "old_initiative"."issue_id" = "issue_id_p" 1.1358 + AND "old_initiative"."indirect_majority" = TRUE 1.1359 + AND CASE WHEN "policy_row"."indirect_majority_strict" THEN 1.1360 + "battle_win"."count" * "policy_row"."indirect_majority_den" > 1.1361 + "policy_row"."indirect_majority_num" * 1.1362 + ("battle_win"."count"+"battle_lose"."count") 1.1363 + ELSE 1.1364 + "battle_win"."count" * "policy_row"."indirect_majority_den" >= 1.1365 + "policy_row"."indirect_majority_num" * 1.1366 + ("battle_win"."count"+"battle_lose"."count") 1.1367 + END 1.1368 + AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" 1.1369 + AND "issue_row"."voter_count"-"battle_lose"."count" >= 1.1370 + "policy_row"."indirect_majority_non_negative" 1.1371 + ) AS "subquery" 1.1372 + WHERE "id" = "subquery"."initiative_id"; 1.1373 + EXIT WHEN NOT FOUND; 1.1374 + END LOOP; 1.1375 + -- set "multistage_majority" for remaining matching initiatives: 1.1376 + UPDATE "initiative" SET "multistage_majority" = TRUE 1.1377 + FROM ( 1.1378 + SELECT "losing_initiative"."id" AS "initiative_id" 1.1379 + FROM "initiative" "losing_initiative" 1.1380 + JOIN "initiative" "winning_initiative" 1.1381 + ON "winning_initiative"."issue_id" = "issue_id_p" 1.1382 + AND "winning_initiative"."admitted" 1.1383 + JOIN "battle" "battle_win" 1.1384 + ON "battle_win"."issue_id" = "issue_id_p" 1.1385 + AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 1.1386 + AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 1.1387 + JOIN "battle" "battle_lose" 1.1388 + ON "battle_lose"."issue_id" = "issue_id_p" 1.1389 + AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 1.1390 + AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 1.1391 + WHERE "losing_initiative"."issue_id" = "issue_id_p" 1.1392 + AND "losing_initiative"."admitted" 1.1393 + AND "winning_initiative"."schulze_rank" < 1.1394 + "losing_initiative"."schulze_rank" 1.1395 + AND "battle_win"."count" > "battle_lose"."count" 1.1396 + AND ( 1.1397 + "battle_win"."count" > "winning_initiative"."positive_votes" OR 1.1398 + "battle_lose"."count" < "losing_initiative"."negative_votes" ) 1.1399 + ) AS "subquery" 1.1400 + WHERE "id" = "subquery"."initiative_id"; 1.1401 + -- mark eligible initiatives: 1.1402 + UPDATE "initiative" SET "eligible" = TRUE 1.1403 + WHERE "issue_id" = "issue_id_p" 1.1404 + AND "initiative"."direct_majority" 1.1405 + AND "initiative"."indirect_majority" 1.1406 + AND "initiative"."better_than_status_quo" 1.1407 + AND ( 1.1408 + "policy_row"."no_multistage_majority" = FALSE OR 1.1409 + "initiative"."multistage_majority" = FALSE ) 1.1410 + AND ( 1.1411 + "policy_row"."no_reverse_beat_path" = FALSE OR 1.1412 + "initiative"."reverse_beat_path" = FALSE ); 1.1413 + -- mark final winner: 1.1414 + UPDATE "initiative" SET "winner" = TRUE 1.1415 + FROM ( 1.1416 + SELECT "id" AS "initiative_id" 1.1417 + FROM "initiative" 1.1418 + WHERE "issue_id" = "issue_id_p" AND "eligible" 1.1419 + ORDER BY 1.1420 + "schulze_rank", 1.1421 + "id" 1.1422 + LIMIT 1 1.1423 + ) AS "subquery" 1.1424 + WHERE "id" = "subquery"."initiative_id"; 1.1425 + -- write (final) ranks: 1.1426 + "rank_v" := 1; 1.1427 + FOR "initiative_id_v" IN 1.1428 + SELECT "id" 1.1429 + FROM "initiative" 1.1430 + WHERE "issue_id" = "issue_id_p" AND "admitted" 1.1431 + ORDER BY 1.1432 + "winner" DESC, 1.1433 + "eligible" DESC, 1.1434 + "schulze_rank", 1.1435 + "id" 1.1436 + LOOP 1.1437 + UPDATE "initiative" SET "rank" = "rank_v" 1.1438 + WHERE "id" = "initiative_id_v"; 1.1439 + "rank_v" := "rank_v" + 1; 1.1440 + END LOOP; 1.1441 + -- set schulze rank of status quo and mark issue as finished: 1.1442 + UPDATE "issue" SET 1.1443 + "status_quo_schulze_rank" = "rank_ary"["dimension_v"], 1.1444 + "state" = 1.1445 + CASE WHEN EXISTS ( 1.1446 + SELECT NULL FROM "initiative" 1.1447 + WHERE "issue_id" = "issue_id_p" AND "winner" 1.1448 + ) THEN 1.1449 + 'finished_with_winner'::"issue_state" 1.1450 + ELSE 1.1451 + 'finished_without_winner'::"issue_state" 1.1452 + END, 1.1453 + "closed" = "phase_finished", 1.1454 + "phase_finished" = NULL 1.1455 + WHERE "id" = "issue_id_p"; 1.1456 + RETURN; 1.1457 + END; 1.1458 + $$; 1.1459 + 1.1460 +DROP FUNCTION "check_issue"("issue"."id"%TYPE); 1.1461 + 1.1462 +CREATE TYPE "check_issue_persistence" AS ( 1.1463 + "state" "issue_state", 1.1464 + "phase_finished" BOOLEAN, 1.1465 + "issue_revoked" BOOLEAN, 1.1466 + "snapshot_created" BOOLEAN, 1.1467 + "harmonic_weights_set" BOOLEAN, 1.1468 + "closed_voting" BOOLEAN ); 1.1469 +COMMENT ON TYPE "check_issue_persistence" IS 'Type of data returned by "check_issue" function, to be passed to subsequent calls of the same function'; 1.1470 + 1.1471 +CREATE FUNCTION "check_issue" 1.1472 + ( "issue_id_p" "issue"."id"%TYPE, 1.1473 + "persist" "check_issue_persistence" ) 1.1474 + RETURNS "check_issue_persistence" 1.1475 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1476 + DECLARE 1.1477 + "issue_row" "issue"%ROWTYPE; 1.1478 + "policy_row" "policy"%ROWTYPE; 1.1479 + "initiative_row" "initiative"%ROWTYPE; 1.1480 + "state_v" "issue_state"; 1.1481 + BEGIN 1.1482 + PERFORM "require_transaction_isolation"(); 1.1483 + IF "persist" ISNULL THEN 1.1484 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 1.1485 + FOR UPDATE; 1.1486 + IF "issue_row"."closed" NOTNULL THEN 1.1487 + RETURN NULL; 1.1488 + END IF; 1.1489 + "persist"."state" := "issue_row"."state"; 1.1490 + IF 1.1491 + ( "issue_row"."state" = 'admission' AND now() >= 1.1492 + "issue_row"."created" + "issue_row"."admission_time" ) OR 1.1493 + ( "issue_row"."state" = 'discussion' AND now() >= 1.1494 + "issue_row"."accepted" + "issue_row"."discussion_time" ) OR 1.1495 + ( "issue_row"."state" = 'verification' AND now() >= 1.1496 + "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR 1.1497 + ( "issue_row"."state" = 'voting' AND now() >= 1.1498 + "issue_row"."fully_frozen" + "issue_row"."voting_time" ) 1.1499 + THEN 1.1500 + "persist"."phase_finished" := TRUE; 1.1501 + ELSE 1.1502 + "persist"."phase_finished" := FALSE; 1.1503 + END IF; 1.1504 + IF 1.1505 + NOT EXISTS ( 1.1506 + -- all initiatives are revoked 1.1507 + SELECT NULL FROM "initiative" 1.1508 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 1.1509 + ) AND ( 1.1510 + -- and issue has not been accepted yet 1.1511 + "persist"."state" = 'admission' OR 1.1512 + -- or verification time has elapsed 1.1513 + ( "persist"."state" = 'verification' AND 1.1514 + "persist"."phase_finished" ) OR 1.1515 + -- or no initiatives have been revoked lately 1.1516 + NOT EXISTS ( 1.1517 + SELECT NULL FROM "initiative" 1.1518 + WHERE "issue_id" = "issue_id_p" 1.1519 + AND now() < "revoked" + "issue_row"."verification_time" 1.1520 + ) 1.1521 + ) 1.1522 + THEN 1.1523 + "persist"."issue_revoked" := TRUE; 1.1524 + ELSE 1.1525 + "persist"."issue_revoked" := FALSE; 1.1526 + END IF; 1.1527 + IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN 1.1528 + UPDATE "issue" SET "phase_finished" = now() 1.1529 + WHERE "id" = "issue_row"."id"; 1.1530 + RETURN "persist"; 1.1531 + ELSIF 1.1532 + "persist"."state" IN ('admission', 'discussion', 'verification') 1.1533 + THEN 1.1534 + RETURN "persist"; 1.1535 + ELSE 1.1536 + RETURN NULL; 1.1537 + END IF; 1.1538 + END IF; 1.1539 + IF 1.1540 + "persist"."state" IN ('admission', 'discussion', 'verification') AND 1.1541 + coalesce("persist"."snapshot_created", FALSE) = FALSE 1.1542 + THEN 1.1543 + PERFORM "create_snapshot"("issue_id_p"); 1.1544 + "persist"."snapshot_created" = TRUE; 1.1545 + IF "persist"."phase_finished" THEN 1.1546 + IF "persist"."state" = 'admission' THEN 1.1547 + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); 1.1548 + ELSIF "persist"."state" = 'discussion' THEN 1.1549 + PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); 1.1550 + ELSIF "persist"."state" = 'verification' THEN 1.1551 + PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze'); 1.1552 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 1.1553 + SELECT * INTO "policy_row" FROM "policy" 1.1554 + WHERE "id" = "issue_row"."policy_id"; 1.1555 + FOR "initiative_row" IN 1.1556 + SELECT * FROM "initiative" 1.1557 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 1.1558 + FOR UPDATE 1.1559 + LOOP 1.1560 + IF 1.1561 + "initiative_row"."polling" OR ( 1.1562 + "initiative_row"."satisfied_supporter_count" > 0 AND 1.1563 + "initiative_row"."satisfied_supporter_count" * 1.1564 + "policy_row"."initiative_quorum_den" >= 1.1565 + "issue_row"."population" * "policy_row"."initiative_quorum_num" 1.1566 + ) 1.1567 + THEN 1.1568 + UPDATE "initiative" SET "admitted" = TRUE 1.1569 + WHERE "id" = "initiative_row"."id"; 1.1570 + ELSE 1.1571 + UPDATE "initiative" SET "admitted" = FALSE 1.1572 + WHERE "id" = "initiative_row"."id"; 1.1573 + END IF; 1.1574 + END LOOP; 1.1575 + END IF; 1.1576 + END IF; 1.1577 + RETURN "persist"; 1.1578 + END IF; 1.1579 + IF 1.1580 + "persist"."state" IN ('admission', 'discussion', 'verification') AND 1.1581 + coalesce("persist"."harmonic_weights_set", FALSE) = FALSE 1.1582 + THEN 1.1583 + PERFORM "set_harmonic_initiative_weights"("issue_id_p"); 1.1584 + "persist"."harmonic_weights_set" = TRUE; 1.1585 + IF 1.1586 + "persist"."phase_finished" OR 1.1587 + "persist"."issue_revoked" OR 1.1588 + "persist"."state" = 'admission' 1.1589 + THEN 1.1590 + RETURN "persist"; 1.1591 + ELSE 1.1592 + RETURN NULL; 1.1593 + END IF; 1.1594 + END IF; 1.1595 + IF "persist"."issue_revoked" THEN 1.1596 + IF "persist"."state" = 'admission' THEN 1.1597 + "state_v" := 'canceled_revoked_before_accepted'; 1.1598 + ELSIF "persist"."state" = 'discussion' THEN 1.1599 + "state_v" := 'canceled_after_revocation_during_discussion'; 1.1600 + ELSIF "persist"."state" = 'verification' THEN 1.1601 + "state_v" := 'canceled_after_revocation_during_verification'; 1.1602 + END IF; 1.1603 + UPDATE "issue" SET 1.1604 + "state" = "state_v", 1.1605 + "closed" = "phase_finished", 1.1606 + "phase_finished" = NULL 1.1607 + WHERE "id" = "issue_id_p"; 1.1608 + RETURN NULL; 1.1609 + END IF; 1.1610 + IF "persist"."state" = 'admission' THEN 1.1611 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 1.1612 + FOR UPDATE; 1.1613 + SELECT * INTO "policy_row" 1.1614 + FROM "policy" WHERE "id" = "issue_row"."policy_id"; 1.1615 + IF EXISTS ( 1.1616 + SELECT NULL FROM "initiative" 1.1617 + WHERE "issue_id" = "issue_id_p" 1.1618 + AND "supporter_count" > 0 1.1619 + AND "supporter_count" * "policy_row"."issue_quorum_den" 1.1620 + >= "issue_row"."population" * "policy_row"."issue_quorum_num" 1.1621 + ) THEN 1.1622 + UPDATE "issue" SET 1.1623 + "state" = 'discussion', 1.1624 + "accepted" = coalesce("phase_finished", now()), 1.1625 + "phase_finished" = NULL 1.1626 + WHERE "id" = "issue_id_p"; 1.1627 + ELSIF "issue_row"."phase_finished" NOTNULL THEN 1.1628 + UPDATE "issue" SET 1.1629 + "state" = 'canceled_issue_not_accepted', 1.1630 + "closed" = "phase_finished", 1.1631 + "phase_finished" = NULL 1.1632 + WHERE "id" = "issue_id_p"; 1.1633 + END IF; 1.1634 + RETURN NULL; 1.1635 + END IF; 1.1636 + IF "persist"."phase_finished" THEN 1.1637 + if "persist"."state" = 'discussion' THEN 1.1638 + UPDATE "issue" SET 1.1639 + "state" = 'verification', 1.1640 + "half_frozen" = "phase_finished", 1.1641 + "phase_finished" = NULL 1.1642 + WHERE "id" = "issue_id_p"; 1.1643 + RETURN NULL; 1.1644 + END IF; 1.1645 + IF "persist"."state" = 'verification' THEN 1.1646 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 1.1647 + FOR UPDATE; 1.1648 + SELECT * INTO "policy_row" FROM "policy" 1.1649 + WHERE "id" = "issue_row"."policy_id"; 1.1650 + IF EXISTS ( 1.1651 + SELECT NULL FROM "initiative" 1.1652 + WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE 1.1653 + ) THEN 1.1654 + UPDATE "issue" SET 1.1655 + "state" = 'voting', 1.1656 + "accepted" = coalesce("accepted", "phase_finished"), 1.1657 + "half_frozen" = coalesce("half_frozen", "phase_finished"), 1.1658 + "fully_frozen" = "phase_finished", 1.1659 + "phase_finished" = NULL 1.1660 + WHERE "id" = "issue_id_p"; 1.1661 + ELSE 1.1662 + UPDATE "issue" SET 1.1663 + "state" = 'canceled_no_initiative_admitted', 1.1664 + "accepted" = coalesce("accepted", "phase_finished"), 1.1665 + "half_frozen" = coalesce("half_frozen", "phase_finished"), 1.1666 + "fully_frozen" = "phase_finished", 1.1667 + "closed" = "phase_finished", 1.1668 + "phase_finished" = NULL 1.1669 + WHERE "id" = "issue_id_p"; 1.1670 + -- NOTE: The following DELETE statements have effect only when 1.1671 + -- issue state has been manipulated 1.1672 + DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p"; 1.1673 + DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p"; 1.1674 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.1675 + END IF; 1.1676 + RETURN NULL; 1.1677 + END IF; 1.1678 + IF "persist"."state" = 'voting' THEN 1.1679 + IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN 1.1680 + PERFORM "close_voting"("issue_id_p"); 1.1681 + "persist"."closed_voting" = TRUE; 1.1682 + RETURN "persist"; 1.1683 + END IF; 1.1684 + PERFORM "calculate_ranks"("issue_id_p"); 1.1685 + RETURN NULL; 1.1686 + END IF; 1.1687 + END IF; 1.1688 + RAISE WARNING 'should not happen'; 1.1689 + RETURN NULL; 1.1690 + END; 1.1691 + $$; 1.1692 +COMMENT ON FUNCTION "check_issue" 1.1693 + ( "issue"."id"%TYPE, 1.1694 + "check_issue_persistence" ) 1.1695 + IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required, and perform the status change when necessary; Function must be called multiple times with the previous result as second parameter, until the result is NULL (see source code of function "check_everything")'; 1.1696 + 1.1697 +CREATE OR REPLACE FUNCTION "check_everything"() 1.1698 + RETURNS VOID 1.1699 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1700 + DECLARE 1.1701 + "issue_id_v" "issue"."id"%TYPE; 1.1702 + "persist_v" "check_issue_persistence"; 1.1703 + BEGIN 1.1704 + RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes'; 1.1705 + DELETE FROM "expired_session"; 1.1706 + PERFORM "check_activity"(); 1.1707 + PERFORM "calculate_member_counts"(); 1.1708 + FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP 1.1709 + "persist_v" := NULL; 1.1710 + LOOP 1.1711 + "persist_v" := "check_issue"("issue_id_v", "persist_v"); 1.1712 + EXIT WHEN "persist_v" ISNULL; 1.1713 + END LOOP; 1.1714 + END LOOP; 1.1715 + RETURN; 1.1716 + END; 1.1717 + $$; 1.1718 +COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue. Use this function only for development and debugging purposes, as you may run into locking and/or serialization problems in productive environments.'; 1.1719 + 1.1720 +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) 1.1721 + RETURNS VOID 1.1722 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.1723 + DECLARE 1.1724 + "issue_row" "issue"%ROWTYPE; 1.1725 + BEGIN 1.1726 + SELECT * INTO "issue_row" 1.1727 + FROM "issue" WHERE "id" = "issue_id_p" 1.1728 + FOR UPDATE; 1.1729 + IF "issue_row"."cleaned" ISNULL THEN 1.1730 + UPDATE "issue" SET 1.1731 + "state" = 'voting', 1.1732 + "closed" = NULL 1.1733 + WHERE "id" = "issue_id_p"; 1.1734 + DELETE FROM "delegating_voter" 1.1735 + WHERE "issue_id" = "issue_id_p"; 1.1736 + DELETE FROM "direct_voter" 1.1737 + WHERE "issue_id" = "issue_id_p"; 1.1738 + DELETE FROM "delegating_interest_snapshot" 1.1739 + WHERE "issue_id" = "issue_id_p"; 1.1740 + DELETE FROM "direct_interest_snapshot" 1.1741 + WHERE "issue_id" = "issue_id_p"; 1.1742 + DELETE FROM "delegating_population_snapshot" 1.1743 + WHERE "issue_id" = "issue_id_p"; 1.1744 + DELETE FROM "direct_population_snapshot" 1.1745 + WHERE "issue_id" = "issue_id_p"; 1.1746 + DELETE FROM "non_voter" 1.1747 + WHERE "issue_id" = "issue_id_p"; 1.1748 + DELETE FROM "delegation" 1.1749 + WHERE "issue_id" = "issue_id_p"; 1.1750 + DELETE FROM "supporter" 1.1751 + USING "initiative" -- NOTE: due to missing index on issue_id 1.1752 + WHERE "initiative"."issue_id" = "issue_id_p" 1.1753 + AND "supporter"."initiative_id" = "initiative_id"; 1.1754 + UPDATE "issue" SET 1.1755 + "state" = "issue_row"."state", 1.1756 + "closed" = "issue_row"."closed", 1.1757 + "cleaned" = now() 1.1758 + WHERE "id" = "issue_id_p"; 1.1759 + END IF; 1.1760 + RETURN; 1.1761 + END; 1.1762 + $$; 1.1763 + 1.1764 +COMMIT;