From e6a27117405d0553ac0c0926cc3814dffc574c34 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Thu, 22 Mar 2018 12:36:43 -0400 Subject: [PATCH] LP#1750894 Workstation settings; cascade lookup Signed-off-by: Bill Erickson --- .../upgrade/XXXX.schema.workstation-settings.sql | 220 +++++++-------------- .../Pg/upgrade/YYYY.data.workstation-settings.sql | 8 +- .../ZZZZ.schema.workstation-settings.UNDO.sql | 11 +- 3 files changed, 78 insertions(+), 161 deletions(-) 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 index 59b33d175e..e4a68a4b93 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.workstation-settings.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.workstation-settings.sql @@ -2,221 +2,142 @@ BEGIN; -- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); -CREATE TABLE config.setting_type ( +CREATE TABLE config.workstation_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, - reg_default TEXT, -- TODO: replace w/ org setting as default? - opac_visible BOOLEAN NOT NULL DEFAULT FALSE, -- user settings only - - 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 + CONSTRAINT cwst_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) - ) + CONSTRAINT cwst_no_empty_link CHECK + ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR + ( datatype <> 'link' AND fm_class IS NULL ) ) ); 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) + name TEXT NOT NULL REFERENCES config.workstation_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, reg_default, opac_visible, usr_setting) -SELECT name, label, grp, description, - datatype, fm_class, reg_default, opac_visible, 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 - FROM config.setting_type WHERE org_setting; - -CREATE VIEW config.usr_setting_type AS - SELECT name, label, grp, description, datatype, - fm_class, reg_default, opac_visible - FROM config.setting_type WHERE usr_setting; - -CREATE OR REPLACE FUNCTION actor.setting_type_is_valid() +CREATE OR REPLACE FUNCTION config.setting_is_user_or_ws() 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; + + IF TG_TABLE_NAME = 'usr_setting_type' THEN + PERFORM TRUE FROM config.workstation_setting_type cwst + WHERE cwst.name = NEW.name; + IF NOT FOUND THEN + RETURN NULL; + END IF; 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(); + IF TG_TABLE_NAME = 'workstation_setting_type' THEN + PERFORM TRUE FROM config.usr_setting_type cust + WHERE cust.name = NEW.name; + IF NOT FOUND THEN + RETURN NULL; + END IF; + END IF; -CREATE CONSTRAINT TRIGGER valid_setting_type - AFTER INSERT OR UPDATE ON actor.usr_setting - FOR EACH ROW EXECUTE PROCEDURE actor.setting_type_is_valid(); + RAISE EXCEPTION + '% Cannot be used as both a user setting and a workstation setting.', + NEW.name; +END; +$FUNC$ LANGUAGE PLPGSQL STABLE; -CREATE CONSTRAINT TRIGGER valid_setting_type - AFTER INSERT OR UPDATE ON actor.workstation_setting - FOR EACH ROW EXECUTE PROCEDURE actor.setting_type_is_valid(); +CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws + AFTER INSERT OR UPDATE ON config.usr_setting_type + FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws(); +CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws + AFTER INSERT OR UPDATE ON config.workstation_setting_type + FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws(); CREATE OR REPLACE FUNCTION actor.get_setting(setting_name TEXT, org_id INT, user_id INT, workstation_id INT) RETURNS JSON AS $FUNC$ DECLARE - setting_type config.setting_type%ROWTYPE; setting_value JSON; + org_setting_type config.org_unit_setting_type%ROWTYPE; BEGIN - SELECT INTO setting_type * FROM config.setting_type - WHERE name = setting_name; - - IF NOT FOUND THEN - RETURN NULL; - END IF; - - -- User and workstation settings have the same priority. We do not - -- check view_perm's on user settings since values apply only to the - -- calling user. - IF user_id IS NOT NULL AND setting_type.usr_setting THEN - SELECT INTO setting_value value FROM actor.usr_setting + -- User and workstation settings have the same priority. + -- Start with user settings since that's the simplest code path. + IF user_id IS NOT NULL THEN + SELECT INTO setting_value value + FROM actor.usr_setting WHERE usr = user_id AND name = setting_name; IF FOUND THEN RETURN setting_value; END IF; END IF; - -- Org and workstation settings enforce the view_perm. - - -- Get the org_id from the workstation when present Org value is - -- used in perm checks and org_unit_setting lookups when needed. + -- No user setting value found. Next try workstation. IF workstation_id IS NOT NULL THEN + + SELECT INTO setting_value value + FROM actor.workstation_setting + WHERE workstation = workstation_id AND name = setting_name; + + IF FOUND THEN + RETURN setting_value; + END IF; + + -- Workstation setting not found. However, since we have a + -- workstation let's use its owning_lib as our context org unit + -- instead of the value provided by 'org_id' which could be NULL. SELECT INTO org_id owning_lib FROM actor.workstation WHERE id = workstation_id; END IF; - IF org_id IS NULL THEN - -- No workstation or org_id has been provided. nothnig we can - -- do. This is not necessarily an error condition, since the - -- caller may only care about user settings. + -- Some org unit settings are protected by a view permission. + -- First see if we have any data that needs protecting, then + -- check the permission if needed. + + SELECT INTO setting_value value + FROM actor.org_unit_ancestor_setting(setting_name, org_id); + + IF NOT FOUND THEN + -- No value found -- perm check is irrelevant. RETURN NULL; END IF; - IF setting_type.view_perm IS NOT NULL THEN + -- Check view permissions if necessary. + SELECT INTO org_setting_type * + FROM config.org_unit_setting_type WHERE name = setting_name; + + IF org_setting_type.view_perm IS NOT NULL THEN - -- settings with view perms need to know who is requesting them. IF user_id IS NULL THEN RAISE NOTICE 'Perm check required but no user_id provided'; RETURN NULL; END IF; IF NOT permission.usr_has_perm( - user_id, - (SELECT code FROM permission.perm_list WHERE id = setting_type.view_perm), - org_id) + user_id, (SELECT code FROM permission.perm_list + WHERE id = org_setting_type.view_perm), org_id) THEN RAISE NOTICE 'Perm check failed for user % on %', - user_id, setting_type.view_perm; + user_id, org_setting_type.view_perm; RETURN NULL; END IF; END IF; - IF workstation_id IS NOT NULL AND setting_type.ws_setting THEN - SELECT INTO setting_value value FROM actor.workstation_setting - WHERE workstation = workstation_id AND name = setting_name; - IF FOUND THEN - RETURN setting_value; - END IF; - END IF; - - IF setting_type.org_setting THEN - SELECT INTO setting_value value - FROM actor.org_unit_ancestor_setting(setting_name, org_id); - - IF FOUND THEN - RETURN setting_value; - END IF; - END IF; - - RETURN NULL; + -- Perm check succeeded or was not necessary. + RETURN setting_value; END; $FUNC$ LANGUAGE PLPGSQL; @@ -224,4 +145,3 @@ $FUNC$ LANGUAGE PLPGSQL; 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 index 445b2441eb..c07dc6902a 100644 --- a/Open-ILS/src/sql/Pg/upgrade/YYYY.data.workstation-settings.sql +++ b/Open-ILS/src/sql/Pg/upgrade/YYYY.data.workstation-settings.sql @@ -3,11 +3,11 @@ 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) +/* +INSERT INTO config.workstation_setting_type (name, label, grp, datatype) VALUES ('eg.circ.checkin.no_precat_alert', - 'Checkin Suppress Precat Alert', -- TODO: i18n - 'circ', 'bool', TRUE, TRUE); + 'Checkin Suppress Precat Alert' /*TODO: i18n*/, 'circ', 'bool'); + */ 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 index 5f91e04421..6408f8db78 100644 --- 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 @@ -2,14 +2,11 @@ BEGIN; DROP FUNCTION actor.get_setting(TEXT, INT, INT, INT); -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 TRIGGER IF EXISTS check_setting_is_usr_or_ws ON actor.workstation_setting; +DROP TRIGGER IF EXISTS check_setting_is_usr_or_ws ON actor.usr_setting; +DROP FUNCTION IF EXISTS config.setting_is_user_or_ws(); DROP TABLE actor.workstation_setting; - --- rebuild settings tables... -DROP TABLE config.setting_type; +DROP TABLE config.workstation_setting_type; COMMIT; -- 2.11.0