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@193: SELECT * FROM (VALUES ('2.0.0', 2, 0, 0)) 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@184: ALTER TABLE "member" ADD COLUMN "last_activity" DATE; jbe@184: ALTER TABLE "member" DROP COLUMN "last_login_public"; jbe@182: ALTER TABLE "member" ALTER COLUMN "active" SET DEFAULT FALSE; jbe@177: ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT; jbe@182: jbe@246: -- Backported fix of future version to include unused invite codes in member table: jbe@246: ALTER TABLE "member" ALTER COLUMN "name" DROP NOT NULL; jbe@246: 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@186: COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes'; jbe@184: COMMENT ON COLUMN "member"."activated" IS 'Timestamp of activation of account (i.e. usage of "invite_code"); required to be set for "active" members'; jbe@184: COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members'; jbe@184: COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. Automatically set to FALSE, if "last_activity" is older than "system_setting"."member_ttl".'; 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@185: CREATE TYPE "application_access_level" AS ENUM jbe@185: ('member', 'full', 'pseudonymous', 'anonymous'); jbe@185: jbe@185: COMMENT ON TYPE "application_access_level" IS 'Access privileges for applications using the API'; jbe@185: jbe@185: CREATE TABLE "member_application" ( jbe@185: "id" SERIAL8 PRIMARY KEY, jbe@185: UNIQUE ("member_id", "name"), jbe@185: "member_id" INT4 NOT NULL REFERENCES "member" ("id") jbe@185: ON DELETE CASCADE ON UPDATE CASCADE, jbe@185: "name" TEXT NOT NULL, jbe@185: "comment" TEXT, jbe@185: "access_level" "application_access_level" NOT NULL, jbe@190: "key" TEXT NOT NULL UNIQUE, jbe@185: "last_usage" TIMESTAMPTZ ); jbe@185: jbe@185: COMMENT ON TABLE "member_application" IS 'Registered application being allowed to use the API'; jbe@185: 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@183: DROP VIEW "expired_session"; jbe@183: DROP TABLE "session"; jbe@183: 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@249: ALTER TABLE "battle" ALTER COLUMN "winning_initiative_id" DROP NOT NULL; jbe@249: ALTER TABLE "battle" ALTER COLUMN "losing_initiative_id" DROP 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: 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@183: DROP TABLE "invite_code_unit"; jbe@183: 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@184: DROP FUNCTION "check_last_login"(); jbe@184: jbe@184: CREATE FUNCTION "check_activity"() jbe@184: RETURNS VOID jbe@184: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@184: DECLARE jbe@184: "system_setting_row" "system_setting"%ROWTYPE; jbe@184: BEGIN jbe@184: SELECT * INTO "system_setting_row" FROM "system_setting"; jbe@184: LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE; jbe@184: IF "system_setting_row"."member_ttl" NOTNULL THEN jbe@184: UPDATE "member" SET "active" = FALSE jbe@184: WHERE "active" = TRUE jbe@184: AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE; jbe@184: END IF; jbe@184: RETURN; jbe@184: END; jbe@184: $$; jbe@184: jbe@184: COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".'; jbe@184: jbe@189: CREATE OR REPLACE FUNCTION "calculate_member_counts"() jbe@189: RETURNS VOID jbe@189: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@189: BEGIN jbe@189: LOCK TABLE "member" IN SHARE MODE; jbe@189: LOCK TABLE "member_count" IN EXCLUSIVE MODE; jbe@189: LOCK TABLE "unit" IN EXCLUSIVE MODE; jbe@189: LOCK TABLE "area" IN EXCLUSIVE MODE; jbe@189: LOCK TABLE "privilege" IN SHARE MODE; jbe@189: LOCK TABLE "membership" IN SHARE MODE; jbe@189: DELETE FROM "member_count"; jbe@189: INSERT INTO "member_count" ("total_count") jbe@189: SELECT "total_count" FROM "member_count_view"; jbe@189: UPDATE "unit" SET "member_count" = "view"."member_count" jbe@189: FROM "unit_member_count" AS "view" jbe@189: WHERE "view"."unit_id" = "unit"."id"; jbe@189: UPDATE "area" SET jbe@189: "direct_member_count" = "view"."direct_member_count", jbe@189: "member_weight" = "view"."member_weight" jbe@189: FROM "area_member_count" AS "view" jbe@189: WHERE "view"."area_id" = "area"."id"; jbe@189: RETURN; jbe@189: END; jbe@189: $$; jbe@189: 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@183: CREATE OR REPLACE FUNCTION "check_everything"() jbe@183: RETURNS VOID jbe@183: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@183: DECLARE jbe@183: "issue_id_v" "issue"."id"%TYPE; jbe@183: BEGIN jbe@184: PERFORM "check_activity"(); jbe@183: PERFORM "calculate_member_counts"(); jbe@183: FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP jbe@183: PERFORM "check_issue"("issue_id_v"); jbe@183: END LOOP; jbe@183: FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP jbe@183: PERFORM "calculate_ranks"("issue_id_v"); jbe@183: END LOOP; jbe@183: RETURN; jbe@183: END; jbe@183: $$; jbe@183: jbe@183: CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) jbe@183: RETURNS VOID jbe@183: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@183: BEGIN jbe@183: UPDATE "member" SET jbe@183: "last_login" = NULL, jbe@183: "login" = NULL, jbe@183: "password" = NULL, jbe@183: "locked" = TRUE, jbe@183: "active" = FALSE, jbe@183: "notify_email" = NULL, jbe@183: "notify_email_unconfirmed" = NULL, jbe@183: "notify_email_secret" = NULL, jbe@183: "notify_email_secret_expiry" = NULL, jbe@183: "notify_email_lock_expiry" = NULL, jbe@183: "password_reset_secret" = NULL, jbe@183: "password_reset_secret_expiry" = NULL, jbe@183: "organizational_unit" = NULL, jbe@183: "internal_posts" = NULL, jbe@183: "realname" = NULL, jbe@183: "birthday" = NULL, jbe@183: "address" = NULL, jbe@183: "email" = NULL, jbe@183: "xmpp_address" = NULL, jbe@183: "website" = NULL, jbe@183: "phone" = NULL, jbe@183: "mobile_phone" = NULL, jbe@183: "profession" = NULL, jbe@183: "external_memberships" = NULL, jbe@183: "external_posts" = NULL, jbe@183: "statement" = NULL jbe@183: WHERE "id" = "member_id_p"; jbe@183: -- "text_search_data" is updated by triggers jbe@183: DELETE FROM "setting" WHERE "member_id" = "member_id_p"; jbe@183: DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; jbe@183: DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; jbe@183: DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; jbe@183: DELETE FROM "contact" WHERE "member_id" = "member_id_p"; jbe@183: DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; jbe@183: DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; jbe@183: DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; jbe@183: DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; jbe@183: DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; jbe@183: DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; jbe@183: DELETE FROM "membership" WHERE "member_id" = "member_id_p"; jbe@183: DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; jbe@183: DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; jbe@183: DELETE FROM "direct_voter" USING "issue" jbe@183: WHERE "direct_voter"."issue_id" = "issue"."id" jbe@183: AND "issue"."closed" ISNULL jbe@183: AND "member_id" = "member_id_p"; jbe@183: RETURN; jbe@183: END; jbe@183: $$; jbe@183: jbe@183: CREATE OR REPLACE FUNCTION "delete_private_data"() jbe@183: RETURNS VOID jbe@183: LANGUAGE 'plpgsql' VOLATILE AS $$ jbe@183: BEGIN jbe@183: UPDATE "member" SET jbe@183: "last_login" = NULL, jbe@183: "login" = NULL, jbe@183: "password" = NULL, jbe@183: "notify_email" = NULL, jbe@183: "notify_email_unconfirmed" = NULL, jbe@183: "notify_email_secret" = NULL, jbe@183: "notify_email_secret_expiry" = NULL, jbe@183: "notify_email_lock_expiry" = NULL, jbe@183: "password_reset_secret" = NULL, jbe@183: "password_reset_secret_expiry" = NULL, jbe@183: "organizational_unit" = NULL, jbe@183: "internal_posts" = NULL, jbe@183: "realname" = NULL, jbe@183: "birthday" = NULL, jbe@183: "address" = NULL, jbe@183: "email" = NULL, jbe@183: "xmpp_address" = NULL, jbe@183: "website" = NULL, jbe@183: "phone" = NULL, jbe@183: "mobile_phone" = NULL, jbe@183: "profession" = NULL, jbe@183: "external_memberships" = NULL, jbe@183: "external_posts" = NULL, jbe@183: "statement" = NULL; jbe@183: -- "text_search_data" is updated by triggers jbe@183: DELETE FROM "invite_code"; jbe@183: DELETE FROM "setting"; jbe@183: DELETE FROM "setting_map"; jbe@183: DELETE FROM "member_relation_setting"; jbe@183: DELETE FROM "member_image"; jbe@183: DELETE FROM "contact"; jbe@183: DELETE FROM "ignored_member"; jbe@183: DELETE FROM "area_setting"; jbe@183: DELETE FROM "issue_setting"; jbe@183: DELETE FROM "ignored_initiative"; jbe@183: DELETE FROM "initiative_setting"; jbe@183: DELETE FROM "suggestion_setting"; jbe@183: DELETE FROM "non_voter"; jbe@183: DELETE FROM "direct_voter" USING "issue" jbe@183: WHERE "direct_voter"."issue_id" = "issue"."id" jbe@183: AND "issue"."closed" ISNULL; jbe@183: RETURN; jbe@183: END; jbe@183: $$; jbe@183: jbe@178: COMMIT; jbe@178: jbe@178: BEGIN; jbe@178: jbe@184: UPDATE "member" SET jbe@184: "activated" = "created", jbe@184: "last_activity" = CASE WHEN "active" THEN jbe@184: coalesce("last_login"::DATE, now()) jbe@184: ELSE jbe@184: "last_login"::DATE jbe@184: END; 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@245: INSERT INTO "member" ("created", "invite_code", "admin_comment") jbe@245: SELECT "created", "code", "comment" jbe@245: FROM "invite_code" WHERE "member_id" ISNULL; jbe@245: jbe@245: INSERT INTO "privilege" ("unit_id", "member_id", "voting_right") jbe@245: SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right" jbe@245: FROM "member" WHERE "activated" ISNULL; jbe@245: 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@186: COALESCE(max("initiative"."rank") + 1, 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@184: ALTER TABLE "member" ADD CONSTRAINT "active_requires_activated_and_last_activity" jbe@184: CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)); jbe@177: ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;