1 -- Apply in an update script some fixes that were previously applied only
2 -- to the base installation script 090.schema.action.sql.
4 -- Also fix a typo: INTEVAL -> INTERVAL
8 INSERT INTO config.upgrade_log (version) VALUES ('0418'); -- Scott McKellar
10 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
12 usr_keep_age actor.usr_setting%ROWTYPE;
13 usr_keep_start actor.usr_setting%ROWTYPE;
14 org_keep_age INTERVAL;
20 circ_chain_head action.circulation%ROWTYPE;
21 circ_chain_tail action.circulation%ROWTYPE;
29 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
31 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
32 IF org_keep_count IS NULL THEN
33 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
36 -- First, find copies with more than keep_count non-renewal circs
39 COUNT(*) AS total_real_circs
40 FROM action.circulation
41 WHERE parent_circ IS NULL
42 AND xact_finish IS NOT NULL
44 HAVING COUNT(*) > org_keep_count
47 -- And, for those, select circs that are finished and older than keep_age
48 FOR circ_chain_head IN
50 FROM action.circulation
51 WHERE target_copy = target_acp.target_copy
52 AND parent_circ IS NULL
56 -- Stop once we've purged enough circs to hit org_keep_count
57 EXIT WHEN target_acp.total_real_circs - purge_position <= org_keep_count;
59 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
60 EXIT WHEN circ_chain_tail.xact_finish IS NULL;
62 -- Now get the user settings, if any, to block purging if the user wants to keep more circs
63 usr_keep_age.value := NULL;
64 SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';
66 usr_keep_start.value := NULL;
67 SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start';
69 IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
70 IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN
71 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
73 keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL;
75 ELSIF usr_keep_start.value IS NOT NULL THEN
76 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
78 keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTERVAL );
81 EXIT WHEN AGE(NOW(), circ_chain_tail.xact_finish) < keep_age;
83 -- We've passed the purging tests, purge the circ chain starting at the end
84 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
85 WHILE circ_chain_tail.parent_circ IS NOT NULL LOOP
86 SELECT * INTO circ_chain_tail FROM action.circulation WHERE id = circ_chain_tail.parent_circ;
87 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
90 count_purged := count_purged + 1;
91 purge_position := purge_position + 1;
96 $func$ LANGUAGE PLPGSQL;