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 DEFAULT '0';

15 ALTER TABLE "policy" ALTER COLUMN "min_admission_time" DROP DEFAULT;

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

17

18 ALTER TABLE "policy" DROP CONSTRAINT "timing";

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

20 ( "polling" = FALSE AND

21 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND

22 "discussion_time" NOTNULL AND

23 "verification_time" NOTNULL AND

24 "voting_time" NOTNULL ) OR

25 ( "polling" = TRUE AND

26 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND

27 "discussion_time" NOTNULL AND

28 "verification_time" NOTNULL AND

29 "voting_time" NOTNULL ) OR

30 ( "polling" = TRUE AND

31 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND

32 "discussion_time" ISNULL AND

33 "verification_time" ISNULL AND

34 "voting_time" ISNULL ) );

35

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

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

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

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

40

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

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

43

44 ALTER TABLE "issue" ADD COLUMN "min_admission_time" INTERVAL DEFAULT '0';

45 ALTER TABLE "issue" ALTER COLUMN "min_admission_time" DROP DEFAULT;

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

47

48 ALTER TABLE "issue" DROP CONSTRAINT "admission_time_not_null_unless_instantly_accepted";

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

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

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

52

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

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

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

56

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

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

59

60 DROP TRIGGER "update_text_search_data" ON "initiative";

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

62

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

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

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

66

67 CREATE TRIGGER "update_text_search_data"

68 BEFORE INSERT OR UPDATE ON "initiative"

69 FOR EACH ROW EXECUTE PROCEDURE

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

71

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

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

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

75

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

77 "event" != 'issue_state_changed' OR (

78 "member_id" ISNULL AND

79 "issue_id" NOTNULL AND

80 "state" NOTNULL AND

81 "initiative_id" ISNULL AND

82 "draft_id" ISNULL AND

83 "suggestion_id" ISNULL ) );

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

85 "event" NOT IN (

86 'initiative_created_in_new_issue',

87 'initiative_created_in_existing_issue',

88 'initiative_revoked',

89 'new_draft_created'

90 ) OR (

91 "member_id" NOTNULL AND

92 "issue_id" NOTNULL AND

93 "state" NOTNULL AND

94 "initiative_id" NOTNULL AND

95 "draft_id" NOTNULL AND

96 "suggestion_id" ISNULL ) );

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

98 "event" != 'suggestion_created' OR (

99 "member_id" NOTNULL AND

100 "issue_id" NOTNULL AND

101 "state" NOTNULL AND

102 "initiative_id" NOTNULL AND

103 "draft_id" ISNULL AND

104 "suggestion_id" NOTNULL ) );

105

106 CREATE OR REPLACE FUNCTION "copy_timings_trigger"()

107 RETURNS TRIGGER

108 LANGUAGE 'plpgsql' VOLATILE AS $$

109 DECLARE

110 "policy_row" "policy"%ROWTYPE;

111 BEGIN

112 SELECT * INTO "policy_row" FROM "policy"

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

114 IF NEW."min_admission_time" ISNULL THEN

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

116 END IF;

117 IF NEW."max_admission_time" ISNULL THEN

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

119 END IF;

120 IF NEW."discussion_time" ISNULL THEN

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

122 END IF;

123 IF NEW."verification_time" ISNULL THEN

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

125 END IF;

126 IF NEW."voting_time" ISNULL THEN

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

128 END IF;

129 RETURN NEW;

130 END;

131 $$;

132

133 CREATE OR REPLACE FUNCTION "check_issue"

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

135 "persist" "check_issue_persistence" )

136 RETURNS "check_issue_persistence"

137 LANGUAGE 'plpgsql' VOLATILE AS $$

138 DECLARE

139 "issue_row" "issue"%ROWTYPE;

140 "policy_row" "policy"%ROWTYPE;

141 "initiative_row" "initiative"%ROWTYPE;

142 "state_v" "issue_state";

143 BEGIN

144 PERFORM "require_transaction_isolation"();

145 IF "persist" ISNULL THEN

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

147 FOR UPDATE;

148 IF "issue_row"."closed" NOTNULL THEN

149 RETURN NULL;

150 END IF;

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

152 IF

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

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

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

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

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

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

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

160 "issue_row"."fully_frozen" + "issue_row"."voting_time" )

161 THEN

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

163 ELSE

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

165 END IF;

166 IF

167 NOT EXISTS (

168 -- all initiatives are revoked

169 SELECT NULL FROM "initiative"

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

171 ) AND (

172 -- and issue has not been accepted yet

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

174 -- or verification time has elapsed

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

176 "persist"."phase_finished" ) OR

177 -- or no initiatives have been revoked lately

178 NOT EXISTS (

179 SELECT NULL FROM "initiative"

180 WHERE "issue_id" = "issue_id_p"

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

182 )

183 )

184 THEN

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

186 ELSE

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

188 END IF;

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

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

191 WHERE "id" = "issue_row"."id";

192 RETURN "persist";

193 ELSIF

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

195 THEN

196 RETURN "persist";

197 ELSE

198 RETURN NULL;

199 END IF;

200 END IF;

201 IF

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

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

204 THEN

205 PERFORM "create_snapshot"("issue_id_p");

206 "persist"."snapshot_created" = TRUE;

207 IF "persist"."phase_finished" THEN

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

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

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

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

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

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

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

215 SELECT * INTO "policy_row" FROM "policy"

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

217 FOR "initiative_row" IN

218 SELECT * FROM "initiative"

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

220 FOR UPDATE

221 LOOP

222 IF

223 "initiative_row"."polling" OR (

224 "initiative_row"."satisfied_supporter_count" > 0 AND

225 "initiative_row"."satisfied_supporter_count" *

226 "policy_row"."initiative_quorum_den" >=

227 "issue_row"."population" * "policy_row"."initiative_quorum_num"

228 )

229 THEN

230 UPDATE "initiative" SET "admitted" = TRUE

231 WHERE "id" = "initiative_row"."id";

232 ELSE

233 UPDATE "initiative" SET "admitted" = FALSE

234 WHERE "id" = "initiative_row"."id";

235 END IF;

236 END LOOP;

237 END IF;

238 END IF;

239 RETURN "persist";

240 END IF;

241 IF

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

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

244 THEN

245 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

246 "persist"."harmonic_weights_set" = TRUE;

247 IF

248 "persist"."phase_finished" OR

249 "persist"."issue_revoked" OR

250 "persist"."state" = 'admission'

251 THEN

252 RETURN "persist";

253 ELSE

254 RETURN NULL;

255 END IF;

256 END IF;

257 IF "persist"."issue_revoked" THEN

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

259 "state_v" := 'canceled_revoked_before_accepted';

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

261 "state_v" := 'canceled_after_revocation_during_discussion';

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

263 "state_v" := 'canceled_after_revocation_during_verification';

264 END IF;

265 UPDATE "issue" SET

266 "state" = "state_v",

267 "closed" = "phase_finished",

268 "phase_finished" = NULL

269 WHERE "id" = "issue_id_p";

270 RETURN NULL;

271 END IF;

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

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

274 FOR UPDATE;

275 SELECT * INTO "policy_row"

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

277 IF

278 ( now() >=

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

280 EXISTS (

281 SELECT NULL FROM "initiative"

282 WHERE "issue_id" = "issue_id_p"

283 AND "supporter_count" > 0

284 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

286 )

287 THEN

288 UPDATE "issue" SET

289 "state" = 'discussion',

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

291 "phase_finished" = NULL

292 WHERE "id" = "issue_id_p";

293 ELSIF "issue_row"."phase_finished" NOTNULL THEN

294 UPDATE "issue" SET

295 "state" = 'canceled_issue_not_accepted',

296 "closed" = "phase_finished",

297 "phase_finished" = NULL

298 WHERE "id" = "issue_id_p";

299 END IF;

300 RETURN NULL;

301 END IF;

302 IF "persist"."phase_finished" THEN

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

304 UPDATE "issue" SET

305 "state" = 'verification',

306 "half_frozen" = "phase_finished",

307 "phase_finished" = NULL

308 WHERE "id" = "issue_id_p";

309 RETURN NULL;

310 END IF;

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

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

313 FOR UPDATE;

314 SELECT * INTO "policy_row" FROM "policy"

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

316 IF EXISTS (

317 SELECT NULL FROM "initiative"

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

319 ) THEN

320 UPDATE "issue" SET

321 "state" = 'voting',

322 "fully_frozen" = "phase_finished",

323 "phase_finished" = NULL

324 WHERE "id" = "issue_id_p";

325 ELSE

326 UPDATE "issue" SET

327 "state" = 'canceled_no_initiative_admitted',

328 "fully_frozen" = "phase_finished",

329 "closed" = "phase_finished",

330 "phase_finished" = NULL

331 WHERE "id" = "issue_id_p";

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

333 -- issue state has been manipulated

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

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

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

337 END IF;

338 RETURN NULL;

339 END IF;

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

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

342 PERFORM "close_voting"("issue_id_p");

343 "persist"."closed_voting" = TRUE;

344 RETURN "persist";

345 END IF;

346 PERFORM "calculate_ranks"("issue_id_p");

347 RETURN NULL;

348 END IF;

349 END IF;

350 RAISE WARNING 'should not happen';

351 RETURN NULL;

352 END;

353 $$;

354

355 COMMIT;