install-osrf.sh: avoid log spew on cstore max_children
[sitka/sitka-tools.git] / maintenance / age-circs.sql
CommitLineData
9fb9f27b
JD
1/*
2 * age-circs.sql - a script to age/anonymize patrons' circ history.
3 *
4 * Copyright (C) 2008 Equinox Software, Inc.
5 * Copyright (C) 2014 BC Libraries Cooperative
6 * Author: Jeff Davis <jdavis@sitka.bclibraries.ca>
7 *
8 * This program is free software; you can redistribute it and/or
9 * modify it under the terms of the GNU General Public License
10 * as published by the Free Software Foundation; either version 2
11 * of the License, or (at your option) any later version.
12 *
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
17 */
18
19BEGIN;
20
cedfa307
JD
21\echo 'utility function for testing interval'
22CREATE OR REPLACE FUNCTION sitka.is_valid_interval(TEXT) RETURNS BOOL AS $$
23BEGIN
24 RETURN CASE WHEN $1::INTERVAL IS NULL THEN FALSE ELSE TRUE END;
25EXCEPTION WHEN OTHERS THEN
26 RETURN FALSE;
27END;
28$$ LANGUAGE plpgsql;
29
9fb9f27b 30\echo 'exception matrix table'
a0be88cc 31CREATE TABLE IF NOT EXISTS sitka.age_circ_exception_matrix (
9fb9f27b
JD
32 id SERIAL NOT NULL,
33 active BOOLEAN NOT NULL DEFAULT TRUE,
34 org_unit INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
35 grp INT REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
36);
37
38\echo 'function for finding exceptions'
39CREATE OR REPLACE FUNCTION sitka.has_age_circ_exception(circ_id BIGINT) RETURNS BOOL AS $$
40DECLARE
41 potential_aged_circ action.circulation%ROWTYPE;
ceb63535 42 usr actor.usr%ROWTYPE;
9fb9f27b
JD
43 matching_exceptions INT;
44BEGIN
45 matching_exceptions := 0;
46 -- TODO: should we match exceptions against the entire circ chain?
47 SELECT * INTO potential_aged_circ FROM action.circulation WHERE id = circ_id;
ceb63535 48 SELECT * INTO usr FROM actor.usr WHERE id = potential_aged_circ.usr;
9fb9f27b
JD
49 SELECT count(*) INTO matching_exceptions
50 FROM sitka.age_circ_exception_matrix m
ceb63535 51 LEFT JOIN permission.grp_ancestors_distance( usr.profile ) upgad ON m.grp = upgad.id
9fb9f27b
JD
52 LEFT JOIN actor.org_unit_ancestors_distance( potential_aged_circ.circ_lib ) ctoua ON m.org_unit = ctoua.id
53 WHERE m.active
54 AND (m.grp IS NULL OR upgad.id IS NOT NULL)
55 AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL);
56 IF matching_exceptions > 0 THEN
57 RETURN TRUE;
58 END IF;
59 RETURN FALSE;
60END;
61$$ LANGUAGE plpgsql;
62
da0f32d4 63
10f5aa58 64-- function for testing whether circ should be aged
6adda430 65CREATE OR REPLACE FUNCTION sitka.age_circ_test(circ_id BIGINT, cutoff DATE, skip_basic_tests BOOL DEFAULT FALSE) RETURNS BOOLEAN AS $$
da0f32d4
JD
66DECLARE
67 potential_aged_circ action.circulation%ROWTYPE;
68 balance NUMERIC;
69 circ_chain_finish TIMESTAMP WITH TIME ZONE;
70 usr_keep_start TEXT;
71 usr_keep_age TEXT;
72 usr_keep_date DATE;
73 circ_cutoff DATE;
74 has_exception BOOL;
75BEGIN
76
77 -- use the cutoff param as the default cutoff date for the current circ
78 circ_cutoff := cutoff;
79
80 -- grab our circ
81 SELECT * INTO potential_aged_circ FROM action.circulation WHERE id = circ_id;
82
83 -- When you are batch-deleting old circs, you would normally gather a list of
84 -- closed circs with no outstanding balance, and then pass each of those
85 -- circs through the age_circ_test function to check for further exceptions.
86 -- In that situation, we don't want to waste time and processing power
87 -- re-checking whether the circs are closed or have outstanding balances,
88 -- so we provide an option to skip those basic tests.
89 IF skip_basic_tests IS FALSE THEN
90 IF potential_aged_circ.xact_finish IS NULL THEN
10f5aa58 91 RETURN FALSE;
da0f32d4
JD
92 END IF;
93 SELECT balance_owed INTO balance FROM money.materialized_billable_xact_summary WHERE id = circ_id;
94 IF balance != 0 THEN
10f5aa58 95 RETURN FALSE;
da0f32d4
JD
96 END IF;
97 END IF;
98
99 -- renewals create a "chain" of circs; find the last circ in the current chain
88189092
JD
100 -- but don't use MAX(xact_finish) since the most recent circ might have a null xact_finish
101 SELECT xact_finish INTO circ_chain_finish FROM action.circ_chain(potential_aged_circ.id)
102 ORDER BY xact_start DESC LIMIT 1;
da0f32d4
JD
103
104 -- user's history.circ.retention_start setting, if any, overrides default cutoff date
105 SELECT value INTO usr_keep_start FROM actor.usr_setting WHERE usr = potential_aged_circ.usr AND name = 'history.circ.retention_start'; IF usr_keep_start IS NOT NULL THEN
106 circ_cutoff := oils_json_to_text(usr_keep_start)::DATE;
da0f32d4
JD
107 END IF;
108
109 -- trim cutoff to user's history.circ.retention_age, if set
110 SELECT oils_json_to_text(value) INTO usr_keep_age FROM actor.usr_setting WHERE usr = potential_aged_circ.usr AND name = 'history.circ.retention_age';
111 IF usr_keep_age IS NOT NULL AND sitka.is_valid_interval(usr_keep_age) IS TRUE THEN
112 SELECT (now() - usr_keep_age::INTERVAL)::DATE INTO usr_keep_date;
113 IF usr_keep_date IS NOT NULL AND usr_keep_date < circ_cutoff THEN
114 circ_cutoff := usr_keep_date;
da0f32d4
JD
115 END IF;
116 END IF;
117
118 -- don't age the chain if the last circ is later than the cutoff
119 IF circ_chain_finish IS NULL OR circ_chain_finish > circ_cutoff THEN
10f5aa58 120 RETURN FALSE;
da0f32d4
JD
121 END IF;
122
123 -- don't age the circ if it matches a defined exception
124 has_exception := FALSE;
125 SELECT * INTO has_exception FROM sitka.has_age_circ_exception(potential_aged_circ.id);
126 IF has_exception IS TRUE THEN
10f5aa58 127 RETURN FALSE;
da0f32d4
JD
128 END IF;
129
10f5aa58
JD
130 -- this circ passes all our tests
131 RETURN TRUE;
da0f32d4
JD
132
133EXCEPTION WHEN OTHERS THEN
134 RAISE WARNING 'Cannot age circ %, error during age circ test', circ_id;
10f5aa58 135 RETURN FALSE;
da0f32d4
JD
136END;
137$$ LANGUAGE plpgsql;
138
139
9fb9f27b 140\echo 'table for aged circs'
a0be88cc 141CREATE TABLE IF NOT EXISTS sitka.aged_circs (
9fb9f27b 142 id BIGINT NOT NULL,
9fb9f27b
JD
143 delete_date DATE NOT NULL
144);
145
146\echo 'table for aged circ count'
a0be88cc 147CREATE TABLE IF NOT EXISTS sitka.aged_circs_by_user (
9fb9f27b
JD
148 usr BIGINT NOT NULL,
149 month INT NOT NULL,
150 year INT NOT NULL,
151 count INT NOT NULL DEFAULT 0,
152 UNIQUE (usr, month, year)
153);
154
155CREATE OR REPLACE FUNCTION sitka.age_circs(cutoff DATE, max_circ_count BIGINT) RETURNS INT AS $$
156DECLARE
157 today DATE;
da0f32d4 158 potential_aged_circ BIGINT;
10f5aa58 159 can_age_circ BOOL;
9fb9f27b
JD
160 circs_aged INT;
161BEGIN
162 today := now()::DATE;
163
164 -- TODO: more strictness here?
165 CREATE TEMPORARY TABLE circs_to_age AS
10f5aa58 166 SELECT c.id FROM action.circulation c
9fb9f27b
JD
167 JOIN money.materialized_billable_xact_summary m ON c.id = m.id
168 WHERE c.xact_finish IS NOT NULL AND c.xact_finish < cutoff
169 AND m.balance_owed = 0
170 ORDER BY c.xact_start
171 LIMIT max_circ_count;
172
173 -- for each circ that we might want to age...
174 FOR potential_aged_circ IN
da0f32d4 175 SELECT id FROM circs_to_age
9fb9f27b
JD
176 LOOP
177
10f5aa58 178 can_age_circ := NULL;
9fb9f27b 179
da0f32d4 180 -- test whether we can age each circ
10f5aa58
JD
181 SELECT * INTO can_age_circ FROM sitka.age_circ_test(potential_aged_circ, cutoff, TRUE);
182 IF can_age_circ IS FALSE OR can_age_circ IS NULL THEN
da0f32d4 183 DELETE FROM circs_to_age WHERE id = potential_aged_circ;
9fb9f27b
JD
184 END IF;
185
186 END LOOP;
187
9fb9f27b
JD
188 -- update user circ counts
189 CREATE TEMPORARY TABLE usr_circ_totals AS
190 SELECT a.usr, (EXTRACT(month FROM a.xact_start)) AS month,
191 (EXTRACT(year FROM a.xact_start)) AS year, count(*) AS fresh_circs
192 FROM action.circulation a
193 JOIN circs_to_age b ON a.id = b.id
194 GROUP BY 1,2,3;
195 UPDATE sitka.aged_circs_by_user a SET count = count + b.fresh_circs
196 FROM usr_circ_totals b WHERE a.usr = b.usr
197 AND a.month = b.month AND a.year = b.year;
198 INSERT INTO sitka.aged_circs_by_user (usr, month, year, count)
199 SELECT usr, month, year, fresh_circs FROM usr_circ_totals
200 WHERE (usr, month, year) NOT IN (SELECT usr, month, year FROM sitka.aged_circs_by_user);
201 DROP TABLE usr_circ_totals;
202
ceb63535
JD
203 -- old circs may have a null copy_location, but action.aged_circulation doesn't allow that
204 UPDATE action.circulation a SET copy_location = 1
205 FROM circs_to_age b WHERE a.id = b.id
206 AND a.copy_location IS NULL;
207
9fb9f27b
JD
208 -- this will set off the age_circs trigger on action.circulation
209 DELETE FROM action.circulation WHERE id IN (SELECT id FROM circs_to_age);
210
211 UPDATE action.aged_circulation
212 SET usr_post_code = NULL, usr_birth_year = NULL
213 WHERE usr_post_code IS NOT NULL OR usr_birth_year IS NOT NULL;
214
10f5aa58
JD
215 INSERT INTO sitka.aged_circs (id, delete_date)
216 SELECT id, today FROM circs_to_age;
cedfa307 217
9fb9f27b 218 SELECT count(*) INTO circs_aged FROM circs_to_age;
ceb63535 219 DROP TABLE circs_to_age;
9fb9f27b 220 RETURN circs_aged;
cedfa307 221END; $$ LANGUAGE plpgsql;
9fb9f27b
JD
222
223SAVEPOINT ready;
224
225---- To actually age circs, do something like the following:
226--SELECT count(*) FROM action.aged_circulation;
227--SELECT * FROM sitka.age_circs( (now() - '2 years'::INTERVAL)::DATE, NULL );
228--SELECT count(*) FROM action.aged_circulation;
229--
230--\echo 'SAVEPOINT done:'
231--SAVEPOINT done;
232--
233--COMMIT;
234