#!/usr/bin/perl -w
# create a MySQL test database
use strict;
use DBI;
my $db = shift || 'test';

my $dbh = DBI->connect("dbi:mysql:$db"
            . ";mysql_read_default_file=$ENV{HOME}/.my.cnf", undef, undef,
# replace with the following line if you don't have a config file
#       , "username", "password",
        {RaiseError => 1})
   or die "can't connect\n";

my $max_fields         = 20;
my $field_size         = 9;
my $max_records        = 100_000;
my $max_rows_per_query = 10000;   # adjust this value to max_allowed_packet
my $count              = 0;
my $text               = 'abcdefghi';
my $inserted           = 0;

$dbh->do(qq{drop table if exists testdbi});
$dbh->do(qq{create table testdbi
    ( }
    . (join ",", map {"id$_ char($field_size) not null"} (1..$max_fields))
    .qq{ , primary key (id1)
    )});

my $base_query = qq{INSERT INTO testdbi VALUES};
my $query = $base_query;

# create a multiple insertion statement
# INSERT ... VALUES (...), (...), (...) ... ;
#
for (1..$max_records) {
    $query .= ',' if $count++;
    $query .= '('
        . join( ",", map( {"'". ($text++)."'"}(1 .. $max_fields)))
        .')';
    if ($count >= $max_rows_per_query) {
        $inserted += $dbh->do($query);
        print "$inserted\n";
        $query = $base_query;
        $count = 0;
    }
}
$inserted += $dbh->do($query) if $count;
print "inserted $inserted records\n";
$dbh->disconnect;