liquid_feedback_core: 61fab7216893 update/core-update.v3.1.0-v3.2.0.sql

liquid_feedback_core

view update/core-update.v3.1.0-v3.2.0.sql @ 512:61fab7216893

Corrected execution order in update script to v3.2.0

author	jbe
date	Sat Apr 16 19:57:53 2016 +0200 (2016-04-16)
parents	48761b189274
children	bca63fc70e73

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

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

36 CREATE TABLE "subscription" (

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

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

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

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

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

44 CREATE TABLE "ignored_area" (

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

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

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

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

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

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

53 DROP INDEX "ignored_initiative_member_id_idx";

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

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

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

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

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

63 CREATE TABLE "notification_initiative_sent" (

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

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

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

67 "last_draft_id" INT8 NOT NULL,

68 "last_suggestion_id" INT8 );

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

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

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

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

76 CREATE TABLE "newsletter" (

77 "id" SERIAL4 PRIMARY KEY,

78 "published" TIMESTAMPTZ NOT NULL,

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

80 "include_all_members" BOOLEAN NOT NULL,

81 "sent" TIMESTAMPTZ,

82 "subject" TEXT NOT NULL,

83 "content" TEXT NOT NULL );

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

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

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

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

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

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

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

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

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

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

97 CREATE OR REPLACE FUNCTION "issue_requires_first_initiative_trigger"()

98 RETURNS TRIGGER

99 LANGUAGE 'plpgsql' VOLATILE AS $$

100 BEGIN

101 IF NOT EXISTS (

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

103 ) THEN

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

105 ERRCODE = 'integrity_constraint_violation',

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

107 END IF;

108 RETURN NULL;

109 END;

110 $$;

111

112 CREATE OR REPLACE FUNCTION "initiative_requires_first_draft_trigger"()

113 RETURNS TRIGGER

114 LANGUAGE 'plpgsql' VOLATILE AS $$

115 BEGIN

116 IF NOT EXISTS (

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

118 ) THEN

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

120 ERRCODE = 'integrity_constraint_violation',

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

122 END IF;

123 RETURN NULL;

124 END;

125 $$;

126

127 CREATE OR REPLACE FUNCTION "suggestion_requires_first_opinion_trigger"()

128 RETURNS TRIGGER

129 LANGUAGE 'plpgsql' VOLATILE AS $$

130 BEGIN

131 IF NOT EXISTS (

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

133 ) THEN

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

135 ERRCODE = 'integrity_constraint_violation',

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

137 END IF;

138 RETURN NULL;

139 END;

140 $$;

141

142 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()

143 RETURNS TRIGGER

144 LANGUAGE 'plpgsql' VOLATILE AS $$

145 DECLARE

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

147 "issue_row" "issue"%ROWTYPE;

148 BEGIN

149 IF EXISTS (

150 SELECT NULL FROM "temporary_transaction_data"

151 WHERE "txid" = txid_current()

152 AND "key" = 'override_protection_triggers'

153 AND "value" = TRUE::TEXT

154 ) THEN

155 RETURN NULL;

156 END IF;

157 IF TG_OP = 'DELETE' THEN

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

159 ELSE

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

161 END IF;

162 SELECT INTO "issue_row" * FROM "issue"

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

164 IF (

165 "issue_row"."closed" NOTNULL OR (

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

167 "issue_row"."phase_finished" NOTNULL

168 )

169 ) THEN

170 IF

171 TG_RELID = 'direct_voter'::regclass AND

172 TG_OP = 'UPDATE'

173 THEN

174 IF

175 OLD."issue_id" = NEW."issue_id" AND

176 OLD."member_id" = NEW."member_id" AND

177 OLD."weight" = NEW."weight"

178 THEN

179 RETURN NULL; -- allows changing of voter comment

180 END IF;

181 END IF;

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

183 ERRCODE = 'integrity_constraint_violation';

184 END IF;

185 RETURN NULL;

186 END;

187 $$;

188

189 CREATE VIEW "event_for_notification" AS

190 SELECT

191 "member"."id" AS "recipient_id",

192 "event".*

193 FROM "member" CROSS JOIN "event"

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

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

196 LEFT JOIN "privilege" ON

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

198 "privilege"."unit_id" = "area"."unit_id" AND

199 "privilege"."voting_right" = TRUE

200 LEFT JOIN "subscription" ON

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

202 "subscription"."unit_id" = "area"."unit_id"

203 LEFT JOIN "ignored_area" ON

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

205 "ignored_area"."area_id" = "issue"."area_id"

206 LEFT JOIN "interest" ON

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

208 "interest"."issue_id" = "event"."issue_id"

209 LEFT JOIN "supporter" ON

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

211 "supporter"."initiative_id" = "event"."initiative_id"

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

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

214 AND (

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

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

217 "supporter"."member_id" NOTNULL ) );

218

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

220

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

222

223 CREATE VIEW "updated_initiative" AS

224 SELECT

225 "supporter"."member_id" AS "recipient_id",

226 FALSE AS "featured",

227 "supporter"."initiative_id"

228 FROM "supporter"

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

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

231 LEFT JOIN "notification_initiative_sent" AS "sent" ON

232 "sent"."member_id" = "supporter"."member_id" AND

233 "sent"."initiative_id" = "supporter"."initiative_id"

234 LEFT JOIN "ignored_initiative" ON

235 "ignored_initiative"."member_id" = "supporter"."member_id" AND

236 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"

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

238 AND "initiative"."revoked" ISNULL

239 AND "ignored_initiative"."member_id" ISNULL

240 AND (

241 EXISTS (

242 SELECT NULL FROM "draft"

243 LEFT JOIN "ignored_member" ON

244 "ignored_member"."member_id" = "supporter"."member_id" AND

245 "ignored_member"."other_member_id" = "draft"."author_id"

246 WHERE "draft"."initiative_id" = "supporter"."initiative_id"

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

248 AND "ignored_member"."member_id" ISNULL

249 ) OR EXISTS (

250 SELECT NULL FROM "suggestion"

251 LEFT JOIN "opinion" ON

252 "opinion"."member_id" = "supporter"."member_id" AND

253 "opinion"."suggestion_id" = "suggestion"."id"

254 LEFT JOIN "ignored_member" ON

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

256 "ignored_member"."other_member_id" = "suggestion"."author_id"

257 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"

258 AND "opinion"."member_id" ISNULL

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

260 AND "ignored_member"."member_id" ISNULL

261 )

262 );

263

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

265

266 CREATE FUNCTION "featured_initiative"

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

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

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

270 LANGUAGE 'plpgsql' STABLE AS $$

271 DECLARE

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

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

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

275 "match_v" BOOLEAN;

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

277 "seed_v" TEXT;

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

279 BEGIN

280 SELECT "notification_counter", "notification_sample_size"

281 INTO "counter_v", "sample_size_v"

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

283 "initiative_id_ary" := '{}';

284 LOOP

285 "match_v" := FALSE;

286 FOR "member_id_v", "seed_v" IN

287 SELECT * FROM (

288 SELECT DISTINCT

289 "supporter"."member_id",

290 md5(

291 "recipient_id_p" || '-' ||

292 "counter_v" || '-' ||

293 "area_id_p" || '-' ||

294 "supporter"."member_id"

295 ) AS "seed"

296 FROM "supporter"

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

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

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

300 AND "issue"."area_id" = "area_id_p"

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

302 ) AS "subquery"

303 ORDER BY "seed"

304 LOOP

305 SELECT "initiative"."id" INTO "initiative_id_v"

306 FROM "initiative"

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

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

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

310 LEFT JOIN "supporter" AS "self_support" ON

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

312 "self_support"."member_id" = "recipient_id_p"

313 LEFT JOIN "privilege" ON

314 "privilege"."member_id" = "recipient_id_p" AND

315 "privilege"."unit_id" = "area"."unit_id" AND

316 "privilege"."voting_right" = TRUE

317 LEFT JOIN "subscription" ON

318 "subscription"."member_id" = "recipient_id_p" AND

319 "subscription"."unit_id" = "area"."unit_id"

320 LEFT JOIN "ignored_initiative" ON

321 "ignored_initiative"."member_id" = "recipient_id_p" AND

322 "ignored_initiative"."initiative_id" = "initiative"."id"

323 WHERE "supporter"."member_id" = "member_id_v"

324 AND "issue"."area_id" = "area_id_p"

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

326 AND "initiative"."revoked" ISNULL

327 AND "self_support"."member_id" ISNULL

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

329 AND (

330 "privilege"."member_id" NOTNULL OR

331 "subscription"."member_id" NOTNULL )

332 AND "ignored_initiative"."member_id" ISNULL

333 AND NOT EXISTS (

334 SELECT NULL FROM "draft"

335 JOIN "ignored_member" ON

336 "ignored_member"."member_id" = "recipient_id_p" AND

337 "ignored_member"."other_member_id" = "draft"."author_id"

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

339 )

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

341 LIMIT 1;

342 IF FOUND THEN

343 "match_v" := TRUE;

344 RETURN NEXT "initiative_id_v";

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

346 RETURN;

347 END IF;

348 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";

349 END IF;

350 END LOOP;

351 EXIT WHEN NOT "match_v";

352 END LOOP;

353 RETURN;

354 END;

355 $$;

356

357 COMMENT ON FUNCTION "featured_initiative"

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

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

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

361

362 CREATE VIEW "updated_or_featured_initiative" AS

363 SELECT

364 "subquery".*,

365 NOT EXISTS (

366 SELECT NULL FROM "initiative" AS "better_initiative"

367 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"

368 AND

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

370 -"better_initiative"."id" ) >

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

372 -"initiative"."id" )

373 ) AS "leading"

374 FROM (

375 SELECT * FROM "updated_initiative"

376 UNION ALL

377 SELECT

378 "member"."id" AS "recipient_id",

379 TRUE AS "featured",

380 "featured_initiative_id" AS "initiative_id"

381 FROM "member" CROSS JOIN "area"

382 CROSS JOIN LATERAL

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

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

385 ) AS "subquery"

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

387

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

389

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

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

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

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

394

395 CREATE VIEW "leading_complement_initiative" AS

396 SELECT * FROM (

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

398 "uf_initiative"."recipient_id",

399 FALSE AS "featured",

400 "uf_initiative"."initiative_id",

401 TRUE AS "leading"

402 FROM "updated_or_featured_initiative" AS "uf_initiative"

403 JOIN "initiative" AS "uf_initiative_full" ON

404 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"

405 JOIN "initiative" ON

406 "initiative"."issue_id" = "uf_initiative_full"."issue_id"

407 WHERE "initiative"."revoked" ISNULL

408 ORDER BY

409 "uf_initiative"."recipient_id",

410 "initiative"."issue_id",

411 "initiative"."supporter_count" DESC,

412 "initiative"."id"

413 ) AS "subquery"

414 WHERE NOT EXISTS (

415 SELECT NULL FROM "updated_or_featured_initiative" AS "other"

416 WHERE "other"."recipient_id" = "subquery"."recipient_id"

417 AND "other"."initiative_id" = "subquery"."initiative_id"

418 );

419

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

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

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

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

424

425 CREATE VIEW "unfiltered_initiative_for_notification" AS

426 SELECT

427 "subquery".*,

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

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

430 EXISTS (

431 SELECT NULL FROM "draft"

432 WHERE "draft"."initiative_id" = "subquery"."initiative_id"

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

434 )

435 ELSE

436 EXISTS (

437 SELECT NULL FROM "draft"

438 WHERE "draft"."initiative_id" = "subquery"."initiative_id"

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

440 )

441 END AS "new_draft",

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

443 ( SELECT count(1) FROM "suggestion"

444 LEFT JOIN "opinion" ON

445 "opinion"."member_id" = "supporter"."member_id" AND

446 "opinion"."suggestion_id" = "suggestion"."id"

447 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"

448 AND "opinion"."member_id" ISNULL

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

450 )

451 ELSE

452 ( SELECT count(1) FROM "suggestion"

453 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"

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

455 )

456 END AS "new_suggestion_count"

457 FROM (

458 SELECT * FROM "updated_or_featured_initiative"

459 UNION ALL

460 SELECT * FROM "leading_complement_initiative"

461 ) AS "subquery"

462 LEFT JOIN "supporter" ON

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

464 "supporter"."initiative_id" = "subquery"."initiative_id"

465 LEFT JOIN "notification_initiative_sent" AS "sent" ON

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

467 "sent"."initiative_id" = "subquery"."initiative_id";

468

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

470

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

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

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

474

475 CREATE VIEW "initiative_for_notification" AS

476 SELECT "unfiltered1".*

477 FROM "unfiltered_initiative_for_notification" "unfiltered1"

478 JOIN "initiative" AS "initiative1" ON

479 "initiative1"."id" = "unfiltered1"."initiative_id"

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

481 WHERE EXISTS (

482 SELECT NULL

483 FROM "unfiltered_initiative_for_notification" "unfiltered2"

484 JOIN "initiative" AS "initiative2" ON

485 "initiative2"."id" = "unfiltered2"."initiative_id"

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

487 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"

488 AND "issue1"."area_id" = "issue2"."area_id"

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

490 );

491

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

493

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

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

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

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

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

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

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

501

502 CREATE VIEW "scheduled_notification_to_send" AS

503 SELECT * FROM (

504 SELECT

505 "id" AS "recipient_id",

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

507 ( "notification_sent"::DATE + CASE

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

509 THEN 0 ELSE 1 END

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

511 ELSE

512 ( "notification_sent"::DATE +

513 ( 7 + "notification_dow" -

514 EXTRACT(DOW FROM

515 ( "notification_sent"::DATE + CASE

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

517 THEN 0 ELSE 1 END

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

519 )::INTEGER

520 ) % 7 +

521 CASE

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

523 THEN 0 ELSE 1

524 END

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

526 END AS "pending"

527 FROM (

528 SELECT

529 "id",

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

531 "notification_dow",

532 "notification_hour"

533 FROM "member"

534 WHERE "disable_notifications" = FALSE

535 AND "notification_hour" NOTNULL

536 ) AS "subquery1"

537 ) AS "subquery2"

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

539

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

541

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

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

544

545 CREATE VIEW "newsletter_to_send" AS

546 SELECT

547 "member"."id" AS "recipient_id",

548 "newsletter"."id" AS "newsletter_id"

549 FROM "newsletter" CROSS JOIN "member"

550 LEFT JOIN "privilege" ON

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

552 "privilege"."unit_id" = "newsletter"."unit_id" AND

553 "privilege"."voting_right" = TRUE

554 LEFT JOIN "subscription" ON

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

556 "subscription"."unit_id" = "newsletter"."unit_id"

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

558 AND "newsletter"."sent" ISNULL

559 AND "member"."locked" = FALSE

560 AND (

561 "member"."disable_notifications" = FALSE OR

562 "newsletter"."include_all_members" = TRUE )

563 AND (

564 "newsletter"."unit_id" ISNULL OR

565 "privilege"."member_id" NOTNULL OR

566 "subscription"."member_id" NOTNULL );

567

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

569

570 CREATE OR REPLACE FUNCTION "require_transaction_isolation"()

571 RETURNS VOID

572 LANGUAGE 'plpgsql' VOLATILE AS $$

573 BEGIN

574 IF

575 current_setting('transaction_isolation') NOT IN

576 ('repeatable read', 'serializable')

577 THEN

578 RAISE EXCEPTION 'Insufficient transaction isolation level' USING

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

580 END IF;

581 RETURN;

582 END;

583 $$;

584

585 CREATE FUNCTION "get_initiatives_for_notification"

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

587 RETURNS SETOF "initiative_for_notification"

588 LANGUAGE 'plpgsql' VOLATILE AS $$

589 DECLARE

590 "result_row" "initiative_for_notification"%ROWTYPE;

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

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

593 BEGIN

594 PERFORM "require_transaction_isolation"();

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

596 FOR "result_row" IN

597 SELECT * FROM "initiative_for_notification"

598 WHERE "recipient_id" = "recipient_id_p"

599 LOOP

600 SELECT "id" INTO "last_draft_id_v" FROM "draft"

601 WHERE "draft"."initiative_id" = "result_row"."initiative_id"

602 ORDER BY "id" DESC LIMIT 1;

603 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"

604 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"

605 ORDER BY "id" DESC LIMIT 1;

606 INSERT INTO "notification_initiative_sent"

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

608 VALUES (

609 "recipient_id_p",

610 "result_row"."initiative_id",

611 "last_draft_id_v",

612 "last_suggestion_id_v" )

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

614 "last_draft_id" = CASE

615 WHEN "notification_initiative_sent"."last_draft_id" > "last_draft_id_v"

616 THEN "notification_initiative_sent"."last_draft_id"

617 ELSE "last_draft_id_v"

618 END,

619 "last_suggestion_id" = CASE

620 WHEN "notification_initiative_sent"."last_suggestion_id" > "last_suggestion_id_v"

621 THEN "notification_initiative_sent"."last_suggestion_id"

622 ELSE "last_suggestion_id_v"

623 END;

624 RETURN NEXT "result_row";

625 END LOOP;

626 DELETE FROM "notification_initiative_sent"

627 USING "initiative", "issue"

628 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"

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

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

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

632 UPDATE "member" SET

633 "notification_counter" = "notification_counter" + 1,

634 "notification_sent" = now()

635 WHERE "id" = "recipient_id_p";

636 RETURN;

637 END;

638 $$;

639

640 COMMENT ON FUNCTION "get_initiatives_for_notification"

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

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

643

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

645 RETURNS VOID

646 LANGUAGE 'plpgsql' VOLATILE AS $$

647 BEGIN

648 UPDATE "member" SET

649 "last_login" = NULL,

650 "last_delegation_check" = NULL,

651 "login" = NULL,

652 "password" = NULL,

653 "authority" = NULL,

654 "authority_uid" = NULL,

655 "authority_login" = NULL,

656 "locked" = TRUE,

657 "active" = FALSE,

658 "notify_email" = NULL,

659 "notify_email_unconfirmed" = NULL,

660 "notify_email_secret" = NULL,

661 "notify_email_secret_expiry" = NULL,

662 "notify_email_lock_expiry" = NULL,

663 "disable_notifications" = NULL,

664 "notification_counter" = NULL,

665 "notification_sample_size" = NULL,

666 "notification_dow" = NULL,

667 "notification_hour" = NULL,

668 "login_recovery_expiry" = NULL,

669 "password_reset_secret" = NULL,

670 "password_reset_secret_expiry" = NULL,

671 "organizational_unit" = NULL,

672 "internal_posts" = NULL,

673 "realname" = NULL,

674 "birthday" = NULL,

675 "address" = NULL,

676 "email" = NULL,

677 "xmpp_address" = NULL,

678 "website" = NULL,

679 "phone" = NULL,

680 "mobile_phone" = NULL,

681 "profession" = NULL,

682 "external_memberships" = NULL,

683 "external_posts" = NULL,

684 "statement" = NULL

685 WHERE "id" = "member_id_p";

686 -- "text_search_data" is updated by triggers

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

702 DELETE FROM "direct_voter" USING "issue"

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

704 AND "issue"."closed" ISNULL

705 AND "member_id" = "member_id_p";

706 RETURN;

707 END;

708 $$;

709

710 CREATE OR REPLACE FUNCTION "delete_private_data"()

711 RETURNS VOID

712 LANGUAGE 'plpgsql' VOLATILE AS $$

713 BEGIN

714 DELETE FROM "temporary_transaction_data";

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

716 UPDATE "member" SET

717 "invite_code" = NULL,

718 "invite_code_expiry" = NULL,

719 "admin_comment" = NULL,

720 "last_login" = NULL,

721 "last_delegation_check" = NULL,

722 "login" = NULL,

723 "password" = NULL,

724 "authority" = NULL,

725 "authority_uid" = NULL,

726 "authority_login" = NULL,

727 "lang" = NULL,

728 "notify_email" = NULL,

729 "notify_email_unconfirmed" = NULL,

730 "notify_email_secret" = NULL,

731 "notify_email_secret_expiry" = NULL,

732 "notify_email_lock_expiry" = NULL,

733 "disable_notifications" = NULL,

734 "notification_counter" = NULL,

735 "notification_sample_size" = NULL,

736 "notification_dow" = NULL,

737 "notification_hour" = NULL,

738 "login_recovery_expiry" = NULL,

739 "password_reset_secret" = NULL,

740 "password_reset_secret_expiry" = NULL,

741 "organizational_unit" = NULL,

742 "internal_posts" = NULL,

743 "realname" = NULL,

744 "birthday" = NULL,

745 "address" = NULL,

746 "email" = NULL,

747 "xmpp_address" = NULL,

748 "website" = NULL,

749 "phone" = NULL,

750 "mobile_phone" = NULL,

751 "profession" = NULL,

752 "external_memberships" = NULL,

753 "external_posts" = NULL,

754 "formatting_engine" = NULL,

755 "statement" = NULL;

756 -- "text_search_data" is updated by triggers

757 DELETE FROM "setting";

758 DELETE FROM "setting_map";

759 DELETE FROM "member_relation_setting";

760 DELETE FROM "member_image";

761 DELETE FROM "contact";

762 DELETE FROM "ignored_member";

763 DELETE FROM "session";

764 DELETE FROM "area_setting";

765 DELETE FROM "issue_setting";

766 DELETE FROM "ignored_initiative";

767 DELETE FROM "initiative_setting";

768 DELETE FROM "suggestion_setting";

769 DELETE FROM "non_voter";

770 DELETE FROM "direct_voter" USING "issue"

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

772 AND "issue"."closed" ISNULL;

773 RETURN;

774 END;

775 $$;

776

777 COMMIT;