liquid_feedback_core
diff update/core-update.v3.1.0-v3.2.1.sql @ 517:e4f619e87664
Fixed accidental PostgreSQL 9.5 dependency (because of UPSERT aka ON CONFLICT clause)
| author | jbe |
|---|---|
| date | Sat Apr 30 17:03:48 2016 +0200 (2016-04-30) |
| parents | update/core-update.v3.1.0-v3.2.0.sql@ab05fb3bf974 |
| children |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v3.1.0-v3.2.1.sql Sat Apr 30 17:03:48 2016 +0200 1.3 @@ -0,0 +1,795 @@ 1.4 +BEGIN; 1.5 + 1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.7 + SELECT * FROM (VALUES ('3.2.1', 3, 2, 1)) 1.8 + AS "subquery"("string", "major", "minor", "revision"); 1.9 + 1.10 +ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE; 1.11 +ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1; 1.12 +ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3; 1.13 +ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6); 1.14 +ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 DEFAULT floor(random() * 24) CHECK ("notification_hour" BETWEEN 0 AND 23); 1.15 +ALTER TABLE "member" ADD COLUMN "notification_sent" TIMESTAMP; 1.16 +ALTER TABLE "member" ADD 1.17 + CONSTRAINT "notification_dow_requires_notification_hour" 1.18 + CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL); 1.19 + 1.20 +UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none'::"notify_level"; 1.21 + 1.22 +DROP VIEW "selected_event_seen_by_member"; 1.23 +DROP VIEW "event_seen_by_member"; 1.24 + 1.25 +ALTER TABLE "member" DROP COLUMN "notify_level"; 1.26 + 1.27 +DROP TYPE "notify_level"; 1.28 + 1.29 +COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications'; 1.30 +COMMENT ON COLUMN "member"."notification_counter" IS 'Sequential number of next scheduled notification message (used as a seed for pseudo-random initiative selection algorithm)'; 1.31 +COMMENT ON COLUMN "member"."notification_sample_size" IS 'Number of featured initiatives per issue in scheduled notification messages'; 1.32 +COMMENT ON COLUMN "member"."notification_dow" IS 'Day of week for scheduled notifications (NULL to receive a daily digest)'; 1.33 +COMMENT ON COLUMN "member"."notification_hour" IS 'Time of day when scheduled notifications are sent out'; 1.34 +COMMENT ON COLUMN "member"."notification_sent" IS 'Timestamp of last scheduled notification mail that has been sent out'; 1.35 + 1.36 +ALTER TABLE "rendered_member_statement" ALTER COLUMN "member_id" SET DATA TYPE INT4; 1.37 + 1.38 +DROP VIEW "expired_session"; 1.39 + 1.40 +ALTER TABLE "session" ALTER COLUMN "member_id" SET DATA TYPE INT4; 1.41 + 1.42 +CREATE VIEW "expired_session" AS 1.43 + SELECT * FROM "session" WHERE now() > "expiry"; 1.44 +CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD 1.45 + DELETE FROM "session" WHERE "ident" = OLD."ident"; 1.46 + 1.47 +COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible'; 1.48 +COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"'; 1.49 + 1.50 +CREATE TABLE "subscription" ( 1.51 + PRIMARY KEY ("member_id", "unit_id"), 1.52 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.53 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.54 +CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id"); 1.55 + 1.56 +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'; 1.57 + 1.58 +CREATE TABLE "ignored_area" ( 1.59 + PRIMARY KEY ("member_id", "area_id"), 1.60 + "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.61 + "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.62 +CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id"); 1.63 + 1.64 +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'; 1.65 + 1.66 +ALTER TABLE "ignored_initiative" DROP CONSTRAINT "ignored_initiative_pkey"; 1.67 +DROP INDEX "ignored_initiative_member_id_idx"; 1.68 + 1.69 +ALTER TABLE "ignored_initiative" ADD PRIMARY KEY ("member_id", "initiative_id"); 1.70 +CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id"); 1.71 + 1.72 +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'; 1.73 + 1.74 +ALTER TABLE "notification_sent" RENAME TO "notification_event_sent"; 1.75 +ALTER INDEX "notification_sent_singleton_idx" RENAME TO "notification_event_sent_singleton_idx"; 1.76 + 1.77 +CREATE TABLE "notification_initiative_sent" ( 1.78 + PRIMARY KEY ("member_id", "initiative_id"), 1.79 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.80 + "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.81 + "last_draft_id" INT8 NOT NULL, 1.82 + "last_suggestion_id" INT8 ); 1.83 +CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id"); 1.84 + 1.85 +COMMENT ON TABLE "notification_initiative_sent" IS 'Information which initiatives have been promoted to a member in a scheduled notification mail'; 1.86 + 1.87 +COMMENT ON COLUMN "notification_initiative_sent"."last_draft_id" IS 'Current (i.e. last) draft_id when initiative had been promoted'; 1.88 +COMMENT ON COLUMN "notification_initiative_sent"."last_suggestion_id" IS 'Current (i.e. last) draft_id when initiative had been promoted'; 1.89 + 1.90 +CREATE TABLE "newsletter" ( 1.91 + "id" SERIAL4 PRIMARY KEY, 1.92 + "published" TIMESTAMPTZ NOT NULL, 1.93 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.94 + "include_all_members" BOOLEAN NOT NULL, 1.95 + "sent" TIMESTAMPTZ, 1.96 + "subject" TEXT NOT NULL, 1.97 + "content" TEXT NOT NULL ); 1.98 +CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published"); 1.99 +CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL; 1.100 +CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published"); 1.101 + 1.102 +COMMENT ON TABLE "newsletter" IS 'Contains newsletters created by administrators to be sent out and for further reference'; 1.103 + 1.104 +COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter is to be sent out (and made available in the frontend)'; 1.105 +COMMENT ON COLUMN "newsletter"."unit_id" IS 'If set, only members with voting right in the given unit are considered to be recipients'; 1.106 +COMMENT ON COLUMN "newsletter"."include_all_members" IS 'TRUE = include all members regardless of their ''disable_notifications'' setting'; 1.107 +COMMENT ON COLUMN "newsletter"."sent" IS 'Timestamp when the newsletter has been mailed out'; 1.108 +COMMENT ON COLUMN "newsletter"."subject" IS 'Subject line (e.g. to be used for the email)'; 1.109 +COMMENT ON COLUMN "newsletter"."content" IS 'Plain text content of the newsletter'; 1.110 + 1.111 +CREATE OR REPLACE FUNCTION "issue_requires_first_initiative_trigger"() 1.112 + RETURNS TRIGGER 1.113 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.114 + BEGIN 1.115 + IF NOT EXISTS ( 1.116 + SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id" 1.117 + ) THEN 1.118 + RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING 1.119 + ERRCODE = 'integrity_constraint_violation', 1.120 + HINT = 'Create issue, initiative, and draft within the same transaction.'; 1.121 + END IF; 1.122 + RETURN NULL; 1.123 + END; 1.124 + $$; 1.125 + 1.126 +CREATE OR REPLACE FUNCTION "initiative_requires_first_draft_trigger"() 1.127 + RETURNS TRIGGER 1.128 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.129 + BEGIN 1.130 + IF NOT EXISTS ( 1.131 + SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id" 1.132 + ) THEN 1.133 + RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING 1.134 + ERRCODE = 'integrity_constraint_violation', 1.135 + HINT = 'Create issue, initiative and draft within the same transaction.'; 1.136 + END IF; 1.137 + RETURN NULL; 1.138 + END; 1.139 + $$; 1.140 + 1.141 +CREATE OR REPLACE FUNCTION "suggestion_requires_first_opinion_trigger"() 1.142 + RETURNS TRIGGER 1.143 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.144 + BEGIN 1.145 + IF NOT EXISTS ( 1.146 + SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id" 1.147 + ) THEN 1.148 + RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING 1.149 + ERRCODE = 'integrity_constraint_violation', 1.150 + HINT = 'Create suggestion and opinion within the same transaction.'; 1.151 + END IF; 1.152 + RETURN NULL; 1.153 + END; 1.154 + $$; 1.155 + 1.156 +CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"() 1.157 + RETURNS TRIGGER 1.158 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.159 + DECLARE 1.160 + "issue_id_v" "issue"."id"%TYPE; 1.161 + "issue_row" "issue"%ROWTYPE; 1.162 + BEGIN 1.163 + IF EXISTS ( 1.164 + SELECT NULL FROM "temporary_transaction_data" 1.165 + WHERE "txid" = txid_current() 1.166 + AND "key" = 'override_protection_triggers' 1.167 + AND "value" = TRUE::TEXT 1.168 + ) THEN 1.169 + RETURN NULL; 1.170 + END IF; 1.171 + IF TG_OP = 'DELETE' THEN 1.172 + "issue_id_v" := OLD."issue_id"; 1.173 + ELSE 1.174 + "issue_id_v" := NEW."issue_id"; 1.175 + END IF; 1.176 + SELECT INTO "issue_row" * FROM "issue" 1.177 + WHERE "id" = "issue_id_v" FOR SHARE; 1.178 + IF ( 1.179 + "issue_row"."closed" NOTNULL OR ( 1.180 + "issue_row"."state" = 'voting' AND 1.181 + "issue_row"."phase_finished" NOTNULL 1.182 + ) 1.183 + ) THEN 1.184 + IF 1.185 + TG_RELID = 'direct_voter'::regclass AND 1.186 + TG_OP = 'UPDATE' 1.187 + THEN 1.188 + IF 1.189 + OLD."issue_id" = NEW."issue_id" AND 1.190 + OLD."member_id" = NEW."member_id" AND 1.191 + OLD."weight" = NEW."weight" 1.192 + THEN 1.193 + RETURN NULL; -- allows changing of voter comment 1.194 + END IF; 1.195 + END IF; 1.196 + RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING 1.197 + ERRCODE = 'integrity_constraint_violation'; 1.198 + END IF; 1.199 + RETURN NULL; 1.200 + END; 1.201 + $$; 1.202 + 1.203 +CREATE VIEW "event_for_notification" AS 1.204 + SELECT 1.205 + "member"."id" AS "recipient_id", 1.206 + "event".* 1.207 + FROM "member" CROSS JOIN "event" 1.208 + JOIN "issue" ON "issue"."id" = "event"."issue_id" 1.209 + JOIN "area" ON "area"."id" = "issue"."area_id" 1.210 + LEFT JOIN "privilege" ON 1.211 + "privilege"."member_id" = "member"."id" AND 1.212 + "privilege"."unit_id" = "area"."unit_id" AND 1.213 + "privilege"."voting_right" = TRUE 1.214 + LEFT JOIN "subscription" ON 1.215 + "subscription"."member_id" = "member"."id" AND 1.216 + "subscription"."unit_id" = "area"."unit_id" 1.217 + LEFT JOIN "ignored_area" ON 1.218 + "ignored_area"."member_id" = "member"."id" AND 1.219 + "ignored_area"."area_id" = "issue"."area_id" 1.220 + LEFT JOIN "interest" ON 1.221 + "interest"."member_id" = "member"."id" AND 1.222 + "interest"."issue_id" = "event"."issue_id" 1.223 + LEFT JOIN "supporter" ON 1.224 + "supporter"."member_id" = "member"."id" AND 1.225 + "supporter"."initiative_id" = "event"."initiative_id" 1.226 + WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL) 1.227 + AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL) 1.228 + AND ( 1.229 + "event"."event" = 'issue_state_changed'::"event_type" OR 1.230 + ( "event"."event" = 'initiative_revoked'::"event_type" AND 1.231 + "supporter"."member_id" NOTNULL ) ); 1.232 + 1.233 +COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient'; 1.234 + 1.235 +COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail'; 1.236 + 1.237 +CREATE VIEW "updated_initiative" AS 1.238 + SELECT 1.239 + "supporter"."member_id" AS "recipient_id", 1.240 + FALSE AS "featured", 1.241 + "supporter"."initiative_id" 1.242 + FROM "supporter" 1.243 + JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id" 1.244 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.245 + LEFT JOIN "notification_initiative_sent" AS "sent" ON 1.246 + "sent"."member_id" = "supporter"."member_id" AND 1.247 + "sent"."initiative_id" = "supporter"."initiative_id" 1.248 + LEFT JOIN "ignored_initiative" ON 1.249 + "ignored_initiative"."member_id" = "supporter"."member_id" AND 1.250 + "ignored_initiative"."initiative_id" = "supporter"."initiative_id" 1.251 + WHERE "issue"."state" IN ('admission', 'discussion') 1.252 + AND "initiative"."revoked" ISNULL 1.253 + AND "ignored_initiative"."member_id" ISNULL 1.254 + AND ( 1.255 + EXISTS ( 1.256 + SELECT NULL FROM "draft" 1.257 + LEFT JOIN "ignored_member" ON 1.258 + "ignored_member"."member_id" = "supporter"."member_id" AND 1.259 + "ignored_member"."other_member_id" = "draft"."author_id" 1.260 + WHERE "draft"."initiative_id" = "supporter"."initiative_id" 1.261 + AND "draft"."id" > "supporter"."draft_id" 1.262 + AND "ignored_member"."member_id" ISNULL 1.263 + ) OR EXISTS ( 1.264 + SELECT NULL FROM "suggestion" 1.265 + LEFT JOIN "opinion" ON 1.266 + "opinion"."member_id" = "supporter"."member_id" AND 1.267 + "opinion"."suggestion_id" = "suggestion"."id" 1.268 + LEFT JOIN "ignored_member" ON 1.269 + "ignored_member"."member_id" = "supporter"."member_id" AND 1.270 + "ignored_member"."other_member_id" = "suggestion"."author_id" 1.271 + WHERE "suggestion"."initiative_id" = "supporter"."initiative_id" 1.272 + AND "opinion"."member_id" ISNULL 1.273 + AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE) 1.274 + AND "ignored_member"."member_id" ISNULL 1.275 + ) 1.276 + ); 1.277 + 1.278 +COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"'; 1.279 + 1.280 +CREATE FUNCTION "featured_initiative" 1.281 + ( "recipient_id_p" "member"."id"%TYPE, 1.282 + "area_id_p" "area"."id"%TYPE ) 1.283 + RETURNS SETOF "initiative"."id"%TYPE 1.284 + LANGUAGE 'plpgsql' STABLE AS $$ 1.285 + DECLARE 1.286 + "counter_v" "member"."notification_counter"%TYPE; 1.287 + "sample_size_v" "member"."notification_sample_size"%TYPE; 1.288 + "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[] 1.289 + "match_v" BOOLEAN; 1.290 + "member_id_v" "member"."id"%TYPE; 1.291 + "seed_v" TEXT; 1.292 + "initiative_id_v" "initiative"."id"%TYPE; 1.293 + BEGIN 1.294 + SELECT "notification_counter", "notification_sample_size" 1.295 + INTO "counter_v", "sample_size_v" 1.296 + FROM "member" WHERE "id" = "recipient_id_p"; 1.297 + "initiative_id_ary" := '{}'; 1.298 + LOOP 1.299 + "match_v" := FALSE; 1.300 + FOR "member_id_v", "seed_v" IN 1.301 + SELECT * FROM ( 1.302 + SELECT DISTINCT 1.303 + "supporter"."member_id", 1.304 + md5( 1.305 + "recipient_id_p" || '-' || 1.306 + "counter_v" || '-' || 1.307 + "area_id_p" || '-' || 1.308 + "supporter"."member_id" 1.309 + ) AS "seed" 1.310 + FROM "supporter" 1.311 + JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id" 1.312 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.313 + WHERE "supporter"."member_id" != "recipient_id_p" 1.314 + AND "issue"."area_id" = "area_id_p" 1.315 + AND "issue"."state" IN ('admission', 'discussion', 'verification') 1.316 + ) AS "subquery" 1.317 + ORDER BY "seed" 1.318 + LOOP 1.319 + SELECT "initiative"."id" INTO "initiative_id_v" 1.320 + FROM "initiative" 1.321 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.322 + JOIN "area" ON "area"."id" = "issue"."area_id" 1.323 + JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id" 1.324 + LEFT JOIN "supporter" AS "self_support" ON 1.325 + "self_support"."initiative_id" = "initiative"."id" AND 1.326 + "self_support"."member_id" = "recipient_id_p" 1.327 + LEFT JOIN "privilege" ON 1.328 + "privilege"."member_id" = "recipient_id_p" AND 1.329 + "privilege"."unit_id" = "area"."unit_id" AND 1.330 + "privilege"."voting_right" = TRUE 1.331 + LEFT JOIN "subscription" ON 1.332 + "subscription"."member_id" = "recipient_id_p" AND 1.333 + "subscription"."unit_id" = "area"."unit_id" 1.334 + LEFT JOIN "ignored_initiative" ON 1.335 + "ignored_initiative"."member_id" = "recipient_id_p" AND 1.336 + "ignored_initiative"."initiative_id" = "initiative"."id" 1.337 + WHERE "supporter"."member_id" = "member_id_v" 1.338 + AND "issue"."area_id" = "area_id_p" 1.339 + AND "issue"."state" IN ('admission', 'discussion', 'verification') 1.340 + AND "initiative"."revoked" ISNULL 1.341 + AND "self_support"."member_id" ISNULL 1.342 + AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"] 1.343 + AND ( 1.344 + "privilege"."member_id" NOTNULL OR 1.345 + "subscription"."member_id" NOTNULL ) 1.346 + AND "ignored_initiative"."member_id" ISNULL 1.347 + AND NOT EXISTS ( 1.348 + SELECT NULL FROM "draft" 1.349 + JOIN "ignored_member" ON 1.350 + "ignored_member"."member_id" = "recipient_id_p" AND 1.351 + "ignored_member"."other_member_id" = "draft"."author_id" 1.352 + WHERE "draft"."initiative_id" = "initiative"."id" 1.353 + ) 1.354 + ORDER BY md5("seed_v" || '-' || "initiative"."id") 1.355 + LIMIT 1; 1.356 + IF FOUND THEN 1.357 + "match_v" := TRUE; 1.358 + RETURN NEXT "initiative_id_v"; 1.359 + IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN 1.360 + RETURN; 1.361 + END IF; 1.362 + "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v"; 1.363 + END IF; 1.364 + END LOOP; 1.365 + EXIT WHEN NOT "match_v"; 1.366 + END LOOP; 1.367 + RETURN; 1.368 + END; 1.369 + $$; 1.370 + 1.371 +COMMENT ON FUNCTION "featured_initiative" 1.372 + ( "recipient_id_p" "member"."id"%TYPE, 1.373 + "area_id_p" "area"."id"%TYPE ) 1.374 + IS 'Helper function for view "updated_or_featured_initiative"'; 1.375 + 1.376 +CREATE VIEW "updated_or_featured_initiative" AS 1.377 + SELECT 1.378 + "subquery".*, 1.379 + NOT EXISTS ( 1.380 + SELECT NULL FROM "initiative" AS "better_initiative" 1.381 + WHERE "better_initiative"."issue_id" = "initiative"."issue_id" 1.382 + AND 1.383 + ( COALESCE("better_initiative"."supporter_count", -1), 1.384 + -"better_initiative"."id" ) > 1.385 + ( COALESCE("initiative"."supporter_count", -1), 1.386 + -"initiative"."id" ) 1.387 + ) AS "leading" 1.388 + FROM ( 1.389 + SELECT * FROM "updated_initiative" 1.390 + UNION ALL 1.391 + SELECT 1.392 + "member"."id" AS "recipient_id", 1.393 + TRUE AS "featured", 1.394 + "featured_initiative_id" AS "initiative_id" 1.395 + FROM "member" CROSS JOIN "area" 1.396 + CROSS JOIN LATERAL 1.397 + "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id" 1.398 + JOIN "initiative" ON "initiative"."id" = "featured_initiative_id" 1.399 + ) AS "subquery" 1.400 + JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"; 1.401 + 1.402 +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'; 1.403 + 1.404 +COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail'; 1.405 +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")'; 1.406 +COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail'; 1.407 +COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue'; 1.408 + 1.409 +CREATE VIEW "leading_complement_initiative" AS 1.410 + SELECT * FROM ( 1.411 + SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id") 1.412 + "uf_initiative"."recipient_id", 1.413 + FALSE AS "featured", 1.414 + "uf_initiative"."initiative_id", 1.415 + TRUE AS "leading" 1.416 + FROM "updated_or_featured_initiative" AS "uf_initiative" 1.417 + JOIN "initiative" AS "uf_initiative_full" ON 1.418 + "uf_initiative_full"."id" = "uf_initiative"."initiative_id" 1.419 + JOIN "initiative" ON 1.420 + "initiative"."issue_id" = "uf_initiative_full"."issue_id" 1.421 + WHERE "initiative"."revoked" ISNULL 1.422 + ORDER BY 1.423 + "uf_initiative"."recipient_id", 1.424 + "initiative"."issue_id", 1.425 + "initiative"."supporter_count" DESC, 1.426 + "initiative"."id" 1.427 + ) AS "subquery" 1.428 + WHERE NOT EXISTS ( 1.429 + SELECT NULL FROM "updated_or_featured_initiative" AS "other" 1.430 + WHERE "other"."recipient_id" = "subquery"."recipient_id" 1.431 + AND "other"."initiative_id" = "subquery"."initiative_id" 1.432 + ); 1.433 + 1.434 +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'; 1.435 +COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view'; 1.436 +COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail'; 1.437 +COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view'; 1.438 + 1.439 +CREATE VIEW "unfiltered_initiative_for_notification" AS 1.440 + SELECT 1.441 + "subquery".*, 1.442 + "supporter"."member_id" NOTNULL AS "supported", 1.443 + CASE WHEN "supporter"."member_id" NOTNULL THEN 1.444 + EXISTS ( 1.445 + SELECT NULL FROM "draft" 1.446 + WHERE "draft"."initiative_id" = "subquery"."initiative_id" 1.447 + AND "draft"."id" > "supporter"."draft_id" 1.448 + ) 1.449 + ELSE 1.450 + EXISTS ( 1.451 + SELECT NULL FROM "draft" 1.452 + WHERE "draft"."initiative_id" = "subquery"."initiative_id" 1.453 + AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE) 1.454 + ) 1.455 + END AS "new_draft", 1.456 + CASE WHEN "supporter"."member_id" NOTNULL THEN 1.457 + ( SELECT count(1) FROM "suggestion" 1.458 + LEFT JOIN "opinion" ON 1.459 + "opinion"."member_id" = "supporter"."member_id" AND 1.460 + "opinion"."suggestion_id" = "suggestion"."id" 1.461 + WHERE "suggestion"."initiative_id" = "subquery"."initiative_id" 1.462 + AND "opinion"."member_id" ISNULL 1.463 + AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE) 1.464 + ) 1.465 + ELSE 1.466 + ( SELECT count(1) FROM "suggestion" 1.467 + WHERE "suggestion"."initiative_id" = "subquery"."initiative_id" 1.468 + AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE) 1.469 + ) 1.470 + END AS "new_suggestion_count" 1.471 + FROM ( 1.472 + SELECT * FROM "updated_or_featured_initiative" 1.473 + UNION ALL 1.474 + SELECT * FROM "leading_complement_initiative" 1.475 + ) AS "subquery" 1.476 + LEFT JOIN "supporter" ON 1.477 + "supporter"."member_id" = "subquery"."recipient_id" AND 1.478 + "supporter"."initiative_id" = "subquery"."initiative_id" 1.479 + LEFT JOIN "notification_initiative_sent" AS "sent" ON 1.480 + "sent"."member_id" = "subquery"."recipient_id" AND 1.481 + "sent"."initiative_id" = "subquery"."initiative_id"; 1.482 + 1.483 +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'; 1.484 + 1.485 +COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient'; 1.486 +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)'; 1.487 +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")'; 1.488 + 1.489 +CREATE VIEW "initiative_for_notification" AS 1.490 + SELECT "unfiltered1".* 1.491 + FROM "unfiltered_initiative_for_notification" "unfiltered1" 1.492 + JOIN "initiative" AS "initiative1" ON 1.493 + "initiative1"."id" = "unfiltered1"."initiative_id" 1.494 + JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id" 1.495 + WHERE EXISTS ( 1.496 + SELECT NULL 1.497 + FROM "unfiltered_initiative_for_notification" "unfiltered2" 1.498 + JOIN "initiative" AS "initiative2" ON 1.499 + "initiative2"."id" = "unfiltered2"."initiative_id" 1.500 + JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id" 1.501 + WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id" 1.502 + AND "issue1"."area_id" = "issue2"."area_id" 1.503 + AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 ) 1.504 + ); 1.505 + 1.506 +COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail'; 1.507 + 1.508 +COMMENT ON COLUMN "initiative_for_notification"."recipient_id" IS '"id" of the member who receives the notification mail'; 1.509 +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")'; 1.510 +COMMENT ON COLUMN "initiative_for_notification"."initiative_id" IS '"id" of the initiative to be included in the notification mail'; 1.511 +COMMENT ON COLUMN "initiative_for_notification"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue'; 1.512 +COMMENT ON COLUMN "initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient'; 1.513 +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)'; 1.514 +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")'; 1.515 + 1.516 +CREATE VIEW "scheduled_notification_to_send" AS 1.517 + SELECT * FROM ( 1.518 + SELECT 1.519 + "id" AS "recipient_id", 1.520 + now() - CASE WHEN "notification_dow" ISNULL THEN 1.521 + ( "notification_sent"::DATE + CASE 1.522 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.523 + THEN 0 ELSE 1 END 1.524 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.525 + ELSE 1.526 + ( "notification_sent"::DATE + 1.527 + ( 7 + "notification_dow" - 1.528 + EXTRACT(DOW FROM 1.529 + ( "notification_sent"::DATE + CASE 1.530 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.531 + THEN 0 ELSE 1 END 1.532 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.533 + )::INTEGER 1.534 + ) % 7 + 1.535 + CASE 1.536 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.537 + THEN 0 ELSE 1 1.538 + END 1.539 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.540 + END AS "pending" 1.541 + FROM ( 1.542 + SELECT 1.543 + "id", 1.544 + COALESCE("notification_sent", "activated") AS "notification_sent", 1.545 + "notification_dow", 1.546 + "notification_hour" 1.547 + FROM "member" 1.548 + WHERE "disable_notifications" = FALSE 1.549 + AND "notification_hour" NOTNULL 1.550 + ) AS "subquery1" 1.551 + ) AS "subquery2" 1.552 + WHERE "pending" > '0'::INTERVAL; 1.553 + 1.554 +COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending'; 1.555 + 1.556 +COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail'; 1.557 +COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending'; 1.558 + 1.559 +CREATE VIEW "newsletter_to_send" AS 1.560 + SELECT 1.561 + "member"."id" AS "recipient_id", 1.562 + "newsletter"."id" AS "newsletter_id", 1.563 + "newsletter"."published" 1.564 + FROM "newsletter" CROSS JOIN "member" 1.565 + LEFT JOIN "privilege" ON 1.566 + "privilege"."member_id" = "member"."id" AND 1.567 + "privilege"."unit_id" = "newsletter"."unit_id" AND 1.568 + "privilege"."voting_right" = TRUE 1.569 + LEFT JOIN "subscription" ON 1.570 + "subscription"."member_id" = "member"."id" AND 1.571 + "subscription"."unit_id" = "newsletter"."unit_id" 1.572 + WHERE "newsletter"."published" <= now() 1.573 + AND "newsletter"."sent" ISNULL 1.574 + AND "member"."locked" = FALSE 1.575 + AND ( 1.576 + "member"."disable_notifications" = FALSE OR 1.577 + "newsletter"."include_all_members" = TRUE ) 1.578 + AND ( 1.579 + "newsletter"."unit_id" ISNULL OR 1.580 + "privilege"."member_id" NOTNULL OR 1.581 + "subscription"."member_id" NOTNULL ); 1.582 + 1.583 +COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out'; 1.584 +COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)'; 1.585 + 1.586 +CREATE OR REPLACE FUNCTION "require_transaction_isolation"() 1.587 + RETURNS VOID 1.588 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.589 + BEGIN 1.590 + IF 1.591 + current_setting('transaction_isolation') NOT IN 1.592 + ('repeatable read', 'serializable') 1.593 + THEN 1.594 + RAISE EXCEPTION 'Insufficient transaction isolation level' USING 1.595 + HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.'; 1.596 + END IF; 1.597 + RETURN; 1.598 + END; 1.599 + $$; 1.600 + 1.601 +CREATE FUNCTION "get_initiatives_for_notification" 1.602 + ( "recipient_id_p" "member"."id"%TYPE ) 1.603 + RETURNS SETOF "initiative_for_notification" 1.604 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.605 + DECLARE 1.606 + "result_row" "initiative_for_notification"%ROWTYPE; 1.607 + "last_draft_id_v" "draft"."id"%TYPE; 1.608 + "last_suggestion_id_v" "suggestion"."id"%TYPE; 1.609 + BEGIN 1.610 + PERFORM "require_transaction_isolation"(); 1.611 + PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE; 1.612 + FOR "result_row" IN 1.613 + SELECT * FROM "initiative_for_notification" 1.614 + WHERE "recipient_id" = "recipient_id_p" 1.615 + LOOP 1.616 + SELECT "id" INTO "last_draft_id_v" FROM "draft" 1.617 + WHERE "draft"."initiative_id" = "result_row"."initiative_id" 1.618 + ORDER BY "id" DESC LIMIT 1; 1.619 + SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion" 1.620 + WHERE "suggestion"."initiative_id" = "result_row"."initiative_id" 1.621 + ORDER BY "id" DESC LIMIT 1; 1.622 + /* compatibility with PostgreSQL 9.1 */ 1.623 + DELETE FROM "notification_initiative_sent" 1.624 + WHERE "member_id" = "recipient_id_p" 1.625 + AND "initiative_id" = "result_row"."initiative_id"; 1.626 + INSERT INTO "notification_initiative_sent" 1.627 + ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 1.628 + VALUES ( 1.629 + "recipient_id_p", 1.630 + "result_row"."initiative_id", 1.631 + "last_draft_id_v", 1.632 + "last_suggestion_id_v" ); 1.633 + /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher 1.634 + INSERT INTO "notification_initiative_sent" 1.635 + ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 1.636 + VALUES ( 1.637 + "recipient_id_p", 1.638 + "result_row"."initiative_id", 1.639 + "last_draft_id_v", 1.640 + "last_suggestion_id_v" ) 1.641 + ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET 1.642 + "last_draft_id" = "last_draft_id_v", 1.643 + "last_suggestion_id" = "last_suggestion_id_v"; 1.644 + */ 1.645 + RETURN NEXT "result_row"; 1.646 + END LOOP; 1.647 + DELETE FROM "notification_initiative_sent" 1.648 + USING "initiative", "issue" 1.649 + WHERE "notification_initiative_sent"."member_id" = "recipient_id_p" 1.650 + AND "initiative"."id" = "notification_initiative_sent"."initiative_id" 1.651 + AND "issue"."id" = "initiative"."issue_id" 1.652 + AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL ); 1.653 + UPDATE "member" SET 1.654 + "notification_counter" = "notification_counter" + 1, 1.655 + "notification_sent" = now() 1.656 + WHERE "id" = "recipient_id_p"; 1.657 + RETURN; 1.658 + END; 1.659 + $$; 1.660 + 1.661 +COMMENT ON FUNCTION "get_initiatives_for_notification" 1.662 + ( "member"."id"%TYPE ) 1.663 + 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'; 1.664 + 1.665 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 1.666 + RETURNS VOID 1.667 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.668 + BEGIN 1.669 + UPDATE "member" SET 1.670 + "last_login" = NULL, 1.671 + "last_delegation_check" = NULL, 1.672 + "login" = NULL, 1.673 + "password" = NULL, 1.674 + "authority" = NULL, 1.675 + "authority_uid" = NULL, 1.676 + "authority_login" = NULL, 1.677 + "locked" = TRUE, 1.678 + "active" = FALSE, 1.679 + "notify_email" = NULL, 1.680 + "notify_email_unconfirmed" = NULL, 1.681 + "notify_email_secret" = NULL, 1.682 + "notify_email_secret_expiry" = NULL, 1.683 + "notify_email_lock_expiry" = NULL, 1.684 + "disable_notifications" = NULL, 1.685 + "notification_counter" = NULL, 1.686 + "notification_sample_size" = NULL, 1.687 + "notification_dow" = NULL, 1.688 + "notification_hour" = NULL, 1.689 + "login_recovery_expiry" = NULL, 1.690 + "password_reset_secret" = NULL, 1.691 + "password_reset_secret_expiry" = NULL, 1.692 + "organizational_unit" = NULL, 1.693 + "internal_posts" = NULL, 1.694 + "realname" = NULL, 1.695 + "birthday" = NULL, 1.696 + "address" = NULL, 1.697 + "email" = NULL, 1.698 + "xmpp_address" = NULL, 1.699 + "website" = NULL, 1.700 + "phone" = NULL, 1.701 + "mobile_phone" = NULL, 1.702 + "profession" = NULL, 1.703 + "external_memberships" = NULL, 1.704 + "external_posts" = NULL, 1.705 + "statement" = NULL 1.706 + WHERE "id" = "member_id_p"; 1.707 + -- "text_search_data" is updated by triggers 1.708 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 1.709 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 1.710 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 1.711 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 1.712 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 1.713 + DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 1.714 + DELETE FROM "session" WHERE "member_id" = "member_id_p"; 1.715 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 1.716 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 1.717 + DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 1.718 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 1.719 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 1.720 + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 1.721 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 1.722 + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 1.723 + DELETE FROM "direct_voter" USING "issue" 1.724 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.725 + AND "issue"."closed" ISNULL 1.726 + AND "member_id" = "member_id_p"; 1.727 + RETURN; 1.728 + END; 1.729 + $$; 1.730 + 1.731 +CREATE OR REPLACE FUNCTION "delete_private_data"() 1.732 + RETURNS VOID 1.733 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.734 + BEGIN 1.735 + DELETE FROM "temporary_transaction_data"; 1.736 + DELETE FROM "member" WHERE "activated" ISNULL; 1.737 + UPDATE "member" SET 1.738 + "invite_code" = NULL, 1.739 + "invite_code_expiry" = NULL, 1.740 + "admin_comment" = NULL, 1.741 + "last_login" = NULL, 1.742 + "last_delegation_check" = NULL, 1.743 + "login" = NULL, 1.744 + "password" = NULL, 1.745 + "authority" = NULL, 1.746 + "authority_uid" = NULL, 1.747 + "authority_login" = NULL, 1.748 + "lang" = NULL, 1.749 + "notify_email" = NULL, 1.750 + "notify_email_unconfirmed" = NULL, 1.751 + "notify_email_secret" = NULL, 1.752 + "notify_email_secret_expiry" = NULL, 1.753 + "notify_email_lock_expiry" = NULL, 1.754 + "disable_notifications" = NULL, 1.755 + "notification_counter" = NULL, 1.756 + "notification_sample_size" = NULL, 1.757 + "notification_dow" = NULL, 1.758 + "notification_hour" = NULL, 1.759 + "login_recovery_expiry" = NULL, 1.760 + "password_reset_secret" = NULL, 1.761 + "password_reset_secret_expiry" = NULL, 1.762 + "organizational_unit" = NULL, 1.763 + "internal_posts" = NULL, 1.764 + "realname" = NULL, 1.765 + "birthday" = NULL, 1.766 + "address" = NULL, 1.767 + "email" = NULL, 1.768 + "xmpp_address" = NULL, 1.769 + "website" = NULL, 1.770 + "phone" = NULL, 1.771 + "mobile_phone" = NULL, 1.772 + "profession" = NULL, 1.773 + "external_memberships" = NULL, 1.774 + "external_posts" = NULL, 1.775 + "formatting_engine" = NULL, 1.776 + "statement" = NULL; 1.777 + -- "text_search_data" is updated by triggers 1.778 + DELETE FROM "setting"; 1.779 + DELETE FROM "setting_map"; 1.780 + DELETE FROM "member_relation_setting"; 1.781 + DELETE FROM "member_image"; 1.782 + DELETE FROM "contact"; 1.783 + DELETE FROM "ignored_member"; 1.784 + DELETE FROM "session"; 1.785 + DELETE FROM "area_setting"; 1.786 + DELETE FROM "issue_setting"; 1.787 + DELETE FROM "ignored_initiative"; 1.788 + DELETE FROM "initiative_setting"; 1.789 + DELETE FROM "suggestion_setting"; 1.790 + DELETE FROM "non_voter"; 1.791 + DELETE FROM "direct_voter" USING "issue" 1.792 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.793 + AND "issue"."closed" ISNULL; 1.794 + RETURN; 1.795 + END; 1.796 + $$; 1.797 + 1.798 +COMMIT;