Slacker Tests

Slacker is based on RSpec - one of the most popular behavior-driven development (BDD) frameworks for Ruby.
As such, a Slacker test is essentially an RSpec specification (with a twist - keep reading).

All specifications (or tests) are located in the spec sub-folder of a Slacker project. A specification contains one or more example groups defined using the describe method. Each example group contains one or more examples defined using the it method.

For example:

# spec/my_nifty_sproc_spec.rb

# Example group
describe 'my_nifty_sproc' do

  # First example
  it 'does miracles with numbers' do
    # Example code goes here
  end
  
  # Second example
  it 'puts some system sprocs to shame' do
    # Example code goes here
  end
  
  ...

end

If you are not familiar with the basic RSpec specification structure, please take a look here.

Context Setup

Just like any regular RSpec specification, a Slacker spec can have a common context setup through before(:each):

# spec/my_nifty_sproc_spec.rb

# Example group
describe 'my_nifty_sproc' do

  before(:each) do
    # Setup code goes here
    # This code will be executed before each example in the group
  end

  it 'does miracles with numbers' do
  ...  

  it 'puts some system sprocs to shame' do
  ...
end

The code passed to before(:each) is executed before every example in the group.
One can use after(:each) as a way to tear down the context, but as you will see later, you rarely need to use after(:each) in Slacker, because all examples are executed in their own isolated transaction which is automatically rolled back as soon as the example is complete.

Slacker Runtime

When you execute slacker, it launches RSpec, bootstraps it with the Slacker environment and then executes the specifications.

The Slacker environment delivers a Slacker DSL to your specifications. It also maintains the connection to the target database, the evaluation and execution of your SQL scripts as well as the process of creating the SQL trace scripts generated for each example.

Slacker executes each example in a separate T-SQL transaction which is rolled back after the example is complete.

All the code of an example, including the before(:each) and after(:each) blocks, is executed in the context of its transaction.

The transaction of an example begins before executing the first before(:each) block of the example (or before the example begins in case there is no before(:each) block). The transaction of an example is rolled back after the last after(:each) block of the example (or after the example completes or fails in case there is no after(:each) block).

This ensures that every example starts with a clean slate database. It also means that you do not need to clean up your test database after every execution of Slacker -- all changes performed by an example and its before(:each) and after(:each) blocks are automatically rolled back at the end of the example.

Slacker DSL

Slacker DSL is a collection of database-aware and Slacker project-specific methods available to your test code.

query
sql
csv
load_csv
touch_csv
match

In addition to the built-in DSL methods, if you create your Slacker projects with slacker_new, you will have access to the following helper methods which allow you to execute and test stored procedures, scalar functions and table functions without the need to invoke SQL through query or sql:

sproc
s_func
t_func

query

Use query to execute a T-SQL script of one or more statements against the target database.

describe 'My database' do

  it 'contains system objects' do
    res = query("select id, name from sysobjects where xtype = 'S';")
  end

end

The return value of query depends on the number of resultsets generated by the script.

If the script generates a single resultset (such as in the example above), the return value of query is that single resultset. A resultset is an array of records. A record is a hash of name-value pairs representing the name of each field in the resultset and the field's value.

For example, the above call will return something of the sort:

[
  {:id => 4, :name => 'sysrowsetcolumns'},
  {:id => 5, :name => 'sysrowsets'},
  {:id => 7, :name => 'sysallocunits'},
  ...
]

When the script passed to query generates multiple resultsets, query returns an array of resultsets.

For example:

res = query("select id, name from sysobjects where xtype = 'S';
             select count(*) c from sysobjects;")

returns...

[
  # First resultset
  [
    {:id => 4, :name => 'sysrowsetcolumns'},
    {:id => 5, :name => 'sysrowsets'},
    {:id => 7, :name => 'sysallocunits'},
    ...
  ],
  
  # Second resultset
  [
    :c => 41
  ]
]

The result of a query can be verified with the help of RSpec expectations. For example, the following example makes sure that our database has exactly one system object called sysrowsetcolumns:

describe 'My database' do

  it 'contains sysrowsetcolumns' do
    res = query("select * from sysobjects where xtype = 'S' and name = 'sysrowsetcolumns';")

    expect(res.count).to be == 1
    expect(res[0][:name]).to be == 'sysrowsetcolumns';
  end

end

If a Ruby block is passed to query, it yields this block and passes the results to it as a block parameter:

query "select id, name from sysobjects where xtype = 'S' order by name asc;" do |res|
  expect(res.count).to be == 41
  expect(res[0][:name]).to be == 'sysallocunits'
  expect(res[1][:name]).to be == 'sysasymkeys'
end

sql

Method sql represents a more structured approach to SQL script execution.

The sql folder of a Slacker project is where you put your SQL templates. A SQL template is an ERB (Embedded Ruby) template file which contains a T-SQL script optionally intermixed with Ruby code.

Here's an example of a simple pure SQL template (no Ruby) stored in file sql/get_top_sys_object.sql.erb:

declare @x varchar(250);

select top 1 @x = name from sysobjects where xtype = 'S' order by name asc;
select @x as [name];

And here's one intermixed with Ruby (for now ignore the options calls below -- we'll cover that later):

select * from customer
  where age > <%= options[:age_limit] %>
    and first_name = '<%= options[:name] %>';

A SQL template stored in folder sql automatically appears as a method of the object returned by method sql.

For example, the above template could be invoked as follows:

res = sql.get_top_sys_object

...or like this:

sql.get_top_sys_object do |res|

  ...

end

The object returned by sql strictly follows the structure of your sql folder.
For example, if the contents of your sql folder looked like this:

my_project
         :
         :
         └ sql
             ├ template_x.sql.erb
             ├ common_templates
             │                ├ template_y.sql.erb
             │                └ tempalte_z.sql.erb
             └ special_templates
                              └ template_x.sql.erb

...the above four templates could be invoked as follows:

sql.template_x
sql.common_templates.template_y
sql.common_templates.template_z
sql.special_templates.template_x

SQL Template Parameters

When a SQL template is invoked with a hash passed as a parameter, this hash becomes available to the template's Ruby code through the options method.

For example, if we called a template like this:

res = sql.template_name(:age_limit => 17, :name => 'John')

...and if the code of the template looked like this...

select * from customer
  where age > <%= options[:age_limit] %>
    and first_name = '<%= options[:name] %>';

...Slacker will evaluate the template to the following SQL script:

select * from customer
  where age > 17
    and first_name = 'John';

csv

Method csv loads a CSV file into a CSV::Table object.

For example, if we had the following data/my_data.csv file:

"name","age"
"John", 23
"Melissa", 21
...

...we would be able to access the above data from any Slacker specification as follows:

my_csv = csv('my_data.csv')

Note that the CSV file path is relative to the project's data folder.

Method csv is useful when you need to load a CSV file in memory and manipulate it before passing it to one of the other CSV related methods such as load_csv and match.

load_csv

Use load_csv to load a CSV file directly into a table in the target database.
For example, if we had the following data/my_data.csv file:

"name","age"
"John", 23
"Melissa", 21

We can load it into table dbo.MyCustomer from within a Slacker specification:

load_csv('my_data.csv', 'dbo.MyCustomer')

This generates an INSERT SQL statement with UNION ALL to combine the CSV data into a resultset which is then loaded into the target table. No data is deleted from the table prior to the INSERT.

Note that the column names of the CSV file must exist in the target table, otherwise you will get an error while Slacker executes the generated INSERT SQL script.

load_csv can be used to load data from a CSV::Table object too.
This is useful in case you need to modify the CSV data before loading it into the table:

my_csv_obj = csv('my_data.csv')
my_csv_obj[0][:name] = 'Peter' # Replace John with Peter
load_csv(my_csv_obj, 'dbo.MyCustomer')

touch_csv

touch_csv is a data generator which helps you generate a CSV::Table object with an arbitrary number of records populated with data based on a template CSV file.

The output of touch_csv can be passed to any of the other CSV methods such as load_csv and match.

Let's say that you have the following single-record CSV file data/one_good_customer.csv:

"name", "age", "attr1", "attr2"
"John", "20", "abc", "xyz"

Now let's say we had to load a database table with 5 records similar to the above record, but with unique names.
We can call touch_csv to generate this CSV object for us:

csv_obj = touch_csv('one_good_customer.csv',
                    [
                      {:name => 'Melissa', :age => '13'},
                      {:name => 'Ethan'},
                      {:name => 'Hunter'},
                      {:name => 'Kyle', :age => '23'},
                      {:name => 'Jennifer'}
                    ]
                  )

This will generate the following CSV::Table object:

[
  {:name => 'Melissa', :age => '13', :attr1 => 'abc', :attr2 => 'xyz'},
  {:name => 'Ethan', :age => '20', :attr1 => 'abc', :attr2 => 'xyz'},
  {:name => 'Hunter', :age => '20', :attr1 => 'abc', :attr2 => 'xyz'},
  {:name => 'Kyle', :age => '23', :attr1 => 'abc', :attr2 => 'xyz'},
  {:name => 'Jennifer', :age => '20', :attr1 => 'abc', :attr2 => 'xyz'}
]

touch_csv loads the CSV file passed as the first parameter and then modifies it according to the hash-array passed as a second parameter. If the hash-array contains more records than the source CSV file, touch_csv pads the result with records copied from the source CSV and then applies changes to the fields according to the hash-array.

If you need to modify only the first record of a CSV file, you can simply pass a hash as the second parameter to touch_csv:

csv_obj = touch_csv('one_good_customer.csv', {:name => 'Melissa', :age => '13'})

...or simply:

csv_obj = touch_csv('one_good_customer.csv', :name => 'Melissa', :age => '13')

touch_csv can also be called with a pre-loaded CSV object:

csv_obj1 = csv('my_data.csv')

...

csv_obj2 = touch_csv(csv_obj1, :x => 'xyz')

In case you need to generate data for particular fields and you don't really care what the values are, as long as they are unique per record, you can use the third parameter of touch_csv.
Consider again our one_good_customer.csv file located in data:

"name", "age", "attr1", "attr2"
"John", "20", "abc", "xyz"

If we want to generate a 3-record CSV object with predefined names and unique values for fields attr1 and attr2, we can call touch_csv like this:

csv_obj = touch_csv('one_good_customer.csv',
                    [
                      {:name => 'Melissa'},
                      {:name => 'Ethan'},
                      {:name => 'Hunter'}
                    ],
                    {:attr1 => 'abc_', :attr2 => 'xyz_'}
                  )

This will generate the following CSV::Table object:

[
  {:name => 'Melissa', :age => '20', :attr1 => 'abc_1', :attr2 => 'xyz_1'},
  {:name => 'Ethan', :age => '20', :attr1 => 'abc_2', :attr2 => 'xyz_2'},
  {:name => 'Hunter', :age => '20', :attr1 => 'abc_3', :attr2 => 'xyz_3'}
]

touch_csv takes the third parameter as an indication that the values for the fields, whose names are keys in that hash, have to be generated.

The value generation is performed by appending the index of the current record (starting at 1) to the value associated with the field's name in that third parameter hash.

match

match is a custom Slacker matcher used to compare the resultset returned by query or sql (or any of the methods based on them, such as sproc and t_func), with the contents of a CSV file or a CSV::Table object.

For example, if a stored procedure returned a resultset, one can match that result with an expected resultset stored in a file like this:

# Call the sproc
result = sproc('dbo.Get_Order_Items', :order_code => 'X12-024')

# Compare results with expectations
expect(result).to match('expected_order_items_1.csv')

Slacker performs an exact match comparison between the resultset and the contents of the CSV file. If it finds that the resultset does not match the CSV file it fails the test with a message similar to:

Failure/Error: expect(result).to match('expected_order_items_1.csv')
  Field "xyz", Record 12: Expected value "12.5", got "9.65"

Of course match can be used with pre-loaded CSV::Table objects as well:

csv_obj = touch_csv('expected_result.csv', :xyz => '12.5')
expect(sql.get_calculated_data).to match(csv_obj)

This means that you can run comparisons against expectations generated by touch_csv, which opens the door for matrix-based combinatorial testing scenarios.

sproc

sproc is a helper method which makes it easy to call and test stored procedures without having to create parameterized SQL templates.

result = sproc('dbo.My_Sproc', :param1 => 12, :param2 => 'Hello')
expect(result).to match('expected_result.csv')

The method takes three parameters - a stored procedure name, an optional hash of input parameters and an optional hash of output parameters.

When invoking a stored procedure with output parameters, the helper method will produce SQL script which declares variables which will be populated with the output values.

The script will then perform a select with the names of the variables as columns.

For example:

result = sproc('xp_sprintf',
  # Input parameters.
  {
    :format => 'Hello %s',
    :argument1 => 'Marry'
  },
  # Output parameters.
  {
    :string => 'varchar(100)'
  }
)[0][:string]

expect(result).to be == 'Hello Marry'

This call will declare variable @string of type varchar(100) and will pass it as the output value to parameter named @string. It will then perform select string = @string and will return this as the result of calling sproc.

If for some reason you want to name the receiving variable with a name different from the sproc parameter, instead of passing an output parameter hash like {:param_name => 'type'} you can pass an output parameter hash in the form of {:param_name => {:var_name => 'type'}}:

result = sproc('xp_sprintf',
  # Input parameters.
  {
    :format => 'Hello %s',
    :argument1 => 'John'
  },
  # Output parameters.
  {
    :string => {:my_output_var => 'varchar(100)'}
  }
)[0][:my_output_var]

expect(result).to be == 'Hello John'

This will generate a SQL script which declares variable @my_output_var of type varchar(100) and passes it to the output parameter @string.

s_func

s_func is a helper method which makes it easy to execute a scalar function with an arbitrary number of parameters.

For example:

result = s_func('COALESCE', nil, 12, nil, 24)
expect(result).to be == 12

t_func

t_func invokes a table function with an arbitrary set of parameters and puts the result of the call in the resultset.

For example:

expect(t_func('dbo.tf_Fibonacci', 1000000)).to match('helper_method_examples/fibonacci_1.csv')

result = t_func('dbo.tf_My_Function', 'param_1_value', 12, 102.32)
expect(result).to be == 12

Examples

To see examples of using sproc, s_func and t_func, check out helper_method_examples.rb which is included in the scaffolded project generated by slacker_new.

Table-Valued Parameters

Table valued parameters can be passed to sproc and t_func through the use of helper class TableVariable.

Here's an example.

Let's say that we have a user-defined table type:

create type dbo.NumberTable as table
(
  number_1 int,
  number_2 int
);

And let's say that we have a table function with the following signature:

create function dbo.tf_MultiplyNumbers
(
  @numbers dbo.NumberTable readonly,
  @multiplier int
)
...

We could call dbo.tf_MultiplyNumbers by passing a table-valued variable like this:

# Declare a table-valued variable by passing its name, type, columns to populate
# and record values.
my_table_variable = TableVariable.new(
  '@my_table_variable',
  'dbo.NumberTable',
  ['number_1', 'number_2'],
  [
    [1, 12],
    [2, 4],
    [3, 7]
  ]
)

result = t_func('dbo.tf_MultiplyNumbers', my_table_variable, 24)

Common Methods

Very often two or more examples in a specification share the same functionality. If you see that you are typing the same code over and over again in different examples, you can factor that code out in a method like this:

describe 'XYZ' do

  def my_common_method(param1, param2)
    # Common code
    ...
  end

  it 'should work as expected with abc and xyz' do
    ...
    my_common_method('abc', 'xyz')
    ...
  end

  it 'should work as expected with bbc and nbc' do
    ...
    my_common_method('bbc', 'nbc')
    ...
  end

end

See Helper Modules to learn how to share functionality across specifications.

Slacker Project Folder Access

To access files in the currently running Slacker project from within a Slacker test, you can use the following method:

Slacker.configuration.expand_path('relative_path')

This will expand the 'relative_path' to an absolute path based on the Slacker's project folder.

Next, see Helper Modules.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Slacker Tests

Slacker Runtime

Slacker DSL

query

sql

csv

load_csv

touch_csv

match

sproc

s_func

t_func

Examples

Table-Valued Parameters

Common Methods

Slacker Project Folder Access

Clone this wiki locally