Enforcing naming conventions using database unit testing

jamiet — Tue, 05 Oct 2010 22:42:17 GMT

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:

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:

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!!!) 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

Search results matching tags 'Datadude' and 'naming conventions'

Enforcing naming conventions using database unit testing

My naming convention obsession

Database unit testing to the rescue

Wrap-up