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_email_idx ON actor.usr (evergreen.lowercase(email));
88 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
89 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
90 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
92 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
93 (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
95 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
96 (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
98 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
99 (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
101 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
102 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
104 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
106 NEW.passwd = MD5( NEW.passwd );
111 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
113 IF NEW.passwd <> OLD.passwd THEN
114 NEW.passwd = MD5( NEW.passwd );
120 CREATE OR REPLACE FUNCTION actor.au_updated()
121 RETURNS TRIGGER AS $$
123 NEW.last_update_time := now();
128 CREATE TRIGGER au_update_trig
129 BEFORE INSERT OR UPDATE ON actor.usr
130 FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
132 CREATE TRIGGER actor_crypt_pw_update_trigger
133 BEFORE UPDATE ON actor.usr FOR EACH ROW
134 EXECUTE PROCEDURE actor.crypt_pw_update ();
136 CREATE TRIGGER actor_crypt_pw_insert_trigger
137 BEFORE INSERT ON actor.usr FOR EACH ROW
138 EXECUTE PROCEDURE actor.crypt_pw_insert ();
140 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;
142 CREATE TABLE actor.usr_note (
143 id BIGSERIAL PRIMARY KEY,
144 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
145 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
146 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
147 pub BOOL NOT NULL DEFAULT FALSE,
151 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
152 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
154 CREATE TABLE actor.usr_setting (
155 id BIGSERIAL PRIMARY KEY,
156 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
157 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
159 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
161 COMMENT ON TABLE actor.usr_setting IS $$
164 This table contains any arbitrary settings that a client
165 program would like to save for a user.
168 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
170 CREATE TABLE actor.stat_cat_sip_fields (
171 field CHAR(2) PRIMARY KEY,
173 one_only BOOL NOT NULL DEFAULT FALSE
175 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
176 Actor Statistical Category SIP Fields
178 Contains the list of valid SIP Field identifiers for
179 Statistical Categories.
182 CREATE TABLE actor.stat_cat (
183 id SERIAL PRIMARY KEY,
186 opac_visible BOOL NOT NULL DEFAULT FALSE,
187 usr_summary BOOL NOT NULL DEFAULT FALSE,
188 sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
190 checkout_archive BOOL NOT NULL DEFAULT FALSE,
191 required BOOL NOT NULL DEFAULT FALSE,
192 allow_freetext BOOL NOT NULL DEFAULT TRUE,
193 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
195 COMMENT ON TABLE actor.stat_cat IS $$
196 User Statistical Catagories
198 Local data collected about Users is placed into a Statistical
199 Catagory. Here's where those catagories are defined.
203 CREATE TABLE actor.stat_cat_entry (
204 id SERIAL PRIMARY KEY,
205 stat_cat INT NOT NULL,
208 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
210 COMMENT ON TABLE actor.stat_cat_entry IS $$
211 User Statistical Catagory Entries
213 Local data collected about Users is placed into a Statistical
214 Catagory. Each library can create entries into any of its own
215 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
219 CREATE TABLE actor.stat_cat_entry_usr_map (
220 id BIGSERIAL PRIMARY KEY,
221 stat_cat_entry TEXT NOT NULL,
222 stat_cat INT NOT NULL,
223 target_usr INT NOT NULL,
224 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
226 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
227 Statistical Catagory Entry to User map
229 Records the stat_cat entries for each user.
232 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
234 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
236 sipfield actor.stat_cat_sip_fields%ROWTYPE;
239 IF NEW.sip_field IS NOT NULL THEN
240 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
241 IF sipfield.one_only THEN
242 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
243 IF use_count > 0 THEN
244 RAISE EXCEPTION 'Sip field cannot be used twice';
250 $func$ LANGUAGE PLPGSQL;
252 CREATE TRIGGER actor_stat_cat_sip_update_trigger
253 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
254 EXECUTE PROCEDURE actor.stat_cat_check();
256 CREATE TABLE actor.card (
257 id SERIAL PRIMARY KEY,
258 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
259 barcode TEXT NOT NULL UNIQUE,
260 active BOOL NOT NULL DEFAULT TRUE
262 COMMENT ON TABLE actor.card IS $$
265 Each User has one or more library cards. The current "main"
266 card is linked to here from the actor.usr table, and it is up
267 to the consortium policy whether more than one card can be
268 active for any one user at a given time.
271 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
272 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
274 CREATE TABLE actor.org_unit_type (
275 id SERIAL PRIMARY KEY,
277 opac_label TEXT NOT NULL,
279 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
280 can_have_vols BOOL NOT NULL DEFAULT TRUE,
281 can_have_users BOOL NOT NULL DEFAULT TRUE
283 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
285 CREATE TABLE actor.org_unit (
286 id SERIAL PRIMARY KEY,
287 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
288 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
293 shortname TEXT NOT NULL UNIQUE,
294 name TEXT NOT NULL UNIQUE,
297 opac_visible BOOL NOT NULL DEFAULT TRUE,
298 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
300 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
301 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
302 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
303 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
304 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
305 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
307 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
309 current_aou actor.org_unit%ROWTYPE;
315 seen_ous := ARRAY[NEW.id];
317 IF (TG_OP = 'UPDATE') THEN
318 IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
319 RETURN NEW; -- Doing an UPDATE with no change, just return it
324 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
325 RETURN NEW; -- No loop. Carry on.
327 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
328 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
331 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
332 seen_ous := seen_ous || current_aou.id;
333 depth_count := depth_count + 1;
334 IF depth_count = 100 THEN
335 RAISE 'OU CHECK TOO DEEP';
343 CREATE TRIGGER actor_org_unit_parent_protect_trigger
344 BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
345 EXECUTE PROCEDURE actor.org_unit_parent_protect ();
347 CREATE TABLE actor.org_lasso (
348 id SERIAL PRIMARY KEY,
352 CREATE TABLE actor.org_lasso_map (
353 id SERIAL PRIMARY KEY,
354 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
355 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
357 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
358 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
360 CREATE TABLE actor.org_unit_proximity (
361 id BIGSERIAL PRIMARY KEY,
366 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
368 CREATE TABLE actor.stat_cat_entry_default (
369 id SERIAL PRIMARY KEY,
370 stat_cat_entry INT NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
371 stat_cat INT NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
372 owner INT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
373 CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
375 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
376 User Statistical Category Default Entry
378 A library may choose one of the stat_cat entries to be the
383 CREATE TABLE actor.org_unit_proximity_adjustment (
384 id SERIAL PRIMARY KEY,
385 item_circ_lib INT REFERENCES actor.org_unit (id),
386 item_owning_lib INT REFERENCES actor.org_unit (id),
387 copy_location INT, -- REFERENCES asset.copy_location (id),
388 hold_pickup_lib INT REFERENCES actor.org_unit (id),
389 hold_request_lib INT REFERENCES actor.org_unit (id),
390 pos INT NOT NULL DEFAULT 0,
391 absolute_adjustment BOOL NOT NULL DEFAULT FALSE,
392 prox_adjustment NUMERIC,
393 circ_mod TEXT, -- REFERENCES config.circ_modifier (code),
394 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)
396 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
397 COALESCE(item_circ_lib, -1),
398 COALESCE(item_owning_lib, -1),
399 COALESCE(copy_location, -1),
400 COALESCE(hold_pickup_lib, -1),
401 COALESCE(hold_request_lib, -1),
402 COALESCE(circ_mod, ''),
405 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
406 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
407 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
408 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
409 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
410 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
412 CREATE TABLE actor.hours_of_operation (
413 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
414 dow_0_open TIME NOT NULL DEFAULT '09:00',
415 dow_0_close TIME NOT NULL DEFAULT '17:00',
416 dow_1_open TIME NOT NULL DEFAULT '09:00',
417 dow_1_close TIME NOT NULL DEFAULT '17:00',
418 dow_2_open TIME NOT NULL DEFAULT '09:00',
419 dow_2_close TIME NOT NULL DEFAULT '17:00',
420 dow_3_open TIME NOT NULL DEFAULT '09:00',
421 dow_3_close TIME NOT NULL DEFAULT '17:00',
422 dow_4_open TIME NOT NULL DEFAULT '09:00',
423 dow_4_close TIME NOT NULL DEFAULT '17:00',
424 dow_5_open TIME NOT NULL DEFAULT '09:00',
425 dow_5_close TIME NOT NULL DEFAULT '17:00',
426 dow_6_open TIME NOT NULL DEFAULT '09:00',
427 dow_6_close TIME NOT NULL DEFAULT '17:00'
429 COMMENT ON TABLE actor.hours_of_operation IS $$
430 When does this org_unit usually open and close? (Variations
431 are expressed in the actor.org_unit_closed table.)
433 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
434 When does this org_unit open on Monday?
436 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
437 When does this org_unit close on Monday?
439 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
440 When does this org_unit open on Tuesday?
442 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
443 When does this org_unit close on Tuesday?
445 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
446 When does this org_unit open on Wednesday?
448 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
449 When does this org_unit close on Wednesday?
451 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
452 When does this org_unit open on Thursday?
454 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
455 When does this org_unit close on Thursday?
457 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
458 When does this org_unit open on Friday?
460 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
461 When does this org_unit close on Friday?
463 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
464 When does this org_unit open on Saturday?
466 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
467 When does this org_unit close on Saturday?
469 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
470 When does this org_unit open on Sunday?
472 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
473 When does this org_unit close on Sunday?
476 CREATE TABLE actor.org_unit_closed (
477 id SERIAL PRIMARY KEY,
478 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
479 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
480 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
484 -- Workstation registration...
485 CREATE TABLE actor.workstation (
486 id SERIAL PRIMARY KEY,
487 name TEXT NOT NULL UNIQUE,
488 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
491 CREATE TABLE actor.usr_org_unit_opt_in (
492 id SERIAL PRIMARY KEY,
493 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
494 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
495 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
496 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
497 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
498 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
500 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
502 CREATE TABLE actor.org_unit_setting (
503 id BIGSERIAL PRIMARY KEY,
504 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
505 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
507 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
508 CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
510 COMMENT ON TABLE actor.org_unit_setting IS $$
513 This table contains any arbitrary settings that a client
514 program would like to save for an org unit.
517 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
519 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
520 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
524 -- Check for which setting is being updated, and log it.
525 SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
527 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
531 $ous_change_log$ LANGUAGE plpgsql;
533 CREATE TRIGGER log_ous_change
534 BEFORE INSERT OR UPDATE ON actor.org_unit_setting
535 FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
537 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
541 -- Check for which setting is being updated, and log it.
542 SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
544 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
548 $ous_delete_log$ LANGUAGE plpgsql;
550 CREATE TRIGGER log_ous_del
551 BEFORE DELETE ON actor.org_unit_setting
552 FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
557 CREATE TABLE actor.usr_address (
558 id SERIAL PRIMARY KEY,
559 valid BOOL NOT NULL DEFAULT TRUE,
560 within_city_limits BOOL NOT NULL DEFAULT TRUE,
561 address_type TEXT NOT NULL DEFAULT 'MAILING',
562 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
563 street1 TEXT NOT NULL,
568 country TEXT NOT NULL,
569 post_code TEXT NOT NULL,
570 pending BOOL NOT NULL DEFAULT FALSE,
571 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
574 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
576 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
577 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
579 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
580 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
581 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
583 CREATE TABLE actor.usr_password_reset (
584 id SERIAL PRIMARY KEY,
586 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
587 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
588 has_been_reset BOOL NOT NULL DEFAULT false
590 COMMENT ON TABLE actor.usr_password_reset IS $$
591 Self-serve password reset requests
593 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
594 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
595 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
596 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
598 CREATE TABLE actor.org_address (
599 id SERIAL PRIMARY KEY,
600 valid BOOL NOT NULL DEFAULT TRUE,
601 address_type TEXT NOT NULL DEFAULT 'MAILING',
602 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
603 street1 TEXT NOT NULL,
608 country TEXT NOT NULL,
609 post_code TEXT NOT NULL,
613 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
615 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
616 SELECT SUBSTRING( $1, 1, 5);
619 CREATE TABLE actor.usr_standing_penalty (
620 id SERIAL PRIMARY KEY,
621 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
622 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
623 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
624 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
625 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
626 stop_date TIMESTAMP WITH TIME ZONE,
629 COMMENT ON TABLE actor.usr_standing_penalty IS $$
630 User standing penalties
633 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
634 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
637 CREATE TABLE actor.usr_saved_search (
638 id SERIAL PRIMARY KEY,
639 owner INT NOT NULL REFERENCES actor.usr (id)
641 DEFERRABLE INITIALLY DEFERRED,
643 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
644 query_text TEXT NOT NULL,
645 query_type TEXT NOT NULL
646 CONSTRAINT valid_query_text CHECK (
647 query_type IN ( 'URL' )) DEFAULT 'URL',
648 -- we may add other types someday
650 CONSTRAINT valid_target CHECK (
651 target IN ( 'record', 'metarecord', 'callnumber' )),
652 CONSTRAINT name_once_per_user UNIQUE (owner, name)
655 CREATE TABLE actor.address_alert (
656 id SERIAL PRIMARY KEY,
657 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
658 active BOOL NOT NULL DEFAULT TRUE,
659 match_all BOOL NOT NULL DEFAULT TRUE,
660 alert_message TEXT NOT NULL,
668 mailing_address BOOL NOT NULL DEFAULT FALSE,
669 billing_address BOOL NOT NULL DEFAULT FALSE
672 CREATE TABLE actor.usr_activity (
673 id BIGSERIAL PRIMARY KEY,
674 usr INT REFERENCES actor.usr (id) ON DELETE SET NULL,
675 etype INT NOT NULL REFERENCES config.usr_activity_type (id),
676 event_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
678 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
680 CREATE TABLE actor.toolbar (
681 id BIGSERIAL PRIMARY KEY,
682 ws INT REFERENCES actor.workstation (id) ON DELETE CASCADE,
683 org INT REFERENCES actor.org_unit (id) ON DELETE CASCADE,
684 usr INT REFERENCES actor.usr (id) ON DELETE CASCADE,
686 layout TEXT NOT NULL,
687 CONSTRAINT only_one_type CHECK (
688 (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
689 (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
690 (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
692 CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
694 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
695 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
696 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
698 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
699 CREATE TABLE actor.org_unit_custom_tree (
700 id SERIAL PRIMARY KEY,
701 active BOOLEAN DEFAULT FALSE,
702 purpose actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
705 CREATE TABLE actor.org_unit_custom_tree_node (
706 id SERIAL PRIMARY KEY,
707 tree INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
708 org_unit INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
709 parent_node INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
710 sibling_order INTEGER NOT NULL DEFAULT 0,
711 CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
714 CREATE TABLE actor.search_query (
715 id SERIAL PRIMARY KEY,
716 label TEXT NOT NULL, -- i18n
717 query_text TEXT NOT NULL -- QP text
720 CREATE TABLE actor.search_filter_group (
721 id SERIAL PRIMARY KEY,
722 owner INT NOT NULL REFERENCES actor.org_unit (id)
723 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
724 code TEXT NOT NULL, -- for CGI, etc.
725 label TEXT NOT NULL, -- i18n
726 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
727 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
728 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
731 CREATE TABLE actor.search_filter_group_entry (
732 id SERIAL PRIMARY KEY,
733 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
734 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
735 pos INT NOT NULL DEFAULT 0,
736 query INT NOT NULL REFERENCES actor.search_query(id)
737 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
738 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)