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 OR REPLACE VIEW action.open_non_cataloged_circulation AS
59 FROM action.non_cataloged_circulation ncc
60 JOIN config.non_cataloged_type nct ON nct.id = ncc.item_type
61 WHERE ncc.circ_time + nct.circ_duration > CURRENT_TIMESTAMP
64 CREATE TABLE action.survey (
65 id SERIAL PRIMARY KEY,
66 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
67 start_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
68 end_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() + '10 years'::INTERVAL,
69 usr_summary BOOL NOT NULL DEFAULT FALSE,
70 opac BOOL NOT NULL DEFAULT FALSE,
71 poll BOOL NOT NULL DEFAULT FALSE,
72 required BOOL NOT NULL DEFAULT FALSE,
74 description TEXT NOT NULL
76 CREATE UNIQUE INDEX asv_once_per_owner_idx ON action.survey (owner,name);
78 CREATE TABLE action.survey_question (
79 id SERIAL PRIMARY KEY,
80 survey INT NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED,
81 question TEXT NOT NULL
84 CREATE TABLE action.survey_answer (
85 id SERIAL PRIMARY KEY,
86 question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
90 CREATE SEQUENCE action.survey_response_group_id_seq;
92 CREATE TABLE action.survey_response (
93 id BIGSERIAL PRIMARY KEY,
94 response_group_id INT,
95 usr INT, -- REFERENCES actor.usr
96 survey INT NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED,
97 question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
98 answer INT NOT NULL REFERENCES action.survey_answer DEFERRABLE INITIALLY DEFERRED,
99 answer_date TIMESTAMP WITH TIME ZONE,
100 effective_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
102 CREATE INDEX action_survey_response_usr_idx ON action.survey_response ( usr );
104 CREATE OR REPLACE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS '
106 NEW.answer_date := NOW();
109 ' LANGUAGE 'plpgsql';
110 CREATE TRIGGER action_survey_response_answer_date_fixup_tgr
111 BEFORE INSERT ON action.survey_response
113 EXECUTE PROCEDURE action.survey_response_answer_date_fixup ();
115 CREATE TABLE action.archive_actor_stat_cat (
116 id BIGSERIAL PRIMARY KEY,
117 xact BIGINT NOT NULL, -- action.circulation (+aged/all)
118 stat_cat INT NOT NULL,
122 CREATE TABLE action.archive_asset_stat_cat (
123 id BIGSERIAL PRIMARY KEY,
124 xact BIGINT NOT NULL, -- action.circulation (+aged/all)
125 stat_cat INT NOT NULL,
130 CREATE TABLE action.circulation (
131 target_copy BIGINT NOT NULL, -- asset.copy.id
132 circ_lib INT NOT NULL, -- actor.org_unit.id
133 circ_staff INT NOT NULL, -- actor.usr.id
134 checkin_staff INT, -- actor.usr.id
135 checkin_lib INT, -- actor.org_unit.id
136 renewal_remaining INT NOT NULL, -- derived from "circ duration" rule
137 grace_period INTERVAL NOT NULL, -- derived from "circ fine" rule
138 due_date TIMESTAMP WITH TIME ZONE,
139 stop_fines_time TIMESTAMP WITH TIME ZONE,
140 checkin_time TIMESTAMP WITH TIME ZONE,
141 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
142 duration INTERVAL, -- derived from "circ duration" rule
143 fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
144 recurring_fine NUMERIC(6,2), -- derived from "circ fine" rule
145 max_fine NUMERIC(6,2), -- derived from "max fine" rule
146 phone_renewal BOOL NOT NULL DEFAULT FALSE,
147 desk_renewal BOOL NOT NULL DEFAULT FALSE,
148 opac_renewal BOOL NOT NULL DEFAULT FALSE,
149 duration_rule TEXT NOT NULL, -- name of "circ duration" rule
150 recurring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule
151 max_fine_rule TEXT NOT NULL, -- name of "max fine" rule
152 stop_fines TEXT CHECK (stop_fines IN (
153 'CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE','CLAIMSNEVERCHECKEDOUT')),
154 workstation INT REFERENCES actor.workstation(id)
156 DEFERRABLE INITIALLY DEFERRED,
157 checkin_workstation INT REFERENCES actor.workstation(id)
159 DEFERRABLE INITIALLY DEFERRED,
160 copy_location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
161 checkin_scan_time TIMESTAMP WITH TIME ZONE,
162 auto_renewal BOOLEAN NOT NULL DEFAULT FALSE,
163 auto_renewal_remaining INTEGER
164 ) INHERITS (money.billable_xact);
165 ALTER TABLE action.circulation ADD PRIMARY KEY (id);
166 ALTER TABLE action.circulation
167 ADD COLUMN parent_circ BIGINT
168 REFERENCES action.circulation( id )
169 DEFERRABLE INITIALLY DEFERRED;
170 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
171 CREATE INDEX circ_outstanding_idx ON action.circulation (usr) WHERE checkin_time IS NULL;
172 CREATE INDEX circ_checkin_time ON "action".circulation (checkin_time) WHERE checkin_time IS NOT NULL;
173 CREATE INDEX circ_circ_lib_idx ON "action".circulation (circ_lib);
174 CREATE INDEX circ_open_date_idx ON "action".circulation (xact_start) WHERE xact_finish IS NULL;
175 CREATE INDEX circ_all_usr_idx ON action.circulation ( usr );
176 CREATE INDEX circ_circ_staff_idx ON action.circulation ( circ_staff );
177 CREATE INDEX circ_checkin_staff_idx ON action.circulation ( checkin_staff );
178 CREATE INDEX action_circulation_target_copy_idx ON action.circulation (target_copy);
179 CREATE UNIQUE INDEX circ_parent_idx ON action.circulation ( parent_circ ) WHERE parent_circ IS NOT NULL;
180 CREATE UNIQUE INDEX only_one_concurrent_checkout_per_copy ON action.circulation(target_copy) WHERE checkin_time IS NULL;
182 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');
184 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation');
185 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
186 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
188 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
190 proper_tz TEXT := COALESCE(
193 FROM actor.org_unit_ancestor_setting('lib.timezone',NEW.circ_lib)
196 CURRENT_SETTING('timezone')
200 IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 -- day-granular duration
201 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
202 NEW.due_date = ((NEW.due_date AT TIME ZONE proper_tz)::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL) || ' ' || proper_tz;
209 CREATE TRIGGER push_due_date_tgr BEFORE INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.push_circ_due_time();
211 CREATE OR REPLACE FUNCTION action.fill_circ_copy_location () RETURNS TRIGGER AS $$
213 SELECT INTO NEW.copy_location location FROM asset.copy WHERE id = NEW.target_copy;
218 CREATE TRIGGER fill_circ_copy_location_tgr BEFORE INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.fill_circ_copy_location();
220 CREATE OR REPLACE FUNCTION action.archive_stat_cats () RETURNS TRIGGER AS $$
222 INSERT INTO action.archive_actor_stat_cat(xact, stat_cat, value)
223 SELECT NEW.id, asceum.stat_cat, asceum.stat_cat_entry
224 FROM actor.stat_cat_entry_usr_map asceum
225 JOIN actor.stat_cat sc ON asceum.stat_cat = sc.id
226 WHERE NEW.usr = asceum.target_usr AND sc.checkout_archive;
227 INSERT INTO action.archive_asset_stat_cat(xact, stat_cat, value)
228 SELECT NEW.id, ascecm.stat_cat, asce.value
229 FROM asset.stat_cat_entry_copy_map ascecm
230 JOIN asset.stat_cat sc ON ascecm.stat_cat = sc.id
231 JOIN asset.stat_cat_entry asce ON ascecm.stat_cat_entry = asce.id
232 WHERE NEW.target_copy = ascecm.owning_copy AND sc.checkout_archive;
237 CREATE TRIGGER archive_stat_cats_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.archive_stat_cats();
239 CREATE TABLE action.aged_circulation (
241 usr_home_ou INT NOT NULL,
242 usr_profile INT NOT NULL,
244 copy_call_number INT NOT NULL,
245 copy_owning_lib INT NOT NULL,
246 copy_circ_lib INT NOT NULL,
247 copy_bib_record BIGINT NOT NULL,
248 LIKE action.circulation
251 ALTER TABLE action.aged_circulation ADD PRIMARY KEY (id);
252 ALTER TABLE action.aged_circulation DROP COLUMN usr;
253 CREATE INDEX aged_circ_circ_lib_idx ON "action".aged_circulation (circ_lib);
254 CREATE INDEX aged_circ_start_idx ON "action".aged_circulation (xact_start);
255 CREATE INDEX aged_circ_copy_circ_lib_idx ON "action".aged_circulation (copy_circ_lib);
256 CREATE INDEX aged_circ_copy_owning_lib_idx ON "action".aged_circulation (copy_owning_lib);
257 CREATE INDEX aged_circ_copy_location_idx ON "action".aged_circulation (copy_location);
258 CREATE INDEX action_aged_circulation_target_copy_idx ON action.aged_circulation (target_copy);
259 CREATE INDEX action_aged_circulation_parent_circ_idx ON action.aged_circulation (parent_circ);
261 CREATE OR REPLACE VIEW action.all_circulation AS
262 SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
263 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
264 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
265 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
266 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
267 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
268 auto_renewal, auto_renewal_remaining,
270 FROM action.aged_circulation
272 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,
273 cp.call_number AS copy_call_number, circ.copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
274 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
275 circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
276 circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
277 circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
278 circ.parent_circ, circ.auto_renewal, circ.auto_renewal_remaining, circ.usr
279 FROM action.circulation circ
280 JOIN asset.copy cp ON (circ.target_copy = cp.id)
281 JOIN asset.call_number cn ON (cp.call_number = cn.id)
282 JOIN actor.usr p ON (circ.usr = p.id)
283 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
284 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
286 CREATE OR REPLACE VIEW action.all_circulation_slim AS
287 SELECT * FROM action.circulation
322 auto_renewal_remaining,
324 FROM action.aged_circulation
328 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
333 -- If there are any renewals for this circulation, don't archive or delete
334 -- it yet. We'll do so later, when we archive and delete the renewals.
336 SELECT 'Y' INTO found
337 FROM action.circulation
338 WHERE parent_circ = OLD.id
342 RETURN NULL; -- don't delete
345 -- Archive a copy of the old row to action.aged_circulation
347 INSERT INTO action.aged_circulation
348 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
349 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
350 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
351 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
352 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
353 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
354 auto_renewal, auto_renewal_remaining)
356 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
357 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
358 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
359 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
360 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
361 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
362 auto_renewal, auto_renewal_remaining
363 FROM action.all_circulation WHERE id = OLD.id;
365 -- Migrate billings and payments to aged tables
367 SELECT 'Y' INTO found FROM config.global_flag
368 WHERE name = 'history.money.age_with_circs' AND enabled;
371 PERFORM money.age_billings_and_payments_for_xact(OLD.id);
374 -- Break the link with the user in action_trigger.event (warning: event_output may essentially have this information)
376 action_trigger.event e
380 action.all_circulation c
383 AND e.context_user = c.usr
387 FROM action_trigger.event_definition
388 WHERE hook in (SELECT key FROM action_trigger.hook WHERE core_type = 'circ')
394 $$ LANGUAGE 'plpgsql';
396 CREATE TRIGGER action_circulation_aging_tgr
397 BEFORE DELETE ON action.circulation
399 EXECUTE PROCEDURE action.age_circ_on_delete ();
402 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
405 -- Having deleted a renewal, we can delete the original circulation (or a previous
406 -- renewal, if that's what parent_circ is pointing to). That deletion will trigger
407 -- deletion of any prior parents, etc. recursively.
409 IF OLD.parent_circ IS NOT NULL THEN
410 DELETE FROM action.circulation
411 WHERE id = OLD.parent_circ;
416 $$ LANGUAGE 'plpgsql';
418 CREATE TRIGGER age_parent_circ
419 AFTER DELETE ON action.circulation
421 EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
424 CREATE OR REPLACE VIEW action.open_circulation AS
426 FROM action.circulation
427 WHERE checkin_time IS NULL
431 CREATE OR REPLACE VIEW action.billable_circulations AS
433 FROM action.circulation
434 WHERE xact_finish IS NULL;
436 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
438 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
439 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
440 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
442 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
443 UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
445 IF NEW.stop_fines = 'LOST' THEN
446 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
451 $$ LANGUAGE 'plpgsql';
452 CREATE TRIGGER action_circulation_stop_fines_tgr
453 BEFORE UPDATE ON action.circulation
455 EXECUTE PROCEDURE action.circulation_claims_returned ();
457 CREATE TABLE action.hold_request_cancel_cause (
458 id SERIAL PRIMARY KEY,
460 manual BOOL NOT NULL DEFAULT FALSE
463 CREATE TABLE action.hold_request (
464 id SERIAL PRIMARY KEY,
465 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
466 capture_time TIMESTAMP WITH TIME ZONE,
467 fulfillment_time TIMESTAMP WITH TIME ZONE,
468 checkin_time TIMESTAMP WITH TIME ZONE,
469 return_time TIMESTAMP WITH TIME ZONE,
470 prev_check_time TIMESTAMP WITH TIME ZONE,
471 expire_time TIMESTAMP WITH TIME ZONE,
472 cancel_time TIMESTAMP WITH TIME ZONE,
473 cancel_cause INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
475 target BIGINT NOT NULL, -- see hold_type
476 current_copy BIGINT, -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.unit now...
477 fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
478 fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
479 request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
480 requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
481 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
482 selection_ou INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
483 selection_depth INT NOT NULL DEFAULT 0,
484 pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
485 hold_type TEXT REFERENCES config.hold_type (hold_type) DEFERRABLE INITIALLY DEFERRED,
486 holdable_formats TEXT,
488 email_notify BOOL NOT NULL DEFAULT FALSE,
490 sms_carrier INT REFERENCES config.sms_carrier (id),
491 frozen BOOL NOT NULL DEFAULT FALSE,
492 thaw_date TIMESTAMP WITH TIME ZONE,
493 shelf_time TIMESTAMP WITH TIME ZONE,
495 mint_condition BOOL NOT NULL DEFAULT TRUE,
496 shelf_expire_time TIMESTAMPTZ,
497 current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
498 behind_desk BOOLEAN NOT NULL DEFAULT FALSE,
499 hopeless_date TIMESTAMP WITH TIME ZONE
501 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
503 OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
507 CREATE OR REPLACE FUNCTION action.hold_request_clear_map () RETURNS TRIGGER AS $$
509 DELETE FROM action.hold_copy_map WHERE hold = NEW.id;
514 CREATE TRIGGER hold_request_clear_map_tgr
515 AFTER UPDATE ON action.hold_request
518 (NEW.cancel_time IS NOT NULL AND OLD.cancel_time IS NULL)
519 OR (NEW.fulfillment_time IS NOT NULL AND OLD.fulfillment_time IS NULL)
521 EXECUTE PROCEDURE action.hold_request_clear_map();
523 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
524 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
525 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
526 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
527 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
528 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
529 CREATE INDEX hold_request_requestor_idx ON action.hold_request ( requestor );
530 CREATE INDEX hold_request_open_idx ON action.hold_request (id) WHERE cancel_time IS NULL AND fulfillment_time IS NULL;
531 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;
532 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;
533 CREATE INDEX hold_request_copy_capture_time_idx ON action.hold_request (current_copy,capture_time);
534 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);
535 CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
536 CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);
537 CREATE INDEX hold_request_hopeless_date_idx ON action.hold_request (hopeless_date);
539 CREATE TABLE action.hold_request_note (
541 id BIGSERIAL PRIMARY KEY,
542 hold BIGINT NOT NULL REFERENCES action.hold_request (id)
544 DEFERRABLE INITIALLY DEFERRED,
547 slip BOOL NOT NULL DEFAULT FALSE,
548 pub BOOL NOT NULL DEFAULT FALSE,
549 staff BOOL NOT NULL DEFAULT FALSE -- created by staff
552 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
555 CREATE TABLE action.hold_notification (
556 id SERIAL PRIMARY KEY,
557 hold INT NOT NULL REFERENCES action.hold_request (id)
559 DEFERRABLE INITIALLY DEFERRED,
560 notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
561 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
562 method TEXT NOT NULL, -- email address or phone number
565 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
566 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
568 CREATE TABLE action.hold_copy_map (
569 id BIGSERIAL PRIMARY KEY,
570 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
571 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
573 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
575 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
576 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
578 CREATE OR REPLACE FUNCTION
579 action.hold_request_regen_copy_maps(
580 hold_id INTEGER, copy_ids INTEGER[]) RETURNS VOID AS $$
581 DELETE FROM action.hold_copy_map WHERE hold = $1;
582 INSERT INTO action.hold_copy_map (hold, target_copy) SELECT DISTINCT $1, UNNEST($2);
585 CREATE TABLE action.transit_copy (
586 id SERIAL PRIMARY KEY,
587 source_send_time TIMESTAMP WITH TIME ZONE,
588 dest_recv_time TIMESTAMP WITH TIME ZONE,
589 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
590 source INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
591 dest INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
592 prev_hop INT REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
593 copy_status INT NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
594 persistant_transfer BOOL NOT NULL DEFAULT FALSE,
595 prev_dest INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
596 cancel_time TIMESTAMP WITH TIME ZONE
598 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
599 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
600 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
601 CREATE INDEX active_transit_for_copy ON action.transit_copy (target_copy)
602 WHERE dest_recv_time IS NULL AND cancel_time IS NULL;
604 -- Check for duplicate transits across all transit types
605 CREATE OR REPLACE FUNCTION action.copy_transit_is_unique()
606 RETURNS TRIGGER AS $func$
608 PERFORM * FROM action.transit_copy
609 WHERE target_copy = NEW.target_copy
610 AND dest_recv_time IS NULL
611 AND cancel_time IS NULL;
614 RAISE EXCEPTION 'Copy id=% is already in transit', NEW.target_copy;
618 $func$ LANGUAGE PLPGSQL STABLE;
620 CREATE CONSTRAINT TRIGGER transit_copy_is_unique_check
621 AFTER INSERT ON action.transit_copy
622 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
624 CREATE TABLE action.hold_transit_copy (
625 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
626 ) INHERITS (action.transit_copy);
627 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
628 -- 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
629 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
630 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
631 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
632 CREATE INDEX hold_transit_copy_hold_idx on action.hold_transit_copy (hold);
634 CREATE CONSTRAINT TRIGGER hold_transit_copy_is_unique_check
635 AFTER INSERT ON action.hold_transit_copy
636 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
639 CREATE TABLE action.unfulfilled_hold_list (
640 id BIGSERIAL PRIMARY KEY,
641 current_copy BIGINT NOT NULL,
643 circ_lib INT NOT NULL,
644 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
646 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
648 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
652 FROM action.unfulfilled_hold_list u
653 JOIN asset.copy c ON (c.id = u.current_copy)
656 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
659 FROM action.unfulfilled_hold_loops
662 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
664 FROM action.unfulfilled_hold_loops l
665 JOIN action.unfulfilled_hold_min_loop m USING (hold)
666 WHERE l.count = m.min;
668 CREATE VIEW action.unfulfilled_hold_max_loop AS
671 FROM action.unfulfilled_hold_loops
675 CREATE TABLE action.aged_hold_request (
677 usr_home_ou INT NOT NULL,
678 usr_profile INT NOT NULL,
680 staff_placed BOOLEAN NOT NULL,
681 LIKE action.hold_request
683 ALTER TABLE action.aged_hold_request
684 ADD PRIMARY KEY (id),
686 DROP COLUMN requestor,
687 DROP COLUMN sms_carrier,
688 ALTER COLUMN phone_notify TYPE BOOLEAN
689 USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
690 ALTER COLUMN sms_notify TYPE BOOLEAN
691 USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
692 ALTER COLUMN phone_notify SET NOT NULL,
693 ALTER COLUMN sms_notify SET NOT NULL;
694 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
695 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
696 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
697 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
699 CREATE OR REPLACE VIEW action.all_hold_request AS
701 COALESCE(a.post_code, b.post_code) AS usr_post_code,
702 p.home_ou AS usr_home_ou,
703 p.profile AS usr_profile,
704 EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
705 CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
709 ahr.fulfillment_time,
719 ahr.fulfillment_staff,
726 ahr.holdable_formats,
728 WHEN ahr.phone_notify IS NULL THEN FALSE
729 WHEN ahr.phone_notify = '' THEN FALSE
734 WHEN ahr.sms_notify IS NULL THEN FALSE
735 WHEN ahr.sms_notify = '' THEN FALSE
743 ahr.shelf_expire_time,
744 ahr.current_shelf_lib,
746 FROM action.hold_request ahr
747 JOIN actor.usr p ON (ahr.usr = p.id)
748 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
749 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
789 FROM action.aged_hold_request;
791 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
794 -- Archive a copy of the old row to action.aged_hold_request
796 INSERT INTO action.aged_hold_request
872 FROM action.all_hold_request WHERE id = OLD.id;
876 $$ LANGUAGE 'plpgsql';
878 CREATE TRIGGER action_hold_request_aging_tgr
879 BEFORE DELETE ON action.hold_request
881 EXECUTE PROCEDURE action.age_hold_on_delete ();
883 CREATE TABLE action.fieldset_group (
884 id SERIAL PRIMARY KEY,
886 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
887 complete_time TIMESTAMPTZ,
888 container INT, -- Points to a container of some type ...
889 container_type TEXT, -- One of 'biblio_record_entry', 'user', 'call_number', 'copy'
890 can_rollback BOOL DEFAULT TRUE,
891 rollback_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
892 rollback_time TIMESTAMPTZ,
893 creator INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
894 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
897 CREATE TABLE action.fieldset (
898 id SERIAL PRIMARY KEY,
899 fieldset_group INT REFERENCES action.fieldset_group (id)
900 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
901 owner INT NOT NULL REFERENCES actor.usr (id)
902 DEFERRABLE INITIALLY DEFERRED,
903 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
904 DEFERRABLE INITIALLY DEFERRED,
906 CONSTRAINT valid_status CHECK ( status in
907 ( 'PENDING', 'APPLIED', 'ERROR' )),
908 creation_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
909 scheduled_time TIMESTAMPTZ,
910 applied_time TIMESTAMPTZ,
911 classname TEXT NOT NULL, -- an IDL class name
914 stored_query INT REFERENCES query.stored_query (id)
915 DEFERRABLE INITIALLY DEFERRED,
917 CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
918 CONSTRAINT fieldset_one_or_the_other CHECK (
919 (stored_query IS NOT NULL AND pkey_value IS NULL) OR
920 (pkey_value IS NOT NULL AND stored_query IS NULL)
922 -- the CHECK constraint means we can update the fields for a single
923 -- row without all the extra overhead involved in a query
926 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
927 CREATE INDEX action_owner_idx ON action.fieldset( owner );
930 CREATE TABLE action.fieldset_col_val (
931 id SERIAL PRIMARY KEY,
932 fieldset INT NOT NULL REFERENCES action.fieldset
934 DEFERRABLE INITIALLY DEFERRED,
935 col TEXT NOT NULL, -- "field" from the idl ... the column on the table
936 val TEXT, -- value for the column ... NULL means, well, NULL
937 CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
941 -- represents a circ chain summary
942 CREATE TYPE action.circ_chain_summary AS (
944 start_time TIMESTAMP WITH TIME ZONE,
945 checkout_workstation TEXT,
946 last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
947 last_stop_fines TEXT,
948 last_stop_fines_time TIMESTAMP WITH TIME ZONE,
949 last_renewal_workstation TEXT, -- NULL if no renewals
950 last_checkin_workstation TEXT,
951 last_checkin_time TIMESTAMP WITH TIME ZONE,
952 last_checkin_scan_time TIMESTAMP WITH TIME ZONE
956 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id BIGINT ) RETURNS SETOF action.circulation AS $$
958 tmp_circ action.circulation%ROWTYPE;
959 circ_0 action.circulation%ROWTYPE;
962 SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
964 IF tmp_circ IS NULL THEN
965 RETURN NEXT tmp_circ;
969 -- find the front of the chain
971 SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
972 IF tmp_circ IS NULL THEN
978 -- now send the circs to the caller, oldest to newest
981 IF tmp_circ IS NULL THEN
984 RETURN NEXT tmp_circ;
985 SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
989 $$ LANGUAGE 'plpgsql';
991 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id BIGINT ) RETURNS action.circ_chain_summary AS $$
995 -- first circ in the chain
996 circ_0 action.circulation%ROWTYPE;
998 -- last circ in the chain
999 circ_n action.circulation%ROWTYPE;
1001 -- circ chain under construction
1002 chain action.circ_chain_summary;
1003 tmp_circ action.circulation%ROWTYPE;
1007 chain.num_circs := 0;
1008 FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
1010 IF chain.num_circs = 0 THEN
1014 chain.num_circs := chain.num_circs + 1;
1018 chain.start_time := circ_0.xact_start;
1019 chain.last_stop_fines := circ_n.stop_fines;
1020 chain.last_stop_fines_time := circ_n.stop_fines_time;
1021 chain.last_checkin_time := circ_n.checkin_time;
1022 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
1023 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
1024 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
1026 IF chain.num_circs > 1 THEN
1027 chain.last_renewal_time := circ_n.xact_start;
1028 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
1034 $$ LANGUAGE 'plpgsql';
1036 -- same as action.circ_chain, but returns action.all_circulation
1037 -- rows which may include aged circulations.
1038 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
1039 RETURNS SETOF action.all_circulation_slim AS $$
1041 tmp_circ action.all_circulation_slim%ROWTYPE;
1042 circ_0 action.all_circulation_slim%ROWTYPE;
1045 SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
1047 IF tmp_circ IS NULL THEN
1048 RETURN NEXT tmp_circ;
1052 -- find the front of the chain
1054 SELECT INTO tmp_circ * FROM action.all_circulation_slim
1055 WHERE id = tmp_circ.parent_circ;
1056 IF tmp_circ IS NULL THEN
1062 -- now send the circs to the caller, oldest to newest
1065 IF tmp_circ IS NULL THEN
1068 RETURN NEXT tmp_circ;
1069 SELECT INTO tmp_circ * FROM action.all_circulation_slim
1070 WHERE parent_circ = tmp_circ.id;
1074 $$ LANGUAGE 'plpgsql';
1076 -- same as action.summarize_circ_chain, but returns data collected
1077 -- from action.all_circulation, which may include aged circulations.
1078 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
1079 (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
1083 -- first circ in the chain
1084 circ_0 action.all_circulation_slim%ROWTYPE;
1086 -- last circ in the chain
1087 circ_n action.all_circulation_slim%ROWTYPE;
1089 -- circ chain under construction
1090 chain action.circ_chain_summary;
1091 tmp_circ action.all_circulation_slim%ROWTYPE;
1095 chain.num_circs := 0;
1096 FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
1098 IF chain.num_circs = 0 THEN
1102 chain.num_circs := chain.num_circs + 1;
1106 chain.start_time := circ_0.xact_start;
1107 chain.last_stop_fines := circ_n.stop_fines;
1108 chain.last_stop_fines_time := circ_n.stop_fines_time;
1109 chain.last_checkin_time := circ_n.checkin_time;
1110 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
1111 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
1112 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
1114 IF chain.num_circs > 1 THEN
1115 chain.last_renewal_time := circ_n.xact_start;
1116 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
1122 $$ LANGUAGE 'plpgsql';
1124 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
1126 h action.hold_request%ROWTYPE;
1129 usr_view_count actor.usr_setting%ROWTYPE;
1130 usr_view_age actor.usr_setting%ROWTYPE;
1131 usr_view_start actor.usr_setting%ROWTYPE;
1133 SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
1134 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
1135 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
1139 FROM action.hold_request
1141 AND fulfillment_time IS NULL
1142 AND cancel_time IS NULL
1143 ORDER BY request_time DESC
1148 IF usr_view_start.value IS NULL THEN
1152 IF usr_view_age.value IS NOT NULL THEN
1153 -- User opted in and supplied a retention age
1154 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
1155 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1157 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
1161 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
1164 IF usr_view_count.value IS NOT NULL THEN
1165 view_count := oils_json_to_text(usr_view_count.value)::INT;
1170 -- show some fulfilled/canceled holds
1173 FROM action.hold_request
1175 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
1176 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
1177 ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
1185 $func$ LANGUAGE PLPGSQL;
1187 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
1189 org_keep_age INTERVAL;
1190 org_use_last BOOL = false;
1191 org_age_is_min BOOL = false;
1197 circ_chain_head action.circulation%ROWTYPE;
1198 circ_chain_tail action.circulation%ROWTYPE;
1203 last_finished TIMESTAMP WITH TIME ZONE;
1208 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
1210 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
1211 IF org_keep_count IS NULL THEN
1212 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
1215 SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
1216 SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
1218 -- First, find copies with more than keep_count non-renewal circs
1221 COUNT(*) AS total_real_circs
1222 FROM action.circulation
1223 WHERE parent_circ IS NULL
1224 AND xact_finish IS NOT NULL
1225 GROUP BY target_copy
1226 HAVING COUNT(*) > org_keep_count
1228 -- And, for those, select circs that are finished and older than keep_age
1229 FOR circ_chain_head IN
1230 -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
1231 -- The outer query then uses that information to skip the most recent set the library wants to keep
1232 -- End result is we don't care what order they come out in, as they are all potentials for deletion.
1233 SELECT ac.* FROM action.circulation ac JOIN (
1234 SELECT rank() OVER (ORDER BY xact_start DESC), ac.id
1235 FROM action.circulation ac
1236 WHERE ac.target_copy = target_acp.target_copy
1237 AND ac.parent_circ IS NULL
1238 ORDER BY ac.xact_start ) ranked USING (id)
1239 WHERE ranked.rank > org_keep_count
1242 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
1243 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);
1244 CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
1246 IF NOT org_use_last THEN
1247 last_finished := circ_chain_tail.xact_finish;
1250 keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
1252 IF org_age_is_min THEN
1253 keep_age := GREATEST( keep_age, org_keep_age );
1256 CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
1258 -- We've passed the purging tests, purge the circ chain starting at the end
1259 -- A trigger should auto-purge the rest of the chain.
1260 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
1262 count_purged := count_purged + 1;
1267 return count_purged;
1269 $func$ LANGUAGE PLPGSQL;
1271 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
1273 current_hold RECORD;
1279 user_start TIMESTAMPTZ;
1284 SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
1285 SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
1286 SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
1289 rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
1290 cgf_cs.value::INTERVAL as cgf_cs,
1293 action.hold_request ahr
1294 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)
1296 (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
1298 IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
1299 prev_usr := current_hold.usr;
1300 SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
1301 SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
1302 SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
1303 IF user_start IS NOT NULL THEN
1304 user_age := LEAST(user_age, AGE(NOW(), user_start));
1306 IF user_count IS NULL THEN
1307 user_count := 1000; -- Assumption based on the user visible holds routine
1310 -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
1311 IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
1314 IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
1318 -- User keep age needs combining with count. If too old AND within the count, keep!
1319 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
1323 -- All checks should have passed, delete!
1324 DELETE FROM action.hold_request WHERE id = current_hold.id;
1325 purged_holds := purged_holds + 1;
1327 RETURN purged_holds;
1329 $func$ LANGUAGE plpgsql;
1331 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1332 fieldset_id IN INT, -- id from action.fieldset
1333 table_name IN TEXT, -- table to be updated
1334 pkey_name IN TEXT, -- name of primary key column in that table
1335 query IN TEXT -- query constructed by qstore (for query-based
1336 -- fieldsets only; otherwise null
1352 fs_obj action.fieldset%ROWTYPE;
1353 fs_group action.fieldset_group%ROWTYPE;
1357 IF fieldset_id IS NULL THEN
1358 RETURN 'Fieldset ID parameter is NULL';
1360 IF table_name IS NULL THEN
1361 RETURN 'Table name parameter is NULL';
1363 IF pkey_name IS NULL THEN
1364 RETURN 'Primary key name parameter is NULL';
1369 quote_literal( pkey_value )
1379 -- Build the WHERE clause. This differs according to whether it's a
1380 -- single-row fieldset or a query-based fieldset.
1382 IF query IS NULL AND fs_pkey_value IS NULL THEN
1383 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1384 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1385 fs_query := rtrim( query, ';' );
1386 where_clause := 'WHERE ' || pkey_name || ' IN ( '
1387 || fs_query || ' )';
1388 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
1389 where_clause := 'WHERE ' || pkey_name || ' = ';
1390 IF pkey_name = 'id' THEN
1391 where_clause := where_clause || fs_pkey_value;
1392 ELSIF pkey_name = 'code' THEN
1393 where_clause := where_clause || quote_literal(fs_pkey_value);
1395 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1397 ELSE -- both are not null
1398 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1401 IF fs_status IS NULL THEN
1402 RETURN 'No fieldset found for id = ' || fieldset_id;
1403 ELSIF fs_status = 'APPLIED' THEN
1404 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1407 SELECT * INTO fs_obj FROM action.fieldset WHERE id = fieldset_id;
1408 SELECT * INTO fs_group FROM action.fieldset_group WHERE id = fs_obj.fieldset_group;
1410 IF fs_group.can_rollback THEN
1411 -- This is part of a non-rollback group. We need to record the current values for future rollback.
1413 INSERT INTO action.fieldset_group (can_rollback, name, creator, owning_lib, container, container_type)
1414 VALUES (FALSE, 'ROLLBACK: '|| fs_group.name, fs_group.creator, fs_group.owning_lib, fs_group.container, fs_group.container_type);
1416 fsg_id := CURRVAL('action.fieldset_group_id_seq');
1418 FOR rb_row IN EXECUTE 'SELECT * FROM ' || table_name || ' ' || where_clause LOOP
1419 IF pkey_name = 'id' THEN
1420 fs_pkey_value := rb_row.id;
1421 ELSIF pkey_name = 'code' THEN
1422 fs_pkey_value := rb_row.code;
1424 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
1426 INSERT INTO action.fieldset (fieldset_group,owner,owning_lib,status,classname,name,pkey_value)
1427 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);
1429 fs_id := CURRVAL('action.fieldset_id_seq');
1433 FROM action.fieldset_col_val
1434 WHERE fieldset = fieldset_id
1436 EXECUTE 'INSERT INTO action.fieldset_col_val (fieldset, col, val) ' ||
1437 'SELECT '|| fs_id || ', '||quote_literal(cv.col)||', '||cv.col||' FROM '||table_name||' WHERE '||pkey_name||' = '||fs_pkey_value;
1442 statement := 'UPDATE ' || table_name || ' SET';
1448 FROM action.fieldset_col_val
1449 WHERE fieldset = fieldset_id
1451 statement := statement || sep || ' ' || cv.col
1452 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1457 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1459 statement := statement || ' ' || where_clause;
1462 -- Execute the update
1466 GET DIAGNOSTICS update_count = ROW_COUNT;
1468 IF update_count = 0 THEN
1469 RAISE data_exception;
1472 IF fsg_id IS NOT NULL THEN
1473 UPDATE action.fieldset_group SET rollback_group = fsg_id WHERE id = fs_group.id;
1476 IF fs_group.id IS NOT NULL THEN
1477 UPDATE action.fieldset_group SET complete_time = now() WHERE id = fs_group.id;
1480 UPDATE action.fieldset SET status = 'APPLIED', applied_time = now() WHERE id = fieldset_id;
1482 EXCEPTION WHEN data_exception THEN
1483 msg := 'No eligible rows found for fieldset ' || fieldset_id;
1484 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1491 EXCEPTION WHEN OTHERS THEN
1492 msg := 'Unable to apply fieldset ' || fieldset_id || ': ' || sqlerrm;
1493 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
1497 $$ LANGUAGE plpgsql;
1499 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
1500 Applies a specified fieldset, using a supplied table name and primary
1501 key name. The query parameter should be non-null only for
1502 query-based fieldsets.
1504 Returns NULL if successful, or an error message if not.
1507 CREATE OR REPLACE FUNCTION action.copy_calculated_proximity(
1514 ) RETURNS NUMERIC AS $f$
1516 baseline_prox NUMERIC;
1517 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
1520 -- First, gather the baseline proximity of "here" to pickup lib
1521 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = vacp_cl AND to_org = pickup;
1523 -- Find any absolute adjustments, and set the baseline prox to that
1524 SELECT adj.* INTO aoupa
1525 FROM actor.org_unit_proximity_adjustment adj
1526 LEFT JOIN actor.org_unit_ancestors_distance(vacp_cl) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1527 LEFT JOIN actor.org_unit_ancestors_distance(vacn_ol) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1528 LEFT JOIN actor.org_unit_ancestors_distance(vacl_ol) acl_ol ON (acl_ol.id = adj.copy_location)
1529 LEFT JOIN actor.org_unit_ancestors_distance(pickup) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1530 LEFT JOIN actor.org_unit_ancestors_distance(request) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1531 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = vacp_cm) AND
1532 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1533 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1534 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1535 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1536 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1537 absolute_adjustment AND
1538 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1540 COALESCE(acp_cl.distance,999)
1541 + COALESCE(acn_ol.distance,999)
1542 + COALESCE(acl_ol.distance,999)
1543 + COALESCE(ahr_pl.distance,999)
1544 + COALESCE(ahr_rl.distance,999),
1549 baseline_prox := aoupa.prox_adjustment;
1552 -- Now find any relative adjustments, and change the baseline prox based on them
1555 FROM actor.org_unit_proximity_adjustment adj
1556 LEFT JOIN actor.org_unit_ancestors_distance(vacp_cl) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1557 LEFT JOIN actor.org_unit_ancestors_distance(vacn_ol) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1558 LEFT JOIN actor.org_unit_ancestors_distance(vacl_ol) acl_ol ON (acn_ol.id = adj.copy_location)
1559 LEFT JOIN actor.org_unit_ancestors_distance(pickup) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1560 LEFT JOIN actor.org_unit_ancestors_distance(request) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1561 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = vacp_cm) AND
1562 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1563 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1564 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1565 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1566 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1567 NOT absolute_adjustment AND
1568 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1570 baseline_prox := baseline_prox + aoupa.prox_adjustment;
1573 RETURN baseline_prox;
1575 $f$ LANGUAGE PLPGSQL;
1577 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
1580 copy_context_ou INT DEFAULT NULL
1581 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
1582 -- support an "ahprox" measurement: adjust prox between copy circ lib and
1583 -- hold request lib, but I'm unsure whether to use this theoretical
1584 -- argument only in the baseline calculation or later in the other
1585 -- queries in this function.
1586 ) RETURNS NUMERIC AS $f$
1588 ahr action.hold_request%ROWTYPE;
1589 acp asset.copy%ROWTYPE;
1590 acn asset.call_number%ROWTYPE;
1591 acl asset.copy_location%ROWTYPE;
1596 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
1597 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
1598 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
1599 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
1601 IF copy_context_ou IS NULL THEN
1602 copy_context_ou := acp.circ_lib;
1605 SELECT action.copy_calculated_proximity(
1616 $f$ LANGUAGE PLPGSQL;
1618 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$
1620 NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy);
1623 $f$ LANGUAGE PLPGSQL;
1625 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 ();
1627 CREATE TABLE action.usr_circ_history (
1628 id BIGSERIAL PRIMARY KEY,
1629 usr INTEGER NOT NULL REFERENCES actor.usr(id)
1630 DEFERRABLE INITIALLY DEFERRED,
1631 xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1632 target_copy BIGINT NOT NULL, -- asset.copy.id / serial.unit.id
1633 due_date TIMESTAMP WITH TIME ZONE NOT NULL,
1634 checkin_time TIMESTAMP WITH TIME ZONE,
1635 source_circ BIGINT REFERENCES action.circulation(id)
1636 ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
1639 CREATE INDEX action_usr_circ_history_usr_idx ON action.usr_circ_history ( usr );
1640 CREATE INDEX action_usr_circ_history_source_circ_idx ON action.usr_circ_history ( source_circ );
1642 CREATE TRIGGER action_usr_circ_history_target_copy_trig
1643 AFTER INSERT OR UPDATE ON action.usr_circ_history
1644 FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
1646 CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history()
1647 RETURNS TRIGGER AS $FUNK$
1653 -- Any retention value signifies history is enabled.
1654 -- This assumes that clearing these values via external
1655 -- process deletes the action.usr_circ_history rows.
1656 -- TODO: replace these settings w/ a single bool setting?
1657 PERFORM 1 FROM actor.usr_setting
1658 WHERE usr = NEW.usr AND value IS NOT NULL AND name IN (
1659 'history.circ.retention_age',
1660 'history.circ.retention_start'
1667 IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN
1668 -- Starting a new circulation. Insert the history row.
1669 INSERT INTO action.usr_circ_history
1670 (usr, xact_start, target_copy, due_date, source_circ)
1682 -- find the first and last circs in the circ chain
1683 -- for the currently modified circ.
1684 FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP
1685 IF first_circ IS NULL THEN
1686 first_circ := cur_circ;
1689 -- Allow the loop to continue so that at as the loop
1690 -- completes cur_circ points to the final circulation.
1693 IF NEW.id <> cur_circ THEN
1694 -- Modifying an intermediate circ. Ignore it.
1698 -- Update the due_date/checkin_time on the history row if the current
1699 -- circ is the last circ in the chain and an update is warranted.
1701 UPDATE action.usr_circ_history
1703 due_date = NEW.due_date,
1704 checkin_time = NEW.checkin_time
1706 source_circ = first_circ
1708 due_date <> NEW.due_date OR (
1709 (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR
1710 (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR
1711 (checkin_time <> NEW.checkin_time)
1716 $FUNK$ LANGUAGE PLPGSQL;
1718 CREATE TRIGGER maintain_usr_circ_history_tgr
1719 AFTER INSERT OR UPDATE ON action.circulation
1720 FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history();
1722 CREATE OR REPLACE VIEW action.all_circulation_combined_types AS
1723 SELECT acirc.id AS id,
1728 ac_acirc.circ_modifier AS item_type,
1729 'regular_circ'::text AS circ_type
1730 FROM action.circulation acirc,
1732 WHERE acirc.target_copy = ac_acirc.id
1734 SELECT ancc.id::BIGINT AS id,
1735 ancc.circ_time AS xact_start,
1737 ancc.staff AS circ_staff,
1738 ancc.circ_time AS create_time,
1739 cnct_ancc.name AS item_type,
1740 'non-cat_circ'::text AS circ_type
1741 FROM action.non_cataloged_circulation ancc,
1742 config.non_cataloged_type cnct_ancc
1743 WHERE ancc.item_type = cnct_ancc.id
1745 SELECT aihu.id::BIGINT AS id,
1746 aihu.use_time AS xact_start,
1747 aihu.org_unit AS circ_lib,
1748 aihu.staff AS circ_staff,
1749 aihu.use_time AS create_time,
1750 ac_aihu.circ_modifier AS item_type,
1751 'in-house_use'::text AS circ_type
1752 FROM action.in_house_use aihu,
1754 WHERE aihu.item = ac_aihu.id
1756 SELECT ancihu.id::BIGINT AS id,
1757 ancihu.use_time AS xact_start,
1758 ancihu.org_unit AS circ_lib,
1759 ancihu.staff AS circ_staff,
1760 ancihu.use_time AS create_time,
1761 cnct_ancihu.name AS item_type,
1762 'non-cat-in-house_use'::text AS circ_type
1763 FROM action.non_cat_in_house_use ancihu,
1764 config.non_cataloged_type cnct_ancihu
1765 WHERE ancihu.item_type = cnct_ancihu.id
1767 SELECT aacirc.id AS id,
1772 ac_aacirc.circ_modifier AS item_type,
1773 'aged_circ'::text AS circ_type
1774 FROM action.aged_circulation aacirc,
1775 asset.copy ac_aacirc
1776 WHERE aacirc.target_copy = ac_aacirc.id;
1778 CREATE TABLE action.curbside (
1779 id SERIAL PRIMARY KEY,
1780 patron INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1781 org INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1784 stage_staff INT REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1785 arrival TIMESTAMPTZ,
1786 delivered TIMESTAMPTZ,
1787 delivery_staff INT REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1791 CREATE TABLE action.batch_hold_event (
1792 id SERIAL PRIMARY KEY,
1793 staff INT NOT NULL REFERENCES actor.usr (id) ON UPDATE CASCADE ON DELETE CASCADE,
1794 bucket INT NOT NULL REFERENCES container.user_bucket (id) ON UPDATE CASCADE ON DELETE CASCADE,
1795 target INT NOT NULL,
1796 hold_type TEXT NOT NULL DEFAULT 'T', -- maybe different hold types in the future...
1797 run_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1798 cancelled TIMESTAMP WITH TIME ZONE
1801 CREATE TABLE action.batch_hold_event_map (
1802 id SERIAL PRIMARY KEY,
1803 batch_hold_event INT NOT NULL REFERENCES action.batch_hold_event (id) ON UPDATE CASCADE ON DELETE CASCADE,
1804 hold INT NOT NULL REFERENCES action.hold_request (id) ON UPDATE CASCADE ON DELETE CASCADE
1807 CREATE TABLE action.ingest_queue (
1808 id SERIAL PRIMARY KEY,
1809 created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
1810 run_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
1811 who INT REFERENCES actor.usr (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
1812 start_time TIMESTAMPTZ,
1813 end_time TIMESTAMPTZ,
1818 CREATE TABLE action.ingest_queue_entry (
1819 id BIGSERIAL PRIMARY KEY,
1820 record BIGINT NOT NULL, -- points to a record id of the appropriate record_type
1821 record_type TEXT NOT NULL,
1822 action TEXT NOT NULL,
1823 run_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
1824 state_data TEXT NOT NULL DEFAULT '',
1825 queue INT REFERENCES action.ingest_queue (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1826 override_by BIGINT REFERENCES action.ingest_queue_entry (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
1827 ingest_time TIMESTAMPTZ,
1828 fail_time TIMESTAMPTZ
1830 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;
1831 CREATE INDEX entry_override_by_idx ON action.ingest_queue_entry (override_by) WHERE override_by IS NOT NULL;
1833 CREATE OR REPLACE FUNCTION action.enqueue_ingest_entry (
1835 rtype TEXT DEFAULT 'biblio',
1836 when_to_run TIMESTAMPTZ DEFAULT NOW(),
1837 queue_id INT DEFAULT NULL,
1838 ingest_action TEXT DEFAULT 'update', -- will be the most common?
1839 old_state_data TEXT DEFAULT ''
1840 ) RETURNS BOOL AS $F$
1842 new_entry action.ingest_queue_entry%ROWTYPE;
1843 prev_del_entry action.ingest_queue_entry%ROWTYPE;
1848 IF ingest_action = 'delete' THEN
1849 -- first see if there is an outstanding entry
1850 SELECT * INTO prev_del_entry
1851 FROM action.ingest_queue_entry
1852 WHERE qe.record = record_id
1853 AND qe.state_date = old_state_data
1854 AND qe.record_type = rtype
1855 AND qe.ingest_time IS NULL
1856 AND qe.override_by IS NULL;
1859 WITH existing_queue_entry_cte AS (
1860 SELECT queue_id AS queue,
1861 rtype AS record_type,
1862 record_id AS record,
1863 qe.id AS override_by,
1864 ingest_action AS action,
1866 old_state_data AS state_data
1867 FROM action.ingest_queue_entry qe
1868 JOIN action.ingest_queue q ON (qe.queue = q.id)
1869 WHERE qe.record = record_id
1870 AND q.end_time IS NULL
1871 AND qe.record_type = rtype
1872 AND qe.state_data = old_state_data
1873 AND qe.ingest_time IS NULL
1874 AND qe.fail_time IS NULL
1875 AND qe.override_by IS NULL
1876 ), existing_nonqueue_entry_cte AS (
1877 SELECT queue_id AS queue,
1878 rtype AS record_type,
1879 record_id AS record,
1880 qe.id AS override_by,
1881 ingest_action AS action,
1882 qe.run_at AS run_at,
1883 old_state_data AS state_data
1884 FROM action.ingest_queue_entry qe
1885 WHERE qe.record = record_id
1886 AND qe.queue IS NULL
1887 AND qe.record_type = rtype
1888 AND qe.state_data = old_state_data
1889 AND qe.ingest_time IS NULL
1890 AND qe.fail_time IS NULL
1891 AND qe.override_by IS NULL
1892 ), new_entry_cte AS (
1893 SELECT * FROM existing_queue_entry_cte
1895 SELECT * FROM existing_nonqueue_entry_cte
1897 SELECT queue_id, rtype, record_id, NULL, ingest_action, COALESCE(when_to_run,NOW()), old_state_data
1898 ), insert_entry_cte AS (
1899 INSERT INTO action.ingest_queue_entry
1900 (queue, record_type, record, override_by, action, run_at, state_data)
1901 SELECT queue, record_type, record, override_by, action, run_at, state_data FROM new_entry_cte
1902 ORDER BY 4 NULLS LAST, 6
1905 ) SELECT * INTO new_entry FROM insert_entry_cte;
1907 IF prev_del_entry.id IS NOT NULL THEN -- later delete overrides earlier unapplied entry
1908 UPDATE action.ingest_queue_entry
1909 SET override_by = new_entry.id
1910 WHERE id = prev_del_entry.id;
1912 UPDATE action.ingest_queue_entry
1913 SET override_by = NULL
1914 WHERE id = new_entry.id;
1916 ELSIF new_entry.override_by IS NOT NULL THEN
1917 RETURN TRUE; -- already handled, don't notify
1920 NOTIFY queued_ingest;
1923 EXCEPTION WHEN OTHERS THEN
1924 GET STACKED DIAGNOSTICS diag_detail = PG_EXCEPTION_DETAIL,
1925 diag_context = PG_EXCEPTION_CONTEXT;
1926 RAISE WARNING '%\n%', diag_detail, diag_context;
1929 $F$ LANGUAGE PLPGSQL;
1931 CREATE OR REPLACE FUNCTION action.process_ingest_queue_entry (qeid BIGINT) RETURNS BOOL AS $func$
1933 ingest_success BOOL := NULL;
1934 qe action.ingest_queue_entry%ROWTYPE;
1935 aid authority.record_entry.id%TYPE;
1938 SELECT * INTO qe FROM action.ingest_queue_entry WHERE id = qeid;
1939 IF qe.ingest_time IS NOT NULL OR qe.override_by IS NOT NULL THEN
1940 RETURN TRUE; -- Already done
1943 IF qe.action = 'delete' THEN
1944 IF qe.record_type = 'biblio' THEN
1945 SELECT metabib.indexing_delete(r.*, qe.state_data) INTO ingest_success FROM biblio.record_entry r WHERE r.id = qe.record;
1946 ELSIF qe.record_type = 'authority' THEN
1947 SELECT authority.indexing_delete(r.*, qe.state_data) INTO ingest_success FROM authority.record_entry r WHERE r.id = qe.record;
1950 IF qe.record_type = 'biblio' THEN
1951 IF qe.action = 'propagate' THEN
1952 SELECT authority.apply_propagate_changes(qe.state_data::BIGINT, qe.record) INTO aid;
1953 SELECT aid = qe.state_data::BIGINT INTO ingest_success;
1955 SELECT metabib.indexing_update(r.*, qe.action = 'insert', qe.state_data) INTO ingest_success FROM biblio.record_entry r WHERE r.id = qe.record;
1957 ELSIF qe.record_type = 'authority' THEN
1958 SELECT authority.indexing_update(r.*, qe.action = 'insert', qe.state_data) INTO ingest_success FROM authority.record_entry r WHERE r.id = qe.record;
1962 IF NOT ingest_success THEN
1963 UPDATE action.ingest_queue_entry SET fail_time = NOW() WHERE id = qe.id;
1964 PERFORM * FROM config.internal_flag WHERE name = 'ingest.queued.abort_on_error' AND enabled;
1966 RAISE EXCEPTION 'Ingest action of % on %.record_entry % for queue entry % failed', qe.action, qe.record_type, qe.record, qe.id;
1968 RAISE WARNING 'Ingest action of % on %.record_entry % for queue entry % failed', qe.action, qe.record_type, qe.record, qe.id;
1971 UPDATE action.ingest_queue_entry SET ingest_time = NOW() WHERE id = qe.id;
1974 RETURN ingest_success;
1976 $func$ LANGUAGE PLPGSQL;
1978 CREATE OR REPLACE FUNCTION action.complete_duplicated_entries () RETURNS TRIGGER AS $F$
1980 IF NEW.ingest_time IS NOT NULL THEN
1981 UPDATE action.ingest_queue_entry SET ingest_time = NEW.ingest_time WHERE override_by = NEW.id;
1986 $F$ LANGUAGE PLPGSQL;
1988 CREATE TRIGGER complete_duplicated_entries_trigger
1989 AFTER UPDATE ON action.ingest_queue_entry
1990 FOR EACH ROW WHEN (NEW.override_by IS NULL)
1991 EXECUTE PROCEDURE action.complete_duplicated_entries();
1993 CREATE OR REPLACE FUNCTION action.set_ingest_queue(INT) RETURNS VOID AS $$
1994 $_SHARED{"ingest_queue_id"} = $_[0];
1995 $$ LANGUAGE plperlu;
1997 CREATE OR REPLACE FUNCTION action.get_ingest_queue() RETURNS INT AS $$
1998 return $_SHARED{"ingest_queue_id"};
1999 $$ LANGUAGE plperlu;
2001 CREATE OR REPLACE FUNCTION action.clear_ingest_queue() RETURNS VOID AS $$
2002 delete($_SHARED{"ingest_queue_id"});
2003 $$ LANGUAGE plperlu;
2005 CREATE OR REPLACE FUNCTION action.set_queued_ingest_force(TEXT) RETURNS VOID AS $$
2006 $_SHARED{"ingest_queue_force"} = $_[0];
2007 $$ LANGUAGE plperlu;
2009 CREATE OR REPLACE FUNCTION action.get_queued_ingest_force() RETURNS TEXT AS $$
2010 return $_SHARED{"ingest_queue_force"};
2011 $$ LANGUAGE plperlu;
2013 CREATE OR REPLACE FUNCTION action.clear_queued_ingest_force() RETURNS VOID AS $$
2014 delete($_SHARED{"ingest_queue_force"});
2015 $$ LANGUAGE plperlu;
2017 CREATE OR REPLACE FUNCTION authority.propagate_changes
2018 (aid BIGINT, bid BIGINT) RETURNS BIGINT AS $func$
2020 queuing_success BOOL := FALSE;
2023 PERFORM 1 FROM config.global_flag
2024 WHERE name IN ('ingest.queued.all','ingest.queued.authority.propagate')
2028 -- XXX enqueue special 'propagate' bib action
2029 SELECT action.enqueue_ingest_entry( bid, 'biblio', NOW(), NULL, 'propagate', aid::TEXT) INTO queuing_success;
2031 IF queuing_success THEN
2036 PERFORM authority.apply_propagate_changes(aid, bid);
2039 $func$ LANGUAGE PLPGSQL;
2041 CREATE OR REPLACE FUNCTION authority.apply_propagate_changes
2042 (aid BIGINT, bid BIGINT) RETURNS BIGINT AS $func$
2044 bib_forced BOOL := FALSE;
2045 bib_rec biblio.record_entry%ROWTYPE;
2049 SELECT INTO bib_rec * FROM biblio.record_entry WHERE id = bid;
2051 new_marc := vandelay.merge_record_xml(
2052 bib_rec.marc, authority.generate_overlay_template(aid));
2054 IF new_marc = bib_rec.marc THEN
2055 -- Authority record change had no impact on this bib record.
2056 -- Nothing left to do.
2060 PERFORM 1 FROM config.global_flag
2061 WHERE name = 'ingest.disable_authority_auto_update_bib_meta'
2065 -- update the bib record editor and edit_date
2067 SELECT editor FROM authority.record_entry WHERE id = aid);
2068 bib_rec.edit_date = NOW();
2071 PERFORM action.set_queued_ingest_force('ingest.queued.biblio.update.disabled');
2073 UPDATE biblio.record_entry SET
2075 editor = bib_rec.editor,
2076 edit_date = bib_rec.edit_date
2079 PERFORM action.clear_queued_ingest_force();
2084 $func$ LANGUAGE PLPGSQL;
2086 CREATE OR REPLACE FUNCTION evergreen.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
2088 old_state_data TEXT := '';
2091 queuing_flag_name TEXT;
2092 queuing_flag BOOL := FALSE;
2093 queuing_success BOOL := FALSE;
2094 ingest_success BOOL := FALSE;
2098 -- Identify the ingest action type
2099 IF TG_OP = 'UPDATE' THEN
2101 -- Gather type-specific data for later use
2102 IF TG_TABLE_SCHEMA = 'authority' THEN
2103 old_state_data = OLD.heading;
2106 IF NOT OLD.deleted THEN -- maybe reingest?
2108 new_action = 'delete'; -- nope, delete
2110 new_action = 'update'; -- yes, update
2112 ELSIF NOT NEW.deleted THEN
2113 new_action = 'insert'; -- revivify, AKA insert
2115 RETURN NEW; -- was and is still deleted, don't ingest
2117 ELSIF TG_OP = 'INSERT' THEN
2118 new_action = 'insert'; -- brand new
2120 RETURN OLD; -- really deleting the record
2123 queuing_flag_name := 'ingest.queued.'||TG_TABLE_SCHEMA||'.'||new_action;
2124 -- See if we should be queuing anything
2125 SELECT enabled INTO queuing_flag
2126 FROM config.internal_flag
2127 WHERE name IN ('ingest.queued.all','ingest.queued.'||TG_TABLE_SCHEMA||'.all', queuing_flag_name)
2131 SELECT action.get_queued_ingest_force() INTO queuing_force;
2132 IF queuing_flag IS NULL AND queuing_force = queuing_flag_name THEN
2133 queuing_flag := TRUE;
2136 -- you (or part of authority propagation) can forcibly disable specific queuing actions
2137 IF queuing_force = queuing_flag_name||'.disabled' THEN
2138 queuing_flag := FALSE;
2141 -- And if we should be queuing ...
2142 IF queuing_flag THEN
2143 ingest_queue := action.get_ingest_queue();
2145 -- ... but this is NOT a named or forced queue request (marc editor update, say, or vandelay overlay)...
2146 IF queuing_force IS NULL AND ingest_queue IS NULL AND new_action = 'update' THEN -- re-ingest?
2148 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
2150 -- ... then don't do anything if ingest.reingest.force_on_same_marc is not enabled and the MARC hasn't changed
2151 IF NOT FOUND AND OLD.marc = NEW.marc THEN
2156 -- Otherwise, attempt to enqueue
2157 SELECT action.enqueue_ingest_entry( NEW.id, TG_TABLE_SCHEMA, NOW(), ingest_queue, new_action, old_state_data) INTO queuing_success;
2160 -- If queuing was not requested, or failed for some reason, do it live.
2161 IF NOT queuing_success THEN
2162 IF queuing_flag THEN
2163 RAISE WARNING 'Enqueuing of %.record_entry % for ingest failed, attempting direct ingest', TG_TABLE_SCHEMA, NEW.id;
2166 IF new_action = 'delete' THEN
2167 IF TG_TABLE_SCHEMA = 'biblio' THEN
2168 SELECT metabib.indexing_delete(NEW.*, old_state_data) INTO ingest_success;
2169 ELSIF TG_TABLE_SCHEMA = 'authority' THEN
2170 SELECT authority.indexing_delete(NEW.*, old_state_data) INTO ingest_success;
2173 IF TG_TABLE_SCHEMA = 'biblio' THEN
2174 SELECT metabib.indexing_update(NEW.*, new_action = 'insert', old_state_data) INTO ingest_success;
2175 ELSIF TG_TABLE_SCHEMA = 'authority' THEN
2176 SELECT authority.indexing_update(NEW.*, new_action = 'insert', old_state_data) INTO ingest_success;
2180 IF NOT ingest_success THEN
2181 PERFORM * FROM config.internal_flag WHERE name = 'ingest.queued.abort_on_error' AND enabled;
2183 RAISE EXCEPTION 'Ingest of %.record_entry % failed', TG_TABLE_SCHEMA, NEW.id;
2185 RAISE WARNING 'Ingest of %.record_entry % failed', TG_TABLE_SCHEMA, NEW.id;
2192 $func$ LANGUAGE PLPGSQL;
2194 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 ();
2195 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 ();