Tech Blog

With our day-to-day test automation, we try to avoid dependencies as much as possible. The majority of tests are executed on a set of data that exists in the database in the QA environment. However, there are situations when we need to add or edit data in order to carry out end to end testing. When we do this, we need to make sure we don’t influence existing data which is used by different tests.

Cucumber with ActiveRecord

Since we have a limited number of tests that are dependent on a unique set of data, we use a standalone ruby script to deal with the database without creating a model. We create a connection to the QA database using ActiveRecord. We use this connection to perform queries and validate data. Here is an example test scenario where we altered data values via ActiveRecord…

  Scenario: Previously ordered item is unavailable when reordering
    Given I have placed an order including a new item
    And I am on the home page
    And one of the items is unavailable
    When I select the option to reorder
    Then I am taken to the menu page
    And I should see a message that says some items are not available
    And I should see the basket summary as:
      | item         | Americana |
      | price        | 10.00     |
      | delivery_fee | 2.00      |
      | total        | £12.00    |

Here is how we make the DB connection with ActiveRecord in the helper file. Also it notifies us after the DB has connected successfully and vice-versa.

require 'active_record'
class DbDataHelper
 def initialize
   db_connect('#{tenant}', 'qa')
  end
 def db_connect(tenant, environment)
   dsn_string = "Driver={SQL Server};Server=-server_name;Database=database_name;Uid=user_id;Pwd=password;"
   ActiveRecord::Base.establish_connection(:adapter => 'sqlserver', :dsn => dsn_string, :mode => 'odbc')
   begin
     ActiveRecord::Base.connection.active?
     puts "Info - Connected to the [#{DB_name}] Database"
   rescue => e
     puts "Error - Exception db connection : #{e.message}"
     raise "Failed to connect to the [#{DB_name}] Database"
   end
   ActiveRecord::Base.connection
 end
end

The scenario validates certain error messages if the product price has changed or a product has been removed from the menu when the user orders something they have ordered before. Since we need to delete the item or edit the item price for a particular menu, we had to add a new item to the DB every time we ran this test. This was to ensure we were not affecting existing data which is used by other tests. The following example describes creating a new product and adding it to certain menus. The first task is to a create the new item. We had to make sure every new item was created with a unique name. When the item is created, it is given a generated product ID, which we then needed to extract in order to add it to a menu.

@new_item_name = generate_new_item_name
  def generate_new_item_name
    'Test Item' + generate_random_text(5)
  end
  def create_new_item(restaurant_id, menu_id)
  @new_item_name = generate_new_item_name
    add_item_into_product = "
        INSERT INTO dbo.Item
                ( XX1,
                  XX2,
                  XX3
                )
        VALUES  ( 'YY1' , -- XX1- varchar(50)
                  '#{@new_item_name}', -- XX2- varchar(50)
                  YY3, -- XX3- int
                                 )"

Initially we assign the SQL insertion query which needs to be executed into a variable.
Afterwards, we pass the assigned variable along with the DB connection. In this scenario, we have used the execute method since we don’t expect anything to return.

 ActiveRecord::Base.connection.execute(add_item_into_product)

Then we have used the select_value method to return a single value (i.e. product ID) from a record, in order to add the new item into the respective menu.

get_the_product_id = "SELECT ProductID FROM dbo.Item
                WHERE ResturanteID = restaurant_id}AND Name = '#{@new_item_name}'"
 product_id = ActiveRecord::Base.connection.select_value(get_the_product_id)
add_item_into_menucardproduct = "INSERT INTO dbo.Mc_product
            ( XX1,
                  XX2,
                  XX3
                )
        VALUES  ( 'YY1' , -- XX1- varchar(50)
                  YY2, -- XX2- varchar(50)
                  #{product_id}, -- XX3- int
                                 )"
    ActiveRecord::Base.connection.execute(add_item_into_menucardproduct)
    kill_connections
    { product: product_id, item: @new_item_name }
  end

When updating a certain value related to a product (e.g. price of the product), we pass the update SQL query to the execute method, like we did in the insertion step.
In each method we clear the DB connection which we created at the beginning, because we don’t want to leave the DB connection open.

def kill_connections
   ActiveRecord::Base.clear_active_connections!
 end

The changes that we are doing within the test need to be reset at the end of test. Here’s an example where we delete the item from all the references it has…

   def delete_the_item(product_id, menu_id)
    delete_item_from_menucard_product = "DELETE FROM dbo.Mc_product
    WHERE ProductID = #{product_id} AND MenuCardID = #{menu_id}"
    delete_item_from_product = "DELETE FROM dbo.Item
                WHERE ProductID = #{product_id} AND Name = '#{@new_item_name}'"
    ActiveRecord::Base.connection.execute(delete_item_from_menucard_product)
    ActiveRecord::Base.connection.execute(delete_item_from_product)
    kill_connections
  end

We add an exit tag for the tests that require data to be reset at the end of execution. This tag ensures that the item is deleted from the DB even if the test failed in the middle of execution. This is how we defined the exit tag in the env.rb file.

After('@reset_product') do |scenario|
  delete_item_from_menu '#{menu_id}' if scenario.failed?
end

DB changes via API

Since the components of the JUST EAT site communicate with various APIs, any data updated in the DB needs an API refresh in order to be visible in the front-end.
For example, when we are updating an existing item price via the DB, we need to clear the cache of the menu API in order to see the price change in the menu (in the front-end) during the test execution.

def edit_item_price(menu_id)
  access_db = DbDataHelper.new
  access_db.edit_the_item_price(@product_id, menu_id)
  clear_cacheing_menu_api menu_id
end
def clear_cacheing_menu_api(menu_id)
  request_url = URI.escape("#{ menu_api_url }/menu/#{ menu_id }")
  post(request_url, '{}')
end

Summary

We can see how data intensive it can be to test an application. In this instance particularly, the tests are completely data dependent, and getting the data right at first is an absolute must. As an example we can see not only the data model approach but also the direct sql query execution fits the purpose of retrieving or altering data.
 
Thanks for reading.
~ Deepthi