1 --Upgrade Script for 2.3.4 to 2.3.5
2 \set eg_version '''2.3.5'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.3.5', :eg_version);
5 -- Evergreen DB patch XXXX.function.merge_record_assets_deleted_call_numbers.sql
8 -- check whether patch can be applied
9 SELECT evergreen.upgrade_deps_block_check('0761', :eg_version);
11 CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
13 moved_objects INT := 0;
14 source_cn asset.call_number%ROWTYPE;
15 target_cn asset.call_number%ROWTYPE;
16 metarec metabib.metarecord%ROWTYPE;
17 hold action.hold_request%ROWTYPE;
18 ser_rec serial.record_entry%ROWTYPE;
19 ser_sub serial.subscription%ROWTYPE;
20 acq_lineitem acq.lineitem%ROWTYPE;
21 acq_request acq.user_request%ROWTYPE;
22 booking booking.resource_type%ROWTYPE;
23 source_part biblio.monograph_part%ROWTYPE;
24 target_part biblio.monograph_part%ROWTYPE;
25 multi_home biblio.peer_bib_copy_map%ROWTYPE;
32 -- move any 856 entries on records that have at least one MARC-mapped URI entry
33 SELECT INTO uri_count COUNT(*)
34 FROM asset.uri_call_number_map m
35 JOIN asset.call_number cn ON (m.call_number = cn.id)
36 WHERE cn.record = source_record;
40 -- This returns more nodes than you might expect:
41 -- 7 instead of 1 for an 856 with $u $y $9
42 SELECT COUNT(*) INTO counter
43 FROM oils_xpath_table(
46 'biblio.record_entry',
48 'id=' || source_record
51 FOR i IN 1 .. counter LOOP
52 SELECT '<datafield xmlns="http://www.loc.gov/MARC21/slim"' ||
54 ' ind1="' || FIRST(ind1) || '"' ||
55 ' ind2="' || FIRST(ind2) || '">' ||
58 '<subfield code="' || subfield || '">' ||
61 regexp_replace(data,'&','&','g'),
67 ) || '</datafield>' INTO uri_datafield
68 FROM oils_xpath_table(
71 'biblio.record_entry',
72 '//*[@tag="856"][position()=' || i || ']/@ind1|' ||
73 '//*[@tag="856"][position()=' || i || ']/@ind2|' ||
74 '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
75 '//*[@tag="856"][position()=' || i || ']/*[@code]',
76 'id=' || source_record
77 ) as t(id int,ind1 text, ind2 text,subfield text,data text);
79 -- As most of the results will be NULL, protect against NULLifying
80 -- the valid content that we do generate
81 uri_text := uri_text || COALESCE(uri_datafield, '');
84 IF uri_text <> '' THEN
85 UPDATE biblio.record_entry
86 SET marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
87 WHERE id = target_record;
92 -- Find and move metarecords to the target record
94 FROM metabib.metarecord
95 WHERE master_record = source_record;
98 UPDATE metabib.metarecord
99 SET master_record = target_record,
101 WHERE id = metarec.id;
103 moved_objects := moved_objects + 1;
106 -- Find call numbers attached to the source ...
107 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
109 SELECT INTO target_cn *
110 FROM asset.call_number
111 WHERE label = source_cn.label
112 AND owning_lib = source_cn.owning_lib
113 AND record = target_record
116 -- ... and if there's a conflicting one on the target ...
119 -- ... move the copies to that, and ...
121 SET call_number = target_cn.id
122 WHERE call_number = source_cn.id;
124 -- ... move V holds to the move-target call number
125 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
127 UPDATE action.hold_request
128 SET target = target_cn.id
131 moved_objects := moved_objects + 1;
136 -- ... just move the call number to the target record
137 UPDATE asset.call_number
138 SET record = target_record
139 WHERE id = source_cn.id;
142 moved_objects := moved_objects + 1;
145 -- Find T holds targeting the source record ...
146 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP
148 -- ... and move them to the target record
149 UPDATE action.hold_request
150 SET target = target_record
153 moved_objects := moved_objects + 1;
156 -- Find serial records targeting the source record ...
157 FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
158 -- ... and move them to the target record
159 UPDATE serial.record_entry
160 SET record = target_record
161 WHERE id = ser_rec.id;
163 moved_objects := moved_objects + 1;
166 -- Find serial subscriptions targeting the source record ...
167 FOR ser_sub IN SELECT * FROM serial.subscription WHERE record_entry = source_record LOOP
168 -- ... and move them to the target record
169 UPDATE serial.subscription
170 SET record_entry = target_record
171 WHERE id = ser_sub.id;
173 moved_objects := moved_objects + 1;
176 -- Find booking resource types targeting the source record ...
177 FOR booking IN SELECT * FROM booking.resource_type WHERE record = source_record LOOP
178 -- ... and move them to the target record
179 UPDATE booking.resource_type
180 SET record = target_record
181 WHERE id = booking.id;
183 moved_objects := moved_objects + 1;
186 -- Find acq lineitems targeting the source record ...
187 FOR acq_lineitem IN SELECT * FROM acq.lineitem WHERE eg_bib_id = source_record LOOP
188 -- ... and move them to the target record
190 SET eg_bib_id = target_record
191 WHERE id = acq_lineitem.id;
193 moved_objects := moved_objects + 1;
196 -- Find acq user purchase requests targeting the source record ...
197 FOR acq_request IN SELECT * FROM acq.user_request WHERE eg_bib = source_record LOOP
198 -- ... and move them to the target record
199 UPDATE acq.user_request
200 SET eg_bib = target_record
201 WHERE id = acq_request.id;
203 moved_objects := moved_objects + 1;
206 -- Find parts attached to the source ...
207 FOR source_part IN SELECT * FROM biblio.monograph_part WHERE record = source_record LOOP
209 SELECT INTO target_part *
210 FROM biblio.monograph_part
211 WHERE label = source_part.label
212 AND record = target_record;
214 -- ... and if there's a conflicting one on the target ...
217 -- ... move the copy-part maps to that, and ...
218 UPDATE asset.copy_part_map
219 SET part = target_part.id
220 WHERE part = source_part.id;
222 -- ... move P holds to the move-target part
223 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_part.id AND hold_type = 'P' LOOP
225 UPDATE action.hold_request
226 SET target = target_part.id
229 moved_objects := moved_objects + 1;
234 -- ... just move the part to the target record
235 UPDATE biblio.monograph_part
236 SET record = target_record
237 WHERE id = source_part.id;
240 moved_objects := moved_objects + 1;
243 -- Find multi_home items attached to the source ...
244 FOR multi_home IN SELECT * FROM biblio.peer_bib_copy_map WHERE peer_record = source_record LOOP
245 -- ... and move them to the target record
246 UPDATE biblio.peer_bib_copy_map
247 SET peer_record = target_record
248 WHERE id = multi_home.id;
250 moved_objects := moved_objects + 1;
253 -- And delete mappings where the item's home bib was merged with the peer bib
254 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = (
255 SELECT (SELECT record FROM asset.call_number WHERE id = call_number)
256 FROM asset.copy WHERE id = target_copy
259 -- Finally, "delete" the source record
260 DELETE FROM biblio.record_entry WHERE id = source_record;
262 -- That's all, folks!
263 RETURN moved_objects;
265 $func$ LANGUAGE plpgsql;
268 SELECT evergreen.upgrade_deps_block_check('0764', :eg_version);
270 UPDATE config.z3950_source
271 SET host = 'lx2.loc.gov', port = 210, db = 'LCDB'
273 AND host = 'z3950.loc.gov'
277 UPDATE config.z3950_attr
284 -- Evergreen DB patch XXXX.handle_null_svf_during_import.sql
286 -- Prevent applying a normalization function to a null SVF
287 -- attribute value from breaking record import.
291 -- check whether patch can be applied
292 SELECT evergreen.upgrade_deps_block_check('0766', :eg_version);
294 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$
296 transformed_xml TEXT;
299 xfrm config.xml_transform%ROWTYPE;
301 new_attrs HSTORE := ''::HSTORE;
302 attr_def config.record_attr_definition%ROWTYPE;
305 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP
307 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
308 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(x.value), COALESCE(attr_def.joiner,' ')) INTO attr_value
309 FROM vandelay.flatten_marc(xml) AS x
310 WHERE x.tag LIKE attr_def.tag
312 WHEN attr_def.sf_list IS NOT NULL
313 THEN POSITION(x.subfield IN attr_def.sf_list) > 0
320 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
321 attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field);
323 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
325 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
327 -- See if we can skip the XSLT ... it's expensive
328 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
329 -- Can't skip the transform
330 IF xfrm.xslt <> '---' THEN
331 transformed_xml := oils_xslt_process(xml,xfrm.xslt);
333 transformed_xml := xml;
336 prev_xfrm := xfrm.name;
339 IF xfrm.name IS NULL THEN
340 -- just grab the marcxml (empty) transform
341 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
342 prev_xfrm := xfrm.name;
345 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
347 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
348 SELECT m.value::TEXT INTO attr_value
349 FROM vandelay.marc21_physical_characteristics(xml) v
350 JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
351 WHERE v.subfield = attr_def.phys_char_sf
352 LIMIT 1; -- Just in case ...
356 -- apply index normalizers to attr_value
358 SELECT n.func AS func,
359 n.param_count AS param_count,
361 FROM config.index_normalizer n
362 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
363 WHERE attr = attr_def.name
365 EXECUTE 'SELECT ' || normalizer.func || '(' ||
366 quote_nullable( attr_value ) ||
368 WHEN normalizer.param_count > 0
369 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
376 -- Add the new value to the hstore
377 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
383 $_$ LANGUAGE PLPGSQL;
387 SELECT evergreen.upgrade_deps_block_check('0767', :eg_version);
389 CREATE OR REPLACE FUNCTION evergreen.could_be_serial_holding_code(TEXT) RETURNS BOOL AS $$
394 my $holding_code = (new JSON::XS)->decode(shift);
395 new MARC::Field('999', @$holding_code);
398 # verify that subfield labels are exactly one character long
399 foreach (keys %{ { @$holding_code } }) {
400 return 0 if length($_) != 1;
405 COMMENT ON FUNCTION evergreen.could_be_serial_holding_code(TEXT) IS
406 'Return true if parameter is valid JSON representing an array that at minimu
407 m doesn''t make MARC::Field balk and only has subfield labels exactly one character long. Otherwise false.';
410 -- This UPDATE throws away data, but only bad data that makes things break
412 UPDATE serial.issuance
413 SET holding_code = NULL
414 WHERE NOT could_be_serial_holding_code(holding_code);
416 ALTER TABLE serial.issuance
417 DROP CONSTRAINT IF EXISTS issuance_holding_code_check;
419 ALTER TABLE serial.issuance
420 ADD CHECK (holding_code IS NULL OR could_be_serial_holding_code(holding_code));
423 SELECT evergreen.upgrade_deps_block_check('0770', :eg_version);
425 CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$
430 asset_barcodes TEXT[];
431 actor_barcodes TEXT[];
432 do_asset BOOL = false;
433 do_serial BOOL = false;
434 do_booking BOOL = false;
435 do_actor BOOL = false;
436 completion_set config.barcode_completion%ROWTYPE;
439 IF position('asset' in type) > 0 THEN
442 IF position('serial' in type) > 0 THEN
445 IF position('booking' in type) > 0 THEN
448 IF do_asset OR do_serial OR do_booking THEN
449 asset_barcodes = asset_barcodes || in_barcode;
451 IF position('actor' in type) > 0 THEN
453 actor_barcodes = actor_barcodes || in_barcode;
456 barcode_len := length(in_barcode);
458 FOR completion_set IN
459 SELECT * FROM config.barcode_completion
461 AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou)
463 IF completion_set.prefix IS NULL THEN
464 completion_set.prefix := '';
466 IF completion_set.suffix IS NULL THEN
467 completion_set.suffix := '';
469 IF completion_set.length = 0 OR completion_set.padding IS NULL OR length(completion_set.padding) = 0 THEN
470 cur_barcode = completion_set.prefix || in_barcode || completion_set.suffix;
472 completion_len = completion_set.length - length(completion_set.prefix) - length(completion_set.suffix);
473 IF completion_len >= barcode_len THEN
474 IF completion_set.padding_end THEN
475 cur_barcode = rpad(in_barcode, completion_len, completion_set.padding);
477 cur_barcode = lpad(in_barcode, completion_len, completion_set.padding);
479 cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix;
482 IF completion_set.actor THEN
483 actor_barcodes = actor_barcodes || cur_barcode;
485 IF completion_set.asset THEN
486 asset_barcodes = asset_barcodes || cur_barcode;
490 IF do_asset AND do_serial THEN
491 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM ONLY asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
492 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
494 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
496 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
499 RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes);
502 RETURN QUERY SELECT 'actor'::TEXT, c.usr::BIGINT, c.barcode FROM actor.card c JOIN actor.usr u ON c.usr = u.id WHERE
503 ((c.barcode = ANY(actor_barcodes) AND c.active) OR c.barcode = in_barcode) AND NOT u.deleted ORDER BY usr;
509 -- Evergreen DB patch 0783.schema.enforce_use_id_for_tcn.sql
511 -- Sets the TCN value in the biblio.record_entry row to bib ID,
512 -- if the appropriate setting is in place
515 -- check whether patch can be applied
516 SELECT evergreen.upgrade_deps_block_check('0783', :eg_version);
518 -- FIXME: add/check SQL statements to perform the upgrade
519 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
522 use MARC::File::XML (BinaryEncoding => 'UTF-8');
525 use Unicode::Normalize;
527 MARC::Charset->assume_unicode(1);
529 my $schema = $_TD->{table_schema};
530 my $marc = MARC::Record->new_from_xml($_TD->{new}{marc});
532 my @old901s = $marc->field('901');
533 $marc->delete_fields(@old901s);
535 if ($schema eq 'biblio') {
536 my $tcn_value = $_TD->{new}{tcn_value};
538 # Set TCN value to record ID?
539 my $id_as_tcn = spi_exec_query("
541 FROM config.global_flag
542 WHERE name = 'cat.bib.use_id_for_tcn'
544 if (($id_as_tcn->{processed}) && $id_as_tcn->{rows}[0]->{enabled} eq 't') {
545 $tcn_value = $_TD->{new}{id};
546 $_TD->{new}{tcn_value} = $tcn_value;
549 my $new_901 = MARC::Field->new("901", " ", " ",
551 "b" => $_TD->{new}{tcn_source},
552 "c" => $_TD->{new}{id},
556 if ($_TD->{new}{owner}) {
557 $new_901->add_subfields("o" => $_TD->{new}{owner});
560 if ($_TD->{new}{share_depth}) {
561 $new_901->add_subfields("d" => $_TD->{new}{share_depth});
564 $marc->append_fields($new_901);
565 } elsif ($schema eq 'authority') {
566 my $new_901 = MARC::Field->new("901", " ", " ",
567 "c" => $_TD->{new}{id},
570 $marc->append_fields($new_901);
571 } elsif ($schema eq 'serial') {
572 my $new_901 = MARC::Field->new("901", " ", " ",
573 "c" => $_TD->{new}{id},
575 "o" => $_TD->{new}{owning_lib},
578 if ($_TD->{new}{record}) {
579 $new_901->add_subfields("r" => $_TD->{new}{record});
582 $marc->append_fields($new_901);
584 my $new_901 = MARC::Field->new("901", " ", " ",
585 "c" => $_TD->{new}{id},
588 $marc->append_fields($new_901);
591 my $xml = $marc->as_xml_record();
593 $xml =~ s/^<\?xml.+\?\s*>//go;
594 $xml =~ s/>\s+</></go;
595 $xml =~ s/\p{Cc}//go;
597 # Embed a version of OpenILS::Application::AppUtils->entityize()
598 # to avoid having to set PERL5LIB for PostgreSQL as well
600 # If we are going to convert non-ASCII characters to XML entities,
601 # we had better be dealing with a UTF8 string to begin with
602 $xml = decode_utf8($xml);
606 # Convert raw ampersands to entities
607 $xml =~ s/&(?!\S+;)/&/gso;
609 # Convert Unicode characters to entities
610 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
612 $xml =~ s/[\x00-\x1f]//go;
613 $_TD->{new}{marc} = $xml;
616 $func$ LANGUAGE PLPERLU;