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"
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 (

Impressum / About Us