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 CREATE INDEX aum_editor ON actor.usr_message (editor);
 801 ALTER TABLE actor.usr_standing_penalty ALTER COLUMN id SET DEFAULT nextval('actor.usr_message_id_seq'::regclass);
 802 ALTER TABLE actor.usr_standing_penalty ADD COLUMN usr_message BIGINT REFERENCES actor.usr_message(id);
 803 CREATE INDEX usr_standing_penalty_usr_message_idx ON actor.usr_standing_penalty (usr_message);
 804
 805 CREATE RULE protect_usr_message_delete AS
 806         ON DELETE TO actor.usr_message DO INSTEAD (
 807                 UPDATE  actor.usr_message
 808                   SET   deleted = TRUE
 809                   WHERE OLD.id = actor.usr_message.id
 810         );
 811
 812 -- limited view to ensure that a library user who somehow
 813 -- manages to figure out how to access pcrud cannot change
 814 -- the text of messages sent them
 815 CREATE VIEW actor.usr_message_limited
 816 AS SELECT * FROM actor.usr_message WHERE pub AND NOT deleted;
 817
 818 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
 819 BEGIN
 820     IF TG_OP = 'UPDATE' THEN
 821         UPDATE actor.usr_message
 822         SET    read_date = NEW.read_date,
 823                deleted   = NEW.deleted
 824         WHERE  id = NEW.id;
 825         RETURN NEW;
 826     END IF;
 827     RETURN NULL;
 828 END;
 829 $$ LANGUAGE PLPGSQL;
 830
 831 CREATE TRIGGER restrict_usr_message_limited_tgr
 832     INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
 833     FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
 834
 835 -- combined view of actor.usr_standing_penalty and actor.usr_message for populating
 836 -- staff Notes (formerly Messages) interface
 837
 838 CREATE VIEW actor.usr_message_penalty AS
 839 SELECT -- ausp with or without messages
 840     ausp.id AS "id",
 841     ausp.id AS "ausp_id",
 842     aum.id AS "aum_id",
 843     ausp.org_unit AS "org_unit",
 844     ausp.org_unit AS "ausp_org_unit",
 845     aum.sending_lib AS "aum_sending_lib",
 846     ausp.usr AS "usr",
 847     ausp.usr as "ausp_usr",
 848     aum.usr as "aum_usr",
 849     ausp.standing_penalty AS "standing_penalty",
 850     ausp.staff AS "staff",
 851     ausp.set_date AS "create_date",
 852     ausp.set_date AS "ausp_set_date",
 853     aum.create_date AS "aum_create_date",
 854     ausp.stop_date AS "stop_date",
 855     ausp.stop_date AS "ausp_stop_date",
 856     aum.stop_date AS "aum_stop_date",
 857     ausp.usr_message AS "ausp_usr_message",
 858     aum.title AS "title",
 859     aum.message AS "message",
 860     aum.deleted AS "deleted",
 861     aum.read_date AS "read_date",
 862     aum.pub AS "pub",
 863     aum.editor AS "editor",
 864     aum.edit_date AS "edit_date"
 865 FROM
 866     actor.usr_standing_penalty ausp
 867     LEFT JOIN actor.usr_message aum ON (ausp.usr_message = aum.id)
 868         UNION ALL
 869 SELECT -- aum without penalties
 870     aum.id AS "id",
 871     NULL::INT AS "ausp_id",
 872     aum.id AS "aum_id",
 873     aum.sending_lib AS "org_unit",
 874     NULL::INT AS "ausp_org_unit",
 875     aum.sending_lib AS "aum_sending_lib",
 876     aum.usr AS "usr",
 877     NULL::INT as "ausp_usr",
 878     aum.usr as "aum_usr",
 879     NULL::INT AS "standing_penalty",
 880     NULL::INT AS "staff",
 881     aum.create_date AS "create_date",
 882     NULL::TIMESTAMPTZ AS "ausp_set_date",
 883     aum.create_date AS "aum_create_date",
 884     aum.stop_date AS "stop_date",
 885     NULL::TIMESTAMPTZ AS "ausp_stop_date",
 886     aum.stop_date AS "aum_stop_date",
 887     NULL::INT AS "ausp_usr_message",
 888     aum.title AS "title",
 889     aum.message AS "message",
 890     aum.deleted AS "deleted",
 891     aum.read_date AS "read_date",
 892     aum.pub AS "pub",
 893     aum.editor AS "editor",
 894     aum.edit_date AS "edit_date"
 895 FROM
 896     actor.usr_message aum
 897     LEFT JOIN actor.usr_standing_penalty ausp ON (ausp.usr_message = aum.id)
 898 WHERE NOT aum.deleted AND ausp.id IS NULL
 899 ;
 900
 901 CREATE TABLE actor.passwd_type (
 902     code        TEXT PRIMARY KEY,
 903     name        TEXT UNIQUE NOT NULL,
 904     login       BOOLEAN NOT NULL DEFAULT FALSE,
 905     regex       TEXT,   -- pending
 906     crypt_algo  TEXT,   -- e.g. 'bf'
 907
 908     -- gen_salt() iter count used with each new salt.
 909     -- A non-NULL value for iter_count is our indication the
 910     -- password is salted and encrypted via crypt()
 911     iter_count  INTEGER CHECK (iter_count IS NULL OR iter_count > 0)
 912 );
 913
 914 CREATE TABLE actor.passwd (
 915     id          SERIAL PRIMARY KEY,
 916     usr         INTEGER NOT NULL REFERENCES actor.usr(id)
 917                 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 918     salt        TEXT, -- will be NULL for non-crypt'ed passwords
 919     passwd      TEXT NOT NULL,
 920     passwd_type TEXT NOT NULL REFERENCES actor.passwd_type(code)
 921                 DEFERRABLE INITIALLY DEFERRED,
 922     create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 923     edit_date   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 924     CONSTRAINT  passwd_type_once_per_user UNIQUE (usr, passwd_type)
 925 );
 926
 927 CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT)
 928     RETURNS TEXT AS $$
 929 DECLARE
 930     type_row actor.passwd_type%ROWTYPE;
 931 BEGIN
 932     /* Returns a new salt based on the passwd_type encryption settings.
 933      * Returns NULL If the password type is not crypt()'ed.
 934      */
 935
 936     SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;
 937
 938     IF NOT FOUND THEN
 939         RETURN EXCEPTION 'No such password type: %', pw_type;
 940     END IF;
 941
 942     IF type_row.iter_count IS NULL THEN
 943         -- This password type is unsalted.  That's OK.
 944         RETURN NULL;
 945     END IF;
 946
 947     RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
 948 END;
 949 $$ LANGUAGE PLPGSQL;
 950
 951
 952 /*
 953     TODO: when a user changes their password in the application, the
 954     app layer has access to the bare password.  At that point, we have
 955     the opportunity to store the new password without the MD5(MD5())
 956     intermediate hashing.  Do we care?  We would need a way to indicate
 957     which passwords have the legacy intermediate hashing and which don't
 958     so the app layer would know whether it should perform the intermediate
 959     hashing.  In either event, with the exception of migrate_passwd(), the
 960     DB functions know or care nothing about intermediate hashing.  Every
 961     password is just a value that may or may not be internally crypt'ed.
 962 */
 963
 964 CREATE OR REPLACE FUNCTION actor.set_passwd(
 965     pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL)
 966     RETURNS BOOLEAN AS $$
 967 DECLARE
 968     pw_salt TEXT;
 969     pw_text TEXT;
 970 BEGIN
 971     /* Sets the password value, creating a new actor.passwd row if needed.
 972      * If the password type supports it, the new_pass value is crypt()'ed.
 973      * For crypt'ed passwords, the salt comes from one of 3 places in order:
 974      * new_salt (if present), existing salt (if present), newly created
 975      * salt.
 976      */
 977
 978     IF new_salt IS NOT NULL THEN
 979         pw_salt := new_salt;
 980     ELSE
 981         pw_salt := actor.get_salt(pw_usr, pw_type);
 982
 983         IF pw_salt IS NULL THEN
 984             /* We have no salt for this user + type.  Assume they want a
 985              * new salt.  If this type is unsalted, create_salt() will
 986              * return NULL. */
 987             pw_salt := actor.create_salt(pw_type);
 988         END IF;
 989     END IF;
 990
 991     IF pw_salt IS NULL THEN
 992         pw_text := new_pass; -- unsalted, use as-is.
 993     ELSE
 994         pw_text := CRYPT(new_pass, pw_salt);
 995     END IF;
 996
 997     UPDATE actor.passwd
 998         SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
 999         WHERE usr = pw_usr AND passwd_type = pw_type;
1000
1001     IF NOT FOUND THEN
1002         -- no password row exists for this user + type.  Create one.
1003         INSERT INTO actor.passwd (usr, passwd_type, salt, passwd)
1004             VALUES (pw_usr, pw_type, pw_salt, pw_text);
1005     END IF;
1006
1007     RETURN TRUE;
1008 END;
1009 $$ LANGUAGE PLPGSQL;
1010
1011 CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT)
1012     RETURNS TEXT AS $$
1013 DECLARE
1014     pw_salt TEXT;
1015     type_row actor.passwd_type%ROWTYPE;
1016 BEGIN
1017     /* Returns the salt for the requested user + type.  If the password
1018      * type of "main" is requested and no password exists in actor.passwd,
1019      * the user's existing password is migrated and the new salt is returned.
1020      * Returns NULL if the password type is not crypt'ed (iter_count is NULL).
1021      */
1022
1023     SELECT INTO pw_salt salt FROM actor.passwd
1024         WHERE usr = pw_usr AND passwd_type = pw_type;
1025
1026     IF FOUND THEN
1027         RETURN pw_salt;
1028     END IF;
1029
1030     IF pw_type = 'main' THEN
1031         -- Main password has not yet been migrated.
1032         -- Do it now and return the newly created salt.
1033         RETURN actor.migrate_passwd(pw_usr);
1034     END IF;
1035
1036     -- We have no salt to return.  actor.create_salt() needed.
1037     RETURN NULL;
1038 END;
1039 $$ LANGUAGE PLPGSQL;
1040
1041 CREATE OR REPLACE FUNCTION
1042     actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$
1043 DECLARE
1044     pw_salt TEXT;
1045     usr_row actor.usr%ROWTYPE;
1046 BEGIN
1047     /* Migrates legacy actor.usr.passwd value to actor.passwd with
1048      * a password type 'main' and returns the new salt.  For backwards
1049      * compatibility with existing CHAP-style API's, we perform a
1050      * layer of intermediate MD5(MD5()) hashing.  This is intermediate
1051      * hashing is not required of other passwords.
1052      */
1053
1054     -- Avoid calling get_salt() here, because it may result in a
1055     -- migrate_passwd() call, creating a loop.
1056     SELECT INTO pw_salt salt FROM actor.passwd
1057         WHERE usr = pw_usr AND passwd_type = 'main';
1058
1059     -- Only migrate passwords that have not already been migrated.
1060     IF FOUND THEN
1061         RETURN pw_salt;
1062     END IF;
1063
1064     SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
1065
1066     pw_salt := actor.create_salt('main');
1067
1068     PERFORM actor.set_passwd(
1069         pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
1070
1071     -- clear the existing password
1072     UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
1073
1074     RETURN pw_salt;
1075 END;
1076 $$ LANGUAGE PLPGSQL;
1077
1078 CREATE OR REPLACE FUNCTION
1079     actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT)
1080     RETURNS BOOLEAN AS $$
1081 DECLARE
1082     pw_salt TEXT;
1083 BEGIN
1084     /* Returns TRUE if the password provided matches the in-db password.
1085      * If the password type is salted, we compare the output of CRYPT().
1086      * NOTE: test_passwd is MD5(salt || MD5(password)) for legacy
1087      * 'main' passwords.
1088      */
1089
1090     SELECT INTO pw_salt salt FROM actor.passwd
1091         WHERE usr = pw_usr AND passwd_type = pw_type;
1092
1093     IF NOT FOUND THEN
1094         -- no such password
1095         RETURN FALSE;
1096     END IF;
1097
1098     IF pw_salt IS NULL THEN
1099         -- Password is unsalted, compare the un-CRYPT'ed values.
1100         RETURN EXISTS (
1101             SELECT TRUE FROM actor.passwd WHERE
1102                 usr = pw_usr AND
1103                 passwd_type = pw_type AND
1104                 passwd = test_passwd
1105         );
1106     END IF;
1107
1108     RETURN EXISTS (
1109         SELECT TRUE FROM actor.passwd WHERE
1110             usr = pw_usr AND
1111             passwd_type = pw_type AND
1112             passwd = CRYPT(test_passwd, pw_salt)
1113     );
1114 END;
1115 $$ STRICT LANGUAGE PLPGSQL;
1116
1117 -- Remove all activity entries by activity type,
1118 -- except the most recent entry per user.
1119 CREATE OR REPLACE FUNCTION
1120     actor.purge_usr_activity_by_type(act_type INTEGER)
1121     RETURNS VOID AS $$
1122 DECLARE
1123     cur_usr INTEGER;
1124 BEGIN
1125     FOR cur_usr IN SELECT DISTINCT(usr)
1126         FROM actor.usr_activity WHERE etype = act_type LOOP
1127         DELETE FROM actor.usr_activity WHERE id IN (
1128             SELECT id
1129             FROM actor.usr_activity
1130             WHERE usr = cur_usr AND etype = act_type
1131             ORDER BY event_time DESC OFFSET 1
1132         );
1133
1134     END LOOP;
1135 END $$ LANGUAGE PLPGSQL;
1136
1137 CREATE TABLE actor.workstation_setting (
1138     id          SERIAL PRIMARY KEY,
1139     workstation INT    NOT NULL REFERENCES actor.workstation (id)
1140                        ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1141     name        TEXT   NOT NULL REFERENCES config.workstation_setting_type (name)
1142                        ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1143     value       JSON   NOT NULL,
1144     CONSTRAINT  ws_once_per_key UNIQUE (workstation, name)
1145 );
1146
1147 CREATE INDEX actor_workstation_setting_workstation_idx
1148     ON actor.workstation_setting (workstation);
1149
1150 CREATE TYPE actor.cascade_setting_summary AS (
1151     name TEXT,
1152     value JSON,
1153     has_org_setting BOOLEAN,
1154     has_user_setting BOOLEAN,
1155     has_workstation_setting BOOLEAN
1156 );
1157
1158 CREATE OR REPLACE FUNCTION actor.get_cascade_setting(
1159     setting_name TEXT, org_id INT, user_id INT, workstation_id INT)
1160     RETURNS actor.cascade_setting_summary AS
1161 $FUNC$
1162 DECLARE
1163     setting_value JSON;
1164     summary actor.cascade_setting_summary;
1165     org_setting_type config.org_unit_setting_type%ROWTYPE;
1166 BEGIN
1167
1168     summary.name := setting_name;
1169
1170     -- Collect the org setting type status first in case we exit early.
1171     -- The existance of an org setting type is not considered
1172     -- privileged information.
1173     SELECT INTO org_setting_type *
1174         FROM config.org_unit_setting_type WHERE name = setting_name;
1175     IF FOUND THEN
1176         summary.has_org_setting := TRUE;
1177     ELSE
1178         summary.has_org_setting := FALSE;
1179     END IF;
1180
1181     -- User and workstation settings have the same priority.
1182     -- Start with user settings since that's the simplest code path.
1183     -- The workstation_id is ignored if no user_id is provided.
1184     IF user_id IS NOT NULL THEN
1185
1186         SELECT INTO summary.value value FROM actor.usr_setting
1187             WHERE usr = user_id AND name = setting_name;
1188
1189         IF FOUND THEN
1190             -- if we have a value, we have a setting type
1191             summary.has_user_setting := TRUE;
1192
1193             IF workstation_id IS NOT NULL THEN
1194                 -- Only inform the caller about the workstation
1195                 -- setting type disposition when a workstation id is
1196                 -- provided.  Otherwise, it's NULL to indicate UNKNOWN.
1197                 summary.has_workstation_setting := FALSE;
1198             END IF;
1199
1200             RETURN summary;
1201         END IF;
1202
1203         -- no user setting value, but a setting type may exist
1204         SELECT INTO summary.has_user_setting EXISTS (
1205             SELECT TRUE FROM config.usr_setting_type
1206             WHERE name = setting_name
1207         );
1208
1209         IF workstation_id IS NOT NULL THEN
1210
1211             IF NOT summary.has_user_setting THEN
1212                 -- A workstation setting type may only exist when a user
1213                 -- setting type does not.
1214
1215                 SELECT INTO summary.value value
1216                     FROM actor.workstation_setting
1217                     WHERE workstation = workstation_id AND name = setting_name;
1218
1219                 IF FOUND THEN
1220                     -- if we have a value, we have a setting type
1221                     summary.has_workstation_setting := TRUE;
1222                     RETURN summary;
1223                 END IF;
1224
1225                 -- no value, but a setting type may exist
1226                 SELECT INTO summary.has_workstation_setting EXISTS (
1227                     SELECT TRUE FROM config.workstation_setting_type
1228                     WHERE name = setting_name
1229                 );
1230             END IF;
1231
1232             -- Finally make use of the workstation to determine the org
1233             -- unit if none is provided.
1234             IF org_id IS NULL AND summary.has_org_setting THEN
1235                 SELECT INTO org_id owning_lib
1236                     FROM actor.workstation WHERE id = workstation_id;
1237             END IF;
1238         END IF;
1239     END IF;
1240
1241     -- Some org unit settings are protected by a view permission.
1242     -- First see if we have any data that needs protecting, then
1243     -- check the permission if needed.
1244
1245     IF NOT summary.has_org_setting THEN
1246         RETURN summary;
1247     END IF;
1248
1249     -- avoid putting the value into the summary until we confirm
1250     -- the value should be visible to the caller.
1251     SELECT INTO setting_value value
1252         FROM actor.org_unit_ancestor_setting(setting_name, org_id);
1253
1254     IF NOT FOUND THEN
1255         -- No value found -- perm check is irrelevant.
1256         RETURN summary;
1257     END IF;
1258
1259     IF org_setting_type.view_perm IS NOT NULL THEN
1260
1261         IF user_id IS NULL THEN
1262             RAISE NOTICE 'Perm check required but no user_id provided';
1263             RETURN summary;
1264         END IF;
1265
1266         IF NOT permission.usr_has_perm(
1267             user_id, (SELECT code FROM permission.perm_list
1268                 WHERE id = org_setting_type.view_perm), org_id)
1269         THEN
1270             RAISE NOTICE 'Perm check failed for user % on %',
1271                 user_id, org_setting_type.view_perm;
1272             RETURN summary;
1273         END IF;
1274     END IF;
1275
1276     -- Perm check succeeded or was not necessary.
1277     summary.value := setting_value;
1278     RETURN summary;
1279 END;
1280 $FUNC$ LANGUAGE PLPGSQL;
1281
1282
1283 CREATE OR REPLACE FUNCTION actor.get_cascade_setting_batch(
1284     setting_names TEXT[], org_id INT, user_id INT, workstation_id INT)
1285     RETURNS SETOF actor.cascade_setting_summary AS
1286 $FUNC$
1287 -- Returns a row per setting matching the setting name order.  If no
1288 -- value is applied, NULL is returned to retain name-response ordering.
1289 DECLARE
1290     setting_name TEXT;
1291     summary actor.cascade_setting_summary;
1292 BEGIN
1293     FOREACH setting_name IN ARRAY setting_names LOOP
1294         SELECT INTO summary * FROM actor.get_cascade_setting(
1295             setting_Name, org_id, user_id, workstation_id);
1296         RETURN NEXT summary;
1297     END LOOP;
1298 END;
1299 $FUNC$ LANGUAGE PLPGSQL;
1300
1301 CREATE TABLE actor.usr_privacy_waiver (
1302     id BIGSERIAL PRIMARY KEY,
1303     usr BIGINT NOT NULL REFERENCES actor.usr(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1304     name TEXT NOT NULL,
1305     place_holds BOOL DEFAULT FALSE,
1306     pickup_holds BOOL DEFAULT FALSE,
1307     view_history BOOL DEFAULT FALSE,
1308     checkout_items BOOL DEFAULT FALSE
1309 );
1310 CREATE INDEX actor_usr_privacy_waiver_usr_idx ON actor.usr_privacy_waiver (usr);
1311
1312 COMMIT;