Unit Testing Databases with tSQLt Part 12 – Unit Testing Views

by Greg M Lucas on 30 October 2012

In Part 11, we delved into mocking stored procedures and explored how to populate output parameters or add a row to a table when faking a procedure. In this post, we will look at views, including writing tests against the views themselves and also how to mock a view that another object under test depends upon.

As we’re going to focus our attention on views, let’s assume that we have the following schema in place – most of which is completely unrelated to the views we’re going to create. But that’s a lot like like the real world where the tSQLt Framework’s ability to mock the relevant objects means we can safely ignore the rest of the schema. You can download the DDL for this complete strucure here.

Example Schema

Testing a Simple View

There are two scenarios we are interested in, the first is where we are writing tests to validate the behaviour of the view itself, the second is where we are writing tests for an object that that references a view. The first case is pretty straightforward and for the second case, it is not so well known that tSQLt allows us to mock a view in the same way that we can fake a table. So we will start with testing views then move on to how to fake a view.

So given the schema above, let’s create a view called OrderItemView. Among other things we want this view to include a Value for each line item (i.e. Price x Quantity). So the very simplest test we can write looks like this:

CREATE PROCEDURE [ViewTests].[test_OrderItemView_calculates_correct_value]
AS
BEGIN
    --! Assemble
    EXEC tSQLt.FakeTable @TableName = 'dbo.OrderItem' ;

    INSERT dbo.OrderItem (Quantity, Price) VALUES (5, 1.54) ;

    DECLARE @Expected float; SET @Expected = 7.7;

    --! Act
    DECLARE @Actual float; SELECT @Actual = [Value] FROM dbo.OrderItemView

    --! Assert
    EXEC tSQLt.AssertEquals @Expected, @Actual
END

In this first test, we fake the OrderItem table to isolate the dependencies and add one row to the mocke version of the table. You’ll notice that we only need to populate the Quantity and Price columns for this test. Although on the real table most of the remaining columns are defined as NOT NULL, all columns on a faked table allow NULL so we can ignore them if they’re not relevant to the test in hand. This can not only save time writing this test, it also means we don’t have to go back and refactor this test in future when other non-null columns are added to the OrderItem table. When we first run this test, as expected we get this error:

[ViewTests].[test_OrderItemView_calculates_correct_value] failed: Invalid object name 'dbo.OrderItemView'.{test_OrderItemView_calculates_correct_value,13}

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name                                             |Result|
+--+-----------------------------------------------------------+------+
|1 |[ViewTests].[test_OrderItemView_calculates_correct_value]|Error |
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 1 test case(s) executed, 0 succeeded, 0 failed, 1 errored.
-----------------------------------------------------------------------------

So what is the minimum code we can write to pass this test?

CREATE VIEW [dbo].[OrderItemView]
AS
SELECT
    Price * Quantity AS [Value]
FROM
    dbo.OrderItem AS oi

Ok, so as views go this one is of pretty limited use but we do have our first passing test and we would expect this test to continue to pass as we add more functionality. Let’s skip ahead to the next interesting feature. Our view now looks like this, returning all the columns from OrderItem including DispatchDate which, as you would expect, allows NULL. However, the application that uses this data needs the dispatch date to be some fixed point in the future as it doesn’t handle NULL date/times very well. Arguably, this isn’t the best way to address this issue but for the sake of this example, our view needs to ensure that DispatchDate is never NULL.

CREATE VIEW [dbo].[OrderItemView]
AS
SELECT
      ItemId
    , OrderId
    , ProductId
    , Quantity
    , Price
    , DispatchDate
    , Price * Quantity AS [Value]
FROM
    dbo.OrderItem AS oi

So, to test this we want to consider what happens when there is or is not a value for DispatchDate. Again, because we’re using FakeTable, we only need to populate the columns of interest and we just need one row with a DispatchDate and one row without. The easiest way to write this test is to put the results we expected to see into a table called #expected and select the columns of interest from the view into an #actual table then use tSQLt.AssertEqualsTable to compare the two.

CREATE PROCEDURE [ViewTests].[test_OrderItemView_supplies_default_DispatchDate]
AS
BEGIN
    --! Assemble
    EXEC tSQLt.FakeTable @TableName = 'dbo.OrderItem' ;

    --! Use a fixed date and time to avoid issues with ms differences causing
    --! this test to fail intermittently
    DECLARE @DispatchDate datetime; SET @DispatchDate = DATEADD(day, -1, GETDATE()) ;

    INSERT dbo.OrderItem (ItemId, DispatchDate)
          SELECT 1,@DispatchDate
    UNION SELECT 2, NULL

    CREATE TABLE #expected (ItemId int NULL, DispatchDate datetime NULL) ;

    INSERT #expected (ItemId, DispatchDate)
          SELECT 1, @DispatchDate
    UNION SELECT 2, '22220222 22:22:22'

    --! Act
    SELECT
          ItemId
        , DispatchDate
    INTO
        #actual
    FROM dbo.OrderItemView

    --! Assert
    EXEC tSQLt.AssertEqualsTable #expected, #actual ;
END

As we are practicing test-first development, this test will fail until we write just enough code to pass it.

[ViewTests].[test_OrderItemView_supplies_default_DispatchDate] failed: unexpected/missing resultset rows!
|_m_|ItemId|DispatchDate           |
+---+------+-----------------------+
|<  |2     |2222-02-22 22:22:22.000| |=  |1     |2012-10-27 10:00:37.757| |>  |2     |!NULL!                 |

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name                                                  |Result |
+--+----------------------------------------------------------------+-------+
|1 |[ViewTests].[test_OrderItemView_calculates_correct_value]     |Success|
|2 |[ViewTests].[test_OrderItemView_supplies_default_DispatchDate]|Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 2 test case(s) executed, 1 succeeded, 1 failed, 0 errored.
-----------------------------------------------------------------------------

The view that passes this test looks like this:

CREATE VIEW [dbo].[OrderItemView]
AS
SELECT
      ItemId
    , OrderId
    , ProductId
    , Quantity
    , Price
    , COALESCE(DispatchDate, '22220222 22:22:22') AS [DispatchDate]
    , Price * Quantity AS [Value]
FROM
    dbo.OrderItem AS oi

Testing an Object that Depends on View

I said at the beginning of this post that tSQLt also allows us to fake views in the same way that we can fake tables. So let us assume that our OrderItemView now looks like this:

CREATE VIEW [dbo].[OrderItemView]
AS
SELECT
      oi.ItemId
    , oi.OrderId
    , oi.ProductId
    , p.DisplayName AS [Product]
    , typ.Name AS [ProductType]
    , oi.Quantity
    , oi.Price
    , COALESCE(oi.DispatchDate, '22220222 22:22:22') AS [DispatchDate]
    , oi.Price * oi.Quantity AS [Value]
-----------------------------------------------------------
FROM      dbo.OrderItem     AS oi
LEFT JOIN dbo.Product       AS p   ON p.ProductId = oi.ProductId
LEFT JOIN dbo.TypeOfProduct AS typ ON typ.ProductTypeId = P.ProductTypeId
-----------------------------------------------------------

… and that we also have another view that references OrderItemView that, at the moment, looks like this:

CREATE VIEW [dbo].[OrderDetailView]
AS
SELECT
      oh.OrderId
    , oh.OrderDateTime
    , c.LastName + ', ' + c.FirstName + COALESCE(' ' + c.MiddleInitial, '') AS [CustomerName]
    , e.LastName + ', ' + e.FirstName + COALESCE(' ' + LEFT(e.MiddleName, 1), '') AS [OrderTaker]
    , oi.ProductType + ' - ' + oi.Product AS [Product]
    , oi.Quantity
    , oi.Value AS [OrderValue]
    , oi.DispatchDate
    , oh.CustomerId
    , oh.EmployeeId
    , oi.ItemId
    , oi.ProductId
-----------------------------------------------------------
FROM       dbo.OrderHeader  AS oh
-----------------------------------------------------------
LEFT JOIN dbo.Employee      AS e  ON e.EmployeeId = oh.EmployeeId
LEFT JOIN dbo.Customer      AS c  ON c.CustomerId = oh.CustomerId
-----------------------------------------------------------
LEFT JOIN dbo.OrderItemView AS oi  ON oi.OrderId = oh.OrderId
-----------------------------------------------------------

You may have noticed that both of these views make use of LEFT joins – even though the join columns are NOT NULL meaning we could also use INNER joins. This is based on something I learnt from Rob Farley (blog | twitter) in an old post about query simplification and redundant joins. Basically, the principle is that if we use LEFT joins, the query optimizer can choose to leave out tables that are not required to fulfill the query. For example, in the query

SELECT SUM(OrderValue) AS [OrderValue] FROM dbo.OrderDetailView WHERE CustomerId = 1234;

Only the [OrderHeader] and [OrderItem] tables need to be accessed. Because we’re using LEFT joins, the [Customer], [Employee], [Product] and [TypeOfProduct] table are ignored – as you can see from this execution plan.

This also happens to make it much easier for us when writing tests since we can also choose to only populate the minimum number of tables – those that are actually relevant to the test in hand.  So if we want to test that the [ProductDescription] column in [OrderDetailView] is properly formatted, we do not need to populate the [Employee] or [Customer] tables.

However, we will need to populate [OrderHeader] plus [OrderItem], [Product] and [TypeOfProduct] or just the [OrderHeader] table and [OrderItemView].  As it’s less work, I’m going to go with faking one table and [OrderItemView].  Let’s take a first stab at this test

CREATE PROCEDURE [ViewTests].[test_OrderDetailView_formats_ProductDescription]
AS
BEGIN
    --! Assemble
    EXEC tSQLt.FakeTable @TableName = 'dbo.OrderHeader' ;
    EXEC tSQLt.FakeTable @TableName = 'dbo.OrderItemView' ;

    INSERT dbo.OrderHeader (OrderId) VALUES (1001) ;

    INSERT dbo.OrderItemView (OrderId, ItemId, Product, ProductType, ProductId)
    VALUES(1001, 1, 'Train set', 'Toys', 99)

    CREATE TABLE #expected (OrderId int NULL, ProductDescription varchar(1000) NULL, ProductId int) ;

    INSERT #expected (OrderId, ProductDescription, ProductId)
    VALUES (1001, 'Toys - Train set', 99)    

    --! Act
    SELECT
          OrderId
        , ProductDescription
        , ProductId
    INTO
        #actual
    FROM
        dbo.OrderDetailView

    --! Assert
    EXEC tSQLt.AssertEqualsTable #expected, #actual ;
END

In this test, we use tSQLt.FakeTable to mock both the [OrderHeader] table and [OrderItemView]. The syntax for faking a view is identical to faking a table (without the identity and constraint options etc). We then add one row to the header table and a row to the view so we have a ProductDescription to test.  If [OrderItemView] only referenced a single table, this would work but as there are multiple tables and there is no BEFORE trigger, we get the following error when we try to compile the procedure.

Msg 4405, Level 16, State 1, Procedure test_OrderDetailView_formats_ProductDescription_on_all_values_present, Line 11
View or function 'dbo.OrderItemView' is not updatable because the modification affects multiple base tables.

This is a SQL Server error (not tSQLt) and the solution is to use dynamic SQL to populate [OrderItemView]. This only gets compiled and validated at run time, by which point [FakeTable] will have created a table called [OrderItemView]. So our new INSERT statement might look something like this:

    DECLARE @cmd nvarchar(max);
    SET @cmd = 'INSERT dbo.OrderItemView (OrderId, ItemId, Product, ProductType, ProductId)'
            + ' VALUES(1001, 1, ''Train set'', ''Toys'', 99);' ;
    EXEC (@cmd) ;

This third test will now compile and when we run the tests using EXEC tSQLt.Run 'ViewTests';, all three tests pass:

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name                                                |Result |
+--+--------------------------------------------------------------+-------+
|1 |[ViewTests].[test_OrderDetailView_formats_ProductDescription] |Success|
|2 |[ViewTests].[test_OrderItemView_calculates_correct_value]     |Success|
|3 |[ViewTests].[test_OrderItemView_supplies_default_DispatchDate]|Success|
-----------------------------------------------------------------------------
Test Case Summary: 3 test case(s) executed, 3 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

Interestingly, if you are testing CustomerName or OrderTaker you don’t need to fake [OrderItemView] at all, just add a row to [OrderHeader] and either [Customer] or [Employee] (depending on what you are testing). Because all our joins are redundant, the view (and indeed the tables it refers to) are not required to get the Customer or Employee name. For example:

CREATE PROCEDURE [ViewTests].[test_OrderDetailView_formats_CustomerName_on_all_values_present]
AS
BEGIN
    --! Assemble
    EXEC tSQLt.FakeTable @TableName = 'dbo.OrderHeader' ;
    EXEC tSQLt.FakeTable @TableName = 'dbo.Customer' ;

    INSERT dbo.OrderHeader (OrderId, CustomerId) VALUES (1001, 76) ;

    INSERT dbo.Customer (CustomerId, FirstName, MiddleInitial, LastName)
    VALUES (76, 'Theopholus', 'P', 'Wildebeeste')

    CREATE TABLE #expected (OrderId int NULL, CustomerName varchar(1000) NULL, CustomerId int) ;

    INSERT #expected (OrderId, CustomerName, CustomerId)
    VALUES (1001, 'Wildebeeste, Theopholus P', 76)    

    --! Act
    SELECT
          OrderId
        , CustomerName
        , CustomerId
    INTO
        #actual
    FROM
        dbo.OrderDetailView

    --! Assert
    EXEC tSQLt.AssertEqualsTable #expected, #actual ;
END

You can download the DDL for completed views here and all the tests here.

Comments on this entry are closed.

Previous post:

Next post: