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
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));
96 CREATE INDEX actor_usr_guardian_idx ON actor.usr (evergreen.lowercase(guardian));
97 CREATE INDEX actor_usr_guardian_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(guardian));
99 CREATE INDEX actor_usr_pref_first_given_name_idx ON actor.usr (evergreen.lowercase(pref_first_given_name));
100 CREATE INDEX actor_usr_pref_second_given_name_idx ON actor.usr (evergreen.lowercase(pref_second_given_name));
101 CREATE INDEX actor_usr_pref_family_name_idx ON actor.usr (evergreen.lowercase(pref_family_name));
102 CREATE INDEX actor_usr_pref_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(pref_first_given_name));
103 CREATE INDEX actor_usr_pref_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(pref_second_given_name));
104 CREATE INDEX actor_usr_pref_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(pref_family_name));
106 CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname));
107 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
109 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
110 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
111 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
113 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
114 (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
116 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
117 (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
119 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
120 (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
122 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
123 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
125 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
127 NEW.passwd = MD5( NEW.passwd );
132 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
134 IF NEW.passwd <> OLD.passwd THEN
135 NEW.passwd = MD5( NEW.passwd );
141 CREATE OR REPLACE FUNCTION actor.au_updated()
142 RETURNS TRIGGER AS $$
144 NEW.last_update_time := now();
149 CREATE TRIGGER au_update_trig
150 BEFORE INSERT OR UPDATE ON actor.usr
151 FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
153 CREATE TRIGGER actor_crypt_pw_update_trigger
154 BEFORE UPDATE ON actor.usr FOR EACH ROW
155 EXECUTE PROCEDURE actor.crypt_pw_update ();
157 CREATE TRIGGER actor_crypt_pw_insert_trigger
158 BEFORE INSERT ON actor.usr FOR EACH ROW
159 EXECUTE PROCEDURE actor.crypt_pw_insert ();
161 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;
163 CREATE OR REPLACE FUNCTION actor.user_ingest_name_keywords()
164 RETURNS TRIGGER AS $func$
166 NEW.name_kw_tsvector := TO_TSVECTOR(
167 COALESCE(NEW.prefix, '') || ' ' ||
168 COALESCE(NEW.first_given_name, '') || ' ' ||
169 COALESCE(evergreen.unaccent_and_squash(NEW.first_given_name), '') || ' ' ||
170 COALESCE(NEW.second_given_name, '') || ' ' ||
171 COALESCE(evergreen.unaccent_and_squash(NEW.second_given_name), '') || ' ' ||
172 COALESCE(NEW.family_name, '') || ' ' ||
173 COALESCE(evergreen.unaccent_and_squash(NEW.family_name), '') || ' ' ||
174 COALESCE(NEW.suffix, '') || ' ' ||
175 COALESCE(NEW.pref_prefix, '') || ' ' ||
176 COALESCE(NEW.pref_first_given_name, '') || ' ' ||
177 COALESCE(evergreen.unaccent_and_squash(NEW.pref_first_given_name), '') || ' ' ||
178 COALESCE(NEW.pref_second_given_name, '') || ' ' ||
179 COALESCE(evergreen.unaccent_and_squash(NEW.pref_second_given_name), '') || ' ' ||
180 COALESCE(NEW.pref_family_name, '') || ' ' ||
181 COALESCE(evergreen.unaccent_and_squash(NEW.pref_family_name), '') || ' ' ||
182 COALESCE(NEW.pref_suffix, '') || ' ' ||
183 COALESCE(NEW.name_keywords, '')
187 $func$ LANGUAGE PLPGSQL;
189 -- Add after the batch upate above to avoid duplicate updates.
190 CREATE TRIGGER user_ingest_name_keywords_tgr
191 BEFORE INSERT OR UPDATE ON actor.usr
192 FOR EACH ROW EXECUTE PROCEDURE actor.user_ingest_name_keywords();
194 CREATE TABLE actor.usr_setting (
195 id BIGSERIAL PRIMARY KEY,
196 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
197 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
199 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
201 COMMENT ON TABLE actor.usr_setting IS $$
204 This table contains any arbitrary settings that a client
205 program would like to save for a user.
208 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
210 CREATE TABLE actor.stat_cat_sip_fields (
211 field CHAR(2) PRIMARY KEY,
213 one_only BOOL NOT NULL DEFAULT FALSE
215 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
216 Actor Statistical Category SIP Fields
218 Contains the list of valid SIP Field identifiers for
219 Statistical Categories.
222 CREATE TABLE actor.stat_cat (
223 id SERIAL PRIMARY KEY,
226 opac_visible BOOL NOT NULL DEFAULT FALSE,
227 usr_summary BOOL NOT NULL DEFAULT FALSE,
228 sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
230 checkout_archive BOOL NOT NULL DEFAULT FALSE,
231 required BOOL NOT NULL DEFAULT FALSE,
232 allow_freetext BOOL NOT NULL DEFAULT TRUE,
233 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
235 COMMENT ON TABLE actor.stat_cat IS $$
236 User Statistical Catagories
238 Local data collected about Users is placed into a Statistical
239 Catagory. Here's where those catagories are defined.
243 CREATE TABLE actor.stat_cat_entry (
244 id SERIAL PRIMARY KEY,
245 stat_cat INT NOT NULL,
248 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
250 COMMENT ON TABLE actor.stat_cat_entry IS $$
251 User Statistical Catagory Entries
253 Local data collected about Users is placed into a Statistical
254 Catagory. Each library can create entries into any of its own
255 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
259 CREATE TABLE actor.stat_cat_entry_usr_map (
260 id BIGSERIAL PRIMARY KEY,
261 stat_cat_entry TEXT NOT NULL,
262 stat_cat INT NOT NULL,
263 target_usr INT NOT NULL,
264 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
266 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
267 Statistical Catagory Entry to User map
269 Records the stat_cat entries for each user.
272 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
274 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
276 sipfield actor.stat_cat_sip_fields%ROWTYPE;
279 IF NEW.sip_field IS NOT NULL THEN
280 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
281 IF sipfield.one_only THEN
282 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
283 IF use_count > 0 THEN
284 RAISE EXCEPTION 'Sip field cannot be used twice';
290 $func$ LANGUAGE PLPGSQL;
292 CREATE TRIGGER actor_stat_cat_sip_update_trigger
293 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
294 EXECUTE PROCEDURE actor.stat_cat_check();
296 CREATE TABLE actor.card (
297 id SERIAL PRIMARY KEY,
298 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
299 barcode TEXT NOT NULL UNIQUE,
300 active BOOL NOT NULL DEFAULT TRUE
302 COMMENT ON TABLE actor.card IS $$
305 Each User has one or more library cards. The current "main"
306 card is linked to here from the actor.usr table, and it is up
307 to the consortium policy whether more than one card can be
308 active for any one user at a given time.
311 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
312 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
314 CREATE TABLE actor.org_unit_type (
315 id SERIAL PRIMARY KEY,
317 opac_label TEXT NOT NULL,
319 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
320 can_have_vols BOOL NOT NULL DEFAULT TRUE,
321 can_have_users BOOL NOT NULL DEFAULT TRUE
323 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
325 CREATE TABLE actor.org_unit (
326 id SERIAL PRIMARY KEY,
327 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
328 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
333 shortname TEXT NOT NULL UNIQUE,
334 name TEXT NOT NULL UNIQUE,
337 opac_visible BOOL NOT NULL DEFAULT TRUE,
338 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
340 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
341 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
342 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
343 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
344 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
345 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
347 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
349 current_aou actor.org_unit%ROWTYPE;
355 seen_ous := ARRAY[NEW.id];
357 IF (TG_OP = 'UPDATE') THEN
358 IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
359 RETURN NEW; -- Doing an UPDATE with no change, just return it
364 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
365 RETURN NEW; -- No loop. Carry on.
367 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
368 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
371 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
372 seen_ous := seen_ous || current_aou.id;
373 depth_count := depth_count + 1;
374 IF depth_count = 100 THEN
375 RAISE 'OU CHECK TOO DEEP';
383 CREATE TRIGGER actor_org_unit_parent_protect_trigger
384 BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
385 EXECUTE PROCEDURE actor.org_unit_parent_protect ();
387 CREATE TABLE actor.org_lasso (
388 id SERIAL PRIMARY KEY,
390 global BOOL NOT NULL DEFAULT FALSE
393 CREATE TABLE actor.org_lasso_map (
394 id SERIAL PRIMARY KEY,
395 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
396 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
398 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
399 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
401 CREATE TABLE actor.org_unit_proximity (
402 id BIGSERIAL PRIMARY KEY,
407 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
409 CREATE TABLE actor.stat_cat_entry_default (
410 id SERIAL PRIMARY KEY,
411 stat_cat_entry INT NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
412 stat_cat INT NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
413 owner INT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
414 CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
416 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
417 User Statistical Category Default Entry
419 A library may choose one of the stat_cat entries to be the
424 CREATE TABLE actor.org_unit_proximity_adjustment (
425 id SERIAL PRIMARY KEY,
426 item_circ_lib INT REFERENCES actor.org_unit (id),
427 item_owning_lib INT REFERENCES actor.org_unit (id),
428 copy_location INT, -- REFERENCES asset.copy_location (id),
429 hold_pickup_lib INT REFERENCES actor.org_unit (id),
430 hold_request_lib INT REFERENCES actor.org_unit (id),
431 pos INT NOT NULL DEFAULT 0,
432 absolute_adjustment BOOL NOT NULL DEFAULT FALSE,
433 prox_adjustment NUMERIC,
434 circ_mod TEXT, -- REFERENCES config.circ_modifier (code),
435 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)
437 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
438 COALESCE(item_circ_lib, -1),
439 COALESCE(item_owning_lib, -1),
440 COALESCE(copy_location, -1),
441 COALESCE(hold_pickup_lib, -1),
442 COALESCE(hold_request_lib, -1),
443 COALESCE(circ_mod, ''),
446 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
447 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
448 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
449 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
450 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
451 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
453 CREATE TABLE actor.hours_of_operation (
454 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
455 dow_0_open TIME NOT NULL DEFAULT '09:00',
456 dow_0_close TIME NOT NULL DEFAULT '17:00',
457 dow_1_open TIME NOT NULL DEFAULT '09:00',
458 dow_1_close TIME NOT NULL DEFAULT '17:00',
459 dow_2_open TIME NOT NULL DEFAULT '09:00',
460 dow_2_close TIME NOT NULL DEFAULT '17:00',
461 dow_3_open TIME NOT NULL DEFAULT '09:00',
462 dow_3_close TIME NOT NULL DEFAULT '17:00',
463 dow_4_open TIME NOT NULL DEFAULT '09:00',
464 dow_4_close TIME NOT NULL DEFAULT '17:00',
465 dow_5_open TIME NOT NULL DEFAULT '09:00',
466 dow_5_close TIME NOT NULL DEFAULT '17:00',
467 dow_6_open TIME NOT NULL DEFAULT '09:00',
468 dow_6_close TIME NOT NULL DEFAULT '17:00'
470 COMMENT ON TABLE actor.hours_of_operation IS $$
471 When does this org_unit usually open and close? (Variations
472 are expressed in the actor.org_unit_closed table.)
474 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
475 When does this org_unit open on Monday?
477 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
478 When does this org_unit close on Monday?
480 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
481 When does this org_unit open on Tuesday?
483 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
484 When does this org_unit close on Tuesday?
486 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
487 When does this org_unit open on Wednesday?
489 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
490 When does this org_unit close on Wednesday?
492 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
493 When does this org_unit open on Thursday?
495 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
496 When does this org_unit close on Thursday?
498 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
499 When does this org_unit open on Friday?
501 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
502 When does this org_unit close on Friday?
504 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
505 When does this org_unit open on Saturday?
507 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
508 When does this org_unit close on Saturday?
510 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
511 When does this org_unit open on Sunday?
513 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
514 When does this org_unit close on Sunday?
517 CREATE TABLE actor.org_unit_closed (
518 id SERIAL PRIMARY KEY,
519 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
520 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
521 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
522 full_day BOOLEAN NOT NULL DEFAULT FALSE,
523 multi_day BOOLEAN NOT NULL DEFAULT FALSE,
527 -- Workstation registration...
528 CREATE TABLE actor.workstation (
529 id SERIAL PRIMARY KEY,
530 name TEXT NOT NULL UNIQUE,
531 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
534 CREATE TABLE actor.usr_org_unit_opt_in (
535 id SERIAL PRIMARY KEY,
536 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
537 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
538 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
539 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
540 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
541 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
543 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
545 CREATE TABLE actor.org_unit_setting (
546 id BIGSERIAL PRIMARY KEY,
547 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
548 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
550 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
551 CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
553 COMMENT ON TABLE actor.org_unit_setting IS $$
556 This table contains any arbitrary settings that a client
557 program would like to save for an org unit.
560 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
562 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
563 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
567 -- Check for which setting is being updated, and log it.
568 SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
570 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
574 $ous_change_log$ LANGUAGE plpgsql;
576 CREATE TRIGGER log_ous_change
577 BEFORE INSERT OR UPDATE ON actor.org_unit_setting
578 FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
580 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
584 -- Check for which setting is being updated, and log it.
585 SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
587 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
591 $ous_delete_log$ LANGUAGE plpgsql;
593 CREATE TRIGGER log_ous_del
594 BEFORE DELETE ON actor.org_unit_setting
595 FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
600 CREATE TABLE actor.usr_address (
601 id SERIAL PRIMARY KEY,
602 valid BOOL NOT NULL DEFAULT TRUE,
603 within_city_limits BOOL NOT NULL DEFAULT TRUE,
604 address_type TEXT NOT NULL DEFAULT 'MAILING',
605 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
606 street1 TEXT NOT NULL,
611 country TEXT NOT NULL,
612 post_code TEXT NOT NULL,
613 pending BOOL NOT NULL DEFAULT FALSE,
614 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
617 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
619 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
620 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
622 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
623 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
624 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
626 CREATE TABLE actor.usr_password_reset (
627 id SERIAL PRIMARY KEY,
629 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
630 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
631 has_been_reset BOOL NOT NULL DEFAULT false
633 COMMENT ON TABLE actor.usr_password_reset IS $$
634 Self-serve password reset requests
636 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
637 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
638 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
639 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
641 CREATE TABLE actor.org_address (
642 id SERIAL PRIMARY KEY,
643 valid BOOL NOT NULL DEFAULT TRUE,
644 address_type TEXT NOT NULL DEFAULT 'MAILING',
645 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
646 street1 TEXT NOT NULL,
651 country TEXT NOT NULL,
652 post_code TEXT NOT NULL,
658 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
660 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
661 SELECT SUBSTRING( $1, 1, 5);
664 CREATE TABLE actor.usr_standing_penalty (
665 id SERIAL PRIMARY KEY,
666 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
667 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
668 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
669 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
670 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
671 stop_date TIMESTAMP WITH TIME ZONE
673 COMMENT ON TABLE actor.usr_standing_penalty IS $$
674 User standing penalties
677 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
678 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
681 CREATE TABLE actor.usr_saved_search (
682 id SERIAL PRIMARY KEY,
683 owner INT NOT NULL REFERENCES actor.usr (id)
685 DEFERRABLE INITIALLY DEFERRED,
687 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
688 query_text TEXT NOT NULL,
689 query_type TEXT NOT NULL
690 CONSTRAINT valid_query_text CHECK (
691 query_type IN ( 'URL' )) DEFAULT 'URL',
692 -- we may add other types someday
694 CONSTRAINT valid_target CHECK (
695 target IN ( 'record', 'metarecord', 'callnumber' )),
696 CONSTRAINT name_once_per_user UNIQUE (owner, name)
699 CREATE TABLE actor.address_alert (
700 id SERIAL PRIMARY KEY,
701 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
702 active BOOL NOT NULL DEFAULT TRUE,
703 match_all BOOL NOT NULL DEFAULT TRUE,
704 alert_message TEXT NOT NULL,
712 mailing_address BOOL NOT NULL DEFAULT FALSE,
713 billing_address BOOL NOT NULL DEFAULT FALSE
716 CREATE TABLE actor.usr_activity (
717 id BIGSERIAL PRIMARY KEY,
718 usr INT REFERENCES actor.usr (id) ON DELETE SET NULL,
719 etype INT NOT NULL REFERENCES config.usr_activity_type (id),
720 event_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
722 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
724 CREATE TABLE actor.toolbar (
725 id BIGSERIAL PRIMARY KEY,
726 ws INT REFERENCES actor.workstation (id) ON DELETE CASCADE,
727 org INT REFERENCES actor.org_unit (id) ON DELETE CASCADE,
728 usr INT REFERENCES actor.usr (id) ON DELETE CASCADE,
730 layout TEXT NOT NULL,
731 CONSTRAINT only_one_type CHECK (
732 (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
733 (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
734 (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
736 CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
738 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
739 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
740 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
742 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
743 CREATE TABLE actor.org_unit_custom_tree (
744 id SERIAL PRIMARY KEY,
745 active BOOLEAN DEFAULT FALSE,
746 purpose actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
749 CREATE TABLE actor.org_unit_custom_tree_node (
750 id SERIAL PRIMARY KEY,
751 tree INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
752 org_unit INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
753 parent_node INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
754 sibling_order INTEGER NOT NULL DEFAULT 0,
755 CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
758 CREATE TABLE actor.search_query (
759 id SERIAL PRIMARY KEY,
760 label TEXT NOT NULL, -- i18n
761 query_text TEXT NOT NULL -- QP text
764 CREATE TABLE actor.search_filter_group (
765 id SERIAL PRIMARY KEY,
766 owner INT NOT NULL REFERENCES actor.org_unit (id)
767 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
768 code TEXT NOT NULL, -- for CGI, etc.
769 label TEXT NOT NULL, -- i18n
770 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
771 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
772 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
775 CREATE TABLE actor.search_filter_group_entry (
776 id SERIAL PRIMARY KEY,
777 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
778 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
779 pos INT NOT NULL DEFAULT 0,
780 query INT NOT NULL REFERENCES actor.search_query(id)
781 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
782 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
785 CREATE TABLE actor.usr_message (
786 id SERIAL PRIMARY KEY,
787 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
789 message TEXT NOT NULL,
790 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
791 deleted BOOL NOT NULL DEFAULT FALSE,
792 read_date TIMESTAMP WITH TIME ZONE,
793 sending_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
794 pub BOOL NOT NULL DEFAULT FALSE,
795 stop_date TIMESTAMP WITH TIME ZONE,
796 editor BIGINT REFERENCES actor.usr (id),
797 edit_date TIMESTAMP WITH TIME ZONE
799 CREATE INDEX aum_usr ON actor.usr_message (usr);
800 CREATE INDEX aum_editor ON actor.usr_message (editor);
801 ALTER TABLE actor.usr_standing_penalty ALTER COLUMN id SET DEFAULT nextval('actor.usr_message_id_seq'::regclass);
802 ALTER TABLE actor.usr_standing_penalty ADD COLUMN usr_message BIGINT REFERENCES actor.usr_message(id);
803 CREATE INDEX usr_standing_penalty_usr_message_idx ON actor.usr_standing_penalty (usr_message);
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 -- limited view to ensure that a library user who somehow
813 -- manages to figure out how to access pcrud cannot change
814 -- the text of messages sent them
815 CREATE VIEW actor.usr_message_limited
816 AS SELECT * FROM actor.usr_message WHERE pub AND NOT deleted;
818 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
820 IF TG_OP = 'UPDATE' THEN
821 UPDATE actor.usr_message
822 SET read_date = NEW.read_date,
823 deleted = NEW.deleted
831 CREATE TRIGGER restrict_usr_message_limited_tgr
832 INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
833 FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
835 -- combined view of actor.usr_standing_penalty and actor.usr_message for populating
836 -- staff Notes (formerly Messages) interface
838 CREATE VIEW actor.usr_message_penalty AS
839 SELECT -- ausp with or without messages
841 ausp.id AS "ausp_id",
843 ausp.org_unit AS "org_unit",
844 ausp.org_unit AS "ausp_org_unit",
845 aum.sending_lib AS "aum_sending_lib",
847 ausp.usr as "ausp_usr",
848 aum.usr as "aum_usr",
849 ausp.standing_penalty AS "standing_penalty",
850 ausp.staff AS "staff",
851 ausp.set_date AS "create_date",
852 ausp.set_date AS "ausp_set_date",
853 aum.create_date AS "aum_create_date",
854 ausp.stop_date AS "stop_date",
855 ausp.stop_date AS "ausp_stop_date",
856 aum.stop_date AS "aum_stop_date",
857 ausp.usr_message AS "ausp_usr_message",
858 aum.title AS "title",
859 aum.message AS "message",
860 aum.deleted AS "deleted",
861 aum.read_date AS "read_date",
863 aum.editor AS "editor",
864 aum.edit_date AS "edit_date"
866 actor.usr_standing_penalty ausp
867 LEFT JOIN actor.usr_message aum ON (ausp.usr_message = aum.id)
869 SELECT -- aum without penalties
871 NULL::INT AS "ausp_id",
873 aum.sending_lib AS "org_unit",
874 NULL::INT AS "ausp_org_unit",
875 aum.sending_lib AS "aum_sending_lib",
877 NULL::INT as "ausp_usr",
878 aum.usr as "aum_usr",
879 NULL::INT AS "standing_penalty",
880 NULL::INT AS "staff",
881 aum.create_date AS "create_date",
882 NULL::TIMESTAMPTZ AS "ausp_set_date",
883 aum.create_date AS "aum_create_date",
884 aum.stop_date AS "stop_date",
885 NULL::TIMESTAMPTZ AS "ausp_stop_date",
886 aum.stop_date AS "aum_stop_date",
887 NULL::INT AS "ausp_usr_message",
888 aum.title AS "title",
889 aum.message AS "message",
890 aum.deleted AS "deleted",
891 aum.read_date AS "read_date",
893 aum.editor AS "editor",
894 aum.edit_date AS "edit_date"
896 actor.usr_message aum
897 LEFT JOIN actor.usr_standing_penalty ausp ON (ausp.usr_message = aum.id)
898 WHERE NOT aum.deleted AND ausp.id IS NULL
901 CREATE TABLE actor.passwd_type (
902 code TEXT PRIMARY KEY,
903 name TEXT UNIQUE NOT NULL,
904 login BOOLEAN NOT NULL DEFAULT FALSE,
905 regex TEXT, -- pending
906 crypt_algo TEXT, -- e.g. 'bf'
908 -- gen_salt() iter count used with each new salt.
909 -- A non-NULL value for iter_count is our indication the
910 -- password is salted and encrypted via crypt()
911 iter_count INTEGER CHECK (iter_count IS NULL OR iter_count > 0)
914 CREATE TABLE actor.passwd (
915 id SERIAL PRIMARY KEY,
916 usr INTEGER NOT NULL REFERENCES actor.usr(id)
917 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
918 salt TEXT, -- will be NULL for non-crypt'ed passwords
919 passwd TEXT NOT NULL,
920 passwd_type TEXT NOT NULL REFERENCES actor.passwd_type(code)
921 DEFERRABLE INITIALLY DEFERRED,
922 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
923 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
924 CONSTRAINT passwd_type_once_per_user UNIQUE (usr, passwd_type)
927 CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT)
930 type_row actor.passwd_type%ROWTYPE;
932 /* Returns a new salt based on the passwd_type encryption settings.
933 * Returns NULL If the password type is not crypt()'ed.
936 SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;
939 RETURN EXCEPTION 'No such password type: %', pw_type;
942 IF type_row.iter_count IS NULL THEN
943 -- This password type is unsalted. That's OK.
947 RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
953 TODO: when a user changes their password in the application, the
954 app layer has access to the bare password. At that point, we have
955 the opportunity to store the new password without the MD5(MD5())
956 intermediate hashing. Do we care? We would need a way to indicate
957 which passwords have the legacy intermediate hashing and which don't
958 so the app layer would know whether it should perform the intermediate
959 hashing. In either event, with the exception of migrate_passwd(), the
960 DB functions know or care nothing about intermediate hashing. Every
961 password is just a value that may or may not be internally crypt'ed.
964 CREATE OR REPLACE FUNCTION actor.set_passwd(
965 pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL)
966 RETURNS BOOLEAN AS $$
971 /* Sets the password value, creating a new actor.passwd row if needed.
972 * If the password type supports it, the new_pass value is crypt()'ed.
973 * For crypt'ed passwords, the salt comes from one of 3 places in order:
974 * new_salt (if present), existing salt (if present), newly created
978 IF new_salt IS NOT NULL THEN
981 pw_salt := actor.get_salt(pw_usr, pw_type);
983 IF pw_salt IS NULL THEN
984 /* We have no salt for this user + type. Assume they want a
985 * new salt. If this type is unsalted, create_salt() will
987 pw_salt := actor.create_salt(pw_type);
991 IF pw_salt IS NULL THEN
992 pw_text := new_pass; -- unsalted, use as-is.
994 pw_text := CRYPT(new_pass, pw_salt);
998 SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
999 WHERE usr = pw_usr AND passwd_type = pw_type;
1002 -- no password row exists for this user + type. Create one.
1003 INSERT INTO actor.passwd (usr, passwd_type, salt, passwd)
1004 VALUES (pw_usr, pw_type, pw_salt, pw_text);
1009 $$ LANGUAGE PLPGSQL;
1011 CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT)
1015 type_row actor.passwd_type%ROWTYPE;
1017 /* Returns the salt for the requested user + type. If the password
1018 * type of "main" is requested and no password exists in actor.passwd,
1019 * the user's existing password is migrated and the new salt is returned.
1020 * Returns NULL if the password type is not crypt'ed (iter_count is NULL).
1023 SELECT INTO pw_salt salt FROM actor.passwd
1024 WHERE usr = pw_usr AND passwd_type = pw_type;
1030 IF pw_type = 'main' THEN
1031 -- Main password has not yet been migrated.
1032 -- Do it now and return the newly created salt.
1033 RETURN actor.migrate_passwd(pw_usr);
1036 -- We have no salt to return. actor.create_salt() needed.
1039 $$ LANGUAGE PLPGSQL;
1041 CREATE OR REPLACE FUNCTION
1042 actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$
1045 usr_row actor.usr%ROWTYPE;
1047 /* Migrates legacy actor.usr.passwd value to actor.passwd with
1048 * a password type 'main' and returns the new salt. For backwards
1049 * compatibility with existing CHAP-style API's, we perform a
1050 * layer of intermediate MD5(MD5()) hashing. This is intermediate
1051 * hashing is not required of other passwords.
1054 -- Avoid calling get_salt() here, because it may result in a
1055 -- migrate_passwd() call, creating a loop.
1056 SELECT INTO pw_salt salt FROM actor.passwd
1057 WHERE usr = pw_usr AND passwd_type = 'main';
1059 -- Only migrate passwords that have not already been migrated.
1064 SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
1066 pw_salt := actor.create_salt('main');
1068 PERFORM actor.set_passwd(
1069 pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
1071 -- clear the existing password
1072 UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
1076 $$ LANGUAGE PLPGSQL;
1078 CREATE OR REPLACE FUNCTION
1079 actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT)
1080 RETURNS BOOLEAN AS $$
1084 /* Returns TRUE if the password provided matches the in-db password.
1085 * If the password type is salted, we compare the output of CRYPT().
1086 * NOTE: test_passwd is MD5(salt || MD5(password)) for legacy
1090 SELECT INTO pw_salt salt FROM actor.passwd
1091 WHERE usr = pw_usr AND passwd_type = pw_type;
1098 IF pw_salt IS NULL THEN
1099 -- Password is unsalted, compare the un-CRYPT'ed values.
1101 SELECT TRUE FROM actor.passwd WHERE
1103 passwd_type = pw_type AND
1104 passwd = test_passwd
1109 SELECT TRUE FROM actor.passwd WHERE
1111 passwd_type = pw_type AND
1112 passwd = CRYPT(test_passwd, pw_salt)
1115 $$ STRICT LANGUAGE PLPGSQL;
1117 -- Remove all activity entries by activity type,
1118 -- except the most recent entry per user.
1119 CREATE OR REPLACE FUNCTION
1120 actor.purge_usr_activity_by_type(act_type INTEGER)
1125 FOR cur_usr IN SELECT DISTINCT(usr)
1126 FROM actor.usr_activity WHERE etype = act_type LOOP
1127 DELETE FROM actor.usr_activity WHERE id IN (
1129 FROM actor.usr_activity
1130 WHERE usr = cur_usr AND etype = act_type
1131 ORDER BY event_time DESC OFFSET 1
1135 END $$ LANGUAGE PLPGSQL;
1137 CREATE TABLE actor.workstation_setting (
1138 id SERIAL PRIMARY KEY,
1139 workstation INT NOT NULL REFERENCES actor.workstation (id)
1140 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1141 name TEXT NOT NULL REFERENCES config.workstation_setting_type (name)
1142 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1143 value JSON NOT NULL,
1144 CONSTRAINT ws_once_per_key UNIQUE (workstation, name)
1147 CREATE INDEX actor_workstation_setting_workstation_idx
1148 ON actor.workstation_setting (workstation);
1150 CREATE TYPE actor.cascade_setting_summary AS (
1153 has_org_setting BOOLEAN,
1154 has_user_setting BOOLEAN,
1155 has_workstation_setting BOOLEAN
1158 CREATE OR REPLACE FUNCTION actor.get_cascade_setting(
1159 setting_name TEXT, org_id INT, user_id INT, workstation_id INT)
1160 RETURNS actor.cascade_setting_summary AS
1164 summary actor.cascade_setting_summary;
1165 org_setting_type config.org_unit_setting_type%ROWTYPE;
1168 summary.name := setting_name;
1170 -- Collect the org setting type status first in case we exit early.
1171 -- The existance of an org setting type is not considered
1172 -- privileged information.
1173 SELECT INTO org_setting_type *
1174 FROM config.org_unit_setting_type WHERE name = setting_name;
1176 summary.has_org_setting := TRUE;
1178 summary.has_org_setting := FALSE;
1181 -- User and workstation settings have the same priority.
1182 -- Start with user settings since that's the simplest code path.
1183 -- The workstation_id is ignored if no user_id is provided.
1184 IF user_id IS NOT NULL THEN
1186 SELECT INTO summary.value value FROM actor.usr_setting
1187 WHERE usr = user_id AND name = setting_name;
1190 -- if we have a value, we have a setting type
1191 summary.has_user_setting := TRUE;
1193 IF workstation_id IS NOT NULL THEN
1194 -- Only inform the caller about the workstation
1195 -- setting type disposition when a workstation id is
1196 -- provided. Otherwise, it's NULL to indicate UNKNOWN.
1197 summary.has_workstation_setting := FALSE;
1203 -- no user setting value, but a setting type may exist
1204 SELECT INTO summary.has_user_setting EXISTS (
1205 SELECT TRUE FROM config.usr_setting_type
1206 WHERE name = setting_name
1209 IF workstation_id IS NOT NULL THEN
1211 IF NOT summary.has_user_setting THEN
1212 -- A workstation setting type may only exist when a user
1213 -- setting type does not.
1215 SELECT INTO summary.value value
1216 FROM actor.workstation_setting
1217 WHERE workstation = workstation_id AND name = setting_name;
1220 -- if we have a value, we have a setting type
1221 summary.has_workstation_setting := TRUE;
1225 -- no value, but a setting type may exist
1226 SELECT INTO summary.has_workstation_setting EXISTS (
1227 SELECT TRUE FROM config.workstation_setting_type
1228 WHERE name = setting_name
1232 -- Finally make use of the workstation to determine the org
1233 -- unit if none is provided.
1234 IF org_id IS NULL AND summary.has_org_setting THEN
1235 SELECT INTO org_id owning_lib
1236 FROM actor.workstation WHERE id = workstation_id;
1241 -- Some org unit settings are protected by a view permission.
1242 -- First see if we have any data that needs protecting, then
1243 -- check the permission if needed.
1245 IF NOT summary.has_org_setting THEN
1249 -- avoid putting the value into the summary until we confirm
1250 -- the value should be visible to the caller.
1251 SELECT INTO setting_value value
1252 FROM actor.org_unit_ancestor_setting(setting_name, org_id);
1255 -- No value found -- perm check is irrelevant.
1259 IF org_setting_type.view_perm IS NOT NULL THEN
1261 IF user_id IS NULL THEN
1262 RAISE NOTICE 'Perm check required but no user_id provided';
1266 IF NOT permission.usr_has_perm(
1267 user_id, (SELECT code FROM permission.perm_list
1268 WHERE id = org_setting_type.view_perm), org_id)
1270 RAISE NOTICE 'Perm check failed for user % on %',
1271 user_id, org_setting_type.view_perm;
1276 -- Perm check succeeded or was not necessary.
1277 summary.value := setting_value;
1280 $FUNC$ LANGUAGE PLPGSQL;
1283 CREATE OR REPLACE FUNCTION actor.get_cascade_setting_batch(
1284 setting_names TEXT[], org_id INT, user_id INT, workstation_id INT)
1285 RETURNS SETOF actor.cascade_setting_summary AS
1287 -- Returns a row per setting matching the setting name order. If no
1288 -- value is applied, NULL is returned to retain name-response ordering.
1291 summary actor.cascade_setting_summary;
1293 FOREACH setting_name IN ARRAY setting_names LOOP
1294 SELECT INTO summary * FROM actor.get_cascade_setting(
1295 setting_Name, org_id, user_id, workstation_id);
1296 RETURN NEXT summary;
1299 $FUNC$ LANGUAGE PLPGSQL;
1301 CREATE TABLE actor.usr_privacy_waiver (
1302 id BIGSERIAL PRIMARY KEY,
1303 usr BIGINT NOT NULL REFERENCES actor.usr(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1305 place_holds BOOL DEFAULT FALSE,
1306 pickup_holds BOOL DEFAULT FALSE,
1307 view_history BOOL DEFAULT FALSE,
1308 checkout_items BOOL DEFAULT FALSE
1310 CREATE INDEX actor_usr_privacy_waiver_usr_idx ON actor.usr_privacy_waiver (usr);