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);
367 -- Break the link with the user in action_trigger.event (warning: event_output may essentially have this information)
369 action_trigger.event e
373 action.all_circulation c
376 AND e.context_user = c.usr
380 FROM action_trigger.event_definition
381 WHERE hook in (SELECT key FROM action_trigger.hook WHERE core_type = 'circ')
387 $$ LANGUAGE 'plpgsql';
389 CREATE TRIGGER action_circulation_aging_tgr
390 BEFORE DELETE ON action.circulation
392 EXECUTE PROCEDURE action.age_circ_on_delete ();
395 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
398 -- Having deleted a renewal, we can delete the original circulation (or a previous
399 -- renewal, if that's what parent_circ is pointing to). That deletion will trigger
400 -- deletion of any prior parents, etc. recursively.
402 IF OLD.parent_circ IS NOT NULL THEN
403 DELETE FROM action.circulation
404 WHERE id = OLD.parent_circ;
409 $$ LANGUAGE 'plpgsql';
411 CREATE TRIGGER age_parent_circ
412 AFTER DELETE ON action.circulation
414 EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
417 CREATE OR REPLACE VIEW action.open_circulation AS
419 FROM action.circulation
420 WHERE checkin_time IS NULL
424 CREATE OR REPLACE VIEW action.billable_circulations AS
426 FROM action.circulation
427 WHERE xact_finish IS NULL;
429 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
431 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
432 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
433 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
435 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
436 UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
438 IF NEW.stop_fines = 'LOST' THEN
439 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
444 $$ LANGUAGE 'plpgsql';
445 CREATE TRIGGER action_circulation_stop_fines_tgr
446 BEFORE UPDATE ON action.circulation
448 EXECUTE PROCEDURE action.circulation_claims_returned ();
450 CREATE TABLE action.hold_request_cancel_cause (
451 id SERIAL PRIMARY KEY,
453 manual BOOL NOT NULL DEFAULT FALSE
456 CREATE TABLE action.hold_request (
457 id SERIAL PRIMARY KEY,
458 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
459 capture_time TIMESTAMP WITH TIME ZONE,
460 fulfillment_time TIMESTAMP WITH TIME ZONE,
461 checkin_time TIMESTAMP WITH TIME ZONE,
462 return_time TIMESTAMP WITH TIME ZONE,
463 prev_check_time TIMESTAMP WITH TIME ZONE,
464 expire_time TIMESTAMP WITH TIME ZONE,
465 cancel_time TIMESTAMP WITH TIME ZONE,
466 cancel_cause INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
468 target BIGINT NOT NULL, -- see hold_type
469 current_copy BIGINT, -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.unit now...
470 fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
471 fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
472 request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
473 requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
474 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
475 selection_ou INT NOT NULL,
476 selection_depth INT NOT NULL DEFAULT 0,
477 pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
478 hold_type TEXT REFERENCES config.hold_type (hold_type) DEFERRABLE INITIALLY DEFERRED,
479 holdable_formats TEXT,
481 email_notify BOOL NOT NULL DEFAULT FALSE,
483 sms_carrier INT REFERENCES config.sms_carrier (id),
484 frozen BOOL NOT NULL DEFAULT FALSE,
485 thaw_date TIMESTAMP WITH TIME ZONE,
486 shelf_time TIMESTAMP WITH TIME ZONE,
488 mint_condition BOOL NOT NULL DEFAULT TRUE,
489 shelf_expire_time TIMESTAMPTZ,
490 current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
491 behind_desk BOOLEAN NOT NULL DEFAULT FALSE,
492 hopeless_date TIMESTAMP WITH TIME ZONE
494 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
496 OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
500 CREATE OR REPLACE FUNCTION action.hold_request_clear_map () RETURNS TRIGGER AS $$
502 DELETE FROM action.hold_copy_map WHERE hold = NEW.id;
507 CREATE TRIGGER hold_request_clear_map_tgr
508 AFTER UPDATE ON action.hold_request
511 (NEW.cancel_time IS NOT NULL AND OLD.cancel_time IS NULL)
512 OR (NEW.fulfillment_time IS NOT NULL AND OLD.fulfillment_time IS NULL)
514 EXECUTE PROCEDURE action.hold_request_clear_map();
516 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
517 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
518 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
519 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
520 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
521 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
522 CREATE INDEX hold_request_requestor_idx ON action.hold_request ( requestor );
523 CREATE INDEX hold_request_open_idx ON action.hold_request (id) WHERE cancel_time IS NULL AND fulfillment_time IS NULL;
524 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;
525 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;
526 CREATE INDEX hold_request_copy_capture_time_idx ON action.hold_request (current_copy,capture_time);
527 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);
528 CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
529 CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);
530 CREATE INDEX hold_request_hopeless_date_idx ON action.hold_request (hopeless_date);
532 CREATE TABLE action.hold_request_note (
534 id BIGSERIAL PRIMARY KEY,
535 hold BIGINT NOT NULL REFERENCES action.hold_request (id)
537 DEFERRABLE INITIALLY DEFERRED,
540 slip BOOL NOT NULL DEFAULT FALSE,
541 pub BOOL NOT NULL DEFAULT FALSE,
542 staff BOOL NOT NULL DEFAULT FALSE -- created by staff
545 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
548 CREATE TABLE action.hold_notification (
549 id SERIAL PRIMARY KEY,
550 hold INT NOT NULL REFERENCES action.hold_request (id)
552 DEFERRABLE INITIALLY DEFERRED,
553 notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
554 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
555 method TEXT NOT NULL, -- email address or phone number
558 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
559 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
561 CREATE TABLE action.hold_copy_map (
562 id BIGSERIAL PRIMARY KEY,
563 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
564 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
566 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
568 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
569 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
571 CREATE OR REPLACE FUNCTION
572 action.hold_request_regen_copy_maps(
573 hold_id INTEGER, copy_ids INTEGER[]) RETURNS VOID AS $$
574 DELETE FROM action.hold_copy_map WHERE hold = $1;
575 INSERT INTO action.hold_copy_map (hold, target_copy) SELECT DISTINCT $1, UNNEST($2);
578 CREATE TABLE action.transit_copy (
579 id SERIAL PRIMARY KEY,
580 source_send_time TIMESTAMP WITH TIME ZONE,
581 dest_recv_time TIMESTAMP WITH TIME ZONE,
582 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
583 source INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
584 dest INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
585 prev_hop INT REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
586 copy_status INT NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
587 persistant_transfer BOOL NOT NULL DEFAULT FALSE,
588 prev_dest INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
589 cancel_time TIMESTAMP WITH TIME ZONE
591 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
592 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
593 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
594 CREATE INDEX active_transit_for_copy ON action.transit_copy (target_copy)
595 WHERE dest_recv_time IS NULL AND cancel_time IS NULL;
597 -- Check for duplicate transits across all transit types
598 CREATE OR REPLACE FUNCTION action.copy_transit_is_unique()
599 RETURNS TRIGGER AS $func$
601 PERFORM * FROM action.transit_copy
602 WHERE target_copy = NEW.target_copy
603 AND dest_recv_time IS NULL
604 AND cancel_time IS NULL;
607 RAISE EXCEPTION 'Copy id=% is already in transit', NEW.target_copy;
611 $func$ LANGUAGE PLPGSQL STABLE;
613 CREATE CONSTRAINT TRIGGER transit_copy_is_unique_check
614 AFTER INSERT ON action.transit_copy
615 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
617 CREATE TABLE action.hold_transit_copy (
618 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
619 ) INHERITS (action.transit_copy);
620 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
621 -- 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
622 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
623 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
624 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
625 CREATE INDEX hold_transit_copy_hold_idx on action.hold_transit_copy (hold);
627 CREATE CONSTRAINT TRIGGER hold_transit_copy_is_unique_check
628 AFTER INSERT ON action.hold_transit_copy
629 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
632 CREATE TABLE action.unfulfilled_hold_list (
633 id BIGSERIAL PRIMARY KEY,
634 current_copy BIGINT NOT NULL,
636 circ_lib INT NOT NULL,
637 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
639 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
641 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
645 FROM action.unfulfilled_hold_list u
646 JOIN asset.copy c ON (c.id = u.current_copy)
649 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
652 FROM action.unfulfilled_hold_loops
655 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
657 FROM action.unfulfilled_hold_loops l
658 JOIN action.unfulfilled_hold_min_loop m USING (hold)
659 WHERE l.count = m.min;
661 CREATE VIEW action.unfulfilled_hold_max_loop AS
664 FROM action.unfulfilled_hold_loops
668 CREATE TABLE action.aged_hold_request (
670 usr_home_ou INT NOT NULL,
671 usr_profile INT NOT NULL,
673 staff_placed BOOLEAN NOT NULL,
674 LIKE action.hold_request
676 ALTER TABLE action.aged_hold_request
677 ADD PRIMARY KEY (id),
679 DROP COLUMN requestor,
680 DROP COLUMN sms_carrier,
681 ALTER COLUMN phone_notify TYPE BOOLEAN
682 USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
683 ALTER COLUMN sms_notify TYPE BOOLEAN
684 USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
685 ALTER COLUMN phone_notify SET NOT NULL,
686 ALTER COLUMN sms_notify SET NOT NULL;
687 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
688 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
689 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
690 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
692 CREATE OR REPLACE VIEW action.all_hold_request AS
694 COALESCE(a.post_code, b.post_code) AS usr_post_code,
695 p.home_ou AS usr_home_ou,
696 p.profile AS usr_profile,
697 EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
698 CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
702 ahr.fulfillment_time,
712 ahr.fulfillment_staff,
719 ahr.holdable_formats,
721 WHEN ahr.phone_notify IS NULL THEN FALSE
722 WHEN ahr.phone_notify = '' THEN FALSE
727 WHEN ahr.sms_notify IS NULL THEN FALSE
728 WHEN ahr.sms_notify = '' THEN FALSE
736 ahr.shelf_expire_time,
737 ahr.current_shelf_lib,
739 FROM action.hold_request ahr
740 JOIN actor.usr p ON (ahr.usr = p.id)
741 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
742 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
782 FROM action.aged_hold_request;
784 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
787 -- Archive a copy of the old row to action.aged_hold_request
789 INSERT INTO action.aged_hold_request
865 FROM action.all_hold_request WHERE id = OLD.id;
869 $$ LANGUAGE 'plpgsql';
871 CREATE TRIGGER action_hold_request_aging_tgr
872 BEFORE DELETE ON action.hold_request
874 EXECUTE PROCEDURE action.age_hold_on_delete ();
876 CREATE TABLE action.fieldset_group (
877 id SERIAL PRIMARY KEY,
879 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
880 complete_time TIMESTAMPTZ,
881 container INT, -- Points to a container of some type ...
882 container_type TEXT, -- One of 'biblio_record_entry', 'user', 'call_number', 'copy'
883 can_rollback BOOL DEFAULT TRUE,
884 rollback_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
885 rollback_time TIMESTAMPTZ,
886 creator INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
887 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
890 CREATE TABLE action.fieldset (
891 id SERIAL PRIMARY KEY,
892 fieldset_group INT REFERENCES action.fieldset_group (id)
893 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
894 owner INT NOT NULL REFERENCES actor.usr (id)
895 DEFERRABLE INITIALLY DEFERRED,
896 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
897 DEFERRABLE INITIALLY DEFERRED,
899 CONSTRAINT valid_status CHECK ( status in
900 ( 'PENDING', 'APPLIED', 'ERROR' )),
901 creation_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
902 scheduled_time TIMESTAMPTZ,
903 applied_time TIMESTAMPTZ,
904 classname TEXT NOT NULL, -- an IDL class name
907 stored_query INT REFERENCES query.stored_query (id)
908 DEFERRABLE INITIALLY DEFERRED,
910 CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
911 CONSTRAINT fieldset_one_or_the_other CHECK (
912 (stored_query IS NOT NULL AND pkey_value IS NULL) OR
913 (pkey_value IS NOT NULL AND stored_query IS NULL)
915 -- the CHECK constraint means we can update the fields for a single
916 -- row without all the extra overhead involved in a query
919 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
920 CREATE INDEX action_owner_idx ON action.fieldset( owner );
923 CREATE TABLE action.fieldset_col_val (
924 id SERIAL PRIMARY KEY,
925 fieldset INT NOT NULL REFERENCES action.fieldset
927 DEFERRABLE INITIALLY DEFERRED,
928 col TEXT NOT NULL, -- "field" from the idl ... the column on the table
929 val TEXT, -- value for the column ... NULL means, well, NULL
930 CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
934 -- represents a circ chain summary
935 CREATE TYPE action.circ_chain_summary AS (
937 start_time TIMESTAMP WITH TIME ZONE,
938 checkout_workstation TEXT,
939 last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
940 last_stop_fines TEXT,
941 last_stop_fines_time TIMESTAMP WITH TIME ZONE,
942 last_renewal_workstation TEXT, -- NULL if no renewals
943 last_checkin_workstation TEXT,
944 last_checkin_time TIMESTAMP WITH TIME ZONE,
945 last_checkin_scan_time TIMESTAMP WITH TIME ZONE
949 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id BIGINT ) RETURNS SETOF action.circulation AS $$
951 tmp_circ action.circulation%ROWTYPE;
952 circ_0 action.circulation%ROWTYPE;
955 SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
957 IF tmp_circ IS NULL THEN
958 RETURN NEXT tmp_circ;
962 -- find the front of the chain
964 SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
965 IF tmp_circ IS NULL THEN
971 -- now send the circs to the caller, oldest to newest
974 IF tmp_circ IS NULL THEN
977 RETURN NEXT tmp_circ;
978 SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
982 $$ LANGUAGE 'plpgsql';
984 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id BIGINT ) RETURNS action.circ_chain_summary AS $$
988 -- first circ in the chain
989 circ_0 action.circulation%ROWTYPE;
991 -- last circ in the chain
992 circ_n action.circulation%ROWTYPE;
994 -- circ chain under construction
995 chain action.circ_chain_summary;
996 tmp_circ action.circulation%ROWTYPE;
1000 chain.num_circs := 0;
1001 FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
1003 IF chain.num_circs = 0 THEN
1007 chain.num_circs := chain.num_circs + 1;
1011 chain.start_time := circ_0.xact_start;
1012 chain.last_stop_fines := circ_n.stop_fines;
1013 chain.last_stop_fines_time := circ_n.stop_fines_time;
1014 chain.last_checkin_time := circ_n.checkin_time;
1015 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
1016 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
1017 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
1019 IF chain.num_circs > 1 THEN
1020 chain.last_renewal_time := circ_n.xact_start;
1021 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
1027 $$ LANGUAGE 'plpgsql';
1029 -- same as action.circ_chain, but returns action.all_circulation
1030 -- rows which may include aged circulations.
1031 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
1032 RETURNS SETOF action.all_circulation_slim AS $$
1034 tmp_circ action.all_circulation_slim%ROWTYPE;
1035 circ_0 action.all_circulation_slim%ROWTYPE;
1038 SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
1040 IF tmp_circ IS NULL THEN
1041 RETURN NEXT tmp_circ;
1045 -- find the front of the chain
1047 SELECT INTO tmp_circ * FROM action.all_circulation_slim
1048 WHERE id = tmp_circ.parent_circ;
1049 IF tmp_circ IS NULL THEN
1055 -- now send the circs to the caller, oldest to newest
1058 IF tmp_circ IS NULL THEN
1061 RETURN NEXT tmp_circ;
1062 SELECT INTO tmp_circ * FROM action.all_circulation_slim
1063 WHERE parent_circ = tmp_circ.id;
1067 $$ LANGUAGE 'plpgsql';
1069 -- same as action.summarize_circ_chain, but returns data collected
1070 -- from action.all_circulation, which may include aged circulations.
1071 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
1072 (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
1076 -- first circ in the chain
1077 circ_0 action.all_circulation_slim%ROWTYPE;
1079 -- last circ in the chain
1080 circ_n action.all_circulation_slim%ROWTYPE;
1082 -- circ chain under construction
1083 chain action.circ_chain_summary;
1084 tmp_circ action.all_circulation_slim%ROWTYPE;
1088 chain.num_circs := 0;
1089 FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
1091 IF chain.num_circs = 0 THEN
1095 chain.num_circs := chain.num_circs + 1;
1099 chain.start_time := circ_0.xact_start;
1100 chain.last_stop_fines := circ_n.stop_fines;
1101 chain.last_stop_fines_time := circ_n.stop_fines_time;
1102 chain.last_checkin_time := circ_n.checkin_time;
1103 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
1104 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
1105 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
1107 IF chain.num_circs > 1 THEN
1108 chain.last_renewal_time := circ_n.xact_start;
1109 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
1115 $$ LANGUAGE 'plpgsql';
1117 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
1119 h action.hold_request%ROWTYPE;
1122 usr_view_count actor.usr_setting%ROWTYPE;
1123 usr_view_age actor.usr_setting%ROWTYPE;
1124 usr_view_start actor.usr_setting%ROWTYPE;
1126 SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
1127 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
1128 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
1132 FROM action.hold_request
1134 AND fulfillment_time IS NULL
1135 AND cancel_time IS NULL
1136 ORDER BY request_time DESC
1141 IF usr_view_start.value IS NULL THEN
1145 IF usr_view_age.value IS NOT NULL THEN
1146 -- User opted in and supplied a retention age
1147 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
1148 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1150 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
1154 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1157 IF usr_view_count.value IS NOT NULL THEN
1158 view_count := oils_json_to_text(usr_view_count.value)::INT;
1163 -- show some fulfilled/canceled holds
1166 FROM action.hold_request
1168 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
1169 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
1170 ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
1178 $func$ LANGUAGE PLPGSQL;
1180 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
1182 org_keep_age INTERVAL;
1183 org_use_last BOOL = false;
1184 org_age_is_min BOOL = false;
1190 circ_chain_head action.circulation%ROWTYPE;
1191 circ_chain_tail action.circulation%ROWTYPE;
1196 last_finished TIMESTAMP WITH TIME ZONE;
1201 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
1203 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
1204 IF org_keep_count IS NULL THEN
1205 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
1208 SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
1209 SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
1211 -- First, find copies with more than keep_count non-renewal circs
1214 COUNT(*) AS total_real_circs
1215 FROM action.circulation
1216 WHERE parent_circ IS NULL
1217 AND xact_finish IS NOT NULL
1218 GROUP BY target_copy
1219 HAVING COUNT(*) > org_keep_count
1221 -- And, for those, select circs that are finished and older than keep_age
1222 FOR circ_chain_head IN
1223 -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
1224 -- The outer query then uses that information to skip the most recent set the library wants to keep
1225 -- End result is we don't care what order they come out in, as they are all potentials for deletion.
1226 SELECT ac.* FROM action.circulation ac JOIN (
1227 SELECT rank() OVER (ORDER BY xact_start DESC), ac.id
1228 FROM action.circulation ac
1229 WHERE ac.target_copy = target_acp.target_copy
1230 AND ac.parent_circ IS NULL
1231 ORDER BY ac.xact_start ) ranked USING (id)
1232 WHERE ranked.rank > org_keep_count
1235 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
1236 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);
1237 CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
1239 IF NOT org_use_last THEN
1240 last_finished := circ_chain_tail.xact_finish;
1243 keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
1245 IF org_age_is_min THEN
1246 keep_age := GREATEST( keep_age, org_keep_age );
1249 CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
1251 -- We've passed the purging tests, purge the circ chain starting at the end
1252 -- A trigger should auto-purge the rest of the chain.
1253 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
1255 count_purged := count_purged + 1;
1260 return count_purged;
1262 $func$ LANGUAGE PLPGSQL;
1264 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
1266 current_hold RECORD;
1272 user_start TIMESTAMPTZ;
1277 SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
1278 SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
1279 SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
1282 rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
1283 cgf_cs.value::INTERVAL as cgf_cs,
1286 action.hold_request ahr
1287 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)
1289 (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
1291 IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
1292 prev_usr := current_hold.usr;
1293 SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
1294 SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
1295 SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
1296 IF user_start IS NOT NULL THEN
1297 user_age := LEAST(user_age, AGE(NOW(), user_start));
1299 IF user_count IS NULL THEN
1300 user_count := 1000; -- Assumption based on the user visible holds routine
1303 -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
1304 IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
1307 IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
1311 -- User keep age needs combining with count. If too old AND within the count, keep!
1312 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
1316 -- All checks should have passed, delete!
1317 DELETE FROM action.hold_request WHERE id = current_hold.id;
1318 purged_holds := purged_holds + 1;
1320 RETURN purged_holds;
1322 $func$ LANGUAGE plpgsql;
1324 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1325 fieldset_id IN INT, -- id from action.fieldset
1326 table_name IN TEXT, -- table to be updated
1327 pkey_name IN TEXT, -- name of primary key column in that table
1328 query IN TEXT -- query constructed by qstore (for query-based
1329 -- fieldsets only; otherwise null
1345 fs_obj action.fieldset%ROWTYPE;
1346 fs_group action.fieldset_group%ROWTYPE;
1350 IF fieldset_id IS NULL THEN
1351 RETURN 'Fieldset ID parameter is NULL';
1353 IF table_name IS NULL THEN
1354 RETURN 'Table name parameter is NULL';
1356 IF pkey_name IS NULL THEN
1357 RETURN 'Primary key name parameter is NULL';
1362 quote_literal( pkey_value )
1372 -- Build the WHERE clause. This differs according to whether it's a
1373 -- single-row fieldset or a query-based fieldset.
1375 IF query IS NULL AND fs_pkey_value IS NULL THEN
1376 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1377 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1378 fs_query := rtrim( query, ';' );
1379 where_clause := 'WHERE ' || pkey_name || ' IN ( '
1380 || fs_query || ' )';
1381 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
1382 where_clause := 'WHERE ' || pkey_name || ' = ';
1383 IF pkey_name = 'id' THEN
1384 where_clause := where_clause || fs_pkey_value;
1385 ELSIF pkey_name = 'code' THEN
1386 where_clause := where_clause || quote_literal(fs_pkey_value);
1388 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1390 ELSE -- both are not null
1391 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1394 IF fs_status IS NULL THEN
1395 RETURN 'No fieldset found for id = ' || fieldset_id;
1396 ELSIF fs_status = 'APPLIED' THEN
1397 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1400 SELECT * INTO fs_obj FROM action.fieldset WHERE id = fieldset_id;
1401 SELECT * INTO fs_group FROM action.fieldset_group WHERE id = fs_obj.fieldset_group;
1403 IF fs_group.can_rollback THEN
1404 -- This is part of a non-rollback group. We need to record the current values for future rollback.
1406 INSERT INTO action.fieldset_group (can_rollback, name, creator, owning_lib, container, container_type)
1407 VALUES (FALSE, 'ROLLBACK: '|| fs_group.name, fs_group.creator, fs_group.owning_lib, fs_group.container, fs_group.container_type);
1409 fsg_id := CURRVAL('action.fieldset_group_id_seq');
1411 FOR rb_row IN EXECUTE 'SELECT * FROM ' || table_name || ' ' || where_clause LOOP
1412 IF pkey_name = 'id' THEN
1413 fs_pkey_value := rb_row.id;
1414 ELSIF pkey_name = 'code' THEN
1415 fs_pkey_value := rb_row.code;
1417 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1419 INSERT INTO action.fieldset (fieldset_group,owner,owning_lib,status,classname,name,pkey_value)
1420 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);
1422 fs_id := CURRVAL('action.fieldset_id_seq');
1426 FROM action.fieldset_col_val
1427 WHERE fieldset = fieldset_id
1429 EXECUTE 'INSERT INTO action.fieldset_col_val (fieldset, col, val) ' ||
1430 'SELECT '|| fs_id || ', '||quote_literal(cv.col)||', '||cv.col||' FROM '||table_name||' WHERE '||pkey_name||' = '||fs_pkey_value;
1435 statement := 'UPDATE ' || table_name || ' SET';
1441 FROM action.fieldset_col_val
1442 WHERE fieldset = fieldset_id
1444 statement := statement || sep || ' ' || cv.col
1445 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1450 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1452 statement := statement || ' ' || where_clause;
1455 -- Execute the update
1459 GET DIAGNOSTICS update_count = ROW_COUNT;
1461 IF update_count = 0 THEN
1462 RAISE data_exception;
1465 IF fsg_id IS NOT NULL THEN
1466 UPDATE action.fieldset_group SET rollback_group = fsg_id WHERE id = fs_group.id;
1469 IF fs_group.id IS NOT NULL THEN
1470 UPDATE action.fieldset_group SET complete_time = now() WHERE id = fs_group.id;
1473 UPDATE action.fieldset SET status = 'APPLIED', applied_time = now() WHERE id = fieldset_id;
1475 EXCEPTION WHEN data_exception THEN
1476 msg := 'No eligible rows found for fieldset ' || fieldset_id;
1477 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1484 EXCEPTION WHEN OTHERS THEN
1485 msg := 'Unable to apply fieldset ' || fieldset_id || ': ' || sqlerrm;
1486 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1490 $$ LANGUAGE plpgsql;
1492 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
1493 Applies a specified fieldset, using a supplied table name and primary
1494 key name. The query parameter should be non-null only for
1495 query-based fieldsets.
1497 Returns NULL if successful, or an error message if not.
1500 CREATE OR REPLACE FUNCTION action.copy_calculated_proximity(
1507 ) RETURNS NUMERIC AS $f$
1509 baseline_prox NUMERIC;
1510 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
1513 -- First, gather the baseline proximity of "here" to pickup lib
1514 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = vacp_cl AND to_org = pickup;
1516 -- Find any absolute adjustments, and set the baseline prox to that
1517 SELECT adj.* INTO aoupa
1518 FROM actor.org_unit_proximity_adjustment adj
1519 LEFT JOIN actor.org_unit_ancestors_distance(vacp_cl) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1520 LEFT JOIN actor.org_unit_ancestors_distance(vacn_ol) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1521 LEFT JOIN actor.org_unit_ancestors_distance(vacl_ol) acl_ol ON (acl_ol.id = adj.copy_location)
1522 LEFT JOIN actor.org_unit_ancestors_distance(pickup) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1523 LEFT JOIN actor.org_unit_ancestors_distance(request) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1524 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = vacp_cm) AND
1525 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1526 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1527 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1528 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1529 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1530 absolute_adjustment AND
1531 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1533 COALESCE(acp_cl.distance,999)
1534 + COALESCE(acn_ol.distance,999)
1535 + COALESCE(acl_ol.distance,999)
1536 + COALESCE(ahr_pl.distance,999)
1537 + COALESCE(ahr_rl.distance,999),
1542 baseline_prox := aoupa.prox_adjustment;
1545 -- Now find any relative adjustments, and change the baseline prox based on them
1548 FROM actor.org_unit_proximity_adjustment adj
1549 LEFT JOIN actor.org_unit_ancestors_distance(vacp_cl) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1550 LEFT JOIN actor.org_unit_ancestors_distance(vacn_ol) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1551 LEFT JOIN actor.org_unit_ancestors_distance(vacl_ol) acl_ol ON (acn_ol.id = adj.copy_location)
1552 LEFT JOIN actor.org_unit_ancestors_distance(pickup) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1553 LEFT JOIN actor.org_unit_ancestors_distance(request) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1554 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = vacp_cm) AND
1555 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1556 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1557 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1558 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1559 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1560 NOT absolute_adjustment AND
1561 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1563 baseline_prox := baseline_prox + aoupa.prox_adjustment;
1566 RETURN baseline_prox;
1568 $f$ LANGUAGE PLPGSQL;
1570 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
1573 copy_context_ou INT DEFAULT NULL
1574 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
1575 -- support an "ahprox" measurement: adjust prox between copy circ lib and
1576 -- hold request lib, but I'm unsure whether to use this theoretical
1577 -- argument only in the baseline calculation or later in the other
1578 -- queries in this function.
1579 ) RETURNS NUMERIC AS $f$
1581 ahr action.hold_request%ROWTYPE;
1582 acp asset.copy%ROWTYPE;
1583 acn asset.call_number%ROWTYPE;
1584 acl asset.copy_location%ROWTYPE;
1589 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
1590 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
1591 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
1592 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
1594 IF copy_context_ou IS NULL THEN
1595 copy_context_ou := acp.circ_lib;
1598 SELECT action.copy_calculated_proximity(
1609 $f$ LANGUAGE PLPGSQL;
1611 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$
1613 NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy);
1616 $f$ LANGUAGE PLPGSQL;
1618 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 ();
1620 CREATE TABLE action.usr_circ_history (
1621 id BIGSERIAL PRIMARY KEY,
1622 usr INTEGER NOT NULL REFERENCES actor.usr(id)
1623 DEFERRABLE INITIALLY DEFERRED,
1624 xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1625 target_copy BIGINT NOT NULL, -- asset.copy.id / serial.unit.id
1626 due_date TIMESTAMP WITH TIME ZONE NOT NULL,
1627 checkin_time TIMESTAMP WITH TIME ZONE,
1628 source_circ BIGINT REFERENCES action.circulation(id)
1629 ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
1632 CREATE INDEX action_usr_circ_history_usr_idx ON action.usr_circ_history ( usr );
1633 CREATE INDEX action_usr_circ_history_source_circ_idx ON action.usr_circ_history ( source_circ );
1635 CREATE TRIGGER action_usr_circ_history_target_copy_trig
1636 AFTER INSERT OR UPDATE ON action.usr_circ_history
1637 FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
1639 CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history()
1640 RETURNS TRIGGER AS $FUNK$
1646 -- Any retention value signifies history is enabled.
1647 -- This assumes that clearing these values via external
1648 -- process deletes the action.usr_circ_history rows.
1649 -- TODO: replace these settings w/ a single bool setting?
1650 PERFORM 1 FROM actor.usr_setting
1651 WHERE usr = NEW.usr AND value IS NOT NULL AND name IN (
1652 'history.circ.retention_age',
1653 'history.circ.retention_start'
1660 IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN
1661 -- Starting a new circulation. Insert the history row.
1662 INSERT INTO action.usr_circ_history
1663 (usr, xact_start, target_copy, due_date, source_circ)
1675 -- find the first and last circs in the circ chain
1676 -- for the currently modified circ.
1677 FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP
1678 IF first_circ IS NULL THEN
1679 first_circ := cur_circ;
1682 -- Allow the loop to continue so that at as the loop
1683 -- completes cur_circ points to the final circulation.
1686 IF NEW.id <> cur_circ THEN
1687 -- Modifying an intermediate circ. Ignore it.
1691 -- Update the due_date/checkin_time on the history row if the current
1692 -- circ is the last circ in the chain and an update is warranted.
1694 UPDATE action.usr_circ_history
1696 due_date = NEW.due_date,
1697 checkin_time = NEW.checkin_time
1699 source_circ = first_circ
1701 due_date <> NEW.due_date OR (
1702 (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR
1703 (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR
1704 (checkin_time <> NEW.checkin_time)
1709 $FUNK$ LANGUAGE PLPGSQL;
1711 CREATE TRIGGER maintain_usr_circ_history_tgr
1712 AFTER INSERT OR UPDATE ON action.circulation
1713 FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history();
1715 CREATE OR REPLACE VIEW action.all_circulation_combined_types AS
1716 SELECT acirc.id AS id,
1721 ac_acirc.circ_modifier AS item_type,
1722 'regular_circ'::text AS circ_type
1723 FROM action.circulation acirc,
1725 WHERE acirc.target_copy = ac_acirc.id
1727 SELECT ancc.id::BIGINT AS id,
1728 ancc.circ_time AS xact_start,
1730 ancc.staff AS circ_staff,
1731 ancc.circ_time AS create_time,
1732 cnct_ancc.name AS item_type,
1733 'non-cat_circ'::text AS circ_type
1734 FROM action.non_cataloged_circulation ancc,
1735 config.non_cataloged_type cnct_ancc
1736 WHERE ancc.item_type = cnct_ancc.id
1738 SELECT aihu.id::BIGINT AS id,
1739 aihu.use_time AS xact_start,
1740 aihu.org_unit AS circ_lib,
1741 aihu.staff AS circ_staff,
1742 aihu.use_time AS create_time,
1743 ac_aihu.circ_modifier AS item_type,
1744 'in-house_use'::text AS circ_type
1745 FROM action.in_house_use aihu,
1747 WHERE aihu.item = ac_aihu.id
1749 SELECT ancihu.id::BIGINT AS id,
1750 ancihu.use_time AS xact_start,
1751 ancihu.org_unit AS circ_lib,
1752 ancihu.staff AS circ_staff,
1753 ancihu.use_time AS create_time,
1754 cnct_ancihu.name AS item_type,
1755 'non-cat-in-house_use'::text AS circ_type
1756 FROM action.non_cat_in_house_use ancihu,
1757 config.non_cataloged_type cnct_ancihu
1758 WHERE ancihu.item_type = cnct_ancihu.id
1760 SELECT aacirc.id AS id,
1765 ac_aacirc.circ_modifier AS item_type,
1766 'aged_circ'::text AS circ_type
1767 FROM action.aged_circulation aacirc,
1768 asset.copy ac_aacirc
1769 WHERE aacirc.target_copy = ac_aacirc.id;
1771 CREATE TABLE action.curbside (
1772 id SERIAL PRIMARY KEY,
1773 patron INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1774 org INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1777 stage_staff INT REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1778 arrival TIMESTAMPTZ,
1779 delivered TIMESTAMPTZ,
1780 delivery_staff INT REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1784 CREATE TABLE action.batch_hold_event (
1785 id SERIAL PRIMARY KEY,
1786 staff INT NOT NULL REFERENCES actor.usr (id) ON UPDATE CASCADE ON DELETE CASCADE,
1787 bucket INT NOT NULL REFERENCES container.user_bucket (id) ON UPDATE CASCADE ON DELETE CASCADE,
1788 target INT NOT NULL,
1789 hold_type TEXT NOT NULL DEFAULT 'T', -- maybe different hold types in the future...
1790 run_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1791 cancelled TIMESTAMP WITH TIME ZONE
1794 CREATE TABLE action.batch_hold_event_map (
1795 id SERIAL PRIMARY KEY,
1796 batch_hold_event INT NOT NULL REFERENCES action.batch_hold_event (id) ON UPDATE CASCADE ON DELETE CASCADE,
1797 hold INT NOT NULL REFERENCES action.hold_request (id) ON UPDATE CASCADE ON DELETE CASCADE
1800 CREATE TABLE action.ingest_queue (
1801 id SERIAL PRIMARY KEY,
1802 created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
1803 run_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
1804 who INT REFERENCES actor.usr (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
1805 start_time TIMESTAMPTZ,
1806 end_time TIMESTAMPTZ,
1811 CREATE TABLE action.ingest_queue_entry (
1812 id BIGSERIAL PRIMARY KEY,
1813 record BIGINT NOT NULL, -- points to a record id of the appropriate record_type
1814 record_type TEXT NOT NULL,
1815 action TEXT NOT NULL,
1816 run_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
1817 state_data TEXT NOT NULL DEFAULT '',
1818 queue INT REFERENCES action.ingest_queue (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1819 override_by BIGINT REFERENCES action.ingest_queue_entry (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
1820 ingest_time TIMESTAMPTZ,
1821 fail_time TIMESTAMPTZ
1823 CREATE UNIQUE INDEX record_pending_once ON action.ingest_queue_entry (record_type,record,state_data) WHERE ingest_time IS NULL AND override_by IS NULL;
1824 CREATE INDEX entry_override_by_idx ON action.ingest_queue_entry (override_by) WHERE override_by IS NOT NULL;
1826 CREATE OR REPLACE FUNCTION action.enqueue_ingest_entry (
1828 rtype TEXT DEFAULT 'biblio',
1829 when_to_run TIMESTAMPTZ DEFAULT NOW(),
1830 queue_id INT DEFAULT NULL,
1831 ingest_action TEXT DEFAULT 'update', -- will be the most common?
1832 old_state_data TEXT DEFAULT ''
1833 ) RETURNS BOOL AS $F$
1835 new_entry action.ingest_queue_entry%ROWTYPE;
1836 prev_del_entry action.ingest_queue_entry%ROWTYPE;
1841 IF ingest_action = 'delete' THEN
1842 -- first see if there is an outstanding entry
1843 SELECT * INTO prev_del_entry
1844 FROM action.ingest_queue_entry
1845 WHERE qe.record = record_id
1846 AND qe.state_date = old_state_data
1847 AND qe.record_type = rtype
1848 AND qe.ingest_time IS NULL
1849 AND qe.override_by IS NULL;
1852 WITH existing_queue_entry_cte AS (
1853 SELECT queue_id AS queue,
1854 rtype AS record_type,
1855 record_id AS record,
1856 qe.id AS override_by,
1857 ingest_action AS action,
1859 old_state_data AS state_data
1860 FROM action.ingest_queue_entry qe
1861 JOIN action.ingest_queue q ON (qe.queue = q.id)
1862 WHERE qe.record = record_id
1863 AND q.end_time IS NULL
1864 AND qe.record_type = rtype
1865 AND qe.state_data = old_state_data
1866 AND qe.ingest_time IS NULL
1867 AND qe.fail_time IS NULL
1868 AND qe.override_by IS NULL
1869 ), existing_nonqueue_entry_cte AS (
1870 SELECT queue_id AS queue,
1871 rtype AS record_type,
1872 record_id AS record,
1873 qe.id AS override_by,
1874 ingest_action AS action,
1875 qe.run_at AS run_at,
1876 old_state_data AS state_data
1877 FROM action.ingest_queue_entry qe
1878 WHERE qe.record = record_id
1879 AND qe.queue IS NULL
1880 AND qe.record_type = rtype
1881 AND qe.state_data = old_state_data
1882 AND qe.ingest_time IS NULL
1883 AND qe.fail_time IS NULL
1884 AND qe.override_by IS NULL
1885 ), new_entry_cte AS (
1886 SELECT * FROM existing_queue_entry_cte
1888 SELECT * FROM existing_nonqueue_entry_cte
1890 SELECT queue_id, rtype, record_id, NULL, ingest_action, COALESCE(when_to_run,NOW()), old_state_data
1891 ), insert_entry_cte AS (
1892 INSERT INTO action.ingest_queue_entry
1893 (queue, record_type, record, override_by, action, run_at, state_data)
1894 SELECT queue, record_type, record, override_by, action, run_at, state_data FROM new_entry_cte
1895 ORDER BY 4 NULLS LAST, 6
1898 ) SELECT * INTO new_entry FROM insert_entry_cte;
1900 IF prev_del_entry.id IS NOT NULL THEN -- later delete overrides earlier unapplied entry
1901 UPDATE action.ingest_queue_entry
1902 SET override_by = new_entry.id
1903 WHERE id = prev_del_entry.id;
1905 UPDATE action.ingest_queue_entry
1906 SET override_by = NULL
1907 WHERE id = new_entry.id;
1909 ELSIF new_entry.override_by IS NOT NULL THEN
1910 RETURN TRUE; -- already handled, don't notify
1913 NOTIFY queued_ingest;
1916 EXCEPTION WHEN OTHERS THEN
1917 GET STACKED DIAGNOSTICS diag_detail = PG_EXCEPTION_DETAIL,
1918 diag_context = PG_EXCEPTION_CONTEXT;
1919 RAISE WARNING '%\n%', diag_detail, diag_context;
1922 $F$ LANGUAGE PLPGSQL;
1924 CREATE OR REPLACE FUNCTION action.process_ingest_queue_entry (qeid BIGINT) RETURNS BOOL AS $func$
1926 ingest_success BOOL := NULL;
1927 qe action.ingest_queue_entry%ROWTYPE;
1928 aid authority.record_entry.id%TYPE;
1931 SELECT * INTO qe FROM action.ingest_queue_entry WHERE id = qeid;
1932 IF qe.ingest_time IS NOT NULL OR qe.override_by IS NOT NULL THEN
1933 RETURN TRUE; -- Already done
1936 IF qe.action = 'delete' THEN
1937 IF qe.record_type = 'biblio' THEN
1938 SELECT metabib.indexing_delete(r.*, qe.state_data) INTO ingest_success FROM biblio.record_entry r WHERE r.id = qe.record;
1939 ELSIF qe.record_type = 'authority' THEN
1940 SELECT authority.indexing_delete(r.*, qe.state_data) INTO ingest_success FROM authority.record_entry r WHERE r.id = qe.record;
1943 IF qe.record_type = 'biblio' THEN
1944 IF qe.action = 'propagate' THEN
1945 SELECT authority.apply_propagate_changes(qe.state_data::BIGINT, qe.record) INTO aid;
1946 SELECT aid = qe.state_data::BIGINT INTO ingest_success;
1948 SELECT metabib.indexing_update(r.*, qe.action = 'insert', qe.state_data) INTO ingest_success FROM biblio.record_entry r WHERE r.id = qe.record;
1950 ELSIF qe.record_type = 'authority' THEN
1951 SELECT authority.indexing_update(r.*, qe.action = 'insert', qe.state_data) INTO ingest_success FROM authority.record_entry r WHERE r.id = qe.record;
1955 IF NOT ingest_success THEN
1956 UPDATE action.ingest_queue_entry SET fail_time = NOW() WHERE id = qe.id;
1957 PERFORM * FROM config.internal_flag WHERE name = 'ingest.queued.abort_on_error' AND enabled;
1959 RAISE EXCEPTION 'Ingest action of % on %.record_entry % for queue entry % failed', qe.action, qe.record_type, qe.record, qe.id;
1961 RAISE WARNING 'Ingest action of % on %.record_entry % for queue entry % failed', qe.action, qe.record_type, qe.record, qe.id;
1964 UPDATE action.ingest_queue_entry SET ingest_time = NOW() WHERE id = qe.id;
1967 RETURN ingest_success;
1969 $func$ LANGUAGE PLPGSQL;
1971 CREATE OR REPLACE FUNCTION action.complete_duplicated_entries () RETURNS TRIGGER AS $F$
1973 IF NEW.ingest_time IS NOT NULL THEN
1974 UPDATE action.ingest_queue_entry SET ingest_time = NEW.ingest_time WHERE override_by = NEW.id;
1979 $F$ LANGUAGE PLPGSQL;
1981 CREATE TRIGGER complete_duplicated_entries_trigger
1982 AFTER UPDATE ON action.ingest_queue_entry
1983 FOR EACH ROW WHEN (NEW.override_by IS NULL)
1984 EXECUTE PROCEDURE action.complete_duplicated_entries();
1986 CREATE OR REPLACE FUNCTION action.set_ingest_queue(INT) RETURNS VOID AS $$
1987 $_SHARED{"ingest_queue_id"} = $_[0];
1988 $$ LANGUAGE plperlu;
1990 CREATE OR REPLACE FUNCTION action.get_ingest_queue() RETURNS INT AS $$
1991 return $_SHARED{"ingest_queue_id"};
1992 $$ LANGUAGE plperlu;
1994 CREATE OR REPLACE FUNCTION action.clear_ingest_queue() RETURNS VOID AS $$
1995 delete($_SHARED{"ingest_queue_id"});
1996 $$ LANGUAGE plperlu;
1998 CREATE OR REPLACE FUNCTION action.set_queued_ingest_force(TEXT) RETURNS VOID AS $$
1999 $_SHARED{"ingest_queue_force"} = $_[0];
2000 $$ LANGUAGE plperlu;
2002 CREATE OR REPLACE FUNCTION action.get_queued_ingest_force() RETURNS TEXT AS $$
2003 return $_SHARED{"ingest_queue_force"};
2004 $$ LANGUAGE plperlu;
2006 CREATE OR REPLACE FUNCTION action.clear_queued_ingest_force() RETURNS VOID AS $$
2007 delete($_SHARED{"ingest_queue_force"});
2008 $$ LANGUAGE plperlu;
2010 CREATE OR REPLACE FUNCTION authority.propagate_changes
2011 (aid BIGINT, bid BIGINT) RETURNS BIGINT AS $func$
2013 queuing_success BOOL := FALSE;
2016 PERFORM 1 FROM config.global_flag
2017 WHERE name IN ('ingest.queued.all','ingest.queued.authority.propagate')
2021 -- XXX enqueue special 'propagate' bib action
2022 SELECT action.enqueue_ingest_entry( bid, 'biblio', NOW(), NULL, 'propagate', aid::TEXT) INTO queuing_success;
2024 IF queuing_success THEN
2029 PERFORM authority.apply_propagate_changes(aid, bid);
2032 $func$ LANGUAGE PLPGSQL;
2034 CREATE OR REPLACE FUNCTION authority.apply_propagate_changes
2035 (aid BIGINT, bid BIGINT) RETURNS BIGINT AS $func$
2037 bib_forced BOOL := FALSE;
2038 bib_rec biblio.record_entry%ROWTYPE;
2042 SELECT INTO bib_rec * FROM biblio.record_entry WHERE id = bid;
2044 new_marc := vandelay.merge_record_xml(
2045 bib_rec.marc, authority.generate_overlay_template(aid));
2047 IF new_marc = bib_rec.marc THEN
2048 -- Authority record change had no impact on this bib record.
2049 -- Nothing left to do.
2053 PERFORM 1 FROM config.global_flag
2054 WHERE name = 'ingest.disable_authority_auto_update_bib_meta'
2058 -- update the bib record editor and edit_date
2060 SELECT editor FROM authority.record_entry WHERE id = aid);
2061 bib_rec.edit_date = NOW();
2064 PERFORM action.set_queued_ingest_force('ingest.queued.biblio.update.disabled');
2066 UPDATE biblio.record_entry SET
2068 editor = bib_rec.editor,
2069 edit_date = bib_rec.edit_date
2072 PERFORM action.clear_queued_ingest_force();
2077 $func$ LANGUAGE PLPGSQL;
2079 CREATE OR REPLACE FUNCTION evergreen.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
2081 old_state_data TEXT := '';
2084 queuing_flag_name TEXT;
2085 queuing_flag BOOL := FALSE;
2086 queuing_success BOOL := FALSE;
2087 ingest_success BOOL := FALSE;
2091 -- Identify the ingest action type
2092 IF TG_OP = 'UPDATE' THEN
2094 -- Gather type-specific data for later use
2095 IF TG_TABLE_SCHEMA = 'authority' THEN
2096 old_state_data = OLD.heading;
2099 IF NOT OLD.deleted THEN -- maybe reingest?
2101 new_action = 'delete'; -- nope, delete
2103 new_action = 'update'; -- yes, update
2105 ELSIF NOT NEW.deleted THEN
2106 new_action = 'insert'; -- revivify, AKA insert
2108 RETURN NEW; -- was and is still deleted, don't ingest
2110 ELSIF TG_OP = 'INSERT' THEN
2111 new_action = 'insert'; -- brand new
2113 RETURN OLD; -- really deleting the record
2116 queuing_flag_name := 'ingest.queued.'||TG_TABLE_SCHEMA||'.'||new_action;
2117 -- See if we should be queuing anything
2118 SELECT enabled INTO queuing_flag
2119 FROM config.internal_flag
2120 WHERE name IN ('ingest.queued.all','ingest.queued.'||TG_TABLE_SCHEMA||'.all', queuing_flag_name)
2124 SELECT action.get_queued_ingest_force() INTO queuing_force;
2125 IF queuing_flag IS NULL AND queuing_force = queuing_flag_name THEN
2126 queuing_flag := TRUE;
2129 -- you (or part of authority propagation) can forcibly disable specific queuing actions
2130 IF queuing_force = queuing_flag_name||'.disabled' THEN
2131 queuing_flag := FALSE;
2134 -- And if we should be queuing ...
2135 IF queuing_flag THEN
2136 ingest_queue := action.get_ingest_queue();
2138 -- ... but this is NOT a named or forced queue request (marc editor update, say, or vandelay overlay)...
2139 IF queuing_force IS NULL AND ingest_queue IS NULL AND new_action = 'update' THEN -- re-ingest?
2141 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
2143 -- ... then don't do anything if ingest.reingest.force_on_same_marc is not enabled and the MARC hasn't changed
2144 IF NOT FOUND AND OLD.marc = NEW.marc THEN
2149 -- Otherwise, attempt to enqueue
2150 SELECT action.enqueue_ingest_entry( NEW.id, TG_TABLE_SCHEMA, NOW(), ingest_queue, new_action, old_state_data) INTO queuing_success;
2153 -- If queuing was not requested, or failed for some reason, do it live.
2154 IF NOT queuing_success THEN
2155 IF queuing_flag THEN
2156 RAISE WARNING 'Enqueuing of %.record_entry % for ingest failed, attempting direct ingest', TG_TABLE_SCHEMA, NEW.id;
2159 IF new_action = 'delete' THEN
2160 IF TG_TABLE_SCHEMA = 'biblio' THEN
2161 SELECT metabib.indexing_delete(NEW.*, old_state_data) INTO ingest_success;
2162 ELSIF TG_TABLE_SCHEMA = 'authority' THEN
2163 SELECT authority.indexing_delete(NEW.*, old_state_data) INTO ingest_success;
2166 IF TG_TABLE_SCHEMA = 'biblio' THEN
2167 SELECT metabib.indexing_update(NEW.*, new_action = 'insert', old_state_data) INTO ingest_success;
2168 ELSIF TG_TABLE_SCHEMA = 'authority' THEN
2169 SELECT authority.indexing_update(NEW.*, new_action = 'insert', old_state_data) INTO ingest_success;
2173 IF NOT ingest_success THEN
2174 PERFORM * FROM config.internal_flag WHERE name = 'ingest.queued.abort_on_error' AND enabled;
2176 RAISE EXCEPTION 'Ingest of %.record_entry % failed', TG_TABLE_SCHEMA, NEW.id;
2178 RAISE WARNING 'Ingest of %.record_entry % failed', TG_TABLE_SCHEMA, NEW.id;
2185 $func$ LANGUAGE PLPGSQL;
2187 CREATE TRIGGER aaa_indexing_ingest_or_delete AFTER INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.indexing_ingest_or_delete ();
2188 CREATE TRIGGER aaa_auth_ingest_or_delete AFTER INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.indexing_ingest_or_delete ();