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 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 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_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 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 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_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_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);
}
private static async Task ProvisionClientManuallyAsync()
{
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncProvider(serverConnectionString);
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Create standard Setup and Options
var setup = new SyncSetup(new string[] { "Address", "Customer", "CustomerAddress" });
var options = new SyncOptions();
// -----------------------------------------------------------------
// 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 local scope that will contains after provisioning, the serialized version of your scope / schema
// - Get the schema from the server side using a RemoteOrchestrator or a WebClientOrchestrator
// - Provision everything locally
// - Save the local scope information
// Create a local orchestrator used to provision everything locally
var localOrchestrator = new LocalOrchestrator(clientProvider, options, setup);
// Because we need the schema from remote side, create a remote orchestrator
var remoteOrchestrator = new RemoteOrchestrator(serverProvider, options, setup);
// Getting the schema from server side
var serverSchema = await remoteOrchestrator.GetSchemaAsync();
// At this point, if you need the schema and you are not able to create a RemoteOrchestrator,
// You can create a WebClientOrchestrator and get the schema as well
// var proxyClientProvider = new WebClientOrchestrator("https://localhost:44369/api/Sync");
// var serverSchema = proxyClientProvider.GetSchemaAsync();
// get the local scope
var clientScope = await localOrchestrator.GetClientScopeAsync();
// Provision everything needed (sp, triggers, tracking tables, AND TABLES)
await localOrchestrator.ProvisionAsync(serverSchema, SyncProvision.StoredProcedures
| SyncProvision.Triggers | SyncProvision.TrackingTable | SyncProvision.Table);
// affect good values
clientScope.Setup = setup;
clientScope.Schema = serverSchema;
// save the client scope
await localOrchestrator.SaveClientScopeAsync(clientScope);
}
public async Task BaseOrchestrator_Provision_SchemaFail_If_SchemaHasColumnsDefinitionButNoPrimaryKey()
{
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));
schema.Tables.Add(table);
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(schema, provision));
Assert.Equal(SyncStage.Provisioning, se.SyncStage);
Assert.Equal(SyncSide.ClientSide, se.Side);
Assert.Equal("MissingPrimaryKeyException", 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);
// Create standard Setup and Options
var setup = new SyncSetup(new string[] { "Address", "Customer", "CustomerAddress" });
var options = new SyncOptions();
// -----------------------------------------------------------------
// 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 schema from the server side using a RemoteOrchestrator or a WebClientOrchestrator
// - Provision everything locally
// Create a local orchestrator used to provision everything locally
var localOrchestrator = new LocalOrchestrator(clientProvider, options, setup);
// Because we need the schema from remote side, create a remote orchestrator
var remoteOrchestrator = new RemoteOrchestrator(serverProvider, options, setup);
// Getting the schema from server side
var serverSchema = await remoteOrchestrator.GetSchemaAsync();
// At this point, if you need the schema and you are not able to create a RemoteOrchestrator,
// You can create a WebClientOrchestrator and get the schema as well
// var proxyClientProvider = new WebClientOrchestrator("https://localhost:44369/api/Sync");
// var serverSchema = proxyClientProvider.GetSchemaAsync();
// Provision everything needed (sp, triggers, tracking tables, AND TABLES)
await localOrchestrator.ProvisionAsync(serverSchema);
}
public async Task Scenario_Using_ExistingClientDatabase_ProvisionDeprovision_WithoutAccessToServerSide(SyncOptions options)
{
// This test works only if we have the same exact provider on both sides
// create client orchestrator that is the same as server
var clientDatabaseName = HelperDatabase.GetRandomName("tcpfilt_cli_");
var clientProvider = this.CreateProvider(this.ServerType, clientDatabaseName);
var client = (clientDatabaseName, Server.ProviderType, Provider : clientProvider);
// create a client schema without seeding
await this.EnsureDatabaseSchemaAndSeedAsync(client, false, UseFallbackSchema);
// Since we don't have access to remote orchestrator,
// we can simulate a server scope
var localOrchestrator = new LocalOrchestrator(client.Provider, options);
// Get the local scope
var localScopeInfo = await localOrchestrator.GetClientScopeInfoAsync();
// getting local scope did not get the schema
var schema = await localOrchestrator.GetSchemaAsync(this.FilterSetup);
// getting local schema from these provider will not fill the schema name for each table
// and we need the exact same name even if it's not used on client
if (client.ProviderType == ProviderType.MySql || client.ProviderType == ProviderType.MariaDB || client.ProviderType == ProviderType.Sqlite)
{
foreach (var table in schema.Tables)
{
var setupTable = this.FilterSetup.Tables.First(t => t.TableName == table.TableName);
table.SchemaName = setupTable.SchemaName;
}
}
// Simulate a server scope
var serverScope = new ServerScopeInfo
{
Name = localScopeInfo.Name,
Schema = schema,
Setup = this.FilterSetup,
Version = localScopeInfo.Version
};
// just check interceptor
var onTableCreatedCount = 0;
localOrchestrator.OnTableCreated(args => onTableCreatedCount++);
// Provision the database with all tracking tables, stored procedures, triggers and scope
var clientScope = await localOrchestrator.ProvisionAsync(serverScope);
//--------------------------
// ASSERTION
//--------------------------
// check if scope table is correctly created
var scopeInfoTableExists = await localOrchestrator.ExistScopeInfoTableAsync(clientScope.Name, DbScopeType.Client);
Assert.True(scopeInfoTableExists);
// get the db manager
foreach (var setupTable in this.FilterSetup.Tables)
{
Assert.True(await localOrchestrator.ExistTrackingTableAsync(clientScope, setupTable.TableName, setupTable.SchemaName));
Assert.True(await localOrchestrator.ExistTriggerAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbTriggerType.Delete));
Assert.True(await localOrchestrator.ExistTriggerAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbTriggerType.Insert));
Assert.True(await localOrchestrator.ExistTriggerAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbTriggerType.Update));
if (client.ProviderType == ProviderType.Sql)
{
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.BulkDeleteRows));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.BulkTableType));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.BulkUpdateRows));
}
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.DeleteMetadata));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.DeleteRow));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.Reset));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectChanges));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectInitializedChanges));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectRow));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.UpdateRow));
// Filters here
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectChangesWithFilters));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectInitializedChangesWithFilters));
}
//localOrchestrator.OnTableProvisioned(null);
//// Deprovision the database with all tracking tables, stored procedures, triggers and scope
await localOrchestrator.DeprovisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers | SyncProvision.ClientScope | SyncProvision.TrackingTable);
// check if scope table is correctly created
scopeInfoTableExists = await localOrchestrator.ExistScopeInfoTableAsync(clientScope.Name, DbScopeType.Client);
Assert.False(scopeInfoTableExists);
// get the db manager
foreach (var setupTable in this.FilterSetup.Tables)
{
Assert.False(await localOrchestrator.ExistTrackingTableAsync(clientScope, setupTable.TableName, setupTable.SchemaName));
Assert.False(await localOrchestrator.ExistTriggerAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbTriggerType.Delete));
Assert.False(await localOrchestrator.ExistTriggerAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbTriggerType.Insert));
Assert.False(await localOrchestrator.ExistTriggerAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbTriggerType.Update));
if (client.ProviderType == ProviderType.Sql)
{
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.BulkDeleteRows));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.BulkTableType));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.BulkUpdateRows));
}
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.DeleteMetadata));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.DeleteRow));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.Reset));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectChanges));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectInitializedChanges));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectRow));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.UpdateRow));
// check filters are deleted
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectChangesWithFilters));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectInitializedChangesWithFilters));
}
}
public async Task BaseOrchestrator_Provision_Check_Interceptors()
{
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();
// Options set here and we must check if TableBuilder has correct version
options.UseBulkOperations = true;
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var onTableProvisioned = false;
var onTableProvisioning = false;
var onDatabaseProvisioned = false;
var onDatabaseProvisioning = false;
var provision = SyncProvision.Table | SyncProvision.TrackingTable | SyncProvision.StoredProcedures | SyncProvision.Triggers;
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
// Assuming GetSchemaAsync() is correct
// make the GetSchema call, before registrating interceptors
var schema = await localOrchestrator.GetSchemaAsync();
localOrchestrator.OnDatabaseProvisioning(args =>
{
Assert.IsType <DatabaseProvisioningArgs>(args);
Assert.Equal(SyncStage.Provisioning, args.Context.SyncStage);
Assert.Equal(scopeName, args.Context.ScopeName);
Assert.NotNull(args.Connection);
Assert.NotNull(args.Transaction);
Assert.Equal(ConnectionState.Open, args.Connection.State);
Assert.Equal(provision, args.Provision);
Assert.Equal(schema, args.Schema);
onDatabaseProvisioning = true;
});
localOrchestrator.OnDatabaseProvisioned(args =>
{
Assert.IsType <DatabaseProvisionedArgs>(args);
Assert.Equal(SyncStage.Provisioned, args.Context.SyncStage);
Assert.Equal(scopeName, args.Context.ScopeName);
Assert.NotNull(args.Connection);
Assert.Null(args.Transaction);
Assert.Equal(ConnectionState.Closed, args.Connection.State);
Assert.Equal(provision, args.Provision);
Assert.Equal(schema, args.Schema);
onDatabaseProvisioned = true;
});
localOrchestrator.OnTableProvisioning(args =>
{
Assert.IsType <TableProvisioningArgs>(args);
Assert.Equal(SyncStage.Provisioning, args.Context.SyncStage);
Assert.Equal(scopeName, args.Context.ScopeName);
Assert.NotNull(args.Connection);
Assert.NotNull(args.Transaction);
Assert.Equal(ConnectionState.Open, args.Connection.State);
Assert.True(args.TableBuilder.UseBulkProcedures);
Assert.False(args.TableBuilder.UseChangeTracking);
Assert.NotNull(args.TableBuilder.TableDescription);
Assert.Equal("SalesLT.Product", args.TableBuilder.TableDescription.GetFullName());
onTableProvisioning = true;
});
localOrchestrator.OnTableProvisioned(args =>
{
Assert.IsType <TableProvisionedArgs>(args);
// We are still provisioning the tables
Assert.Equal(SyncStage.Provisioning, args.Context.SyncStage);
Assert.Equal(scopeName, args.Context.ScopeName);
Assert.NotNull(args.Connection);
Assert.NotNull(args.Transaction);
Assert.Equal(ConnectionState.Open, args.Connection.State);
Assert.NotNull(args.SchemaTable);
Assert.Equal("SalesLT.Product", args.SchemaTable.GetFullName());
onTableProvisioned = true;
});
AssertConnectionAndTransaction(localOrchestrator, SyncStage.Provisioning, SyncStage.Provisioned);
await localOrchestrator.ProvisionAsync(schema, provision);
Assert.Equal(SyncStage.Provisioned, localOrchestrator.GetContext().SyncStage);
Assert.True(onTableProvisioned);
Assert.True(onTableProvisioning);
Assert.True(onDatabaseProvisioned);
Assert.True(onDatabaseProvisioning);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public virtual async Task Scenario_Adding_OneColumn_OneTable_On_SameScope_Using_Interceptor()
{
// create a server schema with seeding
await this.EnsureDatabaseSchemaAndSeedAsync(this.Server, true, UseFallbackSchema);
// create empty client databases
foreach (var client in this.Clients)
{
await this.CreateDatabaseAsync(client.ProviderType, client.DatabaseName, true);
}
// --------------------------
// Step 1: Create a default scope and Sync clients
// Note we are not including the [Attribute With Space] column
var productCategoryTableName = this.Server.ProviderType == ProviderType.Sql ? "SalesLT.ProductCategory" : "ProductCategory";
var productTableName = this.Server.ProviderType == ProviderType.Sql ? "SalesLT.Product" : "Product";
var setup = new SyncSetup(new string[] { productCategoryTableName });
setup.Tables[productCategoryTableName].Columns.AddRange(
new string[] { "ProductCategoryId", "Name", "rowguid", "ModifiedDate" });
int productCategoryRowsCount = 0;
using (var readCtx = new AdventureWorksContext(Server, this.UseFallbackSchema))
{
productCategoryRowsCount = readCtx.ProductCategory.AsNoTracking().Count();
}
// First sync to initialiaze client database, create table and fill product categories
foreach (var client in this.Clients)
{
var agent = new SyncAgent(client.Provider, Server.Provider);
var r = await agent.SynchronizeAsync("v1", setup);
Assert.Equal(productCategoryRowsCount, r.TotalChangesDownloaded);
}
var remoteOrchestrator = new RemoteOrchestrator(Server.Provider);
// Editing the current scope on the server with this new column and a new table
setup.Tables.Add(productTableName);
setup.Tables[productCategoryTableName].Columns.Clear();
setup.Tables[productCategoryTableName].Columns.AddRange("ProductCategoryId", "Name", "rowguid", "ModifiedDate", "Attribute With Space");
// overwrite the setup
var serverScope = await remoteOrchestrator.ProvisionAsync("v1", setup, overwrite : true);
if (Server.ProviderType == ProviderType.MySql || Server.ProviderType == ProviderType.MariaDB)
{
var connection = Server.Provider.CreateConnection();
// tracking https://github.com/mysql-net/MySqlConnector/issues/924
MySqlConnection.ClearPool(connection as MySqlConnection);
}
// Create a server new ProductCategory with the new column value filled
// and a Product related
var productId = Guid.NewGuid();
var productName = HelperDatabase.GetRandomName();
var productNumber = productName.ToUpperInvariant().Substring(0, 10);
var productCategoryName = HelperDatabase.GetRandomName();
var productCategoryId = productCategoryName.ToUpperInvariant().Substring(0, 6);
var newAttributeWithSpaceValue = HelperDatabase.GetRandomName();
using (var ctx = new AdventureWorksContext(Server, this.UseFallbackSchema))
{
var pc = new ProductCategory
{
ProductCategoryId = productCategoryId,
Name = productCategoryName,
AttributeWithSpace = newAttributeWithSpaceValue
};
ctx.ProductCategory.Add(pc);
var product = new Product {
ProductId = productId, Name = productName, ProductNumber = productNumber, ProductCategoryId = productCategoryId
};
ctx.Product.Add(product);
await ctx.SaveChangesAsync();
}
foreach (var client in this.Clients)
{
var commandText = client.ProviderType switch
{
ProviderType.Sql => $@"ALTER TABLE {productCategoryTableName} ADD [Attribute With Space] nvarchar(250) NULL;",
ProviderType.Sqlite => @"ALTER TABLE ProductCategory ADD [Attribute With Space] text NULL;",
ProviderType.MySql => @"ALTER TABLE `ProductCategory` ADD `Attribute With Space` nvarchar(250) NULL;",
ProviderType.MariaDB => @"ALTER TABLE `ProductCategory` ADD `Attribute With Space` nvarchar(250) NULL;",
_ => throw new NotImplementedException()
};
var connection = client.Provider.CreateConnection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = commandText;
command.Connection = connection;
await command.ExecuteNonQueryAsync();
connection.Close();
if (client.ProviderType == ProviderType.MySql || client.ProviderType == ProviderType.MariaDB)
{
// tracking https://github.com/mysql-net/MySqlConnector/issues/924
MySqlConnection.ClearPool(connection as MySqlConnection);
}
// Creating a new table is quite easier since DMS can do it for us
// Get scope from server (v1 because it contains the new table schema)
// we already have it, but you cand call GetServerScopInfoAsync("v1") if needed
// var serverScope = await remoteOrchestrator.GetServerScopeInfoAsync("v1");
var localOrchestrator = new LocalOrchestrator(client.Provider);
await localOrchestrator.CreateTableAsync(serverScope, "Product", "SalesLT");
// We are ready to sync this new scope !
var agent = new SyncAgent(client.Provider, Server.Provider);
agent.LocalOrchestrator.OnConflictingSetup(async args =>
{
if (args.ServerScopeInfo != null)
{
args.ClientScopeInfo = await localOrchestrator.ProvisionAsync(args.ServerScopeInfo, overwrite: true);
args.Action = ConflictingSetupAction.Continue;
return;
}
args.Action = ConflictingSetupAction.Abort;
});
var r = await agent.SynchronizeAsync("v1");
Assert.Equal(2, r.TotalChangesDownloaded);
}
}
}
public async Task LocalOrchestrator_MultipleScopes_Check_Metadatas_Are_Deleted()
{
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 localOrchestrator = new LocalOrchestrator(sqlProvider, options);
var setup = new SyncSetup(this.Tables);
var setup2 = new SyncSetup(this.Tables);
setup2.Filters.Add("Customer", "EmployeeID");
var schema = await localOrchestrator.GetSchemaAsync(setup);
var localScopeInfo1 = await localOrchestrator.GetClientScopeInfoAsync();
var localScopeInfo2 = await localOrchestrator.GetClientScopeInfoAsync("A");
var serverScope1 = new ServerScopeInfo
{
Name = localScopeInfo1.Name,
Schema = schema,
Setup = setup,
Version = localScopeInfo1.Version
};
var serverScope2 = new ServerScopeInfo
{
Name = localScopeInfo2.Name,
Schema = schema,
Setup = setup2,
Version = localScopeInfo2.Version
};
// Provision two scopes (already tested in previous test)
localScopeInfo1 = await localOrchestrator.ProvisionAsync(serverScope1);
localScopeInfo2 = await localOrchestrator.ProvisionAsync(serverScope2);
Assert.NotNull(localScopeInfo1.Setup);
Assert.NotNull(localScopeInfo1.Schema);
Assert.NotNull(localScopeInfo2.Setup);
Assert.NotNull(localScopeInfo2.Schema);
// Deprovision
await localOrchestrator.DeprovisionAsync("A");
foreach (var table in localScopeInfo1.Setup.Tables)
{
var tableName = table.TableName;
var schemaName = table.SchemaName;
foreach (var objectSpType in Enum.GetValues(typeof(Builders.DbStoredProcedureType)))
{
var spType = (Builders.DbStoredProcedureType)objectSpType;
var exists1 = await localOrchestrator.ExistStoredProcedureAsync(
localScopeInfo1, tableName, schemaName, spType);
var exists2 = await localOrchestrator.ExistStoredProcedureAsync(
localScopeInfo2, tableName, schemaName, spType);
if (spType == Builders.DbStoredProcedureType.SelectChangesWithFilters ||
spType == Builders.DbStoredProcedureType.SelectInitializedChangesWithFilters)
{
Assert.False(exists1);
}
else
{
Assert.True(exists1);
}
Assert.False(exists2);
}
foreach (var objectSpType in Enum.GetValues(typeof(Builders.DbTriggerType)))
{
var trigType = (Builders.DbTriggerType)objectSpType;
var existsTrig1 = await localOrchestrator.ExistTriggerAsync(localScopeInfo1, tableName, schemaName, trigType);
var existsTrig2 = await localOrchestrator.ExistTriggerAsync(localScopeInfo2, tableName, schemaName, trigType);
Assert.False(existsTrig1);
Assert.False(existsTrig2);
}
var trackTableExists1 = await localOrchestrator.ExistTrackingTableAsync(localScopeInfo1, tableName, schemaName);
var trackTableExists2 = await localOrchestrator.ExistTrackingTableAsync(localScopeInfo2, tableName, schemaName);
// Tracking table are still existing for others scopes
Assert.True(trackTableExists1);
Assert.True(trackTableExists2);
}
// Deprovision
await localOrchestrator.DeprovisionAsync();
foreach (var table in localScopeInfo1.Setup.Tables)
{
var tableName = table.TableName;
var schemaName = table.SchemaName;
foreach (var objectSpType in Enum.GetValues(typeof(Builders.DbStoredProcedureType)))
{
var spType = (Builders.DbStoredProcedureType)objectSpType;
var exists1 = await localOrchestrator.ExistStoredProcedureAsync(
localScopeInfo1, tableName, schemaName, spType);
var exists2 = await localOrchestrator.ExistStoredProcedureAsync(
localScopeInfo2, tableName, schemaName, spType);
Assert.False(exists1);
Assert.False(exists2);
}
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
private static async Task SynchronizeThenDeprovisionThenProvisionAsync()
{
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(serverDbName));
var clientProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(clientDbName));
// Create standard Setup and Options
var setup = new SyncSetup(new string[] { "Address", "Customer", "CustomerAddress" });
var options = new SyncOptions();
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, options, setup);
// Using the Progress pattern to handle progession during the synchronization
var progress = new SynchronousProgress <ProgressArgs>(s => Console.WriteLine($"{s.Context.SyncStage}:\t{s.Message}"));
// First sync to have a starting point
var s1 = await agent.SynchronizeAsync(progress);
Console.WriteLine(s1);
// -----------------------------------------------------------------
// Migrating a table by adding a new column
// -----------------------------------------------------------------
// Adding a new column called CreatedDate to Address table, on the server, and on the client.
await AddNewColumnToAddressAsync(serverProvider.CreateConnection());
await AddNewColumnToAddressAsync(clientProvider.CreateConnection());
// -----------------------------------------------------------------
// Server side
// -----------------------------------------------------------------
// Creating a setup regarding only the table Address
var setupAddress = new SyncSetup(new string[] { "Address" });
// Create a server orchestrator used to Deprovision and Provision only table Address
var remoteOrchestrator = new RemoteOrchestrator(serverProvider, options, setupAddress);
// Unprovision the Address triggers / stored proc.
// We can conserve the Address tracking table, since we just add a column,
// that is not a primary key used in the tracking table
// That way, we are preserving historical data
await remoteOrchestrator.DeprovisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// Provision the Address triggers / stored proc again,
// This provision method will fetch the address schema from the database,
// so it will contains all the columns, including the new Address column added
await remoteOrchestrator.ProvisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// Now we need the full setup to get the full schema.
// Setup includes [Address] [Customer] and [CustomerAddress]
remoteOrchestrator.Setup = setup;
var newSchema = await remoteOrchestrator.GetSchemaAsync();
// Now we need to save this new schema to the serverscope table
// get the server scope again
var serverScope = await remoteOrchestrator.GetServerScopeAsync();
// affect good values
serverScope.Setup = setup;
serverScope.Schema = newSchema;
// save it
await remoteOrchestrator.WriteServerScopeAsync(serverScope);
// -----------------------------------------------------------------
// Client side
// -----------------------------------------------------------------
// Now go for local orchestrator
var localOrchestrator = new LocalOrchestrator(clientProvider, options, setupAddress);
// Unprovision the Address triggers / stored proc. We can conserve tracking table, since we just add a column, that is not a primary key used in the tracking table
// In this case, we will
await localOrchestrator.DeprovisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// Provision the Address triggers / stored proc again,
// This provision method will fetch the address schema from the database, so it will contains all the columns, including the new one added
await localOrchestrator.ProvisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// Now we need to save this to clientscope
// get the server scope again
var clientScope = await localOrchestrator.GetClientScopeAsync();
// At this point, if you need the schema and you are not able to create a RemoteOrchestrator,
// You can create a WebClientOrchestrator and get the schema as well
// var proxyClientProvider = new WebClientOrchestrator("https://localhost:44369/api/Sync");
// var newSchema = proxyClientProvider.GetSchemaAsync();
// affect good values
clientScope.Setup = setup;
clientScope.Schema = newSchema;
// save it
await localOrchestrator.WriteClientScopeAsync(clientScope);
// Now test a new sync, everything should work as expected.
do
{
// Console.Clear();
Console.WriteLine("Sync Start");
try
{
var s2 = await agent.SynchronizeAsync();
// Write results
Console.WriteLine(s2);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
public virtual async Task Scenario_Adding_OneColumn_OneTable_With_TwoScopes()
{
// create a server schema with seeding
await this.EnsureDatabaseSchemaAndSeedAsync(this.Server, true, UseFallbackSchema);
// create empty client databases
foreach (var client in this.Clients)
{
await this.CreateDatabaseAsync(client.ProviderType, client.DatabaseName, true);
}
var productCategoryTableName = this.Server.ProviderType == ProviderType.Sql ? "SalesLT.ProductCategory" : "ProductCategory";
var productTableName = this.Server.ProviderType == ProviderType.Sql ? "SalesLT.Product" : "Product";
// --------------------------
// Step 1: Create a default scope and Sync clients
// Note we are not including the [Attribute With Space] column
var setup = new SyncSetup(new string[] { productCategoryTableName });
setup.Tables[productCategoryTableName].Columns.AddRange(
new string[] { "ProductCategoryId", "Name", "rowguid", "ModifiedDate" });
// configure server orchestrator
this.Kestrell.AddSyncServer(this.Server.Provider.GetType(), this.Server.Provider.ConnectionString, SyncOptions.DefaultScopeName, setup);
var serviceUri = this.Kestrell.Run();
int productCategoryRowsCount = 0;
using (var readCtx = new AdventureWorksContext(Server, this.UseFallbackSchema))
{
productCategoryRowsCount = readCtx.ProductCategory.AsNoTracking().Count();
}
// First sync to initialiaze client database, create table and fill product categories
foreach (var client in this.Clients)
{
var webServerProxyOrchestrator = new WebRemoteOrchestrator(serviceUri);
var agent = new SyncAgent(client.Provider, webServerProxyOrchestrator);
var r = await agent.SynchronizeAsync();
Assert.Equal(productCategoryRowsCount, r.TotalChangesDownloaded);
}
await this.Kestrell.StopAsync();
// On server side, playing around with a direct RemoteOrchestrator
var remoteOrchestrator = new RemoteOrchestrator(Server.Provider);
// Adding a new scope on the server with this new column and a new table
// Creating a new scope called "V1" on server
var setupV1 = new SyncSetup(new string[] { productCategoryTableName, productTableName });
setupV1.Tables[productCategoryTableName].Columns.AddRange(
new string[] { "ProductCategoryId", "Name", "rowguid", "ModifiedDate", "Attribute With Space" });
var serverScope = await remoteOrchestrator.ProvisionAsync("v1", setupV1);
// Create a server new ProductCategory with the new column value filled
// and a Product related
var productId = Guid.NewGuid();
var productName = HelperDatabase.GetRandomName();
var productNumber = productName.ToUpperInvariant().Substring(0, 10);
var productCategoryName = HelperDatabase.GetRandomName();
var productCategoryId = productCategoryName.ToUpperInvariant().Substring(0, 6);
var newAttributeWithSpaceValue = HelperDatabase.GetRandomName();
using (var ctx = new AdventureWorksContext(Server, this.UseFallbackSchema))
{
var pc = new ProductCategory
{
ProductCategoryId = productCategoryId,
Name = productCategoryName,
AttributeWithSpace = newAttributeWithSpaceValue
};
ctx.ProductCategory.Add(pc);
var product = new Product {
ProductId = productId, Name = productName, ProductNumber = productNumber, ProductCategoryId = productCategoryId
};
ctx.Product.Add(product);
await ctx.SaveChangesAsync();
}
// configure server orchestrator
this.Kestrell.AddSyncServer(this.Server.Provider.GetType(), this.Server.Provider.ConnectionString, SyncOptions.DefaultScopeName, setup);
this.Kestrell.AddSyncServer(this.Server.Provider.GetType(), this.Server.Provider.ConnectionString, SyncOptions.DefaultScopeName, setupV1);
serviceUri = this.Kestrell.Run();
foreach (var client in this.Clients)
{
var commandText = client.ProviderType switch
{
ProviderType.Sql => $@"ALTER TABLE {productCategoryTableName} ADD [Attribute With Space] nvarchar(250) NULL;",
ProviderType.Sqlite => @"ALTER TABLE ProductCategory ADD [Attribute With Space] text NULL;",
ProviderType.MySql => @"ALTER TABLE `ProductCategory` ADD `Attribute With Space` nvarchar(250) NULL;",
ProviderType.MariaDB => @"ALTER TABLE `ProductCategory` ADD `Attribute With Space` nvarchar(250) NULL;",
_ => throw new NotImplementedException()
};
var connection = client.Provider.CreateConnection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = commandText;
command.Connection = connection;
await command.ExecuteNonQueryAsync();
connection.Close();
// Get scope from server (v1 because it contains the new table schema)
var webServerProxyOrchestrator = new WebRemoteOrchestrator(serviceUri);
serverScope = await webServerProxyOrchestrator.GetServerScopeInfoAsync("v1");
// Creating a new table is quite easier since DMS can do it for us
var localOrchestrator = new LocalOrchestrator(client.Provider);
if (this.Server.ProviderType == ProviderType.Sql)
{
await localOrchestrator.CreateTableAsync(serverScope, "Product", "SalesLT");
}
else
{
await localOrchestrator.CreateTableAsync(serverScope, "Product");
}
// Once created we can provision the new scope, thanks to the serverScope instance we already have
var clientScopeV1 = await localOrchestrator.ProvisionAsync(serverScope);
// IF we launch synchronize on this new scope, it will get all the rows from the server
// We are making a shadow copy of previous scope to get the last synchronization metadata
var oldClientScopeInfo = await localOrchestrator.GetClientScopeInfoAsync();
clientScopeV1.ShadowScope(oldClientScopeInfo);
await localOrchestrator.SaveClientScopeInfoAsync(clientScopeV1);
// We are ready to sync this new scope !
var agent = new SyncAgent(client.Provider, Server.Provider);
var r = await agent.SynchronizeAsync("v1");
Assert.Equal(2, r.TotalChangesDownloaded);
}
}
public virtual async Task Scenario_Adding_OneColumn_OneTable_With_TwoScopes_OneClient_Still_OnOldScope_OneClient_OnNewScope()
{
// create a server schema with seeding
await this.EnsureDatabaseSchemaAndSeedAsync(this.Server, true, UseFallbackSchema);
// create 2 client databases
// First one will update to new scope
// Second one will stay on last scope
// For this purpose, using two sqlite databases
var client1DatabaseName = HelperDatabase.GetRandomName();
var client2DatabaseName = HelperDatabase.GetRandomName();
// Create the two databases
await this.CreateDatabaseAsync(ProviderType.Sqlite, client1DatabaseName, true);
await this.CreateDatabaseAsync(ProviderType.Sqlite, client2DatabaseName, true);
var client1provider = new SqliteSyncProvider(HelperDatabase.GetSqliteFilePath(client1DatabaseName));
var client2provider = new SqliteSyncProvider(HelperDatabase.GetSqliteFilePath(client2DatabaseName));
// --------------------------
// Step 1: Create a default scope and Sync clients
// Note we are not including the [Attribute With Space] column
var productCategoryTableName = this.Server.ProviderType == ProviderType.Sql ? "SalesLT.ProductCategory" : "ProductCategory";
var productTableName = this.Server.ProviderType == ProviderType.Sql ? "SalesLT.Product" : "Product";
var setup = new SyncSetup(new string[] { productCategoryTableName });
setup.Tables[productCategoryTableName].Columns.AddRange(
new string[] { "ProductCategoryId", "Name", "rowguid", "ModifiedDate" });
// Counting product categories & products
int productCategoryRowsCount = 0;
int productsCount = 0;
using (var readCtx = new AdventureWorksContext(Server, this.UseFallbackSchema))
{
productCategoryRowsCount = readCtx.ProductCategory.AsNoTracking().Count();
productsCount = readCtx.Product.AsNoTracking().Count();
}
var agent1 = new SyncAgent(client1provider, Server.Provider);
var r1 = await agent1.SynchronizeAsync(setup);
Assert.Equal(productCategoryRowsCount, r1.TotalChangesDownloaded);
var agent2 = new SyncAgent(client2provider, Server.Provider);
var r2 = await agent2.SynchronizeAsync(setup);
Assert.Equal(productCategoryRowsCount, r2.TotalChangesDownloaded);
// From now, the client 1 will upgrade to new scope
// the client 2 will remain on old scope
// Adding a new scope
var remoteOrchestrator = agent1.RemoteOrchestrator; // agent2.RemoteOrchestrator is the same, btw
// Adding a new scope on the server with this new column and a new table
// Creating a new scope called "V1" on server
var setupV1 = new SyncSetup(new string[] { productCategoryTableName, productTableName });
setupV1.Tables[productCategoryTableName].Columns.AddRange(
new string[] { "ProductCategoryId", "Name", "rowguid", "ModifiedDate", "Attribute With Space" });
var serverScope = await remoteOrchestrator.ProvisionAsync("v1", setupV1);
// Create a server new ProductCategory with the new column value filled
// and a Product related
var productId = Guid.NewGuid();
var productName = HelperDatabase.GetRandomName();
var productNumber = productName.ToUpperInvariant().Substring(0, 10);
var productCategoryName = HelperDatabase.GetRandomName();
var productCategoryId = productCategoryName.ToUpperInvariant().Substring(0, 6);
var newAttributeWithSpaceValue = HelperDatabase.GetRandomName();
using (var ctx = new AdventureWorksContext(Server, this.UseFallbackSchema))
{
var pc = new ProductCategory
{
ProductCategoryId = productCategoryId,
Name = productCategoryName,
AttributeWithSpace = newAttributeWithSpaceValue
};
ctx.ProductCategory.Add(pc);
var product = new Product {
ProductId = productId, Name = productName, ProductNumber = productNumber, ProductCategoryId = productCategoryId
};
ctx.Product.Add(product);
await ctx.SaveChangesAsync();
}
// Add this new column on the client 1, with default value as null
var connection = client1provider.CreateConnection();
connection.Open();
var command = connection.CreateCommand();
command.CommandText = @"ALTER TABLE ProductCategory ADD [Attribute With Space] text NULL;";
command.Connection = connection;
await command.ExecuteNonQueryAsync();
connection.Close();
// Creating a new table is quite easier since DMS can do it for us
// Get scope from server (v1 because it contains the new table schema)
// we already have it, but you cand call GetServerScopInfoAsync("v1") if needed
// var serverScope = await remoteOrchestrator.GetServerScopeInfoAsync("v1");
var localOrchestrator = new LocalOrchestrator(client1provider);
if (this.Server.ProviderType == ProviderType.Sql)
{
await localOrchestrator.CreateTableAsync(serverScope, "Product", "SalesLT");
}
else
{
await localOrchestrator.CreateTableAsync(serverScope, "Product");
}
// Once created we can provision the new scope, thanks to the serverScope instance we already have
var clientScopeV1 = await localOrchestrator.ProvisionAsync(serverScope);
// IF we launch synchronize on this new scope, it will get all the rows from the server
// We are making a shadow copy of previous scope to get the last synchronization metadata
var oldClientScopeInfo = await localOrchestrator.GetClientScopeInfoAsync();
clientScopeV1.ShadowScope(oldClientScopeInfo);
await localOrchestrator.SaveClientScopeInfoAsync(clientScopeV1);
// We are ready to sync this new scope !
// we still can use the old agent, since it's already configured with correct providers
// just be sure to set the correct scope
r1 = await agent1.SynchronizeAsync("v1");
Assert.Equal(2, r1.TotalChangesDownloaded);
// make a sync on old scope for client 2
r2 = await agent2.SynchronizeAsync();
Assert.Equal(1, r2.TotalChangesDownloaded);
// now check values on each client
using (var ctx1 = new AdventureWorksContext((client1DatabaseName, ProviderType.Sqlite, client1provider), false))
{
var producCategory1 = ctx1.ProductCategory.First(pc => pc.ProductCategoryId == productCategoryId);
Assert.Equal(newAttributeWithSpaceValue, producCategory1.AttributeWithSpace);
}
using (var ctx2 = new AdventureWorksContext((client2DatabaseName, ProviderType.Sqlite, client2provider), false))
{
var exc = Assert.ThrowsAny <Microsoft.Data.Sqlite.SqliteException>(() => ctx2.ProductCategory.First(pc => pc.ProductCategoryId == productCategoryId));
Assert.Contains("no such column", exc.Message);
}
// Assuming we want to migrate the client 2 now
var serverScope2 = await agent2.RemoteOrchestrator.GetServerScopeInfoAsync();
// Create the new table locally
if (this.Server.ProviderType == ProviderType.Sql)
{
await localOrchestrator.CreateTableAsync(serverScope, "Product", "SalesLT");
}
else
{
await localOrchestrator.CreateTableAsync(serverScope, "Product");
}
// Add this new column on the client 1, with default value as null
connection = client2provider.CreateConnection();
connection.Open();
command = connection.CreateCommand();
command.CommandText = @"ALTER TABLE ProductCategory ADD [Attribute With Space] text NULL;";
command.Connection = connection;
await command.ExecuteNonQueryAsync();
connection.Close();
// Don't bother to ShadowCopy metadata, since we are doing a reinit
// Just Provision
var clientScope2 = await agent2.LocalOrchestrator.ProvisionAsync(serverScope2);
// Sync
r2 = await agent2.SynchronizeAsync("v1", SyncType.Reinitialize);
using (var readCtx = new AdventureWorksContext(Server, this.UseFallbackSchema))
{
productCategoryRowsCount = readCtx.ProductCategory.AsNoTracking().Count();
productsCount = readCtx.Product.AsNoTracking().Count();
}
Assert.Equal((productCategoryRowsCount + productsCount), r2.TotalChangesDownloaded);
}
private static async Task SynchronizeThenDeprovisionThenProvisionAsync()
{
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncProvider(serverConnectionString);
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Create standard Setup and Options
var setup = new SyncSetup(new string[] { "Address", "Customer", "CustomerAddress" });
var options = new SyncOptions();
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, options, setup);
// Using the Progress pattern to handle progession during the synchronization
var progress = new SynchronousProgress <ProgressArgs>(args => Console.WriteLine($"{args.ProgressPercentage:p}:\t{args.Message}"));
// First sync to have a starting point
var s1 = await agent.SynchronizeAsync(progress);
Console.WriteLine(s1);
// -----------------------------------------------------------------
// Migrating a table by adding a new column
// -----------------------------------------------------------------
// Adding a new column called CreatedDate to Address table, on the server, and on the client.
await Helper.AddNewColumnToAddressAsync(serverProvider.CreateConnection());
await Helper.AddNewColumnToAddressAsync(clientProvider.CreateConnection());
// -----------------------------------------------------------------
// Server side
// -----------------------------------------------------------------
// Creating a setup regarding only the table Address
var setupAddress = new SyncSetup(new string[] { "Address" });
// Create a server orchestrator used to Deprovision and Provision only table Address
var remoteOrchestrator = new RemoteOrchestrator(serverProvider, options, setupAddress);
// Unprovision the old Address triggers / stored proc.
// We can conserve the Address tracking table, since we just add a column,
// that is not a primary key used in the tracking table
// That way, we are preserving historical data
await remoteOrchestrator.DeprovisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// Provision the new Address triggers / stored proc again,
// This provision method will fetch the address schema from the database,
// so it will contains all the columns, including the new Address column added
await remoteOrchestrator.ProvisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// -----------------------------------------------------------------
// Client side
// -----------------------------------------------------------------
// Now go for local orchestrator
var localOrchestrator = new LocalOrchestrator(clientProvider, options, setupAddress);
// Unprovision the Address triggers / stored proc. We can conserve tracking table, since we just add a column, that is not a primary key used in the tracking table
// In this case, we will
await localOrchestrator.DeprovisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// Provision the Address triggers / stored proc again,
// This provision method will fetch the address schema from the database, so it will contains all the columns, including the new one added
await localOrchestrator.ProvisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers);
// Now test a new sync, everything should work as expected.
do
{
// Console.Clear();
Console.WriteLine("Sync Start");
try
{
var s2 = await agent.SynchronizeAsync();
// Write results
Console.WriteLine(s2);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}