Today, I’d like to present another real-life example for aspect-oriented programming and post-build reflection. The problem to be solved in a nutshell could be described as follows: How to store date / time values including timezone offset efficiently in an SQLite database?
Let’s look into the architecture in detail:
In a current project, I’m using a local SQLite database from within a .NET application (UWP Windows Store App). Fortunately, there exists the SQLite.Net-PCL library which makes basic access to the database from .NET code possible without manually writing SQL statements, assisted by SQLite-Net Extensions which builds upon the former and acts as (although very simple) OR mapper to the SQLite database.
The core problem lies within the limited range of data types supported by SQLite: If we omit null values, SQLite only understands text, numbers (integers and decimal numbers), and blob data. What about
DateTime values, as commonly known from .NET? Well, by default SQLite.Net-PCL converts
DateTime objects to .NET ticks (a tick corresponding to 100 nanoseconds) and stores this timestamp as integer value. If desired, SQLite.Net-PCL can be forced to store DateTimes as string (following ISO8601 format “YYYY-MM-DD HH:MM:SS.SSS”) by setting the
storeDateTimeAsTicks parameter to false while creating the
SQLiteConnection. (According to SQLite documentation, internal database functions can also handle date / time values as decimal numbers, but since the .NET helper library doesn’t utilize this functionality and it won’t help us in storing timezone offsets, we ignore it for the moment.)
Whenever the user may enter date / time values into the app, they are also asked for timezone information. The reason for this is that the full date / time information is then sent to a backend service to be reviewed by back-office personnel, and these users must see the timezone offset in addition to each date / time value. This means, we need to store timezone information in addition to the date and time values in the database.
The obvious solution is to use .NET
DateTimeOffset objects within data model classes (which will be serialized to SQLite tables), as this simplifies date / time handling within the app since
DateTimeOffset objects already contain the necessary timezone offset. Unfortunately, when writing
DateTimeOffset objects to the database, SQLite.Net-PCL automatically converts them to UTC and stores them as ticks (or as ISO8601 string, as discussed above). This means, although the offset information is available throughout the app, it is lost as soon as a dataset it serialized to the database.
After some hours of brainstorming, I came up with the following approaches:
- Manually convert
stringproperties and store these, or
- extract the DateTime and offset (as
TimeSpandate type) parts of the
DateTimeOffset, and store these in two separate columns
- Store the
TimeSpan(offset) parts of the
DateTimeOffsetin two columns of a separate table, and maintain references to this separate table
- Use SQLiteNetExtensions’
TextBlobattribute to serialize the
DateTimeOffsetto a single text column
stringproperties on data models (which will be converted to SQLite tables), and convert them to
DateTimeOffsetobjects whenever they are used (e.g., in ViewModels) through AutoMapper or a similar library
Unfortunately, none of these is feasible for most real-life applications:
- For #1 and #2, we’d need to add additional properties to the data model, flag the original
DateTimeOffsetproperties with the SQLite.NET
[Ignore]attribute, and handle manual conversion (in both directions) – the effort for these approaches rises with the number of data model classes / SQLite tables.
- For #3, we’d need to define a custom data type (in order to specify how the
TimeSpanparts should be stored), and cannot use the default .NET
- For #4, we’d need to make sure that only SQLiteNetExtensions’ extension methods are used for DB insert / update, and we’d still need an additional
stringproperty on all the data model classes.
- #5 looks most promising, but (as well as #1 and #4, by the way) it still stores values as string, which means we cannot perform mathematical operations (e.g. comparisons in order to sort data sets) on DateTime columns…
What are we gonna do?
Alright, let’s talk about approach #6! First, we’re going to define a .NET attribute that can be used to flag those
DateTimeOffset objects in data model classes that should be stored including timezone offset, instead of converting them to UTC timestamps. In addition, we’ll need a separate process (in its simplest form, this is a .NET console application) that scans a given .NET assembly’s code, looks for properties flagged with the above-mentioned attribute, and adds some magic to the respective SQLite table class… For details, read on in part #2 of this tutorial!