DdBRm5C2

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