Database queries are often business-critical. But there are few methods for ensuring their correctness. Businesses need new methods and tools that increase confidence in the correctness of both standard and ad-hoc queries.
- There may be no data at all, e.g. in a green field project; or, there may be data in a legacy system but data migration may not have happened yet; or, there may be data but it may be too sensitive to share with the software development team.
- When there is data, there is often so much of it that it is a challenge for someone to determine the expected results of a query.
- Testing many different SQL queries over the same data set introduces many challenges, such as, avoiding conflicts between the requirements of two similar queries.
The Query-Aware Database Generator populates a database with test data. The tool chooses the data by analyzing the programmer’s query. It keeps the amount of data small, for ease of comprehension. But it chooses data that `exercises‘ different conditions within the query and query variants to reveal likely errors.
The records are generated by a constraint-solving process.
A SQL query can be very complex involving multiple clauses and sub-queries, each of which produce intermediate results. The Query Aware Database Generator creates a database which is query-aware i.e. the contents ‘exercise’ the clauses of the query.
An analogy with conventional program testing explains what we mean by this: if a program contains an if-statement, then testing should at least ‘exercise’ all statements, i.e. execute them.
The test cases include data that makes the if-else-statement’s condition true and data that makes it false. Similarly, the generated database’s contents ‘exercise’ different cases, e.g. records that satisfy a join condition alongside records that do not satisfy a join condition.
This work is not restricted to particular market sectors. Any organization in which complex SQL is written will benefit from tools to improve query correctness.
Team Company Partners
- Barry O’Sullivan
- Derek Bridge
- Ken Brown
- Paul Davern
- Liam O’Toole