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