liquid_feedback_core
changeset 13:fd9295e23be4 beta14
Version beta14
Function delete_private_data() deletes now all member contacts, including private ones, to protect users privacy when database dumps are published
New table member_history logging changes of names and logins
Function delete_private_data() deletes now all member contacts, including private ones, to protect users privacy when database dumps are published
New table member_history logging changes of names and logins
author | jbe |
---|---|
date | Mon Jan 04 12:00:00 2010 +0100 (2010-01-04) |
parents | a67c1cd4facf |
children | ac7836ac00d9 |
files | core.sql |
line diff
1.1 --- a/core.sql Sat Jan 02 12:00:00 2010 +0100 1.2 +++ b/core.sql Mon Jan 04 12:00:00 2010 +0100 1.3 @@ -6,7 +6,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('beta13', NULL, NULL, NULL)) 1.8 + SELECT * FROM (VALUES ('beta14', NULL, NULL, NULL)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -55,6 +55,7 @@ 1.13 1.14 CREATE TABLE "member" ( 1.15 "id" SERIAL4 PRIMARY KEY, 1.16 + "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.17 "login" TEXT NOT NULL UNIQUE, 1.18 "password" TEXT, 1.19 "active" BOOLEAN NOT NULL DEFAULT TRUE, 1.20 @@ -112,6 +113,19 @@ 1.21 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system'; 1.22 1.23 1.24 +CREATE TABLE "member_history" ( 1.25 + "id" SERIAL8 PRIMARY KEY, 1.26 + "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.27 + "until" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.28 + "login" TEXT NOT NULL, 1.29 + "name" TEXT NOT NULL ); 1.30 + 1.31 +COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and login names of members'; 1.32 + 1.33 +COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)'; 1.34 +COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid'; 1.35 + 1.36 + 1.37 CREATE TABLE "invite_code" ( 1.38 "code" TEXT PRIMARY KEY, 1.39 "created" TIMESTAMPTZ NOT NULL DEFAULT now(), 1.40 @@ -682,6 +696,31 @@ 1.41 1.42 1.43 1.44 +-------------------------------- 1.45 +-- Writing of history entries -- 1.46 +-------------------------------- 1.47 + 1.48 +CREATE FUNCTION "write_member_history_trigger"() 1.49 + RETURNS TRIGGER 1.50 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.51 + BEGIN 1.52 + IF NEW."login" != OLD."login" OR NEW."name" != OLD."name" THEN 1.53 + INSERT INTO "member_history" ("member_id", "login", "name") 1.54 + VALUES (NEW."id", OLD."login", OLD."name"); 1.55 + END IF; 1.56 + RETURN NULL; 1.57 + END; 1.58 + $$; 1.59 + 1.60 +CREATE TRIGGER "write_member_history" 1.61 + AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE 1.62 + "write_member_history_trigger"(); 1.63 + 1.64 +COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"'; 1.65 +COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing name or login of a member, create a history entry in "member_history" table'; 1.66 + 1.67 + 1.68 + 1.69 ---------------------------- 1.70 -- Additional constraints -- 1.71 ---------------------------- 1.72 @@ -2774,7 +2813,7 @@ 1.73 -- "text_search_data" is updated by triggers 1.74 DELETE FROM "session"; 1.75 DELETE FROM "invite_code"; 1.76 - DELETE FROM "contact" WHERE NOT "public"; 1.77 + DELETE FROM "contact"; 1.78 DELETE FROM "setting"; 1.79 DELETE FROM "member_image"; 1.80 DELETE FROM "direct_voter" USING "issue"