# HG changeset patch
# User jbe
# Date 1262602800 -3600
# Node ID fd9295e23be445e3636f4b9aea9c273ae75094dc
# Parent  a67c1cd4facfbc32d7953d8c24060b207c685f65
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

diff -r a67c1cd4facf -r fd9295e23be4 core.sql
--- a/core.sql	Sat Jan 02 12:00:00 2010 +0100
+++ b/core.sql	Mon Jan 04 12:00:00 2010 +0100
@@ -6,7 +6,7 @@
 BEGIN;
 
 CREATE VIEW "liquid_feedback_version" AS
-  SELECT * FROM (VALUES ('beta13', NULL, NULL, NULL))
+  SELECT * FROM (VALUES ('beta14', NULL, NULL, NULL))
   AS "subquery"("string", "major", "minor", "revision");
 
 
@@ -55,6 +55,7 @@
 
 CREATE TABLE "member" (
         "id"                    SERIAL4         PRIMARY KEY,
+        "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
         "login"                 TEXT            NOT NULL UNIQUE,
         "password"              TEXT,
         "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
@@ -112,6 +113,19 @@
 COMMENT ON COLUMN "member"."statement"            IS 'Freely chosen text of the member for his homepage within the system';
 
 
+CREATE TABLE "member_history" (
+        "id"                    SERIAL8         PRIMARY KEY,
+        "member_id"             INT4            NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
+        "until"                 TIMESTAMPTZ     NOT NULL DEFAULT now(),
+        "login"                 TEXT            NOT NULL,
+        "name"                  TEXT            NOT NULL );
+
+COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and login names of members';
+
+COMMENT ON COLUMN "member_history"."id"    IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
+COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid';
+
+
 CREATE TABLE "invite_code" (
         "code"                  TEXT            PRIMARY KEY,
         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
@@ -682,6 +696,31 @@
 
 
 
+--------------------------------
+-- Writing of history entries --
+--------------------------------
+
+CREATE FUNCTION "write_member_history_trigger"()
+  RETURNS TRIGGER
+  LANGUAGE 'plpgsql' VOLATILE AS $$
+    BEGIN
+      IF NEW."login" != OLD."login" OR NEW."name" != OLD."name" THEN
+        INSERT INTO "member_history" ("member_id", "login", "name")
+          VALUES (NEW."id", OLD."login", OLD."name");
+      END IF;
+      RETURN NULL;
+    END;
+  $$;
+
+CREATE TRIGGER "write_member_history"
+  AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
+  "write_member_history_trigger"();
+
+COMMENT ON FUNCTION "write_member_history_trigger"()  IS 'Implementation of trigger "write_member_history" on table "member"';
+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';
+
+
+
 ----------------------------
 -- Additional constraints --
 ----------------------------
@@ -2774,7 +2813,7 @@
       -- "text_search_data" is updated by triggers
       DELETE FROM "session";
       DELETE FROM "invite_code";
-      DELETE FROM "contact" WHERE NOT "public";
+      DELETE FROM "contact";
       DELETE FROM "setting";
       DELETE FROM "member_image";
       DELETE FROM "direct_voter" USING "issue"