liquid_feedback_core
view update/core-update.v1.4.0_rc3-v1.4.0_rc4.sql @ 378:e88d0606891f
Bugfix regarding "proportional_order" of suggestions:
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
author | jbe |
---|---|
date | Mon Mar 18 09:36:21 2013 +0100 (2013-03-18) |
parents | 1be788313b84 |
children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('1.4.0_rc4', 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 JOIN "privilege"
49 ON "privilege"."unit_id" = "unit_id_v"
50 AND "privilege"."member_id" = "member"."id"
51 LEFT JOIN "direct_voter"
52 ON "interest"."member_id" = "direct_voter"."member_id"
53 AND "interest"."issue_id" = "direct_voter"."issue_id"
54 LEFT JOIN "delegating_voter"
55 ON "interest"."member_id" = "delegating_voter"."member_id"
56 AND "interest"."issue_id" = "delegating_voter"."issue_id"
57 WHERE "interest"."issue_id" = "issue_id_p"
58 AND "interest"."autoreject" = TRUE
59 AND "member"."active"
60 AND "privilege"."voting_right"
61 AND "direct_voter"."member_id" ISNULL
62 AND "delegating_voter"."member_id" ISNULL
63 UNION SELECT "membership"."member_id"
64 FROM "membership"
65 JOIN "member"
66 ON "membership"."member_id" = "member"."id"
67 JOIN "privilege"
68 ON "privilege"."unit_id" = "unit_id_v"
69 AND "privilege"."member_id" = "member"."id"
70 LEFT JOIN "interest"
71 ON "membership"."member_id" = "interest"."member_id"
72 AND "interest"."issue_id" = "issue_id_p"
73 LEFT JOIN "direct_voter"
74 ON "membership"."member_id" = "direct_voter"."member_id"
75 AND "direct_voter"."issue_id" = "issue_id_p"
76 LEFT JOIN "delegating_voter"
77 ON "membership"."member_id" = "delegating_voter"."member_id"
78 AND "delegating_voter"."issue_id" = "issue_id_p"
79 WHERE "membership"."area_id" = "area_id_v"
80 AND "membership"."autoreject" = TRUE
81 AND "member"."active"
82 AND "privilege"."voting_right"
83 AND "interest"."autoreject" ISNULL
84 AND "direct_voter"."member_id" ISNULL
85 AND "delegating_voter"."member_id" ISNULL
86 LOOP
87 INSERT INTO "direct_voter"
88 ("member_id", "issue_id", "weight", "autoreject") VALUES
89 ("member_id_v", "issue_id_p", 1, TRUE);
90 INSERT INTO "vote" (
91 "member_id",
92 "issue_id",
93 "initiative_id",
94 "grade"
95 ) SELECT
96 "member_id_v" AS "member_id",
97 "issue_id_p" AS "issue_id",
98 "id" AS "initiative_id",
99 -1 AS "grade"
100 FROM "initiative"
101 WHERE "issue_id" = "issue_id_p" AND "admitted";
102 END LOOP;
103 PERFORM "add_vote_delegations"("issue_id_p");
104 UPDATE "issue" SET
105 "state" = 'calculation',
106 "closed" = now(),
107 "voter_count" = (
108 SELECT coalesce(sum("weight"), 0)
109 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
110 )
111 WHERE "id" = "issue_id_p";
112 UPDATE "initiative" SET
113 "positive_votes" = "vote_counts"."positive_votes",
114 "negative_votes" = "vote_counts"."negative_votes",
115 "agreed" = CASE WHEN "majority_strict" THEN
116 "vote_counts"."positive_votes" * "majority_den" >
117 "majority_num" *
118 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
119 ELSE
120 "vote_counts"."positive_votes" * "majority_den" >=
121 "majority_num" *
122 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
123 END
124 FROM
125 ( SELECT
126 "initiative"."id" AS "initiative_id",
127 coalesce(
128 sum(
129 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
130 ),
131 0
132 ) AS "positive_votes",
133 coalesce(
134 sum(
135 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
136 ),
137 0
138 ) AS "negative_votes"
139 FROM "initiative"
140 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
141 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
142 LEFT JOIN "direct_voter"
143 ON "direct_voter"."issue_id" = "initiative"."issue_id"
144 LEFT JOIN "vote"
145 ON "vote"."initiative_id" = "initiative"."id"
146 AND "vote"."member_id" = "direct_voter"."member_id"
147 WHERE "initiative"."issue_id" = "issue_id_p"
148 AND "initiative"."admitted" -- NOTE: NULL case is handled too
149 GROUP BY "initiative"."id"
150 ) AS "vote_counts",
151 "issue",
152 "policy"
153 WHERE "vote_counts"."initiative_id" = "initiative"."id"
154 AND "issue"."id" = "initiative"."issue_id"
155 AND "policy"."id" = "issue"."policy_id";
156 -- NOTE: "closed" column of issue must be set at this point
157 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
158 INSERT INTO "battle" (
159 "issue_id",
160 "winning_initiative_id", "losing_initiative_id",
161 "count"
162 ) SELECT
163 "issue_id",
164 "winning_initiative_id", "losing_initiative_id",
165 "count"
166 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
167 END;
168 $$;
170 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
171 RETURNS VOID
172 LANGUAGE 'plpgsql' VOLATILE AS $$
173 DECLARE
174 "issue_row" "issue"%ROWTYPE;
175 BEGIN
176 SELECT * INTO "issue_row"
177 FROM "issue" WHERE "id" = "issue_id_p"
178 FOR UPDATE;
179 IF "issue_row"."cleaned" ISNULL THEN
180 UPDATE "issue" SET
181 "state" = 'voting',
182 "closed" = NULL,
183 "ranks_available" = FALSE
184 WHERE "id" = "issue_id_p";
185 DELETE FROM "issue_comment"
186 WHERE "issue_id" = "issue_id_p";
187 DELETE FROM "voting_comment"
188 WHERE "issue_id" = "issue_id_p";
189 DELETE FROM "delegating_voter"
190 WHERE "issue_id" = "issue_id_p";
191 DELETE FROM "direct_voter"
192 WHERE "issue_id" = "issue_id_p";
193 DELETE FROM "delegating_interest_snapshot"
194 WHERE "issue_id" = "issue_id_p";
195 DELETE FROM "direct_interest_snapshot"
196 WHERE "issue_id" = "issue_id_p";
197 DELETE FROM "delegating_population_snapshot"
198 WHERE "issue_id" = "issue_id_p";
199 DELETE FROM "direct_population_snapshot"
200 WHERE "issue_id" = "issue_id_p";
201 DELETE FROM "non_voter"
202 WHERE "issue_id" = "issue_id_p";
203 DELETE FROM "delegation"
204 WHERE "issue_id" = "issue_id_p";
205 DELETE FROM "supporter"
206 WHERE "issue_id" = "issue_id_p";
207 UPDATE "issue" SET
208 "state" = "issue_row"."state",
209 "closed" = "issue_row"."closed",
210 "ranks_available" = "issue_row"."ranks_available",
211 "cleaned" = now()
212 WHERE "id" = "issue_id_p";
213 END IF;
214 RETURN;
215 END;
216 $$;
218 COMMIT;