#!/usr/bin/perl -w
sub BEGIN {
-$ENV{DBI_AUTOPROXY}='dbi:Gofer:transport=stream;url=ssh:dpavlin@koha.ffzg.hr';
+#$ENV{DBI_AUTOPROXY}='dbi:Gofer:transport=stream;url=ssh:dpavlin@koha.ffzg.hr';
}
use strict;
use DBI;
use MongoDB;
use Data::Dump qw/dump/;
+use Getopt::Long;
-$|++;
+my $debug = 0;
+my $drop = 0;
+my $limit = 10000;
-my $debug = @ARGV ? 1 : 0;
+GetOptions(
+ 'debug!' => \$debug,
+ 'drop!' => \$drop,
+ 'limit=i' => \$limit,
+) || die $!;
our ( $dbi, $user, $password ) = ( "DBI:mysql:database=test" );
-our ( $database, $collection ) = ( 'test', 'test' );
-our ( $table, $pk ) = ( 'items', 'id' );
+our ( $database, $collection ) = ( '', '' );
+our ( $table, $pk ) = ( 'biblio' => 'biblionumber' );
+our ( $table2, $fk ) = ( 'biblioitems' => 'biblionumber' );
-require 'config.pl';
+my $config = shift @ARGV || die "usage: $0 config.pl\n";
+require $config;
-warn "# $dbi $user -> $database $collection\n";
+warn "# $dbi $user -> $database $collection $table.$pk<->$table2.$fk\n";
my $conn = MongoDB::Connection->new;
my $db = $conn->get_database( $database );
# mysql_enable_utf8 => 1,
});
-$db->drop if $debug;
+$db->drop if $drop;
# db.items.find().sort({_id:-1}).limit(1);
my $last = $coll->query()->sort({ '_id' => -1 })->limit(1)->next;
warn dump( $last );
my $last_id = $last->{_id} || 0;
-print "Fetching items from $dbi _id > $last_id\n";
+print "import $table.$pk > $last_id from $dbi\n";
-my $sth = $dbh->prepare(qq{
- select
- $pk as _id,
- $table.*
- from $table
- where $pk > ?
- order by $pk asc
- limit 100000
-});
+our $offset = 0;
+our $sth;
+
+sub select_table {
-$sth->execute( $last_id );
+ $sth = $dbh->prepare(qq{
+ select
+ $pk as _id,
+ $table.*
+ from $table
+ where $pk > ?
+ order by $pk asc
+ limit $limit
+ offset $offset
+ });
-warn dump( $sth->{NAME} );
+ print STDERR " $table:$offset ";
+ $sth->execute( $last_id );
+# warn "# $table columns ",dump( $sth->{NAME} ) if $offset == 0;
+ print STDERR " join ", $sth->rows, " rows ";
+}
-print "found ",$sth->rows," items to process...\n";
+our $row;
+sub fetch_row {
+ $row = $sth->fetchrow_hashref();
+ if ( ! $row && $sth->rows == $limit ) {
+ $offset += $limit;
+ select_table;
+ $row = $sth->fetchrow_hashref();
+ }
+ return $row;
+}
-while (my $row = $sth->fetchrow_hashref() ) {
+our $join_offset = 0;
+our $sth_join;
+
+sub join_table {
+ $sth_join = $dbh->prepare(qq{
+ select
+ $table2.*
+ from $table2
+ where $fk > ?
+ order by $fk asc
+ limit $limit
+ offset $join_offset
+ });
+ print STDERR " $table2:$join_offset ";
+ $sth_join->execute( $last_id );
+# warn "# $table2 columns ",dump( $sth_join->{NAME} ) if $join_offset = 0;
+ print STDERR " join ",$sth_join->rows, " rows ";
+}
+
+our $row_join;
+sub fetch_row_join {
+ $row_join = $sth_join->fetchrow_hashref();
+ if ( ! $row_join && $sth_join->rows == $limit ) {
+ $join_offset += $limit;
+ join_table;
+ $row_join = $sth_join->fetchrow_hashref();
+ }
+ return $row_join;
+}
+sub guess_types {
+ my $row = shift;
map { $row->{$_} * 1 } grep { defined $row->{$_} && $row->{$_} =~ /^\d+$/ } keys %$row;
- $coll->insert( $row );
+ return $row;
+}
+
+select_table;
+join_table;
+fetch_row_join;
+
+while (my $row = fetch_row() ) {
+
+ while ( $row_join && $row_join->{$fk} < $row->{$pk} ) {
+ fetch_row_join;
+ }
+
+ while ( $row_join && $row_join->{$fk} == $row->{$pk} ) {
+ push @{ $row->{ $table2 } }, guess_types($row_join);
+ print STDERR "j";
+ fetch_row_join;
+ }
+
+ $coll->insert( guess_types($row) );
+ print STDERR ".";
+
}
+print STDERR "\n";