/// <summary>
/// Create the tables in each database
/// Add some datas in each database
/// Performs an optional DeprovisionAsync, to be sure we are starting from scratch
/// </summary>
private static async Task SetupDatabasesAsync(SqlSyncProvider serverProvider, SqliteSyncProvider clientProvider)
{
// Add some datas in both
var serverConnection = serverProvider.CreateConnection();
await Helper.CreateSqlServerServiceTicketsTableAsync(serverConnection);
var clientConnection = clientProvider.CreateConnection();
await Helper.CreateSqliteServiceTicketsTableAsync(clientConnection);
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider);
// Be sure we don't have an already existing sync setup. (from previous run)
await agent.LocalOrchestrator.DropAllAsync();
await agent.RemoteOrchestrator.DropAllAsync();
// Be sure we don't have existing rows (from previous run)
await Helper.DropRowsAsync(serverConnection);
await Helper.DropRowsAsync(clientConnection);
// Add rows
await Helper.AddRowsAsync(serverConnection);
await Helper.AddRowsAsync(clientConnection);
}
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 MigrateClientsUsingMultiScopesAsync()
{
// Create the server Sync provider
var serverProvider = new SqlSyncProvider(serverConnectionString);
// Create 2 clients. First will migrate, 2nd will stay without new column
var client1Provider = new SqlSyncProvider(clientConnectionString);
var databaseName = $"{Path.GetRandomFileName().Replace(".", "").ToLowerInvariant()}.db";
var client2Provider = new SqliteSyncProvider(databaseName);
// Create standard Setup
var setup = new SyncSetup("Address", "Customer", "CustomerAddress");
// Creating agents that will handle all the process
var agent1 = new SyncAgent(client1Provider, serverProvider);
var agent2 = new SyncAgent(client2Provider, serverProvider);
// 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
// To make a full example, we are going to use differente scope name (v0, v1)
// v0 is the initial database
// v1 will contains the new column in the Address table
var s1 = await agent1.SynchronizeAsync("v0", setup, progress);
Console.WriteLine("Initial Sync on Sql Server Client 1");
Console.WriteLine(s1);
var s2 = await agent2.SynchronizeAsync("v0", setup, progress);
Console.WriteLine("Initial Sync on Sqlite Client 2");
Console.WriteLine(s2);
// -----------------------------------------------------------------
// Migrating a table by adding a new column
// -----------------------------------------------------------------
// Adding a new column called CreatedDate to Address table, on the server
await Helper.AddNewColumnToAddressAsync(new SqlConnection(serverConnectionString));
Console.WriteLine("Column added on server");
// -----------------------------------------------------------------
// Server side
// -----------------------------------------------------------------
// Creating a new setup with the same tables
// We are going to provision a new scope (v1)
// Since this scope is not existing yet, it will force DMS to refresh the schema and
// get the new column
var setupAddress = new SyncSetup("Address", "Customer", "CustomerAddress");
// Create a server orchestrator used to Deprovision and Provision only table Address
var remoteOrchestrator = new RemoteOrchestrator(serverProvider);
// Provision everything again for this new scope v1,
// This provision method will fetch the address schema from the database,
// since the new scope name is not existing yet
// so it will contains all the columns, including the new Address column added
await remoteOrchestrator.ProvisionAsync("v1", setupAddress, progress : progress);
Console.WriteLine("Server migration with new column CreatedDate done.");
// At this point, server database has two scopes:
// v0 : first scope with Address table without the new column
// v1 : second scope with Address table with the new column CreatedDate
// Take a look at the database in SQL management studio and see differences in stored proc
// Now add a row on the server (with the new column)
var addressId = await Helper.InsertOneAddressWithNewColumnAsync(new SqlConnection(serverConnectionString));
Console.WriteLine($"New address row added with pk {addressId}");
// -----------------------------------------------------------------
// SQlite Client will stay on old schema (without the new CreatedDate column)
// -----------------------------------------------------------------
// First of all, we are still able to sync the local database without having to migrate the client
// allows old clients that do not have the new column, to continue sync normally
// these old clients will continue to sync on the v0 scope
var s3 = await agent2.SynchronizeAsync("v0", setup, progress : progress);
Console.WriteLine($"Sqlite not migrated, doing a sync on first scope v0:");
Console.WriteLine(s3);
// If we get the row from the client, we have the new row inserted on server,
// but without the new column
var client2row = await Helper.GetLastAddressRowAsync(client2Provider.CreateConnection(), addressId);
Console.WriteLine(client2row);
// -----------------------------------------------------------------
// SQL Server Client will add the column and will sync on the new scope (with the new CreatedDate column)
// -----------------------------------------------------------------
// Now we are going to upgrade the client 1
// adding the column to the client
await Helper.AddNewColumnToAddressAsync(new SqlConnection(clientConnectionString));
Console.WriteLine("Sql Server client1 migration with new column CreatedDate done.");
// Provision client with the new the V1 scope
// Getting the scope from server and apply it locally
var serverScope = await agent1.RemoteOrchestrator.GetServerScopeInfoAsync("v1", progress : progress);
var v1clientScope = await agent1.LocalOrchestrator.ProvisionAsync(serverScope, progress : progress);
Console.WriteLine("Sql Server client1 Provision done.");
// if you look the stored procs on your local sql database
// you will that you have the two scopes (v0 and v1)
// TRICKY PART
/*
* The scope v1 is new.
* If we sync now, since v1 is new, we are going to sync all the rows from start
* What we want is to sync from the last point we sync the old v0 scope
* That's why we are shadowing the metadata info from v0 into v1
*/
var v0clientScope = await agent1.LocalOrchestrator.GetClientScopeInfoAsync("v0");
v1clientScope.ShadowScope(v0clientScope);
v1clientScope = await agent1.LocalOrchestrator.SaveClientScopeInfoAsync(v1clientScope);
// Now test a new sync, on this new scope v1
var s4 = await agent1.SynchronizeAsync("v1", progress : progress);
Console.WriteLine($"Sql Server client1 migrated, doing a sync on second scope v1:");
Console.WriteLine(s4);
// If we get the client row from the client database, it should contains the value
var client1row = await Helper.GetLastAddressRowAsync(new SqlConnection(clientConnectionString), addressId);
Console.WriteLine(client1row);
// OPTIONAL
// -----------------------------------------------------------------
// On this new client, migrated, we no longer need the v0 scope
// we can deprovision it
await agent1.LocalOrchestrator.DeprovisionAsync("v0", SyncProvision.StoredProcedures, progress : progress);
await agent1.LocalOrchestrator.DeleteClientScopeInfoAsync(v0clientScope, progress : progress);
Console.WriteLine($"Deprovision of old scope v0 done on Sql Server client1");
// -----------------------------------------------------------------
// SQLite Client will eventually migrate to v1
// -----------------------------------------------------------------
// It's time to migrate the sqlite client
// Adding the column to the SQLite client
await Helper.AddNewColumnToAddressAsync(client2Provider.CreateConnection());
Console.WriteLine($"Column eventually added to Sqlite client2");
// Provision SQLite client with the new the V1 scope
var v1client2Scope = await agent2.LocalOrchestrator.ProvisionAsync(serverScope, progress : progress);
Console.WriteLine($"Provision v1 done on SQLite client2");
// ShadowScope old scope to new scope
var v0client2Scope = await agent2.LocalOrchestrator.GetClientScopeInfoAsync("v0");
v1client2Scope.ShadowScope(v0client2Scope);
v1client2Scope = await agent2.LocalOrchestrator.SaveClientScopeInfoAsync(v1client2Scope);
// let's try to sync firstly
// Now test a new sync, on this new scope v1
// Obviously, we don't have anything from the server
var s5 = await agent2.SynchronizeAsync("v1", progress : progress);
Console.WriteLine(s5);
// If we get the row from client, we have the new column, but value remains null
// since this row was synced before client migration
client2row = await Helper.GetLastAddressRowAsync(client2Provider.CreateConnection(), addressId);
Console.WriteLine(client2row);
// What we can do here, is just make a sync with Renit
var s6 = await agent2.SynchronizeAsync("v1", SyncType.ReinitializeWithUpload, progress : progress);
Console.WriteLine($"Making a full Reinitialize sync on SQLite client2");
Console.WriteLine(s6);
// And now the row is correct
// If we get the row from client, we have the new column, but value remains null
// since this row was synced before client migration
client2row = await Helper.GetLastAddressRowAsync(client2Provider.CreateConnection(), addressId);
Console.WriteLine(client2row);
// Migration done
Console.WriteLine("End");
}