liquid_feedback_core
changeset 486:9aa403a05261
New table "initiative_notification_sent"
author | jbe |
---|---|
date | Sat Apr 02 22:08:39 2016 +0200 (2016-04-02) |
parents | e431f1b73c35 |
children | 14bca0b56925 |
files | core.sql |
line diff
1.1 --- a/core.sql Fri Apr 01 17:30:08 2016 +0200 1.2 +++ b/core.sql Sat Apr 02 22:08:39 2016 +0200 1.3 @@ -119,10 +119,11 @@ 1.4 "notify_email_secret" TEXT UNIQUE, 1.5 "notify_email_secret_expiry" TIMESTAMPTZ, 1.6 "notify_email_lock_expiry" TIMESTAMPTZ, 1.7 - "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE, 1.8 - "notification_counter" INT4 NOT NULL DEFAULT 0, 1.9 - "sample_size" INT4 NOT NULL DEFAULT 3, 1.10 - "last_notified_suggestion_id" INT8, 1.11 + "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE, 1.12 + "notification_counter" INT4 NOT NULL DEFAULT 1, 1.13 + "notification_sample_size" INT4 NOT NULL DEFAULT 3, 1.14 + "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6), 1.15 + "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23), 1.16 "login_recovery_expiry" TIMESTAMPTZ, 1.17 "password_reset_secret" TEXT UNIQUE, 1.18 "password_reset_secret_expiry" TIMESTAMPTZ, 1.19 @@ -229,16 +230,6 @@ 1.20 COMMENT ON TABLE "rendered_member_statement" IS 'This table may be used by frontends to cache "rendered" member statements (e.g. HTML output generated from wiki text)'; 1.21 1.22 1.23 -CREATE TABLE "subscription_time" ( 1.24 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.25 - "day_of_week" INT2 CONSTRAINT "day_of_week_range" CHECK ("day_of_week" BETWEEN 0 AND 6), 1.26 - "time_of_day" TIME NOT NULL ); 1.27 -CREATE UNIQUE INDEX "subscription_time_all_days_of_week_time_of_day_idx" ON "subscription_time" ("time_of_day", "member_id") WHERE ("day_of_week" ISNULL); 1.28 -CREATE UNIQUE INDEX "subscription_time_day_of_week_time_of_day_idx" ON "subscription_time" ("day_of_week", "time_of_day", "member_id"); 1.29 - 1.30 -COMMENT ON TABLE "subscription_time" IS 'Selects when a member receives digests on new user content in the system'; 1.31 - 1.32 - 1.33 CREATE TABLE "setting" ( 1.34 PRIMARY KEY ("member_id", "key"), 1.35 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.36 @@ -1279,6 +1270,15 @@ 1.37 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.'; 1.38 1.39 1.40 +CREATE TABLE "initiative_notification_sent" ( 1.41 + PRIMARY KEY ("member_id", "initiative_id"), 1.42 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.43 + "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.44 + "last_draft_id" INT8 NOT NULL, 1.45 + "last_suggestion_id" INT8 NOT NULL ); 1.46 +CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id"); 1.47 + 1.48 + 1.49 1.50 ---------------------------------------------- 1.51 -- Writing of history entries and event log -- 1.52 @@ -2385,7 +2385,7 @@ 1.53 1.54 CREATE VIEW "updated_initiative" AS 1.55 SELECT 1.56 - "member"."id" AS "seen_by_member_id", 1.57 + "supporter"."member_id" AS "seen_by_member_id", 1.58 TRUE AS "supported", 1.59 EXISTS ( 1.60 SELECT NULL FROM "draft" 1.61 @@ -2395,7 +2395,7 @@ 1.62 ( SELECT count(1) FROM "suggestion" 1.63 WHERE "suggestion"."initiative_id" = "initiative"."id" 1.64 AND COALESCE( 1.65 - "suggestion"."id" > "member"."last_notified_suggestion_id", 1.66 + "suggestion"."id" > "sent"."last_suggestion_id", 1.67 TRUE 1.68 ) 1.69 ) AS "new_suggestion_count", 1.70 @@ -2411,11 +2411,12 @@ 1.71 -"initiative"."id" ) 1.72 ) AS "leading", 1.73 "initiative".* 1.74 - FROM "member" CROSS JOIN "initiative" 1.75 + FROM "supporter" JOIN "initiative" 1.76 + ON "supporter"."initiative_id" = "initiative"."id" 1.77 + LEFT JOIN "initiative_notification_sent" AS "sent" 1.78 + ON "sent"."member_id" = "supporter"."member_id" 1.79 + AND "sent"."initiative_id" = "initiative"."id" 1.80 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.81 - JOIN "supporter" ON 1.82 - "supporter"."member_id" = "member"."id" AND 1.83 - "supporter"."initiative_id" = "initiative"."id" 1.84 WHERE "issue"."state" IN ('admission', 'discussion') 1.85 AND ( 1.86 EXISTS ( 1.87 @@ -2426,7 +2427,7 @@ 1.88 SELECT NULL FROM "suggestion" 1.89 WHERE "suggestion"."initiative_id" = "initiative"."id" 1.90 AND COALESCE( 1.91 - "suggestion"."id" > "member"."last_notified_suggestion_id", 1.92 + "suggestion"."id" > "sent"."last_suggestion_id", 1.93 TRUE 1.94 ) 1.95 ) 1.96 @@ -2482,7 +2483,7 @@ 1.97 "match_v" := TRUE; 1.98 "initiative_id_ary" := "initiative_id_ary" || "result_row"."id"; 1.99 RETURN NEXT "result_row"; 1.100 - IF array_length("initiative_id_ary", 1) >= "member_row"."sample_size" THEN 1.101 + IF array_length("initiative_id_ary", 1) >= "member_row"."notification_sample_size" THEN 1.102 RETURN; 1.103 END IF; 1.104 END IF;