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