Defining Enum Tables

Back in January I posted about "Defining Many To Many" tables; and now (to take a note from the Colbert Report), here is Part 2 in my infinite part series...

Better Know an ORM Programmatic Definition: Defining Enum Tables!

The idea here is that we want to generate Enums from database tables; so for each table we specify as an Enum table, we want create an Enum and populate it's values from the rows in that table. As always, the goal is to make this solution be as generic as possible. We want this to be able to work on pretty much any database we throw at it, we want it to check for any usual pitfalls, and of course we want what we generate to be as useful as possible! Let's begin with an example...

Example Table (Input)

Table Name: StarTrek
Columns: Id (int), Name (string), Captain (string)
Row 1: "Original", "James Tiberius Kirk"
Row 2: "Animated", "James Tiberius Kirk"
Row 3: "TNG", "Jean-Luc Picard"
Row 4: "DS9", "Benjamin Sisko"
Row 5: "Voyager", "Kathryn Janeway"
Row 6: "Enterprise", "Scott Bakula? Seriously?"

Example Enum (Output)

public enum StarTrek : int
{
    /// <summary>James Tiberius Kirk</summary>
    Original = 1,
...etc...
    /// <summary>Scott Bakula? Seriously?</summary>
    Enterprise = 6
}

So, what is the logic?

1) Explicitly identify select the table.

While associations can be determined by examining keys and other qualities, Enum tables just don't have enough unique qualities to identify in that manner; thus we will want to explicitly choose our Enum tables. For this task I recommend a Regex for Enum table names; however you could always use a good old fashion table list. Now that we have identified that the table SHOULD be an Enum, we need to determine if it CAN be an Enum table...

2) The table must have a primary key.

Enums values can be assigned a numeric value, so to allow for association mapping and logical comparisons it's a good idea that our generated Enums are assigned meaningful values at generation time. NOTE: While assigning a numeric value to Enum values can server many different purposes, the following was specifically chosen because it allows for Enums to act as database associations for business entity objects.

2.A) The table must have a primary key composed of a single column. (It's hard to have a composite key that evaluates to a single numerical value.)

2.B) The primary key column must be of a number type. (This is so that the Enum values can be assigned to the key value.)

3) The table must have a column to specify the values.

Well if the table is the Enum itself, where are the values going to come from? You have to chose which column the value is going to come out of! Again I recommend using a Regex to find this column by name, but if that fails (or if you are feeling lazy) you could default to taking the first column of a string type.

4) The table must have at least one row.

Firstly, this is because there's not a lot of use for an empty Enum; but also, some languages (such as VB) don't support it.

Additionally

When generating the enums, it might come in handy to generate the description for each value as well (as we did in the example above); so, in the code below is an extra function for finding that description with (surprise surprise) a Regex.

And finally, here is what your code might look like...

public static Regex EnumTableNameRegex = new Regex("(E|e)num$", RegexOptions.Compile);
public static Regex EnumValueColumnRegex = new Regex("((V|v)alue)|((N|n)ame)|((T|t)ype(C|c)ode)", RegexOptions.Compile);
public static Regex EnumDescriptionColumnRegex = new Regex("(D|d)esc", RegexOptions.Compile);

public bool IsEnum(TableSchema table)
{
    return EnumTableNameRegex.IsMatch(table.Name)                            // 1) Matches the enum regex.
        && table.PrimaryKey != null                                                          // 2) Has a Primary Key...
        && table.PrimaryKey.MemberColumns.Count == 1                         // a) ...that is a single column...
        && IsNumeric(table.PrimaryKey.MemberColumns[0].SystemType)   // b) ...of a number type.
        && !string.IsNullOrEmpty(GetEnumNameColumnName(table))         // 3) Contains a column for name.
        && table.GetTableData().Rows.Count > 0;                                      // 4) Must have at least one row.
}

private bool IsNumeric(Type t)
{
    return t == typeof(byte)
           || t == typeof(sbyte)
           || t == typeof(short)
           || t == typeof(ushort)
           || t == typeof(int)
           || t == typeof(uint)
           || t == typeof(long)
           || t == typeof(ulong);
}

public string GetEnumValueColumn(TableSchema table)
{
    string result = GetEnumColumn(table, EnumValueColumnRegex);

    // If no Regex match found, use first column of type string.
    if (string.IsNullOrEmpty(result))
        foreach (ColumnSchema column in table.Columns)
            if (column.SystemType == typeof(string))
            {
                result = column.Name;
                break;
            }

    return result;
}

private string GetEnumColumn(TableSchema table, Regex regex)
{
    string result = string.Empty;

    foreach (ColumnSchema column in table.Columns)
        if (regex.IsMatch(column.Name))
        {
            result = column.Name;
            break;
        }

    return result;
}

public string GetEnumDescriptionColumn(TableSchema table)
{
    return GetEnumColumnName(table, EnumDescriptionExpressions);
}