#!/usr/bin/perl -w
use strict;
use warnings;
use DBI;
use Data::Dumper;
use Test::More tests=>138 ; 
# qw(no_plan);

my $dbh= DBI->connect('dbi:mysql:test;host=localhost;'
         ."mysql_read_default_file=$ENV{HOME}/.my.cnf",
         undef,undef, {RaiseError => 1})
         or die "$DBI::errstr\n";

my ($version) = $dbh->selectrow_array(qq{select version()});
ok($version gt '5.1', 'version');
diag "version = $version";
ok_do(qq{DROP TABLE IF EXISTS t1, t2},'initializing');

my $insert_t1_values = 
qq{
INSERT INTO t1 VALUES (16421), (19092), (22589)
};

my $insert_t2_values =
qq{
INSERT INTO t2 VALUES 
('2006-09-27 21:50:01',16421), ('2006-10-02 21:50:01',16421),
('2006-09-27 21:50:01',19092), ('2006-09-28 21:50:01',19092),
('2006-09-29 21:50:01',19092), ('2006-09-30 21:50:01',19092),
('2006-10-01 21:50:01',19092), ('2006-10-02 21:50:01',19092),
('2006-09-27 21:50:01',22589), ('2006-09-29 21:50:01',22589)
};

my $base_t1_table = 
qq{CREATE TABLE t1 (
  id int(8) NOT NULL
  #KEYS
) ENGINE=_ENGINE_
};

my $base_t2_table = 
qq{CREATE TABLE t2 (
  taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  id int(11) NOT NULL DEFAULT '0'
  #KEYS
) ENGINE=_ENGINE_ 
};

my $t1_indexes = qq{,\n PRIMARY KEY (id)\n};
my $t2_indexes = qq{,\n PRIMARY KEY (id,taken),\nKEY taken (taken)\n};
my @partition_clauses = (
    {   name        => 'plain',
        statement   => q{},
    },
    {
        name => 'list by date',
        statement => qq{
        PARTITION BY list (to_days(taken)) 
        (
        PARTITION p01 VALUES IN ( to_days('2006-09-27'), to_days('2006-09-28') ), 
        PARTITION p02 VALUES IN ( to_days('2006-09-29'), to_days('2006-09-30') ), 
        PARTITION p03 VALUES IN ( to_days('2006-10-01'), to_days('2006-10-02') ) 
        )
        }
    },
     {
        name => 'list by number',
        statement => qq{
        PARTITION BY list (id) 
        (
        PARTITION p01 VALUES IN ( 16421  ), 
        PARTITION p02 VALUES IN ( 19092, 22589 )
        )
        }
    },
     {
        name => 'range by date',
        statement => qq{
        PARTITION BY RANGE (to_days(taken)) 
        (
        PARTITION p01 VALUES LESS THAN (732920) , 
        PARTITION p02 VALUES LESS THAN (732950) , 
        PARTITION p03 VALUES LESS THAN MAXVALUE ) ;
        }
    },
    {
        name => 'range by number',
        statement => qq{
        PARTITION BY RANGE (id) 
        (
        PARTITION p01 VALUES LESS THAN (17000) , 
        PARTITION p02 VALUES LESS THAN (19000) , 
        PARTITION p03 VALUES LESS THAN (22000) , 
        PARTITION p4  VALUES LESS THAN MAXVALUE ) ;
        }
    },
    {
        name => 'key by date',
        statement => qq{
        PARTITION BY KEY () 
        PARTITIONS 4
        }
    },
    {
        name => 'key by number',
        statement => qq{
        PARTITION BY KEY (id) 
        PARTITIONS 4
        }
    },
    {
        name => 'hash by date',
        statement => qq{
        PARTITION BY HASH (to_days(taken)) 
        PARTITIONS 4
        }
    },
    {
        name => 'hash by number',
        statement => qq{
        PARTITION BY HASH (id) 
        PARTITIONS 4
        }
    },
);

my @engines  = (
    {name => 'MyISAM',  has_keys => 1},
    {name => 'InnoDB',  has_keys => 1},
    {name => 'Archive', has_keys => 0},
);
my $test_query =
qq{
SELECT t1.id AS _WHAT_
FROM t1
WHERE t1.id IN (
    SELECT distinct id
    FROM t2
    WHERE taken BETWEEN \@f_date AND date_add(\@t_date, INTERVAL 1 DAY))
ORDER BY t1.id 
};

ok_do(qq{set \@f_date='2006-09-28'},'setting f_date');
ok_do(qq{set \@t_date='2006-10-02'},'setting t_date');

my $base_recs;

for my $engine ( @engines ) {

    my $t1_query = $base_t1_table;
    if ($engine->{has_keys}) {
        $t1_query =~ s/#KEYS/$t1_indexes/;
    }    

    $t1_query =~ s/_ENGINE_/$engine->{name}/;
    ok_do($t1_query, 'creating t1 '. $engine->{name});
    ok_do($insert_t1_values, 'inserting t1 values');

    for my $part (@partition_clauses) {
        my $t2_query = $base_t2_table;
        if ($engine->{has_keys}) {
            $t2_query =~ s/#KEYS/$t2_indexes/;
        }    
        else {
            next if $part->{statement} =~ /\bkey\b/i;
        }
        $t2_query =~ s/_ENGINE_/$engine->{name}/;
        $t2_query .= $part->{statement} ;

        ok_do( $t2_query, 'creating t1 ' . $engine->{name} . ' ' . $part->{name});
        ok_do( $insert_t2_values, 'inserting t2 values '. $engine->{name} . ' ' . $part->{name});
        my $exec_query = $test_query;
        $exec_query =~ s/_WHAT_/$dbh->quote('result ' . $engine->{name} . ' ' . $part->{name})/e;

        my $recs = $dbh->selectall_arrayref($exec_query);
        ok($recs , 'running '. $engine->{name} . ' ' . $part->{name} );
        if ($part->{name} eq 'plain') {
            $base_recs = $recs;
        }

        unless ( is_deeply($recs, $base_recs, 'data found ' . $engine->{name} . ' ' . $part->{name} )) {
            diag out($recs,$base_recs);        
        }
        ok_do(qq{drop table if exists t2}, 'cleaning up t2 '. $engine->{name} . ' ' . $part->{name} );
    }
    ok_do(qq{drop table if exists t1}, 'cleaning up t1 '. $engine->{name}  );
}

sub ok_do {
    my ($query, $msg, @params) = @_;
    my $result;
    if (@params)
    {
        eval {$result = $dbh->do($query, undef, @params)};
    }
    else {
        eval {$result = $dbh->do($query)};
    }
    if ($@) {
        print $query,$/;
        die $@;
    }
    return ok($result, $msg);
}
sub out {
    my ($got, $expected) = @_;
    return   "got     : [" . join( q{,}, map {join( q{:},@$_ ) }  @$got) . q{] - }
           . "expected: [" . join( q{,}, map {join( q{:},@$_ ) }  @$expected) . q{]};    
}