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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
|
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
{
/// <summary>
/// Represents an <see cref="ISqlDataBase"/> synchronization engine.
/// </summary>
/// <seealso cref="System.IDisposable" />
public class SqlDataBaseComparer : IDisposable
{
/// <summary>
/// Gets the master SQL.
/// </summary>
public ISqlDataBase MasterSQL { get; private set; }
/// <summary>
/// Gets the slave SQL.
/// </summary>
public ISqlDataBase SlaveSQL { get; private set; }
/// <summary>
/// Initializes a new instance of the <see cref="SqlDataBaseComparer"/> class.
/// </summary>
/// <param name="masterSQL">The master SQL.</param>
/// <param name="slaveSQL">The slave SQL.</param>
public SqlDataBaseComparer(ISqlDataBase masterSQL, ISqlDataBase slaveSQL)
{
MasterSQL = masterSQL;
SlaveSQL = slaveSQL;
}
/// <summary>
/// Compares the master and slave SQL data base and returns a collection of <see cref="SqlDiff"/>.
/// </summary>
/// <returns></returns>
public List<SqlDiff> Compare()
{
LogManager.Log("Comparing databases " + Path.GetFileName(MasterSQL.Source) + " <=> " + Path.GetFileName(SlaveSQL.Source));
LogManager.Log("Loading master tables...");
MasterSQL.LoadTables();
LogManager.Log("Loading slave tables...");
SlaveSQL.LoadTables();
List<SqlDiff> diffs = new List<SqlDiff>();
foreach (var masterTable in MasterSQL.Tables)
{
LogManager.Log("Comparing table " + masterTable.TableName);
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.
{
LogManager.Log("Table not found on slave, adding difference.");
//Clone table from slave db to master db including records!
diffs.Add(new SqlDiff(SqlDiffAction.AddTableToSlave, String.Format("Add table {0} to slave", masterTable.TableName), () => SlaveSQL.CloneTableFrom(MasterSQL, masterTable), SlaveSQL.GetCloneTableFromCommand(MasterSQL, masterTable)));
continue;
}
LogManager.Log("Table found, comparing columns...");
foreach (DataColumn masterColumn in masterTable.Columns)
{
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.
{
LogManager.Log("Column not found on slave, adding difference.");
//Add column to slave table.
diffs.Add(new SqlDiff(SqlDiffAction.AddColumnToSlave, String.Format("Add column {0} to slave table", masterColumn.ColumnName), () => SlaveSQL.AddColumn(masterTable, masterColumn), SlaveSQL.GetAddColumnCommand(masterTable, masterColumn)));
}
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>();
LogManager.Log("Comparing rows...");
int count = 0;
foreach (DataRow masterRow in masterTable.Rows)
{
LogManager.Log("Comparing row " + count++);
String guid = masterRow.Field<String>(Constants.GUID);
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)
{
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)
{
LogManager.Log("Master => Slave Update " + masterDate.ToSQLiteDateString() + ", adding difference.");
updateSlave.Add(masterRow);
}
else if (slaveDate > masterDate)
{
LogManager.Log("Master <= Slave Update " + masterDate.ToSQLiteDateString() + ", adding difference.");
updateMaster.Add(slaveRow);
}
else
{
LogManager.Log("Master <=> Slave No Update.");
}
}
else
{
LogManager.Log("Slave row not found, adding difference.");
addToSlave.Add(masterRow);
}
}
LogManager.Log("Done comparing rows...");
LogManager.Log("Searching for missing rows on master...");
foreach (DataRow slaveRow in slaveTable.Rows)
{
String guid = slaveRow.Field<String>(Constants.GUID);
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)
{
LogManager.Log("Master row not found, adding difference.");
addToMaster.Add(slaveRow);
}
else
{
LogManager.Log("Master row found.");
}
}
LogManager.Log("Done searching for missing rows on master...");
foreach (var row in addToSlave)
{
diffs.Add(new SqlDiff(SqlDiffAction.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 SqlDiff(SqlDiffAction.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 SqlDiff(SqlDiffAction.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 SqlDiff(SqlDiffAction.UpdateRowInMaster, String.Format("Update row in master table {0}", masterTable.TableName), () => MasterSQL.UpdateRow(masterTable, row), MasterSQL.GetUpdateRowCommand(masterTable, row)));
}
LogManager.Log("Done comparing table " + masterTable.TableName);
}
LogManager.Log("Databases comparison completed.");
return diffs;
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
/// </summary>
public void Dispose()
{
MasterSQL.Dispose();
SlaveSQL.Dispose();
}
}
}
|
