Simple database Migration manager

[pintinho12]

Hello!

Today I've got a few free minutes at my work and coded something that I noticed that I need long time ago! There's room for improvements of course but this will be good for those who often updates their game database and also need to keep track of the changes. The script is simple and I'll be updating here as I upgrade my code on my source.

 Spoiler

using System;
using System.IO;
using MySql.Data.MySqlClient;

namespace FtwCore.Database.Migrations
{
    public sealed class MigrationManager
    {
        private string m_szConnectionString;
        private string m_szMigrationsPath;

        public MigrationManager(string host, string user, string pass, string data, int port)
        {
            m_szMigrationsPath = Environment.CurrentDirectory + "\\migrations\\";
            m_szConnectionString = $"Server={host};Port={port};Database={data};Uid={user};Password={pass};charset=utf8;";
        }

        public string LastException
        {
            get; private set;
        }

        public void Migrate()
        {
            if (!Directory.Exists(m_szMigrationsPath))
                Directory.CreateDirectory(m_szMigrationsPath);

            string[] files = Directory.GetFiles(m_szMigrationsPath);
            foreach (var file in files)
            {
                FileInfo info = new FileInfo(file);
                if (info.Extension != ".sql")
                {
                    Console.WriteLine($@"Skip migration file {info.Name} not sql");
                    continue;
                }

                if (Path.GetFileNameWithoutExtension(file).Length < 16)
                {
                    Console.WriteLine($@"Skip migration file {info.Name} name length less than 16");
                    continue;
                }

                string szYear = info.Name.Substring(0, 4);
                string szMonth = info.Name.Substring(4, 2);
                string szDay = info.Name.Substring(6, 2);
                string szHours = info.Name.Substring(8, 2);
                string szMinutes = info.Name.Substring(10, 2);
                string szSeconds = info.Name.Substring(12, 2);

                if (!int.TryParse(szYear, out int year)
                    || !int.TryParse(szMonth, out int month)
                    || !int.TryParse(szDay, out int day)
                    || !int.TryParse(szHours, out int hour)
                    || !int.TryParse(szMinutes, out int minute)
                    || !int.TryParse(szSeconds, out int second)
                    || !DateTime.TryParse($"{year}-{month}-{day} {hour}:{minute}:{second}", out DateTime dtFullTime))
                {
                    Console.WriteLine($@"Skip migration file {info.Name} invalid date stamp yyyyMMddHHmmss");
                    continue;
                }

                string fileName = Path.GetFileName(file);
                if (HasExecuted(fileName))
                {
                    continue;
                }

                if (!Execute(fileName))
                {
                    Console.WriteLine($@"Error on executing migration for file {info.Name}! Error: {LastException}");
                    continue;
                }

                ExecuteInsert($"INSERT INTO `migrations` (`file`, `execute_time`) VALUES ('{fileName}', '{DateTime.Now:yyyy-MM-dd HH:mm:ss}')");
                Console.WriteLine($@"Migration file {info.Name} has been executed");
            }
        }

        public bool Execute(string fileName)
        {
            if (!fileName.ToLower().EndsWith(".sql"))
                fileName += ".sql";

            try
            {
                using (MySqlConnection conn = new MySqlConnection(m_szConnectionString))
                {
                    conn.Open();
                    MySqlScript script = new MySqlScript(conn, File.ReadAllText($"{m_szMigrationsPath}{fileName}"));
                    script.Execute();
                    conn.Close();
                }
                return true;
            }
            catch (MySqlException ex)
            {
                LastException = ex.Message;
                return false;
            }
        }

        public bool HasExecuted(string fileName)
        {
            string query = "SELECT id FROM migrations WHERE `file`[email protected] LIMIT 1";
            try
            {
                using (MySqlConnection conn = new MySqlConnection(m_szConnectionString))
                {
                    using (MySqlCommand command = new MySqlCommand(query, conn))
                    {
                        conn.Open();
                        command.Prepare();
                        command.Parameters.AddWithValue("@file", fileName);
                        bool result = command.ExecuteReader().HasRows;
                        conn.Close();
                        return result;
                    }
                }
            }
            catch (MySqlException ex)
            {
                LastException = ex.Message;
                return false;
            }
        }

        public int ExecuteInsert(string szQuery)
        {
            try
            {
                int result = -1;
                using (MySqlConnection conn = new MySqlConnection(m_szConnectionString))
                {
                    using (MySqlCommand command = new MySqlCommand(szQuery, conn))
                    {
                        conn.Open();
                        result = command.ExecuteNonQuery();
                        conn.Close();
                    }
                }
                return result;
            }
            catch (MySqlException ex)
            {
                LastException = ex.Message;
                return -1;
            }
        }

        public bool TestConnection()
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(m_szConnectionString))
                {
                    conn.Open();
                    conn.Close();
                }
                return true;
            }
            catch (MySqlException ex)
            {
                LastException = ex.Message;
                return false;
            }
        }
    }
}

How to use:

• Create a 'migrations' folder on your executable path
• Put all your SQL Scripts in files inside of it
• Files must have yyyyMMddHHmmss as the first 14 characters
• Files must have from 16 to 128 characters
• Before loading your mysql data just create a new instance of the MigrationManager and call .Migrate() method
• Done!

Then, anything that you code on your test database, save the final code on a SQL file, name it 20200129183000 WhateverYouWant.sql and put in your production migrations folder! If the code hasn't been executed, your server will execute and you can keep track of the updates.

Ey! I'm sending your first migration sql file so you can test it! Good luck.

PS: don't forget to change the namespace!

[Spirited]

I see a lot of work went into this, so first thing's first, good job. But doesn't EF Core also allow you to track database migrations via command line?

[turk55]

Originally Posted by Spirited

I see a lot of work went into this, so first thing's first, good job. But doesn't EF Core also allow you to track database migrations via command line?

dotnet ef migrations add [MigrationName]

[pintinho12]

I know that I have a lot of things that I want to implement, but, I use Fluent NHibernate in my project and I don't want to depend on it to do everything. With files I have freedom to write SQL code as I wish.
But this will be updated as I find better pratices too. But for this I mostly just want to depend on MySQL standard library.

[Super Aids]

What's up with the variable names?

[Relic]

Originally Posted by Super Aids

What's up with the variable names?

Looks like he's using an outdated naming convention, back when IDEs weren't as powerful especially with intellisense.



It's all personal preference in the end though.

[KraHen]

I also don't see why the standard .NET migrations are not sufficient for this, but hey. Also, what's the reason behind fluent nhibernate? I'm a bit out of the loop with ORM stuff but IIRC it was really slow compared to the competition (I think it was Dapper maybe?)

[pintinho12]

Originally Posted by KraHen

I also don't see why the standard .NET migrations are not sufficient for this, but hey. Also, what's the reason behind fluent nhibernate? I'm a bit out of the loop with ORM stuff but IIRC it was really slow compared to the competition (I think it was Dapper maybe?)

If I start changing my server ORM now I will need to rewrite the entire server. Fluent NHibernate isn't slow at all, I never had performance issues with it, but since most servers out there doesn't use any ORM.
There's FluentMigrator but I don't want to use it. Also SchemaUpdate once droped all my tables and I got mad at it xD but of course it was my fault.