liquid_feedback_core
diff update/core-update.v3.2.2-v4.0.0.sql @ 557:0fc78541dc15
Added "verification" table to store verification requests
author | jbe |
---|---|
date | Mon Sep 18 01:44:32 2017 +0200 (2017-09-18) |
parents | 3f21631a7f6d |
children | 25b551e53da2 |
line diff
1.1 --- a/update/core-update.v3.2.2-v4.0.0.sql Sun Sep 17 13:19:37 2017 +0200 1.2 +++ b/update/core-update.v3.2.2-v4.0.0.sql Mon Sep 18 01:44:32 2017 +0200 1.3 @@ -33,6 +33,38 @@ 1.4 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.'; 1.5 1.6 1.7 +CREATE TABLE "verification" ( 1.8 + "id" SERIAL8 PRIMARY KEY, 1.9 + "requested" TIMESTAMPTZ, 1.10 + "request_origin" JSONB, 1.11 + "request_data" JSONB, 1.12 + "verified" TIMESTAMPTZ, 1.13 + "verification_origin" JSONB, 1.14 + "verification_data" JSONB, 1.15 + "denied" TIMESTAMPTZ, 1.16 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.17 + "comment" TEXT, 1.18 + CONSTRAINT "verified_and_denied_conflict" CHECK ( 1.19 + "verified" ISNULL OR "denied" ISNULL ) ); 1.20 +CREATE INDEX "verification_requested_idx" ON "verification" ("requested"); 1.21 +CREATE INDEX "verification_open_request_idx" ON "verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL; 1.22 +CREATE INDEX "verification_verified_idx" ON "verification" ("verified"); 1.23 +CREATE INDEX "verification_denied_idx" ON "verification" ("denied"); 1.24 +CREATE INDEX "verification_member_id_idx" ON "verification" ("member_id"); 1.25 + 1.26 +COMMENT ON TABLE "verification" IS 'Request to verify a participant'; 1.27 + 1.28 +COMMENT ON COLUMN "verification"."requested" IS 'Timestamp when request for verification has been submitted'; 1.29 +COMMENT ON COLUMN "verification"."request_origin" IS 'JSON data containing information about the origin of the request (e.g. IP address or hostname)'; 1.30 +COMMENT ON COLUMN "verification"."request_data" IS 'JSON data containing information about the entity to be verified (e.g. real name, address, etc.)'; 1.31 +COMMENT ON COLUMN "verification"."verified" IS 'Timestamp when request for verification has been accepted by authority'; 1.32 +COMMENT ON COLUMN "verification"."verification_origin" IS 'JSON data containing information about the authority or operator who accepted or denied the request'; 1.33 +COMMENT ON COLUMN "verification"."verification_data" IS 'JSON data containing additional verified data, but all public information shall be copied to "member"."identification", "member"."verification" and/or "member"."name" if applicable for setup'; 1.34 +COMMENT ON COLUMN "verification"."denied" IS 'Timestamp when request for verification has been denied by authority'; 1.35 +COMMENT ON COLUMN "verification"."member_id" IS 'Timestamp when request for verification has been denied by authority'; 1.36 +COMMENT ON COLUMN "verification"."comment" IS 'Administrative comment'; 1.37 + 1.38 + 1.39 ALTER TABLE "member" ADD COLUMN "deleted" TIMESTAMPTZ; 1.40 ALTER TABLE "member" ADD CONSTRAINT "deleted_requires_locked" 1.41 CHECK ("deleted" ISNULL OR "locked" = TRUE); 1.42 @@ -3364,6 +3396,7 @@ 1.43 "password_reset_secret_expiry" = NULL, 1.44 "location" = NULL; 1.45 -- "text_search_data" is updated by triggers 1.46 + DELETE FROM "verification"; 1.47 DELETE FROM "member_settings"; 1.48 DELETE FROM "member_useterms"; 1.49 DELETE FROM "member_profile";