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_usrname_idx ON actor.usr (evergreen.lowercase(usrname));
  87 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
  88
  89 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
  90 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
  91 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
  92
  93 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
  94     (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
  95
  96 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
  97     (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
  98
  99 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
 100     (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
 101
 102 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
 103 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
 104
 105 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
 106         BEGIN
 107                 NEW.passwd = MD5( NEW.passwd );
 108                 RETURN NEW;
 109         END;
 110 $$ LANGUAGE PLPGSQL;
 111
 112 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
 113         BEGIN
 114                 IF NEW.passwd <> OLD.passwd THEN
 115                         NEW.passwd = MD5( NEW.passwd );
 116                 END IF;
 117                 RETURN NEW;
 118         END;
 119 $$ LANGUAGE PLPGSQL;
 120
 121 CREATE OR REPLACE FUNCTION actor.au_updated()
 122 RETURNS TRIGGER AS $$
 123 BEGIN
 124     NEW.last_update_time := now();
 125         RETURN NEW;
 126 END;
 127 $$ LANGUAGE plpgsql;
 128
 129 CREATE TRIGGER au_update_trig
 130         BEFORE INSERT OR UPDATE ON actor.usr
 131         FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
 132
 133 CREATE TRIGGER actor_crypt_pw_update_trigger
 134         BEFORE UPDATE ON actor.usr FOR EACH ROW
 135         EXECUTE PROCEDURE actor.crypt_pw_update ();
 136
 137 CREATE TRIGGER actor_crypt_pw_insert_trigger
 138         BEFORE INSERT ON actor.usr FOR EACH ROW
 139         EXECUTE PROCEDURE actor.crypt_pw_insert ();
 140
 141 CREATE RULE protect_user_delete AS ON DELETE TO actor.usr DO INSTEAD UPDATE actor.usr SET deleted = TRUE WHERE OLD.id = actor.usr.id;
 142
 143 CREATE TABLE actor.usr_note (
 144         id              BIGSERIAL                       PRIMARY KEY,
 145         usr             BIGINT                          NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 146         creator         BIGINT                          NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 147         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 148         pub             BOOL                            NOT NULL DEFAULT FALSE,
 149         title           TEXT                            NOT NULL,
 150         value           TEXT                            NOT NULL
 151 );
 152 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
 153 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
 154
 155 CREATE TABLE actor.usr_message (
 156         id              SERIAL                          PRIMARY KEY,
 157         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 158         title           TEXT,
 159         message         TEXT                            NOT NULL,
 160         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 161         deleted         BOOL                            NOT NULL DEFAULT FALSE,
 162         read_date       TIMESTAMP WITH TIME ZONE,
 163         sending_lib     INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 164 );
 165 CREATE INDEX aum_usr ON actor.usr_message (usr);
 166
 167 CREATE RULE protect_usr_message_delete AS
 168         ON DELETE TO actor.usr_message DO INSTEAD (
 169                 UPDATE  actor.usr_message
 170                   SET   deleted = TRUE
 171                   WHERE OLD.id = actor.usr_message.id
 172         );
 173
 174 CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$
 175 BEGIN
 176         IF NEW.pub THEN
 177                 IF TG_OP = 'UPDATE' THEN
 178                         IF OLD.pub = TRUE THEN
 179                                 RETURN NEW;
 180                         END IF;
 181                 END IF;
 182
 183                 INSERT INTO actor.usr_message (usr, title, message, sending_lib)
 184                         VALUES (NEW.usr, NEW.title, NEW.value, (SELECT home_ou FROM actor.usr WHERE id = NEW.creator));
 185         END IF;
 186
 187         RETURN NEW;
 188 END;
 189 $$ LANGUAGE PLPGSQL;
 190
 191 CREATE TRIGGER actor.convert_usr_note_to_message_tgr
 192         AFTER INSERT OR UPDATE ON actor.usr_note
 193         FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message();
 194
 195 -- limited view to ensure that a library user who somehow
 196 -- manages to figure out how to access pcrud cannot change
 197 -- the text of messages sent them
 198 CREATE VIEW actor.usr_message_limited
 199 AS SELECT * FROM actor.usr_message;
 200
 201 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
 202 BEGIN
 203     IF TG_OP = 'UPDATE' THEN
 204         UPDATE actor.usr_message
 205         SET    read_date = NEW.read_date,
 206                deleted   = NEW.deleted
 207         WHERE  id = NEW.id;
 208         RETURN NEW;
 209     END IF;
 210     RETURN NULL;
 211 END;
 212 $$ LANGUAGE PLPGSQL;
 213
 214 CREATE TRIGGER restrict_usr_message_limited_tgr
 215     INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
 216     FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
 217
 218 CREATE TABLE actor.usr_setting (
 219         id      BIGSERIAL       PRIMARY KEY,
 220         usr     INT             NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 221         name    TEXT            NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 222         value   TEXT            NOT NULL,
 223         CONSTRAINT usr_once_per_key UNIQUE (usr,name)
 224 );
 225 COMMENT ON TABLE actor.usr_setting IS $$
 226 User settings
 227
 228 This table contains any arbitrary settings that a client
 229 program would like to save for a user.
 230 $$;
 231
 232 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
 233
 234 CREATE TABLE actor.stat_cat_sip_fields (
 235     field   CHAR(2) PRIMARY KEY,
 236     name    TEXT    NOT NULL,
 237     one_only  BOOL    NOT NULL DEFAULT FALSE
 238 );
 239 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
 240 Actor Statistical Category SIP Fields
 241
 242 Contains the list of valid SIP Field identifiers for
 243 Statistical Categories.
 244 $$;
 245
 246 CREATE TABLE actor.stat_cat (
 247         id              SERIAL  PRIMARY KEY,
 248         owner           INT     NOT NULL,
 249         name            TEXT    NOT NULL,
 250         opac_visible    BOOL NOT NULL DEFAULT FALSE,
 251         usr_summary     BOOL NOT NULL DEFAULT FALSE,
 252     sip_field   CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 253     sip_format  TEXT,
 254     checkout_archive    BOOL NOT NULL DEFAULT FALSE,
 255         required        BOOL NOT NULL DEFAULT FALSE,
 256         allow_freetext  BOOL NOT NULL DEFAULT TRUE,
 257         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
 258 );
 259 COMMENT ON TABLE actor.stat_cat IS $$
 260 User Statistical Catagories
 261
 262 Local data collected about Users is placed into a Statistical
 263 Catagory.  Here's where those catagories are defined.
 264 $$;
 265
 266
 267 CREATE TABLE actor.stat_cat_entry (
 268         id              SERIAL  PRIMARY KEY,
 269         stat_cat        INT     NOT NULL,
 270         owner           INT     NOT NULL,
 271         value           TEXT    NOT NULL,
 272         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
 273 );
 274 COMMENT ON TABLE actor.stat_cat_entry IS $$
 275 User Statistical Catagory Entries
 276
 277 Local data collected about Users is placed into a Statistical
 278 Catagory.  Each library can create entries into any of its own
 279 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
 280 $$;
 281
 282
 283 CREATE TABLE actor.stat_cat_entry_usr_map (
 284         id              BIGSERIAL       PRIMARY KEY,
 285         stat_cat_entry  TEXT            NOT NULL,
 286         stat_cat        INT             NOT NULL,
 287         target_usr      INT             NOT NULL,
 288         CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
 289 );
 290 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
 291 Statistical Catagory Entry to User map
 292
 293 Records the stat_cat entries for each user.
 294 $$;
 295
 296 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
 297
 298 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
 299 DECLARE
 300     sipfield actor.stat_cat_sip_fields%ROWTYPE;
 301     use_count INT;
 302 BEGIN
 303     IF NEW.sip_field IS NOT NULL THEN
 304         SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
 305         IF sipfield.one_only THEN
 306             SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
 307             IF use_count > 0 THEN
 308                 RAISE EXCEPTION 'Sip field cannot be used twice';
 309             END IF;
 310         END IF;
 311     END IF;
 312     RETURN NEW;
 313 END;
 314 $func$ LANGUAGE PLPGSQL;
 315
 316 CREATE TRIGGER actor_stat_cat_sip_update_trigger
 317     BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
 318     EXECUTE PROCEDURE actor.stat_cat_check();
 319
 320 CREATE TABLE actor.card (
 321         id      SERIAL  PRIMARY KEY,
 322         usr     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 323         barcode TEXT    NOT NULL UNIQUE,
 324         active  BOOL    NOT NULL DEFAULT TRUE
 325 );
 326 COMMENT ON TABLE actor.card IS $$
 327 Library Cards
 328
 329 Each User has one or more library cards.  The current "main"
 330 card is linked to here from the actor.usr table, and it is up
 331 to the consortium policy whether more than one card can be
 332 active for any one user at a given time.
 333 $$;
 334
 335 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
 336 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
 337
 338 CREATE TABLE actor.org_unit_type (
 339         id              SERIAL  PRIMARY KEY,
 340         name            TEXT    NOT NULL,
 341         opac_label      TEXT    NOT NULL,
 342         depth           INT     NOT NULL,
 343         parent          INT     REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
 344         can_have_vols   BOOL    NOT NULL DEFAULT TRUE,
 345         can_have_users  BOOL    NOT NULL DEFAULT TRUE
 346 );
 347 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
 348
 349 CREATE TABLE actor.org_unit (
 350         id              SERIAL  PRIMARY KEY,
 351         parent_ou       INT     REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 352         ou_type         INT     NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
 353         ill_address     INT,
 354         holds_address   INT,
 355         mailing_address INT,
 356         billing_address INT,
 357         shortname       TEXT    NOT NULL UNIQUE,
 358         name            TEXT    NOT NULL UNIQUE,
 359         email           TEXT,
 360         phone           TEXT,
 361         opac_visible    BOOL    NOT NULL DEFAULT TRUE,
 362         fiscal_calendar INT     NOT NULL DEFAULT 1   -- foreign key constraint to be added later
 363 );
 364 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
 365 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
 366 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
 367 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
 368 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
 369 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
 370
 371 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
 372         DECLARE
 373                 current_aou actor.org_unit%ROWTYPE;
 374                 seen_ous    INT[];
 375                 depth_count INT;
 376         BEGIN
 377                 current_aou := NEW;
 378                 depth_count := 0;
 379                 seen_ous := ARRAY[NEW.id];
 380
 381                 IF (TG_OP = 'UPDATE') THEN
 382                         IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
 383                                 RETURN NEW; -- Doing an UPDATE with no change, just return it
 384                         END IF;
 385                 END IF;
 386
 387                 LOOP
 388                         IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
 389                                 RETURN NEW; -- No loop. Carry on.
 390                         END IF;
 391                         IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
 392                                 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
 393                         END IF;
 394                         -- Get the next one!
 395                         SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
 396                         seen_ous := seen_ous || current_aou.id;
 397                         depth_count := depth_count + 1;
 398                         IF depth_count = 100 THEN
 399                                 RAISE 'OU CHECK TOO DEEP';
 400                         END IF;
 401                 END LOOP;
 402
 403                 RETURN NEW;
 404         END;
 405 $$ LANGUAGE PLPGSQL;
 406
 407 CREATE TRIGGER actor_org_unit_parent_protect_trigger
 408     BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
 409     EXECUTE PROCEDURE actor.org_unit_parent_protect ();
 410
 411 CREATE TABLE actor.org_lasso (
 412     id      SERIAL  PRIMARY KEY,
 413     name        TEXT    UNIQUE
 414 );
 415
 416 CREATE TABLE actor.org_lasso_map (
 417     id          SERIAL  PRIMARY KEY,
 418     lasso       INT     NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 419     org_unit    INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 420 );
 421 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
 422 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
 423
 424 CREATE TABLE actor.org_unit_proximity (
 425         id              BIGSERIAL       PRIMARY KEY,
 426         from_org        INT,
 427         to_org          INT,
 428         prox            INT
 429 );
 430 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
 431
 432 CREATE TABLE actor.stat_cat_entry_default (
 433         id              SERIAL  PRIMARY KEY,
 434         stat_cat_entry  INT     NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 435         stat_cat        INT     NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 436         owner           INT     NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 437         CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
 438 );
 439 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
 440 User Statistical Category Default Entry
 441
 442 A library may choose one of the stat_cat entries to be the
 443 default entry.
 444 $$;
 445
 446
 447 CREATE TABLE actor.org_unit_proximity_adjustment (
 448     id                  SERIAL   PRIMARY KEY,
 449     item_circ_lib       INT         REFERENCES actor.org_unit (id),
 450     item_owning_lib     INT         REFERENCES actor.org_unit (id),
 451     copy_location       INT,        -- REFERENCES asset.copy_location (id),
 452     hold_pickup_lib     INT         REFERENCES actor.org_unit (id),
 453     hold_request_lib    INT         REFERENCES actor.org_unit (id),
 454     pos                 INT         NOT NULL DEFAULT 0,
 455     absolute_adjustment BOOL        NOT NULL DEFAULT FALSE,
 456     prox_adjustment     NUMERIC,
 457     circ_mod            TEXT,       -- REFERENCES config.circ_modifier (code),
 458     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)
 459 );
 460 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
 461     COALESCE(item_circ_lib, -1),
 462     COALESCE(item_owning_lib, -1),
 463     COALESCE(copy_location, -1),
 464     COALESCE(hold_pickup_lib, -1),
 465     COALESCE(hold_request_lib, -1),
 466     COALESCE(circ_mod, ''),
 467     pos
 468 );
 469 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
 470 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
 471 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
 472 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
 473 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
 474 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
 475
 476 CREATE TABLE actor.hours_of_operation (
 477         id              INT     PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 478         dow_0_open      TIME    NOT NULL DEFAULT '09:00',
 479         dow_0_close     TIME    NOT NULL DEFAULT '17:00',
 480         dow_1_open      TIME    NOT NULL DEFAULT '09:00',
 481         dow_1_close     TIME    NOT NULL DEFAULT '17:00',
 482         dow_2_open      TIME    NOT NULL DEFAULT '09:00',
 483         dow_2_close     TIME    NOT NULL DEFAULT '17:00',
 484         dow_3_open      TIME    NOT NULL DEFAULT '09:00',
 485         dow_3_close     TIME    NOT NULL DEFAULT '17:00',
 486         dow_4_open      TIME    NOT NULL DEFAULT '09:00',
 487         dow_4_close     TIME    NOT NULL DEFAULT '17:00',
 488         dow_5_open      TIME    NOT NULL DEFAULT '09:00',
 489         dow_5_close     TIME    NOT NULL DEFAULT '17:00',
 490         dow_6_open      TIME    NOT NULL DEFAULT '09:00',
 491         dow_6_close     TIME    NOT NULL DEFAULT '17:00'
 492 );
 493 COMMENT ON TABLE actor.hours_of_operation IS $$
 494 When does this org_unit usually open and close?  (Variations
 495 are expressed in the actor.org_unit_closed table.)
 496 $$;
 497 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
 498 When does this org_unit open on Monday?
 499 $$;
 500 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
 501 When does this org_unit close on Monday?
 502 $$;
 503 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
 504 When does this org_unit open on Tuesday?
 505 $$;
 506 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
 507 When does this org_unit close on Tuesday?
 508 $$;
 509 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
 510 When does this org_unit open on Wednesday?
 511 $$;
 512 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
 513 When does this org_unit close on Wednesday?
 514 $$;
 515 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
 516 When does this org_unit open on Thursday?
 517 $$;
 518 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
 519 When does this org_unit close on Thursday?
 520 $$;
 521 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
 522 When does this org_unit open on Friday?
 523 $$;
 524 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
 525 When does this org_unit close on Friday?
 526 $$;
 527 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
 528 When does this org_unit open on Saturday?
 529 $$;
 530 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
 531 When does this org_unit close on Saturday?
 532 $$;
 533 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
 534 When does this org_unit open on Sunday?
 535 $$;
 536 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
 537 When does this org_unit close on Sunday?
 538 $$;
 539
 540 CREATE TABLE actor.org_unit_closed (
 541         id              SERIAL                          PRIMARY KEY,
 542         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 543         close_start     TIMESTAMP WITH TIME ZONE        NOT NULL,
 544         close_end       TIMESTAMP WITH TIME ZONE        NOT NULL,
 545         reason          TEXT
 546 );
 547
 548 -- Workstation registration...
 549 CREATE TABLE actor.workstation (
 550         id              SERIAL  PRIMARY KEY,
 551         name            TEXT    NOT NULL UNIQUE,
 552         owning_lib      INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 553 );
 554
 555 CREATE TABLE actor.usr_org_unit_opt_in (
 556         id              SERIAL                          PRIMARY KEY,
 557         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 558         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 559         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 560         opt_in_ts       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 561         opt_in_ws       INT                             NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
 562         CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
 563 );
 564 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
 565
 566 CREATE TABLE actor.org_unit_setting (
 567         id              BIGSERIAL       PRIMARY KEY,
 568         org_unit        INT             NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 569         name            TEXT    NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
 570         value           TEXT            NOT NULL,
 571         CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
 572         CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
 573 );
 574 COMMENT ON TABLE actor.org_unit_setting IS $$
 575 Org Unit settings
 576
 577 This table contains any arbitrary settings that a client
 578 program would like to save for an org unit.
 579 $$;
 580
 581 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
 582
 583 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
 584 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
 585     DECLARE
 586     original TEXT;
 587     BEGIN
 588         -- Check for which setting is being updated, and log it.
 589         SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
 590
 591         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
 592
 593         RETURN NEW;
 594     END;
 595 $ous_change_log$ LANGUAGE plpgsql;
 596
 597 CREATE TRIGGER log_ous_change
 598     BEFORE INSERT OR UPDATE ON actor.org_unit_setting
 599     FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
 600
 601 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
 602     DECLARE
 603     original TEXT;
 604     BEGIN
 605         -- Check for which setting is being updated, and log it.
 606         SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
 607
 608         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
 609
 610         RETURN OLD;
 611     END;
 612 $ous_delete_log$ LANGUAGE plpgsql;
 613
 614 CREATE TRIGGER log_ous_del
 615     BEFORE DELETE ON actor.org_unit_setting
 616     FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
 617
 618
 619
 620
 621 CREATE TABLE actor.usr_address (
 622         id                      SERIAL  PRIMARY KEY,
 623         valid                   BOOL    NOT NULL DEFAULT TRUE,
 624         within_city_limits      BOOL    NOT NULL DEFAULT TRUE,
 625         address_type            TEXT    NOT NULL DEFAULT 'MAILING',
 626         usr                     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 627         street1                 TEXT    NOT NULL,
 628         street2                 TEXT,
 629         city                    TEXT    NOT NULL,
 630         county                  TEXT,
 631         state                   TEXT,
 632         country                 TEXT    NOT NULL,
 633         post_code               TEXT    NOT NULL,
 634     pending         BOOL    NOT NULL DEFAULT FALSE,
 635         replaces            INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
 636 );
 637
 638 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
 639
 640 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
 641 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
 642
 643 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
 644 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
 645 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
 646
 647 CREATE TABLE actor.usr_password_reset (
 648   id SERIAL PRIMARY KEY,
 649   uuid TEXT NOT NULL,
 650   usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
 651   request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 652   has_been_reset BOOL NOT NULL DEFAULT false
 653 );
 654 COMMENT ON TABLE actor.usr_password_reset IS $$
 655 Self-serve password reset requests
 656 $$;
 657 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
 658 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
 659 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
 660 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
 661
 662 CREATE TABLE actor.org_address (
 663         id              SERIAL  PRIMARY KEY,
 664         valid           BOOL    NOT NULL DEFAULT TRUE,
 665         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 666         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 667         street1         TEXT    NOT NULL,
 668         street2         TEXT,
 669         city            TEXT    NOT NULL,
 670         county          TEXT,
 671         state           TEXT,
 672         country         TEXT    NOT NULL,
 673         post_code       TEXT    NOT NULL,
 674     san         TEXT
 675 );
 676
 677 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
 678
 679 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
 680         SELECT SUBSTRING( $1, 1, 5);
 681 $$ LANGUAGE SQL;
 682
 683 CREATE TABLE actor.usr_standing_penalty (
 684         id                      SERIAL  PRIMARY KEY,
 685         org_unit                INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 686         usr                     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 687         standing_penalty        INT     NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 688         staff                   INT     REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 689         set_date                TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 690         stop_date               TIMESTAMP WITH TIME ZONE,
 691         note                    TEXT
 692 );
 693 COMMENT ON TABLE actor.usr_standing_penalty IS $$
 694 User standing penalties
 695 $$;
 696
 697 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
 698 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
 699
 700
 701 CREATE TABLE actor.usr_saved_search (
 702     id              SERIAL          PRIMARY KEY,
 703         owner           INT             NOT NULL REFERENCES actor.usr (id)
 704                                         ON DELETE CASCADE
 705                                         DEFERRABLE INITIALLY DEFERRED,
 706         name            TEXT            NOT NULL,
 707         create_date     TIMESTAMPTZ     NOT NULL DEFAULT now(),
 708         query_text      TEXT            NOT NULL,
 709         query_type      TEXT            NOT NULL
 710                                         CONSTRAINT valid_query_text CHECK (
 711                                         query_type IN ( 'URL' )) DEFAULT 'URL',
 712                                         -- we may add other types someday
 713         target          TEXT            NOT NULL
 714                                         CONSTRAINT valid_target CHECK (
 715                                         target IN ( 'record', 'metarecord', 'callnumber' )),
 716         CONSTRAINT name_once_per_user UNIQUE (owner, name)
 717 );
 718
 719 CREATE TABLE actor.address_alert (
 720     id              SERIAL  PRIMARY KEY,
 721     owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 722     active          BOOL    NOT NULL DEFAULT TRUE,
 723     match_all       BOOL    NOT NULL DEFAULT TRUE,
 724     alert_message   TEXT    NOT NULL,
 725     street1         TEXT,
 726     street2         TEXT,
 727     city            TEXT,
 728     county          TEXT,
 729     state           TEXT,
 730     country         TEXT,
 731     post_code       TEXT,
 732     mailing_address BOOL    NOT NULL DEFAULT FALSE,
 733     billing_address BOOL    NOT NULL DEFAULT FALSE
 734 );
 735
 736 CREATE TABLE actor.usr_activity (
 737     id          BIGSERIAL   PRIMARY KEY,
 738     usr         INT         REFERENCES actor.usr (id) ON DELETE SET NULL,
 739     etype       INT         NOT NULL REFERENCES config.usr_activity_type (id),
 740     event_time  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 741 );
 742 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
 743
 744 CREATE TABLE actor.toolbar (
 745     id          BIGSERIAL   PRIMARY KEY,
 746     ws          INT         REFERENCES actor.workstation (id) ON DELETE CASCADE,
 747     org         INT         REFERENCES actor.org_unit (id) ON DELETE CASCADE,
 748     usr         INT         REFERENCES actor.usr (id) ON DELETE CASCADE,
 749     label       TEXT        NOT NULL,
 750     layout      TEXT        NOT NULL,
 751     CONSTRAINT only_one_type CHECK (
 752         (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
 753         (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
 754         (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
 755     ),
 756     CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
 757 );
 758 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
 759 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
 760 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
 761
 762 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
 763 CREATE TABLE actor.org_unit_custom_tree (
 764     id              SERIAL  PRIMARY KEY,
 765     active          BOOLEAN DEFAULT FALSE,
 766     purpose         actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
 767 );
 768
 769 CREATE TABLE actor.org_unit_custom_tree_node (
 770     id              SERIAL  PRIMARY KEY,
 771     tree            INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
 772         org_unit        INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 773         parent_node     INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
 774     sibling_order   INTEGER NOT NULL DEFAULT 0,
 775     CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
 776 );
 777
 778 CREATE TABLE actor.search_query (
 779     id          SERIAL PRIMARY KEY,
 780     label       TEXT NOT NULL, -- i18n
 781     query_text  TEXT NOT NULL -- QP text
 782 );
 783
 784 CREATE TABLE actor.search_filter_group (
 785     id          SERIAL      PRIMARY KEY,
 786     owner       INT         NOT NULL REFERENCES actor.org_unit (id)
 787                             ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 788     code        TEXT        NOT NULL, -- for CGI, etc.
 789     label       TEXT        NOT NULL, -- i18n
 790     create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
 791     CONSTRAINT  asfg_label_once_per_org UNIQUE (owner, label),
 792     CONSTRAINT  asfg_code_once_per_org UNIQUE (owner, code)
 793 );
 794
 795 CREATE TABLE actor.search_filter_group_entry (
 796     id          SERIAL  PRIMARY KEY,
 797     grp         INT     NOT NULL REFERENCES actor.search_filter_group(id)
 798                         ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 799     pos         INT     NOT NULL DEFAULT 0,
 800     query       INT     NOT NULL REFERENCES actor.search_query(id)
 801                         ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 802     CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
 803 );
 804
 805 COMMIT;