DP8Uaj2t

1#!/bin/bash
2
3# Usage
4# doTest numtable numrows numrowstocreate
5#
6# e.g. doTest 1 10000 10000: it will search and execute the test_table1.sql file, fill the table with 10000 rows and 
7# execute all the query_*.sql queries on it. The query_*.sql must use "@tablename" instead of the final tablename 
8# (that has to be of the form "data<numtable>" 
9#
10# e.g. doTest 1 10000 0: it won't execute the test_table1.sql file, and won't fill the table: it will just 
11# execute all the query_*.sql queries on it. The query_*.sql must use "@tablename" instead of the final tablename 
12# (that has to be of the form "data<numtable>" 
13#
14# test results are in a test_results table
15  
16if [ "$3" -ne "0" ]; then
17echo "First table"
18mysql mydb -e "use mydb; source test_table$1.sql;"
19
20echo "temp table"
21mysql mydb -e "use mydb;DROP TABLE IF EXISTS tmp$1; CREATE TABLE tmp$1 ( id integer NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
22
23echo "fill temp table"
24mysql 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
25ect 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 
26union 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
27 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;"
28
29echo "fill table"
30
31
32let "limitValue=$2+20"
33read -d '' sqlcommand1 <<- EOF
34/*---------SQL BEGIN------------*/
35use mydb;
36
37create table if not exists test_results (
38 id int not null auto_increment primary key,
39 tablename varchar(16),
40 query_descr varchar(255),
41 num_rows integer,
42 completed tinyint(4),
43 start_ts timestamp(6) default current_timestamp(6),
44 end_ts timestamp(6) on update current_timestamp(6) 
45) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
46
47SET autocommit=0;
48SET unique_checks=0;
49SET foreign_key_checks=0;
50
51SET @rank=0; 
52insert into data$1 (uri, category, value) 
53  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;
54
55
56SET @rank=$2-20; 
57insert into data$1 (uri, category, value) 
58       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;
59
60SET autocommit=1;
61SET unique_checks=1;
62SET foreign_key_checks=1;
63
64/*---------SQL END--------------*/
65
66EOF
67
68mysql mydb -e "${sqlcommand1}"
69fi;
70
71echo "start query suite"
72
73for testfile in query_*.sql 
74do
75description="$(echo "$testfile"| cut -c7-| cut -d\. -f1)"
76query=$(cat $testfile| sed -e s/@tablename/data$1/g)
77read -d '' sqlcommand <<- EOF
78/*---------SQL BEGIN------------*/
79use mydb;
80
81delete from test_results where tablename='data$1' and num_rows=$2 and completed=0;
82insert into test_results (tablename, completed, num_rows, query_descr, end_ts) values ('data$1',0,$2,'$description',null);
83
84RESET QUERY CACHE;
85
86$query
87
88update test_results set completed=1 where tablename='data$1' and num_rows=$2 and query_descr='$description';
89/*---------SQL END--------------*/
90
91EOF
92mysql mydb -e "${sqlcommand}"