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 "min_admission_time" <= "max_admission_time"

23 "discussion_time" NOTNULL AND

24 "verification_time" NOTNULL AND

25 "voting_time" NOTNULL ) OR

26 ( "polling" = TRUE AND

27 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND

28 "discussion_time" NOTNULL AND

29 "verification_time" NOTNULL AND

30 "voting_time" NOTNULL ) OR

31 ( "polling" = TRUE AND

32 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND

33 "discussion_time" ISNULL AND

34 "verification_time" ISNULL AND

35 "voting_time" ISNULL ) );

36

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

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

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

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

41

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

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

44

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

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

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

48

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

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

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

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

53

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

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

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

57

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

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

60

61 DROP TRIGGER "update_text_search_data" ON "initiative";

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

63

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

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

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

67

68 CREATE TRIGGER "update_text_search_data"

69 BEFORE INSERT OR UPDATE ON "initiative"

70 FOR EACH ROW EXECUTE PROCEDURE

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

72

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

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

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

76

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

78 "event" != 'issue_state_changed' OR (

79 "member_id" ISNULL AND

80 "issue_id" NOTNULL AND

81 "state" NOTNULL AND

82 "initiative_id" ISNULL AND

83 "draft_id" ISNULL AND

84 "suggestion_id" ISNULL ) );

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

86 "event" NOT IN (

87 'initiative_created_in_new_issue',

88 'initiative_created_in_existing_issue',

89 'initiative_revoked',

90 'new_draft_created'

91 ) OR (

92 "member_id" NOTNULL AND

93 "issue_id" NOTNULL AND

94 "state" NOTNULL AND

95 "initiative_id" NOTNULL AND

96 "draft_id" NOTNULL AND

97 "suggestion_id" ISNULL ) );

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

99 "event" != 'suggestion_created' OR (

100 "member_id" NOTNULL AND

101 "issue_id" NOTNULL AND

102 "state" NOTNULL AND

103 "initiative_id" NOTNULL AND

104 "draft_id" ISNULL AND

105 "suggestion_id" NOTNULL ) );

106

107 CREATE OR REPLACE FUNCTION "copy_timings_trigger"()

108 RETURNS TRIGGER

109 LANGUAGE 'plpgsql' VOLATILE AS $$

110 DECLARE

111 "policy_row" "policy"%ROWTYPE;

112 BEGIN

113 SELECT * INTO "policy_row" FROM "policy"

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

115 IF NEW."min_admission_time" ISNULL THEN

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

117 END IF;

118 IF NEW."max_admission_time" ISNULL THEN

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

120 END IF;

121 IF NEW."discussion_time" ISNULL THEN

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

123 END IF;

124 IF NEW."verification_time" ISNULL THEN

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

126 END IF;

127 IF NEW."voting_time" ISNULL THEN

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

129 END IF;

130 RETURN NEW;

131 END;

132 $$;

133

134 CREATE OR REPLACE FUNCTION "check_issue"

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

136 "persist" "check_issue_persistence" )

137 RETURNS "check_issue_persistence"

138 LANGUAGE 'plpgsql' VOLATILE AS $$

139 DECLARE

140 "issue_row" "issue"%ROWTYPE;

141 "policy_row" "policy"%ROWTYPE;

142 "initiative_row" "initiative"%ROWTYPE;

143 "state_v" "issue_state";

144 BEGIN

145 PERFORM "require_transaction_isolation"();

146 IF "persist" ISNULL THEN

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

148 FOR UPDATE;

149 IF "issue_row"."closed" NOTNULL THEN

150 RETURN NULL;

151 END IF;

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

153 IF

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

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

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

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

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

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

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

161 "issue_row"."fully_frozen" + "issue_row"."voting_time" )

162 THEN

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

164 ELSE

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

166 END IF;

167 IF

168 NOT EXISTS (

169 -- all initiatives are revoked

170 SELECT NULL FROM "initiative"

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

172 ) AND (

173 -- and issue has not been accepted yet

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

175 -- or verification time has elapsed

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

177 "persist"."phase_finished" ) OR

178 -- or no initiatives have been revoked lately

179 NOT EXISTS (

180 SELECT NULL FROM "initiative"

181 WHERE "issue_id" = "issue_id_p"

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

183 )

184 )

185 THEN

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

187 ELSE

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

189 END IF;

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

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

192 WHERE "id" = "issue_row"."id";

193 RETURN "persist";

194 ELSIF

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

196 THEN

197 RETURN "persist";

198 ELSE

199 RETURN NULL;

200 END IF;

201 END IF;

202 IF

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

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

205 THEN

206 PERFORM "create_snapshot"("issue_id_p");

207 "persist"."snapshot_created" = TRUE;

208 IF "persist"."phase_finished" THEN

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

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

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

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

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

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

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

216 SELECT * INTO "policy_row" FROM "policy"

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

218 FOR "initiative_row" IN

219 SELECT * FROM "initiative"

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

221 FOR UPDATE

222 LOOP

223 IF

224 "initiative_row"."polling" OR (

225 "initiative_row"."satisfied_supporter_count" > 0 AND

226 "initiative_row"."satisfied_supporter_count" *

227 "policy_row"."initiative_quorum_den" >=

228 "issue_row"."population" * "policy_row"."initiative_quorum_num"

229 )

230 THEN

231 UPDATE "initiative" SET "admitted" = TRUE

232 WHERE "id" = "initiative_row"."id";

233 ELSE

234 UPDATE "initiative" SET "admitted" = FALSE

235 WHERE "id" = "initiative_row"."id";

236 END IF;

237 END LOOP;

238 END IF;

239 END IF;

240 RETURN "persist";

241 END IF;

242 IF

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

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

245 THEN

246 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

247 "persist"."harmonic_weights_set" = TRUE;

248 IF

249 "persist"."phase_finished" OR

250 "persist"."issue_revoked" OR

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

252 THEN

253 RETURN "persist";

254 ELSE

255 RETURN NULL;

256 END IF;

257 END IF;

258 IF "persist"."issue_revoked" THEN

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

260 "state_v" := 'canceled_revoked_before_accepted';

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

262 "state_v" := 'canceled_after_revocation_during_discussion';

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

264 "state_v" := 'canceled_after_revocation_during_verification';

265 END IF;

266 UPDATE "issue" SET

267 "state" = "state_v",

268 "closed" = "phase_finished",

269 "phase_finished" = NULL

270 WHERE "id" = "issue_id_p";

271 RETURN NULL;

272 END IF;

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

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

275 FOR UPDATE;

276 SELECT * INTO "policy_row"

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

278 IF

279 ( now() >=

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

281 EXISTS (

282 SELECT NULL FROM "initiative"

283 WHERE "issue_id" = "issue_id_p"

284 AND "supporter_count" > 0

285 AND "supporter_count" * "policy_row"."issue_quorum_den"

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

287 )

288 THEN

289 UPDATE "issue" SET

290 "state" = 'discussion',

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

292 "phase_finished" = NULL

293 WHERE "id" = "issue_id_p";

294 ELSIF "issue_row"."phase_finished" NOTNULL THEN

295 UPDATE "issue" SET

296 "state" = 'canceled_issue_not_accepted',

297 "closed" = "phase_finished",

298 "phase_finished" = NULL

299 WHERE "id" = "issue_id_p";

300 END IF;

301 RETURN NULL;

302 END IF;

303 IF "persist"."phase_finished" THEN

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

305 UPDATE "issue" SET

306 "state" = 'verification',

307 "half_frozen" = "phase_finished",

308 "phase_finished" = NULL

309 WHERE "id" = "issue_id_p";

310 RETURN NULL;

311 END IF;

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

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

314 FOR UPDATE;

315 SELECT * INTO "policy_row" FROM "policy"

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

317 IF EXISTS (

318 SELECT NULL FROM "initiative"

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

320 ) THEN

321 UPDATE "issue" SET

322 "state" = 'voting',

323 "fully_frozen" = "phase_finished",

324 "phase_finished" = NULL

325 WHERE "id" = "issue_id_p";

326 ELSE

327 UPDATE "issue" SET

328 "state" = 'canceled_no_initiative_admitted',

329 "fully_frozen" = "phase_finished",

330 "closed" = "phase_finished",

331 "phase_finished" = NULL

332 WHERE "id" = "issue_id_p";

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

334 -- issue state has been manipulated

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

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

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

338 END IF;

339 RETURN NULL;

340 END IF;

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

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

343 PERFORM "close_voting"("issue_id_p");

344 "persist"."closed_voting" = TRUE;

345 RETURN "persist";

346 END IF;

347 PERFORM "calculate_ranks"("issue_id_p");

348 RETURN NULL;

349 END IF;

350 END IF;

351 RAISE WARNING 'should not happen';

352 RETURN NULL;

353 END;

354 $$;

355

356 COMMIT;