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