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,
363 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (1,'Untargeted expiration');
364 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (2,'Hold Shelf expiration');
365 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (3,'Patron via phone');
366 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (4,'Patron in person');
367 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (5,'Staff forced');
368 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (6,'Patron via OPAC');
369 SELECT SETVAL('action.hold_request_cancel_cause_id_seq', 100);
371 CREATE TABLE action.hold_request (
372 id SERIAL PRIMARY KEY,
373 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
374 capture_time TIMESTAMP WITH TIME ZONE,
375 fulfillment_time TIMESTAMP WITH TIME ZONE,
376 checkin_time TIMESTAMP WITH TIME ZONE,
377 return_time TIMESTAMP WITH TIME ZONE,
378 prev_check_time TIMESTAMP WITH TIME ZONE,
379 expire_time TIMESTAMP WITH TIME ZONE,
380 cancel_time TIMESTAMP WITH TIME ZONE,
381 cancel_cause INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
383 target BIGINT NOT NULL, -- see hold_type
384 current_copy BIGINT, -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.unit now...
385 fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
386 fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
387 request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
388 requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
389 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
390 selection_ou INT NOT NULL,
391 selection_depth INT NOT NULL DEFAULT 0,
392 pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
393 hold_type TEXT NOT NULL, -- CHECK (hold_type IN ('M','T','V','C')), -- XXX constraint too constraining...
394 holdable_formats TEXT,
396 email_notify BOOL NOT NULL DEFAULT FALSE,
398 sms_carrier INT REFERENCES config.sms_carrier (id),
399 frozen BOOL NOT NULL DEFAULT FALSE,
400 thaw_date TIMESTAMP WITH TIME ZONE,
401 shelf_time TIMESTAMP WITH TIME ZONE,
403 mint_condition BOOL NOT NULL DEFAULT TRUE,
404 shelf_expire_time TIMESTAMPTZ,
405 current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED
407 ALTER TABLE action.hold_request ADD CONSTRAINT sms_check CHECK (
409 OR sms_carrier IS NOT NULL -- and implied sms_notify IS NOT NULL
413 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
414 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
415 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
416 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
417 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
418 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
419 CREATE INDEX hold_request_requestor_idx ON action.hold_request ( requestor );
422 CREATE TABLE action.hold_request_note (
424 id BIGSERIAL PRIMARY KEY,
425 hold BIGINT NOT NULL REFERENCES action.hold_request (id)
427 DEFERRABLE INITIALLY DEFERRED,
430 slip BOOL NOT NULL DEFAULT FALSE,
431 pub BOOL NOT NULL DEFAULT FALSE,
432 staff BOOL NOT NULL DEFAULT FALSE -- created by staff
435 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
438 CREATE TABLE action.hold_notification (
439 id SERIAL PRIMARY KEY,
440 hold INT NOT NULL REFERENCES action.hold_request (id)
442 DEFERRABLE INITIALLY DEFERRED,
443 notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
444 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
445 method TEXT NOT NULL, -- email address or phone number
448 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
449 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
451 CREATE TABLE action.hold_copy_map (
452 id BIGSERIAL PRIMARY KEY,
453 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
454 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
455 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
457 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
458 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
460 CREATE TABLE action.transit_copy (
461 id SERIAL PRIMARY KEY,
462 source_send_time TIMESTAMP WITH TIME ZONE,
463 dest_recv_time TIMESTAMP WITH TIME ZONE,
464 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
465 source INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
466 dest INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
467 prev_hop INT REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
468 copy_status INT NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
469 persistant_transfer BOOL NOT NULL DEFAULT FALSE,
470 prev_dest INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
472 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
473 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
474 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
477 CREATE TABLE action.hold_transit_copy (
478 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
479 ) INHERITS (action.transit_copy);
480 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
481 -- 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
482 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
483 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
484 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
487 CREATE TABLE action.unfulfilled_hold_list (
488 id BIGSERIAL PRIMARY KEY,
489 current_copy BIGINT NOT NULL,
491 circ_lib INT NOT NULL,
492 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
494 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
496 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
500 FROM action.unfulfilled_hold_list u
501 JOIN asset.copy c ON (c.id = u.current_copy)
504 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
507 FROM action.unfulfilled_hold_loops
510 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
512 FROM action.unfulfilled_hold_loops l
513 JOIN action.unfulfilled_hold_min_loop m USING (hold)
514 WHERE l.count = m.min;
516 CREATE VIEW action.unfulfilled_hold_max_loop AS
519 FROM action.unfulfilled_hold_loops
523 CREATE TABLE action.fieldset (
524 id SERIAL PRIMARY KEY,
525 owner INT NOT NULL REFERENCES actor.usr (id)
526 DEFERRABLE INITIALLY DEFERRED,
527 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
528 DEFERRABLE INITIALLY DEFERRED,
530 CONSTRAINT valid_status CHECK ( status in
531 ( 'PENDING', 'APPLIED', 'ERROR' )),
532 creation_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
533 scheduled_time TIMESTAMPTZ,
534 applied_time TIMESTAMPTZ,
535 classname TEXT NOT NULL, -- an IDL class name
537 stored_query INT REFERENCES query.stored_query (id)
538 DEFERRABLE INITIALLY DEFERRED,
540 CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
541 CONSTRAINT fieldset_one_or_the_other CHECK (
542 (stored_query IS NOT NULL AND pkey_value IS NULL) OR
543 (pkey_value IS NOT NULL AND stored_query IS NULL)
545 -- the CHECK constraint means we can update the fields for a single
546 -- row without all the extra overhead involved in a query
549 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
550 CREATE INDEX action_owner_idx ON action.fieldset( owner );
553 CREATE TABLE action.fieldset_col_val (
554 id SERIAL PRIMARY KEY,
555 fieldset INT NOT NULL REFERENCES action.fieldset
557 DEFERRABLE INITIALLY DEFERRED,
558 col TEXT NOT NULL, -- "field" from the idl ... the column on the table
559 val TEXT, -- value for the column ... NULL means, well, NULL
560 CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
564 -- represents a circ chain summary
565 CREATE TYPE action.circ_chain_summary AS (
567 start_time TIMESTAMP WITH TIME ZONE,
568 checkout_workstation TEXT,
569 last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
570 last_stop_fines TEXT,
571 last_stop_fines_time TIMESTAMP WITH TIME ZONE,
572 last_renewal_workstation TEXT, -- NULL if no renewals
573 last_checkin_workstation TEXT,
574 last_checkin_time TIMESTAMP WITH TIME ZONE,
575 last_checkin_scan_time TIMESTAMP WITH TIME ZONE
579 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id INTEGER ) RETURNS SETOF action.circulation AS $$
581 tmp_circ action.circulation%ROWTYPE;
582 circ_0 action.circulation%ROWTYPE;
585 SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
587 IF tmp_circ IS NULL THEN
588 RETURN NEXT tmp_circ;
592 -- find the front of the chain
594 SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
595 IF tmp_circ IS NULL THEN
601 -- now send the circs to the caller, oldest to newest
604 IF tmp_circ IS NULL THEN
607 RETURN NEXT tmp_circ;
608 SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
612 $$ LANGUAGE 'plpgsql';
614 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id INTEGER ) RETURNS action.circ_chain_summary AS $$
618 -- first circ in the chain
619 circ_0 action.circulation%ROWTYPE;
621 -- last circ in the chain
622 circ_n action.circulation%ROWTYPE;
624 -- circ chain under construction
625 chain action.circ_chain_summary;
626 tmp_circ action.circulation%ROWTYPE;
630 chain.num_circs := 0;
631 FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
633 IF chain.num_circs = 0 THEN
637 chain.num_circs := chain.num_circs + 1;
641 chain.start_time := circ_0.xact_start;
642 chain.last_stop_fines := circ_n.stop_fines;
643 chain.last_stop_fines_time := circ_n.stop_fines_time;
644 chain.last_checkin_time := circ_n.checkin_time;
645 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
646 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
647 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
649 IF chain.num_circs > 1 THEN
650 chain.last_renewal_time := circ_n.xact_start;
651 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
657 $$ LANGUAGE 'plpgsql';
659 -- Return the list of circ chain heads in xact_start order that the user has chosen to "retain"
660 CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$
662 c action.circulation%ROWTYPE;
664 usr_view_age actor.usr_setting%ROWTYPE;
665 usr_view_start actor.usr_setting%ROWTYPE;
667 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age';
668 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start';
670 IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN
671 -- User opted in and supplied a retention age
672 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
673 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
675 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
677 ELSIF usr_view_start.value IS NOT NULL THEN
679 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
681 -- User did not opt in
687 FROM action.circulation
689 AND parent_circ IS NULL
690 AND xact_start > NOW() - view_age
691 ORDER BY xact_start DESC
698 $func$ LANGUAGE PLPGSQL;
700 CREATE OR REPLACE FUNCTION action.usr_visible_circ_copies( INTEGER ) RETURNS SETOF BIGINT AS $$
701 SELECT DISTINCT(target_copy) FROM action.usr_visible_circs($1)
702 $$ LANGUAGE SQL ROWS 10;
704 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
706 h action.hold_request%ROWTYPE;
709 usr_view_count actor.usr_setting%ROWTYPE;
710 usr_view_age actor.usr_setting%ROWTYPE;
711 usr_view_start actor.usr_setting%ROWTYPE;
713 SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
714 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
715 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
719 FROM action.hold_request
721 AND fulfillment_time IS NULL
722 AND cancel_time IS NULL
723 ORDER BY request_time DESC
728 IF usr_view_start.value IS NULL THEN
732 IF usr_view_age.value IS NOT NULL THEN
733 -- User opted in and supplied a retention age
734 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
735 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
737 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
741 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
744 IF usr_view_count.value IS NOT NULL THEN
745 view_count := oils_json_to_text(usr_view_count.value)::INT;
750 -- show some fulfilled/canceled holds
753 FROM action.hold_request
755 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
756 AND request_time > NOW() - view_age
757 ORDER BY request_time DESC
765 $func$ LANGUAGE PLPGSQL;
767 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
769 usr_keep_age actor.usr_setting%ROWTYPE;
770 usr_keep_start actor.usr_setting%ROWTYPE;
771 org_keep_age INTERVAL;
777 circ_chain_head action.circulation%ROWTYPE;
778 circ_chain_tail action.circulation%ROWTYPE;
786 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
788 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
789 IF org_keep_count IS NULL THEN
790 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
793 -- First, find copies with more than keep_count non-renewal circs
796 COUNT(*) AS total_real_circs
797 FROM action.circulation
798 WHERE parent_circ IS NULL
799 AND xact_finish IS NOT NULL
801 HAVING COUNT(*) > org_keep_count
804 -- And, for those, select circs that are finished and older than keep_age
805 FOR circ_chain_head IN
807 FROM action.circulation
808 WHERE target_copy = target_acp.target_copy
809 AND parent_circ IS NULL
813 -- Stop once we've purged enough circs to hit org_keep_count
814 EXIT WHEN target_acp.total_real_circs - purge_position <= org_keep_count;
816 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
817 EXIT WHEN circ_chain_tail.xact_finish IS NULL;
819 -- Now get the user settings, if any, to block purging if the user wants to keep more circs
820 usr_keep_age.value := NULL;
821 SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';
823 usr_keep_start.value := NULL;
824 SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start';
826 IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
827 IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN
828 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
830 keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL;
832 ELSIF usr_keep_start.value IS NOT NULL THEN
833 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
835 keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTERVAL );
838 EXIT WHEN AGE(NOW(), circ_chain_tail.xact_finish) < keep_age;
840 -- We've passed the purging tests, purge the circ chain starting at the end
841 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
842 WHILE circ_chain_tail.parent_circ IS NOT NULL LOOP
843 SELECT * INTO circ_chain_tail FROM action.circulation WHERE id = circ_chain_tail.parent_circ;
844 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
847 count_purged := count_purged + 1;
848 purge_position := purge_position + 1;
853 $func$ LANGUAGE PLPGSQL;
856 CREATE OR REPLACE FUNCTION action.apply_fieldset(
857 fieldset_id IN INT, -- id from action.fieldset
858 table_name IN TEXT, -- table to be updated
859 pkey_name IN TEXT, -- name of primary key column in that table
860 query IN TEXT -- query constructed by qstore (for query-based
861 -- fieldsets only; otherwise null
876 IF fieldset_id IS NULL THEN
877 RETURN 'Fieldset ID parameter is NULL';
879 IF table_name IS NULL THEN
880 RETURN 'Table name parameter is NULL';
882 IF pkey_name IS NULL THEN
883 RETURN 'Primary key name parameter is NULL';
886 statement := 'UPDATE ' || table_name || ' SET';
890 quote_literal( pkey_value )
899 IF fs_status IS NULL THEN
900 RETURN 'No fieldset found for id = ' || fieldset_id;
901 ELSIF fs_status = 'APPLIED' THEN
902 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
909 FROM action.fieldset_col_val
910 WHERE fieldset = fieldset_id
912 statement := statement || sep || ' ' || cv.col
913 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
918 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
921 -- Add the WHERE clause. This differs according to whether it's a
922 -- single-row fieldset or a query-based fieldset.
924 IF query IS NULL AND fs_pkey_value IS NULL THEN
925 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
926 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
927 fs_query := rtrim( query, ';' );
928 statement := statement || ' WHERE ' || pkey_name || ' IN ( '
929 || fs_query || ' );';
930 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
931 statement := statement || ' WHERE ' || pkey_name || ' = '
932 || fs_pkey_value || ';';
933 ELSE -- both are not null
934 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
937 -- Execute the update
941 GET DIAGNOSTICS update_count = ROW_COUNT;
943 IF UPDATE_COUNT > 0 THEN
944 status_code := 'APPLIED';
947 status_code := 'ERROR';
948 msg := 'No eligible rows found for fieldset ' || fieldset_id;
950 EXCEPTION WHEN OTHERS THEN
951 status_code := 'ERROR';
952 msg := 'Unable to apply fieldset ' || fieldset_id
956 -- Update fieldset status
958 UPDATE action.fieldset
959 SET status = status_code,
961 WHERE id = fieldset_id;
967 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
968 Applies a specified fieldset, using a supplied table name and primary
969 key name. The query parameter should be non-null only for
970 query-based fieldsets.
972 Returns NULL if successful, or an error message if not.