예제 #1
0
        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));
                    }
                }
            }
        }
예제 #2
0
        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;
                        }
                    }
                }
            }
        }
예제 #4
0
        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);
                }
            }
        }
예제 #5
0
        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);
                    }
                }
            }
        }