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 ();
105 CREATE TABLE action.circulation (
106 target_copy BIGINT NOT NULL, -- asset.copy.id
107 circ_lib INT NOT NULL, -- actor.org_unit.id
108 circ_staff INT NOT NULL, -- actor.usr.id
109 checkin_staff INT, -- actor.usr.id
110 checkin_lib INT, -- actor.org_unit.id
111 renewal_remaining INT NOT NULL, -- derived from "circ duration" rule
112 grace_period INTERVAL NOT NULL, -- derived from "circ fine" rule
113 due_date TIMESTAMP WITH TIME ZONE,
114 stop_fines_time TIMESTAMP WITH TIME ZONE,
115 checkin_time TIMESTAMP WITH TIME ZONE,
116 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
117 duration INTERVAL, -- derived from "circ duration" rule
118 fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
119 recurring_fine NUMERIC(6,2), -- derived from "circ fine" rule
120 max_fine NUMERIC(6,2), -- derived from "max fine" rule
121 phone_renewal BOOL NOT NULL DEFAULT FALSE,
122 desk_renewal BOOL NOT NULL DEFAULT FALSE,
123 opac_renewal BOOL NOT NULL DEFAULT FALSE,
124 duration_rule TEXT NOT NULL, -- name of "circ duration" rule
125 recurring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule
126 max_fine_rule TEXT NOT NULL, -- name of "max fine" rule
127 stop_fines TEXT CHECK (stop_fines IN (
128 'CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE','CLAIMSNEVERCHECKEDOUT')),
129 workstation INT REFERENCES actor.workstation(id)
131 DEFERRABLE INITIALLY DEFERRED,
132 checkin_workstation INT REFERENCES actor.workstation(id)
134 DEFERRABLE INITIALLY DEFERRED,
135 checkin_scan_time TIMESTAMP WITH TIME ZONE
136 ) INHERITS (money.billable_xact);
137 ALTER TABLE action.circulation ADD PRIMARY KEY (id);
138 ALTER TABLE action.circulation
139 ADD COLUMN parent_circ BIGINT
140 REFERENCES action.circulation( id )
141 DEFERRABLE INITIALLY DEFERRED;
142 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
143 CREATE INDEX circ_outstanding_idx ON action.circulation (usr) WHERE checkin_time IS NULL;
144 CREATE INDEX circ_checkin_time ON "action".circulation (checkin_time) WHERE checkin_time IS NOT NULL;
145 CREATE INDEX circ_circ_lib_idx ON "action".circulation (circ_lib);
146 CREATE INDEX circ_open_date_idx ON "action".circulation (xact_start) WHERE xact_finish IS NULL;
147 CREATE INDEX circ_all_usr_idx ON action.circulation ( usr );
148 CREATE INDEX circ_circ_staff_idx ON action.circulation ( circ_staff );
149 CREATE INDEX circ_checkin_staff_idx ON action.circulation ( checkin_staff );
150 CREATE INDEX action_circulation_target_copy_idx ON action.circulation (target_copy);
151 CREATE UNIQUE INDEX circ_parent_idx ON action.circulation ( parent_circ ) WHERE parent_circ IS NOT NULL;
152 CREATE UNIQUE INDEX only_one_concurrent_checkout_per_copy ON action.circulation(target_copy) WHERE checkin_time IS NULL;
154 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');
156 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation');
157 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
158 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
160 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
162 IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 THEN
163 NEW.due_date = (NEW.due_date::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL)::TIMESTAMPTZ;
170 CREATE TRIGGER push_due_date_tgr BEFORE INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.push_circ_due_time();
172 CREATE TABLE action.aged_circulation (
174 usr_home_ou INT NOT NULL,
175 usr_profile INT NOT NULL,
177 copy_call_number INT NOT NULL,
178 copy_location INT NOT NULL,
179 copy_owning_lib INT NOT NULL,
180 copy_circ_lib INT NOT NULL,
181 copy_bib_record BIGINT NOT NULL,
182 LIKE action.circulation
185 ALTER TABLE action.aged_circulation ADD PRIMARY KEY (id);
186 ALTER TABLE action.aged_circulation DROP COLUMN usr;
187 CREATE INDEX aged_circ_circ_lib_idx ON "action".aged_circulation (circ_lib);
188 CREATE INDEX aged_circ_start_idx ON "action".aged_circulation (xact_start);
189 CREATE INDEX aged_circ_copy_circ_lib_idx ON "action".aged_circulation (copy_circ_lib);
190 CREATE INDEX aged_circ_copy_owning_lib_idx ON "action".aged_circulation (copy_owning_lib);
191 CREATE INDEX aged_circ_copy_location_idx ON "action".aged_circulation (copy_location);
192 CREATE INDEX action_aged_circulation_target_copy_idx ON action.aged_circulation (target_copy);
194 CREATE OR REPLACE VIEW action.all_circulation AS
195 SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
196 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
197 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
198 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
199 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
200 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
201 FROM action.aged_circulation
203 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,
204 cp.call_number AS copy_call_number, cp.location AS copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
205 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
206 circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
207 circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
208 circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
210 FROM action.circulation circ
211 JOIN asset.copy cp ON (circ.target_copy = cp.id)
212 JOIN asset.call_number cn ON (cp.call_number = cn.id)
213 JOIN actor.usr p ON (circ.usr = p.id)
214 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
215 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
217 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
222 -- If there are any renewals for this circulation, don't archive or delete
223 -- it yet. We'll do so later, when we archive and delete the renewals.
225 SELECT 'Y' INTO found
226 FROM action.circulation
227 WHERE parent_circ = OLD.id
231 RETURN NULL; -- don't delete
234 -- Archive a copy of the old row to action.aged_circulation
236 INSERT INTO action.aged_circulation
237 (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)
244 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
245 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
246 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
247 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
248 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
249 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
250 FROM action.all_circulation WHERE id = OLD.id;
254 $$ LANGUAGE 'plpgsql';
256 CREATE TRIGGER action_circulation_aging_tgr
257 BEFORE DELETE ON action.circulation
259 EXECUTE PROCEDURE action.age_circ_on_delete ();
262 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
265 -- Having deleted a renewal, we can delete the original circulation (or a previous
266 -- renewal, if that's what parent_circ is pointing to). That deletion will trigger
267 -- deletion of any prior parents, etc. recursively.
269 IF OLD.parent_circ IS NOT NULL THEN
270 DELETE FROM action.circulation
271 WHERE id = OLD.parent_circ;
276 $$ LANGUAGE 'plpgsql';
278 CREATE TRIGGER age_parent_circ
279 AFTER DELETE ON action.circulation
281 EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
284 CREATE OR REPLACE VIEW action.open_circulation AS
286 FROM action.circulation
287 WHERE checkin_time IS NULL
291 CREATE OR REPLACE VIEW action.billable_circulations AS
293 FROM action.circulation
294 WHERE xact_finish IS NULL;
296 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
298 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
299 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
300 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
302 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
303 UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
305 IF NEW.stop_fines = 'LOST' THEN
306 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
311 $$ LANGUAGE 'plpgsql';
312 CREATE TRIGGER action_circulation_stop_fines_tgr
313 BEFORE UPDATE ON action.circulation
315 EXECUTE PROCEDURE action.circulation_claims_returned ();
317 CREATE TABLE action.hold_request_cancel_cause (
318 id SERIAL PRIMARY KEY,
321 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (1,'Untargeted expiration');
322 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (2,'Hold Shelf expiration');
323 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (3,'Patron via phone');
324 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (4,'Patron in person');
325 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (5,'Staff forced');
326 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (6,'Patron via OPAC');
327 SELECT SETVAL('action.hold_request_cancel_cause_id_seq', 100);
329 CREATE TABLE action.hold_request (
330 id SERIAL PRIMARY KEY,
331 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
332 capture_time TIMESTAMP WITH TIME ZONE,
333 fulfillment_time TIMESTAMP WITH TIME ZONE,
334 checkin_time TIMESTAMP WITH TIME ZONE,
335 return_time TIMESTAMP WITH TIME ZONE,
336 prev_check_time TIMESTAMP WITH TIME ZONE,
337 expire_time TIMESTAMP WITH TIME ZONE,
338 cancel_time TIMESTAMP WITH TIME ZONE,
339 cancel_cause INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
341 target BIGINT NOT NULL, -- see hold_type
342 current_copy BIGINT, -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.unit now...
343 fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
344 fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
345 request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
346 requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
347 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
348 selection_ou INT NOT NULL,
349 selection_depth INT NOT NULL DEFAULT 0,
350 pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
351 hold_type TEXT NOT NULL, -- CHECK (hold_type IN ('M','T','V','C')), -- XXX constraint too constraining...
352 holdable_formats TEXT,
354 email_notify BOOL NOT NULL DEFAULT TRUE,
355 frozen BOOL NOT NULL DEFAULT FALSE,
356 thaw_date TIMESTAMP WITH TIME ZONE,
357 shelf_time TIMESTAMP WITH TIME ZONE,
359 mint_condition BOOL NOT NULL DEFAULT TRUE,
360 shelf_expire_time TIMESTAMPTZ,
361 current_shelf_lib INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED
364 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
365 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
366 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
367 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
368 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
369 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
370 CREATE INDEX hold_request_requestor_idx ON action.hold_request ( requestor );
373 CREATE TABLE action.hold_request_note (
375 id BIGSERIAL PRIMARY KEY,
376 hold BIGINT NOT NULL REFERENCES action.hold_request (id)
378 DEFERRABLE INITIALLY DEFERRED,
381 slip BOOL NOT NULL DEFAULT FALSE,
382 pub BOOL NOT NULL DEFAULT FALSE,
383 staff BOOL NOT NULL DEFAULT FALSE -- created by staff
386 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
389 CREATE TABLE action.hold_notification (
390 id SERIAL PRIMARY KEY,
391 hold INT NOT NULL REFERENCES action.hold_request (id)
393 DEFERRABLE INITIALLY DEFERRED,
394 notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
395 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
396 method TEXT NOT NULL, -- email address or phone number
399 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
400 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
402 CREATE TABLE action.hold_copy_map (
403 id BIGSERIAL PRIMARY KEY,
404 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
405 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
406 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
408 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
409 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
411 CREATE TABLE action.transit_copy (
412 id SERIAL PRIMARY KEY,
413 source_send_time TIMESTAMP WITH TIME ZONE,
414 dest_recv_time TIMESTAMP WITH TIME ZONE,
415 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
416 source INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
417 dest INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
418 prev_hop INT REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
419 copy_status INT NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
420 persistant_transfer BOOL NOT NULL DEFAULT FALSE,
421 prev_dest INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
423 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
424 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
425 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
428 CREATE TABLE action.hold_transit_copy (
429 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
430 ) INHERITS (action.transit_copy);
431 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
432 -- 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
433 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
434 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
435 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
438 CREATE TABLE action.unfulfilled_hold_list (
439 id BIGSERIAL PRIMARY KEY,
440 current_copy BIGINT NOT NULL,
442 circ_lib INT NOT NULL,
443 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
445 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
447 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
451 FROM action.unfulfilled_hold_list u
452 JOIN asset.copy c ON (c.id = u.current_copy)
455 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
458 FROM action.unfulfilled_hold_loops
461 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
463 FROM action.unfulfilled_hold_loops l
464 JOIN action.unfulfilled_hold_min_loop m USING (hold)
465 WHERE l.count = m.min;
467 CREATE VIEW action.unfulfilled_hold_max_loop AS
470 FROM action.unfulfilled_hold_loops
474 CREATE TABLE action.fieldset (
475 id SERIAL PRIMARY KEY,
476 owner INT NOT NULL REFERENCES actor.usr (id)
477 DEFERRABLE INITIALLY DEFERRED,
478 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
479 DEFERRABLE INITIALLY DEFERRED,
481 CONSTRAINT valid_status CHECK ( status in
482 ( 'PENDING', 'APPLIED', 'ERROR' )),
483 creation_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
484 scheduled_time TIMESTAMPTZ,
485 applied_time TIMESTAMPTZ,
486 classname TEXT NOT NULL, -- an IDL class name
488 stored_query INT REFERENCES query.stored_query (id)
489 DEFERRABLE INITIALLY DEFERRED,
491 CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
492 CONSTRAINT fieldset_one_or_the_other CHECK (
493 (stored_query IS NOT NULL AND pkey_value IS NULL) OR
494 (pkey_value IS NOT NULL AND stored_query IS NULL)
496 -- the CHECK constraint means we can update the fields for a single
497 -- row without all the extra overhead involved in a query
500 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
501 CREATE INDEX action_owner_idx ON action.fieldset( owner );
504 CREATE TABLE action.fieldset_col_val (
505 id SERIAL PRIMARY KEY,
506 fieldset INT NOT NULL REFERENCES action.fieldset
508 DEFERRABLE INITIALLY DEFERRED,
509 col TEXT NOT NULL, -- "field" from the idl ... the column on the table
510 val TEXT, -- value for the column ... NULL means, well, NULL
511 CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
515 -- represents a circ chain summary
516 CREATE TYPE action.circ_chain_summary AS (
518 start_time TIMESTAMP WITH TIME ZONE,
519 checkout_workstation TEXT,
520 last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
521 last_stop_fines TEXT,
522 last_stop_fines_time TIMESTAMP WITH TIME ZONE,
523 last_renewal_workstation TEXT, -- NULL if no renewals
524 last_checkin_workstation TEXT,
525 last_checkin_time TIMESTAMP WITH TIME ZONE,
526 last_checkin_scan_time TIMESTAMP WITH TIME ZONE
530 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id INTEGER ) RETURNS SETOF action.circulation AS $$
532 tmp_circ action.circulation%ROWTYPE;
533 circ_0 action.circulation%ROWTYPE;
536 SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
538 IF tmp_circ IS NULL THEN
539 RETURN NEXT tmp_circ;
543 -- find the front of the chain
545 SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
546 IF tmp_circ IS NULL THEN
552 -- now send the circs to the caller, oldest to newest
555 IF tmp_circ IS NULL THEN
558 RETURN NEXT tmp_circ;
559 SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
563 $$ LANGUAGE 'plpgsql';
565 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id INTEGER ) RETURNS action.circ_chain_summary AS $$
569 -- first circ in the chain
570 circ_0 action.circulation%ROWTYPE;
572 -- last circ in the chain
573 circ_n action.circulation%ROWTYPE;
575 -- circ chain under construction
576 chain action.circ_chain_summary;
577 tmp_circ action.circulation%ROWTYPE;
581 chain.num_circs := 0;
582 FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
584 IF chain.num_circs = 0 THEN
588 chain.num_circs := chain.num_circs + 1;
592 chain.start_time := circ_0.xact_start;
593 chain.last_stop_fines := circ_n.stop_fines;
594 chain.last_stop_fines_time := circ_n.stop_fines_time;
595 chain.last_checkin_time := circ_n.checkin_time;
596 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
597 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
598 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
600 IF chain.num_circs > 1 THEN
601 chain.last_renewal_time := circ_n.xact_start;
602 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
608 $$ LANGUAGE 'plpgsql';
610 -- Return the list of circ chain heads in xact_start order that the user has chosen to "retain"
611 CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$
613 c action.circulation%ROWTYPE;
615 usr_view_age actor.usr_setting%ROWTYPE;
616 usr_view_start actor.usr_setting%ROWTYPE;
618 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age';
619 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start';
621 IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN
622 -- User opted in and supplied a retention age
623 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
624 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
626 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
628 ELSIF usr_view_start.value IS NOT NULL THEN
630 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
632 -- User did not opt in
638 FROM action.circulation
640 AND parent_circ IS NULL
641 AND xact_start > NOW() - view_age
642 ORDER BY xact_start DESC
649 $func$ LANGUAGE PLPGSQL;
651 CREATE OR REPLACE FUNCTION action.usr_visible_circ_copies( INTEGER ) RETURNS SETOF BIGINT AS $$
652 SELECT DISTINCT(target_copy) FROM action.usr_visible_circs($1)
655 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
657 h action.hold_request%ROWTYPE;
660 usr_view_count actor.usr_setting%ROWTYPE;
661 usr_view_age actor.usr_setting%ROWTYPE;
662 usr_view_start actor.usr_setting%ROWTYPE;
664 SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
665 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
666 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
670 FROM action.hold_request
672 AND fulfillment_time IS NULL
673 AND cancel_time IS NULL
674 ORDER BY request_time DESC
679 IF usr_view_start.value IS NULL THEN
683 IF usr_view_age.value IS NOT NULL THEN
684 -- User opted in and supplied a retention age
685 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
686 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
688 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
692 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
695 IF usr_view_count.value IS NOT NULL THEN
696 view_count := oils_json_to_text(usr_view_count.value)::INT;
701 -- show some fulfilled/canceled holds
704 FROM action.hold_request
706 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
707 AND request_time > NOW() - view_age
708 ORDER BY request_time DESC
716 $func$ LANGUAGE PLPGSQL;
718 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
720 usr_keep_age actor.usr_setting%ROWTYPE;
721 usr_keep_start actor.usr_setting%ROWTYPE;
722 org_keep_age INTERVAL;
728 circ_chain_head action.circulation%ROWTYPE;
729 circ_chain_tail action.circulation%ROWTYPE;
737 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
739 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
740 IF org_keep_count IS NULL THEN
741 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
744 -- First, find copies with more than keep_count non-renewal circs
747 COUNT(*) AS total_real_circs
748 FROM action.circulation
749 WHERE parent_circ IS NULL
750 AND xact_finish IS NOT NULL
752 HAVING COUNT(*) > org_keep_count
755 -- And, for those, select circs that are finished and older than keep_age
756 FOR circ_chain_head IN
758 FROM action.circulation
759 WHERE target_copy = target_acp.target_copy
760 AND parent_circ IS NULL
764 -- Stop once we've purged enough circs to hit org_keep_count
765 EXIT WHEN target_acp.total_real_circs - purge_position <= org_keep_count;
767 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
768 EXIT WHEN circ_chain_tail.xact_finish IS NULL;
770 -- Now get the user settings, if any, to block purging if the user wants to keep more circs
771 usr_keep_age.value := NULL;
772 SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';
774 usr_keep_start.value := NULL;
775 SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start';
777 IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
778 IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN
779 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
781 keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL;
783 ELSIF usr_keep_start.value IS NOT NULL THEN
784 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
786 keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTERVAL );
789 EXIT WHEN AGE(NOW(), circ_chain_tail.xact_finish) < keep_age;
791 -- We've passed the purging tests, purge the circ chain starting at the end
792 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
793 WHILE circ_chain_tail.parent_circ IS NOT NULL LOOP
794 SELECT * INTO circ_chain_tail FROM action.circulation WHERE id = circ_chain_tail.parent_circ;
795 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
798 count_purged := count_purged + 1;
799 purge_position := purge_position + 1;
804 $func$ LANGUAGE PLPGSQL;
807 CREATE OR REPLACE FUNCTION action.apply_fieldset(
808 fieldset_id IN INT, -- id from action.fieldset
809 table_name IN TEXT, -- table to be updated
810 pkey_name IN TEXT, -- name of primary key column in that table
811 query IN TEXT -- query constructed by qstore (for query-based
812 -- fieldsets only; otherwise null
827 IF fieldset_id IS NULL THEN
828 RETURN 'Fieldset ID parameter is NULL';
830 IF table_name IS NULL THEN
831 RETURN 'Table name parameter is NULL';
833 IF pkey_name IS NULL THEN
834 RETURN 'Primary key name parameter is NULL';
837 statement := 'UPDATE ' || table_name || ' SET';
841 quote_literal( pkey_value )
850 IF fs_status IS NULL THEN
851 RETURN 'No fieldset found for id = ' || fieldset_id;
852 ELSIF fs_status = 'APPLIED' THEN
853 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
860 FROM action.fieldset_col_val
861 WHERE fieldset = fieldset_id
863 statement := statement || sep || ' ' || cv.col
864 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
869 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
872 -- Add the WHERE clause. This differs according to whether it's a
873 -- single-row fieldset or a query-based fieldset.
875 IF query IS NULL AND fs_pkey_value IS NULL THEN
876 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
877 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
878 fs_query := rtrim( query, ';' );
879 statement := statement || ' WHERE ' || pkey_name || ' IN ( '
880 || fs_query || ' );';
881 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
882 statement := statement || ' WHERE ' || pkey_name || ' = '
883 || fs_pkey_value || ';';
884 ELSE -- both are not null
885 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
888 -- Execute the update
892 GET DIAGNOSTICS update_count = ROW_COUNT;
894 IF UPDATE_COUNT > 0 THEN
895 status_code := 'APPLIED';
898 status_code := 'ERROR';
899 msg := 'No eligible rows found for fieldset ' || fieldset_id;
901 EXCEPTION WHEN OTHERS THEN
902 status_code := 'ERROR';
903 msg := 'Unable to apply fieldset ' || fieldset_id
907 -- Update fieldset status
909 UPDATE action.fieldset
910 SET status = status_code,
912 WHERE id = fieldset_id;
918 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
919 Applies a specified fieldset, using a supplied table name and primary
920 key name. The query parameter should be non-null only for
921 query-based fieldsets.
923 Returns NULL if successful, or an error message if not.