use DBI;

$dbh=DBI->connect("dbi:mysql:port=9306:database=test", "root", "") or die $DBI::errstr;

$total_rows=100;
$duplicates=1000;

print "Creating table\n";

$dbh->do("drop table if exists bench1");

$dbh->do("create table bench1 (id1 int NOT NULL,
                               id2 int NOT NULL,
                               dupnum int NOT NULL,
                               index ix_id12 (id1,id2),
                               index ix_id2  (id2)) ENGINE=Falcon");

print "Inserting in reverse order: $total_rows unique rows, $duplicates of each\n";

$query="insert into bench1 (id1,id2,dupnum) values ";

for ($i=0 ; $i < $total_rows ; $i += 2)
{
   $fieldvalue = $total_rows-1-$i;
   for ($j = 1; $j <= $duplicates ; $j++)
   {
      $sth = $dbh->do($query       . "(" . 
                     ($fieldvalue) . "," .
                     ($fieldvalue) . "," .
                     ($j)          . ")" ) or die $DBI::errstr;
   }
   if ($duplicates > 500)
   {
      $j -= 1;
      $total = $j * ($i + 1);
      print "$total records: $j inserted with id1 = $fieldvalue\n";
   }

   $fieldvalue = $i + 10;
   for ($j = 1; $j <= $duplicates ; $j++)
   {
      $sth = $dbh->do($query       . "(" . 
                     ($fieldvalue) . "," .
                     ($fieldvalue) . "," .
                     ($j)          . ")" ) or die $DBI::errstr;
   }
   if ($duplicates > 500)
   {
      $j -= 1;
      $total = $j * ($i + 2);
      print "$total records: $j inserted with id1 = $fieldvalue\n";
   }
}


print "Retrieving inserted data from id1:\n";
$stmt=$dbh->prepare("select * from bench1 where id1=?");
$ok=0;
$nok=0;
for ($i=0; $i < $total_rows; $i += 2)
{
   $fieldvalue = $total_rows-1-$i;
   $n=$stmt->execute($fieldvalue) or  die $DBI::errstr;
   if ($n==$duplicates)
   {
    #print "ok $n\n";
    $ok++;
   }
   else
   {
    print "rows for id1=$fieldvalue not found but they exist $n\n";
    $nok++;
   }

   $fieldvalue = $i + 10;
   $n=$stmt->execute($fieldvalue) or  die $DBI::errstr;
   if ($n==$duplicates)
   {
    #print "ok $n\n";
    $ok++;
   }
   else
   {
    print "rows for id1=$fieldvalue not found but they exist $n\n";
    $nok++;
   }
}

print "Retrieving inserted data from id2:\n";
$stmt=$dbh->prepare("select * from bench1 where id2=?");
$ok=0;
$nok=0;
for ($i=0; $i < $total_rows; $i += 2)
{
   $fieldvalue = $total_rows-1-$i;
   $n=$stmt->execute($fieldvalue) or  die $DBI::errstr;
   if ($n==$duplicates)
   {
    #print "ok $n\n";
    $ok++;
   }
   else
   {
    print "rows for id2=$fieldvalue not found but they exist $n\n";
    $nok++;
   }

   $fieldvalue = $i + 10;
   $n=$stmt->execute($fieldvalue) or  die $DBI::errstr;
   if ($n==$duplicates)
   {
    #print "ok $n\n";
    $ok++;
   }
   else
   {
    print "rows for id2=$fieldvalue not found but they exist $n\n";
    $nok++;
   }
}

$half = $total_rows / 2;
print "Deleting where id1==$half\n";

$query="delete from bench1 where id1 = $half";

$sth = $dbh->do($query) or die $DBI::errstr;



$stmt->finish();
$dbh->disconnect();


print "Total ok=$ok nok=$nok\n";