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         alias                   TEXT,
  46         day_phone               TEXT,
  47         evening_phone           TEXT,
  48         other_phone             TEXT,
  49         mailing_address         INT,
  50         billing_address         INT,
  51         home_ou                 INT                             NOT NULL,
  52         dob                     TIMESTAMP WITH TIME ZONE,
  53         active                  BOOL                            NOT NULL DEFAULT TRUE,
  54         master_account          BOOL                            NOT NULL DEFAULT FALSE,
  55         super_user              BOOL                            NOT NULL DEFAULT FALSE,
  56         barred                  BOOL                            NOT NULL DEFAULT FALSE,
  57         deleted                 BOOL                            NOT NULL DEFAULT FALSE,
  58         juvenile                BOOL                            NOT NULL DEFAULT FALSE,
  59         usrgroup                SERIAL                          NOT NULL,
  60         claims_returned_count   INT                             NOT NULL DEFAULT 0,
  61         credit_forward_balance  NUMERIC(6,2)                    NOT NULL DEFAULT 0.00,
  62         last_xact_id            TEXT                            NOT NULL DEFAULT 'none',
  63         alert_message           TEXT,
  64         create_date             TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
  65         expire_date             TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT (now() + '3 years'::INTERVAL),
  66         claims_never_checked_out_count  INT         NOT NULL DEFAULT 0,
  67     last_update_time    TIMESTAMP WITH TIME ZONE
  68 );
  69 COMMENT ON TABLE actor.usr IS $$
  70 User objects
  71
  72 This table contains the core User objects that describe both
  73 staff members and patrons.  The difference between the two
  74 types of users is based on the user's permissions.
  75 $$;
  76
  77 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
  78 CREATE INDEX actor_usr_usrgroup_idx ON actor.usr (usrgroup);
  79 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
  80 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
  81
  82 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name));
  83 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name));
  84 CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name));
  85
  86 CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname));
  87 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
  88
  89 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
  90 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
  91 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
  92
  93 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
  94     (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
  95
  96 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
  97     (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
  98
  99 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
 100     (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
 101
 102 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
 103 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
 104
 105 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
 106         BEGIN
 107                 NEW.passwd = MD5( NEW.passwd );
 108                 RETURN NEW;
 109         END;
 110 $$ LANGUAGE PLPGSQL;
 111
 112 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
 113         BEGIN
 114                 IF NEW.passwd <> OLD.passwd THEN
 115                         NEW.passwd = MD5( NEW.passwd );
 116                 END IF;
 117                 RETURN NEW;
 118         END;
 119 $$ LANGUAGE PLPGSQL;
 120
 121 CREATE OR REPLACE FUNCTION actor.au_updated()
 122 RETURNS TRIGGER AS $$
 123 BEGIN
 124     NEW.last_update_time := now();
 125         RETURN NEW;
 126 END;
 127 $$ LANGUAGE plpgsql;
 128
 129 CREATE TRIGGER au_update_trig
 130         BEFORE INSERT OR UPDATE ON actor.usr
 131         FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
 132
 133 CREATE TRIGGER actor_crypt_pw_update_trigger
 134         BEFORE UPDATE ON actor.usr FOR EACH ROW
 135         EXECUTE PROCEDURE actor.crypt_pw_update ();
 136
 137 CREATE TRIGGER actor_crypt_pw_insert_trigger
 138         BEFORE INSERT ON actor.usr FOR EACH ROW
 139         EXECUTE PROCEDURE actor.crypt_pw_insert ();
 140
 141 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;
 142
 143 CREATE TABLE actor.usr_note (
 144         id              BIGSERIAL                       PRIMARY KEY,
 145         usr             BIGINT                          NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 146         creator         BIGINT                          NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 147         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 148         pub             BOOL                            NOT NULL DEFAULT FALSE,
 149         title           TEXT                            NOT NULL,
 150         value           TEXT                            NOT NULL
 151 );
 152 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
 153 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
 154
 155 CREATE TABLE actor.usr_setting (
 156         id      BIGSERIAL       PRIMARY KEY,
 157         usr     INT             NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 158         name    TEXT            NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 159         value   TEXT            NOT NULL,
 160         CONSTRAINT usr_once_per_key UNIQUE (usr,name)
 161 );
 162 COMMENT ON TABLE actor.usr_setting IS $$
 163 User settings
 164
 165 This table contains any arbitrary settings that a client
 166 program would like to save for a user.
 167 $$;
 168
 169 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
 170
 171 CREATE TABLE actor.stat_cat_sip_fields (
 172     field   CHAR(2) PRIMARY KEY,
 173     name    TEXT    NOT NULL,
 174     one_only  BOOL    NOT NULL DEFAULT FALSE
 175 );
 176 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
 177 Actor Statistical Category SIP Fields
 178
 179 Contains the list of valid SIP Field identifiers for
 180 Statistical Categories.
 181 $$;
 182
 183 CREATE TABLE actor.stat_cat (
 184         id              SERIAL  PRIMARY KEY,
 185         owner           INT     NOT NULL,
 186         name            TEXT    NOT NULL,
 187         opac_visible    BOOL NOT NULL DEFAULT FALSE,
 188         usr_summary     BOOL NOT NULL DEFAULT FALSE,
 189     sip_field   CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 190     sip_format  TEXT,
 191     checkout_archive    BOOL NOT NULL DEFAULT FALSE,
 192         required        BOOL NOT NULL DEFAULT FALSE,
 193         allow_freetext  BOOL NOT NULL DEFAULT TRUE,
 194         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
 195 );
 196 COMMENT ON TABLE actor.stat_cat IS $$
 197 User Statistical Catagories
 198
 199 Local data collected about Users is placed into a Statistical
 200 Catagory.  Here's where those catagories are defined.
 201 $$;
 202
 203
 204 CREATE TABLE actor.stat_cat_entry (
 205         id              SERIAL  PRIMARY KEY,
 206         stat_cat        INT     NOT NULL,
 207         owner           INT     NOT NULL,
 208         value           TEXT    NOT NULL,
 209         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
 210 );
 211 COMMENT ON TABLE actor.stat_cat_entry IS $$
 212 User Statistical Catagory Entries
 213
 214 Local data collected about Users is placed into a Statistical
 215 Catagory.  Each library can create entries into any of its own
 216 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
 217 $$;
 218
 219
 220 CREATE TABLE actor.stat_cat_entry_usr_map (
 221         id              BIGSERIAL       PRIMARY KEY,
 222         stat_cat_entry  TEXT            NOT NULL,
 223         stat_cat        INT             NOT NULL,
 224         target_usr      INT             NOT NULL,
 225         CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
 226 );
 227 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
 228 Statistical Catagory Entry to User map
 229
 230 Records the stat_cat entries for each user.
 231 $$;
 232
 233 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
 234
 235 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
 236 DECLARE
 237     sipfield actor.stat_cat_sip_fields%ROWTYPE;
 238     use_count INT;
 239 BEGIN
 240     IF NEW.sip_field IS NOT NULL THEN
 241         SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
 242         IF sipfield.one_only THEN
 243             SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
 244             IF use_count > 0 THEN
 245                 RAISE EXCEPTION 'Sip field cannot be used twice';
 246             END IF;
 247         END IF;
 248     END IF;
 249     RETURN NEW;
 250 END;
 251 $func$ LANGUAGE PLPGSQL;
 252
 253 CREATE TRIGGER actor_stat_cat_sip_update_trigger
 254     BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
 255     EXECUTE PROCEDURE actor.stat_cat_check();
 256
 257 CREATE TABLE actor.card (
 258         id      SERIAL  PRIMARY KEY,
 259         usr     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 260         barcode TEXT    NOT NULL UNIQUE,
 261         active  BOOL    NOT NULL DEFAULT TRUE
 262 );
 263 COMMENT ON TABLE actor.card IS $$
 264 Library Cards
 265
 266 Each User has one or more library cards.  The current "main"
 267 card is linked to here from the actor.usr table, and it is up
 268 to the consortium policy whether more than one card can be
 269 active for any one user at a given time.
 270 $$;
 271
 272 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
 273 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
 274
 275 CREATE TABLE actor.org_unit_type (
 276         id              SERIAL  PRIMARY KEY,
 277         name            TEXT    NOT NULL,
 278         opac_label      TEXT    NOT NULL,
 279         depth           INT     NOT NULL,
 280         parent          INT     REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
 281         can_have_vols   BOOL    NOT NULL DEFAULT TRUE,
 282         can_have_users  BOOL    NOT NULL DEFAULT TRUE
 283 );
 284 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
 285
 286 CREATE TABLE actor.org_unit (
 287         id              SERIAL  PRIMARY KEY,
 288         parent_ou       INT     REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 289         ou_type         INT     NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
 290         ill_address     INT,
 291         holds_address   INT,
 292         mailing_address INT,
 293         billing_address INT,
 294         shortname       TEXT    NOT NULL UNIQUE,
 295         name            TEXT    NOT NULL UNIQUE,
 296         email           TEXT,
 297         phone           TEXT,
 298         opac_visible    BOOL    NOT NULL DEFAULT TRUE,
 299         fiscal_calendar INT     NOT NULL DEFAULT 1   -- foreign key constraint to be added later
 300 );
 301 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
 302 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
 303 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
 304 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
 305 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
 306 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
 307
 308 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
 309         DECLARE
 310                 current_aou actor.org_unit%ROWTYPE;
 311                 seen_ous    INT[];
 312                 depth_count INT;
 313         BEGIN
 314                 current_aou := NEW;
 315                 depth_count := 0;
 316                 seen_ous := ARRAY[NEW.id];
 317
 318                 IF (TG_OP = 'UPDATE') THEN
 319                         IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
 320                                 RETURN NEW; -- Doing an UPDATE with no change, just return it
 321                         END IF;
 322                 END IF;
 323
 324                 LOOP
 325                         IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
 326                                 RETURN NEW; -- No loop. Carry on.
 327                         END IF;
 328                         IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
 329                                 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
 330                         END IF;
 331                         -- Get the next one!
 332                         SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
 333                         seen_ous := seen_ous || current_aou.id;
 334                         depth_count := depth_count + 1;
 335                         IF depth_count = 100 THEN
 336                                 RAISE 'OU CHECK TOO DEEP';
 337                         END IF;
 338                 END LOOP;
 339
 340                 RETURN NEW;
 341         END;
 342 $$ LANGUAGE PLPGSQL;
 343
 344 CREATE TRIGGER actor_org_unit_parent_protect_trigger
 345     BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
 346     EXECUTE PROCEDURE actor.org_unit_parent_protect ();
 347
 348 CREATE TABLE actor.org_lasso (
 349     id      SERIAL  PRIMARY KEY,
 350     name        TEXT    UNIQUE
 351 );
 352
 353 CREATE TABLE actor.org_lasso_map (
 354     id          SERIAL  PRIMARY KEY,
 355     lasso       INT     NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 356     org_unit    INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 357 );
 358 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
 359 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
 360
 361 CREATE TABLE actor.org_unit_proximity (
 362         id              BIGSERIAL       PRIMARY KEY,
 363         from_org        INT,
 364         to_org          INT,
 365         prox            INT
 366 );
 367 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
 368
 369 CREATE TABLE actor.stat_cat_entry_default (
 370         id              SERIAL  PRIMARY KEY,
 371         stat_cat_entry  INT     NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 372         stat_cat        INT     NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 373         owner           INT     NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 374         CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
 375 );
 376 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
 377 User Statistical Category Default Entry
 378
 379 A library may choose one of the stat_cat entries to be the
 380 default entry.
 381 $$;
 382
 383
 384 CREATE TABLE actor.org_unit_proximity_adjustment (
 385     id                  SERIAL   PRIMARY KEY,
 386     item_circ_lib       INT         REFERENCES actor.org_unit (id),
 387     item_owning_lib     INT         REFERENCES actor.org_unit (id),
 388     copy_location       INT,        -- REFERENCES asset.copy_location (id),
 389     hold_pickup_lib     INT         REFERENCES actor.org_unit (id),
 390     hold_request_lib    INT         REFERENCES actor.org_unit (id),
 391     pos                 INT         NOT NULL DEFAULT 0,
 392     absolute_adjustment BOOL        NOT NULL DEFAULT FALSE,
 393     prox_adjustment     NUMERIC,
 394     circ_mod            TEXT,       -- REFERENCES config.circ_modifier (code),
 395     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)
 396 );
 397 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
 398     COALESCE(item_circ_lib, -1),
 399     COALESCE(item_owning_lib, -1),
 400     COALESCE(copy_location, -1),
 401     COALESCE(hold_pickup_lib, -1),
 402     COALESCE(hold_request_lib, -1),
 403     COALESCE(circ_mod, ''),
 404     pos
 405 );
 406 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
 407 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
 408 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
 409 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
 410 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
 411 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
 412
 413 CREATE TABLE actor.hours_of_operation (
 414         id              INT     PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 415         dow_0_open      TIME    NOT NULL DEFAULT '09:00',
 416         dow_0_close     TIME    NOT NULL DEFAULT '17:00',
 417         dow_1_open      TIME    NOT NULL DEFAULT '09:00',
 418         dow_1_close     TIME    NOT NULL DEFAULT '17:00',
 419         dow_2_open      TIME    NOT NULL DEFAULT '09:00',
 420         dow_2_close     TIME    NOT NULL DEFAULT '17:00',
 421         dow_3_open      TIME    NOT NULL DEFAULT '09:00',
 422         dow_3_close     TIME    NOT NULL DEFAULT '17:00',
 423         dow_4_open      TIME    NOT NULL DEFAULT '09:00',
 424         dow_4_close     TIME    NOT NULL DEFAULT '17:00',
 425         dow_5_open      TIME    NOT NULL DEFAULT '09:00',
 426         dow_5_close     TIME    NOT NULL DEFAULT '17:00',
 427         dow_6_open      TIME    NOT NULL DEFAULT '09:00',
 428         dow_6_close     TIME    NOT NULL DEFAULT '17:00'
 429 );
 430 COMMENT ON TABLE actor.hours_of_operation IS $$
 431 When does this org_unit usually open and close?  (Variations
 432 are expressed in the actor.org_unit_closed table.)
 433 $$;
 434 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
 435 When does this org_unit open on Monday?
 436 $$;
 437 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
 438 When does this org_unit close on Monday?
 439 $$;
 440 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
 441 When does this org_unit open on Tuesday?
 442 $$;
 443 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
 444 When does this org_unit close on Tuesday?
 445 $$;
 446 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
 447 When does this org_unit open on Wednesday?
 448 $$;
 449 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
 450 When does this org_unit close on Wednesday?
 451 $$;
 452 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
 453 When does this org_unit open on Thursday?
 454 $$;
 455 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
 456 When does this org_unit close on Thursday?
 457 $$;
 458 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
 459 When does this org_unit open on Friday?
 460 $$;
 461 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
 462 When does this org_unit close on Friday?
 463 $$;
 464 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
 465 When does this org_unit open on Saturday?
 466 $$;
 467 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
 468 When does this org_unit close on Saturday?
 469 $$;
 470 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
 471 When does this org_unit open on Sunday?
 472 $$;
 473 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
 474 When does this org_unit close on Sunday?
 475 $$;
 476
 477 CREATE TABLE actor.org_unit_closed (
 478         id              SERIAL                          PRIMARY KEY,
 479         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 480         close_start     TIMESTAMP WITH TIME ZONE        NOT NULL,
 481         close_end       TIMESTAMP WITH TIME ZONE        NOT NULL,
 482         reason          TEXT
 483 );
 484
 485 -- Workstation registration...
 486 CREATE TABLE actor.workstation (
 487         id              SERIAL  PRIMARY KEY,
 488         name            TEXT    NOT NULL UNIQUE,
 489         owning_lib      INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 490 );
 491
 492 CREATE TABLE actor.usr_org_unit_opt_in (
 493         id              SERIAL                          PRIMARY KEY,
 494         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 495         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 496         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 497         opt_in_ts       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 498         opt_in_ws       INT                             NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
 499         CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
 500 );
 501 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
 502
 503 CREATE TABLE actor.org_unit_setting (
 504         id              BIGSERIAL       PRIMARY KEY,
 505         org_unit        INT             NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 506         name            TEXT    NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
 507         value           TEXT            NOT NULL,
 508         CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
 509         CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
 510 );
 511 COMMENT ON TABLE actor.org_unit_setting IS $$
 512 Org Unit settings
 513
 514 This table contains any arbitrary settings that a client
 515 program would like to save for an org unit.
 516 $$;
 517
 518 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
 519
 520 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
 521 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
 522     DECLARE
 523     original TEXT;
 524     BEGIN
 525         -- Check for which setting is being updated, and log it.
 526         SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
 527
 528         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
 529
 530         RETURN NEW;
 531     END;
 532 $ous_change_log$ LANGUAGE plpgsql;
 533
 534 CREATE TRIGGER log_ous_change
 535     BEFORE INSERT OR UPDATE ON actor.org_unit_setting
 536     FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
 537
 538 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
 539     DECLARE
 540     original TEXT;
 541     BEGIN
 542         -- Check for which setting is being updated, and log it.
 543         SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
 544
 545         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
 546
 547         RETURN OLD;
 548     END;
 549 $ous_delete_log$ LANGUAGE plpgsql;
 550
 551 CREATE TRIGGER log_ous_del
 552     BEFORE DELETE ON actor.org_unit_setting
 553     FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
 554
 555
 556
 557
 558 CREATE TABLE actor.usr_address (
 559         id                      SERIAL  PRIMARY KEY,
 560         valid                   BOOL    NOT NULL DEFAULT TRUE,
 561         within_city_limits      BOOL    NOT NULL DEFAULT TRUE,
 562         address_type            TEXT    NOT NULL DEFAULT 'MAILING',
 563         usr                     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 564         street1                 TEXT    NOT NULL,
 565         street2                 TEXT,
 566         city                    TEXT    NOT NULL,
 567         county                  TEXT,
 568         state                   TEXT,
 569         country                 TEXT    NOT NULL,
 570         post_code               TEXT    NOT NULL,
 571     pending         BOOL    NOT NULL DEFAULT FALSE,
 572         replaces            INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
 573 );
 574
 575 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
 576
 577 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
 578 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
 579
 580 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
 581 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
 582 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
 583
 584 CREATE TABLE actor.usr_password_reset (
 585   id SERIAL PRIMARY KEY,
 586   uuid TEXT NOT NULL,
 587   usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
 588   request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 589   has_been_reset BOOL NOT NULL DEFAULT false
 590 );
 591 COMMENT ON TABLE actor.usr_password_reset IS $$
 592 Self-serve password reset requests
 593 $$;
 594 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
 595 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
 596 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
 597 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
 598
 599 CREATE TABLE actor.org_address (
 600         id              SERIAL  PRIMARY KEY,
 601         valid           BOOL    NOT NULL DEFAULT TRUE,
 602         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 603         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 604         street1         TEXT    NOT NULL,
 605         street2         TEXT,
 606         city            TEXT    NOT NULL,
 607         county          TEXT,
 608         state           TEXT,
 609         country         TEXT    NOT NULL,
 610         post_code       TEXT    NOT NULL,
 611     san         TEXT
 612 );
 613
 614 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
 615
 616 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
 617         SELECT SUBSTRING( $1, 1, 5);
 618 $$ LANGUAGE SQL;
 619
 620 CREATE TABLE actor.usr_standing_penalty (
 621         id                      SERIAL  PRIMARY KEY,
 622         org_unit                INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 623         usr                     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 624         standing_penalty        INT     NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 625         staff                   INT     REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 626         set_date                TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 627         stop_date               TIMESTAMP WITH TIME ZONE,
 628         note                    TEXT
 629 );
 630 COMMENT ON TABLE actor.usr_standing_penalty IS $$
 631 User standing penalties
 632 $$;
 633
 634 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
 635 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
 636
 637
 638 CREATE TABLE actor.usr_saved_search (
 639     id              SERIAL          PRIMARY KEY,
 640         owner           INT             NOT NULL REFERENCES actor.usr (id)
 641                                         ON DELETE CASCADE
 642                                         DEFERRABLE INITIALLY DEFERRED,
 643         name            TEXT            NOT NULL,
 644         create_date     TIMESTAMPTZ     NOT NULL DEFAULT now(),
 645         query_text      TEXT            NOT NULL,
 646         query_type      TEXT            NOT NULL
 647                                         CONSTRAINT valid_query_text CHECK (
 648                                         query_type IN ( 'URL' )) DEFAULT 'URL',
 649                                         -- we may add other types someday
 650         target          TEXT            NOT NULL
 651                                         CONSTRAINT valid_target CHECK (
 652                                         target IN ( 'record', 'metarecord', 'callnumber' )),
 653         CONSTRAINT name_once_per_user UNIQUE (owner, name)
 654 );
 655
 656 CREATE TABLE actor.address_alert (
 657     id              SERIAL  PRIMARY KEY,
 658     owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 659     active          BOOL    NOT NULL DEFAULT TRUE,
 660     match_all       BOOL    NOT NULL DEFAULT TRUE,
 661     alert_message   TEXT    NOT NULL,
 662     street1         TEXT,
 663     street2         TEXT,
 664     city            TEXT,
 665     county          TEXT,
 666     state           TEXT,
 667     country         TEXT,
 668     post_code       TEXT,
 669     mailing_address BOOL    NOT NULL DEFAULT FALSE,
 670     billing_address BOOL    NOT NULL DEFAULT FALSE
 671 );
 672
 673 CREATE TABLE actor.usr_activity (
 674     id          BIGSERIAL   PRIMARY KEY,
 675     usr         INT         REFERENCES actor.usr (id) ON DELETE SET NULL,
 676     etype       INT         NOT NULL REFERENCES config.usr_activity_type (id),
 677     event_time  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 678 );
 679 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
 680
 681 CREATE TABLE actor.toolbar (
 682     id          BIGSERIAL   PRIMARY KEY,
 683     ws          INT         REFERENCES actor.workstation (id) ON DELETE CASCADE,
 684     org         INT         REFERENCES actor.org_unit (id) ON DELETE CASCADE,
 685     usr         INT         REFERENCES actor.usr (id) ON DELETE CASCADE,
 686     label       TEXT        NOT NULL,
 687     layout      TEXT        NOT NULL,
 688     CONSTRAINT only_one_type CHECK (
 689         (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
 690         (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
 691         (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
 692     ),
 693     CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
 694 );
 695 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
 696 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
 697 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
 698
 699 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
 700 CREATE TABLE actor.org_unit_custom_tree (
 701     id              SERIAL  PRIMARY KEY,
 702     active          BOOLEAN DEFAULT FALSE,
 703     purpose         actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
 704 );
 705
 706 CREATE TABLE actor.org_unit_custom_tree_node (
 707     id              SERIAL  PRIMARY KEY,
 708     tree            INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
 709         org_unit        INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 710         parent_node     INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
 711     sibling_order   INTEGER NOT NULL DEFAULT 0,
 712     CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
 713 );
 714
 715 CREATE TABLE actor.search_query (
 716     id          SERIAL PRIMARY KEY,
 717     label       TEXT NOT NULL, -- i18n
 718     query_text  TEXT NOT NULL -- QP text
 719 );
 720
 721 CREATE TABLE actor.search_filter_group (
 722     id          SERIAL      PRIMARY KEY,
 723     owner       INT         NOT NULL REFERENCES actor.org_unit (id)
 724                             ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 725     code        TEXT        NOT NULL, -- for CGI, etc.
 726     label       TEXT        NOT NULL, -- i18n
 727     create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
 728     CONSTRAINT  asfg_label_once_per_org UNIQUE (owner, label),
 729     CONSTRAINT  asfg_code_once_per_org UNIQUE (owner, code)
 730 );
 731
 732 CREATE TABLE actor.search_filter_group_entry (
 733     id          SERIAL  PRIMARY KEY,
 734     grp         INT     NOT NULL REFERENCES actor.search_filter_group(id)
 735                         ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 736     pos         INT     NOT NULL DEFAULT 0,
 737     query       INT     NOT NULL REFERENCES actor.search_query(id)
 738                         ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 739     CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
 740 );
 741
 742 CREATE TABLE actor.usr_message (
 743         id              SERIAL                          PRIMARY KEY,
 744         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 745         title           TEXT,
 746         message         TEXT                            NOT NULL,
 747         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 748         deleted         BOOL                            NOT NULL DEFAULT FALSE,
 749         read_date       TIMESTAMP WITH TIME ZONE,
 750         sending_lib     INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 751 );
 752 CREATE INDEX aum_usr ON actor.usr_message (usr);
 753
 754 CREATE RULE protect_usr_message_delete AS
 755         ON DELETE TO actor.usr_message DO INSTEAD (
 756                 UPDATE  actor.usr_message
 757                   SET   deleted = TRUE
 758                   WHERE OLD.id = actor.usr_message.id
 759         );
 760
 761 CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$
 762 DECLARE
 763         sending_ou INTEGER;
 764 BEGIN
 765         IF NEW.pub THEN
 766                 IF TG_OP = 'UPDATE' THEN
 767                         IF OLD.pub = TRUE THEN
 768                                 RETURN NEW;
 769                         END IF;
 770                 END IF;
 771
 772                 SELECT INTO sending_ou aw.owning_lib
 773                 FROM auditor.get_audit_info() agai
 774                 JOIN actor.workstation aw ON (aw.id = agai.eg_ws);
 775                 IF sending_ou IS NULL THEN
 776                         SELECT INTO sending_ou home_ou
 777                         FROM actor.usr
 778                         WHERE id = NEW.creator;
 779                 END IF;
 780                 INSERT INTO actor.usr_message (usr, title, message, sending_lib)
 781                         VALUES (NEW.usr, NEW.title, NEW.value, sending_ou);
 782         END IF;
 783
 784         RETURN NEW;
 785 END;
 786 $$ LANGUAGE PLPGSQL;
 787
 788 CREATE TRIGGER convert_usr_note_to_message_tgr
 789         AFTER INSERT OR UPDATE ON actor.usr_note
 790         FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message();
 791
 792 -- limited view to ensure that a library user who somehow
 793 -- manages to figure out how to access pcrud cannot change
 794 -- the text of messages sent them
 795 CREATE VIEW actor.usr_message_limited
 796 AS SELECT * FROM actor.usr_message;
 797
 798 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
 799 BEGIN
 800     IF TG_OP = 'UPDATE' THEN
 801         UPDATE actor.usr_message
 802         SET    read_date = NEW.read_date,
 803                deleted   = NEW.deleted
 804         WHERE  id = NEW.id;
 805         RETURN NEW;
 806     END IF;
 807     RETURN NULL;
 808 END;
 809 $$ LANGUAGE PLPGSQL;
 810
 811 CREATE TRIGGER restrict_usr_message_limited_tgr
 812     INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
 813     FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
 814
 815 COMMIT;