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"); }