liquid_feedback_core

changeset 147:37a264fb5eef

Merged bugfix in function "close_voting": Create autoreject ballots only for members with "voting_right" in the unit
author jbe
date Thu Jun 02 01:25:08 2011 +0200 (2011-06-02)
parents 8422bca3a218 259dd3e127c0
children ec1fdf2fc8c9
files .hgtags core.sql
line diff
     1.1 --- a/.hgtags	Thu Jun 02 01:23:49 2011 +0200
     1.2 +++ b/.hgtags	Thu Jun 02 01:25:08 2011 +0200
     1.3 @@ -42,4 +42,5 @@
     1.4  fb9688f31740c4a95a9f2c63c114f64f812ddf05 v1.3.0
     1.5  ea4928ccc8eb303fdaf268f57c5572099b89d0ef v1.3.1
     1.6  0144b703b2610a4c93158427d5eff79cca3fca84 v1.4.0_rc1
     1.7 +07cfc7c1ce3f9c7191a028d3fabf27881edcc8c7 v1.4.0_rc2
     1.8  0fa3c4739ca75a0dd55c073903e274ec20fce137 v1.4.0_rc3
     2.1 --- a/core.sql	Thu Jun 02 01:23:49 2011 +0200
     2.2 +++ b/core.sql	Thu Jun 02 01:25:08 2011 +0200
     2.3 @@ -7,7 +7,7 @@
     2.4  BEGIN;
     2.5  
     2.6  CREATE VIEW "liquid_feedback_version" AS
     2.7 -  SELECT * FROM (VALUES ('1.4.0_rc2', 1, 4, -1))
     2.8 +  SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1))
     2.9    AS "subquery"("string", "major", "minor", "revision");
    2.10  
    2.11  
    2.12 @@ -310,7 +310,9 @@
    2.13          "initiative_quorum_den" INT4            NOT NULL,
    2.14          "majority_num"          INT4            NOT NULL DEFAULT 1,
    2.15          "majority_den"          INT4            NOT NULL DEFAULT 2,
    2.16 -        "majority_strict"       BOOLEAN         NOT NULL DEFAULT TRUE );
    2.17 +        "majority_strict"       BOOLEAN         NOT NULL DEFAULT TRUE,
    2.18 +        "majority_positive"     INT4            NOT NULL DEFAULT 0,
    2.19 +        "majority_non_negative" INT4            NOT NULL DEFAULT 0 );
    2.20  CREATE INDEX "policy_active_idx" ON "policy" ("active");
    2.21  
    2.22  COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
    2.23 @@ -325,9 +327,11 @@
    2.24  COMMENT ON COLUMN "policy"."issue_quorum_den"      IS 'Denominator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"';
    2.25  COMMENT ON COLUMN "policy"."initiative_quorum_num" IS   'Numerator of satisfied supporter quorum  to be reached by an initiative to be "admitted" for voting';
    2.26  COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
    2.27 -COMMENT ON COLUMN "policy"."majority_num"          IS   'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
    2.28 -COMMENT ON COLUMN "policy"."majority_den"          IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
    2.29 +COMMENT ON COLUMN "policy"."majority_num"          IS   'Numerator of fraction of majority to be reached during voting by an initiative to be "attainable"';
    2.30 +COMMENT ON COLUMN "policy"."majority_den"          IS 'Denominator of fraction of majority to be reached during voting by an initiative to be "attainable"';
    2.31  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.';
    2.32 +COMMENT ON COLUMN "policy"."majority_positive"     IS 'Absolute number of "positive_votes" neccessary for an initiative to be "attainable".';
    2.33 +COMMENT ON COLUMN "policy"."majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be "attainable".';
    2.34  
    2.35  
    2.36  CREATE TABLE "unit" (
    2.37 @@ -441,8 +445,6 @@
    2.38          "snapshot"              TIMESTAMPTZ,
    2.39          "latest_snapshot_event" "snapshot_event",
    2.40          "population"            INT4,
    2.41 -        "vote_now"              INT4,
    2.42 -        "vote_later"            INT4,
    2.43          "voter_count"           INT4,
    2.44          CONSTRAINT "valid_state" CHECK ((
    2.45            ("accepted" ISNULL  AND "half_frozen" ISNULL  AND "fully_frozen" ISNULL  AND "closed" ISNULL  AND "ranks_available" = FALSE) OR
    2.46 @@ -502,11 +504,9 @@
    2.47  COMMENT ON COLUMN "issue"."discussion_time"       IS 'Copied from "policy" table at creation of issue';
    2.48  COMMENT ON COLUMN "issue"."verification_time"     IS 'Copied from "policy" table at creation of issue';
    2.49  COMMENT ON COLUMN "issue"."voting_time"           IS 'Copied from "policy" table at creation of issue';
    2.50 -COMMENT ON COLUMN "issue"."snapshot"              IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated';
    2.51 +COMMENT ON COLUMN "issue"."snapshot"              IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
    2.52  COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables';
    2.53  COMMENT ON COLUMN "issue"."population"            IS 'Sum of "weight" column in table "direct_population_snapshot"';
    2.54 -COMMENT ON COLUMN "issue"."vote_now"              IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
    2.55 -COMMENT ON COLUMN "issue"."vote_later"            IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
    2.56  COMMENT ON COLUMN "issue"."voter_count"           IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
    2.57  
    2.58  
    2.59 @@ -537,8 +537,13 @@
    2.60          "satisfied_informed_supporter_count" INT4,
    2.61          "positive_votes"        INT4,
    2.62          "negative_votes"        INT4,
    2.63 -        "agreed"                BOOLEAN,
    2.64 -        "rank"                  INT4,
    2.65 +        "attainable"            BOOLEAN,
    2.66 +        "favored"               BOOLEAN,
    2.67 +        "unfavored"             BOOLEAN,
    2.68 +        "preliminary_rank"      INT4,
    2.69 +        "final_rank"            INT4,
    2.70 +        "disqualified"          BOOLEAN,
    2.71 +        "winner"                BOOLEAN,
    2.72          "text_search_data"      TSVECTOR,
    2.73          CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
    2.74            CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
    2.75 @@ -546,12 +551,13 @@
    2.76            CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
    2.77          CONSTRAINT "revoked_initiatives_cant_be_admitted"
    2.78            CHECK ("revoked" ISNULL OR "admitted" ISNULL),
    2.79 -        CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
    2.80 -          CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
    2.81 -        CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
    2.82 -          CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
    2.83 -        CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
    2.84 -          CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
    2.85 +        CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
    2.86 +          ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
    2.87 +          ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
    2.88 +            "attainable" ISNULL AND "favored" ISNULL AND "unfavored" ISNULL AND
    2.89 +            "disqualified" ISNULL AND "preliminary_rank" ISNULL AND
    2.90 +            "final_rank" ISNULL AND "winner" ISNULL ) ),
    2.91 +        CONSTRAINT "favored_excludes_unfavored" CHECK (NOT ("favored" AND "unfavored")) );
    2.92  CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
    2.93  CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
    2.94  CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
    2.95 @@ -571,22 +577,33 @@
    2.96  COMMENT ON COLUMN "initiative"."informed_supporter_count"           IS 'Calculated from table "direct_supporter_snapshot"';
    2.97  COMMENT ON COLUMN "initiative"."satisfied_supporter_count"          IS 'Calculated from table "direct_supporter_snapshot"';
    2.98  COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
    2.99 -COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
   2.100 -COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
   2.101 -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"';
   2.102 -COMMENT ON COLUMN "initiative"."rank"           IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
   2.103 +COMMENT ON COLUMN "initiative"."positive_votes"       IS 'Calculated from table "direct_voter"';
   2.104 +COMMENT ON COLUMN "initiative"."negative_votes"       IS 'Calculated from table "direct_voter"';
   2.105 +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", and "positive_votes" is greater-equal than "majority_positive", and ("positive_votes"+abstentions) is greater-equal than "majority_non_negative"';
   2.106 +COMMENT ON COLUMN "initiative"."favored"              IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
   2.107 +COMMENT ON COLUMN "initiative"."unfavored"            IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
   2.108 +COMMENT ON COLUMN "initiative"."preliminary_rank"     IS 'Schulze-Ranking without tie-breaking';
   2.109 +COMMENT ON COLUMN "initiative"."final_rank"           IS 'Schulze-Ranking after tie-breaking';
   2.110 +COMMENT ON COLUMN "initiative"."disqualified"         IS 'TRUE, if initiative may not win, because it either (a) has no better rank than the status quo, or (b) because there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X';
   2.111 +COMMENT ON COLUMN "initiative"."winner"               IS 'TRUE, if initiative is final winner (best ranked initiative being "attainable" and not "disqualified")';
   2.112  
   2.113  
   2.114  CREATE TABLE "battle" (
   2.115 -        PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),
   2.116 -        "issue_id"              INT4,
   2.117 +        "issue_id"              INT4            NOT NULL,
   2.118          "winning_initiative_id" INT4,
   2.119          FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.120          "losing_initiative_id"  INT4,
   2.121          FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.122 -        "count"                 INT4            NOT NULL);
   2.123 -
   2.124 -COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';
   2.125 +        "count"                 INT4            NOT NULL,
   2.126 +        CONSTRAINT "initiative_ids_not_equal" CHECK (
   2.127 +          "winning_initiative_id" != "losing_initiative_id" OR
   2.128 +          ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
   2.129 +            ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
   2.130 +CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
   2.131 +CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
   2.132 +CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
   2.133 +
   2.134 +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';
   2.135  
   2.136  
   2.137  CREATE TABLE "ignored_initiative" (
   2.138 @@ -731,14 +748,12 @@
   2.139          PRIMARY KEY ("issue_id", "member_id"),
   2.140          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.141          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.142 -        "autoreject"            BOOLEAN,
   2.143 -        "voting_requested"      BOOLEAN );
   2.144 +        "autoreject"            BOOLEAN );
   2.145  CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
   2.146  
   2.147  COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue; Frontends must ensure that interest for fully_frozen or closed issues is not added or removed.';
   2.148  
   2.149  COMMENT ON COLUMN "interest"."autoreject"       IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
   2.150 -COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
   2.151  
   2.152  
   2.153  CREATE TABLE "initiator" (
   2.154 @@ -854,15 +869,13 @@
   2.155          "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
   2.156          "event"                 "snapshot_event",
   2.157          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
   2.158 -        "weight"                INT4,
   2.159 -        "voting_requested"      BOOLEAN );
   2.160 +        "weight"                INT4 );
   2.161  CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
   2.162  
   2.163  COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
   2.164  
   2.165  COMMENT ON COLUMN "direct_interest_snapshot"."event"            IS 'Reason for snapshot, see "snapshot_event" type for details';
   2.166  COMMENT ON COLUMN "direct_interest_snapshot"."weight"           IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
   2.167 -COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
   2.168  
   2.169  
   2.170  CREATE TABLE "delegating_interest_snapshot" (
   2.171 @@ -1870,6 +1883,18 @@
   2.172  COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
   2.173  
   2.174  
   2.175 +CREATE VIEW "battle_participant" AS
   2.176 +    SELECT "initiative"."id", "initiative"."issue_id"
   2.177 +    FROM "issue" JOIN "initiative"
   2.178 +    ON "issue"."id" = "initiative"."issue_id"
   2.179 +    WHERE "initiative"."admitted"
   2.180 +  UNION ALL
   2.181 +    SELECT NULL, "id" AS "issue_id"
   2.182 +    FROM "issue";
   2.183 +
   2.184 +COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
   2.185 +
   2.186 +
   2.187  CREATE VIEW "battle_view" AS
   2.188    SELECT
   2.189      "issue"."id" AS "issue_id",
   2.190 @@ -1884,12 +1909,10 @@
   2.191    FROM "issue"
   2.192    LEFT JOIN "direct_voter"
   2.193    ON "issue"."id" = "direct_voter"."issue_id"
   2.194 -  JOIN "initiative" AS "winning_initiative"
   2.195 +  JOIN "battle_participant" AS "winning_initiative"
   2.196      ON "issue"."id" = "winning_initiative"."issue_id"
   2.197 -    AND "winning_initiative"."agreed"
   2.198 -  JOIN "initiative" AS "losing_initiative"
   2.199 +  JOIN "battle_participant" AS "losing_initiative"
   2.200      ON "issue"."id" = "losing_initiative"."issue_id"
   2.201 -    AND "losing_initiative"."agreed"
   2.202    LEFT JOIN "vote" AS "better_vote"
   2.203      ON "direct_voter"."member_id" = "better_vote"."member_id"
   2.204      AND "winning_initiative"."id" = "better_vote"."initiative_id"
   2.205 @@ -1898,13 +1921,16 @@
   2.206      AND "losing_initiative"."id" = "worse_vote"."initiative_id"
   2.207    WHERE "issue"."closed" NOTNULL
   2.208    AND "issue"."cleaned" ISNULL
   2.209 -  AND "winning_initiative"."id" != "losing_initiative"."id"
   2.210 +  AND (
   2.211 +    "winning_initiative"."id" != "losing_initiative"."id" OR
   2.212 +    ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
   2.213 +      ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
   2.214    GROUP BY
   2.215      "issue"."id",
   2.216      "winning_initiative"."id",
   2.217      "losing_initiative"."id";
   2.218  
   2.219 -COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
   2.220 +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';
   2.221  
   2.222  
   2.223  CREATE VIEW "expired_session" AS
   2.224 @@ -2918,12 +2944,11 @@
   2.225          WHERE "issue_id" = "issue_id_p"
   2.226          AND "event" = 'periodic';
   2.227        INSERT INTO "direct_interest_snapshot"
   2.228 -        ("issue_id", "event", "member_id", "voting_requested")
   2.229 +        ("issue_id", "event", "member_id")
   2.230          SELECT
   2.231            "issue_id_p"  AS "issue_id",
   2.232            'periodic'    AS "event",
   2.233 -          "member"."id" AS "member_id",
   2.234 -          "interest"."voting_requested"
   2.235 +          "member"."id" AS "member_id"
   2.236          FROM "issue"
   2.237          JOIN "area" ON "issue"."area_id" = "area"."id"
   2.238          JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   2.239 @@ -3001,20 +3026,6 @@
   2.240            FROM "direct_population_snapshot"
   2.241            WHERE "issue_id" = "issue_id_p"
   2.242            AND "event" = 'periodic'
   2.243 -        ),
   2.244 -        "vote_now" = (
   2.245 -          SELECT coalesce(sum("weight"), 0)
   2.246 -          FROM "direct_interest_snapshot"
   2.247 -          WHERE "issue_id" = "issue_id_p"
   2.248 -          AND "event" = 'periodic'
   2.249 -          AND "voting_requested" = TRUE
   2.250 -        ),
   2.251 -        "vote_later" = (
   2.252 -          SELECT coalesce(sum("weight"), 0)
   2.253 -          FROM "direct_interest_snapshot"
   2.254 -          WHERE "issue_id" = "issue_id_p"
   2.255 -          AND "event" = 'periodic'
   2.256 -          AND "voting_requested" = FALSE
   2.257          )
   2.258          WHERE "id" = "issue_id_p";
   2.259        FOR "initiative_id_v" IN
   2.260 @@ -3407,6 +3418,7 @@
   2.261        PERFORM "lock_issue"("issue_id_p");
   2.262        SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   2.263        SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   2.264 +      -- consider delegations and auto-reject:
   2.265        DELETE FROM "delegating_voter"
   2.266          WHERE "issue_id" = "issue_id_p";
   2.267        DELETE FROM "direct_voter"
   2.268 @@ -3492,6 +3504,7 @@
   2.269            FROM "initiative" WHERE "issue_id" = "issue_id_p";
   2.270        END LOOP;
   2.271        PERFORM "add_vote_delegations"("issue_id_p");
   2.272 +      -- set voter count and mark issue as being calculated:
   2.273        UPDATE "issue" SET
   2.274          "state"  = 'calculation',
   2.275          "closed" = now(),
   2.276 @@ -3500,18 +3513,23 @@
   2.277            FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   2.278          )
   2.279          WHERE "id" = "issue_id_p";
   2.280 +      -- calculate "positive_votes", "negative_votes" and "attainable":
   2.281        UPDATE "initiative" SET
   2.282          "positive_votes" = "vote_counts"."positive_votes",
   2.283          "negative_votes" = "vote_counts"."negative_votes",
   2.284 -        "agreed" = CASE WHEN "majority_strict" THEN
   2.285 -          "vote_counts"."positive_votes" * "majority_den" >
   2.286 -          "majority_num" *
   2.287 -          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   2.288 -        ELSE
   2.289 -          "vote_counts"."positive_votes" * "majority_den" >=
   2.290 -          "majority_num" *
   2.291 -          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   2.292 -        END
   2.293 +        "attainable" =
   2.294 +          CASE WHEN "majority_strict" THEN
   2.295 +            "vote_counts"."positive_votes" * "majority_den" >
   2.296 +            "majority_num" *
   2.297 +            ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   2.298 +          ELSE
   2.299 +            "vote_counts"."positive_votes" * "majority_den" >=
   2.300 +            "majority_num" *
   2.301 +            ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   2.302 +          END
   2.303 +          AND "vote_counts"."positive_votes" >= "policy"."majority_positive"
   2.304 +          AND "issue"."voter_count"-"vote_counts"."negative_votes" >=
   2.305 +              "policy"."majority_non_negative"
   2.306          FROM
   2.307            ( SELECT
   2.308                "initiative"."id" AS "initiative_id",
   2.309 @@ -3544,6 +3562,7 @@
   2.310          WHERE "vote_counts"."initiative_id" = "initiative"."id"
   2.311          AND "issue"."id" = "initiative"."issue_id"
   2.312          AND "policy"."id" = "issue"."policy_id";
   2.313 +      -- materialize battle_view:
   2.314        -- NOTE: "closed" column of issue must be set at this point
   2.315        DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   2.316        INSERT INTO "battle" (
   2.317 @@ -3646,26 +3665,23 @@
   2.318    RETURNS VOID
   2.319    LANGUAGE 'plpgsql' VOLATILE AS $$
   2.320      DECLARE
   2.321 -      "dimension_v"     INTEGER;
   2.322 -      "vote_matrix"     INT4[][];  -- absolute votes
   2.323 -      "matrix"          INT8[][];  -- defeat strength / best paths
   2.324 -      "i"               INTEGER;
   2.325 -      "j"               INTEGER;
   2.326 -      "k"               INTEGER;
   2.327 -      "battle_row"      "battle"%ROWTYPE;
   2.328 -      "rank_ary"        INT4[];
   2.329 -      "rank_v"          INT4;
   2.330 -      "done_v"          INTEGER;
   2.331 -      "winners_ary"     INTEGER[];
   2.332 -      "initiative_id_v" "initiative"."id"%TYPE;
   2.333 +      "dimension_v"       INTEGER;
   2.334 +      "vote_matrix"       INT4[][];  -- absolute votes
   2.335 +      "matrix"            INT8[][];  -- defeat strength / best paths
   2.336 +      "i"                 INTEGER;
   2.337 +      "j"                 INTEGER;
   2.338 +      "k"                 INTEGER;
   2.339 +      "battle_row"        "battle"%ROWTYPE;
   2.340 +      "rank_ary"          INT4[];
   2.341 +      "rank_v"            INT4;
   2.342 +      "done_v"            INTEGER;
   2.343 +      "winners_ary"       INTEGER[];
   2.344 +      "initiative_id_v"   "initiative"."id"%TYPE;
   2.345      BEGIN
   2.346        PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
   2.347 -      SELECT count(1) INTO "dimension_v" FROM "initiative"
   2.348 -        WHERE "issue_id" = "issue_id_p" AND "agreed";
   2.349 -      IF "dimension_v" = 1 THEN
   2.350 -        UPDATE "initiative" SET "rank" = 1
   2.351 -          WHERE "issue_id" = "issue_id_p" AND "agreed";
   2.352 -      ELSIF "dimension_v" > 1 THEN
   2.353 +      SELECT count(1) INTO "dimension_v"
   2.354 +        FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
   2.355 +      IF "dimension_v" > 1 THEN
   2.356          -- Create "vote_matrix" with absolute number of votes in pairwise
   2.357          -- comparison:
   2.358          "vote_matrix" := "square_matrix_init_string"("dimension_v");  -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
   2.359 @@ -3673,7 +3689,9 @@
   2.360          "j" := 2;
   2.361          FOR "battle_row" IN
   2.362            SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
   2.363 -          ORDER BY "winning_initiative_id", "losing_initiative_id"
   2.364 +          ORDER BY
   2.365 +          "winning_initiative_id" NULLS LAST,
   2.366 +          "losing_initiative_id" NULLS LAST
   2.367          LOOP
   2.368            "vote_matrix"["i"]["j"] := "battle_row"."count";
   2.369            IF "j" = "dimension_v" THEN
   2.370 @@ -3777,42 +3795,93 @@
   2.371            EXIT WHEN "done_v" = "dimension_v";
   2.372            "rank_v" := "rank_v" + 1;
   2.373          END LOOP;
   2.374 -        -- write preliminary ranks:
   2.375 +        -- write preliminary results:
   2.376          "i" := 1;
   2.377          FOR "initiative_id_v" IN
   2.378            SELECT "id" FROM "initiative"
   2.379 -          WHERE "issue_id" = "issue_id_p" AND "agreed"
   2.380 +          WHERE "issue_id" = "issue_id_p" AND "admitted"
   2.381            ORDER BY "id"
   2.382          LOOP
   2.383 -          UPDATE "initiative" SET "rank" = "rank_ary"["i"]
   2.384 +          UPDATE "initiative" SET
   2.385 +            "favored"          = "rank_ary"["i"] < "rank_ary"["dimension_v"],
   2.386 +            "unfavored"        = "rank_ary"["i"] > "rank_ary"["dimension_v"],
   2.387 +            "preliminary_rank" = "rank_ary"["i"],
   2.388 +            "disqualified"     = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
   2.389 +            "winner"           = FALSE
   2.390              WHERE "id" = "initiative_id_v";
   2.391            "i" := "i" + 1;
   2.392          END LOOP;
   2.393 -        IF "i" != "dimension_v" + 1 THEN
   2.394 +        IF "i" != "dimension_v" THEN
   2.395            RAISE EXCEPTION 'Wrong winner count (should not happen)';
   2.396          END IF;
   2.397 -        -- straighten ranks (start counting with 1, no equal ranks):
   2.398 +        -- remove possible gap in preliminary ranks:
   2.399 +        IF NOT EXISTS (
   2.400 +          SELECT NULL FROM "initiative"
   2.401 +          WHERE "issue_id" = "issue_id_p"
   2.402 +          AND "favored"=FALSE AND "unfavored"=FALSE
   2.403 +        ) THEN
   2.404 +          UPDATE "initiative" SET "preliminary_rank" = "preliminary_rank" - 1
   2.405 +            WHERE "issue_id" = "issue_id_p" AND "unfavored";
   2.406 +        END IF;
   2.407 +        -- disqualify certain initiatives to enforce a stable result:
   2.408 +         UPDATE "initiative" SET "disqualified" = TRUE
   2.409 +          FROM (
   2.410 +            SELECT "losing_initiative"."id" AS "initiative_id"
   2.411 +            FROM "initiative" "losing_initiative"
   2.412 +            JOIN "initiative" "winning_initiative"
   2.413 +              ON "winning_initiative"."issue_id" = "issue_id_p"
   2.414 +              AND "winning_initiative"."admitted"
   2.415 +            JOIN "battle" "battle_win"
   2.416 +              ON "battle_win"."issue_id" = "issue_id_p"
   2.417 +              AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
   2.418 +              AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
   2.419 +            JOIN "battle" "battle_lose"
   2.420 +              ON "battle_lose"."issue_id" = "issue_id_p"
   2.421 +              AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
   2.422 +              AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
   2.423 +            WHERE "losing_initiative"."issue_id" = "issue_id_p"
   2.424 +            AND "losing_initiative"."admitted"
   2.425 +            AND "winning_initiative"."preliminary_rank" <
   2.426 +                "losing_initiative"."preliminary_rank"
   2.427 +            AND "battle_win"."count" > "battle_lose"."count"
   2.428 +            AND (
   2.429 +              "battle_win"."count" > "winning_initiative"."positive_votes" OR
   2.430 +              "battle_lose"."count" < "losing_initiative"."negative_votes" )
   2.431 +          ) AS "subquery"
   2.432 +          WHERE "id" = "subquery"."initiative_id";
   2.433 +        -- calculate final ranks (start counting with 1, no equal ranks):
   2.434          "rank_v" := 1;
   2.435          FOR "initiative_id_v" IN
   2.436            SELECT "id" FROM "initiative"
   2.437 -          WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
   2.438 -          ORDER BY
   2.439 -            "rank",
   2.440 -            "vote_ratio"("positive_votes", "negative_votes") DESC,
   2.441 -            "id"
   2.442 +          WHERE "issue_id" = "issue_id_p" AND "admitted"
   2.443 +          ORDER BY "preliminary_rank", "id"
   2.444          LOOP
   2.445 -          UPDATE "initiative" SET "rank" = "rank_v"
   2.446 +          UPDATE "initiative" SET "final_rank" = "rank_v"
   2.447              WHERE "id" = "initiative_id_v";
   2.448            "rank_v" := "rank_v" + 1;
   2.449          END LOOP;
   2.450 +        -- mark final winner:
   2.451 +        UPDATE "initiative" SET "winner" = TRUE
   2.452 +          FROM (
   2.453 +            SELECT "id" AS "initiative_id"
   2.454 +            FROM "initiative"
   2.455 +            WHERE "issue_id" = "issue_id_p"
   2.456 +            AND "attainable" AND NOT "disqualified"
   2.457 +            ORDER BY "final_rank"
   2.458 +            LIMIT 1
   2.459 +          ) AS "subquery"
   2.460 +         WHERE "id" = "subquery"."initiative_id";
   2.461        END IF;
   2.462 -      -- mark issue as finished
   2.463 +      -- mark issue as finished:
   2.464        UPDATE "issue" SET
   2.465          "state" =
   2.466 -          CASE WHEN "dimension_v" = 0 THEN
   2.467 +          CASE WHEN EXISTS (
   2.468 +            SELECT NULL FROM "initiative"
   2.469 +            WHERE "issue_id" = "issue_id_p" AND "winner"
   2.470 +          ) THEN
   2.471 +            'finished_with_winner'::"issue_state"
   2.472 +          ELSE
   2.473              'finished_without_winner'::"issue_state"
   2.474 -          ELSE
   2.475 -            'finished_with_winner'::"issue_state"
   2.476            END,
   2.477          "ranks_available" = TRUE
   2.478          WHERE "id" = "issue_id_p";
   2.479 @@ -3838,7 +3907,6 @@
   2.480      DECLARE
   2.481        "issue_row"         "issue"%ROWTYPE;
   2.482        "policy_row"        "policy"%ROWTYPE;
   2.483 -      "voting_requested_v" BOOLEAN;
   2.484      BEGIN
   2.485        PERFORM "lock_issue"("issue_id_p");
   2.486        SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   2.487 @@ -3886,21 +3954,8 @@
   2.488            "issue_row"."accepted" NOTNULL AND
   2.489            "issue_row"."half_frozen" ISNULL
   2.490          THEN
   2.491 -          SELECT
   2.492 -            CASE
   2.493 -              WHEN "vote_now" * 2 > "issue_row"."population" THEN
   2.494 -                TRUE
   2.495 -              WHEN "vote_later" * 2 > "issue_row"."population" THEN
   2.496 -                FALSE
   2.497 -              ELSE NULL
   2.498 -            END
   2.499 -            INTO "voting_requested_v"
   2.500 -            FROM "issue" WHERE "id" = "issue_id_p";
   2.501            IF
   2.502 -            "voting_requested_v" OR (
   2.503 -              "voting_requested_v" ISNULL AND
   2.504 -              now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
   2.505 -            )
   2.506 +            now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
   2.507            THEN
   2.508              PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
   2.509              -- NOTE: "issue_row" used later
   2.510 @@ -4018,6 +4073,7 @@
   2.511          FROM "issue" WHERE "id" = "issue_id_p"
   2.512          FOR UPDATE;
   2.513        IF "issue_row"."cleaned" ISNULL THEN
   2.514 +        -- TODO: might be broken due to new constraints!
   2.515          UPDATE "issue" SET
   2.516            "closed" = NULL,
   2.517            "ranks_available" = FALSE
     3.1 --- a/demo.sql	Thu Jun 02 01:23:49 2011 +0200
     3.2 +++ b/demo.sql	Thu Jun 02 01:25:08 2011 +0200
     3.3 @@ -92,7 +92,7 @@
     3.4    (4,  6, FALSE),
     3.5    (4,  9, FALSE),
     3.6    (4, 13, FALSE),
     3.7 -  (4, 22, TRUE);
     3.8 +  (4, 22, FALSE);
     3.9  
    3.10  -- global delegations
    3.11  INSERT INTO "delegation"
    3.12 @@ -186,6 +186,53 @@
    3.13  INSERT INTO "opinion" ("member_id", "suggestion_id", "degree", "fulfilled") VALUES
    3.14    (19, 1, 2, FALSE);
    3.15  
    3.16 +INSERT INTO "issue" ("area_id", "policy_id") VALUES
    3.17 +  (4, 1);  -- id 2
    3.18 +
    3.19 +INSERT INTO "initiative" ("issue_id", "name") VALUES
    3.20 +  (2, 'Initiative A'),  -- id  8
    3.21 +  (2, 'Initiative B'),  -- id  9
    3.22 +  (2, 'Initiative C'),  -- id 10
    3.23 +  (2, 'Initiative D');  -- id 11
    3.24 +
    3.25 +INSERT INTO "draft" ("initiative_id", "author_id", "content") VALUES
    3.26 +  ( 8, 1, 'Lorem ipsum...'),  -- id  8
    3.27 +  ( 9, 2, 'Lorem ipsum...'),  -- id  9
    3.28 +  (10, 3, 'Lorem ipsum...'),  -- id 10
    3.29 +  (11, 4, 'Lorem ipsum...');  -- id 11
    3.30 +
    3.31 +INSERT INTO "initiator" ("initiative_id", "member_id") VALUES
    3.32 +  ( 8, 1),
    3.33 +  ( 9, 2),
    3.34 +  (10, 3),
    3.35 +  (11, 4);
    3.36 +
    3.37 +INSERT INTO "supporter" ("member_id", "initiative_id", "draft_id") VALUES
    3.38 +  (1,  8,  8),
    3.39 +  (1,  9,  9),
    3.40 +  (1, 10, 10),
    3.41 +  (1, 11, 11),
    3.42 +  (2,  8,  8),
    3.43 +  (2,  9,  9),
    3.44 +  (2, 10, 10),
    3.45 +  (2, 11, 11),
    3.46 +  (3,  8,  8),
    3.47 +  (3,  9,  9),
    3.48 +  (3, 10, 10),
    3.49 +  (3, 11, 11),
    3.50 +  (4,  8,  8),
    3.51 +  (4,  9,  9),
    3.52 +  (4, 10, 10),
    3.53 +  (4, 11, 11),
    3.54 +  (5,  8,  8),
    3.55 +  (5,  9,  9),
    3.56 +  (5, 10, 10),
    3.57 +  (5, 11, 11),
    3.58 +  (6,  8,  8),
    3.59 +  (6,  9,  9),
    3.60 +  (6, 10, 10),
    3.61 +  (6, 11, 11);
    3.62 + 
    3.63  SELECT "time_warp"();
    3.64  SELECT "time_warp"();
    3.65  SELECT "time_warp"();
    3.66 @@ -247,6 +294,110 @@
    3.67    (20, 1, 5,  1),
    3.68    (21, 1, 5, -1);
    3.69  
    3.70 +INSERT INTO "direct_voter" ("member_id", "issue_id") VALUES
    3.71 +  ( 1, 2),
    3.72 +  ( 2, 2),
    3.73 +  ( 3, 2),
    3.74 +  ( 4, 2),
    3.75 +  ( 5, 2),
    3.76 +  ( 6, 2),
    3.77 +  ( 7, 2),
    3.78 +  ( 8, 2),
    3.79 +  ( 9, 2),
    3.80 +  (10, 2),
    3.81 +  (11, 2),
    3.82 +  (12, 2),
    3.83 +  (13, 2),
    3.84 +  (14, 2),
    3.85 +  (15, 2),
    3.86 +  (16, 2),
    3.87 +  (17, 2),
    3.88 +  (18, 2),
    3.89 +  (19, 2),
    3.90 +  (20, 2);
    3.91 +
    3.92 +INSERT INTO "vote" ("member_id", "issue_id", "initiative_id", "grade") VALUES
    3.93 +  ( 1, 2,  8,  3),
    3.94 +  ( 1, 2,  9,  4),
    3.95 +  ( 1, 2, 10,  2),
    3.96 +  ( 1, 2, 11,  1),
    3.97 +  ( 2, 2,  8,  3),
    3.98 +  ( 2, 2,  9,  4),
    3.99 +  ( 2, 2, 10,  2),
   3.100 +  ( 2, 2, 11,  1),
   3.101 +  ( 3, 2,  8,  4),
   3.102 +  ( 3, 2,  9,  3),
   3.103 +  ( 3, 2, 10,  2),
   3.104 +  ( 3, 2, 11,  1),
   3.105 +  ( 4, 2,  8,  4),
   3.106 +  ( 4, 2,  9,  3),
   3.107 +  ( 4, 2, 10,  2),
   3.108 +  ( 4, 2, 11,  1),
   3.109 +  ( 5, 2,  8,  4),
   3.110 +  ( 5, 2,  9,  3),
   3.111 +  ( 5, 2, 10,  2),
   3.112 +  ( 5, 2, 11,  1),
   3.113 +  ( 6, 2,  8,  4),
   3.114 +  ( 6, 2,  9,  3),
   3.115 +  ( 6, 2, 10,  2),
   3.116 +  ( 6, 2, 11,  1),
   3.117 +  ( 7, 2,  8,  4),
   3.118 +  ( 7, 2,  9,  3),
   3.119 +  ( 7, 2, 10,  2),
   3.120 +  ( 7, 2, 11,  1),
   3.121 +  ( 8, 2,  8,  4),
   3.122 +  ( 8, 2,  9,  3),
   3.123 +  ( 8, 2, 10,  2),
   3.124 +  ( 8, 2, 11,  1),
   3.125 +  ( 9, 2,  8, -1),
   3.126 +  ( 9, 2,  9,  1),
   3.127 +  ( 9, 2, 10,  3),
   3.128 +  ( 9, 2, 11,  2),
   3.129 +  (10, 2,  8, -1),
   3.130 +  (10, 2,  9,  1),
   3.131 +  (10, 2, 10,  3),
   3.132 +  (10, 2, 11,  2),
   3.133 +  (11, 2,  8, -1),
   3.134 +  (11, 2,  9,  1),
   3.135 +  (11, 2, 10,  3),
   3.136 +  (11, 2, 11,  2),
   3.137 +  (12, 2,  8, -1),
   3.138 +  (12, 2,  9,  1),
   3.139 +  (12, 2, 10,  3),
   3.140 +  (12, 2, 11,  2),
   3.141 +  (13, 2,  8, -1),
   3.142 +  (13, 2,  9,  1),
   3.143 +  (13, 2, 10,  3),
   3.144 +  (13, 2, 11,  2),
   3.145 +  (14, 2,  8, -1),
   3.146 +  (14, 2,  9,  1),
   3.147 +  (14, 2, 10,  3),
   3.148 +  (14, 2, 11,  2),
   3.149 +  (15, 2,  8, -1),
   3.150 +  (15, 2,  9, -3),
   3.151 +  (15, 2, 10, -4),
   3.152 +  (15, 2, 11, -2),
   3.153 +  (16, 2,  8, -1),
   3.154 +  (16, 2,  9, -3),
   3.155 +  (16, 2, 10, -4),
   3.156 +  (16, 2, 11, -2),
   3.157 +  (17, 2,  8, -1),
   3.158 +  (17, 2,  9, -3),
   3.159 +  (17, 2, 10, -4),
   3.160 +  (17, 2, 11, -2),
   3.161 +  (18, 2,  8, -1),
   3.162 +  (18, 2,  9,  1),
   3.163 +  (18, 2, 10, -2),
   3.164 +  (18, 2, 11,  2),
   3.165 +  (19, 2,  8, -1),
   3.166 +  (19, 2,  9,  1),
   3.167 +  (19, 2, 10, -2),
   3.168 +  (19, 2, 11,  2),
   3.169 +  (20, 2,  8,  1),
   3.170 +  (20, 2,  9,  2),
   3.171 +  (20, 2, 10, -1),
   3.172 +  (20, 2, 11,  3);
   3.173 +
   3.174  SELECT "time_warp"();
   3.175  
   3.176  DROP FUNCTION "time_warp"();

Impressum / About Us