Replies: 6 comments 15 replies
-
In
If you are creating a new scope, that means one or more stored procedures are different (if adding / removing columns or filters) and / or one or more stored procedures are added / removed (if adding / removing tables) Otherwise, no need to create a new scope. That's being said, how would you implement such feature in DMS, especially the code part? This is a very interesting question, that would imply a lot of modification in DMS to be implemented. |
Beta Was this translation helpful? Give feedback.
-
I think about your problem. Here is an example: In this example, I'm creating 2 scopes on the server:
The trick here is that we will NOT create all stored procedures for scope filterproducts: // Creating one Setup
// This Setup contains all tables without filters
// When provision, it will be called "default"
var setup = new SyncSetup("ProductCategory", "Product", "Address",
"Customer", "CustomerAddress");
var serverProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(serverDbName));
var remoteOrchestrator = new RemoteOrchestrator(serverProvider);
// Provision this "default" scope
var serverScopeInfo = await remoteOrchestrator.ProvisionAsync("default", setup);
// Now, we are creating a new setup with a filter on ProductCategory and Product
var setup2 = new SyncSetup("ProductCategory", "Product", "Address",
"Customer", "CustomerAddress");
setup2.Filters.Add("ProductCategory", "Name");
var productFilter = new SetupFilter("Product");
productFilter.AddParameter("Name", DbType.String);
productFilter.AddJoin(Join.Left, "ProductCategory")
.On("Product", "ProductCategoryID", "ProductCategory", "ProductCategoryID");
productFilter.AddWhere("Name", "ProductCategory", "Name");
setup2.Filters.Add(productFilter);
// Instead of Provisioning everything, we are
// Provisionning the Stored procedures specific to filters and then save this new Scope
// Create a new ServerScopeInfo instance with schema and setup containing the filter
var schema2 = await remoteOrchestrator.GetSchemaAsync(setup2);
serverScopeInfo.Schema = schema2;
serverScopeInfo.Setup = setup2;
serverScopeInfo.Name = "filterproducts";
// Only generate the get changes with filters
await remoteOrchestrator.CreateStoredProcedureAsync(serverScopeInfo,
"ProductCategory", default, DbStoredProcedureType.SelectChangesWithFilters, true);
await remoteOrchestrator.CreateStoredProcedureAsync(serverScopeInfo,
"ProductCategory", default, DbStoredProcedureType.SelectInitializedChangesWithFilters, true);
await remoteOrchestrator.CreateStoredProcedureAsync(serverScopeInfo,
"Product", default, DbStoredProcedureType.SelectChangesWithFilters, true);
await remoteOrchestrator.CreateStoredProcedureAsync(serverScopeInfo,
"Product", default, DbStoredProcedureType.SelectInitializedChangesWithFilters, true);
await remoteOrchestrator.SaveServerScopeInfoAsync(serverScopeInfo); Now if you look at what have been generated on the server, you have something like this: As you can see, all stored procedures for scope "default" have been generated, but only a few have been created for scope "filterproducts" Now, we need to redirect all call to the good stored procedure.
var clientProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(clientDbName));
var agent = new SyncAgent(clientProvider, serverProvider);
// Eeach time a command is called on remote side,
// We are making a redirection to "default" stored procedures, except for filters specific stored proc
agent.RemoteOrchestrator.OnGetCommand(args =>
{
if (args.Command.CommandType == CommandType.StoredProcedure)
{
switch (args.CommandType)
{
case DbCommandType.SelectInitializedChangesWithFilters:
case DbCommandType.SelectChangesWithFilters:
break;
default:
args.Command.CommandText = args.Command.CommandText.Replace("_filterproducts_", "_default_");
break;
}
Console.WriteLine(args.Command.CommandText);
}
});
var p = new SyncParameters(("Name", "Bikes"));
var s = await agent.SynchronizeAsync("filterproducts", p); As you can see, we are executing stored procedures from both scopes, depending on the command type we need. Since it required some code modification, you can test it on the branch https://github.com/Mimetis/Dotmim.Sync/tree/OnGetCommand Let me know if it can solve your problem |
Beta Was this translation helpful? Give feedback.
-
I think I have a good idea of how it could be accomplished but it definitely would require some schema changes. The changes are fairly minor on the server-side and a little larger on the client-side but it would end up making the two schemas more uniform in structure. The key thing that this would seem to allow is being able to dynamically add new filters on an as needed basis without having to reinitialize, causing all the data to be downloaded again. Think of a person using an application that needs to synchronize all the asset data for the city they are currently working in. They have a few main cities they work out of but they also sometimes need to travel to new locations. This would allow them to have a scope instance per city and when they go to a new location a new instance is created with the new city parameter. Then, only the assets for that city will be downloaded, keeping all the asset data for their main cities still in tact. In fact the data for those other cities can still continue syncing and receiving updates so that when they get back everything is already in sync. CREATE TABLE [dbo].[scope_info_history](
[sync_scope_id] [uniqueidentifier] NOT NULL, -- Actually the client id
[sync_scope_name] [nvarchar](100) NOT NULL, -- Name of the scope config
[sync_scope_instance] [nvarchar](100) NOT NULL -- Name of the instance (default to sync_scope_name)
[scope_last_sync_timestamp] [bigint] NULL,
[scope_last_sync_duration] [bigint] NULL,
[scope_last_sync] [datetime] NULL,
)
-- Client only has a single scope_info table so probably it would make more sense if it were split into two tables like the server is
CREATE TABLE [dbo].[scope_info](
[sync_scope_name] [nvarchar](100) NOT NULL,
[sync_scope_schema] [nvarchar](max) NULL,
[sync_scope_setup] [nvarchar](max) NULL,
[sync_scope_version] [nvarchar](10) NULL,
)
CREATE TABLE [dbo].[scope_info_history](
[sync_scope_id] [uniqueidentifier] NOT NULL, -- Actually the client id
[sync_scope_name] [nvarchar](100) NOT NULL, -- Name of the scope config
[sync_scope_instance] [nvarchar](100) NOT NULL -- Name of the instance (default to sync_scope_name)
[scope_last_server_sync_timestamp] [bigint] NULL,
[scope_last_sync_timestamp] [bigint] NULL,
[scope_last_sync_duration] [bigint] NULL,
[scope_last_sync] [datetime] NULL,
) // Creating one Setup
// This Setup contains only the assets table
var setup = new SyncSetup("Assets");
// Create a filter On the city column of the assets tables
var assetsFilter = new SetupFilter("Assets");
productFilter.AddParameter("City", DbType.String);
productFilter.AddWhere("City", "Assets", "City");
setup.Filters.Add(assetsFilter);
var serverProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(serverDbName));
var clientProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(clientDbName));
// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverProvider);
var parameters1 = new SyncParameters
{
{ "City", "New York" },
};
var parameters2 = new SyncParameters
{
{ "City", "Los Angeles" },
};
// Sync the scope providing an instance name of Assets_NewYork. The name doesn't really matter as long as it is unique and is always supplied the same set of parameters.
var s1 = await agent.SynchronizeAsync(setup, parameters1, "Assets_NewYork);
// Sync the scope providing an instance name of Assets_LosAngeles. The name doesn't really matter as long as it is unique and is always supplied the same set of parameters.
var s1 = await agent.SynchronizeAsync(setup, parameters2, "Assets_LosAngeles);
// This will result in two records being stored in the scope_info_history table. |
Beta Was this translation helpful? Give feedback.
-
I will test the new OnGetCommand interceptor since this seems like the solution with the least amount of changes and won't cause backwards compatibility issues. In the scenario of the creating new scopes on the client, it will automatically try to provision on the client/server. We can block the entire provision process through this interceptor? |
Beta Was this translation helpful? Give feedback.
-
I've been working for the last 5 days on a new version of From your perspective, the most important to notice is that now, we have 2 tables on each side (server / client) that are identical:
Those two tables are created on both side and are identical. I realized that doing this made the DMS framework a little bit more fluent.. and easier to maintain. As an example, I've created a sync with 3 tables, 2 of them are filtered ( Here is a
As you can see, we don't have any more the last sync timestamp information here.
Each Here is the code I used to have this result: var setup = new SyncSetup("ProductCategory", "Product", "Employee");
setup.Tables[productCategoryTableName].Columns.AddRange("ProductCategoryId", "Name", "rowguid", "ModifiedDate");
setup.Filters.Add("ProductCategory", "ProductCategoryId");
setup.Filters.Add("Product", "ProductCategoryId");
var pMount = new SyncParameters(("ProductCategoryId", "MOUNTB"));
var pRoad = new SyncParameters(("ProductCategoryId", "ROADFR"));
var agent = new SyncAgent(client.Provider, Server.Provider);
var r1 = await agent.SynchronizeAsync("v1", setup, pMount);
var r2 = await agent.SynchronizeAsync("v1", setup, pRoad); It's a big step moving forward for If you want to test this version, you can grab the branch ClientHistory Be careful, there is no upgrade path from the current release version to this new one. And please let me know what you think about it |
Beta Was this translation helpful? Give feedback.
-
I tested the branch and it's working good! Table structure is clean. Huge improvement to DMS. |
Beta Was this translation helpful? Give feedback.
-
Currently the stored procs are unique based on table and scope name. In our scenario we create ‘instances’ of the base scope for filters. This causes the same stored procs to be created many times with the only difference being the scope name. This creates so many stored procs that are not needed. It also provisions an unnecessary amount of times.
Is there a solution for this? Sync framework had a scope config table and the scope info table linked to a config which solved this issue. You could have multiple instances of the same scope but only one set of stored procs.
Beta Was this translation helpful? Give feedback.
All reactions