liquid_feedback_core

changeset 507:7bc72214ecc9

Renamed tables "notification_sent" to "notification_event_sent" and "initiative_notification_sent" to "notification_initiative_sent"
author jbe
date Sat Apr 16 14:38:44 2016 +0200 (2016-04-16)
parents f5aba1fbb845
children e9a6f63612da
files core.sql
line diff
     1.1 --- a/core.sql	Tue Apr 05 20:33:39 2016 +0200
     1.2 +++ b/core.sql	Sat Apr 16 14:38:44 2016 +0200
     1.3 @@ -1259,21 +1259,21 @@
     1.4  COMMENT ON COLUMN "event"."state"      IS 'If issue_id is set: state of affected issue; If state changed: new state';
     1.5  
     1.6  
     1.7 -CREATE TABLE "notification_sent" (
     1.8 +CREATE TABLE "notification_event_sent" (
     1.9          "event_id"              INT8            NOT NULL );
    1.10 -CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
    1.11 -
    1.12 -COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
    1.13 -COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
    1.14 -
    1.15 -
    1.16 -CREATE TABLE "initiative_notification_sent" (
    1.17 +CREATE UNIQUE INDEX "notification_event_sent_singleton_idx" ON "notification_event_sent" ((1));
    1.18 +
    1.19 +COMMENT ON TABLE "notification_event_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
    1.20 +COMMENT ON INDEX "notification_event_sent_singleton_idx" IS 'This index ensures that "notification_event_sent" only contains one row maximum.';
    1.21 +
    1.22 +
    1.23 +CREATE TABLE "notification_initiative_sent" (
    1.24          PRIMARY KEY ("member_id", "initiative_id"),
    1.25          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.26          "initiative_id"         INT4            REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.27          "last_draft_id"         INT8            NOT NULL,
    1.28          "last_suggestion_id"    INT8 );
    1.29 -CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id");
    1.30 +CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id");
    1.31  
    1.32  
    1.33  CREATE TABLE "newsletter" (
    1.34 @@ -2377,7 +2377,7 @@
    1.35    FROM "supporter"
    1.36    JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
    1.37    JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
    1.38 -  LEFT JOIN "initiative_notification_sent" AS "sent" ON
    1.39 +  LEFT JOIN "notification_initiative_sent" AS "sent" ON
    1.40      "sent"."member_id" = "supporter"."member_id" AND
    1.41      "sent"."initiative_id" = "supporter"."initiative_id"
    1.42    LEFT JOIN "ignored_initiative" ON
    1.43 @@ -2592,7 +2592,7 @@
    1.44    LEFT JOIN "supporter" ON
    1.45      "supporter"."member_id" = "subquery"."recipient_id" AND
    1.46      "supporter"."initiative_id" = "subquery"."initiative_id"
    1.47 -  LEFT JOIN "initiative_notification_sent" AS "sent" ON
    1.48 +  LEFT JOIN "notification_initiative_sent" AS "sent" ON
    1.49      "sent"."member_id" = "subquery"."recipient_id" AND
    1.50      "sent"."initiative_id" = "subquery"."initiative_id";
    1.51  
    1.52 @@ -3184,7 +3184,7 @@
    1.53          SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
    1.54            WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
    1.55            ORDER BY "id" DESC LIMIT 1;
    1.56 -        INSERT INTO "initiative_notification_sent"
    1.57 +        INSERT INTO "notification_initiative_sent"
    1.58            ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
    1.59            VALUES (
    1.60              "recipient_id_p",
    1.61 @@ -3193,21 +3193,21 @@
    1.62              "last_suggestion_id_v" )
    1.63            ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
    1.64              "last_draft_id" = CASE
    1.65 -              WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v"
    1.66 -              THEN "initiative_notification_sent"."last_draft_id"
    1.67 +              WHEN "notification_initiative_sent"."last_draft_id" > "last_draft_id_v"
    1.68 +              THEN "notification_initiative_sent"."last_draft_id"
    1.69                ELSE "last_draft_id_v"
    1.70              END,
    1.71              "last_suggestion_id" = CASE
    1.72 -              WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v"
    1.73 -              THEN "initiative_notification_sent"."last_suggestion_id"
    1.74 +              WHEN "notification_initiative_sent"."last_suggestion_id" > "last_suggestion_id_v"
    1.75 +              THEN "notification_initiative_sent"."last_suggestion_id"
    1.76                ELSE "last_suggestion_id_v"
    1.77              END;
    1.78          RETURN NEXT "result_row";
    1.79        END LOOP;
    1.80 -      DELETE FROM "initiative_notification_sent"
    1.81 +      DELETE FROM "notification_initiative_sent"
    1.82          USING "initiative", "issue"
    1.83 -        WHERE "initiative_notification_sent"."member_id" = "recipient_id_p"
    1.84 -        AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
    1.85 +        WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
    1.86 +        AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
    1.87          AND "issue"."id" = "initiative"."issue_id"
    1.88          AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
    1.89        UPDATE "member" SET

Impressum / About Us