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.
Process is key to querying with confidence: businesses must use methods that foster query correctness. But, within these methods, technologies can assist the people who write and test queries. We are developing a query correctness platform , whose tools will assist programmers in testing and debugging complex SQL queries. The platform comprises a Query-Aware Database Generator and a Query Audit Tool.
Query-Aware Database Generator
The 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
Query Audit Tool
With the Query Audit Tool, programmers can visualize result provenance : it shows which records in base tables contribute to each record in a query’s result table. With this tool, programmers and auditors can determine why a record appears in a query’s result table. A demonstration of the tool can be found at the address https://ceadar.ucd.ie/video/ under ‘Data Validation’.
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.
- Barry O’Sullivan
- Derek Bridge
- Ken Brown
- Liam O’Toole
- Alexandre Papadopoulos
- Paul Davern
For more information, please visit: https://licence.ucd.ie/tech/513