#!/usr/bin/perl

use warnings;
use strict;
use DBI;
use Benchmark 'cmpthese';

my %db=( "on disk" => "/tmp/sqlitetest",
	 "in mem"  => ":memory:",
	 "ramdisk" => "/mnt/ramfs/sqlitetest" );

my $dbh;
for my $current_db (keys %db) {
  print "## Testing backing store $current_db: ##\n";
  $dbh = DBI->connect("dbi:SQLite:dbname=$db{$current_db}","","")
    or die "Couldn't create $current_db db";
  $dbh->do(qq{create table test ( id integer, text varchar, nuthertext varchar)}) 
      or die $dbh->errstr();

  for my $queries (10,100,300) {
      print "$queries actions per connection:\n";
      cmpthese (-5, 
		{ "no transact."    => sub {
		    my $sth
			= $dbh->prepare(q(insert into test values(?,?,?)));
		    for my $i(1..$queries) {
			$sth->execute($i,'No business','like perl business');
			my @row_ary 
			    = $dbh->selectrow_array('select count(*) from test');
		    }
		    $sth->finish();
		    
		    $dbh->do(qq{delete from test});  
		},

		  "transact." => sub { 
		      $dbh->begin_work();
		      my $sth = $dbh->prepare(q(insert into test values(?,?,?)));
		      for my $i(1..$queries) {
			  $sth->execute($i,'No business','like perl business');
			  my @row_ary 
			      = $dbh->selectrow_array('select count(*) from test');
		      }
		      $sth->finish();
		      $dbh->do(qq{delete from test});
		      $dbh->commit();
		  }
	      }
		);
  }
  $dbh->disconnect();
  unlink $db{$current_db};
}


##</code><code>##

## Testing backing store ramdisk: ##
10 actions per connection:
              Rate no transact.    transact.
no transact. 372/s           --         -37%
transact.    586/s          58%           --
100 actions per connection:
               Rate no transact.    transact.
no transact. 37.1/s           --         -44%
transact.    66.5/s          79%           --
300 actions per connection:
               Rate no transact.    transact.
no transact. 11.4/s           --         -44%
transact.    20.3/s          78%           --
## Testing backing store in mem: ##
10 actions per connection:
              Rate    transact. no transact.
transact.    621/s           --          -1%
no transact. 630/s           1%           --
100 actions per connection:
               Rate no transact.    transact.
no transact. 66.1/s           --          -0%
transact.    66.4/s           0%           --
300 actions per connection:
               Rate no transact.    transact.
no transact. 20.2/s           --          -0%
transact.    20.2/s           0%           --
## Testing backing store on disk: ##
10 actions per connection:
               Rate no transact.    transact.
no transact. 42.5/s           --         -84%
transact.     271/s         538%           --
100 actions per connection:
               Rate no transact.    transact.
no transact. 4.65/s           --         -92%
transact.    59.4/s        1177%           --
300 actions per connection:
               Rate no transact.    transact.
no transact. 1.58/s           --         -92%
transact.    19.6/s        1141%           --