liquid_feedback_core
changeset 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 |
files | core.sql update/core-update.v3.2.2-v4.0.0.sql |
line diff
1.1 --- a/core.sql Sun Sep 17 13:19:37 2017 +0200 1.2 +++ b/core.sql Mon Sep 18 01:44:32 2017 +0200 1.3 @@ -207,6 +207,38 @@ 1.4 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid'; 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 CREATE TABLE "member_settings" ( 1.40 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 1.41 "settings" JSONB NOT NULL CHECK (jsonb_typeof("settings") = 'object') ); 1.42 @@ -6336,6 +6368,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";
2.1 --- a/update/core-update.v3.2.2-v4.0.0.sql Sun Sep 17 13:19:37 2017 +0200 2.2 +++ b/update/core-update.v3.2.2-v4.0.0.sql Mon Sep 18 01:44:32 2017 +0200 2.3 @@ -33,6 +33,38 @@ 2.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.'; 2.5 2.6 2.7 +CREATE TABLE "verification" ( 2.8 + "id" SERIAL8 PRIMARY KEY, 2.9 + "requested" TIMESTAMPTZ, 2.10 + "request_origin" JSONB, 2.11 + "request_data" JSONB, 2.12 + "verified" TIMESTAMPTZ, 2.13 + "verification_origin" JSONB, 2.14 + "verification_data" JSONB, 2.15 + "denied" TIMESTAMPTZ, 2.16 + "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 2.17 + "comment" TEXT, 2.18 + CONSTRAINT "verified_and_denied_conflict" CHECK ( 2.19 + "verified" ISNULL OR "denied" ISNULL ) ); 2.20 +CREATE INDEX "verification_requested_idx" ON "verification" ("requested"); 2.21 +CREATE INDEX "verification_open_request_idx" ON "verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL; 2.22 +CREATE INDEX "verification_verified_idx" ON "verification" ("verified"); 2.23 +CREATE INDEX "verification_denied_idx" ON "verification" ("denied"); 2.24 +CREATE INDEX "verification_member_id_idx" ON "verification" ("member_id"); 2.25 + 2.26 +COMMENT ON TABLE "verification" IS 'Request to verify a participant'; 2.27 + 2.28 +COMMENT ON COLUMN "verification"."requested" IS 'Timestamp when request for verification has been submitted'; 2.29 +COMMENT ON COLUMN "verification"."request_origin" IS 'JSON data containing information about the origin of the request (e.g. IP address or hostname)'; 2.30 +COMMENT ON COLUMN "verification"."request_data" IS 'JSON data containing information about the entity to be verified (e.g. real name, address, etc.)'; 2.31 +COMMENT ON COLUMN "verification"."verified" IS 'Timestamp when request for verification has been accepted by authority'; 2.32 +COMMENT ON COLUMN "verification"."verification_origin" IS 'JSON data containing information about the authority or operator who accepted or denied the request'; 2.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'; 2.34 +COMMENT ON COLUMN "verification"."denied" IS 'Timestamp when request for verification has been denied by authority'; 2.35 +COMMENT ON COLUMN "verification"."member_id" IS 'Timestamp when request for verification has been denied by authority'; 2.36 +COMMENT ON COLUMN "verification"."comment" IS 'Administrative comment'; 2.37 + 2.38 + 2.39 ALTER TABLE "member" ADD COLUMN "deleted" TIMESTAMPTZ; 2.40 ALTER TABLE "member" ADD CONSTRAINT "deleted_requires_locked" 2.41 CHECK ("deleted" ISNULL OR "locked" = TRUE); 2.42 @@ -3364,6 +3396,7 @@ 2.43 "password_reset_secret_expiry" = NULL, 2.44 "location" = NULL; 2.45 -- "text_search_data" is updated by triggers 2.46 + DELETE FROM "verification"; 2.47 DELETE FROM "member_settings"; 2.48 DELETE FROM "member_useterms"; 2.49 DELETE FROM "member_profile";