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