1 -- NOTICE: This update script disables the "no_reserve_beat_path" setting for

2 -- all policies. If this is not intended, please edit this script

3 -- before applying it to your database.

4

5 BEGIN;

6

7 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

8 SELECT * FROM (VALUES ('3.0.1', 3, 0, 1))

9 AS "subquery"("string", "major", "minor", "revision");

10

11 ALTER TABLE "policy" ALTER COLUMN "no_reverse_beat_path" SET DEFAULT FALSE;

12

13 UPDATE "policy" SET "no_reverse_beat_path" = FALSE; -- recommended

14

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.';

16

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").';

18

19 ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4;

20

21 ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";

22 ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (

23 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR

24 ( "first_preference_votes" ISNULL AND

25 "positive_votes" ISNULL AND "negative_votes" ISNULL AND

26 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND

27 "schulze_rank" ISNULL AND

28 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND

29 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND

30 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );

31

32 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';

33 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';

34 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';

35

36 -- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL; -- should not be necessary

37 ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL;

38

39 ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN;

40

41 ALTER TABLE "vote" ADD

42 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"

43 CHECK ("grade" > 0 OR "first_preference" ISNULL);

44

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.';

46

47 INSERT INTO "temporary_transaction_data" ("key", "value")

48 VALUES ('override_protection_triggers', TRUE::TEXT);

49

50 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"

51 FROM (

52 SELECT

53 "vote"."initiative_id",

54 "vote"."member_id",

55 CASE WHEN "vote"."grade" > 0 THEN

56 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END

57 ELSE NULL

58 END AS "first_preference"

59 FROM "vote"

60 JOIN "initiative" -- NOTE: due to missing index on issue_id

61 ON "vote"."issue_id" = "initiative"."issue_id"

62 JOIN "vote" AS "agg"

63 ON "initiative"."id" = "agg"."initiative_id"

64 AND "vote"."member_id" = "agg"."member_id"

65 GROUP BY "vote"."initiative_id", "vote"."member_id"

66 ) AS "subquery"

67 WHERE "vote"."initiative_id" = "subquery"."initiative_id"

68 AND "vote"."member_id" = "subquery"."member_id";

69

70 DELETE FROM "temporary_transaction_data"

71 WHERE "key" = 'override_protection_triggers';

72

73 UPDATE "initiative"

74 SET "first_preference_votes" = coalesce("subquery"."sum", 0)

75 FROM (

76 SELECT "vote"."initiative_id", sum("direct_voter"."weight")

77 FROM "vote" JOIN "direct_voter"

78 ON "vote"."issue_id" = "direct_voter"."issue_id"

79 AND "vote"."member_id" = "direct_voter"."member_id"

80 WHERE "vote"."first_preference"

81 GROUP BY "vote"."initiative_id"

82 ) AS "subquery"

83 WHERE "initiative"."admitted"

84 AND "initiative"."id" = "subquery"."initiative_id";

85

86 -- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0)

87 -- to avoid future data loss when executing "clean_issue" to delete voting data:

88 INSERT INTO "battle" (

89 "issue_id",

90 "winning_initiative_id",

91 "losing_initiative_id",

92 "count"

93 ) SELECT

94 "battle_view"."issue_id",

95 "battle_view"."winning_initiative_id",

96 "battle_view"."losing_initiative_id",

97 "battle_view"."count"

98 FROM (

99 SELECT

100 "issue"."id" AS "issue_id",

101 "winning_initiative"."id" AS "winning_initiative_id",

102 "losing_initiative"."id" AS "losing_initiative_id",

103 sum(

104 CASE WHEN

105 coalesce("better_vote"."grade", 0) >

106 coalesce("worse_vote"."grade", 0)

107 THEN "direct_voter"."weight" ELSE 0 END

108 ) AS "count"

109 FROM "issue"

110 LEFT JOIN "direct_voter"

111 ON "issue"."id" = "direct_voter"."issue_id"

112 JOIN "battle_participant" AS "winning_initiative"

113 ON "issue"."id" = "winning_initiative"."issue_id"

114 JOIN "battle_participant" AS "losing_initiative"

115 ON "issue"."id" = "losing_initiative"."issue_id"

116 LEFT JOIN "vote" AS "better_vote"

117 ON "direct_voter"."member_id" = "better_vote"."member_id"

118 AND "winning_initiative"."id" = "better_vote"."initiative_id"

119 LEFT JOIN "vote" AS "worse_vote"

120 ON "direct_voter"."member_id" = "worse_vote"."member_id"

121 AND "losing_initiative"."id" = "worse_vote"."initiative_id"

122 WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner')

123 AND "winning_initiative"."id" != "losing_initiative"."id"

124 -- NOTE: comparisons with status-quo are intentionally omitted to mark

125 -- issues that were counted prior LiquidFeedback Core v2.0.0

126 GROUP BY

127 "issue"."id",

128 "winning_initiative"."id",

129 "losing_initiative"."id"

130 ) AS "battle_view"

131 LEFT JOIN "battle"

132 ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id"

133 AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id"

134 WHERE "battle" ISNULL;

135

136 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)

137 RETURNS VOID

138 LANGUAGE 'plpgsql' VOLATILE AS $$

139 DECLARE

140 "area_id_v" "area"."id"%TYPE;

141 "unit_id_v" "unit"."id"%TYPE;

142 "member_id_v" "member"."id"%TYPE;

143 BEGIN

144 PERFORM "require_transaction_isolation"();

145 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";

146 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";

147 -- override protection triggers:

148 INSERT INTO "temporary_transaction_data" ("key", "value")

149 VALUES ('override_protection_triggers', TRUE::TEXT);

150 -- delete timestamp of voting comment:

151 UPDATE "direct_voter" SET "comment_changed" = NULL

152 WHERE "issue_id" = "issue_id_p";

153 -- delete delegating votes (in cases of manual reset of issue state):

154 DELETE FROM "delegating_voter"

155 WHERE "issue_id" = "issue_id_p";

156 -- delete votes from non-privileged voters:

157 DELETE FROM "direct_voter"

158 USING (

159 SELECT

160 "direct_voter"."member_id"

161 FROM "direct_voter"

162 JOIN "member" ON "direct_voter"."member_id" = "member"."id"

163 LEFT JOIN "privilege"

164 ON "privilege"."unit_id" = "unit_id_v"

165 AND "privilege"."member_id" = "direct_voter"."member_id"

166 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (

167 "member"."active" = FALSE OR

168 "privilege"."voting_right" ISNULL OR

169 "privilege"."voting_right" = FALSE

170 )

171 ) AS "subquery"

172 WHERE "direct_voter"."issue_id" = "issue_id_p"

173 AND "direct_voter"."member_id" = "subquery"."member_id";

174 -- consider delegations:

175 UPDATE "direct_voter" SET "weight" = 1

176 WHERE "issue_id" = "issue_id_p";

177 PERFORM "add_vote_delegations"("issue_id_p");

178 -- mark first preferences:

179 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"

180 FROM (

181 SELECT

182 "vote"."initiative_id",

183 "vote"."member_id",

184 CASE WHEN "vote"."grade" > 0 THEN

185 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END

186 ELSE NULL

187 END AS "first_preference"

188 FROM "vote"

189 JOIN "initiative" -- NOTE: due to missing index on issue_id

190 ON "vote"."issue_id" = "initiative"."issue_id"

191 JOIN "vote" AS "agg"

192 ON "initiative"."id" = "agg"."initiative_id"

193 AND "vote"."member_id" = "agg"."member_id"

194 GROUP BY "vote"."initiative_id", "vote"."member_id"

195 ) AS "subquery"

196 WHERE "vote"."issue_id" = "issue_id_p"

197 AND "vote"."initiative_id" = "subquery"."initiative_id"

198 AND "vote"."member_id" = "subquery"."member_id";

199 -- finish overriding protection triggers (avoids garbage):

200 DELETE FROM "temporary_transaction_data"

201 WHERE "key" = 'override_protection_triggers';

202 -- materialize battle_view:

203 -- NOTE: "closed" column of issue must be set at this point

204 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

205 INSERT INTO "battle" (

206 "issue_id",

207 "winning_initiative_id", "losing_initiative_id",

208 "count"

209 ) SELECT

210 "issue_id",

211 "winning_initiative_id", "losing_initiative_id",

212 "count"

213 FROM "battle_view" WHERE "issue_id" = "issue_id_p";

214 -- set voter count:

215 UPDATE "issue" SET

216 "voter_count" = (

217 SELECT coalesce(sum("weight"), 0)

218 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

219 )

220 WHERE "id" = "issue_id_p";

221 -- calculate "first_preference_votes":

222 UPDATE "initiative"

223 SET "first_preference_votes" = coalesce("subquery"."sum", 0)

224 FROM (

225 SELECT "vote"."initiative_id", sum("direct_voter"."weight")

226 FROM "vote" JOIN "direct_voter"

227 ON "vote"."issue_id" = "direct_voter"."issue_id"

228 AND "vote"."member_id" = "direct_voter"."member_id"

229 WHERE "vote"."first_preference"

230 GROUP BY "vote"."initiative_id"

231 ) AS "subquery"

232 WHERE "initiative"."issue_id" = "issue_id_p"

233 AND "initiative"."admitted"

234 AND "initiative"."id" = "subquery"."initiative_id";

235 -- copy "positive_votes" and "negative_votes" from "battle" table:

236 UPDATE "initiative" SET

237 "positive_votes" = "battle_win"."count",

238 "negative_votes" = "battle_lose"."count"

239 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

240 WHERE

241 "battle_win"."issue_id" = "issue_id_p" AND

242 "battle_win"."winning_initiative_id" = "initiative"."id" AND

243 "battle_win"."losing_initiative_id" ISNULL AND

244 "battle_lose"."issue_id" = "issue_id_p" AND

245 "battle_lose"."losing_initiative_id" = "initiative"."id" AND

246 "battle_lose"."winning_initiative_id" ISNULL;

247 END;

248 $$;

249

250 COMMIT;