liquid_feedback_core

changeset 339:aecc4f182602

Update script to v2.2.0
author jbe
date Thu Feb 21 01:27:47 2013 +0100 (2013-02-21)
parents 705c29b0ed44
children 29163d72fe9a
files update/core-update.v2.1.0-v2.2.0.sql
line diff
     1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v2.1.0-v2.2.0.sql	Thu Feb 21 01:27:47 2013 +0100
     1.3 @@ -0,0 +1,1761 @@
     1.4 +BEGIN;
     1.5 +
     1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.7 +  SELECT * FROM (VALUES ('2.2.0', 2, 2, 0))
     1.8 +  AS "subquery"("string", "major", "minor", "revision");
     1.9 +
    1.10 +DROP VIEW "issue_with_ranks_missing";
    1.11 +DROP VIEW "open_issue"; -- recreated later
    1.12 +DROP VIEW "event_seen_by_member";  -- recreated later
    1.13 +DROP VIEW "selected_event_seen_by_member";  -- recreated later
    1.14 +ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
    1.15 +ALTER TABLE "issue" DROP COLUMN "ranks_available";
    1.16 +ALTER TABLE "event" DROP CONSTRAINT "event_state_check";
    1.17 +ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_issue_state_changed";  -- recreated later
    1.18 +ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft";  -- recreated later
    1.19 +ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_suggestion_creation";  -- recreated later
    1.20 +ALTER TYPE "issue_state" RENAME TO "issue_state_old";
    1.21 +CREATE TYPE "issue_state" AS ENUM (
    1.22 +        'admission', 'discussion', 'verification', 'voting',
    1.23 +        'canceled_revoked_before_accepted',
    1.24 +        'canceled_issue_not_accepted',
    1.25 +        'canceled_after_revocation_during_discussion',
    1.26 +        'canceled_after_revocation_during_verification',
    1.27 +        'canceled_no_initiative_admitted',
    1.28 +        'finished_without_winner', 'finished_with_winner');
    1.29 +ALTER TABLE "issue" ALTER COLUMN "state" DROP DEFAULT;
    1.30 +ALTER TABLE "issue" ALTER COLUMN "state" TYPE "issue_state" USING "state"::text::"issue_state";
    1.31 +ALTER TABLE "event" ALTER COLUMN "state" TYPE "issue_state" USING "state"::text::"issue_state";
    1.32 +DROP TYPE "issue_state_old";
    1.33 +ALTER TABLE "issue" ALTER COLUMN "state" SET DEFAULT 'admission';
    1.34 +ALTER TABLE "issue" ADD CONSTRAINT "valid_state"
    1.35 +        CHECK ((
    1.36 +          ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL ) OR
    1.37 +          ("accepted" NOTNULL AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL ) OR
    1.38 +          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
    1.39 +          ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
    1.40 +        ) AND (
    1.41 +          ("state" = 'admission'    AND "closed" ISNULL AND "accepted" ISNULL) OR
    1.42 +          ("state" = 'discussion'   AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
    1.43 +          ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
    1.44 +          ("state" = 'voting'       AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
    1.45 +          ("state" = 'canceled_revoked_before_accepted'              AND "closed" NOTNULL AND "accepted" ISNULL) OR
    1.46 +          ("state" = 'canceled_issue_not_accepted'                   AND "closed" NOTNULL AND "accepted" ISNULL) OR
    1.47 +          ("state" = 'canceled_after_revocation_during_discussion'   AND "closed" NOTNULL AND "half_frozen"  ISNULL) OR
    1.48 +          ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
    1.49 +          ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
    1.50 +          ("state" = 'finished_without_winner'         AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
    1.51 +          ("state" = 'finished_with_winner'            AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
    1.52 +        ));
    1.53 +ALTER TABLE "issue" ADD COLUMN "phase_finished" TIMESTAMPTZ;
    1.54 +COMMENT ON COLUMN "issue"."phase_finished" IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set';
    1.55 +ALTER TABLE "issue" ADD CONSTRAINT "phase_finished_only_when_not_closed"
    1.56 +          CHECK ("phase_finished" ISNULL OR "closed" ISNULL);
    1.57 +ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
    1.58 +          "event" != 'issue_state_changed' OR (
    1.59 +            "member_id"     ISNULL  AND
    1.60 +            "issue_id"      NOTNULL AND
    1.61 +            "state"         NOTNULL AND
    1.62 +            "initiative_id" ISNULL  AND
    1.63 +            "draft_id"      ISNULL  AND
    1.64 +            "suggestion_id" ISNULL  ));
    1.65 +ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
    1.66 +          "event" NOT IN (
    1.67 +            'initiative_created_in_new_issue',
    1.68 +            'initiative_created_in_existing_issue',
    1.69 +            'initiative_revoked',
    1.70 +            'new_draft_created'
    1.71 +          ) OR (
    1.72 +            "member_id"     NOTNULL AND
    1.73 +            "issue_id"      NOTNULL AND
    1.74 +            "state"         NOTNULL AND
    1.75 +            "initiative_id" NOTNULL AND
    1.76 +            "draft_id"      NOTNULL AND
    1.77 +            "suggestion_id" ISNULL  ));
    1.78 +ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
    1.79 +          "event" != 'suggestion_created' OR (
    1.80 +            "member_id"     NOTNULL AND
    1.81 +            "issue_id"      NOTNULL AND
    1.82 +            "state"         NOTNULL AND
    1.83 +            "initiative_id" NOTNULL AND
    1.84 +            "draft_id"      ISNULL  AND
    1.85 +            "suggestion_id" NOTNULL ));
    1.86 +CREATE VIEW "open_issue" AS
    1.87 +  SELECT * FROM "issue" WHERE "closed" ISNULL;
    1.88 +COMMENT ON VIEW "open_issue" IS 'All open issues';
    1.89 +CREATE VIEW "event_seen_by_member" AS
    1.90 +  SELECT
    1.91 +    "member"."id" AS "seen_by_member_id",
    1.92 +    CASE WHEN "event"."state" IN (
    1.93 +      'voting',
    1.94 +      'finished_without_winner',
    1.95 +      'finished_with_winner'
    1.96 +    ) THEN
    1.97 +      'voting'::"notify_level"
    1.98 +    ELSE
    1.99 +      CASE WHEN "event"."state" IN (
   1.100 +        'verification',
   1.101 +        'canceled_after_revocation_during_verification',
   1.102 +        'canceled_no_initiative_admitted'
   1.103 +      ) THEN
   1.104 +        'verification'::"notify_level"
   1.105 +      ELSE
   1.106 +        CASE WHEN "event"."state" IN (
   1.107 +          'discussion',
   1.108 +          'canceled_after_revocation_during_discussion'
   1.109 +        ) THEN
   1.110 +          'discussion'::"notify_level"
   1.111 +        ELSE
   1.112 +          'all'::"notify_level"
   1.113 +        END
   1.114 +      END
   1.115 +    END AS "notify_level",
   1.116 +    "event".*
   1.117 +  FROM "member" CROSS JOIN "event"
   1.118 +  LEFT JOIN "issue"
   1.119 +    ON "event"."issue_id" = "issue"."id"
   1.120 +  LEFT JOIN "membership"
   1.121 +    ON "member"."id" = "membership"."member_id"
   1.122 +    AND "issue"."area_id" = "membership"."area_id"
   1.123 +  LEFT JOIN "interest"
   1.124 +    ON "member"."id" = "interest"."member_id"
   1.125 +    AND "event"."issue_id" = "interest"."issue_id"
   1.126 +  LEFT JOIN "supporter"
   1.127 +    ON "member"."id" = "supporter"."member_id"
   1.128 +    AND "event"."initiative_id" = "supporter"."initiative_id"
   1.129 +  LEFT JOIN "ignored_member"
   1.130 +    ON "member"."id" = "ignored_member"."member_id"
   1.131 +    AND "event"."member_id" = "ignored_member"."other_member_id"
   1.132 +  LEFT JOIN "ignored_initiative"
   1.133 +    ON "member"."id" = "ignored_initiative"."member_id"
   1.134 +    AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
   1.135 +  WHERE (
   1.136 +    "supporter"."member_id" NOTNULL OR
   1.137 +    "interest"."member_id" NOTNULL OR
   1.138 +    ( "membership"."member_id" NOTNULL AND
   1.139 +      "event"."event" IN (
   1.140 +        'issue_state_changed',
   1.141 +        'initiative_created_in_new_issue',
   1.142 +        'initiative_created_in_existing_issue',
   1.143 +        'initiative_revoked' ) ) )
   1.144 +  AND "ignored_member"."member_id" ISNULL
   1.145 +  AND "ignored_initiative"."member_id" ISNULL;
   1.146 +COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support, but ignoring members "notify_level"';
   1.147 +CREATE VIEW "selected_event_seen_by_member" AS
   1.148 +  SELECT
   1.149 +    "member"."id" AS "seen_by_member_id",
   1.150 +    CASE WHEN "event"."state" IN (
   1.151 +      'voting',
   1.152 +      'finished_without_winner',
   1.153 +      'finished_with_winner'
   1.154 +    ) THEN
   1.155 +      'voting'::"notify_level"
   1.156 +    ELSE
   1.157 +      CASE WHEN "event"."state" IN (
   1.158 +        'verification',
   1.159 +        'canceled_after_revocation_during_verification',
   1.160 +        'canceled_no_initiative_admitted'
   1.161 +      ) THEN
   1.162 +        'verification'::"notify_level"
   1.163 +      ELSE
   1.164 +        CASE WHEN "event"."state" IN (
   1.165 +          'discussion',
   1.166 +          'canceled_after_revocation_during_discussion'
   1.167 +        ) THEN
   1.168 +          'discussion'::"notify_level"
   1.169 +        ELSE
   1.170 +          'all'::"notify_level"
   1.171 +        END
   1.172 +      END
   1.173 +    END AS "notify_level",
   1.174 +    "event".*
   1.175 +  FROM "member" CROSS JOIN "event"
   1.176 +  LEFT JOIN "issue"
   1.177 +    ON "event"."issue_id" = "issue"."id"
   1.178 +  LEFT JOIN "membership"
   1.179 +    ON "member"."id" = "membership"."member_id"
   1.180 +    AND "issue"."area_id" = "membership"."area_id"
   1.181 +  LEFT JOIN "interest"
   1.182 +    ON "member"."id" = "interest"."member_id"
   1.183 +    AND "event"."issue_id" = "interest"."issue_id"
   1.184 +  LEFT JOIN "supporter"
   1.185 +    ON "member"."id" = "supporter"."member_id"
   1.186 +    AND "event"."initiative_id" = "supporter"."initiative_id"
   1.187 +  LEFT JOIN "ignored_member"
   1.188 +    ON "member"."id" = "ignored_member"."member_id"
   1.189 +    AND "event"."member_id" = "ignored_member"."other_member_id"
   1.190 +  LEFT JOIN "ignored_initiative"
   1.191 +    ON "member"."id" = "ignored_initiative"."member_id"
   1.192 +    AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
   1.193 +  WHERE (
   1.194 +    ( "member"."notify_level" >= 'all' ) OR
   1.195 +    ( "member"."notify_level" >= 'voting' AND
   1.196 +      "event"."state" IN (
   1.197 +        'voting',
   1.198 +        'finished_without_winner',
   1.199 +        'finished_with_winner' ) ) OR
   1.200 +    ( "member"."notify_level" >= 'verification' AND
   1.201 +      "event"."state" IN (
   1.202 +        'verification',
   1.203 +        'canceled_after_revocation_during_verification',
   1.204 +        'canceled_no_initiative_admitted' ) ) OR
   1.205 +    ( "member"."notify_level" >= 'discussion' AND
   1.206 +      "event"."state" IN (
   1.207 +        'discussion',
   1.208 +        'canceled_after_revocation_during_discussion' ) ) )
   1.209 +  AND (
   1.210 +    "supporter"."member_id" NOTNULL OR
   1.211 +    "interest"."member_id" NOTNULL OR
   1.212 +    ( "membership"."member_id" NOTNULL AND
   1.213 +      "event"."event" IN (
   1.214 +        'issue_state_changed',
   1.215 +        'initiative_created_in_new_issue',
   1.216 +        'initiative_created_in_existing_issue',
   1.217 +        'initiative_revoked' ) ) )
   1.218 +  AND "ignored_member"."member_id" ISNULL
   1.219 +  AND "ignored_initiative"."member_id" ISNULL;
   1.220 +COMMENT ON VIEW "selected_event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests, support and members "notify_level"';
   1.221 +
   1.222 +ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
   1.223 +COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key';
   1.224 +
   1.225 +CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()
   1.226 +  RETURNS TRIGGER
   1.227 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.228 +    BEGIN
   1.229 +      IF NEW."state" != OLD."state" THEN
   1.230 +        INSERT INTO "event" ("event", "issue_id", "state")
   1.231 +          VALUES ('issue_state_changed', NEW."id", NEW."state");
   1.232 +      END IF;
   1.233 +      RETURN NULL;
   1.234 +    END;
   1.235 +  $$;
   1.236 +
   1.237 +CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
   1.238 +  RETURNS TRIGGER
   1.239 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.240 +    DECLARE
   1.241 +      "issue_id_v" "issue"."id"%TYPE;
   1.242 +      "issue_row"  "issue"%ROWTYPE;
   1.243 +    BEGIN
   1.244 +      IF TG_OP = 'DELETE' THEN
   1.245 +        "issue_id_v" := OLD."issue_id";
   1.246 +      ELSE
   1.247 +        "issue_id_v" := NEW."issue_id";
   1.248 +      END IF;
   1.249 +      SELECT INTO "issue_row" * FROM "issue"
   1.250 +        WHERE "id" = "issue_id_v" FOR SHARE;
   1.251 +      IF "issue_row"."closed" NOTNULL THEN
   1.252 +        IF
   1.253 +          TG_RELID = 'direct_voter'::regclass AND
   1.254 +          TG_OP = 'UPDATE'
   1.255 +        THEN
   1.256 +          IF
   1.257 +            OLD."issue_id"  = NEW."issue_id"  AND
   1.258 +            OLD."member_id" = NEW."member_id" AND
   1.259 +            OLD."weight" = NEW."weight"
   1.260 +          THEN
   1.261 +            RETURN NULL;  -- allows changing of voter comment
   1.262 +          END IF;
   1.263 +        END IF;
   1.264 +        RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
   1.265 +      ELSIF
   1.266 +        "issue_row"."state" = 'voting' AND
   1.267 +        "issue_row"."phase_finished" NOTNULL
   1.268 +      THEN
   1.269 +        IF TG_RELID = 'vote'::regclass THEN
   1.270 +          RAISE EXCEPTION 'Tried to modify data after voting has been closed.';
   1.271 +        END IF;
   1.272 +      END IF;
   1.273 +      RETURN NULL;
   1.274 +    END;
   1.275 +  $$;
   1.276 +
   1.277 +CREATE OR REPLACE VIEW "battle_view" AS
   1.278 +  SELECT
   1.279 +    "issue"."id" AS "issue_id",
   1.280 +    "winning_initiative"."id" AS "winning_initiative_id",
   1.281 +    "losing_initiative"."id" AS "losing_initiative_id",
   1.282 +    sum(
   1.283 +      CASE WHEN
   1.284 +        coalesce("better_vote"."grade", 0) >
   1.285 +        coalesce("worse_vote"."grade", 0)
   1.286 +      THEN "direct_voter"."weight" ELSE 0 END
   1.287 +    ) AS "count"
   1.288 +  FROM "issue"
   1.289 +  LEFT JOIN "direct_voter"
   1.290 +  ON "issue"."id" = "direct_voter"."issue_id"
   1.291 +  JOIN "battle_participant" AS "winning_initiative"
   1.292 +    ON "issue"."id" = "winning_initiative"."issue_id"
   1.293 +  JOIN "battle_participant" AS "losing_initiative"
   1.294 +    ON "issue"."id" = "losing_initiative"."issue_id"
   1.295 +  LEFT JOIN "vote" AS "better_vote"
   1.296 +    ON "direct_voter"."member_id" = "better_vote"."member_id"
   1.297 +    AND "winning_initiative"."id" = "better_vote"."initiative_id"
   1.298 +  LEFT JOIN "vote" AS "worse_vote"
   1.299 +    ON "direct_voter"."member_id" = "worse_vote"."member_id"
   1.300 +    AND "losing_initiative"."id" = "worse_vote"."initiative_id"
   1.301 +  WHERE "issue"."state" = 'voting'
   1.302 +  AND "issue"."phase_finished" NOTNULL
   1.303 +  AND (
   1.304 +    "winning_initiative"."id" != "losing_initiative"."id" OR
   1.305 +    ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
   1.306 +      ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
   1.307 +  GROUP BY
   1.308 +    "issue"."id",
   1.309 +    "winning_initiative"."id",
   1.310 +    "losing_initiative"."id";
   1.311 +
   1.312 +DROP VIEW "timeline";
   1.313 +DROP VIEW "timeline_issue";
   1.314 +DROP VIEW "timeline_initiative";
   1.315 +DROP VIEW "timeline_draft";
   1.316 +DROP VIEW "timeline_suggestion";
   1.317 +DROP TYPE "timeline_event";
   1.318 +
   1.319 +DROP TRIGGER "share_row_lock_issue" ON "initiative";
   1.320 +DROP TRIGGER "share_row_lock_issue" ON "interest";
   1.321 +DROP TRIGGER "share_row_lock_issue" ON "supporter";
   1.322 +DROP TRIGGER "share_row_lock_issue_via_initiative" ON "opinion";
   1.323 +DROP TRIGGER "share_row_lock_issue" ON "direct_voter";
   1.324 +DROP TRIGGER "share_row_lock_issue" ON "delegating_voter";
   1.325 +DROP TRIGGER "share_row_lock_issue" ON "vote";
   1.326 +DROP FUNCTION "share_row_lock_issue_trigger"();
   1.327 +
   1.328 +CREATE FUNCTION "require_transaction_isolation"()
   1.329 +  RETURNS VOID
   1.330 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.331 +    BEGIN
   1.332 +      IF
   1.333 +        current_setting('transaction_isolation') NOT IN
   1.334 +        ('repeatable read', 'serializable')
   1.335 +      THEN
   1.336 +        RAISE EXCEPTION 'Insufficient transaction isolation level';
   1.337 +      END IF;
   1.338 +      RETURN;
   1.339 +    END;
   1.340 +  $$;
   1.341 +
   1.342 +CREATE FUNCTION "dont_require_transaction_isolation"()
   1.343 +  RETURNS VOID
   1.344 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.345 +    BEGIN
   1.346 +      IF
   1.347 +        current_setting('transaction_isolation') IN
   1.348 +        ('repeatable read', 'serializable')
   1.349 +      THEN
   1.350 +        RAISE WARNING 'Unneccessary transaction isolation level: %',
   1.351 +          current_setting('transaction_isolation');
   1.352 +      END IF;
   1.353 +      RETURN;
   1.354 +    END;
   1.355 +  $$;
   1.356 +
   1.357 +CREATE OR REPLACE FUNCTION "check_activity"()
   1.358 +  RETURNS VOID
   1.359 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.360 +    DECLARE
   1.361 +      "system_setting_row" "system_setting"%ROWTYPE;
   1.362 +    BEGIN
   1.363 +      PERFORM "dont_require_transaction_isolation"();
   1.364 +      SELECT * INTO "system_setting_row" FROM "system_setting";
   1.365 +      IF "system_setting_row"."member_ttl" NOTNULL THEN
   1.366 +        UPDATE "member" SET "active" = FALSE
   1.367 +          WHERE "active" = TRUE
   1.368 +          AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
   1.369 +      END IF;
   1.370 +      RETURN;
   1.371 +    END;
   1.372 +  $$;
   1.373 +
   1.374 +CREATE OR REPLACE FUNCTION "calculate_member_counts"()
   1.375 +  RETURNS VOID
   1.376 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.377 +    BEGIN
   1.378 +      PERFORM "require_transaction_isolation"();
   1.379 +      DELETE FROM "member_count";
   1.380 +      INSERT INTO "member_count" ("total_count")
   1.381 +        SELECT "total_count" FROM "member_count_view";
   1.382 +      UPDATE "unit" SET "member_count" = "view"."member_count"
   1.383 +        FROM "unit_member_count" AS "view"
   1.384 +        WHERE "view"."unit_id" = "unit"."id";
   1.385 +      UPDATE "area" SET
   1.386 +        "direct_member_count" = "view"."direct_member_count",
   1.387 +        "member_weight"       = "view"."member_weight"
   1.388 +        FROM "area_member_count" AS "view"
   1.389 +        WHERE "view"."area_id" = "area"."id";
   1.390 +      RETURN;
   1.391 +    END;
   1.392 +  $$;
   1.393 +
   1.394 +CREATE VIEW "remaining_harmonic_supporter_weight" AS
   1.395 +  SELECT
   1.396 +    "direct_interest_snapshot"."issue_id",
   1.397 +    "direct_interest_snapshot"."event",
   1.398 +    "direct_interest_snapshot"."member_id",
   1.399 +    "direct_interest_snapshot"."weight" AS "weight_num",
   1.400 +    count("initiative"."id") AS "weight_den"
   1.401 +  FROM "issue"
   1.402 +  JOIN "direct_interest_snapshot"
   1.403 +    ON "issue"."id" = "direct_interest_snapshot"."issue_id"
   1.404 +    AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
   1.405 +  JOIN "initiative"
   1.406 +    ON "issue"."id" = "initiative"."issue_id"
   1.407 +    AND "initiative"."harmonic_weight" ISNULL
   1.408 +  JOIN "direct_supporter_snapshot"
   1.409 +    ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
   1.410 +    AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
   1.411 +    AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
   1.412 +    AND (
   1.413 +      "direct_supporter_snapshot"."satisfied" = TRUE OR
   1.414 +      coalesce("initiative"."admitted", FALSE) = FALSE
   1.415 +    )
   1.416 +  GROUP BY
   1.417 +    "direct_interest_snapshot"."issue_id",
   1.418 +    "direct_interest_snapshot"."event",
   1.419 +    "direct_interest_snapshot"."member_id",
   1.420 +    "direct_interest_snapshot"."weight";
   1.421 +COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
   1.422 +
   1.423 +CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
   1.424 +  SELECT
   1.425 +    "initiative"."issue_id",
   1.426 +    "initiative"."id" AS "initiative_id",
   1.427 +    "initiative"."admitted",
   1.428 +    sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
   1.429 +    "remaining_harmonic_supporter_weight"."weight_den"
   1.430 +  FROM "remaining_harmonic_supporter_weight"
   1.431 +  JOIN "initiative"
   1.432 +    ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
   1.433 +    AND "initiative"."harmonic_weight" ISNULL
   1.434 +  JOIN "direct_supporter_snapshot"
   1.435 +    ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
   1.436 +    AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
   1.437 +    AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
   1.438 +    AND (
   1.439 +      "direct_supporter_snapshot"."satisfied" = TRUE OR
   1.440 +      coalesce("initiative"."admitted", FALSE) = FALSE
   1.441 +    )
   1.442 +  GROUP BY
   1.443 +    "initiative"."issue_id",
   1.444 +    "initiative"."id",
   1.445 +    "initiative"."admitted",
   1.446 +    "remaining_harmonic_supporter_weight"."weight_den";
   1.447 +COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
   1.448 +
   1.449 +CREATE FUNCTION "set_harmonic_initiative_weights"
   1.450 +  ( "issue_id_p" "issue"."id"%TYPE )
   1.451 +  RETURNS VOID
   1.452 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.453 +    DECLARE
   1.454 +      "weight_row"   "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
   1.455 +      "i"            INT4;
   1.456 +      "count_v"      INT4;
   1.457 +      "summand_v"    FLOAT;
   1.458 +      "id_ary"       INT4[];
   1.459 +      "weight_ary"   FLOAT[];
   1.460 +      "min_weight_v" FLOAT;
   1.461 +    BEGIN
   1.462 +      PERFORM "require_transaction_isolation"();
   1.463 +      UPDATE "initiative" SET "harmonic_weight" = NULL
   1.464 +        WHERE "issue_id" = "issue_id_p";
   1.465 +      LOOP
   1.466 +        "min_weight_v" := NULL;
   1.467 +        "i" := 0;
   1.468 +        "count_v" := 0;
   1.469 +        FOR "weight_row" IN
   1.470 +          SELECT * FROM "remaining_harmonic_initiative_weight_summands"
   1.471 +          WHERE "issue_id" = "issue_id_p"
   1.472 +          AND (
   1.473 +            coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
   1.474 +              SELECT NULL FROM "initiative"
   1.475 +              WHERE "issue_id" = "issue_id_p"
   1.476 +              AND "harmonic_weight" ISNULL
   1.477 +              AND coalesce("admitted", FALSE) = FALSE
   1.478 +            )
   1.479 +          )
   1.480 +          ORDER BY "initiative_id" DESC, "weight_den" DESC
   1.481 +          -- NOTE: non-admitted initiatives placed first (at last positions),
   1.482 +          --       latest initiatives treated worse in case of tie
   1.483 +        LOOP
   1.484 +          "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
   1.485 +          IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
   1.486 +            "i" := "i" + 1;
   1.487 +            "count_v" := "i";
   1.488 +            "id_ary"["i"] := "weight_row"."initiative_id";
   1.489 +            "weight_ary"["i"] := "summand_v";
   1.490 +          ELSE
   1.491 +            "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
   1.492 +          END IF;
   1.493 +        END LOOP;
   1.494 +        EXIT WHEN "count_v" = 0;
   1.495 +        "i" := 1;
   1.496 +        LOOP
   1.497 +          "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
   1.498 +          IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
   1.499 +            "min_weight_v" := "weight_ary"["i"];
   1.500 +          END IF;
   1.501 +          "i" := "i" + 1;
   1.502 +          EXIT WHEN "i" > "count_v";
   1.503 +        END LOOP;
   1.504 +        "i" := 1;
   1.505 +        LOOP
   1.506 +          IF "weight_ary"["i"] = "min_weight_v" THEN
   1.507 +            UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
   1.508 +              WHERE "id" = "id_ary"["i"];
   1.509 +            EXIT;
   1.510 +          END IF;
   1.511 +          "i" := "i" + 1;
   1.512 +        END LOOP;
   1.513 +      END LOOP;
   1.514 +      UPDATE "initiative" SET "harmonic_weight" = 0
   1.515 +        WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
   1.516 +    END;
   1.517 +  $$;
   1.518 +COMMENT ON FUNCTION "set_harmonic_initiative_weights"
   1.519 +  ( "issue"."id"%TYPE )
   1.520 +  IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
   1.521 +
   1.522 +CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_population_snapshot"
   1.523 +  ( "issue_id_p"            "issue"."id"%TYPE,
   1.524 +    "member_id_p"           "member"."id"%TYPE,
   1.525 +    "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
   1.526 +  RETURNS "direct_population_snapshot"."weight"%TYPE
   1.527 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.528 +    DECLARE
   1.529 +      "issue_delegation_row"  "issue_delegation"%ROWTYPE;
   1.530 +      "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
   1.531 +      "weight_v"              INT4;
   1.532 +      "sub_weight_v"          INT4;
   1.533 +    BEGIN
   1.534 +      PERFORM "require_transaction_isolation"();
   1.535 +      "weight_v" := 0;
   1.536 +      FOR "issue_delegation_row" IN
   1.537 +        SELECT * FROM "issue_delegation"
   1.538 +        WHERE "trustee_id" = "member_id_p"
   1.539 +        AND "issue_id" = "issue_id_p"
   1.540 +      LOOP
   1.541 +        IF NOT EXISTS (
   1.542 +          SELECT NULL FROM "direct_population_snapshot"
   1.543 +          WHERE "issue_id" = "issue_id_p"
   1.544 +          AND "event" = 'periodic'
   1.545 +          AND "member_id" = "issue_delegation_row"."truster_id"
   1.546 +        ) AND NOT EXISTS (
   1.547 +          SELECT NULL FROM "delegating_population_snapshot"
   1.548 +          WHERE "issue_id" = "issue_id_p"
   1.549 +          AND "event" = 'periodic'
   1.550 +          AND "member_id" = "issue_delegation_row"."truster_id"
   1.551 +        ) THEN
   1.552 +          "delegate_member_ids_v" :=
   1.553 +            "member_id_p" || "delegate_member_ids_p";
   1.554 +          INSERT INTO "delegating_population_snapshot" (
   1.555 +              "issue_id",
   1.556 +              "event",
   1.557 +              "member_id",
   1.558 +              "scope",
   1.559 +              "delegate_member_ids"
   1.560 +            ) VALUES (
   1.561 +              "issue_id_p",
   1.562 +              'periodic',
   1.563 +              "issue_delegation_row"."truster_id",
   1.564 +              "issue_delegation_row"."scope",
   1.565 +              "delegate_member_ids_v"
   1.566 +            );
   1.567 +          "sub_weight_v" := 1 +
   1.568 +            "weight_of_added_delegations_for_population_snapshot"(
   1.569 +              "issue_id_p",
   1.570 +              "issue_delegation_row"."truster_id",
   1.571 +              "delegate_member_ids_v"
   1.572 +            );
   1.573 +          UPDATE "delegating_population_snapshot"
   1.574 +            SET "weight" = "sub_weight_v"
   1.575 +            WHERE "issue_id" = "issue_id_p"
   1.576 +            AND "event" = 'periodic'
   1.577 +            AND "member_id" = "issue_delegation_row"."truster_id";
   1.578 +          "weight_v" := "weight_v" + "sub_weight_v";
   1.579 +        END IF;
   1.580 +      END LOOP;
   1.581 +      RETURN "weight_v";
   1.582 +    END;
   1.583 +  $$;
   1.584 +
   1.585 +CREATE OR REPLACE FUNCTION "create_population_snapshot"
   1.586 +  ( "issue_id_p" "issue"."id"%TYPE )
   1.587 +  RETURNS VOID
   1.588 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.589 +    DECLARE
   1.590 +      "member_id_v" "member"."id"%TYPE;
   1.591 +    BEGIN
   1.592 +      PERFORM "require_transaction_isolation"();
   1.593 +      DELETE FROM "direct_population_snapshot"
   1.594 +        WHERE "issue_id" = "issue_id_p"
   1.595 +        AND "event" = 'periodic';
   1.596 +      DELETE FROM "delegating_population_snapshot"
   1.597 +        WHERE "issue_id" = "issue_id_p"
   1.598 +        AND "event" = 'periodic';
   1.599 +      INSERT INTO "direct_population_snapshot"
   1.600 +        ("issue_id", "event", "member_id")
   1.601 +        SELECT
   1.602 +          "issue_id_p"                 AS "issue_id",
   1.603 +          'periodic'::"snapshot_event" AS "event",
   1.604 +          "member"."id"                AS "member_id"
   1.605 +        FROM "issue"
   1.606 +        JOIN "area" ON "issue"."area_id" = "area"."id"
   1.607 +        JOIN "membership" ON "area"."id" = "membership"."area_id"
   1.608 +        JOIN "member" ON "membership"."member_id" = "member"."id"
   1.609 +        JOIN "privilege"
   1.610 +          ON "privilege"."unit_id" = "area"."unit_id"
   1.611 +          AND "privilege"."member_id" = "member"."id"
   1.612 +        WHERE "issue"."id" = "issue_id_p"
   1.613 +        AND "member"."active" AND "privilege"."voting_right"
   1.614 +        UNION
   1.615 +        SELECT
   1.616 +          "issue_id_p"                 AS "issue_id",
   1.617 +          'periodic'::"snapshot_event" AS "event",
   1.618 +          "member"."id"                AS "member_id"
   1.619 +        FROM "issue"
   1.620 +        JOIN "area" ON "issue"."area_id" = "area"."id"
   1.621 +        JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   1.622 +        JOIN "member" ON "interest"."member_id" = "member"."id"
   1.623 +        JOIN "privilege"
   1.624 +          ON "privilege"."unit_id" = "area"."unit_id"
   1.625 +          AND "privilege"."member_id" = "member"."id"
   1.626 +        WHERE "issue"."id" = "issue_id_p"
   1.627 +        AND "member"."active" AND "privilege"."voting_right";
   1.628 +      FOR "member_id_v" IN
   1.629 +        SELECT "member_id" FROM "direct_population_snapshot"
   1.630 +        WHERE "issue_id" = "issue_id_p"
   1.631 +        AND "event" = 'periodic'
   1.632 +      LOOP
   1.633 +        UPDATE "direct_population_snapshot" SET
   1.634 +          "weight" = 1 +
   1.635 +            "weight_of_added_delegations_for_population_snapshot"(
   1.636 +              "issue_id_p",
   1.637 +              "member_id_v",
   1.638 +              '{}'
   1.639 +            )
   1.640 +          WHERE "issue_id" = "issue_id_p"
   1.641 +          AND "event" = 'periodic'
   1.642 +          AND "member_id" = "member_id_v";
   1.643 +      END LOOP;
   1.644 +      RETURN;
   1.645 +    END;
   1.646 +  $$;
   1.647 +
   1.648 +CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
   1.649 +  ( "issue_id_p"            "issue"."id"%TYPE,
   1.650 +    "member_id_p"           "member"."id"%TYPE,
   1.651 +    "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
   1.652 +  RETURNS "direct_interest_snapshot"."weight"%TYPE
   1.653 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.654 +    DECLARE
   1.655 +      "issue_delegation_row"  "issue_delegation"%ROWTYPE;
   1.656 +      "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
   1.657 +      "weight_v"              INT4;
   1.658 +      "sub_weight_v"          INT4;
   1.659 +    BEGIN
   1.660 +      PERFORM "require_transaction_isolation"();
   1.661 +      "weight_v" := 0;
   1.662 +      FOR "issue_delegation_row" IN
   1.663 +        SELECT * FROM "issue_delegation"
   1.664 +        WHERE "trustee_id" = "member_id_p"
   1.665 +        AND "issue_id" = "issue_id_p"
   1.666 +      LOOP
   1.667 +        IF NOT EXISTS (
   1.668 +          SELECT NULL FROM "direct_interest_snapshot"
   1.669 +          WHERE "issue_id" = "issue_id_p"
   1.670 +          AND "event" = 'periodic'
   1.671 +          AND "member_id" = "issue_delegation_row"."truster_id"
   1.672 +        ) AND NOT EXISTS (
   1.673 +          SELECT NULL FROM "delegating_interest_snapshot"
   1.674 +          WHERE "issue_id" = "issue_id_p"
   1.675 +          AND "event" = 'periodic'
   1.676 +          AND "member_id" = "issue_delegation_row"."truster_id"
   1.677 +        ) THEN
   1.678 +          "delegate_member_ids_v" :=
   1.679 +            "member_id_p" || "delegate_member_ids_p";
   1.680 +          INSERT INTO "delegating_interest_snapshot" (
   1.681 +              "issue_id",
   1.682 +              "event",
   1.683 +              "member_id",
   1.684 +              "scope",
   1.685 +              "delegate_member_ids"
   1.686 +            ) VALUES (
   1.687 +              "issue_id_p",
   1.688 +              'periodic',
   1.689 +              "issue_delegation_row"."truster_id",
   1.690 +              "issue_delegation_row"."scope",
   1.691 +              "delegate_member_ids_v"
   1.692 +            );
   1.693 +          "sub_weight_v" := 1 +
   1.694 +            "weight_of_added_delegations_for_interest_snapshot"(
   1.695 +              "issue_id_p",
   1.696 +              "issue_delegation_row"."truster_id",
   1.697 +              "delegate_member_ids_v"
   1.698 +            );
   1.699 +          UPDATE "delegating_interest_snapshot"
   1.700 +            SET "weight" = "sub_weight_v"
   1.701 +            WHERE "issue_id" = "issue_id_p"
   1.702 +            AND "event" = 'periodic'
   1.703 +            AND "member_id" = "issue_delegation_row"."truster_id";
   1.704 +          "weight_v" := "weight_v" + "sub_weight_v";
   1.705 +        END IF;
   1.706 +      END LOOP;
   1.707 +      RETURN "weight_v";
   1.708 +    END;
   1.709 +  $$;
   1.710 +
   1.711 +CREATE OR REPLACE FUNCTION "create_interest_snapshot"
   1.712 +  ( "issue_id_p" "issue"."id"%TYPE )
   1.713 +  RETURNS VOID
   1.714 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.715 +    DECLARE
   1.716 +      "member_id_v" "member"."id"%TYPE;
   1.717 +    BEGIN
   1.718 +      PERFORM "require_transaction_isolation"();
   1.719 +      DELETE FROM "direct_interest_snapshot"
   1.720 +        WHERE "issue_id" = "issue_id_p"
   1.721 +        AND "event" = 'periodic';
   1.722 +      DELETE FROM "delegating_interest_snapshot"
   1.723 +        WHERE "issue_id" = "issue_id_p"
   1.724 +        AND "event" = 'periodic';
   1.725 +      DELETE FROM "direct_supporter_snapshot"
   1.726 +        USING "initiative"  -- NOTE: due to missing index on issue_id
   1.727 +        WHERE "initiative"."issue_id" = "issue_id_p"
   1.728 +        AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
   1.729 +        AND "direct_supporter_snapshot"."event" = 'periodic';
   1.730 +      INSERT INTO "direct_interest_snapshot"
   1.731 +        ("issue_id", "event", "member_id")
   1.732 +        SELECT
   1.733 +          "issue_id_p"  AS "issue_id",
   1.734 +          'periodic'    AS "event",
   1.735 +          "member"."id" AS "member_id"
   1.736 +        FROM "issue"
   1.737 +        JOIN "area" ON "issue"."area_id" = "area"."id"
   1.738 +        JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   1.739 +        JOIN "member" ON "interest"."member_id" = "member"."id"
   1.740 +        JOIN "privilege"
   1.741 +          ON "privilege"."unit_id" = "area"."unit_id"
   1.742 +          AND "privilege"."member_id" = "member"."id"
   1.743 +        WHERE "issue"."id" = "issue_id_p"
   1.744 +        AND "member"."active" AND "privilege"."voting_right";
   1.745 +      FOR "member_id_v" IN
   1.746 +        SELECT "member_id" FROM "direct_interest_snapshot"
   1.747 +        WHERE "issue_id" = "issue_id_p"
   1.748 +        AND "event" = 'periodic'
   1.749 +      LOOP
   1.750 +        UPDATE "direct_interest_snapshot" SET
   1.751 +          "weight" = 1 +
   1.752 +            "weight_of_added_delegations_for_interest_snapshot"(
   1.753 +              "issue_id_p",
   1.754 +              "member_id_v",
   1.755 +              '{}'
   1.756 +            )
   1.757 +          WHERE "issue_id" = "issue_id_p"
   1.758 +          AND "event" = 'periodic'
   1.759 +          AND "member_id" = "member_id_v";
   1.760 +      END LOOP;
   1.761 +      INSERT INTO "direct_supporter_snapshot"
   1.762 +        ( "issue_id", "initiative_id", "event", "member_id",
   1.763 +          "draft_id", "informed", "satisfied" )
   1.764 +        SELECT
   1.765 +          "issue_id_p"            AS "issue_id",
   1.766 +          "initiative"."id"       AS "initiative_id",
   1.767 +          'periodic'              AS "event",
   1.768 +          "supporter"."member_id" AS "member_id",
   1.769 +          "supporter"."draft_id"  AS "draft_id",
   1.770 +          "supporter"."draft_id" = "current_draft"."id" AS "informed",
   1.771 +          NOT EXISTS (
   1.772 +            SELECT NULL FROM "critical_opinion"
   1.773 +            WHERE "initiative_id" = "initiative"."id"
   1.774 +            AND "member_id" = "supporter"."member_id"
   1.775 +          ) AS "satisfied"
   1.776 +        FROM "initiative"
   1.777 +        JOIN "supporter"
   1.778 +        ON "supporter"."initiative_id" = "initiative"."id"
   1.779 +        JOIN "current_draft"
   1.780 +        ON "initiative"."id" = "current_draft"."initiative_id"
   1.781 +        JOIN "direct_interest_snapshot"
   1.782 +        ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
   1.783 +        AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
   1.784 +        AND "event" = 'periodic'
   1.785 +        WHERE "initiative"."issue_id" = "issue_id_p";
   1.786 +      RETURN;
   1.787 +    END;
   1.788 +  $$;
   1.789 +
   1.790 +CREATE OR REPLACE FUNCTION "create_snapshot"
   1.791 +  ( "issue_id_p" "issue"."id"%TYPE )
   1.792 +  RETURNS VOID
   1.793 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.794 +    DECLARE
   1.795 +      "initiative_id_v"    "initiative"."id"%TYPE;
   1.796 +      "suggestion_id_v"    "suggestion"."id"%TYPE;
   1.797 +    BEGIN
   1.798 +      PERFORM "require_transaction_isolation"();
   1.799 +      PERFORM "create_population_snapshot"("issue_id_p");
   1.800 +      PERFORM "create_interest_snapshot"("issue_id_p");
   1.801 +      UPDATE "issue" SET
   1.802 +        "snapshot" = coalesce("phase_finished", now()),
   1.803 +        "latest_snapshot_event" = 'periodic',
   1.804 +        "population" = (
   1.805 +          SELECT coalesce(sum("weight"), 0)
   1.806 +          FROM "direct_population_snapshot"
   1.807 +          WHERE "issue_id" = "issue_id_p"
   1.808 +          AND "event" = 'periodic'
   1.809 +        )
   1.810 +        WHERE "id" = "issue_id_p";
   1.811 +      FOR "initiative_id_v" IN
   1.812 +        SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
   1.813 +      LOOP
   1.814 +        UPDATE "initiative" SET
   1.815 +          "supporter_count" = (
   1.816 +            SELECT coalesce(sum("di"."weight"), 0)
   1.817 +            FROM "direct_interest_snapshot" AS "di"
   1.818 +            JOIN "direct_supporter_snapshot" AS "ds"
   1.819 +            ON "di"."member_id" = "ds"."member_id"
   1.820 +            WHERE "di"."issue_id" = "issue_id_p"
   1.821 +            AND "di"."event" = 'periodic'
   1.822 +            AND "ds"."initiative_id" = "initiative_id_v"
   1.823 +            AND "ds"."event" = 'periodic'
   1.824 +          ),
   1.825 +          "informed_supporter_count" = (
   1.826 +            SELECT coalesce(sum("di"."weight"), 0)
   1.827 +            FROM "direct_interest_snapshot" AS "di"
   1.828 +            JOIN "direct_supporter_snapshot" AS "ds"
   1.829 +            ON "di"."member_id" = "ds"."member_id"
   1.830 +            WHERE "di"."issue_id" = "issue_id_p"
   1.831 +            AND "di"."event" = 'periodic'
   1.832 +            AND "ds"."initiative_id" = "initiative_id_v"
   1.833 +            AND "ds"."event" = 'periodic'
   1.834 +            AND "ds"."informed"
   1.835 +          ),
   1.836 +          "satisfied_supporter_count" = (
   1.837 +            SELECT coalesce(sum("di"."weight"), 0)
   1.838 +            FROM "direct_interest_snapshot" AS "di"
   1.839 +            JOIN "direct_supporter_snapshot" AS "ds"
   1.840 +            ON "di"."member_id" = "ds"."member_id"
   1.841 +            WHERE "di"."issue_id" = "issue_id_p"
   1.842 +            AND "di"."event" = 'periodic'
   1.843 +            AND "ds"."initiative_id" = "initiative_id_v"
   1.844 +            AND "ds"."event" = 'periodic'
   1.845 +            AND "ds"."satisfied"
   1.846 +          ),
   1.847 +          "satisfied_informed_supporter_count" = (
   1.848 +            SELECT coalesce(sum("di"."weight"), 0)
   1.849 +            FROM "direct_interest_snapshot" AS "di"
   1.850 +            JOIN "direct_supporter_snapshot" AS "ds"
   1.851 +            ON "di"."member_id" = "ds"."member_id"
   1.852 +            WHERE "di"."issue_id" = "issue_id_p"
   1.853 +            AND "di"."event" = 'periodic'
   1.854 +            AND "ds"."initiative_id" = "initiative_id_v"
   1.855 +            AND "ds"."event" = 'periodic'
   1.856 +            AND "ds"."informed"
   1.857 +            AND "ds"."satisfied"
   1.858 +          )
   1.859 +          WHERE "id" = "initiative_id_v";
   1.860 +        FOR "suggestion_id_v" IN
   1.861 +          SELECT "id" FROM "suggestion"
   1.862 +          WHERE "initiative_id" = "initiative_id_v"
   1.863 +        LOOP
   1.864 +          UPDATE "suggestion" SET
   1.865 +            "minus2_unfulfilled_count" = (
   1.866 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.867 +              FROM "issue" CROSS JOIN "opinion"
   1.868 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.869 +              ON "snapshot"."issue_id" = "issue"."id"
   1.870 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.871 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.872 +              WHERE "issue"."id" = "issue_id_p"
   1.873 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.874 +              AND "opinion"."degree" = -2
   1.875 +              AND "opinion"."fulfilled" = FALSE
   1.876 +            ),
   1.877 +            "minus2_fulfilled_count" = (
   1.878 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.879 +              FROM "issue" CROSS JOIN "opinion"
   1.880 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.881 +              ON "snapshot"."issue_id" = "issue"."id"
   1.882 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.883 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.884 +              WHERE "issue"."id" = "issue_id_p"
   1.885 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.886 +              AND "opinion"."degree" = -2
   1.887 +              AND "opinion"."fulfilled" = TRUE
   1.888 +            ),
   1.889 +            "minus1_unfulfilled_count" = (
   1.890 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.891 +              FROM "issue" CROSS JOIN "opinion"
   1.892 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.893 +              ON "snapshot"."issue_id" = "issue"."id"
   1.894 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.895 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.896 +              WHERE "issue"."id" = "issue_id_p"
   1.897 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.898 +              AND "opinion"."degree" = -1
   1.899 +              AND "opinion"."fulfilled" = FALSE
   1.900 +            ),
   1.901 +            "minus1_fulfilled_count" = (
   1.902 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.903 +              FROM "issue" CROSS JOIN "opinion"
   1.904 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.905 +              ON "snapshot"."issue_id" = "issue"."id"
   1.906 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.907 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.908 +              WHERE "issue"."id" = "issue_id_p"
   1.909 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.910 +              AND "opinion"."degree" = -1
   1.911 +              AND "opinion"."fulfilled" = TRUE
   1.912 +            ),
   1.913 +            "plus1_unfulfilled_count" = (
   1.914 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.915 +              FROM "issue" CROSS JOIN "opinion"
   1.916 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.917 +              ON "snapshot"."issue_id" = "issue"."id"
   1.918 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.919 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.920 +              WHERE "issue"."id" = "issue_id_p"
   1.921 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.922 +              AND "opinion"."degree" = 1
   1.923 +              AND "opinion"."fulfilled" = FALSE
   1.924 +            ),
   1.925 +            "plus1_fulfilled_count" = (
   1.926 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.927 +              FROM "issue" CROSS JOIN "opinion"
   1.928 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.929 +              ON "snapshot"."issue_id" = "issue"."id"
   1.930 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.931 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.932 +              WHERE "issue"."id" = "issue_id_p"
   1.933 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.934 +              AND "opinion"."degree" = 1
   1.935 +              AND "opinion"."fulfilled" = TRUE
   1.936 +            ),
   1.937 +            "plus2_unfulfilled_count" = (
   1.938 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.939 +              FROM "issue" CROSS JOIN "opinion"
   1.940 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.941 +              ON "snapshot"."issue_id" = "issue"."id"
   1.942 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.943 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.944 +              WHERE "issue"."id" = "issue_id_p"
   1.945 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.946 +              AND "opinion"."degree" = 2
   1.947 +              AND "opinion"."fulfilled" = FALSE
   1.948 +            ),
   1.949 +            "plus2_fulfilled_count" = (
   1.950 +              SELECT coalesce(sum("snapshot"."weight"), 0)
   1.951 +              FROM "issue" CROSS JOIN "opinion"
   1.952 +              JOIN "direct_interest_snapshot" AS "snapshot"
   1.953 +              ON "snapshot"."issue_id" = "issue"."id"
   1.954 +              AND "snapshot"."event" = "issue"."latest_snapshot_event"
   1.955 +              AND "snapshot"."member_id" = "opinion"."member_id"
   1.956 +              WHERE "issue"."id" = "issue_id_p"
   1.957 +              AND "opinion"."suggestion_id" = "suggestion_id_v"
   1.958 +              AND "opinion"."degree" = 2
   1.959 +              AND "opinion"."fulfilled" = TRUE
   1.960 +            )
   1.961 +            WHERE "suggestion"."id" = "suggestion_id_v";
   1.962 +        END LOOP;
   1.963 +      END LOOP;
   1.964 +      RETURN;
   1.965 +    END;
   1.966 +  $$;
   1.967 +
   1.968 +CREATE OR REPLACE FUNCTION "set_snapshot_event"
   1.969 +  ( "issue_id_p" "issue"."id"%TYPE,
   1.970 +    "event_p" "snapshot_event" )
   1.971 +  RETURNS VOID
   1.972 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.973 +    DECLARE
   1.974 +      "event_v" "issue"."latest_snapshot_event"%TYPE;
   1.975 +    BEGIN
   1.976 +      PERFORM "require_transaction_isolation"();
   1.977 +      SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
   1.978 +        WHERE "id" = "issue_id_p" FOR UPDATE;
   1.979 +      UPDATE "issue" SET "latest_snapshot_event" = "event_p"
   1.980 +        WHERE "id" = "issue_id_p";
   1.981 +      UPDATE "direct_population_snapshot" SET "event" = "event_p"
   1.982 +        WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
   1.983 +      UPDATE "delegating_population_snapshot" SET "event" = "event_p"
   1.984 +        WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
   1.985 +      UPDATE "direct_interest_snapshot" SET "event" = "event_p"
   1.986 +        WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
   1.987 +      UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
   1.988 +        WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
   1.989 +      UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
   1.990 +        FROM "initiative"  -- NOTE: due to missing index on issue_id
   1.991 +        WHERE "initiative"."issue_id" = "issue_id_p"
   1.992 +        AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
   1.993 +        AND "direct_supporter_snapshot"."event" = "event_v";
   1.994 +      RETURN;
   1.995 +    END;
   1.996 +  $$;
   1.997 +
   1.998 +DROP FUNCTION "freeze_after_snapshot"("issue"."id"%TYPE);
   1.999 +DROP FUNCTION "manual_freeze"("issue"."id"%TYPE);
  1.1000 +
  1.1001 +CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations"
  1.1002 +  ( "issue_id_p"            "issue"."id"%TYPE,
  1.1003 +    "member_id_p"           "member"."id"%TYPE,
  1.1004 +    "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
  1.1005 +  RETURNS "direct_voter"."weight"%TYPE
  1.1006 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1007 +    DECLARE
  1.1008 +      "issue_delegation_row"  "issue_delegation"%ROWTYPE;
  1.1009 +      "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
  1.1010 +      "weight_v"              INT4;
  1.1011 +      "sub_weight_v"          INT4;
  1.1012 +    BEGIN
  1.1013 +      PERFORM "require_transaction_isolation"();
  1.1014 +      "weight_v" := 0;
  1.1015 +      FOR "issue_delegation_row" IN
  1.1016 +        SELECT * FROM "issue_delegation"
  1.1017 +        WHERE "trustee_id" = "member_id_p"
  1.1018 +        AND "issue_id" = "issue_id_p"
  1.1019 +      LOOP
  1.1020 +        IF NOT EXISTS (
  1.1021 +          SELECT NULL FROM "direct_voter"
  1.1022 +          WHERE "member_id" = "issue_delegation_row"."truster_id"
  1.1023 +          AND "issue_id" = "issue_id_p"
  1.1024 +        ) AND NOT EXISTS (
  1.1025 +          SELECT NULL FROM "delegating_voter"
  1.1026 +          WHERE "member_id" = "issue_delegation_row"."truster_id"
  1.1027 +          AND "issue_id" = "issue_id_p"
  1.1028 +        ) THEN
  1.1029 +          "delegate_member_ids_v" :=
  1.1030 +            "member_id_p" || "delegate_member_ids_p";
  1.1031 +          INSERT INTO "delegating_voter" (
  1.1032 +              "issue_id",
  1.1033 +              "member_id",
  1.1034 +              "scope",
  1.1035 +              "delegate_member_ids"
  1.1036 +            ) VALUES (
  1.1037 +              "issue_id_p",
  1.1038 +              "issue_delegation_row"."truster_id",
  1.1039 +              "issue_delegation_row"."scope",
  1.1040 +              "delegate_member_ids_v"
  1.1041 +            );
  1.1042 +          "sub_weight_v" := 1 +
  1.1043 +            "weight_of_added_vote_delegations"(
  1.1044 +              "issue_id_p",
  1.1045 +              "issue_delegation_row"."truster_id",
  1.1046 +              "delegate_member_ids_v"
  1.1047 +            );
  1.1048 +          UPDATE "delegating_voter"
  1.1049 +            SET "weight" = "sub_weight_v"
  1.1050 +            WHERE "issue_id" = "issue_id_p"
  1.1051 +            AND "member_id" = "issue_delegation_row"."truster_id";
  1.1052 +          "weight_v" := "weight_v" + "sub_weight_v";
  1.1053 +        END IF;
  1.1054 +      END LOOP;
  1.1055 +      RETURN "weight_v";
  1.1056 +    END;
  1.1057 +  $$;
  1.1058 +
  1.1059 +CREATE OR REPLACE FUNCTION "add_vote_delegations"
  1.1060 +  ( "issue_id_p" "issue"."id"%TYPE )
  1.1061 +  RETURNS VOID
  1.1062 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1063 +    DECLARE
  1.1064 +      "member_id_v" "member"."id"%TYPE;
  1.1065 +    BEGIN
  1.1066 +      PERFORM "require_transaction_isolation"();
  1.1067 +      FOR "member_id_v" IN
  1.1068 +        SELECT "member_id" FROM "direct_voter"
  1.1069 +        WHERE "issue_id" = "issue_id_p"
  1.1070 +      LOOP
  1.1071 +        UPDATE "direct_voter" SET
  1.1072 +          "weight" = "weight" + "weight_of_added_vote_delegations"(
  1.1073 +            "issue_id_p",
  1.1074 +            "member_id_v",
  1.1075 +            '{}'
  1.1076 +          )
  1.1077 +          WHERE "member_id" = "member_id_v"
  1.1078 +          AND "issue_id" = "issue_id_p";
  1.1079 +      END LOOP;
  1.1080 +      RETURN;
  1.1081 +    END;
  1.1082 +  $$;
  1.1083 +
  1.1084 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
  1.1085 +  RETURNS VOID
  1.1086 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1087 +    DECLARE
  1.1088 +      "area_id_v"   "area"."id"%TYPE;
  1.1089 +      "unit_id_v"   "unit"."id"%TYPE;
  1.1090 +      "member_id_v" "member"."id"%TYPE;
  1.1091 +    BEGIN
  1.1092 +      PERFORM "require_transaction_isolation"();
  1.1093 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
  1.1094 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
  1.1095 +      -- delete timestamp of voting comment:
  1.1096 +      UPDATE "direct_voter" SET "comment_changed" = NULL
  1.1097 +        WHERE "issue_id" = "issue_id_p";
  1.1098 +      -- delete delegating votes (in cases of manual reset of issue state):
  1.1099 +      DELETE FROM "delegating_voter"
  1.1100 +        WHERE "issue_id" = "issue_id_p";
  1.1101 +      -- delete votes from non-privileged voters:
  1.1102 +      DELETE FROM "direct_voter"
  1.1103 +        USING (
  1.1104 +          SELECT
  1.1105 +            "direct_voter"."member_id"
  1.1106 +          FROM "direct_voter"
  1.1107 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
  1.1108 +          LEFT JOIN "privilege"
  1.1109 +          ON "privilege"."unit_id" = "unit_id_v"
  1.1110 +          AND "privilege"."member_id" = "direct_voter"."member_id"
  1.1111 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
  1.1112 +            "member"."active" = FALSE OR
  1.1113 +            "privilege"."voting_right" ISNULL OR
  1.1114 +            "privilege"."voting_right" = FALSE
  1.1115 +          )
  1.1116 +        ) AS "subquery"
  1.1117 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
  1.1118 +        AND "direct_voter"."member_id" = "subquery"."member_id";
  1.1119 +      -- consider delegations:
  1.1120 +      UPDATE "direct_voter" SET "weight" = 1
  1.1121 +        WHERE "issue_id" = "issue_id_p";
  1.1122 +      PERFORM "add_vote_delegations"("issue_id_p");
  1.1123 +      -- materialize battle_view:
  1.1124 +      -- NOTE: "closed" column of issue must be set at this point
  1.1125 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
  1.1126 +      INSERT INTO "battle" (
  1.1127 +        "issue_id",
  1.1128 +        "winning_initiative_id", "losing_initiative_id",
  1.1129 +        "count"
  1.1130 +      ) SELECT
  1.1131 +        "issue_id",
  1.1132 +        "winning_initiative_id", "losing_initiative_id",
  1.1133 +        "count"
  1.1134 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
  1.1135 +      -- set voter count:
  1.1136 +      UPDATE "issue" SET
  1.1137 +        "voter_count" = (
  1.1138 +          SELECT coalesce(sum("weight"), 0)
  1.1139 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
  1.1140 +        )
  1.1141 +        WHERE "id" = "issue_id_p";
  1.1142 +      -- copy "positive_votes" and "negative_votes" from "battle" table:
  1.1143 +      UPDATE "initiative" SET
  1.1144 +        "positive_votes" = "battle_win"."count",
  1.1145 +        "negative_votes" = "battle_lose"."count"
  1.1146 +        FROM "battle" AS "battle_win", "battle" AS "battle_lose"
  1.1147 +        WHERE
  1.1148 +          "battle_win"."issue_id" = "issue_id_p" AND
  1.1149 +          "battle_win"."winning_initiative_id" = "initiative"."id" AND
  1.1150 +          "battle_win"."losing_initiative_id" ISNULL AND
  1.1151 +          "battle_lose"."issue_id" = "issue_id_p" AND
  1.1152 +          "battle_lose"."losing_initiative_id" = "initiative"."id" AND
  1.1153 +          "battle_lose"."winning_initiative_id" ISNULL;
  1.1154 +    END;
  1.1155 +  $$;
  1.1156 +
  1.1157 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
  1.1158 +  RETURNS VOID
  1.1159 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1160 +    DECLARE
  1.1161 +      "issue_row"         "issue"%ROWTYPE;
  1.1162 +      "policy_row"        "policy"%ROWTYPE;
  1.1163 +      "dimension_v"       INTEGER;
  1.1164 +      "vote_matrix"       INT4[][];  -- absolute votes
  1.1165 +      "matrix"            INT8[][];  -- defeat strength / best paths
  1.1166 +      "i"                 INTEGER;
  1.1167 +      "j"                 INTEGER;
  1.1168 +      "k"                 INTEGER;
  1.1169 +      "battle_row"        "battle"%ROWTYPE;
  1.1170 +      "rank_ary"          INT4[];
  1.1171 +      "rank_v"            INT4;
  1.1172 +      "done_v"            INTEGER;
  1.1173 +      "winners_ary"       INTEGER[];
  1.1174 +      "initiative_id_v"   "initiative"."id"%TYPE;
  1.1175 +    BEGIN
  1.1176 +      PERFORM "require_transaction_isolation"();
  1.1177 +      SELECT * INTO "issue_row"
  1.1178 +        FROM "issue" WHERE "id" = "issue_id_p";
  1.1179 +      SELECT * INTO "policy_row"
  1.1180 +        FROM "policy" WHERE "id" = "issue_row"."policy_id";
  1.1181 +      SELECT count(1) INTO "dimension_v"
  1.1182 +        FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
  1.1183 +      -- Create "vote_matrix" with absolute number of votes in pairwise
  1.1184 +      -- comparison:
  1.1185 +      "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
  1.1186 +      "i" := 1;
  1.1187 +      "j" := 2;
  1.1188 +      FOR "battle_row" IN
  1.1189 +        SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
  1.1190 +        ORDER BY
  1.1191 +        "winning_initiative_id" NULLS LAST,
  1.1192 +        "losing_initiative_id" NULLS LAST
  1.1193 +      LOOP
  1.1194 +        "vote_matrix"["i"]["j"] := "battle_row"."count";
  1.1195 +        IF "j" = "dimension_v" THEN
  1.1196 +          "i" := "i" + 1;
  1.1197 +          "j" := 1;
  1.1198 +        ELSE
  1.1199 +          "j" := "j" + 1;
  1.1200 +          IF "j" = "i" THEN
  1.1201 +            "j" := "j" + 1;
  1.1202 +          END IF;
  1.1203 +        END IF;
  1.1204 +      END LOOP;
  1.1205 +      IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
  1.1206 +        RAISE EXCEPTION 'Wrong battle count (should not happen)';
  1.1207 +      END IF;
  1.1208 +      -- Store defeat strengths in "matrix" using "defeat_strength"
  1.1209 +      -- function:
  1.1210 +      "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
  1.1211 +      "i" := 1;
  1.1212 +      LOOP
  1.1213 +        "j" := 1;
  1.1214 +        LOOP
  1.1215 +          IF "i" != "j" THEN
  1.1216 +            "matrix"["i"]["j"] := "defeat_strength"(
  1.1217 +              "vote_matrix"["i"]["j"],
  1.1218 +              "vote_matrix"["j"]["i"]
  1.1219 +            );
  1.1220 +          END IF;
  1.1221 +          EXIT WHEN "j" = "dimension_v";
  1.1222 +          "j" := "j" + 1;
  1.1223 +        END LOOP;
  1.1224 +        EXIT WHEN "i" = "dimension_v";
  1.1225 +        "i" := "i" + 1;
  1.1226 +      END LOOP;
  1.1227 +      -- Find best paths:
  1.1228 +      "i" := 1;
  1.1229 +      LOOP
  1.1230 +        "j" := 1;
  1.1231 +        LOOP
  1.1232 +          IF "i" != "j" THEN
  1.1233 +            "k" := 1;
  1.1234 +            LOOP
  1.1235 +              IF "i" != "k" AND "j" != "k" THEN
  1.1236 +                IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
  1.1237 +                  IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
  1.1238 +                    "matrix"["j"]["k"] := "matrix"["j"]["i"];
  1.1239 +                  END IF;
  1.1240 +                ELSE
  1.1241 +                  IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
  1.1242 +                    "matrix"["j"]["k"] := "matrix"["i"]["k"];
  1.1243 +                  END IF;
  1.1244 +                END IF;
  1.1245 +              END IF;
  1.1246 +              EXIT WHEN "k" = "dimension_v";
  1.1247 +              "k" := "k" + 1;
  1.1248 +            END LOOP;
  1.1249 +          END IF;
  1.1250 +          EXIT WHEN "j" = "dimension_v";
  1.1251 +          "j" := "j" + 1;
  1.1252 +        END LOOP;
  1.1253 +        EXIT WHEN "i" = "dimension_v";
  1.1254 +        "i" := "i" + 1;
  1.1255 +      END LOOP;
  1.1256 +      -- Determine order of winners:
  1.1257 +      "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
  1.1258 +      "rank_v" := 1;
  1.1259 +      "done_v" := 0;
  1.1260 +      LOOP
  1.1261 +        "winners_ary" := '{}';
  1.1262 +        "i" := 1;
  1.1263 +        LOOP
  1.1264 +          IF "rank_ary"["i"] ISNULL THEN
  1.1265 +            "j" := 1;
  1.1266 +            LOOP
  1.1267 +              IF
  1.1268 +                "i" != "j" AND
  1.1269 +                "rank_ary"["j"] ISNULL AND
  1.1270 +                "matrix"["j"]["i"] > "matrix"["i"]["j"]
  1.1271 +              THEN
  1.1272 +                -- someone else is better
  1.1273 +                EXIT;
  1.1274 +              END IF;
  1.1275 +              IF "j" = "dimension_v" THEN
  1.1276 +                -- noone is better
  1.1277 +                "winners_ary" := "winners_ary" || "i";
  1.1278 +                EXIT;
  1.1279 +              END IF;
  1.1280 +              "j" := "j" + 1;
  1.1281 +            END LOOP;
  1.1282 +          END IF;
  1.1283 +          EXIT WHEN "i" = "dimension_v";
  1.1284 +          "i" := "i" + 1;
  1.1285 +        END LOOP;
  1.1286 +        "i" := 1;
  1.1287 +        LOOP
  1.1288 +          "rank_ary"["winners_ary"["i"]] := "rank_v";
  1.1289 +          "done_v" := "done_v" + 1;
  1.1290 +          EXIT WHEN "i" = array_upper("winners_ary", 1);
  1.1291 +          "i" := "i" + 1;
  1.1292 +        END LOOP;
  1.1293 +        EXIT WHEN "done_v" = "dimension_v";
  1.1294 +        "rank_v" := "rank_v" + 1;
  1.1295 +      END LOOP;
  1.1296 +      -- write preliminary results:
  1.1297 +      "i" := 1;
  1.1298 +      FOR "initiative_id_v" IN
  1.1299 +        SELECT "id" FROM "initiative"
  1.1300 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
  1.1301 +        ORDER BY "id"
  1.1302 +      LOOP
  1.1303 +        UPDATE "initiative" SET
  1.1304 +          "direct_majority" =
  1.1305 +            CASE WHEN "policy_row"."direct_majority_strict" THEN
  1.1306 +              "positive_votes" * "policy_row"."direct_majority_den" >
  1.1307 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
  1.1308 +            ELSE
  1.1309 +              "positive_votes" * "policy_row"."direct_majority_den" >=
  1.1310 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
  1.1311 +            END
  1.1312 +            AND "positive_votes" >= "policy_row"."direct_majority_positive"
  1.1313 +            AND "issue_row"."voter_count"-"negative_votes" >=
  1.1314 +                "policy_row"."direct_majority_non_negative",
  1.1315 +            "indirect_majority" =
  1.1316 +            CASE WHEN "policy_row"."indirect_majority_strict" THEN
  1.1317 +              "positive_votes" * "policy_row"."indirect_majority_den" >
  1.1318 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
  1.1319 +            ELSE
  1.1320 +              "positive_votes" * "policy_row"."indirect_majority_den" >=
  1.1321 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
  1.1322 +            END
  1.1323 +            AND "positive_votes" >= "policy_row"."indirect_majority_positive"
  1.1324 +            AND "issue_row"."voter_count"-"negative_votes" >=
  1.1325 +                "policy_row"."indirect_majority_non_negative",
  1.1326 +          "schulze_rank"           = "rank_ary"["i"],
  1.1327 +          "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
  1.1328 +          "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"["dimension_v"],
  1.1329 +          "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
  1.1330 +          "reverse_beat_path"      = "matrix"["dimension_v"]["i"] >= 0,
  1.1331 +          "eligible"               = FALSE,
  1.1332 +          "winner"                 = FALSE,
  1.1333 +          "rank"                   = NULL  -- NOTE: in cases of manual reset of issue state
  1.1334 +          WHERE "id" = "initiative_id_v";
  1.1335 +        "i" := "i" + 1;
  1.1336 +      END LOOP;
  1.1337 +      IF "i" != "dimension_v" THEN
  1.1338 +        RAISE EXCEPTION 'Wrong winner count (should not happen)';
  1.1339 +      END IF;
  1.1340 +      -- take indirect majorities into account:
  1.1341 +      LOOP
  1.1342 +        UPDATE "initiative" SET "indirect_majority" = TRUE
  1.1343 +          FROM (
  1.1344 +            SELECT "new_initiative"."id" AS "initiative_id"
  1.1345 +            FROM "initiative" "old_initiative"
  1.1346 +            JOIN "initiative" "new_initiative"
  1.1347 +              ON "new_initiative"."issue_id" = "issue_id_p"
  1.1348 +              AND "new_initiative"."indirect_majority" = FALSE
  1.1349 +            JOIN "battle" "battle_win"
  1.1350 +              ON "battle_win"."issue_id" = "issue_id_p"
  1.1351 +              AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
  1.1352 +              AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
  1.1353 +            JOIN "battle" "battle_lose"
  1.1354 +              ON "battle_lose"."issue_id" = "issue_id_p"
  1.1355 +              AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
  1.1356 +              AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
  1.1357 +            WHERE "old_initiative"."issue_id" = "issue_id_p"
  1.1358 +            AND "old_initiative"."indirect_majority" = TRUE
  1.1359 +            AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
  1.1360 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >
  1.1361 +              "policy_row"."indirect_majority_num" *
  1.1362 +              ("battle_win"."count"+"battle_lose"."count")
  1.1363 +            ELSE
  1.1364 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >=
  1.1365 +              "policy_row"."indirect_majority_num" *
  1.1366 +              ("battle_win"."count"+"battle_lose"."count")
  1.1367 +            END
  1.1368 +            AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
  1.1369 +            AND "issue_row"."voter_count"-"battle_lose"."count" >=
  1.1370 +                "policy_row"."indirect_majority_non_negative"
  1.1371 +          ) AS "subquery"
  1.1372 +          WHERE "id" = "subquery"."initiative_id";
  1.1373 +        EXIT WHEN NOT FOUND;
  1.1374 +      END LOOP;
  1.1375 +      -- set "multistage_majority" for remaining matching initiatives:
  1.1376 +      UPDATE "initiative" SET "multistage_majority" = TRUE
  1.1377 +        FROM (
  1.1378 +          SELECT "losing_initiative"."id" AS "initiative_id"
  1.1379 +          FROM "initiative" "losing_initiative"
  1.1380 +          JOIN "initiative" "winning_initiative"
  1.1381 +            ON "winning_initiative"."issue_id" = "issue_id_p"
  1.1382 +            AND "winning_initiative"."admitted"
  1.1383 +          JOIN "battle" "battle_win"
  1.1384 +            ON "battle_win"."issue_id" = "issue_id_p"
  1.1385 +            AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
  1.1386 +            AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
  1.1387 +          JOIN "battle" "battle_lose"
  1.1388 +            ON "battle_lose"."issue_id" = "issue_id_p"
  1.1389 +            AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
  1.1390 +            AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
  1.1391 +          WHERE "losing_initiative"."issue_id" = "issue_id_p"
  1.1392 +          AND "losing_initiative"."admitted"
  1.1393 +          AND "winning_initiative"."schulze_rank" <
  1.1394 +              "losing_initiative"."schulze_rank"
  1.1395 +          AND "battle_win"."count" > "battle_lose"."count"
  1.1396 +          AND (
  1.1397 +            "battle_win"."count" > "winning_initiative"."positive_votes" OR
  1.1398 +            "battle_lose"."count" < "losing_initiative"."negative_votes" )
  1.1399 +        ) AS "subquery"
  1.1400 +        WHERE "id" = "subquery"."initiative_id";
  1.1401 +      -- mark eligible initiatives:
  1.1402 +      UPDATE "initiative" SET "eligible" = TRUE
  1.1403 +        WHERE "issue_id" = "issue_id_p"
  1.1404 +        AND "initiative"."direct_majority"
  1.1405 +        AND "initiative"."indirect_majority"
  1.1406 +        AND "initiative"."better_than_status_quo"
  1.1407 +        AND (
  1.1408 +          "policy_row"."no_multistage_majority" = FALSE OR
  1.1409 +          "initiative"."multistage_majority" = FALSE )
  1.1410 +        AND (
  1.1411 +          "policy_row"."no_reverse_beat_path" = FALSE OR
  1.1412 +          "initiative"."reverse_beat_path" = FALSE );
  1.1413 +      -- mark final winner:
  1.1414 +      UPDATE "initiative" SET "winner" = TRUE
  1.1415 +        FROM (
  1.1416 +          SELECT "id" AS "initiative_id"
  1.1417 +          FROM "initiative"
  1.1418 +          WHERE "issue_id" = "issue_id_p" AND "eligible"
  1.1419 +          ORDER BY
  1.1420 +            "schulze_rank",
  1.1421 +            "id"
  1.1422 +          LIMIT 1
  1.1423 +        ) AS "subquery"
  1.1424 +        WHERE "id" = "subquery"."initiative_id";
  1.1425 +      -- write (final) ranks:
  1.1426 +      "rank_v" := 1;
  1.1427 +      FOR "initiative_id_v" IN
  1.1428 +        SELECT "id"
  1.1429 +        FROM "initiative"
  1.1430 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
  1.1431 +        ORDER BY
  1.1432 +          "winner" DESC,
  1.1433 +          "eligible" DESC,
  1.1434 +          "schulze_rank",
  1.1435 +          "id"
  1.1436 +      LOOP
  1.1437 +        UPDATE "initiative" SET "rank" = "rank_v"
  1.1438 +          WHERE "id" = "initiative_id_v";
  1.1439 +        "rank_v" := "rank_v" + 1;
  1.1440 +      END LOOP;
  1.1441 +      -- set schulze rank of status quo and mark issue as finished:
  1.1442 +      UPDATE "issue" SET
  1.1443 +        "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
  1.1444 +        "state" =
  1.1445 +          CASE WHEN EXISTS (
  1.1446 +            SELECT NULL FROM "initiative"
  1.1447 +            WHERE "issue_id" = "issue_id_p" AND "winner"
  1.1448 +          ) THEN
  1.1449 +            'finished_with_winner'::"issue_state"
  1.1450 +          ELSE
  1.1451 +            'finished_without_winner'::"issue_state"
  1.1452 +          END,
  1.1453 +        "closed" = "phase_finished",
  1.1454 +        "phase_finished" = NULL
  1.1455 +        WHERE "id" = "issue_id_p";
  1.1456 +      RETURN;
  1.1457 +    END;
  1.1458 +  $$;
  1.1459 +
  1.1460 +DROP FUNCTION "check_issue"("issue"."id"%TYPE);
  1.1461 +
  1.1462 +CREATE TYPE "check_issue_persistence" AS (
  1.1463 +        "state"                 "issue_state",
  1.1464 +        "phase_finished"        BOOLEAN,
  1.1465 +        "issue_revoked"         BOOLEAN,
  1.1466 +        "snapshot_created"      BOOLEAN,
  1.1467 +        "harmonic_weights_set"  BOOLEAN,
  1.1468 +        "closed_voting"         BOOLEAN );
  1.1469 +COMMENT ON TYPE "check_issue_persistence" IS 'Type of data returned by "check_issue" function, to be passed to subsequent calls of the same function';
  1.1470 +
  1.1471 +CREATE FUNCTION "check_issue"
  1.1472 +  ( "issue_id_p" "issue"."id"%TYPE,
  1.1473 +    "persist"    "check_issue_persistence" )
  1.1474 +  RETURNS "check_issue_persistence"
  1.1475 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1476 +    DECLARE
  1.1477 +      "issue_row"      "issue"%ROWTYPE;
  1.1478 +      "policy_row"     "policy"%ROWTYPE;
  1.1479 +      "initiative_row" "initiative"%ROWTYPE;
  1.1480 +      "state_v"        "issue_state";
  1.1481 +    BEGIN
  1.1482 +      PERFORM "require_transaction_isolation"();
  1.1483 +      IF "persist" ISNULL THEN
  1.1484 +        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
  1.1485 +          FOR UPDATE;
  1.1486 +        IF "issue_row"."closed" NOTNULL THEN
  1.1487 +          RETURN NULL;
  1.1488 +        END IF;
  1.1489 +        "persist"."state" := "issue_row"."state";
  1.1490 +        IF
  1.1491 +          ( "issue_row"."state" = 'admission' AND now() >=
  1.1492 +            "issue_row"."created" + "issue_row"."admission_time" ) OR
  1.1493 +          ( "issue_row"."state" = 'discussion' AND now() >=
  1.1494 +            "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
  1.1495 +          ( "issue_row"."state" = 'verification' AND now() >=
  1.1496 +            "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
  1.1497 +          ( "issue_row"."state" = 'voting' AND now() >=
  1.1498 +            "issue_row"."fully_frozen" + "issue_row"."voting_time" )
  1.1499 +        THEN
  1.1500 +          "persist"."phase_finished" := TRUE;
  1.1501 +        ELSE
  1.1502 +          "persist"."phase_finished" := FALSE;
  1.1503 +        END IF;
  1.1504 +        IF
  1.1505 +          NOT EXISTS (
  1.1506 +            -- all initiatives are revoked
  1.1507 +            SELECT NULL FROM "initiative"
  1.1508 +            WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
  1.1509 +          ) AND (
  1.1510 +            -- and issue has not been accepted yet
  1.1511 +            "persist"."state" = 'admission' OR
  1.1512 +            -- or verification time has elapsed
  1.1513 +            ( "persist"."state" = 'verification' AND
  1.1514 +              "persist"."phase_finished" ) OR
  1.1515 +            -- or no initiatives have been revoked lately
  1.1516 +            NOT EXISTS (
  1.1517 +              SELECT NULL FROM "initiative"
  1.1518 +              WHERE "issue_id" = "issue_id_p"
  1.1519 +              AND now() < "revoked" + "issue_row"."verification_time"
  1.1520 +            )
  1.1521 +          )
  1.1522 +        THEN
  1.1523 +          "persist"."issue_revoked" := TRUE;
  1.1524 +        ELSE
  1.1525 +          "persist"."issue_revoked" := FALSE;
  1.1526 +        END IF;
  1.1527 +        IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
  1.1528 +          UPDATE "issue" SET "phase_finished" = now()
  1.1529 +            WHERE "id" = "issue_row"."id";
  1.1530 +          RETURN "persist";
  1.1531 +        ELSIF
  1.1532 +          "persist"."state" IN ('admission', 'discussion', 'verification')
  1.1533 +        THEN
  1.1534 +          RETURN "persist";
  1.1535 +        ELSE
  1.1536 +          RETURN NULL;
  1.1537 +        END IF;
  1.1538 +      END IF;
  1.1539 +      IF
  1.1540 +        "persist"."state" IN ('admission', 'discussion', 'verification') AND
  1.1541 +        coalesce("persist"."snapshot_created", FALSE) = FALSE
  1.1542 +      THEN
  1.1543 +        PERFORM "create_snapshot"("issue_id_p");
  1.1544 +        "persist"."snapshot_created" = TRUE;
  1.1545 +        IF "persist"."phase_finished" THEN
  1.1546 +          IF "persist"."state" = 'admission' THEN
  1.1547 +            PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
  1.1548 +          ELSIF "persist"."state" = 'discussion' THEN
  1.1549 +            PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
  1.1550 +          ELSIF "persist"."state" = 'verification' THEN
  1.1551 +            PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
  1.1552 +            SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
  1.1553 +            SELECT * INTO "policy_row" FROM "policy"
  1.1554 +              WHERE "id" = "issue_row"."policy_id";
  1.1555 +            FOR "initiative_row" IN
  1.1556 +              SELECT * FROM "initiative"
  1.1557 +              WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
  1.1558 +              FOR UPDATE
  1.1559 +            LOOP
  1.1560 +              IF
  1.1561 +                "initiative_row"."polling" OR (
  1.1562 +                  "initiative_row"."satisfied_supporter_count" > 0 AND
  1.1563 +                  "initiative_row"."satisfied_supporter_count" *
  1.1564 +                  "policy_row"."initiative_quorum_den" >=
  1.1565 +                  "issue_row"."population" * "policy_row"."initiative_quorum_num"
  1.1566 +                )
  1.1567 +              THEN
  1.1568 +                UPDATE "initiative" SET "admitted" = TRUE
  1.1569 +                  WHERE "id" = "initiative_row"."id";
  1.1570 +              ELSE
  1.1571 +                UPDATE "initiative" SET "admitted" = FALSE
  1.1572 +                  WHERE "id" = "initiative_row"."id";
  1.1573 +              END IF;
  1.1574 +            END LOOP;
  1.1575 +          END IF;
  1.1576 +        END IF;
  1.1577 +        RETURN "persist";
  1.1578 +      END IF;
  1.1579 +      IF
  1.1580 +        "persist"."state" IN ('admission', 'discussion', 'verification') AND
  1.1581 +        coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
  1.1582 +      THEN
  1.1583 +        PERFORM "set_harmonic_initiative_weights"("issue_id_p");
  1.1584 +        "persist"."harmonic_weights_set" = TRUE;
  1.1585 +        IF
  1.1586 +          "persist"."phase_finished" OR
  1.1587 +          "persist"."issue_revoked" OR
  1.1588 +          "persist"."state" = 'admission'
  1.1589 +        THEN
  1.1590 +          RETURN "persist";
  1.1591 +        ELSE
  1.1592 +          RETURN NULL;
  1.1593 +        END IF;
  1.1594 +      END IF;
  1.1595 +      IF "persist"."issue_revoked" THEN
  1.1596 +        IF "persist"."state" = 'admission' THEN
  1.1597 +          "state_v" := 'canceled_revoked_before_accepted';
  1.1598 +        ELSIF "persist"."state" = 'discussion' THEN
  1.1599 +          "state_v" := 'canceled_after_revocation_during_discussion';
  1.1600 +        ELSIF "persist"."state" = 'verification' THEN
  1.1601 +          "state_v" := 'canceled_after_revocation_during_verification';
  1.1602 +        END IF;
  1.1603 +        UPDATE "issue" SET
  1.1604 +          "state"          = "state_v",
  1.1605 +          "closed"         = "phase_finished",
  1.1606 +          "phase_finished" = NULL
  1.1607 +          WHERE "id" = "issue_id_p";
  1.1608 +        RETURN NULL;
  1.1609 +      END IF;
  1.1610 +      IF "persist"."state" = 'admission' THEN
  1.1611 +        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
  1.1612 +          FOR UPDATE;
  1.1613 +        SELECT * INTO "policy_row"
  1.1614 +          FROM "policy" WHERE "id" = "issue_row"."policy_id";
  1.1615 +        IF EXISTS (
  1.1616 +          SELECT NULL FROM "initiative"
  1.1617 +          WHERE "issue_id" = "issue_id_p"
  1.1618 +          AND "supporter_count" > 0
  1.1619 +          AND "supporter_count" * "policy_row"."issue_quorum_den"
  1.1620 +          >= "issue_row"."population" * "policy_row"."issue_quorum_num"
  1.1621 +        ) THEN
  1.1622 +          UPDATE "issue" SET
  1.1623 +            "state"          = 'discussion',
  1.1624 +            "accepted"       = coalesce("phase_finished", now()),
  1.1625 +            "phase_finished" = NULL
  1.1626 +            WHERE "id" = "issue_id_p";
  1.1627 +        ELSIF "issue_row"."phase_finished" NOTNULL THEN
  1.1628 +          UPDATE "issue" SET
  1.1629 +            "state"          = 'canceled_issue_not_accepted',
  1.1630 +            "closed"         = "phase_finished",
  1.1631 +            "phase_finished" = NULL
  1.1632 +            WHERE "id" = "issue_id_p";
  1.1633 +        END IF;
  1.1634 +        RETURN NULL;
  1.1635 +      END IF;
  1.1636 +      IF "persist"."phase_finished" THEN
  1.1637 +        if "persist"."state" = 'discussion' THEN
  1.1638 +          UPDATE "issue" SET
  1.1639 +            "state"          = 'verification',
  1.1640 +            "half_frozen"    = "phase_finished",
  1.1641 +            "phase_finished" = NULL
  1.1642 +            WHERE "id" = "issue_id_p";
  1.1643 +          RETURN NULL;
  1.1644 +        END IF;
  1.1645 +        IF "persist"."state" = 'verification' THEN
  1.1646 +          SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
  1.1647 +            FOR UPDATE;
  1.1648 +          SELECT * INTO "policy_row" FROM "policy"
  1.1649 +            WHERE "id" = "issue_row"."policy_id";
  1.1650 +          IF EXISTS (
  1.1651 +            SELECT NULL FROM "initiative"
  1.1652 +            WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
  1.1653 +          ) THEN
  1.1654 +            UPDATE "issue" SET
  1.1655 +              "state"         = 'voting',
  1.1656 +              "accepted"      = coalesce("accepted", "phase_finished"),
  1.1657 +              "half_frozen"   = coalesce("half_frozen", "phase_finished"),
  1.1658 +              "fully_frozen"  = "phase_finished",
  1.1659 +              "phase_finished" = NULL
  1.1660 +              WHERE "id" = "issue_id_p";
  1.1661 +          ELSE
  1.1662 +            UPDATE "issue" SET
  1.1663 +              "state"           = 'canceled_no_initiative_admitted',
  1.1664 +              "accepted"        = coalesce("accepted", "phase_finished"),
  1.1665 +              "half_frozen"     = coalesce("half_frozen", "phase_finished"),
  1.1666 +              "fully_frozen"    = "phase_finished",
  1.1667 +              "closed"          = "phase_finished",
  1.1668 +              "phase_finished"  = NULL
  1.1669 +              WHERE "id" = "issue_id_p";
  1.1670 +            -- NOTE: The following DELETE statements have effect only when
  1.1671 +            --       issue state has been manipulated
  1.1672 +            DELETE FROM "direct_voter"     WHERE "issue_id" = "issue_id_p";
  1.1673 +            DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
  1.1674 +            DELETE FROM "battle"           WHERE "issue_id" = "issue_id_p";
  1.1675 +          END IF;
  1.1676 +          RETURN NULL;
  1.1677 +        END IF;
  1.1678 +        IF "persist"."state" = 'voting' THEN
  1.1679 +          IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
  1.1680 +            PERFORM "close_voting"("issue_id_p");
  1.1681 +            "persist"."closed_voting" = TRUE;
  1.1682 +            RETURN "persist";
  1.1683 +          END IF;
  1.1684 +          PERFORM "calculate_ranks"("issue_id_p");
  1.1685 +          RETURN NULL;
  1.1686 +        END IF;
  1.1687 +      END IF;
  1.1688 +      RAISE WARNING 'should not happen';
  1.1689 +      RETURN NULL;
  1.1690 +    END;
  1.1691 +  $$;
  1.1692 +COMMENT ON FUNCTION "check_issue"
  1.1693 +  ( "issue"."id"%TYPE,
  1.1694 +    "check_issue_persistence" )
  1.1695 +  IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required, and perform the status change when necessary; Function must be called multiple times with the previous result as second parameter, until the result is NULL (see source code of function "check_everything")';
  1.1696 +
  1.1697 +CREATE OR REPLACE FUNCTION "check_everything"()
  1.1698 +  RETURNS VOID
  1.1699 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1700 +    DECLARE
  1.1701 +      "issue_id_v" "issue"."id"%TYPE;
  1.1702 +      "persist_v"  "check_issue_persistence";
  1.1703 +    BEGIN
  1.1704 +      RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
  1.1705 +      DELETE FROM "expired_session";
  1.1706 +      PERFORM "check_activity"();
  1.1707 +      PERFORM "calculate_member_counts"();
  1.1708 +      FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
  1.1709 +        "persist_v" := NULL;
  1.1710 +        LOOP
  1.1711 +          "persist_v" := "check_issue"("issue_id_v", "persist_v");
  1.1712 +          EXIT WHEN "persist_v" ISNULL;
  1.1713 +        END LOOP;
  1.1714 +      END LOOP;
  1.1715 +      RETURN;
  1.1716 +    END;
  1.1717 +  $$;
  1.1718 +COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue. Use this function only for development and debugging purposes, as you may run into locking and/or serialization problems in productive environments.';
  1.1719 +
  1.1720 +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
  1.1721 +  RETURNS VOID
  1.1722 +  LANGUAGE 'plpgsql' VOLATILE AS $$
  1.1723 +    DECLARE
  1.1724 +      "issue_row" "issue"%ROWTYPE;
  1.1725 +    BEGIN
  1.1726 +      SELECT * INTO "issue_row"
  1.1727 +        FROM "issue" WHERE "id" = "issue_id_p"
  1.1728 +        FOR UPDATE;
  1.1729 +      IF "issue_row"."cleaned" ISNULL THEN
  1.1730 +        UPDATE "issue" SET
  1.1731 +          "state"  = 'voting',
  1.1732 +          "closed" = NULL
  1.1733 +          WHERE "id" = "issue_id_p";
  1.1734 +        DELETE FROM "delegating_voter"
  1.1735 +          WHERE "issue_id" = "issue_id_p";
  1.1736 +        DELETE FROM "direct_voter"
  1.1737 +          WHERE "issue_id" = "issue_id_p";
  1.1738 +        DELETE FROM "delegating_interest_snapshot"
  1.1739 +          WHERE "issue_id" = "issue_id_p";
  1.1740 +        DELETE FROM "direct_interest_snapshot"
  1.1741 +          WHERE "issue_id" = "issue_id_p";
  1.1742 +        DELETE FROM "delegating_population_snapshot"
  1.1743 +          WHERE "issue_id" = "issue_id_p";
  1.1744 +        DELETE FROM "direct_population_snapshot"
  1.1745 +          WHERE "issue_id" = "issue_id_p";
  1.1746 +        DELETE FROM "non_voter"
  1.1747 +          WHERE "issue_id" = "issue_id_p";
  1.1748 +        DELETE FROM "delegation"
  1.1749 +          WHERE "issue_id" = "issue_id_p";
  1.1750 +        DELETE FROM "supporter"
  1.1751 +          USING "initiative"  -- NOTE: due to missing index on issue_id
  1.1752 +          WHERE "initiative"."issue_id" = "issue_id_p"
  1.1753 +          AND "supporter"."initiative_id" = "initiative_id";
  1.1754 +        UPDATE "issue" SET
  1.1755 +          "state"   = "issue_row"."state",
  1.1756 +          "closed"  = "issue_row"."closed",
  1.1757 +          "cleaned" = now()
  1.1758 +          WHERE "id" = "issue_id_p";
  1.1759 +      END IF;
  1.1760 +      RETURN;
  1.1761 +    END;
  1.1762 +  $$;
  1.1763 +
  1.1764 +COMMIT;

Impressum / About Us