X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;fp=sql%2Fbase%2Fbase.sql;h=46d221a0a54c5b998943af731c1f8746b76cbfbc;hp=2607c9578f8cc9e27236b1da675fd52b93240982;hb=a212c25811ff0694d76573252ffcda63d0816ebd;hpb=aacf49e00625e2ca75279e8b721167ee7697dd26

diff --git a/sql/base/base.sql b/sql/base/base.sql
index 2607c95..46d221a 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -3543,6 +3543,27 @@ CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT
     return \@texts;
 $$ LANGUAGE PLPERLU STABLE;
 
+CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags_filtered (TEXT, TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
+    my ($marcxml, $tag, $subfield, $delimiter, $match) = @_;
+
+    use MARC::Record;
+    use MARC::File::XML;
+    use MARC::Field;
+
+    my @fields;
+    eval {
+        my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+        @fields = $marc->field($tag);
+    };
+    my @texts;
+    foreach my $field (@fields) {
+        if ($field->as_string() =~ qr/$match/) {
+            push @texts, $field->as_string($subfield,$delimiter);
+        }
+    }
+    return \@texts;
+$$ LANGUAGE PLPERLU STABLE;
+
 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
     SELECT action.find_hold_matrix_matchpoint(
         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
@@ -3650,13 +3671,17 @@ $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 
 
 -- convenience functions for handling copy_location maps
-
 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
+    SELECT migration_tools._handle_shelf($1,$2,$3,$4,TRUE);
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
     DECLARE
         table_schema ALIAS FOR $1;
         table_name ALIAS FOR $2;
         org_shortname ALIAS FOR $3;
         org_range ALIAS FOR $4;
+        make_assertion ALIAS FOR $5;
         proceed BOOLEAN;
         org INTEGER;
         -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
@@ -3665,6 +3690,7 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER)
         x_org INTEGER;
         org_list INTEGER[];
         o INTEGER;
+        row_count NUMERIC;
     BEGIN
         EXECUTE 'SELECT EXISTS (
             SELECT 1
@@ -3700,6 +3726,7 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER)
             || ' ADD COLUMN x_shelf INTEGER';
 
         IF x_org_found THEN
+            RAISE INFO 'Found x_org column';
             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
                 || ' SET x_shelf = b.id FROM asset_copy_location b'
                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
@@ -3712,6 +3739,7 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER)
                 || ' AND x_shelf IS NULL'
                 || ' AND NOT b.deleted';
         ELSE
+            RAISE INFO 'Did not find x_org column';
             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
                 || ' SET x_shelf = b.id FROM asset_copy_location b'
                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
@@ -3728,19 +3756,24 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER)
         END IF;
 
         FOREACH o IN ARRAY org_list LOOP
+            RAISE INFO 'Considering org %', o;
             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
                 || ' SET x_shelf = b.id FROM asset.copy_location b'
                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
                 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
                 || ' AND NOT b.deleted'
             USING o;
+            GET DIAGNOSTICS row_count = ROW_COUNT;
+            RAISE INFO 'Updated % rows', row_count;
         END LOOP;
 
-        EXECUTE 'SELECT migration_tools.assert(
-            NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
-            ''Cannot find a desired location'',
-            ''Found all desired locations''
-        );';
+        IF make_assertion THEN
+            EXECUTE 'SELECT migration_tools.assert(
+                NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
+                ''Cannot find a desired location'',
+                ''Found all desired locations''
+            );';
+        END IF;
 
     END;
 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
@@ -4340,7 +4373,59 @@ return $marc_xml->as_xml_record();
 
 $function$;
 
-DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,TEXT,TEXT);
+DROP FUNCTION IF EXISTS migration_tools.munge_sf9_qualifying_match(TEXT,TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.munge_sf9_qualifying_match(marc_xml TEXT, qualifying_match TEXT, new_9_to_set TEXT, force 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 $qualifying_match = shift;
+my $new_9_to_set = shift;
+my $force = 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) {
+    if ($field->as_string() =~ qr/$qualifying_match/) {
+        my $ind1 = $field->indicator('1');
+        if (!defined $ind1) { next; }
+        if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
+        if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
+        my $ind2 = $field->indicator('2');
+        if (!defined $ind2) { next; }
+        if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
+        if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
+        $field->add_subfields( '9' => $new_9_to_set );
+    }
+}
+
+return $marc_xml->as_xml_record();
+
+$function$;
+
+DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,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
@@ -4400,6 +4485,75 @@ $marc_xml->insert_fields_ordered( values( %hash ) );
 return $marc_xml->as_xml_record();
 
 $function$;
+
+DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match2(TEXT,TEXT,TEXT,TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match2 (marc_xml TEXT, qualifying_match 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 $qualifying_match = 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 @unqualified_uris = $marc_xml->field('856');
+my @uris = ();
+foreach my $field (@unqualified_uris) {
+    if ($field->as_string() =~ qr/$qualifying_match/) {
+        push @uris, $field;
+    }
+}
+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)