I'm using Temporal Tables on postgresql (https://github.com/arkhipov/temporal_tables) and C# with dapper.
I'm storing an entity together with its changes. Here's an example entity, with an Id and two values. It was created in 2006 and underwent two simultaneous changes in 2007
| ID | IntValue | StrValue | sys_period |
| 1 | 0 | NULL | [2006-08-08, 2007-02-27) |
| 1 | 1 | "foo" | [2007-02-27, ) |
Here's my setup for storing that kind of data
I have a table with the current entity state:
CREATE TABLE public.SomeEntity
(
Id i integer PRIMARY KEY,
IntValue integer NULL,
StrValue text NULL,
sys_period tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, null)
);
CREATE TABLE public.SomeEntity_History (LIKE public.SomeEntity);
CREATE TRIGGER versioning_trigger
BEFORE INSERT OR UPDATE OR DELETE ON public.SomeEntity
FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period','public.SomeEntity_History ', true);
CREATE VIEW SomeEntity_With_History AS
SELECT * FROM SomeEntity
UNION ALL
SELECT * FROM SomeEntity_History;
SELECT * FROM SomeEntity_With_History WHERE Id = 1 now gives the above table
So, I have a list of the state of the entity at certain times (which I'm going to call 'history'), but how do I see changes?
I see a change as something like this model (in C#):
public class EntityChange
{
public DateTime Timestamp { get; set; }
public PropertyChange[] Changes { get; set; }
}
public class PropertyChange
{
public string PropertyName { get; set; }
public object OldValue { get; set; }
public object NewValue { get; set; }
public Type Type { get; set; }
}
I have a way to change History into Changes
It's a query in SQL and some mapping, type conversion and nesting in C#. But it seems messy. Is it any good?
public async Task<IEnumerable<EntityChange>> GetArticleChangesAsync(int articleId)
{
var propertyChanges = await _context.GetConnection().QueryAsync<PropertyChangeQueryItem>(
@"SELECT PropertyName, NewValue, OldValue, TypeName, Timestamp
FROM
(
SELECT
IntValue, LAG(IntValue) OVER previous AS old_IntValue,
StrValue, LAG(StrValue) OVER previous AS old_StrValue,
LOWER(sys_period) AS timestamp
FROM someentity_with_history
WHERE id = @id
WINDOW previous AS (PARTITION BY id ORDER BY sys_period ASC)
) AS rows
CROSS JOIN LATERAL
(
VALUES
('IntValue', CAST (IntValue AS text), CAST (old_IntValue AS text), @intType),
('StrValue', StrValue, old_StrValue, @stringType),
) AS entityChanges(PropertyName, NewValue, OldValue, TypeName)
WHERE NewValue IS DISTINCT FROM OldValue",
new
{
articleId,
intType = typeof(int?).FullName,
stringType = typeof(string).FullName
});
return PropertyChangeQueryItem.DeNormalize(propertyChanges);
}
internal class PropertyChangeQueryItem
{
public DateTime Timestamp { get; set; }
public string PropertyName { get; set; }
public string NewValue { get; set; }
public string OldValue { get; set; }
public string TypeName { get; set; }
public static IEnumerable<EntityChange> DeNormalize(IEnumerable<PropertyChangeQueryItem> items)
{
var groups = items.GroupBy(ic => ic.Timestamp);
var entityChanges = groups.Select(grp => new EntityChange
{
Timestamp = grp.Key,
Changes = grp.Select(i =>
{
var type = GetType(i.TypeName);
return new PropertyChange
{
PropertyName = i.PropertyName,
NewValue = DeStringify(i.NewValue, type),
OldValue = DeStringify(i.OldValue, type),
Type = type
};
})
.ToArray()
});
return entityChanges;
}
private static Type GetType(string typeName)
{
var type = Type.GetType(typeName);
return Nullable.GetUnderlyingType(type) ?? type;
}
private static object DeStringify(string value, Type type)
{
return value == null ? null : Convert.ChangeType(value, type);
}
}
So what's happening?
I match each row of history with its preceeding row using 'LAG' with 'previous'.
Then, I cut apart these rows so that I have one row for each column, using CROSS JOIN LATERAL.
Then I just choose the rows where the OldValue is different from the new value.
Since I can't be specific about what type I'd like "OldValue" and "NewValue" to be, I have to convert everything to a string. I include the C# type information at that point so I can recover it back into the actual type.
So that query ends up transforming my entity history into this:
| PropertyName | NewValue | OldValue | TypeName | Timestamp |
| "IntValue" | 1 | 0 | System.int32 | 2007-02-27 |
| "StrValue" | "foo" | NULL | System.string | 2007-02-27 |
Then I use the C# to shuffle that into a nested class model and unstringify the types in the DeNormalize method. In the above example table that would result in one entity change at 2007-02-27 with two property changes:
{
DateTime: 2007-02-27
Changes: [
{
PropertyName: "IntValue",
OldValue: 0,
NewValue: 1,
Type: typeof(int)
},
{
PropertyName: "StrValue",
OldValue: null,
NewValue: "foo",
Type: typeof(string)
}
]
}
So, this works. It doesn't return unnecessary data to C# from sql as it does most of the heavy lifting in SQL. But having to specify every column name twice in the SQL code and having to leave C# type hints and then un-stringify everything is messy. And I'm not sure if the query could be more elegant. What do you think?
