#!/usr/bin/perl
use strict;
use DBI;
use Time::HiRes qw(time);
use Data::Dumper;

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

$dbh->do("PREPARE simple AS SELECT * FROM test");
$dbh->do("PREPARE ordered AS SELECT * FROM test ORDER BY i DESC");
$dbh->do(q{PREPARE complex AS SELECT n.nspname as "Schema",
                c.relname as "Name",
                CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
                r.rolname as "Owner"
                FROM pg_catalog.pg_class c
                JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
                LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                WHERE c.relkind IN ('r','v','S','')
                AND pg_catalog.pg_table_is_visible(c.oid)
                ORDER BY 1,2
});

print "Test on simplest query: select * from test\n";

cmpthese(
    100000,
    {
        'sql'   => sub { my $temp = $dbh->selectall_arrayref("SELECT * FROM test"); }, 
        'sql-prep'   => sub { my $temp = $dbh->selectall_arrayref("execute simple"); }, 
        'next'  => sub { my $temp = $dbh->selectall_arrayref("SELECT * FROM return_next_simple()"); }, 
        'query' => sub { my $temp = $dbh->selectall_arrayref("SELECT * FROM return_query_simple()"); }, 
    }
);

print "Test on ordered query: select * from test order by i desc\n";

cmpthese(
    100000,
    {
        'sql'   => sub { my $temp = $dbh->selectall_arrayref("SELECT * FROM test ORDER BY i DESC"); }, 
        'sql-prep' => sub { my $temp = $dbh->selectall_arrayref("execute ordered"); },
        'next'  => sub { my $temp = $dbh->selectall_arrayref("SELECT * FROM return_next_simple_ordered()"); }, 
        'query' => sub { my $temp = $dbh->selectall_arrayref("SELECT * FROM return_query_simple_ordered()"); }, 
    }
);

print "Test on complex query: (\\d with all schemas, 70 entries returned)\n";

cmpthese(
    1000000,
    {
        'sql'   => sub { my $temp = $dbh->selectall_arrayref(q{
                SELECT n.nspname as "Schema",
                c.relname as "Name",
                CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
                r.rolname as "Owner"
                FROM pg_catalog.pg_class c
                JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
                LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                WHERE c.relkind IN ('r','v','S','')
                AND pg_catalog.pg_table_is_visible(c.oid)
                ORDER BY 1,2
                }); }, 
        'sql-prep' => sub { my $temp = $dbh->selectall_arrayref("execute complex"); },
        'next'  => sub { my $temp = $dbh->selectall_arrayref("SELECT * FROM return_next_complex()"); }, 
        'query' => sub { my $temp = $dbh->selectall_arrayref("SELECT * FROM return_query_complex()"); }, 
    }
);

exit;

sub cmpthese {
    my ($count, $tests) = @_;
    my @test_codes = sort keys %{ $tests };
    my %times = ();
    my $longest_code = 0;
    for (1..$count) {
        for my $test (@test_codes) {
            $longest_code = length $test if $longest_code < length $test;
            my $time = timeit($tests->{ $test });
            $times{ $test }->{ 'sum' } += $time;
            $times{ $test }->{ 'min' } = $time if !defined $times{ $test }->{ 'min' } || $times{ $test }->{ 'min' } > $time;
            $times{ $test }->{ 'max' } = $time if !defined $times{ $test }->{ 'max' } || $times{ $test }->{ 'max' } < $time;
        }
    }

    printf(" %-${longest_code}s |     min     |     max     |     sum     | iter/s\n", "test:");
    for my $test (sort { $times{$b}->{'sum'} <=> $times{$a}->{'sum'} } @test_codes) {
        my $min = $times{ $test }->{'min'};
        my $max = $times{ $test }->{'max'};
        my $sum = $times{ $test }->{'sum'};
        my $iter = $count / $sum;
        printf(" %${longest_code}s | %11.5f | %11.5f | %11.5f | %11.5f\n", $test, $min, $max, $sum, $iter);
    }
}

sub timeit {
    my $code = shift;
    my $start = time();
    $code->();
    return time() - $start;
}