liquid_feedback_core
changeset 126:5edfb00d840a
Implementation of Schulze's Supermajority Requirements
Changes in detail:
- Renamed column "agreed" of table "initiative" to "majority"
- Added column "winner" to table "initiative"
- Extended constraint "non_admitted_initiatives_cant_contain_voting_results" of table "initiative" to "rank" and "winner" columns
- Removed constraint "non_agreed_initiatives_cant_get_a_rank" from table "initiative"
- Replaced PRIMARY KEY of "battle" table by a single NOT NULL constraint on column "issue_id" and three (partial) indicies allowing NULL values as initiative ids
- Added constraint "initiative_ids_not_equal" to "battle" table
- Added view "battle_participant" which selects admitted initiatives plus one virtual "status-quo" initiative for each issue denoted by an initiative_id being NULL
- Modified "battle_view" to include all admitted initiatives plus the virtual "status-quo" initiative
- Modified function "calculate_ranks" to respect all battle participants, to set the rank of all initiatives where the rank is better than status-quo, and to mark the final winner (if existent)
Changes in detail:
- Renamed column "agreed" of table "initiative" to "majority"
- Added column "winner" to table "initiative"
- Extended constraint "non_admitted_initiatives_cant_contain_voting_results" of table "initiative" to "rank" and "winner" columns
- Removed constraint "non_agreed_initiatives_cant_get_a_rank" from table "initiative"
- Replaced PRIMARY KEY of "battle" table by a single NOT NULL constraint on column "issue_id" and three (partial) indicies allowing NULL values as initiative ids
- Added constraint "initiative_ids_not_equal" to "battle" table
- Added view "battle_participant" which selects admitted initiatives plus one virtual "status-quo" initiative for each issue denoted by an initiative_id being NULL
- Modified "battle_view" to include all admitted initiatives plus the virtual "status-quo" initiative
- Modified function "calculate_ranks" to respect all battle participants, to set the rank of all initiatives where the rank is better than status-quo, and to mark the final winner (if existent)
author | jbe |
---|---|
date | Tue May 24 03:01:49 2011 +0200 (2011-05-24) |
parents | 96db76cde218 |
children | bbadd6b2634d |
files | core.sql |
line diff
1.1 --- a/core.sql Sun Mar 13 13:29:33 2011 +0100 1.2 +++ b/core.sql Tue May 24 03:01:49 2011 +0200 1.3 @@ -537,8 +537,9 @@ 1.4 "satisfied_informed_supporter_count" INT4, 1.5 "positive_votes" INT4, 1.6 "negative_votes" INT4, 1.7 - "agreed" BOOLEAN, 1.8 + "majority" BOOLEAN, 1.9 "rank" INT4, 1.10 + "winner" BOOLEAN, 1.11 "text_search_data" TSVECTOR, 1.12 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" 1.13 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL), 1.14 @@ -547,11 +548,9 @@ 1.15 CONSTRAINT "revoked_initiatives_cant_be_admitted" 1.16 CHECK ("revoked" ISNULL OR "admitted" ISNULL), 1.17 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" 1.18 - CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)), 1.19 - CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null" 1.20 - CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL), 1.21 - CONSTRAINT "non_agreed_initiatives_cant_get_a_rank" 1.22 - CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) ); 1.23 + CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "majority" ISNULL AND "rank" ISNULL AND "winner" ISNULL)), 1.24 + CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_majority_must_be_null" 1.25 + CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "majority" NOTNULL) ); 1.26 CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); 1.27 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked"); 1.28 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data"); 1.29 @@ -573,20 +572,27 @@ 1.30 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.31 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; 1.32 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 1.33 -COMMENT ON COLUMN "initiative"."agreed" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den"'; 1.34 -COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"'; 1.35 +COMMENT ON COLUMN "initiative"."majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den"'; 1.36 +COMMENT ON COLUMN "initiative"."rank" IS 'Rank of initiatives, which are ranked better than the "status-quo"; set to NULL for initiatives ranked worse than status-quo or being non-admitted'; 1.37 +COMMENT ON COLUMN "initiative"."winner" IS 'TRUE, if initiative is final winner (having "majority"=TRUE and the smallest rank among other initiatives with "majority"=TRUE); set to NULL for non-admitted initiatives'; 1.38 1.39 1.40 CREATE TABLE "battle" ( 1.41 - PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"), 1.42 - "issue_id" INT4, 1.43 + "issue_id" INT4 NOT NULL, 1.44 "winning_initiative_id" INT4, 1.45 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.46 "losing_initiative_id" INT4, 1.47 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.48 - "count" INT4 NOT NULL); 1.49 - 1.50 -COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue'; 1.51 + "count" INT4 NOT NULL, 1.52 + CONSTRAINT "initiative_ids_not_equal" CHECK ( 1.53 + "winning_initiative_id" != "losing_initiative_id" OR 1.54 + ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR 1.55 + ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) ); 1.56 +CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id"); 1.57 +CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL; 1.58 +CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL; 1.59 + 1.60 +COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue; NULL as initiative_id denotes virtual "status-quo" initiative'; 1.61 1.62 1.63 CREATE TABLE "ignored_initiative" ( 1.64 @@ -1870,6 +1876,18 @@ 1.65 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction'; 1.66 1.67 1.68 +CREATE VIEW "battle_participant" AS 1.69 + SELECT "initiative"."id", "initiative"."issue_id" 1.70 + FROM "issue" JOIN "initiative" 1.71 + ON "issue"."id" = "initiative"."issue_id" 1.72 + WHERE "initiative"."admitted" 1.73 + UNION ALL 1.74 + SELECT NULL, "id" AS "issue_id" 1.75 + FROM "issue"; 1.76 + 1.77 +COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference'; 1.78 + 1.79 + 1.80 CREATE VIEW "battle_view" AS 1.81 SELECT 1.82 "issue"."id" AS "issue_id", 1.83 @@ -1884,12 +1902,10 @@ 1.84 FROM "issue" 1.85 LEFT JOIN "direct_voter" 1.86 ON "issue"."id" = "direct_voter"."issue_id" 1.87 - JOIN "initiative" AS "winning_initiative" 1.88 + JOIN "battle_participant" AS "winning_initiative" 1.89 ON "issue"."id" = "winning_initiative"."issue_id" 1.90 - AND "winning_initiative"."agreed" 1.91 - JOIN "initiative" AS "losing_initiative" 1.92 + JOIN "battle_participant" AS "losing_initiative" 1.93 ON "issue"."id" = "losing_initiative"."issue_id" 1.94 - AND "losing_initiative"."agreed" 1.95 LEFT JOIN "vote" AS "better_vote" 1.96 ON "direct_voter"."member_id" = "better_vote"."member_id" 1.97 AND "winning_initiative"."id" = "better_vote"."initiative_id" 1.98 @@ -1898,13 +1914,16 @@ 1.99 AND "losing_initiative"."id" = "worse_vote"."initiative_id" 1.100 WHERE "issue"."closed" NOTNULL 1.101 AND "issue"."cleaned" ISNULL 1.102 - AND "winning_initiative"."id" != "losing_initiative"."id" 1.103 + AND ( 1.104 + "winning_initiative"."id" != "losing_initiative"."id" OR 1.105 + ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR 1.106 + ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) ) 1.107 GROUP BY 1.108 "issue"."id", 1.109 "winning_initiative"."id", 1.110 "losing_initiative"."id"; 1.111 1.112 -COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table'; 1.113 +COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative (or status-quo) to another initiative (or status-quo); Used to fill "battle" table'; 1.114 1.115 1.116 CREATE VIEW "expired_session" AS 1.117 @@ -3495,7 +3514,7 @@ 1.118 UPDATE "initiative" SET 1.119 "positive_votes" = "vote_counts"."positive_votes", 1.120 "negative_votes" = "vote_counts"."negative_votes", 1.121 - "agreed" = CASE WHEN "majority_strict" THEN 1.122 + "majority" = CASE WHEN "majority_strict" THEN 1.123 "vote_counts"."positive_votes" * "majority_den" > 1.124 "majority_num" * 1.125 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 1.126 @@ -3652,12 +3671,9 @@ 1.127 "initiative_id_v" "initiative"."id"%TYPE; 1.128 BEGIN 1.129 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 1.130 - SELECT count(1) INTO "dimension_v" FROM "initiative" 1.131 - WHERE "issue_id" = "issue_id_p" AND "agreed"; 1.132 - IF "dimension_v" = 1 THEN 1.133 - UPDATE "initiative" SET "rank" = 1 1.134 - WHERE "issue_id" = "issue_id_p" AND "agreed"; 1.135 - ELSIF "dimension_v" > 1 THEN 1.136 + SELECT count(1) INTO "dimension_v" 1.137 + FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 1.138 + IF "dimension_v" > 1 THEN 1.139 -- Create "vote_matrix" with absolute number of votes in pairwise 1.140 -- comparison: 1.141 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4) 1.142 @@ -3665,7 +3681,9 @@ 1.143 "j" := 2; 1.144 FOR "battle_row" IN 1.145 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 1.146 - ORDER BY "winning_initiative_id", "losing_initiative_id" 1.147 + ORDER BY 1.148 + "winning_initiative_id" NULLS LAST, 1.149 + "losing_initiative_id" NULLS LAST 1.150 LOOP 1.151 "vote_matrix"["i"]["j"] := "battle_row"."count"; 1.152 IF "j" = "dimension_v" THEN 1.153 @@ -3773,14 +3791,15 @@ 1.154 "i" := 1; 1.155 FOR "initiative_id_v" IN 1.156 SELECT "id" FROM "initiative" 1.157 - WHERE "issue_id" = "issue_id_p" AND "agreed" 1.158 + WHERE "issue_id" = "issue_id_p" AND "admitted" 1.159 ORDER BY "id" 1.160 LOOP 1.161 UPDATE "initiative" SET "rank" = "rank_ary"["i"] 1.162 - WHERE "id" = "initiative_id_v"; 1.163 + WHERE "id" = "initiative_id_v" 1.164 + AND "rank_ary"["i"] < "rank_ary"["dimension_v"]; 1.165 "i" := "i" + 1; 1.166 END LOOP; 1.167 - IF "i" != "dimension_v" + 1 THEN 1.168 + IF "i" != "dimension_v" THEN 1.169 RAISE EXCEPTION 'Wrong winner count (should not happen)'; 1.170 END IF; 1.171 -- straighten ranks (start counting with 1, no equal ranks): 1.172 @@ -3797,6 +3816,12 @@ 1.173 WHERE "id" = "initiative_id_v"; 1.174 "rank_v" := "rank_v" + 1; 1.175 END LOOP; 1.176 + -- mark final winner: 1.177 + UPDATE "initiative" SET "winner" = 1.178 + ( "majority" = TRUE AND 1.179 + "rank" = ( 1.180 + SELECT min("rank") FROM "initiative" 1.181 + WHERE "issue_id" = "issue_id_p" ) ); 1.182 END IF; 1.183 -- mark issue as finished 1.184 UPDATE "issue" SET