1 --Upgrade Script for 2.2.6 to 2.2.7
2 \set eg_version '''2.2.7'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.2.7', :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
285 SELECT evergreen.upgrade_deps_block_check('0770', :eg_version);
287 CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$
292 asset_barcodes TEXT[];
293 actor_barcodes TEXT[];
294 do_asset BOOL = false;
295 do_serial BOOL = false;
296 do_booking BOOL = false;
297 do_actor BOOL = false;
298 completion_set config.barcode_completion%ROWTYPE;
301 IF position('asset' in type) > 0 THEN
304 IF position('serial' in type) > 0 THEN
307 IF position('booking' in type) > 0 THEN
310 IF do_asset OR do_serial OR do_booking THEN
311 asset_barcodes = asset_barcodes || in_barcode;
313 IF position('actor' in type) > 0 THEN
315 actor_barcodes = actor_barcodes || in_barcode;
318 barcode_len := length(in_barcode);
320 FOR completion_set IN
321 SELECT * FROM config.barcode_completion
323 AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou)
325 IF completion_set.prefix IS NULL THEN
326 completion_set.prefix := '';
328 IF completion_set.suffix IS NULL THEN
329 completion_set.suffix := '';
331 IF completion_set.length = 0 OR completion_set.padding IS NULL OR length(completion_set.padding) = 0 THEN
332 cur_barcode = completion_set.prefix || in_barcode || completion_set.suffix;
334 completion_len = completion_set.length - length(completion_set.prefix) - length(completion_set.suffix);
335 IF completion_len >= barcode_len THEN
336 IF completion_set.padding_end THEN
337 cur_barcode = rpad(in_barcode, completion_len, completion_set.padding);
339 cur_barcode = lpad(in_barcode, completion_len, completion_set.padding);
341 cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix;
344 IF completion_set.actor THEN
345 actor_barcodes = actor_barcodes || cur_barcode;
347 IF completion_set.asset THEN
348 asset_barcodes = asset_barcodes || cur_barcode;
352 IF do_asset AND do_serial THEN
353 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM ONLY asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
354 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
356 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
358 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
361 RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes);
364 RETURN QUERY SELECT 'actor'::TEXT, c.usr::BIGINT, c.barcode FROM actor.card c JOIN actor.usr u ON c.usr = u.id WHERE
365 ((c.barcode = ANY(actor_barcodes) AND c.active) OR c.barcode = in_barcode) AND NOT u.deleted ORDER BY usr;
371 -- Evergreen DB patch 0783.schema.enforce_use_id_for_tcn.sql
373 -- Sets the TCN value in the biblio.record_entry row to bib ID,
374 -- if the appropriate setting is in place
377 -- check whether patch can be applied
378 SELECT evergreen.upgrade_deps_block_check('0783', :eg_version);
380 -- FIXME: add/check SQL statements to perform the upgrade
381 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
384 use MARC::File::XML (BinaryEncoding => 'UTF-8');
387 use Unicode::Normalize;
389 MARC::Charset->assume_unicode(1);
391 my $schema = $_TD->{table_schema};
392 my $marc = MARC::Record->new_from_xml($_TD->{new}{marc});
394 my @old901s = $marc->field('901');
395 $marc->delete_fields(@old901s);
397 if ($schema eq 'biblio') {
398 my $tcn_value = $_TD->{new}{tcn_value};
400 # Set TCN value to record ID?
401 my $id_as_tcn = spi_exec_query("
403 FROM config.global_flag
404 WHERE name = 'cat.bib.use_id_for_tcn'
406 if (($id_as_tcn->{processed}) && $id_as_tcn->{rows}[0]->{enabled} eq 't') {
407 $tcn_value = $_TD->{new}{id};
408 $_TD->{new}{tcn_value} = $tcn_value;
411 my $new_901 = MARC::Field->new("901", " ", " ",
413 "b" => $_TD->{new}{tcn_source},
414 "c" => $_TD->{new}{id},
418 if ($_TD->{new}{owner}) {
419 $new_901->add_subfields("o" => $_TD->{new}{owner});
422 if ($_TD->{new}{share_depth}) {
423 $new_901->add_subfields("d" => $_TD->{new}{share_depth});
426 $marc->append_fields($new_901);
427 } elsif ($schema eq 'authority') {
428 my $new_901 = MARC::Field->new("901", " ", " ",
429 "c" => $_TD->{new}{id},
432 $marc->append_fields($new_901);
433 } elsif ($schema eq 'serial') {
434 my $new_901 = MARC::Field->new("901", " ", " ",
435 "c" => $_TD->{new}{id},
437 "o" => $_TD->{new}{owning_lib},
440 if ($_TD->{new}{record}) {
441 $new_901->add_subfields("r" => $_TD->{new}{record});
444 $marc->append_fields($new_901);
446 my $new_901 = MARC::Field->new("901", " ", " ",
447 "c" => $_TD->{new}{id},
450 $marc->append_fields($new_901);
453 my $xml = $marc->as_xml_record();
455 $xml =~ s/^<\?xml.+\?\s*>//go;
456 $xml =~ s/>\s+</></go;
457 $xml =~ s/\p{Cc}//go;
459 # Embed a version of OpenILS::Application::AppUtils->entityize()
460 # to avoid having to set PERL5LIB for PostgreSQL as well
462 # If we are going to convert non-ASCII characters to XML entities,
463 # we had better be dealing with a UTF8 string to begin with
464 $xml = decode_utf8($xml);
468 # Convert raw ampersands to entities
469 $xml =~ s/&(?!\S+;)/&/gso;
471 # Convert Unicode characters to entities
472 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
474 $xml =~ s/[\x00-\x1f]//go;
475 $_TD->{new}{marc} = $xml;
478 $func$ LANGUAGE PLPERLU;