THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Enforcing naming conventions using database unit testing

My naming convention obsession

Anyone that has ever worked with me will tell you that I am a stickler for naming conventions. I have a somewhat obsessive reputation for it; I can’t help it – I seem to have a deep seated uncontrollable desire to ensure that every object in my database(s) is/are named consistently (is there anyone else out there equally as obsessive?).

I have tried various techniques down the years to try and enforce naming conventions but none of them really worked. I’ve got scripts that alter object names (such a script is in my script library in fact) but these are only any use if they actually get run, they don’t actually enforce the conventions – that’s a manual step. I’ve thought about using Policy-Based Management (PBM) to enforce naming conventions but given I’m a developer and not a DBA that’s not something that is readily available to me and besides, using PBM to enforce naming conventions is reactive rather than proactive if you are developing the code on a machine where the policies are not enforced.

Another option I looked into using was Team Foundation Server (TFS) check-in policies; these are policies that can be applied to artefacts when they get checked-in to TFS’s source control system. This option really appealed to me because the naming conventions could be enforced during check-in (i.e. very very early) and didn’t require DBA intervention. In practice though enforcing naming conventions using TFS check-in policies has a few sizable issues:

  • Its not easy. It would require you to parse the file that was getting checked-in, decide what sort of object is defined in the file, and then check the name of the object based on things like object name, schema, etc...
  • TFS check-in policies are not installed on the TFS server, they are installed on the development workstations. This means there is a dependency and, even though the source code for the check-in policies can be distributed with your application source code, I didn’t really like this.
  • You’re relying on each developer to enforce the check-in policy and with the greatest will in the world….that aint gonna happen. Its too easy to turn them off.
  • There is the obvious dependency on using TFS, not something every development shop uses even in the Microsoft space.

Database unit testing to the rescue

No, a better solution was needed and I came up with one in the shape of automated database unit testing. I have spoken recently about how I have become a big fan of database unit testing (see my post Experiences from writing sp_CascadingDataViewer – DB unit testing and code distribution) and being able to enforce naming conventions is one very good reason for that. Enforcing naming conventions using automated unit tests has a number of advantages:

  • They can be written against the metadata of the objects themselves (i.e. by querying SQL Server’s system views) so there’s no parsing that needs to be done.
  • They can be employed as part of a Continuous Integration (CI) process and run as a build verification test (BVT). Someone checks-in an object that violates the naming convention? Bang: broken build!
  • Developers can’t circumvent the tests.
  • Nothing needs to be installed on the development workstations. The tests live wholly as part of your source code.
  • Not dependent on use of a particular source control system

Hence I have written some unit tests that enforce the following naming conventions:

  • Check constraints must be of the form CK_<schemaName><tableName>_XXX
  • Column names must begin with a capital letter
  • Column names cannot contain underscores
  • Default constraints must be named DF_<schemaName><tableName>_<ColumnName>
  • Foreign keys must be of the form FK_<parentObjectSchema><parentObject>_REF_<referencedObjectSchema><referencedObject>XXX
  • Non-unique clustered keys must be of the form IXC_<schemaName<TableName>_<Column><Column><Column>…
  • Non-unique non-clustered keys must be of the form IX_<schemaName><TableName>_<Column><Column><Column>...
  • Unique clustered keys must be of the form IXUN_<schemaName><TableName>_<Column><Column><Column>…
  • Unique non-clustered keys must be of the form IXUN_<schemaName><TableName>_<ColumnColumnColumn>...
    • Primary keys must be of the form PK_<schemaName><tableName>
    • Stored procedure names should not contain underscores
    • Stored procedure names must begin with a capital letter
    • Table names must not contain underscores
    • Table names must begin with a capital letter

    I’m not stating that you should agree with these naming conventions (I don’t necessarily agree with them myself – they were defined before I arrived on my current project), the point here is that all of these rules can be enforced and its very easy to do it. Here’s the code for the unit test that enforces the  primary key naming convention:

       /*PK name is PK_<schemaName><tableName>*/
      
    SET NOCOUNT ON
       DECLARE
    @cnt    INT
    ;

      
    SELECT 
    *
      
    INTO   
    #t
      
    FROM   
    (
              
    SELECT  OBJECT_NAME(c.[parent_object_id]) AS [TableName],OBJECT_SCHEMA_NAME(c.[parent_object_id]) AS [SchemaName],c.
    *
              
    FROM   
    [sys].[key_constraints] c
              
    INNER JOIN
    [sys].[tables] t
                  
    ON  c.[parent_object_id] =
    t.[object_id]      
              
    LEFT OUTER JOIN
    sys.extended_properties ep
                  
    ON  t.[object_id] =
    ep.major_id
                  
    AND ep.[name] =
    'microsoft_database_tools_support'
              
    WHERE ep.[major_id] IS
    NULL
               AND
    c.[type] =
    'PK'
              
    )
    q
      
    WHERE   [name] <> N'PK_' + [SchemaName] +
    [TableName]

      
    SET     @cnt = @@ROWCOUNT
    ;
      
    IF  (@cnt > 0
    )
      
    BEGIN
               DECLARE
    @msg NVARCHAR(2048
    );
              
    SELECT  @msg = '%d Primary Keys do not conform to naming convention (PK_<schemaName><tableName>):' + STUFF((SELECT ', ' + [name] FROM #t a FOR XML PATH('') ),1,2,''
    )
              
    FROM   
    (
                      
    SELECT  1 AS [Id]
    ,*
                      
    FROM    #t
    t
                      
    )
    q
              
    GROUP   BY
    q.[Id]
              
    SELECT
    @msg
              
    RAISERROR(@msg,11,1,@cnt
    );
      
    END

    Essentially all it does is pull all of the primary keys out of [sys].[key_constraints], checks to see what the name should be, then if it finds any that violate the naming convention raise an error containing the names of all the primary keys in question. Here’s the error obtained when running the test against [AdventureWorks] (I’ve highlighted the pertinent bit):

    Test method Prs.SchemaTests.NamingConventions.PrimaryKeys threw exception:
    System.Data.SqlClient.SqlException: 70 Primary Keys do not conform to naming convention (PK_<schemaName><tableName>):PK_ErrorLog_ErrorLogID, PK_Address_AddressID, PK_AddressType_AddressTypeID, PK_AWBuildVersion_SystemInformationID, PK_BillOfMaterials_BillOfMaterialsID, PK_Contact_ContactID, PK_ContactCreditCard_ContactID_CreditCardID, PK_ContactType_ContactTypeID, PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode, PK_CountryRegion_CountryRegionCode, PK_CreditCard_CreditCardID, PK_Culture_CultureID, PK_Currency_CurrencyCode, PK_CurrencyRate_CurrencyRateID, PK_Customer_CustomerID, PK_CustomerAddress_CustomerID_AddressID, PK_DatabaseLog_DatabaseLogID, PK_Department_DepartmentID, PK_Document_DocumentID, PK_Employee_EmployeeID, PK_EmployeeAddress_EmployeeID_AddressID, PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID, PK_EmployeePayHistory_EmployeeID_RateChangeDate, PK_Illustration_IllustrationID, PK_Individual_CustomerID, PK_JobCandidate_JobCandidateID, PK_Location_LocationID, PK_Product_ProductID, PK_ProductCategory_ProductCategoryID, PK_ProductCostHistory_ProductID_StartDate, PK_ProductDescription_ProductDescriptionID, PK_ProductDocument_ProductID_DocumentID, PK_ProductInventory_ProductID_LocationID, PK_ProductListPriceHistory_ProductID_StartDate, PK_ProductModel_ProductModelID, PK_ProductModelIllustration_ProductModelID_IllustrationID, PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID, PK_ProductPhoto_ProductPhotoID, PK_ProductProductPhoto_ProductID_ProductPhotoID, PK_ProductReview_ProductReviewID, PK_ProductSubcategory_ProductSubcategoryID, PK_ProductVendor_ProductID_VendorID, PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID, PK_PurchaseOrderHeader_PurchaseOrderID, PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID, PK_SalesOrderHeader_SalesOrderID, PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID, PK_SalesPerson_SalesPersonID, PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate, PK_SalesReason_SalesReasonID, PK_SalesTaxRate_SalesTaxRateID, PK_SalesTerritory_Territor...

    I am currently including these tests inside a C# test project inside Visual Studio 2010. Visual Studio has a rather nice feature that allows you to link to artefacts in other projects and hence we can host our single test class containing all of these tests in one place and link to it from whichever test project we want (typically you will have a test project per database) thus following the DRY principle. Here I show the dialog that demonstrates adding a link to an existing test class:

    image

    And how it appears in the project. Note that NamingConventions.cs exists in both test projects but one is just a link to the other:

    image

    Wrap-up

    I’m not sure my colleagues are too happy about these new tests given that they’re now breaking the build more often but nonetheless I think they realise the value (I guess I’ll find out tomorrow when they read this!!!) Smile All-in-all its working very well for us and I’m now a very happy bunny knowing that naming conventions are being enforced and will continue to be so with zero effort from here on in. I have made the test class that contains all of the tests that I detailed above available on my SkyDrive at http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20101005/MIControlTest.zip. If you want to use it you should simply be able to drop it into an existing C# database test project and away you go (change the tests to suit your naming conventions of course though).

    Hope this helps. If it does please let me know, I’d really love some feedback on this.

    @Jamiet

    Published Wednesday, October 06, 2010 12:42 AM by jamiet

    Comment Notification

    If you would like to receive an email when updates are made to this post, please register here

    Subscribe to this post's comments using RSS

    Comments

     

    Jack Corbett said:

    Awesome!  I really need to S better and VS2010 DB Projects so I can do more with unit tests for my database projects.  Especially automating them.

    October 5, 2010 8:38 PM
     

    Marzena said:

    "is there anyone else out there equally as obsessive?" Yeah, it's me :-). Excellent post, will try it on my projects. Thanks, Jamie.

    October 6, 2010 3:15 AM
     

    Alexander Kuznetsov said:

    Jamie,

    The convention that matters most to me is this: we should not have tabs in our source code, only spaces. Otherwise my code may look great on my screen, and be an unreadable mess on yours. I have a unit test enforcing this in my system.

    Yet I don't think I would enforce naming conventions when the team does not agree on them. In my environment that would be counter-productive. Also when we are in a hurry to fix something important, we would be frustrated if it does not build because of naming conventions.

    I think ReSharper does a great job in encouraging us to follow naming conventions. ReSharper will clearly indicate that a name does not comply, but it will let me still use it if I insist. I like it better.

    Naming conventions are a great way to improve communication and eliminate ambiguity. Yet when we take them too seriously, we suppress initiative and creativity, and the morale of the team suffers. As a result, if we control the process too much, the overall productivity plunges.

    Of course, we are all different, and the environments we work in are different as well, so we don't have to agree on one and the same approach for all.

    October 6, 2010 10:46 AM
     

    jamiet said:

    Alexander,

    Indeed, the team should agree on the naming conventions. On our project those naming conventions have been documented since day one - unfortunately they were not adhered to.

    "we don't have to agree on one and the same approach for all"

    I didn't realise we were disagreeing! :)

    -Jamie

    October 6, 2010 11:00 AM
     

    Tom said:

    Tremendous Jamie, you are a man after my own heart. Good work.

    October 11, 2010 8:10 AM
     

    SSIS Junkie said:

    Over the past eighteen months I have worked on four separate projects for customers that wanted to make

    January 2, 2012 11:07 AM

    Leave a Comment

    (required) 
    (required) 
    Submit

    This Blog

    Syndication

    Powered by Community Server (Commercial Edition), by Telligent Systems
      Privacy Statement