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