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:
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:
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):
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)
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.
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).
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).
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.
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 rulesnoconditions
: If present, rules for conditions (select operator) are not generatednoboolean
: If present, rules for boolean conditions (select operator) are not generatednonulls
: If present, rules for nulls in conditions (select operator) are not generatednoboundaries
: If present, rules for boundary values in numeric expressions are not generatednojoins
: If present, rules for joins are not generatednosubqueries
: If present, rules for subqueries are not generatednogroupby
: If present, rules for group by clauses are not generatednoaggregate
: If present, rules for aggregate funcions are not generatednotautology
: If present, a number of rules for WHERE and CASE conditions that are unsolvable due to coupled conditions are filtered outSQLMutation 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 rulesnoequivalent
: If present, equivalent mutants are not generatedSee the SQLRules REST API documentation for details
The information required to access the database is explained below:
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 |
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
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 |