#!/usr/bin/perl
# Copyright (C) 1997-1999 TcX AB & Monty Program KB & Detron HB
#
# This programs tries to find all limits for a sql server
# It gets the name from what it does to most servers :)
#
# This software is distributed with NO WARRANTY OF ANY KIND.  No author or
# distributor accepts any responsibility for the consequences of using it, or
# for whether it serves any particular purpose or works at all, unless he or
# she says so in writing.  Refer to the Free Public License (the "License")
# for full details.
#
# Be sure to use --help before running this!
#
# Written by Monty for the TCX/Monty Program/Detron benchmark suite.
# empress patches by Luuk de Boer
#
# If you want to add support for another server, add a new package for the
# server in server-cfg.  You only have to support the 'new' and 'version'
# functions. new doesn't need to have any limits if one doesn't want to
# use the benchmarks.
#

$version="1.30";

use DBI;
use Getopt::Long;
chomp($pwd = `pwd`); $pwd = "." if ($pwd eq '');
require "$pwd/server-cfg" || die "Can't read Configuration file: $!\n";

$opt_server="mysql"; $opt_host="localhost"; $opt_database="test";
$opt_dir="limits";
$opt_debug=$opt_help=$opt_Information=$opt_restart=$opt_force=$opt_quick=0;
$opt_log_all_queries=$opt_fix_limit_file=$opt_batch_mode=0;
$opt_db_start_cmd="";           # the db server start command
$opt_sleep=10;                  # time to sleep while starting the db server
$limit_changed=0;               # For configure file
$reconnect_count=0;
$opt_comment=$opt_config_file=$opt_log_queries_to_file="";
$limits{'crash_me_safe'}='yes';
$prompts{'crash_me_safe'}='crash me safe';
$limits{'operating_system'}= machine();
$prompts{'operating_system'}='crash-me tested on';
$retry_limit=3;

GetOptions("Information","help","server=s","debug","user=s","password=s","database=s","restart","force","quick","log-all-queries","comment=s","host=s","fix-limit-file","dir=s","db-start-cmd=s","sleep=s","batch-mode","config-file=s","log-queries-to-file=s") || usage();
usage() if ($opt_help || $opt_Information);

$opt_config_file="$pwd/$opt_dir/$opt_server.cfg" if (length($opt_config_file) == 0);

if ($opt_fix_limit_file)
{
  print "Fixing limit file for $opt_server\n";
  read_config_data();
  $limit_changed=1;
  save_all_config_data();
  exit 0;
}

$server=get_server($opt_server,$opt_host,$opt_database);
$opt_server=$server->{'cmp_name'};

$|=1;                           # For debugging

print "Running $0 $version on '",($server_version=$server->version()),"'\n\n";
print "I hope you didn't have anything important running on this server....\n";
read_config_data();
if ($limit_changed)             # Must have been restarted
{
  save_config_data('crash_me_safe','no',"crash me safe");
}

if (!$opt_force && !$opt_batch_mode)
{
  server_info();
}
else
{
  print "Using --force.  I assume you know what you are doing...\n";
}
print "\n";

save_config_data('crash_me_version',$version,"crash me version");
if ($server_version)
{
  save_config_data('server_version',$server_version,"server version");
}
if (length($opt_comment))
{
  save_config_data('user_comment',$opt_comment,"comment");
}

$opt_log=0;
if (length($opt_log_queries_to_file))
{
  open(LOG,">$opt_log_queries_to_file") || die "Can't open file $opt_log_queries_to_file\n";
  $opt_log=1;
}

#
# Set up some limits that's regared as unlimited
# We don't want to take up all resources from the server...
#

$max_connections="+1000";       # Number of simultanious connections
$max_buffer_size="+16000000";   # size of communication buffer.
$max_string_size="+8000000";    # Enough for this test
$max_name_length="+512";        # Actually 256, but ...
$max_keys="+64";                # Probably too big.
$max_join_tables="+64";         # Probably too big.
$max_columns="+8192";           # Probably too big.
$max_row_length=$max_string_size;
$max_key_length="+8192";        # Big enough
$max_order_by="+64";		# Big enough
$query_size=$max_buffer_size;
#
# First do some checks that needed for the rest of the benchmark
#
use sigtrap;		       # Must be removed with perl5.005_2 on Win98
$SIG{PIPE} = 'IGNORE';
$SIG{SEGV} = sub {warn('SEGFAULT')};
$dbh=safe_connect();
$dbh->do("drop table crash_me");        # Remove old run
$dbh->do("drop table crash_q");         # Remove old run

$prompt="Tables without primary key";
if (!safe_query(["create table crash_me (a integer not null,b char(10) not null)",
		 "insert into crash_me (a,b) values (1,'a')"]))
{
  if (!safe_query(["create table crash_me (a integer not null,b char(10) not null, primary key (a))",
		   "insert into crash_me (a,b) values (1,'a')"]))
  {
    die "Can't create table 'crash_me' with one record: $DBI::errstr\n";
  }
  save_config_data('no_primary_key',"no",$prompt);
}
else
{
  save_config_data('no_primary_key',"yes",$prompt);
}

print "$prompt: $limits{'no_primary_key'}\n";

report("SELECT without FROM",'select_without_from',"select 1");
if ($limits{'select_without_from'} ne "yes")
{
  $end_query=" from crash_me";
  $check_connect="select a from crash_me";
}
else
{
  $end_query="";
  $check_connect="select 1";
}

assert($check_connect);
assert("select a from crash_me where b<'b'");

report("Select constants",'select_constants',"select 1 $end_query");
report("Select table_name.*",'table_wildcard',
       "select crash_me.* from crash_me");
report("Allows \' and \" as string markers",'quote_with_"',
       'select a from crash_me where b<"c"');
check_and_report("Double '' as ' in strings",'double_quotes',[],
		 "select 'Walker''s' $end_query",[],"Walker's",1);
check_and_report("Multiple line strings","multi_strings",[],
		 "select a from crash_me where b < 'a'\n'b'",[],"1",0);
report("Column alias","column_alias","select a as ab from crash_me");
report("Table alias","table_alias","select b.a from crash_me as b");
report("Functions",'functions',"select 1+1 $end_query");
report("Group functions",'group_functions',"select count(*) from crash_me");
report("Group functions with distinct",'group_distinct_functions',
       "select count(distinct a) from crash_me");
report("Group by",'group_by',"select a from crash_me group by a");
report("Group by position",'group_by_position',
       "select a from crash_me group by 1");
report("Group by alias",'group_by_alias',
       "select a as ab from crash_me group by ab");
report("Order by",'order_by',"select a from crash_me order by a");
report("Order by position",'order_by_position',
       "select a from crash_me order by 1");
report("Order by function","order_by_function",
       "select a from crash_me order by a+1");
check_and_report("Order by DESC is remembered",'order_by_remember_desc',
		 ["create table crash_q (s int,s1 int)",
		  "insert into crash_q values(1,1)",
		  "insert into crash_q values(3,1)",
		  "insert into crash_q values(2,1)"],
		 "select s,s1 from crash_q order by s1 DESC,s",
		 ["drop table crash_q"],[3,2,1],7,undef(),3);
report("Compute",'compute',
       "select a from crash_me order by a compute sum(a) by a");
report("Value lists in INSERT",'multi_value_insert',
       "create table crash_q (s char(10))",
       "insert into crash_q values ('a'),('b')",
       "drop table crash_q");
report("INSERT with set syntax",'insert_with_set',
       "create table crash_q (a integer)",
       "insert into crash_q SET a=1",
       "drop table crash_q");
report("allows end ';'","end_colon", "select * from crash_me;");
report("SELECT with LIMIT","select_limit", "select * from crash_me limit 1");
report("SELECT with LIMIT #,#","select_limit2", "select * from crash_me limit 1,1");

# The following alter table commands MUST be kept together!
$dbh->do("create table crash_q (a integer, b integer,c CHAR(10))")  ||
  die "Can't create test table for alter table";
report("Alter table add column",'alter_add_col',
       "alter table crash_q add d integer");
report_one("Alter table add many columns",'alter_add_multi_col',
	   [["alter table crash_q add f integer,add g integer","yes"],
	    ["alter table crash_q add f integer,g integer","without add"]] );
report("Alter table change column",'alter_change_col',
       "alter table crash_q change a e char(50)");
# informix can only change data type with modify
report("Alter table modify column",'alter_modify_col',
       "alter table crash_q modify c CHAR(20)");
report("Alter table alter column",'alter_alter_col',
       "alter table crash_q alter b set default NULL");
report("Alter table drop column",'alter_drop_col',
       "alter table crash_q drop b");
report("Alter table rename table",'alter_rename_table',
       "alter table crash_q rename crash_q1");
# Make sure the both tables will be dropped, even if rename fails.
$dbh->do("drop table crash_q1");
$dbh->do("drop table crash_q");

check_and_report("case insensitive compare","case_insensitive_strings",
		 [],"select b from crash_me where b = 'A'",[],'a',1);
check_and_report("ignore end space in compare","ignore_end_space",
		 [],"select b from crash_me where b = 'a '",[],'a',1);
check_and_report("group on column with null values",'group_by_null',
		 ["create table crash_q (s char(10))",
		  "insert into crash_q values(null)",
		  "insert into crash_q values(null)"],
		 "select count(*) from crash_q group by s",
		 ["drop table crash_q"],2,0);

$prompt="Having";
if (!defined($limits{'having'}))
{                               # Complicated because of postgreSQL
  if (!safe_query_result("select a from crash_me group by a having a > 0",1,0))
  {
    if (!safe_query_result("select a from crash_me group by a having a < 0",
			   1,0))
    { save_config_data("having","error",$prompt); }
    else
    { save_config_data("having","yes",$prompt); }
  }
  else
  { save_config_data("having","no",$prompt); }
}
print "$prompt: $limits{'having'}\n";

if ($limits{'having'} eq 'yes')
{
  report("Having with group function","having_with_group",
	 "select a from crash_me group by a having count(*) = 1");
}

if ($limits{'column_alias'} eq 'yes')
{
  report("Order by alias",'order_by_alias',
	 "select a as ab from crash_me order by ab");
  if ($limits{'having'} eq 'yes')
  {
    report("Having on alias","having_with_alias",
	   "select a as ab from crash_me group by a having ab > 0");
  }
}
report("binary items (0x41)","binary_items","select 0x41 $end_query");

report_result("Value of logical operation (1=1)","logical_value",
	      "select (1=1) $end_query");

$logical_value= $limits{'logical_value'};

#
# Check how many connections the server can handle:
# We can't test unlimited connections, because this may take down the
# server...
#

$prompt="Simultaneous connections";
print "$prompt: ";
if (defined($limits{'connections'}))
{
  print "$limits{'connections'}\n";
}
else
{
  @connect=($dbh);

  for ($i=1; $i < $max_connections ; $i++)
  {
    if (!($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
			  { PrintError => 0})))
    {
      print "Last connect error: $DBI::errstr\n" if ($opt_debug);
      last;
    }
    $dbh->{LongReadLen}= 16000000; # Set retrieval buffer
    print "." if ($opt_debug);
    push(@connect,$dbh);
  }
  print "$i\n";
  save_config_data('connections',$i,$prompt);
  foreach $dbh (@connect)
  {
    print "#" if ($opt_debug);
    $dbh->disconnect;           # close connection
  }

  $#connect=-1;                 # Free connections

  if ($i == 0)
  {
    print "Can't connect to server: $DBI::errstr.  Please start it and try again\n";
    exit 1;
  }
  $dbh=safe_connect();
}


#
# Check size of communication buffer, strings...
#

$prompt="query size";
print "$prompt: ";
if (!defined($limits{'query_size'}))
{
  $query="select ";
  $first=64;
  $end=$max_buffer_size;
  $select= $limits{'select_without_from'} eq 'yes' ? 1 : 'a';

  assert($query . "$select$end_query");

  $first=$limits{'restart'}{'low'} if ($limits{'restart'}{'low'});

  if ($limits{'restart'}{'tohigh'})
  {
    $end = $limits{'restart'}{'tohigh'} - 1;
    print "\nRestarting this with low limit: $first and high limit: $end\n";
    delete $limits{'restart'};
    $first=$first+int(($end-$first+4)/5);           # Prefere lower on errors
  }
  for ($i=$first ; $i < $end ; $i*=2)
  {
    last if (!safe_query($query . (" " x ($i - length($query)-length($end_query) -1)) . "$select$end_query"));
    $first=$i;
    save_config_data("restart",$i,"") if ($opt_restart);
  }
  $end=$i;

  if ($i < $max_buffer_size)
  {
    while ($first != $end)
    {
      $i=int(($first+$end+1)/2);
      if (safe_query($query .
		     (" " x ($i - length($query)-length($end_query) -1)) .
		     "$select$end_query"))
      {
	$first=$i;
      }
      else
      {
	$end=$i-1;
      }
    }
  }
  save_config_data('query_size',$end,$prompt);
}
$query_size=$limits{'query_size'};

print "$limits{'query_size'}\n";
#
# Test database types
#

@sql_types=("character(1)","char(1)","char varying(1)", "character varying(1)",
	    "varchar(1)",
	    "integer","int","numeric(9,2)","decimal(6,2)","dec(6,2)",
	    "bit", "bit(2)","bit varying(2)","float","float(6,2)","real",
	    "double","double precision", "date","time","timestamp",
	    "interval year");
@odbc_types=("binary(1)","varbinary(1)","tinyint","smallint","bigint",
	     "datetime");
@extra_types=("blob","byte","long varbinary","image","text","text(10)",
	      "mediumtext",
	      "char2","char4","char8","char16","nvarchar(6,2)","nchar(1)",
	      "long varchar(1)",
	      "mediumint","middleint","int unsigned",
	      "int1","int2","int3","int4","int8","uint",
	      "money","smallmoney","float4","float8","smallfloat",
	      "enum('red')","set('red')", "int(5) zerofill", "serial",
	      "char(10) binary","int not null auto_increment,unique(q)",
	      "abstime","year","datetime","smalldatetime","timespan","reltime",
	      "box","bool","circle","polygon","point","line","lseg","path", # postgres types
	      "varchar2(16)","nvarchar2(16)","number(9,2)","number(9)","number","float(6)", # oracle types
		"long","raw(16)","long raw","rowid","mlslabel","clob","nclob","bfile" # oracle types
	      );

@types=(["sql",\@sql_types],
	["odbc",\@odbc_types],
	["extra",\@extra_types]);

foreach $types (@types)
{
  print "\nSupported $types->[0] types\n";
  $tmp=@$types->[1];
  foreach $use_type (@$tmp)
  {
    $type=$use_type;
    $type =~ s/\(.*\)/(1 arg)/;
    if (index($use_type,",")>= 0)
    {
      $type =~ s/\(1 arg\)/(2 arg)/;
    }
    if (($tmp2=index($type,",unique")) >= 0)
    {
      $type=substr($type,0,$tmp2);
    }
    $tmp2=$type;
    $tmp2 =~ s/ /_/g;
    $tmp2 =~ s/_not_null//g;
    report("Type $type","type_$types->[0]_$tmp2",
	   "create table crash_q (q $use_type)",
	   "drop table crash_q");
  }
}

#
# Test some type limits
#

check_and_report("Remembers end space in char()","remember_end_space",
		 ["create table crash_q (a char(10))",
		  "insert into crash_q values('hello ')"],
		 "select a from crash_q where a = 'hello '",
		 ["drop table crash_q"],
		 'hello ',6);

check_and_report("Remembers end space in varchar()",
		 "remember_end_space_varchar",
		 ["create table crash_q (a varchar(10))",
		  "insert into crash_q values('hello ')"],
		 "select a from crash_q where a = 'hello '",
		 ["drop table crash_q"],
		 'hello ',6);

check_and_report("Supports 0000-00-00 dates","date_zero",
		 ["create table crash_me2 (a date not null)",
		  "insert into crash_me2 values ('0000-00-00')"],
		 "select a from crash_me2",
		 ["drop table crash_me2"],
		 "0000-00-00",1);

if (!defined($limits{'date_with_YY'}))
{
    check_and_report("Supports YY-MM-DD dates","date_with_YY",
		     ["create table crash_me2 (a date not null)",
		      "insert into crash_me2 values ('98-03-03')"],
		     "select a from crash_me2",
		     ["drop table crash_me2"],
		     "1998-03-03",5);
    if ($limits{'date_with_YY'} eq "yes")
    {
	undef($limits{'date_with_YY'});
	check_and_report("Supports YY-MM-DD 2000 compilant dates",
			 "date_with_YY",
			 ["create table crash_me2 (a date not null)",
			  "insert into crash_me2 values ('10-03-03')"],
			 "select a from crash_me2",
			 ["drop table crash_me2"],
			 "2010-03-03",5);
    }
}

if ($limits{'type_sql_float(2_arg)'} eq "yes" ||
    $limits{'type_sql_decimal(2_arg)'} eq "yes")
{
  my $type=$limits{'type_sql_float(2_arg)'} eq "yes" ? "float(4,1)" :
    "decimal(4,1)";
  if (check_and_report("Correct rounding when storing float values",
		       "round_on_store",
		       ["create table crash_q (q1 $type)",
			"insert into crash_q values(1.14)"],
		       "select q1 from crash_q",
		       ["drop table crash_q"],1.10,0,
		       undef(),1))
  {
    check_and_report("Correct rounding when storing float values",
		     "round_on_store",
		     ["create table crash_q1 (q1 $type)",
		      "insert into crash_q1 values(1.16)"],
		     "select q1 from crash_q1",
		     ["drop table crash_q1"],1.20,0,
		     1);
  }
}
#
# Test functions
#

@sql_functions=
  (["+, -, * and /","+","5*3-4/2+1",14,0],
   ["concatenation with ||","concat_as_||","'abc' || 'def'","abcdef",1],
   ["BIT_LENGTH","bit_length","bit_length('abc')",24,0],
   ["CHAR_LENGTH","char_length","char_length('abcd')","4",0],
   ["CAST","cast","CAST(1 as CHAR)","1",1],
   ["CASE","case","case when 1 > 2 then 'false' when 2 > 1 then 'true' end",
    "true",1],
   ["CHARACTER_LENGTH","character_length","character_length('abcd')","4",0],
   ["CURRENT_DATE()","current_date()","current_date()",0,2],
   ["CURRENT_DATE","current_date","current_date",0,2],
   ["CURRENT_TIME()","current_time()","current_time()",0,2],
   ["CURRENT_TIME","current_time","current_time",0,2],
   ["CURRENT_TIMESTAMP()","current_timestamp()","current_timestamp()",0,2],
   ["CURRENT_TIMESTAMP","current_timestamp","current_timestamp",0,2],
   ["LOWER","lower","LOWER('ABC')","abc",1],
   ["OCTET_LENGTH","octet_length","octet_length('abc')",3,0],
   ["POSITION","position","position('ll' in 'hello')",3,0],
   ["SESSION_USER","session_user","session_user()",0,2],
   ["ANSI SQL SUBSTRING","substring","substring('abcd' from 2 for 2)","bc",1],
   ["SYSDATE","sysdate","sysdate()",0,2],
   ["SYSTEM_USER","system_user","system_user()",0,2],
   ["TRIM","trim","trim(trailing from trim(LEADING FROM ' abc '))","abc",1],
   ["UPPER","upper","UPPER('abc')","ABC",1],
   );

@odbc_functions=
  (["ASCII", "ascii", "ASCII('A')","65",0],
   ["CHAR", "char", "CHAR(65)"  ,"A",1],
   ["CONCAT(2 arg)","concat", "concat('a','b')","ab",1],
   ["DIFFERENCE()","difference","difference('abc','abe')",0,2],
   ["INSERT","insert","insert('abcd',2,2,'ef')","aefd",1],
   ["LEFT","left","left('abcd',2)","ab",1],
   ["LTRIM","ltrim","ltrim('   abcd')","abcd",1],
   ["REAL LENGTH","length","length('abcd ')","5",0],
   ["ODBC LENGTH","length_without_space","length('abcd ')","4",0],
   ["LOCATE(2 arg)","locate_2","locate('bcd','abcd')","2",0],
   ["LOCATE(3 arg)","locate_3","locate('bcd','abcd',3)","0",0],
   ["LCASE","lcase","lcase('ABC')","abc",1],
   ["REPEAT","repeat","repeat('ab',3)","ababab",1],
   ["REPLACE","replace","replace('abbaab','ab','ba')","bababa",1],
   ["RIGHT","right","right('abcd',2)","cd",1],
   ["RTRIM","rtrim","rtrim(' abcd  ')"," abcd",1],
   ["SPACE","space","space(5)","     ",3],
   ["SOUNDEX","soundex","soundex('hello')",0,2],
   ["ODBC SUBSTRING","substring","substring('abcd',3,2)","cd",1],
   ["UCASE","ucase","ucase('abc')","ABC",1],

   ["ABS","abs","abs(-5)",5,0],
   ["ACOS","acos","acos(0)","1.570796",0],
   ["ASIN","asin","asin(1)","1.570796",0],
   ["ATAN","atan","atan(1)","0.785398",0],
   ["ATAN2","atan2","atan2(1,0)","1.570796",0],
   ["CEILING","ceiling","ceiling(-4.5)",-4,0],
   ["COS","cos","cos(0)","1.00000",0],
   ["COT","cot","cot(1)","0.64209262",0],
   ["DEGREES","degrees","degrees(6.283185)","360",0],
   ["EXP","exp","exp(1)","2.718282",0],
   ["FLOOR","floor","floor(2.5)","2",0],
   ["LOG","log","log(2)","0.693147",0],
   ["LOG10","log10","log10(10)","1",0],
   ["MOD","mod","mod(11,7)","4",0],
   ["PI","pi","pi()","3.141593",0],
   ["POWER","power","power(2,4)","16",0],
   ["RAND","rand","rand(1)",0,2],       # Any value is acceptable
   ["RADIANS","radians","radians(360)","6.283185",0],
   ["ROUND(2 arg)","round","round(5.63,2)","5.6",0],
   ["SIGN","sign","sign(-5)",-1,0],
   ["SIN","sin","sin(1)","0.841471",0],
   ["SQRT","sqrt","sqrt(4)",2,0],
   ["TAN","tan","tan(1)","1.557408",0],
   ["TRUNCATE","truncate","truncate(18.18,-1)",10,0],
   ["NOW","now","now()",0,2],           # Any value is acceptable
   ["CURDATE","curdate","curdate()",0,2],
   ["DAYNAME","dayname","dayname(DATE '1997-02-01')","",2],
   ["MONTH","month","month(DATE '1997-02-01')","",2],
   ["MONTHNAME","monthname","monthname(DATE '1997-02-01')","",2],
   ["DAYOFMONTH","dayofmonth","dayofmonth(DATE '1997-01-02')",2,0],
   ["DAYOFWEEK","dayofweek","dayofweek(DATE '1997-01-02')",5,0],
   ["DAYOFYEAR","dayofyear","dayofyear(DATE '1997-02-01')",32,0],
   ["EXTRACT","extract","extract(YEAR from '1997-02-01')","",2],
   ["QUARTER","quarter","quarter(DATE '1997-01-02')",1,0],
   ["WEEK","week","week(DATE '1997-01-01')",1,0],
   ["YEAR","year","year(DATE '1997-01-02')",1997,0],
   ["CURTIME","curtime","curtime()",0,2],
   ["HOUR","hour","hour('12:13:14')",12,0],
   ["ANSI HOUR","hour_time","hour(TIME '12:13:14')",12,0],
   ["MINUTE","minute","minute('12:13:14')",13,0],
   ["SECOND","second","second('12:13:14')",14,0],
   ["TIMESTAMPADD","timestampadd",
    "timestampadd(SQL_TSI_SECOND,1,DATE '1997-01-01 00:00:00')",
    "1997-01-01 00:00:01",1],
   ["TIMESTAMPDIFF","timestampdiff",
    "timestampdiff(SQL_TSI_SECOND,DATE '1997-01-01 00:00:01', DATE '1997-01-01 00:00:01')","1",0],
   ["USER()","user()","user()",0,2],
   ["DATABASE","database","database()",0,2],
   ["IFNULL","ifnull","ifnull(2,3)",2,0],
   ["ODBC syntax LEFT & RIGHT", "fn_left",
    "{ fn LEFT( { fn RIGHT('abcd',2) },1) }","c",1],
   );

@extra_functions=
  (
   ["& (bitwise and)",'&',"5 & 3",1,0],
   ["| (bitwise or)",'|',"1 | 2",3,0],
   ["<< and >> (bitwise shifts)",'binary_shifts',"(1 << 4) >> 2",4,0],
   ["<> in SELECT","<>","1<>1","0",0],
   ["=","=","(1=1)",1,$logical_value],
   ["ADD_MONTHS","add_months","add_months('1997-01-01',1)","1997-02-01",0], # oracle the date plus n months
   ["AND and OR in SELECT","and_or","1=1 AND 2=2",$logical_value,0],
   ["AND as '&&'",'&&',"1=1 && 2=2",$logical_value,0],
   ["ATN2","atn2","atn2(1,0)","1.570796",0],
   ["BETWEEN in SELECT","between","5 between 4 and 6",$logical_value,0],
   ["BIT_COUNT","bit_count","bit_count(5)",2,0],
   ["CEIL","ceil","ceil(-4.5)",-4,0], # oracle
   ["CHARINDEX","charindex","charindex('a','crash')",3,0],
   ["CHR", "chr", "CHR(65)"  ,"A",1], # oracle
   ["COALESCE", "coalesce", "coalesce(NULL,'A')", "A", 1],
   ["CONCAT(list)","concat_list", "concat('a','b','c','d')","abcd",1],
   ["CONVERT","convert","convert(CHAR,5)","5",1],
   ["COSH","cosh","cosh(0)","1",0], # oracle hyperbolic cosine of n.
   ["DATEADD","dateadd","dateadd(day,3,'Nov 30 1997')",0,2],
   ["DATEDIFF","datediff","datediff(month,'Oct 21 1997','Nov 30 1997')",0,2],
   ["DATENAME","datename","datename(month,'Nov 30 1997')",0,2],
   ["DATEPART","datepart","datepart(month,'July 20 1997')",0,2],
   ["DATE_FORMAT","date_format", "date_format('1997-01-02 03:04:05','M W D Y y m d h i s w')", 0,2],
   ["ELT","elt","elt(2,'ONE','TWO','THREE')","TWO",1],
   ["ENCRYPT","encrypt","encrypt('hello')",0,2],
   ["FIELD","field","field('IBM','NCA','ICL','SUN','IBM','DIGITAL')",4,0],
   ["FORMAT","format","format(1234.5555,2)","1,234.56",1],
   ["FROM_DAYS","from_days","from_days(729024)","1996-01-01",1],
   ["FROM_UNIXTIME","from_unixtime","from_unixtime(0)",0,2],
   ["GETDATE","getdate","getdate()",0,2],
   ["GREATEST","greatest","greatest('HARRY','HARRIOT','HAROLD')","HARRY",1], # oracle
   ["IF","if", "if(5,6,7)",6,0],
   ["IN on numbers in SELECT","in_num","2 in (3,2,5,9,5,1)",$logical_value,0],
   ["IN on strings in SELECT","in_str","'monty' in ('david','monty','allan')", $logical_value,0],
   ["INITCAP","initcap","initcap('the soap')","The Soap",1], # oracle Returns char, with the first letter of each word in uppercase
   ["INSTR (Oracle syntax)", "instr_oracle", "INSTR('CORPORATE FLOOR','OR',3,2)"  ,"14",0], # oracle instring
   ["INSTRB", "instrb", "INSTRB('CORPORATE FLOOR','OR',5,2)"  ,"27",0], # oracle instring in bytes
   ["INTERVAL","interval","interval(55,10,20,30,40,50,60,70,80,90,100)",5,0],
   ["LAST_DAY","last_day","last_day('1997-04-01')","1997-04-30",0], # oracle last day of month of date
   ["LAST_INSERT_ID","last_insert_id","last_insert_id()",0,2],
   ["LEAST","least","least('HARRY','HARRIOT','HAROLD')","HAROLD",1], # oracle
   ["LENGTHB","lengthb","lengthb('CANDIDE')","14",0], # oracle length in bytes
   ["LIKE ESCAPE in SELECT","like_escape","'%' like 'a%' escape 'a'",$logical_value,0],
   ["LIKE in SELECT","like","'a' like 'a%'",$logical_value,0],
   ["LN","ln","ln(95)","4.55387689",0], # oracle natural logarithm of n
   ["LOCATE as INSTR","instr","instr('hello','ll')",3,0],
   ["LOG(m,n)","log(m_n)","log(10,100)","2",0], # oracle logarithm, base m, of n
   ["LOGN","logn","logn(2)","0.693147",0], # informix
   ["LPAD","lpad","lpad('hi',4,'??')",'??hi',3],
   ["MDY","mdy","mdy(7,1,1998)","1998-07-01",0], # informix
   ["MOD as %","%","10%7","3",0],
   ["MONTHS_BETWEEN","months_between","months_between('1997-02-02','1997-01-01')","1.03225806",0], # oracle number of months between 2 dates
   ["NOT BETWEEN in SELECT","not_between","5 not between 4 and 6",0,0],
   ["NOT LIKE in SELECT","not_like","'a' not like 'a%'",0,0],
   ["NOT as '!' in SELECT","!","! 1",0,0],
   ["NOT in SELECT","not","not 0",$logical_value,0],
   ["ODBC CONVERT","odbc_convert","convert(5,SQL_CHAR)","5",1],
   ["OR as '||'",'||',"1=0 || 1=1",$logical_value,0],
   ["PASSWORD","password","password('hello')",0,2],
   ["PATINDEX","patindex","patindex('%a%','crash')",3,0],
   ["PERIOD_ADD","period_add","period_add(9602,-12)",199502,0],
   ["PERIOD_DIFF","period_diff","period_diff(199505,199404)",13,0],
   ["POW","pow","pow(3,2)",9,0],
   ["RANGE","range","range(a)","0.0",0], # informix range(a) = max(a) - min(a)
   ["REGEXP in SELECT","regexp","'a' regexp '^(a|b)*\$'",$logical_value,0],
   ["REPLICATE","replicate","replicate('a',5)","aaaaa",1],
   ["REVERSE","reverse","reverse('abcd')","dcba",1],
   ["ROOT","root","root(4)",2,0], # informix
   ["ROUND(1 arg)","round1","round(5.63)","6",0],
   ["RPAD","rpad","rpad('hi',4,'??')",'hi??',3],
   ["SEC_TO_TIME","sec_to_time","sec_to_time(5001)","01:23:21",1],
   ["SINH","sinh","sinh(1)","1.17520119",0], # oracle hyperbolic sine of n
   ["STR","str","str(123.45,5,1)",123.5,3],
   ["STRCMP","strcmp","strcmp('abc','adc')",-1,0],
   ["STUFF","stuff","stuff('abc',2,3,'xyz')",'axyz',3],
   ["SUBSTRB", "substrb", "SUBSTRB('ABCDEFG',5,4.2)"  ,"CD",1], # oracle substring with bytes
   ["SUBSTRING as MID","mid","mid('hello',3,2)","ll",1],
   ["SUBSTRING_INDEX","substring_index","substring_index('www.tcx.se','.',-2)", "tcx.se",1],
   ["TANH","tanh","tanh(1)","0.462117157",0], # oracle hyperbolic tangent of n
   ["TIME_TO_SEC","time_to_sec","time_to_sec('01:23:21')","5001",0],
   ["TO_DAYS","to_days","to_days(DATE '1996-01-01')",729024,0],
   ["TRANSLATE","translate","translate('abc','bc','de')",'ade',3],
   ["TRIM; Many char extension","trim_many_char","trim(':!' FROM ':abc!')","abc",3],
   ["TRIM; Substring extension","trim_substring","trim('cb' FROM 'abccb')","abc",3],
   ["TRUNC","trunc","trunc(18.18,-1)",10,0], # oracle
   ["UID","uid","uid",0,2], # oracle uid from user
   ["UNIX_TIMESTAMP","unix_timestamp","unix_timestamp()",0,2],
   ["USERENV","userenv","userenv",0,2], # oracle user enviroment
   ["VERSION","version","version()",0,2],
   ["WEEKDAY","weekday","weekday(DATE '1997-11-29')",5,0],
   ["automatic num->string convert","auto_num2string","concat('a',2)","a2",1],
   ["automatic string->num convert","auto_string2num","'1'+2",3,0],
   ["concatenation with +","concat_as_+","'abc' + 'def'","abcdef",1],
   );

@sql_group_functions=
  (
   ["AVG","avg","avg(a)",1,0],
   ["COUNT (*)","count_*","count(*)",1,0],
   ["COUNT column name","count_column","count(a)",1,0],
   ["COUNT(DISTINCT expr)","count_distinct","count(distinct a)",1,0],
   ["MAX on numbers","max","max(a)",1,0],
   ["MAX on strings","max_str","max(b)","a",1],
   ["MIN on numbers","min","min(a)",1,0],
   ["MIN on strings","min_str","min(b)","a",1],
   ["SUM","sum","sum(a)",1,0],
   );

@extra_group_functions=
  (
   ["BIT_AND",'bit_and',"bit_and(a)",1,0],
   ["BIT_OR", 'bit_or', "bit_or(a)",1,0],
   ["COUNT(DISTINCT expr,expr,...)","count_distinct_list","count(distinct a,b)",1,0],
   ["STD","std","std(a)",0,0],
   ["STDDEV","stddev","stddev(a)",0,0],
   ["VARIANCE","variance","variance(a)",0,0],
   );

@where_functions=
(
 ["= ALL","eq_all","b =all (select b from crash_me)",1,0],
 ["= ANY","eq_any","b =any (select b from crash_me)",1,0],
 ["= SOME","eq_some","b =some (select b from crash_me)",1,0],
 ["BETWEEN","between","5 between 4 and 6",1,0],
 ["EXISTS","exists","exists (select * from crash_me)",1,0],
 ["IN on numbers","in_num","2 in (3,2,5,9,5,1)",1,0],
 ["LIKE ESCAPE","like_escape","b like '%' escape 'a'",1,0],
 ["LIKE","like","b like 'a%'",1,0],
 ["MATCH UNIQUE","match_unique","1 match unique (select a from crash_me)",1,0],
 ["MATCH","match","1 match (select a from crash_me)",1,0],
 ["MATCHES","matches","b matcjhes 'a*'",1,0],
 ["NOT BETWEEN","not_between","7 not between 4 and 6",1,0],
 ["NOT EXISTS","not_exists","not exists (select * from crash_me where a = 2)",1,0],
 ["NOT LIKE","not_like","b not like 'b%'",1,0],
 ["NOT UNIQUE","not_unique","not unique (select * from crash_me where a = 2)",1,0],
 ["UNIQUE","unique","unique (select * from crash_me)",1,0],
 );

@types=(["sql",\@sql_functions,0],
	["odbc",\@odbc_functions,0],
	["extra",\@extra_functions,0],
	["where",\@where_functions,0]);

@group_types=(["sql",\@sql_group_functions,0],
	      ["extra",\@extra_group_functions,0]);


foreach $types (@types)
{
  print "\nSupported $types->[0] functions\n";
  $tmp=@$types->[1];
  foreach $type (@$tmp)
  {
    if ($types->[0] eq "where")
    {
      check_and_report("Function $type->[0]","func_$types->[0]_$type->[1]",
		       [],"select a from crash_me where $type->[2]",[],
		       $type->[3],$type->[4]);
    }
    elsif ($limits{'functions'} eq 'yes')
    {
      if (!check_and_report("Function $type->[0]",
			    "func_$types->[0]_$type->[1]",
			    [],"select $type->[2] $end_query",[],
			    $type->[3],$type->[4]))
      {
	# check without type specifyer
	if ($type->[2] =~ /DATE /)
	{
	  my $tmp= $type->[2];
	  $tmp =~ s/DATE //;
	  undef($limits{"func_$types->[0]_$type->[1]"});
	  check_and_report("Function $type->[0]",
			   "func_$types->[0]_$type->[1]",
			   [],"select $tmp $end_query",[],
			   $type->[3],$type->[4]);
	}
      }
    }
  }
}

if ($limits{'functions'} eq 'yes')
{
  foreach $types (@group_types)
  {
    print "\nSupported $types->[0] group functions\n";
    $tmp=@$types->[1];
    foreach $type (@$tmp)
    {
      check_and_report("Group function $type->[0]",
		       "group_func_$types->[0]_$type->[1]",
		       [],"select $type->[2],a from crash_me group by a",[],
		       $type->[3],$type->[4]);
    }
  }
  print "\n";
  report("mixing of integer and float in expression","float_int_expr",
	 "select 1+1.0 $end_query");

  check_and_report("Is 1+NULL = NULL","null_num_expr",
		   [],"select 1+NULL $end_query",[],undef(),4);
  $tmp=sql_concat("'a'","NULL");
  if (defined($tmp))
  {
    check_and_report("Is $tmp = NULL", "null_concat_expr", [],
		     "select $tmp $end_query",[], undef(),4);
  }
}
else
{
  print "\n";
}


report("LIKE on numbers","like_with_number",
       "create table crash_q (a int,b int)",
       "insert into crash_q values(10,10)",
       "select * from crash_q where a like '10'",
       "drop table crash_q");

report("column LIKE column","like_with_column",
       "create table crash_q (a char(10),b char(10))",
       "insert into crash_q values('abc','abc')",
       "select * from crash_q where a like b",
       "drop table crash_q");

report("update of column= -column","NEG",
       "create table crash_q (a integer)",
       "insert into crash_q values(10)",
       "update crash_q set a=-a",
       "drop table crash_q");

if ($limits{'func_odbc_left'} eq 'yes' ||
    $limits{'func_odbc_substring'} eq 'yes')
{
  my $type= ($limits{'func_odbc_left'} eq 'yes' ?
	     "left(a,4)" : "substring(a,1,4)");

    check_and_report("String functions on date columns","date_as_string",
		     ["create table crash_me2 (a date not null)",
		      "insert into crash_me2 values ('1998-03-03')"],
		     "select $type from crash_me2",
		     ["drop table crash_me2"],
		     "1998",1);
}


$tmp=sql_concat("b","b");
if (defined($tmp))
{
  check_and_report("char are space filled","char_is_space_filled",
		   [],"select $tmp from crash_me",[],
		   'b         b         ',6);
}

if (!defined($limits{'multi_table_update'}))
{
  if (check_and_report("Update with many tables","multi_table_update",
		   ["create table crash_q (a integer,b char(10))",
		    "insert into crash_q values(1,'c')",
		    "update crash_q left join crash_me on crash_q.a=crash_me.a set crash_q.b=crash_me.b"],
		   "select b from crash_q",
		   ["drop table crash_q"],
		   "a",1,undef(),2))
  {
    check_and_report("Update with many tables","multi_table_update",
		     ["create table crash_q (a integer,b char(10))",
		      "insert into crash_q values(1,'c')",
		      "update crash_q,crash_me set crash_q.b=crash_me.b where crash_q.a=crash_me.a"],
		     "select b from crash_q",
		     ["drop table crash_q"],
		     "a",1,
		    1);
  }
}

report("DELETE FROM table1,table2...","multi_table_delete",
       "create table crash_q (a integer,b char(10))",
       "insert into crash_q values(1,'c')",
       "delete crash_q from crashq,crash_me where crash_q.a=crash_me.a",
       "drop table crash_q");

check_and_report("Update with sub select","select_table_update",
		 ["create table crash_q (a integer,b char(10))",
		  "insert into crash_q values(1,'c')",
		  "update crash_q set crash_q.b= (select b from crash_me where crash_q.a = crash_me.a)"],
		 "select b from crash_q",
		 ["drop table crash_q"],
		 "a",1);

check_and_report("Calculate 1--1","minus_neg",[],
		 "select a--1 from crash_me",[],0,2);

report("ANSI SQL simple joins","simple_joins",
       "select crash_me.a from crash_me, crash_me t0");

#
# Check max string size, and expression limits
#

find_limit(($prompt="constant string size in where"),"where_string_size",
	   new query_repeat([],"select a from crash_me where b <'",
			    "","","a","","'"));
if ($limits{'where_string_size'} == 10)
{
  save_config_data('where_string_size','nonstandard',$prompt);
}

if ($limits{'select_constants'} eq 'yes')
{
  find_limit("constant string size in SELECT","select_string_size",
	     new query_repeat([],"select '","","","a","","'$end_query"));
}

goto no_functions if ($limits{'functions'} ne "yes");

if ($limits{'func_odbc_repeat'} eq 'yes')
{
  find_limit("return string size from function","repeat_string_size",
	     new query_many([],
			    "select repeat('a',%d) $end_query","%s",
			    [],
			    $max_string_size,0));
}

$tmp=find_limit("simple expressions","max_expressions",
		new query_repeat([],"select 1","","","+1","",$end_query));

if ($tmp > 10)
{
  $tmp= "(1" . ( '+1' x ($tmp-10) ) . ")";
  find_limit("big expressions", "max_big_expressions",
	     new query_repeat([],"select 0","","","+$tmp","",$end_query));
}

find_limit("stacked expressions", "max_stack_expression",
	   new query_repeat([],"select 1","","","+(1",")",$end_query));

no_functions:

if (!defined($limits{'max_conditions'}))
{
  find_limit("OR and AND in WHERE","max_conditions",
	     new query_repeat([],
			      "select a from crash_me where a=1 and b='a'","",
			      "", " or a=%d and b='%d'","","","",
			      [],($query_size-42)/19,undef,2));
  $limits{'max_conditions'}*=2;
}

find_limit("tables in join", "join_tables",
	   new query_repeat([],
			    "select crash_me.a",",t%d.a","from crash_me",
			    ",crash_me t%d","","",[],$max_join_tables,undef,
			    1));

# Different CREATE TABLE options

report("primary key in create table",'primary_key_in_create',
       "create table crash_q (q integer not null,primary key (q))",
       "drop table crash_q");

report("unique in create table",'unique_in_create',
       "create table crash_q (q integer not null,unique (q))",
       "drop table crash_q");

if ($limits{'unique_in_create'} eq 'yes')
{
  report("unique null in create",'unique_null_in_create',
	 "create table crash_q (q integer,unique (q))",
	 "insert into crash_q (q) values (NULL)",
	 "insert into crash_q (q) values (NULL)",
	 "insert into crash_q (q) values (1)",
	 "drop table crash_q");
}

report("default value for column",'create_default',
       "create table crash_q (q integer not null default 10)",
       "drop table crash_q");

report("default value function for column",'create_default_func',
       "create table crash_q (q integer not null,q1 integer default q+1)",
       "drop table crash_q");

report("temporary tables",'tempoary_table',
       "create temporary table crash_q (q integer not null)",
       "drop table crash_q");

report("create table from select",'create_table_select',
       "create table crash_q SELECT * from crash_me",
       "drop table crash_q");

report("index in create table",'index_in_create',
       "create table crash_q (q integer not null,index (q))",
       "drop table crash_q");

# The following must be executed as we need the value of end_drop_keyword
# later

if ($res=safe_query("create index crash_q on crash_me (a)"))
{
  $res="yes";
  $drop_res="yes";
  $end_drop_keyword="";
  if (!safe_query("drop index crash_q"))
  {
    # Can't drop the standard way; Check if mSQL
    if (safe_query("drop index crash_q from crash_me"))
    {
      $drop_res="with 'FROM'";	# Drop is not ANSI SQL
      $end_drop_keyword="drop index %i from %t";
    }
    # else check if Access or MySQL
    elsif (safe_query("drop index crash_q on crash_me"))
    {
      $drop_res="with 'ON'";	# Drop is not ANSI SQL
      $end_drop_keyword="drop index %i on %t";
    }
    # else check if MS-SQL
    elsif (safe_query("drop index crash_me.crash_q"))
    {
      $drop_res="with 'table.index'"; # Drop is not ANSI SQL
      $end_drop_keyword="drop index %t.%i";
    }
  }
  else
  {
    # Old MySQL 3.21 supports only the create index syntax
    # This means that the second create doesn't give an error.
    $res=safe_query(["create index crash_q on crash_me (a)",
		     "create index crash_q on crash_me (a)",
		     "drop index crash_q"]);
    $res= $res ? 'ignored' : 'yes';
  }
}
else
{
  $drop_res=$res='no';
}
save_config_data('create_index',$res,"create index");
save_config_data('drop_index',$drop_res,"drop index");

print "create index: $limits{'create_index'}\n";
print "drop index: $limits{'drop_index'}\n";

# check if we can have 'NULL' as a key
if ($limits{'index_in_create'} eq 'yes')
{
  $tmp=["create table crash_q (a char(10),index (a))",
	"insert into crash_q values (NULL)"];
}
else
{
  $tmp=["create table crash_q (a char(10))",
	"create index crash_q_index on crash_q (a)",
	"insert into crash_q values (NULL)"];
}
check_and_report("null in index","null_in_index",
		 $tmp,
		 "select * from crash_q",
		 ["drop table crash_q"],
		 undef(),4);

if ($limits{'unique_in_create'} eq 'yes')
{
  report("null in unique",'null_in_unique',
	 "create table crash_q (q integer,unique (q))",
	 "drop table crash_q");
}



if ($limits{'create_index'} ne 'no')
{
  $end_drop=$end_drop_keyword;
  $end_drop =~ s/%i/crash_q/;
  $end_drop =~ s/%t/crash_me/;
  report("index on column part (extension)","index_parts",,
	 "create index crash_q on crash_me (b(5))",
	 $end_drop);
  $end_drop=$end_drop_keyword;
  $end_drop =~ s/%i/crash_me/;
  $end_drop =~ s/%t/crash_me/;
  report("different namespace for index",
	 "index_namespace",
	 "create index crash_me on crash_me (b)",
	 $end_drop);
}

if (!report("case independent table names","table_name_case",
	    "create table crash_q (q integer)",
	    "drop table CRASH_Q"))
{
  safe_query("drop table crash_q");
}

if (!report("drop table if exists","drop_if_exists",
	    "create table crash_q (q integer)",
	    "drop table if exists crash_q"))
{
  safe_query("drop table crash_q");
}

report("create table if not exists","create_if_not_exists",
       "create table crash_q (q integer)",
       "create table if not exists crash_q (q integer)",
       "drop table if exists crash_q");

#
# test name limits
#

find_limit("table name length","max_table_name",
	   new query_many(["create table crash_q%s (q integer)",
			   "insert into crash_q%s values(1)"],
			   "select * from crash_q%s",1,
			   ["drop table crash_q%s"],
			   $max_name_length,7,1));

find_limit("column name length","max_column_name",
	   new query_many(["create table crash_q (q%s integer)",
			  "insert into crash_q (q%s) values(1)"],
			  "select q%s from crash_q",1,
			  ["drop table crash_q"],
			   $max_name_length,1));

if ($limits{'column_alias'} eq 'yes')
{
  find_limit("select alias name length","max_select_alias_name",
	   new query_many(undef,
			  "select b as %s from crash_me",undef,
			  undef, $max_name_length));
}

find_limit("table alias name length","max_table_alias_name",
	   new query_many(undef,
			  "select %s.b from crash_me %s",
			  undef,
			  undef, $max_name_length));

$end_drop_keyword = "drop index %i" if (!$end_drop_keyword);
$end_drop=$end_drop_keyword;
$end_drop =~ s/%i/crash_q%s/;
$end_drop =~ s/%t/crash_me/;

if ($limits{'create_index'} ne 'no')
{
  find_limit("index name length","max_index_name",
	     new query_many(["create index crash_q%s on crash_me (a)"],
			    undef,undef,
			    [$end_drop],
			    $max_name_length,7));
}

find_limit("max char() size","max_char_size",
	   new query_many(["create table crash_q (q char(%d))",
			   "insert into crash_q values ('%s')"],
			  "select * from crash_q","%s",
			  ["drop table crash_q"],
			  min($max_string_size,$limits{'query_size'})));

if ($limits{'type_sql_varchar(1_arg)'} eq 'yes')
{
  find_limit("max varchar() size","max_varchar_size",
	     new query_many(["create table crash_q (q varchar(%d))",
			     "insert into crash_q values ('%s')"],
			    "select * from crash_q","%s",
			    ["drop table crash_q"],
			    min($max_string_size,$limits{'query_size'})));
}

$found=undef;
foreach $type (('mediumtext','text','text()','blob','long'))
{
  if ($limits{"type_extra_$type"} eq 'yes')
  {
    $found=$type;
    last;
  }
}
if (defined($found))
{
  $found =~ s/\(\)/\(%d\)/;
  find_limit("max text or blob size","max_text_size",
	     new query_many(["create table crash_q (q $found)",
			     "insert into crash_q values ('%s')"],
			    "select * from crash_q","%s",
			    ["drop table crash_q"],
			    min($max_string_size,$limits{'query_size'}-30)));

}

$tmp=new query_repeat([],"create table crash_q (a integer","","",
		      ",a%d integer","",")",["drop table crash_q"],
		      $max_columns);
$tmp->{'offset'}=1;
find_limit("Columns in table","max_columns",$tmp);

# Make a field definition to be used when testing keys

$key_definitions="q0 integer not null";
$key_fields="q0";
for ($i=1; $i < min($limits{'max_columns'},$max_keys) ; $i++)
{
  $key_definitions.=",q$i integer not null";
  $key_fields.=",q$i";
}
$key_values="1," x $i;
chop($key_values);

if ($limits{'unique_in_create'} eq 'yes')
{
  find_limit("unique indexes","max_unique_index",
	     new query_table("create table crash_q (q integer",
			     ",q%d integer not null,unique (q%d)",")",
			     ["insert into crash_q (q,%f) values (1,%v)"],
			     "select q from crash_q",1,
			     "drop table crash_q",
			     $max_keys,0));

  find_limit("index parts","max_index_parts",
	     new query_table("create table crash_q ($key_definitions,unique (q0",
			     ",q%d","))",
			     ["insert into crash_q ($key_fields) values ($key_values)"],
			     "select q0 from crash_q",1,
			     "drop table crash_q",
			     $max_keys,1));

  find_limit("max index part length","max_index_part_length",
	     new query_many(["create table crash_q (q char(%d) not null,unique(q))",
			     "insert into crash_q (q) values ('%s')"],
			    "select q from crash_q","%s",
			    ["drop table crash_q"],
			    $limits{'max_char_size'},0));

  if ($limits{'type_sql_varchar(1_arg)'} eq 'yes')
  {
    find_limit("index varchar part length","max_index_varchar_part_length",
	     new query_many(["create table crash_q (q varchar(%d) not null,unique(q))",
			     "insert into crash_q (q) values ('%s')"],
			    "select q from crash_q","%s",
			    ["drop table crash_q"],
			    $limits{'max_varchar_size'},0));
  }
}


if ($limits{'create_index'} ne 'no')
{
  if ($limits{'create_index'} eq 'ignored' ||
      $limits{'unique_in_create'} eq 'yes')
  {                                     # This should be true
    save_config_data('max_index',$limits{'max_unique_index'},"max index");
    print "indexes: $limits{'max_index'}\n";
  }
  else
  {
    if (!defined($limits{'max_index'}))
    {
      assert("create table crash_q ($key_definitions)");
      for ($i=1; $i <= min($limits{'max_columns'},$max_keys) ; $i++)
      {
	last if (!safe_query("create index crash_q$i on crash_q (q$i)"));
      }
      save_config_data('max_index',$i == $max_keys ? $max_keys : $i,
		       "max index");
      while ( --$i > 0)
      {
	$end_drop=$end_drop_keyword;
	$end_drop =~ s/%i/crash_q$i/;
	$end_drop =~ s/%t/crash_q/;
	assert($end_drop);
      }
      assert("drop table crash_q");
    }
    print "indexs: $limits{'max_index'}\n";
    if (!defined($limits{'max_unique_index'}))
    {
      assert("create table crash_q ($key_definitions)");
      for ($i=0; $i < min($limits{'max_columns'},$max_keys) ; $i++)
      {
	last if (!safe_query("create unique index crash_q$i on crash_q (q$i)"));
      }
      save_config_data('max_unique_index',$i == $max_keys ? $max_keys : $i,
		       "max unique index");
      while ( --$i >= 0)
      {
	$end_drop=$end_drop_keyword;
	$end_drop =~ s/%i/crash_q$i/;
	$end_drop =~ s/%t/crash_q/;
	assert($end_drop);
      }
      assert("drop table crash_q");
    }
    print "unique indexes: $limits{'max_unique_index'}\n";
    if (!defined($limits{'max_index_parts'}))
    {
      assert("create table crash_q ($key_definitions)");
      $end_drop=$end_drop_keyword;
      $end_drop =~ s/%i/crash_q1%d/;
      $end_drop =~ s/%t/crash_q/;
      find_limit("index parts","max_index_parts",
		 new query_table("create index crash_q1%d on crash_q (q0",
				 ",q%d",")",
				 [],
				 undef,undef,
				 $end_drop,
				 $max_keys,1));
      assert("drop table crash_q");
    }
    else
    {
      print "index parts: $limits{'max_index_parts'}\n";
    }
    $end_drop=$end_drop_keyword;
    $end_drop =~ s/%i/crash_q2%d/;
    $end_drop =~ s/%t/crash_me/;

    find_limit("index part length","max_index_part_length",
	       new query_many(["create table crash_q (q char(%d))",
			       "create index crash_q2%d on crash_q (q)",
			       "insert into crash_q values('%s')"],
			      "select q from crash_q",
			      "%s",
			      [ $end_drop,
			       "drop table crash_q"],
			      min($limits{'max_char_size'},"+8192")));
  }
}

find_limit("index length","max_index_length",
	   new query_index_length("create table crash_q ",
				  "drop table crash_q",
				  $max_key_length));

find_limit("max table row length (without blobs)","max_row_length",
	   new query_row_length("crash_q ",
				"not null",
				"drop table crash_q",
				min($max_row_length,
				    $limits{'max_columns'}*
				    min($limits{'max_char_size'},255))));

find_limit("table row length with nulls (without blobs)",
	   "max_row_length_with_null",
	   new query_row_length("crash_q ",
				"",
				"drop table crash_q",
				$limits{'max_row_length'}*2));

find_limit("number of columns in order by","columns_in_order_by",
	   new query_many(["create table crash_q (%F)",
			   "insert into crash_q values(%v)",
			   "insert into crash_q values(%v)"],
			  "select * from crash_q order by %f",
			  undef(),
			  ["drop table crash_q"],
			  $max_order_by));

find_limit("number of columns in group by","columns_in_group_by",
	   new query_many(["create table crash_q (%F)",
			   "insert into crash_q values(%v)",
			   "insert into crash_q values(%v)"],
			  "select * from crash_q group by %f",
			  undef(),
			  ["drop table crash_q"],
			  $max_order_by));

#
# test of different join types
#

assert("create table crash_me2 (a integer not null,b char(10) not null)");
assert("insert into crash_me2 (a,b) values (1,'b')");

report("left outer join","left_outer_join",
       "select crash_me.a from crash_me left join crash_me2 ON crash_me.a=crash_me2.a");
report("natural left outer join","natural_left_outer_join",
       "select crash_me.a from crash_me natural left join crash_me2");
report("left outer join using","left_outer_join_using",
       "select crash_me.a from crash_me left join crash_me2 using (a)");
report("left outer join odbc style","odbc_left_outer_join",
       "select crash_me.a from { oj crash_me left outer join crash_me2 ON crash_me.a=crash_me2.a }");
report("right outer join","right_outer_join",
       "select crash_me.a from crash_me right join crash_me2 ON crash_me.a=crash_me2.a");
report("full outer join","full_outer_join",
       "select crash_me.a from crash_me full join crash_me2 ON crash_me.a=crash_me2.a");
report("cross join (same as from a,b)","cross_join",
       "select crash_me.a from crash_me cross join crash_me2");
report("natural join","natural_join",
       "select crash_me.a from crash_me natural join crash_me2");
report("union","union",
       "select * from crash_me union select * from crash_me2");
report("union all","union_all",
       "select * from crash_me union all select * from crash_me2");
report("intersect","intersect",
       "select * from crash_me intersect select * from crash_me2");
report("intersect all","intersect_all",
       "select * from crash_me intersect all select * from crash_me2");
report("except","except",
       "select * from crash_me except select * from crash_me2");
report("except all","except_all",
       "select * from crash_me except all select * from crash_me2");
report("minus","minus",
       "select * from crash_me minus select * from crash_me2"); # oracle ...

assert("drop table crash_me2");

# somethings to be added here ....
# FOR UNION - INTERSECT - EXCEPT -> CORRESPONDING [ BY ]
# after subqueries:
# >ALL | ANY | SOME - EXISTS - UNIQUE

if (report("subqueries","subqueries",
	   "select a from crash_me where crash_me.a in (select max(a) from crash_me)"))
{
    $tmp=new query_repeat([],"select a from crash_me","","",
			  " where a in (select a from crash_me",")",
			  "","",[],$max_join_tables);
    find_limit("recursive subqueries", "recursive_subqueries",$tmp);
}

report("insert INTO ... SELECT ...","insert_select",
       "create table crash_q (a int)",
       "insert into crash_q (a) SELECT crash_me.a from crash_me",
       "drop table crash_q");

report_trans("transactions","transactions",
       [create_table("crash_q",["a integer not null"],[]),
       "insert into crash_q values (1)"],
       "select * from crash_q",
       "drop table crash_q"
       );

report("atomic updates","atomic_updates",
       create_table("crash_q",["a integer not null"],["primary key (a)"]),
       "insert into crash_q values (2)",
       "insert into crash_q values (3)",
       "insert into crash_q values (1)",
       "update crash_q set a=a+1",
       "drop table crash_q");

if ($limits{'atomic_updates'} eq 'yes')
{
  report_fail("atomic_updates_with_rollback","atomic_updates_with_rollback",
	      create_table("crash_q",["a integer not null"],
			   ["primary key (a)"]),
	      "insert into crash_q values (2)",
	      "insert into crash_q values (3)",
	      "insert into crash_q values (1)",
	      "update crash_q set a=a+1 where a < 3",
	      "drop table crash_q");
}

# To add with the views:
# DROP VIEW - CREAT VIEW *** [ WITH [ CASCADE | LOCAL ] CHECK OPTION ]
report("views","views",
       "create view crash_q as select a from crash_me",
       "drop view crash_q");

report("foreign key syntax","foreign_key_syntax",
       create_table("crash_q",["a integer not null"],["primary key (a)"]),
       create_table("crash_q2",["a integer not null",
				"foreign key (a) references crash_q (a)"],
		    []),
       "insert into crash_q values (1)",
       "insert into crash_q2 values (1)",
       "drop table crash_q2",
       "drop table crash_q");

if ($limits{'foreign_key_syntax'} eq 'yes')
{
  report_fail("foreign keys","foreign_key",
	      create_table("crash_q",["a integer not null"],
			   ["primary key (a)"]),
	      create_table("crash_q2",["a integer not null",
				       "foreign key (a) references crash_q (a)"],
			   []),
	      "insert into crash_q values (1)",
	      "insert into crash_q2 values (2)",
	      "drop table crash_q2",
	      "drop table crash_q");
}

if (!defined($limits{'lock_tables'}))
{
  report("lock table","lock_tables",
	 "lock table crash_me READ",
	 "unlock tables");
  if ($limits{'lock_tables'} eq 'no')
  {
    delete $limits{'lock_tables'};
    report("lock table","lock_tables",
	   "lock table crash_me IN SHARE MODE");
  }
}

if (!report("many tables to drop table","multi_drop",
	   "create table crash_q (a int)",
	   "create table crash_q2 (a int)",
	   "drop table crash_q,crash_q2"))
{
  $dbh->do("drop table crash_q");
  $dbh->do("drop table crash_q2");
}


report("-- as comment","comment_--",
       "select * from crash_me -- Testing of comments");
report("# as comment","comment_#",
       "select * from crash_me # Testing of comments");
report("/* */ as comment","comment_/**/",
       "select * from crash_me /* Testing of comments */");

#
# Check things that fails one some servers
#

# Empress can't insert empty strings in a char() field
report("insert empty string","insert_empty_string",
       create_table("crash_q",["a char(10) not null,b char(10)"],[]),
       "insert into crash_q values ('','')",
       "drop table crash_q");

report("Having with alias","having_with_alias",
       create_table("crash_q",["a integer"],[]),
       "insert into crash_q values (10)",
       "select sum(a) as b from crash_q group by a having b > 0",
       "drop table crash_q");

#
# End of test
#

$dbh->do("drop table crash_me");        # Remove temporary table

print "crash-me safe: $limits{'crash_me_safe'}\n";
print "reconnected $reconnect_count times\n";

$dbh->disconnect;
save_all_config_data();
exit 0;

sub usage
{
    print <<EOF;
$0  Ver $version

This program tries to find all limits and capabilities for a SQL
server.  As it will use the server in some 'unexpected' ways, one
shouldn\'t have anything important running on it at the same time this
program runs!  There is a slight chance that something unexpected may
happen....

As all used queries are legal according to some SQL standard. any
reasonable SQL server should be able to run this test without any
problems.

All questions is cached in $opt_dir/'server_name'.cfg that future runs will use
limits found in previous runs. Remove this file if you want to find the
current limits for your version of the database server.

This program uses some table names while testing things. If you have any
tables with the name of 'crash_me' or 'crash_qxxxx' where 'x' is a number,
they will be deleted by this test!

$0 takes the following options:

--help or --Information
  Shows this help

--batch-mode
  Don\'t ask any questions, quit on errors.

--comment='some comment'
  Add this comment to the crash-me limit file

--database='database' (Default $opt_database)
  Create test tables in this database.

--dir='limits'
  Save crash-me output in this directory

--debug
  Lots of printing to help debugging if something goes wrong.

--fix-limit-file
  Reformat the crash-me limit file.  crash-me is not run!

--force
  Start test at once, without a warning screen and without questions.
  This is a option for the very brave.
  Use this in your cron scripts to test your database every night.

--log-all-queries
  Prints all queries that are executed. Mostly used for debugging crash-me.

--log-queries-to-file='filename'
  Log full queries to file.

--host='hostname' (Default $opt_host)
  Run tests on this host.

--password='password'
  Password for the current user.

--restart
  Save states during each limit tests. This will make it possible to continue
  by restarting with the same options if there is some bug in the DBI or
  DBD driver that caused $0 to die!

--server='server name'  (Default $opt_server)
  Run the test on the given server.
  Known servers names are: Empress, mSQL, MySQL, Pg, Solid and Oracle.
  For others $0 can\'t report the server version.

--user='user_name'
  User name to log into the SQL server.

--start-cmd='command to restart server'
  Automaticly restarts server with this command if the server dies.

--sleep='time in seconds' (Default $opt_sleep)
  Wait this long before restarting server.

EOF
  exit(0);
}


sub server_info
{
  my ($ok,$tmp);
  $ok=0;
  print "\nNOTE: You should be familiar with '$0 --help' before continuing!\n\n";
  if (lc($opt_server) eq "mysql")
  {
    $ok=1;
    print <<EOF;
This test should not crash MySQL if it was distributed together with the
running MySQL version.
If this is the case you can probably continue without having to worry about
destroying something.
EOF
  }
  elsif (lc($opt_server) eq "msql")
  {
    print <<EOF;
This test will take down mSQL repeatedly while finding limits.
To make this test easier, start mSQL in another terminal with something like:

while (true); do /usr/local/mSQL/bin/msql2d ; done

You should be sure that no one is doing anything important with mSQL and that
you have privileges to restart it!
It may take awhile to determinate the number of joinable tables, so prepare to
wait!
EOF
  }
  elsif (lc($opt_server) eq "solid")
  {
    print <<EOF;
This test will take down Solid server repeatedly while finding limits.
You should be sure that no one is doing anything important with Solid
and that you have privileges to restart it!

If you are running Solid without logging and/or backup YOU WILL LOSE!
Solid does not write data from the cache often enough. So if you continue
you may lose tables and data that you entered hours ago!

Solid will also take a lot of memory running this test. You will nead
at least 234M free!

When doing the connect test Solid server or the perl api will hang when
freeing connections. Kill this program and restart it to continue with the
test. You don\'t have to use --restart for this case.
EOF
    if (!$opt_restart)
    {
      print "\nWhen DBI/Solid dies you should run this program repeatedly\n";
      print "with --restart until all tests have completed\n";
    }
  }
  elsif (lc($opt_server) eq "pg")
  {
    print <<EOF;
This test will crash postgreSQL when calculating the number of joinable tables!
You should be sure that no one is doing anything important with postgreSQL
and that you have privileges to restart it!
EOF
  }
  else
  {
    print <<EOF;
This test may crash $opt_server repeatedly while finding limits!
You should be sure that no one is doing anything important with $opt_server
and that you have privileges to restart it!
EOF
  }
  print <<EOF;

You can always contact your database vendor and ask if the database server
is $0 safe.  As crash-me only uses legal SQL statements to test the
server, any reasonable server should be crash-me safe. Unfortunately this
isn\'t the case for all SQL servers.

We, the creators of this utility, are not responsible in any way if your
database server unexpectedly crashes while this program tries to find the
limitations of your server. By accepting the following question with 'yes',
you agree to the above!

EOF

  #
  # No default reply here so no one can blame us for starting the test
  # automaticly.
  #
  for (;;)
  {
    print "Start test (yes/no) ? ";
    $tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp);
    last if ($tmp =~ /^yes$/i);
    exit 1 if ($tmp =~ /^n/i);
    print "\n";
  }
}

sub machine
{
  $name= `uname -s -r -m`;
  if ($?)
  {
    $name= `uname -s -m`;
  }
  if ($?)
  {
    $name= `uname -s`;
  }
  if ($?)
  {
    $name= `uname`;
  }
  if ($?)
  {
    $name="unknown";
  }
  chomp($name); $name =~ s/[\n\r]//g;
  return $name;
}


#
# Help functions that we need
#

sub safe_connect
{
  my ($object)=@_;
  my ($dbh,$tmp);

  for (;;)
  {
    if (($dbh=DBI->connect($server->{'data_source'},$opt_user,$opt_password,
			   { PrintError => 0})))
    {
      $dbh->{LongReadLen}= 16000000; # Set max retrieval buffer
      return $dbh;
    }
    print "Error: $DBI::errstr;  $server->{'data_source'}  - '$opt_user' - '$opt_password'\n";
    print "I got the above error when connecting to $opt_server\n";
    if (defined($object) && defined($object->{'limit'}))
    {
      print "This check was done with limit: $object->{'limit'}.\nNext check will be done with a smaller limit!\n";
      $object=undef();
    }
    save_config_data('crash_me_safe','no',"crash me safe");
    if ($opt_db_start_cmd)
    {
      print "Restarting the db server with:\n'$opt_db_start_cmd'\n";
      system("$opt_db_start_cmd");
      print "Waiting $opt_sleep seconds so the server can initialize\n";
      sleep $opt_sleep;
    }
    else
    {
      exit(1) if ($opt_batch_mode);
      print "Can you check/restart it so I can continue testing?\n";
      for (;;)
      {
	print "Continue test (yes/no) ? [yes] ";
	$tmp=<STDIN>; chomp($tmp); $tmp=lc($tmp);
	$tmp = "yes" if ($tmp eq "");
	last if (index("yes",$tmp) >= 0);
	exit 1 if (index("no",$tmp) >= 0);
	print "\n";
      }
    }
  }
}

#
# Check if the server is upp and running. If not, ask the user to restart it
#

sub check_connect
{
  my ($object)=@_;
  print "Checking connection\n" if ($opt_log_all_queries);
  return if (defined($check_connect) && defined($dbh->do($check_connect)));
  $dbh->disconnect;
  print "\nreconnecting\n" if ($opt_debug);
  $reconnect_count++;
  $dbh=safe_connect($object);
}

#
# print query if debugging
#
sub print_query
{
  my ($query)=@_;
  $last_error=$DBI::errstr;
  if ($opt_debug)
  {
    if (length($query) > 130)
    {
      $query=substr($query,0,120) . "...(" . (length($query)-120) . ")";
    }
    printf "\nGot error from query: '%s'\n%s\n",$query,$DBI::errstr;
  }
}

#
# Do one or many queries. Return 1 if all was ok
#

sub safe_query
{
  my($queries)=@_;
  my($query,$ok,$retry,@tmp);
  $ok=1;
  if (ref($queries) ne "ARRAY")
  {
    push(@tmp,$queries);
    $queries= \@tmp;
  }
  foreach $query (@$queries)
  {
    printf "query1: %-80.80s ...(%d)\n",$query,length($query)  if ($opt_log_all_queries);
    print LOG "$query;\n" if ($opt_log);
    if (length($query) > $query_size)
    {
      $ok=0;
      next;
    }
    for ($retry=0; $retry < $retry_limit ; $retry++)
    {
      if (!$dbh->do($query))
      {
	print_query($query);
	$retry=100 if (!$server->abort_if_fatal_error());
	# Force a reconnect because of Access drop table bug!
	if ($retry == $retry_limit-2)
	{
	  print "Forcing discoennect to retry query\n" if ($opt_debug);
	  $dbh->disconnect;
	}
	check_connect();        # Check that server is still up
	$ok=0;                  # Didn't work. Continue with cleanup
      }
      else
      {
	last;
      }
    }
  }
  return $ok;
}


#
# Do a query on a query package object.
#

sub limit_query
{
  my($object,$limit)=@_;
  my ($query,$result,$retry,$sth);

  $query=$object->query($limit);
  $result=safe_query($query);
  if (!$result)
  {
    $object->cleanup();
    return 0;
  }
  if (defined($query=$object->check_query()))
  {
    for ($retry=0 ; $retry < $retry_limit ; $retry++)
    {
      printf "query2: %-80.80s\n",$query if ($opt_log_all_queries);
      print LOG "$query;\n" if ($opt_log);
      if (($sth= $dbh->prepare($query)))
      {
	if ($sth->execute)
	{
	  $result= $object->check($sth);
	  $sth->finish;
	  $object->cleanup();
	  return $result;
	}
	print_query($query);
	$sth->finish;
      }
      else
      {
	print_query($query);
      }
      $retry=100 if (!$server->abort_if_fatal_error()); # No need to continue
      if ($retry == $retry_limit-2)
      {
	print "Forcing discoennect to retry query\n" if ($opt_debug);
	$dbh->disconnect;
      }
      check_connect($object);   # Check that server is still up
    }
    $result=0;                  # Query failed
  }
  $object->cleanup();
  return $result;               # Server couldn't handle the query
}


sub report
{
  my ($prompt,$limit,@queries)=@_;
  print "$prompt: ";
  if (!defined($limits{$limit}))
  {
    save_config_data($limit,safe_query(\@queries) ? "yes" : "no",$prompt);
  }
  print "$limits{$limit}\n";
  return $limits{$limit} ne "no";
}

sub report_fail
{
  my ($prompt,$limit,@queries)=@_;
  print "$prompt: ";
  if (!defined($limits{$limit}))
  {
    save_config_data($limit,safe_query(\@queries) ? "no" : "yes",$prompt);
  }
  print "$limits{$limit}\n";
  return $limits{$limit} ne "no";
}


# Return true if one of the queries is ok

sub report_one
{
  my ($prompt,$limit,$queries)=@_;
  my ($query,$res,$result);
  print "$prompt: ";
  if (!defined($limits{$limit}))
  {
    $result="no";
    foreach $query (@$queries)
    {
      if (safe_query($query->[0]))
      {
	$result= $query->[1];
	last;
      }
    }
    save_config_data($limit,$result,$prompt);
  }
  print "$limits{$limit}\n";
  return $limits{$limit} ne "no";
}


# Execute query and save result as limit value.

sub report_result
{
  my ($prompt,$limit,$query)=@_;
  my($error);
  print "$prompt: ";
  if (!defined($limits{$limit}))
  {
    $error=safe_query_result($query,"1",2);
    save_config_data($limit,$error ? "not supported" : $last_result,$prompt);
  }
  print "$limits{$limit}\n";
  return $limits{$limit} ne "no";
}

sub report_trans
{
  my ($prompt,$limit,$queries,$check,$clear)=@_;
  print "$prompt: ";
  if (!defined($limits{$limit}))
  {
    eval {undef($dbh->{AutoCommit})};
    if (!$@)
    {
      if (safe_query(\@$queries))
      {
	  $rc = $dbh->rollback;
	  if ($rc) {
	    $dbh->{AutoCommit} = 1;
	    if (safe_query_result($check,"","")) {
	      save_config_data($limit,"yes",$prompt);
	    }
	  } else {
	    $dbh->{AutoCommit} = 1;
	    safe_query($clear);
	    save_config_data($limit,"error",$prompt);
	  }
      } else {
        save_config_data($limit,"error",$prompt);
      }
      $dbh->{AutoCommit} = 1;
    }
    else
    {
      save_config_data($limit,"no",$prompt);
    }
  }
  print "$limits{$limit}\n";
  return $limits{$limit} ne "no";
}


sub check_and_report
{
  my ($prompt,$limit,$pre,$query,$post,$answer,$string_type,$skip_prompt,
      $function)=@_;
  my ($tmp);
  $function=0 if (!defined($function));

  print "$prompt: " if (!defined($skip_prompt));
  if (!defined($limits{$limit}))
  {
    $tmp=1-safe_query(\@$pre);
    $tmp=safe_query_result($query,$answer,$string_type) if (!$tmp);
    safe_query(\@$post);
    if ($function == 3)		# Report error as 'no'.
    {
      $function=0;
      $tmp= -$tmp;
    }
    if ($function == 0 ||
	$tmp != 0 && $function == 1 ||
	$tmp == 0 && $function== 2)
    {
      save_config_data($limit, $tmp == 0 ? "yes" : $tmp == 1 ? "no" : "error",
		       $prompt);
      print "$limits{$limit}\n";
      return $function == 0 ? $limits{$limit} eq "yes" : 0;
    }
    return 1;			# more things to check
  }
  print "$limits{$limit}\n";
  return 0 if (defined($function));
  return $limits{$limit} eq "yes";
}


# returns 0 if ok, 1 if error, -1 if wrong answer
# Sets $last_result to value of query

sub safe_query_result
{
  my ($query,$answer,$result_type)=@_;
  my ($sth,$row,$result,$retry);
  undef($last_result);
  
  printf "\nquery3: %-80.80s\n",$query  if ($opt_log_all_queries);
  print LOG "$query;\n" if ($opt_log);
  for ($retry=0; $retry < $retry_limit ; $retry++)
  {
    if (!($sth=$dbh->prepare($query)))
    {
      print_query($query);
      if ($server->abort_if_fatal_error())
      {
	check_connect();	# Check that server is still up
	next;			# Retry again
      }
      check_connect();		# Check that server is still up
      return 1;
    }
    if (!$sth->execute)
    {
      print_query($query);
      if ($server->abort_if_fatal_error())
      {
	check_connect();	# Check that server is still up
	next;			# Retry again
      }
      check_connect();		# Check that server is still up
      return 1;
    }
    else
    {
      last;
    }
  }
  if (!($row=$sth->fetchrow_arrayref))
  {
    print "\nquery: $query didn't return any result\n" if ($opt_debug);
    return 1;
  }
  $result=0;                  	# Ok
  $last_result= $row->[0];	# Save for report_result;
  if ($result_type == 0)	# Compare numbers
  {
    if ($row->[0] != $answer && (abs($row->[0]- $answer)/
				 (abs($row->[0]) + abs($answer))) > 0.01)
    {
      $result=-1;
    }
  }
  elsif ($result_type == 1)	# Compare where end space may differ
  {
    $row->[0] =~ s/\s+$//;
    $result=-1 if ($row->[0] ne $answer);
  }
  elsif ($result_type == 3)	# This should be a exact match
  {
    $result= -1 if ($row->[0] ne $answer);
  }
  elsif ($result_type == 4)	# If results should be NULL
  {
    $result= -1 if (defined($row->[0]));
  }
  elsif ($result_type == 5)	# Result should have given prefix
  {
    $result= -1 if (length($row->[0]) < length($answer) &&
		    substring($row->[0],1,length($answer)) ne $answer);
  }
  elsif ($result_type == 6)	# Exact match but ignore errors
  {
    $result= 1 if ($row->[0] ne $answer);
  }
  elsif ($result_type == 7)	# Compare against array of numbers
  {
    if ($row->[0] != $answer->[0])
    {
      $result= -1;
    }
    else
    {
      my ($value);
      shift @$answer;
      while (($row=$sth->fetchrow_arrayref))
      {
	$value=shift(@$answer);
	if (!defined($value))
	{
	  print "\nquery: $query returned to many results\n"
	    if ($opt_debug);
	  $result= 1;
	  last;
	}
	if ($row->[0] != $value)
	{
	  $result= -1;
	  last;
	}
      }
      if ($#$answer != -1)
      {
	print "\nquery: $query returned too few results\n"
	  if ($opt_debug);
	$result= 1;
      }	  
    }
  }
  $sth->finish;
  print "\nquery: '$query' returned '$row->[0]' instead of '$answer'\n"
    if ($opt_debug && $result && $result_type != 7);
  return $result;
}

#
# Find limit using binary search.  This is a weighed binary search that
# will prefere lower limits to get the server to crash as few times as possible
#

sub find_limit()
{
  my ($prompt,$limit,$query)=@_;
  my ($first,$end,$i,$tmp);
  print "$prompt: ";
  if (defined($end=$limits{$limit}))
  {
    print "$end (cache)\n";
    return $end;
  }
  if (defined($query->{'init'}) && !defined($end=$limits{'restart'}{'tohigh'}))
  {
    if (!safe_query($query->{'init'}))
    {
      $query->cleanup();
      return "error";
    }
  }

  if (!limit_query($query,1))           # This must work
  {
    print "\nMaybe fatal error: Can't check '$prompt' for limit=1\nerror: $last_error\n";
    return "error";
  }

  $first=0;
  $first=$limits{'restart'}{'low'} if ($limits{'restart'}{'low'});

  if (defined($end=$limits{'restart'}{'tohigh'}))
  {
    $end--;
    print "\nRestarting this with low limit: $first and high limit: $end\n";
    delete $limits{'restart'};
    $i=$first+int(($end-$first+4)/5);           # Prefere lower on errors
  }
  else
  {
    $end= $query->max_limit();
    $i=int(($end+$first)/2);
  }
  while ($first < $end)
  {
    print "." if ($opt_debug);
    save_config_data("restart",$i,"") if ($opt_restart);
    if (limit_query($query,$i))
    {
      $first=$i;
#      $i=$first+int(($end-$first+2)/3);
      $i=$first+int(($end-$first+1)/2); # to be a bit faster to go up
    }
    else
    {
      $end=$i-1;
      $i=$first+int(($end-$first+4)/5); # Prefere lower on errors
    }
  }
  $end+=$query->{'offset'} if ($end && defined($query->{'offset'}));
  if ($end >= $query->{'max_limit'} &&
      substr($query->{'max_limit'},0,1) eq '+')
  {
    $end= $query->{'max_limit'};
  }
  print "$end\n";
  save_config_data($limit,$end,$prompt);
  delete $limits{'restart'};
  return $end;
}

#
# Check that the query works!
#

sub assert
{
  my($query)=@_;

  if (!safe_query($query))
  {
    $query=join("; ",@$query) if (ref($query) eq "ARRAY");
    print "\nFatal error:\nquery: '$query'\nerror: $DBI::errstr\n";
    exit 1;
  }
}


sub read_config_data
{
  my ($key,$limit,$prompt);
  if (-e $opt_config_file)
  {
    open(CONFIG_FILE,"+<$opt_config_file") ||
      die "Can't open configure file $opt_config_file\n";
    print "Reading old values from cache: $opt_config_file\n";
  }
  else
  {
    open(CONFIG_FILE,"+>>$opt_config_file") ||
      die "Can't create configure file $opt_config_file: $!\n";
  }
  select CONFIG_FILE;
  $|=1;
  select STDOUT;
  while (<CONFIG_FILE>)
  {
    chomp;
    if (/^(\S+)=([^\#]*[^\#\s])\s*(\# .*)*$/)
    {
      $key=$1; $limit=$2 ; $prompt=$3;
      if (!$opt_quick || $limit =~ /\d/ || $key =~ /crash_me/)
      {
	if ($key !~ /restart/i)
	{
	  $limits{$key}=$limit;
	  $prompts{$key}=length($prompt) ? substr($prompt,2) : "";
	  delete $limits{'restart'};
	}
	else
	{
	  $limit_changed=1;
	  if ($limit > $limits{'restart'}{'tohigh'})
	  {
	    $limits{'restart'}{'low'} = $limits{'restart'}{'tohigh'};
	  }
	  $limits{'restart'}{'tohigh'} = $limit;
	}
      }
    }
    elsif (!/^\s*$/ && !/^\#/)
    {
      die "Wrong config row: $_\n";
    }
  }
}


sub save_config_data
{
  my ($key,$limit,$prompt)=@_;
  $prompts{$key}=$prompt;
  return if (defined($limits{$key}) && $limits{$key} eq $limit);
  if (!defined($limit) || $limit eq "")
  {
    die "Undefined limit for $key\n";
  }
  print CONFIG_FILE "$key=$limit\t# $prompt\n";
  $limits{$key}=$limit;
  $limit_changed=1;
  if (($opt_restart && $limits{'operating_system'} =~ /windows/i) ||
		       ($limits{'operating_system'} =~ /NT/))
  {
    # If perl crashes in windows, everything is lost (Wonder why? :)
    close CONFIG_FILE;
    open(CONFIG_FILE,"+>>$opt_config_file") ||
      die "Can't reopen configure file $opt_config_file: $!\n";
  }
}


sub save_all_config_data
{
  my ($key,$tmp);
  close CONFIG_FILE;
  return if (!$limit_changed);
  open(CONFIG_FILE,">$opt_config_file") ||
    die "Can't create configure file $opt_config_file: $!\n";
  select CONFIG_FILE;
  $|=1;
  select STDOUT;
  delete $limits{'restart'};

  print CONFIG_FILE "#This file is automaticly generated by crash-me $version\n\n";
  foreach $key (sort keys %limits)
  {
    $tmp="$key=$limits{$key}";
    print CONFIG_FILE $tmp . ("\t" x (int((32-min(length($tmp),32)+7)/8)+1)) .
      "# $prompts{$key}\n";
  }
  close CONFIG_FILE;
}


sub check_repeat
{
  my ($sth,$limit)=@_;
  my ($row);

  return 0 if (!($row=$sth->fetchrow_arrayref));
  return (defined($row->[0]) && ('a' x $limit) eq $row->[0]) ? 1 : 0;
}


sub min
{
  my($min)=$_[0];
  my($i);
  for ($i=1 ; $i <= $#_; $i++)
  {
    $min=$_[$i] if ($min > $_[$i]);
  }
  return $min;
}

sub sql_concat
{
  my ($a,$b)= @_;
  return "$a || $b" if ($limits{'func_sql_concat_as_||'} eq 'yes');
  return "concat($a,$b)" if ($limits{'func_odbc_concat'} eq 'yes');
  return "$a + $b" if ($limits{'func_extra_concat_as_+'} eq 'yes');
  return undef;
}

#
# Returns a list of statements to create a table in a portable manner
# but still utilizing features in the databases.
#

sub create_table
{
  my($table_name,$fields,$index) = @_;
  my($query,$nr,$parts,@queries,@index);

  $query="create table $table_name (";
  $nr=0;
  foreach $field (@$fields)
  {
    $query.= $field . ',';
  }
  foreach $index (@$index)
  {
    $index =~ /\(([^\(]*)\)$/i;
    $parts=$1;
    if ($index =~ /^primary key/)
    {
      if ($limits{'primary_key_in_create'} eq 'yes')
      {
	$query.= $index . ',';
      }
      else
      {
	push(@queries,
	     "create unique index ${table_name}_prim on $table_name ($parts)");
      }
    }
    elsif ($index =~ /^unique/)
    {
      if ($limits{'unique_in_create'} eq 'yes')
      {
	$query.= "unique ($parts),";
      }
      else
      {
	$nr++;
	push(@queries,
	     "create unique index ${table_name}_$nr on $table_name ($parts)");

      }
    }
    else
    {
      if ($limits{'index_in_create'} eq 'yes')
      {
	$query.= "index ($parts),";
      }
      else
      {
	$nr++;
	push(@queries,
	     "create index ${table_name}_$nr on $table_name ($1)");
      }
    }
  }
  chop($query);
  $query.= ')';
  unshift(@queries,$query);
  return @queries;
}


#
# This is used by some query packages to change:
# %d -> limit
# %s -> 'a' x limit
# %v -> "1,1,1,1,1" where there are 'limit' number of ones
# %f -> q1,q2,q3....
# %F -> q1 integer,q2 integer,q3 integer....

sub fix_query
{
  my ($query,$limit)=@_;
  my ($repeat,$i);

  return $query if !(defined($query));
  $query =~ s/%d/$limit/g;
  if ($query =~ /%s/)
  {
    $repeat= 'a' x $limit;
    $query =~ s/%s/$repeat/g;
  }
  if ($query =~ /%v/)
  {
    $repeat= '1,' x $limit;
    chop($repeat);
    $query =~ s/%v/$repeat/g;
  }
  if ($query =~ /%f/)
  {
    $repeat="";
    for ($i=1 ; $i <= $limit ; $i++)
    {
      $repeat.="q$i,";
    }
    chop($repeat);
    $query =~ s/%f/$repeat/g;
  }
  if ($query =~ /%F/)
  {
    $repeat="";
    for ($i=1 ; $i <= $limit ; $i++)
    {
      $repeat.="q$i integer,";
    }
    chop($repeat);
    $query =~ s/%F/$repeat/g;
  }
  return $query;
}


#
# Different query packages
#

package query_repeat;

sub new
{
  my ($type,$init,$query,$add1,$add_mid,$add,$add_end,$end_query,$cleanup,
      $max_limit, $check, $offset)=@_;
  my $self={};
  if (defined($init) && $#$init != -1)
  {
    $self->{'init'}=$init;
  }
  $self->{'query'}=$query;
  $self->{'add1'}=$add1;
  $self->{'add_mid'}=$add_mid;
  $self->{'add'}=$add;
  $self->{'add_end'}=$add_end;
  $self->{'end_query'}=$end_query;
  $self->{'cleanup'}=$cleanup;
  $self->{'max_limit'}=(defined($max_limit) ? $max_limit : $main::query_size);
  $self->{'check'}=$check;
  $self->{'offset'}=$offset;
  $self->{'printf'}= ($add =~ /%d/);
  bless $self;
}

sub query
{
  my ($self,$limit)=@_;
  if (!$self->{'printf'})
  {
    return $self->{'query'} . ($self->{'add'} x $limit) .
      ($self->{'add_end'} x $limit) . $self->{'end_query'};
  }
  my ($tmp,$tmp2,$tmp3,$i);
  $tmp=$self->{'query'};
  if ($self->{'add1'})
  {
    for ($i=0; $i < $limit ; $i++)
    {
      $tmp3 = $self->{'add1'};
      $tmp3 =~ s/%d/$i/g;
      $tmp  .= $tmp3;
    }
  }
  $tmp .= " ".$self->{'add_mid'};
  if ($self->{'add'})
  {
    for ($i=0; $i < $limit ; $i++)
    {
      $tmp2 = $self->{'add'};
      $tmp2 =~ s/%d/$i/g;
      $tmp  .= $tmp2;
    }
  }
  return ($tmp .
	  ($self->{'add_end'} x $limit) . $self->{'end_query'});
}

sub max_limit
{
  my ($self)=@_;
  my $tmp;
  $tmp=int(($main::limits{"query_size"}-length($self->{'query'})
	    -length($self->{'add_mid'})-length($self->{'end_query'}))/
	   (length($self->{'add1'})+
	   length($self->{'add'})+length($self->{'add_end'})));
  return main::min($self->{'max_limit'},$tmp);
}


sub cleanup
{
  my ($self)=@_;
  my($tmp,$statement);
  $tmp=$self->{'cleanup'};
  foreach $statement (@$tmp)
  {
    main::safe_query($statement) if (defined($statement) && length($statement));
  }
}

sub check
{
  my ($self,$sth)=@_;
  my $check=$self->{'check'};
  return &$check($sth,$self->{'limit'}) if (defined($check));
  return 1;
}

sub check_query
{
  return undef;
}


package query_num;

sub new
{
  my ($type,$query,$end_query,$cleanup,$max_limit,$check)=@_;
  my $self={};
  $self->{'query'}=$query;
  $self->{'end_query'}=$end_query;
  $self->{'cleanup'}=$cleanup;
  $self->{'max_limit'}=$max_limit;
  $self->{'check'}=$check;
  bless $self;
}


sub query
{
  my ($self,$i)=@_;
  $self->{'limit'}=$i;
  return "$self->{'query'}$i$self->{'end_query'}";
}

sub max_limit
{
  my ($self)=@_;
  return $self->{'max_limit'};
}

sub cleanup
{
  my ($self)=@_;
  my($statement);
  foreach $statement ($self->{'$cleanup'})
  {
    main::safe_query($statement) if (defined($statement) && length($statement));
  }
}


sub check
{
  my ($self,$sth)=@_;
  my $check=$self->{'check'};
  return &$check($sth,$self->{'limit'}) if (defined($check));
  return 1;
}

sub check_query
{
  return undef;
}

#
# This package is used when testing CREATE TABLE!
#

package query_table;

sub new
{
  my ($type,$query, $add, $end_query, $extra_init, $safe_query, $check,
      $cleanup, $max_limit, $offset)=@_;
  my $self={};
  $self->{'query'}=$query;
  $self->{'add'}=$add;
  $self->{'end_query'}=$end_query;
  $self->{'extra_init'}=$extra_init;
  $self->{'safe_query'}=$safe_query;
  $self->{'check'}=$check;
  $self->{'cleanup'}=$cleanup;
  $self->{'max_limit'}=$max_limit;
  $self->{'offset'}=$offset;
  bless $self;
}


sub query
{
  my ($self,$limit)=@_;
  $self->{'limit'}=$limit;
  $self->cleanup();     # Drop table before create

  my ($tmp,$tmp2,$i,$query,@res);
  $tmp =$self->{'query'};
  $tmp =~ s/%d/$limit/g;
  for ($i=1; $i <= $limit ; $i++)
  {
    $tmp2 = $self->{'add'};
    $tmp2 =~ s/%d/$i/g;
    $tmp  .= $tmp2;
  }
  push(@res,$tmp . $self->{'end_query'});
  $tmp=$self->{'extra_init'};
  foreach $query (@$tmp)
  {
    push(@res,main::fix_query($query,$limit));
  }
  return \@res;
}


sub max_limit
{
  my ($self)=@_;
  return $self->{'max_limit'};
}


sub check_query
{
  my ($self)=@_;
  return main::fix_query($self->{'safe_query'},$self->{'limit'});
}

sub check
{
  my ($self,$sth)=@_;
  my $check=$self->{'check'};
  return 0 if (!($row=$sth->fetchrow_arrayref));
  if (defined($check))
  {
    return (defined($row->[0]) &&
	    $row->[0] eq main::fix_query($check,$self->{'limit'})) ? 1 : 0;
  }
  return 1;
}


# Remove table before and after create table query

sub cleanup()
{
  my ($self)=@_;
  main::safe_query(main::fix_query($self->{'cleanup'},$self->{'limit'}));
}

#
# Package to do many queries with %d, and %s substitution
#

package query_many;

sub new
{
  my ($type,$query,$safe_query,$check_result,$cleanup,$max_limit,$offset,
      $safe_cleanup)=@_;
  my $self={};
  $self->{'query'}=$query;
  $self->{'safe_query'}=$safe_query;
  $self->{'check'}=$check_result;
  $self->{'cleanup'}=$cleanup;
  $self->{'max_limit'}=$max_limit;
  $self->{'offset'}=$offset;
  $self->{'safe_cleanup'}=$safe_cleanup;
  bless $self;
}


sub query
{
  my ($self,$limit)=@_;
  my ($queries,$query,@res);
  $self->{'limit'}=$limit;
  $self->cleanup() if (defined($self->{'safe_cleanup'}));
  $queries=$self->{'query'};
  foreach $query (@$queries)
  {
    push(@res,main::fix_query($query,$limit));
  }
  return \@res;
}

sub check_query
{
  my ($self)=@_;
  return main::fix_query($self->{'safe_query'},$self->{'limit'});
}

sub cleanup
{
  my ($self)=@_;
  my($tmp,$statement);
  return if (!defined($self->{'cleanup'}));
  $tmp=$self->{'cleanup'};
  foreach $statement (@$tmp)
  {
    if (defined($statement) && length($statement))
    {
      main::safe_query(main::fix_query($statement,$self->{'limit'}));
    }
  }
}


sub check
{
  my ($self,$sth)=@_;
  my ($check,$row);
  return 0 if (!($row=$sth->fetchrow_arrayref));
  $check=$self->{'check'};
  if (defined($check))
  {
    return (defined($row->[0]) &&
	    $row->[0] eq main::fix_query($check,$self->{'limit'})) ? 1 : 0;
  }
  return 1;
}

sub max_limit
{
  my ($self)=@_;
  return $self->{'max_limit'};
}

#
# Used to find max supported row length
#

package query_row_length;

sub new
{
  my ($type,$create,$null,$drop,$max_limit)=@_;
  my $self={};
  $self->{'table_name'}=$create;
  $self->{'null'}=$null;
  $self->{'cleanup'}=$drop;
  $self->{'max_limit'}=$max_limit;
  bless $self;
}


sub query
{
  my ($self,$limit)=@_;
  my ($res,$values,$size,$length,$i);
  $self->{'limit'}=$limit;

  $res="";
  $size=main::min($main::limits{'max_char_size'},255);
  for ($length=$i=0; $length + $size <= $limit ; $length+=$size, $i++)
  {
    $res.= "q$i char($size) $self->{'null'},";
    $values.="'" . ('a' x $size) . "',";
  }
  if ($length < $limit)
  {
    $size=$limit-$length;
    $res.= "q$i char($size) $self->{'null'},";
    $values.="'" . ('a' x $size) . "',";
  }
  chop($res);
  chop($values);
  return ["create table " . $self->{'table_name'} . " ($res)",
	  "insert into " . $self->{'table_name'} . " values ($values)"];
}

sub max_limit
{
  my ($self)=@_;
  return $self->{'max_limit'};
}

sub cleanup
{
  my ($self)=@_;
  main::safe_query($self->{'cleanup'});
}


sub check
{
  return 1;
}

sub check_query
{
  return undef;
}

#
# Used to find max supported index length
#

package query_index_length;

sub new
{
  my ($type,$create,$drop,$max_limit)=@_;
  my $self={};
  $self->{'create'}=$create;
  $self->{'cleanup'}=$drop;
  $self->{'max_limit'}=$max_limit;
  bless $self;
}


sub query
{
  my ($self,$limit)=@_;
  my ($res,$size,$length,$i,$parts,$values);
  $self->{'limit'}=$limit;

  $res=$parts=$values="";
  $size=main::min($main::limits{'max_index_part_length'},$main::limits{'max_char_size'});
  $size=1 if ($size == 0);	# Avoid infinite loop errors
  for ($length=$i=0; $length + $size <= $limit ; $length+=$size, $i++)
  {
    $res.= "q$i char($size) not null,";
    $parts.= "q$i,";
    $values.= "'" . ('a' x $size) . "',";
  }
  if ($length < $limit)
  {
    $size=$limit-$length;
    $res.= "q$i char($size) not null,";
    $parts.="q$i,";
    $values.= "'" . ('a' x $size) . "',";
  }
  chop($parts);
  chop($res);
  chop($values);
  if ($main::limits{'unique_in_create'} eq 'yes')
  {
    return [$self->{'create'} . "($res,unique ($parts))",
	    "insert into crash_q values($values)"];
  }
  return [$self->{'create'} . "($res)",
	  "create index crash_q_index on crash_q ($parts)",
	  "insert into crash_q values($values)"];
}

sub max_limit
{
  my ($self)=@_;
  return $self->{'max_limit'};
}

sub cleanup
{
  my ($self)=@_;
  main::safe_query($self->{'cleanup'});
}


sub check
{
  return 1;
}

sub check_query
{
  return undef;
}