[Koha-cvs] koha/misc/migration_tools/22_to_30 biblio_frame... [rel_3_0]

[paul poulain]

CVSROOT:        /sources/koha
Module name:    koha
Branch:         rel_3_0
Changes by:     paul poulain <tipaul>   06/11/28 10:10:02

Added files:
        misc/migration_tools/22_to_30: biblio_framework.sql 
                                       convert_to_utf8.pl 
                                       export_Authorities.pl 
                                       export_Authorities_xml.pl 
                                       missing090field.pl 
                                       move_marc_to_authheader.pl 
                                       move_marc_to_biblioitems.pl 
                                       phrase_log.sql 

Log message:
        directory cleaning : 
        * moving 
        - all tools to migrate from 2.2 to 3.0 into 
misc/migration_tools/22_to_30 directory
        - all zebra configuration files to misc/zebra/
        
        * removing previous directories :
        - migration_tools/unimarc
        - zebraplugin
        - misc/zebraauthorities
        - misc/zebra/unimarc
        - misc/zebra/usmarc
        
        * updating misc/migration_tools/rebuild_zebra.pl script
        
        I'll summarize the final result on koha-devel, the main goal being, as 
kados suggested :
        - to have all config files in a single place, and with a common naming 
convention
        - all tools scripts in a single place too. The 22_to_30 directory being 
only for migrations from 2.2 to 3.0 Scripts useful to reindex... will stay in 
misc/migration_tools

CVSWeb URLs:
http://cvs.savannah.gnu.org/viewcvs/koha/misc/migration_tools/22_to_30/biblio_framework.sql?cvsroot=koha&only_with_tag=rel_3_0&rev=1.1.2.1
http://cvs.savannah.gnu.org/viewcvs/koha/misc/migration_tools/22_to_30/convert_to_utf8.pl?cvsroot=koha&only_with_tag=rel_3_0&rev=1.1.2.1
http://cvs.savannah.gnu.org/viewcvs/koha/misc/migration_tools/22_to_30/export_Authorities.pl?cvsroot=koha&only_with_tag=rel_3_0&rev=1.1.2.1
http://cvs.savannah.gnu.org/viewcvs/koha/misc/migration_tools/22_to_30/export_Authorities_xml.pl?cvsroot=koha&only_with_tag=rel_3_0&rev=1.1.2.1
http://cvs.savannah.gnu.org/viewcvs/koha/misc/migration_tools/22_to_30/missing090field.pl?cvsroot=koha&only_with_tag=rel_3_0&rev=1.1.2.1
http://cvs.savannah.gnu.org/viewcvs/koha/misc/migration_tools/22_to_30/move_marc_to_authheader.pl?cvsroot=koha&only_with_tag=rel_3_0&rev=1.1.2.1
http://cvs.savannah.gnu.org/viewcvs/koha/misc/migration_tools/22_to_30/move_marc_to_biblioitems.pl?cvsroot=koha&only_with_tag=rel_3_0&rev=1.1.2.1
http://cvs.savannah.gnu.org/viewcvs/koha/misc/migration_tools/22_to_30/phrase_log.sql?cvsroot=koha&only_with_tag=rel_3_0&rev=1.1.2.1

Patches:
Index: biblio_framework.sql
===================================================================
RCS file: biblio_framework.sql
diff -N biblio_framework.sql
--- /dev/null   1 Jan 1970 00:00:00 -0000
+++ biblio_framework.sql        28 Nov 2006 10:10:02 -0000      1.1.2.1
@@ -0,0 +1,6 @@
+alter table biblio add frameworkcode char(4);
+update biblio,marc_biblio set biblio.frameworkcode=marc_biblio.frameworkcode 
where marc_biblio.biblionumber=biblio.biblionumber;
+alter table biblioitems add marcxml text;
+alter table biblioitems add lcsort varchar(25);
+alter table items add onloan date;
+alter table items add Cutterextra varchar(45);

Index: convert_to_utf8.pl
===================================================================
RCS file: convert_to_utf8.pl
diff -N convert_to_utf8.pl
--- /dev/null   1 Jan 1970 00:00:00 -0000
+++ convert_to_utf8.pl  28 Nov 2006 10:10:02 -0000      1.1.2.1
@@ -0,0 +1,23 @@
+#!/usr/bin/perl
+
+# small script to convert mysql tables to utf-8
+
+use C4::Context;
+use strict;
+
+my $dbh=C4::Context->dbh();
+
+my $database=C4::Context->config("database");
+my $query="Show tables";
+my $sth=$dbh->prepare($query);
+$sth->execute();
+while (my @table=$sth->fetchrow_array()){
+    print "Altering table $table[0]\n";
+    my $alter_query="ALTER TABLE $table[0] convert to CHARACTER SET UTF8 
collate utf8_general_ci";
+    my $sth2=$dbh->prepare($alter_query);
+    $sth2->execute();
+    $sth2->finish();
+
+}
+$sth->finish();
+$dbh->disconnect();

Index: export_Authorities.pl
===================================================================
RCS file: export_Authorities.pl
diff -N export_Authorities.pl
--- /dev/null   1 Jan 1970 00:00:00 -0000
+++ export_Authorities.pl       28 Nov 2006 10:10:02 -0000      1.1.2.1
@@ -0,0 +1,57 @@
+#!/usr/bin/perl
+use C4::Context;
+#use MARC::File::XML(BinaryEncoding=>"utf8");
+#use MARC::File::USMARC;
+use MARC::Record;
+use C4::AuthoritiesMarc;
+use POSIX;
+#MARC::File::XML::default_record_format("UNIMARCAUTH");
+my $dbh = C4::Context->dbh;
+my $rq= $dbh->prepare(qq|
+  SELECT authid,authtypecode
+  FROM auth_header
+  |);
+my $filename= shift @ARGV;
+$rq->execute;
+#ATTENTION : Mettre la base en utf8 auparavant.
+#BEWARE : Set database into utf8 before.
+#open FILEOUTPUT,">:utf8", "$filename" or die "unable to open $filename";
+while (my ($authid,$authtypecode)=$rq->fetchrow){
+  my $record=AUTHgetauthority($dbh,$authid);
+  if (! utf8::is_utf8($record)) {
+         utf8::decode($record);
+  }
+  
+  if (C4::Context->preference('marcflavour') eq "UNIMARC"){
+       $record->leader('     nac  22     1u 4500');
+    my $string=$1 if $time=~m/([0-9\-]+)/;
+    $string=~s/\-//g;
+     $string = sprintf("%-*s",26, $string);
+     substr($string,9,6,"frey50");
+     unless ($record->subfield('100',"a")){
+       
$record->insert_fields_ordered(MARC::Field->new('100',"","","a"=>$string));
+     }
+     if ($record->field('152')){
+       if ($record->subfield('152','b')){
+       } else {
+            $record->field('152')->add_subfields("b"=>$authtypecode);
+       }
+     } else {
+            
$record->insert_fields_ordered(MARC::Field->new('152',"","","b"=>$authtypecode));
+     }
+     unless ($record->field('001')){
+       $record->insert_fields_ordered(MARC::Field->new('001',$authid));
+     }
+     
+     AUTHmodauthority($dbh,$authid,$record,1);
+   } else {
+    $record->encoding( 'UTF-8' );
+  }
+#  warn $record->as_usmarc;
+     # warn $record->as_formatted;
+     #   warn $record->as_usmarc;
+
+  print $record->as_usmarc();
+
+}
+close ;

Index: export_Authorities_xml.pl
===================================================================
RCS file: export_Authorities_xml.pl
diff -N export_Authorities_xml.pl
--- /dev/null   1 Jan 1970 00:00:00 -0000
+++ export_Authorities_xml.pl   28 Nov 2006 10:10:02 -0000      1.1.2.1
@@ -0,0 +1,42 @@
+#!/usr/bin/perl
+use C4::Context;
+use MARC::File::XML(BinaryEncoding=>"utf8");
+use MARC::Record;
+use C4::AuthoritiesMarc;
+use POSIX;
+MARC::File::XML::default_record_format("UNIMARCAUTH");
+my $dbh = C4::Context->dbh;
+my $rq= $dbh->prepare(qq|
+  SELECT authid
+  FROM auth_header
+  |);
+my $filename= shift @ARGV;
+$rq->execute;
+#ATTENTION : Mettre la base en utf8 auparavant.
+#BEWARE : Set database into utf8 before.
+while (my ($authid)=$rq->fetchrow){
+open FILEOUTPUT,">:utf8", "./$filename/$authid.xml" or die "unable to open 
$filename";
+  my $record=AUTHgetauthority($dbh,$authid);
+  if (! utf8::is_utf8($record)) {
+    utf8::decode($record);
+  }
+                       
+#  if (C4::Context->preference('marcflavour') eq "UNIMARC"){
+       $record->leader('     nac  22     1u 4500');
+    my $string=$1 if $time=~m/([0-9\-]+)/;
+    $string=~s/\-//g;
+     $string = sprintf("%-*s",26, $string);
+     substr($string,9,6,"frey50");
+     unless ($record->subfield(100,"a")){
+       
$record->insert_fields_ordered(MARC::Field->new(100,"","","a"=>$string));
+     }
+     unless ($record->subfield('001')){
+       $record->insert_fields_ordered(MARC::Field->new('001',$authid));
+     }
+     # } else {
+#    $record->encoding( 'UTF-8' );
+#  }
+  print FILEOUTPUT $record->as_xml();
+close FILEOUPUT;
+
+}

Index: missing090field.pl
===================================================================
RCS file: missing090field.pl
diff -N missing090field.pl
--- /dev/null   1 Jan 1970 00:00:00 -0000
+++ missing090field.pl  28 Nov 2006 10:10:02 -0000      1.1.2.1
@@ -0,0 +1,51 @@
+#!/usr/bin/perl
+# This script finds and fixes missing 090 fields in Koha for MARC21
+#  Written by TG on 01/10/2005
+#  Revised by Joshua Ferraro on 03/31/2006
+use strict;
+
+# Koha modules used
+
+use C4::Context;
+use C4::Biblio;
+use MARC::Record;
+use MARC::File::USMARC;
+
+
+my $dbh = C4::Context->dbh;
+
+my $sth=$dbh->prepare("select m.biblionumber,b.biblioitemnumber from 
marc_biblio m left join biblioitems b on b.biblionumber=m.biblionumber");
+       $sth->execute();
+
+while (my ($biblionumber,$biblioitemnumber)=$sth->fetchrow ){
+ my $record = MARCgetbiblio($dbh,$biblionumber);
+    print "$biblionumber \n";  
+ 
+               MARCmodbiblionumber($biblionumber,$biblioitemnumber,$record);
+               
+}
+
+sub MARCmodbiblionumber{
+my ($biblionumber,$biblioitemnumber,$record)address@hidden;
+
+my ($tagfield,$biblionumtagsubfield) = 
&MARCfind_marc_from_kohafield($dbh,"biblio.biblionumber","");
+my ($tagfield2,$biblioitemtagsubfield) = 
&MARCfind_marc_from_kohafield($dbh,"biblio.biblioitemnumber","");
+       
+my $update=0;
+      my @tags = $record->field($tagfield);
+
+if (address@hidden){
+         
+my $newrec = MARC::Field->new( $tagfield,'','', $biblionumtagsubfield => 
$biblionumber,$biblioitemtagsubfield=>$biblioitemnumber);
+    $record->append_fields($newrec);
+ $update=1;
+       }       
+
+ 
+if ($update){  
+&MARCmodbiblio($dbh,$biblionumber,$record,'',0);
+       print "$biblionumber \n";       
+       }
+
+}
+END;

Index: move_marc_to_authheader.pl
===================================================================
RCS file: move_marc_to_authheader.pl
diff -N move_marc_to_authheader.pl
--- /dev/null   1 Jan 1970 00:00:00 -0000
+++ move_marc_to_authheader.pl  28 Nov 2006 10:10:02 -0000      1.1.2.1
@@ -0,0 +1,61 @@
+#!/usr/bin/perl
+
+# script to shift marc to biblioitems
+# scraped from updatedatabase for dev week by address@hidden
+
+use C4::Context;
+use C4::AuthoritiesMarc;
+use MARC::Record;
+use MARC::File::XML ( BinaryEncoding => 'utf8' );
+
+print "moving MARC record to marc_header table\n";
+
+my $dbh = C4::Context->dbh();
+# changing marc field type
+$dbh->do('ALTER TABLE auth_header CHANGE marc marc BLOB NULL DEFAULT NULL ');
+
+# adding marc xml, just for convenience
+$dbh->do(
+'ALTER TABLE auth_header ADD marcxml TEXT CHARACTER SET utf8 COLLATE 
utf8_general_ci NOT NULL '
+);
+
+$|=1; # flushes output
+
+# moving data from marc_subfield_value to biblio
+$sth = $dbh->prepare('select authid from auth_header');
+$sth->execute;
+my $sth_update =
+  $dbh->prepare(
+    'update auth_header set marc=?,marcxml=? where authid=?');
+my $totaldone = 0;
+while ( my ( $authid) = $sth->fetchrow ) {
+    my $record = AUTHgetauthority( $dbh, $authid );
+    $record->leader('     nac  22     1u 4500');
+    my $string=$1 if $time=~m/([0-9\-]+)/;
+    $string=~s/\-//g;
+    $string = sprintf("%-*s",26, $string);
+    substr($string,9,6,"frey50");
+    unless ($record->subfield(100,"a")){
+      $record->insert_fields_ordered(MARC::Field->new(100,"","","a"=>$string));
+    }
+    if ($record->field(152)){
+      if ($record->subfield('152','b')){
+      } else {
+        $record->field('152')->add_subfields("b"=>$authtypecode);
+      }
+    } else {
+      
$record->insert_fields_ordered(MARC::Field->new(152,"","","b"=>$authtypecode));
+    }
+    unless ($record->field('001')){
+      $record->insert_fields_ordered(MARC::Field->new('001',$authid));
+    }
+                                                                               
                                                                                
                
+
+    #Force UTF-8 in record leaded
+    $record->encoding('UTF-8');
+    $sth_update->execute( $record->as_usmarc(),$record->as_xml("UNIMARCAUTH"),
+        $authid );
+    $totaldone++;
+    print "\r$totaldone / $totaltodo" unless ( $totaldone % 100 );
+}
+print "\rdone\n";

Index: move_marc_to_biblioitems.pl
===================================================================
RCS file: move_marc_to_biblioitems.pl
diff -N move_marc_to_biblioitems.pl
--- /dev/null   1 Jan 1970 00:00:00 -0000
+++ move_marc_to_biblioitems.pl 28 Nov 2006 10:10:02 -0000      1.1.2.1
@@ -0,0 +1,38 @@
+#!/usr/bin/perl
+
+# script to shift marc to biblioitems
+# scraped from updatedatabase for dev week by address@hidden
+
+use C4::Context;
+use C4::Biblio;
+use MARC::Record;
+use MARC::File::XML ( BinaryEncoding => 'utf8' );
+
+print "moving MARC record to biblioitems table\n";
+
+my $dbh = C4::Context->dbh();
+# changing marc field type
+$dbh->do('ALTER TABLE biblioitems CHANGE marc marc BLOB NULL DEFAULT NULL ');
+
+# adding marc xml, just for convenience
+$dbh->do(
+'ALTER TABLE biblioitems ADD marcxml TEXT CHARACTER SET utf8 COLLATE 
utf8_general_ci NOT NULL '
+);
+
+# moving data from marc_subfield_value to biblio
+$sth = $dbh->prepare('select bibid,biblionumber from marc_biblio');
+$sth->execute;
+my $sth_update =
+  $dbh->prepare(
+    'update biblioitems set marcxml=?, marc=? where biblionumber=?');
+my $totaldone = 0;
+while ( my ( $bibid, $biblionumber ) = $sth->fetchrow ) {
+    my $record = MARCgetbiblio( $dbh, $bibid );
+
+    #Force UTF-8 in record leader
+    $record->encoding('UTF-8');
+    $sth_update->execute( $record->as_xml(),$record->as_usmarc(),$biblionumber 
);
+    $totaldone++;
+    print "\r$totaldone / $totaltodo" unless ( $totaldone % 100 );
+}
+print "\rdone\n";

Index: phrase_log.sql
===================================================================
RCS file: phrase_log.sql
diff -N phrase_log.sql
--- /dev/null   1 Jan 1970 00:00:00 -0000
+++ phrase_log.sql      28 Nov 2006 10:10:02 -0000      1.1.2.1
@@ -0,0 +1,49 @@
+-- MySQL Administrator dump 1.4
+--
+-- ------------------------------------------------------
+-- Server version      4.1.15-nt
+
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, 
FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+
+
+--
+-- Create schema koha
+--
+
+--
+-- Table structure for table `koha`.`phrase_log`
+--
+
+DROP TABLE IF EXISTS `phrase_log`;
+CREATE TABLE `phrase_log` (
+  `phr_phrase` varchar(100) NOT NULL default '',
+  `phr_resultcount` int(11) NOT NULL default '0',
+  `phr_ip` varchar(30) NOT NULL default '',
+  `user` varchar(45) default NULL,
+  `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
CURRENT_TIMESTAMP,
+  `actual` text NOT NULL,
+  KEY `phr_ip` (`phr_ip`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+--
+-- Dumping data for table `koha`.`phrase_log`
+--
+
+/*!40000 ALTER TABLE `phrase_log` DISABLE KEYS */;
+/*!40000 ALTER TABLE `phrase_log` ENABLE KEYS */;
+
+/*!40101 SET address@hidden */;
+/*!40014 SET address@hidden */;
+/*!40014 SET address@hidden */;
+/*!40101 SET address@hidden */;
+/*!40101 SET address@hidden */;
+/*!40101 SET address@hidden */;
+/*!40101 SET address@hidden */;