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.call_number_prefix,asset.call_number_suffix,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,actor.usr_standing_penalty,actor.usr_setting,action.circulation,action.hold_request,action.hold_notification,action.hold_request_note,action.hold_transit_copy,action.transit_copy,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.provider_contact,acq.provider_contact_address,acq.fund,acq.fund_allocation,acq.fund_tag,acq.fund_tag_map,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note,config.circ_matrix_matchpoint,config.circ_matrix_limit_set_map,config.hold_matrix_matchpoint,asset.copy_tag,asset.copy_tag_copy_map,config.copy_tag_type,serial.item,serial.item_note,serial.record_entry'' );' );
+ PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.call_number_prefix,asset.call_number_suffix,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,actor.usr_standing_penalty,actor.usr_setting,action.circulation,action.hold_request,action.hold_notification,action.hold_request_note,action.hold_transit_copy,action.transit_copy,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.provider_contact,acq.provider_contact_address,acq.fund,acq.fund_allocation,acq.fund_tag,acq.fund_tag_map,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note,config.circ_matrix_matchpoint,config.circ_matrix_limit_set_map,config.hold_matrix_matchpoint,asset.copy_tag,asset.copy_tag_copy_map,config.copy_tag_type,serial.item,serial.item_note,serial.record_entry,biblio.record_entry'' );' );
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);' );
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$
+ my ($address) = @_;
+
+ use Geo::StreetAddress::US;
+
+ my $a = Geo::StreetAddress::US->parse_location($address);
+
+ return [
+ "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}"
+ ,"$a->{sec_unit_type} $a->{sec_unit_num}"
+ ,$a->{city}
+ ,$a->{state}
+ ,$a->{zip}
+ ];
+$$ LANGUAGE PLPERLU STABLE;
+
CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
DECLARE
n TEXT := o;
return $barcode . $checkdigit;
$$ LANGUAGE PLPERLU STRICT STABLE;
+-- add_mod16_checkdigit
+-- $barcode source barcode
+--
+-- https://www.activebarcode.com/codes/checkdigit/modulo16.html
+
+CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
+ my $barcode = shift;
+
+ my @digits = split //, $barcode;
+ my $total = 0;
+ foreach $digit (@digits) {
+ if ($digit =~ /[0-9]/) { $total += $digit;
+ } elsif ($digit eq '-') { $total += 10;
+ } elsif ($digit eq '$') { $total += 11;
+ } elsif ($digit eq ':') { $total += 12;
+ } elsif ($digit eq '/') { $total += 13;
+ } elsif ($digit eq '.') { $total += 14;
+ } elsif ($digit eq '+') { $total += 15;
+ } elsif ($digit eq 'A') { $total += 16;
+ } elsif ($digit eq 'B') { $total += 17;
+ } elsif ($digit eq 'C') { $total += 18;
+ } elsif ($digit eq 'D') { $total += 19;
+ } else { die "invalid digit <$digit>";
+ }
+ }
+ my $remainder = $total % 16;
+ my $difference = 16 - $remainder;
+ my $checkdigit;
+ if ($difference < 10) { $checkdigit = $difference;
+ } elsif ($difference == 10) { $checkdigit = '-';
+ } elsif ($difference == 11) { $checkdigit = '$';
+ } elsif ($difference == 12) { $checkdigit = ':';
+ } elsif ($difference == 13) { $checkdigit = '/';
+ } elsif ($difference == 14) { $checkdigit = '.';
+ } elsif ($difference == 15) { $checkdigit = '+';
+ } else { die "error calculating checkdigit";
+ }
+
+ return $barcode . $checkdigit;
+$$ LANGUAGE PLPERLU STRICT STABLE;
+
CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
DECLARE
phone TEXT := $1;
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
|| ' SET x_migrate = CASE'
|| ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
+ || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
|| ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
+ || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
|| ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
|| ' END';
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$;
+
-- 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)
IF btrim_desired THEN
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_item = id FROM asset_copy_legacy b'
+ || ' SET x_item = b.id FROM asset_copy_legacy b'
|| ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
|| ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
ELSE
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_item = id FROM asset_copy_legacy b'
+ || ' SET x_item = b.id FROM asset_copy_legacy b'
|| ' WHERE a.' || quote_ident(foreign_column_name)
|| ' = b.' || quote_ident(main_column_name);
END IF;
IF btrim_desired THEN
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_user = id FROM actor_usr_legacy b'
+ || ' SET x_user = b.id FROM actor_usr_legacy b'
|| ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
|| ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
ELSE
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_user = id FROM actor_usr_legacy b'
+ || ' SET x_user = b.id FROM actor_usr_legacy b'
|| ' WHERE a.' || quote_ident(foreign_column_name)
|| ' = b.' || quote_ident(main_column_name);
END IF;
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
+ LANGUAGE plpgsql
+AS $function$
+DECLARE
+ c_name TEXT;
+BEGIN
+
+ FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
+ table_name = t_name
+ AND table_schema = s_name
+ AND (data_type='text' OR data_type='character varying')
+ AND column_name like 'l_%'
+ LOOP
+ EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
+ END LOOP;
+
+ RETURN TRUE;
+END
+$function$;
+
+DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
+ LANGUAGE plpgsql
+AS $function$
+DECLARE
+ c_name TEXT;
+BEGIN
+
+ FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
+ table_name = t_name
+ AND table_schema = s_name
+ AND (data_type='text' OR data_type='character varying')
+ AND column_name like 'l_%'
+ LOOP
+ EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
+ END LOOP;
+
+ RETURN TRUE;
+END
+$function$;
+