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