2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
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.
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.
18 DROP SCHEMA IF EXISTS action CASCADE;
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()
31 CREATE INDEX action_in_house_use_staff_idx ON action.in_house_use ( staff );
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()
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 );
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()
51 CREATE INDEX non_cat_in_house_use_staff_idx ON action.non_cat_in_house_use ( staff );
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,
63 description TEXT NOT NULL
65 CREATE UNIQUE INDEX asv_once_per_owner_idx ON action.survey (owner,name);
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
73 CREATE TABLE action.survey_answer (
74 id SERIAL PRIMARY KEY,
75 question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
79 CREATE SEQUENCE action.survey_response_group_id_seq;
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()
91 CREATE INDEX action_survey_response_usr_idx ON action.survey_response ( usr );
93 CREATE OR REPLACE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS '
95 NEW.answer_date := NOW();
99 CREATE TRIGGER action_survey_response_answer_date_fixup_tgr
100 BEFORE INSERT ON action.survey_response
102 EXECUTE PROCEDURE action.survey_response_answer_date_fixup ();
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,
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,
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)
145 DEFERRABLE INITIALLY DEFERRED,
146 checkin_workstation INT REFERENCES actor.workstation(id)
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;
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');
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 ();
175 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
177 proper_tz TEXT := COALESCE(
180 FROM actor.org_unit_ancestor_setting('lib.timezone',NEW.circ_lib)
183 CURRENT_SETTING('timezone')
187 IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 -- day-granular duration
188 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
189 NEW.due_date = ((NEW.due_date AT TIME ZONE proper_tz)::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL) || ' ' || proper_tz;
196 CREATE TRIGGER push_due_date_tgr BEFORE INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.push_circ_due_time();
198 CREATE OR REPLACE FUNCTION action.fill_circ_copy_location () RETURNS TRIGGER AS $$
200 SELECT INTO NEW.copy_location location FROM asset.copy WHERE id = NEW.target_copy;
205 CREATE TRIGGER fill_circ_copy_location_tgr BEFORE INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.fill_circ_copy_location();
207 CREATE OR REPLACE FUNCTION action.archive_stat_cats () RETURNS TRIGGER AS $$
209 INSERT INTO action.archive_actor_stat_cat(xact, stat_cat, value)
210 SELECT NEW.id, asceum.stat_cat, asceum.stat_cat_entry
211 FROM actor.stat_cat_entry_usr_map asceum
212 JOIN actor.stat_cat sc ON asceum.stat_cat = sc.id
213 WHERE NEW.usr = asceum.target_usr AND sc.checkout_archive;
214 INSERT INTO action.archive_asset_stat_cat(xact, stat_cat, value)
215 SELECT NEW.id, ascecm.stat_cat, asce.value
216 FROM asset.stat_cat_entry_copy_map ascecm
217 JOIN asset.stat_cat sc ON ascecm.stat_cat = sc.id
218 JOIN asset.stat_cat_entry asce ON ascecm.stat_cat_entry = asce.id
219 WHERE NEW.target_copy = ascecm.owning_copy AND sc.checkout_archive;
224 CREATE TRIGGER archive_stat_cats_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.archive_stat_cats();
226 CREATE TABLE action.aged_circulation (
228 usr_home_ou INT NOT NULL,
229 usr_profile INT NOT NULL,
231 copy_call_number INT NOT NULL,
232 copy_owning_lib INT NOT NULL,
233 copy_circ_lib INT NOT NULL,
234 copy_bib_record BIGINT NOT NULL,
235 LIKE action.circulation
238 ALTER TABLE action.aged_circulation ADD PRIMARY KEY (id);
239 ALTER TABLE action.aged_circulation DROP COLUMN usr;
240 CREATE INDEX aged_circ_circ_lib_idx ON "action".aged_circulation (circ_lib);
241 CREATE INDEX aged_circ_start_idx ON "action".aged_circulation (xact_start);
242 CREATE INDEX aged_circ_copy_circ_lib_idx ON "action".aged_circulation (copy_circ_lib);
243 CREATE INDEX aged_circ_copy_owning_lib_idx ON "action".aged_circulation (copy_owning_lib);
244 CREATE INDEX aged_circ_copy_location_idx ON "action".aged_circulation (copy_location);
245 CREATE INDEX action_aged_circulation_target_copy_idx ON action.aged_circulation (target_copy);
246 CREATE INDEX action_aged_circulation_parent_circ_idx ON action.aged_circulation (parent_circ);
248 CREATE OR REPLACE VIEW action.all_circulation AS
249 SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
250 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
251 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
252 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
253 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
254 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
256 FROM action.aged_circulation
258 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,
259 cp.call_number AS copy_call_number, circ.copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
260 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
261 circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
262 circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
263 circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
264 circ.parent_circ, circ.usr
265 FROM action.circulation circ
266 JOIN asset.copy cp ON (circ.target_copy = cp.id)
267 JOIN asset.call_number cn ON (cp.call_number = cn.id)
268 JOIN actor.usr p ON (circ.usr = p.id)
269 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
270 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
272 CREATE OR REPLACE VIEW action.all_circulation_slim AS
273 SELECT * FROM action.circulation
308 FROM action.aged_circulation
313 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
318 -- If there are any renewals for this circulation, don't archive or delete
319 -- it yet. We'll do so later, when we archive and delete the renewals.
321 SELECT 'Y' INTO found
322 FROM action.circulation
323 WHERE parent_circ = OLD.id
327 RETURN NULL; -- don't delete
330 -- Archive a copy of the old row to action.aged_circulation
332 INSERT INTO action.aged_circulation
333 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
334 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
335 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
336 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
337 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
338 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
340 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
341 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
342 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
343 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
344 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
345 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
346 FROM action.all_circulation WHERE id = OLD.id;
350 $$ LANGUAGE 'plpgsql';
352 CREATE TRIGGER action_circulation_aging_tgr
353 BEFORE DELETE ON action.circulation
355 EXECUTE PROCEDURE action.age_circ_on_delete ();
358 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
361 -- Having deleted a renewal, we can delete the original circulation (or a previous
362 -- renewal, if that's what parent_circ is pointing to). That deletion will trigger
363 -- deletion of any prior parents, etc. recursively.
365 IF OLD.parent_circ IS NOT NULL THEN
366 DELETE FROM action.circulation
367 WHERE id = OLD.parent_circ;
372 $$ LANGUAGE 'plpgsql';
374 CREATE TRIGGER age_parent_circ
375 AFTER DELETE ON action.circulation
377 EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
380 CREATE OR REPLACE VIEW action.open_circulation AS
382 FROM action.circulation
383 WHERE checkin_time IS NULL
387 CREATE OR REPLACE VIEW action.billable_circulations AS
389 FROM action.circulation
390 WHERE xact_finish IS NULL;
392 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
394 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
395 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
396 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
398 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
399 UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
401 IF NEW.stop_fines = 'LOST' THEN
402 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
407 $$ LANGUAGE 'plpgsql';
408 CREATE TRIGGER action_circulation_stop_fines_tgr
409 BEFORE UPDATE ON action.circulation
411 EXECUTE PROCEDURE action.circulation_claims_returned ();
413 CREATE TABLE action.hold_request_cancel_cause (
414 id SERIAL PRIMARY KEY,
418 CREATE TABLE action.hold_request (
419 id SERIAL PRIMARY KEY,
420 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
421 capture_time TIMESTAMP WITH TIME ZONE,
422 fulfillment_time TIMESTAMP WITH TIME ZONE,
423 checkin_time TIMESTAMP WITH TIME ZONE,
424 return_time TIMESTAMP WITH TIME ZONE,
425 prev_check_time TIMESTAMP WITH TIME ZONE,
426 expire_time TIMESTAMP WITH TIME ZONE,
427 cancel_time TIMESTAMP WITH TIME ZONE,
428 cancel_cause INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
430 target BIGINT NOT NULL, -- see hold_type
431 current_copy BIGINT, -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.unit now...
432 fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
433 fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
434 request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
435 requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
436 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
437 selection_ou INT NOT NULL,
438 selection_depth INT NOT NULL DEFAULT 0,
439 pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
440 hold_type TEXT NOT NULL, -- CHECK (hold_type IN ('M','T','V','C')), -- XXX constraint too constraining...
441 holdable_formats TEXT,
443 email_notify BOOL NOT NULL DEFAULT FALSE,
445 sms_carrier INT REFERENCES config.sms_carrier (id),
446 frozen BOOL NOT NULL DEFAULT FALSE,
447 thaw_date TIMESTAMP WITH TIME ZONE,
448 shelf_time TIMESTAMP WITH TIME ZONE,
450 mint_condition BOOL NOT NULL DEFAULT TRUE,
451 shelf_expire_time TIMESTAMPTZ,
452 current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
453 behind_desk BOOLEAN NOT NULL DEFAULT FALSE
455 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
457 OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
461 CREATE OR REPLACE FUNCTION action.hold_request_clear_map () RETURNS TRIGGER AS $$
463 DELETE FROM action.hold_copy_map WHERE hold = NEW.id;
468 CREATE TRIGGER hold_request_clear_map_tgr
469 AFTER UPDATE ON action.hold_request
472 (NEW.cancel_time IS NOT NULL AND OLD.cancel_time IS NULL)
473 OR (NEW.fulfillment_time IS NOT NULL AND OLD.fulfillment_time IS NULL)
475 EXECUTE PROCEDURE action.hold_request_clear_map();
477 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
478 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
479 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
480 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
481 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
482 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
483 CREATE INDEX hold_request_requestor_idx ON action.hold_request ( requestor );
484 CREATE INDEX hold_request_open_idx ON action.hold_request (id) WHERE cancel_time IS NULL AND fulfillment_time IS NULL;
485 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;
486 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;
487 CREATE INDEX hold_request_copy_capture_time_idx ON action.hold_request (current_copy,capture_time);
488 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);
489 CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
490 CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);
493 CREATE TABLE action.hold_request_note (
495 id BIGSERIAL PRIMARY KEY,
496 hold BIGINT NOT NULL REFERENCES action.hold_request (id)
498 DEFERRABLE INITIALLY DEFERRED,
501 slip BOOL NOT NULL DEFAULT FALSE,
502 pub BOOL NOT NULL DEFAULT FALSE,
503 staff BOOL NOT NULL DEFAULT FALSE -- created by staff
506 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
509 CREATE TABLE action.hold_notification (
510 id SERIAL PRIMARY KEY,
511 hold INT NOT NULL REFERENCES action.hold_request (id)
513 DEFERRABLE INITIALLY DEFERRED,
514 notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
515 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
516 method TEXT NOT NULL, -- email address or phone number
519 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
520 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
522 CREATE TABLE action.hold_copy_map (
523 id BIGSERIAL PRIMARY KEY,
524 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
525 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
527 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
529 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
530 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
532 CREATE OR REPLACE FUNCTION
533 action.hold_request_regen_copy_maps(
534 hold_id INTEGER, copy_ids INTEGER[]) RETURNS VOID AS $$
535 DELETE FROM action.hold_copy_map WHERE hold = $1;
536 INSERT INTO action.hold_copy_map (hold, target_copy) SELECT $1, UNNEST($2);
539 CREATE TABLE action.transit_copy (
540 id SERIAL PRIMARY KEY,
541 source_send_time TIMESTAMP WITH TIME ZONE,
542 dest_recv_time TIMESTAMP WITH TIME ZONE,
543 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
544 source INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
545 dest INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
546 prev_hop INT REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
547 copy_status INT NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
548 persistant_transfer BOOL NOT NULL DEFAULT FALSE,
549 prev_dest INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
550 cancel_time TIMESTAMP WITH TIME ZONE
552 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
553 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
554 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
557 CREATE TABLE action.hold_transit_copy (
558 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
559 ) INHERITS (action.transit_copy);
560 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
561 -- 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
562 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
563 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
564 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
565 CREATE INDEX hold_transit_copy_hold_idx on action.hold_transit_copy (hold);
568 CREATE TABLE action.unfulfilled_hold_list (
569 id BIGSERIAL PRIMARY KEY,
570 current_copy BIGINT NOT NULL,
572 circ_lib INT NOT NULL,
573 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
575 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
577 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
581 FROM action.unfulfilled_hold_list u
582 JOIN asset.copy c ON (c.id = u.current_copy)
585 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
588 FROM action.unfulfilled_hold_loops
591 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
593 FROM action.unfulfilled_hold_loops l
594 JOIN action.unfulfilled_hold_min_loop m USING (hold)
595 WHERE l.count = m.min;
597 CREATE VIEW action.unfulfilled_hold_max_loop AS
600 FROM action.unfulfilled_hold_loops
604 CREATE TABLE action.aged_hold_request (
606 usr_home_ou INT NOT NULL,
607 usr_profile INT NOT NULL,
609 staff_placed BOOLEAN NOT NULL,
610 LIKE action.hold_request
612 ALTER TABLE action.aged_hold_request
613 ADD PRIMARY KEY (id),
615 DROP COLUMN requestor,
616 DROP COLUMN sms_carrier,
617 ALTER COLUMN phone_notify TYPE BOOLEAN
618 USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
619 ALTER COLUMN sms_notify TYPE BOOLEAN
620 USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
621 ALTER COLUMN phone_notify SET NOT NULL,
622 ALTER COLUMN sms_notify SET NOT NULL;
623 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
624 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
625 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
626 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
628 CREATE OR REPLACE VIEW action.all_hold_request AS
630 COALESCE(a.post_code, b.post_code) AS usr_post_code,
631 p.home_ou AS usr_home_ou,
632 p.profile AS usr_profile,
633 EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
634 CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
638 ahr.fulfillment_time,
648 ahr.fulfillment_staff,
655 ahr.holdable_formats,
657 WHEN ahr.phone_notify IS NULL THEN FALSE
658 WHEN ahr.phone_notify = '' THEN FALSE
663 WHEN ahr.sms_notify IS NULL THEN FALSE
664 WHEN ahr.sms_notify = '' THEN FALSE
672 ahr.shelf_expire_time,
673 ahr.current_shelf_lib,
675 FROM action.hold_request ahr
676 JOIN actor.usr p ON (ahr.usr = p.id)
677 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
678 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
718 FROM action.aged_hold_request;
720 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
723 -- Archive a copy of the old row to action.aged_hold_request
725 INSERT INTO action.aged_hold_request
801 FROM action.all_hold_request WHERE id = OLD.id;
805 $$ LANGUAGE 'plpgsql';
807 CREATE TRIGGER action_hold_request_aging_tgr
808 BEFORE DELETE ON action.hold_request
810 EXECUTE PROCEDURE action.age_hold_on_delete ();
812 CREATE TABLE action.fieldset_group (
813 id SERIAL PRIMARY KEY,
815 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
816 complete_time TIMESTAMPTZ,
817 container INT, -- Points to a container of some type ...
818 container_type TEXT, -- One of 'biblio_record_entry', 'user', 'call_number', 'copy'
819 can_rollback BOOL DEFAULT TRUE,
820 rollback_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
821 rollback_time TIMESTAMPTZ,
822 creator INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
823 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
826 CREATE TABLE action.fieldset (
827 id SERIAL PRIMARY KEY,
828 fieldset_group INT REFERENCES action.fieldset_group (id)
829 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
830 owner INT NOT NULL REFERENCES actor.usr (id)
831 DEFERRABLE INITIALLY DEFERRED,
832 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
833 DEFERRABLE INITIALLY DEFERRED,
835 CONSTRAINT valid_status CHECK ( status in
836 ( 'PENDING', 'APPLIED', 'ERROR' )),
837 creation_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
838 scheduled_time TIMESTAMPTZ,
839 applied_time TIMESTAMPTZ,
840 classname TEXT NOT NULL, -- an IDL class name
843 stored_query INT REFERENCES query.stored_query (id)
844 DEFERRABLE INITIALLY DEFERRED,
846 CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
847 CONSTRAINT fieldset_one_or_the_other CHECK (
848 (stored_query IS NOT NULL AND pkey_value IS NULL) OR
849 (pkey_value IS NOT NULL AND stored_query IS NULL)
851 -- the CHECK constraint means we can update the fields for a single
852 -- row without all the extra overhead involved in a query
855 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
856 CREATE INDEX action_owner_idx ON action.fieldset( owner );
859 CREATE TABLE action.fieldset_col_val (
860 id SERIAL PRIMARY KEY,
861 fieldset INT NOT NULL REFERENCES action.fieldset
863 DEFERRABLE INITIALLY DEFERRED,
864 col TEXT NOT NULL, -- "field" from the idl ... the column on the table
865 val TEXT, -- value for the column ... NULL means, well, NULL
866 CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
870 -- represents a circ chain summary
871 CREATE TYPE action.circ_chain_summary AS (
873 start_time TIMESTAMP WITH TIME ZONE,
874 checkout_workstation TEXT,
875 last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
876 last_stop_fines TEXT,
877 last_stop_fines_time TIMESTAMP WITH TIME ZONE,
878 last_renewal_workstation TEXT, -- NULL if no renewals
879 last_checkin_workstation TEXT,
880 last_checkin_time TIMESTAMP WITH TIME ZONE,
881 last_checkin_scan_time TIMESTAMP WITH TIME ZONE
885 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id BIGINT ) RETURNS SETOF action.circulation AS $$
887 tmp_circ action.circulation%ROWTYPE;
888 circ_0 action.circulation%ROWTYPE;
891 SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
893 IF tmp_circ IS NULL THEN
894 RETURN NEXT tmp_circ;
898 -- find the front of the chain
900 SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
901 IF tmp_circ IS NULL THEN
907 -- now send the circs to the caller, oldest to newest
910 IF tmp_circ IS NULL THEN
913 RETURN NEXT tmp_circ;
914 SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
918 $$ LANGUAGE 'plpgsql';
920 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id BIGINT ) RETURNS action.circ_chain_summary AS $$
924 -- first circ in the chain
925 circ_0 action.circulation%ROWTYPE;
927 -- last circ in the chain
928 circ_n action.circulation%ROWTYPE;
930 -- circ chain under construction
931 chain action.circ_chain_summary;
932 tmp_circ action.circulation%ROWTYPE;
936 chain.num_circs := 0;
937 FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
939 IF chain.num_circs = 0 THEN
943 chain.num_circs := chain.num_circs + 1;
947 chain.start_time := circ_0.xact_start;
948 chain.last_stop_fines := circ_n.stop_fines;
949 chain.last_stop_fines_time := circ_n.stop_fines_time;
950 chain.last_checkin_time := circ_n.checkin_time;
951 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
952 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
953 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
955 IF chain.num_circs > 1 THEN
956 chain.last_renewal_time := circ_n.xact_start;
957 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
963 $$ LANGUAGE 'plpgsql';
965 -- same as action.circ_chain, but returns action.all_circulation
966 -- rows which may include aged circulations.
967 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
968 RETURNS SETOF action.all_circulation_slim AS $$
970 tmp_circ action.all_circulation_slim%ROWTYPE;
971 circ_0 action.all_circulation_slim%ROWTYPE;
974 SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
976 IF tmp_circ IS NULL THEN
977 RETURN NEXT tmp_circ;
981 -- find the front of the chain
983 SELECT INTO tmp_circ * FROM action.all_circulation_slim
984 WHERE id = tmp_circ.parent_circ;
985 IF tmp_circ IS NULL THEN
991 -- now send the circs to the caller, oldest to newest
994 IF tmp_circ IS NULL THEN
997 RETURN NEXT tmp_circ;
998 SELECT INTO tmp_circ * FROM action.all_circulation_slim
999 WHERE parent_circ = tmp_circ.id;
1003 $$ LANGUAGE 'plpgsql';
1005 -- same as action.summarize_circ_chain, but returns data collected
1006 -- from action.all_circulation, which may include aged circulations.
1007 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
1008 (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
1012 -- first circ in the chain
1013 circ_0 action.all_circulation_slim%ROWTYPE;
1015 -- last circ in the chain
1016 circ_n action.all_circulation_slim%ROWTYPE;
1018 -- circ chain under construction
1019 chain action.circ_chain_summary;
1020 tmp_circ action.all_circulation_slim%ROWTYPE;
1024 chain.num_circs := 0;
1025 FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
1027 IF chain.num_circs = 0 THEN
1031 chain.num_circs := chain.num_circs + 1;
1035 chain.start_time := circ_0.xact_start;
1036 chain.last_stop_fines := circ_n.stop_fines;
1037 chain.last_stop_fines_time := circ_n.stop_fines_time;
1038 chain.last_checkin_time := circ_n.checkin_time;
1039 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
1040 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
1041 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
1043 IF chain.num_circs > 1 THEN
1044 chain.last_renewal_time := circ_n.xact_start;
1045 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
1051 $$ LANGUAGE 'plpgsql';
1053 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
1055 h action.hold_request%ROWTYPE;
1058 usr_view_count actor.usr_setting%ROWTYPE;
1059 usr_view_age actor.usr_setting%ROWTYPE;
1060 usr_view_start actor.usr_setting%ROWTYPE;
1062 SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
1063 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
1064 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
1068 FROM action.hold_request
1070 AND fulfillment_time IS NULL
1071 AND cancel_time IS NULL
1072 ORDER BY request_time DESC
1077 IF usr_view_start.value IS NULL THEN
1081 IF usr_view_age.value IS NOT NULL THEN
1082 -- User opted in and supplied a retention age
1083 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
1084 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1086 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
1090 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1093 IF usr_view_count.value IS NOT NULL THEN
1094 view_count := oils_json_to_text(usr_view_count.value)::INT;
1099 -- show some fulfilled/canceled holds
1102 FROM action.hold_request
1104 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
1105 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
1106 ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
1114 $func$ LANGUAGE PLPGSQL;
1116 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
1118 org_keep_age INTERVAL;
1119 org_use_last BOOL = false;
1120 org_age_is_min BOOL = false;
1126 circ_chain_head action.circulation%ROWTYPE;
1127 circ_chain_tail action.circulation%ROWTYPE;
1132 last_finished TIMESTAMP WITH TIME ZONE;
1137 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
1139 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
1140 IF org_keep_count IS NULL THEN
1141 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
1144 SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
1145 SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
1147 -- First, find copies with more than keep_count non-renewal circs
1150 COUNT(*) AS total_real_circs
1151 FROM action.circulation
1152 WHERE parent_circ IS NULL
1153 AND xact_finish IS NOT NULL
1154 GROUP BY target_copy
1155 HAVING COUNT(*) > org_keep_count
1157 -- And, for those, select circs that are finished and older than keep_age
1158 FOR circ_chain_head IN
1159 -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
1160 -- The outer query then uses that information to skip the most recent set the library wants to keep
1161 -- End result is we don't care what order they come out in, as they are all potentials for deletion.
1162 SELECT ac.* FROM action.circulation ac JOIN (
1163 SELECT rank() OVER (ORDER BY xact_start DESC), ac.id
1164 FROM action.circulation ac
1165 WHERE ac.target_copy = target_acp.target_copy
1166 AND ac.parent_circ IS NULL
1167 ORDER BY ac.xact_start ) ranked USING (id)
1168 WHERE ranked.rank > org_keep_count
1171 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
1172 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);
1173 CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
1175 IF NOT org_use_last THEN
1176 last_finished := circ_chain_tail.xact_finish;
1179 keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
1181 IF org_age_is_min THEN
1182 keep_age := GREATEST( keep_age, org_keep_age );
1185 CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
1187 -- We've passed the purging tests, purge the circ chain starting at the end
1188 -- A trigger should auto-purge the rest of the chain.
1189 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
1191 count_purged := count_purged + 1;
1196 return count_purged;
1198 $func$ LANGUAGE PLPGSQL;
1200 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
1202 current_hold RECORD;
1208 user_start TIMESTAMPTZ;
1213 SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
1214 SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
1215 SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
1218 rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
1219 cgf_cs.value::INTERVAL as cgf_cs,
1222 action.hold_request ahr
1223 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)
1225 (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
1227 IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
1228 prev_usr := current_hold.usr;
1229 SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
1230 SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
1231 SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
1232 IF user_start IS NOT NULL THEN
1233 user_age := LEAST(user_age, AGE(NOW(), user_start));
1235 IF user_count IS NULL THEN
1236 user_count := 1000; -- Assumption based on the user visible holds routine
1239 -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
1240 IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
1243 IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
1247 -- User keep age needs combining with count. If too old AND within the count, keep!
1248 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
1252 -- All checks should have passed, delete!
1253 DELETE FROM action.hold_request WHERE id = current_hold.id;
1254 purged_holds := purged_holds + 1;
1256 RETURN purged_holds;
1258 $func$ LANGUAGE plpgsql;
1260 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1261 fieldset_id IN INT, -- id from action.fieldset
1262 table_name IN TEXT, -- table to be updated
1263 pkey_name IN TEXT, -- name of primary key column in that table
1264 query IN TEXT -- query constructed by qstore (for query-based
1265 -- fieldsets only; otherwise null
1281 fs_obj action.fieldset%ROWTYPE;
1282 fs_group action.fieldset_group%ROWTYPE;
1286 IF fieldset_id IS NULL THEN
1287 RETURN 'Fieldset ID parameter is NULL';
1289 IF table_name IS NULL THEN
1290 RETURN 'Table name parameter is NULL';
1292 IF pkey_name IS NULL THEN
1293 RETURN 'Primary key name parameter is NULL';
1298 quote_literal( pkey_value )
1308 -- Build the WHERE clause. This differs according to whether it's a
1309 -- single-row fieldset or a query-based fieldset.
1311 IF query IS NULL AND fs_pkey_value IS NULL THEN
1312 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1313 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1314 fs_query := rtrim( query, ';' );
1315 where_clause := 'WHERE ' || pkey_name || ' IN ( '
1316 || fs_query || ' )';
1317 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
1318 where_clause := 'WHERE ' || pkey_name || ' = ';
1319 IF pkey_name = 'id' THEN
1320 where_clause := where_clause || fs_pkey_value;
1321 ELSIF pkey_name = 'code' THEN
1322 where_clause := where_clause || quote_literal(fs_pkey_value);
1324 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1326 ELSE -- both are not null
1327 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1330 IF fs_status IS NULL THEN
1331 RETURN 'No fieldset found for id = ' || fieldset_id;
1332 ELSIF fs_status = 'APPLIED' THEN
1333 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1336 SELECT * INTO fs_obj FROM action.fieldset WHERE id = fieldset_id;
1337 SELECT * INTO fs_group FROM action.fieldset_group WHERE id = fs_obj.fieldset_group;
1339 IF fs_group.can_rollback THEN
1340 -- This is part of a non-rollback group. We need to record the current values for future rollback.
1342 INSERT INTO action.fieldset_group (can_rollback, name, creator, owning_lib, container, container_type)
1343 VALUES (FALSE, 'ROLLBACK: '|| fs_group.name, fs_group.creator, fs_group.owning_lib, fs_group.container, fs_group.container_type);
1345 fsg_id := CURRVAL('action.fieldset_group_id_seq');
1347 FOR rb_row IN EXECUTE 'SELECT * FROM ' || table_name || ' ' || where_clause LOOP
1348 IF pkey_name = 'id' THEN
1349 fs_pkey_value := rb_row.id;
1350 ELSIF pkey_name = 'code' THEN
1351 fs_pkey_value := rb_row.code;
1353 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1355 INSERT INTO action.fieldset (fieldset_group,owner,owning_lib,status,classname,name,pkey_value)
1356 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);
1358 fs_id := CURRVAL('action.fieldset_id_seq');
1362 FROM action.fieldset_col_val
1363 WHERE fieldset = fieldset_id
1365 EXECUTE 'INSERT INTO action.fieldset_col_val (fieldset, col, val) ' ||
1366 'SELECT '|| fs_id || ', '||quote_literal(cv.col)||', '||cv.col||' FROM '||table_name||' WHERE '||pkey_name||' = '||fs_pkey_value;
1371 statement := 'UPDATE ' || table_name || ' SET';
1377 FROM action.fieldset_col_val
1378 WHERE fieldset = fieldset_id
1380 statement := statement || sep || ' ' || cv.col
1381 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1386 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1388 statement := statement || ' ' || where_clause;
1391 -- Execute the update
1395 GET DIAGNOSTICS update_count = ROW_COUNT;
1397 IF update_count = 0 THEN
1398 RAISE data_exception;
1401 IF fsg_id IS NOT NULL THEN
1402 UPDATE action.fieldset_group SET rollback_group = fsg_id WHERE id = fs_group.id;
1405 IF fs_group.id IS NOT NULL THEN
1406 UPDATE action.fieldset_group SET complete_time = now() WHERE id = fs_group.id;
1409 UPDATE action.fieldset SET status = 'APPLIED', applied_time = now() WHERE id = fieldset_id;
1411 EXCEPTION WHEN data_exception THEN
1412 msg := 'No eligible rows found for fieldset ' || fieldset_id;
1413 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1420 EXCEPTION WHEN OTHERS THEN
1421 msg := 'Unable to apply fieldset ' || fieldset_id || ': ' || sqlerrm;
1422 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1426 $$ LANGUAGE plpgsql;
1428 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
1429 Applies a specified fieldset, using a supplied table name and primary
1430 key name. The query parameter should be non-null only for
1431 query-based fieldsets.
1433 Returns NULL if successful, or an error message if not.
1436 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
1439 copy_context_ou INT DEFAULT NULL
1440 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
1441 -- support an "ahprox" measurement: adjust prox between copy circ lib and
1442 -- hold request lib, but I'm unsure whether to use this theoretical
1443 -- argument only in the baseline calculation or later in the other
1444 -- queries in this function.
1445 ) RETURNS NUMERIC AS $f$
1447 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
1448 ahr action.hold_request%ROWTYPE;
1449 acp asset.copy%ROWTYPE;
1450 acn asset.call_number%ROWTYPE;
1451 acl asset.copy_location%ROWTYPE;
1452 baseline_prox NUMERIC;
1462 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
1463 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
1464 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
1465 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
1467 IF copy_context_ou IS NULL THEN
1468 copy_context_ou := acp.circ_lib;
1471 -- First, gather the baseline proximity of "here" to pickup lib
1472 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
1474 -- Find any absolute adjustments, and set the baseline prox to that
1475 SELECT adj.* INTO aoupa
1476 FROM actor.org_unit_proximity_adjustment adj
1477 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1478 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1479 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acl_ol.id = adj.copy_location)
1480 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1481 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1482 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1483 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1484 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1485 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1486 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1487 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1488 absolute_adjustment AND
1489 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1491 COALESCE(acp_cl.distance,999)
1492 + COALESCE(acn_ol.distance,999)
1493 + COALESCE(acl_ol.distance,999)
1494 + COALESCE(ahr_pl.distance,999)
1495 + COALESCE(ahr_rl.distance,999),
1500 baseline_prox := aoupa.prox_adjustment;
1503 -- Now find any relative adjustments, and change the baseline prox based on them
1506 FROM actor.org_unit_proximity_adjustment adj
1507 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1508 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1509 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1510 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1511 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1512 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1513 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1514 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1515 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1516 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1517 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1518 NOT absolute_adjustment AND
1519 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1521 baseline_prox := baseline_prox + aoupa.prox_adjustment;
1524 RETURN baseline_prox;
1526 $f$ LANGUAGE PLPGSQL;
1528 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$
1530 NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy);
1533 $f$ LANGUAGE PLPGSQL;
1535 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 ();
1537 CREATE TABLE action.usr_circ_history (
1538 id BIGSERIAL PRIMARY KEY,
1539 usr INTEGER NOT NULL REFERENCES actor.usr(id)
1540 DEFERRABLE INITIALLY DEFERRED,
1541 xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1542 target_copy BIGINT NOT NULL, -- asset.copy.id / serial.unit.id
1543 due_date TIMESTAMP WITH TIME ZONE NOT NULL,
1544 checkin_time TIMESTAMP WITH TIME ZONE,
1545 source_circ BIGINT REFERENCES action.circulation(id)
1546 ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
1549 CREATE INDEX action_usr_circ_history_usr_idx ON action.usr_circ_history ( usr );
1551 CREATE TRIGGER action_usr_circ_history_target_copy_trig
1552 AFTER INSERT OR UPDATE ON action.usr_circ_history
1553 FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
1555 CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history()
1556 RETURNS TRIGGER AS $FUNK$
1562 -- Any retention value signifies history is enabled.
1563 -- This assumes that clearing these values via external
1564 -- process deletes the action.usr_circ_history rows.
1565 -- TODO: replace these settings w/ a single bool setting?
1566 PERFORM 1 FROM actor.usr_setting
1567 WHERE usr = NEW.usr AND value IS NOT NULL AND name IN (
1568 'history.circ.retention_age',
1569 'history.circ.retention_start'
1576 IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN
1577 -- Starting a new circulation. Insert the history row.
1578 INSERT INTO action.usr_circ_history
1579 (usr, xact_start, target_copy, due_date, source_circ)
1591 -- find the first and last circs in the circ chain
1592 -- for the currently modified circ.
1593 FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP
1594 IF first_circ IS NULL THEN
1595 first_circ := cur_circ;
1598 -- Allow the loop to continue so that at as the loop
1599 -- completes cur_circ points to the final circulation.
1602 IF NEW.id <> cur_circ THEN
1603 -- Modifying an intermediate circ. Ignore it.
1607 -- Update the due_date/checkin_time on the history row if the current
1608 -- circ is the last circ in the chain and an update is warranted.
1610 UPDATE action.usr_circ_history
1612 due_date = NEW.due_date,
1613 checkin_time = NEW.checkin_time
1615 source_circ = first_circ
1617 due_date <> NEW.due_date OR (
1618 (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR
1619 (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR
1620 (checkin_time <> NEW.checkin_time)
1625 $FUNK$ LANGUAGE PLPGSQL;
1627 CREATE TRIGGER maintain_usr_circ_history_tgr
1628 AFTER INSERT OR UPDATE ON action.circulation
1629 FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history();
1631 CREATE OR REPLACE VIEW action.all_circulation_combined_types AS
1632 SELECT acirc.id AS id,
1637 ac_acirc.circ_modifier AS item_type,
1638 'regular_circ'::text AS circ_type
1639 FROM action.circulation acirc,
1641 WHERE acirc.target_copy = ac_acirc.id
1643 SELECT ancc.id::BIGINT AS id,
1644 ancc.circ_time AS xact_start,
1646 ancc.staff AS circ_staff,
1647 ancc.circ_time AS create_time,
1648 cnct_ancc.name AS item_type,
1649 'non-cat_circ'::text AS circ_type
1650 FROM action.non_cataloged_circulation ancc,
1651 config.non_cataloged_type cnct_ancc
1652 WHERE ancc.item_type = cnct_ancc.id
1654 SELECT aihu.id::BIGINT AS id,
1655 aihu.use_time AS xact_start,
1656 aihu.org_unit AS circ_lib,
1657 aihu.staff AS circ_staff,
1658 aihu.use_time AS create_time,
1659 ac_aihu.circ_modifier AS item_type,
1660 'in-house_use'::text AS circ_type
1661 FROM action.in_house_use aihu,
1663 WHERE aihu.item = ac_aihu.id
1665 SELECT ancihu.id::BIGINT AS id,
1666 ancihu.use_time AS xact_start,
1667 ancihu.org_unit AS circ_lib,
1668 ancihu.staff AS circ_staff,
1669 ancihu.use_time AS create_time,
1670 cnct_ancihu.name AS item_type,
1671 'non-cat_circ'::text AS circ_type
1672 FROM action.non_cat_in_house_use ancihu,
1673 config.non_cataloged_type cnct_ancihu
1674 WHERE ancihu.item_type = cnct_ancihu.id
1676 SELECT aacirc.id AS id,
1681 ac_aacirc.circ_modifier AS item_type,
1682 'aged_circ'::text AS circ_type
1683 FROM action.aged_circulation aacirc,
1684 asset.copy ac_aacirc
1685 WHERE aacirc.target_copy = ac_aacirc.id;