RSpec Testing Framework

Qualified supports the RSpec behavior-driven testing framework, which uses Ruby.

These notes are adapted from rspec-core.

Why Ruby/RSpec?

Most Qualified challenges are tested using the same language as the solution. However, SQL is a query language which is not a feasible option for writing unit tests with. With this in mind, Qualified offers Ruby and the RSpec testing framework to execute and test SQL. Ruby and RSpec are easy to use and allow you to set up database state programmatically, fetch and generate data, create test cases and render tables, visualizations and advanced output.

RSpec SQL custom utilities

A small set of utility methods and classes have been provided to make testing and displaying SQL queries easy within Ruby. These utility methods are available from any file in the runner.

  • $sql is a global variable which contains the user's SQL solution
  • run_sql is a method that will run the user's query, execute any non-SELECT statements and print and return any result sets from SQL SELECT statements.
  • compare_with is a method which compares the candidate's submitted query to the reference query and auto-generates a test suite based on the expected query results.

Sequel

We use the Sequel gem to communicate with the database driver.

Daff

We use the Daff gem to generate HTML tables for rendering on the code runner user interface. These tables provide feedback to the candidate and allow them to interact with the query results.

Basic Example

RSpec uses the words "describe" and "it" so we can express concepts like a conversation:

"Describe a query"
"It should return 5 rows"
# prints the user's query to the UI and returns the results as a Sequel dataset.
results = run_sql

describe "Query" do
  it "should return 5 rows" do
    expect(results.size).to eq 5
  end
end

The describe method creates an ExampleGroup. Within the block passed to describe you can declare examples using the it method.

Under the hood, an example group is a class in which the block passed to describe is evaluated. The blocks passed to it are evaluated in the context of an instance of that class.

compare_with(expected)

The compare_with method takes an expected parameter, the reference query. You can embed this reference query within the preloaded code like so:

def expected
  DB[%q(
    PLACE YOUR QUERY HERE
  )].to_a
end
Caution

Preloaded code is visible to candidates programmatically since it exists in the setup.rb file in the workspace. You can disable sample test cases or remove setup.rb when run to prevent the candidate from accessing its source.

Caution

Make sure to always return an array by having to_a at the end. Otherwise its possible to expose the query's SQL string.

Within your Test cases section, invoke compare_with, passing in your expected function's return value:

compare_with(expected())

When compare_with runs, describe/it RSpec blocks will automatically be generated for based on the expected data which provide feedback to the candidate. Assertions related to rows and columns (such as row count) are auto-generated by compare_with.

You can also extend the generated specs by providing a block. For example:

compare_with expected do
  rows do
    it "should have movie titles ordered alphabetically" do
      expect(actual.first[:title]).to be < actual.last[:title]
    end
  end
end

Learn More

You can learn how to use it on the RSpec Website.