~drizzle-trunk/drizzle/development

#!/usr/bin/perl

#

# Tests MySQL. Output is given to the stderr. Use

# diff to check the possible differencies.

#

use DBI;

use Getopt::Long;

$VER = "1.9";

$| = 1;

$opt_db =            "test";

$opt_user =         $opt_password = $opt_without = "";

$opt_host =         "localhost";

$opt_port =          "4427";

$opt_socket =      "/tmp/mysql.sock";

$opt_help =          0;

$NO_ERR  = 0;   # No error

$EXP_ERR = 1;   # Expect error

$MAY_ERR = 2;   # Maybe error

$HS      = 0;   # Horizontal style of output

$VS      = 1;   # Vertical style of output

$VERBOSE = 0;   # Print the results

$SILENT  = 1;   # No output

@test_packages = ("FUNC", "PROC", "SHOW");

####

#### main program

####

main();

sub main()

{

  GetOptions("help", "db=s", "port=i", "host=s", "password=s", "user=s", "socket=s", 

	      "without=s") || usage();

  usage() if ($opt_help);

  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host:port=$opt_port:mysql_socket=$opt_socket", $opt_user, $opt_password, { PrintError => 0 }) 

  || die $DBI::errstr;

## QQ ######################################

$sth = $dbh->prepare("show felds from t2") 

|| die "Couldn't prepare query: $DBI::errstr\n";

if (!$sth->execute)

{

  print "Couldn't execute query: $DBI::errstr\n";

  $sth->finish;

  die;

}

while (($row = $sth->fetchrow_arrayref))

{

  print "$row->[1]\n";

}

exit(0);

## QQ ######################################

  printf("####\n#### THIS IS mysql-test script RUNNING\n");

  printf("####      mysql-test version $VER\n####\n");

  test_mysql_functions() if (&chk_package($opt_without, $test_packages[0]));

  test_mysql_procedures() if (&chk_package($opt_without, $test_packages[1]));

  test_mysql_show() if (&chk_package($opt_without, $test_packages[2]));

  print "\n";

  return;

}

####

#### test show -command of MySQL

####

sub test_mysql_show

{

  my ($query, $i);

  $query = create_show_tables();

  &exec_query(["drop table my_t"], $MAY_ERR, $SILENT);

  for ($i = 0; $query[$i]; $i++)

  {

    &exec_query([$query[$i]], $NO_ERR, $VERBOSE, $HS);

    &exec_query(["show fields from my_t"], $NO_ERR, $VERBOSE, $HS);

    &exec_query(["show keys from my_t"], $NO_ERR, $VERBOSE, $HS);

    &exec_query(["drop table my_t"], $NO_ERR, $SILENT);

  }

}

sub create_show_tables

{

  my ($query, $i);

  $query[0] = <<EOF;

create table my_t (i int, f float, s char(64), b blob, t text)

EOF

  $query[1] = <<EOF;

create table my_t (i int, f float, s char(64), b blob, t text, primary key (i))

EOF

  $query[2] = <<EOF;

create table my_t (i int, f float, s char(64), b blob, t text, unique (i), unique(s))

EOF

  for ($i = 0; $query[$i]; $i++) { chop($query[$i]); }

  return $query;

}

####

#### test procedures, currently only procedure analyze()

####

sub test_mysql_procedures

{

  test_analyze();

}

sub test_analyze

{

  my ($query, $i, $j);

  if ($opt_help)

  {

    usage();

  }

  # invalid queries

  &exec_query(["select * from mails procedure analyse(-1)"], 

	      $EXP_ERR, $VERBOSE, $HS);

  &exec_query(["select * from mails procedure analyse(10, -1)"], 

	      $EXP_ERR, $VERBOSE, $HS);

  &exec_query(["select * from mails procedure analyse(1, 2, 3)"],

	      $EXP_ERR, $VERBOSE, $HS);

  &exec_query(["select * from mails procedure analyse(-10, 10)"],

	      $EXP_ERR, $VERBOSE, $HS);

  &exec_query(["select * from mails procedure analyse('a', 'a')"],

	      $EXP_ERR, $VERBOSE, $HS);

  # valid queries

#  &exec_query(["select * from mails procedure analyse(10)"], 0, 0);

#  &exec_query(["select * from mails procedure analyse(10, 10)"], 0, 0);

#  &exec_query(["select hash from mails procedure analyse()"], 0, 0);

  &exec_query(["use mysql_test"], $NO_ERR, $VERBOSE, $HS);

#  &exec_query(["select timestamp from w32_user procedure analyse(0)"], 0, 0); 

  $query = create_test_tables();

  &exec_query(["drop table my_t"], $MAY_ERR, $SILENT);

  for ($i = 0; $query[$i][0]; $i++)

  {

    &exec_query([$query[$i][0]], $NO_ERR, $SILENT); # create table

    for ($j = 1; $query[$i][$j]; $j++)

    {

      &exec_query([$query[$i][$j]], $NO_ERR, $SILENT); # do inserts

    }

    &exec_query(["select * from my_t procedure analyse(0,0)"],

		$NO_ERR, $VERBOSE, $HS);

    &exec_query(["select * from my_t procedure analyse()"],

		$NO_ERR, $VERBOSE, $HS);

    &exec_query(["drop table my_t"], $NO_ERR, $SILENT);

  }

}

####

#### if $opt is found as a part from the '--without=...' option string

#### return 0, else 1. if zero is returned, then that part of MySQL

#### won't be tested

####

sub chk_package

{

  my ($opt_str, $opt) = @_;

  $sub_opt_str = '';

  for ($i = 0, $ptr = substr($opt_str, $i, 1); $ptr || $ptr eq '0';

       $i++, $ptr = substr($opt_str, $i, 1))

  {

    $sub_opt_str .= $ptr;

    if ($sub_opt_str eq $opt)

    {

      $next_chr = substr($opt_str, ($i + 1), 1);

      if ($next_chr eq ',' || (!$next_chr && $next_chr ne '0'))

      {

	return 0;

      }

    }

    if ($ptr eq ',')

    {

      # next word on the opt_str

      $sub_opt_str = '';

    }

  }

  return 1;

}

####

#### Tests given function(s) with given value(s) $count rounds

#### If function doesn't have an arg, test it once and continue.

#### ulargs (number of unlimited args) is the number of arguments 

#### to be placed in place of '.' . '.' means that any number

#### of the last argument type is possible to the function.

#### If force is given, never mind about errors

#### args: $func:   list of functions to be tested

####       $value:  list of values to be used with functions

####       $count:  number of times one function should be tested

####       $ulargs: number of unlimited args to be used when possible

####       $table_info: information about the table to be used, contains:

####       table name, info about the fields in the table, for example:

####       [mysql_test1, "Zi", "Rd"], where mysql_test1 is the name of the

####       table, "Zi" tells, that the first field name is 'i' and it is

####       type 'Z' (integer), see test_mysql_functions, 'Rd' tells that

####       the second field name is 'd' and the type is 'R' (real number)

####       $force:  if given, never mind about errors

####       $mix:    if 0, use the same argument at a time in a 

####                function that has two or more same type arguments

####                if 1, use different values

####

sub test_func()

{

  my ($func, $value, $count, $ulargs, $table_info, $force, $mix) = @_;

  my ($query, $i, $j, $k, $no_arg, $row, $ulimit, $tbinfo, $tbused, $arg);

  if (!$func->[0][0])

  {

    printf("No function found!\n");

    if (!$force) { die; }

  }

  for ($i = 0; $func->[$i][0]; $i++)

  {

    $tbused = 0;

    $no_arg = 0;

    for ($j = 0; $j < $count && !$no_arg; $j++)

    {      

      if ($tbused || $no_arg) { next; }

      $query = "select $func->[$i][0](";

      #search the values for the args

      for ($k = 0; $k < length($func->[$i][1]) && !$no_arg; $k++)

      {

	if ($mix)

	{

	  $arg = $j + 1 + $k;

	}

	else

	{

	  $arg = $j + 1;

	}

	if (substr($func->[$i][1], $k, 1) eq 'E')

	{

	  $no_arg = 1;

	  next;

	}

	if ($k) { $query .= ','; }

	if (substr($func->[$i][1], $k, 1) eq 'S')

	{

	  $query .= &find_value(\@value, 'S', $arg);

	}

	elsif (substr($func->[$i][1], $k, 1) eq 'N')

	{

	  $query .= &find_value(\@value, 'N', $arg);

	}

	elsif (substr($func->[$i][1], $k, 1) eq 'Z')

	{

	  $query .= &find_value(\@value, 'Z', $arg);

	}

	elsif ((substr($func->[$i][1], $k, 1) eq 'R'))

	{

	  $query .= &find_value(\@value, 'R', $arg);

	}

	elsif (substr($func->[$i][1], $k, 1) eq 'T')

	{

	  $query .= &find_value(\@value, 'T', $arg);

	}

	elsif (substr($func->[$i][1], $k, 1) eq 'D')

	{

	  $query .= &find_value(\@value, 'D', $arg);

	}

	elsif (substr($func->[$i][1], $k, 1) eq 'B')

	{

	  $query .= &find_value(\@value, 'B', $arg);

	}

	elsif (substr($func->[$i][1], $k, 1) eq 'C')

	{

	  $query .= &find_value(\@value, 'C', $arg);

	}

	elsif (substr($func->[$i][1], $k, 1) eq 'F')

	{

	  $query .= &find_value(\@value, 'F', $arg);

	}

	elsif (substr($func->[$i][1], $k, 1) eq '.')

	{

	  chop($query);

	  for ($ulimit = 0; $ulimit < $ulargs; $ulimit++)

	  {

	    $query .= ',';

	    $query .= &find_value(\@value,

				  substr($func->[$i][1], $k - 1, 1),

				  $j + $ulimit + 2);

	  }

	}

	elsif (substr($func->[$i][1], $k, 1) eq 'A')

	{

	  for ($tbinfo = 1; substr($table_info->[$tbinfo], 0, 1) ne

	       substr($func->[$i][1], $k + 1, 1); $tbinfo++)

	  {

	    if (!defined($table_info->[$tbinfo]))

	    {

	      printf("Illegal function structure!\n");

	      printf("A table was needed, but no type specified!\n");

	      printf("Unready query was: $query\n");

	      if (!$force) { die; }

	      else { next; }

	    }

	  }

	  if ($k) { $query .= ","; }

	  $query .= substr($table_info->[$tbinfo], 1,

			   length($table_info->[$tbinfo]) - 1);

	  $k++;

	  $tbused = 1;

	}

   	else

	{

	  printf("Not a valid type: \n");

	  printf(substr($func->[$i][1], $k, 1));

	  printf("\nAttempted to be used with unready query: \n");

	  printf("$query\n");

	}

      }

      $query .= ")";

      if ($tbused)

      {

	$query .= " from ";

	$query .= $table_info->[0];

      }

      if (!($sth = $dbh->prepare($query)))

      {

	printf("Couldn't prepare: $query\n");

	if (!$force) { die; }

      }

      if (!$sth->execute)

      {

	printf("Execution failed: $DBI::errstr\n");

	printf("Attempted query was:\n$query\n");

	$sth->finish;

	if (!$force) { die; }

      }

      else 

      { 

	printf("mysql> $query;\n");

	display($sth, 1);

	printf("Query OK\n\n");

      }

    }

  }

}

####

#### mk_str returns a string where the first arg is repeated second arg times

#### if repeat is 1, return the original str

####

sub mk_str()

{

  my ($str, $repeat) = @_;

  my ($res_str);

  if ($repeat <= 0)

  {

    die "Invalid repeat times!\n";

  }

  for ($repeat--, $res_str = $str; $repeat > 0; $repeat--)

  {

    $res_str .= $str;

  }

  return $res_str;

}

####

#### find_value: returns a value from list of values

#### args: $values:  list of values

####       $type:    type of argument (S = string, N = integer etc.)

####       $ordinal: the ordinal number of an argument in the list

####

sub find_value()

{

  my ($values, $type, $ordinal) = @_;

  my ($total, $i, $j, $tmp, $val);

  $total = -1; # The first one is the type

  for ($i = 0; $values[$i][0]; $i++)

  {

    if ($values[$i][0] eq $type)

    {

      $tmp = $values[$i];

      foreach $val (@$tmp) { $total++; }

      for ( ;$total < $ordinal; )

      {

	$ordinal -= $total;

      }

      return $values[$i][$ordinal];

    }

  }

  printf("No type '$type' found in values\n");

  die;

}

####

#### exec_query: execute a query, print information if wanted and exit

#### args: $queries:      list of queries to be executed

####       $expect_error: if 0, error is not expected. In this case if an

####                      error occurs, inform about it and quit

####                      if 1, error is expected. In this case if sql server

####                      doesn't give an error message, inform about it

####                      and quit

####                      if 2, error may happen or not, don't care

####       $silent:       if true, reduce output

####       $style:        type of output, 0 == horizontal, 1 == vertical

####

sub exec_query()

{

  my ($queries, $expect_error, $silent, $style) = @_;

  my ($query);

  foreach $query (@$queries)

  {

    if (!($sth = $dbh->prepare($query)))

    {

      printf("Couldn't prepare: $query\n");

      die;

    }

    if (!$sth->execute)

    {

      if ($expect_error == 1)

      {

        printf("An invalid instruction was purposely made,\n"); 

        printf("server failed succesfully:\n");

        printf("$DBI::errstr\n");

        printf("Everything OK, continuing...\n");

        return;

      }

      if ($expect_error != 2)

      {

        printf("Execution failed: $DBI::errstr\n");

        printf("Attempted query was:\n$query\n");

        die;

      }

    }

    if ($expect_error == 1)

    {

      printf("An invalid instruction was purposely made,\n");

      printf("server didn't note, ALARM!\n");

      printf("The query made was: $query\n");

      printf("The output from the server:\n");

    }

    if ($expect_error == 2) { return; }

    if (!$silent) { printf("mysql> $query;\n"); }

    display($sth, $style);

    if (!$silent) { printf("Query OK\n\n"); }

    if ($expect_error) { die; }

  }

  return;

}

####

#### Display to stderr

#### Args: 1: ($sth) statememt handler

####       2: ($style) 0 == horizontal style, 1 == vertical style

####

sub display()

{

  my ($sth, $style) = @_;

  my (@data, @max_length, $row, $nr_rows, $nr_cols, $i, $j, $tmp, $mxl);

  # Store the field names and values in @data.

  # Store the max field lengths in @max_length

  for ($i = 0; ($row = $sth->fetchrow_arrayref); $i++)

  {

    if (!$i)

    {

      $nr_cols = $#$row;

      for ($j = 0; $j <= $#$row; $j++)

      {

        $data[$i][$j] = $sth->{NAME}->[$j];

        $max_length[$j] = length($data[$i][$j]);

      }

      $i++;

    }

    for ($j = 0; $j <= $#$row; $j++)

    {

      $data[$i][$j] = $row->[$j];

      $max_length[$j] = $tmp if ($max_length[$j] < 

				 ($tmp = length($data[$i][$j])));

    }

  }

  if (!($nr_rows = $i))

  {

    return;

  }

  # Display data

  if ($style == 0)

  {

    for ($i = 0; $i < $nr_rows; $i++)

    {

      if (!$i)

      {

        for ($j = 0; $j <= $nr_cols; $j++)

        {

          print "+"; print "-" x ($max_length[$j] + 2);

        }

        print "+\n";

      }

      print "|";

      for ($j = 0; $j <= $nr_cols; $j++)

      {

        print " ";

        if (defined($data[$i][$j]))

        {

          print $data[$i][$j];

	  $tmp = length($data[$i][$j]);

        }

	else

	{

	  print "NULL";

	  $tmp = 4;

	}

        print " " x ($max_length[$j] - $tmp);

        print " |";

      }

      print "\n";

      if (!$i)

      {

        for ($j = 0; $j <= $nr_cols; $j++)

        {

          print "+"; print "-" x ($max_length[$j] + 2);

        }

        print "+\n";

      }

    }

    for ($j = 0; $j <= $nr_cols; $j++)

    {

      print "+"; print "-" x ($max_length[$j] + 2);

    }

    print "+\n";

    return;

  }

  if ($style == 1)

  {

    for ($i = 0; $max_length[$i]; $i++)

    {

      $mxl = $max_length[$i] if ($mxl < $max_length[$i]);

    }

    for ($i = 1; $i < $nr_rows; $i++)

    {

      print "*" x 27;

      print " " . $i . ". row ";

      print "*" x 27;

      print "\n";

      for ($j = 0; $j <= $nr_cols; $j++)

      {

	print " " x ($mxl - length($data[0][$j]));

	print "$data[0][$j]: ";

	if (defined($data[$i][$j]))

	{

	  print "$data[$i][$j] \n";

	}

	else

	{

	  print "NULL\n";

	}

      }

    }

    return;

  }

}

####

#### usage

####

sub usage

{

    print <<EOF;

mysql-test $VER by Jani Tolonen

Usage: mysql-test [options]

Options:

--help      Show this help

--db=       Database to use (Default: $opt_db)

--port=     TCP/IP port to use for connection (Default: $opt_port)

--socket=   UNIX socket to use for connection (Default: $opt_socket)

--host=     Connect to host (Default: $opt_host)

--user=     User for login if not current user

--password  Password to use when connecting to server

--without=PART_NAME1,PART_NAME2,...  

            test without a certain part of MySQL, optional parts listed below

Optional parts:

FUNC        Ignore MySQL basic functions

PROC        Ignore MySQL procedure functions

EOF

  exit(0);

}

sub test_mysql_functions

{

  ####

  #### MySQL functions

  ####

  #### Types: S = string (or real number) , N = unsigned integer, Z = integer, 

  ####        R = real number, T = time_stamp, E = no argument, D = date, 

  ####        B = boolean, C = character

  ####        F = format (usually used with the date-types)

  ####        . = any number of the last argument type possible

  ####        A = require table for test, the following argument 

  ####            is the argument for the function

  # Muista get_lock,group_unique_users,

  # position, unique_users

  # ks. kaikki date function, kerää yhteen, testaa erikseen

  # adddate, date_add, subdate, date_sub, between, benchmark, count

  # decode, encode, get_lock, make_set, position

  @functions = (["abs","R"],["acos","R"],["ascii","C"],["asin","R"],

		["atan","R"],["atan2","R"],["avg","AR"],["bin","Z"],

		["bit_count","Z"],["bit_or","AZ"],["bit_and","AZ"],

		["ceiling","R"],["char","N."],["char_length","S"],

		["concat","SS."],["conv","ZZZ"],

		["cos","R"],["cot","R"],["curdate","E"],

		["curtime","E"],["database","E"],["date_format","DF"],

		["dayofmonth","D"],["dayofyear","D"],["dayname","D"],

		["degrees","R"],["elt","NS."],["encode","SS"],

		["encrypt","S"],["encrypt","SS"],["exp","R"],["field","SS."],

		["find_in_set","SS"],["floor","R"],["format","RN"],

		["from_days","N"],["from_unixtime","N"],

		["from_unixtime","NF"],["greatest","RR."],["hex","Z"],

		["hour","D"],["if","ZSS"],["ifnull","SS"],["insert","SNNS"],

		["instr","SS"],["interval","RR."],["isnull","S"],

		["last_insert_id","E"],["lcase","S"],["least","RR."],

		["left","SN"],["length","S"],["locate","SS"],

		["log","R"],["log10","R"],["lpad","SNS"],["ltrim","S"],

		["max","AR"],["mid","SNN"],["min","AR"],["minute","D"],

		["mod","ZZ"],["monthname","D"],

		["month","D"],["now","E"],["oct","Z"],

		["octet_length","S"],["password","S"],["period_add","DD"],

		["period_diff","DD"],["pi","E"],

		["pow","RR"],["quarter","D"],["radians","R"],

		["rand","E"],["rand","R"],["release_lock","S"],

		["repeat","SN"],["replace","SSS"],["reverse","S"],

		["right","SN"],["round","R"],["round","RN"],

		["rpad","SNS"],["rtrim","S"],["sec_to_time","N"],

		["second","T"],["sign","R"],["sin","R"],

		["space","N"],["soundex","S"],["sqrt","R"],["std","AR"],

		["strcmp","SS"],["substring","SN"],["substring","SNN"],

		["substring_index","SSZ"],["sum","AR"],

		["tan","R"],["time_format","TF"],["time_to_sec","T"],

		["to_days","D"],["trim","S"],

		["truncate","RN"],["ucase","S"],

		["unix_timestamp","E"],["unix_timestamp","D"],["user","E"],

		["version","E"],["week","D"],["weekday","D"],["year","D"]);

  ####

  #### Various tests for the functions above

  ####

  &exec_query(["drop table mysql_test1"], $MAY_ERR, $SILENT);

  $query .= <<EOF;

create table mysql_test1 (

  i int,

  d double

)

EOF

  chop($query);

  &exec_query([$query], $NO_ERR, $SILENT);

  ####

  #### Basic tests

  ####

  printf("####\n#### BASIC TESTS FOR FUNCTIONS\n####\n\n");

  @bunch = ("insert into mysql_test1 values(-20,-10.5),(20,10.5),(50,100.00)",

	    "insert into mysql_test1 values(100,500.333)");

  &exec_query(\@bunch, $NO_ERR, $SILENT);

  printf("\n####\n#### First basic test part\n####\n\n");

  @values = (["S", "'a'", "'abc'", "'abc def'", "'abcd'", "'QWERTY'", 

	      "'\\\\'", "'*.!\"#€%&/()'", "'" . &mk_str('a',1024) . "'",

	      "?", "<>", "#__#"],

	     ["N", -1000, -500, -100, -1, 0, 1, 40, 50, 70, 90,

	      100, 500, 1000],

	     ["Z", -100, -50, 200, 1000],

	     ["R", -500.5, -10.333, 100.667, 400.0],

	     ["T", 19980728154204, 19980728154205, 19980728154206,

	      19980728154207],

	     ["D", "'1997-12-06'", "'1997-12-07'", "'1997-12-08'", 

	      "'1997-12-09'"],

	     ["B", 1, 0, 0, 1],

	     ["C", "'a'", "'e'", "'r'", "'q'"],

	     ["F", "'%a'", "'%b'", "'%d'", "'%H'"]);

  &test_func(\@functions, \@values, 4, 5, ["mysql_test1","Zi","Rd"]);

  printf("\n####\n#### Second basic test part\n####\n\n");

  @values = (["S", "'a'", "'BC'", "'def'", "'HIJK'", "'lmnop'", "'QRSTUV'"],

	     ["N", 0, 1, 2, 3, 4, 5],

	     ["Z", 0, 1, 2, 3, 4, 5],

	     ["R", 0, 1, 2, 3, 4, 5],

	     ["T", 19990608234530, 20000709014631, 20010810024732,

	      20020911034833, 20031012044934, 20041113055035],

	     ["D", "'1999-06-08'", "'2000-07-09'", "'2001-08-10'",

	      "'2002-09-11'", "'2003-10-12'", "'2004-11-13'"],

	     ["B", 0, 1, 0, 1, 0, 1],

	     ["C", "'a'", "'BC'", "'def'", "'HIJK'", "'lmnop'", "'QRSTUV'"],

	     ["F", "'%a'", "'%b'", "'%d'", "'%h'", "'%H'", "'%i'"]);

  &test_func(\@functions, \@values, 6, 6, ["mysql_test1","Zi","Rd"], 0, 1);

  printf("\n####\n#### Third basic test part\n####\n\n");

  @values = (["S", "'Monty'", "'Jani'", "'MySQL'", "''"],

	     ["N", 10, 54, -70, -499],

	     ["Z", 11.03, "'Abo'", 54.333, "''"],

	     ["R", 12, "'gnome'", -34.211, "''"],

	     ["T", 3, "'Redhat'", -19984021774433, "''"],

	     ["D", "'1990-01-31'", "'-3333-10-23'", -5631_23_12, "''"],

	     ["B", 0, "'asb'", -4, "''"],

	     ["C", "'a'", 503, -45353453, "''"],