# HG changeset patch # User jbe # Date 1307547060 -7200 # Node ID 9975ce3f20ee7eaa00e8c1fc0d9ac24a12bc2e48 # Parent b2d87fbb123b6a0aca64834bc08a5958c9828955 First version of update script from v1.4.0 to v1.5.0 diff -r b2d87fbb123b -r 9975ce3f20ee update/core-update.v1.4.0-v1.5.0.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/update/core-update.v1.4.0-v1.5.0.sql Wed Jun 08 17:31:00 2011 +0200 @@ -0,0 +1,1044 @@ +BEGIN; + +CREATE OR REPLACE VIEW "liquid_feedback_version" AS + SELECT * FROM (VALUES ('1.5.0_devel', 1, 5, -1)) + AS "subquery"("string", "major", "minor", "revision"); + +ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT; +COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"'; + +CREATE TABLE "rendered_member_statement" ( + PRIMARY KEY ("member_id", "format"), + "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, + "format" TEXT, + "content" TEXT NOT NULL ); + +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)'; + +ALTER TABLE "policy" ADD COLUMN "direct_majority_num" INT4 NOT NULL DEFAULT 1; +ALTER TABLE "policy" ADD COLUMN "direct_majority_den" INT4 NOT NULL DEFAULT 2; +ALTER TABLE "policy" ADD COLUMN "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE; +ALTER TABLE "policy" ADD COLUMN "direct_majority_positive" INT4 NOT NULL DEFAULT 0; +ALTER TABLE "policy" ADD COLUMN "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0; +ALTER TABLE "policy" ADD COLUMN "indirect_majority_num" INT4 NOT NULL DEFAULT 1; +ALTER TABLE "policy" ADD COLUMN "indirect_majority_den" INT4 NOT NULL DEFAULT 2; +ALTER TABLE "policy" ADD COLUMN "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE; +ALTER TABLE "policy" ADD COLUMN "indirect_majority_positive" INT4 NOT NULL DEFAULT 0; +ALTER TABLE "policy" ADD COLUMN "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0; +ALTER TABLE "policy" ADD COLUMN "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE; +ALTER TABLE "policy" ADD COLUMN "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE; + +UPDATE "policy" SET + "direct_majority_num" = "majority_num", + "direct_majority_den" = "majority_den", + "direct_majority_strict" = "majority_strict", + "indirect_majority_num" = "majority_num", + "indirect_majority_den" = "majority_den", + "indirect_majority_strict" = "majority_strict"; + +ALTER TABLE "policy" DROP COLUMN "majority_num"; +ALTER TABLE "policy" DROP COLUMN "majority_den"; +ALTER TABLE "policy" DROP COLUMN "majority_strict"; + +COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner'; +COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner'; +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.'; +COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner'; +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'; +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'; +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'; +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.'; +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'; +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'; +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.'; +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").'; + +ALTER TABLE "area" DROP COLUMN "autoreject_weight"; + +DROP VIEW "open_issue"; +DROP VIEW "issue_with_ranks_missing"; + +ALTER TABLE "issue" DROP COLUMN "vote_now"; +ALTER TABLE "issue" DROP COLUMN "vote_later"; +ALTER TABLE "issue" ADD COLUMN "status_quo_schulze_rank" INT4; + +CREATE VIEW "open_issue" AS + SELECT * FROM "issue" WHERE "closed" ISNULL; + +COMMENT ON VIEW "open_issue" IS 'All open issues'; + +CREATE VIEW "issue_with_ranks_missing" AS + SELECT * FROM "issue" + WHERE "fully_frozen" NOTNULL + AND "closed" NOTNULL + AND "ranks_available" = FALSE; + +COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet'; + +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.'; +COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated'; +COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function'; + +ALTER TABLE "initiative" ADD COLUMN "direct_majority" BOOLEAN; +ALTER TABLE "initiative" ADD COLUMN "indirect_majority" BOOLEAN; +ALTER TABLE "initiative" ADD COLUMN "schulze_rank" INT4; +ALTER TABLE "initiative" ADD COLUMN "better_than_status_quo" BOOLEAN; +ALTER TABLE "initiative" ADD COLUMN "worse_than_status_quo" BOOLEAN; +ALTER TABLE "initiative" ADD COLUMN "reverse_beat_path" BOOLEAN; +ALTER TABLE "initiative" ADD COLUMN "multistage_majority" BOOLEAN; +ALTER TABLE "initiative" ADD COLUMN "eligible" BOOLEAN; +ALTER TABLE "initiative" ADD COLUMN "winner" BOOLEAN; + +ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"; +ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"; +ALTER TABLE "initiative" DROP CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"; + +ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( + ( "admitted" NOTNULL AND "admitted" = TRUE ) OR + ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND + "direct_majority" ISNULL AND "indirect_majority" ISNULL AND + "schulze_rank" ISNULL AND + "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND + "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND + "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ); +ALTER TABLE "initiative" ADD CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")); +ALTER TABLE "initiative" ADD CONSTRAINT "minimum_requirement_to_be_eligible" CHECK ( + "eligible" = FALSE OR +("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ); +ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE); +ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1); +ALTER TABLE "initiative" ADD CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE); +ALTER TABLE "initiative" ADD CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank"); + +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"'; +COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths'; +COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking'; +COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)'; +COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)'; +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'; +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'; +COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"'; +COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"'; +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'; + +ALTER TABLE "battle" DROP CONSTRAINT "battle_pkey"; +ALTER TABLE "battle" ALTER COLUMN "issue_id" SET NOT NULL; +ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK ( + "winning_initiative_id" != "losing_initiative_id" OR + ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR + ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ); + +CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id"); +CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL; +CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL; + +ALTER TABLE "suggestion" ADD COLUMN "draft_id" INT8; +-- more later + +ALTER TABLE "suggestion" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE; +ALTER TABLE "suggestion" ADD COLUMN "formatting_engine" TEXT; +ALTER TABLE "suggestion" RENAME COLUMN "description" TO "content"; + +DROP TRIGGER "update_text_search_data" ON "suggestion"; + +CREATE TRIGGER "update_text_search_data" + BEFORE INSERT OR UPDATE ON "suggestion" + FOR EACH ROW EXECUTE PROCEDURE + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', + "name", "content"); + +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")'; + +CREATE TABLE "rendered_suggestion" ( + PRIMARY KEY ("suggestion_id", "format"), + "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE, + "format" TEXT, + "content" TEXT NOT NULL ); + +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)'; + +DROP VIEW "area_member_count"; + +ALTER TABLE "membership" DROP COLUMN "autoreject"; + +ALTER TABLE "interest" DROP COLUMN "autoreject"; +ALTER TABLE "interest" DROP COLUMN "voting_requested"; + +ALTER TABLE "supporter" DROP CONSTRAINT "supporter_initiative_id_fkey"; +ALTER TABLE "supporter" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE; + +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")'; + +ALTER TABLE "direct_interest_snapshot" DROP COLUMN "voting_requested"; +ALTER TABLE "direct_voter" DROP COLUMN "autoreject"; + +DROP TRIGGER "default_for_draft_id" ON "supporter"; +DROP FUNCTION "supporter_default_for_draft_id_trigger"(); + +CREATE FUNCTION "default_for_draft_id_trigger"() + RETURNS TRIGGER + LANGUAGE 'plpgsql' VOLATILE AS $$ + BEGIN + IF NEW."draft_id" ISNULL THEN + SELECT "id" INTO NEW."draft_id" FROM "current_draft" + WHERE "initiative_id" = NEW."initiative_id"; + END IF; + RETURN NEW; + END; + $$; + +CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion" + FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"(); +CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter" + FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"(); + +COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"'; +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'; +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'; + +CREATE VIEW "area_member_count" AS + SELECT + "area"."id" AS "area_id", + count("member"."id") AS "direct_member_count", + coalesce( + sum( + CASE WHEN "member"."id" NOTNULL THEN + "membership_weight"("area"."id", "member"."id") + ELSE 0 END + ) + ) AS "member_weight" + FROM "area" + LEFT JOIN "membership" + ON "area"."id" = "membership"."area_id" + LEFT JOIN "privilege" + ON "privilege"."unit_id" = "area"."unit_id" + AND "privilege"."member_id" = "membership"."member_id" + AND "privilege"."voting_right" + LEFT JOIN "member" + ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here! + AND "member"."active" + GROUP BY "area"."id"; + +COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"'; + +CREATE VIEW "battle_participant" AS + SELECT "initiative"."id", "initiative"."issue_id" + FROM "issue" JOIN "initiative" + ON "issue"."id" = "initiative"."issue_id" + WHERE "initiative"."admitted" + UNION ALL + SELECT NULL, "id" AS "issue_id" + FROM "issue"; + +COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference'; + +CREATE OR REPLACE VIEW "battle_view" AS + SELECT + "issue"."id" AS "issue_id", + "winning_initiative"."id" AS "winning_initiative_id", + "losing_initiative"."id" AS "losing_initiative_id", + sum( + CASE WHEN + coalesce("better_vote"."grade", 0) > + coalesce("worse_vote"."grade", 0) + THEN "direct_voter"."weight" ELSE 0 END + ) AS "count" + FROM "issue" + LEFT JOIN "direct_voter" + ON "issue"."id" = "direct_voter"."issue_id" + JOIN "battle_participant" AS "winning_initiative" + ON "issue"."id" = "winning_initiative"."issue_id" + JOIN "battle_participant" AS "losing_initiative" + ON "issue"."id" = "losing_initiative"."issue_id" + LEFT JOIN "vote" AS "better_vote" + ON "direct_voter"."member_id" = "better_vote"."member_id" + AND "winning_initiative"."id" = "better_vote"."initiative_id" + LEFT JOIN "vote" AS "worse_vote" + ON "direct_voter"."member_id" = "worse_vote"."member_id" + AND "losing_initiative"."id" = "worse_vote"."initiative_id" + WHERE "issue"."closed" NOTNULL + AND "issue"."cleaned" ISNULL + AND ( + "winning_initiative"."id" != "losing_initiative"."id" OR + ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR + ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) ) + GROUP BY + "issue"."id", + "winning_initiative"."id", + "losing_initiative"."id"; + +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'; + +CREATE OR REPLACE FUNCTION "create_interest_snapshot" + ( "issue_id_p" "issue"."id"%TYPE ) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "member_id_v" "member"."id"%TYPE; + BEGIN + DELETE FROM "direct_interest_snapshot" + WHERE "issue_id" = "issue_id_p" + AND "event" = 'periodic'; + DELETE FROM "delegating_interest_snapshot" + WHERE "issue_id" = "issue_id_p" + AND "event" = 'periodic'; + DELETE FROM "direct_supporter_snapshot" + WHERE "issue_id" = "issue_id_p" + AND "event" = 'periodic'; + INSERT INTO "direct_interest_snapshot" + ("issue_id", "event", "member_id") + SELECT + "issue_id_p" AS "issue_id", + 'periodic' AS "event", + "member"."id" AS "member_id" + FROM "issue" + JOIN "area" ON "issue"."area_id" = "area"."id" + JOIN "interest" ON "issue"."id" = "interest"."issue_id" + JOIN "member" ON "interest"."member_id" = "member"."id" + JOIN "privilege" + ON "privilege"."unit_id" = "area"."unit_id" + AND "privilege"."member_id" = "member"."id" + WHERE "issue"."id" = "issue_id_p" + AND "member"."active" AND "privilege"."voting_right"; + FOR "member_id_v" IN + SELECT "member_id" FROM "direct_interest_snapshot" + WHERE "issue_id" = "issue_id_p" + AND "event" = 'periodic' + LOOP + UPDATE "direct_interest_snapshot" SET + "weight" = 1 + + "weight_of_added_delegations_for_interest_snapshot"( + "issue_id_p", + "member_id_v", + '{}' + ) + WHERE "issue_id" = "issue_id_p" + AND "event" = 'periodic' + AND "member_id" = "member_id_v"; + END LOOP; + INSERT INTO "direct_supporter_snapshot" + ( "issue_id", "initiative_id", "event", "member_id", + "informed", "satisfied" ) + SELECT + "issue_id_p" AS "issue_id", + "initiative"."id" AS "initiative_id", + 'periodic' AS "event", + "supporter"."member_id" AS "member_id", + "supporter"."draft_id" = "current_draft"."id" AS "informed", + NOT EXISTS ( + SELECT NULL FROM "critical_opinion" + WHERE "initiative_id" = "initiative"."id" + AND "member_id" = "supporter"."member_id" + ) AS "satisfied" + FROM "initiative" + JOIN "supporter" + ON "supporter"."initiative_id" = "initiative"."id" + JOIN "current_draft" + ON "initiative"."id" = "current_draft"."initiative_id" + JOIN "direct_interest_snapshot" + ON "supporter"."member_id" = "direct_interest_snapshot"."member_id" + AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id" + AND "event" = 'periodic' + WHERE "initiative"."issue_id" = "issue_id_p"; + RETURN; + END; + $$; + +CREATE OR REPLACE FUNCTION "create_snapshot" + ( "issue_id_p" "issue"."id"%TYPE ) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "initiative_id_v" "initiative"."id"%TYPE; + "suggestion_id_v" "suggestion"."id"%TYPE; + BEGIN + PERFORM "lock_issue"("issue_id_p"); + PERFORM "create_population_snapshot"("issue_id_p"); + PERFORM "create_interest_snapshot"("issue_id_p"); + UPDATE "issue" SET + "snapshot" = now(), + "latest_snapshot_event" = 'periodic', + "population" = ( + SELECT coalesce(sum("weight"), 0) + FROM "direct_population_snapshot" + WHERE "issue_id" = "issue_id_p" + AND "event" = 'periodic' + ) + WHERE "id" = "issue_id_p"; + FOR "initiative_id_v" IN + SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" + LOOP + UPDATE "initiative" SET + "supporter_count" = ( + SELECT coalesce(sum("di"."weight"), 0) + FROM "direct_interest_snapshot" AS "di" + JOIN "direct_supporter_snapshot" AS "ds" + ON "di"."member_id" = "ds"."member_id" + WHERE "di"."issue_id" = "issue_id_p" + AND "di"."event" = 'periodic' + AND "ds"."initiative_id" = "initiative_id_v" + AND "ds"."event" = 'periodic' + ), + "informed_supporter_count" = ( + SELECT coalesce(sum("di"."weight"), 0) + FROM "direct_interest_snapshot" AS "di" + JOIN "direct_supporter_snapshot" AS "ds" + ON "di"."member_id" = "ds"."member_id" + WHERE "di"."issue_id" = "issue_id_p" + AND "di"."event" = 'periodic' + AND "ds"."initiative_id" = "initiative_id_v" + AND "ds"."event" = 'periodic' + AND "ds"."informed" + ), + "satisfied_supporter_count" = ( + SELECT coalesce(sum("di"."weight"), 0) + FROM "direct_interest_snapshot" AS "di" + JOIN "direct_supporter_snapshot" AS "ds" + ON "di"."member_id" = "ds"."member_id" + WHERE "di"."issue_id" = "issue_id_p" + AND "di"."event" = 'periodic' + AND "ds"."initiative_id" = "initiative_id_v" + AND "ds"."event" = 'periodic' + AND "ds"."satisfied" + ), + "satisfied_informed_supporter_count" = ( + SELECT coalesce(sum("di"."weight"), 0) + FROM "direct_interest_snapshot" AS "di" + JOIN "direct_supporter_snapshot" AS "ds" + ON "di"."member_id" = "ds"."member_id" + WHERE "di"."issue_id" = "issue_id_p" + AND "di"."event" = 'periodic' + AND "ds"."initiative_id" = "initiative_id_v" + AND "ds"."event" = 'periodic' + AND "ds"."informed" + AND "ds"."satisfied" + ) + WHERE "id" = "initiative_id_v"; + FOR "suggestion_id_v" IN + SELECT "id" FROM "suggestion" + WHERE "initiative_id" = "initiative_id_v" + LOOP + UPDATE "suggestion" SET + "minus2_unfulfilled_count" = ( + SELECT coalesce(sum("snapshot"."weight"), 0) + FROM "issue" CROSS JOIN "opinion" + JOIN "direct_interest_snapshot" AS "snapshot" + ON "snapshot"."issue_id" = "issue"."id" + AND "snapshot"."event" = "issue"."latest_snapshot_event" + AND "snapshot"."member_id" = "opinion"."member_id" + WHERE "issue"."id" = "issue_id_p" + AND "opinion"."suggestion_id" = "suggestion_id_v" + AND "opinion"."degree" = -2 + AND "opinion"."fulfilled" = FALSE + ), + "minus2_fulfilled_count" = ( + SELECT coalesce(sum("snapshot"."weight"), 0) + FROM "issue" CROSS JOIN "opinion" + JOIN "direct_interest_snapshot" AS "snapshot" + ON "snapshot"."issue_id" = "issue"."id" + AND "snapshot"."event" = "issue"."latest_snapshot_event" + AND "snapshot"."member_id" = "opinion"."member_id" + WHERE "issue"."id" = "issue_id_p" + AND "opinion"."suggestion_id" = "suggestion_id_v" + AND "opinion"."degree" = -2 + AND "opinion"."fulfilled" = TRUE + ), + "minus1_unfulfilled_count" = ( + SELECT coalesce(sum("snapshot"."weight"), 0) + FROM "issue" CROSS JOIN "opinion" + JOIN "direct_interest_snapshot" AS "snapshot" + ON "snapshot"."issue_id" = "issue"."id" + AND "snapshot"."event" = "issue"."latest_snapshot_event" + AND "snapshot"."member_id" = "opinion"."member_id" + WHERE "issue"."id" = "issue_id_p" + AND "opinion"."suggestion_id" = "suggestion_id_v" + AND "opinion"."degree" = -1 + AND "opinion"."fulfilled" = FALSE + ), + "minus1_fulfilled_count" = ( + SELECT coalesce(sum("snapshot"."weight"), 0) + FROM "issue" CROSS JOIN "opinion" + JOIN "direct_interest_snapshot" AS "snapshot" + ON "snapshot"."issue_id" = "issue"."id" + AND "snapshot"."event" = "issue"."latest_snapshot_event" + AND "snapshot"."member_id" = "opinion"."member_id" + WHERE "issue"."id" = "issue_id_p" + AND "opinion"."suggestion_id" = "suggestion_id_v" + AND "opinion"."degree" = -1 + AND "opinion"."fulfilled" = TRUE + ), + "plus1_unfulfilled_count" = ( + SELECT coalesce(sum("snapshot"."weight"), 0) + FROM "issue" CROSS JOIN "opinion" + JOIN "direct_interest_snapshot" AS "snapshot" + ON "snapshot"."issue_id" = "issue"."id" + AND "snapshot"."event" = "issue"."latest_snapshot_event" + AND "snapshot"."member_id" = "opinion"."member_id" + WHERE "issue"."id" = "issue_id_p" + AND "opinion"."suggestion_id" = "suggestion_id_v" + AND "opinion"."degree" = 1 + AND "opinion"."fulfilled" = FALSE + ), + "plus1_fulfilled_count" = ( + SELECT coalesce(sum("snapshot"."weight"), 0) + FROM "issue" CROSS JOIN "opinion" + JOIN "direct_interest_snapshot" AS "snapshot" + ON "snapshot"."issue_id" = "issue"."id" + AND "snapshot"."event" = "issue"."latest_snapshot_event" + AND "snapshot"."member_id" = "opinion"."member_id" + WHERE "issue"."id" = "issue_id_p" + AND "opinion"."suggestion_id" = "suggestion_id_v" + AND "opinion"."degree" = 1 + AND "opinion"."fulfilled" = TRUE + ), + "plus2_unfulfilled_count" = ( + SELECT coalesce(sum("snapshot"."weight"), 0) + FROM "issue" CROSS JOIN "opinion" + JOIN "direct_interest_snapshot" AS "snapshot" + ON "snapshot"."issue_id" = "issue"."id" + AND "snapshot"."event" = "issue"."latest_snapshot_event" + AND "snapshot"."member_id" = "opinion"."member_id" + WHERE "issue"."id" = "issue_id_p" + AND "opinion"."suggestion_id" = "suggestion_id_v" + AND "opinion"."degree" = 2 + AND "opinion"."fulfilled" = FALSE + ), + "plus2_fulfilled_count" = ( + SELECT coalesce(sum("snapshot"."weight"), 0) + FROM "issue" CROSS JOIN "opinion" + JOIN "direct_interest_snapshot" AS "snapshot" + ON "snapshot"."issue_id" = "issue"."id" + AND "snapshot"."event" = "issue"."latest_snapshot_event" + AND "snapshot"."member_id" = "opinion"."member_id" + WHERE "issue"."id" = "issue_id_p" + AND "opinion"."suggestion_id" = "suggestion_id_v" + AND "opinion"."degree" = 2 + AND "opinion"."fulfilled" = TRUE + ) + WHERE "suggestion"."id" = "suggestion_id_v"; + END LOOP; + END LOOP; + RETURN; + END; + $$; + +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "area_id_v" "area"."id"%TYPE; + "unit_id_v" "unit"."id"%TYPE; + "member_id_v" "member"."id"%TYPE; + BEGIN + PERFORM "lock_issue"("issue_id_p"); + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; + -- delete delegating votes (in cases of manual reset of issue state): + DELETE FROM "delegating_voter" + WHERE "issue_id" = "issue_id_p"; + -- delete votes from non-privileged voters: + DELETE FROM "direct_voter" + USING ( + SELECT + "direct_voter"."member_id" + FROM "direct_voter" + JOIN "member" ON "direct_voter"."member_id" = "member"."id" + LEFT JOIN "privilege" + ON "privilege"."unit_id" = "unit_id_v" + AND "privilege"."member_id" = "direct_voter"."member_id" + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( + "member"."active" = FALSE OR + "privilege"."voting_right" ISNULL OR + "privilege"."voting_right" = FALSE + ) + ) AS "subquery" + WHERE "direct_voter"."issue_id" = "issue_id_p" + AND "direct_voter"."member_id" = "subquery"."member_id"; + -- consider delegations: + UPDATE "direct_voter" SET "weight" = 1 + WHERE "issue_id" = "issue_id_p"; + PERFORM "add_vote_delegations"("issue_id_p"); + -- set voter count and mark issue as being calculated: + UPDATE "issue" SET + "state" = 'calculation', + "closed" = now(), + "voter_count" = ( + SELECT coalesce(sum("weight"), 0) + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" + ) + WHERE "id" = "issue_id_p"; + -- materialize battle_view: + -- NOTE: "closed" column of issue must be set at this point + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; + INSERT INTO "battle" ( + "issue_id", + "winning_initiative_id", "losing_initiative_id", + "count" + ) SELECT + "issue_id", + "winning_initiative_id", "losing_initiative_id", + "count" + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; + -- copy "positive_votes" and "negative_votes" from "battle" table: + UPDATE "initiative" SET + "positive_votes" = "battle_win"."count", + "negative_votes" = "battle_lose"."count" + FROM "battle" AS "battle_win", "battle" AS "battle_lose" + WHERE + "battle_win"."issue_id" = "issue_id_p" AND + "battle_win"."winning_initiative_id" = "initiative"."id" AND + "battle_win"."losing_initiative_id" ISNULL AND + "battle_lose"."issue_id" = "issue_id_p" AND + "battle_lose"."losing_initiative_id" = "initiative"."id" AND + "battle_lose"."winning_initiative_id" ISNULL; + END; + $$; + +DROP FUNCTION "array_init_string"(INTEGER); +DROP FUNCTION "square_matrix_init_string"(INTEGER); + +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "issue_row" "issue"%ROWTYPE; + "policy_row" "policy"%ROWTYPE; + "dimension_v" INTEGER; + "vote_matrix" INT4[][]; -- absolute votes + "matrix" INT8[][]; -- defeat strength / best paths + "i" INTEGER; + "j" INTEGER; + "k" INTEGER; + "battle_row" "battle"%ROWTYPE; + "rank_ary" INT4[]; + "rank_v" INT4; + "done_v" INTEGER; + "winners_ary" INTEGER[]; + "initiative_id_v" "initiative"."id"%TYPE; + BEGIN + SELECT * INTO "issue_row" + FROM "issue" WHERE "id" = "issue_id_p" + FOR UPDATE; + SELECT * INTO "policy_row" + FROM "policy" WHERE "id" = "issue_row"."policy_id"; + SELECT count(1) INTO "dimension_v" + FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; + -- Create "vote_matrix" with absolute number of votes in pairwise + -- comparison: + "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); + "i" := 1; + "j" := 2; + FOR "battle_row" IN + SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" + ORDER BY + "winning_initiative_id" NULLS LAST, + "losing_initiative_id" NULLS LAST + LOOP + "vote_matrix"["i"]["j"] := "battle_row"."count"; + IF "j" = "dimension_v" THEN + "i" := "i" + 1; + "j" := 1; + ELSE + "j" := "j" + 1; + IF "j" = "i" THEN + "j" := "j" + 1; + END IF; + END IF; + END LOOP; + IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN + RAISE EXCEPTION 'Wrong battle count (should not happen)'; + END IF; + -- Store defeat strengths in "matrix" using "defeat_strength" + -- function: + "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); + "i" := 1; + LOOP + "j" := 1; + LOOP + IF "i" != "j" THEN + "matrix"["i"]["j"] := "defeat_strength"( + "vote_matrix"["i"]["j"], + "vote_matrix"["j"]["i"] + ); + END IF; + EXIT WHEN "j" = "dimension_v"; + "j" := "j" + 1; + END LOOP; + EXIT WHEN "i" = "dimension_v"; + "i" := "i" + 1; + END LOOP; + -- Find best paths: + "i" := 1; + LOOP + "j" := 1; + LOOP + IF "i" != "j" THEN + "k" := 1; + LOOP + IF "i" != "k" AND "j" != "k" THEN + IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN + IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN + "matrix"["j"]["k"] := "matrix"["j"]["i"]; + END IF; + ELSE + IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN + "matrix"["j"]["k"] := "matrix"["i"]["k"]; + END IF; + END IF; + END IF; + EXIT WHEN "k" = "dimension_v"; + "k" := "k" + 1; + END LOOP; + END IF; + EXIT WHEN "j" = "dimension_v"; + "j" := "j" + 1; + END LOOP; + EXIT WHEN "i" = "dimension_v"; + "i" := "i" + 1; + END LOOP; + -- Determine order of winners: + "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); + "rank_v" := 1; + "done_v" := 0; + LOOP + "winners_ary" := '{}'; + "i" := 1; + LOOP + IF "rank_ary"["i"] ISNULL THEN + "j" := 1; + LOOP + IF + "i" != "j" AND + "rank_ary"["j"] ISNULL AND + "matrix"["j"]["i"] > "matrix"["i"]["j"] + THEN + -- someone else is better + EXIT; + END IF; + IF "j" = "dimension_v" THEN + -- noone is better + "winners_ary" := "winners_ary" || "i"; + EXIT; + END IF; + "j" := "j" + 1; + END LOOP; + END IF; + EXIT WHEN "i" = "dimension_v"; + "i" := "i" + 1; + END LOOP; + "i" := 1; + LOOP + "rank_ary"["winners_ary"["i"]] := "rank_v"; + "done_v" := "done_v" + 1; + EXIT WHEN "i" = array_upper("winners_ary", 1); + "i" := "i" + 1; + END LOOP; + EXIT WHEN "done_v" = "dimension_v"; + "rank_v" := "rank_v" + 1; + END LOOP; + -- write preliminary results: + "i" := 1; + FOR "initiative_id_v" IN + SELECT "id" FROM "initiative" + WHERE "issue_id" = "issue_id_p" AND "admitted" + ORDER BY "id" + LOOP + UPDATE "initiative" SET + "direct_majority" = + CASE WHEN "policy_row"."direct_majority_strict" THEN + "positive_votes" * "policy_row"."direct_majority_den" > + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") + ELSE + "positive_votes" * "policy_row"."direct_majority_den" >= + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") + END + AND "positive_votes" >= "policy_row"."direct_majority_positive" + AND "issue_row"."voter_count"-"negative_votes" >= + "policy_row"."direct_majority_non_negative", + "indirect_majority" = + CASE WHEN "policy_row"."indirect_majority_strict" THEN + "positive_votes" * "policy_row"."indirect_majority_den" > + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") + ELSE + "positive_votes" * "policy_row"."indirect_majority_den" >= + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") + END + AND "positive_votes" >= "policy_row"."indirect_majority_positive" + AND "issue_row"."voter_count"-"negative_votes" >= + "policy_row"."indirect_majority_non_negative", + "schulze_rank" = "rank_ary"["i"], + "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], + "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], + "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], + "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, + "winner" = FALSE + WHERE "id" = "initiative_id_v"; + "i" := "i" + 1; + END LOOP; + IF "i" != "dimension_v" THEN + RAISE EXCEPTION 'Wrong winner count (should not happen)'; + END IF; + -- take indirect majorities into account: + LOOP + UPDATE "initiative" SET "indirect_majority" = TRUE + FROM ( + SELECT "new_initiative"."id" AS "initiative_id" + FROM "initiative" "old_initiative" + JOIN "initiative" "new_initiative" + ON "new_initiative"."issue_id" = "issue_id_p" + AND "new_initiative"."indirect_majority" = FALSE + JOIN "battle" "battle_win" + ON "battle_win"."issue_id" = "issue_id_p" + AND "battle_win"."winning_initiative_id" = "new_initiative"."id" + AND "battle_win"."losing_initiative_id" = "old_initiative"."id" + JOIN "battle" "battle_lose" + ON "battle_lose"."issue_id" = "issue_id_p" + AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" + AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" + WHERE "old_initiative"."issue_id" = "issue_id_p" + AND "old_initiative"."indirect_majority" = TRUE + AND CASE WHEN "policy_row"."indirect_majority_strict" THEN + "battle_win"."count" * "policy_row"."indirect_majority_den" > + "policy_row"."indirect_majority_num" * + ("battle_win"."count"+"battle_lose"."count") + ELSE + "battle_win"."count" * "policy_row"."indirect_majority_den" >= + "policy_row"."indirect_majority_num" * + ("battle_win"."count"+"battle_lose"."count") + END + AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" + AND "issue_row"."voter_count"-"battle_lose"."count" >= + "policy_row"."indirect_majority_non_negative" + ) AS "subquery" + WHERE "id" = "subquery"."initiative_id"; + EXIT WHEN NOT FOUND; + END LOOP; + -- set "multistage_majority" for remaining matching initiatives: + UPDATE "initiative" SET "multistage_majority" = TRUE + FROM ( + SELECT "losing_initiative"."id" AS "initiative_id" + FROM "initiative" "losing_initiative" + JOIN "initiative" "winning_initiative" + ON "winning_initiative"."issue_id" = "issue_id_p" + AND "winning_initiative"."admitted" + JOIN "battle" "battle_win" + ON "battle_win"."issue_id" = "issue_id_p" + AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" + AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" + JOIN "battle" "battle_lose" + ON "battle_lose"."issue_id" = "issue_id_p" + AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" + AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" + WHERE "losing_initiative"."issue_id" = "issue_id_p" + AND "losing_initiative"."admitted" + AND "winning_initiative"."schulze_rank" < + "losing_initiative"."schulze_rank" + AND "battle_win"."count" > "battle_lose"."count" + AND ( + "battle_win"."count" > "winning_initiative"."positive_votes" OR + "battle_lose"."count" < "losing_initiative"."negative_votes" ) + ) AS "subquery" + WHERE "id" = "subquery"."initiative_id"; + -- mark eligible initiatives: + UPDATE "initiative" SET "eligible" = TRUE + WHERE "issue_id" = "issue_id_p" + AND "initiative"."direct_majority" + AND "initiative"."indirect_majority" + AND "initiative"."better_than_status_quo" + AND ( + "policy_row"."no_multistage_majority" = FALSE OR + "initiative"."multistage_majority" = FALSE ) + AND ( + "policy_row"."no_reverse_beat_path" = FALSE OR + "initiative"."reverse_beat_path" = FALSE ); + -- mark final winner: + UPDATE "initiative" SET "winner" = TRUE + FROM ( + SELECT "id" AS "initiative_id" + FROM "initiative" + WHERE "issue_id" = "issue_id_p" AND "eligible" + ORDER BY "schulze_rank", "id" + LIMIT 1 + ) AS "subquery" + WHERE "id" = "subquery"."initiative_id"; + -- write (final) ranks: + "rank_v" := 1; + FOR "initiative_id_v" IN + SELECT "id" + FROM "initiative" + WHERE "issue_id" = "issue_id_p" AND "admitted" + ORDER BY + "winner" DESC, + ("direct_majority" AND "indirect_majority") DESC, + "schulze_rank", + "id" + LOOP + UPDATE "initiative" SET "rank" = "rank_v" + WHERE "id" = "initiative_id_v"; + "rank_v" := "rank_v" + 1; + END LOOP; + -- set schulze rank of status quo and mark issue as finished: + UPDATE "issue" SET + "status_quo_schulze_rank" = "rank_ary"["dimension_v"], + "state" = + CASE WHEN EXISTS ( + SELECT NULL FROM "initiative" + WHERE "issue_id" = "issue_id_p" AND "winner" + ) THEN + 'finished_with_winner'::"issue_state" + ELSE + 'finished_without_winner'::"issue_state" + END, + "ranks_available" = TRUE + WHERE "id" = "issue_id_p"; + RETURN; + END; + $$; + +CREATE OR REPLACE FUNCTION "check_issue" + ( "issue_id_p" "issue"."id"%TYPE ) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "issue_row" "issue"%ROWTYPE; + "policy_row" "policy"%ROWTYPE; + BEGIN + PERFORM "lock_issue"("issue_id_p"); + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; + -- only process open issues: + IF "issue_row"."closed" ISNULL THEN + SELECT * INTO "policy_row" FROM "policy" + WHERE "id" = "issue_row"."policy_id"; + -- create a snapshot, unless issue is already fully frozen: + IF "issue_row"."fully_frozen" ISNULL THEN + PERFORM "create_snapshot"("issue_id_p"); + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; + END IF; + -- eventually close or accept issues, which have not been accepted: + IF "issue_row"."accepted" ISNULL THEN + IF EXISTS ( + SELECT NULL FROM "initiative" + WHERE "issue_id" = "issue_id_p" + AND "supporter_count" > 0 + AND "supporter_count" * "policy_row"."issue_quorum_den" + >= "issue_row"."population" * "policy_row"."issue_quorum_num" + ) THEN + -- accept issues, if supporter count is high enough + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); + -- NOTE: "issue_row" used later + "issue_row"."state" := 'discussion'; + "issue_row"."accepted" := now(); + UPDATE "issue" SET + "state" = "issue_row"."state", + "accepted" = "issue_row"."accepted" + WHERE "id" = "issue_row"."id"; + ELSIF + now() >= "issue_row"."created" + "issue_row"."admission_time" + THEN + -- close issues, if admission time has expired + PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission'); + UPDATE "issue" SET + "state" = 'canceled_issue_not_accepted', + "closed" = now() + WHERE "id" = "issue_row"."id"; + END IF; + END IF; + -- eventually half freeze issues: + IF + -- NOTE: issue can't be closed at this point, if it has been accepted + "issue_row"."accepted" NOTNULL AND + "issue_row"."half_frozen" ISNULL + THEN + IF + now() >= "issue_row"."accepted" + "issue_row"."discussion_time" + THEN + PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze'); + -- NOTE: "issue_row" used later + "issue_row"."state" := 'verification'; + "issue_row"."half_frozen" := now(); + UPDATE "issue" SET + "state" = "issue_row"."state", + "half_frozen" = "issue_row"."half_frozen" + WHERE "id" = "issue_row"."id"; + END IF; + END IF; + -- close issues after some time, if all initiatives have been revoked: + IF + "issue_row"."closed" ISNULL AND + NOT EXISTS ( + -- all initiatives are revoked + SELECT NULL FROM "initiative" + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL + ) AND ( + -- and issue has not been accepted yet + "issue_row"."accepted" ISNULL OR + NOT EXISTS ( + -- or no initiatives have been revoked lately + SELECT NULL FROM "initiative" + WHERE "issue_id" = "issue_id_p" + AND now() < "revoked" + "issue_row"."verification_time" + ) OR ( + -- or verification time has elapsed + "issue_row"."half_frozen" NOTNULL AND + "issue_row"."fully_frozen" ISNULL AND + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" + ) + ) + THEN + -- NOTE: "issue_row" used later + IF "issue_row"."accepted" ISNULL THEN + "issue_row"."state" := 'canceled_revoked_before_accepted'; + ELSIF "issue_row"."half_frozen" ISNULL THEN + "issue_row"."state" := 'canceled_after_revocation_during_discussion'; + ELSE + "issue_row"."state" := 'canceled_after_revocation_during_verification'; + END IF; + "issue_row"."closed" := now(); + UPDATE "issue" SET + "state" = "issue_row"."state", + "closed" = "issue_row"."closed" + WHERE "id" = "issue_row"."id"; + END IF; + -- fully freeze issue after verification time: + IF + "issue_row"."half_frozen" NOTNULL AND + "issue_row"."fully_frozen" ISNULL AND + "issue_row"."closed" ISNULL AND + now() >= "issue_row"."half_frozen" + "issue_row"."verification_time" + THEN + PERFORM "freeze_after_snapshot"("issue_id_p"); + -- NOTE: "issue" might change, thus "issue_row" has to be updated below + END IF; + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; + -- close issue by calling close_voting(...) after voting time: + IF + "issue_row"."closed" ISNULL AND + "issue_row"."fully_frozen" NOTNULL AND + now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time" + THEN + PERFORM "close_voting"("issue_id_p"); + -- calculate ranks will not consume much time and can be done now + PERFORM "calculate_ranks"("issue_id_p"); + END IF; + END IF; + RETURN; + END; + $$; + +-- TODO: recalculate voting results + +COMMIT; + +UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id" + FROM ( + SELECT DISTINCT ON ("suggestion"."id") + "suggestion"."id" AS "suggestion_id", + "draft"."id" AS "draft_id" + FROM "suggestion" JOIN "draft" + ON "suggestion"."initiative_id" = "draft"."initiative_id" + WHERE "draft"."created" <= "suggestion"."created" + ORDER BY "suggestion"."id", "draft"."created" DESC + ) AS "subquery" + WHERE "suggestion"."id" = "subquery"."suggestion_id"; + +ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;