Open-ILS/src/sql/Pg/005.schema.actors.sql

   1 /*
   2  * Copyright (C) 2005-2008  Equinox Software, Inc. / Georgia Public Library Service
   3  * Mike Rylander <mrylander@gmail.com>
   4  * Copyright (C) 2010 Laurentian University
   5  * Dan Scott <dscott@laurentian.ca>
   6  *
   7  * This program is free software; you can redistribute it and/or
   8  * modify it under the terms of the GNU General Public License
   9  * as published by the Free Software Foundation; either version 2
  10  * of the License, or (at your option) any later version.
  11  *
  12  * This program is distributed in the hope that it will be useful,
  13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15  * GNU General Public License for more details.
  16  */
  17
  18 DROP SCHEMA IF EXISTS actor CASCADE;
  19
  20 BEGIN;
  21 CREATE SCHEMA actor;
  22 COMMENT ON SCHEMA actor IS $$
  23 Holds all tables pertaining to users and libraries (org units).
  24 $$;
  25
  26 CREATE TABLE actor.usr (
  27         id                      SERIAL                          PRIMARY KEY,
  28         card                    INT                             UNIQUE, -- active card
  29         profile                 INT                             NOT NULL, -- patron profile
  30         usrname                 TEXT                            NOT NULL UNIQUE,
  31         email                   TEXT,
  32         passwd                  TEXT                            NOT NULL,
  33         standing                INT                             NOT NULL DEFAULT 1 REFERENCES config.standing (id) DEFERRABLE INITIALLY DEFERRED,
  34         ident_type              INT                             NOT NULL REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED,
  35         ident_value             TEXT,
  36         ident_type2             INT                             REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED,
  37         ident_value2            TEXT,
  38         net_access_level        INT                             NOT NULL DEFAULT 1 REFERENCES config.net_access_level (id) DEFERRABLE INITIALLY DEFERRED,
  39         photo_url               TEXT,
  40         prefix                  TEXT,
  41         first_given_name        TEXT                            NOT NULL,
  42         second_given_name       TEXT,
  43         family_name             TEXT                            NOT NULL,
  44         suffix                  TEXT,
  45         alias                   TEXT,
  46         day_phone               TEXT,
  47         evening_phone           TEXT,
  48         other_phone             TEXT,
  49         mailing_address         INT,
  50         billing_address         INT,
  51         home_ou                 INT                             NOT NULL,
  52         dob                     TIMESTAMP WITH TIME ZONE,
  53         active                  BOOL                            NOT NULL DEFAULT TRUE,
  54         master_account          BOOL                            NOT NULL DEFAULT FALSE,
  55         super_user              BOOL                            NOT NULL DEFAULT FALSE,
  56         barred                  BOOL                            NOT NULL DEFAULT FALSE,
  57         deleted                 BOOL                            NOT NULL DEFAULT FALSE,
  58         juvenile                BOOL                            NOT NULL DEFAULT FALSE,
  59         usrgroup                SERIAL                          NOT NULL,
  60         claims_returned_count   INT                             NOT NULL DEFAULT 0,
  61         credit_forward_balance  NUMERIC(6,2)                    NOT NULL DEFAULT 0.00,
  62         last_xact_id            TEXT                            NOT NULL DEFAULT 'none',
  63         alert_message           TEXT,
  64         create_date             TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
  65         expire_date             TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT (now() + '3 years'::INTERVAL),
  66         claims_never_checked_out_count  INT         NOT NULL DEFAULT 0,
  67     last_update_time    TIMESTAMP WITH TIME ZONE
  68 );
  69 COMMENT ON TABLE actor.usr IS $$
  70 User objects
  71
  72 This table contains the core User objects that describe both
  73 staff members and patrons.  The difference between the two
  74 types of users is based on the user's permissions.
  75 $$;
  76
  77 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
  78 CREATE INDEX actor_usr_usrgroup_idx ON actor.usr (usrgroup);
  79 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
  80 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
  81
  82 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name));
  83 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name));
  84 CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name));
  85
  86 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
  87
  88 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
  89 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
  90 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
  91
  92 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
  93     (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
  94
  95 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
  96     (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
  97
  98 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
  99     (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
 100
 101 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
 102 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
 103
 104 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
 105         BEGIN
 106                 NEW.passwd = MD5( NEW.passwd );
 107                 RETURN NEW;
 108         END;
 109 $$ LANGUAGE PLPGSQL;
 110
 111 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
 112         BEGIN
 113                 IF NEW.passwd <> OLD.passwd THEN
 114                         NEW.passwd = MD5( NEW.passwd );
 115                 END IF;
 116                 RETURN NEW;
 117         END;
 118 $$ LANGUAGE PLPGSQL;
 119
 120 CREATE OR REPLACE FUNCTION actor.au_updated()
 121 RETURNS TRIGGER AS $$
 122 BEGIN
 123     NEW.last_update_time := now();
 124         RETURN NEW;
 125 END;
 126 $$ LANGUAGE plpgsql;
 127
 128 CREATE TRIGGER au_update_trig
 129         BEFORE INSERT OR UPDATE ON actor.usr
 130         FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
 131
 132 CREATE TRIGGER actor_crypt_pw_update_trigger
 133         BEFORE UPDATE ON actor.usr FOR EACH ROW
 134         EXECUTE PROCEDURE actor.crypt_pw_update ();
 135
 136 CREATE TRIGGER actor_crypt_pw_insert_trigger
 137         BEFORE INSERT ON actor.usr FOR EACH ROW
 138         EXECUTE PROCEDURE actor.crypt_pw_insert ();
 139
 140 CREATE RULE protect_user_delete AS ON DELETE TO actor.usr DO INSTEAD UPDATE actor.usr SET deleted = TRUE WHERE OLD.id = actor.usr.id;
 141
 142 CREATE TABLE actor.usr_note (
 143         id              BIGSERIAL                       PRIMARY KEY,
 144         usr             BIGINT                          NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 145         creator         BIGINT                          NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 146         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 147         pub             BOOL                            NOT NULL DEFAULT FALSE,
 148         title           TEXT                            NOT NULL,
 149         value           TEXT                            NOT NULL
 150 );
 151 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
 152 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
 153
 154 CREATE TABLE actor.usr_setting (
 155         id      BIGSERIAL       PRIMARY KEY,
 156         usr     INT             NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 157         name    TEXT            NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 158         value   TEXT            NOT NULL,
 159         CONSTRAINT usr_once_per_key UNIQUE (usr,name)
 160 );
 161 COMMENT ON TABLE actor.usr_setting IS $$
 162 User settings
 163
 164 This table contains any arbitrary settings that a client
 165 program would like to save for a user.
 166 $$;
 167
 168 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
 169
 170 CREATE TABLE actor.stat_cat_sip_fields (
 171     field   CHAR(2) PRIMARY KEY,
 172     name    TEXT    NOT NULL,
 173     one_only  BOOL    NOT NULL DEFAULT FALSE
 174 );
 175 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
 176 Actor Statistical Category SIP Fields
 177
 178 Contains the list of valid SIP Field identifiers for
 179 Statistical Categories.
 180 $$;
 181
 182 CREATE TABLE actor.stat_cat (
 183         id              SERIAL  PRIMARY KEY,
 184         owner           INT     NOT NULL,
 185         name            TEXT    NOT NULL,
 186         opac_visible    BOOL NOT NULL DEFAULT FALSE,
 187         usr_summary     BOOL NOT NULL DEFAULT FALSE,
 188     sip_field   CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 189     sip_format  TEXT,
 190     checkout_archive    BOOL NOT NULL DEFAULT FALSE,
 191         required        BOOL NOT NULL DEFAULT FALSE,
 192         allow_freetext  BOOL NOT NULL DEFAULT TRUE,
 193         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
 194 );
 195 COMMENT ON TABLE actor.stat_cat IS $$
 196 User Statistical Catagories
 197
 198 Local data collected about Users is placed into a Statistical
 199 Catagory.  Here's where those catagories are defined.
 200 $$;
 201
 202
 203 CREATE TABLE actor.stat_cat_entry (
 204         id              SERIAL  PRIMARY KEY,
 205         stat_cat        INT     NOT NULL,
 206         owner           INT     NOT NULL,
 207         value           TEXT    NOT NULL,
 208         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
 209 );
 210 COMMENT ON TABLE actor.stat_cat_entry IS $$
 211 User Statistical Catagory Entries
 212
 213 Local data collected about Users is placed into a Statistical
 214 Catagory.  Each library can create entries into any of its own
 215 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
 216 $$;
 217
 218
 219 CREATE TABLE actor.stat_cat_entry_usr_map (
 220         id              BIGSERIAL       PRIMARY KEY,
 221         stat_cat_entry  TEXT            NOT NULL,
 222         stat_cat        INT             NOT NULL,
 223         target_usr      INT             NOT NULL,
 224         CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
 225 );
 226 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
 227 Statistical Catagory Entry to User map
 228
 229 Records the stat_cat entries for each user.
 230 $$;
 231
 232 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
 233
 234 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
 235 DECLARE
 236     sipfield actor.stat_cat_sip_fields%ROWTYPE;
 237     use_count INT;
 238 BEGIN
 239     IF NEW.sip_field IS NOT NULL THEN
 240         SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
 241         IF sipfield.one_only THEN
 242             SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
 243             IF use_count > 0 THEN
 244                 RAISE EXCEPTION 'Sip field cannot be used twice';
 245             END IF;
 246         END IF;
 247     END IF;
 248     RETURN NEW;
 249 END;
 250 $func$ LANGUAGE PLPGSQL;
 251
 252 CREATE TRIGGER actor_stat_cat_sip_update_trigger
 253     BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
 254     EXECUTE PROCEDURE actor.stat_cat_check();
 255
 256 CREATE TABLE actor.card (
 257         id      SERIAL  PRIMARY KEY,
 258         usr     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 259         barcode TEXT    NOT NULL UNIQUE,
 260         active  BOOL    NOT NULL DEFAULT TRUE
 261 );
 262 COMMENT ON TABLE actor.card IS $$
 263 Library Cards
 264
 265 Each User has one or more library cards.  The current "main"
 266 card is linked to here from the actor.usr table, and it is up
 267 to the consortium policy whether more than one card can be
 268 active for any one user at a given time.
 269 $$;
 270
 271 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
 272 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
 273
 274 CREATE TABLE actor.org_unit_type (
 275         id              SERIAL  PRIMARY KEY,
 276         name            TEXT    NOT NULL,
 277         opac_label      TEXT    NOT NULL,
 278         depth           INT     NOT NULL,
 279         parent          INT     REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
 280         can_have_vols   BOOL    NOT NULL DEFAULT TRUE,
 281         can_have_users  BOOL    NOT NULL DEFAULT TRUE
 282 );
 283 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
 284
 285 CREATE TABLE actor.org_unit (
 286         id              SERIAL  PRIMARY KEY,
 287         parent_ou       INT     REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 288         ou_type         INT     NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
 289         ill_address     INT,
 290         holds_address   INT,
 291         mailing_address INT,
 292         billing_address INT,
 293         shortname       TEXT    NOT NULL UNIQUE,
 294         name            TEXT    NOT NULL UNIQUE,
 295         email           TEXT,
 296         phone           TEXT,
 297         opac_visible    BOOL    NOT NULL DEFAULT TRUE,
 298         fiscal_calendar INT     NOT NULL DEFAULT 1,   -- foreign key constraint to be added later
 299         sibling_order   INT     NOT NULL DEFAULT 0
 300 );
 301 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
 302 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
 303 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
 304 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
 305 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
 306 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
 307
 308 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
 309         DECLARE
 310                 current_aou actor.org_unit%ROWTYPE;
 311                 seen_ous    INT[];
 312                 depth_count INT;
 313         BEGIN
 314                 current_aou := NEW;
 315                 depth_count := 0;
 316                 seen_ous := ARRAY[NEW.id];
 317
 318                 IF (TG_OP = 'UPDATE') THEN
 319                         IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
 320                                 RETURN NEW; -- Doing an UPDATE with no change, just return it
 321                         END IF;
 322                 END IF;
 323
 324                 LOOP
 325                         IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
 326                                 RETURN NEW; -- No loop. Carry on.
 327                         END IF;
 328                         IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
 329                                 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
 330                         END IF;
 331                         -- Get the next one!
 332                         SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
 333                         seen_ous := seen_ous || current_aou.id;
 334                         depth_count := depth_count + 1;
 335                         IF depth_count = 100 THEN
 336                                 RAISE 'OU CHECK TOO DEEP';
 337                         END IF;
 338                 END LOOP;
 339
 340                 RETURN NEW;
 341         END;
 342 $$ LANGUAGE PLPGSQL;
 343
 344 CREATE TRIGGER actor_org_unit_parent_protect_trigger
 345     BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
 346     EXECUTE PROCEDURE actor.org_unit_parent_protect ();
 347
 348 CREATE TABLE actor.org_lasso (
 349     id      SERIAL  PRIMARY KEY,
 350     name        TEXT    UNIQUE
 351 );
 352
 353 CREATE TABLE actor.org_lasso_map (
 354     id          SERIAL  PRIMARY KEY,
 355     lasso       INT     NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 356     org_unit    INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 357 );
 358 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
 359 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
 360
 361 CREATE TABLE actor.org_unit_proximity (
 362         id              BIGSERIAL       PRIMARY KEY,
 363         from_org        INT,
 364         to_org          INT,
 365         prox            INT
 366 );
 367 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
 368
 369 CREATE TABLE actor.stat_cat_entry_default (
 370         id              SERIAL  PRIMARY KEY,
 371         stat_cat_entry  INT     NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 372         stat_cat        INT     NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 373         owner           INT     NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 374         CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
 375 );
 376 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
 377 User Statistical Category Default Entry
 378
 379 A library may choose one of the stat_cat entries to be the
 380 default entry.
 381 $$;
 382
 383
 384 CREATE TABLE actor.hours_of_operation (
 385         id              INT     PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 386         dow_0_open      TIME    NOT NULL DEFAULT '09:00',
 387         dow_0_close     TIME    NOT NULL DEFAULT '17:00',
 388         dow_1_open      TIME    NOT NULL DEFAULT '09:00',
 389         dow_1_close     TIME    NOT NULL DEFAULT '17:00',
 390         dow_2_open      TIME    NOT NULL DEFAULT '09:00',
 391         dow_2_close     TIME    NOT NULL DEFAULT '17:00',
 392         dow_3_open      TIME    NOT NULL DEFAULT '09:00',
 393         dow_3_close     TIME    NOT NULL DEFAULT '17:00',
 394         dow_4_open      TIME    NOT NULL DEFAULT '09:00',
 395         dow_4_close     TIME    NOT NULL DEFAULT '17:00',
 396         dow_5_open      TIME    NOT NULL DEFAULT '09:00',
 397         dow_5_close     TIME    NOT NULL DEFAULT '17:00',
 398         dow_6_open      TIME    NOT NULL DEFAULT '09:00',
 399         dow_6_close     TIME    NOT NULL DEFAULT '17:00'
 400 );
 401 COMMENT ON TABLE actor.hours_of_operation IS $$
 402 When does this org_unit usually open and close?  (Variations
 403 are expressed in the actor.org_unit_closed table.)
 404 $$;
 405 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
 406 When does this org_unit open on Monday?
 407 $$;
 408 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
 409 When does this org_unit close on Monday?
 410 $$;
 411 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
 412 When does this org_unit open on Tuesday?
 413 $$;
 414 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
 415 When does this org_unit close on Tuesday?
 416 $$;
 417 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
 418 When does this org_unit open on Wednesday?
 419 $$;
 420 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
 421 When does this org_unit close on Wednesday?
 422 $$;
 423 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
 424 When does this org_unit open on Thursday?
 425 $$;
 426 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
 427 When does this org_unit close on Thursday?
 428 $$;
 429 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
 430 When does this org_unit open on Friday?
 431 $$;
 432 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
 433 When does this org_unit close on Friday?
 434 $$;
 435 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
 436 When does this org_unit open on Saturday?
 437 $$;
 438 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
 439 When does this org_unit close on Saturday?
 440 $$;
 441 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
 442 When does this org_unit open on Sunday?
 443 $$;
 444 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
 445 When does this org_unit close on Sunday?
 446 $$;
 447
 448 CREATE TABLE actor.org_unit_closed (
 449         id              SERIAL                          PRIMARY KEY,
 450         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 451         close_start     TIMESTAMP WITH TIME ZONE        NOT NULL,
 452         close_end       TIMESTAMP WITH TIME ZONE        NOT NULL,
 453         reason          TEXT
 454 );
 455
 456 -- Workstation registration...
 457 CREATE TABLE actor.workstation (
 458         id              SERIAL  PRIMARY KEY,
 459         name            TEXT    NOT NULL UNIQUE,
 460         owning_lib      INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 461 );
 462
 463 CREATE TABLE actor.usr_org_unit_opt_in (
 464         id              SERIAL                          PRIMARY KEY,
 465         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 466         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 467         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 468         opt_in_ts       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 469         opt_in_ws       INT                             NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
 470         CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
 471 );
 472 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
 473
 474 CREATE TABLE actor.org_unit_setting (
 475         id              BIGSERIAL       PRIMARY KEY,
 476         org_unit        INT             NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 477         name            TEXT    NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
 478         value           TEXT            NOT NULL,
 479         CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
 480         CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
 481 );
 482 COMMENT ON TABLE actor.org_unit_setting IS $$
 483 Org Unit settings
 484
 485 This table contains any arbitrary settings that a client
 486 program would like to save for an org unit.
 487 $$;
 488
 489 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
 490
 491 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
 492 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
 493     DECLARE
 494     original TEXT;
 495     BEGIN
 496         -- Check for which setting is being updated, and log it.
 497         SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
 498
 499         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
 500
 501         RETURN NEW;
 502     END;
 503 $ous_change_log$ LANGUAGE plpgsql;
 504
 505 CREATE TRIGGER log_ous_change
 506     BEFORE INSERT OR UPDATE ON actor.org_unit_setting
 507     FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
 508
 509 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
 510     DECLARE
 511     original TEXT;
 512     BEGIN
 513         -- Check for which setting is being updated, and log it.
 514         SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
 515
 516         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
 517
 518         RETURN OLD;
 519     END;
 520 $ous_delete_log$ LANGUAGE plpgsql;
 521
 522 CREATE TRIGGER log_ous_del
 523     BEFORE DELETE ON actor.org_unit_setting
 524     FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
 525
 526
 527
 528
 529 CREATE TABLE actor.usr_address (
 530         id                      SERIAL  PRIMARY KEY,
 531         valid                   BOOL    NOT NULL DEFAULT TRUE,
 532         within_city_limits      BOOL    NOT NULL DEFAULT TRUE,
 533         address_type            TEXT    NOT NULL DEFAULT 'MAILING',
 534         usr                     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 535         street1                 TEXT    NOT NULL,
 536         street2                 TEXT,
 537         city                    TEXT    NOT NULL,
 538         county                  TEXT,
 539         state                   TEXT    NOT NULL,
 540         country                 TEXT    NOT NULL,
 541         post_code               TEXT    NOT NULL,
 542     pending         BOOL    NOT NULL DEFAULT FALSE,
 543         replaces            INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
 544 );
 545
 546 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
 547
 548 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
 549 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
 550
 551 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
 552 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
 553 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
 554
 555 CREATE TABLE actor.usr_password_reset (
 556   id SERIAL PRIMARY KEY,
 557   uuid TEXT NOT NULL,
 558   usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
 559   request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 560   has_been_reset BOOL NOT NULL DEFAULT false
 561 );
 562 COMMENT ON TABLE actor.usr_password_reset IS $$
 563 Self-serve password reset requests
 564 $$;
 565 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
 566 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
 567 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
 568 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
 569
 570 CREATE TABLE actor.org_address (
 571         id              SERIAL  PRIMARY KEY,
 572         valid           BOOL    NOT NULL DEFAULT TRUE,
 573         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 574         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 575         street1         TEXT    NOT NULL,
 576         street2         TEXT,
 577         city            TEXT    NOT NULL,
 578         county          TEXT,
 579         state           TEXT    NOT NULL,
 580         country         TEXT    NOT NULL,
 581         post_code       TEXT    NOT NULL,
 582     san         TEXT
 583 );
 584
 585 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
 586
 587 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
 588         SELECT SUBSTRING( $1, 1, 5);
 589 $$ LANGUAGE SQL;
 590
 591 CREATE TABLE actor.usr_standing_penalty (
 592         id                      SERIAL  PRIMARY KEY,
 593         org_unit                INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 594         usr                     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 595         standing_penalty        INT     NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 596         staff                   INT     REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 597         set_date                TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 598         stop_date               TIMESTAMP WITH TIME ZONE,
 599         note                    TEXT
 600 );
 601 COMMENT ON TABLE actor.usr_standing_penalty IS $$
 602 User standing penalties
 603 $$;
 604
 605 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
 606 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
 607
 608
 609 CREATE TABLE actor.usr_saved_search (
 610     id              SERIAL          PRIMARY KEY,
 611         owner           INT             NOT NULL REFERENCES actor.usr (id)
 612                                         ON DELETE CASCADE
 613                                         DEFERRABLE INITIALLY DEFERRED,
 614         name            TEXT            NOT NULL,
 615         create_date     TIMESTAMPTZ     NOT NULL DEFAULT now(),
 616         query_text      TEXT            NOT NULL,
 617         query_type      TEXT            NOT NULL
 618                                         CONSTRAINT valid_query_text CHECK (
 619                                         query_type IN ( 'URL' )) DEFAULT 'URL',
 620                                         -- we may add other types someday
 621         target          TEXT            NOT NULL
 622                                         CONSTRAINT valid_target CHECK (
 623                                         target IN ( 'record', 'metarecord', 'callnumber' )),
 624         CONSTRAINT name_once_per_user UNIQUE (owner, name)
 625 );
 626
 627 CREATE TABLE actor.address_alert (
 628     id              SERIAL  PRIMARY KEY,
 629     owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 630     active          BOOL    NOT NULL DEFAULT TRUE,
 631     match_all       BOOL    NOT NULL DEFAULT TRUE,
 632     alert_message   TEXT    NOT NULL,
 633     street1         TEXT,
 634     street2         TEXT,
 635     city            TEXT,
 636     county          TEXT,
 637     state           TEXT,
 638     country         TEXT,
 639     post_code       TEXT,
 640     mailing_address BOOL    NOT NULL DEFAULT FALSE,
 641     billing_address BOOL    NOT NULL DEFAULT FALSE
 642 );
 643
 644 CREATE TABLE actor.usr_activity (
 645     id          BIGSERIAL   PRIMARY KEY,
 646     usr         INT         REFERENCES actor.usr (id) ON DELETE SET NULL,
 647     etype       INT         NOT NULL REFERENCES config.usr_activity_type (id),
 648     event_time  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 649 );
 650
 651 CREATE TABLE actor.toolbar (
 652     id          BIGSERIAL   PRIMARY KEY,
 653     ws          INT         REFERENCES actor.workstation (id) ON DELETE CASCADE,
 654     org         INT         REFERENCES actor.org_unit (id) ON DELETE CASCADE,
 655     usr         INT         REFERENCES actor.usr (id) ON DELETE CASCADE,
 656     label       TEXT        NOT NULL,
 657     layout      TEXT        NOT NULL,
 658     CONSTRAINT only_one_type CHECK (
 659         (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
 660         (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
 661         (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
 662     ),
 663     CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
 664 );
 665 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
 666 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
 667 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
 668
 669 COMMIT;