From 968dbc8bc6637064a999f091b51a4db958e7fc46 Mon Sep 17 00:00:00 2001 From: Mark Tompsett Date: Mon, 19 Oct 2015 14:43:42 -0400 Subject: [PATCH] Bug 4226: Follow to address comment #9 It would be appreciated if testing of this script was redone to confirm if all the points were handled properly. The only point truly outstanding still is 6. --- misc/migration_tools/sysno_keeper.pl | 327 +++++++++++++++++++---------------- 1 file changed, 175 insertions(+), 152 deletions(-) diff --git a/misc/migration_tools/sysno_keeper.pl b/misc/migration_tools/sysno_keeper.pl index 1f41739..594c6be 100644 --- a/misc/migration_tools/sysno_keeper.pl +++ b/misc/migration_tools/sysno_keeper.pl @@ -1,66 +1,79 @@ #!/usr/bin/perl -# Copyright 2011 Stefano Bargioni, Pontificia Università della Santa Croce - Rome -# # This file is part of Koha. # -# Koha is free software; you can redistribute it and/or modify it under the -# terms of the GNU General Public License as published by the Free Software -# Foundation; either version 2 of the License, or (at your option) any later -# version. +# Copyright 2011 Stefano Bargioni, Pontificia Università della Santa Croce - Rome +# Copyright 2015 Mark Tompsett +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. # -# Koha is distributed in the hope that it will be useful, but WITHOUT ANY -# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR -# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. # -# You should have received a copy of the GNU General Public License along -# with Koha; if not, write to the Free Software Foundation, Inc., -# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . # Written by Stefano Bargioni Pontificia Università della Santa Croce - Rome # bargioni@pusc.it 2011-11-07 -use strict; -use warnings; -use MARC::Record; -use MARC::File::XML; -use MARC::Field; +# Made perlcritic nicer by Mark Tompsett 2015-10-19 + use C4::Biblio; use C4::Context; + +use Carp; +use English qw( -no_match_vars ); use Getopt::Long; use IO::File; +use MARC::Field; +use MARC::File::XML; +use MARC::Record; +use Modern::Perl; use Pod::Usage; -binmode(STDOUT, ":utf8"); +use constant DEFAULT_RECORDS_BEFORE_FLUSH => 50; +use constant DEFAULT_TAG_LENGTH => 3; +use constant DEFAULT_TAG => '001'; +use constant DEFAULT_SUBTAG => q{@}; +use constant SINGLE_QUOTE => q{'}; +use constant PERIOD => q{.}; + +binmode STDOUT, ':encoding(UTF-8)'; my $help = 0; -my $commit = 50; +my $commit = DEFAULT_RECORDS_BEFORE_FLUSH; my $biblio = 0; my $auth = 0; -my $tag = '001'; -my $subfield = '@'; +my $tag = DEFAULT_TAG; +my $subfield = DEFAULT_SUBTAG; my $verbose = 0; GetOptions( - 'help|?|h' => \$help, - 'a|authorities:s' => \$auth, - 'b|biblios:s' => \$biblio, - 'c|commit:n' => \$commit, - 't|tag:s' => \$tag, - 's|subfield:s' => \$subfield, - 'v|verbose' => \$verbose, + 'help|?|h' => \$help, + 'a|authorities' => \$auth, + 'b|biblios' => \$biblio, + 'c|commit:n' => \$commit, + 't|tag:s' => \$tag, + 's|subfield:s' => \$subfield, + 'v|verbose' => \$verbose, ) or pod2usage(2); -pod2usage( -exitstatus => 0, -verbose => 2 ) if $help; -die('No record type specified') if ( !$biblio && !$auth ); -die('You cannot specify both record types') if ( $biblio && $auth ); -die("Invalid MARC tag $tag") if ( length($tag) != 3 ); -die("Invalid subfield $subfield") if ( length($subfield) != 1 ); -die('Invalid commit value') if ( $commit < 1 ); +if ($help) { pod2usage( -exitstatus => 0, -verbose => 2 ); } + +if ( !$biblio && !$auth ) { croak ('No record type specified'); } +if ( $biblio && $auth ) { croak ('You cannot specify both record types'); } +if ( length($tag) != DEFAULT_TAG_LENGTH ) { croak ("Invalid MARC tag $tag"); } +if ( length($subfield) != 1 ) { croak ("Invalid subfield $subfield"); } +if ( $commit < 1 ) { croak ('Invalid commit value'); } my $dbh = C4::Context->dbh; my $rows; -$|=1; # autoflush +$OUTPUT_AUTOFLUSH = 1; # autoflush # check MySQL version >= 5.1. Otherwise MySQL lacks the ExtractValue XML function my $sqlv = 'select version()'; @@ -68,136 +81,146 @@ my $sthv = $dbh->prepare($sqlv); $sthv->execute; my ($mysql_version) = $sthv->fetchrow_array(); $sthv->finish; -die('Your MySQL version $mysql_version is less than 5.1') if ($mysql_version lt '5.1'); +if ($mysql_version lt '5.1') { croak "Your MySQL version $mysql_version is less than 5.1"; } # An example of $xpath: '//datafield[@tag="999"]/subfield[@code="c"]'; -my $xpath = "'//"; +my $xpath = q{'//}; if ($tag gt '009') {$xpath .= 'datafield'} else {$xpath .= 'controlfield'}; -$xpath .= '[@tag="'.$tag.'"]'; -$xpath .= "/subfield[\@code=\"$subfield\"]" if ($subfield ne '@'); -$xpath .= "'"; -print 'using xpath '.$xpath."\n" if ($verbose); +$xpath .= "[\@tag=\"$tag\"]"; +if ($subfield ne DEFAULT_SUBTAG) { + $xpath .= "/subfield[\@code=\"$subfield\"]"; +} +$xpath .= SINGLE_QUOTE; +if ($verbose) { print "using xpath $xpath\n"; } # exit; # test -print `date` if ($verbose); +if ($verbose) { system 'date'; } if ($auth) { - print "Keeping system numbers of authority records\n" if ($verbose); - # check existence of tag/subfield - $sqlv = "select count(*) from auth_header union select count(*) from auth_header where ExtractValue(marcxml,$xpath) != ''"; - $sthv = $dbh->prepare($sqlv); - $sthv->execute; - ($rows) = $sthv->fetchrow_array(); - my ($ctsxml) = $sthv->fetchrow_array(); # a second line can be absent - if (!defined $ctsxml) {$ctsxml=$rows} - print "$ctsxml of $rows records containing $tag\$$subfield\n" if ($verbose); - $sthv->finish; - die("Tag and/or subfield $tag\$$subfield specified are not present in each record") if($rows-$ctsxml !=0 || $rows+$ctsxml==0); - - print "1. dropping primary key for auth_header.authid\n" if ($verbose); - $dbh->do('ALTER TABLE auth_header CHANGE authid authid BIGINT(20) UNSIGNED NOT NULL'); - $dbh->do('ALTER TABLE auth_header DROP PRIMARY KEY'); - print "2. filling in auth_header.authid... " if ($verbose); - $rows = $dbh->do("UPDATE auth_header SET authid=ExtractValue(marcxml,$xpath)"); - print "3. rebuilding primary key for auth_header.authid\n"; - $dbh->do('ALTER TABLE auth_header CHANGE authid authid BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY'); - print `date` if ($verbose); + if ($verbose) { print "Keeping system numbers of authority records\n"; } + # check existence of tag/subfield + $sqlv = "select count(*) from auth_header union select count(*) from auth_header where ExtractValue(marcxml,$xpath) != ''"; + $sthv = $dbh->prepare($sqlv); + $sthv->execute; + ($rows) = $sthv->fetchrow_array(); + my ($ctsxml) = $sthv->fetchrow_array(); # a second line can be absent + if (!defined $ctsxml) {$ctsxml=$rows} + if ($verbose) { print "$ctsxml of $rows records containing $tag\$$subfield\n"; } + $sthv->finish; + if($rows-$ctsxml !=0 || $rows+$ctsxml==0) { croak ("Tag and/or subfield $tag\$$subfield specified are not present in each record"); } + + if ($verbose) { print "1. dropping primary key for auth_header.authid\n"; } + else { print PERIOD; } + $dbh->do('ALTER TABLE auth_header CHANGE authid authid BIGINT(20) UNSIGNED NOT NULL'); + $dbh->do('ALTER TABLE auth_header DROP PRIMARY KEY'); + + if ($verbose) { print "2. filling in auth_header.authid...\n"; } + else { print PERIOD; } + $rows = $dbh->do("UPDATE auth_header SET authid=ExtractValue(marcxml,$xpath)"); + + if ($verbose) { print "3. rebuilding primary key for auth_header.authid\n"; } + else { print PERIOD . "\n"; } + $dbh->do('ALTER TABLE auth_header CHANGE authid authid BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY'); + + if ($verbose) { system 'date'; } } if ($biblio) { - print "Keeping system numbers of bibliographic records\n" if ($verbose); - # check existence of tag/subfield - $sqlv = "select count(*) from biblioitems union select count(*) from biblioitems where ExtractValue(marcxml,$xpath) != ''"; - $sthv = $dbh->prepare($sqlv); - $sthv->execute; - ($rows) = $sthv->fetchrow_array(); - my ($ctsxml) = $sthv->fetchrow_array(); # a second line can be absent - if (!defined $ctsxml) {$ctsxml=$rows} - print "$ctsxml of $rows records containing $tag\$$subfield\n" if ($verbose); - $sthv->finish; - die("Tag and/or subfield $tag\$$subfield specified are not present in each record") if($rows-$ctsxml !=0 || $rows+$ctsxml==0); - - print "adding new columns temp_id to tables biblio, biblioitems, items\n" if ($verbose); - $dbh->do('ALTER TABLE biblio add COLUMN temp_id int'); - $dbh->do('ALTER TABLE biblioitems ADD COLUMN temp_id int'); - $dbh->do('ALTER TABLE items ADD COLUMN temp_id int'); - - print "filling in biblioitems.temp_id... " if ($verbose); - $rows = $dbh->do("UPDATE biblioitems SET temp_id=ExtractValue(marcxml,$xpath)"); - print "effettuato su $rows records\n"; - print "indicizzazione di biblioitems.temp_id\n"; - $dbh->do('ALTER TABLE biblioitems ADD INDEX (temp_id)'); - - print "changing biblio.biblionumber:\n" if ($verbose); - print "1. filling in biblio.temp_id\n"; # update with join - $dbh->do('UPDATE biblio, biblioitems SET biblio.temp_id = biblioitems.temp_id WHERE biblio.biblionumber = biblioitems.biblionumber'); - print "2. dropping primary key for biblio.biblionumber\n"; - $dbh->do('ALTER TABLE biblio CHANGE biblionumber biblionumber INT(11) NOT NULL'); - $dbh->do('ALTER TABLE biblio DROP PRIMARY KEY'); - print "3. copying temp_id in biblio.biblionumber\n"; - $dbh->do('UPDATE biblio SET biblionumber=temp_id'); - print "4. dropping column biblio.temp_id\n"; - $dbh->do('ALTER TABLE biblio DROP temp_id'); - print "5. rebuilding primary key for biblio.biblionumber\n"; - $dbh->do('ALTER TABLE biblio CHANGE biblionumber biblionumber INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY'); - - print "changing items.biblionumber and items.biblioitemnumber\n" if ($verbose); - print "1. filling in items.temp_id\n"; # update with join - $dbh->do('UPDATE items, biblioitems SET items.temp_id = biblioitems.temp_id WHERE items.biblionumber = biblioitems.biblioitemnumber'); - print "2. dropping foreign key that links biblioitems to items\n" if ($verbose); - $dbh->do('ALTER TABLE items DROP FOREIGN KEY items_ibfk_1'); - print "3. copying items.temp_id to items.biblionumber and items.biblioitemnumber\n" if ($verbose); # drop and rebuild itembibnoidx and itembinoidx indices? - $dbh->do('UPDATE items SET biblionumber=temp_id, biblioitemnumber=temp_id'); - print "4. dropping column items.temp_id\n" if ($verbose); - $dbh->do('ALTER TABLE items DROP temp_id'); - - print "modifying table biblioitems:\n" if ($verbose); - print "1. dropping foreign key and primary key in biblioitems.biblioitemnumber\n"; - $dbh->do('ALTER TABLE biblioitems DROP FOREIGN KEY biblioitems_ibfk_1'); - $dbh->do('ALTER TABLE biblioitems CHANGE biblioitemnumber biblioitemnumber INT(11) NOT NULL'); - $dbh->do('ALTER TABLE biblioitems DROP PRIMARY KEY'); - print "2. copying biblioitems.temp_id to biblioitems.biblionumber e biblioitems.biblioitemnumber\n" if ($verbose); - $dbh->do('UPDATE biblioitems SET biblionumber = temp_id, biblioitemnumber = temp_id'); # drop and rebuild bibinoidx index? - print "3. rebuilding primary key and foreign key in biblioitems.biblioitemnumber\n"; - $dbh->do('ALTER TABLE biblioitems CHANGE biblioitemnumber biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY'); - $dbh->do('ALTER TABLE biblioitems ADD CONSTRAINT biblioitems_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE'); - print "4. dropping column biblioitems.temp_id and its index\n" if ($verbose); - $dbh->do('ALTER TABLE biblioitems DROP INDEX temp_id'); - $dbh->do('ALTER TABLE biblioitems DROP temp_id'); - - print `date`." changing 999c and 999d in biblioitems.marc and biblioitems.marcxml\n" if ($verbose); - my $i=0; # record counter - my $sqlu = 'UPDATE biblioitems SET marc=?, marcxml=? WHERE biblionumber=?'; - my $sthu = $dbh->prepare($sqlu); - my $sql = "SELECT biblionumber FROM biblioitems ORDER BY biblionumber"; - my $sth = $dbh->prepare($sql); - $sth->execute; - $dbh->{AutoCommit}=0; - while (my ($biblionumber) = $sth->fetchrow_array) { - my $record = GetMarcBiblio($biblionumber); - my $f999 = $record->field('999'); - $f999->update(c => $biblionumber, d => $biblionumber); - my $marc = $record->as_usmarc(); - my $marcxml = $record->as_xml_record(); - $sthu->execute($marc,$marcxml,$biblionumber); - $i++; - if ($i%50 == 0) { - print "\r$i"; - $dbh->commit(); - } - } - print "\r$i\n"; - $dbh->commit(); - $sthu->finish; - $sth->finish; - print `date`."changed 999c an 999d for $i records\n" if ($verbose); - - print "rebuilding foreign key that links biblioitems to items\n" if ($verbose); - $dbh->do('ALTER TABLE items ADD CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE'); - print `date` if ($verbose); + if ($verbose) { print "Keeping system numbers of bibliographic records\n"; } + # check existence of tag/subfield + $sqlv = "select count(*) from biblioitems union select count(*) from biblioitems where ExtractValue(marcxml,$xpath) != ''"; + $sthv = $dbh->prepare($sqlv); + $sthv->execute; + ($rows) = $sthv->fetchrow_array(); + my ($ctsxml) = $sthv->fetchrow_array(); # a second line can be absent + if (!defined $ctsxml) {$ctsxml=$rows} + if ($verbose) { print "$ctsxml of $rows records containing $tag\$$subfield\n"; } + $sthv->finish; + if($rows-$ctsxml !=0 || $rows+$ctsxml==0) { croak ("Tag and/or subfield $tag\$$subfield specified are not present in each record"); } + + if ($verbose) { print "adding new columns temp_id to tables biblio, biblioitems, items\n"; } + $dbh->do('ALTER TABLE biblio add COLUMN temp_id int'); + $dbh->do('ALTER TABLE biblioitems ADD COLUMN temp_id int'); + $dbh->do('ALTER TABLE items ADD COLUMN temp_id int'); + + if ($verbose) { print 'filling in biblioitems.temp_id... '; } + $rows = $dbh->do("UPDATE biblioitems SET temp_id=ExtractValue(marcxml,$xpath)"); + print "affected $rows records\n"; + print "indexing on biblioitems.temp_id\n"; + $dbh->do('ALTER TABLE biblioitems ADD INDEX (temp_id)'); + + if ($verbose) { print "changing biblio.biblionumber:\n"; } + print "1. filling in biblio.temp_id\n"; # update with join + $dbh->do('UPDATE biblio, biblioitems SET biblio.temp_id = biblioitems.temp_id WHERE biblio.biblionumber = biblioitems.biblionumber'); + print "2. dropping primary key for biblio.biblionumber\n"; + $dbh->do('ALTER TABLE biblio CHANGE biblionumber biblionumber INT(11) NOT NULL'); + $dbh->do('ALTER TABLE biblio DROP PRIMARY KEY'); + print "3. copying temp_id in biblio.biblionumber\n"; + $dbh->do('UPDATE biblio SET biblionumber=temp_id'); + print "4. dropping column biblio.temp_id\n"; + $dbh->do('ALTER TABLE biblio DROP temp_id'); + print "5. rebuilding primary key for biblio.biblionumber\n"; + $dbh->do('ALTER TABLE biblio CHANGE biblionumber biblionumber INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY'); + + if ($verbose) { print "changing items.biblionumber and items.biblioitemnumber\n"; } + print "1. filling in items.temp_id\n"; # update with join + $dbh->do('UPDATE items, biblioitems SET items.temp_id = biblioitems.temp_id WHERE items.biblionumber = biblioitems.biblioitemnumber'); + if ($verbose) { print "2. dropping foreign key that links biblioitems to items\n"; } + $dbh->do('ALTER TABLE items DROP FOREIGN KEY items_ibfk_1'); + if ($verbose) { print "3. copying items.temp_id to items.biblionumber and items.biblioitemnumber\n"; } + $dbh->do('UPDATE items SET biblionumber=temp_id, biblioitemnumber=temp_id'); + if ($verbose) { print "4. dropping column items.temp_id\n"; } + $dbh->do('ALTER TABLE items DROP temp_id'); + + if ($verbose) { print "modifying table biblioitems:\n"; } + print "1. dropping foreign key and primary key in biblioitems.biblioitemnumber\n"; + $dbh->do('ALTER TABLE biblioitems DROP FOREIGN KEY biblioitems_ibfk_1'); + $dbh->do('ALTER TABLE biblioitems CHANGE biblioitemnumber biblioitemnumber INT(11) NOT NULL'); + $dbh->do('ALTER TABLE biblioitems DROP PRIMARY KEY'); + if ($verbose) { print "2. copying biblioitems.temp_id to biblioitems.biblionumber e biblioitems.biblioitemnumber\n"; } + $dbh->do('UPDATE biblioitems SET biblionumber = temp_id, biblioitemnumber = temp_id'); # drop and rebuild bibinoidx index? + print "3. rebuilding primary key and foreign key in biblioitems.biblioitemnumber\n"; + $dbh->do('ALTER TABLE biblioitems CHANGE biblioitemnumber biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY'); + $dbh->do('ALTER TABLE biblioitems ADD CONSTRAINT biblioitems_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE'); + if ($verbose) { print "4. dropping column biblioitems.temp_id and its index\n"; } + $dbh->do('ALTER TABLE biblioitems DROP INDEX temp_id'); + $dbh->do('ALTER TABLE biblioitems DROP temp_id'); + + if ($verbose) { system 'echo `date` changing 999c and 999d in biblioitems.marc and biblioitems.marcxml'; } + my $i=0; # record counter + my $sqlu = 'UPDATE biblioitems SET marc=?, marcxml=? WHERE biblionumber=?'; + my $sthu = $dbh->prepare($sqlu); + my $sql = 'SELECT biblionumber FROM biblioitems ORDER BY biblionumber'; + my $sth = $dbh->prepare($sql); + $sth->execute; + $dbh->{AutoCommit}=0; + while (my ($biblionumber) = $sth->fetchrow_array) { + my $biblio_record = GetMarcBiblio($biblionumber); + my $f999 = $biblio_record->field('999'); + $f999->update(c => $biblionumber, d => $biblionumber); + my $marc = $biblio_record->as_usmarc(); + my $marcxml = $biblio_record->as_xml_record(); + $sthu->execute($marc,$marcxml,$biblionumber); + $i++; + if ( $i % $commit == 0) { + print "\r$i"; + $dbh->commit(); + } + } + print "\r$i\n"; + $dbh->commit(); + $sthu->finish; + $sth->finish; + if ($verbose) { system 'echo `date` changed 999c an 999d for ' . $i . ' records'; } + + if ($verbose) { print "rebuilding foreign key that links biblioitems to items\n"; } + $dbh->do('ALTER TABLE items ADD CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE'); + if ($verbose) { system 'date'; } } +__END__ + =head1 sysno_keeper.pl Preserve system numbers when importing MARC records into Koha. -- 2.1.4