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     guardian        TEXT,
  46     pref_prefix TEXT,
  47     pref_first_given_name TEXT,
  48     pref_second_given_name TEXT,
  49     pref_family_name TEXT,
  50     pref_suffix TEXT,
  51     name_keywords TEXT,
  52     name_kw_tsvector TSVECTOR,
  53         alias                   TEXT,
  54         day_phone               TEXT,
  55         evening_phone           TEXT,
  56         other_phone             TEXT,
  57         mailing_address         INT,
  58         billing_address         INT,
  59         home_ou                 INT                             NOT NULL,
  60         dob                     DATE,
  61         active                  BOOL                            NOT NULL DEFAULT TRUE,
  62         master_account          BOOL                            NOT NULL DEFAULT FALSE,
  63         super_user              BOOL                            NOT NULL DEFAULT FALSE,
  64         barred                  BOOL                            NOT NULL DEFAULT FALSE,
  65         deleted                 BOOL                            NOT NULL DEFAULT FALSE,
  66         juvenile                BOOL                            NOT NULL DEFAULT FALSE,
  67         usrgroup                SERIAL                          NOT NULL,
  68         claims_returned_count   INT                             NOT NULL DEFAULT 0,
  69         credit_forward_balance  NUMERIC(6,2)                    NOT NULL DEFAULT 0.00,
  70         last_xact_id            TEXT                            NOT NULL DEFAULT 'none',
  71         create_date             TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
  72         expire_date             TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT (now() + '3 years'::INTERVAL),
  73         claims_never_checked_out_count  INT         NOT NULL DEFAULT 0,
  74     last_update_time    TIMESTAMP WITH TIME ZONE
  75 );
  76 COMMENT ON TABLE actor.usr IS $$
  77 User objects
  78
  79 This table contains the core User objects that describe both
  80 staff members and patrons.  The difference between the two
  81 types of users is based on the user's permissions.
  82 $$;
  83
  84 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
  85 CREATE INDEX actor_usr_usrgroup_idx ON actor.usr (usrgroup);
  86 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
  87 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
  88
  89 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name));
  90 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name));
  91 CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name));
  92 CREATE INDEX actor_usr_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(first_given_name));
  93 CREATE INDEX actor_usr_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(second_given_name));
  94 CREATE INDEX actor_usr_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(family_name));
  95 CREATE INDEX actor_usr_usrname_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(usrname));
  96 CREATE INDEX actor_usr_guardian_idx ON actor.usr (evergreen.lowercase(guardian));
  97 CREATE INDEX actor_usr_guardian_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(guardian));
  98
  99 CREATE INDEX actor_usr_pref_first_given_name_idx ON actor.usr (evergreen.lowercase(pref_first_given_name));
 100 CREATE INDEX actor_usr_pref_second_given_name_idx ON actor.usr (evergreen.lowercase(pref_second_given_name));
 101 CREATE INDEX actor_usr_pref_family_name_idx ON actor.usr (evergreen.lowercase(pref_family_name));
 102 CREATE INDEX actor_usr_pref_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(pref_first_given_name));
 103 CREATE INDEX actor_usr_pref_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(pref_second_given_name));
 104 CREATE INDEX actor_usr_pref_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(pref_family_name));
 105
 106 CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname));
 107 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
 108
 109 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
 110 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
 111 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
 112
 113 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
 114     (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
 115
 116 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
 117     (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
 118
 119 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
 120     (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
 121
 122 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
 123 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
 124
 125 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
 126         BEGIN
 127                 NEW.passwd = MD5( NEW.passwd );
 128                 RETURN NEW;
 129         END;
 130 $$ LANGUAGE PLPGSQL;
 131
 132 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
 133         BEGIN
 134                 IF NEW.passwd <> OLD.passwd THEN
 135                         NEW.passwd = MD5( NEW.passwd );
 136                 END IF;
 137                 RETURN NEW;
 138         END;
 139 $$ LANGUAGE PLPGSQL;
 140
 141 CREATE OR REPLACE FUNCTION actor.au_updated()
 142 RETURNS TRIGGER AS $$
 143 BEGIN
 144     NEW.last_update_time := now();
 145         RETURN NEW;
 146 END;
 147 $$ LANGUAGE plpgsql;
 148
 149 CREATE TRIGGER au_update_trig
 150         BEFORE INSERT OR UPDATE ON actor.usr
 151         FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
 152
 153 CREATE TRIGGER actor_crypt_pw_update_trigger
 154         BEFORE UPDATE ON actor.usr FOR EACH ROW
 155         EXECUTE PROCEDURE actor.crypt_pw_update ();
 156
 157 CREATE TRIGGER actor_crypt_pw_insert_trigger
 158         BEFORE INSERT ON actor.usr FOR EACH ROW
 159         EXECUTE PROCEDURE actor.crypt_pw_insert ();
 160
 161 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;
 162
 163 CREATE OR REPLACE FUNCTION actor.user_ingest_name_keywords()
 164     RETURNS TRIGGER AS $func$
 165 BEGIN
 166     NEW.name_kw_tsvector := TO_TSVECTOR(
 167         COALESCE(NEW.prefix, '')                || ' ' ||
 168         COALESCE(NEW.first_given_name, '')      || ' ' ||
 169         COALESCE(evergreen.unaccent_and_squash(NEW.first_given_name), '') || ' ' ||
 170         COALESCE(NEW.second_given_name, '')     || ' ' ||
 171         COALESCE(evergreen.unaccent_and_squash(NEW.second_given_name), '') || ' ' ||
 172         COALESCE(NEW.family_name, '')           || ' ' ||
 173         COALESCE(evergreen.unaccent_and_squash(NEW.family_name), '') || ' ' ||
 174         COALESCE(NEW.suffix, '')                || ' ' ||
 175         COALESCE(NEW.pref_prefix, '')            || ' ' ||
 176         COALESCE(NEW.pref_first_given_name, '')  || ' ' ||
 177         COALESCE(evergreen.unaccent_and_squash(NEW.pref_first_given_name), '') || ' ' ||
 178         COALESCE(NEW.pref_second_given_name, '') || ' ' ||
 179         COALESCE(evergreen.unaccent_and_squash(NEW.pref_second_given_name), '') || ' ' ||
 180         COALESCE(NEW.pref_family_name, '')       || ' ' ||
 181         COALESCE(evergreen.unaccent_and_squash(NEW.pref_family_name), '') || ' ' ||
 182         COALESCE(NEW.pref_suffix, '')            || ' ' ||
 183         COALESCE(NEW.name_keywords, '')
 184     );
 185     RETURN NEW;
 186 END;
 187 $func$ LANGUAGE PLPGSQL;
 188
 189 -- Add after the batch upate above to avoid duplicate updates.
 190 CREATE TRIGGER user_ingest_name_keywords_tgr
 191     BEFORE INSERT OR UPDATE ON actor.usr
 192     FOR EACH ROW EXECUTE PROCEDURE actor.user_ingest_name_keywords();
 193
 194 CREATE TABLE actor.usr_setting (
 195         id      BIGSERIAL       PRIMARY KEY,
 196         usr     INT             NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 197         name    TEXT            NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 198         value   TEXT            NOT NULL,
 199         CONSTRAINT usr_once_per_key UNIQUE (usr,name)
 200 );
 201 COMMENT ON TABLE actor.usr_setting IS $$
 202 User settings
 203
 204 This table contains any arbitrary settings that a client
 205 program would like to save for a user.
 206 $$;
 207
 208 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
 209
 210 CREATE TABLE actor.stat_cat_sip_fields (
 211     field   CHAR(2) PRIMARY KEY,
 212     name    TEXT    NOT NULL,
 213     one_only  BOOL    NOT NULL DEFAULT FALSE
 214 );
 215 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
 216 Actor Statistical Category SIP Fields
 217
 218 Contains the list of valid SIP Field identifiers for
 219 Statistical Categories.
 220 $$;
 221
 222 CREATE TABLE actor.stat_cat (
 223         id              SERIAL  PRIMARY KEY,
 224         owner           INT     NOT NULL,
 225         name            TEXT    NOT NULL,
 226         opac_visible    BOOL NOT NULL DEFAULT FALSE,
 227         usr_summary     BOOL NOT NULL DEFAULT FALSE,
 228     sip_field   CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 229     sip_format  TEXT,
 230     checkout_archive    BOOL NOT NULL DEFAULT FALSE,
 231         required        BOOL NOT NULL DEFAULT FALSE,
 232         allow_freetext  BOOL NOT NULL DEFAULT TRUE,
 233         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
 234 );
 235 COMMENT ON TABLE actor.stat_cat IS $$
 236 User Statistical Catagories
 237
 238 Local data collected about Users is placed into a Statistical
 239 Catagory.  Here's where those catagories are defined.
 240 $$;
 241
 242
 243 CREATE TABLE actor.stat_cat_entry (
 244         id              SERIAL  PRIMARY KEY,
 245         stat_cat        INT     NOT NULL,
 246         owner           INT     NOT NULL,
 247         value           TEXT    NOT NULL,
 248         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
 249 );
 250 COMMENT ON TABLE actor.stat_cat_entry IS $$
 251 User Statistical Catagory Entries
 252
 253 Local data collected about Users is placed into a Statistical
 254 Catagory.  Each library can create entries into any of its own
 255 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
 256 $$;
 257
 258
 259 CREATE TABLE actor.stat_cat_entry_usr_map (
 260         id              BIGSERIAL       PRIMARY KEY,
 261         stat_cat_entry  TEXT            NOT NULL,
 262         stat_cat        INT             NOT NULL,
 263         target_usr      INT             NOT NULL,
 264         CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
 265 );
 266 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
 267 Statistical Catagory Entry to User map
 268
 269 Records the stat_cat entries for each user.
 270 $$;
 271
 272 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
 273
 274 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
 275 DECLARE
 276     sipfield actor.stat_cat_sip_fields%ROWTYPE;
 277     use_count INT;
 278 BEGIN
 279     IF NEW.sip_field IS NOT NULL THEN
 280         SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
 281         IF sipfield.one_only THEN
 282             SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
 283             IF use_count > 0 THEN
 284                 RAISE EXCEPTION 'Sip field cannot be used twice';
 285             END IF;
 286         END IF;
 287     END IF;
 288     RETURN NEW;
 289 END;
 290 $func$ LANGUAGE PLPGSQL;
 291
 292 CREATE TRIGGER actor_stat_cat_sip_update_trigger
 293     BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
 294     EXECUTE PROCEDURE actor.stat_cat_check();
 295
 296 CREATE TABLE actor.card (
 297         id      SERIAL  PRIMARY KEY,
 298         usr     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 299         barcode TEXT    NOT NULL UNIQUE,
 300         active  BOOL    NOT NULL DEFAULT TRUE
 301 );
 302 COMMENT ON TABLE actor.card IS $$
 303 Library Cards
 304
 305 Each User has one or more library cards.  The current "main"
 306 card is linked to here from the actor.usr table, and it is up
 307 to the consortium policy whether more than one card can be
 308 active for any one user at a given time.
 309 $$;
 310
 311 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
 312 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
 313
 314 CREATE TABLE actor.org_unit_type (
 315         id              SERIAL  PRIMARY KEY,
 316         name            TEXT    NOT NULL,
 317         opac_label      TEXT    NOT NULL,
 318         depth           INT     NOT NULL,
 319         parent          INT     REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
 320         can_have_vols   BOOL    NOT NULL DEFAULT TRUE,
 321         can_have_users  BOOL    NOT NULL DEFAULT TRUE
 322 );
 323 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
 324
 325 CREATE TABLE actor.org_unit (
 326         id              SERIAL  PRIMARY KEY,
 327         parent_ou       INT     REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 328         ou_type         INT     NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
 329         ill_address     INT,
 330         holds_address   INT,
 331         mailing_address INT,
 332         billing_address INT,
 333         shortname       TEXT    NOT NULL UNIQUE,
 334         name            TEXT    NOT NULL UNIQUE,
 335         email           TEXT,
 336         phone           TEXT,
 337         opac_visible    BOOL    NOT NULL DEFAULT TRUE,
 338         fiscal_calendar INT     NOT NULL DEFAULT 1   -- foreign key constraint to be added later
 339 );
 340 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
 341 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
 342 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
 343 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
 344 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
 345 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
 346
 347 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
 348         DECLARE
 349                 current_aou actor.org_unit%ROWTYPE;
 350                 seen_ous    INT[];
 351                 depth_count INT;
 352         BEGIN
 353                 current_aou := NEW;
 354                 depth_count := 0;
 355                 seen_ous := ARRAY[NEW.id];
 356
 357                 IF (TG_OP = 'UPDATE') THEN
 358                         IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
 359                                 RETURN NEW; -- Doing an UPDATE with no change, just return it
 360                         END IF;
 361                 END IF;
 362
 363                 LOOP
 364                         IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
 365                                 RETURN NEW; -- No loop. Carry on.
 366                         END IF;
 367                         IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
 368                                 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
 369                         END IF;
 370                         -- Get the next one!
 371                         SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
 372                         seen_ous := seen_ous || current_aou.id;
 373                         depth_count := depth_count + 1;
 374                         IF depth_count = 100 THEN
 375                                 RAISE 'OU CHECK TOO DEEP';
 376                         END IF;
 377                 END LOOP;
 378
 379                 RETURN NEW;
 380         END;
 381 $$ LANGUAGE PLPGSQL;
 382
 383 CREATE TRIGGER actor_org_unit_parent_protect_trigger
 384     BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
 385     EXECUTE PROCEDURE actor.org_unit_parent_protect ();
 386
 387 CREATE TABLE actor.org_lasso (
 388     id      SERIAL  PRIMARY KEY,
 389     name        TEXT    UNIQUE,
 390     global  BOOL    NOT NULL DEFAULT FALSE
 391 );
 392
 393 CREATE TABLE actor.org_lasso_map (
 394     id          SERIAL  PRIMARY KEY,
 395     lasso       INT     NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 396     org_unit    INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 397 );
 398 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
 399 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
 400
 401 CREATE TABLE actor.org_unit_proximity (
 402         id              BIGSERIAL       PRIMARY KEY,
 403         from_org        INT,
 404         to_org          INT,
 405         prox            INT
 406 );
 407 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
 408
 409 CREATE TABLE actor.stat_cat_entry_default (
 410         id              SERIAL  PRIMARY KEY,
 411         stat_cat_entry  INT     NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 412         stat_cat        INT     NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 413         owner           INT     NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 414         CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
 415 );
 416 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
 417 User Statistical Category Default Entry
 418
 419 A library may choose one of the stat_cat entries to be the
 420 default entry.
 421 $$;
 422
 423
 424 CREATE TABLE actor.org_unit_proximity_adjustment (
 425     id                  SERIAL   PRIMARY KEY,
 426     item_circ_lib       INT         REFERENCES actor.org_unit (id),
 427     item_owning_lib     INT         REFERENCES actor.org_unit (id),
 428     copy_location       INT,        -- REFERENCES asset.copy_location (id),
 429     hold_pickup_lib     INT         REFERENCES actor.org_unit (id),
 430     hold_request_lib    INT         REFERENCES actor.org_unit (id),
 431     pos                 INT         NOT NULL DEFAULT 0,
 432     absolute_adjustment BOOL        NOT NULL DEFAULT FALSE,
 433     prox_adjustment     NUMERIC,
 434     circ_mod            TEXT,       -- REFERENCES config.circ_modifier (code),
 435     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)
 436 );
 437 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
 438     COALESCE(item_circ_lib, -1),
 439     COALESCE(item_owning_lib, -1),
 440     COALESCE(copy_location, -1),
 441     COALESCE(hold_pickup_lib, -1),
 442     COALESCE(hold_request_lib, -1),
 443     COALESCE(circ_mod, ''),
 444     pos
 445 );
 446 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
 447 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
 448 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
 449 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
 450 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
 451 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
 452
 453 CREATE TABLE actor.hours_of_operation (
 454         id              INT     PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 455         dow_0_open      TIME    NOT NULL DEFAULT '09:00',
 456         dow_0_close     TIME    NOT NULL DEFAULT '17:00',
 457         dow_1_open      TIME    NOT NULL DEFAULT '09:00',
 458         dow_1_close     TIME    NOT NULL DEFAULT '17:00',
 459         dow_2_open      TIME    NOT NULL DEFAULT '09:00',
 460         dow_2_close     TIME    NOT NULL DEFAULT '17:00',
 461         dow_3_open      TIME    NOT NULL DEFAULT '09:00',
 462         dow_3_close     TIME    NOT NULL DEFAULT '17:00',
 463         dow_4_open      TIME    NOT NULL DEFAULT '09:00',
 464         dow_4_close     TIME    NOT NULL DEFAULT '17:00',
 465         dow_5_open      TIME    NOT NULL DEFAULT '09:00',
 466         dow_5_close     TIME    NOT NULL DEFAULT '17:00',
 467         dow_6_open      TIME    NOT NULL DEFAULT '09:00',
 468         dow_6_close     TIME    NOT NULL DEFAULT '17:00'
 469 );
 470 COMMENT ON TABLE actor.hours_of_operation IS $$
 471 When does this org_unit usually open and close?  (Variations
 472 are expressed in the actor.org_unit_closed table.)
 473 $$;
 474 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
 475 When does this org_unit open on Monday?
 476 $$;
 477 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
 478 When does this org_unit close on Monday?
 479 $$;
 480 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
 481 When does this org_unit open on Tuesday?
 482 $$;
 483 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
 484 When does this org_unit close on Tuesday?
 485 $$;
 486 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
 487 When does this org_unit open on Wednesday?
 488 $$;
 489 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
 490 When does this org_unit close on Wednesday?
 491 $$;
 492 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
 493 When does this org_unit open on Thursday?
 494 $$;
 495 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
 496 When does this org_unit close on Thursday?
 497 $$;
 498 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
 499 When does this org_unit open on Friday?
 500 $$;
 501 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
 502 When does this org_unit close on Friday?
 503 $$;
 504 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
 505 When does this org_unit open on Saturday?
 506 $$;
 507 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
 508 When does this org_unit close on Saturday?
 509 $$;
 510 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
 511 When does this org_unit open on Sunday?
 512 $$;
 513 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
 514 When does this org_unit close on Sunday?
 515 $$;
 516
 517 CREATE TABLE actor.org_unit_closed (
 518         id              SERIAL                          PRIMARY KEY,
 519         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 520         close_start     TIMESTAMP WITH TIME ZONE        NOT NULL,
 521         close_end       TIMESTAMP WITH TIME ZONE        NOT NULL,
 522     full_day    BOOLEAN                     NOT NULL DEFAULT FALSE,
 523     multi_day   BOOLEAN                     NOT NULL DEFAULT FALSE,
 524         reason          TEXT
 525 );
 526
 527 -- Workstation registration...
 528 CREATE TABLE actor.workstation (
 529         id              SERIAL  PRIMARY KEY,
 530         name            TEXT    NOT NULL UNIQUE,
 531         owning_lib      INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 532 );
 533
 534 CREATE TABLE actor.usr_org_unit_opt_in (
 535         id              SERIAL                          PRIMARY KEY,
 536         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 537         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 538         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 539         opt_in_ts       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 540         opt_in_ws       INT                             NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
 541         CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
 542 );
 543 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
 544
 545 CREATE TABLE actor.org_unit_setting (
 546         id              BIGSERIAL       PRIMARY KEY,
 547         org_unit        INT             NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 548         name            TEXT    NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
 549         value           TEXT            NOT NULL,
 550         CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
 551         CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
 552 );
 553 COMMENT ON TABLE actor.org_unit_setting IS $$
 554 Org Unit settings
 555
 556 This table contains any arbitrary settings that a client
 557 program would like to save for an org unit.
 558 $$;
 559
 560 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
 561
 562 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
 563 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
 564     DECLARE
 565     original TEXT;
 566     BEGIN
 567         -- Check for which setting is being updated, and log it.
 568         SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
 569
 570         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
 571
 572         RETURN NEW;
 573     END;
 574 $ous_change_log$ LANGUAGE plpgsql;
 575
 576 CREATE TRIGGER log_ous_change
 577     BEFORE INSERT OR UPDATE ON actor.org_unit_setting
 578     FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
 579
 580 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
 581     DECLARE
 582     original TEXT;
 583     BEGIN
 584         -- Check for which setting is being updated, and log it.
 585         SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
 586
 587         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
 588
 589         RETURN OLD;
 590     END;
 591 $ous_delete_log$ LANGUAGE plpgsql;
 592
 593 CREATE TRIGGER log_ous_del
 594     BEFORE DELETE ON actor.org_unit_setting
 595     FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
 596
 597
 598
 599
 600 CREATE TABLE actor.usr_address (
 601         id                      SERIAL  PRIMARY KEY,
 602         valid                   BOOL    NOT NULL DEFAULT TRUE,
 603         within_city_limits      BOOL    NOT NULL DEFAULT TRUE,
 604         address_type            TEXT    NOT NULL DEFAULT 'MAILING',
 605         usr                     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 606         street1                 TEXT    NOT NULL,
 607         street2                 TEXT,
 608         city                    TEXT    NOT NULL,
 609         county                  TEXT,
 610         state                   TEXT,
 611         country                 TEXT    NOT NULL,
 612         post_code               TEXT    NOT NULL,
 613     pending         BOOL    NOT NULL DEFAULT FALSE,
 614         replaces            INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
 615 );
 616
 617 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
 618
 619 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
 620 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
 621
 622 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
 623 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
 624 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
 625
 626 CREATE TABLE actor.usr_password_reset (
 627   id SERIAL PRIMARY KEY,
 628   uuid TEXT NOT NULL,
 629   usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
 630   request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 631   has_been_reset BOOL NOT NULL DEFAULT false
 632 );
 633 COMMENT ON TABLE actor.usr_password_reset IS $$
 634 Self-serve password reset requests
 635 $$;
 636 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
 637 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
 638 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
 639 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
 640
 641 CREATE TABLE actor.org_address (
 642         id              SERIAL  PRIMARY KEY,
 643         valid           BOOL    NOT NULL DEFAULT TRUE,
 644         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 645         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 646         street1         TEXT    NOT NULL,
 647         street2         TEXT,
 648         city            TEXT    NOT NULL,
 649         county          TEXT,
 650         state           TEXT,
 651         country         TEXT    NOT NULL,
 652         post_code       TEXT    NOT NULL,
 653         san                     TEXT,
 654         latitude        FLOAT,
 655         longitude       FLOAT
 656 );
 657
 658 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
 659
 660 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
 661         SELECT SUBSTRING( $1, 1, 5);
 662 $$ LANGUAGE SQL;
 663
 664 CREATE TABLE actor.usr_standing_penalty (
 665         id                      SERIAL  PRIMARY KEY,
 666         org_unit                INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 667         usr                     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 668         standing_penalty        INT     NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 669         staff                   INT     REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 670         set_date                TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 671         stop_date               TIMESTAMP WITH TIME ZONE
 672 );
 673 COMMENT ON TABLE actor.usr_standing_penalty IS $$
 674 User standing penalties
 675 $$;
 676
 677 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
 678 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
 679
 680
 681 CREATE TABLE actor.usr_saved_search (
 682     id              SERIAL          PRIMARY KEY,
 683         owner           INT             NOT NULL REFERENCES actor.usr (id)
 684                                         ON DELETE CASCADE
 685                                         DEFERRABLE INITIALLY DEFERRED,
 686         name            TEXT            NOT NULL,
 687         create_date     TIMESTAMPTZ     NOT NULL DEFAULT now(),
 688         query_text      TEXT            NOT NULL,
 689         query_type      TEXT            NOT NULL
 690                                         CONSTRAINT valid_query_text CHECK (
 691                                         query_type IN ( 'URL' )) DEFAULT 'URL',
 692                                         -- we may add other types someday
 693         target          TEXT            NOT NULL
 694                                         CONSTRAINT valid_target CHECK (
 695                                         target IN ( 'record', 'metarecord', 'callnumber' )),
 696         CONSTRAINT name_once_per_user UNIQUE (owner, name)
 697 );
 698
 699 CREATE TABLE actor.address_alert (
 700     id              SERIAL  PRIMARY KEY,
 701     owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 702     active          BOOL    NOT NULL DEFAULT TRUE,
 703     match_all       BOOL    NOT NULL DEFAULT TRUE,
 704     alert_message   TEXT    NOT NULL,
 705     street1         TEXT,
 706     street2         TEXT,
 707     city            TEXT,
 708     county          TEXT,
 709     state           TEXT,
 710     country         TEXT,
 711     post_code       TEXT,
 712     mailing_address BOOL    NOT NULL DEFAULT FALSE,
 713     billing_address BOOL    NOT NULL DEFAULT FALSE
 714 );
 715
 716 CREATE TABLE actor.usr_activity (
 717     id          BIGSERIAL   PRIMARY KEY,
 718     usr         INT         REFERENCES actor.usr (id) ON DELETE SET NULL,
 719     etype       INT         NOT NULL REFERENCES config.usr_activity_type (id),
 720     event_time  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 721 );
 722 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
 723
 724 CREATE TABLE actor.toolbar (
 725     id          BIGSERIAL   PRIMARY KEY,
 726     ws          INT         REFERENCES actor.workstation (id) ON DELETE CASCADE,
 727     org         INT         REFERENCES actor.org_unit (id) ON DELETE CASCADE,
 728     usr         INT         REFERENCES actor.usr (id) ON DELETE CASCADE,
 729     label       TEXT        NOT NULL,
 730     layout      TEXT        NOT NULL,
 731     CONSTRAINT only_one_type CHECK (
 732         (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
 733         (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
 734         (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
 735     ),
 736     CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
 737 );
 738 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
 739 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
 740 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
 741
 742 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
 743 CREATE TABLE actor.org_unit_custom_tree (
 744     id              SERIAL  PRIMARY KEY,
 745     active          BOOLEAN DEFAULT FALSE,
 746     purpose         actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
 747 );
 748
 749 CREATE TABLE actor.org_unit_custom_tree_node (
 750     id              SERIAL  PRIMARY KEY,
 751     tree            INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
 752         org_unit        INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 753         parent_node     INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
 754     sibling_order   INTEGER NOT NULL DEFAULT 0,
 755     CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
 756 );
 757
 758 CREATE TABLE actor.search_query (
 759     id          SERIAL PRIMARY KEY,
 760     label       TEXT NOT NULL, -- i18n
 761     query_text  TEXT NOT NULL -- QP text
 762 );
 763
 764 CREATE TABLE actor.search_filter_group (
 765     id          SERIAL      PRIMARY KEY,
 766     owner       INT         NOT NULL REFERENCES actor.org_unit (id)
 767                             ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 768     code        TEXT        NOT NULL, -- for CGI, etc.
 769     label       TEXT        NOT NULL, -- i18n
 770     create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
 771     CONSTRAINT  asfg_label_once_per_org UNIQUE (owner, label),
 772     CONSTRAINT  asfg_code_once_per_org UNIQUE (owner, code)
 773 );
 774
 775 CREATE TABLE actor.search_filter_group_entry (
 776     id          SERIAL  PRIMARY KEY,
 777     grp         INT     NOT NULL REFERENCES actor.search_filter_group(id)
 778                         ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 779     pos         INT     NOT NULL DEFAULT 0,
 780     query       INT     NOT NULL REFERENCES actor.search_query(id)
 781                         ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 782     CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
 783 );
 784
 785 CREATE TABLE actor.usr_message (
 786         id              SERIAL                          PRIMARY KEY,
 787         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 788         title           TEXT,
 789         message         TEXT                            NOT NULL,
 790         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 791         deleted         BOOL                            NOT NULL DEFAULT FALSE,
 792         read_date       TIMESTAMP WITH TIME ZONE,
 793         sending_lib     INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 794         pub             BOOL                            NOT NULL DEFAULT FALSE,
 795         stop_date               TIMESTAMP WITH TIME ZONE,
 796         editor  BIGINT REFERENCES actor.usr (id),
 797         edit_date               TIMESTAMP WITH TIME ZONE
 798 );
 799 CREATE INDEX aum_usr ON actor.usr_message (usr);
 800 ALTER TABLE actor.usr_standing_penalty ALTER COLUMN id SET DEFAULT nextval('actor.usr_message_id_seq'::regclass);
 801 ALTER TABLE actor.usr_standing_penalty ADD COLUMN usr_message BIGINT REFERENCES actor.usr_message(id);
 802 CREATE INDEX usr_standing_penalty_usr_message_idx ON actor.usr_standing_penalty (usr_message);
 803
 804 CREATE RULE protect_usr_message_delete AS
 805         ON DELETE TO actor.usr_message DO INSTEAD (
 806                 UPDATE  actor.usr_message
 807                   SET   deleted = TRUE
 808                   WHERE OLD.id = actor.usr_message.id
 809         );
 810
 811 -- limited view to ensure that a library user who somehow
 812 -- manages to figure out how to access pcrud cannot change
 813 -- the text of messages sent them
 814 CREATE VIEW actor.usr_message_limited
 815 AS SELECT * FROM actor.usr_message WHERE pub AND NOT deleted;
 816
 817 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
 818 BEGIN
 819     IF TG_OP = 'UPDATE' THEN
 820         UPDATE actor.usr_message
 821         SET    read_date = NEW.read_date,
 822                deleted   = NEW.deleted
 823         WHERE  id = NEW.id;
 824         RETURN NEW;
 825     END IF;
 826     RETURN NULL;
 827 END;
 828 $$ LANGUAGE PLPGSQL;
 829
 830 CREATE TRIGGER restrict_usr_message_limited_tgr
 831     INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
 832     FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
 833
 834 -- combined view of actor.usr_standing_penalty and actor.usr_message for populating
 835 -- staff Notes (formerly Messages) interface
 836
 837 CREATE VIEW actor.usr_message_penalty AS
 838 SELECT -- ausp with or without messages
 839     ausp.id AS "id",
 840     ausp.id AS "ausp_id",
 841     aum.id AS "aum_id",
 842     ausp.org_unit AS "org_unit",
 843     ausp.org_unit AS "ausp_org_unit",
 844     aum.sending_lib AS "aum_sending_lib",
 845     ausp.usr AS "usr",
 846     ausp.usr as "ausp_usr",
 847     aum.usr as "aum_usr",
 848     ausp.standing_penalty AS "standing_penalty",
 849     ausp.staff AS "staff",
 850     ausp.set_date AS "create_date",
 851     ausp.set_date AS "ausp_set_date",
 852     aum.create_date AS "aum_create_date",
 853     ausp.stop_date AS "stop_date",
 854     ausp.stop_date AS "ausp_stop_date",
 855     aum.stop_date AS "aum_stop_date",
 856     ausp.usr_message AS "ausp_usr_message",
 857     aum.title AS "title",
 858     aum.message AS "message",
 859     aum.deleted AS "deleted",
 860     aum.read_date AS "read_date",
 861     aum.pub AS "pub",
 862     aum.editor AS "editor",
 863     aum.edit_date AS "edit_date"
 864 FROM
 865     actor.usr_standing_penalty ausp
 866     LEFT JOIN actor.usr_message aum ON (ausp.usr_message = aum.id)
 867         UNION ALL
 868 SELECT -- aum without penalties
 869     aum.id AS "id",
 870     NULL::INT AS "ausp_id",
 871     aum.id AS "aum_id",
 872     aum.sending_lib AS "org_unit",
 873     NULL::INT AS "ausp_org_unit",
 874     aum.sending_lib AS "aum_sending_lib",
 875     aum.usr AS "usr",
 876     NULL::INT as "ausp_usr",
 877     aum.usr as "aum_usr",
 878     NULL::INT AS "standing_penalty",
 879     NULL::INT AS "staff",
 880     aum.create_date AS "create_date",
 881     NULL::TIMESTAMPTZ AS "ausp_set_date",
 882     aum.create_date AS "aum_create_date",
 883     aum.stop_date AS "stop_date",
 884     NULL::TIMESTAMPTZ AS "ausp_stop_date",
 885     aum.stop_date AS "aum_stop_date",
 886     NULL::INT AS "ausp_usr_message",
 887     aum.title AS "title",
 888     aum.message AS "message",
 889     aum.deleted AS "deleted",
 890     aum.read_date AS "read_date",
 891     aum.pub AS "pub",
 892     aum.editor AS "editor",
 893     aum.edit_date AS "edit_date"
 894 FROM
 895     actor.usr_message aum
 896     LEFT JOIN actor.usr_standing_penalty ausp ON (ausp.usr_message = aum.id)
 897 WHERE NOT aum.deleted AND ausp.id IS NULL
 898 ;
 899
 900 CREATE TABLE actor.passwd_type (
 901     code        TEXT PRIMARY KEY,
 902     name        TEXT UNIQUE NOT NULL,
 903     login       BOOLEAN NOT NULL DEFAULT FALSE,
 904     regex       TEXT,   -- pending
 905     crypt_algo  TEXT,   -- e.g. 'bf'
 906
 907     -- gen_salt() iter count used with each new salt.
 908     -- A non-NULL value for iter_count is our indication the
 909     -- password is salted and encrypted via crypt()
 910     iter_count  INTEGER CHECK (iter_count IS NULL OR iter_count > 0)
 911 );
 912
 913 CREATE TABLE actor.passwd (
 914     id          SERIAL PRIMARY KEY,
 915     usr         INTEGER NOT NULL REFERENCES actor.usr(id)
 916                 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 917     salt        TEXT, -- will be NULL for non-crypt'ed passwords
 918     passwd      TEXT NOT NULL,
 919     passwd_type TEXT NOT NULL REFERENCES actor.passwd_type(code)
 920                 DEFERRABLE INITIALLY DEFERRED,
 921     create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 922     edit_date   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 923     CONSTRAINT  passwd_type_once_per_user UNIQUE (usr, passwd_type)
 924 );
 925
 926 CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT)
 927     RETURNS TEXT AS $$
 928 DECLARE
 929     type_row actor.passwd_type%ROWTYPE;
 930 BEGIN
 931     /* Returns a new salt based on the passwd_type encryption settings.
 932      * Returns NULL If the password type is not crypt()'ed.
 933      */
 934
 935     SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;
 936
 937     IF NOT FOUND THEN
 938         RETURN EXCEPTION 'No such password type: %', pw_type;
 939     END IF;
 940
 941     IF type_row.iter_count IS NULL THEN
 942         -- This password type is unsalted.  That's OK.
 943         RETURN NULL;
 944     END IF;
 945
 946     RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
 947 END;
 948 $$ LANGUAGE PLPGSQL;
 949
 950
 951 /*
 952     TODO: when a user changes their password in the application, the
 953     app layer has access to the bare password.  At that point, we have
 954     the opportunity to store the new password without the MD5(MD5())
 955     intermediate hashing.  Do we care?  We would need a way to indicate
 956     which passwords have the legacy intermediate hashing and which don't
 957     so the app layer would know whether it should perform the intermediate
 958     hashing.  In either event, with the exception of migrate_passwd(), the
 959     DB functions know or care nothing about intermediate hashing.  Every
 960     password is just a value that may or may not be internally crypt'ed.
 961 */
 962
 963 CREATE OR REPLACE FUNCTION actor.set_passwd(
 964     pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL)
 965     RETURNS BOOLEAN AS $$
 966 DECLARE
 967     pw_salt TEXT;
 968     pw_text TEXT;
 969 BEGIN
 970     /* Sets the password value, creating a new actor.passwd row if needed.
 971      * If the password type supports it, the new_pass value is crypt()'ed.
 972      * For crypt'ed passwords, the salt comes from one of 3 places in order:
 973      * new_salt (if present), existing salt (if present), newly created
 974      * salt.
 975      */
 976
 977     IF new_salt IS NOT NULL THEN
 978         pw_salt := new_salt;
 979     ELSE
 980         pw_salt := actor.get_salt(pw_usr, pw_type);
 981
 982         IF pw_salt IS NULL THEN
 983             /* We have no salt for this user + type.  Assume they want a
 984              * new salt.  If this type is unsalted, create_salt() will
 985              * return NULL. */
 986             pw_salt := actor.create_salt(pw_type);
 987         END IF;
 988     END IF;
 989
 990     IF pw_salt IS NULL THEN
 991         pw_text := new_pass; -- unsalted, use as-is.
 992     ELSE
 993         pw_text := CRYPT(new_pass, pw_salt);
 994     END IF;
 995
 996     UPDATE actor.passwd
 997         SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
 998         WHERE usr = pw_usr AND passwd_type = pw_type;
 999
1000     IF NOT FOUND THEN
1001         -- no password row exists for this user + type.  Create one.
1002         INSERT INTO actor.passwd (usr, passwd_type, salt, passwd)
1003             VALUES (pw_usr, pw_type, pw_salt, pw_text);
1004     END IF;
1005
1006     RETURN TRUE;
1007 END;
1008 $$ LANGUAGE PLPGSQL;
1009
1010 CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT)
1011     RETURNS TEXT AS $$
1012 DECLARE
1013     pw_salt TEXT;
1014     type_row actor.passwd_type%ROWTYPE;
1015 BEGIN
1016     /* Returns the salt for the requested user + type.  If the password
1017      * type of "main" is requested and no password exists in actor.passwd,
1018      * the user's existing password is migrated and the new salt is returned.
1019      * Returns NULL if the password type is not crypt'ed (iter_count is NULL).
1020      */
1021
1022     SELECT INTO pw_salt salt FROM actor.passwd
1023         WHERE usr = pw_usr AND passwd_type = pw_type;
1024
1025     IF FOUND THEN
1026         RETURN pw_salt;
1027     END IF;
1028
1029     IF pw_type = 'main' THEN
1030         -- Main password has not yet been migrated.
1031         -- Do it now and return the newly created salt.
1032         RETURN actor.migrate_passwd(pw_usr);
1033     END IF;
1034
1035     -- We have no salt to return.  actor.create_salt() needed.
1036     RETURN NULL;
1037 END;
1038 $$ LANGUAGE PLPGSQL;
1039
1040 CREATE OR REPLACE FUNCTION
1041     actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$
1042 DECLARE
1043     pw_salt TEXT;
1044     usr_row actor.usr%ROWTYPE;
1045 BEGIN
1046     /* Migrates legacy actor.usr.passwd value to actor.passwd with
1047      * a password type 'main' and returns the new salt.  For backwards
1048      * compatibility with existing CHAP-style API's, we perform a
1049      * layer of intermediate MD5(MD5()) hashing.  This is intermediate
1050      * hashing is not required of other passwords.
1051      */
1052
1053     -- Avoid calling get_salt() here, because it may result in a
1054     -- migrate_passwd() call, creating a loop.
1055     SELECT INTO pw_salt salt FROM actor.passwd
1056         WHERE usr = pw_usr AND passwd_type = 'main';
1057
1058     -- Only migrate passwords that have not already been migrated.
1059     IF FOUND THEN
1060         RETURN pw_salt;
1061     END IF;
1062
1063     SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
1064
1065     pw_salt := actor.create_salt('main');
1066
1067     PERFORM actor.set_passwd(
1068         pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
1069
1070     -- clear the existing password
1071     UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
1072
1073     RETURN pw_salt;
1074 END;
1075 $$ LANGUAGE PLPGSQL;
1076
1077 CREATE OR REPLACE FUNCTION
1078     actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT)
1079     RETURNS BOOLEAN AS $$
1080 DECLARE
1081     pw_salt TEXT;
1082 BEGIN
1083     /* Returns TRUE if the password provided matches the in-db password.
1084      * If the password type is salted, we compare the output of CRYPT().
1085      * NOTE: test_passwd is MD5(salt || MD5(password)) for legacy
1086      * 'main' passwords.
1087      */
1088
1089     SELECT INTO pw_salt salt FROM actor.passwd
1090         WHERE usr = pw_usr AND passwd_type = pw_type;
1091
1092     IF NOT FOUND THEN
1093         -- no such password
1094         RETURN FALSE;
1095     END IF;
1096
1097     IF pw_salt IS NULL THEN
1098         -- Password is unsalted, compare the un-CRYPT'ed values.
1099         RETURN EXISTS (
1100             SELECT TRUE FROM actor.passwd WHERE
1101                 usr = pw_usr AND
1102                 passwd_type = pw_type AND
1103                 passwd = test_passwd
1104         );
1105     END IF;
1106
1107     RETURN EXISTS (
1108         SELECT TRUE FROM actor.passwd WHERE
1109             usr = pw_usr AND
1110             passwd_type = pw_type AND
1111             passwd = CRYPT(test_passwd, pw_salt)
1112     );
1113 END;
1114 $$ STRICT LANGUAGE PLPGSQL;
1115
1116 -- Remove all activity entries by activity type,
1117 -- except the most recent entry per user.
1118 CREATE OR REPLACE FUNCTION
1119     actor.purge_usr_activity_by_type(act_type INTEGER)
1120     RETURNS VOID AS $$
1121 DECLARE
1122     cur_usr INTEGER;
1123 BEGIN
1124     FOR cur_usr IN SELECT DISTINCT(usr)
1125         FROM actor.usr_activity WHERE etype = act_type LOOP
1126         DELETE FROM actor.usr_activity WHERE id IN (
1127             SELECT id
1128             FROM actor.usr_activity
1129             WHERE usr = cur_usr AND etype = act_type
1130             ORDER BY event_time DESC OFFSET 1
1131         );
1132
1133     END LOOP;
1134 END $$ LANGUAGE PLPGSQL;
1135
1136 CREATE TABLE actor.workstation_setting (
1137     id          SERIAL PRIMARY KEY,
1138     workstation INT    NOT NULL REFERENCES actor.workstation (id)
1139                        ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1140     name        TEXT   NOT NULL REFERENCES config.workstation_setting_type (name)
1141                        ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1142     value       JSON   NOT NULL,
1143     CONSTRAINT  ws_once_per_key UNIQUE (workstation, name)
1144 );
1145
1146 CREATE INDEX actor_workstation_setting_workstation_idx
1147     ON actor.workstation_setting (workstation);
1148
1149 CREATE TYPE actor.cascade_setting_summary AS (
1150     name TEXT,
1151     value JSON,
1152     has_org_setting BOOLEAN,
1153     has_user_setting BOOLEAN,
1154     has_workstation_setting BOOLEAN
1155 );
1156
1157 CREATE OR REPLACE FUNCTION actor.get_cascade_setting(
1158     setting_name TEXT, org_id INT, user_id INT, workstation_id INT)
1159     RETURNS actor.cascade_setting_summary AS
1160 $FUNC$
1161 DECLARE
1162     setting_value JSON;
1163     summary actor.cascade_setting_summary;
1164     org_setting_type config.org_unit_setting_type%ROWTYPE;
1165 BEGIN
1166
1167     summary.name := setting_name;
1168
1169     -- Collect the org setting type status first in case we exit early.
1170     -- The existance of an org setting type is not considered
1171     -- privileged information.
1172     SELECT INTO org_setting_type *
1173         FROM config.org_unit_setting_type WHERE name = setting_name;
1174     IF FOUND THEN
1175         summary.has_org_setting := TRUE;
1176     ELSE
1177         summary.has_org_setting := FALSE;
1178     END IF;
1179
1180     -- User and workstation settings have the same priority.
1181     -- Start with user settings since that's the simplest code path.
1182     -- The workstation_id is ignored if no user_id is provided.
1183     IF user_id IS NOT NULL THEN
1184
1185         SELECT INTO summary.value value FROM actor.usr_setting
1186             WHERE usr = user_id AND name = setting_name;
1187
1188         IF FOUND THEN
1189             -- if we have a value, we have a setting type
1190             summary.has_user_setting := TRUE;
1191
1192             IF workstation_id IS NOT NULL THEN
1193                 -- Only inform the caller about the workstation
1194                 -- setting type disposition when a workstation id is
1195                 -- provided.  Otherwise, it's NULL to indicate UNKNOWN.
1196                 summary.has_workstation_setting := FALSE;
1197             END IF;
1198
1199             RETURN summary;
1200         END IF;
1201
1202         -- no user setting value, but a setting type may exist
1203         SELECT INTO summary.has_user_setting EXISTS (
1204             SELECT TRUE FROM config.usr_setting_type
1205             WHERE name = setting_name
1206         );
1207
1208         IF workstation_id IS NOT NULL THEN
1209
1210             IF NOT summary.has_user_setting THEN
1211                 -- A workstation setting type may only exist when a user
1212                 -- setting type does not.
1213
1214                 SELECT INTO summary.value value
1215                     FROM actor.workstation_setting
1216                     WHERE workstation = workstation_id AND name = setting_name;
1217
1218                 IF FOUND THEN
1219                     -- if we have a value, we have a setting type
1220                     summary.has_workstation_setting := TRUE;
1221                     RETURN summary;
1222                 END IF;
1223
1224                 -- no value, but a setting type may exist
1225                 SELECT INTO summary.has_workstation_setting EXISTS (
1226                     SELECT TRUE FROM config.workstation_setting_type
1227                     WHERE name = setting_name
1228                 );
1229             END IF;
1230
1231             -- Finally make use of the workstation to determine the org
1232             -- unit if none is provided.
1233             IF org_id IS NULL AND summary.has_org_setting THEN
1234                 SELECT INTO org_id owning_lib
1235                     FROM actor.workstation WHERE id = workstation_id;
1236             END IF;
1237         END IF;
1238     END IF;
1239
1240     -- Some org unit settings are protected by a view permission.
1241     -- First see if we have any data that needs protecting, then
1242     -- check the permission if needed.
1243
1244     IF NOT summary.has_org_setting THEN
1245         RETURN summary;
1246     END IF;
1247
1248     -- avoid putting the value into the summary until we confirm
1249     -- the value should be visible to the caller.
1250     SELECT INTO setting_value value
1251         FROM actor.org_unit_ancestor_setting(setting_name, org_id);
1252
1253     IF NOT FOUND THEN
1254         -- No value found -- perm check is irrelevant.
1255         RETURN summary;
1256     END IF;
1257
1258     IF org_setting_type.view_perm IS NOT NULL THEN
1259
1260         IF user_id IS NULL THEN
1261             RAISE NOTICE 'Perm check required but no user_id provided';
1262             RETURN summary;
1263         END IF;
1264
1265         IF NOT permission.usr_has_perm(
1266             user_id, (SELECT code FROM permission.perm_list
1267                 WHERE id = org_setting_type.view_perm), org_id)
1268         THEN
1269             RAISE NOTICE 'Perm check failed for user % on %',
1270                 user_id, org_setting_type.view_perm;
1271             RETURN summary;
1272         END IF;
1273     END IF;
1274
1275     -- Perm check succeeded or was not necessary.
1276     summary.value := setting_value;
1277     RETURN summary;
1278 END;
1279 $FUNC$ LANGUAGE PLPGSQL;
1280
1281
1282 CREATE OR REPLACE FUNCTION actor.get_cascade_setting_batch(
1283     setting_names TEXT[], org_id INT, user_id INT, workstation_id INT)
1284     RETURNS SETOF actor.cascade_setting_summary AS
1285 $FUNC$
1286 -- Returns a row per setting matching the setting name order.  If no
1287 -- value is applied, NULL is returned to retain name-response ordering.
1288 DECLARE
1289     setting_name TEXT;
1290     summary actor.cascade_setting_summary;
1291 BEGIN
1292     FOREACH setting_name IN ARRAY setting_names LOOP
1293         SELECT INTO summary * FROM actor.get_cascade_setting(
1294             setting_Name, org_id, user_id, workstation_id);
1295         RETURN NEXT summary;
1296     END LOOP;
1297 END;
1298 $FUNC$ LANGUAGE PLPGSQL;
1299
1300 CREATE TABLE actor.usr_privacy_waiver (
1301     id BIGSERIAL PRIMARY KEY,
1302     usr BIGINT NOT NULL REFERENCES actor.usr(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1303     name TEXT NOT NULL,
1304     place_holds BOOL DEFAULT FALSE,
1305     pickup_holds BOOL DEFAULT FALSE,
1306     view_history BOOL DEFAULT FALSE,
1307     checkout_items BOOL DEFAULT FALSE
1308 );
1309 CREATE INDEX actor_usr_privacy_waiver_usr_idx ON actor.usr_privacy_waiver (usr);
1310
1311 COMMIT;