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