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"
- 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 $$