Open-ILS/src/sql/Pg/090.schema.action.sql

   1 /*
   2  * Copyright (C) 2004-2008  Georgia Public Library Service
   3  * Copyright (C) 2007-2008  Equinox Software, Inc.
   4  * Mike Rylander <miker@esilibrary.com>
   5  *
   6  * This program is free software; you can redistribute it and/or
   7  * modify it under the terms of the GNU General Public License
   8  * as published by the Free Software Foundation; either version 2
   9  * of the License, or (at your option) any later version.
  10  *
  11  * This program is distributed in the hope that it will be useful,
  12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14  * GNU General Public License for more details.
  15  *
  16  */
  17
  18 DROP SCHEMA IF EXISTS action CASCADE;
  19
  20 BEGIN;
  21
  22 CREATE SCHEMA action;
  23
  24 CREATE TABLE action.in_house_use (
  25         id              SERIAL                          PRIMARY KEY,
  26         item            BIGINT                          NOT NULL, -- REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
  27         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  28         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  29         use_time        TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
  30 );
  31 CREATE INDEX action_in_house_use_staff_idx      ON action.in_house_use ( staff );
  32
  33 CREATE TABLE action.non_cataloged_circulation (
  34         id              SERIAL                          PRIMARY KEY,
  35         patron          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  36         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  37         circ_lib        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  38         item_type       INT                             NOT NULL REFERENCES config.non_cataloged_type (id) DEFERRABLE INITIALLY DEFERRED,
  39         circ_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
  40 );
  41 CREATE INDEX action_non_cat_circ_patron_idx ON action.non_cataloged_circulation ( patron );
  42 CREATE INDEX action_non_cat_circ_staff_idx  ON action.non_cataloged_circulation ( staff );
  43
  44 CREATE TABLE action.non_cat_in_house_use (
  45         id              SERIAL                          PRIMARY KEY,
  46         item_type       BIGINT                          NOT NULL REFERENCES config.non_cataloged_type(id) DEFERRABLE INITIALLY DEFERRED,
  47         staff           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  48         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  49         use_time        TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
  50 );
  51 CREATE INDEX non_cat_in_house_use_staff_idx ON action.non_cat_in_house_use ( staff );
  52
  53 CREATE TABLE action.survey (
  54         id              SERIAL                          PRIMARY KEY,
  55         owner           INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  56         start_date      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  57         end_date        TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW() + '10 years'::INTERVAL,
  58         usr_summary     BOOL                            NOT NULL DEFAULT FALSE,
  59         opac            BOOL                            NOT NULL DEFAULT FALSE,
  60         poll            BOOL                            NOT NULL DEFAULT FALSE,
  61         required        BOOL                            NOT NULL DEFAULT FALSE,
  62         name            TEXT                            NOT NULL,
  63         description     TEXT                            NOT NULL
  64 );
  65 CREATE UNIQUE INDEX asv_once_per_owner_idx ON action.survey (owner,name);
  66
  67 CREATE TABLE action.survey_question (
  68         id              SERIAL  PRIMARY KEY,
  69         survey          INT     NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED,
  70         question        TEXT    NOT NULL
  71 );
  72
  73 CREATE TABLE action.survey_answer (
  74         id              SERIAL  PRIMARY KEY,
  75         question        INT     NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
  76         answer          TEXT    NOT NULL
  77 );
  78
  79 CREATE SEQUENCE action.survey_response_group_id_seq;
  80
  81 CREATE TABLE action.survey_response (
  82         id                      BIGSERIAL                       PRIMARY KEY,
  83         response_group_id       INT,
  84         usr                     INT, -- REFERENCES actor.usr
  85         survey                  INT                             NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED,
  86         question                INT                             NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
  87         answer                  INT                             NOT NULL REFERENCES action.survey_answer DEFERRABLE INITIALLY DEFERRED,
  88         answer_date             TIMESTAMP WITH TIME ZONE,
  89         effective_date          TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
  90 );
  91 CREATE INDEX action_survey_response_usr_idx ON action.survey_response ( usr );
  92
  93 CREATE OR REPLACE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS '
  94 BEGIN
  95         NEW.answer_date := NOW();
  96         RETURN NEW;
  97 END;
  98 ' LANGUAGE 'plpgsql';
  99 CREATE TRIGGER action_survey_response_answer_date_fixup_tgr
 100         BEFORE INSERT ON action.survey_response
 101         FOR EACH ROW
 102         EXECUTE PROCEDURE action.survey_response_answer_date_fixup ();
 103
 104 CREATE TABLE action.archive_actor_stat_cat (
 105     id          BIGSERIAL   PRIMARY KEY,
 106     xact        BIGINT      NOT NULL, -- action.circulation (+aged/all)
 107     stat_cat    INT         NOT NULL,
 108     value       TEXT        NOT NULL
 109 );
 110
 111 CREATE TABLE action.archive_asset_stat_cat (
 112     id          BIGSERIAL   PRIMARY KEY,
 113     xact        BIGINT      NOT NULL, -- action.circulation (+aged/all)
 114     stat_cat    INT         NOT NULL,
 115     value       TEXT        NOT NULL
 116 );
 117
 118
 119 CREATE TABLE action.circulation (
 120         target_copy             BIGINT                          NOT NULL, -- asset.copy.id
 121         circ_lib                INT                             NOT NULL, -- actor.org_unit.id
 122         circ_staff              INT                             NOT NULL, -- actor.usr.id
 123         checkin_staff           INT,                                      -- actor.usr.id
 124         checkin_lib             INT,                                      -- actor.org_unit.id
 125         renewal_remaining       INT                             NOT NULL, -- derived from "circ duration" rule
 126     grace_period           INTERVAL             NOT NULL, -- derived from "circ fine" rule
 127         due_date                TIMESTAMP WITH TIME ZONE,
 128         stop_fines_time         TIMESTAMP WITH TIME ZONE,
 129         checkin_time            TIMESTAMP WITH TIME ZONE,
 130         create_time             TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
 131         duration                INTERVAL,                                 -- derived from "circ duration" rule
 132         fine_interval           INTERVAL                        NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
 133         recurring_fine          NUMERIC(6,2),                             -- derived from "circ fine" rule
 134         max_fine                NUMERIC(6,2),                             -- derived from "max fine" rule
 135         phone_renewal           BOOL                            NOT NULL DEFAULT FALSE,
 136         desk_renewal            BOOL                            NOT NULL DEFAULT FALSE,
 137         opac_renewal            BOOL                            NOT NULL DEFAULT FALSE,
 138         duration_rule           TEXT                            NOT NULL, -- name of "circ duration" rule
 139         recurring_fine_rule     TEXT                            NOT NULL, -- name of "circ fine" rule
 140         max_fine_rule           TEXT                            NOT NULL, -- name of "max fine" rule
 141         stop_fines              TEXT                            CHECK (stop_fines IN (
 142                                                'CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE','CLAIMSNEVERCHECKEDOUT')),
 143         workstation         INT        REFERENCES actor.workstation(id)
 144                                        ON DELETE SET NULL
 145                                                                    DEFERRABLE INITIALLY DEFERRED,
 146         checkin_workstation INT        REFERENCES actor.workstation(id)
 147                                        ON DELETE SET NULL
 148                                                                    DEFERRABLE INITIALLY DEFERRED,
 149         copy_location   INT                             NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
 150         checkin_scan_time   TIMESTAMP WITH TIME ZONE,
 151     auto_renewal            BOOLEAN,
 152     auto_renewal_remaining  INTEGER
 153 ) INHERITS (money.billable_xact);
 154 ALTER TABLE action.circulation ADD PRIMARY KEY (id);
 155 ALTER TABLE action.circulation
 156        ADD COLUMN parent_circ BIGINT
 157        REFERENCES action.circulation( id )
 158        DEFERRABLE INITIALLY DEFERRED;
 159 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
 160 CREATE INDEX circ_outstanding_idx ON action.circulation (usr) WHERE checkin_time IS NULL;
 161 CREATE INDEX circ_checkin_time ON "action".circulation (checkin_time) WHERE checkin_time IS NOT NULL;
 162 CREATE INDEX circ_circ_lib_idx ON "action".circulation (circ_lib);
 163 CREATE INDEX circ_open_date_idx ON "action".circulation (xact_start) WHERE xact_finish IS NULL;
 164 CREATE INDEX circ_all_usr_idx       ON action.circulation ( usr );
 165 CREATE INDEX circ_circ_staff_idx    ON action.circulation ( circ_staff );
 166 CREATE INDEX circ_checkin_staff_idx ON action.circulation ( checkin_staff );
 167 CREATE INDEX action_circulation_target_copy_idx ON action.circulation (target_copy);
 168 CREATE UNIQUE INDEX circ_parent_idx ON action.circulation ( parent_circ ) WHERE parent_circ IS NOT NULL;
 169 CREATE UNIQUE INDEX only_one_concurrent_checkout_per_copy ON action.circulation(target_copy) WHERE checkin_time IS NULL;
 170
 171 CREATE TRIGGER action_circulation_target_copy_trig AFTER INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
 172
 173 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation');
 174 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
 175 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
 176
 177 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
 178 DECLARE
 179     proper_tz TEXT := COALESCE(
 180         oils_json_to_text((
 181             SELECT value
 182               FROM  actor.org_unit_ancestor_setting('lib.timezone',NEW.circ_lib)
 183               LIMIT 1
 184         )),
 185         CURRENT_SETTING('timezone')
 186     );
 187 BEGIN
 188
 189     IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 -- day-granular duration
 190         AND SUBSTRING((NEW.due_date AT TIME ZONE proper_tz)::TIME::TEXT FROM 1 FOR 8) <> '23:59:59' THEN -- has not yet been pushed
 191         NEW.due_date = ((NEW.due_date AT TIME ZONE proper_tz)::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL) || ' ' || proper_tz;
 192     END IF;
 193
 194     RETURN NEW;
 195 END;
 196 $$ LANGUAGE PLPGSQL;
 197
 198 CREATE TRIGGER push_due_date_tgr BEFORE INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.push_circ_due_time();
 199
 200 CREATE OR REPLACE FUNCTION action.fill_circ_copy_location () RETURNS TRIGGER AS $$
 201 BEGIN
 202     SELECT INTO NEW.copy_location location FROM asset.copy WHERE id = NEW.target_copy;
 203     RETURN NEW;
 204 END;
 205 $$ LANGUAGE PLPGSQL;
 206
 207 CREATE TRIGGER fill_circ_copy_location_tgr BEFORE INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.fill_circ_copy_location();
 208
 209 CREATE OR REPLACE FUNCTION action.archive_stat_cats () RETURNS TRIGGER AS $$
 210 BEGIN
 211     INSERT INTO action.archive_actor_stat_cat(xact, stat_cat, value)
 212         SELECT NEW.id, asceum.stat_cat, asceum.stat_cat_entry
 213         FROM actor.stat_cat_entry_usr_map asceum
 214              JOIN actor.stat_cat sc ON asceum.stat_cat = sc.id
 215         WHERE NEW.usr = asceum.target_usr AND sc.checkout_archive;
 216     INSERT INTO action.archive_asset_stat_cat(xact, stat_cat, value)
 217         SELECT NEW.id, ascecm.stat_cat, asce.value
 218         FROM asset.stat_cat_entry_copy_map ascecm
 219              JOIN asset.stat_cat sc ON ascecm.stat_cat = sc.id
 220              JOIN asset.stat_cat_entry asce ON ascecm.stat_cat_entry = asce.id
 221         WHERE NEW.target_copy = ascecm.owning_copy AND sc.checkout_archive;
 222     RETURN NULL;
 223 END;
 224 $$ LANGUAGE PLPGSQL;
 225
 226 CREATE TRIGGER archive_stat_cats_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.archive_stat_cats();
 227
 228 CREATE TABLE action.aged_circulation (
 229         usr_post_code           TEXT,
 230         usr_home_ou             INT     NOT NULL,
 231         usr_profile             INT     NOT NULL,
 232         usr_birth_year          INT,
 233         copy_call_number        INT     NOT NULL,
 234         copy_owning_lib         INT     NOT NULL,
 235         copy_circ_lib           INT     NOT NULL,
 236         copy_bib_record         BIGINT  NOT NULL,
 237         LIKE action.circulation
 238
 239 );
 240 ALTER TABLE action.aged_circulation ADD PRIMARY KEY (id);
 241 ALTER TABLE action.aged_circulation DROP COLUMN usr;
 242 CREATE INDEX aged_circ_circ_lib_idx ON "action".aged_circulation (circ_lib);
 243 CREATE INDEX aged_circ_start_idx ON "action".aged_circulation (xact_start);
 244 CREATE INDEX aged_circ_copy_circ_lib_idx ON "action".aged_circulation (copy_circ_lib);
 245 CREATE INDEX aged_circ_copy_owning_lib_idx ON "action".aged_circulation (copy_owning_lib);
 246 CREATE INDEX aged_circ_copy_location_idx ON "action".aged_circulation (copy_location);
 247 CREATE INDEX action_aged_circulation_target_copy_idx ON action.aged_circulation (target_copy);
 248 CREATE INDEX action_aged_circulation_parent_circ_idx ON action.aged_circulation (parent_circ);
 249
 250 CREATE OR REPLACE VIEW action.all_circulation AS
 251     SELECT  id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
 252         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
 253         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
 254         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
 255         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
 256         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
 257         auto_renewal, auto_renewal_remaining,
 258         NULL AS usr
 259       FROM  action.aged_circulation
 260             UNION ALL
 261     SELECT  DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
 262         cp.call_number AS copy_call_number, circ.copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
 263         cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
 264         circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
 265         circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
 266         circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
 267         circ.parent_circ, circ.auto_renewal, circ.auto_renewal_remaining, circ.usr
 268       FROM  action.circulation circ
 269         JOIN asset.copy cp ON (circ.target_copy = cp.id)
 270         JOIN asset.call_number cn ON (cp.call_number = cn.id)
 271         JOIN actor.usr p ON (circ.usr = p.id)
 272         LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
 273         LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
 274
 275 CREATE OR REPLACE VIEW action.all_circulation_slim AS
 276     SELECT * FROM action.circulation
 277 UNION ALL
 278     SELECT
 279         id,
 280         NULL AS usr,
 281         xact_start,
 282         xact_finish,
 283         unrecovered,
 284         target_copy,
 285         circ_lib,
 286         circ_staff,
 287         checkin_staff,
 288         checkin_lib,
 289         renewal_remaining,
 290         grace_period,
 291         due_date,
 292         stop_fines_time,
 293         checkin_time,
 294         create_time,
 295         duration,
 296         fine_interval,
 297         recurring_fine,
 298         max_fine,
 299         phone_renewal,
 300         desk_renewal,
 301         opac_renewal,
 302         duration_rule,
 303         recurring_fine_rule,
 304         max_fine_rule,
 305         stop_fines,
 306         workstation,
 307         checkin_workstation,
 308         copy_location,
 309         checkin_scan_time,
 310         auto_renewal,
 311         auto_renewal_remaining,
 312         parent_circ
 313     FROM action.aged_circulation
 314 ;
 315
 316
 317
 318 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
 319 DECLARE
 320 found char := 'N';
 321 BEGIN
 322
 323     -- If there are any renewals for this circulation, don't archive or delete
 324     -- it yet.   We'll do so later, when we archive and delete the renewals.
 325
 326     SELECT 'Y' INTO found
 327     FROM action.circulation
 328     WHERE parent_circ = OLD.id
 329     LIMIT 1;
 330
 331     IF found = 'Y' THEN
 332         RETURN NULL;  -- don't delete
 333         END IF;
 334
 335     -- Archive a copy of the old row to action.aged_circulation
 336
 337     INSERT INTO action.aged_circulation
 338         (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
 339         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
 340         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
 341         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
 342         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
 343         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
 344         auto_renewal, auto_renewal_remaining)
 345       SELECT
 346         id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
 347         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
 348         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
 349         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
 350         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
 351         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
 352         auto_renewal, auto_renewal_remaining
 353         FROM action.all_circulation WHERE id = OLD.id;
 354
 355     RETURN OLD;
 356 END;
 357 $$ LANGUAGE 'plpgsql';
 358
 359 CREATE TRIGGER action_circulation_aging_tgr
 360         BEFORE DELETE ON action.circulation
 361         FOR EACH ROW
 362         EXECUTE PROCEDURE action.age_circ_on_delete ();
 363
 364
 365 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
 366 BEGIN
 367
 368     -- Having deleted a renewal, we can delete the original circulation (or a previous
 369     -- renewal, if that's what parent_circ is pointing to).  That deletion will trigger
 370     -- deletion of any prior parents, etc. recursively.
 371
 372     IF OLD.parent_circ IS NOT NULL THEN
 373         DELETE FROM action.circulation
 374         WHERE id = OLD.parent_circ;
 375     END IF;
 376
 377     RETURN OLD;
 378 END;
 379 $$ LANGUAGE 'plpgsql';
 380
 381 CREATE TRIGGER age_parent_circ
 382         AFTER DELETE ON action.circulation
 383         FOR EACH ROW
 384         EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
 385
 386
 387 CREATE OR REPLACE VIEW action.open_circulation AS
 388         SELECT  *
 389           FROM  action.circulation
 390           WHERE checkin_time IS NULL
 391           ORDER BY due_date;
 392
 393
 394 CREATE OR REPLACE VIEW action.billable_circulations AS
 395         SELECT  *
 396           FROM  action.circulation
 397           WHERE xact_finish IS NULL;
 398
 399 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
 400 BEGIN
 401         IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
 402                 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
 403                         UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
 404                 END IF;
 405                 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
 406                         UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
 407                 END IF;
 408                 IF NEW.stop_fines = 'LOST' THEN
 409                         UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
 410                 END IF;
 411         END IF;
 412         RETURN NEW;
 413 END;
 414 $$ LANGUAGE 'plpgsql';
 415 CREATE TRIGGER action_circulation_stop_fines_tgr
 416         BEFORE UPDATE ON action.circulation
 417         FOR EACH ROW
 418         EXECUTE PROCEDURE action.circulation_claims_returned ();
 419
 420 CREATE TABLE action.hold_request_cancel_cause (
 421     id      SERIAL  PRIMARY KEY,
 422     label   TEXT    UNIQUE
 423 );
 424
 425 CREATE TABLE action.hold_request (
 426         id                      SERIAL                          PRIMARY KEY,
 427         request_time            TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 428         capture_time            TIMESTAMP WITH TIME ZONE,
 429         fulfillment_time        TIMESTAMP WITH TIME ZONE,
 430         checkin_time            TIMESTAMP WITH TIME ZONE,
 431         return_time             TIMESTAMP WITH TIME ZONE,
 432         prev_check_time         TIMESTAMP WITH TIME ZONE,
 433         expire_time             TIMESTAMP WITH TIME ZONE,
 434         cancel_time             TIMESTAMP WITH TIME ZONE,
 435         cancel_cause    INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 436         cancel_note             TEXT,
 437         target                  BIGINT                          NOT NULL, -- see hold_type
 438         current_copy            BIGINT,                         -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,  -- XXX could be an serial.unit now...
 439         fulfillment_staff       INT                             REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 440         fulfillment_lib         INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 441         request_lib             INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 442         requestor               INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 443         usr                     INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 444         selection_ou            INT                             NOT NULL,
 445         selection_depth         INT                             NOT NULL DEFAULT 0,
 446         pickup_lib              INT                             NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
 447         hold_type               TEXT                            NOT NULL, -- CHECK (hold_type IN ('M','T','V','C')),  -- XXX constraint too constraining...
 448         holdable_formats        TEXT,
 449         phone_notify            TEXT,
 450         email_notify            BOOL                            NOT NULL DEFAULT FALSE,
 451         sms_notify              TEXT,
 452         sms_carrier             INT REFERENCES config.sms_carrier (id),
 453         frozen                  BOOL                            NOT NULL DEFAULT FALSE,
 454         thaw_date               TIMESTAMP WITH TIME ZONE,
 455         shelf_time              TIMESTAMP WITH TIME ZONE,
 456     cut_in_line     BOOL,
 457         mint_condition  BOOL NOT NULL DEFAULT TRUE,
 458         shelf_expire_time TIMESTAMPTZ,
 459         current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
 460     behind_desk BOOLEAN NOT NULL DEFAULT FALSE
 461 );
 462 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
 463     sms_notify IS NULL
 464     OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
 465 );
 466
 467
 468 CREATE OR REPLACE FUNCTION action.hold_request_clear_map () RETURNS TRIGGER AS $$
 469 BEGIN
 470   DELETE FROM action.hold_copy_map WHERE hold = NEW.id;
 471   RETURN NEW;
 472 END;
 473 $$ LANGUAGE PLPGSQL;
 474
 475 CREATE TRIGGER hold_request_clear_map_tgr
 476     AFTER UPDATE ON action.hold_request
 477     FOR EACH ROW
 478     WHEN (
 479         (NEW.cancel_time IS NOT NULL AND OLD.cancel_time IS NULL)
 480         OR (NEW.fulfillment_time IS NOT NULL AND OLD.fulfillment_time IS NULL)
 481     )
 482     EXECUTE PROCEDURE action.hold_request_clear_map();
 483
 484 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
 485 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
 486 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
 487 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
 488 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
 489 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
 490 CREATE INDEX hold_request_requestor_idx         ON action.hold_request ( requestor );
 491 CREATE INDEX hold_request_open_idx ON action.hold_request (id) WHERE cancel_time IS NULL AND fulfillment_time IS NULL;
 492 CREATE INDEX hold_request_current_copy_before_cap_idx ON action.hold_request (current_copy) WHERE capture_time IS NULL AND cancel_time IS NULL;
 493 CREATE UNIQUE INDEX hold_request_capture_protect_idx ON action.hold_request (current_copy) WHERE current_copy IS NOT NULL AND capture_time IS NOT NULL AND cancel_time IS NULL AND fulfillment_time IS NULL;
 494 CREATE INDEX hold_request_copy_capture_time_idx ON action.hold_request (current_copy,capture_time);
 495 CREATE INDEX hold_request_open_captured_shelf_lib_idx ON action.hold_request (current_shelf_lib) WHERE capture_time IS NOT NULL AND fulfillment_time IS NULL AND (pickup_lib <> current_shelf_lib);
 496 CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
 497 CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);
 498
 499
 500 CREATE TABLE action.hold_request_note (
 501
 502     id     BIGSERIAL PRIMARY KEY,
 503     hold   BIGINT    NOT NULL REFERENCES action.hold_request (id)
 504                               ON DELETE CASCADE
 505                               DEFERRABLE INITIALLY DEFERRED,
 506     title  TEXT      NOT NULL,
 507     body   TEXT      NOT NULL,
 508     slip   BOOL      NOT NULL DEFAULT FALSE,
 509     pub    BOOL      NOT NULL DEFAULT FALSE,
 510     staff  BOOL      NOT NULL DEFAULT FALSE  -- created by staff
 511
 512 );
 513 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
 514
 515
 516 CREATE TABLE action.hold_notification (
 517         id              SERIAL                          PRIMARY KEY,
 518         hold            INT                             NOT NULL REFERENCES action.hold_request (id)
 519                                                                         ON DELETE CASCADE
 520                                                                         DEFERRABLE INITIALLY DEFERRED,
 521         notify_staff    INT                     REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 522         notify_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 523         method          TEXT                            NOT NULL, -- email address or phone number
 524         note            TEXT
 525 );
 526 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
 527 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
 528
 529 CREATE TABLE action.hold_copy_map (
 530         id              BIGSERIAL       PRIMARY KEY,
 531         hold            INT     NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 532         target_copy     BIGINT  NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
 533         proximity       NUMERIC,
 534         CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
 535 );
 536 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
 537 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
 538
 539 CREATE OR REPLACE FUNCTION
 540     action.hold_request_regen_copy_maps(
 541         hold_id INTEGER, copy_ids INTEGER[]) RETURNS VOID AS $$
 542     DELETE FROM action.hold_copy_map WHERE hold = $1;
 543     INSERT INTO action.hold_copy_map (hold, target_copy) SELECT $1, UNNEST($2);
 544 $$ LANGUAGE SQL;
 545
 546 CREATE TABLE action.transit_copy (
 547         id                      SERIAL                          PRIMARY KEY,
 548         source_send_time        TIMESTAMP WITH TIME ZONE,
 549         dest_recv_time          TIMESTAMP WITH TIME ZONE,
 550         target_copy             BIGINT                          NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
 551         source                  INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 552         dest                    INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 553         prev_hop                INT                             REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
 554         copy_status             INT                             NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
 555         persistant_transfer     BOOL                            NOT NULL DEFAULT FALSE,
 556         prev_dest               INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 557         cancel_time             TIMESTAMP WITH TIME ZONE
 558 );
 559 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
 560 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
 561 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
 562 CREATE INDEX active_transit_for_copy ON action.transit_copy (target_copy)
 563     WHERE dest_recv_time IS NULL AND cancel_time IS NULL;
 564
 565 -- Check for duplicate transits across all transit types
 566 CREATE OR REPLACE FUNCTION action.copy_transit_is_unique()
 567     RETURNS TRIGGER AS $func$
 568 BEGIN
 569     PERFORM * FROM action.transit_copy
 570         WHERE target_copy = NEW.target_copy
 571               AND dest_recv_time IS NULL
 572               AND cancel_time IS NULL;
 573
 574     IF FOUND THEN
 575         RAISE EXCEPTION 'Copy id=% is already in transit', NEW.target_copy;
 576     END IF;
 577     RETURN NULL;
 578 END;
 579 $func$ LANGUAGE PLPGSQL STABLE;
 580
 581 CREATE CONSTRAINT TRIGGER transit_copy_is_unique_check
 582     AFTER INSERT ON action.transit_copy
 583     FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
 584
 585 CREATE TABLE action.hold_transit_copy (
 586         hold    INT     REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
 587 ) INHERITS (action.transit_copy);
 588 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
 589 -- ALTER TABLE action.hold_transit_copy ADD CONSTRAINT ahtc_tc_fkey FOREIGN KEY (target_copy) REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -- XXX could be an serial.issuance
 590 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
 591 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
 592 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
 593 CREATE INDEX hold_transit_copy_hold_idx on action.hold_transit_copy (hold);
 594
 595 CREATE CONSTRAINT TRIGGER hold_transit_copy_is_unique_check
 596     AFTER INSERT ON action.hold_transit_copy
 597     FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
 598
 599
 600 CREATE TABLE action.unfulfilled_hold_list (
 601         id              BIGSERIAL                       PRIMARY KEY,
 602         current_copy    BIGINT                          NOT NULL,
 603         hold            INT                             NOT NULL,
 604         circ_lib        INT                             NOT NULL,
 605         fail_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
 606 );
 607 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
 608
 609 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
 610     SELECT  u.hold,
 611             c.circ_lib,
 612             count(*)
 613       FROM  action.unfulfilled_hold_list u
 614             JOIN asset.copy c ON (c.id = u.current_copy)
 615       GROUP BY 1,2;
 616
 617 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
 618     SELECT  hold,
 619             min(count)
 620       FROM  action.unfulfilled_hold_loops
 621       GROUP BY 1;
 622
 623 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
 624     SELECT  DISTINCT l.*
 625       FROM  action.unfulfilled_hold_loops l
 626             JOIN action.unfulfilled_hold_min_loop m USING (hold)
 627       WHERE l.count = m.min;
 628
 629 CREATE VIEW action.unfulfilled_hold_max_loop AS
 630     SELECT  hold,
 631             max(count) AS max
 632       FROM  action.unfulfilled_hold_loops
 633       GROUP BY 1;
 634
 635
 636 CREATE TABLE action.aged_hold_request (
 637     usr_post_code               TEXT,
 638     usr_home_ou         INT     NOT NULL,
 639     usr_profile         INT     NOT NULL,
 640     usr_birth_year              INT,
 641     staff_placed        BOOLEAN NOT NULL,
 642     LIKE action.hold_request
 643 );
 644 ALTER TABLE action.aged_hold_request
 645       ADD PRIMARY KEY (id),
 646       DROP COLUMN usr,
 647       DROP COLUMN requestor,
 648       DROP COLUMN sms_carrier,
 649       ALTER COLUMN phone_notify TYPE BOOLEAN
 650             USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
 651       ALTER COLUMN sms_notify TYPE BOOLEAN
 652             USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
 653       ALTER COLUMN phone_notify SET NOT NULL,
 654       ALTER COLUMN sms_notify SET NOT NULL;
 655 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
 656 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
 657 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
 658 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
 659
 660 CREATE OR REPLACE VIEW action.all_hold_request AS
 661     SELECT DISTINCT
 662            COALESCE(a.post_code, b.post_code) AS usr_post_code,
 663            p.home_ou AS usr_home_ou,
 664            p.profile AS usr_profile,
 665            EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
 666            CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
 667            ahr.id,
 668            ahr.request_time,
 669            ahr.capture_time,
 670            ahr.fulfillment_time,
 671            ahr.checkin_time,
 672            ahr.return_time,
 673            ahr.prev_check_time,
 674            ahr.expire_time,
 675            ahr.cancel_time,
 676            ahr.cancel_cause,
 677            ahr.cancel_note,
 678            ahr.target,
 679            ahr.current_copy,
 680            ahr.fulfillment_staff,
 681            ahr.fulfillment_lib,
 682            ahr.request_lib,
 683            ahr.selection_ou,
 684            ahr.selection_depth,
 685            ahr.pickup_lib,
 686            ahr.hold_type,
 687            ahr.holdable_formats,
 688            CASE
 689            WHEN ahr.phone_notify IS NULL THEN FALSE
 690            WHEN ahr.phone_notify = '' THEN FALSE
 691            ELSE TRUE
 692            END AS phone_notify,
 693            ahr.email_notify,
 694            CASE
 695            WHEN ahr.sms_notify IS NULL THEN FALSE
 696            WHEN ahr.sms_notify = '' THEN FALSE
 697            ELSE TRUE
 698            END AS sms_notify,
 699            ahr.frozen,
 700            ahr.thaw_date,
 701            ahr.shelf_time,
 702            ahr.cut_in_line,
 703            ahr.mint_condition,
 704            ahr.shelf_expire_time,
 705            ahr.current_shelf_lib,
 706            ahr.behind_desk
 707     FROM action.hold_request ahr
 708          JOIN actor.usr p ON (ahr.usr = p.id)
 709          LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
 710          LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
 711     UNION ALL
 712     SELECT
 713            usr_post_code,
 714            usr_home_ou,
 715            usr_profile,
 716            usr_birth_year,
 717            staff_placed,
 718            id,
 719            request_time,
 720            capture_time,
 721            fulfillment_time,
 722            checkin_time,
 723            return_time,
 724            prev_check_time,
 725            expire_time,
 726            cancel_time,
 727            cancel_cause,
 728            cancel_note,
 729            target,
 730            current_copy,
 731            fulfillment_staff,
 732            fulfillment_lib,
 733            request_lib,
 734            selection_ou,
 735            selection_depth,
 736            pickup_lib,
 737            hold_type,
 738            holdable_formats,
 739            phone_notify,
 740            email_notify,
 741            sms_notify,
 742            frozen,
 743            thaw_date,
 744            shelf_time,
 745            cut_in_line,
 746            mint_condition,
 747            shelf_expire_time,
 748            current_shelf_lib,
 749            behind_desk
 750     FROM action.aged_hold_request;
 751
 752 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
 753 DECLARE
 754 BEGIN
 755     -- Archive a copy of the old row to action.aged_hold_request
 756
 757     INSERT INTO action.aged_hold_request
 758            (usr_post_code,
 759             usr_home_ou,
 760             usr_profile,
 761             usr_birth_year,
 762             staff_placed,
 763             id,
 764             request_time,
 765             capture_time,
 766             fulfillment_time,
 767             checkin_time,
 768             return_time,
 769             prev_check_time,
 770             expire_time,
 771             cancel_time,
 772             cancel_cause,
 773             cancel_note,
 774             target,
 775             current_copy,
 776             fulfillment_staff,
 777             fulfillment_lib,
 778             request_lib,
 779             selection_ou,
 780             selection_depth,
 781             pickup_lib,
 782             hold_type,
 783             holdable_formats,
 784             phone_notify,
 785             email_notify,
 786             sms_notify,
 787             frozen,
 788             thaw_date,
 789             shelf_time,
 790             cut_in_line,
 791             mint_condition,
 792             shelf_expire_time,
 793             current_shelf_lib,
 794             behind_desk)
 795       SELECT
 796            usr_post_code,
 797            usr_home_ou,
 798            usr_profile,
 799            usr_birth_year,
 800            staff_placed,
 801            id,
 802            request_time,
 803            capture_time,
 804            fulfillment_time,
 805            checkin_time,
 806            return_time,
 807            prev_check_time,
 808            expire_time,
 809            cancel_time,
 810            cancel_cause,
 811            cancel_note,
 812            target,
 813            current_copy,
 814            fulfillment_staff,
 815            fulfillment_lib,
 816            request_lib,
 817            selection_ou,
 818            selection_depth,
 819            pickup_lib,
 820            hold_type,
 821            holdable_formats,
 822            phone_notify,
 823            email_notify,
 824            sms_notify,
 825            frozen,
 826            thaw_date,
 827            shelf_time,
 828            cut_in_line,
 829            mint_condition,
 830            shelf_expire_time,
 831            current_shelf_lib,
 832            behind_desk
 833         FROM action.all_hold_request WHERE id = OLD.id;
 834
 835     RETURN OLD;
 836 END;
 837 $$ LANGUAGE 'plpgsql';
 838
 839 CREATE TRIGGER action_hold_request_aging_tgr
 840         BEFORE DELETE ON action.hold_request
 841         FOR EACH ROW
 842         EXECUTE PROCEDURE action.age_hold_on_delete ();
 843
 844 CREATE TABLE action.fieldset_group (
 845     id              SERIAL  PRIMARY KEY,
 846     name            TEXT        NOT NULL,
 847     create_time     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 848     complete_time   TIMESTAMPTZ,
 849     container       INT,        -- Points to a container of some type ...
 850     container_type  TEXT,       -- One of 'biblio_record_entry', 'user', 'call_number', 'copy'
 851     can_rollback    BOOL        DEFAULT TRUE,
 852     rollback_group  INT         REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 853     rollback_time   TIMESTAMPTZ,
 854     creator         INT         NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 855     owning_lib      INT         NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 856 );
 857
 858 CREATE TABLE action.fieldset (
 859     id              SERIAL          PRIMARY KEY,
 860     fieldset_group  INT             REFERENCES action.fieldset_group (id)
 861                                     ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 862     owner           INT             NOT NULL REFERENCES actor.usr (id)
 863                                     DEFERRABLE INITIALLY DEFERRED,
 864         owning_lib      INT             NOT NULL REFERENCES actor.org_unit (id)
 865                                     DEFERRABLE INITIALLY DEFERRED,
 866         status          TEXT            NOT NULL
 867                                         CONSTRAINT valid_status CHECK ( status in
 868                                                                         ( 'PENDING', 'APPLIED', 'ERROR' )),
 869     creation_time   TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
 870     scheduled_time  TIMESTAMPTZ,
 871     applied_time    TIMESTAMPTZ,
 872     classname       TEXT            NOT NULL, -- an IDL class name
 873     name            TEXT            NOT NULL,
 874     error_msg       TEXT,
 875     stored_query    INT             REFERENCES query.stored_query (id)
 876                                     DEFERRABLE INITIALLY DEFERRED,
 877     pkey_value      TEXT,
 878         CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
 879     CONSTRAINT fieldset_one_or_the_other CHECK (
 880         (stored_query IS NOT NULL AND pkey_value IS NULL) OR
 881         (pkey_value IS NOT NULL AND stored_query IS NULL)
 882     )
 883         -- the CHECK constraint means we can update the fields for a single
 884         -- row without all the extra overhead involved in a query
 885 );
 886
 887 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
 888 CREATE INDEX action_owner_idx               ON action.fieldset( owner );
 889
 890
 891 CREATE TABLE action.fieldset_col_val (
 892     id              SERIAL  PRIMARY KEY,
 893     fieldset        INT     NOT NULL REFERENCES action.fieldset
 894                                          ON DELETE CASCADE
 895                                          DEFERRABLE INITIALLY DEFERRED,
 896     col             TEXT    NOT NULL,  -- "field" from the idl ... the column on the table
 897     val             TEXT,              -- value for the column ... NULL means, well, NULL
 898     CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
 899 );
 900
 901
 902 -- represents a circ chain summary
 903 CREATE TYPE action.circ_chain_summary AS (
 904     num_circs INTEGER,
 905     start_time TIMESTAMP WITH TIME ZONE,
 906     checkout_workstation TEXT,
 907     last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
 908     last_stop_fines TEXT,
 909     last_stop_fines_time TIMESTAMP WITH TIME ZONE,
 910     last_renewal_workstation TEXT, -- NULL if no renewals
 911     last_checkin_workstation TEXT,
 912     last_checkin_time TIMESTAMP WITH TIME ZONE,
 913     last_checkin_scan_time TIMESTAMP WITH TIME ZONE
 914 );
 915
 916
 917 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id BIGINT ) RETURNS SETOF action.circulation AS $$
 918 DECLARE
 919     tmp_circ action.circulation%ROWTYPE;
 920     circ_0 action.circulation%ROWTYPE;
 921 BEGIN
 922
 923     SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
 924
 925     IF tmp_circ IS NULL THEN
 926         RETURN NEXT tmp_circ;
 927     END IF;
 928     circ_0 := tmp_circ;
 929
 930     -- find the front of the chain
 931     WHILE TRUE LOOP
 932         SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
 933         IF tmp_circ IS NULL THEN
 934             EXIT;
 935         END IF;
 936         circ_0 := tmp_circ;
 937     END LOOP;
 938
 939     -- now send the circs to the caller, oldest to newest
 940     tmp_circ := circ_0;
 941     WHILE TRUE LOOP
 942         IF tmp_circ IS NULL THEN
 943             EXIT;
 944         END IF;
 945         RETURN NEXT tmp_circ;
 946         SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
 947     END LOOP;
 948
 949 END;
 950 $$ LANGUAGE 'plpgsql';
 951
 952 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id BIGINT ) RETURNS action.circ_chain_summary AS $$
 953
 954 DECLARE
 955
 956     -- first circ in the chain
 957     circ_0 action.circulation%ROWTYPE;
 958
 959     -- last circ in the chain
 960     circ_n action.circulation%ROWTYPE;
 961
 962     -- circ chain under construction
 963     chain action.circ_chain_summary;
 964     tmp_circ action.circulation%ROWTYPE;
 965
 966 BEGIN
 967
 968     chain.num_circs := 0;
 969     FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
 970
 971         IF chain.num_circs = 0 THEN
 972             circ_0 := tmp_circ;
 973         END IF;
 974
 975         chain.num_circs := chain.num_circs + 1;
 976         circ_n := tmp_circ;
 977     END LOOP;
 978
 979     chain.start_time := circ_0.xact_start;
 980     chain.last_stop_fines := circ_n.stop_fines;
 981     chain.last_stop_fines_time := circ_n.stop_fines_time;
 982     chain.last_checkin_time := circ_n.checkin_time;
 983     chain.last_checkin_scan_time := circ_n.checkin_scan_time;
 984     SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
 985     SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
 986
 987     IF chain.num_circs > 1 THEN
 988         chain.last_renewal_time := circ_n.xact_start;
 989         SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
 990     END IF;
 991
 992     RETURN chain;
 993
 994 END;
 995 $$ LANGUAGE 'plpgsql';
 996
 997 -- same as action.circ_chain, but returns action.all_circulation
 998 -- rows which may include aged circulations.
 999 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
1000     RETURNS SETOF action.all_circulation_slim AS $$
1001 DECLARE
1002     tmp_circ action.all_circulation_slim%ROWTYPE;
1003     circ_0 action.all_circulation_slim%ROWTYPE;
1004 BEGIN
1005
1006     SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
1007
1008     IF tmp_circ IS NULL THEN
1009         RETURN NEXT tmp_circ;
1010     END IF;
1011     circ_0 := tmp_circ;
1012
1013     -- find the front of the chain
1014     WHILE TRUE LOOP
1015         SELECT INTO tmp_circ * FROM action.all_circulation_slim
1016             WHERE id = tmp_circ.parent_circ;
1017         IF tmp_circ IS NULL THEN
1018             EXIT;
1019         END IF;
1020         circ_0 := tmp_circ;
1021     END LOOP;
1022
1023     -- now send the circs to the caller, oldest to newest
1024     tmp_circ := circ_0;
1025     WHILE TRUE LOOP
1026         IF tmp_circ IS NULL THEN
1027             EXIT;
1028         END IF;
1029         RETURN NEXT tmp_circ;
1030         SELECT INTO tmp_circ * FROM action.all_circulation_slim
1031             WHERE parent_circ = tmp_circ.id;
1032     END LOOP;
1033
1034 END;
1035 $$ LANGUAGE 'plpgsql';
1036
1037 -- same as action.summarize_circ_chain, but returns data collected
1038 -- from action.all_circulation, which may include aged circulations.
1039 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
1040     (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
1041
1042 DECLARE
1043
1044     -- first circ in the chain
1045     circ_0 action.all_circulation_slim%ROWTYPE;
1046
1047     -- last circ in the chain
1048     circ_n action.all_circulation_slim%ROWTYPE;
1049
1050     -- circ chain under construction
1051     chain action.circ_chain_summary;
1052     tmp_circ action.all_circulation_slim%ROWTYPE;
1053
1054 BEGIN
1055
1056     chain.num_circs := 0;
1057     FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
1058
1059         IF chain.num_circs = 0 THEN
1060             circ_0 := tmp_circ;
1061         END IF;
1062
1063         chain.num_circs := chain.num_circs + 1;
1064         circ_n := tmp_circ;
1065     END LOOP;
1066
1067     chain.start_time := circ_0.xact_start;
1068     chain.last_stop_fines := circ_n.stop_fines;
1069     chain.last_stop_fines_time := circ_n.stop_fines_time;
1070     chain.last_checkin_time := circ_n.checkin_time;
1071     chain.last_checkin_scan_time := circ_n.checkin_scan_time;
1072     SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
1073     SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
1074
1075     IF chain.num_circs > 1 THEN
1076         chain.last_renewal_time := circ_n.xact_start;
1077         SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
1078     END IF;
1079
1080     RETURN chain;
1081
1082 END;
1083 $$ LANGUAGE 'plpgsql';
1084
1085 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
1086 DECLARE
1087     h               action.hold_request%ROWTYPE;
1088     view_age        INTERVAL;
1089     view_count      INT;
1090     usr_view_count  actor.usr_setting%ROWTYPE;
1091     usr_view_age    actor.usr_setting%ROWTYPE;
1092     usr_view_start  actor.usr_setting%ROWTYPE;
1093 BEGIN
1094     SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
1095     SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
1096     SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
1097
1098     FOR h IN
1099         SELECT  *
1100           FROM  action.hold_request
1101           WHERE usr = usr_id
1102                 AND fulfillment_time IS NULL
1103                 AND cancel_time IS NULL
1104           ORDER BY request_time DESC
1105     LOOP
1106         RETURN NEXT h;
1107     END LOOP;
1108
1109     IF usr_view_start.value IS NULL THEN
1110         RETURN;
1111     END IF;
1112
1113     IF usr_view_age.value IS NOT NULL THEN
1114         -- User opted in and supplied a retention age
1115         IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
1116             view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1117         ELSE
1118             view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
1119         END IF;
1120     ELSE
1121         -- User opted in
1122         view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1123     END IF;
1124
1125     IF usr_view_count.value IS NOT NULL THEN
1126         view_count := oils_json_to_text(usr_view_count.value)::INT;
1127     ELSE
1128         view_count := 1000;
1129     END IF;
1130
1131     -- show some fulfilled/canceled holds
1132     FOR h IN
1133         SELECT  *
1134           FROM  action.hold_request
1135           WHERE usr = usr_id
1136                 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
1137                 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
1138           ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
1139           LIMIT view_count
1140     LOOP
1141         RETURN NEXT h;
1142     END LOOP;
1143
1144     RETURN;
1145 END;
1146 $func$ LANGUAGE PLPGSQL;
1147
1148 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
1149 DECLARE
1150     org_keep_age    INTERVAL;
1151     org_use_last    BOOL = false;
1152     org_age_is_min  BOOL = false;
1153     org_keep_count  INT;
1154
1155     keep_age        INTERVAL;
1156
1157     target_acp      RECORD;
1158     circ_chain_head action.circulation%ROWTYPE;
1159     circ_chain_tail action.circulation%ROWTYPE;
1160
1161     count_purged    INT;
1162     num_incomplete  INT;
1163
1164     last_finished   TIMESTAMP WITH TIME ZONE;
1165 BEGIN
1166
1167     count_purged := 0;
1168
1169     SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
1170
1171     SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
1172     IF org_keep_count IS NULL THEN
1173         RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
1174     END IF;
1175
1176     SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
1177     SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
1178
1179     -- First, find copies with more than keep_count non-renewal circs
1180     FOR target_acp IN
1181         SELECT  target_copy,
1182                 COUNT(*) AS total_real_circs
1183           FROM  action.circulation
1184           WHERE parent_circ IS NULL
1185                 AND xact_finish IS NOT NULL
1186           GROUP BY target_copy
1187           HAVING COUNT(*) > org_keep_count
1188     LOOP
1189         -- And, for those, select circs that are finished and older than keep_age
1190         FOR circ_chain_head IN
1191             -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
1192             -- The outer query then uses that information to skip the most recent set the library wants to keep
1193             -- End result is we don't care what order they come out in, as they are all potentials for deletion.
1194             SELECT ac.* FROM action.circulation ac JOIN (
1195               SELECT  rank() OVER (ORDER BY xact_start DESC), ac.id
1196                 FROM  action.circulation ac
1197                 WHERE ac.target_copy = target_acp.target_copy
1198                   AND ac.parent_circ IS NULL
1199                 ORDER BY ac.xact_start ) ranked USING (id)
1200                 WHERE ranked.rank > org_keep_count
1201         LOOP
1202
1203             SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
1204             SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id);
1205             CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
1206
1207             IF NOT org_use_last THEN
1208                 last_finished := circ_chain_tail.xact_finish;
1209             END IF;
1210
1211             keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
1212
1213             IF org_age_is_min THEN
1214                 keep_age := GREATEST( keep_age, org_keep_age );
1215             END IF;
1216
1217             CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
1218
1219             -- We've passed the purging tests, purge the circ chain starting at the end
1220             -- A trigger should auto-purge the rest of the chain.
1221             DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
1222
1223             count_purged := count_purged + 1;
1224
1225         END LOOP;
1226     END LOOP;
1227
1228     return count_purged;
1229 END;
1230 $func$ LANGUAGE PLPGSQL;
1231
1232 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
1233 DECLARE
1234   current_hold RECORD;
1235   purged_holds INT;
1236   cgf_d INTERVAL;
1237   cgf_f INTERVAL;
1238   cgf_c INTERVAL;
1239   prev_usr INT;
1240   user_start TIMESTAMPTZ;
1241   user_age INTERVAL;
1242   user_count INT;
1243 BEGIN
1244   purged_holds := 0;
1245   SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
1246   SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
1247   SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
1248   FOR current_hold IN
1249     SELECT
1250       rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
1251       cgf_cs.value::INTERVAL as cgf_cs,
1252       ahr.*
1253     FROM
1254       action.hold_request ahr
1255       LEFT JOIN config.global_flag cgf_cs ON (ahr.cancel_cause IS NOT NULL AND cgf_cs.name = 'history.hold.retention_age_canceled_' || ahr.cancel_cause AND cgf_cs.enabled)
1256     WHERE
1257       (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
1258   LOOP
1259     IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
1260       prev_usr := current_hold.usr;
1261       SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
1262       SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
1263       SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
1264       IF user_start IS NOT NULL THEN
1265         user_age := LEAST(user_age, AGE(NOW(), user_start));
1266       END IF;
1267       IF user_count IS NULL THEN
1268         user_count := 1000; -- Assumption based on the user visible holds routine
1269       END IF;
1270     END IF;
1271     -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
1272     IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
1273       CONTINUE;
1274     END IF;
1275     IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
1276       CONTINUE;
1277     END IF;
1278
1279     -- User keep age needs combining with count. If too old AND within the count, keep!
1280     IF user_start IS NOT NULL AND COALESCE(current_hold.fulfillment_time, current_hold.cancel_time) > NOW() - user_age AND current_hold.rank <= user_count THEN
1281       CONTINUE;
1282     END IF;
1283
1284     -- All checks should have passed, delete!
1285     DELETE FROM action.hold_request WHERE id = current_hold.id;
1286     purged_holds := purged_holds + 1;
1287   END LOOP;
1288   RETURN purged_holds;
1289 END;
1290 $func$ LANGUAGE plpgsql;
1291
1292 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1293     fieldset_id IN INT,        -- id from action.fieldset
1294     table_name  IN TEXT,       -- table to be updated
1295     pkey_name   IN TEXT,       -- name of primary key column in that table
1296     query       IN TEXT        -- query constructed by qstore (for query-based
1297                                --    fieldsets only; otherwise null
1298 )
1299 RETURNS TEXT AS $$
1300 DECLARE
1301     statement TEXT;
1302     where_clause TEXT;
1303     fs_status TEXT;
1304     fs_pkey_value TEXT;
1305     fs_query TEXT;
1306     sep CHAR;
1307     status_code TEXT;
1308     msg TEXT;
1309     fs_id INT;
1310     fsg_id INT;
1311     update_count INT;
1312     cv RECORD;
1313     fs_obj action.fieldset%ROWTYPE;
1314     fs_group action.fieldset_group%ROWTYPE;
1315     rb_row RECORD;
1316 BEGIN
1317     -- Sanity checks
1318     IF fieldset_id IS NULL THEN
1319         RETURN 'Fieldset ID parameter is NULL';
1320     END IF;
1321     IF table_name IS NULL THEN
1322         RETURN 'Table name parameter is NULL';
1323     END IF;
1324     IF pkey_name IS NULL THEN
1325         RETURN 'Primary key name parameter is NULL';
1326     END IF;
1327
1328     SELECT
1329         status,
1330         quote_literal( pkey_value )
1331     INTO
1332         fs_status,
1333         fs_pkey_value
1334     FROM
1335         action.fieldset
1336     WHERE
1337         id = fieldset_id;
1338
1339     --
1340     -- Build the WHERE clause.  This differs according to whether it's a
1341     -- single-row fieldset or a query-based fieldset.
1342     --
1343     IF query IS NULL        AND fs_pkey_value IS NULL THEN
1344         RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1345     ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1346         fs_query := rtrim( query, ';' );
1347         where_clause := 'WHERE ' || pkey_name || ' IN ( '
1348                      || fs_query || ' )';
1349     ELSIF query IS NULL     AND fs_pkey_value IS NOT NULL THEN
1350         where_clause := 'WHERE ' || pkey_name || ' = ';
1351         IF pkey_name = 'id' THEN
1352             where_clause := where_clause || fs_pkey_value;
1353         ELSIF pkey_name = 'code' THEN
1354             where_clause := where_clause || quote_literal(fs_pkey_value);
1355         ELSE
1356             RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1357         END IF;
1358     ELSE  -- both are not null
1359         RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1360     END IF;
1361
1362     IF fs_status IS NULL THEN
1363         RETURN 'No fieldset found for id = ' || fieldset_id;
1364     ELSIF fs_status = 'APPLIED' THEN
1365         RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1366     END IF;
1367
1368     SELECT * INTO fs_obj FROM action.fieldset WHERE id = fieldset_id;
1369     SELECT * INTO fs_group FROM action.fieldset_group WHERE id = fs_obj.fieldset_group;
1370
1371     IF fs_group.can_rollback THEN
1372         -- This is part of a non-rollback group.  We need to record the current values for future rollback.
1373
1374         INSERT INTO action.fieldset_group (can_rollback, name, creator, owning_lib, container, container_type)
1375             VALUES (FALSE, 'ROLLBACK: '|| fs_group.name, fs_group.creator, fs_group.owning_lib, fs_group.container, fs_group.container_type);
1376
1377         fsg_id := CURRVAL('action.fieldset_group_id_seq');
1378
1379         FOR rb_row IN EXECUTE 'SELECT * FROM ' || table_name || ' ' || where_clause LOOP
1380             IF pkey_name = 'id' THEN
1381                 fs_pkey_value := rb_row.id;
1382             ELSIF pkey_name = 'code' THEN
1383                 fs_pkey_value := rb_row.code;
1384             ELSE
1385                 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1386             END IF;
1387             INSERT INTO action.fieldset (fieldset_group,owner,owning_lib,status,classname,name,pkey_value)
1388                 VALUES (fsg_id, fs_obj.owner, fs_obj.owning_lib, 'PENDING', fs_obj.classname, fs_obj.name || ' ROLLBACK FOR ' || fs_pkey_value, fs_pkey_value);
1389
1390             fs_id := CURRVAL('action.fieldset_id_seq');
1391             sep := '';
1392             FOR cv IN
1393                 SELECT  DISTINCT col
1394                 FROM    action.fieldset_col_val
1395                 WHERE   fieldset = fieldset_id
1396             LOOP
1397                 EXECUTE 'INSERT INTO action.fieldset_col_val (fieldset, col, val) ' ||
1398                     'SELECT '|| fs_id || ', '||quote_literal(cv.col)||', '||cv.col||' FROM '||table_name||' WHERE '||pkey_name||' = '||fs_pkey_value;
1399             END LOOP;
1400         END LOOP;
1401     END IF;
1402
1403     statement := 'UPDATE ' || table_name || ' SET';
1404
1405     sep := '';
1406     FOR cv IN
1407         SELECT  col,
1408                 val
1409         FROM    action.fieldset_col_val
1410         WHERE   fieldset = fieldset_id
1411     LOOP
1412         statement := statement || sep || ' ' || cv.col
1413                      || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1414         sep := ',';
1415     END LOOP;
1416
1417     IF sep = '' THEN
1418         RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1419     END IF;
1420     statement := statement || ' ' || where_clause;
1421
1422     --
1423     -- Execute the update
1424     --
1425     BEGIN
1426         EXECUTE statement;
1427         GET DIAGNOSTICS update_count = ROW_COUNT;
1428
1429         IF update_count = 0 THEN
1430             RAISE data_exception;
1431         END IF;
1432
1433         IF fsg_id IS NOT NULL THEN
1434             UPDATE action.fieldset_group SET rollback_group = fsg_id WHERE id = fs_group.id;
1435         END IF;
1436
1437         IF fs_group.id IS NOT NULL THEN
1438             UPDATE action.fieldset_group SET complete_time = now() WHERE id = fs_group.id;
1439         END IF;
1440
1441         UPDATE action.fieldset SET status = 'APPLIED', applied_time = now() WHERE id = fieldset_id;
1442
1443     EXCEPTION WHEN data_exception THEN
1444         msg := 'No eligible rows found for fieldset ' || fieldset_id;
1445         UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1446         RETURN msg;
1447
1448     END;
1449
1450     RETURN msg;
1451
1452 EXCEPTION WHEN OTHERS THEN
1453     msg := 'Unable to apply fieldset ' || fieldset_id || ': ' || sqlerrm;
1454     UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1455     RETURN msg;
1456
1457 END;
1458 $$ LANGUAGE plpgsql;
1459
1460 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
1461 Applies a specified fieldset, using a supplied table name and primary
1462 key name.  The query parameter should be non-null only for
1463 query-based fieldsets.
1464
1465 Returns NULL if successful, or an error message if not.
1466 $$;
1467
1468 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
1469     ahr_id INT,
1470     acp_id BIGINT,
1471     copy_context_ou INT DEFAULT NULL
1472     -- TODO maybe? hold_context_ou INT DEFAULT NULL.  This would optionally
1473     -- support an "ahprox" measurement: adjust prox between copy circ lib and
1474     -- hold request lib, but I'm unsure whether to use this theoretical
1475     -- argument only in the baseline calculation or later in the other
1476     -- queries in this function.
1477 ) RETURNS NUMERIC AS $f$
1478 DECLARE
1479     aoupa           actor.org_unit_proximity_adjustment%ROWTYPE;
1480     ahr             action.hold_request%ROWTYPE;
1481     acp             asset.copy%ROWTYPE;
1482     acn             asset.call_number%ROWTYPE;
1483     acl             asset.copy_location%ROWTYPE;
1484     baseline_prox   NUMERIC;
1485
1486     icl_list        INT[];
1487     iol_list        INT[];
1488     isl_list        INT[];
1489     hpl_list        INT[];
1490     hrl_list        INT[];
1491
1492 BEGIN
1493
1494     SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
1495     SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
1496     SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
1497     SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
1498
1499     IF copy_context_ou IS NULL THEN
1500         copy_context_ou := acp.circ_lib;
1501     END IF;
1502
1503     -- First, gather the baseline proximity of "here" to pickup lib
1504     SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
1505
1506     -- Find any absolute adjustments, and set the baseline prox to that
1507     SELECT  adj.* INTO aoupa
1508       FROM  actor.org_unit_proximity_adjustment adj
1509             LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1510             LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1511             LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acl_ol.id = adj.copy_location)
1512             LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1513             LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1514       WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1515             (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1516             (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1517             (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1518             (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1519             (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1520             absolute_adjustment AND
1521             COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1522       ORDER BY
1523             COALESCE(acp_cl.distance,999)
1524                 + COALESCE(acn_ol.distance,999)
1525                 + COALESCE(acl_ol.distance,999)
1526                 + COALESCE(ahr_pl.distance,999)
1527                 + COALESCE(ahr_rl.distance,999),
1528             adj.pos
1529       LIMIT 1;
1530
1531     IF FOUND THEN
1532         baseline_prox := aoupa.prox_adjustment;
1533     END IF;
1534
1535     -- Now find any relative adjustments, and change the baseline prox based on them
1536     FOR aoupa IN
1537         SELECT  adj.*
1538           FROM  actor.org_unit_proximity_adjustment adj
1539                 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1540                 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1541                 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1542                 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1543                 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1544           WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1545                 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1546                 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1547                 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1548                 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1549                 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1550                 NOT absolute_adjustment AND
1551                 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1552     LOOP
1553         baseline_prox := baseline_prox + aoupa.prox_adjustment;
1554     END LOOP;
1555
1556     RETURN baseline_prox;
1557 END;
1558 $f$ LANGUAGE PLPGSQL;
1559
1560 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$
1561 BEGIN
1562     NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy);
1563     RETURN NEW;
1564 END;
1565 $f$ LANGUAGE PLPGSQL;
1566
1567 CREATE TRIGGER hold_copy_proximity_update_tgr BEFORE INSERT OR UPDATE ON action.hold_copy_map FOR EACH ROW EXECUTE PROCEDURE action.hold_copy_calculated_proximity_update ();
1568
1569 CREATE TABLE action.usr_circ_history (
1570     id           BIGSERIAL PRIMARY KEY,
1571     usr          INTEGER NOT NULL REFERENCES actor.usr(id)
1572                  DEFERRABLE INITIALLY DEFERRED,
1573     xact_start   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1574     target_copy  BIGINT NOT NULL, -- asset.copy.id / serial.unit.id
1575     due_date     TIMESTAMP WITH TIME ZONE NOT NULL,
1576     checkin_time TIMESTAMP WITH TIME ZONE,
1577     source_circ  BIGINT REFERENCES action.circulation(id)
1578                  ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
1579 );
1580
1581 CREATE INDEX action_usr_circ_history_usr_idx ON action.usr_circ_history ( usr );
1582 CREATE INDEX action_usr_circ_history_source_circ_idx ON action.usr_circ_history ( source_circ );
1583
1584 CREATE TRIGGER action_usr_circ_history_target_copy_trig
1585     AFTER INSERT OR UPDATE ON action.usr_circ_history
1586     FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
1587
1588 CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history()
1589     RETURNS TRIGGER AS $FUNK$
1590 DECLARE
1591     cur_circ  BIGINT;
1592     first_circ BIGINT;
1593 BEGIN
1594
1595     -- Any retention value signifies history is enabled.
1596     -- This assumes that clearing these values via external
1597     -- process deletes the action.usr_circ_history rows.
1598     -- TODO: replace these settings w/ a single bool setting?
1599     PERFORM 1 FROM actor.usr_setting
1600         WHERE usr = NEW.usr AND value IS NOT NULL AND name IN (
1601             'history.circ.retention_age',
1602             'history.circ.retention_start'
1603         );
1604
1605     IF NOT FOUND THEN
1606         RETURN NEW;
1607     END IF;
1608
1609     IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN
1610         -- Starting a new circulation.  Insert the history row.
1611         INSERT INTO action.usr_circ_history
1612             (usr, xact_start, target_copy, due_date, source_circ)
1613         VALUES (
1614             NEW.usr,
1615             NEW.xact_start,
1616             NEW.target_copy,
1617             NEW.due_date,
1618             NEW.id
1619         );
1620
1621         RETURN NEW;
1622     END IF;
1623
1624     -- find the first and last circs in the circ chain
1625     -- for the currently modified circ.
1626     FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP
1627         IF first_circ IS NULL THEN
1628             first_circ := cur_circ;
1629             CONTINUE;
1630         END IF;
1631         -- Allow the loop to continue so that at as the loop
1632         -- completes cur_circ points to the final circulation.
1633     END LOOP;
1634
1635     IF NEW.id <> cur_circ THEN
1636         -- Modifying an intermediate circ.  Ignore it.
1637         RETURN NEW;
1638     END IF;
1639
1640     -- Update the due_date/checkin_time on the history row if the current
1641     -- circ is the last circ in the chain and an update is warranted.
1642
1643     UPDATE action.usr_circ_history
1644         SET
1645             due_date = NEW.due_date,
1646             checkin_time = NEW.checkin_time
1647         WHERE
1648             source_circ = first_circ
1649             AND (
1650                 due_date <> NEW.due_date OR (
1651                     (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR
1652                     (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR
1653                     (checkin_time <> NEW.checkin_time)
1654                 )
1655             );
1656     RETURN NEW;
1657 END;
1658 $FUNK$ LANGUAGE PLPGSQL;
1659
1660 CREATE TRIGGER maintain_usr_circ_history_tgr
1661     AFTER INSERT OR UPDATE ON action.circulation
1662     FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history();
1663
1664 CREATE OR REPLACE VIEW action.all_circulation_combined_types AS
1665  SELECT acirc.id AS id,
1666     acirc.xact_start,
1667     acirc.circ_lib,
1668     acirc.circ_staff,
1669     acirc.create_time,
1670     ac_acirc.circ_modifier AS item_type,
1671     'regular_circ'::text AS circ_type
1672    FROM action.circulation acirc,
1673     asset.copy ac_acirc
1674   WHERE acirc.target_copy = ac_acirc.id
1675 UNION ALL
1676  SELECT ancc.id::BIGINT AS id,
1677     ancc.circ_time AS xact_start,
1678     ancc.circ_lib,
1679     ancc.staff AS circ_staff,
1680     ancc.circ_time AS create_time,
1681     cnct_ancc.name AS item_type,
1682     'non-cat_circ'::text AS circ_type
1683    FROM action.non_cataloged_circulation ancc,
1684     config.non_cataloged_type cnct_ancc
1685   WHERE ancc.item_type = cnct_ancc.id
1686 UNION ALL
1687  SELECT aihu.id::BIGINT AS id,
1688     aihu.use_time AS xact_start,
1689     aihu.org_unit AS circ_lib,
1690     aihu.staff AS circ_staff,
1691     aihu.use_time AS create_time,
1692     ac_aihu.circ_modifier AS item_type,
1693     'in-house_use'::text AS circ_type
1694    FROM action.in_house_use aihu,
1695     asset.copy ac_aihu
1696   WHERE aihu.item = ac_aihu.id
1697 UNION ALL
1698  SELECT ancihu.id::BIGINT AS id,
1699     ancihu.use_time AS xact_start,
1700     ancihu.org_unit AS circ_lib,
1701     ancihu.staff AS circ_staff,
1702     ancihu.use_time AS create_time,
1703     cnct_ancihu.name AS item_type,
1704     'non-cat_circ'::text AS circ_type
1705    FROM action.non_cat_in_house_use ancihu,
1706     config.non_cataloged_type cnct_ancihu
1707   WHERE ancihu.item_type = cnct_ancihu.id
1708 UNION ALL
1709  SELECT aacirc.id AS id,
1710     aacirc.xact_start,
1711     aacirc.circ_lib,
1712     aacirc.circ_staff,
1713     aacirc.create_time,
1714     ac_aacirc.circ_modifier AS item_type,
1715     'aged_circ'::text AS circ_type
1716    FROM action.aged_circulation aacirc,
1717     asset.copy ac_aacirc
1718   WHERE aacirc.target_copy = ac_aacirc.id;
1719
1720 COMMIT;
1721