2
\$\begingroup\$

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?

\$\endgroup\$
7
  • 1
    \$\begingroup\$ We require concrete code from a project, with sufficient context for reviewers to understand how that code is used. Pseudocode, stub code, hypothetical code, obfuscated code,... are outside the scope of this site. Please follow the tour, and read "What topics can I ask about here?", "How do I ask a good question?" and "What types of questions should I avoid asking?". \$\endgroup\$
    – BCdotWEB
    Jul 7 '20 at 9:18
  • 3
    \$\begingroup\$ Is there a reason you're not calling a stored procedure from C#? The question is a little bit confusing, it is primarily an SQL question but includes C# as well. \$\endgroup\$
    – pacmaninbw
    Jul 7 '20 at 12:28
  • 1
    \$\begingroup\$ I consider this on-topic. The only risky bit is the title prefix, which I will delete. \$\endgroup\$
    – Reinderien
    Jul 7 '20 at 15:08
  • \$\begingroup\$ @Reinderien Can you answer it then? It's accumulating close votes. \$\endgroup\$
    – Mast
    Jul 9 '20 at 19:10
  • 2
    \$\begingroup\$ with the row proceeding it - you probably mean "preceding it". \$\endgroup\$
    – Reinderien
    Jul 9 '20 at 22:59
3
\$\begingroup\$

Externalize your DML

Given the length of the query written in GetArticleChangesAsync, I would expect that either

  • it be moved to a stored procedure (common but IMO overkill); or
  • moved to a view (my usual preference given read-only queries like this).

There are several reasons for this, including:

  • If you have a DBA, they will have more control over the fine-grained details of the query
  • The query is by its nature more closely coupled to the database design than the application design
  • It will be often easier to update a view than rebuild and redeploy the entire application
\$\endgroup\$
2
  • \$\begingroup\$ Thanks +1. Interesting answer. Made me think. Probably not for me though. I think reasons 1 and 3 are good points, but more suitable for environments where time-to-deploy is slow or deployment downtime is a problem. I'm not comfortable with letting SQL changes, even efficiency ones, happen without tests running. I also have a fully reproducible environment. So, I'd need to write migrations for DB changes, which are a hassle compared to changing a method. And I'd need to worry about versioning and compatibility between old and new running instances as they swap over. \$\endgroup\$ Jul 10 '20 at 10:39
  • 1
    \$\begingroup\$ It's fair, but it sounds like you have a different problem: DB migrations should not be a hassle, and if they are you need to revisit your migration procedure. \$\endgroup\$
    – Reinderien
    Jul 10 '20 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.