public async Task <SyncAnchor> ApplyChangesAsync([NotNull] SyncChangeSet changeSet, [CanBeNull] Func <SyncItem, ConflictResolution> onConflictFunc = null, CancellationToken cancellationToken = default)
{
Validate.NotNull(changeSet, nameof(changeSet));
await InitializeStoreAsync(cancellationToken);
var now = DateTime.Now;
_logger?.Info($"[{_storeId}] Begin ApplyChanges(source={changeSet.SourceAnchor}, target={changeSet.TargetAnchor}, {changeSet.Items.Count} items)");
using (var c = new SqliteConnection(Configuration.ConnectionString)) // +";Foreign Keys=False"))
{
await c.OpenAsync(cancellationToken);
using (var cmd = new SqliteCommand())
{
using (var tr = c.BeginTransaction())
{
cmd.Connection = c;
cmd.Transaction = tr;
try
{
cmd.CommandText = "SELECT MAX(ID) FROM __CORE_SYNC_CT";
var version = await cmd.ExecuteLongScalarAsync(cancellationToken);
cmd.CommandText = "SELECT MIN(ID) FROM __CORE_SYNC_CT";
var minVersion = await cmd.ExecuteLongScalarAsync(cancellationToken);
foreach (var item in changeSet.Items)
{
var table = (SqliteSyncTable)Configuration.Tables.FirstOrDefault(_ => _.Name == item.TableName);
if (table == null)
{
continue;
}
bool syncForceWrite = false;
var itemChangeType = item.ChangeType;
retryWrite:
cmd.Parameters.Clear();
table.SetupCommand(cmd, itemChangeType, item.Values);
cmd.Parameters.Add(new SqliteParameter("@last_sync_version", changeSet.TargetAnchor.Version));
cmd.Parameters.Add(new SqliteParameter("@sync_force_write", syncForceWrite));
int affectedRows = 0;
try
{
affectedRows = await cmd.ExecuteNonQueryAsync(cancellationToken);
if (affectedRows > 0)
{
_logger?.Trace($"[{_storeId}] Successfully applied {item}");
}
}
catch (OperationCanceledException)
{
throw;
}
catch (Exception ex)
{
//throw new SynchronizationException($"Unable to {item} item to store for table {table}", ex);
_logger?.Warning($"Unable to {item} {Environment.NewLine}{ex}");
}
if (affectedRows == 0)
{
if (itemChangeType == ChangeType.Insert)
{
//If we can't apply an insert means that we already
//applied the insert or another record with same values (see primary key)
//is already present in table.
//In any case we can't proceed
cmd.CommandText = table.SelectExistingQuery;
cmd.Parameters.Clear();
var valueItem = item.Values[table.PrimaryColumnName];
cmd.Parameters.Add(new SqliteParameter("@" + table.PrimaryColumnName.Replace(" ", "_"), valueItem.Value ?? DBNull.Value));
if (1 == (long)await cmd.ExecuteScalarAsync(cancellationToken) && !syncForceWrite)
{
itemChangeType = ChangeType.Update;
goto retryWrite;
}
else
{
_logger?.Warning($"Unable to {item}: much probably there is a foreign key constraint issue logged before");
}
}
else if (itemChangeType == ChangeType.Update ||
itemChangeType == ChangeType.Delete)
{
if (syncForceWrite)
{
if (itemChangeType == ChangeType.Delete)
{
//item is already deleted in data store
//so this means that we're going to delete a already deleted record
//i.e. nothing to do
_logger?.Trace($"[{_storeId}] Insert on delete conflict occurred for {item}");
}
else
{
//if user wants to update forcely a deleted record means that
//he asctually wants to insert it again in store
_logger?.Trace($"[{_storeId}] Insert on delete conflict occurred for {item}");
itemChangeType = ChangeType.Insert;
goto retryWrite;
}
}
else
{
//conflict detected
var res = onConflictFunc?.Invoke(item);
if (res.HasValue && res.Value == ConflictResolution.ForceWrite)
{
_logger?.Trace($"[{_storeId}] Force write on conflict occurred for {item}");
syncForceWrite = true;
goto retryWrite;
}
else
{
_logger?.Warning($"[{_storeId}] Skip conflict for {item}");
}
}
}
}
if (affectedRows > 0)
{
cmd.CommandText = "SELECT MAX(ID) FROM __CORE_SYNC_CT";
cmd.Parameters.Clear();
var currentVersion = await cmd.ExecuteLongScalarAsync(cancellationToken);
cmd.CommandText = "UPDATE [__CORE_SYNC_CT] SET [SRC] = @sourceId WHERE [ID] = @version";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@sourceId", changeSet.SourceAnchor.StoreId.ToString());
cmd.Parameters.AddWithValue("@version", currentVersion);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}
}
cmd.CommandText = $"UPDATE [__CORE_SYNC_REMOTE_ANCHOR] SET [REMOTE_VERSION] = @version WHERE [ID] = @id";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@id", changeSet.SourceAnchor.StoreId.ToString());
cmd.Parameters.AddWithValue("@version", changeSet.SourceAnchor.Version);
if (0 == await cmd.ExecuteNonQueryAsync(cancellationToken))
{
cmd.CommandText = "INSERT INTO [__CORE_SYNC_REMOTE_ANCHOR] ([ID], [REMOTE_VERSION]) VALUES (@id, @version)";
await cmd.ExecuteNonQueryAsync(cancellationToken);
}
tr.Commit();
var resAnchor = new SyncAnchor(_storeId, version);
_logger?.Info($"[{_storeId}] Completed ApplyChanges(resAnchor={resAnchor}) in {(DateTime.Now - now).TotalMilliseconds}ms");
return(resAnchor);
}
catch (Exception)
{
tr.Rollback();
throw;
}
}
}
}
}
public async Task <SyncAnchor> ApplyChangesAsync([NotNull] SyncChangeSet changeSet, Func <SyncItem, ConflictResolution> onConflictFunc = null, CancellationToken cancellationToken = default)
{
Validate.NotNull(changeSet, nameof(changeSet));
await InitializeStoreAsync(cancellationToken);
var now = DateTime.Now;
_logger?.Info($"[{_storeId}] Begin ApplyChanges(source={changeSet.SourceAnchor}, target={changeSet.TargetAnchor}, {changeSet.Items.Count} items)");
using (var c = new SqlConnection(Configuration.ConnectionString))
{
var messageLog = new List <SqlInfoMessageEventArgs>();
try
{
c.InfoMessage += (s, e) => messageLog.Add(e);
await c.OpenAsync(cancellationToken);
//await DisableConstraintsForChangeSetTables(c, changeSet);
using (var cmd = new SqlCommand())
{
using (var tr = c.BeginTransaction())
{
cmd.Connection = c;
cmd.Transaction = tr;
try
{
cmd.CommandText = "SELECT MAX(ID) FROM __CORE_SYNC_CT";
var version = await cmd.ExecuteLongScalarAsync(cancellationToken);
cmd.CommandText = "SELECT MIN(ID) FROM __CORE_SYNC_CT";
var minVersion = await cmd.ExecuteLongScalarAsync(cancellationToken);
cmd.CommandText = $"DECLARE @session uniqueidentifier; SET @session = @sync_client_id_binary; SET CONTEXT_INFO @session";
cmd.Parameters.Add(new SqlParameter("@sync_client_id_binary", changeSet.SourceAnchor.StoreId));
await cmd.ExecuteNonQueryAsync(cancellationToken);
cmd.Parameters.Clear();
cmd.CommandText = $"SELECT CONTEXT_INFO()";
var contextInfo = await cmd.ExecuteScalarAsync(cancellationToken);
cmd.Parameters.Clear();
foreach (var item in changeSet.Items)
{
var table = (SqlSyncTable)Configuration.Tables.FirstOrDefault(_ => _.Name == item.TableName);
if (table == null)
{
continue;
}
bool syncForceWrite = false;
var itemChangeType = item.ChangeType;
retryWrite:
cmd.Parameters.Clear();
table.SetupCommand(cmd, itemChangeType, item.Values);
cmd.Parameters.Add(new SqlParameter("@last_sync_version", changeSet.TargetAnchor.Version));
cmd.Parameters.Add(new SqlParameter("@sync_force_write", syncForceWrite));
int affectedRows;
try
{
affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
_logger?.Trace($"[{_storeId}] Successfully applied {item}");
}
}
catch (Exception ex)
{
throw new SynchronizationException($"Unable to {itemChangeType} item {item} to store for table {table}", ex);
}
if (affectedRows == 0)
{
if (itemChangeType == ChangeType.Insert)
{
//If we can't apply an insert means that we already
//applied the insert or another record with same values (see primary key)
//is already present in table.
cmd.CommandText = table.SelectExistingQuery;
cmd.Parameters.Clear();
var valueItem = item.Values[table.PrimaryColumnName];
cmd.Parameters.Add(new SqlParameter("@" + table.PrimaryColumnName.Replace(" ", "_"), table.Columns[table.PrimaryColumnName].DbType)
{
Value = Utils.ConvertToSqlType(valueItem, table.Columns[table.PrimaryColumnName].DbType)
});
if (1 == (int)await cmd.ExecuteScalarAsync(cancellationToken) && !syncForceWrite)
{
itemChangeType = ChangeType.Update;
goto retryWrite;
//_logger?.Trace($"[{_storeId}] Existing record for {item}");
}
else
{
_logger?.Warning($"[{_storeId}] Unable to {itemChangeType} item {item} on table {table}. Messages: Messages:{Environment.NewLine}{string.Join(Environment.NewLine, messageLog.Select(_ => _.Message))}");
}
}
else if (itemChangeType == ChangeType.Update ||
itemChangeType == ChangeType.Delete)
{
if (syncForceWrite)
{
if (itemChangeType == ChangeType.Delete)
{
//item is already deleted in data store
//so this means that we're going to delete a already deleted record
//i.e. nothing to do
_logger?.Trace($"[{_storeId}] Insert on delete conflict occurred for {item}");
}
else
{
//if user wants to update forcely a deleted record means
//he wants to actually insert it again in store
_logger?.Trace($"[{_storeId}] Insert on delete conflict occurred for {item}");
itemChangeType = ChangeType.Insert;
goto retryWrite;
}
}
else
{
//conflict detected
var res = onConflictFunc?.Invoke(item);
if (res.HasValue && res.Value == ConflictResolution.ForceWrite)
{
_logger?.Trace($"[{_storeId}] Force write on conflict occurred for {item}");
syncForceWrite = true;
goto retryWrite;
}
else
{
_logger?.Warning($"[{_storeId}] Skip conflict for {item}");
}
}
}
}
}
cmd.CommandText = $"UPDATE [__CORE_SYNC_REMOTE_ANCHOR] SET [REMOTE_VERSION] = @version WHERE [ID] = @id";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@id", changeSet.SourceAnchor.StoreId.ToString());
cmd.Parameters.AddWithValue("@version", changeSet.SourceAnchor.Version);
if (0 == await cmd.ExecuteNonQueryAsync(cancellationToken))
{
cmd.CommandText = "INSERT INTO [__CORE_SYNC_REMOTE_ANCHOR] ([ID], [REMOTE_VERSION]) VALUES (@id, @version)";
await cmd.ExecuteNonQueryAsync(cancellationToken);
}
tr.Commit();
var resAnchor = new SyncAnchor(_storeId, version);
_logger?.Info($"[{_storeId}] Completed ApplyChanges(resAnchor={resAnchor}) in {(DateTime.Now - now).TotalMilliseconds}ms");
return(resAnchor);
}
catch (Exception)
{
tr.Rollback();
throw;
}
}
}
}
catch (Exception ex)
{
var exceptionMessage = $"An exception occurred during synchronization:{Environment.NewLine}Errors:{Environment.NewLine}{string.Join(Environment.NewLine, messageLog.SelectMany(_ => _.Errors.Cast<SqlError>()))}{Environment.NewLine}Messages:{Environment.NewLine}{string.Join(Environment.NewLine, messageLog.Select(_ => _.Message))}";
throw new SyncErrorException(exceptionMessage, ex);
}
finally
{
//await RestoreConstraintsForChangeSetTables(c, changeSet);
}
}
}