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 auto_renewal BOOLEAN NOT NULL DEFAULT FALSE,
152 auto_renewal_remaining INTEGER
153 ) INHERITS (money.billable_xact);
154 ALTER TABLE action.circulation ADD PRIMARY KEY (id);
155 ALTER TABLE action.circulation
156 ADD COLUMN parent_circ BIGINT
157 REFERENCES action.circulation( id )
158 DEFERRABLE INITIALLY DEFERRED;
159 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
160 CREATE INDEX circ_outstanding_idx ON action.circulation (usr) WHERE checkin_time IS NULL;
161 CREATE INDEX circ_checkin_time ON "action".circulation (checkin_time) WHERE checkin_time IS NOT NULL;
162 CREATE INDEX circ_circ_lib_idx ON "action".circulation (circ_lib);
163 CREATE INDEX circ_open_date_idx ON "action".circulation (xact_start) WHERE xact_finish IS NULL;
164 CREATE INDEX circ_all_usr_idx ON action.circulation ( usr );
165 CREATE INDEX circ_circ_staff_idx ON action.circulation ( circ_staff );
166 CREATE INDEX circ_checkin_staff_idx ON action.circulation ( checkin_staff );
167 CREATE INDEX action_circulation_target_copy_idx ON action.circulation (target_copy);
168 CREATE UNIQUE INDEX circ_parent_idx ON action.circulation ( parent_circ ) WHERE parent_circ IS NOT NULL;
169 CREATE UNIQUE INDEX only_one_concurrent_checkout_per_copy ON action.circulation(target_copy) WHERE checkin_time IS NULL;
171 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');
173 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation');
174 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
175 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
177 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
179 proper_tz TEXT := COALESCE(
182 FROM actor.org_unit_ancestor_setting('lib.timezone',NEW.circ_lib)
185 CURRENT_SETTING('timezone')
189 IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 -- day-granular duration
190 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
191 NEW.due_date = ((NEW.due_date AT TIME ZONE proper_tz)::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL) || ' ' || proper_tz;
198 CREATE TRIGGER push_due_date_tgr BEFORE INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.push_circ_due_time();
200 CREATE OR REPLACE FUNCTION action.fill_circ_copy_location () RETURNS TRIGGER AS $$
202 SELECT INTO NEW.copy_location location FROM asset.copy WHERE id = NEW.target_copy;
207 CREATE TRIGGER fill_circ_copy_location_tgr BEFORE INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.fill_circ_copy_location();
209 CREATE OR REPLACE FUNCTION action.archive_stat_cats () RETURNS TRIGGER AS $$
211 INSERT INTO action.archive_actor_stat_cat(xact, stat_cat, value)
212 SELECT NEW.id, asceum.stat_cat, asceum.stat_cat_entry
213 FROM actor.stat_cat_entry_usr_map asceum
214 JOIN actor.stat_cat sc ON asceum.stat_cat = sc.id
215 WHERE NEW.usr = asceum.target_usr AND sc.checkout_archive;
216 INSERT INTO action.archive_asset_stat_cat(xact, stat_cat, value)
217 SELECT NEW.id, ascecm.stat_cat, asce.value
218 FROM asset.stat_cat_entry_copy_map ascecm
219 JOIN asset.stat_cat sc ON ascecm.stat_cat = sc.id
220 JOIN asset.stat_cat_entry asce ON ascecm.stat_cat_entry = asce.id
221 WHERE NEW.target_copy = ascecm.owning_copy AND sc.checkout_archive;
226 CREATE TRIGGER archive_stat_cats_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.archive_stat_cats();
228 CREATE TABLE action.aged_circulation (
230 usr_home_ou INT NOT NULL,
231 usr_profile INT NOT NULL,
233 copy_call_number INT NOT NULL,
234 copy_owning_lib INT NOT NULL,
235 copy_circ_lib INT NOT NULL,
236 copy_bib_record BIGINT NOT NULL,
237 LIKE action.circulation
240 ALTER TABLE action.aged_circulation ADD PRIMARY KEY (id);
241 ALTER TABLE action.aged_circulation DROP COLUMN usr;
242 CREATE INDEX aged_circ_circ_lib_idx ON "action".aged_circulation (circ_lib);
243 CREATE INDEX aged_circ_start_idx ON "action".aged_circulation (xact_start);
244 CREATE INDEX aged_circ_copy_circ_lib_idx ON "action".aged_circulation (copy_circ_lib);
245 CREATE INDEX aged_circ_copy_owning_lib_idx ON "action".aged_circulation (copy_owning_lib);
246 CREATE INDEX aged_circ_copy_location_idx ON "action".aged_circulation (copy_location);
247 CREATE INDEX action_aged_circulation_target_copy_idx ON action.aged_circulation (target_copy);
248 CREATE INDEX action_aged_circulation_parent_circ_idx ON action.aged_circulation (parent_circ);
250 CREATE OR REPLACE VIEW action.all_circulation AS
251 SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
252 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
253 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
254 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
255 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
256 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
257 auto_renewal, auto_renewal_remaining,
259 FROM action.aged_circulation
261 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,
262 cp.call_number AS copy_call_number, circ.copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
263 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
264 circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
265 circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
266 circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
267 circ.parent_circ, circ.auto_renewal, circ.auto_renewal_remaining, circ.usr
268 FROM action.circulation circ
269 JOIN asset.copy cp ON (circ.target_copy = cp.id)
270 JOIN asset.call_number cn ON (cp.call_number = cn.id)
271 JOIN actor.usr p ON (circ.usr = p.id)
272 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
273 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
275 CREATE OR REPLACE VIEW action.all_circulation_slim AS
276 SELECT * FROM action.circulation
311 auto_renewal_remaining,
313 FROM action.aged_circulation
318 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
323 -- If there are any renewals for this circulation, don't archive or delete
324 -- it yet. We'll do so later, when we archive and delete the renewals.
326 SELECT 'Y' INTO found
327 FROM action.circulation
328 WHERE parent_circ = OLD.id
332 RETURN NULL; -- don't delete
335 -- Archive a copy of the old row to action.aged_circulation
337 INSERT INTO action.aged_circulation
338 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
339 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
340 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
341 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
342 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
343 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
344 auto_renewal, auto_renewal_remaining)
346 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
347 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
348 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
349 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
350 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
351 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
352 auto_renewal, auto_renewal_remaining
353 FROM action.all_circulation WHERE id = OLD.id;
355 -- Migrate billings and payments to aged tables
357 INSERT INTO money.aged_billing
358 SELECT * FROM money.billing WHERE xact = OLD.id;
360 INSERT INTO money.aged_payment
361 SELECT * FROM money.payment_view WHERE xact = OLD.id;
363 DELETE FROM money.payment WHERE xact = OLD.id;
364 DELETE FROM money.billing WHERE xact = OLD.id;
368 $$ LANGUAGE 'plpgsql';
370 CREATE TRIGGER action_circulation_aging_tgr
371 BEFORE DELETE ON action.circulation
373 EXECUTE PROCEDURE action.age_circ_on_delete ();
376 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
379 -- Having deleted a renewal, we can delete the original circulation (or a previous
380 -- renewal, if that's what parent_circ is pointing to). That deletion will trigger
381 -- deletion of any prior parents, etc. recursively.
383 IF OLD.parent_circ IS NOT NULL THEN
384 DELETE FROM action.circulation
385 WHERE id = OLD.parent_circ;
390 $$ LANGUAGE 'plpgsql';
392 CREATE TRIGGER age_parent_circ
393 AFTER DELETE ON action.circulation
395 EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
398 CREATE OR REPLACE VIEW action.open_circulation AS
400 FROM action.circulation
401 WHERE checkin_time IS NULL
405 CREATE OR REPLACE VIEW action.billable_circulations AS
407 FROM action.circulation
408 WHERE xact_finish IS NULL;
410 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
412 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
413 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
414 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
416 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
417 UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
419 IF NEW.stop_fines = 'LOST' THEN
420 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
425 $$ LANGUAGE 'plpgsql';
426 CREATE TRIGGER action_circulation_stop_fines_tgr
427 BEFORE UPDATE ON action.circulation
429 EXECUTE PROCEDURE action.circulation_claims_returned ();
431 CREATE TABLE action.hold_request_cancel_cause (
432 id SERIAL PRIMARY KEY,
436 CREATE TABLE action.hold_request (
437 id SERIAL PRIMARY KEY,
438 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
439 capture_time TIMESTAMP WITH TIME ZONE,
440 fulfillment_time TIMESTAMP WITH TIME ZONE,
441 checkin_time TIMESTAMP WITH TIME ZONE,
442 return_time TIMESTAMP WITH TIME ZONE,
443 prev_check_time TIMESTAMP WITH TIME ZONE,
444 expire_time TIMESTAMP WITH TIME ZONE,
445 cancel_time TIMESTAMP WITH TIME ZONE,
446 cancel_cause INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
448 target BIGINT NOT NULL, -- see hold_type
449 current_copy BIGINT, -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.unit now...
450 fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
451 fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
452 request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
453 requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
454 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
455 selection_ou INT NOT NULL,
456 selection_depth INT NOT NULL DEFAULT 0,
457 pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
458 hold_type TEXT REFERENCES config.hold_type (hold_type) DEFERRABLE INITIALLY DEFERRED,
459 holdable_formats TEXT,
461 email_notify BOOL NOT NULL DEFAULT FALSE,
463 sms_carrier INT REFERENCES config.sms_carrier (id),
464 frozen BOOL NOT NULL DEFAULT FALSE,
465 thaw_date TIMESTAMP WITH TIME ZONE,
466 shelf_time TIMESTAMP WITH TIME ZONE,
468 mint_condition BOOL NOT NULL DEFAULT TRUE,
469 shelf_expire_time TIMESTAMPTZ,
470 current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
471 behind_desk BOOLEAN NOT NULL DEFAULT FALSE
473 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
475 OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
479 CREATE OR REPLACE FUNCTION action.hold_request_clear_map () RETURNS TRIGGER AS $$
481 DELETE FROM action.hold_copy_map WHERE hold = NEW.id;
486 CREATE TRIGGER hold_request_clear_map_tgr
487 AFTER UPDATE ON action.hold_request
490 (NEW.cancel_time IS NOT NULL AND OLD.cancel_time IS NULL)
491 OR (NEW.fulfillment_time IS NOT NULL AND OLD.fulfillment_time IS NULL)
493 EXECUTE PROCEDURE action.hold_request_clear_map();
495 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
496 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
497 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
498 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
499 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
500 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
501 CREATE INDEX hold_request_requestor_idx ON action.hold_request ( requestor );
502 CREATE INDEX hold_request_open_idx ON action.hold_request (id) WHERE cancel_time IS NULL AND fulfillment_time IS NULL;
503 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;
504 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;
505 CREATE INDEX hold_request_copy_capture_time_idx ON action.hold_request (current_copy,capture_time);
506 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);
507 CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
508 CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);
511 CREATE TABLE action.hold_request_note (
513 id BIGSERIAL PRIMARY KEY,
514 hold BIGINT NOT NULL REFERENCES action.hold_request (id)
516 DEFERRABLE INITIALLY DEFERRED,
519 slip BOOL NOT NULL DEFAULT FALSE,
520 pub BOOL NOT NULL DEFAULT FALSE,
521 staff BOOL NOT NULL DEFAULT FALSE -- created by staff
524 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
527 CREATE TABLE action.hold_notification (
528 id SERIAL PRIMARY KEY,
529 hold INT NOT NULL REFERENCES action.hold_request (id)
531 DEFERRABLE INITIALLY DEFERRED,
532 notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
533 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
534 method TEXT NOT NULL, -- email address or phone number
537 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
538 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
540 CREATE TABLE action.hold_copy_map (
541 id BIGSERIAL PRIMARY KEY,
542 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
543 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
545 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
547 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
548 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
550 CREATE OR REPLACE FUNCTION
551 action.hold_request_regen_copy_maps(
552 hold_id INTEGER, copy_ids INTEGER[]) RETURNS VOID AS $$
553 DELETE FROM action.hold_copy_map WHERE hold = $1;
554 INSERT INTO action.hold_copy_map (hold, target_copy) SELECT $1, UNNEST($2);
557 CREATE TABLE action.transit_copy (
558 id SERIAL PRIMARY KEY,
559 source_send_time TIMESTAMP WITH TIME ZONE,
560 dest_recv_time TIMESTAMP WITH TIME ZONE,
561 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
562 source INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
563 dest INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
564 prev_hop INT REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
565 copy_status INT NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
566 persistant_transfer BOOL NOT NULL DEFAULT FALSE,
567 prev_dest INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
568 cancel_time TIMESTAMP WITH TIME ZONE
570 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
571 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
572 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
573 CREATE INDEX active_transit_for_copy ON action.transit_copy (target_copy)
574 WHERE dest_recv_time IS NULL AND cancel_time IS NULL;
576 -- Check for duplicate transits across all transit types
577 CREATE OR REPLACE FUNCTION action.copy_transit_is_unique()
578 RETURNS TRIGGER AS $func$
580 PERFORM * FROM action.transit_copy
581 WHERE target_copy = NEW.target_copy
582 AND dest_recv_time IS NULL
583 AND cancel_time IS NULL;
586 RAISE EXCEPTION 'Copy id=% is already in transit', NEW.target_copy;
590 $func$ LANGUAGE PLPGSQL STABLE;
592 CREATE CONSTRAINT TRIGGER transit_copy_is_unique_check
593 AFTER INSERT ON action.transit_copy
594 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
596 CREATE TABLE action.hold_transit_copy (
597 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
598 ) INHERITS (action.transit_copy);
599 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
600 -- 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
601 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
602 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
603 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
604 CREATE INDEX hold_transit_copy_hold_idx on action.hold_transit_copy (hold);
606 CREATE CONSTRAINT TRIGGER hold_transit_copy_is_unique_check
607 AFTER INSERT ON action.hold_transit_copy
608 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
611 CREATE TABLE action.unfulfilled_hold_list (
612 id BIGSERIAL PRIMARY KEY,
613 current_copy BIGINT NOT NULL,
615 circ_lib INT NOT NULL,
616 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
618 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
620 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
624 FROM action.unfulfilled_hold_list u
625 JOIN asset.copy c ON (c.id = u.current_copy)
628 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
631 FROM action.unfulfilled_hold_loops
634 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
636 FROM action.unfulfilled_hold_loops l
637 JOIN action.unfulfilled_hold_min_loop m USING (hold)
638 WHERE l.count = m.min;
640 CREATE VIEW action.unfulfilled_hold_max_loop AS
643 FROM action.unfulfilled_hold_loops
647 CREATE TABLE action.aged_hold_request (
649 usr_home_ou INT NOT NULL,
650 usr_profile INT NOT NULL,
652 staff_placed BOOLEAN NOT NULL,
653 LIKE action.hold_request
655 ALTER TABLE action.aged_hold_request
656 ADD PRIMARY KEY (id),
658 DROP COLUMN requestor,
659 DROP COLUMN sms_carrier,
660 ALTER COLUMN phone_notify TYPE BOOLEAN
661 USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
662 ALTER COLUMN sms_notify TYPE BOOLEAN
663 USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
664 ALTER COLUMN phone_notify SET NOT NULL,
665 ALTER COLUMN sms_notify SET NOT NULL;
666 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
667 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
668 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
669 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
671 CREATE OR REPLACE VIEW action.all_hold_request AS
673 COALESCE(a.post_code, b.post_code) AS usr_post_code,
674 p.home_ou AS usr_home_ou,
675 p.profile AS usr_profile,
676 EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
677 CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
681 ahr.fulfillment_time,
691 ahr.fulfillment_staff,
698 ahr.holdable_formats,
700 WHEN ahr.phone_notify IS NULL THEN FALSE
701 WHEN ahr.phone_notify = '' THEN FALSE
706 WHEN ahr.sms_notify IS NULL THEN FALSE
707 WHEN ahr.sms_notify = '' THEN FALSE
715 ahr.shelf_expire_time,
716 ahr.current_shelf_lib,
718 FROM action.hold_request ahr
719 JOIN actor.usr p ON (ahr.usr = p.id)
720 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
721 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
761 FROM action.aged_hold_request;
763 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
766 -- Archive a copy of the old row to action.aged_hold_request
768 INSERT INTO action.aged_hold_request
844 FROM action.all_hold_request WHERE id = OLD.id;
848 $$ LANGUAGE 'plpgsql';
850 CREATE TRIGGER action_hold_request_aging_tgr
851 BEFORE DELETE ON action.hold_request
853 EXECUTE PROCEDURE action.age_hold_on_delete ();
855 CREATE TABLE action.fieldset_group (
856 id SERIAL PRIMARY KEY,
858 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
859 complete_time TIMESTAMPTZ,
860 container INT, -- Points to a container of some type ...
861 container_type TEXT, -- One of 'biblio_record_entry', 'user', 'call_number', 'copy'
862 can_rollback BOOL DEFAULT TRUE,
863 rollback_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
864 rollback_time TIMESTAMPTZ,
865 creator INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
866 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
869 CREATE TABLE action.fieldset (
870 id SERIAL PRIMARY KEY,
871 fieldset_group INT REFERENCES action.fieldset_group (id)
872 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
873 owner INT NOT NULL REFERENCES actor.usr (id)
874 DEFERRABLE INITIALLY DEFERRED,
875 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
876 DEFERRABLE INITIALLY DEFERRED,
878 CONSTRAINT valid_status CHECK ( status in
879 ( 'PENDING', 'APPLIED', 'ERROR' )),
880 creation_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
881 scheduled_time TIMESTAMPTZ,
882 applied_time TIMESTAMPTZ,
883 classname TEXT NOT NULL, -- an IDL class name
886 stored_query INT REFERENCES query.stored_query (id)
887 DEFERRABLE INITIALLY DEFERRED,
889 CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
890 CONSTRAINT fieldset_one_or_the_other CHECK (
891 (stored_query IS NOT NULL AND pkey_value IS NULL) OR
892 (pkey_value IS NOT NULL AND stored_query IS NULL)
894 -- the CHECK constraint means we can update the fields for a single
895 -- row without all the extra overhead involved in a query
898 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
899 CREATE INDEX action_owner_idx ON action.fieldset( owner );
902 CREATE TABLE action.fieldset_col_val (
903 id SERIAL PRIMARY KEY,
904 fieldset INT NOT NULL REFERENCES action.fieldset
906 DEFERRABLE INITIALLY DEFERRED,
907 col TEXT NOT NULL, -- "field" from the idl ... the column on the table
908 val TEXT, -- value for the column ... NULL means, well, NULL
909 CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
913 -- represents a circ chain summary
914 CREATE TYPE action.circ_chain_summary AS (
916 start_time TIMESTAMP WITH TIME ZONE,
917 checkout_workstation TEXT,
918 last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
919 last_stop_fines TEXT,
920 last_stop_fines_time TIMESTAMP WITH TIME ZONE,
921 last_renewal_workstation TEXT, -- NULL if no renewals
922 last_checkin_workstation TEXT,
923 last_checkin_time TIMESTAMP WITH TIME ZONE,
924 last_checkin_scan_time TIMESTAMP WITH TIME ZONE
928 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id BIGINT ) RETURNS SETOF action.circulation AS $$
930 tmp_circ action.circulation%ROWTYPE;
931 circ_0 action.circulation%ROWTYPE;
934 SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
936 IF tmp_circ IS NULL THEN
937 RETURN NEXT tmp_circ;
941 -- find the front of the chain
943 SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
944 IF tmp_circ IS NULL THEN
950 -- now send the circs to the caller, oldest to newest
953 IF tmp_circ IS NULL THEN
956 RETURN NEXT tmp_circ;
957 SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
961 $$ LANGUAGE 'plpgsql';
963 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id BIGINT ) RETURNS action.circ_chain_summary AS $$
967 -- first circ in the chain
968 circ_0 action.circulation%ROWTYPE;
970 -- last circ in the chain
971 circ_n action.circulation%ROWTYPE;
973 -- circ chain under construction
974 chain action.circ_chain_summary;
975 tmp_circ action.circulation%ROWTYPE;
979 chain.num_circs := 0;
980 FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
982 IF chain.num_circs = 0 THEN
986 chain.num_circs := chain.num_circs + 1;
990 chain.start_time := circ_0.xact_start;
991 chain.last_stop_fines := circ_n.stop_fines;
992 chain.last_stop_fines_time := circ_n.stop_fines_time;
993 chain.last_checkin_time := circ_n.checkin_time;
994 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
995 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
996 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
998 IF chain.num_circs > 1 THEN
999 chain.last_renewal_time := circ_n.xact_start;
1000 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
1006 $$ LANGUAGE 'plpgsql';
1008 -- same as action.circ_chain, but returns action.all_circulation
1009 -- rows which may include aged circulations.
1010 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
1011 RETURNS SETOF action.all_circulation_slim AS $$
1013 tmp_circ action.all_circulation_slim%ROWTYPE;
1014 circ_0 action.all_circulation_slim%ROWTYPE;
1017 SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
1019 IF tmp_circ IS NULL THEN
1020 RETURN NEXT tmp_circ;
1024 -- find the front of the chain
1026 SELECT INTO tmp_circ * FROM action.all_circulation_slim
1027 WHERE id = tmp_circ.parent_circ;
1028 IF tmp_circ IS NULL THEN
1034 -- now send the circs to the caller, oldest to newest
1037 IF tmp_circ IS NULL THEN
1040 RETURN NEXT tmp_circ;
1041 SELECT INTO tmp_circ * FROM action.all_circulation_slim
1042 WHERE parent_circ = tmp_circ.id;
1046 $$ LANGUAGE 'plpgsql';
1048 -- same as action.summarize_circ_chain, but returns data collected
1049 -- from action.all_circulation, which may include aged circulations.
1050 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
1051 (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
1055 -- first circ in the chain
1056 circ_0 action.all_circulation_slim%ROWTYPE;
1058 -- last circ in the chain
1059 circ_n action.all_circulation_slim%ROWTYPE;
1061 -- circ chain under construction
1062 chain action.circ_chain_summary;
1063 tmp_circ action.all_circulation_slim%ROWTYPE;
1067 chain.num_circs := 0;
1068 FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
1070 IF chain.num_circs = 0 THEN
1074 chain.num_circs := chain.num_circs + 1;
1078 chain.start_time := circ_0.xact_start;
1079 chain.last_stop_fines := circ_n.stop_fines;
1080 chain.last_stop_fines_time := circ_n.stop_fines_time;
1081 chain.last_checkin_time := circ_n.checkin_time;
1082 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
1083 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
1084 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
1086 IF chain.num_circs > 1 THEN
1087 chain.last_renewal_time := circ_n.xact_start;
1088 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
1094 $$ LANGUAGE 'plpgsql';
1096 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
1098 h action.hold_request%ROWTYPE;
1101 usr_view_count actor.usr_setting%ROWTYPE;
1102 usr_view_age actor.usr_setting%ROWTYPE;
1103 usr_view_start actor.usr_setting%ROWTYPE;
1105 SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
1106 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
1107 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
1111 FROM action.hold_request
1113 AND fulfillment_time IS NULL
1114 AND cancel_time IS NULL
1115 ORDER BY request_time DESC
1120 IF usr_view_start.value IS NULL THEN
1124 IF usr_view_age.value IS NOT NULL THEN
1125 -- User opted in and supplied a retention age
1126 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
1127 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1129 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
1133 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1136 IF usr_view_count.value IS NOT NULL THEN
1137 view_count := oils_json_to_text(usr_view_count.value)::INT;
1142 -- show some fulfilled/canceled holds
1145 FROM action.hold_request
1147 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
1148 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
1149 ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
1157 $func$ LANGUAGE PLPGSQL;
1159 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
1161 org_keep_age INTERVAL;
1162 org_use_last BOOL = false;
1163 org_age_is_min BOOL = false;
1169 circ_chain_head action.circulation%ROWTYPE;
1170 circ_chain_tail action.circulation%ROWTYPE;
1175 last_finished TIMESTAMP WITH TIME ZONE;
1180 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
1182 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
1183 IF org_keep_count IS NULL THEN
1184 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
1187 SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
1188 SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
1190 -- First, find copies with more than keep_count non-renewal circs
1193 COUNT(*) AS total_real_circs
1194 FROM action.circulation
1195 WHERE parent_circ IS NULL
1196 AND xact_finish IS NOT NULL
1197 GROUP BY target_copy
1198 HAVING COUNT(*) > org_keep_count
1200 -- And, for those, select circs that are finished and older than keep_age
1201 FOR circ_chain_head IN
1202 -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
1203 -- The outer query then uses that information to skip the most recent set the library wants to keep
1204 -- End result is we don't care what order they come out in, as they are all potentials for deletion.
1205 SELECT ac.* FROM action.circulation ac JOIN (
1206 SELECT rank() OVER (ORDER BY xact_start DESC), ac.id
1207 FROM action.circulation ac
1208 WHERE ac.target_copy = target_acp.target_copy
1209 AND ac.parent_circ IS NULL
1210 ORDER BY ac.xact_start ) ranked USING (id)
1211 WHERE ranked.rank > org_keep_count
1214 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
1215 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);
1216 CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
1218 IF NOT org_use_last THEN
1219 last_finished := circ_chain_tail.xact_finish;
1222 keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
1224 IF org_age_is_min THEN
1225 keep_age := GREATEST( keep_age, org_keep_age );
1228 CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
1230 -- We've passed the purging tests, purge the circ chain starting at the end
1231 -- A trigger should auto-purge the rest of the chain.
1232 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
1234 count_purged := count_purged + 1;
1239 return count_purged;
1241 $func$ LANGUAGE PLPGSQL;
1243 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
1245 current_hold RECORD;
1251 user_start TIMESTAMPTZ;
1256 SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
1257 SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
1258 SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
1261 rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
1262 cgf_cs.value::INTERVAL as cgf_cs,
1265 action.hold_request ahr
1266 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)
1268 (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
1270 IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
1271 prev_usr := current_hold.usr;
1272 SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
1273 SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
1274 SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
1275 IF user_start IS NOT NULL THEN
1276 user_age := LEAST(user_age, AGE(NOW(), user_start));
1278 IF user_count IS NULL THEN
1279 user_count := 1000; -- Assumption based on the user visible holds routine
1282 -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
1283 IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
1286 IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
1290 -- User keep age needs combining with count. If too old AND within the count, keep!
1291 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
1295 -- All checks should have passed, delete!
1296 DELETE FROM action.hold_request WHERE id = current_hold.id;
1297 purged_holds := purged_holds + 1;
1299 RETURN purged_holds;
1301 $func$ LANGUAGE plpgsql;
1303 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1304 fieldset_id IN INT, -- id from action.fieldset
1305 table_name IN TEXT, -- table to be updated
1306 pkey_name IN TEXT, -- name of primary key column in that table
1307 query IN TEXT -- query constructed by qstore (for query-based
1308 -- fieldsets only; otherwise null
1324 fs_obj action.fieldset%ROWTYPE;
1325 fs_group action.fieldset_group%ROWTYPE;
1329 IF fieldset_id IS NULL THEN
1330 RETURN 'Fieldset ID parameter is NULL';
1332 IF table_name IS NULL THEN
1333 RETURN 'Table name parameter is NULL';
1335 IF pkey_name IS NULL THEN
1336 RETURN 'Primary key name parameter is NULL';
1341 quote_literal( pkey_value )
1351 -- Build the WHERE clause. This differs according to whether it's a
1352 -- single-row fieldset or a query-based fieldset.
1354 IF query IS NULL AND fs_pkey_value IS NULL THEN
1355 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1356 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1357 fs_query := rtrim( query, ';' );
1358 where_clause := 'WHERE ' || pkey_name || ' IN ( '
1359 || fs_query || ' )';
1360 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
1361 where_clause := 'WHERE ' || pkey_name || ' = ';
1362 IF pkey_name = 'id' THEN
1363 where_clause := where_clause || fs_pkey_value;
1364 ELSIF pkey_name = 'code' THEN
1365 where_clause := where_clause || quote_literal(fs_pkey_value);
1367 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1369 ELSE -- both are not null
1370 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1373 IF fs_status IS NULL THEN
1374 RETURN 'No fieldset found for id = ' || fieldset_id;
1375 ELSIF fs_status = 'APPLIED' THEN
1376 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1379 SELECT * INTO fs_obj FROM action.fieldset WHERE id = fieldset_id;
1380 SELECT * INTO fs_group FROM action.fieldset_group WHERE id = fs_obj.fieldset_group;
1382 IF fs_group.can_rollback THEN
1383 -- This is part of a non-rollback group. We need to record the current values for future rollback.
1385 INSERT INTO action.fieldset_group (can_rollback, name, creator, owning_lib, container, container_type)
1386 VALUES (FALSE, 'ROLLBACK: '|| fs_group.name, fs_group.creator, fs_group.owning_lib, fs_group.container, fs_group.container_type);
1388 fsg_id := CURRVAL('action.fieldset_group_id_seq');
1390 FOR rb_row IN EXECUTE 'SELECT * FROM ' || table_name || ' ' || where_clause LOOP
1391 IF pkey_name = 'id' THEN
1392 fs_pkey_value := rb_row.id;
1393 ELSIF pkey_name = 'code' THEN
1394 fs_pkey_value := rb_row.code;
1396 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1398 INSERT INTO action.fieldset (fieldset_group,owner,owning_lib,status,classname,name,pkey_value)
1399 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);
1401 fs_id := CURRVAL('action.fieldset_id_seq');
1405 FROM action.fieldset_col_val
1406 WHERE fieldset = fieldset_id
1408 EXECUTE 'INSERT INTO action.fieldset_col_val (fieldset, col, val) ' ||
1409 'SELECT '|| fs_id || ', '||quote_literal(cv.col)||', '||cv.col||' FROM '||table_name||' WHERE '||pkey_name||' = '||fs_pkey_value;
1414 statement := 'UPDATE ' || table_name || ' SET';
1420 FROM action.fieldset_col_val
1421 WHERE fieldset = fieldset_id
1423 statement := statement || sep || ' ' || cv.col
1424 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1429 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1431 statement := statement || ' ' || where_clause;
1434 -- Execute the update
1438 GET DIAGNOSTICS update_count = ROW_COUNT;
1440 IF update_count = 0 THEN
1441 RAISE data_exception;
1444 IF fsg_id IS NOT NULL THEN
1445 UPDATE action.fieldset_group SET rollback_group = fsg_id WHERE id = fs_group.id;
1448 IF fs_group.id IS NOT NULL THEN
1449 UPDATE action.fieldset_group SET complete_time = now() WHERE id = fs_group.id;
1452 UPDATE action.fieldset SET status = 'APPLIED', applied_time = now() WHERE id = fieldset_id;
1454 EXCEPTION WHEN data_exception THEN
1455 msg := 'No eligible rows found for fieldset ' || fieldset_id;
1456 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1463 EXCEPTION WHEN OTHERS THEN
1464 msg := 'Unable to apply fieldset ' || fieldset_id || ': ' || sqlerrm;
1465 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1469 $$ LANGUAGE plpgsql;
1471 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
1472 Applies a specified fieldset, using a supplied table name and primary
1473 key name. The query parameter should be non-null only for
1474 query-based fieldsets.
1476 Returns NULL if successful, or an error message if not.
1479 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
1482 copy_context_ou INT DEFAULT NULL
1483 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
1484 -- support an "ahprox" measurement: adjust prox between copy circ lib and
1485 -- hold request lib, but I'm unsure whether to use this theoretical
1486 -- argument only in the baseline calculation or later in the other
1487 -- queries in this function.
1488 ) RETURNS NUMERIC AS $f$
1490 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
1491 ahr action.hold_request%ROWTYPE;
1492 acp asset.copy%ROWTYPE;
1493 acn asset.call_number%ROWTYPE;
1494 acl asset.copy_location%ROWTYPE;
1495 baseline_prox NUMERIC;
1505 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
1506 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
1507 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
1508 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
1510 IF copy_context_ou IS NULL THEN
1511 copy_context_ou := acp.circ_lib;
1514 -- First, gather the baseline proximity of "here" to pickup lib
1515 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
1517 -- Find any absolute adjustments, and set the baseline prox to that
1518 SELECT adj.* INTO aoupa
1519 FROM actor.org_unit_proximity_adjustment adj
1520 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1521 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1522 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acl_ol.id = adj.copy_location)
1523 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1524 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1525 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1526 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1527 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1528 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1529 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1530 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1531 absolute_adjustment AND
1532 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1534 COALESCE(acp_cl.distance,999)
1535 + COALESCE(acn_ol.distance,999)
1536 + COALESCE(acl_ol.distance,999)
1537 + COALESCE(ahr_pl.distance,999)
1538 + COALESCE(ahr_rl.distance,999),
1543 baseline_prox := aoupa.prox_adjustment;
1546 -- Now find any relative adjustments, and change the baseline prox based on them
1549 FROM actor.org_unit_proximity_adjustment adj
1550 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1551 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1552 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1553 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1554 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1555 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1556 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1557 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1558 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1559 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1560 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1561 NOT absolute_adjustment AND
1562 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1564 baseline_prox := baseline_prox + aoupa.prox_adjustment;
1567 RETURN baseline_prox;
1569 $f$ LANGUAGE PLPGSQL;
1571 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$
1573 NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy);
1576 $f$ LANGUAGE PLPGSQL;
1578 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 ();
1580 CREATE TABLE action.usr_circ_history (
1581 id BIGSERIAL PRIMARY KEY,
1582 usr INTEGER NOT NULL REFERENCES actor.usr(id)
1583 DEFERRABLE INITIALLY DEFERRED,
1584 xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1585 target_copy BIGINT NOT NULL, -- asset.copy.id / serial.unit.id
1586 due_date TIMESTAMP WITH TIME ZONE NOT NULL,
1587 checkin_time TIMESTAMP WITH TIME ZONE,
1588 source_circ BIGINT REFERENCES action.circulation(id)
1589 ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
1592 CREATE INDEX action_usr_circ_history_usr_idx ON action.usr_circ_history ( usr );
1593 CREATE INDEX action_usr_circ_history_source_circ_idx ON action.usr_circ_history ( source_circ );
1595 CREATE TRIGGER action_usr_circ_history_target_copy_trig
1596 AFTER INSERT OR UPDATE ON action.usr_circ_history
1597 FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
1599 CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history()
1600 RETURNS TRIGGER AS $FUNK$
1606 -- Any retention value signifies history is enabled.
1607 -- This assumes that clearing these values via external
1608 -- process deletes the action.usr_circ_history rows.
1609 -- TODO: replace these settings w/ a single bool setting?
1610 PERFORM 1 FROM actor.usr_setting
1611 WHERE usr = NEW.usr AND value IS NOT NULL AND name IN (
1612 'history.circ.retention_age',
1613 'history.circ.retention_start'
1620 IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN
1621 -- Starting a new circulation. Insert the history row.
1622 INSERT INTO action.usr_circ_history
1623 (usr, xact_start, target_copy, due_date, source_circ)
1635 -- find the first and last circs in the circ chain
1636 -- for the currently modified circ.
1637 FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP
1638 IF first_circ IS NULL THEN
1639 first_circ := cur_circ;
1642 -- Allow the loop to continue so that at as the loop
1643 -- completes cur_circ points to the final circulation.
1646 IF NEW.id <> cur_circ THEN
1647 -- Modifying an intermediate circ. Ignore it.
1651 -- Update the due_date/checkin_time on the history row if the current
1652 -- circ is the last circ in the chain and an update is warranted.
1654 UPDATE action.usr_circ_history
1656 due_date = NEW.due_date,
1657 checkin_time = NEW.checkin_time
1659 source_circ = first_circ
1661 due_date <> NEW.due_date OR (
1662 (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR
1663 (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR
1664 (checkin_time <> NEW.checkin_time)
1669 $FUNK$ LANGUAGE PLPGSQL;
1671 CREATE TRIGGER maintain_usr_circ_history_tgr
1672 AFTER INSERT OR UPDATE ON action.circulation
1673 FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history();
1675 CREATE OR REPLACE VIEW action.all_circulation_combined_types AS
1676 SELECT acirc.id AS id,
1681 ac_acirc.circ_modifier AS item_type,
1682 'regular_circ'::text AS circ_type
1683 FROM action.circulation acirc,
1685 WHERE acirc.target_copy = ac_acirc.id
1687 SELECT ancc.id::BIGINT AS id,
1688 ancc.circ_time AS xact_start,
1690 ancc.staff AS circ_staff,
1691 ancc.circ_time AS create_time,
1692 cnct_ancc.name AS item_type,
1693 'non-cat_circ'::text AS circ_type
1694 FROM action.non_cataloged_circulation ancc,
1695 config.non_cataloged_type cnct_ancc
1696 WHERE ancc.item_type = cnct_ancc.id
1698 SELECT aihu.id::BIGINT AS id,
1699 aihu.use_time AS xact_start,
1700 aihu.org_unit AS circ_lib,
1701 aihu.staff AS circ_staff,
1702 aihu.use_time AS create_time,
1703 ac_aihu.circ_modifier AS item_type,
1704 'in-house_use'::text AS circ_type
1705 FROM action.in_house_use aihu,
1707 WHERE aihu.item = ac_aihu.id
1709 SELECT ancihu.id::BIGINT AS id,
1710 ancihu.use_time AS xact_start,
1711 ancihu.org_unit AS circ_lib,
1712 ancihu.staff AS circ_staff,
1713 ancihu.use_time AS create_time,
1714 cnct_ancihu.name AS item_type,
1715 'non-cat_circ'::text AS circ_type
1716 FROM action.non_cat_in_house_use ancihu,
1717 config.non_cataloged_type cnct_ancihu
1718 WHERE ancihu.item_type = cnct_ancihu.id
1720 SELECT aacirc.id AS id,
1725 ac_aacirc.circ_modifier AS item_type,
1726 'aged_circ'::text AS circ_type
1727 FROM action.aged_circulation aacirc,
1728 asset.copy ac_aacirc
1729 WHERE aacirc.target_copy = ac_aacirc.id;