liquid_feedback_core
changeset 492:917a65357efb
Updated preliminary update script for new notification system
author | jbe |
---|---|
date | Sun Apr 03 19:48:32 2016 +0200 (2016-04-03) |
parents | aa94c7dbb20f |
children | d932363da4ee |
files | update/core-update.v3.1.0-v3.2.0.sql |
line diff
1.1 --- a/update/core-update.v3.1.0-v3.2.0.sql Sun Apr 03 19:42:09 2016 +0200 1.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql Sun Apr 03 19:48:32 2016 +0200 1.3 @@ -5,30 +5,40 @@ 1.4 AS "subquery"("string", "major", "minor", "revision"); 1.5 1.6 ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE; 1.7 -ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 0; 1.8 -ALTER TABLE "member" ADD COLUMN "sample_size" INT4 NOT NULL DEFAULT 3; 1.9 -ALTER TABLE "member" ADD COLUMN "last_notified_suggestion_id" INT8; 1.10 +ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1; 1.11 +ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3; 1.12 +ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6); 1.13 +ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23); 1.14 1.15 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none'; 1.16 1.17 -CREATE TABLE "subscription_time" ( 1.18 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.19 - "day_of_week" INT2 CONSTRAINT "day_of_week_range" CHECK ("day_of_week" BETWEEN 0 AND 6), 1.20 - "time_of_day" TIME NOT NULL ); 1.21 -CREATE UNIQUE INDEX "subscription_time_all_days_of_week_time_of_day_idx" ON "subscription_time" ("time_of_day", "member_id") WHERE ("day_of_week" ISNULL); 1.22 -CREATE UNIQUE INDEX "subscription_time_day_of_week_time_of_day_idx" ON "subscription_time" ("day_of_week", "time_of_day", "member_id"); 1.23 - 1.24 CREATE TABLE "subscription" ( 1.25 PRIMARY KEY ("member_id", "unit_id"), 1.26 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.27 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.28 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id"); 1.29 - 1.30 -DROP VIEW "selected_event_seen_by_member"; 1.31 + 1.32 +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.33 + 1.34 +CREATE TABLE "ignored_area" ( 1.35 + PRIMARY KEY ("member_id", "area_id"), 1.36 + "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.37 + "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 1.38 +CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id"); 1.39 + 1.40 +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.41 + 1.42 +CREATE TABLE "initiative_notification_sent" ( 1.43 + PRIMARY KEY ("member_id", "initiative_id"), 1.44 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.45 + "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.46 + "last_draft_id" INT8 NOT NULL, 1.47 + "last_suggestion_id" INT8 NOT NULL ); 1.48 +CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id"); 1.49 1.50 CREATE VIEW "updated_initiative" AS 1.51 SELECT 1.52 - "member"."id" AS "seen_by_member_id", 1.53 + "supporter"."member_id" AS "seen_by_member_id", 1.54 TRUE AS "supported", 1.55 EXISTS ( 1.56 SELECT NULL FROM "draft" 1.57 @@ -36,9 +46,13 @@ 1.58 AND "draft"."id" > "supporter"."draft_id" 1.59 ) AS "new_draft", 1.60 ( SELECT count(1) FROM "suggestion" 1.61 + LEFT JOIN "opinion" ON 1.62 + "opinion"."member_id" = "supporter"."member_id" AND 1.63 + "opinion"."suggestion_id" = "suggestion"."id" 1.64 WHERE "suggestion"."initiative_id" = "initiative"."id" 1.65 + AND "opinion"."member_id" ISNULL 1.66 AND COALESCE( 1.67 - "suggestion"."id" > "member"."last_notified_suggestion_id", 1.68 + "suggestion"."id" > "sent"."last_suggestion_id", 1.69 TRUE 1.70 ) 1.71 ) AS "new_suggestion_count", 1.72 @@ -54,11 +68,12 @@ 1.73 -"initiative"."id" ) 1.74 ) AS "leading", 1.75 "initiative".* 1.76 - FROM "member" CROSS JOIN "initiative" 1.77 + FROM "supporter" JOIN "initiative" 1.78 + ON "supporter"."initiative_id" = "initiative"."id" 1.79 + LEFT JOIN "initiative_notification_sent" AS "sent" 1.80 + ON "sent"."member_id" = "supporter"."member_id" 1.81 + AND "sent"."initiative_id" = "initiative"."id" 1.82 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.83 - JOIN "supporter" ON 1.84 - "supporter"."member_id" = "member"."id" AND 1.85 - "supporter"."initiative_id" = "initiative"."id" 1.86 WHERE "issue"."state" IN ('admission', 'discussion') 1.87 AND ( 1.88 EXISTS ( 1.89 @@ -67,9 +82,13 @@ 1.90 AND "draft"."id" > "supporter"."draft_id" 1.91 ) OR EXISTS ( 1.92 SELECT NULL FROM "suggestion" 1.93 + LEFT JOIN "opinion" ON 1.94 + "opinion"."member_id" = "supporter"."member_id" AND 1.95 + "opinion"."suggestion_id" = "suggestion"."id" 1.96 WHERE "suggestion"."initiative_id" = "initiative"."id" 1.97 + AND "opinion"."member_id" ISNULL 1.98 AND COALESCE( 1.99 - "suggestion"."id" > "member"."last_notified_suggestion_id", 1.100 + "suggestion"."id" > "sent"."last_suggestion_id", 1.101 TRUE 1.102 ) 1.103 ) 1.104 @@ -125,7 +144,7 @@ 1.105 "match_v" := TRUE; 1.106 "initiative_id_ary" := "initiative_id_ary" || "result_row"."id"; 1.107 RETURN NEXT "result_row"; 1.108 - IF array_length("initiative_id_ary", 1) >= "member_row"."sample_size" THEN 1.109 + IF array_length("initiative_id_ary", 1) >= "member_row"."notification_sample_size" THEN 1.110 RETURN; 1.111 END IF; 1.112 END IF; 1.113 @@ -142,8 +161,21 @@ 1.114 SELECT 1.115 "member"."id" AS "seen_by_member_id", 1.116 FALSE AS "supported", 1.117 - NULL::BOOLEAN AS "new_draft", 1.118 - NULL::INTEGER AS "new_suggestion_count", 1.119 + EXISTS ( 1.120 + SELECT NULL FROM "draft" 1.121 + WHERE "draft"."initiative_id" = "initiative"."id" 1.122 + AND COALESCE( 1.123 + "draft"."id" > "sent"."last_draft_id", 1.124 + TRUE 1.125 + ) 1.126 + ) AS "new_draft", 1.127 + ( SELECT count(1) FROM "suggestion" 1.128 + WHERE "suggestion"."initiative_id" = "initiative"."id" 1.129 + AND COALESCE( 1.130 + "suggestion"."id" > "sent"."last_suggestion_id", 1.131 + TRUE 1.132 + ) 1.133 + ) AS "new_suggestion_count", 1.134 TRUE AS "featured", 1.135 NOT EXISTS ( 1.136 SELECT NULL FROM "initiative" AS "better_initiative" 1.137 @@ -158,21 +190,47 @@ 1.138 "initiative".* 1.139 FROM "member" CROSS JOIN "area" 1.140 CROSS JOIN LATERAL 1.141 - "featured_initiative"("member"."id", "area"."id") AS "initiative"; 1.142 + "featured_initiative"("member"."id", "area"."id") AS "initiative" 1.143 + LEFT JOIN "initiative_notification_sent" AS "sent" 1.144 + ON "sent"."member_id" = "member"."id" 1.145 + AND "sent"."initiative_id" = "initiative"."id"; 1.146 1.147 CREATE VIEW "leading_complement_initiative" AS 1.148 SELECT * FROM ( 1.149 SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id") 1.150 - "updated_or_featured_initiative"."seen_by_member_id", 1.151 - FALSE AS "supported", 1.152 - NULL::BOOLEAN AS "new_draft", 1.153 - NULL::INTEGER AS "new_suggestion_count", 1.154 + "uf_initiative"."seen_by_member_id", 1.155 + "supporter"."member_id" NOTNULL AS "supported", 1.156 + CASE WHEN "supporter"."member_id" NOTNULL THEN FALSE ELSE 1.157 + EXISTS ( 1.158 + SELECT NULL FROM "draft" 1.159 + WHERE "draft"."initiative_id" = "initiative"."id" 1.160 + AND COALESCE( 1.161 + "draft"."id" > "sent"."last_draft_id", 1.162 + TRUE 1.163 + ) 1.164 + ) 1.165 + END AS "new_draft", 1.166 + CASE WHEN "supporter"."member_id" NOTNULL THEN 0 ELSE 1.167 + ( SELECT count(1) FROM "suggestion" 1.168 + WHERE "suggestion"."initiative_id" = "initiative"."id" 1.169 + AND COALESCE( 1.170 + "suggestion"."id" > "sent"."last_suggestion_id", 1.171 + TRUE 1.172 + ) 1.173 + ) 1.174 + END AS "new_suggestion_count", 1.175 FALSE AS "featured", 1.176 TRUE AS "leading", 1.177 "initiative".* 1.178 - FROM "updated_or_featured_initiative" 1.179 - JOIN "initiative" 1.180 - ON "updated_or_featured_initiative"."issue_id" = "initiative"."issue_id" 1.181 + FROM "updated_or_featured_initiative" AS "uf_initiative" 1.182 + JOIN "initiative" ON 1.183 + "uf_initiative"."issue_id" = "initiative"."issue_id" 1.184 + LEFT JOIN "supporter" ON 1.185 + "supporter"."member_id" = "uf_initiative"."seen_by_member_id" AND 1.186 + "supporter"."initiative_id" = "initiative"."id" 1.187 + LEFT JOIN "initiative_notification_sent" AS "sent" 1.188 + ON "sent"."member_id" = "uf_initiative"."seen_by_member_id" 1.189 + AND "sent"."initiative_id" = "initiative"."id" 1.190 ORDER BY 1.191 "seen_by_member_id", 1.192 "initiative"."issue_id", 1.193 @@ -185,9 +243,75 @@ 1.194 AND "other"."id" = "subquery"."id" 1.195 ); 1.196 1.197 -CREATE VIEW "initiative_for_notification" AS 1.198 +CREATE VIEW "unfiltered_initiative_for_notification" AS 1.199 SELECT * FROM "updated_or_featured_initiative" 1.200 UNION ALL 1.201 SELECT * FROM "leading_complement_initiative"; 1.202 1.203 +CREATE VIEW "initiative_for_notification" AS 1.204 + SELECT "initiative1".* 1.205 + FROM "unfiltered_initiative_for_notification" "initiative1" 1.206 + JOIN "issue" AS "issue1" ON "initiative1"."issue_id" = "issue1"."id" 1.207 + WHERE EXISTS ( 1.208 + SELECT NULL 1.209 + FROM "unfiltered_initiative_for_notification" "initiative2" 1.210 + JOIN "issue" AS "issue2" ON "initiative2"."issue_id" = "issue2"."id" 1.211 + WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id" 1.212 + AND "issue1"."area_id" = "issue2"."area_id" 1.213 + AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 ) 1.214 + ); 1.215 + 1.216 +CREATE FUNCTION "get_initiatives_for_notification" 1.217 + ( "member_id_p" "member"."id"%TYPE ) 1.218 + RETURNS SETOF "initiative_for_notification" 1.219 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.220 + DECLARE 1.221 + "result_row" "initiative_for_notification"%ROWTYPE; 1.222 + "last_draft_id_v" "draft"."id"%TYPE; 1.223 + "last_suggestion_id_v" "suggestion"."id"%TYPE; 1.224 + BEGIN 1.225 + PERFORM "require_transaction_isolation"(); 1.226 + PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE; 1.227 + FOR "result_row" IN 1.228 + SELECT * FROM "initiative_for_notification" 1.229 + WHERE "seen_by_member_id" = "member_id_p" 1.230 + LOOP 1.231 + SELECT "id" INTO "last_draft_id_v" FROM "draft" 1.232 + WHERE "draft"."initiative_id" = "result_row"."id" 1.233 + ORDER BY "id" DESC LIMIT 1; 1.234 + SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion" 1.235 + WHERE "suggestion"."initiative_id" = "result_row"."id" 1.236 + ORDER BY "id" DESC LIMIT 1; 1.237 + INSERT INTO "initiative_notification_sent" 1.238 + ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 1.239 + VALUES ( 1.240 + "member_id_p", 1.241 + "result_row"."id", 1.242 + "last_draft_id", 1.243 + "last_suggestion_id" ) 1.244 + ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET 1.245 + "last_draft_id" = CASE 1.246 + WHEN "last_draft_id" > "last_draft_id_v" 1.247 + THEN "last_draft_id" 1.248 + ELSE "last_draft_id_v" 1.249 + END, 1.250 + "last_suggestion_id" = CASE 1.251 + WHEN "last_suggestion_id" > "last_suggestion_id_v" 1.252 + THEN "last_suggestion_id" 1.253 + ELSE "last_suggestion_id_v" 1.254 + END; 1.255 + RETURN NEXT "result_row"; 1.256 + END LOOP; 1.257 + DELETE FROM "initiative_notification_sent" 1.258 + USING "initiative", "issue" 1.259 + WHERE "initiative_notification_sent"."member_id" = "member_id_p" 1.260 + AND "initiative"."id" = "initiative_notification_sent"."initiative_id" 1.261 + AND "issue"."id" = "initiative"."issue_id" 1.262 + AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL ); 1.263 + UPDATE "member" SET "notification_counter" = "notification_counter" + 1 1.264 + WHERE "id" = "member_id_p"; 1.265 + RETURN; 1.266 + END; 1.267 + $$; 1.268 + 1.269 COMMIT;