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;
+
+-- add koha holding tag to marc
+DROP FUNCTION IF EXISTS migration_tools.generate_koha_holding_tag(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
+
+CREATE OR REPLACE FUNCTION migration_tools.generate_koha_holding_tag(marc TEXT, tag TEXT, ind1 TEXT, ind2 TEXT, barcode TEXT, dateaccessioned TEXT, booksellerid TEXT, homebranch TEXT, price TEXT, replacementprice TEXT, replacementpricedate TEXT, datelastborrowed TEXT, datelastseen TEXT, stack TEXT, notforloan TEXT, damaged TEXT, itemlost TEXT, wthdrawn TEXT, itemcallnumber TEXT, issues TEXT, renewals TEXT, reserves TEXT, restricted TEXT, internalnotes TEXT, itemnotes TEXT, holdingbranch TEXT, location TEXT, onloan TEXT, cn_source TEXT, cn_sort TEXT, ccode TEXT, materials TEXT, uri TEXT, itype TEXT, enumchron TEXT, copynumber TEXT, stocknumber 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, $tag , $ind1 , $ind2 , $barcode , $dateaccessioned , $booksellerid , $homebranch , $price , $replacementprice , $replacementpricedate , $datelastborrowed , $datelastseen , $stack , $notforloan , $damaged , $itemlost , $wthdrawn , $itemcallnumber , $issues , $renewals , $reserves , $restricted , $internalnotes , $itemnotes , $holdingbranch , $location , $onloan , $cn_source , $cn_sort , $ccode , $materials , $uri , $itype , $enumchron , $copynumber , $stocknumber ) = @_;
+
+$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 $new_field = new MARC::Field(
+ $tag, $ind1, $ind2,
+ 'a' => $homebranch,
+ 'b' => $holdingbranch,
+ 'c' => $location,
+ 'p' => $barcode,
+ 'y' => $itype
+);
+
+if ($dateaccessioned) { $new_field->add_subfields('d' => $dateaccessioned); }
+if ($booksellerid) { $new_field->add_subfields('e' => $booksellerid); }
+if ($price) { $new_field->add_subfields('g' => $price); }
+if ($replacementprice) { $new_field->add_subfields('v' => $replacementprice); }
+if ($replacementpricedate) { $new_field->add_subfields('w' => $replacementpricedate); }
+if ($datelastborrowed) { $new_field->add_subfields('s' => $datelastborrowed); }
+if ($datelastseen) { $new_field->add_subfields('r' => $datelastseen); }
+if ($stack) { $new_field->add_subfields('j' => $stack); }
+if ($notforloan) { $new_field->add_subfields('7' => $notforloan); }
+if ($damaged) { $new_field->add_subfields('4' => $damaged); }
+if ($itemlost) { $new_field->add_subfields('1' => $itemlost); }
+if ($wthdrawn) { $new_field->add_subfields('0' => $wthdrawn); }
+if ($itemcallnumber) { $new_field->add_subfields('o' => $itemcallnumber); }
+if ($issues) { $new_field->add_subfields('l' => $issues); }
+if ($renewals) { $new_field->add_subfields('m' => $renewals); }
+if ($reserves) { $new_field->add_subfields('n' => $reserves); }
+if ($restricted) { $new_field->add_subfields('5' => $restricted); }
+if ($internalnotes) { $new_field->add_subfields('x' => $internalnotes); }
+if ($itemnotes) { $new_field->add_subfields('z' => $itemnotes); }
+if ($onloan) { $new_field->add_subfields('q' => $onloan); }
+if ($cn_source) { $new_field->add_subfields('2' => $cn_source); }
+if ($cn_sort) { $new_field->add_subfields('6' => $cn_sort); }
+if ($ccode) { $new_field->add_subfields('8' => $ccode); }
+if ($materials) { $new_field->add_subfields('3' => $materials); }
+if ($uri) { $new_field->add_subfields('u' => $uri); }
+if ($enumchron) { $new_field->add_subfields('h' => $enumchron); }
+if ($copynumber) { $new_field->add_subfields('t' => $copynumber); }
+if ($stocknumber) { $new_field->add_subfields('i' => $stocknumber); }
+
+$marc_xml->insert_grouped_field( $new_field );
+
+return $marc_xml->as_xml_record();
+
+$function$;
+
CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
DECLARE
attempt_value ALIAS FOR $1;
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+</></sgo;
+
+return $xml;
+
+$func$ LANGUAGE PLPERLU;
+COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
+The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
+The second argument is an array of text containing the values to plug into indicator 1 for each field.
+The third argument is an array of text containing the values to plug into indicator 2 for each field.
+The fourth argument is an array of text containing the values to plug into each field.
+If the value for a given field is NULL or the empty string, it is not inserted.
+$$;
+
CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
my ($marcxml, $tag, $pos, $value) = @_;
$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/(<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 @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 $$
projects / migration-tools.git / blobdiff