Configuring a CI server with SQLCE and MigSharp on TeamCity

Posted : Sunday, 31 March 2013 07:26:00

Recently I’ve been working for a client in London, they’re an SAAS provider and have recently developed a whole suite of new products utilising the latest features of the .net framework to compliment their existing codebase. One of the aspects of the product suite that I have been responsible for is to ensure that unit tests are an integral part of the development team strategy. Since reading The Art of Unit Testing, I have been a total convert to unit-testing, while there are a small number of scenarios in which TDD doesn’t make sense (for example intensive IO operations), in the vast majority of cases the first line of code I write when starting out on a new project will invariably be a test method. Prior to my joining them, my current client was using a SqlLite in memory database for their database tests and had built a large number of unit tests which were run as part of a TeamCity CI process. For reasons unknown the decision was made to switch to use SQL Compact Edition, from what I can tell from this point on the database based unit tests no longer worked and, Quel Dommage!, the CI build was turned off. In previous roles I have spent considerable effort establishing the practice of unit-testing and continuous integration and cannot understate the benefits it I have seen it bring. I made it a goal to re-establish the CI process. Given the choice I would probably have opted for Bamboo, it has a nice UI and integrates really well with JIRA and Confluence (both used by the SAAS client), in this case I did not have the choice so TeamCity it was.

The first thing I needed to do was get a testless build running. While not ideal, at the point I started this role there was a significant amount of development time wasted due to breaking commits, typical this would be down to Mercurial merges causing some file or another to contain invalid syntax or a developing forgetting to add a new file. In an ideal world developers would always perform a full clean and  recompile after any merge and this broken code situation wouldn’t happen but this is both time consuming and not failsafe. Generally this problem manifests itself after a developer commits and pushes a change/fix last thing in the day and then shoots home. Anyone trying to get latest the next morning can not compile code until the responsible developer is on hand to resolve the error, or revert the commit history to the last working version – definitely not ideal. After a bit of to-ing and fro-ing and some help from the development manager we got the CI build working to perform a full rebuild on every commit with full shame emails to the entire team in the event that any member of the team pushed a breaking commit – not perfect but so much better than nothing. Within a day the first shame email went out and the effort expended was already beginning to show a return.

The next stage of the process was to get some unit tests running. The product suite on which I am working features a number of we based applications, a range of distributed components and a service bus, I was tasked with writing a few service bus command consumers and in this scenario, TDD was the only way to go – wiring up a full code stack and bus on my dev machine and all the associated development effort just to hit a breakpoint was ridiculous compared to writing a single unit test method in which I could spin up a new instance of the bus command consumer and supply any required dependencies. Fore this reason the code I had written was pretty well covered so it was then merely a question of getting these tests to run as part of the CI build.

In TeamCity I added a unit test step to  the existing build and included the assemblies containing all my unit tests. When I ran the build I kept getting error messages of the type “The specified table schemaname_tablename does not exist.”, needles to say they worked perfectly on my machine. Database versioning for the product suite is managed using the open-source MigSharp project. I hadn’t used this before but my best summation would be that its an early/non-Microsoft version of (and possibly inspiration for?) Entity Framework codefirst migrations. The process by which a database-based unit-test is executed consists of copying an empty SqlCe database into the test bin directory, executing all migrations then running the test before finally deleting the database ready for the next test. I wanted to try and figure out what was going on so I ignored all but one test and commented the line of code which deletes the database at the end of the test. I did this, reran the build and then checked the TeamCity build directory, sure enough the database was empty indicating that for some reason none of the migrations were being run. I created a fork of the code base and added copious amounts of logging – in particular I found a few try/catch blocks with empty catch – this is a total no-no as far as I’m concerned so I added logging into these catch blocks. After running the build again and viewing the output I found this this line of output :

“Unable to find the requested .Net Framework Data Provider. It may not be installed.”.

So how was this working on my machine? I haven’t installed SQL Server Compact Edition? Maybe visual studio did it for me? Who knows? Who cares! I added the following xml to machine.config on the build server:

    <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0"
      description=".NET Framework Data Provider for Microsoft SQL Server Compact"
      type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

Then I manually triggered a build and Bingo! My test ran. I un-ignored all my the other tests, merged in my logging fork, re-implemented the line of code to delete the db on teardown,  committed and pushed the code. I did this a couple of weeks ago and we’ve already had a numerous occurrences of tests breaking from seemingly unrelated changes. I am now going through the arduous task of getting all the old tests running again – the horrible reality of this situation of that this time and effort would not be required if the CI process hadn’t been turned off. Still, you gotta love unit tests right?

  • (This will not appear on the site)