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?).

Unit Testing Evolution Part I - Structure

[This post has been under construction for over 6 months. Every time I think I’ve got it sorted I gain some new insight from somewhere and feel the need to re-evaluate my strategy. The last of these was Kevlin Henney’s ACCU London session which covers similar ground to this post but naturally in a far more intelligent fashion; this post essentially covers my journey towards similar conclusions.]

I’ll be perfectly honest and admit that the unit testing strategy employed within my personal class libraries can leave much to be desired. As I indicated in my previous post “My Unit Testing Epiphany” I see this whole area of the development process as a Rite of Passage. Just like many others I have written my own Unit Testing framework, mostly because I saw the standard xUnit ports as too verbose, but also because I wanted to derive the practices myself from first principles. I’ve no doubt also made many of the same errors and trodden the same paths in a mistaken belief that I can somehow ‘short-circuit’ the process by using a simpler style. Sadly “Real Time” has to pass for many of the consequences of these actions to sink in and the lessons to be learnt.

Genesis - Test Chaos

Initially I saw the essence of unit testing as the act of having tests in place to verify behaviour. In effect it was the regression testing aspect that grabbed me most due to my previous faux pas. Hence I slipped into the habit of just writing a bunch of asserts, which works adequately for very simple free functions with a blindingly obvious interface. The following example are tests from my template based string parsing function:-

TEST_EQUALS(parse<int>(“1234”), 1234);
TEST_EQUALS(parse<int>(“-1234”), -1234);
TEST_THROWS(parse<int>(“9999999999”));
. . .
TEST_THROWS(parse<double>(“XYZ”));
. . .

They should require no explanation as they have no setup, teardown or state. They are grouped by the template instantiation <type> and the tests run in a fairly obvious order: correct  input first, then the edge cases like MAX and MAX+1, additional whitespace and finally malformed input. Every time I’ve gone back to read them I can understand what is going on in little to no time.

However, the same cannot be said for my CommandLineParser class tests. These fall straight into the “write-only” category because they only made sense at the time I wrote them. Here is the simplest example:-

{
    static const char* args[] = { “test”, “--switch” };

    CommandLineParser parser(args);

    TEST_TRUE(parser.isSwitchSet(FLAG_SWITCH));
}

For testing class methods I used scopes to break the tests up rather than write separate functions as I felt that writing a function came with the burden of coming up with useful test function names[#]. I knew that each test needed to avoid side effects so using scopes also allowed me to ensure the test object was recreated each time and that I could use copy-and-paste to write the tests as the variable names could be reused without clashing. At the time I really believed this was a more efficient strategy. Probably the worst CommandLineParser test I wrote was this:-

{
    static const char* args[] = { “test”,
            “--single”, “value”, “/s:value”,
            “--multi”, “value”, “value”, “-f”  };

    CommandLineParser parser(args);

    TEST_TRUE(parser.isSwitchSet(SINGLE_SWITCH);
    TEST_TRUE(parser.isSwitchSet(MULTI_SWITCH);
    TEST_TRUE(parser.isSwitchSet(FLAG_SWITCH));
    TEST_TRUE(parser.arguments[SINGLE_SWITCH].size() == 1);
    . . .
}

This single test highlights the biggest problem with my strategy - that it’s impossible to tell what features are intentionally being tested. This means that changing it would be incredibly difficult as I may accidentally remove unobvious test paths; no wonder I can’t think of a name for the test method! It’s the Single Responsibility Principle again - each test should only test one feature at a time[#].

Just as an aside these are the behaviours that the test was verifying (I think!):-

1. A switch can be provided using the Windows “/switch” style
2. A switch can be provided using the Unix “--switch” style (with full switch name)
3. A switch can be provided using the Unix “-s” style (with short switch name)
4. A switch and it’s value can be separated by whitespace
5. A switch and it’s value can be separated by a “:” if provided using the Windows “/switch” style
6. A switch can be defined as single value but multiple occurrence (hence --single)
7. A switch can be defined as multi value (hence --multi)
8. A switch defined as allowing multiple values consumes all values up to the next switch or end of input.

As a final note the original test did not contain the trailing “-f” parameter. It was added when I discovered the implementation contained a bug in the handling of switches that took a list values. In the rush to get a test in place through which I could fix the code I performed the unforgiveable crime of extending an existing test with yet more responsibilities.

Using Comments to Document Tests

My initial reaction when revisiting these tests some time later was to use the natural tool for documenting code – the comment. This hardest part about this refactoring was working out what each test was supposed to be doing. However once again I was pleased that I had avoided introducing separate functions for each test as I felt that naming functions would be far more limiting that a free text comment and redundant in many simpler cases. Here’s how that earlier test now looked:-

{
    // Boolean switches shouldn’t need a value.
    static const char* args[] = { “test”, “--switch” };

    CommandLineParser parser(args);

    TEST_TRUE(parser.isSwitchSet(FLAG_SWITCH));
}

Strangely I still thought it was ok to test multiple features because I believed the comments would add the necessary documentation for when the time came to deal with a test failure. Of course sometimes the comments still ended up being terse because I struggled to describe succinctly what it was doing[#].

The comments wouldn’t appear on the test output without more macro magic. But I wasn’t really bothered because if a failure does occur the first thing I do is to run the test runner under the debugger and wait for the failing ASSERT to trigger it. Eyeballing the test and code seems pointless when one of the key benefits of unit tests is that they run very quickly.

Decomposing the Big Ball of Mud

As the collection of tests started to grow I ran into a few niggling problems. The first was that each test was not protected with it’s own try/catch block so one stray exception caused the entire set of tests to fail. Although not a massive problem because I would fix every failure ASAP, it did feel as though each test should be better isolated. The second was that as I started practicing TDD I felt it would be beneficial to use the command line to restrict the set of tests run on each change to just those of the class I was developing. I wasn’t sure, but I suspected (for debugging reasons) that I may even want to limit the run to an even smaller subset of tests. This lead to the following new style test definition:-

TEST_SET(StringUtils)
{
    const tstring MAGIC_VALUE = “<something common>”;
    . . .
TEST_CASE(StringUtils, intFormatAndParse)
{
    TEST_TRUE(Core::format<int>(INT_MIN)
              == TXT("-2147483648"));
    TEST_TRUE(Core::parse<int>(TXT(" -2147483648 "))
              == INT_MIN);
    . . .
    TEST_THROWS(Core::parse<int>(TXT("1nv4l1d")));
}
TEST_CASE_END
. . .
TEST_CASE(StringUtils, skipWhitespace)
{
    . . .
}
TEST_CASE_END
. . .
}

The TEST_CASE/_END macros added a try/catch block around each test so each set would always run to completion now. Although you might think it, TEST_CASE does not declare a function, it still just defines a scope which means that you can declare all your common immutable test variables at the top and they will be useable throughout the entire set of tests – still no separate SetUp/TearDown needed. This also gets around the restrictions in C++ when declaring and initializing static const members in class declarations[*].

The example above shows the new smaller grouping of tests. The TEST_CASE macro takes two arguments which I thought could be used as subcategories to help both in the filtering at runtime and to aid in the format of the output. I still wasn’t sure that I wouldn’t need to turn these into real functions at some point (as that’s what most other frameworks seem to do), so the TEST_CASE arguments are short and still adhere to class/function conventions. The multi-paradigm nature of C++ meant that whatever naming scheme I came up with I felt it would need to suit both free function and class/method based uses.

One other thing this refactoring sorted out was a minor problem of accounting. I counted test results based on the number of passed and failed asserts, rather than the number of actual test cases as individual cases weren’t defined originally. This meant the summary at the bottom could give you a much exaggerated view of how many tests you were writing (not that “number of tests” should be seen as a good measure of test coverage).

No Assert Macro Changes

I originally created only three assert macros out of laziness. I decided that the bare minimum would be TEST_TRUE() and TEST_THROWS(); I added TEST_FALSE() purely for symmetry. I know other test frameworks have a plethora of assert functions and support fluent syntaxes for writing clearer asserts, but with C & C++ you can capture the assert expression with the pre-processor with is also powerful. Given that I never really paid much attention to the output for each test anyway I was happy just dumping the raw assert expression out. Plus, as I said before, I go straight for the debugger the moment a failure occurs instead of staring at the console output looking for inspiration.

 

And so this is how things stayed whilst I started getting the hang of TDD at which point some more ‘test smells’ started developing and that’s the focus of part II.

 

[#] This will be the focus of part II.
[*] I’m not sure what the exact rules are but I believe you’re effectively limited to a subset of the primitive types, e.g.float and double is also excluded.