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