X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=569dd07feb62c9285eebade702fbc21fa7dede8b;hp=6ae7e39d9236b137b376e5a55e3cfe0571bfd5b9;hb=96965bbc9a9d05e52cf71d5c721383038eddcbda;hpb=bd2353ff6fc23a1a5da50e4c7698a2619ba3a47c diff --git a/sql/base/base.sql b/sql/base/base.sql index 6ae7e39..569dd07 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -101,7 +101,7 @@ CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$ END; PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.config;' ); PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.config ( key TEXT UNIQUE, value TEXT);' ); - PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,action.circulation,action.hold_request,money.grocery,money.billing,money.cash_payment,money.forgive_payment'' );' ); + PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,action.circulation,action.hold_request,action.hold_notification,money.grocery,money.billing,money.cash_payment,money.forgive_payment'' );' ); PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''country_code'', ''USA'' );' ); PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.fields_requiring_mapping;' ); PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.fields_requiring_mapping( table_schema TEXT, table_name TEXT, column_name TEXT, data_type TEXT);' ); @@ -302,10 +302,18 @@ CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_firs suffix := 'Jr.'; temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' ); END IF; + IF temp ilike '%JR,%' THEN + suffix := 'Jr.'; + temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ); + END IF; IF temp ilike '%SR%' THEN suffix := 'Sr.'; temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' ); END IF; + IF temp ilike '%SR,%' THEN + suffix := 'Sr.'; + temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ); + END IF; IF temp ~ E'\\sII$' THEN suffix := 'II'; temp := REGEXP_REPLACE( temp, E'II$', '', 'i' ); @@ -314,6 +322,10 @@ CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_firs suffix := 'III'; temp := REGEXP_REPLACE( temp, E'III$', '', 'i' ); END IF; + IF temp ~ E'\\sIV$' THEN + suffix := 'IV'; + temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' ); + END IF; family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') ); first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END ); @@ -593,4 +605,210 @@ CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen; return "$prefix$new_barcode$suffix"; -$$ LANGUAGE PLPERL; +$$ LANGUAGE PLPERL STABLE; + +CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT,TEXT,TEXT) RETURNS RECORD AS $$ + DECLARE + attempt_value ALIAS FOR $1; + datatype ALIAS FOR $2; + fail_value ALIAS FOR $3; + output RECORD; + BEGIN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;' + LOOP + RETURN output; + END LOOP; + EXCEPTION + WHEN OTHERS THEN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(fail_value) || '::' || datatype || ' AS a;' + LOOP + RETURN output; + END LOOP; + END; +$$ LANGUAGE PLPGSQL STRICT STABLE; + +CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$ + DECLARE + attempt_value ALIAS FOR $1; + fail_value ALIAS FOR $2; + output DATE; + BEGIN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::date AS a;' + LOOP + RETURN output; + END LOOP; + EXCEPTION + WHEN OTHERS THEN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;' + LOOP + RETURN output; + END LOOP; + END; +$$ LANGUAGE PLPGSQL STRICT STABLE; + +CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$ + DECLARE + attempt_value ALIAS FOR $1; + fail_value ALIAS FOR $2; + output NUMERIC(8,2); + BEGIN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::NUMERIC(8,2) AS a;' + LOOP + RETURN output; + END LOOP; + EXCEPTION + WHEN OTHERS THEN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;' + LOOP + RETURN output; + END LOOP; + END; +$$ LANGUAGE PLPGSQL STRICT STABLE; + +-- add_codabar_checkdigit +-- $barcode source barcode +-- +-- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14 +-- character with a checkdigit computed according to the usual algorithm for library barcodes +-- using the Codabar symbology - see . If the +-- input string does not meet those requirements, it is returned unchanged. +-- +CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$ + my $barcode = shift; + + return $barcode if $barcode !~ /^\d{13,14}$/; + $barcode = substr($barcode, 0, 13); # ignore 14th digit + my @digits = split //, $barcode; + my $total = 0; + $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11); + $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12); + my $remainder = $total % 10; + my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder; + return $barcode . $checkdigit; +$$ LANGUAGE PLPERL STRICT STABLE; + +CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$ + DECLARE + phone TEXT := $1; + areacode TEXT := $2; + temp TEXT := ''; + output TEXT := ''; + n_digits INTEGER := 0; + BEGIN + temp := phone; + temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', ''); + temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3'); + n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g')); + IF n_digits = 7 AND areacode <> '' THEN + temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2'); + output := (areacode || '-' || temp); + ELSE + output := temp; + END IF; + RETURN output; + END; + +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + +CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$ + my ($marcxml, $pos, $value) = @_; + + use MARC::Record; + use MARC::File::XML; + + my $xml = $marcxml; + eval { + my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8'); + my $leader = $marc->leader(); + substr($leader, $pos, 1) = $value; + $marc->leader($leader); + $xml = $marc->as_xml_record; + $xml =~ s/^<\?.+?\?>$//mo; + $xml =~ s/\n//sgo; + $xml =~ s/>\s+new_from_xml($marcxml, 'UTF-8'); + my $f008 = $marc->field('008'); + + if ($f008) { + my $field = $f008->data(); + substr($field, $pos, 1) = $value; + $f008->update($field); + $xml = $marc->as_xml_record; + $xml =~ s/^<\?.+?\?>$//mo; + $xml =~ s/\n//sgo; + $xml =~ s/>\s+new_from_xml($marcxml, 'UTF-8'); + my $to_insert = MARC::Record->new_from_xml("$tags", 'UTF-8'); + + my @incumbents = (); + + foreach my $field ( $marc->fields() ) { + push @incumbents, $field->as_formatted(); + } + + foreach $field ( $to_insert->fields() ) { + if (!grep {$_ eq $field->as_formatted()} @incumbents) { + $marc->insert_fields_ordered( ($field) ); + } + } + + $xml = $marc->as_xml_record; + $xml =~ s/^<\?.+?\?>$//mo; + $xml =~ s/\n//sgo; + $xml =~ s/>\s+