liquid_feedback_core
changeset 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 | 2a3f133a7e25 |
children | 67a2e236fea4 |
files | core.sql update/core-update.beta26-v1.0.0.sql |
line diff
1.1 --- a/core.sql Fri Mar 26 02:17:07 2010 +0100 1.2 +++ b/core.sql Thu Apr 15 18:47:07 2010 +0200 1.3 @@ -6,7 +6,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('beta26', NULL, NULL, NULL)) 1.8 + SELECT * FROM (VALUES ('1.0.0', 1, 0, 0)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -57,7 +57,7 @@ 1.13 "id" SERIAL4 PRIMARY KEY, 1.14 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.15 "last_login" TIMESTAMPTZ, 1.16 - "login" TEXT NOT NULL UNIQUE, 1.17 + "login" TEXT UNIQUE, 1.18 "password" TEXT, 1.19 "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.20 "admin" BOOLEAN NOT NULL DEFAULT FALSE, 1.21 @@ -118,9 +118,10 @@ 1.22 "id" SERIAL8 PRIMARY KEY, 1.23 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.24 "until" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.25 - "login" TEXT NOT NULL, 1.26 + "login" TEXT, 1.27 "active" BOOLEAN NOT NULL, 1.28 "name" TEXT NOT NULL ); 1.29 +CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id"); 1.30 1.31 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names, login names and active flag of members'; 1.32 1.33 @@ -646,7 +647,7 @@ 1.34 PRIMARY KEY ("issue_id", "event", "member_id"), 1.35 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.36 "event" "snapshot_event", 1.37 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.38 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 1.39 "weight" INT4, 1.40 "interest_exists" BOOLEAN NOT NULL ); 1.41 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id"); 1.42 @@ -662,7 +663,7 @@ 1.43 PRIMARY KEY ("issue_id", "event", "member_id"), 1.44 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.45 "event" "snapshot_event", 1.46 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.47 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 1.48 "weight" INT4, 1.49 "scope" "delegation_scope" NOT NULL, 1.50 "delegate_member_ids" INT4[] NOT NULL ); 1.51 @@ -680,7 +681,7 @@ 1.52 PRIMARY KEY ("issue_id", "event", "member_id"), 1.53 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.54 "event" "snapshot_event", 1.55 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.56 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 1.57 "weight" INT4, 1.58 "voting_requested" BOOLEAN ); 1.59 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id"); 1.60 @@ -696,7 +697,7 @@ 1.61 PRIMARY KEY ("issue_id", "event", "member_id"), 1.62 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.63 "event" "snapshot_event", 1.64 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.65 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 1.66 "weight" INT4, 1.67 "scope" "delegation_scope" NOT NULL, 1.68 "delegate_member_ids" INT4[] NOT NULL ); 1.69 @@ -715,7 +716,7 @@ 1.70 PRIMARY KEY ("initiative_id", "event", "member_id"), 1.71 "initiative_id" INT4, 1.72 "event" "snapshot_event", 1.73 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.74 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 1.75 "informed" BOOLEAN NOT NULL, 1.76 "satisfied" BOOLEAN NOT NULL, 1.77 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, 1.78 @@ -732,7 +733,7 @@ 1.79 CREATE TABLE "direct_voter" ( 1.80 PRIMARY KEY ("issue_id", "member_id"), 1.81 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.82 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.83 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 1.84 "weight" INT4, 1.85 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE ); 1.86 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id"); 1.87 @@ -746,7 +747,7 @@ 1.88 CREATE TABLE "delegating_voter" ( 1.89 PRIMARY KEY ("issue_id", "member_id"), 1.90 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.91 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.92 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT, 1.93 "weight" INT4, 1.94 "scope" "delegation_scope" NOT NULL, 1.95 "delegate_member_ids" INT4[] NOT NULL ); 1.96 @@ -795,7 +796,10 @@ 1.97 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.98 BEGIN 1.99 IF 1.100 - NEW."login" != OLD."login" OR 1.101 + ( NEW."login" NOTNULL AND OLD."login" NOTNULL AND 1.102 + NEW."login" != OLD."login" ) OR 1.103 + ( NEW."login" NOTNULL AND OLD."login" ISNULL ) OR 1.104 + ( NEW."login" ISNULL AND OLD."login" NOTNULL ) OR 1.105 NEW."active" != OLD."active" OR 1.106 NEW."name" != OLD."name" 1.107 THEN 1.108 @@ -3184,14 +3188,12 @@ 1.109 ------------------------------ 1.110 1.111 1.112 -CREATE FUNCTION "delete_private_data"() 1.113 +CREATE FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE) 1.114 RETURNS VOID 1.115 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.116 - DECLARE 1.117 - "issue_id_v" "issue"."id"%TYPE; 1.118 BEGIN 1.119 UPDATE "member" SET 1.120 - "login" = 'login' || "id"::text, 1.121 + "login" = NULL, 1.122 "password" = NULL, 1.123 "notify_email" = NULL, 1.124 "notify_email_unconfirmed" = NULL, 1.125 @@ -3212,20 +3214,34 @@ 1.126 "profession" = NULL, 1.127 "external_memberships" = NULL, 1.128 "external_posts" = NULL, 1.129 - "statement" = NULL; 1.130 + "statement" = NULL 1.131 + WHERE "id" = "member_id_p"; 1.132 -- "text_search_data" is updated by triggers 1.133 - UPDATE "member_history" SET "login" = 'login' || "member_id"::text; 1.134 + UPDATE "member_history" SET "login" = NULL 1.135 + WHERE "member_id" = "member_id_p"; 1.136 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 1.137 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 1.138 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 1.139 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 1.140 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 1.141 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 1.142 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 1.143 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 1.144 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 1.145 + RETURN; 1.146 + END; 1.147 + $$; 1.148 + 1.149 +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.150 + 1.151 + 1.152 +CREATE FUNCTION "delete_private_data"() 1.153 + RETURNS VOID 1.154 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.155 + BEGIN 1.156 + PERFORM "delete_member_data"("id") FROM "member"; 1.157 DELETE FROM "invite_code"; 1.158 - DELETE FROM "setting"; 1.159 - DELETE FROM "setting_map"; 1.160 - DELETE FROM "member_relation_setting"; 1.161 - DELETE FROM "member_image"; 1.162 - DELETE FROM "contact"; 1.163 DELETE FROM "session"; 1.164 - DELETE FROM "area_setting"; 1.165 - DELETE FROM "issue_setting"; 1.166 - DELETE FROM "initiative_setting"; 1.167 - DELETE FROM "suggestion_setting"; 1.168 DELETE FROM "direct_voter" USING "issue" 1.169 WHERE "direct_voter"."issue_id" = "issue"."id" 1.170 AND "issue"."closed" ISNULL;
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.beta26-v1.0.0.sql Thu Apr 15 18:47:07 2010 +0200 2.3 @@ -0,0 +1,142 @@ 2.4 +BEGIN; 2.5 + 2.6 +DROP VIEW "liquid_feedback_version"; 2.7 +CREATE VIEW "liquid_feedback_version" AS 2.8 + SELECT * FROM (VALUES ('1.0.0', 1, 0, 0)) 2.9 + AS "subquery"("string", "major", "minor", "revision"); 2.10 + 2.11 +ALTER TABLE "member" ALTER COLUMN "login" DROP NOT NULL; 2.12 + 2.13 +ALTER TABLE "member_history" ALTER COLUMN "login" DROP NOT NULL; 2.14 + 2.15 +CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id"); 2.16 + 2.17 +ALTER TABLE "direct_population_snapshot" DROP 2.18 + CONSTRAINT "direct_population_snapshot_member_id_fkey"; 2.19 +ALTER TABLE "direct_population_snapshot" ADD 2.20 + CONSTRAINT "direct_population_snapshot_member_id_fkey" 2.21 + FOREIGN KEY ("member_id") 2.22 + REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT; 2.23 + 2.24 +ALTER TABLE "delegating_population_snapshot" DROP 2.25 + CONSTRAINT "delegating_population_snapshot_member_id_fkey"; 2.26 +ALTER TABLE "delegating_population_snapshot" ADD 2.27 + CONSTRAINT "delegating_population_snapshot_member_id_fkey" 2.28 + FOREIGN KEY ("member_id") 2.29 + REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT; 2.30 + 2.31 +ALTER TABLE "direct_interest_snapshot" DROP 2.32 + CONSTRAINT "direct_interest_snapshot_member_id_fkey"; 2.33 +ALTER TABLE "direct_interest_snapshot" ADD 2.34 + CONSTRAINT "direct_interest_snapshot_member_id_fkey" 2.35 + FOREIGN KEY ("member_id") 2.36 + REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT; 2.37 + 2.38 +ALTER TABLE "delegating_interest_snapshot" DROP 2.39 + CONSTRAINT "delegating_interest_snapshot_member_id_fkey"; 2.40 +ALTER TABLE "delegating_interest_snapshot" ADD 2.41 + CONSTRAINT "delegating_interest_snapshot_member_id_fkey" 2.42 + FOREIGN KEY ("member_id") 2.43 + REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT; 2.44 + 2.45 +ALTER TABLE "direct_supporter_snapshot" DROP 2.46 + CONSTRAINT "direct_supporter_snapshot_member_id_fkey"; 2.47 +ALTER TABLE "direct_supporter_snapshot" ADD 2.48 + CONSTRAINT "direct_supporter_snapshot_member_id_fkey" 2.49 + FOREIGN KEY ("member_id") 2.50 + REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT; 2.51 + 2.52 +ALTER TABLE "direct_voter" DROP 2.53 + CONSTRAINT "direct_voter_member_id_fkey"; 2.54 +ALTER TABLE "direct_voter" ADD 2.55 + CONSTRAINT "direct_voter_member_id_fkey" 2.56 + FOREIGN KEY ("member_id") 2.57 + REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT; 2.58 + 2.59 +ALTER TABLE "delegating_voter" DROP 2.60 + CONSTRAINT "delegating_voter_member_id_fkey"; 2.61 +ALTER TABLE "delegating_voter" ADD 2.62 + CONSTRAINT "delegating_voter_member_id_fkey" 2.63 + FOREIGN KEY ("member_id") 2.64 + REFERENCES "member"("id") ON DELETE RESTRICT ON UPDATE RESTRICT; 2.65 + 2.66 +CREATE OR REPLACE FUNCTION "write_member_history_trigger"() 2.67 + RETURNS TRIGGER 2.68 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.69 + BEGIN 2.70 + IF 2.71 + ( NEW."login" NOTNULL AND OLD."login" NOTNULL AND 2.72 + NEW."login" != OLD."login" ) OR 2.73 + ( NEW."login" NOTNULL AND OLD."login" ISNULL ) OR 2.74 + ( NEW."login" ISNULL AND OLD."login" NOTNULL ) OR 2.75 + NEW."active" != OLD."active" OR 2.76 + NEW."name" != OLD."name" 2.77 + THEN 2.78 + INSERT INTO "member_history" 2.79 + ("member_id", "login", "active", "name") 2.80 + VALUES (NEW."id", OLD."login", OLD."active", OLD."name"); 2.81 + END IF; 2.82 + RETURN NULL; 2.83 + END; 2.84 + $$; 2.85 + 2.86 +CREATE FUNCTION "delete_member_data"("member_id_p" "member"."id"%TYPE) 2.87 + RETURNS VOID 2.88 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.89 + BEGIN 2.90 + UPDATE "member" SET 2.91 + "login" = NULL, 2.92 + "password" = NULL, 2.93 + "notify_email" = NULL, 2.94 + "notify_email_unconfirmed" = NULL, 2.95 + "notify_email_secret" = NULL, 2.96 + "notify_email_secret_expiry" = NULL, 2.97 + "password_reset_secret" = NULL, 2.98 + "password_reset_secret_expiry" = NULL, 2.99 + "organizational_unit" = NULL, 2.100 + "internal_posts" = NULL, 2.101 + "realname" = NULL, 2.102 + "birthday" = NULL, 2.103 + "address" = NULL, 2.104 + "email" = NULL, 2.105 + "xmpp_address" = NULL, 2.106 + "website" = NULL, 2.107 + "phone" = NULL, 2.108 + "mobile_phone" = NULL, 2.109 + "profession" = NULL, 2.110 + "external_memberships" = NULL, 2.111 + "external_posts" = NULL, 2.112 + "statement" = NULL 2.113 + WHERE "id" = "member_id_p"; 2.114 + -- "text_search_data" is updated by triggers 2.115 + UPDATE "member_history" SET "login" = NULL 2.116 + WHERE "member_id" = "member_id_p"; 2.117 + DELETE FROM "setting" WHERE "member_id" = "member_id_p"; 2.118 + DELETE FROM "setting_map" WHERE "member_id" = "member_id_p"; 2.119 + DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p"; 2.120 + DELETE FROM "member_image" WHERE "member_id" = "member_id_p"; 2.121 + DELETE FROM "contact" WHERE "member_id" = "member_id_p"; 2.122 + DELETE FROM "area_setting" WHERE "member_id" = "member_id_p"; 2.123 + DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p"; 2.124 + DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p"; 2.125 + DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p"; 2.126 + RETURN; 2.127 + END; 2.128 + $$; 2.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)'; 2.130 + 2.131 +CREATE OR REPLACE FUNCTION "delete_private_data"() 2.132 + RETURNS VOID 2.133 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.134 + BEGIN 2.135 + PERFORM "delete_member_data"("id") FROM "member"; 2.136 + DELETE FROM "invite_code"; 2.137 + DELETE FROM "session"; 2.138 + DELETE FROM "direct_voter" USING "issue" 2.139 + WHERE "direct_voter"."issue_id" = "issue"."id" 2.140 + AND "issue"."closed" ISNULL; 2.141 + RETURN; 2.142 + END; 2.143 + $$; 2.144 + 2.145 +COMMIT;