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
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"

Impressum / About Us