liquid_feedback_core
changeset 137:8766ddbd9b40
Added column "majority_indirect" to table "policy" and changed function "close_voting" to respect indirect (super)majorities when configured in policy
author | jbe |
---|---|
date | Sat May 28 04:19:30 2011 +0200 (2011-05-28) |
parents | d5f30a515864 |
children | 1542ffbc7ddb |
files | core.sql |
line diff
1.1 --- a/core.sql Sat May 28 03:34:20 2011 +0200 1.2 +++ b/core.sql Sat May 28 04:19:30 2011 +0200 1.3 @@ -310,7 +310,8 @@ 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_strict" BOOLEAN NOT NULL DEFAULT TRUE, 1.9 + "majority_indirect" 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 @@ -328,6 +329,7 @@ 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 @@ -3444,6 +3446,7 @@ 1.22 PERFORM "lock_issue"("issue_id_p"); 1.23 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 1.24 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.25 + -- consider delegations and auto-reject: 1.26 DELETE FROM "delegating_voter" 1.27 WHERE "issue_id" = "issue_id_p"; 1.28 DELETE FROM "direct_voter" 1.29 @@ -3521,6 +3524,7 @@ 1.30 FROM "initiative" WHERE "issue_id" = "issue_id_p"; 1.31 END LOOP; 1.32 PERFORM "add_vote_delegations"("issue_id_p"); 1.33 + -- set voter count and mark issue as being calculated: 1.34 UPDATE "issue" SET 1.35 "state" = 'calculation', 1.36 "closed" = now(), 1.37 @@ -3529,6 +3533,8 @@ 1.38 FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 1.39 ) 1.40 WHERE "id" = "issue_id_p"; 1.41 + -- calculate "positive_votes" and "negative_votes" 1.42 + -- and set "attainable" flag without regarding indirect majorities: 1.43 UPDATE "initiative" SET 1.44 "positive_votes" = "vote_counts"."positive_votes", 1.45 "negative_votes" = "vote_counts"."negative_votes", 1.46 @@ -3573,6 +3579,7 @@ 1.47 WHERE "vote_counts"."initiative_id" = "initiative"."id" 1.48 AND "issue"."id" = "initiative"."issue_id" 1.49 AND "policy"."id" = "issue"."policy_id"; 1.50 + -- materialize battle_view: 1.51 -- NOTE: "closed" column of issue must be set at this point 1.52 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.53 INSERT INTO "battle" ( 1.54 @@ -3584,6 +3591,46 @@ 1.55 "winning_initiative_id", "losing_initiative_id", 1.56 "count" 1.57 FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 1.58 + -- take indirect majorities into account, 1.59 + -- if "policy"."majority_indirect" = TRUE: 1.60 + LOOP 1.61 + UPDATE "initiative" SET "attainable" = TRUE 1.62 + FROM ( 1.63 + SELECT "new_initiative"."id" AS "initiative_id" 1.64 + FROM "issue" 1.65 + JOIN "policy" ON "issue"."policy_id" = "policy"."id" 1.66 + JOIN "initiative" "old_initiative" 1.67 + ON "old_initiative"."issue_id" = "issue_id_p" 1.68 + AND "old_initiative"."admitted" 1.69 + AND "old_initiative"."attainable" 1.70 + JOIN "initiative" "new_initiative" 1.71 + ON "new_initiative"."issue_id" = "issue_id_p" 1.72 + AND "new_initiative"."admitted" 1.73 + AND NOT "new_initiative"."attainable" 1.74 + AND "new_initiative"."positive_votes" >= "new_initiative"."negative_votes" 1.75 + JOIN "battle" "battle_win" 1.76 + ON "battle_win"."issue_id" = "issue_id_p" 1.77 + AND "battle_win"."winning_initiative_id" = "new_initiative"."id" 1.78 + AND "battle_win"."losing_initiative_id" = "old_initiative"."id" 1.79 + JOIN "battle" "battle_lose" 1.80 + ON "battle_lose"."issue_id" = "issue_id_p" 1.81 + AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" 1.82 + AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" 1.83 + WHERE "issue"."id" = "issue_id_p" 1.84 + AND "policy"."majority_indirect" 1.85 + AND CASE WHEN "policy"."majority_strict" THEN 1.86 + "battle_win"."count" * "policy"."majority_den" > 1.87 + "policy"."majority_num" * 1.88 + ("battle_win"."count"+"battle_lose"."count") 1.89 + ELSE 1.90 + "battle_win"."count" * "policy"."majority_den" >= 1.91 + "policy"."majority_num" * 1.92 + ("battle_win"."count"+"battle_lose"."count") 1.93 + END 1.94 + ) AS "subquery" 1.95 + WHERE "id" = "subquery"."initiative_id"; 1.96 + EXIT WHEN NOT FOUND; 1.97 + END LOOP; 1.98 END; 1.99 $$; 1.100