Package com.privalia.qa.specs

Class SqlDatabaseGSpec

  • public class SqlDatabaseGSpec
extends BaseGSpec
    Steps definitions for working with relational databases (postgresql and mysql)
    Author:
    Jose Fernandez

    • Constructor Detail

      • SqlDatabaseGSpec

        public SqlDatabaseGSpec​(CommonG spec)

    • Method Detail

      • connectDatabase

        @Given("^I( securely)? connect with JDBC to database \'(.+?)\' type \'(mysql|postgresql|clickhouse)\' on host \'(.+?)\' and port \'(.+?)\' with user \'(.+?)\'( and password \'(.+?)\')?$")
public void connectDatabase​(String isSecured,
                            String database,
                            String dataBaseType,
                            String host,
                            String port,
                            String user,
                            String password)
        Attempts to establish a connection with the given database.

        The DriverManager attempts to select an appropriate driver from the set of registered JDBC drivers. All subsequent steps that interact with the database will be performed on this connection. You can also use the step disconnectDatabase() to close this connection at the end of your scenarios. The current supported types of databases are postgresql/mysql 

        
 Example:

 Scenario: Connecting to a mysql database with user/password
      Given I connect with JDBC to database 'databaseName' type 'mysql' on host '121.0.0.1' and port '3306' with user 'root' and password 'P@$$W0RD'

 Scenario: If the database does not have a password
      Given I connect with JDBC to database 'databaseName' type 'mysql' on host '121.0.0.1' and port '3306' with user 'root'

 Scenario: Connecting to a postgresql database
      Given I connect with JDBC to database 'databaseName' type 'postgresql' on host '121.0.0.1' and port '5432' with user 'postgres' and password 'P@$$W0RD'

 Scenario: Connecting to a clickhouse database
      Given I connect with JDBC to database 'databaseName' type 'clickhouse' on host '121.0.0.1' and port '8123' with user 'clickhouse' and password 'P@$$W0RD'
        Parameters:
        isSecured - True if secure connection
        database - Name of the remote database
        dataBaseType - Database type (currently MYSQL/POSTGRESQL)
        host - URL of remote host
        port - Database port
        user - Database user
        password - Database password
        See Also:
        disconnectDatabase()

      • executeQuery

        @When("^I execute query \'(.+?)\'$")
public void executeQuery​(String query)
        Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement

        The given SQL statement must be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement. To execute an statement that does return a ResultSet (rows), such as a SELECT statement, use the step executeSelectQuery(String) 

        
 Example:

 Scenario: Create a table (CREATE statement returns nothing, as well as TRUNCATE, INSERT)
      Given I connect with JDBC to database 'databaseName' type 'mysql' on host '121.0.0.1' and port '3306' with user 'root' and password 'P@$$W0RD'
      And I execute query 'CREATE TABLE IF NOT EXISTS weather1 (city varchar(80), temp_lo int, temp_hi int, prcp real, date date);'
      And I execute query 'TRUNCATE weather1'
      When I execute query 'INSERT INTO weather1 (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 15, 43, 0.0, '2004-11-29');'
      Then I close database connection
        Parameters:
        query - An SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
        See Also:
        executeSelectQuery(String), connectDatabase(String, String, String, String, String, String, String), disconnectDatabase()

      • verifyTableExists

        @Then("^table \'(.+?)\' exists$")
public void verifyTableExists​(String tableName)
        Verify if a table exists 
        
 Example:

 Scenario: Create a table and then verify it was created
      Given I connect with JDBC to database 'databaseName' type 'mysql' on host '121.0.0.1' and port '3306' with user 'root' and password 'P@$$W0RD'
      When I execute query 'CREATE TABLE IF NOT EXISTS weather1 (city varchar(80), temp_lo int, temp_hi int, prcp real, date date);'
      Then table 'weather1' exists
      Then I close database connection
        Parameters:
        tableName - Table name
        See Also:
        connectDatabase(String, String, String, String, String, String, String), executeQuery(String), disconnectDatabase()

      • verifyTableDoesNotExists

        @Then("^table \'(.+?)\' doesn\'t exists$")
public void verifyTableDoesNotExists​(String tableName)
        Verify if a table does not exists 
        
 Example:

 Scenario: DROP a table and verify it was deleted
      Given I connect with JDBC to database 'databaseName' type 'mysql' on host '121.0.0.1' and port '3306' with user 'root' and password 'P@$$W0RD'
      When I execute query 'DROP TABLE weather1;'
      Then table 'weather1' doesn't exists
      Then I close database connection
        Parameters:
        tableName - Table name
        See Also:
        connectDatabase(String, String, String, String, String, String, String), executeQuery(String), disconnectDatabase()

      • executeSelectQuery

        @When("^I query the database with \'(.+?)\'$")
public void executeSelectQuery​(String query)
        Executes an SQL statement which returns a ResultSet, such as a SELECT statement

        This step is for executing a SQL statement which returns a ResultSet object, typically a static SQL SELECT statement. The result is stored in a local variable that can be read by future steps in the same scenario. Other types of statements (statements that dont return a result such as INSERT, UPDATE, DROP, CREATE, etc) must be executed with executeQuery(String) 

        
 Example:

 Scenario: Select all fields from table
      Given I connect with JDBC to database 'databaseName' type 'mysql' on host '121.0.0.1' and port '3306' with user 'root' and password 'P@$$W0RD'
      When I query the database with 'SELECT * FROM weather1;'
        Parameters:
        query - An SQL statement to be sent to the database, typically a static SQL SELECT statement
        See Also:
        connectDatabase(String, String, String, String, String, String, String), compareTable(DataTable)

      • compareTable

        @Then("^I check that result is:$")
public void compareTable​(io.cucumber.datatable.DataTable dataTable)
        Verifies the results of a SELECT query against a DataTable

        This step compares the result of a previous SELECT operation to the given datatable. The datatable must contain the result as it is expected from the database. If the given SELECT statement did not return any rows, only the columns names will be returned as a single row 

        
 Example:

 Scenario: Check the result of a SELECT statement
      Given I connect with JDBC to database 'databaseName' type 'mysql' on host '121.0.0.1' and port '3306' with user 'root' and password 'P@$$W0RD'
      When I query the database with 'SELECT * FROM weather1;'
      Then I check that result is:
        | city      | temp_lo | temp_hi | prcp | date       |
        | Caracas   | 15      | 43      | 0.0  | 2004-11-29 |
        | Barcelona | 5       | 37      | 0.4  | 2014-11-29 |
        | Madrid    | 8       | 37      | 0.4  | 2016-11-30 |
      Then I close database connection
        Parameters:
        dataTable - list of cases to assert in a table format
        See Also:
        connectDatabase(String, String, String, String, String, String, String), executeSelectQuery(String), disconnectDatabase()

      • verifyTableContent

        @Then("^I check that table \'(.+?)\' is equal to$") @Then("^I check that table \'(.+?)\' is iqual to$")
public void verifyTableContent​(String tableName,
                               io.cucumber.datatable.DataTable dataTable)
        Verify if the content of a table matches the given DataTable

        This step verifies the whole content of the table specified. That is like performing a SELECT * FROM statement on the table and then using the datatable to check the result. This, of course, makes sense on tables that dont contain too many rows 

        
 Example:

 Scenario: Checking the content of a table
      Given I connect with JDBC to database 'databaseName' type 'mysql' on host '121.0.0.1' and port '3306' with user 'root' and password 'P@$$W0RD'
      Then I check that table 'weather1' is iqual to
        | city      | temp_lo | temp_hi | prcp | date       |
        | Caracas   | 15      | 43      | 0.0  | 2004-11-29 |
        | Barcelona | 5       | 37      | 0.4  | 2014-11-29 |
        | Madrid    | 8       | 37      | 0.4  | 2016-11-30 |
      Then I close database connection
        Parameters:
        tableName - Table name
        dataTable - DataTable to match against
        See Also:
        connectDatabase(String, String, String, String, String, String, String), disconnectDatabase()

      • executeQueryFromFile

        @Then("^I execute query from \'(.+?)\'")
public void executeQueryFromFile​(String baseData)
                          throws IOException
        Executes an SQL from a file.

        The SQL could be of any kind (a typical SELECT or a SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE) or even SQL Scripts. If the SQL returns a ResultSet, it is stored internally so further steps can use it. GingerSpec will try to resolve any variable present in the file (variables are enclosed in ${}) before executing the query. 

        
 Example:

 Scenario: Execute query from a file:
      Given I connect with JDBC to database 'databaseName' type 'mysql' on host '121.0.0.1' and port '3306' with user 'root' and password 'P@$$W0RD'
      When I execute query from 'sql/selectWeather.sql'
      Then I check that result is:
        | city          | temp_lo | temp_hi | prcp | date       |
        | San Francisco | 15      | 43      | 0.0  | 2004-11-29 |
        | Kyiv          | 5       | 37      | 0.4  | 2014-11-29 |
        | Paris         | 8       | 37      | 0.4  | 2016-11-30 |
      Then I close database connection
        Parameters:
        baseData - File location (typically schemas/myfile.sql)
        Throws:
        IOException - IOException
        See Also:
        connectDatabase(String, String, String, String, String, String, String), compareTable(DataTable), disconnectDatabase()

      • saveSqlResultInVariable

        @Then("^I save the value of the row number \'(\\d+?)\' and the column with name \'(.+?)\' in environment variable \'(.+?)\'$")
public void saveSqlResultInVariable​(int rowNumber,
                                    String columnName,
                                    String envVar)
        Save a specific element (by row and column) in an environmental variable

        The first row (row number 0) corresponds to the column names. If the previous SELECT statement did not return any rows, only the columns names will be returned as a single row 

        
 Example:

 Scenario: Saving values from a previous sql result
      Given I connect with JDBC to database 'databaseName' type 'mysql' on host '121.0.0.1' and port '3306' with user 'root' and password 'P@$$W0RD'
      When I execute query from 'sql/selectWeather.sql'
      Then I check that result is:
        | city      | temp_lo | temp_hi | prcp | date       |
        | Caracas   | 15      | 43      | 0.0  | 2004-11-29 |
        | Barcelona | 5       | 37      | 0.4  | 2014-11-29 |
      Then I save the value of the row number '1' and the column with name 'city' in environment variable 'CITY'
      Then I save the value of the row number '2' and the column with name 'temp_hi' in environment variable 'TEMP_BARCELONA'
      Then '${CITY}' matches 'Caracas'
      Then '${TEMP_BARCELONA}' matches '37'
        Parameters:
        rowNumber - the row number
        columnName - the column name
        envVar - the env var Name
        See Also:
        connectDatabase(String, String, String, String, String, String, String), executeQueryFromFile(String), UtilsGSpec.checkValue(String, String, String)

      • evaluateNumberOfRowsReturnedByQuery

        @Then("^The last sql query returned (at least|exactly|less than|more than) \'(\\d+?)\' rows$")
public void evaluateNumberOfRowsReturnedByQuery​(String condition,
                                                int numberOfRows)
        Check amount of rows returned by last query

        Verifies if the amount of rows returned by the last SQL query is exactly/at least/more than/less than the given value. The last SQL query executed should have returned rows (like a SELECT statement), otherwise, the step will fail. 

        
 Example:

 Scenario: Verify amount ot rows returned from last query (PostgreSQL database)
     Given I connect with JDBC to database 'postgres' type 'postgresql' on host '${POSTGRES_HOST}' and port '5432' with user 'postgres' and password 'postgres'
     And I execute query from 'sql/createWeather.sql'
     When I execute query from 'sql/selectWeather.sql'
     Then The last sql query returned at least '1' rows
     Then The last sql query returned exactly '3' rows
     Then The last sql query returned more than '2' rows
     Then The last sql query returned less than '4' rows
        Parameters:
        condition - Condition to evaluate (at least|exactly|less than|more than)
        numberOfRows - Expected value of rows for the condition
        See Also:
        connectDatabase(String, String, String, String, String, String, String), executeQueryFromFile(String)

      • saveAmountOfRowsReturnedInVariable

        @Then("I save the amount of rows returned by the last query in environment variable {string}")
public void saveAmountOfRowsReturnedInVariable​(String envVar)
        Save amount of rows returned from last query

        Saves the amount of rows returned from last query for future use in the scenario 

        
 Example:

 Scenario: Saving the amount of rows returned by last query in a variable for future use (PostgreSQL database)
     Given I connect with JDBC to database 'postgres' type 'postgresql' on host '${POSTGRES_HOST}' and port '5432' with user 'postgres' and password 'postgres'
     And I execute query from 'sql/createWeather.sql'
     When I execute query from 'sql/selectWeather.sql'
     Then I save the amount of rows returned by the last query in environment variable 'ROWS'
     And '${ROWS}' is '3'
        Parameters:
        envVar - Variable name
        See Also:
        connectDatabase(String, String, String, String, String, String, String), executeQueryFromFile(String)