Guidance with UploadOnly Sync.

[Daxter98]

Hello!

I’m using Dotmim Sync for a project where multiple clients (running a custom POS system with SQLite) sync to a central SQL Server DB via HTTP. The setup includes the following tables:

var tables = new[] {
    "Users", "Roles", "Permissions", "UserRoles", "RolePermissions",
    "FareCategories", "FareTables", "Transactions", "TransactionDetails",
    "CashShifts", "CashMovements", "UserSessions",
    "PaymentStations", "Bracelets", "GlobalSettings"
};

var setup = new SyncSetup(tables);

// Set specific tables to "UploadOnly"
setup.Tables["CashShifts"].SyncDirection = SyncDirection.UploadOnly;
setup.Tables["CashMovements"].SyncDirection = SyncDirection.UploadOnly;
setup.Tables["Transactions"].SyncDirection = SyncDirection.UploadOnly;
setup.Tables["TransactionDetails"].SyncDirection = SyncDirection.UploadOnly;
setup.Tables["Bracelets"].SyncDirection = SyncDirection.UploadOnly;

Some tables are set to "UploadOnly" because transactions are unique to each payment station (e.g., Transaction 1 for PS 1), and stations don’t need the same data.

The Issue:

Occasionally, a payment station goes unused for a while, making its local data outdated. When I try to sync, I need to trigger a reinitialization. From the documentation, it seems reinitialization causes data loss by deleting all client rows and downloading them again.

Question:

Is there a way to preserve existing data during reinitialization? After reinitializing, when I start syncing again, the server data gets overwritten due to the conflict resolution policy, which I’d like to avoid.

I understand that the conflict resolution policy can be modified, but I specifically need to keep the server data intact. Ideally, I’d like a "Reinitialize and Download Once" behavior. Would an interceptor be useful in this case, or is there another approach I should consider?

Any guidance would be greatly appreciated. Thanks in advance!

[Mimetis]

You want to keep the rows for all tables that are upload only right, when making a reinit ?

Once thing you can test (I didn't test, but it's worth a try) is to intercept the command when a reset is occurring.
The idea is to replace the CommandText property with something that will work but will not delete all the rows:

agent.LocalOrchestrator.OnGetCommand(args =>
{
  if (args.CommandType == DbCommandType.Reset)
  {
      var setupTable = args.ScopeInfo.Setup.Tables[args.Table.TableName, args.Table.SchemaName];

      if (setupTable != null && setupTable.SyncDirection == SyncDirection.UploadOnly)
          args.Command.CommandText = "Select 1;";
  }
});

All the rows from UploadOnly tables, on the client will be preserved

Let me know if it's working

[Daxter98]

Oh yes! That worked perfectly. I just had to set DisableConstraintsOnApplyChanges = true in the sync options to resolve the Foreign Key violation exceptions. Thank you so much, Mimetis!

Regarding the "Download Once" feature—do you think it’s possible to implement using an interceptor? During testing, I encountered an unusual situation where the on-site IT staff accidentally deleted the app, along with its database. To resolve it, I had to manually restore the data to the client database. While this scenario shouldn’t typically happen, having a feature like that would be incredibly useful. That said, I understand it might be beyond the scope of DMS.

[Mimetis]

in my opinion, create a new scope (called DownloadOnce for example) where you put these tables in DownloadOnly mode
Then you make a sync using this scope, it will download all rows from server to client in these particular tables:

var setup = new SyncSetup("CashShifts", "CashMovements");
setup.Tables["CashShifts"].SyncDirection = SyncDirection.DownloadOnly;
setup.Tables["CashMovements"].SyncDirection = SyncDirection.DownloadOnly;

var options = new SyncOptions { DisableConstraintsOnApplyChanges = true };

var agent = new SyncAgent(clientProvider, serverProvider, options);
var syncResult = await agent.SynchronizeAsync("DownloadOnly", setup, SyncType.Reinitialize, progress);

And you use this scope when you need it, for exceptional purposes where you have download these rows from the server, to the client;
Then the next sync is a regular one (where all these tables are marked as UploadOnly)

That being said, be careful with the interceptor, do not forget to use another agent where the interceptor is not bind to :)

[Daxter98]

That sounds interesting! I'll be sure to check out this approach as soon as I get the chance. Once again, thank you so much for your support—I truly appreciate it!