You Write Your SQL Unit Tests in SQL?

One of the questions that came up after my ACCU London talk back in January (xUnit Style Database Unit Testing) also came up last week after my ACCU Conference talk (Using xUnit As a Swiss Army Testing Toolkit):-

Why do you write your database unit tests in SQL?

My immediate and somewhat flippant response is:-

Why wouldn’t you write your SQL tests in SQL? You wouldn’t write your Python unit tests in C++ would you?

Now, if you swap the words Python and C++ around in that sentence it begins to sound more plausible. In fact it’s an idea that I’ve looked into in the past, but not so much at the unit test level, more at the component/integration level. Kevlin Henney, in his ACCU London talk last year, pointed out that you could use NUnit[*] to unit test C++ code via the magic of C++/CLI so using a different language to express your tests is by no means wrong but it comes with another cost…

Portability (People Skills)

Steve Love did a talk at the ACCU Conference back in 2009 about portability. Of course what most people think of when you say portability is about the source code and how it works across multiple platforms (e.g. Windows/Unix) or across multiple toolchains (e.g. Visual C++/GCC). But there is another aspect to it and that is portability across people – can you hire another person to do the same job?

I’ve worked in small teams where everyone has to do everything and also in larger teams where people specialise in a particular language or technology. It is hard enough to find good people as it is, adding another orthogonal requirement to the role only makes the search that much more difficult. OK, so many experienced T-SQL developers will probably also have some knowledge of C# and the Cool Kids will happily remind you how we should all be Polyglot Programmers and be able to work with multiple languages, but the organisations I work in don’t get to attract the superstar programmers. Sometimes you’re ‘given’ members of staff from another team, presumably because they can save money by avoiding firing one and hiring another in the mistaken belief that you will turn them into a superstar programmer.

Isolation (Tooling)

Even so, just because they might know C# doesn’t meant they feel comfortable using, say, Visual Studio as their main development tool. Our automated test runner currently uses SQLCMD and we (can) write our SQL code and tests using just SQL Server Management Studio (SSMS). This means that not only are the tests expressed in their language of choice, but the tooling is also the one they are likely most comfortable with. I’ve not tried SQL debugging with SSMS but I would have thought that it is far simpler if you’re not trying to debug across a high technology stack (it feels akin to debugging across RPC calls).

That last point sums up my approach to unit testing, which is that it is all about isolation. That doesn’t just apply at the code level – at the thing under test – but also to the toolchain that sits around it. That doesn’t mean that you should only use primitive tools like Notepad & SQLCMD to develop your code, but that you try and avoid having too many other layers (e.g. C#, ADO.NET) ‘above’ the code in your test scaffolding as it makes it unnecessarily complicated and can make it harder to do things like debugging.

The Right Tool For the Job

Deciding what the right tool is for any job is getting harder every day because new tools are springing up all the time that bring the best bits from other tools together to solve some other hybrid problem. I’m definitely sold on the idea of writing SQL unit tests in SQL as the language provides enough features to allow you to write fairly clear tests, but our Heat Robinson style test runner is clearly begging to be replaced by something better. I also need to look again at how Visual Studio studio approaches the problem because they have an army of developers [hopefully] much cleverer than me that should be able to balance these requirements. I also promised myself I’d got back and look at how TSQLUNIT was coming along as that is also more mature than our framework.

We never set out to write a SQL unit test framework, we just did what felt natural to bootstrap ourselves and that’s the way it ended up. Looking back I still think it feels the right way to go.

[*] Whereas other languages seem to have a few key unit test frameworks, C++ is blessed with a bucket load. At the ACCU Conference last week someone asked for a show of hands of those people that had written a unit test framework and there were quite a few hands. I’ve said before that I believe it’s somewhat of a Rite of Passage and writing a unit test framework seems to be common part of that voyage of discovery.

Database Enumerations – A Non-Performant Query

Back in September last year I wrote about how we have implemented constants and enumerations in our SQL Server database (Implementing Constants & Enumerations in a Database). Essentially we have used parameterless User Defined Functions (UDFs) to act as the symbol for the constant or enumeration. At the time I said that I was not aware of any performance impact that this had, but that I would write it up when I found one. Well, I wrote a support query the other day and noticed that there was quite a difference between the query with and without the UDF. It looked something like this:-

SELECT *
FROM   Requests r
JOIN   Tasks t
ON     r.RequestId = t.RequestId
AND    t.TaskType = dbo.TaskType_Calculation()
WHERE  r.ValueDate = . . .

The two tables involved in the join have millions of rows in them and although there is an index covering part of the query the selectivity is quite low in this case. I didn’t look at the execution plans because this is a template query that I use regularly and the only difference was the explicit filter on TaskType using the UDF. The query took 13 seconds to execute.

Given how often I run very similar queries to this I suspected this was the evidence I was looking for to show how a UDF could affect query performance, and so I did the obvious thing which was to substitute the call to the UDF with the relevant constant:-

AND    t.TaskType = 2 -- dbo.TaskType_Calculation()

Lo and behold the query ran in just 2 seconds. I switched back and forth between the two forms to see if the performance was consistent and it was – 2s vs 13s. Somewhat perturbed now that our lovely idea may be an accident waiting to happen I checked the performance using an intermediate variable, which is effectively how we have used this idiom in our code (because you can’t always reference the UDF directly and it’s a bit verbose):-

DECLARE @calculationTask udt.TaskType_t
SET     @calculationTask = dbo.TaskType_Calculation()

SELECT *
FROM   Requests r
JOIN   Tasks t
ON     r.RequestId = t.RequestId
AND    t.TaskType = @calculationTask
WHERE  r.ValueDate = . . .

Tentatively I pressed CTRL+E in SSMS to execute the query… and… it returned in just 2 seconds. Just to be sure I then alternated between all 3 forms but the intermediate variable version still performed exactly the same as the hard-coded constant. Phew! This is clearly not an exhaustive test but it does give me some continuing comfort that it is a sound idea but that we now know for sure that it can have a performance impact in some scenarios.

P.S. This was on SQL Server 2008 R1.

Describe the Problem, Don’t Prescribe the Solution

The other day whilst scanning the backlog of change requests in JIRA I came across the following entry:-

Replace the use of nchar(x) with nvarchar(x)

And that’s all it said[#]. Now I knew instantly which tables the author was referring to because, like many teams, we only use a fixed width char(x) column for flag type fields such as Y/N or true fixed width strings like currency codes. The reasons why and how these couple of table definition slipped past the informal code review process is somewhat irrelevant, but suffice to say that we were days away from release and chose to take the ‘hit’ instead.

The fact that variable width text was being stored in a fixed width column was not causing any problems within the database or the back-end server code. Yes it was annoying when you cut-and-pasted result values from SSMS (SQL Server Management Studio) as you got a load of trailing spaces that you just had to manually trim:-

...WHERE t.Column = ‘VALUE     ‘

So I put this one aside mentally filing it with the other issues of Technical Debt as there was nothing to fix per-se – the mechanism was ugly but it worked. There was also no reason to suspect it may cause any performance problems either as the tables contain relatively few rows of data.

Then a conversation some weeks later with the change request submitter brought up the subject of raising Technical Debt style change requests and I opined that now we were live the database schema is potentially a whole lot harder to change because we have data to migrate as well[+]. In this instance it wasn’t even vaguely in the area of rocket science, but nevertheless it was non-trivial and ultimately had to be paid for. And then the real requirement came to the surface and brought with it a new perspective…

These fixed width columns were not causing the back-end any problems because we never consumed them – we only every compared them to string literals or ran support queries. The impending GUI on the other hand had controls that displayed some of these fields and apparently the UI framework would bloat the size of the control to allow room for what it presumably thought were always going to be long textual values. My gut reaction was abhorrence at the thought of changing the database schema of a base table just to fix a layout issue in the GUI; that is the kind of nasty coupling that causes change paralysis because managers get worried about what will break. I understood it was a pain having to manually deal with the fallout in the GUI layer but there should be other much simpler solutions that didn’t involve writing SQL scripts to fix the table schema and migrate the data – namely trimming the string somewhere in the data access layer or the SQL query used to fetch the data[*].

Hold on a second I thought. What are you doing directly accessing base tables anyway? The rest of the public interface to the database is via stored procedures and views – you’ll need to abstract that access away behind one of those before going to production. More importantly that gives us another option for remediating this behaviour without touching the schema and data, and better yet, that would be inside the database public interface and so allow the refactoring to occur without changes to any clients. Of course this then forces the issue of what the public interface to this mechanism should really be and at that point it seems prudent to design it properly if the existing implementation is going to leak out badly. But if it doesn’t we can save that job for another day.

The important thing though is that we have identified the root cause and can therefore now begin to assign some value to the potential choices and costs. When the request implies changing the schema of persistent data purely for the sake of it, the risk seems high and costly and the reward seems low. But when faced with a path that allows for incremental change so that the full cost and risk is distributed over smaller steps it seems much more appealing as you can put your spade down at any point.

So how would I have initially phrased that change request? Well a few weeks ago I would have gone for something like this (I can’t remember what the exact issue with the GUI was so it still seems somewhat lame I’m afraid):-

Laying out the XYZ controls has to be done manually because the ABC data has extra whitespace padding due to the use of nchar(x) instead of nvarchar(x) for the FGH columns. These columns should probably be nvarchar(x) anyway as they contain variable width text.

However a recent article by Allan Kelly in the ACCU magazine Overload (The Agile 10 Steps Model) contained a sidebar titled “What’s the Story?” about User Stories. This described a common format for stories:- “As a [Role] I can [Action] So that [Reason]” that felt akin to the use of “[action] [should] [when]” that I follow when writing test case names (itself a variation of the “[given] [when] [then]” style). I felt that this would allow those of us less natural writers to still construct a succinct requirement even if it does sound somewhat robotic, but more importantly it would help bring the value of the request to the forefront. User Stories is something that I’m vaguely aware of but have largely glossed over because my requirements are nearly always technical in nature and tend to come through very informal channels so any connection with ‘End Users’ is usually missing; it just hadn’t clicked that “User Stories” could just as easily be read as “Developer Stories” or “Support Stories”. So this is my attempt at writing it this way:-

As a [developer] I can [trim the whitespace from the ABC data] so that [the XYZ controls are not laid out manually].

OK, so this reads very unnaturally because the story format implies a repeatable action rather than a one off event. A small and hopefully obvious change to the wording and order though should make this more natural:-

As a developer it would save time if the XYZ controls were laid out automatically by ensuring the ABC data contains no excess whitespace.

Do you think that reads better or not? I think the value, while still somewhat intangible as all technical requirements like this are, is clearer because you are forced to justify the action rather than providing it as an afterthought. The beneficiary of the requirement is also clearly stated which helps easily separate the functional from the non-functional.

Of course what the original submitter would probably have written would still be something like this:-

As a developer I need to replace the nchar(x) columns with nvarchar(x) columns so that the GUI layout is automatically laid out.

…because the [action] would likely still be the same, but one would hope that when writing the [reason] alarm bells should start ringing as the tight coupling between the database schema and GUI becomes apparent.

 

[#] This is also a common failing of n00b posters on sites like Stack Overflow. They have already decided on what their solution is and are trying to elicit an answer that helps implement that solution rather than taking the time to explain the underlying problem in case the reason they can’t see the proverbial wood is because there are a load of trees in the way.

[+] As you will see later our well defined SQL public interface and set of SQL unit test means that it is actually quite easy to refactor the schema.

[*] Eventually an O/RM such as Entity Framework or NHibernate will no doubt be in place to tackle this, and so it will be interesting to see how you would go about dealing with this kind of problem when you don’t immediately have access to either the SQL or the code that binds the result values to the DTO members. I’m sure it is easy, but how much out of the way would you have to go?

Friends Don’t Let Friends Use Varargs

Whilst writing my previous post about being clever with strings and varargs functions I was reminded of another time that I had a run-in with them. Luckily I wasn’t the protagonist this time but a colleague instead. He decided that he wanted to make it easier to add an arbitrary set of strings to a particular class via the ctor. In modern day C++ you might do it via an iterator pair like this:-

{
  const char* words[] = { “cat”, “mat”, “dog” }

  Thingy thingy(words, words + ARRAYSIZE(words));
  . . .
}

However this was many years ago and the project was MFC based so the use of STL idioms were very thin on the ground[$].

Another less obvious choice might be to define a vector, add them to that and define a ctor that takes a vector of strings. But nope, my colleague decided that he could avoid the ugly array definition and performance hit of the vector approach by declaring a varargs ctor like so:-

class Thingy
{
public:
  Thingy(const char* word1, ...);
};

This meant that you could provide an arbitrary parameter list of strings and then terminate it with a NULL parameter:-

{
  Thingy things(“cat”, “mat”, “dog”, NULL);
  . . .
}

Naturally I suggested you don’t mess with varargs and that this was an accident waiting to happen[#]. I did agree that it was not an uncommon technique to define an array of items and use a NULL terminator at the end and that what he was effectively doing was this:-

{
  const char* words[] = { “cat”, “mat”, “dog”, NULL }

  Thingy thingy(words);
  . . .
}

At the time we had a definite need to support up to three arguments which were actually all CStrings. I’m sure I suggested writing three ctor overloads that then call a common internal helper method instead for now:-

class Thingy
{
public:
  Thingy(CString arg1);
  Thingy(CString arg1, CString arg2);
  Thingy(const CString& arg1, . . . arg2, . . . arg3);
};

But no, that was far too much work, his way was simpler and, after all, we were all experienced programmers so we don’t make those kind of silly mistakes…

Sadly I can’t remember when the first bug showed up or why it remained hidden for so long[+], but one did suddenly appear and guess what was missing? Yup, the NULL terminator. Of course now I had to check for any other occurrences of malformed ctor calls and I soon discovered one more. Clearly someone was not aware of this need to add a terminator so I did a little Software Archaeology and rooted around in our VCS to find out who needed a little educating… But of course, it was my esteemed colleague, the very same person who suggested that we wouldn’t make that kind of mistake!

So remember kids – Just Say No to Vargs Functions. Unless of course you’re doing Template Meta-Programming in which case you’ll be [ab]using them for a different reason.

 

[$] Due to it’s age MFC has its own containers and single iterator type and doesn’t adhere to the STL idioms that all all know and love now. Consequently if you spent your years knee deep in MFC code you could very easily to be unaware of them.

[#] Of course this was many years before I became aware of my own misfortunes with varargs function and anyway I’d read all the books by Scott Meyers, Herb Sutter, et al so that made me the fountain of all knowledge, right?

[+] It just goes to show that even with debugging techniques like the compiler using guard blocks and filling uninitialized stack space with a non-zero pattern it’s still possible for the stack to end up correctly formed by luck and not by judgement. That’s especially true when the stack variable is a ‘bool’.

A Not-So-Clever-Now String Implementation

Back around 1995 when I first started using C++ commercially[+] I picked up an excellent book by the late Paul DiLascia titled “Windows++”. It was about writing Windows applications in C++ and the book developed a framework along similar lines to the then fledgling MFC. I decided that writing my own C++ framework would be a great way to learn C++ and I (somewhat foolishly) decided that I would use a similar code style to MFC so that I could easily reuse any of my framework code in my day job (after making it production ready of course).

Reinventing the Wheel

The heart of any C++ class library back then was the String class. C++ was still evolving and my work involved using MSVC 1.52c (aka Visual C++ 1.5) which had no template[^] support so naturally I created my own ‘classic’ class that managed an ANSI char buffer:-

class CString
{
private: 
  int   bufsize;  
  char* buffer;      
  . . .
};

One of the problems with this class was that it was too easy to screw up[*] when using the printf() family of functions because it had a different memory layout to a plain char*, so if you forgot to invoke the moral equivalent of c_str() you could end up crashing the process because the bufsize member would be interpreted as a string pointer:-

{ 
  CString str = “chris”; 
  printf(“hello %s”, str); // BOOM!
}

Swapping the two members around would probably work for the single-string case above, but not for N arguments as the trailing bufsize member would become the next string:-

{ 
  CString first = “chris”, last = “oldwood”; 
  printf(“hello %s %s”, first, last); // Still BOOM!
}

Now, I felt that giving up the beauty of the printf() family was too much; I had just spent the last few years learning C and so I had the various format specifiers committed to memory. Windows also had its own variation, wsprintf(), that supported a few other useful extensions such as near/far pointers and the ANSI/Unicode build dependent variants of “%c” and “%s”.

Tempted by the Dark Side

One day whilst debugging, I stepped into some code that passed a MFC string directly into a printf() call and realised that it hadn’t gone bang. As I stepped into the printf() call I realised that the reason it had worked was because an MFC CString object had the same stack footprint as a raw char* pointer. When an object is passed into a varargs style function the object is passed by value, which in the case of a CString is effectively a single pointer. Also because the class only has a pointer based member it has no extra padding or alignment requirements – at least not on the x86 targets I was interested in.

I thought this was rather clever and set about changing my implementation so that it had similar behaviour. It hadn’t registered at first but I later realised the similarity with the implementation of the COM BSTR type which also stores the string length in an area preceding the string contents. And so I switched my implementation to this:-

class CString
{
private: 
  class CStringBuffer 
  {  
      int   bufsize; 
      char buffer[0]; 
  }; 

  char* string;  // Really CStringBuffer.buffer 
  . . .
};

So now I allocate a single chunk of memory for the header (CStringBuffer) + contents (N * sizeof(char)), but instead of storing a pointer to the CStringBuffer header as a member, I slide along the memory block and store a pointer to the first character of the string, i.e. &CStringBuffer.buffer[0]. I used an interesting Microsoft extension- the zero element array. This beast used to be quite common in the Windows headers and is a useful way to create a placeholder for a variable length array that follows a structure in a contiguous piece of memory. The alternative is the standards compliant “char buffer[1]”, but then that makes your sizeof() arithmetic a little more complicated as you have padding to account for along with the extra byte in the array. Of course to access the header structure without littering the code with casts I wrapped the necessaries in an inline method:-

CStringBuffer* CString::GetBuffer()
{ 
  return ((CStringBuffer*)string)-1;
}

All very ugly and switching from a C-style cast to a proper reinterpret_cast<> doesn’t make it any less unpleasant. Still, I happily lived in ignorant bliss for well over a decade until I decided to make a conscious effort to drop my CString class in favour of the standard string type std::string – at least for new libraries that is. The conduit was reading Matthew Wilson’s Imperfect C++ and coming across his STLsoft libraries. Suddenly I found a new impetus to deal with the impedance mismatch between MFC/ATL and Standard C++ (something I believe his new book may well be tackling).

A New Hope

The switchover went swimmingly well with my new Core and XML libraries showing the way forward whilst the old WCL, NCL and MDBL libraries became the epitome of the word ‘'legacy’. Where possible I added extra overloads to allow std:string’s to be used naturally but by-and-large the worlds were kept apart and the cost of the occasional implicit[#] conversion from a CString to a std::string was left for the profiler to find, should it ever show up.

Then I decided that where possible all new code, even in my legacy libraries, should adopt the proper string type and that I would slowly migrate code across. So I started making deeper and deeper refactorings (backed by my unit tests – where they existed) and still things looked good. Visual C++ 10 (aka VS2010) arrived on the scene and so a port was required to ensure my codebase was compatible and much to my surprise a unit test in my ANSI to Unicode conversion helper functions choked on an ASSERT inside the STL. The code was wrong, no doubt about it, but why hadn’t I see it before as I usually use STLport to keep me honest? I did a build with STLport 5.2 and it also whinged! So I fixed my iterator dereferencing bug and ran the entire suite to make sure I hadn’t entered into the murky waters of Undefined Behaviour anywhere else by accident and again it when BANG! One of my COM library tests was now failing! Running the test under the debugger revealed a more serious problem as the MS debug runtime started complaining about other matters…

Undefined Behaviour Strikes Back

It took a couple of debugging attempts but then I winced as I spotted my error. I had switched over a couple of helper functions from returning a CString to a std::string and the result of that function was in turn being passed to a varargs logging function like so:-

std::string SomeClass::toString()
{ 
  return . . .;
}
. . .
void Elsewhere::DoSomething()
{ 
  LOG(“Blah, blah, blah %s”, something.toString()); 
  . . .
}

Yup, there was no call to c_str()!

Then the true cost of my foolish design choice started to dawn on me as I realised that there was no obvious way to catch the problem at compile time because varargs functions are effectively type-less once you extend into the ‘variable’ part of the parameter list. Yes, it might be possible to do something with templates and macros but that would be non-trivial. Add in the fact that I have used varargs style functions to do all my string formatting, often in my exception handling, and you can guarantee that there are plenty of time bombs in the shape of rarely executed exception handlers just waiting to go off.

Another New Hope

Maybe it was fate, but I had always been meaning to get my Core library and its unit test suite to build and run under more than just Visual C++. So I started investigating the alternatives such as GCC and Digital Mars (see “GCC for the Visually C++ Impaired”) and soon I had both Core and XML working under GCC 3.x and 4.x via the Code::Blocks IDE – albeit only with the standard warning level.

After being distracted for some months I finally got back to playing with GCC as I had always intended on compiling my code with the highest warning settings available to see how much static code analysis GCC can do over-and-above what VC++ already does – an awful lot it turns out (a topic for another day). And, guess what one of the behaviours that it singles out? Yup, the passing of non-primitive types to printf() style functions! I’ve yet to dig deeper and see if it is varargs functions in general or whether it’s functions like printf(). Either way it spots code like this:-

{ 
  std::string text(“. . .”); 
  printf(“%s”, text);
}

Which is great because I now have a clear path forward for deprecating my CString type safely:-

1. Add a c_str() method to my CString type to provide compatibility with std::string
2. Start compiling[$] more of my codebase with GCC 4.x to identify and fix those bugs by appending a call to .c_str()
3. When a suitable refactoring moment arises switch code from my CString type to the standard string type
4. Test, test and test some more…

Epilogue

I often wonder whether it’s still worth maintaining my 15 year old C++ codebase of tools. Many of them were made obsolete years ago and so I have stopped fixing up the really old applications but the libraries live on – kept alive by each new utility that I write. I could start again from scratch but you don’t get that luxury in the real world so I’m inclined to try and refactor my way out. This way it remains a learning exercise – albeit one about refactoring legacy code – which sadly is what a lot of commercial C++ projects have become.

 

[+] I first used it at university some years earlier and at that time you used the ‘overload’ keyword to mark overloaded functions. I managed to get G++ (nay GCC) working on my Atari 1040 STE (with twin floppy drives) but it was not a pleasant experience. I already thought C was too bloated in comparison to Assembly Language and C++ was therefore even more pointless. Sooo naive…

[^] Not that I knew what a template was back then of course. I never really started to understand templates until the turn of the millennium and Template Meta-programming is still largely theory to me – that’s why there’s the Boost libraries!

[*] If you were lucky it was in a common code path and you got a UAE/GPF there and there; but often you weren’t and it became a time bomb if the code path was in an error handler that was called infrequently.

[#] Yes, I added a conversion operator to ‘const char*’ to avoid having two overloads on every text API method or littering my code with .c_str() style warts. Choose your poison…

[$] Sadly, although my Core and XML libraries link and I can therefore run the unit tests, I can’t do the same for my WCL, COM or MDBL libraries or projects dependent on them (i.e. everything). But that’s ok because it’s the static code analysis I’m after anyway.

My First Presentation

Last Thursday[+] saw me popping my “presentation” cherry as I stood up in front of a small crowd of around 35 people and waffle on for 60 minutes about unit testing database code (SQL). The size of the audience and the number of questions at the end and in the pub afterwards makes me feel that it can probably be chalked up as a success. I certainly got a buzz out of it and more importantly I’m glad to get one under my belt before the ACCU Conference in April where I’ll be presenting “Using xUnit As a Swiss Army Testing Toolkit”.

It was pretty obvious from my twitter feed that I was more than a little bit nervous and even though I had a few calming replies from my fellow ACCU members I was still somewhat pensive. The ACCU is full of very smart people and the topic on which I was speaking was not what I would consider one of my core skills and so I was well aware that the room would contain people far more knowledgeable than me on the subject. The nightmare scenario was one of those people asking the kind of ‘obvious’ question that would invalidate the entire talk. The basis for this is some of the ‘heckles’ that I’ve seen at past conferences and talks. Fortunately they were unfounded… this time.

So what did I learn from the experience? Well, it’s kind of nice being the centre of attention for an hour or so :-) The biggest thing, and I’ve said this before about blogging, is that it makes you think even harder about your topic so you feel doubly sure you don’t say anything really stupid. When first asked by Allan Kelly to do a talk for ACCU London I reluctantly said ‘yes’ as I find originality a big stumbling block – I’m always assuming that I need to say something novel. Because of the ACCU’s heritage[*] I didn’t want to say anything C++ related, I don’t know anything about Java, the iPhone, Python etc and anyway those angles are already well covered by other prominent members so that pretty much left something C#/SQL based. I also didn’t want to do a shorter version of my upcoming conference talk so I looked back over my blog for inspiration and realised I had a few posts relating to database development that I could probably stitch together into a coherent piece And so the following blog posts were fused together to the form the meat of the session:-

xUnit Style Database Unit Testing
Simple Database Build & Deployment With SQLCMD

…with the following posts providing some additional minor content:-

Debug & Release Database Schemas
Implementing Constants & Enumerations in a Database

Naturally I had extravagant plans when planning it 3 months earlier. I was going to develop my own test runner and follow Paul Grenyer’s fine example by running actual tests with a live demo. I was also going to have a few vibrant slides and follow Martin Fowler’s lead by eschewing bullet point lists and unnecessary images. And then Xmas came and went and like any real student the deadline was looming and still I only had a rough outline.

My wife, who fortunately knows a thing or two about presenting[#], and more importantly knows a thing or two about her lazy husband, provided some sage advice to get me bootstrapped. It didn’t take long to realise I was never going to have time for the live demos (either beforehand or during the talk for that matter) and I also dropped the ‘clever’ slides in favour of creating what were essentially a giant bunch of queue cards. Once I had gone through the entire talk for real I decided I probably shouldn’t try and run before I can walk…

This may seem really obvious to everyone else but there is a world of difference between talking in your head and saying it out loud. I spent a number of train journeys on my commute planning the talk and then going through it in my head, i.e. actually talking in my head to practice what I was going to say – I didn’t think my fellow passengers would be that interested in the content and they might also find it a little repetitive :-). So when I came to practice it out loud I got to hear all the pauses and umm’s and errr’s and it was truly disheartening. I practiced it again over the next few evenings and although the talk become more fluent, I was still annoyed with myself for forgetting to say certain things. My wife stepped in once more with another ‘obvious’ piece of advice “The audience doesn’t have a script, so they won’t know what you did and didn’t mean to say”. She also said that if a point was truly important I wouldn’t forget it. Boy it annoys me how that women is always right! I still ditched one slide right at the last minute as I realised the content was effectively irrelevant, it also was hard to convey fluently and would help bring me back to nearer the 60 minute mark.

On the night it seemed to go pretty much according to plan, except for the technical problems getting my laptop and the projector to acknowledge each other’s presence. I only remember losing my train of thought once and I believe I got all the key points in just like my wife said I would :-). I felt the urge on a number of occasions to ad-lib as I thought of something new or different to say but I held fast and stuck to the content I’d been practicing. If I ever get to do the talk again I guess I can always ‘refactor’[^] it. It was also nice to be able to point out two members of the audience (Phil Nash & Steve Love) whilst acknowledging their ‘indirect’ contributions.

After I had finished rabbiting there were a number of questions ranging from the classic “how do you get your developers to write tests?” to one from fellow member Ed Sykes about whether it’s possible to get code coverage for SQL based tests? No, I don’t know. In the pub afterwards we got to chew the fat further and a few more ideas popped into my head. The question now is whether the alcohol killed off too many brain cells or whether I can still recall what they were for future blogging purposes…

All-in-all a wonderful experience and hopefully an excellent bit of practice in preparation for the main event - the ACCU conference in April.

FWIW Here is a link to the slides (PowerPoint).

[+] The photo was taken by fellow ACCU member Schalk Cronje.

[*] There are members who sit on the C++ panel! They know more than than just their onions…

[#] She’s a producer of live events and one of her many talents is to train and rehearse speakers.

[^] I guess that means “the message” stays the same but I say it in a different way.

Intriguing SCHTASKS Bug

To get our system up and running quickly we chose to use the built-in Windows task scheduler to invoke our nightly batch. During initial development we added a couple of tasks by hand using the Scheduled Tasks UI, but as we got closer to our release date we created a batch file to script creation of the scheduled tasks so that we had a formal (and version controlled) deployment process, e.g.

C:\> SCHTASKS /CREATE /SC WEEKLY /D … /TN “RunBatch”

The script was written and tested on a Windows XP desktop machine, but when we came to test it on our Windows server the script failed with the following error:-

ERROR: The creation of the scheduled task failed.
Reason: The Task Name may not contain the characters: <, >, :, /, \,|.

This was most curious as the server was running Windows Server 2003 (which is the same family as XP) and the task names were all pretty simple using just a number, dash and short name, e.g. “01-EstablishValueDate” – there were no out-of-the-ordinary characters at all. Naturally I took the dash[*] out first – no luck. Then the numbers – nope. Finally I resorted to a binary search to try and pin down exactly which character(s) it was…

It turned out to be the letter ‘D’ at the start of the word “Date”! The first three tasks I tried all had the word “Date” in them somewhere and they all worked after just removing the letter ‘D’. To be more specific it was a capital letter D as a lower case ‘d’ worked! This became our temporarily workaround whilst I did a little Googling in the background as I just couldn’t entertain the idea that this was the real problem; I’ll always assume I’m doing something wrong until Occam's Razor tells me it’s not me :-). Fortunately I quickly discovered the following forum post “Error In Schtasks Command Line” where there author was having no joy in creating a scheduled task called “Defrag”. Sadly it doesn’t say they resorted to just calling their task “defrag” as a workaround. Anyway a short time later I managed to discover an MSDN article that appeared to confirm the behaviour. In the KB article it says the following:-

The Schtasks command verifies the task name by comparing the task name with an invalid character set. If the task name contains one or more characters from the invalid character set, the Schtasks command denies the task creation request.

However, in race conditions, the invalid character set may contain a valid character. Therefore, the Schtasks command cannot create some tasks even if the task names contain only valid characters.

My mind boggles at what the SCHTASKS code is doing to create such a race condition? And why just the letter D?

[*] A common source of error is pasting command lines out of Word documents or web pages into console windows; this often results in curious error messages from the application. The problem is the “clever” document editor converting a dash (en dash, minus sign, etc) into a proper hyphen (em dash) or equivalent Unicode character at a different code point from the ASCII one. It may look like a dash to you and me but the “computer says no”.