liquid_feedback_core

changeset 462:e9525f069607

Added tables "subscription_time" and "advertisement" (work on notification system)
author jbe
date Thu Mar 17 03:15:39 2016 +0100 (2016-03-17)
parents 4c3522ba2552
children 88b47f0dacde
files core.sql
line diff
     1.1 --- a/core.sql	Thu Mar 17 03:12:19 2016 +0100
     1.2 +++ b/core.sql	Thu Mar 17 03:15:39 2016 +0100
     1.3 @@ -226,6 +226,16 @@
     1.4  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.5  
     1.6  
     1.7 +CREATE TABLE "subscription_time" (
     1.8 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
     1.9 +        "day_of_week"           INT2            CONSTRAINT "day_of_week_range" CHECK ("day_of_week" BETWEEN 0 AND 6),
    1.10 +        "time_of_day"           TIME            NOT NULL );
    1.11 +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.12 +CREATE UNIQUE INDEX "subscription_time_day_of_week_time_of_day_idx" ON "subscription_time" ("day_of_week", "time_of_day", "member_id");
    1.13 +
    1.14 +COMMENT ON TABLE "subscription_time" IS 'Selects when a member receives digests on new user content in the system';
    1.15 +
    1.16 +
    1.17  CREATE TABLE "setting" (
    1.18          PRIMARY KEY ("member_id", "key"),
    1.19          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.20 @@ -1267,6 +1277,19 @@
    1.21  COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
    1.22  
    1.23  
    1.24 +CREATE TABLE "advertisement" (
    1.25 +        PRIMARY KEY ("time_serial", "initiative_id", "member_id"),
    1.26 +        "time_serial"           SERIAL8,
    1.27 +        "initiative_id"         INT4            NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.28 +        "member_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.29 +CREATE INDEX "advertisement_initiative_id_idx" ON "advertisement" ("initiative_id");
    1.30 +CREATE INDEX "advertisement_member_id_idx" ON "advertisement" ("member_id");
    1.31 +
    1.32 +COMMENT ON TABLE "advertisement" IS 'Stores which initiatives have been advertised to a member in a mail digest';
    1.33 +
    1.34 +COMMENT ON COLUMN "advertisement"."time_serial" IS 'An increasing integer that may be used to determine which issue or initiative was advertised the longest ago (may be equal or consecutive for those initiatives that are sent in the same notification)';
    1.35 +
    1.36 +
    1.37  
    1.38  ----------------------------------------------
    1.39  -- Writing of history entries and event log --

Impressum / About Us