/// <summary>
/// Publish schema changes that hav eoccurred since the last batch started
/// </summary>
/// <param name="tables">Array of table config objects</param>
/// <param name="sourceDB">Source database name</param>
/// <param name="destDB">Destination database name</param>
/// <param name="CTID">Change tracking batch id</param>
/// <param name="afterDate">Date to pull schema changes after</param>
public void PublishSchemaChanges(IEnumerable <TableConf> tables, string sourceDB, string destDB, Int64 CTID, DateTime afterDate)
{
logger.Log("Pulling DDL events from master since " + Convert.ToString(afterDate), LogLevel.Debug);
DataTable ddlEvents = sourceDataUtils.GetDDLEvents(sourceDB, afterDate);
var schemaChanges = new List <SchemaChange>();
DDLEvent dde;
foreach (DataRow row in ddlEvents.Rows)
{
logger.Log("Processing DDLevent...", LogLevel.Trace);
dde = new DDLEvent(row.Field <int>("DdeID"), row.Field <string>("DdeEventData"));
logger.Log("Event initialized. DDEID is " + Convert.ToString(dde.DdeID), LogLevel.Trace);
//a DDL event can yield 0 or more schema change events, hence the List<SchemaChange>
logger.Log("Parsing DDL event XML", LogLevel.Trace);
schemaChanges = dde.Parse(tables, sourceDataUtils, sourceDB);
//iterate through any schema changes for this event and write them to tblCTSchemaChange_CTID
foreach (SchemaChange schemaChange in schemaChanges)
{
logger.Log("Publishing schema change for DdeID " + Convert.ToString(schemaChange.DdeID) + " of type " + Convert.ToString(schemaChange.EventType) +
" for table " + schemaChange.TableName + ", column " + schemaChange.ColumnName, LogLevel.Trace);
destDataUtils.WriteSchemaChange(destDB, CTID, schemaChange);
}
}
}
public void TestParse_AddColumn_WithTableNotInConfig()
{
//schema change on a table not in the Config. it should be ignored.
String xml = @"<EVENT_INSTANCE><EventType>ALTER_TABLE</EventType><SchemaName>dbo</SchemaName>
<ObjectName>testnotinconfig</ObjectName><ObjectType>TABLE</ObjectType><AlterTableActionList>
<Create><Columns><Name>column1</Name></Columns></Create></AlterTableActionList></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
Assert.Equal(0, result.Count);
}
public void TestParse_AddColumn_WithColumnNotInList()
{
//schema change for a column which isn't the list, so we shouldn't publish it
String xml = @"<EVENT_INSTANCE><EventType>ALTER_TABLE</EventType><SchemaName>dbo</SchemaName>
<ObjectName>test2</ObjectName><ObjectType>TABLE</ObjectType><AlterTableActionList>
<Create><Columns><Name>column3</Name></Columns></Create></AlterTableActionList></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
Assert.Equal(0, result.Count);
}
public void TestParse_AddConstraint_ShouldNotPublish()
{
//this change just adds a constraint to an existing column, so it should be ignored
String xml = @"<EVENT_INSTANCE><EventType>ALTER_TABLE</EventType>
<SchemaName>dbo</SchemaName><ObjectName>test1</ObjectName><ObjectType>TABLE</ObjectType><AlterTableActionList>
<Create><Constraints><Name>DF_test1_column1</Name></Constraints></Create></AlterTableActionList></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
Assert.Equal(0, result.Count);
}
public void TestParse_DropColumn_WithColumnNotInList()
{
//dropping a column with a column list, with the column not in the list shouldn't be published
String xml = @"<EVENT_INSTANCE><EventType>ALTER_TABLE</EventType>
<SchemaName>dbo</SchemaName><ObjectName>test2</ObjectName><ObjectType>TABLE</ObjectType>
<AlterTableActionList><Drop><Columns><Name>col2</Name></Columns></Drop></AlterTableActionList></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
Assert.Equal(0, result.Count);
}
public void TestParse_EnableChangeTracking_ShouldNotPublish()
{
//any action that doesn't operate on columns, such as enabling change tracking, shouldn't be published
String xml = @"<EVENT_INSTANCE><EventType>ALTER_TABLE</EventType>
<SchemaName>dbo</SchemaName><ObjectName>test1</ObjectName><ObjectType>TABLE</ObjectType>
<TSQLCommand><CommandText>alter table test1 enable change_tracking</CommandText></TSQLCommand></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
Assert.Equal(0, result.Count);
}
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_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_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_RenameTable()
{
//don't publish rename table events.
String xml = @"<EVENT_INSTANCE><EventType>RENAME</EventType>
<SchemaName>dbo</SchemaName><ObjectName>test1</ObjectName><ObjectType>TABLE</ObjectType>
<TargetObjectName /><TargetObjectType /><NewObjectName>test1_renamed</NewObjectName>
<Parameters><Param>test1</Param><Param>test1_renamed</Param><Param /></Parameters></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
Assert.Equal(0, result.Count);
}
public void TestParse_RenameColumn_WithColumnNotInList()
{
//renaming a column when a column list is present in Config. if the column isn't the list, it shouldn't be published.
String xml = @"<EVENT_INSTANCE><EventType>RENAME</EventType><SchemaName>dbo</SchemaName>
<ObjectName>col2</ObjectName><ObjectType>COLUMN</ObjectType><TargetObjectName>test2</TargetObjectName>
<TargetObjectType>TABLE</TargetObjectType><NewObjectName>col3</NewObjectName>
<Parameters><Param>test1.col2</Param><Param>col3</Param><Param>COLUMN</Param></Parameters></EVENT_INSTANCE>";
DDLEvent dde = new DDLEvent(100, xml);
List <SchemaChange> result = dde.Parse(tables, (IDataUtils)dataUtils, "testdb");
Assert.Equal(0, result.Count);
}
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_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_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_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));
}
protected void BindDataToGridviewDropdownlist()
{
XmlTextReader xmlreader = new XmlTextReader(Server.MapPath("XMLevent.xml"));
DataSet ds = new DataSet();
ds.ReadXml(xmlreader);
xmlreader.Close();
if (ds.Tables.Count != 0)
{
DDLEvent.DataSource = ds;
DDLEvent.DataTextField = "name";
DDLEvent.DataValueField = "id";
DDLEvent.DataBind();
}
}
/// <summary>
/// Publish schema changes that hav eoccurred since the last batch started
/// </summary>
/// <param name="tables">Array of table config objects</param>
/// <param name="sourceDB">Source database name</param>
/// <param name="destDB">Destination database name</param>
/// <param name="CTID">Change tracking batch id</param>
/// <param name="afterDate">Date to pull schema changes after</param>
public void PublishSchemaChanges(IEnumerable<TableConf> tables, string sourceDB, string destDB, Int64 CTID, DateTime afterDate)
{
logger.Log("Pulling DDL events from master since " + Convert.ToString(afterDate), LogLevel.Debug);
DataTable ddlEvents = sourceDataUtils.GetDDLEvents(sourceDB, afterDate);
var schemaChanges = new List<SchemaChange>();
DDLEvent dde;
foreach (DataRow row in ddlEvents.Rows) {
logger.Log("Processing DDLevent...", LogLevel.Trace);
dde = new DDLEvent(row.Field<int>("DdeID"), row.Field<string>("DdeEventData"));
logger.Log("Event initialized. DDEID is " + Convert.ToString(dde.DdeID), LogLevel.Trace);
//a DDL event can yield 0 or more schema change events, hence the List<SchemaChange>
logger.Log("Parsing DDL event XML", LogLevel.Trace);
schemaChanges = dde.Parse(tables, sourceDataUtils, sourceDB);
//iterate through any schema changes for this event and write them to tblCTSchemaChange_CTID
foreach (SchemaChange schemaChange in schemaChanges) {
logger.Log("Publishing schema change for DdeID " + Convert.ToString(schemaChange.DdeID) + " of type " + Convert.ToString(schemaChange.EventType) +
" for table " + schemaChange.TableName + ", column " + schemaChange.ColumnName, LogLevel.Trace);
destDataUtils.WriteSchemaChange(destDB, CTID, schemaChange);
}
}
}