BEGIN
base_staging_table = REPLACE( production_table, '.', '_' );
--RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
- PERFORM migration_tools.exec( $1, 'CREATE UNLOGGED TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
+ PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
PERFORM migration_tools.exec( $1, '
INSERT INTO ' || migration_schema || '.fields_requiring_mapping
SELECT table_schema, table_name, column_name, data_type
column_list TEXT := '';
column_count INTEGER := 0;
BEGIN
- create_sql := 'CREATE UNLOGGED TABLE ' || migration_schema || '.' || parent_table || '_legacy ( ';
+ create_sql := 'CREATE TABLE ' || migration_schema || '.' || parent_table || '_legacy ( ';
FOR columns IN
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
$$ LANGUAGE PLPERLU STABLE;
DROP TABLE IF EXISTS migration_tools.usps_suffixes;
-CREATE UNLOGGED TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
+CREATE TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
INSERT INTO migration_tools.usps_suffixes VALUES
('ALLEE','ALY'),
('ALLEY','ALY'),
$function$;
+DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match (marc_xml TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
+ RETURNS TEXT
+ LANGUAGE plperlu
+AS $function$
+use strict;
+use warnings;
+
+use MARC::Record;
+use MARC::File::XML (BinaryEncoding => 'utf8');
+
+binmode(STDERR, ':bytes');
+binmode(STDOUT, ':utf8');
+binmode(STDERR, ':utf8');
+
+my $marc_xml = shift;
+my $substring_old_value = shift;
+my $new_value = shift;
+my $fix_indicators = shift;
+
+$marc_xml =~ s/(<leader>.........)./${1}a/;
+
+eval {
+ $marc_xml = MARC::Record->new_from_xml($marc_xml);
+};
+if ($@) {
+ #elog("could not parse $bibid: $@\n");
+ import MARC::File::XML (BinaryEncoding => 'utf8');
+ return $marc_xml;
+}
+
+my @uris = $marc_xml->field('856');
+return $marc_xml->as_xml_record() unless @uris;
+
+foreach my $field (@uris) {
+ my $ind1 = $field->indicator('1');
+ if (defined $ind1) {
+ if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
+ $field->set_indicator(1,'4');
+ }
+ }
+ my $ind2 = $field->indicator('2');
+ if (defined $ind2) {
+ if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
+ $field->set_indicator(2,'0');
+ }
+ }
+ if ($field->as_string('9') =~ qr/$substring_old_value/) {
+ $field->delete_subfield('9');
+ $field->add_subfields( '9' => $new_value );
+ }
+ $marc_xml->delete_field($field); # -- we're going to dedup and add them back
+}
+
+my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
+$marc_xml->insert_fields_ordered( values( %hash ) );
+
+return $marc_xml->as_xml_record();
+
+$function$;
-- strip marc tag
DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
END
$function$;
+
+-- convenience function for handling item barcode collisions in asset_copy_legacy
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
+DECLARE
+ x_barcode TEXT;
+ x_id BIGINT;
+ row_count NUMERIC;
+ internal_collision_count NUMERIC := 0;
+ incumbent_collision_count NUMERIC := 0;
+BEGIN
+ FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
+ LOOP
+ FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
+ LOOP
+ UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ internal_collision_count := internal_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% internal collisions', internal_collision_count;
+ FOR x_barcode IN SELECT a.barcode FROM asset.copy a, asset_copy_legacy b WHERE x_migrate AND a.deleted IS FALSE AND a.barcode = b.barcode
+ LOOP
+ FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
+ LOOP
+ UPDATE asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ incumbent_collision_count := incumbent_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% incumbent collisions', incumbent_collision_count;
+END
+$function$ LANGUAGE plpgsql;
+
+-- convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
+-- this should be ran prior to populating actor_card
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
+DECLARE
+ x_barcode TEXT;
+ x_id BIGINT;
+ row_count NUMERIC;
+ internal_collision_count NUMERIC := 0;
+ incumbent_barcode_collision_count NUMERIC := 0;
+ incumbent_usrname_collision_count NUMERIC := 0;
+BEGIN
+ FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
+ LOOP
+ FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+ LOOP
+ UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ internal_collision_count := internal_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
+
+ FOR x_barcode IN
+ SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
+ LOOP
+ FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+ LOOP
+ UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
+
+ FOR x_barcode IN
+ SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
+ LOOP
+ FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+ LOOP
+ UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
+END
+$function$ LANGUAGE plpgsql;
projects / migration-tools.git / blobdiff