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                     DATE,
  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 CREATE INDEX actor_usr_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(first_given_name));
  86 CREATE INDEX actor_usr_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(second_given_name));
  87 CREATE INDEX actor_usr_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(family_name));
  88
  89 CREATE INDEX actor_usr_usrname_idx ON actor.usr (evergreen.lowercase(usrname));
  90 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
  91
  92 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
  93 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
  94 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
  95
  96 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
  97     (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
  98
  99 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
 100     (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
 101
 102 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
 103     (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
 104
 105 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
 106 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
 107
 108 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
 109         BEGIN
 110                 NEW.passwd = MD5( NEW.passwd );
 111                 RETURN NEW;
 112         END;
 113 $$ LANGUAGE PLPGSQL;
 114
 115 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
 116         BEGIN
 117                 IF NEW.passwd <> OLD.passwd THEN
 118                         NEW.passwd = MD5( NEW.passwd );
 119                 END IF;
 120                 RETURN NEW;
 121         END;
 122 $$ LANGUAGE PLPGSQL;
 123
 124 CREATE OR REPLACE FUNCTION actor.au_updated()
 125 RETURNS TRIGGER AS $$
 126 BEGIN
 127     NEW.last_update_time := now();
 128         RETURN NEW;
 129 END;
 130 $$ LANGUAGE plpgsql;
 131
 132 CREATE TRIGGER au_update_trig
 133         BEFORE INSERT OR UPDATE ON actor.usr
 134         FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
 135
 136 CREATE TRIGGER actor_crypt_pw_update_trigger
 137         BEFORE UPDATE ON actor.usr FOR EACH ROW
 138         EXECUTE PROCEDURE actor.crypt_pw_update ();
 139
 140 CREATE TRIGGER actor_crypt_pw_insert_trigger
 141         BEFORE INSERT ON actor.usr FOR EACH ROW
 142         EXECUTE PROCEDURE actor.crypt_pw_insert ();
 143
 144 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;
 145
 146 CREATE TABLE actor.usr_note (
 147         id              BIGSERIAL                       PRIMARY KEY,
 148         usr             BIGINT                          NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 149         creator         BIGINT                          NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 150         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 151         pub             BOOL                            NOT NULL DEFAULT FALSE,
 152         title           TEXT                            NOT NULL,
 153         value           TEXT                            NOT NULL
 154 );
 155 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
 156 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
 157
 158 CREATE TABLE actor.usr_setting (
 159         id      BIGSERIAL       PRIMARY KEY,
 160         usr     INT             NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 161         name    TEXT            NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 162         value   TEXT            NOT NULL,
 163         CONSTRAINT usr_once_per_key UNIQUE (usr,name)
 164 );
 165 COMMENT ON TABLE actor.usr_setting IS $$
 166 User settings
 167
 168 This table contains any arbitrary settings that a client
 169 program would like to save for a user.
 170 $$;
 171
 172 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
 173
 174 CREATE TABLE actor.stat_cat_sip_fields (
 175     field   CHAR(2) PRIMARY KEY,
 176     name    TEXT    NOT NULL,
 177     one_only  BOOL    NOT NULL DEFAULT FALSE
 178 );
 179 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
 180 Actor Statistical Category SIP Fields
 181
 182 Contains the list of valid SIP Field identifiers for
 183 Statistical Categories.
 184 $$;
 185
 186 CREATE TABLE actor.stat_cat (
 187         id              SERIAL  PRIMARY KEY,
 188         owner           INT     NOT NULL,
 189         name            TEXT    NOT NULL,
 190         opac_visible    BOOL NOT NULL DEFAULT FALSE,
 191         usr_summary     BOOL NOT NULL DEFAULT FALSE,
 192     sip_field   CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 193     sip_format  TEXT,
 194     checkout_archive    BOOL NOT NULL DEFAULT FALSE,
 195         required        BOOL NOT NULL DEFAULT FALSE,
 196         allow_freetext  BOOL NOT NULL DEFAULT TRUE,
 197         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
 198 );
 199 COMMENT ON TABLE actor.stat_cat IS $$
 200 User Statistical Catagories
 201
 202 Local data collected about Users is placed into a Statistical
 203 Catagory.  Here's where those catagories are defined.
 204 $$;
 205
 206
 207 CREATE TABLE actor.stat_cat_entry (
 208         id              SERIAL  PRIMARY KEY,
 209         stat_cat        INT     NOT NULL,
 210         owner           INT     NOT NULL,
 211         value           TEXT    NOT NULL,
 212         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
 213 );
 214 COMMENT ON TABLE actor.stat_cat_entry IS $$
 215 User Statistical Catagory Entries
 216
 217 Local data collected about Users is placed into a Statistical
 218 Catagory.  Each library can create entries into any of its own
 219 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
 220 $$;
 221
 222
 223 CREATE TABLE actor.stat_cat_entry_usr_map (
 224         id              BIGSERIAL       PRIMARY KEY,
 225         stat_cat_entry  TEXT            NOT NULL,
 226         stat_cat        INT             NOT NULL,
 227         target_usr      INT             NOT NULL,
 228         CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
 229 );
 230 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
 231 Statistical Catagory Entry to User map
 232
 233 Records the stat_cat entries for each user.
 234 $$;
 235
 236 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
 237
 238 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
 239 DECLARE
 240     sipfield actor.stat_cat_sip_fields%ROWTYPE;
 241     use_count INT;
 242 BEGIN
 243     IF NEW.sip_field IS NOT NULL THEN
 244         SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
 245         IF sipfield.one_only THEN
 246             SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
 247             IF use_count > 0 THEN
 248                 RAISE EXCEPTION 'Sip field cannot be used twice';
 249             END IF;
 250         END IF;
 251     END IF;
 252     RETURN NEW;
 253 END;
 254 $func$ LANGUAGE PLPGSQL;
 255
 256 CREATE TRIGGER actor_stat_cat_sip_update_trigger
 257     BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
 258     EXECUTE PROCEDURE actor.stat_cat_check();
 259
 260 CREATE TABLE actor.card (
 261         id      SERIAL  PRIMARY KEY,
 262         usr     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 263         barcode TEXT    NOT NULL UNIQUE,
 264         active  BOOL    NOT NULL DEFAULT TRUE
 265 );
 266 COMMENT ON TABLE actor.card IS $$
 267 Library Cards
 268
 269 Each User has one or more library cards.  The current "main"
 270 card is linked to here from the actor.usr table, and it is up
 271 to the consortium policy whether more than one card can be
 272 active for any one user at a given time.
 273 $$;
 274
 275 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
 276 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
 277
 278 CREATE TABLE actor.org_unit_type (
 279         id              SERIAL  PRIMARY KEY,
 280         name            TEXT    NOT NULL,
 281         opac_label      TEXT    NOT NULL,
 282         depth           INT     NOT NULL,
 283         parent          INT     REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
 284         can_have_vols   BOOL    NOT NULL DEFAULT TRUE,
 285         can_have_users  BOOL    NOT NULL DEFAULT TRUE
 286 );
 287 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
 288
 289 CREATE TABLE actor.org_unit (
 290         id              SERIAL  PRIMARY KEY,
 291         parent_ou       INT     REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 292         ou_type         INT     NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
 293         ill_address     INT,
 294         holds_address   INT,
 295         mailing_address INT,
 296         billing_address INT,
 297         shortname       TEXT    NOT NULL UNIQUE,
 298         name            TEXT    NOT NULL UNIQUE,
 299         email           TEXT,
 300         phone           TEXT,
 301         opac_visible    BOOL    NOT NULL DEFAULT TRUE,
 302         fiscal_calendar INT     NOT NULL DEFAULT 1   -- foreign key constraint to be added later
 303 );
 304 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
 305 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
 306 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
 307 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
 308 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
 309 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
 310
 311 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
 312         DECLARE
 313                 current_aou actor.org_unit%ROWTYPE;
 314                 seen_ous    INT[];
 315                 depth_count INT;
 316         BEGIN
 317                 current_aou := NEW;
 318                 depth_count := 0;
 319                 seen_ous := ARRAY[NEW.id];
 320
 321                 IF (TG_OP = 'UPDATE') THEN
 322                         IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
 323                                 RETURN NEW; -- Doing an UPDATE with no change, just return it
 324                         END IF;
 325                 END IF;
 326
 327                 LOOP
 328                         IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
 329                                 RETURN NEW; -- No loop. Carry on.
 330                         END IF;
 331                         IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
 332                                 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
 333                         END IF;
 334                         -- Get the next one!
 335                         SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
 336                         seen_ous := seen_ous || current_aou.id;
 337                         depth_count := depth_count + 1;
 338                         IF depth_count = 100 THEN
 339                                 RAISE 'OU CHECK TOO DEEP';
 340                         END IF;
 341                 END LOOP;
 342
 343                 RETURN NEW;
 344         END;
 345 $$ LANGUAGE PLPGSQL;
 346
 347 CREATE TRIGGER actor_org_unit_parent_protect_trigger
 348     BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
 349     EXECUTE PROCEDURE actor.org_unit_parent_protect ();
 350
 351 CREATE TABLE actor.org_lasso (
 352     id      SERIAL  PRIMARY KEY,
 353     name        TEXT    UNIQUE
 354 );
 355
 356 CREATE TABLE actor.org_lasso_map (
 357     id          SERIAL  PRIMARY KEY,
 358     lasso       INT     NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 359     org_unit    INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 360 );
 361 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
 362 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
 363
 364 CREATE TABLE actor.org_unit_proximity (
 365         id              BIGSERIAL       PRIMARY KEY,
 366         from_org        INT,
 367         to_org          INT,
 368         prox            INT
 369 );
 370 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
 371
 372 CREATE TABLE actor.stat_cat_entry_default (
 373         id              SERIAL  PRIMARY KEY,
 374         stat_cat_entry  INT     NOT NULL REFERENCES actor.stat_cat_entry(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 375         stat_cat        INT     NOT NULL REFERENCES actor.stat_cat(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 376         owner           INT     NOT NULL REFERENCES actor.org_unit(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 377         CONSTRAINT sced_once_per_owner UNIQUE (stat_cat,owner)
 378 );
 379 COMMENT ON TABLE actor.stat_cat_entry_default IS $$
 380 User Statistical Category Default Entry
 381
 382 A library may choose one of the stat_cat entries to be the
 383 default entry.
 384 $$;
 385
 386
 387 CREATE TABLE actor.org_unit_proximity_adjustment (
 388     id                  SERIAL   PRIMARY KEY,
 389     item_circ_lib       INT         REFERENCES actor.org_unit (id),
 390     item_owning_lib     INT         REFERENCES actor.org_unit (id),
 391     copy_location       INT,        -- REFERENCES asset.copy_location (id),
 392     hold_pickup_lib     INT         REFERENCES actor.org_unit (id),
 393     hold_request_lib    INT         REFERENCES actor.org_unit (id),
 394     pos                 INT         NOT NULL DEFAULT 0,
 395     absolute_adjustment BOOL        NOT NULL DEFAULT FALSE,
 396     prox_adjustment     NUMERIC,
 397     circ_mod            TEXT,       -- REFERENCES config.circ_modifier (code),
 398     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)
 399 );
 400 CREATE UNIQUE INDEX prox_adj_once_idx ON actor.org_unit_proximity_adjustment (
 401     COALESCE(item_circ_lib, -1),
 402     COALESCE(item_owning_lib, -1),
 403     COALESCE(copy_location, -1),
 404     COALESCE(hold_pickup_lib, -1),
 405     COALESCE(hold_request_lib, -1),
 406     COALESCE(circ_mod, ''),
 407     pos
 408 );
 409 CREATE INDEX prox_adj_circ_lib_idx ON actor.org_unit_proximity_adjustment (item_circ_lib);
 410 CREATE INDEX prox_adj_owning_lib_idx ON actor.org_unit_proximity_adjustment (item_owning_lib);
 411 CREATE INDEX prox_adj_copy_location_idx ON actor.org_unit_proximity_adjustment (copy_location);
 412 CREATE INDEX prox_adj_pickup_lib_idx ON actor.org_unit_proximity_adjustment (hold_pickup_lib);
 413 CREATE INDEX prox_adj_request_lib_idx ON actor.org_unit_proximity_adjustment (hold_request_lib);
 414 CREATE INDEX prox_adj_circ_mod_idx ON actor.org_unit_proximity_adjustment (circ_mod);
 415
 416 CREATE TABLE actor.hours_of_operation (
 417         id              INT     PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 418         dow_0_open      TIME    NOT NULL DEFAULT '09:00',
 419         dow_0_close     TIME    NOT NULL DEFAULT '17:00',
 420         dow_1_open      TIME    NOT NULL DEFAULT '09:00',
 421         dow_1_close     TIME    NOT NULL DEFAULT '17:00',
 422         dow_2_open      TIME    NOT NULL DEFAULT '09:00',
 423         dow_2_close     TIME    NOT NULL DEFAULT '17:00',
 424         dow_3_open      TIME    NOT NULL DEFAULT '09:00',
 425         dow_3_close     TIME    NOT NULL DEFAULT '17:00',
 426         dow_4_open      TIME    NOT NULL DEFAULT '09:00',
 427         dow_4_close     TIME    NOT NULL DEFAULT '17:00',
 428         dow_5_open      TIME    NOT NULL DEFAULT '09:00',
 429         dow_5_close     TIME    NOT NULL DEFAULT '17:00',
 430         dow_6_open      TIME    NOT NULL DEFAULT '09:00',
 431         dow_6_close     TIME    NOT NULL DEFAULT '17:00'
 432 );
 433 COMMENT ON TABLE actor.hours_of_operation IS $$
 434 When does this org_unit usually open and close?  (Variations
 435 are expressed in the actor.org_unit_closed table.)
 436 $$;
 437 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
 438 When does this org_unit open on Monday?
 439 $$;
 440 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
 441 When does this org_unit close on Monday?
 442 $$;
 443 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
 444 When does this org_unit open on Tuesday?
 445 $$;
 446 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
 447 When does this org_unit close on Tuesday?
 448 $$;
 449 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
 450 When does this org_unit open on Wednesday?
 451 $$;
 452 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
 453 When does this org_unit close on Wednesday?
 454 $$;
 455 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
 456 When does this org_unit open on Thursday?
 457 $$;
 458 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
 459 When does this org_unit close on Thursday?
 460 $$;
 461 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
 462 When does this org_unit open on Friday?
 463 $$;
 464 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
 465 When does this org_unit close on Friday?
 466 $$;
 467 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
 468 When does this org_unit open on Saturday?
 469 $$;
 470 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
 471 When does this org_unit close on Saturday?
 472 $$;
 473 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
 474 When does this org_unit open on Sunday?
 475 $$;
 476 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
 477 When does this org_unit close on Sunday?
 478 $$;
 479
 480 CREATE TABLE actor.org_unit_closed (
 481         id              SERIAL                          PRIMARY KEY,
 482         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 483         close_start     TIMESTAMP WITH TIME ZONE        NOT NULL,
 484         close_end       TIMESTAMP WITH TIME ZONE        NOT NULL,
 485         reason          TEXT
 486 );
 487
 488 -- Workstation registration...
 489 CREATE TABLE actor.workstation (
 490         id              SERIAL  PRIMARY KEY,
 491         name            TEXT    NOT NULL UNIQUE,
 492         owning_lib      INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 493 );
 494
 495 CREATE TABLE actor.usr_org_unit_opt_in (
 496         id              SERIAL                          PRIMARY KEY,
 497         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 498         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 499         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 500         opt_in_ts       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 501         opt_in_ws       INT                             NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
 502         CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
 503 );
 504 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
 505
 506 CREATE TABLE actor.org_unit_setting (
 507         id              BIGSERIAL       PRIMARY KEY,
 508         org_unit        INT             NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 509         name            TEXT    NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
 510         value           TEXT            NOT NULL,
 511         CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
 512         CONSTRAINT aous_must_be_json CHECK ( evergreen.is_json(value) )
 513 );
 514 COMMENT ON TABLE actor.org_unit_setting IS $$
 515 Org Unit settings
 516
 517 This table contains any arbitrary settings that a client
 518 program would like to save for an org unit.
 519 $$;
 520
 521 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
 522
 523 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
 524 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
 525     DECLARE
 526     original TEXT;
 527     BEGIN
 528         -- Check for which setting is being updated, and log it.
 529         SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
 530
 531         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
 532
 533         RETURN NEW;
 534     END;
 535 $ous_change_log$ LANGUAGE plpgsql;
 536
 537 CREATE TRIGGER log_ous_change
 538     BEFORE INSERT OR UPDATE ON actor.org_unit_setting
 539     FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
 540
 541 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
 542     DECLARE
 543     original TEXT;
 544     BEGIN
 545         -- Check for which setting is being updated, and log it.
 546         SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
 547
 548         INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
 549
 550         RETURN OLD;
 551     END;
 552 $ous_delete_log$ LANGUAGE plpgsql;
 553
 554 CREATE TRIGGER log_ous_del
 555     BEFORE DELETE ON actor.org_unit_setting
 556     FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
 557
 558
 559
 560
 561 CREATE TABLE actor.usr_address (
 562         id                      SERIAL  PRIMARY KEY,
 563         valid                   BOOL    NOT NULL DEFAULT TRUE,
 564         within_city_limits      BOOL    NOT NULL DEFAULT TRUE,
 565         address_type            TEXT    NOT NULL DEFAULT 'MAILING',
 566         usr                     INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 567         street1                 TEXT    NOT NULL,
 568         street2                 TEXT,
 569         city                    TEXT    NOT NULL,
 570         county                  TEXT,
 571         state                   TEXT,
 572         country                 TEXT    NOT NULL,
 573         post_code               TEXT    NOT NULL,
 574     pending         BOOL    NOT NULL DEFAULT FALSE,
 575         replaces            INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
 576 );
 577
 578 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
 579
 580 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
 581 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
 582
 583 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
 584 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
 585 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
 586
 587 CREATE TABLE actor.usr_password_reset (
 588   id SERIAL PRIMARY KEY,
 589   uuid TEXT NOT NULL,
 590   usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
 591   request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 592   has_been_reset BOOL NOT NULL DEFAULT false
 593 );
 594 COMMENT ON TABLE actor.usr_password_reset IS $$
 595 Self-serve password reset requests
 596 $$;
 597 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
 598 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
 599 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
 600 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
 601
 602 CREATE TABLE actor.org_address (
 603         id              SERIAL  PRIMARY KEY,
 604         valid           BOOL    NOT NULL DEFAULT TRUE,
 605         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 606         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 607         street1         TEXT    NOT NULL,
 608         street2         TEXT,
 609         city            TEXT    NOT NULL,
 610         county          TEXT,
 611         state           TEXT,
 612         country         TEXT    NOT NULL,
 613         post_code       TEXT    NOT NULL,
 614     san         TEXT
 615 );
 616
 617 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
 618
 619 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
 620         SELECT SUBSTRING( $1, 1, 5);
 621 $$ LANGUAGE SQL;
 622
 623 CREATE TABLE actor.usr_standing_penalty (
 624         id                      SERIAL  PRIMARY KEY,
 625         org_unit                INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 626         usr                     INT     NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 627         standing_penalty        INT     NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 628         staff                   INT     REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 629         set_date                TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 630         stop_date               TIMESTAMP WITH TIME ZONE,
 631         note                    TEXT
 632 );
 633 COMMENT ON TABLE actor.usr_standing_penalty IS $$
 634 User standing penalties
 635 $$;
 636
 637 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
 638 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
 639
 640
 641 CREATE TABLE actor.usr_saved_search (
 642     id              SERIAL          PRIMARY KEY,
 643         owner           INT             NOT NULL REFERENCES actor.usr (id)
 644                                         ON DELETE CASCADE
 645                                         DEFERRABLE INITIALLY DEFERRED,
 646         name            TEXT            NOT NULL,
 647         create_date     TIMESTAMPTZ     NOT NULL DEFAULT now(),
 648         query_text      TEXT            NOT NULL,
 649         query_type      TEXT            NOT NULL
 650                                         CONSTRAINT valid_query_text CHECK (
 651                                         query_type IN ( 'URL' )) DEFAULT 'URL',
 652                                         -- we may add other types someday
 653         target          TEXT            NOT NULL
 654                                         CONSTRAINT valid_target CHECK (
 655                                         target IN ( 'record', 'metarecord', 'callnumber' )),
 656         CONSTRAINT name_once_per_user UNIQUE (owner, name)
 657 );
 658
 659 CREATE TABLE actor.address_alert (
 660     id              SERIAL  PRIMARY KEY,
 661     owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 662     active          BOOL    NOT NULL DEFAULT TRUE,
 663     match_all       BOOL    NOT NULL DEFAULT TRUE,
 664     alert_message   TEXT    NOT NULL,
 665     street1         TEXT,
 666     street2         TEXT,
 667     city            TEXT,
 668     county          TEXT,
 669     state           TEXT,
 670     country         TEXT,
 671     post_code       TEXT,
 672     mailing_address BOOL    NOT NULL DEFAULT FALSE,
 673     billing_address BOOL    NOT NULL DEFAULT FALSE
 674 );
 675
 676 CREATE TABLE actor.usr_activity (
 677     id          BIGSERIAL   PRIMARY KEY,
 678     usr         INT         REFERENCES actor.usr (id) ON DELETE SET NULL,
 679     etype       INT         NOT NULL REFERENCES config.usr_activity_type (id),
 680     event_time  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 681 );
 682 CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr);
 683
 684 CREATE TABLE actor.toolbar (
 685     id          BIGSERIAL   PRIMARY KEY,
 686     ws          INT         REFERENCES actor.workstation (id) ON DELETE CASCADE,
 687     org         INT         REFERENCES actor.org_unit (id) ON DELETE CASCADE,
 688     usr         INT         REFERENCES actor.usr (id) ON DELETE CASCADE,
 689     label       TEXT        NOT NULL,
 690     layout      TEXT        NOT NULL,
 691     CONSTRAINT only_one_type CHECK (
 692         (ws IS NOT NULL AND COALESCE(org,usr) IS NULL) OR
 693         (org IS NOT NULL AND COALESCE(ws,usr) IS NULL) OR
 694         (usr IS NOT NULL AND COALESCE(org,ws) IS NULL)
 695     ),
 696     CONSTRAINT layout_must_be_json CHECK ( is_json(layout) )
 697 );
 698 CREATE UNIQUE INDEX label_once_per_ws ON actor.toolbar (ws, label) WHERE ws IS NOT NULL;
 699 CREATE UNIQUE INDEX label_once_per_org ON actor.toolbar (org, label) WHERE org IS NOT NULL;
 700 CREATE UNIQUE INDEX label_once_per_usr ON actor.toolbar (usr, label) WHERE usr IS NOT NULL;
 701
 702 CREATE TYPE actor.org_unit_custom_tree_purpose AS ENUM ('opac');
 703 CREATE TABLE actor.org_unit_custom_tree (
 704     id              SERIAL  PRIMARY KEY,
 705     active          BOOLEAN DEFAULT FALSE,
 706     purpose         actor.org_unit_custom_tree_purpose NOT NULL DEFAULT 'opac' UNIQUE
 707 );
 708
 709 CREATE TABLE actor.org_unit_custom_tree_node (
 710     id              SERIAL  PRIMARY KEY,
 711     tree            INTEGER REFERENCES actor.org_unit_custom_tree (id) DEFERRABLE INITIALLY DEFERRED,
 712         org_unit        INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 713         parent_node     INTEGER REFERENCES actor.org_unit_custom_tree_node (id) DEFERRABLE INITIALLY DEFERRED,
 714     sibling_order   INTEGER NOT NULL DEFAULT 0,
 715     CONSTRAINT aouctn_once_per_org UNIQUE (tree, org_unit)
 716 );
 717
 718 CREATE TABLE actor.search_query (
 719     id          SERIAL PRIMARY KEY,
 720     label       TEXT NOT NULL, -- i18n
 721     query_text  TEXT NOT NULL -- QP text
 722 );
 723
 724 CREATE TABLE actor.search_filter_group (
 725     id          SERIAL      PRIMARY KEY,
 726     owner       INT         NOT NULL REFERENCES actor.org_unit (id)
 727                             ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 728     code        TEXT        NOT NULL, -- for CGI, etc.
 729     label       TEXT        NOT NULL, -- i18n
 730     create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
 731     CONSTRAINT  asfg_label_once_per_org UNIQUE (owner, label),
 732     CONSTRAINT  asfg_code_once_per_org UNIQUE (owner, code)
 733 );
 734
 735 CREATE TABLE actor.search_filter_group_entry (
 736     id          SERIAL  PRIMARY KEY,
 737     grp         INT     NOT NULL REFERENCES actor.search_filter_group(id)
 738                         ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 739     pos         INT     NOT NULL DEFAULT 0,
 740     query       INT     NOT NULL REFERENCES actor.search_query(id)
 741                         ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 742     CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
 743 );
 744
 745 CREATE TABLE actor.usr_message (
 746         id              SERIAL                          PRIMARY KEY,
 747         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 748         title           TEXT,
 749         message         TEXT                            NOT NULL,
 750         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 751         deleted         BOOL                            NOT NULL DEFAULT FALSE,
 752         read_date       TIMESTAMP WITH TIME ZONE,
 753         sending_lib     INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 754 );
 755 CREATE INDEX aum_usr ON actor.usr_message (usr);
 756
 757 CREATE RULE protect_usr_message_delete AS
 758         ON DELETE TO actor.usr_message DO INSTEAD (
 759                 UPDATE  actor.usr_message
 760                   SET   deleted = TRUE
 761                   WHERE OLD.id = actor.usr_message.id
 762         );
 763
 764 CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$
 765 DECLARE
 766         sending_ou INTEGER;
 767 BEGIN
 768         IF NEW.pub THEN
 769                 IF TG_OP = 'UPDATE' THEN
 770                         IF OLD.pub = TRUE THEN
 771                                 RETURN NEW;
 772                         END IF;
 773                 END IF;
 774
 775                 SELECT INTO sending_ou aw.owning_lib
 776                 FROM auditor.get_audit_info() agai
 777                 JOIN actor.workstation aw ON (aw.id = agai.eg_ws);
 778                 IF sending_ou IS NULL THEN
 779                         SELECT INTO sending_ou home_ou
 780                         FROM actor.usr
 781                         WHERE id = NEW.creator;
 782                 END IF;
 783                 INSERT INTO actor.usr_message (usr, title, message, sending_lib)
 784                         VALUES (NEW.usr, NEW.title, NEW.value, sending_ou);
 785         END IF;
 786
 787         RETURN NEW;
 788 END;
 789 $$ LANGUAGE PLPGSQL;
 790
 791 CREATE TRIGGER convert_usr_note_to_message_tgr
 792         AFTER INSERT OR UPDATE ON actor.usr_note
 793         FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message();
 794
 795 -- limited view to ensure that a library user who somehow
 796 -- manages to figure out how to access pcrud cannot change
 797 -- the text of messages sent them
 798 CREATE VIEW actor.usr_message_limited
 799 AS SELECT * FROM actor.usr_message;
 800
 801 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
 802 BEGIN
 803     IF TG_OP = 'UPDATE' THEN
 804         UPDATE actor.usr_message
 805         SET    read_date = NEW.read_date,
 806                deleted   = NEW.deleted
 807         WHERE  id = NEW.id;
 808         RETURN NEW;
 809     END IF;
 810     RETURN NULL;
 811 END;
 812 $$ LANGUAGE PLPGSQL;
 813
 814 CREATE TRIGGER restrict_usr_message_limited_tgr
 815     INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
 816     FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
 817
 818 CREATE TABLE actor.passwd_type (
 819     code        TEXT PRIMARY KEY,
 820     name        TEXT UNIQUE NOT NULL,
 821     login       BOOLEAN NOT NULL DEFAULT FALSE,
 822     regex       TEXT,   -- pending
 823     crypt_algo  TEXT,   -- e.g. 'bf'
 824
 825     -- gen_salt() iter count used with each new salt.
 826     -- A non-NULL value for iter_count is our indication the
 827     -- password is salted and encrypted via crypt()
 828     iter_count  INTEGER CHECK (iter_count IS NULL OR iter_count > 0)
 829 );
 830
 831 CREATE TABLE actor.passwd (
 832     id          SERIAL PRIMARY KEY,
 833     usr         INTEGER NOT NULL REFERENCES actor.usr(id)
 834                 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 835     salt        TEXT, -- will be NULL for non-crypt'ed passwords
 836     passwd      TEXT NOT NULL,
 837     passwd_type TEXT NOT NULL REFERENCES actor.passwd_type(code)
 838                 DEFERRABLE INITIALLY DEFERRED,
 839     create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 840     edit_date   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
 841     CONSTRAINT  passwd_type_once_per_user UNIQUE (usr, passwd_type)
 842 );
 843
 844 CREATE OR REPLACE FUNCTION actor.create_salt(pw_type TEXT)
 845     RETURNS TEXT AS $$
 846 DECLARE
 847     type_row actor.passwd_type%ROWTYPE;
 848 BEGIN
 849     /* Returns a new salt based on the passwd_type encryption settings.
 850      * Returns NULL If the password type is not crypt()'ed.
 851      */
 852
 853     SELECT INTO type_row * FROM actor.passwd_type WHERE code = pw_type;
 854
 855     IF NOT FOUND THEN
 856         RETURN EXCEPTION 'No such password type: %', pw_type;
 857     END IF;
 858
 859     IF type_row.iter_count IS NULL THEN
 860         -- This password type is unsalted.  That's OK.
 861         RETURN NULL;
 862     END IF;
 863
 864     RETURN gen_salt(type_row.crypt_algo, type_row.iter_count);
 865 END;
 866 $$ LANGUAGE PLPGSQL;
 867
 868
 869 /*
 870     TODO: when a user changes their password in the application, the
 871     app layer has access to the bare password.  At that point, we have
 872     the opportunity to store the new password without the MD5(MD5())
 873     intermediate hashing.  Do we care?  We would need a way to indicate
 874     which passwords have the legacy intermediate hashing and which don't
 875     so the app layer would know whether it should perform the intermediate
 876     hashing.  In either event, with the exception of migrate_passwd(), the
 877     DB functions know or care nothing about intermediate hashing.  Every
 878     password is just a value that may or may not be internally crypt'ed.
 879 */
 880
 881 CREATE OR REPLACE FUNCTION actor.set_passwd(
 882     pw_usr INTEGER, pw_type TEXT, new_pass TEXT, new_salt TEXT DEFAULT NULL)
 883     RETURNS BOOLEAN AS $$
 884 DECLARE
 885     pw_salt TEXT;
 886     pw_text TEXT;
 887 BEGIN
 888     /* Sets the password value, creating a new actor.passwd row if needed.
 889      * If the password type supports it, the new_pass value is crypt()'ed.
 890      * For crypt'ed passwords, the salt comes from one of 3 places in order:
 891      * new_salt (if present), existing salt (if present), newly created
 892      * salt.
 893      */
 894
 895     IF new_salt IS NOT NULL THEN
 896         pw_salt := new_salt;
 897     ELSE
 898         pw_salt := actor.get_salt(pw_usr, pw_type);
 899
 900         IF pw_salt IS NULL THEN
 901             /* We have no salt for this user + type.  Assume they want a
 902              * new salt.  If this type is unsalted, create_salt() will
 903              * return NULL. */
 904             pw_salt := actor.create_salt(pw_type);
 905         END IF;
 906     END IF;
 907
 908     IF pw_salt IS NULL THEN
 909         pw_text := new_pass; -- unsalted, use as-is.
 910     ELSE
 911         pw_text := CRYPT(new_pass, pw_salt);
 912     END IF;
 913
 914     UPDATE actor.passwd
 915         SET passwd = pw_text, salt = pw_salt, edit_date = NOW()
 916         WHERE usr = pw_usr AND passwd_type = pw_type;
 917
 918     IF NOT FOUND THEN
 919         -- no password row exists for this user + type.  Create one.
 920         INSERT INTO actor.passwd (usr, passwd_type, salt, passwd)
 921             VALUES (pw_usr, pw_type, pw_salt, pw_text);
 922     END IF;
 923
 924     RETURN TRUE;
 925 END;
 926 $$ LANGUAGE PLPGSQL;
 927
 928 CREATE OR REPLACE FUNCTION actor.get_salt(pw_usr INTEGER, pw_type TEXT)
 929     RETURNS TEXT AS $$
 930 DECLARE
 931     pw_salt TEXT;
 932     type_row actor.passwd_type%ROWTYPE;
 933 BEGIN
 934     /* Returns the salt for the requested user + type.  If the password
 935      * type of "main" is requested and no password exists in actor.passwd,
 936      * the user's existing password is migrated and the new salt is returned.
 937      * Returns NULL if the password type is not crypt'ed (iter_count is NULL).
 938      */
 939
 940     SELECT INTO pw_salt salt FROM actor.passwd
 941         WHERE usr = pw_usr AND passwd_type = pw_type;
 942
 943     IF FOUND THEN
 944         RETURN pw_salt;
 945     END IF;
 946
 947     IF pw_type = 'main' THEN
 948         -- Main password has not yet been migrated.
 949         -- Do it now and return the newly created salt.
 950         RETURN actor.migrate_passwd(pw_usr);
 951     END IF;
 952
 953     -- We have no salt to return.  actor.create_salt() needed.
 954     RETURN NULL;
 955 END;
 956 $$ LANGUAGE PLPGSQL;
 957
 958 CREATE OR REPLACE FUNCTION
 959     actor.migrate_passwd(pw_usr INTEGER) RETURNS TEXT AS $$
 960 DECLARE
 961     pw_salt TEXT;
 962     usr_row actor.usr%ROWTYPE;
 963 BEGIN
 964     /* Migrates legacy actor.usr.passwd value to actor.passwd with
 965      * a password type 'main' and returns the new salt.  For backwards
 966      * compatibility with existing CHAP-style API's, we perform a
 967      * layer of intermediate MD5(MD5()) hashing.  This is intermediate
 968      * hashing is not required of other passwords.
 969      */
 970
 971     -- Avoid calling get_salt() here, because it may result in a
 972     -- migrate_passwd() call, creating a loop.
 973     SELECT INTO pw_salt salt FROM actor.passwd
 974         WHERE usr = pw_usr AND passwd_type = 'main';
 975
 976     -- Only migrate passwords that have not already been migrated.
 977     IF FOUND THEN
 978         RETURN pw_salt;
 979     END IF;
 980
 981     SELECT INTO usr_row * FROM actor.usr WHERE id = pw_usr;
 982
 983     pw_salt := actor.create_salt('main');
 984
 985     PERFORM actor.set_passwd(
 986         pw_usr, 'main', MD5(pw_salt || usr_row.passwd), pw_salt);
 987
 988     -- clear the existing password
 989     UPDATE actor.usr SET passwd = '' WHERE id = usr_row.id;
 990
 991     RETURN pw_salt;
 992 END;
 993 $$ LANGUAGE PLPGSQL;
 994
 995 CREATE OR REPLACE FUNCTION
 996     actor.verify_passwd(pw_usr INTEGER, pw_type TEXT, test_passwd TEXT)
 997     RETURNS BOOLEAN AS $$
 998 DECLARE
 999     pw_salt TEXT;
1000 BEGIN
1001     /* Returns TRUE if the password provided matches the in-db password.
1002      * If the password type is salted, we compare the output of CRYPT().
1003      * NOTE: test_passwd is MD5(salt || MD5(password)) for legacy
1004      * 'main' passwords.
1005      */
1006
1007     SELECT INTO pw_salt salt FROM actor.passwd
1008         WHERE usr = pw_usr AND passwd_type = pw_type;
1009
1010     IF NOT FOUND THEN
1011         -- no such password
1012         RETURN FALSE;
1013     END IF;
1014
1015     IF pw_salt IS NULL THEN
1016         -- Password is unsalted, compare the un-CRYPT'ed values.
1017         RETURN EXISTS (
1018             SELECT TRUE FROM actor.passwd WHERE
1019                 usr = pw_usr AND
1020                 passwd_type = pw_type AND
1021                 passwd = test_passwd
1022         );
1023     END IF;
1024
1025     RETURN EXISTS (
1026         SELECT TRUE FROM actor.passwd WHERE
1027             usr = pw_usr AND
1028             passwd_type = pw_type AND
1029             passwd = CRYPT(test_passwd, pw_salt)
1030     );
1031 END;
1032 $$ STRICT LANGUAGE PLPGSQL;
1033
1034 COMMIT;