#!/bin/sh
# this script generates the SQL queries

benchnumout()
{
 echo "-------------------------------------${bench}----------------------"
}

# ------------------------------------------------------------------------
echo "========== Start of SetQuery Single User Run ======================"
echo "==================================================================="
echo "Control query: select count(*) from bench"
cat <<END | $DBMS
select count(*) from bench 
$SQLDELIM
END
date

bench=Q1
benchnumout
cat << END
select count(*) from bench 
where kn  = 2

kn from { $LIST1 }
END

for i in $LIST1
do
cat <<END | $DBMS
select count(*) from bench where $i = 2 
$SQLDELIM 
END
 echo ${bench}_${i}
 date 
done

bench=Q2A
benchnumout
cat << END
select count(*) from bench 
where k2 = 2 and kn = 3 

kn from { $LIST1 }
END

for i in $LIST1
do
cat <<END | $DBMS
select count(*) from bench where k2 = 2 and $i = 3 
$SQLDELIM
END
 echo ${bench}_${i}
 date 
done

bench=Q2B 
benchnumout
cat << END
select count(*) from bench 
where k2 = 2 and not (kn = 3) 

kn from { $LIST1 }
END

# beware of parenthesis: and not $i = 3 always fails with MySQL
for i in $LIST1
do
cat <<END | $DBMS
select count(*) from bench where k2 = 2 and not ($i = 3) 
$SQLDELIM
END
 echo ${bench}_${i}
 date
done

bench=Q3A
benchnumout
cat << END
select sum(k1k) from bench 
where kseq between 400000 and 500000 and kn = 3

kn from { $LIST2 }
END

for i in $LIST2
do
cat << END | $DBMS
select sum(k1k) from bench 
where kseq between 400000 and 500000 and $i = 3 
$SQLDELIM
END
 echo ${bench}_${i}
 date 
done

bench=Q3B
benchnumout
cat << END
select sum(k1k) from bench 
where (kseq between 400000 and 410000) 
 or (kseq between 420000 and 430000) 
 or (kseq between 440000 and 450000) 
 or (kseq between 460000 and 470000) 
 or (kseq between 480000 and 500000) 
 and kn = 3

kn from { $LIST2 }
END

for i in $LIST2
do
cat << END | $DBMS
select sum(k1k) from bench
where (kseq between 400000 and 410000) 
or (kseq between 420000 and 430000) 
or (kseq between 440000 and 450000) 
or (kseq between 460000 and 470000) 
or (kseq between 480000 and 500000) 
and $i = 3 
$SQLDELIM
END
 echo ${bench}_${i}
 date
done

bench=Q4A
benchnumout
cat << END
select kseq, k500k from bench 
where COND1 and COND2 and COND3

COND1,2,3 from C1, C2,
C3, C4, 
C5, C6, 
C7, C8, 
C9, CA
END

cat <<END | $DBMS
select kseq,k500k from bench where 
$C1 and $C2 and $C3 
$SQLDELIM
END
echo ${bench}_${C1}-${C2}-${C3}
date 

cat <<END | $DBMS
select kseq,k500k from bench where 
$C2 and $C3 and $C4 
$SQLDELIM
END
echo ${bench}_${C2}-${C3}-${C4}
date

cat  <<END | $DBMS
select kseq,k500k from bench where
$C3 and $C4 and $C5 
$SQLDELIM
END
echo ${bench}_${C3}-${C4}-${C5}
date

cat  <<END | $DBMS
select kseq,k500k from bench where
$C4 and $C5 and $C6 
$SQLDELIM
END
echo ${bench}_${C4}-${C5}-${C6}
date

cat  <<END | $DBMS
select kseq,k500k from bench where
$C5 and $C6 and $C7 
$SQLDELIM
END
echo ${bench}_${C5}-${C6}-${C7}
date

cat  <<END | $DBMS
select kseq,k500k from bench where
$C6 and $C7 and $C8 
$SQLDELIM
END
echo ${bench}_${C6}-${C7}-${C8}
date

cat  <<END | $DBMS
select kseq,k500k from bench where
$C7 and $C8 and $C9 
$SQLDELIM
END
echo ${bench}_${C7}-${C8}-${C9}
date

cat  <<END | $DBMS
select kseq,k500k from bench where
$C8 and $C9 and $CA 
$SQLDELIM
END
echo ${bench}_${C8}-${C9}-${CA}
date

bench=Q4B
benchnumout
cat << END
The same as Q3B, but with five conditions instead of three
END

cat  <<END | $DBMS
select kseq,k500k from bench where
$C1 and $C2 and $C3 and $C4 and $C5 
$SQLDELIM
END
echo ${bench}_${C1}-${C2}-${C3}-${C4}-${C5}
date

cat  <<END | $DBMS
select kseq,k500k from bench where
$C2 and $C3 and $C4 and $C5 and $C6 
$SQLDELIM
END
echo ${bench}_${C2}-${C3}-${C4}-${C5}-${C6}
date

cat  <<END | $DBMS
select kseq,k500k from bench where
$C3 and $C4 and $C5 and $C6 and $C7 
$SQLDELIM
END
echo ${bench}_${C3}-${C4}-${C5}-${C6}-${C7}
date

cat  <<END | $DBMS
select kseq,k500k from bench where
$C4 and $C5 and $C6 and $C7 and $C8 
$SQLDELIM
END
echo ${bench}_${C4}-${C5}-${C6}-${C7}-${C8}
date

cat  <<END | $DBMS
select kseq,k500k from bench where
$C5 and $C6 and $C7 and $C8 and $C9 
$SQLDELIM
END
echo ${bench}_${C5}-${C6}-${C7}-${C8}-${C9}
date

cat  <<END | $DBMS
select kseq,k500k from bench where
$C6 and $C7 and $C8 and $C9 and $CA 
$SQLDELIM
END
echo ${bench}_${C6}-${C7}-${C8}-${C9}-${CA}
date

cat  <<END | $DBMS
select kseq,k500k from bench where
$C7 and $C8 and $C9 and $CA and $C1 
$SQLDELIM
END
echo ${bench}_${C7}-${C8}-${C9}-${CA}-${C1}
date


bench=Q5
benchnumout
cat <<END
select kn1,kn2,count(*) from bench
group by kn1,kn2

kn1,kn2 from { $LIST3 }
END

for i in $LIST3
do
cat  <<END | $DBMS
select $i ,count(*) from bench
group by $i 
$SQLDELIM
END
 echo ${bench}_{$i}
 date
done

bench=Q6A
benchnumout
cat <<END
select count(*) from bench b1, bench b2 where 
b1.kn = 49 and b1.k250k = b2.k5

kn from { $LIST4 }
END

for i in $LIST4
do
cat  <<END | $DBMS
select count(*) from bench b1, bench b2 where
b1.$i = 49 and b1.k250k = b2.k5 
$SQLDELIM
END
 echo ${bench}_${i}
 date
done

bench=Q6B
benchnumout
cat <<END
select b1.kseq , b2.kseq from bench b1, bench b2 where 
b1.kn = 99 
and b1.k250k = b2.k500k 
and b2.k25 = 19

kn from { $LIST5 }
END

for i in $LIST5
do
cat  <<END | $DBMS
select b1.kseq , b2.kseq from bench b1, bench b2 where
b1.$i = 99
and b1.k250k = b2.k500k
and b2.k25 = 19 
$SQLDELIM
END
 echo ${bench}_${i}
 date
done

echo "=================End of Single User Run============================="
echo "===================================================================="
echo "======== END OF STANDARD SET QUERY BENCHMARK========================"

