
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@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;




