TSQLUnit and TDD
To facilitate a smooth flow with TDD, it was key to identify a suitable testing framework to support my goal. After some research and comparisons, I eventually settled on TSQLUnit, an xUnit framework for MS SQL Server. It is interesting to note that for every programming language that exists out there, it is expected that someone will eventually create an xUnit style testing framework for it. Why should SQL Server be any different?
Database testing could be performed from the application side but the nature of the development was primarily schema/DDL changes (e.g. mostly dropping and removing tables, columns, etc.) that involved very little or no business logic. Otherwise, app code would be a better and more appropriate candidate to handle testing the db. This particular scenario lent itself to finding and using something that was as close to the database as possible ideally where the unit tests can be written and supported by the TSQL language itself. The intent was to try and follow one of the core tenets of any xUnit framework which is to do testing in the same language as the code that requires to be changed. TSQLUnit seemed the best fit.
Regardless, it is quite probable, in the future, that I might disregard all of above and still switch to testing all aspects of the database using C#. This is not because of TSQLUnit but because the TSQL language itself can be so cumbersome to work with especially if you are accustomed to the power and flexibility of a non-declarative programming language such C#. For the moment, it is satisfies my current needs for testing so it will suffice.
A Few Key Features of TSQLUnit
One feature of TSQLUnit that is critical for any type of database testing is the ability to rollback changes after each test run completes ensuring that the database is in a nice clean state before the next test starts. This simply implies that the the data will not remain altered when the next test runs potentially tainting its results. Not only does this apply to all data/DML changes (inserts, updates, deletes) but DDL changes (creates, alters, drops) as well. For example, if Test A creates a table or alters a column for an existing table then those schema changes will most certainly rollback and be undone before Test B starts to execute. Therefore, the tests themselves are isolated from each other meaning that all of the unit tests can run independently.
In addition, TSQLUnit supports the xUnit features of Setup/Teardown for a test suite. For example, if you have five tests that are logically related and are dependent on the same preconditions then you can prepare all of your data in a single, shared 'Setup' fixture and TSQLUnit will automatically run this before each associated test thereby enforcing no co-dependencies between the tests. Note that one obvious drawback with repeating the same setup multiple times might be performance if the tests rely on loading large volumes of data for the purpose of testing. As for 'Teardown', it provides essentially the same functionality as 'Setup' with the one difference of executing at the end of each test and not at the beginning.
TSQLUnit is fairly decent but I initially struggled not with the framework itself but with the usual problems with database testing such as setup of data, rollback of changes, etc. It is a lot of work because unlike in application code it is hard to stub out dependencies in the database objects such as tables, stored procedires, etc. (Especially when dealing with a 225+ column table in the good ol' legacy db I was so fortunate to be tasked to make modifications.)
Running TSQLUnit using NAnt
Once a few initial unit tests were created using TSQLUnit, the process of running them was incorporated into the project's NAnt database build script. Just like the database build itself, the unit tests makes use of the NAntContrib <sql> task for their creation and execution. These are the high level targets defined in the build script:
<!-- after building database including applying change scripts --> <call target="InstallTSQLUnit"/> <call target="LoadUnitTests"/> <call target="RunUnitTests"/>
'InstallTSQLUnit' is a single sql script that runs against your target db creating all of the necessary tables, stored procedures, etc. that the TSQLUnit framework requires to function. This is simply the basis and source code for the framework itself that is integrated with the database.
'LoadUnitTests' runs the sql scripts that contains the tests themselves located in a 'Tests' folder in the project's directory. To elaborate, it creates all of the unit test sprocs along with, if necessary, injecting any test data into the database that is not handled by the Setup fixtures.
Finally, 'RunUnitTests' calls the TSQLUnit command to run all of the unit tests inside the database:
<target name="RunUnitTests" description="Run all unit tests "
if="${installUnitTesting}">
<sql connstring="${connectionString}" transaction="true" delimiter=";" delimstyle="Line">
exec tsu_runTests;
</sql>
</target>
The NAnt db build is configurable so that you can specify whether or not to include or exclude the unit tests. This might be necessary if you just simply want to only build the database and nothing more perhaps for the sake of reducing the amount of the build time if the intent is to use database for another purpose other than testing.In the command-line window, as NAnt logs the progress of the database build, at the end it will bubble up the output results of TSQLUnit indicating whether the tests passed or failed:
... ExecuteSql: [echo] loading .\Database\Tests\ut_DeleteTables_VerifyTablesDropped.sql RunUnitTests: [sql] ==================================================================== ============ [sql] -------------------------------------------------------------------- ------------ [sql] SUCCESS! [sql] [sql] 14 tests, of which 0 failed and 0 had an error. [sql] Summary: [sql] Run tests ends:Feb 20 2008 11:44AM [sql] -------------------------------------------------------------------- ------------ [sql] Testsuite: (14 tests ) execution time: 76 ms. [sql] ==================================================================== ============ [sql] Run tests starts:Feb 20 2008 11:44AM [sql] ==================================================================== ============ BUILD SUCCEEDED Total time: 6.1 seconds.Currently, the overall database build does not fail if the unit tests themselves fail since NAnt can not catch and interpret TSQLUnit results. This codeproject MS SQL Server script validation with NAnt Task seems to provide that level integration between NAnt and SQL Server unit testing. Perhaps, this functionality might be implemented later, but, for now, it is good enough.
Other related posts on TSQLUnit:
It's True (And Not False): Assert Equality in TSQLUnit
Database schema changes unit tested using TSQLUnit
Posts
No comments:
Post a Comment