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