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 due_date TIMESTAMP WITH TIME ZONE,
113 stop_fines_time TIMESTAMP WITH TIME ZONE,
114 checkin_time TIMESTAMP WITH TIME ZONE,
115 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
116 duration INTERVAL, -- derived from "circ duration" rule
117 fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
118 recurring_fine NUMERIC(6,2), -- derived from "circ fine" rule
119 max_fine NUMERIC(6,2), -- derived from "max fine" rule
120 phone_renewal BOOL NOT NULL DEFAULT FALSE,
121 desk_renewal BOOL NOT NULL DEFAULT FALSE,
122 opac_renewal BOOL NOT NULL DEFAULT FALSE,
123 duration_rule TEXT NOT NULL, -- name of "circ duration" rule
124 recurring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule
125 max_fine_rule TEXT NOT NULL, -- name of "max fine" rule
126 stop_fines TEXT CHECK (stop_fines IN (
127 'CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE','CLAIMSNEVERCHECKEDOUT')),
128 workstation INT REFERENCES actor.workstation(id)
130 DEFERRABLE INITIALLY DEFERRED,
131 checkin_workstation INT REFERENCES actor.workstation(id)
133 DEFERRABLE INITIALLY DEFERRED,
134 checkin_scan_time TIMESTAMP WITH TIME ZONE
135 ) INHERITS (money.billable_xact);
136 ALTER TABLE action.circulation ADD PRIMARY KEY (id);
137 ALTER TABLE action.circulation
138 ADD COLUMN parent_circ BIGINT
139 REFERENCES action.circulation( id )
140 DEFERRABLE INITIALLY DEFERRED;
141 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
142 CREATE INDEX circ_outstanding_idx ON action.circulation (usr) WHERE checkin_time IS NULL;
143 CREATE INDEX circ_checkin_time ON "action".circulation (checkin_time) WHERE checkin_time IS NOT NULL;
144 CREATE INDEX circ_circ_lib_idx ON "action".circulation (circ_lib);
145 CREATE INDEX circ_open_date_idx ON "action".circulation (xact_start) WHERE xact_finish IS NULL;
146 CREATE INDEX circ_all_usr_idx ON action.circulation ( usr );
147 CREATE INDEX circ_circ_staff_idx ON action.circulation ( circ_staff );
148 CREATE INDEX circ_checkin_staff_idx ON action.circulation ( checkin_staff );
149 CREATE INDEX action_circulation_target_copy_idx ON action.circulation (target_copy);
150 CREATE UNIQUE INDEX circ_parent_idx ON action.circulation ( parent_circ ) WHERE parent_circ IS NOT NULL;
151 CREATE UNIQUE INDEX only_one_concurrent_checkout_per_copy ON action.circulation(target_copy) WHERE checkin_time IS NULL;
153 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation');
154 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
155 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
157 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
159 IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 THEN
160 NEW.due_date = (NEW.due_date::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL)::TIMESTAMPTZ;
167 CREATE TRIGGER push_due_date_tgr BEFORE INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.push_circ_due_time();
169 CREATE TABLE action.aged_circulation (
171 usr_home_ou INT NOT NULL,
172 usr_profile INT NOT NULL,
174 copy_call_number INT NOT NULL,
175 copy_location INT NOT NULL,
176 copy_owning_lib INT NOT NULL,
177 copy_circ_lib INT NOT NULL,
178 copy_bib_record BIGINT NOT NULL,
179 LIKE action.circulation
182 ALTER TABLE action.aged_circulation ADD PRIMARY KEY (id);
183 ALTER TABLE action.aged_circulation DROP COLUMN usr;
184 CREATE INDEX aged_circ_circ_lib_idx ON "action".aged_circulation (circ_lib);
185 CREATE INDEX aged_circ_start_idx ON "action".aged_circulation (xact_start);
186 CREATE INDEX aged_circ_copy_circ_lib_idx ON "action".aged_circulation (copy_circ_lib);
187 CREATE INDEX aged_circ_copy_owning_lib_idx ON "action".aged_circulation (copy_owning_lib);
188 CREATE INDEX aged_circ_copy_location_idx ON "action".aged_circulation (copy_location);
189 CREATE INDEX action_aged_circulation_target_copy_idx ON action.aged_circulation (target_copy);
191 CREATE OR REPLACE VIEW action.all_circulation AS
192 SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
193 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
194 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
195 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
196 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
197 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
198 FROM action.aged_circulation
200 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,
201 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,
202 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
203 circ.checkin_lib, circ.renewal_remaining, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
204 circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
205 circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
207 FROM action.circulation circ
208 JOIN asset.copy cp ON (circ.target_copy = cp.id)
209 JOIN asset.call_number cn ON (cp.call_number = cn.id)
210 JOIN actor.usr p ON (circ.usr = p.id)
211 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
212 LEFT JOIN actor.usr_address b ON (p.billing_address = a.id);
214 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
219 -- If there are any renewals for this circulation, don't archive or delete
220 -- it yet. We'll do so later, when we archive and delete the renewals.
222 SELECT 'Y' INTO found
223 FROM action.circulation
224 WHERE parent_circ = OLD.id
228 RETURN NULL; -- don't delete
231 -- Archive a copy of the old row to action.aged_circulation
233 INSERT INTO action.aged_circulation
234 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
235 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
236 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
237 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
238 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
239 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
241 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
242 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
243 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
244 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
245 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
246 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
247 FROM action.all_circulation WHERE id = OLD.id;
251 $$ LANGUAGE 'plpgsql';
253 CREATE TRIGGER action_circulation_aging_tgr
254 BEFORE DELETE ON action.circulation
256 EXECUTE PROCEDURE action.age_circ_on_delete ();
259 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
262 -- Having deleted a renewal, we can delete the original circulation (or a previous
263 -- renewal, if that's what parent_circ is pointing to). That deletion will trigger
264 -- deletion of any prior parents, etc. recursively.
266 IF OLD.parent_circ IS NOT NULL THEN
267 DELETE FROM action.circulation
268 WHERE id = OLD.parent_circ;
273 $$ LANGUAGE 'plpgsql';
275 CREATE TRIGGER age_parent_circ
276 AFTER DELETE ON action.circulation
278 EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
281 CREATE OR REPLACE VIEW action.open_circulation AS
283 FROM action.circulation
284 WHERE checkin_time IS NULL
288 CREATE OR REPLACE VIEW action.billable_circulations AS
290 FROM action.circulation
291 WHERE xact_finish IS NULL;
293 CREATE VIEW stats.fleshed_circulation AS
295 CAST(c.xact_start AS DATE) AS start_date_day,
296 CAST(c.xact_finish AS DATE) AS finish_date_day,
297 DATE_TRUNC('hour', c.xact_start) AS start_date_hour,
298 DATE_TRUNC('hour', c.xact_finish) AS finish_date_hour,
299 cp.call_number_label,
304 FROM "action".circulation c
305 JOIN stats.fleshed_copy cp ON (cp.id = c.target_copy);
308 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
310 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
311 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
312 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
314 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
315 UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
317 IF NEW.stop_fines = 'LOST' THEN
318 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
323 $$ LANGUAGE 'plpgsql';
324 CREATE TRIGGER action_circulation_stop_fines_tgr
325 BEFORE UPDATE ON action.circulation
327 EXECUTE PROCEDURE action.circulation_claims_returned ();
329 CREATE TABLE action.hold_request_cancel_cause (
330 id SERIAL PRIMARY KEY,
333 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (1,'Untargeted expiration');
334 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (2,'Hold Shelf expiration');
335 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (3,'Patron via phone');
336 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (4,'Patron in person');
337 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (5,'Staff forced');
338 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (6,'Patron via OPAC');
339 SELECT SETVAL('action.hold_request_cancel_cause_id_seq', 100);
341 CREATE TABLE action.hold_request (
342 id SERIAL PRIMARY KEY,
343 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
344 capture_time TIMESTAMP WITH TIME ZONE,
345 fulfillment_time TIMESTAMP WITH TIME ZONE,
346 checkin_time TIMESTAMP WITH TIME ZONE,
347 return_time TIMESTAMP WITH TIME ZONE,
348 prev_check_time TIMESTAMP WITH TIME ZONE,
349 expire_time TIMESTAMP WITH TIME ZONE,
350 cancel_time TIMESTAMP WITH TIME ZONE,
351 cancel_cause INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
353 target BIGINT NOT NULL, -- see hold_type
354 current_copy BIGINT, -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.unit now...
355 fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
356 fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
357 request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
358 requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
359 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
360 selection_ou INT NOT NULL,
361 selection_depth INT NOT NULL DEFAULT 0,
362 pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
363 hold_type TEXT NOT NULL, -- CHECK (hold_type IN ('M','T','V','C')), -- XXX constraint too constraining...
364 holdable_formats TEXT,
366 email_notify BOOL NOT NULL DEFAULT TRUE,
367 frozen BOOL NOT NULL DEFAULT FALSE,
368 thaw_date TIMESTAMP WITH TIME ZONE,
369 shelf_time TIMESTAMP WITH TIME ZONE,
371 mint_condition BOOL NOT NULL DEFAULT TRUE,
372 shelf_expire_time TIMESTAMPTZ
375 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
376 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
377 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
378 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
379 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
380 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
381 CREATE INDEX hold_request_requestor_idx ON action.hold_request ( requestor );
384 CREATE TABLE action.hold_request_note (
386 id BIGSERIAL PRIMARY KEY,
387 hold BIGINT NOT NULL REFERENCES action.hold_request (id)
389 DEFERRABLE INITIALLY DEFERRED,
392 slip BOOL NOT NULL DEFAULT FALSE,
393 pub BOOL NOT NULL DEFAULT FALSE,
394 staff BOOL NOT NULL DEFAULT FALSE -- created by staff
397 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
400 CREATE TABLE action.hold_notification (
401 id SERIAL PRIMARY KEY,
402 hold INT NOT NULL REFERENCES action.hold_request (id)
404 DEFERRABLE INITIALLY DEFERRED,
405 notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
406 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
407 method TEXT NOT NULL, -- email address or phone number
410 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
411 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
413 CREATE TABLE action.hold_copy_map (
414 id BIGSERIAL PRIMARY KEY,
415 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
416 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
417 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
419 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
420 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
422 CREATE TABLE action.transit_copy (
423 id SERIAL PRIMARY KEY,
424 source_send_time TIMESTAMP WITH TIME ZONE,
425 dest_recv_time TIMESTAMP WITH TIME ZONE,
426 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
427 source INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
428 dest INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
429 prev_hop INT REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
430 copy_status INT NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
431 persistant_transfer BOOL NOT NULL DEFAULT FALSE,
432 prev_dest INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
434 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
435 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
436 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
439 CREATE TABLE action.hold_transit_copy (
440 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
441 ) INHERITS (action.transit_copy);
442 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
443 -- 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
444 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
445 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
446 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
449 CREATE TABLE action.unfulfilled_hold_list (
450 id BIGSERIAL PRIMARY KEY,
451 current_copy BIGINT NOT NULL,
453 circ_lib INT NOT NULL,
454 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
456 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
458 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
462 FROM action.unfulfilled_hold_list u
463 JOIN asset.copy c ON (c.id = u.current_copy)
466 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
469 FROM action.unfulfilled_hold_loops
472 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
474 FROM action.unfulfilled_hold_loops l
475 JOIN action.unfulfilled_hold_min_loop m USING (hold)
476 WHERE l.count = m.min;
478 CREATE VIEW action.unfulfilled_hold_max_loop AS
481 FROM action.unfulfilled_hold_loops
485 CREATE TABLE action.fieldset (
486 id SERIAL PRIMARY KEY,
487 owner INT NOT NULL REFERENCES actor.usr (id)
488 DEFERRABLE INITIALLY DEFERRED,
489 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
490 DEFERRABLE INITIALLY DEFERRED,
492 CONSTRAINT valid_status CHECK ( status in
493 ( 'PENDING', 'APPLIED', 'ERROR' )),
494 creation_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
495 scheduled_time TIMESTAMPTZ,
496 applied_time TIMESTAMPTZ,
497 classname TEXT NOT NULL, -- an IDL class name
499 stored_query INT REFERENCES query.stored_query (id)
500 DEFERRABLE INITIALLY DEFERRED,
502 CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
503 CONSTRAINT fieldset_one_or_the_other CHECK (
504 (stored_query IS NOT NULL AND pkey_value IS NULL) OR
505 (pkey_value IS NOT NULL AND stored_query IS NULL)
507 -- the CHECK constraint means we can update the fields for a single
508 -- row without all the extra overhead involved in a query
511 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
512 CREATE INDEX action_owner_idx ON action.fieldset( owner );
515 CREATE TABLE action.fieldset_col_val (
516 id SERIAL PRIMARY KEY,
517 fieldset INT NOT NULL REFERENCES action.fieldset
519 DEFERRABLE INITIALLY DEFERRED,
520 col TEXT NOT NULL, -- "field" from the idl ... the column on the table
521 val TEXT, -- value for the column ... NULL means, well, NULL
522 CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
526 -- represents a circ chain summary
527 CREATE TYPE action.circ_chain_summary AS (
529 start_time TIMESTAMP WITH TIME ZONE,
530 checkout_workstation TEXT,
531 last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
532 last_stop_fines TEXT,
533 last_stop_fines_time TIMESTAMP WITH TIME ZONE,
534 last_renewal_workstation TEXT, -- NULL if no renewals
535 last_checkin_workstation TEXT,
536 last_checkin_time TIMESTAMP WITH TIME ZONE,
537 last_checkin_scan_time TIMESTAMP WITH TIME ZONE
541 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id INTEGER ) RETURNS SETOF action.circulation AS $$
543 tmp_circ action.circulation%ROWTYPE;
544 circ_0 action.circulation%ROWTYPE;
547 SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
549 IF tmp_circ IS NULL THEN
550 RETURN NEXT tmp_circ;
554 -- find the front of the chain
556 SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
557 IF tmp_circ IS NULL THEN
563 -- now send the circs to the caller, oldest to newest
566 IF tmp_circ IS NULL THEN
569 RETURN NEXT tmp_circ;
570 SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
574 $$ LANGUAGE 'plpgsql';
576 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id INTEGER ) RETURNS action.circ_chain_summary AS $$
580 -- first circ in the chain
581 circ_0 action.circulation%ROWTYPE;
583 -- last circ in the chain
584 circ_n action.circulation%ROWTYPE;
586 -- circ chain under construction
587 chain action.circ_chain_summary;
588 tmp_circ action.circulation%ROWTYPE;
592 chain.num_circs := 0;
593 FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
595 IF chain.num_circs = 0 THEN
599 chain.num_circs := chain.num_circs + 1;
603 chain.start_time := circ_0.xact_start;
604 chain.last_stop_fines := circ_n.stop_fines;
605 chain.last_stop_fines_time := circ_n.stop_fines_time;
606 chain.last_checkin_time := circ_n.checkin_time;
607 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
608 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
609 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
611 IF chain.num_circs > 1 THEN
612 chain.last_renewal_time := circ_n.xact_start;
613 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
619 $$ LANGUAGE 'plpgsql';
621 -- Return the list of circ chain heads in xact_start order that the user has chosen to "retain"
622 CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$
624 c action.circulation%ROWTYPE;
626 usr_view_age actor.usr_setting%ROWTYPE;
627 usr_view_start actor.usr_setting%ROWTYPE;
629 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age';
630 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start';
632 IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN
633 -- User opted in and supplied a retention age
634 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
635 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
637 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
639 ELSIF usr_view_start.value IS NOT NULL THEN
641 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
643 -- User did not opt in
649 FROM action.circulation
651 AND parent_circ IS NULL
652 AND xact_start > NOW() - view_age
660 $func$ LANGUAGE PLPGSQL;
662 CREATE OR REPLACE FUNCTION action.usr_visible_circ_copies( INTEGER ) RETURNS SETOF BIGINT AS $$
663 SELECT DISTINCT(target_copy) FROM action.usr_visible_circs($1)
666 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
668 h action.hold_request%ROWTYPE;
671 usr_view_count actor.usr_setting%ROWTYPE;
672 usr_view_age actor.usr_setting%ROWTYPE;
673 usr_view_start actor.usr_setting%ROWTYPE;
675 SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
676 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
677 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
681 FROM action.hold_request
683 AND fulfillment_time IS NULL
684 AND cancel_time IS NULL
685 ORDER BY request_time DESC
690 IF usr_view_start.value IS NULL THEN
694 IF usr_view_age.value IS NOT NULL THEN
695 -- User opted in and supplied a retention age
696 IF oils_json_to_string(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ) THEN
697 view_age := AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ);
699 view_age := oils_json_to_string(usr_view_age.value)::INTERVAL;
703 view_age := AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ);
706 IF usr_view_count.value IS NOT NULL THEN
707 view_count := oils_json_to_text(usr_view_count.value)::INT;
712 -- show some fulfilled/canceled holds
715 FROM action.hold_request
717 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
718 AND request_time > NOW() - view_age
719 ORDER BY request_time DESC
727 $func$ LANGUAGE PLPGSQL;
729 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
731 usr_keep_age actor.usr_setting%ROWTYPE;
732 usr_keep_start actor.usr_setting%ROWTYPE;
733 org_keep_age INTERVAL;
739 circ_chain_head action.circulation%ROWTYPE;
740 circ_chain_tail action.circulation%ROWTYPE;
748 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
750 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
751 IF org_keep_count IS NULL THEN
752 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
755 -- First, find copies with more than keep_count non-renewal circs
758 COUNT(*) AS total_real_circs
759 FROM action.circulation
760 WHERE parent_circ IS NULL
761 AND xact_finish IS NOT NULL
763 HAVING COUNT(*) > org_keep_count
766 -- And, for those, select circs that are finished and older than keep_age
767 FOR circ_chain_head IN
769 FROM action.circulation
770 WHERE target_copy = target_acp.target_copy
771 AND parent_circ IS NULL
775 -- Stop once we've purged enough circs to hit org_keep_count
776 EXIT WHEN target_acp.total_real_circs - purge_position <= org_keep_count;
778 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
779 EXIT WHEN circ_chain_tail.xact_finish IS NULL;
781 -- Now get the user settings, if any, to block purging if the user wants to keep more circs
782 usr_keep_age.value := NULL;
783 SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';
785 usr_keep_start.value := NULL;
786 SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start';
788 IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
789 IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN
790 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
792 keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL;
794 ELSIF usr_keep_start.value IS NOT NULL THEN
795 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
797 keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTERVAL );
800 EXIT WHEN AGE(NOW(), circ_chain_tail.xact_finish) < keep_age;
802 -- We've passed the purging tests, purge the circ chain starting at the end
803 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
804 WHILE circ_chain_tail.parent_circ IS NOT NULL LOOP
805 SELECT * INTO circ_chain_tail FROM action.circulation WHERE id = circ_chain_tail.parent_circ;
806 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
809 count_purged := count_purged + 1;
810 purge_position := purge_position + 1;
815 $func$ LANGUAGE PLPGSQL;
818 CREATE OR REPLACE FUNCTION action.apply_fieldset(
819 fieldset_id IN INT, -- id from action.fieldset
820 table_name IN TEXT, -- table to be updated
821 pkey_name IN TEXT, -- name of primary key column in that table
822 query IN TEXT -- query constructed by qstore (for query-based
823 -- fieldsets only; otherwise null
838 IF fieldset_id IS NULL THEN
839 RETURN 'Fieldset ID parameter is NULL';
841 IF table_name IS NULL THEN
842 RETURN 'Table name parameter is NULL';
844 IF pkey_name IS NULL THEN
845 RETURN 'Primary key name parameter is NULL';
848 statement := 'UPDATE ' || table_name || ' SET';
852 quote_literal( pkey_value )
861 IF fs_status IS NULL THEN
862 RETURN 'No fieldset found for id = ' || fieldset_id;
863 ELSIF fs_status = 'APPLIED' THEN
864 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
871 FROM action.fieldset_col_val
872 WHERE fieldset = fieldset_id
874 statement := statement || sep || ' ' || cv.col
875 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
880 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
883 -- Add the WHERE clause. This differs according to whether it's a
884 -- single-row fieldset or a query-based fieldset.
886 IF query IS NULL AND fs_pkey_value IS NULL THEN
887 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
888 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
889 fs_query := rtrim( query, ';' );
890 statement := statement || ' WHERE ' || pkey_name || ' IN ( '
891 || fs_query || ' );';
892 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
893 statement := statement || ' WHERE ' || pkey_name || ' = '
894 || fs_pkey_value || ';';
895 ELSE -- both are not null
896 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
899 -- Execute the update
903 GET DIAGNOSTICS update_count = ROW_COUNT;
905 IF UPDATE_COUNT > 0 THEN
906 status_code := 'APPLIED';
909 status_code := 'ERROR';
910 msg := 'No eligible rows found for fieldset ' || fieldset_id;
912 EXCEPTION WHEN OTHERS THEN
913 status_code := 'ERROR';
914 msg := 'Unable to apply fieldset ' || fieldset_id
918 -- Update fieldset status
920 UPDATE action.fieldset
921 SET status = status_code,
923 WHERE id = fieldset_id;
929 COMMENT ON FUNCTION action.apply_fieldset( INT, TEXT, TEXT, TEXT ) IS $$
931 * Applies a specified fieldset, using a supplied table name and primary
932 * key name. The query parameter should be non-null only for
933 * query-based fieldsets.
935 * Returns NULL if successful, or an error message if not.