liquid_feedback_core
changeset 517:e4f619e87664 v3.2.1
Fixed accidental PostgreSQL 9.5 dependency (because of UPSERT aka ON CONFLICT clause)
author | jbe |
---|---|
date | Sat Apr 30 17:03:48 2016 +0200 (2016-04-30) |
parents | ba7d5c7ede4f |
children | b1a7ebf67470 |
files | core.sql update/core-update.v3.1.0-v3.2.1.sql update/core-update.v3.2.0-v3.2.1.sql |
line diff
1.1 --- a/core.sql Sun Apr 24 21:30:22 2016 +0200 1.2 +++ b/core.sql Sat Apr 30 17:03:48 2016 +0200 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.2.0', 3, 2, 0)) 1.8 + SELECT * FROM (VALUES ('3.2.1', 3, 2, 1)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -3257,6 +3257,18 @@ 1.13 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion" 1.14 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id" 1.15 ORDER BY "id" DESC LIMIT 1; 1.16 + /* compatibility with PostgreSQL 9.1 */ 1.17 + DELETE FROM "notification_initiative_sent" 1.18 + WHERE "member_id" = "recipient_id_p" 1.19 + AND "initiative_id" = "result_row"."initiative_id"; 1.20 + INSERT INTO "notification_initiative_sent" 1.21 + ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 1.22 + VALUES ( 1.23 + "recipient_id_p", 1.24 + "result_row"."initiative_id", 1.25 + "last_draft_id_v", 1.26 + "last_suggestion_id_v" ); 1.27 + /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher 1.28 INSERT INTO "notification_initiative_sent" 1.29 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 1.30 VALUES ( 1.31 @@ -3265,16 +3277,9 @@ 1.32 "last_draft_id_v", 1.33 "last_suggestion_id_v" ) 1.34 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET 1.35 - "last_draft_id" = CASE 1.36 - WHEN "notification_initiative_sent"."last_draft_id" > "last_draft_id_v" 1.37 - THEN "notification_initiative_sent"."last_draft_id" 1.38 - ELSE "last_draft_id_v" 1.39 - END, 1.40 - "last_suggestion_id" = CASE 1.41 - WHEN "notification_initiative_sent"."last_suggestion_id" > "last_suggestion_id_v" 1.42 - THEN "notification_initiative_sent"."last_suggestion_id" 1.43 - ELSE "last_suggestion_id_v" 1.44 - END; 1.45 + "last_draft_id" = "last_draft_id_v", 1.46 + "last_suggestion_id" = "last_suggestion_id_v"; 1.47 + */ 1.48 RETURN NEXT "result_row"; 1.49 END LOOP; 1.50 DELETE FROM "notification_initiative_sent"
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v3.1.0-v3.2.1.sql Sat Apr 30 17:03:48 2016 +0200 2.3 @@ -0,0 +1,795 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('3.2.1', 3, 2, 1)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 +ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE; 2.11 +ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1; 2.12 +ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3; 2.13 +ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6); 2.14 +ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 DEFAULT floor(random() * 24) CHECK ("notification_hour" BETWEEN 0 AND 23); 2.15 +ALTER TABLE "member" ADD COLUMN "notification_sent" TIMESTAMP; 2.16 +ALTER TABLE "member" ADD 2.17 + CONSTRAINT "notification_dow_requires_notification_hour" 2.18 + CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL); 2.19 + 2.20 +UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none'::"notify_level"; 2.21 + 2.22 +DROP VIEW "selected_event_seen_by_member"; 2.23 +DROP VIEW "event_seen_by_member"; 2.24 + 2.25 +ALTER TABLE "member" DROP COLUMN "notify_level"; 2.26 + 2.27 +DROP TYPE "notify_level"; 2.28 + 2.29 +COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications'; 2.30 +COMMENT ON COLUMN "member"."notification_counter" IS 'Sequential number of next scheduled notification message (used as a seed for pseudo-random initiative selection algorithm)'; 2.31 +COMMENT ON COLUMN "member"."notification_sample_size" IS 'Number of featured initiatives per issue in scheduled notification messages'; 2.32 +COMMENT ON COLUMN "member"."notification_dow" IS 'Day of week for scheduled notifications (NULL to receive a daily digest)'; 2.33 +COMMENT ON COLUMN "member"."notification_hour" IS 'Time of day when scheduled notifications are sent out'; 2.34 +COMMENT ON COLUMN "member"."notification_sent" IS 'Timestamp of last scheduled notification mail that has been sent out'; 2.35 + 2.36 +ALTER TABLE "rendered_member_statement" ALTER COLUMN "member_id" SET DATA TYPE INT4; 2.37 + 2.38 +DROP VIEW "expired_session"; 2.39 + 2.40 +ALTER TABLE "session" ALTER COLUMN "member_id" SET DATA TYPE INT4; 2.41 + 2.42 +CREATE VIEW "expired_session" AS 2.43 + SELECT * FROM "session" WHERE now() > "expiry"; 2.44 +CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD 2.45 + DELETE FROM "session" WHERE "ident" = OLD."ident"; 2.46 + 2.47 +COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible'; 2.48 +COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"'; 2.49 + 2.50 +CREATE TABLE "subscription" ( 2.51 + PRIMARY KEY ("member_id", "unit_id"), 2.52 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.53 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 2.54 +CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id"); 2.55 + 2.56 +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'; 2.57 + 2.58 +CREATE TABLE "ignored_area" ( 2.59 + PRIMARY KEY ("member_id", "area_id"), 2.60 + "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.61 + "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); 2.62 +CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id"); 2.63 + 2.64 +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'; 2.65 + 2.66 +ALTER TABLE "ignored_initiative" DROP CONSTRAINT "ignored_initiative_pkey"; 2.67 +DROP INDEX "ignored_initiative_member_id_idx"; 2.68 + 2.69 +ALTER TABLE "ignored_initiative" ADD PRIMARY KEY ("member_id", "initiative_id"); 2.70 +CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id"); 2.71 + 2.72 +COMMENT ON TABLE "ignored_initiative" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given initiative'; 2.73 + 2.74 +ALTER TABLE "notification_sent" RENAME TO "notification_event_sent"; 2.75 +ALTER INDEX "notification_sent_singleton_idx" RENAME TO "notification_event_sent_singleton_idx"; 2.76 + 2.77 +CREATE TABLE "notification_initiative_sent" ( 2.78 + PRIMARY KEY ("member_id", "initiative_id"), 2.79 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.80 + "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.81 + "last_draft_id" INT8 NOT NULL, 2.82 + "last_suggestion_id" INT8 ); 2.83 +CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id"); 2.84 + 2.85 +COMMENT ON TABLE "notification_initiative_sent" IS 'Information which initiatives have been promoted to a member in a scheduled notification mail'; 2.86 + 2.87 +COMMENT ON COLUMN "notification_initiative_sent"."last_draft_id" IS 'Current (i.e. last) draft_id when initiative had been promoted'; 2.88 +COMMENT ON COLUMN "notification_initiative_sent"."last_suggestion_id" IS 'Current (i.e. last) draft_id when initiative had been promoted'; 2.89 + 2.90 +CREATE TABLE "newsletter" ( 2.91 + "id" SERIAL4 PRIMARY KEY, 2.92 + "published" TIMESTAMPTZ NOT NULL, 2.93 + "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 2.94 + "include_all_members" BOOLEAN NOT NULL, 2.95 + "sent" TIMESTAMPTZ, 2.96 + "subject" TEXT NOT NULL, 2.97 + "content" TEXT NOT NULL ); 2.98 +CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published"); 2.99 +CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL; 2.100 +CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published"); 2.101 + 2.102 +COMMENT ON TABLE "newsletter" IS 'Contains newsletters created by administrators to be sent out and for further reference'; 2.103 + 2.104 +COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter is to be sent out (and made available in the frontend)'; 2.105 +COMMENT ON COLUMN "newsletter"."unit_id" IS 'If set, only members with voting right in the given unit are considered to be recipients'; 2.106 +COMMENT ON COLUMN "newsletter"."include_all_members" IS 'TRUE = include all members regardless of their ''disable_notifications'' setting'; 2.107 +COMMENT ON COLUMN "newsletter"."sent" IS 'Timestamp when the newsletter has been mailed out'; 2.108 +COMMENT ON COLUMN "newsletter"."subject" IS 'Subject line (e.g. to be used for the email)'; 2.109 +COMMENT ON COLUMN "newsletter"."content" IS 'Plain text content of the newsletter'; 2.110 + 2.111 +CREATE OR REPLACE FUNCTION "issue_requires_first_initiative_trigger"() 2.112 + RETURNS TRIGGER 2.113 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.114 + BEGIN 2.115 + IF NOT EXISTS ( 2.116 + SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id" 2.117 + ) THEN 2.118 + RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING 2.119 + ERRCODE = 'integrity_constraint_violation', 2.120 + HINT = 'Create issue, initiative, and draft within the same transaction.'; 2.121 + END IF; 2.122 + RETURN NULL; 2.123 + END; 2.124 + $$; 2.125 + 2.126 +CREATE OR REPLACE FUNCTION "initiative_requires_first_draft_trigger"() 2.127 + RETURNS TRIGGER 2.128 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.129 + BEGIN 2.130 + IF NOT EXISTS ( 2.131 + SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id" 2.132 + ) THEN 2.133 + RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING 2.134 + ERRCODE = 'integrity_constraint_violation', 2.135 + HINT = 'Create issue, initiative and draft within the same transaction.'; 2.136 + END IF; 2.137 + RETURN NULL; 2.138 + END; 2.139 + $$; 2.140 + 2.141 +CREATE OR REPLACE FUNCTION "suggestion_requires_first_opinion_trigger"() 2.142 + RETURNS TRIGGER 2.143 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.144 + BEGIN 2.145 + IF NOT EXISTS ( 2.146 + SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id" 2.147 + ) THEN 2.148 + RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING 2.149 + ERRCODE = 'integrity_constraint_violation', 2.150 + HINT = 'Create suggestion and opinion within the same transaction.'; 2.151 + END IF; 2.152 + RETURN NULL; 2.153 + END; 2.154 + $$; 2.155 + 2.156 +CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"() 2.157 + RETURNS TRIGGER 2.158 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.159 + DECLARE 2.160 + "issue_id_v" "issue"."id"%TYPE; 2.161 + "issue_row" "issue"%ROWTYPE; 2.162 + BEGIN 2.163 + IF EXISTS ( 2.164 + SELECT NULL FROM "temporary_transaction_data" 2.165 + WHERE "txid" = txid_current() 2.166 + AND "key" = 'override_protection_triggers' 2.167 + AND "value" = TRUE::TEXT 2.168 + ) THEN 2.169 + RETURN NULL; 2.170 + END IF; 2.171 + IF TG_OP = 'DELETE' THEN 2.172 + "issue_id_v" := OLD."issue_id"; 2.173 + ELSE 2.174 + "issue_id_v" := NEW."issue_id"; 2.175 + END IF; 2.176 + SELECT INTO "issue_row" * FROM "issue" 2.177 + WHERE "id" = "issue_id_v" FOR SHARE; 2.178 + IF ( 2.179 + "issue_row"."closed" NOTNULL OR ( 2.180 + "issue_row"."state" = 'voting' AND 2.181 + "issue_row"."phase_finished" NOTNULL 2.182 + ) 2.183 + ) THEN 2.184 + IF 2.185 + TG_RELID = 'direct_voter'::regclass AND 2.186 + TG_OP = 'UPDATE' 2.187 + THEN 2.188 + IF 2.189 + OLD."issue_id" = NEW."issue_id" AND 2.190 + OLD."member_id" = NEW."member_id" AND 2.191 + OLD."weight" = NEW."weight" 2.192 + THEN 2.193 + RETURN NULL; -- allows changing of voter comment 2.194 + END IF; 2.195 + END IF; 2.196 + RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING 2.197 + ERRCODE = 'integrity_constraint_violation'; 2.198 + END IF; 2.199 + RETURN NULL; 2.200 + END; 2.201 + $$; 2.202 + 2.203 +CREATE VIEW "event_for_notification" AS 2.204 + SELECT 2.205 + "member"."id" AS "recipient_id", 2.206 + "event".* 2.207 + FROM "member" CROSS JOIN "event" 2.208 + JOIN "issue" ON "issue"."id" = "event"."issue_id" 2.209 + JOIN "area" ON "area"."id" = "issue"."area_id" 2.210 + LEFT JOIN "privilege" ON 2.211 + "privilege"."member_id" = "member"."id" AND 2.212 + "privilege"."unit_id" = "area"."unit_id" AND 2.213 + "privilege"."voting_right" = TRUE 2.214 + LEFT JOIN "subscription" ON 2.215 + "subscription"."member_id" = "member"."id" AND 2.216 + "subscription"."unit_id" = "area"."unit_id" 2.217 + LEFT JOIN "ignored_area" ON 2.218 + "ignored_area"."member_id" = "member"."id" AND 2.219 + "ignored_area"."area_id" = "issue"."area_id" 2.220 + LEFT JOIN "interest" ON 2.221 + "interest"."member_id" = "member"."id" AND 2.222 + "interest"."issue_id" = "event"."issue_id" 2.223 + LEFT JOIN "supporter" ON 2.224 + "supporter"."member_id" = "member"."id" AND 2.225 + "supporter"."initiative_id" = "event"."initiative_id" 2.226 + WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL) 2.227 + AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL) 2.228 + AND ( 2.229 + "event"."event" = 'issue_state_changed'::"event_type" OR 2.230 + ( "event"."event" = 'initiative_revoked'::"event_type" AND 2.231 + "supporter"."member_id" NOTNULL ) ); 2.232 + 2.233 +COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient'; 2.234 + 2.235 +COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail'; 2.236 + 2.237 +CREATE VIEW "updated_initiative" AS 2.238 + SELECT 2.239 + "supporter"."member_id" AS "recipient_id", 2.240 + FALSE AS "featured", 2.241 + "supporter"."initiative_id" 2.242 + FROM "supporter" 2.243 + JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id" 2.244 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 2.245 + LEFT JOIN "notification_initiative_sent" AS "sent" ON 2.246 + "sent"."member_id" = "supporter"."member_id" AND 2.247 + "sent"."initiative_id" = "supporter"."initiative_id" 2.248 + LEFT JOIN "ignored_initiative" ON 2.249 + "ignored_initiative"."member_id" = "supporter"."member_id" AND 2.250 + "ignored_initiative"."initiative_id" = "supporter"."initiative_id" 2.251 + WHERE "issue"."state" IN ('admission', 'discussion') 2.252 + AND "initiative"."revoked" ISNULL 2.253 + AND "ignored_initiative"."member_id" ISNULL 2.254 + AND ( 2.255 + EXISTS ( 2.256 + SELECT NULL FROM "draft" 2.257 + LEFT JOIN "ignored_member" ON 2.258 + "ignored_member"."member_id" = "supporter"."member_id" AND 2.259 + "ignored_member"."other_member_id" = "draft"."author_id" 2.260 + WHERE "draft"."initiative_id" = "supporter"."initiative_id" 2.261 + AND "draft"."id" > "supporter"."draft_id" 2.262 + AND "ignored_member"."member_id" ISNULL 2.263 + ) OR EXISTS ( 2.264 + SELECT NULL FROM "suggestion" 2.265 + LEFT JOIN "opinion" ON 2.266 + "opinion"."member_id" = "supporter"."member_id" AND 2.267 + "opinion"."suggestion_id" = "suggestion"."id" 2.268 + LEFT JOIN "ignored_member" ON 2.269 + "ignored_member"."member_id" = "supporter"."member_id" AND 2.270 + "ignored_member"."other_member_id" = "suggestion"."author_id" 2.271 + WHERE "suggestion"."initiative_id" = "supporter"."initiative_id" 2.272 + AND "opinion"."member_id" ISNULL 2.273 + AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE) 2.274 + AND "ignored_member"."member_id" ISNULL 2.275 + ) 2.276 + ); 2.277 + 2.278 +COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"'; 2.279 + 2.280 +CREATE FUNCTION "featured_initiative" 2.281 + ( "recipient_id_p" "member"."id"%TYPE, 2.282 + "area_id_p" "area"."id"%TYPE ) 2.283 + RETURNS SETOF "initiative"."id"%TYPE 2.284 + LANGUAGE 'plpgsql' STABLE AS $$ 2.285 + DECLARE 2.286 + "counter_v" "member"."notification_counter"%TYPE; 2.287 + "sample_size_v" "member"."notification_sample_size"%TYPE; 2.288 + "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[] 2.289 + "match_v" BOOLEAN; 2.290 + "member_id_v" "member"."id"%TYPE; 2.291 + "seed_v" TEXT; 2.292 + "initiative_id_v" "initiative"."id"%TYPE; 2.293 + BEGIN 2.294 + SELECT "notification_counter", "notification_sample_size" 2.295 + INTO "counter_v", "sample_size_v" 2.296 + FROM "member" WHERE "id" = "recipient_id_p"; 2.297 + "initiative_id_ary" := '{}'; 2.298 + LOOP 2.299 + "match_v" := FALSE; 2.300 + FOR "member_id_v", "seed_v" IN 2.301 + SELECT * FROM ( 2.302 + SELECT DISTINCT 2.303 + "supporter"."member_id", 2.304 + md5( 2.305 + "recipient_id_p" || '-' || 2.306 + "counter_v" || '-' || 2.307 + "area_id_p" || '-' || 2.308 + "supporter"."member_id" 2.309 + ) AS "seed" 2.310 + FROM "supporter" 2.311 + JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id" 2.312 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 2.313 + WHERE "supporter"."member_id" != "recipient_id_p" 2.314 + AND "issue"."area_id" = "area_id_p" 2.315 + AND "issue"."state" IN ('admission', 'discussion', 'verification') 2.316 + ) AS "subquery" 2.317 + ORDER BY "seed" 2.318 + LOOP 2.319 + SELECT "initiative"."id" INTO "initiative_id_v" 2.320 + FROM "initiative" 2.321 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 2.322 + JOIN "area" ON "area"."id" = "issue"."area_id" 2.323 + JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id" 2.324 + LEFT JOIN "supporter" AS "self_support" ON 2.325 + "self_support"."initiative_id" = "initiative"."id" AND 2.326 + "self_support"."member_id" = "recipient_id_p" 2.327 + LEFT JOIN "privilege" ON 2.328 + "privilege"."member_id" = "recipient_id_p" AND 2.329 + "privilege"."unit_id" = "area"."unit_id" AND 2.330 + "privilege"."voting_right" = TRUE 2.331 + LEFT JOIN "subscription" ON 2.332 + "subscription"."member_id" = "recipient_id_p" AND 2.333 + "subscription"."unit_id" = "area"."unit_id" 2.334 + LEFT JOIN "ignored_initiative" ON 2.335 + "ignored_initiative"."member_id" = "recipient_id_p" AND 2.336 + "ignored_initiative"."initiative_id" = "initiative"."id" 2.337 + WHERE "supporter"."member_id" = "member_id_v" 2.338 + AND "issue"."area_id" = "area_id_p" 2.339 + AND "issue"."state" IN ('admission', 'discussion', 'verification') 2.340 + AND "initiative"."revoked" ISNULL 2.341 + AND "self_support"."member_id" ISNULL 2.342 + AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"] 2.343 + AND ( 2.344 + "privilege"."member_id" NOTNULL OR 2.345 + "subscription"."member_id" NOTNULL ) 2.346 + AND "ignored_initiative"."member_id" ISNULL 2.347 + AND NOT EXISTS ( 2.348 + SELECT NULL FROM "draft" 2.349 + JOIN "ignored_member" ON 2.350 + "ignored_member"."member_id" = "recipient_id_p" AND 2.351 + "ignored_member"."other_member_id" = "draft"."author_id" 2.352 + WHERE "draft"."initiative_id" = "initiative"."id" 2.353 + ) 2.354 + ORDER BY md5("seed_v" || '-' || "initiative"."id") 2.355 + LIMIT 1; 2.356 + IF FOUND THEN 2.357 + "match_v" := TRUE; 2.358 + RETURN NEXT "initiative_id_v"; 2.359 + IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN 2.360 + RETURN; 2.361 + END IF; 2.362 + "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v"; 2.363 + END IF; 2.364 + END LOOP; 2.365 + EXIT WHEN NOT "match_v"; 2.366 + END LOOP; 2.367 + RETURN; 2.368 + END; 2.369 + $$; 2.370 + 2.371 +COMMENT ON FUNCTION "featured_initiative" 2.372 + ( "recipient_id_p" "member"."id"%TYPE, 2.373 + "area_id_p" "area"."id"%TYPE ) 2.374 + IS 'Helper function for view "updated_or_featured_initiative"'; 2.375 + 2.376 +CREATE VIEW "updated_or_featured_initiative" AS 2.377 + SELECT 2.378 + "subquery".*, 2.379 + NOT EXISTS ( 2.380 + SELECT NULL FROM "initiative" AS "better_initiative" 2.381 + WHERE "better_initiative"."issue_id" = "initiative"."issue_id" 2.382 + AND 2.383 + ( COALESCE("better_initiative"."supporter_count", -1), 2.384 + -"better_initiative"."id" ) > 2.385 + ( COALESCE("initiative"."supporter_count", -1), 2.386 + -"initiative"."id" ) 2.387 + ) AS "leading" 2.388 + FROM ( 2.389 + SELECT * FROM "updated_initiative" 2.390 + UNION ALL 2.391 + SELECT 2.392 + "member"."id" AS "recipient_id", 2.393 + TRUE AS "featured", 2.394 + "featured_initiative_id" AS "initiative_id" 2.395 + FROM "member" CROSS JOIN "area" 2.396 + CROSS JOIN LATERAL 2.397 + "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id" 2.398 + JOIN "initiative" ON "initiative"."id" = "featured_initiative_id" 2.399 + ) AS "subquery" 2.400 + JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"; 2.401 + 2.402 +COMMENT ON VIEW "updated_or_featured_initiative" IS 'Initiatives to be included in a scheduled notification mail because (a) they have been updated or (b) they are featured'; 2.403 + 2.404 +COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail'; 2.405 +COMMENT ON COLUMN "updated_or_featured_initiative"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")'; 2.406 +COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail'; 2.407 +COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue'; 2.408 + 2.409 +CREATE VIEW "leading_complement_initiative" AS 2.410 + SELECT * FROM ( 2.411 + SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id") 2.412 + "uf_initiative"."recipient_id", 2.413 + FALSE AS "featured", 2.414 + "uf_initiative"."initiative_id", 2.415 + TRUE AS "leading" 2.416 + FROM "updated_or_featured_initiative" AS "uf_initiative" 2.417 + JOIN "initiative" AS "uf_initiative_full" ON 2.418 + "uf_initiative_full"."id" = "uf_initiative"."initiative_id" 2.419 + JOIN "initiative" ON 2.420 + "initiative"."issue_id" = "uf_initiative_full"."issue_id" 2.421 + WHERE "initiative"."revoked" ISNULL 2.422 + ORDER BY 2.423 + "uf_initiative"."recipient_id", 2.424 + "initiative"."issue_id", 2.425 + "initiative"."supporter_count" DESC, 2.426 + "initiative"."id" 2.427 + ) AS "subquery" 2.428 + WHERE NOT EXISTS ( 2.429 + SELECT NULL FROM "updated_or_featured_initiative" AS "other" 2.430 + WHERE "other"."recipient_id" = "subquery"."recipient_id" 2.431 + AND "other"."initiative_id" = "subquery"."initiative_id" 2.432 + ); 2.433 + 2.434 +COMMENT ON VIEW "leading_complement_initiative" IS 'Helper view for view "unfiltered_initiative_for_notification" in order to always include the most supported initiative of an issue'; 2.435 +COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view'; 2.436 +COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail'; 2.437 +COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view'; 2.438 + 2.439 +CREATE VIEW "unfiltered_initiative_for_notification" AS 2.440 + SELECT 2.441 + "subquery".*, 2.442 + "supporter"."member_id" NOTNULL AS "supported", 2.443 + CASE WHEN "supporter"."member_id" NOTNULL THEN 2.444 + EXISTS ( 2.445 + SELECT NULL FROM "draft" 2.446 + WHERE "draft"."initiative_id" = "subquery"."initiative_id" 2.447 + AND "draft"."id" > "supporter"."draft_id" 2.448 + ) 2.449 + ELSE 2.450 + EXISTS ( 2.451 + SELECT NULL FROM "draft" 2.452 + WHERE "draft"."initiative_id" = "subquery"."initiative_id" 2.453 + AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE) 2.454 + ) 2.455 + END AS "new_draft", 2.456 + CASE WHEN "supporter"."member_id" NOTNULL THEN 2.457 + ( SELECT count(1) FROM "suggestion" 2.458 + LEFT JOIN "opinion" ON 2.459 + "opinion"."member_id" = "supporter"."member_id" AND 2.460 + "opinion"."suggestion_id" = "suggestion"."id" 2.461 + WHERE "suggestion"."initiative_id" = "subquery"."initiative_id" 2.462 + AND "opinion"."member_id" ISNULL 2.463 + AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE) 2.464 + ) 2.465 + ELSE 2.466 + ( SELECT count(1) FROM "suggestion" 2.467 + WHERE "suggestion"."initiative_id" = "subquery"."initiative_id" 2.468 + AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE) 2.469 + ) 2.470 + END AS "new_suggestion_count" 2.471 + FROM ( 2.472 + SELECT * FROM "updated_or_featured_initiative" 2.473 + UNION ALL 2.474 + SELECT * FROM "leading_complement_initiative" 2.475 + ) AS "subquery" 2.476 + LEFT JOIN "supporter" ON 2.477 + "supporter"."member_id" = "subquery"."recipient_id" AND 2.478 + "supporter"."initiative_id" = "subquery"."initiative_id" 2.479 + LEFT JOIN "notification_initiative_sent" AS "sent" ON 2.480 + "sent"."member_id" = "subquery"."recipient_id" AND 2.481 + "sent"."initiative_id" = "subquery"."initiative_id"; 2.482 + 2.483 +COMMENT ON VIEW "unfiltered_initiative_for_notification" IS 'Helper view which simply combines the views "updated_or_featured_initiative" and "leading_complement_initiative" and adds columns "supported", "new_draft", and "new_suggestion_count'; 2.484 + 2.485 +COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient'; 2.486 +COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_draft" IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)'; 2.487 +COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")'; 2.488 + 2.489 +CREATE VIEW "initiative_for_notification" AS 2.490 + SELECT "unfiltered1".* 2.491 + FROM "unfiltered_initiative_for_notification" "unfiltered1" 2.492 + JOIN "initiative" AS "initiative1" ON 2.493 + "initiative1"."id" = "unfiltered1"."initiative_id" 2.494 + JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id" 2.495 + WHERE EXISTS ( 2.496 + SELECT NULL 2.497 + FROM "unfiltered_initiative_for_notification" "unfiltered2" 2.498 + JOIN "initiative" AS "initiative2" ON 2.499 + "initiative2"."id" = "unfiltered2"."initiative_id" 2.500 + JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id" 2.501 + WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id" 2.502 + AND "issue1"."area_id" = "issue2"."area_id" 2.503 + AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 ) 2.504 + ); 2.505 + 2.506 +COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail'; 2.507 + 2.508 +COMMENT ON COLUMN "initiative_for_notification"."recipient_id" IS '"id" of the member who receives the notification mail'; 2.509 +COMMENT ON COLUMN "initiative_for_notification"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")'; 2.510 +COMMENT ON COLUMN "initiative_for_notification"."initiative_id" IS '"id" of the initiative to be included in the notification mail'; 2.511 +COMMENT ON COLUMN "initiative_for_notification"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue'; 2.512 +COMMENT ON COLUMN "initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient'; 2.513 +COMMENT ON COLUMN "initiative_for_notification"."new_draft" IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)'; 2.514 +COMMENT ON COLUMN "initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")'; 2.515 + 2.516 +CREATE VIEW "scheduled_notification_to_send" AS 2.517 + SELECT * FROM ( 2.518 + SELECT 2.519 + "id" AS "recipient_id", 2.520 + now() - CASE WHEN "notification_dow" ISNULL THEN 2.521 + ( "notification_sent"::DATE + CASE 2.522 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 2.523 + THEN 0 ELSE 1 END 2.524 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 2.525 + ELSE 2.526 + ( "notification_sent"::DATE + 2.527 + ( 7 + "notification_dow" - 2.528 + EXTRACT(DOW FROM 2.529 + ( "notification_sent"::DATE + CASE 2.530 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 2.531 + THEN 0 ELSE 1 END 2.532 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 2.533 + )::INTEGER 2.534 + ) % 7 + 2.535 + CASE 2.536 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 2.537 + THEN 0 ELSE 1 2.538 + END 2.539 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 2.540 + END AS "pending" 2.541 + FROM ( 2.542 + SELECT 2.543 + "id", 2.544 + COALESCE("notification_sent", "activated") AS "notification_sent", 2.545 + "notification_dow", 2.546 + "notification_hour" 2.547 + FROM "member" 2.548 + WHERE "disable_notifications" = FALSE 2.549 + AND "notification_hour" NOTNULL 2.550 + ) AS "subquery1" 2.551 + ) AS "subquery2" 2.552 + WHERE "pending" > '0'::INTERVAL; 2.553 + 2.554 +COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending'; 2.555 + 2.556 +COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail'; 2.557 +COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending'; 2.558 + 2.559 +CREATE VIEW "newsletter_to_send" AS 2.560 + SELECT 2.561 + "member"."id" AS "recipient_id", 2.562 + "newsletter"."id" AS "newsletter_id", 2.563 + "newsletter"."published" 2.564 + FROM "newsletter" CROSS JOIN "member" 2.565 + LEFT JOIN "privilege" ON 2.566 + "privilege"."member_id" = "member"."id" AND 2.567 + "privilege"."unit_id" = "newsletter"."unit_id" AND 2.568 + "privilege"."voting_right" = TRUE 2.569 + LEFT JOIN "subscription" ON 2.570 + "subscription"."member_id" = "member"."id" AND 2.571 + "subscription"."unit_id" = "newsletter"."unit_id" 2.572 + WHERE "newsletter"."published" <= now() 2.573 + AND "newsletter"."sent" ISNULL 2.574 + AND "member"."locked" = FALSE 2.575 + AND ( 2.576 + "member"."disable_notifications" = FALSE OR 2.577 + "newsletter"."include_all_members" = TRUE ) 2.578 + AND ( 2.579 + "newsletter"."unit_id" ISNULL OR 2.580 + "privilege"."member_id" NOTNULL OR 2.581 + "subscription"."member_id" NOTNULL ); 2.582 + 2.583 +COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out'; 2.584 +COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)'; 2.585 + 2.586 +CREATE OR REPLACE FUNCTION "require_transaction_isolation"() 2.587 + RETURNS VOID 2.588 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.589 + BEGIN 2.590 + IF 2.591 + current_setting('transaction_isolation') NOT IN 2.592 + ('repeatable read', 'serializable') 2.593 + THEN 2.594 + RAISE EXCEPTION 'Insufficient transaction isolation level' USING 2.595 + HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.'; 2.596 + END IF; 2.597 + RETURN; 2.598 + END; 2.599 + $$; 2.600 + 2.601 +CREATE FUNCTION "get_initiatives_for_notification" 2.602 + ( "recipient_id_p" "member"."id"%TYPE ) 2.603 + RETURNS SETOF "initiative_for_notification" 2.604 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.605 + DECLARE 2.606 + "result_row" "initiative_for_notification"%ROWTYPE; 2.607 + "last_draft_id_v" "draft"."id"%TYPE; 2.608 + "last_suggestion_id_v" "suggestion"."id"%TYPE; 2.609 + BEGIN 2.610 + PERFORM "require_transaction_isolation"(); 2.611 + PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE; 2.612 + FOR "result_row" IN 2.613 + SELECT * FROM "initiative_for_notification" 2.614 + WHERE "recipient_id" = "recipient_id_p" 2.615 + LOOP 2.616 + SELECT "id" INTO "last_draft_id_v" FROM "draft" 2.617 + WHERE "draft"."initiative_id" = "result_row"."initiative_id" 2.618 + ORDER BY "id" DESC LIMIT 1; 2.619 + SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion" 2.620 + WHERE "suggestion"."initiative_id" = "result_row"."initiative_id" 2.621 + ORDER BY "id" DESC LIMIT 1; 2.622 + /* compatibility with PostgreSQL 9.1 */ 2.623 + DELETE FROM "notification_initiative_sent" 2.624 + WHERE "member_id" = "recipient_id_p" 2.625 + AND "initiative_id" = "result_row"."initiative_id"; 2.626 + INSERT INTO "notification_initiative_sent" 2.627 + ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 2.628 + VALUES ( 2.629 + "recipient_id_p", 2.630 + "result_row"."initiative_id", 2.631 + "last_draft_id_v", 2.632 + "last_suggestion_id_v" ); 2.633 + /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher 2.634 + INSERT INTO "notification_initiative_sent" 2.635 + ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 2.636 + VALUES ( 2.637 + "recipient_id_p", 2.638 + "result_row"."initiative_id", 2.639 + "last_draft_id_v", 2.640 + "last_suggestion_id_v" ) 2.641 + ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET 2.642 + "last_draft_id" = "last_draft_id_v", 2.643 + "last_suggestion_id" = "last_suggestion_id_v"; 2.644 + */ 2.645 + RETURN NEXT "result_row"; 2.646 + END LOOP; 2.647 + DELETE FROM "notification_initiative_sent" 2.648 + USING "initiative", "issue" 2.649 + WHERE "notification_initiative_sent"."member_id" = "recipient_id_p" 2.650 + AND "initiative"."id" = "notification_initiative_sent"."initiative_id" 2.651 + AND "issue"."id" = "initiative"."issue_id" 2.652 + AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL ); 2.653 + UPDATE "member" SET 2.654 + "notification_counter" = "notification_counter" + 1, 2.655 + "notification_sent" = now() 2.656 + WHERE "id" = "recipient_id_p"; 2.657 + RETURN; 2.658 + END; 2.659 + $$; 2.660 + 2.661 +COMMENT ON FUNCTION "get_initiatives_for_notification" 2.662 + ( "member"."id"%TYPE ) 2.663 + IS 'Returns rows from view "initiative_for_notification" for a given recipient while updating table "notification_initiative_sent" and columns "notification_counter" and "notification_sent" of "member" table'; 2.664 + 2.665 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 2.666 + RETURNS VOID 2.667 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.668 + BEGIN 2.669 + UPDATE "member" SET 2.670 + "last_login" = NULL, 2.671 + "last_delegation_check" = NULL, 2.672 + "login" = NULL, 2.673 + "password" = NULL, 2.674 + "authority" = NULL, 2.675 + "authority_uid" = NULL, 2.676 + "authority_login" = NULL, 2.677 + "locked" = TRUE, 2.678 + "active" = FALSE, 2.679 + "notify_email" = NULL, 2.680 + "notify_email_unconfirmed" = NULL, 2.681 + "notify_email_secret" = NULL, 2.682 + "notify_email_secret_expiry" = NULL, 2.683 + "notify_email_lock_expiry" = NULL, 2.684 + "disable_notifications" = NULL, 2.685 + "notification_counter" = NULL, 2.686 + "notification_sample_size" = NULL, 2.687 + "notification_dow" = NULL, 2.688 + "notification_hour" = NULL, 2.689 + "login_recovery_expiry" = NULL, 2.690 + "password_reset_secret" = NULL, 2.691 + "password_reset_secret_expiry" = NULL, 2.692 + "organizational_unit" = NULL, 2.693 + "internal_posts" = NULL, 2.694 + "realname" = NULL, 2.695 + "birthday" = NULL, 2.696 + "address" = NULL, 2.697 + "email" = NULL, 2.698 + "xmpp_address" = NULL, 2.699 + "website" = NULL, 2.700 + "phone" = NULL, 2.701 + "mobile_phone" = NULL, 2.702 + "profession" = NULL, 2.703 + "external_memberships" = NULL, 2.704 + "external_posts" = NULL, 2.705 + "statement" = NULL 2.706 + WHERE "id" = "member_id_p"; 2.707 + -- "text_search_data" is updated by triggers 2.708 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 2.709 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 2.710 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 2.711 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 2.712 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 2.713 + DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 2.714 + DELETE FROM "session" WHERE "member_id" = "member_id_p"; 2.715 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 2.716 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 2.717 + DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 2.718 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 2.719 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 2.720 + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 2.721 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 2.722 + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 2.723 + DELETE FROM "direct_voter" USING "issue" 2.724 + WHERE "direct_voter"."issue_id" = "issue"."id" 2.725 + AND "issue"."closed" ISNULL 2.726 + AND "member_id" = "member_id_p"; 2.727 + RETURN; 2.728 + END; 2.729 + $$; 2.730 + 2.731 +CREATE OR REPLACE FUNCTION "delete_private_data"() 2.732 + RETURNS VOID 2.733 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.734 + BEGIN 2.735 + DELETE FROM "temporary_transaction_data"; 2.736 + DELETE FROM "member" WHERE "activated" ISNULL; 2.737 + UPDATE "member" SET 2.738 + "invite_code" = NULL, 2.739 + "invite_code_expiry" = NULL, 2.740 + "admin_comment" = NULL, 2.741 + "last_login" = NULL, 2.742 + "last_delegation_check" = NULL, 2.743 + "login" = NULL, 2.744 + "password" = NULL, 2.745 + "authority" = NULL, 2.746 + "authority_uid" = NULL, 2.747 + "authority_login" = NULL, 2.748 + "lang" = NULL, 2.749 + "notify_email" = NULL, 2.750 + "notify_email_unconfirmed" = NULL, 2.751 + "notify_email_secret" = NULL, 2.752 + "notify_email_secret_expiry" = NULL, 2.753 + "notify_email_lock_expiry" = NULL, 2.754 + "disable_notifications" = NULL, 2.755 + "notification_counter" = NULL, 2.756 + "notification_sample_size" = NULL, 2.757 + "notification_dow" = NULL, 2.758 + "notification_hour" = NULL, 2.759 + "login_recovery_expiry" = NULL, 2.760 + "password_reset_secret" = NULL, 2.761 + "password_reset_secret_expiry" = NULL, 2.762 + "organizational_unit" = NULL, 2.763 + "internal_posts" = NULL, 2.764 + "realname" = NULL, 2.765 + "birthday" = NULL, 2.766 + "address" = NULL, 2.767 + "email" = NULL, 2.768 + "xmpp_address" = NULL, 2.769 + "website" = NULL, 2.770 + "phone" = NULL, 2.771 + "mobile_phone" = NULL, 2.772 + "profession" = NULL, 2.773 + "external_memberships" = NULL, 2.774 + "external_posts" = NULL, 2.775 + "formatting_engine" = NULL, 2.776 + "statement" = NULL; 2.777 + -- "text_search_data" is updated by triggers 2.778 + DELETE FROM "setting"; 2.779 + DELETE FROM "setting_map"; 2.780 + DELETE FROM "member_relation_setting"; 2.781 + DELETE FROM "member_image"; 2.782 + DELETE FROM "contact"; 2.783 + DELETE FROM "ignored_member"; 2.784 + DELETE FROM "session"; 2.785 + DELETE FROM "area_setting"; 2.786 + DELETE FROM "issue_setting"; 2.787 + DELETE FROM "ignored_initiative"; 2.788 + DELETE FROM "initiative_setting"; 2.789 + DELETE FROM "suggestion_setting"; 2.790 + DELETE FROM "non_voter"; 2.791 + DELETE FROM "direct_voter" USING "issue" 2.792 + WHERE "direct_voter"."issue_id" = "issue"."id" 2.793 + AND "issue"."closed" ISNULL; 2.794 + RETURN; 2.795 + END; 2.796 + $$; 2.797 + 2.798 +COMMIT;
3.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 3.2 +++ b/update/core-update.v3.2.0-v3.2.1.sql Sat Apr 30 17:03:48 2016 +0200 3.3 @@ -0,0 +1,67 @@ 3.4 +BEGIN; 3.5 + 3.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 3.7 + SELECT * FROM (VALUES ('3.2.1', 3, 2, 1)) 3.8 + AS "subquery"("string", "major", "minor", "revision"); 3.9 + 3.10 +CREATE OR REPLACE FUNCTION "get_initiatives_for_notification" 3.11 + ( "recipient_id_p" "member"."id"%TYPE ) 3.12 + RETURNS SETOF "initiative_for_notification" 3.13 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.14 + DECLARE 3.15 + "result_row" "initiative_for_notification"%ROWTYPE; 3.16 + "last_draft_id_v" "draft"."id"%TYPE; 3.17 + "last_suggestion_id_v" "suggestion"."id"%TYPE; 3.18 + BEGIN 3.19 + PERFORM "require_transaction_isolation"(); 3.20 + PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE; 3.21 + FOR "result_row" IN 3.22 + SELECT * FROM "initiative_for_notification" 3.23 + WHERE "recipient_id" = "recipient_id_p" 3.24 + LOOP 3.25 + SELECT "id" INTO "last_draft_id_v" FROM "draft" 3.26 + WHERE "draft"."initiative_id" = "result_row"."initiative_id" 3.27 + ORDER BY "id" DESC LIMIT 1; 3.28 + SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion" 3.29 + WHERE "suggestion"."initiative_id" = "result_row"."initiative_id" 3.30 + ORDER BY "id" DESC LIMIT 1; 3.31 + /* compatibility with PostgreSQL 9.1 */ 3.32 + DELETE FROM "notification_initiative_sent" 3.33 + WHERE "member_id" = "recipient_id_p" 3.34 + AND "initiative_id" = "result_row"."initiative_id"; 3.35 + INSERT INTO "notification_initiative_sent" 3.36 + ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 3.37 + VALUES ( 3.38 + "recipient_id_p", 3.39 + "result_row"."initiative_id", 3.40 + "last_draft_id_v", 3.41 + "last_suggestion_id_v" ); 3.42 + /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher 3.43 + INSERT INTO "notification_initiative_sent" 3.44 + ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 3.45 + VALUES ( 3.46 + "recipient_id_p", 3.47 + "result_row"."initiative_id", 3.48 + "last_draft_id_v", 3.49 + "last_suggestion_id_v" ) 3.50 + ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET 3.51 + "last_draft_id" = "last_draft_id_v", 3.52 + "last_suggestion_id" = "last_suggestion_id_v"; 3.53 + */ 3.54 + RETURN NEXT "result_row"; 3.55 + END LOOP; 3.56 + DELETE FROM "notification_initiative_sent" 3.57 + USING "initiative", "issue" 3.58 + WHERE "notification_initiative_sent"."member_id" = "recipient_id_p" 3.59 + AND "initiative"."id" = "notification_initiative_sent"."initiative_id" 3.60 + AND "issue"."id" = "initiative"."issue_id" 3.61 + AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL ); 3.62 + UPDATE "member" SET 3.63 + "notification_counter" = "notification_counter" + 1, 3.64 + "notification_sent" = now() 3.65 + WHERE "id" = "recipient_id_p"; 3.66 + RETURN; 3.67 + END; 3.68 + $$; 3.69 + 3.70 +COMMIT;