|
Line 0
Link Here
|
| 0 |
- |
1 |
#!/usr/bin/perl |
|
|
2 |
|
| 3 |
# Copyright 2011 Stefano Bargioni, Pontificia Università della Santa Croce - Rome |
| 4 |
# |
| 5 |
# This file is part of Koha. |
| 6 |
# |
| 7 |
# Koha is free software; you can redistribute it and/or modify it under the |
| 8 |
# terms of the GNU General Public License as published by the Free Software |
| 9 |
# Foundation; either version 2 of the License, or (at your option) any later |
| 10 |
# version. |
| 11 |
# |
| 12 |
# Koha is distributed in the hope that it will be useful, but WITHOUT ANY |
| 13 |
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR |
| 14 |
# A PARTICULAR PURPOSE. See the GNU General Public License for more details. |
| 15 |
# |
| 16 |
# You should have received a copy of the GNU General Public License along |
| 17 |
# with Koha; if not, write to the Free Software Foundation, Inc., |
| 18 |
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. |
| 19 |
|
| 20 |
# Written by Stefano Bargioni Pontificia Università della Santa Croce - Rome |
| 21 |
# bargioni@pusc.it 2011-11-07 |
| 22 |
|
| 23 |
use strict; |
| 24 |
use warnings; |
| 25 |
use MARC::Record; |
| 26 |
use MARC::File::XML; |
| 27 |
use MARC::Field; |
| 28 |
use C4::Biblio; |
| 29 |
use C4::Context; |
| 30 |
use Getopt::Long; |
| 31 |
use IO::File; |
| 32 |
use Pod::Usage; |
| 33 |
|
| 34 |
binmode(STDOUT, ":utf8"); |
| 35 |
|
| 36 |
my $help = 0; |
| 37 |
my $commit = 50; |
| 38 |
my $biblio = 0; |
| 39 |
my $auth = 0; |
| 40 |
my $tag = '001'; |
| 41 |
my $subfield = '@'; |
| 42 |
my $verbose = 0; |
| 43 |
|
| 44 |
GetOptions( |
| 45 |
'help|?|h' => \$help, |
| 46 |
'a|authorities:s' => \$auth, |
| 47 |
'b|biblios:s' => \$biblio, |
| 48 |
'c|commit:n' => \$commit, |
| 49 |
't|tag:s' => \$tag, |
| 50 |
's|subfield:s' => \$subfield, |
| 51 |
'v|verbose' => \$verbose, |
| 52 |
) or pod2usage(2); |
| 53 |
pod2usage( -exitstatus => 0, -verbose => 2 ) if $help; |
| 54 |
|
| 55 |
die('No record type specified') if ( !$biblio && !$auth ); |
| 56 |
die('You cannot specify both record types') if ( $biblio && $auth ); |
| 57 |
die("Invalid MARC tag $tag") if ( length($tag) != 3 ); |
| 58 |
die("Invalid subfield $subfield") if ( length($subfield) != 1 ); |
| 59 |
die('Invalid commit value') if ( $commit < 1 ); |
| 60 |
|
| 61 |
my $dbh = C4::Context->dbh; |
| 62 |
my $rows; |
| 63 |
$|=1; # autoflush |
| 64 |
|
| 65 |
# check MySQL version >= 5.1. Otherwise MySQL lacks the ExtractValue XML function |
| 66 |
my $sqlv = 'select version()'; |
| 67 |
my $sthv = $dbh->prepare($sqlv); |
| 68 |
$sthv->execute; |
| 69 |
my ($mysql_version) = $sthv->fetchrow_array(); |
| 70 |
$sthv->finish; |
| 71 |
die('Your MySQL version $mysql_version is less than 5.1') if ($mysql_version lt '5.1'); |
| 72 |
|
| 73 |
# An example of $xpath: '//datafield[@tag="999"]/subfield[@code="c"]'; |
| 74 |
my $xpath = "'//"; |
| 75 |
if ($tag gt '009') {$xpath .= 'datafield'} else {$xpath .= 'controlfield'}; |
| 76 |
$xpath .= '[@tag="'.$tag.'"]'; |
| 77 |
$xpath .= "/subfield[\@code=\"$subfield\"]" if ($subfield ne '@'); |
| 78 |
$xpath .= "'"; |
| 79 |
print 'using xpath '.$xpath."\n" if ($verbose); |
| 80 |
|
| 81 |
# exit; # test |
| 82 |
|
| 83 |
print `date` if ($verbose); |
| 84 |
|
| 85 |
if ($auth) { |
| 86 |
print "Keeping system numbers of authority records\n" if ($verbose); |
| 87 |
# check existence of tag/subfield |
| 88 |
$sqlv = "select count(*) from auth_header union select count(*) from auth_header where ExtractValue(marcxml,$xpath) != ''"; |
| 89 |
$sthv = $dbh->prepare($sqlv); |
| 90 |
$sthv->execute; |
| 91 |
($rows) = $sthv->fetchrow_array(); |
| 92 |
my ($ctsxml) = $sthv->fetchrow_array(); # a second line can be absent |
| 93 |
if (!defined $ctsxml) {$ctsxml=$rows} |
| 94 |
print "$ctsxml of $rows records containing $tag\$$subfield\n" if ($verbose); |
| 95 |
$sthv->finish; |
| 96 |
die("Tag and/or subfield $tag\$$subfield specified are not present in each record") if($rows-$ctsxml !=0 || $rows+$ctsxml==0); |
| 97 |
|
| 98 |
print "1. dropping primary key for auth_header.authid\n" if ($verbose); |
| 99 |
$dbh->do('ALTER TABLE auth_header CHANGE authid authid BIGINT(20) UNSIGNED NOT NULL'); |
| 100 |
$dbh->do('ALTER TABLE auth_header DROP PRIMARY KEY'); |
| 101 |
print "2. filling in auth_header.authid... " if ($verbose); |
| 102 |
$rows = $dbh->do("UPDATE auth_header SET authid=ExtractValue(marcxml,$xpath)"); |
| 103 |
print "3. rebuilding primary key for auth_header.authid\n"; |
| 104 |
$dbh->do('ALTER TABLE auth_header CHANGE authid authid BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY'); |
| 105 |
print `date` if ($verbose); |
| 106 |
} |
| 107 |
|
| 108 |
if ($biblio) { |
| 109 |
print "Keeping system numbers of bibliographic records\n" if ($verbose); |
| 110 |
# check existence of tag/subfield |
| 111 |
$sqlv = "select count(*) from biblioitems union select count(*) from biblioitems where ExtractValue(marcxml,$xpath) != ''"; |
| 112 |
$sthv = $dbh->prepare($sqlv); |
| 113 |
$sthv->execute; |
| 114 |
($rows) = $sthv->fetchrow_array(); |
| 115 |
my ($ctsxml) = $sthv->fetchrow_array(); # a second line can be absent |
| 116 |
if (!defined $ctsxml) {$ctsxml=$rows} |
| 117 |
print "$ctsxml of $rows records containing $tag\$$subfield\n" if ($verbose); |
| 118 |
$sthv->finish; |
| 119 |
die("Tag and/or subfield $tag\$$subfield specified are not present in each record") if($rows-$ctsxml !=0 || $rows+$ctsxml==0); |
| 120 |
|
| 121 |
print "adding new columns temp_id to tables biblio, biblioitems, items\n" if ($verbose); |
| 122 |
$dbh->do('ALTER TABLE biblio add COLUMN temp_id int'); |
| 123 |
$dbh->do('ALTER TABLE biblioitems ADD COLUMN temp_id int'); |
| 124 |
$dbh->do('ALTER TABLE items ADD COLUMN temp_id int'); |
| 125 |
|
| 126 |
print "filling in biblioitems.temp_id... " if ($verbose); |
| 127 |
$rows = $dbh->do("UPDATE biblioitems SET temp_id=ExtractValue(marcxml,$xpath)"); |
| 128 |
print "effettuato su $rows records\n"; |
| 129 |
print "indicizzazione di biblioitems.temp_id\n"; |
| 130 |
$dbh->do('ALTER TABLE biblioitems ADD INDEX (temp_id)'); |
| 131 |
|
| 132 |
print "changing biblio.biblionumber:\n" if ($verbose); |
| 133 |
print "1. filling in biblio.temp_id\n"; # update with join |
| 134 |
$dbh->do('UPDATE biblio, biblioitems SET biblio.temp_id = biblioitems.temp_id WHERE biblio.biblionumber = biblioitems.biblionumber'); |
| 135 |
print "2. dropping primary key for biblio.biblionumber\n"; |
| 136 |
$dbh->do('ALTER TABLE biblio CHANGE biblionumber biblionumber INT(11) NOT NULL'); |
| 137 |
$dbh->do('ALTER TABLE biblio DROP PRIMARY KEY'); |
| 138 |
print "3. copying temp_id in biblio.biblionumber\n"; |
| 139 |
$dbh->do('UPDATE biblio SET biblionumber=temp_id'); |
| 140 |
print "4. dropping column biblio.temp_id\n"; |
| 141 |
$dbh->do('ALTER TABLE biblio DROP temp_id'); |
| 142 |
print "5. rebuilding primary key for biblio.biblionumber\n"; |
| 143 |
$dbh->do('ALTER TABLE biblio CHANGE biblionumber biblionumber INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY'); |
| 144 |
|
| 145 |
print "changing items.biblionumber and items.biblioitemnumber\n" if ($verbose); |
| 146 |
print "1. filling in items.temp_id\n"; # update with join |
| 147 |
$dbh->do('UPDATE items, biblioitems SET items.temp_id = biblioitems.temp_id WHERE items.biblionumber = biblioitems.biblioitemnumber'); |
| 148 |
print "2. dropping foreign key that links biblioitems to items\n" if ($verbose); |
| 149 |
$dbh->do('ALTER TABLE items DROP FOREIGN KEY items_ibfk_1'); |
| 150 |
print "3. copying items.temp_id to items.biblionumber and items.biblioitemnumber\n" if ($verbose); # drop and rebuild itembibnoidx and itembinoidx indices? |
| 151 |
$dbh->do('UPDATE items SET biblionumber=temp_id, biblioitemnumber=temp_id'); |
| 152 |
print "4. dropping column items.temp_id\n" if ($verbose); |
| 153 |
$dbh->do('ALTER TABLE items DROP temp_id'); |
| 154 |
|
| 155 |
print "modifying table biblioitems:\n" if ($verbose); |
| 156 |
print "1. dropping foreign key and primary key in biblioitems.biblioitemnumber\n"; |
| 157 |
$dbh->do('ALTER TABLE biblioitems DROP FOREIGN KEY biblioitems_ibfk_1'); |
| 158 |
$dbh->do('ALTER TABLE biblioitems CHANGE biblioitemnumber biblioitemnumber INT(11) NOT NULL'); |
| 159 |
$dbh->do('ALTER TABLE biblioitems DROP PRIMARY KEY'); |
| 160 |
print "2. copying biblioitems.temp_id to biblioitems.biblionumber e biblioitems.biblioitemnumber\n" if ($verbose); |
| 161 |
$dbh->do('UPDATE biblioitems SET biblionumber = temp_id, biblioitemnumber = temp_id'); # drop and rebuild bibinoidx index? |
| 162 |
print "3. rebuilding primary key and foreign key in biblioitems.biblioitemnumber\n"; |
| 163 |
$dbh->do('ALTER TABLE biblioitems CHANGE biblioitemnumber biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY'); |
| 164 |
$dbh->do('ALTER TABLE biblioitems ADD CONSTRAINT biblioitems_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE'); |
| 165 |
print "4. dropping column biblioitems.temp_id and its index\n" if ($verbose); |
| 166 |
$dbh->do('ALTER TABLE biblioitems DROP INDEX temp_id'); |
| 167 |
$dbh->do('ALTER TABLE biblioitems DROP temp_id'); |
| 168 |
|
| 169 |
print `date`." changing 999c and 999d in biblioitems.marc and biblioitems.marcxml\n" if ($verbose); |
| 170 |
my $i=0; # record counter |
| 171 |
my $sqlu = 'UPDATE biblioitems SET marc=?, marcxml=? WHERE biblionumber=?'; |
| 172 |
my $sthu = $dbh->prepare($sqlu); |
| 173 |
my $sql = "SELECT biblionumber FROM biblioitems ORDER BY biblionumber"; |
| 174 |
my $sth = $dbh->prepare($sql); |
| 175 |
$sth->execute; |
| 176 |
$dbh->{AutoCommit}=0; |
| 177 |
while (my ($biblionumber) = $sth->fetchrow_array) { |
| 178 |
my $record = GetMarcBiblio($biblionumber); |
| 179 |
my $f999 = $record->field('999'); |
| 180 |
$f999->update(c => $biblionumber, d => $biblionumber); |
| 181 |
my $marc = $record->as_usmarc(); |
| 182 |
my $marcxml = $record->as_xml_record(); |
| 183 |
$sthu->execute($marc,$marcxml,$biblionumber); |
| 184 |
$i++; |
| 185 |
if ($i%50 == 0) { |
| 186 |
print "\r$i"; |
| 187 |
$dbh->commit(); |
| 188 |
} |
| 189 |
} |
| 190 |
print "\r$i\n"; |
| 191 |
$dbh->commit(); |
| 192 |
$sthu->finish; |
| 193 |
$sth->finish; |
| 194 |
print `date`."changed 999c an 999d for $i records\n" if ($verbose); |
| 195 |
|
| 196 |
print "rebuilding foreign key that links biblioitems to items\n" if ($verbose); |
| 197 |
$dbh->do('ALTER TABLE items ADD CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE'); |
| 198 |
print `date` if ($verbose); |
| 199 |
} |
| 200 |
|
| 201 |
=head1 sysno_keeper.pl |
| 202 |
|
| 203 |
Preserve system numbers when importing MARC records into Koha. |
| 204 |
|
| 205 |
=head1 SYNOPSIS |
| 206 |
|
| 207 |
sysno_keeper.pl [options] |
| 208 |
|
| 209 |
Options: |
| 210 |
-? -h --help this help message |
| 211 |
-a --auth keep system numbers of authority records |
| 212 |
(incompatible with -b) |
| 213 |
-b --biblio keep system numbers of bibliographic records |
| 214 |
(incompatible with -a) |
| 215 |
-c --commit number of updates before writing biblios to disk (default 50). |
| 216 |
Doesn't apply to -a. |
| 217 |
-t --tag tag containing the original record no. (default 001) |
| 218 |
-s --subfield subfield containing the original record no. (default @) |
| 219 |
|
| 220 |
=head1 OPTIONS |
| 221 |
|
| 222 |
=over 8 |
| 223 |
|
| 224 |
=item B<-? -h --help> |
| 225 |
|
| 226 |
Print this message and exit. |
| 227 |
|
| 228 |
=item B<-b --biblios> |
| 229 |
|
| 230 |
Keep system numbers of bibliographic records |
| 231 |
|
| 232 |
=item B<-a --authorities> |
| 233 |
|
| 234 |
Keep system numbers of authority records |
| 235 |
|
| 236 |
=item B<-c --commit>=I<NUMBER> |
| 237 |
|
| 238 |
The I<NUMBER> of records to wait before performing a 'commit' operation when working with bibliographic records. Default 50. |
| 239 |
|
| 240 |
=item B<-t --tag>=I<MARC TAG> |
| 241 |
|
| 242 |
The I<MARC TAG> to copy. Default 001. |
| 243 |
|
| 244 |
=item B<-s --subfield>=I<MARC SUBFIELD> |
| 245 |
|
| 246 |
The I<MARC SUBFIELD> to copy, if -t is not a controlfield. Default @. |
| 247 |
|
| 248 |
=back |
| 249 |
|
| 250 |
=head1 DESCRIPTION |
| 251 |
|
| 252 |
B<sysno_keeper.pl> will preserve original record numbers of bibliographic or authority records. |
| 253 |
This will allow record migration without loss of internal links (like 77x in MARC21), or xxx$9 links to auhority records. |
| 254 |
|
| 255 |
sysno_keeper.pl must be run immediately after bulkmarcimport.pl. |
| 256 |
|
| 257 |
For bibliographic records, the original record number is copied from -t$-s or from -t |
| 258 |
to biblionumber, biblioitemnumber, 999c and 999d. |
| 259 |
|
| 260 |
For authority records, the original record number is copied from -t$-s or from -t |
| 261 |
to auth_header.authid. |
| 262 |
|
| 263 |
This is a time and CPU consuming operation. |
| 264 |
Table auth_header is affected for -a. |
| 265 |
Tables biblio, biblioitems, items are affected for -b. |
| 266 |
|
| 267 |
WARNING: do not interrupt the operation. Your tables can be seriously damaged. Ensure to have a backup of your MySQL data. |
| 268 |
|
| 269 |
MySQL version 5.1 or greater is required (the ExtractValue() function is needed). |
| 270 |
|
| 271 |
=cut |