public async static Task SyncHttpThroughKestellAsync()
{
// server provider
var serverProvider = new SqlSyncProvider(GetDatabaseConnectionString("AdventureWorks"));
// proxy server based on server provider
var proxyServerProvider = new WebProxyServerProvider(serverProvider);
// client provider
var client1Provider = new SqlSyncProvider(GetDatabaseConnectionString("Adv"));
// proxy client provider
var proxyClientProvider = new WebProxyClientProvider();
var tables = new string[] { "ProductCategory",
"ProductDescription", "ProductModel",
"Product", "ProductModelProductDescription",
"Address", "Customer", "CustomerAddress",
"SalesOrderHeader", "SalesOrderDetail" };
var configuration = new SyncConfiguration(tables)
{
ScopeName = "AdventureWorks",
ScopeInfoTableName = "tscopeinfo",
SerializationFormat = Dotmim.Sync.Enumerations.SerializationFormat.Binary,
DownloadBatchSizeInKB = 400,
StoredProceduresPrefix = "s",
StoredProceduresSuffix = "",
TrackingTablesPrefix = "t",
TrackingTablesSuffix = "",
};
var serverHandler = new RequestDelegate(async context =>
{
proxyServerProvider.Configuration = configuration;
await proxyServerProvider.HandleRequestAsync(context);
});
using (var server = new KestrellTestServer())
{
var clientHandler = new ResponseDelegate(async(serviceUri) =>
{
proxyClientProvider.ServiceUri = new Uri(serviceUri);
var syncAgent = new SyncAgent(client1Provider, proxyClientProvider);
do
{
Console.Clear();
Console.WriteLine("Sync Start");
try
{
CancellationTokenSource cts = new CancellationTokenSource();
Console.WriteLine("--------------------------------------------------");
Console.WriteLine("1 : Normal synchronization.");
Console.WriteLine("2 : Fill configuration from server side");
Console.WriteLine("3 : Synchronization with reinitialize");
Console.WriteLine("4 : Synchronization with upload and reinitialize");
Console.WriteLine("5 : Deprovision everything from client side (tables included)");
Console.WriteLine("6 : Deprovision everything from client side (tables not included)");
Console.WriteLine("7 : Deprovision everything from server side (tables not included)");
Console.WriteLine("8 : Provision everything on the client side (tables included)");
Console.WriteLine("9 : Provision everything on the server side (tables not included)");
Console.WriteLine("10 : Insert datas on client");
Console.WriteLine("--------------------------------------------------");
Console.WriteLine("What's your choice ? ");
Console.WriteLine("--------------------------------------------------");
var choice = Console.ReadLine();
if (int.TryParse(choice, out int choiceNumber))
{
Console.WriteLine($"You choose {choice}. Start operation....");
switch (choiceNumber)
{
case 1:
var s1 = await syncAgent.SynchronizeAsync(cts.Token);
Console.WriteLine(s1);
break;
case 2:
SyncContext ctx = new SyncContext(Guid.NewGuid());
SqlSyncProvider syncConfigProvider = new SqlSyncProvider(GetDatabaseConnectionString("AdventureWorks"));
(ctx, configuration.Schema) = await syncConfigProvider.EnsureSchemaAsync(ctx, new Dotmim.Sync.Messages.MessageEnsureSchema
{
Schema = configuration.Schema,
SerializationFormat = Dotmim.Sync.Enumerations.SerializationFormat.Json
});
break;
case 3:
s1 = await syncAgent.SynchronizeAsync(SyncType.Reinitialize, cts.Token);
Console.WriteLine(s1);
break;
case 4:
s1 = await syncAgent.SynchronizeAsync(SyncType.ReinitializeWithUpload, cts.Token);
Console.WriteLine(s1);
break;
case 5:
SqlSyncProvider clientSyncProvider = syncAgent.LocalProvider as SqlSyncProvider;
await clientSyncProvider.DeprovisionAsync(configuration, SyncProvision.All | SyncProvision.Table);
Console.WriteLine("Deprovision complete on client");
break;
case 6:
SqlSyncProvider client2SyncProvider = syncAgent.LocalProvider as SqlSyncProvider;
await client2SyncProvider.DeprovisionAsync(configuration, SyncProvision.All);
Console.WriteLine("Deprovision complete on client");
break;
case 7:
SqlSyncProvider remoteSyncProvider = new SqlSyncProvider(GetDatabaseConnectionString("AdventureWorks"));
await remoteSyncProvider.DeprovisionAsync(configuration, SyncProvision.All);
Console.WriteLine("Deprovision complete on remote");
break;
case 8:
SqlSyncProvider clientSyncProvider2 = syncAgent.LocalProvider as SqlSyncProvider;
await clientSyncProvider2.ProvisionAsync(configuration, SyncProvision.All | SyncProvision.Table);
Console.WriteLine("Provision complete on client");
break;
case 9:
SqlSyncProvider remoteSyncProvider2 = new SqlSyncProvider(GetDatabaseConnectionString("AdventureWorks"));
await remoteSyncProvider2.ProvisionAsync(configuration, SyncProvision.All);
Console.WriteLine("Provision complete on remote");
break;
case 10:
var c = GetDatabaseConnectionString("Adv");
var catId = await InsertProductCategory(c);
var modelId = await InsertProductModel(c);
await InsertProduct(c, catId, modelId);
Console.WriteLine("Inserted a model, a category and a product.");
break;
default:
break;
}
}
}
catch (SyncException e)
{
Console.WriteLine(e.ToString());
}
catch (Exception e)
{
Console.WriteLine("UNKNOW EXCEPTION : " + e.Message);
}
Console.WriteLine("--------------------------------------------------");
Console.WriteLine("Press a key to choose again, or Escapte to end");
} while (Console.ReadKey().Key != ConsoleKey.Escape);
});
await server.Run(serverHandler, clientHandler);
}
}
public MySqlSyncTests(MySqlSyncSimpleFixture fixture)
{
this.fixture = fixture;
this.agent = fixture.Agent;
}
public void CreateInitialLocalDB(string strConnectionString,bool isCreated)
{
try
{
strClientConnectionString = strConnectionString;
// sync = new SqlCeClientSyncProvider(strClientConnectionString);
clientSyncProvider = new SqlCeClientSyncProvider(strClientConnectionString);
if (!isCreated)
{
SqlCeEngine clientEngine = new SqlCeEngine(strClientConnectionString);
clientEngine.CreateDatabase();
clientEngine.Dispose();
tblCallTable = CreateCallTable();
tblLeadsTable = CreateLeadsTable();
tblCallBackTable = CreateCallBackTable();
tblDispositionTable = CreateDispositionTable();
}
else
{
tblCallTable = new SyncTable("Call");
tblCallTable.SyncDirection = SyncDirection.UploadOnly;
tblLeadsTable = new SyncTable("Leads");
tblLeadsTable.SyncDirection = SyncDirection.UploadOnly;
tblCallBackTable = new SyncTable("CallBack");
tblCallBackTable.SyncDirection = SyncDirection.UploadOnly;
//Creating Disposition Table (Added by Alpa)
tblDispositionTable = new SyncTable("Disposition");
tblDispositionTable.SyncDirection = SyncDirection.UploadOnly;
}
strClientConnectionString = strConnectionString;
// sync = new SqlCeClientSyncProvider(strClientConnectionString);
serverSyncProvider = new DbServerSyncProvider();
syncAgent = new SyncAgent();
// syncAgent.ServerSyncProvider = serverSyncProvider;
syncAgent.RemoteProvider = serverSyncProvider;
serverConnection = new SqlConnection(VMuktiAPI.VMuktiInfo.MainConnectionString);
serverSyncProvider.Connection = serverConnection;
serverSyncProvider.ApplyChangeFailed += new EventHandler<ApplyChangeFailedEventArgs>(serverSyncProvider_ApplyChangeFailed);
//syncAgent.ClientSyncProvider = clientSyncProvider;
syncAgent.LocalProvider = clientSyncProvider;
myGroup = new SyncGroup("DialerGroup");
tblCallTable.SyncGroup = myGroup;
tblLeadsTable.SyncGroup = myGroup;
tblCallBackTable.SyncGroup = myGroup;
tblDispositionTable.SyncGroup = myGroup;
syncAgent.Configuration.SyncTables.Add(tblCallTable);
syncAgent.Configuration.SyncTables.Add(tblLeadsTable);
syncAgent.Configuration.SyncTables.Add(tblCallBackTable);
syncAgent.Configuration.SyncTables.Add(tblDispositionTable);
CallAdapter = new SqlSyncAdapterBuilder();
CallAdapter.Connection = serverConnection;
CallAdapter.SyncDirection = SyncDirection.UploadOnly;
CallAdapter.TableName = "Call";
// CallAdapter.DataColumns.Add("ID");
CallAdapter.DataColumns.Add("LeadID");
CallAdapter.DataColumns.Add("CalledDate");
CallAdapter.DataColumns.Add("ModifiedDate");
CallAdapter.DataColumns.Add("ModifiedBy");
CallAdapter.DataColumns.Add("GeneratedBy");
CallAdapter.DataColumns.Add("StartDate");
CallAdapter.DataColumns.Add("StartTime");
CallAdapter.DataColumns.Add("DurationInSecond");
CallAdapter.DataColumns.Add("DespositionID");
CallAdapter.DataColumns.Add("CampaignID");
CallAdapter.DataColumns.Add("ConfID");
CallAdapter.DataColumns.Add("IsDeleted");
CallAdapter.DataColumns.Add("CallNote");
CallAdapter.DataColumns.Add("IsDNC");
CallAdapter.DataColumns.Add("IsGlobal");
CallAdapter.DataColumns.Add("RecordedFileName"); //For Recording File Name
CallAdapterSyncAdapter = CallAdapter.ToSyncAdapter();
CallAdapterSyncAdapter.DeleteCommand = null;
serverSyncProvider.SyncAdapters.Add(CallAdapterSyncAdapter);
LeadAdapter = new SqlSyncAdapterBuilder();
LeadAdapter.Connection = serverConnection;
LeadAdapter.SyncDirection = SyncDirection.UploadOnly;
LeadAdapter.TableName = "Leads";
LeadAdapter.DataColumns.Add("ID");
LeadAdapter.DataColumns.Add("PhoneNo");
LeadAdapter.DataColumns.Add("LeadFormatID");
LeadAdapter.DataColumns.Add("CreatedDate");
LeadAdapter.DataColumns.Add("CreatedBy");
LeadAdapter.DataColumns.Add("DeletedDate");
LeadAdapter.DataColumns.Add("DeletedBy");
LeadAdapter.DataColumns.Add("IsDeleted");
LeadAdapter.DataColumns.Add("ModifiedDate");
LeadAdapter.DataColumns.Add("ModifiedBy");
LeadAdapter.DataColumns.Add("DNCFlag");
LeadAdapter.DataColumns.Add("DNCBy");
LeadAdapter.DataColumns.Add("ListID");
LeadAdapter.DataColumns.Add("LocationID");
LeadAdapter.DataColumns.Add("RecycleCount");
LeadAdapter.DataColumns.Add("Status");
LeadAdapter.DataColumns.Add("IsGlobalDNC");
//LeadAdapter.DataColumns.Add("LastEditDate");
//LeadAdapter.DataColumns.Add("CreationDate");
LeadAdapterSyncAdapter = LeadAdapter.ToSyncAdapter();
LeadAdapterSyncAdapter.DeleteCommand = null;
LeadAdapterSyncAdapter.InsertCommand = null;
serverSyncProvider.SyncAdapters.Add(LeadAdapterSyncAdapter);
CallBackAdapter = new SqlSyncAdapterBuilder();
CallBackAdapter.Connection = serverConnection;
CallBackAdapter.SyncDirection = SyncDirection.UploadOnly;
CallBackAdapter.TableName = "CallBack";
CallBackAdapter.DataColumns.Add("ID");
CallBackAdapter.DataColumns.Add("CallID");
CallBackAdapter.DataColumns.Add("CallBackDate");
CallBackAdapter.DataColumns.Add("Comment");
CallBackAdapter.DataColumns.Add("IsPublic");
CallBackAdapter.DataColumns.Add("IsDeleted");
CallBackAdapterSyncAdapter = CallBackAdapter.ToSyncAdapter();
CallBackAdapterSyncAdapter.DeleteCommand = null;
serverSyncProvider.SyncAdapters.Add(CallBackAdapterSyncAdapter);
//Creating Disposition Table in sdf (Added by Alpa)
DispositionAdapter = new SqlSyncAdapterBuilder();
DispositionAdapter.Connection = serverConnection;
DispositionAdapter.SyncDirection = SyncDirection.UploadOnly;
DispositionAdapter.TableName = "Disposition";
DispositionAdapter.DataColumns.Add("ID");
DispositionAdapter.DataColumns.Add("DespositionName");
DispositionAdapter.DataColumns.Add("Description");
DispositionAdapter.DataColumns.Add("IsActive");
DispositionAdapter.DataColumns.Add("IsDeleted");
DispositionAdapter.DataColumns.Add("CreatedDate");
DispositionAdapter.DataColumns.Add("CreatedBy");
DispositionAdapter.DataColumns.Add("ModifiedDate");
DispositionAdapter.DataColumns.Add("ModifiedBy");
DispositionAdapterSyncAdapter = DispositionAdapter.ToSyncAdapter();
DispositionAdapterSyncAdapter.DeleteCommand = null;
DispositionAdapterSyncAdapter.InsertCommand = null;
serverSyncProvider.SyncAdapters.Add(DispositionAdapterSyncAdapter);
ce = new SqlCeConnection(strClientConnectionString);
ce.Open();
CheckPreviousSyncWithServer();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Synchronize()
{
if (log.IsDebugEnabled)
{
log.Debug("Synchronize:begin");
}
if (!CheckConnection(localConnection, "Local"))
{
return;
}
if (!CheckConnection(remoteConnection, "Remote"))
{
return;
}
log4net.GlobalContext.Properties["test"] = "123";
syncAgent = new SyncAgent();
syncAgent.Local = localConnection.GetSyncDatabase();
syncAgent.Remote = remoteConnection.GetSyncDatabase();
syncAgent.LocalToRemoteLinkId = linkInfo.LocalId;
syncAgent.RemoteToLocalLinkId = linkInfo.RemoteId;
syncAgent.StateChanged += new SyncAgentStateChangedHandler(syncAgent_StateChanged);
syncAgent.LocalFilters.AddRange(linkInfo.LocalFilters);
syncAgent.RemoteFilters.AddRange(linkInfo.RemoteFilters);
syncAgent.Name = linkInfo.Name;
syncAgent.Order = linkInfo.Order;
//syncAgent.Local.Name = string.Format("{0} ({1})", linkInfo.Name, syncAgent.Local.Name);
//syncAgent.Remote.Name = string.Format("{0} ({1})", linkInfo.Name, syncAgent.Remote.Name);
syncAgent.SetLoggerName(log.Logger.Name);
bool executionResult = syncAgent.Execute();
if (executionResult)
{
log.Info("Сеанс синхронизации прошел успешно");
if (failedCount > 0)
{
// восстановление после ошибки
thread.Delay = linkInfo.SyncDelay;
}
failedCount = 0; // сброс счетчика ошибок
}
else
{
failedCount++;
if (failedCount < linkInfo.LinkRetries)
{
log.WarnFormat("Сеанс синхронизации завершен с ошибкой. Это {0} ошибочный сеанс. По достижении {1} ошибочных сеансов связь будет приостановлена.",
failedCount, linkInfo.LinkRetries);
}
else if (failedCount >= linkInfo.LinkRetries)
{
log.ErrorFormat("Сеанс синхронизации завершен с ошибкой. Попытка связи № {0}. Связь приостановлена.",
failedCount);
thread.Delay = linkInfo.RetryAfterErrorsDelay;
}
}
if (log.IsDebugEnabled)
{
log.Debug("Synchronize:end");
}
}
/// <summary>
/// Creates or updates a sync agent.
/// </summary>
/// <param name='operations'>
/// The operations group for this extension method.
/// </param>
/// <param name='resourceGroupName'>
/// The name of the resource group that contains the resource. You can obtain
/// this value from the Azure Resource Manager API or the portal.
/// </param>
/// <param name='serverName'>
/// The name of the server on which the sync agent is hosted.
/// </param>
/// <param name='syncAgentName'>
/// The name of the sync agent.
/// </param>
/// <param name='parameters'>
/// The requested sync agent resource state.
/// </param>
/// <param name='cancellationToken'>
/// The cancellation token.
/// </param>
public static async Task <SyncAgent> BeginCreateOrUpdateAsync(this ISyncAgentsOperations operations, string resourceGroupName, string serverName, string syncAgentName, SyncAgent parameters, CancellationToken cancellationToken = default(CancellationToken))
{
using (var _result = await operations.BeginCreateOrUpdateWithHttpMessagesAsync(resourceGroupName, serverName, syncAgentName, parameters, null, cancellationToken).ConfigureAwait(false))
{
return(_result.Body);
}
}
/// <summary>
/// Converts the response from the service to a powershell sync agent object
/// </summary>
/// <param name="resourceGroupName">The resource group the agent is in</param>
/// <param name="server">The server name</param>
/// <param name="syncAgent">The sync agent object from the response</param>
/// <returns>The converted model</returns>
public static AzureSqlSyncAgentModel CreateSyncAgentModelFromResponse(string resourceGroupName, string serverName, SyncAgent syncAgent)
{
return(new AzureSqlSyncAgentModel(resourceGroupName, serverName, syncAgent));
}
/// <summary>
/// Launch a simple sync, over TCP network, each sql server (client and server are reachable through TCP cp
/// </summary>
/// <returns></returns>
private static async Task SynchronizeExistingTablesAsync()
{
string serverName = "ServerTablesExist";
string clientName = "ClientsTablesExist";
await DbHelper.EnsureDatabasesAsync(serverName);
await DbHelper.EnsureDatabasesAsync(clientName);
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncProvider(DbHelper.GetDatabaseConnectionString(serverName));
var clientProvider = new SqlSyncProvider(DbHelper.GetDatabaseConnectionString(clientName));
// Tables involved in the sync process:
var tables = allTables;
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, tables);
// Using the Progress pattern to handle progession during the synchronization
var progress = new Progress <ProgressArgs>(s => Console.WriteLine($"[client]: {s.Context.SyncStage}:\t{s.Message}"));
// Setting configuration options
agent.SetConfiguration(s =>
{
s.ScopeInfoTableName = "tscopeinfo";
s.SerializationFormat = Dotmim.Sync.Enumerations.SerializationFormat.Binary;
s.StoredProceduresPrefix = "s";
s.StoredProceduresSuffix = "";
s.TrackingTablesPrefix = "t";
s.TrackingTablesSuffix = "";
});
agent.SetOptions(opt =>
{
opt.BatchDirectory = Path.Combine(SyncOptions.GetDefaultUserBatchDiretory(), "sync");
opt.BatchSize = 100;
opt.CleanMetadatas = true;
opt.UseBulkOperations = true;
opt.UseVerboseErrors = false;
});
var remoteProvider = agent.RemoteProvider as CoreProvider;
var dpAction = new Action <DatabaseProvisionedArgs>(args =>
{
Console.WriteLine($"-- [InterceptDatabaseProvisioned] -- ");
var sql = $"Update tscopeinfo set scope_last_sync_timestamp = 0 where [scope_is_local] = 1";
var cmd = args.Connection.CreateCommand();
cmd.Transaction = args.Transaction;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
});
remoteProvider.InterceptDatabaseProvisioned(dpAction);
agent.LocalProvider.InterceptDatabaseProvisioned(dpAction);
do
{
Console.Clear();
Console.WriteLine("Sync Start");
try
{
// Launch the sync process
var s1 = await agent.SynchronizeAsync(progress);
// Write results
Console.WriteLine(s1);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
//Console.WriteLine("Sync Ended. Press a key to start again, or Escapte to end");
} 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);
}
// --------------------------
// 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);
// Adding a new scope on the server with this new column and a new table
// Creating a new scope called "v2" on server
var setupV2 = new SyncSetup(new string[] { productCategoryTableName, productTableName });
setupV2.Tables[productCategoryTableName].Columns.AddRange(
new string[] { "ProductCategoryId", "Name", "rowguid", "ModifiedDate", "Attribute With Space" });
var serverScope = await remoteOrchestrator.ProvisionAsync("v2", setupV2);
// 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, with default value as null
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();
// 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");
// 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("v1");
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("v2");
Assert.Equal(2, r.TotalChangesDownloaded);
}
}
private static async Task TestSyncSQLite()
{
// Get SQL Server connection string
ConfigurationBuilder configurationBuilder = new ConfigurationBuilder();
configurationBuilder.AddJsonFile("config.json", true);
IConfiguration Configuration = configurationBuilder.Build();
var serverConfig = Configuration["AppConfiguration:ServerConnectionString"];
var clientConfig = Configuration["AppConfiguration:ClientSQLiteConnectionString"];
SqlSyncProvider serverProvider = new SqlSyncProvider(serverConfig);
SQLiteSyncProvider clientProvider = new SQLiteSyncProvider(clientConfig);
// With a config when we are in local mode (no proxy)
SyncConfiguration configuration = new SyncConfiguration(new string[] { "Customers", "ServiceTickets" });
SyncAgent agent = new SyncAgent(clientProvider, serverProvider, configuration);
agent.SyncProgress += SyncProgress;
//(s,e) =>
//{
// switch (e.Context.SyncStage)
// {
// case SyncStage.EnsureConfiguration:
// break;
// case SyncStage.EnsureDatabase:
// if (e.DatabaseScript != null)
// e.Action = ChangeApplicationAction.Rollback;
// break;
// case SyncStage.SelectedChanges:
// Console.WriteLine($"Selected changes : {e.ChangesStatistics.TotalSelectedChanges}. I:{e.ChangesStatistics.TotalSelectedChangesInserts}. U:{e.ChangesStatistics.TotalSelectedChangesUpdates}. D:{e.ChangesStatistics.TotalSelectedChangesDeletes}");
// break;
// case SyncStage.ApplyingChanges:
// Console.WriteLine($"Going to apply changes.");
// e.Action = ChangeApplicationAction.Continue;
// break;
// case SyncStage.AppliedChanges:
// Console.WriteLine($"Applied changes : {e.ChangesStatistics.TotalAppliedChanges}");
// e.Action = ChangeApplicationAction.Continue;
// break;
// case SyncStage.ApplyingInserts:
// Console.WriteLine($"Applying Inserts : {e.ChangesStatistics.AppliedChanges.Where(ac => ac.State == DmRowState.Added).Sum(ac => ac.ChangesApplied) }");
// e.Action = ChangeApplicationAction.Continue;
// break;
// case SyncStage.ApplyingDeletes:
// Console.WriteLine($"Applying Deletes : {e.ChangesStatistics.AppliedChanges.Where(ac => ac.State == DmRowState.Deleted).Sum(ac => ac.ChangesApplied) }");
// break;
// case SyncStage.ApplyingUpdates:
// Console.WriteLine($"Applying Updates : {e.ChangesStatistics.AppliedChanges.Where(ac => ac.State == DmRowState.Modified).Sum(ac => ac.ChangesApplied) }");
// e.Action = ChangeApplicationAction.Continue;
// break;
// }
//};
agent.ApplyChangedFailed += ApplyChangedFailed;
do
{
Console.Clear();
Console.WriteLine("Sync Start");
try
{
var s = await agent.SynchronizeAsync();
}
catch (SyncException e)
{
Console.WriteLine(e.ToString());
}
catch (Exception e)
{
Console.WriteLine("UNKNOW EXCEPTION : " + e.Message);
}
Console.WriteLine("Sync Ended. Press a key to start again, or Escapte to end");
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
public async Task RemoteOrchestrator_GetChanges_ShouldReturnNewRowsInserted()
{
var dbNameSrv = HelperDatabase.GetRandomName("tcp_lo_srv");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbNameSrv, true);
var dbNameCli = HelperDatabase.GetRandomName("tcp_lo_cli");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbNameCli, true);
var csServer = HelperDatabase.GetConnectionString(ProviderType.Sql, dbNameSrv);
var serverProvider = new SqlSyncProvider(csServer);
var csClient = HelperDatabase.GetConnectionString(ProviderType.Sql, dbNameCli);
var clientProvider = new SqlSyncProvider(csClient);
await new AdventureWorksContext((dbNameSrv, ProviderType.Sql, serverProvider), true, false).Database.EnsureCreatedAsync();
await new AdventureWorksContext((dbNameCli, ProviderType.Sql, clientProvider), true, false).Database.EnsureCreatedAsync();
var scopeName = "scopesnap1";
var syncOptions = new SyncOptions();
var setup = new SyncSetup();
// Make a first sync to be sure everything is in place
var agent = new SyncAgent(clientProvider, serverProvider, this.Tables, scopeName);
// Making a first sync, will initialize everything we need
await agent.SynchronizeAsync();
// Get the orchestrators
var localOrchestrator = agent.LocalOrchestrator;
var remoteOrchestrator = agent.RemoteOrchestrator;
// Server side : Create a product category and a product
// Create a productcategory item
// Create a new product on server
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);
using (var ctx = new AdventureWorksContext((dbNameSrv, ProviderType.Sql, serverProvider)))
{
var pc = new ProductCategory {
ProductCategoryId = productCategoryId, Name = productCategoryName
};
ctx.Add(pc);
var product = new Product {
ProductId = productId, Name = productName, ProductNumber = productNumber
};
ctx.Add(product);
await ctx.SaveChangesAsync();
}
// Get client scope
var clientScope = await localOrchestrator.GetClientScopeAsync();
// Get changes to be populated to the server
var changes = await remoteOrchestrator.GetChangesAsync(clientScope);
Assert.NotNull(changes.ServerBatchInfo);
Assert.NotNull(changes.ServerChangesSelected);
Assert.Equal(2, changes.ServerChangesSelected.TableChangesSelected.Count);
Assert.Contains("Product", changes.ServerChangesSelected.TableChangesSelected.Select(tcs => tcs.TableName).ToList());
Assert.Contains("ProductCategory", changes.ServerChangesSelected.TableChangesSelected.Select(tcs => tcs.TableName).ToList());
var productTable = changes.ServerBatchInfo.InMemoryData.Tables["Product", "SalesLT"];
var productRowName = productTable.Rows[0]["Name"];
Assert.Equal(productName, productRowName);
var productCategoryTable = changes.ServerBatchInfo.InMemoryData.Tables["ProductCategory", "SalesLT"];
var productCategoryRowName = productCategoryTable.Rows[0]["Name"];
Assert.Equal(productCategoryName, productCategoryRowName);
}
private static async Task OutDatedAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
// Create 2 Sql Sync providers
var serverProvider = new SqlSyncProvider(serverConnectionString);
// Second provider is using plain old Sql Server provider, relying on triggers and tracking tables to create the sync environment
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Tables involved in the sync process:
var tables = new string[] { "ProductCategory", "ProductModel", "Product",
"Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail" };
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider);
Console.WriteLine("- Initialize the databases with initial data");
// Make a first sync to have everything in place
Console.WriteLine(await agent.SynchronizeAsync(tables));
// Call a server delete metadata to update the last valid timestamp value in scope_info_server table
var dmc = await agent.RemoteOrchestrator.DeleteMetadatasAsync();
Console.WriteLine("- Insert data in client database and then generate an out dated scenario");
// Insert a value on client
await Helper.InsertOneCustomerAsync(clientProvider.CreateConnection(), "John", "Doe");
// Simulate an outdated situation in the local database
await Helper.SimulateOutDateScenarioAsync(clientProvider.CreateConnection(), dmc.TimestampLimit - 1);
// Action when outdate occurs
agent.LocalOrchestrator.OnOutdated(oa =>
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("local database is too old to synchronize with the server.");
Console.ResetColor();
Console.WriteLine("Do you want to synchronize anyway, and potentially lost data ? ");
Console.Write("Enter a value ('r' for reinitialize or 'ru' for reinitialize with upload): ");
var answer = Console.ReadLine();
if (answer.ToLowerInvariant() == "r")
{
oa.Action = OutdatedAction.Reinitialize;
}
else if (answer.ToLowerInvariant() == "ru")
{
oa.Action = OutdatedAction.ReinitializeWithUpload;
}
});
do
{
try
{
Console.WriteLine("- Launch synchronization");
var res = await agent.SynchronizeAsync();
Console.WriteLine(res);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
public SqliteSyncTests(SqliteSyncSimpleFixture fixture)
{
this.fixture = fixture;
this.agent = fixture.Agent;
}
public SyncService(SyncProvider provider, IConfiguration configuration, ISyncSender sync, ILogger <SyncService> logger)
{
_provider = provider;
_configuration = configuration;
ConnectionString = _configuration.GetConnectionString("HUB");
_clientProvider = new SqliteSyncProvider("box.db");
_proxyClientProvider = new WebProxyClientProvider(new Uri(_configuration["ClaWebApiServer"] + "/sync/post"));
_agent = new SyncAgent(_clientProvider, _proxyClientProvider);
_agent.LocalProvider.InterceptConnectionOpen(args =>
{
var connection = args.Connection as SqliteConnection;
if (connection == null)
{
_logger.LogError("Couldn't convert db connection to SqliteConnection");
return;
}
var cmd = connection.CreateCommand();
var password = _configuration["SqlPassword"];
// Prevent SQL Injection
cmd.CommandText = "SELECT quote($password);";
cmd.Parameters.AddWithValue("$password", password);
var quotedPassword = (string)cmd.ExecuteScalar();
// Encrypt database
cmd.CommandText = "PRAGMA key = " + quotedPassword;
cmd.Parameters.Clear();
cmd.ExecuteNonQuery();
});
_sync = sync;
_logger = logger;
//_sync.ChangeDetect += async (sender, args) => { await _provider.UpdateServer(_hubConnection, _myJwt); };
_sync.ChangeDetect += (sender, args) =>
{
_logger.LogInformation($"[SyncService] Change Registered. Debouncing for 5 seconds...");
_changeSubject.OnNext(true);
};
_changeSubject
.Throttle(TimeSpan.FromSeconds(5))
.Subscribe(async s =>
{
try
{
_logger.LogInformation($"[SyncService] Debounce complete. Attempting to update.");
if (_sync.GetConnectedStatus())
{
await RequestSync();
}
else
{
_logger.LogInformation($"[SyncService] Server connection offline. Not updating.");
}
}
catch (Exception e)
{
_logger.LogError($"[ChangeDetect] Exception {e.Message}");
}
});
_hubConnection = new HubConnectionBuilder()
.WithUrl(_configuration["ClaWebApiServer"] + "/sync", options =>
{
options.AccessTokenProvider = async() => _myJwt;
})
.AddJsonProtocol(options =>
{
options.PayloadSerializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
})
.Build();
_boxUiHubConnection = new HubConnectionBuilder()
.WithUrl("http://localhost:5001/signalr", options =>
{
options.Transports = HttpTransportType.WebSockets;
options.SkipNegotiation = true;
})
.Build();
_registryManager = RegistryManager.CreateFromConnectionString(ConnectionString);
}
public async Task LocalOrchestrator_ApplyChanges()
{
var dbNameSrv = HelperDatabase.GetRandomName("tcp_lo_srv");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbNameSrv, true);
var dbNameCli = HelperDatabase.GetRandomName("tcp_lo_cli");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbNameCli, true);
var csServer = HelperDatabase.GetConnectionString(ProviderType.Sql, dbNameSrv);
var serverProvider = new SqlSyncProvider(csServer);
var csClient = HelperDatabase.GetConnectionString(ProviderType.Sql, dbNameCli);
var clientProvider = new SqlSyncProvider(csClient);
await new AdventureWorksContext((dbNameSrv, ProviderType.Sql, serverProvider), true, false).Database.EnsureCreatedAsync();
await new AdventureWorksContext((dbNameCli, ProviderType.Sql, clientProvider), true, false).Database.EnsureCreatedAsync();
var scopeName = "scopesnap1";
var syncOptions = new SyncOptions();
var setup = new SyncSetup();
// Make a first sync to be sure everything is in place
var agent = new SyncAgent(clientProvider, serverProvider, this.Tables, scopeName);
// Making a first sync, will initialize everything we need
var s = await agent.SynchronizeAsync();
// Get the orchestrators
var localOrchestrator = agent.LocalOrchestrator;
var remoteOrchestrator = agent.RemoteOrchestrator;
// Client side : Create a product category and a product
// Create a productcategory item
// Create a new product on server
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);
using (var ctx = new AdventureWorksContext((dbNameSrv, ProviderType.Sql, serverProvider)))
{
var pc = new ProductCategory {
ProductCategoryId = productCategoryId, Name = productCategoryName
};
ctx.Add(pc);
var product = new Product {
ProductId = productId, Name = productName, ProductNumber = productNumber
};
ctx.Add(product);
await ctx.SaveChangesAsync();
}
var onDatabaseApplying = 0;
var onDatabaseApplied = 0;
var onBatchApplying = 0;
var onBatchApplied = 0;
var onApplying = 0;
var onApplied = 0;
localOrchestrator.OnDatabaseChangesApplying(dcs =>
{
onDatabaseApplying++;
});
localOrchestrator.OnDatabaseChangesApplied(dcs =>
{
Assert.NotNull(dcs.ChangesApplied);
Assert.Equal(2, dcs.ChangesApplied.TableChangesApplied.Count);
onDatabaseApplied++;
});
localOrchestrator.OnTableChangesBatchApplying(action =>
{
Assert.NotNull(action.Changes);
Assert.NotNull(action.Command);
onBatchApplying++;
});
localOrchestrator.OnTableChangesBatchApplied(action =>
{
Assert.Equal(1, action.TableChangesApplied.Applied);
onBatchApplied++;
});
localOrchestrator.OnTableChangesApplying(action =>
{
Assert.NotNull(action.Table);
onApplying++;
});
localOrchestrator.OnTableChangesApplied(action =>
{
Assert.Equal(1, action.TableChangesApplied.Applied);
onApplied++;
});
// Making a first sync, will initialize everything we need
var s2 = await agent.SynchronizeAsync();
Assert.Equal(2, onBatchApplying);
Assert.Equal(2, onBatchApplied);
Assert.Equal(1, onDatabaseApplying);
Assert.Equal(1, onDatabaseApplied);
Assert.Equal(4, onApplying); // Deletes + Modified state = Table count * 2
Assert.Equal(2, onApplied); // Two tables applied
HelperDatabase.DropDatabase(ProviderType.Sql, dbNameSrv);
HelperDatabase.DropDatabase(ProviderType.Sql, dbNameCli);
}
public static async Task SyncHttpThroughKestellAsync()
{
// server provider
var serverProvider = new SqlSyncProvider(DbHelper.GetDatabaseConnectionString(serverDbName));
// client provider
var client1Provider = new SqlSyncProvider(DbHelper.GetDatabaseConnectionString(clientDbName));
// proxy client provider
var proxyClientProvider = new WebProxyClientProvider();
var tables = new string[] { "ProductCategory",
"ProductDescription", "ProductModel",
"Product", "ProductModelProductDescription",
"Address", "Customer", "CustomerAddress",
"SalesOrderHeader", "SalesOrderDetail" };
var configuration = new Action <SyncConfiguration>(conf =>
{
conf.ScopeName = "AdventureWorks";
conf.ScopeInfoTableName = "tscopeinfo";
conf.SerializationFormat = Dotmim.Sync.Enumerations.SerializationFormat.Binary;
conf.StoredProceduresPrefix = "s";
conf.StoredProceduresSuffix = "";
conf.TrackingTablesPrefix = "t";
conf.TrackingTablesSuffix = "";
conf.Add(tables);
});
var optionsClient = new Action <SyncOptions>(opt =>
{
opt.BatchDirectory = Path.Combine(SyncOptions.GetDefaultUserBatchDiretory(), "client");
opt.BatchSize = 100;
opt.CleanMetadatas = true;
opt.UseBulkOperations = true;
opt.UseVerboseErrors = false;
});
var optionsServer = new Action <SyncOptions>(opt =>
{
opt.BatchDirectory = Path.Combine(SyncOptions.GetDefaultUserBatchDiretory(), "server");
opt.BatchSize = 100;
opt.CleanMetadatas = true;
opt.UseBulkOperations = true;
opt.UseVerboseErrors = false;
});
var serverHandler = new RequestDelegate(async context =>
{
var proxyServerProvider = WebProxyServerProvider.Create(context, serverProvider, configuration, optionsServer);
await proxyServerProvider.HandleRequestAsync(context);
});
using (var server = new KestrellTestServer())
{
var clientHandler = new ResponseDelegate(async(serviceUri) =>
{
proxyClientProvider.ServiceUri = new Uri(serviceUri);
var syncAgent = new SyncAgent(client1Provider, proxyClientProvider);
do
{
Console.Clear();
Console.WriteLine("Sync Start");
try
{
var cts = new CancellationTokenSource();
Console.WriteLine("--------------------------------------------------");
Console.WriteLine("1 : Normal synchronization.");
Console.WriteLine("2 : Synchronization with reinitialize");
Console.WriteLine("3 : Synchronization with upload and reinitialize");
Console.WriteLine("--------------------------------------------------");
Console.WriteLine("What's your choice ? ");
Console.WriteLine("--------------------------------------------------");
var choice = Console.ReadLine();
if (int.TryParse(choice, out var choiceNumber))
{
Console.WriteLine($"You choose {choice}. Start operation....");
switch (choiceNumber)
{
case 1:
var s1 = await syncAgent.SynchronizeAsync(cts.Token);
Console.WriteLine(s1);
break;
case 2:
s1 = await syncAgent.SynchronizeAsync(SyncType.Reinitialize, cts.Token);
Console.WriteLine(s1);
break;
case 3:
s1 = await syncAgent.SynchronizeAsync(SyncType.ReinitializeWithUpload, cts.Token);
Console.WriteLine(s1);
break;
default:
break;
}
}
}
catch (SyncException e)
{
Console.WriteLine(e.ToString());
}
catch (Exception e)
{
Console.WriteLine("UNKNOW EXCEPTION : " + e.Message);
}
Console.WriteLine("--------------------------------------------------");
Console.WriteLine("Press a key to choose again, or Escapte to end");
} while (Console.ReadKey().Key != ConsoleKey.Escape);
});
await server.Run(serverHandler, clientHandler);
}
}
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));
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" });
// 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 agent2.LocalOrchestrator.CreateTableAsync(serverScope2, "Product", "SalesLT");
}
else
{
await agent2.LocalOrchestrator.CreateTableAsync(serverScope2, "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);
}
public PostgreSqlSyncTests(PostgreSqlSyncSimpleFixture fixture)
{
this.fixture = fixture;
this.agent = fixture.Agent;
}
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);
}
}
}
internal SyncContext Sync(string value)
{
if (String.IsNullOrEmpty(value))
{
throw new Exception("Loading a project requires a name. Ex : dotnet sync --load project01");
}
Project project = DataStore.Current.LoadProject(value);
if (project == null)
{
throw new Exception($"Project {value} does not exists.");
}
if (project.ServerProvider == null || string.IsNullOrEmpty(project.ServerProvider.ConnectionString))
{
throw new Exception($"Server provider for project {project.Name} is not correctly defined. See help: dotnet sync provider --help");
}
if (project.ClientProvider == null || string.IsNullOrEmpty(project.ClientProvider.ConnectionString))
{
throw new Exception($"Client provider for project {project.Name} is not correctly defined. See help: dotnet sync provider --help");
}
if (project.ServerProvider.ProviderType != ProviderType.Web && (project.Tables == null || project.Tables.Count <= 0))
{
throw new Exception($"No table configured for project {project.Name}. See help: dotnet sync table --help");
}
IProvider serverProvider, clientprovider;
switch (project.ServerProvider.ProviderType)
{
case ProviderType.Sqlite:
throw new Exception("Can't use Sqlite as a server provider");
case ProviderType.Web:
serverProvider = new WebProxyClientProvider(new Uri(project.ServerProvider.ConnectionString));
break;
case ProviderType.MySql:
serverProvider = new MySqlSyncProvider(project.ServerProvider.ConnectionString);
break;
case ProviderType.SqlServer:
default:
serverProvider = new SqlSyncProvider(project.ServerProvider.ConnectionString);
break;
}
switch (project.ClientProvider.ProviderType)
{
case ProviderType.Web:
throw new Exception("Web proxy is used as a proxy server. You have to use an ASP.NET web backend. CLI uses a proxy as server provider");
case ProviderType.Sqlite:
clientprovider = new SqliteSyncProvider(project.ClientProvider.ConnectionString);
break;
case ProviderType.MySql:
clientprovider = new MySqlSyncProvider(project.ClientProvider.ConnectionString);
break;
case ProviderType.SqlServer:
default:
clientprovider = new SqlSyncProvider(project.ClientProvider.ConnectionString);
break;
}
SyncAgent agent = null;
if (project.ServerProvider.ProviderType != ProviderType.Web)
{
agent = new SyncAgent(clientprovider, serverProvider);
SyncConfiguration syncConfiguration = agent.Configuration;
foreach (var t in project.Tables.OrderBy(tbl => tbl.Order))
{
// Potentially user can pass something like [SalesLT].[Product]
// or SalesLT.Product or Product. ObjectNameParser will handle it
ObjectNameParser parser = new ObjectNameParser(t.Name);
var tableName = parser.ObjectName;
var schema = string.IsNullOrEmpty(t.Schema) ? parser.SchemaName : t.Schema;
var dmTable = new DmTable(tableName);
if (!String.IsNullOrEmpty(schema))
{
dmTable.Schema = schema;
}
dmTable.SyncDirection = t.Direction;
syncConfiguration.Add(dmTable);
}
syncConfiguration.BatchDirectory = string.IsNullOrEmpty(project.Configuration.BatchDirectory) ? null : project.Configuration.BatchDirectory;
syncConfiguration.SerializationFormat = project.Configuration.SerializationFormat;
syncConfiguration.UseBulkOperations = project.Configuration.UseBulkOperations;
syncConfiguration.DownloadBatchSizeInKB = (int)Math.Min(Int32.MaxValue, project.Configuration.DownloadBatchSizeInKB);
syncConfiguration.ConflictResolutionPolicy = project.Configuration.ConflictResolutionPolicy;
}
else
{
agent = new SyncAgent(clientprovider, serverProvider);
}
agent.TableChangesSelected += (sender, a) =>
Console.WriteLine($"Changes selected for table {a.TableChangesSelected.TableName}: {a.TableChangesSelected.TotalChanges}");
agent.TableChangesApplied += (sender, a) =>
Console.WriteLine($"Changes applied for table {a.TableChangesApplied.TableName}: [{a.TableChangesApplied.State}] {a.TableChangesApplied.Applied}");
// synchronous call
var syncContext = agent.SynchronizeAsync().GetAwaiter().GetResult();
var tsEnded = TimeSpan.FromTicks(syncContext.CompleteTime.Ticks);
var tsStarted = TimeSpan.FromTicks(syncContext.StartTime.Ticks);
var durationTs = tsEnded.Subtract(tsStarted);
var durationstr = $"{durationTs.Hours}:{durationTs.Minutes}:{durationTs.Seconds}.{durationTs.Milliseconds}";
Console.ForegroundColor = ConsoleColor.Green;
var s = $"Synchronization done. " + Environment.NewLine +
$"\tTotal changes downloaded: {syncContext.TotalChangesDownloaded} " + Environment.NewLine +
$"\tTotal changes uploaded: {syncContext.TotalChangesUploaded}" + Environment.NewLine +
$"\tTotal duration :{durationstr} ";
Console.WriteLine(s);
Console.ResetColor();
return(syncContext);
}
/// <summary>
/// Creates or updates a sync agent.
/// </summary>
/// <param name='operations'>
/// The operations group for this extension method.
/// </param>
/// <param name='resourceGroupName'>
/// The name of the resource group that contains the resource. You can obtain
/// this value from the Azure Resource Manager API or the portal.
/// </param>
/// <param name='serverName'>
/// The name of the server on which the sync agent is hosted.
/// </param>
/// <param name='syncAgentName'>
/// The name of the sync agent.
/// </param>
/// <param name='parameters'>
/// The requested sync agent resource state.
/// </param>
public static SyncAgent CreateOrUpdate(this ISyncAgentsOperations operations, string resourceGroupName, string serverName, string syncAgentName, SyncAgent parameters)
{
return(operations.CreateOrUpdateAsync(resourceGroupName, serverName, syncAgentName, parameters).GetAwaiter().GetResult());
}
private static async Task SynchronizeAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
// Create 2 Sql Sync providers
// First provider is using the Sql change tracking feature. Don't forget to enable it on your database until running this code !
// For instance, use this SQL statement on your server database : ALTER DATABASE AdventureWorks SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = ON)
// Otherwise, if you don't want to use Change Tracking feature, just change 'SqlSyncChangeTrackingProvider' to 'SqlSyncProvider'
var serverProvider = new SqlSyncChangeTrackingProvider(serverConnectionString);
var clientProvider = new SqlSyncProvider(clientConnectionString);
// Tables involved in the sync process:
var tables = new string[] { "ProductCategory", "ProductModel", "Product",
"Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail" };
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider, tables);
agent.Options.BatchSize = 20;
// Using the IProgress<T> pattern to handle progession dring the synchronization
// Be careful, Progress<T> is not synchronous. Use SynchronousProgress<T> instead !
var progress = new SynchronousProgress <ProgressArgs>(args => Console.WriteLine($"{args.Context.SyncStage}:\t{args.Message}"));
// --------------------------------------------
// Using Interceptors
// --------------------------------------------
// CancellationTokenSource is used to cancel a sync process in the next example
var cts = new CancellationTokenSource();
// Intercept a table changes selecting
// Because the changes are not yet selected, we can easily interrupt the process with the cancellation token
agent.LocalOrchestrator.OnTableChangesSelecting(args =>
{
Console.WriteLine($"-------- Getting changes from table {args.Table.GetFullName()} ...");
if (args.Table.TableName == "Table_That_Should_Not_Be_Sync")
{
cts.Cancel();
}
});
// Intercept a table changes applying with a particular state [Upsert] or [Deleted]
// The rows included in the args.Changes table will be applied right after.
agent.LocalOrchestrator.OnTableChangesBatchApplying(args =>
{
Console.WriteLine($"-------- Applying changes {args.State} to table {args.Changes.GetFullName()} ...");
if (args.Changes == null || args.Changes.Rows.Count == 0)
{
return;
}
foreach (var row in args.Changes.Rows)
{
Console.WriteLine(row);
}
});
// Intercept a table changes selected.
// The rows included in the args.Changes have been selected from the datasource and will be sent to the server
agent.LocalOrchestrator.OnTableChangesSelected(args =>
{
if (args.Changes == null || args.Changes.Rows.Count == 0)
{
return;
}
foreach (var row in args.Changes.Rows)
{
Console.WriteLine(row);
}
});
// ------------------------
// Because we are in a TCP mode, we can hook the server side events
// In an HTTP mode, the code below won't work
// ------------------------
agent.RemoteOrchestrator.OnTableChangesSelecting(args =>
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine($"-------- Getting changes from table {args.Table.GetFullName()} ...");
Console.ResetColor();
});
agent.RemoteOrchestrator.OnTableChangesSelected(args =>
{
if (args.Changes == null || args.Changes.Rows.Count == 0)
{
return;
}
Console.ForegroundColor = ConsoleColor.Yellow;
foreach (var row in args.Changes.Rows)
{
Console.WriteLine(row);
}
Console.ResetColor();
});
agent.RemoteOrchestrator.OnTableChangesBatchApplying(args =>
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine($"-------- Applying changes {args.State} to table {args.Changes.GetFullName()} ...");
if (args.Changes == null || args.Changes.Rows.Count == 0)
{
return;
}
foreach (var row in args.Changes.Rows)
{
Console.WriteLine(row);
}
Console.ResetColor();
});
do
{
// Launch the sync process
var s1 = await agent.SynchronizeAsync(SyncType.Normal, cts.Token, progress);
// Write results
Console.WriteLine(s1);
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
public async Task RemoteOrchestrator_CreateSnapshot_CheckInterceptors()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_srv");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var serverProvider = new SqlSyncProvider(cs);
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, serverProvider), true, true);
await ctx.Database.EnsureCreatedAsync();
var scopeName = "scopesnap1";
var onSnapshotCreating = false;
var onSnapshotCreated = false;
// snapshot directory
var snapshotDirctoryName = HelperDatabase.GetRandomName();
var snapshotDirectory = Path.Combine(Environment.CurrentDirectory, snapshotDirctoryName);
var options = new SyncOptions
{
SnapshotsDirectory = snapshotDirectory,
BatchSize = 200
};
var setup = new SyncSetup(Tables);
var remoteOrchestrator = new RemoteOrchestrator(serverProvider, options, setup, scopeName);
// Assert on connection and transaction interceptors
BaseOrchestratorTests.AssertConnectionAndTransaction(remoteOrchestrator, SyncStage.SnapshotCreating);
remoteOrchestrator.OnSnapshotCreating(args =>
{
Assert.IsType <SnapshotCreatingArgs>(args);
Assert.Equal(SyncStage.SnapshotCreating, 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.Schema);
Assert.Equal(snapshotDirectory, args.SnapshotDirectory);
Assert.NotEqual(0, args.Timestamp);
onSnapshotCreating = true;
});
remoteOrchestrator.OnSnapshotCreated(args =>
{
Assert.IsType <SnapshotCreatedArgs>(args);
Assert.Equal(SyncStage.SnapshotCreating, args.Context.SyncStage);
Assert.Equal(scopeName, args.Context.ScopeName);
Assert.NotNull(args.Connection);
Assert.Null(args.Transaction);
Assert.NotNull(args.BatchInfo);
var finalDirectoryFullName = Path.Combine(snapshotDirectory, scopeName);
Assert.Equal(finalDirectoryFullName, args.BatchInfo.DirectoryRoot);
Assert.Equal("ALL", args.BatchInfo.DirectoryName);
Assert.Single(args.BatchInfo.BatchPartsInfo);
Assert.Equal(16, args.BatchInfo.BatchPartsInfo[0].Tables.Length);
Assert.True(args.BatchInfo.BatchPartsInfo[0].IsLastBatch);
onSnapshotCreated = true;
});
var bi = await remoteOrchestrator.CreateSnapshotAsync();
Assert.Equal(SyncStage.SnapshotCreating, remoteOrchestrator.GetContext().SyncStage);
Assert.True(onSnapshotCreating);
Assert.True(onSnapshotCreated);
var dbNameCli = HelperDatabase.GetRandomName("tcp_lo_cli");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbNameCli, true);
var csClient = HelperDatabase.GetConnectionString(ProviderType.Sql, dbNameCli);
var clientProvider = new SqlSyncProvider(csClient);
// Make a first sync to be sure everything is in place
var agent = new SyncAgent(clientProvider, serverProvider, options, setup, scopeName);
var onSnapshotApplying = false;
var onSnapshotApplied = false;
agent.LocalOrchestrator.OnSnapshotApplying(saa =>
{
onSnapshotApplying = true;
});
agent.LocalOrchestrator.OnSnapshotApplied(saa =>
{
onSnapshotApplied = true;
});
// Making a first sync, will initialize everything we need
await agent.SynchronizeAsync();
Assert.True(onSnapshotApplying);
Assert.True(onSnapshotApplied);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task LocalOrchestrator_MetadataCleaning()
{
var dbNameSrv = HelperDatabase.GetRandomName("tcp_lo_srv");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbNameSrv, true);
var dbNameCli = HelperDatabase.GetRandomName("tcp_lo_cli");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbNameCli, true);
var csServer = HelperDatabase.GetConnectionString(ProviderType.Sql, dbNameSrv);
var serverProvider = new SqlSyncProvider(csServer);
var csClient = HelperDatabase.GetConnectionString(ProviderType.Sql, dbNameCli);
var clientProvider = new SqlSyncProvider(csClient);
await new AdventureWorksContext((dbNameSrv, ProviderType.Sql, serverProvider), true, false).Database.EnsureCreatedAsync();
await new AdventureWorksContext((dbNameCli, ProviderType.Sql, clientProvider), true, false).Database.EnsureCreatedAsync();
var scopeName = "scopesnap1";
var syncOptions = new SyncOptions();
var setup = new SyncSetup();
// Make a first sync to be sure everything is in place
var agent = new SyncAgent(clientProvider, serverProvider, this.Tables, scopeName);
// Making a first sync, will initialize everything we need
var s = await agent.SynchronizeAsync();
// Get the orchestrators
var localOrchestrator = agent.LocalOrchestrator;
var remoteOrchestrator = agent.RemoteOrchestrator;
// Server side : Create a product category and a product
// Create a productcategory item
// Create a new product on server
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);
using (var ctx = new AdventureWorksContext((dbNameSrv, ProviderType.Sql, serverProvider)))
{
var pc = new ProductCategory {
ProductCategoryId = productCategoryId, Name = productCategoryName
};
ctx.Add(pc);
var product = new Product {
ProductId = productId, Name = productName, ProductNumber = productNumber
};
ctx.Add(product);
await ctx.SaveChangesAsync();
}
var cleaning = 0;
var cleaned = 0;
localOrchestrator.OnMetadataCleaning(args =>
{
cleaning++;
});
localOrchestrator.OnMetadataCleaned(args =>
{
cleaned++;
Assert.Equal(0, args.DatabaseMetadatasCleaned.RowsCleanedCount);
Assert.Empty(args.DatabaseMetadatasCleaned.Tables);
});
// Making a first sync, will call cleaning, but nothing is cleaned
var s2 = await agent.SynchronizeAsync();
Assert.Equal(1, cleaning);
Assert.Equal(1, cleaned);
// Reset interceptors
localOrchestrator.OnMetadataCleaning(null);
localOrchestrator.OnMetadataCleaned(null);
cleaning = 0;
cleaned = 0;
localOrchestrator.OnMetadataCleaning(args =>
{
cleaning++;
});
localOrchestrator.OnMetadataCleaned(args =>
{
cleaned++;
});
// Making a second empty sync.
var s3 = await agent.SynchronizeAsync();
// If there is no changes on any tables, no metadata cleaning is called
Assert.Equal(0, cleaning);
Assert.Equal(0, cleaned);
// Server side : Create a product category
productCategoryName = HelperDatabase.GetRandomName();
productCategoryId = productCategoryName.ToUpperInvariant().Substring(0, 6);
using (var ctx = new AdventureWorksContext((dbNameSrv, ProviderType.Sql, serverProvider)))
{
var pc = new ProductCategory {
ProductCategoryId = productCategoryId, Name = productCategoryName
};
ctx.Add(pc);
await ctx.SaveChangesAsync();
}
// Reset interceptors
localOrchestrator.OnMetadataCleaning(null);
localOrchestrator.OnMetadataCleaned(null);
cleaning = 0;
cleaned = 0;
localOrchestrator.OnMetadataCleaning(args =>
{
cleaning++;
});
localOrchestrator.OnMetadataCleaned(args =>
{
cleaned++;
Assert.Equal(1, args.DatabaseMetadatasCleaned.RowsCleanedCount);
Assert.Single(args.DatabaseMetadatasCleaned.Tables);
Assert.Equal("SalesLT", args.DatabaseMetadatasCleaned.Tables[0].SchemaName);
Assert.Equal("ProductCategory", args.DatabaseMetadatasCleaned.Tables[0].TableName);
Assert.Equal(1, args.DatabaseMetadatasCleaned.Tables[0].RowsCleanedCount);
});
var s4 = await agent.SynchronizeAsync();
Assert.Equal(1, cleaning);
Assert.Equal(1, cleaned);
// Server side : Create a product category and a product
// Create a productcategory item
// Create a new product on server
productId = Guid.NewGuid();
productName = HelperDatabase.GetRandomName();
productNumber = productName.ToUpperInvariant().Substring(0, 10);
productCategoryName = HelperDatabase.GetRandomName();
productCategoryId = productCategoryName.ToUpperInvariant().Substring(0, 6);
using (var ctx = new AdventureWorksContext((dbNameSrv, ProviderType.Sql, serverProvider)))
{
var pc = new ProductCategory {
ProductCategoryId = productCategoryId, Name = productCategoryName
};
ctx.Add(pc);
var product = new Product {
ProductId = productId, Name = productName, ProductNumber = productNumber
};
ctx.Add(product);
await ctx.SaveChangesAsync();
}
// Reset interceptors
localOrchestrator.OnMetadataCleaning(null);
localOrchestrator.OnMetadataCleaned(null);
cleaning = 0;
cleaned = 0;
localOrchestrator.OnMetadataCleaning(args =>
{
cleaning++;
});
localOrchestrator.OnMetadataCleaned(args =>
{
cleaned++;
Assert.Equal(0, args.DatabaseMetadatasCleaned.RowsCleanedCount);
});
var s5 = await agent.SynchronizeAsync();
// cleaning is always called on N-1 rows, so nothing here should be called
Assert.Equal(1, cleaning);
Assert.Equal(1, cleaned);
HelperDatabase.DropDatabase(ProviderType.Sql, dbNameSrv);
HelperDatabase.DropDatabase(ProviderType.Sql, dbNameCli);
}
// SqlCeConnection ce = null;
public void CreateInitialLocalDB(string strConnectionString, bool isCreated)
{
try
{
strClientConnectionString = strConnectionString;
// sync = new SqlCeClientSyncProvider(strClientConnectionString);
clientSyncProvider = new SqlCeClientSyncProvider(strClientConnectionString);
if (!isCreated)
{
SqlCeEngine clientEngine = new SqlCeEngine(strClientConnectionString);
clientEngine.CreateDatabase();
clientEngine.Dispose();
tblCallTable = CreateCallTable();
tblLeadsTable = CreateLeadsTable();
tblCallBackTable = CreateCallBackTable();
}
else
{
tblCallTable = new SyncTable("Call");
tblCallTable.SyncDirection = SyncDirection.UploadOnly;
tblLeadsTable = new SyncTable("Leads");
tblLeadsTable.SyncDirection = SyncDirection.UploadOnly;
tblCallBackTable = new SyncTable("CallBack");
tblCallBackTable.SyncDirection = SyncDirection.UploadOnly;
}
strClientConnectionString = strConnectionString;
// sync = new SqlCeClientSyncProvider(strClientConnectionString);
serverSyncProvider = new DbServerSyncProvider();
syncAgent = new SyncAgent();
// syncAgent.ServerSyncProvider = serverSyncProvider;
syncAgent.RemoteProvider = serverSyncProvider;
serverConnection = new SqlConnection(VMuktiInfo.MainConnectionString);
serverSyncProvider.Connection = serverConnection;
//SqlCommand cmdAnchor = new SqlCommand();
// cmdAnchor.CommandType = CommandType.Text;
// cmdAnchor.CommandText = "SELECT @@DBTS";
// serverSyncProvider.SelectNewAnchorCommand = cmdAnchor;
// SqlCommand cmdClientId = new SqlCommand();
// cmdClientId.CommandType = CommandType.Text;
// cmdClientId.CommandText = "SELECT 1";
// serverSyncProvider.SelectClientIdCommand = cmdClientId;
//syncAgent.ClientSyncProvider = clientSyncProvider;
syncAgent.LocalProvider = clientSyncProvider;
myGroup = new SyncGroup("DialerGroup");
tblCallTable.SyncGroup = myGroup;
tblLeadsTable.SyncGroup = myGroup;
tblCallBackTable.SyncGroup = myGroup;
//syncAgent.SyncTables.Add(tblCallTable);
//syncAgent.SyncTables.Add(tblLeadsTable);
//syncAgent.SyncTables.Add(tblCallBackTable);
syncAgent.Configuration.SyncTables.Add(tblCallTable);
syncAgent.Configuration.SyncTables.Add(tblLeadsTable);
syncAgent.Configuration.SyncTables.Add(tblCallBackTable);
CallAdapter = new SqlSyncAdapterBuilder();
CallAdapter.Connection = serverConnection;
CallAdapter.SyncDirection = SyncDirection.UploadOnly;
CallAdapter.TableName = "Call";
CallAdapter.DataColumns.Add("ID");
CallAdapter.DataColumns.Add("LeadID");
CallAdapter.DataColumns.Add("CalledDate");
CallAdapter.DataColumns.Add("ModifiedDate");
CallAdapter.DataColumns.Add("ModifiedBy");
CallAdapter.DataColumns.Add("GeneratedBy");
CallAdapter.DataColumns.Add("StartDate");
CallAdapter.DataColumns.Add("StartTime");
CallAdapter.DataColumns.Add("DurationInSecond");
CallAdapter.DataColumns.Add("DespositionID");
CallAdapter.DataColumns.Add("CampaignID");
CallAdapter.DataColumns.Add("ConfID");
CallAdapter.DataColumns.Add("IsDeleted");
CallAdapter.DataColumns.Add("CallNote");
CallAdapter.DataColumns.Add("IsDNC");
CallAdapter.DataColumns.Add("IsGlobal");
CallAdapterSyncAdapter = CallAdapter.ToSyncAdapter();
CallAdapterSyncAdapter.DeleteCommand = null;
serverSyncProvider.SyncAdapters.Add(CallAdapterSyncAdapter);
LeadAdapter = new SqlSyncAdapterBuilder();
LeadAdapter.Connection = serverConnection;
LeadAdapter.SyncDirection = SyncDirection.UploadOnly;
LeadAdapter.TableName = "Leads";
LeadAdapter.DataColumns.Add("ID");
LeadAdapter.DataColumns.Add("PhoneNo");
LeadAdapter.DataColumns.Add("LeadFormatID");
LeadAdapter.DataColumns.Add("CreatedDate");
LeadAdapter.DataColumns.Add("CreatedBy");
LeadAdapter.DataColumns.Add("DeletedDate");
LeadAdapter.DataColumns.Add("DeletedBy");
LeadAdapter.DataColumns.Add("IsDeleted");
LeadAdapter.DataColumns.Add("ModifiedDate");
LeadAdapter.DataColumns.Add("ModifiedBy");
LeadAdapter.DataColumns.Add("DNCFlag");
LeadAdapter.DataColumns.Add("DNCBy");
LeadAdapter.DataColumns.Add("ListID");
LeadAdapter.DataColumns.Add("LocationID");
LeadAdapter.DataColumns.Add("RecycleCount");
LeadAdapter.DataColumns.Add("Status");
LeadAdapter.DataColumns.Add("IsGlobalDNC");
//LeadAdapter.DataColumns.Add("LastEditDate");
//LeadAdapter.DataColumns.Add("CreationDate");
LeadAdapterSyncAdapter = LeadAdapter.ToSyncAdapter();
LeadAdapterSyncAdapter.DeleteCommand = null;
LeadAdapterSyncAdapter.InsertCommand = null;
//LeadAdapterSyncAdapter.ColumnMappings.Add("Status", "Status");
//LeadAdapterSyncAdapter.ColumnMappings.Add("DNCFlag", "DNCFlag");
//LeadAdapterSyncAdapter.ColumnMappings.Add("DNCBy", "DNCBy");
serverSyncProvider.SyncAdapters.Add(LeadAdapterSyncAdapter);
CallBackAdapter = new SqlSyncAdapterBuilder();
CallBackAdapter.Connection = serverConnection;
CallBackAdapter.SyncDirection = SyncDirection.UploadOnly;
CallBackAdapter.TableName = "CallBack";
CallBackAdapter.DataColumns.Add("ID");
CallBackAdapter.DataColumns.Add("CallID");
CallBackAdapter.DataColumns.Add("CallBackDate");
CallBackAdapter.DataColumns.Add("Comment");
CallBackAdapter.DataColumns.Add("IsPublic");
CallBackAdapter.DataColumns.Add("IsDeleted");
CallBackAdapterSyncAdapter = CallBackAdapter.ToSyncAdapter();
CallBackAdapterSyncAdapter.DeleteCommand = null;
serverSyncProvider.SyncAdapters.Add(CallBackAdapterSyncAdapter);
CheckPreviousSyncWithServer();
}
catch (Exception ex)
{
VMuktiAPI.VMuktiHelper.ExceptionHandler(ex, "CreateInitialLocalDB()", "ClsUserDataService.cs");
//MessageBox.Show("CreateInitialLocalDB: " + ex.Message);
}
}
private static async Task EncryptionAsync()
{
// Database script used for this sample : https://github.com/Mimetis/Dotmim.Sync/blob/master/CreateAdventureWorks.sql
var myRijndael = new RijndaelManaged();
myRijndael.GenerateKey();
myRijndael.GenerateIV();
// Create action for serializing and deserialzing for both remote and local orchestrators
var deserializing = new Action <DeserializingSetArgs>(dsa =>
{
// Create an encryptor to perform the stream transform.
var decryptor = myRijndael.CreateDecryptor(myRijndael.Key, myRijndael.IV);
using (var csDecrypt = new CryptoStream(dsa.FileStream, decryptor, CryptoStreamMode.Read))
{
using (var swDecrypt = new StreamReader(csDecrypt))
{
//Read all data to the ContainerSet
var str = swDecrypt.ReadToEnd();
dsa.Result = JsonConvert.DeserializeObject <ContainerSet>(str);
Console.WriteLine($"Deserialized container from file {dsa.FileName}. Container tables count:{dsa.Result.Tables.Count}");
}
}
});
var serializing = new Action <SerializingSetArgs>(ssa =>
{
Console.WriteLine($"Serialized container to file {ssa.FileName}. container tables count:{ssa.Set.Tables.Count}");
// Create an encryptor to perform the stream transform.
var encryptor = myRijndael.CreateEncryptor(myRijndael.Key, myRijndael.IV);
using (var msEncrypt = new MemoryStream())
{
using (var csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write))
{
using (var swEncrypt = new StreamWriter(csEncrypt))
{
//Write all data to the stream.
var strSet = JsonConvert.SerializeObject(ssa.Set);
swEncrypt.Write(strSet);
}
ssa.Result = msEncrypt.ToArray();
}
}
});
SqlSyncProvider serverProvider = new SqlSyncProvider(GetDatabaseConnectionString("AdventureWorks"));
SqlSyncProvider clientProvider = new SqlSyncProvider(GetDatabaseConnectionString("Client"));
// Defining options with Batchsize to enable serialization on disk
var options = new SyncOptions {
BatchSize = 1000
};
SyncAgent agent = new SyncAgent(clientProvider, serverProvider, options, new string[] {
"ProductCategory", "ProductModel", "Product", "Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail"
});
// Get the orchestrators
var localOrchestrator = agent.LocalOrchestrator;
var remoteOrchestrator = agent.RemoteOrchestrator;
// Encrypting / decrypting data on disk
localOrchestrator.OnSerializingSet(serializing);
localOrchestrator.OnDeserializingSet(deserializing);
remoteOrchestrator.OnSerializingSet(serializing);
remoteOrchestrator.OnDeserializingSet(deserializing);
Console.WriteLine(await agent.SynchronizeAsync());
Console.WriteLine("End");
}
