Implementation Plan: is_dirty Change Tracking for SQLite

[gentledepp]

Replace timestamp-based change tracking in Dotmim.Sync.Sqlite with a dirty-flag approach using [is_dirty] and [sync_session_id] columns to prevent issues from users changing system time on mobile devices.

Core Changes

1. Add New DbCommandType Enums (DbCommandType.cs:152)

• Add MarkRowAsSyncing - Sets sync_session_id on tracking rows when changes are selected
• Add MarkRowAsSynced - Clears is_dirty and sync_session_id after successful sync

2. Update Tracking Table Schema (SqliteTableBuilder.cs:178-234)

Already has the columns (lines 197-198):

• [is_dirty] - already exists with default(0)
• [sync_session_id] - already exists with COLLATE NOCASE
• Update index at line 230 to include is_dirty

3. Update Triggers (SqliteTableBuilder.cs:359-519)

• Insert Trigger (line 359): Set [is_dirty]=1 and [sync_session_id]=NULL on insert
• Update Trigger (line 454): Set [is_dirty]=1 and [sync_session_id]=NULL on update
• Delete Trigger (line 407): Set [is_dirty]=1 and [sync_session_id]=NULL on delete

4. Update SelectChanges Query (SQLiteObjectNames.cs:688)

• Replace timestamp check ([side].[timestamp] > @sync_min_timestamp...) (line 764)
• Use [is_dirty] = 1 instead
• Keep [update_scope_id] IS NULL check to avoid selecting rows from server-side sync

5. Add MarkRowAsSyncing Command (SQLiteObjectNames.cs:109-133)

Create new command that:

• Updates tracking table rows with [is_dirty]=1
• Sets [sync_session_id] = @sync_session_id (context.SessionId)
• Executed before SelectChanges reads the data

6. Add MarkRowAsSynced Command (SQLiteObjectNames.cs:109-133)

Create new command that:

• Updates tracking table where [sync_session_id] = @sync_session_id
• Sets [is_dirty]=0 and [sync_session_id]=NULL
• Executed after successful sync completion

7. Orchestrator Integration

• BaseOrchestrator.GetChanges.cs: Call MarkRowAsSyncing before reading changes (around line 350)
• BaseOrchestrator.ApplyChanges.cs: Call MarkRowAsSynced after successful apply (end of sync flow)
• Pass context.SessionId to both commands

8. Update SqliteSyncAdapter (SQLiteSyncAdapter.cs:48)

• Handle new DbCommandType cases in GetCommand method
• Return appropriate command text for MarkRowAsSyncing and MarkRowAsSynced

Key Benefits

• Race condition protection: If a row changes during sync (after MarkRowAsSyncing but before MarkRowAsSynced), its sync_session_id will be NULL, so it won't be marked as
  synced
• Time-independent: No reliance on system clock
• Backward compatible: Existing tracking table already has required columns

Files to Modify

1. /Projects/Dotmim.Sync.Core/Builders/DbCommandType.cs
2. /Projects/Dotmim.Sync.Sqlite/Builders/SqliteTableBuilder.cs
3. /Projects/Dotmim.Sync.Sqlite/Builders/SQLiteObjectNames.cs
4. /Projects/Dotmim.Sync.Sqlite/SQLiteSyncAdapter.cs
5. /Projects/Dotmim.Sync.Core/Orchestrators/GetChanges/BaseOrchestrator.GetChanges.cs
6. /Projects/Dotmim.Sync.Core/Orchestrators/ApplyChanges/BaseOrchestrator.ApplyChanges.cs (or LocalOrchestrator)

[Mimetis]

I didn't look at your implementation, but it can't work
We need to have a timestamp, to specify a range when making a sync

[gentledepp]

when exactly do you need that:

specify a range when making a sync

I did not drop the timestamp column, just added two additional ones (is_dirty and sync_session_id)
These are only used to detect on the sqlite side, which changes need to be sent to the server.
The server never sees any is_dirty or sync_session_id, only the timestamp.

This feature is implemented and tested already - all tests green. So "wha can't it work?"