#!/usr/bin/perl
##
# Clumsy test, trying to determine if using "BIT" columns if more efficient
# than the "old" way of using "TINYINT" columns (yes, MySQL supports "real"
# BIT columns since v5.0.3) :)
# 
# HINT: create your test data using whatever options you want, but afterwards
#  keep on using the "-n" option ("nocreate") in order to reuse that data ;-)
#  e.g.: 
#     # create data using default options
#     # ...  and run a test for each method to-be-benchmarked
#     ./sql-bits-mysql.pl -t
#     # run benchmark reusing previously created data
#     ./sql-bits-mysql.pl -n
##
# altblue 2007-11-15
##
use strict;
use warnings;
use Getopt::Long qw(:config no_ignore_case bundling auto_version auto_help);
use Benchmark qw(:all);
use DBI ();
use Smart::Comments;

my %opt = (
    iterations => -0.2,     # how many? Benchmark style!
    rows       => 10_000,   # populate test tables with "this" many rows
    bits       => 8,        # setting up "this" many "bit" fields

    dsn      => 'DBI:mysql:test',    # SQL connection info
    username => 'test',              # SQL user
    password => '',                  # SQL password
    table    => 'bits_bench',        # two tables will be created using this
                                     # "name": "<name>_real" and "<name>_fake"
);
Getopt::Long::GetOptions( \%opt, qw[
    dsn|d=s
    username|u=s
    password|p=s
    iterations|i=i
    rows|r=i
    bits|b=i
    test|t!
    nocreate|n!
]);

### connecting to `$opt{dsn}` as `$opt{username}` ...
my $dbh = DBI->connect(
    @opt{qw(dsn username password)},
    {   PrintError         => 0,
        PrintWarn          => 1,
        RaiseError         => 1,
        ShowErrorStatement => 1,
        AutoCommit         => 1,
    }
) or confess $DBI::errstr;
END { $dbh and $dbh->disconnect; }

sub drop_tables {
    for my $table (map { join '_', $opt{table}, $_ } qw(real fake)) {
        ### dropping table `$table` ...
        $dbh->do("DROP TABLE IF EXISTS `$table`");
    }
}

sub create_tables {
    ### creating table `$opt{table}_real` ...
    $dbh->do(qq[
        CREATE TABLE `$opt{table}_real` (
            `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
            `flags` BIT($opt{bits}) NOT NULL COMMENT 'Bit Flags',
            PRIMARY KEY (`id`),
            KEY `flags` (`flags`)
        ) Engine=InnoDB
    ]);

    ### creating table `$opt{table}_fake` ...
    {
        my $sql = qq[
            CREATE TABLE `$opt{table}_fake` (
                `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
        ];
        for (1 .. $opt{bits}) {
            $sql .= qq[
                `flag$_`  TINYINT(1) UNSIGNED NOT NULL COMMENT 'Fake Bit Flag $_',
                KEY `flag$_` (`flag$_`),
            ];
        }
        $sql .= qq[
                PRIMARY KEY (`id`)
            ) Engine=InnoDB
        ];
        $dbh->do($sql);
    }
}

sub populate_tables {
    ### inserting $opt{rows} rows into `$opt{table}_real` ...
    {
        # yes, use the crappy b'10101010' notation, as DBI quotes provided values
        my $sth = $dbh->prepare(
            "INSERT INTO `$opt{table}_real` (`flags`) VALUES(b?)"
        );
        for my $idx ( 1 .. $opt{rows} ) {    ### Inserting ===[%]     done
            # cycle through all possible values
            $sth->execute( sprintf "%0$opt{bits}b", $idx % 2**$opt{bits} );
        }
    }

    ### inserting $opt{rows} rows into `$opt{table}_fake` ...
    {
        my $sth = $dbh->prepare(
            "INSERT INTO `$opt{table}_fake` (`"
            . join( '`,`', map { 'flag' . $_ } 1 .. $opt{bits} )
            . '`) VALUES('
            . join(',', ('?') x $opt{bits})
            . ')'
        );
        for my $idx ( 1 .. $opt{rows} ) {    ### Inserting ===[%]     done
            # cycle through all possible values
            $sth->execute(split //, sprintf "%0$opt{bits}b", $idx % 2**$opt{bits});
        }
    }
}


if ($opt{nocreate}) {
    ### determining bits/rows from previous test run ...
    my ($name, $sql) = $dbh->selectrow_array(
        "SHOW CREATE TABLE `$opt{table}_real`"
    );
    $sql =~ /flags \`? \s+ bit\( (\d+)/six and $opt{bits} = $1;
    ### bits: $opt{bits}

    ($opt{rows}) = $dbh->selectrow_array(
        "SELECT COUNT(*) FROM `$opt{table}_real`"
    );
    ### rows: $opt{rows}
}
else {
    drop_tables();
    create_tables();
    populate_tables();
}

my $sel_real = "SELECT SQL_NO_CACHE COUNT(*) FROM `$opt{table}_real` WHERE ";
my $sel_fake = "SELECT SQL_NO_CACHE COUNT(*) FROM `$opt{table}_fake` WHERE ";
my %selectors = (
    r1  => "$sel_real `flags` & 1",
    f1  => "$sel_fake `flag$opt{bits}` = 1",
);

{
    my $last_fake_sql = $selectors{f1};
    my $last_fake_idx = 0;
    for my $bit (2 .. $opt{bits}) {
        my $top = 2 ** $bit - 1;
        $selectors{"r$top"} = "$sel_real `flags` & $top = $top";
        $last_fake_idx++;
        $last_fake_sql .= ' AND `flag' . ($opt{bits}-$last_fake_idx) . '` = 1';
        $selectors{"f$top"} = $last_fake_sql;
    }
}

my %methods;
while (my ($name, $sql) = each %selectors) {
    $methods{$name} = sub { return $dbh->selectall_arrayref($sql); };
}

if ($opt{test}) {
    ### testing ...
    while (my ($name, $method) = each %methods) {
        print Data::Dumper->new( [$method->()], [$name] )->Indent(1)->Dump;
    }
}
else {
    ### benchmarking ...
    cmpthese( timethese( $opt{iterations}, \%methods ) );
}