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