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_usrname_idx ON actor.usr (evergreen.lowercase(usrname));
  87 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
  88
  89 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
  90 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
  91 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
  92
  93 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
  94     (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
  95
  96 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
  97     (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
  98
  99 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
 100     (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
 101
 102 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
 103 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
 104
 105 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
 106         BEGIN
 107                 NEW.passwd = MD5( NEW.passwd );
 108                 RETURN NEW;
 109         END;
 110 $$ LANGUAGE PLPGSQL;
 111
 112 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
 113         BEGIN
 114                 IF NEW.passwd <> OLD.passwd THEN
 115                         NEW.passwd = MD5( NEW.passwd );
 116                 END IF;
 117                 RETURN NEW;
 118         END;
 119 $$ LANGUAGE PLPGSQL;
 120
 121 CREATE OR REPLACE FUNCTION actor.au_updated()
 122 RETURNS TRIGGER AS $$
 123 BEGIN
 124     NEW.last_update_time := now();
 125         RETURN NEW;
 126 END;
 127 $$ LANGUAGE plpgsql;
 128
 129 CREATE TRIGGER au_update_trig
 130         BEFORE INSERT OR UPDATE ON actor.usr
 131         FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
 132
 133 CREATE TRIGGER actor_crypt_pw_update_trigger
 134         BEFORE UPDATE ON actor.usr FOR EACH ROW
 135         EXECUTE PROCEDURE actor.crypt_pw_update ();
 136
 137 CREATE TRIGGER actor_crypt_pw_insert_trigger
 138         BEFORE INSERT ON actor.usr FOR EACH ROW
 139         EXECUTE PROCEDURE actor.crypt_pw_insert ();
 140
 141 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;
 142
 143 CREATE TABLE actor.usr_note (
 144         id              BIGSERIAL                       PRIMARY KEY,
 145         usr             BIGINT                          NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 146         creator         BIGINT                          NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 147         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 148         pub             BOOL                            NOT NULL DEFAULT FALSE,
 149         title           TEXT                            NOT NULL,
 150         value           TEXT                            NOT NULL
 151 );
 152 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
 153 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
 154
 155 CREATE TABLE actor.usr_message (
 156         id              SERIAL                          PRIMARY KEY,
 157         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 158         title           TEXT,
 159         message         TEXT                            NOT NULL,
 160         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 161         deleted         BOOL                            NOT NULL DEFAULT FALSE,
 162         read_date       TIMESTAMP WITH TIME ZONE,
 163         sending_lib     INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 164 );
 165 CREATE INDEX aum_usr ON actor.usr_message (usr);
 166
 167 CREATE RULE protect_usr_message_delete AS
 168         ON DELETE TO actor.usr_message DO INSTEAD (
 169                 UPDATE  actor.usr_message
 170                   SET   deleted = TRUE
 171                   WHERE OLD.id = actor.usr_message.id
 172         );
 173
 174 ALTER TALBE action_trigger.event_definition
 175         ADD COLUMN message_template TEXT,
 176         ADD COLUMN message_usr_path TEXT,
 177         ADD COLUMN message_library_path TEXT,
 178         ADD COLUMN message_title TEXT;
 179
 180 CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$
 181 BEGIN
 182         IF NEW.pub THEN
 183                 IF TG_OP = 'UPDATE' THEN
 184                         IF OLD.pub = TRUE THEN
 185                                 RETURN NEW;
 186                         END IF;
 187                 END IF;
 188
 189                 INSERT INTO actor.usr_message (usr, title, message, sending_lib)
 190                         VALUES (NEW.usr, NEW.title, NEW.value, (SELECT home_ou FROM actor.usr WHERE id = NEW.creator));
 191         END IF;
 192
 193         RETURN NEW;
 194 END;
 195 $$ LANGUAGE PLPGSQL;
 196
 197 CREATE TRIGGER actor.convert_usr_note_to_message_tgr
 198         AFTER INSERT OR UPDATE ON actor.usr_note
 199         FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message();
 200
 201 CREATE TABLE actor.usr_setting (
 202         id      BIGSERIAL       PRIMARY KEY,
 203         usr     INT             NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 204         name    TEXT            NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 205         value   TEXT            NOT NULL,
 206         CONSTRAINT usr_once_per_key UNIQUE (usr,name)
 207 );
 208 COMMENT ON TABLE actor.usr_setting IS $$
 209 User settings
 210
 211 This table contains any arbitrary settings that a client
 212 program would like to save for a user.
 213 $$;
 214
 215 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
 216
 217 CREATE TABLE actor.stat_cat_sip_fields (
 218     field   CHAR(2) PRIMARY KEY,
 219     name    TEXT    NOT NULL,
 220     one_only  BOOL    NOT NULL DEFAULT FALSE
 221 );
 222 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
 223 Actor Statistical Category SIP Fields
 224
 225 Contains the list of valid SIP Field identifiers for
 226 Statistical Categories.
 227 $$;
 228
 229 CREATE TABLE actor.stat_cat (
 230         id              SERIAL  PRIMARY KEY,
 231         owner           INT     NOT NULL,
 232         name            TEXT    NOT NULL,
 233         opac_visible    BOOL NOT NULL DEFAULT FALSE,
 234         usr_summary     BOOL NOT NULL DEFAULT FALSE,
 235     sip_field   CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 236     sip_format  TEXT,
 237     checkout_archive    BOOL NOT NULL DEFAULT FALSE,
 238         required        BOOL NOT NULL DEFAULT FALSE,
 239         allow_freetext  BOOL NOT NULL DEFAULT TRUE,
 240         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
 241 );
 242 COMMENT ON TABLE actor.stat_cat IS $$
 243 User Statistical Catagories
 244
 245 Local data collected about Users is placed into a Statistical
 246 Catagory.  Here's where those catagories are defined.
 247 $$;
 248
 249
 250 CREATE TABLE actor.stat_cat_entry (
 251         id              SERIAL  PRIMARY KEY,
 252         stat_cat        INT     NOT NULL,
 253         owner           INT     NOT NULL,
 254         value           TEXT    NOT NULL,
 255         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
 256 );
 257 COMMENT ON TABLE actor.stat_cat_entry IS $$
 258 User Statistical Catagory Entries
 259
 260 Local data collected about Users is placed into a Statistical
 261 Catagory.  Each library can create entries into any of its own
 262 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
 263 $$;
 264
 265
 266 CREATE TABLE actor.stat_cat_entry_usr_map (
 267         id              BIGSERIAL       PRIMARY KEY,
 268         stat_cat_entry  TEXT            NOT NULL,
 269         stat_cat        INT             NOT NULL,
 270         target_usr      INT             NOT NULL,
 271         CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
 272 );
 273 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
 274 Statistical Catagory Entry to User map
 275
 276 Records the stat_cat entries for each user.
 277 $$;
 278
 279 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
 280
 281 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
 282 DECLARE
 283     sipfield actor.stat_cat_sip_fields%ROWTYPE;
 284     use_count INT;
 285 BEGIN
 286     IF NEW.sip_field IS NOT NULL THEN
 287         SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
 288         IF sipfield.one_only THEN
 289             SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
 290             IF use_count > 0 THEN
 291                 RAISE EXCEPTION 'Sip field cannot be used twice';
 292             END IF;
 293         END IF;
 294     END IF;
 295     RETURN NEW;
 296 END;
 297 $func$ LANGUAGE PLPGSQL;
 298
 299 CREATE TRIGGER actor_stat_cat_sip_update_trigger
 300     BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
 301     EXECUTE PROCEDURE actor.stat_cat_check();
 302
 303 CREATE TABLE actor.card (
 304         id      SERIAL  PRIMARY KEY,
 305         usr     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 306         barcode TEXT    NOT NULL UNIQUE,
 307         active  BOOL    NOT NULL DEFAULT TRUE
 308 );
 309 COMMENT ON TABLE actor.card IS $$
 310 Library Cards
 311
 312 Each User has one or more library cards.  The current "main"
 313 card is linked to here from the actor.usr table, and it is up
 314 to the consortium policy whether more than one card can be
 315 active for any one user at a given time.
 316 $$;
 317
 318 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
 319 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
 320
 321 CREATE TABLE actor.org_unit_type (
 322         id              SERIAL  PRIMARY KEY,
 323         name            TEXT    NOT NULL,
 324         opac_label      TEXT    NOT NULL,
 325         depth           INT     NOT NULL,
 326         parent          INT     REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
 327         can_have_vols   BOOL    NOT NULL DEFAULT TRUE,
 328         can_have_users  BOOL    NOT NULL DEFAULT TRUE
 329 );
 330 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
 331
 332 CREATE TABLE actor.org_unit (
 333         id              SERIAL  PRIMARY KEY,
 334         parent_ou       INT     REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 335         ou_type         INT     NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
 336         ill_address     INT,
 337         holds_address   INT,
 338         mailing_address INT,
 339         billing_address INT,
 340         shortname       TEXT    NOT NULL UNIQUE,
 341         name            TEXT    NOT NULL UNIQUE,
 342         email           TEXT,
 343         phone           TEXT,
 344         opac_visible    BOOL    NOT NULL DEFAULT TRUE,
 345         fiscal_calendar INT     NOT NULL DEFAULT 1   -- foreign key constraint to be added later
 346 );
 347 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
 348 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
 349 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
 350 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
 351 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
 352 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
 353
 354 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
 355         DECLARE
 356                 current_aou actor.org_unit%ROWTYPE;
 357                 seen_ous    INT[];
 358                 depth_count INT;
 359         BEGIN
 360                 current_aou := NEW;
 361                 depth_count := 0;
 362                 seen_ous := ARRAY[NEW.id];
 363
 364                 IF (TG_OP = 'UPDATE') THEN
 365                         IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
 366                                 RETURN NEW; -- Doing an UPDATE with no change, just return it
 367                         END IF;
 368                 END IF;
 369
 370                 LOOP
 371                         IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
 372                                 RETURN NEW; -- No loop. Carry on.
 373                         END IF;
 374                         IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
 375                                 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
 376                         END IF;
 377                         -- Get the next one!
 378                         SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
 379                         seen_ous := seen_ous || current_aou.id;
 380                         depth_count := depth_count + 1;
 381                         IF depth_count = 100 THEN
 382                                 RAISE 'OU CHECK TOO DEEP';
 383                         END IF;
 384                 END LOOP;
 385
 386                 RETURN NEW;
 387         END;
 388 $$ LANGUAGE PLPGSQL;
 389
 390 CREATE TRIGGER actor_org_unit_parent_protect_trigger
 391     BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
 392     EXECUTE PROCEDURE actor.org_unit_parent_protect ();
 393
 394 CREATE TABLE actor.org_lasso (
 395     id      SERIAL  PRIMARY KEY,
 396     name        TEXT    UNIQUE
 397 );
 398
 399 CREATE TABLE actor.org_lasso_map (
 400     id          SERIAL  PRIMARY KEY,
 401     lasso       INT     NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 402     org_unit    INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 403 );
 404 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
 405 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
 406
 407 CREATE TABLE actor.org_unit_proximity (
 408         id              BIGSERIAL       PRIMARY KEY,
 409         from_org        INT,
 410         to_org          INT,
 411         prox            INT
 412 );
 413 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
 414
 415 CREATE TABLE actor.stat_cat_entry_default (
 416         id              SERIAL  PRIMARY KEY,
 417         stat_cat_entry  INT     NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 418         stat_cat        INT     NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 419         owner           INT     NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 420         CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
 421 );
 422 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
 423 User Statistical Category Default Entry
 424
 425 A library may choose one of the stat_cat entries to be the
 426 default entry.
 427 $$;
 428
 429
 430 CREATE TABLE actor.org_unit_proximity_adjustment (
 431     id                  SERIAL   PRIMARY KEY,
 432     item_circ_lib       INT         REFERENCES actor.org_unit (id),
 433     item_owning_lib     INT         REFERENCES actor.org_unit (id),
 434     copy_location       INT,        -- REFERENCES asset.copy_location (id),
 435     hold_pickup_lib     INT         REFERENCES actor.org_unit (id),
 436     hold_request_lib    INT         REFERENCES actor.org_unit (id),
 437     pos                 INT         NOT NULL DEFAULT 0,
 438     absolute_adjustment BOOL        NOT NULL DEFAULT FALSE,
 439     prox_adjustment     NUMERIC,
 440     circ_mod            TEXT,       -- REFERENCES config.circ_modifier (code),
 441     CONSTRAINT prox_adj_criterium CHECK (COALESCE(item_circ_lib::TEXT,item_owning_lib::TEXT,copy_location::TEXT,hold_pickup_lib::TEXT,hold_request_lib::TEXT,circ_mod) IS NOT NULL)
 442 );
 443 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
 444     COALESCE(item_circ_lib, -1),
 445     COALESCE(item_owning_lib, -1),
 446     COALESCE(copy_location, -1),
 447     COALESCE(hold_pickup_lib, -1),
 448     COALESCE(hold_request_lib, -1),
 449     COALESCE(circ_mod, ''),
 450     pos
 451 );
 452 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
 453 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
 454 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
 455 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
 456 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
 457 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
 458
 459 CREATE TABLE actor.hours_of_operation (
 460         id              INT     PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 461         dow_0_open      TIME    NOT NULL DEFAULT '09:00',
 462         dow_0_close     TIME    NOT NULL DEFAULT '17:00',
 463         dow_1_open      TIME    NOT NULL DEFAULT '09:00',
 464         dow_1_close     TIME    NOT NULL DEFAULT '17:00',
 465         dow_2_open      TIME    NOT NULL DEFAULT '09:00',
 466         dow_2_close     TIME    NOT NULL DEFAULT '17:00',
 467         dow_3_open      TIME    NOT NULL DEFAULT '09:00',
 468         dow_3_close     TIME    NOT NULL DEFAULT '17:00',
 469         dow_4_open      TIME    NOT NULL DEFAULT '09:00',
 470         dow_4_close     TIME    NOT NULL DEFAULT '17:00',
 471         dow_5_open      TIME    NOT NULL DEFAULT '09:00',
 472         dow_5_close     TIME    NOT NULL DEFAULT '17:00',
 473         dow_6_open      TIME    NOT NULL DEFAULT '09:00',
 474         dow_6_close     TIME    NOT NULL DEFAULT '17:00'
 475 );
 476 COMMENT ON TABLE actor.hours_of_operation IS $$
 477 When does this org_unit usually open and close?  (Variations
 478 are expressed in the actor.org_unit_closed table.)
 479 $$;
 480 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
 481 When does this org_unit open on Monday?
 482 $$;
 483 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
 484 When does this org_unit close on Monday?
 485 $$;
 486 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
 487 When does this org_unit open on Tuesday?
 488 $$;
 489 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
 490 When does this org_unit close on Tuesday?
 491 $$;
 492 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
 493 When does this org_unit open on Wednesday?
 494 $$;
 495 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
 496 When does this org_unit close on Wednesday?
 497 $$;
 498 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
 499 When does this org_unit open on Thursday?
 500 $$;
 501 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
 502 When does this org_unit close on Thursday?
 503 $$;
 504 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
 505 When does this org_unit open on Friday?
 506 $$;
 507 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
 508 When does this org_unit close on Friday?
 509 $$;
 510 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
 511 When does this org_unit open on Saturday?
 512 $$;
 513 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
 514 When does this org_unit close on Saturday?
 515 $$;
 516 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
 517 When does this org_unit open on Sunday?
 518 $$;
 519 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
 520 When does this org_unit close on Sunday?
 521 $$;
 522
 523 CREATE TABLE actor.org_unit_closed (
 524         id              SERIAL                          PRIMARY KEY,
 525         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 526         close_start     TIMESTAMP WITH TIME ZONE        NOT NULL,
 527         close_end       TIMESTAMP WITH TIME ZONE        NOT NULL,
 528         reason          TEXT
 529 );
 530
 531 -- Workstation registration...
 532 CREATE TABLE actor.workstation (
 533         id              SERIAL  PRIMARY KEY,
 534         name            TEXT    NOT NULL UNIQUE,
 535         owning_lib      INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 536 );
 537
 538 CREATE TABLE actor.usr_org_unit_opt_in (
 539         id              SERIAL                          PRIMARY KEY,
 540         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 541         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 542         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 543         opt_in_ts       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 544         opt_in_ws       INT                             NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
 545         CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
 546 );
 547 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
 548
 549 CREATE TABLE actor.org_unit_setting (
 550         id              BIGSERIAL       PRIMARY KEY,
 551         org_unit        INT             NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 552         name            TEXT    NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
 553         value           TEXT            NOT NULL,
 554         CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
 555         CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
 556 );
 557 COMMENT ON TABLE actor.org_unit_setting IS $$
 558 Org Unit settings
 559
 560 This table contains any arbitrary settings that a client
 561 program would like to save for an org unit.
 562 $$;
 563
 564 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
 565
 566 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
 567 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
 568     DECLARE
 569     original TEXT;
 570     BEGIN
 571         -- Check for which setting is being updated, and log it.
 572         SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
 573
 574         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
 575
 576         RETURN NEW;
 577     END;
 578 $ous_change_log$ LANGUAGE plpgsql;
 579
 580 CREATE TRIGGER log_ous_change
 581     BEFORE INSERT OR UPDATE ON actor.org_unit_setting
 582     FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
 583
 584 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
 585     DECLARE
 586     original TEXT;
 587     BEGIN
 588         -- Check for which setting is being updated, and log it.
 589         SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
 590
 591         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
 592
 593         RETURN OLD;
 594     END;
 595 $ous_delete_log$ LANGUAGE plpgsql;
 596
 597 CREATE TRIGGER log_ous_del
 598     BEFORE DELETE ON actor.org_unit_setting
 599     FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
 600
 601
 602
 603
 604 CREATE TABLE actor.usr_address (
 605         id                      SERIAL  PRIMARY KEY,
 606         valid                   BOOL    NOT NULL DEFAULT TRUE,
 607         within_city_limits      BOOL    NOT NULL DEFAULT TRUE,
 608         address_type            TEXT    NOT NULL DEFAULT 'MAILING',
 609         usr                     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 610         street1                 TEXT    NOT NULL,
 611         street2                 TEXT,
 612         city                    TEXT    NOT NULL,
 613         county                  TEXT,
 614         state                   TEXT,
 615         country                 TEXT    NOT NULL,
 616         post_code               TEXT    NOT NULL,
 617     pending         BOOL    NOT NULL DEFAULT FALSE,
 618         replaces            INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
 619 );
 620
 621 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
 622
 623 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
 624 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
 625
 626 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
 627 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
 628 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
 629
 630 CREATE TABLE actor.usr_password_reset (
 631   id SERIAL PRIMARY KEY,
 632   uuid TEXT NOT NULL,
 633   usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
 634   request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 635   has_been_reset BOOL NOT NULL DEFAULT false
 636 );
 637 COMMENT ON TABLE actor.usr_password_reset IS $$
 638 Self-serve password reset requests
 639 $$;
 640 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
 641 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
 642 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
 643 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
 644
 645 CREATE TABLE actor.org_address (
 646         id              SERIAL  PRIMARY KEY,
 647         valid           BOOL    NOT NULL DEFAULT TRUE,
 648         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 649         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 650         street1         TEXT    NOT NULL,
 651         street2         TEXT,
 652         city            TEXT    NOT NULL,
 653         county          TEXT,
 654         state           TEXT,
 655         country         TEXT    NOT NULL,
 656         post_code       TEXT    NOT NULL,
 657     san         TEXT
 658 );
 659
 660 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
 661
 662 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
 663         SELECT SUBSTRING( $1, 1, 5);
 664 $$ LANGUAGE SQL;
 665
 666 CREATE TABLE actor.usr_standing_penalty (
 667         id                      SERIAL  PRIMARY KEY,
 668         org_unit                INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 669         usr                     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 670         standing_penalty        INT     NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 671         staff                   INT     REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 672         set_date                TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 673         stop_date               TIMESTAMP WITH TIME ZONE,
 674         note                    TEXT
 675 );
 676 COMMENT ON TABLE actor.usr_standing_penalty IS $$
 677 User standing penalties
 678 $$;
 679
 680 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
 681 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
 682
 683
 684 CREATE TABLE actor.usr_saved_search (
 685     id              SERIAL          PRIMARY KEY,
 686         owner           INT             NOT NULL REFERENCES actor.usr (id)
 687                                         ON DELETE CASCADE
 688                                         DEFERRABLE INITIALLY DEFERRED,
 689         name            TEXT            NOT NULL,
 690         create_date     TIMESTAMPTZ     NOT NULL DEFAULT now(),
 691         query_text      TEXT            NOT NULL,
 692         query_type      TEXT            NOT NULL
 693                                         CONSTRAINT valid_query_text CHECK (
 694                                         query_type IN ( 'URL' )) DEFAULT 'URL',
 695                                         -- we may add other types someday
 696         target          TEXT            NOT NULL
 697                                         CONSTRAINT valid_target CHECK (
 698                                         target IN ( 'record', 'metarecord', 'callnumber' )),
 699         CONSTRAINT name_once_per_user UNIQUE (owner, name)
 700 );
 701
 702 CREATE TABLE actor.address_alert (
 703     id              SERIAL  PRIMARY KEY,
 704     owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 705     active          BOOL    NOT NULL DEFAULT TRUE,
 706     match_all       BOOL    NOT NULL DEFAULT TRUE,
 707     alert_message   TEXT    NOT NULL,
 708     street1         TEXT,
 709     street2         TEXT,
 710     city            TEXT,
 711     county          TEXT,
 712     state           TEXT,
 713     country         TEXT,
 714     post_code       TEXT,
 715     mailing_address BOOL    NOT NULL DEFAULT FALSE,
 716     billing_address BOOL    NOT NULL DEFAULT FALSE
 717 );
 718
 719 CREATE TABLE actor.usr_activity (
 720     id          BIGSERIAL   PRIMARY KEY,
 721     usr         INT         REFERENCES actor.usr (id) ON DELETE SET NULL,
 722     etype       INT         NOT NULL REFERENCES config.usr_activity_type (id),
 723     event_time  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 724 );
 725 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
 726
 727 CREATE TABLE actor.toolbar (
 728     id          BIGSERIAL   PRIMARY KEY,
 729     ws          INT         REFERENCES actor.workstation (id) ON DELETE CASCADE,
 730     org         INT         REFERENCES actor.org_unit (id) ON DELETE CASCADE,
 731     usr         INT         REFERENCES actor.usr (id) ON DELETE CASCADE,
 732     label       TEXT        NOT NULL,
 733     layout      TEXT        NOT NULL,
 734     CONSTRAINT only_one_type CHECK (
 735         (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
 736         (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
 737         (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
 738     ),
 739     CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
 740 );
 741 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
 742 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
 743 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
 744
 745 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
 746 CREATE TABLE actor.org_unit_custom_tree (
 747     id              SERIAL  PRIMARY KEY,
 748     active          BOOLEAN DEFAULT FALSE,
 749     purpose         actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
 750 );
 751
 752 CREATE TABLE actor.org_unit_custom_tree_node (
 753     id              SERIAL  PRIMARY KEY,
 754     tree            INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
 755         org_unit        INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 756         parent_node     INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
 757     sibling_order   INTEGER NOT NULL DEFAULT 0,
 758     CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
 759 );
 760
 761 CREATE TABLE actor.search_query (
 762     id          SERIAL PRIMARY KEY,
 763     label       TEXT NOT NULL, -- i18n
 764     query_text  TEXT NOT NULL -- QP text
 765 );
 766
 767 CREATE TABLE actor.search_filter_group (
 768     id          SERIAL      PRIMARY KEY,
 769     owner       INT         NOT NULL REFERENCES actor.org_unit (id)
 770                             ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 771     code        TEXT        NOT NULL, -- for CGI, etc.
 772     label       TEXT        NOT NULL, -- i18n
 773     create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
 774     CONSTRAINT  asfg_label_once_per_org UNIQUE (owner, label),
 775     CONSTRAINT  asfg_code_once_per_org UNIQUE (owner, code)
 776 );
 777
 778 CREATE TABLE actor.search_filter_group_entry (
 779     id          SERIAL  PRIMARY KEY,
 780     grp         INT     NOT NULL REFERENCES actor.search_filter_group(id)
 781                         ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 782     pos         INT     NOT NULL DEFAULT 0,
 783     query       INT     NOT NULL REFERENCES actor.search_query(id)
 784                         ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 785     CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
 786 );
 787
 788 COMMIT;