Problem

In-depth reporting and analysis of Cycle tests often requires the use of a database to log all of the details of your Cycle executions, but not all users have a readily available database to use for this.

Solution

Cycle comes pre-packaged with an H2 database. H2 is a serverless database that allows the Cycle user to create a local database file on the machine running cycle. This database can be used to store and maintain data for driving test execution as well as storing reporting data. This document describes an approach to use an H2 database to store easily maintained data and use it to drive test execution.  This approach can also be used with a MOCA database or any other database with a connection step in Cycle.

H2 Database Engine

The H2 database console can be downloaded using the link below. Downloading the H2 database to your local machine gives you the ability to use the H2 console to create, connect to, and maintain a local database without the need for a database server.

http://www.h2database.com/html/main.html

Cycle 2.4/2.5 contains H2 Driver version 1.4.197. Use the Latest Stable release, 1.4.199, on the H2 download page to avoid compatibility issues.

The example database and feature included a the end of this document show one potential approach for using variable test data stored in a database rather than hard-coding variable data values within a feature or using a csv file to store data.

Creating a Local H2 Database

After installing the H2 Database Engine downloaded from the link above, you will have the H2 Console available to use on your machine. This console opens a web based front end for the H2 DB that allows you to create and maintain your local database. The H2 database can also be accessed using any SQL query tool that allows JDBC connections.

Run the H2 Console application to open the H2 login page. You will see a login page similar to the one below:

The JDBC URL is the location of your database. In this example, I have created a directory under my Windows user directory called ~\Cycle\H2_Database_Project\local_database\ and I have named my database “sample_H2_db”.

I have added the additional parameter to the JDBC URL “;AUTO_SERVER=TRUE”. This option allows me to have the database open in my web browser and allow Cycle to access the DB at the same time. If AUTO_SERVER is not enabled, then you must ensure the DB session is disconnected for Cycle to be able to access the DB. Only one active connection is allowed at a time if this parameter is not used as part of the JDBC URL.

The full JDBC_URL for the sample project in this document is:

jdbc:h2:~\Cycle\H2_Database_Project\local_database\sample_H2_db;AUTO_SERVER=TRUE

I have specified a user name of “cycle” and a password of “cyclepwd” as part of my connection parameters.

Creating a new database is as simple as populating the JDBC URL, User Name, and Password fields with new data. The H2 console will attempt to connect to an existing database at the JDBC URL. If no database exists, then H2 console will create a new database using the parameters provided.

H2 Console

The H2 Console web browser based front end allows you to write SQL statements and execute them against your database.

In addition to writing queries to create tables, insert data, update data, delete data, etc…, the console also gives you the ability to edit records directly in the console with the use of the edit button under the results grid.

Example

If you have the "Write results to data store" setting turned on while running a test, 

Cycle will write the results of the execution into the database specified in the DATA STORE CONNECTION section of your reporting settings.

H2 Example

Note: If you are using an H2 database for Data Store reporting and also have an open session connected to it in a separate DB client, Cycle will not be able to simultaneously connect to it unless you include ;AUTO_SERVER=TRUE at the end of your Server URL as seen in the example.

Test Connection

This will confirm that the credentials supplied are in fact able to log in and create a session on the database.

Create Database Tables

This will attempt to create the tables in the specified Database for logging results. While this is not required it will help determine whether the user has the necessary permissions to write to the database.

The tables created will resemble the following:

**Note:** At minimum, the user configured in your connection settings should have `select`,`insert`, and `update` privileges to these tables.

JDBC Data store ERD

Sample Queries

To get the max test execution id for the most recent Cycle execution

SELECT
 MAX(execution_id)
FROM
 CYCLE_EXECUTION_RESULTS;

Use the result of that query and put in in place of <EXECUTION_ID>. This produces a summary report of that execution.

 SELECT
  f.name,
  f.status,
  f.file_uri,
  s.name,
  s.status,
  COUNT(CASE step.status WHEN 'Pass' THEN 1 WHEN 'True' THEN 1 ELSE NULL END) AS passed_steps,
  COUNT(CASE step.status WHEN 'Fail' THEN 1 WHEN 'False' THEN 1 ELSE NULL END) AS failed_steps
FROM
  cycle_execution_results AS f
JOIN cycle_execution_results AS s ON
  s.execution_id = f.execution_id
  AND s.node_type = 'Scenario'
  AND s.sending_node_id = f.node_id
LEFT JOIN cycle_execution_results AS step ON
  step.execution_id = s.execution_id
  AND step.node_type = 'Step'
WHERE
  f.execution_id = '<EXECUTION_ID>'
  AND f.node_type = 'Feature'
GROUP BY
  f.name,
  f.status,
  f.file_uri,
  s.name,
  s.status;

Sample Output