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); } }