public async Task <SyncVersion> GetSyncVersionAsync(CancellationToken cancellationToken = default) { await InitializeStoreAsync(cancellationToken); using (var c = new SqliteConnection(Configuration.ConnectionString)) { try { await c.OpenAsync(cancellationToken); using (var cmd = new SqliteCommand()) { 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(cancellationToken); cmd.CommandText = "SELECT MIN(ID) FROM __CORE_SYNC_CT"; var minVersion = await cmd.ExecuteLongScalarAsync(cancellationToken); return(new SyncVersion(version, minVersion)); } } } catch (Exception ex) { throw new InvalidOperationException($"Unable to get current/minimum version from store", ex); } } }
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)); } } } }
public async Task <SyncVersion> ApplyRetentionPolicyAsync(int minVersion, CancellationToken cancellationToken = default) { await InitializeStoreAsync(cancellationToken); using (var c = new SqliteConnection(Configuration.ConnectionString)) { try { await c.OpenAsync(); using (var cmd = new SqliteCommand()) { using (var tr = c.BeginTransaction()) { cmd.Connection = c; cmd.Transaction = tr; try { cmd.CommandText = $"DELETE FROM __CORE_SYNC_CT WHERE ID < {minVersion}"; await cmd.ExecuteNonQueryAsync(cancellationToken); 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 newMinVersion = await cmd.ExecuteLongScalarAsync(cancellationToken); tr.Commit(); return(new SyncVersion(version, newMinVersion)); } catch (Exception) { tr.Rollback(); throw; } } } } catch (Exception ex) { throw new InvalidOperationException($"Unable to apply version {minVersion} to tracking table of the store", ex); } } }
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 <SyncChangeSet> GetChangesAsync(Guid otherStoreId, SyncFilterParameter[] syncFilterParameters, SyncDirection syncDirection, CancellationToken cancellationToken = default) { syncFilterParameters = syncFilterParameters ?? new SyncFilterParameter[] { }; var fromAnchor = (await GetLastLocalAnchorForStoreAsync(otherStoreId, cancellationToken)); //if fromVersion is 0 means that client needs actually to initialize its local datastore //await InitializeStoreAsync(); var now = DateTime.Now; _logger?.Info($"[{_storeId}] Begin GetChanges(from={otherStoreId}, syncDirection={syncDirection}, fromVersion={fromAnchor})"); using (var c = new SqliteConnection(Configuration.ConnectionString)) { await c.OpenAsync(cancellationToken); using (var cmd = new SqliteCommand()) { var items = new List <SqliteSyncItem>(); using (var tr = c.BeginTransaction()) { cmd.Connection = c; cmd.Transaction = tr; try { cmd.CommandText = "SELECT MAX(ID) FROM __CORE_SYNC_CT"; cmd.Parameters.Clear(); var version = await cmd.ExecuteLongScalarAsync(cancellationToken); cmd.CommandText = "SELECT MIN(ID) FROM __CORE_SYNC_CT"; cmd.Parameters.Clear(); var minVersion = await cmd.ExecuteLongScalarAsync(cancellationToken); if (!fromAnchor.IsNull() && fromAnchor.Version < minVersion - 1) { throw new InvalidOperationException($"Unable to get changes, version of data requested ({fromAnchor}) is too old (min valid version {minVersion})"); } foreach (var table in Configuration.Tables.Cast <SqliteSyncTable>().Where(_ => _.Columns.Any())) { if (table.SyncDirection != SyncDirection.UploadAndDownload && table.SyncDirection != syncDirection) { continue; } //var snapshotItems = new HashSet<object>(); if (fromAnchor.IsNull() && !table.SkipInitialSnapshot) { cmd.CommandText = table.InitialSnapshotQuery; cmd.Parameters.Clear(); foreach (var syncFilterParameter in syncFilterParameters) { cmd.Parameters.AddWithValue(syncFilterParameter.Name, syncFilterParameter.Value); } using (var r = await cmd.ExecuteReaderAsync(cancellationToken)) { while (await r.ReadAsync(cancellationToken)) { var values = Enumerable.Range(0, r.FieldCount).ToDictionary(_ => r.GetName(_), _ => GetValueFromRecord(table, r.GetName(_), _, r)); items.Add(new SqliteSyncItem(table, ChangeType.Insert, values)); //snapshotItems.Add(values[table.PrimaryColumnName]); _logger?.Trace($"[{_storeId}] Initial snapshot {items.Last()}"); } } } if (!fromAnchor.IsNull()) { cmd.CommandText = table.IncrementalAddOrUpdatesQuery; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@version", fromAnchor.Version); cmd.Parameters.AddWithValue("@sourceId", otherStoreId.ToString()); foreach (var syncFilterParameter in syncFilterParameters) { cmd.Parameters.AddWithValue(syncFilterParameter.Name, syncFilterParameter.Value); } using (var r = await cmd.ExecuteReaderAsync(cancellationToken)) { while (await r.ReadAsync(cancellationToken)) { var values = Enumerable.Range(0, r.FieldCount).ToDictionary(_ => r.GetName(_), _ => GetValueFromRecord(table, r.GetName(_), _, r)); //if (snapshotItems.Contains(values[table.PrimaryColumnName])) // continue; items.Add(new SqliteSyncItem(table, DetectChangeType(values), values.Where(_ => _.Key != "__OP").ToDictionary(_ => _.Key, _ => _.Value == DBNull.Value ? null : _.Value))); _logger?.Trace($"[{_storeId}] Incremental add or update {items.Last()}"); } } cmd.CommandText = table.IncrementalDeletesQuery; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@version", fromAnchor.Version); cmd.Parameters.AddWithValue("@sourceId", otherStoreId.ToString()); using (var r = await cmd.ExecuteReaderAsync(cancellationToken)) { while (await r.ReadAsync(cancellationToken)) { var values = Enumerable.Range(0, r.FieldCount).ToDictionary(_ => r.GetName(_), _ => GetValueFromRecord(table, r.GetName(_), _, r)); items.Add(new SqliteSyncItem(table, ChangeType.Delete, values)); _logger?.Trace($"[{_storeId}] Incremental delete {items.Last()}"); } } } } tr.Commit(); var resChangeSet = new SyncChangeSet(new SyncAnchor(_storeId, version), await GetLastRemoteAnchorForStoreAsync(otherStoreId, cancellationToken), items); _logger?.Info($"[{_storeId}] Completed GetChanges(to={version}, {items.Count} items) in {(DateTime.Now - now).TotalMilliseconds}ms"); return(resChangeSet); } catch (Exception) { tr.Rollback(); throw; } } } } }
public async Task <SyncAnchor> ApplyChangesAsync([NotNull] SyncChangeSet changeSet, [CanBeNull] Func <SyncItem, ConflictResolution> onConflictFunc = null) { Validate.NotNull(changeSet, nameof(changeSet)); if (changeSet.TargetAnchor.StoreId != _storeId) { throw new ArgumentException("Invalid anchor store id"); } await InitializeAsync(); using (var c = new SqliteConnection(Configuration.ConnectionString)) { await c.OpenAsync(); using (var cmd = new SqliteCommand()) { 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 (changeSet.TargetAnchor.Version < minVersion - 1) { throw new InvalidOperationException($"Unable to apply changes, version of data requested ({changeSet.TargetAnchor.Version}) is too old (min valid version {minVersion})"); } foreach (var item in changeSet.Items) { var table = (SqliteSyncTable)Configuration.Tables.First(_ => _.Name == item.Table.Name); 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 SqliteParameter("@last_sync_version", changeSet.TargetAnchor.Version)); cmd.Parameters.Add(new SqliteParameter("@sync_force_write", syncForceWrite)); foreach (var valueItem in item.Values) { cmd.Parameters.Add(new SqliteParameter("@" + 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 delete 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; } } } } cmd.CommandText = "SELECT MAX(ID) FROM __CORE_SYNC_CT"; version = await cmd.ExecuteLongScalarAsync(); cmd.Parameters.Clear(); cmd.CommandText = "UPDATE [__CORE_SYNC_REMOTE_ANCHOR] SET [VERSION] = @version WHERE [ID] = @id"; cmd.Parameters.AddWithValue("@id", changeSet.SourceAnchor.StoreId.ToString()); cmd.Parameters.AddWithValue("@version", 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.ToString()); cmd.Parameters.AddWithValue("@version", version); await cmd.ExecuteNonQueryAsync(); } tr.Commit(); return(new SyncAnchor(_storeId, version)); } } } }