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