liquid_feedback_core
diff update/core-update.beta26-v1.0.0.sql @ 45:5a01d558565b
New function "delete_member_data"(...); Added missing index for "member_history"; References fixed to restrict updates
author | jbe |
---|---|
date | Thu Apr 15 18:47:07 2010 +0200 (2010-04-15) |
parents | |
children |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.beta26-v1.0.0.sql Thu Apr 15 18:47:07 2010 +0200 1.3 @@ -0,0 +1,142 @@ 1.4 +BEGIN; 1.5 + 1.6 +DROP VIEW "liquid_feedback_version"; 1.7 +CREATE VIEW "liquid_feedback_version" AS 1.8 + SELECT * FROM (VALUES ('1.0.0', 1, 0, 0)) 1.9 + AS "subquery"("string", "major", "minor", "revision"); 1.10 + 1.11 +ALTER TABLE "member" ALTER COLUMN "login" DROP NOT NULL; 1.12 + 1.13 +ALTER TABLE "member_history" ALTER COLUMN "login" DROP NOT NULL; 1.14 + 1.15 +CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id"); 1.16 + 1.17 +ALTER TABLE "direct_population_snapshot" DROP 1.18 + CONSTRAINT "direct_population_snapshot_member_id_fkey"; 1.19 +ALTER TABLE "direct_population_snapshot" ADD 1.20 + CONSTRAINT "direct_population_snapshot_member_id_fkey" 1.21 + FOREIGN KEY ("member_id") 1.22 + REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT; 1.23 + 1.24 +ALTER TABLE "delegating_population_snapshot" DROP 1.25 + CONSTRAINT "delegating_population_snapshot_member_id_fkey"; 1.26 +ALTER TABLE "delegating_population_snapshot" ADD 1.27 + CONSTRAINT "delegating_population_snapshot_member_id_fkey" 1.28 + FOREIGN KEY ("member_id") 1.29 + REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT; 1.30 + 1.31 +ALTER TABLE "direct_interest_snapshot" DROP 1.32 + CONSTRAINT "direct_interest_snapshot_member_id_fkey"; 1.33 +ALTER TABLE "direct_interest_snapshot" ADD 1.34 + CONSTRAINT "direct_interest_snapshot_member_id_fkey" 1.35 + FOREIGN KEY ("member_id") 1.36 + REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT; 1.37 + 1.38 +ALTER TABLE "delegating_interest_snapshot" DROP 1.39 + CONSTRAINT "delegating_interest_snapshot_member_id_fkey"; 1.40 +ALTER TABLE "delegating_interest_snapshot" ADD 1.41 + CONSTRAINT "delegating_interest_snapshot_member_id_fkey" 1.42 + FOREIGN KEY ("member_id") 1.43 + REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT; 1.44 + 1.45 +ALTER TABLE "direct_supporter_snapshot" DROP 1.46 + CONSTRAINT "direct_supporter_snapshot_member_id_fkey"; 1.47 +ALTER TABLE "direct_supporter_snapshot" ADD 1.48 + CONSTRAINT "direct_supporter_snapshot_member_id_fkey" 1.49 + FOREIGN KEY ("member_id") 1.50 + REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT; 1.51 + 1.52 +ALTER TABLE "direct_voter" DROP 1.53 + CONSTRAINT "direct_voter_member_id_fkey"; 1.54 +ALTER TABLE "direct_voter" ADD 1.55 + CONSTRAINT "direct_voter_member_id_fkey" 1.56 + FOREIGN KEY ("member_id") 1.57 + REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT; 1.58 + 1.59 +ALTER TABLE "delegating_voter" DROP 1.60 + CONSTRAINT "delegating_voter_member_id_fkey"; 1.61 +ALTER TABLE "delegating_voter" ADD 1.62 + CONSTRAINT "delegating_voter_member_id_fkey" 1.63 + FOREIGN KEY ("member_id") 1.64 + REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT; 1.65 + 1.66 +CREATE OR REPLACE FUNCTION "write_member_history_trigger"() 1.67 + RETURNS TRIGGER 1.68 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.69 + BEGIN 1.70 + IF 1.71 + ( NEW."login" NOTNULL AND OLD."login" NOTNULL AND 1.72 + NEW."login" != OLD."login" ) OR 1.73 + ( NEW."login" NOTNULL AND OLD."login" ISNULL ) OR 1.74 + ( NEW."login" ISNULL AND OLD."login" NOTNULL ) OR 1.75 + NEW."active" != OLD."active" OR 1.76 + NEW."name" != OLD."name" 1.77 + THEN 1.78 + INSERT INTO "member_history" 1.79 + ("member_id", "login", "active", "name") 1.80 + VALUES (NEW."id", OLD."login", OLD."active", OLD."name"); 1.81 + END IF; 1.82 + RETURN NULL; 1.83 + END; 1.84 + $$; 1.85 + 1.86 +CREATE FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE) 1.87 + RETURNS VOID 1.88 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.89 + BEGIN 1.90 + UPDATE "member" SET 1.91 + "login" = NULL, 1.92 + "password" = NULL, 1.93 + "notify_email" = NULL, 1.94 + "notify_email_unconfirmed" = NULL, 1.95 + "notify_email_secret" = NULL, 1.96 + "notify_email_secret_expiry" = NULL, 1.97 + "password_reset_secret" = NULL, 1.98 + "password_reset_secret_expiry" = NULL, 1.99 + "organizational_unit" = NULL, 1.100 + "internal_posts" = NULL, 1.101 + "realname" = NULL, 1.102 + "birthday" = NULL, 1.103 + "address" = NULL, 1.104 + "email" = NULL, 1.105 + "xmpp_address" = NULL, 1.106 + "website" = NULL, 1.107 + "phone" = NULL, 1.108 + "mobile_phone" = NULL, 1.109 + "profession" = NULL, 1.110 + "external_memberships" = NULL, 1.111 + "external_posts" = NULL, 1.112 + "statement" = NULL 1.113 + WHERE "id" = "member_id_p"; 1.114 + -- "text_search_data" is updated by triggers 1.115 + UPDATE "member_history" SET "login" = NULL 1.116 + WHERE "member_id" = "member_id_p"; 1.117 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 1.118 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 1.119 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 1.120 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 1.121 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 1.122 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 1.123 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 1.124 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 1.125 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 1.126 + RETURN; 1.127 + END; 1.128 + $$; 1.129 +COMMENT ON FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)'; 1.130 + 1.131 +CREATE OR REPLACE FUNCTION "delete_private_data"() 1.132 + RETURNS VOID 1.133 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.134 + BEGIN 1.135 + PERFORM "delete_member_data"("id") FROM "member"; 1.136 + DELETE FROM "invite_code"; 1.137 + DELETE FROM "session"; 1.138 + DELETE FROM "direct_voter" USING "issue" 1.139 + WHERE "direct_voter"."issue_id" = "issue"."id" 1.140 + AND "issue"."closed" ISNULL; 1.141 + RETURN; 1.142 + END; 1.143 + $$; 1.144 + 1.145 +COMMIT;