From 69e91419d708f1fdb72f4ccd181270b527051331 Mon Sep 17 00:00:00 2001
From: Bill Erickson <berickxx@gmail.com>
Date: Tue, 13 Mar 2018 14:06:23 -0400
Subject: [PATCH] LP#1750894 Unified org/usr/ws settings / WIP

Signed-off-by: Bill Erickson <berickxx@gmail.com>
---
 Open-ILS/examples/fm_IDL.xml                       |  20 ++-
 .../upgrade/XXXX.schema.workstation-settings.sql   | 140 +++++++++++++++++++++
 .../Pg/upgrade/YYYY.data.workstation-settings.sql  |  15 +++
 .../ZZZZ.schema.workstation-settings.UNDO.sql      |  15 +++
 4 files changed, 189 insertions(+), 1 deletion(-)
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.workstation-settings.sql
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/YYYY.data.workstation-settings.sql
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/ZZZZ.schema.workstation-settings.UNDO.sql

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index b272b9ffab..259ea853c8 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -12358,8 +12358,26 @@ SELECT  usr,
 	</permacrud>
         </class>
 
-	<!-- ********************************************************************************************************************* -->
+	<!-- TODO config.setting_type -->
+
+	<class id="awss" 
+		controller="open-ils.cstore open-ils.pcrud" 
+		oils_obj:fieldmapper="actor::workstation_setting" 
+		oils_persist:tablename="actor.usr_setting" 
+		reporter:label="Workstation Setting">
+		<fields oils_persist:primary="id" oils_persist:sequence="actor.workstation_setting_id_seq">
+			<field reporter:label="Setting ID" name="id" reporter:datatype="id" />
+			<field reporter:label="Name" name="name" reporter:datatype="link"/>
+			<field reporter:label="Value" name="value" reporter:datatype="text"/>
+			<field reporter:label="Workstation" name="owning_ws" reporter:datatype="link"/>
+		</fields>
+		<links>
+			<link field="name" reltype="has_a" key="name" map="" class="cwst"/>
+			<link field="workstation" reltype="has_a" key="id" map="" class="aws"/>
+		</links>
+	</class>
 
+	<!-- ********************************************************************************************************************* -->
 </IDL>
 
 <!--
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.workstation-settings.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.workstation-settings.sql
new file mode 100644
index 0000000000..fafe1f5582
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.workstation-settings.sql
@@ -0,0 +1,140 @@
+BEGIN;
+
+-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
+
+CREATE TABLE config.setting_type (
+    name            TEXT    PRIMARY KEY,
+    label           TEXT    UNIQUE NOT NULL,
+    grp             TEXT    REFERENCES config.settings_group (name),
+    description     TEXT,
+    datatype        TEXT    NOT NULL DEFAULT 'string',
+    fm_class        TEXT,
+    view_perm       INT,
+    update_perm     INT,
+
+    org_setting     BOOLEAN NOT NULL DEFAULT FALSE,
+    usr_setting     BOOLEAN NOT NULL DEFAULT FALSE,
+    ws_setting      BOOLEAN NOT NULL DEFAULT FALSE,
+
+    --
+    -- define valid datatypes
+    --
+    CONSTRAINT cst_valid_datatype CHECK ( datatype IN
+    ( 'bool', 'integer', 'float', 'currency', 'interval',
+      'date', 'string', 'object', 'array', 'link' ) ),
+    --
+    -- fm_class is meaningful only for 'link' datatype
+    --
+    CONSTRAINT cst_no_empty_link CHECK ( 
+      (datatype =  'link' AND fm_class IS NOT NULL) OR
+      (datatype <> 'link' AND fm_class IS NULL) 
+    ),
+
+    CONSTRAINT cst_storage_ops_sanity_check CHECK (
+        -- all settings require at least one storage option
+        (org_setting IS TRUE OR usr_setting IS TRUE OR ws_setting IS TRUE)
+        -- settings cannot apply to both users and workstations
+        AND (usr_setting IS FALSE OR ws_setting IS FALSE) 
+    )
+);
+
+CREATE TABLE actor.workstation_setting (
+    id          SERIAL PRIMARY KEY,
+    workstation INT    NOT NULL REFERENCES actor.workstation (id) 
+                       ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+    name        TEXT   NOT NULL REFERENCES config.setting_type (name) 
+                       ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
+    value       JSON   NOT NULL
+);
+
+
+-- move org unit settings into the new table
+INSERT INTO config.setting_type (name, label, grp, description, 
+        datatype, fm_class, view_perm, update_perm, org_setting)
+SELECT name, label, grp, description, datatype, 
+    fm_class, view_perm, update_perm, TRUE 
+FROM config.org_unit_setting_type;
+
+-- move user settings into the new table
+INSERT INTO config.setting_type 
+    (name, label, grp, description, datatype, fm_class, usr_setting)
+SELECT name, label, grp, description, datatype, fm_class, TRUE 
+FROM config.usr_setting_type;
+
+-- Move foreign keys from config.*_setting_type to config.setting_type
+-- NOTE: in the IDL, these fields will be left unchanged and will point
+-- to the DB views created below.
+ALTER TABLE actor.usr_setting DROP CONSTRAINT usr_setting_name_fkey;
+ALTER TABLE actor.org_unit_setting DROP CONSTRAINT org_unit_setting_name_fkey;
+ALTER TABLE config.org_unit_setting_type_log 
+    DROP CONSTRAINT org_unit_setting_type_log_field_name_fkey;
+ALTER TABLE action_trigger.event_definition
+    DROP CONSTRAINT event_definition_opt_in_setting_fkey;
+
+ALTER TABLE config.org_unit_setting_type_log 
+    ADD CONSTRAINT org_unit_setting_type_log_field_name_fkey
+    FOREIGN KEY (field_name) REFERENCES config.setting_type(name) 
+    DEFERRABLE INITIALLY DEFERRED;
+
+ALTER TABLE actor.org_unit_setting
+    ADD CONSTRAINT org_unit_setting_name_fkey
+    FOREIGN KEY (name) REFERENCES config.setting_type(name) 
+    DEFERRABLE INITIALLY DEFERRED;
+
+ALTER TABLE actor.usr_setting
+    ADD CONSTRAINT usr_setting_name_fkey
+    FOREIGN KEY (name) REFERENCES config.setting_type(name) 
+    ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
+
+ALTER TABLE action_trigger.event_definition
+    ADD CONSTRAINT event_definition_opt_in_setting_fkey
+    FOREIGN KEY (opt_in_setting) REFERENCES config.setting_type(name) 
+    DEFERRABLE INITIALLY DEFERRED;
+
+-- TODO: remove any PCRUD WRITE operations from these views.
+
+DROP TABLE config.org_unit_setting_type;
+DROP TABLE config.usr_setting_type;
+
+CREATE VIEW config.org_unit_setting_type AS
+    SELECT name, label, grp, description, 
+        datatype, fm_class, view_perm, update_perm, org_setting
+    FROM config.setting_type WHERE org_setting;
+
+CREATE VIEW config.usr_setting_type AS
+    SELECT name, label, grp, description, 
+        datatype, fm_class, org_setting
+    FROM config.setting_type WHERE usr_setting;
+
+CREATE OR REPLACE FUNCTION actor.setting_type_is_valid() 
+RETURNS TRIGGER AS $FUNC$
+BEGIN
+    PERFORM * FROM config.setting_type WHERE name = NEW.name AND 
+        CASE 
+            WHEN TG_TABLE_NAME = 'org_unit_setting' THEN org_setting
+            WHEN TG_TABLE_NAME = 'usr_setting' THEN usr_setting
+            WHEN TG_TABLE_NAME = 'workstation_setting' THEN ws_setting
+        END;
+    IF FOUND THEN
+        RETURN NULL;
+    END IF;
+    RAISE EXCEPTION '% is not a valid org unit setting type', NEW.name;
+END;
+$FUNC$ LANGUAGE PLPGSQL STABLE;
+
+CREATE CONSTRAINT TRIGGER valid_setting_type 
+  AFTER INSERT OR UPDATE ON actor.org_unit_setting
+  FOR EACH ROW EXECUTE PROCEDURE actor.setting_type_is_valid();
+
+CREATE CONSTRAINT TRIGGER valid_setting_type 
+  AFTER INSERT OR UPDATE ON actor.usr_setting
+  FOR EACH ROW EXECUTE PROCEDURE actor.setting_type_is_valid();
+
+CREATE CONSTRAINT TRIGGER valid_setting_type 
+  AFTER INSERT OR UPDATE ON actor.workstation_setting
+  FOR EACH ROW EXECUTE PROCEDURE actor.setting_type_is_valid();
+
+COMMIT;
+
+
+
diff --git a/Open-ILS/src/sql/Pg/upgrade/YYYY.data.workstation-settings.sql b/Open-ILS/src/sql/Pg/upgrade/YYYY.data.workstation-settings.sql
new file mode 100644
index 0000000000..445b2441eb
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/YYYY.data.workstation-settings.sql
@@ -0,0 +1,15 @@
+BEGIN;
+
+-- TODO: entries for all existing workstation settings we want to track.
+
+-- TEST DATA
+INSERT INTO config.setting_type 
+    (name, label, grp, datatype, org_setting, ws_setting)
+VALUES ('eg.circ.checkin.no_precat_alert', 
+    'Checkin Suppress Precat Alert', -- TODO: i18n
+    'circ', 'bool', TRUE, TRUE);
+
+COMMIT;
+
+
+
diff --git a/Open-ILS/src/sql/Pg/upgrade/ZZZZ.schema.workstation-settings.UNDO.sql b/Open-ILS/src/sql/Pg/upgrade/ZZZZ.schema.workstation-settings.UNDO.sql
new file mode 100644
index 0000000000..4b922d2a9d
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/ZZZZ.schema.workstation-settings.UNDO.sql
@@ -0,0 +1,15 @@
+BEGIN;
+
+DROP TRIGGER IF EXISTS valid_setting_type ON actor.workstation_setting;
+DROP TRIGGER IF EXISTS valid_setting_type ON actor.usr_setting;
+DROP TRIGGER IF EXISTS valid_setting_type ON actor.org_unit_setting;
+DROP FUNCTION IF EXISTS actor.setting_type_is_valid();
+DROP TABLE actor.workstation_setting;
+
+-- rebuild settings tables...
+DROP TABLE config.setting_type;
+
+COMMIT;
+
+
+
-- 
2.11.0