2 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
3 * Mike Rylander <mrylander@gmail.com>
4 * Copyright (C) 2010 Laurentian University
5 * Dan Scott <dscott@laurentian.ca>
7 * This program is free software; you can redistribute it and/or
8 * modify it under the terms of the GNU General Public License
9 * as published by the Free Software Foundation; either version 2
10 * of the License, or (at your option) any later version.
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
18 DROP SCHEMA IF EXISTS actor CASCADE;
22 COMMENT ON SCHEMA actor IS $$
23 Holds all tables pertaining to users and libraries (org units).
26 CREATE TABLE actor.usr (
27 id SERIAL PRIMARY KEY,
28 card INT UNIQUE, -- active card
29 profile INT NOT NULL, -- patron profile
30 usrname TEXT NOT NULL UNIQUE,
33 standing INT NOT NULL DEFAULT 1 REFERENCES config.standing (id) DEFERRABLE INITIALLY DEFERRED,
34 ident_type INT NOT NULL REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED,
36 ident_type2 INT REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED,
38 net_access_level INT NOT NULL DEFAULT 1 REFERENCES config.net_access_level (id) DEFERRABLE INITIALLY DEFERRED,
41 first_given_name TEXT NOT NULL,
42 second_given_name TEXT,
43 family_name TEXT NOT NULL,
53 active BOOL NOT NULL DEFAULT TRUE,
54 master_account BOOL NOT NULL DEFAULT FALSE,
55 super_user BOOL NOT NULL DEFAULT FALSE,
56 barred BOOL NOT NULL DEFAULT FALSE,
57 deleted BOOL NOT NULL DEFAULT FALSE,
58 juvenile BOOL NOT NULL DEFAULT FALSE,
59 usrgroup SERIAL NOT NULL,
60 claims_returned_count INT NOT NULL DEFAULT 0,
61 credit_forward_balance NUMERIC(6,2) NOT NULL DEFAULT 0.00,
62 last_xact_id TEXT NOT NULL DEFAULT 'none',
64 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
65 expire_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT (now() + '3 years'::INTERVAL),
66 claims_never_checked_out_count INT NOT NULL DEFAULT 0,
67 last_update_time TIMESTAMP WITH TIME ZONE
69 COMMENT ON TABLE actor.usr IS $$
72 This table contains the core User objects that describe both
73 staff members and patrons. The difference between the two
74 types of users is based on the user's permissions.
77 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
78 CREATE INDEX actor_usr_usrgroup_idx ON actor.usr (usrgroup);
79 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
80 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
82 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name));
83 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name));
84 CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name));
85 CREATE INDEX actor_usr_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(first_given_name));
86 CREATE INDEX actor_usr_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(second_given_name));
87 CREATE INDEX actor_usr_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(family_name));
88 CREATE INDEX actor_usr_usrname_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(usrname));
90 CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname));
91 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
93 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
94 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
95 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
97 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
98 (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
100 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
101 (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
103 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
104 (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
106 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
107 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
109 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
111 NEW.passwd = MD5( NEW.passwd );
116 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
118 IF NEW.passwd <> OLD.passwd THEN
119 NEW.passwd = MD5( NEW.passwd );
125 CREATE OR REPLACE FUNCTION actor.au_updated()
126 RETURNS TRIGGER AS $$
128 NEW.last_update_time := now();
133 CREATE TRIGGER au_update_trig
134 BEFORE INSERT OR UPDATE ON actor.usr
135 FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
137 CREATE TRIGGER actor_crypt_pw_update_trigger
138 BEFORE UPDATE ON actor.usr FOR EACH ROW
139 EXECUTE PROCEDURE actor.crypt_pw_update ();
141 CREATE TRIGGER actor_crypt_pw_insert_trigger
142 BEFORE INSERT ON actor.usr FOR EACH ROW
143 EXECUTE PROCEDURE actor.crypt_pw_insert ();
145 CREATE RULE protect_user_delete AS ON DELETE TO actor.usr DO INSTEAD UPDATE actor.usr SET deleted = TRUE WHERE OLD.id = actor.usr.id;
147 CREATE TABLE actor.usr_note (
148 id BIGSERIAL PRIMARY KEY,
149 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
150 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
151 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
152 pub BOOL NOT NULL DEFAULT FALSE,
156 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
157 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
159 CREATE TABLE actor.usr_setting (
160 id BIGSERIAL PRIMARY KEY,
161 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
162 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
164 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
166 COMMENT ON TABLE actor.usr_setting IS $$
169 This table contains any arbitrary settings that a client
170 program would like to save for a user.
173 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
175 CREATE TABLE actor.stat_cat_sip_fields (
176 field CHAR(2) PRIMARY KEY,
178 one_only BOOL NOT NULL DEFAULT FALSE
180 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
181 Actor Statistical Category SIP Fields
183 Contains the list of valid SIP Field identifiers for
184 Statistical Categories.
187 CREATE TABLE actor.stat_cat (
188 id SERIAL PRIMARY KEY,
191 opac_visible BOOL NOT NULL DEFAULT FALSE,
192 usr_summary BOOL NOT NULL DEFAULT FALSE,
193 sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
195 checkout_archive BOOL NOT NULL DEFAULT FALSE,
196 required BOOL NOT NULL DEFAULT FALSE,
197 allow_freetext BOOL NOT NULL DEFAULT TRUE,
198 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
200 COMMENT ON TABLE actor.stat_cat IS $$
201 User Statistical Catagories
203 Local data collected about Users is placed into a Statistical
204 Catagory. Here's where those catagories are defined.
208 CREATE TABLE actor.stat_cat_entry (
209 id SERIAL PRIMARY KEY,
210 stat_cat INT NOT NULL,
213 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
215 COMMENT ON TABLE actor.stat_cat_entry IS $$
216 User Statistical Catagory Entries
218 Local data collected about Users is placed into a Statistical
219 Catagory. Each library can create entries into any of its own
220 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
224 CREATE TABLE actor.stat_cat_entry_usr_map (
225 id BIGSERIAL PRIMARY KEY,
226 stat_cat_entry TEXT NOT NULL,
227 stat_cat INT NOT NULL,
228 target_usr INT NOT NULL,
229 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
231 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
232 Statistical Catagory Entry to User map
234 Records the stat_cat entries for each user.
237 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
239 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
241 sipfield actor.stat_cat_sip_fields%ROWTYPE;
244 IF NEW.sip_field IS NOT NULL THEN
245 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
246 IF sipfield.one_only THEN
247 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
248 IF use_count > 0 THEN
249 RAISE EXCEPTION 'Sip field cannot be used twice';
255 $func$ LANGUAGE PLPGSQL;
257 CREATE TRIGGER actor_stat_cat_sip_update_trigger
258 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
259 EXECUTE PROCEDURE actor.stat_cat_check();
261 CREATE TABLE actor.card (
262 id SERIAL PRIMARY KEY,
263 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
264 barcode TEXT NOT NULL UNIQUE,
265 active BOOL NOT NULL DEFAULT TRUE
267 COMMENT ON TABLE actor.card IS $$
270 Each User has one or more library cards. The current "main"
271 card is linked to here from the actor.usr table, and it is up
272 to the consortium policy whether more than one card can be
273 active for any one user at a given time.
276 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
277 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
279 CREATE TABLE actor.org_unit_type (
280 id SERIAL PRIMARY KEY,
282 opac_label TEXT NOT NULL,
284 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
285 can_have_vols BOOL NOT NULL DEFAULT TRUE,
286 can_have_users BOOL NOT NULL DEFAULT TRUE
288 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
290 CREATE TABLE actor.org_unit (
291 id SERIAL PRIMARY KEY,
292 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
293 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
298 shortname TEXT NOT NULL UNIQUE,
299 name TEXT NOT NULL UNIQUE,
302 opac_visible BOOL NOT NULL DEFAULT TRUE,
303 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
305 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
306 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
307 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
308 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
309 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
310 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
312 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
314 current_aou actor.org_unit%ROWTYPE;
320 seen_ous := ARRAY[NEW.id];
322 IF (TG_OP = 'UPDATE') THEN
323 IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
324 RETURN NEW; -- Doing an UPDATE with no change, just return it
329 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
330 RETURN NEW; -- No loop. Carry on.
332 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
333 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
336 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
337 seen_ous := seen_ous || current_aou.id;
338 depth_count := depth_count + 1;
339 IF depth_count = 100 THEN
340 RAISE 'OU CHECK TOO DEEP';
348 CREATE TRIGGER actor_org_unit_parent_protect_trigger
349 BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
350 EXECUTE PROCEDURE actor.org_unit_parent_protect ();
352 CREATE TABLE actor.org_lasso (
353 id SERIAL PRIMARY KEY,
357 CREATE TABLE actor.org_lasso_map (
358 id SERIAL PRIMARY KEY,
359 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
360 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
362 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
363 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
365 CREATE TABLE actor.org_unit_proximity (
366 id BIGSERIAL PRIMARY KEY,
371 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
373 CREATE TABLE actor.stat_cat_entry_default (
374 id SERIAL PRIMARY KEY,
375 stat_cat_entry INT NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
376 stat_cat INT NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
377 owner INT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
378 CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
380 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
381 User Statistical Category Default Entry
383 A library may choose one of the stat_cat entries to be the
388 CREATE TABLE actor.org_unit_proximity_adjustment (
389 id SERIAL PRIMARY KEY,
390 item_circ_lib INT REFERENCES actor.org_unit (id),
391 item_owning_lib INT REFERENCES actor.org_unit (id),
392 copy_location INT, -- REFERENCES asset.copy_location (id),
393 hold_pickup_lib INT REFERENCES actor.org_unit (id),
394 hold_request_lib INT REFERENCES actor.org_unit (id),
395 pos INT NOT NULL DEFAULT 0,
396 absolute_adjustment BOOL NOT NULL DEFAULT FALSE,
397 prox_adjustment NUMERIC,
398 circ_mod TEXT, -- REFERENCES config.circ_modifier (code),
399 CONSTRAINT prox_adj_criterium CHECK (COALESCE(item_circ_lib::TEXT,item_owning_lib::TEXT,copy_location::TEXT,hold_pickup_lib::TEXT,hold_request_lib::TEXT,circ_mod) IS NOT NULL)
401 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
402 COALESCE(item_circ_lib, -1),
403 COALESCE(item_owning_lib, -1),
404 COALESCE(copy_location, -1),
405 COALESCE(hold_pickup_lib, -1),
406 COALESCE(hold_request_lib, -1),
407 COALESCE(circ_mod, ''),
410 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
411 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
412 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
413 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
414 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
415 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
417 CREATE TABLE actor.hours_of_operation (
418 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
419 dow_0_open TIME NOT NULL DEFAULT '09:00',
420 dow_0_close TIME NOT NULL DEFAULT '17:00',
421 dow_1_open TIME NOT NULL DEFAULT '09:00',
422 dow_1_close TIME NOT NULL DEFAULT '17:00',
423 dow_2_open TIME NOT NULL DEFAULT '09:00',
424 dow_2_close TIME NOT NULL DEFAULT '17:00',
425 dow_3_open TIME NOT NULL DEFAULT '09:00',
426 dow_3_close TIME NOT NULL DEFAULT '17:00',
427 dow_4_open TIME NOT NULL DEFAULT '09:00',
428 dow_4_close TIME NOT NULL DEFAULT '17:00',
429 dow_5_open TIME NOT NULL DEFAULT '09:00',
430 dow_5_close TIME NOT NULL DEFAULT '17:00',
431 dow_6_open TIME NOT NULL DEFAULT '09:00',
432 dow_6_close TIME NOT NULL DEFAULT '17:00'
434 COMMENT ON TABLE actor.hours_of_operation IS $$
435 When does this org_unit usually open and close? (Variations
436 are expressed in the actor.org_unit_closed table.)
438 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
439 When does this org_unit open on Monday?
441 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
442 When does this org_unit close on Monday?
444 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
445 When does this org_unit open on Tuesday?
447 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
448 When does this org_unit close on Tuesday?
450 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
451 When does this org_unit open on Wednesday?
453 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
454 When does this org_unit close on Wednesday?
456 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
457 When does this org_unit open on Thursday?
459 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
460 When does this org_unit close on Thursday?
462 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
463 When does this org_unit open on Friday?
465 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
466 When does this org_unit close on Friday?
468 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
469 When does this org_unit open on Saturday?
471 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
472 When does this org_unit close on Saturday?
474 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
475 When does this org_unit open on Sunday?
477 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
478 When does this org_unit close on Sunday?
481 CREATE TABLE actor.org_unit_closed (
482 id SERIAL PRIMARY KEY,
483 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
484 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
485 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
486 full_day BOOLEAN NOT NULL DEFAULT FALSE,
487 multi_day BOOLEAN NOT NULL DEFAULT FALSE,
491 -- Workstation registration...
492 CREATE TABLE actor.workstation (
493 id SERIAL PRIMARY KEY,
494 name TEXT NOT NULL UNIQUE,
495 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
498 CREATE TABLE actor.usr_org_unit_opt_in (
499 id SERIAL PRIMARY KEY,
500 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
501 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
502 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
503 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
504 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
505 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
507 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
509 CREATE TABLE actor.org_unit_setting (
510 id BIGSERIAL PRIMARY KEY,
511 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
512 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
514 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
515 CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
517 COMMENT ON TABLE actor.org_unit_setting IS $$
520 This table contains any arbitrary settings that a client
521 program would like to save for an org unit.
524 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
526 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
527 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
531 -- Check for which setting is being updated, and log it.
532 SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
534 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
538 $ous_change_log$ LANGUAGE plpgsql;
540 CREATE TRIGGER log_ous_change
541 BEFORE INSERT OR UPDATE ON actor.org_unit_setting
542 FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
544 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
548 -- Check for which setting is being updated, and log it.
549 SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
551 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
555 $ous_delete_log$ LANGUAGE plpgsql;
557 CREATE TRIGGER log_ous_del
558 BEFORE DELETE ON actor.org_unit_setting
559 FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
564 CREATE TABLE actor.usr_address (
565 id SERIAL PRIMARY KEY,
566 valid BOOL NOT NULL DEFAULT TRUE,
567 within_city_limits BOOL NOT NULL DEFAULT TRUE,
568 address_type TEXT NOT NULL DEFAULT 'MAILING',
569 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
570 street1 TEXT NOT NULL,
575 country TEXT NOT NULL,
576 post_code TEXT NOT NULL,
577 pending BOOL NOT NULL DEFAULT FALSE,
578 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
581 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
583 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
584 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
586 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
587 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
588 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
590 CREATE TABLE actor.usr_password_reset (
591 id SERIAL PRIMARY KEY,
593 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
594 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
595 has_been_reset BOOL NOT NULL DEFAULT false
597 COMMENT ON TABLE actor.usr_password_reset IS $$
598 Self-serve password reset requests
600 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
601 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
602 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
603 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
605 CREATE TABLE actor.org_address (
606 id SERIAL PRIMARY KEY,
607 valid BOOL NOT NULL DEFAULT TRUE,
608 address_type TEXT NOT NULL DEFAULT 'MAILING',
609 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
610 street1 TEXT NOT NULL,
615 country TEXT NOT NULL,
616 post_code TEXT NOT NULL,
620 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
622 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
623 SELECT SUBSTRING( $1, 1, 5);
626 CREATE TABLE actor.usr_standing_penalty (
627 id SERIAL PRIMARY KEY,
628 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
629 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
630 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
631 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
632 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
633 stop_date TIMESTAMP WITH TIME ZONE,
636 COMMENT ON TABLE actor.usr_standing_penalty IS $$
637 User standing penalties
640 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
641 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
644 CREATE TABLE actor.usr_saved_search (
645 id SERIAL PRIMARY KEY,
646 owner INT NOT NULL REFERENCES actor.usr (id)
648 DEFERRABLE INITIALLY DEFERRED,
650 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
651 query_text TEXT NOT NULL,
652 query_type TEXT NOT NULL
653 CONSTRAINT valid_query_text CHECK (
654 query_type IN ( 'URL' )) DEFAULT 'URL',
655 -- we may add other types someday
657 CONSTRAINT valid_target CHECK (
658 target IN ( 'record', 'metarecord', 'callnumber' )),
659 CONSTRAINT name_once_per_user UNIQUE (owner, name)
662 CREATE TABLE actor.address_alert (
663 id SERIAL PRIMARY KEY,
664 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
665 active BOOL NOT NULL DEFAULT TRUE,
666 match_all BOOL NOT NULL DEFAULT TRUE,
667 alert_message TEXT NOT NULL,
675 mailing_address BOOL NOT NULL DEFAULT FALSE,
676 billing_address BOOL NOT NULL DEFAULT FALSE
679 CREATE TABLE actor.usr_activity (
680 id BIGSERIAL PRIMARY KEY,
681 usr INT REFERENCES actor.usr (id) ON DELETE SET NULL,
682 etype INT NOT NULL REFERENCES config.usr_activity_type (id),
683 event_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
685 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
687 CREATE TABLE actor.toolbar (
688 id BIGSERIAL PRIMARY KEY,
689 ws INT REFERENCES actor.workstation (id) ON DELETE CASCADE,
690 org INT REFERENCES actor.org_unit (id) ON DELETE CASCADE,
691 usr INT REFERENCES actor.usr (id) ON DELETE CASCADE,
693 layout TEXT NOT NULL,
694 CONSTRAINT only_one_type CHECK (
695 (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
696 (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
697 (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
699 CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
701 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
702 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
703 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
705 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
706 CREATE TABLE actor.org_unit_custom_tree (
707 id SERIAL PRIMARY KEY,
708 active BOOLEAN DEFAULT FALSE,
709 purpose actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
712 CREATE TABLE actor.org_unit_custom_tree_node (
713 id SERIAL PRIMARY KEY,
714 tree INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
715 org_unit INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
716 parent_node INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
717 sibling_order INTEGER NOT NULL DEFAULT 0,
718 CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
721 CREATE TABLE actor.search_query (
722 id SERIAL PRIMARY KEY,
723 label TEXT NOT NULL, -- i18n
724 query_text TEXT NOT NULL -- QP text
727 CREATE TABLE actor.search_filter_group (
728 id SERIAL PRIMARY KEY,
729 owner INT NOT NULL REFERENCES actor.org_unit (id)
730 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
731 code TEXT NOT NULL, -- for CGI, etc.
732 label TEXT NOT NULL, -- i18n
733 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
734 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
735 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
738 CREATE TABLE actor.search_filter_group_entry (
739 id SERIAL PRIMARY KEY,
740 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
741 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
742 pos INT NOT NULL DEFAULT 0,
743 query INT NOT NULL REFERENCES actor.search_query(id)
744 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
745 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
748 CREATE TABLE actor.usr_message (
749 id SERIAL PRIMARY KEY,
750 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
752 message TEXT NOT NULL,
753 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
754 deleted BOOL NOT NULL DEFAULT FALSE,
755 read_date TIMESTAMP WITH TIME ZONE,
756 sending_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
758 CREATE INDEX aum_usr ON actor.usr_message (usr);
760 CREATE RULE protect_usr_message_delete AS
761 ON DELETE TO actor.usr_message DO INSTEAD (
762 UPDATE actor.usr_message
764 WHERE OLD.id = actor.usr_message.id
767 CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$
772 IF TG_OP = 'UPDATE' THEN
773 IF OLD.pub = TRUE THEN
778 SELECT INTO sending_ou aw.owning_lib
779 FROM auditor.get_audit_info() agai
780 JOIN actor.workstation aw ON (aw.id = agai.eg_ws);
781 IF sending_ou IS NULL THEN
782 SELECT INTO sending_ou home_ou
784 WHERE id = NEW.creator;
786 INSERT INTO actor.usr_message (usr, title, message, sending_lib)
787 VALUES (NEW.usr, NEW.title, NEW.value, sending_ou);
794 CREATE TRIGGER convert_usr_note_to_message_tgr
795 AFTER INSERT OR UPDATE ON actor.usr_note
796 FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message();
798 -- limited view to ensure that a library user who somehow
799 -- manages to figure out how to access pcrud cannot change
800 -- the text of messages sent them
801 CREATE VIEW actor.usr_message_limited
802 AS SELECT * FROM actor.usr_message;
804 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
806 IF TG_OP = 'UPDATE' THEN
807 UPDATE actor.usr_message
808 SET read_date = NEW.read_date,
809 deleted = NEW.deleted
817 CREATE TRIGGER restrict_usr_message_limited_tgr
818 INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
819 FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
821 CREATE TABLE actor.passwd_type (
822 code TEXT PRIMARY KEY,
823 name TEXT UNIQUE NOT NULL,
824 login BOOLEAN NOT NULL DEFAULT FALSE,
825 regex TEXT, -- pending
826 crypt_algo TEXT, -- e.g. 'bf'
828 -- gen_salt() iter count used with each new salt.
829 -- A non-NULL value for iter_count is our indication the
830 -- password is salted and encrypted via crypt()
831 iter_count INTEGER CHECK (iter_count IS NULL OR iter_count > 0)
834 CREATE TABLE actor.passwd (
835 id SERIAL PRIMARY KEY,
836 usr INTEGER NOT NULL REFERENCES actor.usr(id)
837 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
838 salt TEXT, -- will be NULL for non-crypt'ed passwords
839 passwd TEXT NOT NULL,
840 passwd_type TEXT NOT NULL REFERENCES actor.passwd_type(code)
841 DEFERRABLE INITIALLY DEFERRED,
842 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
843 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
844 CONSTRAINT passwd_type_once_per_user UNIQUE (usr, passwd_type)
847 CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT)
850 type_row actor.passwd_type%ROWTYPE;
852 /* Returns a new salt based on the passwd_type encryption settings.
853 * Returns NULL If the password type is not crypt()'ed.
856 SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;
859 RETURN EXCEPTION 'No such password type: %', pw_type;
862 IF type_row.iter_count IS NULL THEN
863 -- This password type is unsalted. That's OK.
867 RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
873 TODO: when a user changes their password in the application, the
874 app layer has access to the bare password. At that point, we have
875 the opportunity to store the new password without the MD5(MD5())
876 intermediate hashing. Do we care? We would need a way to indicate
877 which passwords have the legacy intermediate hashing and which don't
878 so the app layer would know whether it should perform the intermediate
879 hashing. In either event, with the exception of migrate_passwd(), the
880 DB functions know or care nothing about intermediate hashing. Every
881 password is just a value that may or may not be internally crypt'ed.
884 CREATE OR REPLACE FUNCTION actor.set_passwd(
885 pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL)
886 RETURNS BOOLEAN AS $$
891 /* Sets the password value, creating a new actor.passwd row if needed.
892 * If the password type supports it, the new_pass value is crypt()'ed.
893 * For crypt'ed passwords, the salt comes from one of 3 places in order:
894 * new_salt (if present), existing salt (if present), newly created
898 IF new_salt IS NOT NULL THEN
901 pw_salt := actor.get_salt(pw_usr, pw_type);
903 IF pw_salt IS NULL THEN
904 /* We have no salt for this user + type. Assume they want a
905 * new salt. If this type is unsalted, create_salt() will
907 pw_salt := actor.create_salt(pw_type);
911 IF pw_salt IS NULL THEN
912 pw_text := new_pass; -- unsalted, use as-is.
914 pw_text := CRYPT(new_pass, pw_salt);
918 SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
919 WHERE usr = pw_usr AND passwd_type = pw_type;
922 -- no password row exists for this user + type. Create one.
923 INSERT INTO actor.passwd (usr, passwd_type, salt, passwd)
924 VALUES (pw_usr, pw_type, pw_salt, pw_text);
931 CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT)
935 type_row actor.passwd_type%ROWTYPE;
937 /* Returns the salt for the requested user + type. If the password
938 * type of "main" is requested and no password exists in actor.passwd,
939 * the user's existing password is migrated and the new salt is returned.
940 * Returns NULL if the password type is not crypt'ed (iter_count is NULL).
943 SELECT INTO pw_salt salt FROM actor.passwd
944 WHERE usr = pw_usr AND passwd_type = pw_type;
950 IF pw_type = 'main' THEN
951 -- Main password has not yet been migrated.
952 -- Do it now and return the newly created salt.
953 RETURN actor.migrate_passwd(pw_usr);
956 -- We have no salt to return. actor.create_salt() needed.
961 CREATE OR REPLACE FUNCTION
962 actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$
965 usr_row actor.usr%ROWTYPE;
967 /* Migrates legacy actor.usr.passwd value to actor.passwd with
968 * a password type 'main' and returns the new salt. For backwards
969 * compatibility with existing CHAP-style API's, we perform a
970 * layer of intermediate MD5(MD5()) hashing. This is intermediate
971 * hashing is not required of other passwords.
974 -- Avoid calling get_salt() here, because it may result in a
975 -- migrate_passwd() call, creating a loop.
976 SELECT INTO pw_salt salt FROM actor.passwd
977 WHERE usr = pw_usr AND passwd_type = 'main';
979 -- Only migrate passwords that have not already been migrated.
984 SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
986 pw_salt := actor.create_salt('main');
988 PERFORM actor.set_passwd(
989 pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
991 -- clear the existing password
992 UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
998 CREATE OR REPLACE FUNCTION
999 actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT)
1000 RETURNS BOOLEAN AS $$
1004 /* Returns TRUE if the password provided matches the in-db password.
1005 * If the password type is salted, we compare the output of CRYPT().
1006 * NOTE: test_passwd is MD5(salt || MD5(password)) for legacy
1010 SELECT INTO pw_salt salt FROM actor.passwd
1011 WHERE usr = pw_usr AND passwd_type = pw_type;
1018 IF pw_salt IS NULL THEN
1019 -- Password is unsalted, compare the un-CRYPT'ed values.
1021 SELECT TRUE FROM actor.passwd WHERE
1023 passwd_type = pw_type AND
1024 passwd = test_passwd
1029 SELECT TRUE FROM actor.passwd WHERE
1031 passwd_type = pw_type AND
1032 passwd = CRYPT(test_passwd, pw_salt)
1035 $$ STRICT LANGUAGE PLPGSQL;
1037 -- Remove all activity entries by activity type,
1038 -- except the most recent entry per user.
1039 CREATE OR REPLACE FUNCTION
1040 actor.purge_usr_activity_by_type(act_type INTEGER)
1045 FOR cur_usr IN SELECT DISTINCT(usr)
1046 FROM actor.usr_activity WHERE etype = act_type LOOP
1047 DELETE FROM actor.usr_activity WHERE id IN (
1049 FROM actor.usr_activity
1050 WHERE usr = cur_usr AND etype = act_type
1051 ORDER BY event_time DESC OFFSET 1
1055 END $$ LANGUAGE PLPGSQL;
1057 CREATE TABLE actor.workstation_setting (
1058 id SERIAL PRIMARY KEY,
1059 workstation INT NOT NULL REFERENCES actor.workstation (id)
1060 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1061 name TEXT NOT NULL REFERENCES config.workstation_setting_type (name)
1062 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1066 CREATE INDEX actor_workstation_setting_workstation_idx
1067 ON actor.workstation_setting (workstation);
1069 CREATE TYPE actor.cascade_setting_summary AS (
1072 has_org_setting BOOLEAN,
1073 has_user_setting BOOLEAN,
1074 has_workstation_setting BOOLEAN
1077 CREATE OR REPLACE FUNCTION actor.get_cascade_setting(
1078 setting_name TEXT, org_id INT, user_id INT, workstation_id INT)
1079 RETURNS actor.cascade_setting_summary AS
1083 summary actor.cascade_setting_summary;
1084 org_setting_type config.org_unit_setting_type%ROWTYPE;
1087 summary.name := setting_name;
1089 -- Collect the org setting type status first in case we exit early.
1090 -- The existance of an org setting type is not considered
1091 -- privileged information.
1092 SELECT INTO org_setting_type *
1093 FROM config.org_unit_setting_type WHERE name = setting_name;
1095 summary.has_org_setting := TRUE;
1097 summary.has_org_setting := FALSE;
1100 -- User and workstation settings have the same priority.
1101 -- Start with user settings since that's the simplest code path.
1102 -- The workstation_id is ignored if no user_id is provided.
1103 IF user_id IS NOT NULL THEN
1105 SELECT INTO summary.value value FROM actor.usr_setting
1106 WHERE usr = user_id AND name = setting_name;
1109 -- if we have a value, we have a setting type
1110 summary.has_user_setting := TRUE;
1112 IF workstation_id IS NOT NULL THEN
1113 -- Only inform the caller about the workstation
1114 -- setting type disposition when a workstation id is
1115 -- provided. Otherwise, it's NULL to indicate UNKNOWN.
1116 summary.has_workstation_setting := FALSE;
1122 -- no user setting value, but a setting type may exist
1123 SELECT INTO summary.has_user_setting EXISTS (
1124 SELECT TRUE FROM config.usr_setting_type
1125 WHERE name = setting_name
1128 IF workstation_id IS NOT NULL THEN
1130 IF NOT summary.has_user_setting THEN
1131 -- A workstation setting type may only exist when a user
1132 -- setting type does not.
1134 SELECT INTO summary.value value
1135 FROM actor.workstation_setting
1136 WHERE workstation = workstation_id AND name = setting_name;
1139 -- if we have a value, we have a setting type
1140 summary.has_workstation_setting := TRUE;
1144 -- no value, but a setting type may exist
1145 SELECT INTO summary.has_workstation_setting EXISTS (
1146 SELECT TRUE FROM config.workstation_setting_type
1147 WHERE name = setting_name
1151 -- Finally make use of the workstation to determine the org
1152 -- unit if none is provided.
1153 IF org_id IS NULL AND summary.has_org_setting THEN
1154 SELECT INTO org_id owning_lib
1155 FROM actor.workstation WHERE id = workstation_id;
1160 -- Some org unit settings are protected by a view permission.
1161 -- First see if we have any data that needs protecting, then
1162 -- check the permission if needed.
1164 IF NOT summary.has_org_setting THEN
1168 -- avoid putting the value into the summary until we confirm
1169 -- the value should be visible to the caller.
1170 SELECT INTO setting_value value
1171 FROM actor.org_unit_ancestor_setting(setting_name, org_id);
1174 -- No value found -- perm check is irrelevant.
1178 IF org_setting_type.view_perm IS NOT NULL THEN
1180 IF user_id IS NULL THEN
1181 RAISE NOTICE 'Perm check required but no user_id provided';
1185 IF NOT permission.usr_has_perm(
1186 user_id, (SELECT code FROM permission.perm_list
1187 WHERE id = org_setting_type.view_perm), org_id)
1189 RAISE NOTICE 'Perm check failed for user % on %',
1190 user_id, org_setting_type.view_perm;
1195 -- Perm check succeeded or was not necessary.
1196 summary.value := setting_value;
1199 $FUNC$ LANGUAGE PLPGSQL;
1202 CREATE OR REPLACE FUNCTION actor.get_cascade_setting_batch(
1203 setting_names TEXT[], org_id INT, user_id INT, workstation_id INT)
1204 RETURNS SETOF actor.cascade_setting_summary AS
1206 -- Returns a row per setting matching the setting name order. If no
1207 -- value is applied, NULL is returned to retain name-response ordering.
1210 summary actor.cascade_setting_summary;
1212 FOREACH setting_name IN ARRAY setting_names LOOP
1213 SELECT INTO summary * FROM actor.get_cascade_setting(
1214 setting_Name, org_id, user_id, workstation_id);
1215 RETURN NEXT summary;
1218 $FUNC$ LANGUAGE PLPGSQL;