SQLRules Desktop - Coverage Evaluation for SQL Database Queries

SQLRules Desktop is a standalone application for evaluating the coverage of SQL database queries (see SQLRules). 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:

SQLFpc - Full Predicate Coverage rules for SQL: Based on Modified Condition Decision Coverage (MCDC) for SQL. Each rule is an SQL statement that is covered if the execution against the database returns at least one row.
See article Full predicate coverage for testing SQL database queries. Software Testing, Verification and Reliability, 20 (3) 237-288, September 2010.
SQLMutation - Mutants for SQL: It is a mutation system for database queries. Each rule is a mutant of the original SQL query. The mutant is dead (i.e. the rule is covered) if the execution against the database returns the same rows than the original query.
See article Mutating Database Queries, Information and Software Technology, 49(4) 398-417, 2007.

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:

Terms of Use
Download
Quick Start
Specifying connection info
Specifying query parameters
Evaluating rules
Integration with external programs
Database vendors and drivers supported
Release notes

Download

Download here the latest version. Next, unpack the ZIP file. The following filles will be present in your directory:

sqlrules-all.jar: Java archive for the application and its dependencies.
sqlrules.bat, sqlrules.sh: Launcher command file.

After running it for the first time, the following files are generated:

sqlrules.settings.xml: Remembers the last used configuration.
sqlrules.error.xml: Should an error occours, some debug data is included in this file. If you notify a software problem report you should include this file in order to facilitate the detection of the problem.

First at all, you must be sure that you have your database running and the appropriate jdbc database driver is in the classpath (or in the folder where the application has been unzipped). Below are some direct links to download the drivers for some databases (maven):

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

Fill in the information that is needed to connect the database using jdbc (Database connection info at the left of the window). See Specifying connection info for more information.
Specify one or more SQL queries. Before each query you may specify the values of one or more sets of parameters to be passed to the query. See Specifying query parameters.
Select the kind of coverage to be evaluated (SQLFpc rules or SQL Mutants). Note: multiple sets of parameters are only supported by SQLFpc Rules).
Finally click Generate and run Rules/Mutants. See Evaluating rules for more information.

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:

id: The unique id of the query.
sql: The SQL expression of the query. If query was a CREATE VIEW statement, it is translated there into the corresponding SELECT query.
error: Should a problem occours while the generation of rules.
count: Number of rules generated for the query.
countt: The sum of all count values for all rules (see below at Rules for Selected Query).
dead: Number of rules that have been covered.
deadt: The sum of all deadt values for all rules (see below at Rules for Selected Query).
errors: Number of errors while evaluating the rules.
%cov: The percent coverage for the query.
errlist: Should a problem occurs while evaluating any rule, the list of all error messages appear here.

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:

id, category, type, subtype, location: for identifying each rule.
sql: the SQL statement for the rule.
description (only for SQLFpc rules): textual meaning of the rule which indicates what is the situation that the rule represents.

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

count: Number of rules (for each single rule this value is 1).
countt: When the query has multiple sets of parameters, it represents the number of times that the rule has been evaluated with different parameters. If the rule is not covered, this value equals the number of sets of parameters.
dead: 1 if rule is covered (mutant is dead), 0 otherwise.
deadt: When the query has multiple sets of parameters, it represents the number of times that the rule has been evaluated with different parameters until covered. If the rule is not covered, this value is 0.
errorcount: 1 if the rule was executed with error, 0 otherwise. A rule that has been executed with error is considered dead.
qtime: Execution time in milliseconds.
error: if the rule was executed with error, it contains the error message.

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

SQLFpc rules allow these options

lang=[en|es]: Specifies the output language for the textul description (lang=es or lang=en)
numberjdbcparam: Convert each jdbc parameter (?) into ?n? where n is the position in the sql. Needed if paramerers are later substituted to run the generated rules
noconditions: If present, rules for conditions (select operator) are not generated
noboolean: If present, rules for boolean conditions (select operator) are not generated
nonulls: If present, rules for nulls in conditions (select operator) are not generated
noboundaries: If present, rules for boundary values in numeric expressions are not generated
nojoins: If present, rules for joins are not generated
nosubqueries: If present, rules for subqueries are not generated
nogroupby: If present, rules for group by clauses are not generated
noaggregate: If present, rules for aggregate funcions are not generated
notautology: If present, a number of rules for WHERE and CASE conditions that are unsolvable due to coupled conditions are filtered out

SQLMutation mutants allow these options

numberjdbcparam: Convert each jdbc parameter (?) into ?n? where n is the position in the sql. Needed if paramerers are later substituted to run the generated rules
noequivalent: If present, equivalent mutants are not generated

Integration with external programs

See the SQLRules REST API documentation for details

Specifying connection info

The information required to access the database is explained below:

Database connection info required for getConnection(): You must specify the driver class name and connection url along with the access credentials (if required). The driver class name and connection url is database vendor specific. Some examples of connections using the default ports are:

Database Vendor	Class Driver Name	Connection url
Oracle (OCI Driver)	oracle.jdbc.driver.OracleDriver	jdbc:oracle:oci:@<SERVER>:1521:ORCL
SQL Server	com.microsoft.sqlserver.jdbc.SQLServerDriver	jdbc:sqlserver://<SERVER>:1433;database=<DATABASENAME>
PostgreSQL	org.postgresql.Driver	jdbc:postgresql://<SERVER>:5432

The option Connections in the menubar facitlitates the configuration for some typical connections.

Database connection info required for getMetadata(): In order to obtain the database schema, the Java getMetadata methods are used, which require additional information about the database catalog and database schema where to find the information about the database tables and views. This information is not allways required, but in many ocasions is needed, depending on the database vendor and the access privileges of the user who has access to the database. Below are some common scenarios:
- SQLServer: The catalog is the database name. and usually the schema is dbo. So as, in a common scenario you will specify the database name as catalog name and dbo as schema name. However, in many cases if you don't specify any information for schema or catalog you attain the same effect, provided that the user has only access to his database and dbo schema.
- Oracle: The database username is asssociated with the schema and with a null catalog. So as, usually, you will not specify any catalog, but if you don't sypecify any schema, and click Load All Tables in Schema you will find all tables and views that this user has access to, possibly including system tables and tables from other schemas (if user has DBA privileges). Therefore, is highly recommended to specify the schema.
- PostgreSQL: The schema is usually public and with a null catalog.

Supported database vendors and drivers

Latest builds of these applications has been tested with Java 1.8 on Windows and linux

As it uses standard jdbc methods to discover the database schema, it will work with virtually any database vendor, provided that you have the appropriate jdbc driver.

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
3.0.2	2022.05	Uses the new version of the sqlrules services api (V3)
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

]

Test4Data - Testing for Database Applications