Revision History:
04/16/2003 - William E. Caputo (wcaputo@sourceforge.net) - project moved to SourceForge
03/25/2001 - William E. Caputo (wcaputo@sourceforge.net) - written in T-SQL for MS SQL Server 2000
SPUnit is (loosely) based on the xUnit family of Unit Test Frameworks. It is intended for testing MS SQL Server stored procedures using actual stored procedures. The idea was to make it really painless to test the code in stored procedures, and facilitate test-first programming of stored procs.
It is a port inspired by JUnit, a testing framework for Java, developed by Kent Beck and Erich Gamma, and also, CPPUnit -- a port of JUnit -- developed by Michael Feathers.
| Readme.html | This File |
| license.txt | The legal stuff |
| Cookbook.html | A tutorial demonstrating how to use the framework. |
| Files | Folder containing support files for the documentation |
| Cookbook.sql | The finished example from the cookbook. |
| Create_SPUnit.sql | This script will build the database and all ofthe stored procs, and support tables. |
| runner.sql | A simple test runner that can be used from within Query Analyzer. |
| Create_SPSampleDB.sql | This script will create a sample database for use with the sample TestCase, or the Cookbook example. |
| SPSampleDBTests.sql | This is an example test case script. |
| sprunner.vbs | A command line based runner that uses VBScript and WSH. |
| sp.bat | A simple wrapper batch file for the simple command line runner. |
| sprunner.udl | A data link file that you can use with the simple command line runner. |
Simply run the Create_SPUnit.sql script against the SQL Server installation you want to use the framework on. It is suggested that you use an account that has permission to use the dbo security role. Furthermore, when running the tests if you are going to use Windows Authentication, make sure the user account being used has permission to run the stored procedures.
Ideally you want to run your tests with same account that creates the stored procedures. In order to test effectively you will most likely need the ability to do SELECT's against the underlying tables, or views.
For example: we use this framework from our automated build. This build process runs as a user called buildmaster. In order for this user account to have access to the framework's "Run" procedure, the buildmaster account would need the appropriate permissions.
There is also a test database with an example TestCase. Run the Create_SPSampleDB.sql script to create this test database.
Create an sql script that looks like the sample test case. Specifically, it must have at minimum:
The framework provides a procedure named "Assert". It compares two values of sql_variant type, so it should be useful for most SQL Server types. Look at the TestCase script and the cookbook for some simple examples.
Create a TestCase script (see cookbook for an example), and then run it. This creates the stored procedures in the database you want to test.
After running the Test Case script, and so registering your test procedures. execute the "Run" script from the framework (there are runners bundled with the framework). That's all there is to it.
All tests are executed inside a transaction that gets unconditionally rolled back, so the database state shouldn't change from run to run.
It would be pretty easy to allow the "Run" procedure to query the database under test to discover the Test Procedures -- kinda like how JUnit uses reflection.
Right now the Database being tested needs an actual procedure named "Suite". An improvement might be to have a registration table in the Framework, so that the Suite Procedure could be set using a stored procedure in the framework.
A GUI runner.
There are many, this is a pretty simple framework (DTSTTCPW). It exists because I needed one, so I wrote it.
Specifically:
This was a fun little project. I hope to enhance this as I go. I mainly wanted to get something together to apply test-first programming to stored procedures. I figured maybe we could do it from within another stored procedure, and this is the result. I hope someone finds it useful.
Naturally, there are some huge differences between this framework, and the other xUnit frameworks. For one thing, T-SQL isn't an OO language, so there are no classes to use as test cases, so a test case is simply a script, and there is only one per database.
I'd like to thank Kent Beck and Erich Gamma for writing JUnit, and Michael Feathers for writing CPPUnit. These were the two xUnit frameworks I knew when I wrote this, and were the inspiration for me writing this one. So as not to break the chain of gratitude, I'd like to thank them for their cookbooks which I used as the basis for the one included here. Also a big thanks to Ron Jeffries, and everyone else on the XP mailing list http://groups.yahoo.com/group/extremeprogramming I have learned more than I knew I could know about software development and TestFirst programming from everyone on that list.
Another thank you to Roy Singham, Martin Fowler and the gang at ThoughtWorks. A great bunch of people to work with.
One more thank you to Bob Martin, Eric Meade, and Jim Newkirk I learned a lot about writing testFirst from these guys.
Thanks also to John Duncan for helping me design this framework as we walked to Dylan's on Clinton in Chicago to get farewell Guinnesses. (this was over two years ago now, wow!)
1.0 -- The basic framework, and installation written.