#!/usr/bin/perl
use strict;
use warnings;

use Time::HiRes qw( time );
use Data::Dumper;
use DBI;

my @numbers = ();

for ( 1 .. 100 ) {
    my $x = join "", map { int rand 10 } ( 1 .. 12 );
    push @numbers, $x;
}

my $dbh = DBI->connect(
    "dbi:Pg:dbname=depesz;host=127.0.0.1;port=5840",
    "depesz", undef,
    {
        'AutoCommit' => 1,
        'RaiseError' => 1,
        'PrintError' => 1,
    }
);

my %sqls = (
    'depesz'    => 'select * from prefixes_depesz where prefix in (?, ?, ?, ?, ?, ?, ?) order by length(prefix) desc limit 1',
    'dim'       => 'select * from prefixes_dim where prefix @> ? order by length(prefix) desc limit 1',
    'andrewsn1' => 'select * from prefixes_andrewsn_1 where substring(prefix for 1) = ? order by length(prefix) desc limit 1',
    'andrewsn2' => 'select * from prefixes_andrewsn_2 where ( length(prefix) = 1 and prefix = ? ) or ( length(prefix) >= 2 and substring(prefix for 2) = ? ) order by length(prefix) desc limit 1',
    'andrewsn3' =>
'select * from prefixes_andrewsn_3 where ( length(prefix) = 1 and prefix = ? ) or ( length(prefix) = 2 and prefix = ? ) or ( length(prefix) >= 3 and substring(prefix for 3) = ? ) order by length(prefix) desc limit 1',
    'andrewsn4' =>
'select * from prefixes_andrewsn_4 where ( length(prefix) = 1 and prefix = ? ) or ( length(prefix) = 2 and prefix = ? ) or ( length(prefix) = 3 and prefix = ? ) or ( length(prefix) >= 4 and substring(prefix for 4) = ? ) order by length(prefix) desc limit 1',
    'andrewsn5' =>
'select * from prefixes_andrewsn_5 where ( length(prefix) = 1 and prefix = ? ) or ( length(prefix) = 2 and prefix = ? ) or ( length(prefix) = 3 and prefix = ? ) or ( length(prefix) = 4 and prefix = ? ) or ( length(prefix) >= 5 and substring(prefix for 5) = ? ) order by length(prefix) desc limit 1',
    'andrewsn6' =>
'select * from prefixes_andrewsn_6 where ( length(prefix) = 1 and prefix = ? ) or ( length(prefix) = 2 and prefix = ? ) or ( length(prefix) = 3 and prefix = ? ) or ( length(prefix) = 4 and prefix = ? ) or ( length(prefix) = 5 and prefix = ? ) or ( length(prefix) >= 6 and substring(prefix for 6) = ? ) order by length(prefix) desc limit 1',
    'andrewsn7' =>
'select * from prefixes_andrewsn_7 where ( length(prefix) = 1 and prefix = ? ) or ( length(prefix) = 2 and prefix = ? ) or ( length(prefix) = 3 and prefix = ? ) or ( length(prefix) = 4 and prefix = ? ) or ( length(prefix) = 5 and prefix = ? ) or ( length(prefix) = 6 and prefix = ? ) or ( length(prefix) >= 7 and substring(prefix for 7) = ? ) order by length(prefix) desc limit 1',
);

$dbh->do( 'SET enable_seqscan = off' );
bench_it(
    1000,
    'depesz'    => \&test_depesz,
    'dim'       => \&test_dim,
    'andrewsn1' => \&test_andrewsn1,
    'andrewsn2' => \&test_andrewsn2,
    'andrewsn3' => \&test_andrewsn3,
    'andrewsn4' => \&test_andrewsn4,
    'andrewsn5' => \&test_andrewsn5,
    'andrewsn6' => \&test_andrewsn6,
    'andrewsn7' => \&test_andrewsn7,
);

exit;

sub bench_it {
    my ( $iter, %methods ) = @_;
    my %results = ();

    for my $i ( 1 .. $iter ) {
        for my $what ( keys %methods ) {
            my $start = time();
            for my $number ( @numbers ) {
                $methods{ $what }->( $number );
            }
            my $finish = time();
            $results{ $what } += ( $finish - $start );
        }
    }

    my $base = 0;
    for my $what ( sort { $results{ $a } <=> $results{ $b } } keys %results ) {
        $base ||= $results{ $what };
        printf "%-15s : %8.6f : %6.3f\n", $what, $results{ $what }, $results{ $what } * 100 / $base;
    }
    print "\n--------------------------------\n";
}

sub test_depesz {
    my $num = shift;
    return $dbh->selectall_arrayref( $sqls{ 'depesz' }, undef, map { substr( $num, 0, $_ ) } ( 1 .. 7 ) );
}

sub test_dim {
    my $num = shift;
    return $dbh->selectall_arrayref( $sqls{ 'dim' }, undef, $num );
}

sub test_andrewsn1 {
    my $num = shift;
    return $dbh->selectall_arrayref( $sqls{ 'andrewsn1' }, undef, substr( $num, 0, 1 ) );
}

sub test_andrewsn2 {
    my $num = shift;
    return $dbh->selectall_arrayref( $sqls{ 'andrewsn2' }, undef, map { substr( $num, 0, $_ ) } ( 1 .. 2 ) );
}

sub test_andrewsn3 {
    my $num = shift;
    return $dbh->selectall_arrayref( $sqls{ 'andrewsn3' }, undef, map { substr( $num, 0, $_ ) } ( 1 .. 3 ) );
}

sub test_andrewsn4 {
    my $num = shift;
    return $dbh->selectall_arrayref( $sqls{ 'andrewsn4' }, undef, map { substr( $num, 0, $_ ) } ( 1 .. 4 ) );
}

sub test_andrewsn5 {
    my $num = shift;
    return $dbh->selectall_arrayref( $sqls{ 'andrewsn5' }, undef, map { substr( $num, 0, $_ ) } ( 1 .. 5 ) );
}

sub test_andrewsn6 {
    my $num = shift;
    return $dbh->selectall_arrayref( $sqls{ 'andrewsn6' }, undef, map { substr( $num, 0, $_ ) } ( 1 .. 6 ) );
}

sub test_andrewsn7 {
    my $num = shift;
    return $dbh->selectall_arrayref( $sqls{ 'andrewsn7' }, undef, map { substr( $num, 0, $_ ) } ( 1 .. 7 ) );
}