# HG changeset patch
# User jbe
# Date 1505584707 -7200
# Node ID a676d305502fe6fb2878c021ef704651cf9fcd51
# Parent  deabd90adae8cdbcd2cf36ef4f0301ac34a5af58
Added column "member"."deleted" as TIMESTAMPTZ

diff -r deabd90adae8 -r a676d305502f core.sql
--- a/core.sql	Sat Sep 16 15:53:31 2017 +0200
+++ b/core.sql	Sat Sep 16 19:58:27 2017 +0200
@@ -93,6 +93,7 @@
 CREATE TABLE "member" (
         "id"                    SERIAL4         PRIMARY KEY,
         "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
+        "deleted"               TIMESTAMPTZ,
         "invite_code"           TEXT            UNIQUE,
         "invite_code_expiry"    TIMESTAMPTZ,
         "admin_comment"         TEXT,
@@ -128,6 +129,8 @@
         "authentication"        TEXT,
         "location"              JSONB,
         "text_search_data"      TSVECTOR,
+        CONSTRAINT "deleted_requires_locked"
+          CHECK ("deleted" ISNULL OR "locked" = TRUE),
         CONSTRAINT "active_requires_activated_and_last_activity"
           CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
         CONSTRAINT "authority_requires_uid_and_vice_versa" 
@@ -165,6 +168,7 @@
 COMMENT ON COLUMN "member"."authority"            IS 'NULL if LiquidFeedback Core is authoritative for the member account; otherwise a string that indicates the source/authority of the external account (e.g. ''LDAP'' for an LDAP account)';
 COMMENT ON COLUMN "member"."authority_uid"        IS 'Unique identifier (unique per "authority") that allows to identify an external account (e.g. even if the login name changes)';
 COMMENT ON COLUMN "member"."authority_login"      IS 'Login name for external accounts (field is not unique!)';
+COMMENT ON COLUMN "member"."deleted"              IS 'Timestamp of deletion (set by "delete_member" function)';
 COMMENT ON COLUMN "member"."locked"               IS 'Locked members can not log in.';
 COMMENT ON COLUMN "member"."active"               IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. Automatically set to FALSE, if "last_activity" is older than "system_setting"."member_ttl".';
 COMMENT ON COLUMN "member"."admin"                IS 'TRUE for admins, which can administrate other users and setup policies and areas';
@@ -2015,7 +2019,7 @@
   LANGUAGE 'plpgsql' VOLATILE AS $$
     BEGIN
       IF TG_OP = 'INSERT' THEN
-        IF NEW."activated" NOTNULL THEN
+        IF NEW."activated" NOTNULL AND NEW."deleted" ISNULL THEN
           INSERT INTO "event" ("event", "member_id")
             VALUES ('member_activated', NEW."id");
         END IF;
@@ -2027,6 +2031,18 @@
         IF OLD."id" != NEW."id" THEN
           RAISE EXCEPTION 'Cannot change member ID';
         END IF;
+        IF
+          (OLD."activated" ISNULL OR OLD."deleted" NOTNULL) AND
+          NEW."activated" NOTNULL AND NEW."deleted" ISNULL
+        THEN
+          INSERT INTO "event" ("event", "member_id")
+            VALUES ('member_activated', NEW."id");
+        END IF;
+        IF OLD."active" != NEW."active" THEN
+          INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
+            'member_active', NEW."id", NEW."active"
+          );
+        END IF;
         IF OLD."name" != NEW."name" THEN
           INSERT INTO "event" (
             "event", "member_id", "text_value", "old_text_value"
@@ -2034,17 +2050,9 @@
             'member_name_updated', NEW."id", NEW."name", OLD."name"
           );
         END IF;
-        IF OLD."active" != NEW."active" THEN
-          INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
-            'member_active', NEW."id", NEW."active"
-          );
-        END IF;
         IF
-          OLD."activated" NOTNULL AND
-          (OLD."login" NOTNULL OR OLD."authority_login" NOTNULL) AND
-          NEW."login"           ISNULL AND
-          NEW."authority_login" ISNULL AND
-          NEW."locked"          = TRUE
+          OLD."activated" NOTNULL AND OLD."deleted" ISNULL AND
+          (NEW."activated" ISNULL OR NEW."deleted" NOTNULL)
         THEN
           INSERT INTO "event" ("event", "member_id")
             VALUES ('member_removed', NEW."id");
@@ -6247,6 +6255,7 @@
         "authority"                    = NULL,
         "authority_uid"                = NULL,
         "authority_login"              = NULL,
+        "deleted"                      = coalesce("deleted", now()),
         "locked"                       = TRUE,
         "active"                       = FALSE,
         "notify_email"                 = NULL,
diff -r deabd90adae8 -r a676d305502f update/core-update.v3.2.2-v4.0.0.sql
--- a/update/core-update.v3.2.2-v4.0.0.sql	Sat Sep 16 15:53:31 2017 +0200
+++ b/update/core-update.v3.2.2-v4.0.0.sql	Sat Sep 16 19:58:27 2017 +0200
@@ -31,7 +31,14 @@
 ALTER TABLE "system_setting" ADD COLUMN "snapshot_retention" INTERVAL;
 
 COMMENT ON COLUMN "system_setting"."snapshot_retention" IS 'Unreferenced snapshots are retained for the given period of time after creation; set to NULL for infinite retention.';
+
+
+ALTER TABLE "member" ADD COLUMN "deleted" TIMESTAMPTZ;
+ALTER TABLE "member" ADD CONSTRAINT "deleted_requires_locked"
+  CHECK ("deleted" ISNULL OR "locked" = TRUE);
  
+COMMENT ON COLUMN "member"."deleted" IS 'Timestamp of deletion (set by "delete_member" function)';
+
  
 CREATE TABLE "member_settings" (
         "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
@@ -1250,7 +1257,7 @@
   LANGUAGE 'plpgsql' VOLATILE AS $$
     BEGIN
       IF TG_OP = 'INSERT' THEN
-        IF NEW."activated" NOTNULL THEN
+        IF NEW."activated" NOTNULL AND NEW."deleted" ISNULL THEN
           INSERT INTO "event" ("event", "member_id")
             VALUES ('member_activated', NEW."id");
         END IF;
@@ -1262,6 +1269,18 @@
         IF OLD."id" != NEW."id" THEN
           RAISE EXCEPTION 'Cannot change member ID';
         END IF;
+        IF
+          (OLD."activated" ISNULL OR OLD."deleted" NOTNULL) AND
+          NEW."activated" NOTNULL AND NEW."deleted" ISNULL
+        THEN
+          INSERT INTO "event" ("event", "member_id")
+            VALUES ('member_activated', NEW."id");
+        END IF;
+        IF OLD."active" != NEW."active" THEN
+          INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
+            'member_active', NEW."id", NEW."active"
+          );
+        END IF;
         IF OLD."name" != NEW."name" THEN
           INSERT INTO "event" (
             "event", "member_id", "text_value", "old_text_value"
@@ -1269,17 +1288,9 @@
             'member_name_updated', NEW."id", NEW."name", OLD."name"
           );
         END IF;
-        IF OLD."active" != NEW."active" THEN
-          INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
-            'member_active', NEW."id", NEW."active"
-          );
-        END IF;
         IF
-          OLD."activated" NOTNULL AND
-          (OLD."login" NOTNULL OR OLD."authority_login" NOTNULL) AND
-          NEW."login"           ISNULL AND
-          NEW."authority_login" ISNULL AND
-          NEW."locked"          = TRUE
+          OLD."activated" NOTNULL AND OLD."deleted" ISNULL AND
+          (NEW."activated" ISNULL OR NEW."deleted" NOTNULL)
         THEN
           INSERT INTO "event" ("event", "member_id")
             VALUES ('member_removed', NEW."id");
@@ -3271,6 +3282,7 @@
         "authority"                    = NULL,
         "authority_uid"                = NULL,
         "authority_login"              = NULL,
+        "deleted"                      = coalesce("deleted", now()),
         "locked"                       = TRUE,
         "active"                       = FALSE,
         "notify_email"                 = NULL,