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',
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,
75 locale TEXT REFERENCES config.i18n_locale(code) INITIALLY DEFERRED
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_setting (
196 id BIGSERIAL PRIMARY KEY,
197 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
198 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
200 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
202 COMMENT ON TABLE actor.usr_setting IS $$
205 This table contains any arbitrary settings that a client
206 program would like to save for a user.
209 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
211 CREATE TABLE actor.stat_cat_sip_fields (
212 field CHAR(2) PRIMARY KEY,
214 one_only BOOL NOT NULL DEFAULT FALSE
216 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
217 Actor Statistical Category SIP Fields
219 Contains the list of valid SIP Field identifiers for
220 Statistical Categories.
223 CREATE TABLE actor.stat_cat (
224 id SERIAL PRIMARY KEY,
227 opac_visible BOOL NOT NULL DEFAULT FALSE,
228 usr_summary BOOL NOT NULL DEFAULT FALSE,
229 sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
231 checkout_archive BOOL NOT NULL DEFAULT FALSE,
232 required BOOL NOT NULL DEFAULT FALSE,
233 allow_freetext BOOL NOT NULL DEFAULT TRUE,
234 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
236 COMMENT ON TABLE actor.stat_cat IS $$
237 User Statistical Catagories
239 Local data collected about Users is placed into a Statistical
240 Catagory. Here's where those catagories are defined.
244 CREATE TABLE actor.stat_cat_entry (
245 id SERIAL PRIMARY KEY,
246 stat_cat INT NOT NULL,
249 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
251 COMMENT ON TABLE actor.stat_cat_entry IS $$
252 User Statistical Catagory Entries
254 Local data collected about Users is placed into a Statistical
255 Catagory. Each library can create entries into any of its own
256 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
260 CREATE TABLE actor.stat_cat_entry_usr_map (
261 id BIGSERIAL PRIMARY KEY,
262 stat_cat_entry TEXT NOT NULL,
263 stat_cat INT NOT NULL,
264 target_usr INT NOT NULL,
265 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
267 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
268 Statistical Catagory Entry to User map
270 Records the stat_cat entries for each user.
273 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
275 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
277 sipfield actor.stat_cat_sip_fields%ROWTYPE;
280 IF NEW.sip_field IS NOT NULL THEN
281 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
282 IF sipfield.one_only THEN
283 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
284 IF use_count > 0 THEN
285 RAISE EXCEPTION 'Sip field cannot be used twice';
291 $func$ LANGUAGE PLPGSQL;
293 CREATE TRIGGER actor_stat_cat_sip_update_trigger
294 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
295 EXECUTE PROCEDURE actor.stat_cat_check();
297 CREATE TABLE actor.card (
298 id SERIAL PRIMARY KEY,
299 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
300 barcode TEXT NOT NULL UNIQUE,
301 active BOOL NOT NULL DEFAULT TRUE
303 COMMENT ON TABLE actor.card IS $$
306 Each User has one or more library cards. The current "main"
307 card is linked to here from the actor.usr table, and it is up
308 to the consortium policy whether more than one card can be
309 active for any one user at a given time.
312 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
313 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
315 CREATE TABLE actor.org_unit_type (
316 id SERIAL PRIMARY KEY,
318 opac_label TEXT NOT NULL,
320 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
321 can_have_vols BOOL NOT NULL DEFAULT TRUE,
322 can_have_users BOOL NOT NULL DEFAULT TRUE
324 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
326 CREATE TABLE actor.org_unit (
327 id SERIAL PRIMARY KEY,
328 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
329 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
334 shortname TEXT NOT NULL UNIQUE,
335 name TEXT NOT NULL UNIQUE,
338 opac_visible BOOL NOT NULL DEFAULT TRUE,
339 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
341 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
342 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
343 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
344 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
345 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
346 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
348 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
350 current_aou actor.org_unit%ROWTYPE;
356 seen_ous := ARRAY[NEW.id];
358 IF (TG_OP = 'UPDATE') THEN
359 IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
360 RETURN NEW; -- Doing an UPDATE with no change, just return it
365 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
366 RETURN NEW; -- No loop. Carry on.
368 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
369 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
372 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
373 seen_ous := seen_ous || current_aou.id;
374 depth_count := depth_count + 1;
375 IF depth_count = 100 THEN
376 RAISE 'OU CHECK TOO DEEP';
384 CREATE TRIGGER actor_org_unit_parent_protect_trigger
385 BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
386 EXECUTE PROCEDURE actor.org_unit_parent_protect ();
388 CREATE TABLE actor.org_lasso (
389 id SERIAL PRIMARY KEY,
391 global BOOL NOT NULL DEFAULT FALSE
394 CREATE TABLE actor.org_lasso_map (
395 id SERIAL PRIMARY KEY,
396 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
397 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
399 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
400 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
402 CREATE TABLE actor.org_unit_proximity (
403 id BIGSERIAL PRIMARY KEY,
408 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
410 CREATE TABLE actor.stat_cat_entry_default (
411 id SERIAL PRIMARY KEY,
412 stat_cat_entry INT NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
413 stat_cat INT NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
414 owner INT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
415 CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
417 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
418 User Statistical Category Default Entry
420 A library may choose one of the stat_cat entries to be the
425 CREATE TABLE actor.org_unit_proximity_adjustment (
426 id SERIAL PRIMARY KEY,
427 item_circ_lib INT REFERENCES actor.org_unit (id),
428 item_owning_lib INT REFERENCES actor.org_unit (id),
429 copy_location INT, -- REFERENCES asset.copy_location (id),
430 hold_pickup_lib INT REFERENCES actor.org_unit (id),
431 hold_request_lib INT REFERENCES actor.org_unit (id),
432 pos INT NOT NULL DEFAULT 0,
433 absolute_adjustment BOOL NOT NULL DEFAULT FALSE,
434 prox_adjustment NUMERIC,
435 circ_mod TEXT, -- REFERENCES config.circ_modifier (code),
436 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)
438 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
439 COALESCE(item_circ_lib, -1),
440 COALESCE(item_owning_lib, -1),
441 COALESCE(copy_location, -1),
442 COALESCE(hold_pickup_lib, -1),
443 COALESCE(hold_request_lib, -1),
444 COALESCE(circ_mod, ''),
447 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
448 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
449 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
450 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
451 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
452 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
454 CREATE TABLE actor.hours_of_operation (
455 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
456 dow_0_open TIME NOT NULL DEFAULT '09:00',
457 dow_0_close TIME NOT NULL DEFAULT '17:00',
459 dow_1_open TIME NOT NULL DEFAULT '09:00',
460 dow_1_close TIME NOT NULL DEFAULT '17:00',
462 dow_2_open TIME NOT NULL DEFAULT '09:00',
463 dow_2_close TIME NOT NULL DEFAULT '17:00',
465 dow_3_open TIME NOT NULL DEFAULT '09:00',
466 dow_3_close TIME NOT NULL DEFAULT '17:00',
468 dow_4_open TIME NOT NULL DEFAULT '09:00',
469 dow_4_close TIME NOT NULL DEFAULT '17:00',
471 dow_5_open TIME NOT NULL DEFAULT '09:00',
472 dow_5_close TIME NOT NULL DEFAULT '17:00',
474 dow_6_open TIME NOT NULL DEFAULT '09:00',
475 dow_6_close TIME NOT NULL DEFAULT '17:00',
478 COMMENT ON TABLE actor.hours_of_operation IS $$
479 When does this org_unit usually open and close? (Variations
480 are expressed in the actor.org_unit_closed table.)
482 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
483 When does this org_unit open on Monday?
485 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
486 When does this org_unit close on Monday?
488 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
489 When does this org_unit open on Tuesday?
491 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
492 When does this org_unit close on Tuesday?
494 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
495 When does this org_unit open on Wednesday?
497 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
498 When does this org_unit close on Wednesday?
500 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
501 When does this org_unit open on Thursday?
503 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
504 When does this org_unit close on Thursday?
506 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
507 When does this org_unit open on Friday?
509 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
510 When does this org_unit close on Friday?
512 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
513 When does this org_unit open on Saturday?
515 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
516 When does this org_unit close on Saturday?
518 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
519 When does this org_unit open on Sunday?
521 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
522 When does this org_unit close on Sunday?
525 CREATE TABLE actor.org_unit_closed (
526 id SERIAL PRIMARY KEY,
527 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
528 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
529 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
530 full_day BOOLEAN NOT NULL DEFAULT FALSE,
531 multi_day BOOLEAN NOT NULL DEFAULT FALSE,
535 -- Workstation registration...
536 CREATE TABLE actor.workstation (
537 id SERIAL PRIMARY KEY,
538 name TEXT NOT NULL UNIQUE,
539 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
542 CREATE TABLE actor.usr_org_unit_opt_in (
543 id SERIAL PRIMARY KEY,
544 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
545 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
546 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
547 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
548 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
549 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
551 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
553 CREATE TABLE actor.org_unit_setting (
554 id BIGSERIAL PRIMARY KEY,
555 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
556 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
558 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
559 CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
561 COMMENT ON TABLE actor.org_unit_setting IS $$
564 This table contains any arbitrary settings that a client
565 program would like to save for an org unit.
568 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
570 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
571 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
575 -- Check for which setting is being updated, and log it.
576 SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
578 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
582 $ous_change_log$ LANGUAGE plpgsql;
584 CREATE TRIGGER log_ous_change
585 BEFORE INSERT OR UPDATE ON actor.org_unit_setting
586 FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
588 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
592 -- Check for which setting is being updated, and log it.
593 SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
595 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
599 $ous_delete_log$ LANGUAGE plpgsql;
601 CREATE TRIGGER log_ous_del
602 BEFORE DELETE ON actor.org_unit_setting
603 FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
608 CREATE TABLE actor.usr_address (
609 id SERIAL PRIMARY KEY,
610 valid BOOL NOT NULL DEFAULT TRUE,
611 within_city_limits BOOL NOT NULL DEFAULT TRUE,
612 address_type TEXT NOT NULL DEFAULT 'MAILING',
613 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
614 street1 TEXT NOT NULL,
619 country TEXT NOT NULL,
620 post_code TEXT NOT NULL,
621 pending BOOL NOT NULL DEFAULT FALSE,
622 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
625 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
627 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
628 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
630 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
631 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
632 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
634 CREATE TABLE actor.usr_password_reset (
635 id SERIAL PRIMARY KEY,
637 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
638 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
639 has_been_reset BOOL NOT NULL DEFAULT false
641 COMMENT ON TABLE actor.usr_password_reset IS $$
642 Self-serve password reset requests
644 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
645 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
646 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
647 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
649 CREATE TABLE actor.org_address (
650 id SERIAL PRIMARY KEY,
651 valid BOOL NOT NULL DEFAULT TRUE,
652 address_type TEXT NOT NULL DEFAULT 'MAILING',
653 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
654 street1 TEXT NOT NULL,
659 country TEXT NOT NULL,
660 post_code TEXT NOT NULL,
666 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
668 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
669 SELECT SUBSTRING( $1, 1, 5);
672 CREATE TABLE actor.usr_standing_penalty (
673 id SERIAL PRIMARY KEY,
674 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
675 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
676 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
677 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
678 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
679 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) ON DELETE CASCADE 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,
802 pub BOOL NOT NULL DEFAULT FALSE,
803 stop_date TIMESTAMP WITH TIME ZONE,
804 editor BIGINT REFERENCES actor.usr (id),
805 edit_date TIMESTAMP WITH TIME ZONE
807 CREATE INDEX aum_usr ON actor.usr_message (usr);
808 CREATE INDEX aum_editor ON actor.usr_message (editor);
809 ALTER TABLE actor.usr_standing_penalty ALTER COLUMN id SET DEFAULT nextval('actor.usr_message_id_seq'::regclass);
810 ALTER TABLE actor.usr_standing_penalty ADD COLUMN usr_message BIGINT REFERENCES actor.usr_message(id);
811 CREATE INDEX usr_standing_penalty_usr_message_idx ON actor.usr_standing_penalty (usr_message);
813 CREATE RULE protect_usr_message_delete AS
814 ON DELETE TO actor.usr_message DO INSTEAD (
815 UPDATE actor.usr_message
817 WHERE OLD.id = actor.usr_message.id
820 -- limited view to ensure that a library user who somehow
821 -- manages to figure out how to access pcrud cannot change
822 -- the text of messages sent them
823 CREATE VIEW actor.usr_message_limited
824 AS SELECT * FROM actor.usr_message WHERE pub AND NOT deleted;
826 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
828 IF TG_OP = 'UPDATE' THEN
829 UPDATE actor.usr_message
830 SET read_date = NEW.read_date,
831 deleted = NEW.deleted
839 CREATE TRIGGER restrict_usr_message_limited_tgr
840 INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
841 FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
843 -- combined view of actor.usr_standing_penalty and actor.usr_message for populating
844 -- staff Notes (formerly Messages) interface
846 CREATE VIEW actor.usr_message_penalty AS
847 SELECT -- ausp with or without messages
849 ausp.id AS "ausp_id",
851 ausp.org_unit AS "org_unit",
852 ausp.org_unit AS "ausp_org_unit",
853 aum.sending_lib AS "aum_sending_lib",
855 ausp.usr as "ausp_usr",
856 aum.usr as "aum_usr",
857 ausp.standing_penalty AS "standing_penalty",
858 ausp.staff AS "staff",
859 ausp.set_date AS "create_date",
860 ausp.set_date AS "ausp_set_date",
861 aum.create_date AS "aum_create_date",
862 ausp.stop_date AS "stop_date",
863 ausp.stop_date AS "ausp_stop_date",
864 aum.stop_date AS "aum_stop_date",
865 ausp.usr_message AS "ausp_usr_message",
866 aum.title AS "title",
867 aum.message AS "message",
868 aum.deleted AS "deleted",
869 aum.read_date AS "read_date",
871 aum.editor AS "editor",
872 aum.edit_date AS "edit_date"
874 actor.usr_standing_penalty ausp
875 LEFT JOIN actor.usr_message aum ON (ausp.usr_message = aum.id)
877 SELECT -- aum without penalties
879 NULL::INT AS "ausp_id",
881 aum.sending_lib AS "org_unit",
882 NULL::INT AS "ausp_org_unit",
883 aum.sending_lib AS "aum_sending_lib",
885 NULL::INT as "ausp_usr",
886 aum.usr as "aum_usr",
887 NULL::INT AS "standing_penalty",
888 NULL::INT AS "staff",
889 aum.create_date AS "create_date",
890 NULL::TIMESTAMPTZ AS "ausp_set_date",
891 aum.create_date AS "aum_create_date",
892 aum.stop_date AS "stop_date",
893 NULL::TIMESTAMPTZ AS "ausp_stop_date",
894 aum.stop_date AS "aum_stop_date",
895 NULL::INT AS "ausp_usr_message",
896 aum.title AS "title",
897 aum.message AS "message",
898 aum.deleted AS "deleted",
899 aum.read_date AS "read_date",
901 aum.editor AS "editor",
902 aum.edit_date AS "edit_date"
904 actor.usr_message aum
905 LEFT JOIN actor.usr_standing_penalty ausp ON (ausp.usr_message = aum.id)
906 WHERE NOT aum.deleted AND ausp.id IS NULL
909 CREATE TABLE actor.passwd_type (
910 code TEXT PRIMARY KEY,
911 name TEXT UNIQUE NOT NULL,
912 login BOOLEAN NOT NULL DEFAULT FALSE,
913 regex TEXT, -- pending
914 crypt_algo TEXT, -- e.g. 'bf'
916 -- gen_salt() iter count used with each new salt.
917 -- A non-NULL value for iter_count is our indication the
918 -- password is salted and encrypted via crypt()
919 iter_count INTEGER CHECK (iter_count IS NULL OR iter_count > 0)
922 CREATE TABLE actor.passwd (
923 id SERIAL PRIMARY KEY,
924 usr INTEGER NOT NULL REFERENCES actor.usr(id)
925 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
926 salt TEXT, -- will be NULL for non-crypt'ed passwords
927 passwd TEXT NOT NULL,
928 passwd_type TEXT NOT NULL REFERENCES actor.passwd_type(code)
929 DEFERRABLE INITIALLY DEFERRED,
930 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
931 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
932 CONSTRAINT passwd_type_once_per_user UNIQUE (usr, passwd_type)
935 CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT)
938 type_row actor.passwd_type%ROWTYPE;
940 /* Returns a new salt based on the passwd_type encryption settings.
941 * Returns NULL If the password type is not crypt()'ed.
944 SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;
947 RETURN EXCEPTION 'No such password type: %', pw_type;
950 IF type_row.iter_count IS NULL THEN
951 -- This password type is unsalted. That's OK.
955 RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
961 TODO: when a user changes their password in the application, the
962 app layer has access to the bare password. At that point, we have
963 the opportunity to store the new password without the MD5(MD5())
964 intermediate hashing. Do we care? We would need a way to indicate
965 which passwords have the legacy intermediate hashing and which don't
966 so the app layer would know whether it should perform the intermediate
967 hashing. In either event, with the exception of migrate_passwd(), the
968 DB functions know or care nothing about intermediate hashing. Every
969 password is just a value that may or may not be internally crypt'ed.
972 CREATE OR REPLACE FUNCTION actor.set_passwd(
973 pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL)
974 RETURNS BOOLEAN AS $$
979 /* Sets the password value, creating a new actor.passwd row if needed.
980 * If the password type supports it, the new_pass value is crypt()'ed.
981 * For crypt'ed passwords, the salt comes from one of 3 places in order:
982 * new_salt (if present), existing salt (if present), newly created
986 IF new_salt IS NOT NULL THEN
989 pw_salt := actor.get_salt(pw_usr, pw_type);
991 IF pw_salt IS NULL THEN
992 /* We have no salt for this user + type. Assume they want a
993 * new salt. If this type is unsalted, create_salt() will
995 pw_salt := actor.create_salt(pw_type);
999 IF pw_salt IS NULL THEN
1000 pw_text := new_pass; -- unsalted, use as-is.
1002 pw_text := CRYPT(new_pass, pw_salt);
1006 SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
1007 WHERE usr = pw_usr AND passwd_type = pw_type;
1010 -- no password row exists for this user + type. Create one.
1011 INSERT INTO actor.passwd (usr, passwd_type, salt, passwd)
1012 VALUES (pw_usr, pw_type, pw_salt, pw_text);
1017 $$ LANGUAGE PLPGSQL;
1019 CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT)
1023 type_row actor.passwd_type%ROWTYPE;
1025 /* Returns the salt for the requested user + type. If the password
1026 * type of "main" is requested and no password exists in actor.passwd,
1027 * the user's existing password is migrated and the new salt is returned.
1028 * Returns NULL if the password type is not crypt'ed (iter_count is NULL).
1031 SELECT INTO pw_salt salt FROM actor.passwd
1032 WHERE usr = pw_usr AND passwd_type = pw_type;
1038 IF pw_type = 'main' THEN
1039 -- Main password has not yet been migrated.
1040 -- Do it now and return the newly created salt.
1041 RETURN actor.migrate_passwd(pw_usr);
1044 -- We have no salt to return. actor.create_salt() needed.
1047 $$ LANGUAGE PLPGSQL;
1049 CREATE OR REPLACE FUNCTION
1050 actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$
1053 usr_row actor.usr%ROWTYPE;
1055 /* Migrates legacy actor.usr.passwd value to actor.passwd with
1056 * a password type 'main' and returns the new salt. For backwards
1057 * compatibility with existing CHAP-style API's, we perform a
1058 * layer of intermediate MD5(MD5()) hashing. This is intermediate
1059 * hashing is not required of other passwords.
1062 -- Avoid calling get_salt() here, because it may result in a
1063 -- migrate_passwd() call, creating a loop.
1064 SELECT INTO pw_salt salt FROM actor.passwd
1065 WHERE usr = pw_usr AND passwd_type = 'main';
1067 -- Only migrate passwords that have not already been migrated.
1072 SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
1074 pw_salt := actor.create_salt('main');
1076 PERFORM actor.set_passwd(
1077 pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
1079 -- clear the existing password
1080 UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
1084 $$ LANGUAGE PLPGSQL;
1086 CREATE OR REPLACE FUNCTION
1087 actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT)
1088 RETURNS BOOLEAN AS $$
1092 /* Returns TRUE if the password provided matches the in-db password.
1093 * If the password type is salted, we compare the output of CRYPT().
1094 * NOTE: test_passwd is MD5(salt || MD5(password)) for legacy
1098 SELECT INTO pw_salt salt FROM actor.passwd
1099 WHERE usr = pw_usr AND passwd_type = pw_type;
1106 IF pw_salt IS NULL THEN
1107 -- Password is unsalted, compare the un-CRYPT'ed values.
1109 SELECT TRUE FROM actor.passwd WHERE
1111 passwd_type = pw_type AND
1112 passwd = test_passwd
1117 SELECT TRUE FROM actor.passwd WHERE
1119 passwd_type = pw_type AND
1120 passwd = CRYPT(test_passwd, pw_salt)
1123 $$ STRICT LANGUAGE PLPGSQL;
1125 -- Remove all activity entries by activity type,
1126 -- except the most recent entry per user.
1127 CREATE OR REPLACE FUNCTION
1128 actor.purge_usr_activity_by_type(act_type INTEGER)
1133 FOR cur_usr IN SELECT DISTINCT(usr)
1134 FROM actor.usr_activity WHERE etype = act_type LOOP
1135 DELETE FROM actor.usr_activity WHERE id IN (
1137 FROM actor.usr_activity
1138 WHERE usr = cur_usr AND etype = act_type
1139 ORDER BY event_time DESC OFFSET 1
1143 END $$ LANGUAGE PLPGSQL;
1145 CREATE TABLE actor.workstation_setting (
1146 id SERIAL PRIMARY KEY,
1147 workstation INT NOT NULL REFERENCES actor.workstation (id)
1148 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1149 name TEXT NOT NULL REFERENCES config.workstation_setting_type (name)
1150 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1151 value JSON NOT NULL,
1152 CONSTRAINT ws_once_per_key UNIQUE (workstation, name)
1155 CREATE INDEX actor_workstation_setting_workstation_idx
1156 ON actor.workstation_setting (workstation);
1158 CREATE TYPE actor.cascade_setting_summary AS (
1161 has_org_setting BOOLEAN,
1162 has_user_setting BOOLEAN,
1163 has_workstation_setting BOOLEAN
1166 CREATE OR REPLACE FUNCTION actor.get_cascade_setting(
1167 setting_name TEXT, org_id INT, user_id INT, workstation_id INT)
1168 RETURNS actor.cascade_setting_summary AS
1172 summary actor.cascade_setting_summary;
1173 org_setting_type config.org_unit_setting_type%ROWTYPE;
1176 summary.name := setting_name;
1178 -- Collect the org setting type status first in case we exit early.
1179 -- The existance of an org setting type is not considered
1180 -- privileged information.
1181 SELECT INTO org_setting_type *
1182 FROM config.org_unit_setting_type WHERE name = setting_name;
1184 summary.has_org_setting := TRUE;
1186 summary.has_org_setting := FALSE;
1189 -- User and workstation settings have the same priority.
1190 -- Start with user settings since that's the simplest code path.
1191 -- The workstation_id is ignored if no user_id is provided.
1192 IF user_id IS NOT NULL THEN
1194 SELECT INTO summary.value value FROM actor.usr_setting
1195 WHERE usr = user_id AND name = setting_name;
1198 -- if we have a value, we have a setting type
1199 summary.has_user_setting := TRUE;
1201 IF workstation_id IS NOT NULL THEN
1202 -- Only inform the caller about the workstation
1203 -- setting type disposition when a workstation id is
1204 -- provided. Otherwise, it's NULL to indicate UNKNOWN.
1205 summary.has_workstation_setting := FALSE;
1211 -- no user setting value, but a setting type may exist
1212 SELECT INTO summary.has_user_setting EXISTS (
1213 SELECT TRUE FROM config.usr_setting_type
1214 WHERE name = setting_name
1217 IF workstation_id IS NOT NULL THEN
1219 IF NOT summary.has_user_setting THEN
1220 -- A workstation setting type may only exist when a user
1221 -- setting type does not.
1223 SELECT INTO summary.value value
1224 FROM actor.workstation_setting
1225 WHERE workstation = workstation_id AND name = setting_name;
1228 -- if we have a value, we have a setting type
1229 summary.has_workstation_setting := TRUE;
1233 -- no value, but a setting type may exist
1234 SELECT INTO summary.has_workstation_setting EXISTS (
1235 SELECT TRUE FROM config.workstation_setting_type
1236 WHERE name = setting_name
1240 -- Finally make use of the workstation to determine the org
1241 -- unit if none is provided.
1242 IF org_id IS NULL AND summary.has_org_setting THEN
1243 SELECT INTO org_id owning_lib
1244 FROM actor.workstation WHERE id = workstation_id;
1249 -- Some org unit settings are protected by a view permission.
1250 -- First see if we have any data that needs protecting, then
1251 -- check the permission if needed.
1253 IF NOT summary.has_org_setting THEN
1257 -- avoid putting the value into the summary until we confirm
1258 -- the value should be visible to the caller.
1259 SELECT INTO setting_value value
1260 FROM actor.org_unit_ancestor_setting(setting_name, org_id);
1263 -- No value found -- perm check is irrelevant.
1267 IF org_setting_type.view_perm IS NOT NULL THEN
1269 IF user_id IS NULL THEN
1270 RAISE NOTICE 'Perm check required but no user_id provided';
1274 IF NOT permission.usr_has_perm(
1275 user_id, (SELECT code FROM permission.perm_list
1276 WHERE id = org_setting_type.view_perm), org_id)
1278 RAISE NOTICE 'Perm check failed for user % on %',
1279 user_id, org_setting_type.view_perm;
1284 -- Perm check succeeded or was not necessary.
1285 summary.value := setting_value;
1288 $FUNC$ LANGUAGE PLPGSQL;
1291 CREATE OR REPLACE FUNCTION actor.get_cascade_setting_batch(
1292 setting_names TEXT[], org_id INT, user_id INT, workstation_id INT)
1293 RETURNS SETOF actor.cascade_setting_summary AS
1295 -- Returns a row per setting matching the setting name order. If no
1296 -- value is applied, NULL is returned to retain name-response ordering.
1299 summary actor.cascade_setting_summary;
1301 FOREACH setting_name IN ARRAY setting_names LOOP
1302 SELECT INTO summary * FROM actor.get_cascade_setting(
1303 setting_Name, org_id, user_id, workstation_id);
1304 RETURN NEXT summary;
1307 $FUNC$ LANGUAGE PLPGSQL;
1309 CREATE TABLE actor.usr_privacy_waiver (
1310 id BIGSERIAL PRIMARY KEY,
1311 usr BIGINT NOT NULL REFERENCES actor.usr(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1313 place_holds BOOL DEFAULT FALSE,
1314 pickup_holds BOOL DEFAULT FALSE,
1315 view_history BOOL DEFAULT FALSE,
1316 checkout_items BOOL DEFAULT FALSE
1318 CREATE INDEX actor_usr_privacy_waiver_usr_idx ON actor.usr_privacy_waiver (usr);