SQLRules Desktop V1 - 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.
NOTE: This version (V1) is deprecated. Please, use the
new Version 2.
Contents:
Download
Download here the latest version.
Next, unpack the ZIP file. The following filles will be present in your directory:
- sqlrulesv1-all.jar: Java archive for the application and its dependencies.
- sqlrulesv1.bat, sqlrulesv1.sh: Launcher command file.
After running it for the first time, the following files are generated:
- sqlrulesv1.settings.xml: Remembers the last used configuration .
- sqlrulesv1.error.xml: If 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 following window will be displayed:
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 the SQL query and database parameters (if any). See
Specifying query parameters.
- At tthe bottom of the screen, click Generate Scema for generating the database
schema of the tables that are used by the query. See XML database
schema syntax for more details.
- Select the kind of coverage rules to be generated.
- Finally click Generate Rules/Mutants and then Run Rules. See Generating and running rules for more information.
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:
- id, category, type, subtype, location:
for identifying each rule.
- sql: the sql statement for the rule.
- description (only for fpc): textual meaning of the rule.
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:
- In the case of Full Predicate Coverage, a rule si covered if its execution results
in a non empty resultset
- In the case of Mutants, the mutant is dead (rule is covered) if its execution results
in a different resultset that of the obtained after executing the original query
For each rule, the results of the execution are:
- count: Number of rules (for each rules this value is 1) .
- dead: 1 if rule is fulfilled (mutant is dead), 0 otherwise .
- 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.
- id, category, type, subtype, location, sql, description (as explained above).
- error: if the rule was executed with error contains the error message.
The total count of rules, dead rules or rules with error appears under the tag <summary>
in the Rules/Mutants section.
Other available options:
- Query Timeout (optional): specifies the maximum allowed time (in seconds)
for the execution of a rule/mutant.
- Reset Coverage: After running a set of rules, if some changes on the database contents or parameters are done and rules executed again, the results will show the
cumulative effect of all executions. This option resets all counters (it produces
the same effect than regenerate the rules).
- Edit Cell: Opens a new window with the contents of the selected cell, it
may be used to inspect de details of queries or error messages.
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.
XML database schema syntax
The schema content is definde as indicated in the
OpenApi specification
(see DbSchema, DbTable and DbColumn in the Schemas section):
- The JSON representation is obtained by a Jackson serializer (empty or null attributes are omitted):
An object representing the schema that contains an array of tables, each containing an array of columns.
Attributes of the tables and columns contain the information related to name, data type, etc.
- 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 may include three attributes, namely:
- dbms: Vendor's name of the database.
- catalog: Default catalog as specified in the connection info.
- schema: Default schema as specified in the connection info.
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:
- If neither catalog nor schema are specified:
- When loading all tables, all tables that are present in any schema are searched for.
If the user has visibility over more than one catalog/schema and there are tables
with the same name, an error occours because of a duplicated table name.
- Given a set of tables, when you generate the schema, a similar error may occour
if there are tables with same name that are visible. However, you may avoid this
error by qualifying the table names that are duplicated. For instance, if a table
T is present in two schemas S1 and S2, you will distinghish
the tables by their
qualified names S1.T and S2.T
- If catalog and/or schema are specified:
- When loading all tables, all tables that match the catalog/schema
specified are searched for. If some of dbcatalog or dbschema is not specified, it means any.
- Given a set of tables, when the schema is generated, if a table name is not qualified,
the table is searched for in the catalog/schema specified by default. If table name is qualified,
the table is searched for in the catalog/schema as indicated by table name qualifier.
For instance, if an schema S1 is specified by default, a table with name T will be searched in the schema S1, as same
as if S1.T is specified. However, S2.T is specified, table T
will be searched in the schema S2.
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
|
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 |
|
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 |
