3 CREATE TYPE actor.cascade_setting_summary AS (
6 has_org_setting BOOLEAN,
7 has_user_setting BOOLEAN,
8 has_workstation_setting BOOLEAN
11 SELECT evergreen.upgrade_deps_block_check('1116', :eg_version);
13 CREATE TABLE config.workstation_setting_type (
14 name TEXT PRIMARY KEY,
15 label TEXT UNIQUE NOT NULL,
16 grp TEXT REFERENCES config.settings_group (name),
18 datatype TEXT NOT NULL DEFAULT 'string',
21 -- define valid datatypes
23 CONSTRAINT cwst_valid_datatype CHECK ( datatype IN
24 ( 'bool', 'integer', 'float', 'currency', 'interval',
25 'date', 'string', 'object', 'array', 'link' ) ),
27 -- fm_class is meaningful only for 'link' datatype
29 CONSTRAINT cwst_no_empty_link CHECK
30 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
31 ( datatype <> 'link' AND fm_class IS NULL ) )
34 CREATE TABLE actor.workstation_setting (
35 id SERIAL PRIMARY KEY,
36 workstation INT NOT NULL REFERENCES actor.workstation (id)
37 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
38 name TEXT NOT NULL REFERENCES config.workstation_setting_type (name)
39 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
44 CREATE INDEX actor_workstation_setting_workstation_idx
45 ON actor.workstation_setting (workstation);
47 CREATE OR REPLACE FUNCTION config.setting_is_user_or_ws()
48 RETURNS TRIGGER AS $FUNC$
51 IF TG_TABLE_NAME = 'usr_setting_type' THEN
52 PERFORM TRUE FROM config.workstation_setting_type cwst
53 WHERE cwst.name = NEW.name;
59 IF TG_TABLE_NAME = 'workstation_setting_type' THEN
60 PERFORM TRUE FROM config.usr_setting_type cust
61 WHERE cust.name = NEW.name;
68 '% Cannot be used as both a user setting and a workstation setting.',
71 $FUNC$ LANGUAGE PLPGSQL STABLE;
73 CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws
74 AFTER INSERT OR UPDATE ON config.usr_setting_type
75 FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws();
77 CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws
78 AFTER INSERT OR UPDATE ON config.workstation_setting_type
79 FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws();
81 CREATE OR REPLACE FUNCTION actor.get_cascade_setting(
82 setting_name TEXT, org_id INT, user_id INT, workstation_id INT)
83 RETURNS actor.cascade_setting_summary AS
87 summary actor.cascade_setting_summary;
88 org_setting_type config.org_unit_setting_type%ROWTYPE;
91 summary.name := setting_name;
93 -- Collect the org setting type status first in case we exit early.
94 -- The existance of an org setting type is not considered
95 -- privileged information.
96 SELECT INTO org_setting_type *
97 FROM config.org_unit_setting_type WHERE name = setting_name;
99 summary.has_org_setting := TRUE;
101 summary.has_org_setting := FALSE;
104 -- User and workstation settings have the same priority.
105 -- Start with user settings since that's the simplest code path.
106 -- The workstation_id is ignored if no user_id is provided.
107 IF user_id IS NOT NULL THEN
109 SELECT INTO summary.value value FROM actor.usr_setting
110 WHERE usr = user_id AND name = setting_name;
113 -- if we have a value, we have a setting type
114 summary.has_user_setting := TRUE;
116 IF workstation_id IS NOT NULL THEN
117 -- Only inform the caller about the workstation
118 -- setting type disposition when a workstation id is
119 -- provided. Otherwise, it's NULL to indicate UNKNOWN.
120 summary.has_workstation_setting := FALSE;
126 -- no user setting value, but a setting type may exist
127 SELECT INTO summary.has_user_setting EXISTS (
128 SELECT TRUE FROM config.usr_setting_type
129 WHERE name = setting_name
132 IF workstation_id IS NOT NULL THEN
134 IF NOT summary.has_user_setting THEN
135 -- A workstation setting type may only exist when a user
136 -- setting type does not.
138 SELECT INTO summary.value value
139 FROM actor.workstation_setting
140 WHERE workstation = workstation_id AND name = setting_name;
143 -- if we have a value, we have a setting type
144 summary.has_workstation_setting := TRUE;
148 -- no value, but a setting type may exist
149 SELECT INTO summary.has_workstation_setting EXISTS (
150 SELECT TRUE FROM config.workstation_setting_type
151 WHERE name = setting_name
155 -- Finally make use of the workstation to determine the org
156 -- unit if none is provided.
157 IF org_id IS NULL AND summary.has_org_setting THEN
158 SELECT INTO org_id owning_lib
159 FROM actor.workstation WHERE id = workstation_id;
164 -- Some org unit settings are protected by a view permission.
165 -- First see if we have any data that needs protecting, then
166 -- check the permission if needed.
168 IF NOT summary.has_org_setting THEN
172 -- avoid putting the value into the summary until we confirm
173 -- the value should be visible to the caller.
174 SELECT INTO setting_value value
175 FROM actor.org_unit_ancestor_setting(setting_name, org_id);
178 -- No value found -- perm check is irrelevant.
182 IF org_setting_type.view_perm IS NOT NULL THEN
184 IF user_id IS NULL THEN
185 RAISE NOTICE 'Perm check required but no user_id provided';
189 IF NOT permission.usr_has_perm(
190 user_id, (SELECT code FROM permission.perm_list
191 WHERE id = org_setting_type.view_perm), org_id)
193 RAISE NOTICE 'Perm check failed for user % on %',
194 user_id, org_setting_type.view_perm;
199 -- Perm check succeeded or was not necessary.
200 summary.value := setting_value;
203 $FUNC$ LANGUAGE PLPGSQL;
206 CREATE OR REPLACE FUNCTION actor.get_cascade_setting_batch(
207 setting_names TEXT[], org_id INT, user_id INT, workstation_id INT)
208 RETURNS SETOF actor.cascade_setting_summary AS
210 -- Returns a row per setting matching the setting name order. If no
211 -- value is applied, NULL is returned to retain name-response ordering.
214 summary actor.cascade_setting_summary;
216 FOREACH setting_name IN ARRAY setting_names LOOP
217 SELECT INTO summary * FROM actor.get_cascade_setting(
218 setting_Name, org_id, user_id, workstation_id);
222 $FUNC$ LANGUAGE PLPGSQL;