1 -- Evergreen DB patch 0677.schema.circ_limits.sql
3 -- FIXME: insert description of change, if needed
8 -- check whether patch can be applied
9 SELECT evergreen.upgrade_deps_block_check('0677', :eg_version);
11 -- FIXME: add/check SQL statements to perform the upgrade
12 -- Limit groups for circ counting
13 CREATE TABLE config.circ_limit_group (
14 id SERIAL PRIMARY KEY,
15 name TEXT UNIQUE NOT NULL,
20 CREATE TABLE config.circ_limit_set (
21 id SERIAL PRIMARY KEY,
22 name TEXT UNIQUE NOT NULL,
23 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
24 items_out INT NOT NULL, -- Total current active circulations must be less than this. 0 means skip counting (always pass)
25 depth INT NOT NULL DEFAULT 0, -- Depth count starts at
26 global BOOL NOT NULL DEFAULT FALSE, -- If enabled, include everything below depth, otherwise ancestors/descendants only
30 -- Linkage between matchpoints and limit sets
31 CREATE TABLE config.circ_matrix_limit_set_map (
32 id SERIAL PRIMARY KEY,
33 matchpoint INT NOT NULL REFERENCES config.circ_matrix_matchpoint (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
34 limit_set INT NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
35 fallthrough BOOL NOT NULL DEFAULT FALSE, -- If true fallthrough will grab this rule as it goes along
36 active BOOL NOT NULL DEFAULT TRUE,
37 CONSTRAINT circ_limit_set_once_per_matchpoint UNIQUE (matchpoint, limit_set)
40 -- Linkage between limit sets and circ mods
41 CREATE TABLE config.circ_limit_set_circ_mod_map (
42 id SERIAL PRIMARY KEY,
43 limit_set INT NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
44 circ_mod TEXT NOT NULL REFERENCES config.circ_modifier (code) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
45 CONSTRAINT cm_once_per_set UNIQUE (limit_set, circ_mod)
48 -- Linkage between limit sets and limit groups
49 CREATE TABLE config.circ_limit_set_group_map (
50 id SERIAL PRIMARY KEY,
51 limit_set INT NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
52 limit_group INT NOT NULL REFERENCES config.circ_limit_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
53 check_only BOOL NOT NULL DEFAULT FALSE, -- If true, don't accumulate this limit_group for storing with the circulation
54 CONSTRAINT clg_once_per_set UNIQUE (limit_set, limit_group)
57 -- Linkage between limit groups and circulations
58 CREATE TABLE action.circulation_limit_group_map (
59 circ BIGINT NOT NULL REFERENCES action.circulation (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
60 limit_group INT NOT NULL REFERENCES config.circ_limit_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
61 PRIMARY KEY (circ, limit_group)
64 -- Function for populating the circ/limit group mappings
65 CREATE OR REPLACE FUNCTION action.link_circ_limit_groups ( BIGINT, INT[] ) RETURNS VOID AS $func$
66 INSERT INTO action.circulation_limit_group_map(circ, limit_group) SELECT $1, id FROM config.circ_limit_group WHERE id IN (SELECT * FROM UNNEST($2));
69 DROP TYPE IF EXISTS action.circ_matrix_test_result CASCADE;
70 CREATE TYPE action.circ_matrix_test_result AS ( success BOOL, fail_part TEXT, buildrows INT[], matchpoint INT, circulate BOOL, duration_rule INT, recurring_fine_rule INT, max_fine_rule INT, hard_due_date INT, renewals INT, grace_period INTERVAL, limit_groups INT[] );
72 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$
74 user_object actor.usr%ROWTYPE;
75 standing_penalty config.standing_penalty%ROWTYPE;
76 item_object asset.copy%ROWTYPE;
77 item_status_object config.copy_status%ROWTYPE;
78 item_location_object asset.copy_location%ROWTYPE;
79 result action.circ_matrix_test_result;
80 circ_test action.found_circ_matrix_matchpoint;
81 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
82 circ_limit_set config.circ_limit_set%ROWTYPE;
83 hold_ratio action.hold_stats%ROWTYPE;
86 context_org_list INT[];
89 -- Assume success unless we hit a failure condition
90 result.success := TRUE;
92 -- Need user info to look up matchpoints
93 SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
95 -- (Insta)Fail if we couldn't find the user
96 IF user_object.id IS NULL THEN
97 result.fail_part := 'no_user';
98 result.success := FALSE;
104 -- Need item info to look up matchpoints
105 SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
107 -- (Insta)Fail if we couldn't find the item
108 IF item_object.id IS NULL THEN
109 result.fail_part := 'no_item';
110 result.success := FALSE;
116 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
118 circ_matchpoint := circ_test.matchpoint;
119 result.matchpoint := circ_matchpoint.id;
120 result.circulate := circ_matchpoint.circulate;
121 result.duration_rule := circ_matchpoint.duration_rule;
122 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
123 result.max_fine_rule := circ_matchpoint.max_fine_rule;
124 result.hard_due_date := circ_matchpoint.hard_due_date;
125 result.renewals := circ_matchpoint.renewals;
126 result.grace_period := circ_matchpoint.grace_period;
127 result.buildrows := circ_test.buildrows;
129 -- (Insta)Fail if we couldn't find a matchpoint
130 IF circ_test.success = false THEN
131 result.fail_part := 'no_matchpoint';
132 result.success := FALSE;
138 -- All failures before this point are non-recoverable
139 -- Below this point are possibly overridable failures
141 -- Fail if the user is barred
142 IF user_object.barred IS TRUE THEN
143 result.fail_part := 'actor.usr.barred';
144 result.success := FALSE;
149 -- Fail if the item can't circulate
150 IF item_object.circulate IS FALSE THEN
151 result.fail_part := 'asset.copy.circulate';
152 result.success := FALSE;
157 -- Fail if the item isn't in a circulateable status on a non-renewal
158 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
159 result.fail_part := 'asset.copy.status';
160 result.success := FALSE;
163 -- Alternately, fail if the item isn't checked out on a renewal
164 ELSIF renewal AND item_object.status <> 1 THEN
165 result.fail_part := 'asset.copy.status';
166 result.success := FALSE;
171 -- Fail if the item can't circulate because of the shelving location
172 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
173 IF item_location_object.circulate IS FALSE THEN
174 result.fail_part := 'asset.copy_location.circulate';
175 result.success := FALSE;
180 -- Use Circ OU for penalties and such
181 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );
184 penalty_type = '%RENEW%';
186 penalty_type = '%CIRC%';
189 FOR standing_penalty IN
190 SELECT DISTINCT csp.*
191 FROM actor.usr_standing_penalty usp
192 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
193 WHERE usr = match_user
194 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
195 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
196 AND csp.block_list LIKE penalty_type LOOP
198 result.fail_part := standing_penalty.name;
199 result.success := FALSE;
204 -- Fail if the test is set to hard non-circulating
205 IF circ_matchpoint.circulate IS FALSE THEN
206 result.fail_part := 'config.circ_matrix_test.circulate';
207 result.success := FALSE;
212 -- Fail if the total copy-hold ratio is too low
213 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
214 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
215 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
216 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
217 result.success := FALSE;
223 -- Fail if the available copy-hold ratio is too low
224 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
225 IF hold_ratio.hold_count IS NULL THEN
226 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
228 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
229 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
230 result.success := FALSE;
236 -- Fail if the user has too many items out by defined limit sets
237 FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
238 JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
239 WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
240 ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
242 IF circ_limit_set.items_out > 0 AND NOT renewal THEN
243 SELECT INTO context_org_list ARRAY_AGG(aou.id)
244 FROM actor.org_unit_full_path( circ_ou ) aou
245 JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
246 WHERE aout.depth >= circ_limit_set.depth;
247 IF circ_limit_set.global THEN
248 WITH RECURSIVE descendant_depth AS (
251 FROM actor.org_unit ou
252 WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
256 FROM actor.org_unit ou
257 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
258 ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
260 SELECT INTO items_out COUNT(DISTINCT circ.id)
261 FROM action.circulation circ
262 JOIN asset.copy copy ON (copy.id = circ.target_copy)
263 LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
264 WHERE circ.usr = match_user
265 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
266 AND circ.checkin_time IS NULL
267 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
268 AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
269 OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
271 IF items_out >= circ_limit_set.items_out THEN
272 result.fail_part := 'config.circ_matrix_circ_mod_test';
273 result.success := FALSE;
278 SELECT INTO result.limit_groups result.limit_groups || ARRAY_AGG(limit_group) FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id AND NOT check_only;
281 -- If we passed everything, return the successful matchpoint
288 $func$ LANGUAGE plpgsql;
290 -- We need to re-create these, as they got dropped with the type above.
291 CREATE OR REPLACE FUNCTION action.item_user_circ_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$
292 SELECT * FROM action.item_user_circ_test( $1, $2, $3, FALSE );
295 CREATE OR REPLACE FUNCTION action.item_user_renew_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$
296 SELECT * FROM action.item_user_circ_test( $1, $2, $3, TRUE );
299 -- Temp function for migrating circ mod limits.
300 CREATE OR REPLACE FUNCTION evergreen.temp_migrate_circ_mod_limits() RETURNS VOID AS $func$
302 circ_mod_group config.circ_matrix_circ_mod_test%ROWTYPE;
306 FOR circ_mod_group IN SELECT * FROM config.circ_matrix_circ_mod_test LOOP
307 INSERT INTO config.circ_limit_set(name, owning_lib, items_out, depth, global, description)
308 SELECT org_unit || ' : Matchpoint ' || circ_mod_group.matchpoint || ' : Circ Mod Test ' || circ_mod_group.id, org_unit, circ_mod_group.items_out, 0, false, 'Migrated from Circ Mod Test System'
309 FROM config.circ_matrix_matchpoint WHERE id = circ_mod_group.matchpoint
310 RETURNING id INTO current_set;
311 INSERT INTO config.circ_matrix_limit_set_map(matchpoint, limit_set, fallthrough, active) VALUES (circ_mod_group.matchpoint, current_set, false, true);
312 INSERT INTO config.circ_limit_set_circ_mod_map(limit_set, circ_mod)
313 SELECT current_set, circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = circ_mod_group.id;
314 SELECT INTO circ_mod_count count(id) FROM config.circ_limit_set_circ_mod_map WHERE limit_set = current_set;
315 RAISE NOTICE 'Created limit set with id % and % circ modifiers attached to matchpoint %', current_set, circ_mod_count, circ_mod_group.matchpoint;
318 $func$ LANGUAGE plpgsql;
320 -- Run the temp function
321 SELECT * FROM evergreen.temp_migrate_circ_mod_limits();
323 -- Drop the temp function
324 DROP FUNCTION evergreen.temp_migrate_circ_mod_limits();
326 --Drop the old tables
327 --Not sure we want to do this. Keeping them may help "something went wrong" correction.
328 --DROP TABLE IF EXISTS config.circ_matrix_circ_mod_test_map, config.circ_matrix_circ_mod_test;