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