liquid_feedback_core
changeset 499:bc4b590a8eec
Revised notification system; Dropped tables "selected_event_seen_by_member" and "event_seen_by_member"
author | jbe |
---|---|
date | Mon Apr 04 18:42:56 2016 +0200 (2016-04-04) |
parents | 10b90162e982 |
children | c1a2954078d7 |
files | core.sql update/core-update.v3.1.0-v3.2.0.sql |
line diff
1.1 --- a/core.sql Sun Apr 03 20:57:44 2016 +0200 1.2 +++ b/core.sql Mon Apr 04 18:42:56 2016 +0200 1.3 @@ -89,12 +89,6 @@ 1.4 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame'; 1.5 1.6 1.7 -CREATE TYPE "notify_level" AS ENUM 1.8 - ('none', 'voting', 'verification', 'discussion', 'all'); 1.9 - 1.10 -COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything'; 1.11 - 1.12 - 1.13 CREATE TABLE "member" ( 1.14 "id" SERIAL4 PRIMARY KEY, 1.15 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.16 @@ -2339,135 +2333,96 @@ 1.17 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.'; 1.18 1.19 1.20 -CREATE VIEW "event_seen_by_member" AS 1.21 +CREATE VIEW "event_for_notification" AS 1.22 SELECT 1.23 - "member"."id" AS "seen_by_member_id", 1.24 - CASE WHEN "event"."state" IN ( 1.25 - 'voting', 1.26 - 'finished_without_winner', 1.27 - 'finished_with_winner' 1.28 - ) THEN 1.29 - 'voting'::"notify_level" 1.30 - ELSE 1.31 - CASE WHEN "event"."state" IN ( 1.32 - 'verification', 1.33 - 'canceled_after_revocation_during_verification', 1.34 - 'canceled_no_initiative_admitted' 1.35 - ) THEN 1.36 - 'verification'::"notify_level" 1.37 - ELSE 1.38 - CASE WHEN "event"."state" IN ( 1.39 - 'discussion', 1.40 - 'canceled_after_revocation_during_discussion' 1.41 - ) THEN 1.42 - 'discussion'::"notify_level" 1.43 - ELSE 1.44 - 'all'::"notify_level" 1.45 - END 1.46 - END 1.47 - END AS "notify_level", 1.48 + "member"."id" AS "recipient_id", 1.49 "event".* 1.50 FROM "member" CROSS JOIN "event" 1.51 - LEFT JOIN "issue" 1.52 - ON "event"."issue_id" = "issue"."id" 1.53 - LEFT JOIN "membership" 1.54 - ON "member"."id" = "membership"."member_id" 1.55 - AND "issue"."area_id" = "membership"."area_id" 1.56 - LEFT JOIN "interest" 1.57 - ON "member"."id" = "interest"."member_id" 1.58 - AND "event"."issue_id" = "interest"."issue_id" 1.59 - LEFT JOIN "ignored_member" 1.60 - ON "member"."id" = "ignored_member"."member_id" 1.61 - AND "event"."member_id" = "ignored_member"."other_member_id" 1.62 - LEFT JOIN "ignored_initiative" 1.63 - ON "member"."id" = "ignored_initiative"."member_id" 1.64 - AND "event"."initiative_id" = "ignored_initiative"."initiative_id" 1.65 - WHERE ( 1.66 - "interest"."member_id" NOTNULL OR 1.67 - ( "membership"."member_id" NOTNULL AND 1.68 - "event"."event" IN ( 1.69 - 'issue_state_changed', 1.70 - 'initiative_created_in_new_issue', 1.71 - 'initiative_created_in_existing_issue', 1.72 - 'initiative_revoked' ) ) ) 1.73 - AND "ignored_member"."member_id" ISNULL 1.74 - AND "ignored_initiative"."member_id" ISNULL; 1.75 - 1.76 -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.77 + JOIN "issue" ON "issue"."id" = "event"."issue_id" 1.78 + JOIN "area" ON "area"."id" = "issue"."area_id" 1.79 + LEFT JOIN "privilege" ON 1.80 + "privilege"."member_id" = "member"."id" AND 1.81 + "privilege"."unit_id" = "area"."unit_id" AND 1.82 + "privilege"."voting_right" = TRUE 1.83 + LEFT JOIN "subscription" ON 1.84 + "subscription"."member_id" = "member"."id" AND 1.85 + "subscription"."unit_id" = "area"."unit_id" 1.86 + LEFT JOIN "ignored_area" ON 1.87 + "ignored_area"."member_id" = "member"."id" AND 1.88 + "ignored_area"."area_id" = "issue"."area_id" 1.89 + LEFT JOIN "interest" ON 1.90 + "interest"."member_id" = "member"."id" AND 1.91 + "interest"."issue_id" = "event"."issue_id" 1.92 + LEFT JOIN "supporter" ON 1.93 + "supporter"."member_id" = "member"."id" AND 1.94 + "supporter"."initiative_id" = "event"."initiative_id" 1.95 + WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL) 1.96 + AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL) 1.97 + AND ( 1.98 + "event"."event" = 'issue_state_changed'::"event_type" OR 1.99 + ( "event"."event" = 'initiative_revoked'::"event_type" AND 1.100 + "supporter"."member_id" NOTNULL ) ); 1.101 + 1.102 +COMMENT ON VIEW "event_for_notification" IS 'TODO: documentation'; 1.103 1.104 1.105 CREATE VIEW "updated_initiative" AS 1.106 SELECT 1.107 - "supporter"."member_id" AS "seen_by_member_id", 1.108 - TRUE AS "supported", 1.109 - EXISTS ( 1.110 - SELECT NULL FROM "draft" 1.111 - WHERE "draft"."initiative_id" = "initiative"."id" 1.112 - AND "draft"."id" > "supporter"."draft_id" 1.113 - ) AS "new_draft", 1.114 - ( SELECT count(1) FROM "suggestion" 1.115 - LEFT JOIN "opinion" ON 1.116 - "opinion"."member_id" = "supporter"."member_id" AND 1.117 - "opinion"."suggestion_id" = "suggestion"."id" 1.118 - WHERE "suggestion"."initiative_id" = "initiative"."id" 1.119 - AND "opinion"."member_id" ISNULL 1.120 - AND COALESCE( 1.121 - "suggestion"."id" > "sent"."last_suggestion_id", 1.122 - TRUE 1.123 - ) 1.124 - ) AS "new_suggestion_count", 1.125 + "supporter"."member_id" AS "recipient_id", 1.126 FALSE AS "featured", 1.127 - NOT EXISTS ( 1.128 - SELECT NULL FROM "initiative" AS "better_initiative" 1.129 - WHERE 1.130 - "better_initiative"."issue_id" = "initiative"."issue_id" 1.131 - AND 1.132 - ( COALESCE("better_initiative"."harmonic_weight", -1), 1.133 - -"better_initiative"."id" ) > 1.134 - ( COALESCE("initiative"."harmonic_weight", -1), 1.135 - -"initiative"."id" ) 1.136 - ) AS "leading", 1.137 - "initiative".* 1.138 - FROM "supporter" JOIN "initiative" 1.139 - ON "supporter"."initiative_id" = "initiative"."id" 1.140 - LEFT JOIN "initiative_notification_sent" AS "sent" 1.141 - ON "sent"."member_id" = "supporter"."member_id" 1.142 - AND "sent"."initiative_id" = "initiative"."id" 1.143 + "supporter"."initiative_id" 1.144 + FROM "supporter" 1.145 + JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id" 1.146 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.147 + LEFT JOIN "initiative_notification_sent" AS "sent" ON 1.148 + "sent"."member_id" = "supporter"."member_id" AND 1.149 + "sent"."initiative_id" = "supporter"."initiative_id" 1.150 + LEFT JOIN "ignored_initiative" ON 1.151 + "ignored_initiative"."member_id" = "supporter"."member_id" AND 1.152 + "ignored_initiative"."initiative_id" = "supporter"."initiative_id" 1.153 WHERE "issue"."state" IN ('admission', 'discussion') 1.154 + AND "ignored_initiative"."member_id" ISNULL 1.155 AND ( 1.156 EXISTS ( 1.157 SELECT NULL FROM "draft" 1.158 - WHERE "draft"."initiative_id" = "initiative"."id" 1.159 + LEFT JOIN "ignored_member" ON 1.160 + "ignored_member"."member_id" = "supporter"."member_id" AND 1.161 + "ignored_member"."other_member_id" = "draft"."author_id" 1.162 + WHERE "draft"."initiative_id" = "supporter"."initiative_id" 1.163 AND "draft"."id" > "supporter"."draft_id" 1.164 + AND "ignored_member"."member_id" ISNULL 1.165 ) OR EXISTS ( 1.166 SELECT NULL FROM "suggestion" 1.167 LEFT JOIN "opinion" ON 1.168 "opinion"."member_id" = "supporter"."member_id" AND 1.169 "opinion"."suggestion_id" = "suggestion"."id" 1.170 - WHERE "suggestion"."initiative_id" = "initiative"."id" 1.171 + LEFT JOIN "ignored_member" ON 1.172 + "ignored_member"."member_id" = "supporter"."member_id" AND 1.173 + "ignored_member"."other_member_id" = "suggestion"."author_id" 1.174 + WHERE "suggestion"."initiative_id" = "supporter"."initiative_id" 1.175 AND "opinion"."member_id" ISNULL 1.176 - AND COALESCE( 1.177 - "suggestion"."id" > "sent"."last_suggestion_id", 1.178 - TRUE 1.179 - ) 1.180 + AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE) 1.181 + AND "ignored_member"."member_id" ISNULL 1.182 ) 1.183 ); 1.184 1.185 CREATE FUNCTION "featured_initiative" 1.186 - ( "member_id_p" "member"."id"%TYPE, 1.187 - "area_id_p" "area"."id"%TYPE ) 1.188 - RETURNS SETOF "initiative" 1.189 + ( "recipient_id_p" "member"."id"%TYPE, 1.190 + "area_id_p" "area"."id"%TYPE ) 1.191 + RETURNS SETOF "initiative"."id"%TYPE 1.192 LANGUAGE 'plpgsql' STABLE AS $$ 1.193 DECLARE 1.194 - "member_row" "member"%ROWTYPE; 1.195 + "counter_v" "member"."notification_counter"%TYPE; 1.196 + "sample_size_v" "member"."notification_sample_size"%TYPE; 1.197 + "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[] 1.198 + "match_v" BOOLEAN; 1.199 "member_id_v" "member"."id"%TYPE; 1.200 "seed_v" TEXT; 1.201 - "result_row" "initiative"%ROWTYPE; 1.202 - "match_v" BOOLEAN; 1.203 - "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[] 1.204 + "initiative_id_v" "initiative"."id"%TYPE; 1.205 BEGIN 1.206 - SELECT INTO "member_row" * FROM "member" WHERE "id" = "member_id_p"; 1.207 + SELECT "notification_counter", "notification_sample_size" 1.208 + INTO "counter_v", "sample_size_v" 1.209 + FROM "member" WHERE "id" = "recipient_id_p"; 1.210 "initiative_id_ary" := '{}'; 1.211 LOOP 1.212 "match_v" := FALSE; 1.213 @@ -2475,38 +2430,64 @@ 1.214 SELECT * FROM ( 1.215 SELECT DISTINCT 1.216 "supporter"."member_id", 1.217 - md5("member_id_p" || '-' || "member_row"."notification_counter" || '-' || "area_id_p" || '-' || "supporter"."member_id") AS "seed" 1.218 + md5( 1.219 + "recipient_id_p" || '-' || 1.220 + "counter_v" || '-' || 1.221 + "area_id_p" || '-' || 1.222 + "supporter"."member_id" 1.223 + ) AS "seed" 1.224 FROM "supporter" 1.225 - JOIN "member" ON "member"."id" = "supporter"."member_id" 1.226 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id" 1.227 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.228 - WHERE "supporter"."member_id" != "member_id_p" 1.229 + WHERE "supporter"."member_id" != "recipient_id_p" 1.230 AND "issue"."area_id" = "area_id_p" 1.231 AND "issue"."state" IN ('admission', 'discussion', 'verification') 1.232 ) AS "subquery" 1.233 ORDER BY "seed" 1.234 LOOP 1.235 - SELECT "initiative".* INTO "result_row" 1.236 + SELECT "initiative"."id" INTO "initiative_id_v" 1.237 FROM "initiative" 1.238 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.239 + JOIN "area" ON "area"."id" = "issue"."area_id" 1.240 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id" 1.241 LEFT JOIN "supporter" AS "self_support" ON 1.242 "self_support"."initiative_id" = "initiative"."id" AND 1.243 - "self_support"."member_id" = "member_id_p" 1.244 + "self_support"."member_id" = "recipient_id_p" 1.245 + LEFT JOIN "privilege" ON 1.246 + "privilege"."member_id" = "recipient_id_p" AND 1.247 + "privilege"."unit_id" = "area"."unit_id" AND 1.248 + "privilege"."voting_right" = TRUE 1.249 + LEFT JOIN "subscription" ON 1.250 + "subscription"."member_id" = "recipient_id_p" AND 1.251 + "subscription"."unit_id" = "area"."unit_id" 1.252 + LEFT JOIN "ignored_initiative" ON 1.253 + "ignored_initiative"."member_id" = "recipient_id_p" AND 1.254 + "ignored_initiative"."initiative_id" = "initiative"."id" 1.255 WHERE "supporter"."member_id" = "member_id_v" 1.256 AND "issue"."area_id" = "area_id_p" 1.257 AND "issue"."state" IN ('admission', 'discussion', 'verification') 1.258 AND "self_support"."member_id" ISNULL 1.259 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"] 1.260 + AND ( 1.261 + "privilege"."member_id" NOTNULL OR 1.262 + "subscription"."member_id" NOTNULL ) 1.263 + AND "ignored_initiative"."member_id" ISNULL 1.264 + AND NOT EXISTS ( 1.265 + SELECT NULL FROM "draft" 1.266 + JOIN "ignored_member" ON 1.267 + "ignored_member"."member_id" = "recipient_id_p" AND 1.268 + "ignored_member"."other_member_id" = "draft"."author_id" 1.269 + WHERE "draft"."initiative_id" = "initiative"."id" 1.270 + ) 1.271 ORDER BY md5("seed_v" || '-' || "initiative"."id") 1.272 LIMIT 1; 1.273 IF FOUND THEN 1.274 "match_v" := TRUE; 1.275 - "initiative_id_ary" := "initiative_id_ary" || "result_row"."id"; 1.276 - RETURN NEXT "result_row"; 1.277 - IF array_length("initiative_id_ary", 1) >= "member_row"."notification_sample_size" THEN 1.278 + RETURN NEXT "initiative_id_v"; 1.279 + IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN 1.280 RETURN; 1.281 END IF; 1.282 + "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v"; 1.283 END IF; 1.284 END LOOP; 1.285 EXIT WHEN NOT "match_v"; 1.286 @@ -2516,115 +2497,120 @@ 1.287 $$; 1.288 1.289 CREATE VIEW "updated_or_featured_initiative" AS 1.290 - SELECT * FROM "updated_initiative" 1.291 - UNION ALL 1.292 SELECT 1.293 - "member"."id" AS "seen_by_member_id", 1.294 - FALSE AS "supported", 1.295 - EXISTS ( 1.296 - SELECT NULL FROM "draft" 1.297 - WHERE "draft"."initiative_id" = "initiative"."id" 1.298 - AND COALESCE( 1.299 - "draft"."id" > "sent"."last_draft_id", 1.300 - TRUE 1.301 - ) 1.302 - ) AS "new_draft", 1.303 - ( SELECT count(1) FROM "suggestion" 1.304 - WHERE "suggestion"."initiative_id" = "initiative"."id" 1.305 - AND COALESCE( 1.306 - "suggestion"."id" > "sent"."last_suggestion_id", 1.307 - TRUE 1.308 - ) 1.309 - ) AS "new_suggestion_count", 1.310 - TRUE AS "featured", 1.311 + "subquery".*, 1.312 NOT EXISTS ( 1.313 SELECT NULL FROM "initiative" AS "better_initiative" 1.314 - WHERE 1.315 - "better_initiative"."issue_id" = "initiative"."issue_id" 1.316 + WHERE "better_initiative"."issue_id" = "initiative"."issue_id" 1.317 AND 1.318 ( COALESCE("better_initiative"."harmonic_weight", -1), 1.319 -"better_initiative"."id" ) > 1.320 ( COALESCE("initiative"."harmonic_weight", -1), 1.321 -"initiative"."id" ) 1.322 - ) AS "leading", 1.323 - "initiative".* 1.324 - FROM "member" CROSS JOIN "area" 1.325 - CROSS JOIN LATERAL 1.326 - "featured_initiative"("member"."id", "area"."id") AS "initiative" 1.327 - LEFT JOIN "initiative_notification_sent" AS "sent" 1.328 - ON "sent"."member_id" = "member"."id" 1.329 - AND "sent"."initiative_id" = "initiative"."id"; 1.330 + ) AS "leading" 1.331 + FROM ( 1.332 + SELECT * FROM "updated_initiative" 1.333 + UNION ALL 1.334 + SELECT 1.335 + "member"."id" AS "recipient_id", 1.336 + TRUE AS "featured", 1.337 + "featured_initiative_id" AS "initiative_id" 1.338 + FROM "member" CROSS JOIN "area" 1.339 + CROSS JOIN LATERAL 1.340 + "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id" 1.341 + JOIN "initiative" ON "initiative"."id" = "featured_initiative_id" 1.342 + ) AS "subquery" 1.343 + JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"; 1.344 1.345 CREATE VIEW "leading_complement_initiative" AS 1.346 SELECT * FROM ( 1.347 - SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id") 1.348 - "uf_initiative"."seen_by_member_id", 1.349 - "supporter"."member_id" NOTNULL AS "supported", 1.350 - CASE WHEN "supporter"."member_id" NOTNULL THEN FALSE ELSE 1.351 - EXISTS ( 1.352 - SELECT NULL FROM "draft" 1.353 - WHERE "draft"."initiative_id" = "initiative"."id" 1.354 - AND COALESCE( 1.355 - "draft"."id" > "sent"."last_draft_id", 1.356 - TRUE 1.357 - ) 1.358 - ) 1.359 - END AS "new_draft", 1.360 - CASE WHEN "supporter"."member_id" NOTNULL THEN 0 ELSE 1.361 - ( SELECT count(1) FROM "suggestion" 1.362 - WHERE "suggestion"."initiative_id" = "initiative"."id" 1.363 - AND COALESCE( 1.364 - "suggestion"."id" > "sent"."last_suggestion_id", 1.365 - TRUE 1.366 - ) 1.367 - ) 1.368 - END AS "new_suggestion_count", 1.369 + SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id") 1.370 + "uf_initiative"."recipient_id", 1.371 FALSE AS "featured", 1.372 - TRUE AS "leading", 1.373 - "initiative".* 1.374 + "uf_initiative"."initiative_id", 1.375 + TRUE AS "leading" 1.376 FROM "updated_or_featured_initiative" AS "uf_initiative" 1.377 + JOIN "initiative" AS "uf_initiative_full" ON 1.378 + "uf_initiative_full"."id" = "uf_initiative"."initiative_id" 1.379 JOIN "initiative" ON 1.380 - "uf_initiative"."issue_id" = "initiative"."issue_id" 1.381 - LEFT JOIN "supporter" ON 1.382 - "supporter"."member_id" = "uf_initiative"."seen_by_member_id" AND 1.383 - "supporter"."initiative_id" = "initiative"."id" 1.384 - LEFT JOIN "initiative_notification_sent" AS "sent" 1.385 - ON "sent"."member_id" = "uf_initiative"."seen_by_member_id" 1.386 - AND "sent"."initiative_id" = "initiative"."id" 1.387 + "initiative"."issue_id" = "uf_initiative_full"."issue_id" 1.388 ORDER BY 1.389 - "seen_by_member_id", 1.390 + "uf_initiative"."recipient_id", 1.391 "initiative"."issue_id", 1.392 "initiative"."harmonic_weight" DESC, 1.393 "initiative"."id" 1.394 ) AS "subquery" 1.395 WHERE NOT EXISTS ( 1.396 SELECT NULL FROM "updated_or_featured_initiative" AS "other" 1.397 - WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id" 1.398 - AND "other"."id" = "subquery"."id" 1.399 + WHERE "other"."recipient_id" = "subquery"."recipient_id" 1.400 + AND "other"."initiative_id" = "subquery"."initiative_id" 1.401 ); 1.402 1.403 CREATE VIEW "unfiltered_initiative_for_notification" AS 1.404 - SELECT * FROM "updated_or_featured_initiative" 1.405 - UNION ALL 1.406 - SELECT * FROM "leading_complement_initiative"; 1.407 + SELECT 1.408 + "subquery".*, 1.409 + "supporter"."member_id" NOTNULL AS "supported", 1.410 + CASE WHEN "supporter"."member_id" NOTNULL THEN 1.411 + EXISTS ( 1.412 + SELECT NULL FROM "draft" 1.413 + WHERE "draft"."initiative_id" = "subquery"."initiative_id" 1.414 + AND "draft"."id" > "supporter"."draft_id" 1.415 + ) 1.416 + ELSE 1.417 + EXISTS ( 1.418 + SELECT NULL FROM "draft" 1.419 + WHERE "draft"."initiative_id" = "subquery"."initiative_id" 1.420 + AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE) 1.421 + ) 1.422 + END AS "new_draft", 1.423 + CASE WHEN "supporter"."member_id" NOTNULL THEN 1.424 + ( SELECT count(1) FROM "suggestion" 1.425 + LEFT JOIN "opinion" ON 1.426 + "opinion"."member_id" = "supporter"."member_id" AND 1.427 + "opinion"."suggestion_id" = "suggestion"."id" 1.428 + WHERE "suggestion"."initiative_id" = "subquery"."initiative_id" 1.429 + AND "opinion"."member_id" ISNULL 1.430 + AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE) 1.431 + ) 1.432 + ELSE 1.433 + ( SELECT count(1) FROM "suggestion" 1.434 + WHERE "suggestion"."initiative_id" = "subquery"."initiative_id" 1.435 + AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE) 1.436 + ) 1.437 + END AS "new_suggestion_count" 1.438 + FROM ( 1.439 + SELECT * FROM "updated_or_featured_initiative" 1.440 + UNION ALL 1.441 + SELECT * FROM "leading_complement_initiative" 1.442 + ) AS "subquery" 1.443 + LEFT JOIN "supporter" ON 1.444 + "supporter"."member_id" = "subquery"."recipient_id" AND 1.445 + "supporter"."initiative_id" = "subquery"."initiative_id" 1.446 + LEFT JOIN "initiative_notification_sent" AS "sent" ON 1.447 + "sent"."member_id" = "subquery"."recipient_id" AND 1.448 + "sent"."initiative_id" = "subquery"."initiative_id"; 1.449 1.450 CREATE VIEW "initiative_for_notification" AS 1.451 - SELECT "initiative1".* 1.452 - FROM "unfiltered_initiative_for_notification" "initiative1" 1.453 - JOIN "issue" AS "issue1" ON "initiative1"."issue_id" = "issue1"."id" 1.454 + SELECT "unfiltered1".* 1.455 + FROM "unfiltered_initiative_for_notification" "unfiltered1" 1.456 + JOIN "initiative" AS "initiative1" ON 1.457 + "initiative1"."id" = "unfiltered1"."initiative_id" 1.458 + JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id" 1.459 WHERE EXISTS ( 1.460 SELECT NULL 1.461 - FROM "unfiltered_initiative_for_notification" "initiative2" 1.462 - JOIN "issue" AS "issue2" ON "initiative2"."issue_id" = "issue2"."id" 1.463 - WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id" 1.464 + FROM "unfiltered_initiative_for_notification" "unfiltered2" 1.465 + JOIN "initiative" AS "initiative2" ON 1.466 + "initiative2"."id" = "unfiltered2"."initiative_id" 1.467 + JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id" 1.468 + WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id" 1.469 AND "issue1"."area_id" = "issue2"."area_id" 1.470 - AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 ) 1.471 + AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 ) 1.472 ); 1.473 1.474 CREATE VIEW "newsletter_to_send" AS 1.475 SELECT 1.476 - "newsletter"."id" AS "newsletter_id", 1.477 - "member"."id" AS "member_id" 1.478 + "member"."id" AS "recipient_id", 1.479 + "newsletter"."id" AS "newsletter_id" 1.480 FROM "newsletter" CROSS JOIN "member" 1.481 LEFT JOIN "privilege" ON 1.482 "privilege"."member_id" = "member"."id" AND 1.483 @@ -3146,19 +3132,19 @@ 1.484 PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE; 1.485 FOR "result_row" IN 1.486 SELECT * FROM "initiative_for_notification" 1.487 - WHERE "seen_by_member_id" = "member_id_p" 1.488 + WHERE "member_id" = "member_id_p" 1.489 LOOP 1.490 SELECT "id" INTO "last_draft_id_v" FROM "draft" 1.491 - WHERE "draft"."initiative_id" = "result_row"."id" 1.492 + WHERE "draft"."initiative_id" = "result_row"."initiative_id" 1.493 ORDER BY "id" DESC LIMIT 1; 1.494 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion" 1.495 - WHERE "suggestion"."initiative_id" = "result_row"."id" 1.496 + WHERE "suggestion"."initiative_id" = "result_row"."initiative_id" 1.497 ORDER BY "id" DESC LIMIT 1; 1.498 INSERT INTO "initiative_notification_sent" 1.499 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 1.500 VALUES ( 1.501 "member_id_p", 1.502 - "result_row"."id", 1.503 + "result_row"."initiative_id", 1.504 "last_draft_id_v", 1.505 "last_suggestion_id_v" ) 1.506 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET 1.507 @@ -4979,6 +4965,11 @@ 1.508 "notify_email_secret" = NULL, 1.509 "notify_email_secret_expiry" = NULL, 1.510 "notify_email_lock_expiry" = NULL, 1.511 + "disable_notifications" = NULL, 1.512 + "notification_counter" = NULL, 1.513 + "notification_sample_size" = NULL, 1.514 + "notification_dow" = NULL, 1.515 + "notification_hour" = NULL, 1.516 "login_recovery_expiry" = NULL, 1.517 "password_reset_secret" = NULL, 1.518 "password_reset_secret_expiry" = NULL, 1.519 @@ -5047,7 +5038,11 @@ 1.520 "notify_email_secret" = NULL, 1.521 "notify_email_secret_expiry" = NULL, 1.522 "notify_email_lock_expiry" = NULL, 1.523 - "notify_level" = NULL, 1.524 + "disable_notifications" = NULL, 1.525 + "notification_counter" = NULL, 1.526 + "notification_sample_size" = NULL, 1.527 + "notification_dow" = NULL, 1.528 + "notification_hour" = NULL, 1.529 "login_recovery_expiry" = NULL, 1.530 "password_reset_secret" = NULL, 1.531 "password_reset_secret_expiry" = NULL,
2.1 --- a/update/core-update.v3.1.0-v3.2.0.sql Sun Apr 03 20:57:44 2016 +0200 2.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql Mon Apr 04 18:42:56 2016 +0200 2.3 @@ -4,6 +4,8 @@ 2.4 SELECT * FROM (VALUES ('3.2.0', 3, 2, 0)) 2.5 AS "subquery"("string", "major", "minor", "revision"); 2.6 2.7 +-- TODO: preliminary script 2.8 + 2.9 ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE; 2.10 ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1; 2.11 ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3; 2.12 @@ -11,6 +13,11 @@ 2.13 ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23); 2.14 2.15 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none'; 2.16 + 2.17 +DROP TABLE "selected_event_seen_by_member"; 2.18 +DROP TABLE "event_seen_by_member"; 2.19 +ALTER TABLE "member" DROP COLUMN "notify_level"; 2.20 +DROP TYPE "notify_level"; 2.21 2.22 CREATE TABLE "subscription" ( 2.23 PRIMARY KEY ("member_id", "unit_id"), 2.24 @@ -48,78 +55,93 @@ 2.25 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL; 2.26 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published"); 2.27 2.28 +CREATE VIEW "event_for_notification" AS 2.29 + SELECT 2.30 + "member"."id" AS "recipient_id", 2.31 + "event".* 2.32 + FROM "member" CROSS JOIN "event" 2.33 + JOIN "issue" ON "issue"."id" = "event"."issue_id" 2.34 + JOIN "area" ON "area"."id" = "issue"."area_id" 2.35 + LEFT JOIN "privilege" ON 2.36 + "privilege"."member_id" = "member"."id" AND 2.37 + "privilege"."unit_id" = "area"."unit_id" AND 2.38 + "privilege"."voting_right" = TRUE 2.39 + LEFT JOIN "subscription" ON 2.40 + "subscription"."member_id" = "member"."id" AND 2.41 + "subscription"."unit_id" = "area"."unit_id" 2.42 + LEFT JOIN "ignored_area" ON 2.43 + "ignored_area"."member_id" = "member"."id" AND 2.44 + "ignored_area"."area_id" = "issue"."area_id" 2.45 + LEFT JOIN "interest" ON 2.46 + "interest"."member_id" = "member"."id" AND 2.47 + "interest"."issue_id" = "event"."issue_id" 2.48 + LEFT JOIN "supporter" ON 2.49 + "supporter"."member_id" = "member"."id" AND 2.50 + "supporter"."initiative_id" = "event"."initiative_id" 2.51 + WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL) 2.52 + AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL) 2.53 + AND ( 2.54 + "event"."event" = 'issue_state_changed'::"event_type" OR 2.55 + ( "event"."event" = 'initiative_revoked'::"event_type" AND 2.56 + "supporter"."member_id" NOTNULL ) ); 2.57 + 2.58 CREATE VIEW "updated_initiative" AS 2.59 SELECT 2.60 - "supporter"."member_id" AS "seen_by_member_id", 2.61 - TRUE AS "supported", 2.62 - EXISTS ( 2.63 - SELECT NULL FROM "draft" 2.64 - WHERE "draft"."initiative_id" = "initiative"."id" 2.65 - AND "draft"."id" > "supporter"."draft_id" 2.66 - ) AS "new_draft", 2.67 - ( SELECT count(1) FROM "suggestion" 2.68 - LEFT JOIN "opinion" ON 2.69 - "opinion"."member_id" = "supporter"."member_id" AND 2.70 - "opinion"."suggestion_id" = "suggestion"."id" 2.71 - WHERE "suggestion"."initiative_id" = "initiative"."id" 2.72 - AND "opinion"."member_id" ISNULL 2.73 - AND COALESCE( 2.74 - "suggestion"."id" > "sent"."last_suggestion_id", 2.75 - TRUE 2.76 - ) 2.77 - ) AS "new_suggestion_count", 2.78 + "supporter"."member_id" AS "recipient_id", 2.79 FALSE AS "featured", 2.80 - NOT EXISTS ( 2.81 - SELECT NULL FROM "initiative" AS "better_initiative" 2.82 - WHERE 2.83 - "better_initiative"."issue_id" = "initiative"."issue_id" 2.84 - AND 2.85 - ( COALESCE("better_initiative"."harmonic_weight", -1), 2.86 - -"better_initiative"."id" ) > 2.87 - ( COALESCE("initiative"."harmonic_weight", -1), 2.88 - -"initiative"."id" ) 2.89 - ) AS "leading", 2.90 - "initiative".* 2.91 - FROM "supporter" JOIN "initiative" 2.92 - ON "supporter"."initiative_id" = "initiative"."id" 2.93 - LEFT JOIN "initiative_notification_sent" AS "sent" 2.94 - ON "sent"."member_id" = "supporter"."member_id" 2.95 - AND "sent"."initiative_id" = "initiative"."id" 2.96 + "supporter"."initiative_id" 2.97 + FROM "supporter" 2.98 + JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id" 2.99 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 2.100 + LEFT JOIN "initiative_notification_sent" AS "sent" ON 2.101 + "sent"."member_id" = "supporter"."member_id" AND 2.102 + "sent"."initiative_id" = "supporter"."initiative_id" 2.103 + LEFT JOIN "ignored_initiative" ON 2.104 + "ignored_initiative"."member_id" = "supporter"."member_id" AND 2.105 + "ignored_initiative"."initiative_id" = "supporter"."initiative_id" 2.106 WHERE "issue"."state" IN ('admission', 'discussion') 2.107 + AND "ignored_initiative"."member_id" ISNULL 2.108 AND ( 2.109 EXISTS ( 2.110 SELECT NULL FROM "draft" 2.111 - WHERE "draft"."initiative_id" = "initiative"."id" 2.112 + LEFT JOIN "ignored_member" ON 2.113 + "ignored_member"."member_id" = "supporter"."member_id" AND 2.114 + "ignored_member"."other_member_id" = "draft"."author_id" 2.115 + WHERE "draft"."initiative_id" = "supporter"."initiative_id" 2.116 AND "draft"."id" > "supporter"."draft_id" 2.117 + AND "ignored_member"."member_id" ISNULL 2.118 ) OR EXISTS ( 2.119 SELECT NULL FROM "suggestion" 2.120 LEFT JOIN "opinion" ON 2.121 "opinion"."member_id" = "supporter"."member_id" AND 2.122 "opinion"."suggestion_id" = "suggestion"."id" 2.123 - WHERE "suggestion"."initiative_id" = "initiative"."id" 2.124 + LEFT JOIN "ignored_member" ON 2.125 + "ignored_member"."member_id" = "supporter"."member_id" AND 2.126 + "ignored_member"."other_member_id" = "suggestion"."author_id" 2.127 + WHERE "suggestion"."initiative_id" = "supporter"."initiative_id" 2.128 AND "opinion"."member_id" ISNULL 2.129 - AND COALESCE( 2.130 - "suggestion"."id" > "sent"."last_suggestion_id", 2.131 - TRUE 2.132 - ) 2.133 + AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE) 2.134 + AND "ignored_member"."member_id" ISNULL 2.135 ) 2.136 ); 2.137 2.138 CREATE FUNCTION "featured_initiative" 2.139 - ( "member_id_p" "member"."id"%TYPE, 2.140 - "area_id_p" "area"."id"%TYPE ) 2.141 - RETURNS SETOF "initiative" 2.142 + ( "recipient_id_p" "member"."id"%TYPE, 2.143 + "area_id_p" "area"."id"%TYPE ) 2.144 + RETURNS SETOF "initiative"."id"%TYPE 2.145 LANGUAGE 'plpgsql' STABLE AS $$ 2.146 DECLARE 2.147 - "member_row" "member"%ROWTYPE; 2.148 + "counter_v" "member"."notification_counter"%TYPE; 2.149 + "sample_size_v" "member"."notification_sample_size"%TYPE; 2.150 + "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[] 2.151 + "match_v" BOOLEAN; 2.152 "member_id_v" "member"."id"%TYPE; 2.153 "seed_v" TEXT; 2.154 - "result_row" "initiative"%ROWTYPE; 2.155 - "match_v" BOOLEAN; 2.156 - "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[] 2.157 + "initiative_id_v" "initiative"."id"%TYPE; 2.158 BEGIN 2.159 - SELECT INTO "member_row" * FROM "member" WHERE "id" = "member_id_p"; 2.160 + SELECT "notification_counter", "notification_sample_size" 2.161 + INTO "counter_v", "sample_size_v" 2.162 + FROM "member" WHERE "id" = "recipient_id_p"; 2.163 "initiative_id_ary" := '{}'; 2.164 LOOP 2.165 "match_v" := FALSE; 2.166 @@ -127,38 +149,64 @@ 2.167 SELECT * FROM ( 2.168 SELECT DISTINCT 2.169 "supporter"."member_id", 2.170 - md5("member_id_p" || '-' || "member_row"."notification_counter" || '-' || "area_id_p" || '-' || "supporter"."member_id") AS "seed" 2.171 + md5( 2.172 + "recipient_id_p" || '-' || 2.173 + "counter_v" || '-' || 2.174 + "area_id_p" || '-' || 2.175 + "supporter"."member_id" 2.176 + ) AS "seed" 2.177 FROM "supporter" 2.178 - JOIN "member" ON "member"."id" = "supporter"."member_id" 2.179 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id" 2.180 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 2.181 - WHERE "supporter"."member_id" != "member_id_p" 2.182 + WHERE "supporter"."member_id" != "recipient_id_p" 2.183 AND "issue"."area_id" = "area_id_p" 2.184 AND "issue"."state" IN ('admission', 'discussion', 'verification') 2.185 ) AS "subquery" 2.186 ORDER BY "seed" 2.187 LOOP 2.188 - SELECT "initiative".* INTO "result_row" 2.189 + SELECT "initiative"."id" INTO "initiative_id_v" 2.190 FROM "initiative" 2.191 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 2.192 + JOIN "area" ON "area"."id" = "issue"."area_id" 2.193 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id" 2.194 LEFT JOIN "supporter" AS "self_support" ON 2.195 "self_support"."initiative_id" = "initiative"."id" AND 2.196 - "self_support"."member_id" = "member_id_p" 2.197 + "self_support"."member_id" = "recipient_id_p" 2.198 + LEFT JOIN "privilege" ON 2.199 + "privilege"."member_id" = "recipient_id_p" AND 2.200 + "privilege"."unit_id" = "area"."unit_id" AND 2.201 + "privilege"."voting_right" = TRUE 2.202 + LEFT JOIN "subscription" ON 2.203 + "subscription"."member_id" = "recipient_id_p" AND 2.204 + "subscription"."unit_id" = "area"."unit_id" 2.205 + LEFT JOIN "ignored_initiative" ON 2.206 + "ignored_initiative"."member_id" = "recipient_id_p" AND 2.207 + "ignored_initiative"."initiative_id" = "initiative"."id" 2.208 WHERE "supporter"."member_id" = "member_id_v" 2.209 AND "issue"."area_id" = "area_id_p" 2.210 AND "issue"."state" IN ('admission', 'discussion', 'verification') 2.211 AND "self_support"."member_id" ISNULL 2.212 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"] 2.213 + AND ( 2.214 + "privilege"."member_id" NOTNULL OR 2.215 + "subscription"."member_id" NOTNULL ) 2.216 + AND "ignored_initiative"."member_id" ISNULL 2.217 + AND NOT EXISTS ( 2.218 + SELECT NULL FROM "draft" 2.219 + JOIN "ignored_member" ON 2.220 + "ignored_member"."member_id" = "recipient_id_p" AND 2.221 + "ignored_member"."other_member_id" = "draft"."author_id" 2.222 + WHERE "draft"."initiative_id" = "initiative"."id" 2.223 + ) 2.224 ORDER BY md5("seed_v" || '-' || "initiative"."id") 2.225 LIMIT 1; 2.226 IF FOUND THEN 2.227 "match_v" := TRUE; 2.228 - "initiative_id_ary" := "initiative_id_ary" || "result_row"."id"; 2.229 - RETURN NEXT "result_row"; 2.230 - IF array_length("initiative_id_ary", 1) >= "member_row"."notification_sample_size" THEN 2.231 + RETURN NEXT "initiative_id_v"; 2.232 + IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN 2.233 RETURN; 2.234 END IF; 2.235 + "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v"; 2.236 END IF; 2.237 END LOOP; 2.238 EXIT WHEN NOT "match_v"; 2.239 @@ -168,115 +216,120 @@ 2.240 $$; 2.241 2.242 CREATE VIEW "updated_or_featured_initiative" AS 2.243 - SELECT * FROM "updated_initiative" 2.244 - UNION ALL 2.245 SELECT 2.246 - "member"."id" AS "seen_by_member_id", 2.247 - FALSE AS "supported", 2.248 - EXISTS ( 2.249 - SELECT NULL FROM "draft" 2.250 - WHERE "draft"."initiative_id" = "initiative"."id" 2.251 - AND COALESCE( 2.252 - "draft"."id" > "sent"."last_draft_id", 2.253 - TRUE 2.254 - ) 2.255 - ) AS "new_draft", 2.256 - ( SELECT count(1) FROM "suggestion" 2.257 - WHERE "suggestion"."initiative_id" = "initiative"."id" 2.258 - AND COALESCE( 2.259 - "suggestion"."id" > "sent"."last_suggestion_id", 2.260 - TRUE 2.261 - ) 2.262 - ) AS "new_suggestion_count", 2.263 - TRUE AS "featured", 2.264 + "subquery".*, 2.265 NOT EXISTS ( 2.266 SELECT NULL FROM "initiative" AS "better_initiative" 2.267 - WHERE 2.268 - "better_initiative"."issue_id" = "initiative"."issue_id" 2.269 + WHERE "better_initiative"."issue_id" = "initiative"."issue_id" 2.270 AND 2.271 ( COALESCE("better_initiative"."harmonic_weight", -1), 2.272 -"better_initiative"."id" ) > 2.273 ( COALESCE("initiative"."harmonic_weight", -1), 2.274 -"initiative"."id" ) 2.275 - ) AS "leading", 2.276 - "initiative".* 2.277 - FROM "member" CROSS JOIN "area" 2.278 - CROSS JOIN LATERAL 2.279 - "featured_initiative"("member"."id", "area"."id") AS "initiative" 2.280 - LEFT JOIN "initiative_notification_sent" AS "sent" 2.281 - ON "sent"."member_id" = "member"."id" 2.282 - AND "sent"."initiative_id" = "initiative"."id"; 2.283 + ) AS "leading" 2.284 + FROM ( 2.285 + SELECT * FROM "updated_initiative" 2.286 + UNION ALL 2.287 + SELECT 2.288 + "member"."id" AS "recipient_id", 2.289 + TRUE AS "featured", 2.290 + "featured_initiative_id" AS "initiative_id" 2.291 + FROM "member" CROSS JOIN "area" 2.292 + CROSS JOIN LATERAL 2.293 + "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id" 2.294 + JOIN "initiative" ON "initiative"."id" = "featured_initiative_id" 2.295 + ) AS "subquery" 2.296 + JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"; 2.297 2.298 CREATE VIEW "leading_complement_initiative" AS 2.299 SELECT * FROM ( 2.300 - SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id") 2.301 - "uf_initiative"."seen_by_member_id", 2.302 - "supporter"."member_id" NOTNULL AS "supported", 2.303 - CASE WHEN "supporter"."member_id" NOTNULL THEN FALSE ELSE 2.304 - EXISTS ( 2.305 - SELECT NULL FROM "draft" 2.306 - WHERE "draft"."initiative_id" = "initiative"."id" 2.307 - AND COALESCE( 2.308 - "draft"."id" > "sent"."last_draft_id", 2.309 - TRUE 2.310 - ) 2.311 - ) 2.312 - END AS "new_draft", 2.313 - CASE WHEN "supporter"."member_id" NOTNULL THEN 0 ELSE 2.314 - ( SELECT count(1) FROM "suggestion" 2.315 - WHERE "suggestion"."initiative_id" = "initiative"."id" 2.316 - AND COALESCE( 2.317 - "suggestion"."id" > "sent"."last_suggestion_id", 2.318 - TRUE 2.319 - ) 2.320 - ) 2.321 - END AS "new_suggestion_count", 2.322 + SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id") 2.323 + "uf_initiative"."recipient_id", 2.324 FALSE AS "featured", 2.325 - TRUE AS "leading", 2.326 - "initiative".* 2.327 + "uf_initiative"."initiative_id", 2.328 + TRUE AS "leading" 2.329 FROM "updated_or_featured_initiative" AS "uf_initiative" 2.330 + JOIN "initiative" AS "uf_initiative_full" ON 2.331 + "uf_initiative_full"."id" = "uf_initiative"."initiative_id" 2.332 JOIN "initiative" ON 2.333 - "uf_initiative"."issue_id" = "initiative"."issue_id" 2.334 - LEFT JOIN "supporter" ON 2.335 - "supporter"."member_id" = "uf_initiative"."seen_by_member_id" AND 2.336 - "supporter"."initiative_id" = "initiative"."id" 2.337 - LEFT JOIN "initiative_notification_sent" AS "sent" 2.338 - ON "sent"."member_id" = "uf_initiative"."seen_by_member_id" 2.339 - AND "sent"."initiative_id" = "initiative"."id" 2.340 + "initiative"."issue_id" = "uf_initiative_full"."issue_id" 2.341 ORDER BY 2.342 - "seen_by_member_id", 2.343 + "uf_initiative"."recipient_id", 2.344 "initiative"."issue_id", 2.345 "initiative"."harmonic_weight" DESC, 2.346 "initiative"."id" 2.347 ) AS "subquery" 2.348 WHERE NOT EXISTS ( 2.349 SELECT NULL FROM "updated_or_featured_initiative" AS "other" 2.350 - WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id" 2.351 - AND "other"."id" = "subquery"."id" 2.352 + WHERE "other"."recipient_id" = "subquery"."recipient_id" 2.353 + AND "other"."initiative_id" = "subquery"."initiative_id" 2.354 ); 2.355 2.356 CREATE VIEW "unfiltered_initiative_for_notification" AS 2.357 - SELECT * FROM "updated_or_featured_initiative" 2.358 - UNION ALL 2.359 - SELECT * FROM "leading_complement_initiative"; 2.360 + SELECT 2.361 + "subquery".*, 2.362 + "supporter"."member_id" NOTNULL AS "supported", 2.363 + CASE WHEN "supporter"."member_id" NOTNULL THEN 2.364 + EXISTS ( 2.365 + SELECT NULL FROM "draft" 2.366 + WHERE "draft"."initiative_id" = "subquery"."initiative_id" 2.367 + AND "draft"."id" > "supporter"."draft_id" 2.368 + ) 2.369 + ELSE 2.370 + EXISTS ( 2.371 + SELECT NULL FROM "draft" 2.372 + WHERE "draft"."initiative_id" = "subquery"."initiative_id" 2.373 + AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE) 2.374 + ) 2.375 + END AS "new_draft", 2.376 + CASE WHEN "supporter"."member_id" NOTNULL THEN 2.377 + ( SELECT count(1) FROM "suggestion" 2.378 + LEFT JOIN "opinion" ON 2.379 + "opinion"."member_id" = "supporter"."member_id" AND 2.380 + "opinion"."suggestion_id" = "suggestion"."id" 2.381 + WHERE "suggestion"."initiative_id" = "subquery"."initiative_id" 2.382 + AND "opinion"."member_id" ISNULL 2.383 + AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE) 2.384 + ) 2.385 + ELSE 2.386 + ( SELECT count(1) FROM "suggestion" 2.387 + WHERE "suggestion"."initiative_id" = "subquery"."initiative_id" 2.388 + AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE) 2.389 + ) 2.390 + END AS "new_suggestion_count" 2.391 + FROM ( 2.392 + SELECT * FROM "updated_or_featured_initiative" 2.393 + UNION ALL 2.394 + SELECT * FROM "leading_complement_initiative" 2.395 + ) AS "subquery" 2.396 + LEFT JOIN "supporter" ON 2.397 + "supporter"."member_id" = "subquery"."recipient_id" AND 2.398 + "supporter"."initiative_id" = "subquery"."initiative_id" 2.399 + LEFT JOIN "initiative_notification_sent" AS "sent" ON 2.400 + "sent"."member_id" = "subquery"."recipient_id" AND 2.401 + "sent"."initiative_id" = "subquery"."initiative_id"; 2.402 2.403 CREATE VIEW "initiative_for_notification" AS 2.404 - SELECT "initiative1".* 2.405 - FROM "unfiltered_initiative_for_notification" "initiative1" 2.406 - JOIN "issue" AS "issue1" ON "initiative1"."issue_id" = "issue1"."id" 2.407 + SELECT "unfiltered1".* 2.408 + FROM "unfiltered_initiative_for_notification" "unfiltered1" 2.409 + JOIN "initiative" AS "initiative1" ON 2.410 + "initiative1"."id" = "unfiltered1"."initiative_id" 2.411 + JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id" 2.412 WHERE EXISTS ( 2.413 SELECT NULL 2.414 - FROM "unfiltered_initiative_for_notification" "initiative2" 2.415 - JOIN "issue" AS "issue2" ON "initiative2"."issue_id" = "issue2"."id" 2.416 - WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id" 2.417 + FROM "unfiltered_initiative_for_notification" "unfiltered2" 2.418 + JOIN "initiative" AS "initiative2" ON 2.419 + "initiative2"."id" = "unfiltered2"."initiative_id" 2.420 + JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id" 2.421 + WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id" 2.422 AND "issue1"."area_id" = "issue2"."area_id" 2.423 - AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 ) 2.424 + AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 ) 2.425 ); 2.426 2.427 CREATE VIEW "newsletter_to_send" AS 2.428 SELECT 2.429 - "newsletter"."id" AS "newsletter_id", 2.430 - "member"."id" AS "member_id" 2.431 + "member"."id" AS "recipient_id", 2.432 + "newsletter"."id" AS "newsletter_id" 2.433 FROM "newsletter" CROSS JOIN "member" 2.434 LEFT JOIN "privilege" ON 2.435 "privilege"."member_id" = "member"."id" AND 2.436 @@ -309,19 +362,19 @@ 2.437 PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE; 2.438 FOR "result_row" IN 2.439 SELECT * FROM "initiative_for_notification" 2.440 - WHERE "seen_by_member_id" = "member_id_p" 2.441 + WHERE "member_id" = "member_id_p" 2.442 LOOP 2.443 SELECT "id" INTO "last_draft_id_v" FROM "draft" 2.444 - WHERE "draft"."initiative_id" = "result_row"."id" 2.445 + WHERE "draft"."initiative_id" = "result_row"."initiative_id" 2.446 ORDER BY "id" DESC LIMIT 1; 2.447 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion" 2.448 - WHERE "suggestion"."initiative_id" = "result_row"."id" 2.449 + WHERE "suggestion"."initiative_id" = "result_row"."initiative_id" 2.450 ORDER BY "id" DESC LIMIT 1; 2.451 INSERT INTO "initiative_notification_sent" 2.452 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 2.453 VALUES ( 2.454 "member_id_p", 2.455 - "result_row"."id", 2.456 + "result_row"."initiative_id", 2.457 "last_draft_id_v", 2.458 "last_suggestion_id_v" ) 2.459 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET