1.1 --- a/update/core-update.v3.1.0-v3.2.0.sql	Tue Apr 05 00:19:33 2016 +0200
     1.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql	Tue Apr 05 00:55:31 2016 +0200
     1.3 @@ -11,6 +11,7 @@
     1.4  ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;
     1.5  ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6);
     1.6  ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23);
     1.7 +ALTER TABLE "member" ADD COLUMN "notification_sent" TIMESTAMP;
     1.8  
     1.9  UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
    1.10  
    1.11 @@ -329,6 +330,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",