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 UNIQUE INDEX circ_parent_idx ON action.circulation ( parent_circ ) WHERE parent_circ IS NOT NULL;
150 CREATE UNIQUE INDEX only_one_concurrent_checkout_per_copy ON action.circulation(target_copy) WHERE checkin_time IS NULL;
152 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation');
153 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
154 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
156 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
158 IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 THEN
159 NEW.due_date = (NEW.due_date::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL)::TIMESTAMPTZ;
166 CREATE TRIGGER push_due_date_tgr BEFORE INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.push_circ_due_time();
168 CREATE TABLE action.aged_circulation (
170 usr_home_ou INT NOT NULL,
171 usr_profile INT NOT NULL,
173 copy_call_number INT NOT NULL,
174 copy_location INT NOT NULL,
175 copy_owning_lib INT NOT NULL,
176 copy_circ_lib INT NOT NULL,
177 copy_bib_record BIGINT NOT NULL,
178 LIKE action.circulation
181 ALTER TABLE action.aged_circulation ADD PRIMARY KEY (id);
182 ALTER TABLE action.aged_circulation DROP COLUMN usr;
183 CREATE INDEX aged_circ_circ_lib_idx ON "action".aged_circulation (circ_lib);
184 CREATE INDEX aged_circ_start_idx ON "action".aged_circulation (xact_start);
185 CREATE INDEX aged_circ_copy_circ_lib_idx ON "action".aged_circulation (copy_circ_lib);
186 CREATE INDEX aged_circ_copy_owning_lib_idx ON "action".aged_circulation (copy_owning_lib);
187 CREATE INDEX aged_circ_copy_location_idx ON "action".aged_circulation (copy_location);
189 CREATE OR REPLACE VIEW action.all_circulation AS
190 SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
191 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
192 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
193 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
194 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
195 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
196 FROM action.aged_circulation
198 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,
199 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,
200 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
201 circ.checkin_lib, circ.renewal_remaining, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
202 circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
203 circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
205 FROM action.circulation circ
206 JOIN asset.copy cp ON (circ.target_copy = cp.id)
207 JOIN asset.call_number cn ON (cp.call_number = cn.id)
208 JOIN actor.usr p ON (circ.usr = p.id)
209 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
210 LEFT JOIN actor.usr_address b ON (p.billing_address = a.id);
212 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
217 -- If there are any renewals for this circulation, don't archive or delete
218 -- it yet. We'll do so later, when we archive and delete the renewals.
220 SELECT 'Y' INTO found
221 FROM action.circulation
222 WHERE parent_circ = OLD.id
226 RETURN NULL; -- don't delete
229 -- Archive a copy of the old row to action.aged_circulation
231 INSERT INTO action.aged_circulation
232 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
233 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
234 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
235 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
236 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
237 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
239 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
240 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
241 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
242 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
243 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
244 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
245 FROM action.all_circulation WHERE id = OLD.id;
249 $$ LANGUAGE 'plpgsql';
251 CREATE TRIGGER action_circulation_aging_tgr
252 BEFORE DELETE ON action.circulation
254 EXECUTE PROCEDURE action.age_circ_on_delete ();
257 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
260 -- Having deleted a renewal, we can delete the original circulation (or a previous
261 -- renewal, if that's what parent_circ is pointing to). That deletion will trigger
262 -- deletion of any prior parents, etc. recursively.
264 IF OLD.parent_circ IS NOT NULL THEN
265 DELETE FROM action.circulation
266 WHERE id = OLD.parent_circ;
271 $$ LANGUAGE 'plpgsql';
273 CREATE TRIGGER age_parent_circ
274 AFTER DELETE ON action.circulation
276 EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
279 CREATE OR REPLACE VIEW action.open_circulation AS
281 FROM action.circulation
282 WHERE checkin_time IS NULL
286 CREATE OR REPLACE VIEW action.billable_circulations AS
288 FROM action.circulation
289 WHERE xact_finish IS NULL;
291 CREATE VIEW stats.fleshed_circulation AS
293 CAST(c.xact_start AS DATE) AS start_date_day,
294 CAST(c.xact_finish AS DATE) AS finish_date_day,
295 DATE_TRUNC('hour', c.xact_start) AS start_date_hour,
296 DATE_TRUNC('hour', c.xact_finish) AS finish_date_hour,
297 cp.call_number_label,
302 FROM "action".circulation c
303 JOIN stats.fleshed_copy cp ON (cp.id = c.target_copy);
306 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
308 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
309 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
310 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
312 IF NEW.stop_fines = 'CLAIMSNEVERCHECKEDOUT' THEN
313 UPDATE actor.usr SET claims_never_checked_out_count = claims_never_checked_out_count + 1 WHERE id = NEW.usr;
315 IF NEW.stop_fines = 'LOST' THEN
316 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
321 $$ LANGUAGE 'plpgsql';
322 CREATE TRIGGER action_circulation_stop_fines_tgr
323 BEFORE UPDATE ON action.circulation
325 EXECUTE PROCEDURE action.circulation_claims_returned ();
327 CREATE TABLE action.hold_request_cancel_cause (
328 id SERIAL PRIMARY KEY,
331 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (1,'Untargeted expiration');
332 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (2,'Hold Shelf expiration');
333 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (3,'Patron via phone');
334 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (4,'Patron in person');
335 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (5,'Staff forced');
336 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (6,'Patron via OPAC');
337 SELECT SETVAL('action.hold_request_cancel_cause_id_seq', 100);
339 CREATE TABLE action.hold_request (
340 id SERIAL PRIMARY KEY,
341 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
342 capture_time TIMESTAMP WITH TIME ZONE,
343 fulfillment_time TIMESTAMP WITH TIME ZONE,
344 checkin_time TIMESTAMP WITH TIME ZONE,
345 return_time TIMESTAMP WITH TIME ZONE,
346 prev_check_time TIMESTAMP WITH TIME ZONE,
347 expire_time TIMESTAMP WITH TIME ZONE,
348 cancel_time TIMESTAMP WITH TIME ZONE,
349 cancel_cause INT REFERENCES action.hold_request_cancel_cause (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
351 target BIGINT NOT NULL, -- see hold_type
352 current_copy BIGINT, -- REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.unit now...
353 fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
354 fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
355 request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
356 requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
357 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
358 selection_ou INT NOT NULL,
359 selection_depth INT NOT NULL DEFAULT 0,
360 pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
361 hold_type TEXT NOT NULL, -- CHECK (hold_type IN ('M','T','V','C')), -- XXX constraint too constraining...
362 holdable_formats TEXT,
364 email_notify BOOL NOT NULL DEFAULT TRUE,
365 frozen BOOL NOT NULL DEFAULT FALSE,
366 thaw_date TIMESTAMP WITH TIME ZONE,
367 shelf_time TIMESTAMP WITH TIME ZONE,
369 mint_condition BOOL NOT NULL DEFAULT TRUE,
370 shelf_expire_time TIMESTAMPTZ
373 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
374 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
375 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
376 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
377 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
378 CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff );
379 CREATE INDEX hold_request_requestor_idx ON action.hold_request ( requestor );
382 CREATE TABLE action.hold_request_note (
384 id BIGSERIAL PRIMARY KEY,
385 hold BIGINT NOT NULL REFERENCES action.hold_request (id)
387 DEFERRABLE INITIALLY DEFERRED,
390 slip BOOL NOT NULL DEFAULT FALSE,
391 pub BOOL NOT NULL DEFAULT FALSE,
392 staff BOOL NOT NULL DEFAULT FALSE -- created by staff
395 CREATE INDEX ahrn_hold_idx ON action.hold_request_note (hold);
398 CREATE TABLE action.hold_notification (
399 id SERIAL PRIMARY KEY,
400 hold INT NOT NULL REFERENCES action.hold_request (id)
402 DEFERRABLE INITIALLY DEFERRED,
403 notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
404 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
405 method TEXT NOT NULL, -- email address or phone number
408 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
409 CREATE INDEX ahn_notify_staff_idx ON action.hold_notification ( notify_staff );
411 CREATE TABLE action.hold_copy_map (
412 id SERIAL PRIMARY KEY,
413 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
414 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
415 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
417 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
418 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
420 CREATE TABLE action.transit_copy (
421 id SERIAL PRIMARY KEY,
422 source_send_time TIMESTAMP WITH TIME ZONE,
423 dest_recv_time TIMESTAMP WITH TIME ZONE,
424 target_copy BIGINT NOT NULL, -- REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- XXX could be an serial.issuance
425 source INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
426 dest INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
427 prev_hop INT REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED,
428 copy_status INT NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
429 persistant_transfer BOOL NOT NULL DEFAULT FALSE,
430 prev_dest INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
432 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
433 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
434 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
437 CREATE TABLE action.hold_transit_copy (
438 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
439 ) INHERITS (action.transit_copy);
440 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
441 -- 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
442 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
443 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
444 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
447 CREATE TABLE action.unfulfilled_hold_list (
448 id BIGSERIAL PRIMARY KEY,
449 current_copy BIGINT NOT NULL,
451 circ_lib INT NOT NULL,
452 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
454 CREATE INDEX uhr_hold_idx ON action.unfulfilled_hold_list (hold);
456 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
460 FROM action.unfulfilled_hold_list u
461 JOIN asset.copy c ON (c.id = u.current_copy)
464 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
467 FROM action.unfulfilled_hold_loops
470 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
472 FROM action.unfulfilled_hold_loops l
473 JOIN action.unfulfilled_hold_min_loop m USING (hold)
474 WHERE l.count = m.min;
476 CREATE VIEW action.unfulfilled_hold_max_loop AS
479 FROM action.unfulfilled_hold_loops
483 CREATE TABLE action.fieldset (
484 id SERIAL PRIMARY KEY,
485 owner INT NOT NULL REFERENCES actor.usr (id)
486 DEFERRABLE INITIALLY DEFERRED,
487 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
488 DEFERRABLE INITIALLY DEFERRED,
490 CONSTRAINT valid_status CHECK ( status in
491 ( 'PENDING', 'APPLIED', 'ERROR' )),
492 creation_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
493 scheduled_time TIMESTAMPTZ,
494 applied_time TIMESTAMPTZ,
495 classname TEXT NOT NULL, -- an IDL class name
497 stored_query INT REFERENCES query.stored_query (id)
498 DEFERRABLE INITIALLY DEFERRED,
500 CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
501 CONSTRAINT fieldset_one_or_the_other CHECK (
502 (stored_query IS NOT NULL AND pkey_value IS NULL) OR
503 (pkey_value IS NOT NULL AND stored_query IS NULL)
505 -- the CHECK constraint means we can update the fields for a single
506 -- row without all the extra overhead involved in a query
509 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
510 CREATE INDEX action_owner_idx ON action.fieldset( owner );
513 CREATE TABLE action.fieldset_col_val (
514 id SERIAL PRIMARY KEY,
515 fieldset INT NOT NULL REFERENCES action.fieldset
517 DEFERRABLE INITIALLY DEFERRED,
518 col TEXT NOT NULL, -- "field" from the idl ... the column on the table
519 val TEXT, -- value for the column ... NULL means, well, NULL
520 CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
524 -- represents a circ chain summary
525 CREATE TYPE action.circ_chain_summary AS (
527 start_time TIMESTAMP WITH TIME ZONE,
528 checkout_workstation TEXT,
529 last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals
530 last_stop_fines TEXT,
531 last_stop_fines_time TIMESTAMP WITH TIME ZONE,
532 last_renewal_workstation TEXT, -- NULL if no renewals
533 last_checkin_workstation TEXT,
534 last_checkin_time TIMESTAMP WITH TIME ZONE,
535 last_checkin_scan_time TIMESTAMP WITH TIME ZONE
539 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id INTEGER ) RETURNS SETOF action.circulation AS $$
541 tmp_circ action.circulation%ROWTYPE;
542 circ_0 action.circulation%ROWTYPE;
545 SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
547 IF tmp_circ IS NULL THEN
548 RETURN NEXT tmp_circ;
552 -- find the front of the chain
554 SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
555 IF tmp_circ IS NULL THEN
561 -- now send the circs to the caller, oldest to newest
564 IF tmp_circ IS NULL THEN
567 RETURN NEXT tmp_circ;
568 SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
572 $$ LANGUAGE 'plpgsql';
574 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id INTEGER ) RETURNS action.circ_chain_summary AS $$
578 -- first circ in the chain
579 circ_0 action.circulation%ROWTYPE;
581 -- last circ in the chain
582 circ_n action.circulation%ROWTYPE;
584 -- circ chain under construction
585 chain action.circ_chain_summary;
586 tmp_circ action.circulation%ROWTYPE;
590 chain.num_circs := 0;
591 FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
593 IF chain.num_circs = 0 THEN
597 chain.num_circs := chain.num_circs + 1;
601 chain.start_time := circ_0.xact_start;
602 chain.last_stop_fines := circ_n.stop_fines;
603 chain.last_stop_fines_time := circ_n.stop_fines_time;
604 chain.last_checkin_time := circ_n.checkin_time;
605 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
606 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
607 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
609 IF chain.num_circs > 1 THEN
610 chain.last_renewal_time := circ_n.xact_start;
611 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
617 $$ LANGUAGE 'plpgsql';
619 -- Return the list of circ chain heads in xact_start order that the user has chosen to "retain"
620 CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$
622 c action.circulation%ROWTYPE;
624 usr_view_age actor.usr_setting%ROWTYPE;
625 usr_view_start actor.usr_setting%ROWTYPE;
627 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age';
628 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start';
630 IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN
631 -- User opted in and supplied a retention age
632 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
633 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
635 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
637 ELSIF usr_view_start.value IS NOT NULL THEN
639 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
641 -- User did not opt in
647 FROM action.circulation
649 AND parent_circ IS NULL
650 AND xact_start > NOW() - view_age
658 $func$ LANGUAGE PLPGSQL;
660 CREATE OR REPLACE FUNCTION action.usr_visible_circ_copies( INTEGER ) RETURNS SETOF BIGINT AS $$
661 SELECT DISTINCT(target_copy) FROM action.usr_visible_circs($1)
664 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
666 h action.hold_request%ROWTYPE;
669 usr_view_count actor.usr_setting%ROWTYPE;
670 usr_view_age actor.usr_setting%ROWTYPE;
671 usr_view_start actor.usr_setting%ROWTYPE;
673 SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
674 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
675 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
679 FROM action.hold_request
681 AND fulfillment_time IS NULL
682 AND cancel_time IS NULL
683 ORDER BY request_time DESC
688 IF usr_view_start.value IS NULL THEN
692 IF usr_view_age.value IS NOT NULL THEN
693 -- User opted in and supplied a retention age
694 IF oils_json_to_string(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ) THEN
695 view_age := AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ);
697 view_age := oils_json_to_string(usr_view_age.value)::INTERVAL;
701 view_age := AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ);
704 IF usr_view_count.value IS NOT NULL THEN
705 view_count := oils_json_to_text(usr_view_count.value)::INT;
710 -- show some fulfilled/canceled holds
713 FROM action.hold_request
715 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
716 AND request_time > NOW() - view_age
717 ORDER BY request_time DESC
725 $func$ LANGUAGE PLPGSQL;
727 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
729 usr_keep_age actor.usr_setting%ROWTYPE;
730 usr_keep_start actor.usr_setting%ROWTYPE;
731 org_keep_age INTERVAL;
737 circ_chain_head action.circulation%ROWTYPE;
738 circ_chain_tail action.circulation%ROWTYPE;
746 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
748 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
749 IF org_keep_count IS NULL THEN
750 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
753 -- First, find copies with more than keep_count non-renewal circs
756 COUNT(*) AS total_real_circs
757 FROM action.circulation
758 WHERE parent_circ IS NULL
759 AND xact_finish IS NOT NULL
761 HAVING COUNT(*) > org_keep_count
764 -- And, for those, select circs that are finished and older than keep_age
765 FOR circ_chain_head IN
767 FROM action.circulation
768 WHERE target_copy = target_acp.target_copy
769 AND parent_circ IS NULL
773 -- Stop once we've purged enough circs to hit org_keep_count
774 EXIT WHEN target_acp.total_real_circs - purge_position <= org_keep_count;
776 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
777 EXIT WHEN circ_chain_tail.xact_finish IS NULL;
779 -- Now get the user setings, if any, to block purging if the user wants to keep more circs
780 usr_keep_age.value := NULL;
781 SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';
783 usr_keep_start.value := NULL;
784 SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start';
786 IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
787 IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN
788 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
790 keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL;
792 ELSIF usr_keep_start.value IS NOT NULL THEN
793 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
795 keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTEVAL );
798 EXIT WHEN AGE(NOW(), circ_chain_tail.xact_finish) < keep_age;
800 -- We've passed the purging tests, purge the circ chain starting at the end
801 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
802 WHILE circ_chain_tail.parent_circ IS NOT NULL LOOP
803 SELECT * INTO circ_chain_tail FROM action.circulation WHERE id = circ_chain_tail.parent_circ;
804 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
807 count_purged := count_purged + 1;
808 purge_position := purge_position + 1;
813 $func$ LANGUAGE PLPGSQL;