1 -- Evergreen DB patch 0591.schema.circ_holds_history_repairs.sql
4 -- check whether patch can be applied
5 SELECT evergreen.upgrade_deps_block_check('0591', :eg_version);
7 CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$
9 c action.circulation%ROWTYPE;
11 usr_view_age actor.usr_setting%ROWTYPE;
12 usr_view_start actor.usr_setting%ROWTYPE;
14 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age';
15 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start';
17 IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN
18 -- User opted in and supplied a retention age
19 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
20 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
22 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
24 ELSIF usr_view_start.value IS NOT NULL THEN
26 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
28 -- User did not opt in
34 FROM action.circulation
36 AND parent_circ IS NULL
37 AND xact_start > NOW() - view_age
38 ORDER BY xact_start DESC
45 $func$ LANGUAGE PLPGSQL;
47 CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$
49 h action.hold_request%ROWTYPE;
52 usr_view_count actor.usr_setting%ROWTYPE;
53 usr_view_age actor.usr_setting%ROWTYPE;
54 usr_view_start actor.usr_setting%ROWTYPE;
56 SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count';
57 SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age';
58 SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start';
62 FROM action.hold_request
64 AND fulfillment_time IS NULL
65 AND cancel_time IS NULL
66 ORDER BY request_time DESC
71 IF usr_view_start.value IS NULL THEN
75 IF usr_view_age.value IS NOT NULL THEN
76 -- User opted in and supplied a retention age
77 IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN
78 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
80 view_age := oils_json_to_text(usr_view_age.value)::INTERVAL;
84 view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ);
87 IF usr_view_count.value IS NOT NULL THEN
88 view_count := oils_json_to_text(usr_view_count.value)::INT;
93 -- show some fulfilled/canceled holds
96 FROM action.hold_request
98 AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL )
99 AND request_time > NOW() - view_age
100 ORDER BY request_time DESC
108 $func$ LANGUAGE PLPGSQL;