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,