Ecd0p6Pe

1# test driver for mysql -- assumes you can create a table called test_sql in the test database
2
3:>data.csv
4
5mysql -e 'CREATE TABLE IF NOT EXISTS `test_sql` (   `sys` int(11) default NULL,   `port` int(11) default NULL,   `type` char(20) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;' test
6
7while read i; do
8    if [ "$i" = "" ]; then
9        if [ $data = 1 ]; then
10            mysql -e 'truncate test_sql' test
11            mysql -e 'load data local infile "data.csv" into table test_sql fields terminated by ","' test
12            data=0
13        fi
14    else
15    
16        if [ "$data" = 1 ]; then
17            echo "$i" >> data.csv
18        else
19            if [ "${i:0:4}" = "TEST" ]; then
20                testname=${i:5}
21                :>data.csv
22                data=1
23            else
24                set -- $i
25                want_a=$1
26                want_b=$2
27                type_a=$3
28                type_b=$4
29                wanted=$6
30                query="select sys as system from (select a.sys, count(distinct a.port) as want_a, count(distinct b.port) as want_b from test_sql a left join test_sql b on a.sys=b.sys and a.port<>b.port and a.type<>b.type where a.type='$type_a' and (b.type='$type_b' or b.type is null) and a.sys in (select sys from test_sql group by sys having count(distinct port) >= $want_a+$want_b) group by a.sys having want_a >= $want_a and want_b >= $want_b) z;"
31                result=$(mysql -B -e "$query" test | sed -e 1d | tr '\n' '|')
32                echo -n "$testname - $want_a * $type_a + $want_b * $type_b = "
33                if [ "$wanted" = "$result" ]; then
34                    echo "PASS ($wanted)"
35                else
36                    echo "FAIL $result"
37                fi
38            fi
39        fi
40    fi
41done