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