My ACCU 2011 Session Proposal - Using xUnit As a Swiss Army Testing Toolkit

I’ve decided to be brave and submit a session proposal for next year’s ACCU Conference. I know if I get accepted that means I’ll have to curtail the relentless late nights in the bar – at least until I’ve given my presentation. Anyway, the catalyst for my proposal was my blog back in March titled Integration Testing with NUnit. Not long after posting that I watched Paul Grenyer give a presentation to the London branch of the ACCU that touched on similar ground. My session is intended to take the idea further still and cover use of the xUnit style in other areas where perhaps custom test scripts and tools might have been written such as API exploration and verifying external systems.

Session Summary

Title: Using xUnit As a Swiss Army Testing Toolkit
Type: Tutorial/Case Study
Duration: 90 minutes
Speaker: Chris Oldwood

Speaker Biography

Chris started out as a bedroom coder in the 80s writing assembler on 8-bit micros; these days it’s C++ and C# on Windows in big plush corporate offices. His career has covered both shrink wrapped applications and in-house systems with the past 5 years focusing on grid-based distributed systems in the Finance industry. When not attached to a keyboard and screen he has a wife and four children to entertain, dips his toe in the local swimming pool and provides the commentary for the annual Godmanchester Gala Day Duck Race.

Session Description

Modern Unit Testing practices act as a conduit for improved software designs that are more amenable to change and can be easily backed by automation for fast feedback on quality assurance. The necessity of reducing external dependencies forces us to design our modules with minimum coupling which can then be leveraged both at the module, component and subsystem levels in our testing. As we start to integrate our units into larger blocks and interface our resulting components with external systems we find ourselves switching nomenclature as we progress from Unit to Integration testing. But is a change in mindset and tooling really required?

The xUnit testing framework is commonly perceived as an aid to Unit Testing but the constraints that it imposes on the architecture mean that it is an excellent mechanism for invoking arbitrary code in a restricted context. Tests can be partitioned by categorisation at the test and fixture level and through physical packaging leading to a flexible test code structure. Throw in its huge popularity and you have a simplified learning curve for expressing more that just unit tests.

Using scenarios from his current system Chris aims to show how you can use a similar format and tooling for unit, component and integration level tests; albeit with a few liberties taken to work around the inherent differences with each methodology.

Implementing Constants & Enumerations in a Database

How often have you come across this kind of code in a stored procedure:-

select abc
from   xyz
where  Status = 3

…or if you’re lucky (or perhaps unlucky if the comment is out-of-date):-

select abc
from   xyz
where  Status = 3 --Failed

One of the downsides of SQL (at least with T-SQL) is a lack of support for constants, and by extension enumerations.

No Magic Numbers

It’s long been a best practice that you avoid the use of so called Magic Numbers like the ‘3’ in the examples above in favour if some more symbolic name. This aids both readability and maintainability because you don’t have to go and lookup what the number represents (in the case of an enumeration) and if the enumeration value needs to change you only have to change the relationship with the symbol and not the code where the symbol is referenced.

Local Constants

If you only need a constant for use within a single stored procedure then you can just use a normal variable:-

declare @Failed int
set     @Failed  = 3
. . .
select abc
from   xyz
where  Status = @Failed

This will make the latter SQL more readable and hopefully more maintainable - it should be far easier to grep for “Failed” than “3” if the numeric value does need to change[*].

Global Constants

Sadly the need for one-off constants is pretty rare. What is more likely is the need for a globally defined constant so that we don’t violate the DRY/SPOT principle by defining local constants in every procedure where it’s used. The best solution I could come up with[#] was to use a User Defined Function:-

create function Failed() 
  returns int
as 
  return 3
go

This means that you can use it like so:-

select abc
from   xyz
where  Status = Failed()

However it’s not always possible to use a UDF in the same place that you can a constant or variable (I’ve yet to read up on why) and so sometimes in a stored procedure you still need to use a local variable as well:-

declare @Failed int
set     @Failed = Failed()
. . .
exec SomeOtherProc @Status = @Failed

Lack of Namespaces

Although some databases have support for schemas, they are often quite simplistic and not designed for nesting like you would in C++ and C#. This means that to avoid conflicts we need to resort to the old style of using some form of prefix or suffix on the name:-

select abc
from xyz
where Status = TaskStatus_Failed()

This becomes a little less distracting with enumerations as in C# you are forced to prefix the enumeration value with it’s type name anyway (unlike C++, although may people nest C++ enumerations within a separate struct to get the same effect) so it’s not too unnatural to read. Of course you could still create a separate namespace, called say ‘constants’, in which you put all these UDFs if you felt that it helped matters.

An Alternative - Lookup Tables

The main thing I like about the UDF solution is that the function name means the symbol appears as code rather than data. One alternative I’ve seen is to use a small lookup table that has the integer value and a string for the symbol. This makes the numbers less ‘magic’ but it means you have invoke a query to get the enumeration value:-

declare @Failed int
select  @Failed = EnumValue from TaskStatusEnum
                  where EnumSymbol = ‘Failed’

I’ll be honest and admit that I’ve done no performance comparisons between this and the UDF idea. It also still feels to me like you’re expressing the enumeration with data rather than code though but that’s probably nonsense.

Client-Side Use

The other benefit of using a UDF is that you can also use it client side should you happen to need to embed queries within your code so your magic numbers don’t leak further afield. We use proper enumerations in our C# code that are kept in sync manually but tested automatically as part of a build. Ideally we should generate the UDF’s and C# enum’s from a single source, but for the moment the automated tests acts as a nice safety net.

 

[*] Of course in the real world you would never rely on the consistent use constants like this and would actually search for all references to the table and/or column name instead, but hey this is theory :-)

[#] Remembering that I’m not a SQL expert by any stretch of the imagination.

What’s Exceptional Depends on Context

The latest edition of Overload (one of the journals from the ACCU) has another episode of Matthew Wilson’s excellent Quality Matters column. The latest topic is exception handling in which he makes a reference to The Practice of Programming by Kernighan & Pike – a book which I’ve only ever skimmed so far. Hence I thought I should to read the relevant chapter and somewhat surprisingly I found the following statement:-

“Exceptions are often overused. Because they distort the flow of control, they can lead to convoluted constructions that are prone to bugs. It is hardly exceptional to fail to open a file; generating an exception in this case strikes us as over-engineering. Exceptions are best reserved for truly unexpected events, such as file systems filling up or floating-point errors.”

My gut reaction to that was “No it’s not – it depends on context”…

External Resources Always Fail

Messer’s Kernighan & Pike are obviously far more knowledgeable and experienced than me so why did they make such a bold statement? Generalising somewhat, I presume that they were suggesting that any interaction with an external resource should be handled with due care as you should expect it to fail – after all we always encounter problems with files, sockets, databases etc. And as a general rule this makes perfect sense.

Full Environmental Control

But… In the world of in-house server-side software you are often completely in control of the entire environment in which your code runs. Even for in-house desktop software the environment may be a little more hostile but you can still ensure certain invariants. Taking the “opening a file” example again and given the following conditions, would failing to open a file still be considered unexceptional?

1. The file exists (say we just created it and control its lifetime)
2. The permissions on the folder are correct (our server installation script set them)
3. The server has plenty of internal resources (memory, handles etc)
4. The process has not suffered any unrecoverable errors

In this situation I would be on the side of it being exceptional. In my experience failures under these kinds of scenarios usually point to a system configuration error [1 or 2], which means it would never have worked, or the server/process itself has become unstable [3 or 4] due to some earlier (and hence unexpected) issue.

Recoverability

Of course the issue is perhaps moot as all errors fall into two categories – recoverable and unrecoverable. In the scenario described above the recovery is effectively manual[*] – fix the system configuration or restart the process/server. In the real world I can’t see how you could justify the cost of designing a system to automatically cope with some/all of those potential edge cases[+]. Even the last one (process instability) can be hard to handle fully via process recycling because too much badly written code masks nasty errors like Out Of Memory or Access Violations by sinking everything so you don’t get to react to it.

In contrast the times where I can see you might be able to recover from failing to open a file are when the choice of file is controlled by the user, such as in a desktop application, or when the contents of the file can be considered optional, such as user settings.

Applying the issue wider afield to databases and remote services we end up at clustering/replication as the obvious recovery option. Even so, given the general reliability of in-house networks and (non-blade) hardware these days, I would still consider it pretty exceptional for a failure to occur. By-and-large misconfiguration is the most common reason I see for failures involving resources.

Do Or Do Not (Or There May Be a Try)

The chapter of The Practice of Programming in question was about designing interfaces so do we assume that this message is aimed more at the writers of libraries rather than applications? Given that you don’t know the context in which your code is being called do you have to choose between error codes or exceptions? Would your interfaces be better or worse if you somehow supported both so that the caller could decide? I’m sure there are many developers out there right now wincing as they stare at their legacy COM heavy codebases that have a mixture of wrappers generated by Visual C++’s #import feature where some methods throw and others return HRESULTs, some have a Chk prefix and others don’t. What I’m suggesting has more moderation to it…

In the .Net world there is a common pattern where methods by default succeed or throw an exception and a parallel set of methods named TryXxx that return a bool and use an output parameter instead. The latter ‘overloads’ allow you to avoid the cost[#] of handling an exception such as when parsing numbers and DateTimes that you can expect to be iffy (format wise) but they will still throw other exceptions which are orthogonal to the task such as Out of Memory.

Was It Just a Poke At Java?

Or maybe I’m just being too literal? The book was written over 10 years ago (back when exceptions where still not exactly mainstream) and it follows an example in Java so perhaps they were just having a pop at the choice Java had made. After all, at that time Java was still popular as a client-side technology – even outside the browser.

The fact that people like Matthew Wilson are still regurgitating this argument a decade later means there is still much to learn. I think the TryXxx pattern is a nice compromise as you get the flexibility of handling a domain failure without resorting to an unnecessary try/catch block and you also don’t accidentally mask orthogonal failures by ignoring the return code or catching an exception of too generic a type. I wonder if this also satisfies Kernighan & Pike or do they find it muddies the waters even further and still stand by their original statement?

 

[*] I say manual because what normally seems to happen is that the system monitoring software gets bombarded with errors and someone has to make sense of them. Once that’s done it usually involves bouncing something…

[+] In the embedded world, where lives may be at risk, there are forces way outside the normal risk/reward trade offs of an in-house corporate system.

[#] I’m referring to both the performance and maintenance costs of using exceptions.

IDisposable’s Should Assert In Their Finalizer

Paradoxically[*] I’m finding that C# makes resource management much harder than C++. In C++ life is so much easier because you always have to deal with resource management and RAII is the tool that makes it a no-brainer – you either use stack based variables or heap allocated variables and a smart-pointer class such as scoped_ptr/shared_ptr. C# on the other hand makes resource management ‘optional’ through the use of the IDisposable interface[%].

What Is and Isn’t Disposable?

And that is my first problem, you don’t always know what does and doesn’t need disposing – you have to go and find out. Of course IntelliSense can help you out here a little but it still means checking every object to see if it has a Dispose() method[#]. The other alternative is to hope you get it right and rely on a static analysis tool like FxCop to point out those ‘occasional’ mistakes. Personally I’ve yet to get anything really useful out of FxCop outside the usual stylistic faux pas’ which seems to be more the domain of StyleCop.

IDisposable is Viral

OK, that’s a little harsh on FxCop as I’m still learning to use it effectively. But after years of using C++ tools like Lint and BoundsChecker to watch my back I was more than a little disappointed. It does seem to point out if I aggregate a type that needs disposing and I haven’t implemented the Dispose pattern, which is nice. However Dispose() is like ‘const correctness’ in C++ - it’s viral - once you start correctly applying IDisposable to your types it then mushrooms and you now need to fix the types that aggregated those and so on.

Should Interfaces Inherit IDisposable?

This leads me to my first question – should interfaces inherit from IDisposable if you know that at least one implementation needs it? On the face of it the answer seems to be no as disposing is purely an implementation detail; but the whole point of interfaces is to avoid ‘programming to an implementation’. If the answer is no then the moment you cast down to an interface you hide the disposing behaviour. COM essentially has to deal with the same problem and its solution is to make AddRef() and Release() a fundamental requirement of every interface. Of course C# has RTTI built in through the use of the ‘as’ and ‘is’ keywords and so you can always attempt a cast to IDisposable from any other interface. However surely this way lies madness as your code would be littered with seemingly random ‘usings’ just in case an implementation later needed it. Here’s an example where this issues has cropped up most often to date…

We are using the Gateway Pattern extensively in our middle tier services to talk to other systems and so the gateway implementation often requires a WCF proxy which requires calling Close() (or a Socket, database connection etc). So, do I expose the disposing requirement through the gateway interface?

public interface ITradeGateway : IDisposable
{
. . .
}

public class TheirBackEnd : ITradeGateway
{
. . .
}

…or just implement IDisposable on the concrete type?

public interface ITradeGateway
{
. . .
}

public class TheirBackEnd : ITradeGateway, IDisposable
{
. . .
}

In my mind the first is more ‘discoverable’ than the second and it gives any static code analysis tools a fighting chance in pointing out where you might have forgotten to call Dispose(). Some might argue that at the point of creation you know the answer anyway as you have the concrete type so why does it matter? Well, I tend to wrap the creation of these kinds of services behind a factory method that returns the object via the intended interface so that you are not inclined to rely on the concrete type unnecessarily:-

public static TradeGatewayFactory
{
    public ITradeGateway CreateTradeGateway()
    {
        return new TheirBackEnd();
    }
}

Most of our factory methods are not quite this simple as they tend to take a configuration object that further controls the construction so that they can hide whether the ‘service’ is hosted in-proc (which is useful for testing and debugging) or remotely via a proxy[+].

Does It Matter If You Forget?

I mostly work on distributed systems where scalability and reliability are major concerns and perhaps I’m being overly pessimistic about the memory consumption of my services but I think it’s important that for certain kinds of resources that their lifetime is managed optimally[$]. At the moment I’m dealing with a managed wrapper over an in-house native library that is used to manipulate the key custom data container that the organisation traffics in. The underlying native implementation uses reference-counted smart pointers for efficiency and naturally this has leaked out into the managed wrappers so that many aspects of the API return objects that implement IDisposable. In fact it’s all too easy to use one of the helper methods (such as an index property) and find yourself leaking a temporary that you didn’t know about and bang goes your careful attempts to control the lifetime of the container, e.g.

// Good. 
using (var section = container.GetSection(“Wibble”)) 
{ 
   var entry = section.Value; 
   . . . 
} 

// Leaky. 
var entry = container[“Wibble”].Value; 
. . .

I definitely think this scenario should be picked up by a static analysis tool and if I’ve read the blurb on FxCop 10.0 (that ships with VS2010) correctly then I have high hopes it will watch more of my back.

Assert In The Finalizer

So can we do anything else than rely on tooling? I think we can and that would be to put a Debug.Assert in the Finalizer - after all if the object is being consumed correctly then you should honour the contract and call Dispose() at the relevant point in time. I think it’s safe to say that the Garbage Collector does a pretty good job of hiding most mistakes by running frequently enough, but as Raymond Chen points out on his blog last week (which is “CLR Week”) - you should not rely on the Garbage Collector running at all.

For my own types that don’t manage any native resources themselves it could be implemented like this:-

public class ResourceManager : IDisposable
{
#ifdef DEBUG
    ~ResourceManager()
    {
        Debug.Assert(false);
    }
#endif
    . . .
    public void Dispose()
    {
        m_member.Dispose();

#ifdef DEBUG
        GC.SuppressFinalize(this);
#endif
    }
}

So basically we’re saying that if Dispose() is not invoked, then, when a Garbage Collection does finally occur at least we’ll know we forgot to do it. Sadly we can’t rely on being able to inspect the members in the debugger to work out which instance of an object was forgotten because finalizers can be run in any order; but maybe we’ll get lucky.

If you start from a clean slate then you can write a unit or integration test that forces a full garbage collection right after exercising your code to ensure any errant finalizers run and get instant feedback about your mistake:-

[Test]
public void should_not_leak_resources()
{
    var consumer = new ResourceConsumer();

    consumer.consumeResources();

    GC.Collect();
    GC.WaitForPendingFinalizers();
}

I’ll be honest and point out that I’ve put off actually trying this out in earnest until I have had time to investigate how to tap into the Asserting mechanism so that I can avoid hanging the test runner with a message box unless I’m running under the debugger. I’ve done this plenty of times with the Debug MS CRT (_CrtSetReportHook) so I’m sure there must be a way (I’ve only scratched the surface of the TraceListener class but I’m guessing it plays a part).

Debug Builds – Not The Done Thing?

Back in an earlier post Debug & Release Database Schemas I suggested there must be times when a debug build is used in the C#/.Net world. Unlike the C++ world, this beast does not seem to be at all prevalent. In fact I’ve yet to come across any 3rd party (or in-house teams) promoting a debug build. Visual Studio and C# supports the concept, but I wonder if teams only expect it to be used for internal testing? Jeffrey Richter briefly mentioned “Managed Debugging Assistants” in his book CLR via C# but I’ve yet to read up on how you use them effectively, i.e. tap into the mechanism programmatically so that I can log these failures whenever the services are running unattended; not just under the debugger.

[*] It’s not really a paradox as 15 years C++ vs 1 year C# isn’t exactly a fair comparison.

[%] Optional in the sense that not every type requires it.

[#] or Close() in the case of the thread synchronization types which is a nice inconsistency.

[+] I’m still not convinced by the use of an off-the-shelf Inversion of Control (IoC) framework as it only seems to save the odd line or two of code at the expense of managing another 3rd party dependency. I also much prefer creating immutable types that are fully constructed via the ctor than providing multi-phase construction via mutators which IoC frameworks seem to require. Maybe I just don’t work on the kind of systems they’re aimed at?

[$] The obvious question here I suppose is “Why are you using C# then?”. And the answer [for now] is “because we are”. I was expecting this to to scale-up further that it has, but we can still scale-out further if needs be.

Stored Procedures Are About More Than Just Performance

A colleague who is new to the team and working on a new GUI project asked me whether we should be using an ORM tool like Entity Framework for the impending GUI Data Access Layer. Our existing services all invoke stored procedures via manually[*] crafted ADO .Net code which is largely due to our inexperience with ORM tools, but also because we hardly have any DB code so it’s not a burden – unlike what the GUI is likely to need. This in turn led to a question about why we use stored procedures in the first place when the performance of modern SQL query optimisers is such that the benefits are less considerable. What I found interesting about this question was that we had never chose to communicate with the database through stored procedures for performance reasons; on the contrary it has always been about creating an abstraction over the database to ease maintenance.

OO Data Models

I have been practising OO for long enough that I naturally see rows in a table as instances of objects and stored procedures as their methods. You can use OO techniques with procedural languages like C and SQL, it just takes a little artistic license in the naming scheme to work around some of the inherent limitations - although simulating virtual functions is a little trickier :-). There are a number of patterns available to deal with the ORM problem (the oldest ones I know of were in James Rumbaugh’s book Object-Orientated Modelling and Design from 1991) and in particular the problem of mapping polymorphic types to tables but I find the vast majority of cases fall into the simple Table == Type category.

A classic example would be a table of customers with ‘methods’ to add a new customer or find an existing one by its unique ID:-

create table Customer
    Id      int not null,
    Name    varchar(100) not null

exec Customer_Insert 1234, ‘name’
exec Customer_FindById 1234

The most common scheme I’ve seen is to just prefix procedures with the ‘type’ name, which can seem a little odd to long term SQL’ists and so sometimes it’s feels more natural to them to treat the prefix as a namespace and repeat the ‘type’ in the ‘method’ name:-

exec Customer_InsertCustomer 1234, ‘name’
exec Customer_FindCustomerById 123

This prefix also has the nice side-effect of causing related procedures to be grouped together within the file-system/VCS and database IDEs like SQL Server Management Studio (SSMS).

Of course for simple INSERTs and SELECTs this probably feels like overkill as all you’ve done is to wrap a one-line query and make more work for yourself. But where it starts to pay off is when your one-liner turns into a two or three-liner, requires tuning because the query optimiser needs help or you want to weave in other aspects such as debug code (which I covered recently in Debug & Release Database Schemas).

Where’s the Public Interface?

In another post about database development, xUnit Style Database Unit Testing, I briefly referred to the ‘Public Interface’ of the database and what might constitute it. Obviously in SQL there is no direct equivalent of ‘public’ and ‘private’ that you can use to restrict access to objects on a logical level, but there is a real security mechanism instead that you can leverage. By following the “Principle of Least Privilege” and only granting access (e.g. EXECUTE permissions) on those objects that the client needs to use you can enforce a real public/private barrier.

Sadly this is not altogether intuitive from a maintenance perspective and can easily lead to the erroneous exposure of private procedures without some other convention to highlight the intended accessibility of the code. Even if you also follow the principle that you only write unit tests that target the public interface, it’s still not enough. One option would be to adorn the names of private procedures with another prefix or suffix, e.g.

exec __Customer_VerifyState @current, @intended
exec Customer_VerifyState_private @current, @intended
exec Customer_internal_VerifyState @current, @intended

Using a prefix disrupts the nice grouping in the IDE/file-system, whilst a suffix may be invisible if it’s too subtle. Placing it between the ‘Type’ and ‘Method’ names adds a little speed bump that could provide enough of a clue without adversely affecting readability, although it feels more intrusive than the suffix. If you are purely relying on naming conventions then a more intrusive adornment may be better as you can then use a tool like grep to find violations in your sever and client code.

The only other option I see available would be to use schemas, e.g.

exec private.Customer_VerifyState @current, @intended

I’ve not played much with schemas and on my current project we have already used them to distinguish between the core production code and other procedures that aid in unit testing (via a ‘test’ schema) or support utilities (via a ‘util’ schema). Our inexperience with schemas has also meant that we’ve run into a few minor permissioning issues with temporary tables and user-defined types that makes us a little wary of sprinkling them too liberally.

SQL Maintenance

All the teams I’ve worked in during the last dozen or so years have had developers whose main role is to maintain the database code. That doesn’t mean that they didn’t have other skills as well, but effectively they were hired to maintain the SQL side of the codebase. That also doesn’t mean that other developers did not contribute to the SQL code (although in some cases a more rigid structure was in place) as SQL is often a core skill, but the quality of SQL generated by the generalists may not be sufficiently high enough for the volume of data being manipulated.

I definitely put myself in the category of developers that can write basic SQL queries and stored procedures and can handle many table joins, but when it comes to concepts like nested sub-queries I start to question whether I’m writing an efficient query or not. I’ve also read around the subject enough to know that cursors have their place and it’s not usually in the kinds of queries I write so having a SQL expert around to turn my functional query into an efficient one really helps.

Of course the query optimisers built into SQL databases now are way more advanced that when I first started using them, but there still seems to be a need for SQL experts who can tweak a query to correct the optimisers bad judgment calls and give orders of magnitude performance gains, or add some hints to avoid deadlocks caused by lock escalation. This kind of maintenance is so much easier if the SQL is contained within stored procedures as you can leverage the faster deployment mechanism.

 

[*] Naturally much of the boiler-plate code has been refactored out by wrapping the underlying SQL classes and using Execute Around Method so that it has minimal excess baggage.

Where’s the PowerShell/Python/IYFSLH*?

An obvious question to many might be “why are you still writing batch files when PowerShell superseded it years ago?”. Even VBScript is way more powerful than cmd.exe and then there’s everyone’s pretender to the sysadmin throne – Python (and by extension IronPython in the Windows world).

Not Corporate Policy, Again

I’ve been following PowerShell since it was called Monad, and IronPython too during its pre DLR days (especially after a pretty cool demo by Michael Foord at the ACCU Conference in 2008). Once again the problem in the corporate world is that Application Servers are often very tightly controlled and organisations generally don’t like teams jumping on every bandwagon when it has the potential to create conflicts and therefore downtime; I’m sure it was Raymond Chen[#] that said “If the solution starts with ‘First Install X’; then now you have two problems”. PowerShell, Python, Ruby, PERL etc. all involve installing runtimes and libraries which may themselves rely on other shared 3rd party components – DLL Hell is still a problem today. Given that many large organisations only recently (i.e. the last couple of years) migrated to XP on the desktop and Windows Server 2003 for their servers you can see why the uptake of PowerShell has been slow – it’s just not been readily available in this environment; plus there’s a mountain of legacy COM and VBScript which still does the job perfectly well.

Drive-By Programming

As a freelance developer I also feel I have the need to act responsibility which means I can’t go off writing code in whatever takes my fancy and then leave my client with a load of stuff they can’t support; of course some see this kind of behaviour as job security… For me to see a technology as worthy of production use means that there must be a critical mass of people (ideally within the team) who can support it. I’ve worked in a medium sized team where we prayed the two production PERL scripts didn’t fail because no one really knew what they did. I’ve also seen an intern write some production code during his 6 month rotation in PowerShell when there was little to no knowledge of it within the team. I raised the obvious question about who would be able to support it when his rotation ended which prompted the usual discussion about code reviews. It also prompted a deeper one about whether we should try and standardise on one ‘glue’ language going forward and where (in the development process) it would be useful to introduce it to allow time to gain the necessary skills for its eventual promotion to production status. One alternative is for the management to be made aware of the impending cost and stump up the training to acquire the critical mass; but training often seems to be the first to go when money gets tight.

Ok, that sounds overly dramatic I know because anyone can perform simple maintenance, such as replacing database connection strings, file-system paths, server names etc. But there is a very real problem there if something more serious goes wrong as it’s highly likely that there won’t be any tests in place to guide any significant development. You also have to factor in that full-time experienced developers often write this kind of code initially but as the team grows and a separate support team comes on board it is them that are faced with picking up this maintenance burden.

The Right Tool For the Job

We discussed right back at the start of my current project what we wanted to pick as our ‘auxiliary’ languages to make sure that we all had the same hymn sheet. The server and client side coding was largely to be done in C#, but we were also going to need a language to act as the ‘glue’ to bind all the disparate Batch Processing tools together; plus we thought we might need another language to aid in development & testing. One again PowerShell and Python seemed to be at the top of the list. But whereas on a previous project it was Python’s interop with COM and C++ that made it look attractive, it was PowerShell’s interop with .Net (due to the C# bias) that made it favourable this time and it was also part of the standard app server and desktop build now so we had no deployment concerns. Somewhat interestingly a few months later the department did a fact-finding mission to see how many people had Python skills as it seems that they wanted to promote the Python/IronPython approach to the development teams instead. I’m looking forward to seeing how this initiative pans out as we’ve only just started on this area of our system so we have very little to throw away.

Proving a Point

I fired up a command prompt on this 9 month old netbook which is fully patched to XP SP3 to knock up some PowerShell equivalents to the cmd shell examples I gave last time and what do you know… PowerShell isn’t installed!

 

[*] IYFSLH = Insert Your Favourite Scripting Language Here

[#] I’m sure it was him, but my Google Fu is unable to locate this quote.

Simple Database Build & Deployment With SQLCMD

The SQL Server client tools has shipped for years with a console based utility for executing SQL. Historically this was OSQL.EXE, but that has been replaced more recently by SQLCMD.EXE which supports pretty much the same syntax. It’s also available as a stand-alone component that requires no shenanigans like COM registration and so could be stored in the 3rd party area of your VCS and packaged along with your SQL scripts during deployment[+].

So, what’s the difference between osql and sqlcmd? Not a great deal when running simple scripts from batch files or the occasional one liner. The big one I see is the ability to pass Environment Variables into the script which can then be referenced as “$(variable)” in the SQL. This is ideal for passing in flags, e.g. Debug/Release. Here’s a blog post this lists further differences.

Examples

If you want to see the full set of switches you can do “sqlcmd –?”; however I’m just going to point out the handful that I use regularly so the later examples make sense. You should be aware that SQLCMD’s switches are case-sensitive, so “–Q” is not the same as “–q”, but fortunately the behaviour of the ‘overloads’ is usually very similar.

-E	Use Windows authentication[*]
-S <server\instance>	The hostname (and instance) of SQL Server
-d <database>	The database name
-Q <query>	The query to execute
-i <filename>	The name of a file containing the query to execute

So putting these to work, here are a few common examples, starting with dropping a database:-

C:\> sqlcmd –E -S MYSERVER\SQLEXPRESS –Q “drop database MYDB”

Creating a database:-

C:\> sqlcmd –E -S MYSERVER\SQLEXPRESS –Q “create database MYDB”

Executing a query stored in a file against the “MYDB” database:-

C:\> sqlcmd –E -S MYSERVER\SQLEXPRESS –d MYDB –i “C:\SQL\My Script.sql”

Because it’s a command line tool you can pipe the output to other command line tools. Here’s how you can see what connections are open to your database (that are stopping you from dropping it perhaps):-

C:\> sqlcmd –E -S MYSERVER\SQLEXPRESS –Q “sp_who2” | findstr “MYDB”

If you want to toast those connections, this will do the trick (you need the SPID from the previous query results):-

C:\> sqlcmd –E -S MYSERVER\SQLEXPRESS –Q “kill <spid>”

SQL Code Structure

If you’re storing your SQL scripts in a VCS then you may have a structure somewhat similar to this:-

\Src
    . . .
    \ForeignKeys
    \Indexes
    \StoredProcs
    \Tables
    . . .

(An obvious alternative would be to store the object type in the extension, e.g. Customer.table.sql, Orders_Customer.ForeignKey.sql. Just so long as the objects are grouped in some way that you can easily discover via folders or file wildcards.)

One of the biggest problems when building a database is dealing with all the dependencies. Tables can have foreign keys into other tables which means that they need to be created in a particular order or SQL Server will complain. The alternatives are to not use foreign keys at all (which seems a little excessive) or split them out into separate scripts as shown above. There is a still an order, but it is far more manageable because it’s at the object ‘type’ level not at the individual script level. But then we already had to consider that if we’re defining users, user-defined types, schemas etc.

What this means is that we can (re)build our database from scratch using the following plan:-

1. drop existing database
2. create new database
3. . . .
4. create Tables
5. create Foreign Keys
6. create Stored Procedures
7. . . .
8. apply permissions
9. . . .

The drop and create[#] were shown above. The schema and object scripts could be done with a sprinkling of simple FOR magic:-

C:\> for /r %i in (*.sql) do sqlcmd –E -S MYSERVER\SQLEXPRESS –d MYDB –i “%i”

Obviously this is too general as we have no control over the order that scripts will be executed, but with a little repetition we can:-

C:\> for /r %i in (Tables\*.sql) do sqlcmd –E -S MYSERVER\SQLEXPRESS –d MYDB –i “%i”
C:\> for /r %i in (ForeignKeys\*.sql) do sqlcmd –E -S MYSERVER\SQLEXPRESS –d MYDB –i “%i”
C:\> for /r %i in (StoredProcs\*.sql) do sqlcmd –E -S MYSERVER\SQLEXPRESS –d MYDB –i “%i”

(Or if you choose to encode the object type in the filenames your ‘in’ wildcards would be (*.Table.sql), (*.ForeignKey.sql) etc.)

Also, don’t forget that %i at the command line becomes %%i when used in a batch file and to enclose the loop variable in quotes (“%i”) to cope with filenames with spaces in.

However, If you just have a big ball of scripts then a more painful way would be to create a text file with a list of relative paths in that defines the order. Naturally this would be horrendous to maintain, but maybe you have an easy way of generating it? Then you could use an alternate form of the FOR statement to read the text file and pass those lines (i.e. the filenames) to sqlcmd, e.g.

filelist.txt:-

Scripts\Customer.sql
Scripts\Orders.sql
Scripts\Customer to Orders.sql (the foreign keys)

C:\> for /f “delims=” %i in (filelist.txt) do sqlcmd –E -S MYSERVER\SQLEXPRESS –d MYDB –i “%i”

Once again you’ll need to watch out for filenames with spaces as the FOR /F statement will by default use the space and tab characters as field separators (which would cause the filename to be split across the variables %i, %j, %k etc) so we use “delims=” to treat each line as a whole filename.

Deployment From an Application Server

Your Continuous Integration server can happily invoke a script to run the automated tests on the build server using a local copy of SQL Express with the source code applied directly from the VCS view/working copy. But when it comes to Continuous Deployment to something like a DEV environment you really want to be packaging this code up (along with SQLCMD and the scripts) and installing from another box. This way you have a solid record of what was deployed (which hopefully ties up with the label in your VCS) and you’re also not tempted to patch code directly in the build server views.

I’ve found that a simple .zip file does the job nicely and there are plenty of command line tools for generating these, such as 7-Zip. You can use the same techniques you used to invoke SQLCMD to invoke the archiver to add the scripts to your package. The built-in Windows unzipper is painfully slow (and only available via the GUI I believe) so I would also publish the archiver alongside the SQL package so that you don’t have another server configuration issue to think about.

A Cautionary Tale

There is one rather nasty gotcha that you should be wary of with sqlcmd[^]. If the native SQL driver is not installed then you’ll not know about it – in the sense that you’ll get no errors reported. So, if your scripts just create objects and execute no queries you’ll be none the wiser. Running a simple query like this:-

C:\> sqlcmd –E -S MYSERVER\SQLEXPRESS –Q “select getdate()”

Will result in no output whatsoever. I somewhat naively assumed that the native SQL driver would be part of our standard application server build, if not Windows itself. Fortunately you can still download an MSI with just the driver from Microsoft Downloads here.

 

[+] I don’t know what your licensing agreement says (IANAL) but I would hope it allows you to use it with your Continuous Integration server to allow it to run your automated unit tests and additionally bundle it along with your scripts in your package to act as your deployment mechanism.

[*] Does anyone still use username/password (or mixed mode) authentication with SQL Server? I would expect anyone using SQL Server to be a Windows ‘shop’ and therefore be all Active Directory and single sign-on these days…

[#] It’s possible that for your UAT and Production databases your “create database” will involve custom hardware configuration stuff like where you put the data files and other careful tuning, but for automated unit testing purposes letting SQL Server use the defaults is perfectly adequate.

[^] I don’t think this affects osql because it uses a different transport (db-library?).