public async Task Trigger_Exists()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert);
var exists = await remoteOrchestrator.ExistTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert);
Assert.True(exists);
exists = await remoteOrchestrator.ExistTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Update);
Assert.False(exists);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_Scopes_Multiple_Check_Metadatas_Are_Deleted()
{
var dbName = HelperDatabase.GetRandomName("tcp_ro_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var setup = new SyncSetup(this.Tables);
var setup2 = new SyncSetup(this.Tables);
setup2.Filters.Add("Customer", "EmployeeID");
var remoteScopeInfo1 = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
var remoteScopeInfo2 = await remoteOrchestrator.GetServerScopeInfoAsync("A", setup2);
Assert.NotNull(remoteScopeInfo1.Setup);
Assert.NotNull(remoteScopeInfo1.Schema);
Assert.NotNull(remoteScopeInfo2.Setup);
Assert.NotNull(remoteScopeInfo2.Schema);
// Provision two scopes (already tested in previous test)
remoteScopeInfo1 = await remoteOrchestrator.ProvisionAsync(setup);
remoteScopeInfo2 = await remoteOrchestrator.ProvisionAsync("A", setup2);
// Deprovision
await remoteOrchestrator.DeprovisionAsync("A");
foreach (var table in remoteScopeInfo1.Setup.Tables)
{
var tableName = table.TableName;
var schemaName = table.SchemaName;
foreach (var objectSpType in Enum.GetValues(typeof(Builders.DbStoredProcedureType)))
{
var spType = (Builders.DbStoredProcedureType)objectSpType;
var exists1 = await remoteOrchestrator.ExistStoredProcedureAsync(
remoteScopeInfo1, tableName, schemaName, spType);
var exists2 = await remoteOrchestrator.ExistStoredProcedureAsync(
remoteScopeInfo2, tableName, schemaName, spType);
if (spType == Builders.DbStoredProcedureType.SelectChangesWithFilters ||
spType == Builders.DbStoredProcedureType.SelectInitializedChangesWithFilters)
{
Assert.False(exists1);
}
else
{
Assert.True(exists1);
}
Assert.False(exists2);
}
foreach (var objectSpType in Enum.GetValues(typeof(Builders.DbTriggerType)))
{
var trigType = (Builders.DbTriggerType)objectSpType;
var existsTrig1 = await remoteOrchestrator.ExistTriggerAsync(remoteScopeInfo1, tableName, schemaName, trigType);
var existsTrig2 = await remoteOrchestrator.ExistTriggerAsync(remoteScopeInfo2, tableName, schemaName, trigType);
Assert.False(existsTrig1);
Assert.False(existsTrig2);
}
var trackTableExists1 = await remoteOrchestrator.ExistTrackingTableAsync(remoteScopeInfo1, tableName, schemaName);
var trackTableExists2 = await remoteOrchestrator.ExistTrackingTableAsync(remoteScopeInfo2, tableName, schemaName);
Assert.True(trackTableExists1);
Assert.True(trackTableExists2);
}
// Deprovision
await remoteOrchestrator.DeprovisionAsync();
foreach (var table in remoteScopeInfo1.Setup.Tables)
{
var tableName = table.TableName;
var schemaName = table.SchemaName;
foreach (var objectSpType in Enum.GetValues(typeof(Builders.DbStoredProcedureType)))
{
var spType = (Builders.DbStoredProcedureType)objectSpType;
var exists1 = await remoteOrchestrator.ExistStoredProcedureAsync(
remoteScopeInfo1, tableName, schemaName, spType);
var exists2 = await remoteOrchestrator.ExistStoredProcedureAsync(
remoteScopeInfo2, tableName, schemaName, spType);
Assert.False(exists1);
Assert.False(exists2);
}
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
/// <summary>
/// Create one tracking table
/// </summary>
private static async Task AddingOneColumnInTrackingTable()
{
var provider = new SqlSyncProvider(serverConnectionString);
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "ProductCategory", "ProductModel", "Product" });
var orchestrator = new RemoteOrchestrator(provider, options, setup);
// working on the product Table
var productSetupTable = setup.Tables["Product"];
orchestrator.OnTrackingTableCreating(ttca =>
{
var addingID = $" ALTER TABLE {ttca.TrackingTableName.Schema().Quoted()} ADD internal_id varchar(10) null";
ttca.Command.CommandText += addingID;
});
var trExists = await orchestrator.ExistTrackingTableAsync(productSetupTable);
if (!trExists)
{
await orchestrator.CreateTrackingTableAsync(productSetupTable);
}
orchestrator.OnTriggerCreating(tca =>
{
string val;
if (tca.TriggerType == DbTriggerType.Insert)
{
val = "INS";
}
else if (tca.TriggerType == DbTriggerType.Delete)
{
val = "DEL";
}
else
{
val = "UPD";
}
var cmdText = $"UPDATE Product_tracking " +
$"SET Product_tracking.internal_id='{val}' " +
$"FROM Product_tracking JOIN Inserted ON Product_tracking.ProductID = Inserted.ProductID;";
tca.Command.CommandText += Environment.NewLine + cmdText;
});
var trgExists = await orchestrator.ExistTriggerAsync(productSetupTable, DbTriggerType.Insert);
if (!trgExists)
{
await orchestrator.CreateTriggerAsync(productSetupTable, DbTriggerType.Insert);
}
trgExists = await orchestrator.ExistTriggerAsync(productSetupTable, DbTriggerType.Update);
if (!trgExists)
{
await orchestrator.CreateTriggerAsync(productSetupTable, DbTriggerType.Update);
}
trgExists = await orchestrator.ExistTriggerAsync(productSetupTable, DbTriggerType.Delete);
if (!trgExists)
{
await orchestrator.CreateTriggerAsync(productSetupTable, DbTriggerType.Delete);
}
orchestrator.OnTriggerCreating(null);
}
