I'm Chris Eck, and I'm the tools developer at HBS for the Battletech project. I've recently been given permission to write up articles about some of the things I work on which I hope to post on a semi regular basis. Feel free to ask questions about these posts or give me suggestions for future topics. However please note, I am unable to answer any questions about new/unconfirmed features.

I've been tackling Unity 2018 upgrade issues as they come my way, and I've been chugging along with my data driven enumeration refactors. I've done four or five now so I'm starting to gain a deeper understanding of the process. I find all references to the hard coded enum and identify where any code decisions are being made. Then I create data fields to drive that logic instead. After I have all those bits defined, I create the table/columns in the database and then populate it.

Creating the C# classes and functions that interface with the database is not quite copy/paste grunt work. There needs to be a class with the fields, and functions to select, insert, and update those rows in the database. But the fields for the different enumerations are all different. The work isn't difficult, but it's time consuming and a bit fiddly. When I started writing this final set I told myself, "Self... Writing this code is boring and repetitive... There has to be a better way..."

Enter Code Generation

I had several completed examples so I the kinks already worked out. I used all the AmmoCategory classes and functions and just copied them into a text file. I then replaced anything that had AmmoCategory specific items with some keys that I could find and replace. I ended up with a template file that looked like this:

public class %ClassName%
{
%FieldList%}


#region %TableName%

public static List<%ClassName%> SelectAll%PluralTableName%(this MetadataDatabase mdd)
{
	string query = "SELECT %TableAlias%.* FROM %TableName% as %TableAlias%";
	List<%ClassName%> %LowerTableName%List = mdd.Query<%ClassName%>(query).ToList();

	return %LowerTableName%List;
}

public static %ClassName% Select%TableName%ByID(this MetadataDatabase mdd, int %LowerTableName%ID)
{
	%ClassName% %LowerTableName%Row = mdd.Query<%ClassName%>(
	"SELECT * FROM %TableName% WHERE %TableName%ID=@%TableName%ID",
		new { %TableName%ID = %LowerTableName%ID })
		.FirstOrDefault();

	return %LowerTableName%Row;
}

public static %ClassName% Insert%TableName%Value(this MetadataDatabase mdd, %TableName%Value %LowerTableName%)
{
	mdd.Execute("INSERT INTO %TableName%" +
		" (%InsertFieldList%)" +
		" values(%InsertValueList%)",
		new { %InsertAnonymousTypeList% });

	%ClassName% %LowerTableName%Row = Select%TableName%ByID(mdd, %LowerTableName%.ID);
	return %LowerTableName%Row;
}

public static %ClassName% Update%TableName%Value(this MetadataDatabase mdd, %TableName%Value %LowerTableName%)
{
	mdd.Execute("UPDATE %TableName% SET" +
	    " %UpdateFieldList%" +
		" WHERE %UpdateWhereFieldList%",
		new { %InsertAnonymousTypeList% });

	%ClassName% %LowerTableName%Row = Select%TableName%ByID(mdd, %LowerTableName%.ID);
	return %LowerTableName%Row;
}

public static %ClassName% InsertOrUpdate%TableName%Value(this MetadataDatabase mdd, %TableName%Value %LowerTableName%)
{
	InsertOrUpdateEnumValue(mdd, %LowerTableName%);

	%ClassName%  %LowerTableName%Row = Select%TableName%ByID(mdd, %LowerTableName%.ID);

	if (%LowerTableName%Row == null)
	{
		%LowerTableName%Row = Insert%TableName%Value(mdd, %LowerTableName%);
	}
	else
	{
		%LowerTableName%Row = Update%TableName%Value(mdd, %LowerTableName%);
	}

	return %LowerTableName%Row;
}

#endregion // %TableName%

Then I defined my WeaponCategory table in the database (that's the new one I was working on). The table has the various column names and their data types defined and Sqlite has a PRAGMA command that can pull that information for you. Once I have that data, I can start with my substitution.

Here's what the code generating function looks like

/// <summary>
/// Given a table name, pull that table's info from the database and generate code for 
/// the DTO, Select, Insert, and Update functions.
/// </summary>
/// <param name="tableName">The name of the table to generate code for</param>
/// <param name="pluralTableName">The pluralized name. I didn't feel like writing code to figure out when to drop the y and ad ies. :P</param>
/// <returns>The generated code for the DTO, Select, Insert, and Update functions.</returns>
public static string GenerateCode(string tableName, string pluralTableName)
{
    MetadataDatabase mdd = MetadataDatabase.Instance;
    string lowerTableName = char.ToLower(tableName[0]) + tableName.Substring(1);
    string className = string.Format("{0}_MDD", tableName);
    string lowerClassName = string.Format("{0}_MDD", lowerTableName);
    List<SqliteTableInfo> columnInfoList = mdd.GetTableInfo(tableName);


    string tableAlias = "";
    for (int i = 0; i < tableName.Length; ++i)
    {
        char c = tableName[i];
        if (char.IsUpper(c))
            tableAlias += char.ToLower(c);
    }

    // Load up our template file.
    string generatedCode = File.ReadAllText("SqliteCodeGen\\CodeGenTemplate.txt");

    StringBuilder fieldList = new StringBuilder();
    StringBuilder insertFieldList = new StringBuilder();
    StringBuilder insertValueList = new StringBuilder();
    StringBuilder insertAnonymousTypeList = new StringBuilder();

    StringBuilder updateFieldList = new StringBuilder();
    StringBuilder updateWhereFieldList = new StringBuilder();

    // Loop through each of our column names and build the data that we need
    for (int i = 0; i < columnInfoList.Count; i++)
    {
        SqliteTableInfo columnInfo = columnInfoList[i];
        fieldList.AppendLine(BuildFieldLine(columnInfo.name, columnInfo.type));

        insertFieldList.Append(BuildInsertField(columnInfo));
        insertValueList.Append(BuildInsertValue(columnInfo));

        insertAnonymousTypeList.Append(BuildInsertAnonymousType(columnInfo, lowerTableName));

        if (columnInfo.pk > 0)
            updateWhereFieldList.Append(BuildUpdateWhereField(columnInfo));
        else
            updateFieldList.Append(BuildUpdateField(columnInfo));
    }

    // Remove the extra ", " from each of these lists.
    insertFieldList.Remove(insertFieldList.Length - 2, 2);
    insertValueList.Remove(insertValueList.Length - 2, 2);
    insertAnonymousTypeList.Remove(insertAnonymousTypeList.Length - 2, 2);
    updateFieldList.Remove(updateFieldList.Length - 2, 2);

    // Remove the extra "AND " from this list
    updateWhereFieldList.Remove(updateWhereFieldList.Length - 4, 4);

    generatedCode = generatedCode.Replace("%TableName%", tableName);
    generatedCode = generatedCode.Replace("%PluralTableName%", pluralTableName);
    generatedCode = generatedCode.Replace("%TableAlias%", tableAlias);
    generatedCode = generatedCode.Replace("%LowerTableName%", lowerTableName);
    generatedCode = generatedCode.Replace("%ClassName%", className);
    generatedCode = generatedCode.Replace("%LowerClassName%", lowerClassName);
    generatedCode = generatedCode.Replace("%FieldList%", fieldList.ToString());
    generatedCode = generatedCode.Replace("%InsertFieldList%", insertFieldList.ToString());
    generatedCode = generatedCode.Replace("%InsertValueList%", insertValueList.ToString());
    generatedCode = generatedCode.Replace("%InsertAnonymousTypeList%", insertAnonymousTypeList.ToString());
    generatedCode = generatedCode.Replace("%UpdateFieldList%", updateFieldList.ToString());
    generatedCode = generatedCode.Replace("%UpdateWhereFieldList%", updateWhereFieldList.ToString());

    // If you want to write out a file instead of use the copy/paste window, uncomment this line.
    //File.WriteAllText(string.Format("SqliteCodeGen\\{0}.cs", tableName), generatedCode);

    return generatedCode;
}

public static string BuildFieldLine(string fieldName, string fieldTypeText)
{
    string fieldType = "string";

    // INTEGER can mean a few different things in SQLITE
    if (fieldTypeText == "INTEGER")
    {
        // We're identifying bools by convention. See if this starts with one of the common bool words.
        if (IsBool(fieldName))
            fieldType = "bool";
        else if (fieldName.EndsWith("ID"))
            fieldType = "long";
        else
            fieldType = "int";
    }
    else if (fieldTypeText == "REAL")
        fieldType = "float";
    else if (fieldTypeText == "TEXT")
        fieldType = "string";
    else
        Debug.LogError(string.Format("Unsupported Field Type [{0}] - defaulting to string", fieldType));

    string fieldLine = string.Format("\tpublic {0} {1} {{ get; private set;}}", fieldType, fieldName);

    return fieldLine;

}

private static bool IsBool(string fieldName)
{
    if (IsBoolWord(fieldName, "Can"))
        return true;

    if (IsBoolWord(fieldName, "Force"))
        return true;

    if (IsBoolWord(fieldName, "Has"))
        return true;

    if (IsBoolWord(fieldName, "Is"))
        return true;

    if (IsBoolWord(fieldName, "Include"))
        return true;

    if (IsBoolWord(fieldName, "Should"))
        return true;

    if (IsBoolWord(fieldName, "Use"))
        return true;

    if (IsBoolWord(fieldName, "Uses"))
        return true;

    return false;
}

public static bool IsBoolWord(string fieldName, string boolWord)
{
    if (fieldName.StartsWith(boolWord) && fieldName.Length > boolWord.Length && char.IsUpper(fieldName[boolWord.Length]))
        return true;
    else
        return false;
}

private static string BuildInsertField(SqliteTableInfo columnInfo)
{
    return string.Format("{0}, ", columnInfo.name);
}

private static string BuildInsertValue(SqliteTableInfo columnInfo)
{
    return string.Format("@{0}, ", columnInfo.name);
}

private static string BuildInsertAnonymousType(SqliteTableInfo columnInfo, string lowerTableName)
{
    string annonymousType = string.Format("{0}={1}.{0}, ", columnInfo.name, lowerTableName);
    return annonymousType;
}

private static string BuildUpdateWhereField(SqliteTableInfo columnInfo)
{
    return string.Format("{0}=@{0} AND ", columnInfo.name);
}

private static string BuildUpdateField(SqliteTableInfo columnInfo)
{
    return string.Format("{0}=@{0}, ", columnInfo.name);
}

Since Sqlite only has a few data types INTEGER, REAL, TEXT, BLOB - it doesn't have types for bool. In the database, we use integer based fields for those. On the c# side though, I want to use bools. I identify which fields are bools by naming convention. If it starts with something like "Is" or "Has" and the next letter is capital, then I call it a bool. It may not be perfect, but it's easily fixed if I misidentify a field.

Running it against this table

CREATE TABLE "WeaponCategory" (
	"WeaponCategoryID"	INTEGER NOT NULL UNIQUE,
	"IsBallistic"	INTEGER NOT NULL,
	"IsMissile"	INTEGER NOT NULL,
	"IsEnergy"	INTEGER NOT NULL,
	"IsSupport"	INTEGER NOT NULL,
	"IsMelee"	INTEGER NOT NULL,
	"CanUseInMelee"	INTEGER NOT NULL,
	"IsAffectedByEvasive"	INTEGER NOT NULL,
	"ForceLightHitReact"	INTEGER NOT NULL,
	"DamageReductionMultiplierStat"	TEXT NOT NULL,
	"ToBeHitStat"	TEXT NOT NULL,
	"DesignMaskString"	TEXT NOT NULL,
	"TurretDamageMultiplier"	REAL NOT NULL,
	"VehicleDamageMultiplier"	REAL NOT NULL,
	"MinHorizontalAngle"	REAL NOT NULL,
	"MaxHorizontalAngle"	REAL NOT NULL,
	"MinVerticalAngle"	REAL NOT NULL,
	"MaxVerticalAngle"	REAL NOT NULL,
	"UIColorRef"	TEXT NOT NULL,
	"FallbackUIColor"	TEXT,
	"Icon"	TEXT NOT NULL,
	"HardpointPrefabText"	TEXT NOT NULL,
	"UseHardpointPrefabTextAsSuffix"	INTEGER NOT NULL,
	PRIMARY KEY("WeaponCategoryID")
);

yields the following code.

#region WeaponCategory

public static List<WeaponCategory_MDD> SelectAllWeaponCategories(this MetadataDatabase mdd)
{
    string query = "SELECT wc.* FROM WeaponCategory as wc";
    List<WeaponCategory_MDD> weaponCategoryList = mdd.Query<WeaponCategory_MDD>(query).ToList();

    return weaponCategoryList;
}

public static WeaponCategory_MDD SelectWeaponCategoryByID(this MetadataDatabase mdd, int weaponCategoryID)
{
    WeaponCategory_MDD weaponCategoryRow = mdd.Query<WeaponCategory_MDD>(
    "SELECT * FROM WeaponCategory WHERE WeaponCategoryID=@WeaponCategoryID",
        new { WeaponCategoryID = weaponCategoryID })
        .FirstOrDefault();

    return weaponCategoryRow;
}

public static WeaponCategory_MDD InsertWeaponCategoryValue(this MetadataDatabase mdd, WeaponCategoryValue weaponCategory)
{
    mdd.Execute("INSERT INTO WeaponCategory" +
        " (WeaponCategoryID, IsBallistic, IsMissile, IsEnergy, IsSupport, IsMelee, CanUseInMelee, IsAffectedByEvasive, ForceLightHitReact, DamageReductionMultiplierStat, ToBeHitStat, DesignMaskString, TurretDamageMultiplier, VehicleDamageMultiplier, MinHorizontalAngle, MaxHorizontalAngle, MinVerticalAngle, MaxVerticalAngle, UIColorRef, FallbackUIColor, Icon, HardpointPrefabText, UseHardpointPrefabTextAsSuffix)" +
        " values(@WeaponCategoryID, @IsBallistic, @IsMissile, @IsEnergy, @IsSupport, @IsMelee, @CanUseInMelee, @IsAffectedByEvasive, @ForceLightHitReact, @DamageReductionMultiplierStat, @ToBeHitStat, @DesignMaskString, @TurretDamageMultiplier, @VehicleDamageMultiplier, @MinHorizontalAngle, @MaxHorizontalAngle, @MinVerticalAngle, @MaxVerticalAngle, @UIColorRef, @FallbackUIColor, @Icon, @HardpointPrefabText, @UseHardpointPrefabTextAsSuffix)",
        new { WeaponCategoryID = weaponCategory.WeaponCategoryID, IsBallistic = weaponCategory.IsBallistic, IsMissile = weaponCategory.IsMissile, IsEnergy = weaponCategory.IsEnergy, IsSupport = weaponCategory.IsSupport, IsMelee = weaponCategory.IsMelee, CanUseInMelee = weaponCategory.CanUseInMelee, IsAffectedByEvasive = weaponCategory.IsAffectedByEvasive, ForceLightHitReact = weaponCategory.ForceLightHitReact, DamageReductionMultiplierStat = weaponCategory.DamageReductionMultiplierStat, ToBeHitStat = weaponCategory.ToBeHitStat, DesignMaskString = weaponCategory.DesignMaskString, TurretDamageMultiplier = weaponCategory.TurretDamageMultiplier, VehicleDamageMultiplier = weaponCategory.VehicleDamageMultiplier, MinHorizontalAngle = weaponCategory.MinHorizontalAngle, MaxHorizontalAngle = weaponCategory.MaxHorizontalAngle, MinVerticalAngle = weaponCategory.MinVerticalAngle, MaxVerticalAngle = weaponCategory.MaxVerticalAngle, UIColorRef = weaponCategory.UIColorRef, FallbackUIColor = weaponCategory.FallbackUIColor, Icon = weaponCategory.Icon, HardpointPrefabText = weaponCategory.HardpointPrefabText, UseHardpointPrefabTextAsSuffix = weaponCategory.UseHardpointPrefabTextAsSuffix, });

    WeaponCategory_MDD weaponCategoryRow = SelectWeaponCategoryByID(mdd, weaponCategory.ID);
    return weaponCategoryRow;
}

public static WeaponCategory_MDD UpdateWeaponCategoryValue(this MetadataDatabase mdd, WeaponCategoryValue weaponCategory)
{
    mdd.Execute("UPDATE WeaponCategory SET" +
        " IsBallistic=@IsBallistic, IsMissile=@IsMissile, IsEnergy=@IsEnergy, IsSupport=@IsSupport, IsMelee=@IsMelee, CanUseInMelee=@CanUseInMelee, IsAffectedByEvasive=@IsAffectedByEvasive, ForceLightHitReact=@ForceLightHitReact, DamageReductionMultiplierStat=@DamageReductionMultiplierStat, ToBeHitStat=@ToBeHitStat, DesignMaskString=@DesignMaskString, TurretDamageMultiplier=@TurretDamageMultiplier, VehicleDamageMultiplier=@VehicleDamageMultiplier, MinHorizontalAngle=@MinHorizontalAngle, MaxHorizontalAngle=@MaxHorizontalAngle, MinVerticalAngle=@MinVerticalAngle, MaxVerticalAngle=@MaxVerticalAngle, UIColorRef=@UIColorRef, FallbackUIColor=@FallbackUIColor, Icon=@Icon, HardpointPrefabText=@HardpointPrefabText, UseHardpointPrefabTextAsSuffix=@UseHardpointPrefabTextAsSuffix" + 
        " WHERE WeaponCategoryID=@WeaponCategoryID ",
        new { WeaponCategoryID = weaponCategory.WeaponCategoryID, IsBallistic = weaponCategory.IsBallistic, IsMissile = weaponCategory.IsMissile, IsEnergy = weaponCategory.IsEnergy, IsSupport = weaponCategory.IsSupport, IsMelee = weaponCategory.IsMelee, CanUseInMelee = weaponCategory.CanUseInMelee, IsAffectedByEvasive = weaponCategory.IsAffectedByEvasive, ForceLightHitReact = weaponCategory.ForceLightHitReact, DamageReductionMultiplierStat = weaponCategory.DamageReductionMultiplierStat, ToBeHitStat = weaponCategory.ToBeHitStat, DesignMaskString = weaponCategory.DesignMaskString, TurretDamageMultiplier = weaponCategory.TurretDamageMultiplier, VehicleDamageMultiplier = weaponCategory.VehicleDamageMultiplier, MinHorizontalAngle = weaponCategory.MinHorizontalAngle, MaxHorizontalAngle = weaponCategory.MaxHorizontalAngle, MinVerticalAngle = weaponCategory.MinVerticalAngle, MaxVerticalAngle = weaponCategory.MaxVerticalAngle, UIColorRef = weaponCategory.UIColorRef, FallbackUIColor = weaponCategory.FallbackUIColor, Icon = weaponCategory.Icon, HardpointPrefabText = weaponCategory.HardpointPrefabText, UseHardpointPrefabTextAsSuffix = weaponCategory.UseHardpointPrefabTextAsSuffix, });

    WeaponCategory_MDD weaponCategoryRow = SelectWeaponCategoryByID(mdd, weaponCategory.ID);
    return weaponCategoryRow;
}

public static WeaponCategory_MDD InsertOrUpdateWeaponCategoryValue(this MetadataDatabase mdd, WeaponCategoryValue weaponCategory)
{
    InsertOrUpdateEnumValue(mdd, weaponCategory);

    WeaponCategory_MDD weaponCategoryRow = SelectWeaponCategoryByID(mdd, weaponCategory.ID);

    if (weaponCategoryRow == null)
    {
        weaponCategoryRow = InsertWeaponCategoryValue(mdd, weaponCategory);
    }
    else
    {
        weaponCategoryRow = UpdateWeaponCategoryValue(mdd, weaponCategory);
    }

    return weaponCategoryRow;
}

#endregion // WeaponCategory    

That's quite a bit of boilerplate code. And not only is generating the code way faster than I could do it by hand, I can count on it being bug free. Or if there is ever a bug in it, I can fix it in my code generation and rerun it against my table entities to fix the same problem everywhere.

I had been putting this off for a while. Any time I was making a new table for the database, time pressures were always high. It was faster to do the one-off table by hand than to do the code-gen system (in the immediate short term). Now that I finally have some breathing room I was able to put this system in place. It didn't take very long and it will probably pay for the time investment after I do one more of these enumerations.

Personal Project Update - Car Wars

A friend of mine invited me to participate in his company's game jam. They all use Unreal so I've been putting my free time into running through Unreal tutorials. I don't want to be a boat anchor on his team. >.< So Car Wars is going to be postponed probably a couple of weeks while I participate in that.

Tips From your Uncle Eck

Keep an eye out for code generation opportunities. If you're writing the same type of code over and over and Generics aren't quite the right fit, consider writing some code generation. It probably doesn't take as long as you might think and you'll save yourself time in the long run.

Links

• Twitter Post: https://twitter.com/Eck314/status/1162755432592683008