Database Migration Framework

I finally have a framework for handling database changes implemented and being reused by multiple services in the game! It cleaned up a lot of code for both the creation of database tables and the definition of "default" entities for the game (towns, races, doors, npcs, etc.). Before I would check whether a table existed upon starting any of my game servers/content service, then create the table + all of its "default" entities if not.

Motivation

Since my tables are simply created from C# classes w/ annotations, a change in the class also meant I'd have to manually update my world databases and content databases tables while the app/service are offline, one at a time, replicating each change I made to the C# class directly in the database.

An example Entity class. The annotations primarily drive table creation, but can also be used to lookup the proper table name for an entity.

using Funkhouse.Data.Attributes;
using Funkhouse.Data.Entities;
using PetaPoco;

namespace Tnj.Server.Core.Npc.Entities {

    [ExplicitColumns]
    [TableName("npc_faction_awards")]
    [PrimaryKey("Id")]
    public class NpcFactionAwardEntity : DatabaseEntity<int> {

        [Field(required: true)]
        [ForeignKey("npc_factions", "Id")]
        public int NpcFactionId { get; set; }

        [Field(required: true)]
        [ForeignKey("factions", "Id")]
        public int FactionId { get; set; }

        [Field(required: true)]
        public short Value { get; set; }
    }
}

How Does It Work?

Now with this new framework framework.. table creation, table modification, inserting of "default" game entities are all defined with "Migration Instruction" classes. Each instruction is tied to a particular version of the database entities. Upon starting a service, the current database/entity version is compared against the target version to find all applicable instruction sets in between. Each instruction is then executed in order. For example:

  • Version 1.0.0 instruction: Create database tables and populate with default entities.
  • Version 1.0.1 instruction: Perform a table.alter() query to rename a field.
  • Version 1.1.0 instruction: Split a single field into two separate fields. Altering the table and copying over any existing records to the new structure.

Here's some of the higher level code which takes a "target version" (i.e. what I configure in my server.yaml, even after the game server/content service is deployed) and compares it to what is stored in the database, performing an upgrade or downgrade if needed.

public void HandleMigrations(dynamic targetVersion, List<Assembly> assemblies = null) {
    var currentVersion = _migrationDao.GetLatestVersion();
    if (currentVersion == targetVersion)
        return;

    var migrationEntity = new MigrationEntity() {
        StartedAt = DateTime.UtcNow,
        Result = MigrationResult.Success,
        StartingVersion = currentVersion,
        TargetVersion = targetVersion
    };

    _logger.Info($"Migrating database entities from version {currentVersion} to {targetVersion}. Please wait...");

    dynamic newVersion;
    try {
        newVersion = Version.Parse(targetVersion) >= Version.Parse(currentVersion) ?
            Upgrade(_dbSessionFactory, currentVersion, targetVersion, assemblies) :
            Downgrade(_dbSessionFactory, currentVersion, targetVersion, assemblies);
    } catch (Exception ex) {
        migrationEntity.FinalVersion = currentVersion;
        migrationEntity.Result = MigrationResult.Failed;
        migrationEntity.CompletedAt = DateTime.UtcNow;
        _migrationDao.Insert(migrationEntity);
        throw ex;
    }

    if (newVersion == null) {
        _logger.Warn($"Database entities were not updated. No migration instructions found. Previous version: {currentVersion}, Target version: {targetVersion}.");
        return;
    }

    if (newVersion != targetVersion) {
        _logger.Warn($"Database entities not updated to expected version. Previous version: {currentVersion}, Target version: {targetVersion}, New version: {newVersion}.");
    } else {
        _logger.Info($"Database entities migrated successfully. Entities have been updated to version {newVersion}.");
    }

    migrationEntity.FinalVersion = newVersion;
    migrationEntity.CompletedAt = DateTime.UtcNow;

    _migrationDao.Insert(migrationEntity);
}

private dynamic Upgrade(IDatabaseSessionFactory dbSessionFactory, dynamic currentVersion, dynamic targetVersion, List<Assembly> assemblies) {
    var db = dbSessionFactory.GetDatabaseSession();
    db.BeginTransaction();
    try {
        var newVersion = VersionMigrator.Upgrade(currentVersion, targetVersion, new object[] { _loggerFactory, _dbSessionFactory, _dataTypeFactory }, assemblies);
        db.CompleteTransaction();
        return newVersion;
    } catch (Exception ex) {
        _logger.Error("Migration of database entities failed. Changes have been rolled back.");
        db.AbortTransaction();
        throw ex;
    }
}

Ignore the use of dynamic.. my lower level class supports integer based versioning vs semantic versioning but I don't have that supported here yet. Haven't decided whether to stick with dynamic or switch to generics yet, either.. 🙂

But notice that I can take an array of assemblies where "MigrationInstructions" may live. If any instructions are found for the versions we're upgrading from <---> to, in ANY of those assemblies, they'll be executed in the proper order.

At this point.. all of my table/default entity creation code for every c# component/service is defined within one of these migration instructions.

Example Instruction

namespace Tnj.Server.World.Data.Instructions {
    [MigrationInstruction("1.0.0", order: 1)]
    public class MigrationInstructions_Game_World_1_0_0 : IMigrationInstruction {
        private readonly IDatabase _database;
        private readonly IDataTypeFactory _dataTypeFactory;
        private readonly IFunkyLogger _logger;

        public MigrationInstructions_Game_World_1_0_0(IFunkyLoggerFactory loggerFactory, IDatabaseSessionFactory dbSessionFactory, IDataTypeFactory dataTypeFactory) {
            _database = dbSessionFactory.GetDatabaseSession();
            _dataTypeFactory = dataTypeFactory;
            _logger = loggerFactory.GetLogger<MigrationInstructions_Game_World_1_0_0>();
        }

        private void CreateTable<T>() {
            DatabaseUtilities.CreateTable<T>(_logger, _database, _dataTypeFactory);
        }

        public void OnDowngrade() {
            throw new System.NotImplementedException();
        }

        private void SetInitialWorldSettingsEntitites() {
            _database.Insert(new WorldSettingsEntity() {
                GameTime = new DateTime(2322, 4, 1, 12, 0, 0)
            });
        }

        public void OnUpgrade() {
            CreateTable<WorldSettingsEntity>();
            CreateTable<PlayerEntity>();
            CreateTable<PlayerBindEntity>();
            CreateTable<PlayerFactionEntryEntity>();
            CreateTable<PlayerInventoryItemEntity>();
            CreateTable<PlayerSkillEntity>();
            CreateTable<GuildEntity>();
            CreateTable<GuildMemberEntity>();
            CreateTable<GuildRoleEntity>();
            CreateTable<PlayerCorpseEntity>();
            CreateTable<PlayerCorpseInventoryItemEntity>();
            CreateTable<TemporaryMerchantItemEntryEntity>();
            CreateTable<PlayerScribedSpellEntity>();
            CreateTable<PlayerMemorizedSpellEntity>();
            CreateTable<PlayerActiveSpellEntity>();
            CreateTable<PlayerActiveSpellEffectEntity>();

            SetInitialWorldSettingsEntitites();
        }
    }
}

Other Uses

In the end I was able to define a common set of instructions which are used by the game servers during local development and by the content service when it is live in production.

These are instructions for entities which would not normally exist inside the game database, but are instead fetched/download as static assets from the content website. However, I have a mechanism to store/load all entities from a database during local development only regardless if they're entities normally controlled by the content website. I also have all of the initial entities which were defined for the content website in a very lengthy instruction.

That led me to the below design.

With the above I am able to reuse the giant list of entities defined for the content service when developing locally. Now I don't need to maintain a separate set of "test" entities for development and will automatically get any changes defined in the shared instruction set

Although better organization is cool, I think the biggest benefit is that I can now define instructions to alter, delete, add, insert, transform the entities in my tables in a programmatic/"versioned" way. With how much I have externalized now I can pretty much change the entities/database structure without recompiling the actual game servers/player client.

Next Steps For This Framework

The framework is pretty much complete and already in use by the game, content service, etc. now. Over the next few months I will be evaluating how this pattern scales with the database changes I need to make. Then I will polish up each interface before adding this as a feature to the Funkhouse Database Framework.

What did you think about this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.