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

Impressum / About Us