liquid_feedback_core: fd330c887a83 update/core-update.v3.1.0-v3.2.0.sql

liquid_feedback_core

view update/core-update.v3.1.0-v3.2.0.sql @ 510:fd330c887a83

Updated update script to v3.2.0

author	jbe
date	Sat Apr 16 19:52:19 2016 +0200 (2016-04-16)
parents	f5aba1fbb845
children	48761b189274

line source

1 BEGIN;

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))

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 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 ALTER TABLE "member" DROP COLUMN "notify_level";

21 DROP VIEW "selected_event_seen_by_member";

22 DROP VIEW "event_seen_by_member";

23 DROP TYPE "notify_level";

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

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

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

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

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

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

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

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

35 CREATE TABLE "subscription" (

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

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

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

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

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

43 CREATE TABLE "ignored_area" (

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

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

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

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

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

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

52 DROP INDEX "ignored_initiative_member_id_idx";

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

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

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

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

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

62 CREATE TABLE "notification_initiative_sent" (

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

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

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

66 "last_draft_id" INT8 NOT NULL,

67 "last_suggestion_id" INT8 );

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

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

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

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

75 CREATE TABLE "newsletter" (

76 "id" SERIAL4 PRIMARY KEY,

77 "published" TIMESTAMPTZ NOT NULL,

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

79 "include_all_members" BOOLEAN NOT NULL,

80 "sent" TIMESTAMPTZ,

81 "subject" TEXT NOT NULL,

82 "content" TEXT NOT NULL );

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

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

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

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

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

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

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

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

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

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

96 CREATE OR REPLACE FUNCTION "issue_requires_first_initiative_trigger"()

97 RETURNS TRIGGER

98 LANGUAGE 'plpgsql' VOLATILE AS $$

99 BEGIN

100 IF NOT EXISTS (

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

102 ) THEN

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

104 ERRCODE = 'integrity_constraint_violation',

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

106 END IF;

107 RETURN NULL;

108 END;

109 $$;

110

111 CREATE OR REPLACE FUNCTION "initiative_requires_first_draft_trigger"()

112 RETURNS TRIGGER

113 LANGUAGE 'plpgsql' VOLATILE AS $$

114 BEGIN

115 IF NOT EXISTS (

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

117 ) THEN

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

119 ERRCODE = 'integrity_constraint_violation',

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

121 END IF;

122 RETURN NULL;

123 END;

124 $$;

125

126 CREATE OR REPLACE FUNCTION "suggestion_requires_first_opinion_trigger"()

127 RETURNS TRIGGER

128 LANGUAGE 'plpgsql' VOLATILE AS $$

129 BEGIN

130 IF NOT EXISTS (

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

132 ) THEN

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

134 ERRCODE = 'integrity_constraint_violation',

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

136 END IF;

137 RETURN NULL;

138 END;

139 $$;

140

141 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()

142 RETURNS TRIGGER

143 LANGUAGE 'plpgsql' VOLATILE AS $$

144 DECLARE

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

146 "issue_row" "issue"%ROWTYPE;

147 BEGIN

148 IF EXISTS (

149 SELECT NULL FROM "temporary_transaction_data"

150 WHERE "txid" = txid_current()

151 AND "key" = 'override_protection_triggers'

152 AND "value" = TRUE::TEXT

153 ) THEN

154 RETURN NULL;

155 END IF;

156 IF TG_OP = 'DELETE' THEN

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

158 ELSE

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

160 END IF;

161 SELECT INTO "issue_row" * FROM "issue"

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

163 IF (

164 "issue_row"."closed" NOTNULL OR (

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

166 "issue_row"."phase_finished" NOTNULL

167 )

168 ) THEN

169 IF

170 TG_RELID = 'direct_voter'::regclass AND

171 TG_OP = 'UPDATE'

172 THEN

173 IF

174 OLD."issue_id" = NEW."issue_id" AND

175 OLD."member_id" = NEW."member_id" AND

176 OLD."weight" = NEW."weight"

177 THEN

178 RETURN NULL; -- allows changing of voter comment

179 END IF;

180 END IF;

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

182 ERRCODE = 'integrity_constraint_violation';

183 END IF;

184 RETURN NULL;

185 END;

186 $$;

187

188 CREATE VIEW "event_for_notification" AS

189 SELECT

190 "member"."id" AS "recipient_id",

191 "event".*

192 FROM "member" CROSS JOIN "event"

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

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

195 LEFT JOIN "privilege" ON

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

197 "privilege"."unit_id" = "area"."unit_id" AND

198 "privilege"."voting_right" = TRUE

199 LEFT JOIN "subscription" ON

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

201 "subscription"."unit_id" = "area"."unit_id"

202 LEFT JOIN "ignored_area" ON

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

204 "ignored_area"."area_id" = "issue"."area_id"

205 LEFT JOIN "interest" ON

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

207 "interest"."issue_id" = "event"."issue_id"

208 LEFT JOIN "supporter" ON

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

210 "supporter"."initiative_id" = "event"."initiative_id"

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

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

213 AND (

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

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

216 "supporter"."member_id" NOTNULL ) );

217

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

219

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

221

222 CREATE VIEW "updated_initiative" AS

223 SELECT

224 "supporter"."member_id" AS "recipient_id",

225 FALSE AS "featured",

226 "supporter"."initiative_id"

227 FROM "supporter"

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

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

230 LEFT JOIN "notification_initiative_sent" AS "sent" ON

231 "sent"."member_id" = "supporter"."member_id" AND

232 "sent"."initiative_id" = "supporter"."initiative_id"

233 LEFT JOIN "ignored_initiative" ON

234 "ignored_initiative"."member_id" = "supporter"."member_id" AND

235 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"

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

237 AND "initiative"."revoked" ISNULL

238 AND "ignored_initiative"."member_id" ISNULL

239 AND (

240 EXISTS (

241 SELECT NULL FROM "draft"

242 LEFT JOIN "ignored_member" ON

243 "ignored_member"."member_id" = "supporter"."member_id" AND

244 "ignored_member"."other_member_id" = "draft"."author_id"

245 WHERE "draft"."initiative_id" = "supporter"."initiative_id"

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

247 AND "ignored_member"."member_id" ISNULL

248 ) OR EXISTS (

249 SELECT NULL FROM "suggestion"

250 LEFT JOIN "opinion" ON

251 "opinion"."member_id" = "supporter"."member_id" AND

252 "opinion"."suggestion_id" = "suggestion"."id"

253 LEFT JOIN "ignored_member" ON

254 "ignored_member"."member_id" = "supporter"."member_id" AND

255 "ignored_member"."other_member_id" = "suggestion"."author_id"

256 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"

257 AND "opinion"."member_id" ISNULL

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

259 AND "ignored_member"."member_id" ISNULL

260 )

261 );

262

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

264

265 CREATE FUNCTION "featured_initiative"

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

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

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

269 LANGUAGE 'plpgsql' STABLE AS $$

270 DECLARE

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

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

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

274 "match_v" BOOLEAN;

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

276 "seed_v" TEXT;

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

278 BEGIN

279 SELECT "notification_counter", "notification_sample_size"

280 INTO "counter_v", "sample_size_v"

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

282 "initiative_id_ary" := '{}';

283 LOOP

284 "match_v" := FALSE;

285 FOR "member_id_v", "seed_v" IN

286 SELECT * FROM (

287 SELECT DISTINCT

288 "supporter"."member_id",

289 md5(

290 "recipient_id_p" || '-' ||

291 "counter_v" || '-' ||

292 "area_id_p" || '-' ||

293 "supporter"."member_id"

294 ) AS "seed"

295 FROM "supporter"

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

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

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

299 AND "issue"."area_id" = "area_id_p"

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

301 ) AS "subquery"

302 ORDER BY "seed"

303 LOOP

304 SELECT "initiative"."id" INTO "initiative_id_v"

305 FROM "initiative"

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

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

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

309 LEFT JOIN "supporter" AS "self_support" ON

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

311 "self_support"."member_id" = "recipient_id_p"

312 LEFT JOIN "privilege" ON

313 "privilege"."member_id" = "recipient_id_p" AND

314 "privilege"."unit_id" = "area"."unit_id" AND

315 "privilege"."voting_right" = TRUE

316 LEFT JOIN "subscription" ON

317 "subscription"."member_id" = "recipient_id_p" AND

318 "subscription"."unit_id" = "area"."unit_id"

319 LEFT JOIN "ignored_initiative" ON

320 "ignored_initiative"."member_id" = "recipient_id_p" AND

321 "ignored_initiative"."initiative_id" = "initiative"."id"

322 WHERE "supporter"."member_id" = "member_id_v"

323 AND "issue"."area_id" = "area_id_p"

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

325 AND "initiative"."revoked" ISNULL

326 AND "self_support"."member_id" ISNULL

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

328 AND (

329 "privilege"."member_id" NOTNULL OR

330 "subscription"."member_id" NOTNULL )

331 AND "ignored_initiative"."member_id" ISNULL

332 AND NOT EXISTS (

333 SELECT NULL FROM "draft"

334 JOIN "ignored_member" ON

335 "ignored_member"."member_id" = "recipient_id_p" AND

336 "ignored_member"."other_member_id" = "draft"."author_id"

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

338 )

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

340 LIMIT 1;

341 IF FOUND THEN

342 "match_v" := TRUE;

343 RETURN NEXT "initiative_id_v";

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

345 RETURN;

346 END IF;

347 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";

348 END IF;

349 END LOOP;

350 EXIT WHEN NOT "match_v";

351 END LOOP;

352 RETURN;

353 END;

354 $$;

355

356 COMMENT ON FUNCTION "featured_initiative"

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

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

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

360

361 CREATE VIEW "updated_or_featured_initiative" AS

362 SELECT

363 "subquery".*,

364 NOT EXISTS (

365 SELECT NULL FROM "initiative" AS "better_initiative"

366 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"

367 AND

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

369 -"better_initiative"."id" ) >

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

371 -"initiative"."id" )

372 ) AS "leading"

373 FROM (

374 SELECT * FROM "updated_initiative"

375 UNION ALL

376 SELECT

377 "member"."id" AS "recipient_id",

378 TRUE AS "featured",

379 "featured_initiative_id" AS "initiative_id"

380 FROM "member" CROSS JOIN "area"

381 CROSS JOIN LATERAL

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

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

384 ) AS "subquery"

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

386

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

388

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

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

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

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

393

394 CREATE VIEW "leading_complement_initiative" AS

395 SELECT * FROM (

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

397 "uf_initiative"."recipient_id",

398 FALSE AS "featured",

399 "uf_initiative"."initiative_id",

400 TRUE AS "leading"

401 FROM "updated_or_featured_initiative" AS "uf_initiative"

402 JOIN "initiative" AS "uf_initiative_full" ON

403 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"

404 JOIN "initiative" ON

405 "initiative"."issue_id" = "uf_initiative_full"."issue_id"

406 WHERE "initiative"."revoked" ISNULL

407 ORDER BY

408 "uf_initiative"."recipient_id",

409 "initiative"."issue_id",

410 "initiative"."supporter_count" DESC,

411 "initiative"."id"

412 ) AS "subquery"

413 WHERE NOT EXISTS (

414 SELECT NULL FROM "updated_or_featured_initiative" AS "other"

415 WHERE "other"."recipient_id" = "subquery"."recipient_id"

416 AND "other"."initiative_id" = "subquery"."initiative_id"

417 );

418

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

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

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

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

423

424 CREATE VIEW "unfiltered_initiative_for_notification" AS

425 SELECT

426 "subquery".*,

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

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

429 EXISTS (

430 SELECT NULL FROM "draft"

431 WHERE "draft"."initiative_id" = "subquery"."initiative_id"

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

433 )

434 ELSE

435 EXISTS (

436 SELECT NULL FROM "draft"

437 WHERE "draft"."initiative_id" = "subquery"."initiative_id"

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

439 )

440 END AS "new_draft",

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

442 ( SELECT count(1) FROM "suggestion"

443 LEFT JOIN "opinion" ON

444 "opinion"."member_id" = "supporter"."member_id" AND

445 "opinion"."suggestion_id" = "suggestion"."id"

446 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"

447 AND "opinion"."member_id" ISNULL

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

449 )

450 ELSE

451 ( SELECT count(1) FROM "suggestion"

452 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"

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

454 )

455 END AS "new_suggestion_count"

456 FROM (

457 SELECT * FROM "updated_or_featured_initiative"

458 UNION ALL

459 SELECT * FROM "leading_complement_initiative"

460 ) AS "subquery"

461 LEFT JOIN "supporter" ON

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

463 "supporter"."initiative_id" = "subquery"."initiative_id"

464 LEFT JOIN "notification_initiative_sent" AS "sent" ON

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

466 "sent"."initiative_id" = "subquery"."initiative_id";

467

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

469

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

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

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

473

474 CREATE VIEW "initiative_for_notification" AS

475 SELECT "unfiltered1".*

476 FROM "unfiltered_initiative_for_notification" "unfiltered1"

477 JOIN "initiative" AS "initiative1" ON

478 "initiative1"."id" = "unfiltered1"."initiative_id"

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

480 WHERE EXISTS (

481 SELECT NULL

482 FROM "unfiltered_initiative_for_notification" "unfiltered2"

483 JOIN "initiative" AS "initiative2" ON

484 "initiative2"."id" = "unfiltered2"."initiative_id"

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

486 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"

487 AND "issue1"."area_id" = "issue2"."area_id"

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

489 );

490

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

492

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

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

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

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

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

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

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

500

501 CREATE VIEW "scheduled_notification_to_send" AS

502 SELECT * FROM (

503 SELECT

504 "id" AS "recipient_id",

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

506 ( "notification_sent"::DATE + CASE

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

508 THEN 0 ELSE 1 END

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

510 ELSE

511 ( "notification_sent"::DATE +

512 ( 7 + "notification_dow" -

513 EXTRACT(DOW FROM

514 ( "notification_sent"::DATE + CASE

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

516 THEN 0 ELSE 1 END

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

518 )::INTEGER

519 ) % 7 +

520 CASE

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

522 THEN 0 ELSE 1

523 END

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

525 END AS "pending"

526 FROM (

527 SELECT

528 "id",

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

530 "notification_dow",

531 "notification_hour"

532 FROM "member"

533 WHERE "disable_notifications" = FALSE

534 AND "notification_hour" NOTNULL

535 ) AS "subquery1"

536 ) AS "subquery2"

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

538

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

540

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

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

543

544 CREATE VIEW "newsletter_to_send" AS

545 SELECT

546 "member"."id" AS "recipient_id",

547 "newsletter"."id" AS "newsletter_id"

548 FROM "newsletter" CROSS JOIN "member"

549 LEFT JOIN "privilege" ON

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

551 "privilege"."unit_id" = "newsletter"."unit_id" AND

552 "privilege"."voting_right" = TRUE

553 LEFT JOIN "subscription" ON

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

555 "subscription"."unit_id" = "newsletter"."unit_id"

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

557 AND "newsletter"."sent" ISNULL

558 AND "member"."locked" = FALSE

559 AND (

560 "member"."disable_notifications" = FALSE OR

561 "newsletter"."include_all_members" = TRUE )

562 AND (

563 "newsletter"."unit_id" ISNULL OR

564 "privilege"."member_id" NOTNULL OR

565 "subscription"."member_id" NOTNULL );

566

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

568

569 CREATE OR REPLACE FUNCTION "require_transaction_isolation"()

570 RETURNS VOID

571 LANGUAGE 'plpgsql' VOLATILE AS $$

572 BEGIN

573 IF

574 current_setting('transaction_isolation') NOT IN

575 ('repeatable read', 'serializable')

576 THEN

577 RAISE EXCEPTION 'Insufficient transaction isolation level' USING

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

579 END IF;

580 RETURN;

581 END;

582 $$;

583

584 CREATE FUNCTION "get_initiatives_for_notification"

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

586 RETURNS SETOF "initiative_for_notification"

587 LANGUAGE 'plpgsql' VOLATILE AS $$

588 DECLARE

589 "result_row" "initiative_for_notification"%ROWTYPE;

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

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

592 BEGIN

593 PERFORM "require_transaction_isolation"();

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

595 FOR "result_row" IN

596 SELECT * FROM "initiative_for_notification"

597 WHERE "recipient_id" = "recipient_id_p"

598 LOOP

599 SELECT "id" INTO "last_draft_id_v" FROM "draft"

600 WHERE "draft"."initiative_id" = "result_row"."initiative_id"

601 ORDER BY "id" DESC LIMIT 1;

602 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"

603 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"

604 ORDER BY "id" DESC LIMIT 1;

605 INSERT INTO "notification_initiative_sent"

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

607 VALUES (

608 "recipient_id_p",

609 "result_row"."initiative_id",

610 "last_draft_id_v",

611 "last_suggestion_id_v" )

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

613 "last_draft_id" = CASE

614 WHEN "notification_initiative_sent"."last_draft_id" > "last_draft_id_v"

615 THEN "notification_initiative_sent"."last_draft_id"

616 ELSE "last_draft_id_v"

617 END,

618 "last_suggestion_id" = CASE

619 WHEN "notification_initiative_sent"."last_suggestion_id" > "last_suggestion_id_v"

620 THEN "notification_initiative_sent"."last_suggestion_id"

621 ELSE "last_suggestion_id_v"

622 END;

623 RETURN NEXT "result_row";

624 END LOOP;

625 DELETE FROM "notification_initiative_sent"

626 USING "initiative", "issue"

627 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"

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

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

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

631 UPDATE "member" SET

632 "notification_counter" = "notification_counter" + 1,

633 "notification_sent" = now()

634 WHERE "id" = "recipient_id_p";

635 RETURN;

636 END;

637 $$;

638

639 -- TODO: comment missing

640

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

642 RETURNS VOID

643 LANGUAGE 'plpgsql' VOLATILE AS $$

644 BEGIN

645 UPDATE "member" SET

646 "last_login" = NULL,

647 "last_delegation_check" = NULL,

648 "login" = NULL,

649 "password" = NULL,

650 "authority" = NULL,

651 "authority_uid" = NULL,

652 "authority_login" = NULL,

653 "locked" = TRUE,

654 "active" = FALSE,

655 "notify_email" = NULL,

656 "notify_email_unconfirmed" = NULL,

657 "notify_email_secret" = NULL,

658 "notify_email_secret_expiry" = NULL,

659 "notify_email_lock_expiry" = NULL,

660 "disable_notifications" = NULL,

661 "notification_counter" = NULL,

662 "notification_sample_size" = NULL,

663 "notification_dow" = NULL,

664 "notification_hour" = NULL,

665 "login_recovery_expiry" = NULL,

666 "password_reset_secret" = NULL,

667 "password_reset_secret_expiry" = NULL,

668 "organizational_unit" = NULL,

669 "internal_posts" = NULL,

670 "realname" = NULL,

671 "birthday" = NULL,

672 "address" = NULL,

673 "email" = NULL,

674 "xmpp_address" = NULL,

675 "website" = NULL,

676 "phone" = NULL,

677 "mobile_phone" = NULL,

678 "profession" = NULL,

679 "external_memberships" = NULL,

680 "external_posts" = NULL,

681 "statement" = NULL

682 WHERE "id" = "member_id_p";

683 -- "text_search_data" is updated by triggers

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

699 DELETE FROM "direct_voter" USING "issue"

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

701 AND "issue"."closed" ISNULL

702 AND "member_id" = "member_id_p";

703 RETURN;

704 END;

705 $$;

706

707 CREATE OR REPLACE FUNCTION "delete_private_data"()

708 RETURNS VOID

709 LANGUAGE 'plpgsql' VOLATILE AS $$

710 BEGIN

711 DELETE FROM "temporary_transaction_data";

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

713 UPDATE "member" SET

714 "invite_code" = NULL,

715 "invite_code_expiry" = NULL,

716 "admin_comment" = NULL,

717 "last_login" = NULL,

718 "last_delegation_check" = NULL,

719 "login" = NULL,

720 "password" = NULL,

721 "authority" = NULL,

722 "authority_uid" = NULL,

723 "authority_login" = NULL,

724 "lang" = NULL,

725 "notify_email" = NULL,

726 "notify_email_unconfirmed" = NULL,

727 "notify_email_secret" = NULL,

728 "notify_email_secret_expiry" = NULL,

729 "notify_email_lock_expiry" = NULL,

730 "disable_notifications" = NULL,

731 "notification_counter" = NULL,

732 "notification_sample_size" = NULL,

733 "notification_dow" = NULL,

734 "notification_hour" = NULL,

735 "login_recovery_expiry" = NULL,

736 "password_reset_secret" = NULL,

737 "password_reset_secret_expiry" = NULL,

738 "organizational_unit" = NULL,

739 "internal_posts" = NULL,

740 "realname" = NULL,

741 "birthday" = NULL,

742 "address" = NULL,

743 "email" = NULL,

744 "xmpp_address" = NULL,

745 "website" = NULL,

746 "phone" = NULL,

747 "mobile_phone" = NULL,

748 "profession" = NULL,

749 "external_memberships" = NULL,

750 "external_posts" = NULL,

751 "formatting_engine" = NULL,

752 "statement" = NULL;

753 -- "text_search_data" is updated by triggers

754 DELETE FROM "setting";

755 DELETE FROM "setting_map";

756 DELETE FROM "member_relation_setting";

757 DELETE FROM "member_image";

758 DELETE FROM "contact";

759 DELETE FROM "ignored_member";

760 DELETE FROM "session";

761 DELETE FROM "area_setting";

762 DELETE FROM "issue_setting";

763 DELETE FROM "ignored_initiative";

764 DELETE FROM "initiative_setting";

765 DELETE FROM "suggestion_setting";

766 DELETE FROM "non_voter";

767 DELETE FROM "direct_voter" USING "issue"

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

769 AND "issue"."closed" ISNULL;

770 RETURN;

771 END;

772 $$;

773

774 COMMIT;