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         IF TG_OP = 'INSERT' OR NEW.parent_ou IS DISTINCT FROM OLD.parent_ou THEN
 301             LOOP
 302                 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
 303                     RETURN NEW; -- No loop. Carry on.
 304                 END IF;
 305                 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
 306                     RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
 307                 END IF;
 308                 -- Get the next one!
 309                 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
 310                 seen_ous := seen_ous || current_aou.id;
 311                 depth_count := depth_count + 1;
 312                 IF depth_count = 100 THEN
 313                     RAISE 'OU CHECK TOO DEEP';
 314                 END IF;
 315             END LOOP;
 316         END IF;
 317                 RETURN NEW;
 318         END;
 319 $$ LANGUAGE PLPGSQL;
 320
 321 CREATE TRIGGER actor_org_unit_parent_protect_trigger
 322     BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
 323     EXECUTE PROCEDURE actor.org_unit_parent_protect ();
 324
 325 CREATE TABLE actor.org_lasso (
 326     id      SERIAL  PRIMARY KEY,
 327     name        TEXT    UNIQUE
 328 );
 329
 330 CREATE TABLE actor.org_lasso_map (
 331     id          SERIAL  PRIMARY KEY,
 332     lasso       INT     NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 333     org_unit    INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 334 );
 335 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
 336 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
 337
 338 CREATE TABLE actor.org_unit_proximity (
 339         id              BIGSERIAL       PRIMARY KEY,
 340         from_org        INT,
 341         to_org          INT,
 342         prox            INT
 343 );
 344 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
 345
 346 CREATE TABLE actor.hours_of_operation (
 347         id              INT     PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 348         dow_0_open      TIME    NOT NULL DEFAULT '09:00',
 349         dow_0_close     TIME    NOT NULL DEFAULT '17:00',
 350         dow_1_open      TIME    NOT NULL DEFAULT '09:00',
 351         dow_1_close     TIME    NOT NULL DEFAULT '17:00',
 352         dow_2_open      TIME    NOT NULL DEFAULT '09:00',
 353         dow_2_close     TIME    NOT NULL DEFAULT '17:00',
 354         dow_3_open      TIME    NOT NULL DEFAULT '09:00',
 355         dow_3_close     TIME    NOT NULL DEFAULT '17:00',
 356         dow_4_open      TIME    NOT NULL DEFAULT '09:00',
 357         dow_4_close     TIME    NOT NULL DEFAULT '17:00',
 358         dow_5_open      TIME    NOT NULL DEFAULT '09:00',
 359         dow_5_close     TIME    NOT NULL DEFAULT '17:00',
 360         dow_6_open      TIME    NOT NULL DEFAULT '09:00',
 361         dow_6_close     TIME    NOT NULL DEFAULT '17:00'
 362 );
 363 COMMENT ON TABLE actor.hours_of_operation IS $$
 364 When does this org_unit usually open and close?  (Variations
 365 are expressed in the actor.org_unit_closed table.)
 366 $$;
 367 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
 368 When does this org_unit open on Monday?
 369 $$;
 370 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
 371 When does this org_unit close on Monday?
 372 $$;
 373 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
 374 When does this org_unit open on Tuesday?
 375 $$;
 376 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
 377 When does this org_unit close on Tuesday?
 378 $$;
 379 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
 380 When does this org_unit open on Wednesday?
 381 $$;
 382 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
 383 When does this org_unit close on Wednesday?
 384 $$;
 385 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
 386 When does this org_unit open on Thursday?
 387 $$;
 388 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
 389 When does this org_unit close on Thursday?
 390 $$;
 391 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
 392 When does this org_unit open on Friday?
 393 $$;
 394 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
 395 When does this org_unit close on Friday?
 396 $$;
 397 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
 398 When does this org_unit open on Saturday?
 399 $$;
 400 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
 401 When does this org_unit close on Saturday?
 402 $$;
 403 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
 404 When does this org_unit open on Sunday?
 405 $$;
 406 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
 407 When does this org_unit close on Sunday?
 408 $$;
 409
 410 CREATE TABLE actor.org_unit_closed (
 411         id              SERIAL                          PRIMARY KEY,
 412         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 413         close_start     TIMESTAMP WITH TIME ZONE        NOT NULL,
 414         close_end       TIMESTAMP WITH TIME ZONE        NOT NULL,
 415         reason          TEXT
 416 );
 417
 418 -- Workstation registration...
 419 CREATE TABLE actor.workstation (
 420         id              SERIAL  PRIMARY KEY,
 421         name            TEXT    NOT NULL UNIQUE,
 422         owning_lib      INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 423 );
 424
 425 CREATE TABLE actor.usr_org_unit_opt_in (
 426         id              SERIAL                          PRIMARY KEY,
 427         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 428         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 429         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 430         opt_in_ts       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 431         opt_in_ws       INT                             NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
 432         CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
 433 );
 434 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
 435
 436 CREATE TABLE actor.org_unit_setting (
 437         id              BIGSERIAL       PRIMARY KEY,
 438         org_unit        INT             NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 439         name            TEXT    NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
 440         value           TEXT            NOT NULL,
 441         CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
 442 );
 443 COMMENT ON TABLE actor.org_unit_setting IS $$
 444 Org Unit settings
 445
 446 This table contains any arbitrary settings that a client
 447 program would like to save for an org unit.
 448 $$;
 449
 450 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
 451
 452
 453 CREATE TABLE actor.usr_address (
 454         id                      SERIAL  PRIMARY KEY,
 455         valid                   BOOL    NOT NULL DEFAULT TRUE,
 456         within_city_limits      BOOL    NOT NULL DEFAULT TRUE,
 457         address_type            TEXT    NOT NULL DEFAULT 'MAILING',
 458         usr                     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 459         street1                 TEXT    NOT NULL,
 460         street2                 TEXT,
 461         city                    TEXT    NOT NULL,
 462         county                  TEXT,
 463         state                   TEXT    NOT NULL,
 464         country                 TEXT    NOT NULL,
 465         post_code               TEXT    NOT NULL,
 466     pending         BOOL    NOT NULL DEFAULT FALSE,
 467         replaces            INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
 468 );
 469
 470 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
 471
 472 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
 473 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
 474
 475 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
 476 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
 477 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
 478
 479 CREATE TABLE actor.usr_password_reset (
 480   id SERIAL PRIMARY KEY,
 481   uuid TEXT NOT NULL,
 482   usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
 483   request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 484   has_been_reset BOOL NOT NULL DEFAULT false
 485 );
 486 COMMENT ON TABLE actor.usr_password_reset IS $$
 487 Self-serve password reset requests
 488 $$;
 489 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
 490 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
 491 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
 492 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
 493
 494 CREATE TABLE actor.org_address (
 495         id              SERIAL  PRIMARY KEY,
 496         valid           BOOL    NOT NULL DEFAULT TRUE,
 497         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 498         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 499         street1         TEXT    NOT NULL,
 500         street2         TEXT,
 501         city            TEXT    NOT NULL,
 502         county          TEXT,
 503         state           TEXT    NOT NULL,
 504         country         TEXT    NOT NULL,
 505         post_code       TEXT    NOT NULL,
 506     san         TEXT
 507 );
 508
 509 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
 510
 511 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
 512         SELECT SUBSTRING( $1, 1, 5);
 513 $$ LANGUAGE SQL;
 514
 515 CREATE TABLE actor.usr_standing_penalty (
 516         id                      SERIAL  PRIMARY KEY,
 517         org_unit                INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 518         usr                     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 519         standing_penalty        INT     NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 520         staff                   INT     REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 521         set_date                TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 522         stop_date               TIMESTAMP WITH TIME ZONE,
 523         note                    TEXT
 524 );
 525 COMMENT ON TABLE actor.usr_standing_penalty IS $$
 526 User standing penalties
 527 $$;
 528
 529 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
 530 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
 531
 532
 533 CREATE TABLE actor.usr_saved_search (
 534     id              SERIAL          PRIMARY KEY,
 535         owner           INT             NOT NULL REFERENCES actor.usr (id)
 536                                         ON DELETE CASCADE
 537                                         DEFERRABLE INITIALLY DEFERRED,
 538         name            TEXT            NOT NULL,
 539         create_date     TIMESTAMPTZ     NOT NULL DEFAULT now(),
 540         query_text      TEXT            NOT NULL,
 541         query_type      TEXT            NOT NULL
 542                                         CONSTRAINT valid_query_text CHECK (
 543                                         query_type IN ( 'URL' )) DEFAULT 'URL',
 544                                         -- we may add other types someday
 545         target          TEXT            NOT NULL
 546                                         CONSTRAINT valid_target CHECK (
 547                                         target IN ( 'record', 'metarecord', 'callnumber' )),
 548         CONSTRAINT name_once_per_user UNIQUE (owner, name)
 549 );
 550
 551 COMMIT;