关于数据同步的探索（一）

• 关于数据同步的探索（一） - wzwyc - 博客园
• 关于数据同步的探索（二） - wzwyc - 博客园
• 关于数据同步的探索（三） - wzwyc - 博客园

开发过程中，自己经常会弄一些小工具。
考虑到数据的同步，经常会用自己云服务器上的数据库。
但是采用这种方式的缺点是，如果网络环境比较差的时候，比方说，在客户单位，用手机流量来访问，有时候信号还不是特别好的时候，体验就不是特别好。
甚至有时候，希望离线能操作。
其实我最喜欢的方式还是【离线操作】+【数据同步】的功能。
所以有时候就觉得GIT真的非常方便，平常完全可以离线操作，需要的时候进行同步。
然后开始对数据的同步方式加了各种探索。

最开始的方式是：
对每个数据都加一个DataGuid，数据被初次创建的时候自动生成，一旦创建后就不会变，作为这个数据的唯一标识。
然后每个数据再加一个DataVersionGuid，每次对数据进行修改的时候进行更新，用于同步时对同一个DataGuid的数据进行是否相同的比较。
仅仅知道两个数据是否相同还不够，还需要知道两个DataVersionGuid之间的关系。
所以又弄了一个VersionHistory的类

using FreeSql.DataAnnotations;

namespace NugetManager.Models;

[Table(Name = "ng_version_history")]
public class VersionHistory : IBaseInfo
{
    [Column(IsIdentity = true, IsPrimary = true)]
    public int ID { get; set; }

    [Column(IsNullable = false)]
    public string HistoryGuid { get; set; }

    public string DataType { get; set; }
    public OperationType Operation { get; set; }

    [Column(IsNullable = false)]
    public string DataGuid { get; set; }

    [Column(IsNullable = false)]
    public string NewVersionGuid { get; set; }

    [Column(IsNullable = false)]
    public string OldVersionGuid { get; set; }

    public DateTime CreateTime { get; set; }

    public VersionHistory()
    {
        CreateTime = DateTime.Now;
        HistoryGuid = System.Guid.NewGuid().ToString();
    }

    public VersionHistory(string dataType, OperationType operation, string dataGuid, string oldVersionGuid, string newVersionGuid) : this()
    {
        DataType = dataType;
        Operation = operation;
        DataGuid = dataGuid;
        OldVersionGuid = oldVersionGuid;
        NewVersionGuid = newVersionGuid;
    }
}

public enum OperationType
{
    Add,
    Edit,
    Delete
}

HistoryGuid：VersionHistory数据本身的Guid，作为VersionHistory数据的唯一标识，创建数据时自动生成。
DataType：用来标识这个是哪种数据的历史数据，作为过滤条件用。
DataGuid: 发生变更的数据的DataGuid
Operation：操作类型，主要是增删改这三种操作
NewVersionGuid：变更前的DataVersionGuid
OldVersionGuid：变更后的DataVersionGuid

相当于对数据增删改的时候，都生成一条VersionHistory记录。
后续需要同步的时候，根据VersionHistory来决定如何进行数据操作。

这种方式的缺点：
1、对数据的准确要求很高，必须都是通过这种方式生成的。对于像我一样，拿之前的工具和数据来改的，比较容易因为版本数据不对，导致同步出错。
2、多了很多的版本数据，相当于每进行一次操作都必须严格记录。
3、还是会有冲突的问题，相当于一个版本A，一个终端把A改成了B，另一个终端把A改成了C，然后进行同步的时候，B和C是没有继承关系的，所以还是需要人为决定以哪个为准。
4、逻辑步骤有点多，出错的概念相对会更高。

因为跟理想的方便安全还是有点差距，于是我又开始进一步的探索，看我的下一个帖子。

下面是我用来操作数据的类：

using NugetManager;

namespace CgdataBase;

public partial class DataHelper : IDisposable
{
    public int AddNugetInfo(NugetInfo info)
    {
        var versionGuid = Guid.NewGuid().ToString();
        var version = new VersionHistory(nameof(NugetInfo), OperationType.Add, info.NugetGuid, null, versionGuid);
        AddDataInfo(version);

        info.VersionGuid = versionGuid;
        info.UpdateTime = DateTime.Now;
        return AddDataInfo(info);
    }

    public void AddNugetInfos(IEnumerable<NugetInfo> items)
    {
        var versionList = new List<VersionHistory>();
        items.ForEach(s =>
        {
            var versionGuid = Guid.NewGuid().ToString();
            versionList.Add(new VersionHistory(nameof(NugetInfo), OperationType.Add, s.NugetGuid, null, versionGuid));

            s.VersionGuid = versionGuid;
            s.UpdateTime = DateTime.Now;
        });
        AddDataInfos(versionList);

        AddDataInfos(items);
    }

    public void UpdateNugetInfo(NugetInfo info)
    {
        var versionGuid = Guid.NewGuid().ToString();
        var version = new VersionHistory(nameof(NugetInfo), OperationType.Edit, info.NugetGuid, info.VersionGuid, versionGuid);
        AddDataInfo(version);

        info.VersionGuid = versionGuid;
        info.UpdateTime = DateTime.Now;
        UpdateDataInfo(info);
    }

    public void UpdateNugetInfos(IEnumerable<NugetInfo> items)
    {
        var versionList = new List<VersionHistory>();
        items.ForEach(s =>
        {
            var versionGuid = Guid.NewGuid().ToString();
            versionList.Add(new VersionHistory(nameof(NugetInfo), OperationType.Edit, s.NugetGuid, s.VersionGuid, versionGuid));

            s.VersionGuid = versionGuid;
            s.UpdateTime = DateTime.Now;
        });
        AddDataInfos(versionList);

        UpdateDataInfos(items);
    }

    public void DeleteNugetInfos(IEnumerable<NugetInfo> items)
    {
        var versionList = new List<VersionHistory>();
        items.ForEach(item =>
        {
            versionList.Add(new VersionHistory(nameof(NugetInfo), OperationType.Delete, item.NugetGuid, item.VersionGuid, null));
        });
        AddDataInfos(versionList);

        DeleteDataInfos(items);
    }

    public int AddGroupInfo(GroupInfo info)
    {
        var versionGuid = Guid.NewGuid().ToString();
        var version = new VersionHistory(nameof(GroupInfo), OperationType.Add, info.GroupGuid, null, versionGuid);
        AddDataInfo(version);

        info.VersionGuid = versionGuid;
        info.UpdateTime = DateTime.Now;
        return AddDataInfo(info);
    }

    public void AddGroupInfos(IEnumerable<GroupInfo> items)
    {
        var versionList = new List<VersionHistory>();
        items.ForEach(s =>
        {
            var versionGuid = Guid.NewGuid().ToString();
            versionList.Add(new VersionHistory(nameof(GroupInfo), OperationType.Add, s.GroupGuid, null, versionGuid));

            s.VersionGuid = versionGuid;
            s.UpdateTime = DateTime.Now;
        });
        AddDataInfos(versionList);

        AddDataInfos(items);
    }

    public void UpdateGroupInfo(GroupInfo info)
    {
        var versionGuid = Guid.NewGuid().ToString();
        var version = new VersionHistory(nameof(GroupInfo), OperationType.Edit, info.GroupGuid, info.VersionGuid, versionGuid);
        AddDataInfo(version);

        info.VersionGuid = versionGuid;
        info.UpdateTime = DateTime.Now;
        UpdateDataInfo(info);
    }

    public void UpdateGroupInfos(IEnumerable<GroupInfo> items)
    {
        var versionList = new List<VersionHistory>();
        items.ForEach(s =>
        {
            var versionGuid = Guid.NewGuid().ToString();
            versionList.Add(new VersionHistory(nameof(GroupInfo), OperationType.Edit, s.GroupGuid, s.VersionGuid, versionGuid));

            s.VersionGuid = versionGuid;
            s.UpdateTime = DateTime.Now;
        });
        AddDataInfos(versionList);

        UpdateDataInfos(items);
    }

    public void DeleteGroupInfo(GroupInfo info)
    {
        var version = new VersionHistory(nameof(GroupInfo), OperationType.Delete, info.GroupGuid, info.VersionGuid, null);
        AddDataInfo(version);

        DeleteDataInfo(info);
    }

    public void UpdateDatabase()
    {
        OnUpdateGroupInfo();
        OnUpdateNugetInfo();
    }

    private void OnUpdateNugetInfo()
    {
        var items = SelectAll<NugetInfo>();
        items.ForEach(s =>
        {
            s.NugetGuid = Guid.NewGuid().ToString();
            s.VersionGuid = Guid.NewGuid().ToString();
        });
        UpdateDataInfos(items);
    }

    private void OnUpdateGroupInfo()
    {
        var items = SelectAll<GroupInfo>();
        items.ForEach(s => s.VersionGuid = Guid.NewGuid().ToString());
        UpdateDataInfos(items);
    }

    [Logging]
    public static void SynchronizeData(DataHelper local, AppSettings settings)
    {
        using (var remote = new DataHelper())
        {
            remote.Initialize(settings.DatabaseType, settings.DatabaseConnectionString, true);

            var now = DateTime.Now;
            SynchronizeData(local, remote, settings.LastSyncTime);
            settings.LastSyncTime = now;
            settings.Save();
        }
    }

    public static void SynchronizeData(DataHelper local, DataHelper remote, DateTime time)
    {
        SynchronizeVersionHistory(local, remote, time);
        SynchronizeGroupInfo(local, remote, time);
        SynchronizeNugetInfo(local, remote, time);
    }

    private static void SynchronizeNugetInfo(DataHelper local, DataHelper remote, DateTime time)
    {
        var remoteItems = remote.Select<NugetInfo>(s => s.UpdateTime >= time);
        var localItems = local.Select<NugetInfo>(s => s.UpdateTime >= time);

        if (remoteItems.IsNullOrEmpty() && localItems.IsNullOrEmpty())
            return;

        foreach (var remoteInfo in remoteItems)
        {
            var localInfo = local.First<NugetInfo>(s => s.NugetGuid == remoteInfo.NugetGuid);
            if (localInfo == null)
            {
                var del = local.First<VersionHistory>(s => s.DataGuid == remoteInfo.NugetGuid && s.Operation == OperationType.Delete);
                if (del != null && del.OldVersionGuid == remoteInfo.VersionGuid)
                {
                    remote.DeleteDataInfo(remoteInfo);
                }
                else
                {
                    local.AddDataInfo(remoteInfo);
                }
            }
            else
            {
                CompareDataVersion(local, remote, localInfo, remoteInfo);
            }
        }

        foreach (var localInfo in localItems)
        {
            var remoteInfo = remote.First<NugetInfo>(s => s.NugetGuid == localInfo.NugetGuid);
            if (remoteInfo == null)
            {
                var del = remote.First<VersionHistory>(s => s.DataGuid == localInfo.NugetGuid && s.Operation == OperationType.Delete);
                if (del != null && del.OldVersionGuid == localInfo.VersionGuid)
                {
                    local.DeleteDataInfo(localInfo);
                }
                else
                {
                    remote.AddDataInfo(localInfo);
                }
            }
            else
            {
                CompareDataVersion(local, remote, localInfo, remoteInfo);
            }
        }
    }

    private static void CompareDataVersion(DataHelper local, DataHelper remote, NugetInfo localInfo, NugetInfo remoteInfo)
    {
        if (localInfo.VersionGuid != remoteInfo.VersionGuid)
        {
            var result = CompareDataVersion(local, localInfo.NugetGuid, localInfo.VersionGuid, remoteInfo.VersionGuid);
            if (result > 0)
            {
                CopyNugetInfo(local, localInfo, remoteInfo);
            }
            else if (result < 0)
            {
                CopyNugetInfo(remote, remoteInfo, localInfo);
            }
            else
            {
                var localText = localInfo.ToString();
                var remoteText = remoteInfo.ToString();
                if (localInfo.Compare(remoteInfo))
                {
                    CopyNugetInfo(local, localInfo, remoteInfo);
                }
                else
                {
                    App.Current.Dispatcher.Invoke(() =>
                    {
                        var win = new WinDiffViewer(localText, remoteText) { Owner = App.Current.MainWindow };
                        if (win.ShowDialog() == true)
                        {
                            switch (win.Operations)
                            {
                                case CompareOperations.Abort:
                                    throw new Exception("同步已中断");

                                case CompareOperations.Remote:
                                    CopyNugetInfo(local, localInfo, remoteInfo);
                                    break;

                                case CompareOperations.Local:
                                    CopyNugetInfo(remote, remoteInfo, localInfo);
                                    break;

                                case CompareOperations.Skip:
                                    break;

                                default:
                                    break;
                            }
                        }
                        else
                        {
                            throw new Exception("同步已中断");
                        }
                    });
                }
            }
        }
    }

    private static void CopyNugetInfo(DataHelper local, NugetInfo localInfo, NugetInfo remoteInfo)
    {
        localInfo.Name = remoteInfo.Name;
        localInfo.PackageName = remoteInfo.PackageName;

        localInfo.Framework = remoteInfo.Framework;
        localInfo.LowVersion = remoteInfo.LowVersion;
        localInfo.LowFramework = remoteInfo.LowFramework;
        localInfo.WebUrl = remoteInfo.WebUrl;
        localInfo.Remark = remoteInfo.Remark;
        localInfo.UsageTimes = remoteInfo.UsageTimes;
        localInfo.Level = remoteInfo.Level;
        localInfo.GroupGuid = remoteInfo.GroupGuid;

        localInfo.CreateTime = remoteInfo.CreateTime;
        localInfo.UpdateTime = remoteInfo.UpdateTime;

        localInfo.SortIndex = remoteInfo.SortIndex;
        localInfo.VersionGuid = remoteInfo.VersionGuid;

        local.UpdateDataInfo(localInfo);
    }

    private static void SynchronizeGroupInfo(DataHelper local, DataHelper remote, DateTime time)
    {
        var remoteItems = remote.Select<GroupInfo>(s => s.CreateTime >= time);
        var localItems = local.Select<GroupInfo>(s => s.CreateTime >= time);

        if (remoteItems.IsNullOrEmpty() && localItems.IsNullOrEmpty())
            return;

        foreach (var remoteInfo in remoteItems)
        {
            var localInfo = local.First<GroupInfo>(s => s.GroupGuid == remoteInfo.GroupGuid);
            if (localInfo == null)
            {
                var del = local.First<VersionHistory>(s => s.DataGuid == remoteInfo.GroupGuid && s.Operation == OperationType.Delete);
                if (del != null && del.OldVersionGuid == remoteInfo.VersionGuid)
                {
                    remote.DeleteDataInfo(remoteInfo);
                }
                else
                {
                    local.AddDataInfo(remoteInfo);
                }
            }
            else
            {
                CompareClassVersion(local, remote, localInfo, remoteInfo);
            }
        }

        foreach (var localInfo in localItems)
        {
            var remoteInfo = remote.First<GroupInfo>(s => s.GroupGuid == localInfo.GroupGuid);
            if (remoteInfo == null)
            {
                var del = remote.First<VersionHistory>(s => s.DataGuid == localInfo.GroupGuid && s.Operation == OperationType.Delete);
                if (del != null && del.OldVersionGuid == localInfo.VersionGuid)
                {
                    local.DeleteDataInfo(localInfo);
                }
                else
                {
                    remote.AddDataInfo(localInfo);
                }
            }
            else
            {
                CompareClassVersion(local, remote, localInfo, remoteInfo);
            }
        }
    }

    private static void CompareClassVersion(DataHelper local, DataHelper remote, GroupInfo localInfo, GroupInfo remoteInfo)
    {
        if (localInfo.VersionGuid != remoteInfo.VersionGuid)
        {
            var result = CompareDataVersion(local, localInfo.GroupGuid, localInfo.VersionGuid, remoteInfo.VersionGuid);
            if (result > 0)
            {
                CopyGroupInfo(local, localInfo, remoteInfo);
            }
            else if (result < 0)
            {
                CopyGroupInfo(remote, remoteInfo, localInfo);
            }
            else
            {
                var localText = localInfo.ToString();
                var remoteText = remoteInfo.ToString();
                if (localInfo.Compare(remoteInfo))
                {
                    CopyGroupInfo(local, localInfo, remoteInfo);
                }
                else
                {
                    App.Current.Dispatcher.Invoke(() =>
                    {
                        var win = new WinDiffViewer(localText, remoteText) { Owner = App.Current.MainWindow };
                        if (win.ShowDialog() == true)
                        {
                            switch (win.Operations)
                            {
                                case CompareOperations.Abort:
                                    throw new Exception("同步已中断");

                                case CompareOperations.Remote:
                                    CopyGroupInfo(local, localInfo, remoteInfo);
                                    break;

                                case CompareOperations.Local:
                                    CopyGroupInfo(remote, remoteInfo, localInfo);
                                    break;

                                case CompareOperations.Skip:
                                    break;

                                default:
                                    break;
                            }
                        }
                        else
                        {
                            throw new Exception("同步已中断");
                        }
                    });
                }
            }
        }
    }

    private static void CopyGroupInfo(DataHelper local, GroupInfo localInfo, GroupInfo remoteInfo)
    {
        localInfo.GroupName = remoteInfo.GroupName;
        localInfo.CreateTime = remoteInfo.CreateTime;
        localInfo.UpdateTime = remoteInfo.UpdateTime;
        localInfo.GroupGuid = remoteInfo.GroupGuid;
        localInfo.Count = remoteInfo.Count;
        localInfo.SortIndex = remoteInfo.SortIndex;
        localInfo.VersionGuid = remoteInfo.VersionGuid;

        local.UpdateDataInfo(localInfo);
    }

    private static void SynchronizeVersionHistory(DataHelper local, DataHelper remote, DateTime time)
    {
        var remoteItems = remote.Select<VersionHistory>(s => s.CreateTime >= time);
        var localItems = local.Select<VersionHistory>(s => s.CreateTime >= time);

        if (remoteItems.IsNullOrEmpty() && localItems.IsNullOrEmpty())
            return;

        var remoteGuids = remoteItems.Select(s => s.HistoryGuid).ToArray();
        var localGuids = localItems.Select(s => s.HistoryGuid).ToArray();

        var remoteAdds = remoteGuids.Except(localGuids).ToArray();
        var localAdds = localGuids.Except(remoteGuids).ToArray();

        if (remoteAdds.HadItems())
        {
            local.AddDataInfos(remoteItems.Where(s => remoteAdds.Contains(s.HistoryGuid)));
        }
        if (localAdds.HadItems())
        {
            remote.AddDataInfos(localItems.Where(s => localAdds.Contains(s.HistoryGuid)));
        }
    }

    /// <summary>
    /// 比较版本
    /// </summary>
    /// <param name="local"></param>
    /// <param name="dataGuid"></param>
    /// <param name="versionGuid1"></param>
    /// <param name="versionGuid2"></param>
    /// <returns>
    /// >0      versionGuid2是versionGuid1的新版本
    /// <0      versionGuid1是versionGuid2的新版本
    /// ==0     这个版本没有直接的继承关系
    /// </returns>
    private static int CompareDataVersion(DataHelper local, string dataGuid, string versionGuid1, string versionGuid2)
    {
        if (versionGuid1.IsNullOrEmpty() || versionGuid2.IsNullOrEmpty())
            return 0;

        var items = local.Select<VersionHistory>(s => s.DataGuid == dataGuid);
        if (items.IsNullOrEmpty())
            return 0;

        if (IsChild(items, versionGuid1, versionGuid2) == true)
            return 1;

        if (IsParent(items, versionGuid1, versionGuid2) == true)
            return -1;

        if (!items.Any(s => s.OldVersionGuid == versionGuid1 || s.NewVersionGuid == versionGuid1) || !items.Any(s => s.OldVersionGuid == versionGuid2 || s.NewVersionGuid == versionGuid2))
            return 0;

        return 0;
    }

    private static bool IsParent(List<VersionHistory> items, string versionGuid1, string versionGuid2)
    {
        var nextVersion = items.FirstOrDefault(s => s.OldVersionGuid == versionGuid2);

        while (nextVersion != null && nextVersion.NewVersionGuid != null)
        {
            if (nextVersion.NewVersionGuid == versionGuid1)
                return true;

            nextVersion = items.FirstOrDefault(s => s.OldVersionGuid == nextVersion.NewVersionGuid);
        }

        return false;
    }

    private static bool IsChild(List<VersionHistory> items, string versionGuid1, string versionGuid2)
    {
        var prevVersion = items.FirstOrDefault(s => s.NewVersionGuid == versionGuid2);

        while (prevVersion != null && prevVersion.OldVersionGuid != null)
        {
            if (prevVersion.OldVersionGuid == versionGuid1)
                return true;

            prevVersion = items.FirstOrDefault(s => s.NewVersionGuid == prevVersion.OldVersionGuid);
        }

        return false;
    }
}