GitLocker: The Coding Marketplace

Description:

rdbunit 1.1.4

RDBUnit: Unit testing for relational database queries

RDBUnit is a unit testing framework for relational database queries.
It allows you to express in a simple way the setup prior to a query,
the query, and the expected results.
RDBUnit can test SELECT queries as well as queries that are used
for creating tables and views.
All types of queries can be either embedded into the test script, or
they can be included from an external file.
The tables for the input and the expected results can be created with
minimal ceremony: RDBUnit will automatically infer the types of the
tables' fields.
For complex relational OLAP queries RDBUnit can be combined particularly
effectively with the simple-rolap
relational online analytical processing framework.
You can find a complete tutorial on using RDBUnit with simple-rolap
for mining Git repositories in a
technical briefing
presented at the 2017 International Conference on Software Engineering.
You can cite this work as follows.

Diomidis Spinellis. Unit Tests for SQL. IEEE Software, vol. 41, no. 1, pp. 31–34, Jan.-Feb. 2024. doi: 10.1109/MS.2023.3328788.

Installation
Using Pip
pip install rdbunit

From source

Clone this repository

git clone --depth=1 https://github.com/dspinellis/rdbunit.git
cd rdbunit
pipenv shell
pip install .

Test specification
For every SQL query you want to test, create an RDBUnit file that
specifies the query's input, execution, and expected result.
Simple example
The following example illustrates this concept.
BEGIN SETUP
sales:
month revenue
March 130
April 50
END

BEGIN SELECT
SELECT MAX(revenue) as max_revenue FROM sales;
END

BEGIN RESULT
max_revenue
130
END

Input file details
The input and output and output are specified as table contents.
The input starts with a line containing the words BEGIN SETUP,
while the results start with a line containing the words
BEGIN RESULTS.
The input and output are specified by first giving a table's name,
followed by a colon.
The name may be prefixed by the name of a database where the table
is to reside, followed by a dot.
The next row contains the table's fields, separated by spaces.
Then comes the table's data, which is terminated by a blank line,
or by the word END.
More than one table can be specified in the setup.
In the results the table name is not specified, if the tested
query is a selection statement, rather than a table or view creation.
The setup-query-results sequence can be specified multiple times within
a test file.
Setup example
BEGIN SETUP
contacts:
name registered value reg_date
John true 12 '2015-03-02'
Mary false 10 '2012-03-02'
END

Results example (named table)
Named table results are used with CREATE queries.
BEGIN RESULT
leadership.nl_commits_leader_comments:
project_id n
1 3
END

Results example (unnamed set)
Unnamed set results are used with SELECT queries.
BEGIN RESULT
name registered value reg_date a
John True 12 '2015-03-02' Null
END

The query to test is either specified inline with a BEGIN SELECT (for
selection queries) or BEGIN CREATE (for creation queries) statement,
or by including a file through the corresponding INCLUDE SELECT or
INCLUDE CREATE statement.
Inline example
BEGIN SELECT
SELECT *, NULL AS a FROM contacts WHERE registered;
END

External query example
INCLUDE CREATE nl_commits_leader_comments.sql

Unit testing
To run the tests run RDBUnit piping its output to one of the supported
relational database systems (current MySQL, PostgreSQL, and sqLite).
A number of command-line flags allow you to tailor the operation of
RDBUnit.
When running, RDBUnit will report on its output something like
ok 1 - recent_commit_projects.rdbu: test_stratsel.recent_commit_projects
for each succeeding test case and
not ok 1 - project_stars.rdbu: test_stratsel.project_stars for each failing
test case.
A final line will list the number of succeeding and failing test cases.
By default RDBUnit creates and operates on temporary databases,
whose name is prefixed with the word test_.
By specifying the --results option (or the equivalent -r short option)
you can direct rdbunit to display the table generated for each test.
This is useful for debugging test failures or for generating reference data
(after suitable manual verification).
Execution example (SQLite)
$ rdbunit --database=sqlite recent_commit_projects.rdbu | sqlite3
ok 1 - recent_commit_projects.rdbu: test_stratsel.recent_commit_projects

Execution example (MySQL)
$ rdbunit commits_comments.rdbu | mysql -u root -p -N
Enter password:
ok 1 - commits_comments.rdbu: test_leadership.nl_commits_leader_comments
ok 2 - commits_comments.rdbu: test_leadership.leader_commits_nl_comments
ok 3 - commits_comments.rdbu: test_leadership.commits_with_comments
1..3

Execution example (PostgreSQL)
$ rdbunit --database=postgresql commits_comments.rdbu | psql -U ght -h 127.0.0.1 -t -q ghtorrent
ok 1 - commits_comments.rdbu: test_leadership.nl_commits_leader_comments

ok 2 - commits_comments.rdbu: test_leadership.leader_commits_nl_comments

ok 3 - commits_comments.rdbu: test_leadership.commits_with_comments

1..3

Development
Contributions via GitHub pull requests are welcomed.
Each contribution passes through continuous integration,
which verifies the code's style (pycodestyle) and checks for errors
(pylint).
It also tests the input and output of RDBunit and its operation on the
three supported relational database systems.
On a local host, after creating a virtual environment (pipenv),
entering it (pipenv shell), and
installing the required development dependencies (pipenv install --dev),
you can run the following commands.

``pycodestyle src/rdbunit/main.py`
pylint src/rdbunit/__main__.py
tests/test-parts.sh
tests/test-sqlite.sh