DB steps
This plugin provides a set of steps to interact with a database via JDBC, making it easy to load and validate data.
NOTE
Due to the large number of database engines available, this plugin does not include specific drivers. This means that in order to work correctly, it is necessary to include the module with the appropriate JDBC driver in the Wakamiti configuration.
- Install
-
- Define connection
- Switch connection
- Execute script
- Execute script (file)
- Select data
- Select data (file)
- Insert data
- Insert data (file)
- Delete data
- Delete data (column)
- Delete data (where)
- Delete data (file)
- Clear table
- Check data existence
- Check data existence (id)
- Check data existence (column)
- Check data existence (where)
- Check data existence (file)
- Check data count
- Check data count (column)
- Check data count (where)
- Check table content
Install
Include the module and the necessary JDBC driver(s) in the corresponding section.
es.iti.wakamiti:db-wakamiti-plugin:3.1.0
es.iti.wakamiti:db-wakamiti-plugin:3.1.0
<dependency> <groupId>es.iti.wakamiti</groupId> <artifactId>db-wakamiti-plugin</artifactId> <version>3.1.0</version> </dependency>
<dependency> <groupId>es.iti.wakamiti</groupId> <artifactId>db-wakamiti-plugin</artifactId> <version>3.1.0</version> </dependency>
Options
database.connection.url
- Type:
string
required
Set the default JDBC connection URL to the database. The URL format will determine the driver used to access the database.
Example:
database: connection: url: jdbc:h2:tcp://localhost:9092/~/test
database.connection.username
- Type:
string
required
Set the default JDBC connection username.
Example:
database: connection: username: test
database.connection.password
- Type:
string
required
Set the default JDBC connection password.
Example:
database: connection: password: test
database.metadata.schema
- Type:
string
Set the default schema that should be used to retrieve metadata as primary keys and nullability. If not specified, the default schema returned by the connection will be used.
Example:
database: metadata: schema: TESTDB
database.metadata.catalog
- Type:
string
Set the default catalog that should be used to retrieve metadata as primary keys and nullability. If not specified, the default catalog returned by the connection will be used (in case the database system uses one).
Example:
database: metadata: catalog: TESTCAT
database.{alias}...
Set the JDBC connection parameters and/or metadata of a database identified by an alias. You can establish as many named connections as you want. The first database will be taken as the default configuration.
Example:
database: db1: connection: url: jdbc:h2:tcp://localhost:9092/~/test username: test1 password: test1 metadata: schema: TESTDB1 catalog: TESTCAT1 db2: connection: url: jdbc:mysql://other.host:3306/test username: test2 password: test2 metadata: schema: TESTDB2 catalog: TESTCAT2
database.csv.format
- Type:
string
- Default
DEFAULT
Set the format used to parse CSV files. The accepted values are directly imported from the Commons CSV project, check it for detailed explanation of each format. Possible values are:
DEFAULT
INFORMIX_UNLOAD
INFORMIX_UNLOAD_CSV
MYSQL
ORACLE
POSTGRESQL_CSV
POSTGRESQL_TEXT
RFC4180
Example:
database: csv: format: ORACLE
database.xls.ignoreSheetPattern
- Type:
regex
- Default
#.*
Set the regex pattern used to determine what sheets to be ignored when loading XLS files.
Example:
database: xls: ignoreSheetPattern: //.*
database.nullSymbol
- Type:
string
- Default
<null>
Set literal used to handle a specific cell value as the SQL NULL
element. It is used in any data source like CSV, XLS,
and in-line tables.
Example:
database: nullSymbol: (null)
database.enableCleanupUponCompletion
- Type:
boolean
- Default
false
The default behavior of the plugin does not perform any database cleanup operation after the tests are finished. This is to be able to check results manually and debug errors. Possible values are:
false
: no cleanup action will be performed.true
: the database will be forced to be cleaned by undoing the test data entered during the execution.
Example:
database: enableCleanupUponCompletion: "true"
Steps
Define connection
the database connection URL {url} using the user {username} and the password {password} (as {alias})
the database connection URL {url} using the user {username} and the password {password} (as {alias})
Configure the connection parameters to the database identified by the specified alias. If not alias is included, it will be set as the default connection.
This step is the declarative equivalent to set the configuration properties
database.connection.url
, database.connection.username
,
database.connection.password
or database.{alias}...
.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
url |
text required |
The URL connection |
username |
text required |
User name |
password |
text required |
User password |
alias |
text |
The connection alias |
Examples:
Given the database connection URL 'jdbc:h2:tcp://localhost:9092/~/test' using the user 'test' and the password 'test'
Given the database connection URL 'jdbc:mysql://other.host:3306/test' using the user 'test' and the password 'test' as 'db1'
Switch connection
(that) the (default|{alias}) connection is used
(that) the (default|{alias}) connection is used
Switch the active database connection to the one specified, or to the default.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
alias |
text |
The connection alias |
Examples:
When the default connection is used
When the 'db1' connection is used
Execute script
(that) the following SQL (script|procedure) is executed: {script}
(that) the following SQL (script|procedure) is executed: {script}
Execute the specified SQL statements or procedure and retrieve the inserted, updated or selected data as a JSON object.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
script |
document required |
Script content |
Examples:
When the following SQL script is executed:
"""sql
INSERT INTO users (id, first_name) VALUES (1, 'Rosa');
INSERT INTO users (id, first_name) VALUES (2, 'Pepe');
"""
It could return the following result:
[ { "id": 1, "first_name": "Rosa" }, { "id": 2, "first_name": "Pepe" } ]
Execute script (file)
(that) the SQL (script|procedure) file {script} is executed
(that) the SQL (script|procedure) file {script} is executed
Execute the specified SQL statements or procedure and retrieve the inserted, updated or selected data as a JSON object.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
script |
file required |
Script content |
Examples:
When the SQL script file 'data/script.sql' is executed
Select data
the following SQL query value(s) ((is|are) retrieved): {script}
the following SQL query value(s) ((is|are) retrieved): {script}
Retrieve data from the specified SQL SELECT as a JSON object.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
script |
document required |
Script content |
Examples:
Given the following SQL query values:
"""sql
SELECT id, first_name FROM users
"""
It could return the following result:
[ { "id": 1, "first_name": "Rosa" }, { "id": 2, "first_name": "Pepe" } ]
Select data (file)
the SQL query value(s) from the file {sql} ((is|are) retrieved)
the SQL query value(s) from the file {sql} ((is|are) retrieved)
Retrieve data from the specified SQL SELECT as a JSON object.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
sql |
file required |
Script content |
Examples:
Given the SQL query values from the file 'data/select.sql'
Insert data
(that) the following * (is|are) inserted into the (database) table {table}: {data}
(that) the following * (is|are) inserted into the (database) table {table}: {data}
Insert rows from the provided DataTable into the specified table and retrieve the inserted data as a JSON object.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
table |
word required |
The table name |
data |
table required |
The table data |
Examples:
When the following users are inserted into the table USER: | USER | STATE | BLOCKING_DATE | | user1 | 2 | <null> | | user2 | 3 | 2020-02-13 |
It could return the following result:
[ { "USER": "user1", "STATE": "2", "BLOCKING_DATE": null }, { "USER": "user2", "STATE": "3", "BLOCKING_DATE": "2020-02-13" } ]
Insert data (file)
(that) the content of the XLS file {file} (is) inserted into the database
(that) the content of the XLS file {file} (is) inserted into the database
(that) the content of the CSV file {file} (is) inserted into the (database) table {table}
(that) the content of the CSV file {file} (is) inserted into the (database) table {table}
Insert rows of the provided XLS or CSV file into the database and retrieve the inserted data as a JSON object. If it is an XLS file, each sheet will represent a table and should be named as such. If it is a CSV file, the name of the table where the data shall be inserted shall be indicated.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
file |
file required |
The file name |
table |
word |
The table name |
Examples:
When the content of the XLS file 'data/users.xls' is inserted into the database
When the content of the CSV file 'data/users.csv' is inserted into the table USER
Delete data
(that) the following * (is|are) deleted from the (database) table {table}: {data}
(that) the following * (is|are) deleted from the (database) table {table}: {data}
Delete the rows in the given table that match the specified values.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
table |
word required |
The table name |
data |
table required |
The table data |
Examples:
When the following users are deleted from the table USER: | USER | STATE | BLOCKING_DATE | | user1 | 2 | <null> | | user2 | 3 | 2020-02-13 |
Delete data (column)
* having {column} = {value} (is|are) deleted from the (database) table {table}
* having {column} = {value} (is|are) deleted from the (database) table {table}
Delete the rows in the given table that match the specified value.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
column |
word required |
Column name |
value |
text required |
Column value |
table |
word required |
The table name |
Examples:
When users having STATE = '2' are deleted from the table USER
Delete data (where)
* satisfying the following SQL clause (is|are) deleted from the (database) table {table}: {where}
* satisfying the following SQL clause (is|are) deleted from the (database) table {table}: {where}
Deletes rows from the specified database table based on the provided SQL WHERE clause.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
table |
word required |
The table name |
where |
document required |
The where clause |
Examples:
When the user satisfying the following SQL clause is deleted from the table client:
"""
birth_date < date '2000-01-01'
"""
Delete data (file)
(that) the content of the XLS file {file} is deleted from the database
(that) the content of the XLS file {file} is deleted from the database
(that) the content of the CSV file {file} is deleted from the database table {table}
(that) the content of the CSV file {file} is deleted from the database table {table}
Delete rows of the provided XLS or CSV file into the database. If it is an XLS file, each sheet will represent a table and should be named as such. If it is a CSV file, the name of the table where the data shall be deleted shall be indicated.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
file |
file required |
The file name |
table |
word |
The table name |
Examples:
When the content of the XLS file 'data/users.xls' is deleted from the database
When the content of the CSV file 'data/users.csv' is deleted from the database table USER
Clear table
(that) the (database) table {word} is cleared
(that) the (database) table {word} is cleared
Delete all rows from the given database table.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
word |
word required |
The table name |
Examples:
When the table USER is cleared
Check data existence
(that) the following record(s) (not) exist(s) in the (database) table {table}: {data}
(that) the following record(s) (not) exist(s) in the (database) table {table}: {data}
Assert that the data provided in the DataTable exists or not in the given table.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
table |
word required |
The table name |
data |
table required |
Data table |
Examples:
Then the following user exists in the table USER: | USER | STATE | BLOCKING_DATE | | user2 | 3 | 2020-02-13 |
Then the following users not exist in the table USER: | USER | STATE | BLOCKING_DATE | | user1 | 2 | <null> | | user2 | 3 | 2020-02-13 |
Check data existence (id)
* identified by {id} (not) exist(s) in the (database) table {table}
* identified by {id} (not) exist(s) in the (database) table {table}
Assert that a row in the given table has or not a primary key matching the specified value. The table must have a single-column primary key accessible from the database metadata.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
id |
text required |
Primary key value |
table |
word required |
The table name |
Examples:
Then a user identified by 'user1' exists in the table USER
Then a user identified by 'user1' not exist in the table USER
Check data existence (column)
* having {column} = {value} (not) exist(s) in the (database) table {table}
* having {column} = {value} (not) exist(s) in the (database) table {table}
Assert that rows with the specified value in the given column exist or not in the given table.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
column |
word required |
Column name |
value |
text required |
Column value |
table |
word required |
The table name |
Examples:
Then a user having STATE = '1' exists in the table USER
Then users having STATE = '1' not exist in the table USER
Check data existence (where)
* satisfying the following SQL clause (not) exist(s) in the (database) table {table}: {where}
* satisfying the following SQL clause (not) exist(s) in the (database) table {table}: {where}
Assert that rows with the specified values in the given clause exist or not in the table.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
table |
word required |
The table name |
where |
document required |
Where clause |
Examples:
Then a user satisfying the following SQL clause exists in the database table USER:
"""
STATE IN (2,3) OR BLOCKING_DATE IS NULL
"""
Then users satisfying the following SQL clause not exist in the database table USER:
"""
STATE IN (2,3) OR BLOCKING_DATE IS NULL
"""
Check data existence (file)
(that) the content of the XLS file {file} (not) exist(s) in the database
(that) the content of the XLS file {file} (not) exist(s) in the database
(that) the content of the CSV file {file} (not) exist(s) in the (database) table {table}
(that) the content of the CSV file {file} (not) exist(s) in the (database) table {table}
Assert that rows of the provided XLS or CSV file exist or not in the database. If it is an XLS file, each sheet will represent a table and should be named as such. If it is a CSV file, the name of the table where the data shall be matched shall be indicated.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
file |
file required |
The file name |
table |
word |
The table name |
Examples:
Then the content of the XLS file 'data/users.xls' exists in the database
Then the content of the CSV file 'data/users.csv' not exist in the table USERS
Check data count
(that) the number of * satisfying the following (info) in the (database) table {table} {matcher}: {data}
(that) the number of * satisfying the following (info) in the (database) table {table} {matcher}: {data}
Asserts that the number of rows in the given table matching the specified values for every column satisfies a numeric expression.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
table |
word required |
The table name |
matcher |
long-assertion required |
Numeric comparator |
data |
table required |
Data table |
Examples:
Then the number of users satisfying the following in the table USER is 0: | USER | STATE | BLOCKING_DATE | | user1 | 2 | <null> |
Then the number of records satisfying the following info in the table USER is more than 0: | USER | STATE | BLOCKING_DATE | | user1 | 2 | <null> |
Check data count (column)
(that) the number of * having {column} = {value} in the (database) table {table} {matcher}
(that) the number of * having {column} = {value} in the (database) table {table} {matcher}
Assert that the number of rows satisfying the condition meets the given numerical comparison.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
column |
word required |
Column name |
value |
text required |
Column value |
table |
word required |
The table name |
matcher |
long-assertion required |
Numeric comparator |
Examples:
Given that the number of users having STATE = '1' in the database table USER is greater than 5
Check data count (where)
* satisfying the following SQL clause exist(s) in the (database) table {table}: {where}
* satisfying the following SQL clause exist(s) in the (database) table {table}: {where}
Asserts that the number of rows in the given table matching the SQL clause satisfies a numeric assertion.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
table |
word required |
The table name |
matcher |
long-assertion required |
Numeric comparator |
where |
document required |
Where clause |
Examples:
Then the number of users satisfying the following SQL clause in the table USER is less than 10:
"""
STATE IN (2,3) OR BLOCKING_DATE IS NULL
"""
Check table content
(that) the (database) table {table} is (not) empty
(that) the (database) table {table} is (not) empty
Assert that the given table is empty or not.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
table |
word required |
The table name |
Examples:
Then the table USER is empty
Then the table USER is not empty
Special modes
Some steps may be executed with a different behavior if they are defined in the following ways:
Post-execution mode
On completion, * (using {alias} connection)
On completion, * (using {alias} connection)
The step shall be executed once the scenario has finished, regardless of the outcome of the execution. If the alias is not included, the default connection will be used.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
alias |
text |
The connection alias |
Examples:
* On completion, the SQL script file 'data/insert-users.sql' is executed
* On completion, the following SQL script is executed using 'db1' connection: """ UPDATE AAAA SET STATE = 2 WHERE ID = 1; DELETE FROM BBBB WHERE ID = 2; """
Async mode
* in {time} seconds
* in {time} seconds
The step waits for a maximum of the indicated seconds until the condition indicated in the step is fulfilled.
Parameters:
Name | Wakamiti type | Description |
---|---|---|
time |
int required |
The timeout |
Examples:
Then a user identified by '1' exists in the table USERS in 10 seconds