Commit | Line | Data |
---|---|---|
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 | ||
19 | BEGIN; | |
20 | ||
cedfa307 JD |
21 | \echo 'utility function for testing interval' |
22 | CREATE OR REPLACE FUNCTION sitka.is_valid_interval(TEXT) RETURNS BOOL AS $$ | |
23 | BEGIN | |
24 | RETURN CASE WHEN $1::INTERVAL IS NULL THEN FALSE ELSE TRUE END; | |
25 | EXCEPTION WHEN OTHERS THEN | |
26 | RETURN FALSE; | |
27 | END; | |
28 | $$ LANGUAGE plpgsql; | |
29 | ||
9fb9f27b JD |
30 | \echo 'exception matrix table' |
31 | CREATE TABLE sitka.age_circ_exception_matrix ( | |
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' | |
39 | CREATE OR REPLACE FUNCTION sitka.has_age_circ_exception(circ_id BIGINT) RETURNS BOOL AS $$ | |
40 | DECLARE | |
41 | potential_aged_circ action.circulation%ROWTYPE; | |
ceb63535 | 42 | usr actor.usr%ROWTYPE; |
9fb9f27b JD |
43 | matching_exceptions INT; |
44 | BEGIN | |
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; | |
60 | END; | |
61 | $$ LANGUAGE plpgsql; | |
62 | ||
da0f32d4 JD |
63 | |
64 | -- Function for testing whether circ should be aged. | |
65 | -- If circ CANNOT be aged based on exceptions and user settings, function returns NULL. | |
66 | -- If circ CAN be aged, function returns xact_finish timestamp of last circ in chain. | |
67 | CREATE OR REPLACE FUNCTION sitka.age_circ_test(circ_id BIGINT, cutoff DATE, skip_basic_tests BOOL DEFAULT FALSE) RETURNS TIMESTAMPTZ AS $$ | |
68 | DECLARE | |
69 | potential_aged_circ action.circulation%ROWTYPE; | |
70 | balance NUMERIC; | |
71 | circ_chain_finish TIMESTAMP WITH TIME ZONE; | |
72 | usr_keep_start TEXT; | |
73 | usr_keep_age TEXT; | |
74 | usr_keep_date DATE; | |
75 | circ_cutoff DATE; | |
76 | has_exception BOOL; | |
77 | BEGIN | |
78 | ||
79 | -- use the cutoff param as the default cutoff date for the current circ | |
80 | circ_cutoff := cutoff; | |
81 | ||
82 | -- grab our circ | |
83 | SELECT * INTO potential_aged_circ FROM action.circulation WHERE id = circ_id; | |
84 | ||
85 | -- When you are batch-deleting old circs, you would normally gather a list of | |
86 | -- closed circs with no outstanding balance, and then pass each of those | |
87 | -- circs through the age_circ_test function to check for further exceptions. | |
88 | -- In that situation, we don't want to waste time and processing power | |
89 | -- re-checking whether the circs are closed or have outstanding balances, | |
90 | -- so we provide an option to skip those basic tests. | |
91 | IF skip_basic_tests IS FALSE THEN | |
92 | IF potential_aged_circ.xact_finish IS NULL THEN | |
93 | --RAISE NOTICE 'Cannot age circ %, circ is not closed', circ_id; | |
94 | RETURN NULL; | |
95 | END IF; | |
96 | SELECT balance_owed INTO balance FROM money.materialized_billable_xact_summary WHERE id = circ_id; | |
97 | IF balance != 0 THEN | |
98 | --RAISE NOTICE 'Cannot age circ %, non-zero balance', circ_id; | |
99 | RETURN NULL; | |
100 | END IF; | |
101 | END IF; | |
102 | ||
103 | -- renewals create a "chain" of circs; find the last circ in the current chain | |
104 | SELECT MAX(xact_finish) INTO circ_chain_finish FROM action.circ_chain(potential_aged_circ.id); | |
105 | ||
106 | -- user's history.circ.retention_start setting, if any, overrides default cutoff date | |
107 | 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 | |
108 | circ_cutoff := oils_json_to_text(usr_keep_start)::DATE; | |
109 | --RAISE NOTICE 'circ cutoff adjusted to % for circ %', circ_cutoff, circ_id; | |
110 | END IF; | |
111 | ||
112 | -- trim cutoff to user's history.circ.retention_age, if set | |
113 | 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'; | |
114 | IF usr_keep_age IS NOT NULL AND sitka.is_valid_interval(usr_keep_age) IS TRUE THEN | |
115 | SELECT (now() - usr_keep_age::INTERVAL)::DATE INTO usr_keep_date; | |
116 | IF usr_keep_date IS NOT NULL AND usr_keep_date < circ_cutoff THEN | |
117 | circ_cutoff := usr_keep_date; | |
118 | --RAISE NOTICE 'circ cutoff adjusted to % for circ %', circ_cutoff, circ_id; | |
119 | END IF; | |
120 | END IF; | |
121 | ||
122 | -- don't age the chain if the last circ is later than the cutoff | |
123 | IF circ_chain_finish IS NULL OR circ_chain_finish > circ_cutoff THEN | |
124 | --RAISE NOTICE 'Cannot age circ %, circ chain is still open or was open on cutoff date', circ_id; | |
125 | RETURN NULL; | |
126 | END IF; | |
127 | ||
128 | -- don't age the circ if it matches a defined exception | |
129 | has_exception := FALSE; | |
130 | SELECT * INTO has_exception FROM sitka.has_age_circ_exception(potential_aged_circ.id); | |
131 | IF has_exception IS TRUE THEN | |
132 | --RAISE NOTICE 'Cannot age circ %, has exceptions', circ_id; | |
133 | RETURN NULL; | |
134 | END IF; | |
135 | ||
136 | -- this circ passes all our tests; return the timestamp when the circ chain was closed | |
137 | RETURN circ_chain_finish; | |
138 | ||
139 | EXCEPTION WHEN OTHERS THEN | |
140 | RAISE WARNING 'Cannot age circ %, error during age circ test', circ_id; | |
141 | RETURN NULL; | |
142 | END; | |
143 | $$ LANGUAGE plpgsql; | |
144 | ||
145 | ||
9fb9f27b JD |
146 | \echo 'table for aged circs' |
147 | CREATE TABLE sitka.aged_circs ( | |
148 | id BIGINT NOT NULL, | |
5f2162a2 | 149 | circ_chain_tail_xact_finish TIMESTAMPTZ NOT NULL, |
9fb9f27b JD |
150 | delete_date DATE NOT NULL |
151 | ); | |
152 | ||
153 | \echo 'table for aged circ count' | |
154 | CREATE TABLE sitka.aged_circs_by_user ( | |
155 | usr BIGINT NOT NULL, | |
156 | month INT NOT NULL, | |
157 | year INT NOT NULL, | |
158 | count INT NOT NULL DEFAULT 0, | |
159 | UNIQUE (usr, month, year) | |
160 | ); | |
161 | ||
162 | CREATE OR REPLACE FUNCTION sitka.age_circs(cutoff DATE, max_circ_count BIGINT) RETURNS INT AS $$ | |
163 | DECLARE | |
164 | today DATE; | |
da0f32d4 JD |
165 | potential_aged_circ BIGINT; |
166 | circ_chain_finish TIMESTAMPTZ; | |
9fb9f27b JD |
167 | circs_aged INT; |
168 | BEGIN | |
169 | today := now()::DATE; | |
170 | ||
171 | -- TODO: more strictness here? | |
172 | CREATE TEMPORARY TABLE circs_to_age AS | |
cedfa307 JD |
173 | SELECT c.id, NULL::TIMESTAMP WITH TIME ZONE AS circ_chain_tail_xact_finish |
174 | FROM action.circulation c | |
9fb9f27b JD |
175 | JOIN money.materialized_billable_xact_summary m ON c.id = m.id |
176 | WHERE c.xact_finish IS NOT NULL AND c.xact_finish < cutoff | |
177 | AND m.balance_owed = 0 | |
178 | ORDER BY c.xact_start | |
179 | LIMIT max_circ_count; | |
180 | ||
181 | -- for each circ that we might want to age... | |
182 | FOR potential_aged_circ IN | |
da0f32d4 | 183 | SELECT id FROM circs_to_age |
9fb9f27b JD |
184 | LOOP |
185 | ||
cedfa307 | 186 | circ_chain_finish := NULL; |
9fb9f27b | 187 | |
da0f32d4 JD |
188 | -- test whether we can age each circ |
189 | SELECT * INTO circ_chain_finish FROM sitka.age_circ_test(potential_aged_circ, cutoff, TRUE); | |
190 | IF circ_chain_finish IS NOT NULL THEN | |
191 | UPDATE circs_to_age SET circ_chain_tail_xact_finish = circ_chain_finish WHERE id = potential_aged_circ; | |
192 | ELSE | |
193 | DELETE FROM circs_to_age WHERE id = potential_aged_circ; | |
9fb9f27b JD |
194 | END IF; |
195 | ||
196 | END LOOP; | |
197 | ||
9fb9f27b JD |
198 | -- update user circ counts |
199 | CREATE TEMPORARY TABLE usr_circ_totals AS | |
200 | SELECT a.usr, (EXTRACT(month FROM a.xact_start)) AS month, | |
201 | (EXTRACT(year FROM a.xact_start)) AS year, count(*) AS fresh_circs | |
202 | FROM action.circulation a | |
203 | JOIN circs_to_age b ON a.id = b.id | |
204 | GROUP BY 1,2,3; | |
205 | UPDATE sitka.aged_circs_by_user a SET count = count + b.fresh_circs | |
206 | FROM usr_circ_totals b WHERE a.usr = b.usr | |
207 | AND a.month = b.month AND a.year = b.year; | |
208 | INSERT INTO sitka.aged_circs_by_user (usr, month, year, count) | |
209 | SELECT usr, month, year, fresh_circs FROM usr_circ_totals | |
210 | WHERE (usr, month, year) NOT IN (SELECT usr, month, year FROM sitka.aged_circs_by_user); | |
211 | DROP TABLE usr_circ_totals; | |
212 | ||
ceb63535 JD |
213 | -- old circs may have a null copy_location, but action.aged_circulation doesn't allow that |
214 | UPDATE action.circulation a SET copy_location = 1 | |
215 | FROM circs_to_age b WHERE a.id = b.id | |
216 | AND a.copy_location IS NULL; | |
217 | ||
9fb9f27b JD |
218 | -- this will set off the age_circs trigger on action.circulation |
219 | DELETE FROM action.circulation WHERE id IN (SELECT id FROM circs_to_age); | |
220 | ||
221 | UPDATE action.aged_circulation | |
222 | SET usr_post_code = NULL, usr_birth_year = NULL | |
223 | WHERE usr_post_code IS NOT NULL OR usr_birth_year IS NOT NULL; | |
224 | ||
cedfa307 JD |
225 | INSERT INTO sitka.aged_circs (id, circ_chain_tail_xact_finish, delete_date) |
226 | SELECT id, circ_chain_tail_xact_finish, today FROM circs_to_age; | |
227 | ||
9fb9f27b | 228 | SELECT count(*) INTO circs_aged FROM circs_to_age; |
ceb63535 | 229 | DROP TABLE circs_to_age; |
9fb9f27b | 230 | RETURN circs_aged; |
cedfa307 | 231 | END; $$ LANGUAGE plpgsql; |
9fb9f27b JD |
232 | |
233 | SAVEPOINT ready; | |
234 | ||
235 | ---- To actually age circs, do something like the following: | |
236 | --SELECT count(*) FROM action.aged_circulation; | |
237 | --SELECT * FROM sitka.age_circs( (now() - '2 years'::INTERVAL)::DATE, NULL ); | |
238 | --SELECT count(*) FROM action.aged_circulation; | |
239 | -- | |
240 | --\echo 'SAVEPOINT done:' | |
241 | --SAVEPOINT done; | |
242 | -- | |
243 | --COMMIT; | |
244 |