TransformationProvider.cs

#region License

//The contents of this file are subject to the Mozilla Public License
//Version 1.1 (the "License"); you may not use this file except in
//compliance with the License. You may obtain a copy of the License at
//http://www.mozilla.org/MPL/
//Software distributed under the License is distributed on an "AS IS"
//basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
//License for the specific language governing rights and limitations
//under the License.

#endregion

using DotNetProjects.Migrator.Framework;
using DotNetProjects.Migrator.Framework.Loggers;
using DotNetProjects.Migrator.Framework.Models;
using DotNetProjects.Migrator.Framework.SchemaBuilder;
using DotNetProjects.Migrator.Providers.Impl.SQLite;
using DotNetProjects.Migrator.Providers.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Text;
using ForeignKeyConstraint = DotNetProjects.Migrator.Framework.ForeignKeyConstraint;
using ForeignKeyConstraintType = DotNetProjects.Migrator.Framework.ForeignKeyConstraintType;
using Index = DotNetProjects.Migrator.Framework.Index;

namespace DotNetProjects.Migrator.Providers;

/// <summary>
/// Base class for every transformation providers.
/// A 'tranformation' is an operation that modifies the database.
/// </summary>
public abstract class TransformationProvider : ITransformationProvider
{
    private string _scope;
    protected readonly string _connectionString;
    protected readonly string _defaultSchema;
    private readonly ForeignKeyConstraintMapper constraintMapper = new();
    protected List<long> _appliedMigrations;
    protected IDbConnection _connection;
    protected bool _outsideConnection = false;
    protected Dialect _dialect;
    private ILogger _logger;
    private IDbTransaction _transaction;

    protected TransformationProvider(Dialect dialect, string connectionString, string defaultSchema, string scope)
    {
        _dialect = dialect;
        _connectionString = connectionString;
        _defaultSchema = defaultSchema;
        _logger = new Logger(false);
        _scope = scope;
    }

    protected TransformationProvider(Dialect dialect, IDbConnection connection, string defaultSchema, string scope)
    {
        _dialect = dialect;
        _connection = connection;
        _outsideConnection = true;
        _defaultSchema = defaultSchema;
        _logger = new Logger(false);
        _scope = scope;
    }

    public IMigration CurrentMigration { get; set; }

    private string _schemaInfotable = "SchemaInfo";
    public string SchemaInfoTable
    {
        get
        {
            return _schemaInfotable;
        }
        set
        {
            _schemaInfotable = value;
        }
    }

    public int? CommandTimeout { get; set; }

    public IDialect Dialect
    {
        get { return _dialect; }
    }

    public string ConnectionString { get { return _connectionString; } }

    /// <summary>
    /// Returns the event logger
    /// </summary>
    public virtual ILogger Logger
    {
        get { return _logger; }
        set { _logger = value; }
    }

    public virtual ITransformationProvider this[string provider]
    {
        get
        {
            if (null != provider && IsThisProvider(provider))
            {
                return this;
            }

            return NoOpTransformationProvider.Instance;
        }
    }

    public virtual Index[] GetIndexes(string table)
    {
        throw new NotImplementedException();
    }

    public virtual Column[] GetColumns(string table)
    {
        var columns = new List<Column>();
        using (var cmd = CreateCommand())
        using (
            var reader =
                ExecuteQuery(
                    cmd, string.Format("select COLUMN_NAME, IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS where table_name = '{0}'", table)))
        {
            while (reader.Read())
            {
                var column = new Column(reader.GetString(0), DbType.String);
                var nullableStr = reader.GetString(1);
                var isNullable = nullableStr == "YES";
                column.ColumnProperty |= isNullable ? ColumnProperty.Null : ColumnProperty.NotNull;

                columns.Add(column);
            }
        }

        return columns.ToArray();
    }

    /// <summary>
    /// Basic implementation works for Postgre and probably for MySQL (not tested). For Oracle it should be overridden
    /// </summary>
    /// <param name="table"></param>
    /// <returns></returns>
    /// <exception cref="MigrationException"></exception>
    public virtual ForeignKeyConstraint[] GetForeignKeyConstraints(string table)
    {
        var constraints = new List<ForeignKeyConstraint>();
        var sb = new StringBuilder();
        sb.AppendLine("SELECT");
        sb.AppendLine("  tc.CONSTRAINT_NAME AS FK_KEY,");
        sb.AppendLine("  tc.TABLE_SCHEMA,");
        sb.AppendLine("  tc.TABLE_NAME AS CHILD_TABLE,");
        sb.AppendLine("  kcu.COLUMN_NAME AS CHILD_COLUMN,");
        sb.AppendLine("  ccu.TABLE_NAME AS PARENT_TABLE,");
        sb.AppendLine("  ccu.COLUMN_NAME AS PARENT_COLUMN");
        sb.AppendLine("FROM ");
        sb.AppendLine("  INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ");
        sb.AppendLine("JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu");
        sb.AppendLine("  ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA");
        sb.AppendLine("JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc");
        sb.AppendLine("  ON tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME AND tc.TABLE_SCHEMA = rc.CONSTRAINT_SCHEMA");
        sb.AppendLine("JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu");
        sb.AppendLine("  ON rc.UNIQUE_CONSTRAINT_NAME = ccu.CONSTRAINT_NAME AND rc.UNIQUE_CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA");
        sb.AppendLine($"WHERE LOWER(tc.TABLE_NAME) = LOWER('{table}') AND tc.CONSTRAINT_TYPE = 'FOREIGN KEY'");
        sb.AppendLine("ORDER BY kcu.ORDINAL_POSITION");

        var sql = sb.ToString();
        List<ForeignKeyConstraintItem> foreignKeyConstraintItems = [];

        using (var cmd = CreateCommand())
        using (var reader = ExecuteQuery(cmd, sql))
        {
            while (reader.Read())
            {
                var constraintItem = new ForeignKeyConstraintItem
                {
                    SchemaName = reader.GetString(reader.GetOrdinal("TABLE_SCHEMA")),
                    ForeignKeyName = reader.GetString(reader.GetOrdinal("FK_KEY")),
                    ChildTableName = reader.GetString(reader.GetOrdinal("CHILD_TABLE")),
                    ChildColumnName = reader.GetString(reader.GetOrdinal("CHILD_COLUMN")),
                    ParentTableName = reader.GetString(reader.GetOrdinal("PARENT_TABLE")),
                    ParentColumnName = reader.GetString(reader.GetOrdinal("PARENT_COLUMN"))
                };

                foreignKeyConstraintItems.Add(constraintItem);
            }
        }

        var schemaChildTableGroups = foreignKeyConstraintItems.GroupBy(x => new { x.SchemaName, x.ChildTableName }).Count();

        if (schemaChildTableGroups > 1)
        {
            throw new MigrationException($"Duplicates found (grouping by schema name and child table name). Since we do not offer schemas in '{nameof(GetForeignKeyConstraints)}' at this moment in time we cannot filter your target schema. Your database use the same table name in different schemas.");
        }

        var groups = foreignKeyConstraintItems.GroupBy(x => x.ForeignKeyName);

        foreach (var group in groups)
        {
            var first = group.First();

            var foreignKeyConstraint = new ForeignKeyConstraint
            {
                Name = first.ForeignKeyName,
                ParentTable = first.ParentTableName,
                ParentColumns = [.. group.Select(x => x.ParentColumnName).Distinct()],
                ChildTable = first.ChildTableName,
                ChildColumns = [.. group.Select(x => x.ChildColumnName).Distinct()]
            };

            constraints.Add(foreignKeyConstraint);
        }

        return [.. constraints];
    }

    public virtual string[] GetConstraints(string table)
    {
        var constraints = new List<string>();
        using (var cmd = CreateCommand())
        using (
            var reader =
                ExecuteQuery(
                    cmd, string.Format("SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE LOWER(TABLE_NAME) = LOWER('{0}')", table)))
        {
            while (reader.Read())
            {
                constraints.Add(reader.GetString(0));
            }
        }

        return constraints.ToArray();
    }

    public virtual Column GetColumnByName(string table, string columnName)
    {
        var columns = GetColumns(table);
        var column = columns.FirstOrDefault(x => x.Name.Equals(columnName, StringComparison.OrdinalIgnoreCase)) ??
            throw new Exception($"Cannot find column '{columnName}' in table '{table}'");

        return column;
    }

    public virtual int GetColumnContentSize(string table, string columnName)
    {
        var result = this.ExecuteScalar("SELECT MAX(LENGTH(" + this.QuoteColumnNameIfRequired(columnName) + ")) FROM " + this.QuoteTableNameIfRequired(table));

        if (result == DBNull.Value)
        {
            return 0;
        }

        return Convert.ToInt32(result);
    }

    public virtual string[] GetTables()
    {
        var tables = new List<string>();
        using (var cmd = CreateCommand())
        using (var reader = ExecuteQuery(cmd, "SELECT table_name FROM INFORMATION_SCHEMA.TABLES"))
        {
            while (reader.Read())
            {
                tables.Add((string)reader[0]);
            }
        }
        return tables.ToArray();
    }

    public virtual void RemoveForeignKey(string table, string name)
    {
        if (!TableExists(table))
        {
            throw new MigrationException($"Table '{table}' does not exist.");
        }

        RemoveConstraint(table, name);
    }

    public virtual void RemoveConstraint(string table, string name)
    {
        if (!TableExists(table))
        {
            throw new MigrationException($"Table '{name}' does not exist");
        }

        if (!ConstraintExists(table, name))
        {
            throw new MigrationException($"Constraint '{name}' does not exist");
        }

        ExecuteNonQuery(string.Format("ALTER TABLE {0} DROP CONSTRAINT {1}", QuoteTableNameIfRequired(table), QuoteConstraintNameIfRequired(name)));
    }

    public virtual void RemoveAllConstraints(string table)
    {
        foreach (var constraint in GetConstraints(table))
        {
            RemoveConstraint(table, constraint);
        }
    }

    public virtual void AddView(string name, string tableName, params IViewField[] fields)
    {
        var lst =
            fields.Where(x => string.IsNullOrEmpty(x.TableName) || x.TableName == tableName)
                .Select(x => x.ColumnName)
                .ToList();

        var nr = 0;
        var joins = "";
        foreach (var joinTable in fields.Where(x => !string.IsNullOrEmpty(x.TableName) && x.TableName != tableName).GroupBy(x => x.TableName))
        {
            foreach (var viewField in joinTable)
            {
                joins += string.Format("JOIN {0} {1} ON {1}.{2} = {3}.{4} ", viewField.TableName, " T" + nr,
                    viewField.KeyColumnName, viewField.ParentTableName, viewField.ParentKeyColumnName);
                lst.Add(" T" + nr + "." + viewField.ColumnName);
            }
        }

        var select = string.Format("SELECT {0} FROM {1} {2}", string.Join(",", lst), tableName, joins);

        var sql = string.Format("CREATE VIEW {0} AS {1}", name, select);

        ExecuteNonQuery(sql);
    }

    public virtual void AddView(string name, string tableName, params IViewElement[] viewElements)
    {
        var selectedColumns = viewElements.Where(x => x is ViewColumn)
            .Select(x =>
            {
                var viewColumn = (ViewColumn)x;
                return $"{viewColumn.Prefix}.{viewColumn.ColumnName} {viewColumn.Prefix}{viewColumn.ColumnName}";
            })
            .ToList();

        var joins = string.Empty;

        foreach (var viewJoin in viewElements.Where(x => x is ViewJoin).Cast<ViewJoin>())
        {
            var joinType = string.Empty;

            switch (viewJoin.JoinType)
            {
                case JoinType.LeftJoin:
                    joinType = "LEFT JOIN";
                    break;
                case JoinType.Join:
                    joinType = "JOIN";
                    break;
            }

            var tableAlias = string.IsNullOrEmpty(viewJoin.TableAlias) ? viewJoin.TableName : viewJoin.TableAlias;

            joins += string.Format("{0} {1} {2} ON {2}.{3} = {4}.{5} ", joinType, viewJoin.TableName, tableAlias,
                viewJoin.ColumnName, viewJoin.ParentTableName, viewJoin.ParentColumnName);
        }

        var select = string.Format("SELECT {0} FROM {1} {1} {2}", string.Join(",", selectedColumns), tableName, joins);
        var sql = string.Format("CREATE VIEW {0} AS {1}", name, select);


        // Works with all DBs. "CREATE OR REPLACE" does not work with SQLite. "DROP IF EXISTS" does not work with oracle.
        try
        {
            ExecuteNonQuery($"DROP VIEW {name}");
        }
        catch
        {
            // Works with all DBs. "CREATE OR REPLACE" does not work with SQLite. "DROP IF EXISTS" does not work with oracle.
        }

        ExecuteNonQuery(sql);
    }

    /// <summary>
    /// Add a new table
    /// </summary>
    /// <param name="name">Table name</param>
    /// <param name="columns">Columns</param>
    public virtual void AddTable(string name, params IDbField[] columns)
    {
        if (this is not SQLiteTransformationProvider && columns.Any(x => x is CheckConstraint))
        {
            throw new MigrationException($"{nameof(CheckConstraint)}s are currently only supported in SQLite.");
        }

        // Most databases don't have the concept of a storage engine, so default is to not use it.
        AddTable(name, null, columns);
    }

    /// <summary>
    /// Adds a new table
    /// </summary>
    /// <param name="name">Table name</param>
    /// <param name="columns">Columns</param>
    /// <param name="engine">the database storage engine to use</param>
    public virtual void AddTable(string name, string engine, params IDbField[] fields)
    {
        var columns = fields.Where(x => x is Column).Cast<Column>().ToArray();

        var pks = GetPrimaryKeys(columns);
        var compoundPrimaryKey = pks.Count > 1;

        var columnProviders = new List<ColumnPropertiesMapper>(columns.Count());

        foreach (var column in columns)
        {
            // Remove the primary key notation if compound primary key because we'll add it back later
            if (compoundPrimaryKey && column.IsPrimaryKey)
            {
                column.ColumnProperty = column.ColumnProperty ^ ColumnProperty.PrimaryKey;
                column.ColumnProperty = column.ColumnProperty | ColumnProperty.NotNull; // PK is always not-null
            }

            var mapper = _dialect.GetAndMapColumnProperties(column);
            columnProviders.Add(mapper);
        }

        var columnsAndIndexes = JoinColumnsAndIndexes(columnProviders);

        AddTable(name, engine, columnsAndIndexes);

        if (compoundPrimaryKey)
        {
            AddPrimaryKey(GetPrimaryKeyname(name), name, pks.ToArray());
        }

        var indexes = fields.Where(x => x is Index).Cast<Index>().ToArray();

        foreach (var index in indexes)
        {
            AddIndex(name, index);
        }

        var foreignKeys = fields.Where(x => x is ForeignKeyConstraint).Cast<ForeignKeyConstraint>().ToArray();

        foreach (var foreignKey in foreignKeys)
        {
            AddForeignKey(name, foreignKey);
        }
    }

    protected virtual string GetPrimaryKeyname(string tableName)
    {
        return "PK_" + tableName;
    }

    public virtual void RemoveTable(string name)
    {
        if (!TableExists(name))
        {
            throw new MigrationException(string.Format("Table with name '{0}' does not exist to rename", name));
        }

        ExecuteNonQuery(string.Format("DROP TABLE {0}", name));
    }

    public virtual void RenameTable(string oldName, string newName)
    {
        oldName = QuoteTableNameIfRequired(oldName);
        newName = QuoteTableNameIfRequired(newName);

        if (TableExists(newName))
        {
            throw new MigrationException(string.Format("Table with name '{0}' already exists", newName));
        }

        if (!TableExists(oldName))
        {
            throw new MigrationException(string.Format("Table with name '{0}' does not exist to rename", oldName));
        }

        ExecuteNonQuery(string.Format("ALTER TABLE {0} RENAME TO {1}", oldName, newName));
    }

    public virtual void RenameColumn(string tableName, string oldColumnName, string newColumnName)
    {
        if (ColumnExists(tableName, newColumnName))
        {
            throw new MigrationException(string.Format("Table '{0}' has column named '{1}' already", tableName, newColumnName));
        }

        if (!ColumnExists(tableName, oldColumnName))
        {
            throw new MigrationException(string.Format("The table '{0}' does not have a column named '{1}'", tableName, oldColumnName));
        }

        var column = GetColumnByName(tableName, oldColumnName);

        var quotedNewColumnName = QuoteColumnNameIfRequired(newColumnName);

        ExecuteNonQuery(string.Format("ALTER TABLE {0} RENAME COLUMN {1} TO {2}", tableName, Dialect.Quote(column.Name), quotedNewColumnName));
    }

    public virtual void RemoveColumn(string tableName, string column)
    {
        if (!TableExists(tableName))
        {
            throw new MigrationException($"The table '{tableName}' does not exist");
        }

        if (!ColumnExists(tableName, column, true))
        {
            throw new MigrationException(string.Format("The table '{0}' does not have a column named '{1}'", tableName, column));
        }

        var existingColumn = GetColumnByName(tableName, column);

        ExecuteNonQuery(string.Format("ALTER TABLE {0} DROP COLUMN {1} ", tableName, Dialect.Quote(existingColumn.Name)));
    }

    public virtual bool ColumnExists(string table, string column)
    {
        return ColumnExists(table, column, true);
    }

    public virtual bool ColumnExists(string table, string column, bool ignoreCase)
    {
        if (ignoreCase)
        {
            return GetColumns(table).Any(x => x.Name.Equals(column, StringComparison.OrdinalIgnoreCase));
        }

        return GetColumns(table).Any(x => x.Name == column);
    }

    public virtual void ChangeColumn(string table, Column column)
    {
        var isUniqueSet = column.ColumnProperty.IsSet(ColumnProperty.Unique);

        column.ColumnProperty = column.ColumnProperty.Clear(ColumnProperty.Unique);

        var mapper = _dialect.GetAndMapColumnProperties(column);

        ChangeColumn(table, mapper.ColumnSql);

        if (isUniqueSet)
        {
            AddUniqueConstraint(string.Format("UX_{0}_{1}", table, column.Name), table, [column.Name]);
        }
    }

    public virtual void RemoveColumnDefaultValue(string table, string column)
    {
        var sql = string.Format("ALTER TABLE {0} ALTER {1} DROP DEFAULT", table, column);
        ExecuteNonQuery(sql);
    }

    public virtual bool TableExists(string table)
    {
        throw new NotImplementedException();
    }

    public virtual bool ViewExists(string view)
    {
        throw new NotImplementedException();
    }

    public virtual void SwitchDatabase(string databaseName)
    {
        _connection.ChangeDatabase(databaseName);
    }

    public abstract List<string> GetDatabases();

    public bool DatabaseExists(string name)
    {
        return GetDatabases().Any(c => string.Equals(name, c, StringComparison.OrdinalIgnoreCase));
    }

    public virtual void CreateDatabases(string databaseName)
    {
        ExecuteNonQuery(string.Format("CREATE DATABASE {0}", databaseName));
    }

    public virtual void KillDatabaseConnections(string databaseName)
    {
        //todo, implement this for each DB, no default implementation possible!!!
    }

    public virtual void DropDatabases(string databaseName)
    {
        ExecuteNonQuery(string.Format("DROP DATABASE {0}", databaseName));
    }

    /// <summary>
    /// Add a new column to an existing table.
    /// </summary>
    /// <param name="table">Table to which to add the column</param>
    /// <param name="column">Column name</param>
    /// <param name="type">Date type of the column</param>
    /// <param name="size">Max length of the column</param>
    /// <param name="property">Properties of the column, see <see cref="ColumnProperty">ColumnProperty</see>,</param>
    /// <param name="defaultValue">Default value</param>
    public void AddColumn(string table, string column, DbType type, int size, ColumnProperty property,
                                  object defaultValue)
    {
        AddColumn(table, column, (MigratorDbType)type, size, property, defaultValue);
    }

    /// <summary>
    /// Add a new column to an existing table.
    /// </summary>
    /// <param name="table">Table to which to add the column</param>
    /// <param name="column">Column name</param>
    /// <param name="type">Date type of the column</param>
    /// <param name="size">Max length of the column</param>
    /// <param name="property">Properties of the column, see <see cref="ColumnProperty">ColumnProperty</see>,</param>
    /// <param name="defaultValue">Default value</param>
    public virtual void AddColumn(string table, string column, MigratorDbType type, int size, ColumnProperty property,
                                  object defaultValue)
    {
        var mapper =
            _dialect.GetAndMapColumnProperties(new Column(column, type, size, property, defaultValue));

        AddColumn(table, mapper.ColumnSql);
    }

    /// <summary>
    /// <see cref="TransformationProvider.AddColumn(string, string, DbType, int, ColumnProperty, object)">
    /// AddColumn(string, string, Type, int, ColumnProperty, object)
    /// </see>
    /// </summary>
    public virtual void AddColumn(string table, string column, DbType type)
    {
        AddColumn(table, column, type, 0, ColumnProperty.Null, null);
    }

    /// <summary>
    /// <see cref="TransformationProvider.AddColumn(string, string, MigratorDbType, int, ColumnProperty, object)">
    /// AddColumn(string, string, Type, int, ColumnProperty, object)
    /// </see>
    /// </summary>
    public virtual void AddColumn(string table, string column, MigratorDbType type)
    {
        AddColumn(table, column, type, 0, ColumnProperty.Null, null);
    }

    /// <summary>
    /// <see cref="TransformationProvider.AddColumn(string, string, DbType, int, ColumnProperty, object)">
    /// AddColumn(string, string, Type, int, ColumnProperty, object)
    /// </see>
    /// </summary>
    public virtual void AddColumn(string table, string column, DbType type, int size)
    {
        AddColumn(table, column, type, size, ColumnProperty.Null, null);
    }

    /// <summary>
    /// <see cref="TransformationProvider.AddColumn(string, string, MigratorDbType, int, ColumnProperty, object)">
    /// AddColumn(string, string, Type, int, ColumnProperty, object)
    /// </see>
    /// </summary>
    public virtual void AddColumn(string table, string column, MigratorDbType type, int size)
    {
        AddColumn(table, column, type, size, ColumnProperty.Null, null);
    }

    public virtual void AddColumn(string table, string column, DbType type, object defaultValue)
    {
        AddColumn(table, column, (MigratorDbType)type, defaultValue);
    }

    public virtual void AddColumn(string table, string column, MigratorDbType type, object defaultValue)
    {
        var mapper =
            _dialect.GetAndMapColumnProperties(new Column(column, type, defaultValue));

        AddColumn(table, mapper.ColumnSql);
    }

    /// <summary>
    /// <see cref="TransformationProvider.AddColumn(string, string, DbType, int, ColumnProperty, object)">
    /// AddColumn(string, string, Type, int, ColumnProperty, object)
    /// </see>
    /// </summary>
    public virtual void AddColumn(string table, string column, DbType type, ColumnProperty property)
    {
        AddColumn(table, column, type, 0, property, null);
    }

    /// <summary>
    /// <see cref="TransformationProvider.AddColumn(string, string, MigratorDbType, int, ColumnProperty, object)">
    /// AddColumn(string, string, Type, int, ColumnProperty, object)
    /// </see>
    /// </summary>
    public virtual void AddColumn(string table, string column, MigratorDbType type, ColumnProperty property)
    {
        AddColumn(table, column, type, 0, property, null);
    }

    /// <summary>
    /// <see cref="AddColumn(string, string, DbType, int, ColumnProperty, object)">
    /// AddColumn(string, string, Type, int, ColumnProperty, object)
    /// </see>
    /// </summary>
    public virtual void AddColumn(string table, string column, DbType type, int size, ColumnProperty property)
    {
        AddColumn(table, column, type, size, property, null);
    }

    /// <summary>
    /// <see cref="AddColumn(string, string, MigratorDbType, int, ColumnProperty, object)">
    /// AddColumn(string, string, Type, int, ColumnProperty, object)
    /// </see>
    /// </summary>
    public virtual void AddColumn(string table, string column, MigratorDbType type, int size, ColumnProperty property)
    {
        AddColumn(table, column, type, size, property, null);
    }

    /// <summary>
    /// Append a primary key to a table.
    /// </summary>
    /// <param name="name">Constraint name</param>
    /// <param name="table">Table name</param>
    /// <param name="columns">Primary column names</param>
    public virtual void AddPrimaryKey(string name, string table, params string[] columns)
    {
        QuoteColumnNamesIfRequired(columns);
        table = QuoteTableNameIfRequired(table);

        ExecuteNonQuery(
            string.Format("ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY ({2}) ", table, name,
                          string.Join(",", QuoteColumnNamesIfRequired(columns))));
    }
    public virtual void AddPrimaryKeyNonClustered(string name, string table, params string[] columns)
    {
        this.AddPrimaryKey(name, table, columns);
    }
    public virtual void AddUniqueConstraint(string name, string table, params string[] columns)
    {
        QuoteColumnNamesIfRequired(columns);
        table = QuoteTableNameIfRequired(table);

        ExecuteNonQuery(string.Format("ALTER TABLE {0} ADD CONSTRAINT {1} UNIQUE({2}) ", table, name, string.Join(", ", columns)));
    }

    public virtual void AddCheckConstraint(string name, string table, string checkSql)
    {
        table = QuoteTableNameIfRequired(table);

        ExecuteNonQuery(string.Format("ALTER TABLE {0} ADD CONSTRAINT {1} CHECK ({2}) ", table, name, checkSql));
    }

    /// <summary>
    /// Guesses the name of the foreign key and adds it
    /// </summary>
    public virtual void GenerateForeignKey(string childTable, string childColumn, string parentTable, string parentColumn)
    {
        AddForeignKey("FK_" + childTable + "_" + parentTable, childTable, childColumn, parentTable, parentColumn);
    }

    /// <summary>
    /// Guesses the name of the foreign key and adds it
    /// </see>
    /// </summary>
    public virtual void GenerateForeignKey(
        string childTable,
        string[] childColumns,
        string parentTable,
        string[] parentColumns)
    {
        AddForeignKey("FK_" + childTable + "_" + parentTable, childTable, childColumns, parentTable, parentColumns);
    }

    /// <summary>
    /// Guesses the name of the foreign key and adds it
    /// </summary>
    public virtual void GenerateForeignKey(
        string childTable,
        string childColumn,
        string parentTable,
        string parentColumn,
        ForeignKeyConstraintType constraint)
    {
        AddForeignKey("FK_" + childTable + "_" + parentTable, childTable, childColumn, parentTable, parentColumn, constraint);
    }

    /// <summary>
    /// Guesses the name of the foreign key and add it
    /// </see>
    /// </summary>
    public virtual void GenerateForeignKey(
        string childTable,
        string[] childColumns,
        string parentTable,
        string[] parentColumns,
        ForeignKeyConstraintType constraint)
    {
        AddForeignKey("FK_" + childTable + "_" + parentTable, childTable, childColumns, parentTable, parentColumns, constraint);
    }

    public virtual void AddForeignKey(string table, ForeignKeyConstraint fk)
    {
        AddForeignKey(fk.Name, table, fk.ParentColumns, fk.ChildTable, fk.ChildColumns);
    }

    public virtual void AddForeignKey(string name, string childTable, string childColumn, string parentTable, string parentColumn)
    {
        try
        {
            AddForeignKey(name, childTable, [childColumn], parentTable, [parentColumn]);
        }
        catch (Exception ex)
        {
            throw new Exception(string.Format("Error occured while adding foreign key: \"{0}\" between table: \"{1}\" and table: \"{2}\" - see inner exception for details", name, parentTable, childTable), ex);
        }
    }

    public virtual void AddForeignKey(string name, string childTable, string[] childColumns, string parentTable, string[] parentColumns)
    {
        AddForeignKey(name, childTable, childColumns, parentTable, parentColumns, ForeignKeyConstraintType.NoAction);
    }

    public virtual void AddForeignKey(string name, string childTable, string childColumn, string parentTable, string parentColumn, ForeignKeyConstraintType constraint)
    {
        AddForeignKey(name, childTable, [childColumn], parentTable, [parentColumn], constraint);
    }

    public virtual void AddForeignKey(
        string name,
        string childTable,
        string[] childColumns,
        string parentTable,
        string[] parentColumns,
        ForeignKeyConstraintType constraint)
    {
        childTable = QuoteTableNameIfRequired(childTable);
        parentTable = QuoteTableNameIfRequired(parentTable);
        QuoteColumnNames(parentColumns);
        QuoteColumnNames(childColumns);

        var constraintResolved = constraintMapper.SqlForConstraint(constraint);

        // TODO Issue #52 still unresolved
        var childColumnsString = string.Join(", ", childColumns);
        var parentColumnsString = string.Join(", ", parentColumns);

        var stringBuilder = new StringBuilder();
        stringBuilder.Append($"ALTER TABLE {childTable} ADD CONSTRAINT {name} FOREIGN KEY ({childColumnsString}) REFERENCES {parentTable} ({parentColumnsString})");
        stringBuilder.Append($"ON UPDATE {constraintResolved} ON DELETE {constraintResolved}");

        ExecuteNonQuery(stringBuilder.ToString());
    }

    /// <summary>
    /// Determines if a constraint exists.
    /// </summary>
    /// <param name="name">Constraint name</param>
    /// <param name="table">Table owning the constraint</param>
    /// <returns><c>true</c> if the constraint exists.</returns>
    public abstract bool ConstraintExists(string table, string name);

    public virtual bool PrimaryKeyExists(string table, string name)
    {
        return ConstraintExists(table, name);
    }

    public virtual int ExecuteNonQuery(string sql)
    {
        return ExecuteNonQuery(sql, CommandTimeout ?? 30);
    }

    public virtual int ExecuteNonQuery(string sql, int timeout)
    {
        return ExecuteNonQuery(sql, timeout, null);
    }

    public virtual int ExecuteNonQuery(string sql, int timeout, params object[] args)
    {
        if (args == null)
        {
            Logger.Trace(sql);
            Logger.ApplyingDBChange(sql);
        }
        else
        {
            Logger.Trace(string.Format(sql, args));
            Logger.ApplyingDBChange(string.Format(sql, args));
        }

        using var cmd = BuildCommand(sql);

        try
        {
            cmd.CommandTimeout = timeout;

            if (args != null)
            {
                var index = 0;

                foreach (var obj in args)
                {
                    var parameter = cmd.CreateParameter();
                    ConfigureParameterWithValue(parameter, index, obj);
                    parameter.ParameterName = GenerateParameterNameParameter(index);
                    cmd.Parameters.Add(parameter);
                    ++index;
                }
            }

            Logger.Trace(cmd.CommandText);
            return cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Logger.Warn(ex.Message);
            throw new MigrationException(string.Format("Error occured executing sql: {0}, see inner exception for details, error: " + ex, sql), ex);
        }
    }

    public List<string> ExecuteStringQuery(string sql, params object[] args)
    {
        var values = new List<string>();

        using (var cmd = CreateCommand())
        {
            using var reader = ExecuteQuery(cmd, string.Format(sql, args));
            while (reader.Read())
            {
                var value = reader[0];

                if (value == null || value == DBNull.Value)
                {
                    values.Add(null);
                }
                else
                {
                    values.Add(value.ToString());
                }
            }
        }

        return values;
    }

    public virtual void ExecuteScript(string fileName)
    {
        if (CurrentMigration != null)
        {
#if NETSTANDARD
            var assembly = CurrentMigration.GetType().GetTypeInfo().Assembly;
#else
            var assembly = CurrentMigration.GetType().Assembly;
#endif

            string sqlText;
            var file = (new System.Uri(assembly.CodeBase)).AbsolutePath;
            using (var reader = File.OpenText(file))
            {
                sqlText = reader.ReadToEnd();
            }

            ExecuteNonQuery(sqlText);
        }
    }

    /// <summary>
    /// Execute an SQL query returning results.
    /// </summary>
    /// <param name="sql">The SQL text.</param>
    /// <param name="cmd">The IDbCommand.</param>
    /// <returns>A data iterator, <see cref="System.Data.IDataReader">IDataReader</see>.</returns>
    public virtual IDataReader ExecuteQuery(IDbCommand cmd, string sql)
    {
        Logger.Trace(sql);
        cmd.CommandText = sql;
        try
        {
            return cmd.ExecuteReader();
        }
        catch (Exception ex)
        {
            Logger.Warn("query failed: {0}", cmd.CommandText);
            throw new Exception("Failed to execute sql statement: " + sql, ex);
        }
    }

    public virtual object ExecuteScalar(string sql)
    {
        Logger.Trace(sql);
        using var cmd = BuildCommand(sql);
        try
        {
            return cmd.ExecuteScalar();
        }
        catch
        {
            Logger.Warn("Query failed: {0}", cmd.CommandText);
            throw;
        }
    }

    public virtual IDataReader Select(IDbCommand cmd, string what, string from)
    {
        return Select(cmd, what, from, "1=1");
    }

    public virtual IDataReader Select(IDbCommand cmd, string what, string from, string where)
    {
        return ExecuteQuery(cmd, string.Format("SELECT {0} FROM {1} WHERE {2}", what, from, where));
    }

    public virtual IDataReader Select(IDbCommand cmd, string table, string[] columns, string[] whereColumns = null, object[] whereValues = null)
    {
        return SelectComplex(cmd, table, columns, whereColumns, whereValues);
    }

    public virtual IDataReader SelectComplex(IDbCommand cmd, string table, string[] columns, string[] whereColumns = null,
        object[] whereValues = null, string[] nullWhereColumns = null, string[] notNullWhereColumns = null)
    {
        if (string.IsNullOrEmpty(table))
        {
            throw new ArgumentNullException("table");
        }

        if (columns == null)
        {
            throw new ArgumentNullException("columns");
        }

        table = QuoteTableNameIfRequired(table);

        var builder = new StringBuilder();
        for (var i = 0; i < columns.Length; i++)
        {
            if (builder.Length > 0)
            {
                builder.Append(", ");
            }

            builder.Append(QuoteColumnNameIfRequired(columns[i]));
        }


        cmd.Transaction = _transaction;

        var query = string.Format("SELECT {0} FROM {1}", builder.ToString(), table);

        if (whereColumns != null || nullWhereColumns != null || notNullWhereColumns != null)
        {
            query = string.Format("SELECT {0} FROM {1} WHERE ", builder.ToString(), table);
        }

        var andNeeded = false;
        if (whereColumns != null)
        {
            query += GetWhereString(whereColumns, whereValues);
            andNeeded = true;
        }
        if (nullWhereColumns != null)
        {
            if (andNeeded)
            {
                query += " AND ";
            }

            query += GetWhereStringIsNull(nullWhereColumns);
            andNeeded = true;
        }
        if (notNullWhereColumns != null)
        {
            if (andNeeded)
            {
                query += " AND ";
            }

            query += GetWhereStringIsNotNull(notNullWhereColumns);
            andNeeded = true;
        }

        cmd.CommandText = query;
        cmd.CommandType = CommandType.Text;

        var paramCount = 0;

        if (whereColumns != null)
        {
            foreach (var value in whereValues)
            {
                var parameter = cmd.CreateParameter();

                ConfigureParameterWithValue(parameter, paramCount, value);

                parameter.ParameterName = GenerateParameterNameParameter(paramCount);

                cmd.Parameters.Add(parameter);

                paramCount++;
            }
        }

        Logger.Trace(cmd.CommandText);
        return cmd.ExecuteReader();

    }

    public object SelectScalar(string what, string from)
    {
        return SelectScalar(what, from, "1=1");
    }

    public virtual object SelectScalar(string what, string from, string where)
    {
        return ExecuteScalar(string.Format("SELECT {0} FROM {1} WHERE {2}", what, from, where));
    }

    public virtual object SelectScalar(string what, string from, string[] whereColumns, object[] whereValues)
    {
        using var command = _connection.CreateCommand();
        if (CommandTimeout.HasValue)
        {
            command.CommandTimeout = CommandTimeout.Value;
        }

        command.Transaction = _transaction;

        var query = string.Format("SELECT {0} FROM {1} WHERE {2}", what, from, GetWhereString(whereColumns, whereValues));

        command.CommandText = query;
        command.CommandType = CommandType.Text;

        var paramCount = 0;

        foreach (var value in whereValues)
        {
            var parameter = command.CreateParameter();

            ConfigureParameterWithValue(parameter, paramCount, value);

            parameter.ParameterName = GenerateParameterNameParameter(paramCount);

            command.Parameters.Add(parameter);

            paramCount++;
        }

        Logger.Trace(command.CommandText);
        return command.ExecuteScalar();
    }

    public virtual int Update(string table, string[] columns, object[] values)
    {
        return Update(table, columns, values, null);
    }

    public virtual int Update(string table, string[] columns, object[] values, string where)
    {
        if (string.IsNullOrEmpty(table))
        {
            throw new ArgumentNullException("table");
        }

        if (columns == null)
        {
            throw new ArgumentNullException("columns");
        }

        if (values == null)
        {
            throw new ArgumentNullException("values");
        }

        if (columns.Length != values.Length)
        {
            throw new Exception(string.Format("The number of columns: {0} does not match the number of supplied values: {1}", columns.Length, values.Length));
        }

        table = QuoteTableNameIfRequired(table);

        var builder = new StringBuilder();
        for (var i = 0; i < values.Length; i++)
        {
            if (builder.Length > 0)
            {
                builder.Append(", ");
            }

            builder.Append(QuoteColumnNameIfRequired(columns[i]));
            builder.Append(" = ");
            builder.Append(GenerateParameterName(i));
        }

        using var command = _connection.CreateCommand();
        if (CommandTimeout.HasValue)
        {
            command.CommandTimeout = CommandTimeout.Value;
        }

        command.Transaction = _transaction;

        var query = string.Format("UPDATE {0} SET {1}", table, builder.ToString());
        if (!string.IsNullOrEmpty(where))
        {
            query += " WHERE " + where;
        }
        command.CommandText = query;
        command.CommandType = CommandType.Text;

        var paramCount = 0;

        foreach (var value in values)
        {
            var parameter = command.CreateParameter();

            ConfigureParameterWithValue(parameter, paramCount, value);

            parameter.ParameterName = GenerateParameterNameParameter(paramCount);

            command.Parameters.Add(parameter);

            paramCount++;
        }

        Logger.Trace(command.CommandText);
        return command.ExecuteNonQuery();
    }

    public virtual int Update(string table, string[] columns, object[] values, string[] whereColumns, object[] whereValues)
    {
        if (string.IsNullOrEmpty(table))
        {
            throw new ArgumentNullException("table");
        }

        if (columns == null)
        {
            throw new ArgumentNullException("columns");
        }

        if (values == null)
        {
            throw new ArgumentNullException("values");
        }

        if (columns.Length != values.Length)
        {
            throw new Exception(string.Format("The number of columns: {0} does not match the number of supplied values: {1}", columns.Length, values.Length));
        }

        if (whereColumns.Length != whereValues.Length)
        {
            throw new Exception(string.Format("The number of whereColumns: {0} does not match the number of supplied whereValues: {1}", whereColumns.Length, whereValues.Length));
        }

        table = QuoteTableNameIfRequired(table);

        var builder = new StringBuilder();

        for (var i = 0; i < values.Length; i++)
        {
            if (builder.Length > 0)
            {
                builder.Append(", ");
            }

            builder.Append(QuoteColumnNameIfRequired(columns[i]));
            builder.Append(" = ");
            builder.Append(GenerateParameterName(i));
        }

        using var command = _connection.CreateCommand();
        if (CommandTimeout.HasValue)
        {
            command.CommandTimeout = CommandTimeout.Value;
        }

        command.Transaction = _transaction;

        var query = string.Format("UPDATE {0} SET {1} WHERE {2}", table, builder.ToString(), GetWhereStringWithNullCheck(whereColumns, whereValues, values.Length));

        command.CommandText = query;
        command.CommandType = CommandType.Text;

        var paramCount = 0;

        foreach (var value in values)
        {
            var parameter = command.CreateParameter();

            ConfigureParameterWithValue(parameter, paramCount, value);

            parameter.ParameterName = GenerateParameterNameParameter(paramCount);

            command.Parameters.Add(parameter);

            paramCount++;
        }

        foreach (var value in whereValues)
        {
            if (value == null || value == DBNull.Value)
            {
                continue;
            }

            var parameter = command.CreateParameter();

            ConfigureParameterWithValue(parameter, paramCount, value);

            parameter.ParameterName = GenerateParameterNameParameter(paramCount);

            command.Parameters.Add(parameter);

            paramCount++;
        }


        Logger.Trace(command.CommandText);
        return command.ExecuteNonQuery();
    }

    public virtual void UpdateTargetFromSource(string tableSourceNotQuoted, string tableTargetNotQuoted, ColumnPair[] fromSourceToTargetColumnPairs, ColumnPair[] conditionColumnPairs)
    {
        throw new NotImplementedException();
    }

    public virtual int Insert(string table, string[] columns, object[] values)
    {
        if (string.IsNullOrEmpty(table))
        {
            throw new ArgumentNullException("table");
        }

        if (columns == null)
        {
            throw new ArgumentNullException("columns");
        }

        if (values == null)
        {
            throw new ArgumentNullException("values");
        }

        if (columns.Length != values.Length)
        {
            throw new MigrationException(string.Format("The number of columns: {0} does not match the number of supplied values: {1}", columns.Length, values.Length));
        }

        table = QuoteTableNameIfRequired(table);

        var columnNames = string.Join(", ", columns.Select(col => QuoteColumnNameIfRequired(col)).ToArray());

        var builder = new StringBuilder();

        for (var i = 0; i < values.Length; i++)
        {
            if (builder.Length > 0)
            {
                builder.Append(", ");
            }

            builder.Append(GenerateParameterName(i));
        }

        var parameterNames = builder.ToString();

        using var command = _connection.CreateCommand();
        if (CommandTimeout.HasValue)
        {
            command.CommandTimeout = CommandTimeout.Value;
        }

        command.Transaction = _transaction;

        command.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", table, columnNames, parameterNames);
        command.CommandType = CommandType.Text;

        var paramCount = 0;

        foreach (var value in values)
        {
            var parameter = command.CreateParameter();

            ConfigureParameterWithValue(parameter, paramCount, value);

            parameter.ParameterName = GenerateParameterNameParameter(paramCount);

            command.Parameters.Add(parameter);

            paramCount++;
        }

        return command.ExecuteNonQuery();
    }

    protected virtual string GetWhereStringWithNullCheck(string[] whereColumns, object[] whereValues, int parameterStartIndex = 0)
    {
        var builder2 = new StringBuilder();
        var parCnt = 0;
        for (var i = 0; i < whereColumns.Length; i++)
        {
            if (builder2.Length > 0)
            {
                builder2.Append(" AND ");
            }

            var val = whereValues[i];
            if (val == null || val == DBNull.Value)
            {
                builder2.Append(QuoteColumnNameIfRequired(whereColumns[i]));
                builder2.Append(" is null ");
            }
            else
            {
                builder2.Append(QuoteColumnNameIfRequired(whereColumns[i]));
                builder2.Append(" = ");
                builder2.Append(GenerateParameterName(parCnt + parameterStartIndex));
                parCnt++;
            }
        }

        return builder2.ToString();
    }

    protected virtual string GetWhereString(string[] whereColumns, object[] whereValues, int parameterStartIndex = 0)
    {
        var builder2 = new StringBuilder();
        for (var i = 0; i < whereColumns.Length; i++)
        {
            if (builder2.Length > 0)
            {
                builder2.Append(" AND ");
            }

            builder2.Append(QuoteColumnNameIfRequired(whereColumns[i]));
            builder2.Append(" = ");
            builder2.Append(GenerateParameterName(i + parameterStartIndex));
        }

        return builder2.ToString();
    }

    protected virtual string GetWhereStringIsNull(string[] whereColumns)
    {
        var builder2 = new StringBuilder();
        for (var i = 0; i < whereColumns.Length; i++)
        {
            if (builder2.Length > 0)
            {
                builder2.Append(" AND ");
            }

            builder2.Append(QuoteColumnNameIfRequired(whereColumns[i]));
            builder2.Append(" IS NULL");
        }

        return builder2.ToString();
    }

    protected virtual string GetWhereStringIsNotNull(string[] whereColumns)
    {
        var builder2 = new StringBuilder();
        for (var i = 0; i < whereColumns.Length; i++)
        {
            if (builder2.Length > 0)
            {
                builder2.Append(" AND ");
            }

            builder2.Append(QuoteColumnNameIfRequired(whereColumns[i]));
            builder2.Append(" IS NOT NULL");
        }

        return builder2.ToString();
    }

    public virtual int InsertIfNotExists(string table, string[] columns, object[] values, string[] whereColumns, object[] whereValues)
    {
        using var cmd = CreateCommand();
        using var reader = this.Select(cmd, table, [whereColumns[0]], whereColumns, whereValues);
        if (!reader.Read())
        {
            reader.Close();
            return this.Insert(table, columns, values);
        }
        else
        {
            reader.Close();
            return 0;
        }
    }

    public virtual int Delete(string table, string[] whereColumns = null, object[] whereValues = null)
    {
        if (string.IsNullOrEmpty(table))
        {
            throw new ArgumentNullException("table");
        }

        if (null == whereColumns || null == whereValues)
        {
            return ExecuteNonQuery(string.Format("DELETE FROM {0}", table));
        }
        else
        {
            table = QuoteTableNameIfRequired(table);

            using var command = _connection.CreateCommand();
            if (CommandTimeout.HasValue)
            {
                command.CommandTimeout = CommandTimeout.Value;
            }

            command.Transaction = _transaction;

            var query = string.Format("DELETE FROM {0} WHERE ({1})", table,
                GetWhereString(whereColumns, whereValues));

            command.CommandText = query;
            command.CommandType = CommandType.Text;

            var paramCount = 0;

            foreach (var value in whereValues)
            {
                var parameter = command.CreateParameter();

                ConfigureParameterWithValue(parameter, paramCount, value);

                parameter.ParameterName = GenerateParameterNameParameter(paramCount);

                command.Parameters.Add(parameter);

                paramCount++;
            }

            Logger.Trace(command.CommandText);
            return command.ExecuteNonQuery();
        }
    }

    public virtual int Delete(string table, string wherecolumn, string wherevalue)
    {
        if (string.IsNullOrEmpty(wherecolumn) && string.IsNullOrEmpty(wherevalue))
        {
            return Delete(table, (string[])null, null);
        }

        return ExecuteNonQuery(string.Format("DELETE FROM {0} WHERE {1} = {2}", table, wherecolumn, QuoteValues(wherevalue)));
    }

    public virtual int TruncateTable(string table)
    {
        return ExecuteNonQuery(string.Format("TRUNCATE TABLE {0} ", table));
    }

    /// <summary>
    /// Starts a transaction. Called by the migration mediator.
    /// </summary>
    public virtual void BeginTransaction()
    {
        if (_transaction == null && _connection != null)
        {
            EnsureHasConnection();
            _transaction = _connection.BeginTransaction(IsolationLevel.ReadCommitted);
        }
    }

    /// <summary>
    /// Rollback the current migration. Called by the migration mediator.
    /// </summary>
    public virtual void Rollback()
    {
        if (_transaction != null && _connection != null && _connection.State == ConnectionState.Open)
        {
            try
            {
                _transaction.Rollback();
            }
            finally
            {
                if (!_outsideConnection)
                {
                    _connection.Close();
                }
            }
        }
        _transaction = null;
    }

    /// <summary>
    /// Commit the current transaction. Called by the migrations mediator.
    /// </summary>
    public virtual void Commit()
    {
        if (_transaction != null && _connection != null && _connection.State == ConnectionState.Open)
        {
            try
            {
                _transaction.Commit();
            }
            finally
            {
                if (!_outsideConnection)
                {
                    _connection.Close();
                }
            }
        }
        _transaction = null;
    }

    /// <summary>
    /// The list of Migrations currently applied to the database.
    /// </summary>
    public virtual List<long> AppliedMigrations
    {
        get
        {
            if (_appliedMigrations == null)
            {
                _appliedMigrations = new List<long>();
                CreateSchemaInfoTable();

                var versionColumn = "Version";
                var scopeColumn = "Scope";

                versionColumn = QuoteColumnNameIfRequired(versionColumn);
                scopeColumn = QuoteColumnNameIfRequired(scopeColumn);

                using var cmd = CreateCommand();
                using var reader = Select(cmd, versionColumn, _schemaInfotable, string.Format("{0} = '{1}'", scopeColumn, _scope));
                while (reader.Read())
                {
                    if (reader.GetFieldType(0) == typeof(decimal))
                    {
                        _appliedMigrations.Add((long)reader.GetDecimal(0));
                    }
                    else
                    {
                        _appliedMigrations.Add(reader.GetInt64(0));
                    }
                }
            }
            return _appliedMigrations;
        }
    }

    public virtual bool IsMigrationApplied(long version, string scope)
    {
        var value = SelectScalar("Version", _schemaInfotable, ["Scope", "Version"], [scope, version]);
        return Convert.ToInt64(value) == version;
    }

    /// <summary>
    /// Marks a Migration version number as having been applied
    /// </summary>
    /// <param name="version">The version number of the migration that was applied</param>
    public virtual void MigrationApplied(long version, string scope)
    {
        CreateSchemaInfoTable();
        Insert(_schemaInfotable, ["Scope", "Version", "TimeStamp"], [scope ?? _scope, version, DateTime.UtcNow]);
        _appliedMigrations.Add(version);
    }

    /// <summary>
    /// Marks a Migration version number as having been rolled back from the database
    /// </summary>
    /// <param name="version">The version number of the migration that was removed</param>
    public virtual void MigrationUnApplied(long version, string scope)
    {
        CreateSchemaInfoTable();
        Delete(_schemaInfotable, ["Scope", "Version"], [scope ?? _scope, version.ToString()]);
        _appliedMigrations.Remove(version);
    }

    public virtual void AddColumn(string table, Column column)
    {
        AddColumn(table, column.Name, column.Type, column.Size, column.ColumnProperty, column.DefaultValue);
    }

    public virtual void GenerateForeignKey(string primaryTable, string refTable)
    {
        GenerateForeignKey(primaryTable, refTable, ForeignKeyConstraintType.NoAction);
    }

    public virtual void GenerateForeignKey(string primaryTable, string refTable, ForeignKeyConstraintType constraint)
    {
        GenerateForeignKey(primaryTable, refTable + "Id", refTable, "Id", constraint);
    }

    public virtual IDbCommand GetCommand()
    {
        return BuildCommand(null);
    }

    public virtual void ExecuteSchemaBuilder(SchemaBuilder builder)
    {
        foreach (var expr in builder.Expressions)
        {
            expr.Create(this);
        }
    }

    public void Dispose()
    {
        if (_connection != null && _connection.State == ConnectionState.Open)
        {
            if (!_outsideConnection)
            {
                _connection.Close();
            }
        }

        if (_connection != null)
        {
            if (!_outsideConnection)
            {
                _connection.Close();
            }
        }

        _connection = null;
    }

    public virtual string QuoteColumnNameIfRequired(string name)
    {
        if (Dialect.ColumnNameNeedsQuote || Dialect.IsReservedWord(name))
        {
            return Dialect.Quote(name);
        }

        return name;
    }

    public virtual string QuoteTableNameIfRequired(string name)
    {
        if (Dialect.TableNameNeedsQuote || Dialect.IsReservedWord(name))
        {
            return Dialect.Quote(name);
        }

        return name;
    }

    public virtual string Encode(Guid guid)
    {
        return guid.ToString();
    }

    public virtual string[] QuoteColumnNamesIfRequired(params string[] columnNames)
    {
        var quotedColumns = new string[columnNames.Length];

        for (var i = 0; i < columnNames.Length; i++)
        {
            quotedColumns[i] = QuoteColumnNameIfRequired(columnNames[i]);
        }

        return quotedColumns;
    }

    public virtual bool IsThisProvider(string provider)
    {
        // XXX: This might need to be more sophisticated. Currently just a convention
        return GetType().Name.ToLower().StartsWith(provider.ToLower());
    }

    public virtual void RemoveAllForeignKeys(string tableName, string columnName)
    { }

    public virtual void AddTable(string table, string engine, string columns)
    {
        table = _dialect.TableNameNeedsQuote ? _dialect.Quote(table) : table;
        var sqlCreate = string.Format("CREATE TABLE {0} ({1})", table, columns);

        ExecuteNonQuery(sqlCreate);
    }

    public virtual List<string> GetPrimaryKeys(IEnumerable<Column> columns)
    {
        var primaryKeys = new List<string>();

        foreach (var col in columns)
        {
            if (col.IsPrimaryKey)
            {
                primaryKeys.Add(col.Name);
            }
        }

        return primaryKeys;
    }

    public virtual void AddColumnDefaultValue(string table, string column, object defaultValue)
    {
        if (defaultValue is DateTime defaultValueDateTime)
        {
            if (defaultValueDateTime.Kind != DateTimeKind.Utc)
            {
                throw new Exception("Only UTC values are accepted as default DateTime values.");
            }
        }

        table = QuoteTableNameIfRequired(table);
        column = QuoteColumnNameIfRequired(column);
        var def = Dialect.Default(defaultValue);
        ExecuteNonQuery(string.Format("ALTER TABLE {0} ADD DEFAULT('{1}') FOR {2}", table, def, column));
    }

    public virtual void AddColumn(string table, string sqlColumn)
    {
        table = QuoteTableNameIfRequired(table);
        ExecuteNonQuery(string.Format("ALTER TABLE {0} ADD COLUMN {1}", table, sqlColumn));
    }

    public virtual void ChangeColumn(string table, string sqlColumn)
    {
        table = QuoteTableNameIfRequired(table);
        ExecuteNonQuery(string.Format("ALTER TABLE {0} ALTER COLUMN {1}", table, sqlColumn));
    }

    protected virtual string JoinColumnsAndIndexes(IEnumerable<ColumnPropertiesMapper> columns)
    {
        var indexes = JoinIndexes(columns);
        var columnsAndIndexes = JoinColumns(columns) + (indexes != null ? "," + indexes : string.Empty);
        return columnsAndIndexes;
    }

    protected virtual string JoinIndexes(IEnumerable<ColumnPropertiesMapper> columns)
    {
        var indexes = new List<string>();
        foreach (var column in columns)
        {
            var indexSql = column.IndexSql;

            if (indexSql != null)
            {
                indexes.Add(indexSql);
            }
        }

        if (indexes.Count == 0)
        {
            return null;
        }

        return string.Join(", ", [.. indexes]);
    }

    protected virtual string JoinColumns(IEnumerable<ColumnPropertiesMapper> columns)
    {
        var columnStrings = new List<string>();

        foreach (var column in columns)
        {
            columnStrings.Add(column.ColumnSql);
        }

        return string.Join(", ", columnStrings.ToArray());
    }

    public IDbCommand CreateCommand()
    {
        EnsureHasConnection();
        var cmd = _connection.CreateCommand();

        if (CommandTimeout.HasValue)
        {
            cmd.CommandTimeout = CommandTimeout.Value;
        }

        cmd.CommandType = CommandType.Text;

        if (_transaction != null)
        {
            cmd.Transaction = _transaction;
        }

        if (CommandTimeout.HasValue)
        {
            cmd.CommandTimeout = CommandTimeout.Value;
        }
        return cmd;
    }

    protected IDbCommand BuildCommand(string sql)
    {
        var cmd = CreateCommand();
        cmd.CommandText = sql;
        return cmd;
    }

    public virtual int Delete(string table)
    {
        return Delete(table, null, (string[])null);
    }

    protected void EnsureHasConnection()
    {
        if (_connection.State != ConnectionState.Open)
        {
            _connection.Open();
        }
    }

    protected virtual void CreateSchemaInfoTable()
    {
        EnsureHasConnection();
        if (!TableExists(_schemaInfotable))
        {
            AddTable(_schemaInfotable,
                new Column("Version", DbType.Int64, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
                new Column("Scope", DbType.String, 50, ColumnProperty.NotNull | ColumnProperty.PrimaryKey, "default"),
                new Column("TimeStamp", DbType.DateTime));
        }
        else
        {
            if (!ColumnExists(_schemaInfotable, "Scope"))
            {
                AddColumn(_schemaInfotable, "Scope", DbType.String, 50, ColumnProperty.NotNull, "default");
                RemoveAllConstraints(_schemaInfotable);
                AddPrimaryKey("PK_SchemaInfo", _schemaInfotable, ["Version", "Scope"]);
            }

            if (!ColumnExists(_schemaInfotable, "TimeStamp"))
            {
                AddColumn(_schemaInfotable, "TimeStamp", DbType.DateTime);
            }
        }
    }

    public virtual string QuoteValues(string values)
    {
        return QuoteValues([values])[0];
    }

    public virtual string[] QuoteValues(string[] values)
    {
        return values.Select(val =>
        {
            if (null == val)
            {
                return "null";
            }
            else
            {
                return string.Format("'{0}'", val.Replace("'", "''"));
            }
        }).ToArray();
    }

    public virtual string JoinColumnsAndValues(string[] columns, string[] values)
    {
        return JoinColumnsAndValues(columns, values, ", ");
    }

    public virtual string JoinColumnsAndValues(string[] columns, string[] values, string joinSeperator)
    {
        var quotedValues = QuoteValues(values);
        var namesAndValues = new string[columns.Length];
        for (var i = 0; i < columns.Length; i++)
        {
            namesAndValues[i] = string.Format("{0}={1}", columns[i], quotedValues[i]);
        }

        return string.Join(joinSeperator, namesAndValues);
    }

    public virtual string GenerateParameterNameParameter(int index)
    {
        return "@p" + index;
    }

    public virtual string GenerateParameterName(int index)
    {
        return GenerateParameterNameParameter(index);
    }

    protected virtual void ConfigureParameterWithValue(IDbDataParameter parameter, int index, object value)
    {
        if (value == null || value == DBNull.Value)
        {
            parameter.Value = DBNull.Value;
        }
        else if (value is Guid || value is Guid?)
        {
            parameter.DbType = DbType.Guid;
            parameter.Value = (Guid)value;
        }
        else if (value is short)
        {
            parameter.DbType = DbType.Int16;
            parameter.Value = value;
        }
        else if (value is int)
        {
            parameter.DbType = DbType.Int32;
            parameter.Value = value;
        }
        else if (value is long)
        {
            parameter.DbType = DbType.Int64;
            parameter.Value = value;
        }
        else if (value is ushort)
        {
            parameter.DbType = DbType.UInt16;
            parameter.Value = value;
        }
        else if (value is uint)
        {
            parameter.DbType = DbType.UInt32;
            parameter.Value = value;
        }
        else if (value is ulong)
        {
            parameter.DbType = DbType.UInt64;
            parameter.Value = value;
        }
        else if (value is double)
        {
            parameter.DbType = DbType.Double;
            parameter.Value = value;
        }
        else if (value is decimal)
        {
            parameter.DbType = DbType.Decimal;
            parameter.Value = value;
        }
        else if (value is string)
        {
            parameter.DbType = DbType.String;
            parameter.Value = value;
        }
        else if (value is DateTime || value is DateTime?)
        {
            parameter.DbType = DbType.DateTime;
            parameter.Value = value;
        }
        else if (value is DateTimeOffset dateTimeOffset)
        {
            parameter.DbType = DbType.DateTimeOffset;
            parameter.Value = dateTimeOffset.ToUniversalTime();
        }
        else if (value is DateTimeOffset?)
        {
            parameter.DbType = DbType.DateTimeOffset;
            parameter.Value = value == null ? null : ((DateTimeOffset?)value).Value.ToUniversalTime();
        }
        else if (value is bool || value is bool?)
        {
            parameter.DbType = DbType.Boolean;
            parameter.Value = value;
        }
        else
        {
            throw new NotSupportedException(string.Format("TransformationProvider does not support value: {0} of type: {1}", value, value.GetType()));
        }
    }

    private string FormatValue(object value)
    {
        if (value == null)
        {
            return null;
        }

        if (value is DateTime)
        {
            return ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss:fff");
        }

        return value.ToString();
    }

    private void QuoteColumnNames(string[] primaryColumns)
    {
        for (var i = 0; i < primaryColumns.Length; i++)
        {
            primaryColumns[i] = QuoteColumnNameIfRequired(primaryColumns[i]);
        }
    }

    public virtual void RemoveIndex(string table, string name)
    {
        if (TableExists(table) && IndexExists(table, name))
        {
            name = QuoteConstraintNameIfRequired(name);
            ExecuteNonQuery(string.Format("DROP INDEX {0}", name));
        }
    }

    public virtual string AddIndex(string table, Index index)
    {
        throw new NotImplementedException($"{nameof(AddIndex)} is not overridden for the provider.");
    }

    public virtual string AddIndex(string name, string table, params string[] columns)
    {
        var index = new Index { Name = name, KeyColumns = columns };

        return AddIndex(table, index);
    }

    protected string QuoteConstraintNameIfRequired(string name)
    {
        return _dialect.ConstraintNameNeedsQuote ? _dialect.Quote(name) : name;
    }

    public abstract bool IndexExists(string table, string name);

    protected virtual string GetPrimaryKeyConstraintName(string table)
    {
        return null;
    }

    public virtual void RemovePrimaryKey(string table)
    {
        if (!TableExists(table))
        {
            return;
        }

        var primaryKeyConstraintName = GetPrimaryKeyConstraintName(table);

        if (primaryKeyConstraintName == null || !ConstraintExists(table, primaryKeyConstraintName))
        {
            return;
        }

        RemoveConstraint(table, primaryKeyConstraintName);
    }

    public virtual void RemoveAllIndexes(string table)
    {
        if (!TableExists(table))
        {
            return;
        }

        var indexes = GetIndexes(table);

        foreach (var index in indexes)
        {
            if (index.Name == null || !IndexExists(table, index.Name))
            {
                continue;
            }

            if (index.PrimaryKey || index.UniqueConstraint)
            {
                RemoveConstraint(table, index.Name);
            }
            else
            {
                RemoveIndex(table, index.Name);
            }
        }
    }

    public virtual string Concatenate(params string[] strings)
    {
        return string.Join(" || ", strings);
    }

    public IDbConnection Connection
    {
        get { return _connection; }
    }

    public IEnumerable<string> GetTables(string schema)
    {
        var tableRestrictions = new string[4];
        tableRestrictions[1] = schema;

        var c = _connection as DbConnection;
        var tables = c.GetSchema("Tables", tableRestrictions);
        return from DataRow row in tables.Rows select (row["TABLE_NAME"] as string);
    }

    public IEnumerable<string> GetColumns(string schema, string table)
    {
        var tableRestrictions = new string[4];
        tableRestrictions[1] = schema;
        tableRestrictions[2] = table;

        var c = _connection as DbConnection;
        var tables = c.GetSchema("Columns", tableRestrictions);
        return from DataRow row in tables.Rows select (row["TABLE_NAME"] as string);
    }

    protected void ValidateIndex(string tableName, Index index)
    {
        var hasFilterItems = index.FilterItems != null && index.FilterItems.Count > 0;
        var columns = GetColumns(table: tableName);

        if (!TableExists(tableName))
        {
            throw new MigrationException($"Table '{tableName}' does not exist.");
        }

        foreach (var keyColumn in index.KeyColumns)
        {
            if (!index.KeyColumns.All(x => columns.Any(y => y.Name.Equals(x, StringComparison.OrdinalIgnoreCase))))
            {
                throw new MigrationException($"Column '{keyColumn}' does not exist.");
            }
        }

        if (hasFilterItems)
        {
            if (!index.FilterItems.All(x => index.KeyColumns.Any(y => x.ColumnName.Equals(y, StringComparison.OrdinalIgnoreCase))))
            {
                throw new MigrationException($"All columns in the {nameof(index.FilterItems)} should exist in the {nameof(index.KeyColumns)}.");
            }
        }

        if (IndexExists(tableName, index.Name))
        {
            throw new MigrationException($"Index '{index.Name}' in table {tableName} already exists.");
        }

        if (index.IncludeColumns != null && index.IncludeColumns.Length > 0)
        {
            if (index.IncludeColumns.Any(x => index.KeyColumns.Any(y => x.Equals(y, StringComparison.OrdinalIgnoreCase))))
            {
                throw new MigrationException($"It is not allowed to use a column in {nameof(index.IncludeColumns)} that exist in {nameof(index.KeyColumns)}.");
            }
        }
    }

    public virtual void CopyDataFromTableToTable(string sourceTableName, List<string> sourceColumnNames, string targetTableName, List<string> targetColumnNames, List<string> orderBySourceColumns = null)
    {
        throw new NotImplementedException();
    }
}