2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
18 DROP SCHEMA IF EXISTS action CASCADE;
24 CREATE TABLE action.in_house_use (
25 id SERIAL PRIMARY KEY,
26 item BIGINT NOT NULL, -- REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
27 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
28 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
29 use_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
31 CREATE INDEX action_in_house_use_staff_idx ON action.in_house_use ( staff );
33 CREATE TABLE action.non_cataloged_circulation (
34 id SERIAL PRIMARY KEY,
35 patron INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
36 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
37 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
38 item_type INT NOT NULL REFERENCES config.non_cataloged_type (id) DEFERRABLE INITIALLY DEFERRED,
39 circ_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
41 CREATE INDEX action_non_cat_circ_patron_idx ON action.non_cataloged_circulation ( patron );
42 CREATE INDEX action_non_cat_circ_staff_idx ON action.non_cataloged_circulation ( staff );
44 CREATE TABLE action.non_cat_in_house_use (
45 id SERIAL PRIMARY KEY,
46 item_type BIGINT NOT NULL REFERENCES config.non_cataloged_type(id) DEFERRABLE INITIALLY DEFERRED,
47 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
48 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
49 use_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
51 CREATE INDEX non_cat_in_house_use_staff_idx ON action.non_cat_in_house_use ( staff );
53 CREATE TABLE action.survey (
54 id SERIAL PRIMARY KEY,
55 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
56 start_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
57 end_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() + '10 years'::INTERVAL,
58 usr_summary BOOL NOT NULL DEFAULT FALSE,
59 opac BOOL NOT NULL DEFAULT FALSE,
60 poll BOOL NOT NULL DEFAULT FALSE,
61 required BOOL NOT NULL DEFAULT FALSE,
63 description TEXT NOT NULL
65 CREATE UNIQUE INDEX asv_once_per_owner_idx ON action.survey (owner,name);
67 CREATE TABLE action.survey_question (
68 id SERIAL PRIMARY KEY,
69 survey INT NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED,
70 question TEXT NOT NULL
73 CREATE TABLE action.survey_answer (
74 id SERIAL PRIMARY KEY,
75 question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
79 CREATE SEQUENCE action.survey_response_group_id_seq;
81 CREATE TABLE action.survey_response (
82 id BIGSERIAL PRIMARY KEY,
83 response_group_id INT,
84 usr INT, -- REFERENCES actor.usr
85 survey INT NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED,
86 question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
87 answer INT NOT NULL REFERENCES action.survey_answer DEFERRABLE INITIALLY DEFERRED,
88 answer_date TIMESTAMP WITH TIME ZONE,
89 effective_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
91 CREATE INDEX action_survey_response_usr_idx ON action.survey_response ( usr );
93 CREATE OR REPLACE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS '
95 NEW.answer_date := NOW();
99 CREATE TRIGGER action_survey_response_answer_date_fixup_tgr
100 BEFORE INSERT ON action.survey_response
102 EXECUTE PROCEDURE action.survey_response_answer_date_fixup ();
104 CREATE TABLE action.archive_actor_stat_cat (
105 id BIGSERIAL PRIMARY KEY,
106 xact BIGINT NOT NULL, -- action.circulation (+aged/all)
107 stat_cat INT NOT NULL,
111 CREATE TABLE action.archive_asset_stat_cat (
112 id BIGSERIAL PRIMARY KEY,
113 xact BIGINT NOT NULL, -- action.circulation (+aged/all)
114 stat_cat INT NOT NULL,
119 CREATE TABLE action.circulation (
120 target_copy BIGINT NOT NULL, -- asset.copy.id
121 circ_lib INT NOT NULL, -- actor.org_unit.id
122 circ_staff INT NOT NULL, -- actor.usr.id
123 checkin_staff INT, -- actor.usr.id
124 checkin_lib INT, -- actor.org_unit.id
125 renewal_remaining INT NOT NULL, -- derived from "circ duration" rule
126 grace_period INTERVAL NOT NULL, -- derived from "circ fine" rule
127 due_date TIMESTAMP WITH TIME ZONE,
128 stop_fines_time TIMESTAMP WITH TIME ZONE,
129 checkin_time TIMESTAMP WITH TIME ZONE,
130 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
131 duration INTERVAL, -- derived from "circ duration" rule
132 fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
133 recurring_fine NUMERIC(6,2), -- derived from "circ fine" rule
134 max_fine NUMERIC(6,2), -- derived from "max fine" rule
135 phone_renewal BOOL NOT NULL DEFAULT FALSE,
136 desk_renewal BOOL NOT NULL DEFAULT FALSE,
137 opac_renewal BOOL NOT NULL DEFAULT FALSE,
138 duration_rule TEXT NOT NULL, -- name of "circ duration" rule
139 recurring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule
140 max_fine_rule TEXT NOT NULL, -- name of "max fine" rule
141 stop_fines TEXT CHECK (stop_fines IN (
142 'CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE','CLAIMSNEVERCHECKEDOUT')),
143 workstation INT REFERENCES actor.workstation(id)
145 DEFERRABLE INITIALLY DEFERRED,
146 checkin_workstation INT REFERENCES actor.workstation(id)
148 DEFERRABLE INITIALLY DEFERRED,
149 copy_location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
150 checkin_scan_time TIMESTAMP WITH TIME ZONE
151 ) INHERITS (money.billable_xact);
152 ALTER TABLE action.circulation ADD PRIMARY KEY (id);
153 ALTER TABLE action.circulation
154 ADD COLUMN parent_circ BIGINT
155 REFERENCES action.circulation( id )
156 DEFERRABLE INITIALLY DEFERRED;
157 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
158 CREATE INDEX circ_outstanding_idx ON action.circulation (usr) WHERE checkin_time IS NULL;
159 CREATE INDEX circ_checkin_time ON "action".circulation (checkin_time) WHERE checkin_time IS NOT NULL;
160 CREATE INDEX circ_circ_lib_idx ON "action".circulation (circ_lib);
161 CREATE INDEX circ_open_date_idx ON "action".circulation (xact_start) WHERE xact_finish IS NULL;
162 CREATE INDEX circ_all_usr_idx ON action.circulation ( usr );
163 CREATE INDEX circ_circ_staff_idx ON action.circulation ( circ_staff );
164 CREATE INDEX circ_checkin_staff_idx ON action.circulation ( checkin_staff );
165 CREATE INDEX action_circulation_target_copy_idx ON action.circulation (target_copy);
166 CREATE UNIQUE INDEX circ_parent_idx ON action.circulation ( parent_circ ) WHERE parent_circ IS NOT NULL;
167 CREATE UNIQUE INDEX only_one_concurrent_checkout_per_copy ON action.circulation(target_copy) WHERE checkin_time IS NULL;
169 CREATE TRIGGER action_circulation_target_copy_trig AFTER INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
171 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation');
172 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
173 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
175 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
177 IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 THEN
178 NEW.due_date = (NEW.due_date::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL)::TIMESTAMPTZ;
185 CREATE TRIGGER push_due_date_tgr BEFORE INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.push_circ_due_time();
187 CREATE OR REPLACE FUNCTION action.fill_circ_copy_location () RETURNS TRIGGER AS $$
189 SELECT INTO NEW.copy_location location FROM asset.copy WHERE id = NEW.target_copy;
194 CREATE TRIGGER fill_circ_copy_location_tgr BEFORE INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.fill_circ_copy_location();
196 CREATE OR REPLACE FUNCTION action.archive_stat_cats () RETURNS TRIGGER AS $$
198 INSERT INTO action.archive_actor_stat_cat(xact, stat_cat, value)
199 SELECT NEW.id, asceum.stat_cat, asceum.stat_cat_entry
200 FROM actor.stat_cat_entry_usr_map asceum
201 JOIN actor.stat_cat sc ON asceum.stat_cat = sc.id
202 WHERE NEW.usr = asceum.target_usr AND sc.checkout_archive;
203 INSERT INTO action.archive_asset_stat_cat(xact, stat_cat, value)
204 SELECT NEW.id, ascecm.stat_cat, asce.value
205 FROM asset.stat_cat_entry_copy_map ascecm
206 JOIN asset.stat_cat sc ON ascecm.stat_cat = sc.id
207 JOIN asset.stat_cat_entry asce ON ascecm.stat_cat_entry = asce.id
208 WHERE NEW.target_copy = ascecm.owning_copy AND sc.checkout_archive;
213 CREATE TRIGGER archive_stat_cats_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.archive_stat_cats();
215 CREATE TABLE action.aged_circulation (
217 usr_home_ou INT NOT NULL,
218 usr_profile INT NOT NULL,
220 copy_call_number INT NOT NULL,
221 copy_owning_lib INT NOT NULL,
222 copy_circ_lib INT NOT NULL,
223 copy_bib_record BIGINT NOT NULL,
224 LIKE action.circulation
227 ALTER TABLE action.aged_circulation ADD PRIMARY KEY (id);
228 ALTER TABLE action.aged_circulation DROP COLUMN usr;
229 CREATE INDEX aged_circ_circ_lib_idx ON "action".aged_circulation (circ_lib);
230 CREATE INDEX aged_circ_start_idx ON "action".aged_circulation (xact_start);
231 CREATE INDEX aged_circ_copy_circ_lib_idx ON "action".aged_circulation (copy_circ_lib);
232 CREATE INDEX aged_circ_copy_owning_lib_idx ON "action".aged_circulation (copy_owning_lib);
233 CREATE INDEX aged_circ_copy_location_idx ON "action".aged_circulation (copy_location);
234 CREATE INDEX action_aged_circulation_target_copy_idx ON action.aged_circulation (target_copy);
236 CREATE OR REPLACE VIEW action.all_circulation AS
237 SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
238 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
239 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
240 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
241 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
242 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
243 FROM action.aged_circulation
245 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,
246 cp.call_number AS copy_call_number, circ.copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
247 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
248 circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
249 circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
250 circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
252 FROM action.circulation circ
253 JOIN asset.copy cp ON (circ.target_copy = cp.id)
254 JOIN asset.call_number cn ON (cp.call_number = cn.id)
255 JOIN actor.usr p ON (circ.usr = p.id)
256 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
257 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
259 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
264 -- If there are any renewals for this circulation, don't archive or delete
265 -- it yet. We'll do so later, when we archive and delete the renewals.
267 SELECT 'Y' INTO found
268 FROM action.circulation
269 WHERE parent_circ = OLD.id
273 RETURN NULL; -- don't delete
276 -- Archive a copy of the old row to action.aged_circulation
278 INSERT INTO action.aged_circulation
279 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
280 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
281 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
282 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
283 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
284 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
286 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
287 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
288 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
289 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
290 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
291 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
292 FROM action.all_circulation WHERE id = OLD.id;
296 $$ LANGUAGE 'plpgsql';
298 CREATE TRIGGER action_circulation_aging_tgr
299 BEFORE DELETE ON action.circulation
301 EXECUTE PROCEDURE action.age_circ_on_delete ();
304 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
307 -- Having deleted a renewal, we can delete the original circulation (or a previous
308 -- renewal, if that's what parent_circ is pointing to). That deletion will trigger
309 -- deletion of any prior parents, etc. recursively.
311 IF OLD.parent_circ IS NOT NULL THEN
312 DELETE FROM action.circulation
313 WHERE id = OLD.parent_circ;
318 $$ LANGUAGE 'plpgsql';
320 CREATE TRIGGER age_parent_circ
321 AFTER DELETE ON action.circulation
323 EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
326 CREATE OR REPLACE VIEW action.open_circulation AS
328 FROM action.circulation
329 WHERE checkin_time IS NULL
333 CREATE OR REPLACE VIEW action.billable_circulations AS
335 FROM action.circulation
336 WHERE xact_finish IS NULL;
338 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
340 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
341 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
342 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
344 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
345 UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
347 IF NEW.stop_fines = 'LOST' THEN
348 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
353 $$ LANGUAGE 'plpgsql';
354 CREATE TRIGGER action_circulation_stop_fines_tgr
355 BEFORE UPDATE ON action.circulation
357 EXECUTE PROCEDURE action.circulation_claims_returned ();
359 CREATE TABLE action.hold_request_cancel_cause (
360 id SERIAL PRIMARY KEY,
364 CREATE TABLE action.hold_request (
365 id SERIAL PRIMARY KEY,
366 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
367 capture_time TIMESTAMP WITH TIME ZONE,
368 fulfillment_time TIMESTAMP WITH TIME ZONE,
369 checkin_time TIMESTAMP WITH TIME ZONE,
370 return_time TIMESTAMP WITH TIME ZONE,
371 prev_check_time TIMESTAMP WITH TIME ZONE,
372 expire_time TIMESTAMP WITH TIME ZONE,
373 cancel_time TIMESTAMP WITH TIME ZONE,
374 cancel_cause INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
376 target BIGINT NOT NULL, -- see hold_type
377 current_copy BIGINT, -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.unit now...
378 fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
379 fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
380 request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
381 requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
382 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
383 selection_ou INT NOT NULL,
384 selection_depth INT NOT NULL DEFAULT 0,
385 pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
386 hold_type TEXT NOT NULL, -- CHECK (hold_type IN ('M','T','V','C')), -- XXX constraint too constraining...
387 holdable_formats TEXT,
389 email_notify BOOL NOT NULL DEFAULT FALSE,
391 sms_carrier INT REFERENCES config.sms_carrier (id),
392 frozen BOOL NOT NULL DEFAULT FALSE,
393 thaw_date TIMESTAMP WITH TIME ZONE,
394 shelf_time TIMESTAMP WITH TIME ZONE,
396 mint_condition BOOL NOT NULL DEFAULT TRUE,
397 shelf_expire_time TIMESTAMPTZ,
398 current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
399 behind_desk BOOLEAN NOT NULL DEFAULT FALSE
401 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
403 OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
407 CREATE OR REPLACE FUNCTION action.hold_request_clear_map () RETURNS TRIGGER AS $$
409 DELETE FROM action.hold_copy_map WHERE hold = NEW.id;
414 CREATE TRIGGER hold_request_clear_map_tgr
415 AFTER UPDATE ON action.hold_request
418 (NEW.cancel_time IS NOT NULL AND OLD.cancel_time IS NULL)
419 OR (NEW.capture_time IS NOT NULL AND OLD.capture_time IS NULL)
421 EXECUTE PROCEDURE action.hold_request_clear_map();
423 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
424 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
425 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
426 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
427 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
428 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
429 CREATE INDEX hold_request_requestor_idx ON action.hold_request ( requestor );
430 CREATE INDEX hold_request_open_idx ON action.hold_request (id) WHERE cancel_time IS NULL AND fulfillment_time IS NULL;
431 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;
434 CREATE TABLE action.hold_request_note (
436 id BIGSERIAL PRIMARY KEY,
437 hold BIGINT NOT NULL REFERENCES action.hold_request (id)
439 DEFERRABLE INITIALLY DEFERRED,
442 slip BOOL NOT NULL DEFAULT FALSE,
443 pub BOOL NOT NULL DEFAULT FALSE,
444 staff BOOL NOT NULL DEFAULT FALSE -- created by staff
447 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
450 CREATE TABLE action.hold_notification (
451 id SERIAL PRIMARY KEY,
452 hold INT NOT NULL REFERENCES action.hold_request (id)
454 DEFERRABLE INITIALLY DEFERRED,
455 notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
456 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
457 method TEXT NOT NULL, -- email address or phone number
460 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
461 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
463 CREATE TABLE action.hold_copy_map (
464 id BIGSERIAL PRIMARY KEY,
465 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
466 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
468 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
470 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
471 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
473 CREATE TABLE action.transit_copy (
474 id SERIAL PRIMARY KEY,
475 source_send_time TIMESTAMP WITH TIME ZONE,
476 dest_recv_time TIMESTAMP WITH TIME ZONE,
477 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
478 source INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
479 dest INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
480 prev_hop INT REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
481 copy_status INT NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
482 persistant_transfer BOOL NOT NULL DEFAULT FALSE,
483 prev_dest INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
485 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
486 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
487 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
490 CREATE TABLE action.hold_transit_copy (
491 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
492 ) INHERITS (action.transit_copy);
493 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
494 -- 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
495 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
496 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
497 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
498 CREATE INDEX hold_transit_copy_hold_idx on action.hold_transit_copy (hold);
501 CREATE TABLE action.unfulfilled_hold_list (
502 id BIGSERIAL PRIMARY KEY,
503 current_copy BIGINT NOT NULL,
505 circ_lib INT NOT NULL,
506 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
508 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
510 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
514 FROM action.unfulfilled_hold_list u
515 JOIN asset.copy c ON (c.id = u.current_copy)
518 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
521 FROM action.unfulfilled_hold_loops
524 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
526 FROM action.unfulfilled_hold_loops l
527 JOIN action.unfulfilled_hold_min_loop m USING (hold)
528 WHERE l.count = m.min;
530 CREATE VIEW action.unfulfilled_hold_max_loop AS
533 FROM action.unfulfilled_hold_loops
537 CREATE TABLE action.aged_hold_request (
539 usr_home_ou INT NOT NULL,
540 usr_profile INT NOT NULL,
542 staff_placed BOOLEAN NOT NULL,
543 LIKE action.hold_request
545 ALTER TABLE action.aged_hold_request
546 ADD PRIMARY KEY (id),
548 DROP COLUMN requestor,
549 DROP COLUMN sms_carrier,
550 ALTER COLUMN phone_notify TYPE BOOLEAN
551 USING CASE WHEN phone_notify IS NULL OR phone_notify = '' THEN FALSE ELSE TRUE END,
552 ALTER COLUMN sms_notify TYPE BOOLEAN
553 USING CASE WHEN sms_notify IS NULL OR sms_notify = '' THEN FALSE ELSE TRUE END,
554 ALTER COLUMN phone_notify SET NOT NULL,
555 ALTER COLUMN sms_notify SET NOT NULL;
556 CREATE INDEX aged_hold_request_target_idx ON action.aged_hold_request (target);
557 CREATE INDEX aged_hold_request_pickup_lib_idx ON action.aged_hold_request (pickup_lib);
558 CREATE INDEX aged_hold_request_current_copy_idx ON action.aged_hold_request (current_copy);
559 CREATE INDEX aged_hold_request_fulfillment_staff_idx ON action.aged_hold_request ( fulfillment_staff );
561 CREATE OR REPLACE VIEW action.all_hold_request AS
563 COALESCE(a.post_code, b.post_code) AS usr_post_code,
564 p.home_ou AS usr_home_ou,
565 p.profile AS usr_profile,
566 EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
567 CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
571 ahr.fulfillment_time,
581 ahr.fulfillment_staff,
588 ahr.holdable_formats,
590 WHEN ahr.phone_notify IS NULL THEN FALSE
591 WHEN ahr.phone_notify = '' THEN FALSE
596 WHEN ahr.sms_notify IS NULL THEN FALSE
597 WHEN ahr.sms_notify = '' THEN FALSE
605 ahr.shelf_expire_time,
606 ahr.current_shelf_lib,
608 FROM action.hold_request ahr
609 JOIN actor.usr p ON (ahr.usr = p.id)
610 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
611 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
651 FROM action.aged_hold_request;
653 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
656 -- Archive a copy of the old row to action.aged_hold_request
658 INSERT INTO action.aged_hold_request
734 FROM action.all_hold_request WHERE id = OLD.id;
738 $$ LANGUAGE 'plpgsql';
740 CREATE TRIGGER action_hold_request_aging_tgr
741 BEFORE DELETE ON action.hold_request
743 EXECUTE PROCEDURE action.age_hold_on_delete ();
745 CREATE TABLE action.fieldset (
746 id SERIAL PRIMARY KEY,
747 owner INT NOT NULL REFERENCES actor.usr (id)
748 DEFERRABLE INITIALLY DEFERRED,
749 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
750 DEFERRABLE INITIALLY DEFERRED,
752 CONSTRAINT valid_status CHECK ( status in
753 ( 'PENDING', 'APPLIED', 'ERROR' )),
754 creation_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
755 scheduled_time TIMESTAMPTZ,
756 applied_time TIMESTAMPTZ,
757 classname TEXT NOT NULL, -- an IDL class name
759 stored_query INT REFERENCES query.stored_query (id)
760 DEFERRABLE INITIALLY DEFERRED,
762 CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
763 CONSTRAINT fieldset_one_or_the_other CHECK (
764 (stored_query IS NOT NULL AND pkey_value IS NULL) OR
765 (pkey_value IS NOT NULL AND stored_query IS NULL)
767 -- the CHECK constraint means we can update the fields for a single
768 -- row without all the extra overhead involved in a query
771 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
772 CREATE INDEX action_owner_idx ON action.fieldset( owner );
775 CREATE TABLE action.fieldset_col_val (
776 id SERIAL PRIMARY KEY,
777 fieldset INT NOT NULL REFERENCES action.fieldset
779 DEFERRABLE INITIALLY DEFERRED,
780 col TEXT NOT NULL, -- "field" from the idl ... the column on the table
781 val TEXT, -- value for the column ... NULL means, well, NULL
782 CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
786 -- represents a circ chain summary
787 CREATE TYPE action.circ_chain_summary AS (
789 start_time TIMESTAMP WITH TIME ZONE,
790 checkout_workstation TEXT,
791 last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
792 last_stop_fines TEXT,
793 last_stop_fines_time TIMESTAMP WITH TIME ZONE,
794 last_renewal_workstation TEXT, -- NULL if no renewals
795 last_checkin_workstation TEXT,
796 last_checkin_time TIMESTAMP WITH TIME ZONE,
797 last_checkin_scan_time TIMESTAMP WITH TIME ZONE
801 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id BIGINT ) RETURNS SETOF action.circulation AS $$
803 tmp_circ action.circulation%ROWTYPE;
804 circ_0 action.circulation%ROWTYPE;
807 SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
809 IF tmp_circ IS NULL THEN
810 RETURN NEXT tmp_circ;
814 -- find the front of the chain
816 SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
817 IF tmp_circ IS NULL THEN
823 -- now send the circs to the caller, oldest to newest
826 IF tmp_circ IS NULL THEN
829 RETURN NEXT tmp_circ;
830 SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
834 $$ LANGUAGE 'plpgsql';
836 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id BIGINT ) RETURNS action.circ_chain_summary AS $$
840 -- first circ in the chain
841 circ_0 action.circulation%ROWTYPE;
843 -- last circ in the chain
844 circ_n action.circulation%ROWTYPE;
846 -- circ chain under construction
847 chain action.circ_chain_summary;
848 tmp_circ action.circulation%ROWTYPE;
852 chain.num_circs := 0;
853 FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
855 IF chain.num_circs = 0 THEN
859 chain.num_circs := chain.num_circs + 1;
863 chain.start_time := circ_0.xact_start;
864 chain.last_stop_fines := circ_n.stop_fines;
865 chain.last_stop_fines_time := circ_n.stop_fines_time;
866 chain.last_checkin_time := circ_n.checkin_time;
867 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
868 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
869 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
871 IF chain.num_circs > 1 THEN
872 chain.last_renewal_time := circ_n.xact_start;
873 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
879 $$ LANGUAGE 'plpgsql';
881 -- Return the list of circ chain heads in xact_start order that the user has chosen to "retain"
882 CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$
884 c action.circulation%ROWTYPE;
886 usr_view_age actor.usr_setting%ROWTYPE;
887 usr_view_start actor.usr_setting%ROWTYPE;
889 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age';
890 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start';
892 IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN
893 -- User opted in and supplied a retention age
894 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
895 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
897 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
899 ELSIF usr_view_start.value IS NOT NULL THEN
901 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
903 -- User did not opt in
909 FROM action.circulation
911 AND parent_circ IS NULL
912 AND xact_start > NOW() - view_age
913 ORDER BY xact_start DESC
920 $func$ LANGUAGE PLPGSQL;
922 CREATE OR REPLACE FUNCTION action.usr_visible_circ_copies( INTEGER ) RETURNS SETOF BIGINT AS $$
923 SELECT DISTINCT(target_copy) FROM action.usr_visible_circs($1)
924 $$ LANGUAGE SQL ROWS 10;
926 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
928 h action.hold_request%ROWTYPE;
931 usr_view_count actor.usr_setting%ROWTYPE;
932 usr_view_age actor.usr_setting%ROWTYPE;
933 usr_view_start actor.usr_setting%ROWTYPE;
935 SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
936 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
937 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
941 FROM action.hold_request
943 AND fulfillment_time IS NULL
944 AND cancel_time IS NULL
945 ORDER BY request_time DESC
950 IF usr_view_start.value IS NULL THEN
954 IF usr_view_age.value IS NOT NULL THEN
955 -- User opted in and supplied a retention age
956 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
957 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
959 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
963 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
966 IF usr_view_count.value IS NOT NULL THEN
967 view_count := oils_json_to_text(usr_view_count.value)::INT;
972 -- show some fulfilled/canceled holds
975 FROM action.hold_request
977 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
978 AND COALESCE(fulfillment_time, cancel_time) > NOW() - view_age
979 ORDER BY COALESCE(fulfillment_time, cancel_time) DESC
987 $func$ LANGUAGE PLPGSQL;
989 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
991 usr_keep_age actor.usr_setting%ROWTYPE;
992 usr_keep_start actor.usr_setting%ROWTYPE;
993 org_keep_age INTERVAL;
994 org_use_last BOOL = false;
995 org_age_is_min BOOL = false;
1001 circ_chain_head action.circulation%ROWTYPE;
1002 circ_chain_tail action.circulation%ROWTYPE;
1007 last_finished TIMESTAMP WITH TIME ZONE;
1012 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
1014 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
1015 IF org_keep_count IS NULL THEN
1016 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
1019 SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
1020 SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
1022 -- First, find copies with more than keep_count non-renewal circs
1025 COUNT(*) AS total_real_circs
1026 FROM action.circulation
1027 WHERE parent_circ IS NULL
1028 AND xact_finish IS NOT NULL
1029 GROUP BY target_copy
1030 HAVING COUNT(*) > org_keep_count
1032 -- And, for those, select circs that are finished and older than keep_age
1033 FOR circ_chain_head IN
1034 -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
1035 -- The outer query then uses that information to skip the most recent set the library wants to keep
1036 -- End result is we don't care what order they come out in, as they are all potentials for deletion.
1037 SELECT ac.* FROM action.circulation ac JOIN (
1038 SELECT rank() OVER (ORDER BY xact_start DESC), ac.id
1039 FROM action.circulation ac
1040 WHERE ac.target_copy = target_acp.target_copy
1041 AND ac.parent_circ IS NULL
1042 ORDER BY ac.xact_start ) ranked USING (id)
1043 WHERE ranked.rank > org_keep_count
1046 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
1047 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);
1048 CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
1050 IF NOT org_use_last THEN
1051 last_finished := circ_chain_tail.xact_finish;
1054 -- Now get the user settings, if any, to block purging if the user wants to keep more circs
1055 usr_keep_age.value := NULL;
1056 SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';
1058 usr_keep_start.value := NULL;
1059 SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start';
1061 IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
1062 IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN
1063 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
1065 keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL;
1067 ELSIF usr_keep_start.value IS NOT NULL THEN
1068 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
1070 keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
1073 IF org_age_is_min THEN
1074 keep_age := GREATEST( keep_age, org_keep_age );
1077 CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
1079 -- We've passed the purging tests, purge the circ chain starting at the end
1080 -- A trigger should auto-purge the rest of the chain.
1081 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
1083 count_purged := count_purged + 1;
1088 return count_purged;
1090 $func$ LANGUAGE PLPGSQL;
1092 CREATE OR REPLACE FUNCTION action.purge_holds() RETURNS INT AS $func$
1094 current_hold RECORD;
1100 user_start TIMESTAMPTZ;
1105 SELECT INTO cgf_d value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age' AND enabled;
1106 SELECT INTO cgf_f value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_fulfilled' AND enabled;
1107 SELECT INTO cgf_c value::INTERVAL FROM config.global_flag WHERE name = 'history.hold.retention_age_canceled' AND enabled;
1110 rank() OVER (PARTITION BY usr ORDER BY COALESCE(fulfillment_time, cancel_time) DESC),
1111 cgf_cs.value::INTERVAL as cgf_cs,
1114 action.hold_request ahr
1115 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)
1117 (fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL)
1119 IF prev_usr IS NULL OR prev_usr != current_hold.usr THEN
1120 prev_usr := current_hold.usr;
1121 SELECT INTO user_start oils_json_to_text(value)::TIMESTAMPTZ FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_start';
1122 SELECT INTO user_age oils_json_to_text(value)::INTERVAL FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_age';
1123 SELECT INTO user_count oils_json_to_text(value)::INT FROM actor.usr_setting WHERE usr = prev_usr AND name = 'history.hold.retention_count';
1124 IF user_start IS NOT NULL THEN
1125 user_age := LEAST(user_age, AGE(NOW(), user_start));
1127 IF user_count IS NULL THEN
1128 user_count := 1000; -- Assumption based on the user visible holds routine
1131 -- Library keep age trumps user keep anything, for purposes of being able to hold on to things when staff canceled and such.
1132 IF current_hold.fulfillment_time IS NOT NULL AND current_hold.fulfillment_time > NOW() - COALESCE(cgf_f, cgf_d) THEN
1135 IF current_hold.cancel_time IS NOT NULL AND current_hold.cancel_time > NOW() - COALESCE(current_hold.cgf_cs, cgf_c, cgf_d) THEN
1139 -- User keep age needs combining with count. If too old AND within the count, keep!
1140 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
1144 -- All checks should have passed, delete!
1145 DELETE FROM action.hold_request WHERE id = current_hold.id;
1146 purged_holds := purged_holds + 1;
1148 RETURN purged_holds;
1150 $func$ LANGUAGE plpgsql;
1152 CREATE OR REPLACE FUNCTION action.apply_fieldset(
1153 fieldset_id IN INT, -- id from action.fieldset
1154 table_name IN TEXT, -- table to be updated
1155 pkey_name IN TEXT, -- name of primary key column in that table
1156 query IN TEXT -- query constructed by qstore (for query-based
1157 -- fieldsets only; otherwise null
1172 IF fieldset_id IS NULL THEN
1173 RETURN 'Fieldset ID parameter is NULL';
1175 IF table_name IS NULL THEN
1176 RETURN 'Table name parameter is NULL';
1178 IF pkey_name IS NULL THEN
1179 RETURN 'Primary key name parameter is NULL';
1182 statement := 'UPDATE ' || table_name || ' SET';
1186 quote_literal( pkey_value )
1195 IF fs_status IS NULL THEN
1196 RETURN 'No fieldset found for id = ' || fieldset_id;
1197 ELSIF fs_status = 'APPLIED' THEN
1198 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
1205 FROM action.fieldset_col_val
1206 WHERE fieldset = fieldset_id
1208 statement := statement || sep || ' ' || cv.col
1209 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
1214 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
1217 -- Add the WHERE clause. This differs according to whether it's a
1218 -- single-row fieldset or a query-based fieldset.
1220 IF query IS NULL AND fs_pkey_value IS NULL THEN
1221 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
1222 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
1223 fs_query := rtrim( query, ';' );
1224 statement := statement || ' WHERE ' || pkey_name || ' IN ( '
1225 || fs_query || ' );';
1226 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
1227 statement := statement || ' WHERE ' || pkey_name || ' = '
1228 || fs_pkey_value || ';';
1229 ELSE -- both are not null
1230 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
1233 -- Execute the update
1237 GET DIAGNOSTICS update_count = ROW_COUNT;
1239 IF UPDATE_COUNT > 0 THEN
1240 status_code := 'APPLIED';
1243 status_code := 'ERROR';
1244 msg := 'No eligible rows found for fieldset ' || fieldset_id;
1246 EXCEPTION WHEN OTHERS THEN
1247 status_code := 'ERROR';
1248 msg := 'Unable to apply fieldset ' || fieldset_id
1252 -- Update fieldset status
1254 UPDATE action.fieldset
1255 SET status = status_code,
1256 applied_time = now()
1257 WHERE id = fieldset_id;
1261 $$ LANGUAGE plpgsql;
1263 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
1264 Applies a specified fieldset, using a supplied table name and primary
1265 key name. The query parameter should be non-null only for
1266 query-based fieldsets.
1268 Returns NULL if successful, or an error message if not.
1271 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
1274 copy_context_ou INT DEFAULT NULL
1275 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
1276 -- support an "ahprox" measurement: adjust prox between copy circ lib and
1277 -- hold request lib, but I'm unsure whether to use this theoretical
1278 -- argument only in the baseline calculation or later in the other
1279 -- queries in this function.
1280 ) RETURNS NUMERIC AS $f$
1282 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
1283 ahr action.hold_request%ROWTYPE;
1284 acp asset.copy%ROWTYPE;
1285 acn asset.call_number%ROWTYPE;
1286 acl asset.copy_location%ROWTYPE;
1287 baseline_prox NUMERIC;
1297 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
1298 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
1299 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
1300 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
1302 IF copy_context_ou IS NULL THEN
1303 copy_context_ou := acp.circ_lib;
1306 -- First, gather the baseline proximity of "here" to pickup lib
1307 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
1309 -- Find any absolute adjustments, and set the baseline prox to that
1310 SELECT adj.* INTO aoupa
1311 FROM actor.org_unit_proximity_adjustment adj
1312 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1313 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1314 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acl_ol.id = adj.copy_location)
1315 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1316 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1317 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1318 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1319 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1320 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1321 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1322 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1323 absolute_adjustment AND
1324 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1326 COALESCE(acp_cl.distance,999)
1327 + COALESCE(acn_ol.distance,999)
1328 + COALESCE(acl_ol.distance,999)
1329 + COALESCE(ahr_pl.distance,999)
1330 + COALESCE(ahr_rl.distance,999),
1335 baseline_prox := aoupa.prox_adjustment;
1338 -- Now find any relative adjustments, and change the baseline prox based on them
1341 FROM actor.org_unit_proximity_adjustment adj
1342 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
1343 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
1344 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
1345 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
1346 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
1347 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
1348 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
1349 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
1350 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
1351 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
1352 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
1353 NOT absolute_adjustment AND
1354 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
1356 baseline_prox := baseline_prox + aoupa.prox_adjustment;
1359 RETURN baseline_prox;
1361 $f$ LANGUAGE PLPGSQL;
1363 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$
1365 NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy);
1368 $f$ LANGUAGE PLPGSQL;
1370 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 ();