# HG changeset patch
# User jbe
# Date 1500058974 -7200
# Node ID ff2c21f883ce2f29adb8c43a8884b378a9a01a66
# Parent  4e0ee8416bbc4f673a9ac1f8311b32ee20be375e
New tables "member_settings" and "member_useterms"; Make column "profile" of "member_profile" a NOT NULL column (with DEFAULT)

diff -r 4e0ee8416bbc -r ff2c21f883ce core.sql
--- a/core.sql	Fri Jul 14 20:46:31 2017 +0200
+++ b/core.sql	Fri Jul 14 21:02:54 2017 +0200
@@ -203,11 +203,29 @@
 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
 
 
+CREATE TABLE "member_settings" (
+        "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
+        "settings"              JSONB           NOT NULL CHECK (jsonb_typeof("settings") = 'object') );
+
+COMMENT ON TABLE "member_settings" IS 'Stores a JSON document for each member containing optional (additional) settings for the respective member';
+
+
+CREATE TABLE "member_useterms" (
+        "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
+        "accepted"              TIMESTAMPTZ     NOT NULL,
+        "contract_identifier"   TEXT            NOT NULL );
+
+COMMENT ON TABLE "member_useterms" IS 'Keeps record of accepted terms of use; may contain multiple rows per member';
+
+COMMENT ON COLUMN "member_useterms"."accepted"            IS 'Point in time when user accepted the terms of use';
+COMMENT ON COLUMN "member_useterms"."contract_identifier" IS 'String identifier to denote the accepted terms of use, including their version or revision';
+
+
 CREATE TABLE "member_profile" (
         "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
         "formatting_engine"     TEXT,
         "statement"             TEXT,
-        "profile"               JSONB,
+        "profile"               JSONB           NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("profile") = 'object'),
         "profile_text_data"     TEXT,
         "text_search_data"      TSVECTOR );
 CREATE INDEX "member_profile_text_search_data_idx" ON "member_profile" USING gin ("text_search_data");
@@ -6227,6 +6245,7 @@
         "location"                     = NULL
         WHERE "id" = "member_id_p";
       -- "text_search_data" is updated by triggers
+      DELETE FROM "member_settings"    WHERE "member_id" = "member_id_p";
       DELETE FROM "member_profile"     WHERE "member_id" = "member_id_p";
       DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
       DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
@@ -6287,6 +6306,8 @@
         "password_reset_secret_expiry" = NULL,
         "location"                     = NULL;
       -- "text_search_data" is updated by triggers
+      DELETE FROM "member_settings";
+      DELETE FROM "member_useterms";
       DELETE FROM "member_profile";
       DELETE FROM "rendered_member_statement";
       DELETE FROM "member_image";
diff -r 4e0ee8416bbc -r ff2c21f883ce update/core-update.v3.2.2-v4.0.0.sql
--- a/update/core-update.v3.2.2-v4.0.0.sql	Fri Jul 14 20:46:31 2017 +0200
+++ b/update/core-update.v3.2.2-v4.0.0.sql	Fri Jul 14 21:02:54 2017 +0200
@@ -36,11 +36,29 @@
 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.';
  
  
+CREATE TABLE "member_settings" (
+        "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
+        "settings"              JSONB           NOT NULL CHECK (jsonb_typeof("settings") = 'object') );
+
+COMMENT ON TABLE "member_settings" IS 'Stores a JSON document for each member containing optional (additional) settings for the respective member';
+
+
+CREATE TABLE "member_useterms" (
+        "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
+        "accepted"              TIMESTAMPTZ     NOT NULL,
+        "contract_identifier"   TEXT            NOT NULL );
+
+COMMENT ON TABLE "member_useterms" IS 'Keeps record of accepted terms of use; may contain multiple rows per member';
+
+COMMENT ON COLUMN "member_useterms"."accepted"            IS 'Point in time when user accepted the terms of use';
+COMMENT ON COLUMN "member_useterms"."contract_identifier" IS 'String identifier to denote the accepted terms of use, including their version or revision';
+
+
 CREATE TABLE "member_profile" (
         "member_id"             INT4            PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
         "formatting_engine"     TEXT,
         "statement"             TEXT,
-        "profile"               JSONB,
+        "profile"               JSONB           NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("profile") = 'object'),
         "profile_text_data"     TEXT,
         "text_search_data"      TSVECTOR );
 CREATE INDEX "member_profile_text_search_data_idx" ON "member_profile" USING gin ("text_search_data");
@@ -3243,6 +3261,7 @@
         "location"                     = NULL
         WHERE "id" = "member_id_p";
       -- "text_search_data" is updated by triggers
+      DELETE FROM "member_settings"    WHERE "member_id" = "member_id_p";
       DELETE FROM "member_profile"     WHERE "member_id" = "member_id_p";
       DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
       DELETE FROM "member_image"       WHERE "member_id" = "member_id_p";
@@ -3301,6 +3320,8 @@
         "password_reset_secret_expiry" = NULL,
         "location"                     = NULL;
       -- "text_search_data" is updated by triggers
+      DELETE FROM "member_settings";
+      DELETE FROM "member_useterms";
       DELETE FROM "member_profile";
       DELETE FROM "rendered_member_statement";
       DELETE FROM "member_image";