[Enhancement] Dotmim.Sync - specify columns to sync

[gentledepp]

Hello!

As to my knowledge, it is not possible to specify the columns that should be included in (or excluded from) the sync process. Is this a planned feature? Or: From the current architecture, is it hard to implement such a feature?

Our problem is that we do try to keep the model on client- and serverside as closely related as possible, but in reality there will always be some colums that you need on either side but that should not be included in the sync processus :-|

[Mimetis]

Hello @gentledepp

So far, it's not officially supported :) But yes, the Dotmim.Sync fx is writted to be able to support such scenario.

To be honest, I have to make more tests on this feature to be sure everything works correctly. The idea to exclude columns is to modify the Schema in the SyncConfiguration object.

Will make some tests and get back to you with a sample asap !

Seb

[gentledepp]

Hi @Mimetis

If you can provide me with a little more information in how this technique should work, this would be very helpful.

I am now starting to work on the sync-module of our software, so I could spend some time helping you to implement this. (It is an absolute showstopper if I am not able to get this to work ;-))

[gentledepp]

@Mimetis Sorry to bother you again, but we are currently in the middle of implementing the new sync module based on Dotmim.Sync, and we really need this feature for the following scenario:

• in our central database (MSSQL), we provide multitenancy by adding a [TenantID] to every row
• when synchronizing with our xamarin clients (SQLite), we do not want to keep the [TenantID] as it would need to be maintained by the clients.

What we already do with Microsoft Sync Framework is to filter the tables by @tenantid and not transmit those columns to the client. Once the client pushes any changes back to the server, the sync service intercepts all changed entities (e.g. Tasks, ServiceTickets, etc.) and sets the [TenantId]column using the @tenantid filter parameter provided by the sync process.

We really want to keept it that way. So any instructions on how we are allowed to implement it in dotmim.sync are very appreciated!

What I consider necessary at the moment is: 1) Provide a SyncConfiguration specifying that the [TenantId]column is to be ignored 2) specify in the @tenantid filter, that this filter is not intended for use on the client side (Xamarin and SQLite)

Am I right in that assumption?

[gentledepp]

Ok.. made some progress here. As you noted, a user should define the columns to sync by adding those columns to the DmTable when specifying the SyncConfiguration

I tried that, and immediately got an exception as I forgot to define the primary key. The reason for this is, that if there is already a schema, dotmim.sync does not do anything, but use it, so an API user whould have to define the complete DmSchema by hand! (Columns, keys, relationships)

It would be much nicer, if we could just add some kind of "whitelist" - i.e. an IEnumerable<string> containing the name of all the columns that should be included in the sync process.

Then, the CoreProvider of Dotmim.Sync could use that filter when retrieving the list of columns from the tablemanager:

var lstColumns = tblManager.GetTableDefinition()

The question is, where this list of columns should be defined? (I would say the SyncConfiguration)

What is your angle on this?

[Mimetis]

My angle is to:

• Define the tables, like it's already the case today.
• Let the Dotmim.Sync do its job (getting all the columns, constraints, index and so on)
• Intercept the event on SyncConfiguration when schema is applied ( called SchemaApplied )
• Get your DmTable in the SyncConfiguration and then remove the columns you don't want to sync

I guess, it could be more easy.

What do you think ?

[gentledepp]

That makes totally sense!

Should that already work?

(As I did not know about this, I added another virtual override to CoreProvider.Schema:

private async Task ReadSchemaAsync(DmSet schema)
        {
            if (schema == null || schema.Tables.Count <= 0)
                throw new ArgumentNullException("syncConfiguration", "Configuration should contains Tables, at least tables with a name");

            DbConnection connection = null;
            DbTransaction transaction;

            try
            {
                using (connection = this.CreateConnection())
                {
                    await connection.OpenAsync();

                    using (transaction = connection.BeginTransaction())
                    {
                        foreach (var dmTable in schema.Tables)
                        {
                            var builderTable = this.GetDbManager(dmTable.TableName);
                            var tblManager = builderTable.CreateManagerTable(connection, transaction);
                            tblManager.TableName = dmTable.TableName;

                            // get columns list
                            var lstColumns = tblManager.GetTableDefinition();

                            // Validate the column list and get the dmTable configuration object.
                            this.ValidateTableFromColumns(dmTable, lstColumns, tblManager);

                            var relations = tblManager.GetTableRelations();

                            if (relations != null)
                            {
                                foreach (var r in relations)
                                {
                                    DmColumn tblColumn = dmTable.Columns[r.ColumnName];
                                    DmColumn foreignColumn = null;
                                    var foreignTable = schema.Tables[r.ReferenceTableName];

                                    // Since we can have a table with a foreign key but not the parent table
                                    // It's not a problem, just forget it
                                    if (foreignTable == null)
                                        continue;

                                    foreignColumn = foreignTable.Columns[r.ReferenceColumnName];

                                    if (foreignColumn == null)
                                        throw new NotSupportedException(
                                            $"Foreign column {r.ReferenceColumnName} does not exist in table {r.TableName}");

                                    DmRelation dmRelation = new DmRelation(r.ForeignKey, tblColumn, foreignColumn);

                                    schema.Relations.Add(dmRelation);
                                }
                            }
                            // NEW OVERRIDE
                            await OnTableSchemaReadAsync(dmTable);
                            // NEW OVERRIDE

                        }

                        transaction.Commit();
                    }

                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                throw new SyncException(ex, SyncStage.SchemaApplying, this.ProviderTypeName);
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                    connection.Close();
            }
        }

``

[gentledepp]

which actually seems to work :-)

[Mimetis]

You do not have to add another virtual override to CoreProvider.Schema, since there is already some events.

Did you already made some tests ?

[gentledepp]

You are right - I will revert those changes and use the event. I am testing this tomorrow. Fingers crossed ;-)

[gentledepp]

Hi! As it seems, this works! Awesome! 🥇

However, I have one more idea:

• There is a table [Tasks] with a the columns [Id], [AssignedUserId], [Title], [IsCompleted], [Category]
• I am only interested in synchronizing the columns [Id], [AssignedUserId], [Title], [IsCompleted], so not the [Category]

In my perfect world, the Tasks_update_trigger' would use that information and only update theTasks_trackingentry, given the update on the table did not only change the[Category]` column.

However, I do not know how conflict resolution works exactly in Dotmim.Sync. So consider the following:

1. A Task 01 is created on the server and synchronized to the client
2. The server updates the [Category] column of the task. As the Tasks_update_trigger is smart, it ignores that change and does not update the tracking table
3. The client changes Task 01 to [IsCompleted] = 1 and synchronizes with the server

=> would that lead to a conflict?

[Mimetis]

Nice !! I did not expect it to work :) Well, It's a feature I had in mind since the begining, so Dotmim.Sync is already architectured in this way, but honestly I did not expect it to work until I made more tests.

For your question regarding triggers. The triggers, in SQL are fired by default when a row is modified.
There is no way to fire a trigger based in column value.
So, if we want to update the _tracking tables only when the allowed columns are modified (and not the excluded columns) we should make a request in the trigger asking to compare the last values with the new values.

This is actually possible and we could work on a solution.

BUT :)

Imagine we have an other sync scope that will have all tables in its configuration, with all columns.
In this particular case, we need the trigger to be fired for the entire row, even if it's the excluded columns that raise the trigger.

So far, in your sample, the Server wins the conflict, and then the IsCompleted value will be the value of the server.

You can improve your business rule, adding an interceptor when a conflict is raised (ApplyingFailedChanged event)

[gentledepp]

I knew why I was asking you. So I will skip this dangerous feature altogether ;-)

One more thing about the tests:

I noticed, that the SchemaApplied event is raised, when the complete DmSchema has been built. That totally makes sense. But I believe there should be better validation support in the future:

A source of error might be the fact, that CoreProvider collects all columns and relationships of a table. Thereafter, you may remove a column that is actually required in a relationship.

Another problem could be, that you exclude a non-nullable column which has no default value specified.

So there should be improved validations in general, don't you think?

[Mimetis]

Yes, we should probably do something better.
For now, I think that IF you remove a columns THEN you know what you'are doing so far :)

So, in my mind, If you remove a non nullable column or a foreign key, well.... sorry it's your fault !! :) :)

The most important may be to raise a correct error to be sure the developer will be able to understand why the sync failed...

Seb

[gentledepp]

I absolutely agree.

As we are now implementing the sync functionality and doing so, will test the "sync specific columns" feature, I will keep you posted about any findings. (Also, in case everything works just find ;-))

Alex

[bloggrammer]

Hi @Mimetis and @gentledepp,

How can I ignore certain table columns from being synced?

Thank you for your good work.

[Mimetis]

https://dotmimsync.readthedocs.io/Configuration.html#filtering-columns

You can specify the columns you want to sync:

var tables = new string[] {"ProductCategory", "ProductModel", "Product",
        "Address", "Customer", "CustomerAddress", "SalesOrderHeader", "SalesOrderDetail" };

// Creating a sync setup object
var setup = new SyncSetup(tables);

// Filter columns
setup.Tables["Customer"].Columns.AddRange(new string[] {
    "CustomerID", "EmployeeID", "NameStyle", "FirstName", "LastName" });

setup.Tables["Address"].Columns.AddRange(new string[] {
    "AddressID", "AddressLine1", "City", "PostalCode" });

[bloggrammer]

Thank you for your prompt response.

I'd try it out now and get back to you.

[bloggrammer]

I've specified the columns of interest in the API project but it still did not ignore unspecified columns.

Do I have to implement the same on the client-side? On the client side, I am using the SyncAgent object.

var agent = new SyncAgent(clientProvider, serverProvider, Constants.DATABASE_TABLES);

[bloggrammer]

My bad. I mixed up the database connection string. It's working just fine. :)