jbe@339: BEGIN; jbe@339: jbe@347: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; jbe@347: jbe@339: CREATE OR REPLACE VIEW "liquid_feedback_version" AS jbe@339: SELECT * FROM (VALUES ('2.2.0', 2, 2, 0)) jbe@339: AS "subquery"("string", "major", "minor", "revision"); jbe@339: jbe@339: DROP VIEW "issue_with_ranks_missing"; jbe@339: DROP VIEW "open_issue"; -- recreated later jbe@339: DROP VIEW "event_seen_by_member"; -- recreated later jbe@339: DROP VIEW "selected_event_seen_by_member"; -- recreated later jbe@339: ALTER TABLE "issue" DROP CONSTRAINT "valid_state"; jbe@339: ALTER TABLE "issue" DROP COLUMN "ranks_available"; jbe@339: ALTER TABLE "event" DROP CONSTRAINT "event_state_check"; jbe@339: ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_issue_state_changed"; -- recreated later jbe@339: ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft"; -- recreated later jbe@339: ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_suggestion_creation"; -- recreated later jbe@339: ALTER TYPE "issue_state" RENAME TO "issue_state_old"; jbe@339: CREATE TYPE "issue_state" AS ENUM ( jbe@339: 'admission', 'discussion', 'verification', 'voting', jbe@339: 'canceled_revoked_before_accepted', jbe@339: 'canceled_issue_not_accepted', jbe@339: 'canceled_after_revocation_during_discussion', jbe@339: 'canceled_after_revocation_during_verification', jbe@339: 'canceled_no_initiative_admitted', jbe@339: 'finished_without_winner', 'finished_with_winner'); jbe@339: ALTER TABLE "issue" ALTER COLUMN "state" DROP DEFAULT; jbe@339: ALTER TABLE "issue" ALTER COLUMN "state" TYPE "issue_state" USING "state"::text::"issue_state"; jbe@339: ALTER TABLE "event" ALTER COLUMN "state" TYPE "issue_state" USING "state"::text::"issue_state"; jbe@339: DROP TYPE "issue_state_old"; jbe@339: ALTER TABLE "issue" ALTER COLUMN "state" SET DEFAULT 'admission'; jbe@339: ALTER TABLE "issue" ADD CONSTRAINT "valid_state" jbe@339: CHECK (( jbe@339: ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR jbe@339: ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR jbe@339: ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR jbe@339: ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL) jbe@339: ) AND ( jbe@339: ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR jbe@339: ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR jbe@339: ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR jbe@339: ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR jbe@339: ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR jbe@339: ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR jbe@339: ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR jbe@339: ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR jbe@339: ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR jbe@339: ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR jbe@339: ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") jbe@339: )); jbe@339: ALTER TABLE "issue" ADD COLUMN "phase_finished" TIMESTAMPTZ; jbe@339: 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'; jbe@339: ALTER TABLE "issue" ADD CONSTRAINT "phase_finished_only_when_not_closed" jbe@339: CHECK ("phase_finished" ISNULL OR "closed" ISNULL); jbe@339: ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_issue_state_changed" CHECK ( jbe@339: "event" != 'issue_state_changed' OR ( jbe@339: "member_id" ISNULL AND jbe@339: "issue_id" NOTNULL AND jbe@339: "state" NOTNULL AND jbe@339: "initiative_id" ISNULL AND jbe@339: "draft_id" ISNULL AND jbe@339: "suggestion_id" ISNULL )); jbe@339: ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK ( jbe@339: "event" NOT IN ( jbe@339: 'initiative_created_in_new_issue', jbe@339: 'initiative_created_in_existing_issue', jbe@339: 'initiative_revoked', jbe@339: 'new_draft_created' jbe@339: ) OR ( jbe@339: "member_id" NOTNULL AND jbe@339: "issue_id" NOTNULL AND jbe@339: "state" NOTNULL AND jbe@339: "initiative_id" NOTNULL AND jbe@339: "draft_id" NOTNULL AND jbe@339: "suggestion_id" ISNULL )); jbe@339: ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_suggestion_creation" CHECK ( jbe@339: "event" != 'suggestion_created' OR ( jbe@339: "member_id" NOTNULL AND jbe@339: "issue_id" NOTNULL AND jbe@339: "state" NOTNULL AND jbe@339: "initiative_id" NOTNULL AND jbe@339: "draft_id" ISNULL AND jbe@339: "suggestion_id" NOTNULL )); jbe@339: CREATE VIEW "open_issue" AS jbe@339: SELECT * FROM "issue" WHERE "closed" ISNULL; jbe@339: COMMENT ON VIEW "open_issue" IS 'All open issues'; jbe@339: CREATE VIEW "event_seen_by_member" AS jbe@339: SELECT jbe@339: "member"."id" AS "seen_by_member_id", jbe@339: CASE WHEN "event"."state" IN ( jbe@339: 'voting', jbe@339: 'finished_without_winner', jbe@339: 'finished_with_winner' jbe@339: ) THEN jbe@339: 'voting'::"notify_level" jbe@339: ELSE jbe@339: CASE WHEN "event"."state" IN ( jbe@339: 'verification', jbe@339: 'canceled_after_revocation_during_verification', jbe@339: 'canceled_no_initiative_admitted' jbe@339: ) THEN jbe@339: 'verification'::"notify_level" jbe@339: ELSE jbe@339: CASE WHEN "event"."state" IN ( jbe@339: 'discussion', jbe@339: 'canceled_after_revocation_during_discussion' jbe@339: ) THEN jbe@339: 'discussion'::"notify_level" jbe@339: ELSE jbe@339: 'all'::"notify_level" jbe@339: END jbe@339: END jbe@339: END AS "notify_level", jbe@339: "event".* jbe@339: FROM "member" CROSS JOIN "event" jbe@339: LEFT JOIN "issue" jbe@339: ON "event"."issue_id" = "issue"."id" jbe@339: LEFT JOIN "membership" jbe@339: ON "member"."id" = "membership"."member_id" jbe@339: AND "issue"."area_id" = "membership"."area_id" jbe@339: LEFT JOIN "interest" jbe@339: ON "member"."id" = "interest"."member_id" jbe@339: AND "event"."issue_id" = "interest"."issue_id" jbe@339: LEFT JOIN "supporter" jbe@339: ON "member"."id" = "supporter"."member_id" jbe@339: AND "event"."initiative_id" = "supporter"."initiative_id" jbe@339: LEFT JOIN "ignored_member" jbe@339: ON "member"."id" = "ignored_member"."member_id" jbe@339: AND "event"."member_id" = "ignored_member"."other_member_id" jbe@339: LEFT JOIN "ignored_initiative" jbe@339: ON "member"."id" = "ignored_initiative"."member_id" jbe@339: AND "event"."initiative_id" = "ignored_initiative"."initiative_id" jbe@339: WHERE ( jbe@339: "supporter"."member_id" NOTNULL OR jbe@339: "interest"."member_id" NOTNULL OR jbe@339: ( "membership"."member_id" NOTNULL AND jbe@339: "event"."event" IN ( jbe@339: 'issue_state_changed', jbe@339: 'initiative_created_in_new_issue', jbe@339: 'initiative_created_in_existing_issue', jbe@339: 'initiative_revoked' ) ) ) jbe@339: AND "ignored_member"."member_id" ISNULL jbe@339: AND "ignored_initiative"."member_id" ISNULL; jbe@339: 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"'; jbe@339: CREATE VIEW "selected_event_seen_by_member" AS jbe@339: SELECT jbe@339: "member"."id" AS "seen_by_member_id", jbe@339: CASE WHEN "event"."state" IN ( jbe@339: 'voting', jbe@339: 'finished_without_winner', jbe@339: 'finished_with_winner' jbe@339: ) THEN jbe@339: 'voting'::"notify_level" jbe@339: ELSE jbe@339: CASE WHEN "event"."state" IN ( jbe@339: 'verification', jbe@339: 'canceled_after_revocation_during_verification', jbe@339: 'canceled_no_initiative_admitted' jbe@339: ) THEN jbe@339: 'verification'::"notify_level" jbe@339: ELSE jbe@339: CASE WHEN "event"."state" IN ( jbe@339: 'discussion', jbe@339: 'canceled_after_revocation_during_discussion' jbe@339: ) THEN jbe@339: 'discussion'::"notify_level" jbe@339: ELSE jbe@339: 'all'::"notify_level" jbe@339: END jbe@339: END jbe@339: END AS "notify_level", jbe@339: "event".* jbe@339: FROM "member" CROSS JOIN "event" jbe@339: LEFT JOIN "issue" jbe@339: ON "event"."issue_id" = "issue"."id" jbe@339: LEFT JOIN "membership" jbe@339: ON "member"."id" = "membership"."member_id" jbe@339: AND "issue"."area_id" = "membership"."area_id" jbe@339: LEFT JOIN "interest" jbe@339: ON "member"."id" = "interest"."member_id" jbe@339: AND "event"."issue_id" = "interest"."issue_id" jbe@339: LEFT JOIN "supporter" jbe@339: ON "member"."id" = "supporter"."member_id" jbe@339: AND "event"."initiative_id" = "supporter"."initiative_id" jbe@339: LEFT JOIN "ignored_member" jbe@339: ON "member"."id" = "ignored_member"."member_id" jbe@339: AND "event"."member_id" = "ignored_member"."other_member_id" jbe@339: LEFT JOIN "ignored_initiative" jbe@339: ON "member"."id" = "ignored_initiative"."member_id" jbe@339: AND "event"."initiative_id" = "ignored_initiative"."initiative_id" jbe@339: WHERE ( jbe@339: ( "member"."notify_level" >= 'all' ) OR jbe@339: ( "member"."notify_level" >= 'voting' AND jbe@339: "event"."state" IN ( jbe@339: 'voting', jbe@339: 'finished_without_winner', jbe@339: 'finished_with_winner' ) ) OR jbe@339: ( "member"."notify_level" >= 'verification' AND jbe@339: "event"."state" IN ( jbe@339: 'verification', jbe@339: 'canceled_after_revocation_during_verification', jbe@339: 'canceled_no_initiative_admitted' ) ) OR jbe@339: ( "member"."notify_level" >= 'discussion' AND jbe@339: "event"."state" IN ( jbe@339: 'discussion', jbe@339: 'canceled_after_revocation_during_discussion' ) ) ) jbe@339: AND ( jbe@339: "supporter"."member_id" NOTNULL OR jbe@339: "interest"."member_id" NOTNULL OR jbe@339: ( "membership"."member_id" NOTNULL AND jbe@339: "event"."event" IN ( jbe@339: 'issue_state_changed', jbe@339: 'initiative_created_in_new_issue', jbe@339: 'initiative_created_in_existing_issue', jbe@339: 'initiative_revoked' ) ) ) jbe@339: AND "ignored_member"."member_id" ISNULL jbe@339: AND "ignored_initiative"."member_id" ISNULL; jbe@339: 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"'; jbe@339: jbe@339: ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3); jbe@339: 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'; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"() jbe@339: RETURNS TRIGGER jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: BEGIN jbe@339: IF NEW."state" != OLD."state" THEN jbe@339: INSERT INTO "event" ("event", "issue_id", "state") jbe@339: VALUES ('issue_state_changed', NEW."id", NEW."state"); jbe@339: END IF; jbe@339: RETURN NULL; jbe@339: END; jbe@339: $$; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"() jbe@339: RETURNS TRIGGER jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "issue_id_v" "issue"."id"%TYPE; jbe@339: "issue_row" "issue"%ROWTYPE; jbe@339: BEGIN jbe@339: IF TG_OP = 'DELETE' THEN jbe@339: "issue_id_v" := OLD."issue_id"; jbe@339: ELSE jbe@339: "issue_id_v" := NEW."issue_id"; jbe@339: END IF; jbe@339: SELECT INTO "issue_row" * FROM "issue" jbe@339: WHERE "id" = "issue_id_v" FOR SHARE; jbe@339: IF "issue_row"."closed" NOTNULL THEN jbe@339: IF jbe@339: TG_RELID = 'direct_voter'::regclass AND jbe@339: TG_OP = 'UPDATE' jbe@339: THEN jbe@339: IF jbe@339: OLD."issue_id" = NEW."issue_id" AND jbe@339: OLD."member_id" = NEW."member_id" AND jbe@339: OLD."weight" = NEW."weight" jbe@339: THEN jbe@339: RETURN NULL; -- allows changing of voter comment jbe@339: END IF; jbe@339: END IF; jbe@339: RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.'; jbe@339: ELSIF jbe@339: "issue_row"."state" = 'voting' AND jbe@339: "issue_row"."phase_finished" NOTNULL jbe@339: THEN jbe@339: IF TG_RELID = 'vote'::regclass THEN jbe@339: RAISE EXCEPTION 'Tried to modify data after voting has been closed.'; jbe@339: END IF; jbe@339: END IF; jbe@339: RETURN NULL; jbe@339: END; jbe@339: $$; jbe@339: jbe@339: CREATE OR REPLACE VIEW "battle_view" AS jbe@339: SELECT jbe@339: "issue"."id" AS "issue_id", jbe@339: "winning_initiative"."id" AS "winning_initiative_id", jbe@339: "losing_initiative"."id" AS "losing_initiative_id", jbe@339: sum( jbe@339: CASE WHEN jbe@339: coalesce("better_vote"."grade", 0) > jbe@339: coalesce("worse_vote"."grade", 0) jbe@339: THEN "direct_voter"."weight" ELSE 0 END jbe@339: ) AS "count" jbe@339: FROM "issue" jbe@339: LEFT JOIN "direct_voter" jbe@339: ON "issue"."id" = "direct_voter"."issue_id" jbe@339: JOIN "battle_participant" AS "winning_initiative" jbe@339: ON "issue"."id" = "winning_initiative"."issue_id" jbe@339: JOIN "battle_participant" AS "losing_initiative" jbe@339: ON "issue"."id" = "losing_initiative"."issue_id" jbe@339: LEFT JOIN "vote" AS "better_vote" jbe@339: ON "direct_voter"."member_id" = "better_vote"."member_id" jbe@339: AND "winning_initiative"."id" = "better_vote"."initiative_id" jbe@339: LEFT JOIN "vote" AS "worse_vote" jbe@339: ON "direct_voter"."member_id" = "worse_vote"."member_id" jbe@339: AND "losing_initiative"."id" = "worse_vote"."initiative_id" jbe@339: WHERE "issue"."state" = 'voting' jbe@339: AND "issue"."phase_finished" NOTNULL jbe@339: AND ( jbe@339: "winning_initiative"."id" != "losing_initiative"."id" OR jbe@339: ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR jbe@339: ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) ) jbe@339: GROUP BY jbe@339: "issue"."id", jbe@339: "winning_initiative"."id", jbe@339: "losing_initiative"."id"; jbe@339: jbe@339: DROP VIEW "timeline"; jbe@339: DROP VIEW "timeline_issue"; jbe@339: DROP VIEW "timeline_initiative"; jbe@339: DROP VIEW "timeline_draft"; jbe@339: DROP VIEW "timeline_suggestion"; jbe@339: DROP TYPE "timeline_event"; jbe@339: jbe@339: DROP TRIGGER "share_row_lock_issue" ON "initiative"; jbe@339: DROP TRIGGER "share_row_lock_issue" ON "interest"; jbe@339: DROP TRIGGER "share_row_lock_issue" ON "supporter"; jbe@339: DROP TRIGGER "share_row_lock_issue_via_initiative" ON "opinion"; jbe@339: DROP TRIGGER "share_row_lock_issue" ON "direct_voter"; jbe@339: DROP TRIGGER "share_row_lock_issue" ON "delegating_voter"; jbe@339: DROP TRIGGER "share_row_lock_issue" ON "vote"; jbe@339: DROP FUNCTION "share_row_lock_issue_trigger"(); jbe@339: jbe@339: CREATE FUNCTION "require_transaction_isolation"() jbe@339: RETURNS VOID jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: BEGIN jbe@339: IF jbe@339: current_setting('transaction_isolation') NOT IN jbe@339: ('repeatable read', 'serializable') jbe@339: THEN jbe@339: RAISE EXCEPTION 'Insufficient transaction isolation level'; jbe@339: END IF; jbe@339: RETURN; jbe@339: END; jbe@339: $$; jbe@344: COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot'; jbe@339: jbe@339: CREATE FUNCTION "dont_require_transaction_isolation"() jbe@339: RETURNS VOID jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: BEGIN jbe@339: IF jbe@339: current_setting('transaction_isolation') IN jbe@339: ('repeatable read', 'serializable') jbe@339: THEN jbe@339: RAISE WARNING 'Unneccessary transaction isolation level: %', jbe@339: current_setting('transaction_isolation'); jbe@339: END IF; jbe@339: RETURN; jbe@339: END; jbe@339: $$; jbe@344: COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED'; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "check_activity"() jbe@339: RETURNS VOID jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "system_setting_row" "system_setting"%ROWTYPE; jbe@339: BEGIN jbe@339: PERFORM "dont_require_transaction_isolation"(); jbe@339: SELECT * INTO "system_setting_row" FROM "system_setting"; jbe@339: IF "system_setting_row"."member_ttl" NOTNULL THEN jbe@339: UPDATE "member" SET "active" = FALSE jbe@339: WHERE "active" = TRUE jbe@339: AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE; jbe@339: END IF; jbe@339: RETURN; jbe@339: END; jbe@339: $$; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "calculate_member_counts"() jbe@339: RETURNS VOID jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: BEGIN jbe@339: PERFORM "require_transaction_isolation"(); jbe@339: DELETE FROM "member_count"; jbe@339: INSERT INTO "member_count" ("total_count") jbe@339: SELECT "total_count" FROM "member_count_view"; jbe@339: UPDATE "unit" SET "member_count" = "view"."member_count" jbe@339: FROM "unit_member_count" AS "view" jbe@339: WHERE "view"."unit_id" = "unit"."id"; jbe@339: UPDATE "area" SET jbe@339: "direct_member_count" = "view"."direct_member_count", jbe@339: "member_weight" = "view"."member_weight" jbe@339: FROM "area_member_count" AS "view" jbe@339: WHERE "view"."area_id" = "area"."id"; jbe@339: RETURN; jbe@339: END; jbe@339: $$; jbe@339: jbe@339: CREATE VIEW "remaining_harmonic_supporter_weight" AS jbe@339: SELECT jbe@339: "direct_interest_snapshot"."issue_id", jbe@339: "direct_interest_snapshot"."event", jbe@339: "direct_interest_snapshot"."member_id", jbe@339: "direct_interest_snapshot"."weight" AS "weight_num", jbe@339: count("initiative"."id") AS "weight_den" jbe@339: FROM "issue" jbe@339: JOIN "direct_interest_snapshot" jbe@339: ON "issue"."id" = "direct_interest_snapshot"."issue_id" jbe@339: AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event" jbe@339: JOIN "initiative" jbe@339: ON "issue"."id" = "initiative"."issue_id" jbe@339: AND "initiative"."harmonic_weight" ISNULL jbe@339: JOIN "direct_supporter_snapshot" jbe@339: ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id" jbe@339: AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event" jbe@339: AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id" jbe@339: AND ( jbe@339: "direct_supporter_snapshot"."satisfied" = TRUE OR jbe@339: coalesce("initiative"."admitted", FALSE) = FALSE jbe@339: ) jbe@339: GROUP BY jbe@339: "direct_interest_snapshot"."issue_id", jbe@339: "direct_interest_snapshot"."event", jbe@339: "direct_interest_snapshot"."member_id", jbe@339: "direct_interest_snapshot"."weight"; jbe@339: COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"'; jbe@339: jbe@339: CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS jbe@339: SELECT jbe@339: "initiative"."issue_id", jbe@339: "initiative"."id" AS "initiative_id", jbe@339: "initiative"."admitted", jbe@339: sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num", jbe@339: "remaining_harmonic_supporter_weight"."weight_den" jbe@339: FROM "remaining_harmonic_supporter_weight" jbe@339: JOIN "initiative" jbe@339: ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id" jbe@339: AND "initiative"."harmonic_weight" ISNULL jbe@339: JOIN "direct_supporter_snapshot" jbe@339: ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id" jbe@339: AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event" jbe@339: AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id" jbe@339: AND ( jbe@339: "direct_supporter_snapshot"."satisfied" = TRUE OR jbe@339: coalesce("initiative"."admitted", FALSE) = FALSE jbe@339: ) jbe@339: GROUP BY jbe@339: "initiative"."issue_id", jbe@339: "initiative"."id", jbe@339: "initiative"."admitted", jbe@339: "remaining_harmonic_supporter_weight"."weight_den"; jbe@339: COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"'; jbe@339: jbe@349: CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS jbe@349: SELECT jbe@349: "issue_id", jbe@349: "id" AS "initiative_id", jbe@349: "admitted", jbe@349: 0 AS "weight_num", jbe@349: 1 AS "weight_den" jbe@349: FROM "initiative" jbe@349: WHERE "harmonic_weight" ISNULL; jbe@349: COMMENT ON VIEW "remaining_harmonic_initiative_weight_dummies" IS 'Helper view for function "set_harmonic_initiative_weights" providing dummy weights of zero value, which are needed for corner cases where there are no supporters for an initiative at all'; jbe@349: jbe@339: CREATE FUNCTION "set_harmonic_initiative_weights" jbe@339: ( "issue_id_p" "issue"."id"%TYPE ) jbe@339: RETURNS VOID jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE; jbe@339: "i" INT4; jbe@339: "count_v" INT4; jbe@339: "summand_v" FLOAT; jbe@339: "id_ary" INT4[]; jbe@339: "weight_ary" FLOAT[]; jbe@339: "min_weight_v" FLOAT; jbe@339: BEGIN jbe@339: PERFORM "require_transaction_isolation"(); jbe@339: UPDATE "initiative" SET "harmonic_weight" = NULL jbe@339: WHERE "issue_id" = "issue_id_p"; jbe@339: LOOP jbe@339: "min_weight_v" := NULL; jbe@339: "i" := 0; jbe@339: "count_v" := 0; jbe@339: FOR "weight_row" IN jbe@339: SELECT * FROM "remaining_harmonic_initiative_weight_summands" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND ( jbe@339: coalesce("admitted", FALSE) = FALSE OR NOT EXISTS ( jbe@339: SELECT NULL FROM "initiative" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "harmonic_weight" ISNULL jbe@339: AND coalesce("admitted", FALSE) = FALSE jbe@339: ) jbe@339: ) jbe@349: UNION ALL -- needed for corner cases jbe@349: SELECT * FROM "remaining_harmonic_initiative_weight_dummies" jbe@349: WHERE "issue_id" = "issue_id_p" jbe@349: AND ( jbe@349: coalesce("admitted", FALSE) = FALSE OR NOT EXISTS ( jbe@349: SELECT NULL FROM "initiative" jbe@349: WHERE "issue_id" = "issue_id_p" jbe@349: AND "harmonic_weight" ISNULL jbe@349: AND coalesce("admitted", FALSE) = FALSE jbe@349: ) jbe@349: ) jbe@339: ORDER BY "initiative_id" DESC, "weight_den" DESC jbe@339: -- NOTE: non-admitted initiatives placed first (at last positions), jbe@339: -- latest initiatives treated worse in case of tie jbe@339: LOOP jbe@339: "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT; jbe@339: IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN jbe@339: "i" := "i" + 1; jbe@339: "count_v" := "i"; jbe@339: "id_ary"["i"] := "weight_row"."initiative_id"; jbe@339: "weight_ary"["i"] := "summand_v"; jbe@339: ELSE jbe@339: "weight_ary"["i"] := "weight_ary"["i"] + "summand_v"; jbe@339: END IF; jbe@339: END LOOP; jbe@339: EXIT WHEN "count_v" = 0; jbe@339: "i" := 1; jbe@339: LOOP jbe@339: "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3); jbe@339: IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN jbe@339: "min_weight_v" := "weight_ary"["i"]; jbe@339: END IF; jbe@339: "i" := "i" + 1; jbe@339: EXIT WHEN "i" > "count_v"; jbe@339: END LOOP; jbe@339: "i" := 1; jbe@339: LOOP jbe@339: IF "weight_ary"["i"] = "min_weight_v" THEN jbe@339: UPDATE "initiative" SET "harmonic_weight" = "min_weight_v" jbe@339: WHERE "id" = "id_ary"["i"]; jbe@339: EXIT; jbe@339: END IF; jbe@339: "i" := "i" + 1; jbe@339: END LOOP; jbe@339: END LOOP; jbe@339: UPDATE "initiative" SET "harmonic_weight" = 0 jbe@339: WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL; jbe@339: END; jbe@339: $$; jbe@339: COMMENT ON FUNCTION "set_harmonic_initiative_weights" jbe@339: ( "issue"."id"%TYPE ) jbe@339: IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue'; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_population_snapshot" jbe@339: ( "issue_id_p" "issue"."id"%TYPE, jbe@339: "member_id_p" "member"."id"%TYPE, jbe@339: "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE ) jbe@339: RETURNS "direct_population_snapshot"."weight"%TYPE jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "issue_delegation_row" "issue_delegation"%ROWTYPE; jbe@339: "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE; jbe@339: "weight_v" INT4; jbe@339: "sub_weight_v" INT4; jbe@339: BEGIN jbe@339: PERFORM "require_transaction_isolation"(); jbe@339: "weight_v" := 0; jbe@339: FOR "issue_delegation_row" IN jbe@339: SELECT * FROM "issue_delegation" jbe@339: WHERE "trustee_id" = "member_id_p" jbe@339: AND "issue_id" = "issue_id_p" jbe@339: LOOP jbe@339: IF NOT EXISTS ( jbe@339: SELECT NULL FROM "direct_population_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic' jbe@339: AND "member_id" = "issue_delegation_row"."truster_id" jbe@339: ) AND NOT EXISTS ( jbe@339: SELECT NULL FROM "delegating_population_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic' jbe@339: AND "member_id" = "issue_delegation_row"."truster_id" jbe@339: ) THEN jbe@339: "delegate_member_ids_v" := jbe@339: "member_id_p" || "delegate_member_ids_p"; jbe@339: INSERT INTO "delegating_population_snapshot" ( jbe@339: "issue_id", jbe@339: "event", jbe@339: "member_id", jbe@339: "scope", jbe@339: "delegate_member_ids" jbe@339: ) VALUES ( jbe@339: "issue_id_p", jbe@339: 'periodic', jbe@339: "issue_delegation_row"."truster_id", jbe@339: "issue_delegation_row"."scope", jbe@339: "delegate_member_ids_v" jbe@339: ); jbe@339: "sub_weight_v" := 1 + jbe@339: "weight_of_added_delegations_for_population_snapshot"( jbe@339: "issue_id_p", jbe@339: "issue_delegation_row"."truster_id", jbe@339: "delegate_member_ids_v" jbe@339: ); jbe@339: UPDATE "delegating_population_snapshot" jbe@339: SET "weight" = "sub_weight_v" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic' jbe@339: AND "member_id" = "issue_delegation_row"."truster_id"; jbe@339: "weight_v" := "weight_v" + "sub_weight_v"; jbe@339: END IF; jbe@339: END LOOP; jbe@339: RETURN "weight_v"; jbe@339: END; jbe@339: $$; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "create_population_snapshot" jbe@339: ( "issue_id_p" "issue"."id"%TYPE ) jbe@339: RETURNS VOID jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "member_id_v" "member"."id"%TYPE; jbe@339: BEGIN jbe@339: PERFORM "require_transaction_isolation"(); jbe@339: DELETE FROM "direct_population_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic'; jbe@339: DELETE FROM "delegating_population_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic'; jbe@339: INSERT INTO "direct_population_snapshot" jbe@339: ("issue_id", "event", "member_id") jbe@339: SELECT jbe@339: "issue_id_p" AS "issue_id", jbe@339: 'periodic'::"snapshot_event" AS "event", jbe@339: "member"."id" AS "member_id" jbe@339: FROM "issue" jbe@339: JOIN "area" ON "issue"."area_id" = "area"."id" jbe@339: JOIN "membership" ON "area"."id" = "membership"."area_id" jbe@339: JOIN "member" ON "membership"."member_id" = "member"."id" jbe@339: JOIN "privilege" jbe@339: ON "privilege"."unit_id" = "area"."unit_id" jbe@339: AND "privilege"."member_id" = "member"."id" jbe@339: WHERE "issue"."id" = "issue_id_p" jbe@339: AND "member"."active" AND "privilege"."voting_right" jbe@339: UNION jbe@339: SELECT jbe@339: "issue_id_p" AS "issue_id", jbe@339: 'periodic'::"snapshot_event" AS "event", jbe@339: "member"."id" AS "member_id" jbe@339: FROM "issue" jbe@339: JOIN "area" ON "issue"."area_id" = "area"."id" jbe@339: JOIN "interest" ON "issue"."id" = "interest"."issue_id" jbe@339: JOIN "member" ON "interest"."member_id" = "member"."id" jbe@339: JOIN "privilege" jbe@339: ON "privilege"."unit_id" = "area"."unit_id" jbe@339: AND "privilege"."member_id" = "member"."id" jbe@339: WHERE "issue"."id" = "issue_id_p" jbe@339: AND "member"."active" AND "privilege"."voting_right"; jbe@339: FOR "member_id_v" IN jbe@339: SELECT "member_id" FROM "direct_population_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic' jbe@339: LOOP jbe@339: UPDATE "direct_population_snapshot" SET jbe@339: "weight" = 1 + jbe@339: "weight_of_added_delegations_for_population_snapshot"( jbe@339: "issue_id_p", jbe@339: "member_id_v", jbe@339: '{}' jbe@339: ) jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic' jbe@339: AND "member_id" = "member_id_v"; jbe@339: END LOOP; jbe@339: RETURN; jbe@339: END; jbe@339: $$; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_interest_snapshot" jbe@339: ( "issue_id_p" "issue"."id"%TYPE, jbe@339: "member_id_p" "member"."id"%TYPE, jbe@339: "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE ) jbe@339: RETURNS "direct_interest_snapshot"."weight"%TYPE jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "issue_delegation_row" "issue_delegation"%ROWTYPE; jbe@339: "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE; jbe@339: "weight_v" INT4; jbe@339: "sub_weight_v" INT4; jbe@339: BEGIN jbe@339: PERFORM "require_transaction_isolation"(); jbe@339: "weight_v" := 0; jbe@339: FOR "issue_delegation_row" IN jbe@339: SELECT * FROM "issue_delegation" jbe@339: WHERE "trustee_id" = "member_id_p" jbe@339: AND "issue_id" = "issue_id_p" jbe@339: LOOP jbe@339: IF NOT EXISTS ( jbe@339: SELECT NULL FROM "direct_interest_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic' jbe@339: AND "member_id" = "issue_delegation_row"."truster_id" jbe@339: ) AND NOT EXISTS ( jbe@339: SELECT NULL FROM "delegating_interest_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic' jbe@339: AND "member_id" = "issue_delegation_row"."truster_id" jbe@339: ) THEN jbe@339: "delegate_member_ids_v" := jbe@339: "member_id_p" || "delegate_member_ids_p"; jbe@339: INSERT INTO "delegating_interest_snapshot" ( jbe@339: "issue_id", jbe@339: "event", jbe@339: "member_id", jbe@339: "scope", jbe@339: "delegate_member_ids" jbe@339: ) VALUES ( jbe@339: "issue_id_p", jbe@339: 'periodic', jbe@339: "issue_delegation_row"."truster_id", jbe@339: "issue_delegation_row"."scope", jbe@339: "delegate_member_ids_v" jbe@339: ); jbe@339: "sub_weight_v" := 1 + jbe@339: "weight_of_added_delegations_for_interest_snapshot"( jbe@339: "issue_id_p", jbe@339: "issue_delegation_row"."truster_id", jbe@339: "delegate_member_ids_v" jbe@339: ); jbe@339: UPDATE "delegating_interest_snapshot" jbe@339: SET "weight" = "sub_weight_v" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic' jbe@339: AND "member_id" = "issue_delegation_row"."truster_id"; jbe@339: "weight_v" := "weight_v" + "sub_weight_v"; jbe@339: END IF; jbe@339: END LOOP; jbe@339: RETURN "weight_v"; jbe@339: END; jbe@339: $$; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "create_interest_snapshot" jbe@339: ( "issue_id_p" "issue"."id"%TYPE ) jbe@339: RETURNS VOID jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "member_id_v" "member"."id"%TYPE; jbe@339: BEGIN jbe@339: PERFORM "require_transaction_isolation"(); jbe@339: DELETE FROM "direct_interest_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic'; jbe@339: DELETE FROM "delegating_interest_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic'; jbe@339: DELETE FROM "direct_supporter_snapshot" jbe@339: USING "initiative" -- NOTE: due to missing index on issue_id jbe@339: WHERE "initiative"."issue_id" = "issue_id_p" jbe@339: AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id" jbe@339: AND "direct_supporter_snapshot"."event" = 'periodic'; jbe@339: INSERT INTO "direct_interest_snapshot" jbe@339: ("issue_id", "event", "member_id") jbe@339: SELECT jbe@339: "issue_id_p" AS "issue_id", jbe@339: 'periodic' AS "event", jbe@339: "member"."id" AS "member_id" jbe@339: FROM "issue" jbe@339: JOIN "area" ON "issue"."area_id" = "area"."id" jbe@339: JOIN "interest" ON "issue"."id" = "interest"."issue_id" jbe@339: JOIN "member" ON "interest"."member_id" = "member"."id" jbe@339: JOIN "privilege" jbe@339: ON "privilege"."unit_id" = "area"."unit_id" jbe@339: AND "privilege"."member_id" = "member"."id" jbe@339: WHERE "issue"."id" = "issue_id_p" jbe@339: AND "member"."active" AND "privilege"."voting_right"; jbe@339: FOR "member_id_v" IN jbe@339: SELECT "member_id" FROM "direct_interest_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic' jbe@339: LOOP jbe@339: UPDATE "direct_interest_snapshot" SET jbe@339: "weight" = 1 + jbe@339: "weight_of_added_delegations_for_interest_snapshot"( jbe@339: "issue_id_p", jbe@339: "member_id_v", jbe@339: '{}' jbe@339: ) jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic' jbe@339: AND "member_id" = "member_id_v"; jbe@339: END LOOP; jbe@339: INSERT INTO "direct_supporter_snapshot" jbe@339: ( "issue_id", "initiative_id", "event", "member_id", jbe@339: "draft_id", "informed", "satisfied" ) jbe@339: SELECT jbe@339: "issue_id_p" AS "issue_id", jbe@339: "initiative"."id" AS "initiative_id", jbe@339: 'periodic' AS "event", jbe@339: "supporter"."member_id" AS "member_id", jbe@339: "supporter"."draft_id" AS "draft_id", jbe@339: "supporter"."draft_id" = "current_draft"."id" AS "informed", jbe@339: NOT EXISTS ( jbe@339: SELECT NULL FROM "critical_opinion" jbe@339: WHERE "initiative_id" = "initiative"."id" jbe@339: AND "member_id" = "supporter"."member_id" jbe@339: ) AS "satisfied" jbe@339: FROM "initiative" jbe@339: JOIN "supporter" jbe@339: ON "supporter"."initiative_id" = "initiative"."id" jbe@339: JOIN "current_draft" jbe@339: ON "initiative"."id" = "current_draft"."initiative_id" jbe@339: JOIN "direct_interest_snapshot" jbe@339: ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" jbe@339: AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" jbe@339: AND "event" = 'periodic' jbe@339: WHERE "initiative"."issue_id" = "issue_id_p"; jbe@339: RETURN; jbe@339: END; jbe@339: $$; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "create_snapshot" jbe@339: ( "issue_id_p" "issue"."id"%TYPE ) jbe@339: RETURNS VOID jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "initiative_id_v" "initiative"."id"%TYPE; jbe@339: "suggestion_id_v" "suggestion"."id"%TYPE; jbe@339: BEGIN jbe@339: PERFORM "require_transaction_isolation"(); jbe@339: PERFORM "create_population_snapshot"("issue_id_p"); jbe@339: PERFORM "create_interest_snapshot"("issue_id_p"); jbe@339: UPDATE "issue" SET jbe@339: "snapshot" = coalesce("phase_finished", now()), jbe@339: "latest_snapshot_event" = 'periodic', jbe@339: "population" = ( jbe@339: SELECT coalesce(sum("weight"), 0) jbe@339: FROM "direct_population_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "event" = 'periodic' jbe@339: ) jbe@339: WHERE "id" = "issue_id_p"; jbe@339: FOR "initiative_id_v" IN jbe@339: SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" jbe@339: LOOP jbe@339: UPDATE "initiative" SET jbe@339: "supporter_count" = ( jbe@339: SELECT coalesce(sum("di"."weight"), 0) jbe@339: FROM "direct_interest_snapshot" AS "di" jbe@339: JOIN "direct_supporter_snapshot" AS "ds" jbe@339: ON "di"."member_id" = "ds"."member_id" jbe@339: WHERE "di"."issue_id" = "issue_id_p" jbe@339: AND "di"."event" = 'periodic' jbe@339: AND "ds"."initiative_id" = "initiative_id_v" jbe@339: AND "ds"."event" = 'periodic' jbe@339: ), jbe@339: "informed_supporter_count" = ( jbe@339: SELECT coalesce(sum("di"."weight"), 0) jbe@339: FROM "direct_interest_snapshot" AS "di" jbe@339: JOIN "direct_supporter_snapshot" AS "ds" jbe@339: ON "di"."member_id" = "ds"."member_id" jbe@339: WHERE "di"."issue_id" = "issue_id_p" jbe@339: AND "di"."event" = 'periodic' jbe@339: AND "ds"."initiative_id" = "initiative_id_v" jbe@339: AND "ds"."event" = 'periodic' jbe@339: AND "ds"."informed" jbe@339: ), jbe@339: "satisfied_supporter_count" = ( jbe@339: SELECT coalesce(sum("di"."weight"), 0) jbe@339: FROM "direct_interest_snapshot" AS "di" jbe@339: JOIN "direct_supporter_snapshot" AS "ds" jbe@339: ON "di"."member_id" = "ds"."member_id" jbe@339: WHERE "di"."issue_id" = "issue_id_p" jbe@339: AND "di"."event" = 'periodic' jbe@339: AND "ds"."initiative_id" = "initiative_id_v" jbe@339: AND "ds"."event" = 'periodic' jbe@339: AND "ds"."satisfied" jbe@339: ), jbe@339: "satisfied_informed_supporter_count" = ( jbe@339: SELECT coalesce(sum("di"."weight"), 0) jbe@339: FROM "direct_interest_snapshot" AS "di" jbe@339: JOIN "direct_supporter_snapshot" AS "ds" jbe@339: ON "di"."member_id" = "ds"."member_id" jbe@339: WHERE "di"."issue_id" = "issue_id_p" jbe@339: AND "di"."event" = 'periodic' jbe@339: AND "ds"."initiative_id" = "initiative_id_v" jbe@339: AND "ds"."event" = 'periodic' jbe@339: AND "ds"."informed" jbe@339: AND "ds"."satisfied" jbe@339: ) jbe@339: WHERE "id" = "initiative_id_v"; jbe@339: FOR "suggestion_id_v" IN jbe@339: SELECT "id" FROM "suggestion" jbe@339: WHERE "initiative_id" = "initiative_id_v" jbe@339: LOOP jbe@339: UPDATE "suggestion" SET jbe@339: "minus2_unfulfilled_count" = ( jbe@339: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@339: FROM "issue" CROSS JOIN "opinion" jbe@339: JOIN "direct_interest_snapshot" AS "snapshot" jbe@339: ON "snapshot"."issue_id" = "issue"."id" jbe@339: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@339: AND "snapshot"."member_id" = "opinion"."member_id" jbe@339: WHERE "issue"."id" = "issue_id_p" jbe@339: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@339: AND "opinion"."degree" = -2 jbe@339: AND "opinion"."fulfilled" = FALSE jbe@339: ), jbe@339: "minus2_fulfilled_count" = ( jbe@339: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@339: FROM "issue" CROSS JOIN "opinion" jbe@339: JOIN "direct_interest_snapshot" AS "snapshot" jbe@339: ON "snapshot"."issue_id" = "issue"."id" jbe@339: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@339: AND "snapshot"."member_id" = "opinion"."member_id" jbe@339: WHERE "issue"."id" = "issue_id_p" jbe@339: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@339: AND "opinion"."degree" = -2 jbe@339: AND "opinion"."fulfilled" = TRUE jbe@339: ), jbe@339: "minus1_unfulfilled_count" = ( jbe@339: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@339: FROM "issue" CROSS JOIN "opinion" jbe@339: JOIN "direct_interest_snapshot" AS "snapshot" jbe@339: ON "snapshot"."issue_id" = "issue"."id" jbe@339: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@339: AND "snapshot"."member_id" = "opinion"."member_id" jbe@339: WHERE "issue"."id" = "issue_id_p" jbe@339: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@339: AND "opinion"."degree" = -1 jbe@339: AND "opinion"."fulfilled" = FALSE jbe@339: ), jbe@339: "minus1_fulfilled_count" = ( jbe@339: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@339: FROM "issue" CROSS JOIN "opinion" jbe@339: JOIN "direct_interest_snapshot" AS "snapshot" jbe@339: ON "snapshot"."issue_id" = "issue"."id" jbe@339: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@339: AND "snapshot"."member_id" = "opinion"."member_id" jbe@339: WHERE "issue"."id" = "issue_id_p" jbe@339: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@339: AND "opinion"."degree" = -1 jbe@339: AND "opinion"."fulfilled" = TRUE jbe@339: ), jbe@339: "plus1_unfulfilled_count" = ( jbe@339: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@339: FROM "issue" CROSS JOIN "opinion" jbe@339: JOIN "direct_interest_snapshot" AS "snapshot" jbe@339: ON "snapshot"."issue_id" = "issue"."id" jbe@339: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@339: AND "snapshot"."member_id" = "opinion"."member_id" jbe@339: WHERE "issue"."id" = "issue_id_p" jbe@339: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@339: AND "opinion"."degree" = 1 jbe@339: AND "opinion"."fulfilled" = FALSE jbe@339: ), jbe@339: "plus1_fulfilled_count" = ( jbe@339: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@339: FROM "issue" CROSS JOIN "opinion" jbe@339: JOIN "direct_interest_snapshot" AS "snapshot" jbe@339: ON "snapshot"."issue_id" = "issue"."id" jbe@339: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@339: AND "snapshot"."member_id" = "opinion"."member_id" jbe@339: WHERE "issue"."id" = "issue_id_p" jbe@339: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@339: AND "opinion"."degree" = 1 jbe@339: AND "opinion"."fulfilled" = TRUE jbe@339: ), jbe@339: "plus2_unfulfilled_count" = ( jbe@339: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@339: FROM "issue" CROSS JOIN "opinion" jbe@339: JOIN "direct_interest_snapshot" AS "snapshot" jbe@339: ON "snapshot"."issue_id" = "issue"."id" jbe@339: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@339: AND "snapshot"."member_id" = "opinion"."member_id" jbe@339: WHERE "issue"."id" = "issue_id_p" jbe@339: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@339: AND "opinion"."degree" = 2 jbe@339: AND "opinion"."fulfilled" = FALSE jbe@339: ), jbe@339: "plus2_fulfilled_count" = ( jbe@339: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@339: FROM "issue" CROSS JOIN "opinion" jbe@339: JOIN "direct_interest_snapshot" AS "snapshot" jbe@339: ON "snapshot"."issue_id" = "issue"."id" jbe@339: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@339: AND "snapshot"."member_id" = "opinion"."member_id" jbe@339: WHERE "issue"."id" = "issue_id_p" jbe@339: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@339: AND "opinion"."degree" = 2 jbe@339: AND "opinion"."fulfilled" = TRUE jbe@339: ) jbe@339: WHERE "suggestion"."id" = "suggestion_id_v"; jbe@339: END LOOP; jbe@339: END LOOP; jbe@339: RETURN; jbe@339: END; jbe@339: $$; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "set_snapshot_event" jbe@339: ( "issue_id_p" "issue"."id"%TYPE, jbe@339: "event_p" "snapshot_event" ) jbe@339: RETURNS VOID jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "event_v" "issue"."latest_snapshot_event"%TYPE; jbe@339: BEGIN jbe@339: PERFORM "require_transaction_isolation"(); jbe@339: SELECT "latest_snapshot_event" INTO "event_v" FROM "issue" jbe@339: WHERE "id" = "issue_id_p" FOR UPDATE; jbe@339: UPDATE "issue" SET "latest_snapshot_event" = "event_p" jbe@339: WHERE "id" = "issue_id_p"; jbe@339: UPDATE "direct_population_snapshot" SET "event" = "event_p" jbe@339: WHERE "issue_id" = "issue_id_p" AND "event" = "event_v"; jbe@339: UPDATE "delegating_population_snapshot" SET "event" = "event_p" jbe@339: WHERE "issue_id" = "issue_id_p" AND "event" = "event_v"; jbe@339: UPDATE "direct_interest_snapshot" SET "event" = "event_p" jbe@339: WHERE "issue_id" = "issue_id_p" AND "event" = "event_v"; jbe@339: UPDATE "delegating_interest_snapshot" SET "event" = "event_p" jbe@339: WHERE "issue_id" = "issue_id_p" AND "event" = "event_v"; jbe@339: UPDATE "direct_supporter_snapshot" SET "event" = "event_p" jbe@339: FROM "initiative" -- NOTE: due to missing index on issue_id jbe@339: WHERE "initiative"."issue_id" = "issue_id_p" jbe@339: AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id" jbe@339: AND "direct_supporter_snapshot"."event" = "event_v"; jbe@339: RETURN; jbe@339: END; jbe@339: $$; jbe@339: jbe@339: DROP FUNCTION "freeze_after_snapshot"("issue"."id"%TYPE); jbe@339: DROP FUNCTION "manual_freeze"("issue"."id"%TYPE); jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations" jbe@339: ( "issue_id_p" "issue"."id"%TYPE, jbe@339: "member_id_p" "member"."id"%TYPE, jbe@339: "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE ) jbe@339: RETURNS "direct_voter"."weight"%TYPE jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "issue_delegation_row" "issue_delegation"%ROWTYPE; jbe@339: "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE; jbe@339: "weight_v" INT4; jbe@339: "sub_weight_v" INT4; jbe@339: BEGIN jbe@339: PERFORM "require_transaction_isolation"(); jbe@339: "weight_v" := 0; jbe@339: FOR "issue_delegation_row" IN jbe@339: SELECT * FROM "issue_delegation" jbe@339: WHERE "trustee_id" = "member_id_p" jbe@339: AND "issue_id" = "issue_id_p" jbe@339: LOOP jbe@339: IF NOT EXISTS ( jbe@339: SELECT NULL FROM "direct_voter" jbe@339: WHERE "member_id" = "issue_delegation_row"."truster_id" jbe@339: AND "issue_id" = "issue_id_p" jbe@339: ) AND NOT EXISTS ( jbe@339: SELECT NULL FROM "delegating_voter" jbe@339: WHERE "member_id" = "issue_delegation_row"."truster_id" jbe@339: AND "issue_id" = "issue_id_p" jbe@339: ) THEN jbe@339: "delegate_member_ids_v" := jbe@339: "member_id_p" || "delegate_member_ids_p"; jbe@339: INSERT INTO "delegating_voter" ( jbe@339: "issue_id", jbe@339: "member_id", jbe@339: "scope", jbe@339: "delegate_member_ids" jbe@339: ) VALUES ( jbe@339: "issue_id_p", jbe@339: "issue_delegation_row"."truster_id", jbe@339: "issue_delegation_row"."scope", jbe@339: "delegate_member_ids_v" jbe@339: ); jbe@339: "sub_weight_v" := 1 + jbe@339: "weight_of_added_vote_delegations"( jbe@339: "issue_id_p", jbe@339: "issue_delegation_row"."truster_id", jbe@339: "delegate_member_ids_v" jbe@339: ); jbe@339: UPDATE "delegating_voter" jbe@339: SET "weight" = "sub_weight_v" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "member_id" = "issue_delegation_row"."truster_id"; jbe@339: "weight_v" := "weight_v" + "sub_weight_v"; jbe@339: END IF; jbe@339: END LOOP; jbe@339: RETURN "weight_v"; jbe@339: END; jbe@339: $$; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "add_vote_delegations" jbe@339: ( "issue_id_p" "issue"."id"%TYPE ) jbe@339: RETURNS VOID jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "member_id_v" "member"."id"%TYPE; jbe@339: BEGIN jbe@339: PERFORM "require_transaction_isolation"(); jbe@339: FOR "member_id_v" IN jbe@339: SELECT "member_id" FROM "direct_voter" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: LOOP jbe@339: UPDATE "direct_voter" SET jbe@339: "weight" = "weight" + "weight_of_added_vote_delegations"( jbe@339: "issue_id_p", jbe@339: "member_id_v", jbe@339: '{}' jbe@339: ) jbe@339: WHERE "member_id" = "member_id_v" jbe@339: AND "issue_id" = "issue_id_p"; jbe@339: END LOOP; jbe@339: RETURN; jbe@339: END; jbe@339: $$; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) jbe@339: RETURNS VOID jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "area_id_v" "area"."id"%TYPE; jbe@339: "unit_id_v" "unit"."id"%TYPE; jbe@339: "member_id_v" "member"."id"%TYPE; jbe@339: BEGIN jbe@339: PERFORM "require_transaction_isolation"(); jbe@339: SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; jbe@339: SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; jbe@339: -- delete timestamp of voting comment: jbe@339: UPDATE "direct_voter" SET "comment_changed" = NULL jbe@339: WHERE "issue_id" = "issue_id_p"; jbe@339: -- delete delegating votes (in cases of manual reset of issue state): jbe@339: DELETE FROM "delegating_voter" jbe@339: WHERE "issue_id" = "issue_id_p"; jbe@339: -- delete votes from non-privileged voters: jbe@339: DELETE FROM "direct_voter" jbe@339: USING ( jbe@339: SELECT jbe@339: "direct_voter"."member_id" jbe@339: FROM "direct_voter" jbe@339: JOIN "member" ON "direct_voter"."member_id" = "member"."id" jbe@339: LEFT JOIN "privilege" jbe@339: ON "privilege"."unit_id" = "unit_id_v" jbe@339: AND "privilege"."member_id" = "direct_voter"."member_id" jbe@339: WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( jbe@339: "member"."active" = FALSE OR jbe@339: "privilege"."voting_right" ISNULL OR jbe@339: "privilege"."voting_right" = FALSE jbe@339: ) jbe@339: ) AS "subquery" jbe@339: WHERE "direct_voter"."issue_id" = "issue_id_p" jbe@339: AND "direct_voter"."member_id" = "subquery"."member_id"; jbe@339: -- consider delegations: jbe@339: UPDATE "direct_voter" SET "weight" = 1 jbe@339: WHERE "issue_id" = "issue_id_p"; jbe@339: PERFORM "add_vote_delegations"("issue_id_p"); jbe@339: -- materialize battle_view: jbe@339: -- NOTE: "closed" column of issue must be set at this point jbe@339: DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; jbe@339: INSERT INTO "battle" ( jbe@339: "issue_id", jbe@339: "winning_initiative_id", "losing_initiative_id", jbe@339: "count" jbe@339: ) SELECT jbe@339: "issue_id", jbe@339: "winning_initiative_id", "losing_initiative_id", jbe@339: "count" jbe@339: FROM "battle_view" WHERE "issue_id" = "issue_id_p"; jbe@339: -- set voter count: jbe@339: UPDATE "issue" SET jbe@339: "voter_count" = ( jbe@339: SELECT coalesce(sum("weight"), 0) jbe@339: FROM "direct_voter" WHERE "issue_id" = "issue_id_p" jbe@339: ) jbe@339: WHERE "id" = "issue_id_p"; jbe@339: -- copy "positive_votes" and "negative_votes" from "battle" table: jbe@339: UPDATE "initiative" SET jbe@339: "positive_votes" = "battle_win"."count", jbe@339: "negative_votes" = "battle_lose"."count" jbe@339: FROM "battle" AS "battle_win", "battle" AS "battle_lose" jbe@339: WHERE jbe@339: "battle_win"."issue_id" = "issue_id_p" AND jbe@339: "battle_win"."winning_initiative_id" = "initiative"."id" AND jbe@339: "battle_win"."losing_initiative_id" ISNULL AND jbe@339: "battle_lose"."issue_id" = "issue_id_p" AND jbe@339: "battle_lose"."losing_initiative_id" = "initiative"."id" AND jbe@339: "battle_lose"."winning_initiative_id" ISNULL; jbe@339: END; jbe@339: $$; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) jbe@339: RETURNS VOID jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "issue_row" "issue"%ROWTYPE; jbe@339: "policy_row" "policy"%ROWTYPE; jbe@339: "dimension_v" INTEGER; jbe@339: "vote_matrix" INT4[][]; -- absolute votes jbe@339: "matrix" INT8[][]; -- defeat strength / best paths jbe@339: "i" INTEGER; jbe@339: "j" INTEGER; jbe@339: "k" INTEGER; jbe@339: "battle_row" "battle"%ROWTYPE; jbe@339: "rank_ary" INT4[]; jbe@339: "rank_v" INT4; jbe@339: "done_v" INTEGER; jbe@339: "winners_ary" INTEGER[]; jbe@339: "initiative_id_v" "initiative"."id"%TYPE; jbe@339: BEGIN jbe@339: PERFORM "require_transaction_isolation"(); jbe@339: SELECT * INTO "issue_row" jbe@339: FROM "issue" WHERE "id" = "issue_id_p"; jbe@339: SELECT * INTO "policy_row" jbe@339: FROM "policy" WHERE "id" = "issue_row"."policy_id"; jbe@339: SELECT count(1) INTO "dimension_v" jbe@339: FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; jbe@339: -- Create "vote_matrix" with absolute number of votes in pairwise jbe@339: -- comparison: jbe@339: "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); jbe@339: "i" := 1; jbe@339: "j" := 2; jbe@339: FOR "battle_row" IN jbe@339: SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" jbe@339: ORDER BY jbe@339: "winning_initiative_id" NULLS LAST, jbe@339: "losing_initiative_id" NULLS LAST jbe@339: LOOP jbe@339: "vote_matrix"["i"]["j"] := "battle_row"."count"; jbe@339: IF "j" = "dimension_v" THEN jbe@339: "i" := "i" + 1; jbe@339: "j" := 1; jbe@339: ELSE jbe@339: "j" := "j" + 1; jbe@339: IF "j" = "i" THEN jbe@339: "j" := "j" + 1; jbe@339: END IF; jbe@339: END IF; jbe@339: END LOOP; jbe@339: IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN jbe@339: RAISE EXCEPTION 'Wrong battle count (should not happen)'; jbe@339: END IF; jbe@339: -- Store defeat strengths in "matrix" using "defeat_strength" jbe@339: -- function: jbe@339: "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); jbe@339: "i" := 1; jbe@339: LOOP jbe@339: "j" := 1; jbe@339: LOOP jbe@339: IF "i" != "j" THEN jbe@339: "matrix"["i"]["j"] := "defeat_strength"( jbe@339: "vote_matrix"["i"]["j"], jbe@339: "vote_matrix"["j"]["i"] jbe@339: ); jbe@339: END IF; jbe@339: EXIT WHEN "j" = "dimension_v"; jbe@339: "j" := "j" + 1; jbe@339: END LOOP; jbe@339: EXIT WHEN "i" = "dimension_v"; jbe@339: "i" := "i" + 1; jbe@339: END LOOP; jbe@339: -- Find best paths: jbe@339: "i" := 1; jbe@339: LOOP jbe@339: "j" := 1; jbe@339: LOOP jbe@339: IF "i" != "j" THEN jbe@339: "k" := 1; jbe@339: LOOP jbe@339: IF "i" != "k" AND "j" != "k" THEN jbe@339: IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN jbe@339: IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN jbe@339: "matrix"["j"]["k"] := "matrix"["j"]["i"]; jbe@339: END IF; jbe@339: ELSE jbe@339: IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN jbe@339: "matrix"["j"]["k"] := "matrix"["i"]["k"]; jbe@339: END IF; jbe@339: END IF; jbe@339: END IF; jbe@339: EXIT WHEN "k" = "dimension_v"; jbe@339: "k" := "k" + 1; jbe@339: END LOOP; jbe@339: END IF; jbe@339: EXIT WHEN "j" = "dimension_v"; jbe@339: "j" := "j" + 1; jbe@339: END LOOP; jbe@339: EXIT WHEN "i" = "dimension_v"; jbe@339: "i" := "i" + 1; jbe@339: END LOOP; jbe@339: -- Determine order of winners: jbe@339: "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); jbe@339: "rank_v" := 1; jbe@339: "done_v" := 0; jbe@339: LOOP jbe@339: "winners_ary" := '{}'; jbe@339: "i" := 1; jbe@339: LOOP jbe@339: IF "rank_ary"["i"] ISNULL THEN jbe@339: "j" := 1; jbe@339: LOOP jbe@339: IF jbe@339: "i" != "j" AND jbe@339: "rank_ary"["j"] ISNULL AND jbe@339: "matrix"["j"]["i"] > "matrix"["i"]["j"] jbe@339: THEN jbe@339: -- someone else is better jbe@339: EXIT; jbe@339: END IF; jbe@339: IF "j" = "dimension_v" THEN jbe@339: -- noone is better jbe@339: "winners_ary" := "winners_ary" || "i"; jbe@339: EXIT; jbe@339: END IF; jbe@339: "j" := "j" + 1; jbe@339: END LOOP; jbe@339: END IF; jbe@339: EXIT WHEN "i" = "dimension_v"; jbe@339: "i" := "i" + 1; jbe@339: END LOOP; jbe@339: "i" := 1; jbe@339: LOOP jbe@339: "rank_ary"["winners_ary"["i"]] := "rank_v"; jbe@339: "done_v" := "done_v" + 1; jbe@339: EXIT WHEN "i" = array_upper("winners_ary", 1); jbe@339: "i" := "i" + 1; jbe@339: END LOOP; jbe@339: EXIT WHEN "done_v" = "dimension_v"; jbe@339: "rank_v" := "rank_v" + 1; jbe@339: END LOOP; jbe@339: -- write preliminary results: jbe@339: "i" := 1; jbe@339: FOR "initiative_id_v" IN jbe@339: SELECT "id" FROM "initiative" jbe@339: WHERE "issue_id" = "issue_id_p" AND "admitted" jbe@339: ORDER BY "id" jbe@339: LOOP jbe@339: UPDATE "initiative" SET jbe@339: "direct_majority" = jbe@339: CASE WHEN "policy_row"."direct_majority_strict" THEN jbe@339: "positive_votes" * "policy_row"."direct_majority_den" > jbe@339: "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") jbe@339: ELSE jbe@339: "positive_votes" * "policy_row"."direct_majority_den" >= jbe@339: "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") jbe@339: END jbe@339: AND "positive_votes" >= "policy_row"."direct_majority_positive" jbe@339: AND "issue_row"."voter_count"-"negative_votes" >= jbe@339: "policy_row"."direct_majority_non_negative", jbe@339: "indirect_majority" = jbe@339: CASE WHEN "policy_row"."indirect_majority_strict" THEN jbe@339: "positive_votes" * "policy_row"."indirect_majority_den" > jbe@339: "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") jbe@339: ELSE jbe@339: "positive_votes" * "policy_row"."indirect_majority_den" >= jbe@339: "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") jbe@339: END jbe@339: AND "positive_votes" >= "policy_row"."indirect_majority_positive" jbe@339: AND "issue_row"."voter_count"-"negative_votes" >= jbe@339: "policy_row"."indirect_majority_non_negative", jbe@339: "schulze_rank" = "rank_ary"["i"], jbe@339: "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], jbe@339: "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], jbe@339: "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], jbe@339: "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, jbe@339: "eligible" = FALSE, jbe@339: "winner" = FALSE, jbe@339: "rank" = NULL -- NOTE: in cases of manual reset of issue state jbe@339: WHERE "id" = "initiative_id_v"; jbe@339: "i" := "i" + 1; jbe@339: END LOOP; jbe@339: IF "i" != "dimension_v" THEN jbe@339: RAISE EXCEPTION 'Wrong winner count (should not happen)'; jbe@339: END IF; jbe@339: -- take indirect majorities into account: jbe@339: LOOP jbe@339: UPDATE "initiative" SET "indirect_majority" = TRUE jbe@339: FROM ( jbe@339: SELECT "new_initiative"."id" AS "initiative_id" jbe@339: FROM "initiative" "old_initiative" jbe@339: JOIN "initiative" "new_initiative" jbe@339: ON "new_initiative"."issue_id" = "issue_id_p" jbe@339: AND "new_initiative"."indirect_majority" = FALSE jbe@339: JOIN "battle" "battle_win" jbe@339: ON "battle_win"."issue_id" = "issue_id_p" jbe@339: AND "battle_win"."winning_initiative_id" = "new_initiative"."id" jbe@339: AND "battle_win"."losing_initiative_id" = "old_initiative"."id" jbe@339: JOIN "battle" "battle_lose" jbe@339: ON "battle_lose"."issue_id" = "issue_id_p" jbe@339: AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" jbe@339: AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" jbe@339: WHERE "old_initiative"."issue_id" = "issue_id_p" jbe@339: AND "old_initiative"."indirect_majority" = TRUE jbe@339: AND CASE WHEN "policy_row"."indirect_majority_strict" THEN jbe@339: "battle_win"."count" * "policy_row"."indirect_majority_den" > jbe@339: "policy_row"."indirect_majority_num" * jbe@339: ("battle_win"."count"+"battle_lose"."count") jbe@339: ELSE jbe@339: "battle_win"."count" * "policy_row"."indirect_majority_den" >= jbe@339: "policy_row"."indirect_majority_num" * jbe@339: ("battle_win"."count"+"battle_lose"."count") jbe@339: END jbe@339: AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" jbe@339: AND "issue_row"."voter_count"-"battle_lose"."count" >= jbe@339: "policy_row"."indirect_majority_non_negative" jbe@339: ) AS "subquery" jbe@339: WHERE "id" = "subquery"."initiative_id"; jbe@339: EXIT WHEN NOT FOUND; jbe@339: END LOOP; jbe@339: -- set "multistage_majority" for remaining matching initiatives: jbe@339: UPDATE "initiative" SET "multistage_majority" = TRUE jbe@339: FROM ( jbe@339: SELECT "losing_initiative"."id" AS "initiative_id" jbe@339: FROM "initiative" "losing_initiative" jbe@339: JOIN "initiative" "winning_initiative" jbe@339: ON "winning_initiative"."issue_id" = "issue_id_p" jbe@339: AND "winning_initiative"."admitted" jbe@339: JOIN "battle" "battle_win" jbe@339: ON "battle_win"."issue_id" = "issue_id_p" jbe@339: AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" jbe@339: AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" jbe@339: JOIN "battle" "battle_lose" jbe@339: ON "battle_lose"."issue_id" = "issue_id_p" jbe@339: AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" jbe@339: AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" jbe@339: WHERE "losing_initiative"."issue_id" = "issue_id_p" jbe@339: AND "losing_initiative"."admitted" jbe@339: AND "winning_initiative"."schulze_rank" < jbe@339: "losing_initiative"."schulze_rank" jbe@339: AND "battle_win"."count" > "battle_lose"."count" jbe@339: AND ( jbe@339: "battle_win"."count" > "winning_initiative"."positive_votes" OR jbe@339: "battle_lose"."count" < "losing_initiative"."negative_votes" ) jbe@339: ) AS "subquery" jbe@339: WHERE "id" = "subquery"."initiative_id"; jbe@339: -- mark eligible initiatives: jbe@339: UPDATE "initiative" SET "eligible" = TRUE jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "initiative"."direct_majority" jbe@339: AND "initiative"."indirect_majority" jbe@339: AND "initiative"."better_than_status_quo" jbe@339: AND ( jbe@339: "policy_row"."no_multistage_majority" = FALSE OR jbe@339: "initiative"."multistage_majority" = FALSE ) jbe@339: AND ( jbe@339: "policy_row"."no_reverse_beat_path" = FALSE OR jbe@339: "initiative"."reverse_beat_path" = FALSE ); jbe@339: -- mark final winner: jbe@339: UPDATE "initiative" SET "winner" = TRUE jbe@339: FROM ( jbe@339: SELECT "id" AS "initiative_id" jbe@339: FROM "initiative" jbe@339: WHERE "issue_id" = "issue_id_p" AND "eligible" jbe@339: ORDER BY jbe@339: "schulze_rank", jbe@339: "id" jbe@339: LIMIT 1 jbe@339: ) AS "subquery" jbe@339: WHERE "id" = "subquery"."initiative_id"; jbe@339: -- write (final) ranks: jbe@339: "rank_v" := 1; jbe@339: FOR "initiative_id_v" IN jbe@339: SELECT "id" jbe@339: FROM "initiative" jbe@339: WHERE "issue_id" = "issue_id_p" AND "admitted" jbe@339: ORDER BY jbe@339: "winner" DESC, jbe@339: "eligible" DESC, jbe@339: "schulze_rank", jbe@339: "id" jbe@339: LOOP jbe@339: UPDATE "initiative" SET "rank" = "rank_v" jbe@339: WHERE "id" = "initiative_id_v"; jbe@339: "rank_v" := "rank_v" + 1; jbe@339: END LOOP; jbe@339: -- set schulze rank of status quo and mark issue as finished: jbe@339: UPDATE "issue" SET jbe@339: "status_quo_schulze_rank" = "rank_ary"["dimension_v"], jbe@339: "state" = jbe@339: CASE WHEN EXISTS ( jbe@339: SELECT NULL FROM "initiative" jbe@339: WHERE "issue_id" = "issue_id_p" AND "winner" jbe@339: ) THEN jbe@339: 'finished_with_winner'::"issue_state" jbe@339: ELSE jbe@339: 'finished_without_winner'::"issue_state" jbe@339: END, jbe@339: "closed" = "phase_finished", jbe@339: "phase_finished" = NULL jbe@339: WHERE "id" = "issue_id_p"; jbe@339: RETURN; jbe@339: END; jbe@339: $$; jbe@339: jbe@339: DROP FUNCTION "check_issue"("issue"."id"%TYPE); jbe@339: jbe@339: CREATE TYPE "check_issue_persistence" AS ( jbe@339: "state" "issue_state", jbe@339: "phase_finished" BOOLEAN, jbe@339: "issue_revoked" BOOLEAN, jbe@339: "snapshot_created" BOOLEAN, jbe@339: "harmonic_weights_set" BOOLEAN, jbe@339: "closed_voting" BOOLEAN ); jbe@339: 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'; jbe@339: jbe@339: CREATE FUNCTION "check_issue" jbe@339: ( "issue_id_p" "issue"."id"%TYPE, jbe@339: "persist" "check_issue_persistence" ) jbe@339: RETURNS "check_issue_persistence" jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "issue_row" "issue"%ROWTYPE; jbe@339: "policy_row" "policy"%ROWTYPE; jbe@339: "initiative_row" "initiative"%ROWTYPE; jbe@339: "state_v" "issue_state"; jbe@339: BEGIN jbe@339: PERFORM "require_transaction_isolation"(); jbe@339: IF "persist" ISNULL THEN jbe@339: SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" jbe@339: FOR UPDATE; jbe@339: IF "issue_row"."closed" NOTNULL THEN jbe@339: RETURN NULL; jbe@339: END IF; jbe@339: "persist"."state" := "issue_row"."state"; jbe@339: IF jbe@339: ( "issue_row"."state" = 'admission' AND now() >= jbe@339: "issue_row"."created" + "issue_row"."admission_time" ) OR jbe@339: ( "issue_row"."state" = 'discussion' AND now() >= jbe@339: "issue_row"."accepted" + "issue_row"."discussion_time" ) OR jbe@339: ( "issue_row"."state" = 'verification' AND now() >= jbe@339: "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR jbe@339: ( "issue_row"."state" = 'voting' AND now() >= jbe@339: "issue_row"."fully_frozen" + "issue_row"."voting_time" ) jbe@339: THEN jbe@339: "persist"."phase_finished" := TRUE; jbe@339: ELSE jbe@339: "persist"."phase_finished" := FALSE; jbe@339: END IF; jbe@339: IF jbe@339: NOT EXISTS ( jbe@339: -- all initiatives are revoked jbe@339: SELECT NULL FROM "initiative" jbe@339: WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL jbe@339: ) AND ( jbe@339: -- and issue has not been accepted yet jbe@339: "persist"."state" = 'admission' OR jbe@339: -- or verification time has elapsed jbe@339: ( "persist"."state" = 'verification' AND jbe@339: "persist"."phase_finished" ) OR jbe@339: -- or no initiatives have been revoked lately jbe@339: NOT EXISTS ( jbe@339: SELECT NULL FROM "initiative" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND now() < "revoked" + "issue_row"."verification_time" jbe@339: ) jbe@339: ) jbe@339: THEN jbe@339: "persist"."issue_revoked" := TRUE; jbe@339: ELSE jbe@339: "persist"."issue_revoked" := FALSE; jbe@339: END IF; jbe@339: IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN jbe@339: UPDATE "issue" SET "phase_finished" = now() jbe@339: WHERE "id" = "issue_row"."id"; jbe@339: RETURN "persist"; jbe@339: ELSIF jbe@339: "persist"."state" IN ('admission', 'discussion', 'verification') jbe@339: THEN jbe@339: RETURN "persist"; jbe@339: ELSE jbe@339: RETURN NULL; jbe@339: END IF; jbe@339: END IF; jbe@339: IF jbe@339: "persist"."state" IN ('admission', 'discussion', 'verification') AND jbe@339: coalesce("persist"."snapshot_created", FALSE) = FALSE jbe@339: THEN jbe@339: PERFORM "create_snapshot"("issue_id_p"); jbe@339: "persist"."snapshot_created" = TRUE; jbe@339: IF "persist"."phase_finished" THEN jbe@339: IF "persist"."state" = 'admission' THEN jbe@339: PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); jbe@339: ELSIF "persist"."state" = 'discussion' THEN jbe@339: PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); jbe@339: ELSIF "persist"."state" = 'verification' THEN jbe@339: PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze'); jbe@339: SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; jbe@339: SELECT * INTO "policy_row" FROM "policy" jbe@339: WHERE "id" = "issue_row"."policy_id"; jbe@339: FOR "initiative_row" IN jbe@339: SELECT * FROM "initiative" jbe@339: WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL jbe@339: FOR UPDATE jbe@339: LOOP jbe@339: IF jbe@339: "initiative_row"."polling" OR ( jbe@339: "initiative_row"."satisfied_supporter_count" > 0 AND jbe@339: "initiative_row"."satisfied_supporter_count" * jbe@339: "policy_row"."initiative_quorum_den" >= jbe@339: "issue_row"."population" * "policy_row"."initiative_quorum_num" jbe@339: ) jbe@339: THEN jbe@339: UPDATE "initiative" SET "admitted" = TRUE jbe@339: WHERE "id" = "initiative_row"."id"; jbe@339: ELSE jbe@339: UPDATE "initiative" SET "admitted" = FALSE jbe@339: WHERE "id" = "initiative_row"."id"; jbe@339: END IF; jbe@339: END LOOP; jbe@339: END IF; jbe@339: END IF; jbe@339: RETURN "persist"; jbe@339: END IF; jbe@339: IF jbe@339: "persist"."state" IN ('admission', 'discussion', 'verification') AND jbe@339: coalesce("persist"."harmonic_weights_set", FALSE) = FALSE jbe@339: THEN jbe@339: PERFORM "set_harmonic_initiative_weights"("issue_id_p"); jbe@339: "persist"."harmonic_weights_set" = TRUE; jbe@339: IF jbe@339: "persist"."phase_finished" OR jbe@339: "persist"."issue_revoked" OR jbe@339: "persist"."state" = 'admission' jbe@339: THEN jbe@339: RETURN "persist"; jbe@339: ELSE jbe@339: RETURN NULL; jbe@339: END IF; jbe@339: END IF; jbe@339: IF "persist"."issue_revoked" THEN jbe@339: IF "persist"."state" = 'admission' THEN jbe@339: "state_v" := 'canceled_revoked_before_accepted'; jbe@339: ELSIF "persist"."state" = 'discussion' THEN jbe@339: "state_v" := 'canceled_after_revocation_during_discussion'; jbe@339: ELSIF "persist"."state" = 'verification' THEN jbe@339: "state_v" := 'canceled_after_revocation_during_verification'; jbe@339: END IF; jbe@339: UPDATE "issue" SET jbe@339: "state" = "state_v", jbe@339: "closed" = "phase_finished", jbe@339: "phase_finished" = NULL jbe@339: WHERE "id" = "issue_id_p"; jbe@339: RETURN NULL; jbe@339: END IF; jbe@339: IF "persist"."state" = 'admission' THEN jbe@339: SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" jbe@339: FOR UPDATE; jbe@339: SELECT * INTO "policy_row" jbe@339: FROM "policy" WHERE "id" = "issue_row"."policy_id"; jbe@339: IF EXISTS ( jbe@339: SELECT NULL FROM "initiative" jbe@339: WHERE "issue_id" = "issue_id_p" jbe@339: AND "supporter_count" > 0 jbe@339: AND "supporter_count" * "policy_row"."issue_quorum_den" jbe@339: >= "issue_row"."population" * "policy_row"."issue_quorum_num" jbe@339: ) THEN jbe@339: UPDATE "issue" SET jbe@339: "state" = 'discussion', jbe@339: "accepted" = coalesce("phase_finished", now()), jbe@339: "phase_finished" = NULL jbe@339: WHERE "id" = "issue_id_p"; jbe@339: ELSIF "issue_row"."phase_finished" NOTNULL THEN jbe@339: UPDATE "issue" SET jbe@339: "state" = 'canceled_issue_not_accepted', jbe@339: "closed" = "phase_finished", jbe@339: "phase_finished" = NULL jbe@339: WHERE "id" = "issue_id_p"; jbe@339: END IF; jbe@339: RETURN NULL; jbe@339: END IF; jbe@339: IF "persist"."phase_finished" THEN jbe@339: if "persist"."state" = 'discussion' THEN jbe@339: UPDATE "issue" SET jbe@339: "state" = 'verification', jbe@339: "half_frozen" = "phase_finished", jbe@339: "phase_finished" = NULL jbe@339: WHERE "id" = "issue_id_p"; jbe@339: RETURN NULL; jbe@339: END IF; jbe@339: IF "persist"."state" = 'verification' THEN jbe@339: SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" jbe@339: FOR UPDATE; jbe@339: SELECT * INTO "policy_row" FROM "policy" jbe@339: WHERE "id" = "issue_row"."policy_id"; jbe@339: IF EXISTS ( jbe@339: SELECT NULL FROM "initiative" jbe@339: WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE jbe@339: ) THEN jbe@339: UPDATE "issue" SET jbe@343: "state" = 'voting', jbe@343: "fully_frozen" = "phase_finished", jbe@339: "phase_finished" = NULL jbe@339: WHERE "id" = "issue_id_p"; jbe@339: ELSE jbe@339: UPDATE "issue" SET jbe@343: "state" = 'canceled_no_initiative_admitted', jbe@343: "fully_frozen" = "phase_finished", jbe@343: "closed" = "phase_finished", jbe@343: "phase_finished" = NULL jbe@339: WHERE "id" = "issue_id_p"; jbe@339: -- NOTE: The following DELETE statements have effect only when jbe@339: -- issue state has been manipulated jbe@339: DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p"; jbe@339: DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p"; jbe@339: DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; jbe@339: END IF; jbe@339: RETURN NULL; jbe@339: END IF; jbe@339: IF "persist"."state" = 'voting' THEN jbe@339: IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN jbe@339: PERFORM "close_voting"("issue_id_p"); jbe@339: "persist"."closed_voting" = TRUE; jbe@339: RETURN "persist"; jbe@339: END IF; jbe@339: PERFORM "calculate_ranks"("issue_id_p"); jbe@339: RETURN NULL; jbe@339: END IF; jbe@339: END IF; jbe@339: RAISE WARNING 'should not happen'; jbe@339: RETURN NULL; jbe@339: END; jbe@339: $$; jbe@339: COMMENT ON FUNCTION "check_issue" jbe@339: ( "issue"."id"%TYPE, jbe@339: "check_issue_persistence" ) jbe@339: 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")'; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "check_everything"() jbe@339: RETURNS VOID jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "issue_id_v" "issue"."id"%TYPE; jbe@339: "persist_v" "check_issue_persistence"; jbe@339: BEGIN jbe@339: RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes'; jbe@339: DELETE FROM "expired_session"; jbe@339: PERFORM "check_activity"(); jbe@339: PERFORM "calculate_member_counts"(); jbe@339: FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP jbe@339: "persist_v" := NULL; jbe@339: LOOP jbe@339: "persist_v" := "check_issue"("issue_id_v", "persist_v"); jbe@339: EXIT WHEN "persist_v" ISNULL; jbe@339: END LOOP; jbe@339: END LOOP; jbe@339: RETURN; jbe@339: END; jbe@339: $$; jbe@339: 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.'; jbe@339: jbe@339: CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) jbe@339: RETURNS VOID jbe@339: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@339: DECLARE jbe@339: "issue_row" "issue"%ROWTYPE; jbe@339: BEGIN jbe@339: SELECT * INTO "issue_row" jbe@339: FROM "issue" WHERE "id" = "issue_id_p" jbe@339: FOR UPDATE; jbe@339: IF "issue_row"."cleaned" ISNULL THEN jbe@339: UPDATE "issue" SET jbe@339: "state" = 'voting', jbe@339: "closed" = NULL jbe@339: WHERE "id" = "issue_id_p"; jbe@339: DELETE FROM "delegating_voter" jbe@339: WHERE "issue_id" = "issue_id_p"; jbe@339: DELETE FROM "direct_voter" jbe@339: WHERE "issue_id" = "issue_id_p"; jbe@339: DELETE FROM "delegating_interest_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p"; jbe@339: DELETE FROM "direct_interest_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p"; jbe@339: DELETE FROM "delegating_population_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p"; jbe@339: DELETE FROM "direct_population_snapshot" jbe@339: WHERE "issue_id" = "issue_id_p"; jbe@339: DELETE FROM "non_voter" jbe@339: WHERE "issue_id" = "issue_id_p"; jbe@339: DELETE FROM "delegation" jbe@339: WHERE "issue_id" = "issue_id_p"; jbe@339: DELETE FROM "supporter" jbe@339: USING "initiative" -- NOTE: due to missing index on issue_id jbe@339: WHERE "initiative"."issue_id" = "issue_id_p" jbe@339: AND "supporter"."initiative_id" = "initiative_id"; jbe@339: UPDATE "issue" SET jbe@339: "state" = "issue_row"."state", jbe@339: "closed" = "issue_row"."closed", jbe@339: "cleaned" = now() jbe@339: WHERE "id" = "issue_id_p"; jbe@339: END IF; jbe@339: RETURN; jbe@339: END; jbe@339: $$; jbe@339: jbe@346: SELECT "set_harmonic_initiative_weights"("id") FROM "issue"; jbe@346: jbe@339: COMMIT;