rev |
line source |
jbe@436
|
1 -- NOTICE: This update script disables the "no_reserve_beat_path" setting for
|
jbe@436
|
2 -- all policies. If this is not intended, please edit this script
|
jbe@436
|
3 -- before applying it to your database.
|
jbe@436
|
4
|
jbe@436
|
5 BEGIN;
|
jbe@436
|
6
|
jbe@436
|
7 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@436
|
8 SELECT * FROM (VALUES ('3.0.3', 3, 0, 3))
|
jbe@436
|
9 AS "subquery"("string", "major", "minor", "revision");
|
jbe@436
|
10
|
jbe@436
|
11 CREATE FUNCTION "update3_0_3_add_columns_if_missing"()
|
jbe@436
|
12 RETURNS VOID
|
jbe@436
|
13 LANGUAGE 'plpgsql' AS $$
|
jbe@436
|
14 BEGIN
|
jbe@436
|
15 BEGIN
|
jbe@436
|
16 ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4;
|
jbe@436
|
17 EXCEPTION
|
jbe@436
|
18 WHEN duplicate_column THEN
|
jbe@436
|
19 RAISE NOTICE 'column "first_preference_votes" of relation "initiative" already exists, skipping';
|
jbe@436
|
20 END;
|
jbe@436
|
21 BEGIN
|
jbe@436
|
22 ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN;
|
jbe@436
|
23 EXCEPTION
|
jbe@436
|
24 WHEN duplicate_column THEN
|
jbe@436
|
25 RAISE NOTICE 'column "first_preference" of relation "vote" already exists, skipping';
|
jbe@436
|
26 END;
|
jbe@436
|
27 RETURN;
|
jbe@436
|
28 END;
|
jbe@436
|
29 $$;
|
jbe@436
|
30
|
jbe@436
|
31 SELECT "update3_0_3_add_columns_if_missing"();
|
jbe@436
|
32
|
jbe@436
|
33 DROP FUNCTION "update3_0_3_add_columns_if_missing"();
|
jbe@436
|
34
|
jbe@436
|
35 ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
|
jbe@436
|
36 ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
|
jbe@436
|
37 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
|
jbe@436
|
38 ( "first_preference_votes" ISNULL AND
|
jbe@436
|
39 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
|
jbe@436
|
40 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
|
jbe@436
|
41 "schulze_rank" ISNULL AND
|
jbe@436
|
42 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
|
jbe@436
|
43 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
|
jbe@436
|
44 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
|
jbe@436
|
45
|
jbe@436
|
46 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
|
jbe@436
|
47 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
|
jbe@436
|
48 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
|
jbe@436
|
49
|
jbe@436
|
50 -- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL; -- should not be necessary
|
jbe@436
|
51 ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL;
|
jbe@436
|
52
|
jbe@436
|
53 ALTER TABLE "vote" DROP CONSTRAINT IF EXISTS "first_preference_flag_only_set_on_positive_grades";
|
jbe@436
|
54 ALTER TABLE "vote" ADD
|
jbe@436
|
55 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
|
jbe@436
|
56 CHECK ("grade" > 0 OR "first_preference" ISNULL);
|
jbe@436
|
57
|
jbe@436
|
58 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.';
|
jbe@436
|
59
|
jbe@436
|
60 INSERT INTO "temporary_transaction_data" ("key", "value")
|
jbe@436
|
61 VALUES ('override_protection_triggers', TRUE::TEXT);
|
jbe@436
|
62
|
jbe@436
|
63 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
|
jbe@436
|
64 FROM (
|
jbe@436
|
65 SELECT
|
jbe@436
|
66 "vote"."initiative_id",
|
jbe@436
|
67 "vote"."member_id",
|
jbe@436
|
68 CASE WHEN "vote"."grade" > 0 THEN
|
jbe@436
|
69 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
|
jbe@436
|
70 ELSE NULL
|
jbe@436
|
71 END AS "first_preference"
|
jbe@436
|
72 FROM "vote"
|
jbe@436
|
73 JOIN "initiative" -- NOTE: due to missing index on issue_id
|
jbe@436
|
74 ON "vote"."issue_id" = "initiative"."issue_id"
|
jbe@436
|
75 JOIN "vote" AS "agg"
|
jbe@436
|
76 ON "initiative"."id" = "agg"."initiative_id"
|
jbe@436
|
77 AND "vote"."member_id" = "agg"."member_id"
|
jbe@436
|
78 GROUP BY "vote"."initiative_id", "vote"."member_id"
|
jbe@436
|
79 ) AS "subquery"
|
jbe@436
|
80 WHERE "vote"."initiative_id" = "subquery"."initiative_id"
|
jbe@436
|
81 AND "vote"."member_id" = "subquery"."member_id";
|
jbe@436
|
82
|
jbe@436
|
83 DELETE FROM "temporary_transaction_data"
|
jbe@436
|
84 WHERE "key" = 'override_protection_triggers';
|
jbe@436
|
85
|
jbe@436
|
86 UPDATE "initiative" SET "first_preference_votes" = NULL
|
jbe@436
|
87 WHERE "first_preference_votes" = 0;
|
jbe@436
|
88
|
jbe@436
|
89 UPDATE "initiative"
|
jbe@436
|
90 SET "first_preference_votes" = "subquery"."sum"
|
jbe@436
|
91 FROM (
|
jbe@436
|
92 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
|
jbe@436
|
93 FROM "vote" JOIN "direct_voter"
|
jbe@436
|
94 ON "vote"."issue_id" = "direct_voter"."issue_id"
|
jbe@436
|
95 AND "vote"."member_id" = "direct_voter"."member_id"
|
jbe@436
|
96 WHERE "vote"."first_preference"
|
jbe@436
|
97 GROUP BY "vote"."initiative_id"
|
jbe@436
|
98 ) AS "subquery"
|
jbe@436
|
99 WHERE "initiative"."admitted"
|
jbe@436
|
100 AND "initiative"."id" = "subquery"."initiative_id"
|
jbe@436
|
101 AND "initiative"."first_preference_votes" ISNULL;
|
jbe@436
|
102
|
jbe@436
|
103 UPDATE "initiative" SET "first_preference_votes" = 0
|
jbe@436
|
104 WHERE "positive_votes" NOTNULL
|
jbe@436
|
105 AND "first_preference_votes" ISNULL;
|
jbe@436
|
106
|
jbe@436
|
107 -- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0)
|
jbe@436
|
108 -- to avoid future data loss when executing "clean_issue" to delete voting data:
|
jbe@436
|
109 INSERT INTO "battle" (
|
jbe@436
|
110 "issue_id",
|
jbe@436
|
111 "winning_initiative_id",
|
jbe@436
|
112 "losing_initiative_id",
|
jbe@436
|
113 "count"
|
jbe@436
|
114 ) SELECT
|
jbe@436
|
115 "battle_view"."issue_id",
|
jbe@436
|
116 "battle_view"."winning_initiative_id",
|
jbe@436
|
117 "battle_view"."losing_initiative_id",
|
jbe@436
|
118 "battle_view"."count"
|
jbe@436
|
119 FROM (
|
jbe@436
|
120 SELECT
|
jbe@436
|
121 "issue"."id" AS "issue_id",
|
jbe@436
|
122 "winning_initiative"."id" AS "winning_initiative_id",
|
jbe@436
|
123 "losing_initiative"."id" AS "losing_initiative_id",
|
jbe@436
|
124 sum(
|
jbe@436
|
125 CASE WHEN
|
jbe@436
|
126 coalesce("better_vote"."grade", 0) >
|
jbe@436
|
127 coalesce("worse_vote"."grade", 0)
|
jbe@436
|
128 THEN "direct_voter"."weight" ELSE 0 END
|
jbe@436
|
129 ) AS "count"
|
jbe@436
|
130 FROM "issue"
|
jbe@436
|
131 LEFT JOIN "direct_voter"
|
jbe@436
|
132 ON "issue"."id" = "direct_voter"."issue_id"
|
jbe@436
|
133 JOIN "battle_participant" AS "winning_initiative"
|
jbe@436
|
134 ON "issue"."id" = "winning_initiative"."issue_id"
|
jbe@436
|
135 JOIN "battle_participant" AS "losing_initiative"
|
jbe@436
|
136 ON "issue"."id" = "losing_initiative"."issue_id"
|
jbe@436
|
137 LEFT JOIN "vote" AS "better_vote"
|
jbe@436
|
138 ON "direct_voter"."member_id" = "better_vote"."member_id"
|
jbe@436
|
139 AND "winning_initiative"."id" = "better_vote"."initiative_id"
|
jbe@436
|
140 LEFT JOIN "vote" AS "worse_vote"
|
jbe@436
|
141 ON "direct_voter"."member_id" = "worse_vote"."member_id"
|
jbe@436
|
142 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
|
jbe@436
|
143 WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner')
|
jbe@436
|
144 AND "winning_initiative"."id" != "losing_initiative"."id"
|
jbe@436
|
145 -- NOTE: comparisons with status-quo are intentionally omitted to mark
|
jbe@436
|
146 -- issues that were counted prior LiquidFeedback Core v2.0.0
|
jbe@436
|
147 GROUP BY
|
jbe@436
|
148 "issue"."id",
|
jbe@436
|
149 "winning_initiative"."id",
|
jbe@436
|
150 "losing_initiative"."id"
|
jbe@436
|
151 ) AS "battle_view"
|
jbe@436
|
152 LEFT JOIN "battle"
|
jbe@436
|
153 ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id"
|
jbe@436
|
154 AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id"
|
jbe@436
|
155 WHERE "battle" ISNULL;
|
jbe@436
|
156
|
jbe@436
|
157 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
|
jbe@436
|
158 RETURNS VOID
|
jbe@436
|
159 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@436
|
160 DECLARE
|
jbe@436
|
161 "area_id_v" "area"."id"%TYPE;
|
jbe@436
|
162 "unit_id_v" "unit"."id"%TYPE;
|
jbe@436
|
163 "member_id_v" "member"."id"%TYPE;
|
jbe@436
|
164 BEGIN
|
jbe@436
|
165 PERFORM "require_transaction_isolation"();
|
jbe@436
|
166 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@436
|
167 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
|
jbe@436
|
168 -- override protection triggers:
|
jbe@436
|
169 INSERT INTO "temporary_transaction_data" ("key", "value")
|
jbe@436
|
170 VALUES ('override_protection_triggers', TRUE::TEXT);
|
jbe@436
|
171 -- delete timestamp of voting comment:
|
jbe@436
|
172 UPDATE "direct_voter" SET "comment_changed" = NULL
|
jbe@436
|
173 WHERE "issue_id" = "issue_id_p";
|
jbe@436
|
174 -- delete delegating votes (in cases of manual reset of issue state):
|
jbe@436
|
175 DELETE FROM "delegating_voter"
|
jbe@436
|
176 WHERE "issue_id" = "issue_id_p";
|
jbe@436
|
177 -- delete votes from non-privileged voters:
|
jbe@436
|
178 DELETE FROM "direct_voter"
|
jbe@436
|
179 USING (
|
jbe@436
|
180 SELECT
|
jbe@436
|
181 "direct_voter"."member_id"
|
jbe@436
|
182 FROM "direct_voter"
|
jbe@436
|
183 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
|
jbe@436
|
184 LEFT JOIN "privilege"
|
jbe@436
|
185 ON "privilege"."unit_id" = "unit_id_v"
|
jbe@436
|
186 AND "privilege"."member_id" = "direct_voter"."member_id"
|
jbe@436
|
187 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
|
jbe@436
|
188 "member"."active" = FALSE OR
|
jbe@436
|
189 "privilege"."voting_right" ISNULL OR
|
jbe@436
|
190 "privilege"."voting_right" = FALSE
|
jbe@436
|
191 )
|
jbe@436
|
192 ) AS "subquery"
|
jbe@436
|
193 WHERE "direct_voter"."issue_id" = "issue_id_p"
|
jbe@436
|
194 AND "direct_voter"."member_id" = "subquery"."member_id";
|
jbe@436
|
195 -- consider delegations:
|
jbe@436
|
196 UPDATE "direct_voter" SET "weight" = 1
|
jbe@436
|
197 WHERE "issue_id" = "issue_id_p";
|
jbe@436
|
198 PERFORM "add_vote_delegations"("issue_id_p");
|
jbe@436
|
199 -- mark first preferences:
|
jbe@436
|
200 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
|
jbe@436
|
201 FROM (
|
jbe@436
|
202 SELECT
|
jbe@436
|
203 "vote"."initiative_id",
|
jbe@436
|
204 "vote"."member_id",
|
jbe@436
|
205 CASE WHEN "vote"."grade" > 0 THEN
|
jbe@436
|
206 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
|
jbe@436
|
207 ELSE NULL
|
jbe@436
|
208 END AS "first_preference"
|
jbe@436
|
209 FROM "vote"
|
jbe@436
|
210 JOIN "initiative" -- NOTE: due to missing index on issue_id
|
jbe@436
|
211 ON "vote"."issue_id" = "initiative"."issue_id"
|
jbe@436
|
212 JOIN "vote" AS "agg"
|
jbe@436
|
213 ON "initiative"."id" = "agg"."initiative_id"
|
jbe@436
|
214 AND "vote"."member_id" = "agg"."member_id"
|
jbe@436
|
215 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
|
jbe@436
|
216 ) AS "subquery"
|
jbe@436
|
217 WHERE "vote"."issue_id" = "issue_id_p"
|
jbe@436
|
218 AND "vote"."initiative_id" = "subquery"."initiative_id"
|
jbe@436
|
219 AND "vote"."member_id" = "subquery"."member_id";
|
jbe@436
|
220 -- finish overriding protection triggers (avoids garbage):
|
jbe@436
|
221 DELETE FROM "temporary_transaction_data"
|
jbe@436
|
222 WHERE "key" = 'override_protection_triggers';
|
jbe@436
|
223 -- materialize battle_view:
|
jbe@436
|
224 -- NOTE: "closed" column of issue must be set at this point
|
jbe@436
|
225 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
jbe@436
|
226 INSERT INTO "battle" (
|
jbe@436
|
227 "issue_id",
|
jbe@436
|
228 "winning_initiative_id", "losing_initiative_id",
|
jbe@436
|
229 "count"
|
jbe@436
|
230 ) SELECT
|
jbe@436
|
231 "issue_id",
|
jbe@436
|
232 "winning_initiative_id", "losing_initiative_id",
|
jbe@436
|
233 "count"
|
jbe@436
|
234 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
|
jbe@436
|
235 -- set voter count:
|
jbe@436
|
236 UPDATE "issue" SET
|
jbe@436
|
237 "voter_count" = (
|
jbe@436
|
238 SELECT coalesce(sum("weight"), 0)
|
jbe@436
|
239 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
|
jbe@436
|
240 )
|
jbe@436
|
241 WHERE "id" = "issue_id_p";
|
jbe@437
|
242 -- copy "positive_votes" and "negative_votes" from "battle" table:
|
jbe@437
|
243 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
|
jbe@437
|
244 UPDATE "initiative" SET
|
jbe@437
|
245 "first_preference_votes" = 0,
|
jbe@437
|
246 "positive_votes" = "battle_win"."count",
|
jbe@437
|
247 "negative_votes" = "battle_lose"."count"
|
jbe@437
|
248 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
|
jbe@437
|
249 WHERE
|
jbe@437
|
250 "battle_win"."issue_id" = "issue_id_p" AND
|
jbe@437
|
251 "battle_win"."winning_initiative_id" = "initiative"."id" AND
|
jbe@437
|
252 "battle_win"."losing_initiative_id" ISNULL AND
|
jbe@437
|
253 "battle_lose"."issue_id" = "issue_id_p" AND
|
jbe@437
|
254 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
|
jbe@437
|
255 "battle_lose"."winning_initiative_id" ISNULL;
|
jbe@436
|
256 -- calculate "first_preference_votes":
|
jbe@437
|
257 -- NOTE: will only set values not equal to zero
|
jbe@437
|
258 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
|
jbe@436
|
259 FROM (
|
jbe@436
|
260 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
|
jbe@436
|
261 FROM "vote" JOIN "direct_voter"
|
jbe@436
|
262 ON "vote"."issue_id" = "direct_voter"."issue_id"
|
jbe@436
|
263 AND "vote"."member_id" = "direct_voter"."member_id"
|
jbe@436
|
264 WHERE "vote"."first_preference"
|
jbe@436
|
265 GROUP BY "vote"."initiative_id"
|
jbe@436
|
266 ) AS "subquery"
|
jbe@436
|
267 WHERE "initiative"."issue_id" = "issue_id_p"
|
jbe@436
|
268 AND "initiative"."admitted"
|
jbe@436
|
269 AND "initiative"."id" = "subquery"."initiative_id";
|
jbe@436
|
270 END;
|
jbe@436
|
271 $$;
|
jbe@436
|
272
|
jbe@436
|
273 COMMIT;
|