liquid_feedback_core
changeset 504:d07e6a046d41
New column "notification_sent" in "member" table; New view "scheduled_notification_to_send"
author | jbe |
---|---|
date | Tue Apr 05 00:55:31 2016 +0200 (2016-04-05) |
parents | 986d8f3a6895 |
children | be7942edfd1e |
files | core.sql update/core-update.v3.1.0-v3.2.0.sql |
line diff
1.1 --- a/core.sql Tue Apr 05 00:19:33 2016 +0200 1.2 +++ b/core.sql Tue Apr 05 00:55:31 2016 +0200 1.3 @@ -118,6 +118,7 @@ 1.4 "notification_sample_size" INT4 NOT NULL DEFAULT 3, 1.5 "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6), 1.6 "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23), 1.7 + "notification_sent" TIMESTAMP, 1.8 "login_recovery_expiry" TIMESTAMPTZ, 1.9 "password_reset_secret" TEXT UNIQUE, 1.10 "password_reset_secret_expiry" TIMESTAMPTZ, 1.11 @@ -2610,6 +2611,17 @@ 1.12 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 ) 1.13 ); 1.14 1.15 +CREATE VIEW "scheduled_notification_to_send" AS 1.16 + SELECT "id" AS "recipient_id" 1.17 + FROM "member" 1.18 + WHERE "member"."disable_notifications" = FALSE 1.19 + AND COALESCE("notification_dow" = EXTRACT(DOW FROM now()), TRUE) 1.20 + AND "notification_hour" = EXTRACT(HOUR FROM now()) 1.21 + AND NOT ( 1.22 + "notification_sent" NOTNULL AND 1.23 + "notification_sent"::DATE = now()::DATE AND 1.24 + EXTRACT(HOUR FROM "notification_sent") = EXTRACT(HOUR FROM now()) ); 1.25 + 1.26 CREATE VIEW "newsletter_to_send" AS 1.27 SELECT 1.28 "member"."id" AS "recipient_id",
2.1 --- a/update/core-update.v3.1.0-v3.2.0.sql Tue Apr 05 00:19:33 2016 +0200 2.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql Tue Apr 05 00:55:31 2016 +0200 2.3 @@ -11,6 +11,7 @@ 2.4 ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3; 2.5 ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6); 2.6 ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23); 2.7 +ALTER TABLE "member" ADD COLUMN "notification_sent" TIMESTAMP; 2.8 2.9 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none'; 2.10 2.11 @@ -329,6 +330,17 @@ 2.12 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 ) 2.13 ); 2.14 2.15 +CREATE VIEW "scheduled_notification_to_send" AS 2.16 + SELECT "id" AS "recipient_id" 2.17 + FROM "member" 2.18 + WHERE "member"."disable_notifications" = FALSE 2.19 + AND COALESCE("notification_dow" = EXTRACT(DOW FROM now()), TRUE) 2.20 + AND "notification_hour" = EXTRACT(HOUR FROM now()) 2.21 + AND NOT ( 2.22 + "notification_sent" NOTNULL AND 2.23 + "notification_sent"::DATE = now()::DATE AND 2.24 + EXTRACT(HOUR FROM "notification_sent") = EXTRACT(HOUR FROM now()) ); 2.25 + 2.26 CREATE VIEW "newsletter_to_send" AS 2.27 SELECT 2.28 "member"."id" AS "recipient_id",