public async Task <SyncChangeSet> GetIncrementalChangesAsync([NotNull] SyncAnchor anchor)
{
Validate.NotNull(anchor, nameof(anchor));
await InitializeAsync();
using (var c = new SqliteConnection(Configuration.ConnectionString))
{
await c.OpenAsync();
using (var cmd = new SqliteCommand())
{
var items = new List <SqliteSyncItem>();
using (var tr = c.BeginTransaction())
{
cmd.Connection = c;
cmd.Transaction = tr;
cmd.CommandText = "SELECT MAX(ID) FROM __CORE_SYNC_CT";
var version = await cmd.ExecuteLongScalarAsync();
cmd.CommandText = "SELECT MIN(ID) FROM __CORE_SYNC_CT";
var minVersion = await cmd.ExecuteLongScalarAsync();
if (anchor.Version < minVersion - 1)
{
throw new InvalidOperationException($"Unable to get changes, version of data requested ({anchor.Version}) is too old (min valid version {minVersion})");
}
foreach (var table in Configuration.Tables.Cast <SqliteSyncTable>().Where(_ => _.Columns.Any()))
{
var primaryKeyColumns = table.Columns.Where(_ => _.IsPrimaryKey);
cmd.CommandText = $@"SELECT DISTINCT {string.Join(",", table.Columns.Select(_ => "T.[" + _.Name + "]"))}, MIN(CT.OP) AS OP FROM [{table.Schema}].[{table.Name}] AS T INNER JOIN __CORE_SYNC_CT AS CT ON printf('{string.Join("", primaryKeyColumns.Select(_ => TypeToPrintFormat(_.Type)))}', {string.Join(", ", primaryKeyColumns.Select(_ => "T.[" + _.Name + "]"))}) = CT.PK WHERE CT.Id > {anchor.Version}";
using (var r = await cmd.ExecuteReaderAsync())
{
while (await r.ReadAsync())
{
var values = Enumerable.Range(0, r.FieldCount).ToDictionary(_ => r.GetName(_), _ => GetValueFromRecord(table, r.GetName(_), _, r));
if (values["OP"] != null)
{
items.Add(new SqliteSyncItem(table, DetectChangeType(values), values));
}
}
}
}
tr.Commit();
return(new SyncChangeSet(new SyncAnchor(_storeId, version), anchor, items));
}
}
}
}
private async Task <SyncChangeSet> GetIncrementalChangesAsync(SyncAnchor otherStoreAnchor)
{
Validate.NotNull(otherStoreAnchor, nameof(otherStoreAnchor));
await InitializeAsync();
using (var c = new SqlConnection(Configuration.ConnectionString))
{
await c.OpenAsync();
using (var cmd = new SqlCommand())
{
var items = new List <SqlSyncItem>();
using (var tr = c.BeginTransaction(IsolationLevel.Snapshot))
{
cmd.Connection = c;
cmd.Transaction = tr;
cmd.CommandText = "SELECT CHANGE_TRACKING_CURRENT_VERSION()";
long version = (long)await cmd.ExecuteScalarAsync();
foreach (SqlSyncTable table in Configuration.Tables)
{
cmd.CommandText = $"SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('{table.Schema}.[{table.Name}]'))";
long minVersionForTable = (long)await cmd.ExecuteScalarAsync();
if (otherStoreAnchor.Version < minVersionForTable)
{
throw new InvalidOperationException($"Unable to get changes, version of data requested ({otherStoreAnchor.Version}) for table '{table.Schema}.[{table.Name}]' is too old (min valid version {minVersionForTable})");
}
cmd.CommandText = table.IncrementalDataQuery.Replace("@last_synchronization_version", otherStoreAnchor.Version.ToString());
using (var r = await cmd.ExecuteReaderAsync())
{
while (await r.ReadAsync())
{
var values = Enumerable.Range(0, r.FieldCount).ToDictionary(_ => r.GetName(_), _ => r.GetValue(_));
items.Add(new SqlSyncItem(table, DetectChangeType(values), values));
}
}
}
tr.Commit();
return(new SyncChangeSet(new SyncAnchor(_storeId, version), otherStoreAnchor, items));
}
}
}
}
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);
}
}
}
public async Task <SyncAnchor> ApplyChangesAsync([NotNull] SyncChangeSet changeSet, Func <SyncItem, ConflictResolution> onConflictFunc = null)
{
Validate.NotNull(changeSet, nameof(changeSet));
await InitializeAsync();
if (changeSet.TargetAnchor.StoreId != _storeId)
{
throw new InvalidOperationException("ChangeSet doesn't target this store");
}
using (var c = new SqlConnection(Configuration.ConnectionString))
{
await c.OpenAsync();
using (var cmd = new SqlCommand())
{
using (var tr = c.BeginTransaction(IsolationLevel.Snapshot))
{
cmd.Connection = c;
cmd.Transaction = tr;
cmd.CommandText = "SELECT CHANGE_TRACKING_CURRENT_VERSION()";
long version = (long)await cmd.ExecuteScalarAsync();
bool atLeastOneChangeApplied = false;
foreach (var item in changeSet.Items)
{
var table = (SqlSyncTable)Configuration.Tables.First(_ => _.Name == item.Table.Name);
cmd.Parameters.Clear();
cmd.CommandText = $"SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('{table.Schema}.[{table.Name}]'))";
long minVersionForTable = (long)await cmd.ExecuteScalarAsync();
if (changeSet.TargetAnchor.Version < minVersionForTable)
{
throw new InvalidOperationException($"Unable to apply changes, version of data to apply ({changeSet.TargetAnchor.Version}) for table '{table.Schema}.[{table.Name}]' is too old (min valid version {minVersionForTable})");
}
bool syncForceWrite = false;
var itemChangeType = item.ChangeType;
retryWrite:
cmd.Parameters.Clear();
switch (itemChangeType)
{
case ChangeType.Insert:
cmd.CommandText = table.InsertQuery;
break;
case ChangeType.Update:
cmd.CommandText = table.UpdateQuery;
break;
case ChangeType.Delete:
cmd.CommandText = table.DeleteQuery;
break;
}
cmd.Parameters.Add(new SqlParameter("@last_sync_version", changeSet.TargetAnchor.Version));
cmd.Parameters.Add(new SqlParameter("@sync_force_write", syncForceWrite));
foreach (var valueItem in item.Values)
{
cmd.Parameters.Add(new SqlParameter("@" + valueItem.Key.Replace(" ", "_"), valueItem.Value ?? DBNull.Value));
}
var affectedRows = cmd.ExecuteNonQuery();
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
throw new InvalidSyncOperationException(new SyncAnchor(_storeId, changeSet.TargetAnchor.Version + 1));
}
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
}
else
{
//if user wants to update forcely a deletes record means
//he wants to actually insert it again in store
itemChangeType = ChangeType.Insert;
goto retryWrite;
}
}
//conflict detected
var res = onConflictFunc?.Invoke(item);
if (res.HasValue && res.Value == ConflictResolution.ForceWrite)
{
syncForceWrite = true;
goto retryWrite;
}
}
}
else
{
atLeastOneChangeApplied = true;
}
}
var newAnchor = new SyncAnchor(_storeId, version + (atLeastOneChangeApplied ? 1 : 0));
cmd.Parameters.Clear();
cmd.CommandText = "UPDATE [__CORE_SYNC_REMOTE_ANCHOR] SET [VERSION] = @version WHERE [ID] = @id";
cmd.Parameters.AddWithValue("@id", changeSet.SourceAnchor.StoreId);
cmd.Parameters.AddWithValue("@version", newAnchor.Version);
if (0 == await cmd.ExecuteNonQueryAsync())
{
cmd.Parameters.Clear();
cmd.CommandText = "INSERT INTO [__CORE_SYNC_REMOTE_ANCHOR] ([ID], [VERSION]) VALUES (@id, @version)";
cmd.Parameters.AddWithValue("@id", changeSet.SourceAnchor.StoreId);
cmd.Parameters.AddWithValue("@version", newAnchor.Version);
await cmd.ExecuteNonQueryAsync();
}
tr.Commit();
return(newAnchor);
}
}
}
}