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: This version (V1) is deprecated. Please, use the new Version 2 new Version 2.

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 following window will be displayed:

SQLRules main window

Procedure:

Specifying query parameters

Parameters 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 actual values that are to be used when running the rules are specifyed in the Parameters section. The following is an example of two parameters ?1? and ?2? with actual values 3 and 'X' respectively:

    <parameters>
    <parameter name="?1?" value="3" />
    <parameter name="?2?" value="'X'" />
    ...
    </parameters>

Note that character values must include single quotes as same as in sql statements.

Generating and running rules

The first required step is to generate the schema by clicking the Generate Schema button, which generates the database schema for the tables and views involved in the query.

After selecting the kind of rules to be generated (Full Predicate Coverage Rules or Mutants) you will click the Generate Rules button. The web service that generates the rules is invoked and then the rules appear in the Rules/Mutants section (xml format at the top and tabular format at the bottom). If some problem occours in the generation the <error> tag including the error message appears in the xml format.

For each rule, the information presented is:

Once that the rules have been correctly generated, the button Run Rules executes each one using the loaded data in the database and determines if it is covered or not:

For each rule, the results of the execution are:

The total count of rules, dead rules or rules with error appears under the tag <summary> in the Rules/Mutants section.

Other available options:

Generating and running rules from 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:

XML database schema syntax

The XML representation of the database schema consists on a root tag <schema> which contains a series of <table> tags. Each of them contains a <column> tag for each of the columns in this table. Attributes of the tags contain the information related to name, data type, etc.

The <schema> tag may include three attributes, namely:

Both catalog and schema attributes are optional. However, they are relevent in order to determine what tables are to be found. Recall that according to the SQL Standard, a table specification may be qualified in the form [[catalog.]schema.]name. If a table name is not qualified (using catalog and/or schema), a default catalog and/or schema is assumed. In that case the deafaults are the indicated by dbcatalog and/or dbschema:

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
1.0.54.109 2017.02.25 Java 6 to 8 compatibility
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