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_0_note  TEXT,
 459         dow_1_open      TIME    NOT NULL DEFAULT '09:00',
 460         dow_1_close     TIME    NOT NULL DEFAULT '17:00',
 461     dow_1_note  TEXT,
 462         dow_2_open      TIME    NOT NULL DEFAULT '09:00',
 463         dow_2_close     TIME    NOT NULL DEFAULT '17:00',
 464     dow_2_note  TEXT,
 465         dow_3_open      TIME    NOT NULL DEFAULT '09:00',
 466         dow_3_close     TIME    NOT NULL DEFAULT '17:00',
 467     dow_3_note  TEXT,
 468         dow_4_open      TIME    NOT NULL DEFAULT '09:00',
 469         dow_4_close     TIME    NOT NULL DEFAULT '17:00',
 470     dow_4_note  TEXT,
 471         dow_5_open      TIME    NOT NULL DEFAULT '09:00',
 472         dow_5_close     TIME    NOT NULL DEFAULT '17:00',
 473     dow_5_note  TEXT,
 474         dow_6_open      TIME    NOT NULL DEFAULT '09:00',
 475         dow_6_close     TIME    NOT NULL DEFAULT '17:00',
 476     dow_6_note  TEXT
 477 );
 478 COMMENT ON TABLE actor.hours_of_operation IS $$
 479 When does this org_unit usually open and close?  (Variations
 480 are expressed in the actor.org_unit_closed table.)
 481 $$;
 482 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
 483 When does this org_unit open on Monday?
 484 $$;
 485 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
 486 When does this org_unit close on Monday?
 487 $$;
 488 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
 489 When does this org_unit open on Tuesday?
 490 $$;
 491 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
 492 When does this org_unit close on Tuesday?
 493 $$;
 494 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
 495 When does this org_unit open on Wednesday?
 496 $$;
 497 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
 498 When does this org_unit close on Wednesday?
 499 $$;
 500 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
 501 When does this org_unit open on Thursday?
 502 $$;
 503 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
 504 When does this org_unit close on Thursday?
 505 $$;
 506 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
 507 When does this org_unit open on Friday?
 508 $$;
 509 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
 510 When does this org_unit close on Friday?
 511 $$;
 512 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
 513 When does this org_unit open on Saturday?
 514 $$;
 515 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
 516 When does this org_unit close on Saturday?
 517 $$;
 518 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
 519 When does this org_unit open on Sunday?
 520 $$;
 521 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
 522 When does this org_unit close on Sunday?
 523 $$;
 524
 525 CREATE TABLE actor.org_unit_closed (
 526         id              SERIAL                          PRIMARY KEY,
 527         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 528         close_start     TIMESTAMP WITH TIME ZONE        NOT NULL,
 529         close_end       TIMESTAMP WITH TIME ZONE        NOT NULL,
 530     full_day    BOOLEAN                     NOT NULL DEFAULT FALSE,
 531     multi_day   BOOLEAN                     NOT NULL DEFAULT FALSE,
 532         reason          TEXT
 533 );
 534
 535 -- Workstation registration...
 536 CREATE TABLE actor.workstation (
 537         id              SERIAL  PRIMARY KEY,
 538         name            TEXT    NOT NULL UNIQUE,
 539         owning_lib      INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 540 );
 541
 542 CREATE TABLE actor.usr_org_unit_opt_in (
 543         id              SERIAL                          PRIMARY KEY,
 544         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 545         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 546         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 547         opt_in_ts       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 548         opt_in_ws       INT                             NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
 549         CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
 550 );
 551 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
 552
 553 CREATE TABLE actor.org_unit_setting (
 554         id              BIGSERIAL       PRIMARY KEY,
 555         org_unit        INT             NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 556         name            TEXT    NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
 557         value           TEXT            NOT NULL,
 558         CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
 559         CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
 560 );
 561 COMMENT ON TABLE actor.org_unit_setting IS $$
 562 Org Unit settings
 563
 564 This table contains any arbitrary settings that a client
 565 program would like to save for an org unit.
 566 $$;
 567
 568 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
 569
 570 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
 571 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
 572     DECLARE
 573     original TEXT;
 574     BEGIN
 575         -- Check for which setting is being updated, and log it.
 576         SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
 577
 578         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
 579
 580         RETURN NEW;
 581     END;
 582 $ous_change_log$ LANGUAGE plpgsql;
 583
 584 CREATE TRIGGER log_ous_change
 585     BEFORE INSERT OR UPDATE ON actor.org_unit_setting
 586     FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
 587
 588 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
 589     DECLARE
 590     original TEXT;
 591     BEGIN
 592         -- Check for which setting is being updated, and log it.
 593         SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
 594
 595         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
 596
 597         RETURN OLD;
 598     END;
 599 $ous_delete_log$ LANGUAGE plpgsql;
 600
 601 CREATE TRIGGER log_ous_del
 602     BEFORE DELETE ON actor.org_unit_setting
 603     FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
 604
 605
 606
 607
 608 CREATE TABLE actor.usr_address (
 609         id                      SERIAL  PRIMARY KEY,
 610         valid                   BOOL    NOT NULL DEFAULT TRUE,
 611         within_city_limits      BOOL    NOT NULL DEFAULT TRUE,
 612         address_type            TEXT    NOT NULL DEFAULT 'MAILING',
 613         usr                     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 614         street1                 TEXT    NOT NULL,
 615         street2                 TEXT,
 616         city                    TEXT    NOT NULL,
 617         county                  TEXT,
 618         state                   TEXT,
 619         country                 TEXT    NOT NULL,
 620         post_code               TEXT    NOT NULL,
 621     pending         BOOL    NOT NULL DEFAULT FALSE,
 622         replaces            INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
 623 );
 624
 625 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
 626
 627 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
 628 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
 629
 630 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
 631 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
 632 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
 633
 634 CREATE TABLE actor.usr_password_reset (
 635   id SERIAL PRIMARY KEY,
 636   uuid TEXT NOT NULL,
 637   usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
 638   request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 639   has_been_reset BOOL NOT NULL DEFAULT false
 640 );
 641 COMMENT ON TABLE actor.usr_password_reset IS $$
 642 Self-serve password reset requests
 643 $$;
 644 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
 645 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
 646 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
 647 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
 648
 649 CREATE TABLE actor.org_address (
 650         id              SERIAL  PRIMARY KEY,
 651         valid           BOOL    NOT NULL DEFAULT TRUE,
 652         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 653         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 654         street1         TEXT    NOT NULL,
 655         street2         TEXT,
 656         city            TEXT    NOT NULL,
 657         county          TEXT,
 658         state           TEXT,
 659         country         TEXT    NOT NULL,
 660         post_code       TEXT    NOT NULL,
 661         san                     TEXT,
 662         latitude        FLOAT,
 663         longitude       FLOAT
 664 );
 665
 666 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
 667
 668 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
 669         SELECT SUBSTRING( $1, 1, 5);
 670 $$ LANGUAGE SQL;
 671
 672 CREATE TABLE actor.usr_standing_penalty (
 673         id                      SERIAL  PRIMARY KEY,
 674         org_unit                INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 675         usr                     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 676         standing_penalty        INT     NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 677         staff                   INT     REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 678         set_date                TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 679         stop_date               TIMESTAMP WITH TIME ZONE
 680 );
 681 COMMENT ON TABLE actor.usr_standing_penalty IS $$
 682 User standing penalties
 683 $$;
 684
 685 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
 686 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
 687
 688
 689 CREATE TABLE actor.usr_saved_search (
 690     id              SERIAL          PRIMARY KEY,
 691         owner           INT             NOT NULL REFERENCES actor.usr (id)
 692                                         ON DELETE CASCADE
 693                                         DEFERRABLE INITIALLY DEFERRED,
 694         name            TEXT            NOT NULL,
 695         create_date     TIMESTAMPTZ     NOT NULL DEFAULT now(),
 696         query_text      TEXT            NOT NULL,
 697         query_type      TEXT            NOT NULL
 698                                         CONSTRAINT valid_query_text CHECK (
 699                                         query_type IN ( 'URL' )) DEFAULT 'URL',
 700                                         -- we may add other types someday
 701         target          TEXT            NOT NULL
 702                                         CONSTRAINT valid_target CHECK (
 703                                         target IN ( 'record', 'metarecord', 'callnumber' )),
 704         CONSTRAINT name_once_per_user UNIQUE (owner, name)
 705 );
 706
 707 CREATE TABLE actor.address_alert (
 708     id              SERIAL  PRIMARY KEY,
 709     owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 710     active          BOOL    NOT NULL DEFAULT TRUE,
 711     match_all       BOOL    NOT NULL DEFAULT TRUE,
 712     alert_message   TEXT    NOT NULL,
 713     street1         TEXT,
 714     street2         TEXT,
 715     city            TEXT,
 716     county          TEXT,
 717     state           TEXT,
 718     country         TEXT,
 719     post_code       TEXT,
 720     mailing_address BOOL    NOT NULL DEFAULT FALSE,
 721     billing_address BOOL    NOT NULL DEFAULT FALSE
 722 );
 723
 724 CREATE TABLE actor.usr_activity (
 725     id          BIGSERIAL   PRIMARY KEY,
 726     usr         INT         REFERENCES actor.usr (id) ON DELETE SET NULL,
 727     etype       INT         NOT NULL REFERENCES config.usr_activity_type (id),
 728     event_time  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 729 );
 730 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
 731
 732 CREATE TABLE actor.toolbar (
 733     id          BIGSERIAL   PRIMARY KEY,
 734     ws          INT         REFERENCES actor.workstation (id) ON DELETE CASCADE,
 735     org         INT         REFERENCES actor.org_unit (id) ON DELETE CASCADE,
 736     usr         INT         REFERENCES actor.usr (id) ON DELETE CASCADE,
 737     label       TEXT        NOT NULL,
 738     layout      TEXT        NOT NULL,
 739     CONSTRAINT only_one_type CHECK (
 740         (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
 741         (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
 742         (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
 743     ),
 744     CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
 745 );
 746 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
 747 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
 748 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
 749
 750 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
 751 CREATE TABLE actor.org_unit_custom_tree (
 752     id              SERIAL  PRIMARY KEY,
 753     active          BOOLEAN DEFAULT FALSE,
 754     purpose         actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
 755 );
 756
 757 CREATE TABLE actor.org_unit_custom_tree_node (
 758     id              SERIAL  PRIMARY KEY,
 759     tree            INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
 760     org_unit        INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 761     parent_node     INTEGER REFERENCES actor.org_unit_custom_tree_node (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 762     sibling_order   INTEGER NOT NULL DEFAULT 0,
 763     CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
 764 );
 765
 766 CREATE TABLE actor.search_query (
 767     id          SERIAL PRIMARY KEY,
 768     label       TEXT NOT NULL, -- i18n
 769     query_text  TEXT NOT NULL -- QP text
 770 );
 771
 772 CREATE TABLE actor.search_filter_group (
 773     id          SERIAL      PRIMARY KEY,
 774     owner       INT         NOT NULL REFERENCES actor.org_unit (id)
 775                             ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 776     code        TEXT        NOT NULL, -- for CGI, etc.
 777     label       TEXT        NOT NULL, -- i18n
 778     create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
 779     CONSTRAINT  asfg_label_once_per_org UNIQUE (owner, label),
 780     CONSTRAINT  asfg_code_once_per_org UNIQUE (owner, code)
 781 );
 782
 783 CREATE TABLE actor.search_filter_group_entry (
 784     id          SERIAL  PRIMARY KEY,
 785     grp         INT     NOT NULL REFERENCES actor.search_filter_group(id)
 786                         ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 787     pos         INT     NOT NULL DEFAULT 0,
 788     query       INT     NOT NULL REFERENCES actor.search_query(id)
 789                         ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 790     CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
 791 );
 792
 793 CREATE TABLE actor.usr_message (
 794         id              SERIAL                          PRIMARY KEY,
 795         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 796         title           TEXT,
 797         message         TEXT                            NOT NULL,
 798         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 799         deleted         BOOL                            NOT NULL DEFAULT FALSE,
 800         read_date       TIMESTAMP WITH TIME ZONE,
 801         sending_lib     INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 802         pub             BOOL                            NOT NULL DEFAULT FALSE,
 803         stop_date               TIMESTAMP WITH TIME ZONE,
 804         editor  BIGINT REFERENCES actor.usr (id),
 805         edit_date               TIMESTAMP WITH TIME ZONE
 806 );
 807 CREATE INDEX aum_usr ON actor.usr_message (usr);
 808 CREATE INDEX aum_editor ON actor.usr_message (editor);
 809 ALTER TABLE actor.usr_standing_penalty ALTER COLUMN id SET DEFAULT nextval('actor.usr_message_id_seq'::regclass);
 810 ALTER TABLE actor.usr_standing_penalty ADD COLUMN usr_message BIGINT REFERENCES actor.usr_message(id);
 811 CREATE INDEX usr_standing_penalty_usr_message_idx ON actor.usr_standing_penalty (usr_message);
 812
 813 CREATE RULE protect_usr_message_delete AS
 814         ON DELETE TO actor.usr_message DO INSTEAD (
 815                 UPDATE  actor.usr_message
 816                   SET   deleted = TRUE
 817                   WHERE OLD.id = actor.usr_message.id
 818         );
 819
 820 -- limited view to ensure that a library user who somehow
 821 -- manages to figure out how to access pcrud cannot change
 822 -- the text of messages sent them
 823 CREATE VIEW actor.usr_message_limited
 824 AS SELECT * FROM actor.usr_message WHERE pub AND NOT deleted;
 825
 826 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
 827 BEGIN
 828     IF TG_OP = 'UPDATE' THEN
 829         UPDATE actor.usr_message
 830         SET    read_date = NEW.read_date,
 831                deleted   = NEW.deleted
 832         WHERE  id = NEW.id;
 833         RETURN NEW;
 834     END IF;
 835     RETURN NULL;
 836 END;
 837 $$ LANGUAGE PLPGSQL;
 838
 839 CREATE TRIGGER restrict_usr_message_limited_tgr
 840     INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
 841     FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
 842
 843 -- combined view of actor.usr_standing_penalty and actor.usr_message for populating
 844 -- staff Notes (formerly Messages) interface
 845
 846 CREATE VIEW actor.usr_message_penalty AS
 847 SELECT -- ausp with or without messages
 848     ausp.id AS "id",
 849     ausp.id AS "ausp_id",
 850     aum.id AS "aum_id",
 851     ausp.org_unit AS "org_unit",
 852     ausp.org_unit AS "ausp_org_unit",
 853     aum.sending_lib AS "aum_sending_lib",
 854     ausp.usr AS "usr",
 855     ausp.usr as "ausp_usr",
 856     aum.usr as "aum_usr",
 857     ausp.standing_penalty AS "standing_penalty",
 858     ausp.staff AS "staff",
 859     ausp.set_date AS "create_date",
 860     ausp.set_date AS "ausp_set_date",
 861     aum.create_date AS "aum_create_date",
 862     ausp.stop_date AS "stop_date",
 863     ausp.stop_date AS "ausp_stop_date",
 864     aum.stop_date AS "aum_stop_date",
 865     ausp.usr_message AS "ausp_usr_message",
 866     aum.title AS "title",
 867     aum.message AS "message",
 868     aum.deleted AS "deleted",
 869     aum.read_date AS "read_date",
 870     aum.pub AS "pub",
 871     aum.editor AS "editor",
 872     aum.edit_date AS "edit_date"
 873 FROM
 874     actor.usr_standing_penalty ausp
 875     LEFT JOIN actor.usr_message aum ON (ausp.usr_message = aum.id)
 876         UNION ALL
 877 SELECT -- aum without penalties
 878     aum.id AS "id",
 879     NULL::INT AS "ausp_id",
 880     aum.id AS "aum_id",
 881     aum.sending_lib AS "org_unit",
 882     NULL::INT AS "ausp_org_unit",
 883     aum.sending_lib AS "aum_sending_lib",
 884     aum.usr AS "usr",
 885     NULL::INT as "ausp_usr",
 886     aum.usr as "aum_usr",
 887     NULL::INT AS "standing_penalty",
 888     NULL::INT AS "staff",
 889     aum.create_date AS "create_date",
 890     NULL::TIMESTAMPTZ AS "ausp_set_date",
 891     aum.create_date AS "aum_create_date",
 892     aum.stop_date AS "stop_date",
 893     NULL::TIMESTAMPTZ AS "ausp_stop_date",
 894     aum.stop_date AS "aum_stop_date",
 895     NULL::INT AS "ausp_usr_message",
 896     aum.title AS "title",
 897     aum.message AS "message",
 898     aum.deleted AS "deleted",
 899     aum.read_date AS "read_date",
 900     aum.pub AS "pub",
 901     aum.editor AS "editor",
 902     aum.edit_date AS "edit_date"
 903 FROM
 904     actor.usr_message aum
 905     LEFT JOIN actor.usr_standing_penalty ausp ON (ausp.usr_message = aum.id)
 906 WHERE NOT aum.deleted AND ausp.id IS NULL
 907 ;
 908
 909 CREATE TABLE actor.passwd_type (
 910     code        TEXT PRIMARY KEY,
 911     name        TEXT UNIQUE NOT NULL,
 912     login       BOOLEAN NOT NULL DEFAULT FALSE,
 913     regex       TEXT,   -- pending
 914     crypt_algo  TEXT,   -- e.g. 'bf'
 915
 916     -- gen_salt() iter count used with each new salt.
 917     -- A non-NULL value for iter_count is our indication the
 918     -- password is salted and encrypted via crypt()
 919     iter_count  INTEGER CHECK (iter_count IS NULL OR iter_count > 0)
 920 );
 921
 922 CREATE TABLE actor.passwd (
 923     id          SERIAL PRIMARY KEY,
 924     usr         INTEGER NOT NULL REFERENCES actor.usr(id)
 925                 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 926     salt        TEXT, -- will be NULL for non-crypt'ed passwords
 927     passwd      TEXT NOT NULL,
 928     passwd_type TEXT NOT NULL REFERENCES actor.passwd_type(code)
 929                 DEFERRABLE INITIALLY DEFERRED,
 930     create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 931     edit_date   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 932     CONSTRAINT  passwd_type_once_per_user UNIQUE (usr, passwd_type)
 933 );
 934
 935 CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT)
 936     RETURNS TEXT AS $$
 937 DECLARE
 938     type_row actor.passwd_type%ROWTYPE;
 939 BEGIN
 940     /* Returns a new salt based on the passwd_type encryption settings.
 941      * Returns NULL If the password type is not crypt()'ed.
 942      */
 943
 944     SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;
 945
 946     IF NOT FOUND THEN
 947         RETURN EXCEPTION 'No such password type: %', pw_type;
 948     END IF;
 949
 950     IF type_row.iter_count IS NULL THEN
 951         -- This password type is unsalted.  That's OK.
 952         RETURN NULL;
 953     END IF;
 954
 955     RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
 956 END;
 957 $$ LANGUAGE PLPGSQL;
 958
 959
 960 /*
 961     TODO: when a user changes their password in the application, the
 962     app layer has access to the bare password.  At that point, we have
 963     the opportunity to store the new password without the MD5(MD5())
 964     intermediate hashing.  Do we care?  We would need a way to indicate
 965     which passwords have the legacy intermediate hashing and which don't
 966     so the app layer would know whether it should perform the intermediate
 967     hashing.  In either event, with the exception of migrate_passwd(), the
 968     DB functions know or care nothing about intermediate hashing.  Every
 969     password is just a value that may or may not be internally crypt'ed.
 970 */
 971
 972 CREATE OR REPLACE FUNCTION actor.set_passwd(
 973     pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL)
 974     RETURNS BOOLEAN AS $$
 975 DECLARE
 976     pw_salt TEXT;
 977     pw_text TEXT;
 978 BEGIN
 979     /* Sets the password value, creating a new actor.passwd row if needed.
 980      * If the password type supports it, the new_pass value is crypt()'ed.
 981      * For crypt'ed passwords, the salt comes from one of 3 places in order:
 982      * new_salt (if present), existing salt (if present), newly created
 983      * salt.
 984      */
 985
 986     IF new_salt IS NOT NULL THEN
 987         pw_salt := new_salt;
 988     ELSE
 989         pw_salt := actor.get_salt(pw_usr, pw_type);
 990
 991         IF pw_salt IS NULL THEN
 992             /* We have no salt for this user + type.  Assume they want a
 993              * new salt.  If this type is unsalted, create_salt() will
 994              * return NULL. */
 995             pw_salt := actor.create_salt(pw_type);
 996         END IF;
 997     END IF;
 998
 999     IF pw_salt IS NULL THEN
1000         pw_text := new_pass; -- unsalted, use as-is.
1001     ELSE
1002         pw_text := CRYPT(new_pass, pw_salt);
1003     END IF;
1004
1005     UPDATE actor.passwd
1006         SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
1007         WHERE usr = pw_usr AND passwd_type = pw_type;
1008
1009     IF NOT FOUND THEN
1010         -- no password row exists for this user + type.  Create one.
1011         INSERT INTO actor.passwd (usr, passwd_type, salt, passwd)
1012             VALUES (pw_usr, pw_type, pw_salt, pw_text);
1013     END IF;
1014
1015     RETURN TRUE;
1016 END;
1017 $$ LANGUAGE PLPGSQL;
1018
1019 CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT)
1020     RETURNS TEXT AS $$
1021 DECLARE
1022     pw_salt TEXT;
1023     type_row actor.passwd_type%ROWTYPE;
1024 BEGIN
1025     /* Returns the salt for the requested user + type.  If the password
1026      * type of "main" is requested and no password exists in actor.passwd,
1027      * the user's existing password is migrated and the new salt is returned.
1028      * Returns NULL if the password type is not crypt'ed (iter_count is NULL).
1029      */
1030
1031     SELECT INTO pw_salt salt FROM actor.passwd
1032         WHERE usr = pw_usr AND passwd_type = pw_type;
1033
1034     IF FOUND THEN
1035         RETURN pw_salt;
1036     END IF;
1037
1038     IF pw_type = 'main' THEN
1039         -- Main password has not yet been migrated.
1040         -- Do it now and return the newly created salt.
1041         RETURN actor.migrate_passwd(pw_usr);
1042     END IF;
1043
1044     -- We have no salt to return.  actor.create_salt() needed.
1045     RETURN NULL;
1046 END;
1047 $$ LANGUAGE PLPGSQL;
1048
1049 CREATE OR REPLACE FUNCTION
1050     actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$
1051 DECLARE
1052     pw_salt TEXT;
1053     usr_row actor.usr%ROWTYPE;
1054 BEGIN
1055     /* Migrates legacy actor.usr.passwd value to actor.passwd with
1056      * a password type 'main' and returns the new salt.  For backwards
1057      * compatibility with existing CHAP-style API's, we perform a
1058      * layer of intermediate MD5(MD5()) hashing.  This is intermediate
1059      * hashing is not required of other passwords.
1060      */
1061
1062     -- Avoid calling get_salt() here, because it may result in a
1063     -- migrate_passwd() call, creating a loop.
1064     SELECT INTO pw_salt salt FROM actor.passwd
1065         WHERE usr = pw_usr AND passwd_type = 'main';
1066
1067     -- Only migrate passwords that have not already been migrated.
1068     IF FOUND THEN
1069         RETURN pw_salt;
1070     END IF;
1071
1072     SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
1073
1074     pw_salt := actor.create_salt('main');
1075
1076     PERFORM actor.set_passwd(
1077         pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
1078
1079     -- clear the existing password
1080     UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
1081
1082     RETURN pw_salt;
1083 END;
1084 $$ LANGUAGE PLPGSQL;
1085
1086 CREATE OR REPLACE FUNCTION
1087     actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT)
1088     RETURNS BOOLEAN AS $$
1089 DECLARE
1090     pw_salt TEXT;
1091 BEGIN
1092     /* Returns TRUE if the password provided matches the in-db password.
1093      * If the password type is salted, we compare the output of CRYPT().
1094      * NOTE: test_passwd is MD5(salt || MD5(password)) for legacy
1095      * 'main' passwords.
1096      */
1097
1098     SELECT INTO pw_salt salt FROM actor.passwd
1099         WHERE usr = pw_usr AND passwd_type = pw_type;
1100
1101     IF NOT FOUND THEN
1102         -- no such password
1103         RETURN FALSE;
1104     END IF;
1105
1106     IF pw_salt IS NULL THEN
1107         -- Password is unsalted, compare the un-CRYPT'ed values.
1108         RETURN EXISTS (
1109             SELECT TRUE FROM actor.passwd WHERE
1110                 usr = pw_usr AND
1111                 passwd_type = pw_type AND
1112                 passwd = test_passwd
1113         );
1114     END IF;
1115
1116     RETURN EXISTS (
1117         SELECT TRUE FROM actor.passwd WHERE
1118             usr = pw_usr AND
1119             passwd_type = pw_type AND
1120             passwd = CRYPT(test_passwd, pw_salt)
1121     );
1122 END;
1123 $$ STRICT LANGUAGE PLPGSQL;
1124
1125 -- Remove all activity entries by activity type,
1126 -- except the most recent entry per user.
1127 CREATE OR REPLACE FUNCTION
1128     actor.purge_usr_activity_by_type(act_type INTEGER)
1129     RETURNS VOID AS $$
1130 DECLARE
1131     cur_usr INTEGER;
1132 BEGIN
1133     FOR cur_usr IN SELECT DISTINCT(usr)
1134         FROM actor.usr_activity WHERE etype = act_type LOOP
1135         DELETE FROM actor.usr_activity WHERE id IN (
1136             SELECT id
1137             FROM actor.usr_activity
1138             WHERE usr = cur_usr AND etype = act_type
1139             ORDER BY event_time DESC OFFSET 1
1140         );
1141
1142     END LOOP;
1143 END $$ LANGUAGE PLPGSQL;
1144
1145 CREATE TABLE actor.workstation_setting (
1146     id          SERIAL PRIMARY KEY,
1147     workstation INT    NOT NULL REFERENCES actor.workstation (id)
1148                        ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1149     name        TEXT   NOT NULL REFERENCES config.workstation_setting_type (name)
1150                        ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1151     value       JSON   NOT NULL,
1152     CONSTRAINT  ws_once_per_key UNIQUE (workstation, name)
1153 );
1154
1155 CREATE INDEX actor_workstation_setting_workstation_idx
1156     ON actor.workstation_setting (workstation);
1157
1158 CREATE TYPE actor.cascade_setting_summary AS (
1159     name TEXT,
1160     value JSON,
1161     has_org_setting BOOLEAN,
1162     has_user_setting BOOLEAN,
1163     has_workstation_setting BOOLEAN
1164 );
1165
1166 CREATE OR REPLACE FUNCTION actor.get_cascade_setting(
1167     setting_name TEXT, org_id INT, user_id INT, workstation_id INT)
1168     RETURNS actor.cascade_setting_summary AS
1169 $FUNC$
1170 DECLARE
1171     setting_value JSON;
1172     summary actor.cascade_setting_summary;
1173     org_setting_type config.org_unit_setting_type%ROWTYPE;
1174 BEGIN
1175
1176     summary.name := setting_name;
1177
1178     -- Collect the org setting type status first in case we exit early.
1179     -- The existance of an org setting type is not considered
1180     -- privileged information.
1181     SELECT INTO org_setting_type *
1182         FROM config.org_unit_setting_type WHERE name = setting_name;
1183     IF FOUND THEN
1184         summary.has_org_setting := TRUE;
1185     ELSE
1186         summary.has_org_setting := FALSE;
1187     END IF;
1188
1189     -- User and workstation settings have the same priority.
1190     -- Start with user settings since that's the simplest code path.
1191     -- The workstation_id is ignored if no user_id is provided.
1192     IF user_id IS NOT NULL THEN
1193
1194         SELECT INTO summary.value value FROM actor.usr_setting
1195             WHERE usr = user_id AND name = setting_name;
1196
1197         IF FOUND THEN
1198             -- if we have a value, we have a setting type
1199             summary.has_user_setting := TRUE;
1200
1201             IF workstation_id IS NOT NULL THEN
1202                 -- Only inform the caller about the workstation
1203                 -- setting type disposition when a workstation id is
1204                 -- provided.  Otherwise, it's NULL to indicate UNKNOWN.
1205                 summary.has_workstation_setting := FALSE;
1206             END IF;
1207
1208             RETURN summary;
1209         END IF;
1210
1211         -- no user setting value, but a setting type may exist
1212         SELECT INTO summary.has_user_setting EXISTS (
1213             SELECT TRUE FROM config.usr_setting_type
1214             WHERE name = setting_name
1215         );
1216
1217         IF workstation_id IS NOT NULL THEN
1218
1219             IF NOT summary.has_user_setting THEN
1220                 -- A workstation setting type may only exist when a user
1221                 -- setting type does not.
1222
1223                 SELECT INTO summary.value value
1224                     FROM actor.workstation_setting
1225                     WHERE workstation = workstation_id AND name = setting_name;
1226
1227                 IF FOUND THEN
1228                     -- if we have a value, we have a setting type
1229                     summary.has_workstation_setting := TRUE;
1230                     RETURN summary;
1231                 END IF;
1232
1233                 -- no value, but a setting type may exist
1234                 SELECT INTO summary.has_workstation_setting EXISTS (
1235                     SELECT TRUE FROM config.workstation_setting_type
1236                     WHERE name = setting_name
1237                 );
1238             END IF;
1239
1240             -- Finally make use of the workstation to determine the org
1241             -- unit if none is provided.
1242             IF org_id IS NULL AND summary.has_org_setting THEN
1243                 SELECT INTO org_id owning_lib
1244                     FROM actor.workstation WHERE id = workstation_id;
1245             END IF;
1246         END IF;
1247     END IF;
1248
1249     -- Some org unit settings are protected by a view permission.
1250     -- First see if we have any data that needs protecting, then
1251     -- check the permission if needed.
1252
1253     IF NOT summary.has_org_setting THEN
1254         RETURN summary;
1255     END IF;
1256
1257     -- avoid putting the value into the summary until we confirm
1258     -- the value should be visible to the caller.
1259     SELECT INTO setting_value value
1260         FROM actor.org_unit_ancestor_setting(setting_name, org_id);
1261
1262     IF NOT FOUND THEN
1263         -- No value found -- perm check is irrelevant.
1264         RETURN summary;
1265     END IF;
1266
1267     IF org_setting_type.view_perm IS NOT NULL THEN
1268
1269         IF user_id IS NULL THEN
1270             RAISE NOTICE 'Perm check required but no user_id provided';
1271             RETURN summary;
1272         END IF;
1273
1274         IF NOT permission.usr_has_perm(
1275             user_id, (SELECT code FROM permission.perm_list
1276                 WHERE id = org_setting_type.view_perm), org_id)
1277         THEN
1278             RAISE NOTICE 'Perm check failed for user % on %',
1279                 user_id, org_setting_type.view_perm;
1280             RETURN summary;
1281         END IF;
1282     END IF;
1283
1284     -- Perm check succeeded or was not necessary.
1285     summary.value := setting_value;
1286     RETURN summary;
1287 END;
1288 $FUNC$ LANGUAGE PLPGSQL;
1289
1290
1291 CREATE OR REPLACE FUNCTION actor.get_cascade_setting_batch(
1292     setting_names TEXT[], org_id INT, user_id INT, workstation_id INT)
1293     RETURNS SETOF actor.cascade_setting_summary AS
1294 $FUNC$
1295 -- Returns a row per setting matching the setting name order.  If no
1296 -- value is applied, NULL is returned to retain name-response ordering.
1297 DECLARE
1298     setting_name TEXT;
1299     summary actor.cascade_setting_summary;
1300 BEGIN
1301     FOREACH setting_name IN ARRAY setting_names LOOP
1302         SELECT INTO summary * FROM actor.get_cascade_setting(
1303             setting_Name, org_id, user_id, workstation_id);
1304         RETURN NEXT summary;
1305     END LOOP;
1306 END;
1307 $FUNC$ LANGUAGE PLPGSQL;
1308
1309 CREATE TABLE actor.usr_privacy_waiver (
1310     id BIGSERIAL PRIMARY KEY,
1311     usr BIGINT NOT NULL REFERENCES actor.usr(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1312     name TEXT NOT NULL,
1313     place_holds BOOL DEFAULT FALSE,
1314     pickup_holds BOOL DEFAULT FALSE,
1315     view_history BOOL DEFAULT FALSE,
1316     checkout_items BOOL DEFAULT FALSE
1317 );
1318 CREATE INDEX actor_usr_privacy_waiver_usr_idx ON actor.usr_privacy_waiver (usr);
1319
1320 COMMIT;