Creating T-SQL Static Code Analysis Rules

One of the new features in the GDR release is that you can create your own T-SQL Static Code Analysis rules. This post will guide you through creating your first rule.

 

Step 1: Create a new Class Library project

Inside Visual Studio 2008 create a new "Class Library" project, you can use VB.NET or C#, however since I am VB.NET literate (sorry no offense) I will use C# for this example.

image

We will name the project "SqlRule".

 

Step 2: Add references

The next step is to add the required assembly references to the following assemblies:

  • Microsoft.Data.Schema
    • %ProgramFiles%\Microsoft Visual Studio 9.0\VSTSDB\Deploy\Microsoft.Data.Schema.dll
  • Microsoft.Data.Schema.Sql
    • %ProgramFiles%\Microsoft Visual Studio 9.0\VSTSDB\Deploy\Microsoft.Data.Schema.Sql.dll

image image

The Microsoft.Data.Schema assembly contains the core infrastructure, while the Microsoft.Data.Schema.Sql  companion contains the SQL Server specific code

Step 3: Sign the assembly

In order to be able to place the assembly in the GAC, you first need to sign it. In the project properties, go to the Signing tab and create a Strong Name Key file named SqlRule (use a password of your choice).

image

Step 4: Add Extensions.xml file to project

Add a new XML file to the project named: "SqlRule.Extensions.xml"

 

Step 5: Update Extensions.XML file content

In order to register the rule with the system, we need to create an XML file which follows the naming convention of assembly name.extensions.xml and is placed in the VSTSDB\Extensions directory.

This extensions .XML file contains the following XML:

<?xml version="1.0" encoding="utf-8"?> <extensions assembly="" version="1" xmlns="urn:Microsoft.VisualStudio.TeamSystem.Data.Extensions" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:Microsoft.VisualStudio.TeamSystem.Data.Extensions Microsoft.Data.Schema.Extensions.xsd">

    <extension type="SqlRule.UpperCasedTableRule"                assembly ="SqlRule, Version=1.0.0.0, Culture=neutral, PublicKeyToken=????????????????"                enabled="true"/>

</extensions>

Make sure the XML file references the XML schema declared in the %ProgramFiles%\Microsoft Visual Studio 9.0\VSTSDB\Microsoft.Data.Schema.Extensions.xsd. Since the assembly will need to be registered in the GAC it needs to be signed, so make sure to add the correct PublicKeyToken information, which will depend on the key used to sign the assembly.

 

Step 6: Add post build events

Next step is to add three post build events to make sure that the assembly created is copied to the correct location and that the extension manager will pick it up and load accordingly.

Add the following post build steps:

  1. copy "$(TargetDir)$(TargetName)$(TargetExt)" "$(ProgramFiles)\Microsoft Visual Studio 9.0\VSTSDB\Extensions\$(TargetName)$(TargetExt)" /y
  2. copy "$(ProjectDir)$(TargetName).Extensions.xml" "$(ProgramFiles)\Microsoft Visual Studio 9.0\VSTSDB\Extensions\$(TargetName).Extensions.xml" /y
  3. "C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\gacutil.exe" /if "$(ProgramFiles)\Microsoft Visual Studio 9.0\VSTSDB\Extensions\$(TargetName)$(TargetExt)"

Inside the post-build event editor it should look like this:
image

Event 1 copies the generated assembly to the VSTSDB\Extensions directory; 2 copies the XML file that makes the extension known to the system and 3 registers the assembly containing the extension in the GAC (Global Assembly Cache).

 

 

Step 7: Implement the Rule class

Now that we are all setup, we will add a class to implement the rule. The project already contains a class, named Class1, so we will rename the class to UppercasedTableRule. For consistency sake you might want to rename the file name from Class1.cs to UppercaseTableRule.cs as well.

In step 7 we change will the class so at the end you have a template class from which you can start a new rules!

Step 7A: Add using statements

In order to use the be able to access the namespaces more easily add lines 6-13 to you class definition.

    1:  namespace SqlRule
    2:  {
    3:      using System;
    4:      using System.Collections.Generic;
    5:   
    6:      using Microsoft.Data.Schema.Extensibility;
    7:      using Microsoft.Data.Schema.SchemaModel;
    8:      using Microsoft.Data.Schema.SchemaModel.Abstract;
    9:      using Microsoft.Data.Schema.ScriptDom;
   10:      using Microsoft.Data.Schema.ScriptDom.Sql;
   11:      using Microsoft.Data.Schema.Sql.SchemaModel.SqlServer;
   12:      using Microsoft.Data.Schema.Sql.SqlDsp;
   13:      using Microsoft.Data.Schema.StaticCodeAnalysis;
   14:   
   15:      internal class UppercaseTableRule
   16:      {
   17:          public UppercaseTableRule()
   18:          {
   19:          }
   20:      }
   21:  }

 

Step 7B: Add provider support

The underlying architecture of the GDR has been changed to a provider based model, a feature extension like a T-SQL Static Code Analysis rule can express to the underlying extension manager which providers it supports. For example if you want to create a rule that only applies to SQL Server 2000 you can indicate this by using the following attribute:

[DatabaseSchemaProviderCompatibility(typeof(Sql80DatabaseSchemaProvider))]

If you want to indicate that the rule supports all SQL Server versions you can indicate this by using:

[DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]

If you do not care you can simply indicate you support any provider by using:

[DatabaseSchemaProviderCompatibility(typeof(DatabaseSchemaProvider))]

The reason this works is because existence of the following inheritance relationship between these classes: Sql80DatabaseSchemaProvider (and Sql90DatabaseSchemaProvider and Sql100DatabaseSchemaProvider) inherit from SqlDatabaseSchemaProvider, which itself inherits from DatabaseSchemaProvider which implements the IExtension interface. This way the rule writer has full control in expression which versions are supported and not supported.

In our sample rule want to work with all SQL Server providers/versions so your code will look like this (see line 15):

    1:  namespace SqlRule
    2:  {
    3:      using System;
    4:      using System.Collections.Generic;
    5:   
    6:      using Microsoft.Data.Schema.Extensibility;
    7:      using Microsoft.Data.Schema.SchemaModel;
    8:      using Microsoft.Data.Schema.SchemaModel.Abstract;
    9:      using Microsoft.Data.Schema.ScriptDom;
   10:      using Microsoft.Data.Schema.ScriptDom.Sql;
   11:      using Microsoft.Data.Schema.Sql.SchemaModel.SqlServer;
   12:      using Microsoft.Data.Schema.Sql.SqlDsp;
   13:      using Microsoft.Data.Schema.StaticCodeAnalysis;
   14:   
   15:      [DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]
   16:      internal class UppercaseTableRule : Rule
   17:      {
   18:          public UppercaseTableRule()
   19:          {
   20:          }
   21:      }
   22:  }



Step 7C: Inherit from base Rule class and add the constructor

Next step is to have your class inherit from the base Microsoft.Data.Schema.StaticCodeAnalysis.Rule class. See line 16.

The Rules base class implements the Microsoft.Data.Schema.Extensibility.IExtension interface which is used by the extension manager to identify loadable extensions.

After we inherited our class from the Rule base class, it is time to implement the constructor of our class. Since we inherit from rules we need to call one of the two constructors implemented in the base class as a result.

    1:  // Summary:
    2:  //     Initializes a new instance of the Microsoft.Data.Schema.StaticCodeAnalysis.Rule
    3:  //     class.
    4:  //
    5:  // Parameters:
    6:  //   idNamespace:
    7:  //     Specifies the namespace string of the rule
    8:  //
    9:  //   id:
   10:  //     Specifies a short string to uniquely identify the rule inside a namespace
   11:  //
   12:  //   name:
   13:  //     Specifies a string displayed to the user to identify the rule
   14:  //
   15:  //   description:
   16:  //     Specifies a more-detailed description for the rule
   17:  //
   18:  //   helpUrl:
   19:  //     Specifies the URL of a Web page that contains more information about the
   20:  //     rule
   21:  //
   22:  //   helpKeyword:
   23:  //     Specifies a help topic for the rule
   24:  protected Rule(string idNamespace, string id, string name, string description, string helpUrl, string helpKeyword);

Lines 18 - 25 show you how to pass on the information to the base class constructor, defining a unique ID within a namespace and providing a name for your rule. Each rule writer must pick their own unique idNamespace to separate out your rules with rules from others and Microsoft, this is your namespace that you should re-use. Within your namespace you must provide a unique id for each rule, to prevent collision inside the namespace.  For this reason the idNamespace and id parts should never be localized and should remain stable cross languages. The description, helpUrl and helpKeyword parts are optional.

    1:  namespace SqlRule
    2:  {
    3:      using System;
    4:      using System.Collections.Generic;
    5:   
    6:      using Microsoft.Data.Schema.Extensibility;
    7:      using Microsoft.Data.Schema.SchemaModel;
    8:      using Microsoft.Data.Schema.SchemaModel.Abstract;
    9:      using Microsoft.Data.Schema.ScriptDom;
   10:      using Microsoft.Data.Schema.ScriptDom.Sql;
   11:      using Microsoft.Data.Schema.Sql.SchemaModel.SqlServer;
   12:      using Microsoft.Data.Schema.Sql.SqlDsp;
   13:      using Microsoft.Data.Schema.StaticCodeAnalysis;
   14:   
   15:      [DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]
   16:      internal class UppercaseTableRule : Rule
   17:      {
   18:          public UppercaseTableRule()
   19:              : base(
   20:                  "SqlRule",                                  // idNamespace
   21:                  "SR0001",                                   // id
   22:                  "Check if all table names are uppercased",  // name
   23:                  "",                                         // description
   24:                  "",                                         // helpUrl
   25:                  "")                                         // helpKeyword
   26:          {
   27:          }
   28:      }
   29:  }

 

Now we are two steps away from being able to build and debug our rules basic framework. We need to implement two overrides in the base class, the IsSupported and Analyze methods. For now we will implement them by throwing a not implemented exception.

This is how our class looks after implementing the overrides:

    1:  namespace SqlRule
    2:  {
    3:      using System;
    4:      using System.Collections.Generic;
    5:   
    6:      using Microsoft.Data.Schema.Extensibility;
    7:      using Microsoft.Data.Schema.SchemaModel;
    8:      using Microsoft.Data.Schema.SchemaModel.Abstract;
    9:      using Microsoft.Data.Schema.ScriptDom;
   10:      using Microsoft.Data.Schema.ScriptDom.Sql;
   11:      using Microsoft.Data.Schema.Sql.SchemaModel.SqlServer;
   12:      using Microsoft.Data.Schema.Sql.SqlDsp;
   13:      using Microsoft.Data.Schema.StaticCodeAnalysis;
   14:   
   15:      [DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]
   16:      internal class UppercaseTableRule : Rule
   17:      {
   18:          public UppercaseTableRule()
   19:              : base(
   20:                  "SqlRule",                                  // idNamespace
   21:                  "SR0001",                                   // id
   22:                  "Check if all table names are uppercased",  // name
   23:                  "",                                         // description
   24:                  "",                                         // helpUrl
   25:                  "")                                         // helpKeyword
   26:          {
   27:          }
   28:   
   29:          public override bool IsSupported(Microsoft.Data.Schema.SchemaModel.IModelElement element)
   30:          {
   31:              throw new NotImplementedException();
   32:          }
   33:   
   34:          public override IList<Problem> Analyze(Microsoft.Data.Schema.SchemaModel.IModelElement modelElement, RuleSetting ruleSetting, RuleExecutionContext context)
   35:          {
   36:              throw new NotImplementedException();
   37:          }
   38:      }
   39:  }

Before we start making our rule implement something meaningful, lets check if we can build, deploy and debug our rule!

 

Step 8: Debugging a SQL Static Code Analysis rule

Before we start our debugging session, place 3 breakpoints, one on the constructor (line 18), the IsSupported (line 29) and Analyze (line 34) methods.

image Next we need to define the debug host, we are going to use Visual Studio to debug Visual Studio, in order to do this, go to the project properties and define the "Start Action" and point it to use an external program, using  "%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe"

image 

Now you can start debugging. This will start a second instance of the Visual Studio shell.

No breakpoints will fire until you open a Database Project (.dbproj) file. Assuming copy operation of the assembly and Extensions.xml file were copied correctly to the Extensions directory and the assembly was correctly registered in the GAC, the first breakpoint that will fire is during the load if the database project. At that point in time the extension manager will check if assemblies inside the Extensions folder, based on the information provided by the Extensions.xml files inside this directory.

Call Stack: Extension loader, loads the extension

     SqlRule.dll!SqlRule.UppercaseTableRule.UppercaseTableRule    C#      [Native to Managed Transition]         [Managed to Native Transition]         mscorlib.dll!System.RuntimeType.CreateInstanceSlow         mscorlib.dll!System.Activator.CreateInstance         Microsoft.Data.Schema.dll!Microsoft.Data.Schema.Extensibility.ExtensionTypeLoader.InstantiateType<Microsoft.Data.Schema.StaticCodeAnalysis.Rule>         Microsoft.Data.Schema.dll!Microsoft.Data.Schema.Extensibility.ExtensionTypeLoader.InstantiateExtension<Microsoft.Data.Schema.StaticCodeAnalysis.Rule>         Microsoft.Data.Schema.dll!Microsoft.Data.Schema.Extensibility.ExtensionHandle<Microsoft.Data.Schema.StaticCodeAnalysis.Rule>.Instantiate         Microsoft.Data.Schema.dll!Microsoft.Data.Schema.StaticCodeAnalysis.StaticCodeAnalysisEngine.LoadRules         Microsoft.Data.Schema.dll!Microsoft.Data.Schema.StaticCodeAnalysis.StaticCodeAnalysisEngine.LoadRules         Microsoft.Data.Schema.dll!Microsoft.Data.Schema.StaticCodeAnalysis.StaticCodeAnalysisEngine.LoadRules         Microsoft.Data.Schema.Sql.dll!Microsoft.Data.Schema.Sql.SchemaModel.SqlServer.SqlSchemaModel.InitializeValidator         Microsoft.Data.Schema.Sql.dll!Microsoft.Data.Schema.Sql.SchemaModel.SqlServer.Sql100SchemaModel.CommonConstruction         Microsoft.Data.Schema.Sql.dll!Microsoft.Data.Schema.Sql.SchemaModel.SqlServer.Sql100SchemaModel.Sql100SchemaModel         [Native to Managed Transition]   

This is your first validation to make sure you extension is copied to the right location, registered in the GAC and that your Extensions.xml file is correct. If you not breakpoint fires in the constructor, you are not setup correctly. It makes no sense to continue in that case, you have to fix it first. One common problem I have had is when running on Vista or a later OS, is that you need to make sure the post build event copies the files correctly. If you get am access denied error in the post build event, try running the Visual Studio using the "Run as administrator" option.

Now that we can debug and validated that our rule is loaded by the extension manager, we can check the Code Analysis configuration property page (Data->Static Code Analysis->Configure).

image

If everything works as planned, your rule should show up. If not check your Extensions.xml file content, make sure your public key is correct and the naming of your assembly and class are correct.

So far we have only setup the system. This is a great point to save your work, if you want to use this as a template, for other rules you want to develop later. Now we will continue by implementing the to remaining functions the IsSupported() and Analyze() methods which contain the meat of the rule.

 

Step 9: Implement the IsSupported() method

The IsSupported() method is used to indicate if a element should be analyzed or not. In our case we simply have to test if the element is a table, we can do this by checking if the type of element is an ITable.

    1:  public override bool IsSupported(Microsoft.Data.Schema.SchemaModel.IModelElement element)
    2:  {
    3:          return (element is ITable);
    4:  }

 

Step 10: Implement the Analyze() method

The analyze method contains the actual rule implementation, here you have to evaluate whatever you want the rule the check for, if the rule fails, you return one or more probems, by returning an IList of Problem class instances.

Lets walk through the final version of our simple rule.

    1:  public override IList<Problem> Analyze(
    2:      Microsoft.Data.Schema.SchemaModel.IModelElement modelElement, 
    3:      RuleSetting ruleSetting, 
    4:      RuleExecutionContext context)
    5:  {
    6:      if (modelElement.Name == null)
    7:      {
    8:          return null;
    9:      }
   10:   
   11:      List<Problem> problems = new List<Problem>();
   12:   
   13:      IList<string> nameParts = modelElement.Name.Parts;
   14:   
   15:      // test if table name is uppercased
   16:      if (string.Compare(
   17:          nameParts[nameParts.Count - 1],
   18:          nameParts[nameParts.Count - 1].ToUpper(),
   19:          false) != 0)
   20:      {
   21:          string message = string.Format("Table name [{0}].[{1}] is not uppercased.",
   22:              nameParts[0],
   23:              nameParts[1]);
   24:   
   25:          Problem p = new Problem(this, message, (IModelElement)modelElement);
   26:          problems.Add(p);
   27:      }
   28:   
   29:      return problems;
   30:  }

Lines 6-9: check if the model element passed in to the rule analyzer is named. In our case we only fire the rule for ITable objects, and tables have to be named, but in case your are evaluating more objects, you can be handed an unnamed object.

Line 11: Create an list of problems to return, by returning an empty list you indicate the rule did not detect any violations of the rule

Line 13: Our rule needs to check if the name of the table is all uppercase, so we ask the element for its name parts. Since objects can have 1 to 3 name parts the Parts property returns an IList<string> with all the name parts.

Line 16-19: This is the actual test if the name is formatted according to the rules definition. We only want to test the table name, not the schema name, so we only are interested in the last name part.

Line 21-26: If the test fails, the name does not consist of all uppercase letters, we create an error message, pass the message together with the offending element to the constructor of the Problem class, and add the Problem instance to the problems list.

Line 29: When the rule is finished it returns the list of problems.

 

If you are creating a table name my_TABLE, executing the rule will result in the following output in the error list.

image 

This brings us to the end, you have build you first T-SQL Static Code Analysis rule! You can download the sample project SqlRule_v1.zip from my SkyDrive.

I hope this sample helps you getting started writing your own rules.

-GertD