#!/usr/bin/perl -Twl
# MySQL test
# 20040816 altblue@n0i.net
use strict;
use DBI ();
use Benchmark qw(:all);
use Carp;
use vars qw($dbh $t0 $t1);
$| = 1;

my @DB = (
 'DBI:mysql:database=test;host=db',
 'test', '',
 {
   PrintError => 1,
   RaiseError => 1,
   ShowErrorStatement => 1,
   AutoCommit => 0,
 }
);

my @TABLES = qw(mituc_test_1 mituc_test_2);
my $RECORDS = 1_000_000;

my $TABLE_CREATE = q{
	CREATE TABLE IF NOT EXISTS %s (
		client_id mediumint(8) unsigned not null,
		ip int(10) unsigned not null,
		hour tinyint(1) unsigned not null,
		peerip int(4) unsigned not null,
		srcdst tinyint(1) not null,
		bytes int(10) unsigned not null,
		pkts mediumint(8) unsigned not null,
		interface_id smallint(5) unsigned not null,
		KEY (client_id),
		KEY (ip),
		KEY (hour),
		KEY (peerip),
		KEY (srcdst),
		KEY (interface_id),
		KEY idhour (client_id, hour)
	) TYPE=MyISAM
};

my $INSERT = q{
	INSERT INTO %s
	(client_id, ip, hour, peerip, srcdst, bytes, pkts, interface_id)
	VALUES (?, ?, ?, ?, ?, ?, ?, ?)
};
my $NOFIELDS = 8;


my %TESTS = (
	'Bytes per SRCDST' => q{
		SELECT 
			a.client_id, a.srcdst, SUM(a.bytes) - SUM(b.bytes)
		FROM mituc_test_1 a, mituc_test_2 b
		WHERE
			a.client_id = b.client_id
			AND a.srcdst = b.srcdst
		GROUP BY
			a.client_id, b.srcdst
	}
);

### END SETUP

$t0 = new Benchmark;
$dbh = DBI->connect(@DB) || confess $DBI::errstr;

sub vprint {
	$t1 = new Benchmark;
	print STDERR timestr(timediff($t1, $t0), 'noc'), " @_";
	$t0 = $t1;
}

sub count {
	$dbh->selectcol_arrayref('SELECT COUNT(*) FROM ' . shift)->[0];
}

foreach (@TABLES) {
	vprint("Creating table '$_'");
	(my $create = $TABLE_CREATE) =~ s/\Q%s/$_/;
	$dbh->do($create) or confess $dbh->errstr;

	vprint("Inserting $RECORDS random records into table '$_'");
	(my $insert = $INSERT) =~ s/\Q%s/$_/;
	my $sth = $dbh->prepare($insert) or confess $dbh->errstr;

	foreach (1 .. $RECORDS) {
		$sth->execute( map { rand(1_000_000) } (1 .. $NOFIELDS));
		printf STDERR "\033[14D%14d", $_ unless $_ % 100;
#		$dbh->commit unless $_ % 5000;
	}
	$dbh->commit;
	printf STDERR "\033[14D";
	vprint('done. There are ' . count($_) . ' records now in ' . $_);
}

while (my ($name, $query) = each %TESTS) {
	vprint ("Preparing test '$name'");
	my $sth = $dbh->prepare($query) or confess $dbh->errstr;
	vprint ("Executing test '$name'");
	$sth->execute;
	vprint ("Counting rows for test '$name'");
	my $count = 0;
	$count++ while my $ar = $sth->fetchrow_arrayref;
	vprint ("Test '$name' done. $count rows returned.");
}