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