1 BEGIN;

2

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('3.1.0', 3, 1, 0))

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

6

7 ALTER TABLE "member" DROP CONSTRAINT "authority_requires_uid_and_vice_versa";

8 ALTER TABLE "member" ADD CONSTRAINT "authority_requires_uid_and_vice_versa" CHECK (

9 ("authority" NOTNULL) = ("authority_uid" NOTNULL) );

10

11 DROP TABLE "member_application";

12 DROP TYPE "application_access_level";

13

14 ALTER TABLE "policy" ADD COLUMN "min_admission_time" INTERVAL;

15 ALTER TABLE "policy" RENAME COLUMN "admission_time" TO "max_admission_time";

16

17 ALTER TABLE "policy" DROP CONSTRAINT "timing"; -- re-added after UPDATE

18

19 ALTER TABLE "policy" DROP CONSTRAINT "issue_quorum_if_and_only_if_not_polling";

20 ALTER TABLE "policy" ADD CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (

21 "polling" = ("issue_quorum_num" ISNULL) AND

22 "polling" = ("issue_quorum_den" ISNULL) );

23

24 COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues without issue quorum, where certain initiatives (those having the "polling" flag set) do not need to pass the initiative quorum; "min_admission_time" and "max_admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues';

25 COMMENT ON COLUMN "policy"."min_admission_time" IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open';

26

27 ALTER TABLE "issue" ADD COLUMN "min_admission_time" INTERVAL;

28 ALTER TABLE "issue" RENAME COLUMN "admission_time" TO "max_admission_time";

29

30 ALTER TABLE "issue" DROP CONSTRAINT "admission_time_not_null_unless_instantly_accepted"; -- re-added after UPDATE

31

32 ALTER TABLE "issue" DROP CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event";

33 ALTER TABLE "issue" ADD CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event" CHECK (

34 (("snapshot" NOTNULL) = ("latest_snapshot_event" NOTNULL)) );

35

36 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "max_admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.';

37 COMMENT ON COLUMN "issue"."min_admission_time" IS 'Copied from "policy" table at creation of issue';

38

39 DROP TRIGGER "update_text_search_data" ON "initiative";

40 ALTER TABLE "initiative" DROP COLUMN "discussion_url";

41

42 ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null";

43 ALTER TABLE "initiative" ADD CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null" CHECK (

44 ("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL) );

45

46 CREATE TRIGGER "update_text_search_data"

47 BEFORE INSERT OR UPDATE ON "initiative"

48 FOR EACH ROW EXECUTE PROCEDURE

49 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name");

50

51 ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_issue_state_changed";

52 ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft";

53 ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_suggestion_creation";

54

55 ALTER TABLE "event" ADD CONSTRAINT "null_constr_for_issue_state_changed" CHECK (

56 "event" != 'issue_state_changed' OR (

57 "member_id" ISNULL AND

58 "issue_id" NOTNULL AND

59 "state" NOTNULL AND

60 "initiative_id" ISNULL AND

61 "draft_id" ISNULL AND

62 "suggestion_id" ISNULL ) );

63 ALTER TABLE "event" ADD CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (

64 "event" NOT IN (

65 'initiative_created_in_new_issue',

66 'initiative_created_in_existing_issue',

67 'initiative_revoked',

68 'new_draft_created'

69 ) OR (

70 "member_id" NOTNULL AND

71 "issue_id" NOTNULL AND

72 "state" NOTNULL AND

73 "initiative_id" NOTNULL AND

74 "draft_id" NOTNULL AND

75 "suggestion_id" ISNULL ) );

76 ALTER TABLE "event" ADD CONSTRAINT "null_constr_for_suggestion_creation" CHECK (

77 "event" != 'suggestion_created' OR (

78 "member_id" NOTNULL AND

79 "issue_id" NOTNULL AND

80 "state" NOTNULL AND

81 "initiative_id" NOTNULL AND

82 "draft_id" ISNULL AND

83 "suggestion_id" NOTNULL ) );

84

85 CREATE OR REPLACE FUNCTION "copy_timings_trigger"()

86 RETURNS TRIGGER

87 LANGUAGE 'plpgsql' VOLATILE AS $$

88 DECLARE

89 "policy_row" "policy"%ROWTYPE;

90 BEGIN

91 SELECT * INTO "policy_row" FROM "policy"

92 WHERE "id" = NEW."policy_id";

93 IF NEW."min_admission_time" ISNULL THEN

94 NEW."min_admission_time" := "policy_row"."min_admission_time";

95 END IF;

96 IF NEW."max_admission_time" ISNULL THEN

97 NEW."max_admission_time" := "policy_row"."max_admission_time";

98 END IF;

99 IF NEW."discussion_time" ISNULL THEN

100 NEW."discussion_time" := "policy_row"."discussion_time";

101 END IF;

102 IF NEW."verification_time" ISNULL THEN

103 NEW."verification_time" := "policy_row"."verification_time";

104 END IF;

105 IF NEW."voting_time" ISNULL THEN

106 NEW."voting_time" := "policy_row"."voting_time";

107 END IF;

108 RETURN NEW;

109 END;

110 $$;

111

112 CREATE OR REPLACE FUNCTION "check_issue"

113 ( "issue_id_p" "issue"."id"%TYPE,

114 "persist" "check_issue_persistence" )

115 RETURNS "check_issue_persistence"

116 LANGUAGE 'plpgsql' VOLATILE AS $$

117 DECLARE

118 "issue_row" "issue"%ROWTYPE;

119 "policy_row" "policy"%ROWTYPE;

120 "initiative_row" "initiative"%ROWTYPE;

121 "state_v" "issue_state";

122 BEGIN

123 PERFORM "require_transaction_isolation"();

124 IF "persist" ISNULL THEN

125 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

126 FOR UPDATE;

127 IF "issue_row"."closed" NOTNULL THEN

128 RETURN NULL;

129 END IF;

130 "persist"."state" := "issue_row"."state";

131 IF

132 ( "issue_row"."state" = 'admission' AND now() >=

133 "issue_row"."created" + "issue_row"."max_admission_time" ) OR

134 ( "issue_row"."state" = 'discussion' AND now() >=

135 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR

136 ( "issue_row"."state" = 'verification' AND now() >=

137 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR

138 ( "issue_row"."state" = 'voting' AND now() >=

139 "issue_row"."fully_frozen" + "issue_row"."voting_time" )

140 THEN

141 "persist"."phase_finished" := TRUE;

142 ELSE

143 "persist"."phase_finished" := FALSE;

144 END IF;

145 IF

146 NOT EXISTS (

147 -- all initiatives are revoked

148 SELECT NULL FROM "initiative"

149 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL

150 ) AND (

151 -- and issue has not been accepted yet

152 "persist"."state" = 'admission' OR

153 -- or verification time has elapsed

154 ( "persist"."state" = 'verification' AND

155 "persist"."phase_finished" ) OR

156 -- or no initiatives have been revoked lately

157 NOT EXISTS (

158 SELECT NULL FROM "initiative"

159 WHERE "issue_id" = "issue_id_p"

160 AND now() < "revoked" + "issue_row"."verification_time"

161 )

162 )

163 THEN

164 "persist"."issue_revoked" := TRUE;

165 ELSE

166 "persist"."issue_revoked" := FALSE;

167 END IF;

168 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN

169 UPDATE "issue" SET "phase_finished" = now()

170 WHERE "id" = "issue_row"."id";

171 RETURN "persist";

172 ELSIF

173 "persist"."state" IN ('admission', 'discussion', 'verification')

174 THEN

175 RETURN "persist";

176 ELSE

177 RETURN NULL;

178 END IF;

179 END IF;

180 IF

181 "persist"."state" IN ('admission', 'discussion', 'verification') AND

182 coalesce("persist"."snapshot_created", FALSE) = FALSE

183 THEN

184 PERFORM "create_snapshot"("issue_id_p");

185 "persist"."snapshot_created" = TRUE;

186 IF "persist"."phase_finished" THEN

187 IF "persist"."state" = 'admission' THEN

188 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');

189 ELSIF "persist"."state" = 'discussion' THEN

190 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');

191 ELSIF "persist"."state" = 'verification' THEN

192 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');

193 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";

194 SELECT * INTO "policy_row" FROM "policy"

195 WHERE "id" = "issue_row"."policy_id";

196 FOR "initiative_row" IN

197 SELECT * FROM "initiative"

198 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL

199 FOR UPDATE

200 LOOP

201 IF

202 "initiative_row"."polling" OR (

203 "initiative_row"."satisfied_supporter_count" > 0 AND

204 "initiative_row"."satisfied_supporter_count" *

205 "policy_row"."initiative_quorum_den" >=

206 "issue_row"."population" * "policy_row"."initiative_quorum_num"

207 )

208 THEN

209 UPDATE "initiative" SET "admitted" = TRUE

210 WHERE "id" = "initiative_row"."id";

211 ELSE

212 UPDATE "initiative" SET "admitted" = FALSE

213 WHERE "id" = "initiative_row"."id";

214 END IF;

215 END LOOP;

216 END IF;

217 END IF;

218 RETURN "persist";

219 END IF;

220 IF

221 "persist"."state" IN ('admission', 'discussion', 'verification') AND

222 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE

223 THEN

224 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

225 "persist"."harmonic_weights_set" = TRUE;

226 IF

227 "persist"."phase_finished" OR

228 "persist"."issue_revoked" OR

229 "persist"."state" = 'admission'

230 THEN

231 RETURN "persist";

232 ELSE

233 RETURN NULL;

234 END IF;

235 END IF;

236 IF "persist"."issue_revoked" THEN

237 IF "persist"."state" = 'admission' THEN

238 "state_v" := 'canceled_revoked_before_accepted';

239 ELSIF "persist"."state" = 'discussion' THEN

240 "state_v" := 'canceled_after_revocation_during_discussion';

241 ELSIF "persist"."state" = 'verification' THEN

242 "state_v" := 'canceled_after_revocation_during_verification';

243 END IF;

244 UPDATE "issue" SET

245 "state" = "state_v",

246 "closed" = "phase_finished",

247 "phase_finished" = NULL

248 WHERE "id" = "issue_id_p";

249 RETURN NULL;

250 END IF;

251 IF "persist"."state" = 'admission' THEN

252 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

253 FOR UPDATE;

254 SELECT * INTO "policy_row"

255 FROM "policy" WHERE "id" = "issue_row"."policy_id";

256 IF

257 ( now() >=

258 "issue_row"."created" + "issue_row"."min_admission_time" ) AND

259 EXISTS (

260 SELECT NULL FROM "initiative"

261 WHERE "issue_id" = "issue_id_p"

262 AND "supporter_count" > 0

263 AND "supporter_count" * "policy_row"."issue_quorum_den"

264 >= "issue_row"."population" * "policy_row"."issue_quorum_num"

265 )

266 THEN

267 UPDATE "issue" SET

268 "state" = 'discussion',

269 "accepted" = coalesce("phase_finished", now()),

270 "phase_finished" = NULL

271 WHERE "id" = "issue_id_p";

272 ELSIF "issue_row"."phase_finished" NOTNULL THEN

273 UPDATE "issue" SET

274 "state" = 'canceled_issue_not_accepted',

275 "closed" = "phase_finished",

276 "phase_finished" = NULL

277 WHERE "id" = "issue_id_p";

278 END IF;

279 RETURN NULL;

280 END IF;

281 IF "persist"."phase_finished" THEN

282 IF "persist"."state" = 'discussion' THEN

283 UPDATE "issue" SET

284 "state" = 'verification',

285 "half_frozen" = "phase_finished",

286 "phase_finished" = NULL

287 WHERE "id" = "issue_id_p";

288 RETURN NULL;

289 END IF;

290 IF "persist"."state" = 'verification' THEN

291 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"

292 FOR UPDATE;

293 SELECT * INTO "policy_row" FROM "policy"

294 WHERE "id" = "issue_row"."policy_id";

295 IF EXISTS (

296 SELECT NULL FROM "initiative"

297 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE

298 ) THEN

299 UPDATE "issue" SET

300 "state" = 'voting',

301 "fully_frozen" = "phase_finished",

302 "phase_finished" = NULL

303 WHERE "id" = "issue_id_p";

304 ELSE

305 UPDATE "issue" SET

306 "state" = 'canceled_no_initiative_admitted',

307 "fully_frozen" = "phase_finished",

308 "closed" = "phase_finished",

309 "phase_finished" = NULL

310 WHERE "id" = "issue_id_p";

311 -- NOTE: The following DELETE statements have effect only when

312 -- issue state has been manipulated

313 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";

314 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";

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

316 END IF;

317 RETURN NULL;

318 END IF;

319 IF "persist"."state" = 'voting' THEN

320 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN

321 PERFORM "close_voting"("issue_id_p");

322 "persist"."closed_voting" = TRUE;

323 RETURN "persist";

324 END IF;

325 PERFORM "calculate_ranks"("issue_id_p");

326 RETURN NULL;

327 END IF;

328 END IF;

329 RAISE WARNING 'should not happen';

330 RETURN NULL;

331 END;

332 $$;

333

334 -- temporarily drop deferred trigger to allow UPDATE with subsequent ALTER TABLE:

335 DROP TRIGGER "issue_requires_first_initiative" ON "issue";

336

337 -- set "min_admission_time" values where appropriate:

338 UPDATE "policy" SET "min_admission_time" = '0' WHERE "max_admission_time" NOTNULL;

339 UPDATE "issue" SET "min_admission_time" = '0' WHERE "max_admission_time" NOTNULL;

340

341 -- add constraints related to "min_admission_time" after UPDATE:

342 ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK (

343 ( "polling" = FALSE AND

344 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND

345 "min_admission_time" <= "max_admission_time" AND

346 "discussion_time" NOTNULL AND

347 "verification_time" NOTNULL AND

348 "voting_time" NOTNULL ) OR

349 ( "polling" = TRUE AND

350 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND

351 "discussion_time" NOTNULL AND

352 "verification_time" NOTNULL AND

353 "voting_time" NOTNULL ) OR

354 ( "polling" = TRUE AND

355 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND

356 "discussion_time" ISNULL AND

357 "verification_time" ISNULL AND

358 "voting_time" ISNULL ) );

359

360 ALTER TABLE "issue" ADD CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (

361 ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND

362 ("min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created")) );

363

364 -- re-add temporarily dropped trigger:

365 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"

366 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED

367 FOR EACH ROW EXECUTE PROCEDURE

368 "issue_requires_first_initiative_trigger"();

369 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';

370

371 COMMIT;