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));
89 CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname));
90 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
92 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
93 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
94 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
96 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
97 (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
99 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
100 (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
102 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
103 (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
105 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
106 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
108 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
110 NEW.passwd = MD5( NEW.passwd );
115 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
117 IF NEW.passwd <> OLD.passwd THEN
118 NEW.passwd = MD5( NEW.passwd );
124 CREATE OR REPLACE FUNCTION actor.au_updated()
125 RETURNS TRIGGER AS $$
127 NEW.last_update_time := now();
132 CREATE TRIGGER au_update_trig
133 BEFORE INSERT OR UPDATE ON actor.usr
134 FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
136 CREATE TRIGGER actor_crypt_pw_update_trigger
137 BEFORE UPDATE ON actor.usr FOR EACH ROW
138 EXECUTE PROCEDURE actor.crypt_pw_update ();
140 CREATE TRIGGER actor_crypt_pw_insert_trigger
141 BEFORE INSERT ON actor.usr FOR EACH ROW
142 EXECUTE PROCEDURE actor.crypt_pw_insert ();
144 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;
146 CREATE TABLE actor.usr_note (
147 id BIGSERIAL PRIMARY KEY,
148 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
149 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
150 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
151 pub BOOL NOT NULL DEFAULT FALSE,
155 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
156 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
158 CREATE TABLE actor.usr_setting (
159 id BIGSERIAL PRIMARY KEY,
160 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
161 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
163 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
165 COMMENT ON TABLE actor.usr_setting IS $$
168 This table contains any arbitrary settings that a client
169 program would like to save for a user.
172 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
174 CREATE TABLE actor.stat_cat_sip_fields (
175 field CHAR(2) PRIMARY KEY,
177 one_only BOOL NOT NULL DEFAULT FALSE
179 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
180 Actor Statistical Category SIP Fields
182 Contains the list of valid SIP Field identifiers for
183 Statistical Categories.
186 CREATE TABLE actor.stat_cat (
187 id SERIAL PRIMARY KEY,
190 opac_visible BOOL NOT NULL DEFAULT FALSE,
191 usr_summary BOOL NOT NULL DEFAULT FALSE,
192 sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
194 checkout_archive BOOL NOT NULL DEFAULT FALSE,
195 required BOOL NOT NULL DEFAULT FALSE,
196 allow_freetext BOOL NOT NULL DEFAULT TRUE,
197 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
199 COMMENT ON TABLE actor.stat_cat IS $$
200 User Statistical Catagories
202 Local data collected about Users is placed into a Statistical
203 Catagory. Here's where those catagories are defined.
207 CREATE TABLE actor.stat_cat_entry (
208 id SERIAL PRIMARY KEY,
209 stat_cat INT NOT NULL,
212 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
214 COMMENT ON TABLE actor.stat_cat_entry IS $$
215 User Statistical Catagory Entries
217 Local data collected about Users is placed into a Statistical
218 Catagory. Each library can create entries into any of its own
219 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
223 CREATE TABLE actor.stat_cat_entry_usr_map (
224 id BIGSERIAL PRIMARY KEY,
225 stat_cat_entry TEXT NOT NULL,
226 stat_cat INT NOT NULL,
227 target_usr INT NOT NULL,
228 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
230 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
231 Statistical Catagory Entry to User map
233 Records the stat_cat entries for each user.
236 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
238 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
240 sipfield actor.stat_cat_sip_fields%ROWTYPE;
243 IF NEW.sip_field IS NOT NULL THEN
244 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
245 IF sipfield.one_only THEN
246 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
247 IF use_count > 0 THEN
248 RAISE EXCEPTION 'Sip field cannot be used twice';
254 $func$ LANGUAGE PLPGSQL;
256 CREATE TRIGGER actor_stat_cat_sip_update_trigger
257 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
258 EXECUTE PROCEDURE actor.stat_cat_check();
260 CREATE TABLE actor.card (
261 id SERIAL PRIMARY KEY,
262 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
263 barcode TEXT NOT NULL UNIQUE,
264 active BOOL NOT NULL DEFAULT TRUE
266 COMMENT ON TABLE actor.card IS $$
269 Each User has one or more library cards. The current "main"
270 card is linked to here from the actor.usr table, and it is up
271 to the consortium policy whether more than one card can be
272 active for any one user at a given time.
275 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
276 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
278 CREATE TABLE actor.org_unit_type (
279 id SERIAL PRIMARY KEY,
281 opac_label TEXT NOT NULL,
283 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
284 can_have_vols BOOL NOT NULL DEFAULT TRUE,
285 can_have_users BOOL NOT NULL DEFAULT TRUE
287 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
289 CREATE TABLE actor.org_unit (
290 id SERIAL PRIMARY KEY,
291 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
292 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
297 shortname TEXT NOT NULL UNIQUE,
298 name TEXT NOT NULL UNIQUE,
301 opac_visible BOOL NOT NULL DEFAULT TRUE,
302 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
304 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
305 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
306 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
307 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
308 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
309 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
311 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
313 current_aou actor.org_unit%ROWTYPE;
319 seen_ous := ARRAY[NEW.id];
321 IF (TG_OP = 'UPDATE') THEN
322 IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
323 RETURN NEW; -- Doing an UPDATE with no change, just return it
328 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
329 RETURN NEW; -- No loop. Carry on.
331 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
332 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
335 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
336 seen_ous := seen_ous || current_aou.id;
337 depth_count := depth_count + 1;
338 IF depth_count = 100 THEN
339 RAISE 'OU CHECK TOO DEEP';
347 CREATE TRIGGER actor_org_unit_parent_protect_trigger
348 BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
349 EXECUTE PROCEDURE actor.org_unit_parent_protect ();
351 CREATE TABLE actor.org_lasso (
352 id SERIAL PRIMARY KEY,
356 CREATE TABLE actor.org_lasso_map (
357 id SERIAL PRIMARY KEY,
358 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
359 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
361 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
362 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
364 CREATE TABLE actor.org_unit_proximity (
365 id BIGSERIAL PRIMARY KEY,
370 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
372 CREATE TABLE actor.stat_cat_entry_default (
373 id SERIAL PRIMARY KEY,
374 stat_cat_entry INT NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
375 stat_cat INT NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
376 owner INT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
377 CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
379 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
380 User Statistical Category Default Entry
382 A library may choose one of the stat_cat entries to be the
387 CREATE TABLE actor.org_unit_proximity_adjustment (
388 id SERIAL PRIMARY KEY,
389 item_circ_lib INT REFERENCES actor.org_unit (id),
390 item_owning_lib INT REFERENCES actor.org_unit (id),
391 copy_location INT, -- REFERENCES asset.copy_location (id),
392 hold_pickup_lib INT REFERENCES actor.org_unit (id),
393 hold_request_lib INT REFERENCES actor.org_unit (id),
394 pos INT NOT NULL DEFAULT 0,
395 absolute_adjustment BOOL NOT NULL DEFAULT FALSE,
396 prox_adjustment NUMERIC,
397 circ_mod TEXT, -- REFERENCES config.circ_modifier (code),
398 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)
400 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
401 COALESCE(item_circ_lib, -1),
402 COALESCE(item_owning_lib, -1),
403 COALESCE(copy_location, -1),
404 COALESCE(hold_pickup_lib, -1),
405 COALESCE(hold_request_lib, -1),
406 COALESCE(circ_mod, ''),
409 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
410 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
411 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
412 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
413 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
414 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
416 CREATE TABLE actor.hours_of_operation (
417 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
418 dow_0_open TIME NOT NULL DEFAULT '09:00',
419 dow_0_close TIME NOT NULL DEFAULT '17:00',
420 dow_1_open TIME NOT NULL DEFAULT '09:00',
421 dow_1_close TIME NOT NULL DEFAULT '17:00',
422 dow_2_open TIME NOT NULL DEFAULT '09:00',
423 dow_2_close TIME NOT NULL DEFAULT '17:00',
424 dow_3_open TIME NOT NULL DEFAULT '09:00',
425 dow_3_close TIME NOT NULL DEFAULT '17:00',
426 dow_4_open TIME NOT NULL DEFAULT '09:00',
427 dow_4_close TIME NOT NULL DEFAULT '17:00',
428 dow_5_open TIME NOT NULL DEFAULT '09:00',
429 dow_5_close TIME NOT NULL DEFAULT '17:00',
430 dow_6_open TIME NOT NULL DEFAULT '09:00',
431 dow_6_close TIME NOT NULL DEFAULT '17:00'
433 COMMENT ON TABLE actor.hours_of_operation IS $$
434 When does this org_unit usually open and close? (Variations
435 are expressed in the actor.org_unit_closed table.)
437 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
438 When does this org_unit open on Monday?
440 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
441 When does this org_unit close on Monday?
443 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
444 When does this org_unit open on Tuesday?
446 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
447 When does this org_unit close on Tuesday?
449 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
450 When does this org_unit open on Wednesday?
452 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
453 When does this org_unit close on Wednesday?
455 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
456 When does this org_unit open on Thursday?
458 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
459 When does this org_unit close on Thursday?
461 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
462 When does this org_unit open on Friday?
464 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
465 When does this org_unit close on Friday?
467 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
468 When does this org_unit open on Saturday?
470 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
471 When does this org_unit close on Saturday?
473 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
474 When does this org_unit open on Sunday?
476 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
477 When does this org_unit close on Sunday?
480 CREATE TABLE actor.org_unit_closed (
481 id SERIAL PRIMARY KEY,
482 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
483 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
484 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
488 -- Workstation registration...
489 CREATE TABLE actor.workstation (
490 id SERIAL PRIMARY KEY,
491 name TEXT NOT NULL UNIQUE,
492 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
495 CREATE TABLE actor.usr_org_unit_opt_in (
496 id SERIAL PRIMARY KEY,
497 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
498 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
499 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
500 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
501 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
502 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
504 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
506 CREATE TABLE actor.org_unit_setting (
507 id BIGSERIAL PRIMARY KEY,
508 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
509 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
511 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
512 CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
514 COMMENT ON TABLE actor.org_unit_setting IS $$
517 This table contains any arbitrary settings that a client
518 program would like to save for an org unit.
521 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
523 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
524 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
528 -- Check for which setting is being updated, and log it.
529 SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
531 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
535 $ous_change_log$ LANGUAGE plpgsql;
537 CREATE TRIGGER log_ous_change
538 BEFORE INSERT OR UPDATE ON actor.org_unit_setting
539 FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
541 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
545 -- Check for which setting is being updated, and log it.
546 SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
548 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
552 $ous_delete_log$ LANGUAGE plpgsql;
554 CREATE TRIGGER log_ous_del
555 BEFORE DELETE ON actor.org_unit_setting
556 FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
561 CREATE TABLE actor.usr_address (
562 id SERIAL PRIMARY KEY,
563 valid BOOL NOT NULL DEFAULT TRUE,
564 within_city_limits BOOL NOT NULL DEFAULT TRUE,
565 address_type TEXT NOT NULL DEFAULT 'MAILING',
566 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
567 street1 TEXT NOT NULL,
572 country TEXT NOT NULL,
573 post_code TEXT NOT NULL,
574 pending BOOL NOT NULL DEFAULT FALSE,
575 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
578 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
580 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
581 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
583 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
584 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
585 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
587 CREATE TABLE actor.usr_password_reset (
588 id SERIAL PRIMARY KEY,
590 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
591 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
592 has_been_reset BOOL NOT NULL DEFAULT false
594 COMMENT ON TABLE actor.usr_password_reset IS $$
595 Self-serve password reset requests
597 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
598 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
599 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
600 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
602 CREATE TABLE actor.org_address (
603 id SERIAL PRIMARY KEY,
604 valid BOOL NOT NULL DEFAULT TRUE,
605 address_type TEXT NOT NULL DEFAULT 'MAILING',
606 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
607 street1 TEXT NOT NULL,
612 country TEXT NOT NULL,
613 post_code TEXT NOT NULL,
617 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
619 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
620 SELECT SUBSTRING( $1, 1, 5);
623 CREATE TABLE actor.usr_standing_penalty (
624 id SERIAL PRIMARY KEY,
625 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
626 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
627 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
628 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
629 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
630 stop_date TIMESTAMP WITH TIME ZONE,
633 COMMENT ON TABLE actor.usr_standing_penalty IS $$
634 User standing penalties
637 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
638 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
641 CREATE TABLE actor.usr_saved_search (
642 id SERIAL PRIMARY KEY,
643 owner INT NOT NULL REFERENCES actor.usr (id)
645 DEFERRABLE INITIALLY DEFERRED,
647 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
648 query_text TEXT NOT NULL,
649 query_type TEXT NOT NULL
650 CONSTRAINT valid_query_text CHECK (
651 query_type IN ( 'URL' )) DEFAULT 'URL',
652 -- we may add other types someday
654 CONSTRAINT valid_target CHECK (
655 target IN ( 'record', 'metarecord', 'callnumber' )),
656 CONSTRAINT name_once_per_user UNIQUE (owner, name)
659 CREATE TABLE actor.address_alert (
660 id SERIAL PRIMARY KEY,
661 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
662 active BOOL NOT NULL DEFAULT TRUE,
663 match_all BOOL NOT NULL DEFAULT TRUE,
664 alert_message TEXT NOT NULL,
672 mailing_address BOOL NOT NULL DEFAULT FALSE,
673 billing_address BOOL NOT NULL DEFAULT FALSE
676 CREATE TABLE actor.usr_activity (
677 id BIGSERIAL PRIMARY KEY,
678 usr INT REFERENCES actor.usr (id) ON DELETE SET NULL,
679 etype INT NOT NULL REFERENCES config.usr_activity_type (id),
680 event_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
682 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
684 CREATE TABLE actor.toolbar (
685 id BIGSERIAL PRIMARY KEY,
686 ws INT REFERENCES actor.workstation (id) ON DELETE CASCADE,
687 org INT REFERENCES actor.org_unit (id) ON DELETE CASCADE,
688 usr INT REFERENCES actor.usr (id) ON DELETE CASCADE,
690 layout TEXT NOT NULL,
691 CONSTRAINT only_one_type CHECK (
692 (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
693 (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
694 (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
696 CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
698 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
699 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
700 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
702 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
703 CREATE TABLE actor.org_unit_custom_tree (
704 id SERIAL PRIMARY KEY,
705 active BOOLEAN DEFAULT FALSE,
706 purpose actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
709 CREATE TABLE actor.org_unit_custom_tree_node (
710 id SERIAL PRIMARY KEY,
711 tree INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
712 org_unit INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
713 parent_node INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
714 sibling_order INTEGER NOT NULL DEFAULT 0,
715 CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
718 CREATE TABLE actor.search_query (
719 id SERIAL PRIMARY KEY,
720 label TEXT NOT NULL, -- i18n
721 query_text TEXT NOT NULL -- QP text
724 CREATE TABLE actor.search_filter_group (
725 id SERIAL PRIMARY KEY,
726 owner INT NOT NULL REFERENCES actor.org_unit (id)
727 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
728 code TEXT NOT NULL, -- for CGI, etc.
729 label TEXT NOT NULL, -- i18n
730 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
731 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
732 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
735 CREATE TABLE actor.search_filter_group_entry (
736 id SERIAL PRIMARY KEY,
737 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
738 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
739 pos INT NOT NULL DEFAULT 0,
740 query INT NOT NULL REFERENCES actor.search_query(id)
741 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
742 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
745 CREATE TABLE actor.usr_message (
746 id SERIAL PRIMARY KEY,
747 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
749 message TEXT NOT NULL,
750 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
751 deleted BOOL NOT NULL DEFAULT FALSE,
752 read_date TIMESTAMP WITH TIME ZONE,
753 sending_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
755 CREATE INDEX aum_usr ON actor.usr_message (usr);
757 CREATE RULE protect_usr_message_delete AS
758 ON DELETE TO actor.usr_message DO INSTEAD (
759 UPDATE actor.usr_message
761 WHERE OLD.id = actor.usr_message.id
764 CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$
769 IF TG_OP = 'UPDATE' THEN
770 IF OLD.pub = TRUE THEN
775 SELECT INTO sending_ou aw.owning_lib
776 FROM auditor.get_audit_info() agai
777 JOIN actor.workstation aw ON (aw.id = agai.eg_ws);
778 IF sending_ou IS NULL THEN
779 SELECT INTO sending_ou home_ou
781 WHERE id = NEW.creator;
783 INSERT INTO actor.usr_message (usr, title, message, sending_lib)
784 VALUES (NEW.usr, NEW.title, NEW.value, sending_ou);
791 CREATE TRIGGER convert_usr_note_to_message_tgr
792 AFTER INSERT OR UPDATE ON actor.usr_note
793 FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message();
795 -- limited view to ensure that a library user who somehow
796 -- manages to figure out how to access pcrud cannot change
797 -- the text of messages sent them
798 CREATE VIEW actor.usr_message_limited
799 AS SELECT * FROM actor.usr_message;
801 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
803 IF TG_OP = 'UPDATE' THEN
804 UPDATE actor.usr_message
805 SET read_date = NEW.read_date,
806 deleted = NEW.deleted
814 CREATE TRIGGER restrict_usr_message_limited_tgr
815 INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
816 FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
818 CREATE TABLE actor.passwd_type (
819 code TEXT PRIMARY KEY,
820 name TEXT UNIQUE NOT NULL,
821 login BOOLEAN NOT NULL DEFAULT FALSE,
822 regex TEXT, -- pending
823 crypt_algo TEXT, -- e.g. 'bf'
825 -- gen_salt() iter count used with each new salt.
826 -- A non-NULL value for iter_count is our indication the
827 -- password is salted and encrypted via crypt()
828 iter_count INTEGER CHECK (iter_count IS NULL OR iter_count > 0)
831 CREATE TABLE actor.passwd (
832 id SERIAL PRIMARY KEY,
833 usr INTEGER NOT NULL REFERENCES actor.usr(id)
834 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
835 salt TEXT, -- will be NULL for non-crypt'ed passwords
836 passwd TEXT NOT NULL,
837 passwd_type TEXT NOT NULL REFERENCES actor.passwd_type(code)
838 DEFERRABLE INITIALLY DEFERRED,
839 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
840 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
841 CONSTRAINT passwd_type_once_per_user UNIQUE (usr, passwd_type)
844 CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT)
847 type_row actor.passwd_type%ROWTYPE;
849 /* Returns a new salt based on the passwd_type encryption settings.
850 * Returns NULL If the password type is not crypt()'ed.
853 SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;
856 RETURN EXCEPTION 'No such password type: %', pw_type;
859 IF type_row.iter_count IS NULL THEN
860 -- This password type is unsalted. That's OK.
864 RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
870 TODO: when a user changes their password in the application, the
871 app layer has access to the bare password. At that point, we have
872 the opportunity to store the new password without the MD5(MD5())
873 intermediate hashing. Do we care? We would need a way to indicate
874 which passwords have the legacy intermediate hashing and which don't
875 so the app layer would know whether it should perform the intermediate
876 hashing. In either event, with the exception of migrate_passwd(), the
877 DB functions know or care nothing about intermediate hashing. Every
878 password is just a value that may or may not be internally crypt'ed.
881 CREATE OR REPLACE FUNCTION actor.set_passwd(
882 pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL)
883 RETURNS BOOLEAN AS $$
888 /* Sets the password value, creating a new actor.passwd row if needed.
889 * If the password type supports it, the new_pass value is crypt()'ed.
890 * For crypt'ed passwords, the salt comes from one of 3 places in order:
891 * new_salt (if present), existing salt (if present), newly created
895 IF new_salt IS NOT NULL THEN
898 pw_salt := actor.get_salt(pw_usr, pw_type);
900 IF pw_salt IS NULL THEN
901 /* We have no salt for this user + type. Assume they want a
902 * new salt. If this type is unsalted, create_salt() will
904 pw_salt := actor.create_salt(pw_type);
908 IF pw_salt IS NULL THEN
909 pw_text := new_pass; -- unsalted, use as-is.
911 pw_text := CRYPT(new_pass, pw_salt);
915 SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
916 WHERE usr = pw_usr AND passwd_type = pw_type;
919 -- no password row exists for this user + type. Create one.
920 INSERT INTO actor.passwd (usr, passwd_type, salt, passwd)
921 VALUES (pw_usr, pw_type, pw_salt, pw_text);
928 CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT)
932 type_row actor.passwd_type%ROWTYPE;
934 /* Returns the salt for the requested user + type. If the password
935 * type of "main" is requested and no password exists in actor.passwd,
936 * the user's existing password is migrated and the new salt is returned.
937 * Returns NULL if the password type is not crypt'ed (iter_count is NULL).
940 SELECT INTO pw_salt salt FROM actor.passwd
941 WHERE usr = pw_usr AND passwd_type = pw_type;
947 IF pw_type = 'main' THEN
948 -- Main password has not yet been migrated.
949 -- Do it now and return the newly created salt.
950 RETURN actor.migrate_passwd(pw_usr);
953 -- We have no salt to return. actor.create_salt() needed.
958 CREATE OR REPLACE FUNCTION
959 actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$
962 usr_row actor.usr%ROWTYPE;
964 /* Migrates legacy actor.usr.passwd value to actor.passwd with
965 * a password type 'main' and returns the new salt. For backwards
966 * compatibility with existing CHAP-style API's, we perform a
967 * layer of intermediate MD5(MD5()) hashing. This is intermediate
968 * hashing is not required of other passwords.
971 -- Avoid calling get_salt() here, because it may result in a
972 -- migrate_passwd() call, creating a loop.
973 SELECT INTO pw_salt salt FROM actor.passwd
974 WHERE usr = pw_usr AND passwd_type = 'main';
976 -- Only migrate passwords that have not already been migrated.
981 SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
983 pw_salt := actor.create_salt('main');
985 PERFORM actor.set_passwd(
986 pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
988 -- clear the existing password
989 UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
995 CREATE OR REPLACE FUNCTION
996 actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT)
997 RETURNS BOOLEAN AS $$
1001 /* Returns TRUE if the password provided matches the in-db password.
1002 * If the password type is salted, we compare the output of CRYPT().
1003 * NOTE: test_passwd is MD5(salt || MD5(password)) for legacy
1007 SELECT INTO pw_salt salt FROM actor.passwd
1008 WHERE usr = pw_usr AND passwd_type = pw_type;
1015 IF pw_salt IS NULL THEN
1016 -- Password is unsalted, compare the un-CRYPT'ed values.
1018 SELECT TRUE FROM actor.passwd WHERE
1020 passwd_type = pw_type AND
1021 passwd = test_passwd
1026 SELECT TRUE FROM actor.passwd WHERE
1028 passwd_type = pw_type AND
1029 passwd = CRYPT(test_passwd, pw_salt)
1032 $$ STRICT LANGUAGE PLPGSQL;
1034 -- Remove all activity entries by activity type,
1035 -- except the most recent entry per user.
1036 CREATE OR REPLACE FUNCTION
1037 actor.purge_usr_activity_by_type(act_type INTEGER)
1042 FOR cur_usr IN SELECT DISTINCT(usr)
1043 FROM actor.usr_activity WHERE etype = act_type LOOP
1044 DELETE FROM actor.usr_activity WHERE id IN (
1046 FROM actor.usr_activity
1047 WHERE usr = cur_usr AND etype = act_type
1048 ORDER BY event_time DESC OFFSET 1
1052 END $$ LANGUAGE PLPGSQL;