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 ) INHERITS (money.billable_xact);
 152 ALTER TABLE action.circulation ADD PRIMARY KEY (id);
 153 ALTER TABLE action.circulation
 154         ADD COLUMN parent_circ BIGINT
 155         REFERENCES action.circulation( id )
 156         DEFERRABLE INITIALLY DEFERRED;
 157 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
 158 CREATE INDEX circ_outstanding_idx ON action.circulation (usr) WHERE checkin_time IS NULL;
 159 CREATE INDEX circ_checkin_time ON "action".circulation (checkin_time) WHERE checkin_time IS NOT NULL;
 160 CREATE INDEX circ_circ_lib_idx ON "action".circulation (circ_lib);
 161 CREATE INDEX circ_open_date_idx ON "action".circulation (xact_start) WHERE xact_finish IS NULL;
 162 CREATE INDEX circ_all_usr_idx       ON action.circulation ( usr );
 163 CREATE INDEX circ_circ_staff_idx    ON action.circulation ( circ_staff );
 164 CREATE INDEX circ_checkin_staff_idx ON action.circulation ( checkin_staff );
 165 CREATE INDEX action_circulation_target_copy_idx ON action.circulation (target_copy);
 166 CREATE UNIQUE INDEX circ_parent_idx ON action.circulation ( parent_circ ) WHERE parent_circ IS NOT NULL;
 167 CREATE UNIQUE INDEX only_one_concurrent_checkout_per_copy ON action.circulation(target_copy) WHERE checkin_time IS NULL;
 168
 169 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');
 170
 171 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation');
 172 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
 173 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
 174
 175 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
 176 BEGIN
 177     IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 THEN
 178         NEW.due_date = (NEW.due_date::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL)::TIMESTAMPTZ;
 179     END IF;
 180
 181     RETURN NEW;
 182 END;
 183 $$ LANGUAGE PLPGSQL;
 184
 185 CREATE TRIGGER push_due_date_tgr BEFORE INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.push_circ_due_time();
 186
 187 CREATE OR REPLACE FUNCTION action.fill_circ_copy_location () RETURNS TRIGGER AS $$
 188 BEGIN
 189     SELECT INTO NEW.copy_location location FROM asset.copy WHERE id = NEW.target_copy;
 190     RETURN NEW;
 191 END;
 192 $$ LANGUAGE PLPGSQL;
 193
 194 CREATE TRIGGER fill_circ_copy_location_tgr BEFORE INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.fill_circ_copy_location();
 195
 196 CREATE OR REPLACE FUNCTION action.archive_stat_cats () RETURNS TRIGGER AS $$
 197 BEGIN
 198     INSERT INTO action.archive_actor_stat_cat(xact, stat_cat, value)
 199         SELECT NEW.id, asceum.stat_cat, asceum.stat_cat_entry
 200         FROM actor.stat_cat_entry_usr_map asceum
 201              JOIN actor.stat_cat sc ON asceum.stat_cat = sc.id
 202         WHERE NEW.usr = asceum.target_usr AND sc.checkout_archive;
 203     INSERT INTO action.archive_asset_stat_cat(xact, stat_cat, value)
 204         SELECT NEW.id, ascecm.stat_cat, asce.value
 205         FROM asset.stat_cat_entry_copy_map ascecm
 206              JOIN asset.stat_cat sc ON ascecm.stat_cat = sc.id
 207              JOIN asset.stat_cat_entry asce ON ascecm.stat_cat_entry = asce.id
 208         WHERE NEW.target_copy = ascecm.owning_copy AND sc.checkout_archive;
 209     RETURN NULL;
 210 END;
 211 $$ LANGUAGE PLPGSQL;
 212
 213 CREATE TRIGGER archive_stat_cats_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.archive_stat_cats();
 214
 215 CREATE TABLE action.aged_circulation (
 216         usr_post_code           TEXT,
 217         usr_home_ou             INT     NOT NULL,
 218         usr_profile             INT     NOT NULL,
 219         usr_birth_year          INT,
 220         copy_call_number        INT     NOT NULL,
 221         copy_owning_lib         INT     NOT NULL,
 222         copy_circ_lib           INT     NOT NULL,
 223         copy_bib_record         BIGINT  NOT NULL,
 224         LIKE action.circulation
 225
 226 );
 227 ALTER TABLE action.aged_circulation ADD PRIMARY KEY (id);
 228 ALTER TABLE action.aged_circulation DROP COLUMN usr;
 229 CREATE INDEX aged_circ_circ_lib_idx ON "action".aged_circulation (circ_lib);
 230 CREATE INDEX aged_circ_start_idx ON "action".aged_circulation (xact_start);
 231 CREATE INDEX aged_circ_copy_circ_lib_idx ON "action".aged_circulation (copy_circ_lib);
 232 CREATE INDEX aged_circ_copy_owning_lib_idx ON "action".aged_circulation (copy_owning_lib);
 233 CREATE INDEX aged_circ_copy_location_idx ON "action".aged_circulation (copy_location);
 234 CREATE INDEX action_aged_circulation_target_copy_idx ON action.aged_circulation (target_copy);
 235
 236 CREATE OR REPLACE VIEW action.all_circulation AS
 237     SELECT  id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
 238         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
 239         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
 240         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
 241         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
 242         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
 243         NULL AS usr
 244       FROM  action.aged_circulation
 245             UNION ALL
 246     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,
 247         cp.call_number AS copy_call_number, circ.copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
 248         cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
 249         circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
 250         circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
 251         circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
 252         circ.parent_circ, circ.usr
 253       FROM  action.circulation circ
 254         JOIN asset.copy cp ON (circ.target_copy = cp.id)
 255         JOIN asset.call_number cn ON (cp.call_number = cn.id)
 256         JOIN actor.usr p ON (circ.usr = p.id)
 257         LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
 258         LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
 259
 260 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
 261 DECLARE
 262 found char := 'N';
 263 BEGIN
 264
 265     -- If there are any renewals for this circulation, don't archive or delete
 266     -- it yet.   We'll do so later, when we archive and delete the renewals.
 267
 268     SELECT 'Y' INTO found
 269     FROM action.circulation
 270     WHERE parent_circ = OLD.id
 271     LIMIT 1;
 272
 273     IF found = 'Y' THEN
 274         RETURN NULL;  -- don't delete
 275         END IF;
 276
 277     -- Archive a copy of the old row to action.aged_circulation
 278
 279     INSERT INTO action.aged_circulation
 280         (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
 281         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
 282         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
 283         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
 284         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
 285         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
 286       SELECT
 287         id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
 288         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
 289         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
 290         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
 291         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
 292         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
 293         FROM action.all_circulation WHERE id = OLD.id;
 294
 295     RETURN OLD;
 296 END;
 297 $$ LANGUAGE 'plpgsql';
 298
 299 CREATE TRIGGER action_circulation_aging_tgr
 300         BEFORE DELETE ON action.circulation
 301         FOR EACH ROW
 302         EXECUTE PROCEDURE action.age_circ_on_delete ();
 303
 304
 305 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
 306 BEGIN
 307
 308     -- Having deleted a renewal, we can delete the original circulation (or a previous
 309     -- renewal, if that's what parent_circ is pointing to).  That deletion will trigger
 310     -- deletion of any prior parents, etc. recursively.
 311
 312     IF OLD.parent_circ IS NOT NULL THEN
 313         DELETE FROM action.circulation
 314         WHERE id = OLD.parent_circ;
 315     END IF;
 316
 317     RETURN OLD;
 318 END;
 319 $$ LANGUAGE 'plpgsql';
 320
 321 CREATE TRIGGER age_parent_circ
 322         AFTER DELETE ON action.circulation
 323         FOR EACH ROW
 324         EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
 325
 326
 327 CREATE OR REPLACE VIEW action.open_circulation AS
 328         SELECT  *
 329           FROM  action.circulation
 330           WHERE checkin_time IS NULL
 331           ORDER BY due_date;
 332
 333
 334 CREATE OR REPLACE VIEW action.billable_circulations AS
 335         SELECT  *
 336           FROM  action.circulation
 337           WHERE xact_finish IS NULL;
 338
 339 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
 340 BEGIN
 341         IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
 342                 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
 343                         UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
 344                 END IF;
 345                 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
 346                         UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
 347                 END IF;
 348                 IF NEW.stop_fines = 'LOST' THEN
 349                         UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
 350                 END IF;
 351         END IF;
 352         RETURN NEW;
 353 END;
 354 $$ LANGUAGE 'plpgsql';
 355 CREATE TRIGGER action_circulation_stop_fines_tgr
 356         BEFORE UPDATE ON action.circulation
 357         FOR EACH ROW
 358         EXECUTE PROCEDURE action.circulation_claims_returned ();
 359
 360 CREATE TABLE action.hold_request_cancel_cause (
 361     id      SERIAL  PRIMARY KEY,
 362     label   TEXT    UNIQUE
 363 );
 364
 365 CREATE TABLE action.hold_request (
 366         id                      SERIAL                          PRIMARY KEY,
 367         request_time            TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 368         capture_time            TIMESTAMP WITH TIME ZONE,
 369         fulfillment_time        TIMESTAMP WITH TIME ZONE,
 370         checkin_time            TIMESTAMP WITH TIME ZONE,
 371         return_time             TIMESTAMP WITH TIME ZONE,
 372         prev_check_time         TIMESTAMP WITH TIME ZONE,
 373         expire_time             TIMESTAMP WITH TIME ZONE,
 374         cancel_time             TIMESTAMP WITH TIME ZONE,
 375         cancel_cause    INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 376         cancel_note             TEXT,
 377         target                  BIGINT                          NOT NULL, -- see hold_type
 378         current_copy            BIGINT,                         -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,  -- XXX could be an serial.unit now...
 379         fulfillment_staff       INT                             REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 380         fulfillment_lib         INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 381         request_lib             INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 382         requestor               INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 383         usr                     INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 384         selection_ou            INT                             NOT NULL,
 385         selection_depth         INT                             NOT NULL DEFAULT 0,
 386         pickup_lib              INT                             NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
 387         hold_type               TEXT                            NOT NULL, -- CHECK (hold_type IN ('M','T','V','C')),  -- XXX constraint too constraining...
 388         holdable_formats        TEXT,
 389         phone_notify            TEXT,
 390         email_notify            BOOL                            NOT NULL DEFAULT FALSE,
 391         sms_notify              TEXT,
 392         sms_carrier             INT REFERENCES config.sms_carrier (id),
 393         frozen                  BOOL                            NOT NULL DEFAULT FALSE,
 394         thaw_date               TIMESTAMP WITH TIME ZONE,
 395         shelf_time              TIMESTAMP WITH TIME ZONE,
 396     cut_in_line     BOOL,
 397         mint_condition  BOOL NOT NULL DEFAULT TRUE,
 398         shelf_expire_time TIMESTAMPTZ,
 399         current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
 400     behind_desk BOOLEAN NOT NULL DEFAULT FALSE
 401 );
 402 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
 403     sms_notify IS NULL
 404     OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
 405 );
 406
 407
 408 CREATE OR REPLACE FUNCTION action.hold_request_clear_map () RETURNS TRIGGER AS $$
 409 BEGIN
 410   DELETE FROM action.hold_copy_map WHERE hold = NEW.id;
 411   RETURN NEW;
 412 END;
 413 $$ LANGUAGE PLPGSQL;
 414
 415 CREATE TRIGGER hold_request_clear_map_tgr
 416     AFTER UPDATE ON action.hold_request
 417     FOR EACH ROW
 418     WHEN (
 419         (NEW.cancel_time IS NOT NULL AND OLD.cancel_time IS NULL)
 420         OR (NEW.fulfillment_time IS NOT NULL AND OLD.fulfillment_time IS NULL)
 421     )
 422     EXECUTE PROCEDURE action.hold_request_clear_map();
 423
 424 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
 425 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
 426 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
 427 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
 428 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
 429 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
 430 CREATE INDEX hold_request_requestor_idx         ON action.hold_request ( requestor );
 431 CREATE INDEX hold_request_open_idx ON action.hold_request (id) WHERE cancel_time IS NULL AND fulfillment_time IS NULL;
 432 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;
 433 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;
 434 CREATE INDEX hold_request_copy_capture_time_idx ON action.hold_request (current_copy,capture_time);
 435 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);
 436 CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
 437 CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);
 438
 439
 440 CREATE TABLE action.hold_request_note (
 441
 442     id     BIGSERIAL PRIMARY KEY,
 443     hold   BIGINT    NOT NULL REFERENCES action.hold_request (id)
 444                               ON DELETE CASCADE
 445                               DEFERRABLE INITIALLY DEFERRED,
 446     title  TEXT      NOT NULL,
 447     body   TEXT      NOT NULL,
 448     slip   BOOL      NOT NULL DEFAULT FALSE,
 449     pub    BOOL      NOT NULL DEFAULT FALSE,
 450     staff  BOOL      NOT NULL DEFAULT FALSE  -- created by staff
 451
 452 );
 453 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
 454
 455
 456 CREATE TABLE action.hold_notification (
 457         id              SERIAL                          PRIMARY KEY,
 458         hold            INT                             NOT NULL REFERENCES action.hold_request (id)
 459                                                                         ON DELETE CASCADE
 460                                                                         DEFERRABLE INITIALLY DEFERRED,
 461         notify_staff    INT                     REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 462         notify_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 463         method          TEXT                            NOT NULL, -- email address or phone number
 464         note            TEXT
 465 );
 466 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
 467 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
 468
 469 CREATE TABLE action.hold_copy_map (
 470         id              BIGSERIAL       PRIMARY KEY,
 471         hold            INT     NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 472         target_copy     BIGINT  NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
 473         proximity       NUMERIC,
 474         CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
 475 );
 476 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
 477 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
 478
 479 CREATE TABLE action.transit_copy (
 480         id                      SERIAL                          PRIMARY KEY,
 481         source_send_time        TIMESTAMP WITH TIME ZONE,
 482         dest_recv_time          TIMESTAMP WITH TIME ZONE,
 483         target_copy             BIGINT                          NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
 484         source                  INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 485         dest                    INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 486         prev_hop                INT                             REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
 487         copy_status             INT                             NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
 488         persistant_transfer     BOOL                            NOT NULL DEFAULT FALSE,
 489         prev_dest       INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 490 );
 491 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
 492 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
 493 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
 494
 495
 496 CREATE TABLE action.hold_transit_copy (
 497         hold    INT     REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
 498 ) INHERITS (action.transit_copy);
 499 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
 500 -- 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
 501 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
 502 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
 503 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
 504 CREATE INDEX hold_transit_copy_hold_idx on action.hold_transit_copy (hold);
 505
 506
 507 CREATE TABLE action.unfulfilled_hold_list (
 508         id              BIGSERIAL                       PRIMARY KEY,
 509         current_copy    BIGINT                          NOT NULL,
 510         hold            INT                             NOT NULL,
 511         circ_lib        INT                             NOT NULL,
 512         fail_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
 513 );
 514 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
 515
 516 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
 517     SELECT  u.hold,
 518             c.circ_lib,
 519             count(*)
 520       FROM  action.unfulfilled_hold_list u
 521             JOIN asset.copy c ON (c.id = u.current_copy)
 522       GROUP BY 1,2;
 523
 524 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
 525     SELECT  hold,
 526             min(count)
 527       FROM  action.unfulfilled_hold_loops
 528       GROUP BY 1;
 529
 530 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
 531     SELECT  DISTINCT l.*
 532       FROM  action.unfulfilled_hold_loops l
 533             JOIN action.unfulfilled_hold_min_loop m USING (hold)
 534       WHERE l.count = m.min;
 535
 536 CREATE VIEW action.unfulfilled_hold_max_loop AS
 537     SELECT  hold,
 538             max(count) AS max
 539       FROM  action.unfulfilled_hold_loops
 540       GROUP BY 1;
 541
 542
 543 CREATE TABLE action.aged_hold_request (
 544     usr_post_code               TEXT,
 545     usr_home_ou         INT     NOT NULL,
 546     usr_profile         INT     NOT NULL,
 547     usr_birth_year              INT,
 548     staff_placed        BOOLEAN NOT NULL,
 549     LIKE action.hold_request
 550 );
 551 ALTER TABLE action.aged_hold_request
 552       ADD PRIMARY KEY (id),
 553       DROP COLUMN usr,
 554       DROP COLUMN requestor,
 555       DROP COLUMN sms_carrier,
 556       ALTER COLUMN phone_notify TYPE BOOLEAN
 557             USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
 558       ALTER COLUMN sms_notify TYPE BOOLEAN
 559             USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
 560       ALTER COLUMN phone_notify SET NOT NULL,
 561       ALTER COLUMN sms_notify SET NOT NULL;
 562 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
 563 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
 564 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
 565 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
 566
 567 CREATE OR REPLACE VIEW action.all_hold_request AS
 568     SELECT DISTINCT
 569            COALESCE(a.post_code, b.post_code) AS usr_post_code,
 570            p.home_ou AS usr_home_ou,
 571            p.profile AS usr_profile,
 572            EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
 573            CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
 574            ahr.id,
 575            ahr.request_time,
 576            ahr.capture_time,
 577            ahr.fulfillment_time,
 578            ahr.checkin_time,
 579            ahr.return_time,
 580            ahr.prev_check_time,
 581            ahr.expire_time,
 582            ahr.cancel_time,
 583            ahr.cancel_cause,
 584            ahr.cancel_note,
 585            ahr.target,
 586            ahr.current_copy,
 587            ahr.fulfillment_staff,
 588            ahr.fulfillment_lib,
 589            ahr.request_lib,
 590            ahr.selection_ou,
 591            ahr.selection_depth,
 592            ahr.pickup_lib,
 593            ahr.hold_type,
 594            ahr.holdable_formats,
 595            CASE
 596            WHEN ahr.phone_notify IS NULL THEN FALSE
 597            WHEN ahr.phone_notify = '' THEN FALSE
 598            ELSE TRUE
 599            END AS phone_notify,
 600            ahr.email_notify,
 601            CASE
 602            WHEN ahr.sms_notify IS NULL THEN FALSE
 603            WHEN ahr.sms_notify = '' THEN FALSE
 604            ELSE TRUE
 605            END AS sms_notify,
 606            ahr.frozen,
 607            ahr.thaw_date,
 608            ahr.shelf_time,
 609            ahr.cut_in_line,
 610            ahr.mint_condition,
 611            ahr.shelf_expire_time,
 612            ahr.current_shelf_lib,
 613            ahr.behind_desk
 614     FROM action.hold_request ahr
 615          JOIN actor.usr p ON (ahr.usr = p.id)
 616          LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
 617          LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
 618     UNION ALL
 619     SELECT
 620            usr_post_code,
 621            usr_home_ou,
 622            usr_profile,
 623            usr_birth_year,
 624            staff_placed,
 625            id,
 626            request_time,
 627            capture_time,
 628            fulfillment_time,
 629            checkin_time,
 630            return_time,
 631            prev_check_time,
 632            expire_time,
 633            cancel_time,
 634            cancel_cause,
 635            cancel_note,
 636            target,
 637            current_copy,
 638            fulfillment_staff,
 639            fulfillment_lib,
 640            request_lib,
 641            selection_ou,
 642            selection_depth,
 643            pickup_lib,
 644            hold_type,
 645            holdable_formats,
 646            phone_notify,
 647            email_notify,
 648            sms_notify,
 649            frozen,
 650            thaw_date,
 651            shelf_time,
 652            cut_in_line,
 653            mint_condition,
 654            shelf_expire_time,
 655            current_shelf_lib,
 656            behind_desk
 657     FROM action.aged_hold_request;
 658
 659 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
 660 DECLARE
 661 BEGIN
 662     -- Archive a copy of the old row to action.aged_hold_request
 663
 664     INSERT INTO action.aged_hold_request
 665            (usr_post_code,
 666             usr_home_ou,
 667             usr_profile,
 668             usr_birth_year,
 669             staff_placed,
 670             id,
 671             request_time,
 672             capture_time,
 673             fulfillment_time,
 674             checkin_time,
 675             return_time,
 676             prev_check_time,
 677             expire_time,
 678             cancel_time,
 679             cancel_cause,
 680             cancel_note,
 681             target,
 682             current_copy,
 683             fulfillment_staff,
 684             fulfillment_lib,
 685             request_lib,
 686             selection_ou,
 687             selection_depth,
 688             pickup_lib,
 689             hold_type,
 690             holdable_formats,
 691             phone_notify,
 692             email_notify,
 693             sms_notify,
 694             frozen,
 695             thaw_date,
 696             shelf_time,
 697             cut_in_line,
 698             mint_condition,
 699             shelf_expire_time,
 700             current_shelf_lib,
 701             behind_desk)
 702       SELECT
 703            usr_post_code,
 704            usr_home_ou,
 705            usr_profile,
 706            usr_birth_year,
 707            staff_placed,
 708            id,
 709            request_time,
 710            capture_time,
 711            fulfillment_time,
 712            checkin_time,
 713            return_time,
 714            prev_check_time,
 715            expire_time,
 716            cancel_time,
 717            cancel_cause,
 718            cancel_note,
 719            target,
 720            current_copy,
 721            fulfillment_staff,
 722            fulfillment_lib,
 723            request_lib,
 724            selection_ou,
 725            selection_depth,
 726            pickup_lib,
 727            hold_type,
 728            holdable_formats,
 729            phone_notify,
 730            email_notify,
 731            sms_notify,
 732            frozen,
 733            thaw_date,
 734            shelf_time,
 735            cut_in_line,
 736            mint_condition,
 737            shelf_expire_time,
 738            current_shelf_lib,
 739            behind_desk
 740         FROM action.all_hold_request WHERE id = OLD.id;
 741
 742     RETURN OLD;
 743 END;
 744 $$ LANGUAGE 'plpgsql';
 745
 746 CREATE TRIGGER action_hold_request_aging_tgr
 747         BEFORE DELETE ON action.hold_request
 748         FOR EACH ROW
 749         EXECUTE PROCEDURE action.age_hold_on_delete ();
 750
 751 CREATE TABLE action.fieldset (
 752     id              SERIAL          PRIMARY KEY,
 753     owner           INT             NOT NULL REFERENCES actor.usr (id)
 754                                     DEFERRABLE INITIALLY DEFERRED,
 755         owning_lib      INT             NOT NULL REFERENCES actor.org_unit (id)
 756                                     DEFERRABLE INITIALLY DEFERRED,
 757         status          TEXT            NOT NULL
 758                                         CONSTRAINT valid_status CHECK ( status in
 759                                                                         ( 'PENDING', 'APPLIED', 'ERROR' )),
 760     creation_time   TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
 761     scheduled_time  TIMESTAMPTZ,
 762     applied_time    TIMESTAMPTZ,
 763     classname       TEXT            NOT NULL, -- an IDL class name
 764     name            TEXT            NOT NULL,
 765     stored_query    INT             REFERENCES query.stored_query (id)
 766                                     DEFERRABLE INITIALLY DEFERRED,
 767     pkey_value      TEXT,
 768         CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
 769     CONSTRAINT fieldset_one_or_the_other CHECK (
 770         (stored_query IS NOT NULL AND pkey_value IS NULL) OR
 771         (pkey_value IS NOT NULL AND stored_query IS NULL)
 772     )
 773         -- the CHECK constraint means we can update the fields for a single
 774         -- row without all the extra overhead involved in a query
 775 );
 776
 777 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
 778 CREATE INDEX action_owner_idx               ON action.fieldset( owner );
 779
 780
 781 CREATE TABLE action.fieldset_col_val (
 782     id              SERIAL  PRIMARY KEY,
 783     fieldset        INT     NOT NULL REFERENCES action.fieldset
 784                                          ON DELETE CASCADE
 785                                          DEFERRABLE INITIALLY DEFERRED,
 786     col             TEXT    NOT NULL,  -- "field" from the idl ... the column on the table
 787     val             TEXT,              -- value for the column ... NULL means, well, NULL
 788     CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
 789 );
 790
 791
 792 -- represents a circ chain summary
 793 CREATE TYPE action.circ_chain_summary AS (
 794     num_circs INTEGER,
 795     start_time TIMESTAMP WITH TIME ZONE,
 796     checkout_workstation TEXT,
 797     last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
 798     last_stop_fines TEXT,
 799     last_stop_fines_time TIMESTAMP WITH TIME ZONE,
 800     last_renewal_workstation TEXT, -- NULL if no renewals
 801     last_checkin_workstation TEXT,
 802     last_checkin_time TIMESTAMP WITH TIME ZONE,
 803     last_checkin_scan_time TIMESTAMP WITH TIME ZONE
 804 );
 805
 806
 807 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id BIGINT ) RETURNS SETOF action.circulation AS $$
 808 DECLARE
 809     tmp_circ action.circulation%ROWTYPE;
 810     circ_0 action.circulation%ROWTYPE;
 811 BEGIN
 812
 813     SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
 814
 815     IF tmp_circ IS NULL THEN
 816         RETURN NEXT tmp_circ;
 817     END IF;
 818     circ_0 := tmp_circ;
 819
 820     -- find the front of the chain
 821     WHILE TRUE LOOP
 822         SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
 823         IF tmp_circ IS NULL THEN
 824             EXIT;
 825         END IF;
 826         circ_0 := tmp_circ;
 827     END LOOP;
 828
 829     -- now send the circs to the caller, oldest to newest
 830     tmp_circ := circ_0;
 831     WHILE TRUE LOOP
 832         IF tmp_circ IS NULL THEN
 833             EXIT;
 834         END IF;
 835         RETURN NEXT tmp_circ;
 836         SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
 837     END LOOP;
 838
 839 END;
 840 $$ LANGUAGE 'plpgsql';
 841
 842 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id BIGINT ) RETURNS action.circ_chain_summary AS $$
 843
 844 DECLARE
 845
 846     -- first circ in the chain
 847     circ_0 action.circulation%ROWTYPE;
 848
 849     -- last circ in the chain
 850     circ_n action.circulation%ROWTYPE;
 851
 852     -- circ chain under construction
 853     chain action.circ_chain_summary;
 854     tmp_circ action.circulation%ROWTYPE;
 855
 856 BEGIN
 857
 858     chain.num_circs := 0;
 859     FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
 860
 861         IF chain.num_circs = 0 THEN
 862             circ_0 := tmp_circ;
 863         END IF;
 864
 865         chain.num_circs := chain.num_circs + 1;
 866         circ_n := tmp_circ;
 867     END LOOP;
 868
 869     chain.start_time := circ_0.xact_start;
 870     chain.last_stop_fines := circ_n.stop_fines;
 871     chain.last_stop_fines_time := circ_n.stop_fines_time;
 872     chain.last_checkin_time := circ_n.checkin_time;
 873     chain.last_checkin_scan_time := circ_n.checkin_scan_time;
 874     SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
 875     SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
 876
 877     IF chain.num_circs > 1 THEN
 878         chain.last_renewal_time := circ_n.xact_start;
 879         SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
 880     END IF;
 881
 882     RETURN chain;
 883
 884 END;
 885 $$ LANGUAGE 'plpgsql';
 886
 887 -- same as action.circ_chain, but returns action.all_circulation
 888 -- rows which may include aged circulations.
 889 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
 890     RETURNS SETOF action.all_circulation AS $$
 891 DECLARE
 892     tmp_circ action.all_circulation%ROWTYPE;
 893     circ_0 action.all_circulation%ROWTYPE;
 894 BEGIN
 895
 896     SELECT INTO tmp_circ * FROM action.all_circulation WHERE id = ctx_circ_id;
 897
 898     IF tmp_circ IS NULL THEN
 899         RETURN NEXT tmp_circ;
 900     END IF;
 901     circ_0 := tmp_circ;
 902
 903     -- find the front of the chain
 904     WHILE TRUE LOOP
 905         SELECT INTO tmp_circ * FROM action.all_circulation
 906             WHERE id = tmp_circ.parent_circ;
 907         IF tmp_circ IS NULL THEN
 908             EXIT;
 909         END IF;
 910         circ_0 := tmp_circ;
 911     END LOOP;
 912
 913     -- now send the circs to the caller, oldest to newest
 914     tmp_circ := circ_0;
 915     WHILE TRUE LOOP
 916         IF tmp_circ IS NULL THEN
 917             EXIT;
 918         END IF;
 919         RETURN NEXT tmp_circ;
 920         SELECT INTO tmp_circ * FROM action.all_circulation
 921             WHERE parent_circ = tmp_circ.id;
 922     END LOOP;
 923
 924 END;
 925 $$ LANGUAGE 'plpgsql';
 926
 927 -- same as action.summarize_circ_chain, but returns data collected
 928 -- from action.all_circulation, which may include aged circulations.
 929 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
 930     (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
 931
 932 DECLARE
 933
 934     -- first circ in the chain
 935     circ_0 action.all_circulation%ROWTYPE;
 936
 937     -- last circ in the chain
 938     circ_n action.all_circulation%ROWTYPE;
 939
 940     -- circ chain under construction
 941     chain action.circ_chain_summary;
 942     tmp_circ action.all_circulation%ROWTYPE;
 943
 944 BEGIN
 945
 946     chain.num_circs := 0;
 947     FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
 948
 949         IF chain.num_circs = 0 THEN
 950             circ_0 := tmp_circ;
 951         END IF;
 952
 953         chain.num_circs := chain.num_circs + 1;
 954         circ_n := tmp_circ;
 955     END LOOP;
 956
 957     chain.start_time := circ_0.xact_start;
 958     chain.last_stop_fines := circ_n.stop_fines;
 959     chain.last_stop_fines_time := circ_n.stop_fines_time;
 960     chain.last_checkin_time := circ_n.checkin_time;
 961     chain.last_checkin_scan_time := circ_n.checkin_scan_time;
 962     SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
 963     SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
 964
 965     IF chain.num_circs > 1 THEN
 966         chain.last_renewal_time := circ_n.xact_start;
 967         SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
 968     END IF;
 969
 970     RETURN chain;
 971
 972 END;
 973 $$ LANGUAGE 'plpgsql';
 974
 975
 976 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
 977 DECLARE
 978     h               action.hold_request%ROWTYPE;
 979     view_age        INTERVAL;
 980     view_count      INT;
 981     usr_view_count  actor.usr_setting%ROWTYPE;
 982     usr_view_age    actor.usr_setting%ROWTYPE;
 983     usr_view_start  actor.usr_setting%ROWTYPE;
 984 BEGIN
 985     SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
 986     SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
 987     SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
 988
 989     FOR h IN
 990         SELECT  *
 991           FROM  action.hold_request
 992           WHERE usr = usr_id
 993                 AND fulfillment_time IS NULL
 994                 AND cancel_time IS NULL
 995           ORDER BY request_time DESC
 996     LOOP
 997         RETURN NEXT h;
 998     END LOOP;
 999
1000     IF usr_view_start.value IS NULL THEN
1001         RETURN;
1002     END IF;
1003
1004     IF usr_view_age.value IS NOT NULL THEN
1005         -- User opted in and supplied a retention age
1006         IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
1007             view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1008         ELSE
1009             view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
1010         END IF;
1011     ELSE
1012         -- User opted in
1013         view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1014     END IF;
1015
1016     IF usr_view_count.value IS NOT NULL THEN
1017         view_count := oils_json_to_text(usr_view_count.value)::INT;
1018     ELSE
1019         view_count := 1000;
1020     END IF;
1021
1022     -- show some fulfilled/canceled holds
1023     FOR h IN
1024         SELECT  *
1025           FROM  action.hold_request
1026           WHERE usr = usr_id
1027                 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
1028                 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
1029           ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
1030           LIMIT view_count
1031     LOOP
1032         RETURN NEXT h;
1033     END LOOP;
1034
1035     RETURN;
1036 END;
1037 $func$ LANGUAGE PLPGSQL;
1038
1039 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
1040 DECLARE
1041     org_keep_age    INTERVAL;
1042     org_use_last    BOOL = false;
1043     org_age_is_min  BOOL = false;
1044     org_keep_count  INT;
1045
1046     keep_age        INTERVAL;
1047
1048     target_acp      RECORD;
1049     circ_chain_head action.circulation%ROWTYPE;
1050     circ_chain_tail action.circulation%ROWTYPE;
1051
1052     count_purged    INT;
1053     num_incomplete  INT;
1054
1055     last_finished   TIMESTAMP WITH TIME ZONE;
1056 BEGIN
1057
1058     count_purged := 0;
1059
1060     SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
1061
1062     SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
1063     IF org_keep_count IS NULL THEN
1064         RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
1065     END IF;
1066
1067     SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
1068     SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
1069
1070     -- First, find copies with more than keep_count non-renewal circs
1071     FOR target_acp IN
1072         SELECT  target_copy,
1073                 COUNT(*) AS total_real_circs
1074           FROM  action.circulation
1075           WHERE parent_circ IS NULL
1076                 AND xact_finish IS NOT NULL
1077           GROUP BY target_copy
1078           HAVING COUNT(*) > org_keep_count
1079     LOOP
1080         -- And, for those, select circs that are finished and older than keep_age
1081         FOR circ_chain_head IN
1082             -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
1083             -- The outer query then uses that information to skip the most recent set the library wants to keep
1084             -- End result is we don't care what order they come out in, as they are all potentials for deletion.
1085             SELECT ac.* FROM action.circulation ac JOIN (
1086               SELECT  rank() OVER (ORDER BY xact_start DESC), ac.id
1087                 FROM  action.circulation ac
1088                 WHERE ac.target_copy = target_acp.target_copy
1089                   AND ac.parent_circ IS NULL
1090                 ORDER BY ac.xact_start ) ranked USING (id)
1091                 WHERE ranked.rank > org_keep_count
1092         LOOP
1093
1094             SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
1095             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);
1096             CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
1097
1098             IF NOT org_use_last THEN
1099                 last_finished := circ_chain_tail.xact_finish;
1100             END IF;
1101
1102             keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
1103
1104             IF org_age_is_min THEN
1105                 keep_age := GREATEST( keep_age, org_keep_age );
1106             END IF;
1107
1108             CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
1109
1110             -- We've passed the purging tests, purge the circ chain starting at the end
1111             -- A trigger should auto-purge the rest of the chain.
1112             DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
1113
1114             count_purged := count_purged + 1;
1115
1116         END LOOP;
1117     END LOOP;
1118
1119     return count_purged;
1120 END;
1121 $func$ LANGUAGE PLPGSQL;
1122
1123 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
1124 DECLARE
1125   current_hold RECORD;
1126   purged_holds INT;
1127   cgf_d INTERVAL;
1128   cgf_f INTERVAL;
1129   cgf_c INTERVAL;
1130   prev_usr INT;
1131   user_start TIMESTAMPTZ;
1132   user_age INTERVAL;
1133   user_count INT;
1134 BEGIN
1135   purged_holds := 0;
1136   SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
1137   SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
1138   SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
1139   FOR current_hold IN
1140     SELECT
1141       rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
1142       cgf_cs.value::INTERVAL as cgf_cs,
1143       ahr.*
1144     FROM
1145       action.hold_request ahr
1146       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)
1147     WHERE
1148       (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
1149   LOOP
1150     IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
1151       prev_usr := current_hold.usr;
1152       SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
1153       SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
1154       SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
1155       IF user_start IS NOT NULL THEN
1156         user_age := LEAST(user_age, AGE(NOW(), user_start));
1157       END IF;
1158       IF user_count IS NULL THEN
1159         user_count := 1000; -- Assumption based on the user visible holds routine
1160       END IF;
1161     END IF;
1162     -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
1163     IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
1164       CONTINUE;
1165     END IF;
1166     IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
1167       CONTINUE;
1168     END IF;
1169
1170     -- User keep age needs combining with count. If too old AND within the count, keep!
1171     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
1172       CONTINUE;
1173     END IF;
1174
1175     -- All checks should have passed, delete!
1176     DELETE FROM action.hold_request WHERE id = current_hold.id;
1177     purged_holds := purged_holds + 1;
1178   END LOOP;
1179   RETURN purged_holds;
1180 END;
1181 $func$ LANGUAGE plpgsql;
1182
1183 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1184         fieldset_id IN INT,        -- id from action.fieldset
1185         table_name  IN TEXT,       -- table to be updated
1186         pkey_name   IN TEXT,       -- name of primary key column in that table
1187         query       IN TEXT        -- query constructed by qstore (for query-based
1188                                    --    fieldsets only; otherwise null
1189 )
1190 RETURNS TEXT AS $$
1191 DECLARE
1192         statement TEXT;
1193         fs_status TEXT;
1194         fs_pkey_value TEXT;
1195         fs_query TEXT;
1196         sep CHAR;
1197         status_code TEXT;
1198         msg TEXT;
1199         update_count INT;
1200         cv RECORD;
1201 BEGIN
1202         -- Sanity checks
1203         IF fieldset_id IS NULL THEN
1204                 RETURN 'Fieldset ID parameter is NULL';
1205         END IF;
1206         IF table_name IS NULL THEN
1207                 RETURN 'Table name parameter is NULL';
1208         END IF;
1209         IF pkey_name IS NULL THEN
1210                 RETURN 'Primary key name parameter is NULL';
1211         END IF;
1212         --
1213         statement := 'UPDATE ' || table_name || ' SET';
1214         --
1215         SELECT
1216                 status,
1217                 quote_literal( pkey_value )
1218         INTO
1219                 fs_status,
1220                 fs_pkey_value
1221         FROM
1222                 action.fieldset
1223         WHERE
1224                 id = fieldset_id;
1225         --
1226         IF fs_status IS NULL THEN
1227                 RETURN 'No fieldset found for id = ' || fieldset_id;
1228         ELSIF fs_status = 'APPLIED' THEN
1229                 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1230         END IF;
1231         --
1232         sep := '';
1233         FOR cv IN
1234                 SELECT  col,
1235                                 val
1236                 FROM    action.fieldset_col_val
1237                 WHERE   fieldset = fieldset_id
1238         LOOP
1239                 statement := statement || sep || ' ' || cv.col
1240                                          || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1241                 sep := ',';
1242         END LOOP;
1243         --
1244         IF sep = '' THEN
1245                 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1246         END IF;
1247         --
1248         -- Add the WHERE clause.  This differs according to whether it's a
1249         -- single-row fieldset or a query-based fieldset.
1250         --
1251         IF query IS NULL        AND fs_pkey_value IS NULL THEN
1252                 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1253         ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1254             fs_query := rtrim( query, ';' );
1255             statement := statement || ' WHERE ' || pkey_name || ' IN ( '
1256                          || fs_query || ' );';
1257         ELSIF query IS NULL     AND fs_pkey_value IS NOT NULL THEN
1258                 statement := statement || ' WHERE ' || pkey_name || ' = '
1259                                      || fs_pkey_value || ';';
1260         ELSE  -- both are not null
1261                 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1262         END IF;
1263         --
1264         -- Execute the update
1265         --
1266         BEGIN
1267                 EXECUTE statement;
1268                 GET DIAGNOSTICS update_count = ROW_COUNT;
1269                 --
1270                 IF UPDATE_COUNT > 0 THEN
1271                         status_code := 'APPLIED';
1272                         msg := NULL;
1273                 ELSE
1274                         status_code := 'ERROR';
1275                         msg := 'No eligible rows found for fieldset ' || fieldset_id;
1276         END IF;
1277         EXCEPTION WHEN OTHERS THEN
1278                 status_code := 'ERROR';
1279                 msg := 'Unable to apply fieldset ' || fieldset_id
1280                            || ': ' || sqlerrm;
1281         END;
1282         --
1283         -- Update fieldset status
1284         --
1285         UPDATE action.fieldset
1286         SET status       = status_code,
1287             applied_time = now()
1288         WHERE id = fieldset_id;
1289         --
1290         RETURN msg;
1291 END;
1292 $$ LANGUAGE plpgsql;
1293
1294 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
1295 Applies a specified fieldset, using a supplied table name and primary
1296 key name.  The query parameter should be non-null only for
1297 query-based fieldsets.
1298
1299 Returns NULL if successful, or an error message if not.
1300 $$;
1301
1302 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
1303     ahr_id INT,
1304     acp_id BIGINT,
1305     copy_context_ou INT DEFAULT NULL
1306     -- TODO maybe? hold_context_ou INT DEFAULT NULL.  This would optionally
1307     -- support an "ahprox" measurement: adjust prox between copy circ lib and
1308     -- hold request lib, but I'm unsure whether to use this theoretical
1309     -- argument only in the baseline calculation or later in the other
1310     -- queries in this function.
1311 ) RETURNS NUMERIC AS $f$
1312 DECLARE
1313     aoupa           actor.org_unit_proximity_adjustment%ROWTYPE;
1314     ahr             action.hold_request%ROWTYPE;
1315     acp             asset.copy%ROWTYPE;
1316     acn             asset.call_number%ROWTYPE;
1317     acl             asset.copy_location%ROWTYPE;
1318     baseline_prox   NUMERIC;
1319
1320     icl_list        INT[];
1321     iol_list        INT[];
1322     isl_list        INT[];
1323     hpl_list        INT[];
1324     hrl_list        INT[];
1325
1326 BEGIN
1327
1328     SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
1329     SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
1330     SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
1331     SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
1332
1333     IF copy_context_ou IS NULL THEN
1334         copy_context_ou := acp.circ_lib;
1335     END IF;
1336
1337     -- First, gather the baseline proximity of "here" to pickup lib
1338     SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
1339
1340     -- Find any absolute adjustments, and set the baseline prox to that
1341     SELECT  adj.* INTO aoupa
1342       FROM  actor.org_unit_proximity_adjustment adj
1343             LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1344             LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1345             LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acl_ol.id = adj.copy_location)
1346             LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1347             LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1348       WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1349             (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1350             (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1351             (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1352             (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1353             (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1354             absolute_adjustment AND
1355             COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1356       ORDER BY
1357             COALESCE(acp_cl.distance,999)
1358                 + COALESCE(acn_ol.distance,999)
1359                 + COALESCE(acl_ol.distance,999)
1360                 + COALESCE(ahr_pl.distance,999)
1361                 + COALESCE(ahr_rl.distance,999),
1362             adj.pos
1363       LIMIT 1;
1364
1365     IF FOUND THEN
1366         baseline_prox := aoupa.prox_adjustment;
1367     END IF;
1368
1369     -- Now find any relative adjustments, and change the baseline prox based on them
1370     FOR aoupa IN
1371         SELECT  adj.*
1372           FROM  actor.org_unit_proximity_adjustment adj
1373                 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1374                 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1375                 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1376                 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1377                 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1378           WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1379                 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1380                 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1381                 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1382                 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1383                 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1384                 NOT absolute_adjustment AND
1385                 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1386     LOOP
1387         baseline_prox := baseline_prox + aoupa.prox_adjustment;
1388     END LOOP;
1389
1390     RETURN baseline_prox;
1391 END;
1392 $f$ LANGUAGE PLPGSQL;
1393
1394 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$
1395 BEGIN
1396     NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy);
1397     RETURN NEW;
1398 END;
1399 $f$ LANGUAGE PLPGSQL;
1400
1401 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 ();
1402
1403 CREATE TABLE action.usr_circ_history (
1404     id           BIGSERIAL PRIMARY KEY,
1405     usr          INTEGER NOT NULL REFERENCES actor.usr(id)
1406                  DEFERRABLE INITIALLY DEFERRED,
1407     xact_start   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1408     target_copy  BIGINT NOT NULL, -- asset.copy.id / serial.unit.id
1409     due_date     TIMESTAMP WITH TIME ZONE NOT NULL,
1410     checkin_time TIMESTAMP WITH TIME ZONE,
1411     source_circ  BIGINT REFERENCES action.circulation(id)
1412                  ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
1413 );
1414
1415 CREATE TRIGGER action_usr_circ_history_target_copy_trig
1416     AFTER INSERT OR UPDATE ON action.usr_circ_history
1417     FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
1418
1419 CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history()
1420     RETURNS TRIGGER AS $FUNK$
1421 DECLARE
1422     cur_circ  BIGINT;
1423     first_circ BIGINT;
1424 BEGIN
1425
1426     -- Any retention value signifies history is enabled.
1427     -- This assumes that clearing these values via external
1428     -- process deletes the action.usr_circ_history rows.
1429     -- TODO: replace these settings w/ a single bool setting?
1430     PERFORM 1 FROM actor.usr_setting
1431         WHERE usr = NEW.usr AND value IS NOT NULL AND name IN (
1432             'history.circ.retention_age',
1433             'history.circ.retention_start'
1434         );
1435
1436     IF NOT FOUND THEN
1437         RETURN NEW;
1438     END IF;
1439
1440     IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN
1441         -- Starting a new circulation.  Insert the history row.
1442         INSERT INTO action.usr_circ_history
1443             (usr, xact_start, target_copy, due_date, source_circ)
1444         VALUES (
1445             NEW.usr,
1446             NEW.xact_start,
1447             NEW.target_copy,
1448             NEW.due_date,
1449             NEW.id
1450         );
1451
1452         RETURN NEW;
1453     END IF;
1454
1455     -- find the first and last circs in the circ chain
1456     -- for the currently modified circ.
1457     FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP
1458         IF first_circ IS NULL THEN
1459             first_circ := cur_circ;
1460             CONTINUE;
1461         END IF;
1462         -- Allow the loop to continue so that at as the loop
1463         -- completes cur_circ points to the final circulation.
1464     END LOOP;
1465
1466     IF NEW.id <> cur_circ THEN
1467         -- Modifying an intermediate circ.  Ignore it.
1468         RETURN NEW;
1469     END IF;
1470
1471     -- Update the due_date/checkin_time on the history row if the current
1472     -- circ is the last circ in the chain and an update is warranted.
1473
1474     UPDATE action.usr_circ_history
1475         SET
1476             due_date = NEW.due_date,
1477             checkin_time = NEW.checkin_time
1478         WHERE
1479             source_circ = first_circ
1480             AND (
1481                 due_date <> NEW.due_date OR (
1482                     (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR
1483                     (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR
1484                     (checkin_time <> NEW.checkin_time)
1485                 )
1486             );
1487     RETURN NEW;
1488 END;
1489 $FUNK$ LANGUAGE PLPGSQL;
1490
1491 CREATE TRIGGER maintain_usr_circ_history_tgr
1492     AFTER INSERT OR UPDATE ON action.circulation
1493     FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history();
1494
1495 COMMIT;