SQL DSL

Architecture

With the SQL DSL the user can inject a proxy client into the main test code with just a few annotations, and use a set of predefined actions on this proxy client that are optimised for the need of testing. Those actions make the client capable of running queries and updates on a relational database, and asserting expected results from the returned responses.

To have the SQL proxy client injected in the test the only required step is to include an argument of type SqlApplication in the list of arguments of the test and to annotate it with the minimum required annotations. The respective object initialization happens automatically and allows it to be readily used during the test.

Currently, we support two SQL dialects, which you should specify using the pn5:sqlDialect capability:

• sqlserver (which connects to MSSQL database)
• postgresql (which connects to PostgreSQL database)

Example:

class TestClass {
    @Test
    public void sampleTest(
            @Capability(key = BROWSER_NAME, value = "pn5-sql")
            @Capability(key = CAPABILITY_SQL_DIALECT, value = "sqlserver")
            @Capability(key = CAPABILITY_SQL_HOSTNAME, value = "mssql.host", type = ValueType.STRING_PROPERTY)
            @Capability(key = CAPABILITY_SQL_PORT, value = "mssql.port", type = ValueType.INTEGER_PROPERTY)
            @Capability(key = CAPABILITY_SQL_USERNAME, value = "mssql.user", type = ValueType.STRING_PROPERTY)
            @Capability(key = CAPABILITY_SQL_PASSWORD, value = "mssql.password", type = ValueType.STRING_PROPERTY)
                    SqlApplication sqlClient) {

        sqlClient
                .prepareUpdate("DROP DATABASE IF EXISTS test_database")
                .execute()
                .andThen()
                .prepareUpdate("CREATE DATABASE test_database")
                .execute();
    }
}

On the background Pumpo#5 starts a session on the testing farm and transfers specified capabilities to the proxy client. Those are mainly related to the credentials required to connect to the relational database.

The capabilities that can be passed to the SQL proxy client are documented in the section related to configuring connection to systems under test

Wrapping in custom objects

Instead of using the predefined methods in the SqlApplication interface, it is possible to define a custom interface that extends SqlApplication and to wrap the predefined actions into more business oriented methods.

Example:

class TestClass {
    @Test
    public void sampleTest(MyCustomSqlApplication sql) {
            sql
                .dropDatabaseIfExists("test_database");
    }
}

@Capability(key = BROWSER_NAME, value = "pn5-sql")
@Capability(key = CAPABILITY_SQL_DIALECT, value = "sqlserver")
@Capability(key = CAPABILITY_SQL_HOSTNAME, value = "mssql.host", type = ValueType.STRING_PROPERTY)
@Capability(key = CAPABILITY_SQL_PORT, value = "mssql.port", type = ValueType.INTEGER_PROPERTY)
@Capability(key = CAPABILITY_SQL_USERNAME, value = "mssql.user", type = ValueType.STRING_PROPERTY)
@Capability(key = CAPABILITY_SQL_PASSWORD, value = "mssql.password", type = ValueType.STRING_PROPERTY)
public interface MyCustomSqlApplication extends SqlApplication {
    default UpdateResponse dropDatabaseIfExists(String databaseName) {
        return sqlClient
                .prepareUpdate("DROP DATABASE IF EXISTS " + databaseName)
                .execute();
    }
}

This pattern is common for all domains handled by Pumpo#5 and is the recommended coding style to keep tests readable also for people not having deeper knowledge of implementation details.

Method reference

SqlApplication::prepareQuery

Parameter	Type	Description
query	String	The SQL query to be prepared for execution on the relational database.

Prepares a SQL query or query template to be executed on the database you are connected to, via capabilities, and returns a SelectQueryBuilder object. You can then execute the query by calling .execute() on this object.

It must be a query that returns a result set that is 0 or more rows.

SqlApplication::prepareUpdate

Parameter	Type	Description
query	String	The SQL update request to be prepared for execution on the relational database.

Prepares a SQL update request or update request template to be executed on the database you are connected to, via capabilities, and returns a UpdateQueryBuilder object. You can then execute the request by calling .execute() on this object.

It must be a query of type insert, update or delete that returns only the number of affected rows

SelectQueryBuilder members

SelectQueryBuilder::withJdbcParams and UpdateQueryBuilder::withJdbcParams

Parameter	Type	Description
params	Object[]	An array of parameters to be substituted in a JDBC parameterized query

In case you want to use a parameterized query you can pass the parameters using this method.

For example, you have a table "emp" with a list of employees, and want to add a new row with an employee named John Doe, who is a tester and with seniority level junior.

You can do so by executing:

    sqlApplication
        .prepareQuery("SELECT * FROM emp WHERE emp.name = '?' AND emp.position = '?'")
        .withJdbcParams("John Doe", "Tester")
        .execute();

The "?" marks in the query template will be replaced, one by one, by the values passed in withJdbcParams method, in the order they are passed. The final query executed on \ the database looks like

SELECT * FROM emp WHERE emp.name = 'John Doe' AND emp.position = 'Tester'

If there are more parameters passed than question marks, the last parameters passed will be ignored. If there are fewer parameters passed than question marks, the last question marks will remain in the final query. Depending on what you want to achieve, this could lead to an error or unpredictable behavior.

SelectQueryBuilder::withParam and UpdateQueryBuilder::withParam

Parameter	Type	Description
placeholder	String	The placeholder you want to pass a value, used in the query
value	Object	The value to be substituted in for the placeholder

In case you used a placeholder in the query, you can pass a value using this the method, and the placeholder will be replaced by it. The placeholder is a string that must be surrounded by curly braces (open with { and close with }).

For example:

    sqlApplication
        .prepareQuery("SELECT * FROM #{tableName} WHERE emp.name = 'John Doe' AND emp.position = 'Tester'")
        .withParam("tableName", "emp")
        .execute();

The placeholder tableName will be replaced by the value emp. The final query executed on the database will look like:

SELECT * FROM emp WHERE emp.name = 'John Doe' AND emp.position = 'Tester'

You can use as many placeholders as you want in the query. Just make sure that all of them have valid values in the final query to be sent to the database.

SelectQueryBuilder::execute and UpdateQueryBuilder::execute

Execute the prepared query/update operation on the relational database.

SelectResponse::rowsAsListOfJsonNodes

Converts the response returned from the select operation to a List of JsonNode objects, which should be easier to run further operations and assertions on.

SelectResponse::assertThatRowCountIs

Asserts that the row count returned from the select operation is equal to an integer passed as parameter.

SelectResponse::rowsAsListOf

Returns the rows transformed to a List of PoJo objects. The rows will be converted to a PoJo class passed as parameter. Columns not present in the PoJo class will be ignored and missing columns from the PoJo members will not cause an exception.

SelectResponse::rowCount

Returns the number of rows returned from the database.

UpdateResponse::assertThatRowCountIs

Asserts that the number of affected rows returned from the update operation is equal to an integer passed as parameter.

UpdateResponse::rowsAffected

Returns the number of rows affected by the update operation.

Any SqlResponse::andThen

Returns the initial SqlApplication to be able to continue the flow. Typically, after a set of assertions this method can be used to re-query the SQL database for something else to run additional assertions.