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";

Impressum / About Us