Wednesday, May 11, 2016

The tSQLt unit test framework that we use to do our DB tests does not allow for disabled tests, this is because each test is just a stored proc. Right now the only way is to either rename the proc so it doesn’t start with test or to remove the test

I was thinking about this in the shower this morning….here is what I came up with

Here is a quick way how to prevent a test from running when the tSQLt tests run.
Put the code below into each unit test that you want disabled, you should put this code right after the CREATE PROCEDURE ProcName AS part

This code will pick the correct name and schema, so you can use the same exact code in each test

DECLARE@IsTestDisabled bit=1-- set this to 1 if you don't want the test to run
IF @IsTestDisabled =1BEGINDECLARE@SchemaName varchar(1000)
SELECT@SchemaName = QUOTENAME(SCHEMA_NAME(schema_id)) +'.'FROM sys.procedures WHERE object_id =@@procid
PRINT 'Disabled Test: '+@SchemaName + QUOTENAME(OBJECT_NAME(@@procid))
RETURNEND

Monday, January 25, 2016

Whenever I put the tSQLt database on a server, I make sure that I make one change to a stored procedure that ships with tSQLt. The reason for this is that we run our tSQLT tests as part of our automated build process. If any of the tests fail, we developers who are part of that particular project will get an email telling us what the results of the tests were.

The main proc that runs the unit tests always returns 0, I need a way to know if any tests failed or not, if we had failures, we want the email to go out, if there were no failures then we don't want the email to go out.

I added the stuff on lines 9 till 11. I am checking if there were any tests that failed, these tests will have a result of Failure. If any tests failed, the stored procedure will return -1. Now we can easily check this value to determine if we should send the email or not.

I could have also checked if there were any tests which had a result which was not Success instead. A test could also have a status which is Error, you will get such a status if for example an object doesn't exist in the database.

There you have it, this is the change I made. Maybe you had the same problem and this will help you as well.

Nope, only two out of five tests executed.
So what happened, why are only two of the five tests running?
The reason is that each test has to be named so that the first part of the name is test. We created the following four test stored procedures

As you can see the first two are not named so that the name starts with test. Renaming those so that they start with test will fix that. I myself have made this mistake many times in the beginning, hopefully this will save someone some time in the future

What about the 5th proc why didn't that run?

CREATEPROCEDURE [test ObjectExist]

As you can see that proc is named correctly, however it is not in the two test classes that we created. It is created in the default class which most likely will be dbo, You can actually execute this by calling the dbo class

In today's post we will install tSQLt and run a simple test. To start download tSQLt, unzip the zipfile into its own folder. Here is what is inside that zip file, we are looking at executing the tSQLt.class.sql file later on

Open up SSMS, connect to the database server where you want to create the tSQLt database. We need to enable the CLR on the server where you are running your tests because tSQLt is using some CLR functionality.

Execute the following command to enable the CLR on your box

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

Now we need to create the database and make it trustworthy, execute the following

I want to point something important out. Comparing two tables is usually doing for small lookup tables, you create a table inside your unit test with values that you expect to match the table in the database, if these tables don't match, then you have a problem. You wouldn't want to compare two million row tables in a test.

Let's do another assertion, let's see if an table exists.

EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable'

Msg 50000, Level 16, State 10, Procedure Fail, Line 51tSQLt.Failure

That fails as expected, now let's try it with a table that exists

EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.test1'

That ran fine as expected.

Let's clean up by dropping the two tables we created earlier.

DROPTABLE dbo.test1 , dbo.test12

Creating a test class
What you want to do with tSQLt is group all your tests together, for example if you are testing a stored procedure, you can execute just those set of tests. When creating a test class in tSQLt, a new schema will be created, you can easily verify this. Execute the following

USE tSQLt
GOSELECT*FROM sys.schemas
WHERE schema_id BETWEEN5AND1000

nameschema_idprincipal_idtSQLt 51

As you can see, the only schema the query returns is the tSQLt schema. Now let's create a new test class, you do this by executing the NewTestClass stored procedure, if we wanted to create a test class named WebFlags, we would do it like this

tSQLt.NewTestClass 'WebFlags'

Running the following code again, you will now see an additional schema in the output

USE tSQLt
GOSELECT*FROM sys.schemas
WHERE schema_id BETWEEN5AND1000

nameschema_idprincipal_idtSQLt 51WebFlags61

Let's create a couple of test stored procedures with the examples we used before

Now we will create 3 stored procedures. It is very important that you name each stored procedure beginning with 'test ', if you don't do this then tSQLt will skip the proc. Here is the code to create our 3 stored procedures

tSQLt allows you to implement unit tests in T-SQL. This is important as you do not have to switch between various tools to create your code and your unit tests. tSQLt also provides the following features to make it easier to create and manage unit tests:

Tests are automatically run within transactions – this keeps tests independent and reduces any cleanup work you need

Tests can be grouped together within a schema – allowing you to organize your tests and use common setup methods

Output can be generated in plain text or XML – making it easier to integrate with a continuous integration tool

Provides the ability to fake tables and views, and to create stored procedure spies – allowing you to isolate the code which you are testing

So basically after you install tSQLt on your SQL Server instance, it is just a database, you now have a unit test framework that you can use. They way you use tSQLt is by creating stored procedures that will do the unit testing for you. It all runs within SQL Server.

You can run 1 test, all tests in a test class or all tests for all test classes by calling a single stored procedure. Here are the Assertions and Expectations that ship with tSQLt

Assertions

AssertEmptyTable

AssertEquals

AssertEqualsString

AssertEqualsTable

AssertLike

AssertNotEquals

AssertObjectDoesNotExist

AssertObjectExists

AssertResultSetsHaveSameMetaData

Fail

Expectations

ExpectException

ExpectNoException

In addition to that you can of course use IF EXISTS..... and then a Fail Assertions to test anything that is not testable with the built in Assertions

In addition to be able to run this all from a query window in SSMS, if you have Red Gates SQL Test then you can use that to run the tests, you will get a graphical indicator at that point. If the test passes it will be green, if it fails it will be red.

In the next post, we will take a look at how to install tSQLt on your database instance and write our first test.

Wednesday, September 23, 2015

Test on an individual unit of code.
A unit test should ideally only test one discrete unit of code, there should not be a test for example how this unit of code reacts with other units of code. Tests should not be influenced by other units.

Repeatable
Executing a unit test should give you the same result every single time. Since a unit test is repeatable it is also easy to be automated.

Test one thing only
A test should only be testing one thing, it should test one question only. For example if I put money in the account did the balance increase?

Test the requirement for the unit.
If the requirement is that zip codes are 5 digits for example then you should have tests that verify that 5 is correct and for example 4 and 6 will fail the test.

When do I write unit tests?

Before development?
This enables you to focus on requirements, it is part of Agile, it also allows you to develop code structures first

During development?
If requirements change you need to create new unit tests or modify existing unit tests

After development?
You will need to add tests that validate existing code

The answer is of course during all 3 phases!
You will be writing unit tests when you change something in an existing application, start a new application or if you are in the middle of development for a soon to be launched application. The only difference is that with a new application you will write the test before the code and with an existing application that you will write the unit test after the code already exists.

Why are you not unit testing yet?
I asked this question and here are some of the answers I got...I hope the last one is a joke :-)

Don’t know where to startNo timeNo benefitOur customer do our unit testing for us

Hopefully once you are done reading all these posts, these answers should not prevent you from starting to do unit testing

I have been using tSQLt to do unit testing within SQL Server for the last 2 years. I already did a presentation twice at work and decided to document some of this so that it might help other people looking at using tSQLt to do unit testing with SQL Server.

This will be a multi part series, some of the stuff I will cover:

What is unit testing and why you should be unit testing
What tSQLt is and how to get started
Sample of all the different functionality that tSQLt provides
How to integrate tSQLt within your builds

Before starting, let me first tell you what happens when something is checked in into out source code repository.
1) The applications are build
2) Integration tests are run
3) Unit tests are run
4 If there were any errors an email is sent to the developers with the output of the failed tests or compilation errors

If there are errors, tSQLt will give you the name and the error for each test as well have a summary which looks something like the following

I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you"