LP#1750894 Workstation & Cascade settings
[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                     DATE,
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 CREATE INDEX actor_usr_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(first_given_name));
86 CREATE INDEX actor_usr_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(second_given_name));
87 CREATE INDEX actor_usr_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(family_name));
88 CREATE INDEX actor_usr_usrname_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(usrname));
89
90 CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname));
91 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
92
93 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
94 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
95 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
96
97 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
98     (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
99
100 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
101     (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
102
103 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
104     (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
105
106 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
107 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
108
109 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
110         BEGIN
111                 NEW.passwd = MD5( NEW.passwd );
112                 RETURN NEW;
113         END;
114 $$ LANGUAGE PLPGSQL;
115
116 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
117         BEGIN
118                 IF NEW.passwd <> OLD.passwd THEN
119                         NEW.passwd = MD5( NEW.passwd );
120                 END IF;
121                 RETURN NEW;
122         END;
123 $$ LANGUAGE PLPGSQL;
124
125 CREATE OR REPLACE FUNCTION actor.au_updated()
126 RETURNS TRIGGER AS $$
127 BEGIN
128     NEW.last_update_time := now();
129         RETURN NEW;
130 END;
131 $$ LANGUAGE plpgsql;
132
133 CREATE TRIGGER au_update_trig
134         BEFORE INSERT OR UPDATE ON actor.usr
135         FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
136
137 CREATE TRIGGER actor_crypt_pw_update_trigger
138         BEFORE UPDATE ON actor.usr FOR EACH ROW
139         EXECUTE PROCEDURE actor.crypt_pw_update ();
140
141 CREATE TRIGGER actor_crypt_pw_insert_trigger
142         BEFORE INSERT ON actor.usr FOR EACH ROW
143         EXECUTE PROCEDURE actor.crypt_pw_insert ();
144
145 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;
146
147 CREATE TABLE actor.usr_note (
148         id              BIGSERIAL                       PRIMARY KEY,
149         usr             BIGINT                          NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
150         creator         BIGINT                          NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
151         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
152         pub             BOOL                            NOT NULL DEFAULT FALSE,
153         title           TEXT                            NOT NULL,
154         value           TEXT                            NOT NULL
155 );
156 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
157 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
158
159 CREATE TABLE actor.usr_setting (
160         id      BIGSERIAL       PRIMARY KEY,
161         usr     INT             NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
162         name    TEXT            NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
163         value   TEXT            NOT NULL,
164         CONSTRAINT usr_once_per_key UNIQUE (usr,name)
165 );
166 COMMENT ON TABLE actor.usr_setting IS $$
167 User settings
168
169 This table contains any arbitrary settings that a client
170 program would like to save for a user.
171 $$;
172
173 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
174
175 CREATE TABLE actor.stat_cat_sip_fields (
176     field   CHAR(2) PRIMARY KEY,
177     name    TEXT    NOT NULL,
178     one_only  BOOL    NOT NULL DEFAULT FALSE
179 );
180 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
181 Actor Statistical Category SIP Fields
182
183 Contains the list of valid SIP Field identifiers for
184 Statistical Categories.
185 $$;
186
187 CREATE TABLE actor.stat_cat (
188         id              SERIAL  PRIMARY KEY,
189         owner           INT     NOT NULL,
190         name            TEXT    NOT NULL,
191         opac_visible    BOOL NOT NULL DEFAULT FALSE,
192         usr_summary     BOOL NOT NULL DEFAULT FALSE,
193     sip_field   CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
194     sip_format  TEXT,
195     checkout_archive    BOOL NOT NULL DEFAULT FALSE,
196         required        BOOL NOT NULL DEFAULT FALSE,
197         allow_freetext  BOOL NOT NULL DEFAULT TRUE,
198         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
199 );
200 COMMENT ON TABLE actor.stat_cat IS $$
201 User Statistical Catagories
202
203 Local data collected about Users is placed into a Statistical
204 Catagory.  Here's where those catagories are defined.
205 $$;
206
207
208 CREATE TABLE actor.stat_cat_entry (
209         id              SERIAL  PRIMARY KEY,
210         stat_cat        INT     NOT NULL,
211         owner           INT     NOT NULL,
212         value           TEXT    NOT NULL,
213         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
214 );
215 COMMENT ON TABLE actor.stat_cat_entry IS $$
216 User Statistical Catagory Entries
217
218 Local data collected about Users is placed into a Statistical
219 Catagory.  Each library can create entries into any of its own
220 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
221 $$;
222
223
224 CREATE TABLE actor.stat_cat_entry_usr_map (
225         id              BIGSERIAL       PRIMARY KEY,
226         stat_cat_entry  TEXT            NOT NULL,
227         stat_cat        INT             NOT NULL,
228         target_usr      INT             NOT NULL,
229         CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
230 );
231 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
232 Statistical Catagory Entry to User map
233
234 Records the stat_cat entries for each user.
235 $$;
236
237 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
238
239 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
240 DECLARE
241     sipfield actor.stat_cat_sip_fields%ROWTYPE;
242     use_count INT;
243 BEGIN
244     IF NEW.sip_field IS NOT NULL THEN
245         SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
246         IF sipfield.one_only THEN
247             SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
248             IF use_count > 0 THEN
249                 RAISE EXCEPTION 'Sip field cannot be used twice';
250             END IF;
251         END IF;
252     END IF;
253     RETURN NEW;
254 END;
255 $func$ LANGUAGE PLPGSQL;
256
257 CREATE TRIGGER actor_stat_cat_sip_update_trigger
258     BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
259     EXECUTE PROCEDURE actor.stat_cat_check();
260
261 CREATE TABLE actor.card (
262         id      SERIAL  PRIMARY KEY,
263         usr     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
264         barcode TEXT    NOT NULL UNIQUE,
265         active  BOOL    NOT NULL DEFAULT TRUE
266 );
267 COMMENT ON TABLE actor.card IS $$
268 Library Cards
269
270 Each User has one or more library cards.  The current "main"
271 card is linked to here from the actor.usr table, and it is up
272 to the consortium policy whether more than one card can be
273 active for any one user at a given time.
274 $$;
275
276 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
277 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
278
279 CREATE TABLE actor.org_unit_type (
280         id              SERIAL  PRIMARY KEY,
281         name            TEXT    NOT NULL,
282         opac_label      TEXT    NOT NULL,
283         depth           INT     NOT NULL,
284         parent          INT     REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
285         can_have_vols   BOOL    NOT NULL DEFAULT TRUE,
286         can_have_users  BOOL    NOT NULL DEFAULT TRUE
287 );
288 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
289
290 CREATE TABLE actor.org_unit (
291         id              SERIAL  PRIMARY KEY,
292         parent_ou       INT     REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
293         ou_type         INT     NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
294         ill_address     INT,
295         holds_address   INT,
296         mailing_address INT,
297         billing_address INT,
298         shortname       TEXT    NOT NULL UNIQUE,
299         name            TEXT    NOT NULL UNIQUE,
300         email           TEXT,
301         phone           TEXT,
302         opac_visible    BOOL    NOT NULL DEFAULT TRUE,
303         fiscal_calendar INT     NOT NULL DEFAULT 1   -- foreign key constraint to be added later
304 );
305 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
306 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
307 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
308 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
309 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
310 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
311
312 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
313         DECLARE
314                 current_aou actor.org_unit%ROWTYPE;
315                 seen_ous    INT[];
316                 depth_count INT;
317         BEGIN
318                 current_aou := NEW;
319                 depth_count := 0;
320                 seen_ous := ARRAY[NEW.id];
321
322                 IF (TG_OP = 'UPDATE') THEN
323                         IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
324                                 RETURN NEW; -- Doing an UPDATE with no change, just return it
325                         END IF;
326                 END IF;
327
328                 LOOP
329                         IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
330                                 RETURN NEW; -- No loop. Carry on.
331                         END IF;
332                         IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
333                                 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
334                         END IF;
335                         -- Get the next one!
336                         SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
337                         seen_ous := seen_ous || current_aou.id;
338                         depth_count := depth_count + 1;
339                         IF depth_count = 100 THEN
340                                 RAISE 'OU CHECK TOO DEEP';
341                         END IF;
342                 END LOOP;
343
344                 RETURN NEW;
345         END;
346 $$ LANGUAGE PLPGSQL;
347
348 CREATE TRIGGER actor_org_unit_parent_protect_trigger
349     BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
350     EXECUTE PROCEDURE actor.org_unit_parent_protect ();
351
352 CREATE TABLE actor.org_lasso (
353     id      SERIAL  PRIMARY KEY,
354     name        TEXT    UNIQUE
355 );
356
357 CREATE TABLE actor.org_lasso_map (
358     id          SERIAL  PRIMARY KEY,
359     lasso       INT     NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
360     org_unit    INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
361 );
362 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
363 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
364
365 CREATE TABLE actor.org_unit_proximity (
366         id              BIGSERIAL       PRIMARY KEY,
367         from_org        INT,
368         to_org          INT,
369         prox            INT
370 );
371 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
372
373 CREATE TABLE actor.stat_cat_entry_default (
374         id              SERIAL  PRIMARY KEY,
375         stat_cat_entry  INT     NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
376         stat_cat        INT     NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
377         owner           INT     NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
378         CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
379 );
380 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
381 User Statistical Category Default Entry
382
383 A library may choose one of the stat_cat entries to be the
384 default entry.
385 $$;
386
387
388 CREATE TABLE actor.org_unit_proximity_adjustment (
389     id                  SERIAL   PRIMARY KEY,
390     item_circ_lib       INT         REFERENCES actor.org_unit (id),
391     item_owning_lib     INT         REFERENCES actor.org_unit (id),
392     copy_location       INT,        -- REFERENCES asset.copy_location (id),
393     hold_pickup_lib     INT         REFERENCES actor.org_unit (id),
394     hold_request_lib    INT         REFERENCES actor.org_unit (id),
395     pos                 INT         NOT NULL DEFAULT 0,
396     absolute_adjustment BOOL        NOT NULL DEFAULT FALSE,
397     prox_adjustment     NUMERIC,
398     circ_mod            TEXT,       -- REFERENCES config.circ_modifier (code),
399     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)
400 );
401 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
402     COALESCE(item_circ_lib, -1),
403     COALESCE(item_owning_lib, -1),
404     COALESCE(copy_location, -1),
405     COALESCE(hold_pickup_lib, -1),
406     COALESCE(hold_request_lib, -1),
407     COALESCE(circ_mod, ''),
408     pos
409 );
410 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
411 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
412 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
413 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
414 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
415 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
416
417 CREATE TABLE actor.hours_of_operation (
418         id              INT     PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
419         dow_0_open      TIME    NOT NULL DEFAULT '09:00',
420         dow_0_close     TIME    NOT NULL DEFAULT '17:00',
421         dow_1_open      TIME    NOT NULL DEFAULT '09:00',
422         dow_1_close     TIME    NOT NULL DEFAULT '17:00',
423         dow_2_open      TIME    NOT NULL DEFAULT '09:00',
424         dow_2_close     TIME    NOT NULL DEFAULT '17:00',
425         dow_3_open      TIME    NOT NULL DEFAULT '09:00',
426         dow_3_close     TIME    NOT NULL DEFAULT '17:00',
427         dow_4_open      TIME    NOT NULL DEFAULT '09:00',
428         dow_4_close     TIME    NOT NULL DEFAULT '17:00',
429         dow_5_open      TIME    NOT NULL DEFAULT '09:00',
430         dow_5_close     TIME    NOT NULL DEFAULT '17:00',
431         dow_6_open      TIME    NOT NULL DEFAULT '09:00',
432         dow_6_close     TIME    NOT NULL DEFAULT '17:00'
433 );
434 COMMENT ON TABLE actor.hours_of_operation IS $$
435 When does this org_unit usually open and close?  (Variations
436 are expressed in the actor.org_unit_closed table.)
437 $$;
438 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
439 When does this org_unit open on Monday?
440 $$;
441 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
442 When does this org_unit close on Monday?
443 $$;
444 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
445 When does this org_unit open on Tuesday?
446 $$;
447 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
448 When does this org_unit close on Tuesday?
449 $$;
450 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
451 When does this org_unit open on Wednesday?
452 $$;
453 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
454 When does this org_unit close on Wednesday?
455 $$;
456 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
457 When does this org_unit open on Thursday?
458 $$;
459 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
460 When does this org_unit close on Thursday?
461 $$;
462 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
463 When does this org_unit open on Friday?
464 $$;
465 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
466 When does this org_unit close on Friday?
467 $$;
468 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
469 When does this org_unit open on Saturday?
470 $$;
471 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
472 When does this org_unit close on Saturday?
473 $$;
474 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
475 When does this org_unit open on Sunday?
476 $$;
477 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
478 When does this org_unit close on Sunday?
479 $$;
480
481 CREATE TABLE actor.org_unit_closed (
482         id              SERIAL                          PRIMARY KEY,
483         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
484         close_start     TIMESTAMP WITH TIME ZONE        NOT NULL,
485         close_end       TIMESTAMP WITH TIME ZONE        NOT NULL,
486     full_day    BOOLEAN                     NOT NULL DEFAULT FALSE,
487     multi_day   BOOLEAN                     NOT NULL DEFAULT FALSE,
488         reason          TEXT
489 );
490
491 -- Workstation registration...
492 CREATE TABLE actor.workstation (
493         id              SERIAL  PRIMARY KEY,
494         name            TEXT    NOT NULL UNIQUE,
495         owning_lib      INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
496 );
497
498 CREATE TABLE actor.usr_org_unit_opt_in (
499         id              SERIAL                          PRIMARY KEY,
500         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
501         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
502         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
503         opt_in_ts       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
504         opt_in_ws       INT                             NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
505         CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
506 );
507 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
508
509 CREATE TABLE actor.org_unit_setting (
510         id              BIGSERIAL       PRIMARY KEY,
511         org_unit        INT             NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
512         name            TEXT    NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
513         value           TEXT            NOT NULL,
514         CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
515         CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
516 );
517 COMMENT ON TABLE actor.org_unit_setting IS $$
518 Org Unit settings
519
520 This table contains any arbitrary settings that a client
521 program would like to save for an org unit.
522 $$;
523
524 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
525
526 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
527 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
528     DECLARE
529     original TEXT;
530     BEGIN
531         -- Check for which setting is being updated, and log it.
532         SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
533                 
534         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
535         
536         RETURN NEW;
537     END;
538 $ous_change_log$ LANGUAGE plpgsql;    
539
540 CREATE TRIGGER log_ous_change
541     BEFORE INSERT OR UPDATE ON actor.org_unit_setting
542     FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
543
544 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
545     DECLARE
546     original TEXT;
547     BEGIN
548         -- Check for which setting is being updated, and log it.
549         SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
550                 
551         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
552         
553         RETURN OLD;
554     END;
555 $ous_delete_log$ LANGUAGE plpgsql;    
556
557 CREATE TRIGGER log_ous_del
558     BEFORE DELETE ON actor.org_unit_setting
559     FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
560
561
562
563
564 CREATE TABLE actor.usr_address (
565         id                      SERIAL  PRIMARY KEY,
566         valid                   BOOL    NOT NULL DEFAULT TRUE,
567         within_city_limits      BOOL    NOT NULL DEFAULT TRUE,
568         address_type            TEXT    NOT NULL DEFAULT 'MAILING',
569         usr                     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
570         street1                 TEXT    NOT NULL,
571         street2                 TEXT,
572         city                    TEXT    NOT NULL,
573         county                  TEXT,
574         state                   TEXT,
575         country                 TEXT    NOT NULL,
576         post_code               TEXT    NOT NULL,
577     pending         BOOL    NOT NULL DEFAULT FALSE,
578         replaces            INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
579 );
580
581 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
582
583 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
584 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
585
586 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
587 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
588 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
589
590 CREATE TABLE actor.usr_password_reset (
591   id SERIAL PRIMARY KEY,
592   uuid TEXT NOT NULL, 
593   usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED, 
594   request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), 
595   has_been_reset BOOL NOT NULL DEFAULT false
596 );
597 COMMENT ON TABLE actor.usr_password_reset IS $$
598 Self-serve password reset requests
599 $$;
600 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
601 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
602 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
603 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
604
605 CREATE TABLE actor.org_address (
606         id              SERIAL  PRIMARY KEY,
607         valid           BOOL    NOT NULL DEFAULT TRUE,
608         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
609         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
610         street1         TEXT    NOT NULL,
611         street2         TEXT,
612         city            TEXT    NOT NULL,
613         county          TEXT,
614         state           TEXT,
615         country         TEXT    NOT NULL,
616         post_code       TEXT    NOT NULL,
617     san         TEXT
618 );
619
620 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
621
622 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
623         SELECT SUBSTRING( $1, 1, 5);
624 $$ LANGUAGE SQL;
625
626 CREATE TABLE actor.usr_standing_penalty (
627         id                      SERIAL  PRIMARY KEY,
628         org_unit                INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
629         usr                     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
630         standing_penalty        INT     NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
631         staff                   INT     REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
632         set_date                TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
633         stop_date               TIMESTAMP WITH TIME ZONE,
634         note                    TEXT
635 );
636 COMMENT ON TABLE actor.usr_standing_penalty IS $$
637 User standing penalties
638 $$;
639
640 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
641 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
642
643
644 CREATE TABLE actor.usr_saved_search (
645     id              SERIAL          PRIMARY KEY,
646         owner           INT             NOT NULL REFERENCES actor.usr (id)
647                                         ON DELETE CASCADE
648                                         DEFERRABLE INITIALLY DEFERRED,
649         name            TEXT            NOT NULL,
650         create_date     TIMESTAMPTZ     NOT NULL DEFAULT now(),
651         query_text      TEXT            NOT NULL,
652         query_type      TEXT            NOT NULL
653                                         CONSTRAINT valid_query_text CHECK (
654                                         query_type IN ( 'URL' )) DEFAULT 'URL',
655                                         -- we may add other types someday
656         target          TEXT            NOT NULL
657                                         CONSTRAINT valid_target CHECK (
658                                         target IN ( 'record', 'metarecord', 'callnumber' )),
659         CONSTRAINT name_once_per_user UNIQUE (owner, name)
660 );
661
662 CREATE TABLE actor.address_alert (
663     id              SERIAL  PRIMARY KEY,
664     owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
665     active          BOOL    NOT NULL DEFAULT TRUE,
666     match_all       BOOL    NOT NULL DEFAULT TRUE,
667     alert_message   TEXT    NOT NULL,
668     street1         TEXT,
669     street2         TEXT,
670     city            TEXT,
671     county          TEXT,
672     state           TEXT,
673     country         TEXT,
674     post_code       TEXT,
675     mailing_address BOOL    NOT NULL DEFAULT FALSE,
676     billing_address BOOL    NOT NULL DEFAULT FALSE
677 );
678
679 CREATE TABLE actor.usr_activity (
680     id          BIGSERIAL   PRIMARY KEY,
681     usr         INT         REFERENCES actor.usr (id) ON DELETE SET NULL,
682     etype       INT         NOT NULL REFERENCES config.usr_activity_type (id),
683     event_time  TIMESTAMPTZ NOT NULL DEFAULT NOW()
684 );
685 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
686
687 CREATE TABLE actor.toolbar (
688     id          BIGSERIAL   PRIMARY KEY,
689     ws          INT         REFERENCES actor.workstation (id) ON DELETE CASCADE,
690     org         INT         REFERENCES actor.org_unit (id) ON DELETE CASCADE,
691     usr         INT         REFERENCES actor.usr (id) ON DELETE CASCADE,
692     label       TEXT        NOT NULL,
693     layout      TEXT        NOT NULL,
694     CONSTRAINT only_one_type CHECK (
695         (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
696         (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
697         (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
698     ),
699     CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
700 );
701 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
702 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
703 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
704
705 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
706 CREATE TABLE actor.org_unit_custom_tree (
707     id              SERIAL  PRIMARY KEY,
708     active          BOOLEAN DEFAULT FALSE,
709     purpose         actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
710 );
711
712 CREATE TABLE actor.org_unit_custom_tree_node (
713     id              SERIAL  PRIMARY KEY,
714     tree            INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
715         org_unit        INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
716         parent_node     INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
717     sibling_order   INTEGER NOT NULL DEFAULT 0,
718     CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
719 );
720
721 CREATE TABLE actor.search_query (
722     id          SERIAL PRIMARY KEY, 
723     label       TEXT NOT NULL, -- i18n
724     query_text  TEXT NOT NULL -- QP text
725 );
726
727 CREATE TABLE actor.search_filter_group (
728     id          SERIAL      PRIMARY KEY,
729     owner       INT         NOT NULL REFERENCES actor.org_unit (id) 
730                             ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
731     code        TEXT        NOT NULL, -- for CGI, etc.
732     label       TEXT        NOT NULL, -- i18n
733     create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
734     CONSTRAINT  asfg_label_once_per_org UNIQUE (owner, label),
735     CONSTRAINT  asfg_code_once_per_org UNIQUE (owner, code)
736 );
737
738 CREATE TABLE actor.search_filter_group_entry (
739     id          SERIAL  PRIMARY KEY,
740     grp         INT     NOT NULL REFERENCES actor.search_filter_group(id) 
741                         ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
742     pos         INT     NOT NULL DEFAULT 0,
743     query       INT     NOT NULL REFERENCES actor.search_query(id) 
744                         ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
745     CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
746 );
747
748 CREATE TABLE actor.usr_message (
749         id              SERIAL                          PRIMARY KEY,
750         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
751         title           TEXT,                                      
752         message         TEXT                            NOT NULL,
753         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
754         deleted         BOOL                            NOT NULL DEFAULT FALSE,
755         read_date       TIMESTAMP WITH TIME ZONE,
756         sending_lib     INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
757 );
758 CREATE INDEX aum_usr ON actor.usr_message (usr);
759
760 CREATE RULE protect_usr_message_delete AS
761         ON DELETE TO actor.usr_message DO INSTEAD (
762                 UPDATE  actor.usr_message
763                   SET   deleted = TRUE
764                   WHERE OLD.id = actor.usr_message.id
765         );
766
767 CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$
768 DECLARE
769         sending_ou INTEGER;
770 BEGIN
771         IF NEW.pub THEN
772                 IF TG_OP = 'UPDATE' THEN
773                         IF OLD.pub = TRUE THEN
774                                 RETURN NEW;
775                         END IF;
776                 END IF;
777
778                 SELECT INTO sending_ou aw.owning_lib
779                 FROM auditor.get_audit_info() agai
780                 JOIN actor.workstation aw ON (aw.id = agai.eg_ws);
781                 IF sending_ou IS NULL THEN
782                         SELECT INTO sending_ou home_ou
783                         FROM actor.usr
784                         WHERE id = NEW.creator;
785                 END IF;
786                 INSERT INTO actor.usr_message (usr, title, message, sending_lib)
787                         VALUES (NEW.usr, NEW.title, NEW.value, sending_ou);
788         END IF;
789
790         RETURN NEW;
791 END;
792 $$ LANGUAGE PLPGSQL;
793
794 CREATE TRIGGER convert_usr_note_to_message_tgr
795         AFTER INSERT OR UPDATE ON actor.usr_note
796         FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message();
797
798 -- limited view to ensure that a library user who somehow
799 -- manages to figure out how to access pcrud cannot change
800 -- the text of messages sent them
801 CREATE VIEW actor.usr_message_limited
802 AS SELECT * FROM actor.usr_message;
803
804 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
805 BEGIN
806     IF TG_OP = 'UPDATE' THEN
807         UPDATE actor.usr_message
808         SET    read_date = NEW.read_date,
809                deleted   = NEW.deleted
810         WHERE  id = NEW.id;
811         RETURN NEW;
812     END IF;
813     RETURN NULL;
814 END;
815 $$ LANGUAGE PLPGSQL;
816
817 CREATE TRIGGER restrict_usr_message_limited_tgr
818     INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
819     FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
820
821 CREATE TABLE actor.passwd_type (
822     code        TEXT PRIMARY KEY,
823     name        TEXT UNIQUE NOT NULL,
824     login       BOOLEAN NOT NULL DEFAULT FALSE,
825     regex       TEXT,   -- pending
826     crypt_algo  TEXT,   -- e.g. 'bf'
827
828     -- gen_salt() iter count used with each new salt.
829     -- A non-NULL value for iter_count is our indication the 
830     -- password is salted and encrypted via crypt()
831     iter_count  INTEGER CHECK (iter_count IS NULL OR iter_count > 0)
832 );
833
834 CREATE TABLE actor.passwd (
835     id          SERIAL PRIMARY KEY,
836     usr         INTEGER NOT NULL REFERENCES actor.usr(id)
837                 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
838     salt        TEXT, -- will be NULL for non-crypt'ed passwords
839     passwd      TEXT NOT NULL,
840     passwd_type TEXT NOT NULL REFERENCES actor.passwd_type(code)
841                 DEFERRABLE INITIALLY DEFERRED,
842     create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
843     edit_date   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
844     CONSTRAINT  passwd_type_once_per_user UNIQUE (usr, passwd_type)
845 );
846
847 CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT)
848     RETURNS TEXT AS $$
849 DECLARE
850     type_row actor.passwd_type%ROWTYPE;
851 BEGIN
852     /* Returns a new salt based on the passwd_type encryption settings.
853      * Returns NULL If the password type is not crypt()'ed.
854      */
855
856     SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;
857
858     IF NOT FOUND THEN
859         RETURN EXCEPTION 'No such password type: %', pw_type;
860     END IF;
861
862     IF type_row.iter_count IS NULL THEN
863         -- This password type is unsalted.  That's OK.
864         RETURN NULL;
865     END IF;
866
867     RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
868 END;
869 $$ LANGUAGE PLPGSQL;
870
871
872 /* 
873     TODO: when a user changes their password in the application, the
874     app layer has access to the bare password.  At that point, we have
875     the opportunity to store the new password without the MD5(MD5())
876     intermediate hashing.  Do we care?  We would need a way to indicate
877     which passwords have the legacy intermediate hashing and which don't
878     so the app layer would know whether it should perform the intermediate
879     hashing.  In either event, with the exception of migrate_passwd(), the
880     DB functions know or care nothing about intermediate hashing.  Every
881     password is just a value that may or may not be internally crypt'ed. 
882 */
883
884 CREATE OR REPLACE FUNCTION actor.set_passwd(
885     pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL)
886     RETURNS BOOLEAN AS $$
887 DECLARE
888     pw_salt TEXT;
889     pw_text TEXT;
890 BEGIN
891     /* Sets the password value, creating a new actor.passwd row if needed.
892      * If the password type supports it, the new_pass value is crypt()'ed.
893      * For crypt'ed passwords, the salt comes from one of 3 places in order:
894      * new_salt (if present), existing salt (if present), newly created 
895      * salt.
896      */
897
898     IF new_salt IS NOT NULL THEN
899         pw_salt := new_salt;
900     ELSE 
901         pw_salt := actor.get_salt(pw_usr, pw_type);
902
903         IF pw_salt IS NULL THEN
904             /* We have no salt for this user + type.  Assume they want a 
905              * new salt.  If this type is unsalted, create_salt() will 
906              * return NULL. */
907             pw_salt := actor.create_salt(pw_type);
908         END IF;
909     END IF;
910
911     IF pw_salt IS NULL THEN 
912         pw_text := new_pass; -- unsalted, use as-is.
913     ELSE
914         pw_text := CRYPT(new_pass, pw_salt);
915     END IF;
916
917     UPDATE actor.passwd 
918         SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
919         WHERE usr = pw_usr AND passwd_type = pw_type;
920
921     IF NOT FOUND THEN
922         -- no password row exists for this user + type.  Create one.
923         INSERT INTO actor.passwd (usr, passwd_type, salt, passwd) 
924             VALUES (pw_usr, pw_type, pw_salt, pw_text);
925     END IF;
926
927     RETURN TRUE;
928 END;
929 $$ LANGUAGE PLPGSQL;
930
931 CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT)
932     RETURNS TEXT AS $$
933 DECLARE
934     pw_salt TEXT;
935     type_row actor.passwd_type%ROWTYPE;
936 BEGIN
937     /* Returns the salt for the requested user + type.  If the password 
938      * type of "main" is requested and no password exists in actor.passwd, 
939      * the user's existing password is migrated and the new salt is returned.
940      * Returns NULL if the password type is not crypt'ed (iter_count is NULL).
941      */
942
943     SELECT INTO pw_salt salt FROM actor.passwd 
944         WHERE usr = pw_usr AND passwd_type = pw_type;
945
946     IF FOUND THEN
947         RETURN pw_salt;
948     END IF;
949
950     IF pw_type = 'main' THEN
951         -- Main password has not yet been migrated. 
952         -- Do it now and return the newly created salt.
953         RETURN actor.migrate_passwd(pw_usr);
954     END IF;
955
956     -- We have no salt to return.  actor.create_salt() needed.
957     RETURN NULL;
958 END;
959 $$ LANGUAGE PLPGSQL;
960
961 CREATE OR REPLACE FUNCTION 
962     actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$
963 DECLARE
964     pw_salt TEXT;
965     usr_row actor.usr%ROWTYPE;
966 BEGIN
967     /* Migrates legacy actor.usr.passwd value to actor.passwd with 
968      * a password type 'main' and returns the new salt.  For backwards
969      * compatibility with existing CHAP-style API's, we perform a 
970      * layer of intermediate MD5(MD5()) hashing.  This is intermediate
971      * hashing is not required of other passwords.
972      */
973
974     -- Avoid calling get_salt() here, because it may result in a 
975     -- migrate_passwd() call, creating a loop.
976     SELECT INTO pw_salt salt FROM actor.passwd 
977         WHERE usr = pw_usr AND passwd_type = 'main';
978
979     -- Only migrate passwords that have not already been migrated.
980     IF FOUND THEN
981         RETURN pw_salt;
982     END IF;
983
984     SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
985
986     pw_salt := actor.create_salt('main');
987
988     PERFORM actor.set_passwd(
989         pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
990
991     -- clear the existing password
992     UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
993
994     RETURN pw_salt;
995 END;
996 $$ LANGUAGE PLPGSQL;
997
998 CREATE OR REPLACE FUNCTION 
999     actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT) 
1000     RETURNS BOOLEAN AS $$
1001 DECLARE
1002     pw_salt TEXT;
1003 BEGIN
1004     /* Returns TRUE if the password provided matches the in-db password.  
1005      * If the password type is salted, we compare the output of CRYPT().
1006      * NOTE: test_passwd is MD5(salt || MD5(password)) for legacy 
1007      * 'main' passwords.
1008      */
1009
1010     SELECT INTO pw_salt salt FROM actor.passwd 
1011         WHERE usr = pw_usr AND passwd_type = pw_type;
1012
1013     IF NOT FOUND THEN
1014         -- no such password
1015         RETURN FALSE;
1016     END IF;
1017
1018     IF pw_salt IS NULL THEN
1019         -- Password is unsalted, compare the un-CRYPT'ed values.
1020         RETURN EXISTS (
1021             SELECT TRUE FROM actor.passwd WHERE 
1022                 usr = pw_usr AND
1023                 passwd_type = pw_type AND
1024                 passwd = test_passwd
1025         );
1026     END IF;
1027
1028     RETURN EXISTS (
1029         SELECT TRUE FROM actor.passwd WHERE 
1030             usr = pw_usr AND
1031             passwd_type = pw_type AND
1032             passwd = CRYPT(test_passwd, pw_salt)
1033     );
1034 END;
1035 $$ STRICT LANGUAGE PLPGSQL;
1036
1037 -- Remove all activity entries by activity type, 
1038 -- except the most recent entry per user. 
1039 CREATE OR REPLACE FUNCTION
1040     actor.purge_usr_activity_by_type(act_type INTEGER)
1041     RETURNS VOID AS $$
1042 DECLARE
1043     cur_usr INTEGER;
1044 BEGIN
1045     FOR cur_usr IN SELECT DISTINCT(usr)
1046         FROM actor.usr_activity WHERE etype = act_type LOOP
1047         DELETE FROM actor.usr_activity WHERE id IN (
1048             SELECT id
1049             FROM actor.usr_activity
1050             WHERE usr = cur_usr AND etype = act_type
1051             ORDER BY event_time DESC OFFSET 1
1052         );
1053
1054     END LOOP;
1055 END $$ LANGUAGE PLPGSQL;
1056
1057 CREATE TABLE actor.workstation_setting (
1058     id          SERIAL PRIMARY KEY,
1059     workstation INT    NOT NULL REFERENCES actor.workstation (id) 
1060                        ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1061     name        TEXT   NOT NULL REFERENCES config.workstation_setting_type (name) 
1062                        ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1063     value       JSON   NOT NULL
1064 );
1065
1066 CREATE INDEX actor_workstation_setting_workstation_idx 
1067     ON actor.workstation_setting (workstation);
1068
1069 CREATE TYPE actor.cascade_setting_summary AS (
1070     name TEXT,
1071     value JSON,
1072     has_org_setting BOOLEAN,
1073     has_user_setting BOOLEAN,
1074     has_workstation_setting BOOLEAN
1075 );
1076
1077 CREATE OR REPLACE FUNCTION actor.get_cascade_setting(
1078     setting_name TEXT, org_id INT, user_id INT, workstation_id INT) 
1079     RETURNS actor.cascade_setting_summary AS
1080 $FUNC$
1081 DECLARE
1082     setting_value JSON;
1083     summary actor.cascade_setting_summary;
1084     org_setting_type config.org_unit_setting_type%ROWTYPE;
1085 BEGIN
1086
1087     summary.name := setting_name;
1088
1089     -- Collect the org setting type status first in case we exit early.
1090     -- The existance of an org setting type is not considered
1091     -- privileged information.
1092     SELECT INTO org_setting_type * 
1093         FROM config.org_unit_setting_type WHERE name = setting_name;
1094     IF FOUND THEN
1095         summary.has_org_setting := TRUE;
1096     ELSE
1097         summary.has_org_setting := FALSE;
1098     END IF;
1099
1100     -- User and workstation settings have the same priority.
1101     -- Start with user settings since that's the simplest code path.
1102     -- The workstation_id is ignored if no user_id is provided.
1103     IF user_id IS NOT NULL THEN
1104
1105         SELECT INTO summary.value value FROM actor.usr_setting
1106             WHERE usr = user_id AND name = setting_name;
1107
1108         IF FOUND THEN
1109             -- if we have a value, we have a setting type
1110             summary.has_user_setting := TRUE;
1111
1112             IF workstation_id IS NOT NULL THEN
1113                 -- Only inform the caller about the workstation
1114                 -- setting type disposition when a workstation id is
1115                 -- provided.  Otherwise, it's NULL to indicate UNKNOWN.
1116                 summary.has_workstation_setting := FALSE;
1117             END IF;
1118
1119             RETURN summary;
1120         END IF;
1121
1122         -- no user setting value, but a setting type may exist
1123         SELECT INTO summary.has_user_setting EXISTS (
1124             SELECT TRUE FROM config.usr_setting_type 
1125             WHERE name = setting_name
1126         );
1127
1128         IF workstation_id IS NOT NULL THEN 
1129
1130             IF NOT summary.has_user_setting THEN
1131                 -- A workstation setting type may only exist when a user
1132                 -- setting type does not.
1133
1134                 SELECT INTO summary.value value 
1135                     FROM actor.workstation_setting         
1136                     WHERE workstation = workstation_id AND name = setting_name;
1137
1138                 IF FOUND THEN
1139                     -- if we have a value, we have a setting type
1140                     summary.has_workstation_setting := TRUE;
1141                     RETURN summary;
1142                 END IF;
1143
1144                 -- no value, but a setting type may exist
1145                 SELECT INTO summary.has_workstation_setting EXISTS (
1146                     SELECT TRUE FROM config.workstation_setting_type 
1147                     WHERE name = setting_name
1148                 );
1149             END IF;
1150
1151             -- Finally make use of the workstation to determine the org
1152             -- unit if none is provided.
1153             IF org_id IS NULL AND summary.has_org_setting THEN
1154                 SELECT INTO org_id owning_lib 
1155                     FROM actor.workstation WHERE id = workstation_id;
1156             END IF;
1157         END IF;
1158     END IF;
1159
1160     -- Some org unit settings are protected by a view permission.
1161     -- First see if we have any data that needs protecting, then 
1162     -- check the permission if needed.
1163
1164     IF NOT summary.has_org_setting THEN
1165         RETURN summary;
1166     END IF;
1167
1168     -- avoid putting the value into the summary until we confirm
1169     -- the value should be visible to the caller.
1170     SELECT INTO setting_value value 
1171         FROM actor.org_unit_ancestor_setting(setting_name, org_id);
1172
1173     IF NOT FOUND THEN
1174         -- No value found -- perm check is irrelevant.
1175         RETURN summary;
1176     END IF;
1177
1178     IF org_setting_type.view_perm IS NOT NULL THEN
1179
1180         IF user_id IS NULL THEN
1181             RAISE NOTICE 'Perm check required but no user_id provided';
1182             RETURN summary;
1183         END IF;
1184
1185         IF NOT permission.usr_has_perm(
1186             user_id, (SELECT code FROM permission.perm_list 
1187                 WHERE id = org_setting_type.view_perm), org_id) 
1188         THEN
1189             RAISE NOTICE 'Perm check failed for user % on %',
1190                 user_id, org_setting_type.view_perm;
1191             RETURN summary;
1192         END IF;
1193     END IF;
1194
1195     -- Perm check succeeded or was not necessary.
1196     summary.value := setting_value;
1197     RETURN summary;
1198 END;
1199 $FUNC$ LANGUAGE PLPGSQL;
1200
1201
1202 CREATE OR REPLACE FUNCTION actor.get_cascade_setting_batch(
1203     setting_names TEXT[], org_id INT, user_id INT, workstation_id INT) 
1204     RETURNS SETOF actor.cascade_setting_summary AS
1205 $FUNC$
1206 -- Returns a row per setting matching the setting name order.  If no 
1207 -- value is applied, NULL is returned to retain name-response ordering.
1208 DECLARE
1209     setting_name TEXT;
1210     summary actor.cascade_setting_summary;
1211 BEGIN
1212     FOREACH setting_name IN ARRAY setting_names LOOP
1213         SELECT INTO summary * FROM actor.get_cascade_setting(
1214             setting_Name, org_id, user_id, workstation_id);
1215         RETURN NEXT summary;
1216     END LOOP;
1217 END;
1218 $FUNC$ LANGUAGE PLPGSQL;
1219
1220
1221 COMMIT;