liquid_feedback_core

changeset 133:72792038f7f0

Backed out changeset 3d5e38ea2fab due to incomplete commit message
author jbe
date Tue May 24 23:33:29 2011 +0200 (2011-05-24)
parents 3d5e38ea2fab
children bd0cd909189d
files core.sql demo.sql
line diff
     1.1 --- a/core.sql	Tue May 24 23:24:43 2011 +0200
     1.2 +++ b/core.sql	Tue May 24 23:33:29 2011 +0200
     1.3 @@ -7,7 +7,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1))
     1.8 +  SELECT * FROM (VALUES ('1.4.0_rc2', 1, 4, -1))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -543,7 +543,6 @@
    1.13          "eligible"              BOOLEAN,
    1.14          "rank"                  INT4,
    1.15          "winner"                BOOLEAN,
    1.16 -        "promising"             BOOLEAN,
    1.17          "text_search_data"      TSVECTOR,
    1.18          CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
    1.19            CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
    1.20 @@ -555,16 +554,14 @@
    1.21            ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
    1.22            ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
    1.23              "attainable" ISNULL AND "favored" ISNULL AND "unfavored" ISNULL AND
    1.24 -            "eligible" ISNULL AND "rank" ISNULL AND
    1.25 -            "winner" ISNULL AND "promising" ISNULL ) ),
    1.26 +            "eligible" ISNULL AND "rank" ISNULL AND "winner" ISNULL ) ),
    1.27          CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_attainable_must_be_null"
    1.28            CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "attainable" NOTNULL),
    1.29 -        CONSTRAINT "favored_excludes_unfavored" CHECK (NOT ("favored" AND "unfavored")),
    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 -        CONSTRAINT "winner_excludes_promising" CHECK (NOT ("winner" AND "promising")) );
    1.36 +            ("attainable" AND "favored") = "eligible" ) ) );
    1.37  CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
    1.38  CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
    1.39  CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
    1.40 @@ -592,7 +589,6 @@
    1.41  COMMENT ON COLUMN "initiative"."eligible"       IS 'TRUE, if initiative is "attainable" and "favored"';
    1.42  COMMENT ON COLUMN "initiative"."rank"           IS 'Schulze-Ranking after tie-breaking';
    1.43  COMMENT ON COLUMN "initiative"."winner"         IS 'TRUE, if initiative is final winner (best ranked initiative being "eligible")';
    1.44 -COMMENT ON COLUMN "initiative"."promising"      IS 'TRUE, if initiative would win against current winner, when voting is repeated with ballots based on same preferences. Non "attainable" initiatives may never be "winner", but they can be "promising".';
    1.45  
    1.46  
    1.47  CREATE TABLE "battle" (
    1.48 @@ -3675,19 +3671,18 @@
    1.49    RETURNS VOID
    1.50    LANGUAGE 'plpgsql' VOLATILE AS $$
    1.51      DECLARE
    1.52 -      "dimension_v"       INTEGER;
    1.53 -      "vote_matrix"       INT4[][];  -- absolute votes
    1.54 -      "matrix"            INT8[][];  -- defeat strength / best paths
    1.55 -      "i"                 INTEGER;
    1.56 -      "j"                 INTEGER;
    1.57 -      "k"                 INTEGER;
    1.58 -      "battle_row"        "battle"%ROWTYPE;
    1.59 -      "rank_ary"          INT4[];
    1.60 -      "rank_v"            INT4;
    1.61 -      "done_v"            INTEGER;
    1.62 -      "winners_ary"       INTEGER[];
    1.63 -      "initiative_id_v"   "initiative"."id"%TYPE;
    1.64 -      "promising_added_v" BOOLEAN;
    1.65 +      "dimension_v"     INTEGER;
    1.66 +      "vote_matrix"     INT4[][];  -- absolute votes
    1.67 +      "matrix"          INT8[][];  -- defeat strength / best paths
    1.68 +      "i"               INTEGER;
    1.69 +      "j"               INTEGER;
    1.70 +      "k"               INTEGER;
    1.71 +      "battle_row"      "battle"%ROWTYPE;
    1.72 +      "rank_ary"        INT4[];
    1.73 +      "rank_v"          INT4;
    1.74 +      "done_v"          INTEGER;
    1.75 +      "winners_ary"     INTEGER[];
    1.76 +      "initiative_id_v" "initiative"."id"%TYPE;
    1.77      BEGIN
    1.78        PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
    1.79        SELECT count(1) INTO "dimension_v"
    1.80 @@ -3818,79 +3813,39 @@
    1.81              "unfavored" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
    1.82              "eligible"  = "attainable" AND
    1.83                            "rank_ary"["i"] < "rank_ary"["dimension_v"],
    1.84 -            "rank" = "rank_ary"["i"],
    1.85 -            "winner" = FALSE,
    1.86 -            "promising" = FALSE
    1.87 +            "rank" = "rank_ary"["i"]
    1.88              WHERE "id" = "initiative_id_v";
    1.89            "i" := "i" + 1;
    1.90          END LOOP;
    1.91          IF "i" != "dimension_v" THEN
    1.92            RAISE EXCEPTION 'Wrong winner count (should not happen)';
    1.93          END IF;
    1.94 -        -- mark final winner:
    1.95 -        SELECT "id" INTO "initiative_id_v" FROM "initiative"
    1.96 -          WHERE "issue_id" = "issue_id_p" AND "admitted" AND "eligible"
    1.97 -          ORDER BY "rank", "id"
    1.98 -          LIMIT 1;
    1.99 -        UPDATE "initiative" SET "winner" = TRUE WHERE "id" = "initiative_id_v";
   1.100 -        -- determine promising initiatives:
   1.101 -        LOOP
   1.102 -          "promising_added_v" := FALSE;
   1.103 -          FOR "initiative_id_v" IN
   1.104 -            SELECT "new_initiative"."id"
   1.105 -            FROM "issue"
   1.106 -            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
   1.107 -            JOIN "initiative" "old_initiative"
   1.108 -              ON "old_initiative"."issue_id" = "issue_id_p"
   1.109 -              AND "old_initiative"."admitted"
   1.110 -              AND ("old_initiative"."winner" OR "old_initiative"."promising")
   1.111 -            JOIN "initiative" "new_initiative"
   1.112 -              ON "new_initiative"."issue_id" = "issue_id_p"
   1.113 -              AND "new_initiative"."admitted"
   1.114 -              AND NOT ("new_initiative"."winner" OR "new_initiative"."promising")
   1.115 -            JOIN "battle" "battle_win"
   1.116 -              ON "battle_win"."issue_id" = "issue_id_p"
   1.117 -              AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
   1.118 -              AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
   1.119 -            JOIN "battle" "battle_lose"
   1.120 -              ON "battle_lose"."issue_id" = "issue_id_p"
   1.121 -              AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
   1.122 -              AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
   1.123 -            WHERE "issue"."id" = "issue_id_p"
   1.124 -            AND "new_initiative"."rank" < "old_initiative"."rank"
   1.125 -            -- NOTE: non-straightened ranks are used
   1.126 -            AND CASE WHEN "policy"."majority_strict" THEN
   1.127 -              "battle_win"."count" * "policy"."majority_den" >
   1.128 -              "policy"."majority_num" *
   1.129 -              ("battle_win"."count"+"battle_lose"."count")
   1.130 -            ELSE
   1.131 -              "battle_win"."count" * "policy"."majority_den" >=
   1.132 -              "policy"."majority_num" *
   1.133 -              ("battle_win"."count"+"battle_lose"."count")
   1.134 -            END
   1.135 -          LOOP
   1.136 -            UPDATE "initiative" SET "promising" = TRUE
   1.137 -              WHERE "id" = "initiative_id_v";
   1.138 -            "promising_added_v" := TRUE;
   1.139 -          END LOOP;
   1.140 -          EXIT WHEN NOT "promising_added_v";
   1.141 -        END LOOP;
   1.142          -- straighten ranks (start counting with 1, no equal ranks):
   1.143          "rank_v" := 1;
   1.144          FOR "initiative_id_v" IN
   1.145            SELECT "id" FROM "initiative"
   1.146 -          WHERE "issue_id" = "issue_id_p" AND "admitted"
   1.147 -          ORDER BY "rank", "id"
   1.148 +          WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
   1.149 +          ORDER BY
   1.150 +            "rank",
   1.151 +            "vote_ratio"("positive_votes", "negative_votes") DESC,
   1.152 +            "id"
   1.153          LOOP
   1.154            UPDATE "initiative" SET "rank" = "rank_v"
   1.155              WHERE "id" = "initiative_id_v";
   1.156            "rank_v" := "rank_v" + 1;
   1.157          END LOOP;
   1.158 +        -- mark final winner:
   1.159 +        UPDATE "initiative" SET "winner" =
   1.160 +          ( "eligible" = TRUE AND
   1.161 +            "rank" = (
   1.162 +              SELECT min("rank") FROM "initiative"
   1.163 +              WHERE "issue_id" = "issue_id_p"
   1.164 +              AND "eligible" = TRUE ) );
   1.165        END IF;
   1.166        -- mark issue as finished
   1.167        UPDATE "issue" SET
   1.168          "state" =
   1.169 -          CASE WHEN "dimension_v" = 0 THEN  -- TODO: Broken! To be fixed!
   1.170 +          CASE WHEN "dimension_v" = 0 THEN
   1.171              'finished_without_winner'::"issue_state"
   1.172            ELSE
   1.173              'finished_with_winner'::"issue_state"
     2.1 --- a/demo.sql	Tue May 24 23:24:43 2011 +0200
     2.2 +++ b/demo.sql	Tue May 24 23:33:29 2011 +0200
     2.3 @@ -186,35 +186,6 @@
     2.4  INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
     2.5    (19, 1, 2, FALSE);
     2.6  
     2.7 -INSERT INTO "issue" ("area_id", "policy_id") VALUES
     2.8 -  (4, 1);  -- id 2
     2.9 -
    2.10 -INSERT INTO "initiative" ("issue_id", "name") VALUES
    2.11 -  (2, 'Initiative A'),  -- id 8
    2.12 -  (2, 'Initiative B');  -- id 9
    2.13 -
    2.14 -INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
    2.15 -  (8, 1, 'Lorem ipsum...'),  -- id 8
    2.16 -  (9, 2, 'Lorem ipsum...');  -- id 9
    2.17 -
    2.18 -INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
    2.19 -  (8, 1),
    2.20 -  (9, 2);
    2.21 -
    2.22 -INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
    2.23 -  (1, 8, 8),
    2.24 -  (1, 9, 9),
    2.25 -  (2, 8, 8),
    2.26 -  (2, 9, 9),
    2.27 -  (3, 8, 8),
    2.28 -  (3, 9, 9),
    2.29 -  (4, 8, 8),
    2.30 -  (4, 9, 9),
    2.31 -  (5, 8, 8),
    2.32 -  (5, 9, 9),
    2.33 -  (6, 8, 8),
    2.34 -  (6, 9, 9);
    2.35 -
    2.36  SELECT "time_warp"();
    2.37  SELECT "time_warp"();
    2.38  SELECT "time_warp"();
    2.39 @@ -276,70 +247,6 @@
    2.40    (20, 1, 5,  1),
    2.41    (21, 1, 5, -1);
    2.42  
    2.43 -INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
    2.44 -  ( 1, 2),
    2.45 -  ( 2, 2),
    2.46 -  ( 3, 2),
    2.47 -  ( 4, 2),
    2.48 -  ( 5, 2),
    2.49 -  ( 6, 2),
    2.50 -  ( 7, 2),
    2.51 -  ( 8, 2),
    2.52 -  ( 9, 2),
    2.53 -  (10, 2),
    2.54 -  (11, 2),
    2.55 -  (12, 2),
    2.56 -  (13, 2),
    2.57 -  (14, 2),
    2.58 -  (15, 2),
    2.59 -  (16, 2),
    2.60 -  (17, 2),
    2.61 -  (18, 2),
    2.62 -  (19, 2),
    2.63 -  (20, 2);
    2.64 -
    2.65 -INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
    2.66 -  ( 1, 2, 8,  2),
    2.67 -  ( 1, 2, 9,  1),
    2.68 -  ( 2, 2, 8,  2),
    2.69 -  ( 2, 2, 9,  1),
    2.70 -  ( 3, 2, 8,  2),
    2.71 -  ( 3, 2, 9,  1),
    2.72 -  ( 4, 2, 8,  2),
    2.73 -  ( 4, 2, 9,  1),
    2.74 -  ( 5, 2, 8,  2),
    2.75 -  ( 5, 2, 9,  1),
    2.76 -  ( 6, 2, 8,  2),
    2.77 -  ( 6, 2, 9,  1),
    2.78 -  ( 7, 2, 8,  2),
    2.79 -  ( 7, 2, 9,  1),
    2.80 -  ( 8, 2, 8,  2),
    2.81 -  ( 8, 2, 9,  1),
    2.82 -  ( 9, 2, 8,  2),
    2.83 -  ( 9, 2, 9,  1),
    2.84 -  (10, 2, 8, -1),
    2.85 -  (10, 2, 9,  1),
    2.86 -  (11, 2, 8, -1),
    2.87 -  (11, 2, 9,  1),
    2.88 -  (12, 2, 8, -1),
    2.89 -  (12, 2, 9,  1),
    2.90 -  (13, 2, 8, -1),
    2.91 -  (13, 2, 9,  1),
    2.92 -  (14, 2, 8, -1),
    2.93 -  (14, 2, 9,  1),
    2.94 -  (15, 2, 8, -1),
    2.95 -  (15, 2, 9,  1),
    2.96 -  (16, 2, 8, -1),
    2.97 -  (16, 2, 9,  1),
    2.98 -  (17, 2, 8, -1),
    2.99 -  (17, 2, 9, -2),
   2.100 -  (18, 2, 8, -1),
   2.101 -  (18, 2, 9, -2),
   2.102 -  (19, 2, 8, -1),
   2.103 -  (19, 2, 9, -2),
   2.104 -  (20, 2, 8, -1),
   2.105 -  (20, 2, 9, -2);
   2.106 -
   2.107  SELECT "time_warp"();
   2.108  
   2.109  DROP FUNCTION "time_warp"();

Impressum / About Us