liquid_feedback_core
changeset 128:5ea2f9c5ba9a
Set "rank" for all "admitted" initiatives after voting is finished; Additional columns in "initiative" table store voting result information
Changes in detail:
- Renamed column "majority" of table "initiative" to "attainable"
- Added columns "favored", "unfavored" and "eligible" to "initiative" table
- Added constraints to "initiative" table, restricting certain value combinations in the the new columns
- Modified "calculate_ranks" function to update the new columns and always write "rank" information
- Marking the final winner is now done utilizing the new "eligible" column
Changes in detail:
- Renamed column "majority" of table "initiative" to "attainable"
- Added columns "favored", "unfavored" and "eligible" to "initiative" table
- Added constraints to "initiative" table, restricting certain value combinations in the the new columns
- Modified "calculate_ranks" function to update the new columns and always write "rank" information
- Marking the final winner is now done utilizing the new "eligible" column
author | jbe |
---|---|
date | Tue May 24 14:58:42 2011 +0200 (2011-05-24) |
parents | bbadd6b2634d |
children | 284113a50c3b |
files | core.sql |
line diff
1.1 --- a/core.sql Tue May 24 03:12:20 2011 +0200 1.2 +++ b/core.sql Tue May 24 14:58:42 2011 +0200 1.3 @@ -537,7 +537,10 @@ 1.4 "satisfied_informed_supporter_count" INT4, 1.5 "positive_votes" INT4, 1.6 "negative_votes" INT4, 1.7 - "majority" BOOLEAN, 1.8 + "attainable" BOOLEAN, 1.9 + "favored" BOOLEAN, 1.10 + "unfavored" BOOLEAN, 1.11 + "eligible" BOOLEAN, 1.12 "rank" INT4, 1.13 "winner" BOOLEAN, 1.14 "text_search_data" TSVECTOR, 1.15 @@ -547,10 +550,18 @@ 1.16 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL), 1.17 CONSTRAINT "revoked_initiatives_cant_be_admitted" 1.18 CHECK ("revoked" ISNULL OR "admitted" ISNULL), 1.19 - CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" 1.20 - CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "majority" ISNULL AND "rank" ISNULL AND "winner" ISNULL)), 1.21 - CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_majority_must_be_null" 1.22 - CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "majority" NOTNULL) ); 1.23 + CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( 1.24 + ( "admitted" NOTNULL AND "admitted" = TRUE ) OR 1.25 + ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND 1.26 + "attainable" ISNULL AND "favored" ISNULL AND "unfavored" ISNULL AND 1.27 + "eligible" ISNULL AND "rank" ISNULL AND "winner" ISNULL ) ), 1.28 + CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_attainable_must_be_null" 1.29 + CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "attainable" NOTNULL), 1.30 + CONSTRAINT "favored_forbids_unfavored" CHECK (NOT ("favored" AND "unfavored")), 1.31 + CONSTRAINT "attainable_and_favored_result_in_eligible" CHECK ( 1.32 + ( "favored" ISNULL AND "eligible" ISNULL ) OR 1.33 + ( "attainable" NOTNULL AND "favored" NOTNULL AND "eligible" NOTNULL AND 1.34 + ("attainable" AND "favored") = "eligible" ) ) ); 1.35 CREATE INDEX "initiative_created_idx" ON "initiative" ("created"); 1.36 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked"); 1.37 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data"); 1.38 @@ -572,9 +583,12 @@ 1.39 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.40 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; 1.41 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 1.42 -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.43 -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.44 -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.45 +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"'; 1.46 +COMMENT ON COLUMN "initiative"."favored" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)'; 1.47 +COMMENT ON COLUMN "initiative"."unfavored" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)'; 1.48 +COMMENT ON COLUMN "initiative"."eligible" IS 'TRUE, if initiative is "attainable" and "favored"'; 1.49 +COMMENT ON COLUMN "initiative"."rank" IS 'Schulze-Ranking after tie-breaking'; 1.50 +COMMENT ON COLUMN "initiative"."winner" IS 'TRUE, if initiative is final winner (best ranked initiative being "eligible")'; 1.51 1.52 1.53 CREATE TABLE "battle" ( 1.54 @@ -3514,7 +3528,7 @@ 1.55 UPDATE "initiative" SET 1.56 "positive_votes" = "vote_counts"."positive_votes", 1.57 "negative_votes" = "vote_counts"."negative_votes", 1.58 - "majority" = CASE WHEN "majority_strict" THEN 1.59 + "attainable" = CASE WHEN "majority_strict" THEN 1.60 "vote_counts"."positive_votes" * "majority_den" > 1.61 "majority_num" * 1.62 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 1.63 @@ -3787,16 +3801,20 @@ 1.64 EXIT WHEN "done_v" = "dimension_v"; 1.65 "rank_v" := "rank_v" + 1; 1.66 END LOOP; 1.67 - -- write preliminary ranks: 1.68 + -- write preliminary results: 1.69 "i" := 1; 1.70 FOR "initiative_id_v" IN 1.71 SELECT "id" FROM "initiative" 1.72 WHERE "issue_id" = "issue_id_p" AND "admitted" 1.73 ORDER BY "id" 1.74 LOOP 1.75 - UPDATE "initiative" SET "rank" = "rank_ary"["i"] 1.76 - WHERE "id" = "initiative_id_v" 1.77 - AND "rank_ary"["i"] < "rank_ary"["dimension_v"]; 1.78 + UPDATE "initiative" SET 1.79 + "favored" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 1.80 + "unfavored" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 1.81 + "eligible" = "attainable" AND 1.82 + "rank_ary"["i"] < "rank_ary"["dimension_v"], 1.83 + "rank" = "rank_ary"["i"] 1.84 + WHERE "id" = "initiative_id_v"; 1.85 "i" := "i" + 1; 1.86 END LOOP; 1.87 IF "i" != "dimension_v" THEN 1.88 @@ -3818,11 +3836,11 @@ 1.89 END LOOP; 1.90 -- mark final winner: 1.91 UPDATE "initiative" SET "winner" = 1.92 - ( "majority" = TRUE AND 1.93 + ( "eligible" = TRUE AND 1.94 "rank" = ( 1.95 SELECT min("rank") FROM "initiative" 1.96 WHERE "issue_id" = "issue_id_p" 1.97 - AND "majority" = TRUE ) ); 1.98 + AND "eligible" = TRUE ) ); 1.99 END IF; 1.100 -- mark issue as finished 1.101 UPDATE "issue" SET