X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=168e779e11bf688f523b759acdb54cf7eaa40bd9;hp=3e669ae84d885cde3fd19b300f30f53fd512e576;hb=b9b25104e037780b5b959849a55593ddbf1e5205;hpb=2c79ec8f7a4721f7af70b17b9d49f8cc71d3d221 diff --git a/sql/base/base.sql b/sql/base/base.sql index 3e669ae..168e779 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -1675,6 +1675,167 @@ CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) END; $$ LANGUAGE PLPGSQL STRICT STABLE; +CREATE OR REPLACE FUNCTION migration_tools.openbiblio2marc (x_bibid TEXT) RETURNS TEXT AS $func$ +BEGIN +-- Expects the following table/columns: + +-- export_biblio_tsv: +-- l_bibid | 1 +-- l_create_dt | 2007-03-07 09:03:09 +-- l_last_change_dt | 2015-01-23 11:18:54 +-- l_last_change_userid | 2 +-- l_material_cd | 10 +-- l_collection_cd | 13 +-- l_call_nmbr1 | Canada +-- l_call_nmbr2 | ON +-- l_call_nmbr3 | Ottawa 18 +-- l_title | Art and the courts : France ad England +-- l_title_remainder | from 1259-1328 +-- l_responsibility_stmt | +-- l_author | National Gallery of Canada +-- l_topic1 | +-- l_topic2 | +-- l_topic3 | +-- l_topic4 | +-- l_topic5 | +-- l_opac_flg | Y +-- l_flag_attention | 0 + +-- export_biblio_field_tsv: +-- l_bibid | 1 +-- l_fieldid | 1 +-- l_tag | 720 +-- l_ind1_cd | N +-- l_ind2_cd | N +-- l_subfield_cd | a +-- l_field_data | Brieger, Peter Henry + +-- Map export_biblio_tsv as follows: +-- l_call_nmbr? -> 099a +-- l_author -> 100a +-- l_title -> 245a +-- l_title_remainder -> 245b +-- l_responsibility_stmt -> 245c +-- l_topic? -> 650a +-- l_bibid -> 001 + +RETURN + migration_tools.consolidate_tag( migration_tools.make_stub_bib(y.tag,y.ind1,y.ind2,y.data), '245' ) +FROM ( + select + array_agg(lpad(l_tag,3,'0') || l_subfield_cd) as "tag", + array_agg(l_ind1_cd) as "ind1", + array_agg(l_ind2_cd) as "ind2", + array_agg(l_field_data) as "data" + from ( + select + l_tag, + l_subfield_cd, + l_ind1_cd, + l_ind2_cd, + l_field_data + from export_biblio_field_tsv + where l_bibid = x_bibid + union + select + '099' as "l_tag", + 'a' as "l_subfield_cd", + ' ' as "l_ind1_cd", + ' ' as "l_ind2_cd", + concat_ws(' ', + nullif(btrim(l_call_nmbr1),''), + nullif(btrim(l_call_nmbr2),''), + nullif(btrim(l_call_nmbr3),'') + ) as "l_field_data" + from export_biblio_tsv + where l_bibid = x_bibid + union + select + '100' as "l_tag", + 'a' as "l_subfield_cd", + ' ' as "l_ind1_cd", + ' ' as "l_ind2_cd", + l_author as "l_field_data" + from export_biblio_tsv + where l_bibid = x_bibid and nullif(btrim(l_author),'') is not null + union + select + '245' as "l_tag", + 'a' as "l_subfield_cd", + ' ' as "l_ind1_cd", + ' ' as "l_ind2_cd", + l_title as "l_field_data" + from export_biblio_tsv + where l_bibid = x_bibid and nullif(btrim(l_title),'') is not null + union + select + '245' as "l_tag", + 'b' as "l_subfield_cd", + ' ' as "l_ind1_cd", + ' ' as "l_ind2_cd", + l_title_remainder as "l_field_data" + from export_biblio_tsv + where l_bibid = x_bibid and nullif(btrim(l_title_remainder),'') is not null + union + select + '650' as "l_tag", + 'a' as "l_subfield_cd", + ' ' as "l_ind1_cd", + ' ' as "l_ind2_cd", + l_topic1 as "l_field_data" + from export_biblio_tsv + where l_bibid = x_bibid and nullif(btrim(l_topic1),'') is not null + union + select + '650' as "l_tag", + 'a' as "l_subfield_cd", + ' ' as "l_ind1_cd", + ' ' as "l_ind2_cd", + l_topic2 as "l_field_data" + from export_biblio_tsv + where l_bibid = x_bibid and nullif(btrim(l_topic2),'') is not null + union + select + '650' as "l_tag", + 'a' as "l_subfield_cd", + ' ' as "l_ind1_cd", + ' ' as "l_ind2_cd", + l_topic3 as "l_field_data" + from export_biblio_tsv + where l_bibid = x_bibid and nullif(btrim(l_topic3),'') is not null + union + select + '650' as "l_tag", + 'a' as "l_subfield_cd", + ' ' as "l_ind1_cd", + ' ' as "l_ind2_cd", + l_topic4 as "l_field_data" + from export_biblio_tsv + where l_bibid = x_bibid and nullif(btrim(l_topic4),'') is not null + union + select + '650' as "l_tag", + 'a' as "l_subfield_cd", + ' ' as "l_ind1_cd", + ' ' as "l_ind2_cd", + l_topic5 as "l_field_data" + from export_biblio_tsv + where l_bibid = x_bibid and nullif(btrim(l_topic5),'') is not null + union + select + '001' as "l_tag", + '' as "l_subfield_cd", + '' as "l_ind1_cd", + '' as "l_ind2_cd", + l_bibid as "l_field_data" + from export_biblio_tsv + where l_bibid = x_bibid + ) x +) y; + +END +$func$ LANGUAGE plpgsql; + CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$ DECLARE attempt_value ALIAS FOR $1; @@ -3163,6 +3324,73 @@ The second argument is an array of text containing the values to plug into each If the value for a given field is NULL or the empty string, it is not inserted. $$; +CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) RETURNS TEXT AS $func$ + +use strict; +use warnings; + +use MARC::Record; +use MARC::File::XML (BinaryEncoding => 'UTF-8'); +use Text::CSV; + +my $in_tags = shift; +my $in_ind1 = shift; +my $in_ind2 = shift; +my $in_values = shift; + +# hack-and-slash parsing of array-passed-as-string; +# this can go away once everybody is running Postgres 9.1+ +my $csv = Text::CSV->new({binary => 1}); +$in_tags =~ s/^{//; +$in_tags =~ s/}$//; +my $status = $csv->parse($in_tags); +my $tags = [ $csv->fields() ]; +$in_ind1 =~ s/^{//; +$in_ind1 =~ s/}$//; +$status = $csv->parse($in_ind1); +my $ind1s = [ $csv->fields() ]; +$in_ind2 =~ s/^{//; +$in_ind2 =~ s/}$//; +$status = $csv->parse($in_ind2); +my $ind2s = [ $csv->fields() ]; +$in_values =~ s/^{//; +$in_values =~ s/}$//; +$status = $csv->parse($in_values); +my $values = [ $csv->fields() ]; + +my $marc = MARC::Record->new(); + +$marc->leader('00000nam a22000007 4500'); +$marc->append_fields(MARC::Field->new('008', '000000s 000 eng d')); + +foreach my $i (0..$#$tags) { + my ($tag, $sf); + if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) { + $tag = $1; + $sf = $2; + $marc->append_fields(MARC::Field->new($tag, $ind1s->[$i], $ind2s->[$i], $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL'; + } elsif ($tags->[$i] =~ /^(\d{3})$/) { + $tag = $1; + $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL'; + } +} + +my $xml = $marc->as_xml_record; +$xml =~ s/^<\?.+?\?>$//mo; +$xml =~ s/\n//sgo; +$xml =~ s/>\s+as_xml_record(); $function$; +-- consolidate marc tag +DROP FUNCTION IF EXISTS migration_tools.consolidate_tag(TEXT,TEXT); +CREATE OR REPLACE FUNCTION migration_tools.consolidate_tag(marc TEXT, tag 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 $tag = shift; + +$marc_xml =~ s/(.........)./${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 @fields = $marc_xml->field($tag); +return $marc_xml->as_xml_record() unless @fields; + +my @combined_subfield_refs = (); +my @combined_subfields = (); +foreach my $field (@fields) { + my @subfield_refs = $field->subfields(); + push @combined_subfield_refs, @subfield_refs; +} + +my @sorted_subfield_refs = reverse sort { $a->[0] <=> $b->[0] } @combined_subfield_refs; + +while ( my $tuple = pop( @sorted_subfield_refs ) ) { + my ($code,$data) = @$tuple; + unshift( @combined_subfields, $code, $data ); +} + +$marc_xml->delete_fields(@fields); + +my $new_field = new MARC::Field( + $tag, + $fields[0]->indicator(1), + $fields[0]->indicator(2), + @combined_subfields +); + +$marc_xml->insert_grouped_field( $new_field ); + +return $marc_xml->as_xml_record(); + +$function$; + -- convenience function for linking to the item staging table CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$