Project Description

Market Need

download-pdfDatabase 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.

Query Testing

  • 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.

database-generator-01

Technology Solution

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.

database-generator-02

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.

Applicability

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