DB layout for recording custom toolbars
[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         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
192 );
193 COMMENT ON TABLE actor.stat_cat IS $$
194 User Statistical Catagories
195
196 Local data collected about Users is placed into a Statistical
197 Catagory.  Here's where those catagories are defined.
198 $$;
199
200
201 CREATE TABLE actor.stat_cat_entry (
202         id              SERIAL  PRIMARY KEY,
203         stat_cat        INT     NOT NULL,
204         owner           INT     NOT NULL,
205         value           TEXT    NOT NULL,
206         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
207 );
208 COMMENT ON TABLE actor.stat_cat_entry IS $$
209 User Statistical Catagory Entries
210
211 Local data collected about Users is placed into a Statistical
212 Catagory.  Each library can create entries into any of its own
213 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
214 $$;
215
216
217 CREATE TABLE actor.stat_cat_entry_usr_map (
218         id              BIGSERIAL       PRIMARY KEY,
219         stat_cat_entry  TEXT            NOT NULL,
220         stat_cat        INT             NOT NULL,
221         target_usr      INT             NOT NULL,
222         CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
223 );
224 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
225 Statistical Catagory Entry to User map
226
227 Records the stat_cat entries for each user.
228 $$;
229
230 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
231
232 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
233 DECLARE
234     sipfield actor.stat_cat_sip_fields%ROWTYPE;
235     use_count INT;
236 BEGIN
237     IF NEW.sip_field IS NOT NULL THEN
238         SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
239         IF sipfield.one_only THEN
240             SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
241             IF use_count > 0 THEN
242                 RAISE EXCEPTION 'Sip field cannot be used twice';
243             END IF;
244         END IF;
245     END IF;
246     RETURN NEW;
247 END;
248 $func$ LANGUAGE PLPGSQL;
249
250 CREATE TRIGGER actor_stat_cat_sip_update_trigger
251     BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
252     EXECUTE PROCEDURE actor.stat_cat_check();
253
254 CREATE TABLE actor.card (
255         id      SERIAL  PRIMARY KEY,
256         usr     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
257         barcode TEXT    NOT NULL UNIQUE,
258         active  BOOL    NOT NULL DEFAULT TRUE
259 );
260 COMMENT ON TABLE actor.card IS $$
261 Library Cards
262
263 Each User has one or more library cards.  The current "main"
264 card is linked to here from the actor.usr table, and it is up
265 to the consortium policy whether more than one card can be
266 active for any one user at a given time.
267 $$;
268
269 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
270 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
271
272 CREATE TABLE actor.org_unit_type (
273         id              SERIAL  PRIMARY KEY,
274         name            TEXT    NOT NULL,
275         opac_label      TEXT    NOT NULL,
276         depth           INT     NOT NULL,
277         parent          INT     REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
278         can_have_vols   BOOL    NOT NULL DEFAULT TRUE,
279         can_have_users  BOOL    NOT NULL DEFAULT TRUE
280 );
281 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
282
283 CREATE TABLE actor.org_unit (
284         id              SERIAL  PRIMARY KEY,
285         parent_ou       INT     REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
286         ou_type         INT     NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
287         ill_address     INT,
288         holds_address   INT,
289         mailing_address INT,
290         billing_address INT,
291         shortname       TEXT    NOT NULL UNIQUE,
292         name            TEXT    NOT NULL UNIQUE,
293         email           TEXT,
294         phone           TEXT,
295         opac_visible    BOOL    NOT NULL DEFAULT TRUE,
296         fiscal_calendar INT     NOT NULL DEFAULT 1   -- foreign key constraint to be added later
297 );
298 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
299 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
300 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
301 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
302 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
303 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
304
305 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
306         DECLARE
307                 current_aou actor.org_unit%ROWTYPE;
308                 seen_ous    INT[];
309                 depth_count INT;
310         BEGIN
311                 current_aou := NEW;
312                 depth_count := 0;
313                 seen_ous := ARRAY[NEW.id];
314
315                 IF (TG_OP = 'UPDATE') THEN
316                         IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
317                                 RETURN NEW; -- Doing an UPDATE with no change, just return it
318                         END IF;
319                 END IF;
320
321                 LOOP
322                         IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
323                                 RETURN NEW; -- No loop. Carry on.
324                         END IF;
325                         IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
326                                 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
327                         END IF;
328                         -- Get the next one!
329                         SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
330                         seen_ous := seen_ous || current_aou.id;
331                         depth_count := depth_count + 1;
332                         IF depth_count = 100 THEN
333                                 RAISE 'OU CHECK TOO DEEP';
334                         END IF;
335                 END LOOP;
336
337                 RETURN NEW;
338         END;
339 $$ LANGUAGE PLPGSQL;
340
341 CREATE TRIGGER actor_org_unit_parent_protect_trigger
342     BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
343     EXECUTE PROCEDURE actor.org_unit_parent_protect ();
344
345 CREATE TABLE actor.org_lasso (
346     id      SERIAL  PRIMARY KEY,
347     name        TEXT    UNIQUE
348 );
349
350 CREATE TABLE actor.org_lasso_map (
351     id          SERIAL  PRIMARY KEY,
352     lasso       INT     NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
353     org_unit    INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
354 );
355 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
356 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
357
358 CREATE TABLE actor.org_unit_proximity (
359         id              BIGSERIAL       PRIMARY KEY,
360         from_org        INT,
361         to_org          INT,
362         prox            INT
363 );
364 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
365
366 CREATE TABLE actor.hours_of_operation (
367         id              INT     PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
368         dow_0_open      TIME    NOT NULL DEFAULT '09:00',
369         dow_0_close     TIME    NOT NULL DEFAULT '17:00',
370         dow_1_open      TIME    NOT NULL DEFAULT '09:00',
371         dow_1_close     TIME    NOT NULL DEFAULT '17:00',
372         dow_2_open      TIME    NOT NULL DEFAULT '09:00',
373         dow_2_close     TIME    NOT NULL DEFAULT '17:00',
374         dow_3_open      TIME    NOT NULL DEFAULT '09:00',
375         dow_3_close     TIME    NOT NULL DEFAULT '17:00',
376         dow_4_open      TIME    NOT NULL DEFAULT '09:00',
377         dow_4_close     TIME    NOT NULL DEFAULT '17:00',
378         dow_5_open      TIME    NOT NULL DEFAULT '09:00',
379         dow_5_close     TIME    NOT NULL DEFAULT '17:00',
380         dow_6_open      TIME    NOT NULL DEFAULT '09:00',
381         dow_6_close     TIME    NOT NULL DEFAULT '17:00'
382 );
383 COMMENT ON TABLE actor.hours_of_operation IS $$
384 When does this org_unit usually open and close?  (Variations
385 are expressed in the actor.org_unit_closed table.)
386 $$;
387 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
388 When does this org_unit open on Monday?
389 $$;
390 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
391 When does this org_unit close on Monday?
392 $$;
393 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
394 When does this org_unit open on Tuesday?
395 $$;
396 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
397 When does this org_unit close on Tuesday?
398 $$;
399 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
400 When does this org_unit open on Wednesday?
401 $$;
402 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
403 When does this org_unit close on Wednesday?
404 $$;
405 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
406 When does this org_unit open on Thursday?
407 $$;
408 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
409 When does this org_unit close on Thursday?
410 $$;
411 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
412 When does this org_unit open on Friday?
413 $$;
414 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
415 When does this org_unit close on Friday?
416 $$;
417 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
418 When does this org_unit open on Saturday?
419 $$;
420 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
421 When does this org_unit close on Saturday?
422 $$;
423 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
424 When does this org_unit open on Sunday?
425 $$;
426 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
427 When does this org_unit close on Sunday?
428 $$;
429
430 CREATE TABLE actor.org_unit_closed (
431         id              SERIAL                          PRIMARY KEY,
432         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
433         close_start     TIMESTAMP WITH TIME ZONE        NOT NULL,
434         close_end       TIMESTAMP WITH TIME ZONE        NOT NULL,
435         reason          TEXT
436 );
437
438 -- Workstation registration...
439 CREATE TABLE actor.workstation (
440         id              SERIAL  PRIMARY KEY,
441         name            TEXT    NOT NULL UNIQUE,
442         owning_lib      INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
443 );
444
445 CREATE TABLE actor.usr_org_unit_opt_in (
446         id              SERIAL                          PRIMARY KEY,
447         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
448         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
449         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
450         opt_in_ts       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
451         opt_in_ws       INT                             NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
452         CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
453 );
454 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
455
456 CREATE TABLE actor.org_unit_setting (
457         id              BIGSERIAL       PRIMARY KEY,
458         org_unit        INT             NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
459         name            TEXT    NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
460         value           TEXT            NOT NULL,
461         CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
462         CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
463 );
464 COMMENT ON TABLE actor.org_unit_setting IS $$
465 Org Unit settings
466
467 This table contains any arbitrary settings that a client
468 program would like to save for an org unit.
469 $$;
470
471 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
472
473 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
474 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
475     DECLARE
476     original TEXT;
477     BEGIN
478         -- Check for which setting is being updated, and log it.
479         SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
480                 
481         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
482         
483         RETURN NEW;
484     END;
485 $ous_change_log$ LANGUAGE plpgsql;    
486
487 CREATE TRIGGER log_ous_change
488     BEFORE INSERT OR UPDATE ON actor.org_unit_setting
489     FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
490
491 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
492     DECLARE
493     original TEXT;
494     BEGIN
495         -- Check for which setting is being updated, and log it.
496         SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
497                 
498         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
499         
500         RETURN OLD;
501     END;
502 $ous_delete_log$ LANGUAGE plpgsql;    
503
504 CREATE TRIGGER log_ous_del
505     BEFORE DELETE ON actor.org_unit_setting
506     FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
507
508
509
510
511 CREATE TABLE actor.usr_address (
512         id                      SERIAL  PRIMARY KEY,
513         valid                   BOOL    NOT NULL DEFAULT TRUE,
514         within_city_limits      BOOL    NOT NULL DEFAULT TRUE,
515         address_type            TEXT    NOT NULL DEFAULT 'MAILING',
516         usr                     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
517         street1                 TEXT    NOT NULL,
518         street2                 TEXT,
519         city                    TEXT    NOT NULL,
520         county                  TEXT,
521         state                   TEXT    NOT NULL,
522         country                 TEXT    NOT NULL,
523         post_code               TEXT    NOT NULL,
524     pending         BOOL    NOT NULL DEFAULT FALSE,
525         replaces            INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
526 );
527
528 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
529
530 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
531 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
532
533 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
534 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
535 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
536
537 CREATE TABLE actor.usr_password_reset (
538   id SERIAL PRIMARY KEY,
539   uuid TEXT NOT NULL, 
540   usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED, 
541   request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), 
542   has_been_reset BOOL NOT NULL DEFAULT false
543 );
544 COMMENT ON TABLE actor.usr_password_reset IS $$
545 Self-serve password reset requests
546 $$;
547 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
548 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
549 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
550 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
551
552 CREATE TABLE actor.org_address (
553         id              SERIAL  PRIMARY KEY,
554         valid           BOOL    NOT NULL DEFAULT TRUE,
555         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
556         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
557         street1         TEXT    NOT NULL,
558         street2         TEXT,
559         city            TEXT    NOT NULL,
560         county          TEXT,
561         state           TEXT    NOT NULL,
562         country         TEXT    NOT NULL,
563         post_code       TEXT    NOT NULL,
564     san         TEXT
565 );
566
567 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
568
569 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
570         SELECT SUBSTRING( $1, 1, 5);
571 $$ LANGUAGE SQL;
572
573 CREATE TABLE actor.usr_standing_penalty (
574         id                      SERIAL  PRIMARY KEY,
575         org_unit                INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
576         usr                     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
577         standing_penalty        INT     NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
578         staff                   INT     REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
579         set_date                TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
580         stop_date               TIMESTAMP WITH TIME ZONE,
581         note                    TEXT
582 );
583 COMMENT ON TABLE actor.usr_standing_penalty IS $$
584 User standing penalties
585 $$;
586
587 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
588 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
589
590
591 CREATE TABLE actor.usr_saved_search (
592     id              SERIAL          PRIMARY KEY,
593         owner           INT             NOT NULL REFERENCES actor.usr (id)
594                                         ON DELETE CASCADE
595                                         DEFERRABLE INITIALLY DEFERRED,
596         name            TEXT            NOT NULL,
597         create_date     TIMESTAMPTZ     NOT NULL DEFAULT now(),
598         query_text      TEXT            NOT NULL,
599         query_type      TEXT            NOT NULL
600                                         CONSTRAINT valid_query_text CHECK (
601                                         query_type IN ( 'URL' )) DEFAULT 'URL',
602                                         -- we may add other types someday
603         target          TEXT            NOT NULL
604                                         CONSTRAINT valid_target CHECK (
605                                         target IN ( 'record', 'metarecord', 'callnumber' )),
606         CONSTRAINT name_once_per_user UNIQUE (owner, name)
607 );
608
609 CREATE TABLE actor.address_alert (
610     id              SERIAL  PRIMARY KEY,
611     owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
612     active          BOOL    NOT NULL DEFAULT TRUE,
613     match_all       BOOL    NOT NULL DEFAULT TRUE,
614     alert_message   TEXT    NOT NULL,
615     street1         TEXT,
616     street2         TEXT,
617     city            TEXT,
618     county          TEXT,
619     state           TEXT,
620     country         TEXT,
621     post_code       TEXT,
622     mailing_address BOOL    NOT NULL DEFAULT FALSE,
623     billing_address BOOL    NOT NULL DEFAULT FALSE
624 );
625
626 CREATE TABLE actor.usr_activity (
627     id          BIGSERIAL   PRIMARY KEY,
628     usr         INT         REFERENCES actor.usr (id) ON DELETE SET NULL,
629     etype       INT         NOT NULL REFERENCES config.usr_activity_type (id),
630     event_time  TIMESTAMPTZ NOT NULL DEFAULT NOW()
631 );
632
633 CREATE TABLE actor.toolbar (
634     id          BIGSERIAL   PRIMARY KEY,
635     ws          INT         REFERENCES actor.workstation (id) ON DELETE CASCADE,
636     org         INT         REFERENCES actor.org_unit (id) ON DELETE CASCADE,
637     usr         INT         REFERENCES actor.usr (id) ON DELETE CASCADE,
638     label       TEXT        NOT NULL,
639     layout      TEXT        NOT NULL,
640         CONSTRAINT label_once_per_ws UNIQUE (ws, label) WHERE ws IS NOT NULL,
641         CONSTRAINT label_once_per_org UNIQUE (org, label) WHERE org IS NOT NULL,
642         CONSTRAINT label_once_per_usr UNIQUE (usr, label) WHERE usr IS NOT NULL,
643     CONSTRAINT only_one_type CHECK (
644         (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
645         (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
646         (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
647     ),
648     CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
649 );
650
651 COMMIT;