liquid_feedback_core: 617ac1725557 update/core-update.v4.1.0-v4.2.0.sql

liquid_feedback_core

view update/core-update.v4.1.0-v4.2.0.sql @ 603:617ac1725557

Removed column "content_type" from table "draft_attachment" (Content type is stored in "file" table instead)

author	jbe
date	Mon Feb 10 20:33:10 2020 +0100 (2020-02-10)
parents	9570aeabf4fc
children	f69e0d9e5bb3

line source

1 BEGIN;

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

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

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

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

9 COMMIT;

11 BEGIN;

13 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

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

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

17 DROP VIEW "newsletter_to_send";

18 DROP VIEW "scheduled_notification_to_send";

19 DROP VIEW "member_contingent_left";

20 DROP VIEW "member_contingent";

21 DROP VIEW "expired_snapshot";

22 DROP VIEW "current_draft";

23 DROP VIEW "opening_draft";

24 DROP VIEW "area_with_unaccepted_issues";

25 DROP VIEW "member_to_notify";

26 DROP VIEW "member_eligible_to_be_notified";

28 CREATE EXTENSION IF NOT EXISTS btree_gist;

29 CREATE EXTENSION IF NOT EXISTS conflux;

31 DROP FUNCTION "text_search_query" (TEXT);

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

35 CREATE TABLE "file" (

36 "id" SERIAL8 PRIMARY KEY,

37 UNIQUE ("content_type", "hash"),

38 "content_type" TEXT NOT NULL,

39 "hash" TEXT NOT NULL,

40 "data" BYTEA NOT NULL,

41 "preview_content_type" TEXT,

42 "preview_data" BYTEA );

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

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

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

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

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

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

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

53 DROP TRIGGER "update_text_search_data" ON "member";

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

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

58 DROP TRIGGER "update_text_search_data" ON "member_profile";

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

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

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

65 DROP TRIGGER "update_text_search_data" ON "unit";

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

68 DROP TRIGGER "update_text_search_data" ON "area";

70 DROP INDEX "issue_accepted_idx";

71 DROP INDEX "issue_half_frozen_idx";

72 DROP INDEX "issue_fully_frozen_idx";

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

74 DROP INDEX "issue_closed_idx_canceled";

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

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

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

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

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

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

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

83 DROP INDEX "initiative_revoked_idx";

84 DROP TRIGGER "update_text_search_data" ON "initiative";

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

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

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

90 "winning_initiative_id" != "losing_initiative_id" AND

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

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

94 DROP TRIGGER "update_text_search_data" ON "draft";

96 CREATE TABLE "draft_attachment" (

97 "id" SERIAL8 PRIMARY KEY,

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

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

100 "title" TEXT,

101 "description" TEXT );

102

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

104

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

106 DROP TRIGGER "update_text_search_data" ON "suggestion";

107

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

109 DROP TRIGGER "update_text_search_data" ON "direct_voter";

110

111 CREATE TABLE "posting" (

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

113 "id" SERIAL8 PRIMARY KEY,

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

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

116 "message" TEXT NOT NULL,

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

118 "area_id" INT4,

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

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

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

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

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

124 "initiative_id" INT4,

125 "suggestion_id" INT8,

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

127 -- actually deleted

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

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

130 -- ON DELETE CASCADE ON UPDATE CASCADE,

131 CONSTRAINT "area_requires_unit" CHECK (

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

133 CONSTRAINT "policy_set_when_issue_set" CHECK (

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

135 CONSTRAINT "issue_requires_area" CHECK (

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

137 CONSTRAINT "initiative_requires_issue" CHECK (

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

139 CONSTRAINT "suggestion_requires_initiative" CHECK (

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

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

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

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

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

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

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

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

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

149

150 CREATE TABLE "posting_lexeme" (

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

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

153 "posting_id" INT8,

154 "lexeme" TEXT,

155 "author_id" INT4 );

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

157

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

159

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

175 ALTER TABLE "event" ADD

176 CONSTRAINT "constr_for_issue_state_changed" CHECK (

177 "event" != 'issue_state_changed' OR (

178 "posting_id" ISNULL AND

179 "member_id" ISNULL AND

180 "other_member_id" ISNULL AND

181 "scope" ISNULL AND

182 "unit_id" NOTNULL AND

183 "area_id" NOTNULL AND

184 "policy_id" NOTNULL AND

185 "issue_id" NOTNULL AND

186 "state" NOTNULL AND

187 "initiative_id" ISNULL AND

188 "draft_id" ISNULL AND

189 "suggestion_id" ISNULL AND

190 "boolean_value" ISNULL AND

191 "numeric_value" ISNULL AND

192 "text_value" ISNULL AND

193 "old_text_value" ISNULL ));

194 ALTER TABLE "event" ADD

195 CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (

196 "event" NOT IN (

197 'initiative_created_in_new_issue',

198 'initiative_created_in_existing_issue',

199 'initiative_revoked',

200 'new_draft_created'

201 ) OR (

202 "posting_id" ISNULL AND

203 "member_id" NOTNULL AND

204 "other_member_id" ISNULL AND

205 "scope" ISNULL AND

206 "unit_id" NOTNULL AND

207 "area_id" NOTNULL AND

208 "policy_id" NOTNULL AND

209 "issue_id" NOTNULL AND

210 "state" NOTNULL AND

211 "initiative_id" NOTNULL AND

212 "draft_id" NOTNULL AND

213 "suggestion_id" ISNULL AND

214 "boolean_value" ISNULL AND

215 "numeric_value" ISNULL AND

216 "text_value" ISNULL AND

217 "old_text_value" ISNULL ));

218 ALTER TABLE "event" ADD

219 CONSTRAINT "constr_for_suggestion_creation" CHECK (

220 "event" != 'suggestion_created' OR (

221 "posting_id" ISNULL AND

222 "member_id" NOTNULL AND

223 "other_member_id" ISNULL AND

224 "scope" ISNULL AND

225 "unit_id" NOTNULL AND

226 "area_id" NOTNULL AND

227 "policy_id" NOTNULL AND

228 "issue_id" NOTNULL AND

229 "state" NOTNULL AND

230 "initiative_id" NOTNULL AND

231 "draft_id" ISNULL AND

232 "suggestion_id" NOTNULL AND

233 "boolean_value" ISNULL AND

234 "numeric_value" ISNULL AND

235 "text_value" ISNULL AND

236 "old_text_value" ISNULL ));

237 ALTER TABLE "event" ADD

238 CONSTRAINT "constr_for_suggestion_removal" CHECK (

239 "event" != 'suggestion_deleted' OR (

240 "posting_id" ISNULL AND

241 "member_id" ISNULL AND

242 "other_member_id" ISNULL AND

243 "scope" ISNULL AND

244 "unit_id" NOTNULL AND

245 "area_id" NOTNULL AND

246 "policy_id" NOTNULL AND

247 "issue_id" NOTNULL AND

248 "state" NOTNULL AND

249 "initiative_id" NOTNULL AND

250 "draft_id" ISNULL AND

251 "suggestion_id" NOTNULL AND

252 "boolean_value" ISNULL AND

253 "numeric_value" ISNULL AND

254 "text_value" ISNULL AND

255 "old_text_value" ISNULL ));

256 ALTER TABLE "event" ADD

257 CONSTRAINT "constr_for_value_less_member_event" CHECK (

258 "event" NOT IN (

259 'member_activated',

260 'member_deleted',

261 'member_profile_updated',

262 'member_image_updated'

263 ) OR (

264 "posting_id" ISNULL AND

265 "member_id" NOTNULL AND

266 "other_member_id" ISNULL AND

267 "scope" ISNULL AND

268 "unit_id" ISNULL AND

269 "area_id" ISNULL AND

270 "policy_id" ISNULL AND

271 "issue_id" ISNULL AND

272 "state" ISNULL AND

273 "initiative_id" ISNULL AND

274 "draft_id" ISNULL AND

275 "suggestion_id" ISNULL AND

276 "boolean_value" ISNULL AND

277 "numeric_value" ISNULL AND

278 "text_value" ISNULL AND

279 "old_text_value" ISNULL ));

280 ALTER TABLE "event" ADD

281 CONSTRAINT "constr_for_member_active" CHECK (

282 "event" != 'member_active' OR (

283 "posting_id" ISNULL AND

284 "member_id" NOTNULL AND

285 "other_member_id" ISNULL AND

286 "scope" ISNULL AND

287 "unit_id" ISNULL AND

288 "area_id" ISNULL AND

289 "policy_id" ISNULL AND

290 "issue_id" ISNULL AND

291 "state" ISNULL AND

292 "initiative_id" ISNULL AND

293 "draft_id" ISNULL AND

294 "suggestion_id" ISNULL AND

295 "boolean_value" NOTNULL AND

296 "numeric_value" ISNULL AND

297 "text_value" ISNULL AND

298 "old_text_value" ISNULL ));

299 ALTER TABLE "event" ADD

300 CONSTRAINT "constr_for_member_name_updated" CHECK (

301 "event" != 'member_name_updated' OR (

302 "posting_id" ISNULL AND

303 "member_id" NOTNULL AND

304 "other_member_id" ISNULL AND

305 "scope" ISNULL AND

306 "unit_id" ISNULL AND

307 "area_id" ISNULL AND

308 "policy_id" ISNULL AND

309 "issue_id" ISNULL AND

310 "state" ISNULL AND

311 "initiative_id" ISNULL AND

312 "draft_id" ISNULL AND

313 "suggestion_id" ISNULL AND

314 "boolean_value" ISNULL AND

315 "numeric_value" ISNULL AND

316 "text_value" NOTNULL AND

317 "old_text_value" NOTNULL ));

318 ALTER TABLE "event" ADD

319 CONSTRAINT "constr_for_interest" CHECK (

320 "event" != 'interest' OR (

321 "posting_id" ISNULL AND

322 "member_id" NOTNULL AND

323 "other_member_id" ISNULL AND

324 "scope" ISNULL AND

325 "unit_id" NOTNULL AND

326 "area_id" NOTNULL AND

327 "policy_id" NOTNULL AND

328 "issue_id" NOTNULL AND

329 "state" NOTNULL AND

330 "initiative_id" ISNULL AND

331 "draft_id" ISNULL AND

332 "suggestion_id" ISNULL AND

333 "boolean_value" NOTNULL AND

334 "numeric_value" ISNULL AND

335 "text_value" ISNULL AND

336 "old_text_value" ISNULL ));

337 ALTER TABLE "event" ADD

338 CONSTRAINT "constr_for_initiator" CHECK (

339 "event" != 'initiator' OR (

340 "posting_id" ISNULL AND

341 "member_id" NOTNULL AND

342 "other_member_id" ISNULL AND

343 "scope" ISNULL AND

344 "unit_id" NOTNULL AND

345 "area_id" NOTNULL AND

346 "policy_id" NOTNULL AND

347 "issue_id" NOTNULL AND

348 "state" NOTNULL AND

349 "initiative_id" NOTNULL AND

350 "draft_id" ISNULL AND

351 "suggestion_id" ISNULL AND

352 "boolean_value" NOTNULL AND

353 "numeric_value" ISNULL AND

354 "text_value" ISNULL AND

355 "old_text_value" ISNULL ));

356 ALTER TABLE "event" ADD

357 CONSTRAINT "constr_for_support" CHECK (

358 "event" != 'support' OR (

359 "posting_id" ISNULL AND

360 "member_id" NOTNULL AND

361 "other_member_id" ISNULL AND

362 "scope" ISNULL AND

363 "unit_id" NOTNULL AND

364 "area_id" NOTNULL AND

365 "policy_id" NOTNULL AND

366 "issue_id" NOTNULL AND

367 "state" NOTNULL AND

368 "initiative_id" NOTNULL AND

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

370 "suggestion_id" ISNULL AND

371 "boolean_value" NOTNULL AND

372 "numeric_value" ISNULL AND

373 "text_value" ISNULL AND

374 "old_text_value" ISNULL ));

375 ALTER TABLE "event" ADD

376 CONSTRAINT "constr_for_support_updated" CHECK (

377 "event" != 'support_updated' OR (

378 "posting_id" ISNULL AND

379 "member_id" NOTNULL AND

380 "other_member_id" ISNULL AND

381 "scope" ISNULL AND

382 "unit_id" NOTNULL AND

383 "area_id" NOTNULL AND

384 "policy_id" NOTNULL AND

385 "issue_id" NOTNULL AND

386 "state" NOTNULL AND

387 "initiative_id" NOTNULL AND

388 "draft_id" NOTNULL AND

389 "suggestion_id" ISNULL AND

390 "boolean_value" ISNULL AND

391 "numeric_value" ISNULL AND

392 "text_value" ISNULL AND

393 "old_text_value" ISNULL ));

394 ALTER TABLE "event" ADD

395 CONSTRAINT "constr_for_suggestion_rated" CHECK (

396 "event" != 'suggestion_rated' OR (

397 "posting_id" ISNULL AND

398 "member_id" NOTNULL AND

399 "other_member_id" ISNULL AND

400 "scope" ISNULL AND

401 "unit_id" NOTNULL AND

402 "area_id" NOTNULL AND

403 "policy_id" NOTNULL AND

404 "issue_id" NOTNULL AND

405 "state" NOTNULL AND

406 "initiative_id" NOTNULL AND

407 "draft_id" ISNULL AND

408 "suggestion_id" NOTNULL AND

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

410 "numeric_value" NOTNULL AND

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

412 "text_value" ISNULL AND

413 "old_text_value" ISNULL ));

414 ALTER TABLE "event" ADD

415 CONSTRAINT "constr_for_delegation" CHECK (

416 "event" != 'delegation' OR (

417 "posting_id" ISNULL AND

418 "member_id" NOTNULL AND

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

420 "scope" NOTNULL AND

421 "unit_id" NOTNULL AND

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

423 "policy_id" ISNULL AND

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

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

426 "initiative_id" ISNULL AND

427 "draft_id" ISNULL AND

428 "suggestion_id" ISNULL AND

429 "boolean_value" NOTNULL AND

430 "numeric_value" ISNULL AND

431 "text_value" ISNULL AND

432 "old_text_value" ISNULL ));

433 ALTER TABLE "event" ADD

434 CONSTRAINT "constr_for_contact" CHECK (

435 "event" != 'contact' OR (

436 "posting_id" ISNULL AND

437 "member_id" NOTNULL AND

438 "other_member_id" NOTNULL AND

439 "scope" ISNULL AND

440 "unit_id" ISNULL AND

441 "area_id" ISNULL AND

442 "policy_id" ISNULL AND

443 "issue_id" ISNULL AND

444 "state" ISNULL AND

445 "initiative_id" ISNULL AND

446 "draft_id" ISNULL AND

447 "suggestion_id" ISNULL AND

448 "boolean_value" NOTNULL AND

449 "numeric_value" ISNULL AND

450 "text_value" ISNULL AND

451 "old_text_value" ISNULL ));

452 ALTER TABLE "event" ADD

453 CONSTRAINT "constr_for_posting_created" CHECK (

454 "event" != 'posting_created' OR (

455 "posting_id" NOTNULL AND

456 "member_id" NOTNULL AND

457 "other_member_id" ISNULL AND

458 "scope" ISNULL AND

459 "state" ISNULL AND

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

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

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

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

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

465 "draft_id" ISNULL AND

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

467 "boolean_value" ISNULL AND

468 "numeric_value" ISNULL AND

469 "text_value" ISNULL AND

470 "old_text_value" ISNULL ));

471

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

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

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

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

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

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

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

479

480 CREATE OR REPLACE FUNCTION "highlight"

481 ( "body_p" TEXT,

482 "query_text_p" TEXT )

483 RETURNS TEXT

484 LANGUAGE 'plpgsql' IMMUTABLE AS $$

485 BEGIN

486 RETURN ts_headline(

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

488 "plainto_tsquery"("query_text_p"),

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

490 END;

491 $$;

492

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

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

495 $1."name",

496 $1."identification"

497 )) $$;

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

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

500

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

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

503 $1."statement",

504 $1."profile_text_data"

505 )) $$;

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

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

508

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

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

511 $1."name",

512 $1."description"

513 )) $$;

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

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

516

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

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

519 $1."name",

520 $1."description"

521 )) $$;

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

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

524

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

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

527 $1."name",

528 $1."content"

529 )) $$;

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

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

532

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

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

535 $1."content"

536 )) $$;

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

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

539

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

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

542 $1."name",

543 $1."content"

544 )) $$;

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

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

547

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

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

550 $1."comment"

551 )) $$;

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

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

554

555 CREATE FUNCTION "update_posting_lexeme_trigger"()

556 RETURNS TRIGGER

557 LANGUAGE 'plpgsql' VOLATILE AS $$

558 DECLARE

559 "lexeme_v" TEXT;

560 BEGIN

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

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

563 END IF;

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

565 FOR "lexeme_v" IN

566 SELECT regexp_matches[1]

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

568 LOOP

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

570 VALUES (

571 NEW."id",

572 NEW."author_id",

573 "lexeme_v" )

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

575 END LOOP;

576 END IF;

577 RETURN NULL;

578 END;

579 $$;

580

581 CREATE TRIGGER "update_posting_lexeme"

582 AFTER INSERT OR UPDATE OR DELETE ON "posting"

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

584

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

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

587

588 CREATE FUNCTION "write_event_posting_trigger"()

589 RETURNS TRIGGER

590 LANGUAGE 'plpgsql' VOLATILE AS $$

591 BEGIN

592 INSERT INTO "event" (

593 "event", "posting_id", "member_id",

594 "unit_id", "area_id", "policy_id",

595 "issue_id", "initiative_id", "suggestion_id"

596 ) VALUES (

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

598 NEW."unit_id", NEW."area_id", NEW."policy_id",

599 NEW."issue_id", NEW."initiative_id", NEW."suggestion_id"

600 );

601 RETURN NULL;

602 END;

603 $$;

604

605 CREATE TRIGGER "write_event_posting"

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

607 "write_event_posting_trigger"();

608

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

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

611

612 CREATE FUNCTION "file_requires_reference_trigger"()

613 RETURNS TRIGGER

614 LANGUAGE 'plpgsql' VOLATILE AS $$

615 BEGIN

616 IF NOT EXISTS (

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

618 ) THEN

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

620 ERRCODE = 'integrity_constraint_violation',

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

622 END IF;

623 RETURN NULL;

624 END;

625 $$;

626

627 CREATE CONSTRAINT TRIGGER "file_requires_reference"

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

629 FOR EACH ROW EXECUTE PROCEDURE

630 "file_requires_reference_trigger"();

631

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

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

634

635 CREATE FUNCTION "last_reference_deletes_file_trigger"()

636 RETURNS TRIGGER

637 LANGUAGE 'plpgsql' VOLATILE AS $$

638 DECLARE

639 "reference_lost" BOOLEAN;

640 BEGIN

641 IF TG_OP = 'DELETE' THEN

642 "reference_lost" := TRUE;

643 ELSE

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

645 END IF;

646 IF

647 "reference_lost" AND NOT EXISTS (

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

649 )

650 THEN

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

652 END IF;

653 RETURN NULL;

654 END;

655 $$;

656

657 CREATE CONSTRAINT TRIGGER "last_reference_deletes_file"

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

659 FOR EACH ROW EXECUTE PROCEDURE

660 "last_reference_deletes_file_trigger"();

661

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

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

664

665 CREATE OR REPLACE FUNCTION "copy_current_draft_data"

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

667 RETURNS VOID

668 LANGUAGE 'plpgsql' VOLATILE AS $$

669 BEGIN

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

671 FOR UPDATE;

672 UPDATE "initiative" SET

673 "location" = "draft"."location",

674 "content" = "draft"."content"

675 FROM "current_draft" AS "draft"

676 WHERE "initiative"."id" = "initiative_id_p"

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

678 END;

679 $$;

680

681 CREATE VIEW "follower" AS

682 SELECT

683 "id" AS "follower_id",

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

685 FROM "contact"

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

687 AS "following_ids"

688 FROM "member";

689

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

691

692 CREATE OR REPLACE FUNCTION "check_issue"

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

694 "persist" "check_issue_persistence" )

695 RETURNS "check_issue_persistence"

696 LANGUAGE 'plpgsql' VOLATILE AS $$

697 DECLARE

698 "issue_row" "issue"%ROWTYPE;

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

700 "policy_row" "policy"%ROWTYPE;

701 "initiative_row" "initiative"%ROWTYPE;

702 "state_v" "issue_state";

703 BEGIN

704 PERFORM "require_transaction_isolation"();

705 IF "persist" ISNULL THEN

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

707 FOR UPDATE;

708 SELECT "calculated" INTO "last_calculated_v"

709 FROM "snapshot" JOIN "snapshot_issue"

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

711 WHERE "snapshot_issue"."issue_id" = "issue_id_p"

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

713 IF "issue_row"."closed" NOTNULL THEN

714 RETURN NULL;

715 END IF;

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

717 IF

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

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

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

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

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

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

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

725 "issue_row"."fully_frozen" + "issue_row"."voting_time" )

726 THEN

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

728 ELSE

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

730 END IF;

731 IF

732 NOT EXISTS (

733 -- all initiatives are revoked

734 SELECT NULL FROM "initiative"

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

736 ) AND (

737 -- and issue has not been accepted yet

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

739 -- or verification time has elapsed

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

741 "persist"."phase_finished" ) OR

742 -- or no initiatives have been revoked lately

743 NOT EXISTS (

744 SELECT NULL FROM "initiative"

745 WHERE "issue_id" = "issue_id_p"

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

747 )

748 )

749 THEN

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

751 ELSE

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

753 END IF;

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

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

756 WHERE "id" = "issue_row"."id";

757 RETURN "persist";

758 ELSIF

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

760 THEN

761 RETURN "persist";

762 ELSE

763 RETURN NULL;

764 END IF;

765 END IF;

766 IF

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

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

769 THEN

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

771 PERFORM "take_snapshot"("issue_id_p");

772 PERFORM "finish_snapshot"("issue_id_p");

773 ELSE

774 UPDATE "issue" SET "issue_quorum" = "issue_quorum"."issue_quorum"

775 FROM "issue_quorum"

776 WHERE "id" = "issue_id_p"

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

778 END IF;

779 "persist"."snapshot_created" = TRUE;

780 IF "persist"."phase_finished" THEN

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

782 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id"

783 WHERE "id" = "issue_id_p";

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

785 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id"

786 WHERE "id" = "issue_id_p";

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

788 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"

789 WHERE "id" = "issue_id_p";

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

791 FOR "initiative_row" IN

792 SELECT * FROM "initiative"

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

794 FOR UPDATE

795 LOOP

796 IF

797 "initiative_row"."polling" OR

798 "initiative_row"."satisfied_supporter_count" >=

799 "issue_row"."initiative_quorum"

800 THEN

801 UPDATE "initiative" SET "admitted" = TRUE

802 WHERE "id" = "initiative_row"."id";

803 ELSE

804 UPDATE "initiative" SET "admitted" = FALSE

805 WHERE "id" = "initiative_row"."id";

806 END IF;

807 END LOOP;

808 END IF;

809 END IF;

810 RETURN "persist";

811 END IF;

812 IF

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

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

815 THEN

816 PERFORM "set_harmonic_initiative_weights"("issue_id_p");

817 "persist"."harmonic_weights_set" = TRUE;

818 IF

819 "persist"."phase_finished" OR

820 "persist"."issue_revoked" OR

821 "persist"."state" = 'admission'

822 THEN

823 RETURN "persist";

824 ELSE

825 RETURN NULL;

826 END IF;

827 END IF;

828 IF "persist"."issue_revoked" THEN

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

830 "state_v" := 'canceled_revoked_before_accepted';

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

832 "state_v" := 'canceled_after_revocation_during_discussion';

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

834 "state_v" := 'canceled_after_revocation_during_verification';

835 END IF;

836 UPDATE "issue" SET

837 "state" = "state_v",

838 "closed" = "phase_finished",

839 "phase_finished" = NULL

840 WHERE "id" = "issue_id_p";

841 RETURN NULL;

842 END IF;

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

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

845 FOR UPDATE;

846 IF "issue_row"."phase_finished" NOTNULL THEN

847 UPDATE "issue" SET

848 "state" = 'canceled_issue_not_accepted',

849 "closed" = "phase_finished",

850 "phase_finished" = NULL

851 WHERE "id" = "issue_id_p";

852 END IF;

853 RETURN NULL;

854 END IF;

855 IF "persist"."phase_finished" THEN

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

857 UPDATE "issue" SET

858 "state" = 'verification',

859 "half_frozen" = "phase_finished",

860 "phase_finished" = NULL

861 WHERE "id" = "issue_id_p";

862 RETURN NULL;

863 END IF;

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

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

866 FOR UPDATE;

867 SELECT * INTO "policy_row" FROM "policy"

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

869 IF EXISTS (

870 SELECT NULL FROM "initiative"

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

872 ) THEN

873 UPDATE "issue" SET

874 "state" = 'voting',

875 "fully_frozen" = "phase_finished",

876 "phase_finished" = NULL

877 WHERE "id" = "issue_id_p";

878 ELSE

879 UPDATE "issue" SET

880 "state" = 'canceled_no_initiative_admitted',

881 "fully_frozen" = "phase_finished",

882 "closed" = "phase_finished",

883 "phase_finished" = NULL

884 WHERE "id" = "issue_id_p";

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

886 -- issue state has been manipulated

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

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

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

890 END IF;

891 RETURN NULL;

892 END IF;

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

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

895 PERFORM "close_voting"("issue_id_p");

896 "persist"."closed_voting" = TRUE;

897 RETURN "persist";

898 END IF;

899 PERFORM "calculate_ranks"("issue_id_p");

900 RETURN NULL;

901 END IF;

902 END IF;

903 RAISE WARNING 'should not happen';

904 RETURN NULL;

905 END;

906 $$;

907

908 CREATE OR REPLACE FUNCTION "check_everything"()

909 RETURNS VOID

910 LANGUAGE 'plpgsql' VOLATILE AS $$

911 DECLARE

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

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

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

915 "persist_v" "check_issue_persistence";

916 BEGIN

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

918 DELETE FROM "expired_session";

919 DELETE FROM "expired_token";

920 DELETE FROM "unused_snapshot";

921 PERFORM "check_activity"();

922 PERFORM "calculate_member_counts"();

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

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

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

926 WHERE "snapshot_id" = "snapshot_id_v";

927 LOOP

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

929 END LOOP;

930 END LOOP;

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

932 "persist_v" := NULL;

933 LOOP

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

935 EXIT WHEN "persist_v" ISNULL;

936 END LOOP;

937 END LOOP;

938 DELETE FROM "unused_snapshot";

939 RETURN;

940 END;

941 $$;

942

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

944 RETURNS VOID

945 LANGUAGE 'plpgsql' VOLATILE AS $$

946 BEGIN

947 UPDATE "member" SET

948 "last_login" = NULL,

949 "last_delegation_check" = NULL,

950 "login" = NULL,

951 "password" = NULL,

952 "authority" = NULL,

953 "authority_uid" = NULL,

954 "authority_login" = NULL,

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

956 "locked" = TRUE,

957 "active" = FALSE,

958 "notify_email" = NULL,

959 "notify_email_unconfirmed" = NULL,

960 "notify_email_secret" = NULL,

961 "notify_email_secret_expiry" = NULL,

962 "notify_email_lock_expiry" = NULL,

963 "disable_notifications" = TRUE,

964 "notification_counter" = DEFAULT,

965 "notification_sample_size" = 0,

966 "notification_dow" = NULL,

967 "notification_hour" = NULL,

968 "notification_sent" = NULL,

969 "login_recovery_expiry" = NULL,

970 "password_reset_secret" = NULL,

971 "password_reset_secret_expiry" = NULL,

972 "location" = NULL

973 WHERE "id" = "member_id_p";

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

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

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

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

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

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

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

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

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

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

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

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

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

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

988 DELETE FROM "direct_voter" USING "issue"

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

990 AND "issue"."closed" ISNULL

991 AND "member_id" = "member_id_p";

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

993 RETURN;

994 END;

995 $$;

996

997 CREATE OR REPLACE FUNCTION "delete_private_data"()

998 RETURNS VOID

999 LANGUAGE 'plpgsql' VOLATILE AS $$

1000 BEGIN

1001 DELETE FROM "temporary_transaction_data";

1002 DELETE FROM "temporary_suggestion_counts";

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

1004 UPDATE "member" SET

1005 "invite_code" = NULL,

1006 "invite_code_expiry" = NULL,

1007 "admin_comment" = NULL,

1008 "last_login" = NULL,

1009 "last_delegation_check" = NULL,

1010 "login" = NULL,

1011 "password" = NULL,

1012 "authority" = NULL,

1013 "authority_uid" = NULL,

1014 "authority_login" = NULL,

1015 "lang" = NULL,

1016 "notify_email" = NULL,

1017 "notify_email_unconfirmed" = NULL,

1018 "notify_email_secret" = NULL,

1019 "notify_email_secret_expiry" = NULL,

1020 "notify_email_lock_expiry" = NULL,

1021 "disable_notifications" = TRUE,

1022 "notification_counter" = DEFAULT,

1023 "notification_sample_size" = 0,

1024 "notification_dow" = NULL,

1025 "notification_hour" = NULL,

1026 "notification_sent" = NULL,

1027 "login_recovery_expiry" = NULL,

1028 "password_reset_secret" = NULL,

1029 "password_reset_secret_expiry" = NULL,

1030 "location" = NULL;

1031 DELETE FROM "verification";

1032 DELETE FROM "member_settings";

1033 DELETE FROM "member_useterms";

1034 DELETE FROM "member_profile";

1035 DELETE FROM "rendered_member_statement";

1036 DELETE FROM "member_image";

1037 DELETE FROM "contact";

1038 DELETE FROM "ignored_member";

1039 DELETE FROM "session";

1040 DELETE FROM "system_application";

1041 DELETE FROM "system_application_redirect_uri";

1042 DELETE FROM "dynamic_application_scope";

1043 DELETE FROM "member_application";

1044 DELETE FROM "token";

1045 DELETE FROM "subscription";

1046 DELETE FROM "ignored_area";

1047 DELETE FROM "ignored_initiative";

1048 DELETE FROM "non_voter";

1049 DELETE FROM "direct_voter" USING "issue"

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

1051 AND "issue"."closed" ISNULL;

1052 DELETE FROM "event_processed";

1053 DELETE FROM "notification_initiative_sent";

1054 DELETE FROM "newsletter";

1055 RETURN;

1056 END;

1057 $$;

1058

1059 CREATE VIEW "member_eligible_to_be_notified" AS

1060 SELECT * FROM "member"

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

1062

1063 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")';

1064

1065 CREATE VIEW "member_to_notify" AS

1066 SELECT * FROM "member_eligible_to_be_notified"

1067 WHERE "disable_notifications" = FALSE;

1068

1069 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)';

1070

1071 CREATE VIEW "area_with_unaccepted_issues" AS

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

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

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

1075

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

1077

1078 CREATE VIEW "opening_draft" AS

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

1080 ORDER BY "initiative_id", "id";

1081

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

1083

1084 CREATE VIEW "current_draft" AS

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

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

1087

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

1089

1090 CREATE VIEW "member_contingent" AS

1091 SELECT

1092 "member"."id" AS "member_id",

1093 "contingent"."polling",

1094 "contingent"."time_frame",

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

1096 (

1097 SELECT count(1) FROM "draft"

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

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

1100 AND "initiative"."polling" = "contingent"."polling"

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

1102 ) + (

1103 SELECT count(1) FROM "suggestion"

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

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

1106 AND "contingent"."polling" = FALSE

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

1108 )

1109 ELSE NULL END AS "text_entry_count",

1110 "contingent"."text_entry_limit",

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

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

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

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

1115 AND "initiative"."polling" = "contingent"."polling"

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

1117 ) ELSE NULL END AS "initiative_count",

1118 "contingent"."initiative_limit"

1119 FROM "member" CROSS JOIN "contingent";

1120

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

1122

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

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

1125

1126 CREATE VIEW "member_contingent_left" AS

1127 SELECT

1128 "member_id",

1129 "polling",

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

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

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

1133

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

1135

1136 CREATE VIEW "scheduled_notification_to_send" AS

1137 SELECT * FROM (

1138 SELECT

1139 "id" AS "recipient_id",

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

1141 ( "notification_sent"::DATE + CASE

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

1143 THEN 0 ELSE 1 END

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

1145 ELSE

1146 ( "notification_sent"::DATE +

1147 ( 7 + "notification_dow" -

1148 EXTRACT(DOW FROM

1149 ( "notification_sent"::DATE + CASE

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

1151 THEN 0 ELSE 1 END

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

1153 )::INTEGER

1154 ) % 7 +

1155 CASE

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

1157 THEN 0 ELSE 1

1158 END

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

1160 END AS "pending"

1161 FROM (

1162 SELECT

1163 "id",

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

1165 "notification_dow",

1166 "notification_hour"

1167 FROM "member_to_notify"

1168 WHERE "notification_hour" NOTNULL

1169 ) AS "subquery1"

1170 ) AS "subquery2"

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

1172

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

1174

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

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

1177

1178 CREATE VIEW "newsletter_to_send" AS

1179 SELECT

1180 "member"."id" AS "recipient_id",

1181 "newsletter"."id" AS "newsletter_id",

1182 "newsletter"."published"

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

1184 LEFT JOIN "privilege" ON

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

1186 "privilege"."unit_id" = "newsletter"."unit_id" AND

1187 "privilege"."voting_right" = TRUE

1188 LEFT JOIN "subscription" ON

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

1190 "subscription"."unit_id" = "newsletter"."unit_id"

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

1192 AND "newsletter"."sent" ISNULL

1193 AND (

1194 "member"."disable_notifications" = FALSE OR

1195 "newsletter"."include_all_members" = TRUE )

1196 AND (

1197 "newsletter"."unit_id" ISNULL OR

1198 "privilege"."member_id" NOTNULL OR

1199 "subscription"."member_id" NOTNULL );

1200

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

1202

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

1204

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

1206

1207 END;