liquid_feedback_core
changeset 510:fd330c887a83
Updated update script to v3.2.0
author | jbe |
---|---|
date | Sat Apr 16 19:52:19 2016 +0200 (2016-04-16) |
parents | 3b684315c724 |
children | 48761b189274 |
files | update/core-update.v3.1.0-v3.2.0.sql |
line diff
1.1 --- a/update/core-update.v3.1.0-v3.2.0.sql Sat Apr 16 19:41:37 2016 +0200 1.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql Sat Apr 16 19:52:19 2016 +0200 1.3 @@ -4,22 +4,34 @@ 1.4 SELECT * FROM (VALUES ('3.2.0', 3, 2, 0)) 1.5 AS "subquery"("string", "major", "minor", "revision"); 1.6 1.7 --- TODO: preliminary script 1.8 +ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE; 1.9 +ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1; 1.10 +ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3; 1.11 +ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6); 1.12 +ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23); 1.13 +ALTER TABLE "member" ADD COLUMN "notification_sent" TIMESTAMP; 1.14 +ALTER TABLE "member" ADD 1.15 + CONSTRAINT "notification_dow_requires_notification_hour" 1.16 + CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL); 1.17 1.18 -ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE; 1.19 -ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1; 1.20 -ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3; 1.21 -ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6); 1.22 -ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23); 1.23 -ALTER TABLE "member" ADD COLUMN "notification_sent" TIMESTAMP; 1.24 +UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none'::"notify_level"; 1.25 1.26 -UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none'; 1.27 +ALTER TABLE "member" DROP COLUMN "notify_level"; 1.28 1.29 DROP VIEW "selected_event_seen_by_member"; 1.30 DROP VIEW "event_seen_by_member"; 1.31 -ALTER TABLE "member" DROP COLUMN "notify_level"; 1.32 DROP TYPE "notify_level"; 1.33 - 1.34 + 1.35 +COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications'; 1.36 +COMMENT ON COLUMN "member"."notification_counter" IS 'Sequential number of next scheduled notification message (used as a seed for pseudo-random initiative selection algorithm)'; 1.37 +COMMENT ON COLUMN "member"."notification_sample_size" IS 'Number of featured initiatives per issue in scheduled notification messages'; 1.38 +COMMENT ON COLUMN "member"."notification_dow" IS 'Day of week for scheduled notifications (NULL to receive a daily digest)'; 1.39 +COMMENT ON COLUMN "member"."notification_hour" IS 'Time of day when scheduled notifications are sent out'; 1.40 +COMMENT ON COLUMN "member"."notification_sent" IS 'Timestamp of last scheduled notification mail that has been sent out'; 1.41 + 1.42 +ALTER TABLE "rendered_member_statement" ALTER COLUMN "member_id" SET DATA TYPE INT4; 1.43 +ALTER TABLE "session" ALTER COLUMN "member_id" SET DATA TYPE INT4; 1.44 + 1.45 CREATE TABLE "subscription" ( 1.46 PRIMARY KEY ("member_id", "unit_id"), 1.47 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.48 @@ -36,13 +48,29 @@ 1.49 1.50 COMMENT ON TABLE "ignored_area" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given subject area unless he/she declared interested in a particular issue'; 1.51 1.52 -CREATE TABLE "initiative_notification_sent" ( 1.53 +ALTER TABLE "ignored_initiative" DROP CONSTRAINT "ignored_initiative_pkey"; 1.54 +DROP INDEX "ignored_initiative_member_id_idx"; 1.55 + 1.56 +ALTER TABLE "ignored_initiative" ADD PRIMARY KEY ("member_id", "initiative_id"); 1.57 +CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id"); 1.58 + 1.59 +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'; 1.60 + 1.61 +ALTER TABLE "notification_sent" RENAME TO "notification_event_sent"; 1.62 +ALTER INDEX "notification_sent_singleton_idx" RENAME TO "notification_event_sent_singleton_idx"; 1.63 + 1.64 +CREATE TABLE "notification_initiative_sent" ( 1.65 PRIMARY KEY ("member_id", "initiative_id"), 1.66 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.67 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.68 "last_draft_id" INT8 NOT NULL, 1.69 "last_suggestion_id" INT8 ); 1.70 -CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id"); 1.71 +CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id"); 1.72 + 1.73 +COMMENT ON TABLE "notification_initiative_sent" IS 'Information which initiatives have been promoted to a member in a scheduled notification mail'; 1.74 + 1.75 +COMMENT ON COLUMN "notification_initiative_sent"."last_draft_id" IS 'Current (i.e. last) draft_id when initiative had been promoted'; 1.76 +COMMENT ON COLUMN "notification_initiative_sent"."last_suggestion_id" IS 'Current (i.e. last) draft_id when initiative had been promoted'; 1.77 1.78 CREATE TABLE "newsletter" ( 1.79 "id" SERIAL4 PRIMARY KEY, 1.80 @@ -56,6 +84,107 @@ 1.81 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL; 1.82 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published"); 1.83 1.84 +COMMENT ON TABLE "newsletter" IS 'Contains newsletters created by administrators to be sent out and for further reference'; 1.85 + 1.86 +COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter is to be sent out (and made available in the frontend)'; 1.87 +COMMENT ON COLUMN "newsletter"."unit_id" IS 'If set, only members with voting right in the given unit are considered to be recipients'; 1.88 +COMMENT ON COLUMN "newsletter"."include_all_members" IS 'TRUE = include all members regardless of their ''disable_notifications'' setting'; 1.89 +COMMENT ON COLUMN "newsletter"."sent" IS 'Timestamp when the newsletter has been mailed out'; 1.90 +COMMENT ON COLUMN "newsletter"."subject" IS 'Subject line (e.g. to be used for the email)'; 1.91 +COMMENT ON COLUMN "newsletter"."content" IS 'Plain text content of the newsletter'; 1.92 + 1.93 +CREATE OR REPLACE FUNCTION "issue_requires_first_initiative_trigger"() 1.94 + RETURNS TRIGGER 1.95 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.96 + BEGIN 1.97 + IF NOT EXISTS ( 1.98 + SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id" 1.99 + ) THEN 1.100 + RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING 1.101 + ERRCODE = 'integrity_constraint_violation', 1.102 + HINT = 'Create issue, initiative, and draft within the same transaction.'; 1.103 + END IF; 1.104 + RETURN NULL; 1.105 + END; 1.106 + $$; 1.107 + 1.108 +CREATE OR REPLACE FUNCTION "initiative_requires_first_draft_trigger"() 1.109 + RETURNS TRIGGER 1.110 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.111 + BEGIN 1.112 + IF NOT EXISTS ( 1.113 + SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id" 1.114 + ) THEN 1.115 + RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING 1.116 + ERRCODE = 'integrity_constraint_violation', 1.117 + HINT = 'Create issue, initiative and draft within the same transaction.'; 1.118 + END IF; 1.119 + RETURN NULL; 1.120 + END; 1.121 + $$; 1.122 + 1.123 +CREATE OR REPLACE FUNCTION "suggestion_requires_first_opinion_trigger"() 1.124 + RETURNS TRIGGER 1.125 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.126 + BEGIN 1.127 + IF NOT EXISTS ( 1.128 + SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id" 1.129 + ) THEN 1.130 + RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING 1.131 + ERRCODE = 'integrity_constraint_violation', 1.132 + HINT = 'Create suggestion and opinion within the same transaction.'; 1.133 + END IF; 1.134 + RETURN NULL; 1.135 + END; 1.136 + $$; 1.137 + 1.138 +CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"() 1.139 + RETURNS TRIGGER 1.140 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.141 + DECLARE 1.142 + "issue_id_v" "issue"."id"%TYPE; 1.143 + "issue_row" "issue"%ROWTYPE; 1.144 + BEGIN 1.145 + IF EXISTS ( 1.146 + SELECT NULL FROM "temporary_transaction_data" 1.147 + WHERE "txid" = txid_current() 1.148 + AND "key" = 'override_protection_triggers' 1.149 + AND "value" = TRUE::TEXT 1.150 + ) THEN 1.151 + RETURN NULL; 1.152 + END IF; 1.153 + IF TG_OP = 'DELETE' THEN 1.154 + "issue_id_v" := OLD."issue_id"; 1.155 + ELSE 1.156 + "issue_id_v" := NEW."issue_id"; 1.157 + END IF; 1.158 + SELECT INTO "issue_row" * FROM "issue" 1.159 + WHERE "id" = "issue_id_v" FOR SHARE; 1.160 + IF ( 1.161 + "issue_row"."closed" NOTNULL OR ( 1.162 + "issue_row"."state" = 'voting' AND 1.163 + "issue_row"."phase_finished" NOTNULL 1.164 + ) 1.165 + ) THEN 1.166 + IF 1.167 + TG_RELID = 'direct_voter'::regclass AND 1.168 + TG_OP = 'UPDATE' 1.169 + THEN 1.170 + IF 1.171 + OLD."issue_id" = NEW."issue_id" AND 1.172 + OLD."member_id" = NEW."member_id" AND 1.173 + OLD."weight" = NEW."weight" 1.174 + THEN 1.175 + RETURN NULL; -- allows changing of voter comment 1.176 + END IF; 1.177 + END IF; 1.178 + RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING 1.179 + ERRCODE = 'integrity_constraint_violation'; 1.180 + END IF; 1.181 + RETURN NULL; 1.182 + END; 1.183 + $$; 1.184 + 1.185 CREATE VIEW "event_for_notification" AS 1.186 SELECT 1.187 "member"."id" AS "recipient_id", 1.188 @@ -86,6 +215,10 @@ 1.189 ( "event"."event" = 'initiative_revoked'::"event_type" AND 1.190 "supporter"."member_id" NOTNULL ) ); 1.191 1.192 +COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient'; 1.193 + 1.194 +COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail'; 1.195 + 1.196 CREATE VIEW "updated_initiative" AS 1.197 SELECT 1.198 "supporter"."member_id" AS "recipient_id", 1.199 @@ -94,7 +227,7 @@ 1.200 FROM "supporter" 1.201 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id" 1.202 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.203 - LEFT JOIN "initiative_notification_sent" AS "sent" ON 1.204 + LEFT JOIN "notification_initiative_sent" AS "sent" ON 1.205 "sent"."member_id" = "supporter"."member_id" AND 1.206 "sent"."initiative_id" = "supporter"."initiative_id" 1.207 LEFT JOIN "ignored_initiative" ON 1.208 @@ -127,6 +260,8 @@ 1.209 ) 1.210 ); 1.211 1.212 +COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"'; 1.213 + 1.214 CREATE FUNCTION "featured_initiative" 1.215 ( "recipient_id_p" "member"."id"%TYPE, 1.216 "area_id_p" "area"."id"%TYPE ) 1.217 @@ -218,6 +353,11 @@ 1.218 END; 1.219 $$; 1.220 1.221 +COMMENT ON FUNCTION "featured_initiative" 1.222 + ( "recipient_id_p" "member"."id"%TYPE, 1.223 + "area_id_p" "area"."id"%TYPE ) 1.224 + IS 'Helper function for view "updated_or_featured_initiative"'; 1.225 + 1.226 CREATE VIEW "updated_or_featured_initiative" AS 1.227 SELECT 1.228 "subquery".*, 1.229 @@ -244,6 +384,13 @@ 1.230 ) AS "subquery" 1.231 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id"; 1.232 1.233 +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'; 1.234 + 1.235 +COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail'; 1.236 +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")'; 1.237 +COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail'; 1.238 +COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue'; 1.239 + 1.240 CREATE VIEW "leading_complement_initiative" AS 1.241 SELECT * FROM ( 1.242 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id") 1.243 @@ -269,6 +416,11 @@ 1.244 AND "other"."initiative_id" = "subquery"."initiative_id" 1.245 ); 1.246 1.247 +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'; 1.248 +COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view'; 1.249 +COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail'; 1.250 +COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view'; 1.251 + 1.252 CREATE VIEW "unfiltered_initiative_for_notification" AS 1.253 SELECT 1.254 "subquery".*, 1.255 @@ -309,10 +461,16 @@ 1.256 LEFT JOIN "supporter" ON 1.257 "supporter"."member_id" = "subquery"."recipient_id" AND 1.258 "supporter"."initiative_id" = "subquery"."initiative_id" 1.259 - LEFT JOIN "initiative_notification_sent" AS "sent" ON 1.260 + LEFT JOIN "notification_initiative_sent" AS "sent" ON 1.261 "sent"."member_id" = "subquery"."recipient_id" AND 1.262 "sent"."initiative_id" = "subquery"."initiative_id"; 1.263 1.264 +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'; 1.265 + 1.266 +COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient'; 1.267 +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)'; 1.268 +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")'; 1.269 + 1.270 CREATE VIEW "initiative_for_notification" AS 1.271 SELECT "unfiltered1".* 1.272 FROM "unfiltered_initiative_for_notification" "unfiltered1" 1.273 @@ -330,6 +488,16 @@ 1.274 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 ) 1.275 ); 1.276 1.277 +COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail'; 1.278 + 1.279 +COMMENT ON COLUMN "initiative_for_notification"."recipient_id" IS '"id" of the member who receives the notification mail'; 1.280 +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")'; 1.281 +COMMENT ON COLUMN "initiative_for_notification"."initiative_id" IS '"id" of the initiative to be included in the notification mail'; 1.282 +COMMENT ON COLUMN "initiative_for_notification"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue'; 1.283 +COMMENT ON COLUMN "initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient'; 1.284 +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)'; 1.285 +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")'; 1.286 + 1.287 CREATE VIEW "scheduled_notification_to_send" AS 1.288 SELECT * FROM ( 1.289 SELECT 1.290 @@ -368,6 +536,11 @@ 1.291 ) AS "subquery2" 1.292 WHERE "pending" > '0'::INTERVAL; 1.293 1.294 +COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending'; 1.295 + 1.296 +COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail'; 1.297 +COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending'; 1.298 + 1.299 CREATE VIEW "newsletter_to_send" AS 1.300 SELECT 1.301 "member"."id" AS "recipient_id", 1.302 @@ -391,6 +564,23 @@ 1.303 "privilege"."member_id" NOTNULL OR 1.304 "subscription"."member_id" NOTNULL ); 1.305 1.306 +COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out'; 1.307 + 1.308 +CREATE OR REPLACE FUNCTION "require_transaction_isolation"() 1.309 + RETURNS VOID 1.310 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.311 + BEGIN 1.312 + IF 1.313 + current_setting('transaction_isolation') NOT IN 1.314 + ('repeatable read', 'serializable') 1.315 + THEN 1.316 + RAISE EXCEPTION 'Insufficient transaction isolation level' USING 1.317 + HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.'; 1.318 + END IF; 1.319 + RETURN; 1.320 + END; 1.321 + $$; 1.322 + 1.323 CREATE FUNCTION "get_initiatives_for_notification" 1.324 ( "recipient_id_p" "member"."id"%TYPE ) 1.325 RETURNS SETOF "initiative_for_notification" 1.326 @@ -412,7 +602,7 @@ 1.327 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion" 1.328 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id" 1.329 ORDER BY "id" DESC LIMIT 1; 1.330 - INSERT INTO "initiative_notification_sent" 1.331 + INSERT INTO "notification_initiative_sent" 1.332 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 1.333 VALUES ( 1.334 "recipient_id_p", 1.335 @@ -421,21 +611,21 @@ 1.336 "last_suggestion_id_v" ) 1.337 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET 1.338 "last_draft_id" = CASE 1.339 - WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v" 1.340 - THEN "initiative_notification_sent"."last_draft_id" 1.341 + WHEN "notification_initiative_sent"."last_draft_id" > "last_draft_id_v" 1.342 + THEN "notification_initiative_sent"."last_draft_id" 1.343 ELSE "last_draft_id_v" 1.344 END, 1.345 "last_suggestion_id" = CASE 1.346 - WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v" 1.347 - THEN "initiative_notification_sent"."last_suggestion_id" 1.348 + WHEN "notification_initiative_sent"."last_suggestion_id" > "last_suggestion_id_v" 1.349 + THEN "notification_initiative_sent"."last_suggestion_id" 1.350 ELSE "last_suggestion_id_v" 1.351 END; 1.352 RETURN NEXT "result_row"; 1.353 END LOOP; 1.354 - DELETE FROM "initiative_notification_sent" 1.355 + DELETE FROM "notification_initiative_sent" 1.356 USING "initiative", "issue" 1.357 - WHERE "initiative_notification_sent"."member_id" = "recipient_id_p" 1.358 - AND "initiative"."id" = "initiative_notification_sent"."initiative_id" 1.359 + WHERE "notification_initiative_sent"."member_id" = "recipient_id_p" 1.360 + AND "initiative"."id" = "notification_initiative_sent"."initiative_id" 1.361 AND "issue"."id" = "initiative"."issue_id" 1.362 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL ); 1.363 UPDATE "member" SET 1.364 @@ -446,4 +636,139 @@ 1.365 END; 1.366 $$; 1.367 1.368 +-- TODO: comment missing 1.369 + 1.370 +CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) 1.371 + RETURNS VOID 1.372 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.373 + BEGIN 1.374 + UPDATE "member" SET 1.375 + "last_login" = NULL, 1.376 + "last_delegation_check" = NULL, 1.377 + "login" = NULL, 1.378 + "password" = NULL, 1.379 + "authority" = NULL, 1.380 + "authority_uid" = NULL, 1.381 + "authority_login" = NULL, 1.382 + "locked" = TRUE, 1.383 + "active" = FALSE, 1.384 + "notify_email" = NULL, 1.385 + "notify_email_unconfirmed" = NULL, 1.386 + "notify_email_secret" = NULL, 1.387 + "notify_email_secret_expiry" = NULL, 1.388 + "notify_email_lock_expiry" = NULL, 1.389 + "disable_notifications" = NULL, 1.390 + "notification_counter" = NULL, 1.391 + "notification_sample_size" = NULL, 1.392 + "notification_dow" = NULL, 1.393 + "notification_hour" = NULL, 1.394 + "login_recovery_expiry" = NULL, 1.395 + "password_reset_secret" = NULL, 1.396 + "password_reset_secret_expiry" = NULL, 1.397 + "organizational_unit" = NULL, 1.398 + "internal_posts" = NULL, 1.399 + "realname" = NULL, 1.400 + "birthday" = NULL, 1.401 + "address" = NULL, 1.402 + "email" = NULL, 1.403 + "xmpp_address" = NULL, 1.404 + "website" = NULL, 1.405 + "phone" = NULL, 1.406 + "mobile_phone" = NULL, 1.407 + "profession" = NULL, 1.408 + "external_memberships" = NULL, 1.409 + "external_posts" = NULL, 1.410 + "statement" = NULL 1.411 + WHERE "id" = "member_id_p"; 1.412 + -- "text_search_data" is updated by triggers 1.413 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 1.414 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 1.415 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 1.416 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 1.417 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 1.418 + DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p"; 1.419 + DELETE FROM "session" WHERE "member_id" = "member_id_p"; 1.420 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 1.421 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 1.422 + DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p"; 1.423 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 1.424 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 1.425 + DELETE FROM "membership" WHERE "member_id" = "member_id_p"; 1.426 + DELETE FROM "delegation" WHERE "truster_id" = "member_id_p"; 1.427 + DELETE FROM "non_voter" WHERE "member_id" = "member_id_p"; 1.428 + DELETE FROM "direct_voter" USING "issue" 1.429 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.430 + AND "issue"."closed" ISNULL 1.431 + AND "member_id" = "member_id_p"; 1.432 + RETURN; 1.433 + END; 1.434 + $$; 1.435 + 1.436 +CREATE OR REPLACE FUNCTION "delete_private_data"() 1.437 + RETURNS VOID 1.438 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.439 + BEGIN 1.440 + DELETE FROM "temporary_transaction_data"; 1.441 + DELETE FROM "member" WHERE "activated" ISNULL; 1.442 + UPDATE "member" SET 1.443 + "invite_code" = NULL, 1.444 + "invite_code_expiry" = NULL, 1.445 + "admin_comment" = NULL, 1.446 + "last_login" = NULL, 1.447 + "last_delegation_check" = NULL, 1.448 + "login" = NULL, 1.449 + "password" = NULL, 1.450 + "authority" = NULL, 1.451 + "authority_uid" = NULL, 1.452 + "authority_login" = NULL, 1.453 + "lang" = NULL, 1.454 + "notify_email" = NULL, 1.455 + "notify_email_unconfirmed" = NULL, 1.456 + "notify_email_secret" = NULL, 1.457 + "notify_email_secret_expiry" = NULL, 1.458 + "notify_email_lock_expiry" = NULL, 1.459 + "disable_notifications" = NULL, 1.460 + "notification_counter" = NULL, 1.461 + "notification_sample_size" = NULL, 1.462 + "notification_dow" = NULL, 1.463 + "notification_hour" = NULL, 1.464 + "login_recovery_expiry" = NULL, 1.465 + "password_reset_secret" = NULL, 1.466 + "password_reset_secret_expiry" = NULL, 1.467 + "organizational_unit" = NULL, 1.468 + "internal_posts" = NULL, 1.469 + "realname" = NULL, 1.470 + "birthday" = NULL, 1.471 + "address" = NULL, 1.472 + "email" = NULL, 1.473 + "xmpp_address" = NULL, 1.474 + "website" = NULL, 1.475 + "phone" = NULL, 1.476 + "mobile_phone" = NULL, 1.477 + "profession" = NULL, 1.478 + "external_memberships" = NULL, 1.479 + "external_posts" = NULL, 1.480 + "formatting_engine" = NULL, 1.481 + "statement" = NULL; 1.482 + -- "text_search_data" is updated by triggers 1.483 + DELETE FROM "setting"; 1.484 + DELETE FROM "setting_map"; 1.485 + DELETE FROM "member_relation_setting"; 1.486 + DELETE FROM "member_image"; 1.487 + DELETE FROM "contact"; 1.488 + DELETE FROM "ignored_member"; 1.489 + DELETE FROM "session"; 1.490 + DELETE FROM "area_setting"; 1.491 + DELETE FROM "issue_setting"; 1.492 + DELETE FROM "ignored_initiative"; 1.493 + DELETE FROM "initiative_setting"; 1.494 + DELETE FROM "suggestion_setting"; 1.495 + DELETE FROM "non_voter"; 1.496 + DELETE FROM "direct_voter" USING "issue" 1.497 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.498 + AND "issue"."closed" ISNULL; 1.499 + RETURN; 1.500 + END; 1.501 + $$; 1.502 + 1.503 COMMIT;