liquid_feedback_core

changeset 411:44a07d8f1bb4

"schulze_rank" includes tie-breaking by "id"
author jbe
date Mon Dec 23 20:22:32 2013 +0100 (2013-12-23)
parents d301dc24b25c
children 399dc1a86398
files core.sql update/core-update.v2.2.5-v2.2.6.sql
line diff
     1.1 --- a/core.sql	Mon Oct 14 19:36:33 2013 +0200
     1.2 +++ b/core.sql	Mon Dec 23 20:22:32 2013 +0100
     1.3 @@ -707,13 +707,13 @@
     1.4  COMMENT ON COLUMN "initiative"."negative_votes"         IS 'Calculated from table "direct_voter"';
     1.5  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.6  COMMENT ON COLUMN "initiative"."indirect_majority"      IS 'Same as "direct_majority", but also considering indirect beat paths';
     1.7 -COMMENT ON COLUMN "initiative"."schulze_rank"           IS 'Schulze-Ranking without tie-breaking';
     1.8 -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.9 -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.10 +COMMENT ON COLUMN "initiative"."schulze_rank"           IS 'Schulze-Ranking';
    1.11 +COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
    1.12 +COMMENT ON COLUMN "initiative"."worse_than_status_quo"  IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)';
    1.13  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.14  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.15  COMMENT ON COLUMN "initiative"."eligible"               IS 'Initiative has a "direct_majority" and an "indirect_majority", is "better_than_status_quo" and depending on selected policy the initiative has no "reverse_beat_path" or "multistage_majority"';
    1.16 -COMMENT ON COLUMN "initiative"."winner"                 IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
    1.17 +COMMENT ON COLUMN "initiative"."winner"                 IS 'Winner is the "eligible" initiative with best "schulze_rank"';
    1.18  COMMENT ON COLUMN "initiative"."rank"                   IS 'Unique ranking for all "admitted" initiatives per issue; lower rank is better; a winner always has rank 1, but rank 1 does not imply that an initiative is winner; initiatives with "direct_majority" AND "indirect_majority" always have a better (lower) rank than other initiatives';
    1.19  
    1.20  
    1.21 @@ -3811,8 +3811,6 @@
    1.22        "battle_row"        "battle"%ROWTYPE;
    1.23        "rank_ary"          INT4[];
    1.24        "rank_v"            INT4;
    1.25 -      "done_v"            INTEGER;
    1.26 -      "winners_ary"       INTEGER[];
    1.27        "initiative_id_v"   "initiative"."id"%TYPE;
    1.28      BEGIN
    1.29        PERFORM "require_transaction_isolation"();
    1.30 @@ -3830,8 +3828,8 @@
    1.31        FOR "battle_row" IN
    1.32          SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
    1.33          ORDER BY
    1.34 -        "winning_initiative_id" NULLS LAST,
    1.35 -        "losing_initiative_id" NULLS LAST
    1.36 +        "winning_initiative_id" NULLS FIRST,
    1.37 +        "losing_initiative_id" NULLS FIRST
    1.38        LOOP
    1.39          "vote_matrix"["i"]["j"] := "battle_row"."count";
    1.40          IF "j" = "dimension_v" THEN
    1.41 @@ -3898,9 +3896,7 @@
    1.42        -- Determine order of winners:
    1.43        "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
    1.44        "rank_v" := 1;
    1.45 -      "done_v" := 0;
    1.46        LOOP
    1.47 -        "winners_ary" := '{}';
    1.48          "i" := 1;
    1.49          LOOP
    1.50            IF "rank_ary"["i"] ISNULL THEN
    1.51 @@ -3909,34 +3905,33 @@
    1.52                IF
    1.53                  "i" != "j" AND
    1.54                  "rank_ary"["j"] ISNULL AND
    1.55 -                "matrix"["j"]["i"] > "matrix"["i"]["j"]
    1.56 +                ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR
    1.57 +                  -- tie-breaking by "id"
    1.58 +                  ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND
    1.59 +                    "j" < "i" ) )
    1.60                THEN
    1.61                  -- someone else is better
    1.62                  EXIT;
    1.63                END IF;
    1.64 -              IF "j" = "dimension_v" THEN
    1.65 +              "j" := "j" + 1;
    1.66 +              IF "j" = "dimension_v" + 1 THEN
    1.67                  -- noone is better
    1.68 -                "winners_ary" := "winners_ary" || "i";
    1.69 +                "rank_ary"["i"] := "rank_v";
    1.70                  EXIT;
    1.71                END IF;
    1.72 -              "j" := "j" + 1;
    1.73              END LOOP;
    1.74 +            EXIT WHEN "j" = "dimension_v" + 1;
    1.75            END IF;
    1.76 -          EXIT WHEN "i" = "dimension_v";
    1.77            "i" := "i" + 1;
    1.78 +          IF "i" > "dimension_v" THEN
    1.79 +            RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
    1.80 +          END IF;
    1.81          END LOOP;
    1.82 -        "i" := 1;
    1.83 -        LOOP
    1.84 -          "rank_ary"["winners_ary"["i"]] := "rank_v";
    1.85 -          "done_v" := "done_v" + 1;
    1.86 -          EXIT WHEN "i" = array_upper("winners_ary", 1);
    1.87 -          "i" := "i" + 1;
    1.88 -        END LOOP;
    1.89 -        EXIT WHEN "done_v" = "dimension_v";
    1.90 +        EXIT WHEN "rank_v" = "dimension_v";
    1.91          "rank_v" := "rank_v" + 1;
    1.92        END LOOP;
    1.93        -- write preliminary results:
    1.94 -      "i" := 1;
    1.95 +      "i" := 2;  -- omit status quo with "i" = 1
    1.96        FOR "initiative_id_v" IN
    1.97          SELECT "id" FROM "initiative"
    1.98          WHERE "issue_id" = "issue_id_p" AND "admitted"
    1.99 @@ -3966,17 +3961,17 @@
   1.100              AND "issue_row"."voter_count"-"negative_votes" >=
   1.101                  "policy_row"."indirect_majority_non_negative",
   1.102            "schulze_rank"           = "rank_ary"["i"],
   1.103 -          "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
   1.104 -          "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"["dimension_v"],
   1.105 -          "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
   1.106 -          "reverse_beat_path"      = "matrix"["dimension_v"]["i"] >= 0,
   1.107 +          "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
   1.108 +          "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"[1],
   1.109 +          "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"[1],
   1.110 +          "reverse_beat_path"      = "matrix"[1]["i"] >= 0,
   1.111            "eligible"               = FALSE,
   1.112            "winner"                 = FALSE,
   1.113            "rank"                   = NULL  -- NOTE: in cases of manual reset of issue state
   1.114            WHERE "id" = "initiative_id_v";
   1.115          "i" := "i" + 1;
   1.116        END LOOP;
   1.117 -      IF "i" != "dimension_v" THEN
   1.118 +      IF "i" != "dimension_v" + 1 THEN
   1.119          RAISE EXCEPTION 'Wrong winner count (should not happen)';
   1.120        END IF;
   1.121        -- take indirect majorities into account:
   1.122 @@ -4082,7 +4077,7 @@
   1.123        END LOOP;
   1.124        -- set schulze rank of status quo and mark issue as finished:
   1.125        UPDATE "issue" SET
   1.126 -        "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
   1.127 +        "status_quo_schulze_rank" = "rank_ary"[1],
   1.128          "state" =
   1.129            CASE WHEN EXISTS (
   1.130              SELECT NULL FROM "initiative"
     2.1 --- a/update/core-update.v2.2.5-v2.2.6.sql	Mon Oct 14 19:36:33 2013 +0200
     2.2 +++ b/update/core-update.v2.2.5-v2.2.6.sql	Mon Dec 23 20:22:32 2013 +0100
     2.3 @@ -33,4 +33,307 @@
     2.4  
     2.5  COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area';
     2.6  
     2.7 +COMMENT ON COLUMN "initiative"."schulze_rank"           IS 'Schulze-Ranking';
     2.8 +COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
     2.9 +COMMENT ON COLUMN "initiative"."worse_than_status_quo"  IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)';
    2.10 +COMMENT ON COLUMN "initiative"."winner"                 IS 'Winner is the "eligible" initiative with best "schulze_rank"';
    2.11 +
    2.12 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
    2.13 +  RETURNS VOID
    2.14 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    2.15 +    DECLARE
    2.16 +      "issue_row"         "issue"%ROWTYPE;
    2.17 +      "policy_row"        "policy"%ROWTYPE;
    2.18 +      "dimension_v"       INTEGER;
    2.19 +      "vote_matrix"       INT4[][];  -- absolute votes
    2.20 +      "matrix"            INT8[][];  -- defeat strength / best paths
    2.21 +      "i"                 INTEGER;
    2.22 +      "j"                 INTEGER;
    2.23 +      "k"                 INTEGER;
    2.24 +      "battle_row"        "battle"%ROWTYPE;
    2.25 +      "rank_ary"          INT4[];
    2.26 +      "rank_v"            INT4;
    2.27 +      "initiative_id_v"   "initiative"."id"%TYPE;
    2.28 +    BEGIN
    2.29 +      PERFORM "require_transaction_isolation"();
    2.30 +      SELECT * INTO "issue_row"
    2.31 +        FROM "issue" WHERE "id" = "issue_id_p";
    2.32 +      SELECT * INTO "policy_row"
    2.33 +        FROM "policy" WHERE "id" = "issue_row"."policy_id";
    2.34 +      SELECT count(1) INTO "dimension_v"
    2.35 +        FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
    2.36 +      -- Create "vote_matrix" with absolute number of votes in pairwise
    2.37 +      -- comparison:
    2.38 +      "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
    2.39 +      "i" := 1;
    2.40 +      "j" := 2;
    2.41 +      FOR "battle_row" IN
    2.42 +        SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
    2.43 +        ORDER BY
    2.44 +        "winning_initiative_id" NULLS FIRST,
    2.45 +        "losing_initiative_id" NULLS FIRST
    2.46 +      LOOP
    2.47 +        "vote_matrix"["i"]["j"] := "battle_row"."count";
    2.48 +        IF "j" = "dimension_v" THEN
    2.49 +          "i" := "i" + 1;
    2.50 +          "j" := 1;
    2.51 +        ELSE
    2.52 +          "j" := "j" + 1;
    2.53 +          IF "j" = "i" THEN
    2.54 +            "j" := "j" + 1;
    2.55 +          END IF;
    2.56 +        END IF;
    2.57 +      END LOOP;
    2.58 +      IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
    2.59 +        RAISE EXCEPTION 'Wrong battle count (should not happen)';
    2.60 +      END IF;
    2.61 +      -- Store defeat strengths in "matrix" using "defeat_strength"
    2.62 +      -- function:
    2.63 +      "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
    2.64 +      "i" := 1;
    2.65 +      LOOP
    2.66 +        "j" := 1;
    2.67 +        LOOP
    2.68 +          IF "i" != "j" THEN
    2.69 +            "matrix"["i"]["j"] := "defeat_strength"(
    2.70 +              "vote_matrix"["i"]["j"],
    2.71 +              "vote_matrix"["j"]["i"]
    2.72 +            );
    2.73 +          END IF;
    2.74 +          EXIT WHEN "j" = "dimension_v";
    2.75 +          "j" := "j" + 1;
    2.76 +        END LOOP;
    2.77 +        EXIT WHEN "i" = "dimension_v";
    2.78 +        "i" := "i" + 1;
    2.79 +      END LOOP;
    2.80 +      -- Find best paths:
    2.81 +      "i" := 1;
    2.82 +      LOOP
    2.83 +        "j" := 1;
    2.84 +        LOOP
    2.85 +          IF "i" != "j" THEN
    2.86 +            "k" := 1;
    2.87 +            LOOP
    2.88 +              IF "i" != "k" AND "j" != "k" THEN
    2.89 +                IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
    2.90 +                  IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
    2.91 +                    "matrix"["j"]["k"] := "matrix"["j"]["i"];
    2.92 +                  END IF;
    2.93 +                ELSE
    2.94 +                  IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
    2.95 +                    "matrix"["j"]["k"] := "matrix"["i"]["k"];
    2.96 +                  END IF;
    2.97 +                END IF;
    2.98 +              END IF;
    2.99 +              EXIT WHEN "k" = "dimension_v";
   2.100 +              "k" := "k" + 1;
   2.101 +            END LOOP;
   2.102 +          END IF;
   2.103 +          EXIT WHEN "j" = "dimension_v";
   2.104 +          "j" := "j" + 1;
   2.105 +        END LOOP;
   2.106 +        EXIT WHEN "i" = "dimension_v";
   2.107 +        "i" := "i" + 1;
   2.108 +      END LOOP;
   2.109 +      -- Determine order of winners:
   2.110 +      "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
   2.111 +      "rank_v" := 1;
   2.112 +      LOOP
   2.113 +        "i" := 1;
   2.114 +        LOOP
   2.115 +          IF "rank_ary"["i"] ISNULL THEN
   2.116 +            "j" := 1;
   2.117 +            LOOP
   2.118 +              IF
   2.119 +                "i" != "j" AND
   2.120 +                "rank_ary"["j"] ISNULL AND
   2.121 +                ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR
   2.122 +                  -- tie-breaking by "id"
   2.123 +                  ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND
   2.124 +                    "j" < "i" ) )
   2.125 +              THEN
   2.126 +                -- someone else is better
   2.127 +                EXIT;
   2.128 +              END IF;
   2.129 +              "j" := "j" + 1;
   2.130 +              IF "j" = "dimension_v" + 1 THEN
   2.131 +                -- noone is better
   2.132 +                "rank_ary"["i"] := "rank_v";
   2.133 +                EXIT;
   2.134 +              END IF;
   2.135 +            END LOOP;
   2.136 +            EXIT WHEN "j" = "dimension_v" + 1;
   2.137 +          END IF;
   2.138 +          "i" := "i" + 1;
   2.139 +          IF "i" > "dimension_v" THEN
   2.140 +            RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
   2.141 +          END IF;
   2.142 +        END LOOP;
   2.143 +        EXIT WHEN "rank_v" = "dimension_v";
   2.144 +        "rank_v" := "rank_v" + 1;
   2.145 +      END LOOP;
   2.146 +      -- write preliminary results:
   2.147 +      "i" := 2;  -- omit status quo with "i" = 1
   2.148 +      FOR "initiative_id_v" IN
   2.149 +        SELECT "id" FROM "initiative"
   2.150 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
   2.151 +        ORDER BY "id"
   2.152 +      LOOP
   2.153 +        UPDATE "initiative" SET
   2.154 +          "direct_majority" =
   2.155 +            CASE WHEN "policy_row"."direct_majority_strict" THEN
   2.156 +              "positive_votes" * "policy_row"."direct_majority_den" >
   2.157 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   2.158 +            ELSE
   2.159 +              "positive_votes" * "policy_row"."direct_majority_den" >=
   2.160 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   2.161 +            END
   2.162 +            AND "positive_votes" >= "policy_row"."direct_majority_positive"
   2.163 +            AND "issue_row"."voter_count"-"negative_votes" >=
   2.164 +                "policy_row"."direct_majority_non_negative",
   2.165 +            "indirect_majority" =
   2.166 +            CASE WHEN "policy_row"."indirect_majority_strict" THEN
   2.167 +              "positive_votes" * "policy_row"."indirect_majority_den" >
   2.168 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   2.169 +            ELSE
   2.170 +              "positive_votes" * "policy_row"."indirect_majority_den" >=
   2.171 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   2.172 +            END
   2.173 +            AND "positive_votes" >= "policy_row"."indirect_majority_positive"
   2.174 +            AND "issue_row"."voter_count"-"negative_votes" >=
   2.175 +                "policy_row"."indirect_majority_non_negative",
   2.176 +          "schulze_rank"           = "rank_ary"["i"],
   2.177 +          "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
   2.178 +          "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"[1],
   2.179 +          "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"[1],
   2.180 +          "reverse_beat_path"      = "matrix"[1]["i"] >= 0,
   2.181 +          "eligible"               = FALSE,
   2.182 +          "winner"                 = FALSE,
   2.183 +          "rank"                   = NULL  -- NOTE: in cases of manual reset of issue state
   2.184 +          WHERE "id" = "initiative_id_v";
   2.185 +        "i" := "i" + 1;
   2.186 +      END LOOP;
   2.187 +      IF "i" != "dimension_v" + 1 THEN
   2.188 +        RAISE EXCEPTION 'Wrong winner count (should not happen)';
   2.189 +      END IF;
   2.190 +      -- take indirect majorities into account:
   2.191 +      LOOP
   2.192 +        UPDATE "initiative" SET "indirect_majority" = TRUE
   2.193 +          FROM (
   2.194 +            SELECT "new_initiative"."id" AS "initiative_id"
   2.195 +            FROM "initiative" "old_initiative"
   2.196 +            JOIN "initiative" "new_initiative"
   2.197 +              ON "new_initiative"."issue_id" = "issue_id_p"
   2.198 +              AND "new_initiative"."indirect_majority" = FALSE
   2.199 +            JOIN "battle" "battle_win"
   2.200 +              ON "battle_win"."issue_id" = "issue_id_p"
   2.201 +              AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
   2.202 +              AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
   2.203 +            JOIN "battle" "battle_lose"
   2.204 +              ON "battle_lose"."issue_id" = "issue_id_p"
   2.205 +              AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
   2.206 +              AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
   2.207 +            WHERE "old_initiative"."issue_id" = "issue_id_p"
   2.208 +            AND "old_initiative"."indirect_majority" = TRUE
   2.209 +            AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
   2.210 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >
   2.211 +              "policy_row"."indirect_majority_num" *
   2.212 +              ("battle_win"."count"+"battle_lose"."count")
   2.213 +            ELSE
   2.214 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >=
   2.215 +              "policy_row"."indirect_majority_num" *
   2.216 +              ("battle_win"."count"+"battle_lose"."count")
   2.217 +            END
   2.218 +            AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
   2.219 +            AND "issue_row"."voter_count"-"battle_lose"."count" >=
   2.220 +                "policy_row"."indirect_majority_non_negative"
   2.221 +          ) AS "subquery"
   2.222 +          WHERE "id" = "subquery"."initiative_id";
   2.223 +        EXIT WHEN NOT FOUND;
   2.224 +      END LOOP;
   2.225 +      -- set "multistage_majority" for remaining matching initiatives:
   2.226 +      UPDATE "initiative" SET "multistage_majority" = TRUE
   2.227 +        FROM (
   2.228 +          SELECT "losing_initiative"."id" AS "initiative_id"
   2.229 +          FROM "initiative" "losing_initiative"
   2.230 +          JOIN "initiative" "winning_initiative"
   2.231 +            ON "winning_initiative"."issue_id" = "issue_id_p"
   2.232 +            AND "winning_initiative"."admitted"
   2.233 +          JOIN "battle" "battle_win"
   2.234 +            ON "battle_win"."issue_id" = "issue_id_p"
   2.235 +            AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
   2.236 +            AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
   2.237 +          JOIN "battle" "battle_lose"
   2.238 +            ON "battle_lose"."issue_id" = "issue_id_p"
   2.239 +            AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
   2.240 +            AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
   2.241 +          WHERE "losing_initiative"."issue_id" = "issue_id_p"
   2.242 +          AND "losing_initiative"."admitted"
   2.243 +          AND "winning_initiative"."schulze_rank" <
   2.244 +              "losing_initiative"."schulze_rank"
   2.245 +          AND "battle_win"."count" > "battle_lose"."count"
   2.246 +          AND (
   2.247 +            "battle_win"."count" > "winning_initiative"."positive_votes" OR
   2.248 +            "battle_lose"."count" < "losing_initiative"."negative_votes" )
   2.249 +        ) AS "subquery"
   2.250 +        WHERE "id" = "subquery"."initiative_id";
   2.251 +      -- mark eligible initiatives:
   2.252 +      UPDATE "initiative" SET "eligible" = TRUE
   2.253 +        WHERE "issue_id" = "issue_id_p"
   2.254 +        AND "initiative"."direct_majority"
   2.255 +        AND "initiative"."indirect_majority"
   2.256 +        AND "initiative"."better_than_status_quo"
   2.257 +        AND (
   2.258 +          "policy_row"."no_multistage_majority" = FALSE OR
   2.259 +          "initiative"."multistage_majority" = FALSE )
   2.260 +        AND (
   2.261 +          "policy_row"."no_reverse_beat_path" = FALSE OR
   2.262 +          "initiative"."reverse_beat_path" = FALSE );
   2.263 +      -- mark final winner:
   2.264 +      UPDATE "initiative" SET "winner" = TRUE
   2.265 +        FROM (
   2.266 +          SELECT "id" AS "initiative_id"
   2.267 +          FROM "initiative"
   2.268 +          WHERE "issue_id" = "issue_id_p" AND "eligible"
   2.269 +          ORDER BY
   2.270 +            "schulze_rank",
   2.271 +            "id"
   2.272 +          LIMIT 1
   2.273 +        ) AS "subquery"
   2.274 +        WHERE "id" = "subquery"."initiative_id";
   2.275 +      -- write (final) ranks:
   2.276 +      "rank_v" := 1;
   2.277 +      FOR "initiative_id_v" IN
   2.278 +        SELECT "id"
   2.279 +        FROM "initiative"
   2.280 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
   2.281 +        ORDER BY
   2.282 +          "winner" DESC,
   2.283 +          "eligible" DESC,
   2.284 +          "schulze_rank",
   2.285 +          "id"
   2.286 +      LOOP
   2.287 +        UPDATE "initiative" SET "rank" = "rank_v"
   2.288 +          WHERE "id" = "initiative_id_v";
   2.289 +        "rank_v" := "rank_v" + 1;
   2.290 +      END LOOP;
   2.291 +      -- set schulze rank of status quo and mark issue as finished:
   2.292 +      UPDATE "issue" SET
   2.293 +        "status_quo_schulze_rank" = "rank_ary"[1],
   2.294 +        "state" =
   2.295 +          CASE WHEN EXISTS (
   2.296 +            SELECT NULL FROM "initiative"
   2.297 +            WHERE "issue_id" = "issue_id_p" AND "winner"
   2.298 +          ) THEN
   2.299 +            'finished_with_winner'::"issue_state"
   2.300 +          ELSE
   2.301 +            'finished_without_winner'::"issue_state"
   2.302 +          END,
   2.303 +        "closed" = "phase_finished",
   2.304 +        "phase_finished" = NULL
   2.305 +        WHERE "id" = "issue_id_p";
   2.306 +      RETURN;
   2.307 +    END;
   2.308 +  $$;
   2.309 +
   2.310  COMMIT;

Impressum / About Us