liquid_feedback_core
changeset 154:8e7d583e02f9
Configurable supermajority requirements related to "disqualification" of initiatives for stability reasons
- Added columns "no_multistage_majority" and "no_reverse_beat_path" to table "policy"
- Renamed columns "(un)favored" to "(un)favored_to_status_quo"
- Renamed "preliminary_rank" to "schulze_rank"
- "schulze_rank" may have a gap due to status quo
- Renamed column "disqualified" to "multistage_majority" in table "initiative"
- Added column "reverse_beat_path" to table "initiative"
- Added column "eligible" to table "initiative"
- Removed column "final_rank" from table "initiative"
- Modified function "calculate_ranks" to fit above changes
- Added columns "no_multistage_majority" and "no_reverse_beat_path" to table "policy"
- Renamed columns "(un)favored" to "(un)favored_to_status_quo"
- Renamed "preliminary_rank" to "schulze_rank"
- "schulze_rank" may have a gap due to status quo
- Renamed column "disqualified" to "multistage_majority" in table "initiative"
- Added column "reverse_beat_path" to table "initiative"
- Added column "eligible" to table "initiative"
- Removed column "final_rank" from table "initiative"
- Modified function "calculate_ranks" to fit above changes
author | jbe |
---|---|
date | Fri Jun 03 02:50:59 2011 +0200 (2011-06-03) |
parents | c7dbb24108de |
children | 043e6e235a53 |
files | core.sql demo.sql |
line diff
1.1 --- a/core.sql Thu Jun 02 03:19:39 2011 +0200 1.2 +++ b/core.sql Fri Jun 03 02:50:59 2011 +0200 1.3 @@ -312,7 +312,9 @@ 1.4 "majority_den" INT4 NOT NULL DEFAULT 2, 1.5 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE, 1.6 "majority_positive" INT4 NOT NULL DEFAULT 0, 1.7 - "majority_non_negative" INT4 NOT NULL DEFAULT 0 ); 1.8 + "majority_non_negative" INT4 NOT NULL DEFAULT 0, 1.9 + "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE, 1.10 + "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT FALSE ); 1.11 CREATE INDEX "policy_active_idx" ON "policy" ("active"); 1.12 1.13 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)'; 1.14 @@ -332,6 +334,8 @@ 1.15 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.16 COMMENT ON COLUMN "policy"."majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be "attainable".'; 1.17 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.18 +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.19 +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.20 1.21 1.22 CREATE TABLE "unit" ( 1.23 @@ -538,11 +542,12 @@ 1.24 "positive_votes" INT4, 1.25 "negative_votes" INT4, 1.26 "attainable" BOOLEAN, 1.27 - "favored" BOOLEAN, 1.28 - "unfavored" BOOLEAN, 1.29 - "preliminary_rank" INT4, 1.30 - "final_rank" INT4, 1.31 - "disqualified" BOOLEAN, 1.32 + "favored_to_status_quo" BOOLEAN, 1.33 + "unfavored_to_status_quo" BOOLEAN, 1.34 + "schulze_rank" INT4, 1.35 + "multistage_majority" BOOLEAN, 1.36 + "reverse_beat_path" BOOLEAN, 1.37 + "eligible" BOOLEAN, 1.38 "winner" BOOLEAN, 1.39 "text_search_data" TSVECTOR, 1.40 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" 1.41 @@ -553,11 +558,12 @@ 1.42 CHECK ("revoked" ISNULL OR "admitted" ISNULL), 1.43 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( 1.44 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR 1.45 - ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND 1.46 - "attainable" ISNULL AND "favored" ISNULL AND "unfavored" ISNULL AND 1.47 - "disqualified" ISNULL AND "preliminary_rank" ISNULL AND 1.48 - "final_rank" ISNULL AND "winner" ISNULL ) ), 1.49 - CONSTRAINT "favored_excludes_unfavored" CHECK (NOT ("favored" AND "unfavored")) ); 1.50 + ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND "attainable" ISNULL AND 1.51 + "favored_to_status_quo" ISNULL AND "unfavored_to_status_quo" ISNULL AND 1.52 + "schulze_rank" ISNULL AND 1.53 + "multistage_majority" ISNULL AND "reverse_beat_path" ISNULL AND 1.54 + "winner" ISNULL ) ), 1.55 + CONSTRAINT "favored_excludes_unfavored" CHECK (NOT ("favored_to_status_quo" AND "unfavored_to_status_quo")) ); 1.56 CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); 1.57 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked"); 1.58 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data"); 1.59 @@ -577,15 +583,16 @@ 1.60 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.61 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.62 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.63 -COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; 1.64 -COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 1.65 -COMMENT ON COLUMN "initiative"."attainable" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den", and "positive_votes" is greater-equal than "majority_positive", and ("positive_votes"+abstentions) is greater-equal than "majority_non_negative"'; 1.66 -COMMENT ON COLUMN "initiative"."favored" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)'; 1.67 -COMMENT ON COLUMN "initiative"."unfavored" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)'; 1.68 -COMMENT ON COLUMN "initiative"."preliminary_rank" IS 'Schulze-Ranking without tie-breaking'; 1.69 -COMMENT ON COLUMN "initiative"."final_rank" IS 'Schulze-Ranking after tie-breaking'; 1.70 -COMMENT ON COLUMN "initiative"."disqualified" IS 'TRUE, if initiative may not win, because it either (a) has no better rank than the status quo, or (b) because 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.71 -COMMENT ON COLUMN "initiative"."winner" IS 'TRUE, if initiative is final winner (best ranked initiative being "attainable" and not "disqualified")'; 1.72 +COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; 1.73 +COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 1.74 +COMMENT ON COLUMN "initiative"."attainable" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den", and "positive_votes" is greater-equal than "majority_positive", and ("positive_votes"+abstentions) is greater-equal than "majority_non_negative"'; 1.75 +COMMENT ON COLUMN "initiative"."favored_to_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)'; 1.76 +COMMENT ON COLUMN "initiative"."unfavored_to_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)'; 1.77 +COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking'; 1.78 +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.79 +COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there a beat path (may include ties), from this initiative to the status quo'; 1.80 +COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative is "attainable" and depending on selected policy has no "multistage_majority" or "reverse_beat_path"'; 1.81 +COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"'; 1.82 1.83 1.84 CREATE TABLE "battle" ( 1.85 @@ -3765,28 +3772,30 @@ 1.86 ORDER BY "id" 1.87 LOOP 1.88 UPDATE "initiative" SET 1.89 - "favored" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 1.90 - "unfavored" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 1.91 - "preliminary_rank" = "rank_ary"["i"], 1.92 - "disqualified" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], 1.93 - "winner" = FALSE 1.94 + "favored_to_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 1.95 + "unfavored_to_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 1.96 + "schulze_rank" = "rank_ary"["i"], 1.97 + "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], 1.98 + "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0 1.99 WHERE "id" = "initiative_id_v"; 1.100 "i" := "i" + 1; 1.101 END LOOP; 1.102 IF "i" != "dimension_v" THEN 1.103 RAISE EXCEPTION 'Wrong winner count (should not happen)'; 1.104 END IF; 1.105 + 1.106 -- remove possible gap in preliminary ranks: 1.107 - IF NOT EXISTS ( 1.108 - SELECT NULL FROM "initiative" 1.109 - WHERE "issue_id" = "issue_id_p" 1.110 - AND "favored"=FALSE AND "unfavored"=FALSE 1.111 - ) THEN 1.112 - UPDATE "initiative" SET "preliminary_rank" = "preliminary_rank" - 1 1.113 - WHERE "issue_id" = "issue_id_p" AND "unfavored"; 1.114 - END IF; 1.115 - -- disqualify certain initiatives to enforce a stable result: 1.116 - UPDATE "initiative" SET "disqualified" = TRUE 1.117 + --IF NOT EXISTS ( 1.118 + -- SELECT NULL FROM "initiative" 1.119 + -- WHERE "issue_id" = "issue_id_p" 1.120 + -- AND "favored_to_status_quo"=FALSE AND "unfavored_to_status_quo"=FALSE 1.121 + --) THEN 1.122 + -- UPDATE "initiative" SET "schulze_rank" = "schulze_rank" - 1 1.123 + -- WHERE "issue_id" = "issue_id_p" AND "unfavored"; 1.124 + --END IF; 1.125 + 1.126 + -- set "multistage_majority" for remaining matching initiatives: 1.127 + UPDATE "initiative" SET "multistage_majority" = TRUE 1.128 FROM ( 1.129 SELECT "losing_initiative"."id" AS "initiative_id" 1.130 FROM "initiative" "losing_initiative" 1.131 @@ -3803,36 +3812,45 @@ 1.132 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 1.133 WHERE "losing_initiative"."issue_id" = "issue_id_p" 1.134 AND "losing_initiative"."admitted" 1.135 - AND "winning_initiative"."preliminary_rank" < 1.136 - "losing_initiative"."preliminary_rank" 1.137 + AND "winning_initiative"."schulze_rank" < 1.138 + "losing_initiative"."schulze_rank" 1.139 AND "battle_win"."count" > "battle_lose"."count" 1.140 AND ( 1.141 "battle_win"."count" > "winning_initiative"."positive_votes" OR 1.142 "battle_lose"."count" < "losing_initiative"."negative_votes" ) 1.143 ) AS "subquery" 1.144 WHERE "id" = "subquery"."initiative_id"; 1.145 - -- calculate final ranks (start counting with 1, no equal ranks): 1.146 + -- mark eligible initiatives: 1.147 "rank_v" := 1; 1.148 - FOR "initiative_id_v" IN 1.149 - SELECT "id" FROM "initiative" 1.150 - WHERE "issue_id" = "issue_id_p" AND "admitted" 1.151 - ORDER BY "preliminary_rank", "id" 1.152 - LOOP 1.153 - UPDATE "initiative" SET "final_rank" = "rank_v" 1.154 - WHERE "id" = "initiative_id_v"; 1.155 - "rank_v" := "rank_v" + 1; 1.156 - END LOOP; 1.157 + UPDATE "initiative" SET "eligible" = TRUE 1.158 + FROM ( 1.159 + SELECT "initiative"."id" AS "initiative_id" 1.160 + FROM "issue" 1.161 + JOIN "policy" 1.162 + ON "issue"."policy_id" = "policy"."id" 1.163 + JOIN "initiative" 1.164 + ON "issue"."id" = "initiative"."issue_id" 1.165 + WHERE "issue_id" = "issue_id_p" 1.166 + AND "initiative"."attainable" 1.167 + AND "initiative"."favored_to_status_quo" 1.168 + AND ( 1.169 + "policy"."no_multistage_majority" = FALSE OR 1.170 + "initiative"."multistage_majority" = FALSE ) 1.171 + AND ( 1.172 + "policy"."no_reverse_beat_path" = FALSE OR 1.173 + "initiative"."reverse_beat_path" = FALSE ) 1.174 + ) AS "subquery" 1.175 + WHERE "id" = "subquery"."initiative_id"; 1.176 -- mark final winner: 1.177 UPDATE "initiative" SET "winner" = TRUE 1.178 FROM ( 1.179 SELECT "id" AS "initiative_id" 1.180 FROM "initiative" 1.181 - WHERE "issue_id" = "issue_id_p" 1.182 - AND "attainable" AND NOT "disqualified" 1.183 - ORDER BY "final_rank" 1.184 + WHERE "issue_id" = "issue_id_p" AND "eligible" 1.185 + ORDER BY "schulze_rank", "id" 1.186 LIMIT 1 1.187 ) AS "subquery" 1.188 - WHERE "id" = "subquery"."initiative_id"; 1.189 + WHERE "id" = "subquery"."initiative_id"; 1.190 END IF; 1.191 -- mark issue as finished: 1.192 UPDATE "issue" SET
2.1 --- a/demo.sql Thu Jun 02 03:19:39 2011 +0200 2.2 +++ b/demo.sql Fri Jun 03 02:50:59 2011 +0200 2.3 @@ -39,13 +39,17 @@ 2.4 "verification_time", 2.5 "voting_time", 2.6 "issue_quorum_num", "issue_quorum_den", 2.7 - "initiative_quorum_num", "initiative_quorum_den" 2.8 + "initiative_quorum_num", "initiative_quorum_den", 2.9 + "majority_num", "majority_den", "majority_strict", 2.10 + "no_multistage_majority", "no_reverse_beat_path" 2.11 ) VALUES ( 2.12 1, 2.13 'Default policy', 2.14 '1 hour', '1 hour', '1 hour', '1 hour', 2.15 25, 100, 2.16 - 20, 100 ); 2.17 + 20, 100, 2.18 + 1, 2, TRUE, 2.19 + TRUE, FALSE ); 2.20 2.21 CREATE FUNCTION "time_warp"() RETURNS VOID 2.22 LANGUAGE 'plpgsql' VOLATILE AS $$