liquid_feedback_core

changeset 139:e3bfa2d7954c

Always ensure stable voting results by disqualifying certain initiatives, and bugfix related to setting "issue_state" in "calculate_ranks"

- Removed column "majority_indirect" from table "policy"
- Removed column "eligible" from table "initiative" ("eligible" can be calculated as "attainable"=TRUE AND "favored"=TRUE AND "disqualified"=FALSE)
- Split "rank" column of table "initiative" into columns "preliminary_rank" and "final_rank"
- Added column "disqualified" to table "initiative"
- Removed column "promising" from table "initiative"
- Removed constraints from table "initiative"
- Updated functions "close_voting" and "calculate_ranks"
- Added TODO notice in function "clean_issue"
author jbe
date Sun May 29 19:51:16 2011 +0200 (2011-05-29)
parents 1542ffbc7ddb
children c6a47e32b2fb
files core.sql demo.sql
line diff
     1.1 --- a/core.sql	Sat May 28 04:35:24 2011 +0200
     1.2 +++ b/core.sql	Sun May 29 19:51:16 2011 +0200
     1.3 @@ -310,8 +310,7 @@
     1.4          "initiative_quorum_den" INT4            NOT NULL,
     1.5          "majority_num"          INT4            NOT NULL DEFAULT 1,
     1.6          "majority_den"          INT4            NOT NULL DEFAULT 2,
     1.7 -        "majority_strict"       BOOLEAN         NOT NULL DEFAULT TRUE,
     1.8 -        "majority_indirect"     BOOLEAN         NOT NULL DEFAULT TRUE );
     1.9 +        "majority_strict"       BOOLEAN         NOT NULL DEFAULT TRUE );
    1.10  CREATE INDEX "policy_active_idx" ON "policy" ("active");
    1.11  
    1.12  COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
    1.13 @@ -329,7 +328,6 @@
    1.14  COMMENT ON COLUMN "policy"."majority_num"          IS   'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
    1.15  COMMENT ON COLUMN "policy"."majority_den"          IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
    1.16  COMMENT ON COLUMN "policy"."majority_strict"       IS 'If TRUE, then the majority must be strictly greater than "majority_num"/"majority_den", otherwise it may also be equal.';
    1.17 -COMMENT ON COLUMN "policy"."majority_indirect"     IS 'If TRUE, then also indirect majorities (though beat paths) are taken into account, when an initiative has at least as many "positive_votes" as "negative_votes".';
    1.18  
    1.19  
    1.20  CREATE TABLE "unit" (
    1.21 @@ -542,10 +540,10 @@
    1.22          "attainable"            BOOLEAN,
    1.23          "favored"               BOOLEAN,
    1.24          "unfavored"             BOOLEAN,
    1.25 -        "eligible"              BOOLEAN,
    1.26 -        "rank"                  INT4,
    1.27 +        "preliminary_rank"      INT4,
    1.28 +        "final_rank"            INT4,
    1.29 +        "disqualified"          BOOLEAN,
    1.30          "winner"                BOOLEAN,
    1.31 -        "promising"             BOOLEAN,
    1.32          "text_search_data"      TSVECTOR,
    1.33          CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
    1.34            CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
    1.35 @@ -557,16 +555,9 @@
    1.36            ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
    1.37            ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
    1.38              "attainable" ISNULL AND "favored" ISNULL AND "unfavored" ISNULL AND
    1.39 -            "eligible" ISNULL AND "rank" ISNULL AND
    1.40 -            "winner" ISNULL AND "promising" ISNULL ) ),
    1.41 -        CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_attainable_must_be_null"
    1.42 -          CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "attainable" NOTNULL),
    1.43 -        CONSTRAINT "favored_excludes_unfavored" CHECK (NOT ("favored" AND "unfavored")),
    1.44 -        CONSTRAINT "attainable_and_favored_result_in_eligible" CHECK (
    1.45 -          ( "favored" ISNULL AND "eligible" ISNULL ) OR
    1.46 -          ( "attainable" NOTNULL AND "favored" NOTNULL AND "eligible" NOTNULL AND
    1.47 -            ("attainable" AND "favored") = "eligible" ) ),
    1.48 -        CONSTRAINT "winner_excludes_promising" CHECK (NOT ("winner" AND "promising")) );
    1.49 +            "disqualified" ISNULL AND "preliminary_rank" ISNULL AND
    1.50 +            "final_rank" ISNULL AND "winner" ISNULL ) ),
    1.51 +        CONSTRAINT "favored_excludes_unfavored" CHECK (NOT ("favored" AND "unfavored")) );
    1.52  CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
    1.53  CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
    1.54  CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
    1.55 @@ -586,15 +577,15 @@
    1.56  COMMENT ON COLUMN "initiative"."informed_supporter_count"           IS 'Calculated from table "direct_supporter_snapshot"';
    1.57  COMMENT ON COLUMN "initiative"."satisfied_supporter_count"          IS 'Calculated from table "direct_supporter_snapshot"';
    1.58  COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
    1.59 -COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
    1.60 -COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
    1.61 -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.62 -COMMENT ON COLUMN "initiative"."favored"        IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
    1.63 -COMMENT ON COLUMN "initiative"."unfavored"      IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
    1.64 -COMMENT ON COLUMN "initiative"."eligible"       IS 'TRUE, if initiative is "attainable" and "favored"';
    1.65 -COMMENT ON COLUMN "initiative"."rank"           IS 'Schulze-Ranking after tie-breaking';
    1.66 -COMMENT ON COLUMN "initiative"."winner"         IS 'TRUE, if initiative is final winner (best ranked initiative being "eligible")';
    1.67 -COMMENT ON COLUMN "initiative"."promising"      IS 'TRUE, if and only if this initiative is not a winner and there is a number n>0, such that repeating the calculation of the winner n times with the previous winner as status quo causes this initiative to win. The calculations use the same preferences and include the previous status quo as an explicit option. Non "attainable" initiatives may never be "winner", but they can be "promising".';
    1.68 +COMMENT ON COLUMN "initiative"."positive_votes"       IS 'Calculated from table "direct_voter"';
    1.69 +COMMENT ON COLUMN "initiative"."negative_votes"       IS 'Calculated from table "direct_voter"';
    1.70 +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.71 +COMMENT ON COLUMN "initiative"."favored"              IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
    1.72 +COMMENT ON COLUMN "initiative"."unfavored"            IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
    1.73 +COMMENT ON COLUMN "initiative"."preliminary_rank"     IS 'Schulze-Ranking without tie-breaking';
    1.74 +COMMENT ON COLUMN "initiative"."final_rank"           IS 'Schulze-Ranking after tie-breaking';
    1.75 +COMMENT ON COLUMN "initiative"."disqualified"         IS 'TRUE, if initiative may not win, because it is directly beaten with a simple majority by a better ranked initiative or by a better ranked status quo (without tie-breaking)';
    1.76 +COMMENT ON COLUMN "initiative"."winner"               IS 'TRUE, if initiative is final winner (best ranked initiative being "attainable", "favored", and not "disqualified")';
    1.77  
    1.78  
    1.79  CREATE TABLE "battle" (
    1.80 @@ -3533,8 +3524,7 @@
    1.81            FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
    1.82          )
    1.83          WHERE "id" = "issue_id_p";
    1.84 -      -- calculate "positive_votes" and "negative_votes"
    1.85 -      -- and set "attainable" flag without regarding indirect majorities:
    1.86 +      -- calculate "positive_votes", "negative_votes" and "attainable":
    1.87        UPDATE "initiative" SET
    1.88          "positive_votes" = "vote_counts"."positive_votes",
    1.89          "negative_votes" = "vote_counts"."negative_votes",
    1.90 @@ -3591,46 +3581,6 @@
    1.91          "winning_initiative_id", "losing_initiative_id",
    1.92          "count"
    1.93          FROM "battle_view" WHERE "issue_id" = "issue_id_p";
    1.94 -      -- take indirect majorities into account,
    1.95 -      -- if "policy"."majority_indirect" = TRUE:
    1.96 -      LOOP
    1.97 -        UPDATE "initiative" SET "attainable" = TRUE
    1.98 -          FROM (
    1.99 -            SELECT "new_initiative"."id" AS "initiative_id"
   1.100 -            FROM "issue"
   1.101 -            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
   1.102 -            JOIN "initiative" "old_initiative"
   1.103 -              ON "old_initiative"."issue_id" = "issue_id_p"
   1.104 -              AND "old_initiative"."admitted"
   1.105 -              AND "old_initiative"."attainable"
   1.106 -            JOIN "initiative" "new_initiative"
   1.107 -              ON "new_initiative"."issue_id" = "issue_id_p"
   1.108 -              AND "new_initiative"."admitted"
   1.109 -              AND NOT "new_initiative"."attainable"
   1.110 -              AND "new_initiative"."positive_votes" >= "new_initiative"."negative_votes"
   1.111 -            JOIN "battle" "battle_win"
   1.112 -              ON "battle_win"."issue_id" = "issue_id_p"
   1.113 -              AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
   1.114 -              AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
   1.115 -            JOIN "battle" "battle_lose"
   1.116 -              ON "battle_lose"."issue_id" = "issue_id_p"
   1.117 -              AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
   1.118 -              AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
   1.119 -            WHERE "issue"."id" = "issue_id_p"
   1.120 -            AND "policy"."majority_indirect"
   1.121 -            AND CASE WHEN "policy"."majority_strict" THEN
   1.122 -              "battle_win"."count" * "policy"."majority_den" >
   1.123 -              "policy"."majority_num" *
   1.124 -              ("battle_win"."count"+"battle_lose"."count")
   1.125 -            ELSE
   1.126 -              "battle_win"."count" * "policy"."majority_den" >=
   1.127 -              "policy"."majority_num" *
   1.128 -              ("battle_win"."count"+"battle_lose"."count")
   1.129 -            END
   1.130 -          ) AS "subquery"
   1.131 -          WHERE "id" = "subquery"."initiative_id";
   1.132 -        EXIT WHEN NOT FOUND;
   1.133 -      END LOOP;
   1.134      END;
   1.135    $$;
   1.136  
   1.137 @@ -3860,86 +3810,105 @@
   1.138            ORDER BY "id"
   1.139          LOOP
   1.140            UPDATE "initiative" SET
   1.141 -            "favored"   = "rank_ary"["i"] < "rank_ary"["dimension_v"],
   1.142 -            "unfavored" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
   1.143 -            "eligible"  = "attainable" AND
   1.144 -                          "rank_ary"["i"] < "rank_ary"["dimension_v"],
   1.145 -            "rank" = "rank_ary"["i"],
   1.146 -            "winner" = FALSE,
   1.147 -            "promising" = FALSE
   1.148 +            "favored"          = "rank_ary"["i"] < "rank_ary"["dimension_v"],
   1.149 +            "unfavored"        = "rank_ary"["i"] > "rank_ary"["dimension_v"],
   1.150 +            "preliminary_rank" = "rank_ary"["i"],
   1.151 +            "disqualified"     = FALSE,
   1.152 +            "winner"           = FALSE
   1.153              WHERE "id" = "initiative_id_v";
   1.154            "i" := "i" + 1;
   1.155          END LOOP;
   1.156          IF "i" != "dimension_v" THEN
   1.157            RAISE EXCEPTION 'Wrong winner count (should not happen)';
   1.158          END IF;
   1.159 -        -- mark final winner:
   1.160 -        SELECT "id" INTO "initiative_id_v" FROM "initiative"
   1.161 -          WHERE "issue_id" = "issue_id_p" AND "admitted" AND "eligible"
   1.162 -          ORDER BY "rank", "id"
   1.163 -          LIMIT 1;
   1.164 -        UPDATE "initiative" SET "winner" = TRUE WHERE "id" = "initiative_id_v";
   1.165 -        -- determine promising initiatives:
   1.166 -        LOOP
   1.167 -          -- NOTE: non-straightened ranks are used
   1.168 -          UPDATE "initiative" SET "promising" = TRUE
   1.169 -            FROM (
   1.170 -              SELECT "new_initiative"."id" AS "initiative_id"
   1.171 -              FROM "issue"
   1.172 -              JOIN "policy" ON "issue"."policy_id" = "policy"."id"
   1.173 -              JOIN "initiative" "old_initiative"
   1.174 -                ON "old_initiative"."issue_id" = "issue_id_p"
   1.175 -                AND "old_initiative"."admitted"
   1.176 -                AND ("old_initiative"."winner" OR "old_initiative"."promising")
   1.177 -              JOIN "initiative" "new_initiative"
   1.178 -                ON "new_initiative"."issue_id" = "issue_id_p"
   1.179 -                AND "new_initiative"."admitted"
   1.180 -                AND "new_initiative"."favored"
   1.181 -                AND NOT ("new_initiative"."winner" OR "new_initiative"."promising")
   1.182 -              JOIN "battle" "battle_win"
   1.183 -                ON "battle_win"."issue_id" = "issue_id_p"
   1.184 -                AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
   1.185 -                AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
   1.186 -              JOIN "battle" "battle_lose"
   1.187 -                ON "battle_lose"."issue_id" = "issue_id_p"
   1.188 -                AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
   1.189 -                AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
   1.190 -              WHERE "issue"."id" = "issue_id_p"
   1.191 -              AND "new_initiative"."rank" < "old_initiative"."rank"
   1.192 -              AND CASE WHEN "policy"."majority_strict" THEN
   1.193 -                "battle_win"."count" * "policy"."majority_den" >
   1.194 -                "policy"."majority_num" *
   1.195 -                ("battle_win"."count"+"battle_lose"."count")
   1.196 -              ELSE
   1.197 -                "battle_win"."count" * "policy"."majority_den" >=
   1.198 -                "policy"."majority_num" *
   1.199 -                ("battle_win"."count"+"battle_lose"."count")
   1.200 -              END
   1.201 -              ORDER BY "new_initiative"."rank"
   1.202 -              LIMIT 1
   1.203 -            ) AS "subquery"
   1.204 -            WHERE "id" = "subquery"."initiative_id";
   1.205 -          EXIT WHEN NOT FOUND;
   1.206 -        END LOOP;
   1.207 -        -- straighten ranks (start counting with 1, no equal ranks):
   1.208 +        -- remove possible gap in preliminary ranks:
   1.209 +        IF NOT EXISTS (
   1.210 +          SELECT NULL FROM "initiative"
   1.211 +          WHERE "issue_id" = "issue_id_p"
   1.212 +          AND "favored"=FALSE AND "unfavored"=FALSE
   1.213 +        ) THEN
   1.214 +          UPDATE "initiative" SET "preliminary_rank" = "preliminary_rank" - 1
   1.215 +            WHERE "issue_id" = "issue_id_p" AND "unfavored";
   1.216 +        END IF;
   1.217 +        -- disqualify certain initiatives to enforce a stable result:
   1.218 +         UPDATE "initiative" SET "disqualified" = TRUE
   1.219 +          FROM (
   1.220 +            SELECT "losing_initiative"."id" AS "initiative_id"
   1.221 +            FROM "issue"
   1.222 +            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
   1.223 +            JOIN "initiative" "losing_initiative"
   1.224 +              ON "losing_initiative"."issue_id" = "issue_id_p"
   1.225 +              AND "losing_initiative"."admitted"
   1.226 +            JOIN "battle_participant" "winning_participant"
   1.227 +              ON "winning_participant"."issue_id" = "issue_id_p"
   1.228 +            LEFT JOIN "initiative" "winning_initiative"
   1.229 +              ON "winning_initiative"."id" = "winning_participant"."id"
   1.230 +            -- NOTE: winner may be status quo:
   1.231 +            -- "losing_initiative"."id" is always NOTNULL
   1.232 +            -- while "winning_initiative"."id" may be NULL
   1.233 +            JOIN "battle" "battle_win"
   1.234 +              ON "battle_win"."issue_id" = "issue_id_p"
   1.235 +              AND (
   1.236 +                "battle_win"."winning_initiative_id" = "winning_initiative"."id" OR
   1.237 +                ( "battle_win"."winning_initiative_id" ISNULL AND
   1.238 +                  "winning_initiative"."id" ISNULL ) )
   1.239 +              AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
   1.240 +            JOIN "battle" "battle_lose"
   1.241 +              ON "battle_lose"."issue_id" = "issue_id_p"
   1.242 +              AND (
   1.243 +                "battle_lose"."losing_initiative_id" = "winning_initiative"."id" OR
   1.244 +                ( "battle_lose"."losing_initiative_id" ISNULL AND
   1.245 +                  "winning_initiative"."id" ISNULL ) )
   1.246 +              AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
   1.247 +            WHERE "issue"."id" = "issue_id_p"
   1.248 +            AND (
   1.249 +              ("winning_initiative"."id" ISNULL AND "losing_initiative"."unfavored") OR
   1.250 +              ( "winning_initiative"."preliminary_rank" <
   1.251 +                "losing_initiative"."preliminary_rank" ) )
   1.252 +            AND CASE WHEN "policy"."majority_strict" THEN
   1.253 +              "battle_win"."count" * "policy"."majority_den" >
   1.254 +              "policy"."majority_num" *
   1.255 +              ("battle_win"."count"+"battle_lose"."count")
   1.256 +            ELSE
   1.257 +              "battle_win"."count" * "policy"."majority_den" >=
   1.258 +              "policy"."majority_num" *
   1.259 +              ("battle_win"."count"+"battle_lose"."count")
   1.260 +            END
   1.261 +          ) AS "subquery"
   1.262 +          WHERE "id" = "subquery"."initiative_id";
   1.263 +        -- calculate final ranks (start counting with 1, no equal ranks):
   1.264          "rank_v" := 1;
   1.265          FOR "initiative_id_v" IN
   1.266            SELECT "id" FROM "initiative"
   1.267            WHERE "issue_id" = "issue_id_p" AND "admitted"
   1.268 -          ORDER BY "rank", "id"
   1.269 +          ORDER BY "preliminary_rank", "id"
   1.270          LOOP
   1.271 -          UPDATE "initiative" SET "rank" = "rank_v"
   1.272 +          UPDATE "initiative" SET "final_rank" = "rank_v"
   1.273              WHERE "id" = "initiative_id_v";
   1.274            "rank_v" := "rank_v" + 1;
   1.275          END LOOP;
   1.276 +        -- mark final winner:
   1.277 +        UPDATE "initiative" SET "winner" = TRUE
   1.278 +          FROM (
   1.279 +            SELECT "id" AS "initiative_id"
   1.280 +            FROM "initiative"
   1.281 +            WHERE "issue_id" = "issue_id_p"
   1.282 +            AND "attainable" AND "favored" AND NOT "disqualified"
   1.283 +            ORDER BY "final_rank"
   1.284 +            LIMIT 1
   1.285 +          ) AS "subquery"
   1.286 +         WHERE "id" = "subquery"."initiative_id";
   1.287        END IF;
   1.288 -      -- mark issue as finished
   1.289 +      -- mark issue as finished:
   1.290        UPDATE "issue" SET
   1.291          "state" =
   1.292 -          CASE WHEN "dimension_v" = 0 THEN  -- TODO: Broken! To be fixed!
   1.293 +          CASE WHEN EXISTS (
   1.294 +            SELECT NULL FROM "initiative"
   1.295 +            WHERE "issue_id" = "issue_id_p" AND "winner"
   1.296 +          ) THEN
   1.297 +            'finished_with_winner'::"issue_state"
   1.298 +          ELSE
   1.299              'finished_without_winner'::"issue_state"
   1.300 -          ELSE
   1.301 -            'finished_with_winner'::"issue_state"
   1.302            END,
   1.303          "ranks_available" = TRUE
   1.304          WHERE "id" = "issue_id_p";
   1.305 @@ -4145,6 +4114,7 @@
   1.306          FROM "issue" WHERE "id" = "issue_id_p"
   1.307          FOR UPDATE;
   1.308        IF "issue_row"."cleaned" ISNULL THEN
   1.309 +        -- TODO: might be broken due to new constraints!
   1.310          UPDATE "issue" SET
   1.311            "closed" = NULL,
   1.312            "ranks_available" = FALSE
     2.1 --- a/demo.sql	Sat May 28 04:35:24 2011 +0200
     2.2 +++ b/demo.sql	Sun May 29 19:51:16 2011 +0200
     2.3 @@ -92,7 +92,7 @@
     2.4    (4,  6, FALSE),
     2.5    (4,  9, FALSE),
     2.6    (4, 13, FALSE),
     2.7 -  (4, 22, TRUE);
     2.8 +  (4, 22, FALSE);
     2.9  
    2.10  -- global delegations
    2.11  INSERT INTO "delegation"
    2.12 @@ -190,31 +190,49 @@
    2.13    (4, 1);  -- id 2
    2.14  
    2.15  INSERT INTO "initiative" ("issue_id", "name") VALUES
    2.16 -  (2, 'Initiative A'),  -- id 8
    2.17 -  (2, 'Initiative B');  -- id 9
    2.18 +  (2, 'Initiative A'),  -- id  8
    2.19 +  (2, 'Initiative B'),  -- id  9
    2.20 +  (2, 'Initiative C'),  -- id 10
    2.21 +  (2, 'Initiative D');  -- id 11
    2.22  
    2.23  INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
    2.24 -  (8, 1, 'Lorem ipsum...'),  -- id 8
    2.25 -  (9, 2, 'Lorem ipsum...');  -- id 9
    2.26 +  ( 8, 1, 'Lorem ipsum...'),  -- id  8
    2.27 +  ( 9, 2, 'Lorem ipsum...'),  -- id  9
    2.28 +  (10, 3, 'Lorem ipsum...'),  -- id 10
    2.29 +  (11, 4, 'Lorem ipsum...');  -- id 11
    2.30  
    2.31  INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
    2.32 -  (8, 1),
    2.33 -  (9, 2);
    2.34 +  ( 8, 1),
    2.35 +  ( 9, 2),
    2.36 +  (10, 3),
    2.37 +  (11, 4);
    2.38  
    2.39  INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
    2.40 -  (1, 8, 8),
    2.41 -  (1, 9, 9),
    2.42 -  (2, 8, 8),
    2.43 -  (2, 9, 9),
    2.44 -  (3, 8, 8),
    2.45 -  (3, 9, 9),
    2.46 -  (4, 8, 8),
    2.47 -  (4, 9, 9),
    2.48 -  (5, 8, 8),
    2.49 -  (5, 9, 9),
    2.50 -  (6, 8, 8),
    2.51 -  (6, 9, 9);
    2.52 -
    2.53 +  (1,  8,  8),
    2.54 +  (1,  9,  9),
    2.55 +  (1, 10, 10),
    2.56 +  (1, 11, 11),
    2.57 +  (2,  8,  8),
    2.58 +  (2,  9,  9),
    2.59 +  (2, 10, 10),
    2.60 +  (2, 11, 11),
    2.61 +  (3,  8,  8),
    2.62 +  (3,  9,  9),
    2.63 +  (3, 10, 10),
    2.64 +  (3, 11, 11),
    2.65 +  (4,  8,  8),
    2.66 +  (4,  9,  9),
    2.67 +  (4, 10, 10),
    2.68 +  (4, 11, 11),
    2.69 +  (5,  8,  8),
    2.70 +  (5,  9,  9),
    2.71 +  (5, 10, 10),
    2.72 +  (5, 11, 11),
    2.73 +  (6,  8,  8),
    2.74 +  (6,  9,  9),
    2.75 +  (6, 10, 10),
    2.76 +  (6, 11, 11);
    2.77 + 
    2.78  SELECT "time_warp"();
    2.79  SELECT "time_warp"();
    2.80  SELECT "time_warp"();
    2.81 @@ -299,46 +317,86 @@
    2.82    (20, 2);
    2.83  
    2.84  INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
    2.85 -  ( 1, 2, 8,  2),
    2.86 -  ( 1, 2, 9,  1),
    2.87 -  ( 2, 2, 8,  2),
    2.88 -  ( 2, 2, 9,  1),
    2.89 -  ( 3, 2, 8,  2),
    2.90 -  ( 3, 2, 9,  1),
    2.91 -  ( 4, 2, 8,  2),
    2.92 -  ( 4, 2, 9,  1),
    2.93 -  ( 5, 2, 8,  2),
    2.94 -  ( 5, 2, 9,  1),
    2.95 -  ( 6, 2, 8,  2),
    2.96 -  ( 6, 2, 9,  1),
    2.97 -  ( 7, 2, 8,  2),
    2.98 -  ( 7, 2, 9,  1),
    2.99 -  ( 8, 2, 8,  2),
   2.100 -  ( 8, 2, 9,  1),
   2.101 -  ( 9, 2, 8,  2),
   2.102 -  ( 9, 2, 9,  1),
   2.103 -  (10, 2, 8, -1),
   2.104 -  (10, 2, 9,  1),
   2.105 -  (11, 2, 8, -1),
   2.106 -  (11, 2, 9,  1),
   2.107 -  (12, 2, 8, -1),
   2.108 -  (12, 2, 9,  1),
   2.109 -  (13, 2, 8, -1),
   2.110 -  (13, 2, 9,  1),
   2.111 -  (14, 2, 8, -1),
   2.112 -  (14, 2, 9,  1),
   2.113 -  (15, 2, 8, -1),
   2.114 -  (15, 2, 9,  1),
   2.115 -  (16, 2, 8, -1),
   2.116 -  (16, 2, 9,  1),
   2.117 -  (17, 2, 8, -1),
   2.118 -  (17, 2, 9, -2),
   2.119 -  (18, 2, 8, -1),
   2.120 -  (18, 2, 9, -2),
   2.121 -  (19, 2, 8, -1),
   2.122 -  (19, 2, 9, -2),
   2.123 -  (20, 2, 8, -1),
   2.124 -  (20, 2, 9, -2);
   2.125 +  ( 1, 2,  8,  3),
   2.126 +  ( 1, 2,  9,  4),
   2.127 +  ( 1, 2, 10,  2),
   2.128 +  ( 1, 2, 11,  1),
   2.129 +  ( 2, 2,  8,  3),
   2.130 +  ( 2, 2,  9,  4),
   2.131 +  ( 2, 2, 10,  2),
   2.132 +  ( 2, 2, 11,  1),
   2.133 +  ( 3, 2,  8,  4),
   2.134 +  ( 3, 2,  9,  3),
   2.135 +  ( 3, 2, 10,  2),
   2.136 +  ( 3, 2, 11,  1),
   2.137 +  ( 4, 2,  8,  4),
   2.138 +  ( 4, 2,  9,  3),
   2.139 +  ( 4, 2, 10,  2),
   2.140 +  ( 4, 2, 11,  1),
   2.141 +  ( 5, 2,  8,  4),
   2.142 +  ( 5, 2,  9,  3),
   2.143 +  ( 5, 2, 10,  2),
   2.144 +  ( 5, 2, 11,  1),
   2.145 +  ( 6, 2,  8,  4),
   2.146 +  ( 6, 2,  9,  3),
   2.147 +  ( 6, 2, 10,  2),
   2.148 +  ( 6, 2, 11,  1),
   2.149 +  ( 7, 2,  8,  4),
   2.150 +  ( 7, 2,  9,  3),
   2.151 +  ( 7, 2, 10,  2),
   2.152 +  ( 7, 2, 11,  1),
   2.153 +  ( 8, 2,  8,  4),
   2.154 +  ( 8, 2,  9,  3),
   2.155 +  ( 8, 2, 10,  2),
   2.156 +  ( 8, 2, 11,  1),
   2.157 +  ( 9, 2,  8, -1),
   2.158 +  ( 9, 2,  9,  1),
   2.159 +  ( 9, 2, 10,  3),
   2.160 +  ( 9, 2, 11,  2),
   2.161 +  (10, 2,  8, -1),
   2.162 +  (10, 2,  9,  1),
   2.163 +  (10, 2, 10,  3),
   2.164 +  (10, 2, 11,  2),
   2.165 +  (11, 2,  8, -1),
   2.166 +  (11, 2,  9,  1),
   2.167 +  (11, 2, 10,  3),
   2.168 +  (11, 2, 11,  2),
   2.169 +  (12, 2,  8, -1),
   2.170 +  (12, 2,  9,  1),
   2.171 +  (12, 2, 10,  3),
   2.172 +  (12, 2, 11,  2),
   2.173 +  (13, 2,  8, -1),
   2.174 +  (13, 2,  9,  1),
   2.175 +  (13, 2, 10,  3),
   2.176 +  (13, 2, 11,  2),
   2.177 +  (14, 2,  8, -1),
   2.178 +  (14, 2,  9,  1),
   2.179 +  (14, 2, 10,  3),
   2.180 +  (14, 2, 11,  2),
   2.181 +  (15, 2,  8, -1),
   2.182 +  (15, 2,  9, -3),
   2.183 +  (15, 2, 10, -4),
   2.184 +  (15, 2, 11, -2),
   2.185 +  (16, 2,  8, -1),
   2.186 +  (16, 2,  9, -3),
   2.187 +  (16, 2, 10, -4),
   2.188 +  (16, 2, 11, -2),
   2.189 +  (17, 2,  8, -1),
   2.190 +  (17, 2,  9, -3),
   2.191 +  (17, 2, 10, -4),
   2.192 +  (17, 2, 11, -2),
   2.193 +  (18, 2,  8, -1),
   2.194 +  (18, 2,  9,  1),
   2.195 +  (18, 2, 10, -2),
   2.196 +  (18, 2, 11,  2),
   2.197 +  (19, 2,  8, -1),
   2.198 +  (19, 2,  9,  1),
   2.199 +  (19, 2, 10, -2),
   2.200 +  (19, 2, 11,  2),
   2.201 +  (20, 2,  8,  1),
   2.202 +  (20, 2,  9,  2),
   2.203 +  (20, 2, 10, -1),
   2.204 +  (20, 2, 11,  3);
   2.205  
   2.206  SELECT "time_warp"();
   2.207  

Impressum / About Us