#!/usr/bin/perl -w
#
# Copyright 2005 MySQL AB
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
# 
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# 
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#
# Generate SQL commands for creating and using a table with 128 indexes.
#

my $table1_name= "t1";
my $table2_name= "t2";
my $table_name;
my $index_cnt= 128;
my $index_no;
my $column_cnt= 16;
my $column_no;
my $part_cnt;
my $part_no;
my $offset;
my $cmd;

print "#\n";
print "# Bug #10932 - " .
      "Building server with key limit of 128, makes test cases fail\n";
print "#\n";
printf "drop table if exists %s, %s;\n", $table1_name, $table2_name;

$table_name= $table1_name;
printf "create table %s (\n", $table_name;
for ($column_no= 0; $column_no < $column_cnt; $column_no++)
{
    printf "  c%02d char(2),\n", $column_no + 1;
}
for ($index_no= 0; $index_no < $index_cnt; $index_no++)
{
    printf "  index i%03d (", $index_no + 1;
    $part_cnt= $index_no % $column_cnt;
    $offset= $index_no / $column_cnt;
    for ($part_no= 0; $part_no <= $part_cnt; $part_no++)
    {
        if ($part_no)
        {
            print ", ";
            ($part_no % 8) || print "\n              ";
        }
        printf "c%02d", ($part_no + $offset) % $column_cnt + 1;
    }
    printf ")%s\n", ($index_no < $index_cnt - 1) ? "," : "";
}
print "  );\n";

printf "insert into %s values\n", $table_name;
for ($index_no= 0; $index_no < $index_cnt; $index_no++)
{
    if ($index_no)
    {
        if ($index_no % 26)
        {
            print "),\n";
        }
        else
        {
            printf ");\ninsert into %s values\n", $table_name;
        }
    }
    print "  (";
    for ($column_no= 0; $column_no < $column_cnt; $column_no++)
    {
        if ($column_no)
        {
            print ", ";
            ($column_no % 8) || print "\n   ";
        }
        printf "'%c%c'", $index_no % 26 + ord('a'), $column_no + ord('a');
    }
}
print ");\n";

for ($index_no= 0; $index_no < $index_cnt; $index_no++)
{
    $cmd= sprintf ("select ");
    $part_cnt= $index_no % $column_cnt;
    $offset= $index_no / $column_cnt;
    for ($part_no= 0; $part_no <= $part_cnt; $part_no++)
    {
        if ($part_no)
        {
            $cmd.= sprintf ", ";
            ($part_no % 8) || ($cmd.= sprintf "\n               ");

        }
        $cmd.= sprintf "c%02d", ($part_no + $offset) % $column_cnt + 1;
    }
    $cmd.= sprintf " from %s where\n               ", $table_name;
    $part_cnt= $index_no % $column_cnt;
    $offset= $index_no / $column_cnt;
    for ($part_no= 0; $part_no <= $part_cnt; $part_no++)
    {
        if ($part_no)
        {
            $cmd.= sprintf " and ";
            ($part_no % 4) || ($cmd.= sprintf "\n               ");
        }
        $cmd.= sprintf "c%02d = '%c%c'",
                       ($part_no + $offset) % $column_cnt + 1,
                       $index_no % 26 + ord('a'), $part_no + ord('a');
    }
    $cmd.= sprintf ";\n";
    print "explain " . $cmd;
    print "        " . $cmd;
}

$table_name= $table2_name;
printf "create table %s (\n", $table_name;
for ($column_no= 0; $column_no < $column_cnt; $column_no++)
{
    printf "  c%02d char(2),\n", $column_no + 1;
}
for ($index_no= 0; $index_no < $index_cnt; $index_no++)
{
    printf "  unique index i%03d (", $index_no + 1;
    $part_cnt= $index_no % $column_cnt;
    $offset= $index_no / $column_cnt;
    for ($part_no= 0; $part_no <= $part_cnt; $part_no++)
    {
        if ($part_no)
        {
            print ", ";
            ($part_no % 8) || print "\n              ";
        }
        printf "c%02d", ($part_no + $offset) % $column_cnt + 1;
    }
    printf ")%s\n", ($index_no < $index_cnt - 1) ? "," : "";
}
print "  );\n";

printf "insert into %s values\n", $table_name;
for ($index_no= 0; $index_no < $index_cnt; $index_no++)
{
    if ($index_no)
    {
        if ($index_no % 26)
        {
            print "),\n";
        }
        else
        {
            printf ");\n#--error 1062\ninsert into %s values\n", $table_name;
        }
    }
    print "  (";
    for ($column_no= 0; $column_no < $column_cnt; $column_no++)
    {
        if ($column_no)
        {
            print ", ";
            ($column_no % 8) || print "\n   ";
        }
        printf "'%c%c'", $index_no % 26 + ord('a'), $column_no + ord('a');
    }
}
print ");\n";

for ($index_no= 0; $index_no < $index_cnt; $index_no++)
{
    $cmd= sprintf ("select ");
    $part_cnt= $index_no % $column_cnt;
    $offset= $index_no / $column_cnt;
    for ($part_no= 0; $part_no <= $part_cnt; $part_no++)
    {
        if ($part_no)
        {
            $cmd.= sprintf ", ";
            ($part_no % 3) || ($cmd.= sprintf "\n               ");

        }
        $cmd.= sprintf "%s.c%02d, %s.c%02d",
                       $table1_name, ($part_no + $offset) % $column_cnt + 1,
                       $table2_name, ($part_no + $offset) % $column_cnt + 1;
    }
    $cmd.= sprintf " from %s, %s where\n               ",
                   $table1_name, $table2_name;
    $part_cnt= $index_no % $column_cnt;
    $offset= $index_no / $column_cnt;
    for ($part_no= 0; $part_no <= $part_cnt; $part_no++)
    {
        if ($part_no)
        {
            $cmd.= sprintf " and ";
            ($part_no % 3) || ($cmd.= sprintf "\n               ");
        }
        $cmd.= sprintf "%s.c%02d = %s.c%02d",
                       $table1_name, ($part_no + $offset) % $column_cnt + 1,
                       $table2_name, ($part_no + $offset) % $column_cnt + 1;
    }
    $cmd.= sprintf ";\n";
    print "explain " . $cmd;
    print "        " . $cmd;
}

printf "#drop table %s, %s;\n", $table1_name, $table2_name;