Jump to content
Search In
  • More options...
Find results that contain...
Find results in...

Welcome to our site

Take a moment to join our board

Sign in to follow this  
Vendramini

Simple database Migration manager

Recommended Posts

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 server.

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

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

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

        public string LastExcepttion
        {
            get; private set;
        }

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

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

                if (Path.GetFileNameWithoutExtension(file).Length < 16)
                {
                    Console.WriteLine([email protected]"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([email protected]"Skip migration file {info.Name} invalid date stamp yyyyMMddHHmmss");
                    continue;
                }

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

                if (!Execute(fileName))
                {
                    Console.WriteLine([email protected]"Error on executing migration for file {info.Name}! Error: {LastExcepttion}");
                    continue;
                }

                ExecuteInsert($"INSERT INTO `migrations` (`file`, `execute_time`) VALUES ('{fileName}', '{DateTime.Now:yyyy-MM-dd HH:mm:ss}')");
            }
        }

        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_szMirationsPath}{fileName}"));
                    script.Execute();
                    conn.Close();
                }
                return true;
            }
            catch (MySqlException ex)
            {
                LastExcepttion = 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)
            {
                LastExcepttion = 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)
            {
                LastExcepttion = ex.Message;
                return -1;
            }
        }

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

How to use:

  1. Create a 'migrations' folder on your executable path
  2. 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
  3. Before loading your mysql data just create a new instance of the MigrationManager and call .Migrate() method
  4. 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!

20200129182100_MyVeryFirstMigration.sql

  • Like 1

Share this post


Link to post
Share on other sites

Looks good. I think someone mentioned naming conventions on the other board. Here're the naming guidelines I follow for dotnet. Outside of naming conversations, I prefer nice interfaces for parameterized queries over database ORMs. Maybe I've worked in Go for too long though. I can see a system like this doing really well for plain MySQL.

  • Like 1

Share this post


Link to post
Share on other sites

Nice! I would also add that migration systems generally come with Up/Down changes. If a migration fails, I would expect the next migration to not run and all other changes be reverted. The reason being is that if I build a table called Users, then a table called Characters that has a foreign key, if Users table fails to execute properly I would expect it to revert all changes and stop all remaining changes. In this case, Characters table would fail to be created due to the foreign key.

 

I like the idea of this as it requires raw queries over some sort of abstract migration system. If I have all of the sql written, it makes it easier if the current migration system becomes abandoned or perhaps a better one is released (I like options.)

  • Like 2

Share this post


Link to post
Share on other sites
13 hours ago, W1cked said:

Nice! I would also add that migration systems generally come with Up/Down changes. If a migration fails, I would expect the next migration to not run and all other changes be reverted. The reason being is that if I build a table called Users, then a table called Characters that has a foreign key, if Users table fails to execute properly I would expect it to revert all changes and stop all remaining changes. In this case, Characters table would fail to be created due to the foreign key.

 

I like the idea of this as it requires raw queries over some sort of abstract migration system. If I have all of the sql written, it makes it easier if the current migration system becomes abandoned or perhaps a better one is released (I like options.)

I'm working on the improvements hehehe but since I'm working for two companies now, development of my own things became slow. I'm working on doing something like this for my company, but it's for SQLServer. I'll convert be converting it to MySQL soon.

Also for naming conventions I like how it was done in C++, sometimes I mistakenly invert somethings but I try to do it correctly xD love sz, psz, n, dw, qw, etc.

And thanks for the feedback.

Edited by Spuzzum

Share this post


Link to post
Share on other sites
12 hours ago, Spuzzum said:

Also for naming conventions I like how it was done in C++, sometimes I mistakenly invert somethings but I try to do it correctly xD love sz, psz, n, dw, qw, etc.

Hungarian notation is no longer recommended in modern codebases. Outside of old C/C++ codebases or APIs (like WinAPI), it is less and less common to prefix the variables with some kind of type. Personally, I'd strongly suggest you not to get use to it (and even less to introduce it in other languages).

  • Like 3

Share this post


Link to post
Share on other sites

I was going to add the same. That naming convention is no longer recommended unless surrounding code already follows that naming convention. Generally, you want to learn and follow the guidelines of a language so that any project you go to with that language is consistent and easy to read and contribute towards.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.