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       FROM  action.aged_circulation
 244             UNION ALL
 245     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,
 246         cp.call_number AS copy_call_number, circ.copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
 247         cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
 248         circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
 249         circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
 250         circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
 251         circ.parent_circ
 252       FROM  action.circulation circ
 253         JOIN asset.copy cp ON (circ.target_copy = cp.id)
 254         JOIN asset.call_number cn ON (cp.call_number = cn.id)
 255         JOIN actor.usr p ON (circ.usr = p.id)
 256         LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
 257         LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
 258
 259 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
 260 DECLARE
 261 found char := 'N';
 262 BEGIN
 263
 264     -- If there are any renewals for this circulation, don't archive or delete
 265     -- it yet.   We'll do so later, when we archive and delete the renewals.
 266
 267     SELECT 'Y' INTO found
 268     FROM action.circulation
 269     WHERE parent_circ = OLD.id
 270     LIMIT 1;
 271
 272     IF found = 'Y' THEN
 273         RETURN NULL;  -- don't delete
 274         END IF;
 275
 276     -- Archive a copy of the old row to action.aged_circulation
 277
 278     INSERT INTO action.aged_circulation
 279         (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
 280         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
 281         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
 282         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
 283         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
 284         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
 285       SELECT
 286         id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
 287         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
 288         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
 289         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
 290         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
 291         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
 292         FROM action.all_circulation WHERE id = OLD.id;
 293
 294     RETURN OLD;
 295 END;
 296 $$ LANGUAGE 'plpgsql';
 297
 298 CREATE TRIGGER action_circulation_aging_tgr
 299         BEFORE DELETE ON action.circulation
 300         FOR EACH ROW
 301         EXECUTE PROCEDURE action.age_circ_on_delete ();
 302
 303
 304 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
 305 BEGIN
 306
 307     -- Having deleted a renewal, we can delete the original circulation (or a previous
 308     -- renewal, if that's what parent_circ is pointing to).  That deletion will trigger
 309     -- deletion of any prior parents, etc. recursively.
 310
 311     IF OLD.parent_circ IS NOT NULL THEN
 312         DELETE FROM action.circulation
 313         WHERE id = OLD.parent_circ;
 314     END IF;
 315
 316     RETURN OLD;
 317 END;
 318 $$ LANGUAGE 'plpgsql';
 319
 320 CREATE TRIGGER age_parent_circ
 321         AFTER DELETE ON action.circulation
 322         FOR EACH ROW
 323         EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
 324
 325
 326 CREATE OR REPLACE VIEW action.open_circulation AS
 327         SELECT  *
 328           FROM  action.circulation
 329           WHERE checkin_time IS NULL
 330           ORDER BY due_date;
 331
 332
 333 CREATE OR REPLACE VIEW action.billable_circulations AS
 334         SELECT  *
 335           FROM  action.circulation
 336           WHERE xact_finish IS NULL;
 337
 338 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
 339 BEGIN
 340         IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
 341                 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
 342                         UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
 343                 END IF;
 344                 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
 345                         UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
 346                 END IF;
 347                 IF NEW.stop_fines = 'LOST' THEN
 348                         UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
 349                 END IF;
 350         END IF;
 351         RETURN NEW;
 352 END;
 353 $$ LANGUAGE 'plpgsql';
 354 CREATE TRIGGER action_circulation_stop_fines_tgr
 355         BEFORE UPDATE ON action.circulation
 356         FOR EACH ROW
 357         EXECUTE PROCEDURE action.circulation_claims_returned ();
 358
 359 CREATE TABLE action.hold_request_cancel_cause (
 360     id      SERIAL  PRIMARY KEY,
 361     label   TEXT    UNIQUE
 362 );
 363 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (1,'Untargeted expiration');
 364 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (2,'Hold Shelf expiration');
 365 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (3,'Patron via phone');
 366 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (4,'Patron in person');
 367 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (5,'Staff forced');
 368 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (6,'Patron via OPAC');
 369 SELECT SETVAL('action.hold_request_cancel_cause_id_seq', 100);
 370
 371 CREATE TABLE action.hold_request (
 372         id                      SERIAL                          PRIMARY KEY,
 373         request_time            TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 374         capture_time            TIMESTAMP WITH TIME ZONE,
 375         fulfillment_time        TIMESTAMP WITH TIME ZONE,
 376         checkin_time            TIMESTAMP WITH TIME ZONE,
 377         return_time             TIMESTAMP WITH TIME ZONE,
 378         prev_check_time         TIMESTAMP WITH TIME ZONE,
 379         expire_time             TIMESTAMP WITH TIME ZONE,
 380         cancel_time             TIMESTAMP WITH TIME ZONE,
 381         cancel_cause    INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 382         cancel_note             TEXT,
 383         target                  BIGINT                          NOT NULL, -- see hold_type
 384         current_copy            BIGINT,                         -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,  -- XXX could be an serial.unit now...
 385         fulfillment_staff       INT                             REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 386         fulfillment_lib         INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 387         request_lib             INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 388         requestor               INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 389         usr                     INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 390         selection_ou            INT                             NOT NULL,
 391         selection_depth         INT                             NOT NULL DEFAULT 0,
 392         pickup_lib              INT                             NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
 393         hold_type               TEXT                            NOT NULL, -- CHECK (hold_type IN ('M','T','V','C')),  -- XXX constraint too constraining...
 394         holdable_formats        TEXT,
 395         phone_notify            TEXT,
 396         email_notify            BOOL                            NOT NULL DEFAULT FALSE,
 397         sms_notify              TEXT,
 398         sms_carrier             INT REFERENCES config.sms_carrier (id),
 399         frozen                  BOOL                            NOT NULL DEFAULT FALSE,
 400         thaw_date               TIMESTAMP WITH TIME ZONE,
 401         shelf_time              TIMESTAMP WITH TIME ZONE,
 402     cut_in_line     BOOL,
 403         mint_condition  BOOL NOT NULL DEFAULT TRUE,
 404         shelf_expire_time TIMESTAMPTZ,
 405         current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED
 406 );
 407 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
 408     sms_notify IS NULL
 409     OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
 410 );
 411
 412
 413 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
 414 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
 415 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
 416 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
 417 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
 418 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
 419 CREATE INDEX hold_request_requestor_idx         ON action.hold_request ( requestor );
 420
 421
 422 CREATE TABLE action.hold_request_note (
 423
 424     id     BIGSERIAL PRIMARY KEY,
 425     hold   BIGINT    NOT NULL REFERENCES action.hold_request (id)
 426                               ON DELETE CASCADE
 427                               DEFERRABLE INITIALLY DEFERRED,
 428     title  TEXT      NOT NULL,
 429     body   TEXT      NOT NULL,
 430     slip   BOOL      NOT NULL DEFAULT FALSE,
 431     pub    BOOL      NOT NULL DEFAULT FALSE,
 432     staff  BOOL      NOT NULL DEFAULT FALSE  -- created by staff
 433
 434 );
 435 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
 436
 437
 438 CREATE TABLE action.hold_notification (
 439         id              SERIAL                          PRIMARY KEY,
 440         hold            INT                             NOT NULL REFERENCES action.hold_request (id)
 441                                                                         ON DELETE CASCADE
 442                                                                         DEFERRABLE INITIALLY DEFERRED,
 443         notify_staff    INT                     REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 444         notify_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 445         method          TEXT                            NOT NULL, -- email address or phone number
 446         note            TEXT
 447 );
 448 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
 449 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
 450
 451 CREATE TABLE action.hold_copy_map (
 452         id              BIGSERIAL       PRIMARY KEY,
 453         hold            INT     NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 454         target_copy     BIGINT  NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
 455         proximity       NUMERIC,
 456         CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
 457 );
 458 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
 459 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
 460
 461 CREATE TABLE action.transit_copy (
 462         id                      SERIAL                          PRIMARY KEY,
 463         source_send_time        TIMESTAMP WITH TIME ZONE,
 464         dest_recv_time          TIMESTAMP WITH TIME ZONE,
 465         target_copy             BIGINT                          NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
 466         source                  INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 467         dest                    INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 468         prev_hop                INT                             REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
 469         copy_status             INT                             NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
 470         persistant_transfer     BOOL                            NOT NULL DEFAULT FALSE,
 471         prev_dest       INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
 472 );
 473 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
 474 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
 475 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
 476
 477
 478 CREATE TABLE action.hold_transit_copy (
 479         hold    INT     REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
 480 ) INHERITS (action.transit_copy);
 481 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
 482 -- 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
 483 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
 484 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
 485 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
 486
 487
 488 CREATE TABLE action.unfulfilled_hold_list (
 489         id              BIGSERIAL                       PRIMARY KEY,
 490         current_copy    BIGINT                          NOT NULL,
 491         hold            INT                             NOT NULL,
 492         circ_lib        INT                             NOT NULL,
 493         fail_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
 494 );
 495 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
 496
 497 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
 498     SELECT  u.hold,
 499             c.circ_lib,
 500             count(*)
 501       FROM  action.unfulfilled_hold_list u
 502             JOIN asset.copy c ON (c.id = u.current_copy)
 503       GROUP BY 1,2;
 504
 505 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
 506     SELECT  hold,
 507             min(count)
 508       FROM  action.unfulfilled_hold_loops
 509       GROUP BY 1;
 510
 511 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
 512     SELECT  DISTINCT l.*
 513       FROM  action.unfulfilled_hold_loops l
 514             JOIN action.unfulfilled_hold_min_loop m USING (hold)
 515       WHERE l.count = m.min;
 516
 517 CREATE VIEW action.unfulfilled_hold_max_loop AS
 518     SELECT  hold,
 519             max(count) AS max
 520       FROM  action.unfulfilled_hold_loops
 521       GROUP BY 1;
 522
 523
 524 CREATE TABLE action.fieldset (
 525     id              SERIAL          PRIMARY KEY,
 526     owner           INT             NOT NULL REFERENCES actor.usr (id)
 527                                     DEFERRABLE INITIALLY DEFERRED,
 528         owning_lib      INT             NOT NULL REFERENCES actor.org_unit (id)
 529                                     DEFERRABLE INITIALLY DEFERRED,
 530         status          TEXT            NOT NULL
 531                                         CONSTRAINT valid_status CHECK ( status in
 532                                                                         ( 'PENDING', 'APPLIED', 'ERROR' )),
 533     creation_time   TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
 534     scheduled_time  TIMESTAMPTZ,
 535     applied_time    TIMESTAMPTZ,
 536     classname       TEXT            NOT NULL, -- an IDL class name
 537     name            TEXT            NOT NULL,
 538     stored_query    INT             REFERENCES query.stored_query (id)
 539                                     DEFERRABLE INITIALLY DEFERRED,
 540     pkey_value      TEXT,
 541         CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
 542     CONSTRAINT fieldset_one_or_the_other CHECK (
 543         (stored_query IS NOT NULL AND pkey_value IS NULL) OR
 544         (pkey_value IS NOT NULL AND stored_query IS NULL)
 545     )
 546         -- the CHECK constraint means we can update the fields for a single
 547         -- row without all the extra overhead involved in a query
 548 );
 549
 550 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
 551 CREATE INDEX action_owner_idx               ON action.fieldset( owner );
 552
 553
 554 CREATE TABLE action.fieldset_col_val (
 555     id              SERIAL  PRIMARY KEY,
 556     fieldset        INT     NOT NULL REFERENCES action.fieldset
 557                                          ON DELETE CASCADE
 558                                          DEFERRABLE INITIALLY DEFERRED,
 559     col             TEXT    NOT NULL,  -- "field" from the idl ... the column on the table
 560     val             TEXT,              -- value for the column ... NULL means, well, NULL
 561     CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
 562 );
 563
 564
 565 -- represents a circ chain summary
 566 CREATE TYPE action.circ_chain_summary AS (
 567     num_circs INTEGER,
 568     start_time TIMESTAMP WITH TIME ZONE,
 569     checkout_workstation TEXT,
 570     last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
 571     last_stop_fines TEXT,
 572     last_stop_fines_time TIMESTAMP WITH TIME ZONE,
 573     last_renewal_workstation TEXT, -- NULL if no renewals
 574     last_checkin_workstation TEXT,
 575     last_checkin_time TIMESTAMP WITH TIME ZONE,
 576     last_checkin_scan_time TIMESTAMP WITH TIME ZONE
 577 );
 578
 579
 580 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id INTEGER ) RETURNS SETOF action.circulation AS $$
 581 DECLARE
 582     tmp_circ action.circulation%ROWTYPE;
 583     circ_0 action.circulation%ROWTYPE;
 584 BEGIN
 585
 586     SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
 587
 588     IF tmp_circ IS NULL THEN
 589         RETURN NEXT tmp_circ;
 590     END IF;
 591     circ_0 := tmp_circ;
 592
 593     -- find the front of the chain
 594     WHILE TRUE LOOP
 595         SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
 596         IF tmp_circ IS NULL THEN
 597             EXIT;
 598         END IF;
 599         circ_0 := tmp_circ;
 600     END LOOP;
 601
 602     -- now send the circs to the caller, oldest to newest
 603     tmp_circ := circ_0;
 604     WHILE TRUE LOOP
 605         IF tmp_circ IS NULL THEN
 606             EXIT;
 607         END IF;
 608         RETURN NEXT tmp_circ;
 609         SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
 610     END LOOP;
 611
 612 END;
 613 $$ LANGUAGE 'plpgsql';
 614
 615 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id INTEGER ) RETURNS action.circ_chain_summary AS $$
 616
 617 DECLARE
 618
 619     -- first circ in the chain
 620     circ_0 action.circulation%ROWTYPE;
 621
 622     -- last circ in the chain
 623     circ_n action.circulation%ROWTYPE;
 624
 625     -- circ chain under construction
 626     chain action.circ_chain_summary;
 627     tmp_circ action.circulation%ROWTYPE;
 628
 629 BEGIN
 630
 631     chain.num_circs := 0;
 632     FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
 633
 634         IF chain.num_circs = 0 THEN
 635             circ_0 := tmp_circ;
 636         END IF;
 637
 638         chain.num_circs := chain.num_circs + 1;
 639         circ_n := tmp_circ;
 640     END LOOP;
 641
 642     chain.start_time := circ_0.xact_start;
 643     chain.last_stop_fines := circ_n.stop_fines;
 644     chain.last_stop_fines_time := circ_n.stop_fines_time;
 645     chain.last_checkin_time := circ_n.checkin_time;
 646     chain.last_checkin_scan_time := circ_n.checkin_scan_time;
 647     SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
 648     SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
 649
 650     IF chain.num_circs > 1 THEN
 651         chain.last_renewal_time := circ_n.xact_start;
 652         SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
 653     END IF;
 654
 655     RETURN chain;
 656
 657 END;
 658 $$ LANGUAGE 'plpgsql';
 659
 660 -- Return the list of circ chain heads in xact_start order that the user has chosen to "retain"
 661 CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$
 662 DECLARE
 663     c               action.circulation%ROWTYPE;
 664     view_age        INTERVAL;
 665     usr_view_age    actor.usr_setting%ROWTYPE;
 666     usr_view_start  actor.usr_setting%ROWTYPE;
 667 BEGIN
 668     SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age';
 669     SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start';
 670
 671     IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN
 672         -- User opted in and supplied a retention age
 673         IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
 674             view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
 675         ELSE
 676             view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
 677         END IF;
 678     ELSIF usr_view_start.value IS NOT NULL THEN
 679         -- User opted in
 680         view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
 681     ELSE
 682         -- User did not opt in
 683         RETURN;
 684     END IF;
 685
 686     FOR c IN
 687         SELECT  *
 688           FROM  action.circulation
 689           WHERE usr = usr_id
 690                 AND parent_circ IS NULL
 691                 AND xact_start > NOW() - view_age
 692           ORDER BY xact_start DESC
 693     LOOP
 694         RETURN NEXT c;
 695     END LOOP;
 696
 697     RETURN;
 698 END;
 699 $func$ LANGUAGE PLPGSQL;
 700
 701 CREATE OR REPLACE FUNCTION action.usr_visible_circ_copies( INTEGER ) RETURNS SETOF BIGINT AS $$
 702     SELECT DISTINCT(target_copy) FROM action.usr_visible_circs($1)
 703 $$ LANGUAGE SQL ROWS 10;
 704
 705 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
 706 DECLARE
 707     h               action.hold_request%ROWTYPE;
 708     view_age        INTERVAL;
 709     view_count      INT;
 710     usr_view_count  actor.usr_setting%ROWTYPE;
 711     usr_view_age    actor.usr_setting%ROWTYPE;
 712     usr_view_start  actor.usr_setting%ROWTYPE;
 713 BEGIN
 714     SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
 715     SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
 716     SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
 717
 718     FOR h IN
 719         SELECT  *
 720           FROM  action.hold_request
 721           WHERE usr = usr_id
 722                 AND fulfillment_time IS NULL
 723                 AND cancel_time IS NULL
 724           ORDER BY request_time DESC
 725     LOOP
 726         RETURN NEXT h;
 727     END LOOP;
 728
 729     IF usr_view_start.value IS NULL THEN
 730         RETURN;
 731     END IF;
 732
 733     IF usr_view_age.value IS NOT NULL THEN
 734         -- User opted in and supplied a retention age
 735         IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
 736             view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
 737         ELSE
 738             view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
 739         END IF;
 740     ELSE
 741         -- User opted in
 742         view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
 743     END IF;
 744
 745     IF usr_view_count.value IS NOT NULL THEN
 746         view_count := oils_json_to_text(usr_view_count.value)::INT;
 747     ELSE
 748         view_count := 1000;
 749     END IF;
 750
 751     -- show some fulfilled/canceled holds
 752     FOR h IN
 753         SELECT  *
 754           FROM  action.hold_request
 755           WHERE usr = usr_id
 756                 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
 757                 AND request_time > NOW() - view_age
 758           ORDER BY request_time DESC
 759           LIMIT view_count
 760     LOOP
 761         RETURN NEXT h;
 762     END LOOP;
 763
 764     RETURN;
 765 END;
 766 $func$ LANGUAGE PLPGSQL;
 767
 768 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
 769 DECLARE
 770     usr_keep_age    actor.usr_setting%ROWTYPE;
 771     usr_keep_start  actor.usr_setting%ROWTYPE;
 772     org_keep_age    INTERVAL;
 773     org_keep_count  INT;
 774
 775     keep_age        INTERVAL;
 776
 777     target_acp      RECORD;
 778     circ_chain_head action.circulation%ROWTYPE;
 779     circ_chain_tail action.circulation%ROWTYPE;
 780
 781     purge_position  INT;
 782     count_purged    INT;
 783 BEGIN
 784
 785     count_purged := 0;
 786
 787     SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
 788
 789     SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
 790     IF org_keep_count IS NULL THEN
 791         RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
 792     END IF;
 793
 794     -- First, find copies with more than keep_count non-renewal circs
 795     FOR target_acp IN
 796         SELECT  target_copy,
 797                 COUNT(*) AS total_real_circs
 798           FROM  action.circulation
 799           WHERE parent_circ IS NULL
 800                 AND xact_finish IS NOT NULL
 801           GROUP BY target_copy
 802           HAVING COUNT(*) > org_keep_count
 803     LOOP
 804         purge_position := 0;
 805         -- And, for those, select circs that are finished and older than keep_age
 806         FOR circ_chain_head IN
 807             SELECT  *
 808               FROM  action.circulation
 809               WHERE target_copy = target_acp.target_copy
 810                     AND parent_circ IS NULL
 811               ORDER BY xact_start
 812         LOOP
 813
 814             -- Stop once we've purged enough circs to hit org_keep_count
 815             EXIT WHEN target_acp.total_real_circs - purge_position <= org_keep_count;
 816
 817             SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
 818             EXIT WHEN circ_chain_tail.xact_finish IS NULL;
 819
 820             -- Now get the user settings, if any, to block purging if the user wants to keep more circs
 821             usr_keep_age.value := NULL;
 822             SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';
 823
 824             usr_keep_start.value := NULL;
 825             SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start';
 826
 827             IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
 828                 IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN
 829                     keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
 830                 ELSE
 831                     keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL;
 832                 END IF;
 833             ELSIF usr_keep_start.value IS NOT NULL THEN
 834                 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
 835             ELSE
 836                 keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTERVAL );
 837             END IF;
 838
 839             EXIT WHEN AGE(NOW(), circ_chain_tail.xact_finish) < keep_age;
 840
 841             -- We've passed the purging tests, purge the circ chain starting at the end
 842             DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
 843             WHILE circ_chain_tail.parent_circ IS NOT NULL LOOP
 844                 SELECT * INTO circ_chain_tail FROM action.circulation WHERE id = circ_chain_tail.parent_circ;
 845                 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
 846             END LOOP;
 847
 848             count_purged := count_purged + 1;
 849             purge_position := purge_position + 1;
 850
 851         END LOOP;
 852     END LOOP;
 853 END;
 854 $func$ LANGUAGE PLPGSQL;
 855
 856
 857 CREATE OR REPLACE FUNCTION action.apply_fieldset(
 858         fieldset_id IN INT,        -- id from action.fieldset
 859         table_name  IN TEXT,       -- table to be updated
 860         pkey_name   IN TEXT,       -- name of primary key column in that table
 861         query       IN TEXT        -- query constructed by qstore (for query-based
 862                                    --    fieldsets only; otherwise null
 863 )
 864 RETURNS TEXT AS $$
 865 DECLARE
 866         statement TEXT;
 867         fs_status TEXT;
 868         fs_pkey_value TEXT;
 869         fs_query TEXT;
 870         sep CHAR;
 871         status_code TEXT;
 872         msg TEXT;
 873         update_count INT;
 874         cv RECORD;
 875 BEGIN
 876         -- Sanity checks
 877         IF fieldset_id IS NULL THEN
 878                 RETURN 'Fieldset ID parameter is NULL';
 879         END IF;
 880         IF table_name IS NULL THEN
 881                 RETURN 'Table name parameter is NULL';
 882         END IF;
 883         IF pkey_name IS NULL THEN
 884                 RETURN 'Primary key name parameter is NULL';
 885         END IF;
 886         --
 887         statement := 'UPDATE ' || table_name || ' SET';
 888         --
 889         SELECT
 890                 status,
 891                 quote_literal( pkey_value )
 892         INTO
 893                 fs_status,
 894                 fs_pkey_value
 895         FROM
 896                 action.fieldset
 897         WHERE
 898                 id = fieldset_id;
 899         --
 900         IF fs_status IS NULL THEN
 901                 RETURN 'No fieldset found for id = ' || fieldset_id;
 902         ELSIF fs_status = 'APPLIED' THEN
 903                 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
 904         END IF;
 905         --
 906         sep := '';
 907         FOR cv IN
 908                 SELECT  col,
 909                                 val
 910                 FROM    action.fieldset_col_val
 911                 WHERE   fieldset = fieldset_id
 912         LOOP
 913                 statement := statement || sep || ' ' || cv.col
 914                                          || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
 915                 sep := ',';
 916         END LOOP;
 917         --
 918         IF sep = '' THEN
 919                 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
 920         END IF;
 921         --
 922         -- Add the WHERE clause.  This differs according to whether it's a
 923         -- single-row fieldset or a query-based fieldset.
 924         --
 925         IF query IS NULL        AND fs_pkey_value IS NULL THEN
 926                 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
 927         ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
 928             fs_query := rtrim( query, ';' );
 929             statement := statement || ' WHERE ' || pkey_name || ' IN ( '
 930                          || fs_query || ' );';
 931         ELSIF query IS NULL     AND fs_pkey_value IS NOT NULL THEN
 932                 statement := statement || ' WHERE ' || pkey_name || ' = '
 933                                      || fs_pkey_value || ';';
 934         ELSE  -- both are not null
 935                 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
 936         END IF;
 937         --
 938         -- Execute the update
 939         --
 940         BEGIN
 941                 EXECUTE statement;
 942                 GET DIAGNOSTICS update_count = ROW_COUNT;
 943                 --
 944                 IF UPDATE_COUNT > 0 THEN
 945                         status_code := 'APPLIED';
 946                         msg := NULL;
 947                 ELSE
 948                         status_code := 'ERROR';
 949                         msg := 'No eligible rows found for fieldset ' || fieldset_id;
 950         END IF;
 951         EXCEPTION WHEN OTHERS THEN
 952                 status_code := 'ERROR';
 953                 msg := 'Unable to apply fieldset ' || fieldset_id
 954                            || ': ' || sqlerrm;
 955         END;
 956         --
 957         -- Update fieldset status
 958         --
 959         UPDATE action.fieldset
 960         SET status       = status_code,
 961             applied_time = now()
 962         WHERE id = fieldset_id;
 963         --
 964         RETURN msg;
 965 END;
 966 $$ LANGUAGE plpgsql;
 967
 968 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
 969 Applies a specified fieldset, using a supplied table name and primary
 970 key name.  The query parameter should be non-null only for
 971 query-based fieldsets.
 972
 973 Returns NULL if successful, or an error message if not.
 974 $$;
 975
 976 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(ahr_id INT, acp_id BIGINT, context_ou INT DEFAULT NULL) RETURNS NUMERIC AS $f$
 977 DECLARE
 978     aoupa           actor.org_unit_proximity_adjustment%ROWTYPE;
 979     ahr             action.hold_request%ROWTYPE;
 980     acp             asset.copy%ROWTYPE;
 981     acn             asset.call_number%ROWTYPE;
 982     acl             asset.copy_location%ROWTYPE;
 983     baseline_prox   NUMERIC;
 984
 985     icl_list        INT[];
 986     iol_list        INT[];
 987     isl_list        INT[];
 988     hpl_list        INT[];
 989     hrl_list        INT[];
 990
 991 BEGIN
 992
 993     SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
 994     SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
 995     SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
 996     SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
 997
 998     IF context_ou IS NULL THEN
 999         context_ou := acp.circ_lib;
1000     END IF;
1001
1002     -- First, gather the baseline proximity of "here" to pickup lib
1003     SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = context_ou AND to_org = ahr.pickup_lib;
1004
1005     -- Find any absolute adjustments, and set the baseline prox to that
1006     SELECT  adj.* INTO aoupa
1007       FROM  actor.org_unit_proximity_adjustment adj
1008             LEFT JOIN actor.org_unit_ancestors_distance(context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1009             LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1010             LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1011             LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1012             LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1013       WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1014         absolute_adjustment AND
1015         COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1016       ORDER BY
1017             COALESCE(acp_cl.distance,999)
1018                 + COALESCE(acn_ol.distance,999)
1019                 + COALESCE(acl_ol.distance,999)
1020                 + COALESCE(ahr_pl.distance,999)
1021                 + COALESCE(ahr_rl.distance,999),
1022             adj.pos
1023       LIMIT 1;
1024
1025     IF FOUND THEN
1026         baseline_prox := aoupa.prox_adjustment;
1027     END IF;
1028
1029     -- Now find any relative adjustments, and change the baseline prox based on them
1030     FOR aoupa IN
1031         SELECT  adj.*
1032           FROM  actor.org_unit_proximity_adjustment adj
1033                 LEFT JOIN actor.org_unit_ancestors_distance(context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1034                 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1035                 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1036                 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1037                 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1038           WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1039             NOT absolute_adjustment AND
1040             COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1041     LOOP
1042         baseline_prox := baseline_prox + aoupa.prox_adjustment;
1043     END LOOP;
1044
1045     RETURN baseline_prox;
1046 END;
1047 $f$ LANGUAGE PLPGSQL;
1048
1049 COMMIT;