gistfile1.txt

TEST 1 - one 100BASE-TX port
1,1,100BASE-TX

1 0 100BASE-TX 1000BASE-TX => 1|

TEST 2 - one 100BASE-TX port
1,1,100BASE-TX

2 0 100BASE-TX 1000BASE-TX =>
0 1 100BASE-TX 1000BASE-TX =>

TEST 3 - two 100BASE-TX ports
1,1,100BASE-TX
1,2,100BASE-TX

1 0 100BASE-TX 1000BASE-TX => 1|
2 0 100BASE-TX 1000BASE-TX => 1|
2 1 100BASE-TX 1000BASE-TX =>
0 1 100BASE-TX 1000BASE-TX =>
0 2 100BASE-TX 1000BASE-TX =>

TEST 4 - 100BASE-TX/1000BASE-TX + 100BASE-TX ports
1,1,100BASE-TX
1,1,1000BASE-TX
1,2,100BASE-TX

1 0 100BASE-TX 1000BASE-TX => 1|
2 0 100BASE-TX 1000BASE-TX => 1|
2 1 100BASE-TX 1000BASE-TX =>
0 1 100BASE-TX 1000BASE-TX => 1|
0 2 100BASE-TX 1000BASE-TX =>

TEST 5 - two 100BASE-TX/1000BASE-TX ports
1,1,100BASE-TX
1,1,1000BASE-TX
1,2,100BASE-TX
1,2,1000BASE-TX

1 0 100BASE-TX 1000BASE-TX => 1|
2 0 100BASE-TX 1000BASE-TX => 1|
2 1 100BASE-TX 1000BASE-TX =>
1 1 100BASE-TX 1000BASE-TX => 1|
0 1 100BASE-TX 1000BASE-TX => 1|
0 2 100BASE-TX 1000BASE-TX => 1|

TEST 6 - Stack Overflow data
1,1,10BASE-T
1,1,100BASE-TX
1,1,1000BASE-TX
1,2,10BASE-T
1,2,100BASE-TX
1,2,1000BASE-TX
1,3,10BASE-T
1,3,100BASE-TX
1,3,1000BASE-TX
2,1,100BASE-TX
2,2,100BASE-TX
2,3,100BASE-TX

2 1 100BASE-TX 1000BASE-TX => 1|
2 2 100BASE-TX 1000BASE-TX =>
3 1 100BASE-TX 1000BASE-TX =>
3 0 100BASE-TX 1000BASE-TX => 1|2|
1 0 100BASE-TX 1000BASE-TX => 1|2|
2 0 100BASE-TX 1000BASE-TX => 1|2|
3 0 100BASE-TX 1000BASE-TX => 1|2|
1 2 100BASE-TX 1000BASE-TX => 1|
0 1 100BASE-TX 1000BASE-TX => 1|
0 2 100BASE-TX 1000BASE-TX => 1|
0 3 100BASE-TX 1000BASE-TX => 1|

gistfile2.sh

:>data.csv
rm -f blank.db

sqlite3 blank.db "create table test (sys int, port int, type char(20));"

while read i; do
    if [ "$i" = "" ]; then
        if [ $data = 1 ]; then
            cp -f blank.db query.db
            (echo ".mode csv"; echo ".import data.csv test") | sqlite3 query.db
            data=0
        fi
    else

            if [ "$data" = 1 ]; then
                echo "$i" >> data.csv
            else
                    if [ "${i:0:4}" = "TEST" ]; then
                testname=${i:5}
                :>data.csv
                        data=1
                    else
                set -- $i
                want_a=$1
                want_b=$2
                type_a=$3
                type_b=$4
                wanted=$6
                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 a left join test 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 group by sys having count(di
stinct port) >= $want_a+$want_b) group by a.sys having want_a >= $want_a and want_b >= $want_b);"
                result=$(sqlite3 query.db "$query" | tr '\n' '|')
                echo -n "$testname - $want_a * $type_a + $want_b * $type_b = "
                if [ "$wanted" = "$result" ]; then
                    echo "PASS ($wanted)"
                else
                    echo "FAIL $result"
                fi
#                       echo "RUN TEST: $i -> $result"
                    fi
            fi
    fi
done

gistfile3.sql

select sys as system from (
  select a.sys, count(distinct a.port) as want_a, count(distinct b.port) as want_b
  from test a left join test 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 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;

mysql.sh

# test driver for mysql -- assumes you can create a table called test_sql in the test database

:>data.csv

mysql -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

while read i; do
    if [ "$i" = "" ]; then
        if [ $data = 1 ]; then
            mysql -e 'truncate test_sql' test
            mysql -e 'load data local infile "data.csv" into table test_sql fields terminated by ","' test
            data=0
        fi
    else
    
        if [ "$data" = 1 ]; then
            echo "$i" >> data.csv
        else
            if [ "${i:0:4}" = "TEST" ]; then
                testname=${i:5}
                :>data.csv
                data=1
            else
                set -- $i
                want_a=$1
                want_b=$2
                type_a=$3
                type_b=$4
                wanted=$6
                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;"
                result=$(mysql -B -e "$query" test | sed -e 1d | tr '\n' '|')
                echo -n "$testname - $want_a * $type_a + $want_b * $type_b = "
                if [ "$wanted" = "$result" ]; then
                    echo "PASS ($wanted)"
                else
                    echo "FAIL $result"
                fi
            fi
        fi
    fi
done