#!perl

use strict;
use warnings;

use Devel::Size qw( total_size );
use LWP::Simple;
use Benchmark qw( cmpthese );
use DBI;


# Comment out either the first declaration of $database and $url,
# or the second declaration, depending on which test you wish
# to run.  This is to test scalability.

#my $database = 'allwords.db';
#my $url = 'http://davido.perlmonk.org/monastery/t1000f.txt';

my $database = 'halfwords.db';
my $url = 'http://davido.perlmonk.org/monastery/t500f.txt';

# --------------

my( %forward, %backward );

my $dbh = DBI->connect( "DBI:SQLite:$database", 
                        '', '', 
                        { RaiseError => 1, 
                          AutoCommit => 0 } 
);

{
    print "Testing for existing word-pair database...\n";
    eval {  my $sth = $dbh->prepare( 
                "SELECT * FROM wordtable WHERE 0=1" 
            );
            $sth->execute;
            $sth->finish;
    };
    if( $@ ) {
        print "Database not found.  It will be created.\n";
        {
            print "Pulling words list from server.\n";
            my @words = split /\s+/, get( $url);
            @forward{ @words } = reverse @words;
            @backward{ reverse @words } = @words;
        }
        print "Creating word-pair database...\n";
        $dbh->do(
            "CREATE TABLE wordtable
                ( Left  varchar,
                  Right varchar
             ) " 
        );
        $dbh->do(
            "CREATE UNIQUE INDEX Index_Left
             ON wordtable ( Left )" 
        );
        $dbh->do(
            "CREATE UNIQUE INDEX Index_Right
             ON wordtable ( Right )"
        );
        my $sth = $dbh->prepare( 
            "INSERT INTO wordtable
             ( Left, Right ) VALUES ( ?, ? )" 
        );
        while ( my( $left, $right ) = each %forward ) {
            $sth->execute( $left, $right );
        }
        $sth->finish;
        $dbh->commit;
        print "Database created.\n";
    } else {
        print "Database found.\n";
        my $sth = $dbh->prepare( "SELECT * FROM wordtable" );
        $sth->execute();
        while ( my( @pair ) = $sth->fetchrow_array() ) {
            $forward{ $pair[0] } = $pair[1];
            $backward{ $pair[1] } = $pair[0];
        }
        $sth->finish();
    }
}

my @find = keys %forward;

my $sth = $dbh->prepare(
    "SELECT Right FROM wordtable WHERE Left = ?" );

print "Number of word pairs: ",
      scalar @find,
      ".\n";
print "Combined size of \%forward and \%backward lookup hashes: ", 
      total_size( \%forward ) + total_size( \%backward ),
      " bytes.\n";

print "Benchmarking...\n";

cmpthese( -5, {
    Hash => sub { 
        my $value = $forward{ $find[ rand( @find ) ] }; 
    },
    Database => sub {
        $sth->execute( $find[ rand( @find ) ] );
        my($value) = ( $sth->fetchrow_array() );
    },
} );

$sth->finish();

$dbh->disconnect();