liquid_feedback_core
changeset 560:318827ecd041
Changes to "verification" table
author | jbe |
---|---|
date | Thu Sep 21 17:45:13 2017 +0200 (2017-09-21) |
parents | 71f431fb78d4 |
children | fb2663ca1e6b |
files | core.sql update/core-update.v3.2.2-v4.0.0.sql |
line diff
1.1 --- a/core.sql Wed Sep 20 18:07:42 2017 +0200 1.2 +++ b/core.sql Thu Sep 21 17:45:13 2017 +0200 1.3 @@ -212,31 +212,34 @@ 1.4 "requested" TIMESTAMPTZ, 1.5 "request_origin" JSONB, 1.6 "request_data" JSONB, 1.7 + "requesting_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.8 + "verifying_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.9 + "verified_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.10 "verified" TIMESTAMPTZ, 1.11 - "verification_origin" JSONB, 1.12 "verification_data" JSONB, 1.13 "denied" TIMESTAMPTZ, 1.14 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 1.15 "comment" TEXT, 1.16 CONSTRAINT "verified_and_denied_conflict" CHECK ( 1.17 "verified" ISNULL OR "denied" ISNULL ) ); 1.18 CREATE INDEX "verification_requested_idx" ON "verification" ("requested"); 1.19 CREATE INDEX "verification_open_request_idx" ON "verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL; 1.20 +CREATE INDEX "verification_requesting_member_id_idx" ON "verification" ("requesting_member_id"); 1.21 +CREATE INDEX "verification_verified_member_id_idx" ON "verification" ("verified_member_id"); 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 +COMMENT ON COLUMN "verification"."requested" IS 'Timestamp when request for verification has been submitted'; 1.38 +COMMENT ON COLUMN "verification"."request_origin" IS 'JSON data containing information about the origin of the request (e.g. IP address or hostname)'; 1.39 +COMMENT ON COLUMN "verification"."request_data" IS 'JSON data containing information about the entity to be verified (e.g. real name, address, etc.)'; 1.40 +COMMENT ON COLUMN "verification"."requesting_member_id" IS 'Member who requested verification'; 1.41 +COMMENT ON COLUMN "verification"."verifying_member_id" IS 'Member who processed the verification request (i.e. who accepted or denied the request)'; 1.42 +COMMENT ON COLUMN "verification"."verified_member_id" IS 'Member entry containing verified information (not necessarily equal to "requesting_member_id" but may be merged with requesting member later)'; 1.43 +COMMENT ON COLUMN "verification"."verified" IS 'Timestamp when request for verification has been accepted by authority'; 1.44 +COMMENT ON COLUMN "verification"."verification_data" IS 'JSON data containing additional verified data or information about the authority or operator who accepted or denied the request, but all public information shall be copied to "member"."identification", "member"."verification" and/or "member"."name" if applicable for setup'; 1.45 +COMMENT ON COLUMN "verification"."denied" IS 'Timestamp when request for verification has been denied by authority'; 1.46 +COMMENT ON COLUMN "verification"."comment" IS 'Administrative comment'; 1.47 1.48 1.49 CREATE TABLE "member_settings" (
2.1 --- a/update/core-update.v3.2.2-v4.0.0.sql Wed Sep 20 18:07:42 2017 +0200 2.2 +++ b/update/core-update.v3.2.2-v4.0.0.sql Thu Sep 21 17:45:13 2017 +0200 2.3 @@ -38,31 +38,34 @@ 2.4 "requested" TIMESTAMPTZ, 2.5 "request_origin" JSONB, 2.6 "request_data" JSONB, 2.7 + "requesting_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 2.8 + "verifying_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 2.9 + "verified_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 2.10 "verified" TIMESTAMPTZ, 2.11 - "verification_origin" JSONB, 2.12 "verification_data" JSONB, 2.13 "denied" TIMESTAMPTZ, 2.14 - "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, 2.15 "comment" TEXT, 2.16 CONSTRAINT "verified_and_denied_conflict" CHECK ( 2.17 "verified" ISNULL OR "denied" ISNULL ) ); 2.18 CREATE INDEX "verification_requested_idx" ON "verification" ("requested"); 2.19 CREATE INDEX "verification_open_request_idx" ON "verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL; 2.20 +CREATE INDEX "verification_requesting_member_id_idx" ON "verification" ("requesting_member_id"); 2.21 +CREATE INDEX "verification_verified_member_id_idx" ON "verification" ("verified_member_id"); 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 +COMMENT ON COLUMN "verification"."requested" IS 'Timestamp when request for verification has been submitted'; 2.38 +COMMENT ON COLUMN "verification"."request_origin" IS 'JSON data containing information about the origin of the request (e.g. IP address or hostname)'; 2.39 +COMMENT ON COLUMN "verification"."request_data" IS 'JSON data containing information about the entity to be verified (e.g. real name, address, etc.)'; 2.40 +COMMENT ON COLUMN "verification"."requesting_member_id" IS 'Member who requested verification'; 2.41 +COMMENT ON COLUMN "verification"."verifying_member_id" IS 'Member who processed the verification request (i.e. who accepted or denied the request)'; 2.42 +COMMENT ON COLUMN "verification"."verified_member_id" IS 'Member entry containing verified information (not necessarily equal to "requesting_member_id" but may be merged with requesting member later)'; 2.43 +COMMENT ON COLUMN "verification"."verified" IS 'Timestamp when request for verification has been accepted by authority'; 2.44 +COMMENT ON COLUMN "verification"."verification_data" IS 'JSON data containing additional verified data or information about the authority or operator who accepted or denied the request, but all public information shall be copied to "member"."identification", "member"."verification" and/or "member"."name" if applicable for setup'; 2.45 +COMMENT ON COLUMN "verification"."denied" IS 'Timestamp when request for verification has been denied by authority'; 2.46 +COMMENT ON COLUMN "verification"."comment" IS 'Administrative comment'; 2.47 2.48 2.49 ALTER TABLE "member" ADD COLUMN "deleted" TIMESTAMPTZ;