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 );
  68 COMMENT ON TABLE actor.usr IS $$
  69 User objects
  70
  71 This table contains the core User objects that describe both
  72 staff members and patrons.  The difference between the two
  73 types of users is based on the user's permissions.
  74 $$;
  75
  76 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
  77 CREATE INDEX actor_usr_usrgroup_idx ON actor.usr (usrgroup);
  78 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
  79 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
  80
  81 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name));
  82 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name));
  83 CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name));
  84
  85 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
  86
  87 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
  88 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
  89 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
  90
  91 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
  92     (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
  93
  94 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
  95     (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
  96
  97 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
  98     (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
  99
 100 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
 101 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
 102
 103 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
 104         BEGIN
 105                 NEW.passwd = MD5( NEW.passwd );
 106                 RETURN NEW;
 107         END;
 108 $$ LANGUAGE PLPGSQL;
 109
 110 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
 111         BEGIN
 112                 IF NEW.passwd <> OLD.passwd THEN
 113                         NEW.passwd = MD5( NEW.passwd );
 114                 END IF;
 115                 RETURN NEW;
 116         END;
 117 $$ LANGUAGE PLPGSQL;
 118
 119 CREATE TRIGGER actor_crypt_pw_update_trigger
 120         BEFORE UPDATE ON actor.usr FOR EACH ROW
 121         EXECUTE PROCEDURE actor.crypt_pw_update ();
 122
 123 CREATE TRIGGER actor_crypt_pw_insert_trigger
 124         BEFORE INSERT ON actor.usr FOR EACH ROW
 125         EXECUTE PROCEDURE actor.crypt_pw_insert ();
 126
 127 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;
 128
 129 CREATE TABLE actor.usr_note (
 130         id              BIGSERIAL                       PRIMARY KEY,
 131         usr             BIGINT                          NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 132         creator         BIGINT                          NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 133         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 134         pub             BOOL                            NOT NULL DEFAULT FALSE,
 135         title           TEXT                            NOT NULL,
 136         value           TEXT                            NOT NULL
 137 );
 138 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
 139 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
 140
 141 CREATE TABLE actor.usr_setting (
 142         id      BIGSERIAL       PRIMARY KEY,
 143         usr     INT             NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 144         name    TEXT            NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 145         value   TEXT            NOT NULL,
 146         CONSTRAINT usr_once_per_key UNIQUE (usr,name)
 147 );
 148 COMMENT ON TABLE actor.usr_setting IS $$
 149 User settings
 150
 151 This table contains any arbitrary settings that a client
 152 program would like to save for a user.
 153 $$;
 154
 155 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
 156
 157 CREATE TABLE actor.stat_cat_sip_fields (
 158     field   CHAR(2) PRIMARY KEY,
 159     name    TEXT    NOT NULL,
 160     one_only  BOOL    NOT NULL DEFAULT FALSE
 161 );
 162 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
 163 Actor Statistical Category SIP Fields
 164
 165 Contains the list of valid SIP Field identifiers for
 166 Statistical Categories.
 167 $$;
 168
 169 CREATE TABLE actor.stat_cat (
 170         id              SERIAL  PRIMARY KEY,
 171         owner           INT     NOT NULL,
 172         name            TEXT    NOT NULL,
 173         opac_visible    BOOL NOT NULL DEFAULT FALSE,
 174         usr_summary     BOOL NOT NULL DEFAULT FALSE,
 175     sip_field   CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 176     sip_format  TEXT,
 177         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
 178 );
 179 COMMENT ON TABLE actor.stat_cat IS $$
 180 User Statistical Catagories
 181
 182 Local data collected about Users is placed into a Statistical
 183 Catagory.  Here's where those catagories are defined.
 184 $$;
 185
 186
 187 CREATE TABLE actor.stat_cat_entry (
 188         id              SERIAL  PRIMARY KEY,
 189         stat_cat        INT     NOT NULL,
 190         owner           INT     NOT NULL,
 191         value           TEXT    NOT NULL,
 192         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
 193 );
 194 COMMENT ON TABLE actor.stat_cat_entry IS $$
 195 User Statistical Catagory Entries
 196
 197 Local data collected about Users is placed into a Statistical
 198 Catagory.  Each library can create entries into any of its own
 199 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
 200 $$;
 201
 202
 203 CREATE TABLE actor.stat_cat_entry_usr_map (
 204         id              BIGSERIAL       PRIMARY KEY,
 205         stat_cat_entry  TEXT            NOT NULL,
 206         stat_cat        INT             NOT NULL,
 207         target_usr      INT             NOT NULL,
 208         CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
 209 );
 210 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
 211 Statistical Catagory Entry to User map
 212
 213 Records the stat_cat entries for each user.
 214 $$;
 215
 216 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
 217
 218 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
 219 DECLARE
 220     sipfield actor.stat_cat_sip_fields%ROWTYPE;
 221     use_count INT;
 222 BEGIN
 223     IF NEW.sip_field IS NOT NULL THEN
 224         SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
 225         IF sipfield.one_only THEN
 226             SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
 227             IF use_count > 0 THEN
 228                 RAISE EXCEPTION 'Sip field cannot be used twice';
 229             END IF;
 230         END IF;
 231     END IF;
 232     RETURN NEW;
 233 END;
 234 $func$ LANGUAGE PLPGSQL;
 235
 236 CREATE TRIGGER actor_stat_cat_sip_update_trigger
 237     BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
 238     EXECUTE PROCEDURE actor.stat_cat_check();
 239
 240 CREATE TABLE actor.card (
 241         id      SERIAL  PRIMARY KEY,
 242         usr     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 243         barcode TEXT    NOT NULL UNIQUE,
 244         active  BOOL    NOT NULL DEFAULT TRUE
 245 );
 246 COMMENT ON TABLE actor.card IS $$
 247 Library Cards
 248
 249 Each User has one or more library cards.  The current "main"
 250 card is linked to here from the actor.usr table, and it is up
 251 to the consortium policy whether more than one card can be
 252 active for any one user at a given time.
 253 $$;
 254
 255 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
 256 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
 257
 258 CREATE TABLE actor.org_unit_type (
 259         id              SERIAL  PRIMARY KEY,
 260         name            TEXT    NOT NULL,
 261         opac_label      TEXT    NOT NULL,
 262         depth           INT     NOT NULL,
 263         parent          INT     REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
 264         can_have_vols   BOOL    NOT NULL DEFAULT TRUE,
 265         can_have_users  BOOL    NOT NULL DEFAULT TRUE
 266 );
 267 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
 268
 269 CREATE TABLE actor.org_unit (
 270         id              SERIAL  PRIMARY KEY,
 271         parent_ou       INT     REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 272         ou_type         INT     NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
 273         ill_address     INT,
 274         holds_address   INT,
 275         mailing_address INT,
 276         billing_address INT,
 277         shortname       TEXT    NOT NULL UNIQUE,
 278         name            TEXT    NOT NULL UNIQUE,
 279         email           TEXT,
 280         phone           TEXT,
 281         opac_visible    BOOL    NOT NULL DEFAULT TRUE,
 282         fiscal_calendar INT     NOT NULL DEFAULT 1   -- foreign key constraint to be added later
 283 );
 284 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
 285 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
 286 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
 287 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
 288 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
 289 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
 290
 291 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
 292         DECLARE
 293                 current_aou actor.org_unit%ROWTYPE;
 294                 seen_ous    INT[];
 295                 depth_count INT;
 296         BEGIN
 297                 current_aou := NEW;
 298                 depth_count := 0;
 299                 seen_ous := ARRAY[NEW.id];
 300
 301                 IF (TG_OP = 'UPDATE') THEN
 302                         IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
 303                                 RETURN NEW; -- Doing an UPDATE with no change, just return it
 304                         END IF;
 305                 END IF;
 306
 307                 LOOP
 308                         IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
 309                                 RETURN NEW; -- No loop. Carry on.
 310                         END IF;
 311                         IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
 312                                 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
 313                         END IF;
 314                         -- Get the next one!
 315                         SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
 316                         seen_ous := seen_ous || current_aou.id;
 317                         depth_count := depth_count + 1;
 318                         IF depth_count = 100 THEN
 319                                 RAISE 'OU CHECK TOO DEEP';
 320                         END IF;
 321                 END LOOP;
 322
 323                 RETURN NEW;
 324         END;
 325 $$ LANGUAGE PLPGSQL;
 326
 327 CREATE TRIGGER actor_org_unit_parent_protect_trigger
 328     BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
 329     EXECUTE PROCEDURE actor.org_unit_parent_protect ();
 330
 331 CREATE TABLE actor.org_lasso (
 332     id      SERIAL  PRIMARY KEY,
 333     name        TEXT    UNIQUE
 334 );
 335
 336 CREATE TABLE actor.org_lasso_map (
 337     id          SERIAL  PRIMARY KEY,
 338     lasso       INT     NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 339     org_unit    INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 340 );
 341 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
 342 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
 343
 344 CREATE TABLE actor.org_unit_proximity (
 345         id              BIGSERIAL       PRIMARY KEY,
 346         from_org        INT,
 347         to_org          INT,
 348         prox            INT
 349 );
 350 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
 351
 352 CREATE TABLE actor.hours_of_operation (
 353         id              INT     PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 354         dow_0_open      TIME    NOT NULL DEFAULT '09:00',
 355         dow_0_close     TIME    NOT NULL DEFAULT '17:00',
 356         dow_1_open      TIME    NOT NULL DEFAULT '09:00',
 357         dow_1_close     TIME    NOT NULL DEFAULT '17:00',
 358         dow_2_open      TIME    NOT NULL DEFAULT '09:00',
 359         dow_2_close     TIME    NOT NULL DEFAULT '17:00',
 360         dow_3_open      TIME    NOT NULL DEFAULT '09:00',
 361         dow_3_close     TIME    NOT NULL DEFAULT '17:00',
 362         dow_4_open      TIME    NOT NULL DEFAULT '09:00',
 363         dow_4_close     TIME    NOT NULL DEFAULT '17:00',
 364         dow_5_open      TIME    NOT NULL DEFAULT '09:00',
 365         dow_5_close     TIME    NOT NULL DEFAULT '17:00',
 366         dow_6_open      TIME    NOT NULL DEFAULT '09:00',
 367         dow_6_close     TIME    NOT NULL DEFAULT '17:00'
 368 );
 369 COMMENT ON TABLE actor.hours_of_operation IS $$
 370 When does this org_unit usually open and close?  (Variations
 371 are expressed in the actor.org_unit_closed table.)
 372 $$;
 373 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
 374 When does this org_unit open on Monday?
 375 $$;
 376 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
 377 When does this org_unit close on Monday?
 378 $$;
 379 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
 380 When does this org_unit open on Tuesday?
 381 $$;
 382 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
 383 When does this org_unit close on Tuesday?
 384 $$;
 385 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
 386 When does this org_unit open on Wednesday?
 387 $$;
 388 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
 389 When does this org_unit close on Wednesday?
 390 $$;
 391 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
 392 When does this org_unit open on Thursday?
 393 $$;
 394 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
 395 When does this org_unit close on Thursday?
 396 $$;
 397 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
 398 When does this org_unit open on Friday?
 399 $$;
 400 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
 401 When does this org_unit close on Friday?
 402 $$;
 403 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
 404 When does this org_unit open on Saturday?
 405 $$;
 406 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
 407 When does this org_unit close on Saturday?
 408 $$;
 409 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
 410 When does this org_unit open on Sunday?
 411 $$;
 412 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
 413 When does this org_unit close on Sunday?
 414 $$;
 415
 416 CREATE TABLE actor.org_unit_closed (
 417         id              SERIAL                          PRIMARY KEY,
 418         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 419         close_start     TIMESTAMP WITH TIME ZONE        NOT NULL,
 420         close_end       TIMESTAMP WITH TIME ZONE        NOT NULL,
 421         reason          TEXT
 422 );
 423
 424 -- Workstation registration...
 425 CREATE TABLE actor.workstation (
 426         id              SERIAL  PRIMARY KEY,
 427         name            TEXT    NOT NULL UNIQUE,
 428         owning_lib      INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 429 );
 430
 431 CREATE TABLE actor.usr_org_unit_opt_in (
 432         id              SERIAL                          PRIMARY KEY,
 433         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 434         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 435         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 436         opt_in_ts       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 437         opt_in_ws       INT                             NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
 438         CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
 439 );
 440 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
 441
 442 CREATE TABLE actor.org_unit_setting (
 443         id              BIGSERIAL       PRIMARY KEY,
 444         org_unit        INT             NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 445         name            TEXT    NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
 446         value           TEXT            NOT NULL,
 447         CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
 448 );
 449 COMMENT ON TABLE actor.org_unit_setting IS $$
 450 Org Unit settings
 451
 452 This table contains any arbitrary settings that a client
 453 program would like to save for an org unit.
 454 $$;
 455
 456 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
 457
 458
 459 CREATE TABLE actor.usr_address (
 460         id                      SERIAL  PRIMARY KEY,
 461         valid                   BOOL    NOT NULL DEFAULT TRUE,
 462         within_city_limits      BOOL    NOT NULL DEFAULT TRUE,
 463         address_type            TEXT    NOT NULL DEFAULT 'MAILING',
 464         usr                     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 465         street1                 TEXT    NOT NULL,
 466         street2                 TEXT,
 467         city                    TEXT    NOT NULL,
 468         county                  TEXT,
 469         state                   TEXT    NOT NULL,
 470         country                 TEXT    NOT NULL,
 471         post_code               TEXT    NOT NULL,
 472     pending         BOOL    NOT NULL DEFAULT FALSE,
 473         replaces            INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
 474 );
 475
 476 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
 477
 478 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
 479 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
 480
 481 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
 482 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
 483 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
 484
 485 CREATE TABLE actor.usr_password_reset (
 486   id SERIAL PRIMARY KEY,
 487   uuid TEXT NOT NULL,
 488   usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
 489   request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 490   has_been_reset BOOL NOT NULL DEFAULT false
 491 );
 492 COMMENT ON TABLE actor.usr_password_reset IS $$
 493 Self-serve password reset requests
 494 $$;
 495 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
 496 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
 497 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
 498 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
 499
 500 CREATE TABLE actor.org_address (
 501         id              SERIAL  PRIMARY KEY,
 502         valid           BOOL    NOT NULL DEFAULT TRUE,
 503         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 504         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 505         street1         TEXT    NOT NULL,
 506         street2         TEXT,
 507         city            TEXT    NOT NULL,
 508         county          TEXT,
 509         state           TEXT    NOT NULL,
 510         country         TEXT    NOT NULL,
 511         post_code       TEXT    NOT NULL,
 512     san         TEXT
 513 );
 514
 515 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
 516
 517 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
 518         SELECT SUBSTRING( $1, 1, 5);
 519 $$ LANGUAGE SQL;
 520
 521 CREATE TABLE actor.usr_standing_penalty (
 522         id                      SERIAL  PRIMARY KEY,
 523         org_unit                INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 524         usr                     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 525         standing_penalty        INT     NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 526         staff                   INT     REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 527         set_date                TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 528         stop_date               TIMESTAMP WITH TIME ZONE,
 529         note                    TEXT
 530 );
 531 COMMENT ON TABLE actor.usr_standing_penalty IS $$
 532 User standing penalties
 533 $$;
 534
 535 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
 536 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
 537
 538
 539 CREATE TABLE actor.usr_saved_search (
 540     id              SERIAL          PRIMARY KEY,
 541         owner           INT             NOT NULL REFERENCES actor.usr (id)
 542                                         ON DELETE CASCADE
 543                                         DEFERRABLE INITIALLY DEFERRED,
 544         name            TEXT            NOT NULL,
 545         create_date     TIMESTAMPTZ     NOT NULL DEFAULT now(),
 546         query_text      TEXT            NOT NULL,
 547         query_type      TEXT            NOT NULL
 548                                         CONSTRAINT valid_query_text CHECK (
 549                                         query_type IN ( 'URL' )) DEFAULT 'URL',
 550                                         -- we may add other types someday
 551         target          TEXT            NOT NULL
 552                                         CONSTRAINT valid_target CHECK (
 553                                         target IN ( 'record', 'metarecord', 'callnumber' )),
 554         CONSTRAINT name_once_per_user UNIQUE (owner, name)
 555 );
 556
 557 COMMIT;