3 -- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
5 CREATE TABLE config.workstation_setting_type (
7 label TEXT UNIQUE NOT NULL,
8 grp TEXT REFERENCES config.settings_group (name),
10 datatype TEXT NOT NULL DEFAULT 'string',
13 -- define valid datatypes
15 CONSTRAINT cwst_valid_datatype CHECK ( datatype IN
16 ( 'bool', 'integer', 'float', 'currency', 'interval',
17 'date', 'string', 'object', 'array', 'link' ) ),
19 -- fm_class is meaningful only for 'link' datatype
21 CONSTRAINT cwst_no_empty_link CHECK
22 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
23 ( datatype <> 'link' AND fm_class IS NULL ) )
26 CREATE TABLE actor.workstation_setting (
27 id SERIAL PRIMARY KEY,
28 workstation INT NOT NULL REFERENCES actor.workstation (id)
29 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
30 name TEXT NOT NULL REFERENCES config.workstation_setting_type (name)
31 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
35 CREATE OR REPLACE FUNCTION config.setting_is_user_or_ws()
36 RETURNS TRIGGER AS $FUNC$
39 IF TG_TABLE_NAME = 'usr_setting_type' THEN
40 PERFORM TRUE FROM config.workstation_setting_type cwst
41 WHERE cwst.name = NEW.name;
47 IF TG_TABLE_NAME = 'workstation_setting_type' THEN
48 PERFORM TRUE FROM config.usr_setting_type cust
49 WHERE cust.name = NEW.name;
56 '% Cannot be used as both a user setting and a workstation setting.',
59 $FUNC$ LANGUAGE PLPGSQL STABLE;
61 CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws
62 AFTER INSERT OR UPDATE ON config.usr_setting_type
63 FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws();
65 CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws
66 AFTER INSERT OR UPDATE ON config.workstation_setting_type
67 FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws();
69 CREATE OR REPLACE FUNCTION actor.get_setting(setting_name TEXT,
70 org_id INT, user_id INT, workstation_id INT) RETURNS JSON AS
74 org_setting_type config.org_unit_setting_type%ROWTYPE;
77 -- User and workstation settings have the same priority.
78 -- Start with user settings since that's the simplest code path.
79 IF user_id IS NOT NULL THEN
80 SELECT INTO setting_value value
81 FROM actor.usr_setting
82 WHERE usr = user_id AND name = setting_name;
88 -- No user setting value found. Next try workstation.
89 IF workstation_id IS NOT NULL THEN
91 SELECT INTO setting_value value
92 FROM actor.workstation_setting
93 WHERE workstation = workstation_id AND name = setting_name;
99 -- Workstation setting not found. However, since we have a
100 -- workstation let's use its owning_lib as our context org unit
101 -- instead of the value provided by 'org_id' which could be NULL.
102 SELECT INTO org_id owning_lib
103 FROM actor.workstation WHERE id = workstation_id;
106 -- Some org unit settings are protected by a view permission.
107 -- First see if we have any data that needs protecting, then
108 -- check the permission if needed.
110 SELECT INTO setting_value value
111 FROM actor.org_unit_ancestor_setting(setting_name, org_id);
114 -- No value found -- perm check is irrelevant.
118 -- Check view permissions if necessary.
119 SELECT INTO org_setting_type *
120 FROM config.org_unit_setting_type WHERE name = setting_name;
122 IF org_setting_type.view_perm IS NOT NULL THEN
124 IF user_id IS NULL THEN
125 RAISE NOTICE 'Perm check required but no user_id provided';
129 IF NOT permission.usr_has_perm(
130 user_id, (SELECT code FROM permission.perm_list
131 WHERE id = org_setting_type.view_perm), org_id)
133 RAISE NOTICE 'Perm check failed for user % on %',
134 user_id, org_setting_type.view_perm;
139 -- Perm check succeeded or was not necessary.
140 RETURN setting_value;
142 $FUNC$ LANGUAGE PLPGSQL;
145 CREATE OR REPLACE FUNCTION actor.get_setting_batch(setting_names TEXT[],
146 org_id INT, user_id INT, workstation_id INT) RETURNS SETOF JSON AS
148 -- Returns a row per setting matching the setting name order. If no
149 -- value is applied, NULL is returned to retain name-response ordering.
153 FOREACH setting_name IN ARRAY setting_names LOOP
154 RETURN NEXT * FROM actor.get_setting(
155 setting_Name, org_id, user_id, workstation_id);
158 $FUNC$ LANGUAGE PLPGSQL;