X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=56cab3db486de04728051ce9aaef42e5f8090304;hp=168e779e11bf688f523b759acdb54cf7eaa40bd9;hb=c240a9ee5487a1d2deee2170a05a3131df60a366;hpb=b9b25104e037780b5b959849a55593ddbf1e5205 diff --git a/sql/base/base.sql b/sql/base/base.sql index 168e779..56cab3d 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -261,6 +261,36 @@ CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TE END; $$ LANGUAGE PLPGSQL STRICT VOLATILE; +-- creates other child table so you can have more than one child table in a schema from a base table +CREATE OR REPLACE FUNCTION build_variant_staging_table(text, text, text) + RETURNS void + LANGUAGE plpgsql + STRICT +AS $function$ + DECLARE + migration_schema ALIAS FOR $1; + production_table ALIAS FOR $2; + base_staging_table ALIAS FOR $3; + columns RECORD; + BEGIN + --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table; + PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' ); + PERFORM migration_tools.exec( $1, ' + INSERT INTO ' || migration_schema || '.fields_requiring_mapping + SELECT table_schema, table_name, column_name, data_type + FROM information_schema.columns + WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL; + ' ); + FOR columns IN + SELECT table_schema, table_name, column_name, data_type + FROM information_schema.columns + WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL + LOOP + PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' ); + END LOOP; + END; +$function$ + CREATE OR REPLACE FUNCTION migration_tools.create_linked_legacy_table_from (TEXT,TEXT,TEXT) RETURNS VOID AS $$ DECLARE migration_schema ALIAS FOR $1; @@ -1836,6 +1866,80 @@ FROM ( 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/(.........)./${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; @@ -3095,29 +3199,6 @@ END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$ - -use MARC::Record; -use MARC::File::XML (BinaryEncoding => 'UTF-8'); -use MARC::Charset; - -MARC::Charset->assume_unicode(1); - -my $xml = shift; - -eval { - my $r = MARC::Record->new_from_xml( $xml ); - my $output_xml = $r->as_xml_record(); -}; -if ($@) { - return 0; -} else { - return 1; -} - -$func$ LANGUAGE PLPERLU; -COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML'; - CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$ BEGIN EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ || @@ -3217,239 +3298,6 @@ BEGIN END; $FUNC$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$ - -use strict; -use warnings; - -use MARC::Record; -use MARC::File::XML (BinaryEncoding => 'UTF-8'); -use MARC::Charset; - -MARC::Charset->assume_unicode(1); - -my $target_xml = shift; -my $source_xml = shift; -my $tags = shift; - -my $target; -my $source; - -eval { $target = MARC::Record->new_from_xml( $target_xml ); }; -if ($@) { - return; -} -eval { $source = MARC::Record->new_from_xml( $source_xml ); }; -if ($@) { - return; -} - -my $source_id = $source->subfield('901', 'c'); -$source_id = $source->subfield('903', 'a') unless $source_id; -my $target_id = $target->subfield('901', 'c'); -$target_id = $target->subfield('903', 'a') unless $target_id; - -my %existing_fields; -foreach my $tag (@$tags) { - my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag); - my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag); - $target->insert_fields_ordered(map { $_->clone() } @to_add); - if (@to_add) { - elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id"); - } -} - -my $xml = $target->as_xml_record; -$xml =~ s/^<\?.+?\?>$//mo; -$xml =~ s/\n//sgo; -$xml =~ s/>\s+ 'UTF-8'); -use Text::CSV; - -my $in_tags = 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_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, ' ', ' ', $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+ '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+ 'UTF-8'); -use MARC::Charset; -use strict; - -MARC::Charset->assume_unicode(1); - -elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/; -elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/; -elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/; -elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/; - -my $xml = $marcxml; -eval { - my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8'); - - foreach my $field ($marc->field($tag)) { - $field->update("ind$pos" => $value); - } - $xml = $marc->as_xml_record; - $xml =~ s/^<\?.+?\?>$//mo; - $xml =~ s/\n//sgo; - $xml =~ s/>\s+new_from_xml($marcxml, 'UTF-8'); - $field = $marc->leader(); - }; - return $field; -$$ LANGUAGE PLPERLU STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$ - my ($marcxml, $tag, $subfield, $delimiter) = @_; - - use MARC::Record; - use MARC::File::XML; - use MARC::Field; - - my $field; - eval { - my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8'); - $field = $marc->field($tag); - }; - return $field->as_string($subfield,$delimiter); -$$ LANGUAGE PLPERLU STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$ - my ($marcxml, $tag, $subfield, $delimiter) = @_; - - use MARC::Record; - use MARC::File::XML; - use MARC::Field; - - my @fields; - eval { - my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8'); - @fields = $marc->field($tag); - }; - my @texts; - foreach my $field (@fields) { - push @texts, $field->as_string($subfield,$delimiter); - } - return \@texts; -$$ LANGUAGE PLPERLU STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags_filtered (TEXT, TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$ - my ($marcxml, $tag, $subfield, $delimiter, $match) = @_; - - use MARC::Record; - use MARC::File::XML; - use MARC::Field; - - my @fields; - eval { - my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8'); - @fields = $marc->field($tag); - }; - my @texts; - foreach my $field (@fields) { - if ($field->as_string() =~ qr/$match/) { - push @texts, $field->as_string($subfield,$delimiter); - } - } - return \@texts; -$$ LANGUAGE PLPERLU STABLE; - CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$ SELECT action.find_hold_matrix_matchpoint( (SELECT pickup_lib FROM action.hold_request WHERE id = $1), @@ -4535,353 +4313,7 @@ BEGIN END $$ LANGUAGE plpgsql; -DROP FUNCTION IF EXISTS migration_tools.munge_sf9(INTEGER,TEXT,TEXT); -CREATE OR REPLACE FUNCTION migration_tools.merge_group(bib_id INTEGER,new_sf9 TEXT,force TEXT DEFAULT 'false') - RETURNS BOOLEAN AS -$BODY$ -DECLARE - marc_xml TEXT; - new_marc TEXT; -BEGIN - SELECT marc FROM biblio.record_entry WHERE id = bib_id INTO marc_xml; - - SELECT munge_sf9(marc_xml,new_sf9,force) INTO new_marc; - UPDATE biblio.record_entry SET marc = new_marc WHERE id = bib_id; - - RETURN true; -END; -$BODY$ LANGUAGE plpgsql; - -DROP FUNCTION IF EXISTS migration_tools.munge_sf9(TEXT,TEXT,TEXT); -CREATE OR REPLACE FUNCTION migration_tools.munge_sf9(marc_xml TEXT, new_9_to_set TEXT, force 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; -my $force = 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 @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' && $force eq 'false') { next; } - if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); } - my $ind2 = $field->indicator('2'); - if (!defined $ind2) { next; } - if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; } - if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); } - $field->add_subfields( '9' => $new_9_to_set ); -} - -return $marc_xml->as_xml_record(); - -$function$; - -DROP FUNCTION IF EXISTS migration_tools.munge_sf9_qualifying_match(TEXT,TEXT,TEXT); -CREATE OR REPLACE FUNCTION migration_tools.munge_sf9_qualifying_match(marc_xml TEXT, qualifying_match TEXT, new_9_to_set TEXT, force 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 $qualifying_match = shift; -my $new_9_to_set = shift; -my $force = 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 @uris = $marc_xml->field('856'); -return $marc_xml->as_xml_record() unless @uris; - -foreach my $field (@uris) { - if ($field->as_string() =~ qr/$qualifying_match/) { - my $ind1 = $field->indicator('1'); - if (!defined $ind1) { next; } - if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; } - if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); } - my $ind2 = $field->indicator('2'); - if (!defined $ind2) { next; } - if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; } - if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); } - $field->add_subfields( '9' => $new_9_to_set ); - } -} - -return $marc_xml->as_xml_record(); - -$function$; - -DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,TEXT,TEXT,TEXT); -CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match (marc_xml TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators 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 $substring_old_value = shift; -my $new_value = shift; -my $fix_indicators = 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 @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) { - if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') { - $field->set_indicator(1,'4'); - } - } - my $ind2 = $field->indicator('2'); - if (defined $ind2) { - if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') { - $field->set_indicator(2,'0'); - } - } - if ($field->as_string('9') =~ qr/$substring_old_value/) { - $field->delete_subfield('9'); - $field->add_subfields( '9' => $new_value ); - } - $marc_xml->delete_field($field); # -- we're going to dedup and add them back -} - -my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-) -$marc_xml->insert_fields_ordered( values( %hash ) ); - -return $marc_xml->as_xml_record(); - -$function$; - -DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match2(TEXT,TEXT,TEXT,TEXT,TEXT); -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) - 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 $qualifying_match = shift; -my $substring_old_value = shift; -my $new_value = shift; -my $fix_indicators = 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 @unqualified_uris = $marc_xml->field('856'); -my @uris = (); -foreach my $field (@unqualified_uris) { - if ($field->as_string() =~ qr/$qualifying_match/) { - push @uris, $field; - } -} -return $marc_xml->as_xml_record() unless @uris; - -foreach my $field (@uris) { - my $ind1 = $field->indicator('1'); - if (defined $ind1) { - if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') { - $field->set_indicator(1,'4'); - } - } - my $ind2 = $field->indicator('2'); - if (defined $ind2) { - if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') { - $field->set_indicator(2,'0'); - } - } - if ($field->as_string('9') =~ qr/$substring_old_value/) { - $field->delete_subfield('9'); - $field->add_subfields( '9' => $new_value ); - } - $marc_xml->delete_field($field); # -- we're going to dedup and add them back -} - -my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-) -$marc_xml->insert_fields_ordered( values( %hash ) ); - -return $marc_xml->as_xml_record(); - -$function$; - --- strip marc tag -DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT); -CREATE OR REPLACE FUNCTION migration_tools.strip_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; - -$marc_xml->delete_fields(@fields); - -return $marc_xml->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$; +function$; -- convenience function for linking to the item staging table @@ -5840,8 +5272,9 @@ DECLARE result_rule_object config.circ_matrix_matchpoint%ROWTYPE; safe_to_delete BOOLEAN := FALSE; m action.found_circ_matrix_matchpoint; - result_matchpoint INTEGER; + n action.found_circ_matrix_matchpoint; -- ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] ) + result_matchpoint INTEGER; BEGIN SELECT INTO test_rule_object * FROM config.circ_matrix_matchpoint WHERE id = test_matchpoint; RAISE INFO 'testing rule: %', test_rule_object; @@ -5916,62 +5349,97 @@ BEGIN user_object, COALESCE(test_rule_object.is_renewal,FALSE) ); - RAISE INFO 'action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)', + RAISE INFO ' action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)', test_rule_object.org_unit, item_object.id, user_object.id, COALESCE(test_rule_object.is_renewal,FALSE), m.success, - (m.matchpoint).id, + m.matchpoint, m.buildrows ; - FOR result_matchpoint IN SELECT UNNEST(m.buildrows) - LOOP - SELECT INTO result_rule_object * FROM config.circ_matrix_matchpoint WHERE id = result_matchpoint; - RAISE INFO 'considering rule: %', result_rule_object; - IF result_rule_object.id = test_rule_object.id THEN - RAISE INFO 'found self'; - CONTINUE; - END IF; - IF (result_rule_object.circulate = test_rule_object.circulate - AND result_rule_object.duration_rule = test_rule_object.duration_rule - AND result_rule_object.recurring_fine_rule = test_rule_object.recurring_fine_rule - AND result_rule_object.max_fine_rule = test_rule_object.max_fine_rule - AND ( - (result_rule_object.hard_due_date IS NULL AND test_rule_object.hard_due_date IS NULL) - OR (result_rule_object.hard_due_date = test_rule_object.hard_due_date) - OR (result_rule_object.hard_due_date IS NOT NULL AND test_rule_object.hard_due_date IS NULL) - ) - AND ( - (result_rule_object.renewals IS NULL AND test_rule_object.renewals IS NULL) - OR (result_rule_object.renewals = test_rule_object.renewals) - OR (result_rule_object.renewals IS NOT NULL AND test_rule_object.renewals IS NULL) - ) - AND ( - (result_rule_object.grace_period IS NULL AND test_rule_object.grace_period IS NULL) - OR (result_rule_object.grace_period = test_rule_object.grace_period) - OR (result_rule_object.grace_period IS NOT NULL AND test_rule_object.grace_period IS NULL) - ) - AND NOT EXISTS ( - SELECT limit_set, fallthrough - FROM config.circ_matrix_limit_set_map - WHERE active and matchpoint = test_rule_object.id - EXCEPT - SELECT limit_set, fallthrough - FROM config.circ_matrix_limit_set_map - WHERE active and matchpoint = result_rule_object.id - ) - ) THEN - RAISE INFO 'rule has same outcome'; - safe_to_delete := TRUE; - ELSE - RAISE INFO 'rule has different outcome, bail now'; - RAISE EXCEPTION 'rollback the item and user tables'; - END IF; - END LOOP; + -- disable the rule being tested to see if the outcome changes + UPDATE config.circ_matrix_matchpoint SET active = FALSE WHERE id = (m.matchpoint).id; + + SELECT INTO n * FROM action.find_circ_matrix_matchpoint( + test_rule_object.org_unit, + item_object, + user_object, + COALESCE(test_rule_object.is_renewal,FALSE) + ); + RAISE INFO 'VS action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)', + test_rule_object.org_unit, + item_object.id, + user_object.id, + COALESCE(test_rule_object.is_renewal,FALSE), + n.success, + n.matchpoint, + n.buildrows + ; + + -- FIXME: We could dig deeper and see if the referenced config.rule_* + -- entries are effectively equivalent, but for now, let's assume no + -- duplicate rules at that level + IF ( + (m.matchpoint).circulate = (n.matchpoint).circulate + AND (m.matchpoint).duration_rule = (n.matchpoint).duration_rule + AND (m.matchpoint).recurring_fine_rule = (n.matchpoint).recurring_fine_rule + AND (m.matchpoint).max_fine_rule = (n.matchpoint).max_fine_rule + AND ( + (m.matchpoint).hard_due_date = (n.matchpoint).hard_due_date + OR ( + (m.matchpoint).hard_due_date IS NULL + AND (n.matchpoint).hard_due_date IS NULL + ) + ) + AND ( + (m.matchpoint).renewals = (n.matchpoint).renewals + OR ( + (m.matchpoint).renewals IS NULL + AND (n.matchpoint).renewals IS NULL + ) + ) + AND ( + (m.matchpoint).grace_period = (n.matchpoint).grace_period + OR ( + (m.matchpoint).grace_period IS NULL + AND (n.matchpoint).grace_period IS NULL + ) + ) + AND ( + (m.matchpoint).total_copy_hold_ratio = (n.matchpoint).total_copy_hold_ratio + OR ( + (m.matchpoint).total_copy_hold_ratio IS NULL + AND (n.matchpoint).total_copy_hold_ratio IS NULL + ) + ) + AND ( + (m.matchpoint).available_copy_hold_ratio = (n.matchpoint).available_copy_hold_ratio + OR ( + (m.matchpoint).available_copy_hold_ratio IS NULL + AND (n.matchpoint).available_copy_hold_ratio IS NULL + ) + ) + AND NOT EXISTS ( + SELECT limit_set, fallthrough + FROM config.circ_matrix_limit_set_map + WHERE active and matchpoint = (m.matchpoint).id + EXCEPT + SELECT limit_set, fallthrough + FROM config.circ_matrix_limit_set_map + WHERE active and matchpoint = (n.matchpoint).id + ) + + ) THEN + RAISE INFO 'rule has same outcome'; + safe_to_delete := TRUE; + ELSE + RAISE INFO 'rule has different outcome'; + safe_to_delete := FALSE; + END IF; - RAISE EXCEPTION 'rollback the item and user tables'; + RAISE EXCEPTION 'rollback the temporary changes'; EXCEPTION WHEN OTHERS THEN