1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v1.4.0_rc4-v1.5.0_rc1.sql	Sat Jul 30 01:57:27 2011 +0200
     1.3 @@ -0,0 +1,1305 @@
     1.4 +SELECT "calculate_ranks"("id") FROM "issue_with_ranks_missing";
     1.5 +
     1.6 +BEGIN;
     1.7 +
     1.8 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.9 +  SELECT * FROM (VALUES ('1.5.0_rc1', 1, 5, -1))
    1.10 +  AS "subquery"("string", "major", "minor", "revision");
    1.11 +
    1.12 +ALTER TABLE "member" ADD COLUMN "invite_code" TEXT UNIQUE;
    1.13 +ALTER TABLE "member" ADD COLUMN "admin_comment" TEXT;
    1.14 +ALTER TABLE "member" ADD COLUMN "activated" TIMESTAMPTZ;
    1.15 +ALTER TABLE "member" ADD COLUMN "last_activity" DATE;
    1.16 +ALTER TABLE "member" DROP COLUMN "last_login_public";
    1.17 +ALTER TABLE "member" ALTER COLUMN "active" SET DEFAULT FALSE;
    1.18 +ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT;
    1.19 +
    1.20 +COMMENT ON COLUMN "member"."created"           IS 'Creation of member record and/or invite code';
    1.21 +COMMENT ON COLUMN "member"."invite_code"       IS 'Optional invite code, to allow a member to initialize his/her account the first time';
    1.22 +COMMENT ON COLUMN "member"."admin_comment"     IS 'Hidden comment for administrative purposes';
    1.23 +COMMENT ON COLUMN "member"."activated"         IS 'Timestamp of activation of account (i.e. usage of "invite_code"); required to be set for "active" members';
    1.24 +COMMENT ON COLUMN "member"."last_activity"     IS 'Date of last activity of member; required to be set for "active" members';
    1.25 +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".';
    1.26 +COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
    1.27 +
    1.28 +CREATE TYPE "application_access_level" AS ENUM
    1.29 +  ('member', 'full', 'pseudonymous', 'anonymous');
    1.30 +
    1.31 +COMMENT ON TYPE "application_access_level" IS 'Access privileges for applications using the API';
    1.32 +
    1.33 +CREATE TABLE "member_application" (
    1.34 +        "id"                    SERIAL8         PRIMARY KEY,
    1.35 +        UNIQUE ("member_id", "name"),
    1.36 +        "member_id"             INT4            NOT NULL REFERENCES "member" ("id")
    1.37 +                                                ON DELETE CASCADE ON UPDATE CASCADE,
    1.38 +        "name"                  TEXT            NOT NULL,
    1.39 +        "comment"               TEXT,
    1.40 +        "access_level" "application_access_level" NOT NULL,
    1.41 +        "key"                   TEXT            NOT NULL,
    1.42 +        "last_usage"            TIMESTAMPTZ );
    1.43 +
    1.44 +COMMENT ON TABLE "member_application" IS 'Registered application being allowed to use the API';
    1.45 +
    1.46 +CREATE TABLE "rendered_member_statement" (
    1.47 +        PRIMARY KEY ("member_id", "format"),
    1.48 +        "member_id"             INT8            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.49 +        "format"                TEXT,
    1.50 +        "content"               TEXT            NOT NULL );
    1.51 +
    1.52 +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)';
    1.53 +
    1.54 +DROP VIEW "expired_session";
    1.55 +DROP TABLE "session";
    1.56 +
    1.57 +ALTER TABLE "policy" ADD COLUMN "direct_majority_num"            INT4    NOT NULL DEFAULT 1;
    1.58 +ALTER TABLE "policy" ADD COLUMN "direct_majority_den"            INT4    NOT NULL DEFAULT 2;
    1.59 +ALTER TABLE "policy" ADD COLUMN "direct_majority_strict"         BOOLEAN NOT NULL DEFAULT TRUE;
    1.60 +ALTER TABLE "policy" ADD COLUMN "direct_majority_positive"       INT4    NOT NULL DEFAULT 0;
    1.61 +ALTER TABLE "policy" ADD COLUMN "direct_majority_non_negative"   INT4    NOT NULL DEFAULT 0;
    1.62 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_num"          INT4    NOT NULL DEFAULT 1;
    1.63 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_den"          INT4    NOT NULL DEFAULT 2;
    1.64 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_strict"       BOOLEAN NOT NULL DEFAULT TRUE;
    1.65 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_positive"     INT4    NOT NULL DEFAULT 0;
    1.66 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_non_negative" INT4    NOT NULL DEFAULT 0;
    1.67 +ALTER TABLE "policy" ADD COLUMN "no_reverse_beat_path"           BOOLEAN NOT NULL DEFAULT TRUE;
    1.68 +ALTER TABLE "policy" ADD COLUMN "no_multistage_majority"         BOOLEAN NOT NULL DEFAULT FALSE;
    1.69 +
    1.70 +UPDATE "policy" SET
    1.71 +  "direct_majority_num"      = "majority_num",
    1.72 +  "direct_majority_den"      = "majority_den",
    1.73 +  "direct_majority_strict"   = "majority_strict",
    1.74 +  "indirect_majority_num"    = "majority_num",
    1.75 +  "indirect_majority_den"    = "majority_den",
    1.76 +  "indirect_majority_strict" = "majority_strict";
    1.77 +
    1.78 +ALTER TABLE "policy" DROP COLUMN "majority_num";
    1.79 +ALTER TABLE "policy" DROP COLUMN "majority_den";
    1.80 +ALTER TABLE "policy" DROP COLUMN "majority_strict";
    1.81 +
    1.82 +COMMENT ON COLUMN "policy"."direct_majority_num"            IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
    1.83 +COMMENT ON COLUMN "policy"."direct_majority_den"            IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
    1.84 +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.';
    1.85 +COMMENT ON COLUMN "policy"."direct_majority_positive"       IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
    1.86 +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';
    1.87 +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';
    1.88 +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';
    1.89 +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.';
    1.90 +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';
    1.91 +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';
    1.92 +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.';
    1.93 +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").';
    1.94 +
    1.95 +ALTER TABLE "area" DROP COLUMN "autoreject_weight";
    1.96 +
    1.97 +DROP VIEW "open_issue";
    1.98 +DROP VIEW "issue_with_ranks_missing";
    1.99 +
   1.100 +ALTER TABLE "issue" DROP COLUMN "vote_now";
   1.101 +ALTER TABLE "issue" DROP COLUMN "vote_later";
   1.102 +ALTER TABLE "issue" ADD COLUMN "status_quo_schulze_rank" INT4;
   1.103 +
   1.104 +CREATE VIEW "open_issue" AS
   1.105 +  SELECT * FROM "issue" WHERE "closed" ISNULL;
   1.106 +
   1.107 +COMMENT ON VIEW "open_issue" IS 'All open issues';
   1.108 +
   1.109 +CREATE VIEW "issue_with_ranks_missing" AS
   1.110 +  SELECT * FROM "issue"
   1.111 +  WHERE "fully_frozen" NOTNULL
   1.112 +  AND "closed" NOTNULL
   1.113 +  AND "ranks_available" = FALSE;
   1.114 +
   1.115 +COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
   1.116 +
   1.117 +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.';
   1.118 +COMMENT ON COLUMN "issue"."snapshot"                IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
   1.119 +COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
   1.120 +
   1.121 +DROP VIEW "battle_view";
   1.122 +
   1.123 +ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
   1.124 +ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null";
   1.125 +ALTER TABLE "initiative" DROP CONSTRAINT "non_agreed_initiatives_cant_get_a_rank";
   1.126 +
   1.127 +ALTER TABLE "initiative" DROP COLUMN "agreed";
   1.128 +ALTER TABLE "initiative" ADD COLUMN "direct_majority"        BOOLEAN;
   1.129 +ALTER TABLE "initiative" ADD COLUMN "indirect_majority"      BOOLEAN;
   1.130 +ALTER TABLE "initiative" ADD COLUMN "schulze_rank"           INT4;
   1.131 +ALTER TABLE "initiative" ADD COLUMN "better_than_status_quo" BOOLEAN;
   1.132 +ALTER TABLE "initiative" ADD COLUMN "worse_than_status_quo"  BOOLEAN;
   1.133 +ALTER TABLE "initiative" ADD COLUMN "reverse_beat_path"      BOOLEAN;
   1.134 +ALTER TABLE "initiative" ADD COLUMN "multistage_majority"    BOOLEAN;
   1.135 +ALTER TABLE "initiative" ADD COLUMN "eligible"               BOOLEAN;
   1.136 +ALTER TABLE "initiative" ADD COLUMN "winner"                 BOOLEAN;
   1.137 +
   1.138 +ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
   1.139 +  ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
   1.140 +  ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
   1.141 +    "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
   1.142 +    "schulze_rank" ISNULL AND
   1.143 +    "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
   1.144 +    "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
   1.145 +    "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
   1.146 +ALTER TABLE "initiative" ADD CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo"));
   1.147 +ALTER TABLE "initiative" ADD CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
   1.148 +  "eligible" = FALSE OR
   1.149 +("direct_majority" AND "indirect_majority" AND "better_than_status_quo") );
   1.150 +ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE);
   1.151 +ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1);
   1.152 +ALTER TABLE "initiative" ADD CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE);
   1.153 +ALTER TABLE "initiative" ADD CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank");
   1.154 +
   1.155 +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"';
   1.156 +COMMENT ON COLUMN "initiative"."indirect_majority"       IS 'Same as "direct_majority", but also considering indirect beat paths';
   1.157 +COMMENT ON COLUMN "initiative"."schulze_rank"            IS 'Schulze-Ranking without tie-breaking';
   1.158 +COMMENT ON COLUMN "initiative"."better_than_status_quo"  IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
   1.159 +COMMENT ON COLUMN "initiative"."worse_than_status_quo"   IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
   1.160 +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';
   1.161 +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';
   1.162 +COMMENT ON COLUMN "initiative"."eligible"                IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"';
   1.163 +COMMENT ON COLUMN "initiative"."winner"                  IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
   1.164 +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';
   1.165 +
   1.166 +ALTER TABLE "battle" DROP CONSTRAINT "battle_pkey";
   1.167 +ALTER TABLE "battle" ALTER COLUMN "issue_id" SET NOT NULL;
   1.168 +ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK (
   1.169 +  "winning_initiative_id" != "losing_initiative_id" OR
   1.170 +  ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
   1.171 +    ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) );
   1.172 +
   1.173 +CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
   1.174 +CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
   1.175 +CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
   1.176 +
   1.177 +ALTER TABLE "suggestion" ADD COLUMN "draft_id" INT8;
   1.178 +ALTER TABLE "suggestion" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;
   1.179 +ALTER TABLE "suggestion" ADD COLUMN "formatting_engine" TEXT;
   1.180 +ALTER TABLE "suggestion" RENAME COLUMN "description" TO "content";
   1.181 +
   1.182 +DROP TRIGGER "update_text_search_data" ON "suggestion";
   1.183 +
   1.184 +CREATE TRIGGER "update_text_search_data"
   1.185 +  BEFORE INSERT OR UPDATE ON "suggestion"
   1.186 +  FOR EACH ROW EXECUTE PROCEDURE
   1.187 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
   1.188 +    "name", "content");
   1.189 +
   1.190 +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")';
   1.191 +
   1.192 +CREATE TABLE "rendered_suggestion" (
   1.193 +        PRIMARY KEY ("suggestion_id", "format"),
   1.194 +        "suggestion_id"         INT8            REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   1.195 +        "format"                TEXT,
   1.196 +        "content"               TEXT            NOT NULL );
   1.197 +
   1.198 +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)';
   1.199 +
   1.200 +DROP TABLE "invite_code_unit";
   1.201 +
   1.202 +DROP VIEW "area_member_count";
   1.203 +
   1.204 +ALTER TABLE "membership" DROP COLUMN "autoreject";
   1.205 +
   1.206 +ALTER TABLE "interest" DROP COLUMN "autoreject";
   1.207 +ALTER TABLE "interest" DROP COLUMN "voting_requested";
   1.208 +
   1.209 +ALTER TABLE "supporter" DROP CONSTRAINT "supporter_initiative_id_fkey";
   1.210 +ALTER TABLE "supporter" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;
   1.211 +
   1.212 +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")';
   1.213 +
   1.214 +ALTER TABLE "direct_interest_snapshot" DROP COLUMN "voting_requested";
   1.215 +ALTER TABLE "direct_voter" DROP COLUMN "autoreject";
   1.216 +
   1.217 +DROP TRIGGER "default_for_draft_id" ON "supporter";
   1.218 +DROP FUNCTION "supporter_default_for_draft_id_trigger"();
   1.219 +
   1.220 +CREATE FUNCTION "default_for_draft_id_trigger"()
   1.221 +  RETURNS TRIGGER
   1.222 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.223 +    BEGIN
   1.224 +      IF NEW."draft_id" ISNULL THEN
   1.225 +        SELECT "id" INTO NEW."draft_id" FROM "current_draft"
   1.226 +          WHERE "initiative_id" = NEW."initiative_id";
   1.227 +      END IF;
   1.228 +      RETURN NEW;
   1.229 +    END;
   1.230 +  $$;
   1.231 +
   1.232 +CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
   1.233 +  FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
   1.234 +CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
   1.235 +  FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
   1.236 +
   1.237 +COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
   1.238 +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';
   1.239 +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';
   1.240 +
   1.241 +CREATE VIEW "area_member_count" AS
   1.242 +  SELECT
   1.243 +    "area"."id" AS "area_id",
   1.244 +    count("member"."id") AS "direct_member_count",
   1.245 +    coalesce(
   1.246 +      sum(
   1.247 +        CASE WHEN "member"."id" NOTNULL THEN
   1.248 +          "membership_weight"("area"."id", "member"."id")
   1.249 +        ELSE 0 END
   1.250 +      )
   1.251 +    ) AS "member_weight"
   1.252 +  FROM "area"
   1.253 +  LEFT JOIN "membership"
   1.254 +  ON "area"."id" = "membership"."area_id"
   1.255 +  LEFT JOIN "privilege"
   1.256 +  ON "privilege"."unit_id" = "area"."unit_id"
   1.257 +  AND "privilege"."member_id" = "membership"."member_id"
   1.258 +  AND "privilege"."voting_right"
   1.259 +  LEFT JOIN "member"
   1.260 +  ON "member"."id" = "privilege"."member_id"  -- NOTE: no membership here!
   1.261 +  AND "member"."active"
   1.262 +  GROUP BY "area"."id";
   1.263 +
   1.264 +COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
   1.265 +
   1.266 +CREATE VIEW "battle_participant" AS
   1.267 +    SELECT "initiative"."id", "initiative"."issue_id"
   1.268 +    FROM "issue" JOIN "initiative"
   1.269 +    ON "issue"."id" = "initiative"."issue_id"
   1.270 +    WHERE "initiative"."admitted"
   1.271 +  UNION ALL
   1.272 +    SELECT NULL, "id" AS "issue_id"
   1.273 +    FROM "issue";
   1.274 +
   1.275 +COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
   1.276 +
   1.277 +CREATE VIEW "battle_view" AS
   1.278 +  SELECT
   1.279 +    "issue"."id" AS "issue_id",
   1.280 +    "winning_initiative"."id" AS "winning_initiative_id",
   1.281 +    "losing_initiative"."id" AS "losing_initiative_id",
   1.282 +    sum(
   1.283 +      CASE WHEN
   1.284 +        coalesce("better_vote"."grade", 0) >
   1.285 +        coalesce("worse_vote"."grade", 0)
   1.286 +      THEN "direct_voter"."weight" ELSE 0 END
   1.287 +    ) AS "count"
   1.288 +  FROM "issue"
   1.289 +  LEFT JOIN "direct_voter"
   1.290 +  ON "issue"."id" = "direct_voter"."issue_id"
   1.291 +  JOIN "battle_participant" AS "winning_initiative"
   1.292 +    ON "issue"."id" = "winning_initiative"."issue_id"
   1.293 +  JOIN "battle_participant" AS "losing_initiative"
   1.294 +    ON "issue"."id" = "losing_initiative"."issue_id"
   1.295 +  LEFT JOIN "vote" AS "better_vote"
   1.296 +    ON "direct_voter"."member_id" = "better_vote"."member_id"
   1.297 +    AND "winning_initiative"."id" = "better_vote"."initiative_id"
   1.298 +  LEFT JOIN "vote" AS "worse_vote"
   1.299 +    ON "direct_voter"."member_id" = "worse_vote"."member_id"
   1.300 +    AND "losing_initiative"."id" = "worse_vote"."initiative_id"
   1.301 +  WHERE "issue"."closed" NOTNULL
   1.302 +  AND "issue"."cleaned" ISNULL
   1.303 +  AND (
   1.304 +    "winning_initiative"."id" != "losing_initiative"."id" OR
   1.305 +    ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
   1.306 +      ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
   1.307 +  GROUP BY
   1.308 +    "issue"."id",
   1.309 +    "winning_initiative"."id",
   1.310 +    "losing_initiative"."id";
   1.311 +
   1.312 +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';
   1.313 +
   1.314 +DROP FUNCTION "check_last_login"();
   1.315 +
   1.316 +CREATE FUNCTION "check_activity"()
   1.317 +  RETURNS VOID
   1.318 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.319 +    DECLARE
   1.320 +      "system_setting_row" "system_setting"%ROWTYPE;
   1.321 +    BEGIN
   1.322 +      SELECT * INTO "system_setting_row" FROM "system_setting";
   1.323 +      LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
   1.324 +      IF "system_setting_row"."member_ttl" NOTNULL THEN
   1.325 +        UPDATE "member" SET "active" = FALSE
   1.326 +          WHERE "active" = TRUE
   1.327 +          AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
   1.328 +      END IF;
   1.329 +      RETURN;
   1.330 +    END;
   1.331 +  $$;
   1.332 +
   1.333 +COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
   1.334 +
   1.335 +CREATE OR REPLACE FUNCTION "create_interest_snapshot"
   1.336 +  ( "issue_id_p" "issue"."id"%TYPE )
   1.337 +  RETURNS VOID
   1.338 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.339 +    DECLARE
   1.340 +      "member_id_v" "member"."id"%TYPE;
   1.341 +    BEGIN
   1.342 +      DELETE FROM "direct_interest_snapshot"
   1.343 +        WHERE "issue_id" = "issue_id_p"
   1.344 +        AND "event" = 'periodic';
   1.345 +      DELETE FROM "delegating_interest_snapshot"
   1.346 +        WHERE "issue_id" = "issue_id_p"
   1.347 +        AND "event" = 'periodic';
   1.348 +      DELETE FROM "direct_supporter_snapshot"
   1.349 +        WHERE "issue_id" = "issue_id_p"
   1.350 +        AND "event" = 'periodic';
   1.351 +      INSERT INTO "direct_interest_snapshot"
   1.352 +        ("issue_id", "event", "member_id")
   1.353 +        SELECT
   1.354 +          "issue_id_p"  AS "issue_id",
   1.355 +          'periodic'    AS "event",
   1.356 +          "member"."id" AS "member_id"
   1.357 +        FROM "issue"
   1.358 +        JOIN "area" ON "issue"."area_id" = "area"."id"
   1.359 +        JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   1.360 +        JOIN "member" ON "interest"."member_id" = "member"."id"
   1.361 +        JOIN "privilege"
   1.362 +          ON "privilege"."unit_id" = "area"."unit_id"
   1.363 +          AND "privilege"."member_id" = "member"."id"
   1.364 +        WHERE "issue"."id" = "issue_id_p"
   1.365 +        AND "member"."active" AND "privilege"."voting_right";
   1.366 +      FOR "member_id_v" IN
   1.367 +        SELECT "member_id" FROM "direct_interest_snapshot"
   1.368 +        WHERE "issue_id" = "issue_id_p"
   1.369 +        AND "event" = 'periodic'
   1.370 +      LOOP
   1.371 +        UPDATE "direct_interest_snapshot" SET
   1.372 +          "weight" = 1 +
   1.373 +            "weight_of_added_delegations_for_interest_snapshot"(
   1.374 +              "issue_id_p",
   1.375 +              "member_id_v",
   1.376 +              '{}'
   1.377 +            )
   1.378 +          WHERE "issue_id" = "issue_id_p"
   1.379 +          AND "event" = 'periodic'
   1.380 +          AND "member_id" = "member_id_v";
   1.381 +      END LOOP;
   1.382 +      INSERT INTO "direct_supporter_snapshot"
   1.383 +        ( "issue_id", "initiative_id", "event", "member_id",
   1.384 +          "informed", "satisfied" )
   1.385 +        SELECT
   1.386 +          "issue_id_p"            AS "issue_id",
   1.387 +          "initiative"."id"       AS "initiative_id",
   1.388 +          'periodic'              AS "event",
   1.389 +          "supporter"."member_id" AS "member_id",
   1.390 +          "supporter"."draft_id" = "current_draft"."id" AS "informed",
   1.391 +          NOT EXISTS (
   1.392 +            SELECT NULL FROM "critical_opinion"
   1.393 +            WHERE "initiative_id" = "initiative"."id"
   1.394 +            AND "member_id" = "supporter"."member_id"
   1.395 +          ) AS "satisfied"
   1.396 +        FROM "initiative"
   1.397 +        JOIN "supporter"
   1.398 +        ON "supporter"."initiative_id" = "initiative"."id"
   1.399 +        JOIN "current_draft"
   1.400 +        ON "initiative"."id" = "current_draft"."initiative_id"
   1.401 +        JOIN "direct_interest_snapshot"
   1.402 +        ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
   1.403 +        AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
   1.404 +        AND "event" = 'periodic'
   1.405 +        WHERE "initiative"."issue_id" = "issue_id_p";
   1.406 +      RETURN;
   1.407 +    END;
   1.408 +  $$;
   1.409 +
   1.410 +CREATE OR REPLACE FUNCTION "create_snapshot"
   1.411 +  ( "issue_id_p" "issue"."id"%TYPE )
   1.412 +  RETURNS VOID
   1.413 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.414 +    DECLARE
   1.415 +      "initiative_id_v"    "initiative"."id"%TYPE;
   1.416 +      "suggestion_id_v"    "suggestion"."id"%TYPE;
   1.417 +    BEGIN
   1.418 +      PERFORM "lock_issue"("issue_id_p");
   1.419 +      PERFORM "create_population_snapshot"("issue_id_p");
   1.420 +      PERFORM "create_interest_snapshot"("issue_id_p");
   1.421 +      UPDATE "issue" SET
   1.422 +        "snapshot" = now(),
   1.423 +        "latest_snapshot_event" = 'periodic',
   1.424 +        "population" = (
   1.425 +          SELECT coalesce(sum("weight"), 0)
   1.426 +          FROM "direct_population_snapshot"
   1.427 +          WHERE "issue_id" = "issue_id_p"
   1.428 +          AND "event" = 'periodic'
   1.429 +        )
   1.430 +        WHERE "id" = "issue_id_p";
   1.431 +      FOR "initiative_id_v" IN
   1.432 +        SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
   1.433 +      LOOP
   1.434 +        UPDATE "initiative" SET
   1.435 +          "supporter_count" = (
   1.436 +            SELECT coalesce(sum("di"."weight"), 0)
   1.437 +            FROM "direct_interest_snapshot" AS "di"
   1.438 +            JOIN "direct_supporter_snapshot" AS "ds"
   1.439 +            ON "di"."member_id" = "ds"."member_id"
   1.440 +            WHERE "di"."issue_id" = "issue_id_p"
   1.441 +            AND "di"."event" = 'periodic'
   1.442 +            AND "ds"."initiative_id" = "initiative_id_v"
   1.443 +            AND "ds"."event" = 'periodic'
   1.444 +          ),
   1.445 +          "informed_supporter_count" = (
   1.446 +            SELECT coalesce(sum("di"."weight"), 0)
   1.447 +            FROM "direct_interest_snapshot" AS "di"
   1.448 +            JOIN "direct_supporter_snapshot" AS "ds"
   1.449 +            ON "di"."member_id" = "ds"."member_id"
   1.450 +            WHERE "di"."issue_id" = "issue_id_p"
   1.451 +            AND "di"."event" = 'periodic'
   1.452 +            AND "ds"."initiative_id" = "initiative_id_v"
   1.453 +            AND "ds"."event" = 'periodic'
   1.454 +            AND "ds"."informed"
   1.455 +          ),
   1.456 +          "satisfied_supporter_count" = (
   1.457 +            SELECT coalesce(sum("di"."weight"), 0)
   1.458 +            FROM "direct_interest_snapshot" AS "di"
   1.459 +            JOIN "direct_supporter_snapshot" AS "ds"
   1.460 +            ON "di"."member_id" = "ds"."member_id"
   1.461 +            WHERE "di"."issue_id" = "issue_id_p"
   1.462 +            AND "di"."event" = 'periodic'
   1.463 +            AND "ds"."initiative_id" = "initiative_id_v"
   1.464 +            AND "ds"."event" = 'periodic'
   1.465 +            AND "ds"."satisfied"
   1.466 +          ),
   1.467 +          "satisfied_informed_supporter_count" = (
   1.468 +            SELECT coalesce(sum("di"."weight"), 0)
   1.469 +            FROM "direct_interest_snapshot" AS "di"
   1.470 +            JOIN "direct_supporter_snapshot" AS "ds"
   1.471 +            ON "di"."member_id" = "ds"."member_id"
   1.472 +            WHERE "di"."issue_id" = "issue_id_p"
   1.473 +            AND "di"."event" = 'periodic'
   1.474 +            AND "ds"."initiative_id" = "initiative_id_v"
   1.475 +            AND "ds"."event" = 'periodic'
   1.476 +            AND "ds"."informed"
   1.477 +            AND "ds"."satisfied"
   1.478 +          )
   1.479 +          WHERE "id" = "initiative_id_v";
   1.480 +        FOR "suggestion_id_v" IN
   1.481 +          SELECT "id" FROM "suggestion"
   1.482 +          WHERE "initiative_id" = "initiative_id_v"
   1.483 +        LOOP
   1.484 +          UPDATE "suggestion" SET
   1.485 +            "minus2_unfulfilled_count" = (
   1.486 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.487 +              FROM "issue" CROSS JOIN "opinion"
   1.488 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.489 +              ON "snapshot"."issue_id" = "issue"."id"
   1.490 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.491 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.492 +              WHERE "issue"."id" = "issue_id_p"
   1.493 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.494 +              AND "opinion"."degree" = -2
   1.495 +              AND "opinion"."fulfilled" = FALSE
   1.496 +            ),
   1.497 +            "minus2_fulfilled_count" = (
   1.498 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.499 +              FROM "issue" CROSS JOIN "opinion"
   1.500 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.501 +              ON "snapshot"."issue_id" = "issue"."id"
   1.502 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.503 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.504 +              WHERE "issue"."id" = "issue_id_p"
   1.505 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.506 +              AND "opinion"."degree" = -2
   1.507 +              AND "opinion"."fulfilled" = TRUE
   1.508 +            ),
   1.509 +            "minus1_unfulfilled_count" = (
   1.510 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.511 +              FROM "issue" CROSS JOIN "opinion"
   1.512 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.513 +              ON "snapshot"."issue_id" = "issue"."id"
   1.514 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.515 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.516 +              WHERE "issue"."id" = "issue_id_p"
   1.517 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.518 +              AND "opinion"."degree" = -1
   1.519 +              AND "opinion"."fulfilled" = FALSE
   1.520 +            ),
   1.521 +            "minus1_fulfilled_count" = (
   1.522 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.523 +              FROM "issue" CROSS JOIN "opinion"
   1.524 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.525 +              ON "snapshot"."issue_id" = "issue"."id"
   1.526 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.527 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.528 +              WHERE "issue"."id" = "issue_id_p"
   1.529 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.530 +              AND "opinion"."degree" = -1
   1.531 +              AND "opinion"."fulfilled" = TRUE
   1.532 +            ),
   1.533 +            "plus1_unfulfilled_count" = (
   1.534 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.535 +              FROM "issue" CROSS JOIN "opinion"
   1.536 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.537 +              ON "snapshot"."issue_id" = "issue"."id"
   1.538 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.539 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.540 +              WHERE "issue"."id" = "issue_id_p"
   1.541 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.542 +              AND "opinion"."degree" = 1
   1.543 +              AND "opinion"."fulfilled" = FALSE
   1.544 +            ),
   1.545 +            "plus1_fulfilled_count" = (
   1.546 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.547 +              FROM "issue" CROSS JOIN "opinion"
   1.548 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.549 +              ON "snapshot"."issue_id" = "issue"."id"
   1.550 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.551 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.552 +              WHERE "issue"."id" = "issue_id_p"
   1.553 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.554 +              AND "opinion"."degree" = 1
   1.555 +              AND "opinion"."fulfilled" = TRUE
   1.556 +            ),
   1.557 +            "plus2_unfulfilled_count" = (
   1.558 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.559 +              FROM "issue" CROSS JOIN "opinion"
   1.560 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.561 +              ON "snapshot"."issue_id" = "issue"."id"
   1.562 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.563 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.564 +              WHERE "issue"."id" = "issue_id_p"
   1.565 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.566 +              AND "opinion"."degree" = 2
   1.567 +              AND "opinion"."fulfilled" = FALSE
   1.568 +            ),
   1.569 +            "plus2_fulfilled_count" = (
   1.570 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.571 +              FROM "issue" CROSS JOIN "opinion"
   1.572 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.573 +              ON "snapshot"."issue_id" = "issue"."id"
   1.574 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.575 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.576 +              WHERE "issue"."id" = "issue_id_p"
   1.577 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.578 +              AND "opinion"."degree" = 2
   1.579 +              AND "opinion"."fulfilled" = TRUE
   1.580 +            )
   1.581 +            WHERE "suggestion"."id" = "suggestion_id_v";
   1.582 +        END LOOP;
   1.583 +      END LOOP;
   1.584 +      RETURN;
   1.585 +    END;
   1.586 +  $$;
   1.587 +
   1.588 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
   1.589 +  RETURNS VOID
   1.590 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.591 +    DECLARE
   1.592 +      "area_id_v"   "area"."id"%TYPE;
   1.593 +      "unit_id_v"   "unit"."id"%TYPE;
   1.594 +      "member_id_v" "member"."id"%TYPE;
   1.595 +    BEGIN
   1.596 +      PERFORM "lock_issue"("issue_id_p");
   1.597 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   1.598 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   1.599 +      -- delete delegating votes (in cases of manual reset of issue state):
   1.600 +      DELETE FROM "delegating_voter"
   1.601 +        WHERE "issue_id" = "issue_id_p";
   1.602 +      -- delete votes from non-privileged voters:
   1.603 +      DELETE FROM "direct_voter"
   1.604 +        USING (
   1.605 +          SELECT
   1.606 +            "direct_voter"."member_id"
   1.607 +          FROM "direct_voter"
   1.608 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   1.609 +          LEFT JOIN "privilege"
   1.610 +          ON "privilege"."unit_id" = "unit_id_v"
   1.611 +          AND "privilege"."member_id" = "direct_voter"."member_id"
   1.612 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   1.613 +            "member"."active" = FALSE OR
   1.614 +            "privilege"."voting_right" ISNULL OR
   1.615 +            "privilege"."voting_right" = FALSE
   1.616 +          )
   1.617 +        ) AS "subquery"
   1.618 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
   1.619 +        AND "direct_voter"."member_id" = "subquery"."member_id";
   1.620 +      -- consider delegations:
   1.621 +      UPDATE "direct_voter" SET "weight" = 1
   1.622 +        WHERE "issue_id" = "issue_id_p";
   1.623 +      PERFORM "add_vote_delegations"("issue_id_p");
   1.624 +      -- set voter count and mark issue as being calculated:
   1.625 +      UPDATE "issue" SET
   1.626 +        "state"  = 'calculation',
   1.627 +        "closed" = now(),
   1.628 +        "voter_count" = (
   1.629 +          SELECT coalesce(sum("weight"), 0)
   1.630 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   1.631 +        )
   1.632 +        WHERE "id" = "issue_id_p";
   1.633 +      -- materialize battle_view:
   1.634 +      -- NOTE: "closed" column of issue must be set at this point
   1.635 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   1.636 +      INSERT INTO "battle" (
   1.637 +        "issue_id",
   1.638 +        "winning_initiative_id", "losing_initiative_id",
   1.639 +        "count"
   1.640 +      ) SELECT
   1.641 +        "issue_id",
   1.642 +        "winning_initiative_id", "losing_initiative_id",
   1.643 +        "count"
   1.644 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   1.645 +      -- copy "positive_votes" and "negative_votes" from "battle" table:
   1.646 +      UPDATE "initiative" SET
   1.647 +        "positive_votes" = "battle_win"."count",
   1.648 +        "negative_votes" = "battle_lose"."count"
   1.649 +        FROM "battle" AS "battle_win", "battle" AS "battle_lose"
   1.650 +        WHERE
   1.651 +          "battle_win"."issue_id" = "issue_id_p" AND
   1.652 +          "battle_win"."winning_initiative_id" = "initiative"."id" AND
   1.653 +          "battle_win"."losing_initiative_id" ISNULL AND
   1.654 +          "battle_lose"."issue_id" = "issue_id_p" AND
   1.655 +          "battle_lose"."losing_initiative_id" = "initiative"."id" AND
   1.656 +          "battle_lose"."winning_initiative_id" ISNULL;
   1.657 +    END;
   1.658 +  $$;
   1.659 +
   1.660 +DROP FUNCTION "array_init_string"(INTEGER);
   1.661 +DROP FUNCTION "square_matrix_init_string"(INTEGER);
   1.662 +
   1.663 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
   1.664 +  RETURNS VOID
   1.665 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.666 +    DECLARE
   1.667 +      "issue_row"         "issue"%ROWTYPE;
   1.668 +      "policy_row"        "policy"%ROWTYPE;
   1.669 +      "dimension_v"       INTEGER;
   1.670 +      "vote_matrix"       INT4[][];  -- absolute votes
   1.671 +      "matrix"            INT8[][];  -- defeat strength / best paths
   1.672 +      "i"                 INTEGER;
   1.673 +      "j"                 INTEGER;
   1.674 +      "k"                 INTEGER;
   1.675 +      "battle_row"        "battle"%ROWTYPE;
   1.676 +      "rank_ary"          INT4[];
   1.677 +      "rank_v"            INT4;
   1.678 +      "done_v"            INTEGER;
   1.679 +      "winners_ary"       INTEGER[];
   1.680 +      "initiative_id_v"   "initiative"."id"%TYPE;
   1.681 +    BEGIN
   1.682 +      SELECT * INTO "issue_row"
   1.683 +        FROM "issue" WHERE "id" = "issue_id_p"
   1.684 +        FOR UPDATE;
   1.685 +      SELECT * INTO "policy_row"
   1.686 +        FROM "policy" WHERE "id" = "issue_row"."policy_id";
   1.687 +      SELECT count(1) INTO "dimension_v"
   1.688 +        FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
   1.689 +      -- Create "vote_matrix" with absolute number of votes in pairwise
   1.690 +      -- comparison:
   1.691 +      "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
   1.692 +      "i" := 1;
   1.693 +      "j" := 2;
   1.694 +      FOR "battle_row" IN
   1.695 +        SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
   1.696 +        ORDER BY
   1.697 +        "winning_initiative_id" NULLS LAST,
   1.698 +        "losing_initiative_id" NULLS LAST
   1.699 +      LOOP
   1.700 +        "vote_matrix"["i"]["j"] := "battle_row"."count";
   1.701 +        IF "j" = "dimension_v" THEN
   1.702 +          "i" := "i" + 1;
   1.703 +          "j" := 1;
   1.704 +        ELSE
   1.705 +          "j" := "j" + 1;
   1.706 +          IF "j" = "i" THEN
   1.707 +            "j" := "j" + 1;
   1.708 +          END IF;
   1.709 +        END IF;
   1.710 +      END LOOP;
   1.711 +      IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
   1.712 +        RAISE EXCEPTION 'Wrong battle count (should not happen)';
   1.713 +      END IF;
   1.714 +      -- Store defeat strengths in "matrix" using "defeat_strength"
   1.715 +      -- function:
   1.716 +      "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
   1.717 +      "i" := 1;
   1.718 +      LOOP
   1.719 +        "j" := 1;
   1.720 +        LOOP
   1.721 +          IF "i" != "j" THEN
   1.722 +            "matrix"["i"]["j"] := "defeat_strength"(
   1.723 +              "vote_matrix"["i"]["j"],
   1.724 +              "vote_matrix"["j"]["i"]
   1.725 +            );
   1.726 +          END IF;
   1.727 +          EXIT WHEN "j" = "dimension_v";
   1.728 +          "j" := "j" + 1;
   1.729 +        END LOOP;
   1.730 +        EXIT WHEN "i" = "dimension_v";
   1.731 +        "i" := "i" + 1;
   1.732 +      END LOOP;
   1.733 +      -- Find best paths:
   1.734 +      "i" := 1;
   1.735 +      LOOP
   1.736 +        "j" := 1;
   1.737 +        LOOP
   1.738 +          IF "i" != "j" THEN
   1.739 +            "k" := 1;
   1.740 +            LOOP
   1.741 +              IF "i" != "k" AND "j" != "k" THEN
   1.742 +                IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
   1.743 +                  IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
   1.744 +                    "matrix"["j"]["k"] := "matrix"["j"]["i"];
   1.745 +                  END IF;
   1.746 +                ELSE
   1.747 +                  IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
   1.748 +                    "matrix"["j"]["k"] := "matrix"["i"]["k"];
   1.749 +                  END IF;
   1.750 +                END IF;
   1.751 +              END IF;
   1.752 +              EXIT WHEN "k" = "dimension_v";
   1.753 +              "k" := "k" + 1;
   1.754 +            END LOOP;
   1.755 +          END IF;
   1.756 +          EXIT WHEN "j" = "dimension_v";
   1.757 +          "j" := "j" + 1;
   1.758 +        END LOOP;
   1.759 +        EXIT WHEN "i" = "dimension_v";
   1.760 +        "i" := "i" + 1;
   1.761 +      END LOOP;
   1.762 +      -- Determine order of winners:
   1.763 +      "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
   1.764 +      "rank_v" := 1;
   1.765 +      "done_v" := 0;
   1.766 +      LOOP
   1.767 +        "winners_ary" := '{}';
   1.768 +        "i" := 1;
   1.769 +        LOOP
   1.770 +          IF "rank_ary"["i"] ISNULL THEN
   1.771 +            "j" := 1;
   1.772 +            LOOP
   1.773 +              IF
   1.774 +                "i" != "j" AND
   1.775 +                "rank_ary"["j"] ISNULL AND
   1.776 +                "matrix"["j"]["i"] > "matrix"["i"]["j"]
   1.777 +              THEN
   1.778 +                -- someone else is better
   1.779 +                EXIT;
   1.780 +              END IF;
   1.781 +              IF "j" = "dimension_v" THEN
   1.782 +                -- noone is better
   1.783 +                "winners_ary" := "winners_ary" || "i";
   1.784 +                EXIT;
   1.785 +              END IF;
   1.786 +              "j" := "j" + 1;
   1.787 +            END LOOP;
   1.788 +          END IF;
   1.789 +          EXIT WHEN "i" = "dimension_v";
   1.790 +          "i" := "i" + 1;
   1.791 +        END LOOP;
   1.792 +        "i" := 1;
   1.793 +        LOOP
   1.794 +          "rank_ary"["winners_ary"["i"]] := "rank_v";
   1.795 +          "done_v" := "done_v" + 1;
   1.796 +          EXIT WHEN "i" = array_upper("winners_ary", 1);
   1.797 +          "i" := "i" + 1;
   1.798 +        END LOOP;
   1.799 +        EXIT WHEN "done_v" = "dimension_v";
   1.800 +        "rank_v" := "rank_v" + 1;
   1.801 +      END LOOP;
   1.802 +      -- write preliminary results:
   1.803 +      "i" := 1;
   1.804 +      FOR "initiative_id_v" IN
   1.805 +        SELECT "id" FROM "initiative"
   1.806 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
   1.807 +        ORDER BY "id"
   1.808 +      LOOP
   1.809 +        UPDATE "initiative" SET
   1.810 +          "direct_majority" =
   1.811 +            CASE WHEN "policy_row"."direct_majority_strict" THEN
   1.812 +              "positive_votes" * "policy_row"."direct_majority_den" >
   1.813 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   1.814 +            ELSE
   1.815 +              "positive_votes" * "policy_row"."direct_majority_den" >=
   1.816 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   1.817 +            END
   1.818 +            AND "positive_votes" >= "policy_row"."direct_majority_positive"
   1.819 +            AND "issue_row"."voter_count"-"negative_votes" >=
   1.820 +                "policy_row"."direct_majority_non_negative",
   1.821 +            "indirect_majority" =
   1.822 +            CASE WHEN "policy_row"."indirect_majority_strict" THEN
   1.823 +              "positive_votes" * "policy_row"."indirect_majority_den" >
   1.824 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   1.825 +            ELSE
   1.826 +              "positive_votes" * "policy_row"."indirect_majority_den" >=
   1.827 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   1.828 +            END
   1.829 +            AND "positive_votes" >= "policy_row"."indirect_majority_positive"
   1.830 +            AND "issue_row"."voter_count"-"negative_votes" >=
   1.831 +                "policy_row"."indirect_majority_non_negative",
   1.832 +          "schulze_rank"           = "rank_ary"["i"],
   1.833 +          "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
   1.834 +          "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"["dimension_v"],
   1.835 +          "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
   1.836 +          "reverse_beat_path"      = "matrix"["dimension_v"]["i"] >= 0,
   1.837 +          "winner"                 = FALSE
   1.838 +          WHERE "id" = "initiative_id_v";
   1.839 +        "i" := "i" + 1;
   1.840 +      END LOOP;
   1.841 +      IF "i" != "dimension_v" THEN
   1.842 +        RAISE EXCEPTION 'Wrong winner count (should not happen)';
   1.843 +      END IF;
   1.844 +      -- take indirect majorities into account:
   1.845 +      LOOP
   1.846 +        UPDATE "initiative" SET "indirect_majority" = TRUE
   1.847 +          FROM (
   1.848 +            SELECT "new_initiative"."id" AS "initiative_id"
   1.849 +            FROM "initiative" "old_initiative"
   1.850 +            JOIN "initiative" "new_initiative"
   1.851 +              ON "new_initiative"."issue_id" = "issue_id_p"
   1.852 +              AND "new_initiative"."indirect_majority" = FALSE
   1.853 +            JOIN "battle" "battle_win"
   1.854 +              ON "battle_win"."issue_id" = "issue_id_p"
   1.855 +              AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
   1.856 +              AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
   1.857 +            JOIN "battle" "battle_lose"
   1.858 +              ON "battle_lose"."issue_id" = "issue_id_p"
   1.859 +              AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
   1.860 +              AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
   1.861 +            WHERE "old_initiative"."issue_id" = "issue_id_p"
   1.862 +            AND "old_initiative"."indirect_majority" = TRUE
   1.863 +            AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
   1.864 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >
   1.865 +              "policy_row"."indirect_majority_num" *
   1.866 +              ("battle_win"."count"+"battle_lose"."count")
   1.867 +            ELSE
   1.868 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >=
   1.869 +              "policy_row"."indirect_majority_num" *
   1.870 +              ("battle_win"."count"+"battle_lose"."count")
   1.871 +            END
   1.872 +            AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
   1.873 +            AND "issue_row"."voter_count"-"battle_lose"."count" >=
   1.874 +                "policy_row"."indirect_majority_non_negative"
   1.875 +          ) AS "subquery"
   1.876 +          WHERE "id" = "subquery"."initiative_id";
   1.877 +        EXIT WHEN NOT FOUND;
   1.878 +      END LOOP;
   1.879 +      -- set "multistage_majority" for remaining matching initiatives:
   1.880 +       UPDATE "initiative" SET "multistage_majority" = TRUE
   1.881 +        FROM (
   1.882 +          SELECT "losing_initiative"."id" AS "initiative_id"
   1.883 +          FROM "initiative" "losing_initiative"
   1.884 +          JOIN "initiative" "winning_initiative"
   1.885 +            ON "winning_initiative"."issue_id" = "issue_id_p"
   1.886 +            AND "winning_initiative"."admitted"
   1.887 +          JOIN "battle" "battle_win"
   1.888 +            ON "battle_win"."issue_id" = "issue_id_p"
   1.889 +            AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
   1.890 +            AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
   1.891 +          JOIN "battle" "battle_lose"
   1.892 +            ON "battle_lose"."issue_id" = "issue_id_p"
   1.893 +            AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
   1.894 +            AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
   1.895 +          WHERE "losing_initiative"."issue_id" = "issue_id_p"
   1.896 +          AND "losing_initiative"."admitted"
   1.897 +          AND "winning_initiative"."schulze_rank" <
   1.898 +              "losing_initiative"."schulze_rank"
   1.899 +          AND "battle_win"."count" > "battle_lose"."count"
   1.900 +          AND (
   1.901 +            "battle_win"."count" > "winning_initiative"."positive_votes" OR
   1.902 +            "battle_lose"."count" < "losing_initiative"."negative_votes" )
   1.903 +        ) AS "subquery"
   1.904 +        WHERE "id" = "subquery"."initiative_id";
   1.905 +      -- mark eligible initiatives:
   1.906 +      UPDATE "initiative" SET "eligible" = TRUE
   1.907 +        WHERE "issue_id" = "issue_id_p"
   1.908 +        AND "initiative"."direct_majority"
   1.909 +        AND "initiative"."indirect_majority"
   1.910 +        AND "initiative"."better_than_status_quo"
   1.911 +        AND (
   1.912 +          "policy_row"."no_multistage_majority" = FALSE OR
   1.913 +          "initiative"."multistage_majority" = FALSE )
   1.914 +        AND (
   1.915 +          "policy_row"."no_reverse_beat_path" = FALSE OR
   1.916 +          "initiative"."reverse_beat_path" = FALSE );
   1.917 +      -- mark final winner:
   1.918 +      UPDATE "initiative" SET "winner" = TRUE
   1.919 +        FROM (
   1.920 +          SELECT "id" AS "initiative_id"
   1.921 +          FROM "initiative"
   1.922 +          WHERE "issue_id" = "issue_id_p" AND "eligible"
   1.923 +          ORDER BY "schulze_rank", "id"
   1.924 +          LIMIT 1
   1.925 +        ) AS "subquery"
   1.926 +        WHERE "id" = "subquery"."initiative_id";
   1.927 +      -- write (final) ranks:
   1.928 +      "rank_v" := 1;
   1.929 +      FOR "initiative_id_v" IN
   1.930 +        SELECT "id"
   1.931 +        FROM "initiative"
   1.932 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
   1.933 +        ORDER BY
   1.934 +          "winner" DESC,
   1.935 +          ("direct_majority" AND "indirect_majority") DESC,
   1.936 +          "schulze_rank",
   1.937 +          "id"
   1.938 +      LOOP
   1.939 +        UPDATE "initiative" SET "rank" = "rank_v"
   1.940 +          WHERE "id" = "initiative_id_v";
   1.941 +        "rank_v" := "rank_v" + 1;
   1.942 +      END LOOP;
   1.943 +      -- set schulze rank of status quo and mark issue as finished:
   1.944 +      UPDATE "issue" SET
   1.945 +        "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
   1.946 +        "state" =
   1.947 +          CASE WHEN EXISTS (
   1.948 +            SELECT NULL FROM "initiative"
   1.949 +            WHERE "issue_id" = "issue_id_p" AND "winner"
   1.950 +          ) THEN
   1.951 +            'finished_with_winner'::"issue_state"
   1.952 +          ELSE
   1.953 +            'finished_without_winner'::"issue_state"
   1.954 +          END,
   1.955 +        "ranks_available" = TRUE
   1.956 +        WHERE "id" = "issue_id_p";
   1.957 +      RETURN;
   1.958 +    END;
   1.959 +  $$;
   1.960 +
   1.961 +CREATE OR REPLACE FUNCTION "check_issue"
   1.962 +  ( "issue_id_p" "issue"."id"%TYPE )
   1.963 +  RETURNS VOID
   1.964 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.965 +    DECLARE
   1.966 +      "issue_row"         "issue"%ROWTYPE;
   1.967 +      "policy_row"        "policy"%ROWTYPE;
   1.968 +    BEGIN
   1.969 +      PERFORM "lock_issue"("issue_id_p");
   1.970 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.971 +      -- only process open issues:
   1.972 +      IF "issue_row"."closed" ISNULL THEN
   1.973 +        SELECT * INTO "policy_row" FROM "policy"
   1.974 +          WHERE "id" = "issue_row"."policy_id";
   1.975 +        -- create a snapshot, unless issue is already fully frozen:
   1.976 +        IF "issue_row"."fully_frozen" ISNULL THEN
   1.977 +          PERFORM "create_snapshot"("issue_id_p");
   1.978 +          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.979 +        END IF;
   1.980 +        -- eventually close or accept issues, which have not been accepted:
   1.981 +        IF "issue_row"."accepted" ISNULL THEN
   1.982 +          IF EXISTS (
   1.983 +            SELECT NULL FROM "initiative"
   1.984 +            WHERE "issue_id" = "issue_id_p"
   1.985 +            AND "supporter_count" > 0
   1.986 +            AND "supporter_count" * "policy_row"."issue_quorum_den"
   1.987 +            >= "issue_row"."population" * "policy_row"."issue_quorum_num"
   1.988 +          ) THEN
   1.989 +            -- accept issues, if supporter count is high enough
   1.990 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
   1.991 +            -- NOTE: "issue_row" used later
   1.992 +            "issue_row"."state" := 'discussion';
   1.993 +            "issue_row"."accepted" := now();
   1.994 +            UPDATE "issue" SET
   1.995 +              "state"    = "issue_row"."state",
   1.996 +              "accepted" = "issue_row"."accepted"
   1.997 +              WHERE "id" = "issue_row"."id";
   1.998 +          ELSIF
   1.999 +            now() >= "issue_row"."created" + "issue_row"."admission_time"
  1.1000 +          THEN
  1.1001 +            -- close issues, if admission time has expired
  1.1002 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  1.1003 +            UPDATE "issue" SET
  1.1004 +              "state" = 'canceled_issue_not_accepted',
  1.1005 +              "closed" = now()
  1.1006 +              WHERE "id" = "issue_row"."id";
  1.1007 +          END IF;
  1.1008 +        END IF;
  1.1009 +        -- eventually half freeze issues:
  1.1010 +        IF
  1.1011 +          -- NOTE: issue can't be closed at this point, if it has been accepted
  1.1012 +          "issue_row"."accepted" NOTNULL AND
  1.1013 +          "issue_row"."half_frozen" ISNULL
  1.1014 +        THEN
  1.1015 +          IF
  1.1016 +            now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
  1.1017 +          THEN
  1.1018 +            PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
  1.1019 +            -- NOTE: "issue_row" used later
  1.1020 +            "issue_row"."state" := 'verification';
  1.1021 +            "issue_row"."half_frozen" := now();
  1.1022 +            UPDATE "issue" SET
  1.1023 +              "state"       = "issue_row"."state",
  1.1024 +              "half_frozen" = "issue_row"."half_frozen"
  1.1025 +              WHERE "id" = "issue_row"."id";
  1.1026 +          END IF;
  1.1027 +        END IF;
  1.1028 +        -- close issues after some time, if all initiatives have been revoked:
  1.1029 +        IF
  1.1030 +          "issue_row"."closed" ISNULL AND
  1.1031 +          NOT EXISTS (
  1.1032 +            -- all initiatives are revoked
  1.1033 +            SELECT NULL FROM "initiative"
  1.1034 +            WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
  1.1035 +          ) AND (
  1.1036 +            -- and issue has not been accepted yet
  1.1037 +            "issue_row"."accepted" ISNULL OR
  1.1038 +            NOT EXISTS (
  1.1039 +              -- or no initiatives have been revoked lately
  1.1040 +              SELECT NULL FROM "initiative"
  1.1041 +              WHERE "issue_id" = "issue_id_p"
  1.1042 +              AND now() < "revoked" + "issue_row"."verification_time"
  1.1043 +            ) OR (
  1.1044 +              -- or verification time has elapsed
  1.1045 +              "issue_row"."half_frozen" NOTNULL AND
  1.1046 +              "issue_row"."fully_frozen" ISNULL AND
  1.1047 +              now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  1.1048 +            )
  1.1049 +          )
  1.1050 +        THEN
  1.1051 +          -- NOTE: "issue_row" used later
  1.1052 +          IF "issue_row"."accepted" ISNULL THEN
  1.1053 +            "issue_row"."state" := 'canceled_revoked_before_accepted';
  1.1054 +          ELSIF "issue_row"."half_frozen" ISNULL THEN
  1.1055 +            "issue_row"."state" := 'canceled_after_revocation_during_discussion';
  1.1056 +          ELSE
  1.1057 +            "issue_row"."state" := 'canceled_after_revocation_during_verification';
  1.1058 +          END IF;
  1.1059 +          "issue_row"."closed" := now();
  1.1060 +          UPDATE "issue" SET
  1.1061 +            "state"  = "issue_row"."state",
  1.1062 +            "closed" = "issue_row"."closed"
  1.1063 +            WHERE "id" = "issue_row"."id";
  1.1064 +        END IF;
  1.1065 +        -- fully freeze issue after verification time:
  1.1066 +        IF
  1.1067 +          "issue_row"."half_frozen" NOTNULL AND
  1.1068 +          "issue_row"."fully_frozen" ISNULL AND
  1.1069 +          "issue_row"."closed" ISNULL AND
  1.1070 +          now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  1.1071 +        THEN
  1.1072 +          PERFORM "freeze_after_snapshot"("issue_id_p");
  1.1073 +          -- NOTE: "issue" might change, thus "issue_row" has to be updated below
  1.1074 +        END IF;
  1.1075 +        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1076 +        -- close issue by calling close_voting(...) after voting time:
  1.1077 +        IF
  1.1078 +          "issue_row"."closed" ISNULL AND
  1.1079 +          "issue_row"."fully_frozen" NOTNULL AND
  1.1080 +          now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
  1.1081 +        THEN
  1.1082 +          PERFORM "close_voting"("issue_id_p");
  1.1083 +          -- calculate ranks will not consume much time and can be done now
  1.1084 +          PERFORM "calculate_ranks"("issue_id_p");
  1.1085 +        END IF;
  1.1086 +      END IF;
  1.1087 +      RETURN;
  1.1088 +    END;
  1.1089 +  $$;
  1.1090 +
  1.1091 +CREATE OR REPLACE FUNCTION "check_everything"()
  1.1092 +  RETURNS VOID
  1.1093 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1094 +    DECLARE
  1.1095 +      "issue_id_v" "issue"."id"%TYPE;
  1.1096 +    BEGIN
  1.1097 +      PERFORM "check_activity"();
  1.1098 +      PERFORM "calculate_member_counts"();
  1.1099 +      FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
  1.1100 +        PERFORM "check_issue"("issue_id_v");
  1.1101 +      END LOOP;
  1.1102 +      FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
  1.1103 +        PERFORM "calculate_ranks"("issue_id_v");
  1.1104 +      END LOOP;
  1.1105 +      RETURN;
  1.1106 +    END;
  1.1107 +  $$;
  1.1108 +
  1.1109 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
  1.1110 +  RETURNS VOID
  1.1111 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1112 +    BEGIN
  1.1113 +      UPDATE "member" SET
  1.1114 +        "last_login"                   = NULL,
  1.1115 +        "login"                        = NULL,
  1.1116 +        "password"                     = NULL,
  1.1117 +        "locked"                       = TRUE,
  1.1118 +        "active"                       = FALSE,
  1.1119 +        "notify_email"                 = NULL,
  1.1120 +        "notify_email_unconfirmed"     = NULL,
  1.1121 +        "notify_email_secret"          = NULL,
  1.1122 +        "notify_email_secret_expiry"   = NULL,
  1.1123 +        "notify_email_lock_expiry"     = NULL,
  1.1124 +        "password_reset_secret"        = NULL,
  1.1125 +        "password_reset_secret_expiry" = NULL,
  1.1126 +        "organizational_unit"          = NULL,
  1.1127 +        "internal_posts"               = NULL,
  1.1128 +        "realname"                     = NULL,
  1.1129 +        "birthday"                     = NULL,
  1.1130 +        "address"                      = NULL,
  1.1131 +        "email"                        = NULL,
  1.1132 +        "xmpp_address"                 = NULL,
  1.1133 +        "website"                      = NULL,
  1.1134 +        "phone"                        = NULL,
  1.1135 +        "mobile_phone"                 = NULL,
  1.1136 +        "profession"                   = NULL,
  1.1137 +        "external_memberships"         = NULL,
  1.1138 +        "external_posts"               = NULL,
  1.1139 +        "statement"                    = NULL
  1.1140 +        WHERE "id" = "member_id_p";
  1.1141 +      -- "text_search_data" is updated by triggers
  1.1142 +      DELETE FROM "setting"            WHERE "member_id" = "member_id_p";
  1.1143 +      DELETE FROM "setting_map"        WHERE "member_id" = "member_id_p";
  1.1144 +      DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
  1.1145 +      DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
  1.1146 +      DELETE FROM "contact"            WHERE "member_id" = "member_id_p";
  1.1147 +      DELETE FROM "ignored_member"     WHERE "member_id" = "member_id_p";
  1.1148 +      DELETE FROM "area_setting"       WHERE "member_id" = "member_id_p";
  1.1149 +      DELETE FROM "issue_setting"      WHERE "member_id" = "member_id_p";
  1.1150 +      DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
  1.1151 +      DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
  1.1152 +      DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
  1.1153 +      DELETE FROM "membership"         WHERE "member_id" = "member_id_p";
  1.1154 +      DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
  1.1155 +      DELETE FROM "non_voter"          WHERE "member_id" = "member_id_p";
  1.1156 +      DELETE FROM "direct_voter" USING "issue"
  1.1157 +        WHERE "direct_voter"."issue_id" = "issue"."id"
  1.1158 +        AND "issue"."closed" ISNULL
  1.1159 +        AND "member_id" = "member_id_p";
  1.1160 +      RETURN;
  1.1161 +    END;
  1.1162 +  $$;
  1.1163 +
  1.1164 +CREATE OR REPLACE FUNCTION "delete_private_data"()
  1.1165 +  RETURNS VOID
  1.1166 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1167 +    BEGIN
  1.1168 +      UPDATE "member" SET
  1.1169 +        "last_login"                   = NULL,
  1.1170 +        "login"                        = NULL,
  1.1171 +        "password"                     = NULL,
  1.1172 +        "notify_email"                 = NULL,
  1.1173 +        "notify_email_unconfirmed"     = NULL,
  1.1174 +        "notify_email_secret"          = NULL,
  1.1175 +        "notify_email_secret_expiry"   = NULL,
  1.1176 +        "notify_email_lock_expiry"     = NULL,
  1.1177 +        "password_reset_secret"        = NULL,
  1.1178 +        "password_reset_secret_expiry" = NULL,
  1.1179 +        "organizational_unit"          = NULL,
  1.1180 +        "internal_posts"               = NULL,
  1.1181 +        "realname"                     = NULL,
  1.1182 +        "birthday"                     = NULL,
  1.1183 +        "address"                      = NULL,
  1.1184 +        "email"                        = NULL,
  1.1185 +        "xmpp_address"                 = NULL,
  1.1186 +        "website"                      = NULL,
  1.1187 +        "phone"                        = NULL,
  1.1188 +        "mobile_phone"                 = NULL,
  1.1189 +        "profession"                   = NULL,
  1.1190 +        "external_memberships"         = NULL,
  1.1191 +        "external_posts"               = NULL,
  1.1192 +        "statement"                    = NULL;
  1.1193 +      -- "text_search_data" is updated by triggers
  1.1194 +      DELETE FROM "invite_code";
  1.1195 +      DELETE FROM "setting";
  1.1196 +      DELETE FROM "setting_map";
  1.1197 +      DELETE FROM "member_relation_setting";
  1.1198 +      DELETE FROM "member_image";
  1.1199 +      DELETE FROM "contact";
  1.1200 +      DELETE FROM "ignored_member";
  1.1201 +      DELETE FROM "area_setting";
  1.1202 +      DELETE FROM "issue_setting";
  1.1203 +      DELETE FROM "ignored_initiative";
  1.1204 +      DELETE FROM "initiative_setting";
  1.1205 +      DELETE FROM "suggestion_setting";
  1.1206 +      DELETE FROM "non_voter";
  1.1207 +      DELETE FROM "direct_voter" USING "issue"
  1.1208 +        WHERE "direct_voter"."issue_id" = "issue"."id"
  1.1209 +        AND "issue"."closed" ISNULL;
  1.1210 +      RETURN;
  1.1211 +    END;
  1.1212 +  $$;
  1.1213 +
  1.1214 +COMMIT;
  1.1215 +
  1.1216 +BEGIN;
  1.1217 +
  1.1218 +UPDATE "member" SET
  1.1219 +  "activated" = "created",
  1.1220 +  "last_activity" = CASE WHEN "active" THEN
  1.1221 +    coalesce("last_login"::DATE, now())
  1.1222 +  ELSE
  1.1223 +    "last_login"::DATE
  1.1224 +  END;
  1.1225 +
  1.1226 +UPDATE "member" SET
  1.1227 +  "created" = "invite_code"."created",
  1.1228 +  "invite_code" = "invite_code"."code",
  1.1229 +  "admin_comment" = "invite_code"."comment"
  1.1230 +  FROM "invite_code"
  1.1231 +  WHERE "member"."id" = "invite_code"."member_id";
  1.1232 +
  1.1233 +DROP TABLE "invite_code";
  1.1234 +
  1.1235 +UPDATE "initiative" SET
  1.1236 +    "direct_majority"        = "rank" NOTNULL,
  1.1237 +    "indirect_majority"      = "rank" NOTNULL,
  1.1238 +    "schulze_rank"           = "rank",
  1.1239 +    "better_than_status_quo" = "rank" NOTNULL,
  1.1240 +    "worse_than_status_quo"  = "rank" ISNULL,
  1.1241 +    "reverse_beat_path"      = "rank" ISNULL,
  1.1242 +    "multistage_majority"    = "rank" ISNULL,
  1.1243 +    "eligible"               = "rank" NOTNULL,
  1.1244 +    "winner"                 = ("rank" = 1)
  1.1245 +  FROM "issue"
  1.1246 +  WHERE "issue"."id" = "initiative"."issue_id"
  1.1247 +  AND "issue"."state" IN ('finished_without_winner', 'finished_with_winner')
  1.1248 +  AND "initiative"."admitted";
  1.1249 +
  1.1250 +UPDATE "issue" SET "status_quo_schulze_rank" = "subquery"."rank"
  1.1251 +  FROM (
  1.1252 +    SELECT
  1.1253 +      "issue"."id" AS "issue_id",
  1.1254 +      COALESCE(max("initiative"."rank") + 1, 1) AS "rank"
  1.1255 +    FROM "issue" JOIN "initiative"
  1.1256 +    ON "issue"."id" = "initiative"."issue_id"
  1.1257 +    WHERE "issue"."state" IN ('finished_without_winner', 'finished_with_winner')
  1.1258 +    AND "initiative"."admitted"
  1.1259 +    GROUP BY "issue"."id"
  1.1260 +  ) AS "subquery"
  1.1261 +  WHERE "issue"."id" = "subquery"."issue_id";
  1.1262 +
  1.1263 +CREATE FUNCTION "update__set_remaining_ranks"("issue_id_p" "issue"."id"%TYPE)
  1.1264 +  RETURNS VOID
  1.1265 +  LANGUAGE 'plpgsql' AS $$
  1.1266 +    DECLARE
  1.1267 +      "rank_v"          INT4;
  1.1268 +      "initiative_id_v" INT4;
  1.1269 +    BEGIN
  1.1270 +      SELECT "status_quo_schulze_rank" INTO "rank_v"
  1.1271 +        FROM "issue" WHERE "id" = "issue_id_p";
  1.1272 +      FOR "initiative_id_v" IN
  1.1273 +        SELECT "id" FROM "initiative"
  1.1274 +        WHERE "issue_id" = "issue_id_p" AND "admitted" AND "rank" ISNULL
  1.1275 +        ORDER BY "vote_ratio"("positive_votes", "negative_votes") DESC
  1.1276 +      LOOP
  1.1277 +        UPDATE "initiative" SET
  1.1278 +          "schulze_rank" = "rank_v" + 1,
  1.1279 +          "rank"         = "rank_v"
  1.1280 +          WHERE "id" = "initiative_id_v";
  1.1281 +        "rank_v" := "rank_v" + 1;
  1.1282 +      END LOOP;
  1.1283 +      RETURN;
  1.1284 +    END;
  1.1285 +  $$;
  1.1286 +
  1.1287 +SELECT "update__set_remaining_ranks"("id") FROM "issue"
  1.1288 +  WHERE "state" IN ('finished_without_winner', 'finished_with_winner');
  1.1289 +
  1.1290 +DROP FUNCTION "update__set_remaining_ranks"("issue"."id"%TYPE);
  1.1291 +
  1.1292 +UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id"
  1.1293 +  FROM (
  1.1294 +    SELECT DISTINCT ON ("suggestion"."id")
  1.1295 +      "suggestion"."id" AS "suggestion_id",
  1.1296 +      "draft"."id" AS "draft_id"
  1.1297 +    FROM "suggestion" JOIN "draft"
  1.1298 +    ON "suggestion"."initiative_id" = "draft"."initiative_id"
  1.1299 +    WHERE "draft"."created" <= "suggestion"."created"
  1.1300 +    ORDER BY "suggestion"."id", "draft"."created" DESC
  1.1301 +  ) AS "subquery"
  1.1302 +  WHERE "suggestion"."id" = "subquery"."suggestion_id";
  1.1303 +
  1.1304 +COMMIT;
  1.1305 +
  1.1306 +ALTER TABLE "member" ADD CONSTRAINT "active_requires_activated_and_last_activity"
  1.1307 +  CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL));
  1.1308 +ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;