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,
52 dob TIMESTAMP WITH TIME ZONE,
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));
86 CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname));
87 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
89 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
90 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
91 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
93 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
94 (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
96 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
97 (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
99 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
100 (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
102 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
103 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
105 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
107 NEW.passwd = MD5( NEW.passwd );
112 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
114 IF NEW.passwd <> OLD.passwd THEN
115 NEW.passwd = MD5( NEW.passwd );
121 CREATE OR REPLACE FUNCTION actor.au_updated()
122 RETURNS TRIGGER AS $$
124 NEW.last_update_time := now();
129 CREATE TRIGGER au_update_trig
130 BEFORE INSERT OR UPDATE ON actor.usr
131 FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
133 CREATE TRIGGER actor_crypt_pw_update_trigger
134 BEFORE UPDATE ON actor.usr FOR EACH ROW
135 EXECUTE PROCEDURE actor.crypt_pw_update ();
137 CREATE TRIGGER actor_crypt_pw_insert_trigger
138 BEFORE INSERT ON actor.usr FOR EACH ROW
139 EXECUTE PROCEDURE actor.crypt_pw_insert ();
141 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;
143 CREATE TABLE actor.usr_note (
144 id BIGSERIAL PRIMARY KEY,
145 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
146 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
147 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
148 pub BOOL NOT NULL DEFAULT FALSE,
152 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
153 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
155 CREATE TABLE actor.usr_message (
156 id SERIAL PRIMARY KEY,
157 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
159 message TEXT NOT NULL,
160 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
161 deleted BOOL NOT NULL DEFAULT FALSE,
162 read_date TIMESTAMP WITH TIME ZONE,
163 sending_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
165 CREATE INDEX aum_usr ON actor.usr_message (usr);
167 CREATE RULE protect_usr_message_delete AS
168 ON DELETE TO actor.usr_message DO INSTEAD (
169 UPDATE actor.usr_message
171 WHERE OLD.id = actor.usr_message.id
174 CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$
177 IF TG_OP = 'UPDATE' THEN
178 IF OLD.pub = TRUE THEN
183 INSERT INTO actor.usr_message (usr, title, message, sending_lib)
184 VALUES (NEW.usr, NEW.title, NEW.value, (SELECT home_ou FROM actor.usr WHERE id = NEW.creator));
191 CREATE TRIGGER actor.convert_usr_note_to_message_tgr
192 AFTER INSERT OR UPDATE ON actor.usr_note
193 FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message();
195 -- limited view to ensure that a library user who somehow
196 -- manages to figure out how to access pcrud cannot change
197 -- the text of messages sent them
198 CREATE VIEW actor.usr_message_limited
199 AS SELECT * FROM actor.usr_message;
201 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
203 IF TG_OP = 'UPDATE' THEN
204 UPDATE actor.usr_message
205 SET read_date = NEW.read_date,
206 deleted = NEW.deleted
214 CREATE TRIGGER restrict_usr_message_limited_tgr
215 INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
216 FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
218 CREATE TABLE actor.usr_setting (
219 id BIGSERIAL PRIMARY KEY,
220 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
221 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
223 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
225 COMMENT ON TABLE actor.usr_setting IS $$
228 This table contains any arbitrary settings that a client
229 program would like to save for a user.
232 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
234 CREATE TABLE actor.stat_cat_sip_fields (
235 field CHAR(2) PRIMARY KEY,
237 one_only BOOL NOT NULL DEFAULT FALSE
239 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
240 Actor Statistical Category SIP Fields
242 Contains the list of valid SIP Field identifiers for
243 Statistical Categories.
246 CREATE TABLE actor.stat_cat (
247 id SERIAL PRIMARY KEY,
250 opac_visible BOOL NOT NULL DEFAULT FALSE,
251 usr_summary BOOL NOT NULL DEFAULT FALSE,
252 sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
254 checkout_archive BOOL NOT NULL DEFAULT FALSE,
255 required BOOL NOT NULL DEFAULT FALSE,
256 allow_freetext BOOL NOT NULL DEFAULT TRUE,
257 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
259 COMMENT ON TABLE actor.stat_cat IS $$
260 User Statistical Catagories
262 Local data collected about Users is placed into a Statistical
263 Catagory. Here's where those catagories are defined.
267 CREATE TABLE actor.stat_cat_entry (
268 id SERIAL PRIMARY KEY,
269 stat_cat INT NOT NULL,
272 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
274 COMMENT ON TABLE actor.stat_cat_entry IS $$
275 User Statistical Catagory Entries
277 Local data collected about Users is placed into a Statistical
278 Catagory. Each library can create entries into any of its own
279 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
283 CREATE TABLE actor.stat_cat_entry_usr_map (
284 id BIGSERIAL PRIMARY KEY,
285 stat_cat_entry TEXT NOT NULL,
286 stat_cat INT NOT NULL,
287 target_usr INT NOT NULL,
288 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
290 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
291 Statistical Catagory Entry to User map
293 Records the stat_cat entries for each user.
296 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
298 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
300 sipfield actor.stat_cat_sip_fields%ROWTYPE;
303 IF NEW.sip_field IS NOT NULL THEN
304 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
305 IF sipfield.one_only THEN
306 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
307 IF use_count > 0 THEN
308 RAISE EXCEPTION 'Sip field cannot be used twice';
314 $func$ LANGUAGE PLPGSQL;
316 CREATE TRIGGER actor_stat_cat_sip_update_trigger
317 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
318 EXECUTE PROCEDURE actor.stat_cat_check();
320 CREATE TABLE actor.card (
321 id SERIAL PRIMARY KEY,
322 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
323 barcode TEXT NOT NULL UNIQUE,
324 active BOOL NOT NULL DEFAULT TRUE
326 COMMENT ON TABLE actor.card IS $$
329 Each User has one or more library cards. The current "main"
330 card is linked to here from the actor.usr table, and it is up
331 to the consortium policy whether more than one card can be
332 active for any one user at a given time.
335 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
336 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
338 CREATE TABLE actor.org_unit_type (
339 id SERIAL PRIMARY KEY,
341 opac_label TEXT NOT NULL,
343 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
344 can_have_vols BOOL NOT NULL DEFAULT TRUE,
345 can_have_users BOOL NOT NULL DEFAULT TRUE
347 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
349 CREATE TABLE actor.org_unit (
350 id SERIAL PRIMARY KEY,
351 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
352 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
357 shortname TEXT NOT NULL UNIQUE,
358 name TEXT NOT NULL UNIQUE,
361 opac_visible BOOL NOT NULL DEFAULT TRUE,
362 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
364 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
365 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
366 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
367 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
368 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
369 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
371 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
373 current_aou actor.org_unit%ROWTYPE;
379 seen_ous := ARRAY[NEW.id];
381 IF (TG_OP = 'UPDATE') THEN
382 IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
383 RETURN NEW; -- Doing an UPDATE with no change, just return it
388 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
389 RETURN NEW; -- No loop. Carry on.
391 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
392 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
395 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
396 seen_ous := seen_ous || current_aou.id;
397 depth_count := depth_count + 1;
398 IF depth_count = 100 THEN
399 RAISE 'OU CHECK TOO DEEP';
407 CREATE TRIGGER actor_org_unit_parent_protect_trigger
408 BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
409 EXECUTE PROCEDURE actor.org_unit_parent_protect ();
411 CREATE TABLE actor.org_lasso (
412 id SERIAL PRIMARY KEY,
416 CREATE TABLE actor.org_lasso_map (
417 id SERIAL PRIMARY KEY,
418 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
419 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
421 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
422 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
424 CREATE TABLE actor.org_unit_proximity (
425 id BIGSERIAL PRIMARY KEY,
430 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
432 CREATE TABLE actor.stat_cat_entry_default (
433 id SERIAL PRIMARY KEY,
434 stat_cat_entry INT NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
435 stat_cat INT NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
436 owner INT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
437 CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
439 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
440 User Statistical Category Default Entry
442 A library may choose one of the stat_cat entries to be the
447 CREATE TABLE actor.org_unit_proximity_adjustment (
448 id SERIAL PRIMARY KEY,
449 item_circ_lib INT REFERENCES actor.org_unit (id),
450 item_owning_lib INT REFERENCES actor.org_unit (id),
451 copy_location INT, -- REFERENCES asset.copy_location (id),
452 hold_pickup_lib INT REFERENCES actor.org_unit (id),
453 hold_request_lib INT REFERENCES actor.org_unit (id),
454 pos INT NOT NULL DEFAULT 0,
455 absolute_adjustment BOOL NOT NULL DEFAULT FALSE,
456 prox_adjustment NUMERIC,
457 circ_mod TEXT, -- REFERENCES config.circ_modifier (code),
458 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)
460 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
461 COALESCE(item_circ_lib, -1),
462 COALESCE(item_owning_lib, -1),
463 COALESCE(copy_location, -1),
464 COALESCE(hold_pickup_lib, -1),
465 COALESCE(hold_request_lib, -1),
466 COALESCE(circ_mod, ''),
469 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
470 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
471 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
472 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
473 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
474 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
476 CREATE TABLE actor.hours_of_operation (
477 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
478 dow_0_open TIME NOT NULL DEFAULT '09:00',
479 dow_0_close TIME NOT NULL DEFAULT '17:00',
480 dow_1_open TIME NOT NULL DEFAULT '09:00',
481 dow_1_close TIME NOT NULL DEFAULT '17:00',
482 dow_2_open TIME NOT NULL DEFAULT '09:00',
483 dow_2_close TIME NOT NULL DEFAULT '17:00',
484 dow_3_open TIME NOT NULL DEFAULT '09:00',
485 dow_3_close TIME NOT NULL DEFAULT '17:00',
486 dow_4_open TIME NOT NULL DEFAULT '09:00',
487 dow_4_close TIME NOT NULL DEFAULT '17:00',
488 dow_5_open TIME NOT NULL DEFAULT '09:00',
489 dow_5_close TIME NOT NULL DEFAULT '17:00',
490 dow_6_open TIME NOT NULL DEFAULT '09:00',
491 dow_6_close TIME NOT NULL DEFAULT '17:00'
493 COMMENT ON TABLE actor.hours_of_operation IS $$
494 When does this org_unit usually open and close? (Variations
495 are expressed in the actor.org_unit_closed table.)
497 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
498 When does this org_unit open on Monday?
500 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
501 When does this org_unit close on Monday?
503 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
504 When does this org_unit open on Tuesday?
506 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
507 When does this org_unit close on Tuesday?
509 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
510 When does this org_unit open on Wednesday?
512 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
513 When does this org_unit close on Wednesday?
515 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
516 When does this org_unit open on Thursday?
518 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
519 When does this org_unit close on Thursday?
521 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
522 When does this org_unit open on Friday?
524 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
525 When does this org_unit close on Friday?
527 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
528 When does this org_unit open on Saturday?
530 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
531 When does this org_unit close on Saturday?
533 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
534 When does this org_unit open on Sunday?
536 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
537 When does this org_unit close on Sunday?
540 CREATE TABLE actor.org_unit_closed (
541 id SERIAL PRIMARY KEY,
542 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
543 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
544 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
548 -- Workstation registration...
549 CREATE TABLE actor.workstation (
550 id SERIAL PRIMARY KEY,
551 name TEXT NOT NULL UNIQUE,
552 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
555 CREATE TABLE actor.usr_org_unit_opt_in (
556 id SERIAL PRIMARY KEY,
557 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
558 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
559 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
560 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
561 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
562 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
564 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
566 CREATE TABLE actor.org_unit_setting (
567 id BIGSERIAL PRIMARY KEY,
568 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
569 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
571 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
572 CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
574 COMMENT ON TABLE actor.org_unit_setting IS $$
577 This table contains any arbitrary settings that a client
578 program would like to save for an org unit.
581 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
583 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
584 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
588 -- Check for which setting is being updated, and log it.
589 SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
591 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
595 $ous_change_log$ LANGUAGE plpgsql;
597 CREATE TRIGGER log_ous_change
598 BEFORE INSERT OR UPDATE ON actor.org_unit_setting
599 FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
601 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
605 -- Check for which setting is being updated, and log it.
606 SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
608 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
612 $ous_delete_log$ LANGUAGE plpgsql;
614 CREATE TRIGGER log_ous_del
615 BEFORE DELETE ON actor.org_unit_setting
616 FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
621 CREATE TABLE actor.usr_address (
622 id SERIAL PRIMARY KEY,
623 valid BOOL NOT NULL DEFAULT TRUE,
624 within_city_limits BOOL NOT NULL DEFAULT TRUE,
625 address_type TEXT NOT NULL DEFAULT 'MAILING',
626 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
627 street1 TEXT NOT NULL,
632 country TEXT NOT NULL,
633 post_code TEXT NOT NULL,
634 pending BOOL NOT NULL DEFAULT FALSE,
635 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
638 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
640 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
641 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
643 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
644 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
645 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
647 CREATE TABLE actor.usr_password_reset (
648 id SERIAL PRIMARY KEY,
650 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
651 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
652 has_been_reset BOOL NOT NULL DEFAULT false
654 COMMENT ON TABLE actor.usr_password_reset IS $$
655 Self-serve password reset requests
657 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
658 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
659 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
660 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
662 CREATE TABLE actor.org_address (
663 id SERIAL PRIMARY KEY,
664 valid BOOL NOT NULL DEFAULT TRUE,
665 address_type TEXT NOT NULL DEFAULT 'MAILING',
666 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
667 street1 TEXT NOT NULL,
672 country TEXT NOT NULL,
673 post_code TEXT NOT NULL,
677 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
679 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
680 SELECT SUBSTRING( $1, 1, 5);
683 CREATE TABLE actor.usr_standing_penalty (
684 id SERIAL PRIMARY KEY,
685 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
686 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
687 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
688 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
689 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
690 stop_date TIMESTAMP WITH TIME ZONE,
693 COMMENT ON TABLE actor.usr_standing_penalty IS $$
694 User standing penalties
697 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
698 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
701 CREATE TABLE actor.usr_saved_search (
702 id SERIAL PRIMARY KEY,
703 owner INT NOT NULL REFERENCES actor.usr (id)
705 DEFERRABLE INITIALLY DEFERRED,
707 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
708 query_text TEXT NOT NULL,
709 query_type TEXT NOT NULL
710 CONSTRAINT valid_query_text CHECK (
711 query_type IN ( 'URL' )) DEFAULT 'URL',
712 -- we may add other types someday
714 CONSTRAINT valid_target CHECK (
715 target IN ( 'record', 'metarecord', 'callnumber' )),
716 CONSTRAINT name_once_per_user UNIQUE (owner, name)
719 CREATE TABLE actor.address_alert (
720 id SERIAL PRIMARY KEY,
721 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
722 active BOOL NOT NULL DEFAULT TRUE,
723 match_all BOOL NOT NULL DEFAULT TRUE,
724 alert_message TEXT NOT NULL,
732 mailing_address BOOL NOT NULL DEFAULT FALSE,
733 billing_address BOOL NOT NULL DEFAULT FALSE
736 CREATE TABLE actor.usr_activity (
737 id BIGSERIAL PRIMARY KEY,
738 usr INT REFERENCES actor.usr (id) ON DELETE SET NULL,
739 etype INT NOT NULL REFERENCES config.usr_activity_type (id),
740 event_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
742 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
744 CREATE TABLE actor.toolbar (
745 id BIGSERIAL PRIMARY KEY,
746 ws INT REFERENCES actor.workstation (id) ON DELETE CASCADE,
747 org INT REFERENCES actor.org_unit (id) ON DELETE CASCADE,
748 usr INT REFERENCES actor.usr (id) ON DELETE CASCADE,
750 layout TEXT NOT NULL,
751 CONSTRAINT only_one_type CHECK (
752 (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
753 (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
754 (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
756 CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
758 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
759 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
760 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
762 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
763 CREATE TABLE actor.org_unit_custom_tree (
764 id SERIAL PRIMARY KEY,
765 active BOOLEAN DEFAULT FALSE,
766 purpose actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
769 CREATE TABLE actor.org_unit_custom_tree_node (
770 id SERIAL PRIMARY KEY,
771 tree INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
772 org_unit INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
773 parent_node INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
774 sibling_order INTEGER NOT NULL DEFAULT 0,
775 CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
778 CREATE TABLE actor.search_query (
779 id SERIAL PRIMARY KEY,
780 label TEXT NOT NULL, -- i18n
781 query_text TEXT NOT NULL -- QP text
784 CREATE TABLE actor.search_filter_group (
785 id SERIAL PRIMARY KEY,
786 owner INT NOT NULL REFERENCES actor.org_unit (id)
787 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
788 code TEXT NOT NULL, -- for CGI, etc.
789 label TEXT NOT NULL, -- i18n
790 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
791 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
792 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
795 CREATE TABLE actor.search_filter_group_entry (
796 id SERIAL PRIMARY KEY,
797 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
798 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
799 pos INT NOT NULL DEFAULT 0,
800 query INT NOT NULL REFERENCES actor.search_query(id)
801 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
802 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)