liquid_feedback_core

changeset 460:6d4e51332251

Work on new notification system: new column "disable_notifications" in "member" table, new tables "subscription" and "ignored_area", dropped view "selected_event_seen_by_member"
author jbe
date Wed Mar 16 00:29:12 2016 +0100 (2016-03-16)
parents 0efd132b3f6d
children 4c3522ba2552
files core.sql
line diff
     1.1 --- a/core.sql	Mon Dec 14 17:51:24 2015 +0100
     1.2 +++ b/core.sql	Wed Mar 16 00:29:12 2016 +0100
     1.3 @@ -7,7 +7,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('3.1.0', 3, 1, 0))
     1.8 +  SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -119,7 +119,7 @@
    1.13          "notify_email_secret"          TEXT     UNIQUE,
    1.14          "notify_email_secret_expiry"   TIMESTAMPTZ,
    1.15          "notify_email_lock_expiry"     TIMESTAMPTZ,
    1.16 -        "notify_level"          "notify_level",
    1.17 +        "disable_notifications" BOOLEAN         NOT NULL DEFAULT FALSE,
    1.18          "login_recovery_expiry"        TIMESTAMPTZ,
    1.19          "password_reset_secret"        TEXT     UNIQUE,
    1.20          "password_reset_secret_expiry" TIMESTAMPTZ,
    1.21 @@ -186,7 +186,7 @@
    1.22  COMMENT ON COLUMN "member"."notify_email_secret"        IS 'Secret sent to the address in "notify_email_unconformed"';
    1.23  COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
    1.24  COMMENT ON COLUMN "member"."notify_email_lock_expiry"   IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
    1.25 -COMMENT ON COLUMN "member"."notify_level"         IS 'Selects which event notifications are to be sent to the "notify_email" mail address, may be NULL if member did not make any selection yet';
    1.26 +COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications';
    1.27  COMMENT ON COLUMN "member"."login_recovery_expiry"        IS 'Date/time after which another login recovery attempt is allowed';
    1.28  COMMENT ON COLUMN "member"."password_reset_secret"        IS 'Secret string sent via e-mail for password recovery';
    1.29  COMMENT ON COLUMN "member"."password_reset_secret_expiry" IS 'Date/time until the password recovery secret is valid, and date/time after which another password recovery attempt is allowed';
    1.30 @@ -909,6 +909,24 @@
    1.31  COMMENT ON COLUMN "privilege"."polling_right"    IS 'Right to create issues with policies having the "policy"."polling" flag set, and to add initiatives having the "initiative"."polling" flag set to those issues';
    1.32  
    1.33  
    1.34 +CREATE TABLE "subscription" (
    1.35 +        PRIMARY KEY ("unit_id", "member_id"),
    1.36 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.37 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.38 +CREATE INDEX "subscription_member_id_idx" ON "subscription" ("member_id");
    1.39 +
    1.40 +COMMENT ON TABLE "subscription" IS 'An entry in this table denotes that the member wishes to receive notifications regardless of his/her privileges in the given unit';
    1.41 +
    1.42 +
    1.43 +CREATE TABLE "ignored_area" (
    1.44 +        PRIMARY KEY ("area_id", "member_id"),
    1.45 +        "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.46 +        "member_id"             INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.47 +CREATE INDEX "ignored_area_member_id_idx" ON "ignored_area" ("member_id");
    1.48 +
    1.49 +COMMENT ON TABLE "ignored_area" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given subject area unless he/she declared interested in a particular issue';
    1.50 +
    1.51 +
    1.52  CREATE TABLE "membership" (
    1.53          PRIMARY KEY ("area_id", "member_id"),
    1.54          "area_id"               INT4            REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.55 @@ -2352,79 +2370,6 @@
    1.56  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.57  
    1.58  
    1.59 -CREATE VIEW "selected_event_seen_by_member" AS
    1.60 -  SELECT
    1.61 -    "member"."id" AS "seen_by_member_id",
    1.62 -    CASE WHEN "event"."state" IN (
    1.63 -      'voting',
    1.64 -      'finished_without_winner',
    1.65 -      'finished_with_winner'
    1.66 -    ) THEN
    1.67 -      'voting'::"notify_level"
    1.68 -    ELSE
    1.69 -      CASE WHEN "event"."state" IN (
    1.70 -        'verification',
    1.71 -        'canceled_after_revocation_during_verification',
    1.72 -        'canceled_no_initiative_admitted'
    1.73 -      ) THEN
    1.74 -        'verification'::"notify_level"
    1.75 -      ELSE
    1.76 -        CASE WHEN "event"."state" IN (
    1.77 -          'discussion',
    1.78 -          'canceled_after_revocation_during_discussion'
    1.79 -        ) THEN
    1.80 -          'discussion'::"notify_level"
    1.81 -        ELSE
    1.82 -          'all'::"notify_level"
    1.83 -        END
    1.84 -      END
    1.85 -    END AS "notify_level",
    1.86 -    "event".*
    1.87 -  FROM "member" CROSS JOIN "event"
    1.88 -  LEFT JOIN "issue"
    1.89 -    ON "event"."issue_id" = "issue"."id"
    1.90 -  LEFT JOIN "membership"
    1.91 -    ON "member"."id" = "membership"."member_id"
    1.92 -    AND "issue"."area_id" = "membership"."area_id"
    1.93 -  LEFT JOIN "interest"
    1.94 -    ON "member"."id" = "interest"."member_id"
    1.95 -    AND "event"."issue_id" = "interest"."issue_id"
    1.96 -  LEFT JOIN "ignored_member"
    1.97 -    ON "member"."id" = "ignored_member"."member_id"
    1.98 -    AND "event"."member_id" = "ignored_member"."other_member_id"
    1.99 -  LEFT JOIN "ignored_initiative"
   1.100 -    ON "member"."id" = "ignored_initiative"."member_id"
   1.101 -    AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
   1.102 -  WHERE (
   1.103 -    ( "member"."notify_level" >= 'all' ) OR
   1.104 -    ( "member"."notify_level" >= 'voting' AND
   1.105 -      "event"."state" IN (
   1.106 -        'voting',
   1.107 -        'finished_without_winner',
   1.108 -        'finished_with_winner' ) ) OR
   1.109 -    ( "member"."notify_level" >= 'verification' AND
   1.110 -      "event"."state" IN (
   1.111 -        'verification',
   1.112 -        'canceled_after_revocation_during_verification',
   1.113 -        'canceled_no_initiative_admitted' ) ) OR
   1.114 -    ( "member"."notify_level" >= 'discussion' AND
   1.115 -      "event"."state" IN (
   1.116 -        'discussion',
   1.117 -        'canceled_after_revocation_during_discussion' ) ) )
   1.118 -  AND (
   1.119 -    "interest"."member_id" NOTNULL OR
   1.120 -    ( "membership"."member_id" NOTNULL AND
   1.121 -      "event"."event" IN (
   1.122 -        'issue_state_changed',
   1.123 -        'initiative_created_in_new_issue',
   1.124 -        'initiative_created_in_existing_issue',
   1.125 -        'initiative_revoked' ) ) )
   1.126 -  AND "ignored_member"."member_id" ISNULL
   1.127 -  AND "ignored_initiative"."member_id" ISNULL;
   1.128 -
   1.129 -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.130 -
   1.131 -
   1.132  
   1.133  ------------------------------------------------------
   1.134  -- Row set returning function for delegation chains --

Impressum / About Us