path: root/Software/Visual_Studio/Tango.Synchronization/Local/LocalDBComparer.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tango.Logging;

namespace Tango.Synchronization.Local
{
    /// <summary>
    /// Represents an <see cref="ILocalDataBase"/> synchronization engine.
    /// </summary>
    /// <seealso cref="System.IDisposable" />
    public class LocalDBComparer : IDBComparer
    {
        /// <summary>
        /// Used to notify about the comparer progress using text messages.
        /// </summary>
        public event EventHandler<string> Progress;

        /// <summary>
        /// Gets the master SQL.
        /// </summary>
        public ILocalDataBase MasterSQL { get; private set; }

        /// <summary>
        /// Gets the slave SQL.
        /// </summary>
        public ILocalDataBase SlaveSQL { get; private set; }

        /// <summary>
        /// Initializes a new instance of the <see cref="LocalDBComparer"/> class.
        /// </summary>
        /// <param name="masterSQL">The master SQL.</param>
        /// <param name="slaveSQL">The slave SQL.</param>
        public LocalDBComparer(ILocalDataBase masterSQL, ILocalDataBase slaveSQL)
        {
            MasterSQL = masterSQL;
            SlaveSQL = slaveSQL;
        }

        /// <summary>
        /// Compares the master and slave SQL data base and returns a collection of <see cref="Diff"/>.
        /// </summary>
        /// <returns></returns>
        public List<Diff> Compare()
        {
            OnProgress(LogManager.Log("Comparing databases " + Path.GetFileName(MasterSQL.Source) + " <=> " + Path.GetFileName(SlaveSQL.Source)));

            OnProgress(LogManager.Log("Loading master tables..."));
            MasterSQL.LoadTables();

            OnProgress(LogManager.Log("Loading slave tables..."));
            SlaveSQL.LoadTables();

            List<Diff> diffs = new List<Diff>();

            DataTable sync_table = MasterSQL.Tables.Single(x => x.TableName == Constants.SYNC_CONFIGURATIONS_TABLE_NAME);

            List<DataTable> sync_tables = new List<DataTable>();
            List<DataTable> overwrite_tables = new List<DataTable>();

            foreach (var row in sync_table.AsEnumerable())
            {
                SyncConfiguration config = (SyncConfiguration)row.Field<Int64>(Constants.SYNC_CONFIGURATION_SYNC_TYPE_COLUMN);

                if (config == SyncConfiguration.Synchronize || config == SyncConfiguration.SynchronizeToRemote)
                {
                    sync_tables.Add(MasterSQL.Tables.Single(x => x.TableName == row.Field<String>(Constants.SYNC_CONFIGURATION_TABLE_NAME_COLUMN)));
                }
                else
                {
                    overwrite_tables.Add(MasterSQL.Tables.Single(x => x.TableName == row.Field<String>(Constants.SYNC_CONFIGURATION_TABLE_NAME_COLUMN)));
                }
            }

            foreach (var masterTable in overwrite_tables)
            {
                OnProgress(LogManager.Log("Generating table overwrite difference for table " + masterTable.TableName));

                var slaveTable = SlaveSQL.Tables.SingleOrDefault(x => x.TableName == masterTable.TableName); //Get matching slave table on slave db.

                if (slaveTable == null) //Table not found on slave.
                {
                    OnProgress(LogManager.Log("Table not found on slave, adding difference."));
                    //Clone table from slave db to master db including records!
                    diffs.Add(new Diff(DiffAction.AddTableToSlave, String.Format("Add table {0} to slave", masterTable.TableName), () => SlaveSQL.CloneTableFrom(MasterSQL, masterTable), SlaveSQL.GetCloneTableFromCommand(MasterSQL, masterTable)));
                    continue;
                }

                foreach (DataColumn masterColumn in masterTable.Columns)
                {
                    OnProgress(LogManager.Log("Searching for column " + masterColumn.ColumnName + " on slave..."));

                    var slaveColumn = slaveTable.Columns[masterColumn.ColumnName]; //Get matching slave column on slave table.

                    if (slaveColumn == null) //Slave column not found.
                    {
                        OnProgress(LogManager.Log("Column not found on slave, adding difference."));
                        //Add column to slave table.
                        diffs.Add(new Diff(DiffAction.AddColumnToSlave, String.Format("Add column {0} to slave table", masterColumn.ColumnName), () => SlaveSQL.AddColumn(masterTable, masterColumn), SlaveSQL.GetAddColumnCommand(masterTable, masterColumn)));
                    }

                    OnProgress(LogManager.Log("Column found."));
                }

                diffs.Add(new Diff(DiffAction.ReplaceTableDataInSlave, "Replace all rows on slave table " + masterTable.TableName, () =>
                {

                    SlaveSQL.ReplaceTableData(MasterSQL, masterTable);

                }, SlaveSQL.GetReplaceTableDataCommand(MasterSQL, masterTable)));
            }

            foreach (var masterTable in sync_tables)
            {
                OnProgress(LogManager.Log("Comparing table " + masterTable.TableName));
                OnProgress(LogManager.Log("Searching table " + masterTable.TableName + " on slave..."));

                var slaveTable = SlaveSQL.Tables.SingleOrDefault(x => x.TableName == masterTable.TableName); //Get matching slave table on slave db.

                if (slaveTable == null) //Table not found on slave.
                {
                    OnProgress(LogManager.Log("Table not found on slave, adding difference."));
                    //Clone table from slave db to master db including records!
                    diffs.Add(new Diff(DiffAction.AddTableToSlave, String.Format("Add table {0} to slave", masterTable.TableName), () => SlaveSQL.CloneTableFrom(MasterSQL, masterTable), SlaveSQL.GetCloneTableFromCommand(MasterSQL, masterTable)));
                    continue;
                }

                OnProgress(LogManager.Log("Table found, comparing columns..."));

                foreach (DataColumn masterColumn in masterTable.Columns)
                {
                    OnProgress(LogManager.Log("Searching for column " + masterColumn.ColumnName + " on slave..."));

                    var slaveColumn = slaveTable.Columns[masterColumn.ColumnName]; //Get matching slave column on slave table.

                    if (slaveColumn == null) //Slave column not found.
                    {
                        OnProgress(LogManager.Log("Column not found on slave, adding difference."));
                        //Add column to slave table.
                        diffs.Add(new Diff(DiffAction.AddColumnToSlave, String.Format("Add column {0} to slave table", masterColumn.ColumnName), () => SlaveSQL.AddColumn(masterTable, masterColumn), SlaveSQL.GetAddColumnCommand(masterTable, masterColumn)));
                    }

                    OnProgress(LogManager.Log("Column found."));
                }

                List<DataRow> addToSlave = new List<DataRow>();
                List<DataRow> updateSlave = new List<DataRow>();
                List<DataRow> addToMaster = new List<DataRow>();
                List<DataRow> updateMaster = new List<DataRow>();

                OnProgress(LogManager.Log("Comparing rows..."));

                int count = 0;

                foreach (DataRow masterRow in masterTable.Rows)
                {
                    OnProgress(LogManager.Log("Comparing row " + count++));

                    String guid = masterRow.Field<String>(Constants.GUID);

                    OnProgress(LogManager.Log("Searching for row with GUID " + guid + " on slave table..."));

                    //Get Matching slave row.
                    DataRow slaveRow = slaveTable.AsEnumerable().SingleOrDefault(x => x.Field<String>(Constants.GUID) == guid);

                    if (slaveRow != null)
                    {
                        OnProgress(LogManager.Log("Slave row found, comparing dates..."));

                        DateTime masterDate = masterRow.Field<DateTime>(Constants.LAST_UPDATED);
                        DateTime slaveDate = slaveRow.Field<DateTime>(Constants.LAST_UPDATED);

                        if (masterDate > slaveDate)
                        {
                            OnProgress(LogManager.Log("Master => Slave Update " + masterDate.ToSQLiteDateString() + ", adding difference."));
                            updateSlave.Add(masterRow);
                        }
                        else if (slaveDate > masterDate)
                        {
                            OnProgress(LogManager.Log("Master <= Slave Update " + masterDate.ToSQLiteDateString() + ", adding difference."));
                            updateMaster.Add(slaveRow);
                        }
                        else
                        {
                            OnProgress(LogManager.Log("Master <=> Slave No Update."));
                        }
                    }
                    else
                    {
                        OnProgress(LogManager.Log("Slave row not found, adding difference."));
                        addToSlave.Add(masterRow);
                    }
                }

                OnProgress(LogManager.Log("Done comparing rows..."));

                OnProgress(LogManager.Log("Searching for missing rows on master..."));

                foreach (DataRow slaveRow in slaveTable.Rows)
                {
                    String guid = slaveRow.Field<String>(Constants.GUID);

                    OnProgress(LogManager.Log("Searching for row with GUID " + guid + " on master table..."));

                    //Get Matching slave row.
                    DataRow masterRow = masterTable.AsEnumerable().SingleOrDefault(x => x.Field<String>(Constants.GUID) == guid);

                    if (masterRow == null)
                    {
                        OnProgress(LogManager.Log("Master row not found, adding difference."));
                        addToMaster.Add(slaveRow);
                    }
                    else
                    {
                        OnProgress(LogManager.Log("Master row found."));
                    }
                }

                OnProgress(LogManager.Log("Done searching for missing rows on master..."));

                foreach (var row in addToSlave)
                {
                    diffs.Add(new Diff(DiffAction.AddRowToSlave, String.Format("Add row to slave table {0}", slaveTable.TableName), () => SlaveSQL.AddRow(slaveTable, row), SlaveSQL.GetAddRowCommand(slaveTable, row)));
                }

                foreach (var row in addToMaster)
                {
                    diffs.Add(new Diff(DiffAction.AddRowToMaster, String.Format("Add row to master table {0}", masterTable.TableName), () => MasterSQL.AddRow(masterTable, row), MasterSQL.GetAddRowCommand(masterTable, row)));
                }

                foreach (var row in updateSlave)
                {
                    diffs.Add(new Diff(DiffAction.UpdateRowInSlave, String.Format("Update row in slave table {0}", slaveTable.TableName), () => SlaveSQL.UpdateRow(slaveTable, row), SlaveSQL.GetUpdateRowCommand(slaveTable, row)));
                }

                foreach (var row in updateMaster)
                {
                    diffs.Add(new Diff(DiffAction.UpdateRowInMaster, String.Format("Update row in master table {0}", masterTable.TableName), () => MasterSQL.UpdateRow(masterTable, row), MasterSQL.GetUpdateRowCommand(masterTable, row)));
                }

                OnProgress(LogManager.Log("Done comparing table " + masterTable.TableName));
            }

            OnProgress(LogManager.Log("Databases comparison completed."));

            return diffs;
        }

        protected virtual void OnProgress(String message)
        {
            Progress?.Invoke(this, message);
        }

        /// <summary>
        /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
        /// </summary>
        public void Dispose()
        {
            MasterSQL.Dispose();
            SlaveSQL.Dispose();
        }
    }
}