FitNesse Query Tables

Query Tables
In the last post we looked at using Decision Tables with FitNesse. Each row in a decision table can be thought of as a Test Case, with some of the columns representing Inputs and others representing expected Outputs.

Now we move on to something a little more complicated. Query Tables represent a set of results. The Table in its entirety represents a single test (e.g. Get All Employees in IT Department).

The table contains a row for each expected result, and the columns represent the attributes of the objects. Here’s a Query Table in FitNesse that loads a set of Employees.

The Markup for this table is about as simple as you’d expect.

|Query:All Employees                             |
|Employee Number|First Name|Last Name|Hire Date  |
|1429           |Bob       |Martin   |10-Oct-1975|
|9924           |Bill      |Mitchell |19-Dec-1966|

The first thing to note is the word Query in the first row. This tells FitNesse that the table should be interpreted as a Query Table.

The name of the table ‘All Employees’ will need to map to a Fixture class called ‘AllEmployees’.

The next row contains the column headers. Our Test Fixture is going to need to mark the returned data with these Column names so that FitNesse knows where it fits in the table.

Query Table Fixtures
Before we look at the specific details of how to pass data to and return data from the Query Table Test Fixture, lets look at the structure of the Test Fixture itself.

At its simplest we need only provide a function called ‘Query’ that returns a list of objects. Since it has no parameters the fixture class doesn’t need any special constructor. The query method will get called and should return the table of data.

We’ll get to the exact details of how the list of objects will represent a table of data. For now you just need to understand that when the Tests are run in FitNesse, the ‘Query’ method will be executed, and it returns a list of something.

public class AllEmployees
{
    public List<Object> query()
    {
        throw new NotImplementedException();
    }
}

We can add a constructor to the class, this will allow our test to feed individual parameters into the Test Fixture. This can be useful if we want to pull back a filtered list of data.

public class SimpleQueryFixture
{
    public SimpleQueryFixture(date HiredBefore)
    {
    }

    public List<Object> query()
    {
        throw new NotImplementedException();
    }
}

Constructor Parameters are specified in the first row of the table.

|Query:All Employees                 |01-Jan-2000|
|Employee Number|First Name|Last Name|Hire Date  |
|1429           |Bob       |Martin   |10-Oct-1975|
|9924           |Bill      |Mitchell |19-Dec-1966|

Of course you can provide multiple parameters to your constructor, just make sure the values in the table match the signature of the Test Fixture constructor.

|Query:Employees By Date City Dept|01-Jan-2000|"IT"|"London" |
|Employee Number             |First Name |Last Name|Hire Date|

We can also optionally implement a ‘Table’ method. If supplied this will be invoked before the ‘Query’ method, and it will receive the existing FitNesse table and it’s contents. This can be useful if we want to provide a table of data, or a partially filled table, and have columns filled in or even overwritten.

public class SimpleQueryFixture
{
    public void table(List<List<String>> table)
    {
        throw new NotImplementedException();
    }

    public List<Object> query()
    {
        throw new NotImplementedException();
    }
}

Table Formats
The eagle eyed among you will notice that the data structure passed to the ‘Table’ method is a List of Lists of Strings, whereas the data structure returned from the ‘Query’ method is a List of Objects.

It’s worth understanding how these tables are represented and the differences between them. We’ll see these data structures used again and again as we look at other types of FitNesse/SLiM tables.

The Output Table – From the ‘Query’ method
We’ll start with the output table since that is by far the most common one you’ll use. It uses a nested List of Lists of Lists type arrangement. Here’s a hard-coded example of how you would return a table of employee details from the query method of your Test Fixture. It illustrates the data structure.

public List<Object> query()
{
    return new
          List<Object>{
            new List<Object>{
              new List<String>{"Employee Number", "1429"},
              new List<String>{"First Name", "Bob"},
              new List<String>{"Last Name", "Martin"},
              new List<String>{"Hire Date", "10-Oct-1974"}
            },
            new List<Object>{
              new List<String>{"Employee Number", "8832"},
              new List<String>{"First Name", "James"},
              new List<String>{"Last Name", "Grenning"},
              new List<String>{"Hire Date", "15-Dec-1979"}
            }
          };
}

From the outside our table looks like a List of Objects. Each item in that list represents a row in the table, and is also represented by a List of Objects. Each cell in the row is represented by a List of Strings. This last bit is where the confusion may lie for the uninitiated.

Instead of representing the Column headers once in a row of their own, the table represents each cell in every row as a Name/Value pair in the form of a List of two strings.

This has it’s advantages, you can pull an individual Row out of the Data structure and have everything you need, the column names and cell values.

It is a little unconventional though, there’s a bit of a mismatch between how you view a table and how it’s actually represented. But, once you know what’s happening it’s fine. This incidentally is where the input and output tables differ. Tables that are sent to the Table method represent the column headers as a row in their own right.

On order to avoid creating boilerplate code from scratch every time we use the Query Table, we can take the tasks of translating from domain objects to this list arrangement and make a generic reusable version. We’ll look at how to do that towards the end of this post.

The Input Table – To the ‘Table’ method
Tables that are sent by FitNesse to the ‘Table’ method are represented by a List>. To put that in english, each row is a list of Strings, and a table is a List of these rows. This is much closer to the way you would imagine a table would be represented.

Using our tables from above as an example, if we have a table that shows Employee Number, First Name, Last Name and Hire Date, we could use code like the following to turn the table into a list of Employee objects.

List<Employee> employees = new List<Employee>();

    foreach(var row in table)
        {
            if(row[0] != "Employee Number")
            {
                employees.Add(
                    new Employee(row[0],
                                 row[1],
                                 row[2],
                                 row[3]));
            }
        }

Pardon the horrible code here, but you get the point. We ignore the first row as it contains the column headers. The four items in each subsequent row represent the cell values from the table, we pass these to the constructor of our domain object.

Translating from Domain Objects To Query Table
It’s unlikely that the data in your application will be represented by a data structure that looks anything like that required by the Query Table.

Assuming we have a list of Employee objects, how are we going to knock it into the shape that will allow our test fixture to pass it pack to FitNesse.

Obviously we could hand code each Test Fixture as the need arises, but that’s a lot of boring repetitive boilerplate code.

Below is an example of using Reflection to convert a list of objects into a list that FitNesse can work with. It makes the assumption that the properties of the object are in CamelCase and by splitting the words in the property name we get the Column name from the table. So, ‘EmployeeNumber’ becomes ‘Employee Number’

class SlimTableAdaptor<T>
{
    public List<Object> FromObjects(List<T> items)
    {
        return CreateTable(items);
    }

    private List<Object> CreateTable(List<T> items)
    {
        return items.Select(CreateRow).Cast<Object>().ToList();
    }

    private List<Object> CreateRow(T item)
    {
        PropertyInfo[] properties = GetProperties();
        return properties.Select(property =>
                         CreateColumn(item, property)).ToList();
    }

    private object CreateColumn(Object item, PropertyInfo property)
    {
        string propertyName = SplitCamelCase(property.Name);
        string value = GetPropertyValue(item, property.Name);
        return new List<string> { propertyName, value };
    }

    private PropertyInfo[] GetProperties()
    {
        return typeof(T).GetProperties();
    }

    private string GetPropertyValue(object source, string property)
    {
        object value = source.GetType()
                             .GetProperty(property)
                             .GetValue(source, null);

        string valueAsString = value.ToString();

        if (value.GetType().Name == "DateTime")
            valueAsString = FixDate(value);

        return valueAsString;
    }

    private string SplitCamelCase(string value)
    {
        return System.Text.RegularExpressions.Regex
            .Replace(value, "([A-Z])", " $1").Trim();
    }

    private string FixDate(Object value)
    {
        DateTime date = (DateTime) value;

        if (date.TimeOfDay.Ticks == 0)
            return date.ToString("dd-MMM-yyyy");

        return value.ToString();
    }
}

With a helper class like this written, it becomes a trivial matter to create a Test Fixture class. Here’s what our Query method might look like.

public List<Object> query()
{
    var employees = EmployeeRepository.GetAll();

    var helper = new SlimTableAdaptor<Employee>();
    return helper.FromObjects(employees);
}

This is a simplified version of the helper class. You might like to add some additional features such as

• Ability to specifically map property names to column names
• Ability to return a subset of properties rather than all of them
• Ability to translate from Input Table to list of Objects
• Ability to translate other sources of Data (e.g. Data Tables, XML etc.)

Subset Tables
There are two specialised types of Query Table that you can use, Subset Query Tables, and Ordered Query Tables. The names pretty much leave nothing to the imagination here.

When we use a Subset Query Table our tests pass as long as the rows we specify exist in the results returned from the Fixture.

When we use an Ordered Query Table our tests pass as long as the rows we specify match exactly the rows returned from the Fixture, including the ordering of the rows.

Subset and Ordered Query Tables work exactly like regular Query Tables, the data is returned in the same format, we can specify constructor parameters and an input table in exactly the same way.

Here’s a Subset Query Table

|Subset Query:All Employees                      |
|Employee Number|First Name|Last Name|Hire Date  |
|1429           |Bob       |Martin   |10-Oct-1975|

As long as one of the rows returned from the AllEmployees test fixture matches the row in the table, the test will pass.

Here’s an Ordered Query Table

|Ordered Query:All Employees                     |
|Employee Number|First Name|Last Name|Hire Date  |
|1429           |Bob       |Martin   |10-Oct-1975|
|9924           |Bill      |Mitchell |19-Dec-1966|

The rows returned from the AllEmployees test fixture must match those in the table exactly, including matching the order in which the rows are listed.