Software/Visual_Studio/Tango.Synchronization/Remote/RemoteDBComparer.cs


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="CommonServiceLocator" version="1.3" targetFramework="net46" />
  <package id="EntityFramework" version="6.0.0" targetFramework="net472" />
  <package id="Expression.Blend.Sdk" version="1.0.2" targetFramework="net46" />
  <package id="FontAwesome.WPF" version="4.7.0.9" targetFramework="net46" />
  <package id="MahApps.Metro" version="1.5.0" targetFramework="net46" />
  <package id="MaterialDesignColors" version="1.1.2" targetFramework="net46" />
  <package id="MaterialDesignThemes" version="2.3.1.953" targetFramework="net46" />
  <package id="SimpleValidator" version="0.6.1.0" targetFramework="net46" />
</packages>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using remote = Tango.DAL.Remote.DB;
using local = Tango.DAL.Local.DB;
using System.Data.Entity;
using Tango.Logging;
using System.Collections;

namespace Tango.Synchronization.Remote
{
    /// <summary>
    /// Represents a Remote database to SQLite database comparison engine.
    /// </summary>
    /// <seealso cref="Tango.Synchronization.IDBComparer" />
    public class RemoteDBComparer : IDBComparer
    {
        private remote.RemoteDB _remoteDB;
        private local.LocalDB _localDB;
        private String _machineSerial;
        private List<Diff> _diffs;
        private LogManager LogManager = LogManager.Default;

        /// <summary>
        /// Used to notify about the comparer progress using text messages.
        /// </summary>
        public event EventHandler<string> Progress;

        /// <summary>
        /// Initializes a new instance of the <see cref="RemoteDBComparer"/> class.
        /// </summary>
        /// <param name="remoteDB">The remote database.</param>
        /// <param name="localDB">The local database.</param>
        /// <param name="machineSerial">The machine serial number.</param>
        public RemoteDBComparer(remote.RemoteDB remoteDB, local.LocalDB localDB, String machineSerial)
        {
            _machineSerial = machineSerial;
            _remoteDB = remoteDB;
            _localDB = localDB;
        }

        /// <summary>
        /// Performs a database comparison and returns a set of differences.
        /// </summary>
        /// <returns></returns>
        /// <exception cref="Tango.Synchronization.DataBaseComparisonException">Could not locate machine on remote server " + _machineSerial</exception>
        public List<Diff> Compare()
        {
            _diffs = new List<Diff>();

            OnProgress(LogManager.Log("Performing SQL Server <=> SQLite database comparison..."));

            OnProgress(LogManager.Log("Loading SQLite database to memory..."));

            OnProgress(LogManager.Log("Querying all local addresses..."));
            var local_addresses = _localDB.ADDRESSES.ToList();
            OnProgress(LogManager.Log("Querying all local configurations..."));
            var local_configurations = _localDB.CONFIGURATIONS.ToList();
            OnProgress(LogManager.Log("Querying all local configuration dispensers..."));
            var local_ids_packs = _localDB.IDS_PACKS.ToList();
            OnProgress(LogManager.Log("Querying all local contacts..."));
            var local_contacts = _localDB.CONTACTS.ToList();
            OnProgress(LogManager.Log("Querying all local machines..."));
            var local_machines = _localDB.MACHINES.Where(x => x.SERIAL_NUMBER == _machineSerial).ToList();
            OnProgress(LogManager.Log("Querying all local machines configurations..."));
            var local_machines_configurations = _localDB.MACHINES_CONFIGURATIONS.ToList();
            OnProgress(LogManager.Log("Querying all local machines events..."));
            var local_machines_events = _localDB.MACHINES_EVENTS.ToList();
            OnProgress(LogManager.Log("Querying all local organizations..."));
            var local_organizations = _localDB.ORGANIZATIONS.ToList();
            OnProgress(LogManager.Log("Querying all local users..."));
            var local_users = _localDB.USERS.ToList();
            OnProgress(LogManager.Log("Querying all local users roles..."));
            var local_users_roles = _localDB.USERS_ROLES.ToList();
            OnProgress(LogManager.Log("Querying all local jobs..."));
            var local_jobs = _localDB.JOBS.ToList();
            OnProgress(LogManager.Log("Querying all local job runs..."));
            var local_job_runs = _localDB.JOB_RUNS.ToList();
            OnProgress(LogManager.Log("Querying all local segments..."));
            var local_segments = _localDB.SEGMENTS.ToList();
            OnProgress(LogManager.Log("Querying all local brush stops..."));
            var local_brush_stops = _localDB.BRUSH_STOPS.ToList();
            //LogManager.Log("Querying all CATS...");
            //var local_cats = _localDB.CATS.ToList();

            OnProgress(LogManager.Log("Loading SQL Server database to memory..."));

            List<String> guids = new List<string>();

            OnProgress(LogManager.Log("Querying all remote machines..."));
            var remote_machines = _remoteDB.MACHINES.Where(x => x.SERIAL_NUMBER == _machineSerial).ToList();

            if (remote_machines.Count == 0)
            {
                throw LogManager.Log(new DataBaseComparisonException("Could not locate machine on remote server " + _machineSerial));
            }

            OnProgress(LogManager.Log("Querying all remote organizations..."));
            guids = remote_machines.Select(x => x.ORGANIZATION_GUID).ToList();
            var remote_organizations = _remoteDB.ORGANIZATIONS.Where(x => guids.Contains(x.GUID)).ToList();

            OnProgress(LogManager.Log("Querying all remote machines configurations..."));
            guids = remote_machines.Select(x => x.GUID).ToList();
            //var remote_machines_configurations = _remoteDB.MACHINES_CONFIGURATIONS.Where(x => guids.Contains(x.MACHINE_GUID)).ToList();
            //var remote_configurations = remote_machines_configurations.Select(x => x.CONFIGURATION).ToList();

            OnProgress(LogManager.Log("Querying all remote machines events..."));
            var remote_machines_events = _remoteDB.MACHINES_EVENTS.Where(x => guids.Contains(x.MACHINE_GUID)).ToList();

            OnProgress(LogManager.Log("Querying all remote users..."));
            guids = remote_organizations.Select(x => x.GUID).ToList();
            var remote_users = _remoteDB.USERS.Where(x => guids.Contains(x.ORGANIZATION_GUID)).ToList();

            OnProgress(LogManager.Log("Querying all remote users roles..."));
            guids = remote_users.Select(x => x.GUID).ToList();
            var remote_users_roles = _remoteDB.USERS_ROLES.Where(x => guids.Contains(x.USER_GUID)).ToList();

            OnProgress(LogManager.Log("Querying all remote addresses..."));
            var remote_addresses = remote_users.Select(x => x.ADDRESS).ToList();
            remote_addresses.AddRange(remote_machines.Select(x => x.ORGANIZATION).Select(x => x.ADDRESS));
            remote_addresses = remote_addresses.Distinct().ToList();

            OnProgress(LogManager.Log("Querying all remote contacts..."));
            var remote_contacts = remote_users.Select(x => x.CONTACT).ToList();
            remote_contacts.AddRange(remote_machines.Select(x => x.ORGANIZATION).Select(x => x.CONTACT));
            remote_contacts = remote_contacts.Distinct().ToList();

            OnProgress(LogManager.Log("Querying all remote IDS Packs..."));
            //guids = remote_configurations.Select(x => x.GUID).ToList();
            var remote_ids_packs = _remoteDB.IDS_PACKS.Where(x => guids.Contains(x.CONFIGURATION_GUID)).ToList();

            OnProgress(LogManager.Log("Querying all remote dispenser types..."));
            //guids = remote_ids_packs.Select(x => x.DISPENSER_TYPE_GUID).ToList();
            var remote_dispensers_types = _remoteDB.DISPENSER_TYPES.Where(x => guids.Contains(x.GUID)).ToList();

            OnProgress(LogManager.Log("Querying all remote cartridge types..."));
            guids = remote_ids_packs.Select(x => x.CARTRIDGE_TYPE_GUID).ToList();
            var remote_cartridges_types = _remoteDB.CARTRIDGE_TYPES.Where(x => guids.Contains(x.GUID)).ToList();

            OnProgress(LogManager.Log("Querying all remote jobs..."));
            var remote_jobs = remote_machines.SelectMany(x => x.JOBS).ToList();

            OnProgress(LogManager.Log("Querying all remote job runs..."));
            var remote_jobs_runs = remote_jobs.SelectMany(x => x.JOB_RUNS).ToList();

            OnProgress(LogManager.Log("Querying all remote segments..."));
            var remote_segments = remote_jobs.SelectMany(x => x.SEGMENTS).ToList();

            OnProgress(LogManager.Log("Querying all remote brush stops..."));
            var remote_brush_stops = remote_segments.SelectMany(x => x.BRUSH_STOPS).ToList();

            //LogManager.Log("Querying all remote CATS...");
            //guids = remote_machines.Select(x => x.GUID).ToList();
            //var remote_cats = _remoteDB.CATS.Where(x => guids.Contains(x.MACHINE_GUID)).ToList();


            foreach (var config in _remoteDB.SYNC_CONFIGURATIONS.Where(x => (SyncConfiguration)x.SYNC_TYPE == SyncConfiguration.OverwriteLocal))
            {
                OverrideTable(config);
            }


            OnProgress(LogManager.Log("Comparing addresses"));
            CompareCollections(remote_addresses, local_addresses, _remoteDB.ADDRESSES, _localDB.ADDRESSES);

            OnProgress(LogManager.Log("Comparing configurations"));
            //CompareCollections(remote_configurations, local_configurations, _remoteDB.CONFIGURATIONS, _localDB.CONFIGURATIONS);

            OnProgress(LogManager.Log("Comparing configurations dispensers"));
            CompareCollections(remote_ids_packs, local_ids_packs, _remoteDB.IDS_PACKS, _localDB.IDS_PACKS);

            OnProgress(LogManager.Log("Comparing contacts"));
            CompareCollections(remote_contacts, local_contacts, _remoteDB.CONTACTS, _localDB.CONTACTS);

            OnProgress(LogManager.Log("Comparing machines"));
            CompareCollections(remote_machines, local_machines, _remoteDB.MACHINES, _localDB.MACHINES);

            OnProgress(LogManager.Log("Comparing machines configuration"));
            //CompareCollections(remote_machines_configurations, local_machines_configurations, _remoteDB.MACHINES_CONFIGURATIONS, _localDB.MACHINES_CONFIGURATIONS);

            OnProgress(LogManager.Log("Comparing machines events"));
            CompareCollections(remote_machines_events, local_machines_events, _remoteDB.MACHINES_EVENTS, _localDB.MACHINES_EVENTS);

            OnProgress(LogManager.Log("Comparing organizations"));
            CompareCollections(remote_organizations, local_organizations, _remoteDB.ORGANIZATIONS, _localDB.ORGANIZATIONS);

            OnProgress(LogManager.Log("Comparing users"));
            CompareCollections(remote_users, local_users, _remoteDB.USERS, _localDB.USERS);

            OnProgress(LogManager.Log("Comparing users roles"));
            CompareCollections(remote_users_roles, local_users_roles, _remoteDB.USERS_ROLES, _localDB.USERS_ROLES);

            OnProgress(LogManager.Log("Comparing jobs"));
            CompareCollections(remote_jobs, local_jobs, _remoteDB.JOBS, _localDB.JOBS);

            OnProgress(LogManager.Log("Comparing job runs"));
            CompareCollections(remote_jobs_runs, local_job_runs, _remoteDB.JOB_RUNS, _localDB.JOB_RUNS);

            OnProgress(LogManager.Log("Comparing segments"));
            CompareCollections(remote_segments, local_segments, _remoteDB.SEGMENTS, _localDB.SEGMENTS);

            OnProgress(LogManager.Log("Comparing brush stops"));
            CompareCollections(remote_brush_stops, local_brush_stops, _remoteDB.BRUSH_STOPS, _localDB.BRUSH_STOPS);

            //LogManager.Log("Comparing cats");
            //CompareCollections(remote_cats, local_cats, _remoteDB.CATS, _localDB.CATS);

            OnProgress(LogManager.Log("Comparison done!"));

            return _diffs;
        }

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

        /// <summary>
        /// Copies source entity properties to the destination entity.
        /// </summary>
        /// <param name="source">The source entity.</param>
        /// <param name="destination">The destination entity.</param>
        /// <exception cref="InvalidOperationException"></exception>
        private void CopyEntity(Object source, Object destination)
        {
            foreach (var prop in source.GetType().GetProperties().Where(x => x.PropertyType.IsPrimitive || x.PropertyType == typeof(String) || x.PropertyType == typeof(DateTime) || x.PropertyType == typeof(byte[])))
            {
                if (destination.GetType().GetProperty(prop.Name) == null)
                {
                    throw LogManager.Log(new InvalidOperationException(String.Format("Property '{0}' not found on destination table '{1}'.", prop.Name, destination.GetType().Name)));
                }

                if (prop.PropertyType == typeof(Int64))
                {
                    destination.GetType().GetProperty(prop.Name).SetValue(destination, Convert.ToInt32((Int64)prop.GetValue(source)));
                }
                else
                {
                    destination.GetType().GetProperty(prop.Name).SetValue(destination, prop.GetValue(source));
                }
            }
        }

        /// <summary>
        /// Compares a collection of source entities to a collection of destination entities.
        /// </summary>
        /// <typeparam name="Master">The type of the aster.</typeparam>
        /// <typeparam name="Slave">The type of the lave.</typeparam>
        /// <param name="masterCollection">The master collection.</param>
        /// <param name="slaveCollection">The slave collection.</param>
        /// <param name="masterSet">The master set.</param>
        /// <param name="slaveSet">The slave set.</param>
        private void CompareCollections<Master, Slave>(List<Master> masterCollection, List<Slave> slaveCollection, DbSet<Master> masterSet, DbSet<Slave> slaveSet) where Master : class where Slave : class
        {
            var slaveProp = typeof(Slave).GetProperty("GUID");
            var masterProp = typeof(Master).GetProperty("GUID");

            List<Slave> compared = new List<Slave>();

            foreach (var masterRow in masterCollection)
            {
                Slave slaveRow = slaveCollection.SingleOrDefault(x => slaveProp.GetValue(x).ToString() == masterProp.GetValue(masterRow).ToString());
                CompareEntities(masterRow, slaveRow, masterSet, slaveSet);

                if (slaveRow != null)
                {
                    compared.Add(slaveRow);
                }
            }

            foreach (var slaveRow in slaveCollection.Where(x => !compared.Contains(x)))
            {
                CompareEntities(masterCollection.SingleOrDefault(x => masterProp.GetValue(x).ToString() == slaveProp.GetValue(slaveRow).ToString()), slaveRow, masterSet, slaveSet);
            }
        }

        /// <summary>
        /// Overrides the table by the specified synchronization configuration.
        /// </summary>
        /// <param name="config">The configuration.</param>
        private void OverrideTable(remote.SYNC_CONFIGURATIONS config)
        {
            OnProgress(LogManager.Log("Generating table override difference for " + config.TABLE_NAME + "..."));

            var master = _remoteDB.GetType().GetProperty(config.TABLE_NAME).GetValue(_remoteDB) as IEnumerable;
            var slave = _localDB.GetType().GetProperty(config.TABLE_NAME).GetValue(_localDB) as IEnumerable;

            _diffs.Add(new Diff(DiffAction.ReplaceTableDataInSlave, "Override all rows on slave table " + config.TABLE_NAME, () =>
            {
                OnProgress(LogManager.Log("Overwriting slave table " + config.TABLE_NAME + "..."));

                _localDB.Database.ExecuteSqlCommand("DELETE FROM " + config.TABLE_NAME + ";");

                foreach (var entity in master)
                {
                    var newRow = slave.GetType().GetMethods().Where(x => x.Name == "Create").First().Invoke(slave, new object[] { });
                    CopyEntity(entity, newRow);
                    slave.GetType().GetMethod("Add").Invoke(slave, new object[] { newRow });
                }

            }, null));
        }

        /// <summary>
        /// Compares the specified entities.
        /// </summary>
        /// <typeparam name="Master">The type of the master.</typeparam>
        /// <typeparam name="Slave">The type of the slave.</typeparam>
        /// <param name="master">The master.</param>
        /// <param name="slave">The slave.</param>
        private void CompareEntities<Master, Slave>(Master master, Slave slave) where Master : class where Slave : class
        {
            Diff diff = null;

            DateTime masterDate = (DateTime)master.GetType().GetProperty("LAST_UPDATED").GetValue(master);
            DateTime slaveDate = (DateTime)slave.GetType().GetProperty("LAST_UPDATED").GetValue(slave);

            if (masterDate > slaveDate)
            {
                diff = new Diff(DiffAction.UpdateRowInSlave, "Update row in slave table " + typeof(Master).Name, () =>
                {
                    OnProgress(LogManager.Log("Updating row in slave table " + typeof(Master).Name));
                    CopyEntity(master, slave);
                }, null);
            }
            else if (slaveDate > masterDate)
            {
                diff = new Diff(DiffAction.UpdateRowInMaster, "Update row in master table " + typeof(Master).Name, () =>
                {
                    OnProgress(LogManager.Log("Updating row in master table " + typeof(Master).Name));
                    CopyEntity(slave, master);
                }, null);
            }

            if (diff != null)
            {
                _diffs.Add(diff);
            }
        }

        /// <summary>
        /// Compares the entities.
        /// </summary>
        /// <typeparam name="Master">The type of the master.</typeparam>
        /// <typeparam name="Slave">The type of the slave.</typeparam>
        /// <param name="master">The master.</param>
        /// <param name="slave">The slave.</param>
        /// <param name="masterSet">The master set.</param>
        /// <param name="slaveSet">The slave set.</param>
        private void CompareEntities<Master, Slave>(Master master, Slave slave, DbSet<Master> masterSet, DbSet<Slave> slaveSet) where Master : class where Slave : class
        {
            bool skipAddToSlave = false;

            if (slave == null && _remoteDB.SYNC_CONFIGURATIONS.ToList().Exists(x => (SyncConfiguration)x.SYNC_TYPE == SyncConfiguration.SynchronizeToRemote && x.TABLE_NAME.SingularizeMVC() == typeof(Master).Name))
            {
                skipAddToSlave = true;
            }

            if (slave == null && !skipAddToSlave)
            {
                _diffs.Add(new Diff(DiffAction.AddRowToSlave, "Add row to slave table " + typeof(Master).Name, () =>
               {
                   OnProgress(LogManager.Log("Adding row to slave table " + typeof(Master).Name));
                   Slave newRow = slaveSet.Create();
                   CopyEntity(master, newRow);
                   slaveSet.Add(newRow);

               }, null));

                return;
            }

            if (master == null)
            {
                _diffs.Add(new Diff(DiffAction.AddRowToMaster, "Add row to master table " + typeof(Master).Name, () =>
                {
                    OnProgress(LogManager.Log("Adding row to master table " + typeof(Master).Name));
                    Master newRow = masterSet.Create();
                    CopyEntity(slave, newRow);
                    masterSet.Add(newRow);

                }, null));

                return;
            }

            if (slave != null && master != null)
            {
                CompareEntities(master, slave);
            }
        }

        /// <summary>
        /// Raises the <see cref="Progress"/> event.
        /// </summary>
        /// <returns></returns>
        protected virtual void OnProgress(String message)
        {
            Progress?.Invoke(this, message);
        }
    }
}