New SQL Tools - SS-Unit, SS-Cop & sql2doxygen

At my current client I’ve spent far more time working with SQL (and specifically SQL Server) then ever before; but that is probably quite apparent from the bias of blog posts over the last year or so. With any new venture you soon find yourself drawing on your past experiences and in this case I’ve been looking for some of the equivalent tools in the SQL world that I’d use normally use with C# & C++. But I’ve found them hard to come by. It’s possible I’m looking in the wrong places but it’s not as if there are a stream of vendor adverts either trying to distract me and that gives me a feeling it’s not worthy of a prolonged hunt.

There is another reason why I may have not searched overly hard, and that’s because I find it more fun to build something myself - it’s a great learning exercise. Mind you that’s not the right answer for my client where we have both money and far more important things to be doing than building tooling - assuming this is we can find the right alternatives[*]. What I’m releasing below are my clean-room[+] versions of some of the tools we knocked-up to get ourselves going.

Naturally as these begin to surpass what we lashed up they become 3rd party candidates themselves to take over the same duties and my client wins too because it no longer has to consider supporting the code internally. I’ve always made the source code available for my stuff so that others have a chance to tinker or fix bugs and these are no exception. Of course given that they are T-SQL based (or PowerShell in the case of sql2doxygen), it would be pretty hard for me to keep it secret anyway!

SS-Unit - SQL Server Unit

This is a pure T-SQL based unit testing framework. You write your unit tests, in T-SQL, in the familiar xUnit style and then execute them by invoking the SS-Unit test runner (a stored procedure). The framework has support for both the test and fixture level SetUp and TearDown helpers which are more prevalent in SQL tests because of the static data dependencies you often need to satisfy. It also distinguishes between being run interactively, in something like SQL Server Management Studio (SSMS), and being run in batch mode with, say, SQLCMD so that it can be integrated into your Continuous Integration system for automated SQL unit testing.

The .zip file package, which includes a trivial example database and the unit tests for the framework itself is available on my web site here on the SQL section page. There is also an online copy of the manual if you just want to have a nosy.

SS-Cop - SQL Server Schema Cop

Earlier this year I posted about “DbCop”, a StyleCop/FxCop like tool that a team-mate had put together for use on the project. That generated quite a bit of interest and a few people asked if there was any chance of it being released. As I point out in a footnote[+] that isn’t really an option so instead here is my take on the idea. Clearly FxCop does some pretty deep analysis and I’m not suggesting this is going to provide anything of that magnitude. But there are a number of checks that we can do that ensure style conventions are being adhered to and common mistakes, like forgetting a primary key, are avoided. I have a few other new ideas too, such as trying to detect when a foreign key might be missing.

The .zip package is available from the same SQL page as SS-Unit and also contains an online copy of the manual. Not unsurprisingly the unit tests for SS-Cop are written using SS-Unit and so you could consider it a more realistic example of how to use SS-Unit - SS-Cop was written using a Test-Driven Development (TDD) approach.

sql2doxygen - SQL Doxygen Input Filter

Funnily enough the chap that wrote DbCop was the same person that introduced me to Doxygen - a tool for generating API style documentation from source code. What attracted me was the fact that it could generate useful documentation even when no effort had been made by the developer! Better still was that with only a small change in your comment style Doxygen could handle so much more - no horrendous tags or mark up like you get with JavaDoc or the standard C# offering. There are still a bunch of special tags if you need them but they don’t detract from the comments which is what you need when you’re actually trying to read them as you understand the source code.

Anyway, Doxygen supports many programming languages out of the box, but sadly not SQL. However it does provide you with a mechanism for supporting any language as long as you can transform the source files into something Doxygen understands; these are called input filters and are configured with the INPUT_FILTER setting. This is the command line of a process or batch file to execute for each source file and in the case of sql2doxygen it’s a PowerShell script that turns T-SQL code into C.

The sql2doxygen .zip package contains the Doxygen filter script and a manual describing what T-SQL styles the script understands. It is of course available from the same page I’ve mentioned twice already above. Both SS-Unit and SS-Cop are documented with Doxygen using the sql2doxygen filter and so examples of the output are available online here and here.

 

[*] These tools are not deployed into production and so if we picked, say, an open source project that went stale and became unusable, or bought a tool for a lesser known vendor that went bust the direct impact on the business is none. There is of course an indirect impact because we use the tools to help us deliver faster and with better quality and that would be compromised.

[+] Although the stuff we knocked up at my client’s is not the kind of thing I could imagine they would ever try and turn into a product (IT is not their business for starters) the code is clearly their property. I also wouldn’t know where to start trying to get permission to release it. Then there is the whole Fred Brooks approach which is to just throw it away anyway and learn from your mistakes instead. And this is the approach I have decided to take here.

Unit Tests Obscure Dead Code

I’ve been having a bit of a clean-up in our codebase as I know of various large bits of functionality that for one reason or another never made it into production and now they are just becoming a distraction. Even though we are using the thoroughly useful ReSharper, which can point out some obvious dead code, it doesn’t perform enough analysis to see past code that is only referenced by unit tests.

Oh the irony… I spend all that time banging on about writing unit tests and now I’m cursing their existence because they stop me from seeing dead code! But my team is using NUnit, not some obscure framework, and ReSharper has native support for NUnit, so it’s not out of the realms of possibility that JetBrains could “join the dots” and provide a facility to highlight dead code that only has unit tests for it. If you’re writing a library I presume you’d want this feature off by default :-) but if you’re writing an application you’d probably like it on. Of course if you have a really large codebase and you’ve split it to ensure you only build what you need then I guess it becomes less accurate and false positives are not exactly a confidence builder. I’d still prefer to have the choice of enabling a “be aggressive” flag when I’m in cleanup mode.

Clearly there are other analysis tools, like NCover, that could help determine dead code, but I’m not talking about squeezing every last line of redundant code out (which would probably involve actually running it and then deciding whether you covered enough scenarios), but just pointing out the really obvious stuff like unused classes, methods, interface  implementations etc.

Anyway, until such time as JetBrains (or a suitable alternative) comes entirely to my rescue I’m using the following semi-automatic ReSharper assisted approach:-

1. Load the solution and remove all unit test projects from it
2. Use ReSharper to show up dead code
3. Delete dead code
4. Goto 2 to find what new dead code ReSharper has unearthed[+]
5. Reload the solution with unit test projects
6. Delete redundant unit tests
7. Fix any false positives[*]

Of course having said all that someone will go and point out that I should RTFM or show me how FxCop already does this… Please do!

 

[+] ReSharper uses the term “heuristically determined unused method”, and yet it still doesn’t point out a lot of dead code - you often have to go and delete the unused callers before it will mark something as dead. And these aren’t methods with deep call graphs, maybe only 1 or two deep. Perhaps it’s more pessimistic when interfaces are involved?

[*] Yes we have other dependent code (such as various PowerShell scripts) that means you can’t just carelessly swing the axe about but it’s pretty obvious what would be called externally.

A StyleCop/FxCop For Databases

My team all use the most excellent ReSharper during day-to-day C# coding as it performs some great on-the-fly static code analysis. We also occasionally run FxCop to provide some further insights into our C# code. However we didn’t really have anything for the database side of things, so a colleague of mine wrote one and called it ‘DbCop’. OK, so it’s clearly not in the same league as ReSharper but it has got me wondering if there are any commercial products out there that fill this space? I’ve not specifically hunted for one, but you still see things like this mentioned (if they’re worth anything) on sites like StackOverflow and personal blogs. I also dropped some not-so-subtle hints at the Red Gate stand at this years ACCU Conference but they didn’t give anything away if they do have anything like this in the pipeline...

Our tool is really nothing fancy, just some simple SQL scripts that spelunk the system tables and look at the schema metadata for some common mistakes and coding convention violations. It runs at the end of our database Continuous Integration build and generates a report; but it doesn’t fail the build if it finds a problem because we currently have no exclusion mechanism in place (besides manually hard-coding one into the script). We also have a separate schema/namespace called “build” for the DbCop objects so that they are not applied to integration test/system test/production databases by accident.

So far it only checks the following:-

• No reserved words have been used for the names of our objects
• The names of tables, columns and parameters adhere to our coding conventions
• Table columns are defined using our UDT’s, not primitive types
• Each table has a primary key
• Each table has a clustered index

Clearly rules like the last two are made to be broken, but as a general rule they are pretty sound. Personally I tend to avoid a Primary Key rule violation by writing a unit test in the first place that ensures duplicates are disallowed when appropriate. It would be great to include spell checking into the process (just like FxCop) because I’m forever misspelling identifiers and with a database it’s much harder to change table and column names after they’ve gone into production and contain millions of rows.

Using Visual C++ Together With GCC - Some Alternatives

My last post described how I have started using GCC 4.4 to compile my codebase so that I gain a little more portability, but more importantly I get some extra static code analysis without spanking my wallet. My Heath Robinson-esque solution involves using the free Code::Blocks IDE as it can import Visual C++ projects and solutions with minimal fuss, they are easy to maintain, and it's only an extra .cbp and .workspace checked into the SCM repository - so it's not messy.

As I mentioned before this was a suggestion from fellow ACCU member Steve Love, but since then I have had a some discussions on the accu-general mailing list to see what alternatives there are...

I guess the most obvious alternative would be Eclipse + CDT (C/C++ Development Tooling) as this mirrors my current setup of an IDE + GCC. Given the volume of development in Eclipse it also probably makes the most sense, but I have not had a good experience with Eclipse or CDT in the past - probably because I've been spoilt by the simplicity of installing and using Visual C++. I recently tried the CDT 5.0.x release but once again got lost in the Eclipse menus trying to configure it for my MinGW installation. This was in stark contrast to Code::Blocks where I had it configured in minutes. I guess I shouldn't be surprised that the learning curve of a tool as powerful as Eclipse is going to be much steeper, but it's just too steep for my VC++ biased plan at the moment. I should also point out that I haven't even opened the manual, so cries of RTFM are entirely valid and I only have myself to blame.

Both the Code::Blocks and Eclipse solutions rely on a separate IDE to manage the build process which is annoying because of the additional maintenance required to keep them in sync with the master Visual C++ projects. So, is there a way to drive GCC directly from the Visual Studio IDE?

A little bit of Googling during my earlier research led to a tool called gnu2msdev that translates the output of GCC to the same format that CL produces so that the Visual Studio IDE can navigate to lines with compilation errors. From this I discovered a number of posts suggesting the use of a makefile project, but they were more about using Visual Studio as an expensive text editor rather than compiling the same source with two compilers. It feels like there might be something possible along these lines and I'm going to continue investigating in the background.

I've never used the Intel C/C++ compiler, but another fellow ACCU member, Anna-Jayne Metcalfe of Riverblade, who knows a thing or two about integrating with Visual Studio, pointed out that Intel have managed the tight integration, so perhaps it would possible to do the same with GCC? I've looked at the MSDN documentation and it looks somewhat complicated for now :-) However, the signs are that it will be somewhat easier in the future as Microsoft have been slowly separating the IDE and build system so that it can support other languages and toolchains. The VS Project Team Blog has a number of posts on the changes to Visual Studio extensibility in VS2010 that show what they are up to.

For the moment I'm sticking with the Code::Blocks approach, not least because the .cbp file format is simple and I reckon I could solve the sync'ing problem with a bit of script. However I'm still keen to get into Eclipse because there is so much development clout behind it. Hopefully by the time VS2010 becomes my baseline someone else would have already written the GCC integration and I'll get another free ride :-)

GCC for the Visually C++ Impaired

At this years ACCU conference Steve Love gave a talk titled Why Portable Code?. It covered far more than just toolchains and platforms, but it reminded me of a previous chat I had with Steve about portability after one of the recent ACCU London gatherings. I've always liked the idea of writing truly portable code, but quite frankly in the corporate waters where I swim Visual C++ is The Big Fish and any ideas about using alternate toolchains to satisfy personal desires around "writing quality code" would be seen as frivolous - irrespective of whether it has long term benefits or not.

In my discussion with Steve I explained that I hadn't even looked at a Makefile in a long time (according to my personal SourceSafe repository it's 1997) and I didn't feel that I could introduce any other build system just to enable portability checks. What I wanted initially was a quick way of taking a C++ codebase and just running it through A.N. Other compiler. It wouldn't have to link or run, just allow me to check compilation. Steve's suggestion was Code::Blocks, an Open Source, cross platform IDE, that was obviously also free. It turned out to be much better than I had hoped for and has also given me a partial answer to my previous blog 'Where Are the "Lite" Editions of Static Code Analysis Tools?'.

As you can see from my earlier post 'Building Visual C++ Projects From the Command Line', I am not adverse to command line tools, but the thought of trying to use another toolchain, especially one with a Unix heritage like GCC, didn't exactly fill me with joy. The last time I had used G++ was on a twin-floppy Atari STE circa 1990 and it needed a Unix like OS to run it. Googling didn't fill me with any more confidence as I started to read about Unix emulation layers like Cygwin and I didn't want to have to learn another 'Shell' - I just wanted to be able to run the compiler from a Windows command prompt. The answer again turned out to be pretty simple - MinGW - a Win32 port of GCC for building Windows applications. Feeling cocky I also downloaded the Digital Mars C/C++ compiler as well after noticing support for it in the Code::Blocks UI.

Now, I only wanted the IDE for the build system, not the text editor. So I created a simple Hello World C++ console application in Visual C++ and then switched to Code::Blocks to see if I could get it to build. After hunting though the menus I discovered how to configure a toolchain (it had detected VS2005 Express automatically, but not my VS2003 Professional) and noticed that it had an option to import a Visual Studio Solution or Visual C++ Project. I picked the former and played around in the UI to see what it had done. Amazingly I hit build and it worked... and ran too! I discovered how you switch compilers on a project and pointed it to my MinGW 3.4.5 installation and lo-and-behold it also worked (except for some warnings about unknown compiler switches which is an artifact of switching tools). I tried the Digital Mars compiler, after also downloading STLport, and it compiled, but didn't link (I discovered later that I had to specify the stlport .lib manually). I then got hold of the MinGW 4.4.0 release, but now it all went horribly wrong. However that was entirely my fault due to a lack of RTFM'ing the release notes. It was actually a blessing as I discovered the TDM GCC builds as an alternative to MinGW and it's "On-Demand Installer" worked out-of-the-box and better suits a heathen like me.

With my new found confidence I decided to take my Core library, which is the more platform agnostic part of my framework and give it a whirl with GCC 3.4.5 (I didn't have 4.4.0 working at that point). Once again I used the import feature of Code::Blocks to configure the build and it complained quite loudly at my code, which I kind of expected :-) But all the problems were quite simple and related to my use of Visual C++ specific #pragma's in the common build configuration header files. Once these were fixed the rest of the code was pretty sound - with GCC 3.4.5 that is. GCC 4.4.0 being much newer and stricter had a LOT more to say! I am going to blog about the changes I made because I was pleasantly surprised at how little I needed to change on the structural front to enable GCC support, but also the kinds of issues 4.4.0 has raised are squarely in the Static Analysis arena and not reported by any version of Visual C++ up to 9.0 (i.e. VS2008).

I feel somewhat annoyed that it has taken me so long to truly see the light. In Steve Love's talk he identified three stereotypes - The Ignorant, The Skeptic & The Zealot. I don't believe I'm any single one of those, as I've always agreed with the principle, but I probably share traits from all of them and add in a dose of procrastination for good measure. As I write this I'm just finishing up porting the oldest part of my framework, my Windows Class Library, and as soon as that is done I'll write some posts about the discoveries I've made.

Where Are the "Lite" Editions of Static Code Analysis Tools?

When I started out, the compiler I was using was set to build on warning level 2 (it was MS C600) which pretty much just told you if your code was well formed or not, and that was all I cared about then. Fortunately whilst working there the company discovered Writing Solid Code by Steve Maguire along with Code Complete by SteveMcConnell. One of the practices Steve Maguire suggests is cranking the diagnostic level up on your tools to maximum and leaving it there - pretty much a best practice these days. The net effect of this is to enable the static code analysis within the compiler to highlight your 'valid', but potentially dubious code, giving you a chance to fix it before it becomes a bona fide bug. Yes, sometimes it gets it wrong, but there is nearly always a way to rewrite the code to silence the compiler (preferable) or a sledgehammer in the form of a pragma to disable the warning for a small section of code.


But compilers are first-and-foremost seen as a build tool - the static code analysis abilities are a nice-to-have feature that has probably dropped out as a by-product. For full-on code analysis you apparently need an industrial strength tool, which quite probably comes at an industrial strength price. Now I'm sure these tools have taken a considerable investment and therefore demand a high price, which is fine for a big corporate client who can also afford the training required, but for smaller outfits and freelance developers the cost is prohibitive. Nowadays I can get started in C++ development using good quality free tools like Code::Blocks for the IDE and GCC for the compiler suite or even the all-in-one: Visual Studio Express. But what about when I need to take my skill further so that I can improve the quality aspects of my coding?


At various clients in the past I have used tools like BoundsChecker (now DevPartner Studio) and Rational PurifyPlus to check for memory leaks, buffer overruns, uninitialised variables etc. and they have been useful, but I don't feel it was the most efficient use of my time. They are a great way of tracking down a specific issue, but not at all the right tool for continuously sanity checking the codebase. On the other hand one of the oldest and potentially useful tools for doing this with C++ is probably PC-Lint (something I am keen to get my hands on - more so since going to Anna-Jayne Metcalfe's ACCU talk Taming the Lint Monster). And even Microsoft have got in on the act and added an /Analyze switch to its Visual C++ compiler - but only in the Enterprise edition.

Code Analysis (Static or Dynamic) is a noisy business, meaning that you need to be proficient in the tool to make the best use of it. But to do that requires some serious time with it and many businesses just wont be able to justify the cost of both the tool and time required for training. And this is normally where those "Lite" editions come into play. Freelancers (and hobbyist coders) like myself, who often have the ability to affect buying decisions would have considerably more weight if we were able to demonstrate the real benefits because they were a natural part of our toolset.

One client I worked at lost many man days due to uninitialised member variables, something I know at least one static code analysis tool points out. Personally I get annoyed when I see a bug that I know a tool could have prevented. What I really wanted to introduce into the build process was a step to run a short static code analysis job before the build and then later, as we got more proficient with the tool, add a weekly step to run a much deeper analysis. The stumbling block was that I could not even get a trial version of the product I wanted to use so that I could play in my own time to build a list of current defects to use to justify the cost.

Having the knowledge of certain products on my CV naturally helps make me more marketable, but more importantly, as a professional I feel I have another weapon in my arsenal to help ensure the quality of my code.