liquid_feedback_core: e0b8175fda29 update/core-update.v4.1.0-v4.2.0.sql

liquid_feedback_core

view update/core-update.v4.1.0-v4.2.0.sql @ 620:e0b8175fda29

Added TODO notice regarding indices on "posting"

author	jbe
date	Sat Dec 05 18:48:33 2020 +0100 (2020-12-05)
parents	63092784fe9d
children

line source

1 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

2 SELECT * FROM (VALUES ('4.2.0-incomplete-update', 4, 2, -1))

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

5 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'posting_created';

7 BEGIN;

9 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

10 SELECT * FROM (VALUES ('4.2.0', 4, 2, 0))

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

13 DROP VIEW "newsletter_to_send";

14 DROP VIEW "scheduled_notification_to_send";

15 DROP VIEW "member_contingent_left";

16 DROP VIEW "member_contingent";

17 DROP VIEW "expired_snapshot";

18 DROP VIEW "current_draft";

19 DROP VIEW "opening_draft";

20 DROP VIEW "area_with_unaccepted_issues";

21 DROP VIEW "member_to_notify";

22 DROP VIEW "member_eligible_to_be_notified";

24 CREATE EXTENSION IF NOT EXISTS btree_gist;

25 CREATE EXTENSION IF NOT EXISTS conflux VERSION "0.5";

27 DROP FUNCTION "text_search_query" (TEXT);

29 ALTER TABLE "system_setting" DROP COLUMN "snapshot_retention";

31 CREATE TABLE "file" (

32 "id" SERIAL8 PRIMARY KEY,

33 UNIQUE ("content_type", "hash"),

34 "content_type" TEXT NOT NULL,

35 "hash" TEXT NOT NULL,

36 "data" BYTEA NOT NULL,

37 "preview_content_type" TEXT,

38 "preview_data" BYTEA );

40 COMMENT ON TABLE "file" IS 'Table holding file contents for draft attachments';

42 COMMENT ON COLUMN "file"."content_type" IS 'Content type of "data"';

43 COMMENT ON COLUMN "file"."hash" IS 'Hash of "data" to avoid storing duplicates where content-type and data is identical';

44 COMMENT ON COLUMN "file"."data" IS 'Binary content';

45 COMMENT ON COLUMN "file"."preview_content_type" IS 'Content type of "preview_data"';

46 COMMENT ON COLUMN "file"."preview_data" IS 'Preview (e.g. preview image)';

48 ALTER TABLE "member" DROP COLUMN "text_search_data";

49 DROP TRIGGER "update_text_search_data" ON "member";

51 CREATE INDEX "member_useterms_member_id_contract_identifier" ON "member_useterms" ("member_id", "contract_identifier");

53 ALTER TABLE "member_profile" DROP COLUMN "text_search_data";

54 DROP TRIGGER "update_text_search_data" ON "member_profile";

56 ALTER TABLE "contact" ADD COLUMN "following" BOOLEAN NOT NULL DEFAULT TRUE;

58 COMMENT ON COLUMN "contact"."following" IS 'TRUE = actions of contact are shown in personal timeline';

60 ALTER TABLE "unit" DROP COLUMN "text_search_data";

61 DROP TRIGGER "update_text_search_data" ON "unit";

63 ALTER TABLE "area" DROP COLUMN "text_search_data";

64 DROP TRIGGER "update_text_search_data" ON "area";

66 DROP INDEX "issue_accepted_idx";

67 DROP INDEX "issue_half_frozen_idx";

68 DROP INDEX "issue_fully_frozen_idx";

69 ALTER INDEX "issue_created_idx_open" RENAME TO "issue_open_created_idx";

70 DROP INDEX "issue_closed_idx_canceled";

71 ALTER INDEX "issue_latest_snapshot_id" RENAME TO "issue_latest_snapshot_id_idx";

72 ALTER INDEX "issue_admission_snapshot_id" RENAME TO "issue_admission_snapshot_id_idx";

73 ALTER INDEX "issue_half_freeze_snapshot_id" RENAME TO "issue_half_freeze_snapshot_id_idx";

74 ALTER INDEX "issue_full_freeze_snapshot_id" RENAME TO "issue_full_freeze_snapshot_id_idx";

76 ALTER TABLE "initiative" ADD COLUMN "content" TEXT;

77 ALTER TABLE "initiative" DROP COLUMN "text_search_data";

78 ALTER TABLE "initiative" DROP COLUMN "draft_text_search_data";

79 DROP INDEX "initiative_revoked_idx";

80 DROP TRIGGER "update_text_search_data" ON "initiative";

82 COMMENT ON COLUMN "initiative"."content" IS 'Initiative text (automatically copied from most recent draft)';

84 ALTER TABLE "battle" DROP CONSTRAINT "initiative_ids_not_equal";

85 ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK (

86 "winning_initiative_id" != "losing_initiative_id" AND

87 ("winning_initiative_id" NOTNULL OR "losing_initiative_id" NOTNULL) );

89 ALTER TABLE "draft" DROP COLUMN "text_search_data";

90 DROP TRIGGER "update_text_search_data" ON "draft";

92 CREATE TABLE "draft_attachment" (

93 "id" SERIAL8 PRIMARY KEY,

94 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

95 "file_id" INT8 REFERENCES "file" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,

96 "title" TEXT,

97 "description" TEXT );

99 COMMENT ON TABLE "draft_attachment" IS 'Binary attachments for a draft (images, PDF file, etc.); Implicitly ordered through ''id'' column';

100

101 ALTER TABLE "suggestion" DROP COLUMN "text_search_data";

102 DROP TRIGGER "update_text_search_data" ON "suggestion";

103

104 ALTER TABLE "direct_voter" DROP COLUMN "text_search_data";

105 DROP TRIGGER "update_text_search_data" ON "direct_voter";

106

107 CREATE TABLE "posting" (

108 UNIQUE ("author_id", "id"), -- index needed for foreign-key on table "posting_lexeme"

109 "id" SERIAL8 PRIMARY KEY,

110 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,

111 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),

112 "message" TEXT NOT NULL,

113 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

114 "area_id" INT4,

115 FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,

116 "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

117 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

118 FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,

119 FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,

120 "initiative_id" INT4,

121 "suggestion_id" INT8,

122 -- NOTE: no referential integrity for suggestions because those are

123 -- actually deleted

124 -- FOREIGN KEY ("initiative_id", "suggestion_id")

125 -- REFERENCES "suggestion" ("initiative_id", "id")

126 -- ON DELETE CASCADE ON UPDATE CASCADE,

127 CONSTRAINT "area_requires_unit" CHECK (

128 "area_id" ISNULL OR "unit_id" NOTNULL ),

129 CONSTRAINT "policy_set_when_issue_set" CHECK (

130 ("policy_id" NOTNULL) = ("issue_id" NOTNULL) ),

131 CONSTRAINT "issue_requires_area" CHECK (

132 "issue_id" ISNULL OR "area_id" NOTNULL ),

133 CONSTRAINT "initiative_requires_issue" CHECK (

134 "initiative_id" ISNULL OR "issue_id" NOTNULL ),

135 CONSTRAINT "suggestion_requires_initiative" CHECK (

136 "suggestion_id" ISNULL OR "initiative_id" NOTNULL ) );

137 CREATE INDEX "posting_global_idx" ON "posting" USING gist ((pstamp("author_id", "id")));

138 CREATE INDEX "posting_unit_idx" ON "posting" USING gist ("unit_id", (pstamp("author_id", "id"))) WHERE "unit_id" NOTNULL;

139 CREATE INDEX "posting_area_idx" ON "posting" USING gist ("area_id", (pstamp("author_id", "id"))) WHERE "area_id" NOTNULL;

140 CREATE INDEX "posting_policy_idx" ON "posting" USING gist ("policy_id", (pstamp("author_id", "id"))) WHERE "policy_id" NOTNULL;

141 CREATE INDEX "posting_issue_idx" ON "posting" USING gist ("issue_id", (pstamp("author_id", "id"))) WHERE "issue_id" NOTNULL;

142 CREATE INDEX "posting_initiative_idx" ON "posting" USING gist ("initiative_id", (pstamp("author_id", "id"))) WHERE "initiative_id" NOTNULL;

143 CREATE INDEX "posting_suggestion_idx" ON "posting" USING gist ("suggestion_id", (pstamp("author_id", "id"))) WHERE "suggestion_id" NOTNULL;

144 COMMENT ON TABLE "posting" IS 'Text postings of members; a text posting may optionally be associated to a unit, area, policy, issue, initiative, or suggestion';

145

146 CREATE TABLE "posting_lexeme" (

147 PRIMARY KEY ("posting_id", "lexeme"),

148 FOREIGN KEY ("posting_id", "author_id") REFERENCES "posting" ("id", "author_id") ON DELETE CASCADE ON UPDATE CASCADE,

149 "posting_id" INT8,

150 "lexeme" TEXT,

151 "author_id" INT4 );

152 CREATE INDEX "posting_lexeme_idx" ON "posting_lexeme" USING gist ("lexeme", (pstamp("author_id", "posting_id")));

153

154 COMMENT ON TABLE "posting_lexeme" IS 'Helper table to allow searches for hashtags.';

155

156 ALTER TABLE "event" ADD COLUMN "posting_id" INT8 REFERENCES "posting" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;

157 ALTER TABLE "event" DROP CONSTRAINT "constr_for_issue_state_changed";

158 ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft";

159 ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_creation";

160 ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_removal";

161 ALTER TABLE "event" DROP CONSTRAINT "constr_for_value_less_member_event";

162 ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_active";

163 ALTER TABLE "event" DROP CONSTRAINT "constr_for_member_name_updated";

164 ALTER TABLE "event" DROP CONSTRAINT "constr_for_interest";

165 ALTER TABLE "event" DROP CONSTRAINT "constr_for_initiator";

166 ALTER TABLE "event" DROP CONSTRAINT "constr_for_support";

167 ALTER TABLE "event" DROP CONSTRAINT "constr_for_support_updated";

168 ALTER TABLE "event" DROP CONSTRAINT "constr_for_suggestion_rated";

169 ALTER TABLE "event" DROP CONSTRAINT "constr_for_delegation";

170 ALTER TABLE "event" DROP CONSTRAINT "constr_for_contact";

171 ALTER TABLE "event" ADD

172 CONSTRAINT "constr_for_issue_state_changed" CHECK (

173 "event" != 'issue_state_changed' OR (

174 "posting_id" ISNULL AND

175 "member_id" ISNULL AND

176 "other_member_id" ISNULL AND

177 "scope" ISNULL AND

178 "unit_id" NOTNULL AND

179 "area_id" NOTNULL AND

180 "policy_id" NOTNULL AND

181 "issue_id" NOTNULL AND

182 "state" NOTNULL AND

183 "initiative_id" ISNULL AND

184 "draft_id" ISNULL AND

185 "suggestion_id" ISNULL AND

186 "boolean_value" ISNULL AND

187 "numeric_value" ISNULL AND

188 "text_value" ISNULL AND

189 "old_text_value" ISNULL ));

190 ALTER TABLE "event" ADD

191 CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (

192 "event" NOT IN (

193 'initiative_created_in_new_issue',

194 'initiative_created_in_existing_issue',

195 'initiative_revoked',

196 'new_draft_created'

197 ) OR (

198 "posting_id" ISNULL AND

199 "member_id" NOTNULL AND

200 "other_member_id" ISNULL AND

201 "scope" ISNULL AND

202 "unit_id" NOTNULL AND

203 "area_id" NOTNULL AND

204 "policy_id" NOTNULL AND

205 "issue_id" NOTNULL AND

206 "state" NOTNULL AND

207 "initiative_id" NOTNULL AND

208 "draft_id" NOTNULL AND

209 "suggestion_id" ISNULL AND

210 "boolean_value" ISNULL AND

211 "numeric_value" ISNULL AND

212 "text_value" ISNULL AND

213 "old_text_value" ISNULL ));

214 ALTER TABLE "event" ADD

215 CONSTRAINT "constr_for_suggestion_creation" CHECK (

216 "event" != 'suggestion_created' OR (

217 "posting_id" ISNULL AND

218 "member_id" NOTNULL AND

219 "other_member_id" ISNULL AND

220 "scope" ISNULL AND

221 "unit_id" NOTNULL AND

222 "area_id" NOTNULL AND

223 "policy_id" NOTNULL AND

224 "issue_id" NOTNULL AND

225 "state" NOTNULL AND

226 "initiative_id" NOTNULL AND

227 "draft_id" ISNULL AND

228 "suggestion_id" NOTNULL AND

229 "boolean_value" ISNULL AND

230 "numeric_value" ISNULL AND

231 "text_value" ISNULL AND

232 "old_text_value" ISNULL ));

233 ALTER TABLE "event" ADD

234 CONSTRAINT "constr_for_suggestion_removal" CHECK (

235 "event" != 'suggestion_deleted' OR (

236 "posting_id" ISNULL AND

237 "member_id" ISNULL AND

238 "other_member_id" ISNULL AND

239 "scope" ISNULL AND

240 "unit_id" NOTNULL AND

241 "area_id" NOTNULL AND

242 "policy_id" NOTNULL AND

243 "issue_id" NOTNULL AND

244 "state" NOTNULL AND

245 "initiative_id" NOTNULL AND

246 "draft_id" ISNULL AND

247 "suggestion_id" NOTNULL AND

248 "boolean_value" ISNULL AND

249 "numeric_value" ISNULL AND

250 "text_value" ISNULL AND

251 "old_text_value" ISNULL ));

252 ALTER TABLE "event" ADD

253 CONSTRAINT "constr_for_value_less_member_event" CHECK (

254 "event" NOT IN (

255 'member_activated',

256 'member_deleted',

257 'member_profile_updated',

258 'member_image_updated'

259 ) OR (

260 "posting_id" ISNULL AND

261 "member_id" NOTNULL AND

262 "other_member_id" ISNULL AND

263 "scope" ISNULL AND

264 "unit_id" ISNULL AND

265 "area_id" ISNULL AND

266 "policy_id" ISNULL AND

267 "issue_id" ISNULL AND

268 "state" ISNULL AND

269 "initiative_id" ISNULL AND

270 "draft_id" ISNULL AND

271 "suggestion_id" ISNULL AND

272 "boolean_value" ISNULL AND

273 "numeric_value" ISNULL AND

274 "text_value" ISNULL AND

275 "old_text_value" ISNULL ));

276 ALTER TABLE "event" ADD

277 CONSTRAINT "constr_for_member_active" CHECK (

278 "event" != 'member_active' OR (

279 "posting_id" ISNULL AND

280 "member_id" NOTNULL AND

281 "other_member_id" ISNULL AND

282 "scope" ISNULL AND

283 "unit_id" ISNULL AND

284 "area_id" ISNULL AND

285 "policy_id" ISNULL AND

286 "issue_id" ISNULL AND

287 "state" ISNULL AND

288 "initiative_id" ISNULL AND

289 "draft_id" ISNULL AND

290 "suggestion_id" ISNULL AND

291 "boolean_value" NOTNULL AND

292 "numeric_value" ISNULL AND

293 "text_value" ISNULL AND

294 "old_text_value" ISNULL ));

295 ALTER TABLE "event" ADD

296 CONSTRAINT "constr_for_member_name_updated" CHECK (

297 "event" != 'member_name_updated' OR (

298 "posting_id" ISNULL AND

299 "member_id" NOTNULL AND

300 "other_member_id" ISNULL AND

301 "scope" ISNULL AND

302 "unit_id" ISNULL AND

303 "area_id" ISNULL AND

304 "policy_id" ISNULL AND

305 "issue_id" ISNULL AND

306 "state" ISNULL AND

307 "initiative_id" ISNULL AND

308 "draft_id" ISNULL AND

309 "suggestion_id" ISNULL AND

310 "boolean_value" ISNULL AND

311 "numeric_value" ISNULL AND

312 "text_value" NOTNULL AND

313 "old_text_value" NOTNULL ));

314 ALTER TABLE "event" ADD

315 CONSTRAINT "constr_for_interest" CHECK (

316 "event" != 'interest' OR (

317 "posting_id" ISNULL AND

318 "member_id" NOTNULL AND

319 "other_member_id" ISNULL AND

320 "scope" ISNULL AND

321 "unit_id" NOTNULL AND

322 "area_id" NOTNULL AND

323 "policy_id" NOTNULL AND

324 "issue_id" NOTNULL AND

325 "state" NOTNULL AND

326 "initiative_id" ISNULL AND

327 "draft_id" ISNULL AND

328 "suggestion_id" ISNULL AND

329 "boolean_value" NOTNULL AND

330 "numeric_value" ISNULL AND

331 "text_value" ISNULL AND

332 "old_text_value" ISNULL ));

333 ALTER TABLE "event" ADD

334 CONSTRAINT "constr_for_initiator" CHECK (

335 "event" != 'initiator' OR (

336 "posting_id" ISNULL AND

337 "member_id" NOTNULL AND

338 "other_member_id" ISNULL AND

339 "scope" ISNULL AND

340 "unit_id" NOTNULL AND

341 "area_id" NOTNULL AND

342 "policy_id" NOTNULL AND

343 "issue_id" NOTNULL AND

344 "state" NOTNULL AND

345 "initiative_id" NOTNULL AND

346 "draft_id" ISNULL AND

347 "suggestion_id" ISNULL AND

348 "boolean_value" NOTNULL AND

349 "numeric_value" ISNULL AND

350 "text_value" ISNULL AND

351 "old_text_value" ISNULL ));

352 ALTER TABLE "event" ADD

353 CONSTRAINT "constr_for_support" CHECK (

354 "event" != 'support' OR (

355 "posting_id" ISNULL AND

356 "member_id" NOTNULL AND

357 "other_member_id" ISNULL AND

358 "scope" ISNULL AND

359 "unit_id" NOTNULL AND

360 "area_id" NOTNULL AND

361 "policy_id" NOTNULL AND

362 "issue_id" NOTNULL AND

363 "state" NOTNULL AND

364 "initiative_id" NOTNULL AND

365 ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND

366 "suggestion_id" ISNULL AND

367 "boolean_value" NOTNULL AND

368 "numeric_value" ISNULL AND

369 "text_value" ISNULL AND

370 "old_text_value" ISNULL ));

371 ALTER TABLE "event" ADD

372 CONSTRAINT "constr_for_support_updated" CHECK (

373 "event" != 'support_updated' OR (

374 "posting_id" ISNULL AND

375 "member_id" NOTNULL AND

376 "other_member_id" ISNULL AND

377 "scope" ISNULL AND

378 "unit_id" NOTNULL AND

379 "area_id" NOTNULL AND

380 "policy_id" NOTNULL AND

381 "issue_id" NOTNULL AND

382 "state" NOTNULL AND

383 "initiative_id" NOTNULL AND

384 "draft_id" NOTNULL AND

385 "suggestion_id" ISNULL AND

386 "boolean_value" ISNULL AND

387 "numeric_value" ISNULL AND

388 "text_value" ISNULL AND

389 "old_text_value" ISNULL ));

390 ALTER TABLE "event" ADD

391 CONSTRAINT "constr_for_suggestion_rated" CHECK (

392 "event" != 'suggestion_rated' OR (

393 "posting_id" ISNULL AND

394 "member_id" NOTNULL AND

395 "other_member_id" ISNULL AND

396 "scope" ISNULL AND

397 "unit_id" NOTNULL AND

398 "area_id" NOTNULL AND

399 "policy_id" NOTNULL AND

400 "issue_id" NOTNULL AND

401 "state" NOTNULL AND

402 "initiative_id" NOTNULL AND

403 "draft_id" ISNULL AND

404 "suggestion_id" NOTNULL AND

405 ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND

406 "numeric_value" NOTNULL AND

407 "numeric_value" IN (-2, -1, 0, 1, 2) AND

408 "text_value" ISNULL AND

409 "old_text_value" ISNULL ));

410 ALTER TABLE "event" ADD

411 CONSTRAINT "constr_for_delegation" CHECK (

412 "event" != 'delegation' OR (

413 "posting_id" ISNULL AND

414 "member_id" NOTNULL AND

415 (("other_member_id" ISNULL) OR ("boolean_value" = TRUE)) AND

416 "scope" NOTNULL AND

417 "unit_id" NOTNULL AND

418 ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND

419 "policy_id" ISNULL AND

420 ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND

421 ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND

422 "initiative_id" ISNULL AND

423 "draft_id" ISNULL AND

424 "suggestion_id" ISNULL AND

425 "boolean_value" NOTNULL AND

426 "numeric_value" ISNULL AND

427 "text_value" ISNULL AND

428 "old_text_value" ISNULL ));

429 ALTER TABLE "event" ADD

430 CONSTRAINT "constr_for_contact" CHECK (

431 "event" != 'contact' OR (

432 "posting_id" ISNULL AND

433 "member_id" NOTNULL AND

434 "other_member_id" NOTNULL AND

435 "scope" ISNULL AND

436 "unit_id" ISNULL AND

437 "area_id" ISNULL AND

438 "policy_id" ISNULL AND

439 "issue_id" ISNULL AND

440 "state" ISNULL AND

441 "initiative_id" ISNULL AND

442 "draft_id" ISNULL AND

443 "suggestion_id" ISNULL AND

444 "boolean_value" NOTNULL AND

445 "numeric_value" ISNULL AND

446 "text_value" ISNULL AND

447 "old_text_value" ISNULL ));

448 ALTER TABLE "event" ADD

449 CONSTRAINT "constr_for_posting_created" CHECK (

450 "event" != 'posting_created' OR (

451 "posting_id" NOTNULL AND

452 "member_id" NOTNULL AND

453 "other_member_id" ISNULL AND

454 "scope" ISNULL AND

455 "state" ISNULL AND

456 ("area_id" ISNULL OR "unit_id" NOTNULL) AND

457 ("policy_id" NOTNULL) = ("issue_id" NOTNULL) AND

458 ("issue_id" ISNULL OR "area_id" NOTNULL) AND

459 ("state" NOTNULL) = ("issue_id" NOTNULL) AND

460 ("initiative_id" ISNULL OR "issue_id" NOTNULL) AND

461 "draft_id" ISNULL AND

462 ("suggestion_id" ISNULL OR "initiative_id" NOTNULL) AND

463 "boolean_value" ISNULL AND

464 "numeric_value" ISNULL AND

465 "text_value" ISNULL AND

466 "old_text_value" ISNULL ));

467

468 CREATE INDEX "event_tl_global_idx" ON "event" USING gist ((pstamp("member_id", "id")));

469 CREATE INDEX "event_tl_unit_idx" ON "event" USING gist ("unit_id", (pstamp("member_id", "id"))) WHERE "unit_id" NOTNULL;

470 CREATE INDEX "event_tl_area_idx" ON "event" USING gist ("area_id", (pstamp("member_id", "id"))) WHERE "area_id" NOTNULL;

471 CREATE INDEX "event_tl_policy_idx" ON "event" USING gist ("policy_id", (pstamp("member_id", "id"))) WHERE "policy_id" NOTNULL;

472 CREATE INDEX "event_tl_issue_idx" ON "event" USING gist ("issue_id", (pstamp("member_id", "id"))) WHERE "issue_id" NOTNULL;

473 CREATE INDEX "event_tl_initiative_idx" ON "event" USING gist ("initiative_id", (pstamp("member_id", "id"))) WHERE "initiative_id" NOTNULL;

474 CREATE INDEX "event_tl_suggestion_idx" ON "event" USING gist ("suggestion_id", (pstamp("member_id", "id"))) WHERE "suggestion_id" NOTNULL;

475

476 CREATE OR REPLACE FUNCTION "highlight"

477 ( "body_p" TEXT,

478 "query_text_p" TEXT )

479 RETURNS TEXT

480 LANGUAGE 'plpgsql' IMMUTABLE AS $$

481 BEGIN

482 RETURN ts_headline(

483 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),

484 "plainto_tsquery"("query_text_p"),

485 'StartSel=* StopSel=* HighlightAll=TRUE' );

486 END;

487 $$;

488

489 CREATE FUNCTION "to_tsvector"("member") RETURNS TSVECTOR

490 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

491 $1."name",

492 $1."identification"

493 )) $$;

494 CREATE INDEX "member_to_tsvector_idx" ON "member" USING gin

495 (("to_tsvector"("member".*)));

496

497 CREATE FUNCTION "to_tsvector"("member_profile") RETURNS TSVECTOR

498 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

499 $1."statement",

500 $1."profile_text_data"

501 )) $$;

502 CREATE INDEX "member_profile_to_tsvector_idx" ON "member_profile" USING gin

503 (("to_tsvector"("member_profile".*)));

504

505 CREATE FUNCTION "to_tsvector"("unit") RETURNS TSVECTOR

506 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

507 $1."name",

508 $1."description"

509 )) $$;

510 CREATE INDEX "unit_to_tsvector_idx" ON "unit" USING gin

511 (("to_tsvector"("unit".*)));

512

513 CREATE FUNCTION "to_tsvector"("area") RETURNS TSVECTOR

514 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

515 $1."name",

516 $1."description"

517 )) $$;

518 CREATE INDEX "area_to_tsvector_idx" ON "area" USING gin

519 (("to_tsvector"("area".*)));

520

521 CREATE FUNCTION "to_tsvector"("initiative") RETURNS TSVECTOR

522 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

523 $1."name",

524 $1."content"

525 )) $$;

526 CREATE INDEX "initiative_to_tsvector_idx" ON "initiative" USING gin

527 (("to_tsvector"("initiative".*)));

528

529 CREATE FUNCTION "to_tsvector"("draft") RETURNS TSVECTOR

530 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

531 $1."content"

532 )) $$;

533 CREATE INDEX "draft_to_tsvector_idx" ON "draft" USING gin

534 (("to_tsvector"("draft".*)));

535

536 CREATE FUNCTION "to_tsvector"("suggestion") RETURNS TSVECTOR

537 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

538 $1."name",

539 $1."content"

540 )) $$;

541 CREATE INDEX "suggestion_to_tsvector_idx" ON "suggestion" USING gin

542 (("to_tsvector"("suggestion".*)));

543

544 CREATE FUNCTION "to_tsvector"("direct_voter") RETURNS TSVECTOR

545 LANGUAGE SQL IMMUTABLE AS $$ SELECT to_tsvector(concat_ws(' ',

546 $1."comment"

547 )) $$;

548 CREATE INDEX "direct_voter_to_tsvector_idx" ON "direct_voter" USING gin

549 (("to_tsvector"("direct_voter".*)));

550

551 CREATE FUNCTION "update_posting_lexeme_trigger"()

552 RETURNS TRIGGER

553 LANGUAGE 'plpgsql' VOLATILE AS $$

554 DECLARE

555 "lexeme_v" TEXT;

556 BEGIN

557 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN

558 DELETE FROM "posting_lexeme" WHERE "posting_id" = OLD."id";

559 END IF;

560 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN

561 FOR "lexeme_v" IN

562 SELECT regexp_matches[1]

563 FROM regexp_matches(NEW."message", '#[^\s.,;:]+')

564 LOOP

565 INSERT INTO "posting_lexeme" ("posting_id", "author_id", "lexeme")

566 VALUES (

567 NEW."id",

568 NEW."author_id",

569 "lexeme_v" )

570 ON CONFLICT ("posting_id", "lexeme") DO NOTHING;

571 END LOOP;

572 END IF;

573 RETURN NULL;

574 END;

575 $$;

576

577 CREATE TRIGGER "update_posting_lexeme"

578 AFTER INSERT OR UPDATE OR DELETE ON "posting"

579 FOR EACH ROW EXECUTE PROCEDURE "update_posting_lexeme_trigger"();

580

581 COMMENT ON FUNCTION "update_posting_lexeme_trigger"() IS 'Implementation of trigger "update_posting_lexeme" on table "posting"';

582 COMMENT ON TRIGGER "update_posting_lexeme" ON "posting" IS 'Keeps table "posting_lexeme" up to date';

583

584 CREATE FUNCTION "write_event_posting_trigger"()

585 RETURNS TRIGGER

586 LANGUAGE 'plpgsql' VOLATILE AS $$

587 BEGIN

588 INSERT INTO "event" (

589 "event", "posting_id", "member_id",

590 "unit_id", "area_id", "policy_id",

591 "issue_id", "initiative_id", "suggestion_id"

592 ) VALUES (

593 'posting_created', NEW."id", NEW."author_id",

594 NEW."unit_id", NEW."area_id", NEW."policy_id",

595 NEW."issue_id", NEW."initiative_id", NEW."suggestion_id"

596 );

597 RETURN NULL;

598 END;

599 $$;

600

601 CREATE TRIGGER "write_event_posting"

602 AFTER INSERT ON "posting" FOR EACH ROW EXECUTE PROCEDURE

603 "write_event_posting_trigger"();

604

605 COMMENT ON FUNCTION "write_event_posting_trigger"() IS 'Implementation of trigger "write_event_posting" on table "posting"';

606 COMMENT ON TRIGGER "write_event_posting" ON "posting" IS 'Create entry in "event" table when creating a new posting';

607

608 CREATE FUNCTION "file_requires_reference_trigger"()

609 RETURNS TRIGGER

610 LANGUAGE 'plpgsql' VOLATILE AS $$

611 BEGIN

612 IF NOT EXISTS (

613 SELECT NULL FROM "draft_attachment" WHERE "file_id" = NEW."id"

614 ) THEN

615 RAISE EXCEPTION 'Cannot create an unreferenced file.' USING

616 ERRCODE = 'integrity_constraint_violation',

617 HINT = 'Create file and its reference in another table within the same transaction.';

618 END IF;

619 RETURN NULL;

620 END;

621 $$;

622

623 CREATE CONSTRAINT TRIGGER "file_requires_reference"

624 AFTER INSERT OR UPDATE ON "file" DEFERRABLE INITIALLY DEFERRED

625 FOR EACH ROW EXECUTE PROCEDURE

626 "file_requires_reference_trigger"();

627

628 COMMENT ON FUNCTION "file_requires_reference_trigger"() IS 'Implementation of trigger "file_requires_reference" on table "file"';

629 COMMENT ON TRIGGER "file_requires_reference" ON "file" IS 'Ensure that files are always referenced';

630

631 CREATE FUNCTION "last_reference_deletes_file_trigger"()

632 RETURNS TRIGGER

633 LANGUAGE 'plpgsql' VOLATILE AS $$

634 DECLARE

635 "reference_lost" BOOLEAN;

636 BEGIN

637 IF TG_OP = 'DELETE' THEN

638 "reference_lost" := TRUE;

639 ELSE

640 "reference_lost" := NEW."file_id" != OLD."file_id";

641 END IF;

642 IF

643 "reference_lost" AND NOT EXISTS (

644 SELECT NULL FROM "draft_attachment" WHERE "file_id" = OLD."file_id"

645 )

646 THEN

647 DELETE FROM "file" WHERE "id" = OLD."file_id";

648 END IF;

649 RETURN NULL;

650 END;

651 $$;

652

653 CREATE CONSTRAINT TRIGGER "last_reference_deletes_file"

654 AFTER UPDATE OR DELETE ON "draft_attachment" DEFERRABLE INITIALLY DEFERRED

655 FOR EACH ROW EXECUTE PROCEDURE

656 "last_reference_deletes_file_trigger"();

657

658 COMMENT ON FUNCTION "last_reference_deletes_file_trigger"() IS 'Implementation of trigger "last_reference_deletes_file" on table "draft_attachment"';

659 COMMENT ON TRIGGER "last_reference_deletes_file" ON "draft_attachment" IS 'Removing the last reference to a file deletes the file';

660

661 CREATE OR REPLACE FUNCTION "copy_current_draft_data"

662 ("initiative_id_p" "initiative"."id"%TYPE )

663 RETURNS VOID

664 LANGUAGE 'plpgsql' VOLATILE AS $$

665 BEGIN

666 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"

667 FOR UPDATE;

668 UPDATE "initiative" SET

669 "location" = "draft"."location",

670 "content" = "draft"."content"

671 FROM "current_draft" AS "draft"

672 WHERE "initiative"."id" = "initiative_id_p"

673 AND "draft"."initiative_id" = "initiative_id_p";

674 END;

675 $$;

676

677 CREATE VIEW "follower" AS

678 SELECT

679 "id" AS "follower_id",

680 ( SELECT ARRAY["member"."id"] || array_agg("contact"."other_member_id")

681 FROM "contact"

682 WHERE "contact"."member_id" = "member"."id" AND "contact"."following" )

683 AS "following_ids"

684 FROM "member";

685

686 COMMENT ON VIEW "follower" IS 'Provides the contacts of each member that are being followed (including the member itself) as an array of IDs';

687

688 CREATE OR REPLACE FUNCTION "check_issue"

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

690 "persist" "check_issue_persistence" )

691 RETURNS "check_issue_persistence"

692 LANGUAGE 'plpgsql' VOLATILE AS $$

693 DECLARE

694 "issue_row" "issue"%ROWTYPE;

695 "last_calculated_v" "snapshot"."calculated"%TYPE;

696 "policy_row" "policy"%ROWTYPE;

697 "initiative_row" "initiative"%ROWTYPE;

698 "state_v" "issue_state";

699 BEGIN

700 PERFORM "require_transaction_isolation"();

701 IF "persist" ISNULL THEN

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

703 FOR UPDATE;

704 SELECT "calculated" INTO "last_calculated_v"

705 FROM "snapshot" JOIN "snapshot_issue"

706 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"

707 WHERE "snapshot_issue"."issue_id" = "issue_id_p"

708 ORDER BY "snapshot"."id" DESC;

709 IF "issue_row"."closed" NOTNULL THEN

710 RETURN NULL;

711 END IF;

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

713 IF

714 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=

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

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

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

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

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

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

721 "issue_row"."fully_frozen" + "issue_row"."voting_time" )

722 THEN

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

724 ELSE

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

726 END IF;

727 IF

728 NOT EXISTS (

729 -- all initiatives are revoked

730 SELECT NULL FROM "initiative"

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

732 ) AND (

733 -- and issue has not been accepted yet

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

735 -- or verification time has elapsed

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

737 "persist"."phase_finished" ) OR

738 -- or no initiatives have been revoked lately

739 NOT EXISTS (

740 SELECT NULL FROM "initiative"

741 WHERE "issue_id" = "issue_id_p"

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

743 )

744 )

745 THEN

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

747 ELSE

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

749 END IF;

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

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

752 WHERE "id" = "issue_row"."id";

753 RETURN "persist";

754 ELSIF

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

756 THEN

757 RETURN "persist";

758 ELSE

759 RETURN NULL;

760 END IF;

761 END IF;

762 IF

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

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

765 THEN

766 IF "persist"."state" != 'admission' THEN

767 PERFORM "take_snapshot"("issue_id_p");

768 PERFORM "finish_snapshot"("issue_id_p");

769 ELSE

770 UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum"

771 FROM "issue_quorum"

772 WHERE "id" = "issue_id_p"

773 AND "issue_quorum"."issue_id" = "issue_id_p";

774 END IF;

775 "persist"."snapshot_created" = TRUE;

776 IF "persist"."phase_finished" THEN

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

778 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id"

779 WHERE "id" = "issue_id_p";

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

781 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id"

782 WHERE "id" = "issue_id_p";

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

784 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"

785 WHERE "id" = "issue_id_p";

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

787 FOR "initiative_row" IN

788 SELECT * FROM "initiative"

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

790 FOR UPDATE

791 LOOP

792 IF

793 "initiative_row"."polling" OR

794 "initiative_row"."satisfied_supporter_count" >=

795 "issue_row"."initiative_quorum"

796 THEN

797 UPDATE "initiative" SET "admitted" = TRUE

798 WHERE "id" = "initiative_row"."id";

799 ELSE

800 UPDATE "initiative" SET "admitted" = FALSE

801 WHERE "id" = "initiative_row"."id";

802 END IF;

803 END LOOP;

804 END IF;

805 END IF;

806 RETURN "persist";

807 END IF;

808 IF

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

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

811 THEN

812 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

813 "persist"."harmonic_weights_set" = TRUE;

814 IF

815 "persist"."phase_finished" OR

816 "persist"."issue_revoked" OR

817 "persist"."state" = 'admission'

818 THEN

819 RETURN "persist";

820 ELSE

821 RETURN NULL;

822 END IF;

823 END IF;

824 IF "persist"."issue_revoked" THEN

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

826 "state_v" := 'canceled_revoked_before_accepted';

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

828 "state_v" := 'canceled_after_revocation_during_discussion';

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

830 "state_v" := 'canceled_after_revocation_during_verification';

831 END IF;

832 UPDATE "issue" SET

833 "state" = "state_v",

834 "closed" = "phase_finished",

835 "phase_finished" = NULL

836 WHERE "id" = "issue_id_p";

837 RETURN NULL;

838 END IF;

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

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

841 FOR UPDATE;

842 IF "issue_row"."phase_finished" NOTNULL THEN

843 UPDATE "issue" SET

844 "state" = 'canceled_issue_not_accepted',

845 "closed" = "phase_finished",

846 "phase_finished" = NULL

847 WHERE "id" = "issue_id_p";

848 END IF;

849 RETURN NULL;

850 END IF;

851 IF "persist"."phase_finished" THEN

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

853 UPDATE "issue" SET

854 "state" = 'verification',

855 "half_frozen" = "phase_finished",

856 "phase_finished" = NULL

857 WHERE "id" = "issue_id_p";

858 RETURN NULL;

859 END IF;

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

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

862 FOR UPDATE;

863 SELECT * INTO "policy_row" FROM "policy"

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

865 IF EXISTS (

866 SELECT NULL FROM "initiative"

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

868 ) THEN

869 UPDATE "issue" SET

870 "state" = 'voting',

871 "fully_frozen" = "phase_finished",

872 "phase_finished" = NULL

873 WHERE "id" = "issue_id_p";

874 ELSE

875 UPDATE "issue" SET

876 "state" = 'canceled_no_initiative_admitted',

877 "fully_frozen" = "phase_finished",

878 "closed" = "phase_finished",

879 "phase_finished" = NULL

880 WHERE "id" = "issue_id_p";

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

882 -- issue state has been manipulated

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

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

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

886 END IF;

887 RETURN NULL;

888 END IF;

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

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

891 PERFORM "close_voting"("issue_id_p");

892 "persist"."closed_voting" = TRUE;

893 RETURN "persist";

894 END IF;

895 PERFORM "calculate_ranks"("issue_id_p");

896 RETURN NULL;

897 END IF;

898 END IF;

899 RAISE WARNING 'should not happen';

900 RETURN NULL;

901 END;

902 $$;

903

904 CREATE OR REPLACE FUNCTION "check_everything"()

905 RETURNS VOID

906 LANGUAGE 'plpgsql' VOLATILE AS $$

907 DECLARE

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

909 "snapshot_id_v" "snapshot"."id"%TYPE;

910 "issue_id_v" "issue"."id"%TYPE;

911 "persist_v" "check_issue_persistence";

912 BEGIN

913 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';

914 DELETE FROM "expired_session";

915 DELETE FROM "expired_token";

916 DELETE FROM "unused_snapshot";

917 PERFORM "check_activity"();

918 PERFORM "calculate_member_counts"();

919 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP

920 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";

921 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"

922 WHERE "snapshot_id" = "snapshot_id_v";

923 LOOP

924 EXIT WHEN "issue_admission"("area_id_v") = FALSE;

925 END LOOP;

926 END LOOP;

927 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP

928 "persist_v" := NULL;

929 LOOP

930 "persist_v" := "check_issue"("issue_id_v", "persist_v");

931 EXIT WHEN "persist_v" ISNULL;

932 END LOOP;

933 END LOOP;

934 DELETE FROM "unused_snapshot";

935 RETURN;

936 END;

937 $$;

938

939 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)

940 RETURNS VOID

941 LANGUAGE 'plpgsql' VOLATILE AS $$

942 BEGIN

943 UPDATE "member" SET

944 "last_login" = NULL,

945 "last_delegation_check" = NULL,

946 "login" = NULL,

947 "password" = NULL,

948 "authority" = NULL,

949 "authority_uid" = NULL,

950 "authority_login" = NULL,

951 "deleted" = coalesce("deleted", now()),

952 "locked" = TRUE,

953 "active" = FALSE,

954 "notify_email" = NULL,

955 "notify_email_unconfirmed" = NULL,

956 "notify_email_secret" = NULL,

957 "notify_email_secret_expiry" = NULL,

958 "notify_email_lock_expiry" = NULL,

959 "disable_notifications" = TRUE,

960 "notification_counter" = DEFAULT,

961 "notification_sample_size" = 0,

962 "notification_dow" = NULL,

963 "notification_hour" = NULL,

964 "notification_sent" = NULL,

965 "login_recovery_expiry" = NULL,

966 "password_reset_secret" = NULL,

967 "password_reset_secret_expiry" = NULL,

968 "location" = NULL

969 WHERE "id" = "member_id_p";

970 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";

971 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";

972 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";

973 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";

974 DELETE FROM "contact" WHERE "member_id" = "member_id_p";

975 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";

976 DELETE FROM "session" WHERE "member_id" = "member_id_p";

977 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";

978 DELETE FROM "token" WHERE "member_id" = "member_id_p";

979 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";

980 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";

981 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";

982 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";

983 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";

984 DELETE FROM "direct_voter" USING "issue"

985 WHERE "direct_voter"."issue_id" = "issue"."id"

986 AND "issue"."closed" ISNULL

987 AND "member_id" = "member_id_p";

988 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";

989 RETURN;

990 END;

991 $$;

992

993 CREATE OR REPLACE FUNCTION "delete_private_data"()

994 RETURNS VOID

995 LANGUAGE 'plpgsql' VOLATILE AS $$

996 BEGIN

997 DELETE FROM "temporary_transaction_data";

998 DELETE FROM "temporary_suggestion_counts";

999 DELETE FROM "member" WHERE "activated" ISNULL;

1000 UPDATE "member" SET

1001 "invite_code" = NULL,

1002 "invite_code_expiry" = NULL,

1003 "admin_comment" = NULL,

1004 "last_login" = NULL,

1005 "last_delegation_check" = NULL,

1006 "login" = NULL,

1007 "password" = NULL,

1008 "authority" = NULL,

1009 "authority_uid" = NULL,

1010 "authority_login" = NULL,

1011 "lang" = NULL,

1012 "notify_email" = NULL,

1013 "notify_email_unconfirmed" = NULL,

1014 "notify_email_secret" = NULL,

1015 "notify_email_secret_expiry" = NULL,

1016 "notify_email_lock_expiry" = NULL,

1017 "disable_notifications" = TRUE,

1018 "notification_counter" = DEFAULT,

1019 "notification_sample_size" = 0,

1020 "notification_dow" = NULL,

1021 "notification_hour" = NULL,

1022 "notification_sent" = NULL,

1023 "login_recovery_expiry" = NULL,

1024 "password_reset_secret" = NULL,

1025 "password_reset_secret_expiry" = NULL,

1026 "location" = NULL;

1027 DELETE FROM "verification";

1028 DELETE FROM "member_settings";

1029 DELETE FROM "member_useterms";

1030 DELETE FROM "member_profile";

1031 DELETE FROM "rendered_member_statement";

1032 DELETE FROM "member_image";

1033 DELETE FROM "contact";

1034 DELETE FROM "ignored_member";

1035 DELETE FROM "session";

1036 DELETE FROM "system_application";

1037 DELETE FROM "system_application_redirect_uri";

1038 DELETE FROM "dynamic_application_scope";

1039 DELETE FROM "member_application";

1040 DELETE FROM "token";

1041 DELETE FROM "subscription";

1042 DELETE FROM "ignored_area";

1043 DELETE FROM "ignored_initiative";

1044 DELETE FROM "non_voter";

1045 DELETE FROM "direct_voter" USING "issue"

1046 WHERE "direct_voter"."issue_id" = "issue"."id"

1047 AND "issue"."closed" ISNULL;

1048 DELETE FROM "event_processed";

1049 DELETE FROM "notification_initiative_sent";

1050 DELETE FROM "newsletter";

1051 RETURN;

1052 END;

1053 $$;

1054

1055 CREATE VIEW "member_eligible_to_be_notified" AS

1056 SELECT * FROM "member"

1057 WHERE "activated" NOTNULL AND "locked" = FALSE;

1058

1059 COMMENT ON VIEW "member_eligible_to_be_notified" IS 'Filtered "member" table containing only activated and non-locked members (used as helper view for "member_to_notify" and "newsletter_to_send")';

1060

1061 CREATE VIEW "member_to_notify" AS

1062 SELECT * FROM "member_eligible_to_be_notified"

1063 WHERE "disable_notifications" = FALSE;

1064

1065 COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)';

1066

1067 CREATE VIEW "area_with_unaccepted_issues" AS

1068 SELECT DISTINCT ON ("area"."id") "area".*

1069 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"

1070 WHERE "issue"."state" = 'admission';

1071

1072 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';

1073

1074 CREATE VIEW "opening_draft" AS

1075 SELECT DISTINCT ON ("initiative_id") * FROM "draft"

1076 ORDER BY "initiative_id", "id";

1077

1078 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';

1079

1080 CREATE VIEW "current_draft" AS

1081 SELECT DISTINCT ON ("initiative_id") * FROM "draft"

1082 ORDER BY "initiative_id", "id" DESC;

1083

1084 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';

1085

1086 CREATE VIEW "member_contingent" AS

1087 SELECT

1088 "member"."id" AS "member_id",

1089 "contingent"."polling",

1090 "contingent"."time_frame",

1091 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN

1092 (

1093 SELECT count(1) FROM "draft"

1094 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"

1095 WHERE "draft"."author_id" = "member"."id"

1096 AND "initiative"."polling" = "contingent"."polling"

1097 AND "draft"."created" > now() - "contingent"."time_frame"

1098 ) + (

1099 SELECT count(1) FROM "suggestion"

1100 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"

1101 WHERE "suggestion"."author_id" = "member"."id"

1102 AND "contingent"."polling" = FALSE

1103 AND "suggestion"."created" > now() - "contingent"."time_frame"

1104 )

1105 ELSE NULL END AS "text_entry_count",

1106 "contingent"."text_entry_limit",

1107 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (

1108 SELECT count(1) FROM "opening_draft" AS "draft"

1109 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"

1110 WHERE "draft"."author_id" = "member"."id"

1111 AND "initiative"."polling" = "contingent"."polling"

1112 AND "draft"."created" > now() - "contingent"."time_frame"

1113 ) ELSE NULL END AS "initiative_count",

1114 "contingent"."initiative_limit"

1115 FROM "member" CROSS JOIN "contingent";

1116

1117 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';

1118

1119 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';

1120 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';

1121

1122 CREATE VIEW "member_contingent_left" AS

1123 SELECT

1124 "member_id",

1125 "polling",

1126 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",

1127 max("initiative_limit" - "initiative_count") AS "initiatives_left"

1128 FROM "member_contingent" GROUP BY "member_id", "polling";

1129

1130 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';

1131

1132 CREATE VIEW "scheduled_notification_to_send" AS

1133 SELECT * FROM (

1134 SELECT

1135 "id" AS "recipient_id",

1136 now() - CASE WHEN "notification_dow" ISNULL THEN

1137 ( "notification_sent"::DATE + CASE

1138 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"

1139 THEN 0 ELSE 1 END

1140 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"

1141 ELSE

1142 ( "notification_sent"::DATE +

1143 ( 7 + "notification_dow" -

1144 EXTRACT(DOW FROM

1145 ( "notification_sent"::DATE + CASE

1146 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"

1147 THEN 0 ELSE 1 END

1148 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"

1149 )::INTEGER

1150 ) % 7 +

1151 CASE

1152 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"

1153 THEN 0 ELSE 1

1154 END

1155 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"

1156 END AS "pending"

1157 FROM (

1158 SELECT

1159 "id",

1160 COALESCE("notification_sent", "activated") AS "notification_sent",

1161 "notification_dow",

1162 "notification_hour"

1163 FROM "member_to_notify"

1164 WHERE "notification_hour" NOTNULL

1165 ) AS "subquery1"

1166 ) AS "subquery2"

1167 WHERE "pending" > '0'::INTERVAL;

1168

1169 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';

1170

1171 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';

1172 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';

1173

1174 CREATE VIEW "newsletter_to_send" AS

1175 SELECT

1176 "member"."id" AS "recipient_id",

1177 "newsletter"."id" AS "newsletter_id",

1178 "newsletter"."published"

1179 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"

1180 LEFT JOIN "privilege" ON

1181 "privilege"."member_id" = "member"."id" AND

1182 "privilege"."unit_id" = "newsletter"."unit_id" AND

1183 "privilege"."voting_right" = TRUE

1184 LEFT JOIN "subscription" ON

1185 "subscription"."member_id" = "member"."id" AND

1186 "subscription"."unit_id" = "newsletter"."unit_id"

1187 WHERE "newsletter"."published" <= now()

1188 AND "newsletter"."sent" ISNULL

1189 AND (

1190 "member"."disable_notifications" = FALSE OR

1191 "newsletter"."include_all_members" = TRUE )

1192 AND (

1193 "newsletter"."unit_id" ISNULL OR

1194 "privilege"."member_id" NOTNULL OR

1195 "subscription"."member_id" NOTNULL );

1196

1197 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';

1198

1199 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';

1200

1201 SELECT "copy_current_draft_data" ("id") FROM "initiative";

1202

1203 COMMIT;