private string createInsertSqlCommandString(MinistryPlatformTable table)
{
var sql = "INSERT INTO {targetDbName}.{tableName} ({columns}) VALUES ({placeholders}) ";
var columns = getColumnsForTable(table.tableName);
StringBuilder placeholders = new StringBuilder();
Regex columnName = new Regex(@".*\[(.*)\]");
for (int i = 0; i < columns.Count; i++)
{
if (i > 0)
{
placeholders.Append(", ");
}
placeholders.Append("@").Append(columnName.Replace(columns[i], "$1"));
}
var parms = new Dictionary <string, string> {
{ "columns", String.Join(", ", columns) },
{ "targetDbName", targetDbConnection.Database },
{ "tableName", table.tableName },
{ "placeholders", placeholders.ToString() },
};
return(sql.Inject(parms));
}
public List <MinistryPlatformTable> readConfig(Stream fileStream)
{
List <MinistryPlatformTable> tables = new List <MinistryPlatformTable>();
using (TextFieldParser parser = new TextFieldParser(fileStream))
{
parser.Delimiters = new string[] { "," };
parser.CommentTokens = new string[] { "#", "--", "//", "'" };
parser.TextFieldType = FieldType.Delimited;
parser.HasFieldsEnclosedInQuotes = true;
parser.TrimWhiteSpace = true;
string[] fields;
while (!parser.EndOfData)
{
fields = parser.ReadFields();
var t = new MinistryPlatformTable
{
tableName = fields[0],
migrationType = fields[1].Length > 0 ? (MigrationType)Enum.Parse(typeof(MigrationType), fields[1]) : MigrationType.INSERT_OR_UPDATE,
filterClause = fields[2].Length > 0 ? fields[2] : null,
};
tables.Add(t);
}
}
return(tables);
}
public void testMoveDataWithoutExecute()
{
MinistryPlatformTable table = new MinistryPlatformTable
{
tableName = "table1",
filterClause = "1 = 1"
};
Assert.IsTrue(fixture.moveData(table, false));
}
private string createSelectSqlCommandString(MinistryPlatformTable table)
{
var sql = "SELECT {columns} FROM {sourceDbName}.{tableName} AS S {filterClause} EXCEPT SELECT * FROM {targetDbName}.{tableName} ";
string filterClause = table.filterClause != null && table.filterClause.Length > 0 ? "WHERE " + table.filterClause : "";
var columns = getColumnsForTable(table.tableName);
var parms = new Dictionary <string, string> {
{ "columns", String.Join(", ", columns) },
{ "sourceDbName", sourceDatabaseName },
{ "targetDbName", targetDatabaseName },
{ "tableName", table.tableName },
{ "filterClause", filterClause },
};
return(sql.Inject(parms));
}
public bool moveData(MinistryPlatformTable table, bool execute)
{
var connections = new[] { sourceDbConnection, targetDbConnection };
if (execute)
{
foreach (var conn in connections)
{
conn.Open();
}
}
try
{
logger.Info("BEGIN: Moving data for table " + table.tableName);
var response = doMoveData(table, execute);
logger.Info("DONE: Moving data for table " + table.tableName);
return(response);
}
finally
{
if (execute)
{
foreach (var conn in connections)
{
try
{
conn.Close();
}
catch (Exception e)
{
logger.Warn("Error closing DB Connection for table " + table.tableName, e);
}
}
}
}
}
public void testMoveData()
{
MinistryPlatformTable table = new MinistryPlatformTable
{
tableName = "table1",
filterClause = "1 = 1"
};
sourceDbConnection.Setup(mocked => mocked.Open());
sourceDbConnection.Setup(mocked => mocked.Close());
sourceDbConnection.Setup(mocked => mocked.CreateCommand()).Returns(sourceDbCommand.Object);
sourceDbCommand.SetupSet(mocked => mocked.CommandType = CommandType.Text).Verifiable();
sourceDbCommand.Setup(mocked => mocked.ExecuteReader()).Returns(new Queue <IDataReader>(new[] { sourceDbColumnNameReader.Object, sourceDbColumnNameReader.Object, sourceDbColumnNameReader.Object, sourceDbDataReader.Object }).Dequeue);
sourceDbColumnNameReader.Setup(mocked => mocked.Read()).Returns(new Queue <bool>(new[] { true, false, true, false, true, false }).Dequeue);
sourceDbColumnNameReader.Setup(mocked => mocked.GetString(0)).Returns("column_name");
sourceDbColumnNameReader.Setup(mocked => mocked.Close());
sourceDbColumnNameReader.Setup(mocked => mocked.Dispose());
sourceDbDataReader.SetupGet(mocked => mocked.FieldCount).Returns(1);
sourceDbDataReader.Setup(mocked => mocked.GetSchemaTable()).Returns(schemaTable);
sourceDbDataReader.Setup(mocked => mocked.Read()).Returns(new Queue <bool>(new[] { true, false }).Dequeue);
sourceDbDataReader.Setup(mocked => mocked.GetName(0)).Returns("column_name");
sourceDbDataReader.Setup(mocked => mocked.GetFieldType(0)).Returns(typeof(string));
sourceDbDataReader.Setup(mocked => mocked.GetValue(0)).Returns("value1");
sourceDbCommand.SetupSet(mocked => mocked.CommandType = CommandType.Text).Verifiable();
sourceDbCommand.SetupSet(mocked => mocked.CommandText = "SELECT column_name FROM src.table1 AS S WHERE 1 = 1 EXCEPT SELECT * FROM dest.table1 ").Verifiable();
sourceDbCommand.SetupSet(mocked => mocked.CommandText = "SELECT CONCAT('[', [Column_Name], ']') FROM src.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table1' ORDER BY ordinal_position").Verifiable();
sourceDbCommand.Setup(mocked => mocked.Dispose());
targetDbConnection.SetupGet(mocked => mocked.Database).Returns(targetDbName);
targetDbConnection.Setup(mocked => mocked.Open());
targetDbConnection.Setup(mocked => mocked.Close());
targetDbConnection.Setup(mocked => mocked.CreateCommand()).Returns(targetDbCommand.Object);
targetDbConnection.Setup(mocked => mocked.BeginTransaction(IsolationLevel.ReadUncommitted)).Returns(dbTransaction.Object);
targetDbCommand.SetupSet(mocked => mocked.CommandType = CommandType.Text).Verifiable();
targetDbCommand.SetupGet(mocked => mocked.CommandText).Returns("");
targetDbCommand.Setup(mocked => mocked.CreateParameter()).Returns(dataParameter.Object);
targetDbCommand.Setup(mocked => mocked.Prepare());
preparedStatementParameterCollection.SetupGet(mocked => mocked[0]).Returns(dataParameter.Object);
preparedStatementParameterCollection.Setup(mocked => mocked.Add(It.IsAny <IDbDataParameter>())).Returns(1);
targetDbCommand.SetupGet(mocked => mocked.Parameters).Returns(preparedStatementParameterCollection.Object);
dataParameter.SetupSet(mocked => mocked.ParameterName = "@column_name");
dataParameter.SetupSet(mocked => mocked.DbType = DbType.String);
dataParameter.SetupSet(mocked => mocked.Size = 1);
dataParameter.SetupSet(mocked => mocked.Precision = 2);
dataParameter.SetupSet(mocked => mocked.Scale = 3);
dataParameter.SetupSet(mocked => mocked.Value = "value1");
dataParameter.SetupGet(mocked => mocked.ParameterName).Returns("@column_name");
dataParameter.SetupGet(mocked => mocked.DbType).Returns(DbType.String);
targetDbCommand.Setup(mocked => mocked.Dispose());
targetDbCommand.SetupSet(mocked => mocked.Transaction = dbTransaction.Object).Verifiable();
targetDbCommand.SetupSet(mocked => mocked.CommandText = "UPDATE dest.table1 SET WHERE column_name = @column_name").Verifiable();
targetDbCommand.SetupSet(mocked => mocked.CommandText = "INSERT INTO dest.table1 (column_name) VALUES (@column_name) ").Verifiable();
targetDbCommand.SetupSet(mocked => mocked.CommandText = "SET IDENTITY_INSERT dest.table1 ON").Verifiable();
targetDbCommand.SetupSet(mocked => mocked.CommandText = "SET IDENTITY_INSERT dest.table1 OFF").Verifiable();
targetDbCommand.SetupSet(mocked => mocked.CommandText = "SELECT COUNT(*) FROM dest.sys.columns WHERE IS_IDENTITY = 1 AND OBJECT_NAME(object_id) = 'table1'").Verifiable();
targetDbCommand.SetupSet(mocked => mocked.CommandText = "SELECT column_name FROM dest.INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = 'table1'").Verifiable();
targetDbCommand.Setup(mocked => mocked.ExecuteScalar()).Returns(1);
targetDbCommand.Setup(mocked => mocked.ExecuteNonQuery()).Returns(1);
Mock <IDataReader> pkReader = new Mock <IDataReader>();
targetDbCommand.Setup(mocked => mocked.ExecuteReader()).Returns(pkReader.Object);
pkReader.Setup(mocked => mocked.Read()).Returns(new Queue <bool>(new[] { true, false }).Dequeue);
pkReader.Setup(mocked => mocked.GetString(0)).Returns("column_name");
dbTransaction.Setup(mocked => mocked.Commit());
Assert.IsTrue(fixture.moveData(table, true));
sourceDbConnection.VerifyAll();
targetDbCommand.Verify(mocked => mocked.ExecuteNonQuery(), Times.Exactly(3));
sourceDbCommand.VerifyAll();
dbTransaction.VerifyAll();
sourceDbDataReader.VerifyAll();
}
private bool doMoveData(MinistryPlatformTable table, bool execute)
{
if (!execute)
{
logger.Info("Running in test mode, not moving data for table " + table.tableName);
return(true);
}
var selectSql = createSelectSqlCommandString(table);
logger.Debug("Select SQL: " + selectSql);
var selectCommand = sourceDbConnection.CreateCommand();
selectCommand.CommandType = CommandType.Text;
selectCommand.CommandText = selectSql;
var tx = targetDbConnection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
var insertCommand = targetDbConnection.CreateCommand();
insertCommand.CommandType = CommandType.Text;
insertCommand.CommandText = createInsertSqlCommandString(table);
insertCommand.Transaction = tx;
var updateCommand = createUpdateSqlCommand(table, tx);
IDataReader reader = null;
try
{
setAllowInsertIdentityColumn(table.tableName, tx, true);
reader = selectCommand.ExecuteReader();
logger.Debug("Insert SQL: " + insertCommand.CommandText);
bool firstInsert = true;
bool firstUpdate = true;
var counts = new Counters();
while (reader.Read())
{
if (firstInsert)
{
prepareCommand(insertCommand, reader);
firstInsert = false;
}
try
{
counts.inserts += bindAndExecuteCommand(insertCommand, reader);;
}
catch (SqlException e)
{
if (e.Number == 2601 || e.Number == 2627)
{
if (table.migrationType != MigrationType.INSERT_OR_UPDATE)
{
logger.Warn("Row already exists in table " + table.tableName + ", but migration mode is INSERT_ONLY, skipping: " + e.Message);
counts.skips++;
}
else
{
logger.Warn("Row already exists in table " + table.tableName + ", and migration mode is INSERT_OR_UPDATE, updating: " + e.Message);
if (firstUpdate)
{
prepareCommand(updateCommand, reader);
firstUpdate = false;
}
counts.updates += bindAndExecuteCommand(updateCommand, reader);
}
}
else
{
throw (e);
}
}
if (counts.totals % 10 == 0)
{
logger.Info("Inserted " + counts.inserts + " rows for table " + table.tableName);
logger.Info("Updated " + counts.updates + " rows for table " + table.tableName);
logger.Info("Skipped " + counts.skips + " rows for table " + table.tableName);
logger.Info("Total " + counts.totals + " rows for table " + table.tableName);
}
}
logger.Info("Inserted " + counts.inserts + " rows for table " + table.tableName);
logger.Info("Updated " + counts.updates + " rows for table " + table.tableName);
logger.Info("Skipped " + counts.skips + " rows for table " + table.tableName);
logger.Info("Total " + counts.totals + " rows for table " + table.tableName);
setAllowInsertIdentityColumn(table.tableName, tx, false);
tx.Commit();
return(true);
}
catch (Exception e)
{
logger.Error("Error updating table " + table.tableName + ", updates will be rolled back", e);
setAllowInsertIdentityColumn(table.tableName, tx, false);
tx.Rollback();
return(false);
}
finally
{
var commands = new [] { selectCommand, insertCommand, updateCommand };
foreach (var cmd in commands)
{
try
{
cmd.Dispose();
}
catch (Exception e)
{
logger.Warn("Error disposing command for table " + table.tableName, e);
}
}
}
}
private IDbCommand createUpdateSqlCommand(MinistryPlatformTable table, IDbTransaction tx)
{
// Determine the primary key column(s)
var pkSql = "SELECT column_name FROM {targetDbName}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = '{tableName}'".Inject(new Dictionary <string, string>
{
{ "targetDbName", targetDbConnection.Database },
{ "tableName", getTableName(table.tableName) },
});
var pkCommand = targetDbConnection.CreateCommand();
pkCommand.CommandType = CommandType.Text;
pkCommand.CommandText = pkSql;
pkCommand.Transaction = tx;
var pkColumns = new List <string>();
var pkReader = pkCommand.ExecuteReader();
while (pkReader.Read())
{
pkColumns.Add(pkReader.GetString(0));
}
pkReader.Close();
pkReader.Dispose();
pkCommand.Dispose();
var columns = getColumnsForTable(table.tableName);
StringBuilder sets = new StringBuilder();
StringBuilder where = new StringBuilder();
Regex columnName = new Regex(@".*\[(.*)\]");
bool firstSet = true;
bool firstWhere = true;
for (int i = 0; i < columns.Count; i++)
{
var col = columnName.Replace(columns[i], "$1");
if (pkColumns.Contains(col))
{
if (!firstWhere)
{
where.Append("AND ");
}
firstWhere = false;
where.Append(col).Append(" = @").Append(col);
}
else
{
if (!firstSet)
{
sets.Append(", ");
}
firstSet = false;
sets.Append(col).Append(" = @").Append(col);
}
}
if (where.Length <= 0)
{
throw (new InvalidOperationException("No primary key column was defined for table " + table.tableName));
}
var sql = "UPDATE {targetDbName}.{tableName} SET {sets} WHERE {where}".Inject(new Dictionary <string, string> {
{ "targetDbName", targetDbConnection.Database },
{ "tableName", table.tableName },
{ "sets", sets.ToString() },
{ "where", where.ToString() }
});
var updateCommand = targetDbConnection.CreateCommand();
updateCommand.CommandType = CommandType.Text;
updateCommand.CommandText = sql;
updateCommand.Transaction = tx;
return(updateCommand);
}
