1 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
2 my ($marcxml, $pos, $value) = @_;
9 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
10 my $leader = $marc->leader();
11 substr($leader, $pos, 1) = $value;
12 $marc->leader($leader);
13 $xml = $marc->as_xml_record;
14 $xml =~ s/^<\?.+?\?>$//mo;
16 $xml =~ s/>\s+</></sgo;
19 $$ LANGUAGE PLPERLU STABLE;
21 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
22 my ($marcxml, $pos, $value) = @_;
29 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
30 my $f008 = $marc->field('008');
33 my $field = $f008->data();
34 substr($field, $pos, 1) = $value;
35 $f008->update($field);
36 $xml = $marc->as_xml_record;
37 $xml =~ s/^<\?.+?\?>$//mo;
39 $xml =~ s/>\s+</></sgo;
43 $$ LANGUAGE PLPERLU STABLE;
45 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
47 my ($marcxml, $tags) = @_;
55 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
56 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
60 foreach my $field ( $marc->fields() ) {
61 push @incumbents, $field->as_formatted();
64 foreach $field ( $to_insert->fields() ) {
65 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
66 $marc->insert_fields_ordered( ($field) );
70 $xml = $marc->as_xml_record;
71 $xml =~ s/^<\?.+?\?>$//mo;
73 $xml =~ s/>\s+</></sgo;
78 $$ LANGUAGE PLPERLU STABLE;
80 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
83 use MARC::File::XML (BinaryEncoding => 'UTF-8');
86 MARC::Charset->assume_unicode(1);
91 my $r = MARC::Record->new_from_xml( $xml );
92 my $output_xml = $r->as_xml_record();
100 $func$ LANGUAGE PLPERLU;
101 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
103 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
109 use MARC::File::XML (BinaryEncoding => 'UTF-8');
112 MARC::Charset->assume_unicode(1);
114 my $target_xml = shift;
115 my $source_xml = shift;
121 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
125 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
130 my $source_id = $source->subfield('901', 'c');
131 $source_id = $source->subfield('903', 'a') unless $source_id;
132 my $target_id = $target->subfield('901', 'c');
133 $target_id = $target->subfield('903', 'a') unless $target_id;
136 foreach my $tag (@$tags) {
137 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
138 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
139 $target->insert_fields_ordered(map { $_->clone() } @to_add);
141 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
145 my $xml = $target->as_xml_record;
146 $xml =~ s/^<\?.+?\?>$//mo;
148 $xml =~ s/>\s+</></sgo;
152 $func$ LANGUAGE PLPERLU;
153 COMMENT ON FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) IS 'Given two MARCXML strings and an array of tags, returns MARCXML representing the merge of the specified fields from the second MARCXML record into the first.';
155 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
161 use MARC::File::XML (BinaryEncoding => 'UTF-8');
165 my $in_values = shift;
167 # hack-and-slash parsing of array-passed-as-string;
168 # this can go away once everybody is running Postgres 9.1+
169 my $csv = Text::CSV->new({binary => 1});
172 my $status = $csv->parse($in_tags);
173 my $tags = [ $csv->fields() ];
174 $in_values =~ s/^{//;
175 $in_values =~ s/}$//;
176 $status = $csv->parse($in_values);
177 my $values = [ $csv->fields() ];
179 my $marc = MARC::Record->new();
181 $marc->leader('00000nam a22000007 4500');
182 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
184 foreach my $i (0..$#$tags) {
186 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
189 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
190 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
192 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
196 my $xml = $marc->as_xml_record;
197 $xml =~ s/^<\?.+?\?>$//mo;
199 $xml =~ s/>\s+</></sgo;
203 $func$ LANGUAGE PLPERLU;
204 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
205 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
206 The second argument is an array of text containing the values to plug into each field.
207 If the value for a given field is NULL or the empty string, it is not inserted.
210 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) RETURNS TEXT AS $func$
216 use MARC::File::XML (BinaryEncoding => 'UTF-8');
222 my $in_values = shift;
224 # hack-and-slash parsing of array-passed-as-string;
225 # this can go away once everybody is running Postgres 9.1+
226 my $csv = Text::CSV->new({binary => 1});
229 my $status = $csv->parse($in_tags);
230 my $tags = [ $csv->fields() ];
233 $status = $csv->parse($in_ind1);
234 my $ind1s = [ $csv->fields() ];
237 $status = $csv->parse($in_ind2);
238 my $ind2s = [ $csv->fields() ];
239 $in_values =~ s/^{//;
240 $in_values =~ s/}$//;
241 $status = $csv->parse($in_values);
242 my $values = [ $csv->fields() ];
244 my $marc = MARC::Record->new();
246 $marc->leader('00000nam a22000007 4500');
247 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
249 foreach my $i (0..$#$tags) {
251 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
254 $marc->append_fields(MARC::Field->new($tag, $ind1s->[$i], $ind2s->[$i], $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
255 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
257 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
261 my $xml = $marc->as_xml_record;
262 $xml =~ s/^<\?.+?\?>$//mo;
264 $xml =~ s/>\s+</></sgo;
268 $func$ LANGUAGE PLPERLU;
269 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.
270 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
271 The second argument is an array of text containing the values to plug into indicator 1 for each field.
272 The third argument is an array of text containing the values to plug into indicator 2 for each field.
273 The fourth argument is an array of text containing the values to plug into each field.
274 If the value for a given field is NULL or the empty string, it is not inserted.
277 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
279 my ($marcxml, $tag, $pos, $value) = @_;
282 use MARC::File::XML (BinaryEncoding => 'UTF-8');
286 MARC::Charset->assume_unicode(1);
288 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
289 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
290 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
291 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
295 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
297 foreach my $field ($marc->field($tag)) {
298 $field->update("ind$pos" => $value);
300 $xml = $marc->as_xml_record;
301 $xml =~ s/^<\?.+?\?>$//mo;
303 $xml =~ s/>\s+</></sgo;
307 $func$ LANGUAGE PLPERLU;
309 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
310 The first argument is a MARCXML string.
311 The second argument is a MARC tag.
312 The third argument is the indicator position, either 1 or 2.
313 The fourth argument is the character to set the indicator value to.
314 All occurences of the specified field will be changed.
315 The function returns the revised MARCXML string.$$;
317 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
322 first_name TEXT DEFAULT '',
323 last_name TEXT DEFAULT ''
324 ) RETURNS VOID AS $func$
326 RAISE NOTICE '%', org ;
327 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
328 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
329 FROM actor.org_unit aou, permission.grp_tree pgt
330 WHERE aou.shortname = org
331 AND pgt.name = perm_group;
336 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
345 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
346 $field = $marc->leader();
349 $$ LANGUAGE PLPERLU STABLE;
351 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
352 my ($marcxml, $tag, $subfield, $delimiter) = @_;
360 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
361 $field = $marc->field($tag);
363 return $field->as_string($subfield,$delimiter) if $field;
365 $$ LANGUAGE PLPERLU STABLE;
367 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
368 my ($marcxml, $tag, $subfield, $delimiter) = @_;
376 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
377 @fields = $marc->field($tag);
380 foreach my $field (@fields) {
381 push @texts, $field->as_string($subfield,$delimiter);
384 $$ LANGUAGE PLPERLU STABLE;
386 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags_filtered (TEXT, TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
387 my ($marcxml, $tag, $subfield, $delimiter, $match) = @_;
395 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
396 @fields = $marc->field($tag);
399 foreach my $field (@fields) {
400 if ($field->as_string() =~ qr/$match/) {
401 push @texts, $field->as_string($subfield,$delimiter);
405 $$ LANGUAGE PLPERLU STABLE;
407 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(INTEGER,TEXT,TEXT);
408 CREATE OR REPLACE FUNCTION migration_tools.merge_sf9(bib_id INTEGER,new_sf9 TEXT,force TEXT DEFAULT 'false')
415 SELECT marc FROM biblio.record_entry WHERE id = bib_id INTO marc_xml;
417 SELECT munge_sf9(marc_xml,new_sf9,force) INTO new_marc;
418 UPDATE biblio.record_entry SET marc = new_marc WHERE id = bib_id;
422 $BODY$ LANGUAGE plpgsql;
424 DROP FUNCTION IF EXISTS migration_tools.munge_sf9(TEXT,TEXT,TEXT);
425 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9(marc_xml TEXT, new_9_to_set TEXT, force TEXT)
433 use MARC::File::XML (BinaryEncoding => 'utf8');
435 binmode(STDERR, ':bytes');
436 binmode(STDOUT, ':utf8');
437 binmode(STDERR, ':utf8');
439 my $marc_xml = shift;
440 my $new_9_to_set = shift;
443 $marc_xml =~ s/(<leader>.........)./${1}a/;
446 $marc_xml = MARC::Record->new_from_xml($marc_xml);
449 #elog("could not parse $bibid: $@\n");
450 import MARC::File::XML (BinaryEncoding => 'utf8');
454 my @uris = $marc_xml->field('856');
455 return $marc_xml->as_xml_record() unless @uris;
457 foreach my $field (@uris) {
458 my $ind1 = $field->indicator('1');
459 if (!defined $ind1) { next; }
460 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
461 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
462 my $ind2 = $field->indicator('2');
463 if (!defined $ind2) { next; }
464 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
465 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
466 $field->add_subfields( '9' => $new_9_to_set );
469 return $marc_xml->as_xml_record();
473 DROP FUNCTION IF EXISTS migration_tools.munge_sf9_qualifying_match(TEXT,TEXT,TEXT);
474 CREATE OR REPLACE FUNCTION migration_tools.munge_sf9_qualifying_match(marc_xml TEXT, qualifying_match TEXT, new_9_to_set TEXT, force TEXT)
482 use MARC::File::XML (BinaryEncoding => 'utf8');
484 binmode(STDERR, ':bytes');
485 binmode(STDOUT, ':utf8');
486 binmode(STDERR, ':utf8');
488 my $marc_xml = shift;
489 my $qualifying_match = shift;
490 my $new_9_to_set = shift;
493 $marc_xml =~ s/(<leader>.........)./${1}a/;
496 $marc_xml = MARC::Record->new_from_xml($marc_xml);
499 #elog("could not parse $bibid: $@\n");
500 import MARC::File::XML (BinaryEncoding => 'utf8');
504 my @uris = $marc_xml->field('856');
505 return $marc_xml->as_xml_record() unless @uris;
507 foreach my $field (@uris) {
508 if ($field->as_string() =~ qr/$qualifying_match/) {
509 my $ind1 = $field->indicator('1');
510 if (!defined $ind1) { next; }
511 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
512 if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
513 my $ind2 = $field->indicator('2');
514 if (!defined $ind2) { next; }
515 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
516 if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
517 $field->add_subfields( '9' => $new_9_to_set );
521 return $marc_xml->as_xml_record();
525 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,TEXT,TEXT,TEXT);
526 CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match (marc_xml TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
534 use MARC::File::XML (BinaryEncoding => 'utf8');
536 binmode(STDERR, ':bytes');
537 binmode(STDOUT, ':utf8');
538 binmode(STDERR, ':utf8');
540 my $marc_xml = shift;
541 my $substring_old_value = shift;
542 my $new_value = shift;
543 my $fix_indicators = shift;
545 $marc_xml =~ s/(<leader>.........)./${1}a/;
548 $marc_xml = MARC::Record->new_from_xml($marc_xml);
551 #elog("could not parse $bibid: $@\n");
552 import MARC::File::XML (BinaryEncoding => 'utf8');
556 my @uris = $marc_xml->field('856');
557 return $marc_xml->as_xml_record() unless @uris;
559 foreach my $field (@uris) {
560 my $ind1 = $field->indicator('1');
562 if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
563 $field->set_indicator(1,'4');
566 my $ind2 = $field->indicator('2');
568 if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
569 $field->set_indicator(2,'0');
572 if ($field->as_string('9') =~ qr/$substring_old_value/) {
573 $field->delete_subfield('9');
574 $field->add_subfields( '9' => $new_value );
576 $marc_xml->delete_field($field); # -- we're going to dedup and add them back
579 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
580 $marc_xml->insert_fields_ordered( values( %hash ) );
582 return $marc_xml->as_xml_record();
586 DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match2(TEXT,TEXT,TEXT,TEXT,TEXT);
587 CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match2 (marc_xml TEXT, qualifying_match TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
595 use MARC::File::XML (BinaryEncoding => 'utf8');
597 binmode(STDERR, ':bytes');
598 binmode(STDOUT, ':utf8');
599 binmode(STDERR, ':utf8');
601 my $marc_xml = shift;
602 my $qualifying_match = shift;
603 my $substring_old_value = shift;
604 my $new_value = shift;
605 my $fix_indicators = shift;
607 $marc_xml =~ s/(<leader>.........)./${1}a/;
610 $marc_xml = MARC::Record->new_from_xml($marc_xml);
613 #elog("could not parse $bibid: $@\n");
614 import MARC::File::XML (BinaryEncoding => 'utf8');
618 my @unqualified_uris = $marc_xml->field('856');
620 foreach my $field (@unqualified_uris) {
621 if ($field->as_string() =~ qr/$qualifying_match/) {
625 return $marc_xml->as_xml_record() unless @uris;
627 foreach my $field (@uris) {
628 my $ind1 = $field->indicator('1');
630 if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
631 $field->set_indicator(1,'4');
634 my $ind2 = $field->indicator('2');
636 if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
637 $field->set_indicator(2,'0');
640 if ($field->as_string('9') =~ qr/$substring_old_value/) {
641 $field->delete_subfield('9');
642 $field->add_subfields( '9' => $new_value );
644 $marc_xml->delete_field($field); # -- we're going to dedup and add them back
647 my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
648 $marc_xml->insert_fields_ordered( values( %hash ) );
650 return $marc_xml->as_xml_record();
655 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
656 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
664 use MARC::File::XML (BinaryEncoding => 'utf8');
666 binmode(STDERR, ':bytes');
667 binmode(STDOUT, ':utf8');
668 binmode(STDERR, ':utf8');
670 my $marc_xml = shift;
673 $marc_xml =~ s/(<leader>.........)./${1}a/;
676 $marc_xml = MARC::Record->new_from_xml($marc_xml);
679 #elog("could not parse $bibid: $@\n");
680 import MARC::File::XML (BinaryEncoding => 'utf8');
684 my @fields = $marc_xml->field($tag);
685 return $marc_xml->as_xml_record() unless @fields;
687 $marc_xml->delete_fields(@fields);
689 return $marc_xml->as_xml_record();
693 -- removes tags from record based on tag, subfield and evidence
694 -- example: strip_tag(marc, '500', 'a', 'gift') will remove 500s with 'gift' as a part of the $a
695 DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT,TEXT,TEXT);
696 CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT, subfield TEXT, evidence TEXT)
704 use MARC::File::XML (BinaryEncoding => 'utf8');
706 binmode(STDERR, ':bytes');
707 binmode(STDOUT, ':utf8');
708 binmode(STDERR, ':utf8');
710 my $marc_xml = shift;
712 my $subfield = shift;
713 my $evidence = shift;
715 $marc_xml =~ s/(<leader>.........)./${1}a/;
718 $marc_xml = MARC::Record->new_from_xml($marc_xml);
721 #elog("could not parse $bibid: $@\n");
722 import MARC::File::XML (BinaryEncoding => 'utf8');
726 my @fields = $marc_xml->field($tag);
727 return $marc_xml->as_xml_record() unless @fields;
729 my @fields_to_delete;
731 foreach my $f (@fields) {
732 my $sf = lc($f->as_string($subfield));
733 if ($sf =~ m/$evidence/) { push @fields_to_delete, $f; }
736 $marc_xml->delete_fields(@fields_to_delete);
738 return $marc_xml->as_xml_record();
742 -- consolidate marc tag
743 DROP FUNCTION IF EXISTS migration_tools.consolidate_tag(TEXT,TEXT);
744 CREATE OR REPLACE FUNCTION migration_tools.consolidate_tag(marc TEXT, tag TEXT)
752 use MARC::File::XML (BinaryEncoding => 'utf8');
754 binmode(STDERR, ':bytes');
755 binmode(STDOUT, ':utf8');
756 binmode(STDERR, ':utf8');
758 my $marc_xml = shift;
761 $marc_xml =~ s/(<leader>.........)./${1}a/;
764 $marc_xml = MARC::Record->new_from_xml($marc_xml);
767 #elog("could not parse $bibid: $@\n");
768 import MARC::File::XML (BinaryEncoding => 'utf8');
772 my @fields = $marc_xml->field($tag);
773 return $marc_xml->as_xml_record() unless @fields;
775 my @combined_subfield_refs = ();
776 my @combined_subfields = ();
777 foreach my $field (@fields) {
778 my @subfield_refs = $field->subfields();
779 push @combined_subfield_refs, @subfield_refs;
782 my @sorted_subfield_refs = reverse sort { $a->[0] <=> $b->[0] } @combined_subfield_refs;
784 while ( my $tuple = pop( @sorted_subfield_refs ) ) {
785 my ($code,$data) = @$tuple;
786 unshift( @combined_subfields, $code, $data );
789 $marc_xml->delete_fields(@fields);
791 my $new_field = new MARC::Field(
793 $fields[0]->indicator(1),
794 $fields[0]->indicator(2),
798 $marc_xml->insert_grouped_field( $new_field );
800 return $marc_xml->as_xml_record();