SQLUnit

SQLUnit is a test runner for SQL queries, which is currently limited to MariaDB.

usage: sqlunit [OPTIONS] solutionFile [testFile1 [testFile2 [testFile3]
               ...]]
Runs all tests in the test files by executing the corresponding SQL
queries in the solution files and comparing the result with the expected
result..

OPTIONS:
 -o,--out <arg>        Output path for the resulting TREX report.
 -s,--solution <arg>   Comma separated list of paths to the solution
                       files.

If all tests have finished, exit with status 0. If an unexpected exception
occurs in the test runner, return status 1.

Solution Files

The code under test must consist of a sequence of query segments. Each segment is identified by an ID and contains the SQL query to execute:

solution.sql

-- QUERY eis:
select distinct(eisName)
from (select name from Eisdiele where ort = 'Wiesbaden')t1
join EisdieleVerkauftEis on t1.name= EisdieleVerkauftEis.dielenName order by eisName;

-- QUERY karteschlecker:
select t1.eisName from
        (select * from EisdieleVerkauftEis where dielenName='Lecker Schmecker') t1
                  natural join
        (select eisName, dielenName from EsserBervorzugtEis where dielenName='Lecker Schmecker')t2
        group by t1.eisName order by COUNT(preis) desc, eisName;

If a segment contains multiple queries, only the result of the last query is compared against the expected output.

Test Files

Test files must consist of segments with the same IDs as the code under test. Each segment defines the expected result of the associated query. The expected results are represented as key-value pairs, where the key is the column name and the value is the expected value. By default, the order of the returned rows must match the expected output. When unsorted: true is set for the expected result, the row order is ignored during comparison.

expected.txt

-- QUERY eis:
{"unsorted": true, "result":
  [ {"eisName": "Himbeere"}
  , {"eisName": "Schokolade"}
  , {"eisName": "Stracciatella"}
  , {"eisName": "Vanille" }
  ]
}

Build, Usage and Tests

Build SQLUnit with ./gradlew build. The sqlunit.jar fat jar is then located under build/libs. SQLUnit expects a MariaDB 10.6.x server to be running on localhost:3306 using the credentials root:root. A database named test must exist and may be pre-populated before the runner is executed.

SQLUnit writes the results in TREX format to the specified output file whenever the test state changes. A test is reported as failed if the query result does not match the expected output. A test is reported as an error if a query contains a syntax error or a required query segment is missing from the solution file. If an unexpected error occurs, SQLUnit exits with status code 1. In this case, the generated output file is invalid and should be ignored.

The SQLUnit project includes a test suite to verify the runner's functionality. During testing, a MariaDB server is started automatically using MariaDB4j and shut down after the tests complete. This requires the libncurses5 and libnsl libraries to be installed on the host system. The test suite can be run with ./gradlew test.