liquid_feedback_core
changeset 505:be7942edfd1e
New algorithm for view "scheduled_notification_to_send"; Update "notification_sent" column in "get_initiatives_for_notification" function
author | jbe |
---|---|
date | Tue Apr 05 20:12:24 2016 +0200 (2016-04-05) |
parents | d07e6a046d41 |
children | f5aba1fbb845 |
files | core.sql update/core-update.v3.1.0-v3.2.0.sql |
line diff
1.1 --- a/core.sql Tue Apr 05 00:55:31 2016 +0200 1.2 +++ b/core.sql Tue Apr 05 20:12:24 2016 +0200 1.3 @@ -149,6 +149,8 @@ 1.4 UNIQUE ("authority", "authority_uid"), 1.5 CONSTRAINT "authority_login_requires_authority" 1.6 CHECK ("authority" NOTNULL OR "authority_login" ISNULL), 1.7 + CONSTRAINT "notification_dow_requires_notification_hour" 1.8 + CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL), 1.9 CONSTRAINT "name_not_null_if_activated" 1.10 CHECK ("activated" ISNULL OR "name" NOTNULL) ); 1.11 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login"); 1.12 @@ -2612,15 +2614,42 @@ 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 + SELECT * FROM ( 1.26 + SELECT 1.27 + "id" AS "recipient_id", 1.28 + now() - CASE WHEN "notification_dow" ISNULL THEN 1.29 + ( "notification_sent"::DATE + CASE 1.30 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.31 + THEN 0 ELSE 1 END 1.32 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.33 + ELSE 1.34 + ( "notification_sent"::DATE + 1.35 + ( 7 + "notification_dow" - 1.36 + EXTRACT(DOW FROM 1.37 + ( "notification_sent"::DATE + CASE 1.38 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.39 + THEN 0 ELSE 1 END 1.40 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.41 + )::INTEGER 1.42 + ) % 7 + 1.43 + CASE 1.44 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.45 + THEN 0 ELSE 1 1.46 + END 1.47 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.48 + END AS "pending" 1.49 + FROM ( 1.50 + SELECT 1.51 + "id", 1.52 + COALESCE("notification_sent", "activated") AS "notification_sent", 1.53 + "notification_dow", 1.54 + "notification_hour" 1.55 + FROM "member" 1.56 + WHERE "disable_notifications" = FALSE 1.57 + AND "notification_hour" NOTNULL 1.58 + ) AS "subquery1" 1.59 + ) AS "subquery2" 1.60 + WHERE "pending" > '0'::INTERVAL; 1.61 1.62 CREATE VIEW "newsletter_to_send" AS 1.63 SELECT 1.64 @@ -3181,7 +3210,9 @@ 1.65 AND "initiative"."id" = "initiative_notification_sent"."initiative_id" 1.66 AND "issue"."id" = "initiative"."issue_id" 1.67 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL ); 1.68 - UPDATE "member" SET "notification_counter" = "notification_counter" + 1 1.69 + UPDATE "member" SET 1.70 + "notification_counter" = "notification_counter" + 1 AND 1.71 + "notification_sent" = now() 1.72 WHERE "id" = "recipient_id_p"; 1.73 RETURN; 1.74 END;
2.1 --- a/update/core-update.v3.1.0-v3.2.0.sql Tue Apr 05 00:55:31 2016 +0200 2.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql Tue Apr 05 20:12:24 2016 +0200 2.3 @@ -331,15 +331,42 @@ 2.4 ); 2.5 2.6 CREATE VIEW "scheduled_notification_to_send" AS 2.7 - SELECT "id" AS "recipient_id" 2.8 - FROM "member" 2.9 - WHERE "member"."disable_notifications" = FALSE 2.10 - AND COALESCE("notification_dow" = EXTRACT(DOW FROM now()), TRUE) 2.11 - AND "notification_hour" = EXTRACT(HOUR FROM now()) 2.12 - AND NOT ( 2.13 - "notification_sent" NOTNULL AND 2.14 - "notification_sent"::DATE = now()::DATE AND 2.15 - EXTRACT(HOUR FROM "notification_sent") = EXTRACT(HOUR FROM now()) ); 2.16 + SELECT * FROM ( 2.17 + SELECT 2.18 + "id" AS "recipient_id", 2.19 + now() - CASE WHEN "notification_dow" ISNULL THEN 2.20 + ( "notification_sent"::DATE + CASE 2.21 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 2.22 + THEN 0 ELSE 1 END 2.23 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 2.24 + ELSE 2.25 + ( "notification_sent"::DATE + 2.26 + ( 7 + "notification_dow" - 2.27 + EXTRACT(DOW FROM 2.28 + ( "notification_sent"::DATE + CASE 2.29 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 2.30 + THEN 0 ELSE 1 END 2.31 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 2.32 + )::INTEGER 2.33 + ) % 7 + 2.34 + CASE 2.35 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 2.36 + THEN 0 ELSE 1 2.37 + END 2.38 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 2.39 + END AS "pending" 2.40 + FROM ( 2.41 + SELECT 2.42 + "id", 2.43 + COALESCE("notification_sent", "activated") AS "notification_sent", 2.44 + "notification_dow", 2.45 + "notification_hour" 2.46 + FROM "member" 2.47 + WHERE "disable_notifications" = FALSE 2.48 + AND "notification_hour" NOTNULL 2.49 + ) AS "subquery1" 2.50 + ) AS "subquery2" 2.51 + WHERE "pending" > '0'::INTERVAL; 2.52 2.53 CREATE VIEW "newsletter_to_send" AS 2.54 SELECT 2.55 @@ -411,7 +438,9 @@ 2.56 AND "initiative"."id" = "initiative_notification_sent"."initiative_id" 2.57 AND "issue"."id" = "initiative"."issue_id" 2.58 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL ); 2.59 - UPDATE "member" SET "notification_counter" = "notification_counter" + 1 2.60 + UPDATE "member" SET 2.61 + "notification_counter" = "notification_counter" + 1 AND 2.62 + "notification_sent" = now() 2.63 WHERE "id" = "recipient_id_p"; 2.64 RETURN; 2.65 END;