public PrimaryKeyInfo(PrimaryKeyInfo src)
 {
     FieldNames = new string[src.FieldNames.Length];
     for (int i = 0; i < src.FieldNames.Length; ++i)
     {
         FieldNames[i] = src.FieldNames[i];
     }
 }
        static void CreateTable(
            this SqlConnection conn,
            SqlTable table,
            PrimaryKeyInfo primaryKey,
            Dictionary <string, IColumnInfo> fields,
            Dictionary <string, NamedKeyInfo> tableKeys,
            uint tableRevision,
            ILog log,
            SqlTransaction transaction)
        {
            var b = new SqlCommandBuilder();

            log.InfoFormat("Creating table '{0}' at revision {1}", table.Name, tableRevision);
            var fieldSqls = new List <string>();

            foreach (IColumnInfo field in fields.Values)
            {
                fieldSqls.Add(field.FieldSql(table.Name));
            }
            if (null != primaryKey)
            {
                fieldSqls.Add(primaryKey.FieldSql());
            }

            string escapedTableName = b.QuoteIdentifier(table.Name);
            var    cmd = new StringBuilder("CREATE TABLE " + escapedTableName + " (");

            cmd.Append(string.Join(",", fieldSqls));
            cmd.Append(");");
            foreach (NamedKeyInfo key in tableKeys.Values)
            {
                cmd.Append(key.Sql(table.Name));
            }
            cmd.AppendFormat("EXEC sys.{2} @name=N'table_revision', " +
                             "@value = N'{1}', @level0type = N'SCHEMA', @level0name = N'dbo'," +
                             "@level1type = N'TABLE', @level1name = N'{0}';", table.Name, tableRevision, "sp_addextendedproperty");
            ExecuteStatement(conn, cmd.ToString(), log, transaction);
        }
        public static void MigrateTables(this SqlConnection conn, IMigrationElement[] processTable, ILog log)
        {
            var            b                       = new SqlCommandBuilder();
            var            tableFields             = new Dictionary <string, IColumnInfo>();
            PrimaryKeyInfo primaryKey              = null;
            var            tableKeys               = new Dictionary <string, NamedKeyInfo>();
            SqlTable       table                   = null;
            uint           processingTableRevision = 0;
            uint           currentAtRevision       = 0;
            SqlTransaction insideTransaction       = null;

            m_MaxAvailableMigrationRevision = 1;

            if (processTable.Length == 0)
            {
                throw new MsSqlMigrationException("Invalid MsSql migration");
            }

            if (null == processTable[0] as SqlTable)
            {
                throw new MsSqlMigrationException("First entry must be table name");
            }

            bool skipToNext = false;

            foreach (IMigrationElement migration in processTable)
            {
                Type migrationType = migration.GetType();

                if (typeof(SqlTable) == migrationType)
                {
                    skipToNext = false;
                    if (insideTransaction != null)
                    {
                        CommentTable(conn, table.Name, processingTableRevision, log, insideTransaction);
                        insideTransaction.Commit();
                        insideTransaction = null;
                    }

                    if (null != table && 0 != processingTableRevision)
                    {
                        if (currentAtRevision == 0)
                        {
                            conn.CreateTable(
                                table,
                                primaryKey,
                                tableFields,
                                tableKeys,
                                processingTableRevision,
                                log, insideTransaction);
                        }
                        tableFields.Clear();
                        tableKeys.Clear();
                        primaryKey = null;
                    }
                    table                   = (SqlTable)migration;
                    currentAtRevision       = conn.GetTableRevision(table.Name);
                    processingTableRevision = 1;
                    if (currentAtRevision != 0 && m_DeleteTablesBefore)
                    {
                        log.Info($"Dropping table {table.Name}");
                        ExecuteStatement(conn, $"DROP TABLE {table.Name}", log, insideTransaction);
                        currentAtRevision = 0;
                    }
                }
                else if (skipToNext)
                {
                    /* skip processing */
                    if (typeof(TableRevision) == migrationType)
                    {
                        m_MaxAvailableMigrationRevision = Math.Max(m_MaxAvailableMigrationRevision, ((TableRevision)migration).Revision);
                    }
                }
                else if (typeof(TableRevision) == migrationType)
                {
                    if (insideTransaction != null)
                    {
                        CommentTable(conn, table.Name, processingTableRevision, log, insideTransaction);
                        insideTransaction.Commit();
                        insideTransaction = null;
                        if (currentAtRevision != 0)
                        {
                            currentAtRevision = processingTableRevision;
                        }
                    }

                    var rev = (TableRevision)migration;
                    m_MaxAvailableMigrationRevision = Math.Max(m_MaxAvailableMigrationRevision, rev.Revision);
                    if (processingTableRevision == m_StopAtMigrationRevision)
                    {
                        /* advance to next table for testing */
                        skipToNext = true;
                        continue;
                    }

                    if (rev.Revision != processingTableRevision + 1)
                    {
                        throw new MsSqlMigrationException(string.Format("Invalid TableRevision entry. Expected {0}. Got {1}", processingTableRevision + 1, rev.Revision));
                    }

                    processingTableRevision = rev.Revision;

                    if (processingTableRevision - 1 == currentAtRevision && 0 != currentAtRevision)
                    {
                        insideTransaction = conn.BeginTransaction(System.Data.IsolationLevel.Serializable);
                        log.InfoFormat("Migration table '{0}' to revision {1}", table.Name, processingTableRevision);
                    }
                }
                else if (processingTableRevision == 0 || table == null)
                {
                    if (table != null)
                    {
                        throw new MsSqlMigrationException("Unexpected processing element for " + table.Name);
                    }
                    else
                    {
                        throw new MsSqlMigrationException("Unexpected processing element");
                    }
                }
                else
                {
                    Type[] interfaces = migration.GetType().GetInterfaces();

                    if (interfaces.Contains(typeof(IAddColumn)))
                    {
                        var columnInfo = (IAddColumn)migration;
                        if (tableFields.ContainsKey(columnInfo.Name))
                        {
                            throw new ArgumentException("Column " + columnInfo.Name + " was added twice.");
                        }
                        tableFields.Add(columnInfo.Name, columnInfo);
                        if (insideTransaction != null)
                        {
                            ExecuteStatement(conn, columnInfo.Sql(table.Name), log, insideTransaction);
                        }
                    }
                    else if (interfaces.Contains(typeof(IChangeColumn)))
                    {
                        var         columnInfo = (IChangeColumn)migration;
                        IColumnInfo oldColumn;
                        if (columnInfo.OldName?.Length != 0)
                        {
                            if (!tableFields.TryGetValue(columnInfo.OldName, out oldColumn))
                            {
                                throw new ArgumentException("Change column for " + columnInfo.Name + " has no preceeding AddColumn for " + columnInfo.OldName);
                            }
                        }
                        else if (!tableFields.TryGetValue(columnInfo.Name, out oldColumn))
                        {
                            throw new ArgumentException("Change column for " + columnInfo.Name + " has no preceeding AddColumn");
                        }
                        if (insideTransaction != null)
                        {
                            ExecuteStatement(conn, columnInfo.Sql(table.Name, oldColumn.FieldType), log, insideTransaction);
                        }
                        if (columnInfo.OldName?.Length != 0)
                        {
                            tableFields.Remove(columnInfo.OldName);
                            if (primaryKey != null)
                            {
                                string[] fields = primaryKey.FieldNames;
                                int      n      = fields.Length;
                                for (int i = 0; i < n; ++i)
                                {
                                    if (fields[i] == columnInfo.OldName)
                                    {
                                        fields[i] = columnInfo.Name;
                                    }
                                }
                            }
                            foreach (NamedKeyInfo keyinfo in tableKeys.Values)
                            {
                                string[] fields = keyinfo.FieldNames;
                                int      n      = fields.Length;
                                for (int i = 0; i < n; ++i)
                                {
                                    if (fields[i] == columnInfo.OldName)
                                    {
                                        fields[i] = columnInfo.Name;
                                    }
                                }
                            }
                        }
                        tableFields[columnInfo.Name] = columnInfo;
                    }
                    else if (migrationType == typeof(DropColumn))
                    {
                        var columnInfo = (DropColumn)migration;
                        if (insideTransaction != null)
                        {
                            ExecuteStatement(conn, columnInfo.Sql(table.Name, tableFields[columnInfo.Name].FieldType), log, insideTransaction);
                        }
                        tableFields.Remove(columnInfo.Name);
                    }
                    else if (migrationType == typeof(PrimaryKeyInfo))
                    {
                        if (null != primaryKey && insideTransaction != null)
                        {
                            ExecuteStatement(conn, "DECLARE @SQL VARCHAR(4000);" +
                                             "SET @SQL = 'ALTER TABLE " + b.QuoteIdentifier(table.Name) + " DROP CONSTRAINT |ConstraintName| ';" +
                                             "SET @SQL = REPLACE(@SQL, '|ConstraintName|', ( SELECT   name " +
                                             "FROM sysobjects " +
                                             "WHERE xtype = 'PK' " +
                                             " AND parent_obj = OBJECT_ID(" + table.Name.ToMsSqlQuoted() + ")));" +
                                             "EXEC(@SQL);", log, insideTransaction);
                        }
                        primaryKey = new PrimaryKeyInfo((PrimaryKeyInfo)migration);
                        if (insideTransaction != null)
                        {
                            ExecuteStatement(conn, primaryKey.Sql(table.Name), log, insideTransaction);
                        }
                    }
                    else if (migrationType == typeof(DropPrimaryKeyinfo))
                    {
                        if (null != primaryKey && insideTransaction != null)
                        {
                            ExecuteStatement(conn, ((DropPrimaryKeyinfo)migration).Sql(table.Name), log, insideTransaction);
                        }
                        primaryKey = null;
                    }
                    else if (migrationType == typeof(NamedKeyInfo))
                    {
                        var namedKey = (NamedKeyInfo)migration;
                        tableKeys.Add(namedKey.Name, new NamedKeyInfo(namedKey));
                        if (insideTransaction != null)
                        {
                            ExecuteStatement(conn, namedKey.Sql(table.Name), log, insideTransaction);
                        }
                    }
                    else if (migrationType == typeof(DropNamedKeyInfo))
                    {
                        var namedKey = (DropNamedKeyInfo)migration;
                        tableKeys.Remove(namedKey.Name);
                        if (insideTransaction != null)
                        {
                            ExecuteStatement(conn, namedKey.Sql(table.Name), log, insideTransaction);
                        }
                    }
                    else
                    {
                        throw new MsSqlMigrationException("Invalid type " + migrationType.FullName + " in migration list");
                    }
                }
            }

            if (insideTransaction != null)
            {
                CommentTable(conn, table.Name, processingTableRevision, log, insideTransaction);
                insideTransaction.Commit();
                if (currentAtRevision != 0)
                {
                    currentAtRevision = processingTableRevision;
                }
                insideTransaction = null;
            }

            if (null != table && 0 != processingTableRevision && currentAtRevision == 0)
            {
                conn.CreateTable(
                    table,
                    primaryKey,
                    tableFields,
                    tableKeys,
                    processingTableRevision,
                    log, insideTransaction);
            }
        }