SSDT March 2014 Static Code Analysis Extensibility

Static Code Paralysis.

Coding standards (and more latterly styles) in any organisation of any size are liable to incite religious wars and makes zealots out of even the most considered or apathetic of us.   Inconsistency and subjectivity,  lack of enforcement and heavy handedness all contribute to the contempt often leveled at them.  

Sod it.

I say sod all that, if you or your company aren't using static analysis tools  then you are missing out on improved code quality and productivity (borne out from a consistent approach) and reducing the most banal and mind numbing conversations and sometimes pissing contests associated with discussing the minutiae of standards.  

As an aside, having a few years under my belt  in the industry  I've found that people who "really" moan most about this are usually rabble-rousing morale sappers who like to moan about everything, so it stops them doing this and maybe might lead to them getting on with work instead!

Makes things easier for everyone involved - really.


Barring any major clangers or anything truly detrimental to program performance and readability if there is a consistent view of coding standards and styles which are automatically enforced,  and can - within reason - be suppressed and challenged from time to time to aid with best practice, then the pain of the standards largely goes away and it becomes something that just happens is accepted and then more important things which should be reasoned about -  are.

Currently, in our organisation we use a combination of  StyleCop, Resharper, and FxCop for our .NET static code analysis needs and its generally a pretty edifying experience, even though there were a few integration issues which were quickly resolved. 

The SQL parallel universe.


There is one big place (at least) where all of the pain and fundamentalism, mentioned above, still exists, the database Universe.   Our team have taken the initiative to get code analysis into the enterprise for new SQL development in order that our data team has less reasons to pull releases, saving many thousands of pounds and delay in the process, for missing a few spaces and tabs (yes this stuff really does still happen).  

Moving the identification of this type of "defect"  back to the development life cycle, where it belongs,  in itself saves cost, egg on peoples faces and confidence from the exec that the IT department aren't a gang of idiots. 

SSDT Code Analysis Extensibility

With the SSDT March 2014 release, comes the ability to extend (once again - its been missing from the last few releases) the code analysis rules for Visual Studio 2012 and 2013, that you get out of the box.   

The MSDN example application for extending the rules is good  (Authoring an SSDT Rule the accompanying documentation for the some of the types used in the API could be better, which meant having to debug to find out what some of the TSqlFragments were, which was a little time consuming.
  
A very important thing to note is that you will need to install the SSDT 2014 March release in order to debug and execute the code analysis sets you develop.  Also the folder which is probed for the extensions is in the SQL extensions folder 
  • <Microsoft SQL Server Install Dir>\120\DAC\Bin\Extensions
NOT the Visual Studio extensions folder 
  • <Visual Studio Install Dir>\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Extensions
I couldnt get the latter location to work after a number of attempts. 

That being said, once the sample is up and running getting yourself going is a breeze.  

The analysis is facilitated by dog-fooding  SQL scripts under scrutiny, into a set of strongly type TSqlFragments  which are themselves plopped into an AST.  The TSqlFragments  allow for extensibility via use of the visitor pattern, accepting TSqlFragmentVisitors which works really well.  

This arrangement also allows for fine grained control and the ability to have scope, such that a BooleanExpression on a JOIN can be treated differently to one in a WHERE clause, by nesting visitors.  

The visitors are used by rules derived from SqlCodeAnalysisRule which can look at the fragments and allow developers to codify their organisation's take on what they should look like or whether or not they should be present.  For example, ensuring SELECT * isn't used or that column numbers aren't used in ORDER BY clauses.  

Errors and warnings are presented to users by building up a SqlRuleProblem giving appropriate context and severity. 

The TSqlFragmentVisitor has over 800 overloads which, incidentally,  rips ReSharper intellisense to shreds. It allows developers to visit on nearly every conceivable element type of a SQL script, and do something with it. 

Developing rules can be time consuming especially when considering specific formatting rules.

Its quite conceivable that a tool could be written atop of this API to code gen the rules, especially the formatting ones. This would be a great toy project.  

As I write this I have just come across TSQL Smells by Dave Ballatyne
TSQL code smells in SSDT 
There are 43 rules (compared to the stock 15 or so in Visual Studio) you can utilise, it is open sourced and can be extended or used as a leg up for your own stuff.

One last advantage and compelling reason to use SSDT is the fact it will work with msbuild, with little or no extra config other than ticking a tick box and ensuring the library is accessible on your build agents,  a large consideration when assessing your needs. This will ensure CI is subject to the same static code analysis as local development envs.

In Summary.

SSDT isn't the last word on SQL static code analysis, there are other tools available such as a Red Gate offering, SQL Enlight and SQL Code Guard to name but a few (and there really are only a few) but all of these have been judged (in relation to our needs),  after recent research by my colleagues to have various shortcomings (from price, incomplete features, to lack of integration in Visual Studio) in comparison to SSDT. 

Given all of this it seems that SQL static code analysis is still an immature area and hopefully one which will see more attention in the not too distant future, and hopefully I don't have to author too many rules....










Comments

  1. Hi Colin, thanks for taking the time to write this post. I work on the SSDT team and have a few questions / comments.

    The extension location bug will be fixed in the next SSDT release (so you can install into \Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Extensions).

    I'd be very interested to knowmore about your issues with the API documentation. Which were the main areas you ran into trouble with - the actual code analysis APIs, model APIs etc.? This is something we can look into improving in future releases so we'd really like to understand where to focus our attention. You can contact kcunnane [at] microsoft [dot] com if you have detailed comments, we'd appreciate any feedback.

    Finally we have some samples ourselves at http://dacsamples.codeplex.com/. These cover using the model APIs in addition to TSqlFragmentVisitors, and can be useful in some cases where it would be hard to write the rule using the visitor pattern. Thanks,

    Kevin

    ReplyDelete
    Replies
    1. Thanks for the heads up regards the examples. Ill drop you a line,

      Delete
  2. Hi Kevin, I am trying to identify keywords having to be uppercased as part of the static code analysis I am doing. Problem is, I would need to override every single method which seems overkill for what I want to do. I want to be able to capture each fragment but the major problem here is that the TSqlFragment prevents me from setting the line and column number. Is there a way round this?

    ReplyDelete
    Replies
    1. Hey, get the ScriptTokenStream of TSqlFragment, iterate over it via a for each. Inside the foreach use the IsKeyword()-Method.

      Roman

      Delete

Post a Comment

Popular posts from this blog

Book Review: Test Driven Development By Example (Kent Beck)

Testing, Testing, 1.2.3.4.5.6..ad infinitum