END;
$$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
+CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix2 (TEXT) RETURNS TEXT[] AS $$
+ DECLARE
+ full_name TEXT := $1;
+ temp TEXT;
+ family_name TEXT := '';
+ first_given_name TEXT := '';
+ second_given_name TEXT := '';
+ suffix TEXT := '';
+ prefix TEXT := '';
+ BEGIN
+ temp := BTRIM(full_name);
+ -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
+ --IF temp ~ '^\S{2,}\.' THEN
+ -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
+ -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
+ --END IF;
+ --IF temp ~ '\S{2,}\.$' THEN
+ -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
+ -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
+ --END IF;
+ IF temp ilike '%MR.%' THEN
+ prefix := 'Mr.';
+ temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
+ END IF;
+ IF temp ilike '%MRS.%' THEN
+ prefix := 'Mrs.';
+ temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
+ END IF;
+ IF temp ilike '%MS.%' THEN
+ prefix := 'Ms.';
+ temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
+ END IF;
+ IF temp ilike '%DR.%' THEN
+ prefix := 'Dr.';
+ temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
+ END IF;
+ IF temp ilike '%JR.%' THEN
+ suffix := 'Jr.';
+ temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
+ END IF;
+ IF temp ilike '%JR,%' THEN
+ suffix := 'Jr.';
+ temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
+ END IF;
+ IF temp ilike '%SR.%' THEN
+ suffix := 'Sr.';
+ temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
+ END IF;
+ IF temp ilike '%SR,%' THEN
+ suffix := 'Sr.';
+ temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
+ END IF;
+ IF temp like '%III%' THEN
+ suffix := 'III';
+ temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
+ END IF;
+ IF temp like '%II%' THEN
+ suffix := 'II';
+ temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
+ END IF;
+
+ IF temp ~ ',' THEN
+ family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
+ temp := BTRIM(REPLACE( temp, family_name, '' ));
+ family_name := REPLACE( family_name, ',', '' );
+ IF temp ~ ' ' THEN
+ first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
+ second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
+ ELSE
+ first_given_name := temp;
+ second_given_name := '';
+ END IF;
+ ELSE
+ IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
+ first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
+ second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
+ family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
+ ELSE
+ first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
+ second_given_name := temp;
+ family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
+ END IF;
+ END IF;
+
+ family_name := BTRIM(REPLACE(REPLACE(family_name,',',''),'"',''));
+ first_given_name := BTRIM(REPLACE(REPLACE(first_given_name,',',''),'"',''));
+ second_given_name := BTRIM(REPLACE(REPLACE(second_given_name,',',''),'"',''));
+
+ RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
+ END;
+$$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
+
CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
DECLARE
city_state_zip TEXT := $1;
IF x_org_found THEN
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_shelf = id FROM asset_copy_location b'
+ || ' SET x_shelf = b.id FROM asset_copy_location b'
|| ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
|| ' AND b.owning_lib = x_org'
|| ' AND NOT b.deleted';
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_shelf = id FROM asset.copy_location b'
+ || ' SET x_shelf = b.id FROM asset.copy_location b'
|| ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
|| ' AND b.owning_lib = x_org'
|| ' AND x_shelf IS NULL'
|| ' AND NOT b.deleted';
ELSE
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_shelf = id FROM asset_copy_location b'
+ || ' 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 NOT b.deleted'
USING org;
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_shelf = id FROM asset_copy_location b'
+ || ' 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'
FOREACH o IN ARRAY org_list LOOP
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_shelf = id FROM asset.copy_location b'
+ || ' 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'
|| ' ADD COLUMN x_org INTEGER';
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_org = id FROM actor.org_unit b'
+ || ' SET x_org = b.id FROM actor.org_unit b'
|| ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
EXECUTE 'SELECT migration_tools.assert(
END
$$ LANGUAGE plpgsql;
--- yet another subfield 9 function, this one only adds the $9 if the ind1 = 1 or 4 and ind2 = 0 or 1
-DROP FUNCTION IF EXISTS migration_tools.strict_add_sf9(TEXT,TEXT);
-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$;
+DROP FUNCTION IF EXISTS migration_tools.munge_sf9(INTEGER,TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.merge_group(bib_id INTEGER,new_sf9 TEXT,force TEXT DEFAULT 'false')
+ RETURNS BOOLEAN AS
+$BODY$
+DECLARE
+ marc_xml TEXT;
+ new_marc TEXT;
+BEGIN
+ SELECT marc FROM biblio.record_entry WHERE id = bib_id INTO marc_xml;
+
+ SELECT munge_sf9(marc_xml,new_sf9,force) INTO new_marc;
+ UPDATE biblio.record_entry SET marc = new_marc WHERE id = bib_id;
+
+ RETURN true;
+END;
+$BODY$ LANGUAGE plpgsql;
--- yet another subfield 9 function, this one only adds the $9 and forces
--- ind1 = 4 if not already ind1 = 1 or 4 and ind2 = 0 if not already ind2 = 0 or 1
-DROP FUNCTION IF EXISTS migration_tools.force_add_sf9(TEXT,TEXT);
-CREATE OR REPLACE FUNCTION migration_tools.force_add_sf9(marc TEXT, new_9 TEXT)
+DROP FUNCTION IF EXISTS migration_tools.munge_sf9(TEXT,TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.munge_sf9(marc_xml TEXT, new_9_to_set TEXT, force TEXT)
RETURNS TEXT
LANGUAGE plperlu
AS $function$
my $marc_xml = shift;
my $new_9_to_set = shift;
+my $force = shift;
$marc_xml =~ s/(<leader>.........)./${1}a/;
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'); }
+ 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') { $field->set_indicator(2,'0'); }
+ 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 );
}
$function$;
--- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
-DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT);
-CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u 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 $matching_u_text = 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;
-}
-
-my @uris = $marc_xml->field('856');
-return unless @uris;
-
-foreach my $field (@uris) {
- my $sfu = $field->subfield('u');
- my $ind2 = $field->indicator('2');
- if (!defined $ind2) { next; }
- if ($ind2 ne '0') { next; }
- if (!defined $sfu) { next; }
- if ($sfu =~ m/$matching_u_text/ or $matching_u_text eq 'pineapple') {
- $field->add_subfields( '9' => $new_9_to_set );
- last;
- }
-}
-
-return $marc_xml->as_xml_record();
-
-$function$;
-
-DROP FUNCTION IF EXISTS migration_tools.add_sf9(BIGINT, TEXT, TEXT, REGCLASS);
-CREATE OR REPLACE FUNCTION migration_tools.add_sf9(bib_id BIGINT, target_u_text TEXT, sf9_text TEXT, bib_table REGCLASS)
- RETURNS BOOLEAN AS
-$BODY$
-DECLARE
- source_xml TEXT;
- new_xml TEXT;
- r BOOLEAN;
-BEGIN
-
- EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml;
-
- SELECT migration_tools.add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
-
- r = FALSE;
- new_xml = '$_$' || new_xml || '$_$';
-
- IF new_xml != source_xml THEN
- EXECUTE 'UPDATE ' || bib_table || ' SET marc = ' || new_xml || ' WHERE id = ' || bib_id;
- r = TRUE;
- END IF;
-
- RETURN r;
-
-END;
-$BODY$ LANGUAGE plpgsql;
-
-- 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)
projects / migration-tools.git / blobdiff