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 workstation INT REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
29 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
30 use_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
32 CREATE INDEX action_in_house_use_staff_idx ON action.in_house_use ( staff );
33 CREATE INDEX action_in_house_use_ws_idx ON action.in_house_use ( workstation );
35 CREATE TABLE action.non_cataloged_circulation (
36 id SERIAL PRIMARY KEY,
37 patron INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
38 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
39 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
40 item_type INT NOT NULL REFERENCES config.non_cataloged_type (id) DEFERRABLE INITIALLY DEFERRED,
41 circ_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
43 CREATE INDEX action_non_cat_circ_patron_idx ON action.non_cataloged_circulation ( patron );
44 CREATE INDEX action_non_cat_circ_staff_idx ON action.non_cataloged_circulation ( staff );
46 CREATE TABLE action.non_cat_in_house_use (
47 id SERIAL PRIMARY KEY,
48 item_type BIGINT NOT NULL REFERENCES config.non_cataloged_type(id) DEFERRABLE INITIALLY DEFERRED,
49 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
50 workstation INT REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
51 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
52 use_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
54 CREATE INDEX non_cat_in_house_use_staff_idx ON action.non_cat_in_house_use ( staff );
55 CREATE INDEX non_cat_in_house_use_ws_idx ON action.non_cat_in_house_use ( workstation );
57 CREATE TABLE action.survey (
58 id SERIAL PRIMARY KEY,
59 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
60 start_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
61 end_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() + '10 years'::INTERVAL,
62 usr_summary BOOL NOT NULL DEFAULT FALSE,
63 opac BOOL NOT NULL DEFAULT FALSE,
64 poll BOOL NOT NULL DEFAULT FALSE,
65 required BOOL NOT NULL DEFAULT FALSE,
67 description TEXT NOT NULL
69 CREATE UNIQUE INDEX asv_once_per_owner_idx ON action.survey (owner,name);
71 CREATE TABLE action.survey_question (
72 id SERIAL PRIMARY KEY,
73 survey INT NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED,
74 question TEXT NOT NULL
77 CREATE TABLE action.survey_answer (
78 id SERIAL PRIMARY KEY,
79 question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
83 CREATE SEQUENCE action.survey_response_group_id_seq;
85 CREATE TABLE action.survey_response (
86 id BIGSERIAL PRIMARY KEY,
87 response_group_id INT,
88 usr INT, -- REFERENCES actor.usr
89 survey INT NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED,
90 question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
91 answer INT NOT NULL REFERENCES action.survey_answer DEFERRABLE INITIALLY DEFERRED,
92 answer_date TIMESTAMP WITH TIME ZONE,
93 effective_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
95 CREATE INDEX action_survey_response_usr_idx ON action.survey_response ( usr );
97 CREATE OR REPLACE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS '
99 NEW.answer_date := NOW();
102 ' LANGUAGE 'plpgsql';
103 CREATE TRIGGER action_survey_response_answer_date_fixup_tgr
104 BEFORE INSERT ON action.survey_response
106 EXECUTE PROCEDURE action.survey_response_answer_date_fixup ();
108 CREATE TABLE action.archive_actor_stat_cat (
109 id BIGSERIAL PRIMARY KEY,
110 xact BIGINT NOT NULL, -- action.circulation (+aged/all)
111 stat_cat INT NOT NULL,
115 CREATE TABLE action.archive_asset_stat_cat (
116 id BIGSERIAL PRIMARY KEY,
117 xact BIGINT NOT NULL, -- action.circulation (+aged/all)
118 stat_cat INT NOT NULL,
123 CREATE TABLE action.circulation (
124 target_copy BIGINT NOT NULL, -- asset.copy.id
125 circ_lib INT NOT NULL, -- actor.org_unit.id
126 circ_staff INT NOT NULL, -- actor.usr.id
127 checkin_staff INT, -- actor.usr.id
128 checkin_lib INT, -- actor.org_unit.id
129 renewal_remaining INT NOT NULL, -- derived from "circ duration" rule
130 grace_period INTERVAL NOT NULL, -- derived from "circ fine" rule
131 due_date TIMESTAMP WITH TIME ZONE,
132 stop_fines_time TIMESTAMP WITH TIME ZONE,
133 checkin_time TIMESTAMP WITH TIME ZONE,
134 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
135 duration INTERVAL, -- derived from "circ duration" rule
136 fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
137 recurring_fine NUMERIC(6,2), -- derived from "circ fine" rule
138 max_fine NUMERIC(6,2), -- derived from "max fine" rule
139 phone_renewal BOOL NOT NULL DEFAULT FALSE,
140 desk_renewal BOOL NOT NULL DEFAULT FALSE,
141 opac_renewal BOOL NOT NULL DEFAULT FALSE,
142 duration_rule TEXT NOT NULL, -- name of "circ duration" rule
143 recurring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule
144 max_fine_rule TEXT NOT NULL, -- name of "max fine" rule
145 stop_fines TEXT CHECK (stop_fines IN (
146 'CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE','CLAIMSNEVERCHECKEDOUT')),
147 workstation INT REFERENCES actor.workstation(id)
149 DEFERRABLE INITIALLY DEFERRED,
150 checkin_workstation INT REFERENCES actor.workstation(id)
152 DEFERRABLE INITIALLY DEFERRED,
153 copy_location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
154 checkin_scan_time TIMESTAMP WITH TIME ZONE,
155 auto_renewal BOOLEAN NOT NULL DEFAULT FALSE,
156 auto_renewal_remaining INTEGER
157 ) INHERITS (money.billable_xact);
158 ALTER TABLE action.circulation ADD PRIMARY KEY (id);
159 ALTER TABLE action.circulation
160 ADD COLUMN parent_circ BIGINT
161 REFERENCES action.circulation( id )
162 DEFERRABLE INITIALLY DEFERRED;
163 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
164 CREATE INDEX circ_outstanding_idx ON action.circulation (usr) WHERE checkin_time IS NULL;
165 CREATE INDEX circ_checkin_time ON "action".circulation (checkin_time) WHERE checkin_time IS NOT NULL;
166 CREATE INDEX circ_circ_lib_idx ON "action".circulation (circ_lib);
167 CREATE INDEX circ_open_date_idx ON "action".circulation (xact_start) WHERE xact_finish IS NULL;
168 CREATE INDEX circ_all_usr_idx ON action.circulation ( usr );
169 CREATE INDEX circ_circ_staff_idx ON action.circulation ( circ_staff );
170 CREATE INDEX circ_checkin_staff_idx ON action.circulation ( checkin_staff );
171 CREATE INDEX action_circulation_target_copy_idx ON action.circulation (target_copy);
172 CREATE UNIQUE INDEX circ_parent_idx ON action.circulation ( parent_circ ) WHERE parent_circ IS NOT NULL;
173 CREATE UNIQUE INDEX only_one_concurrent_checkout_per_copy ON action.circulation(target_copy) WHERE checkin_time IS NULL;
175 CREATE TRIGGER action_circulation_target_copy_trig AFTER INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
177 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation');
178 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
179 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
181 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
183 proper_tz TEXT := COALESCE(
186 FROM actor.org_unit_ancestor_setting('lib.timezone',NEW.circ_lib)
189 CURRENT_SETTING('timezone')
193 IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 -- day-granular duration
194 AND SUBSTRING((NEW.due_date AT TIME ZONE proper_tz)::TIME::TEXT FROM 1 FOR 8) <> '23:59:59' THEN -- has not yet been pushed
195 NEW.due_date = ((NEW.due_date AT TIME ZONE proper_tz)::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL) || ' ' || proper_tz;
202 CREATE TRIGGER push_due_date_tgr BEFORE INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.push_circ_due_time();
204 CREATE OR REPLACE FUNCTION action.fill_circ_copy_location () RETURNS TRIGGER AS $$
206 SELECT INTO NEW.copy_location location FROM asset.copy WHERE id = NEW.target_copy;
211 CREATE TRIGGER fill_circ_copy_location_tgr BEFORE INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.fill_circ_copy_location();
213 CREATE OR REPLACE FUNCTION action.archive_stat_cats () RETURNS TRIGGER AS $$
215 INSERT INTO action.archive_actor_stat_cat(xact, stat_cat, value)
216 SELECT NEW.id, asceum.stat_cat, asceum.stat_cat_entry
217 FROM actor.stat_cat_entry_usr_map asceum
218 JOIN actor.stat_cat sc ON asceum.stat_cat = sc.id
219 WHERE NEW.usr = asceum.target_usr AND sc.checkout_archive;
220 INSERT INTO action.archive_asset_stat_cat(xact, stat_cat, value)
221 SELECT NEW.id, ascecm.stat_cat, asce.value
222 FROM asset.stat_cat_entry_copy_map ascecm
223 JOIN asset.stat_cat sc ON ascecm.stat_cat = sc.id
224 JOIN asset.stat_cat_entry asce ON ascecm.stat_cat_entry = asce.id
225 WHERE NEW.target_copy = ascecm.owning_copy AND sc.checkout_archive;
230 CREATE TRIGGER archive_stat_cats_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.archive_stat_cats();
232 CREATE TABLE action.aged_circulation (
234 usr_home_ou INT NOT NULL,
235 usr_profile INT NOT NULL,
237 copy_call_number INT NOT NULL,
238 copy_owning_lib INT NOT NULL,
239 copy_circ_lib INT NOT NULL,
240 copy_bib_record BIGINT NOT NULL,
241 LIKE action.circulation
244 ALTER TABLE action.aged_circulation ADD PRIMARY KEY (id);
245 ALTER TABLE action.aged_circulation DROP COLUMN usr;
246 CREATE INDEX aged_circ_circ_lib_idx ON "action".aged_circulation (circ_lib);
247 CREATE INDEX aged_circ_start_idx ON "action".aged_circulation (xact_start);
248 CREATE INDEX aged_circ_copy_circ_lib_idx ON "action".aged_circulation (copy_circ_lib);
249 CREATE INDEX aged_circ_copy_owning_lib_idx ON "action".aged_circulation (copy_owning_lib);
250 CREATE INDEX aged_circ_copy_location_idx ON "action".aged_circulation (copy_location);
251 CREATE INDEX action_aged_circulation_target_copy_idx ON action.aged_circulation (target_copy);
252 CREATE INDEX action_aged_circulation_parent_circ_idx ON action.aged_circulation (parent_circ);
254 CREATE OR REPLACE VIEW action.all_circulation AS
255 SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
256 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
257 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
258 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
259 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
260 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
261 auto_renewal, auto_renewal_remaining,
263 FROM action.aged_circulation
265 SELECT DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
266 cp.call_number AS copy_call_number, circ.copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
267 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
268 circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
269 circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
270 circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
271 circ.parent_circ, circ.auto_renewal, circ.auto_renewal_remaining, circ.usr
272 FROM action.circulation circ
273 JOIN asset.copy cp ON (circ.target_copy = cp.id)
274 JOIN asset.call_number cn ON (cp.call_number = cn.id)
275 JOIN actor.usr p ON (circ.usr = p.id)
276 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
277 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
279 CREATE OR REPLACE VIEW action.all_circulation_slim AS
280 SELECT * FROM action.circulation
315 auto_renewal_remaining,
317 FROM action.aged_circulation
321 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
326 -- If there are any renewals for this circulation, don't archive or delete
327 -- it yet. We'll do so later, when we archive and delete the renewals.
329 SELECT 'Y' INTO found
330 FROM action.circulation
331 WHERE parent_circ = OLD.id
335 RETURN NULL; -- don't delete
338 -- Archive a copy of the old row to action.aged_circulation
340 INSERT INTO action.aged_circulation
341 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
342 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
343 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
344 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
345 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
346 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
347 auto_renewal, auto_renewal_remaining)
349 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
350 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
351 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
352 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
353 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
354 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
355 auto_renewal, auto_renewal_remaining
356 FROM action.all_circulation WHERE id = OLD.id;
358 -- Migrate billings and payments to aged tables
360 SELECT 'Y' INTO found FROM config.global_flag
361 WHERE name = 'history.money.age_with_circs' AND enabled;
364 PERFORM money.age_billings_and_payments_for_xact(OLD.id);
369 $$ LANGUAGE 'plpgsql';
371 CREATE TRIGGER action_circulation_aging_tgr
372 BEFORE DELETE ON action.circulation
374 EXECUTE PROCEDURE action.age_circ_on_delete ();
377 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
380 -- Having deleted a renewal, we can delete the original circulation (or a previous
381 -- renewal, if that's what parent_circ is pointing to). That deletion will trigger
382 -- deletion of any prior parents, etc. recursively.
384 IF OLD.parent_circ IS NOT NULL THEN
385 DELETE FROM action.circulation
386 WHERE id = OLD.parent_circ;
391 $$ LANGUAGE 'plpgsql';
393 CREATE TRIGGER age_parent_circ
394 AFTER DELETE ON action.circulation
396 EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
399 CREATE OR REPLACE VIEW action.open_circulation AS
401 FROM action.circulation
402 WHERE checkin_time IS NULL
406 CREATE OR REPLACE VIEW action.billable_circulations AS
408 FROM action.circulation
409 WHERE xact_finish IS NULL;
411 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
413 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
414 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
415 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
417 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
418 UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
420 IF NEW.stop_fines = 'LOST' THEN
421 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
426 $$ LANGUAGE 'plpgsql';
427 CREATE TRIGGER action_circulation_stop_fines_tgr
428 BEFORE UPDATE ON action.circulation
430 EXECUTE PROCEDURE action.circulation_claims_returned ();
432 CREATE TABLE action.hold_request_cancel_cause (
433 id SERIAL PRIMARY KEY,
437 CREATE TABLE action.hold_request (
438 id SERIAL PRIMARY KEY,
439 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
440 capture_time TIMESTAMP WITH TIME ZONE,
441 fulfillment_time TIMESTAMP WITH TIME ZONE,
442 checkin_time TIMESTAMP WITH TIME ZONE,
443 return_time TIMESTAMP WITH TIME ZONE,
444 prev_check_time TIMESTAMP WITH TIME ZONE,
445 expire_time TIMESTAMP WITH TIME ZONE,
446 cancel_time TIMESTAMP WITH TIME ZONE,
447 cancel_cause INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
449 target BIGINT NOT NULL, -- see hold_type
450 current_copy BIGINT, -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.unit now...
451 fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
452 fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
453 request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
454 requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
455 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
456 selection_ou INT NOT NULL,
457 selection_depth INT NOT NULL DEFAULT 0,
458 pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
459 hold_type TEXT REFERENCES config.hold_type (hold_type) DEFERRABLE INITIALLY DEFERRED,
460 holdable_formats TEXT,
462 email_notify BOOL NOT NULL DEFAULT FALSE,
464 sms_carrier INT REFERENCES config.sms_carrier (id),
465 frozen BOOL NOT NULL DEFAULT FALSE,
466 thaw_date TIMESTAMP WITH TIME ZONE,
467 shelf_time TIMESTAMP WITH TIME ZONE,
469 mint_condition BOOL NOT NULL DEFAULT TRUE,
470 shelf_expire_time TIMESTAMPTZ,
471 current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
472 behind_desk BOOLEAN NOT NULL DEFAULT FALSE,
473 hopeless_date TIMESTAMP WITH TIME ZONE
475 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
477 OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
481 CREATE OR REPLACE FUNCTION action.hold_request_clear_map () RETURNS TRIGGER AS $$
483 DELETE FROM action.hold_copy_map WHERE hold = NEW.id;
488 CREATE TRIGGER hold_request_clear_map_tgr
489 AFTER UPDATE ON action.hold_request
492 (NEW.cancel_time IS NOT NULL AND OLD.cancel_time IS NULL)
493 OR (NEW.fulfillment_time IS NOT NULL AND OLD.fulfillment_time IS NULL)
495 EXECUTE PROCEDURE action.hold_request_clear_map();
497 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
498 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
499 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
500 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
501 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
502 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
503 CREATE INDEX hold_request_requestor_idx ON action.hold_request ( requestor );
504 CREATE INDEX hold_request_open_idx ON action.hold_request (id) WHERE cancel_time IS NULL AND fulfillment_time IS NULL;
505 CREATE INDEX hold_request_current_copy_before_cap_idx ON action.hold_request (current_copy) WHERE capture_time IS NULL AND cancel_time IS NULL;
506 CREATE UNIQUE INDEX hold_request_capture_protect_idx ON action.hold_request (current_copy) WHERE current_copy IS NOT NULL AND capture_time IS NOT NULL AND cancel_time IS NULL AND fulfillment_time IS NULL;
507 CREATE INDEX hold_request_copy_capture_time_idx ON action.hold_request (current_copy,capture_time);
508 CREATE INDEX hold_request_open_captured_shelf_lib_idx ON action.hold_request (current_shelf_lib) WHERE capture_time IS NOT NULL AND fulfillment_time IS NULL AND (pickup_lib <> current_shelf_lib);
509 CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
510 CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);
513 CREATE TABLE action.hold_request_note (
515 id BIGSERIAL PRIMARY KEY,
516 hold BIGINT NOT NULL REFERENCES action.hold_request (id)
518 DEFERRABLE INITIALLY DEFERRED,
521 slip BOOL NOT NULL DEFAULT FALSE,
522 pub BOOL NOT NULL DEFAULT FALSE,
523 staff BOOL NOT NULL DEFAULT FALSE -- created by staff
526 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
529 CREATE TABLE action.hold_notification (
530 id SERIAL PRIMARY KEY,
531 hold INT NOT NULL REFERENCES action.hold_request (id)
533 DEFERRABLE INITIALLY DEFERRED,
534 notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
535 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
536 method TEXT NOT NULL, -- email address or phone number
539 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
540 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
542 CREATE TABLE action.hold_copy_map (
543 id BIGSERIAL PRIMARY KEY,
544 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
545 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
547 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
549 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
550 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
552 CREATE OR REPLACE FUNCTION
553 action.hold_request_regen_copy_maps(
554 hold_id INTEGER, copy_ids INTEGER[]) RETURNS VOID AS $$
555 DELETE FROM action.hold_copy_map WHERE hold = $1;
556 INSERT INTO action.hold_copy_map (hold, target_copy) SELECT DISTINCT $1, UNNEST($2);
559 CREATE TABLE action.transit_copy (
560 id SERIAL PRIMARY KEY,
561 source_send_time TIMESTAMP WITH TIME ZONE,
562 dest_recv_time TIMESTAMP WITH TIME ZONE,
563 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
564 source INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
565 dest INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
566 prev_hop INT REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
567 copy_status INT NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
568 persistant_transfer BOOL NOT NULL DEFAULT FALSE,
569 prev_dest INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
570 cancel_time TIMESTAMP WITH TIME ZONE
572 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
573 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
574 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
575 CREATE INDEX active_transit_for_copy ON action.transit_copy (target_copy)
576 WHERE dest_recv_time IS NULL AND cancel_time IS NULL;
578 -- Check for duplicate transits across all transit types
579 CREATE OR REPLACE FUNCTION action.copy_transit_is_unique()
580 RETURNS TRIGGER AS $func$
582 PERFORM * FROM action.transit_copy
583 WHERE target_copy = NEW.target_copy
584 AND dest_recv_time IS NULL
585 AND cancel_time IS NULL;
588 RAISE EXCEPTION 'Copy id=% is already in transit', NEW.target_copy;
592 $func$ LANGUAGE PLPGSQL STABLE;
594 CREATE CONSTRAINT TRIGGER transit_copy_is_unique_check
595 AFTER INSERT ON action.transit_copy
596 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
598 CREATE TABLE action.hold_transit_copy (
599 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
600 ) INHERITS (action.transit_copy);
601 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
602 -- ALTER TABLE action.hold_transit_copy ADD CONSTRAINT ahtc_tc_fkey FOREIGN KEY (target_copy) REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -- XXX could be an serial.issuance
603 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
604 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
605 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
606 CREATE INDEX hold_transit_copy_hold_idx on action.hold_transit_copy (hold);
608 CREATE CONSTRAINT TRIGGER hold_transit_copy_is_unique_check
609 AFTER INSERT ON action.hold_transit_copy
610 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
613 CREATE TABLE action.unfulfilled_hold_list (
614 id BIGSERIAL PRIMARY KEY,
615 current_copy BIGINT NOT NULL,
617 circ_lib INT NOT NULL,
618 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
620 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
622 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
626 FROM action.unfulfilled_hold_list u
627 JOIN asset.copy c ON (c.id = u.current_copy)
630 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
633 FROM action.unfulfilled_hold_loops
636 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
638 FROM action.unfulfilled_hold_loops l
639 JOIN action.unfulfilled_hold_min_loop m USING (hold)
640 WHERE l.count = m.min;
642 CREATE VIEW action.unfulfilled_hold_max_loop AS
645 FROM action.unfulfilled_hold_loops
649 CREATE TABLE action.aged_hold_request (
651 usr_home_ou INT NOT NULL,
652 usr_profile INT NOT NULL,
654 staff_placed BOOLEAN NOT NULL,
655 LIKE action.hold_request
657 ALTER TABLE action.aged_hold_request
658 ADD PRIMARY KEY (id),
660 DROP COLUMN requestor,
661 DROP COLUMN sms_carrier,
662 ALTER COLUMN phone_notify TYPE BOOLEAN
663 USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
664 ALTER COLUMN sms_notify TYPE BOOLEAN
665 USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
666 ALTER COLUMN phone_notify SET NOT NULL,
667 ALTER COLUMN sms_notify SET NOT NULL;
668 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
669 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
670 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
671 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
673 CREATE OR REPLACE VIEW action.all_hold_request AS
675 COALESCE(a.post_code, b.post_code) AS usr_post_code,
676 p.home_ou AS usr_home_ou,
677 p.profile AS usr_profile,
678 EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
679 CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
683 ahr.fulfillment_time,
693 ahr.fulfillment_staff,
700 ahr.holdable_formats,
702 WHEN ahr.phone_notify IS NULL THEN FALSE
703 WHEN ahr.phone_notify = '' THEN FALSE
708 WHEN ahr.sms_notify IS NULL THEN FALSE
709 WHEN ahr.sms_notify = '' THEN FALSE
717 ahr.shelf_expire_time,
718 ahr.current_shelf_lib,
720 FROM action.hold_request ahr
721 JOIN actor.usr p ON (ahr.usr = p.id)
722 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
723 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
763 FROM action.aged_hold_request;
765 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
768 -- Archive a copy of the old row to action.aged_hold_request
770 INSERT INTO action.aged_hold_request
846 FROM action.all_hold_request WHERE id = OLD.id;
850 $$ LANGUAGE 'plpgsql';
852 CREATE TRIGGER action_hold_request_aging_tgr
853 BEFORE DELETE ON action.hold_request
855 EXECUTE PROCEDURE action.age_hold_on_delete ();
857 CREATE TABLE action.fieldset_group (
858 id SERIAL PRIMARY KEY,
860 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
861 complete_time TIMESTAMPTZ,
862 container INT, -- Points to a container of some type ...
863 container_type TEXT, -- One of 'biblio_record_entry', 'user', 'call_number', 'copy'
864 can_rollback BOOL DEFAULT TRUE,
865 rollback_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
866 rollback_time TIMESTAMPTZ,
867 creator INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
868 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
871 CREATE TABLE action.fieldset (
872 id SERIAL PRIMARY KEY,
873 fieldset_group INT REFERENCES action.fieldset_group (id)
874 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
875 owner INT NOT NULL REFERENCES actor.usr (id)
876 DEFERRABLE INITIALLY DEFERRED,
877 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
878 DEFERRABLE INITIALLY DEFERRED,
880 CONSTRAINT valid_status CHECK ( status in
881 ( 'PENDING', 'APPLIED', 'ERROR' )),
882 creation_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
883 scheduled_time TIMESTAMPTZ,
884 applied_time TIMESTAMPTZ,
885 classname TEXT NOT NULL, -- an IDL class name
888 stored_query INT REFERENCES query.stored_query (id)
889 DEFERRABLE INITIALLY DEFERRED,
891 CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
892 CONSTRAINT fieldset_one_or_the_other CHECK (
893 (stored_query IS NOT NULL AND pkey_value IS NULL) OR
894 (pkey_value IS NOT NULL AND stored_query IS NULL)
896 -- the CHECK constraint means we can update the fields for a single
897 -- row without all the extra overhead involved in a query
900 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
901 CREATE INDEX action_owner_idx ON action.fieldset( owner );
904 CREATE TABLE action.fieldset_col_val (
905 id SERIAL PRIMARY KEY,
906 fieldset INT NOT NULL REFERENCES action.fieldset
908 DEFERRABLE INITIALLY DEFERRED,
909 col TEXT NOT NULL, -- "field" from the idl ... the column on the table
910 val TEXT, -- value for the column ... NULL means, well, NULL
911 CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
915 -- represents a circ chain summary
916 CREATE TYPE action.circ_chain_summary AS (
918 start_time TIMESTAMP WITH TIME ZONE,
919 checkout_workstation TEXT,
920 last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
921 last_stop_fines TEXT,
922 last_stop_fines_time TIMESTAMP WITH TIME ZONE,
923 last_renewal_workstation TEXT, -- NULL if no renewals
924 last_checkin_workstation TEXT,
925 last_checkin_time TIMESTAMP WITH TIME ZONE,
926 last_checkin_scan_time TIMESTAMP WITH TIME ZONE
930 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id BIGINT ) RETURNS SETOF action.circulation AS $$
932 tmp_circ action.circulation%ROWTYPE;
933 circ_0 action.circulation%ROWTYPE;
936 SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
938 IF tmp_circ IS NULL THEN
939 RETURN NEXT tmp_circ;
943 -- find the front of the chain
945 SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
946 IF tmp_circ IS NULL THEN
952 -- now send the circs to the caller, oldest to newest
955 IF tmp_circ IS NULL THEN
958 RETURN NEXT tmp_circ;
959 SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
963 $$ LANGUAGE 'plpgsql';
965 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id BIGINT ) RETURNS action.circ_chain_summary AS $$
969 -- first circ in the chain
970 circ_0 action.circulation%ROWTYPE;
972 -- last circ in the chain
973 circ_n action.circulation%ROWTYPE;
975 -- circ chain under construction
976 chain action.circ_chain_summary;
977 tmp_circ action.circulation%ROWTYPE;
981 chain.num_circs := 0;
982 FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
984 IF chain.num_circs = 0 THEN
988 chain.num_circs := chain.num_circs + 1;
992 chain.start_time := circ_0.xact_start;
993 chain.last_stop_fines := circ_n.stop_fines;
994 chain.last_stop_fines_time := circ_n.stop_fines_time;
995 chain.last_checkin_time := circ_n.checkin_time;
996 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
997 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
998 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
1000 IF chain.num_circs > 1 THEN
1001 chain.last_renewal_time := circ_n.xact_start;
1002 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
1008 $$ LANGUAGE 'plpgsql';
1010 -- same as action.circ_chain, but returns action.all_circulation
1011 -- rows which may include aged circulations.
1012 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
1013 RETURNS SETOF action.all_circulation_slim AS $$
1015 tmp_circ action.all_circulation_slim%ROWTYPE;
1016 circ_0 action.all_circulation_slim%ROWTYPE;
1019 SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
1021 IF tmp_circ IS NULL THEN
1022 RETURN NEXT tmp_circ;
1026 -- find the front of the chain
1028 SELECT INTO tmp_circ * FROM action.all_circulation_slim
1029 WHERE id = tmp_circ.parent_circ;
1030 IF tmp_circ IS NULL THEN
1036 -- now send the circs to the caller, oldest to newest
1039 IF tmp_circ IS NULL THEN
1042 RETURN NEXT tmp_circ;
1043 SELECT INTO tmp_circ * FROM action.all_circulation_slim
1044 WHERE parent_circ = tmp_circ.id;
1048 $$ LANGUAGE 'plpgsql';
1050 -- same as action.summarize_circ_chain, but returns data collected
1051 -- from action.all_circulation, which may include aged circulations.
1052 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
1053 (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
1057 -- first circ in the chain
1058 circ_0 action.all_circulation_slim%ROWTYPE;
1060 -- last circ in the chain
1061 circ_n action.all_circulation_slim%ROWTYPE;
1063 -- circ chain under construction
1064 chain action.circ_chain_summary;
1065 tmp_circ action.all_circulation_slim%ROWTYPE;
1069 chain.num_circs := 0;
1070 FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
1072 IF chain.num_circs = 0 THEN
1076 chain.num_circs := chain.num_circs + 1;
1080 chain.start_time := circ_0.xact_start;
1081 chain.last_stop_fines := circ_n.stop_fines;
1082 chain.last_stop_fines_time := circ_n.stop_fines_time;
1083 chain.last_checkin_time := circ_n.checkin_time;
1084 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
1085 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
1086 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
1088 IF chain.num_circs > 1 THEN
1089 chain.last_renewal_time := circ_n.xact_start;
1090 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
1096 $$ LANGUAGE 'plpgsql';
1098 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
1100 h action.hold_request%ROWTYPE;
1103 usr_view_count actor.usr_setting%ROWTYPE;
1104 usr_view_age actor.usr_setting%ROWTYPE;
1105 usr_view_start actor.usr_setting%ROWTYPE;
1107 SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
1108 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
1109 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
1113 FROM action.hold_request
1115 AND fulfillment_time IS NULL
1116 AND cancel_time IS NULL
1117 ORDER BY request_time DESC
1122 IF usr_view_start.value IS NULL THEN
1126 IF usr_view_age.value IS NOT NULL THEN
1127 -- User opted in and supplied a retention age
1128 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
1129 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1131 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
1135 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1138 IF usr_view_count.value IS NOT NULL THEN
1139 view_count := oils_json_to_text(usr_view_count.value)::INT;
1144 -- show some fulfilled/canceled holds
1147 FROM action.hold_request
1149 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
1150 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
1151 ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
1159 $func$ LANGUAGE PLPGSQL;
1161 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
1163 org_keep_age INTERVAL;
1164 org_use_last BOOL = false;
1165 org_age_is_min BOOL = false;
1171 circ_chain_head action.circulation%ROWTYPE;
1172 circ_chain_tail action.circulation%ROWTYPE;
1177 last_finished TIMESTAMP WITH TIME ZONE;
1182 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
1184 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
1185 IF org_keep_count IS NULL THEN
1186 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
1189 SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
1190 SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
1192 -- First, find copies with more than keep_count non-renewal circs
1195 COUNT(*) AS total_real_circs
1196 FROM action.circulation
1197 WHERE parent_circ IS NULL
1198 AND xact_finish IS NOT NULL
1199 GROUP BY target_copy
1200 HAVING COUNT(*) > org_keep_count
1202 -- And, for those, select circs that are finished and older than keep_age
1203 FOR circ_chain_head IN
1204 -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
1205 -- The outer query then uses that information to skip the most recent set the library wants to keep
1206 -- End result is we don't care what order they come out in, as they are all potentials for deletion.
1207 SELECT ac.* FROM action.circulation ac JOIN (
1208 SELECT rank() OVER (ORDER BY xact_start DESC), ac.id
1209 FROM action.circulation ac
1210 WHERE ac.target_copy = target_acp.target_copy
1211 AND ac.parent_circ IS NULL
1212 ORDER BY ac.xact_start ) ranked USING (id)
1213 WHERE ranked.rank > org_keep_count
1216 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
1217 SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id);
1218 CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
1220 IF NOT org_use_last THEN
1221 last_finished := circ_chain_tail.xact_finish;
1224 keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
1226 IF org_age_is_min THEN
1227 keep_age := GREATEST( keep_age, org_keep_age );
1230 CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
1232 -- We've passed the purging tests, purge the circ chain starting at the end
1233 -- A trigger should auto-purge the rest of the chain.
1234 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
1236 count_purged := count_purged + 1;
1241 return count_purged;
1243 $func$ LANGUAGE PLPGSQL;
1245 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
1247 current_hold RECORD;
1253 user_start TIMESTAMPTZ;
1258 SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
1259 SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
1260 SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
1263 rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
1264 cgf_cs.value::INTERVAL as cgf_cs,
1267 action.hold_request ahr
1268 LEFT JOIN config.global_flag cgf_cs ON (ahr.cancel_cause IS NOT NULL AND cgf_cs.name = 'history.hold.retention_age_canceled_' || ahr.cancel_cause AND cgf_cs.enabled)
1270 (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
1272 IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
1273 prev_usr := current_hold.usr;
1274 SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
1275 SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
1276 SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
1277 IF user_start IS NOT NULL THEN
1278 user_age := LEAST(user_age, AGE(NOW(), user_start));
1280 IF user_count IS NULL THEN
1281 user_count := 1000; -- Assumption based on the user visible holds routine
1284 -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
1285 IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
1288 IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
1292 -- User keep age needs combining with count. If too old AND within the count, keep!
1293 IF user_start IS NOT NULL AND COALESCE(current_hold.fulfillment_time, current_hold.cancel_time) > NOW() - user_age AND current_hold.rank <= user_count THEN
1297 -- All checks should have passed, delete!
1298 DELETE FROM action.hold_request WHERE id = current_hold.id;
1299 purged_holds := purged_holds + 1;
1301 RETURN purged_holds;
1303 $func$ LANGUAGE plpgsql;
1305 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1306 fieldset_id IN INT, -- id from action.fieldset
1307 table_name IN TEXT, -- table to be updated
1308 pkey_name IN TEXT, -- name of primary key column in that table
1309 query IN TEXT -- query constructed by qstore (for query-based
1310 -- fieldsets only; otherwise null
1326 fs_obj action.fieldset%ROWTYPE;
1327 fs_group action.fieldset_group%ROWTYPE;
1331 IF fieldset_id IS NULL THEN
1332 RETURN 'Fieldset ID parameter is NULL';
1334 IF table_name IS NULL THEN
1335 RETURN 'Table name parameter is NULL';
1337 IF pkey_name IS NULL THEN
1338 RETURN 'Primary key name parameter is NULL';
1343 quote_literal( pkey_value )
1353 -- Build the WHERE clause. This differs according to whether it's a
1354 -- single-row fieldset or a query-based fieldset.
1356 IF query IS NULL AND fs_pkey_value IS NULL THEN
1357 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1358 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1359 fs_query := rtrim( query, ';' );
1360 where_clause := 'WHERE ' || pkey_name || ' IN ( '
1361 || fs_query || ' )';
1362 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
1363 where_clause := 'WHERE ' || pkey_name || ' = ';
1364 IF pkey_name = 'id' THEN
1365 where_clause := where_clause || fs_pkey_value;
1366 ELSIF pkey_name = 'code' THEN
1367 where_clause := where_clause || quote_literal(fs_pkey_value);
1369 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1371 ELSE -- both are not null
1372 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1375 IF fs_status IS NULL THEN
1376 RETURN 'No fieldset found for id = ' || fieldset_id;
1377 ELSIF fs_status = 'APPLIED' THEN
1378 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1381 SELECT * INTO fs_obj FROM action.fieldset WHERE id = fieldset_id;
1382 SELECT * INTO fs_group FROM action.fieldset_group WHERE id = fs_obj.fieldset_group;
1384 IF fs_group.can_rollback THEN
1385 -- This is part of a non-rollback group. We need to record the current values for future rollback.
1387 INSERT INTO action.fieldset_group (can_rollback, name, creator, owning_lib, container, container_type)
1388 VALUES (FALSE, 'ROLLBACK: '|| fs_group.name, fs_group.creator, fs_group.owning_lib, fs_group.container, fs_group.container_type);
1390 fsg_id := CURRVAL('action.fieldset_group_id_seq');
1392 FOR rb_row IN EXECUTE 'SELECT * FROM ' || table_name || ' ' || where_clause LOOP
1393 IF pkey_name = 'id' THEN
1394 fs_pkey_value := rb_row.id;
1395 ELSIF pkey_name = 'code' THEN
1396 fs_pkey_value := rb_row.code;
1398 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1400 INSERT INTO action.fieldset (fieldset_group,owner,owning_lib,status,classname,name,pkey_value)
1401 VALUES (fsg_id, fs_obj.owner, fs_obj.owning_lib, 'PENDING', fs_obj.classname, fs_obj.name || ' ROLLBACK FOR ' || fs_pkey_value, fs_pkey_value);
1403 fs_id := CURRVAL('action.fieldset_id_seq');
1407 FROM action.fieldset_col_val
1408 WHERE fieldset = fieldset_id
1410 EXECUTE 'INSERT INTO action.fieldset_col_val (fieldset, col, val) ' ||
1411 'SELECT '|| fs_id || ', '||quote_literal(cv.col)||', '||cv.col||' FROM '||table_name||' WHERE '||pkey_name||' = '||fs_pkey_value;
1416 statement := 'UPDATE ' || table_name || ' SET';
1422 FROM action.fieldset_col_val
1423 WHERE fieldset = fieldset_id
1425 statement := statement || sep || ' ' || cv.col
1426 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1431 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1433 statement := statement || ' ' || where_clause;
1436 -- Execute the update
1440 GET DIAGNOSTICS update_count = ROW_COUNT;
1442 IF update_count = 0 THEN
1443 RAISE data_exception;
1446 IF fsg_id IS NOT NULL THEN
1447 UPDATE action.fieldset_group SET rollback_group = fsg_id WHERE id = fs_group.id;
1450 IF fs_group.id IS NOT NULL THEN
1451 UPDATE action.fieldset_group SET complete_time = now() WHERE id = fs_group.id;
1454 UPDATE action.fieldset SET status = 'APPLIED', applied_time = now() WHERE id = fieldset_id;
1456 EXCEPTION WHEN data_exception THEN
1457 msg := 'No eligible rows found for fieldset ' || fieldset_id;
1458 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1465 EXCEPTION WHEN OTHERS THEN
1466 msg := 'Unable to apply fieldset ' || fieldset_id || ': ' || sqlerrm;
1467 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1471 $$ LANGUAGE plpgsql;
1473 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
1474 Applies a specified fieldset, using a supplied table name and primary
1475 key name. The query parameter should be non-null only for
1476 query-based fieldsets.
1478 Returns NULL if successful, or an error message if not.
1481 CREATE OR REPLACE FUNCTION action.copy_calculated_proximity(
1488 ) RETURNS NUMERIC AS $f$
1490 baseline_prox NUMERIC;
1491 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
1494 -- First, gather the baseline proximity of "here" to pickup lib
1495 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = vacp_cl AND to_org = pickup;
1497 -- Find any absolute adjustments, and set the baseline prox to that
1498 SELECT adj.* INTO aoupa
1499 FROM actor.org_unit_proximity_adjustment adj
1500 LEFT JOIN actor.org_unit_ancestors_distance(vacp_cl) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1501 LEFT JOIN actor.org_unit_ancestors_distance(vacn_ol) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1502 LEFT JOIN actor.org_unit_ancestors_distance(vacl_ol) acl_ol ON (acl_ol.id = adj.copy_location)
1503 LEFT JOIN actor.org_unit_ancestors_distance(pickup) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1504 LEFT JOIN actor.org_unit_ancestors_distance(request) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1505 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = vacp_cm) AND
1506 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1507 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1508 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1509 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1510 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1511 absolute_adjustment AND
1512 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1514 COALESCE(acp_cl.distance,999)
1515 + COALESCE(acn_ol.distance,999)
1516 + COALESCE(acl_ol.distance,999)
1517 + COALESCE(ahr_pl.distance,999)
1518 + COALESCE(ahr_rl.distance,999),
1523 baseline_prox := aoupa.prox_adjustment;
1526 -- Now find any relative adjustments, and change the baseline prox based on them
1529 FROM actor.org_unit_proximity_adjustment adj
1530 LEFT JOIN actor.org_unit_ancestors_distance(vacp_cl) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1531 LEFT JOIN actor.org_unit_ancestors_distance(vacn_ol) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1532 LEFT JOIN actor.org_unit_ancestors_distance(vacl_ol) acl_ol ON (acn_ol.id = adj.copy_location)
1533 LEFT JOIN actor.org_unit_ancestors_distance(pickup) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1534 LEFT JOIN actor.org_unit_ancestors_distance(request) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1535 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = vacp_cm) AND
1536 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1537 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1538 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1539 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1540 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1541 NOT absolute_adjustment AND
1542 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1544 baseline_prox := baseline_prox + aoupa.prox_adjustment;
1547 RETURN baseline_prox;
1549 $f$ LANGUAGE PLPGSQL;
1551 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
1554 copy_context_ou INT DEFAULT NULL
1555 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
1556 -- support an "ahprox" measurement: adjust prox between copy circ lib and
1557 -- hold request lib, but I'm unsure whether to use this theoretical
1558 -- argument only in the baseline calculation or later in the other
1559 -- queries in this function.
1560 ) RETURNS NUMERIC AS $f$
1562 ahr action.hold_request%ROWTYPE;
1563 acp asset.copy%ROWTYPE;
1564 acn asset.call_number%ROWTYPE;
1565 acl asset.copy_location%ROWTYPE;
1570 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
1571 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
1572 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
1573 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
1575 IF copy_context_ou IS NULL THEN
1576 copy_context_ou := acp.circ_lib;
1579 SELECT action.copy_calculated_proximity(
1590 $f$ LANGUAGE PLPGSQL;
1592 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$
1594 NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy);
1597 $f$ LANGUAGE PLPGSQL;
1599 CREATE TRIGGER hold_copy_proximity_update_tgr BEFORE INSERT OR UPDATE ON action.hold_copy_map FOR EACH ROW EXECUTE PROCEDURE action.hold_copy_calculated_proximity_update ();
1601 CREATE TABLE action.usr_circ_history (
1602 id BIGSERIAL PRIMARY KEY,
1603 usr INTEGER NOT NULL REFERENCES actor.usr(id)
1604 DEFERRABLE INITIALLY DEFERRED,
1605 xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1606 target_copy BIGINT NOT NULL, -- asset.copy.id / serial.unit.id
1607 due_date TIMESTAMP WITH TIME ZONE NOT NULL,
1608 checkin_time TIMESTAMP WITH TIME ZONE,
1609 source_circ BIGINT REFERENCES action.circulation(id)
1610 ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
1613 CREATE INDEX action_usr_circ_history_usr_idx ON action.usr_circ_history ( usr );
1614 CREATE INDEX action_usr_circ_history_source_circ_idx ON action.usr_circ_history ( source_circ );
1616 CREATE TRIGGER action_usr_circ_history_target_copy_trig
1617 AFTER INSERT OR UPDATE ON action.usr_circ_history
1618 FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
1620 CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history()
1621 RETURNS TRIGGER AS $FUNK$
1627 -- Any retention value signifies history is enabled.
1628 -- This assumes that clearing these values via external
1629 -- process deletes the action.usr_circ_history rows.
1630 -- TODO: replace these settings w/ a single bool setting?
1631 PERFORM 1 FROM actor.usr_setting
1632 WHERE usr = NEW.usr AND value IS NOT NULL AND name IN (
1633 'history.circ.retention_age',
1634 'history.circ.retention_start'
1641 IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN
1642 -- Starting a new circulation. Insert the history row.
1643 INSERT INTO action.usr_circ_history
1644 (usr, xact_start, target_copy, due_date, source_circ)
1656 -- find the first and last circs in the circ chain
1657 -- for the currently modified circ.
1658 FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP
1659 IF first_circ IS NULL THEN
1660 first_circ := cur_circ;
1663 -- Allow the loop to continue so that at as the loop
1664 -- completes cur_circ points to the final circulation.
1667 IF NEW.id <> cur_circ THEN
1668 -- Modifying an intermediate circ. Ignore it.
1672 -- Update the due_date/checkin_time on the history row if the current
1673 -- circ is the last circ in the chain and an update is warranted.
1675 UPDATE action.usr_circ_history
1677 due_date = NEW.due_date,
1678 checkin_time = NEW.checkin_time
1680 source_circ = first_circ
1682 due_date <> NEW.due_date OR (
1683 (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR
1684 (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR
1685 (checkin_time <> NEW.checkin_time)
1690 $FUNK$ LANGUAGE PLPGSQL;
1692 CREATE TRIGGER maintain_usr_circ_history_tgr
1693 AFTER INSERT OR UPDATE ON action.circulation
1694 FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history();
1696 CREATE OR REPLACE VIEW action.all_circulation_combined_types AS
1697 SELECT acirc.id AS id,
1702 ac_acirc.circ_modifier AS item_type,
1703 'regular_circ'::text AS circ_type
1704 FROM action.circulation acirc,
1706 WHERE acirc.target_copy = ac_acirc.id
1708 SELECT ancc.id::BIGINT AS id,
1709 ancc.circ_time AS xact_start,
1711 ancc.staff AS circ_staff,
1712 ancc.circ_time AS create_time,
1713 cnct_ancc.name AS item_type,
1714 'non-cat_circ'::text AS circ_type
1715 FROM action.non_cataloged_circulation ancc,
1716 config.non_cataloged_type cnct_ancc
1717 WHERE ancc.item_type = cnct_ancc.id
1719 SELECT aihu.id::BIGINT AS id,
1720 aihu.use_time AS xact_start,
1721 aihu.org_unit AS circ_lib,
1722 aihu.staff AS circ_staff,
1723 aihu.use_time AS create_time,
1724 ac_aihu.circ_modifier AS item_type,
1725 'in-house_use'::text AS circ_type
1726 FROM action.in_house_use aihu,
1728 WHERE aihu.item = ac_aihu.id
1730 SELECT ancihu.id::BIGINT AS id,
1731 ancihu.use_time AS xact_start,
1732 ancihu.org_unit AS circ_lib,
1733 ancihu.staff AS circ_staff,
1734 ancihu.use_time AS create_time,
1735 cnct_ancihu.name AS item_type,
1736 'non-cat-in-house_use'::text AS circ_type
1737 FROM action.non_cat_in_house_use ancihu,
1738 config.non_cataloged_type cnct_ancihu
1739 WHERE ancihu.item_type = cnct_ancihu.id
1741 SELECT aacirc.id AS id,
1746 ac_aacirc.circ_modifier AS item_type,
1747 'aged_circ'::text AS circ_type
1748 FROM action.aged_circulation aacirc,
1749 asset.copy ac_aacirc
1750 WHERE aacirc.target_copy = ac_aacirc.id;
1752 CREATE TABLE action.curbside (
1753 id SERIAL PRIMARY KEY,
1754 patron INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1755 org INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1758 stage_staff INT REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1759 arrival TIMESTAMPTZ,
1760 delivered TIMESTAMPTZ,
1761 delivery_staff INT REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1765 CREATE TABLE action.batch_hold_event (
1766 id SERIAL PRIMARY KEY,
1767 staff INT NOT NULL REFERENCES actor.usr (id) ON UPDATE CASCADE ON DELETE CASCADE,
1768 bucket INT NOT NULL REFERENCES container.user_bucket (id) ON UPDATE CASCADE ON DELETE CASCADE,
1769 target INT NOT NULL,
1770 hold_type TEXT NOT NULL DEFAULT 'T', -- maybe different hold types in the future...
1771 run_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1772 cancelled TIMESTAMP WITH TIME ZONE
1775 CREATE TABLE action.batch_hold_event_map (
1776 id SERIAL PRIMARY KEY,
1777 batch_hold_event INT NOT NULL REFERENCES action.batch_hold_event (id) ON UPDATE CASCADE ON DELETE CASCADE,
1778 hold INT NOT NULL REFERENCES action.hold_request (id) ON UPDATE CASCADE ON DELETE CASCADE