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;

Impressum / About Us