#!/usr/bin/perl -w

# This code available under a BSD 3-clause license.
# http://opensource.org/licenses/BSD-3-Clause

use strict;
use DBI;
use Time::HiRes qw[ gettimeofday tv_interval ];

my $dbh_local = DBI->connect("dbi:SQLite:db=:memory:", "", "", {
		AutoCommit	=> 1,
		RaiseError	=> 1
	}
);
defined $dbh_local or die "db connect failed";
$dbh_local->begin_work();

$dbh_local->do(qq[ CREATE TABLE phrase (id INTEGER PRIMARY KEY, phrase TEXT) ]);

# Insert 500 rows of a common phrase:
my $phrase = "The quick red fox jumps over the lazy brown dog!";
my $insert = $dbh_local->prepare(qq[ INSERT INTO phrase (phrase) VALUES (?) ]);
$insert->execute($phrase) for (1..500);
$dbh_local->commit();

# Start the SELECT query from the local DB:
my $select = $dbh_local->prepare(qq[ SELECT phrase FROM phrase ]);
$select->execute();

# This time, put all the rows we get into a single array:
my @bulk_values = ();
while (my $row = $select->fetchrow_arrayref()) {
	push @bulk_values, $row->[0];
}
# Always check if you have values to insert. This is a very simple example:
($#bulk_values >= 0) or die "Whopsie, nothing to insert!";

# Connect to the remote database
my $dbh_remote = DBI->connect("dbi:Pg:host=172.21.2.10;db=lab", "labUser", "labPass", {
		AutoCommit	=> 0,
		RaiseError	=> 1
	}
);
defined $dbh_remote or die "db connect failed";

# Now prepare the "values" clause..
my $sql_values = "(?)";
# .. and add a row for each row in the prepared @bulk_values:
$sql_values .= ",(?)" for (1 .. $#bulk_values);

# Now prepare the real query, adding in this clause:
$insert = $dbh_remote->prepare(qq[ INSERT INTO phrase (phrase) values $sql_values ]);

# START TIMER, then do the inserts.
my @timer = gettimeofday();

$insert->execute(@bulk_values);

$dbh_remote->commit();

# REPORT TIME, now that the inserts are done
printf "Total time to insert was: %.2fs\n", tv_interval(\@timer);