liquid_feedback_core

changeset 193:055d232404d6

Renamed v1.5.0 to v2.0.0
author jbe
date Sat Sep 10 22:39:06 2011 +0200 (2011-09-10)
parents a5fed9a4154e
children 5d89f5fcdcfc
files core.sql update/core-update.v1.4.0_rc4-v1.5.0_rc1.sql update/core-update.v1.4.0_rc4-v2.0.0.sql
line diff
     1.1 --- a/core.sql	Sun Sep 04 23:42:05 2011 +0200
     1.2 +++ b/core.sql	Sat Sep 10 22:39:06 2011 +0200
     1.3 @@ -7,7 +7,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('1.5.0_rc1', 1, 5, -1))
     1.8 +  SELECT * FROM (VALUES ('2.0.0', 2, 0, 0))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
     2.1 --- a/update/core-update.v1.4.0_rc4-v1.5.0_rc1.sql	Sun Sep 04 23:42:05 2011 +0200
     2.2 +++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.3 @@ -1,1330 +0,0 @@
     2.4 -SELECT "calculate_ranks"("id") FROM "issue_with_ranks_missing";
     2.5 -
     2.6 -BEGIN;
     2.7 -
     2.8 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     2.9 -  SELECT * FROM (VALUES ('1.5.0_rc1', 1, 5, -1))
    2.10 -  AS "subquery"("string", "major", "minor", "revision");
    2.11 -
    2.12 -ALTER TABLE "member" ADD COLUMN "invite_code" TEXT UNIQUE;
    2.13 -ALTER TABLE "member" ADD COLUMN "admin_comment" TEXT;
    2.14 -ALTER TABLE "member" ADD COLUMN "activated" TIMESTAMPTZ;
    2.15 -ALTER TABLE "member" ADD COLUMN "last_activity" DATE;
    2.16 -ALTER TABLE "member" DROP COLUMN "last_login_public";
    2.17 -ALTER TABLE "member" ALTER COLUMN "active" SET DEFAULT FALSE;
    2.18 -ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT;
    2.19 -
    2.20 -COMMENT ON COLUMN "member"."created"           IS 'Creation of member record and/or invite code';
    2.21 -COMMENT ON COLUMN "member"."invite_code"       IS 'Optional invite code, to allow a member to initialize his/her account the first time';
    2.22 -COMMENT ON COLUMN "member"."admin_comment"     IS 'Hidden comment for administrative purposes';
    2.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';
    2.24 -COMMENT ON COLUMN "member"."last_activity"     IS 'Date of last activity of member; required to be set for "active" members';
    2.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".';
    2.26 -COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
    2.27 -
    2.28 -CREATE TYPE "application_access_level" AS ENUM
    2.29 -  ('member', 'full', 'pseudonymous', 'anonymous');
    2.30 -
    2.31 -COMMENT ON TYPE "application_access_level" IS 'Access privileges for applications using the API';
    2.32 -
    2.33 -CREATE TABLE "member_application" (
    2.34 -        "id"                    SERIAL8         PRIMARY KEY,
    2.35 -        UNIQUE ("member_id", "name"),
    2.36 -        "member_id"             INT4            NOT NULL REFERENCES "member" ("id")
    2.37 -                                                ON DELETE CASCADE ON UPDATE CASCADE,
    2.38 -        "name"                  TEXT            NOT NULL,
    2.39 -        "comment"               TEXT,
    2.40 -        "access_level" "application_access_level" NOT NULL,
    2.41 -        "key"                   TEXT            NOT NULL UNIQUE,
    2.42 -        "last_usage"            TIMESTAMPTZ );
    2.43 -
    2.44 -COMMENT ON TABLE "member_application" IS 'Registered application being allowed to use the API';
    2.45 -
    2.46 -CREATE TABLE "rendered_member_statement" (
    2.47 -        PRIMARY KEY ("member_id", "format"),
    2.48 -        "member_id"             INT8            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.49 -        "format"                TEXT,
    2.50 -        "content"               TEXT            NOT NULL );
    2.51 -
    2.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)';
    2.53 -
    2.54 -DROP VIEW "expired_session";
    2.55 -DROP TABLE "session";
    2.56 -
    2.57 -ALTER TABLE "policy" ADD COLUMN "direct_majority_num"            INT4    NOT NULL DEFAULT 1;
    2.58 -ALTER TABLE "policy" ADD COLUMN "direct_majority_den"            INT4    NOT NULL DEFAULT 2;
    2.59 -ALTER TABLE "policy" ADD COLUMN "direct_majority_strict"         BOOLEAN NOT NULL DEFAULT TRUE;
    2.60 -ALTER TABLE "policy" ADD COLUMN "direct_majority_positive"       INT4    NOT NULL DEFAULT 0;
    2.61 -ALTER TABLE "policy" ADD COLUMN "direct_majority_non_negative"   INT4    NOT NULL DEFAULT 0;
    2.62 -ALTER TABLE "policy" ADD COLUMN "indirect_majority_num"          INT4    NOT NULL DEFAULT 1;
    2.63 -ALTER TABLE "policy" ADD COLUMN "indirect_majority_den"          INT4    NOT NULL DEFAULT 2;
    2.64 -ALTER TABLE "policy" ADD COLUMN "indirect_majority_strict"       BOOLEAN NOT NULL DEFAULT TRUE;
    2.65 -ALTER TABLE "policy" ADD COLUMN "indirect_majority_positive"     INT4    NOT NULL DEFAULT 0;
    2.66 -ALTER TABLE "policy" ADD COLUMN "indirect_majority_non_negative" INT4    NOT NULL DEFAULT 0;
    2.67 -ALTER TABLE "policy" ADD COLUMN "no_reverse_beat_path"           BOOLEAN NOT NULL DEFAULT TRUE;
    2.68 -ALTER TABLE "policy" ADD COLUMN "no_multistage_majority"         BOOLEAN NOT NULL DEFAULT FALSE;
    2.69 -
    2.70 -UPDATE "policy" SET
    2.71 -  "direct_majority_num"      = "majority_num",
    2.72 -  "direct_majority_den"      = "majority_den",
    2.73 -  "direct_majority_strict"   = "majority_strict",
    2.74 -  "indirect_majority_num"    = "majority_num",
    2.75 -  "indirect_majority_den"    = "majority_den",
    2.76 -  "indirect_majority_strict" = "majority_strict";
    2.77 -
    2.78 -ALTER TABLE "policy" DROP COLUMN "majority_num";
    2.79 -ALTER TABLE "policy" DROP COLUMN "majority_den";
    2.80 -ALTER TABLE "policy" DROP COLUMN "majority_strict";
    2.81 -
    2.82 -COMMENT ON COLUMN "policy"."direct_majority_num"            IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
    2.83 -COMMENT ON COLUMN "policy"."direct_majority_den"            IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
    2.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.';
    2.85 -COMMENT ON COLUMN "policy"."direct_majority_positive"       IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
    2.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';
    2.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';
    2.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';
    2.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.';
    2.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';
    2.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';
    2.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.';
    2.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").';
    2.94 -
    2.95 -ALTER TABLE "area" DROP COLUMN "autoreject_weight";
    2.96 -
    2.97 -DROP VIEW "open_issue";
    2.98 -DROP VIEW "issue_with_ranks_missing";
    2.99 -
   2.100 -ALTER TABLE "issue" DROP COLUMN "vote_now";
   2.101 -ALTER TABLE "issue" DROP COLUMN "vote_later";
   2.102 -ALTER TABLE "issue" ADD COLUMN "status_quo_schulze_rank" INT4;
   2.103 -
   2.104 -CREATE VIEW "open_issue" AS
   2.105 -  SELECT * FROM "issue" WHERE "closed" ISNULL;
   2.106 -
   2.107 -COMMENT ON VIEW "open_issue" IS 'All open issues';
   2.108 -
   2.109 -CREATE VIEW "issue_with_ranks_missing" AS
   2.110 -  SELECT * FROM "issue"
   2.111 -  WHERE "fully_frozen" NOTNULL
   2.112 -  AND "closed" NOTNULL
   2.113 -  AND "ranks_available" = FALSE;
   2.114 -
   2.115 -COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
   2.116 -
   2.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.';
   2.118 -COMMENT ON COLUMN "issue"."snapshot"                IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
   2.119 -COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
   2.120 -
   2.121 -DROP VIEW "battle_view";
   2.122 -
   2.123 -ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
   2.124 -ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null";
   2.125 -ALTER TABLE "initiative" DROP CONSTRAINT "non_agreed_initiatives_cant_get_a_rank";
   2.126 -
   2.127 -ALTER TABLE "initiative" DROP COLUMN "agreed";
   2.128 -ALTER TABLE "initiative" ADD COLUMN "direct_majority"        BOOLEAN;
   2.129 -ALTER TABLE "initiative" ADD COLUMN "indirect_majority"      BOOLEAN;
   2.130 -ALTER TABLE "initiative" ADD COLUMN "schulze_rank"           INT4;
   2.131 -ALTER TABLE "initiative" ADD COLUMN "better_than_status_quo" BOOLEAN;
   2.132 -ALTER TABLE "initiative" ADD COLUMN "worse_than_status_quo"  BOOLEAN;
   2.133 -ALTER TABLE "initiative" ADD COLUMN "reverse_beat_path"      BOOLEAN;
   2.134 -ALTER TABLE "initiative" ADD COLUMN "multistage_majority"    BOOLEAN;
   2.135 -ALTER TABLE "initiative" ADD COLUMN "eligible"               BOOLEAN;
   2.136 -ALTER TABLE "initiative" ADD COLUMN "winner"                 BOOLEAN;
   2.137 -
   2.138 -ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
   2.139 -  ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
   2.140 -  ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
   2.141 -    "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
   2.142 -    "schulze_rank" ISNULL AND
   2.143 -    "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
   2.144 -    "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
   2.145 -    "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
   2.146 -ALTER TABLE "initiative" ADD CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo"));
   2.147 -ALTER TABLE "initiative" ADD CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
   2.148 -  "eligible" = FALSE OR
   2.149 -("direct_majority" AND "indirect_majority" AND "better_than_status_quo") );
   2.150 -ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE);
   2.151 -ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1);
   2.152 -ALTER TABLE "initiative" ADD CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE);
   2.153 -ALTER TABLE "initiative" ADD CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank");
   2.154 -
   2.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"';
   2.156 -COMMENT ON COLUMN "initiative"."indirect_majority"       IS 'Same as "direct_majority", but also considering indirect beat paths';
   2.157 -COMMENT ON COLUMN "initiative"."schulze_rank"            IS 'Schulze-Ranking without tie-breaking';
   2.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)';
   2.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)';
   2.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';
   2.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';
   2.162 -COMMENT ON COLUMN "initiative"."eligible"                IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"';
   2.163 -COMMENT ON COLUMN "initiative"."winner"                  IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
   2.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';
   2.165 -
   2.166 -ALTER TABLE "battle" DROP CONSTRAINT "battle_pkey";
   2.167 -ALTER TABLE "battle" ALTER COLUMN "issue_id" SET NOT NULL;
   2.168 -ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK (
   2.169 -  "winning_initiative_id" != "losing_initiative_id" OR
   2.170 -  ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
   2.171 -    ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) );
   2.172 -
   2.173 -CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
   2.174 -CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
   2.175 -CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
   2.176 -
   2.177 -ALTER TABLE "suggestion" ADD COLUMN "draft_id" INT8;
   2.178 -ALTER TABLE "suggestion" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;
   2.179 -ALTER TABLE "suggestion" ADD COLUMN "formatting_engine" TEXT;
   2.180 -ALTER TABLE "suggestion" RENAME COLUMN "description" TO "content";
   2.181 -
   2.182 -DROP TRIGGER "update_text_search_data" ON "suggestion";
   2.183 -
   2.184 -CREATE TRIGGER "update_text_search_data"
   2.185 -  BEFORE INSERT OR UPDATE ON "suggestion"
   2.186 -  FOR EACH ROW EXECUTE PROCEDURE
   2.187 -  tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
   2.188 -    "name", "content");
   2.189 -
   2.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")';
   2.191 -
   2.192 -CREATE TABLE "rendered_suggestion" (
   2.193 -        PRIMARY KEY ("suggestion_id", "format"),
   2.194 -        "suggestion_id"         INT8            REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.195 -        "format"                TEXT,
   2.196 -        "content"               TEXT            NOT NULL );
   2.197 -
   2.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)';
   2.199 -
   2.200 -DROP TABLE "invite_code_unit";
   2.201 -
   2.202 -DROP VIEW "area_member_count";
   2.203 -
   2.204 -ALTER TABLE "membership" DROP COLUMN "autoreject";
   2.205 -
   2.206 -ALTER TABLE "interest" DROP COLUMN "autoreject";
   2.207 -ALTER TABLE "interest" DROP COLUMN "voting_requested";
   2.208 -
   2.209 -ALTER TABLE "supporter" DROP CONSTRAINT "supporter_initiative_id_fkey";
   2.210 -ALTER TABLE "supporter" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;
   2.211 -
   2.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")';
   2.213 -
   2.214 -ALTER TABLE "direct_interest_snapshot" DROP COLUMN "voting_requested";
   2.215 -ALTER TABLE "direct_voter" DROP COLUMN "autoreject";
   2.216 -
   2.217 -DROP TRIGGER "default_for_draft_id" ON "supporter";
   2.218 -DROP FUNCTION "supporter_default_for_draft_id_trigger"();
   2.219 -
   2.220 -CREATE FUNCTION "default_for_draft_id_trigger"()
   2.221 -  RETURNS TRIGGER
   2.222 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.223 -    BEGIN
   2.224 -      IF NEW."draft_id" ISNULL THEN
   2.225 -        SELECT "id" INTO NEW."draft_id" FROM "current_draft"
   2.226 -          WHERE "initiative_id" = NEW."initiative_id";
   2.227 -      END IF;
   2.228 -      RETURN NEW;
   2.229 -    END;
   2.230 -  $$;
   2.231 -
   2.232 -CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
   2.233 -  FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
   2.234 -CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
   2.235 -  FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
   2.236 -
   2.237 -COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
   2.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';
   2.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';
   2.240 -
   2.241 -CREATE VIEW "area_member_count" AS
   2.242 -  SELECT
   2.243 -    "area"."id" AS "area_id",
   2.244 -    count("member"."id") AS "direct_member_count",
   2.245 -    coalesce(
   2.246 -      sum(
   2.247 -        CASE WHEN "member"."id" NOTNULL THEN
   2.248 -          "membership_weight"("area"."id", "member"."id")
   2.249 -        ELSE 0 END
   2.250 -      )
   2.251 -    ) AS "member_weight"
   2.252 -  FROM "area"
   2.253 -  LEFT JOIN "membership"
   2.254 -  ON "area"."id" = "membership"."area_id"
   2.255 -  LEFT JOIN "privilege"
   2.256 -  ON "privilege"."unit_id" = "area"."unit_id"
   2.257 -  AND "privilege"."member_id" = "membership"."member_id"
   2.258 -  AND "privilege"."voting_right"
   2.259 -  LEFT JOIN "member"
   2.260 -  ON "member"."id" = "privilege"."member_id"  -- NOTE: no membership here!
   2.261 -  AND "member"."active"
   2.262 -  GROUP BY "area"."id";
   2.263 -
   2.264 -COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
   2.265 -
   2.266 -CREATE VIEW "battle_participant" AS
   2.267 -    SELECT "initiative"."id", "initiative"."issue_id"
   2.268 -    FROM "issue" JOIN "initiative"
   2.269 -    ON "issue"."id" = "initiative"."issue_id"
   2.270 -    WHERE "initiative"."admitted"
   2.271 -  UNION ALL
   2.272 -    SELECT NULL, "id" AS "issue_id"
   2.273 -    FROM "issue";
   2.274 -
   2.275 -COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
   2.276 -
   2.277 -CREATE VIEW "battle_view" AS
   2.278 -  SELECT
   2.279 -    "issue"."id" AS "issue_id",
   2.280 -    "winning_initiative"."id" AS "winning_initiative_id",
   2.281 -    "losing_initiative"."id" AS "losing_initiative_id",
   2.282 -    sum(
   2.283 -      CASE WHEN
   2.284 -        coalesce("better_vote"."grade", 0) >
   2.285 -        coalesce("worse_vote"."grade", 0)
   2.286 -      THEN "direct_voter"."weight" ELSE 0 END
   2.287 -    ) AS "count"
   2.288 -  FROM "issue"
   2.289 -  LEFT JOIN "direct_voter"
   2.290 -  ON "issue"."id" = "direct_voter"."issue_id"
   2.291 -  JOIN "battle_participant" AS "winning_initiative"
   2.292 -    ON "issue"."id" = "winning_initiative"."issue_id"
   2.293 -  JOIN "battle_participant" AS "losing_initiative"
   2.294 -    ON "issue"."id" = "losing_initiative"."issue_id"
   2.295 -  LEFT JOIN "vote" AS "better_vote"
   2.296 -    ON "direct_voter"."member_id" = "better_vote"."member_id"
   2.297 -    AND "winning_initiative"."id" = "better_vote"."initiative_id"
   2.298 -  LEFT JOIN "vote" AS "worse_vote"
   2.299 -    ON "direct_voter"."member_id" = "worse_vote"."member_id"
   2.300 -    AND "losing_initiative"."id" = "worse_vote"."initiative_id"
   2.301 -  WHERE "issue"."closed" NOTNULL
   2.302 -  AND "issue"."cleaned" ISNULL
   2.303 -  AND (
   2.304 -    "winning_initiative"."id" != "losing_initiative"."id" OR
   2.305 -    ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
   2.306 -      ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
   2.307 -  GROUP BY
   2.308 -    "issue"."id",
   2.309 -    "winning_initiative"."id",
   2.310 -    "losing_initiative"."id";
   2.311 -
   2.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';
   2.313 -
   2.314 -DROP FUNCTION "check_last_login"();
   2.315 -
   2.316 -CREATE FUNCTION "check_activity"()
   2.317 -  RETURNS VOID
   2.318 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.319 -    DECLARE
   2.320 -      "system_setting_row" "system_setting"%ROWTYPE;
   2.321 -    BEGIN
   2.322 -      SELECT * INTO "system_setting_row" FROM "system_setting";
   2.323 -      LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
   2.324 -      IF "system_setting_row"."member_ttl" NOTNULL THEN
   2.325 -        UPDATE "member" SET "active" = FALSE
   2.326 -          WHERE "active" = TRUE
   2.327 -          AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
   2.328 -      END IF;
   2.329 -      RETURN;
   2.330 -    END;
   2.331 -  $$;
   2.332 -
   2.333 -COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
   2.334 -
   2.335 -CREATE OR REPLACE FUNCTION "calculate_member_counts"()
   2.336 -  RETURNS VOID
   2.337 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.338 -    BEGIN
   2.339 -      LOCK TABLE "member"       IN SHARE MODE;
   2.340 -      LOCK TABLE "member_count" IN EXCLUSIVE MODE;
   2.341 -      LOCK TABLE "unit"         IN EXCLUSIVE MODE;
   2.342 -      LOCK TABLE "area"         IN EXCLUSIVE MODE;
   2.343 -      LOCK TABLE "privilege"    IN SHARE MODE;
   2.344 -      LOCK TABLE "membership"   IN SHARE MODE;
   2.345 -      DELETE FROM "member_count";
   2.346 -      INSERT INTO "member_count" ("total_count")
   2.347 -        SELECT "total_count" FROM "member_count_view";
   2.348 -      UPDATE "unit" SET "member_count" = "view"."member_count"
   2.349 -        FROM "unit_member_count" AS "view"
   2.350 -        WHERE "view"."unit_id" = "unit"."id";
   2.351 -      UPDATE "area" SET
   2.352 -        "direct_member_count" = "view"."direct_member_count",
   2.353 -        "member_weight"       = "view"."member_weight"
   2.354 -        FROM "area_member_count" AS "view"
   2.355 -        WHERE "view"."area_id" = "area"."id";
   2.356 -      RETURN;
   2.357 -    END;
   2.358 -  $$;
   2.359 -
   2.360 -CREATE OR REPLACE FUNCTION "create_interest_snapshot"
   2.361 -  ( "issue_id_p" "issue"."id"%TYPE )
   2.362 -  RETURNS VOID
   2.363 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.364 -    DECLARE
   2.365 -      "member_id_v" "member"."id"%TYPE;
   2.366 -    BEGIN
   2.367 -      DELETE FROM "direct_interest_snapshot"
   2.368 -        WHERE "issue_id" = "issue_id_p"
   2.369 -        AND "event" = 'periodic';
   2.370 -      DELETE FROM "delegating_interest_snapshot"
   2.371 -        WHERE "issue_id" = "issue_id_p"
   2.372 -        AND "event" = 'periodic';
   2.373 -      DELETE FROM "direct_supporter_snapshot"
   2.374 -        WHERE "issue_id" = "issue_id_p"
   2.375 -        AND "event" = 'periodic';
   2.376 -      INSERT INTO "direct_interest_snapshot"
   2.377 -        ("issue_id", "event", "member_id")
   2.378 -        SELECT
   2.379 -          "issue_id_p"  AS "issue_id",
   2.380 -          'periodic'    AS "event",
   2.381 -          "member"."id" AS "member_id"
   2.382 -        FROM "issue"
   2.383 -        JOIN "area" ON "issue"."area_id" = "area"."id"
   2.384 -        JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   2.385 -        JOIN "member" ON "interest"."member_id" = "member"."id"
   2.386 -        JOIN "privilege"
   2.387 -          ON "privilege"."unit_id" = "area"."unit_id"
   2.388 -          AND "privilege"."member_id" = "member"."id"
   2.389 -        WHERE "issue"."id" = "issue_id_p"
   2.390 -        AND "member"."active" AND "privilege"."voting_right";
   2.391 -      FOR "member_id_v" IN
   2.392 -        SELECT "member_id" FROM "direct_interest_snapshot"
   2.393 -        WHERE "issue_id" = "issue_id_p"
   2.394 -        AND "event" = 'periodic'
   2.395 -      LOOP
   2.396 -        UPDATE "direct_interest_snapshot" SET
   2.397 -          "weight" = 1 +
   2.398 -            "weight_of_added_delegations_for_interest_snapshot"(
   2.399 -              "issue_id_p",
   2.400 -              "member_id_v",
   2.401 -              '{}'
   2.402 -            )
   2.403 -          WHERE "issue_id" = "issue_id_p"
   2.404 -          AND "event" = 'periodic'
   2.405 -          AND "member_id" = "member_id_v";
   2.406 -      END LOOP;
   2.407 -      INSERT INTO "direct_supporter_snapshot"
   2.408 -        ( "issue_id", "initiative_id", "event", "member_id",
   2.409 -          "informed", "satisfied" )
   2.410 -        SELECT
   2.411 -          "issue_id_p"            AS "issue_id",
   2.412 -          "initiative"."id"       AS "initiative_id",
   2.413 -          'periodic'              AS "event",
   2.414 -          "supporter"."member_id" AS "member_id",
   2.415 -          "supporter"."draft_id" = "current_draft"."id" AS "informed",
   2.416 -          NOT EXISTS (
   2.417 -            SELECT NULL FROM "critical_opinion"
   2.418 -            WHERE "initiative_id" = "initiative"."id"
   2.419 -            AND "member_id" = "supporter"."member_id"
   2.420 -          ) AS "satisfied"
   2.421 -        FROM "initiative"
   2.422 -        JOIN "supporter"
   2.423 -        ON "supporter"."initiative_id" = "initiative"."id"
   2.424 -        JOIN "current_draft"
   2.425 -        ON "initiative"."id" = "current_draft"."initiative_id"
   2.426 -        JOIN "direct_interest_snapshot"
   2.427 -        ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
   2.428 -        AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
   2.429 -        AND "event" = 'periodic'
   2.430 -        WHERE "initiative"."issue_id" = "issue_id_p";
   2.431 -      RETURN;
   2.432 -    END;
   2.433 -  $$;
   2.434 -
   2.435 -CREATE OR REPLACE FUNCTION "create_snapshot"
   2.436 -  ( "issue_id_p" "issue"."id"%TYPE )
   2.437 -  RETURNS VOID
   2.438 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.439 -    DECLARE
   2.440 -      "initiative_id_v"    "initiative"."id"%TYPE;
   2.441 -      "suggestion_id_v"    "suggestion"."id"%TYPE;
   2.442 -    BEGIN
   2.443 -      PERFORM "lock_issue"("issue_id_p");
   2.444 -      PERFORM "create_population_snapshot"("issue_id_p");
   2.445 -      PERFORM "create_interest_snapshot"("issue_id_p");
   2.446 -      UPDATE "issue" SET
   2.447 -        "snapshot" = now(),
   2.448 -        "latest_snapshot_event" = 'periodic',
   2.449 -        "population" = (
   2.450 -          SELECT coalesce(sum("weight"), 0)
   2.451 -          FROM "direct_population_snapshot"
   2.452 -          WHERE "issue_id" = "issue_id_p"
   2.453 -          AND "event" = 'periodic'
   2.454 -        )
   2.455 -        WHERE "id" = "issue_id_p";
   2.456 -      FOR "initiative_id_v" IN
   2.457 -        SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
   2.458 -      LOOP
   2.459 -        UPDATE "initiative" SET
   2.460 -          "supporter_count" = (
   2.461 -            SELECT coalesce(sum("di"."weight"), 0)
   2.462 -            FROM "direct_interest_snapshot" AS "di"
   2.463 -            JOIN "direct_supporter_snapshot" AS "ds"
   2.464 -            ON "di"."member_id" = "ds"."member_id"
   2.465 -            WHERE "di"."issue_id" = "issue_id_p"
   2.466 -            AND "di"."event" = 'periodic'
   2.467 -            AND "ds"."initiative_id" = "initiative_id_v"
   2.468 -            AND "ds"."event" = 'periodic'
   2.469 -          ),
   2.470 -          "informed_supporter_count" = (
   2.471 -            SELECT coalesce(sum("di"."weight"), 0)
   2.472 -            FROM "direct_interest_snapshot" AS "di"
   2.473 -            JOIN "direct_supporter_snapshot" AS "ds"
   2.474 -            ON "di"."member_id" = "ds"."member_id"
   2.475 -            WHERE "di"."issue_id" = "issue_id_p"
   2.476 -            AND "di"."event" = 'periodic'
   2.477 -            AND "ds"."initiative_id" = "initiative_id_v"
   2.478 -            AND "ds"."event" = 'periodic'
   2.479 -            AND "ds"."informed"
   2.480 -          ),
   2.481 -          "satisfied_supporter_count" = (
   2.482 -            SELECT coalesce(sum("di"."weight"), 0)
   2.483 -            FROM "direct_interest_snapshot" AS "di"
   2.484 -            JOIN "direct_supporter_snapshot" AS "ds"
   2.485 -            ON "di"."member_id" = "ds"."member_id"
   2.486 -            WHERE "di"."issue_id" = "issue_id_p"
   2.487 -            AND "di"."event" = 'periodic'
   2.488 -            AND "ds"."initiative_id" = "initiative_id_v"
   2.489 -            AND "ds"."event" = 'periodic'
   2.490 -            AND "ds"."satisfied"
   2.491 -          ),
   2.492 -          "satisfied_informed_supporter_count" = (
   2.493 -            SELECT coalesce(sum("di"."weight"), 0)
   2.494 -            FROM "direct_interest_snapshot" AS "di"
   2.495 -            JOIN "direct_supporter_snapshot" AS "ds"
   2.496 -            ON "di"."member_id" = "ds"."member_id"
   2.497 -            WHERE "di"."issue_id" = "issue_id_p"
   2.498 -            AND "di"."event" = 'periodic'
   2.499 -            AND "ds"."initiative_id" = "initiative_id_v"
   2.500 -            AND "ds"."event" = 'periodic'
   2.501 -            AND "ds"."informed"
   2.502 -            AND "ds"."satisfied"
   2.503 -          )
   2.504 -          WHERE "id" = "initiative_id_v";
   2.505 -        FOR "suggestion_id_v" IN
   2.506 -          SELECT "id" FROM "suggestion"
   2.507 -          WHERE "initiative_id" = "initiative_id_v"
   2.508 -        LOOP
   2.509 -          UPDATE "suggestion" SET
   2.510 -            "minus2_unfulfilled_count" = (
   2.511 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.512 -              FROM "issue" CROSS JOIN "opinion"
   2.513 -              JOIN "direct_interest_snapshot" AS "snapshot"
   2.514 -              ON "snapshot"."issue_id" = "issue"."id"
   2.515 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.516 -              AND "snapshot"."member_id" = "opinion"."member_id"
   2.517 -              WHERE "issue"."id" = "issue_id_p"
   2.518 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.519 -              AND "opinion"."degree" = -2
   2.520 -              AND "opinion"."fulfilled" = FALSE
   2.521 -            ),
   2.522 -            "minus2_fulfilled_count" = (
   2.523 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.524 -              FROM "issue" CROSS JOIN "opinion"
   2.525 -              JOIN "direct_interest_snapshot" AS "snapshot"
   2.526 -              ON "snapshot"."issue_id" = "issue"."id"
   2.527 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.528 -              AND "snapshot"."member_id" = "opinion"."member_id"
   2.529 -              WHERE "issue"."id" = "issue_id_p"
   2.530 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.531 -              AND "opinion"."degree" = -2
   2.532 -              AND "opinion"."fulfilled" = TRUE
   2.533 -            ),
   2.534 -            "minus1_unfulfilled_count" = (
   2.535 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.536 -              FROM "issue" CROSS JOIN "opinion"
   2.537 -              JOIN "direct_interest_snapshot" AS "snapshot"
   2.538 -              ON "snapshot"."issue_id" = "issue"."id"
   2.539 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.540 -              AND "snapshot"."member_id" = "opinion"."member_id"
   2.541 -              WHERE "issue"."id" = "issue_id_p"
   2.542 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.543 -              AND "opinion"."degree" = -1
   2.544 -              AND "opinion"."fulfilled" = FALSE
   2.545 -            ),
   2.546 -            "minus1_fulfilled_count" = (
   2.547 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.548 -              FROM "issue" CROSS JOIN "opinion"
   2.549 -              JOIN "direct_interest_snapshot" AS "snapshot"
   2.550 -              ON "snapshot"."issue_id" = "issue"."id"
   2.551 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.552 -              AND "snapshot"."member_id" = "opinion"."member_id"
   2.553 -              WHERE "issue"."id" = "issue_id_p"
   2.554 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.555 -              AND "opinion"."degree" = -1
   2.556 -              AND "opinion"."fulfilled" = TRUE
   2.557 -            ),
   2.558 -            "plus1_unfulfilled_count" = (
   2.559 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.560 -              FROM "issue" CROSS JOIN "opinion"
   2.561 -              JOIN "direct_interest_snapshot" AS "snapshot"
   2.562 -              ON "snapshot"."issue_id" = "issue"."id"
   2.563 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.564 -              AND "snapshot"."member_id" = "opinion"."member_id"
   2.565 -              WHERE "issue"."id" = "issue_id_p"
   2.566 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.567 -              AND "opinion"."degree" = 1
   2.568 -              AND "opinion"."fulfilled" = FALSE
   2.569 -            ),
   2.570 -            "plus1_fulfilled_count" = (
   2.571 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.572 -              FROM "issue" CROSS JOIN "opinion"
   2.573 -              JOIN "direct_interest_snapshot" AS "snapshot"
   2.574 -              ON "snapshot"."issue_id" = "issue"."id"
   2.575 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.576 -              AND "snapshot"."member_id" = "opinion"."member_id"
   2.577 -              WHERE "issue"."id" = "issue_id_p"
   2.578 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.579 -              AND "opinion"."degree" = 1
   2.580 -              AND "opinion"."fulfilled" = TRUE
   2.581 -            ),
   2.582 -            "plus2_unfulfilled_count" = (
   2.583 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.584 -              FROM "issue" CROSS JOIN "opinion"
   2.585 -              JOIN "direct_interest_snapshot" AS "snapshot"
   2.586 -              ON "snapshot"."issue_id" = "issue"."id"
   2.587 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.588 -              AND "snapshot"."member_id" = "opinion"."member_id"
   2.589 -              WHERE "issue"."id" = "issue_id_p"
   2.590 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.591 -              AND "opinion"."degree" = 2
   2.592 -              AND "opinion"."fulfilled" = FALSE
   2.593 -            ),
   2.594 -            "plus2_fulfilled_count" = (
   2.595 -              SELECT coalesce(sum("snapshot"."weight"), 0)
   2.596 -              FROM "issue" CROSS JOIN "opinion"
   2.597 -              JOIN "direct_interest_snapshot" AS "snapshot"
   2.598 -              ON "snapshot"."issue_id" = "issue"."id"
   2.599 -              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   2.600 -              AND "snapshot"."member_id" = "opinion"."member_id"
   2.601 -              WHERE "issue"."id" = "issue_id_p"
   2.602 -              AND "opinion"."suggestion_id" = "suggestion_id_v"
   2.603 -              AND "opinion"."degree" = 2
   2.604 -              AND "opinion"."fulfilled" = TRUE
   2.605 -            )
   2.606 -            WHERE "suggestion"."id" = "suggestion_id_v";
   2.607 -        END LOOP;
   2.608 -      END LOOP;
   2.609 -      RETURN;
   2.610 -    END;
   2.611 -  $$;
   2.612 -
   2.613 -CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
   2.614 -  RETURNS VOID
   2.615 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.616 -    DECLARE
   2.617 -      "area_id_v"   "area"."id"%TYPE;
   2.618 -      "unit_id_v"   "unit"."id"%TYPE;
   2.619 -      "member_id_v" "member"."id"%TYPE;
   2.620 -    BEGIN
   2.621 -      PERFORM "lock_issue"("issue_id_p");
   2.622 -      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   2.623 -      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   2.624 -      -- delete delegating votes (in cases of manual reset of issue state):
   2.625 -      DELETE FROM "delegating_voter"
   2.626 -        WHERE "issue_id" = "issue_id_p";
   2.627 -      -- delete votes from non-privileged voters:
   2.628 -      DELETE FROM "direct_voter"
   2.629 -        USING (
   2.630 -          SELECT
   2.631 -            "direct_voter"."member_id"
   2.632 -          FROM "direct_voter"
   2.633 -          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   2.634 -          LEFT JOIN "privilege"
   2.635 -          ON "privilege"."unit_id" = "unit_id_v"
   2.636 -          AND "privilege"."member_id" = "direct_voter"."member_id"
   2.637 -          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   2.638 -            "member"."active" = FALSE OR
   2.639 -            "privilege"."voting_right" ISNULL OR
   2.640 -            "privilege"."voting_right" = FALSE
   2.641 -          )
   2.642 -        ) AS "subquery"
   2.643 -        WHERE "direct_voter"."issue_id" = "issue_id_p"
   2.644 -        AND "direct_voter"."member_id" = "subquery"."member_id";
   2.645 -      -- consider delegations:
   2.646 -      UPDATE "direct_voter" SET "weight" = 1
   2.647 -        WHERE "issue_id" = "issue_id_p";
   2.648 -      PERFORM "add_vote_delegations"("issue_id_p");
   2.649 -      -- set voter count and mark issue as being calculated:
   2.650 -      UPDATE "issue" SET
   2.651 -        "state"  = 'calculation',
   2.652 -        "closed" = now(),
   2.653 -        "voter_count" = (
   2.654 -          SELECT coalesce(sum("weight"), 0)
   2.655 -          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   2.656 -        )
   2.657 -        WHERE "id" = "issue_id_p";
   2.658 -      -- materialize battle_view:
   2.659 -      -- NOTE: "closed" column of issue must be set at this point
   2.660 -      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   2.661 -      INSERT INTO "battle" (
   2.662 -        "issue_id",
   2.663 -        "winning_initiative_id", "losing_initiative_id",
   2.664 -        "count"
   2.665 -      ) SELECT
   2.666 -        "issue_id",
   2.667 -        "winning_initiative_id", "losing_initiative_id",
   2.668 -        "count"
   2.669 -        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   2.670 -      -- copy "positive_votes" and "negative_votes" from "battle" table:
   2.671 -      UPDATE "initiative" SET
   2.672 -        "positive_votes" = "battle_win"."count",
   2.673 -        "negative_votes" = "battle_lose"."count"
   2.674 -        FROM "battle" AS "battle_win", "battle" AS "battle_lose"
   2.675 -        WHERE
   2.676 -          "battle_win"."issue_id" = "issue_id_p" AND
   2.677 -          "battle_win"."winning_initiative_id" = "initiative"."id" AND
   2.678 -          "battle_win"."losing_initiative_id" ISNULL AND
   2.679 -          "battle_lose"."issue_id" = "issue_id_p" AND
   2.680 -          "battle_lose"."losing_initiative_id" = "initiative"."id" AND
   2.681 -          "battle_lose"."winning_initiative_id" ISNULL;
   2.682 -    END;
   2.683 -  $$;
   2.684 -
   2.685 -DROP FUNCTION "array_init_string"(INTEGER);
   2.686 -DROP FUNCTION "square_matrix_init_string"(INTEGER);
   2.687 -
   2.688 -CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
   2.689 -  RETURNS VOID
   2.690 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.691 -    DECLARE
   2.692 -      "issue_row"         "issue"%ROWTYPE;
   2.693 -      "policy_row"        "policy"%ROWTYPE;
   2.694 -      "dimension_v"       INTEGER;
   2.695 -      "vote_matrix"       INT4[][];  -- absolute votes
   2.696 -      "matrix"            INT8[][];  -- defeat strength / best paths
   2.697 -      "i"                 INTEGER;
   2.698 -      "j"                 INTEGER;
   2.699 -      "k"                 INTEGER;
   2.700 -      "battle_row"        "battle"%ROWTYPE;
   2.701 -      "rank_ary"          INT4[];
   2.702 -      "rank_v"            INT4;
   2.703 -      "done_v"            INTEGER;
   2.704 -      "winners_ary"       INTEGER[];
   2.705 -      "initiative_id_v"   "initiative"."id"%TYPE;
   2.706 -    BEGIN
   2.707 -      SELECT * INTO "issue_row"
   2.708 -        FROM "issue" WHERE "id" = "issue_id_p"
   2.709 -        FOR UPDATE;
   2.710 -      SELECT * INTO "policy_row"
   2.711 -        FROM "policy" WHERE "id" = "issue_row"."policy_id";
   2.712 -      SELECT count(1) INTO "dimension_v"
   2.713 -        FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
   2.714 -      -- Create "vote_matrix" with absolute number of votes in pairwise
   2.715 -      -- comparison:
   2.716 -      "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
   2.717 -      "i" := 1;
   2.718 -      "j" := 2;
   2.719 -      FOR "battle_row" IN
   2.720 -        SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
   2.721 -        ORDER BY
   2.722 -        "winning_initiative_id" NULLS LAST,
   2.723 -        "losing_initiative_id" NULLS LAST
   2.724 -      LOOP
   2.725 -        "vote_matrix"["i"]["j"] := "battle_row"."count";
   2.726 -        IF "j" = "dimension_v" THEN
   2.727 -          "i" := "i" + 1;
   2.728 -          "j" := 1;
   2.729 -        ELSE
   2.730 -          "j" := "j" + 1;
   2.731 -          IF "j" = "i" THEN
   2.732 -            "j" := "j" + 1;
   2.733 -          END IF;
   2.734 -        END IF;
   2.735 -      END LOOP;
   2.736 -      IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
   2.737 -        RAISE EXCEPTION 'Wrong battle count (should not happen)';
   2.738 -      END IF;
   2.739 -      -- Store defeat strengths in "matrix" using "defeat_strength"
   2.740 -      -- function:
   2.741 -      "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
   2.742 -      "i" := 1;
   2.743 -      LOOP
   2.744 -        "j" := 1;
   2.745 -        LOOP
   2.746 -          IF "i" != "j" THEN
   2.747 -            "matrix"["i"]["j"] := "defeat_strength"(
   2.748 -              "vote_matrix"["i"]["j"],
   2.749 -              "vote_matrix"["j"]["i"]
   2.750 -            );
   2.751 -          END IF;
   2.752 -          EXIT WHEN "j" = "dimension_v";
   2.753 -          "j" := "j" + 1;
   2.754 -        END LOOP;
   2.755 -        EXIT WHEN "i" = "dimension_v";
   2.756 -        "i" := "i" + 1;
   2.757 -      END LOOP;
   2.758 -      -- Find best paths:
   2.759 -      "i" := 1;
   2.760 -      LOOP
   2.761 -        "j" := 1;
   2.762 -        LOOP
   2.763 -          IF "i" != "j" THEN
   2.764 -            "k" := 1;
   2.765 -            LOOP
   2.766 -              IF "i" != "k" AND "j" != "k" THEN
   2.767 -                IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
   2.768 -                  IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
   2.769 -                    "matrix"["j"]["k"] := "matrix"["j"]["i"];
   2.770 -                  END IF;
   2.771 -                ELSE
   2.772 -                  IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
   2.773 -                    "matrix"["j"]["k"] := "matrix"["i"]["k"];
   2.774 -                  END IF;
   2.775 -                END IF;
   2.776 -              END IF;
   2.777 -              EXIT WHEN "k" = "dimension_v";
   2.778 -              "k" := "k" + 1;
   2.779 -            END LOOP;
   2.780 -          END IF;
   2.781 -          EXIT WHEN "j" = "dimension_v";
   2.782 -          "j" := "j" + 1;
   2.783 -        END LOOP;
   2.784 -        EXIT WHEN "i" = "dimension_v";
   2.785 -        "i" := "i" + 1;
   2.786 -      END LOOP;
   2.787 -      -- Determine order of winners:
   2.788 -      "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
   2.789 -      "rank_v" := 1;
   2.790 -      "done_v" := 0;
   2.791 -      LOOP
   2.792 -        "winners_ary" := '{}';
   2.793 -        "i" := 1;
   2.794 -        LOOP
   2.795 -          IF "rank_ary"["i"] ISNULL THEN
   2.796 -            "j" := 1;
   2.797 -            LOOP
   2.798 -              IF
   2.799 -                "i" != "j" AND
   2.800 -                "rank_ary"["j"] ISNULL AND
   2.801 -                "matrix"["j"]["i"] > "matrix"["i"]["j"]
   2.802 -              THEN
   2.803 -                -- someone else is better
   2.804 -                EXIT;
   2.805 -              END IF;
   2.806 -              IF "j" = "dimension_v" THEN
   2.807 -                -- noone is better
   2.808 -                "winners_ary" := "winners_ary" || "i";
   2.809 -                EXIT;
   2.810 -              END IF;
   2.811 -              "j" := "j" + 1;
   2.812 -            END LOOP;
   2.813 -          END IF;
   2.814 -          EXIT WHEN "i" = "dimension_v";
   2.815 -          "i" := "i" + 1;
   2.816 -        END LOOP;
   2.817 -        "i" := 1;
   2.818 -        LOOP
   2.819 -          "rank_ary"["winners_ary"["i"]] := "rank_v";
   2.820 -          "done_v" := "done_v" + 1;
   2.821 -          EXIT WHEN "i" = array_upper("winners_ary", 1);
   2.822 -          "i" := "i" + 1;
   2.823 -        END LOOP;
   2.824 -        EXIT WHEN "done_v" = "dimension_v";
   2.825 -        "rank_v" := "rank_v" + 1;
   2.826 -      END LOOP;
   2.827 -      -- write preliminary results:
   2.828 -      "i" := 1;
   2.829 -      FOR "initiative_id_v" IN
   2.830 -        SELECT "id" FROM "initiative"
   2.831 -        WHERE "issue_id" = "issue_id_p" AND "admitted"
   2.832 -        ORDER BY "id"
   2.833 -      LOOP
   2.834 -        UPDATE "initiative" SET
   2.835 -          "direct_majority" =
   2.836 -            CASE WHEN "policy_row"."direct_majority_strict" THEN
   2.837 -              "positive_votes" * "policy_row"."direct_majority_den" >
   2.838 -              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   2.839 -            ELSE
   2.840 -              "positive_votes" * "policy_row"."direct_majority_den" >=
   2.841 -              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   2.842 -            END
   2.843 -            AND "positive_votes" >= "policy_row"."direct_majority_positive"
   2.844 -            AND "issue_row"."voter_count"-"negative_votes" >=
   2.845 -                "policy_row"."direct_majority_non_negative",
   2.846 -            "indirect_majority" =
   2.847 -            CASE WHEN "policy_row"."indirect_majority_strict" THEN
   2.848 -              "positive_votes" * "policy_row"."indirect_majority_den" >
   2.849 -              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   2.850 -            ELSE
   2.851 -              "positive_votes" * "policy_row"."indirect_majority_den" >=
   2.852 -              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   2.853 -            END
   2.854 -            AND "positive_votes" >= "policy_row"."indirect_majority_positive"
   2.855 -            AND "issue_row"."voter_count"-"negative_votes" >=
   2.856 -                "policy_row"."indirect_majority_non_negative",
   2.857 -          "schulze_rank"           = "rank_ary"["i"],
   2.858 -          "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
   2.859 -          "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"["dimension_v"],
   2.860 -          "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
   2.861 -          "reverse_beat_path"      = "matrix"["dimension_v"]["i"] >= 0,
   2.862 -          "winner"                 = FALSE
   2.863 -          WHERE "id" = "initiative_id_v";
   2.864 -        "i" := "i" + 1;
   2.865 -      END LOOP;
   2.866 -      IF "i" != "dimension_v" THEN
   2.867 -        RAISE EXCEPTION 'Wrong winner count (should not happen)';
   2.868 -      END IF;
   2.869 -      -- take indirect majorities into account:
   2.870 -      LOOP
   2.871 -        UPDATE "initiative" SET "indirect_majority" = TRUE
   2.872 -          FROM (
   2.873 -            SELECT "new_initiative"."id" AS "initiative_id"
   2.874 -            FROM "initiative" "old_initiative"
   2.875 -            JOIN "initiative" "new_initiative"
   2.876 -              ON "new_initiative"."issue_id" = "issue_id_p"
   2.877 -              AND "new_initiative"."indirect_majority" = FALSE
   2.878 -            JOIN "battle" "battle_win"
   2.879 -              ON "battle_win"."issue_id" = "issue_id_p"
   2.880 -              AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
   2.881 -              AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
   2.882 -            JOIN "battle" "battle_lose"
   2.883 -              ON "battle_lose"."issue_id" = "issue_id_p"
   2.884 -              AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
   2.885 -              AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
   2.886 -            WHERE "old_initiative"."issue_id" = "issue_id_p"
   2.887 -            AND "old_initiative"."indirect_majority" = TRUE
   2.888 -            AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
   2.889 -              "battle_win"."count" * "policy_row"."indirect_majority_den" >
   2.890 -              "policy_row"."indirect_majority_num" *
   2.891 -              ("battle_win"."count"+"battle_lose"."count")
   2.892 -            ELSE
   2.893 -              "battle_win"."count" * "policy_row"."indirect_majority_den" >=
   2.894 -              "policy_row"."indirect_majority_num" *
   2.895 -              ("battle_win"."count"+"battle_lose"."count")
   2.896 -            END
   2.897 -            AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
   2.898 -            AND "issue_row"."voter_count"-"battle_lose"."count" >=
   2.899 -                "policy_row"."indirect_majority_non_negative"
   2.900 -          ) AS "subquery"
   2.901 -          WHERE "id" = "subquery"."initiative_id";
   2.902 -        EXIT WHEN NOT FOUND;
   2.903 -      END LOOP;
   2.904 -      -- set "multistage_majority" for remaining matching initiatives:
   2.905 -       UPDATE "initiative" SET "multistage_majority" = TRUE
   2.906 -        FROM (
   2.907 -          SELECT "losing_initiative"."id" AS "initiative_id"
   2.908 -          FROM "initiative" "losing_initiative"
   2.909 -          JOIN "initiative" "winning_initiative"
   2.910 -            ON "winning_initiative"."issue_id" = "issue_id_p"
   2.911 -            AND "winning_initiative"."admitted"
   2.912 -          JOIN "battle" "battle_win"
   2.913 -            ON "battle_win"."issue_id" = "issue_id_p"
   2.914 -            AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
   2.915 -            AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
   2.916 -          JOIN "battle" "battle_lose"
   2.917 -            ON "battle_lose"."issue_id" = "issue_id_p"
   2.918 -            AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
   2.919 -            AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
   2.920 -          WHERE "losing_initiative"."issue_id" = "issue_id_p"
   2.921 -          AND "losing_initiative"."admitted"
   2.922 -          AND "winning_initiative"."schulze_rank" <
   2.923 -              "losing_initiative"."schulze_rank"
   2.924 -          AND "battle_win"."count" > "battle_lose"."count"
   2.925 -          AND (
   2.926 -            "battle_win"."count" > "winning_initiative"."positive_votes" OR
   2.927 -            "battle_lose"."count" < "losing_initiative"."negative_votes" )
   2.928 -        ) AS "subquery"
   2.929 -        WHERE "id" = "subquery"."initiative_id";
   2.930 -      -- mark eligible initiatives:
   2.931 -      UPDATE "initiative" SET "eligible" = TRUE
   2.932 -        WHERE "issue_id" = "issue_id_p"
   2.933 -        AND "initiative"."direct_majority"
   2.934 -        AND "initiative"."indirect_majority"
   2.935 -        AND "initiative"."better_than_status_quo"
   2.936 -        AND (
   2.937 -          "policy_row"."no_multistage_majority" = FALSE OR
   2.938 -          "initiative"."multistage_majority" = FALSE )
   2.939 -        AND (
   2.940 -          "policy_row"."no_reverse_beat_path" = FALSE OR
   2.941 -          "initiative"."reverse_beat_path" = FALSE );
   2.942 -      -- mark final winner:
   2.943 -      UPDATE "initiative" SET "winner" = TRUE
   2.944 -        FROM (
   2.945 -          SELECT "id" AS "initiative_id"
   2.946 -          FROM "initiative"
   2.947 -          WHERE "issue_id" = "issue_id_p" AND "eligible"
   2.948 -          ORDER BY "schulze_rank", "id"
   2.949 -          LIMIT 1
   2.950 -        ) AS "subquery"
   2.951 -        WHERE "id" = "subquery"."initiative_id";
   2.952 -      -- write (final) ranks:
   2.953 -      "rank_v" := 1;
   2.954 -      FOR "initiative_id_v" IN
   2.955 -        SELECT "id"
   2.956 -        FROM "initiative"
   2.957 -        WHERE "issue_id" = "issue_id_p" AND "admitted"
   2.958 -        ORDER BY
   2.959 -          "winner" DESC,
   2.960 -          ("direct_majority" AND "indirect_majority") DESC,
   2.961 -          "schulze_rank",
   2.962 -          "id"
   2.963 -      LOOP
   2.964 -        UPDATE "initiative" SET "rank" = "rank_v"
   2.965 -          WHERE "id" = "initiative_id_v";
   2.966 -        "rank_v" := "rank_v" + 1;
   2.967 -      END LOOP;
   2.968 -      -- set schulze rank of status quo and mark issue as finished:
   2.969 -      UPDATE "issue" SET
   2.970 -        "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
   2.971 -        "state" =
   2.972 -          CASE WHEN EXISTS (
   2.973 -            SELECT NULL FROM "initiative"
   2.974 -            WHERE "issue_id" = "issue_id_p" AND "winner"
   2.975 -          ) THEN
   2.976 -            'finished_with_winner'::"issue_state"
   2.977 -          ELSE
   2.978 -            'finished_without_winner'::"issue_state"
   2.979 -          END,
   2.980 -        "ranks_available" = TRUE
   2.981 -        WHERE "id" = "issue_id_p";
   2.982 -      RETURN;
   2.983 -    END;
   2.984 -  $$;
   2.985 -
   2.986 -CREATE OR REPLACE FUNCTION "check_issue"
   2.987 -  ( "issue_id_p" "issue"."id"%TYPE )
   2.988 -  RETURNS VOID
   2.989 -  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.990 -    DECLARE
   2.991 -      "issue_row"         "issue"%ROWTYPE;
   2.992 -      "policy_row"        "policy"%ROWTYPE;
   2.993 -    BEGIN
   2.994 -      PERFORM "lock_issue"("issue_id_p");
   2.995 -      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   2.996 -      -- only process open issues:
   2.997 -      IF "issue_row"."closed" ISNULL THEN
   2.998 -        SELECT * INTO "policy_row" FROM "policy"
   2.999 -          WHERE "id" = "issue_row"."policy_id";
  2.1000 -        -- create a snapshot, unless issue is already fully frozen:
  2.1001 -        IF "issue_row"."fully_frozen" ISNULL THEN
  2.1002 -          PERFORM "create_snapshot"("issue_id_p");
  2.1003 -          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  2.1004 -        END IF;
  2.1005 -        -- eventually close or accept issues, which have not been accepted:
  2.1006 -        IF "issue_row"."accepted" ISNULL THEN
  2.1007 -          IF EXISTS (
  2.1008 -            SELECT NULL FROM "initiative"
  2.1009 -            WHERE "issue_id" = "issue_id_p"
  2.1010 -            AND "supporter_count" > 0
  2.1011 -            AND "supporter_count" * "policy_row"."issue_quorum_den"
  2.1012 -            >= "issue_row"."population" * "policy_row"."issue_quorum_num"
  2.1013 -          ) THEN
  2.1014 -            -- accept issues, if supporter count is high enough
  2.1015 -            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  2.1016 -            -- NOTE: "issue_row" used later
  2.1017 -            "issue_row"."state" := 'discussion';
  2.1018 -            "issue_row"."accepted" := now();
  2.1019 -            UPDATE "issue" SET
  2.1020 -              "state"    = "issue_row"."state",
  2.1021 -              "accepted" = "issue_row"."accepted"
  2.1022 -              WHERE "id" = "issue_row"."id";
  2.1023 -          ELSIF
  2.1024 -            now() >= "issue_row"."created" + "issue_row"."admission_time"
  2.1025 -          THEN
  2.1026 -            -- close issues, if admission time has expired
  2.1027 -            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  2.1028 -            UPDATE "issue" SET
  2.1029 -              "state" = 'canceled_issue_not_accepted',
  2.1030 -              "closed" = now()
  2.1031 -              WHERE "id" = "issue_row"."id";
  2.1032 -          END IF;
  2.1033 -        END IF;
  2.1034 -        -- eventually half freeze issues:
  2.1035 -        IF
  2.1036 -          -- NOTE: issue can't be closed at this point, if it has been accepted
  2.1037 -          "issue_row"."accepted" NOTNULL AND
  2.1038 -          "issue_row"."half_frozen" ISNULL
  2.1039 -        THEN
  2.1040 -          IF
  2.1041 -            now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
  2.1042 -          THEN
  2.1043 -            PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
  2.1044 -            -- NOTE: "issue_row" used later
  2.1045 -            "issue_row"."state" := 'verification';
  2.1046 -            "issue_row"."half_frozen" := now();
  2.1047 -            UPDATE "issue" SET
  2.1048 -              "state"       = "issue_row"."state",
  2.1049 -              "half_frozen" = "issue_row"."half_frozen"
  2.1050 -              WHERE "id" = "issue_row"."id";
  2.1051 -          END IF;
  2.1052 -        END IF;
  2.1053 -        -- close issues after some time, if all initiatives have been revoked:
  2.1054 -        IF
  2.1055 -          "issue_row"."closed" ISNULL AND
  2.1056 -          NOT EXISTS (
  2.1057 -            -- all initiatives are revoked
  2.1058 -            SELECT NULL FROM "initiative"
  2.1059 -            WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
  2.1060 -          ) AND (
  2.1061 -            -- and issue has not been accepted yet
  2.1062 -            "issue_row"."accepted" ISNULL OR
  2.1063 -            NOT EXISTS (
  2.1064 -              -- or no initiatives have been revoked lately
  2.1065 -              SELECT NULL FROM "initiative"
  2.1066 -              WHERE "issue_id" = "issue_id_p"
  2.1067 -              AND now() < "revoked" + "issue_row"."verification_time"
  2.1068 -            ) OR (
  2.1069 -              -- or verification time has elapsed
  2.1070 -              "issue_row"."half_frozen" NOTNULL AND
  2.1071 -              "issue_row"."fully_frozen" ISNULL AND
  2.1072 -              now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  2.1073 -            )
  2.1074 -          )
  2.1075 -        THEN
  2.1076 -          -- NOTE: "issue_row" used later
  2.1077 -          IF "issue_row"."accepted" ISNULL THEN
  2.1078 -            "issue_row"."state" := 'canceled_revoked_before_accepted';
  2.1079 -          ELSIF "issue_row"."half_frozen" ISNULL THEN
  2.1080 -            "issue_row"."state" := 'canceled_after_revocation_during_discussion';
  2.1081 -          ELSE
  2.1082 -            "issue_row"."state" := 'canceled_after_revocation_during_verification';
  2.1083 -          END IF;
  2.1084 -          "issue_row"."closed" := now();
  2.1085 -          UPDATE "issue" SET
  2.1086 -            "state"  = "issue_row"."state",
  2.1087 -            "closed" = "issue_row"."closed"
  2.1088 -            WHERE "id" = "issue_row"."id";
  2.1089 -        END IF;
  2.1090 -        -- fully freeze issue after verification time:
  2.1091 -        IF
  2.1092 -          "issue_row"."half_frozen" NOTNULL AND
  2.1093 -          "issue_row"."fully_frozen" ISNULL AND
  2.1094 -          "issue_row"."closed" ISNULL AND
  2.1095 -          now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  2.1096 -        THEN
  2.1097 -          PERFORM "freeze_after_snapshot"("issue_id_p");
  2.1098 -          -- NOTE: "issue" might change, thus "issue_row" has to be updated below
  2.1099 -        END IF;
  2.1100 -        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  2.1101 -        -- close issue by calling close_voting(...) after voting time:
  2.1102 -        IF
  2.1103 -          "issue_row"."closed" ISNULL AND
  2.1104 -          "issue_row"."fully_frozen" NOTNULL AND
  2.1105 -          now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
  2.1106 -        THEN
  2.1107 -          PERFORM "close_voting"("issue_id_p");
  2.1108 -          -- calculate ranks will not consume much time and can be done now
  2.1109 -          PERFORM "calculate_ranks"("issue_id_p");
  2.1110 -        END IF;
  2.1111 -      END IF;
  2.1112 -      RETURN;
  2.1113 -    END;
  2.1114 -  $$;
  2.1115 -
  2.1116 -CREATE OR REPLACE FUNCTION "check_everything"()
  2.1117 -  RETURNS VOID
  2.1118 -  LANGUAGE 'plpgsql' VOLATILE AS $$
  2.1119 -    DECLARE
  2.1120 -      "issue_id_v" "issue"."id"%TYPE;
  2.1121 -    BEGIN
  2.1122 -      PERFORM "check_activity"();
  2.1123 -      PERFORM "calculate_member_counts"();
  2.1124 -      FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
  2.1125 -        PERFORM "check_issue"("issue_id_v");
  2.1126 -      END LOOP;
  2.1127 -      FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
  2.1128 -        PERFORM "calculate_ranks"("issue_id_v");
  2.1129 -      END LOOP;
  2.1130 -      RETURN;
  2.1131 -    END;
  2.1132 -  $$;
  2.1133 -
  2.1134 -CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
  2.1135 -  RETURNS VOID
  2.1136 -  LANGUAGE 'plpgsql' VOLATILE AS $$
  2.1137 -    BEGIN
  2.1138 -      UPDATE "member" SET
  2.1139 -        "last_login"                   = NULL,
  2.1140 -        "login"                        = NULL,
  2.1141 -        "password"                     = NULL,
  2.1142 -        "locked"                       = TRUE,
  2.1143 -        "active"                       = FALSE,
  2.1144 -        "notify_email"                 = NULL,
  2.1145 -        "notify_email_unconfirmed"     = NULL,
  2.1146 -        "notify_email_secret"          = NULL,
  2.1147 -        "notify_email_secret_expiry"   = NULL,
  2.1148 -        "notify_email_lock_expiry"     = NULL,
  2.1149 -        "password_reset_secret"        = NULL,
  2.1150 -        "password_reset_secret_expiry" = NULL,
  2.1151 -        "organizational_unit"          = NULL,
  2.1152 -        "internal_posts"               = NULL,
  2.1153 -        "realname"                     = NULL,
  2.1154 -        "birthday"                     = NULL,
  2.1155 -        "address"                      = NULL,
  2.1156 -        "email"                        = NULL,
  2.1157 -        "xmpp_address"                 = NULL,
  2.1158 -        "website"                      = NULL,
  2.1159 -        "phone"                        = NULL,
  2.1160 -        "mobile_phone"                 = NULL,
  2.1161 -        "profession"                   = NULL,
  2.1162 -        "external_memberships"         = NULL,
  2.1163 -        "external_posts"               = NULL,
  2.1164 -        "statement"                    = NULL
  2.1165 -        WHERE "id" = "member_id_p";
  2.1166 -      -- "text_search_data" is updated by triggers
  2.1167 -      DELETE FROM "setting"            WHERE "member_id" = "member_id_p";
  2.1168 -      DELETE FROM "setting_map"        WHERE "member_id" = "member_id_p";
  2.1169 -      DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
  2.1170 -      DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
  2.1171 -      DELETE FROM "contact"            WHERE "member_id" = "member_id_p";
  2.1172 -      DELETE FROM "ignored_member"     WHERE "member_id" = "member_id_p";
  2.1173 -      DELETE FROM "area_setting"       WHERE "member_id" = "member_id_p";
  2.1174 -      DELETE FROM "issue_setting"      WHERE "member_id" = "member_id_p";
  2.1175 -      DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
  2.1176 -      DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
  2.1177 -      DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
  2.1178 -      DELETE FROM "membership"         WHERE "member_id" = "member_id_p";
  2.1179 -      DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
  2.1180 -      DELETE FROM "non_voter"          WHERE "member_id" = "member_id_p";
  2.1181 -      DELETE FROM "direct_voter" USING "issue"
  2.1182 -        WHERE "direct_voter"."issue_id" = "issue"."id"
  2.1183 -        AND "issue"."closed" ISNULL
  2.1184 -        AND "member_id" = "member_id_p";
  2.1185 -      RETURN;
  2.1186 -    END;
  2.1187 -  $$;
  2.1188 -
  2.1189 -CREATE OR REPLACE FUNCTION "delete_private_data"()
  2.1190 -  RETURNS VOID
  2.1191 -  LANGUAGE 'plpgsql' VOLATILE AS $$
  2.1192 -    BEGIN
  2.1193 -      UPDATE "member" SET
  2.1194 -        "last_login"                   = NULL,
  2.1195 -        "login"                        = NULL,
  2.1196 -        "password"                     = NULL,
  2.1197 -        "notify_email"                 = NULL,
  2.1198 -        "notify_email_unconfirmed"     = NULL,
  2.1199 -        "notify_email_secret"          = NULL,
  2.1200 -        "notify_email_secret_expiry"   = NULL,
  2.1201 -        "notify_email_lock_expiry"     = NULL,
  2.1202 -        "password_reset_secret"        = NULL,
  2.1203 -        "password_reset_secret_expiry" = NULL,
  2.1204 -        "organizational_unit"          = NULL,
  2.1205 -        "internal_posts"               = NULL,
  2.1206 -        "realname"                     = NULL,
  2.1207 -        "birthday"                     = NULL,
  2.1208 -        "address"                      = NULL,
  2.1209 -        "email"                        = NULL,
  2.1210 -        "xmpp_address"                 = NULL,
  2.1211 -        "website"                      = NULL,
  2.1212 -        "phone"                        = NULL,
  2.1213 -        "mobile_phone"                 = NULL,
  2.1214 -        "profession"                   = NULL,
  2.1215 -        "external_memberships"         = NULL,
  2.1216 -        "external_posts"               = NULL,
  2.1217 -        "statement"                    = NULL;
  2.1218 -      -- "text_search_data" is updated by triggers
  2.1219 -      DELETE FROM "invite_code";
  2.1220 -      DELETE FROM "setting";
  2.1221 -      DELETE FROM "setting_map";
  2.1222 -      DELETE FROM "member_relation_setting";
  2.1223 -      DELETE FROM "member_image";
  2.1224 -      DELETE FROM "contact";
  2.1225 -      DELETE FROM "ignored_member";
  2.1226 -      DELETE FROM "area_setting";
  2.1227 -      DELETE FROM "issue_setting";
  2.1228 -      DELETE FROM "ignored_initiative";
  2.1229 -      DELETE FROM "initiative_setting";
  2.1230 -      DELETE FROM "suggestion_setting";
  2.1231 -      DELETE FROM "non_voter";
  2.1232 -      DELETE FROM "direct_voter" USING "issue"
  2.1233 -        WHERE "direct_voter"."issue_id" = "issue"."id"
  2.1234 -        AND "issue"."closed" ISNULL;
  2.1235 -      RETURN;
  2.1236 -    END;
  2.1237 -  $$;
  2.1238 -
  2.1239 -COMMIT;
  2.1240 -
  2.1241 -BEGIN;
  2.1242 -
  2.1243 -UPDATE "member" SET
  2.1244 -  "activated" = "created",
  2.1245 -  "last_activity" = CASE WHEN "active" THEN
  2.1246 -    coalesce("last_login"::DATE, now())
  2.1247 -  ELSE
  2.1248 -    "last_login"::DATE
  2.1249 -  END;
  2.1250 -
  2.1251 -UPDATE "member" SET
  2.1252 -  "created" = "invite_code"."created",
  2.1253 -  "invite_code" = "invite_code"."code",
  2.1254 -  "admin_comment" = "invite_code"."comment"
  2.1255 -  FROM "invite_code"
  2.1256 -  WHERE "member"."id" = "invite_code"."member_id";
  2.1257 -
  2.1258 -DROP TABLE "invite_code";
  2.1259 -
  2.1260 -UPDATE "initiative" SET
  2.1261 -    "direct_majority"        = "rank" NOTNULL,
  2.1262 -    "indirect_majority"      = "rank" NOTNULL,
  2.1263 -    "schulze_rank"           = "rank",
  2.1264 -    "better_than_status_quo" = "rank" NOTNULL,
  2.1265 -    "worse_than_status_quo"  = "rank" ISNULL,
  2.1266 -    "reverse_beat_path"      = "rank" ISNULL,
  2.1267 -    "multistage_majority"    = "rank" ISNULL,
  2.1268 -    "eligible"               = "rank" NOTNULL,
  2.1269 -    "winner"                 = ("rank" = 1)
  2.1270 -  FROM "issue"
  2.1271 -  WHERE "issue"."id" = "initiative"."issue_id"
  2.1272 -  AND "issue"."state" IN ('finished_without_winner', 'finished_with_winner')
  2.1273 -  AND "initiative"."admitted";
  2.1274 -
  2.1275 -UPDATE "issue" SET "status_quo_schulze_rank" = "subquery"."rank"
  2.1276 -  FROM (
  2.1277 -    SELECT
  2.1278 -      "issue"."id" AS "issue_id",
  2.1279 -      COALESCE(max("initiative"."rank") + 1, 1) AS "rank"
  2.1280 -    FROM "issue" JOIN "initiative"
  2.1281 -    ON "issue"."id" = "initiative"."issue_id"
  2.1282 -    WHERE "issue"."state" IN ('finished_without_winner', 'finished_with_winner')
  2.1283 -    AND "initiative"."admitted"
  2.1284 -    GROUP BY "issue"."id"
  2.1285 -  ) AS "subquery"
  2.1286 -  WHERE "issue"."id" = "subquery"."issue_id";
  2.1287 -
  2.1288 -CREATE FUNCTION "update__set_remaining_ranks"("issue_id_p" "issue"."id"%TYPE)
  2.1289 -  RETURNS VOID
  2.1290 -  LANGUAGE 'plpgsql' AS $$
  2.1291 -    DECLARE
  2.1292 -      "rank_v"          INT4;
  2.1293 -      "initiative_id_v" INT4;
  2.1294 -    BEGIN
  2.1295 -      SELECT "status_quo_schulze_rank" INTO "rank_v"
  2.1296 -        FROM "issue" WHERE "id" = "issue_id_p";
  2.1297 -      FOR "initiative_id_v" IN
  2.1298 -        SELECT "id" FROM "initiative"
  2.1299 -        WHERE "issue_id" = "issue_id_p" AND "admitted" AND "rank" ISNULL
  2.1300 -        ORDER BY "vote_ratio"("positive_votes", "negative_votes") DESC
  2.1301 -      LOOP
  2.1302 -        UPDATE "initiative" SET
  2.1303 -          "schulze_rank" = "rank_v" + 1,
  2.1304 -          "rank"         = "rank_v"
  2.1305 -          WHERE "id" = "initiative_id_v";
  2.1306 -        "rank_v" := "rank_v" + 1;
  2.1307 -      END LOOP;
  2.1308 -      RETURN;
  2.1309 -    END;
  2.1310 -  $$;
  2.1311 -
  2.1312 -SELECT "update__set_remaining_ranks"("id") FROM "issue"
  2.1313 -  WHERE "state" IN ('finished_without_winner', 'finished_with_winner');
  2.1314 -
  2.1315 -DROP FUNCTION "update__set_remaining_ranks"("issue"."id"%TYPE);
  2.1316 -
  2.1317 -UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id"
  2.1318 -  FROM (
  2.1319 -    SELECT DISTINCT ON ("suggestion"."id")
  2.1320 -      "suggestion"."id" AS "suggestion_id",
  2.1321 -      "draft"."id" AS "draft_id"
  2.1322 -    FROM "suggestion" JOIN "draft"
  2.1323 -    ON "suggestion"."initiative_id" = "draft"."initiative_id"
  2.1324 -    WHERE "draft"."created" <= "suggestion"."created"
  2.1325 -    ORDER BY "suggestion"."id", "draft"."created" DESC
  2.1326 -  ) AS "subquery"
  2.1327 -  WHERE "suggestion"."id" = "subquery"."suggestion_id";
  2.1328 -
  2.1329 -COMMIT;
  2.1330 -
  2.1331 -ALTER TABLE "member" ADD CONSTRAINT "active_requires_activated_and_last_activity"
  2.1332 -  CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL));
  2.1333 -ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;
     3.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     3.2 +++ b/update/core-update.v1.4.0_rc4-v2.0.0.sql	Sat Sep 10 22:39:06 2011 +0200
     3.3 @@ -0,0 +1,1330 @@
     3.4 +SELECT "calculate_ranks"("id") FROM "issue_with_ranks_missing";
     3.5 +
     3.6 +BEGIN;
     3.7 +
     3.8 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     3.9 +  SELECT * FROM (VALUES ('2.0.0', 2, 0, 0))
    3.10 +  AS "subquery"("string", "major", "minor", "revision");
    3.11 +
    3.12 +ALTER TABLE "member" ADD COLUMN "invite_code" TEXT UNIQUE;
    3.13 +ALTER TABLE "member" ADD COLUMN "admin_comment" TEXT;
    3.14 +ALTER TABLE "member" ADD COLUMN "activated" TIMESTAMPTZ;
    3.15 +ALTER TABLE "member" ADD COLUMN "last_activity" DATE;
    3.16 +ALTER TABLE "member" DROP COLUMN "last_login_public";
    3.17 +ALTER TABLE "member" ALTER COLUMN "active" SET DEFAULT FALSE;
    3.18 +ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT;
    3.19 +
    3.20 +COMMENT ON COLUMN "member"."created"           IS 'Creation of member record and/or invite code';
    3.21 +COMMENT ON COLUMN "member"."invite_code"       IS 'Optional invite code, to allow a member to initialize his/her account the first time';
    3.22 +COMMENT ON COLUMN "member"."admin_comment"     IS 'Hidden comment for administrative purposes';
    3.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';
    3.24 +COMMENT ON COLUMN "member"."last_activity"     IS 'Date of last activity of member; required to be set for "active" members';
    3.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".';
    3.26 +COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
    3.27 +
    3.28 +CREATE TYPE "application_access_level" AS ENUM
    3.29 +  ('member', 'full', 'pseudonymous', 'anonymous');
    3.30 +
    3.31 +COMMENT ON TYPE "application_access_level" IS 'Access privileges for applications using the API';
    3.32 +
    3.33 +CREATE TABLE "member_application" (
    3.34 +        "id"                    SERIAL8         PRIMARY KEY,
    3.35 +        UNIQUE ("member_id", "name"),
    3.36 +        "member_id"             INT4            NOT NULL REFERENCES "member" ("id")
    3.37 +                                                ON DELETE CASCADE ON UPDATE CASCADE,
    3.38 +        "name"                  TEXT            NOT NULL,
    3.39 +        "comment"               TEXT,
    3.40 +        "access_level" "application_access_level" NOT NULL,
    3.41 +        "key"                   TEXT            NOT NULL UNIQUE,
    3.42 +        "last_usage"            TIMESTAMPTZ );
    3.43 +
    3.44 +COMMENT ON TABLE "member_application" IS 'Registered application being allowed to use the API';
    3.45 +
    3.46 +CREATE TABLE "rendered_member_statement" (
    3.47 +        PRIMARY KEY ("member_id", "format"),
    3.48 +        "member_id"             INT8            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    3.49 +        "format"                TEXT,
    3.50 +        "content"               TEXT            NOT NULL );
    3.51 +
    3.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)';
    3.53 +
    3.54 +DROP VIEW "expired_session";
    3.55 +DROP TABLE "session";
    3.56 +
    3.57 +ALTER TABLE "policy" ADD COLUMN "direct_majority_num"            INT4    NOT NULL DEFAULT 1;
    3.58 +ALTER TABLE "policy" ADD COLUMN "direct_majority_den"            INT4    NOT NULL DEFAULT 2;
    3.59 +ALTER TABLE "policy" ADD COLUMN "direct_majority_strict"         BOOLEAN NOT NULL DEFAULT TRUE;
    3.60 +ALTER TABLE "policy" ADD COLUMN "direct_majority_positive"       INT4    NOT NULL DEFAULT 0;
    3.61 +ALTER TABLE "policy" ADD COLUMN "direct_majority_non_negative"   INT4    NOT NULL DEFAULT 0;
    3.62 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_num"          INT4    NOT NULL DEFAULT 1;
    3.63 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_den"          INT4    NOT NULL DEFAULT 2;
    3.64 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_strict"       BOOLEAN NOT NULL DEFAULT TRUE;
    3.65 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_positive"     INT4    NOT NULL DEFAULT 0;
    3.66 +ALTER TABLE "policy" ADD COLUMN "indirect_majority_non_negative" INT4    NOT NULL DEFAULT 0;
    3.67 +ALTER TABLE "policy" ADD COLUMN "no_reverse_beat_path"           BOOLEAN NOT NULL DEFAULT TRUE;
    3.68 +ALTER TABLE "policy" ADD COLUMN "no_multistage_majority"         BOOLEAN NOT NULL DEFAULT FALSE;
    3.69 +
    3.70 +UPDATE "policy" SET
    3.71 +  "direct_majority_num"      = "majority_num",
    3.72 +  "direct_majority_den"      = "majority_den",
    3.73 +  "direct_majority_strict"   = "majority_strict",
    3.74 +  "indirect_majority_num"    = "majority_num",
    3.75 +  "indirect_majority_den"    = "majority_den",
    3.76 +  "indirect_majority_strict" = "majority_strict";
    3.77 +
    3.78 +ALTER TABLE "policy" DROP COLUMN "majority_num";
    3.79 +ALTER TABLE "policy" DROP COLUMN "majority_den";
    3.80 +ALTER TABLE "policy" DROP COLUMN "majority_strict";
    3.81 +
    3.82 +COMMENT ON COLUMN "policy"."direct_majority_num"            IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
    3.83 +COMMENT ON COLUMN "policy"."direct_majority_den"            IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
    3.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.';
    3.85 +COMMENT ON COLUMN "policy"."direct_majority_positive"       IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
    3.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';
    3.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';
    3.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';
    3.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.';
    3.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';
    3.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';
    3.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.';
    3.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").';
    3.94 +
    3.95 +ALTER TABLE "area" DROP COLUMN "autoreject_weight";
    3.96 +
    3.97 +DROP VIEW "open_issue";
    3.98 +DROP VIEW "issue_with_ranks_missing";
    3.99 +
   3.100 +ALTER TABLE "issue" DROP COLUMN "vote_now";
   3.101 +ALTER TABLE "issue" DROP COLUMN "vote_later";
   3.102 +ALTER TABLE "issue" ADD COLUMN "status_quo_schulze_rank" INT4;
   3.103 +
   3.104 +CREATE VIEW "open_issue" AS
   3.105 +  SELECT * FROM "issue" WHERE "closed" ISNULL;
   3.106 +
   3.107 +COMMENT ON VIEW "open_issue" IS 'All open issues';
   3.108 +
   3.109 +CREATE VIEW "issue_with_ranks_missing" AS
   3.110 +  SELECT * FROM "issue"
   3.111 +  WHERE "fully_frozen" NOTNULL
   3.112 +  AND "closed" NOTNULL
   3.113 +  AND "ranks_available" = FALSE;
   3.114 +
   3.115 +COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
   3.116 +
   3.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.';
   3.118 +COMMENT ON COLUMN "issue"."snapshot"                IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
   3.119 +COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
   3.120 +
   3.121 +DROP VIEW "battle_view";
   3.122 +
   3.123 +ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
   3.124 +ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null";
   3.125 +ALTER TABLE "initiative" DROP CONSTRAINT "non_agreed_initiatives_cant_get_a_rank";
   3.126 +
   3.127 +ALTER TABLE "initiative" DROP COLUMN "agreed";
   3.128 +ALTER TABLE "initiative" ADD COLUMN "direct_majority"        BOOLEAN;
   3.129 +ALTER TABLE "initiative" ADD COLUMN "indirect_majority"      BOOLEAN;
   3.130 +ALTER TABLE "initiative" ADD COLUMN "schulze_rank"           INT4;
   3.131 +ALTER TABLE "initiative" ADD COLUMN "better_than_status_quo" BOOLEAN;
   3.132 +ALTER TABLE "initiative" ADD COLUMN "worse_than_status_quo"  BOOLEAN;
   3.133 +ALTER TABLE "initiative" ADD COLUMN "reverse_beat_path"      BOOLEAN;
   3.134 +ALTER TABLE "initiative" ADD COLUMN "multistage_majority"    BOOLEAN;
   3.135 +ALTER TABLE "initiative" ADD COLUMN "eligible"               BOOLEAN;
   3.136 +ALTER TABLE "initiative" ADD COLUMN "winner"                 BOOLEAN;
   3.137 +
   3.138 +ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
   3.139 +  ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
   3.140 +  ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
   3.141 +    "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
   3.142 +    "schulze_rank" ISNULL AND
   3.143 +    "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
   3.144 +    "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
   3.145 +    "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
   3.146 +ALTER TABLE "initiative" ADD CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo"));
   3.147 +ALTER TABLE "initiative" ADD CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
   3.148 +  "eligible" = FALSE OR
   3.149 +("direct_majority" AND "indirect_majority" AND "better_than_status_quo") );
   3.150 +ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE);
   3.151 +ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1);
   3.152 +ALTER TABLE "initiative" ADD CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE);
   3.153 +ALTER TABLE "initiative" ADD CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank");
   3.154 +
   3.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"';
   3.156 +COMMENT ON COLUMN "initiative"."indirect_majority"       IS 'Same as "direct_majority", but also considering indirect beat paths';
   3.157 +COMMENT ON COLUMN "initiative"."schulze_rank"            IS 'Schulze-Ranking without tie-breaking';
   3.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)';
   3.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)';
   3.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';
   3.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';
   3.162 +COMMENT ON COLUMN "initiative"."eligible"                IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"';
   3.163 +COMMENT ON COLUMN "initiative"."winner"                  IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
   3.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';
   3.165 +
   3.166 +ALTER TABLE "battle" DROP CONSTRAINT "battle_pkey";
   3.167 +ALTER TABLE "battle" ALTER COLUMN "issue_id" SET NOT NULL;
   3.168 +ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK (
   3.169 +  "winning_initiative_id" != "losing_initiative_id" OR
   3.170 +  ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
   3.171 +    ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) );
   3.172 +
   3.173 +CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
   3.174 +CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
   3.175 +CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
   3.176 +
   3.177 +ALTER TABLE "suggestion" ADD COLUMN "draft_id" INT8;
   3.178 +ALTER TABLE "suggestion" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;
   3.179 +ALTER TABLE "suggestion" ADD COLUMN "formatting_engine" TEXT;
   3.180 +ALTER TABLE "suggestion" RENAME COLUMN "description" TO "content";
   3.181 +
   3.182 +DROP TRIGGER "update_text_search_data" ON "suggestion";
   3.183 +
   3.184 +CREATE TRIGGER "update_text_search_data"
   3.185 +  BEFORE INSERT OR UPDATE ON "suggestion"
   3.186 +  FOR EACH ROW EXECUTE PROCEDURE
   3.187 +  tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
   3.188 +    "name", "content");
   3.189 +
   3.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")';
   3.191 +
   3.192 +CREATE TABLE "rendered_suggestion" (
   3.193 +        PRIMARY KEY ("suggestion_id", "format"),
   3.194 +        "suggestion_id"         INT8            REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   3.195 +        "format"                TEXT,
   3.196 +        "content"               TEXT            NOT NULL );
   3.197 +
   3.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)';
   3.199 +
   3.200 +DROP TABLE "invite_code_unit";
   3.201 +
   3.202 +DROP VIEW "area_member_count";
   3.203 +
   3.204 +ALTER TABLE "membership" DROP COLUMN "autoreject";
   3.205 +
   3.206 +ALTER TABLE "interest" DROP COLUMN "autoreject";
   3.207 +ALTER TABLE "interest" DROP COLUMN "voting_requested";
   3.208 +
   3.209 +ALTER TABLE "supporter" DROP CONSTRAINT "supporter_initiative_id_fkey";
   3.210 +ALTER TABLE "supporter" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;
   3.211 +
   3.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")';
   3.213 +
   3.214 +ALTER TABLE "direct_interest_snapshot" DROP COLUMN "voting_requested";
   3.215 +ALTER TABLE "direct_voter" DROP COLUMN "autoreject";
   3.216 +
   3.217 +DROP TRIGGER "default_for_draft_id" ON "supporter";
   3.218 +DROP FUNCTION "supporter_default_for_draft_id_trigger"();
   3.219 +
   3.220 +CREATE FUNCTION "default_for_draft_id_trigger"()
   3.221 +  RETURNS TRIGGER
   3.222 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   3.223 +    BEGIN
   3.224 +      IF NEW."draft_id" ISNULL THEN
   3.225 +        SELECT "id" INTO NEW."draft_id" FROM "current_draft"
   3.226 +          WHERE "initiative_id" = NEW."initiative_id";
   3.227 +      END IF;
   3.228 +      RETURN NEW;
   3.229 +    END;
   3.230 +  $$;
   3.231 +
   3.232 +CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
   3.233 +  FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
   3.234 +CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
   3.235 +  FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
   3.236 +
   3.237 +COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
   3.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';
   3.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';
   3.240 +
   3.241 +CREATE VIEW "area_member_count" AS
   3.242 +  SELECT
   3.243 +    "area"."id" AS "area_id",
   3.244 +    count("member"."id") AS "direct_member_count",
   3.245 +    coalesce(
   3.246 +      sum(
   3.247 +        CASE WHEN "member"."id" NOTNULL THEN
   3.248 +          "membership_weight"("area"."id", "member"."id")
   3.249 +        ELSE 0 END
   3.250 +      )
   3.251 +    ) AS "member_weight"
   3.252 +  FROM "area"
   3.253 +  LEFT JOIN "membership"
   3.254 +  ON "area"."id" = "membership"."area_id"
   3.255 +  LEFT JOIN "privilege"
   3.256 +  ON "privilege"."unit_id" = "area"."unit_id"
   3.257 +  AND "privilege"."member_id" = "membership"."member_id"
   3.258 +  AND "privilege"."voting_right"
   3.259 +  LEFT JOIN "member"
   3.260 +  ON "member"."id" = "privilege"."member_id"  -- NOTE: no membership here!
   3.261 +  AND "member"."active"
   3.262 +  GROUP BY "area"."id";
   3.263 +
   3.264 +COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
   3.265 +
   3.266 +CREATE VIEW "battle_participant" AS
   3.267 +    SELECT "initiative"."id", "initiative"."issue_id"
   3.268 +    FROM "issue" JOIN "initiative"
   3.269 +    ON "issue"."id" = "initiative"."issue_id"
   3.270 +    WHERE "initiative"."admitted"
   3.271 +  UNION ALL
   3.272 +    SELECT NULL, "id" AS "issue_id"
   3.273 +    FROM "issue";
   3.274 +
   3.275 +COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
   3.276 +
   3.277 +CREATE VIEW "battle_view" AS
   3.278 +  SELECT
   3.279 +    "issue"."id" AS "issue_id",
   3.280 +    "winning_initiative"."id" AS "winning_initiative_id",
   3.281 +    "losing_initiative"."id" AS "losing_initiative_id",
   3.282 +    sum(
   3.283 +      CASE WHEN
   3.284 +        coalesce("better_vote"."grade", 0) >
   3.285 +        coalesce("worse_vote"."grade", 0)
   3.286 +      THEN "direct_voter"."weight" ELSE 0 END
   3.287 +    ) AS "count"
   3.288 +  FROM "issue"
   3.289 +  LEFT JOIN "direct_voter"
   3.290 +  ON "issue"."id" = "direct_voter"."issue_id"
   3.291 +  JOIN "battle_participant" AS "winning_initiative"
   3.292 +    ON "issue"."id" = "winning_initiative"."issue_id"
   3.293 +  JOIN "battle_participant" AS "losing_initiative"
   3.294 +    ON "issue"."id" = "losing_initiative"."issue_id"
   3.295 +  LEFT JOIN "vote" AS "better_vote"
   3.296 +    ON "direct_voter"."member_id" = "better_vote"."member_id"
   3.297 +    AND "winning_initiative"."id" = "better_vote"."initiative_id"
   3.298 +  LEFT JOIN "vote" AS "worse_vote"
   3.299 +    ON "direct_voter"."member_id" = "worse_vote"."member_id"
   3.300 +    AND "losing_initiative"."id" = "worse_vote"."initiative_id"
   3.301 +  WHERE "issue"."closed" NOTNULL
   3.302 +  AND "issue"."cleaned" ISNULL
   3.303 +  AND (
   3.304 +    "winning_initiative"."id" != "losing_initiative"."id" OR
   3.305 +    ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
   3.306 +      ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
   3.307 +  GROUP BY
   3.308 +    "issue"."id",
   3.309 +    "winning_initiative"."id",
   3.310 +    "losing_initiative"."id";
   3.311 +
   3.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';
   3.313 +
   3.314 +DROP FUNCTION "check_last_login"();
   3.315 +
   3.316 +CREATE FUNCTION "check_activity"()
   3.317 +  RETURNS VOID
   3.318 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   3.319 +    DECLARE
   3.320 +      "system_setting_row" "system_setting"%ROWTYPE;
   3.321 +    BEGIN
   3.322 +      SELECT * INTO "system_setting_row" FROM "system_setting";
   3.323 +      LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
   3.324 +      IF "system_setting_row"."member_ttl" NOTNULL THEN
   3.325 +        UPDATE "member" SET "active" = FALSE
   3.326 +          WHERE "active" = TRUE
   3.327 +          AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
   3.328 +      END IF;
   3.329 +      RETURN;
   3.330 +    END;
   3.331 +  $$;
   3.332 +
   3.333 +COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
   3.334 +
   3.335 +CREATE OR REPLACE FUNCTION "calculate_member_counts"()
   3.336 +  RETURNS VOID
   3.337 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   3.338 +    BEGIN
   3.339 +      LOCK TABLE "member"       IN SHARE MODE;
   3.340 +      LOCK TABLE "member_count" IN EXCLUSIVE MODE;
   3.341 +      LOCK TABLE "unit"         IN EXCLUSIVE MODE;
   3.342 +      LOCK TABLE "area"         IN EXCLUSIVE MODE;
   3.343 +      LOCK TABLE "privilege"    IN SHARE MODE;
   3.344 +      LOCK TABLE "membership"   IN SHARE MODE;
   3.345 +      DELETE FROM "member_count";
   3.346 +      INSERT INTO "member_count" ("total_count")
   3.347 +        SELECT "total_count" FROM "member_count_view";
   3.348 +      UPDATE "unit" SET "member_count" = "view"."member_count"
   3.349 +        FROM "unit_member_count" AS "view"
   3.350 +        WHERE "view"."unit_id" = "unit"."id";
   3.351 +      UPDATE "area" SET
   3.352 +        "direct_member_count" = "view"."direct_member_count",
   3.353 +        "member_weight"       = "view"."member_weight"
   3.354 +        FROM "area_member_count" AS "view"
   3.355 +        WHERE "view"."area_id" = "area"."id";
   3.356 +      RETURN;
   3.357 +    END;
   3.358 +  $$;
   3.359 +
   3.360 +CREATE OR REPLACE FUNCTION "create_interest_snapshot"
   3.361 +  ( "issue_id_p" "issue"."id"%TYPE )
   3.362 +  RETURNS VOID
   3.363 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   3.364 +    DECLARE
   3.365 +      "member_id_v" "member"."id"%TYPE;
   3.366 +    BEGIN
   3.367 +      DELETE FROM "direct_interest_snapshot"
   3.368 +        WHERE "issue_id" = "issue_id_p"
   3.369 +        AND "event" = 'periodic';
   3.370 +      DELETE FROM "delegating_interest_snapshot"
   3.371 +        WHERE "issue_id" = "issue_id_p"
   3.372 +        AND "event" = 'periodic';
   3.373 +      DELETE FROM "direct_supporter_snapshot"
   3.374 +        WHERE "issue_id" = "issue_id_p"
   3.375 +        AND "event" = 'periodic';
   3.376 +      INSERT INTO "direct_interest_snapshot"
   3.377 +        ("issue_id", "event", "member_id")
   3.378 +        SELECT
   3.379 +          "issue_id_p"  AS "issue_id",
   3.380 +          'periodic'    AS "event",
   3.381 +          "member"."id" AS "member_id"
   3.382 +        FROM "issue"
   3.383 +        JOIN "area" ON "issue"."area_id" = "area"."id"
   3.384 +        JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   3.385 +        JOIN "member" ON "interest"."member_id" = "member"."id"
   3.386 +        JOIN "privilege"
   3.387 +          ON "privilege"."unit_id" = "area"."unit_id"
   3.388 +          AND "privilege"."member_id" = "member"."id"
   3.389 +        WHERE "issue"."id" = "issue_id_p"
   3.390 +        AND "member"."active" AND "privilege"."voting_right";
   3.391 +      FOR "member_id_v" IN
   3.392 +        SELECT "member_id" FROM "direct_interest_snapshot"
   3.393 +        WHERE "issue_id" = "issue_id_p"
   3.394 +        AND "event" = 'periodic'
   3.395 +      LOOP
   3.396 +        UPDATE "direct_interest_snapshot" SET
   3.397 +          "weight" = 1 +
   3.398 +            "weight_of_added_delegations_for_interest_snapshot"(
   3.399 +              "issue_id_p",
   3.400 +              "member_id_v",
   3.401 +              '{}'
   3.402 +            )
   3.403 +          WHERE "issue_id" = "issue_id_p"
   3.404 +          AND "event" = 'periodic'
   3.405 +          AND "member_id" = "member_id_v";
   3.406 +      END LOOP;
   3.407 +      INSERT INTO "direct_supporter_snapshot"
   3.408 +        ( "issue_id", "initiative_id", "event", "member_id",
   3.409 +          "informed", "satisfied" )
   3.410 +        SELECT
   3.411 +          "issue_id_p"            AS "issue_id",
   3.412 +          "initiative"."id"       AS "initiative_id",
   3.413 +          'periodic'              AS "event",
   3.414 +          "supporter"."member_id" AS "member_id",
   3.415 +          "supporter"."draft_id" = "current_draft"."id" AS "informed",
   3.416 +          NOT EXISTS (
   3.417 +            SELECT NULL FROM "critical_opinion"
   3.418 +            WHERE "initiative_id" = "initiative"."id"
   3.419 +            AND "member_id" = "supporter"."member_id"
   3.420 +          ) AS "satisfied"
   3.421 +        FROM "initiative"
   3.422 +        JOIN "supporter"
   3.423 +        ON "supporter"."initiative_id" = "initiative"."id"
   3.424 +        JOIN "current_draft"
   3.425 +        ON "initiative"."id" = "current_draft"."initiative_id"
   3.426 +        JOIN "direct_interest_snapshot"
   3.427 +        ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
   3.428 +        AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
   3.429 +        AND "event" = 'periodic'
   3.430 +        WHERE "initiative"."issue_id" = "issue_id_p";
   3.431 +      RETURN;
   3.432 +    END;
   3.433 +  $$;
   3.434 +
   3.435 +CREATE OR REPLACE FUNCTION "create_snapshot"
   3.436 +  ( "issue_id_p" "issue"."id"%TYPE )
   3.437 +  RETURNS VOID
   3.438 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   3.439 +    DECLARE
   3.440 +      "initiative_id_v"    "initiative"."id"%TYPE;
   3.441 +      "suggestion_id_v"    "suggestion"."id"%TYPE;
   3.442 +    BEGIN
   3.443 +      PERFORM "lock_issue"("issue_id_p");
   3.444 +      PERFORM "create_population_snapshot"("issue_id_p");
   3.445 +      PERFORM "create_interest_snapshot"("issue_id_p");
   3.446 +      UPDATE "issue" SET
   3.447 +        "snapshot" = now(),
   3.448 +        "latest_snapshot_event" = 'periodic',
   3.449 +        "population" = (
   3.450 +          SELECT coalesce(sum("weight"), 0)
   3.451 +          FROM "direct_population_snapshot"
   3.452 +          WHERE "issue_id" = "issue_id_p"
   3.453 +          AND "event" = 'periodic'
   3.454 +        )
   3.455 +        WHERE "id" = "issue_id_p";
   3.456 +      FOR "initiative_id_v" IN
   3.457 +        SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
   3.458 +      LOOP
   3.459 +        UPDATE "initiative" SET
   3.460 +          "supporter_count" = (
   3.461 +            SELECT coalesce(sum("di"."weight"), 0)
   3.462 +            FROM "direct_interest_snapshot" AS "di"
   3.463 +            JOIN "direct_supporter_snapshot" AS "ds"
   3.464 +            ON "di"."member_id" = "ds"."member_id"
   3.465 +            WHERE "di"."issue_id" = "issue_id_p"
   3.466 +            AND "di"."event" = 'periodic'
   3.467 +            AND "ds"."initiative_id" = "initiative_id_v"
   3.468 +            AND "ds"."event" = 'periodic'
   3.469 +          ),
   3.470 +          "informed_supporter_count" = (
   3.471 +            SELECT coalesce(sum("di"."weight"), 0)
   3.472 +            FROM "direct_interest_snapshot" AS "di"
   3.473 +            JOIN "direct_supporter_snapshot" AS "ds"
   3.474 +            ON "di"."member_id" = "ds"."member_id"
   3.475 +            WHERE "di"."issue_id" = "issue_id_p"
   3.476 +            AND "di"."event" = 'periodic'
   3.477 +            AND "ds"."initiative_id" = "initiative_id_v"
   3.478 +            AND "ds"."event" = 'periodic'
   3.479 +            AND "ds"."informed"
   3.480 +          ),
   3.481 +          "satisfied_supporter_count" = (
   3.482 +            SELECT coalesce(sum("di"."weight"), 0)
   3.483 +            FROM "direct_interest_snapshot" AS "di"
   3.484 +            JOIN "direct_supporter_snapshot" AS "ds"
   3.485 +            ON "di"."member_id" = "ds"."member_id"
   3.486 +            WHERE "di"."issue_id" = "issue_id_p"
   3.487 +            AND "di"."event" = 'periodic'
   3.488 +            AND "ds"."initiative_id" = "initiative_id_v"
   3.489 +            AND "ds"."event" = 'periodic'
   3.490 +            AND "ds"."satisfied"
   3.491 +          ),
   3.492 +          "satisfied_informed_supporter_count" = (
   3.493 +            SELECT coalesce(sum("di"."weight"), 0)
   3.494 +            FROM "direct_interest_snapshot" AS "di"
   3.495 +            JOIN "direct_supporter_snapshot" AS "ds"
   3.496 +            ON "di"."member_id" = "ds"."member_id"
   3.497 +            WHERE "di"."issue_id" = "issue_id_p"
   3.498 +            AND "di"."event" = 'periodic'
   3.499 +            AND "ds"."initiative_id" = "initiative_id_v"
   3.500 +            AND "ds"."event" = 'periodic'
   3.501 +            AND "ds"."informed"
   3.502 +            AND "ds"."satisfied"
   3.503 +          )
   3.504 +          WHERE "id" = "initiative_id_v";
   3.505 +        FOR "suggestion_id_v" IN
   3.506 +          SELECT "id" FROM "suggestion"
   3.507 +          WHERE "initiative_id" = "initiative_id_v"
   3.508 +        LOOP
   3.509 +          UPDATE "suggestion" SET
   3.510 +            "minus2_unfulfilled_count" = (
   3.511 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   3.512 +              FROM "issue" CROSS JOIN "opinion"
   3.513 +              JOIN "direct_interest_snapshot" AS "snapshot"
   3.514 +              ON "snapshot"."issue_id" = "issue"."id"
   3.515 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   3.516 +              AND "snapshot"."member_id" = "opinion"."member_id"
   3.517 +              WHERE "issue"."id" = "issue_id_p"
   3.518 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   3.519 +              AND "opinion"."degree" = -2
   3.520 +              AND "opinion"."fulfilled" = FALSE
   3.521 +            ),
   3.522 +            "minus2_fulfilled_count" = (
   3.523 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   3.524 +              FROM "issue" CROSS JOIN "opinion"
   3.525 +              JOIN "direct_interest_snapshot" AS "snapshot"
   3.526 +              ON "snapshot"."issue_id" = "issue"."id"
   3.527 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   3.528 +              AND "snapshot"."member_id" = "opinion"."member_id"
   3.529 +              WHERE "issue"."id" = "issue_id_p"
   3.530 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   3.531 +              AND "opinion"."degree" = -2
   3.532 +              AND "opinion"."fulfilled" = TRUE
   3.533 +            ),
   3.534 +            "minus1_unfulfilled_count" = (
   3.535 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   3.536 +              FROM "issue" CROSS JOIN "opinion"
   3.537 +              JOIN "direct_interest_snapshot" AS "snapshot"
   3.538 +              ON "snapshot"."issue_id" = "issue"."id"
   3.539 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   3.540 +              AND "snapshot"."member_id" = "opinion"."member_id"
   3.541 +              WHERE "issue"."id" = "issue_id_p"
   3.542 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   3.543 +              AND "opinion"."degree" = -1
   3.544 +              AND "opinion"."fulfilled" = FALSE
   3.545 +            ),
   3.546 +            "minus1_fulfilled_count" = (
   3.547 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   3.548 +              FROM "issue" CROSS JOIN "opinion"
   3.549 +              JOIN "direct_interest_snapshot" AS "snapshot"
   3.550 +              ON "snapshot"."issue_id" = "issue"."id"
   3.551 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   3.552 +              AND "snapshot"."member_id" = "opinion"."member_id"
   3.553 +              WHERE "issue"."id" = "issue_id_p"
   3.554 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   3.555 +              AND "opinion"."degree" = -1
   3.556 +              AND "opinion"."fulfilled" = TRUE
   3.557 +            ),
   3.558 +            "plus1_unfulfilled_count" = (
   3.559 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   3.560 +              FROM "issue" CROSS JOIN "opinion"
   3.561 +              JOIN "direct_interest_snapshot" AS "snapshot"
   3.562 +              ON "snapshot"."issue_id" = "issue"."id"
   3.563 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   3.564 +              AND "snapshot"."member_id" = "opinion"."member_id"
   3.565 +              WHERE "issue"."id" = "issue_id_p"
   3.566 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   3.567 +              AND "opinion"."degree" = 1
   3.568 +              AND "opinion"."fulfilled" = FALSE
   3.569 +            ),
   3.570 +            "plus1_fulfilled_count" = (
   3.571 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   3.572 +              FROM "issue" CROSS JOIN "opinion"
   3.573 +              JOIN "direct_interest_snapshot" AS "snapshot"
   3.574 +              ON "snapshot"."issue_id" = "issue"."id"
   3.575 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   3.576 +              AND "snapshot"."member_id" = "opinion"."member_id"
   3.577 +              WHERE "issue"."id" = "issue_id_p"
   3.578 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   3.579 +              AND "opinion"."degree" = 1
   3.580 +              AND "opinion"."fulfilled" = TRUE
   3.581 +            ),
   3.582 +            "plus2_unfulfilled_count" = (
   3.583 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   3.584 +              FROM "issue" CROSS JOIN "opinion"
   3.585 +              JOIN "direct_interest_snapshot" AS "snapshot"
   3.586 +              ON "snapshot"."issue_id" = "issue"."id"
   3.587 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   3.588 +              AND "snapshot"."member_id" = "opinion"."member_id"
   3.589 +              WHERE "issue"."id" = "issue_id_p"
   3.590 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   3.591 +              AND "opinion"."degree" = 2
   3.592 +              AND "opinion"."fulfilled" = FALSE
   3.593 +            ),
   3.594 +            "plus2_fulfilled_count" = (
   3.595 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   3.596 +              FROM "issue" CROSS JOIN "opinion"
   3.597 +              JOIN "direct_interest_snapshot" AS "snapshot"
   3.598 +              ON "snapshot"."issue_id" = "issue"."id"
   3.599 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   3.600 +              AND "snapshot"."member_id" = "opinion"."member_id"
   3.601 +              WHERE "issue"."id" = "issue_id_p"
   3.602 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   3.603 +              AND "opinion"."degree" = 2
   3.604 +              AND "opinion"."fulfilled" = TRUE
   3.605 +            )
   3.606 +            WHERE "suggestion"."id" = "suggestion_id_v";
   3.607 +        END LOOP;
   3.608 +      END LOOP;
   3.609 +      RETURN;
   3.610 +    END;
   3.611 +  $$;
   3.612 +
   3.613 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
   3.614 +  RETURNS VOID
   3.615 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   3.616 +    DECLARE
   3.617 +      "area_id_v"   "area"."id"%TYPE;
   3.618 +      "unit_id_v"   "unit"."id"%TYPE;
   3.619 +      "member_id_v" "member"."id"%TYPE;
   3.620 +    BEGIN
   3.621 +      PERFORM "lock_issue"("issue_id_p");
   3.622 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   3.623 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   3.624 +      -- delete delegating votes (in cases of manual reset of issue state):
   3.625 +      DELETE FROM "delegating_voter"
   3.626 +        WHERE "issue_id" = "issue_id_p";
   3.627 +      -- delete votes from non-privileged voters:
   3.628 +      DELETE FROM "direct_voter"
   3.629 +        USING (
   3.630 +          SELECT
   3.631 +            "direct_voter"."member_id"
   3.632 +          FROM "direct_voter"
   3.633 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   3.634 +          LEFT JOIN "privilege"
   3.635 +          ON "privilege"."unit_id" = "unit_id_v"
   3.636 +          AND "privilege"."member_id" = "direct_voter"."member_id"
   3.637 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   3.638 +            "member"."active" = FALSE OR
   3.639 +            "privilege"."voting_right" ISNULL OR
   3.640 +            "privilege"."voting_right" = FALSE
   3.641 +          )
   3.642 +        ) AS "subquery"
   3.643 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
   3.644 +        AND "direct_voter"."member_id" = "subquery"."member_id";
   3.645 +      -- consider delegations:
   3.646 +      UPDATE "direct_voter" SET "weight" = 1
   3.647 +        WHERE "issue_id" = "issue_id_p";
   3.648 +      PERFORM "add_vote_delegations"("issue_id_p");
   3.649 +      -- set voter count and mark issue as being calculated:
   3.650 +      UPDATE "issue" SET
   3.651 +        "state"  = 'calculation',
   3.652 +        "closed" = now(),
   3.653 +        "voter_count" = (
   3.654 +          SELECT coalesce(sum("weight"), 0)
   3.655 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   3.656 +        )
   3.657 +        WHERE "id" = "issue_id_p";
   3.658 +      -- materialize battle_view:
   3.659 +      -- NOTE: "closed" column of issue must be set at this point
   3.660 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   3.661 +      INSERT INTO "battle" (
   3.662 +        "issue_id",
   3.663 +        "winning_initiative_id", "losing_initiative_id",
   3.664 +        "count"
   3.665 +      ) SELECT
   3.666 +        "issue_id",
   3.667 +        "winning_initiative_id", "losing_initiative_id",
   3.668 +        "count"
   3.669 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   3.670 +      -- copy "positive_votes" and "negative_votes" from "battle" table:
   3.671 +      UPDATE "initiative" SET
   3.672 +        "positive_votes" = "battle_win"."count",
   3.673 +        "negative_votes" = "battle_lose"."count"
   3.674 +        FROM "battle" AS "battle_win", "battle" AS "battle_lose"
   3.675 +        WHERE
   3.676 +          "battle_win"."issue_id" = "issue_id_p" AND
   3.677 +          "battle_win"."winning_initiative_id" = "initiative"."id" AND
   3.678 +          "battle_win"."losing_initiative_id" ISNULL AND
   3.679 +          "battle_lose"."issue_id" = "issue_id_p" AND
   3.680 +          "battle_lose"."losing_initiative_id" = "initiative"."id" AND
   3.681 +          "battle_lose"."winning_initiative_id" ISNULL;
   3.682 +    END;
   3.683 +  $$;
   3.684 +
   3.685 +DROP FUNCTION "array_init_string"(INTEGER);
   3.686 +DROP FUNCTION "square_matrix_init_string"(INTEGER);
   3.687 +
   3.688 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
   3.689 +  RETURNS VOID
   3.690 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   3.691 +    DECLARE
   3.692 +      "issue_row"         "issue"%ROWTYPE;
   3.693 +      "policy_row"        "policy"%ROWTYPE;
   3.694 +      "dimension_v"       INTEGER;
   3.695 +      "vote_matrix"       INT4[][];  -- absolute votes
   3.696 +      "matrix"            INT8[][];  -- defeat strength / best paths
   3.697 +      "i"                 INTEGER;
   3.698 +      "j"                 INTEGER;
   3.699 +      "k"                 INTEGER;
   3.700 +      "battle_row"        "battle"%ROWTYPE;
   3.701 +      "rank_ary"          INT4[];
   3.702 +      "rank_v"            INT4;
   3.703 +      "done_v"            INTEGER;
   3.704 +      "winners_ary"       INTEGER[];
   3.705 +      "initiative_id_v"   "initiative"."id"%TYPE;
   3.706 +    BEGIN
   3.707 +      SELECT * INTO "issue_row"
   3.708 +        FROM "issue" WHERE "id" = "issue_id_p"
   3.709 +        FOR UPDATE;
   3.710 +      SELECT * INTO "policy_row"
   3.711 +        FROM "policy" WHERE "id" = "issue_row"."policy_id";
   3.712 +      SELECT count(1) INTO "dimension_v"
   3.713 +        FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
   3.714 +      -- Create "vote_matrix" with absolute number of votes in pairwise
   3.715 +      -- comparison:
   3.716 +      "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
   3.717 +      "i" := 1;
   3.718 +      "j" := 2;
   3.719 +      FOR "battle_row" IN
   3.720 +        SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
   3.721 +        ORDER BY
   3.722 +        "winning_initiative_id" NULLS LAST,
   3.723 +        "losing_initiative_id" NULLS LAST
   3.724 +      LOOP
   3.725 +        "vote_matrix"["i"]["j"] := "battle_row"."count";
   3.726 +        IF "j" = "dimension_v" THEN
   3.727 +          "i" := "i" + 1;
   3.728 +          "j" := 1;
   3.729 +        ELSE
   3.730 +          "j" := "j" + 1;
   3.731 +          IF "j" = "i" THEN
   3.732 +            "j" := "j" + 1;
   3.733 +          END IF;
   3.734 +        END IF;
   3.735 +      END LOOP;
   3.736 +      IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
   3.737 +        RAISE EXCEPTION 'Wrong battle count (should not happen)';
   3.738 +      END IF;
   3.739 +      -- Store defeat strengths in "matrix" using "defeat_strength"
   3.740 +      -- function:
   3.741 +      "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
   3.742 +      "i" := 1;
   3.743 +      LOOP
   3.744 +        "j" := 1;
   3.745 +        LOOP
   3.746 +          IF "i" != "j" THEN
   3.747 +            "matrix"["i"]["j"] := "defeat_strength"(
   3.748 +              "vote_matrix"["i"]["j"],
   3.749 +              "vote_matrix"["j"]["i"]
   3.750 +            );
   3.751 +          END IF;
   3.752 +          EXIT WHEN "j" = "dimension_v";
   3.753 +          "j" := "j" + 1;
   3.754 +        END LOOP;
   3.755 +        EXIT WHEN "i" = "dimension_v";
   3.756 +        "i" := "i" + 1;
   3.757 +      END LOOP;
   3.758 +      -- Find best paths:
   3.759 +      "i" := 1;
   3.760 +      LOOP
   3.761 +        "j" := 1;
   3.762 +        LOOP
   3.763 +          IF "i" != "j" THEN
   3.764 +            "k" := 1;
   3.765 +            LOOP
   3.766 +              IF "i" != "k" AND "j" != "k" THEN
   3.767 +                IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
   3.768 +                  IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
   3.769 +                    "matrix"["j"]["k"] := "matrix"["j"]["i"];
   3.770 +                  END IF;
   3.771 +                ELSE
   3.772 +                  IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
   3.773 +                    "matrix"["j"]["k"] := "matrix"["i"]["k"];
   3.774 +                  END IF;
   3.775 +                END IF;
   3.776 +              END IF;
   3.777 +              EXIT WHEN "k" = "dimension_v";
   3.778 +              "k" := "k" + 1;
   3.779 +            END LOOP;
   3.780 +          END IF;
   3.781 +          EXIT WHEN "j" = "dimension_v";
   3.782 +          "j" := "j" + 1;
   3.783 +        END LOOP;
   3.784 +        EXIT WHEN "i" = "dimension_v";
   3.785 +        "i" := "i" + 1;
   3.786 +      END LOOP;
   3.787 +      -- Determine order of winners:
   3.788 +      "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
   3.789 +      "rank_v" := 1;
   3.790 +      "done_v" := 0;
   3.791 +      LOOP
   3.792 +        "winners_ary" := '{}';
   3.793 +        "i" := 1;
   3.794 +        LOOP
   3.795 +          IF "rank_ary"["i"] ISNULL THEN
   3.796 +            "j" := 1;
   3.797 +            LOOP
   3.798 +              IF
   3.799 +                "i" != "j" AND
   3.800 +                "rank_ary"["j"] ISNULL AND
   3.801 +                "matrix"["j"]["i"] > "matrix"["i"]["j"]
   3.802 +              THEN
   3.803 +                -- someone else is better
   3.804 +                EXIT;
   3.805 +              END IF;
   3.806 +              IF "j" = "dimension_v" THEN
   3.807 +                -- noone is better
   3.808 +                "winners_ary" := "winners_ary" || "i";
   3.809 +                EXIT;
   3.810 +              END IF;
   3.811 +              "j" := "j" + 1;
   3.812 +            END LOOP;
   3.813 +          END IF;
   3.814 +          EXIT WHEN "i" = "dimension_v";
   3.815 +          "i" := "i" + 1;
   3.816 +        END LOOP;
   3.817 +        "i" := 1;
   3.818 +        LOOP
   3.819 +          "rank_ary"["winners_ary"["i"]] := "rank_v";
   3.820 +          "done_v" := "done_v" + 1;
   3.821 +          EXIT WHEN "i" = array_upper("winners_ary", 1);
   3.822 +          "i" := "i" + 1;
   3.823 +        END LOOP;
   3.824 +        EXIT WHEN "done_v" = "dimension_v";
   3.825 +        "rank_v" := "rank_v" + 1;
   3.826 +      END LOOP;
   3.827 +      -- write preliminary results:
   3.828 +      "i" := 1;
   3.829 +      FOR "initiative_id_v" IN
   3.830 +        SELECT "id" FROM "initiative"
   3.831 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
   3.832 +        ORDER BY "id"
   3.833 +      LOOP
   3.834 +        UPDATE "initiative" SET
   3.835 +          "direct_majority" =
   3.836 +            CASE WHEN "policy_row"."direct_majority_strict" THEN
   3.837 +              "positive_votes" * "policy_row"."direct_majority_den" >
   3.838 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   3.839 +            ELSE
   3.840 +              "positive_votes" * "policy_row"."direct_majority_den" >=
   3.841 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   3.842 +            END
   3.843 +            AND "positive_votes" >= "policy_row"."direct_majority_positive"
   3.844 +            AND "issue_row"."voter_count"-"negative_votes" >=
   3.845 +                "policy_row"."direct_majority_non_negative",
   3.846 +            "indirect_majority" =
   3.847 +            CASE WHEN "policy_row"."indirect_majority_strict" THEN
   3.848 +              "positive_votes" * "policy_row"."indirect_majority_den" >
   3.849 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   3.850 +            ELSE
   3.851 +              "positive_votes" * "policy_row"."indirect_majority_den" >=
   3.852 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   3.853 +            END
   3.854 +            AND "positive_votes" >= "policy_row"."indirect_majority_positive"
   3.855 +            AND "issue_row"."voter_count"-"negative_votes" >=
   3.856 +                "policy_row"."indirect_majority_non_negative",
   3.857 +          "schulze_rank"           = "rank_ary"["i"],
   3.858 +          "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
   3.859 +          "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"["dimension_v"],
   3.860 +          "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
   3.861 +          "reverse_beat_path"      = "matrix"["dimension_v"]["i"] >= 0,
   3.862 +          "winner"                 = FALSE
   3.863 +          WHERE "id" = "initiative_id_v";
   3.864 +        "i" := "i" + 1;
   3.865 +      END LOOP;
   3.866 +      IF "i" != "dimension_v" THEN
   3.867 +        RAISE EXCEPTION 'Wrong winner count (should not happen)';
   3.868 +      END IF;
   3.869 +      -- take indirect majorities into account:
   3.870 +      LOOP
   3.871 +        UPDATE "initiative" SET "indirect_majority" = TRUE
   3.872 +          FROM (
   3.873 +            SELECT "new_initiative"."id" AS "initiative_id"
   3.874 +            FROM "initiative" "old_initiative"
   3.875 +            JOIN "initiative" "new_initiative"
   3.876 +              ON "new_initiative"."issue_id" = "issue_id_p"
   3.877 +              AND "new_initiative"."indirect_majority" = FALSE
   3.878 +            JOIN "battle" "battle_win"
   3.879 +              ON "battle_win"."issue_id" = "issue_id_p"
   3.880 +              AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
   3.881 +              AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
   3.882 +            JOIN "battle" "battle_lose"
   3.883 +              ON "battle_lose"."issue_id" = "issue_id_p"
   3.884 +              AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
   3.885 +              AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
   3.886 +            WHERE "old_initiative"."issue_id" = "issue_id_p"
   3.887 +            AND "old_initiative"."indirect_majority" = TRUE
   3.888 +            AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
   3.889 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >
   3.890 +              "policy_row"."indirect_majority_num" *
   3.891 +              ("battle_win"."count"+"battle_lose"."count")
   3.892 +            ELSE
   3.893 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >=
   3.894 +              "policy_row"."indirect_majority_num" *
   3.895 +              ("battle_win"."count"+"battle_lose"."count")
   3.896 +            END
   3.897 +            AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
   3.898 +            AND "issue_row"."voter_count"-"battle_lose"."count" >=
   3.899 +                "policy_row"."indirect_majority_non_negative"
   3.900 +          ) AS "subquery"
   3.901 +          WHERE "id" = "subquery"."initiative_id";
   3.902 +        EXIT WHEN NOT FOUND;
   3.903 +      END LOOP;
   3.904 +      -- set "multistage_majority" for remaining matching initiatives:
   3.905 +       UPDATE "initiative" SET "multistage_majority" = TRUE
   3.906 +        FROM (
   3.907 +          SELECT "losing_initiative"."id" AS "initiative_id"
   3.908 +          FROM "initiative" "losing_initiative"
   3.909 +          JOIN "initiative" "winning_initiative"
   3.910 +            ON "winning_initiative"."issue_id" = "issue_id_p"
   3.911 +            AND "winning_initiative"."admitted"
   3.912 +          JOIN "battle" "battle_win"
   3.913 +            ON "battle_win"."issue_id" = "issue_id_p"
   3.914 +            AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
   3.915 +            AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
   3.916 +          JOIN "battle" "battle_lose"
   3.917 +            ON "battle_lose"."issue_id" = "issue_id_p"
   3.918 +            AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
   3.919 +            AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
   3.920 +          WHERE "losing_initiative"."issue_id" = "issue_id_p"
   3.921 +          AND "losing_initiative"."admitted"
   3.922 +          AND "winning_initiative"."schulze_rank" <
   3.923 +              "losing_initiative"."schulze_rank"
   3.924 +          AND "battle_win"."count" > "battle_lose"."count"
   3.925 +          AND (
   3.926 +            "battle_win"."count" > "winning_initiative"."positive_votes" OR
   3.927 +            "battle_lose"."count" < "losing_initiative"."negative_votes" )
   3.928 +        ) AS "subquery"
   3.929 +        WHERE "id" = "subquery"."initiative_id";
   3.930 +      -- mark eligible initiatives:
   3.931 +      UPDATE "initiative" SET "eligible" = TRUE
   3.932 +        WHERE "issue_id" = "issue_id_p"
   3.933 +        AND "initiative"."direct_majority"
   3.934 +        AND "initiative"."indirect_majority"
   3.935 +        AND "initiative"."better_than_status_quo"
   3.936 +        AND (
   3.937 +          "policy_row"."no_multistage_majority" = FALSE OR
   3.938 +          "initiative"."multistage_majority" = FALSE )
   3.939 +        AND (
   3.940 +          "policy_row"."no_reverse_beat_path" = FALSE OR
   3.941 +          "initiative"."reverse_beat_path" = FALSE );
   3.942 +      -- mark final winner:
   3.943 +      UPDATE "initiative" SET "winner" = TRUE
   3.944 +        FROM (
   3.945 +          SELECT "id" AS "initiative_id"
   3.946 +          FROM "initiative"
   3.947 +          WHERE "issue_id" = "issue_id_p" AND "eligible"
   3.948 +          ORDER BY "schulze_rank", "id"
   3.949 +          LIMIT 1
   3.950 +        ) AS "subquery"
   3.951 +        WHERE "id" = "subquery"."initiative_id";
   3.952 +      -- write (final) ranks:
   3.953 +      "rank_v" := 1;
   3.954 +      FOR "initiative_id_v" IN
   3.955 +        SELECT "id"
   3.956 +        FROM "initiative"
   3.957 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
   3.958 +        ORDER BY
   3.959 +          "winner" DESC,
   3.960 +          ("direct_majority" AND "indirect_majority") DESC,
   3.961 +          "schulze_rank",
   3.962 +          "id"
   3.963 +      LOOP
   3.964 +        UPDATE "initiative" SET "rank" = "rank_v"
   3.965 +          WHERE "id" = "initiative_id_v";
   3.966 +        "rank_v" := "rank_v" + 1;
   3.967 +      END LOOP;
   3.968 +      -- set schulze rank of status quo and mark issue as finished:
   3.969 +      UPDATE "issue" SET
   3.970 +        "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
   3.971 +        "state" =
   3.972 +          CASE WHEN EXISTS (
   3.973 +            SELECT NULL FROM "initiative"
   3.974 +            WHERE "issue_id" = "issue_id_p" AND "winner"
   3.975 +          ) THEN
   3.976 +            'finished_with_winner'::"issue_state"
   3.977 +          ELSE
   3.978 +            'finished_without_winner'::"issue_state"
   3.979 +          END,
   3.980 +        "ranks_available" = TRUE
   3.981 +        WHERE "id" = "issue_id_p";
   3.982 +      RETURN;
   3.983 +    END;
   3.984 +  $$;
   3.985 +
   3.986 +CREATE OR REPLACE FUNCTION "check_issue"
   3.987 +  ( "issue_id_p" "issue"."id"%TYPE )
   3.988 +  RETURNS VOID
   3.989 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   3.990 +    DECLARE
   3.991 +      "issue_row"         "issue"%ROWTYPE;
   3.992 +      "policy_row"        "policy"%ROWTYPE;
   3.993 +    BEGIN
   3.994 +      PERFORM "lock_issue"("issue_id_p");
   3.995 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   3.996 +      -- only process open issues:
   3.997 +      IF "issue_row"."closed" ISNULL THEN
   3.998 +        SELECT * INTO "policy_row" FROM "policy"
   3.999 +          WHERE "id" = "issue_row"."policy_id";
  3.1000 +        -- create a snapshot, unless issue is already fully frozen:
  3.1001 +        IF "issue_row"."fully_frozen" ISNULL THEN
  3.1002 +          PERFORM "create_snapshot"("issue_id_p");
  3.1003 +          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  3.1004 +        END IF;
  3.1005 +        -- eventually close or accept issues, which have not been accepted:
  3.1006 +        IF "issue_row"."accepted" ISNULL THEN
  3.1007 +          IF EXISTS (
  3.1008 +            SELECT NULL FROM "initiative"
  3.1009 +            WHERE "issue_id" = "issue_id_p"
  3.1010 +            AND "supporter_count" > 0
  3.1011 +            AND "supporter_count" * "policy_row"."issue_quorum_den"
  3.1012 +            >= "issue_row"."population" * "policy_row"."issue_quorum_num"
  3.1013 +          ) THEN
  3.1014 +            -- accept issues, if supporter count is high enough
  3.1015 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  3.1016 +            -- NOTE: "issue_row" used later
  3.1017 +            "issue_row"."state" := 'discussion';
  3.1018 +            "issue_row"."accepted" := now();
  3.1019 +            UPDATE "issue" SET
  3.1020 +              "state"    = "issue_row"."state",
  3.1021 +              "accepted" = "issue_row"."accepted"
  3.1022 +              WHERE "id" = "issue_row"."id";
  3.1023 +          ELSIF
  3.1024 +            now() >= "issue_row"."created" + "issue_row"."admission_time"
  3.1025 +          THEN
  3.1026 +            -- close issues, if admission time has expired
  3.1027 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  3.1028 +            UPDATE "issue" SET
  3.1029 +              "state" = 'canceled_issue_not_accepted',
  3.1030 +              "closed" = now()
  3.1031 +              WHERE "id" = "issue_row"."id";
  3.1032 +          END IF;
  3.1033 +        END IF;
  3.1034 +        -- eventually half freeze issues:
  3.1035 +        IF
  3.1036 +          -- NOTE: issue can't be closed at this point, if it has been accepted
  3.1037 +          "issue_row"."accepted" NOTNULL AND
  3.1038 +          "issue_row"."half_frozen" ISNULL
  3.1039 +        THEN
  3.1040 +          IF
  3.1041 +            now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
  3.1042 +          THEN
  3.1043 +            PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
  3.1044 +            -- NOTE: "issue_row" used later
  3.1045 +            "issue_row"."state" := 'verification';
  3.1046 +            "issue_row"."half_frozen" := now();
  3.1047 +            UPDATE "issue" SET
  3.1048 +              "state"       = "issue_row"."state",
  3.1049 +              "half_frozen" = "issue_row"."half_frozen"
  3.1050 +              WHERE "id" = "issue_row"."id";
  3.1051 +          END IF;
  3.1052 +        END IF;
  3.1053 +        -- close issues after some time, if all initiatives have been revoked:
  3.1054 +        IF
  3.1055 +          "issue_row"."closed" ISNULL AND
  3.1056 +          NOT EXISTS (
  3.1057 +            -- all initiatives are revoked
  3.1058 +            SELECT NULL FROM "initiative"
  3.1059 +            WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
  3.1060 +          ) AND (
  3.1061 +            -- and issue has not been accepted yet
  3.1062 +            "issue_row"."accepted" ISNULL OR
  3.1063 +            NOT EXISTS (
  3.1064 +              -- or no initiatives have been revoked lately
  3.1065 +              SELECT NULL FROM "initiative"
  3.1066 +              WHERE "issue_id" = "issue_id_p"
  3.1067 +              AND now() < "revoked" + "issue_row"."verification_time"
  3.1068 +            ) OR (
  3.1069 +              -- or verification time has elapsed
  3.1070 +              "issue_row"."half_frozen" NOTNULL AND
  3.1071 +              "issue_row"."fully_frozen" ISNULL AND
  3.1072 +              now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  3.1073 +            )
  3.1074 +          )
  3.1075 +        THEN
  3.1076 +          -- NOTE: "issue_row" used later
  3.1077 +          IF "issue_row"."accepted" ISNULL THEN
  3.1078 +            "issue_row"."state" := 'canceled_revoked_before_accepted';
  3.1079 +          ELSIF "issue_row"."half_frozen" ISNULL THEN
  3.1080 +            "issue_row"."state" := 'canceled_after_revocation_during_discussion';
  3.1081 +          ELSE
  3.1082 +            "issue_row"."state" := 'canceled_after_revocation_during_verification';
  3.1083 +          END IF;
  3.1084 +          "issue_row"."closed" := now();
  3.1085 +          UPDATE "issue" SET
  3.1086 +            "state"  = "issue_row"."state",
  3.1087 +            "closed" = "issue_row"."closed"
  3.1088 +            WHERE "id" = "issue_row"."id";
  3.1089 +        END IF;
  3.1090 +        -- fully freeze issue after verification time:
  3.1091 +        IF
  3.1092 +          "issue_row"."half_frozen" NOTNULL AND
  3.1093 +          "issue_row"."fully_frozen" ISNULL AND
  3.1094 +          "issue_row"."closed" ISNULL AND
  3.1095 +          now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
  3.1096 +        THEN
  3.1097 +          PERFORM "freeze_after_snapshot"("issue_id_p");
  3.1098 +          -- NOTE: "issue" might change, thus "issue_row" has to be updated below
  3.1099 +        END IF;
  3.1100 +        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  3.1101 +        -- close issue by calling close_voting(...) after voting time:
  3.1102 +        IF
  3.1103 +          "issue_row"."closed" ISNULL AND
  3.1104 +          "issue_row"."fully_frozen" NOTNULL AND
  3.1105 +          now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
  3.1106 +        THEN
  3.1107 +          PERFORM "close_voting"("issue_id_p");
  3.1108 +          -- calculate ranks will not consume much time and can be done now
  3.1109 +          PERFORM "calculate_ranks"("issue_id_p");
  3.1110 +        END IF;
  3.1111 +      END IF;
  3.1112 +      RETURN;
  3.1113 +    END;
  3.1114 +  $$;
  3.1115 +
  3.1116 +CREATE OR REPLACE FUNCTION "check_everything"()
  3.1117 +  RETURNS VOID
  3.1118 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  3.1119 +    DECLARE
  3.1120 +      "issue_id_v" "issue"."id"%TYPE;
  3.1121 +    BEGIN
  3.1122 +      PERFORM "check_activity"();
  3.1123 +      PERFORM "calculate_member_counts"();
  3.1124 +      FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
  3.1125 +        PERFORM "check_issue"("issue_id_v");
  3.1126 +      END LOOP;
  3.1127 +      FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
  3.1128 +        PERFORM "calculate_ranks"("issue_id_v");
  3.1129 +      END LOOP;
  3.1130 +      RETURN;
  3.1131 +    END;
  3.1132 +  $$;
  3.1133 +
  3.1134 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
  3.1135 +  RETURNS VOID
  3.1136 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  3.1137 +    BEGIN
  3.1138 +      UPDATE "member" SET
  3.1139 +        "last_login"                   = NULL,
  3.1140 +        "login"                        = NULL,
  3.1141 +        "password"                     = NULL,
  3.1142 +        "locked"                       = TRUE,
  3.1143 +        "active"                       = FALSE,
  3.1144 +        "notify_email"                 = NULL,
  3.1145 +        "notify_email_unconfirmed"     = NULL,
  3.1146 +        "notify_email_secret"          = NULL,
  3.1147 +        "notify_email_secret_expiry"   = NULL,
  3.1148 +        "notify_email_lock_expiry"     = NULL,
  3.1149 +        "password_reset_secret"        = NULL,
  3.1150 +        "password_reset_secret_expiry" = NULL,
  3.1151 +        "organizational_unit"          = NULL,
  3.1152 +        "internal_posts"               = NULL,
  3.1153 +        "realname"                     = NULL,
  3.1154 +        "birthday"                     = NULL,
  3.1155 +        "address"                      = NULL,
  3.1156 +        "email"                        = NULL,
  3.1157 +        "xmpp_address"                 = NULL,
  3.1158 +        "website"                      = NULL,
  3.1159 +        "phone"                        = NULL,
  3.1160 +        "mobile_phone"                 = NULL,
  3.1161 +        "profession"                   = NULL,
  3.1162 +        "external_memberships"         = NULL,
  3.1163 +        "external_posts"               = NULL,
  3.1164 +        "statement"                    = NULL
  3.1165 +        WHERE "id" = "member_id_p";
  3.1166 +      -- "text_search_data" is updated by triggers
  3.1167 +      DELETE FROM "setting"            WHERE "member_id" = "member_id_p";
  3.1168 +      DELETE FROM "setting_map"        WHERE "member_id" = "member_id_p";
  3.1169 +      DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
  3.1170 +      DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
  3.1171 +      DELETE FROM "contact"            WHERE "member_id" = "member_id_p";
  3.1172 +      DELETE FROM "ignored_member"     WHERE "member_id" = "member_id_p";
  3.1173 +      DELETE FROM "area_setting"       WHERE "member_id" = "member_id_p";
  3.1174 +      DELETE FROM "issue_setting"      WHERE "member_id" = "member_id_p";
  3.1175 +      DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
  3.1176 +      DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
  3.1177 +      DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
  3.1178 +      DELETE FROM "membership"         WHERE "member_id" = "member_id_p";
  3.1179 +      DELETE FROM "delegation"         WHERE "truster_id" = "member_id_p";
  3.1180 +      DELETE FROM "non_voter"          WHERE "member_id" = "member_id_p";
  3.1181 +      DELETE FROM "direct_voter" USING "issue"
  3.1182 +        WHERE "direct_voter"."issue_id" = "issue"."id"
  3.1183 +        AND "issue"."closed" ISNULL
  3.1184 +        AND "member_id" = "member_id_p";
  3.1185 +      RETURN;
  3.1186 +    END;
  3.1187 +  $$;
  3.1188 +
  3.1189 +CREATE OR REPLACE FUNCTION "delete_private_data"()
  3.1190 +  RETURNS VOID
  3.1191 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  3.1192 +    BEGIN
  3.1193 +      UPDATE "member" SET
  3.1194 +        "last_login"                   = NULL,
  3.1195 +        "login"                        = NULL,
  3.1196 +        "password"                     = NULL,
  3.1197 +        "notify_email"                 = NULL,
  3.1198 +        "notify_email_unconfirmed"     = NULL,
  3.1199 +        "notify_email_secret"          = NULL,
  3.1200 +        "notify_email_secret_expiry"   = NULL,
  3.1201 +        "notify_email_lock_expiry"     = NULL,
  3.1202 +        "password_reset_secret"        = NULL,
  3.1203 +        "password_reset_secret_expiry" = NULL,
  3.1204 +        "organizational_unit"          = NULL,
  3.1205 +        "internal_posts"               = NULL,
  3.1206 +        "realname"                     = NULL,
  3.1207 +        "birthday"                     = NULL,
  3.1208 +        "address"                      = NULL,
  3.1209 +        "email"                        = NULL,
  3.1210 +        "xmpp_address"                 = NULL,
  3.1211 +        "website"                      = NULL,
  3.1212 +        "phone"                        = NULL,
  3.1213 +        "mobile_phone"                 = NULL,
  3.1214 +        "profession"                   = NULL,
  3.1215 +        "external_memberships"         = NULL,
  3.1216 +        "external_posts"               = NULL,
  3.1217 +        "statement"                    = NULL;
  3.1218 +      -- "text_search_data" is updated by triggers
  3.1219 +      DELETE FROM "invite_code";
  3.1220 +      DELETE FROM "setting";
  3.1221 +      DELETE FROM "setting_map";
  3.1222 +      DELETE FROM "member_relation_setting";
  3.1223 +      DELETE FROM "member_image";
  3.1224 +      DELETE FROM "contact";
  3.1225 +      DELETE FROM "ignored_member";
  3.1226 +      DELETE FROM "area_setting";
  3.1227 +      DELETE FROM "issue_setting";
  3.1228 +      DELETE FROM "ignored_initiative";
  3.1229 +      DELETE FROM "initiative_setting";
  3.1230 +      DELETE FROM "suggestion_setting";
  3.1231 +      DELETE FROM "non_voter";
  3.1232 +      DELETE FROM "direct_voter" USING "issue"
  3.1233 +        WHERE "direct_voter"."issue_id" = "issue"."id"
  3.1234 +        AND "issue"."closed" ISNULL;
  3.1235 +      RETURN;
  3.1236 +    END;
  3.1237 +  $$;
  3.1238 +
  3.1239 +COMMIT;
  3.1240 +
  3.1241 +BEGIN;
  3.1242 +
  3.1243 +UPDATE "member" SET
  3.1244 +  "activated" = "created",
  3.1245 +  "last_activity" = CASE WHEN "active" THEN
  3.1246 +    coalesce("last_login"::DATE, now())
  3.1247 +  ELSE
  3.1248 +    "last_login"::DATE
  3.1249 +  END;
  3.1250 +
  3.1251 +UPDATE "member" SET
  3.1252 +  "created" = "invite_code"."created",
  3.1253 +  "invite_code" = "invite_code"."code",
  3.1254 +  "admin_comment" = "invite_code"."comment"
  3.1255 +  FROM "invite_code"
  3.1256 +  WHERE "member"."id" = "invite_code"."member_id";
  3.1257 +
  3.1258 +DROP TABLE "invite_code";
  3.1259 +
  3.1260 +UPDATE "initiative" SET
  3.1261 +    "direct_majority"        = "rank" NOTNULL,
  3.1262 +    "indirect_majority"      = "rank" NOTNULL,
  3.1263 +    "schulze_rank"           = "rank",
  3.1264 +    "better_than_status_quo" = "rank" NOTNULL,
  3.1265 +    "worse_than_status_quo"  = "rank" ISNULL,
  3.1266 +    "reverse_beat_path"      = "rank" ISNULL,
  3.1267 +    "multistage_majority"    = "rank" ISNULL,
  3.1268 +    "eligible"               = "rank" NOTNULL,
  3.1269 +    "winner"                 = ("rank" = 1)
  3.1270 +  FROM "issue"
  3.1271 +  WHERE "issue"."id" = "initiative"."issue_id"
  3.1272 +  AND "issue"."state" IN ('finished_without_winner', 'finished_with_winner')
  3.1273 +  AND "initiative"."admitted";
  3.1274 +
  3.1275 +UPDATE "issue" SET "status_quo_schulze_rank" = "subquery"."rank"
  3.1276 +  FROM (
  3.1277 +    SELECT
  3.1278 +      "issue"."id" AS "issue_id",
  3.1279 +      COALESCE(max("initiative"."rank") + 1, 1) AS "rank"
  3.1280 +    FROM "issue" JOIN "initiative"
  3.1281 +    ON "issue"."id" = "initiative"."issue_id"
  3.1282 +    WHERE "issue"."state" IN ('finished_without_winner', 'finished_with_winner')
  3.1283 +    AND "initiative"."admitted"
  3.1284 +    GROUP BY "issue"."id"
  3.1285 +  ) AS "subquery"
  3.1286 +  WHERE "issue"."id" = "subquery"."issue_id";
  3.1287 +
  3.1288 +CREATE FUNCTION "update__set_remaining_ranks"("issue_id_p" "issue"."id"%TYPE)
  3.1289 +  RETURNS VOID
  3.1290 +  LANGUAGE 'plpgsql' AS $$
  3.1291 +    DECLARE
  3.1292 +      "rank_v"          INT4;
  3.1293 +      "initiative_id_v" INT4;
  3.1294 +    BEGIN
  3.1295 +      SELECT "status_quo_schulze_rank" INTO "rank_v"
  3.1296 +        FROM "issue" WHERE "id" = "issue_id_p";
  3.1297 +      FOR "initiative_id_v" IN
  3.1298 +        SELECT "id" FROM "initiative"
  3.1299 +        WHERE "issue_id" = "issue_id_p" AND "admitted" AND "rank" ISNULL
  3.1300 +        ORDER BY "vote_ratio"("positive_votes", "negative_votes") DESC
  3.1301 +      LOOP
  3.1302 +        UPDATE "initiative" SET
  3.1303 +          "schulze_rank" = "rank_v" + 1,
  3.1304 +          "rank"         = "rank_v"
  3.1305 +          WHERE "id" = "initiative_id_v";
  3.1306 +        "rank_v" := "rank_v" + 1;
  3.1307 +      END LOOP;
  3.1308 +      RETURN;
  3.1309 +    END;
  3.1310 +  $$;
  3.1311 +
  3.1312 +SELECT "update__set_remaining_ranks"("id") FROM "issue"
  3.1313 +  WHERE "state" IN ('finished_without_winner', 'finished_with_winner');
  3.1314 +
  3.1315 +DROP FUNCTION "update__set_remaining_ranks"("issue"."id"%TYPE);
  3.1316 +
  3.1317 +UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id"
  3.1318 +  FROM (
  3.1319 +    SELECT DISTINCT ON ("suggestion"."id")
  3.1320 +      "suggestion"."id" AS "suggestion_id",
  3.1321 +      "draft"."id" AS "draft_id"
  3.1322 +    FROM "suggestion" JOIN "draft"
  3.1323 +    ON "suggestion"."initiative_id" = "draft"."initiative_id"
  3.1324 +    WHERE "draft"."created" <= "suggestion"."created"
  3.1325 +    ORDER BY "suggestion"."id", "draft"."created" DESC
  3.1326 +  ) AS "subquery"
  3.1327 +  WHERE "suggestion"."id" = "subquery"."suggestion_id";
  3.1328 +
  3.1329 +COMMIT;
  3.1330 +
  3.1331 +ALTER TABLE "member" ADD CONSTRAINT "active_requires_activated_and_last_activity"
  3.1332 +  CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL));
  3.1333 +ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;

Impressum / About Us