Filters Problem in Version 1.0.2

[AbderrahmaneAhmam]

---

Filters Problem in Version 1.0.2

Hello,

I encountered an issue while upgrading to version 1.0.2, specifically with the filters. Upon investigating the code, I found changes in the SQLiteObjectNames.CreateSelectChangesCommandText method. It appears that server-side filters are being applied on the client side. This approach can cause problems because certain aggregation functions used on the server, such as DATEDIFF(DAY, ?, ?), do not have equivalents in SQLite.

Proposed Solution

To address this issue, I suggest the following:

1. Remove Server-Side Filters in Client Commands: First, remove the filters section from the command generated in CreateSelectChangesCommandText to prevent server-side filters from being applied on the client side.

2. Define Client-Side Filters: During the provisioning process, add the necessary filters on the client side. This will provide full control over the filters used, ensuring compatibility with SQLite and other client databases.

If you agree with this approach, I am willing to work on this solution.

Kind regards.

[Mimetis]

God damn, I don't remember why I have enable again the filters on the SQLite side...

What we can do is to disable by default the filters generation when using the SQLiteSyncProvider maybe ?

Something like that ?

var clientProvider = new SqliteSyncProvider(sqliteConnectionString);
clientProvider.DisableSqlFiltersGeneration = true;

And maybe make it true as default ?

[AbderrahmaneAhmam]

I think it's a good idea. I can work on it, and I will give you a correction for this issue as soon as possible.

[AbderrahmaneAhmam]

Temporary Solution for SQL Filters Generation Issue

As a temporary solution for this issue in version 1.0.2, you can customize the command during generation by using the following approach:

_agent.LocalOrchestrator.OnGetCommand(s =>
{
    if (s.CommandType != DbCommandType.SelectChangesWithFilters && s.CommandType != DbCommandType.SelectChanges)
        return;
    s.Command.CommandText = CreateSelectChangesCommandText(s.Table);
});

private string CreateSelectChangesCommandText(SyncTable table)
{
    var stringBuilder = new StringBuilder("");
    stringBuilder.AppendLine("SELECT ");

    foreach (var mutableColumn in table.GetMutableColumns(false, true))
    {
        var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();

        var isPrimaryKey = table.PrimaryKeys.Any(pkey => mutableColumn.ColumnName.Equals(pkey, SyncGlobalization.DataSourceStringComparison));

        if (isPrimaryKey)
            stringBuilder.AppendLine($"\t[side].{columnName}, ");
        else
            stringBuilder.AppendLine($"\t[base].{columnName}, ");
    }
    stringBuilder.AppendLine($"\t[side].[sync_row_is_tombstone], ");
    stringBuilder.AppendLine($"\t[side].[update_scope_id] as [sync_update_scope_id] ");
    stringBuilder.AppendLine($"FROM {table.TableName}_tracking [side]");
    stringBuilder.AppendLine($"LEFT JOIN {table.TableName} [base]");
    stringBuilder.Append("ON ");

    string empty = "";
    foreach (var pkColumn in table.GetPrimaryKeysColumns())
    {
        var columnName = ParserName.Parse(pkColumn).Quoted().ToString();

        stringBuilder.Append($"{empty}[base].{columnName} = [side].{columnName}");
        empty = " AND ";
    }
    stringBuilder.AppendLine();

    stringBuilder.AppendLine("WHERE ([side].[timestamp] > @sync_min_timestamp AND [side].[update_scope_id] IS NULL)");

    return stringBuilder.ToString();
}