liquid_feedback_core

changeset 155:043e6e235a53

Added configuration option to allow indirect supermajority requirements

- Added column "majority_indirect" to table "policy"
- Do not calculate "attainable" field in function "close_voting" but in function "calculate_ranks"
- Copy "positive_votes" and "negative_votes" from "battle" table, instead of calculating them seperatly
- Optionally regarding indirect (super)majorities in "calculate_ranks"
author jbe
date Fri Jun 03 13:28:51 2011 +0200 (2011-06-03)
parents 8e7d583e02f9
children fb5dc1bf921f
files core.sql demo.sql
line diff
     1.1 --- a/core.sql	Fri Jun 03 02:50:59 2011 +0200
     1.2 +++ b/core.sql	Fri Jun 03 13:28:51 2011 +0200
     1.3 @@ -313,8 +313,9 @@
     1.4          "majority_strict"       BOOLEAN         NOT NULL DEFAULT TRUE,
     1.5          "majority_positive"     INT4            NOT NULL DEFAULT 0,
     1.6          "majority_non_negative" INT4            NOT NULL DEFAULT 0,
     1.7 -        "no_multistage_majority" BOOLEAN        NOT NULL DEFAULT FALSE,
     1.8 -        "no_reverse_beat_path"  BOOLEAN         NOT NULL DEFAULT FALSE );
     1.9 +        "majority_indirect"     BOOLEAN         NOT NULL DEFAULT TRUE,
    1.10 +        "no_reverse_beat_path"  BOOLEAN         NOT NULL DEFAULT TRUE,
    1.11 +        "no_multistage_majority" BOOLEAN        NOT NULL DEFAULT FALSE );
    1.12  CREATE INDEX "policy_active_idx" ON "policy" ("active");
    1.13  
    1.14  COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
    1.15 @@ -334,8 +335,9 @@
    1.16  COMMENT ON COLUMN "policy"."majority_strict"       IS 'If TRUE, then the majority must be strictly greater than "majority_num"/"majority_den", otherwise it may also be equal.';
    1.17  COMMENT ON COLUMN "policy"."majority_positive"     IS 'Absolute number of "positive_votes" neccessary for an initiative to be "attainable".';
    1.18  COMMENT ON COLUMN "policy"."majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be "attainable".';
    1.19 +COMMENT ON COLUMN "policy"."majority_indirect"     IS 'If TRUE, then the majority specified by "majority_num", "majority_den", "majority_strict", "majority_positive" and "majority_non_negative" might be indirect; if FALSE, then the status quo must be beaten directly with that majority';
    1.20 +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".';
    1.21  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".';
    1.22 -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".';
    1.23  
    1.24  
    1.25  CREATE TABLE "unit" (
    1.26 @@ -3543,55 +3545,6 @@
    1.27            FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
    1.28          )
    1.29          WHERE "id" = "issue_id_p";
    1.30 -      -- calculate "positive_votes", "negative_votes" and "attainable":
    1.31 -      UPDATE "initiative" SET
    1.32 -        "positive_votes" = "vote_counts"."positive_votes",
    1.33 -        "negative_votes" = "vote_counts"."negative_votes",
    1.34 -        "attainable" =
    1.35 -          CASE WHEN "majority_strict" THEN
    1.36 -            "vote_counts"."positive_votes" * "majority_den" >
    1.37 -            "majority_num" *
    1.38 -            ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
    1.39 -          ELSE
    1.40 -            "vote_counts"."positive_votes" * "majority_den" >=
    1.41 -            "majority_num" *
    1.42 -            ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
    1.43 -          END
    1.44 -          AND "vote_counts"."positive_votes" >= "policy"."majority_positive"
    1.45 -          AND "issue"."voter_count"-"vote_counts"."negative_votes" >=
    1.46 -              "policy"."majority_non_negative"
    1.47 -        FROM
    1.48 -          ( SELECT
    1.49 -              "initiative"."id" AS "initiative_id",
    1.50 -              coalesce(
    1.51 -                sum(
    1.52 -                  CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
    1.53 -                ),
    1.54 -                0
    1.55 -              ) AS "positive_votes",
    1.56 -              coalesce(
    1.57 -                sum(
    1.58 -                  CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
    1.59 -                ),
    1.60 -                0
    1.61 -              ) AS "negative_votes"
    1.62 -            FROM "initiative"
    1.63 -            JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
    1.64 -            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
    1.65 -            LEFT JOIN "direct_voter"
    1.66 -              ON "direct_voter"."issue_id" = "initiative"."issue_id"
    1.67 -            LEFT JOIN "vote"
    1.68 -              ON "vote"."initiative_id" = "initiative"."id"
    1.69 -              AND "vote"."member_id" = "direct_voter"."member_id"
    1.70 -            WHERE "initiative"."issue_id" = "issue_id_p"
    1.71 -            AND "initiative"."admitted"  -- NOTE: NULL case is handled too
    1.72 -            GROUP BY "initiative"."id"
    1.73 -          ) AS "vote_counts",
    1.74 -          "issue",
    1.75 -          "policy"
    1.76 -        WHERE "vote_counts"."initiative_id" = "initiative"."id"
    1.77 -        AND "issue"."id" = "initiative"."issue_id"
    1.78 -        AND "policy"."id" = "issue"."policy_id";
    1.79        -- materialize battle_view:
    1.80        -- NOTE: "closed" column of issue must be set at this point
    1.81        DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
    1.82 @@ -3604,6 +3557,18 @@
    1.83          "winning_initiative_id", "losing_initiative_id",
    1.84          "count"
    1.85          FROM "battle_view" WHERE "issue_id" = "issue_id_p";
    1.86 +      -- copy "positive_votes" and "negative_votes" from "battle" table:
    1.87 +      UPDATE "initiative" SET
    1.88 +        "positive_votes" = "battle_win"."count",
    1.89 +        "negative_votes" = "battle_lose"."count"
    1.90 +        FROM "battle" AS "battle_win", "battle" AS "battle_lose"
    1.91 +        WHERE
    1.92 +          "battle_win"."issue_id" = "issue_id_p" AND
    1.93 +          "battle_win"."winning_initiative_id" = "initiative"."id" AND
    1.94 +          "battle_win"."losing_initiative_id" ISNULL AND
    1.95 +          "battle_lose"."issue_id" = "issue_id_p" AND
    1.96 +          "battle_lose"."losing_initiative_id" = "initiative"."id" AND
    1.97 +          "battle_lose"."winning_initiative_id" ISNULL;
    1.98      END;
    1.99    $$;
   1.100  
   1.101 @@ -3634,6 +3599,8 @@
   1.102    RETURNS VOID
   1.103    LANGUAGE 'plpgsql' VOLATILE AS $$
   1.104      DECLARE
   1.105 +      "issue_row"         "issue"%ROWTYPE;
   1.106 +      "policy_row"        "policy"%ROWTYPE;
   1.107        "dimension_v"       INTEGER;
   1.108        "vote_matrix"       INT4[][];  -- absolute votes
   1.109        "matrix"            INT8[][];  -- defeat strength / best paths
   1.110 @@ -3647,7 +3614,11 @@
   1.111        "winners_ary"       INTEGER[];
   1.112        "initiative_id_v"   "initiative"."id"%TYPE;
   1.113      BEGIN
   1.114 -      PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
   1.115 +      SELECT * INTO "issue_row"
   1.116 +        FROM "issue" WHERE "id" = "issue_id_p"
   1.117 +        FOR UPDATE;
   1.118 +      SELECT * INTO "policy_row"
   1.119 +        FROM "policy" WHERE "id" = "issue_row"."policy_id";
   1.120        SELECT count(1) INTO "dimension_v"
   1.121          FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
   1.122        IF "dimension_v" > 1 THEN
   1.123 @@ -3772,6 +3743,17 @@
   1.124            ORDER BY "id"
   1.125          LOOP
   1.126            UPDATE "initiative" SET
   1.127 +            "attainable" =
   1.128 +              CASE WHEN "policy_row"."majority_strict" THEN
   1.129 +                "positive_votes" * "policy_row"."majority_den" >
   1.130 +                "policy_row"."majority_num" * ("positive_votes"+"negative_votes")
   1.131 +              ELSE
   1.132 +                "positive_votes" * "policy_row"."majority_den" >=
   1.133 +                "policy_row"."majority_num" * ("positive_votes"+"negative_votes")
   1.134 +              END
   1.135 +              AND "positive_votes" >= "policy_row"."majority_positive"
   1.136 +              AND "issue_row"."voter_count"-"negative_votes" >=
   1.137 +                  "policy_row"."majority_non_negative",
   1.138              "favored_to_status_quo"   = "rank_ary"["i"] < "rank_ary"["dimension_v"],
   1.139              "unfavored_to_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
   1.140              "schulze_rank"            = "rank_ary"["i"],
   1.141 @@ -3783,17 +3765,43 @@
   1.142          IF "i" != "dimension_v" THEN
   1.143            RAISE EXCEPTION 'Wrong winner count (should not happen)';
   1.144          END IF;
   1.145 -
   1.146 -        -- remove possible gap in preliminary ranks:
   1.147 -        --IF NOT EXISTS (
   1.148 -        --  SELECT NULL FROM "initiative"
   1.149 -        --  WHERE "issue_id" = "issue_id_p"
   1.150 -        --  AND "favored_to_status_quo"=FALSE AND "unfavored_to_status_quo"=FALSE
   1.151 -        --) THEN
   1.152 -        --  UPDATE "initiative" SET "schulze_rank" = "schulze_rank" - 1
   1.153 -        --    WHERE "issue_id" = "issue_id_p" AND "unfavored";
   1.154 -        --END IF;
   1.155 -
   1.156 +        if "policy_row"."majority_indirect" THEN
   1.157 +          -- take indirect majorities into account:
   1.158 +          LOOP
   1.159 +            UPDATE "initiative" SET "attainable" = TRUE
   1.160 +              FROM (
   1.161 +                SELECT "new_initiative"."id" AS "initiative_id"
   1.162 +                FROM "initiative" "old_initiative"
   1.163 +                JOIN "initiative" "new_initiative"
   1.164 +                  ON "new_initiative"."issue_id" = "issue_id_p"
   1.165 +                  AND "new_initiative"."attainable" = FALSE
   1.166 +                JOIN "battle" "battle_win"
   1.167 +                  ON "battle_win"."issue_id" = "issue_id_p"
   1.168 +                  AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
   1.169 +                  AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
   1.170 +                JOIN "battle" "battle_lose"
   1.171 +                  ON "battle_lose"."issue_id" = "issue_id_p"
   1.172 +                  AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
   1.173 +                  AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
   1.174 +                WHERE "old_initiative"."issue_id" = "issue_id_p"
   1.175 +                AND "old_initiative"."attainable" = TRUE
   1.176 +                AND CASE WHEN "policy_row"."majority_strict" THEN
   1.177 +                  "battle_win"."count" * "policy_row"."majority_den" >
   1.178 +                  "policy_row"."majority_num" *
   1.179 +                  ("battle_win"."count"+"battle_lose"."count")
   1.180 +                ELSE
   1.181 +                  "battle_win"."count" * "policy_row"."majority_den" >=
   1.182 +                  "policy_row"."majority_num" *
   1.183 +                  ("battle_win"."count"+"battle_lose"."count")
   1.184 +                END
   1.185 +                AND "battle_win"."count" >= "policy_row"."majority_positive"
   1.186 +                AND "issue_row"."voter_count"-"battle_lose"."count" >=
   1.187 +                    "policy_row"."majority_non_negative"
   1.188 +              ) AS "subquery"
   1.189 +              WHERE "id" = "subquery"."initiative_id";
   1.190 +            EXIT WHEN NOT FOUND;
   1.191 +          END LOOP;
   1.192 +        END IF;
   1.193          -- set "multistage_majority" for remaining matching initiatives:
   1.194           UPDATE "initiative" SET "multistage_majority" = TRUE
   1.195            FROM (
     2.1 --- a/demo.sql	Fri Jun 03 02:50:59 2011 +0200
     2.2 +++ b/demo.sql	Fri Jun 03 13:28:51 2011 +0200
     2.3 @@ -32,24 +32,26 @@
     2.4  UPDATE "member" SET "password" = '$1$PcI6b1Bg$2SHjAZH2nMLFp0fxHis.Q0';
     2.5  
     2.6  INSERT INTO "policy" (
     2.7 -        "index",
     2.8 -        "name",
     2.9 -        "admission_time",
    2.10 -        "discussion_time",
    2.11 -        "verification_time",
    2.12 -        "voting_time",
    2.13 -        "issue_quorum_num", "issue_quorum_den",
    2.14 -        "initiative_quorum_num", "initiative_quorum_den",
    2.15 -	"majority_num", "majority_den", "majority_strict",
    2.16 -	"no_multistage_majority", "no_reverse_beat_path"
    2.17 -    ) VALUES (
    2.18 -        1,
    2.19 -        'Default policy',
    2.20 -        '1 hour', '1 hour', '1 hour', '1 hour',
    2.21 -        25, 100,
    2.22 -        20, 100,
    2.23 -        1, 2, TRUE,
    2.24 -        TRUE, FALSE );
    2.25 +    "index",
    2.26 +    "name",
    2.27 +    "admission_time",
    2.28 +    "discussion_time",
    2.29 +    "verification_time",
    2.30 +    "voting_time",
    2.31 +    "issue_quorum_num", "issue_quorum_den",
    2.32 +    "initiative_quorum_num", "initiative_quorum_den",
    2.33 +    "majority_num", "majority_den", "majority_strict",
    2.34 +    "majority_indirect",
    2.35 +    "no_reverse_beat_path", "no_multistage_majority"
    2.36 +  ) VALUES (
    2.37 +    1,
    2.38 +    'Default policy',
    2.39 +    '1 hour', '1 hour', '1 hour', '1 hour',
    2.40 +    25, 100,
    2.41 +    20, 100,
    2.42 +    1, 2, TRUE,
    2.43 +    TRUE,
    2.44 +    TRUE, FALSE );
    2.45  
    2.46  CREATE FUNCTION "time_warp"() RETURNS VOID
    2.47    LANGUAGE 'plpgsql' VOLATILE AS $$

Impressum / About Us