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",

Impressum / About Us