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

Impressum / About Us