liquid_feedback_core

changeset 544:ff2c21f883ce

New tables "member_settings" and "member_useterms"; Make column "profile" of "member_profile" a NOT NULL column (with DEFAULT)
author jbe
date Fri Jul 14 21:02:54 2017 +0200 (2017-07-14)
parents 4e0ee8416bbc
children 9c433d24ed00
files core.sql update/core-update.v3.2.2-v4.0.0.sql
line diff
     1.1 --- a/core.sql	Fri Jul 14 20:46:31 2017 +0200
     1.2 +++ b/core.sql	Fri Jul 14 21:02:54 2017 +0200
     1.3 @@ -203,11 +203,29 @@
     1.4  COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
     1.5  
     1.6  
     1.7 +CREATE TABLE "member_settings" (
     1.8 +        "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
     1.9 +        "settings"              JSONB           NOT NULL CHECK (jsonb_typeof("settings") = 'object') );
    1.10 +
    1.11 +COMMENT ON TABLE "member_settings" IS 'Stores a JSON document for each member containing optional (additional) settings for the respective member';
    1.12 +
    1.13 +
    1.14 +CREATE TABLE "member_useterms" (
    1.15 +        "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.16 +        "accepted"              TIMESTAMPTZ     NOT NULL,
    1.17 +        "contract_identifier"   TEXT            NOT NULL );
    1.18 +
    1.19 +COMMENT ON TABLE "member_useterms" IS 'Keeps record of accepted terms of use; may contain multiple rows per member';
    1.20 +
    1.21 +COMMENT ON COLUMN "member_useterms"."accepted"            IS 'Point in time when user accepted the terms of use';
    1.22 +COMMENT ON COLUMN "member_useterms"."contract_identifier" IS 'String identifier to denote the accepted terms of use, including their version or revision';
    1.23 +
    1.24 +
    1.25  CREATE TABLE "member_profile" (
    1.26          "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.27          "formatting_engine"     TEXT,
    1.28          "statement"             TEXT,
    1.29 -        "profile"               JSONB,
    1.30 +        "profile"               JSONB           NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("profile") = 'object'),
    1.31          "profile_text_data"     TEXT,
    1.32          "text_search_data"      TSVECTOR );
    1.33  CREATE INDEX "member_profile_text_search_data_idx" ON "member_profile" USING gin ("text_search_data");
    1.34 @@ -6227,6 +6245,7 @@
    1.35          "location"                     = NULL
    1.36          WHERE "id" = "member_id_p";
    1.37        -- "text_search_data" is updated by triggers
    1.38 +      DELETE FROM "member_settings"    WHERE "member_id" = "member_id_p";
    1.39        DELETE FROM "member_profile"     WHERE "member_id" = "member_id_p";
    1.40        DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
    1.41        DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
    1.42 @@ -6287,6 +6306,8 @@
    1.43          "password_reset_secret_expiry" = NULL,
    1.44          "location"                     = NULL;
    1.45        -- "text_search_data" is updated by triggers
    1.46 +      DELETE FROM "member_settings";
    1.47 +      DELETE FROM "member_useterms";
    1.48        DELETE FROM "member_profile";
    1.49        DELETE FROM "rendered_member_statement";
    1.50        DELETE FROM "member_image";
     2.1 --- a/update/core-update.v3.2.2-v4.0.0.sql	Fri Jul 14 20:46:31 2017 +0200
     2.2 +++ b/update/core-update.v3.2.2-v4.0.0.sql	Fri Jul 14 21:02:54 2017 +0200
     2.3 @@ -36,11 +36,29 @@
     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 "member_settings" (
     2.8 +        "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
     2.9 +        "settings"              JSONB           NOT NULL CHECK (jsonb_typeof("settings") = 'object') );
    2.10 +
    2.11 +COMMENT ON TABLE "member_settings" IS 'Stores a JSON document for each member containing optional (additional) settings for the respective member';
    2.12 +
    2.13 +
    2.14 +CREATE TABLE "member_useterms" (
    2.15 +        "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.16 +        "accepted"              TIMESTAMPTZ     NOT NULL,
    2.17 +        "contract_identifier"   TEXT            NOT NULL );
    2.18 +
    2.19 +COMMENT ON TABLE "member_useterms" IS 'Keeps record of accepted terms of use; may contain multiple rows per member';
    2.20 +
    2.21 +COMMENT ON COLUMN "member_useterms"."accepted"            IS 'Point in time when user accepted the terms of use';
    2.22 +COMMENT ON COLUMN "member_useterms"."contract_identifier" IS 'String identifier to denote the accepted terms of use, including their version or revision';
    2.23 +
    2.24 +
    2.25  CREATE TABLE "member_profile" (
    2.26          "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.27          "formatting_engine"     TEXT,
    2.28          "statement"             TEXT,
    2.29 -        "profile"               JSONB,
    2.30 +        "profile"               JSONB           NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("profile") = 'object'),
    2.31          "profile_text_data"     TEXT,
    2.32          "text_search_data"      TSVECTOR );
    2.33  CREATE INDEX "member_profile_text_search_data_idx" ON "member_profile" USING gin ("text_search_data");
    2.34 @@ -3243,6 +3261,7 @@
    2.35          "location"                     = NULL
    2.36          WHERE "id" = "member_id_p";
    2.37        -- "text_search_data" is updated by triggers
    2.38 +      DELETE FROM "member_settings"    WHERE "member_id" = "member_id_p";
    2.39        DELETE FROM "member_profile"     WHERE "member_id" = "member_id_p";
    2.40        DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
    2.41        DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
    2.42 @@ -3301,6 +3320,8 @@
    2.43          "password_reset_secret_expiry" = NULL,
    2.44          "location"                     = NULL;
    2.45        -- "text_search_data" is updated by triggers
    2.46 +      DELETE FROM "member_settings";
    2.47 +      DELETE FROM "member_useterms";
    2.48        DELETE FROM "member_profile";
    2.49        DELETE FROM "rendered_member_statement";
    2.50        DELETE FROM "member_image";

Impressum / About Us