liquid_feedback_core

annotate update/core-update.v3.2.1-v3.2.2.sql @ 524:bc6d9dc60ca4

find changesets by author, revision, files, or words in the commit message
Require members to be "activated" in order to receive messages/notifications; New views "member_eligible_to_be_notified" and "member_to_notify"
author jbe
date Thu May 05 21:13:09 2016 +0200 (2016-05-05)
parents 30b67ca0c242
children
rev   line source
jbe@520 1 BEGIN;
jbe@520 2
jbe@520 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@520 4 SELECT * FROM (VALUES ('3.2.2', 3, 2, 2))
jbe@520 5 AS "subquery"("string", "major", "minor", "revision");
jbe@520 6
jbe@523 7 UPDATE "member" SET
jbe@523 8 "disable_notifications" = TRUE,
jbe@523 9 "notification_counter" = DEFAULT,
jbe@523 10 "notification_sample_size" = 0,
jbe@523 11 "notification_dow" = NULL,
jbe@523 12 "notification_hour" = NULL
jbe@523 13 WHERE "last_login" ISNULL
jbe@523 14 AND "login" ISNULL
jbe@523 15 AND "authority_login" ISNULL
jbe@523 16 AND "locked" = TRUE
jbe@523 17 AND "active" = FALSE;
jbe@523 18
jbe@524 19 CREATE VIEW "member_eligible_to_be_notified" AS
jbe@524 20 SELECT * FROM "member"
jbe@524 21 WHERE "activated" NOTNULL AND "locked" = FALSE;
jbe@524 22
jbe@524 23 COMMENT ON VIEW "member_eligible_to_be_notified" IS 'Filtered "member" table containing only activated and non-locked members (used as helper view for "member_to_notify" and "newsletter_to_send")';
jbe@524 24
jbe@524 25 CREATE VIEW "member_to_notify" AS
jbe@524 26 SELECT * FROM "member_eligible_to_be_notified"
jbe@524 27 WHERE "disable_notifications" = FALSE;
jbe@524 28
jbe@524 29 COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)';
jbe@524 30
jbe@520 31 CREATE OR REPLACE FUNCTION "featured_initiative"
jbe@520 32 ( "recipient_id_p" "member"."id"%TYPE,
jbe@520 33 "area_id_p" "area"."id"%TYPE )
jbe@520 34 RETURNS SETOF "initiative"."id"%TYPE
jbe@520 35 LANGUAGE 'plpgsql' STABLE AS $$
jbe@520 36 DECLARE
jbe@520 37 "counter_v" "member"."notification_counter"%TYPE;
jbe@520 38 "sample_size_v" "member"."notification_sample_size"%TYPE;
jbe@520 39 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
jbe@520 40 "match_v" BOOLEAN;
jbe@520 41 "member_id_v" "member"."id"%TYPE;
jbe@520 42 "seed_v" TEXT;
jbe@520 43 "initiative_id_v" "initiative"."id"%TYPE;
jbe@520 44 BEGIN
jbe@520 45 SELECT "notification_counter", "notification_sample_size"
jbe@520 46 INTO "counter_v", "sample_size_v"
jbe@520 47 FROM "member" WHERE "id" = "recipient_id_p";
jbe@520 48 IF COALESCE("sample_size_v" <= 0, TRUE) THEN
jbe@520 49 RETURN;
jbe@520 50 END IF;
jbe@520 51 "initiative_id_ary" := '{}';
jbe@520 52 LOOP
jbe@520 53 "match_v" := FALSE;
jbe@520 54 FOR "member_id_v", "seed_v" IN
jbe@520 55 SELECT * FROM (
jbe@520 56 SELECT DISTINCT
jbe@520 57 "supporter"."member_id",
jbe@520 58 md5(
jbe@520 59 "recipient_id_p" || '-' ||
jbe@520 60 "counter_v" || '-' ||
jbe@520 61 "area_id_p" || '-' ||
jbe@520 62 "supporter"."member_id"
jbe@520 63 ) AS "seed"
jbe@520 64 FROM "supporter"
jbe@520 65 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
jbe@520 66 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@520 67 WHERE "supporter"."member_id" != "recipient_id_p"
jbe@520 68 AND "issue"."area_id" = "area_id_p"
jbe@520 69 AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@520 70 ) AS "subquery"
jbe@520 71 ORDER BY "seed"
jbe@520 72 LOOP
jbe@520 73 SELECT "initiative"."id" INTO "initiative_id_v"
jbe@520 74 FROM "initiative"
jbe@520 75 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@520 76 JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@520 77 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
jbe@520 78 LEFT JOIN "supporter" AS "self_support" ON
jbe@520 79 "self_support"."initiative_id" = "initiative"."id" AND
jbe@520 80 "self_support"."member_id" = "recipient_id_p"
jbe@520 81 LEFT JOIN "privilege" ON
jbe@520 82 "privilege"."member_id" = "recipient_id_p" AND
jbe@520 83 "privilege"."unit_id" = "area"."unit_id" AND
jbe@520 84 "privilege"."voting_right" = TRUE
jbe@520 85 LEFT JOIN "subscription" ON
jbe@520 86 "subscription"."member_id" = "recipient_id_p" AND
jbe@520 87 "subscription"."unit_id" = "area"."unit_id"
jbe@520 88 LEFT JOIN "ignored_initiative" ON
jbe@520 89 "ignored_initiative"."member_id" = "recipient_id_p" AND
jbe@520 90 "ignored_initiative"."initiative_id" = "initiative"."id"
jbe@520 91 WHERE "supporter"."member_id" = "member_id_v"
jbe@520 92 AND "issue"."area_id" = "area_id_p"
jbe@520 93 AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@520 94 AND "initiative"."revoked" ISNULL
jbe@520 95 AND "self_support"."member_id" ISNULL
jbe@520 96 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
jbe@520 97 AND (
jbe@520 98 "privilege"."member_id" NOTNULL OR
jbe@520 99 "subscription"."member_id" NOTNULL )
jbe@520 100 AND "ignored_initiative"."member_id" ISNULL
jbe@520 101 AND NOT EXISTS (
jbe@520 102 SELECT NULL FROM "draft"
jbe@520 103 JOIN "ignored_member" ON
jbe@520 104 "ignored_member"."member_id" = "recipient_id_p" AND
jbe@520 105 "ignored_member"."other_member_id" = "draft"."author_id"
jbe@520 106 WHERE "draft"."initiative_id" = "initiative"."id"
jbe@520 107 )
jbe@520 108 ORDER BY md5("seed_v" || '-' || "initiative"."id")
jbe@520 109 LIMIT 1;
jbe@520 110 IF FOUND THEN
jbe@520 111 "match_v" := TRUE;
jbe@520 112 RETURN NEXT "initiative_id_v";
jbe@520 113 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
jbe@520 114 RETURN;
jbe@520 115 END IF;
jbe@520 116 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
jbe@520 117 END IF;
jbe@520 118 END LOOP;
jbe@520 119 EXIT WHEN NOT "match_v";
jbe@520 120 END LOOP;
jbe@520 121 RETURN;
jbe@520 122 END;
jbe@520 123 $$;
jbe@520 124
jbe@521 125 CREATE OR REPLACE VIEW "scheduled_notification_to_send" AS
jbe@521 126 SELECT * FROM (
jbe@521 127 SELECT
jbe@521 128 "id" AS "recipient_id",
jbe@521 129 now() - CASE WHEN "notification_dow" ISNULL THEN
jbe@521 130 ( "notification_sent"::DATE + CASE
jbe@521 131 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@521 132 THEN 0 ELSE 1 END
jbe@521 133 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@521 134 ELSE
jbe@521 135 ( "notification_sent"::DATE +
jbe@521 136 ( 7 + "notification_dow" -
jbe@521 137 EXTRACT(DOW FROM
jbe@521 138 ( "notification_sent"::DATE + CASE
jbe@521 139 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@521 140 THEN 0 ELSE 1 END
jbe@521 141 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@521 142 )::INTEGER
jbe@521 143 ) % 7 +
jbe@521 144 CASE
jbe@521 145 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@521 146 THEN 0 ELSE 1
jbe@521 147 END
jbe@521 148 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@521 149 END AS "pending"
jbe@521 150 FROM (
jbe@521 151 SELECT
jbe@521 152 "id",
jbe@521 153 COALESCE("notification_sent", "activated") AS "notification_sent",
jbe@521 154 "notification_dow",
jbe@521 155 "notification_hour"
jbe@524 156 FROM "member_to_notify"
jbe@524 157 WHERE "notification_hour" NOTNULL
jbe@521 158 ) AS "subquery1"
jbe@521 159 ) AS "subquery2"
jbe@521 160 WHERE "pending" > '0'::INTERVAL;
jbe@521 161
jbe@524 162 CREATE OR REPLACE VIEW "newsletter_to_send" AS
jbe@524 163 SELECT
jbe@524 164 "member"."id" AS "recipient_id",
jbe@524 165 "newsletter"."id" AS "newsletter_id",
jbe@524 166 "newsletter"."published"
jbe@524 167 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
jbe@524 168 LEFT JOIN "privilege" ON
jbe@524 169 "privilege"."member_id" = "member"."id" AND
jbe@524 170 "privilege"."unit_id" = "newsletter"."unit_id" AND
jbe@524 171 "privilege"."voting_right" = TRUE
jbe@524 172 LEFT JOIN "subscription" ON
jbe@524 173 "subscription"."member_id" = "member"."id" AND
jbe@524 174 "subscription"."unit_id" = "newsletter"."unit_id"
jbe@524 175 WHERE "newsletter"."published" <= now()
jbe@524 176 AND "newsletter"."sent" ISNULL
jbe@524 177 AND (
jbe@524 178 "member"."disable_notifications" = FALSE OR
jbe@524 179 "newsletter"."include_all_members" = TRUE )
jbe@524 180 AND (
jbe@524 181 "newsletter"."unit_id" ISNULL OR
jbe@524 182 "privilege"."member_id" NOTNULL OR
jbe@524 183 "subscription"."member_id" NOTNULL );
jbe@524 184
jbe@522 185 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@522 186 RETURNS VOID
jbe@522 187 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@522 188 BEGIN
jbe@522 189 UPDATE "member" SET
jbe@522 190 "last_login" = NULL,
jbe@522 191 "last_delegation_check" = NULL,
jbe@522 192 "login" = NULL,
jbe@522 193 "password" = NULL,
jbe@522 194 "authority" = NULL,
jbe@522 195 "authority_uid" = NULL,
jbe@522 196 "authority_login" = NULL,
jbe@522 197 "locked" = TRUE,
jbe@522 198 "active" = FALSE,
jbe@522 199 "notify_email" = NULL,
jbe@522 200 "notify_email_unconfirmed" = NULL,
jbe@522 201 "notify_email_secret" = NULL,
jbe@522 202 "notify_email_secret_expiry" = NULL,
jbe@522 203 "notify_email_lock_expiry" = NULL,
jbe@522 204 "disable_notifications" = TRUE,
jbe@522 205 "notification_counter" = DEFAULT,
jbe@522 206 "notification_sample_size" = 0,
jbe@522 207 "notification_dow" = NULL,
jbe@522 208 "notification_hour" = NULL,
jbe@522 209 "login_recovery_expiry" = NULL,
jbe@522 210 "password_reset_secret" = NULL,
jbe@522 211 "password_reset_secret_expiry" = NULL,
jbe@522 212 "organizational_unit" = NULL,
jbe@522 213 "internal_posts" = NULL,
jbe@522 214 "realname" = NULL,
jbe@522 215 "birthday" = NULL,
jbe@522 216 "address" = NULL,
jbe@522 217 "email" = NULL,
jbe@522 218 "xmpp_address" = NULL,
jbe@522 219 "website" = NULL,
jbe@522 220 "phone" = NULL,
jbe@522 221 "mobile_phone" = NULL,
jbe@522 222 "profession" = NULL,
jbe@522 223 "external_memberships" = NULL,
jbe@522 224 "external_posts" = NULL,
jbe@522 225 "statement" = NULL
jbe@522 226 WHERE "id" = "member_id_p";
jbe@522 227 -- "text_search_data" is updated by triggers
jbe@522 228 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@522 229 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@522 230 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@522 231 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@522 232 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@522 233 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
jbe@522 234 DELETE FROM "session" WHERE "member_id" = "member_id_p";
jbe@522 235 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@522 236 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@522 237 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@522 238 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@522 239 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@522 240 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@522 241 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@522 242 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
jbe@522 243 DELETE FROM "direct_voter" USING "issue"
jbe@522 244 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@522 245 AND "issue"."closed" ISNULL
jbe@522 246 AND "member_id" = "member_id_p";
jbe@522 247 RETURN;
jbe@522 248 END;
jbe@522 249 $$;
jbe@522 250
jbe@522 251 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@522 252 RETURNS VOID
jbe@522 253 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@522 254 BEGIN
jbe@522 255 DELETE FROM "temporary_transaction_data";
jbe@522 256 DELETE FROM "member" WHERE "activated" ISNULL;
jbe@522 257 UPDATE "member" SET
jbe@522 258 "invite_code" = NULL,
jbe@522 259 "invite_code_expiry" = NULL,
jbe@522 260 "admin_comment" = NULL,
jbe@522 261 "last_login" = NULL,
jbe@522 262 "last_delegation_check" = NULL,
jbe@522 263 "login" = NULL,
jbe@522 264 "password" = NULL,
jbe@522 265 "authority" = NULL,
jbe@522 266 "authority_uid" = NULL,
jbe@522 267 "authority_login" = NULL,
jbe@522 268 "lang" = NULL,
jbe@522 269 "notify_email" = NULL,
jbe@522 270 "notify_email_unconfirmed" = NULL,
jbe@522 271 "notify_email_secret" = NULL,
jbe@522 272 "notify_email_secret_expiry" = NULL,
jbe@522 273 "notify_email_lock_expiry" = NULL,
jbe@522 274 "disable_notifications" = TRUE,
jbe@522 275 "notification_counter" = DEFAULT,
jbe@522 276 "notification_sample_size" = 0,
jbe@522 277 "notification_dow" = NULL,
jbe@522 278 "notification_hour" = NULL,
jbe@522 279 "login_recovery_expiry" = NULL,
jbe@522 280 "password_reset_secret" = NULL,
jbe@522 281 "password_reset_secret_expiry" = NULL,
jbe@522 282 "organizational_unit" = NULL,
jbe@522 283 "internal_posts" = NULL,
jbe@522 284 "realname" = NULL,
jbe@522 285 "birthday" = NULL,
jbe@522 286 "address" = NULL,
jbe@522 287 "email" = NULL,
jbe@522 288 "xmpp_address" = NULL,
jbe@522 289 "website" = NULL,
jbe@522 290 "phone" = NULL,
jbe@522 291 "mobile_phone" = NULL,
jbe@522 292 "profession" = NULL,
jbe@522 293 "external_memberships" = NULL,
jbe@522 294 "external_posts" = NULL,
jbe@522 295 "formatting_engine" = NULL,
jbe@522 296 "statement" = NULL;
jbe@522 297 -- "text_search_data" is updated by triggers
jbe@522 298 DELETE FROM "setting";
jbe@522 299 DELETE FROM "setting_map";
jbe@522 300 DELETE FROM "member_relation_setting";
jbe@522 301 DELETE FROM "member_image";
jbe@522 302 DELETE FROM "contact";
jbe@522 303 DELETE FROM "ignored_member";
jbe@522 304 DELETE FROM "session";
jbe@522 305 DELETE FROM "area_setting";
jbe@522 306 DELETE FROM "issue_setting";
jbe@522 307 DELETE FROM "ignored_initiative";
jbe@522 308 DELETE FROM "initiative_setting";
jbe@522 309 DELETE FROM "suggestion_setting";
jbe@522 310 DELETE FROM "non_voter";
jbe@522 311 DELETE FROM "direct_voter" USING "issue"
jbe@522 312 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@522 313 AND "issue"."closed" ISNULL;
jbe@522 314 RETURN;
jbe@522 315 END;
jbe@522 316 $$;
jbe@522 317
jbe@520 318 COMMIT;

Impressum / About Us