liquid_feedback_core
view update/core-update.v1.4.0_rc2-v1.4.0_rc3.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_rc3', 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" WHERE "issue_id" = "issue_id_p";
101 END LOOP;
102 PERFORM "add_vote_delegations"("issue_id_p");
103 UPDATE "issue" SET
104 "state" = 'calculation',
105 "closed" = now(),
106 "voter_count" = (
107 SELECT coalesce(sum("weight"), 0)
108 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
109 )
110 WHERE "id" = "issue_id_p";
111 UPDATE "initiative" SET
112 "positive_votes" = "vote_counts"."positive_votes",
113 "negative_votes" = "vote_counts"."negative_votes",
114 "agreed" = CASE WHEN "majority_strict" THEN
115 "vote_counts"."positive_votes" * "majority_den" >
116 "majority_num" *
117 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
118 ELSE
119 "vote_counts"."positive_votes" * "majority_den" >=
120 "majority_num" *
121 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
122 END
123 FROM
124 ( SELECT
125 "initiative"."id" AS "initiative_id",
126 coalesce(
127 sum(
128 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
129 ),
130 0
131 ) AS "positive_votes",
132 coalesce(
133 sum(
134 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
135 ),
136 0
137 ) AS "negative_votes"
138 FROM "initiative"
139 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
140 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
141 LEFT JOIN "direct_voter"
142 ON "direct_voter"."issue_id" = "initiative"."issue_id"
143 LEFT JOIN "vote"
144 ON "vote"."initiative_id" = "initiative"."id"
145 AND "vote"."member_id" = "direct_voter"."member_id"
146 WHERE "initiative"."issue_id" = "issue_id_p"
147 AND "initiative"."admitted" -- NOTE: NULL case is handled too
148 GROUP BY "initiative"."id"
149 ) AS "vote_counts",
150 "issue",
151 "policy"
152 WHERE "vote_counts"."initiative_id" = "initiative"."id"
153 AND "issue"."id" = "initiative"."issue_id"
154 AND "policy"."id" = "issue"."policy_id";
155 -- NOTE: "closed" column of issue must be set at this point
156 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
157 INSERT INTO "battle" (
158 "issue_id",
159 "winning_initiative_id", "losing_initiative_id",
160 "count"
161 ) SELECT
162 "issue_id",
163 "winning_initiative_id", "losing_initiative_id",
164 "count"
165 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
166 END;
167 $$;
169 COMMIT;