Unit tests - Executing over multiple application versions

The main application I design and develop usually contains additional database schema with each new version e.g. a recent 3.6.1 release will no doubt have a few new tables / new stored procedures e.t.c. Currently all our automated unit tests execute against a single database using the latest schema. When we complete and release 3.6.1 the cruise control build will only execute tests against this schema. However I would like to be able to execute our unit tests against old versions at least one back if not more e.g. so we can execute tests against 3.6.0 and 3.6.1. We do put effort into the assemblies to ensure backwards compatibility so it would be very handy to test this within our continuous integration process. Even in cases where we haven’t added any specific backwards compatibility code it would be nice to know existing tests still worked on old databases as we will still apply database patches to them.

The obvious issue with executing the most up to date unit test assemblies is some new features in 3.6.1 won't work on a 3.6.0 database. e.g. assuming a new calendar table was included in a feature in 3.6.1, any tests which required this table would fail if run on databases less than 3.6.1. A calendar class may typically exist in a common assembly which contains many other types which work in previous versions. There are essentially three types of modiciations we could be making to unit test assemblies in each new release

1. Addition of schema which wouldn't work in previous versions e.g. using new table / sps / UDFs
2. Updates to existing unit tests. As we adapt existing code we may have to change tests in some cases slightly, although this should be quite rare
3. Addition of new tests for old schemas e.g. we may find some additional scenarios which haven't previously been tested.

2 and 3 obviously don't pose any issues however when executing unit tests against old databases we clearly need to conditionally execute the tests in 1 only if the schema is valid. A solution to this could be to apply an optional attribute against any test method called something like ApplicationVersion which would take the version number of the schema the unit test can be executed on. The attribute could be applied on a class or method. If applied on a class all tests within the class would require this version number unless any test methods overrode this by specifying a different number. e.g. a CalendarTest class could be defined like this

[TestFixture]
[ApplicationVersion(3,6,1,0)]
public class CalendarTest

However I thought whilst the attribute and use itself seemed quite generic, the actual implementation to retrieve the version number would not be. There are two ways in this case NUnit could be modified to incorporate this

1. Use a plug in framework within NUnit. The plug in could have one method which allows the method to determine whether an actual method is executed or simply ignored. A Bottom line plug in could simply have knowledge about the custom attribute, read it using reflection at runtime, and then verify the version number against that stored in the database and decide whether or not to ignore the method.
2. Have specific knowledge of the ApplicationVersion attribute and perhaps allow a simple piece of configured embedded C# script which could evaluate what the application number would be at runtime e.g. for us we could read it from the data store from the a table which contains the schema version.

If tests were ignored, the ignore reason could be added automatically e.g. "Test method ignored as version executing against is 3.6.0 and this method requires 3.6.1".

This feature would certainly be useful for us as we have a large number of clients using old versions and only a few at a time initially using the most up to date versions. I'm sure the solution would be useful to others too, thoughts?

Continuous Integration of database scripts

I've recently got Cruise Control working on our development server to get latest from source safe, build our .NET assemblies and execute all unit tests. I'm extremely pleased with this and has really proved it's worth recently to show complete visibility to the team of failed builds. Any broken builds which do occur get turned around very quickly; I would strongly encourage anyone who hasn't already integrated cruise control into their build process to do so now.

I have also recently taken this further by integrating database script changes into the process. In source safe we keep all the creation scripts for tables / stored procedures / UDFs e.t.c. When any changes are detected on the VSS database the scripts are applied using a few nant tasks and then the unit tests are re executed. This has quickly caught a few issues recently when stored procedures weren't in sync with the .NET code e.g. wrong data types or too many parameters in the DB compared to the client code.

Like most development teams we have to maintain a few versions of our application and upgrade databases from old version to new versions. The latest database script are rerunnable against old versions of the schema i.e. it will add columns / stored procedures if their not already there, Ideally I would like to execute the database scripts against a number of different versions of the database and then execute the unit tests against the newly updated database to ensure the tests still passed. This would find issues for clients upgrading very quickly. I'd be interested in hearing from anyone else who has tried to automatically integrate database changes within their continous integration process.