liquid_feedback_core

changeset 420:044a2b65c707 v3.0.1

Merged from v2.2.6: Marked "no_reverse_beat_path" and "no_multistage_majority" as experimental; Disabled "no_reverse_beat_path" by default
author jbe
date Sat Apr 05 23:04:43 2014 +0200 (2014-04-05)
parents 1088d83d92e8 eaba9174d117
children 3b7c05dbbec6
files core.sql update/core-update.v2.2.6-v3.0.1.sql update/core-update.v3.0.0-v3.0.1.sql
line diff
     1.1 --- a/core.sql	Wed Mar 26 20:08:50 2014 +0100
     1.2 +++ b/core.sql	Sat Apr 05 23:04:43 2014 +0200
     1.3 @@ -373,7 +373,7 @@
     1.4          "indirect_majority_strict"      BOOLEAN NOT NULL DEFAULT TRUE,
     1.5          "indirect_majority_positive"    INT4    NOT NULL DEFAULT 0,
     1.6          "indirect_majority_non_negative" INT4   NOT NULL DEFAULT 0,
     1.7 -        "no_reverse_beat_path"          BOOLEAN NOT NULL DEFAULT TRUE,
     1.8 +        "no_reverse_beat_path"          BOOLEAN NOT NULL DEFAULT FALSE,
     1.9          "no_multistage_majority"        BOOLEAN NOT NULL DEFAULT FALSE,
    1.10          CONSTRAINT "timing" CHECK (
    1.11            ( "polling" = FALSE AND
    1.12 @@ -413,8 +413,8 @@
    1.13  COMMENT ON COLUMN "policy"."indirect_majority_strict"       IS 'If TRUE, then the indirect majority must be strictly greater than "indirect_majority_num"/"indirect_majority_den", otherwise it may also be equal.';
    1.14  COMMENT ON COLUMN "policy"."indirect_majority_positive"     IS 'Absolute number of votes in favor of the winner neccessary in a beat path to the status quo for an initaitive to be attainable as winner';
    1.15  COMMENT ON COLUMN "policy"."indirect_majority_non_negative" IS 'Absolute number of sum of votes in favor and abstentions in a beat path to the status quo for an initiative to be attainable as winner';
    1.16 -COMMENT ON COLUMN "policy"."no_reverse_beat_path"  IS 'Causes initiatives with "reverse_beat_path" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."reverse_beat_path". This option ensures both that a winning initiative is never tied in a (weak) condorcet paradox with the status quo and a winning initiative always beats the status quo directly with a simple majority.';
    1.17 -COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'Causes initiatives with "multistage_majority" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."multistage_majority". This disqualifies initiatives which could cause an instable result. An instable result in this meaning is a result such that repeating the ballot with same preferences but with the winner of the first ballot as status quo would lead to a different winner in the second ballot. If there are no direct majorities required for the winner, or if in direct comparison only simple majorities are required and "no_reverse_beat_path" is true, then results are always stable and this flag does not have any effect on the winner (but still affects the "eligible" flag of an "initiative").';
    1.18 +COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'EXPERIMENTAL FEATURE: Causes initiatives with "reverse_beat_path" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."reverse_beat_path". This option ensures both that a winning initiative is never tied in a (weak) condorcet paradox with the status quo and a winning initiative always beats the status quo directly with a simple majority.';
    1.19 +COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'EXPERIMENTAL FEATURE: Causes initiatives with "multistage_majority" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."multistage_majority". This disqualifies initiatives which could cause an instable result. An instable result in this meaning is a result such that repeating the ballot with same preferences but with the winner of the first ballot as status quo would lead to a different winner in the second ballot. If there are no direct majorities required for the winner, or if in direct comparison only simple majorities are required and "no_reverse_beat_path" is true, then results are always stable and this flag does not have any effect on the winner (but still affects the "eligible" flag of an "initiative").';
    1.20  
    1.21  
    1.22  CREATE TABLE "unit" (
     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/update/core-update.v2.2.5-v2.2.6.sql	Sat Apr 05 23:04:43 2014 +0200
     2.3 @@ -0,0 +1,19 @@
     2.4 +-- NOTICE: This update script disables the "no_reserve_beat_path" setting for
     2.5 +--         all policies. If this is not intended, please edit this script
     2.6 +--         before applying it to your database.
     2.7 +
     2.8 +BEGIN;
     2.9 +
    2.10 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
    2.11 +  SELECT * FROM (VALUES ('2.2.6', 2, 2, 6))
    2.12 +  AS "subquery"("string", "major", "minor", "revision");
    2.13 +
    2.14 +ALTER TABLE "policy" ALTER COLUMN "no_reverse_beat_path" SET DEFAULT FALSE;
    2.15 +
    2.16 +UPDATE "policy" SET "no_reverse_beat_path" = FALSE;  -- recommended
    2.17 +
    2.18 +COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'EXPERIMENTAL FEATURE: Causes initiatives with "reverse_beat_path" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."reverse_beat_path". This option ensures both that a winning initiative is never tied in a (weak) condorcet paradox with the status quo and a winning initiative always beats the status quo directly with a simple majority.';
    2.19 +
    2.20 +COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'EXPERIMENTAL FEATURE: Causes initiatives with "multistage_majority" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."multistage_majority". This disqualifies initiatives which could cause an instable result. An instable result in this meaning is a result such that repeating the ballot with same preferences but with the winner of the first ballot as status quo would lead to a different winner in the second ballot. If there are no direct majorities required for the winner, or if in direct comparison only simple majorities are required and "no_reverse_beat_path" is true, then results are always stable and this flag does not have any effect on the winner (but still affects the "eligible" flag of an "initiative").';
    2.21 +
    2.22 +COMMIT;
     3.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     3.2 +++ b/update/core-update.v2.2.6-v3.0.1.sql	Sat Apr 05 23:04:43 2014 +0200
     3.3 @@ -0,0 +1,339 @@
     3.4 +BEGIN;
     3.5 +
     3.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     3.7 +  SELECT * FROM (VALUES ('3.0.1', 3, 0, 1))
     3.8 +  AS "subquery"("string", "major", "minor", "revision");
     3.9 +
    3.10 +CREATE TABLE "issue_order_in_admission_state" (
    3.11 +        "id"                    INT8            PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    3.12 +        "order_in_area"         INT4,
    3.13 +        "order_in_unit"         INT4 );
    3.14 +
    3.15 +COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"';
    3.16 +
    3.17 +COMMENT ON COLUMN "issue_order_in_admission_state"."id"            IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
    3.18 +COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last';
    3.19 +COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last';
    3.20 +
    3.21 +CREATE VIEW "issue_supporter_in_admission_state" AS
    3.22 +  SELECT DISTINCT
    3.23 +    "area"."unit_id",
    3.24 +    "issue"."area_id",
    3.25 +    "issue"."id" AS "issue_id",
    3.26 +    "supporter"."member_id",
    3.27 +    "direct_interest_snapshot"."weight"
    3.28 +  FROM "issue"
    3.29 +  JOIN "area" ON "area"."id" = "issue"."area_id"
    3.30 +  JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
    3.31 +  JOIN "direct_interest_snapshot"
    3.32 +    ON  "direct_interest_snapshot"."issue_id" = "issue"."id"
    3.33 +    AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
    3.34 +    AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
    3.35 +  WHERE "issue"."state" = 'admission'::"issue_state";
    3.36 +
    3.37 +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';
    3.38 +
    3.39 +COMMENT ON COLUMN "initiative"."schulze_rank"           IS 'Schulze-Ranking';
    3.40 +COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
    3.41 +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)';
    3.42 +COMMENT ON COLUMN "initiative"."winner"                 IS 'Winner is the "eligible" initiative with best "schulze_rank"';
    3.43 +
    3.44 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
    3.45 +  RETURNS VOID
    3.46 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    3.47 +    DECLARE
    3.48 +      "issue_row"         "issue"%ROWTYPE;
    3.49 +      "policy_row"        "policy"%ROWTYPE;
    3.50 +      "dimension_v"       INTEGER;
    3.51 +      "vote_matrix"       INT4[][];  -- absolute votes
    3.52 +      "matrix"            INT8[][];  -- defeat strength / best paths
    3.53 +      "i"                 INTEGER;
    3.54 +      "j"                 INTEGER;
    3.55 +      "k"                 INTEGER;
    3.56 +      "battle_row"        "battle"%ROWTYPE;
    3.57 +      "rank_ary"          INT4[];
    3.58 +      "rank_v"            INT4;
    3.59 +      "initiative_id_v"   "initiative"."id"%TYPE;
    3.60 +    BEGIN
    3.61 +      PERFORM "require_transaction_isolation"();
    3.62 +      SELECT * INTO "issue_row"
    3.63 +        FROM "issue" WHERE "id" = "issue_id_p";
    3.64 +      SELECT * INTO "policy_row"
    3.65 +        FROM "policy" WHERE "id" = "issue_row"."policy_id";
    3.66 +      SELECT count(1) INTO "dimension_v"
    3.67 +        FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
    3.68 +      -- Create "vote_matrix" with absolute number of votes in pairwise
    3.69 +      -- comparison:
    3.70 +      "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
    3.71 +      "i" := 1;
    3.72 +      "j" := 2;
    3.73 +      FOR "battle_row" IN
    3.74 +        SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
    3.75 +        ORDER BY
    3.76 +        "winning_initiative_id" NULLS FIRST,
    3.77 +        "losing_initiative_id" NULLS FIRST
    3.78 +      LOOP
    3.79 +        "vote_matrix"["i"]["j"] := "battle_row"."count";
    3.80 +        IF "j" = "dimension_v" THEN
    3.81 +          "i" := "i" + 1;
    3.82 +          "j" := 1;
    3.83 +        ELSE
    3.84 +          "j" := "j" + 1;
    3.85 +          IF "j" = "i" THEN
    3.86 +            "j" := "j" + 1;
    3.87 +          END IF;
    3.88 +        END IF;
    3.89 +      END LOOP;
    3.90 +      IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
    3.91 +        RAISE EXCEPTION 'Wrong battle count (should not happen)';
    3.92 +      END IF;
    3.93 +      -- Store defeat strengths in "matrix" using "defeat_strength"
    3.94 +      -- function:
    3.95 +      "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
    3.96 +      "i" := 1;
    3.97 +      LOOP
    3.98 +        "j" := 1;
    3.99 +        LOOP
   3.100 +          IF "i" != "j" THEN
   3.101 +            "matrix"["i"]["j"] := "defeat_strength"(
   3.102 +              "vote_matrix"["i"]["j"],
   3.103 +              "vote_matrix"["j"]["i"]
   3.104 +            );
   3.105 +          END IF;
   3.106 +          EXIT WHEN "j" = "dimension_v";
   3.107 +          "j" := "j" + 1;
   3.108 +        END LOOP;
   3.109 +        EXIT WHEN "i" = "dimension_v";
   3.110 +        "i" := "i" + 1;
   3.111 +      END LOOP;
   3.112 +      -- Find best paths:
   3.113 +      "i" := 1;
   3.114 +      LOOP
   3.115 +        "j" := 1;
   3.116 +        LOOP
   3.117 +          IF "i" != "j" THEN
   3.118 +            "k" := 1;
   3.119 +            LOOP
   3.120 +              IF "i" != "k" AND "j" != "k" THEN
   3.121 +                IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
   3.122 +                  IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
   3.123 +                    "matrix"["j"]["k"] := "matrix"["j"]["i"];
   3.124 +                  END IF;
   3.125 +                ELSE
   3.126 +                  IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
   3.127 +                    "matrix"["j"]["k"] := "matrix"["i"]["k"];
   3.128 +                  END IF;
   3.129 +                END IF;
   3.130 +              END IF;
   3.131 +              EXIT WHEN "k" = "dimension_v";
   3.132 +              "k" := "k" + 1;
   3.133 +            END LOOP;
   3.134 +          END IF;
   3.135 +          EXIT WHEN "j" = "dimension_v";
   3.136 +          "j" := "j" + 1;
   3.137 +        END LOOP;
   3.138 +        EXIT WHEN "i" = "dimension_v";
   3.139 +        "i" := "i" + 1;
   3.140 +      END LOOP;
   3.141 +      -- Determine order of winners:
   3.142 +      "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
   3.143 +      "rank_v" := 1;
   3.144 +      LOOP
   3.145 +        "i" := 1;
   3.146 +        LOOP
   3.147 +          IF "rank_ary"["i"] ISNULL THEN
   3.148 +            "j" := 1;
   3.149 +            LOOP
   3.150 +              IF
   3.151 +                "i" != "j" AND
   3.152 +                "rank_ary"["j"] ISNULL AND
   3.153 +                ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR
   3.154 +                  -- tie-breaking by "id"
   3.155 +                  ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND
   3.156 +                    "j" < "i" ) )
   3.157 +              THEN
   3.158 +                -- someone else is better
   3.159 +                EXIT;
   3.160 +              END IF;
   3.161 +              "j" := "j" + 1;
   3.162 +              IF "j" = "dimension_v" + 1 THEN
   3.163 +                -- noone is better
   3.164 +                "rank_ary"["i"] := "rank_v";
   3.165 +                EXIT;
   3.166 +              END IF;
   3.167 +            END LOOP;
   3.168 +            EXIT WHEN "j" = "dimension_v" + 1;
   3.169 +          END IF;
   3.170 +          "i" := "i" + 1;
   3.171 +          IF "i" > "dimension_v" THEN
   3.172 +            RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
   3.173 +          END IF;
   3.174 +        END LOOP;
   3.175 +        EXIT WHEN "rank_v" = "dimension_v";
   3.176 +        "rank_v" := "rank_v" + 1;
   3.177 +      END LOOP;
   3.178 +      -- write preliminary results:
   3.179 +      "i" := 2;  -- omit status quo with "i" = 1
   3.180 +      FOR "initiative_id_v" IN
   3.181 +        SELECT "id" FROM "initiative"
   3.182 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
   3.183 +        ORDER BY "id"
   3.184 +      LOOP
   3.185 +        UPDATE "initiative" SET
   3.186 +          "direct_majority" =
   3.187 +            CASE WHEN "policy_row"."direct_majority_strict" THEN
   3.188 +              "positive_votes" * "policy_row"."direct_majority_den" >
   3.189 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   3.190 +            ELSE
   3.191 +              "positive_votes" * "policy_row"."direct_majority_den" >=
   3.192 +              "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
   3.193 +            END
   3.194 +            AND "positive_votes" >= "policy_row"."direct_majority_positive"
   3.195 +            AND "issue_row"."voter_count"-"negative_votes" >=
   3.196 +                "policy_row"."direct_majority_non_negative",
   3.197 +            "indirect_majority" =
   3.198 +            CASE WHEN "policy_row"."indirect_majority_strict" THEN
   3.199 +              "positive_votes" * "policy_row"."indirect_majority_den" >
   3.200 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   3.201 +            ELSE
   3.202 +              "positive_votes" * "policy_row"."indirect_majority_den" >=
   3.203 +              "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
   3.204 +            END
   3.205 +            AND "positive_votes" >= "policy_row"."indirect_majority_positive"
   3.206 +            AND "issue_row"."voter_count"-"negative_votes" >=
   3.207 +                "policy_row"."indirect_majority_non_negative",
   3.208 +          "schulze_rank"           = "rank_ary"["i"],
   3.209 +          "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
   3.210 +          "worse_than_status_quo"  = "rank_ary"["i"] > "rank_ary"[1],
   3.211 +          "multistage_majority"    = "rank_ary"["i"] >= "rank_ary"[1],
   3.212 +          "reverse_beat_path"      = "matrix"[1]["i"] >= 0,
   3.213 +          "eligible"               = FALSE,
   3.214 +          "winner"                 = FALSE,
   3.215 +          "rank"                   = NULL  -- NOTE: in cases of manual reset of issue state
   3.216 +          WHERE "id" = "initiative_id_v";
   3.217 +        "i" := "i" + 1;
   3.218 +      END LOOP;
   3.219 +      IF "i" != "dimension_v" + 1 THEN
   3.220 +        RAISE EXCEPTION 'Wrong winner count (should not happen)';
   3.221 +      END IF;
   3.222 +      -- take indirect majorities into account:
   3.223 +      LOOP
   3.224 +        UPDATE "initiative" SET "indirect_majority" = TRUE
   3.225 +          FROM (
   3.226 +            SELECT "new_initiative"."id" AS "initiative_id"
   3.227 +            FROM "initiative" "old_initiative"
   3.228 +            JOIN "initiative" "new_initiative"
   3.229 +              ON "new_initiative"."issue_id" = "issue_id_p"
   3.230 +              AND "new_initiative"."indirect_majority" = FALSE
   3.231 +            JOIN "battle" "battle_win"
   3.232 +              ON "battle_win"."issue_id" = "issue_id_p"
   3.233 +              AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
   3.234 +              AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
   3.235 +            JOIN "battle" "battle_lose"
   3.236 +              ON "battle_lose"."issue_id" = "issue_id_p"
   3.237 +              AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
   3.238 +              AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
   3.239 +            WHERE "old_initiative"."issue_id" = "issue_id_p"
   3.240 +            AND "old_initiative"."indirect_majority" = TRUE
   3.241 +            AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
   3.242 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >
   3.243 +              "policy_row"."indirect_majority_num" *
   3.244 +              ("battle_win"."count"+"battle_lose"."count")
   3.245 +            ELSE
   3.246 +              "battle_win"."count" * "policy_row"."indirect_majority_den" >=
   3.247 +              "policy_row"."indirect_majority_num" *
   3.248 +              ("battle_win"."count"+"battle_lose"."count")
   3.249 +            END
   3.250 +            AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
   3.251 +            AND "issue_row"."voter_count"-"battle_lose"."count" >=
   3.252 +                "policy_row"."indirect_majority_non_negative"
   3.253 +          ) AS "subquery"
   3.254 +          WHERE "id" = "subquery"."initiative_id";
   3.255 +        EXIT WHEN NOT FOUND;
   3.256 +      END LOOP;
   3.257 +      -- set "multistage_majority" for remaining matching initiatives:
   3.258 +      UPDATE "initiative" SET "multistage_majority" = TRUE
   3.259 +        FROM (
   3.260 +          SELECT "losing_initiative"."id" AS "initiative_id"
   3.261 +          FROM "initiative" "losing_initiative"
   3.262 +          JOIN "initiative" "winning_initiative"
   3.263 +            ON "winning_initiative"."issue_id" = "issue_id_p"
   3.264 +            AND "winning_initiative"."admitted"
   3.265 +          JOIN "battle" "battle_win"
   3.266 +            ON "battle_win"."issue_id" = "issue_id_p"
   3.267 +            AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
   3.268 +            AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
   3.269 +          JOIN "battle" "battle_lose"
   3.270 +            ON "battle_lose"."issue_id" = "issue_id_p"
   3.271 +            AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
   3.272 +            AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
   3.273 +          WHERE "losing_initiative"."issue_id" = "issue_id_p"
   3.274 +          AND "losing_initiative"."admitted"
   3.275 +          AND "winning_initiative"."schulze_rank" <
   3.276 +              "losing_initiative"."schulze_rank"
   3.277 +          AND "battle_win"."count" > "battle_lose"."count"
   3.278 +          AND (
   3.279 +            "battle_win"."count" > "winning_initiative"."positive_votes" OR
   3.280 +            "battle_lose"."count" < "losing_initiative"."negative_votes" )
   3.281 +        ) AS "subquery"
   3.282 +        WHERE "id" = "subquery"."initiative_id";
   3.283 +      -- mark eligible initiatives:
   3.284 +      UPDATE "initiative" SET "eligible" = TRUE
   3.285 +        WHERE "issue_id" = "issue_id_p"
   3.286 +        AND "initiative"."direct_majority"
   3.287 +        AND "initiative"."indirect_majority"
   3.288 +        AND "initiative"."better_than_status_quo"
   3.289 +        AND (
   3.290 +          "policy_row"."no_multistage_majority" = FALSE OR
   3.291 +          "initiative"."multistage_majority" = FALSE )
   3.292 +        AND (
   3.293 +          "policy_row"."no_reverse_beat_path" = FALSE OR
   3.294 +          "initiative"."reverse_beat_path" = FALSE );
   3.295 +      -- mark final winner:
   3.296 +      UPDATE "initiative" SET "winner" = TRUE
   3.297 +        FROM (
   3.298 +          SELECT "id" AS "initiative_id"
   3.299 +          FROM "initiative"
   3.300 +          WHERE "issue_id" = "issue_id_p" AND "eligible"
   3.301 +          ORDER BY
   3.302 +            "schulze_rank",
   3.303 +            "id"
   3.304 +          LIMIT 1
   3.305 +        ) AS "subquery"
   3.306 +        WHERE "id" = "subquery"."initiative_id";
   3.307 +      -- write (final) ranks:
   3.308 +      "rank_v" := 1;
   3.309 +      FOR "initiative_id_v" IN
   3.310 +        SELECT "id"
   3.311 +        FROM "initiative"
   3.312 +        WHERE "issue_id" = "issue_id_p" AND "admitted"
   3.313 +        ORDER BY
   3.314 +          "winner" DESC,
   3.315 +          "eligible" DESC,
   3.316 +          "schulze_rank",
   3.317 +          "id"
   3.318 +      LOOP
   3.319 +        UPDATE "initiative" SET "rank" = "rank_v"
   3.320 +          WHERE "id" = "initiative_id_v";
   3.321 +        "rank_v" := "rank_v" + 1;
   3.322 +      END LOOP;
   3.323 +      -- set schulze rank of status quo and mark issue as finished:
   3.324 +      UPDATE "issue" SET
   3.325 +        "status_quo_schulze_rank" = "rank_ary"[1],
   3.326 +        "state" =
   3.327 +          CASE WHEN EXISTS (
   3.328 +            SELECT NULL FROM "initiative"
   3.329 +            WHERE "issue_id" = "issue_id_p" AND "winner"
   3.330 +          ) THEN
   3.331 +            'finished_with_winner'::"issue_state"
   3.332 +          ELSE
   3.333 +            'finished_without_winner'::"issue_state"
   3.334 +          END,
   3.335 +        "closed" = "phase_finished",
   3.336 +        "phase_finished" = NULL
   3.337 +        WHERE "id" = "issue_id_p";
   3.338 +      RETURN;
   3.339 +    END;
   3.340 +  $$;
   3.341 +
   3.342 +COMMIT;
     4.1 --- a/update/core-update.v3.0.0-v3.0.1.sql	Wed Mar 26 20:08:50 2014 +0100
     4.2 +++ b/update/core-update.v3.0.0-v3.0.1.sql	Sat Apr 05 23:04:43 2014 +0200
     4.3 @@ -1,9 +1,21 @@
     4.4 +-- NOTICE: This update script disables the "no_reserve_beat_path" setting for
     4.5 +--         all policies. If this is not intended, please edit this script
     4.6 +--         before applying it to your database.
     4.7 +
     4.8  BEGIN;
     4.9  
    4.10  CREATE OR REPLACE VIEW "liquid_feedback_version" AS
    4.11    SELECT * FROM (VALUES ('3.0.1', 3, 0, 1))
    4.12    AS "subquery"("string", "major", "minor", "revision");
    4.13  
    4.14 +ALTER TABLE "policy" ALTER COLUMN "no_reverse_beat_path" SET DEFAULT FALSE;
    4.15 +
    4.16 +UPDATE "policy" SET "no_reverse_beat_path" = FALSE;  -- recommended
    4.17 +
    4.18 +COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'EXPERIMENTAL FEATURE: Causes initiatives with "reverse_beat_path" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."reverse_beat_path". This option ensures both that a winning initiative is never tied in a (weak) condorcet paradox with the status quo and a winning initiative always beats the status quo directly with a simple majority.';
    4.19 +
    4.20 +COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'EXPERIMENTAL FEATURE: Causes initiatives with "multistage_majority" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."multistage_majority". This disqualifies initiatives which could cause an instable result. An instable result in this meaning is a result such that repeating the ballot with same preferences but with the winner of the first ballot as status quo would lead to a different winner in the second ballot. If there are no direct majorities required for the winner, or if in direct comparison only simple majorities are required and "no_reverse_beat_path" is true, then results are always stable and this flag does not have any effect on the winner (but still affects the "eligible" flag of an "initiative").';
    4.21 +
    4.22  ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4;
    4.23  
    4.24  ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";

Impressum / About Us