#!/usr/bin/env perl
## Usage: perl create.pl [ n_rows ]
use strict;
use warnings;
use Cwd 'abs_path'; ## Insert lib-path at the head of @INC.
use lib abs_path($0 =~ m{^(.*)[\\/]} && $1 || abs_path) . '/../../lib';
my ($prog_name, $prog_dir, $db_file);
BEGIN {
$prog_name = $0; $prog_name =~ s{^.*[\\/]}{}g;
$prog_dir = abs_path($0); $prog_dir =~ s{[\\/][^\\/]*$}{};
$db_file = "$prog_dir/SAMPLE.DB";
}
use Time::HiRes 'time';
use DBI;
my $start = time;
###############################################################################
my $n_rows = shift || 3000;
my $batch_size = 3000;
my $batch_i = $batch_size;
unless ($n_rows =~ /\A\d+\z/) {
die "usage: perl $prog_name [ n_rows ]\n";
}
if (-e $db_file) {
unlink $db_file or die "Cannot unlink $db_file: $!\n";
}
my ($dsn, $user, $password) = ("dbi:SQLite:dbname=$db_file", "", "");
my $dbh = DBI->connect($dsn, $user, $password, {
PrintError => 0, RaiseError => 1, AutoCommit => 1,
FetchHashKeyName => 'NAME_lc',
}) or die $DBI::errstr;
exit($?) unless -e $db_file;
$dbh->do('PRAGMA page_size = 4096');
###############################################################################
my ($sql, $sth);
$sql = <<'END_SQL';
CREATE TABLE seq (
seq_id INTEGER PRIMARY KEY,
value1 INTEGER,
value2 REAL
)
END_SQL
$dbh->do($sql);
$sth = $dbh->prepare(
"INSERT INTO seq (seq_id, value1, value2) VALUES (?, ?, ?)"
);
$dbh->begin_work; ## $dbh->do('BEGIN');
for my $seq_id (1 .. $n_rows) {
$sth->execute($seq_id, $seq_id * 2, $seq_id * 1.618);
unless (--$batch_i) {
$dbh->commit; ## $dbh->do('COMMIT');
$dbh->begin_work; ## $dbh->do('BEGIN');
$batch_i = $batch_size;
}
}
$dbh->commit; ## $dbh->do('COMMIT');
$sth->finish;
$dbh->disconnect;
printf {*STDERR} "\n## Compute time: %0.03f\n\n", time - $start;