1 --Upgrade Script for 2.10.0 to 2.10.1
2 \set eg_version '''2.10.1'''
5 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.10.1', :eg_version);
7 SELECT evergreen.upgrade_deps_block_check('0975', :eg_version);
9 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
37 attr_set vandelay.import_item%ROWTYPE;
44 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
48 attr_set.definition := attr_def.id;
50 -- Build the combined XPath
54 WHEN attr_def.owning_lib IS NULL THEN 'null()'
55 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '*[@code="' || attr_def.owning_lib || '"]'
56 ELSE '*' || attr_def.owning_lib
61 WHEN attr_def.circ_lib IS NULL THEN 'null()'
62 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '*[@code="' || attr_def.circ_lib || '"]'
63 ELSE '*' || attr_def.circ_lib
68 WHEN attr_def.call_number IS NULL THEN 'null()'
69 WHEN LENGTH( attr_def.call_number ) = 1 THEN '*[@code="' || attr_def.call_number || '"]'
70 ELSE '*' || attr_def.call_number
75 WHEN attr_def.copy_number IS NULL THEN 'null()'
76 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '*[@code="' || attr_def.copy_number || '"]'
77 ELSE '*' || attr_def.copy_number
82 WHEN attr_def.status IS NULL THEN 'null()'
83 WHEN LENGTH( attr_def.status ) = 1 THEN '*[@code="' || attr_def.status || '"]'
84 ELSE '*' || attr_def.status
89 WHEN attr_def.location IS NULL THEN 'null()'
90 WHEN LENGTH( attr_def.location ) = 1 THEN '*[@code="' || attr_def.location || '"]'
91 ELSE '*' || attr_def.location
96 WHEN attr_def.circulate IS NULL THEN 'null()'
97 WHEN LENGTH( attr_def.circulate ) = 1 THEN '*[@code="' || attr_def.circulate || '"]'
98 ELSE '*' || attr_def.circulate
103 WHEN attr_def.deposit IS NULL THEN 'null()'
104 WHEN LENGTH( attr_def.deposit ) = 1 THEN '*[@code="' || attr_def.deposit || '"]'
105 ELSE '*' || attr_def.deposit
110 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
111 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '*[@code="' || attr_def.deposit_amount || '"]'
112 ELSE '*' || attr_def.deposit_amount
117 WHEN attr_def.ref IS NULL THEN 'null()'
118 WHEN LENGTH( attr_def.ref ) = 1 THEN '*[@code="' || attr_def.ref || '"]'
119 ELSE '*' || attr_def.ref
124 WHEN attr_def.holdable IS NULL THEN 'null()'
125 WHEN LENGTH( attr_def.holdable ) = 1 THEN '*[@code="' || attr_def.holdable || '"]'
126 ELSE '*' || attr_def.holdable
131 WHEN attr_def.price IS NULL THEN 'null()'
132 WHEN LENGTH( attr_def.price ) = 1 THEN '*[@code="' || attr_def.price || '"]'
133 ELSE '*' || attr_def.price
138 WHEN attr_def.barcode IS NULL THEN 'null()'
139 WHEN LENGTH( attr_def.barcode ) = 1 THEN '*[@code="' || attr_def.barcode || '"]'
140 ELSE '*' || attr_def.barcode
145 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
146 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '*[@code="' || attr_def.circ_modifier || '"]'
147 ELSE '*' || attr_def.circ_modifier
152 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
153 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '*[@code="' || attr_def.circ_as_type || '"]'
154 ELSE '*' || attr_def.circ_as_type
159 WHEN attr_def.alert_message IS NULL THEN 'null()'
160 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '*[@code="' || attr_def.alert_message || '"]'
161 ELSE '*' || attr_def.alert_message
166 WHEN attr_def.opac_visible IS NULL THEN 'null()'
167 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '*[@code="' || attr_def.opac_visible || '"]'
168 ELSE '*' || attr_def.opac_visible
173 WHEN attr_def.pub_note IS NULL THEN 'null()'
174 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '*[@code="' || attr_def.pub_note || '"]'
175 ELSE '*' || attr_def.pub_note
179 WHEN attr_def.priv_note IS NULL THEN 'null()'
180 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '*[@code="' || attr_def.priv_note || '"]'
181 ELSE '*' || attr_def.priv_note
186 WHEN attr_def.internal_id IS NULL THEN 'null()'
187 WHEN LENGTH( attr_def.internal_id ) = 1 THEN '*[@code="' || attr_def.internal_id || '"]'
188 ELSE '*' || attr_def.internal_id
193 WHEN attr_def.stat_cat_data IS NULL THEN 'null()'
194 WHEN LENGTH( attr_def.stat_cat_data ) = 1 THEN '*[@code="' || attr_def.stat_cat_data || '"]'
195 ELSE '*' || attr_def.stat_cat_data
200 WHEN attr_def.parts_data IS NULL THEN 'null()'
201 WHEN LENGTH( attr_def.parts_data ) = 1 THEN '*[@code="' || attr_def.parts_data || '"]'
202 ELSE '*' || attr_def.parts_data
207 xpaths := ARRAY[owning_lib, circ_lib, call_number, copy_number, status, location, circulate,
208 deposit, deposit_amount, ref, holdable, price, barcode, circ_modifier, circ_as_type,
209 alert_message, pub_note, priv_note, internal_id, stat_cat_data, parts_data, opac_visible];
213 FROM oils_xpath_tag_to_table( (SELECT marc FROM vandelay.queued_bib_record WHERE id = import_id), attr_def.tag, xpaths)
214 AS t( ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
215 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
216 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, internal_id TEXT,
217 stat_cat_data TEXT, parts_data TEXT, opac_vis TEXT )
220 attr_set.import_error := NULL;
221 attr_set.error_detail := NULL;
222 attr_set.deposit_amount := NULL;
223 attr_set.copy_number := NULL;
224 attr_set.price := NULL;
225 attr_set.circ_modifier := NULL;
226 attr_set.location := NULL;
227 attr_set.barcode := NULL;
228 attr_set.call_number := NULL;
230 IF tmp_attr_set.pr != '' THEN
231 tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
233 attr_set.import_error := 'import.item.invalid.price';
234 attr_set.error_detail := tmp_attr_set.pr; -- original value
235 RETURN NEXT attr_set; CONTINUE;
237 attr_set.price := tmp_str::NUMERIC(8,2);
240 IF tmp_attr_set.dep_amount != '' THEN
241 tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
243 attr_set.import_error := 'import.item.invalid.deposit_amount';
244 attr_set.error_detail := tmp_attr_set.dep_amount;
245 RETURN NEXT attr_set; CONTINUE;
247 attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
250 IF tmp_attr_set.cnum != '' THEN
251 tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
253 attr_set.import_error := 'import.item.invalid.copy_number';
254 attr_set.error_detail := tmp_attr_set.cnum;
255 RETURN NEXT attr_set; CONTINUE;
257 attr_set.copy_number := tmp_str::INT;
260 IF tmp_attr_set.ol != '' THEN
261 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
263 attr_set.import_error := 'import.item.invalid.owning_lib';
264 attr_set.error_detail := tmp_attr_set.ol;
265 RETURN NEXT attr_set; CONTINUE;
269 IF tmp_attr_set.clib != '' THEN
270 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
272 attr_set.import_error := 'import.item.invalid.circ_lib';
273 attr_set.error_detail := tmp_attr_set.clib;
274 RETURN NEXT attr_set; CONTINUE;
278 IF tmp_attr_set.cs != '' THEN
279 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
281 attr_set.import_error := 'import.item.invalid.status';
282 attr_set.error_detail := tmp_attr_set.cs;
283 RETURN NEXT attr_set; CONTINUE;
287 IF COALESCE(tmp_attr_set.circ_mod, '') = '' THEN
289 -- no circ mod defined, see if we should apply a default
290 SELECT INTO attr_set.circ_modifier TRIM(BOTH '"' FROM value)
291 FROM actor.org_unit_ancestor_setting(
292 'vandelay.item.circ_modifier.default',
296 -- make sure the value from the org setting is still valid
297 PERFORM 1 FROM config.circ_modifier WHERE code = attr_set.circ_modifier;
299 attr_set.import_error := 'import.item.invalid.circ_modifier';
300 attr_set.error_detail := tmp_attr_set.circ_mod;
301 RETURN NEXT attr_set; CONTINUE;
306 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
308 attr_set.import_error := 'import.item.invalid.circ_modifier';
309 attr_set.error_detail := tmp_attr_set.circ_mod;
310 RETURN NEXT attr_set; CONTINUE;
314 IF tmp_attr_set.circ_as != '' THEN
315 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
317 attr_set.import_error := 'import.item.invalid.circ_as_type';
318 attr_set.error_detail := tmp_attr_set.circ_as;
319 RETURN NEXT attr_set; CONTINUE;
323 IF COALESCE(tmp_attr_set.cl, '') = '' THEN
324 -- no location specified, see if we should apply a default
326 SELECT INTO attr_set.location TRIM(BOTH '"' FROM value)
327 FROM actor.org_unit_ancestor_setting(
328 'vandelay.item.copy_location.default',
332 -- make sure the value from the org setting is still valid
333 PERFORM 1 FROM asset.copy_location
334 WHERE id = attr_set.location AND NOT deleted;
336 attr_set.import_error := 'import.item.invalid.location';
337 attr_set.error_detail := tmp_attr_set.cs;
338 RETURN NEXT attr_set; CONTINUE;
342 -- search up the org unit tree for a matching copy location
343 WITH RECURSIVE anscestor_depth AS (
347 FROM actor.org_unit ou
348 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
349 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
354 FROM actor.org_unit ou
355 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
356 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
357 ) SELECT cpl.id INTO attr_set.location
358 FROM anscestor_depth a
359 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
360 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
362 ORDER BY a.depth DESC
366 attr_set.import_error := 'import.item.invalid.location';
367 attr_set.error_detail := tmp_attr_set.cs;
368 RETURN NEXT attr_set; CONTINUE;
372 attr_set.circulate :=
373 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
374 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
377 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
378 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
381 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
382 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
384 attr_set.opac_visible :=
385 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
386 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
389 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
390 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
392 attr_set.call_number := tmp_attr_set.cn; -- TEXT
393 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
394 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
395 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
396 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
397 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
398 attr_set.internal_id := tmp_attr_set.internal_id::BIGINT;
399 attr_set.stat_cat_data := tmp_attr_set.stat_cat_data; -- TEXT,
400 attr_set.parts_data := tmp_attr_set.parts_data; -- TEXT,
402 RETURN NEXT attr_set;