#!/usr/bin/perl

use strict;
use Time::HiRes qw(gettimeofday tv_interval);
use Template;
use Getopt::Long;
use DBI;

# Connect to database using parameters in localconfig or hardcoded here.
our $db_host = "localhost";         # where is the database?
our $db_port = 3306;                # which port to use
our $db_sock = '';
our $db_name = "bugs_test";              # name of the MySQL database
our $db_user = "root";              # user to attach to the MySQL database
our $db_pass = '';
do "localconfig" or die "couldn't do localconfig: $!\n" if -e "localconfig";
my $dsn = "DBI:mysql:host=$db_host;database=$db_name;port=$db_port";
$dsn .= ";mysql_socket=$db_sock" if $db_sock;
my $dbh = DBI->connect($dsn, $db_user, $db_pass);

# Find out what version of MySQL we're using so we don't try something
# not supported by one version (f.e. turning off the query cache,
# which doesn't exist in version 3).
my (undef, $mysql_version) =
  $dbh->selectrow_array("SHOW VARIABLES LIKE 'version'");
$mysql_version =~ s/^(\d)\..*/$1/;

my @tests_to_run;
my $skip_runs = 1;
my $num_runs = 6;
my $fulltext = 0;
GetOptions ("test=s"      => \@tests_to_run,
            "numruns=i"   => \$num_runs,
            "skipruns=i"  => \$skip_runs,
            "fulltext"    => \$fulltext);

sub execute_timed {
  my $statement = shift;
  
  # Turn off the query cache if necessary so it doesn't skew the results.
  $statement =~ s/SELECT /SELECT SQL_NO_CACHE / if $mysql_version >= 4;

  # Turn off boolean mode in MySQL 3, since that version doesn't support it.
  $statement =~ s/ IN BOOLEAN MODE// if $mysql_version <= 3;

  my $explanation = $dbh->selectall_arrayref("EXPLAIN $statement");

  my $t0 = [gettimeofday];
  my $qry = $dbh->prepare($statement);
  $qry->execute();
  my $t1 = tv_interval($t0);
  
  my $count = $statement =~ /COUNT\(\*\)/i ? $qry->fetchrow_array() : $qry->rows;
  return($count, $t1, $explanation);
}

my @tests = 
(
{
  name => "1 str",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE INSTR(cf_aa, 'meta')" },
  dist => { query => "SELECT COUNT(*) FROM distcol_str WHERE field_id = 0 AND INSTR(value, 'meta')" }
},
{
  name => "1 str w/index",
  desc => "like the standard one-string query, but it searches with 'MATCH... AGAINST', which uses a fulltext index when possible",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE MATCH(cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE)" },
  dist => { query => "SELECT COUNT(*) FROM distcol_str WHERE field_id = 0 AND MATCH(value) AGAINST ('+meta*' IN BOOLEAN MODE)" }
},
{
  name => "1 str bzlike",
  desc => "like the one-string query, but it joins tables in the distributed and real distributed queries just like Bugzilla would",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE INSTR(cf_aa, 'meta')" },
  dist => { query => "SELECT COUNT(*) FROM realcol, distcol_str WHERE realcol.bug_id = distcol_str.bug_id AND field_id = 0 AND INSTR(value, 'meta')" },
  redi => { query => "SELECT COUNT(*) FROM realcol, cf_aa WHERE realcol.bug_id = cf_aa.bug_id AND INSTR(cf_aa.cf_aa, 'meta')" }
},
{
  name => "1 str bzlike w/index",
  desc => "like the one-string query with index, but it joins tables in the distributed and real distributed queries just like Bugzilla would",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE MATCH(cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE)" },
  dist => { query => "SELECT COUNT(*) FROM realcol, distcol_str WHERE realcol.bug_id = distcol_str.bug_id AND field_id = 0 AND MATCH(value) AGAINST ('+meta*' IN BOOLEAN MODE)" },
  redi => { query => "SELECT COUNT(*) FROM realcol, cf_aa WHERE realcol.bug_id = cf_aa.bug_id AND MATCH(cf_aa.cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE)" }
},
{
  name => "2 str",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE INSTR(cf_aa, 'meta') AND INSTR(cf_ab, 'meta')" },
  dist => { query => "SELECT COUNT(*) FROM distcol_str d1, distcol_str d2 WHERE d1.bug_id = d2.bug_id AND d1.field_id =0 AND d2.field_id = 1 AND INSTR(d1.value, 'meta') AND INSTR(d2.value, 'meta')" }
},
{
  name => "2 str w/index",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE MATCH(cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ab) AGAINST ('+meta*' IN BOOLEAN MODE)" },
  dist => { query => "SELECT COUNT(*) FROM distcol_str d1, distcol_str d2 WHERE d1.bug_id = d2.bug_id AND d1.field_id =0 AND d2.field_id = 1 AND MATCH(d1.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d2.value) AGAINST ('+meta*' IN BOOLEAN MODE)" }
},
{
  name => "3 str",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE INSTR(cf_aa, 'meta') AND INSTR(cf_ab, 'meta') AND INSTR(cf_ac, 'meta')" },
  dist => { query => "SELECT COUNT(*) FROM distcol_str d1, distcol_str d2, distcol_str d3 WHERE d1.bug_id = d2.bug_id AND d2.bug_id = d3.bug_id AND d1.field_id = 0 and d2.field_id = 1 AND d3.field_id = 2 and INSTR(d1.value, 'meta') AND INSTR(d2.value, 'meta') AND INSTR(d3.value, 'meta')" }
},
{
  name => "3 str w/index",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE MATCH(cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ab) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ac) AGAINST ('+meta*' IN BOOLEAN MODE)" },
  dist => { query => "SELECT COUNT(*) FROM distcol_str d1, distcol_str d2, distcol_str d3 WHERE d1.bug_id = d2.bug_id AND d2.bug_id = d3.bug_id AND d1.field_id = 0 and d2.field_id = 1 AND d3.field_id = 2 and MATCH(d1.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d2.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d3.value) AGAINST ('+meta*' IN BOOLEAN MODE)" }
},
{
  name => "10 str",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE INSTR(cf_aa, 'meta') AND INSTR(cf_ab, 'meta') AND INSTR(cf_ac, 'meta') AND INSTR(cf_ad, 'meta') AND INSTR(cf_ae, 'meta') AND INSTR(cf_af, 'meta') AND INSTR(cf_ag, 'meta') AND INSTR(cf_ah, 'meta') AND INSTR(cf_ai, 'meta') AND INSTR(cf_aj, 'meta')" },
  dist => { query => "SELECT COUNT(*) FROM distcol_str d1, distcol_str d2, distcol_str d3, distcol_str d4, distcol_str d5, distcol_str d6, distcol_str d7, distcol_str d8, distcol_str d9, distcol_str d10 WHERE d1.bug_id = d2.bug_id AND d2.bug_id = d3.bug_id AND d3.bug_id = d4.bug_id AND d4.bug_id = d5.bug_id AND d5.bug_id = d6.bug_id AND d6.bug_id = d7.bug_id AND d7.bug_id = d8.bug_id AND d8.bug_id = d9.bug_id AND d9.bug_id = d10.bug_id AND d1.field_id = 0 AND d2.field_id = 1 AND d3.field_id = 2 AND d4.field_id = 3 AND d5.field_id = 4 AND d6.field_id = 5 AND d7.field_id = 6 AND d8.field_id = 7 AND d9.field_id = 8 AND d10.field_id = 9 AND INSTR(d1.value, 'meta') AND INSTR(d2.value, 'meta') AND INSTR(d3.value, 'meta') AND INSTR(d4.value, 'meta') AND INSTR(d5.value, 'meta') AND INSTR(d6.value, 'meta') AND INSTR(d7.value, 'meta') AND INSTR(d8.value, 'meta') AND INSTR(d9.value, 'meta') AND INSTR(d10.value, 'meta')" }
},
{
  name => "10 str w/index",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE MATCH(cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ab) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ac) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ad) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ae) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_af) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ag) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ah) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ai) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_aj) AGAINST ('+meta*' IN BOOLEAN MODE)" },
  dist => { query => "SELECT COUNT(*) FROM distcol_str d1, distcol_str d2, distcol_str d3, distcol_str d4, distcol_str d5, distcol_str d6, distcol_str d7, distcol_str d8, distcol_str d9, distcol_str d10 WHERE d1.bug_id = d2.bug_id AND d2.bug_id = d3.bug_id AND d3.bug_id = d4.bug_id AND d4.bug_id = d5.bug_id AND d5.bug_id = d6.bug_id AND d6.bug_id = d7.bug_id AND d7.bug_id = d8.bug_id AND d8.bug_id = d9.bug_id AND d9.bug_id = d10.bug_id AND d1.field_id = 0 AND d2.field_id = 1 AND d3.field_id = 2 AND d4.field_id = 3 AND d5.field_id = 4 AND d6.field_id = 5 AND d7.field_id = 6 AND d8.field_id = 7 AND d9.field_id = 8 AND d10.field_id = 9 AND MATCH(d1.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d2.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d3.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d4.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d5.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d6.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d7.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d8.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d9.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d10.value) AGAINST ('+meta*' IN BOOLEAN MODE)" }
},
{
  name => "1 int bzlike",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE cf_int1 > 500" },
  dist => { query => "SELECT COUNT(*) FROM realcol, distcol_int d2 WHERE realcol.bug_id = d2.bug_id AND d2.field_id = 10 AND d2.value > 500" }
},
{
  name => "1 str, 1 int",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE INSTR(cf_aa, 'meta') AND cf_int1 > 500" },
  dist => { query => "SELECT COUNT(*) FROM distcol_str d1, distcol_int d2 WHERE d1.bug_id = d2.bug_id AND d1.field_id = 0 AND d2.field_id = 10 AND INSTR(d1.value, 'meta') AND d2.value > 500" }
},
{
  name => "1 str, 1 int w/index",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE MATCH(cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE) AND cf_int1 > 500" },
  dist => { query => "SELECT COUNT(*) FROM distcol_str d1, distcol_int d2 WHERE d1.bug_id = d2.bug_id AND d1.field_id = 0 AND d2.field_id = 10 AND MATCH(d1.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND d2.value > 500" }
},
{
  name => "2 str, 2 int",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE INSTR(cf_aa, 'meta') AND instr(cf_ab, 'meta') AND cf_int1 > 500 AND cf_int2 > 500" },
  dist => { query => "SELECT COUNT(*) FROM distcol_str d1, distcol_str d2, distcol_int d3, distcol_int d4 WHERE d1.bug_id = d2.bug_id AND d2.bug_id = d3.bug_id AND d3.bug_id = d4.bug_id AND d1.field_id = 0 AND d2.field_id = 1 AND d3.field_id = 10 AND d4.field_id = 11 AND INSTR(d1.value, 'meta') AND INSTR(d2.value, 'meta') AND d3.value > 500 AND d4.value > 500" }
},
{
  name => "2 str, 2 int w/index",
  real => { query => "SELECT COUNT(*) FROM realcol WHERE MATCH(cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ab) AGAINST ('+meta*' IN BOOLEAN MODE) AND cf_int1 > 500 AND cf_int2 > 500" },
  dist => { query => "SELECT COUNT(*) FROM distcol_str d1, distcol_str d2, distcol_int d3, distcol_int d4 WHERE d1.bug_id = d2.bug_id AND d2.bug_id = d3.bug_id AND d3.bug_id = d4.bug_id AND d1.field_id = 0 AND d2.field_id = 1 AND d3.field_id = 10 AND d4.field_id = 11 AND MATCH(d1.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d2.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND d3.value > 500 AND d4.value > 500" }
},
{
  name => "simple b.m.o query",
  real => { query => "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id WHERE bugs.assigned_to = map_assigned_to.userid AND ((INSTR(LOWER(bugs.status_whiteboard), 'meta'))) AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id ORDER BY bugs.bug_id" },
  dist => { query => "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id, distcol_str WHERE bugs.bug_id = distcol_str.bug_id AND bugs.assigned_to = map_assigned_to.userid AND distcol_str.field_id = 11 AND ((INSTR(LOWER(distcol_str.value), 'meta'))) AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id ORDER BY bugs.bug_id" },
  redi => { query => "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id, status_whiteboard WHERE bugs.bug_id = status_whiteboard.bug_id AND bugs.assigned_to = map_assigned_to.userid AND ((INSTR(LOWER(status_whiteboard.status_whiteboard), 'meta'))) AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id ORDER BY bugs.bug_id" },
},
{
  name => "simple b.m.o query w/index",
  real => { query => "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id WHERE bugs.assigned_to = map_assigned_to.userid AND MATCH(status_whiteboard) AGAINST ('+meta*' IN BOOLEAN MODE) AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id ORDER BY bugs.bug_id" },
  dist => { query => "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id, distcol_str WHERE bugs.bug_id = distcol_str.bug_id AND bugs.assigned_to = map_assigned_to.userid AND distcol_str.field_id = 11 AND MATCH(distcol_str.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id ORDER BY bugs.bug_id" },
  redi => { query => "SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id, status_whiteboard WHERE bugs.bug_id = status_whiteboard.bug_id AND bugs.assigned_to = map_assigned_to.userid AND MATCH(status_whiteboard.status_whiteboard) AGAINST ('+meta*' IN BOOLEAN MODE) AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id ORDER BY bugs.bug_id" },
},
);

my $template = <<'EOF';

[% types = { real => "Real", 
             dist => "Distributed",
             redi => "Real Distributed" } %]

<html>
  <head>
    <title>Test Results</title>
    <style>
      td.winner { background-color: lightgreen; }
      td.loser { background-color: lightyellow; }
      td.mismatch { background-color: lightred; }
      th { text-align: left; }
    </style>
  </head>
  <body>
    <h1>Test Results</h1>
    
    <h2>Summary</h2>
    
    <table border="1">
      <tr>
        <th>Test</th>
        [% FOREACH type = ["real", "dist", "redi"] %]
          <th>[% types.${type} %]</th>
        [% END %]
        <th>Count</th>
      </tr>
      [% FOREACH test = tests %]
        [% NEXT IF !test.run %]
        <tr>
          <td>
            <a href="#[% test.name %]">[% test.name %]</a>
          </td>
          [% FOREACH type = ["real", "dist", "redi"] %]
            [% IF test.$type %]
              <td class="[% type == test.winner ? "winner" : "loser" %]">
                ~[% test.${type}.avg_cost %]
              </td>
            [% ELSE %]
              <td></td>
            [% END %]
          [% END %]
          <td class="[% "mismatch" IF test.count_mismatch %]">
            [% test.count %]
          </td>
        </tr>
      [% END %]
    </table>

    [% FOREACH test = tests %]
      [% NEXT IF !test.run %]
      <h2><a name="[% test.name %]">[% test.name %]</a></h2>
      <p>[% test.desc %]</p>
      [% FOREACH type = ["real", "dist", "redi"] %]
        [% NEXT IF !test.$type %]
        <h3>[% types.${type} %]</h3>
        <p>Query: [% test.${type}.query %]</p>
        <p>Times: [% FOREACH cost = test.${type}.cost %][% cost %][% ", " UNLESS loop.last() %][% END %]</p>
        <table border="1">
          <caption>Explanation</caption>
          <tr>
            <th>id</th>
            <th>select_type</th>
            <th>table</th>
            <th>type</th>
            <th>possible_keys</th>
            <th>key</th>
            <th>key_len</th>
            <th>ref</th>
            <th>rows</th>
            <th>Extra</th>
          </tr>
          [% FOREACH row = test.${type}.explanation %]
            <tr>
              [% FOREACH field = row %]
                <td>[% field %]</td>
                [% END %]
            </tr>
          [% END %]
        </table>
      [% END %]
    [% END %]
  </body>
</html>
EOF

my ($cost, $i);
foreach my $test (@tests) {
  next if scalar(@tests_to_run) && !grep($test->{name} eq $_, @tests_to_run);
  next if $test->{name} =~ /w\/index/ and !$fulltext;
  $test->{run} = 1;
  
  my $winning_time;
  foreach my $type (qw(real dist redi)) {
    next if !$test->{$type};
    $test->{$type}->{cost} = [];
    my $total_cost = 0;
    foreach $i (1 .. $num_runs) {
      ($test->{$type}->{count}, $cost, $test->{$type}->{explanation})
        = execute_timed($test->{$type}->{query});
       {
        push(@{$test->{$type}->{cost}}, $cost);
        $total_cost += $cost unless $i <= $skip_runs;
      }
    }
    $test->{$type}->{avg_cost} = $total_cost / ($num_runs - $skip_runs);
    if (!defined $winning_time || $test->{$type}->{avg_cost} < $winning_time) {
      $winning_time = $test->{$type}->{avg_cost};
      $test->{winner} = $type;
    }
    $test->{count} ||= $test->{$type}->{count};
    $test->{count_mismatch} = 1 if $test->{$type}->{count} != $test->{count};
  }
}

my $tt = new Template();
$tt->process(\$template, { tests => \@tests })
  || die "Template process failed: ", $tt->error(), "\n";
