liquid_feedback_core

changeset 414:719ad5c5c940

Calculation of "first_preference" votes
author jbe
date Wed Mar 26 04:44:58 2014 +0100 (2014-03-26)
parents e024c50cfe3d
children ffc4c08cd835
files core.sql
line diff
     1.1 --- a/core.sql	Fri Jan 31 12:46:17 2014 +0100
     1.2 +++ b/core.sql	Wed Mar 26 04:44:58 2014 +0100
     1.3 @@ -646,6 +646,7 @@
     1.4          "satisfied_informed_supporter_count" INT4,
     1.5          "harmonic_weight"       NUMERIC(12, 3),
     1.6          "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
     1.7 +        "first_preference_votes" INT4,
     1.8          "positive_votes"        INT4,
     1.9          "negative_votes"        INT4,
    1.10          "direct_majority"       BOOLEAN,
    1.11 @@ -667,7 +668,8 @@
    1.12            CHECK ("revoked" ISNULL OR "admitted" ISNULL),
    1.13          CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
    1.14            ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
    1.15 -          ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
    1.16 +          ( "first_preference_votes" ISNULL AND
    1.17 +            "positive_votes" ISNULL AND "negative_votes" ISNULL AND
    1.18              "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
    1.19              "schulze_rank" ISNULL AND
    1.20              "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
    1.21 @@ -703,8 +705,9 @@
    1.22  COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
    1.23  COMMENT ON COLUMN "initiative"."harmonic_weight"        IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key';
    1.24  COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
    1.25 -COMMENT ON COLUMN "initiative"."positive_votes"         IS 'Calculated from table "direct_voter"';
    1.26 -COMMENT ON COLUMN "initiative"."negative_votes"         IS 'Calculated from table "direct_voter"';
    1.27 +COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
    1.28 +COMMENT ON COLUMN "initiative"."positive_votes"         IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
    1.29 +COMMENT ON COLUMN "initiative"."negative_votes"         IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
    1.30  COMMENT ON COLUMN "initiative"."direct_majority"        IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"';
    1.31  COMMENT ON COLUMN "initiative"."indirect_majority"      IS 'Same as "direct_majority", but also considering indirect beat paths';
    1.32  COMMENT ON COLUMN "initiative"."schulze_rank"           IS 'Schulze-Ranking';
    1.33 @@ -1120,15 +1123,19 @@
    1.34          PRIMARY KEY ("initiative_id", "member_id"),
    1.35          "initiative_id"         INT4,
    1.36          "member_id"             INT4,
    1.37 -        "grade"                 INT4,
    1.38 +        "grade"                 INT4            NOT NULL,
    1.39 +        "first_preference"      BOOLEAN,
    1.40          FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.41 -        FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.42 +        FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.43 +        CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
    1.44 +          CHECK ("grade" > 0 OR "first_preference" ISNULL) );
    1.45  CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
    1.46  
    1.47  COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions; frontends must ensure that no votes are added modified or removed when the issue has been closed; for corrections refer to column "issue_notice" of "issue" table';
    1.48  
    1.49 -COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
    1.50 -COMMENT ON COLUMN "vote"."grade"    IS 'Values smaller than zero mean reject, values greater than zero mean acceptance, zero or missing row means abstention. Preferences are expressed by different positive or negative numbers.';
    1.51 +COMMENT ON COLUMN "vote"."issue_id"         IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
    1.52 +COMMENT ON COLUMN "vote"."grade"            IS 'Values smaller than zero mean reject, values greater than zero mean acceptance, zero or missing row means abstention. Preferences are expressed by different positive or negative numbers.';
    1.53 +COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.';
    1.54  
    1.55  
    1.56  CREATE TYPE "event_type" AS ENUM (
    1.57 @@ -3736,6 +3743,22 @@
    1.58        UPDATE "direct_voter" SET "weight" = 1
    1.59          WHERE "issue_id" = "issue_id_p";
    1.60        PERFORM "add_vote_delegations"("issue_id_p");
    1.61 +      -- mark first preferences:
    1.62 +      UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
    1.63 +        FROM (
    1.64 +          SELECT
    1.65 +            "vote"."initiative_id",
    1.66 +            "vote"."member_id",
    1.67 +            CASE WHEN "vote"."grade" > 0 THEN
    1.68 +              CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
    1.69 +            ELSE NULL
    1.70 +            END AS "first_preference"
    1.71 +          FROM "vote" JOIN "vote" AS "agg" USING ("issue_id", "member_id")
    1.72 +          GROUP BY "vote"."initiative_id", "vote"."member_id"
    1.73 +        ) AS "subquery"
    1.74 +        WHERE "vote"."issue_id" = "issue_id_p"
    1.75 +        AND "vote"."initiative_id" = "subquery"."initiative_id"
    1.76 +        AND "vote"."member_id" = "subquery"."member_id";
    1.77        -- finish overriding protection triggers (avoids garbage):
    1.78        DELETE FROM "temporary_transaction_data"
    1.79          WHERE "key" = 'override_protection_triggers';
    1.80 @@ -3758,6 +3781,20 @@
    1.81            FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
    1.82          )
    1.83          WHERE "id" = "issue_id_p";
    1.84 +      -- calculate "first_preference_votes":
    1.85 +      UPDATE "initiative"
    1.86 +        SET "first_preference_votes" = coalesce("subquery"."sum", 0)
    1.87 +        FROM (
    1.88 +          SELECT "vote"."initiative_id", sum("direct_voter"."weight")
    1.89 +          FROM "vote" JOIN "direct_voter"
    1.90 +          ON "vote"."issue_id" = "direct_voter"."issue_id"
    1.91 +          AND "vote"."member_id" = "direct_voter"."member_id"
    1.92 +          WHERE "vote"."first_preference"
    1.93 +          GROUP BY "vote"."initiative_id"
    1.94 +        ) AS "subquery"
    1.95 +        WHERE "initiative"."issue_id" = "issue_id_p"
    1.96 +        AND "initiative"."admitted"
    1.97 +        AND "initiative"."id" = "subquery"."initiative_id";
    1.98        -- copy "positive_votes" and "negative_votes" from "battle" table:
    1.99        UPDATE "initiative" SET
   1.100          "positive_votes" = "battle_win"."count",

Impressum / About Us