T.S.T. the T-SQL Test Tool

Updated Release: Version 1.5 (Mar 17, 2010)

lmolnar — Thu, 18 Mar 2010 13:18:38 GMT

Version 1.5 changes:

Bug fix. In V1.4 and earlier table comparison failed if the tables compared had columns with spaces in them.

Released: Version 1.5 (Mar 17, 2010)

Thu, 18 Mar 2010 13:18:38 GMT

Version 1.5 changes:

Bug fix. In V1.4 and earlier table comparison failed if the tables compared had columns with spaces in them.

Updated Wiki: Documentation

lmolnar — Wed, 17 Mar 2010 08:15:49 GMT

For complete documentation including a Quick Start section and a video introduction see click on the Home tab.

Updated Wiki: Home

lmolnar — Wed, 17 Mar 2010 08:11:25 GMT

Welcome to T.S.T.

TST is a tool that simplifies writing and running automated tests for code written in T-SQL. At the heart of the TST tool is the TST database. This database contains a series of stored procedures that represent a test API. Part of this API is similar with those found in Unit Testing libraries familiar to programmers in C# or Java.

Feature Highlights

Support for validating views as well as stored procedures and functions that return a table.
A reliable implementation of Assert.Equals / Assert.NotEquals procedures that detect when a comparison should not be made due to incompatibility of the data types. Additional procedures like Assert.NumericEquals, Assert.NumericNotEquals, Assert.FloatEquals, Assert.FloatNotEquals are provided.
Ability to run concurrent test sessions against the same or different databases.
Ability to produce XML results.
Automatic rollback of the database changes done during each test.
TST will detect when its own rollback mechanism becomes ineffective in some scenarios that involve testing code that uses transactions. The TST rollback can be disabled at the test, suite or global level.
No need to register the test stored procedures; they are picked up automatically by the TST test runners.
Can be run from the command prompt or directly by calling one of the test runner stored procedures.
The TST infrastructure is isolated in a separate database.
Easy to install and integrate with other test frameworks.
Does not have any dependencies besides SQL Server.
Very small learning curve for someone already familiar with T-SQL.

Current release: Version 1.5

Related project on CodePlex:
Managed Code Interface for T-SQL Test Tool http://www.codeplex.com/MCI4TST.

Complete documentation including a Quick Start section

HTML format: TST.mht
Word 2007 file: TST.docx

7 minutes video demo: http://www.youtube.com/watch?v=bLtK20bl7G4

Author's home page and other projects: http://www.ladimolnar.com.

The TST API

Assert.LogInfo
Assert.Pass
Assert.Fail
Assert.Equals
Assert.NotEquals
Assert.NumericEquals
Assert.NumericNotEquals
Assert.FloatEquals
Assert.FloatNotEquals
Assert.IsLike
Assert.IsNotLike
Assert.IsNull
Assert.IsNotNull
Assert.TableEquals
Assert.IsTableEmpty
Assert.RegisterExpectedError

Runner.RunAll
Runner.RunSuite
Runner.RunTest

Utils.SetConfiguration

Updated Release: Version 1.5 (Mar 17, 2010)

lmolnar — Wed, 17 Mar 2010 08:11:01 GMT

Version 1.5 changes:

Fix a bug. In V1.4 and earlier table comparison failed if the tables compared had columns with spaces in them.

Released: Version 1.5 (Mar 17, 2010)

Wed, 17 Mar 2010 08:11:01 GMT

Version 1.5 changes:

Fix a bug. In V1.4 and earlier table comparison failed if the tables compared had columns with spaces in them.

Updated Wiki: Home

lmolnar — Wed, 17 Mar 2010 08:10:14 GMT

Support for validating views as well as stored procedures and functions that return a table.
A reliable implementation of Assert.Equals / Assert.NotEquals procedures that detect when a comparison should not be made due to incompatibility of the data types. Additional procedures like Assert.NumericEquals, Assert.NumericNotEquals, Assert.FloatEquals, Assert.FloatNotEquals are provided.
Ability to run concurrent test sessions against the same or different databases.
Ability to produce XML results.
Automatic rollback of the database changes done during each test.
TST will detect when its own rollback mechanism becomes ineffective in some scenarios that involve testing code that uses transactions. The TST rollback can be disabled at the test, suite or global level.
No need to register the test stored procedures; they are picked up automatically by the TST test runners.
Can be run from the command prompt or directly by calling one of the test runner stored procedures.
The TST infrastructure is isolated in a separate database.
Easy to install and integrate with other test frameworks.
Does not have any dependencies besides SQL Server.
Very small learning curve for someone already familiar with T-SQL.

Current release: Version 1.4

Related project on CodePlex:
Managed Code Interface for T-SQL Test Tool http://www.codeplex.com/MCI4TST.

Complete documentation including a Quick Start section

HTML format: TST.mht
Word 2007 file: TST.docx

7 minutes video demo: http://www.youtube.com/watch?v=bLtK20bl7G4

Author's home page and other projects: http://www.ladimolnar.com.

The TST API

Assert.LogInfo
Assert.Pass
Assert.Fail
Assert.Equals
Assert.NotEquals
Assert.NumericEquals
Assert.NumericNotEquals
Assert.FloatEquals
Assert.FloatNotEquals
Assert.IsLike
Assert.IsNotLike
Assert.IsNull
Assert.IsNotNull
Assert.TableEquals
Assert.IsTableEmpty
Assert.RegisterExpectedError

Runner.RunAll
Runner.RunSuite
Runner.RunTest

Utils.SetConfiguration

New Post: Running UnitTests in multiple databases

JohnMayo — Thu, 11 Feb 2010 02:04:17 GMT

Is there a way to run UnitTests in multiple databases in one pass?

Ideally, it would be nice to be able to run either all of the unit tests in multiple databases or all of the unit tests of within a specific suite in across various databases.

If the RunAll, Runsuite and RunTest stored procedures could take a list of database names instead of just a single database name, it would be really handy.

New Post: Initial Data

nmiliotis — Tue, 17 Nov 2009 09:10:14 GMT

First of all thanks for your fast response.

I will create a secondary empty database "REDBTest" which will be used to hold the initial data from the test fixture. "MISReporting" Stored Procedures retrieves data from the "REDB". Instead of writing the statements to call them directly as below:

select a.xxx, b.yyy 
from MISReporting.dbo.Table1 a 
inner join REDB.dbo.Table2 b 
on a.Id = b.Id

I will utilize the "Create Synonym" command as an abstruction layer. So using the following command on "MisReporting".

Create Synonym REDB_Table2 for REDBTest.dbo.Table2

And the TSQL statement inside the Stored Procedure on "MISReporting" database will be:

select a.xxx, b.yyy 
from MISReporting.dbo.Table1 a 
inner join REDB_Table2 b 
on a.Id = b.Id

If i run the SQLTest Tests it will reference the "MISReporting" with "REDBTest" data.

To restore for production purpose i will use the command:

USE [MISReporting]
GO

DROP SYNONYM REDB_Table2
GO
CREATE SYNONYM REDB_Table2 for REDB.dbo.Table2
GO

New Post: Initial Data

lmolnar — Tue, 17 Nov 2009 04:28:21 GMT

You’d want to have a build environment where you have control of some test version of your DBs as opposed to an environment that uses production DBs. If you need to switch between test DBs and production DBs you can write a script that does restore DB from some backups. Unless you have really big DBs, switching from one set of backups to another will be relatively fast.

In many cases it is useful if as part of your build process you include the process that initializes your DBs. In that case the test automation can be integrated as one of the final steps of the build process and you can work on top of a clean and predictable set of DBs. In some cases you may also want to create a back-up of the clean DB (this typically takes a few seconds) and restore it at different moments during the test process.

Having this type of environment where you have a clean DB, you will have to set the initial data as part of your test. Typically a test will not do a lot of things. It will be focused on a limited aspect and it will have the following structure:

The test fixture. The part where you set up the data context for your test. Can be INSERTS / UPDATES or even better calls to sprocs.
Exercise the unit under test. Here you invoke the sprocs / functions, views, etc that are the subject of your test.
Read and verify the expected result.

My point here is that 1. (the test fixture) is part of the test. Normally you should not rely on the right context being already in place. Rather you should start from a clean or at least predictable state and set-up the right context in the test.

There are plenty of reason why in a test environment you really want to avoid having to work with a production DB: Complexity that is out of your control, performance, the inability of scoping tests to only well defined scenarios, and so on.

New Post: Initial Data

nmiliotis — Mon, 16 Nov 2009 20:36:23 GMT

As i am checking the T.S.T. project i wonder about the Initial Data.

I have two databases. The "REDB" and the "MISReporting". The second database has a lot of SP's that evaluate the first database and store the results How do i validate the results if i don't know the expected results. Do i need to clean up the main database each time when running the SQLTST_xxx SP's and initialize with specified records in order to know what i am expecting. Or do i have to create a new emtpy database "REDBTest" which will contain the initial data. In that case i have the problem with "MISReporting" queries which refers to "REDB" and not to the "REDBTest". What is your opinion?

Updated Release: Version 1.4 (Sep 23, 2009)

lmolnar — Sat, 26 Sep 2009 17:20:17 GMT

Version 1.4 changes:

V 1.3 or earlier will not install on a SQL Server with a case sensitive collation. The V1.4 release fixes all known issues related to case sensitive collations.

Released: Version 1.4 (Sep 23, 2009)

Sat, 26 Sep 2009 17:20:17 GMT

Version 1.4 changes:

V 1.3 or earlier will not install on a SQL Server with a case sensitive collation. The V1.4 release fixes all known issues related to case sensitive collations.

Closed Issue: Another Case Sensitivity Issue [4033]

lmolnar — Wed, 23 Sep 2009 09:25:34 GMT

Internal.PrepareTestInformation looks for stored procedures with "Setup_" and "Teardown_" in the SP name
Internal.AnalyzeSprocName tests for "SETUP_" and "TEARDOWN_" in the SP name

Formats should be consistent, either all upper or all Pascal (mixed) case.
Comments:

This issue is fixed in V1.4

Closed Issue: Error on installation when using a case-sensitive collation [3536]

lmolnar — Wed, 23 Sep 2009 09:23:19 GMT

I received an error while installing the tst database using "tst.bat /Setup" The version is 1.2

Changed database context to 'tempdb'.
Changed database context to 'TST'.

(1 rows affected)
Msg 207, Level 16, State 1, Server ACNCND63419BB, Procedure PrepareTestInformation, Line 118
Invalid column name 'SuiteID'.
Msg 207, Level 16, State 1, Server ACNCND63419BB, Procedure PrepareTestInformation, Line 118
Invalid column name 'SprocType'.

This cause of this error was that my sql server database is using a case-sensitive collation. The following changes were made in SetTSTDatabase.sql to resolve the issue:

Line 985, change SuiteID to SuiteId
Line 985, change SprocType to SProcType

After these two changes were made, the setup completed successfully.

-- Erin
Comments:

This issue is fixed in V 1.4

Updated Wiki: Home

lmolnar — Wed, 23 Sep 2009 09:15:20 GMT

Support for validating views as well as stored procedures and functions that return a table.
A reliable implementation of Assert.Equals / Assert.NotEquals procedures that detect when a comparison should not be made due to incompatibility of the data types. Additional procedures like Assert.NumericEquals, Assert.NumericNotEquals, Assert.FloatEquals, Assert.FloatNotEquals are provided.
Ability to run concurrent test sessions against the same or different databases.
Ability to produce XML results.
Automatic rollback of the database changes done during each test.
TST will detect when its own rollback mechanism becomes ineffective in some scenarios that involve testing code that uses transactions. The TST rollback can be disabled at the test, suite or global level.
No need to register the test stored procedures; they are picked up automatically by the TST test runners.
Can be run from the command prompt or directly by calling one of the test runner stored procedures.
The TST infrastructure is isolated in a separate database.
Easy to install and integrate with other test frameworks.
Does not have any dependencies besides SQL Server.
Very small learning curve for someone already familiar with T-SQL.

HTML format: TST.mht
Word 2007 file: TST.docx

7 minutes video demo: http://www.youtube.com/watch?v=bLtK20bl7G4

Author's home page and other projects: http://www.ladimolnar.com.

The TST API

Assert.LogInfo
Assert.Pass
Assert.Fail
Assert.Equals
Assert.NotEquals
Assert.NumericEquals
Assert.NumericNotEquals
Assert.FloatEquals
Assert.FloatNotEquals
Assert.IsLike
Assert.IsNotLike
Assert.IsNull
Assert.IsNotNull
Assert.TableEquals
Assert.IsTableEmpty
Assert.RegisterExpectedError

Runner.RunAll
Runner.RunSuite
Runner.RunTest

Utils.SetConfiguration

Updated Release: Version 1.4 (Sep 23, 2009)

lmolnar — Wed, 23 Sep 2009 09:14:38 GMT

Version 1.4 changes:

V 1.3 failed to install on a SQL Server with a case sensitive collation. This release fixes all known issues related to case sensitive collations.

Released: Version 1.4 (Sep 23, 2009)

Wed, 23 Sep 2009 09:14:38 GMT

Version 1.4 changes:

V 1.3 failed to install on a SQL Server with a case sensitive collation. This release fixes all known issues related to case sensitive collations.

Created Release: Version 1.4 (Sep 23, 2009)

lmolnar — Wed, 23 Sep 2009 09:14:09 GMT

Version 1.4 changes:

V 1.3 failed to install on a SQL Server with a case sensitive collation. This release fixes all issues related to case sensitive collations.

Created Issue: Another Case Sensitivity Issue [4033]

hanemaj — Mon, 21 Sep 2009 18:59:40 GMT