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.
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
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).
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:
- copy "$(TargetDir)$(TargetName)$(TargetExt)" "$(ProgramFiles)\Microsoft Visual Studio 9.0\VSTSDB\Extensions\$(TargetName)$(TargetExt)" /y
- copy "$(ProjectDir)$(TargetName).Extensions.xml" "$(ProgramFiles)\Microsoft Visual Studio 9.0\VSTSDB\Extensions\$(TargetName).Extensions.xml" /y
- "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:
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.
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"
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).
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.
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
Comments
Anonymous
January 01, 2009
PingBack from http://www.codedstyle.com/creating-t-sql-static-code-analysis-rules/Anonymous
January 05, 2009
GertD on Creating T-SQL Static Code Analysis Rule Amit Chatterjee on Rethinking Testing - Dev Test Challenges...Anonymous
January 05, 2009
Gert Drapers wrote a great detailed post on how to Create T-SQL Static Code Analysis Rules as one of...Anonymous
January 19, 2009
Hi Gert, Why is idNamespace specified in the constructor instead of through an attribute? Currently, it seems to be using a string as an instance field. This exposes the namespace as a string, and does not allow me to specify a strongly typed Uri. Furthermore, an attribute is in itself more strongly typed than a plain CLR string, because it has design-time semantics. Currently, I see no easy way to query at design time all the DLLs under my solution's project umbrella: How can I write reflective LINQ code that lets me query all assemblies for Rule types, grouping by idNamespace? It seems the only solution is to instantiate an object for each Rule, then query the object. There might be another way I'm missing, but I'm still a .NET novice (Java transplant). The other alternative is to create my own Microsoft.Data.Schema.Sql.IdNamespaceAttribute and apply it to my rules, but I would not slurp up Microsoft's rules and I would be violating the DRY principle since I still need to provide an identical IdNamespace string inside the constructor. Among other issues, the roll-your-own attribute approach makes renaming namespaces tedious.Anonymous
February 06, 2009
Earlier this week I presented at an internal conference on the topic of Database Project ExtensibilityAnonymous
February 07, 2009
[ Nacsa Sándor , 2009. február 8.] Ez a Team System változat az adatbázis változások kezeléséhez és teszteléséhezAnonymous
March 02, 2009
I tried to implement my custom rule, but I got an "DisconnectedContext was detected" error ("Context 0x158818' is disconnected. Releasing the interfaces from the current context (context 0x158988)...") while new instance of VS starts. Unfortunately, google doesn't know about this problem. Do you know what could cause this error? And even if I ignore this exception the breakpoints won't be set up.Anonymous
October 14, 2009
Hi all misters any knows any good tool for analyze sql files (Oracle, SQL Server...) ?? I have several SQL files in my csproj (in a solution) VS 2008, and I would like analyze SQL code. Can I extends NDepend tool for it ?? Any Addin for VS 2008 ?? thanks in advancedAnonymous
February 14, 2010
Hi Gert, Things seems to have change a little bit in vsdb 2010, I follow the tutorial provided on msdn (http://msdn.microsoft.com/en-us/library/dd172127%28VS.100%29.aspx#CreatingCustomCodeAnalysisRule) but still with no success, my rule still doesn't appear even if all seems to be correct. Do you try a similar project on VS2010 RC? Thanks!Anonymous
May 03, 2010
Hi Gert, what if I installed VSTS under d:program files while %ProgramFiles% refer to c:program files, the location is still %ProgramFiles%Microsoft Visual Studio 9.0VSTSDBMicrosoft.Data.Schema.Extensions.xsd? It seems not work. Thanks.Anonymous
March 05, 2012
Hi I followed ur rules for creating the custom static code analysis for sql in database project. In step -8, u mentioned break point will hit once we run the code.. But i didnt got any breakpoint interruption in my code, but another instance of visual studio is opened. Pls explain the step-6 elaborately.... Pls response to my commment as soon as possible