3 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0798', :eg_version); -- tsbere/Dyrcona/dbwells
5 INSERT INTO config.global_flag (name, label)
7 'history.circ.retention_uses_last_finished',
9 'history.circ.retention_uses_last_finished',
10 'Historical Circulations use most recent xact_finish date instead of last circ''s.',
15 'history.circ.retention_age_is_min',
17 'history.circ.retention_age_is_min',
18 'Historical Circulations are kept for global retention age at a minimum, regardless of user preferences.',
26 DROP FUNCTION IF EXISTS action.circ_chain(INTEGER);
27 DROP FUNCTION IF EXISTS action.summarize_circ_chain(INTEGER);
29 CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id BIGINT ) RETURNS SETOF action.circulation AS $$
31 tmp_circ action.circulation%ROWTYPE;
32 circ_0 action.circulation%ROWTYPE;
35 SELECT INTO tmp_circ * FROM action.circulation WHERE id = ctx_circ_id;
37 IF tmp_circ IS NULL THEN
42 -- find the front of the chain
44 SELECT INTO tmp_circ * FROM action.circulation WHERE id = tmp_circ.parent_circ;
45 IF tmp_circ IS NULL THEN
51 -- now send the circs to the caller, oldest to newest
54 IF tmp_circ IS NULL THEN
58 SELECT INTO tmp_circ * FROM action.circulation WHERE parent_circ = tmp_circ.id;
62 $$ LANGUAGE 'plpgsql';
64 CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id BIGINT ) RETURNS action.circ_chain_summary AS $$
68 -- first circ in the chain
69 circ_0 action.circulation%ROWTYPE;
71 -- last circ in the chain
72 circ_n action.circulation%ROWTYPE;
74 -- circ chain under construction
75 chain action.circ_chain_summary;
76 tmp_circ action.circulation%ROWTYPE;
81 FOR tmp_circ IN SELECT * FROM action.circ_chain(ctx_circ_id) LOOP
83 IF chain.num_circs = 0 THEN
87 chain.num_circs := chain.num_circs + 1;
91 chain.start_time := circ_0.xact_start;
92 chain.last_stop_fines := circ_n.stop_fines;
93 chain.last_stop_fines_time := circ_n.stop_fines_time;
94 chain.last_checkin_time := circ_n.checkin_time;
95 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
96 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
97 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
99 IF chain.num_circs > 1 THEN
100 chain.last_renewal_time := circ_n.xact_start;
101 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
107 $$ LANGUAGE 'plpgsql';
109 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
111 usr_keep_age actor.usr_setting%ROWTYPE;
112 usr_keep_start actor.usr_setting%ROWTYPE;
113 org_keep_age INTERVAL;
114 org_use_last BOOL = false;
115 org_age_is_min BOOL = false;
121 circ_chain_head action.circulation%ROWTYPE;
122 circ_chain_tail action.circulation%ROWTYPE;
127 last_finished TIMESTAMP WITH TIME ZONE;
132 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
134 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
135 IF org_keep_count IS NULL THEN
136 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
139 SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
140 SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
142 -- First, find copies with more than keep_count non-renewal circs
145 COUNT(*) AS total_real_circs
146 FROM action.circulation
147 WHERE parent_circ IS NULL
148 AND xact_finish IS NOT NULL
150 HAVING COUNT(*) > org_keep_count
152 -- And, for those, select circs that are finished and older than keep_age
153 FOR circ_chain_head IN
154 -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
155 -- The outer query then uses that information to skip the most recent set the library wants to keep
156 -- End result is we don't care what order they come out in, as they are all potentials for deletion.
157 SELECT ac.* FROM action.circulation ac JOIN (
158 SELECT rank() OVER (ORDER BY xact_start DESC), ac.id
159 FROM action.circulation ac
160 WHERE ac.target_copy = target_acp.target_copy
161 AND ac.parent_circ IS NULL
162 ORDER BY ac.xact_start ) ranked USING (id)
163 WHERE ranked.rank > org_keep_count
166 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
167 SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id);
168 CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
170 IF NOT org_use_last THEN
171 last_finished := circ_chain_tail.xact_finish;
174 -- Now get the user settings, if any, to block purging if the user wants to keep more circs
175 usr_keep_age.value := NULL;
176 SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';
178 usr_keep_start.value := NULL;
179 SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start';
181 IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
182 IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN
183 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
185 keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL;
187 ELSIF usr_keep_start.value IS NOT NULL THEN
188 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
190 keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
193 IF org_age_is_min THEN
194 keep_age := GREATEST( keep_age, org_keep_age );
197 CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
199 -- We've passed the purging tests, purge the circ chain starting at the end
200 -- A trigger should auto-purge the rest of the chain.
201 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
203 count_purged := count_purged + 1;
210 $func$ LANGUAGE PLPGSQL;