Open-ILS/src/sql/Pg/090.schema.action.sql

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