SQLRules - Coverage Evaluation for SQL Database Queries

SQLRules is a standalone application for evaluating the coverage of SQL database queries. Coverage criteria are implemented in a set of rules, that when evaluated with respect to a given database determine the coverage of the database with respect to the query. Two kind of coverage rules are generated:

NOTE: Version 2 is now available, including support for parallel execution of rules generted from multiple queries and multiple sets of parameters for each query. The Version 1 is deprecated, although still available here.

Contents:

Download

Download here the latest version. After, unpack the ZIP file. The following filles will be present in your directory:
After running it for the first time, the following files are generated:

First at all, you must be sure that you have your database running and the adequate jdbc database driver is in your default directory. Here you can find the drivers for some databases:

Quick Start

Launch the main class of sqlrules.jar using the following java command (where drivername.jar is the name of the vendor's database driver file. Note that you don't need specify a driver name if your are going to use the jdbc-odbc bridge driver):

java -classpath drivername.jar;sqlrules.jar in2test.application.sqlrules.SQLRules

Alternatively, you may edit the sqlrules.bat script file specifying this command and then run the script.

The screen below depicts and example of the results of the evaluation of the SQLFpc coverage rules for the 22 queries of the TPC-H benchmark, each having 150 sets of parameters. In total the 264 rules have been executed 11,287 times with different parameters in 99.2 seconds to achieve 71,21% coverage (thoughput is 113 rule evaluations per second running in a i5-3320M laptop with a local Oracle database)

SQLRules main window

Procedure

Specifying query parameters

Parameter names in a query may be specified using one of the following syntax :name or ?nn?. The first one is the oracle-style syntax for named parameters and the second one is the syntax used by SQLMutation and SQLFpc. The set of parameters for a query shall be placed just before the query in the form of name=value pairs separated by commas.  The following is an example of two parameters :1 and :2 with actual values 'BUILDING' and '1995-03-31' respectively (note that character and date values must include single quotes as same as in sql statements):

    SET :1='BUILDING', :2='1995-03-31' 
    SELECT .....

Multiple sets of parameters may also be specified, for example:

    SET :1='BUILDING', :2='1995-03-31' 
    SET :1='MACHINERY', :2='1995-03-29'
    SET :1='MACHINERY', :2='1995-03-27'
    SELECT .....

When multiple sets of parameters are specified, each rule is evaluated by applying the first set. If not covered then the second set is applied and so on, until the rule is covered or there are no more sets. The above screen represents the results of evaluation of 22 queries, each having 150 sets of parameters.

Evaluating rules

In the following the term rule is used to represent a SQLFpc coverage rule or a SQL Mutant; the term covered is used to indicate that a SQLFpc coverage rule is covered or a SQL Mutant is dead.

When the user selects Generate and Run Rules/Mutants, SQLRules parses the queries, remove duplicates and generates the rules for each resulting query. Execution of all rules is done in parallel if the value of Threads parameters is set to a value higher than 1 (typically this value will be set between 1 or 2 the number of cores of the processor to maximize thorughput). Additional a value for Query Timeout may be specified in seconds (evaluation of rules will finnish with error when taking more than the specified time).

Queries Summary

SQLRules shows the progress of the evaluation at the Queries Summary table and the status bar (it is not recommendable to perform any other action until finish). The information displayed for each query is:

For example, the above screen displays 15 rules for query 12, with 7 of them covered, leading to  46.67% coverage. Last row displays the total considering all rules (264 rules for all queries, with 188 of them covered, leading to 71.21% coverage).

Rules for Selected Query

When the user selects the row of a query, the table Rules for Selected Query displays the evaluation details for each of its rules. The information displayed for the selected rule is:

Once that the rules have been evaluated the results are completed with the evaluation results:

For example, the above screen displays the details for query 12. Rule 5 is dead, but its deadt value is 4 that means it was covered with the 4th set of parameters. Rule 4 is not dead and its countt value is 150 that means it has been executed with all parameters, but never covered.

Other available options

Integration with external programs

Download the source code of sqlrules.java.eclipse.wtp.zip. It contains a single java source file with the calls for obtaining the database schema, generating the rules and executing the rules.

You need to include the following jars in your classpath in order to succesfully complile and execute:

Specifying connection info

The information required to access the database is explained below:

Supported database vendors and drivers

Latest builds of these applications has been developed and tested in Eclipse 4.3.2 using Java JDK 1.8 and JavaCC 4.2 under Windows 10, but may run on lower JRE versions.

As it uses standard jdbc methods to discover the database schema, it will work with virtually any database vendor, provided that you have a jdbc driver. As of Java JDK 1.8 the jdbc-odbc bridge is not supported, you should run a lower version of the JRE in order to use it.

This is the list of configurations that have been used for testing latest builds:

Database Product NameVersionDatabase Driver NameVersion
Oracle 11g Enterprise 11.2.0.1.0 Oracle JDBC Driver 11.2.0.1.0
Microsoft SQL Server 2008 10.00.5500 Microsoft SQL Server 2005 JDBC Driver 1.2.2828.100
MS Access 2003 11.8166.8202 HXTT Access JDBC Driver 3.0

Some rules and mutants may fail when running under MS Access because of some non standard syntax features of MS Access SQL

 

Release notes

Version Date Changes
2.0.66.132 2017.03.11 Changed library to generate Visual Comparison
2.0.54.109 2017.02.25 Java 6 to 8 compatibility
2.0.81.0 2015.04.20 Generate and run rules for multiple queries
Parallel execution of all rules
Multiple sets of parameters for each query (only SQLFpc rules) 
1.0.74.1 2012.11.25 spr.25312: Query without order by is invalidated when running mutants for SQLServer
1.0.69.2 2011.08.29 spr.25057: Oracle timestamp column name returned as TIMESTAMP(6)
1.0.65 2010.05.03 Added query timeout
Connection recovery from ORA-12152 Oracle errors
1.0.63 2009.08.05 Added visual comparison of queries
1.0.57 2008.07.31 Initial release