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,
46 pref_first_given_name TEXT,
47 pref_second_given_name TEXT,
48 pref_family_name TEXT,
51 name_kw_tsvector TSVECTOR,
60 active BOOL NOT NULL DEFAULT TRUE,
61 master_account BOOL NOT NULL DEFAULT FALSE,
62 super_user BOOL NOT NULL DEFAULT FALSE,
63 barred BOOL NOT NULL DEFAULT FALSE,
64 deleted BOOL NOT NULL DEFAULT FALSE,
65 juvenile BOOL NOT NULL DEFAULT FALSE,
66 usrgroup SERIAL NOT NULL,
67 claims_returned_count INT NOT NULL DEFAULT 0,
68 credit_forward_balance NUMERIC(6,2) NOT NULL DEFAULT 0.00,
69 last_xact_id TEXT NOT NULL DEFAULT 'none',
71 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
72 expire_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT (now() + '3 years'::INTERVAL),
73 claims_never_checked_out_count INT NOT NULL DEFAULT 0,
74 last_update_time TIMESTAMP WITH TIME ZONE
76 COMMENT ON TABLE actor.usr IS $$
79 This table contains the core User objects that describe both
80 staff members and patrons. The difference between the two
81 types of users is based on the user's permissions.
84 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
85 CREATE INDEX actor_usr_usrgroup_idx ON actor.usr (usrgroup);
86 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
87 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
89 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name));
90 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name));
91 CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name));
92 CREATE INDEX actor_usr_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(first_given_name));
93 CREATE INDEX actor_usr_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(second_given_name));
94 CREATE INDEX actor_usr_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(family_name));
95 CREATE INDEX actor_usr_usrname_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(usrname));
97 CREATE INDEX actor_usr_pref_first_given_name_idx ON actor.usr (evergreen.lowercase(pref_first_given_name));
98 CREATE INDEX actor_usr_pref_second_given_name_idx ON actor.usr (evergreen.lowercase(pref_second_given_name));
99 CREATE INDEX actor_usr_pref_family_name_idx ON actor.usr (evergreen.lowercase(pref_family_name));
100 CREATE INDEX actor_usr_pref_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(pref_first_given_name));
101 CREATE INDEX actor_usr_pref_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(pref_second_given_name));
102 CREATE INDEX actor_usr_pref_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(pref_family_name));
104 CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname));
105 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
107 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
108 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
109 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
111 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
112 (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
114 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
115 (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
117 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
118 (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
120 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
121 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
123 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
125 NEW.passwd = MD5( NEW.passwd );
130 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
132 IF NEW.passwd <> OLD.passwd THEN
133 NEW.passwd = MD5( NEW.passwd );
139 CREATE OR REPLACE FUNCTION actor.au_updated()
140 RETURNS TRIGGER AS $$
142 NEW.last_update_time := now();
147 CREATE TRIGGER au_update_trig
148 BEFORE INSERT OR UPDATE ON actor.usr
149 FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
151 CREATE TRIGGER actor_crypt_pw_update_trigger
152 BEFORE UPDATE ON actor.usr FOR EACH ROW
153 EXECUTE PROCEDURE actor.crypt_pw_update ();
155 CREATE TRIGGER actor_crypt_pw_insert_trigger
156 BEFORE INSERT ON actor.usr FOR EACH ROW
157 EXECUTE PROCEDURE actor.crypt_pw_insert ();
159 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;
161 CREATE OR REPLACE FUNCTION actor.user_ingest_name_keywords()
162 RETURNS TRIGGER AS $func$
164 NEW.name_kw_tsvector := TO_TSVECTOR(
165 COALESCE(NEW.prefix, '') || ' ' ||
166 COALESCE(NEW.first_given_name, '') || ' ' ||
167 COALESCE(evergreen.unaccent_and_squash(NEW.first_given_name), '') || ' ' ||
168 COALESCE(NEW.second_given_name, '') || ' ' ||
169 COALESCE(evergreen.unaccent_and_squash(NEW.second_given_name), '') || ' ' ||
170 COALESCE(NEW.family_name, '') || ' ' ||
171 COALESCE(evergreen.unaccent_and_squash(NEW.family_name), '') || ' ' ||
172 COALESCE(NEW.suffix, '') || ' ' ||
173 COALESCE(NEW.pref_prefix, '') || ' ' ||
174 COALESCE(NEW.pref_first_given_name, '') || ' ' ||
175 COALESCE(evergreen.unaccent_and_squash(NEW.pref_first_given_name), '') || ' ' ||
176 COALESCE(NEW.pref_second_given_name, '') || ' ' ||
177 COALESCE(evergreen.unaccent_and_squash(NEW.pref_second_given_name), '') || ' ' ||
178 COALESCE(NEW.pref_family_name, '') || ' ' ||
179 COALESCE(evergreen.unaccent_and_squash(NEW.pref_family_name), '') || ' ' ||
180 COALESCE(NEW.pref_suffix, '') || ' ' ||
181 COALESCE(NEW.name_keywords, '')
185 $func$ LANGUAGE PLPGSQL;
187 -- Add after the batch upate above to avoid duplicate updates.
188 CREATE TRIGGER user_ingest_name_keywords_tgr
189 BEFORE INSERT OR UPDATE ON actor.usr
190 FOR EACH ROW EXECUTE PROCEDURE actor.user_ingest_name_keywords();
192 CREATE TABLE actor.usr_note (
193 id BIGSERIAL PRIMARY KEY,
194 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
195 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
196 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
197 pub BOOL NOT NULL DEFAULT FALSE,
201 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
202 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
204 CREATE TABLE actor.usr_setting (
205 id BIGSERIAL PRIMARY KEY,
206 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
207 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
209 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
211 COMMENT ON TABLE actor.usr_setting IS $$
214 This table contains any arbitrary settings that a client
215 program would like to save for a user.
218 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
220 CREATE TABLE actor.stat_cat_sip_fields (
221 field CHAR(2) PRIMARY KEY,
223 one_only BOOL NOT NULL DEFAULT FALSE
225 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
226 Actor Statistical Category SIP Fields
228 Contains the list of valid SIP Field identifiers for
229 Statistical Categories.
232 CREATE TABLE actor.stat_cat (
233 id SERIAL PRIMARY KEY,
236 opac_visible BOOL NOT NULL DEFAULT FALSE,
237 usr_summary BOOL NOT NULL DEFAULT FALSE,
238 sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
240 checkout_archive BOOL NOT NULL DEFAULT FALSE,
241 required BOOL NOT NULL DEFAULT FALSE,
242 allow_freetext BOOL NOT NULL DEFAULT TRUE,
243 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
245 COMMENT ON TABLE actor.stat_cat IS $$
246 User Statistical Catagories
248 Local data collected about Users is placed into a Statistical
249 Catagory. Here's where those catagories are defined.
253 CREATE TABLE actor.stat_cat_entry (
254 id SERIAL PRIMARY KEY,
255 stat_cat INT NOT NULL,
258 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
260 COMMENT ON TABLE actor.stat_cat_entry IS $$
261 User Statistical Catagory Entries
263 Local data collected about Users is placed into a Statistical
264 Catagory. Each library can create entries into any of its own
265 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
269 CREATE TABLE actor.stat_cat_entry_usr_map (
270 id BIGSERIAL PRIMARY KEY,
271 stat_cat_entry TEXT NOT NULL,
272 stat_cat INT NOT NULL,
273 target_usr INT NOT NULL,
274 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
276 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
277 Statistical Catagory Entry to User map
279 Records the stat_cat entries for each user.
282 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
284 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
286 sipfield actor.stat_cat_sip_fields%ROWTYPE;
289 IF NEW.sip_field IS NOT NULL THEN
290 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
291 IF sipfield.one_only THEN
292 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
293 IF use_count > 0 THEN
294 RAISE EXCEPTION 'Sip field cannot be used twice';
300 $func$ LANGUAGE PLPGSQL;
302 CREATE TRIGGER actor_stat_cat_sip_update_trigger
303 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
304 EXECUTE PROCEDURE actor.stat_cat_check();
306 CREATE TABLE actor.card (
307 id SERIAL PRIMARY KEY,
308 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
309 barcode TEXT NOT NULL UNIQUE,
310 active BOOL NOT NULL DEFAULT TRUE
312 COMMENT ON TABLE actor.card IS $$
315 Each User has one or more library cards. The current "main"
316 card is linked to here from the actor.usr table, and it is up
317 to the consortium policy whether more than one card can be
318 active for any one user at a given time.
321 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
322 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
324 CREATE TABLE actor.org_unit_type (
325 id SERIAL PRIMARY KEY,
327 opac_label TEXT NOT NULL,
329 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
330 can_have_vols BOOL NOT NULL DEFAULT TRUE,
331 can_have_users BOOL NOT NULL DEFAULT TRUE
333 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
335 CREATE TABLE actor.org_unit (
336 id SERIAL PRIMARY KEY,
337 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
338 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
343 shortname TEXT NOT NULL UNIQUE,
344 name TEXT NOT NULL UNIQUE,
347 opac_visible BOOL NOT NULL DEFAULT TRUE,
348 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
350 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
351 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
352 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
353 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
354 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
355 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
357 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
359 current_aou actor.org_unit%ROWTYPE;
365 seen_ous := ARRAY[NEW.id];
367 IF (TG_OP = 'UPDATE') THEN
368 IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
369 RETURN NEW; -- Doing an UPDATE with no change, just return it
374 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
375 RETURN NEW; -- No loop. Carry on.
377 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
378 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
381 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
382 seen_ous := seen_ous || current_aou.id;
383 depth_count := depth_count + 1;
384 IF depth_count = 100 THEN
385 RAISE 'OU CHECK TOO DEEP';
393 CREATE TRIGGER actor_org_unit_parent_protect_trigger
394 BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
395 EXECUTE PROCEDURE actor.org_unit_parent_protect ();
397 CREATE TABLE actor.org_lasso (
398 id SERIAL PRIMARY KEY,
402 CREATE TABLE actor.org_lasso_map (
403 id SERIAL PRIMARY KEY,
404 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
405 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
407 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
408 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
410 CREATE TABLE actor.org_unit_proximity (
411 id BIGSERIAL PRIMARY KEY,
416 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
418 CREATE TABLE actor.stat_cat_entry_default (
419 id SERIAL PRIMARY KEY,
420 stat_cat_entry INT NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
421 stat_cat INT NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
422 owner INT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
423 CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
425 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
426 User Statistical Category Default Entry
428 A library may choose one of the stat_cat entries to be the
433 CREATE TABLE actor.org_unit_proximity_adjustment (
434 id SERIAL PRIMARY KEY,
435 item_circ_lib INT REFERENCES actor.org_unit (id),
436 item_owning_lib INT REFERENCES actor.org_unit (id),
437 copy_location INT, -- REFERENCES asset.copy_location (id),
438 hold_pickup_lib INT REFERENCES actor.org_unit (id),
439 hold_request_lib INT REFERENCES actor.org_unit (id),
440 pos INT NOT NULL DEFAULT 0,
441 absolute_adjustment BOOL NOT NULL DEFAULT FALSE,
442 prox_adjustment NUMERIC,
443 circ_mod TEXT, -- REFERENCES config.circ_modifier (code),
444 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)
446 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
447 COALESCE(item_circ_lib, -1),
448 COALESCE(item_owning_lib, -1),
449 COALESCE(copy_location, -1),
450 COALESCE(hold_pickup_lib, -1),
451 COALESCE(hold_request_lib, -1),
452 COALESCE(circ_mod, ''),
455 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
456 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
457 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
458 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
459 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
460 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
462 CREATE TABLE actor.hours_of_operation (
463 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
464 dow_0_open TIME NOT NULL DEFAULT '09:00',
465 dow_0_close TIME NOT NULL DEFAULT '17:00',
466 dow_1_open TIME NOT NULL DEFAULT '09:00',
467 dow_1_close TIME NOT NULL DEFAULT '17:00',
468 dow_2_open TIME NOT NULL DEFAULT '09:00',
469 dow_2_close TIME NOT NULL DEFAULT '17:00',
470 dow_3_open TIME NOT NULL DEFAULT '09:00',
471 dow_3_close TIME NOT NULL DEFAULT '17:00',
472 dow_4_open TIME NOT NULL DEFAULT '09:00',
473 dow_4_close TIME NOT NULL DEFAULT '17:00',
474 dow_5_open TIME NOT NULL DEFAULT '09:00',
475 dow_5_close TIME NOT NULL DEFAULT '17:00',
476 dow_6_open TIME NOT NULL DEFAULT '09:00',
477 dow_6_close TIME NOT NULL DEFAULT '17:00'
479 COMMENT ON TABLE actor.hours_of_operation IS $$
480 When does this org_unit usually open and close? (Variations
481 are expressed in the actor.org_unit_closed table.)
483 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
484 When does this org_unit open on Monday?
486 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
487 When does this org_unit close on Monday?
489 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
490 When does this org_unit open on Tuesday?
492 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
493 When does this org_unit close on Tuesday?
495 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
496 When does this org_unit open on Wednesday?
498 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
499 When does this org_unit close on Wednesday?
501 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
502 When does this org_unit open on Thursday?
504 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
505 When does this org_unit close on Thursday?
507 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
508 When does this org_unit open on Friday?
510 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
511 When does this org_unit close on Friday?
513 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
514 When does this org_unit open on Saturday?
516 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
517 When does this org_unit close on Saturday?
519 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
520 When does this org_unit open on Sunday?
522 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
523 When does this org_unit close on Sunday?
526 CREATE TABLE actor.org_unit_closed (
527 id SERIAL PRIMARY KEY,
528 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
529 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
530 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
531 full_day BOOLEAN NOT NULL DEFAULT FALSE,
532 multi_day BOOLEAN NOT NULL DEFAULT FALSE,
536 -- Workstation registration...
537 CREATE TABLE actor.workstation (
538 id SERIAL PRIMARY KEY,
539 name TEXT NOT NULL UNIQUE,
540 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
543 CREATE TABLE actor.usr_org_unit_opt_in (
544 id SERIAL PRIMARY KEY,
545 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
546 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
547 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
548 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
549 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
550 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
552 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
554 CREATE TABLE actor.org_unit_setting (
555 id BIGSERIAL PRIMARY KEY,
556 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
557 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
559 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
560 CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
562 COMMENT ON TABLE actor.org_unit_setting IS $$
565 This table contains any arbitrary settings that a client
566 program would like to save for an org unit.
569 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
571 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
572 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
576 -- Check for which setting is being updated, and log it.
577 SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
579 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
583 $ous_change_log$ LANGUAGE plpgsql;
585 CREATE TRIGGER log_ous_change
586 BEFORE INSERT OR UPDATE ON actor.org_unit_setting
587 FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
589 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
593 -- Check for which setting is being updated, and log it.
594 SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
596 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
600 $ous_delete_log$ LANGUAGE plpgsql;
602 CREATE TRIGGER log_ous_del
603 BEFORE DELETE ON actor.org_unit_setting
604 FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
609 CREATE TABLE actor.usr_address (
610 id SERIAL PRIMARY KEY,
611 valid BOOL NOT NULL DEFAULT TRUE,
612 within_city_limits BOOL NOT NULL DEFAULT TRUE,
613 address_type TEXT NOT NULL DEFAULT 'MAILING',
614 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
615 street1 TEXT NOT NULL,
620 country TEXT NOT NULL,
621 post_code TEXT NOT NULL,
622 pending BOOL NOT NULL DEFAULT FALSE,
623 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
626 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
628 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
629 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
631 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
632 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
633 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
635 CREATE TABLE actor.usr_password_reset (
636 id SERIAL PRIMARY KEY,
638 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
639 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
640 has_been_reset BOOL NOT NULL DEFAULT false
642 COMMENT ON TABLE actor.usr_password_reset IS $$
643 Self-serve password reset requests
645 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
646 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
647 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
648 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
650 CREATE TABLE actor.org_address (
651 id SERIAL PRIMARY KEY,
652 valid BOOL NOT NULL DEFAULT TRUE,
653 address_type TEXT NOT NULL DEFAULT 'MAILING',
654 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
655 street1 TEXT NOT NULL,
660 country TEXT NOT NULL,
661 post_code TEXT NOT NULL,
665 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
667 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
668 SELECT SUBSTRING( $1, 1, 5);
671 CREATE TABLE actor.usr_standing_penalty (
672 id SERIAL PRIMARY KEY,
673 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
674 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
675 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
676 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
677 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
678 stop_date TIMESTAMP WITH TIME ZONE,
681 COMMENT ON TABLE actor.usr_standing_penalty IS $$
682 User standing penalties
685 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
686 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
689 CREATE TABLE actor.usr_saved_search (
690 id SERIAL PRIMARY KEY,
691 owner INT NOT NULL REFERENCES actor.usr (id)
693 DEFERRABLE INITIALLY DEFERRED,
695 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
696 query_text TEXT NOT NULL,
697 query_type TEXT NOT NULL
698 CONSTRAINT valid_query_text CHECK (
699 query_type IN ( 'URL' )) DEFAULT 'URL',
700 -- we may add other types someday
702 CONSTRAINT valid_target CHECK (
703 target IN ( 'record', 'metarecord', 'callnumber' )),
704 CONSTRAINT name_once_per_user UNIQUE (owner, name)
707 CREATE TABLE actor.address_alert (
708 id SERIAL PRIMARY KEY,
709 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
710 active BOOL NOT NULL DEFAULT TRUE,
711 match_all BOOL NOT NULL DEFAULT TRUE,
712 alert_message TEXT NOT NULL,
720 mailing_address BOOL NOT NULL DEFAULT FALSE,
721 billing_address BOOL NOT NULL DEFAULT FALSE
724 CREATE TABLE actor.usr_activity (
725 id BIGSERIAL PRIMARY KEY,
726 usr INT REFERENCES actor.usr (id) ON DELETE SET NULL,
727 etype INT NOT NULL REFERENCES config.usr_activity_type (id),
728 event_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
730 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
732 CREATE TABLE actor.toolbar (
733 id BIGSERIAL PRIMARY KEY,
734 ws INT REFERENCES actor.workstation (id) ON DELETE CASCADE,
735 org INT REFERENCES actor.org_unit (id) ON DELETE CASCADE,
736 usr INT REFERENCES actor.usr (id) ON DELETE CASCADE,
738 layout TEXT NOT NULL,
739 CONSTRAINT only_one_type CHECK (
740 (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
741 (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
742 (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
744 CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
746 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
747 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
748 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
750 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
751 CREATE TABLE actor.org_unit_custom_tree (
752 id SERIAL PRIMARY KEY,
753 active BOOLEAN DEFAULT FALSE,
754 purpose actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
757 CREATE TABLE actor.org_unit_custom_tree_node (
758 id SERIAL PRIMARY KEY,
759 tree INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
760 org_unit INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
761 parent_node INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
762 sibling_order INTEGER NOT NULL DEFAULT 0,
763 CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
766 CREATE TABLE actor.search_query (
767 id SERIAL PRIMARY KEY,
768 label TEXT NOT NULL, -- i18n
769 query_text TEXT NOT NULL -- QP text
772 CREATE TABLE actor.search_filter_group (
773 id SERIAL PRIMARY KEY,
774 owner INT NOT NULL REFERENCES actor.org_unit (id)
775 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
776 code TEXT NOT NULL, -- for CGI, etc.
777 label TEXT NOT NULL, -- i18n
778 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
779 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
780 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
783 CREATE TABLE actor.search_filter_group_entry (
784 id SERIAL PRIMARY KEY,
785 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
786 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
787 pos INT NOT NULL DEFAULT 0,
788 query INT NOT NULL REFERENCES actor.search_query(id)
789 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
790 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
793 CREATE TABLE actor.usr_message (
794 id SERIAL PRIMARY KEY,
795 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
797 message TEXT NOT NULL,
798 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
799 deleted BOOL NOT NULL DEFAULT FALSE,
800 read_date TIMESTAMP WITH TIME ZONE,
801 sending_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
803 CREATE INDEX aum_usr ON actor.usr_message (usr);
805 CREATE RULE protect_usr_message_delete AS
806 ON DELETE TO actor.usr_message DO INSTEAD (
807 UPDATE actor.usr_message
809 WHERE OLD.id = actor.usr_message.id
812 CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$
817 IF TG_OP = 'UPDATE' THEN
818 IF OLD.pub = TRUE THEN
823 SELECT INTO sending_ou aw.owning_lib
824 FROM auditor.get_audit_info() agai
825 JOIN actor.workstation aw ON (aw.id = agai.eg_ws);
826 IF sending_ou IS NULL THEN
827 SELECT INTO sending_ou home_ou
829 WHERE id = NEW.creator;
831 INSERT INTO actor.usr_message (usr, title, message, sending_lib)
832 VALUES (NEW.usr, NEW.title, NEW.value, sending_ou);
839 CREATE TRIGGER convert_usr_note_to_message_tgr
840 AFTER INSERT OR UPDATE ON actor.usr_note
841 FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message();
843 -- limited view to ensure that a library user who somehow
844 -- manages to figure out how to access pcrud cannot change
845 -- the text of messages sent them
846 CREATE VIEW actor.usr_message_limited
847 AS SELECT * FROM actor.usr_message;
849 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
851 IF TG_OP = 'UPDATE' THEN
852 UPDATE actor.usr_message
853 SET read_date = NEW.read_date,
854 deleted = NEW.deleted
862 CREATE TRIGGER restrict_usr_message_limited_tgr
863 INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
864 FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
866 CREATE TABLE actor.passwd_type (
867 code TEXT PRIMARY KEY,
868 name TEXT UNIQUE NOT NULL,
869 login BOOLEAN NOT NULL DEFAULT FALSE,
870 regex TEXT, -- pending
871 crypt_algo TEXT, -- e.g. 'bf'
873 -- gen_salt() iter count used with each new salt.
874 -- A non-NULL value for iter_count is our indication the
875 -- password is salted and encrypted via crypt()
876 iter_count INTEGER CHECK (iter_count IS NULL OR iter_count > 0)
879 CREATE TABLE actor.passwd (
880 id SERIAL PRIMARY KEY,
881 usr INTEGER NOT NULL REFERENCES actor.usr(id)
882 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
883 salt TEXT, -- will be NULL for non-crypt'ed passwords
884 passwd TEXT NOT NULL,
885 passwd_type TEXT NOT NULL REFERENCES actor.passwd_type(code)
886 DEFERRABLE INITIALLY DEFERRED,
887 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
888 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
889 CONSTRAINT passwd_type_once_per_user UNIQUE (usr, passwd_type)
892 CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT)
895 type_row actor.passwd_type%ROWTYPE;
897 /* Returns a new salt based on the passwd_type encryption settings.
898 * Returns NULL If the password type is not crypt()'ed.
901 SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;
904 RETURN EXCEPTION 'No such password type: %', pw_type;
907 IF type_row.iter_count IS NULL THEN
908 -- This password type is unsalted. That's OK.
912 RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
918 TODO: when a user changes their password in the application, the
919 app layer has access to the bare password. At that point, we have
920 the opportunity to store the new password without the MD5(MD5())
921 intermediate hashing. Do we care? We would need a way to indicate
922 which passwords have the legacy intermediate hashing and which don't
923 so the app layer would know whether it should perform the intermediate
924 hashing. In either event, with the exception of migrate_passwd(), the
925 DB functions know or care nothing about intermediate hashing. Every
926 password is just a value that may or may not be internally crypt'ed.
929 CREATE OR REPLACE FUNCTION actor.set_passwd(
930 pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL)
931 RETURNS BOOLEAN AS $$
936 /* Sets the password value, creating a new actor.passwd row if needed.
937 * If the password type supports it, the new_pass value is crypt()'ed.
938 * For crypt'ed passwords, the salt comes from one of 3 places in order:
939 * new_salt (if present), existing salt (if present), newly created
943 IF new_salt IS NOT NULL THEN
946 pw_salt := actor.get_salt(pw_usr, pw_type);
948 IF pw_salt IS NULL THEN
949 /* We have no salt for this user + type. Assume they want a
950 * new salt. If this type is unsalted, create_salt() will
952 pw_salt := actor.create_salt(pw_type);
956 IF pw_salt IS NULL THEN
957 pw_text := new_pass; -- unsalted, use as-is.
959 pw_text := CRYPT(new_pass, pw_salt);
963 SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
964 WHERE usr = pw_usr AND passwd_type = pw_type;
967 -- no password row exists for this user + type. Create one.
968 INSERT INTO actor.passwd (usr, passwd_type, salt, passwd)
969 VALUES (pw_usr, pw_type, pw_salt, pw_text);
976 CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT)
980 type_row actor.passwd_type%ROWTYPE;
982 /* Returns the salt for the requested user + type. If the password
983 * type of "main" is requested and no password exists in actor.passwd,
984 * the user's existing password is migrated and the new salt is returned.
985 * Returns NULL if the password type is not crypt'ed (iter_count is NULL).
988 SELECT INTO pw_salt salt FROM actor.passwd
989 WHERE usr = pw_usr AND passwd_type = pw_type;
995 IF pw_type = 'main' THEN
996 -- Main password has not yet been migrated.
997 -- Do it now and return the newly created salt.
998 RETURN actor.migrate_passwd(pw_usr);
1001 -- We have no salt to return. actor.create_salt() needed.
1004 $$ LANGUAGE PLPGSQL;
1006 CREATE OR REPLACE FUNCTION
1007 actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$
1010 usr_row actor.usr%ROWTYPE;
1012 /* Migrates legacy actor.usr.passwd value to actor.passwd with
1013 * a password type 'main' and returns the new salt. For backwards
1014 * compatibility with existing CHAP-style API's, we perform a
1015 * layer of intermediate MD5(MD5()) hashing. This is intermediate
1016 * hashing is not required of other passwords.
1019 -- Avoid calling get_salt() here, because it may result in a
1020 -- migrate_passwd() call, creating a loop.
1021 SELECT INTO pw_salt salt FROM actor.passwd
1022 WHERE usr = pw_usr AND passwd_type = 'main';
1024 -- Only migrate passwords that have not already been migrated.
1029 SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
1031 pw_salt := actor.create_salt('main');
1033 PERFORM actor.set_passwd(
1034 pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
1036 -- clear the existing password
1037 UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
1041 $$ LANGUAGE PLPGSQL;
1043 CREATE OR REPLACE FUNCTION
1044 actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT)
1045 RETURNS BOOLEAN AS $$
1049 /* Returns TRUE if the password provided matches the in-db password.
1050 * If the password type is salted, we compare the output of CRYPT().
1051 * NOTE: test_passwd is MD5(salt || MD5(password)) for legacy
1055 SELECT INTO pw_salt salt FROM actor.passwd
1056 WHERE usr = pw_usr AND passwd_type = pw_type;
1063 IF pw_salt IS NULL THEN
1064 -- Password is unsalted, compare the un-CRYPT'ed values.
1066 SELECT TRUE FROM actor.passwd WHERE
1068 passwd_type = pw_type AND
1069 passwd = test_passwd
1074 SELECT TRUE FROM actor.passwd WHERE
1076 passwd_type = pw_type AND
1077 passwd = CRYPT(test_passwd, pw_salt)
1080 $$ STRICT LANGUAGE PLPGSQL;
1082 -- Remove all activity entries by activity type,
1083 -- except the most recent entry per user.
1084 CREATE OR REPLACE FUNCTION
1085 actor.purge_usr_activity_by_type(act_type INTEGER)
1090 FOR cur_usr IN SELECT DISTINCT(usr)
1091 FROM actor.usr_activity WHERE etype = act_type LOOP
1092 DELETE FROM actor.usr_activity WHERE id IN (
1094 FROM actor.usr_activity
1095 WHERE usr = cur_usr AND etype = act_type
1096 ORDER BY event_time DESC OFFSET 1
1100 END $$ LANGUAGE PLPGSQL;
1102 CREATE TABLE actor.workstation_setting (
1103 id SERIAL PRIMARY KEY,
1104 workstation INT NOT NULL REFERENCES actor.workstation (id)
1105 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1106 name TEXT NOT NULL REFERENCES config.workstation_setting_type (name)
1107 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1111 CREATE INDEX actor_workstation_setting_workstation_idx
1112 ON actor.workstation_setting (workstation);
1114 CREATE TYPE actor.cascade_setting_summary AS (
1117 has_org_setting BOOLEAN,
1118 has_user_setting BOOLEAN,
1119 has_workstation_setting BOOLEAN
1122 CREATE OR REPLACE FUNCTION actor.get_cascade_setting(
1123 setting_name TEXT, org_id INT, user_id INT, workstation_id INT)
1124 RETURNS actor.cascade_setting_summary AS
1128 summary actor.cascade_setting_summary;
1129 org_setting_type config.org_unit_setting_type%ROWTYPE;
1132 summary.name := setting_name;
1134 -- Collect the org setting type status first in case we exit early.
1135 -- The existance of an org setting type is not considered
1136 -- privileged information.
1137 SELECT INTO org_setting_type *
1138 FROM config.org_unit_setting_type WHERE name = setting_name;
1140 summary.has_org_setting := TRUE;
1142 summary.has_org_setting := FALSE;
1145 -- User and workstation settings have the same priority.
1146 -- Start with user settings since that's the simplest code path.
1147 -- The workstation_id is ignored if no user_id is provided.
1148 IF user_id IS NOT NULL THEN
1150 SELECT INTO summary.value value FROM actor.usr_setting
1151 WHERE usr = user_id AND name = setting_name;
1154 -- if we have a value, we have a setting type
1155 summary.has_user_setting := TRUE;
1157 IF workstation_id IS NOT NULL THEN
1158 -- Only inform the caller about the workstation
1159 -- setting type disposition when a workstation id is
1160 -- provided. Otherwise, it's NULL to indicate UNKNOWN.
1161 summary.has_workstation_setting := FALSE;
1167 -- no user setting value, but a setting type may exist
1168 SELECT INTO summary.has_user_setting EXISTS (
1169 SELECT TRUE FROM config.usr_setting_type
1170 WHERE name = setting_name
1173 IF workstation_id IS NOT NULL THEN
1175 IF NOT summary.has_user_setting THEN
1176 -- A workstation setting type may only exist when a user
1177 -- setting type does not.
1179 SELECT INTO summary.value value
1180 FROM actor.workstation_setting
1181 WHERE workstation = workstation_id AND name = setting_name;
1184 -- if we have a value, we have a setting type
1185 summary.has_workstation_setting := TRUE;
1189 -- no value, but a setting type may exist
1190 SELECT INTO summary.has_workstation_setting EXISTS (
1191 SELECT TRUE FROM config.workstation_setting_type
1192 WHERE name = setting_name
1196 -- Finally make use of the workstation to determine the org
1197 -- unit if none is provided.
1198 IF org_id IS NULL AND summary.has_org_setting THEN
1199 SELECT INTO org_id owning_lib
1200 FROM actor.workstation WHERE id = workstation_id;
1205 -- Some org unit settings are protected by a view permission.
1206 -- First see if we have any data that needs protecting, then
1207 -- check the permission if needed.
1209 IF NOT summary.has_org_setting THEN
1213 -- avoid putting the value into the summary until we confirm
1214 -- the value should be visible to the caller.
1215 SELECT INTO setting_value value
1216 FROM actor.org_unit_ancestor_setting(setting_name, org_id);
1219 -- No value found -- perm check is irrelevant.
1223 IF org_setting_type.view_perm IS NOT NULL THEN
1225 IF user_id IS NULL THEN
1226 RAISE NOTICE 'Perm check required but no user_id provided';
1230 IF NOT permission.usr_has_perm(
1231 user_id, (SELECT code FROM permission.perm_list
1232 WHERE id = org_setting_type.view_perm), org_id)
1234 RAISE NOTICE 'Perm check failed for user % on %',
1235 user_id, org_setting_type.view_perm;
1240 -- Perm check succeeded or was not necessary.
1241 summary.value := setting_value;
1244 $FUNC$ LANGUAGE PLPGSQL;
1247 CREATE OR REPLACE FUNCTION actor.get_cascade_setting_batch(
1248 setting_names TEXT[], org_id INT, user_id INT, workstation_id INT)
1249 RETURNS SETOF actor.cascade_setting_summary AS
1251 -- Returns a row per setting matching the setting name order. If no
1252 -- value is applied, NULL is returned to retain name-response ordering.
1255 summary actor.cascade_setting_summary;
1257 FOREACH setting_name IN ARRAY setting_names LOOP
1258 SELECT INTO summary * FROM actor.get_cascade_setting(
1259 setting_Name, org_id, user_id, workstation_id);
1260 RETURN NEXT summary;
1263 $FUNC$ LANGUAGE PLPGSQL;