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