QAShrink - Query-Aware Shrink Test Databases
QAShrink is an application for reducing the size of a database. It starts from
considers an initial database and the set of queries that are executed against it.
The database is reduced in order to preserve the SQLFpc coverage (Full Predicate Coverage or MCDC) of the data with respect to the queries.
This tool is described in the following papers:
Contents:
Download
Download here the latest version of QAShrink.
Next, unpack the ZIP file. The following filles will be present in your directory:
- qashrink-all.jar: Java archive for the application and its dependencies.
- qashrink.bat, qashrink.sh: Launcher command file.
After running it for the first time, the following files are generated:
- qashrink.settings.xml: Remembers the last used configuration .
- qashrink.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 qashrink.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;qashrink.jar in2test.application.qashrink.QAShrink
Alternatively, you may edit the qashrink.bat script file specifying this
command and then run the script.
The following window will be displayed:
Procedure to generate the reduced database:
- 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 queries that are to be used for the reduction (right side of the
window).
- At the bottom, click Do Shink for selecting the database
rows that will compose the reduced database. See Reducing the database
for more details.
- Next, you must prepare a blank database with the same schema that of the original
database. The database catalog/schema will be specified in the Destination Reduced Database field.
- Finally, click View Shrink Commands to view an script with the SQL commands that will copy the required information
from the original database to the reduced database, or click RUN Shrink Commands to perform this
copy. See Populating the destination reduced database for more information.
- Command DO ALL will perform both the reduction and copy to the destination reduced database.
- Bottom options provide further parameters to optimize the reduction process. See Optimizations for more information.
Reducing the database
When clicking the Do Shrink button, the reduction process begins. Because this process
may take some time, it is executed in a separate thread while showing progress information
to the user. Do not press any key until the process finishes.
Afther finishing, all the results of the process are presented at the lower part
of the screen.
- The size of the database is presented on the left side of the screen, with information about the original number of rows for each table, the number of rows that are loaded to satisfy the coverage, the total number of rows of the reduced database (which also includes the rows that are needed to ensure referential integrity) and the percent reduction (percentage of rows that are eliminated).
-
The rigth side of the screen shows information about each query: its ID (duplicate queries are removed), the cost (number of rows that have been added to the reduced database in order to cover this query), number of rows that have been read from the database, number of rules and number of covered rules, the percent coverage and the time spent in the analysis (seconds). Last columns show the SQL of each query and errors and warnings that may occour during the processing.
Populating the destination reduced database
After the Shrinking has been done, you must specify where the reduced database will be created by filling the Destination Reduced Database field. This field must include the full database name (catalog and schema). For instance, in Oracle it is the name of the database user, in SQL Server it is the name of the database, a dot, and the owner (which usually is dbo).
You have two different options:
- View Shrink Commands: It will show all SQL commands that can be issued to populate the reduced database with only the rows that have been selected after the Do Shrink process.
- RUN Shrink Commands: It will execute all SQL commands that populate the reduced database. Note that previously you must create a blank database with the same schema than the original.
Optimizations
- Parallel optimization: When set to a number N greater than 1, analysis and reduction of each coverage rule are performed in parallel
taking advantage from multicore processors.
Typically you will set N equals or slightly higher than the number of cores of the computers which run the application and the database.
- Limit number of rows in subgroups: When reducing queries that have many groups or subqueries, it limits the number of groups of rows
that are read form the original database. Typically you will set this parameter for large databases and complex queries.
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 SQL Syntax, database vendors and drivers
Currently DBShrink supports most of features of SQL (tested with Oracle and SQL Server),
including queries with joins, groups, subqueries and views.
<%Viewstate("AccessNotSupported")=true%>
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.
|
Version |
Date |
Changes |
|
2.0.64.109 |
2017.02.25 |
Java 6 to 8 compatibility |
|
2.0.100.0 |
2015 |
Version 2.0 |
|
2.0.75.0 |
2013 |
Version 2.0 beta. It supports views, groups, subqueries and reduction optimizations |
|
1.0.61.0 |
2009 |
Initial release |
]
