public async Task RemoteOrchestrator_Scope_IsNotNewScope_OnceSaved()
{
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 scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup(this.Tables);
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var remoteScopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
Assert.True(remoteScopeInfo.IsNewScope);
Assert.Equal(SyncVersion.Current, new Version(remoteScopeInfo.Version));
remoteScopeInfo = await remoteOrchestrator.SaveServerScopeInfoAsync(remoteScopeInfo);
Assert.False(remoteScopeInfo.IsNewScope);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
private static async Task ProvisionClientManuallyAsync()
{
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncProvider(serverConnectionString);
var clientProvider = new SqlSyncProvider(clientConnectionString);
// -----------------------------------------------------------------
// Client side
// -----------------------------------------------------------------
// This method is useful if you want to provision by yourself the client database
// You will need to :
// - Create a local orchestrator with the correct setup to provision
// - Get the ServerScopeInfo from the server side using a RemoteOrchestrator or a WebRemoteOrchestrator
// - Provision everything locally
// Create a local orchestrator used to provision everything locally
var localOrchestrator = new LocalOrchestrator(clientProvider);
// Because we need the schema from remote side, create a remote orchestrator
var remoteOrchestrator = new RemoteOrchestrator(serverProvider);
// Getting the server scope from server side
var serverScope = await remoteOrchestrator.GetServerScopeInfoAsync();
// You can create a WebRemoteOrchestrator and get the ServerScope as well
// var proxyClientProvider = new WebRemoteOrchestrator("https://localhost:44369/api/Sync");
// var serverScope = proxyClientProvider.GetServerScopeInfoAsync();
// Provision everything needed (sp, triggers, tracking tables, AND TABLES)
await localOrchestrator.ProvisionAsync(serverScope);
}
public async Task RemoteOrchestrator_GetServerScopeInfo_NonExistingTables_ShouldFail()
{
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);
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
// Create a bad setup with a non existing table
var tables = new string[]
{
"SalesLT.ProductCategory", "SalesLT.ProductModel", "SalesLT.Product", "Employee", "Customer", "Address", "CustomerAddress", "EmployeeAddress",
"SalesLT.SalesOrderHeader", "SalesLT.SalesOrderDetail", "Posts", "Tags", "PostTag",
"PricesList", "PricesListCategory", "PricesListDetail", "WRONGTABLE"
};
var setup = new SyncSetup(tables);
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var se = await Assert.ThrowsAsync <SyncException>(async() =>
{
await remoteOrchestrator.GetServerScopeInfoAsync(setup);
});
Assert.Equal(SyncStage.Provisioning, se.SyncStage);
Assert.Equal(SyncSide.ServerSide, se.Side);
Assert.Equal("MissingTableException", se.TypeName);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_GetServerScopeInfo_NonExistingColumns_ShouldFail()
{
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);
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
// Create a bad setup with a non existing table
var tables = new string[] { "Customer", "Address", "CustomerAddress" };
var setup = new SyncSetup(tables);
setup.Tables["Customer"].Columns.AddRange(new string[] { "FirstName", "LastName", "CompanyName", "BADCOLUMN" });
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var se = await Assert.ThrowsAsync <SyncException>(async() =>
{
await remoteOrchestrator.GetServerScopeInfoAsync(setup);
});
Assert.Equal(SyncStage.Provisioning, se.SyncStage);
Assert.Equal(SyncSide.ServerSide, se.Side);
Assert.Equal("MissingColumnException", se.TypeName);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_StoredProcedure_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 scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" })
{
StoredProceduresPrefix = "sp_",
StoredProceduresSuffix = "_sp"
};
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
await remoteOrchestrator.CreateStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges, false);
Assert.True(await remoteOrchestrator.ExistStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges));
Assert.False(await remoteOrchestrator.ExistStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.UpdateRow));
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_Provision_ShouldFails_If_SetupTable_DoesNotExist()
{
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);
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.badTable" });
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var se = await Assert.ThrowsAsync <SyncException>(async() =>
await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup));
Assert.Equal(SyncStage.Provisioning, se.SyncStage);
Assert.Equal(SyncSide.ServerSide, se.Side);
Assert.Equal("MissingTableException", se.TypeName);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
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 Table_Drop_All()
{
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);
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(this.Tables);
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var onDropping = 0;
var onDropped = 0;
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
remoteOrchestrator.OnTableDropping(ttca => onDropping++);
remoteOrchestrator.OnTableDropped(ttca => onDropped++);
await remoteOrchestrator.DropTablesAsync(scopeInfo);
Assert.Equal(this.Tables.Length, onDropping);
Assert.Equal(this.Tables.Length, onDropped);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_GetServerScopeInfo_SetupColumnsDefined_ShouldReturn_SchemaWithSetupColumnsOnly()
{
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);
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
// Create a bad setup with a non existing table
var tables = new string[] { "Customer", "Address", "CustomerAddress" };
var setup = new SyncSetup(tables);
setup.Tables["Customer"].Columns.AddRange(new string[] { "CustomerID", "FirstName", "LastName", "CompanyName" });
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
Assert.Equal(3, scopeInfo.Schema.Tables.Count);
// Only 4 columns shoud be part of Customer table
Assert.Equal(4, scopeInfo.Schema.Tables["Customer"].Columns.Count);
Assert.Equal(9, scopeInfo.Schema.Tables["Address"].Columns.Count);
Assert.Equal(5, scopeInfo.Schema.Tables["CustomerAddress"].Columns.Count);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_Provision_ShouldCreate_StoredProcedures_WithSpecificScopeName()
{
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" })
{
StoredProceduresPrefix = "s",
StoredProceduresSuffix = "proc"
};
// trackign table name is composed with prefix and suffix from setup
var bulkDelete = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_bulkdelete";
var bulkUpdate = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_bulkupdate";
var changes = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_changes";
var delete = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_delete";
var deletemetadata = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_deletemetadata";
var initialize = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_initialize";
var reset = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_reset";
var selectrow = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_selectrow";
var update = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_update";
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
// Needs the tracking table to be able to create stored procedures
var provision = SyncProvision.TrackingTable | SyncProvision.StoredProcedures;
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
await remoteOrchestrator.ProvisionAsync(scopeInfo, provision);
using (var connection = new SqlConnection(cs))
{
await connection.OpenAsync().ConfigureAwait(false);
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, bulkDelete));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, bulkUpdate));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, changes));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, delete));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, deletemetadata));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, initialize));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, reset));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, selectrow));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, update));
connection.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task TrackingTable_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);
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" })
{
TrackingTablesPrefix = "t_",
TrackingTablesSuffix = "_t"
};
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
var onCreating = false;
var onCreated = false;
remoteOrchestrator.OnTrackingTableCreating(ttca =>
{
var addingID = $" ALTER TABLE {ttca.TrackingTableName.Schema().Quoted()} ADD internal_id int identity(1,1)";
ttca.Command.CommandText += addingID;
onCreating = true;
});
remoteOrchestrator.OnTrackingTableCreated(ttca =>
{
onCreated = true;
});
await remoteOrchestrator.CreateTrackingTableAsync(scopeInfo, "Product", "SalesLT");
Assert.True(onCreating);
Assert.True(onCreated);
// Check we have a new column in tracking table
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var cols = await SqlManagementUtils.GetColumnsForTableAsync(c, null, "t_Product_t", "SalesLT").ConfigureAwait(false);
Assert.Equal(7, cols.Rows.Count);
Assert.NotNull(cols.Rows.FirstOrDefault(r => r["name"].ToString() == "internal_id"));
c.Close();
}
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 Trigger_Create_All()
{
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.CreateTriggersAsync(scopeInfo, "Product", "SalesLT");
Assert.True(isCreated);
Assert.Equal(3, onCreating);
Assert.Equal(3, 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);
check = await SqlManagementUtils.GetTriggerAsync(c, null, "Product_update_trigger", "SalesLT").ConfigureAwait(false);
Assert.Single(check.Rows);
check = await SqlManagementUtils.GetTriggerAsync(c, null, "Product_delete_trigger", "SalesLT").ConfigureAwait(false);
Assert.Single(check.Rows);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_Provision_ShouldCreate_Triggers()
{
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" })
{
TrackingTablesSuffix = "sync",
TrackingTablesPrefix = "trck",
TriggersPrefix = "trg_",
TriggersSuffix = "_trg"
};
// trackign table name is composed with prefix and suffix from setup
var triggerDelete = $"{setup.TriggersPrefix}Product{setup.TriggersSuffix}_delete_trigger";
var triggerInsert = $"{setup.TriggersPrefix}Product{setup.TriggersSuffix}_insert_trigger";
var triggerUpdate = $"{setup.TriggersPrefix}Product{setup.TriggersSuffix}_update_trigger";
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
// Needs the tracking table to be able to create triggers
var provision = SyncProvision.TrackingTable | SyncProvision.Triggers;
await remoteOrchestrator.ProvisionAsync(scopeInfo, provision);
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var trigDel = await SqlManagementUtils.GetTriggerAsync(c, null, triggerDelete, "SalesLT");
Assert.Equal(triggerDelete, trigDel.Rows[0]["Name"].ToString());
var trigIns = await SqlManagementUtils.GetTriggerAsync(c, null, triggerInsert, "SalesLT");
Assert.Equal(triggerInsert, trigIns.Rows[0]["Name"].ToString());
var trigUdate = await SqlManagementUtils.GetTriggerAsync(c, null, triggerUpdate, "SalesLT");
Assert.Equal(triggerUpdate, trigUdate.Rows[0]["Name"].ToString());
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_GetServerScopeInfo_ShouldReturnSchema()
{
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);
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(this.Tables);
var onSchemaRead = false;
var onSchemaReading = false;
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
remoteOrchestrator.OnSchemaLoading(args =>
{
Assert.Equal(scopeName, args.Context.ScopeName);
onSchemaReading = true;
});
remoteOrchestrator.OnSchemaLoaded(args =>
{
Assert.IsType <SchemaLoadedArgs>(args);
Assert.Equal(SyncStage.Provisioning, args.Context.SyncStage);
Assert.Equal(scopeName, args.Context.ScopeName);
Assert.NotNull(args.Connection);
Assert.Null(args.Transaction);
Assert.Equal(ConnectionState.Open, args.Connection.State);
Assert.Equal(16, args.Schema.Tables.Count);
onSchemaRead = true;
});
AssertConnectionAndTransaction(remoteOrchestrator, scopeName);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
Assert.NotNull(scopeInfo.Schema);
Assert.NotNull(scopeInfo.Setup);
Assert.Equal(16, scopeInfo.Schema.Tables.Count);
Assert.True(onSchemaRead);
Assert.True(onSchemaReading);
var schema = await remoteOrchestrator.GetSchemaAsync(scopeName, setup);
Assert.NotNull(schema);
Assert.Equal(16, schema.Tables.Count);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task TrackingTable_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);
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" });
setup.TrackingTablesPrefix = "t_";
setup.TrackingTablesSuffix = "_t";
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
var onDropping = false;
var onDropped = false;
remoteOrchestrator.OnTrackingTableDropping(ttca =>
{
ttca.Cancel = true;
onDropping = true;
});
remoteOrchestrator.OnTrackingTableDropped(ttca =>
{
onDropped = true;
});
await remoteOrchestrator.CreateTrackingTableAsync(scopeInfo, "Product", "SalesLT");
await remoteOrchestrator.DropTrackingTableAsync(scopeInfo, "Product", "SalesLT");
Assert.True(onDropping);
Assert.False(onDropped);
// Check we have a new column in tracking table
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var table = await SqlManagementUtils.GetTableAsync(c, null, "t_Product_t", "SalesLT").ConfigureAwait(false);
Assert.NotEmpty(table.Rows);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_StoredProcedure_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" })
{
StoredProceduresPrefix = "sp_",
StoredProceduresSuffix = "_sp"
};
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
await remoteOrchestrator.CreateStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges, false);
Assert.True(await remoteOrchestrator.ExistStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges));
// Adding a filter to check if stored procedures "with filters" are also generated
setup.Filters.Add("Product", "ProductCategoryID", "SalesLT");
// Create a new scope with this filter
var scopeName2 = "scope2";
var scopeInfo2 = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName2, setup);
await remoteOrchestrator.CreateStoredProcedureAsync(scopeInfo2, "Product", "SalesLT", DbStoredProcedureType.SelectChangesWithFilters, false);
Assert.True(await remoteOrchestrator.ExistStoredProcedureAsync(scopeInfo2, "Product", "SalesLT", DbStoredProcedureType.SelectChangesWithFilters));
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task Trigger_Drop_All()
{
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.CreateTriggersAsync(scopeInfo, "Product", "SalesLT");
Assert.True(isCreated);
Assert.Equal(3, onCreating);
Assert.Equal(3, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
onCreating = 0;
onCreated = 0;
onDropping = 0;
onDropped = 0;
var isDropped = await remoteOrchestrator.DropTriggersAsync(scopeInfo, "Product", "SalesLT");
Assert.True(isCreated);
Assert.Equal(0, onCreating);
Assert.Equal(0, onCreated);
Assert.Equal(3, onDropping);
Assert.Equal(3, onDropped);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_Scopes_Multiples_Check_Schema_Setup_AreNotEmpty()
{
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 remoteScopeInfo1 = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
var remoteScopeInfo2 = await remoteOrchestrator.GetServerScopeInfoAsync("A", setup);
Assert.Equal(SyncOptions.DefaultScopeName, remoteScopeInfo1.Name);
Assert.Equal("A", remoteScopeInfo2.Name);
Assert.NotNull(remoteScopeInfo1);
Assert.NotNull(remoteScopeInfo2);
Assert.NotNull(remoteScopeInfo1.Schema);
Assert.NotNull(remoteScopeInfo2.Schema);
Assert.NotNull(remoteScopeInfo1.Setup);
Assert.NotNull(remoteScopeInfo2.Setup);
Assert.True(remoteScopeInfo1.IsNewScope);
Assert.True(remoteScopeInfo2.IsNewScope);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
/// <summary>
/// Create one tracking table
/// </summary>
private static async Task CreateOneTrackingTable()
{
var provider = new SqlSyncProvider(serverConnectionString);
var options = new SyncOptions();
var setup = new SyncSetup("ProductCategory", "ProductModel", "Product");
var orchestrator = new RemoteOrchestrator(provider, options);
var serverScope = await orchestrator.GetServerScopeInfoAsync(setup);
var spExists = await orchestrator.ExistTrackingTableAsync(serverScope, "Product");;
if (!spExists)
{
await orchestrator.CreateTrackingTableAsync(serverScope, "Product");
}
}
/// <summary>
/// Create one stored procedure
/// </summary>
private static async Task CreateOneStoredProcedure()
{
var provider = new SqlSyncProvider(serverConnectionString);
var options = new SyncOptions();
var setup = new SyncSetup("ProductCategory", "ProductModel", "Product");
var orchestrator = new RemoteOrchestrator(provider, options);
var serverScope = await orchestrator.GetServerScopeInfoAsync("v1", setup);
var spExists = await orchestrator.ExistStoredProcedureAsync(serverScope, "Product", null, DbStoredProcedureType.SelectChanges);
if (!spExists)
{
await orchestrator.CreateStoredProcedureAsync(serverScope, "Product", null, DbStoredProcedureType.SelectChanges);
}
}
public async Task RemoteOrchestrator_Provision_SchemaFail_If_SchemaHasColumnsDefinitionButNoPrimaryKey()
{
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 scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var schema = new SyncSet();
var table = new SyncTable("Product", "SalesLT");
var colID = new SyncColumn("ID", typeof(Guid));
var colName = new SyncColumn("Name", typeof(string));
table.Columns.Add(colID);
table.Columns.Add(colName);
table.Columns.Add("Number", typeof(int));
schema.Tables.Add(table);
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
// Overriding scope info to introduce a bad table with no primary key
scopeInfo.Schema = schema;
scopeInfo.Setup = setup;
var provision = SyncProvision.Table | SyncProvision.TrackingTable | SyncProvision.StoredProcedures | SyncProvision.Triggers;
var se = await Assert.ThrowsAsync <SyncException>(
async() => await remoteOrchestrator.ProvisionAsync(scopeInfo, provision));
Assert.Equal(SyncStage.Provisioning, se.SyncStage);
Assert.Equal(SyncSide.ServerSide, se.Side);
Assert.Equal("MissingPrimaryKeyException", se.TypeName);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_StoredProcedure_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" })
{
StoredProceduresPrefix = "sp_",
StoredProceduresSuffix = "_sp"
};
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
var storedProcedureSelectChanges = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_changes";
await remoteOrchestrator.CreateStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges, false);
var assertOverWritten = false;
remoteOrchestrator.OnStoredProcedureCreating(args =>
{
assertOverWritten = true;
});
await remoteOrchestrator.CreateStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges, false);
Assert.False(assertOverWritten);
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 RemoteOrchestrator_Scope_Should_Fail_If_NoTables_In_Setup()
{
var dbName = HelperDatabase.GetRandomName("tcp_ro_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var options = new SyncOptions();
var setup = new SyncSetup();
var provider = new SqlSyncProvider(cs);
var remoteOrchestrator = new RemoteOrchestrator(provider, options);
var exc = await Assert.ThrowsAsync <SyncException>(() => remoteOrchestrator.GetServerScopeInfoAsync(setup));
Assert.IsType <SyncException>(exc);
Assert.Equal("MissingServerScopeTablesException", exc.TypeName);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_Scope_CancellationToken_ShouldInterrupt_EnsureScope_OnConnectionOpened()
{
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 setup = new SyncSetup(this.Tables);
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
using var cts = new CancellationTokenSource();
remoteOrchestrator.OnConnectionOpen(args => cts.Cancel());
var se = await Assert.ThrowsAsync <SyncException>(
async() => await remoteOrchestrator.GetServerScopeInfoAsync(setup, default, default, cts.Token));
public async Task RemoteOrchestrator_GetServerScopeInfo_CancellationToken_ShouldInterrupt()
{
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 setup = new SyncSetup(this.Tables);
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
using var cts = new CancellationTokenSource();
remoteOrchestrator.OnConnectionOpen(args =>
{
Assert.Equal(SyncStage.ScopeLoading, args.Context.SyncStage);
Assert.IsType <ConnectionOpenedArgs>(args);
Assert.NotNull(args.Connection);
Assert.Null(args.Transaction);
Assert.Equal(ConnectionState.Open, args.Connection.State);
cts.Cancel();
});
var se = await Assert.ThrowsAsync <SyncException>(async() =>
{
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(cancellationToken: cts.Token);
});
Assert.Equal(SyncStage.ScopeLoading, se.SyncStage);
Assert.Equal(SyncSide.ServerSide, se.Side);
Assert.Equal("OperationCanceledException", se.TypeName);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_GetServerScopeInfo_ShouldFail_If_SetupIsEmpty()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var options = new SyncOptions();
var setup = new SyncSetup();
var provider = new SqlSyncProvider(cs);
var orchestrator = new RemoteOrchestrator(provider, options);
var se = await Assert.ThrowsAsync <SyncException>(
async() => await orchestrator.GetServerScopeInfoAsync("scope1", setup));
Assert.Equal(SyncStage.ScopeLoading, se.SyncStage);
Assert.Equal(SyncSide.ServerSide, se.Side);
Assert.Equal("MissingServerScopeTablesException", se.TypeName);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task TrackingTable_Drop_All()
{
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);
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.ProductCategory", "SalesLT.ProductModel", "SalesLT.Product", "Posts" });
setup.TrackingTablesPrefix = "t_";
setup.TrackingTablesSuffix = "_t";
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
var onDropping = 0;
var onDropped = 0;
remoteOrchestrator.OnTrackingTableDropping(ttca => onDropping++);
remoteOrchestrator.OnTrackingTableDropped(ttca => onDropped++);
await remoteOrchestrator.CreateTrackingTablesAsync(scopeInfo);
await remoteOrchestrator.DropTrackingTablesAsync(scopeInfo);
Assert.Equal(4, onDropping);
Assert.Equal(4, onDropped);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_Scope()
{
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);
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(this.Tables);
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeTableCreating = 0;
var scopeTableCreated = 0;
var scopeLoading = 0;
var scopeLoaded = 0;
var scopeSaving = 0;
var scopeSaved = 0;
remoteOrchestrator.OnScopeSaving(ssa =>
{
Assert.NotNull(ssa.Command);
scopeSaving++;
});
remoteOrchestrator.OnScopeSaved(ssa => scopeSaved++);
remoteOrchestrator.OnScopeTableCreating(stca =>
{
Assert.NotNull(stca.Command);
scopeTableCreating++;
});
remoteOrchestrator.OnScopeTableCreated(stca =>
{
scopeTableCreated++;
});
remoteOrchestrator.OnServerScopeInfoLoading(args =>
{
Assert.NotNull(args.Command);
Assert.Equal(scopeName, args.Context.ScopeName);
Assert.Equal(scopeName, args.ScopeName);
Assert.NotNull(args.Connection);
Assert.NotNull(args.Transaction);
Assert.Equal(ConnectionState.Open, args.Connection.State);
scopeLoading++;
});
remoteOrchestrator.OnServerScopeInfoLoaded(args =>
{
Assert.Equal(scopeName, args.Context.ScopeName);
Assert.NotNull(args.Connection);
Assert.NotNull(args.Transaction);
scopeLoaded++;
});
var serverScopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
// TODO : if serverScope.Schema is null, should we Provision here ?
serverScopeInfo.Version = "2.0";
await remoteOrchestrator.SaveServerScopeInfoAsync(serverScopeInfo);
Assert.Equal(2, scopeTableCreating);
Assert.Equal(2, scopeTableCreated);
Assert.Equal(2, scopeLoading);
Assert.Equal(3, scopeLoaded);
Assert.Equal(3, scopeSaving);
Assert.Equal(3, scopeSaved);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
