liquid_feedback_core
diff core.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 | 0dfc5e164b52 |
children | 74c985baf082 |
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;