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