private void ApplySchemaChange(string destDB, TableConf table, SchemaChange schemaChange)
{
switch (schemaChange.EventType)
{
case SchemaChangeType.Rename:
logger.Log("Renaming column " + schemaChange.ColumnName + " to " + schemaChange.NewColumnName, LogLevel.Info);
destDataUtils.RenameColumn(table, destDB, schemaChange.ColumnName, schemaChange.NewColumnName, Config.SlaveCTDB);
break;
case SchemaChangeType.Modify:
logger.Log("Changing data type on column " + schemaChange.ColumnName, LogLevel.Info);
destDataUtils.ModifyColumn(table, destDB, schemaChange.ColumnName, schemaChange.DataType, Config.SlaveCTDB);
break;
case SchemaChangeType.Add:
logger.Log("Adding column " + schemaChange.ColumnName, LogLevel.Info);
destDataUtils.AddColumn(table, destDB, schemaChange.ColumnName, schemaChange.DataType, Config.SlaveCTDB);
break;
case SchemaChangeType.Drop:
logger.Log("Dropping column " + schemaChange.ColumnName, LogLevel.Info);
destDataUtils.DropColumn(table, destDB, schemaChange.ColumnName, Config.SlaveCTDB);
break;
}
}
/// <summary>
/// Initializes static members of the <see cref="DiscoverySchemaChanges"/> class.
/// </summary>
static DiscoverySchemaChanges()
{
// Schema change for passing extended data with the SearchMailboxes request
SearchMailboxesExtendedData = new SchemaChange("15.0.730.0");
// Schema change for additional search scopes such as "AllMailboxes", "PublicFolders", "SearchId" etc with the SearchMailboxes request
SearchMailboxesAdditionalSearchScopes = new SchemaChange("15.0.730.0");
}
/// <summary>
/// Initializes static members of the <see cref="DiscoverySchemaChanges"/> class.
/// </summary>
static DiscoverySchemaChanges()
{
// Schema change for passing extended data with the SearchMailboxes request
SearchMailboxesExtendedData = new SchemaChange("15.0.730.0");
// Schema change for additional search scopes such as "AllMailboxes", "PublicFolders", "SearchId" etc with the SearchMailboxes request
SearchMailboxesAdditionalSearchScopes = new SchemaChange("15.0.730.0");
}
public void TestParse_ModifyColumn_WithoutColumnList()
{
//changing a column's data type should be published
String xml = @"<EVENT_INSTANCE><EventType>ALTER_TABLE</EventType>
<SchemaName>dbo</SchemaName><ObjectName>test1</ObjectName><ObjectType>TABLE</ObjectType>
<AlterTableActionList><Alter><Columns><Name>column1</Name></Columns></Alter></AlterTableActionList></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
SchemaChange expected = new SchemaChange(100, SchemaChangeType.Modify, "dbo", "test1", "column1", null, new DataType("int", null, (byte)10, 0));
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
Assert.True(result[0].Equals(expected));
}
public void TestParse_DropColumn_WithColumnInList()
{
//dropping a column with a column list, with the column in the list should be published
String xml = @"<EVENT_INSTANCE><EventType>ALTER_TABLE</EventType>
<SchemaName>dbo</SchemaName><ObjectName>test2</ObjectName><ObjectType>TABLE</ObjectType>
<AlterTableActionList><Drop><Columns><Name>column1</Name></Columns></Drop></AlterTableActionList></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
SchemaChange expected = new SchemaChange(100, SchemaChangeType.Drop, "dbo", "test2", "column1", null, null);
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
Assert.True(result[0].Equals(expected));
}
public void TestParse_AddColumn_WithColumnInList()
{
//schema change for a column which is in the list, so we should publish it
String xml = @"<EVENT_INSTANCE><EventType>ALTER_TABLE</EventType><SchemaName>dbo</SchemaName>
<ObjectName>test2</ObjectName><ObjectType>TABLE</ObjectType><AlterTableActionList>
<Create><Columns><Name>column2</Name></Columns></Create></AlterTableActionList></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
SchemaChange expected = new SchemaChange(100, SchemaChangeType.Add, "dbo", "test2", "column2", null, new DataType("datetime", null, null, null));
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
Assert.True(result[0].Equals(expected));
}
public void TestParse_RenameColumn_WithColumnInList()
{
//renaming a column when a column list is present in Config. if the column is in the list, it should be published.
String xml = @"<EVENT_INSTANCE><EventType>RENAME</EventType><SchemaName>dbo</SchemaName>
<ObjectName>column2</ObjectName><ObjectType>COLUMN</ObjectType><TargetObjectName>test2</TargetObjectName>
<TargetObjectType>TABLE</TargetObjectType><NewObjectName>col2</NewObjectName>
<Parameters><Param>test2.column2</Param><Param>col2</Param><Param>COLUMN</Param></Parameters></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
SchemaChange expected = new SchemaChange(100, SchemaChangeType.Rename, "dbo", "test2", "column2", "col2", null);
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
Assert.True(result[0].Equals(expected));
}
public void TestParse_ModifyColumn_CaseSensitivity()
{
//same as previous test but with casing on the table name not matching the casing in the dataset.
//doing this to make sure the case ignoring code works.
String xml = @"<EVENT_INSTANCE><EventType>ALTER_TABLE</EventType>
<SchemaName>dbo</SchemaName><ObjectName>TEST1</ObjectName><ObjectType>TABLE</ObjectType>
<AlterTableActionList><Alter><Columns><Name>COLUMN1</Name></Columns></Alter></AlterTableActionList></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
SchemaChange expected = new SchemaChange(100, SchemaChangeType.Modify, "dbo", "TEST1", "COLUMN1", null, new DataType("int", null, (byte)10, 0));
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
Assert.True(result[0].Equals(expected));
}
public void TestParse_AddSingleColumn_WithoutColumnList()
{
//add a column and make sure it is published
String xml = @"<EVENT_INSTANCE><EventType>ALTER_TABLE</EventType><SchemaName>dbo</SchemaName>
<ObjectName>test1</ObjectName><ObjectType>TABLE</ObjectType><AlterTableActionList>
<Create><Columns><Name>column1</Name></Columns></Create></AlterTableActionList></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
SchemaChange expected = new SchemaChange(100, SchemaChangeType.Add, "dbo", "test1", "column1", null, new DataType("int", null, (byte)10, 0));
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
//assert.equal doesn't work for objects but this does
Assert.True(result[0].Equals(expected));
}
public void TestParse_AddColumn_WithDefault()
{
//adding in a constraint, we should still publish the new column but not the constraint
String xml = @"<EVENT_INSTANCE><EventType>ALTER_TABLE</EventType>
<SchemaName>dbo</SchemaName><ObjectName>test1</ObjectName><ObjectType>TABLE</ObjectType>
<AlterTableActionList><Create><Columns><Name>column1</Name></Columns>
<Constraints><Name>DF_test1_column3</Name></Constraints></Create></AlterTableActionList></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
SchemaChange expected = new SchemaChange(100, SchemaChangeType.Add, "dbo", "test1", "column1", null, new DataType("int", null, (byte)10, 0));
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
Assert.True(result[0].Equals(expected));
}
public void TestParse_AddMultiColumn_WithoutColumnList()
{
//add multiple columns, we should publish each of them
String xml = @"<EVENT_INSTANCE><EventType>ALTER_TABLE</EventType><SchemaName>dbo</SchemaName>
<ObjectName>test1</ObjectName><ObjectType>TABLE</ObjectType><AlterTableActionList>
<Create><Columns><Name>column1</Name><Name>column2</Name></Columns></Create></AlterTableActionList></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
SchemaChange expected1 = new SchemaChange(100, SchemaChangeType.Add, "dbo", "test1", "column1", null, new DataType("int", null, (byte)10, 0));
SchemaChange expected2 = new SchemaChange(100, SchemaChangeType.Add, "dbo", "test1", "column2", null, new DataType("varchar", 100, null, null));
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
Assert.True(result[0].Equals(expected1));
Assert.True(result[1].Equals(expected2));
}
public void TestEquals()
{
SchemaChange one = new SchemaChange(1, SchemaChangeType.Add, "sname", "tname", "cname", "ncname");
SchemaChange two = new SchemaChange(1, SchemaChangeType.Add, "sname", "tname", "cname", "ncname");
Assert.True(one.Equals(two));
two = new SchemaChange(1, SchemaChangeType.Drop, "sname", "tname", "cname", "ncname");
Assert.False(one.Equals(two));
two = new SchemaChange(1, SchemaChangeType.Add, "asdf", "tname", "cname", "ncname");
Assert.False(one.Equals(two));
two = new SchemaChange(1, SchemaChangeType.Add, "sname", "tname", "asdf", "ncname");
Assert.False(one.Equals(two));
}
public void TestPublishSchemaChanges()
{
//create tblCTSchemaChange_100
destDataUtils.CreateSchemaChangeTable("CT_testdb", 101);
//publish schema changes from tblDDLevent
PublishSchemaChanges(Config.Tables, "testdb", "CT_testdb", 101, new DateTime(2000, 1, 1));
//retrieve results from tblCTSchemaChange_101
DataTable results = destDataUtils.GetSchemaChanges("CT_testdb", 101);
//parse schema change object for the resulting row
SchemaChange actual = new SchemaChange(results.Rows[0]);
//it should be equal to this
SchemaChange expected = new SchemaChange(10, SchemaChangeType.Add, "dbo", "test1", "column2", null, new DataType("varchar", 100, null, null));
Assert.True(actual.Equals(expected));
//undo changes
((TestDataUtils)destDataUtils).ReloadData("test1");
}
public void ApplySchemaChanges(string sourceDB, string destDB, Int64 CTID)
{
//get list of schema changes from tblCTSChemaChange_ctid on the relay server/db
DataTable result = sourceDataUtils.GetSchemaChanges(sourceDB, CTID);
if (result == null)
{
return;
}
foreach (DataRow row in result.Rows)
{
var schemaChange = new SchemaChange(row);
//String.Compare method returns 0 if the strings are equal
TableConf table = Config.Tables.SingleOrDefault(item => String.Compare(item.Name, schemaChange.TableName, ignoreCase: true) == 0);
if (table == null)
{
logger.Log(new { message = "Ignoring schema change for untracked table", Table = schemaChange.TableName }, LogLevel.Debug);
continue;
}
logger.Log("Processing schema change (CscID: " + row.Field <int>("CscID") +
") of type " + schemaChange.EventType + " for table " + table.Name, LogLevel.Info);
if (table.ColumnList == null || table.ColumnList.Contains(schemaChange.ColumnName, StringComparer.OrdinalIgnoreCase))
{
logger.Log("Schema change applies to a valid column, so we will apply it", LogLevel.Info);
try {
ApplySchemaChange(destDB, table, schemaChange);
} catch (Exception e) {
var wrappedExc = new Exception(schemaChange.ToString(), e);
HandleException(wrappedExc, table);
}
}
else
{
logger.Log("Skipped schema change because the column it impacts is not in our list", LogLevel.Info);
}
}
}
public void WriteSchemaChange(string dbName, Int64 CTID, SchemaChange schemaChange)
{
string schemaChangeTableName = "dbo.tblCTSchemaChange_" + Convert.ToString(CTID);
DataRow row = testData.Tables[schemaChangeTableName, GetTableSpace(dbName)].NewRow();
//set its values
row["CscDdeID"] = schemaChange.DdeID;
row["CscTableName"] = schemaChange.TableName;
row["CscEventType"] = schemaChange.EventType;
row["CscSchema"] = schemaChange.SchemaName;
row["CscColumnName"] = schemaChange.ColumnName;
row["CscNewColumnName"] = schemaChange.NewColumnName;
row["CscBaseDataType"] = schemaChange.DataType.BaseType;
row["CscCharacterMaximumLength"] = (object)schemaChange.DataType.CharacterMaximumLength ?? DBNull.Value;
row["CscNumericPrecision"] = (object)schemaChange.DataType.NumericPrecision ?? DBNull.Value;
row["CscNumericScale"] = (object)schemaChange.DataType.NumericScale ?? DBNull.Value;
//add it to the datatable
testData.Tables[schemaChangeTableName, GetTableSpace(dbName)].Rows.Add(row);
//commit the change
//testData.Tables[schemaChangeTableName].AcceptChanges();
}
public void ApplySchemaChanges(string sourceDB, string destDB, Int64 CTID)
{
//get list of schema changes from tblCTSChemaChange_ctid on the relay server/db
DataTable result = sourceDataUtils.GetSchemaChanges(sourceDB, CTID);
if (result == null) {
return;
}
foreach (DataRow row in result.Rows) {
var schemaChange = new SchemaChange(row);
//String.Compare method returns 0 if the strings are equal
TableConf table = Config.Tables.SingleOrDefault(item => String.Compare(item.Name, schemaChange.TableName, ignoreCase: true) == 0);
if (table == null) {
logger.Log(new { message = "Ignoring schema change for untracked table", Table = schemaChange.TableName }, LogLevel.Debug);
continue;
}
logger.Log("Processing schema change (CscID: " + row.Field<int>("CscID") +
") of type " + schemaChange.EventType + " for table " + table.Name, LogLevel.Info);
if (table.ColumnList == null || table.ColumnList.Contains(schemaChange.ColumnName, StringComparer.OrdinalIgnoreCase)) {
logger.Log("Schema change applies to a valid column, so we will apply it", LogLevel.Info);
try {
ApplySchemaChange(destDB, table, schemaChange);
} catch (Exception e) {
var wrappedExc = new Exception(schemaChange.ToString(), e);
HandleException(wrappedExc, table);
}
} else {
logger.Log("Skipped schema change because the column it impacts is not in our list", LogLevel.Info);
}
}
}
public static SchemaChange CreateSchemaChange(int schemaChangeId, int majorReleaseNumber, int minorReleaseNumber, int pointReleaseNumber, string scriptName, global::System.DateTime dateAppliedUtc)
{
SchemaChange schemaChange = new SchemaChange();
schemaChange.SchemaChangeId = schemaChangeId;
schemaChange.MajorReleaseNumber = majorReleaseNumber;
schemaChange.MinorReleaseNumber = minorReleaseNumber;
schemaChange.PointReleaseNumber = pointReleaseNumber;
schemaChange.ScriptName = scriptName;
schemaChange.DateAppliedUtc = dateAppliedUtc;
return schemaChange;
}
public void AddToSchemaChanges(SchemaChange schemaChange)
{
base.AddObject("SchemaChanges", schemaChange);
}
private SchemaChange RunSingleScript(UpdateScript script)
{
var schemaScript = BuildSchemaFromFile(script);
if (schemaScript == null)
{
schemaScript = new SchemaChange();
schemaScript.Status = Models.SchemaChange.Status_Failed;
schemaScript.ScriptErrors = string.Format("The script name {0} was not found. The script did not run.", script.Name);
return(schemaScript);
}
//Fire the Before Event if we are not creating inital schema or database defaults.
if (!script.Equals(INITIAL_DATABASE_DEFAULTS_FILENAME) && !script.Equals(INITIAL_DATABASE_SCHEMA_FILENAME))
{
ScriptRunEventArgs scriptEventArgs = new ScriptRunEventArgs()
{
ScriptName = script.Name,
ScriptVersion = script.Version
};
OnBeforeScriptRun(scriptEventArgs);
}
using (SqlConnection connection = new SqlConnection(ConnectionString.ConnectionString))
{
connection.Open();
foreach (var item in schemaScript.ScriptLines)
{
schemaScript.Comment = ExtractSingleComment(item, SCRIPT_COMMENT_PREFIX);
using (SqlTransaction transaction = connection.BeginTransaction())
{
try
{
SqlCommand sqlCommand = new SqlCommand(item, connection);
sqlCommand.Connection = connection;
sqlCommand.Transaction = transaction;
sqlCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
schemaScript.ScriptErrors = string.Format("Failed Running script {0} with the comment {1}. SQL Syntax is {2} Error: {3}", schemaScript.ScriptName, schemaScript.Comment, item, ex.Message);
//Get out becuase something broke!
schemaScript.Status = Models.SchemaChange.Status_Failed;
return(schemaScript);
}
}
}
schemaScript.Status = Models.SchemaChange.Status_Success;
if (schemaScript.LogThisChange)
{
AddSchemaChange(schemaScript);
}
//Fire the Before Event if we are not creating inital schema or database defaults.
if (!script.Equals(INITIAL_DATABASE_DEFAULTS_FILENAME) && !script.Equals(INITIAL_DATABASE_SCHEMA_FILENAME))
{
ScriptRunEventArgs scriptEventArgs = new ScriptRunEventArgs()
{
ScriptName = script.Name,
ScriptVersion = script.Version
};
OnAfterScriptRun(scriptEventArgs);
}
return(schemaScript);
}
}
public void WriteSchemaChange(string dbName, Int64 CTID, SchemaChange schemaChange)
{
string schemaChangeTableName = "dbo.tblCTSchemaChange_" + Convert.ToString(CTID);
DataRow row = testData.Tables[schemaChangeTableName, GetTableSpace(dbName)].NewRow();
//set its values
row["CscDdeID"] = schemaChange.DdeID;
row["CscTableName"] = schemaChange.TableName;
row["CscEventType"] = schemaChange.EventType;
row["CscSchema"] = schemaChange.SchemaName;
row["CscColumnName"] = schemaChange.ColumnName;
row["CscNewColumnName"] = schemaChange.NewColumnName;
row["CscBaseDataType"] = schemaChange.DataType.BaseType;
row["CscCharacterMaximumLength"] = (object)schemaChange.DataType.CharacterMaximumLength ?? DBNull.Value;
row["CscNumericPrecision"] = (object)schemaChange.DataType.NumericPrecision ?? DBNull.Value;
row["CscNumericScale"] = (object)schemaChange.DataType.NumericScale ?? DBNull.Value;
//add it to the datatable
testData.Tables[schemaChangeTableName, GetTableSpace(dbName)].Rows.Add(row);
//commit the change
//testData.Tables[schemaChangeTableName].AcceptChanges();
}
public void WriteSchemaChange(string dbName, Int64 CTID, SchemaChange schemaChange)
{
throw new NotImplementedException("Netezza is only supported as a slave!");
}
public void WriteSchemaChange(string dbName, Int64 CTID, SchemaChange schemaChange)
{
throw new NotImplementedException("Netezza is only supported as a slave!");
}
private void ApplySchemaChange(string destDB, TableConf table, SchemaChange schemaChange)
{
switch (schemaChange.EventType) {
case SchemaChangeType.Rename:
logger.Log("Renaming column " + schemaChange.ColumnName + " to " + schemaChange.NewColumnName, LogLevel.Info);
destDataUtils.RenameColumn(table, destDB, schemaChange.SchemaName, schemaChange.TableName,
schemaChange.ColumnName, schemaChange.NewColumnName);
break;
case SchemaChangeType.Modify:
logger.Log("Changing data type on column " + schemaChange.ColumnName, LogLevel.Info);
destDataUtils.ModifyColumn(table, destDB, schemaChange.SchemaName, schemaChange.TableName, schemaChange.ColumnName, schemaChange.DataType.ToString());
break;
case SchemaChangeType.Add:
logger.Log("Adding column " + schemaChange.ColumnName, LogLevel.Info);
destDataUtils.AddColumn(table, destDB, schemaChange.SchemaName, schemaChange.TableName, schemaChange.ColumnName, schemaChange.DataType.ToString());
break;
case SchemaChangeType.Drop:
logger.Log("Dropping column " + schemaChange.ColumnName, LogLevel.Info);
destDataUtils.DropColumn(table, destDB, schemaChange.SchemaName, schemaChange.TableName, schemaChange.ColumnName);
break;
}
}
