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 Trigger_Create_One()
{
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" });
// 1) create a console logger
//var loggerFactory = LoggerFactory.Create(builder => { builder.AddDebug().SetMinimumLevel(LogLevel.Debug); });
//var logger = loggerFactory.CreateLogger("Dotmim.Sync");
var logger = new SyncLogger().AddDebug().SetMinimumLevel(LogLevel.Debug);
options.Logger = logger;
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
remoteOrchestrator.OnTriggerCreating(tca => onCreating++);
remoteOrchestrator.OnTriggerCreated(tca => onCreated++);
remoteOrchestrator.OnTriggerDropping(tca => onDropping++);
remoteOrchestrator.OnTriggerDropped(tca => onDropped++);
var isCreated = await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert);
Assert.True(isCreated);
Assert.Equal(1, onCreating);
Assert.Equal(1, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
// Check
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var check = await SqlManagementUtils.GetTriggerAsync(c, null, "Product_insert_trigger", "SalesLT").ConfigureAwait(false);
Assert.Single(check.Rows);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_Trigger_ShouldNotOverwrite()
{
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 scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" })
{
TriggersPrefix = "trg_",
TriggersSuffix = "_trg"
};
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
var triggerInsert = $"{setup.TriggersPrefix}Product{setup.TriggersSuffix}_insert_trigger";
await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert, false);
var assertOverWritten = false;
remoteOrchestrator.OnTriggerCreating(args =>
{
assertOverWritten = true;
});
await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert, false);
Assert.False(assertOverWritten);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task Trigger_Create_One_Overwrite()
{
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);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
remoteOrchestrator.OnTriggerCreating(tca => onCreating++);
remoteOrchestrator.OnTriggerCreated(tca => onCreated++);
remoteOrchestrator.OnTriggerDropping(tca => onDropping++);
remoteOrchestrator.OnTriggerDropped(tca => onDropped++);
var isCreated = await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert);
Assert.True(isCreated);
Assert.Equal(1, onCreating);
Assert.Equal(1, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
onCreating = 0;
onCreated = 0;
onDropping = 0;
onDropped = 0;
isCreated = await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert);
Assert.False(isCreated);
Assert.Equal(0, onCreating);
Assert.Equal(0, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
isCreated = await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert, true);
Assert.True(isCreated);
Assert.Equal(1, onCreating);
Assert.Equal(1, onCreated);
Assert.Equal(1, onDropping);
Assert.Equal(1, onDropped);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task Trigger_Drop_One_Cancel()
{
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);
var isCreated = await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert);
Assert.True(isCreated);
// Ensuring we have a clean new instance
remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
remoteOrchestrator.OnTriggerCreating(tca => onCreating++);
remoteOrchestrator.OnTriggerCreated(tca => onCreated++);
remoteOrchestrator.OnTriggerDropping(tca =>
{
tca.Cancel = true;
onDropping++;
});
remoteOrchestrator.OnTriggerDropped(tca => onDropped++);
var isDropped = await remoteOrchestrator.DropTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert);
Assert.False(isDropped);
Assert.Equal(0, onCreating);
Assert.Equal(0, onCreated);
Assert.Equal(1, onDropping);
Assert.Equal(0, onDropped);
// Check
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var check = await SqlManagementUtils.GetTriggerAsync(c, null, "Product_insert_trigger", "SalesLT").ConfigureAwait(false);
Assert.Single(check.Rows);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_Trigger_ShouldCreate()
{
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 scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" })
{
TriggersPrefix = "trg_",
TriggersSuffix = "_trg"
};
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
var triggerInsert = $"{setup.TriggersPrefix}Product{setup.TriggersSuffix}_insert_trigger";
await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert, false);
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var trigIns = await SqlManagementUtils.GetTriggerAsync(c, null, triggerInsert, "SalesLT");
Assert.Equal(triggerInsert, trigIns.Rows[0]["Name"].ToString());
c.Close();
}
var triggerUpdate = $"{setup.TriggersPrefix}Product{setup.TriggersSuffix}_update_trigger";
await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Update, false);
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var trig = await SqlManagementUtils.GetTriggerAsync(c, null, triggerUpdate, "SalesLT");
Assert.Equal(triggerUpdate, trig.Rows[0]["Name"].ToString());
c.Close();
}
var triggerDelete = $"{setup.TriggersPrefix}Product{setup.TriggersSuffix}_delete_trigger";
await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Delete, false);
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var trig = await SqlManagementUtils.GetTriggerAsync(c, null, triggerDelete, "SalesLT");
Assert.Equal(triggerDelete, trig.Rows[0]["Name"].ToString());
c.Close();
}
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);
}
