liquid_feedback_core

changeset 552:a676d305502f

Added column "member"."deleted" as TIMESTAMPTZ
author jbe
date Sat Sep 16 19:58:27 2017 +0200 (2017-09-16)
parents deabd90adae8
children 85f25c413238
files core.sql update/core-update.v3.2.2-v4.0.0.sql
line diff
     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,
     2.1 --- a/update/core-update.v3.2.2-v4.0.0.sql	Sat Sep 16 15:53:31 2017 +0200
     2.2 +++ b/update/core-update.v3.2.2-v4.0.0.sql	Sat Sep 16 19:58:27 2017 +0200
     2.3 @@ -31,7 +31,14 @@
     2.4  ALTER TABLE "system_setting" ADD COLUMN "snapshot_retention" INTERVAL;
     2.5  
     2.6  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.';
     2.7 +
     2.8 +
     2.9 +ALTER TABLE "member" ADD COLUMN "deleted" TIMESTAMPTZ;
    2.10 +ALTER TABLE "member" ADD CONSTRAINT "deleted_requires_locked"
    2.11 +  CHECK ("deleted" ISNULL OR "locked" = TRUE);
    2.12   
    2.13 +COMMENT ON COLUMN "member"."deleted" IS 'Timestamp of deletion (set by "delete_member" function)';
    2.14 +
    2.15   
    2.16  CREATE TABLE "member_settings" (
    2.17          "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.18 @@ -1250,7 +1257,7 @@
    2.19    LANGUAGE 'plpgsql' VOLATILE AS $$
    2.20      BEGIN
    2.21        IF TG_OP = 'INSERT' THEN
    2.22 -        IF NEW."activated" NOTNULL THEN
    2.23 +        IF NEW."activated" NOTNULL AND NEW."deleted" ISNULL THEN
    2.24            INSERT INTO "event" ("event", "member_id")
    2.25              VALUES ('member_activated', NEW."id");
    2.26          END IF;
    2.27 @@ -1262,6 +1269,18 @@
    2.28          IF OLD."id" != NEW."id" THEN
    2.29            RAISE EXCEPTION 'Cannot change member ID';
    2.30          END IF;
    2.31 +        IF
    2.32 +          (OLD."activated" ISNULL OR OLD."deleted" NOTNULL) AND
    2.33 +          NEW."activated" NOTNULL AND NEW."deleted" ISNULL
    2.34 +        THEN
    2.35 +          INSERT INTO "event" ("event", "member_id")
    2.36 +            VALUES ('member_activated', NEW."id");
    2.37 +        END IF;
    2.38 +        IF OLD."active" != NEW."active" THEN
    2.39 +          INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
    2.40 +            'member_active', NEW."id", NEW."active"
    2.41 +          );
    2.42 +        END IF;
    2.43          IF OLD."name" != NEW."name" THEN
    2.44            INSERT INTO "event" (
    2.45              "event", "member_id", "text_value", "old_text_value"
    2.46 @@ -1269,17 +1288,9 @@
    2.47              'member_name_updated', NEW."id", NEW."name", OLD."name"
    2.48            );
    2.49          END IF;
    2.50 -        IF OLD."active" != NEW."active" THEN
    2.51 -          INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
    2.52 -            'member_active', NEW."id", NEW."active"
    2.53 -          );
    2.54 -        END IF;
    2.55          IF
    2.56 -          OLD."activated" NOTNULL AND
    2.57 -          (OLD."login" NOTNULL OR OLD."authority_login" NOTNULL) AND
    2.58 -          NEW."login"           ISNULL AND
    2.59 -          NEW."authority_login" ISNULL AND
    2.60 -          NEW."locked"          = TRUE
    2.61 +          OLD."activated" NOTNULL AND OLD."deleted" ISNULL AND
    2.62 +          (NEW."activated" ISNULL OR NEW."deleted" NOTNULL)
    2.63          THEN
    2.64            INSERT INTO "event" ("event", "member_id")
    2.65              VALUES ('member_removed', NEW."id");
    2.66 @@ -3271,6 +3282,7 @@
    2.67          "authority"                    = NULL,
    2.68          "authority_uid"                = NULL,
    2.69          "authority_login"              = NULL,
    2.70 +        "deleted"                      = coalesce("deleted", now()),
    2.71          "locked"                       = TRUE,
    2.72          "active"                       = FALSE,
    2.73          "notify_email"                 = NULL,

Impressum / About Us