From 419c32f59341b386dc8a9f1c115d2663f0a73c4d Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Wed, 20 Mar 2013 10:21:22 -0400 Subject: [PATCH] 2.3.4 -> 2.3.5 DB upgrade script Signed-off-by: Bill Erickson Signed-off-by: Lebbeous Fogle-Weekley --- .../Pg/version-upgrade/2.3.4-2.3.5-upgrade-db.sql | 619 +++++++++++++++++++++ 1 file changed, 619 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/version-upgrade/2.3.4-2.3.5-upgrade-db.sql diff --git a/Open-ILS/src/sql/Pg/version-upgrade/2.3.4-2.3.5-upgrade-db.sql b/Open-ILS/src/sql/Pg/version-upgrade/2.3.4-2.3.5-upgrade-db.sql new file mode 100644 index 0000000000..1f3626825b --- /dev/null +++ b/Open-ILS/src/sql/Pg/version-upgrade/2.3.4-2.3.5-upgrade-db.sql @@ -0,0 +1,619 @@ +--Upgrade Script for 2.3.4 to 2.3.5 +\set eg_version '''2.3.5''' +BEGIN; +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.3.5', :eg_version); +-- Evergreen DB patch XXXX.function.merge_record_assets_deleted_call_numbers.sql +-- + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0761', :eg_version); + +CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$ +DECLARE + moved_objects INT := 0; + source_cn asset.call_number%ROWTYPE; + target_cn asset.call_number%ROWTYPE; + metarec metabib.metarecord%ROWTYPE; + hold action.hold_request%ROWTYPE; + ser_rec serial.record_entry%ROWTYPE; + ser_sub serial.subscription%ROWTYPE; + acq_lineitem acq.lineitem%ROWTYPE; + acq_request acq.user_request%ROWTYPE; + booking booking.resource_type%ROWTYPE; + source_part biblio.monograph_part%ROWTYPE; + target_part biblio.monograph_part%ROWTYPE; + multi_home biblio.peer_bib_copy_map%ROWTYPE; + uri_count INT := 0; + counter INT := 0; + uri_datafield TEXT; + uri_text TEXT := ''; +BEGIN + + -- move any 856 entries on records that have at least one MARC-mapped URI entry + SELECT INTO uri_count COUNT(*) + FROM asset.uri_call_number_map m + JOIN asset.call_number cn ON (m.call_number = cn.id) + WHERE cn.record = source_record; + + IF uri_count > 0 THEN + + -- This returns more nodes than you might expect: + -- 7 instead of 1 for an 856 with $u $y $9 + SELECT COUNT(*) INTO counter + FROM oils_xpath_table( + 'id', + 'marc', + 'biblio.record_entry', + '//*[@tag="856"]', + 'id=' || source_record + ) as t(i int,c text); + + FOR i IN 1 .. counter LOOP + SELECT '' || + array_to_string( + array_accum( + '' || + regexp_replace( + regexp_replace( + regexp_replace(data,'&','&','g'), + '>', '>', 'g' + ), + '<', '<', 'g' + ) || '' + ), '' + ) || '' INTO uri_datafield + FROM oils_xpath_table( + 'id', + 'marc', + 'biblio.record_entry', + '//*[@tag="856"][position()=' || i || ']/@ind1|' || + '//*[@tag="856"][position()=' || i || ']/@ind2|' || + '//*[@tag="856"][position()=' || i || ']/*/@code|' || + '//*[@tag="856"][position()=' || i || ']/*[@code]', + 'id=' || source_record + ) as t(id int,ind1 text, ind2 text,subfield text,data text); + + -- As most of the results will be NULL, protect against NULLifying + -- the valid content that we do generate + uri_text := uri_text || COALESCE(uri_datafield, ''); + END LOOP; + + IF uri_text <> '' THEN + UPDATE biblio.record_entry + SET marc = regexp_replace(marc,'(]*record>)', uri_text || E'\\1') + WHERE id = target_record; + END IF; + + END IF; + + -- Find and move metarecords to the target record + SELECT INTO metarec * + FROM metabib.metarecord + WHERE master_record = source_record; + + IF FOUND THEN + UPDATE metabib.metarecord + SET master_record = target_record, + mods = NULL + WHERE id = metarec.id; + + moved_objects := moved_objects + 1; + END IF; + + -- Find call numbers attached to the source ... + FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP + + SELECT INTO target_cn * + FROM asset.call_number + WHERE label = source_cn.label + AND owning_lib = source_cn.owning_lib + AND record = target_record + AND NOT deleted; + + -- ... and if there's a conflicting one on the target ... + IF FOUND THEN + + -- ... move the copies to that, and ... + UPDATE asset.copy + SET call_number = target_cn.id + WHERE call_number = source_cn.id; + + -- ... move V holds to the move-target call number + FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP + + UPDATE action.hold_request + SET target = target_cn.id + WHERE id = hold.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- ... if not ... + ELSE + -- ... just move the call number to the target record + UPDATE asset.call_number + SET record = target_record + WHERE id = source_cn.id; + END IF; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find T holds targeting the source record ... + FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP + + -- ... and move them to the target record + UPDATE action.hold_request + SET target = target_record + WHERE id = hold.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find serial records targeting the source record ... + FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP + -- ... and move them to the target record + UPDATE serial.record_entry + SET record = target_record + WHERE id = ser_rec.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find serial subscriptions targeting the source record ... + FOR ser_sub IN SELECT * FROM serial.subscription WHERE record_entry = source_record LOOP + -- ... and move them to the target record + UPDATE serial.subscription + SET record_entry = target_record + WHERE id = ser_sub.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find booking resource types targeting the source record ... + FOR booking IN SELECT * FROM booking.resource_type WHERE record = source_record LOOP + -- ... and move them to the target record + UPDATE booking.resource_type + SET record = target_record + WHERE id = booking.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find acq lineitems targeting the source record ... + FOR acq_lineitem IN SELECT * FROM acq.lineitem WHERE eg_bib_id = source_record LOOP + -- ... and move them to the target record + UPDATE acq.lineitem + SET eg_bib_id = target_record + WHERE id = acq_lineitem.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find acq user purchase requests targeting the source record ... + FOR acq_request IN SELECT * FROM acq.user_request WHERE eg_bib = source_record LOOP + -- ... and move them to the target record + UPDATE acq.user_request + SET eg_bib = target_record + WHERE id = acq_request.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find parts attached to the source ... + FOR source_part IN SELECT * FROM biblio.monograph_part WHERE record = source_record LOOP + + SELECT INTO target_part * + FROM biblio.monograph_part + WHERE label = source_part.label + AND record = target_record; + + -- ... and if there's a conflicting one on the target ... + IF FOUND THEN + + -- ... move the copy-part maps to that, and ... + UPDATE asset.copy_part_map + SET part = target_part.id + WHERE part = source_part.id; + + -- ... move P holds to the move-target part + FOR hold IN SELECT * FROM action.hold_request WHERE target = source_part.id AND hold_type = 'P' LOOP + + UPDATE action.hold_request + SET target = target_part.id + WHERE id = hold.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- ... if not ... + ELSE + -- ... just move the part to the target record + UPDATE biblio.monograph_part + SET record = target_record + WHERE id = source_part.id; + END IF; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find multi_home items attached to the source ... + FOR multi_home IN SELECT * FROM biblio.peer_bib_copy_map WHERE peer_record = source_record LOOP + -- ... and move them to the target record + UPDATE biblio.peer_bib_copy_map + SET peer_record = target_record + WHERE id = multi_home.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- And delete mappings where the item's home bib was merged with the peer bib + DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = ( + SELECT (SELECT record FROM asset.call_number WHERE id = call_number) + FROM asset.copy WHERE id = target_copy + ); + + -- Finally, "delete" the source record + DELETE FROM biblio.record_entry WHERE id = source_record; + + -- That's all, folks! + RETURN moved_objects; +END; +$func$ LANGUAGE plpgsql; + + +SELECT evergreen.upgrade_deps_block_check('0764', :eg_version); + +UPDATE config.z3950_source + SET host = 'lx2.loc.gov', port = 210, db = 'LCDB' + WHERE name = 'loc' + AND host = 'z3950.loc.gov' + AND port = 7090 + AND db = 'Voyager'; + +UPDATE config.z3950_attr + SET format = 6 + WHERE source = 'loc' + AND name = 'lccn' + AND format = 1; + + +-- Evergreen DB patch XXXX.handle_null_svf_during_import.sql +-- +-- Prevent applying a normalization function to a null SVF +-- attribute value from breaking record import. +-- + + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0766', :eg_version); + +CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$ +DECLARE + transformed_xml TEXT; + prev_xfrm TEXT; + normalizer RECORD; + xfrm config.xml_transform%ROWTYPE; + attr_value TEXT; + new_attrs HSTORE := ''::HSTORE; + attr_def config.record_attr_definition%ROWTYPE; +BEGIN + + FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP + + IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection + SELECT ARRAY_TO_STRING(ARRAY_ACCUM(x.value), COALESCE(attr_def.joiner,' ')) INTO attr_value + FROM vandelay.flatten_marc(xml) AS x + WHERE x.tag LIKE attr_def.tag + AND CASE + WHEN attr_def.sf_list IS NOT NULL + THEN POSITION(x.subfield IN attr_def.sf_list) > 0 + ELSE TRUE + END + GROUP BY x.tag + ORDER BY x.tag + LIMIT 1; + + ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field + attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field); + + ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression + + SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format; + + -- See if we can skip the XSLT ... it's expensive + IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN + -- Can't skip the transform + IF xfrm.xslt <> '---' THEN + transformed_xml := oils_xslt_process(xml,xfrm.xslt); + ELSE + transformed_xml := xml; + END IF; + + prev_xfrm := xfrm.name; + END IF; + + IF xfrm.name IS NULL THEN + -- just grab the marcxml (empty) transform + SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1; + prev_xfrm := xfrm.name; + END IF; + + attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]); + + ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map + SELECT m.value::TEXT INTO attr_value + FROM vandelay.marc21_physical_characteristics(xml) v + JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value) + WHERE v.subfield = attr_def.phys_char_sf + LIMIT 1; -- Just in case ... + + END IF; + + -- apply index normalizers to attr_value + FOR normalizer IN + SELECT n.func AS func, + n.param_count AS param_count, + m.params AS params + FROM config.index_normalizer n + JOIN config.record_attr_index_norm_map m ON (m.norm = n.id) + WHERE attr = attr_def.name + ORDER BY m.pos LOOP + EXECUTE 'SELECT ' || normalizer.func || '(' || + quote_nullable( attr_value ) || + CASE + WHEN normalizer.param_count > 0 + THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'') + ELSE '' + END || + ')' INTO attr_value; + + END LOOP; + + -- Add the new value to the hstore + new_attrs := new_attrs || hstore( attr_def.name, attr_value ); + + END LOOP; + + RETURN new_attrs; +END; +$_$ LANGUAGE PLPGSQL; + + + +SELECT evergreen.upgrade_deps_block_check('0767', :eg_version); + +CREATE OR REPLACE FUNCTION evergreen.could_be_serial_holding_code(TEXT) RETURNS BOOL AS $$ + use JSON::XS; + use MARC::Field; + + eval { + my $holding_code = (new JSON::XS)->decode(shift); + new MARC::Field('999', @$holding_code); + }; + return 0 if $@; + # verify that subfield labels are exactly one character long + foreach (keys %{ { @$holding_code } }) { + return 0 if length($_) != 1; + } + return 1; +$$ LANGUAGE PLPERLU; + +COMMENT ON FUNCTION evergreen.could_be_serial_holding_code(TEXT) IS + 'Return true if parameter is valid JSON representing an array that at minimu +m doesn''t make MARC::Field balk and only has subfield labels exactly one character long. Otherwise false.'; + + +-- This UPDATE throws away data, but only bad data that makes things break +-- anyway. +UPDATE serial.issuance + SET holding_code = NULL + WHERE NOT could_be_serial_holding_code(holding_code); + +ALTER TABLE serial.issuance + DROP CONSTRAINT IF EXISTS issuance_holding_code_check; + +ALTER TABLE serial.issuance + ADD CHECK (holding_code IS NULL OR could_be_serial_holding_code(holding_code)); + + +SELECT evergreen.upgrade_deps_block_check('0770', :eg_version); + +CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$ +DECLARE + cur_barcode TEXT; + barcode_len INT; + completion_len INT; + asset_barcodes TEXT[]; + actor_barcodes TEXT[]; + do_asset BOOL = false; + do_serial BOOL = false; + do_booking BOOL = false; + do_actor BOOL = false; + completion_set config.barcode_completion%ROWTYPE; +BEGIN + + IF position('asset' in type) > 0 THEN + do_asset = true; + END IF; + IF position('serial' in type) > 0 THEN + do_serial = true; + END IF; + IF position('booking' in type) > 0 THEN + do_booking = true; + END IF; + IF do_asset OR do_serial OR do_booking THEN + asset_barcodes = asset_barcodes || in_barcode; + END IF; + IF position('actor' in type) > 0 THEN + do_actor = true; + actor_barcodes = actor_barcodes || in_barcode; + END IF; + + barcode_len := length(in_barcode); + + FOR completion_set IN + SELECT * FROM config.barcode_completion + WHERE active + AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou) + LOOP + IF completion_set.prefix IS NULL THEN + completion_set.prefix := ''; + END IF; + IF completion_set.suffix IS NULL THEN + completion_set.suffix := ''; + END IF; + IF completion_set.length = 0 OR completion_set.padding IS NULL OR length(completion_set.padding) = 0 THEN + cur_barcode = completion_set.prefix || in_barcode || completion_set.suffix; + ELSE + completion_len = completion_set.length - length(completion_set.prefix) - length(completion_set.suffix); + IF completion_len >= barcode_len THEN + IF completion_set.padding_end THEN + cur_barcode = rpad(in_barcode, completion_len, completion_set.padding); + ELSE + cur_barcode = lpad(in_barcode, completion_len, completion_set.padding); + END IF; + cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix; + END IF; + END IF; + IF completion_set.actor THEN + actor_barcodes = actor_barcodes || cur_barcode; + END IF; + IF completion_set.asset THEN + asset_barcodes = asset_barcodes || cur_barcode; + END IF; + END LOOP; + + IF do_asset AND do_serial THEN + RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM ONLY asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false; + RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false; + ELSIF do_asset THEN + RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false; + ELSIF do_serial THEN + RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false; + END IF; + IF do_booking THEN + RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes); + END IF; + IF do_actor THEN + RETURN QUERY SELECT 'actor'::TEXT, c.usr::BIGINT, c.barcode FROM actor.card c JOIN actor.usr u ON c.usr = u.id WHERE + ((c.barcode = ANY(actor_barcodes) AND c.active) OR c.barcode = in_barcode) AND NOT u.deleted ORDER BY usr; + END IF; + RETURN; +END; +$$ LANGUAGE plpgsql; + +-- Evergreen DB patch 0783.schema.enforce_use_id_for_tcn.sql +-- +-- Sets the TCN value in the biblio.record_entry row to bib ID, +-- if the appropriate setting is in place +-- + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0783', :eg_version); + +-- FIXME: add/check SQL statements to perform the upgrade +CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$ +use strict; +use MARC::Record; +use MARC::File::XML (BinaryEncoding => 'UTF-8'); +use MARC::Charset; +use Encode; +use Unicode::Normalize; + +MARC::Charset->assume_unicode(1); + +my $schema = $_TD->{table_schema}; +my $marc = MARC::Record->new_from_xml($_TD->{new}{marc}); + +my @old901s = $marc->field('901'); +$marc->delete_fields(@old901s); + +if ($schema eq 'biblio') { + my $tcn_value = $_TD->{new}{tcn_value}; + + # Set TCN value to record ID? + my $id_as_tcn = spi_exec_query(" + SELECT enabled + FROM config.global_flag + WHERE name = 'cat.bib.use_id_for_tcn' + "); + if (($id_as_tcn->{processed}) && $id_as_tcn->{rows}[0]->{enabled} eq 't') { + $tcn_value = $_TD->{new}{id}; + $_TD->{new}{tcn_value} = $tcn_value; + } + + my $new_901 = MARC::Field->new("901", " ", " ", + "a" => $tcn_value, + "b" => $_TD->{new}{tcn_source}, + "c" => $_TD->{new}{id}, + "t" => $schema + ); + + if ($_TD->{new}{owner}) { + $new_901->add_subfields("o" => $_TD->{new}{owner}); + } + + if ($_TD->{new}{share_depth}) { + $new_901->add_subfields("d" => $_TD->{new}{share_depth}); + } + + $marc->append_fields($new_901); +} elsif ($schema eq 'authority') { + my $new_901 = MARC::Field->new("901", " ", " ", + "c" => $_TD->{new}{id}, + "t" => $schema, + ); + $marc->append_fields($new_901); +} elsif ($schema eq 'serial') { + my $new_901 = MARC::Field->new("901", " ", " ", + "c" => $_TD->{new}{id}, + "t" => $schema, + "o" => $_TD->{new}{owning_lib}, + ); + + if ($_TD->{new}{record}) { + $new_901->add_subfields("r" => $_TD->{new}{record}); + } + + $marc->append_fields($new_901); +} else { + my $new_901 = MARC::Field->new("901", " ", " ", + "c" => $_TD->{new}{id}, + "t" => $schema, + ); + $marc->append_fields($new_901); +} + +my $xml = $marc->as_xml_record(); +$xml =~ s/\n//sgo; +$xml =~ s/^<\?xml.+\?\s*>//go; +$xml =~ s/>\s+entityize() +# to avoid having to set PERL5LIB for PostgreSQL as well + +# If we are going to convert non-ASCII characters to XML entities, +# we had better be dealing with a UTF8 string to begin with +$xml = decode_utf8($xml); + +$xml = NFC($xml); + +# Convert raw ampersands to entities +$xml =~ s/&(?!\S+;)/&/gso; + +# Convert Unicode characters to entities +$xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe; + +$xml =~ s/[\x00-\x1f]//go; +$_TD->{new}{marc} = $xml; + +return "MODIFY"; +$func$ LANGUAGE PLPERLU; + + +COMMIT; -- 2.11.0