liquid_feedback_core

annotate update/core-update.v1.2.8-v1.2.9.sql @ 142:54ac8c473263

find changesets by author, revision, files, or words in the commit message
Use an improved definition for "disqualified" initiatives

"initiative"."disqualified" is TRUE, if the 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
author jbe
date Wed Jun 01 16:58:00 2011 +0200 (2011-06-01)
parents e588fdf1676e
children
rev   line source
jbe@87 1 BEGIN;
jbe@87 2
jbe@87 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@87 4 SELECT * FROM (VALUES ('1.2.9', 1, 2, 9))
jbe@87 5 AS "subquery"("string", "major", "minor", "revision");
jbe@87 6
jbe@87 7 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@87 8 RETURNS VOID
jbe@87 9 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@87 10 DECLARE
jbe@87 11 "issue_row" "issue"%ROWTYPE;
jbe@87 12 "member_id_v" "member"."id"%TYPE;
jbe@87 13 BEGIN
jbe@87 14 PERFORM "lock_issue"("issue_id_p");
jbe@87 15 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@87 16 DELETE FROM "delegating_voter"
jbe@87 17 WHERE "issue_id" = "issue_id_p";
jbe@87 18 DELETE FROM "direct_voter"
jbe@87 19 WHERE "issue_id" = "issue_id_p"
jbe@87 20 AND "autoreject" = TRUE;
jbe@87 21 DELETE FROM "direct_voter" USING "member"
jbe@87 22 WHERE "direct_voter"."member_id" = "member"."id"
jbe@87 23 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@87 24 AND "member"."active" = FALSE;
jbe@87 25 UPDATE "direct_voter" SET "weight" = 1
jbe@87 26 WHERE "issue_id" = "issue_id_p";
jbe@87 27 PERFORM "add_vote_delegations"("issue_id_p");
jbe@87 28 FOR "member_id_v" IN
jbe@87 29 SELECT "interest"."member_id"
jbe@87 30 FROM "interest"
jbe@87 31 JOIN "member"
jbe@87 32 ON "interest"."member_id" = "member"."id"
jbe@87 33 LEFT JOIN "direct_voter"
jbe@87 34 ON "interest"."member_id" = "direct_voter"."member_id"
jbe@87 35 AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@87 36 LEFT JOIN "delegating_voter"
jbe@87 37 ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@87 38 AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@87 39 WHERE "interest"."issue_id" = "issue_id_p"
jbe@87 40 AND "interest"."autoreject" = TRUE
jbe@87 41 AND "member"."active"
jbe@87 42 AND "direct_voter"."member_id" ISNULL
jbe@87 43 AND "delegating_voter"."member_id" ISNULL
jbe@87 44 UNION SELECT "membership"."member_id"
jbe@87 45 FROM "membership"
jbe@87 46 JOIN "member"
jbe@87 47 ON "membership"."member_id" = "member"."id"
jbe@87 48 LEFT JOIN "interest"
jbe@87 49 ON "membership"."member_id" = "interest"."member_id"
jbe@87 50 AND "interest"."issue_id" = "issue_id_p"
jbe@87 51 LEFT JOIN "direct_voter"
jbe@87 52 ON "membership"."member_id" = "direct_voter"."member_id"
jbe@87 53 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@87 54 LEFT JOIN "delegating_voter"
jbe@87 55 ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@87 56 AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@87 57 WHERE "membership"."area_id" = "issue_row"."area_id"
jbe@87 58 AND "membership"."autoreject" = TRUE
jbe@87 59 AND "member"."active"
jbe@87 60 AND "interest"."autoreject" ISNULL
jbe@87 61 AND "direct_voter"."member_id" ISNULL
jbe@87 62 AND "delegating_voter"."member_id" ISNULL
jbe@87 63 LOOP
jbe@87 64 INSERT INTO "direct_voter"
jbe@87 65 ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@87 66 ("member_id_v", "issue_id_p", 1, TRUE);
jbe@87 67 INSERT INTO "vote" (
jbe@87 68 "member_id",
jbe@87 69 "issue_id",
jbe@87 70 "initiative_id",
jbe@87 71 "grade"
jbe@87 72 ) SELECT
jbe@87 73 "member_id_v" AS "member_id",
jbe@87 74 "issue_id_p" AS "issue_id",
jbe@87 75 "id" AS "initiative_id",
jbe@87 76 -1 AS "grade"
jbe@87 77 FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@87 78 END LOOP;
jbe@87 79 PERFORM "add_vote_delegations"("issue_id_p");
jbe@87 80 UPDATE "issue" SET
jbe@87 81 "closed" = now(),
jbe@87 82 "voter_count" = (
jbe@87 83 SELECT coalesce(sum("weight"), 0)
jbe@87 84 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@87 85 )
jbe@87 86 WHERE "id" = "issue_id_p";
jbe@87 87 UPDATE "initiative" SET
jbe@87 88 "positive_votes" = "vote_counts"."positive_votes",
jbe@87 89 "negative_votes" = "vote_counts"."negative_votes",
jbe@87 90 "agreed" = CASE WHEN "majority_strict" THEN
jbe@87 91 "vote_counts"."positive_votes" * "majority_den" >
jbe@87 92 "majority_num" *
jbe@87 93 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@87 94 ELSE
jbe@87 95 "vote_counts"."positive_votes" * "majority_den" >=
jbe@87 96 "majority_num" *
jbe@87 97 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@87 98 END
jbe@87 99 FROM
jbe@87 100 ( SELECT
jbe@87 101 "initiative"."id" AS "initiative_id",
jbe@87 102 coalesce(
jbe@87 103 sum(
jbe@87 104 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@87 105 ),
jbe@87 106 0
jbe@87 107 ) AS "positive_votes",
jbe@87 108 coalesce(
jbe@87 109 sum(
jbe@87 110 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@87 111 ),
jbe@87 112 0
jbe@87 113 ) AS "negative_votes"
jbe@87 114 FROM "initiative"
jbe@87 115 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@87 116 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@87 117 LEFT JOIN "direct_voter"
jbe@87 118 ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@87 119 LEFT JOIN "vote"
jbe@87 120 ON "vote"."initiative_id" = "initiative"."id"
jbe@87 121 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@87 122 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@87 123 AND "initiative"."admitted" -- NOTE: NULL case is handled too
jbe@87 124 GROUP BY "initiative"."id"
jbe@87 125 ) AS "vote_counts",
jbe@87 126 "issue",
jbe@87 127 "policy"
jbe@87 128 WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@87 129 AND "issue"."id" = "initiative"."issue_id"
jbe@87 130 AND "policy"."id" = "issue"."policy_id";
jbe@87 131 -- NOTE: "closed" column of issue must be set at this point
jbe@87 132 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@87 133 INSERT INTO "battle" (
jbe@87 134 "issue_id",
jbe@87 135 "winning_initiative_id", "losing_initiative_id",
jbe@87 136 "count"
jbe@87 137 ) SELECT
jbe@87 138 "issue_id",
jbe@87 139 "winning_initiative_id", "losing_initiative_id",
jbe@87 140 "count"
jbe@87 141 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@87 142 END;
jbe@87 143 $$;
jbe@87 144
jbe@87 145 COMMIT;

Impressum / About Us