liquid_feedback_core: eb12a069063c update/core-update.v3.0.2-v3.0.3.sql

liquid_feedback_core

view update/core-update.v3.0.2-v3.0.3.sql @ 437:eb12a069063c

Improved previous fix of "close_voting" function

author	jbe
date	Wed Jul 16 16:50:07 2014 +0200 (2014-07-16)
parents	34cc98defa8b
children	785ea3c0fd18

line source

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.

5 BEGIN;

7 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

8 SELECT * FROM (VALUES ('3.0.3', 3, 0, 3))

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

11 CREATE FUNCTION "update3_0_3_add_columns_if_missing"()

12 RETURNS VOID

13 LANGUAGE 'plpgsql' AS $$

14 BEGIN

15 BEGIN

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

17 EXCEPTION

18 WHEN duplicate_column THEN

19 RAISE NOTICE 'column "first_preference_votes" of relation "initiative" already exists, skipping';

20 END;

21 BEGIN

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

23 EXCEPTION

24 WHEN duplicate_column THEN

25 RAISE NOTICE 'column "first_preference" of relation "vote" already exists, skipping';

26 END;

27 RETURN;

28 END;

29 $$;

31 SELECT "update3_0_3_add_columns_if_missing"();

33 DROP FUNCTION "update3_0_3_add_columns_if_missing"();

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

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

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

38 ( "first_preference_votes" ISNULL AND

39 "positive_votes" ISNULL AND "negative_votes" ISNULL AND

40 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND

41 "schulze_rank" ISNULL AND

42 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND

43 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND

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

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

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

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

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

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

53 ALTER TABLE "vote" DROP CONSTRAINT IF EXISTS "first_preference_flag_only_set_on_positive_grades";

54 ALTER TABLE "vote" ADD

55 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"

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

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

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

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

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

64 FROM (

65 SELECT

66 "vote"."initiative_id",

67 "vote"."member_id",

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

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

70 ELSE NULL

71 END AS "first_preference"

72 FROM "vote"

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

74 ON "vote"."issue_id" = "initiative"."issue_id"

75 JOIN "vote" AS "agg"

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

77 AND "vote"."member_id" = "agg"."member_id"

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

79 ) AS "subquery"

80 WHERE "vote"."initiative_id" = "subquery"."initiative_id"

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

83 DELETE FROM "temporary_transaction_data"

84 WHERE "key" = 'override_protection_triggers';

86 UPDATE "initiative" SET "first_preference_votes" = NULL

87 WHERE "first_preference_votes" = 0;

89 UPDATE "initiative"

90 SET "first_preference_votes" = "subquery"."sum"

91 FROM (

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

93 FROM "vote" JOIN "direct_voter"

94 ON "vote"."issue_id" = "direct_voter"."issue_id"

95 AND "vote"."member_id" = "direct_voter"."member_id"

96 WHERE "vote"."first_preference"

97 GROUP BY "vote"."initiative_id"

98 ) AS "subquery"

99 WHERE "initiative"."admitted"

100 AND "initiative"."id" = "subquery"."initiative_id"

101 AND "initiative"."first_preference_votes" ISNULL;

102

103 UPDATE "initiative" SET "first_preference_votes" = 0

104 WHERE "positive_votes" NOTNULL

105 AND "first_preference_votes" ISNULL;

106

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

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

109 INSERT INTO "battle" (

110 "issue_id",

111 "winning_initiative_id",

112 "losing_initiative_id",

113 "count"

114 ) SELECT

115 "battle_view"."issue_id",

116 "battle_view"."winning_initiative_id",

117 "battle_view"."losing_initiative_id",

118 "battle_view"."count"

119 FROM (

120 SELECT

121 "issue"."id" AS "issue_id",

122 "winning_initiative"."id" AS "winning_initiative_id",

123 "losing_initiative"."id" AS "losing_initiative_id",

124 sum(

125 CASE WHEN

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

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

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

129 ) AS "count"

130 FROM "issue"

131 LEFT JOIN "direct_voter"

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

133 JOIN "battle_participant" AS "winning_initiative"

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

135 JOIN "battle_participant" AS "losing_initiative"

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

137 LEFT JOIN "vote" AS "better_vote"

138 ON "direct_voter"."member_id" = "better_vote"."member_id"

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

140 LEFT JOIN "vote" AS "worse_vote"

141 ON "direct_voter"."member_id" = "worse_vote"."member_id"

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

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

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

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

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

147 GROUP BY

148 "issue"."id",

149 "winning_initiative"."id",

150 "losing_initiative"."id"

151 ) AS "battle_view"

152 LEFT JOIN "battle"

153 ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id"

154 AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id"

155 WHERE "battle" ISNULL;

156

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

158 RETURNS VOID

159 LANGUAGE 'plpgsql' VOLATILE AS $$

160 DECLARE

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

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

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

164 BEGIN

165 PERFORM "require_transaction_isolation"();

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

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

168 -- override protection triggers:

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

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

171 -- delete timestamp of voting comment:

172 UPDATE "direct_voter" SET "comment_changed" = NULL

173 WHERE "issue_id" = "issue_id_p";

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

175 DELETE FROM "delegating_voter"

176 WHERE "issue_id" = "issue_id_p";

177 -- delete votes from non-privileged voters:

178 DELETE FROM "direct_voter"

179 USING (

180 SELECT

181 "direct_voter"."member_id"

182 FROM "direct_voter"

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

184 LEFT JOIN "privilege"

185 ON "privilege"."unit_id" = "unit_id_v"

186 AND "privilege"."member_id" = "direct_voter"."member_id"

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

188 "member"."active" = FALSE OR

189 "privilege"."voting_right" ISNULL OR

190 "privilege"."voting_right" = FALSE

191 )

192 ) AS "subquery"

193 WHERE "direct_voter"."issue_id" = "issue_id_p"

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

195 -- consider delegations:

196 UPDATE "direct_voter" SET "weight" = 1

197 WHERE "issue_id" = "issue_id_p";

198 PERFORM "add_vote_delegations"("issue_id_p");

199 -- mark first preferences:

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

201 FROM (

202 SELECT

203 "vote"."initiative_id",

204 "vote"."member_id",

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

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

207 ELSE NULL

208 END AS "first_preference"

209 FROM "vote"

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

211 ON "vote"."issue_id" = "initiative"."issue_id"

212 JOIN "vote" AS "agg"

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

214 AND "vote"."member_id" = "agg"."member_id"

215 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"

216 ) AS "subquery"

217 WHERE "vote"."issue_id" = "issue_id_p"

218 AND "vote"."initiative_id" = "subquery"."initiative_id"

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

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

221 DELETE FROM "temporary_transaction_data"

222 WHERE "key" = 'override_protection_triggers';

223 -- materialize battle_view:

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

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

226 INSERT INTO "battle" (

227 "issue_id",

228 "winning_initiative_id", "losing_initiative_id",

229 "count"

230 ) SELECT

231 "issue_id",

232 "winning_initiative_id", "losing_initiative_id",

233 "count"

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

235 -- set voter count:

236 UPDATE "issue" SET

237 "voter_count" = (

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

239 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

240 )

241 WHERE "id" = "issue_id_p";

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

243 -- NOTE: "first_preference_votes" is set to a default of 0 at this step

244 UPDATE "initiative" SET

245 "first_preference_votes" = 0,

246 "positive_votes" = "battle_win"."count",

247 "negative_votes" = "battle_lose"."count"

248 FROM "battle" AS "battle_win", "battle" AS "battle_lose"

249 WHERE

250 "battle_win"."issue_id" = "issue_id_p" AND

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

252 "battle_win"."losing_initiative_id" ISNULL AND

253 "battle_lose"."issue_id" = "issue_id_p" AND

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

255 "battle_lose"."winning_initiative_id" ISNULL;

256 -- calculate "first_preference_votes":

257 -- NOTE: will only set values not equal to zero

258 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"

259 FROM (

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

261 FROM "vote" JOIN "direct_voter"

262 ON "vote"."issue_id" = "direct_voter"."issue_id"

263 AND "vote"."member_id" = "direct_voter"."member_id"

264 WHERE "vote"."first_preference"

265 GROUP BY "vote"."initiative_id"

266 ) AS "subquery"

267 WHERE "initiative"."issue_id" = "issue_id_p"

268 AND "initiative"."admitted"

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

270 END;

271 $$;

272

273 COMMIT;