Make the "state" field in registration a togglable YAOUS
[working/Evergreen.git] / Open-ILS / src / sql / Pg / 005.schema.actors.sql
1 /*
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>
6  *
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.
11  *
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.
16  */
17
18 DROP SCHEMA IF EXISTS actor CASCADE;
19
20 BEGIN;
21 CREATE SCHEMA actor;
22 COMMENT ON SCHEMA actor IS $$
23 Holds all tables pertaining to users and libraries (org units).
24 $$;
25
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,
31         email                   TEXT,
32         passwd                  TEXT                            NOT NULL,
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,
35         ident_value             TEXT,
36         ident_type2             INT                             REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED,
37         ident_value2            TEXT,
38         net_access_level        INT                             NOT NULL DEFAULT 1 REFERENCES config.net_access_level (id) DEFERRABLE INITIALLY DEFERRED,
39         photo_url               TEXT,
40         prefix                  TEXT,
41         first_given_name        TEXT                            NOT NULL,
42         second_given_name       TEXT,
43         family_name             TEXT                            NOT NULL,
44         suffix                  TEXT,
45         alias                   TEXT,
46         day_phone               TEXT,
47         evening_phone           TEXT,
48         other_phone             TEXT,
49         mailing_address         INT,
50         billing_address         INT,
51         home_ou                 INT                             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',
63         alert_message           TEXT,
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
68 );
69 COMMENT ON TABLE actor.usr IS $$
70 User objects
71
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.
75 $$;
76
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);
81
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
86 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
87
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));
91
92 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
93     (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
94
95 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
96     (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
97
98 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
99     (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
100
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));
103
104 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
105         BEGIN
106                 NEW.passwd = MD5( NEW.passwd );
107                 RETURN NEW;
108         END;
109 $$ LANGUAGE PLPGSQL;
110
111 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
112         BEGIN
113                 IF NEW.passwd <> OLD.passwd THEN
114                         NEW.passwd = MD5( NEW.passwd );
115                 END IF;
116                 RETURN NEW;
117         END;
118 $$ LANGUAGE PLPGSQL;
119
120 CREATE OR REPLACE FUNCTION actor.au_updated()
121 RETURNS TRIGGER AS $$
122 BEGIN
123     NEW.last_update_time := now();
124         RETURN NEW;
125 END;
126 $$ LANGUAGE plpgsql;
127
128 CREATE TRIGGER au_update_trig
129         BEFORE INSERT OR UPDATE ON actor.usr
130         FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
131
132 CREATE TRIGGER actor_crypt_pw_update_trigger
133         BEFORE UPDATE ON actor.usr FOR EACH ROW
134         EXECUTE PROCEDURE actor.crypt_pw_update ();
135
136 CREATE TRIGGER actor_crypt_pw_insert_trigger
137         BEFORE INSERT ON actor.usr FOR EACH ROW
138         EXECUTE PROCEDURE actor.crypt_pw_insert ();
139
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;
141
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,
148         title           TEXT                            NOT NULL,
149         value           TEXT                            NOT NULL
150 );
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 );
153
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,
158         value   TEXT            NOT NULL,
159         CONSTRAINT usr_once_per_key UNIQUE (usr,name)
160 );
161 COMMENT ON TABLE actor.usr_setting IS $$
162 User settings
163
164 This table contains any arbitrary settings that a client
165 program would like to save for a user.
166 $$;
167
168 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
169
170 CREATE TABLE actor.stat_cat_sip_fields (
171     field   CHAR(2) PRIMARY KEY,
172     name    TEXT    NOT NULL,
173     one_only  BOOL    NOT NULL DEFAULT FALSE
174 );
175 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
176 Actor Statistical Category SIP Fields
177
178 Contains the list of valid SIP Field identifiers for
179 Statistical Categories.
180 $$;
181
182 CREATE TABLE actor.stat_cat (
183         id              SERIAL  PRIMARY KEY,
184         owner           INT     NOT NULL,
185         name            TEXT    NOT NULL,
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,
189     sip_format  TEXT,
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)
194 );
195 COMMENT ON TABLE actor.stat_cat IS $$
196 User Statistical Catagories
197
198 Local data collected about Users is placed into a Statistical
199 Catagory.  Here's where those catagories are defined.
200 $$;
201
202
203 CREATE TABLE actor.stat_cat_entry (
204         id              SERIAL  PRIMARY KEY,
205         stat_cat        INT     NOT NULL,
206         owner           INT     NOT NULL,
207         value           TEXT    NOT NULL,
208         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
209 );
210 COMMENT ON TABLE actor.stat_cat_entry IS $$
211 User Statistical Catagory Entries
212
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.
216 $$;
217
218
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)
225 );
226 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
227 Statistical Catagory Entry to User map
228
229 Records the stat_cat entries for each user.
230 $$;
231
232 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
233
234 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
235 DECLARE
236     sipfield actor.stat_cat_sip_fields%ROWTYPE;
237     use_count INT;
238 BEGIN
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';
245             END IF;
246         END IF;
247     END IF;
248     RETURN NEW;
249 END;
250 $func$ LANGUAGE PLPGSQL;
251
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();
255
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
261 );
262 COMMENT ON TABLE actor.card IS $$
263 Library Cards
264
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.
269 $$;
270
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));
273
274 CREATE TABLE actor.org_unit_type (
275         id              SERIAL  PRIMARY KEY,
276         name            TEXT    NOT NULL,
277         opac_label      TEXT    NOT NULL,
278         depth           INT     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
282 );
283 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
284
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,
289         ill_address     INT,
290         holds_address   INT,
291         mailing_address INT,
292         billing_address INT,
293         shortname       TEXT    NOT NULL UNIQUE,
294         name            TEXT    NOT NULL UNIQUE,
295         email           TEXT,
296         phone           TEXT,
297         opac_visible    BOOL    NOT NULL DEFAULT TRUE,
298         fiscal_calendar INT     NOT NULL DEFAULT 1   -- foreign key constraint to be added later
299 );
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);
306
307 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
308         DECLARE
309                 current_aou actor.org_unit%ROWTYPE;
310                 seen_ous    INT[];
311                 depth_count INT;
312         BEGIN
313                 current_aou := NEW;
314                 depth_count := 0;
315                 seen_ous := ARRAY[NEW.id];
316
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
320                         END IF;
321                 END IF;
322
323                 LOOP
324                         IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
325                                 RETURN NEW; -- No loop. Carry on.
326                         END IF;
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!
329                         END IF;
330                         -- Get the next one!
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';
336                         END IF;
337                 END LOOP;
338
339                 RETURN NEW;
340         END;
341 $$ LANGUAGE PLPGSQL;
342
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 ();
346
347 CREATE TABLE actor.org_lasso (
348     id      SERIAL  PRIMARY KEY,
349     name        TEXT    UNIQUE
350 );
351
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
356 );
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);
359
360 CREATE TABLE actor.org_unit_proximity (
361         id              BIGSERIAL       PRIMARY KEY,
362         from_org        INT,
363         to_org          INT,
364         prox            INT
365 );
366 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
367
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)
374 );
375 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
376 User Statistical Category Default Entry
377
378 A library may choose one of the stat_cat entries to be the
379 default entry.
380 $$;
381
382
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)
395 );
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, ''),
403     pos
404 );
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);
411
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'
428 );
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.)
432 $$;
433 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
434 When does this org_unit open on Monday?
435 $$;
436 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
437 When does this org_unit close on Monday?
438 $$;
439 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
440 When does this org_unit open on Tuesday?
441 $$;
442 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
443 When does this org_unit close on Tuesday?
444 $$;
445 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
446 When does this org_unit open on Wednesday?
447 $$;
448 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
449 When does this org_unit close on Wednesday?
450 $$;
451 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
452 When does this org_unit open on Thursday?
453 $$;
454 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
455 When does this org_unit close on Thursday?
456 $$;
457 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
458 When does this org_unit open on Friday?
459 $$;
460 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
461 When does this org_unit close on Friday?
462 $$;
463 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
464 When does this org_unit open on Saturday?
465 $$;
466 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
467 When does this org_unit close on Saturday?
468 $$;
469 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
470 When does this org_unit open on Sunday?
471 $$;
472 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
473 When does this org_unit close on Sunday?
474 $$;
475
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,
481         reason          TEXT
482 );
483
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
489 );
490
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)
499 );
500 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
501
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,
506         value           TEXT            NOT NULL,
507         CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
508         CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
509 );
510 COMMENT ON TABLE actor.org_unit_setting IS $$
511 Org Unit settings
512
513 This table contains any arbitrary settings that a client
514 program would like to save for an org unit.
515 $$;
516
517 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
518
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$
521     DECLARE
522     original TEXT;
523     BEGIN
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;
526                 
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);
528         
529         RETURN NEW;
530     END;
531 $ous_change_log$ LANGUAGE plpgsql;    
532
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();
536
537 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
538     DECLARE
539     original TEXT;
540     BEGIN
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;
543                 
544         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
545         
546         RETURN OLD;
547     END;
548 $ous_delete_log$ LANGUAGE plpgsql;    
549
550 CREATE TRIGGER log_ous_del
551     BEFORE DELETE ON actor.org_unit_setting
552     FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
553
554
555
556
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,
564         street2                 TEXT,
565         city                    TEXT    NOT NULL,
566         county                  TEXT,
567         state                   TEXT,
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
572 );
573
574 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
575
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));
578
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));
582
583 CREATE TABLE actor.usr_password_reset (
584   id SERIAL PRIMARY KEY,
585   uuid TEXT NOT NULL, 
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
589 );
590 COMMENT ON TABLE actor.usr_password_reset IS $$
591 Self-serve password reset requests
592 $$;
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);
597
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,
604         street2         TEXT,
605         city            TEXT    NOT NULL,
606         county          TEXT,
607         state           TEXT    NOT NULL,
608         country         TEXT    NOT NULL,
609         post_code       TEXT    NOT NULL,
610     san         TEXT
611 );
612
613 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
614
615 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
616         SELECT SUBSTRING( $1, 1, 5);
617 $$ LANGUAGE SQL;
618
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,
627         note                    TEXT
628 );
629 COMMENT ON TABLE actor.usr_standing_penalty IS $$
630 User standing penalties
631 $$;
632
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 );
635
636
637 CREATE TABLE actor.usr_saved_search (
638     id              SERIAL          PRIMARY KEY,
639         owner           INT             NOT NULL REFERENCES actor.usr (id)
640                                         ON DELETE CASCADE
641                                         DEFERRABLE INITIALLY DEFERRED,
642         name            TEXT            NOT NULL,
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
649         target          TEXT            NOT NULL
650                                         CONSTRAINT valid_target CHECK (
651                                         target IN ( 'record', 'metarecord', 'callnumber' )),
652         CONSTRAINT name_once_per_user UNIQUE (owner, name)
653 );
654
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,
661     street1         TEXT,
662     street2         TEXT,
663     city            TEXT,
664     county          TEXT,
665     state           TEXT,
666     country         TEXT,
667     post_code       TEXT,
668     mailing_address BOOL    NOT NULL DEFAULT FALSE,
669     billing_address BOOL    NOT NULL DEFAULT FALSE
670 );
671
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()
677 );
678 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
679
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,
685     label       TEXT        NOT NULL,
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)
691     ),
692     CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
693 );
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;
697
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
703 );
704
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)
712 );
713
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
718 );
719
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)
729 );
730
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)
739 );
740
741 COMMIT;