1 -- Evergreen DB patch 0596.schema.vandelay-item-import-error-detail.sql
4 -- check whether patch can be applied
5 SELECT evergreen.upgrade_deps_block_check('0596', :eg_version);
7 INSERT INTO vandelay.import_error ( code, description ) VALUES (
8 'import.item.invalid.status', oils_i18n_gettext('import.item.invalid.status', 'Invalid value for "status"', 'vie', 'description') );
9 INSERT INTO vandelay.import_error ( code, description ) VALUES (
10 'import.item.invalid.price', oils_i18n_gettext('import.item.invalid.price', 'Invalid value for "price"', 'vie', 'description') );
11 INSERT INTO vandelay.import_error ( code, description ) VALUES (
12 'import.item.invalid.deposit_amount', oils_i18n_gettext('import.item.invalid.deposit_amount', 'Invalid value for "deposit_amount"', 'vie', 'description') );
13 INSERT INTO vandelay.import_error ( code, description ) VALUES (
14 'import.item.invalid.owning_lib', oils_i18n_gettext('import.item.invalid.owning_lib', 'Invalid value for "owning_lib"', 'vie', 'description') );
15 INSERT INTO vandelay.import_error ( code, description ) VALUES (
16 'import.item.invalid.circ_lib', oils_i18n_gettext('import.item.invalid.circ_lib', 'Invalid value for "circ_lib"', 'vie', 'description') );
17 INSERT INTO vandelay.import_error ( code, description ) VALUES (
18 'import.item.invalid.copy_number', oils_i18n_gettext('import.item.invalid.copy_number', 'Invalid value for "copy_number"', 'vie', 'description') );
19 INSERT INTO vandelay.import_error ( code, description ) VALUES (
20 'import.item.invalid.circ_as_type', oils_i18n_gettext('import.item.invalid.circ_as_type', 'Invalid value for "circ_as_type"', 'vie', 'description') );
22 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
47 attr_set vandelay.import_item%ROWTYPE;
54 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
58 attr_set.definition := attr_def.id;
60 -- Build the combined XPath
64 WHEN attr_def.owning_lib IS NULL THEN 'null()'
65 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
66 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
71 WHEN attr_def.circ_lib IS NULL THEN 'null()'
72 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
73 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
78 WHEN attr_def.call_number IS NULL THEN 'null()'
79 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
80 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
85 WHEN attr_def.copy_number IS NULL THEN 'null()'
86 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
87 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
92 WHEN attr_def.status IS NULL THEN 'null()'
93 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
94 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
99 WHEN attr_def.location IS NULL THEN 'null()'
100 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
101 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
106 WHEN attr_def.circulate IS NULL THEN 'null()'
107 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
108 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
113 WHEN attr_def.deposit IS NULL THEN 'null()'
114 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
115 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
120 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
121 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
122 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
127 WHEN attr_def.ref IS NULL THEN 'null()'
128 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
129 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
134 WHEN attr_def.holdable IS NULL THEN 'null()'
135 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
136 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
141 WHEN attr_def.price IS NULL THEN 'null()'
142 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
143 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
148 WHEN attr_def.barcode IS NULL THEN 'null()'
149 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
150 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
155 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
156 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
157 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
162 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
163 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
164 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
169 WHEN attr_def.alert_message IS NULL THEN 'null()'
170 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
171 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
176 WHEN attr_def.opac_visible IS NULL THEN 'null()'
177 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
178 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
183 WHEN attr_def.pub_note IS NULL THEN 'null()'
184 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
185 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
189 WHEN attr_def.priv_note IS NULL THEN 'null()'
190 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
191 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
198 call_number || '|' ||
199 copy_number || '|' ||
204 deposit_amount || '|' ||
209 circ_modifier || '|' ||
210 circ_as_type || '|' ||
211 alert_message || '|' ||
218 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
219 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
220 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
221 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
224 attr_set.import_error := NULL;
225 attr_set.error_detail := NULL;
226 attr_set.deposit_amount := NULL;
227 attr_set.copy_number := NULL;
228 attr_set.price := 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 tmp_attr_set.circ_mod != '' THEN
288 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
290 attr_set.import_error := 'import.item.invalid.circ_modifier';
291 attr_set.error_detail := tmp_attr_set.circ_mod;
292 RETURN NEXT attr_set; CONTINUE;
296 IF tmp_attr_set.circ_as != '' THEN
297 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
299 attr_set.import_error := 'import.item.invalid.circ_as_type';
300 attr_set.error_detail := tmp_attr_set.circ_as;
301 RETURN NEXT attr_set; CONTINUE;
305 IF tmp_attr_set.cl != '' THEN
307 -- search up the org unit tree for a matching copy location
308 WITH RECURSIVE anscestor_depth AS (
312 FROM actor.org_unit ou
313 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
314 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
319 FROM actor.org_unit ou
320 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
321 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
322 ) SELECT cpl.id INTO attr_set.location
323 FROM anscestor_depth a
324 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
325 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
326 ORDER BY a.depth DESC
330 attr_set.import_error := 'import.item.invalid.location';
331 attr_set.error_detail := tmp_attr_set.cs;
332 RETURN NEXT attr_set; CONTINUE;
336 attr_set.circulate :=
337 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
338 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
341 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
342 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
345 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
346 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
348 attr_set.opac_visible :=
349 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
350 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
353 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
354 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
356 attr_set.call_number := tmp_attr_set.cn; -- TEXT
357 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
358 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
359 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
360 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
361 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
363 RETURN NEXT attr_set;
374 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
377 item_data vandelay.import_item%ROWTYPE;
380 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
384 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
386 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
387 INSERT INTO vandelay.import_item (
413 item_data.definition,
414 item_data.owning_lib,
416 item_data.call_number,
417 item_data.copy_number,
422 item_data.deposit_amount,
427 item_data.circ_modifier,
428 item_data.circ_as_type,
429 item_data.alert_message,
432 item_data.opac_visible,
433 item_data.import_error,
434 item_data.error_detail
440 $func$ LANGUAGE PLPGSQL;