From 06edf783e85bd398bee90b860c26054dbcf410a5 Mon Sep 17 00:00:00 2001
From: Dobrica Pavlinusic <dpavlin@rot13.org>
Date: Fri, 29 Jan 2010 19:51:41 +0100
Subject: [PATCH] iterate in 10000 rows chunks over primary table

---
 dbi2mongo.pl | 55 +++++++++++++++++++++++++++++++++++-----------------
 1 file changed, 37 insertions(+), 18 deletions(-)

diff --git a/dbi2mongo.pl b/dbi2mongo.pl
index 369e930..2c432ad 100755
--- a/dbi2mongo.pl
+++ b/dbi2mongo.pl
@@ -18,7 +18,7 @@ our ( $database, $collection ) = ( 'test', 'test' );
 our ( $table,  $pk ) = ( 'biblio'      => 'biblionumber' );
 our ( $table2, $fk ) = ( 'biblioitems' => 'biblionumber' );
 
-my $limit      = 50000;
+my $limit      = 10000;
 my $join_limit = 10000;
 
 require 'config.pl';
@@ -42,19 +42,38 @@ my $last_id = $last->{_id} || 0;
 
 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 $limit
-});
+our $offset = 0;
+our $sth;
+
+sub select_table {
+
+	$sth = $dbh->prepare(qq{
+		select
+			$pk as _id,
+			$table.*
+		from $table
+		where $pk > ?
+		order by $pk asc
+		limit $limit
+		offset $offset
+	});
 
-$sth->execute( $last_id );
-warn "# $table columns ",dump( $sth->{NAME} );
-print "import ",$sth->rows," from $table\n";
+	print STDERR " $table:$offset ";
+	$sth->execute( $last_id );
+#	warn "# $table columns ",dump( $sth->{NAME} ) if $offset == 0;
+	print STDERR " join ", $sth->rows, " rows ";
+}
+
+our $row;
+sub fetch_row {
+	$row = $sth->fetchrow_hashref();
+	if ( ! $row && $sth->rows == $limit ) {
+		$offset += $limit;
+		select_table;
+		$row = $sth->fetchrow_hashref();
+	}
+	return $row;
+}
 
 our $join_offset = 0;
 our $sth_join;
@@ -69,14 +88,13 @@ sub join_table {
  		limit $join_limit
 		offset $join_offset
 	});
-	print STDERR "$join_offset";
+	print STDERR " $table2:$join_offset ";
 	$sth_join->execute( $last_id );
-	warn "# $table2 columns ",dump( $sth_join->{NAME} );
-	print "join ",$sth_join->rows," from $table2 offset $join_offset limit $join_limit\n";
+#	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 == $join_limit ) {
@@ -93,10 +111,11 @@ sub guess_types {
 	return $row;
 }
 
+select_table;
 join_table;
 fetch_row_join;
 
-while (my $row = $sth->fetchrow_hashref() ) {
+while (my $row = fetch_row() ) {
 
 	while ( $row_join && $row_join->{$fk} < $row->{$pk} ) {
 		fetch_row_join;
-- 
2.20.1