SSIS Junkie : sql server, Datadude, unit testing

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

Experiences from writing sp_CascadingDataViewer – DB unit testing and code distribution

jamiet — Sat, 18 Sep 2010 16:41:55 GMT

Its now been a month since I introduced sp_CascadingDataViewer and I wanted to take the opportunity to talk about a couple of my experiences while writing it.

SQL Server needs packages

Version 1 of sp_CascadingDataViewer is an 899 line stored procedure (view the code at changeset 58999), that’s a lot of code and much of it is repeated. I would have loved to have hived off different parts into dedicated functions but I couldn’t do that because my aim was to keep everything in a single stored procedure thus making it as easy as possible for someone to add it to their SQL Server instance. That is an unfortunate trade-off that we have to make with SQL Server.

Oracle has a solution to this problem called packages. A package is ostensibly a collection of stored procedures and functions that can be distributed and installed as a single unit. That makes them extremely portable and would be an ideal mechanism for me to distribute a collection of stored procedures and functions that make up Cascading Data Viewer.

SQL Server needs something equivalent to Oracle packages.

You need to be doing automated database unit testing

sp_CascadingDataViewer wouldn’t exist without automated database unit testing in Visual Studio. Period.

With something as complicated as sp_CascadingDataViewer there were many many combinations of code, schema and data that could cause is to break. I had to deal with all those combinations and have a way of ensuring that any changes that I made did not break something else and automated testing was the means with which I did that.

I wrote the code for sp_CascadingDataViewer in Visual Studio rather than SQL Server Management Studio (as I do for all my T-SQL code these days) which meant that my code and the tests lived in the same solution. At the time of writing that solution includes 30 tests and all are available in the source code on Codeplex:

With a simple key chord (CTRL+R, CTRL+A) I could deploy my code and run all my tests against it – that’s an incredibly powerful mechanism and I actually find it to be very productive method of development even if you have many tests that need to be run.

Writing sp_CascadingDataViewer switched me on to the value of automated database unit testing and I now advocate its use wherever I go.

@jamiet

Microsoft publish Visual Studio 2010 Database Project Guidance

jamiet — Mon, 23 Aug 2010 11:33:00 GMT

Three days ago I wrote a blog post entitled Setting up database unit testing as part of a Continuous Integration build process [VS2010 DB Tools - Datadude] which goes through some of the intracasies of using Visual Studio 2010 Database projects (aka datadude projects) and database unit testing as part of a continuous integration build. Well it turns out I needn't have bothered because today Microsoft have (or, more accurately, Jens Suessmeyer has) published a 91-page guidance document on using datadude projects and on page 57 it covers exactly what I covered in my blog post (plus a lot more besides). Ah well...

Anyway, if you want to take a read of this tome head to http://vsdatabaseguide.codeplex.com/ and/or go and read Jens' blog post on the matter ALM Visual Studio Database Projects Guidance is out in the wild !

@Jamiet

Setting up database unit testing as part of a Continuous Integration build process [VS2010 DB Tools - Datadude]

jamiet — Fri, 20 Aug 2010 18:41:00 GMT

[This blog post assumes a familiarity with the terms Continuous Integration (CI), MSBuild & MSTest. If you don’t have a good appreciation of those terms then this blog post probably isn’t for you anyway so don’t worry about it!]

Over the past few days I have been working to get database unit tests executing as part of our Continuous Integration (CI) build and in this blog post I’ll explain how I went about it because it is decidedly not straightforward.

We are using the DB Tools in Visual Studio 2010 (aka DBPro or Datadude - I will refer to it as Datadude from here on in) which includes unit testing functionality. The rest of this blog post also assumes a familiarity with database unit testing in Datadude although if you want to do some background reading an excellent place to start is Jamie Laflen’s whitepaper Apply Test-Driven Development to your Database Projects on MSDN.

We got to the point where we had a C# test project containing database unit tests that executed successfully inside Visual Studio. For demonstration purposes I have put together a solution that contains a simple database project and a test project containing a database unit test:

And just to prove that the test executes successfully in Visual Studio:

N.B. The code for the database object(s) and the unit test itself are not important, that is outside the scope of this blog post.

At this point we have some tests that run in our development sandbox, the configuration for which is done using the Database Test Configuration dialog:

Those config settings are stored in the app.config file which exists as part of our test project (screenshot of which is at the top of this blog post). If we take a look inside that file we can see the ConnectionString that we defined in the Database Test Configuration dialog:

Note the <DatabaseUnitTesting> element, we’re going to be coming back to that a little later!!

Once we have the tests running in Visual Studio the next step is to get them running inside a CI build and for that we call out to MSTest.exe from inside an MSBuild script:

When we execute that script we get some positive results and all looks peachy:

OK, we now have a test that runs successfully both in Visual Studio and by calling MSTest.exe directly from MSBuild. The problem I have to solve now though (and this is the real crux of this blog post) is that the test is still running against the server I specified in app.config; I don’t want this, I want the test to run against a server of my choosing, namely my build server. For this I had to call on the help of the aforementioned Jamie Laflen (who, luckily, I have met in the past) and he gave me guidance on how to achieve it. Below are the steps that you need to take.

Firstly you need to tell Datadude that you want to specify some different credentials and that is done by adding an attribute AllowConfigurationOverride="true" to the <DatabaseUnitTesting> element of app.config like so:

Don’t forget to build your test project after changing the app.config file!!!

This will cause datadude unit testing framework to go and search for a file called *.dbunittest.config where the * indicates either:

The name of machine upon which the tests are being run or
The name of the user running the tests

According to Jamie L datadude runs the following precedence checks:

Is an override specified in app.config? If not, use app.config
Does an override file exist called <machinename>.dbunittest.config? If so, use it, if not…
Does an override file exist called <username>.dbunittest.config? If so, use it, if not…
Fail!

“So”, you may be asking, “what goes in this *.dbunittest.config file then?”. Fair question, that’s where the <DatabaseUnitTesting> element that I mentioned earlier comes in. Copy that element from the app.config file into your *.dbunittest.config file, remove the AllowConfigurationOverride="true" attribute and change the ConnectionString property accordingly:

The next step is to tell MSTest.exe that there is another file that it needs to be aware of when it runs the tests and to do that we need to create a custom testsettings file that will be used instead of the default Local.testsettings. To create a new testsettings file use the Add New Item dialog:

In the Test Settings dialog go to ‘Deployment’, click ‘Enable deployment’, then ‘Add File…’. Browse to your *.dbunittest.config file and add it to the project:

At this point you’re good to go and all you need to do edit your call to MSTest.exe and tell it to use your new testsettings file:

Notice the extra information in the output:

You’ll have to trust me that it used the new config file and therefore the new ConnectionString, although having said that you do get some useful information in the test results file (*.trx) that MSTest.exe creates:

I think its fair to say that this process is a little fiddly to say the least so I’m hoping that Microsoft come up with a better solution in the future, one that integrates better into the whole testing infrastructure. Meanwhile you can download the demo that I put together for this blog post from my SkyDrive.

Hope that helps! Comments are welcome.

Jamiet

UPDATE: Atul Verma has written a 3-part blog series on how to write your database unit tests so if Jamie Laflen's whitepaper (linked to above) doesn't tickle your fancy try reading Atul's series instead: