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,
47 pref_first_given_name TEXT,
48 pref_second_given_name TEXT,
49 pref_family_name TEXT,
52 name_kw_tsvector TSVECTOR,
61 active BOOL NOT NULL DEFAULT TRUE,
62 master_account BOOL NOT NULL DEFAULT FALSE,
63 super_user BOOL NOT NULL DEFAULT FALSE,
64 barred BOOL NOT NULL DEFAULT FALSE,
65 deleted BOOL NOT NULL DEFAULT FALSE,
66 juvenile BOOL NOT NULL DEFAULT FALSE,
67 usrgroup SERIAL NOT NULL,
68 claims_returned_count INT NOT NULL DEFAULT 0,
69 credit_forward_balance NUMERIC(6,2) NOT NULL DEFAULT 0.00,
70 last_xact_id TEXT NOT NULL DEFAULT 'none',
72 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
73 expire_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT (now() + '3 years'::INTERVAL),
74 claims_never_checked_out_count INT NOT NULL DEFAULT 0,
75 last_update_time TIMESTAMP WITH TIME ZONE
77 COMMENT ON TABLE actor.usr IS $$
80 This table contains the core User objects that describe both
81 staff members and patrons. The difference between the two
82 types of users is based on the user's permissions.
85 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
86 CREATE INDEX actor_usr_usrgroup_idx ON actor.usr (usrgroup);
87 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
88 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
90 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name));
91 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name));
92 CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name));
93 CREATE INDEX actor_usr_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(first_given_name));
94 CREATE INDEX actor_usr_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(second_given_name));
95 CREATE INDEX actor_usr_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(family_name));
96 CREATE INDEX actor_usr_usrname_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(usrname));
97 CREATE INDEX actor_usr_guardian_idx ON actor.usr (evergreen.lowercase(guardian));
98 CREATE INDEX actor_usr_guardian_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(guardian));
100 CREATE INDEX actor_usr_pref_first_given_name_idx ON actor.usr (evergreen.lowercase(pref_first_given_name));
101 CREATE INDEX actor_usr_pref_second_given_name_idx ON actor.usr (evergreen.lowercase(pref_second_given_name));
102 CREATE INDEX actor_usr_pref_family_name_idx ON actor.usr (evergreen.lowercase(pref_family_name));
103 CREATE INDEX actor_usr_pref_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(pref_first_given_name));
104 CREATE INDEX actor_usr_pref_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(pref_second_given_name));
105 CREATE INDEX actor_usr_pref_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(pref_family_name));
107 CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname));
108 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
110 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
111 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
112 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
114 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
115 (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
117 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
118 (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
120 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
121 (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
123 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
124 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
126 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
128 NEW.passwd = MD5( NEW.passwd );
133 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
135 IF NEW.passwd <> OLD.passwd THEN
136 NEW.passwd = MD5( NEW.passwd );
142 CREATE OR REPLACE FUNCTION actor.au_updated()
143 RETURNS TRIGGER AS $$
145 NEW.last_update_time := now();
150 CREATE TRIGGER au_update_trig
151 BEFORE INSERT OR UPDATE ON actor.usr
152 FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
154 CREATE TRIGGER actor_crypt_pw_update_trigger
155 BEFORE UPDATE ON actor.usr FOR EACH ROW
156 EXECUTE PROCEDURE actor.crypt_pw_update ();
158 CREATE TRIGGER actor_crypt_pw_insert_trigger
159 BEFORE INSERT ON actor.usr FOR EACH ROW
160 EXECUTE PROCEDURE actor.crypt_pw_insert ();
162 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;
164 CREATE OR REPLACE FUNCTION actor.user_ingest_name_keywords()
165 RETURNS TRIGGER AS $func$
167 NEW.name_kw_tsvector := TO_TSVECTOR(
168 COALESCE(NEW.prefix, '') || ' ' ||
169 COALESCE(NEW.first_given_name, '') || ' ' ||
170 COALESCE(evergreen.unaccent_and_squash(NEW.first_given_name), '') || ' ' ||
171 COALESCE(NEW.second_given_name, '') || ' ' ||
172 COALESCE(evergreen.unaccent_and_squash(NEW.second_given_name), '') || ' ' ||
173 COALESCE(NEW.family_name, '') || ' ' ||
174 COALESCE(evergreen.unaccent_and_squash(NEW.family_name), '') || ' ' ||
175 COALESCE(NEW.suffix, '') || ' ' ||
176 COALESCE(NEW.pref_prefix, '') || ' ' ||
177 COALESCE(NEW.pref_first_given_name, '') || ' ' ||
178 COALESCE(evergreen.unaccent_and_squash(NEW.pref_first_given_name), '') || ' ' ||
179 COALESCE(NEW.pref_second_given_name, '') || ' ' ||
180 COALESCE(evergreen.unaccent_and_squash(NEW.pref_second_given_name), '') || ' ' ||
181 COALESCE(NEW.pref_family_name, '') || ' ' ||
182 COALESCE(evergreen.unaccent_and_squash(NEW.pref_family_name), '') || ' ' ||
183 COALESCE(NEW.pref_suffix, '') || ' ' ||
184 COALESCE(NEW.name_keywords, '')
188 $func$ LANGUAGE PLPGSQL;
190 -- Add after the batch upate above to avoid duplicate updates.
191 CREATE TRIGGER user_ingest_name_keywords_tgr
192 BEFORE INSERT OR UPDATE ON actor.usr
193 FOR EACH ROW EXECUTE PROCEDURE actor.user_ingest_name_keywords();
195 CREATE TABLE actor.usr_note (
196 id BIGSERIAL PRIMARY KEY,
197 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
198 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
199 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
200 pub BOOL NOT NULL DEFAULT FALSE,
204 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
205 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
207 CREATE TABLE actor.usr_setting (
208 id BIGSERIAL PRIMARY KEY,
209 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
210 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
212 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
214 COMMENT ON TABLE actor.usr_setting IS $$
217 This table contains any arbitrary settings that a client
218 program would like to save for a user.
221 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
223 CREATE TABLE actor.stat_cat_sip_fields (
224 field CHAR(2) PRIMARY KEY,
226 one_only BOOL NOT NULL DEFAULT FALSE
228 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
229 Actor Statistical Category SIP Fields
231 Contains the list of valid SIP Field identifiers for
232 Statistical Categories.
235 CREATE TABLE actor.stat_cat (
236 id SERIAL PRIMARY KEY,
239 opac_visible BOOL NOT NULL DEFAULT FALSE,
240 usr_summary BOOL NOT NULL DEFAULT FALSE,
241 sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
243 checkout_archive BOOL NOT NULL DEFAULT FALSE,
244 required BOOL NOT NULL DEFAULT FALSE,
245 allow_freetext BOOL NOT NULL DEFAULT TRUE,
246 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
248 COMMENT ON TABLE actor.stat_cat IS $$
249 User Statistical Catagories
251 Local data collected about Users is placed into a Statistical
252 Catagory. Here's where those catagories are defined.
256 CREATE TABLE actor.stat_cat_entry (
257 id SERIAL PRIMARY KEY,
258 stat_cat INT NOT NULL,
261 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
263 COMMENT ON TABLE actor.stat_cat_entry IS $$
264 User Statistical Catagory Entries
266 Local data collected about Users is placed into a Statistical
267 Catagory. Each library can create entries into any of its own
268 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
272 CREATE TABLE actor.stat_cat_entry_usr_map (
273 id BIGSERIAL PRIMARY KEY,
274 stat_cat_entry TEXT NOT NULL,
275 stat_cat INT NOT NULL,
276 target_usr INT NOT NULL,
277 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
279 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
280 Statistical Catagory Entry to User map
282 Records the stat_cat entries for each user.
285 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
287 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
289 sipfield actor.stat_cat_sip_fields%ROWTYPE;
292 IF NEW.sip_field IS NOT NULL THEN
293 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
294 IF sipfield.one_only THEN
295 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
296 IF use_count > 0 THEN
297 RAISE EXCEPTION 'Sip field cannot be used twice';
303 $func$ LANGUAGE PLPGSQL;
305 CREATE TRIGGER actor_stat_cat_sip_update_trigger
306 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
307 EXECUTE PROCEDURE actor.stat_cat_check();
309 CREATE TABLE actor.card (
310 id SERIAL PRIMARY KEY,
311 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
312 barcode TEXT NOT NULL UNIQUE,
313 active BOOL NOT NULL DEFAULT TRUE
315 COMMENT ON TABLE actor.card IS $$
318 Each User has one or more library cards. The current "main"
319 card is linked to here from the actor.usr table, and it is up
320 to the consortium policy whether more than one card can be
321 active for any one user at a given time.
324 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
325 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
327 CREATE TABLE actor.org_unit_type (
328 id SERIAL PRIMARY KEY,
330 opac_label TEXT NOT NULL,
332 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
333 can_have_vols BOOL NOT NULL DEFAULT TRUE,
334 can_have_users BOOL NOT NULL DEFAULT TRUE
336 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
338 CREATE TABLE actor.org_unit (
339 id SERIAL PRIMARY KEY,
340 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
341 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
346 shortname TEXT NOT NULL UNIQUE,
347 name TEXT NOT NULL UNIQUE,
350 opac_visible BOOL NOT NULL DEFAULT TRUE,
351 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
353 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
354 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
355 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
356 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
357 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
358 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
360 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
362 current_aou actor.org_unit%ROWTYPE;
368 seen_ous := ARRAY[NEW.id];
370 IF (TG_OP = 'UPDATE') THEN
371 IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
372 RETURN NEW; -- Doing an UPDATE with no change, just return it
377 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
378 RETURN NEW; -- No loop. Carry on.
380 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
381 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
384 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
385 seen_ous := seen_ous || current_aou.id;
386 depth_count := depth_count + 1;
387 IF depth_count = 100 THEN
388 RAISE 'OU CHECK TOO DEEP';
396 CREATE TRIGGER actor_org_unit_parent_protect_trigger
397 BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
398 EXECUTE PROCEDURE actor.org_unit_parent_protect ();
400 CREATE TABLE actor.org_lasso (
401 id SERIAL PRIMARY KEY,
403 global BOOL NOT NULL DEFAULT FALSE
406 CREATE TABLE actor.org_lasso_map (
407 id SERIAL PRIMARY KEY,
408 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
409 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
411 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
412 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
414 CREATE TABLE actor.org_unit_proximity (
415 id BIGSERIAL PRIMARY KEY,
420 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
422 CREATE TABLE actor.stat_cat_entry_default (
423 id SERIAL PRIMARY KEY,
424 stat_cat_entry INT NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
425 stat_cat INT NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
426 owner INT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
427 CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
429 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
430 User Statistical Category Default Entry
432 A library may choose one of the stat_cat entries to be the
437 CREATE TABLE actor.org_unit_proximity_adjustment (
438 id SERIAL PRIMARY KEY,
439 item_circ_lib INT REFERENCES actor.org_unit (id),
440 item_owning_lib INT REFERENCES actor.org_unit (id),
441 copy_location INT, -- REFERENCES asset.copy_location (id),
442 hold_pickup_lib INT REFERENCES actor.org_unit (id),
443 hold_request_lib INT REFERENCES actor.org_unit (id),
444 pos INT NOT NULL DEFAULT 0,
445 absolute_adjustment BOOL NOT NULL DEFAULT FALSE,
446 prox_adjustment NUMERIC,
447 circ_mod TEXT, -- REFERENCES config.circ_modifier (code),
448 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)
450 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
451 COALESCE(item_circ_lib, -1),
452 COALESCE(item_owning_lib, -1),
453 COALESCE(copy_location, -1),
454 COALESCE(hold_pickup_lib, -1),
455 COALESCE(hold_request_lib, -1),
456 COALESCE(circ_mod, ''),
459 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
460 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
461 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
462 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
463 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
464 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
466 CREATE TABLE actor.hours_of_operation (
467 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
468 dow_0_open TIME NOT NULL DEFAULT '09:00',
469 dow_0_close TIME NOT NULL DEFAULT '17:00',
470 dow_1_open TIME NOT NULL DEFAULT '09:00',
471 dow_1_close TIME NOT NULL DEFAULT '17:00',
472 dow_2_open TIME NOT NULL DEFAULT '09:00',
473 dow_2_close TIME NOT NULL DEFAULT '17:00',
474 dow_3_open TIME NOT NULL DEFAULT '09:00',
475 dow_3_close TIME NOT NULL DEFAULT '17:00',
476 dow_4_open TIME NOT NULL DEFAULT '09:00',
477 dow_4_close TIME NOT NULL DEFAULT '17:00',
478 dow_5_open TIME NOT NULL DEFAULT '09:00',
479 dow_5_close TIME NOT NULL DEFAULT '17:00',
480 dow_6_open TIME NOT NULL DEFAULT '09:00',
481 dow_6_close TIME NOT NULL DEFAULT '17:00'
483 COMMENT ON TABLE actor.hours_of_operation IS $$
484 When does this org_unit usually open and close? (Variations
485 are expressed in the actor.org_unit_closed table.)
487 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
488 When does this org_unit open on Monday?
490 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
491 When does this org_unit close on Monday?
493 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
494 When does this org_unit open on Tuesday?
496 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
497 When does this org_unit close on Tuesday?
499 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
500 When does this org_unit open on Wednesday?
502 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
503 When does this org_unit close on Wednesday?
505 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
506 When does this org_unit open on Thursday?
508 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
509 When does this org_unit close on Thursday?
511 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
512 When does this org_unit open on Friday?
514 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
515 When does this org_unit close on Friday?
517 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
518 When does this org_unit open on Saturday?
520 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
521 When does this org_unit close on Saturday?
523 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
524 When does this org_unit open on Sunday?
526 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
527 When does this org_unit close on Sunday?
530 CREATE TABLE actor.org_unit_closed (
531 id SERIAL PRIMARY KEY,
532 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
533 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
534 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
535 full_day BOOLEAN NOT NULL DEFAULT FALSE,
536 multi_day BOOLEAN NOT NULL DEFAULT FALSE,
540 -- Workstation registration...
541 CREATE TABLE actor.workstation (
542 id SERIAL PRIMARY KEY,
543 name TEXT NOT NULL UNIQUE,
544 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
547 CREATE TABLE actor.usr_org_unit_opt_in (
548 id SERIAL PRIMARY KEY,
549 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
550 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
551 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
552 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
553 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
554 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
556 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
558 CREATE TABLE actor.org_unit_setting (
559 id BIGSERIAL PRIMARY KEY,
560 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
561 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
563 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
564 CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
566 COMMENT ON TABLE actor.org_unit_setting IS $$
569 This table contains any arbitrary settings that a client
570 program would like to save for an org unit.
573 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
575 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
576 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
580 -- Check for which setting is being updated, and log it.
581 SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
583 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
587 $ous_change_log$ LANGUAGE plpgsql;
589 CREATE TRIGGER log_ous_change
590 BEFORE INSERT OR UPDATE ON actor.org_unit_setting
591 FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
593 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
597 -- Check for which setting is being updated, and log it.
598 SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
600 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
604 $ous_delete_log$ LANGUAGE plpgsql;
606 CREATE TRIGGER log_ous_del
607 BEFORE DELETE ON actor.org_unit_setting
608 FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
613 CREATE TABLE actor.usr_address (
614 id SERIAL PRIMARY KEY,
615 valid BOOL NOT NULL DEFAULT TRUE,
616 within_city_limits BOOL NOT NULL DEFAULT TRUE,
617 address_type TEXT NOT NULL DEFAULT 'MAILING',
618 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
619 street1 TEXT NOT NULL,
624 country TEXT NOT NULL,
625 post_code TEXT NOT NULL,
626 pending BOOL NOT NULL DEFAULT FALSE,
627 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
630 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
632 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
633 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
635 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
636 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
637 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
639 CREATE TABLE actor.usr_password_reset (
640 id SERIAL PRIMARY KEY,
642 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
643 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
644 has_been_reset BOOL NOT NULL DEFAULT false
646 COMMENT ON TABLE actor.usr_password_reset IS $$
647 Self-serve password reset requests
649 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
650 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
651 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
652 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
654 CREATE TABLE actor.org_address (
655 id SERIAL PRIMARY KEY,
656 valid BOOL NOT NULL DEFAULT TRUE,
657 address_type TEXT NOT NULL DEFAULT 'MAILING',
658 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
659 street1 TEXT NOT NULL,
664 country TEXT NOT NULL,
665 post_code TEXT NOT NULL,
669 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
671 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
672 SELECT SUBSTRING( $1, 1, 5);
675 CREATE TABLE actor.usr_standing_penalty (
676 id SERIAL PRIMARY KEY,
677 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
678 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
679 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
680 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
681 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
682 stop_date TIMESTAMP WITH TIME ZONE,
685 COMMENT ON TABLE actor.usr_standing_penalty IS $$
686 User standing penalties
689 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
690 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
693 CREATE TABLE actor.usr_saved_search (
694 id SERIAL PRIMARY KEY,
695 owner INT NOT NULL REFERENCES actor.usr (id)
697 DEFERRABLE INITIALLY DEFERRED,
699 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
700 query_text TEXT NOT NULL,
701 query_type TEXT NOT NULL
702 CONSTRAINT valid_query_text CHECK (
703 query_type IN ( 'URL' )) DEFAULT 'URL',
704 -- we may add other types someday
706 CONSTRAINT valid_target CHECK (
707 target IN ( 'record', 'metarecord', 'callnumber' )),
708 CONSTRAINT name_once_per_user UNIQUE (owner, name)
711 CREATE TABLE actor.address_alert (
712 id SERIAL PRIMARY KEY,
713 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
714 active BOOL NOT NULL DEFAULT TRUE,
715 match_all BOOL NOT NULL DEFAULT TRUE,
716 alert_message TEXT NOT NULL,
724 mailing_address BOOL NOT NULL DEFAULT FALSE,
725 billing_address BOOL NOT NULL DEFAULT FALSE
728 CREATE TABLE actor.usr_activity (
729 id BIGSERIAL PRIMARY KEY,
730 usr INT REFERENCES actor.usr (id) ON DELETE SET NULL,
731 etype INT NOT NULL REFERENCES config.usr_activity_type (id),
732 event_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
734 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
736 CREATE TABLE actor.toolbar (
737 id BIGSERIAL PRIMARY KEY,
738 ws INT REFERENCES actor.workstation (id) ON DELETE CASCADE,
739 org INT REFERENCES actor.org_unit (id) ON DELETE CASCADE,
740 usr INT REFERENCES actor.usr (id) ON DELETE CASCADE,
742 layout TEXT NOT NULL,
743 CONSTRAINT only_one_type CHECK (
744 (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
745 (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
746 (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
748 CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
750 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
751 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
752 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
754 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
755 CREATE TABLE actor.org_unit_custom_tree (
756 id SERIAL PRIMARY KEY,
757 active BOOLEAN DEFAULT FALSE,
758 purpose actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
761 CREATE TABLE actor.org_unit_custom_tree_node (
762 id SERIAL PRIMARY KEY,
763 tree INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
764 org_unit INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
765 parent_node INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
766 sibling_order INTEGER NOT NULL DEFAULT 0,
767 CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
770 CREATE TABLE actor.search_query (
771 id SERIAL PRIMARY KEY,
772 label TEXT NOT NULL, -- i18n
773 query_text TEXT NOT NULL -- QP text
776 CREATE TABLE actor.search_filter_group (
777 id SERIAL PRIMARY KEY,
778 owner INT NOT NULL REFERENCES actor.org_unit (id)
779 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
780 code TEXT NOT NULL, -- for CGI, etc.
781 label TEXT NOT NULL, -- i18n
782 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
783 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
784 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
787 CREATE TABLE actor.search_filter_group_entry (
788 id SERIAL PRIMARY KEY,
789 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
790 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
791 pos INT NOT NULL DEFAULT 0,
792 query INT NOT NULL REFERENCES actor.search_query(id)
793 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
794 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
797 CREATE TABLE actor.usr_message (
798 id SERIAL PRIMARY KEY,
799 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
801 message TEXT NOT NULL,
802 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
803 deleted BOOL NOT NULL DEFAULT FALSE,
804 read_date TIMESTAMP WITH TIME ZONE,
805 sending_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
807 CREATE INDEX aum_usr ON actor.usr_message (usr);
809 CREATE RULE protect_usr_message_delete AS
810 ON DELETE TO actor.usr_message DO INSTEAD (
811 UPDATE actor.usr_message
813 WHERE OLD.id = actor.usr_message.id
816 CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$
821 IF TG_OP = 'UPDATE' THEN
822 IF OLD.pub = TRUE THEN
827 SELECT INTO sending_ou aw.owning_lib
828 FROM auditor.get_audit_info() agai
829 JOIN actor.workstation aw ON (aw.id = agai.eg_ws);
830 IF sending_ou IS NULL THEN
831 SELECT INTO sending_ou home_ou
833 WHERE id = NEW.creator;
835 INSERT INTO actor.usr_message (usr, title, message, sending_lib)
836 VALUES (NEW.usr, NEW.title, NEW.value, sending_ou);
843 CREATE TRIGGER convert_usr_note_to_message_tgr
844 AFTER INSERT OR UPDATE ON actor.usr_note
845 FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message();
847 -- limited view to ensure that a library user who somehow
848 -- manages to figure out how to access pcrud cannot change
849 -- the text of messages sent them
850 CREATE VIEW actor.usr_message_limited
851 AS SELECT * FROM actor.usr_message;
853 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
855 IF TG_OP = 'UPDATE' THEN
856 UPDATE actor.usr_message
857 SET read_date = NEW.read_date,
858 deleted = NEW.deleted
866 CREATE TRIGGER restrict_usr_message_limited_tgr
867 INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
868 FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
870 CREATE TABLE actor.passwd_type (
871 code TEXT PRIMARY KEY,
872 name TEXT UNIQUE NOT NULL,
873 login BOOLEAN NOT NULL DEFAULT FALSE,
874 regex TEXT, -- pending
875 crypt_algo TEXT, -- e.g. 'bf'
877 -- gen_salt() iter count used with each new salt.
878 -- A non-NULL value for iter_count is our indication the
879 -- password is salted and encrypted via crypt()
880 iter_count INTEGER CHECK (iter_count IS NULL OR iter_count > 0)
883 CREATE TABLE actor.passwd (
884 id SERIAL PRIMARY KEY,
885 usr INTEGER NOT NULL REFERENCES actor.usr(id)
886 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
887 salt TEXT, -- will be NULL for non-crypt'ed passwords
888 passwd TEXT NOT NULL,
889 passwd_type TEXT NOT NULL REFERENCES actor.passwd_type(code)
890 DEFERRABLE INITIALLY DEFERRED,
891 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
892 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
893 CONSTRAINT passwd_type_once_per_user UNIQUE (usr, passwd_type)
896 CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT)
899 type_row actor.passwd_type%ROWTYPE;
901 /* Returns a new salt based on the passwd_type encryption settings.
902 * Returns NULL If the password type is not crypt()'ed.
905 SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;
908 RETURN EXCEPTION 'No such password type: %', pw_type;
911 IF type_row.iter_count IS NULL THEN
912 -- This password type is unsalted. That's OK.
916 RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
922 TODO: when a user changes their password in the application, the
923 app layer has access to the bare password. At that point, we have
924 the opportunity to store the new password without the MD5(MD5())
925 intermediate hashing. Do we care? We would need a way to indicate
926 which passwords have the legacy intermediate hashing and which don't
927 so the app layer would know whether it should perform the intermediate
928 hashing. In either event, with the exception of migrate_passwd(), the
929 DB functions know or care nothing about intermediate hashing. Every
930 password is just a value that may or may not be internally crypt'ed.
933 CREATE OR REPLACE FUNCTION actor.set_passwd(
934 pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL)
935 RETURNS BOOLEAN AS $$
940 /* Sets the password value, creating a new actor.passwd row if needed.
941 * If the password type supports it, the new_pass value is crypt()'ed.
942 * For crypt'ed passwords, the salt comes from one of 3 places in order:
943 * new_salt (if present), existing salt (if present), newly created
947 IF new_salt IS NOT NULL THEN
950 pw_salt := actor.get_salt(pw_usr, pw_type);
952 IF pw_salt IS NULL THEN
953 /* We have no salt for this user + type. Assume they want a
954 * new salt. If this type is unsalted, create_salt() will
956 pw_salt := actor.create_salt(pw_type);
960 IF pw_salt IS NULL THEN
961 pw_text := new_pass; -- unsalted, use as-is.
963 pw_text := CRYPT(new_pass, pw_salt);
967 SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
968 WHERE usr = pw_usr AND passwd_type = pw_type;
971 -- no password row exists for this user + type. Create one.
972 INSERT INTO actor.passwd (usr, passwd_type, salt, passwd)
973 VALUES (pw_usr, pw_type, pw_salt, pw_text);
980 CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT)
984 type_row actor.passwd_type%ROWTYPE;
986 /* Returns the salt for the requested user + type. If the password
987 * type of "main" is requested and no password exists in actor.passwd,
988 * the user's existing password is migrated and the new salt is returned.
989 * Returns NULL if the password type is not crypt'ed (iter_count is NULL).
992 SELECT INTO pw_salt salt FROM actor.passwd
993 WHERE usr = pw_usr AND passwd_type = pw_type;
999 IF pw_type = 'main' THEN
1000 -- Main password has not yet been migrated.
1001 -- Do it now and return the newly created salt.
1002 RETURN actor.migrate_passwd(pw_usr);
1005 -- We have no salt to return. actor.create_salt() needed.
1008 $$ LANGUAGE PLPGSQL;
1010 CREATE OR REPLACE FUNCTION
1011 actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$
1014 usr_row actor.usr%ROWTYPE;
1016 /* Migrates legacy actor.usr.passwd value to actor.passwd with
1017 * a password type 'main' and returns the new salt. For backwards
1018 * compatibility with existing CHAP-style API's, we perform a
1019 * layer of intermediate MD5(MD5()) hashing. This is intermediate
1020 * hashing is not required of other passwords.
1023 -- Avoid calling get_salt() here, because it may result in a
1024 -- migrate_passwd() call, creating a loop.
1025 SELECT INTO pw_salt salt FROM actor.passwd
1026 WHERE usr = pw_usr AND passwd_type = 'main';
1028 -- Only migrate passwords that have not already been migrated.
1033 SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
1035 pw_salt := actor.create_salt('main');
1037 PERFORM actor.set_passwd(
1038 pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
1040 -- clear the existing password
1041 UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
1045 $$ LANGUAGE PLPGSQL;
1047 CREATE OR REPLACE FUNCTION
1048 actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT)
1049 RETURNS BOOLEAN AS $$
1053 /* Returns TRUE if the password provided matches the in-db password.
1054 * If the password type is salted, we compare the output of CRYPT().
1055 * NOTE: test_passwd is MD5(salt || MD5(password)) for legacy
1059 SELECT INTO pw_salt salt FROM actor.passwd
1060 WHERE usr = pw_usr AND passwd_type = pw_type;
1067 IF pw_salt IS NULL THEN
1068 -- Password is unsalted, compare the un-CRYPT'ed values.
1070 SELECT TRUE FROM actor.passwd WHERE
1072 passwd_type = pw_type AND
1073 passwd = test_passwd
1078 SELECT TRUE FROM actor.passwd WHERE
1080 passwd_type = pw_type AND
1081 passwd = CRYPT(test_passwd, pw_salt)
1084 $$ STRICT LANGUAGE PLPGSQL;
1086 -- Remove all activity entries by activity type,
1087 -- except the most recent entry per user.
1088 CREATE OR REPLACE FUNCTION
1089 actor.purge_usr_activity_by_type(act_type INTEGER)
1094 FOR cur_usr IN SELECT DISTINCT(usr)
1095 FROM actor.usr_activity WHERE etype = act_type LOOP
1096 DELETE FROM actor.usr_activity WHERE id IN (
1098 FROM actor.usr_activity
1099 WHERE usr = cur_usr AND etype = act_type
1100 ORDER BY event_time DESC OFFSET 1
1104 END $$ LANGUAGE PLPGSQL;
1106 CREATE TABLE actor.workstation_setting (
1107 id SERIAL PRIMARY KEY,
1108 workstation INT NOT NULL REFERENCES actor.workstation (id)
1109 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1110 name TEXT NOT NULL REFERENCES config.workstation_setting_type (name)
1111 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1112 value JSON NOT NULL,
1113 CONSTRAINT ws_once_per_key UNIQUE (workstation, name)
1116 CREATE INDEX actor_workstation_setting_workstation_idx
1117 ON actor.workstation_setting (workstation);
1119 CREATE TYPE actor.cascade_setting_summary AS (
1122 has_org_setting BOOLEAN,
1123 has_user_setting BOOLEAN,
1124 has_workstation_setting BOOLEAN
1127 CREATE OR REPLACE FUNCTION actor.get_cascade_setting(
1128 setting_name TEXT, org_id INT, user_id INT, workstation_id INT)
1129 RETURNS actor.cascade_setting_summary AS
1133 summary actor.cascade_setting_summary;
1134 org_setting_type config.org_unit_setting_type%ROWTYPE;
1137 summary.name := setting_name;
1139 -- Collect the org setting type status first in case we exit early.
1140 -- The existance of an org setting type is not considered
1141 -- privileged information.
1142 SELECT INTO org_setting_type *
1143 FROM config.org_unit_setting_type WHERE name = setting_name;
1145 summary.has_org_setting := TRUE;
1147 summary.has_org_setting := FALSE;
1150 -- User and workstation settings have the same priority.
1151 -- Start with user settings since that's the simplest code path.
1152 -- The workstation_id is ignored if no user_id is provided.
1153 IF user_id IS NOT NULL THEN
1155 SELECT INTO summary.value value FROM actor.usr_setting
1156 WHERE usr = user_id AND name = setting_name;
1159 -- if we have a value, we have a setting type
1160 summary.has_user_setting := TRUE;
1162 IF workstation_id IS NOT NULL THEN
1163 -- Only inform the caller about the workstation
1164 -- setting type disposition when a workstation id is
1165 -- provided. Otherwise, it's NULL to indicate UNKNOWN.
1166 summary.has_workstation_setting := FALSE;
1172 -- no user setting value, but a setting type may exist
1173 SELECT INTO summary.has_user_setting EXISTS (
1174 SELECT TRUE FROM config.usr_setting_type
1175 WHERE name = setting_name
1178 IF workstation_id IS NOT NULL THEN
1180 IF NOT summary.has_user_setting THEN
1181 -- A workstation setting type may only exist when a user
1182 -- setting type does not.
1184 SELECT INTO summary.value value
1185 FROM actor.workstation_setting
1186 WHERE workstation = workstation_id AND name = setting_name;
1189 -- if we have a value, we have a setting type
1190 summary.has_workstation_setting := TRUE;
1194 -- no value, but a setting type may exist
1195 SELECT INTO summary.has_workstation_setting EXISTS (
1196 SELECT TRUE FROM config.workstation_setting_type
1197 WHERE name = setting_name
1201 -- Finally make use of the workstation to determine the org
1202 -- unit if none is provided.
1203 IF org_id IS NULL AND summary.has_org_setting THEN
1204 SELECT INTO org_id owning_lib
1205 FROM actor.workstation WHERE id = workstation_id;
1210 -- Some org unit settings are protected by a view permission.
1211 -- First see if we have any data that needs protecting, then
1212 -- check the permission if needed.
1214 IF NOT summary.has_org_setting THEN
1218 -- avoid putting the value into the summary until we confirm
1219 -- the value should be visible to the caller.
1220 SELECT INTO setting_value value
1221 FROM actor.org_unit_ancestor_setting(setting_name, org_id);
1224 -- No value found -- perm check is irrelevant.
1228 IF org_setting_type.view_perm IS NOT NULL THEN
1230 IF user_id IS NULL THEN
1231 RAISE NOTICE 'Perm check required but no user_id provided';
1235 IF NOT permission.usr_has_perm(
1236 user_id, (SELECT code FROM permission.perm_list
1237 WHERE id = org_setting_type.view_perm), org_id)
1239 RAISE NOTICE 'Perm check failed for user % on %',
1240 user_id, org_setting_type.view_perm;
1245 -- Perm check succeeded or was not necessary.
1246 summary.value := setting_value;
1249 $FUNC$ LANGUAGE PLPGSQL;
1252 CREATE OR REPLACE FUNCTION actor.get_cascade_setting_batch(
1253 setting_names TEXT[], org_id INT, user_id INT, workstation_id INT)
1254 RETURNS SETOF actor.cascade_setting_summary AS
1256 -- Returns a row per setting matching the setting name order. If no
1257 -- value is applied, NULL is returned to retain name-response ordering.
1260 summary actor.cascade_setting_summary;
1262 FOREACH setting_name IN ARRAY setting_names LOOP
1263 SELECT INTO summary * FROM actor.get_cascade_setting(
1264 setting_Name, org_id, user_id, workstation_id);
1265 RETURN NEXT summary;
1268 $FUNC$ LANGUAGE PLPGSQL;
1270 CREATE TABLE actor.usr_privacy_waiver (
1271 id BIGSERIAL PRIMARY KEY,
1272 usr BIGINT NOT NULL REFERENCES actor.usr(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1274 place_holds BOOL DEFAULT FALSE,
1275 pickup_holds BOOL DEFAULT FALSE,
1276 view_history BOOL DEFAULT FALSE,
1277 checkout_items BOOL DEFAULT FALSE
1279 CREATE INDEX actor_usr_privacy_waiver_usr_idx ON actor.usr_privacy_waiver (usr);