liquid_feedback_core
view update/core-update.v3.0.0-v3.0.1.sql @ 418:1088d83d92e8
Bugfix for battle data reconstruction in update script
| author | jbe |
|---|---|
| date | Wed Mar 26 20:08:50 2014 +0100 (2014-03-26) |
| parents | bc94ea65757b |
| children | 044a2b65c707 |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('3.0.1', 3, 0, 1))
5 AS "subquery"("string", "major", "minor", "revision");
7 ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4;
9 ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
10 ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
11 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
12 ( "first_preference_votes" ISNULL AND
13 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
14 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
15 "schulze_rank" ISNULL AND
16 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
17 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
18 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
20 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
21 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
22 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
24 -- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL; -- should not be necessary
25 ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL;
27 ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN;
29 ALTER TABLE "vote" ADD
30 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
31 CHECK ("grade" > 0 OR "first_preference" ISNULL);
33 COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.';
35 INSERT INTO "temporary_transaction_data" ("key", "value")
36 VALUES ('override_protection_triggers', TRUE::TEXT);
38 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
39 FROM (
40 SELECT
41 "vote"."initiative_id",
42 "vote"."member_id",
43 CASE WHEN "vote"."grade" > 0 THEN
44 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
45 ELSE NULL
46 END AS "first_preference"
47 FROM "vote"
48 JOIN "initiative" -- NOTE: due to missing index on issue_id
49 ON "vote"."issue_id" = "initiative"."issue_id"
50 JOIN "vote" AS "agg"
51 ON "initiative"."id" = "agg"."initiative_id"
52 AND "vote"."member_id" = "agg"."member_id"
53 GROUP BY "vote"."initiative_id", "vote"."member_id"
54 ) AS "subquery"
55 WHERE "vote"."initiative_id" = "subquery"."initiative_id"
56 AND "vote"."member_id" = "subquery"."member_id";
58 DELETE FROM "temporary_transaction_data"
59 WHERE "key" = 'override_protection_triggers';
61 UPDATE "initiative"
62 SET "first_preference_votes" = coalesce("subquery"."sum", 0)
63 FROM (
64 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
65 FROM "vote" JOIN "direct_voter"
66 ON "vote"."issue_id" = "direct_voter"."issue_id"
67 AND "vote"."member_id" = "direct_voter"."member_id"
68 WHERE "vote"."first_preference"
69 GROUP BY "vote"."initiative_id"
70 ) AS "subquery"
71 WHERE "initiative"."admitted"
72 AND "initiative"."id" = "subquery"."initiative_id";
74 -- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0)
75 -- to avoid future data loss when executing "clean_issue" to delete voting data:
76 INSERT INTO "battle" (
77 "issue_id",
78 "winning_initiative_id",
79 "losing_initiative_id",
80 "count"
81 ) SELECT
82 "battle_view"."issue_id",
83 "battle_view"."winning_initiative_id",
84 "battle_view"."losing_initiative_id",
85 "battle_view"."count"
86 FROM (
87 SELECT
88 "issue"."id" AS "issue_id",
89 "winning_initiative"."id" AS "winning_initiative_id",
90 "losing_initiative"."id" AS "losing_initiative_id",
91 sum(
92 CASE WHEN
93 coalesce("better_vote"."grade", 0) >
94 coalesce("worse_vote"."grade", 0)
95 THEN "direct_voter"."weight" ELSE 0 END
96 ) AS "count"
97 FROM "issue"
98 LEFT JOIN "direct_voter"
99 ON "issue"."id" = "direct_voter"."issue_id"
100 JOIN "battle_participant" AS "winning_initiative"
101 ON "issue"."id" = "winning_initiative"."issue_id"
102 JOIN "battle_participant" AS "losing_initiative"
103 ON "issue"."id" = "losing_initiative"."issue_id"
104 LEFT JOIN "vote" AS "better_vote"
105 ON "direct_voter"."member_id" = "better_vote"."member_id"
106 AND "winning_initiative"."id" = "better_vote"."initiative_id"
107 LEFT JOIN "vote" AS "worse_vote"
108 ON "direct_voter"."member_id" = "worse_vote"."member_id"
109 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
110 WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner')
111 AND "winning_initiative"."id" != "losing_initiative"."id"
112 -- NOTE: comparisons with status-quo are intentionally omitted to mark
113 -- issues that were counted prior LiquidFeedback Core v2.0.0
114 GROUP BY
115 "issue"."id",
116 "winning_initiative"."id",
117 "losing_initiative"."id"
118 ) AS "battle_view"
119 LEFT JOIN "battle"
120 ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id"
121 AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id"
122 WHERE "battle" ISNULL;
124 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
125 RETURNS VOID
126 LANGUAGE 'plpgsql' VOLATILE AS $$
127 DECLARE
128 "area_id_v" "area"."id"%TYPE;
129 "unit_id_v" "unit"."id"%TYPE;
130 "member_id_v" "member"."id"%TYPE;
131 BEGIN
132 PERFORM "require_transaction_isolation"();
133 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
134 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
135 -- override protection triggers:
136 INSERT INTO "temporary_transaction_data" ("key", "value")
137 VALUES ('override_protection_triggers', TRUE::TEXT);
138 -- delete timestamp of voting comment:
139 UPDATE "direct_voter" SET "comment_changed" = NULL
140 WHERE "issue_id" = "issue_id_p";
141 -- delete delegating votes (in cases of manual reset of issue state):
142 DELETE FROM "delegating_voter"
143 WHERE "issue_id" = "issue_id_p";
144 -- delete votes from non-privileged voters:
145 DELETE FROM "direct_voter"
146 USING (
147 SELECT
148 "direct_voter"."member_id"
149 FROM "direct_voter"
150 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
151 LEFT JOIN "privilege"
152 ON "privilege"."unit_id" = "unit_id_v"
153 AND "privilege"."member_id" = "direct_voter"."member_id"
154 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
155 "member"."active" = FALSE OR
156 "privilege"."voting_right" ISNULL OR
157 "privilege"."voting_right" = FALSE
158 )
159 ) AS "subquery"
160 WHERE "direct_voter"."issue_id" = "issue_id_p"
161 AND "direct_voter"."member_id" = "subquery"."member_id";
162 -- consider delegations:
163 UPDATE "direct_voter" SET "weight" = 1
164 WHERE "issue_id" = "issue_id_p";
165 PERFORM "add_vote_delegations"("issue_id_p");
166 -- mark first preferences:
167 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
168 FROM (
169 SELECT
170 "vote"."initiative_id",
171 "vote"."member_id",
172 CASE WHEN "vote"."grade" > 0 THEN
173 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
174 ELSE NULL
175 END AS "first_preference"
176 FROM "vote"
177 JOIN "initiative" -- NOTE: due to missing index on issue_id
178 ON "vote"."issue_id" = "initiative"."issue_id"
179 JOIN "vote" AS "agg"
180 ON "initiative"."id" = "agg"."initiative_id"
181 AND "vote"."member_id" = "agg"."member_id"
182 GROUP BY "vote"."initiative_id", "vote"."member_id"
183 ) AS "subquery"
184 WHERE "vote"."issue_id" = "issue_id_p"
185 AND "vote"."initiative_id" = "subquery"."initiative_id"
186 AND "vote"."member_id" = "subquery"."member_id";
187 -- finish overriding protection triggers (avoids garbage):
188 DELETE FROM "temporary_transaction_data"
189 WHERE "key" = 'override_protection_triggers';
190 -- materialize battle_view:
191 -- NOTE: "closed" column of issue must be set at this point
192 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
193 INSERT INTO "battle" (
194 "issue_id",
195 "winning_initiative_id", "losing_initiative_id",
196 "count"
197 ) SELECT
198 "issue_id",
199 "winning_initiative_id", "losing_initiative_id",
200 "count"
201 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
202 -- set voter count:
203 UPDATE "issue" SET
204 "voter_count" = (
205 SELECT coalesce(sum("weight"), 0)
206 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
207 )
208 WHERE "id" = "issue_id_p";
209 -- calculate "first_preference_votes":
210 UPDATE "initiative"
211 SET "first_preference_votes" = coalesce("subquery"."sum", 0)
212 FROM (
213 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
214 FROM "vote" JOIN "direct_voter"
215 ON "vote"."issue_id" = "direct_voter"."issue_id"
216 AND "vote"."member_id" = "direct_voter"."member_id"
217 WHERE "vote"."first_preference"
218 GROUP BY "vote"."initiative_id"
219 ) AS "subquery"
220 WHERE "initiative"."issue_id" = "issue_id_p"
221 AND "initiative"."admitted"
222 AND "initiative"."id" = "subquery"."initiative_id";
223 -- copy "positive_votes" and "negative_votes" from "battle" table:
224 UPDATE "initiative" SET
225 "positive_votes" = "battle_win"."count",
226 "negative_votes" = "battle_lose"."count"
227 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
228 WHERE
229 "battle_win"."issue_id" = "issue_id_p" AND
230 "battle_win"."winning_initiative_id" = "initiative"."id" AND
231 "battle_win"."losing_initiative_id" ISNULL AND
232 "battle_lose"."issue_id" = "issue_id_p" AND
233 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
234 "battle_lose"."winning_initiative_id" ISNULL;
235 END;
236 $$;
238 COMMIT;