1 --Upgrade Script for 2.5.1 to 2.5.2
2 \set eg_version '''2.5.2'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.5.2', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('0849', :eg_version);
8 UPDATE config.global_flag
9 SET label = 'Circ: Use original circulation library on desk renewal instead of the workstation library'
10 WHERE name = 'circ.desk_renewal.use_original_circ_lib';
14 SELECT evergreen.upgrade_deps_block_check('0850', :eg_version);
16 CREATE OR REPLACE FUNCTION unapi.mra ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
20 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
21 'tag:open-ils.org:U2@mra/' || mra.id AS id,
22 'tag:open-ils.org:U2@bre/' || mra.id AS record
25 FROM (SELECT XMLELEMENT(
29 cvm.value AS "coded-value",
36 FROM EACH(mra.attrs) AS x
37 JOIN config.record_attr_definition rad ON (x.key = rad.name)
38 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = x.key AND code = x.value)
42 FROM metabib.record_attr mra
44 $F$ LANGUAGE SQL STABLE;
47 SELECT evergreen.upgrade_deps_block_check('0852', :eg_version);
49 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
52 copy_context_ou INT DEFAULT NULL
53 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
54 -- support an "ahprox" measurement: adjust prox between copy circ lib and
55 -- hold request lib, but I'm unsure whether to use this theoretical
56 -- argument only in the baseline calculation or later in the other
57 -- queries in this function.
58 ) RETURNS NUMERIC AS $f$
60 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
61 ahr action.hold_request%ROWTYPE;
62 acp asset.copy%ROWTYPE;
63 acn asset.call_number%ROWTYPE;
64 acl asset.copy_location%ROWTYPE;
65 baseline_prox NUMERIC;
75 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
76 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
77 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
78 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
80 IF copy_context_ou IS NULL THEN
81 copy_context_ou := acp.circ_lib;
84 -- First, gather the baseline proximity of "here" to pickup lib
85 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
87 -- Find any absolute adjustments, and set the baseline prox to that
88 SELECT adj.* INTO aoupa
89 FROM actor.org_unit_proximity_adjustment adj
90 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
91 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
92 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acl_ol.id = adj.copy_location)
93 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
94 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
95 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
96 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
97 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
98 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
99 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
100 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
101 absolute_adjustment AND
102 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
104 COALESCE(acp_cl.distance,999)
105 + COALESCE(acn_ol.distance,999)
106 + COALESCE(acl_ol.distance,999)
107 + COALESCE(ahr_pl.distance,999)
108 + COALESCE(ahr_rl.distance,999),
113 baseline_prox := aoupa.prox_adjustment;
116 -- Now find any relative adjustments, and change the baseline prox based on them
119 FROM actor.org_unit_proximity_adjustment adj
120 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
121 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
122 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
123 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
124 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
125 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
126 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
127 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
128 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
129 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
130 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
131 NOT absolute_adjustment AND
132 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
134 baseline_prox := baseline_prox + aoupa.prox_adjustment;
137 RETURN baseline_prox;
139 $f$ LANGUAGE PLPGSQL;
142 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity(
145 copy_context_ou INT DEFAULT NULL
146 -- TODO maybe? hold_context_ou INT DEFAULT NULL. This would optionally
147 -- support an "ahprox" measurement: adjust prox between copy circ lib and
148 -- hold request lib, but I'm unsure whether to use this theoretical
149 -- argument only in the baseline calculation or later in the other
150 -- queries in this function.
151 ) RETURNS NUMERIC AS $f$
153 aoupa actor.org_unit_proximity_adjustment%ROWTYPE;
154 ahr action.hold_request%ROWTYPE;
155 acp asset.copy%ROWTYPE;
156 acn asset.call_number%ROWTYPE;
157 acl asset.copy_location%ROWTYPE;
158 baseline_prox NUMERIC;
168 SELECT * INTO ahr FROM action.hold_request WHERE id = ahr_id;
169 SELECT * INTO acp FROM asset.copy WHERE id = acp_id;
170 SELECT * INTO acn FROM asset.call_number WHERE id = acp.call_number;
171 SELECT * INTO acl FROM asset.copy_location WHERE id = acp.location;
173 IF copy_context_ou IS NULL THEN
174 copy_context_ou := acp.circ_lib;
177 -- First, gather the baseline proximity of "here" to pickup lib
178 SELECT prox INTO baseline_prox FROM actor.org_unit_proximity WHERE from_org = copy_context_ou AND to_org = ahr.pickup_lib;
180 -- Find any absolute adjustments, and set the baseline prox to that
181 SELECT adj.* INTO aoupa
182 FROM actor.org_unit_proximity_adjustment adj
183 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
184 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
185 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acl_ol.id = adj.copy_location)
186 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
187 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
188 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
189 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
190 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
191 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
192 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
193 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
194 absolute_adjustment AND
195 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
197 COALESCE(acp_cl.distance,999)
198 + COALESCE(acn_ol.distance,999)
199 + COALESCE(acl_ol.distance,999)
200 + COALESCE(ahr_pl.distance,999)
201 + COALESCE(ahr_rl.distance,999),
206 baseline_prox := aoupa.prox_adjustment;
209 -- Now find any relative adjustments, and change the baseline prox based on them
212 FROM actor.org_unit_proximity_adjustment adj
213 LEFT JOIN actor.org_unit_ancestors_distance(copy_context_ou) acp_cl ON (acp_cl.id = adj.item_circ_lib)
214 LEFT JOIN actor.org_unit_ancestors_distance(acn.owning_lib) acn_ol ON (acn_ol.id = adj.item_owning_lib)
215 LEFT JOIN actor.org_unit_ancestors_distance(acl.owning_lib) acl_ol ON (acn_ol.id = adj.copy_location)
216 LEFT JOIN actor.org_unit_ancestors_distance(ahr.pickup_lib) ahr_pl ON (ahr_pl.id = adj.hold_pickup_lib)
217 LEFT JOIN actor.org_unit_ancestors_distance(ahr.request_lib) ahr_rl ON (ahr_rl.id = adj.hold_request_lib)
218 WHERE (adj.circ_mod IS NULL OR adj.circ_mod = acp.circ_modifier) AND
219 (adj.item_circ_lib IS NULL OR adj.item_circ_lib = acp_cl.id) AND
220 (adj.item_owning_lib IS NULL OR adj.item_owning_lib = acn_ol.id) AND
221 (adj.copy_location IS NULL OR adj.copy_location = acl_ol.id) AND
222 (adj.hold_pickup_lib IS NULL OR adj.hold_pickup_lib = ahr_pl.id) AND
223 (adj.hold_request_lib IS NULL OR adj.hold_request_lib = ahr_rl.id) AND
224 NOT absolute_adjustment AND
225 COALESCE(acp_cl.id, acn_ol.id, acl_ol.id, ahr_pl.id, ahr_rl.id) IS NOT NULL
227 baseline_prox := baseline_prox + aoupa.prox_adjustment;
230 RETURN baseline_prox;
232 $f$ LANGUAGE PLPGSQL;