DbMigrate/DbComparer.cs

using System;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace DbMigrate {
    public class DbComparer {
        public SqlDatabase BaseDatabase { get; set; }
        public SqlDatabase CompareDatabase { get; set; }

        public bool RemoveUnusedTablesFromBaseDb { get; set; } = false;
        public bool RemoveUnusedColumnsFromBaseDb { get; set; } = false;
        public bool CopyDataFromNewDb { get; set; } = false;

        public string UpdateSqlScript { get; private set; }

        public DbComparer(SqlDatabase baseDb, SqlDatabase compareDb) {
            BaseDatabase = baseDb;
            CompareDatabase = compareDb;
        }


        private string[] getCommonColumns(string table) {
            string[] baseColumns = BaseDatabase[table].GetColumnNames();
            string[] newColumns = CompareDatabase[table].GetColumnNames();

            return baseColumns.Where(f => newColumns.Contains(f)).ToArray();
        }

        public string Compare() {
            var sb = new StringBuilder();

            foreach (var table in CompareDatabase.Tables) {
                if (!BaseDatabase.ContainsTable(table.TableName)) {
                    // Table does not exist; Create it
                    sb.AppendLine("\r\n-- Create table " + table.TableName);
                    sb.Append(table.CreateTableSql);

                    if (CopyDataFromNewDb) {
                        // Copy data
                        sb.AppendLine("\r\n-- Copy data into new table " + table.TableName);
                        // TODO : Copy data from existing table in the new schema
                    }
                    continue;
                }

                // The table exists, now we need to compare everything to verify no updates are required!
                foreach (var index in table.Indexes) {
                    if (!BaseDatabase[table.TableName].HasIndex(index.Key)) {
                        sb.AppendLine("\r\n-- Create index " + index.Key);
                        sb.AppendLine(index.Value);
                    } else {
                        // Index exists, check if it's the same
                        if (BaseDatabase[table.TableName].Indexes[index.Key] != index.Value) {
                            sb.AppendLine("\r\n-- Drop and recreate index " + index.Key);
                            sb.AppendLine("DROP INDEX IF EXISTS " + index.Key + ";");
                            sb.AppendLine(index.Value);
                        }
                    }
                }

                foreach (var trigger in table.Triggers) {
                    if (!BaseDatabase[table.TableName].HasTrigger(trigger.Key)) {
                        sb.AppendLine("\r\n-- Create trigger " + trigger.Key);
                        sb.AppendLine(trigger.Value);
                    } else {
                        // Trigger exists, check if it's the same
                        if (BaseDatabase[table.TableName].Triggers[trigger.Key] != trigger.Value) {
                            sb.AppendLine("\r\n-- Drop and recreate trigger " + trigger.Key);
                            sb.AppendLine("DROP TRIGGER IF EXISTS " + trigger.Key + ";");
                            sb.AppendLine(trigger.Value);
                        }
                    }
                }

                bool alterTableRequired = false;
                foreach (var column in table.GetColumnNames()) {
                    if (!BaseDatabase[table.TableName].HasColumn(column)) {
                        // The database column does not exist
                        alterTableRequired = true;
                        break;
                    }
                    if (BaseDatabase[table.TableName].Columns[column] != table.Columns[column]) {
                        // The database column exists but is different
                        alterTableRequired = true;
                        break;
                    }
                }

                // TODO : This deletes unused columns - we should probably make this optional
                if (alterTableRequired) {
                    sb.AppendLine("\r\n-- Table " + table.TableName + " requires alteration - Create temp table and move data");
                    string[] commonColumns = getCommonColumns(table.TableName);
                    string columnList = string.Join(",", commonColumns);

                    string sql = CompareDatabase[table.TableName].CreateTableSql;
                    string newTableName = table.TableName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss");
                    sql = Regex.Replace(sql, "CREATE TABLE (\\w*)", "CREATE TABLE IF NOT EXISTS $1_" + newTableName.Substring(newTableName.IndexOf("_") + 1));
                    sb.AppendLine(sql);

                    sb.AppendLine("\r\n-- Copy data from existing table to new table");
                    sb.AppendLine("INSERT INTO " + newTableName + " (" + columnList + ")");
                    sb.AppendLine("\tSELECT " + columnList);
                    sb.AppendLine("\tFROM " + table.TableName + ";");
                    sb.AppendLine("\r\n-- Drop existing table");
                    sb.AppendLine("DROP TABLE " + table.TableName + ";");
                    sb.AppendLine("\r\n-- Rename the new table to replace the old table");
                    sb.AppendLine("ALTER TABLE " + newTableName + " RENAME TO " + table.TableName + ";");
                }
            }


            UpdateSqlScript = "BEGIN TRANSACTION;\r\n" + sb.ToString() + "\r\nCOMMIT;";
            return UpdateSqlScript;
        }
    }
}