Migration to add a RowVersion column to an existing Sqlite table

[spabd]

We have a DatabaseVersion table defined in Migration1000 to which we add a RowVersion column in Migration1010 using Db.Migrate as below.

However, whilst all other tables created with a RowVersion column added at the time of creation are correctly created with the RowVersion column Update Trigger, the Update Trigger is missing for the migrated DatabaseVersion.RowVersion column.

Are we using it incorrectly?

public sealed class Migration1000 : MigrationBase
{
    private sealed class DatabaseVersion
    {
        public int Id { get; set; }

        [Required]
        public int MajorVersion { get; set; }

        [Required]
        public int MinorVersion { get; set; }
    }
    
    public override void Up()
    {
        Db.CreateTableIfNotExists< DatabaseVersion >();
    }
}


public sealed class Migration1010 : MigrationBase
{
    private sealed class DatabaseVersion
    {
        [RowVersion]
        public ulong RowVersion { get; set; }
    }
    
    public override void Up()
    {
        if ( Db.ColumnExists( nameof( DatabaseVersion.RowVersion ), nameof( DatabaseVersion ) ) == false )
        {
            Db.Migrate< DatabaseVersion >();
        }
    }
}

[mythz]

Yeah migrate only adds/removes/renames columns, it doesn't add the triggers at table creation time.

You can create the SQLite's RowVersion trigger with something like:

// Up
Db.ExecuteSql(Db.GetDialectProvider().ToPostCreateTableStatement(typeof(DatabaseVersion).GetModelMetadata()));

// Down
Db.ExecuteSql(Db.GetDialectProvider().ToPostDropTableStatement(typeof(DatabaseVersion).GetModelMetadata()));

Note the way you're using migrations wont let you rollback, revert, etc. as you should be doing compensatory logic in your Down() method and I'd avoid using defensive logic and APIs in your implementations to ensure the migrations are being run in the correct state and order, i.e. if the table already exists when the migration to create it is run something is wrong with the db and migration state which should be investigated to manually resolved to ensure they're in the correct state.

public sealed class Migration1000 : MigrationBase
{
    private sealed class DatabaseVersion
    {
        public int Id { get; set; }

        [Required]
        public int MajorVersion { get; set; }

        [Required]
        public int MinorVersion { get; set; }
    }
    
    public override void Up()
    {
        Db.CreateTable<DatabaseVersion>();
    }
    
    public override void Down()
    {
        Db.DropTable<DatabaseVersion>();
    }
}

public sealed class Migration1010 : MigrationBase
{
    private sealed class DatabaseVersion
    {
        [RowVersion]
        public ulong RowVersion { get; set; }
    }
    
    public override void Up()
    {
        Db.Migrate<DatabaseVersion>();
        Db.ExecuteSql(Db.GetDialectProvider().ToPostCreateTableStatement(typeof(DatabaseVersion).GetModelMetadata()));
    }
    
    public override void Down()
    {
        Db.ExecuteSql(Db.GetDialectProvider().ToPostDropTableStatement(typeof(DatabaseVersion).GetModelMetadata()));
        Db.Revert<DatabaseVersion>();
    }
}