Test Suite

#!/bin/bash

# Usage
# doTest numtable numrows numrowstocreate
#
# e.g. doTest 1 10000 10000: it will search and execute the test_table1.sql file, fill the table with 10000 rows and
# execute all the query_*.sql queries on it. The query_*.sql must use "@tablename" instead of the final tablename
# (that has to be of the form "data<numtable>"
#
# e.g. doTest 1 10000 0: it won't execute the test_table1.sql file, and won't fill the table: it will just
# execute all the query_*.sql queries on it. The query_*.sql must use "@tablename" instead of the final tablename
# (that has to be of the form "data<numtable>"
#
# test results are in a test_results table

if [ "$3" -ne "0" ]; then
echo "First table"
mysql mydb -e "use mydb; source test_table$1.sql;"

echo "temp table"
mysql mydb -e "use mydb;DROP TABLE IF EXISTS tmp$1; CREATE TABLE tmp$1 ( id integer NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;"

echo "fill temp table"
mysql mydb -e "use mydb;INSERT INTO tmp$1 (id) select x from (select (t1.id-1)*100+(t2.id-1)*10+t3.id as x from (select 1 id union all select 2 id union all select 3 id union all select 4 id union all sel
ect 5 id union all select 6 id union all select 7 id union all select 8 id union all select 9 id union all select 10 id) t1, (select 1 id union all select 2 id union all select 3 id union all select 4 id
union all select 5 id union all select 6 id union all select 7 id union all select 8 id union all select 9 id union all select 10 id) t2, (select 1 id union all select 2 id union all select 3 id union all
 select 4 id union all select 5 id union all select 6 id union all select 7 id union all select 8 id union all select 9 id union all select 10 id) t3) tt;"

echo "fill table"


let "limitValue=$2+20"
read -d '' sqlcommand1 <<- EOF
/*---------SQL BEGIN------------*/
use mydb;

create table if not exists test_results (
 id int not null auto_increment primary key,
 tablename varchar(16),
 query_descr varchar(255),
 num_rows integer,
 completed tinyint(4),
 start_ts timestamp(6) default current_timestamp(6),
 end_ts timestamp(6) on update current_timestamp(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

SET @rank=0;
insert into data$1 (uri, category, value)
  select concat('uri',rank), 1, 'foo' from (select @rank:=@rank+1 as rank from tmp$1 t1, tmp$1 t2, tmp$1 t3 limit $2) dd;


SET @rank=$2-20;
insert into data$1 (uri, category, value)
       select concat('uri',rank), 2, rank%5 from (select @rank:=@rank+1 as rank from tmp$1 t1, tmp$1 t2, tmp$1 t3 limit $limitValue ) dd;

SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;

/*---------SQL END--------------*/

EOF

mysql mydb -e "${sqlcommand1}"
fi;

echo "start query suite"

for testfile in query_*.sql
do
description="$(echo "$testfile"| cut -c7-| cut -d\. -f1)"
query=$(cat $testfile| sed -e s/@tablename/data$1/g)
read -d '' sqlcommand <<- EOF
/*---------SQL BEGIN------------*/
use mydb;

delete from test_results where tablename='data$1' and num_rows=$2 and completed=0;
insert into test_results (tablename, completed, num_rows, query_descr, end_ts) values ('data$1',0,$2,'$description',null);

RESET QUERY CACHE;

$query

update test_results set completed=1 where tablename='data$1' and num_rows=$2 and query_descr='$description';
/*---------SQL END--------------*/

EOF
mysql mydb -e "${sqlcommand}"