migration_tools.handle_asset_barcode_collisions

[migration-tools.git] / sql / base / base.sql

diff --git a/sql/base/base.sql b/sql/base/base.sql
index d70f55d..cc26b77 100644 (file)
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -5161,94 +5161,36 @@ BEGIN
 END
 $function$;
 
-CREATE OR REPLACE FUNCTION migration_tools.force_add_sf9(marc text, new_9 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 $new_9_to_set = 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) { next; }
-    if ($ind1 ne '1' && $ind1 ne '4') { $field->set_indicator(1,'4'); }
-    my $ind2 = $field->indicator('2');
-    if (!defined $ind2) { next; }
-    if ($ind2 ne '0' && $ind2 ne '1') { $field->set_indicator(2,'0'); }
-    $field->add_subfields( '9' => $new_9_to_set );
-}
-
-return $marc_xml->as_xml_record();
-
-$function$
-
-CREATE OR REPLACE FUNCTION migration_tools.strict_add_sf9(marc text, new_9 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 $new_9_to_set = 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) { next; }
-    if ($ind1 ne '1' && $ind1 ne '4') { next; }
-    my $ind2 = $field->indicator('2');
-    if (!defined $ind2) { next; }
-    if ($ind2 ne '0' && $ind2 ne '1') { next; }
-    $field->add_subfields( '9' => $new_9_to_set );
-}
-
-return $marc_xml->as_xml_record();
-
-$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;