Attachment 6527 Details for Bug 6113 – An utility to complement bulkmarcimport.pl

An utility to complement bulkmarcimport.pl

sysno_keeper.pl (text/plain), 10.86 KB, created by Stefano Bargioni on 2011-12-02 10:04:27 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Stefano Bargioni

Created: 2011-12-02 10:04:27 UTC

Size: 10.86 KB

patch

obsolete

>#!/usr/bin/perl
>
># Copyright 2000-2002 Katipo Communications
>#
># 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.
>#
># 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.
>
># 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;
>use C4::Biblio;
>use C4::Context;
>use Getopt::Long;
>use IO::File;
>use Pod::Usage;
>
>binmode(STDOUT, ":utf8");
>
>my $help     = 0;
>my $commit   = 50;
>my $biblio   = 0;
>my $auth     = 0;
>my $tag      = '001';
>my $subfield = '@';
>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,
>) 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 );
>
>my $dbh = C4::Context->dbh;
>my $rows;
>$|=1; # autoflush
>
># check MySQL version >= 5.1. Otherwise MySQL lacks the ExtractValue XML function
>my $sqlv = 'select version()';
>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');
>
># An example of $xpath: '//datafield[@tag="999"]/subfield[@code="c"]';
>my $xpath = "'//";
>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);
>
># exit; # test
>
>print `date` if ($verbose);
>
>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 ($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);
>}
>
>=head1 sysno_keeper.pl
> 
>Preserve system numbers when importing MARC records into Koha.
> 
>=head1 SYNOPSIS
> 
>sysno_keeper.pl [options]
> 
> Options:
>   -? -h --help   this help message
>   -a --auth      keep system numbers of authority records 
>                  (incompatible with -b)
>   -b --biblio    keep system numbers of bibliographic records
>                  (incompatible with -a)
>   -c --commit    number of updates before writing biblios to disk (default 50).
>                  Doesn't apply to -a.
>   -t --tag       tag containing the original record no. (default 001)
>   -s --subfield  subfield containing the original record no. (default @)
> 
>=head1 OPTIONS
> 
>=over 8
> 
>=item B<-? -h --help>
> 
>Print this message and exit.
> 
>=item B<-b --biblios>
>
>Keep system numbers of bibliographic records
>
>=item B<-a --authorities>
>
>Keep system numbers of authority records
>
>=item B<-c --commit>=I<NUMBER>
>
>The I<NUMBER> of records to wait before performing a 'commit' operation when working with bibliographic records. Default 50.
>
>=item B<-t --tag>=I<MARC TAG>
>
>The I<MARC TAG> to copy. Default 001.
>
>=item B<-s --subfield>=I<MARC SUBFIELD>
>
>The I<MARC SUBFIELD> to copy, if -t is not a controlfield. Default @.
>
>=back
> 
>=head1 DESCRIPTION
> 
>B<sysno_keeper.pl> will preserve original record numbers of bibliographic or authority records.
>This will allow record migration without loss of internal links (like 77x in MARC21), or xxx$9 links to auhority records.
>
>sysno_keeper.pl must be run immediately after bulkmarcimport.pl.
>
>For bibliographic records, the original record number is copied from -t$-s or from -t 
>to biblionumber, biblioitemnumber, 999c and 999d.
>
>For authority records, the original record number is copied from -t$-s or from -t 
>to auth_header.authid.
>
>This is a time and CPU consuming operation.
>Table auth_header is affected for -a.
>Tables biblio, biblioitems, items are affected for -b.
>
>WARNING: do not interrupt the operation. Your tables can be seriously damaged. Ensure to have a backup of your MySQL data.
>
>MySQL version 5.1 or greater is required (the ExtractValue() function is needed).
> 
>=cut

Actions: View

Attachments on bug 6113: 3845 | 6527 | 21698 | 21699 | 21700 | 21701 | 21702 | 21703 | 24073