liquid_feedback_core

changeset 170:b52f3281e769

Save Schulze rank of status quo in "issue" table
author jbe
date Tue Jun 07 19:39:22 2011 +0200 (2011-06-07)
parents ddd8e17d8f82
children 60aaf3b11ca3
files core.sql
line diff
     1.1 --- a/core.sql	Tue Jun 07 18:02:58 2011 +0200
     1.2 +++ b/core.sql	Tue Jun 07 19:39:22 2011 +0200
     1.3 @@ -469,6 +469,7 @@
     1.4          "latest_snapshot_event" "snapshot_event",
     1.5          "population"            INT4,
     1.6          "voter_count"           INT4,
     1.7 +        "status_quo_schulze_rank" INT4,
     1.8          CONSTRAINT "valid_state" CHECK ((
     1.9            ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    1.10            ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
    1.11 @@ -517,20 +518,21 @@
    1.12  
    1.13  COMMENT ON TABLE "issue" IS 'Groups of initiatives';
    1.14  
    1.15 -COMMENT ON COLUMN "issue"."accepted"              IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
    1.16 -COMMENT ON COLUMN "issue"."half_frozen"           IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
    1.17 -COMMENT ON COLUMN "issue"."fully_frozen"          IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.';
    1.18 -COMMENT ON COLUMN "issue"."closed"                IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.';
    1.19 -COMMENT ON COLUMN "issue"."ranks_available"       IS 'TRUE = ranks have been calculated';
    1.20 -COMMENT ON COLUMN "issue"."cleaned"               IS 'Point in time, when discussion data and votes had been deleted';
    1.21 -COMMENT ON COLUMN "issue"."admission_time"        IS 'Copied from "policy" table at creation of issue';
    1.22 -COMMENT ON COLUMN "issue"."discussion_time"       IS 'Copied from "policy" table at creation of issue';
    1.23 -COMMENT ON COLUMN "issue"."verification_time"     IS 'Copied from "policy" table at creation of issue';
    1.24 -COMMENT ON COLUMN "issue"."voting_time"           IS 'Copied from "policy" table at creation of issue';
    1.25 -COMMENT ON COLUMN "issue"."snapshot"              IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
    1.26 -COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables';
    1.27 -COMMENT ON COLUMN "issue"."population"            IS 'Sum of "weight" column in table "direct_population_snapshot"';
    1.28 -COMMENT ON COLUMN "issue"."voter_count"           IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
    1.29 +COMMENT ON COLUMN "issue"."accepted"                IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
    1.30 +COMMENT ON COLUMN "issue"."half_frozen"             IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
    1.31 +COMMENT ON COLUMN "issue"."fully_frozen"            IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.';
    1.32 +COMMENT ON COLUMN "issue"."closed"                  IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.';
    1.33 +COMMENT ON COLUMN "issue"."ranks_available"         IS 'TRUE = ranks have been calculated';
    1.34 +COMMENT ON COLUMN "issue"."cleaned"                 IS 'Point in time, when discussion data and votes had been deleted';
    1.35 +COMMENT ON COLUMN "issue"."admission_time"          IS 'Copied from "policy" table at creation of issue';
    1.36 +COMMENT ON COLUMN "issue"."discussion_time"         IS 'Copied from "policy" table at creation of issue';
    1.37 +COMMENT ON COLUMN "issue"."verification_time"       IS 'Copied from "policy" table at creation of issue';
    1.38 +COMMENT ON COLUMN "issue"."voting_time"             IS 'Copied from "policy" table at creation of issue';
    1.39 +COMMENT ON COLUMN "issue"."snapshot"                IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
    1.40 +COMMENT ON COLUMN "issue"."latest_snapshot_event"   IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables';
    1.41 +COMMENT ON COLUMN "issue"."population"              IS 'Sum of "weight" column in table "direct_population_snapshot"';
    1.42 +COMMENT ON COLUMN "issue"."voter_count"             IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
    1.43 +COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
    1.44  
    1.45  
    1.46  CREATE TABLE "issue_setting" (
    1.47 @@ -562,9 +564,9 @@
    1.48          "negative_votes"        INT4,
    1.49          "direct_majority"       BOOLEAN,
    1.50          "indirect_majority"     BOOLEAN,
    1.51 +        "schulze_rank"          INT4,
    1.52          "better_than_status_quo" BOOLEAN,
    1.53          "worse_than_status_quo" BOOLEAN,
    1.54 -        "schulze_rank"          INT4,
    1.55          "reverse_beat_path"     BOOLEAN,
    1.56          "multistage_majority"   BOOLEAN,
    1.57          "eligible"              BOOLEAN,
    1.58 @@ -608,9 +610,9 @@
    1.59  COMMENT ON COLUMN "initiative"."negative_votes"          IS 'Calculated from table "direct_voter"';
    1.60  COMMENT ON COLUMN "initiative"."direct_majority"         IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"';
    1.61  COMMENT ON COLUMN "initiative"."indirect_majority"       IS 'Same as "direct_majority", but also considering indirect beat paths';
    1.62 +COMMENT ON COLUMN "initiative"."schulze_rank"            IS 'Schulze-Ranking without tie-breaking';
    1.63  COMMENT ON COLUMN "initiative"."better_than_status_quo"  IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
    1.64  COMMENT ON COLUMN "initiative"."worse_than_status_quo"   IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
    1.65 -COMMENT ON COLUMN "initiative"."schulze_rank"            IS 'Schulze-Ranking without tie-breaking';
    1.66  COMMENT ON COLUMN "initiative"."reverse_beat_path"       IS 'TRUE, if there is a beat path (may include ties), from this initiative to the status quo';
    1.67  COMMENT ON COLUMN "initiative"."multistage_majority"     IS 'TRUE, if either (a) this initiative has no better rank than the status quo, or (b) there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X';
    1.68  COMMENT ON COLUMN "initiative"."eligible"                IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"';
    1.69 @@ -3557,257 +3559,256 @@
    1.70          FROM "policy" WHERE "id" = "issue_row"."policy_id";
    1.71        SELECT count(1) INTO "dimension_v"
    1.72          FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
    1.73 -      IF "dimension_v" > 1 THEN
    1.74 -        -- Create "vote_matrix" with absolute number of votes in pairwise
    1.75 -        -- comparison:
    1.76 -        "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
    1.77 -        "i" := 1;
    1.78 -        "j" := 2;
    1.79 -        FOR "battle_row" IN
    1.80 -          SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
    1.81 -          ORDER BY
    1.82 -          "winning_initiative_id" NULLS LAST,
    1.83 -          "losing_initiative_id" NULLS LAST
    1.84 +      -- Create "vote_matrix" with absolute number of votes in pairwise
    1.85 +      -- comparison:
    1.86 +      "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
    1.87 +      "i" := 1;
    1.88 +      "j" := 2;
    1.89 +      FOR "battle_row" IN
    1.90 +        SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
    1.91 +        ORDER BY
    1.92 +        "winning_initiative_id" NULLS LAST,
    1.93 +        "losing_initiative_id" NULLS LAST
    1.94 +      LOOP
    1.95 +        "vote_matrix"["i"]["j"] := "battle_row"."count";
    1.96 +        IF "j" = "dimension_v" THEN
    1.97 +          "i" := "i" + 1;
    1.98 +          "j" := 1;
    1.99 +        ELSE
   1.100 +          "j" := "j" + 1;
   1.101 +          IF "j" = "i" THEN
   1.102 +            "j" := "j" + 1;
   1.103 +          END IF;
   1.104 +        END IF;
   1.105 +      END LOOP;
   1.106 +      IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
   1.107 +        RAISE EXCEPTION 'Wrong battle count (should not happen)';
   1.108 +      END IF;
   1.109 +      -- Store defeat strengths in "matrix" using "defeat_strength"
   1.110 +      -- function:
   1.111 +      "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
   1.112 +      "i" := 1;
   1.113 +      LOOP
   1.114 +        "j" := 1;
   1.115          LOOP
   1.116 -          "vote_matrix"["i"]["j"] := "battle_row"."count";
   1.117 -          IF "j" = "dimension_v" THEN
   1.118 -            "i" := "i" + 1;
   1.119 -            "j" := 1;
   1.120 -          ELSE
   1.121 -            "j" := "j" + 1;
   1.122 -            IF "j" = "i" THEN
   1.123 -              "j" := "j" + 1;
   1.124 -            END IF;
   1.125 +          IF "i" != "j" THEN
   1.126 +            "matrix"["i"]["j"] := "defeat_strength"(
   1.127 +              "vote_matrix"["i"]["j"],
   1.128 +              "vote_matrix"["j"]["i"]
   1.129 +            );
   1.130            END IF;
   1.131 +          EXIT WHEN "j" = "dimension_v";
   1.132 +          "j" := "j" + 1;
   1.133          END LOOP;
   1.134 -        IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
   1.135 -          RAISE EXCEPTION 'Wrong battle count (should not happen)';
   1.136 -        END IF;
   1.137 -        -- Store defeat strengths in "matrix" using "defeat_strength"
   1.138 -        -- function:
   1.139 -        "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
   1.140 +        EXIT WHEN "i" = "dimension_v";
   1.141 +        "i" := "i" + 1;
   1.142 +      END LOOP;
   1.143 +      -- Find best paths:
   1.144 +      "i" := 1;
   1.145 +      LOOP
   1.146 +        "j" := 1;
   1.147 +        LOOP
   1.148 +          IF "i" != "j" THEN
   1.149 +            "k" := 1;
   1.150 +            LOOP
   1.151 +              IF "i" != "k" AND "j" != "k" THEN
   1.152 +                IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
   1.153 +                  IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
   1.154 +                    "matrix"["j"]["k"] := "matrix"["j"]["i"];
   1.155 +                  END IF;
   1.156 +                ELSE
   1.157 +                  IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
   1.158 +                    "matrix"["j"]["k"] := "matrix"["i"]["k"];
   1.159 +                  END IF;
   1.160 +                END IF;
   1.161 +              END IF;
   1.162 +              EXIT WHEN "k" = "dimension_v";
   1.163 +              "k" := "k" + 1;
   1.164 +            END LOOP;
   1.165 +          END IF;
   1.166 +          EXIT WHEN "j" = "dimension_v";
   1.167 +          "j" := "j" + 1;
   1.168 +        END LOOP;
   1.169 +        EXIT WHEN "i" = "dimension_v";
   1.170 +        "i" := "i" + 1;
   1.171 +      END LOOP;
   1.172 +      -- Determine order of winners:
   1.173 +      "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
   1.174 +      "rank_v" := 1;
   1.175 +      "done_v" := 0;
   1.176 +      LOOP
   1.177 +        "winners_ary" := '{}';
   1.178          "i" := 1;
   1.179          LOOP
   1.180 -          "j" := 1;
   1.181 -          LOOP
   1.182 -            IF "i" != "j" THEN
   1.183 -              "matrix"["i"]["j"] := "defeat_strength"(
   1.184 -                "vote_matrix"["i"]["j"],
   1.185 -                "vote_matrix"["j"]["i"]
   1.186 -              );
   1.187 -            END IF;
   1.188 -            EXIT WHEN "j" = "dimension_v";
   1.189 -            "j" := "j" + 1;
   1.190 -          END LOOP;
   1.191 -          EXIT WHEN "i" = "dimension_v";
   1.192 -          "i" := "i" + 1;
   1.193 -        END LOOP;
   1.194 -        -- Find best paths:
   1.195 -        "i" := 1;
   1.196 -        LOOP
   1.197 -          "j" := 1;
   1.198 -          LOOP
   1.199 -            IF "i" != "j" THEN
   1.200 -              "k" := 1;
   1.201 -              LOOP
   1.202 -                IF "i" != "k" AND "j" != "k" THEN
   1.203 -                  IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
   1.204 -                    IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
   1.205 -                      "matrix"["j"]["k"] := "matrix"["j"]["i"];
   1.206 -                    END IF;
   1.207 -                  ELSE
   1.208 -                    IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
   1.209 -                      "matrix"["j"]["k"] := "matrix"["i"]["k"];
   1.210 -                    END IF;
   1.211 -                  END IF;
   1.212 -                END IF;
   1.213 -                EXIT WHEN "k" = "dimension_v";
   1.214 -                "k" := "k" + 1;
   1.215 -              END LOOP;
   1.216 -            END IF;
   1.217 -            EXIT WHEN "j" = "dimension_v";
   1.218 -            "j" := "j" + 1;
   1.219 -          END LOOP;
   1.220 +          IF "rank_ary"["i"] ISNULL THEN
   1.221 +            "j" := 1;
   1.222 +            LOOP
   1.223 +              IF
   1.224 +                "i" != "j" AND
   1.225 +                "rank_ary"["j"] ISNULL AND
   1.226 +                "matrix"["j"]["i"] > "matrix"["i"]["j"]
   1.227 +              THEN
   1.228 +                -- someone else is better
   1.229 +                EXIT;
   1.230 +              END IF;
   1.231 +              IF "j" = "dimension_v" THEN
   1.232 +                -- noone is better
   1.233 +                "winners_ary" := "winners_ary" || "i";
   1.234 +                EXIT;
   1.235 +              END IF;
   1.236 +              "j" := "j" + 1;
   1.237 +            END LOOP;
   1.238 +          END IF;
   1.239            EXIT WHEN "i" = "dimension_v";
   1.240            "i" := "i" + 1;
   1.241          END LOOP;
   1.242 -        -- Determine order of winners:
   1.243 -        "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
   1.244 -        "rank_v" := 1;
   1.245 -        "done_v" := 0;
   1.246 +        "i" := 1;
   1.247          LOOP
   1.248 -          "winners_ary" := '{}';
   1.249 -          "i" := 1;
   1.250 -          LOOP
   1.251 -            IF "rank_ary"["i"] ISNULL THEN
   1.252 -              "j" := 1;
   1.253 -              LOOP
   1.254 -                IF
   1.255 -                  "i" != "j" AND
   1.256 -                  "rank_ary"["j"] ISNULL AND
   1.257 -                  "matrix"["j"]["i"] > "matrix"["i"]["j"]
   1.258 -                THEN
   1.259 -                  -- someone else is better
   1.260 -                  EXIT;
   1.261 -                END IF;
   1.262 -                IF "j" = "dimension_v" THEN
   1.263 -                  -- noone is better
   1.264 -                  "winners_ary" := "winners_ary" || "i";
   1.265 -                  EXIT;
   1.266 -                END IF;
   1.267 -                "j" := "j" + 1;
   1.268 -              END LOOP;
   1.269 -            END IF;
   1.270 -            EXIT WHEN "i" = "dimension_v";
   1.271 -            "i" := "i" + 1;
   1.272 -          END LOOP;
   1.273 -          "i" := 1;
   1.274 -          LOOP
   1.275 -            "rank_ary"["winners_ary"["i"]] := "rank_v";
   1.276 -            "done_v" := "done_v" + 1;
   1.277 -            EXIT WHEN "i" = array_upper("winners_ary", 1);
   1.278 -            "i" := "i" + 1;
   1.279 -          END LOOP;
   1.280 -          EXIT WHEN "done_v" = "dimension_v";
   1.281 -          "rank_v" := "rank_v" + 1;
   1.282 -        END LOOP;
   1.283 -        -- write preliminary results:
   1.284 -        "i" := 1;
   1.285 -        FOR "initiative_id_v" IN
   1.286 -          SELECT "id" FROM "initiative"
   1.287 -          WHERE "issue_id" = "issue_id_p" AND "admitted"
   1.288 -          ORDER BY "id"
   1.289 -        LOOP
   1.290 -          UPDATE "initiative" SET
   1.291 -            "direct_majority" =
   1.292 -              CASE WHEN "policy_row"."direct_majority_strict" THEN
   1.293 -                "positive_votes" * "policy_row"."direct_majority_den" >
   1.294 -                "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   1.295 -              ELSE
   1.296 -                "positive_votes" * "policy_row"."direct_majority_den" >=
   1.297 -                "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   1.298 -              END
   1.299 -              AND "positive_votes" >= "policy_row"."direct_majority_positive"
   1.300 -              AND "issue_row"."voter_count"-"negative_votes" >=
   1.301 -                  "policy_row"."direct_majority_non_negative",
   1.302 -              "indirect_majority" =
   1.303 -              CASE WHEN "policy_row"."indirect_majority_strict" THEN
   1.304 -                "positive_votes" * "policy_row"."indirect_majority_den" >
   1.305 -                "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   1.306 -              ELSE
   1.307 -                "positive_votes" * "policy_row"."indirect_majority_den" >=
   1.308 -                "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   1.309 -              END
   1.310 -              AND "positive_votes" >= "policy_row"."indirect_majority_positive"
   1.311 -              AND "issue_row"."voter_count"-"negative_votes" >=
   1.312 -                  "policy_row"."indirect_majority_non_negative",
   1.313 -            "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
   1.314 -            "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"["dimension_v"],
   1.315 -            "schulze_rank"           = "rank_ary"["i"],
   1.316 -            "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
   1.317 -            "reverse_beat_path"      = "matrix"["dimension_v"]["i"] >= 0
   1.318 -            WHERE "id" = "initiative_id_v";
   1.319 +          "rank_ary"["winners_ary"["i"]] := "rank_v";
   1.320 +          "done_v" := "done_v" + 1;
   1.321 +          EXIT WHEN "i" = array_upper("winners_ary", 1);
   1.322            "i" := "i" + 1;
   1.323          END LOOP;
   1.324 -        IF "i" != "dimension_v" THEN
   1.325 -          RAISE EXCEPTION 'Wrong winner count (should not happen)';
   1.326 -        END IF;
   1.327 -        -- take indirect majorities into account:
   1.328 -        LOOP
   1.329 -          UPDATE "initiative" SET "indirect_majority" = TRUE
   1.330 -            FROM (
   1.331 -              SELECT "new_initiative"."id" AS "initiative_id"
   1.332 -              FROM "initiative" "old_initiative"
   1.333 -              JOIN "initiative" "new_initiative"
   1.334 -                ON "new_initiative"."issue_id" = "issue_id_p"
   1.335 -                AND "new_initiative"."indirect_majority" = FALSE
   1.336 -              JOIN "battle" "battle_win"
   1.337 -                ON "battle_win"."issue_id" = "issue_id_p"
   1.338 -                AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
   1.339 -                AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
   1.340 -              JOIN "battle" "battle_lose"
   1.341 -                ON "battle_lose"."issue_id" = "issue_id_p"
   1.342 -                AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
   1.343 -                AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
   1.344 -              WHERE "old_initiative"."issue_id" = "issue_id_p"
   1.345 -              AND "old_initiative"."indirect_majority" = TRUE
   1.346 -              AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
   1.347 -                "battle_win"."count" * "policy_row"."indirect_majority_den" >
   1.348 -                "policy_row"."indirect_majority_num" *
   1.349 -                ("battle_win"."count"+"battle_lose"."count")
   1.350 -              ELSE
   1.351 -                "battle_win"."count" * "policy_row"."indirect_majority_den" >=
   1.352 -                "policy_row"."indirect_majority_num" *
   1.353 -                ("battle_win"."count"+"battle_lose"."count")
   1.354 -              END
   1.355 -              AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
   1.356 -              AND "issue_row"."voter_count"-"battle_lose"."count" >=
   1.357 -                  "policy_row"."indirect_majority_non_negative"
   1.358 -            ) AS "subquery"
   1.359 -            WHERE "id" = "subquery"."initiative_id";
   1.360 -          EXIT WHEN NOT FOUND;
   1.361 -        END LOOP;
   1.362 -        -- set "multistage_majority" for remaining matching initiatives:
   1.363 -         UPDATE "initiative" SET "multistage_majority" = TRUE
   1.364 +        EXIT WHEN "done_v" = "dimension_v";
   1.365 +        "rank_v" := "rank_v" + 1;
   1.366 +      END LOOP;
   1.367 +      -- write preliminary results:
   1.368 +      "i" := 1;
   1.369 +      FOR "initiative_id_v" IN
   1.370 +        SELECT "id" FROM "initiative"
   1.371 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
   1.372 +        ORDER BY "id"
   1.373 +      LOOP
   1.374 +        UPDATE "initiative" SET
   1.375 +          "direct_majority" =
   1.376 +            CASE WHEN "policy_row"."direct_majority_strict" THEN
   1.377 +              "positive_votes" * "policy_row"."direct_majority_den" >
   1.378 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   1.379 +            ELSE
   1.380 +              "positive_votes" * "policy_row"."direct_majority_den" >=
   1.381 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   1.382 +            END
   1.383 +            AND "positive_votes" >= "policy_row"."direct_majority_positive"
   1.384 +            AND "issue_row"."voter_count"-"negative_votes" >=
   1.385 +                "policy_row"."direct_majority_non_negative",
   1.386 +            "indirect_majority" =
   1.387 +            CASE WHEN "policy_row"."indirect_majority_strict" THEN
   1.388 +              "positive_votes" * "policy_row"."indirect_majority_den" >
   1.389 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   1.390 +            ELSE
   1.391 +              "positive_votes" * "policy_row"."indirect_majority_den" >=
   1.392 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   1.393 +            END
   1.394 +            AND "positive_votes" >= "policy_row"."indirect_majority_positive"
   1.395 +            AND "issue_row"."voter_count"-"negative_votes" >=
   1.396 +                "policy_row"."indirect_majority_non_negative",
   1.397 +          "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
   1.398 +          "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"["dimension_v"],
   1.399 +          "schulze_rank"           = "rank_ary"["i"],
   1.400 +          "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
   1.401 +          "reverse_beat_path"      = "matrix"["dimension_v"]["i"] >= 0
   1.402 +          WHERE "id" = "initiative_id_v";
   1.403 +        "i" := "i" + 1;
   1.404 +      END LOOP;
   1.405 +      IF "i" != "dimension_v" THEN
   1.406 +        RAISE EXCEPTION 'Wrong winner count (should not happen)';
   1.407 +      END IF;
   1.408 +      -- take indirect majorities into account:
   1.409 +      LOOP
   1.410 +        UPDATE "initiative" SET "indirect_majority" = TRUE
   1.411            FROM (
   1.412 -            SELECT "losing_initiative"."id" AS "initiative_id"
   1.413 -            FROM "initiative" "losing_initiative"
   1.414 -            JOIN "initiative" "winning_initiative"
   1.415 -              ON "winning_initiative"."issue_id" = "issue_id_p"
   1.416 -              AND "winning_initiative"."admitted"
   1.417 +            SELECT "new_initiative"."id" AS "initiative_id"
   1.418 +            FROM "initiative" "old_initiative"
   1.419 +            JOIN "initiative" "new_initiative"
   1.420 +              ON "new_initiative"."issue_id" = "issue_id_p"
   1.421 +              AND "new_initiative"."indirect_majority" = FALSE
   1.422              JOIN "battle" "battle_win"
   1.423                ON "battle_win"."issue_id" = "issue_id_p"
   1.424 -              AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
   1.425 -              AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
   1.426 +              AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
   1.427 +              AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
   1.428              JOIN "battle" "battle_lose"
   1.429                ON "battle_lose"."issue_id" = "issue_id_p"
   1.430 -              AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
   1.431 -              AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
   1.432 -            WHERE "losing_initiative"."issue_id" = "issue_id_p"
   1.433 -            AND "losing_initiative"."admitted"
   1.434 -            AND "winning_initiative"."schulze_rank" <
   1.435 -                "losing_initiative"."schulze_rank"
   1.436 -            AND "battle_win"."count" > "battle_lose"."count"
   1.437 -            AND (
   1.438 -              "battle_win"."count" > "winning_initiative"."positive_votes" OR
   1.439 -              "battle_lose"."count" < "losing_initiative"."negative_votes" )
   1.440 +              AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
   1.441 +              AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
   1.442 +            WHERE "old_initiative"."issue_id" = "issue_id_p"
   1.443 +            AND "old_initiative"."indirect_majority" = TRUE
   1.444 +            AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
   1.445 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >
   1.446 +              "policy_row"."indirect_majority_num" *
   1.447 +              ("battle_win"."count"+"battle_lose"."count")
   1.448 +            ELSE
   1.449 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >=
   1.450 +              "policy_row"."indirect_majority_num" *
   1.451 +              ("battle_win"."count"+"battle_lose"."count")
   1.452 +            END
   1.453 +            AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
   1.454 +            AND "issue_row"."voter_count"-"battle_lose"."count" >=
   1.455 +                "policy_row"."indirect_majority_non_negative"
   1.456            ) AS "subquery"
   1.457            WHERE "id" = "subquery"."initiative_id";
   1.458 -        -- mark eligible initiatives:
   1.459 -        "rank_v" := 1;
   1.460 -        UPDATE "initiative" SET "eligible" = TRUE
   1.461 -          FROM (
   1.462 -            SELECT "initiative"."id" AS "initiative_id"
   1.463 -            FROM "issue"
   1.464 -            JOIN "policy"
   1.465 -              ON "issue"."policy_id" = "policy"."id"
   1.466 -            JOIN "initiative"
   1.467 -              ON "issue"."id" = "initiative"."issue_id"
   1.468 -            WHERE "issue_id" = "issue_id_p"
   1.469 -            AND "initiative"."direct_majority"
   1.470 -            AND "initiative"."indirect_majority"
   1.471 -            AND "initiative"."better_than_status_quo"
   1.472 -            AND (
   1.473 -              "policy"."no_multistage_majority" = FALSE OR
   1.474 -              "initiative"."multistage_majority" = FALSE )
   1.475 -            AND (
   1.476 -              "policy"."no_reverse_beat_path" = FALSE OR
   1.477 -              "initiative"."reverse_beat_path" = FALSE )
   1.478 -          ) AS "subquery"
   1.479 -          WHERE "id" = "subquery"."initiative_id";
   1.480 -        -- mark final winner:
   1.481 -        UPDATE "initiative" SET "winner" = TRUE
   1.482 -          FROM (
   1.483 -            SELECT "id" AS "initiative_id"
   1.484 -            FROM "initiative"
   1.485 -            WHERE "issue_id" = "issue_id_p" AND "eligible"
   1.486 -            ORDER BY "schulze_rank", "id"
   1.487 -            LIMIT 1
   1.488 -          ) AS "subquery"
   1.489 -          WHERE "id" = "subquery"."initiative_id";
   1.490 -      END IF;
   1.491 -      -- mark issue as finished:
   1.492 +        EXIT WHEN NOT FOUND;
   1.493 +      END LOOP;
   1.494 +      -- set "multistage_majority" for remaining matching initiatives:
   1.495 +       UPDATE "initiative" SET "multistage_majority" = TRUE
   1.496 +        FROM (
   1.497 +          SELECT "losing_initiative"."id" AS "initiative_id"
   1.498 +          FROM "initiative" "losing_initiative"
   1.499 +          JOIN "initiative" "winning_initiative"
   1.500 +            ON "winning_initiative"."issue_id" = "issue_id_p"
   1.501 +            AND "winning_initiative"."admitted"
   1.502 +          JOIN "battle" "battle_win"
   1.503 +            ON "battle_win"."issue_id" = "issue_id_p"
   1.504 +            AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
   1.505 +            AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
   1.506 +          JOIN "battle" "battle_lose"
   1.507 +            ON "battle_lose"."issue_id" = "issue_id_p"
   1.508 +            AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
   1.509 +            AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
   1.510 +          WHERE "losing_initiative"."issue_id" = "issue_id_p"
   1.511 +          AND "losing_initiative"."admitted"
   1.512 +          AND "winning_initiative"."schulze_rank" <
   1.513 +              "losing_initiative"."schulze_rank"
   1.514 +          AND "battle_win"."count" > "battle_lose"."count"
   1.515 +          AND (
   1.516 +            "battle_win"."count" > "winning_initiative"."positive_votes" OR
   1.517 +            "battle_lose"."count" < "losing_initiative"."negative_votes" )
   1.518 +        ) AS "subquery"
   1.519 +        WHERE "id" = "subquery"."initiative_id";
   1.520 +      -- mark eligible initiatives:
   1.521 +      "rank_v" := 1;
   1.522 +      UPDATE "initiative" SET "eligible" = TRUE
   1.523 +        FROM (
   1.524 +          SELECT "initiative"."id" AS "initiative_id"
   1.525 +          FROM "issue"
   1.526 +          JOIN "policy"
   1.527 +            ON "issue"."policy_id" = "policy"."id"
   1.528 +          JOIN "initiative"
   1.529 +            ON "issue"."id" = "initiative"."issue_id"
   1.530 +          WHERE "issue_id" = "issue_id_p"
   1.531 +          AND "initiative"."direct_majority"
   1.532 +          AND "initiative"."indirect_majority"
   1.533 +          AND "initiative"."better_than_status_quo"
   1.534 +          AND (
   1.535 +            "policy"."no_multistage_majority" = FALSE OR
   1.536 +            "initiative"."multistage_majority" = FALSE )
   1.537 +          AND (
   1.538 +            "policy"."no_reverse_beat_path" = FALSE OR
   1.539 +            "initiative"."reverse_beat_path" = FALSE )
   1.540 +        ) AS "subquery"
   1.541 +        WHERE "id" = "subquery"."initiative_id";
   1.542 +      -- mark final winner:
   1.543 +      UPDATE "initiative" SET "winner" = TRUE
   1.544 +        FROM (
   1.545 +          SELECT "id" AS "initiative_id"
   1.546 +          FROM "initiative"
   1.547 +          WHERE "issue_id" = "issue_id_p" AND "eligible"
   1.548 +          ORDER BY "schulze_rank", "id"
   1.549 +          LIMIT 1
   1.550 +        ) AS "subquery"
   1.551 +        WHERE "id" = "subquery"."initiative_id";
   1.552 +      -- set schulze rank of status quo and mark issue as finished:
   1.553        UPDATE "issue" SET
   1.554 +        "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
   1.555          "state" =
   1.556            CASE WHEN EXISTS (
   1.557              SELECT NULL FROM "initiative"

Impressum / About Us