3 INSERT INTO config.upgrade_log (version) VALUES ('0281');
5 -- We need this data to do anything interesting, so just add it in the schema upgrade script
6 INSERT INTO config.global_flag (name,label,enabled)
7 VALUES ('history.circ.retention_age',oils_i18n_gettext('history.circ.retention_age', 'Historical Circulation Retention Age', 'cgf', 'label'), TRUE);
8 INSERT INTO config.global_flag (name,label,enabled)
9 VALUES ('history.circ.retention_count',oils_i18n_gettext('history.circ.retention_count', 'Historical Circulations per Copy', 'cgf', 'label'), TRUE);
11 INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype)
12 VALUES ('history.circ.retention_age', TRUE, 'Historical Circulation Retention Age', 'Historical Circulation Retention Age', 'interval');
13 INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype)
14 VALUES ('history.circ.retention_start', FALSE, 'Historical Circulation Retention Start Date', 'Historical Circulation Retention Start Date', 'date');
17 -- upgrade is_json to allow non-ref JSON (strings, numbers, etc)
18 CREATE OR REPLACE FUNCTION is_json( TEXT ) RETURNS BOOL AS $f$
21 eval { JSON::XS->new->allow_nonref->decode( $json ) };
25 -- turn a JSON scalar into an SQL TEXT value
26 CREATE OR REPLACE FUNCTION oils_json_to_text( TEXT ) RETURNS TEXT AS $f$
30 eval { $txt = JSON::XS->new->allow_nonref->decode( $json ) };
35 -- Return the list of circ chain heads in xact_start order that the user has chosen to "retain"
36 CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$
38 c action.circulation%ROWTYPE;
40 usr_view_age actor.usr_setting%ROWTYPE;
41 usr_view_start actor.usr_setting%ROWTYPE;
43 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age';
44 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start_date';
46 IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN
47 -- User opted in and supplied a retention age
48 IF oils_json_to_string(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ) THEN
49 view_age := AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ);
51 view_age := oils_json_to_string(usr_view_age.value)::INTERVAL;
53 ELSIF usr_view_start.value IS NOT NULL THEN
55 view_age := AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ);
57 -- User did not opt in
63 FROM action.circulation
65 AND parent_circ IS NULL
66 AND xact_start < NOW() - view_age
74 $func$ LANGUAGE PLPGSQL;
76 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
78 usr_keep_age actor.usr_setting%ROWTYPE;
79 usr_keep_start actor.usr_setting%ROWTYPE;
80 org_keep_age INTERVAL;
86 circ_chain_head action.circulation%ROWTYPE;
87 circ_chain_tail action.circulation%ROWTYPE;
95 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
97 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
98 IF org_keep_count IS NULL THEN
99 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
102 -- First, find copies with more than keep_count non-renewal circs
105 COUNT(*) AS total_real_circs
106 FROM action.circulation
107 WHERE parent_circ IS NULL
108 AND xact_finish IS NOT NULL
110 HAVING COUNT(*) > org_keep_count
113 -- And, for those, select circs that are finished and older than keep_age
114 FOR circ_chain_head IN
116 FROM action.circulation
117 WHERE target_copy = target_acp.target_copy
118 AND parent_circ IS NULL
122 -- Stop once we've purged enough circs to hit org_keep_count
123 EXIT WHEN target_acp.total_real_circs - purge_position <= org_keep_count;
125 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
126 EXIT WHEN circ_chain_tail.xact_finish IS NULL;
128 -- Now get the user setings, if any, to block purging if the user wants to keep more circs
129 usr_keep_age.value := NULL;
130 SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';
132 usr_keep_start.value := NULL;
133 SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start_date';
135 IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
136 IF oils_json_to_string(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_string(usr_keep_start.value)::TIMESTAMPTZ) THEN
137 keep_age := AGE(NOW(), oils_json_to_string(usr_keep_start.value)::TIMESTAMPTZ);
139 keep_age := oils_json_to_string(usr_keep_age.value)::INTERVAL;
141 ELSIF usr_keep_start.value IS NOT NULL THEN
142 keep_age := AGE(NOW(), oils_json_to_string(usr_keep_start.value)::TIMESTAMPTZ);
144 keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTEVAL );
147 EXIT WHEN AGE(NOW(), circ_chain_tail.xact_finish) < keep_age;
149 -- We've passed the purging tests, purge the circ chain starting at the end
150 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
151 WHILE circ_chain_tail.parent_circ IS NOT NULL LOOP
152 SELECT * INTO circ_chain_tail FROM action.circulation WHERE id = circ_chain_tail.parent_circ;
153 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
156 count_purged := count_purged + 1;
157 purge_position := purge_position + 1;
162 $func$ LANGUAGE PLPGSQL;