In the previous post on unit testing, I tried to demonstrate how a bug can be created in a pretty innocuous fashion. Unit testing is common in most development platforms, but as always, SQL has to be different. SQL has state – mostly because of the data within the database. If you have some C# code, and you want to completely re-write it, go ahead. As long as the inputs, outputs, and other behaviors are the same, you should be fine. Not so with SQL, which makes unit testing a little trickier.
There are several unit testing frameworks out there. The one I have been using is tSQLt. Although it is powerful, the documentation left me lost. In a series of articles, I would like to demonstrate how to set up tSQLt, and how to set up a basic test. I will then go into some more details about building a test framework of many tests, and ways to automate the testing, hopefully elaborating on the documentation as I go along.
Installing tSQLt is easy. You first download it from http://sourceforge.net/projects/tsqlt/files/. Download the latest version. It will be in a zip file, which contains five files:
Example.sql
License.txt
ReleaseNotes.txt
SetClrEnabled.sql
tSQLt.class.sql
The three text files are self explanatory. The others are only slightly more involved.
SetClrEnabled.sql will enable common language runtime for your database, and changes the TRUSTWORTHY database property to ON. These are requirements for tSQLt. The tSQLt.class.sql script is the actual install script that will add all of the objects required for the test environment to work. And Example.sql will set up a database with some sample tests.
Let’s get started then. First, run SetClrEnabled, then tSQLt.class.sql. OK, were finished.
Really, that is all there is too it.
What do the scripts do? Well, you know should never trust SQL scripts you get off of the Internet, right? So take a look. Weighing in at ~2,300 lines, it is sizable but not huge. It creates the following:
Schema: tSQLt, to contain all of the new objects
User Defined Data Type: tSQLt.Private, which is created from the tSQLtCLR assembly (hence the need to enable CLR above). Stored Procedures: you will get 57 stored procedures installed. These are the guts of the framework. They provide the functionality to create tests, compare results, etc. The total count for the various objects are:
CLR_STORED_PROCEDURE 5
PRIMARY_KEY_CONSTRAINT 3
SQL_INLINE_TABLE_VALUED_FUNCTION 19
SQL_SCALAR_FUNCTION 10
SQL_STORED_PROCEDURE 57
SQL_TRIGGER 1
USER_TABLE 6
VIEW 2
Now, you will notice that I installed these in an empty database. That was just to demonstrate the setup. Usually, you would perform this on a test database of some sort.
Let’s test out the example I worked with previously. I will create the same original table, and populate it. I will then create the stored procedure for changing an email address. After that, I will set up a test using tSQLt, show a successful test run, modify the underlying table, and have the test fail.
In order to create your first test, you must do the following:
CREATE A TEST CLASS
To create a test class, you must use the tSQLt.NewTestClass stored procedure. It takes a single parameter, which is the class name. What this procedure actually does is to create a schema named with the class name, and then adds the extended property of tSQLt.TestClass to the new schema. All of the tests you create must be created within a schema created in this fashion.
EXEC tSQLt.NewTestClass 'StudentChanges'
CREATE A TEST PROCEDURE
All tests in tSQLt are created as stored procedures that execute the tests. As mentioned above, these are the guts of the framework. Also, as mentioned above, the procedures must be created within a schema created using the tSQLt.NewTestClass stored procedure. By doing this, all tests are executed within a transaction, that is then rolled back after the test is complete, so no data actually gets changed. Here is a sample test to validate the stored procedure created in the last post.
CREATE PROCEDURE StudentChanges.[Test updateEmail procedure]
AS
BEGIN
DECLARE @StudentID INT = 5;
DECLARE @newEmailAddress VARCHAR(100) = '[email protected]';
DECLARE @expected VARCHAR(100) = '[email protected]';
DECLARE @actual VARCHAR(100)
------Fake Table
EXEC tSQLt.FakeTable 'dbo.Students'--, @Identity = @StudentID;
INSERT INTO dbo.Students
( StudentID ,
[StudentFN] ,
[StudentLN] ,
[StudentDOB] ,
[studentEmail] ,
[StudentCell]
)
VALUES ( @StudentID ,
'Jack' ,
'White' ,
'1958-03-25' ,
'[email protected]' ,
NULL
)
EXEC usp_UpdateEmail @StudentID, @newEmailAddress
SET @actual = ( SELECT studentEmail
FROM dbo.Students
WHERE StudentID = @StudentID
)
------Assertion
EXEC tSQLt.assertEquals @expected, @actual;
END;
GO
Looking at this section by section, we start with basic variable declarations. After that, we see one of the new tSQLt stored procedures, tSQLt.FakeTable. A look at the behavior of this stored procedure gives some insight to the power of the framework. You can create a pseudo-table that your test will utilize, so you can always maintain your test data. In other words, should the actual data in the underlying tables change, your tests will still be valid. This is a great feature. Next, we populate the test data with a standard insert statement. If you look closely you can see that we are populating the StudentID value into the pseudo table. The behavior of identity columns can also be modified with additional parameters for the tSQLt.FakeTable procedure.
Once we have some data in the fake table, we execute our test procedure. We then set the result equal to a variable that we use as a parameter of another tSQL stored procedure, tSQLt.assertEquals. This is the actual test condition, where the result of the stored procedure is compared to the expected result.
RUN THE TEST
To actually run the test, execute the tSQLt.Run stored procedure. If you just specify the test schema, it will runn all tests in the test schema. If you execute tSQLt.RunAll, every test in every test schema will be performed. We only have one test at this point, so here is the command:
exec tSQLt.Run 'StudentChanges.[Test updateEmail procedure]'
Now when we look at the results, we see the following:
+----------------------+ |Test Execution Summary| +----------------------+ |No|Test Case Name |Result | +--+---------------------------------------------+-------+ |1 |[StudentChanges].[Test updateEmail procedure]|Success| ----------------------------------------------------------------------------- Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored. -----------------------------------------------------------------------------
Our test succeeded. Now, what if I change the underlying column definition like I did in the previous post? As a reminder, the change was a column rename.
EXEC sp_rename 'dbo.Students.studentEmail', 'Email', 'COLUMN';
We see the following:
[StudentChanges].[Test updateEmail procedure] failed: Invalid column name 'studentEmail'.{Test updateEmail procedure,13}
+----------------------+
|Test Execution Summary|
+----------------------+
|No|Test Case Name |Result|
+--+---------------------------------------------+------+
|1 |[StudentChanges].[Test updateEmail procedure]|Error |
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 1 test case(s) executed, 0 succeeded, 0 failed, 1 errored.
-----------------------------------------------------------------------------
This is good – we should not see the test succeed. We actually received a test error, not a test failure, but the idea is that something broke because of a change, and we can find out about it before our end users do. This is a very good thing.
In the next article, I will show a few ways to automate the tests!
This is really good explanation on how the FakeTable works. Thank you