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