Geeks With Blogs
// ThomasWeller C#/.NET software development, software integrity, life as a freelancer, and all the rest

In large projects you may have have a big number of enumerations in your domain code that have all to be kept in sync with some corresponding database stuff. Ensuring this correspondence is boring, repetitive and error-prone. Because it is such a tedious task, chances are that you don't check this at all, opening the door for serious runtime failures - especially when the project is nearing the deadline and things generally tend to become a bit more chaotic.

I love enums. I use them wherever possible. They make the code much more expressive and readable. They enforce type safety (well, mostly...) and avoid the use of magic numbers. Suppose you have to deal with 'foreign' code and stumble over the following line:

int category = 12;

Now, what the heck is that? You can't figure out if this really represents some kind of numeric value or if the value 'means' something, i.e. it does represent something else like for example a certain order state or some flag combination. The code just doesn't tell you. If your lucky, the programmer of this code has written appropriate code comments. If not - well, you will have a hard time. I hope for you, that there is still one of the original programmers in the team, who might know about this issue. It goes almost without saying that something like this would be much better:

Category category = Category.SomeCategory;

But, as everything, using enums comes at a price: Usually, you will have a database where you have to keep a 'copy' of your enum. And thus, you have to care about keeping the 'source code version' (i.e. the enum definition) and the 'database version' (the table data) in sync. And you have to care about that during the entire life of your project. Chances are quite good that you or one of your fellow programmers will sooner or later overlook something, thus introducing a defect to the system that has the potential to become very annoying and expensive.

To get along with these necessary but trivial checks, I'm using xUnit.Net in combination with a small generic base class to put all my enums under unit test coverage.

Suppose you have the following database table (named CATEGORIES):

 

 

 

 

 

And this is the corresponding enum declaration in your domain:

/// <summary>
/// Enum that corresponds to DB table <c>CATEGORIES</c>.
/// </summary>
public enum Category
{
    /// <summary>None/error value.</summary>
    None = 0,

    /// <summary>Constant without explicitly defined numerical value.</summary>
    CategoryWithoutExplicitValue,

    /// <summary>Explicitly defined numerical enum value.</summary>
    CategoryWithExplicitValue = 12

} // enum Category

Given the above code and database table, the corresponding unit test code would then be:

using Xunit.Extensions;

namespace EnumsAndDatabase
{
    public class CategoryFixture : EnumValidationBase<Category>
    {
        [Theory]
        [SqlServerData("DB", "DEMO", "SELECT COUNT(*) FROM CATEGORIES")]
        public void ValidateNumberOfEnumValues(int expectedNumberOfEnumValues)
        {
            base.ValidateNumberOfValues(expectedNumberOfEnumValues);
        }

        [Theory]
        [SqlServerData
("DB", "DEMO", "SELECT NAME, VALUE FROM CATEGORIES")]
        public void ValidateEnumAgainstDatabase(string name, int? value)
        {
            base.ValidateEnumValue(name, value);
        }

    } // class CategoryFixture

} // namespace EnumsAndDatabase

Now, this doesn't look like too much work. Basically, you just declare where your data will come from (using xUnit.net's Theory and SqlServerData attributes), and then give these data to a base class, that will do all the actual work. Easy, huh? And you know what? Once you have written these few lines of code, you'll have to think about it never again. You can manipulate both database and code in every way that you like - the tests will fail as soon as there is something out of sync, and they will pass otherwise...

The first test asserts that the total number of rows in the CATEGORIES table matches the number of values in the Category enum.  The second test then compares the value names and, if not NULL,  the numerical values one by one. In sum, the two tests assert that there is a 1:1 match between code and database.

The base class for such a test fixture (I know, test fixtures are not called test fixtures in the xUnit.Net world...) has a generic type parameter to make it specific to a certain enum, so it can do the actual work. Here it is:

using System;
using Xunit;

namespace EnumsAndDatabase
{
    public abstract class EnumValidationBase<TEnum> where TEnum : struct
    {
        /// <summary>
        /// Validates that a given name represents a member of the specified enum
        /// and that it has the specified value.

        /// </summary>
        /// <param name="valueName">The name of an enum value.</param>
        /// <param name="expectedNumericalValue">
        /// The (optional) numeric value that the enum member must have.
        /// If this value is <see langword="null"/>, no validation occurs.
       
///
</param>
        /// <param name="ignoreCase">
        /// If set to <see langword="true"/>, a case
        /// insensitive search for the corresponding enum member is made.

        /// </param>
        protected void ValidateEnumValue(string valueName,
                                         object expectedNumericalValue,
                                         bool ignoreCase)
        {           
            Type
enumType = typeof(TEnum);
            object val = null;

            // is the specified string actually convertible to the given enum ?

            Assert
.DoesNotThrow(() =>
                           val = Enum.Parse(enumType, valueName, ignoreCase));            

            // is the specified numerical value actually correct ?
           
if (expectedNumericalValue != null)
            {
                Assert.Equal(val,
                            Enum.ToObject(enumType, expectedNumericalValue));
            }
        }

        /// <summary>
        /// Validates that a given name represents a member of the specified
        /// enum (case sensitive) and that it has the specified value.

        /// </summary>
        /// <param name="valueName">The name of an enum value.</param>
        /// <param name="expectedNumericalValue">
        /// The (optional) numeric value that the enum member must have.
        /// If this value is <see langword="null"/>, no validation occurs.
       
///
</param>

        protected void ValidateEnumValue(string valueName,
                                         object expectedNumericalValue)
        {           
            this.ValidateEnumValue(valueName, expectedNumericalValue, false)
        }


        /// <summary>
        /// Validates that a given number is equal to
        /// the number of values in the specified enum.
       
/// </summary>
        /// <param name="expectedNumberOfValues">
        /// The expected number of values for the enum
        /// of type <typeparamref name="TEnum"/>.
        /// </param>
        protected void ValidateNumberOfValues(int expectedNumberOfValues)
        {
            int numberOfValuesAsDefinedByEnum =
                       Enum.GetValues(typeof(TEnum)).Length;

            Assert.Equal(expectedNumberOfValues,
                         numberOfValuesAsDefinedByEnum);
        }

 
    } // class EnumValidationBase

} // namespace EnumsAndDatabase

When deriving from this base class, all that's left to do is:
1. Define two database queries for the database table that holds the enum data: one to retrieve the total number of rows and one to select the name/value pairs
2. Define two corresponding test methods: The first one checks if the number of enum values matches the number of database rows. The second one checks if each row from the database really represents an enum value (you can optionally ignore case sensitivity here) and, if so, if the enum value has the correct numeric value (if there is an explicit value at all).

The only tricky thing here is that you have to allow for null values in your test method if you don't want your enum values to be checked against a numeric value. This occurs if you don't have an explicitely defined numeric value for the enumeration member.

If you are using a DBMS other than MS SQL Server you have to use xUnit.Net's OleDbPropertyAttribute instead of the SqlServerData attribute shown here. Everything else remains the same.

With these two short tests in place, you will never have to care about the Category enum again. The tests 'grow' and 'shrink' with the enums they are validating. and if there is an issue regarding the synchronisation of code and database, the test will fail and thus notify you. This is really helpful in large projects with dozens of little enums, since it reduces the number of things you actively have to think of.

This is what your test execution results will finally look like, when the unit tests are executed with Gallio:

Nice, isn't it? You see all the enum values enlisted together with their numerical counterparts. And, what's even more important: In case of a failure the test report will tell you not only that something went wrong, but it will point you exactly to the root of the problem.

Ok, that's it for now. Looking at the next few weeks, I'm thinking about a sequel to this post, where I will look at these constant string values that you can often find in legacy databases, and that may or may not contain spaces or even typos. This second part will most likely use a piece of NHibernate...

Posted on Saturday, August 22, 2009 7:18 AM Unit Testing/TDD , Readability , Automation | Back to top


Comments on this post: Keeping your enums in sync with your database

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Thomas Weller | Powered by: GeeksWithBlogs.net