liquid_feedback_core
changeset 222:cd5fa6c46e13
Changes in event system:
- replaced column "notify_event_id" of "member" table by singleton table "notification_sent"
- removed view "pending_notification"
- added view "selected_event_seen_by_member"
- replaced column "notify_event_id" of "member" table by singleton table "notification_sent"
- removed view "pending_notification"
- added view "selected_event_seen_by_member"
author | jbe |
---|---|
date | Sat Feb 25 15:13:01 2012 +0100 (2012-02-25) |
parents | 4a7ae2507515 |
children | ae08a20808d4 |
files | core.sql |
line diff
1.1 --- a/core.sql Sat Feb 25 15:09:59 2012 +0100 1.2 +++ b/core.sql Sat Feb 25 15:13:01 2012 +0100 1.3 @@ -101,7 +101,6 @@ 1.4 "notify_email_secret_expiry" TIMESTAMPTZ, 1.5 "notify_email_lock_expiry" TIMESTAMPTZ, 1.6 "notify_level" "notify_level" NOT NULL DEFAULT 'none', 1.7 - "notify_event_id" INT8, 1.8 "password_reset_secret" TEXT UNIQUE, 1.9 "password_reset_secret_expiry" TIMESTAMPTZ, 1.10 "name" TEXT NOT NULL UNIQUE, 1.11 @@ -154,7 +153,6 @@ 1.12 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"'; 1.13 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)'; 1.14 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address'; 1.15 -COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about'; 1.16 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member'; 1.17 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member'; 1.18 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated'; 1.19 @@ -1171,6 +1169,14 @@ 1.20 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state'; 1.21 1.22 1.23 +CREATE TABLE "notification_sent" ( 1.24 + "event_id" INT8 NOT NULL ); 1.25 +CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1)); 1.26 + 1.27 +COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out'; 1.28 +COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.'; 1.29 + 1.30 + 1.31 1.32 ---------------------------------------------- 1.33 -- Writing of history entries and event log -- 1.34 @@ -2111,12 +2117,36 @@ 1.35 AND "ignored_member"."member_id" ISNULL 1.36 AND "ignored_initiative"."member_id" ISNULL; 1.37 1.38 -COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support'; 1.39 - 1.40 - 1.41 -CREATE VIEW "pending_notification" AS 1.42 +COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support, but ignoring members "notify_level"'; 1.43 + 1.44 + 1.45 +CREATE VIEW "selected_event_seen_by_member" AS 1.46 SELECT 1.47 "member"."id" AS "seen_by_member_id", 1.48 + CASE WHEN "event"."state" IN ( 1.49 + 'voting', 1.50 + 'finished_without_winner', 1.51 + 'finished_with_winner' 1.52 + ) THEN 1.53 + 'voting'::"notify_level" 1.54 + ELSE 1.55 + CASE WHEN "event"."state" IN ( 1.56 + 'verification', 1.57 + 'canceled_after_revocation_during_verification', 1.58 + 'canceled_no_initiative_admitted' 1.59 + ) THEN 1.60 + 'verification'::"notify_level" 1.61 + ELSE 1.62 + CASE WHEN "event"."state" IN ( 1.63 + 'discussion', 1.64 + 'canceled_after_revocation_during_discussion' 1.65 + ) THEN 1.66 + 'discussion'::"notify_level" 1.67 + ELSE 1.68 + 'all'::"notify_level" 1.69 + END 1.70 + END 1.71 + END AS "notify_level", 1.72 "event".* 1.73 FROM "member" CROSS JOIN "event" 1.74 LEFT JOIN "issue" 1.75 @@ -2137,10 +2167,6 @@ 1.76 ON "member"."id" = "ignored_initiative"."member_id" 1.77 AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 1.78 WHERE ( 1.79 - "member"."notify_event_id" ISNULL OR 1.80 - ( "member"."notify_event_id" NOTNULL AND 1.81 - "member"."notify_event_id" < "event"."id" ) ) 1.82 - AND ( 1.83 ( "member"."notify_level" >= 'all' ) OR 1.84 ( "member"."notify_level" >= 'voting' AND 1.85 "event"."state" IN ( 1.86 @@ -2168,7 +2194,7 @@ 1.87 AND "ignored_member"."member_id" ISNULL 1.88 AND "ignored_initiative"."member_id" ISNULL; 1.89 1.90 -COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"'; 1.91 +COMMENT ON VIEW "selected_event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests, support and members "notify_level"'; 1.92 1.93 1.94 CREATE TYPE "timeline_event" AS ENUM (