public async Task LocalOrchestrator_CancellationToken_ShouldInterrupt_EnsureScope_OnConnectionOpened()
{
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 localOrchestrator = new LocalOrchestrator(sqlProvider, options);
using var cts = new CancellationTokenSource();
localOrchestrator.OnConnectionOpen(args => cts.Cancel());
var se = await Assert.ThrowsAsync <SyncException>(
async() => await localOrchestrator.GetClientScopeInfoAsync(cancellationToken: cts.Token));
Assert.Equal(SyncSide.ClientSide, se.Side);
Assert.Equal("OperationCanceledException", se.TypeName);
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 LocalOrchestrator_EnsureScope_NewScope_WithoutSetup_ShouldBeEmpty()
{
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 scopeName = "scope";
var options = new SyncOptions();
var localOrchestrator = new LocalOrchestrator(sqlProvider, options);
var localScopeInfo = await localOrchestrator.GetClientScopeInfoAsync(scopeName);
Assert.NotNull(localScopeInfo);
Assert.Equal(scopeName, localScopeInfo.Name);
Assert.True(localScopeInfo.IsNewScope);
Assert.NotEqual(Guid.Empty, localScopeInfo.Id);
Assert.Null(localScopeInfo.LastServerSyncTimestamp);
Assert.Null(localScopeInfo.LastSync);
Assert.Equal(0, localScopeInfo.LastSyncDuration);
Assert.Null(localScopeInfo.LastSyncTimestamp);
Assert.Null(localScopeInfo.Schema);
Assert.Null(localScopeInfo.Setup);
Assert.Equal(SyncVersion.Current, new Version(localScopeInfo.Version));
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task BaseOrchestrator_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 localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
var storedProcedureSelectChanges = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_changes";
await localOrchestrator.CreateStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectChanges, false);
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectChanges));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.UpdateRow));
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task LocalOrchestrator_BeginSession_ShouldIncrement_SyncStage()
{
var options = new SyncOptions();
var setup = new SyncSetup();
var provider = new SqlSyncProvider();
var onSessionBegin = false;
var localOrchestrator = new LocalOrchestrator(provider, options, setup);
var ctx = localOrchestrator.GetContext();
localOrchestrator.OnSessionBegin(args =>
{
Assert.Equal(SyncStage.BeginSession, args.Context.SyncStage);
Assert.IsType <SessionBeginArgs>(args);
Assert.Null(args.Connection);
Assert.Null(args.Transaction);
onSessionBegin = true;
});
await localOrchestrator.BeginSessionAsync();
Assert.Equal(SyncStage.BeginSession, ctx.SyncStage);
Assert.True(onSessionBegin);
}
public async Task BaseOrchestrator_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 localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
var provision = SyncProvision.Table | SyncProvision.TrackingTable | SyncProvision.StoredProcedures | SyncProvision.Triggers;
var se = await Assert.ThrowsAsync <SyncException>(async() => await localOrchestrator.ProvisionAsync(provision));
Assert.Equal(SyncStage.Provisioning, se.SyncStage);
Assert.Equal(SyncSide.ClientSide, se.Side);
Assert.Equal("MissingTableException", 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", "dbo.Sql", "Posts" });
setup.TrackingTablesPrefix = "t_";
setup.TrackingTablesSuffix = "_t";
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onDropping = 0;
var onDropped = 0;
localOrchestrator.OnTrackingTableDropping(ttca => onDropping++);
localOrchestrator.OnTrackingTableDropped(ttca => onDropped++);
await localOrchestrator.CreateTrackingTablesAsync();
await localOrchestrator.DropTrackingTablesAsync();
Assert.Equal(5, onDropping);
Assert.Equal(5, onDropped);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task TrackingTable_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);
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", "SalesLT.ProductCategory" });
setup.TrackingTablesPrefix = "t_";
setup.TrackingTablesSuffix = "_t";
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
await localOrchestrator.CreateTrackingTableAsync(setup.Tables[0]);
var exists = await localOrchestrator.ExistTrackingTableAsync(setup.Tables[0]);
Assert.True(exists);
exists = await localOrchestrator.ExistTrackingTableAsync(setup.Tables[1]);
Assert.False(exists);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task BaseOrchestrator_GetSchema_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 localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var se = await Assert.ThrowsAsync <SyncException>(async() =>
{
var schema = await localOrchestrator.GetSchemaAsync();
});
Assert.Equal(SyncStage.SchemaReading, se.SyncStage);
Assert.Equal(SyncSide.ClientSide, se.Side);
Assert.Equal("MissingColumnException", se.TypeName);
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 localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onDropping = 0;
var onDropped = 0;
localOrchestrator.OnTableDropping(ttca => onDropping++);
localOrchestrator.OnTableDropped(ttca => onDropped++);
await localOrchestrator.DropTablesAsync();
Assert.Equal(this.Tables.Length, onDropping);
Assert.Equal(this.Tables.Length, onDropped);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task StoredProcedure_Drop_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" });
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var isCreated = await localOrchestrator.CreateStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectChanges);
Assert.True(isCreated);
// Ensuring we have a clean new instance
localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
localOrchestrator.OnStoredProcedureCreating(tca => onCreating++);
localOrchestrator.OnStoredProcedureCreated(tca => onCreated++);
localOrchestrator.OnStoredProcedureDropping(tca => onDropping++);
localOrchestrator.OnStoredProcedureDropped(tca => onDropped++);
var isDropped = await localOrchestrator.DropStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectChanges);
Assert.True(isDropped);
Assert.Equal(0, onCreating);
Assert.Equal(0, onCreated);
Assert.Equal(1, onDropping);
Assert.Equal(1, onDropped);
// Check
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var check = await SqlManagementUtils.ProcedureExistsAsync(c, null, "SalesLT.Product_changes").ConfigureAwait(false);
Assert.False(check);
c.Close();
}
// try to delete a non existing one
isDropped = await localOrchestrator.DropStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectChangesWithFilters);
Assert.False(isDropped);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task BaseOrchestrator_GetSchema_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", "dbo.Sql", "Posts", "Tags", "PostTag",
"PricesList", "PricesListCategory", "PricesListDetail", "WRONGTABLE"
};
var setup = new SyncSetup(tables);
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var se = await Assert.ThrowsAsync <SyncException>(async() =>
{
var schema = await localOrchestrator.GetSchemaAsync();
});
Assert.Equal(SyncStage.SchemaReading, se.SyncStage);
Assert.Equal(SyncSide.ClientSide, se.Side);
Assert.Equal("MissingTableException", se.TypeName);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task LocalTimestamp()
{
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 localOrchestrator = new LocalOrchestrator(sqlProvider, options);
var onLTLoading = 0;
var onLTLoaded = 0;
localOrchestrator.OnLocalTimestampLoading(tca => onLTLoading++);
localOrchestrator.OnLocalTimestampLoaded(tca => onLTLoaded++);
var ts = await localOrchestrator.GetLocalTimestampAsync();
Assert.Equal(1, onLTLoading);
Assert.Equal(1, onLTLoaded);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task BaseOrchestrator_Provision_SchemaCreated_If_SetupHasTables()
{
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.Product" });
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
var provision = SyncProvision.Table | SyncProvision.TrackingTable | SyncProvision.StoredProcedures | SyncProvision.Triggers;
var schema = await localOrchestrator.ProvisionAsync(provision);
var context = localOrchestrator.GetContext();
Assert.Equal(SyncStage.Provisioning, context.SyncStage);
Assert.Single(schema.Tables);
Assert.Equal("SalesLT.Product", schema.Tables[0].GetFullName());
Assert.Equal(17, schema.Tables[0].Columns.Count);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task BaseOrchestrator_Provision_SchemaNotCreated_If_SetupHasTables_AndDbIsEmpty()
{
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 scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
var provision = SyncProvision.Table | SyncProvision.TrackingTable | SyncProvision.StoredProcedures | SyncProvision.Triggers;
var se = await Assert.ThrowsAsync <SyncException>(async() => await localOrchestrator.ProvisionAsync(provision));
Assert.Equal(SyncStage.Provisioning, se.SyncStage);
Assert.Equal(SyncSide.ClientSide, 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 localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var isCreated = await localOrchestrator.CreateTriggerAsync(setup.Tables["Product", "SalesLT"], DbTriggerType.Insert);
var exists = await localOrchestrator.ExistTriggerAsync(setup.Tables["Product", "SalesLT"], DbTriggerType.Insert);
Assert.True(exists);
exists = await localOrchestrator.ExistTriggerAsync(setup.Tables["Product", "SalesLT"], DbTriggerType.Update);
Assert.False(exists);
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 localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var isCreated = await localOrchestrator.CreateTriggerAsync(setup.Tables["Product", "SalesLT"], DbTriggerType.Insert);
Assert.True(isCreated);
// Ensuring we have a clean new instance
localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
localOrchestrator.OnTriggerCreating(tca => onCreating++);
localOrchestrator.OnTriggerCreated(tca => onCreated++);
localOrchestrator.OnTriggerDropping(tca =>
{
tca.Cancel = true;
onDropping++;
});
localOrchestrator.OnTriggerDropped(tca => onDropped++);
var isDropped = await localOrchestrator.DropTriggerAsync(setup.Tables["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 Table_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);
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product", "SalesLT.ProductCategory" });
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));
table.PrimaryKeys.Add("ID");
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
await localOrchestrator.CreateTableAsync(table);
var exists = await localOrchestrator.ExistTableAsync(setup.Tables[0]).ConfigureAwait(false);
Assert.True(exists);
exists = await localOrchestrator.ExistTableAsync(setup.Tables[1]).ConfigureAwait(false);
Assert.False(exists);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task BaseOrchestrator_GetSchema_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 localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var schema = await localOrchestrator.GetSchemaAsync();
Assert.Equal(SyncStage.SchemaReading, localOrchestrator.GetContext().SyncStage);
Assert.Equal(3, schema.Tables.Count);
// Only 4 columns shoud be part of Customer table
Assert.Equal(4, schema.Tables["Customer"].Columns.Count);
Assert.Equal(9, schema.Tables["Address"].Columns.Count);
Assert.Equal(5, schema.Tables["CustomerAddress"].Columns.Count);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
private static async Task DeprovisionClientManuallyAsync()
{
// Create client provider
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Create standard Setup and Options
var setup = new SyncSetup(new string[] { "Address", "Customer", "CustomerAddress" });
var options = new SyncOptions();
// Create a local orchestrator used to Deprovision everything
var localOrchestrator = new LocalOrchestrator(clientProvider, options, setup);
// Get the local scope
var clientScope = await localOrchestrator.GetClientScopeAsync();
// Deprovision everything
await localOrchestrator.DeprovisionAsync(SyncProvision.StoredProcedures
| SyncProvision.Triggers | SyncProvision.TrackingTable | SyncProvision.Table);
// affect good values
clientScope.Setup = null;
clientScope.Schema = null;
// save the local scope
await localOrchestrator.SaveClientScopeAsync(clientScope);
}
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 localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
localOrchestrator.OnTriggerCreating(tca => onCreating++);
localOrchestrator.OnTriggerCreated(tca => onCreated++);
localOrchestrator.OnTriggerDropping(tca => onDropping++);
localOrchestrator.OnTriggerDropped(tca => onDropped++);
var isCreated = await localOrchestrator.CreateTriggerAsync(setup.Tables["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 localOrchestrator.CreateTriggerAsync(setup.Tables["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 localOrchestrator.CreateTriggerAsync(setup.Tables["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 Table_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 options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
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));
table.PrimaryKeys.Add("ID");
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = false;
var onCreated = false;
localOrchestrator.OnTableCreating(ttca =>
{
var addingID = Environment.NewLine + $"ALTER TABLE {ttca.TableName.Schema().Quoted()} ADD internal_id int identity(1,1)";
ttca.Command.CommandText += addingID;
onCreating = true;
});
localOrchestrator.OnTableCreated(ttca =>
{
onCreated = true;
});
var isCreated = await localOrchestrator.CreateTableAsync(table);
Assert.True(isCreated);
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, "Product", "SalesLT").ConfigureAwait(false);
Assert.Equal(4, cols.Rows.Count);
Assert.NotNull(cols.Rows.FirstOrDefault(r => r["name"].ToString() == "internal_id"));
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task BaseOrchestrator_Provision_ShouldCreate_StoredProcedures()
{
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" });
setup.StoredProceduresPrefix = "s";
setup.StoredProceduresSuffix = "proc";
// trackign table name is composed with prefix and suffix from setup
var bulkDelete = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_bulkdelete";
var bulkUpdate = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_bulkupdate";
var changes = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_changes";
var delete = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_delete";
var deletemetadata = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_deletemetadata";
var initialize = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_initialize";
var reset = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_reset";
var selectrow = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_selectrow";
var update = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_update";
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
// Needs the tracking table to be able to create stored procedures
var provision = SyncProvision.TrackingTable | SyncProvision.StoredProcedures;
await localOrchestrator.ProvisionAsync(provision);
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(c, null, bulkDelete));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(c, null, bulkUpdate));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(c, null, changes));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(c, null, delete));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(c, null, deletemetadata));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(c, null, initialize));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(c, null, reset));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(c, null, selectrow));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(c, null, update));
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task BaseOrchestrator_Provision_ShouldCreate_TrackingTable()
{
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 scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup {
TrackingTablesSuffix = "sync", TrackingTablesPrefix = "trck"
};
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));
table.PrimaryKeys.Add("ID");
//schema.TrackingTablesSuffix = "sync";
//schema.TrackingTablesPrefix = "trck";
schema.Tables.Add(table);
// trackign table name is composed with prefix and suffix from setup
var trackingTableName = $"{setup.TrackingTablesPrefix}{table.TableName}{setup.TrackingTablesSuffix}";
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
var provision = SyncProvision.TrackingTable;
await localOrchestrator.ProvisionAsync(schema, provision);
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var tbl = await SqlManagementUtils.GetTableAsync(c, null, trackingTableName, "SalesLT");
var tblName = tbl.Rows[0]["TableName"].ToString();
var schName = tbl.Rows[0]["SchemaName"].ToString();
Assert.Equal(trackingTableName, tblName);
Assert.Equal(table.SchemaName, schName);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task BaseOrchestrator_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" });
setup.TrackingTablesSuffix = "sync";
setup.TrackingTablesPrefix = "trck";
setup.TriggersPrefix = "trg_";
setup.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 localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
// Needs the tracking table to be able to create triggers
var provision = SyncProvision.TrackingTable | SyncProvision.Triggers;
await localOrchestrator.ProvisionAsync(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 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 localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
localOrchestrator.OnTriggerCreating(tca => onCreating++);
localOrchestrator.OnTriggerCreated(tca => onCreated++);
localOrchestrator.OnTriggerDropping(tca => onDropping++);
localOrchestrator.OnTriggerDropped(tca => onDropped++);
var isCreated = await localOrchestrator.CreateTriggerAsync(setup.Tables["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 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" });
setup.TrackingTablesPrefix = "t_";
setup.TrackingTablesSuffix = "_t";
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = false;
var onCreated = false;
localOrchestrator.OnTrackingTableCreating(ttca =>
{
var addingID = $" ALTER TABLE {ttca.TrackingTableName.Schema().Quoted()} ADD internal_id int identity(1,1)";
ttca.Command.CommandText += addingID;
onCreating = true;
});
localOrchestrator.OnTrackingTableCreated(ttca =>
{
onCreated = true;
});
await localOrchestrator.CreateTrackingTableAsync(setup.Tables[0]);
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 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 localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onDropping = false;
var onDropped = false;
localOrchestrator.OnTrackingTableDropping(ttca =>
{
ttca.Cancel = true;
onDropping = true;
});
localOrchestrator.OnTrackingTableDropped(ttca =>
{
onDropped = true;
});
await localOrchestrator.CreateTrackingTableAsync(setup.Tables[0]);
await localOrchestrator.DropTrackingTableAsync(setup.Tables[0]);
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 LocalOrchestrator_EnsureScope_CheckInterceptors()
{
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 onScopeLoading = false;
var onScopeLoaded = false;
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
localOrchestrator.OnScopeLoading(args =>
{
Assert.Equal(SyncStage.ScopeLoading, args.Context.SyncStage);
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);
Assert.Same(args.Connection, args.Transaction.Connection);
onScopeLoading = true;
});
localOrchestrator.OnScopeLoaded(args =>
{
Assert.Equal(SyncStage.ScopeLoaded, args.Context.SyncStage);
Assert.Equal(scopeName, args.Context.ScopeName);
Assert.NotNull(args.ScopeInfo);
Assert.Equal(scopeName, args.ScopeInfo.Name);
Assert.NotNull(args.Connection);
Assert.Null(args.Transaction);
Assert.Equal(ConnectionState.Closed, args.Connection.State);
onScopeLoaded = true;
});
// Check connection and transaction interceptors
BaseOrchestratorTests.AssertConnectionAndTransaction(localOrchestrator, SyncStage.ScopeLoading, SyncStage.ScopeLoaded);
var localScopeInfo = await localOrchestrator.GetClientScopeAsync();
Assert.True(onScopeLoaded);
Assert.True(onScopeLoading);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task BaseOrchestrator_Provision_SchemaCreated_If_SchemaHasColumnsDefinition()
{
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 scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup();
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));
table.PrimaryKeys.Add("ID");
schema.Tables.Add(table);
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
var provision = SyncProvision.Table | SyncProvision.TrackingTable | SyncProvision.StoredProcedures | SyncProvision.Triggers;
await localOrchestrator.ProvisionAsync(schema, provision);
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var tbl = await SqlManagementUtils.GetTableAsync(c, null, "Product", "SalesLT");
var tblName = tbl.Rows[0]["TableName"].ToString();
var schName = tbl.Rows[0]["SchemaName"].ToString();
Assert.Equal(table.TableName, tblName);
Assert.Equal(table.SchemaName, schName);
var cols = await SqlManagementUtils.GetColumnsForTableAsync(c, null, "Product", "SalesLT");
Assert.Equal(3, cols.Rows.Count);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
