7 use Data::Dump qw(dump);
9 our $koha_dsn = 'dbi:oursql:dbname=koha';
10 our $koha_user = 'kohaadmin';
11 our $koha_passwd = '';
13 our $geo_dsn = 'dbi:Pg:dbname=koha';
17 our $opts = { RaiseError => 1 }; #, AutoCommit => 0, pg_enable_utf8 => 1, oursql_enable_utf8 => 1 };
19 require '/srv/koha-config.pl';
21 my $k_dbh = DBI->connect($koha_dsn, $koha_user, $koha_passwd, $opts) || die $DBI::errstr;
22 my $g_dbh = DBI->connect($geo_dsn, $geo_user, $geo_passwd, $opts) || die $DBI::errstr;
25 my ( $table, $pk ) = @_;
27 warn "# clean $table";
28 $g_dbh->do( "delete from $table" );
32 my $sql = "select * from $table order by $pk";
34 warn "# import $table";
38 my $sth = $k_dbh->prepare( "$sql limit 1000 offset $offset" );
39 print STDERR "$table [$offset] ";
42 $offset = 0 if ! $sth->rows; # exit
44 my @cols = @{ $sth->{NAME_lc} };
45 my $sql_insert = "insert into $table (" . join(',',@cols) . ") values (" . join(',', map { '?' } @cols ) . ")";
46 my $sth_insert = $g_dbh->prepare( $sql_insert );
48 while( my $row = $sth->fetchrow_arrayref ) {
49 eval { $sth_insert->execute( @$row ) };
51 print STDERR "$offset " if $offset % 100 == 0;
59 #fetch_table 'biblio' => 'biblionumber' ;
60 #fetch_table 'biblioitems' => 'biblioitemnumber' ;
62 warn "# drop geo_biblioitems";
63 eval { $g_dbh->do(qq{ drop table geo_biblioitems }) };
65 warn "# create geo_biblioitems";
67 create table geo_biblioitems (
68 biblioitemnumber integer not null references biblioitems(biblioitemnumber),
69 biblionumber integer not null references biblio(biblionumber),
73 my $sth_insert = $g_dbh->prepare(qq{
74 insert into geo_biblioitems values (?,?,?)
77 warn "# select bibiloitems";
78 my $sth = $g_dbh->prepare(qq{
80 biblioitemnumber, biblionumber, isbn, issn, marcxml
85 warn $sth->rows, " rows\n";
88 warn dump @_,$/ if $ENV{DEBUG};
93 while ( my $row = $sth->fetchrow_hashref ) {
94 my $xml = XMLin( delete $row->{marcxml}, ForceArray => [ 'datafield', 'subfield' ] );
96 warn_dump($row, $xml);
98 my @tag_260 = grep { $_->{tag} eq '260' } @{ $xml->{datafield} };
100 next unless @tag_260;
102 warn_dump @tag_260 if $ENV{DEBUG};
104 foreach my $sf ( @{ $tag_260[0]->{subfield} } ) {
105 $row->{ 'tag_260_' . $sf->{code} } = $sf->{content};
108 $row->{city} = $row->{tag_260_a};
109 $row->{city} =~ s/['"]+//g;
110 $row->{city} =~ s/\s*\[etc.*\].*$//;
111 $row->{city} =~ s/\s*[:]\s*$//;
112 $row->{city} =~ s/[\[\]]+//g;
116 warn "# $i ", $row->{city}, $/ if $i++ % 100 == 0;
118 $sth_insert->execute( $row->{biblioitemnumber}, $row->{biblionumber}, $row->{city} );