liquid_feedback_core: 6f427a8f8061 update/core-update.v3.1.0-v3.2.1.sql

liquid_feedback_core

view update/core-update.v3.1.0-v3.2.1.sql @ 588:6f427a8f8061

Added "posting" table and follower concept for members (column "following" in table "contact")

author	jbe
date	Wed Dec 05 00:35:30 2018 +0100 (2018-12-05)
parents	e4f619e87664
children

line source

1 BEGIN;

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('3.2.1', 3, 2, 1))

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

7 ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;

8 ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1;

9 ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;

10 ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6);

11 ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 DEFAULT floor(random() * 24) CHECK ("notification_hour" BETWEEN 0 AND 23);

12 ALTER TABLE "member" ADD COLUMN "notification_sent" TIMESTAMP;

13 ALTER TABLE "member" ADD

14 CONSTRAINT "notification_dow_requires_notification_hour"

15 CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL);

17 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none'::"notify_level";

19 DROP VIEW "selected_event_seen_by_member";

20 DROP VIEW "event_seen_by_member";

22 ALTER TABLE "member" DROP COLUMN "notify_level";

24 DROP TYPE "notify_level";

26 COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications';

27 COMMENT ON COLUMN "member"."notification_counter" IS 'Sequential number of next scheduled notification message (used as a seed for pseudo-random initiative selection algorithm)';

28 COMMENT ON COLUMN "member"."notification_sample_size" IS 'Number of featured initiatives per issue in scheduled notification messages';

29 COMMENT ON COLUMN "member"."notification_dow" IS 'Day of week for scheduled notifications (NULL to receive a daily digest)';

30 COMMENT ON COLUMN "member"."notification_hour" IS 'Time of day when scheduled notifications are sent out';

31 COMMENT ON COLUMN "member"."notification_sent" IS 'Timestamp of last scheduled notification mail that has been sent out';

33 ALTER TABLE "rendered_member_statement" ALTER COLUMN "member_id" SET DATA TYPE INT4;

35 DROP VIEW "expired_session";

37 ALTER TABLE "session" ALTER COLUMN "member_id" SET DATA TYPE INT4;

39 CREATE VIEW "expired_session" AS

40 SELECT * FROM "session" WHERE now() > "expiry";

41 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD

42 DELETE FROM "session" WHERE "ident" = OLD."ident";

44 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';

45 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';

47 CREATE TABLE "subscription" (

48 PRIMARY KEY ("member_id", "unit_id"),

49 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

50 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );

51 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");

53 COMMENT ON TABLE "subscription" IS 'An entry in this table denotes that the member wishes to receive notifications regardless of his/her privileges in the given unit';

55 CREATE TABLE "ignored_area" (

56 PRIMARY KEY ("member_id", "area_id"),

57 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

58 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );

59 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");

61 COMMENT ON TABLE "ignored_area" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given subject area unless he/she declared interested in a particular issue';

63 ALTER TABLE "ignored_initiative" DROP CONSTRAINT "ignored_initiative_pkey";

64 DROP INDEX "ignored_initiative_member_id_idx";

66 ALTER TABLE "ignored_initiative" ADD PRIMARY KEY ("member_id", "initiative_id");

67 CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id");

69 COMMENT ON TABLE "ignored_initiative" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given initiative';

71 ALTER TABLE "notification_sent" RENAME TO "notification_event_sent";

72 ALTER INDEX "notification_sent_singleton_idx" RENAME TO "notification_event_sent_singleton_idx";

74 CREATE TABLE "notification_initiative_sent" (

75 PRIMARY KEY ("member_id", "initiative_id"),

76 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

77 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

78 "last_draft_id" INT8 NOT NULL,

79 "last_suggestion_id" INT8 );

80 CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id");

82 COMMENT ON TABLE "notification_initiative_sent" IS 'Information which initiatives have been promoted to a member in a scheduled notification mail';

84 COMMENT ON COLUMN "notification_initiative_sent"."last_draft_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';

85 COMMENT ON COLUMN "notification_initiative_sent"."last_suggestion_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';

87 CREATE TABLE "newsletter" (

88 "id" SERIAL4 PRIMARY KEY,

89 "published" TIMESTAMPTZ NOT NULL,

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

91 "include_all_members" BOOLEAN NOT NULL,

92 "sent" TIMESTAMPTZ,

93 "subject" TEXT NOT NULL,

94 "content" TEXT NOT NULL );

95 CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");

96 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;

97 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");

99 COMMENT ON TABLE "newsletter" IS 'Contains newsletters created by administrators to be sent out and for further reference';

100

101 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter is to be sent out (and made available in the frontend)';

102 COMMENT ON COLUMN "newsletter"."unit_id" IS 'If set, only members with voting right in the given unit are considered to be recipients';

103 COMMENT ON COLUMN "newsletter"."include_all_members" IS 'TRUE = include all members regardless of their ''disable_notifications'' setting';

104 COMMENT ON COLUMN "newsletter"."sent" IS 'Timestamp when the newsletter has been mailed out';

105 COMMENT ON COLUMN "newsletter"."subject" IS 'Subject line (e.g. to be used for the email)';

106 COMMENT ON COLUMN "newsletter"."content" IS 'Plain text content of the newsletter';

107

108 CREATE OR REPLACE FUNCTION "issue_requires_first_initiative_trigger"()

109 RETURNS TRIGGER

110 LANGUAGE 'plpgsql' VOLATILE AS $$

111 BEGIN

112 IF NOT EXISTS (

113 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"

114 ) THEN

115 RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING

116 ERRCODE = 'integrity_constraint_violation',

117 HINT = 'Create issue, initiative, and draft within the same transaction.';

118 END IF;

119 RETURN NULL;

120 END;

121 $$;

122

123 CREATE OR REPLACE FUNCTION "initiative_requires_first_draft_trigger"()

124 RETURNS TRIGGER

125 LANGUAGE 'plpgsql' VOLATILE AS $$

126 BEGIN

127 IF NOT EXISTS (

128 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"

129 ) THEN

130 RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING

131 ERRCODE = 'integrity_constraint_violation',

132 HINT = 'Create issue, initiative and draft within the same transaction.';

133 END IF;

134 RETURN NULL;

135 END;

136 $$;

137

138 CREATE OR REPLACE FUNCTION "suggestion_requires_first_opinion_trigger"()

139 RETURNS TRIGGER

140 LANGUAGE 'plpgsql' VOLATILE AS $$

141 BEGIN

142 IF NOT EXISTS (

143 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"

144 ) THEN

145 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING

146 ERRCODE = 'integrity_constraint_violation',

147 HINT = 'Create suggestion and opinion within the same transaction.';

148 END IF;

149 RETURN NULL;

150 END;

151 $$;

152

153 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()

154 RETURNS TRIGGER

155 LANGUAGE 'plpgsql' VOLATILE AS $$

156 DECLARE

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

158 "issue_row" "issue"%ROWTYPE;

159 BEGIN

160 IF EXISTS (

161 SELECT NULL FROM "temporary_transaction_data"

162 WHERE "txid" = txid_current()

163 AND "key" = 'override_protection_triggers'

164 AND "value" = TRUE::TEXT

165 ) THEN

166 RETURN NULL;

167 END IF;

168 IF TG_OP = 'DELETE' THEN

169 "issue_id_v" := OLD."issue_id";

170 ELSE

171 "issue_id_v" := NEW."issue_id";

172 END IF;

173 SELECT INTO "issue_row" * FROM "issue"

174 WHERE "id" = "issue_id_v" FOR SHARE;

175 IF (

176 "issue_row"."closed" NOTNULL OR (

177 "issue_row"."state" = 'voting' AND

178 "issue_row"."phase_finished" NOTNULL

179 )

180 ) THEN

181 IF

182 TG_RELID = 'direct_voter'::regclass AND

183 TG_OP = 'UPDATE'

184 THEN

185 IF

186 OLD."issue_id" = NEW."issue_id" AND

187 OLD."member_id" = NEW."member_id" AND

188 OLD."weight" = NEW."weight"

189 THEN

190 RETURN NULL; -- allows changing of voter comment

191 END IF;

192 END IF;

193 RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING

194 ERRCODE = 'integrity_constraint_violation';

195 END IF;

196 RETURN NULL;

197 END;

198 $$;

199

200 CREATE VIEW "event_for_notification" AS

201 SELECT

202 "member"."id" AS "recipient_id",

203 "event".*

204 FROM "member" CROSS JOIN "event"

205 JOIN "issue" ON "issue"."id" = "event"."issue_id"

206 JOIN "area" ON "area"."id" = "issue"."area_id"

207 LEFT JOIN "privilege" ON

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

209 "privilege"."unit_id" = "area"."unit_id" AND

210 "privilege"."voting_right" = TRUE

211 LEFT JOIN "subscription" ON

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

213 "subscription"."unit_id" = "area"."unit_id"

214 LEFT JOIN "ignored_area" ON

215 "ignored_area"."member_id" = "member"."id" AND

216 "ignored_area"."area_id" = "issue"."area_id"

217 LEFT JOIN "interest" ON

218 "interest"."member_id" = "member"."id" AND

219 "interest"."issue_id" = "event"."issue_id"

220 LEFT JOIN "supporter" ON

221 "supporter"."member_id" = "member"."id" AND

222 "supporter"."initiative_id" = "event"."initiative_id"

223 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)

224 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)

225 AND (

226 "event"."event" = 'issue_state_changed'::"event_type" OR

227 ( "event"."event" = 'initiative_revoked'::"event_type" AND

228 "supporter"."member_id" NOTNULL ) );

229

230 COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient';

231

232 COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail';

233

234 CREATE VIEW "updated_initiative" AS

235 SELECT

236 "supporter"."member_id" AS "recipient_id",

237 FALSE AS "featured",

238 "supporter"."initiative_id"

239 FROM "supporter"

240 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"

241 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"

242 LEFT JOIN "notification_initiative_sent" AS "sent" ON

243 "sent"."member_id" = "supporter"."member_id" AND

244 "sent"."initiative_id" = "supporter"."initiative_id"

245 LEFT JOIN "ignored_initiative" ON

246 "ignored_initiative"."member_id" = "supporter"."member_id" AND

247 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"

248 WHERE "issue"."state" IN ('admission', 'discussion')

249 AND "initiative"."revoked" ISNULL

250 AND "ignored_initiative"."member_id" ISNULL

251 AND (

252 EXISTS (

253 SELECT NULL FROM "draft"

254 LEFT JOIN "ignored_member" ON

255 "ignored_member"."member_id" = "supporter"."member_id" AND

256 "ignored_member"."other_member_id" = "draft"."author_id"

257 WHERE "draft"."initiative_id" = "supporter"."initiative_id"

258 AND "draft"."id" > "supporter"."draft_id"

259 AND "ignored_member"."member_id" ISNULL

260 ) OR EXISTS (

261 SELECT NULL FROM "suggestion"

262 LEFT JOIN "opinion" ON

263 "opinion"."member_id" = "supporter"."member_id" AND

264 "opinion"."suggestion_id" = "suggestion"."id"

265 LEFT JOIN "ignored_member" ON

266 "ignored_member"."member_id" = "supporter"."member_id" AND

267 "ignored_member"."other_member_id" = "suggestion"."author_id"

268 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"

269 AND "opinion"."member_id" ISNULL

270 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)

271 AND "ignored_member"."member_id" ISNULL

272 )

273 );

274

275 COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"';

276

277 CREATE FUNCTION "featured_initiative"

278 ( "recipient_id_p" "member"."id"%TYPE,

279 "area_id_p" "area"."id"%TYPE )

280 RETURNS SETOF "initiative"."id"%TYPE

281 LANGUAGE 'plpgsql' STABLE AS $$

282 DECLARE

283 "counter_v" "member"."notification_counter"%TYPE;

284 "sample_size_v" "member"."notification_sample_size"%TYPE;

285 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]

286 "match_v" BOOLEAN;

287 "member_id_v" "member"."id"%TYPE;

288 "seed_v" TEXT;

289 "initiative_id_v" "initiative"."id"%TYPE;

290 BEGIN

291 SELECT "notification_counter", "notification_sample_size"

292 INTO "counter_v", "sample_size_v"

293 FROM "member" WHERE "id" = "recipient_id_p";

294 "initiative_id_ary" := '{}';

295 LOOP

296 "match_v" := FALSE;

297 FOR "member_id_v", "seed_v" IN

298 SELECT * FROM (

299 SELECT DISTINCT

300 "supporter"."member_id",

301 md5(

302 "recipient_id_p" || '-' ||

303 "counter_v" || '-' ||

304 "area_id_p" || '-' ||

305 "supporter"."member_id"

306 ) AS "seed"

307 FROM "supporter"

308 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"

309 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"

310 WHERE "supporter"."member_id" != "recipient_id_p"

311 AND "issue"."area_id" = "area_id_p"

312 AND "issue"."state" IN ('admission', 'discussion', 'verification')

313 ) AS "subquery"

314 ORDER BY "seed"

315 LOOP

316 SELECT "initiative"."id" INTO "initiative_id_v"

317 FROM "initiative"

318 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"

319 JOIN "area" ON "area"."id" = "issue"."area_id"

320 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"

321 LEFT JOIN "supporter" AS "self_support" ON

322 "self_support"."initiative_id" = "initiative"."id" AND

323 "self_support"."member_id" = "recipient_id_p"

324 LEFT JOIN "privilege" ON

325 "privilege"."member_id" = "recipient_id_p" AND

326 "privilege"."unit_id" = "area"."unit_id" AND

327 "privilege"."voting_right" = TRUE

328 LEFT JOIN "subscription" ON

329 "subscription"."member_id" = "recipient_id_p" AND

330 "subscription"."unit_id" = "area"."unit_id"

331 LEFT JOIN "ignored_initiative" ON

332 "ignored_initiative"."member_id" = "recipient_id_p" AND

333 "ignored_initiative"."initiative_id" = "initiative"."id"

334 WHERE "supporter"."member_id" = "member_id_v"

335 AND "issue"."area_id" = "area_id_p"

336 AND "issue"."state" IN ('admission', 'discussion', 'verification')

337 AND "initiative"."revoked" ISNULL

338 AND "self_support"."member_id" ISNULL

339 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]

340 AND (

341 "privilege"."member_id" NOTNULL OR

342 "subscription"."member_id" NOTNULL )

343 AND "ignored_initiative"."member_id" ISNULL

344 AND NOT EXISTS (

345 SELECT NULL FROM "draft"

346 JOIN "ignored_member" ON

347 "ignored_member"."member_id" = "recipient_id_p" AND

348 "ignored_member"."other_member_id" = "draft"."author_id"

349 WHERE "draft"."initiative_id" = "initiative"."id"

350 )

351 ORDER BY md5("seed_v" || '-' || "initiative"."id")

352 LIMIT 1;

353 IF FOUND THEN

354 "match_v" := TRUE;

355 RETURN NEXT "initiative_id_v";

356 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN

357 RETURN;

358 END IF;

359 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";

360 END IF;

361 END LOOP;

362 EXIT WHEN NOT "match_v";

363 END LOOP;

364 RETURN;

365 END;

366 $$;

367

368 COMMENT ON FUNCTION "featured_initiative"

369 ( "recipient_id_p" "member"."id"%TYPE,

370 "area_id_p" "area"."id"%TYPE )

371 IS 'Helper function for view "updated_or_featured_initiative"';

372

373 CREATE VIEW "updated_or_featured_initiative" AS

374 SELECT

375 "subquery".*,

376 NOT EXISTS (

377 SELECT NULL FROM "initiative" AS "better_initiative"

378 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"

379 AND

380 ( COALESCE("better_initiative"."supporter_count", -1),

381 -"better_initiative"."id" ) >

382 ( COALESCE("initiative"."supporter_count", -1),

383 -"initiative"."id" )

384 ) AS "leading"

385 FROM (

386 SELECT * FROM "updated_initiative"

387 UNION ALL

388 SELECT

389 "member"."id" AS "recipient_id",

390 TRUE AS "featured",

391 "featured_initiative_id" AS "initiative_id"

392 FROM "member" CROSS JOIN "area"

393 CROSS JOIN LATERAL

394 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"

395 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"

396 ) AS "subquery"

397 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";

398

399 COMMENT ON VIEW "updated_or_featured_initiative" IS 'Initiatives to be included in a scheduled notification mail because (a) they have been updated or (b) they are featured';

400

401 COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail';

402 COMMENT ON COLUMN "updated_or_featured_initiative"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")';

403 COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';

404 COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';

405

406 CREATE VIEW "leading_complement_initiative" AS

407 SELECT * FROM (

408 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")

409 "uf_initiative"."recipient_id",

410 FALSE AS "featured",

411 "uf_initiative"."initiative_id",

412 TRUE AS "leading"

413 FROM "updated_or_featured_initiative" AS "uf_initiative"

414 JOIN "initiative" AS "uf_initiative_full" ON

415 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"

416 JOIN "initiative" ON

417 "initiative"."issue_id" = "uf_initiative_full"."issue_id"

418 WHERE "initiative"."revoked" ISNULL

419 ORDER BY

420 "uf_initiative"."recipient_id",

421 "initiative"."issue_id",

422 "initiative"."supporter_count" DESC,

423 "initiative"."id"

424 ) AS "subquery"

425 WHERE NOT EXISTS (

426 SELECT NULL FROM "updated_or_featured_initiative" AS "other"

427 WHERE "other"."recipient_id" = "subquery"."recipient_id"

428 AND "other"."initiative_id" = "subquery"."initiative_id"

429 );

430

431 COMMENT ON VIEW "leading_complement_initiative" IS 'Helper view for view "unfiltered_initiative_for_notification" in order to always include the most supported initiative of an issue';

432 COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view';

433 COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';

434 COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view';

435

436 CREATE VIEW "unfiltered_initiative_for_notification" AS

437 SELECT

438 "subquery".*,

439 "supporter"."member_id" NOTNULL AS "supported",

440 CASE WHEN "supporter"."member_id" NOTNULL THEN

441 EXISTS (

442 SELECT NULL FROM "draft"

443 WHERE "draft"."initiative_id" = "subquery"."initiative_id"

444 AND "draft"."id" > "supporter"."draft_id"

445 )

446 ELSE

447 EXISTS (

448 SELECT NULL FROM "draft"

449 WHERE "draft"."initiative_id" = "subquery"."initiative_id"

450 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)

451 )

452 END AS "new_draft",

453 CASE WHEN "supporter"."member_id" NOTNULL THEN

454 ( SELECT count(1) FROM "suggestion"

455 LEFT JOIN "opinion" ON

456 "opinion"."member_id" = "supporter"."member_id" AND

457 "opinion"."suggestion_id" = "suggestion"."id"

458 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"

459 AND "opinion"."member_id" ISNULL

460 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)

461 )

462 ELSE

463 ( SELECT count(1) FROM "suggestion"

464 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"

465 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)

466 )

467 END AS "new_suggestion_count"

468 FROM (

469 SELECT * FROM "updated_or_featured_initiative"

470 UNION ALL

471 SELECT * FROM "leading_complement_initiative"

472 ) AS "subquery"

473 LEFT JOIN "supporter" ON

474 "supporter"."member_id" = "subquery"."recipient_id" AND

475 "supporter"."initiative_id" = "subquery"."initiative_id"

476 LEFT JOIN "notification_initiative_sent" AS "sent" ON

477 "sent"."member_id" = "subquery"."recipient_id" AND

478 "sent"."initiative_id" = "subquery"."initiative_id";

479

480 COMMENT ON VIEW "unfiltered_initiative_for_notification" IS 'Helper view which simply combines the views "updated_or_featured_initiative" and "leading_complement_initiative" and adds columns "supported", "new_draft", and "new_suggestion_count';

481

482 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';

483 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_draft" IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)';

484 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")';

485

486 CREATE VIEW "initiative_for_notification" AS

487 SELECT "unfiltered1".*

488 FROM "unfiltered_initiative_for_notification" "unfiltered1"

489 JOIN "initiative" AS "initiative1" ON

490 "initiative1"."id" = "unfiltered1"."initiative_id"

491 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"

492 WHERE EXISTS (

493 SELECT NULL

494 FROM "unfiltered_initiative_for_notification" "unfiltered2"

495 JOIN "initiative" AS "initiative2" ON

496 "initiative2"."id" = "unfiltered2"."initiative_id"

497 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"

498 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"

499 AND "issue1"."area_id" = "issue2"."area_id"

500 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )

501 );

502

503 COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail';

504

505 COMMENT ON COLUMN "initiative_for_notification"."recipient_id" IS '"id" of the member who receives the notification mail';

506 COMMENT ON COLUMN "initiative_for_notification"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")';

507 COMMENT ON COLUMN "initiative_for_notification"."initiative_id" IS '"id" of the initiative to be included in the notification mail';

508 COMMENT ON COLUMN "initiative_for_notification"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';

509 COMMENT ON COLUMN "initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';

510 COMMENT ON COLUMN "initiative_for_notification"."new_draft" IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)';

511 COMMENT ON COLUMN "initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")';

512

513 CREATE VIEW "scheduled_notification_to_send" AS

514 SELECT * FROM (

515 SELECT

516 "id" AS "recipient_id",

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

518 ( "notification_sent"::DATE + CASE

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

520 THEN 0 ELSE 1 END

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

522 ELSE

523 ( "notification_sent"::DATE +

524 ( 7 + "notification_dow" -

525 EXTRACT(DOW FROM

526 ( "notification_sent"::DATE + CASE

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

528 THEN 0 ELSE 1 END

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

530 )::INTEGER

531 ) % 7 +

532 CASE

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

534 THEN 0 ELSE 1

535 END

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

537 END AS "pending"

538 FROM (

539 SELECT

540 "id",

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

542 "notification_dow",

543 "notification_hour"

544 FROM "member"

545 WHERE "disable_notifications" = FALSE

546 AND "notification_hour" NOTNULL

547 ) AS "subquery1"

548 ) AS "subquery2"

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

550

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

552

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

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

555

556 CREATE VIEW "newsletter_to_send" AS

557 SELECT

558 "member"."id" AS "recipient_id",

559 "newsletter"."id" AS "newsletter_id",

560 "newsletter"."published"

561 FROM "newsletter" CROSS JOIN "member"

562 LEFT JOIN "privilege" ON

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

564 "privilege"."unit_id" = "newsletter"."unit_id" AND

565 "privilege"."voting_right" = TRUE

566 LEFT JOIN "subscription" ON

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

568 "subscription"."unit_id" = "newsletter"."unit_id"

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

570 AND "newsletter"."sent" ISNULL

571 AND "member"."locked" = FALSE

572 AND (

573 "member"."disable_notifications" = FALSE OR

574 "newsletter"."include_all_members" = TRUE )

575 AND (

576 "newsletter"."unit_id" ISNULL OR

577 "privilege"."member_id" NOTNULL OR

578 "subscription"."member_id" NOTNULL );

579

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

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

582

583 CREATE OR REPLACE FUNCTION "require_transaction_isolation"()

584 RETURNS VOID

585 LANGUAGE 'plpgsql' VOLATILE AS $$

586 BEGIN

587 IF

588 current_setting('transaction_isolation') NOT IN

589 ('repeatable read', 'serializable')

590 THEN

591 RAISE EXCEPTION 'Insufficient transaction isolation level' USING

592 HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';

593 END IF;

594 RETURN;

595 END;

596 $$;

597

598 CREATE FUNCTION "get_initiatives_for_notification"

599 ( "recipient_id_p" "member"."id"%TYPE )

600 RETURNS SETOF "initiative_for_notification"

601 LANGUAGE 'plpgsql' VOLATILE AS $$

602 DECLARE

603 "result_row" "initiative_for_notification"%ROWTYPE;

604 "last_draft_id_v" "draft"."id"%TYPE;

605 "last_suggestion_id_v" "suggestion"."id"%TYPE;

606 BEGIN

607 PERFORM "require_transaction_isolation"();

608 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;

609 FOR "result_row" IN

610 SELECT * FROM "initiative_for_notification"

611 WHERE "recipient_id" = "recipient_id_p"

612 LOOP

613 SELECT "id" INTO "last_draft_id_v" FROM "draft"

614 WHERE "draft"."initiative_id" = "result_row"."initiative_id"

615 ORDER BY "id" DESC LIMIT 1;

616 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"

617 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"

618 ORDER BY "id" DESC LIMIT 1;

619 /* compatibility with PostgreSQL 9.1 */

620 DELETE FROM "notification_initiative_sent"

621 WHERE "member_id" = "recipient_id_p"

622 AND "initiative_id" = "result_row"."initiative_id";

623 INSERT INTO "notification_initiative_sent"

624 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")

625 VALUES (

626 "recipient_id_p",

627 "result_row"."initiative_id",

628 "last_draft_id_v",

629 "last_suggestion_id_v" );

630 /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher

631 INSERT INTO "notification_initiative_sent"

632 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")

633 VALUES (

634 "recipient_id_p",

635 "result_row"."initiative_id",

636 "last_draft_id_v",

637 "last_suggestion_id_v" )

638 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET

639 "last_draft_id" = "last_draft_id_v",

640 "last_suggestion_id" = "last_suggestion_id_v";

641 */

642 RETURN NEXT "result_row";

643 END LOOP;

644 DELETE FROM "notification_initiative_sent"

645 USING "initiative", "issue"

646 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"

647 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"

648 AND "issue"."id" = "initiative"."issue_id"

649 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );

650 UPDATE "member" SET

651 "notification_counter" = "notification_counter" + 1,

652 "notification_sent" = now()

653 WHERE "id" = "recipient_id_p";

654 RETURN;

655 END;

656 $$;

657

658 COMMENT ON FUNCTION "get_initiatives_for_notification"

659 ( "member"."id"%TYPE )

660 IS 'Returns rows from view "initiative_for_notification" for a given recipient while updating table "notification_initiative_sent" and columns "notification_counter" and "notification_sent" of "member" table';

661

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

663 RETURNS VOID

664 LANGUAGE 'plpgsql' VOLATILE AS $$

665 BEGIN

666 UPDATE "member" SET

667 "last_login" = NULL,

668 "last_delegation_check" = NULL,

669 "login" = NULL,

670 "password" = NULL,

671 "authority" = NULL,

672 "authority_uid" = NULL,

673 "authority_login" = NULL,

674 "locked" = TRUE,

675 "active" = FALSE,

676 "notify_email" = NULL,

677 "notify_email_unconfirmed" = NULL,

678 "notify_email_secret" = NULL,

679 "notify_email_secret_expiry" = NULL,

680 "notify_email_lock_expiry" = NULL,

681 "disable_notifications" = NULL,

682 "notification_counter" = NULL,

683 "notification_sample_size" = NULL,

684 "notification_dow" = NULL,

685 "notification_hour" = NULL,

686 "login_recovery_expiry" = NULL,

687 "password_reset_secret" = NULL,

688 "password_reset_secret_expiry" = NULL,

689 "organizational_unit" = NULL,

690 "internal_posts" = NULL,

691 "realname" = NULL,

692 "birthday" = NULL,

693 "address" = NULL,

694 "email" = NULL,

695 "xmpp_address" = NULL,

696 "website" = NULL,

697 "phone" = NULL,

698 "mobile_phone" = NULL,

699 "profession" = NULL,

700 "external_memberships" = NULL,

701 "external_posts" = NULL,

702 "statement" = NULL

703 WHERE "id" = "member_id_p";

704 -- "text_search_data" is updated by triggers

705 DELETE FROM "setting" WHERE "member_id" = "member_id_p";

706 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";

707 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";

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

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

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

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

712 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";

713 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";

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

715 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";

716 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";

717 DELETE FROM "membership" WHERE "member_id" = "member_id_p";

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

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

720 DELETE FROM "direct_voter" USING "issue"

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

722 AND "issue"."closed" ISNULL

723 AND "member_id" = "member_id_p";

724 RETURN;

725 END;

726 $$;

727

728 CREATE OR REPLACE FUNCTION "delete_private_data"()

729 RETURNS VOID

730 LANGUAGE 'plpgsql' VOLATILE AS $$

731 BEGIN

732 DELETE FROM "temporary_transaction_data";

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

734 UPDATE "member" SET

735 "invite_code" = NULL,

736 "invite_code_expiry" = NULL,

737 "admin_comment" = NULL,

738 "last_login" = NULL,

739 "last_delegation_check" = NULL,

740 "login" = NULL,

741 "password" = NULL,

742 "authority" = NULL,

743 "authority_uid" = NULL,

744 "authority_login" = NULL,

745 "lang" = NULL,

746 "notify_email" = NULL,

747 "notify_email_unconfirmed" = NULL,

748 "notify_email_secret" = NULL,

749 "notify_email_secret_expiry" = NULL,

750 "notify_email_lock_expiry" = NULL,

751 "disable_notifications" = NULL,

752 "notification_counter" = NULL,

753 "notification_sample_size" = NULL,

754 "notification_dow" = NULL,

755 "notification_hour" = NULL,

756 "login_recovery_expiry" = NULL,

757 "password_reset_secret" = NULL,

758 "password_reset_secret_expiry" = NULL,

759 "organizational_unit" = NULL,

760 "internal_posts" = NULL,

761 "realname" = NULL,

762 "birthday" = NULL,

763 "address" = NULL,

764 "email" = NULL,

765 "xmpp_address" = NULL,

766 "website" = NULL,

767 "phone" = NULL,

768 "mobile_phone" = NULL,

769 "profession" = NULL,

770 "external_memberships" = NULL,

771 "external_posts" = NULL,

772 "formatting_engine" = NULL,

773 "statement" = NULL;

774 -- "text_search_data" is updated by triggers

775 DELETE FROM "setting";

776 DELETE FROM "setting_map";

777 DELETE FROM "member_relation_setting";

778 DELETE FROM "member_image";

779 DELETE FROM "contact";

780 DELETE FROM "ignored_member";

781 DELETE FROM "session";

782 DELETE FROM "area_setting";

783 DELETE FROM "issue_setting";

784 DELETE FROM "ignored_initiative";

785 DELETE FROM "initiative_setting";

786 DELETE FROM "suggestion_setting";

787 DELETE FROM "non_voter";

788 DELETE FROM "direct_voter" USING "issue"

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

790 AND "issue"."closed" ISNULL;

791 RETURN;

792 END;

793 $$;

794

795 COMMIT;