1.1 --- a/core.sql	Sat Sep 16 15:53:31 2017 +0200
     1.2 +++ b/core.sql	Sat Sep 16 19:58:27 2017 +0200
     1.3 @@ -93,6 +93,7 @@
     1.4  CREATE TABLE "member" (
     1.5          "id"                    SERIAL4         PRIMARY KEY,
     1.6          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
     1.7 +        "deleted"               TIMESTAMPTZ,
     1.8          "invite_code"           TEXT            UNIQUE,
     1.9          "invite_code_expiry"    TIMESTAMPTZ,
    1.10          "admin_comment"         TEXT,
    1.11 @@ -128,6 +129,8 @@
    1.12          "authentication"        TEXT,
    1.13          "location"              JSONB,
    1.14          "text_search_data"      TSVECTOR,
    1.15 +        CONSTRAINT "deleted_requires_locked"
    1.16 +          CHECK ("deleted" ISNULL OR "locked" = TRUE),
    1.17          CONSTRAINT "active_requires_activated_and_last_activity"
    1.18            CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
    1.19          CONSTRAINT "authority_requires_uid_and_vice_versa" 
    1.20 @@ -165,6 +168,7 @@
    1.21  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)';
    1.22  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)';
    1.23  COMMENT ON COLUMN "member"."authority_login"      IS 'Login name for external accounts (field is not unique!)';
    1.24 +COMMENT ON COLUMN "member"."deleted"              IS 'Timestamp of deletion (set by "delete_member" function)';
    1.25  COMMENT ON COLUMN "member"."locked"               IS 'Locked members can not log in.';
    1.26  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".';
    1.27  COMMENT ON COLUMN "member"."admin"                IS 'TRUE for admins, which can administrate other users and setup policies and areas';
    1.28 @@ -2015,7 +2019,7 @@
    1.29    LANGUAGE 'plpgsql' VOLATILE AS $$
    1.30      BEGIN
    1.31        IF TG_OP = 'INSERT' THEN
    1.32 -        IF NEW."activated" NOTNULL THEN
    1.33 +        IF NEW."activated" NOTNULL AND NEW."deleted" ISNULL THEN
    1.34            INSERT INTO "event" ("event", "member_id")
    1.35              VALUES ('member_activated', NEW."id");
    1.36          END IF;
    1.37 @@ -2027,6 +2031,18 @@
    1.38          IF OLD."id" != NEW."id" THEN
    1.39            RAISE EXCEPTION 'Cannot change member ID';
    1.40          END IF;
    1.41 +        IF
    1.42 +          (OLD."activated" ISNULL OR OLD."deleted" NOTNULL) AND
    1.43 +          NEW."activated" NOTNULL AND NEW."deleted" ISNULL
    1.44 +        THEN
    1.45 +          INSERT INTO "event" ("event", "member_id")
    1.46 +            VALUES ('member_activated', NEW."id");
    1.47 +        END IF;
    1.48 +        IF OLD."active" != NEW."active" THEN
    1.49 +          INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
    1.50 +            'member_active', NEW."id", NEW."active"
    1.51 +          );
    1.52 +        END IF;
    1.53          IF OLD."name" != NEW."name" THEN
    1.54            INSERT INTO "event" (
    1.55              "event", "member_id", "text_value", "old_text_value"
    1.56 @@ -2034,17 +2050,9 @@
    1.57              'member_name_updated', NEW."id", NEW."name", OLD."name"
    1.58            );
    1.59          END IF;
    1.60 -        IF OLD."active" != NEW."active" THEN
    1.61 -          INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
    1.62 -            'member_active', NEW."id", NEW."active"
    1.63 -          );
    1.64 -        END IF;
    1.65          IF
    1.66 -          OLD."activated" NOTNULL AND
    1.67 -          (OLD."login" NOTNULL OR OLD."authority_login" NOTNULL) AND
    1.68 -          NEW."login"           ISNULL AND
    1.69 -          NEW."authority_login" ISNULL AND
    1.70 -          NEW."locked"          = TRUE
    1.71 +          OLD."activated" NOTNULL AND OLD."deleted" ISNULL AND
    1.72 +          (NEW."activated" ISNULL OR NEW."deleted" NOTNULL)
    1.73          THEN
    1.74            INSERT INTO "event" ("event", "member_id")
    1.75              VALUES ('member_removed', NEW."id");
    1.76 @@ -6247,6 +6255,7 @@
    1.77          "authority"                    = NULL,
    1.78          "authority_uid"                = NULL,
    1.79          "authority_login"              = NULL,
    1.80 +        "deleted"                      = coalesce("deleted", now()),
    1.81          "locked"                       = TRUE,
    1.82          "active"                       = FALSE,
    1.83          "notify_email"                 = NULL,