Test-DB Reducer: Automated Reduction of Bug-Triggering SQL Queries

Part 2 of the Project from the Automated Software Testing course @ ETH Zurich.

Author	Links
Lennart K. M. Schulz	GitHub, LinkedIn
Dovydas Vadišius	GitHub, LinkedIn

Report, Project Description

Installation

The reducer can either be installed locally:

pip install .

or run as a docker container:

docker build . --platform linux/amd64 -t test-db-reducer:latest
docker run test-db-reducer:latest <command>

The main entrypoint (reducer) is on PATH in the container.

Alternatively, the docker-compose file can be used to run the container in the background with the current directory mounted at ~/repo and then get an interactive shell into the container:

docker compose up -d
docker exec -it <container_name> /bin/bash

For development purposes it may help to use an editable pip installation (either locally or in the docker container):

pip install --editable .

This installation will always reflect the changes made to the source files without re-installation.

Requirements

Note that the docker image already has all requirements satisfied.

For a local installation, however it is important that a Java Runtime Environment is installed and python3.12 is used to run the reducer. All further dependencies are downloaded and installed automatically when doing pip install ..

To use the provided validator tool, SQLite installations for versions 3.26.0 and 3.39.4 additionally need to be available in /usr/bin (already provided in the docker image).

Reducer

The reducer can be started as follows:

reducer [Flags] --query <query-to-minimize> --test <query-test-script>

where the --query should specify the path to the SQL file with the query to be minimized and --test should specify the path of a shell script that checks whether the minimized query still triggers the bug.

The output path for the final reduced query can be specified by setting the TEXT_CASE_LOCATION environment variable.

The complete flags and usage options are shown below.

usage: reducer [-h] [-q QUERY] [-t TEST] [-v] [-s] [-o OUT]

options:
  -h, --help            show this help message and exit
  -q QUERY, --query QUERY
                        path of query file
  -t TEST, --test TEST  Validator script
  -v, --verbose
  -s, --store           store statistics
  -o OUT, --out OUT     path for statistics

Shortcut Script

When working with the queries provided in this repo (contained in the queries directory), the reducer can be invoked with via the shortcut script:

./reducer-short <query-to-minimize>