Teaching SQLite how to handle DateTimeOffset values #6

Welcome back to the final part of this tutorial! By now, our SQLite DateTimeOffset serializer is functional, all that’s missing are a few improvements to make it more flexible.

First of all, our custom implementation still does not solve the problem that calculations can not be executed on date / time values stored as text in a database (e.g., sorting database columns by date / time. The solution to this is to store both the serialized DateTimeOffset text value, and the original UTC value as int timestamp. Since calculations are not necessary in all cases, we’ll make this duplicate storage optional.

Obviously, the first thing we need to adapt is the DateTimeOffsetSerializeAttribute: It needs an (optional) constructor parameter that controls whether the original value should be stored in the database or not, and a property to store this flag. Since, as mentioned, the constructor parameter is optional (to be able to use the property without needing to think about it), we keep the original constructor as well – the property’s default value is false, which means that if the parameter is omitted, the value will be stored as text only:

[AttributeUsage(AttributeTargets.Property)]
public class DateTimeOffsetSerializeAttribute : Attribute
{
	public bool KeepOriginal { get; private set; } = false;

	public DateTimeOffsetSerializeAttribute() { }

	public DateTimeOffsetSerializeAttribute(bool keepOriginal)
	{
		KeepOriginal = keepOriginal;
	}
}

Now it’s time to adapt the post-build task. The first thing we’ll need to do is enhance the FindFlaggedProperties method, since this needs to read the DateTimeOffsetSerialize attribute’s boolean KeepOriginal flag:

internal static IEnumerable<FlaggedProperty> FindFlaggedProperties(this TypeDefinition type)
{
	var results = new List<FlaggedProperty>();

	foreach (var property in type.Properties.Where(p =>
		p.HasCustomAttributes &&
		p.CustomAttributes.Any(a => a.AttributeType.FullName.Equals("SQLite.Net.DateTimeOffset.Attributes.DateTimeOffsetSerializeAttribute")) &&
		!p.CustomAttributes.Any(a => a.AttributeType.FullName.Equals("SQLite.Net.Attributes.IgnoreAttribute"))))
	{
		if (!property.PropertyType.FullName.Equals("System.DateTimeOffset"))
			throw new Exception("The DateTimeOffsetSerialize attribute may only be used on properties of type DateTimeOffset!");

		var serializeAttribute =
			property.CustomAttributes.FirstOrDefault(
				a => a.AttributeType.FullName.Equals("SQLite.Net.DateTimeOffset.Attributes.DateTimeOffsetSerializeAttribute"));

		var keepOriginal = false;
		if (serializeAttribute.HasConstructorArguments)
		{
			foreach (var argument in serializeAttribute.ConstructorArguments)
			{
				if (argument.Type.MetadataType == MetadataType.Boolean)
					keepOriginal = (bool) argument.Value;
			}
		}

		results.Add(new FlaggedProperty
		{
			Property = property,
			KeepOriginal = keepOriginal
		});
	}

	return results;
}

You’ll notice that this method does not return a pure enumerable of type PropertyDefinition any more, instead I decided to create a custom type that wraps both the target property’s PropertyDefinition and its KeepOriginal flag. This also makes future enhancements easy, as you will see in a minute. Here is the custom type in details:

internal struct FlaggedProperty
{
	internal PropertyDefinition Property { get; set; }
	internal bool KeepOriginal { get; set; }
}

Finally, we need to take into account the KeepOriginal flag while rebuilding individual properties, of course. This means:

  • Pass the boolean flag into the RebuildProperty method for each property to be adapted:
    internal static void RebuildProperty(this TypeDefinition type, PropertyDefinition property, bool keepOriginal)
    
  • Decide on database column naming (if the original property is flagged with the SQLite [Column] attribute, we previously used this name for the new column, but when the original property shall be stored in the database as well it should also keep its name):
    var columnName = property.Name;
    if (keepOriginal)
    {
    	// If the original property shall be stored in the database as well, we are forced to use the new
    	// property's name as column name
    	columnName = propertyNameDuplicate;
    }
    else if (property.HasCustomAttributes)
    {
    	foreach (var attribute in property.CustomAttributes)
    	{
    		if (attribute.AttributeType.FullName.Equals("SQLite.Net.Attributes.ColumnAttribute") && attribute.HasConstructorArguments)
    		{
    			columnName = attribute.ConstructorArguments[0].Value as string;
    			break;
    		}
    	}
    }
    
  • Omit the [Ignore] attribute on the original property, if KeepOriginal is true:
    if (!keepOriginal)
    {
    	var ignoreCtorMethod = type.Module.FindIgnoreAttributeCtorMethod(log);
    	if (ignoreCtorMethod == null) return false;
    	property.CustomAttributes.Add(new CustomAttribute(ignoreCtorMethod));
    }
    

That’s basically all we have to do! And the best thing, additional improvements are easy to implement in a similar way. For example, what about adding a second (optional) parameter to the DateTimeOffsetSerialize attribute that defines the desired date/time format to be used in the database? Extend the FlaggedProperty struct with a third attribute that holds the format string, fill it while scanning for properties, and use it when rebuilding those properties!

There are a few additional improvements I made, the final code it available open-source on GitHub. If you just want to try it out, you may also install the final NuGet package!