Getting started with DB Sanity

Installation

Download the distribution archive from Sourceforge and uncompress it to a local directory.
Set the environment variable DBSANITY_HOME to the uncompressed folder location.
Add DBSANITY_HOME/bin to your system's PATH (Win: %DBSANITY_HOME%\bin, *x: $DBSANITY_HOME/bin)
Get a JDBC driver for your specific database and put it into the lib folder of your DB Sanity installation.

Getting started

Creating a test project

First of all install DB Sanity as described above. then create a project directory for your tests, e.g. /myproject/dbsanity.

Defining tests

Create a sub folder called 'test', e.g. /myproject/dbsanity/test, for your test definitions.

Create one or more test definition files in the test folder, e.g. basic_tests.xml.

A test definition file contains an arbitrary number of sanity requirements. Two different forms of requirement specification are available:

Constraint: An abstract requirement of which DB Sanity figures out, how to verify a database's data.
Defect Query: A SQL query which finds database entries that are not valid.

More about this later. A simple example for verifying that all rows in table 'person' have a family_name value different from null:

<?xml version="1.0"?>
<dbsanity>

    <check name="Person must have family name" table="person">
        <notNull expression="family_name" />
    </check>

</dbsanity>

You can put several sanity check definition files into the test directory or group them in sub folders.

Defining a database

Look up the database connection properties needed to connect to your database by a JDBC driver and put them into an environment properties file. Choose a name by which you can easily re-identify the database (referred to as <environment> from now on) and save it in your project folder under the name <environment>.env.properties.

An example for a database 'XJ28':

File name: XJ28.env.properties

File Content:

db_url=jdbc:hsqldb:hsql://localhost/XJ28
db_driver=org.hsqldb.jdbcDriver
db_user=peter
db_password=secret

Performing the sanity check(s)

Open a console, go to your project folder (remember: It's the parent folder of your test directoy).

On Windows, type

    dbsanity.bat <environment>

On a Unix-style system, type

    dbsanity.sh <environment>

(replace <environment> with the identifier of the database defined above)

Reading the sanity report

DB Sanity will then connect to the database specified in the command line, perform the tests and create a results folder (e.g. /myproject/dbsanity/results) with an HTML report.

Go to the results directory and open the report's main page in a browser: index.html

Recommended further reading: File Syntax.

databene