People quite often forget that VSTS and TFS are not just version control systems. Although TFS does version control very well, the real value with VSTS is that it allows the entire team to be integrated.
I haven't spent enough time investigating the best way to write unit tests against the database - I'm sure there are better approaches than mine. What I have done is created unit tests that validate functional requirements of an ETL process. Each unit test is a stored procedure that returns a simple "Pass" or "Fail" in the first row/column and various other information in the other columns, which help identify problems.
Here is a code snippet of what a typical unit test may look like (for me - since I combined the positive and negative scenario). I made the text really small so you have to strain your eyes ;-):
|
ALTER PROCEDURE [dbo].[1_1_3_FilterOwners]
AS
DECLARE @PositiveCount int --Should be > 0
DECLARE @NegativeCount int --Should be 0
DECLARE @Result varchar(20)
Select @PositiveCount = COUNT(*) From wkg_owner o
Where not exists (select 'x' from rim_excluded_owner_list e where e.owner_key = o.OwnerID)
Select @NegativeCount = COUNT(*) From wkg_owner o
Where exists (select 'x' from rim_excluded_owner_list e where e.owner_key = o.OwnerID)
SELECT @Result =
CASE (@PositiveCount)
WHEN 0 THEN 'Fail'
ELSE 'Pass'
END
SELECT @Result =
CASE (@NegativeCount)
WHEN 0 THEN 'Pass'
ELSE 'Fail'
END
Select @Result 'Result', @PositiveCount 'Valid Owner Count',@NegativeCount 'Invalid Owner Count'
|
Here is an example of the results of this test:
So as you can see, I have a positive and negative scenario tested in each procedure, and if one fails the entire test fails. I realize some people would argue that this should be split into two tests - perhaps that is the best approach. The reason I decided against that is because I have over 100 tests to run and I don't want to double my work with little gain. I also named my store procedures so that they match functional requirement IDs.
I didn't really need to create stored procedures to do what I am going to show you. I could have simply embedded SQL into my Database tests. However, I chose against this because I want someone without the Test Edition tools or Visual Studio for that matter to have the ability to run my tests (if someone knows something I don't know regarding this, please post a comment). All someone must do is execute the test in the query tool and get a Pass or Fail.
I however am engaged in formal testing and I want to track not only the result of this test, but the results of many. And I want to store the results for reporting. I also want to be able to easily assign bugs to other developers (in this case of SSIS packages) that related back to the test. This way they can easily track what went wrong, how to repeat it, and finally have a work item so that the issue doesn't fall off of the radar.
So let's add our first database test! First, you will need to add a new Test Project to your solution. You will need to have the VSTS Database GDR Edition and Test Edition installed. The GDR is now included as part of VSTS Developer Edition. Thank you Microsoft!
In my case, I already have a Database project added to the solution which is where all of my stored procedures used for testing are stored. I'll get more into Database Edition in another blog, but to suffice it to say it is the source of truth for me (not SQL Server). I have every database object stored in VSTS. If you aren't using the feature found in Database Edition you are wasting your time and your employer or client's money. I feel that strongly about the tool. Soap Box Over.
Next, you will want to add a new Database Unit Test.
The first time you do this, you will see a wizard that allows you to setup your initial database connection. Select the database that contains your stored procedures (unit tests).
This the creates a DatabaseSetup class and populates app.config with my connection strings, as well as the unit test itself which I changed from the default of DatabaseUnitTest1 to Owners because that is what I am testing.
Now, time for a simple test. First, let's talk about test types. You will have the option to test against empty results, inconclusive results, row counts, rows returned, or scalar values. In my tests, I am testing against scalar values. I added each of the test types so I could show you what they do below:
I don't really need to test against all of these types. My test is to verify that I get a "Pass" value returned in the first column of the first row. So, I delete all those tests above (for the demo) and add a new test called FilterOwnerTest1:
All I do now is add the SQL that I want to test against. Once again, in our case it is a store procedure called 1_1_3_FilterOwners. And I add a scalar value to be tested.
Next, I want to set the conditions for this test.
The only property I modified was the Expected Value and I set it to Pass since that is what I am expecting. You can see that it is looking for the first result set (so if you have multiple queries, no problem), the first row, and the first column. All I do now is save my test, and run it. I can locate my test in the test view window. I simply select it, and hit the run button.
And lucky us, our test passes!
If I had just one test to execute, I wouldn't bother with this. However, I have many tests. And I want to publish the results for reporting. I even have different test types, and a requirement for ordered tests. Fortunately, Test Edition allows me to do this. If you go to Test --> Windows --> Test List Editor you can manage and categorize your tests.
I have already added some of the tests, as you can see above. And if I execute the tests, I can run them all in batch:
I have work to do! If this were a test I wanted to keep a record of, I would publish it by clicking the publish button in the Test Results window.
And, since we have failing tests, I can go ahead and assign it to a developer. I do that by right clicking the test in question, going to the Work Item option, and selecting the type of work item we want to create. I my case, I selected bug. This brings up the work item in our window, where I will assign the bug to myself (or any team member):
I can continue on with my testing, but I will always have this bug in my list of work items until I close it. Suppose a week from now I (or the developer assigned this task) starts working on the bug. They will already have basic information attached without me doing a thing. For example, they will have a link to the test results as well as bug details/history:
As you can see, VSTS is more than just a version control tool. It is a collaboration tool. And it is only going to get better in VSTS 2010.
I hope this blog posting has been helpful and gives you some ideas on testing the database! I will be presenting on VSTS Test Edition at the Orlando Code Camp this year, as well as a Firestarter event in Tampa. Visit orlandocodecamp.com to register for Code Camp or visit Joe Healy's website at http://www.devfish.net/fullblogitemview.aspx?blogid=635 to find out more on the firestarter (March 10th, 2009). Here is a bit about my topic:
Visual Studio Team System 2008 Test Edition. Level: 300. Speaker: Roy Lawson This session provides an overview of testing tools common to all Visual Studio Team System editions and focuses on the unique features of the Test Edition, such as automated testing of web applications, automated performance and stress testing, load test analysis, manual testing and test management.
Pasted from <http://www.devfish.net/fullblogitemview.aspx?blogid=635>