jbe@178: SELECT "calculate_ranks"("id") FROM "issue_with_ranks_missing"; jbe@178: jbe@177: BEGIN; jbe@177: jbe@177: CREATE OR REPLACE VIEW "liquid_feedback_version" AS jbe@177: SELECT * FROM (VALUES ('1.5.0_devel', 1, 5, -1)) jbe@177: AS "subquery"("string", "major", "minor", "revision"); jbe@177: jbe@182: ALTER TABLE "member" ADD COLUMN "invite_code" TEXT UNIQUE; jbe@182: ALTER TABLE "member" ADD COLUMN "admin_comment" TEXT; jbe@182: ALTER TABLE "member" ADD COLUMN "activated" TIMESTAMPTZ; jbe@182: ALTER TABLE "member" ALTER COLUMN "active" SET DEFAULT FALSE; jbe@177: ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT; jbe@182: jbe@182: COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code'; jbe@182: COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time'; jbe@182: COMMENT ON COLUMN "member"."activated" IS 'Timestamp of activation of account (i.e. usage of "invite_code"); needs to be set for "active" members'; jbe@182: COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. When the user does not log in for an extended period of time, this flag may be set to FALSE. If the user is not locked, he/she may reset the active flag by logging in (has to be set to TRUE by frontend on every login).'; jbe@177: COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"'; jbe@177: jbe@177: CREATE TABLE "rendered_member_statement" ( jbe@177: PRIMARY KEY ("member_id", "format"), jbe@177: "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@177: "format" TEXT, jbe@177: "content" TEXT NOT NULL ); jbe@177: jbe@177: COMMENT ON TABLE "rendered_member_statement" IS 'This table may be used by frontends to cache "rendered" member statements (e.g. HTML output generated from wiki text)'; jbe@177: jbe@177: ALTER TABLE "policy" ADD COLUMN "direct_majority_num" INT4 NOT NULL DEFAULT 1; jbe@177: ALTER TABLE "policy" ADD COLUMN "direct_majority_den" INT4 NOT NULL DEFAULT 2; jbe@177: ALTER TABLE "policy" ADD COLUMN "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE; jbe@177: ALTER TABLE "policy" ADD COLUMN "direct_majority_positive" INT4 NOT NULL DEFAULT 0; jbe@177: ALTER TABLE "policy" ADD COLUMN "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0; jbe@177: ALTER TABLE "policy" ADD COLUMN "indirect_majority_num" INT4 NOT NULL DEFAULT 1; jbe@177: ALTER TABLE "policy" ADD COLUMN "indirect_majority_den" INT4 NOT NULL DEFAULT 2; jbe@177: ALTER TABLE "policy" ADD COLUMN "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE; jbe@177: ALTER TABLE "policy" ADD COLUMN "indirect_majority_positive" INT4 NOT NULL DEFAULT 0; jbe@177: ALTER TABLE "policy" ADD COLUMN "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0; jbe@177: ALTER TABLE "policy" ADD COLUMN "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE; jbe@177: ALTER TABLE "policy" ADD COLUMN "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE; jbe@177: jbe@177: UPDATE "policy" SET jbe@177: "direct_majority_num" = "majority_num", jbe@177: "direct_majority_den" = "majority_den", jbe@177: "direct_majority_strict" = "majority_strict", jbe@177: "indirect_majority_num" = "majority_num", jbe@177: "indirect_majority_den" = "majority_den", jbe@177: "indirect_majority_strict" = "majority_strict"; jbe@177: jbe@177: ALTER TABLE "policy" DROP COLUMN "majority_num"; jbe@177: ALTER TABLE "policy" DROP COLUMN "majority_den"; jbe@177: ALTER TABLE "policy" DROP COLUMN "majority_strict"; jbe@177: jbe@177: COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner'; jbe@177: COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner'; jbe@177: COMMENT ON COLUMN "policy"."direct_majority_strict" IS 'If TRUE, then the direct majority must be strictly greater than "direct_majority_num"/"direct_majority_den", otherwise it may also be equal.'; jbe@177: COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner'; jbe@177: COMMENT ON COLUMN "policy"."direct_majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be attainable as winner'; jbe@177: COMMENT ON COLUMN "policy"."indirect_majority_num" IS 'Numerator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner'; jbe@177: COMMENT ON COLUMN "policy"."indirect_majority_den" IS 'Denominator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner'; jbe@177: COMMENT ON COLUMN "policy"."indirect_majority_strict" IS 'If TRUE, then the indirect majority must be strictly greater than "indirect_majority_num"/"indirect_majority_den", otherwise it may also be equal.'; jbe@177: COMMENT ON COLUMN "policy"."indirect_majority_positive" IS 'Absolute number of votes in favor of the winner neccessary in a beat path to the status quo for an initaitive to be attainable as winner'; jbe@177: COMMENT ON COLUMN "policy"."indirect_majority_non_negative" IS 'Absolute number of sum of votes in favor and abstentions in a beat path to the status quo for an initiative to be attainable as winner'; jbe@177: COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'Causes initiatives with "reverse_beat_path" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."reverse_beat_path". This option ensures both that a winning initiative is never tied in a (weak) condorcet paradox with the status quo and a winning initiative always beats the status quo directly with a simple majority.'; jbe@177: COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'Causes initiatives with "multistage_majority" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."multistage_majority". This disqualifies initiatives which could cause an instable result. An instable result in this meaning is a result such that repeating the ballot with same preferences but with the winner of the first ballot as status quo would lead to a different winner in the second ballot. If there are no direct majorities required for the winner, or if in direct comparison only simple majorities are required and "no_reverse_beat_path" is true, then results are always stable and this flag does not have any effect on the winner (but still affects the "eligible" flag of an "initiative").'; jbe@177: jbe@177: ALTER TABLE "area" DROP COLUMN "autoreject_weight"; jbe@177: jbe@177: DROP VIEW "open_issue"; jbe@177: DROP VIEW "issue_with_ranks_missing"; jbe@177: jbe@177: ALTER TABLE "issue" DROP COLUMN "vote_now"; jbe@177: ALTER TABLE "issue" DROP COLUMN "vote_later"; jbe@177: ALTER TABLE "issue" ADD COLUMN "status_quo_schulze_rank" INT4; jbe@177: jbe@177: CREATE VIEW "open_issue" AS jbe@177: SELECT * FROM "issue" WHERE "closed" ISNULL; jbe@177: jbe@177: COMMENT ON VIEW "open_issue" IS 'All open issues'; jbe@177: jbe@177: CREATE VIEW "issue_with_ranks_missing" AS jbe@177: SELECT * FROM "issue" jbe@177: WHERE "fully_frozen" NOTNULL jbe@177: AND "closed" NOTNULL jbe@177: AND "ranks_available" = FALSE; jbe@177: jbe@177: COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet'; jbe@177: jbe@177: COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.'; jbe@177: COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated'; jbe@177: COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function'; jbe@177: jbe@179: DROP VIEW "battle_view"; jbe@179: jbe@180: ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"; jbe@180: ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"; jbe@180: ALTER TABLE "initiative" DROP CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"; jbe@180: jbe@178: ALTER TABLE "initiative" DROP COLUMN "agreed"; jbe@177: ALTER TABLE "initiative" ADD COLUMN "direct_majority" BOOLEAN; jbe@177: ALTER TABLE "initiative" ADD COLUMN "indirect_majority" BOOLEAN; jbe@177: ALTER TABLE "initiative" ADD COLUMN "schulze_rank" INT4; jbe@177: ALTER TABLE "initiative" ADD COLUMN "better_than_status_quo" BOOLEAN; jbe@177: ALTER TABLE "initiative" ADD COLUMN "worse_than_status_quo" BOOLEAN; jbe@177: ALTER TABLE "initiative" ADD COLUMN "reverse_beat_path" BOOLEAN; jbe@177: ALTER TABLE "initiative" ADD COLUMN "multistage_majority" BOOLEAN; jbe@177: ALTER TABLE "initiative" ADD COLUMN "eligible" BOOLEAN; jbe@177: ALTER TABLE "initiative" ADD COLUMN "winner" BOOLEAN; jbe@177: jbe@177: ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( jbe@177: ( "admitted" NOTNULL AND "admitted" = TRUE ) OR jbe@177: ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND jbe@177: "direct_majority" ISNULL AND "indirect_majority" ISNULL AND jbe@177: "schulze_rank" ISNULL AND jbe@177: "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND jbe@177: "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND jbe@177: "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ); jbe@177: ALTER TABLE "initiative" ADD CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")); jbe@177: ALTER TABLE "initiative" ADD CONSTRAINT "minimum_requirement_to_be_eligible" CHECK ( jbe@177: "eligible" = FALSE OR jbe@177: ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ); jbe@177: ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE); jbe@177: ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1); jbe@177: ALTER TABLE "initiative" ADD CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE); jbe@177: ALTER TABLE "initiative" ADD CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank"); jbe@177: jbe@177: COMMENT ON COLUMN "initiative"."direct_majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"'; jbe@177: COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths'; jbe@177: COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking'; jbe@177: COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)'; jbe@177: COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)'; jbe@177: COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there is a beat path (may include ties), from this initiative to the status quo'; jbe@177: COMMENT ON COLUMN "initiative"."multistage_majority" IS 'TRUE, if either (a) this initiative has no better rank than the status quo, or (b) there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X'; jbe@177: COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"'; jbe@177: COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"'; jbe@177: COMMENT ON COLUMN "initiative"."rank" IS 'Unique ranking for all "admitted" initiatives per issue; lower rank is better; a winner always has rank 1, but rank 1 does not imply that an initiative is winner; initiatives with "direct_majority" AND "indirect_majority" always have a better (lower) rank than other initiatives'; jbe@177: jbe@177: ALTER TABLE "battle" DROP CONSTRAINT "battle_pkey"; jbe@177: ALTER TABLE "battle" ALTER COLUMN "issue_id" SET NOT NULL; jbe@177: ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK ( jbe@177: "winning_initiative_id" != "losing_initiative_id" OR jbe@177: ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR jbe@177: ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ); jbe@177: jbe@177: CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id"); jbe@177: CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL; jbe@177: CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL; jbe@177: jbe@177: ALTER TABLE "suggestion" ADD COLUMN "draft_id" INT8; jbe@177: -- more later jbe@177: jbe@177: ALTER TABLE "suggestion" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE; jbe@177: ALTER TABLE "suggestion" ADD COLUMN "formatting_engine" TEXT; jbe@177: ALTER TABLE "suggestion" RENAME COLUMN "description" TO "content"; jbe@177: jbe@177: DROP TRIGGER "update_text_search_data" ON "suggestion"; jbe@177: jbe@177: CREATE TRIGGER "update_text_search_data" jbe@177: BEFORE INSERT OR UPDATE ON "suggestion" jbe@177: FOR EACH ROW EXECUTE PROCEDURE jbe@177: tsvector_update_trigger('text_search_data', 'pg_catalog.simple', jbe@177: "name", "content"); jbe@177: jbe@177: COMMENT ON COLUMN "suggestion"."draft_id" IS 'Draft, which the author has seen when composing the suggestion; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; jbe@177: jbe@177: CREATE TABLE "rendered_suggestion" ( jbe@177: PRIMARY KEY ("suggestion_id", "format"), jbe@177: "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE, jbe@177: "format" TEXT, jbe@177: "content" TEXT NOT NULL ); jbe@177: jbe@177: COMMENT ON TABLE "rendered_suggestion" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)'; jbe@177: jbe@177: DROP VIEW "area_member_count"; jbe@177: jbe@177: ALTER TABLE "membership" DROP COLUMN "autoreject"; jbe@177: jbe@177: ALTER TABLE "interest" DROP COLUMN "autoreject"; jbe@177: ALTER TABLE "interest" DROP COLUMN "voting_requested"; jbe@177: jbe@177: ALTER TABLE "supporter" DROP CONSTRAINT "supporter_initiative_id_fkey"; jbe@177: ALTER TABLE "supporter" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE; jbe@177: jbe@177: COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")'; jbe@177: jbe@177: ALTER TABLE "direct_interest_snapshot" DROP COLUMN "voting_requested"; jbe@177: ALTER TABLE "direct_voter" DROP COLUMN "autoreject"; jbe@177: jbe@177: DROP TRIGGER "default_for_draft_id" ON "supporter"; jbe@177: DROP FUNCTION "supporter_default_for_draft_id_trigger"(); jbe@177: jbe@177: CREATE FUNCTION "default_for_draft_id_trigger"() jbe@177: RETURNS TRIGGER jbe@177: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@177: BEGIN jbe@177: IF NEW."draft_id" ISNULL THEN jbe@177: SELECT "id" INTO NEW."draft_id" FROM "current_draft" jbe@177: WHERE "initiative_id" = NEW."initiative_id"; jbe@177: END IF; jbe@177: RETURN NEW; jbe@177: END; jbe@177: $$; jbe@177: jbe@177: CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion" jbe@177: FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"(); jbe@177: CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter" jbe@177: FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"(); jbe@177: jbe@177: COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"'; jbe@177: COMMENT ON TRIGGER "default_for_draft_id" ON "suggestion" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default'; jbe@177: COMMENT ON TRIGGER "default_for_draft_id" ON "supporter" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default'; jbe@177: jbe@177: CREATE VIEW "area_member_count" AS jbe@177: SELECT jbe@177: "area"."id" AS "area_id", jbe@177: count("member"."id") AS "direct_member_count", jbe@177: coalesce( jbe@177: sum( jbe@177: CASE WHEN "member"."id" NOTNULL THEN jbe@177: "membership_weight"("area"."id", "member"."id") jbe@177: ELSE 0 END jbe@177: ) jbe@177: ) AS "member_weight" jbe@177: FROM "area" jbe@177: LEFT JOIN "membership" jbe@177: ON "area"."id" = "membership"."area_id" jbe@177: LEFT JOIN "privilege" jbe@177: ON "privilege"."unit_id" = "area"."unit_id" jbe@177: AND "privilege"."member_id" = "membership"."member_id" jbe@177: AND "privilege"."voting_right" jbe@177: LEFT JOIN "member" jbe@177: ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here! jbe@177: AND "member"."active" jbe@177: GROUP BY "area"."id"; jbe@177: jbe@177: COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"'; jbe@177: jbe@177: CREATE VIEW "battle_participant" AS jbe@177: SELECT "initiative"."id", "initiative"."issue_id" jbe@177: FROM "issue" JOIN "initiative" jbe@177: ON "issue"."id" = "initiative"."issue_id" jbe@177: WHERE "initiative"."admitted" jbe@177: UNION ALL jbe@177: SELECT NULL, "id" AS "issue_id" jbe@177: FROM "issue"; jbe@177: jbe@177: COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference'; jbe@177: jbe@179: CREATE VIEW "battle_view" AS jbe@177: SELECT jbe@177: "issue"."id" AS "issue_id", jbe@177: "winning_initiative"."id" AS "winning_initiative_id", jbe@177: "losing_initiative"."id" AS "losing_initiative_id", jbe@177: sum( jbe@177: CASE WHEN jbe@177: coalesce("better_vote"."grade", 0) > jbe@177: coalesce("worse_vote"."grade", 0) jbe@177: THEN "direct_voter"."weight" ELSE 0 END jbe@177: ) AS "count" jbe@177: FROM "issue" jbe@177: LEFT JOIN "direct_voter" jbe@177: ON "issue"."id" = "direct_voter"."issue_id" jbe@177: JOIN "battle_participant" AS "winning_initiative" jbe@177: ON "issue"."id" = "winning_initiative"."issue_id" jbe@177: JOIN "battle_participant" AS "losing_initiative" jbe@177: ON "issue"."id" = "losing_initiative"."issue_id" jbe@177: LEFT JOIN "vote" AS "better_vote" jbe@177: ON "direct_voter"."member_id" = "better_vote"."member_id" jbe@177: AND "winning_initiative"."id" = "better_vote"."initiative_id" jbe@177: LEFT JOIN "vote" AS "worse_vote" jbe@177: ON "direct_voter"."member_id" = "worse_vote"."member_id" jbe@177: AND "losing_initiative"."id" = "worse_vote"."initiative_id" jbe@177: WHERE "issue"."closed" NOTNULL jbe@177: AND "issue"."cleaned" ISNULL jbe@177: AND ( jbe@177: "winning_initiative"."id" != "losing_initiative"."id" OR jbe@177: ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR jbe@177: ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) ) jbe@177: GROUP BY jbe@177: "issue"."id", jbe@177: "winning_initiative"."id", jbe@177: "losing_initiative"."id"; jbe@177: jbe@177: COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative (or status-quo) to another initiative (or status-quo); Used to fill "battle" table'; jbe@177: jbe@177: CREATE OR REPLACE FUNCTION "create_interest_snapshot" jbe@177: ( "issue_id_p" "issue"."id"%TYPE ) jbe@177: RETURNS VOID jbe@177: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@177: DECLARE jbe@177: "member_id_v" "member"."id"%TYPE; jbe@177: BEGIN jbe@177: DELETE FROM "direct_interest_snapshot" jbe@177: WHERE "issue_id" = "issue_id_p" jbe@177: AND "event" = 'periodic'; jbe@177: DELETE FROM "delegating_interest_snapshot" jbe@177: WHERE "issue_id" = "issue_id_p" jbe@177: AND "event" = 'periodic'; jbe@177: DELETE FROM "direct_supporter_snapshot" jbe@177: WHERE "issue_id" = "issue_id_p" jbe@177: AND "event" = 'periodic'; jbe@177: INSERT INTO "direct_interest_snapshot" jbe@177: ("issue_id", "event", "member_id") jbe@177: SELECT jbe@177: "issue_id_p" AS "issue_id", jbe@177: 'periodic' AS "event", jbe@177: "member"."id" AS "member_id" jbe@177: FROM "issue" jbe@177: JOIN "area" ON "issue"."area_id" = "area"."id" jbe@177: JOIN "interest" ON "issue"."id" = "interest"."issue_id" jbe@177: JOIN "member" ON "interest"."member_id" = "member"."id" jbe@177: JOIN "privilege" jbe@177: ON "privilege"."unit_id" = "area"."unit_id" jbe@177: AND "privilege"."member_id" = "member"."id" jbe@177: WHERE "issue"."id" = "issue_id_p" jbe@177: AND "member"."active" AND "privilege"."voting_right"; jbe@177: FOR "member_id_v" IN jbe@177: SELECT "member_id" FROM "direct_interest_snapshot" jbe@177: WHERE "issue_id" = "issue_id_p" jbe@177: AND "event" = 'periodic' jbe@177: LOOP jbe@177: UPDATE "direct_interest_snapshot" SET jbe@177: "weight" = 1 + jbe@177: "weight_of_added_delegations_for_interest_snapshot"( jbe@177: "issue_id_p", jbe@177: "member_id_v", jbe@177: '{}' jbe@177: ) jbe@177: WHERE "issue_id" = "issue_id_p" jbe@177: AND "event" = 'periodic' jbe@177: AND "member_id" = "member_id_v"; jbe@177: END LOOP; jbe@177: INSERT INTO "direct_supporter_snapshot" jbe@177: ( "issue_id", "initiative_id", "event", "member_id", jbe@177: "informed", "satisfied" ) jbe@177: SELECT jbe@177: "issue_id_p" AS "issue_id", jbe@177: "initiative"."id" AS "initiative_id", jbe@177: 'periodic' AS "event", jbe@177: "supporter"."member_id" AS "member_id", jbe@177: "supporter"."draft_id" = "current_draft"."id" AS "informed", jbe@177: NOT EXISTS ( jbe@177: SELECT NULL FROM "critical_opinion" jbe@177: WHERE "initiative_id" = "initiative"."id" jbe@177: AND "member_id" = "supporter"."member_id" jbe@177: ) AS "satisfied" jbe@177: FROM "initiative" jbe@177: JOIN "supporter" jbe@177: ON "supporter"."initiative_id" = "initiative"."id" jbe@177: JOIN "current_draft" jbe@177: ON "initiative"."id" = "current_draft"."initiative_id" jbe@177: JOIN "direct_interest_snapshot" jbe@177: ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" jbe@177: AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" jbe@177: AND "event" = 'periodic' jbe@177: WHERE "initiative"."issue_id" = "issue_id_p"; jbe@177: RETURN; jbe@177: END; jbe@177: $$; jbe@177: jbe@177: CREATE OR REPLACE FUNCTION "create_snapshot" jbe@177: ( "issue_id_p" "issue"."id"%TYPE ) jbe@177: RETURNS VOID jbe@177: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@177: DECLARE jbe@177: "initiative_id_v" "initiative"."id"%TYPE; jbe@177: "suggestion_id_v" "suggestion"."id"%TYPE; jbe@177: BEGIN jbe@177: PERFORM "lock_issue"("issue_id_p"); jbe@177: PERFORM "create_population_snapshot"("issue_id_p"); jbe@177: PERFORM "create_interest_snapshot"("issue_id_p"); jbe@177: UPDATE "issue" SET jbe@177: "snapshot" = now(), jbe@177: "latest_snapshot_event" = 'periodic', jbe@177: "population" = ( jbe@177: SELECT coalesce(sum("weight"), 0) jbe@177: FROM "direct_population_snapshot" jbe@177: WHERE "issue_id" = "issue_id_p" jbe@177: AND "event" = 'periodic' jbe@177: ) jbe@177: WHERE "id" = "issue_id_p"; jbe@177: FOR "initiative_id_v" IN jbe@177: SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" jbe@177: LOOP jbe@177: UPDATE "initiative" SET jbe@177: "supporter_count" = ( jbe@177: SELECT coalesce(sum("di"."weight"), 0) jbe@177: FROM "direct_interest_snapshot" AS "di" jbe@177: JOIN "direct_supporter_snapshot" AS "ds" jbe@177: ON "di"."member_id" = "ds"."member_id" jbe@177: WHERE "di"."issue_id" = "issue_id_p" jbe@177: AND "di"."event" = 'periodic' jbe@177: AND "ds"."initiative_id" = "initiative_id_v" jbe@177: AND "ds"."event" = 'periodic' jbe@177: ), jbe@177: "informed_supporter_count" = ( jbe@177: SELECT coalesce(sum("di"."weight"), 0) jbe@177: FROM "direct_interest_snapshot" AS "di" jbe@177: JOIN "direct_supporter_snapshot" AS "ds" jbe@177: ON "di"."member_id" = "ds"."member_id" jbe@177: WHERE "di"."issue_id" = "issue_id_p" jbe@177: AND "di"."event" = 'periodic' jbe@177: AND "ds"."initiative_id" = "initiative_id_v" jbe@177: AND "ds"."event" = 'periodic' jbe@177: AND "ds"."informed" jbe@177: ), jbe@177: "satisfied_supporter_count" = ( jbe@177: SELECT coalesce(sum("di"."weight"), 0) jbe@177: FROM "direct_interest_snapshot" AS "di" jbe@177: JOIN "direct_supporter_snapshot" AS "ds" jbe@177: ON "di"."member_id" = "ds"."member_id" jbe@177: WHERE "di"."issue_id" = "issue_id_p" jbe@177: AND "di"."event" = 'periodic' jbe@177: AND "ds"."initiative_id" = "initiative_id_v" jbe@177: AND "ds"."event" = 'periodic' jbe@177: AND "ds"."satisfied" jbe@177: ), jbe@177: "satisfied_informed_supporter_count" = ( jbe@177: SELECT coalesce(sum("di"."weight"), 0) jbe@177: FROM "direct_interest_snapshot" AS "di" jbe@177: JOIN "direct_supporter_snapshot" AS "ds" jbe@177: ON "di"."member_id" = "ds"."member_id" jbe@177: WHERE "di"."issue_id" = "issue_id_p" jbe@177: AND "di"."event" = 'periodic' jbe@177: AND "ds"."initiative_id" = "initiative_id_v" jbe@177: AND "ds"."event" = 'periodic' jbe@177: AND "ds"."informed" jbe@177: AND "ds"."satisfied" jbe@177: ) jbe@177: WHERE "id" = "initiative_id_v"; jbe@177: FOR "suggestion_id_v" IN jbe@177: SELECT "id" FROM "suggestion" jbe@177: WHERE "initiative_id" = "initiative_id_v" jbe@177: LOOP jbe@177: UPDATE "suggestion" SET jbe@177: "minus2_unfulfilled_count" = ( jbe@177: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@177: FROM "issue" CROSS JOIN "opinion" jbe@177: JOIN "direct_interest_snapshot" AS "snapshot" jbe@177: ON "snapshot"."issue_id" = "issue"."id" jbe@177: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@177: AND "snapshot"."member_id" = "opinion"."member_id" jbe@177: WHERE "issue"."id" = "issue_id_p" jbe@177: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@177: AND "opinion"."degree" = -2 jbe@177: AND "opinion"."fulfilled" = FALSE jbe@177: ), jbe@177: "minus2_fulfilled_count" = ( jbe@177: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@177: FROM "issue" CROSS JOIN "opinion" jbe@177: JOIN "direct_interest_snapshot" AS "snapshot" jbe@177: ON "snapshot"."issue_id" = "issue"."id" jbe@177: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@177: AND "snapshot"."member_id" = "opinion"."member_id" jbe@177: WHERE "issue"."id" = "issue_id_p" jbe@177: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@177: AND "opinion"."degree" = -2 jbe@177: AND "opinion"."fulfilled" = TRUE jbe@177: ), jbe@177: "minus1_unfulfilled_count" = ( jbe@177: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@177: FROM "issue" CROSS JOIN "opinion" jbe@177: JOIN "direct_interest_snapshot" AS "snapshot" jbe@177: ON "snapshot"."issue_id" = "issue"."id" jbe@177: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@177: AND "snapshot"."member_id" = "opinion"."member_id" jbe@177: WHERE "issue"."id" = "issue_id_p" jbe@177: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@177: AND "opinion"."degree" = -1 jbe@177: AND "opinion"."fulfilled" = FALSE jbe@177: ), jbe@177: "minus1_fulfilled_count" = ( jbe@177: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@177: FROM "issue" CROSS JOIN "opinion" jbe@177: JOIN "direct_interest_snapshot" AS "snapshot" jbe@177: ON "snapshot"."issue_id" = "issue"."id" jbe@177: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@177: AND "snapshot"."member_id" = "opinion"."member_id" jbe@177: WHERE "issue"."id" = "issue_id_p" jbe@177: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@177: AND "opinion"."degree" = -1 jbe@177: AND "opinion"."fulfilled" = TRUE jbe@177: ), jbe@177: "plus1_unfulfilled_count" = ( jbe@177: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@177: FROM "issue" CROSS JOIN "opinion" jbe@177: JOIN "direct_interest_snapshot" AS "snapshot" jbe@177: ON "snapshot"."issue_id" = "issue"."id" jbe@177: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@177: AND "snapshot"."member_id" = "opinion"."member_id" jbe@177: WHERE "issue"."id" = "issue_id_p" jbe@177: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@177: AND "opinion"."degree" = 1 jbe@177: AND "opinion"."fulfilled" = FALSE jbe@177: ), jbe@177: "plus1_fulfilled_count" = ( jbe@177: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@177: FROM "issue" CROSS JOIN "opinion" jbe@177: JOIN "direct_interest_snapshot" AS "snapshot" jbe@177: ON "snapshot"."issue_id" = "issue"."id" jbe@177: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@177: AND "snapshot"."member_id" = "opinion"."member_id" jbe@177: WHERE "issue"."id" = "issue_id_p" jbe@177: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@177: AND "opinion"."degree" = 1 jbe@177: AND "opinion"."fulfilled" = TRUE jbe@177: ), jbe@177: "plus2_unfulfilled_count" = ( jbe@177: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@177: FROM "issue" CROSS JOIN "opinion" jbe@177: JOIN "direct_interest_snapshot" AS "snapshot" jbe@177: ON "snapshot"."issue_id" = "issue"."id" jbe@177: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@177: AND "snapshot"."member_id" = "opinion"."member_id" jbe@177: WHERE "issue"."id" = "issue_id_p" jbe@177: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@177: AND "opinion"."degree" = 2 jbe@177: AND "opinion"."fulfilled" = FALSE jbe@177: ), jbe@177: "plus2_fulfilled_count" = ( jbe@177: SELECT coalesce(sum("snapshot"."weight"), 0) jbe@177: FROM "issue" CROSS JOIN "opinion" jbe@177: JOIN "direct_interest_snapshot" AS "snapshot" jbe@177: ON "snapshot"."issue_id" = "issue"."id" jbe@177: AND "snapshot"."event" = "issue"."latest_snapshot_event" jbe@177: AND "snapshot"."member_id" = "opinion"."member_id" jbe@177: WHERE "issue"."id" = "issue_id_p" jbe@177: AND "opinion"."suggestion_id" = "suggestion_id_v" jbe@177: AND "opinion"."degree" = 2 jbe@177: AND "opinion"."fulfilled" = TRUE jbe@177: ) jbe@177: WHERE "suggestion"."id" = "suggestion_id_v"; jbe@177: END LOOP; jbe@177: END LOOP; jbe@177: RETURN; jbe@177: END; jbe@177: $$; jbe@177: jbe@177: CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) jbe@177: RETURNS VOID jbe@177: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@177: DECLARE jbe@177: "area_id_v" "area"."id"%TYPE; jbe@177: "unit_id_v" "unit"."id"%TYPE; jbe@177: "member_id_v" "member"."id"%TYPE; jbe@177: BEGIN jbe@177: PERFORM "lock_issue"("issue_id_p"); jbe@177: SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; jbe@177: SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; jbe@177: -- delete delegating votes (in cases of manual reset of issue state): jbe@177: DELETE FROM "delegating_voter" jbe@177: WHERE "issue_id" = "issue_id_p"; jbe@177: -- delete votes from non-privileged voters: jbe@177: DELETE FROM "direct_voter" jbe@177: USING ( jbe@177: SELECT jbe@177: "direct_voter"."member_id" jbe@177: FROM "direct_voter" jbe@177: JOIN "member" ON "direct_voter"."member_id" = "member"."id" jbe@177: LEFT JOIN "privilege" jbe@177: ON "privilege"."unit_id" = "unit_id_v" jbe@177: AND "privilege"."member_id" = "direct_voter"."member_id" jbe@177: WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( jbe@177: "member"."active" = FALSE OR jbe@177: "privilege"."voting_right" ISNULL OR jbe@177: "privilege"."voting_right" = FALSE jbe@177: ) jbe@177: ) AS "subquery" jbe@177: WHERE "direct_voter"."issue_id" = "issue_id_p" jbe@177: AND "direct_voter"."member_id" = "subquery"."member_id"; jbe@177: -- consider delegations: jbe@177: UPDATE "direct_voter" SET "weight" = 1 jbe@177: WHERE "issue_id" = "issue_id_p"; jbe@177: PERFORM "add_vote_delegations"("issue_id_p"); jbe@177: -- set voter count and mark issue as being calculated: jbe@177: UPDATE "issue" SET jbe@177: "state" = 'calculation', jbe@177: "closed" = now(), jbe@177: "voter_count" = ( jbe@177: SELECT coalesce(sum("weight"), 0) jbe@177: FROM "direct_voter" WHERE "issue_id" = "issue_id_p" jbe@177: ) jbe@177: WHERE "id" = "issue_id_p"; jbe@177: -- materialize battle_view: jbe@177: -- NOTE: "closed" column of issue must be set at this point jbe@177: DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; jbe@177: INSERT INTO "battle" ( jbe@177: "issue_id", jbe@177: "winning_initiative_id", "losing_initiative_id", jbe@177: "count" jbe@177: ) SELECT jbe@177: "issue_id", jbe@177: "winning_initiative_id", "losing_initiative_id", jbe@177: "count" jbe@177: FROM "battle_view" WHERE "issue_id" = "issue_id_p"; jbe@177: -- copy "positive_votes" and "negative_votes" from "battle" table: jbe@177: UPDATE "initiative" SET jbe@177: "positive_votes" = "battle_win"."count", jbe@177: "negative_votes" = "battle_lose"."count" jbe@177: FROM "battle" AS "battle_win", "battle" AS "battle_lose" jbe@177: WHERE jbe@177: "battle_win"."issue_id" = "issue_id_p" AND jbe@177: "battle_win"."winning_initiative_id" = "initiative"."id" AND jbe@177: "battle_win"."losing_initiative_id" ISNULL AND jbe@177: "battle_lose"."issue_id" = "issue_id_p" AND jbe@177: "battle_lose"."losing_initiative_id" = "initiative"."id" AND jbe@177: "battle_lose"."winning_initiative_id" ISNULL; jbe@177: END; jbe@177: $$; jbe@177: jbe@177: DROP FUNCTION "array_init_string"(INTEGER); jbe@177: DROP FUNCTION "square_matrix_init_string"(INTEGER); jbe@177: jbe@177: CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) jbe@177: RETURNS VOID jbe@177: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@177: DECLARE jbe@177: "issue_row" "issue"%ROWTYPE; jbe@177: "policy_row" "policy"%ROWTYPE; jbe@177: "dimension_v" INTEGER; jbe@177: "vote_matrix" INT4[][]; -- absolute votes jbe@177: "matrix" INT8[][]; -- defeat strength / best paths jbe@177: "i" INTEGER; jbe@177: "j" INTEGER; jbe@177: "k" INTEGER; jbe@177: "battle_row" "battle"%ROWTYPE; jbe@177: "rank_ary" INT4[]; jbe@177: "rank_v" INT4; jbe@177: "done_v" INTEGER; jbe@177: "winners_ary" INTEGER[]; jbe@177: "initiative_id_v" "initiative"."id"%TYPE; jbe@177: BEGIN jbe@177: SELECT * INTO "issue_row" jbe@177: FROM "issue" WHERE "id" = "issue_id_p" jbe@177: FOR UPDATE; jbe@177: SELECT * INTO "policy_row" jbe@177: FROM "policy" WHERE "id" = "issue_row"."policy_id"; jbe@177: SELECT count(1) INTO "dimension_v" jbe@177: FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; jbe@177: -- Create "vote_matrix" with absolute number of votes in pairwise jbe@177: -- comparison: jbe@177: "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); jbe@177: "i" := 1; jbe@177: "j" := 2; jbe@177: FOR "battle_row" IN jbe@177: SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" jbe@177: ORDER BY jbe@177: "winning_initiative_id" NULLS LAST, jbe@177: "losing_initiative_id" NULLS LAST jbe@177: LOOP jbe@177: "vote_matrix"["i"]["j"] := "battle_row"."count"; jbe@177: IF "j" = "dimension_v" THEN jbe@177: "i" := "i" + 1; jbe@177: "j" := 1; jbe@177: ELSE jbe@177: "j" := "j" + 1; jbe@177: IF "j" = "i" THEN jbe@177: "j" := "j" + 1; jbe@177: END IF; jbe@177: END IF; jbe@177: END LOOP; jbe@177: IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN jbe@177: RAISE EXCEPTION 'Wrong battle count (should not happen)'; jbe@177: END IF; jbe@177: -- Store defeat strengths in "matrix" using "defeat_strength" jbe@177: -- function: jbe@177: "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); jbe@177: "i" := 1; jbe@177: LOOP jbe@177: "j" := 1; jbe@177: LOOP jbe@177: IF "i" != "j" THEN jbe@177: "matrix"["i"]["j"] := "defeat_strength"( jbe@177: "vote_matrix"["i"]["j"], jbe@177: "vote_matrix"["j"]["i"] jbe@177: ); jbe@177: END IF; jbe@177: EXIT WHEN "j" = "dimension_v"; jbe@177: "j" := "j" + 1; jbe@177: END LOOP; jbe@177: EXIT WHEN "i" = "dimension_v"; jbe@177: "i" := "i" + 1; jbe@177: END LOOP; jbe@177: -- Find best paths: jbe@177: "i" := 1; jbe@177: LOOP jbe@177: "j" := 1; jbe@177: LOOP jbe@177: IF "i" != "j" THEN jbe@177: "k" := 1; jbe@177: LOOP jbe@177: IF "i" != "k" AND "j" != "k" THEN jbe@177: IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN jbe@177: IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN jbe@177: "matrix"["j"]["k"] := "matrix"["j"]["i"]; jbe@177: END IF; jbe@177: ELSE jbe@177: IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN jbe@177: "matrix"["j"]["k"] := "matrix"["i"]["k"]; jbe@177: END IF; jbe@177: END IF; jbe@177: END IF; jbe@177: EXIT WHEN "k" = "dimension_v"; jbe@177: "k" := "k" + 1; jbe@177: END LOOP; jbe@177: END IF; jbe@177: EXIT WHEN "j" = "dimension_v"; jbe@177: "j" := "j" + 1; jbe@177: END LOOP; jbe@177: EXIT WHEN "i" = "dimension_v"; jbe@177: "i" := "i" + 1; jbe@177: END LOOP; jbe@177: -- Determine order of winners: jbe@177: "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); jbe@177: "rank_v" := 1; jbe@177: "done_v" := 0; jbe@177: LOOP jbe@177: "winners_ary" := '{}'; jbe@177: "i" := 1; jbe@177: LOOP jbe@177: IF "rank_ary"["i"] ISNULL THEN jbe@177: "j" := 1; jbe@177: LOOP jbe@177: IF jbe@177: "i" != "j" AND jbe@177: "rank_ary"["j"] ISNULL AND jbe@177: "matrix"["j"]["i"] > "matrix"["i"]["j"] jbe@177: THEN jbe@177: -- someone else is better jbe@177: EXIT; jbe@177: END IF; jbe@177: IF "j" = "dimension_v" THEN jbe@177: -- noone is better jbe@177: "winners_ary" := "winners_ary" || "i"; jbe@177: EXIT; jbe@177: END IF; jbe@177: "j" := "j" + 1; jbe@177: END LOOP; jbe@177: END IF; jbe@177: EXIT WHEN "i" = "dimension_v"; jbe@177: "i" := "i" + 1; jbe@177: END LOOP; jbe@177: "i" := 1; jbe@177: LOOP jbe@177: "rank_ary"["winners_ary"["i"]] := "rank_v"; jbe@177: "done_v" := "done_v" + 1; jbe@177: EXIT WHEN "i" = array_upper("winners_ary", 1); jbe@177: "i" := "i" + 1; jbe@177: END LOOP; jbe@177: EXIT WHEN "done_v" = "dimension_v"; jbe@177: "rank_v" := "rank_v" + 1; jbe@177: END LOOP; jbe@177: -- write preliminary results: jbe@177: "i" := 1; jbe@177: FOR "initiative_id_v" IN jbe@177: SELECT "id" FROM "initiative" jbe@177: WHERE "issue_id" = "issue_id_p" AND "admitted" jbe@177: ORDER BY "id" jbe@177: LOOP jbe@177: UPDATE "initiative" SET jbe@177: "direct_majority" = jbe@177: CASE WHEN "policy_row"."direct_majority_strict" THEN jbe@177: "positive_votes" * "policy_row"."direct_majority_den" > jbe@177: "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") jbe@177: ELSE jbe@177: "positive_votes" * "policy_row"."direct_majority_den" >= jbe@177: "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") jbe@177: END jbe@177: AND "positive_votes" >= "policy_row"."direct_majority_positive" jbe@177: AND "issue_row"."voter_count"-"negative_votes" >= jbe@177: "policy_row"."direct_majority_non_negative", jbe@177: "indirect_majority" = jbe@177: CASE WHEN "policy_row"."indirect_majority_strict" THEN jbe@177: "positive_votes" * "policy_row"."indirect_majority_den" > jbe@177: "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") jbe@177: ELSE jbe@177: "positive_votes" * "policy_row"."indirect_majority_den" >= jbe@177: "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") jbe@177: END jbe@177: AND "positive_votes" >= "policy_row"."indirect_majority_positive" jbe@177: AND "issue_row"."voter_count"-"negative_votes" >= jbe@177: "policy_row"."indirect_majority_non_negative", jbe@177: "schulze_rank" = "rank_ary"["i"], jbe@177: "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], jbe@177: "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], jbe@177: "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], jbe@177: "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, jbe@177: "winner" = FALSE jbe@177: WHERE "id" = "initiative_id_v"; jbe@177: "i" := "i" + 1; jbe@177: END LOOP; jbe@177: IF "i" != "dimension_v" THEN jbe@177: RAISE EXCEPTION 'Wrong winner count (should not happen)'; jbe@177: END IF; jbe@177: -- take indirect majorities into account: jbe@177: LOOP jbe@177: UPDATE "initiative" SET "indirect_majority" = TRUE jbe@177: FROM ( jbe@177: SELECT "new_initiative"."id" AS "initiative_id" jbe@177: FROM "initiative" "old_initiative" jbe@177: JOIN "initiative" "new_initiative" jbe@177: ON "new_initiative"."issue_id" = "issue_id_p" jbe@177: AND "new_initiative"."indirect_majority" = FALSE jbe@177: JOIN "battle" "battle_win" jbe@177: ON "battle_win"."issue_id" = "issue_id_p" jbe@177: AND "battle_win"."winning_initiative_id" = "new_initiative"."id" jbe@177: AND "battle_win"."losing_initiative_id" = "old_initiative"."id" jbe@177: JOIN "battle" "battle_lose" jbe@177: ON "battle_lose"."issue_id" = "issue_id_p" jbe@177: AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" jbe@177: AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" jbe@177: WHERE "old_initiative"."issue_id" = "issue_id_p" jbe@177: AND "old_initiative"."indirect_majority" = TRUE jbe@177: AND CASE WHEN "policy_row"."indirect_majority_strict" THEN jbe@177: "battle_win"."count" * "policy_row"."indirect_majority_den" > jbe@177: "policy_row"."indirect_majority_num" * jbe@177: ("battle_win"."count"+"battle_lose"."count") jbe@177: ELSE jbe@177: "battle_win"."count" * "policy_row"."indirect_majority_den" >= jbe@177: "policy_row"."indirect_majority_num" * jbe@177: ("battle_win"."count"+"battle_lose"."count") jbe@177: END jbe@177: AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" jbe@177: AND "issue_row"."voter_count"-"battle_lose"."count" >= jbe@177: "policy_row"."indirect_majority_non_negative" jbe@177: ) AS "subquery" jbe@177: WHERE "id" = "subquery"."initiative_id"; jbe@177: EXIT WHEN NOT FOUND; jbe@177: END LOOP; jbe@177: -- set "multistage_majority" for remaining matching initiatives: jbe@177: UPDATE "initiative" SET "multistage_majority" = TRUE jbe@177: FROM ( jbe@177: SELECT "losing_initiative"."id" AS "initiative_id" jbe@177: FROM "initiative" "losing_initiative" jbe@177: JOIN "initiative" "winning_initiative" jbe@177: ON "winning_initiative"."issue_id" = "issue_id_p" jbe@177: AND "winning_initiative"."admitted" jbe@177: JOIN "battle" "battle_win" jbe@177: ON "battle_win"."issue_id" = "issue_id_p" jbe@177: AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" jbe@177: AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" jbe@177: JOIN "battle" "battle_lose" jbe@177: ON "battle_lose"."issue_id" = "issue_id_p" jbe@177: AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" jbe@177: AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" jbe@177: WHERE "losing_initiative"."issue_id" = "issue_id_p" jbe@177: AND "losing_initiative"."admitted" jbe@177: AND "winning_initiative"."schulze_rank" < jbe@177: "losing_initiative"."schulze_rank" jbe@177: AND "battle_win"."count" > "battle_lose"."count" jbe@177: AND ( jbe@177: "battle_win"."count" > "winning_initiative"."positive_votes" OR jbe@177: "battle_lose"."count" < "losing_initiative"."negative_votes" ) jbe@177: ) AS "subquery" jbe@177: WHERE "id" = "subquery"."initiative_id"; jbe@177: -- mark eligible initiatives: jbe@177: UPDATE "initiative" SET "eligible" = TRUE jbe@177: WHERE "issue_id" = "issue_id_p" jbe@177: AND "initiative"."direct_majority" jbe@177: AND "initiative"."indirect_majority" jbe@177: AND "initiative"."better_than_status_quo" jbe@177: AND ( jbe@177: "policy_row"."no_multistage_majority" = FALSE OR jbe@177: "initiative"."multistage_majority" = FALSE ) jbe@177: AND ( jbe@177: "policy_row"."no_reverse_beat_path" = FALSE OR jbe@177: "initiative"."reverse_beat_path" = FALSE ); jbe@177: -- mark final winner: jbe@177: UPDATE "initiative" SET "winner" = TRUE jbe@177: FROM ( jbe@177: SELECT "id" AS "initiative_id" jbe@177: FROM "initiative" jbe@177: WHERE "issue_id" = "issue_id_p" AND "eligible" jbe@177: ORDER BY "schulze_rank", "id" jbe@177: LIMIT 1 jbe@177: ) AS "subquery" jbe@177: WHERE "id" = "subquery"."initiative_id"; jbe@177: -- write (final) ranks: jbe@177: "rank_v" := 1; jbe@177: FOR "initiative_id_v" IN jbe@177: SELECT "id" jbe@177: FROM "initiative" jbe@177: WHERE "issue_id" = "issue_id_p" AND "admitted" jbe@177: ORDER BY jbe@177: "winner" DESC, jbe@177: ("direct_majority" AND "indirect_majority") DESC, jbe@177: "schulze_rank", jbe@177: "id" jbe@177: LOOP jbe@177: UPDATE "initiative" SET "rank" = "rank_v" jbe@177: WHERE "id" = "initiative_id_v"; jbe@177: "rank_v" := "rank_v" + 1; jbe@177: END LOOP; jbe@177: -- set schulze rank of status quo and mark issue as finished: jbe@177: UPDATE "issue" SET jbe@177: "status_quo_schulze_rank" = "rank_ary"["dimension_v"], jbe@177: "state" = jbe@177: CASE WHEN EXISTS ( jbe@177: SELECT NULL FROM "initiative" jbe@177: WHERE "issue_id" = "issue_id_p" AND "winner" jbe@177: ) THEN jbe@177: 'finished_with_winner'::"issue_state" jbe@177: ELSE jbe@177: 'finished_without_winner'::"issue_state" jbe@177: END, jbe@177: "ranks_available" = TRUE jbe@177: WHERE "id" = "issue_id_p"; jbe@177: RETURN; jbe@177: END; jbe@177: $$; jbe@177: jbe@177: CREATE OR REPLACE FUNCTION "check_issue" jbe@177: ( "issue_id_p" "issue"."id"%TYPE ) jbe@177: RETURNS VOID jbe@177: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@177: DECLARE jbe@177: "issue_row" "issue"%ROWTYPE; jbe@177: "policy_row" "policy"%ROWTYPE; jbe@177: BEGIN jbe@177: PERFORM "lock_issue"("issue_id_p"); jbe@177: SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; jbe@177: -- only process open issues: jbe@177: IF "issue_row"."closed" ISNULL THEN jbe@177: SELECT * INTO "policy_row" FROM "policy" jbe@177: WHERE "id" = "issue_row"."policy_id"; jbe@177: -- create a snapshot, unless issue is already fully frozen: jbe@177: IF "issue_row"."fully_frozen" ISNULL THEN jbe@177: PERFORM "create_snapshot"("issue_id_p"); jbe@177: SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; jbe@177: END IF; jbe@177: -- eventually close or accept issues, which have not been accepted: jbe@177: IF "issue_row"."accepted" ISNULL THEN jbe@177: IF EXISTS ( jbe@177: SELECT NULL FROM "initiative" jbe@177: WHERE "issue_id" = "issue_id_p" jbe@177: AND "supporter_count" > 0 jbe@177: AND "supporter_count" * "policy_row"."issue_quorum_den" jbe@177: >= "issue_row"."population" * "policy_row"."issue_quorum_num" jbe@177: ) THEN jbe@177: -- accept issues, if supporter count is high enough jbe@177: PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); jbe@177: -- NOTE: "issue_row" used later jbe@177: "issue_row"."state" := 'discussion'; jbe@177: "issue_row"."accepted" := now(); jbe@177: UPDATE "issue" SET jbe@177: "state" = "issue_row"."state", jbe@177: "accepted" = "issue_row"."accepted" jbe@177: WHERE "id" = "issue_row"."id"; jbe@177: ELSIF jbe@177: now() >= "issue_row"."created" + "issue_row"."admission_time" jbe@177: THEN jbe@177: -- close issues, if admission time has expired jbe@177: PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); jbe@177: UPDATE "issue" SET jbe@177: "state" = 'canceled_issue_not_accepted', jbe@177: "closed" = now() jbe@177: WHERE "id" = "issue_row"."id"; jbe@177: END IF; jbe@177: END IF; jbe@177: -- eventually half freeze issues: jbe@177: IF jbe@177: -- NOTE: issue can't be closed at this point, if it has been accepted jbe@177: "issue_row"."accepted" NOTNULL AND jbe@177: "issue_row"."half_frozen" ISNULL jbe@177: THEN jbe@177: IF jbe@177: now() >= "issue_row"."accepted" + "issue_row"."discussion_time" jbe@177: THEN jbe@177: PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); jbe@177: -- NOTE: "issue_row" used later jbe@177: "issue_row"."state" := 'verification'; jbe@177: "issue_row"."half_frozen" := now(); jbe@177: UPDATE "issue" SET jbe@177: "state" = "issue_row"."state", jbe@177: "half_frozen" = "issue_row"."half_frozen" jbe@177: WHERE "id" = "issue_row"."id"; jbe@177: END IF; jbe@177: END IF; jbe@177: -- close issues after some time, if all initiatives have been revoked: jbe@177: IF jbe@177: "issue_row"."closed" ISNULL AND jbe@177: NOT EXISTS ( jbe@177: -- all initiatives are revoked jbe@177: SELECT NULL FROM "initiative" jbe@177: WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL jbe@177: ) AND ( jbe@177: -- and issue has not been accepted yet jbe@177: "issue_row"."accepted" ISNULL OR jbe@177: NOT EXISTS ( jbe@177: -- or no initiatives have been revoked lately jbe@177: SELECT NULL FROM "initiative" jbe@177: WHERE "issue_id" = "issue_id_p" jbe@177: AND now() < "revoked" + "issue_row"."verification_time" jbe@177: ) OR ( jbe@177: -- or verification time has elapsed jbe@177: "issue_row"."half_frozen" NOTNULL AND jbe@177: "issue_row"."fully_frozen" ISNULL AND jbe@177: now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" jbe@177: ) jbe@177: ) jbe@177: THEN jbe@177: -- NOTE: "issue_row" used later jbe@177: IF "issue_row"."accepted" ISNULL THEN jbe@177: "issue_row"."state" := 'canceled_revoked_before_accepted'; jbe@177: ELSIF "issue_row"."half_frozen" ISNULL THEN jbe@177: "issue_row"."state" := 'canceled_after_revocation_during_discussion'; jbe@177: ELSE jbe@177: "issue_row"."state" := 'canceled_after_revocation_during_verification'; jbe@177: END IF; jbe@177: "issue_row"."closed" := now(); jbe@177: UPDATE "issue" SET jbe@177: "state" = "issue_row"."state", jbe@177: "closed" = "issue_row"."closed" jbe@177: WHERE "id" = "issue_row"."id"; jbe@177: END IF; jbe@177: -- fully freeze issue after verification time: jbe@177: IF jbe@177: "issue_row"."half_frozen" NOTNULL AND jbe@177: "issue_row"."fully_frozen" ISNULL AND jbe@177: "issue_row"."closed" ISNULL AND jbe@177: now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" jbe@177: THEN jbe@177: PERFORM "freeze_after_snapshot"("issue_id_p"); jbe@177: -- NOTE: "issue" might change, thus "issue_row" has to be updated below jbe@177: END IF; jbe@177: SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; jbe@177: -- close issue by calling close_voting(...) after voting time: jbe@177: IF jbe@177: "issue_row"."closed" ISNULL AND jbe@177: "issue_row"."fully_frozen" NOTNULL AND jbe@177: now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" jbe@177: THEN jbe@177: PERFORM "close_voting"("issue_id_p"); jbe@177: -- calculate ranks will not consume much time and can be done now jbe@177: PERFORM "calculate_ranks"("issue_id_p"); jbe@177: END IF; jbe@177: END IF; jbe@177: RETURN; jbe@177: END; jbe@177: $$; jbe@177: jbe@178: COMMIT; jbe@178: jbe@178: BEGIN; jbe@178: jbe@182: UPDATE "member" SET "activated" = "created"; jbe@182: jbe@182: UPDATE "member" SET jbe@182: "created" = "invite_code"."created", jbe@182: "invite_code" = "invite_code"."code", jbe@182: "admin_comment" = "invite_code"."comment" jbe@182: FROM "invite_code" jbe@182: WHERE "member"."id" = "invite_code"."member_id"; jbe@182: jbe@182: DROP TABLE "invite_code_unit"; jbe@182: DROP TABLE "invite_code"; jbe@182: jbe@178: UPDATE "initiative" SET jbe@178: "direct_majority" = "rank" NOTNULL, jbe@178: "indirect_majority" = "rank" NOTNULL, jbe@178: "schulze_rank" = "rank", jbe@178: "better_than_status_quo" = "rank" NOTNULL, jbe@178: "worse_than_status_quo" = "rank" ISNULL, jbe@178: "reverse_beat_path" = "rank" ISNULL, jbe@178: "multistage_majority" = "rank" ISNULL, jbe@178: "eligible" = "rank" NOTNULL, jbe@178: "winner" = ("rank" = 1) jbe@178: FROM "issue" jbe@178: WHERE "issue"."id" = "initiative"."issue_id" jbe@178: AND "issue"."state" IN ('finished_without_winner', 'finished_with_winner') jbe@178: AND "initiative"."admitted"; jbe@177: jbe@178: UPDATE "issue" SET "status_quo_schulze_rank" = "subquery"."rank" jbe@178: FROM ( jbe@178: SELECT jbe@178: "issue"."id" AS "issue_id", jbe@178: COALESCE(max("initiative"."rank") + 1) AS "rank" jbe@178: FROM "issue" JOIN "initiative" jbe@178: ON "issue"."id" = "initiative"."issue_id" jbe@178: WHERE "issue"."state" IN ('finished_without_winner', 'finished_with_winner') jbe@178: AND "initiative"."admitted" jbe@178: GROUP BY "issue"."id" jbe@178: ) AS "subquery" jbe@178: WHERE "issue"."id" = "subquery"."issue_id"; jbe@178: jbe@178: CREATE FUNCTION "update__set_remaining_ranks"("issue_id_p" "issue"."id"%TYPE) jbe@178: RETURNS VOID jbe@178: LANGUAGE 'plpgsql' AS $$ jbe@178: DECLARE jbe@178: "rank_v" INT4; jbe@178: "initiative_id_v" INT4; jbe@178: BEGIN jbe@178: SELECT "status_quo_schulze_rank" INTO "rank_v" jbe@178: FROM "issue" WHERE "id" = "issue_id_p"; jbe@178: FOR "initiative_id_v" IN jbe@178: SELECT "id" FROM "initiative" jbe@178: WHERE "issue_id" = "issue_id_p" AND "admitted" AND "rank" ISNULL jbe@178: ORDER BY "vote_ratio"("positive_votes", "negative_votes") DESC jbe@178: LOOP jbe@178: UPDATE "initiative" SET jbe@178: "schulze_rank" = "rank_v" + 1, jbe@178: "rank" = "rank_v" jbe@178: WHERE "id" = "initiative_id_v"; jbe@178: "rank_v" := "rank_v" + 1; jbe@178: END LOOP; jbe@178: RETURN; jbe@178: END; jbe@178: $$; jbe@178: jbe@178: SELECT "update__set_remaining_ranks"("id") FROM "issue" jbe@178: WHERE "state" IN ('finished_without_winner', 'finished_with_winner'); jbe@178: jbe@178: DROP FUNCTION "update__set_remaining_ranks"("issue"."id"%TYPE); jbe@177: jbe@177: UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id" jbe@177: FROM ( jbe@177: SELECT DISTINCT ON ("suggestion"."id") jbe@177: "suggestion"."id" AS "suggestion_id", jbe@177: "draft"."id" AS "draft_id" jbe@177: FROM "suggestion" JOIN "draft" jbe@177: ON "suggestion"."initiative_id" = "draft"."initiative_id" jbe@177: WHERE "draft"."created" <= "suggestion"."created" jbe@177: ORDER BY "suggestion"."id", "draft"."created" DESC jbe@177: ) AS "subquery" jbe@177: WHERE "suggestion"."id" = "subquery"."suggestion_id"; jbe@177: jbe@178: COMMIT; jbe@178: jbe@182: ALTER TABLE "member" ADD CONSTRAINT "not_active_without_activated" jbe@182: CHECK ("activated" NOTNULL OR "active" = FALSE); jbe@177: ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;