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_setting (
156 id BIGSERIAL PRIMARY KEY,
157 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
158 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
160 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
162 COMMENT ON TABLE actor.usr_setting IS $$
165 This table contains any arbitrary settings that a client
166 program would like to save for a user.
169 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
171 CREATE TABLE actor.stat_cat_sip_fields (
172 field CHAR(2) PRIMARY KEY,
174 one_only BOOL NOT NULL DEFAULT FALSE
176 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
177 Actor Statistical Category SIP Fields
179 Contains the list of valid SIP Field identifiers for
180 Statistical Categories.
183 CREATE TABLE actor.stat_cat (
184 id SERIAL PRIMARY KEY,
187 opac_visible BOOL NOT NULL DEFAULT FALSE,
188 usr_summary BOOL NOT NULL DEFAULT FALSE,
189 sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
191 checkout_archive BOOL NOT NULL DEFAULT FALSE,
192 required BOOL NOT NULL DEFAULT FALSE,
193 allow_freetext BOOL NOT NULL DEFAULT TRUE,
194 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
196 COMMENT ON TABLE actor.stat_cat IS $$
197 User Statistical Catagories
199 Local data collected about Users is placed into a Statistical
200 Catagory. Here's where those catagories are defined.
204 CREATE TABLE actor.stat_cat_entry (
205 id SERIAL PRIMARY KEY,
206 stat_cat INT NOT NULL,
209 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
211 COMMENT ON TABLE actor.stat_cat_entry IS $$
212 User Statistical Catagory Entries
214 Local data collected about Users is placed into a Statistical
215 Catagory. Each library can create entries into any of its own
216 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
220 CREATE TABLE actor.stat_cat_entry_usr_map (
221 id BIGSERIAL PRIMARY KEY,
222 stat_cat_entry TEXT NOT NULL,
223 stat_cat INT NOT NULL,
224 target_usr INT NOT NULL,
225 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
227 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
228 Statistical Catagory Entry to User map
230 Records the stat_cat entries for each user.
233 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
235 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
237 sipfield actor.stat_cat_sip_fields%ROWTYPE;
240 IF NEW.sip_field IS NOT NULL THEN
241 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
242 IF sipfield.one_only THEN
243 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
244 IF use_count > 0 THEN
245 RAISE EXCEPTION 'Sip field cannot be used twice';
251 $func$ LANGUAGE PLPGSQL;
253 CREATE TRIGGER actor_stat_cat_sip_update_trigger
254 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
255 EXECUTE PROCEDURE actor.stat_cat_check();
257 CREATE TABLE actor.card (
258 id SERIAL PRIMARY KEY,
259 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
260 barcode TEXT NOT NULL UNIQUE,
261 active BOOL NOT NULL DEFAULT TRUE
263 COMMENT ON TABLE actor.card IS $$
266 Each User has one or more library cards. The current "main"
267 card is linked to here from the actor.usr table, and it is up
268 to the consortium policy whether more than one card can be
269 active for any one user at a given time.
272 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
273 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
275 CREATE TABLE actor.org_unit_type (
276 id SERIAL PRIMARY KEY,
278 opac_label TEXT NOT NULL,
280 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
281 can_have_vols BOOL NOT NULL DEFAULT TRUE,
282 can_have_users BOOL NOT NULL DEFAULT TRUE
284 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
286 CREATE TABLE actor.org_unit (
287 id SERIAL PRIMARY KEY,
288 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
289 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
294 shortname TEXT NOT NULL UNIQUE,
295 name TEXT NOT NULL UNIQUE,
298 opac_visible BOOL NOT NULL DEFAULT TRUE,
299 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
301 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
302 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
303 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
304 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
305 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
306 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
308 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
310 current_aou actor.org_unit%ROWTYPE;
316 seen_ous := ARRAY[NEW.id];
318 IF (TG_OP = 'UPDATE') THEN
319 IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
320 RETURN NEW; -- Doing an UPDATE with no change, just return it
325 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
326 RETURN NEW; -- No loop. Carry on.
328 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
329 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
332 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
333 seen_ous := seen_ous || current_aou.id;
334 depth_count := depth_count + 1;
335 IF depth_count = 100 THEN
336 RAISE 'OU CHECK TOO DEEP';
344 CREATE TRIGGER actor_org_unit_parent_protect_trigger
345 BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
346 EXECUTE PROCEDURE actor.org_unit_parent_protect ();
348 CREATE TABLE actor.org_lasso (
349 id SERIAL PRIMARY KEY,
353 CREATE TABLE actor.org_lasso_map (
354 id SERIAL PRIMARY KEY,
355 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
356 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
358 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
359 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
361 CREATE TABLE actor.org_unit_proximity (
362 id BIGSERIAL PRIMARY KEY,
367 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
369 CREATE TABLE actor.stat_cat_entry_default (
370 id SERIAL PRIMARY KEY,
371 stat_cat_entry INT NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
372 stat_cat INT NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
373 owner INT NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
374 CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
376 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
377 User Statistical Category Default Entry
379 A library may choose one of the stat_cat entries to be the
384 CREATE TABLE actor.org_unit_proximity_adjustment (
385 id SERIAL PRIMARY KEY,
386 item_circ_lib INT REFERENCES actor.org_unit (id),
387 item_owning_lib INT REFERENCES actor.org_unit (id),
388 copy_location INT, -- REFERENCES asset.copy_location (id),
389 hold_pickup_lib INT REFERENCES actor.org_unit (id),
390 hold_request_lib INT REFERENCES actor.org_unit (id),
391 pos INT NOT NULL DEFAULT 0,
392 absolute_adjustment BOOL NOT NULL DEFAULT FALSE,
393 prox_adjustment NUMERIC,
394 circ_mod TEXT, -- REFERENCES config.circ_modifier (code),
395 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)
397 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
398 COALESCE(item_circ_lib, -1),
399 COALESCE(item_owning_lib, -1),
400 COALESCE(copy_location, -1),
401 COALESCE(hold_pickup_lib, -1),
402 COALESCE(hold_request_lib, -1),
403 COALESCE(circ_mod, ''),
406 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
407 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
408 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
409 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
410 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
411 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
413 CREATE TABLE actor.hours_of_operation (
414 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
415 dow_0_open TIME NOT NULL DEFAULT '09:00',
416 dow_0_close TIME NOT NULL DEFAULT '17:00',
417 dow_1_open TIME NOT NULL DEFAULT '09:00',
418 dow_1_close TIME NOT NULL DEFAULT '17:00',
419 dow_2_open TIME NOT NULL DEFAULT '09:00',
420 dow_2_close TIME NOT NULL DEFAULT '17:00',
421 dow_3_open TIME NOT NULL DEFAULT '09:00',
422 dow_3_close TIME NOT NULL DEFAULT '17:00',
423 dow_4_open TIME NOT NULL DEFAULT '09:00',
424 dow_4_close TIME NOT NULL DEFAULT '17:00',
425 dow_5_open TIME NOT NULL DEFAULT '09:00',
426 dow_5_close TIME NOT NULL DEFAULT '17:00',
427 dow_6_open TIME NOT NULL DEFAULT '09:00',
428 dow_6_close TIME NOT NULL DEFAULT '17:00'
430 COMMENT ON TABLE actor.hours_of_operation IS $$
431 When does this org_unit usually open and close? (Variations
432 are expressed in the actor.org_unit_closed table.)
434 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
435 When does this org_unit open on Monday?
437 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
438 When does this org_unit close on Monday?
440 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
441 When does this org_unit open on Tuesday?
443 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
444 When does this org_unit close on Tuesday?
446 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
447 When does this org_unit open on Wednesday?
449 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
450 When does this org_unit close on Wednesday?
452 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
453 When does this org_unit open on Thursday?
455 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
456 When does this org_unit close on Thursday?
458 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
459 When does this org_unit open on Friday?
461 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
462 When does this org_unit close on Friday?
464 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
465 When does this org_unit open on Saturday?
467 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
468 When does this org_unit close on Saturday?
470 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
471 When does this org_unit open on Sunday?
473 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
474 When does this org_unit close on Sunday?
477 CREATE TABLE actor.org_unit_closed (
478 id SERIAL PRIMARY KEY,
479 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
480 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
481 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
485 -- Workstation registration...
486 CREATE TABLE actor.workstation (
487 id SERIAL PRIMARY KEY,
488 name TEXT NOT NULL UNIQUE,
489 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
492 CREATE TABLE actor.usr_org_unit_opt_in (
493 id SERIAL PRIMARY KEY,
494 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
495 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
496 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
497 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
498 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
499 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
501 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
503 CREATE TABLE actor.org_unit_setting (
504 id BIGSERIAL PRIMARY KEY,
505 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
506 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
508 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
509 CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
511 COMMENT ON TABLE actor.org_unit_setting IS $$
514 This table contains any arbitrary settings that a client
515 program would like to save for an org unit.
518 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
520 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
521 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
525 -- Check for which setting is being updated, and log it.
526 SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
528 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
532 $ous_change_log$ LANGUAGE plpgsql;
534 CREATE TRIGGER log_ous_change
535 BEFORE INSERT OR UPDATE ON actor.org_unit_setting
536 FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
538 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
542 -- Check for which setting is being updated, and log it.
543 SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
545 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
549 $ous_delete_log$ LANGUAGE plpgsql;
551 CREATE TRIGGER log_ous_del
552 BEFORE DELETE ON actor.org_unit_setting
553 FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
558 CREATE TABLE actor.usr_address (
559 id SERIAL PRIMARY KEY,
560 valid BOOL NOT NULL DEFAULT TRUE,
561 within_city_limits BOOL NOT NULL DEFAULT TRUE,
562 address_type TEXT NOT NULL DEFAULT 'MAILING',
563 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
564 street1 TEXT NOT NULL,
569 country TEXT NOT NULL,
570 post_code TEXT NOT NULL,
571 pending BOOL NOT NULL DEFAULT FALSE,
572 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
575 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
577 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
578 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
580 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
581 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
582 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
584 CREATE TABLE actor.usr_password_reset (
585 id SERIAL PRIMARY KEY,
587 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
588 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
589 has_been_reset BOOL NOT NULL DEFAULT false
591 COMMENT ON TABLE actor.usr_password_reset IS $$
592 Self-serve password reset requests
594 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
595 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
596 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
597 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
599 CREATE TABLE actor.org_address (
600 id SERIAL PRIMARY KEY,
601 valid BOOL NOT NULL DEFAULT TRUE,
602 address_type TEXT NOT NULL DEFAULT 'MAILING',
603 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
604 street1 TEXT NOT NULL,
609 country TEXT NOT NULL,
610 post_code TEXT NOT NULL,
614 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
616 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
617 SELECT SUBSTRING( $1, 1, 5);
620 CREATE TABLE actor.usr_standing_penalty (
621 id SERIAL PRIMARY KEY,
622 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
623 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
624 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
625 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
626 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
627 stop_date TIMESTAMP WITH TIME ZONE,
630 COMMENT ON TABLE actor.usr_standing_penalty IS $$
631 User standing penalties
634 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
635 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
638 CREATE TABLE actor.usr_saved_search (
639 id SERIAL PRIMARY KEY,
640 owner INT NOT NULL REFERENCES actor.usr (id)
642 DEFERRABLE INITIALLY DEFERRED,
644 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
645 query_text TEXT NOT NULL,
646 query_type TEXT NOT NULL
647 CONSTRAINT valid_query_text CHECK (
648 query_type IN ( 'URL' )) DEFAULT 'URL',
649 -- we may add other types someday
651 CONSTRAINT valid_target CHECK (
652 target IN ( 'record', 'metarecord', 'callnumber' )),
653 CONSTRAINT name_once_per_user UNIQUE (owner, name)
656 CREATE TABLE actor.address_alert (
657 id SERIAL PRIMARY KEY,
658 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
659 active BOOL NOT NULL DEFAULT TRUE,
660 match_all BOOL NOT NULL DEFAULT TRUE,
661 alert_message TEXT NOT NULL,
669 mailing_address BOOL NOT NULL DEFAULT FALSE,
670 billing_address BOOL NOT NULL DEFAULT FALSE
673 CREATE TABLE actor.usr_activity (
674 id BIGSERIAL PRIMARY KEY,
675 usr INT REFERENCES actor.usr (id) ON DELETE SET NULL,
676 etype INT NOT NULL REFERENCES config.usr_activity_type (id),
677 event_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
679 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
681 CREATE TABLE actor.toolbar (
682 id BIGSERIAL PRIMARY KEY,
683 ws INT REFERENCES actor.workstation (id) ON DELETE CASCADE,
684 org INT REFERENCES actor.org_unit (id) ON DELETE CASCADE,
685 usr INT REFERENCES actor.usr (id) ON DELETE CASCADE,
687 layout TEXT NOT NULL,
688 CONSTRAINT only_one_type CHECK (
689 (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
690 (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
691 (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
693 CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
695 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
696 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
697 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
699 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
700 CREATE TABLE actor.org_unit_custom_tree (
701 id SERIAL PRIMARY KEY,
702 active BOOLEAN DEFAULT FALSE,
703 purpose actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
706 CREATE TABLE actor.org_unit_custom_tree_node (
707 id SERIAL PRIMARY KEY,
708 tree INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
709 org_unit INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
710 parent_node INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
711 sibling_order INTEGER NOT NULL DEFAULT 0,
712 CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
715 CREATE TABLE actor.search_query (
716 id SERIAL PRIMARY KEY,
717 label TEXT NOT NULL, -- i18n
718 query_text TEXT NOT NULL -- QP text
721 CREATE TABLE actor.search_filter_group (
722 id SERIAL PRIMARY KEY,
723 owner INT NOT NULL REFERENCES actor.org_unit (id)
724 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
725 code TEXT NOT NULL, -- for CGI, etc.
726 label TEXT NOT NULL, -- i18n
727 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
728 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
729 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
732 CREATE TABLE actor.search_filter_group_entry (
733 id SERIAL PRIMARY KEY,
734 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
735 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
736 pos INT NOT NULL DEFAULT 0,
737 query INT NOT NULL REFERENCES actor.search_query(id)
738 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
739 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)