Database Edition Custom Rule

One of the coolest feature of Visual Studio Database Edition is the ability to run Rules against a database project, like fxcop against a c# project. And the coolest part about this feature is that is really simple to write a custom rule.

Rules are important, I’ve seen project where there is no naming rule for objects in database, and so you will end with tables with column like, ablr_xxxx or xxxxx_xxx_Authorname etc etc, and the whole database looses consistency over time. Thanks to database edition I can create a custom rule that force all developers to use for example a standard naming scheme for the column.

Creating a rule is really simple, first of all create a c# project strongly signed, then add a class like this one.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

using System;
using System.Collections.Generic;
using Microsoft.Data.Schema.Extensibility;
using Microsoft.Data.Schema.SchemaModel;
using Microsoft.Data.Schema.SchemaModel.Abstract;
using Microsoft.Data.Schema.ScriptDom.Sql;
using Microsoft.Data.Schema.Sql.SchemaModel.SqlServer;
using Microsoft.Data.Schema.Sql.SqlDsp;
using Microsoft.Data.Schema.StaticCodeAnalysis;

[DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]
internal class MyStylePrefixedTable : Rule

To give visual studio full details about your new rule, simply use the right constructor.

1
2
3
4
5
6
7
8

public MyStylePrefixedTable()
: base(
     "SqlRule",
     "DD0002",
     "All columns must be in form xxxx_xxxxxxxx",
     "All columns must be in form xxxx_xxxxxxxx where the part befor the _ is table code, and part after is descriptivename",
     "",
     "MyStylePrefixedTable")

As you can verify is really simple to define details of the rule, just give a the namespace where the rule resides then a unique code, name and description and some other information like help location.

A rule is a simple class that inherits from Microsoft.Data.Schema.StaticCodeAnalysis.Rule, then you simply need to specify what kind of object you want to check.

1
2
3
4

public override bool IsSupported(IModelElement element)
{
    return element is IColumn;
}

This specific rule has the purpose to enforce a specific naming of all columns of database. Each column must in fact contain 4 letters (table code) then an underscore and then the name of the column. If we have the table Customer the id column will be cust_id. This naming scheme could not be the best one, but I want it to be enforced into the database, because I really dislike that each table has is own naming scheme. All the check is done in the Analyze method

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

public override IList<Problem> Analyze(IModelElement modelElement, RuleSetting ruleSetting, RuleExecutionContext context)
{
    List<Problem> problems = new List<Problem>();
    if (modelElement.Name == null)
    {
        return null;
    }
    IList<string> nameParts = modelElement.Name.Parts;
    IColumn column = modelElement as IColumn;

    if (nameParts.Count == 3)
    {
        if (!Regex.Match(nameParts[2], @"\w{4}_\w*").Success)
        {
            string message = string.Format(
                "Column {0} of table {1}.{2} must have the form pref_columnname.",
                 nameParts[2], nameParts[0], nameParts[1]);

            Problem p = new Problem(this, message, (IModelElement)modelElement);

            p.Severity = ProblemSeverity.Error;
            problems.Add(p);
        }
    }

    return problems;
}

This code is really simple, the important part is that each object has a name subdivided in parts, a column has three parts: schema, table and column name, so I check the last part (column name) and I validate against a regular expression. If the validation fails, I simply create a Problem object. The Analyze method must in fact return a list of Problem object, each one describing a single and unique problem of the object being analyzed.

Problem object have several properties that can be used, but the most important one is the Severity, that is needed to specify if this problem is a warning or an Error. Since I want that my convention is enforced for every table, I set Problem to Error Severity.

Once you have this class ready you need to create an xml file that contains data about the rule

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

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

    <extension type="SqlRule.MyStylePrefixedTable"
                assembly ="SqlRule, Version=1.0.0.0, Culture=neutral, PublicKeyToken=43c7c037e2f19384"
                enabled="true"/>
</extensions>

You must sign your assembly, and specify full qualified name in the xml, then you need to copy this xml file, along with the assembly that contains the rule, into the directory program files \Microsoft Visual Studio 9.0\VSTSDB\Extensions. Now you can reopen visual studio and verify that the rule gets loaded correctly

To verify the rule I’ve simply created one simple table with one valid column and one not valid column.

If you build the project you will get.

As you can see my custom rule gets executed, and the project will not compile until all columns are not named correctly.

Code can be found here.

Alk.

Tags: Visual Studio Database Edition