Setup method?
simonjmartin
Posts: 15
Is SetUp deprecated?
I thought tSQLt allowed a [Schema].[SetUp] sproc which would run before all tests in the schema, but I have been unable to get this working.
How do I run common set up code, for example setting up Expected and Actual tables?
I thought tSQLt allowed a [Schema].[SetUp] sproc which would run before all tests in the schema, but I have been unable to get this working.
How do I run common set up code, for example setting up Expected and Actual tables?
Comments
I blogged about it a while back Unit Testing Databases with tSQLt Part 6 – testing a SELECT stored procedure under the heading "Automatic Setup".
If that doesn't help, perhaps you could post your code.
"Be wary of strong drink. It can make you shoot at tax collectors…and miss" Robert Heinlein
blog: http://datacentricity.net
twitter: @datacentricity
I was trying to refactor my Expected and Actual table creation to the SetUp method but after doing so all my tests are broken and the output shows that the [BoardSummary].[Actual] table does not exist... there are other similar failures on all my tests. It looked to me like the method simply wasn't being called. The class I've got for all my tests is BoardSummary, I created a new test in the class then renamed it to SetUp so I'm confident it is in the right class - but either I've massively mucked up the setup method or it isn't being called before the tests... I'm not sure.
What would you recommend I try?
-- Comments here are associated with the test. -- For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/ ALTER PROCEDURE [BoardSummary].[test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges] AS BEGIN -- Arrange IF OBJECT_ID('Actual') IS NOT NULL DROP TABLE Actual IF OBJECT_ID('Expected') IS NOT NULL DROP TABLE Expected DECLARE @AreaID INT DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME DECLARE @ProviderList VARCHAR(150) DECLARE @RootReportLevelID INT SET @AreaID = 374 -- Leicester City PCT SET @StartDate = '2012-01-01 00:00:00' SET @EndDate = '2012-12-31 23:59:59' SET @ProviderList = N'TEST|' SET @RootReportLevelID = 380 -- The SHA to constrain results to just this area INSERT INTO tblPROMsPreferredProviders ( ShortName , FullName , FKHeaderFilterID , SUSCode , Preferred , Report , FKHeaderID ) VALUES ( 'TEST' , -- ShortName - varchar(50) 'TEST PROVIDER' , -- FullName - varchar(200) 513 , -- FKHeaderFilterID - int 'TEST' , -- SUSCode - varchar(5) 1 , -- Preferred - bit 2 , -- Report - int 71 -- FKHeaderID - int ) DECLARE @prmID INT -- Add the Highest scorecard EXEC apSaveUploadedScoreCardDetailsNew 'Leicester City PCT', -- varchar(100) '2012-07-09 08:24:46', -- datetime 'TEST', -- varchar(150) 'TEST', -- varchar(150) 185, -- int 'TEST', -- varchar(150) 1, -- int 'TEST', -- text '', -- text '', -- text '', -- text 1, -- bit 1, -- bit 1, -- bit @prmID OUT -- int EXEC apSaveUploadedScoreCardResults @prmUploadedID = @prmID, -- int @prmRating = 'T', -- char(3) @prmComments = '', -- text @prmQuestionID = 3448, -- int @prmMaxValue = 100, -- int @prmContribution = 90, -- int @prmGroupID = 217, -- int @prmResponseID = 527 -- int EXEC apAddUploadedScorecardHeader @prmScorecardHeaderID = 71, -- int @prmUploadedScorecardID = @prmID, -- int @prmHeaderText = 'TEST PROVIDER' -- text -- Add the Lowest scorecard EXEC apSaveUploadedScoreCardDetailsNew 'Leicester City PCT', -- varchar(100) '2012-07-09 08:24:46', -- datetime 'TEST', -- varchar(150) 'TEST', -- varchar(150) 185, -- int 'TEST', -- varchar(150) 1, -- int 'TEST', -- text '', -- text '', -- text '', -- text 1, -- bit 1, -- bit 1, -- bit @prmID OUT -- int EXEC apSaveUploadedScoreCardResults @prmUploadedID = @prmID, -- int @prmRating = 'T', -- char(3) @prmComments = '', -- text @prmQuestionID = 3448, -- int @prmMaxValue = 100, -- int @prmContribution = 35, -- int @prmGroupID = 217, -- int @prmResponseID = 527 -- int EXEC apAddUploadedScorecardHeader @prmScorecardHeaderID = 71, -- int @prmUploadedScorecardID = @prmID, -- int @prmHeaderText = 'TEST PROVIDER' -- text CREATE TABLE Expected ( GreenUpper DECIMAL(5,2) , GreenLower DECIMAL(5,2) , AmberUpper DECIMAL(5,2) , AmberLower DECIMAL(5,2) , RedUpper DECIMAL(5,2) , RedLower DECIMAL(5,2) ) INSERT INTO Expected VALUES ( 90, 80, 80, 60, 60, 35 ) --Act DECLARE @Exec TABLE ( GroupedBy VARCHAR(150) , PCTMax DECIMAL(5,2) , PCTAvg DECIMAL(5,2) , PCTMin DECIMAL(5,2) , AllAvg DECIMAL(5,2) , GreenUpper DECIMAL(5,2) , GreenLower DECIMAL(5,2) , AmberUpper DECIMAL(5,2) , AmberLower DECIMAL(5,2) , RedUpper DECIMAL(5,2) , RedLower DECIMAL(5,2) ) INSERT INTO @Exec ( GroupedBy , PCTMax , PCTAvg , PCTMin , AllAvg , GreenUpper , GreenLower , AmberUpper , AmberLower , RedUpper , RedLower ) EXEC apPROMsBoardSummaryReport @AreaID, @StartDate, @EndDate, @ProviderList, @RootReportLevelID CREATE TABLE Actual ( GreenUpper DECIMAL(5,2) , GreenLower DECIMAL(5,2) , AmberUpper DECIMAL(5,2) , AmberLower DECIMAL(5,2) , RedUpper DECIMAL(5,2) , RedLower DECIMAL(5,2) ) INSERT INTO Actual ( GreenUpper , GreenLower , AmberUpper , AmberLower , RedUpper , RedLower ) SELECT GreenUpper , GreenLower , AmberUpper , AmberLower , RedUpper , RedLower FROM @Exec AS E WHERE GroupedBy = 'TEST' --Assert -- For a complete list, see: http://tsqlt.org/user-guide/assertions/ EXEC tSQLt.AssertEqualsTable @Expected = N'Expected', -- nvarchar(max) @Actual = N'Actual', -- nvarchar(max) @FailMsg = N'Ranges for the values' -- nvarchar(max) END;any advice on how to keep it DRY would be appreciated. As I said the first thing I was trying to do was refactor out the Actual and Expected tables as I use them in all the other tests in this class
I Refactored to this (with and without a 'test' prefix to SetUp:
and for the test:
-- Comments here are associated with the test. -- For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/ ALTER PROCEDURE [BoardSummary].[test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges] AS BEGIN -- Arrange DECLARE @AreaID INT DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME DECLARE @ProviderList VARCHAR(150) DECLARE @RootReportLevelID INT SET @AreaID = 374 -- Leicester City PCT SET @StartDate = '2012-01-01 00:00:00' SET @EndDate = '2012-12-31 23:59:59' SET @ProviderList = N'TEST|' SET @RootReportLevelID = 380 -- The SHA to constrain results to just this area INSERT INTO tblPROMsPreferredProviders ( ShortName , FullName , FKHeaderFilterID , SUSCode , Preferred , Report , FKHeaderID ) VALUES ( 'TEST' , -- ShortName - varchar(50) 'TEST PROVIDER' , -- FullName - varchar(200) 513 , -- FKHeaderFilterID - int 'TEST' , -- SUSCode - varchar(5) 1 , -- Preferred - bit 2 , -- Report - int 71 -- FKHeaderID - int ) DECLARE @prmID INT -- Add the Highest scorecard EXEC apSaveUploadedScoreCardDetailsNew 'Leicester City PCT', -- varchar(100) '2012-07-09 08:24:46', -- datetime 'TEST', -- varchar(150) 'TEST', -- varchar(150) 185, -- int 'TEST', -- varchar(150) 1, -- int 'TEST', -- text '', -- text '', -- text '', -- text 1, -- bit 1, -- bit 1, -- bit @prmID OUT -- int EXEC apSaveUploadedScoreCardResults @prmUploadedID = @prmID, -- int @prmRating = 'T', -- char(3) @prmComments = '', -- text @prmQuestionID = 3448, -- int @prmMaxValue = 100, -- int @prmContribution = 90, -- int @prmGroupID = 217, -- int @prmResponseID = 527 -- int EXEC apAddUploadedScorecardHeader @prmScorecardHeaderID = 71, -- int @prmUploadedScorecardID = @prmID, -- int @prmHeaderText = 'TEST PROVIDER' -- text -- Add the Lowest scorecard EXEC apSaveUploadedScoreCardDetailsNew 'Leicester City PCT', -- varchar(100) '2012-07-09 08:24:46', -- datetime 'TEST', -- varchar(150) 'TEST', -- varchar(150) 185, -- int 'TEST', -- varchar(150) 1, -- int 'TEST', -- text '', -- text '', -- text '', -- text 1, -- bit 1, -- bit 1, -- bit @prmID OUT -- int EXEC apSaveUploadedScoreCardResults @prmUploadedID = @prmID, -- int @prmRating = 'T', -- char(3) @prmComments = '', -- text @prmQuestionID = 3448, -- int @prmMaxValue = 100, -- int @prmContribution = 35, -- int @prmGroupID = 217, -- int @prmResponseID = 527 -- int EXEC apAddUploadedScorecardHeader @prmScorecardHeaderID = 71, -- int @prmUploadedScorecardID = @prmID, -- int @prmHeaderText = 'TEST PROVIDER' -- text INSERT INTO [BoardSummary].Expected VALUES ( 90, 80, 80, 60, 60, 35 ) --Act DECLARE @Exec TABLE ( GroupedBy VARCHAR(150) , PCTMax DECIMAL(5,2) , PCTAvg DECIMAL(5,2) , PCTMin DECIMAL(5,2) , AllAvg DECIMAL(5,2) , GreenUpper DECIMAL(5,2) , GreenLower DECIMAL(5,2) , AmberUpper DECIMAL(5,2) , AmberLower DECIMAL(5,2) , RedUpper DECIMAL(5,2) , RedLower DECIMAL(5,2) ) INSERT INTO @Exec ( GroupedBy , PCTMax , PCTAvg , PCTMin , AllAvg , GreenUpper , GreenLower , AmberUpper , AmberLower , RedUpper , RedLower ) EXEC apPROMsBoardSummaryReport @AreaID, @StartDate, @EndDate, @ProviderList, @RootReportLevelID INSERT INTO [BoardSummary].Actual ( GreenUpper , GreenLower , AmberUpper , AmberLower , RedUpper , RedLower ) SELECT GreenUpper , GreenLower , AmberUpper , AmberLower , RedUpper , RedLower FROM @Exec AS E WHERE GroupedBy = 'TEST' --Assert -- For a complete list, see: http://tsqlt.org/user-guide/assertions/ EXEC tSQLt.AssertEqualsTable @Expected = N'Expected', -- nvarchar(max) @Actual = N'Actual', -- nvarchar(max) @FailMsg = N'Ranges for the values' -- nvarchar(max) END;and this returns me the following: [/quote]
I'm glad my tutorials have inspired you. The difficulty is that once you start using TDD, you'll struggle to work without it.
NB: Odd, I didn't see the refactored code when I first looked
Looking at the example code you posted, I'm assuming that this is before refactoring creation of the actual and expected table out into the setup method. The most obvious thing to check is that you also remove the DROP TABLE statements at the beginning of [BoardSummary].[test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges]. In fact arguably the DROPS aren't really required since the CREATES get rolled back at the end of each test. It's my OCD that makes me do it in my code :-)
If that's not the problem, try creating a [BoardSummary].[SetUp] that just does RAISERROR('Setup called!', 0, 1) WITH NOWAIT; then run just one test tSQL.Run '[BoardSummary].[test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges]' in another window to see if SetUp does actually get called. Then build things up from there.
Another thing to check (although a longshot) if you happen to be running on a case-sensitive database is that you are running the latest version of the tSQLt framework SELECT * FROM tSQLt.Info() should yield 1.0.4504.21220. There was a small issue in an earlier version WRT case-sensitivity of the SetUp call. Mind you if you've installed it via SQL Test this shouldn't be a problem.
If these suggestions don't work, let me know and I'll see what else I can come up with
"Be wary of strong drink. It can make you shoot at tax collectors…and miss" Robert Heinlein
blog: http://datacentricity.net
twitter: @datacentricity
I notice that your creating the actual and expected tables in the [dbo] schema. It is a better idea to create these tables in the same schema as the test suite.
But you are refencing them as if they are in the [BoardSummary] schema in your test:
INSERT INTO [BoardSummary].Expected
VALUES ( 90, 80, 80, 60, 60, 35 )
INSERT INTO [BoardSummary].Actual
( GreenUpper ,
GreenLower ,
AmberUpper ,
AmberLower ,
RedUpper ,
RedLower )
SELECT GreenUpper ,
GreenLower ,
AmberUpper ,
AmberLower ,
RedUpper ,
RedLower
FROM @Exec AS E
WHERE GroupedBy = 'TEST'
"Be wary of strong drink. It can make you shoot at tax collectors…and miss" Robert Heinlein
blog: http://datacentricity.net
twitter: @datacentricity
In terms of achieving DRY, you do seem to have a lot of setup here, And that setup seems to be calling production procedures.
Obviously I don't know about the underlying logic but if it were me, I would ask myself whether it really is necessary to call all those, or even any of them.
If I understand this correctly, the procedure under test is apPROMsBoardSummaryReport.
I am presuming that this manipulates data from a number of tables (presumably populated by the previous procedure calls).
If I wanted to isolate this test from everything else, I wopuld endeavour to just populate the tables and columns that are required for this one test.
As a simple example, if I had an [OrderDetail] table with [Price] and [Quantity] columns, and the procedure was supposed to generate a [Cost] column, then at the simplest level these are the only columns that need to be populated to fulfil that test. Only populate the tables columns that are essential to the test, use tSQLt.FakeTable to achieve this where necessary.
If you do use tSQLt.FakeTable, all columns on the mock table are defined as nullable - precisely to allow you to populate only the columns required for the test. Where this really helps is when another NOT NULL column is added to [OrderDetail], your existing tests won't break because they're not populating that column.
Test Driven Development requires a different mindset, look for ways to break functionality down to the lowest common denominator.
If you need three or four tables to contain data in order to test apPROMsBoardSummaryReport, than add rows to those directly. As it stands, if the test you demonstrate here fails, how do you know where the failure is? Looking at your test, it could just as easily be in apSaveUploadedScoreCardDetailsNew, apSaveUploadedScoreCardResults or apAddUploadedScorecardHeader. Those should be subject to their own tests that assert they do everything they should. Otherwise, what you have here is an integration test - which is still useful but much harder to identify the cause of any failure.
There is pattern called Test Data Builder used in the .Net and Java world that can be used to simplify this kind of set up. For example Adapting the Test Data Builder Pattern for T-SQL. Using TDB, you could write a procedure that might take all the inputs from the production procedure calls you are making and puts the correct values in the appropriate tables and columns. Then you have just one procedure call which can be reused in other tests passing different values.
Even if the production procedures you are calling in this test are douing complex calculations, the end result is probably a set of numbers in some rows in some tables - it is those simple numbers you need to set up for this test - not all the intevening logic.
If you're familiar with DRY, you will also know about SRP - Single Responsibility Principle.
Sorry if this reply goes on a bit but this is a complex subject. TDD really does change how you look at code - resulting in more robust, better designed code. We just have to get through that learning curve first :-)
"Be wary of strong drink. It can make you shoot at tax collectors…and miss" Robert Heinlein
blog: http://datacentricity.net
twitter: @datacentricity
The version is returned as: 1.0.4504.21220
I like OCD coding, fewer things can bite you in the rear
The PROMsBoardSummaryReport procedure is aggregating data from the tables I'm adding to above. There are constraints on those tables (foreign key etc) so faking the tables and only loading the minimum required data for the test is definitely where I'm aiming for.
Then I was hoping to look into the Test Data Builder to further clean up the setup for these tests, but its small baby steps for me at the moment. Which is all good because once I've stumbled through things I can give a good presentation to my team.
I definitely appreciate the help and depth of the reply, when trying to learn something new all the information comes in handy. It's getting over the gap between hello world and real world that's the big hurdle and having someone who knows what's what help out is invaluable
Ok so we know that [SetUp] is being called. And, if you've corrected the schema references, we need to find out whether the table is actually created and if it is, at point it disappears again.
Try adding this to the end of your SetUp procedure:
IF OBJECT_ID(N'BoardSummary.Actual') > 0
RAISERROR('SetUp: [Actual] table exists in BoardSummary schema', 0, 1) WITH NOWAIT;
ELSE IF OBJECT_ID(N'dbo.Actual') > 0
RAISERROR('SetUp: [Actual] table not found in BoardSummary schema but deos exist in dbo schema', 0, 1) WITH NOWAIT;
ELSE
RAISERROR('SetUp: [Actual] table not found in BoardSummary schema', 0, 1) WITH NOWAIT;
Then add this at the top of the test procedure IF OBJECT_ID(N'BoardSummary.Actual') > 0
RAISERROR('In Test: [Actual] table exists in BoardSummary schema', 0, 1) WITH NOWAIT;
ELSE
RAISERROR('In Test: [Actual] table not found in BoardSummary schema', 0, 1) WITH NOWAIT;
and then add this just before you try to insert the Actual table:
IF OBJECT_ID(N'BoardSummary.Actual') > 0
RAISERROR('Before Assert: [Actual] table exists in BoardSummary schema', 0, 1) WITH NOWAIT;
ELSE
RAISERROR('Before Assert: [Actual] table not found in BoardSummary schema', 0, 1) WITH NOWAIT;
Then just run the one test with tSQLt.Run and see what the output is.
Also, you were correct in naming the set-up procedure [SetUp]. tSQLt looks for this before running each test. If you'd left it as [test SetUp], it would have got called once because it was treated as just a regular test.
Also, in case it wasn't clear earlier, the reason why I recommend putting the [actual] and [expected] tables in the test schema rather than dbo is because one day you might actually be testing a database that has a table named like that.
"Be wary of strong drink. It can make you shoot at tax collectors…and miss" Robert Heinlein
blog: http://datacentricity.net
twitter: @datacentricity
I think I've just spotted your issue. It's difficult to see on the web page without sysntax highlighting and I've only just seen it on a another review of the code.
When you call AssertEqualsTable, if your expected and actual tables are in a schema other than dbo (which they are), you have to specify the schema name too.
"Be wary of strong drink. It can make you shoot at tax collectors…and miss" Robert Heinlein
blog: http://datacentricity.net
twitter: @datacentricity
FakeTable itself is pretty reliable in my experience, but schema-level tasts can introduce some interesting questions around how we do certain types of test.
"Be wary of strong drink. It can make you shoot at tax collectors…and miss" Robert Heinlein
blog: http://datacentricity.net
twitter: @datacentricity
That was it! The assertion wasn't using the right schema :oops:
As with other users, specifying the schema correctly in all references is a common typo/gotcha.
Are you running against a case-sensitive instance/database? There were a couple of historic bugs around casing for the Setup procedure name - fixed in different versions.
Otherwise, try posting your code and we'll see if we can help
"Be wary of strong drink. It can make you shoot at tax collectors…and miss" Robert Heinlein
blog: http://datacentricity.net
twitter: @datacentricity