2 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
3 * Mike Rylander <mrylander@gmail.com>
4 * Copyright (C) 2010 Laurentian University
5 * Dan Scott <dscott@laurentian.ca>
7 * This program is free software; you can redistribute it and/or
8 * modify it under the terms of the GNU General Public License
9 * as published by the Free Software Foundation; either version 2
10 * of the License, or (at your option) any later version.
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
18 DROP SCHEMA IF EXISTS actor CASCADE;
22 COMMENT ON SCHEMA actor IS $$
23 Holds all tables pertaining to users and libraries (org units).
26 CREATE TABLE actor.usr (
27 id SERIAL PRIMARY KEY,
28 card INT UNIQUE, -- active card
29 profile INT NOT NULL, -- patron profile
30 usrname TEXT NOT NULL UNIQUE,
33 standing INT NOT NULL DEFAULT 1 REFERENCES config.standing (id) DEFERRABLE INITIALLY DEFERRED,
34 ident_type INT NOT NULL REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED,
36 ident_type2 INT REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED,
38 net_access_level INT NOT NULL DEFAULT 1 REFERENCES config.net_access_level (id) DEFERRABLE INITIALLY DEFERRED,
41 first_given_name TEXT NOT NULL,
42 second_given_name TEXT,
43 family_name TEXT NOT NULL,
53 active BOOL NOT NULL DEFAULT TRUE,
54 master_account BOOL NOT NULL DEFAULT FALSE,
55 super_user BOOL NOT NULL DEFAULT FALSE,
56 barred BOOL NOT NULL DEFAULT FALSE,
57 deleted BOOL NOT NULL DEFAULT FALSE,
58 juvenile BOOL NOT NULL DEFAULT FALSE,
59 usrgroup SERIAL NOT NULL,
60 claims_returned_count INT NOT NULL DEFAULT 0,
61 credit_forward_balance NUMERIC(6,2) NOT NULL DEFAULT 0.00,
62 last_xact_id TEXT NOT NULL DEFAULT 'none',
64 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
65 expire_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT (now() + '3 years'::INTERVAL),
66 claims_never_checked_out_count INT NOT NULL DEFAULT 0,
67 last_update_time TIMESTAMP WITH TIME ZONE
69 COMMENT ON TABLE actor.usr IS $$
72 This table contains the core User objects that describe both
73 staff members and patrons. The difference between the two
74 types of users is based on the user's permissions.
77 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
78 CREATE INDEX actor_usr_usrgroup_idx ON actor.usr (usrgroup);
79 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
80 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
82 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name));
83 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name));
84 CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name));
85 CREATE INDEX actor_usr_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(first_given_name));
86 CREATE INDEX actor_usr_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(second_given_name));
87 CREATE INDEX actor_usr_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(family_name));
88 CREATE INDEX actor_usr_usrname_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(usrname));
90 CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname));
91 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
93 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
94 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
95 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
97 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
98 (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
100 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
101 (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
103 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
104 (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
106 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
107 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
109 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
111 NEW.passwd = MD5( NEW.passwd );
116 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
118 IF NEW.passwd <> OLD.passwd THEN
119 NEW.passwd = MD5( NEW.passwd );
125 CREATE OR REPLACE FUNCTION actor.au_updated()
126 RETURNS TRIGGER AS $$
128 NEW.last_update_time := now();
133 CREATE TRIGGER au_update_trig
134 BEFORE INSERT OR UPDATE ON actor.usr
135 FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
137 CREATE TRIGGER actor_crypt_pw_update_trigger
138 BEFORE UPDATE ON actor.usr FOR EACH ROW
139 EXECUTE PROCEDURE actor.crypt_pw_update ();
141 CREATE TRIGGER actor_crypt_pw_insert_trigger
142 BEFORE INSERT ON actor.usr FOR EACH ROW
143 EXECUTE PROCEDURE actor.crypt_pw_insert ();
145 CREATE RULE protect_user_delete AS ON DELETE TO actor.usr DO INSTEAD UPDATE actor.usr SET deleted = TRUE WHERE OLD.id = actor.usr.id;
147 CREATE TABLE actor.usr_note (
148 id BIGSERIAL PRIMARY KEY,
149 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
150 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
151 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
152 pub BOOL NOT NULL DEFAULT FALSE,
156 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
157 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
159 CREATE TABLE actor.usr_setting (
160 id BIGSERIAL PRIMARY KEY,
161 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
162 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
164 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
166 COMMENT ON TABLE actor.usr_setting IS $$
169 This table contains any arbitrary settings that a client
170 program would like to save for a user.
173 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
175 CREATE TABLE actor.stat_cat_sip_fields (
176 field CHAR(2) PRIMARY KEY,
178 one_only BOOL NOT NULL DEFAULT FALSE
180 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
181 Actor Statistical Category SIP Fields
183 Contains the list of valid SIP Field identifiers for
184 Statistical Categories.
187 CREATE TABLE actor.stat_cat (
188 id SERIAL PRIMARY KEY,
191 opac_visible BOOL NOT NULL DEFAULT FALSE,
192 usr_summary BOOL NOT NULL DEFAULT FALSE,
193 sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
195 checkout_archive BOOL NOT NULL DEFAULT FALSE,
196 required BOOL NOT NULL DEFAULT FALSE,
197 allow_freetext BOOL NOT NULL DEFAULT TRUE,
198 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
200 COMMENT ON TABLE actor.stat_cat IS $$
201 User Statistical Catagories
203 Local data collected about Users is placed into a Statistical
204 Catagory. Here's where those catagories are defined.
208 CREATE TABLE actor.stat_cat_entry (
209 id SERIAL PRIMARY KEY,
210 stat_cat INT NOT NULL,
213 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
215 COMMENT ON TABLE actor.stat_cat_entry IS $$
216 User Statistical Catagory Entries
218 Local data collected about Users is placed into a Statistical
219 Catagory. Each library can create entries into any of its own
220 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
224 CREATE TABLE actor.stat_cat_entry_usr_map (
225 id BIGSERIAL PRIMARY KEY,
226 stat_cat_entry TEXT NOT NULL,
227 stat_cat INT NOT NULL,
228 target_usr INT NOT NULL,
229 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
231 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
232 Statistical Catagory Entry to User map
234 Records the stat_cat entries for each user.
237 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
239 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
241 sipfield actor.stat_cat_sip_fields%ROWTYPE;
244 IF NEW.sip_field IS NOT NULL THEN
245 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
246 IF sipfield.one_only THEN
247 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
248 IF use_count > 0 THEN
249 RAISE EXCEPTION 'Sip field cannot be used twice';
255 $func$ LANGUAGE PLPGSQL;
257 CREATE TRIGGER actor_stat_cat_sip_update_trigger
258 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
259 EXECUTE PROCEDURE actor.stat_cat_check();
261 CREATE TABLE actor.card (
262 id SERIAL PRIMARY KEY,
263 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
264 barcode TEXT NOT NULL UNIQUE,
265 active BOOL NOT NULL DEFAULT TRUE
267 COMMENT ON TABLE actor.card IS $$
270 Each User has one or more library cards. The current "main"
271 card is linked to here from the actor.usr table, and it is up
272 to the consortium policy whether more than one card can be
273 active for any one user at a given time.
276 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
277 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
279 CREATE TABLE actor.org_unit_type (
280 id SERIAL PRIMARY KEY,
282 opac_label TEXT NOT NULL,
284 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
285 can_have_vols BOOL NOT NULL DEFAULT TRUE,
286 can_have_users BOOL NOT NULL DEFAULT TRUE
288 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
290 CREATE TABLE actor.org_unit (
291 id SERIAL PRIMARY KEY,
292 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
293 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
298 shortname TEXT NOT NULL UNIQUE,
299 name TEXT NOT NULL UNIQUE,
302 opac_visible BOOL NOT NULL DEFAULT TRUE,
303 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
305 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
306 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
307 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
308 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
309 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
310 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
312 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
314 current_aou actor.org_unit%ROWTYPE;
320 seen_ous := ARRAY[NEW.id];
322 IF (TG_OP = 'UPDATE') THEN
323 IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
324 RETURN NEW; -- Doing an UPDATE with no change, just return it
329 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
330 RETURN NEW; -- No loop. Carry on.
332 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
333 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
336 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
337 seen_ous := seen_ous || current_aou.id;
338 depth_count := depth_count + 1;
339 IF depth_count = 100 THEN
340 RAISE 'OU CHECK TOO DEEP';
348 CREATE TRIGGER actor_org_unit_parent_protect_trigger
349 BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
350 EXECUTE PROCEDURE actor.org_unit_parent_protect ();
352 CREATE TABLE actor.org_lasso (
353 id SERIAL PRIMARY KEY,
357 CREATE TABLE actor.org_lasso_map (
358 id SERIAL PRIMARY KEY,
359 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
360 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
362 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
363 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
365 CREATE TABLE actor.org_unit_proximity (
366 id BIGSERIAL PRIMARY KEY,
371 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
373 CREATE TABLE actor.stat_cat_entry_default (
374 id SERIAL PRIMARY KEY,
375 stat_cat_entry INT NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
376 stat_cat INT NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
377 owner INT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
378 CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
380 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
381 User Statistical Category Default Entry
383 A library may choose one of the stat_cat entries to be the
388 CREATE TABLE actor.org_unit_proximity_adjustment (
389 id SERIAL PRIMARY KEY,
390 item_circ_lib INT REFERENCES actor.org_unit (id),
391 item_owning_lib INT REFERENCES actor.org_unit (id),
392 copy_location INT, -- REFERENCES asset.copy_location (id),
393 hold_pickup_lib INT REFERENCES actor.org_unit (id),
394 hold_request_lib INT REFERENCES actor.org_unit (id),
395 pos INT NOT NULL DEFAULT 0,
396 absolute_adjustment BOOL NOT NULL DEFAULT FALSE,
397 prox_adjustment NUMERIC,
398 circ_mod TEXT, -- REFERENCES config.circ_modifier (code),
399 CONSTRAINT prox_adj_criterium CHECK (COALESCE(item_circ_lib::TEXT,item_owning_lib::TEXT,copy_location::TEXT,hold_pickup_lib::TEXT,hold_request_lib::TEXT,circ_mod) IS NOT NULL)
401 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
402 COALESCE(item_circ_lib, -1),
403 COALESCE(item_owning_lib, -1),
404 COALESCE(copy_location, -1),
405 COALESCE(hold_pickup_lib, -1),
406 COALESCE(hold_request_lib, -1),
407 COALESCE(circ_mod, ''),
410 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
411 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
412 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
413 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
414 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
415 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
417 CREATE TABLE actor.hours_of_operation (
418 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
419 dow_0_open TIME NOT NULL DEFAULT '09:00',
420 dow_0_close TIME NOT NULL DEFAULT '17:00',
421 dow_1_open TIME NOT NULL DEFAULT '09:00',
422 dow_1_close TIME NOT NULL DEFAULT '17:00',
423 dow_2_open TIME NOT NULL DEFAULT '09:00',
424 dow_2_close TIME NOT NULL DEFAULT '17:00',
425 dow_3_open TIME NOT NULL DEFAULT '09:00',
426 dow_3_close TIME NOT NULL DEFAULT '17:00',
427 dow_4_open TIME NOT NULL DEFAULT '09:00',
428 dow_4_close TIME NOT NULL DEFAULT '17:00',
429 dow_5_open TIME NOT NULL DEFAULT '09:00',
430 dow_5_close TIME NOT NULL DEFAULT '17:00',
431 dow_6_open TIME NOT NULL DEFAULT '09:00',
432 dow_6_close TIME NOT NULL DEFAULT '17:00'
434 COMMENT ON TABLE actor.hours_of_operation IS $$
435 When does this org_unit usually open and close? (Variations
436 are expressed in the actor.org_unit_closed table.)
438 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
439 When does this org_unit open on Monday?
441 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
442 When does this org_unit close on Monday?
444 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
445 When does this org_unit open on Tuesday?
447 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
448 When does this org_unit close on Tuesday?
450 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
451 When does this org_unit open on Wednesday?
453 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
454 When does this org_unit close on Wednesday?
456 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
457 When does this org_unit open on Thursday?
459 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
460 When does this org_unit close on Thursday?
462 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
463 When does this org_unit open on Friday?
465 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
466 When does this org_unit close on Friday?
468 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
469 When does this org_unit open on Saturday?
471 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
472 When does this org_unit close on Saturday?
474 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
475 When does this org_unit open on Sunday?
477 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
478 When does this org_unit close on Sunday?
481 CREATE TABLE actor.org_unit_closed (
482 id SERIAL PRIMARY KEY,
483 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
484 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
485 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
489 -- Workstation registration...
490 CREATE TABLE actor.workstation (
491 id SERIAL PRIMARY KEY,
492 name TEXT NOT NULL UNIQUE,
493 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
496 CREATE TABLE actor.usr_org_unit_opt_in (
497 id SERIAL PRIMARY KEY,
498 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
499 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
500 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
501 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
502 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
503 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
505 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
507 CREATE TABLE actor.org_unit_setting (
508 id BIGSERIAL PRIMARY KEY,
509 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
510 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
512 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
513 CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
515 COMMENT ON TABLE actor.org_unit_setting IS $$
518 This table contains any arbitrary settings that a client
519 program would like to save for an org unit.
522 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
524 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
525 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
529 -- Check for which setting is being updated, and log it.
530 SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
532 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
536 $ous_change_log$ LANGUAGE plpgsql;
538 CREATE TRIGGER log_ous_change
539 BEFORE INSERT OR UPDATE ON actor.org_unit_setting
540 FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
542 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
546 -- Check for which setting is being updated, and log it.
547 SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
549 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
553 $ous_delete_log$ LANGUAGE plpgsql;
555 CREATE TRIGGER log_ous_del
556 BEFORE DELETE ON actor.org_unit_setting
557 FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
562 CREATE TABLE actor.usr_address (
563 id SERIAL PRIMARY KEY,
564 valid BOOL NOT NULL DEFAULT TRUE,
565 within_city_limits BOOL NOT NULL DEFAULT TRUE,
566 address_type TEXT NOT NULL DEFAULT 'MAILING',
567 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
568 street1 TEXT NOT NULL,
573 country TEXT NOT NULL,
574 post_code TEXT NOT NULL,
575 pending BOOL NOT NULL DEFAULT FALSE,
576 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
579 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
581 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
582 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
584 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
585 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
586 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
588 CREATE TABLE actor.usr_password_reset (
589 id SERIAL PRIMARY KEY,
591 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
592 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
593 has_been_reset BOOL NOT NULL DEFAULT false
595 COMMENT ON TABLE actor.usr_password_reset IS $$
596 Self-serve password reset requests
598 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
599 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
600 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
601 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
603 CREATE TABLE actor.org_address (
604 id SERIAL PRIMARY KEY,
605 valid BOOL NOT NULL DEFAULT TRUE,
606 address_type TEXT NOT NULL DEFAULT 'MAILING',
607 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
608 street1 TEXT NOT NULL,
613 country TEXT NOT NULL,
614 post_code TEXT NOT NULL,
618 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
620 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
621 SELECT SUBSTRING( $1, 1, 5);
624 CREATE TABLE actor.usr_standing_penalty (
625 id SERIAL PRIMARY KEY,
626 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
627 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
628 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
629 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
630 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
631 stop_date TIMESTAMP WITH TIME ZONE,
634 COMMENT ON TABLE actor.usr_standing_penalty IS $$
635 User standing penalties
638 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
639 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
642 CREATE TABLE actor.usr_saved_search (
643 id SERIAL PRIMARY KEY,
644 owner INT NOT NULL REFERENCES actor.usr (id)
646 DEFERRABLE INITIALLY DEFERRED,
648 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
649 query_text TEXT NOT NULL,
650 query_type TEXT NOT NULL
651 CONSTRAINT valid_query_text CHECK (
652 query_type IN ( 'URL' )) DEFAULT 'URL',
653 -- we may add other types someday
655 CONSTRAINT valid_target CHECK (
656 target IN ( 'record', 'metarecord', 'callnumber' )),
657 CONSTRAINT name_once_per_user UNIQUE (owner, name)
660 CREATE TABLE actor.address_alert (
661 id SERIAL PRIMARY KEY,
662 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
663 active BOOL NOT NULL DEFAULT TRUE,
664 match_all BOOL NOT NULL DEFAULT TRUE,
665 alert_message TEXT NOT NULL,
673 mailing_address BOOL NOT NULL DEFAULT FALSE,
674 billing_address BOOL NOT NULL DEFAULT FALSE
677 CREATE TABLE actor.usr_activity (
678 id BIGSERIAL PRIMARY KEY,
679 usr INT REFERENCES actor.usr (id) ON DELETE SET NULL,
680 etype INT NOT NULL REFERENCES config.usr_activity_type (id),
681 event_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
683 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
685 CREATE TABLE actor.toolbar (
686 id BIGSERIAL PRIMARY KEY,
687 ws INT REFERENCES actor.workstation (id) ON DELETE CASCADE,
688 org INT REFERENCES actor.org_unit (id) ON DELETE CASCADE,
689 usr INT REFERENCES actor.usr (id) ON DELETE CASCADE,
691 layout TEXT NOT NULL,
692 CONSTRAINT only_one_type CHECK (
693 (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
694 (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
695 (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
697 CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
699 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
700 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
701 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
703 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
704 CREATE TABLE actor.org_unit_custom_tree (
705 id SERIAL PRIMARY KEY,
706 active BOOLEAN DEFAULT FALSE,
707 purpose actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
710 CREATE TABLE actor.org_unit_custom_tree_node (
711 id SERIAL PRIMARY KEY,
712 tree INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
713 org_unit INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
714 parent_node INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
715 sibling_order INTEGER NOT NULL DEFAULT 0,
716 CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
719 CREATE TABLE actor.search_query (
720 id SERIAL PRIMARY KEY,
721 label TEXT NOT NULL, -- i18n
722 query_text TEXT NOT NULL -- QP text
725 CREATE TABLE actor.search_filter_group (
726 id SERIAL PRIMARY KEY,
727 owner INT NOT NULL REFERENCES actor.org_unit (id)
728 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
729 code TEXT NOT NULL, -- for CGI, etc.
730 label TEXT NOT NULL, -- i18n
731 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
732 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
733 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
736 CREATE TABLE actor.search_filter_group_entry (
737 id SERIAL PRIMARY KEY,
738 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
739 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
740 pos INT NOT NULL DEFAULT 0,
741 query INT NOT NULL REFERENCES actor.search_query(id)
742 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
743 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
746 CREATE TABLE actor.usr_message (
747 id SERIAL PRIMARY KEY,
748 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
750 message TEXT NOT NULL,
751 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
752 deleted BOOL NOT NULL DEFAULT FALSE,
753 read_date TIMESTAMP WITH TIME ZONE,
754 sending_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
756 CREATE INDEX aum_usr ON actor.usr_message (usr);
758 CREATE RULE protect_usr_message_delete AS
759 ON DELETE TO actor.usr_message DO INSTEAD (
760 UPDATE actor.usr_message
762 WHERE OLD.id = actor.usr_message.id
765 CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$
770 IF TG_OP = 'UPDATE' THEN
771 IF OLD.pub = TRUE THEN
776 SELECT INTO sending_ou aw.owning_lib
777 FROM auditor.get_audit_info() agai
778 JOIN actor.workstation aw ON (aw.id = agai.eg_ws);
779 IF sending_ou IS NULL THEN
780 SELECT INTO sending_ou home_ou
782 WHERE id = NEW.creator;
784 INSERT INTO actor.usr_message (usr, title, message, sending_lib)
785 VALUES (NEW.usr, NEW.title, NEW.value, sending_ou);
792 CREATE TRIGGER convert_usr_note_to_message_tgr
793 AFTER INSERT OR UPDATE ON actor.usr_note
794 FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message();
796 -- limited view to ensure that a library user who somehow
797 -- manages to figure out how to access pcrud cannot change
798 -- the text of messages sent them
799 CREATE VIEW actor.usr_message_limited
800 AS SELECT * FROM actor.usr_message;
802 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
804 IF TG_OP = 'UPDATE' THEN
805 UPDATE actor.usr_message
806 SET read_date = NEW.read_date,
807 deleted = NEW.deleted
815 CREATE TRIGGER restrict_usr_message_limited_tgr
816 INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
817 FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
819 CREATE TABLE actor.passwd_type (
820 code TEXT PRIMARY KEY,
821 name TEXT UNIQUE NOT NULL,
822 login BOOLEAN NOT NULL DEFAULT FALSE,
823 regex TEXT, -- pending
824 crypt_algo TEXT, -- e.g. 'bf'
826 -- gen_salt() iter count used with each new salt.
827 -- A non-NULL value for iter_count is our indication the
828 -- password is salted and encrypted via crypt()
829 iter_count INTEGER CHECK (iter_count IS NULL OR iter_count > 0)
832 CREATE TABLE actor.passwd (
833 id SERIAL PRIMARY KEY,
834 usr INTEGER NOT NULL REFERENCES actor.usr(id)
835 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
836 salt TEXT, -- will be NULL for non-crypt'ed passwords
837 passwd TEXT NOT NULL,
838 passwd_type TEXT NOT NULL REFERENCES actor.passwd_type(code)
839 DEFERRABLE INITIALLY DEFERRED,
840 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
841 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
842 CONSTRAINT passwd_type_once_per_user UNIQUE (usr, passwd_type)
845 CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT)
848 type_row actor.passwd_type%ROWTYPE;
850 /* Returns a new salt based on the passwd_type encryption settings.
851 * Returns NULL If the password type is not crypt()'ed.
854 SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;
857 RETURN EXCEPTION 'No such password type: %', pw_type;
860 IF type_row.iter_count IS NULL THEN
861 -- This password type is unsalted. That's OK.
865 RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
871 TODO: when a user changes their password in the application, the
872 app layer has access to the bare password. At that point, we have
873 the opportunity to store the new password without the MD5(MD5())
874 intermediate hashing. Do we care? We would need a way to indicate
875 which passwords have the legacy intermediate hashing and which don't
876 so the app layer would know whether it should perform the intermediate
877 hashing. In either event, with the exception of migrate_passwd(), the
878 DB functions know or care nothing about intermediate hashing. Every
879 password is just a value that may or may not be internally crypt'ed.
882 CREATE OR REPLACE FUNCTION actor.set_passwd(
883 pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL)
884 RETURNS BOOLEAN AS $$
889 /* Sets the password value, creating a new actor.passwd row if needed.
890 * If the password type supports it, the new_pass value is crypt()'ed.
891 * For crypt'ed passwords, the salt comes from one of 3 places in order:
892 * new_salt (if present), existing salt (if present), newly created
896 IF new_salt IS NOT NULL THEN
899 pw_salt := actor.get_salt(pw_usr, pw_type);
901 IF pw_salt IS NULL THEN
902 /* We have no salt for this user + type. Assume they want a
903 * new salt. If this type is unsalted, create_salt() will
905 pw_salt := actor.create_salt(pw_type);
909 IF pw_salt IS NULL THEN
910 pw_text := new_pass; -- unsalted, use as-is.
912 pw_text := CRYPT(new_pass, pw_salt);
916 SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
917 WHERE usr = pw_usr AND passwd_type = pw_type;
920 -- no password row exists for this user + type. Create one.
921 INSERT INTO actor.passwd (usr, passwd_type, salt, passwd)
922 VALUES (pw_usr, pw_type, pw_salt, pw_text);
929 CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT)
933 type_row actor.passwd_type%ROWTYPE;
935 /* Returns the salt for the requested user + type. If the password
936 * type of "main" is requested and no password exists in actor.passwd,
937 * the user's existing password is migrated and the new salt is returned.
938 * Returns NULL if the password type is not crypt'ed (iter_count is NULL).
941 SELECT INTO pw_salt salt FROM actor.passwd
942 WHERE usr = pw_usr AND passwd_type = pw_type;
948 IF pw_type = 'main' THEN
949 -- Main password has not yet been migrated.
950 -- Do it now and return the newly created salt.
951 RETURN actor.migrate_passwd(pw_usr);
954 -- We have no salt to return. actor.create_salt() needed.
959 CREATE OR REPLACE FUNCTION
960 actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$
963 usr_row actor.usr%ROWTYPE;
965 /* Migrates legacy actor.usr.passwd value to actor.passwd with
966 * a password type 'main' and returns the new salt. For backwards
967 * compatibility with existing CHAP-style API's, we perform a
968 * layer of intermediate MD5(MD5()) hashing. This is intermediate
969 * hashing is not required of other passwords.
972 -- Avoid calling get_salt() here, because it may result in a
973 -- migrate_passwd() call, creating a loop.
974 SELECT INTO pw_salt salt FROM actor.passwd
975 WHERE usr = pw_usr AND passwd_type = 'main';
977 -- Only migrate passwords that have not already been migrated.
982 SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
984 pw_salt := actor.create_salt('main');
986 PERFORM actor.set_passwd(
987 pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
989 -- clear the existing password
990 UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
996 CREATE OR REPLACE FUNCTION
997 actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT)
998 RETURNS BOOLEAN AS $$
1002 /* Returns TRUE if the password provided matches the in-db password.
1003 * If the password type is salted, we compare the output of CRYPT().
1004 * NOTE: test_passwd is MD5(salt || MD5(password)) for legacy
1008 SELECT INTO pw_salt salt FROM actor.passwd
1009 WHERE usr = pw_usr AND passwd_type = pw_type;
1016 IF pw_salt IS NULL THEN
1017 -- Password is unsalted, compare the un-CRYPT'ed values.
1019 SELECT TRUE FROM actor.passwd WHERE
1021 passwd_type = pw_type AND
1022 passwd = test_passwd
1027 SELECT TRUE FROM actor.passwd WHERE
1029 passwd_type = pw_type AND
1030 passwd = CRYPT(test_passwd, pw_salt)
1033 $$ STRICT LANGUAGE PLPGSQL;
1035 -- Remove all activity entries by activity type,
1036 -- except the most recent entry per user.
1037 CREATE OR REPLACE FUNCTION
1038 actor.purge_usr_activity_by_type(act_type INTEGER)
1043 FOR cur_usr IN SELECT DISTINCT(usr)
1044 FROM actor.usr_activity WHERE etype = act_type LOOP
1045 DELETE FROM actor.usr_activity WHERE id IN (
1047 FROM actor.usr_activity
1048 WHERE usr = cur_usr AND etype = act_type
1049 ORDER BY event_time DESC OFFSET 1
1053 END $$ LANGUAGE PLPGSQL;