public SchemaDiff(IDocumentSchema schema, SchemaObjects existing, DocumentMapping mapping)
{
if (existing.HasNone())
{
AllMissing = true;
}
else
{
var expectedTable = mapping.SchemaObjects.As <DocumentSchemaObjects>().StorageTable();
TableDiff = new TableDiff(expectedTable, existing.Table);
// TODO -- drop obsolete indices?
mapping.Indexes.Each(index =>
{
if (existing.ActualIndices.ContainsKey(index.IndexName))
{
var actualIndex = existing.ActualIndices[index.IndexName];
if (!index.Matches(actualIndex))
{
IndexChanges.Add($"drop index {expectedTable.Table.Schema}.{index.IndexName};{Environment.NewLine}{index.ToDDL()};");
}
}
else
{
IndexChanges.Add(index.ToDDL());
}
});
}
_existing = existing;
_mapping = mapping;
}
public void CreatePatch(StoreOptions options, SchemaPatch patch)
{
TableDiff.CreatePatch(_mapping, patch);
FunctionDiff.WritePatch(options, patch);
IndexChanges.Each(x => patch.Updates.Apply(this, x));
IndexRollbacks.Each(x => patch.Rollbacks.Apply(this, x));
}
public void findDifferences(bool showAsHex)
{
if (!showAsHex)
{
this.Text = "File differences";
}
tdiffList = new List <TableDiff>();
labelFileNames.Text = pcm1.FileName + " <> " + pcm2.FileName;
for (int t = 0; t < tdList.Count; t++)
{
TableData td = pcm1.tableDatas[tdList[t]];
TableData td2 = pcm2.tableDatas[tdList2[t]];
uint step = (uint)getElementSize(td.DataType);
uint step2 = (uint)getElementSize(td2.DataType);
int count = td.Rows * td.Columns;
uint addr = (uint)(td.addrInt + td.Offset);
uint addr2 = (uint)(td2.addrInt + td2.Offset);
string data1 = "";
string data2 = "";
string formatStr = "";
if (showAsHex)
{
formatStr = "X" + (step * 2).ToString();
}
for (int a = 0; a < count; a++)
{
if (showAsHex)
{
data1 += ((double)getRawValue(pcm1.buf, addr, td, 0)).ToString(formatStr) + " ";
data2 += ((double)getRawValue(pcm2.buf, addr2, td2, 0)).ToString(formatStr) + " ";
}
else
{
data1 += getValue(pcm1.buf, addr, td, 0, pcm1).ToString(formatStr) + " ";
data2 += getValue(pcm2.buf, addr2, td2, 0, pcm2).ToString(formatStr) + " ";
}
addr += step;
addr2 += step2;
}
TableDiff tDiff = new TableDiff();
tDiff.Data1 = data1.Trim();
tDiff.Data2 = data2.Trim();
tDiff.id = tdList[t];
tDiff.TableName = td.TableName;
tDiff.td = td;
tDiff.id2 = tdList2[t];
tDiff.addr1 = td.Address;
tDiff.addr2 = td2.Address;
tdiffList.Add(tDiff);
}
dataGridView1.DataSource = bindingSource;
bindingSource.DataSource = tdiffList;
dataGridView1.CellMouseDoubleClick += DataGridView1_CellMouseDoubleClick;
dataGridView1.ColumnHeaderMouseClick += DataGridView1_ColumnHeaderMouseClick;
}
public void perfect_match()
{
var users = DocumentSchemaObjects.For <User>();
var actual = users.StorageTable();
var expected = users.StorageTable();
var diff = new TableDiff(expected, actual);
diff.Matches.ShouldBeTrue();
}
public void can_match_up_on_columns()
{
var users = DocumentSchemaObjects.For <User>();
var actual = users.StorageTable();
var expected = users.StorageTable();
var diff = new TableDiff(expected, actual);
diff.Matched.OrderBy(x => x.Name).Select(x => x.Name)
.ShouldHaveTheSameElementsAs("data", "id", DocumentMapping.DotNetTypeColumn, DocumentMapping.LastModifiedColumn, DocumentMapping.VersionColumn);
}
public void CreatePatch(IDDLRunner runner)
{
TableDiff.CreatePatch(_mapping, runner);
if (HasFunctionChanged())
{
_existing.FunctionDropStatements.Each(x => runner.Apply(this, x));
runner.Apply(this, expectedUpsertFunction());
}
IndexChanges.Each(x => runner.Apply(this, x));
}
public ReturnValue Assign(TableDiff tdiff)
{
return ReturnValue.Wrap(() =>
{
if (tdiff.Replica == null) throw new ArgumentNullException("tdiff.Replica");
TableDiff = tdiff;
BuildDataSource();
AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells);
CellFormatting += OnCellFormatting;
HighlightSchemaDiffs();
HighlightKeys();
});
}
public void CreatePatch(Action <string> executeSql)
{
TableDiff.CreatePatch(_mapping, executeSql);
if (HasFunctionChanged())
{
_existing.FunctionDropStatements.Each(executeSql);
// TODO -- need to drop the existing function somehow?
executeSql(expectedUpsertFunction());
}
IndexChanges.Each(executeSql);
}
public static ReturnValue Invoke( TableDiff tdiff)
{
return ReturnValue.Wrap(() =>
{
var form = new DataDiffDetailsForm
{
lblTableName = {Text = tdiff.Master == null ? "" : tdiff.Master.TableName},
TableDiff = tdiff,
};
form.ShowDialog();
});
}
public void not_matching_with_columns_of_same_name_that_are_different()
{
var users = DocumentSchemaObjects.For <User>();
var actual = users.StorageTable();
actual.ReplaceOrAddColumn("id", "int");
var expected = users.StorageTable();
var diff = new TableDiff(expected, actual);
diff.Matches.ShouldBeFalse();
diff.Different.Single().Name.ShouldBe("id");
}
public void not_matching_with_extra_columns()
{
var users = DocumentSchemaObjects.For <User>();
var actual = users.StorageTable();
var tableColumn = new TableColumn("new", "varchar");
actual.Columns.Add(tableColumn);
var expected = users.StorageTable();
var diff = new TableDiff(expected, actual);
diff.Matches.ShouldBeFalse();
diff.Extras.Single().ShouldBe(tableColumn);
}
public SchemaDiff(SchemaObjects existing, DocumentMapping mapping, DdlRules rules)
{
if (existing.HasNone())
{
AllMissing = true;
}
else
{
var expectedTable = mapping.SchemaObjects.As <DocumentSchemaObjects>().StorageTable();
TableDiff = new TableDiff(expectedTable, existing.Table);
// TODO -- drop obsolete indices?
mapping.Indexes.Each(index =>
{
if (existing.ActualIndices.ContainsKey(index.IndexName))
{
var actualIndex = existing.ActualIndices[index.IndexName];
if (!index.Matches(actualIndex))
{
IndexChanges.Add($"drop index {expectedTable.Table.Schema}.{index.IndexName};{Environment.NewLine}{index.ToDDL()};");
IndexRollbacks.Add($"drop index {expectedTable.Table.Schema}.{index.IndexName};{Environment.NewLine}{actualIndex.DDL};");
}
}
else
{
IndexChanges.Add(index.ToDDL());
IndexRollbacks.Add($"drop index concurrently if exists {expectedTable.Table.Schema}.{index.IndexName};");
}
});
existing.ActualIndices.Values.Where(x => mapping.Indexes.All(_ => _.IndexName != x.Name)).Each(
index =>
{
IndexRollbacks.Add(index.DDL);
IndexChanges.Add($"drop index concurrently if exists {mapping.Table.Schema}.{index.Name};");
});
var expectedFunction = new UpsertFunction(mapping);
FunctionDiff = new FunctionDiff(expectedFunction.ToBody(rules), existing.Function);
var missingFKs = mapping.ForeignKeys.Where(x => !existing.ForeignKeys.Contains(x.KeyName));
MissingForeignKeys.AddRange(missingFKs);
}
_mapping = mapping;
}
public void TestCompare()
{
var t1 = new Table("dbo", "Test");
var t2 = new Table("dbo", "Test");
TableDiff diff = default(TableDiff);
//test equal
t1.Columns.Add(new Column("first", "varchar", 30, false, null));
t2.Columns.Add(new Column("first", "varchar", 30, false, null));
t1.Constraints.Add(new Constraint("PK_Test", "PRIMARY KEY", "first"));
t2.Constraints.Add(new Constraint("PK_Test", "PRIMARY KEY", "first"));
diff = t1.Compare(t2);
Assert.IsNotNull(diff);
Assert.IsFalse(diff.IsDiff);
//test add
t1.Columns.Add(new Column("second", "varchar", 30, false, null));
diff = t1.Compare(t2);
Assert.IsTrue(diff.IsDiff);
Assert.AreEqual(1, diff.ColumnsAdded.Count);
//test delete
diff = t2.Compare(t1);
Assert.IsTrue(diff.IsDiff);
Assert.AreEqual(1, diff.ColumnsDropped.Count);
//test diff
t1.Columns.Items[0].Length = 20;
diff = t1.Compare(t2);
Assert.IsTrue(diff.IsDiff);
Assert.AreEqual(1, diff.ColumnsDiff.Count);
Console.WriteLine("--- create ----");
Console.Write(t1.ScriptCreate());
Console.WriteLine("--- migrate up ---");
Console.Write(t1.Compare(t2).Script());
Console.WriteLine("--- migrate down ---");
Console.Write(t2.Compare(t1).Script());
}
public bool CanPatch()
{
return(AllMissing || TableDiff.CanPatch());
}
private void HighlightDifferences(DataGridView dgvImported, TableDiff delta)
{
if (delta.DiffType == TableDiffType.IncompatibleSchema)
{
// dgvImported.Enabled = false;
return;
}
IEnumerable<DataGridViewRow> rows = dgvImported.Rows.Cast<DataGridViewRow>();
foreach (RowDiff item in delta.RowDiffs)
{
DataGridViewRow thisRow = rows.FirstOrDefault(r => (r.DataBoundItem as DataRowView).Row == item.Row);
thisRow.DefaultCellStyle.BackColor = Color.Yellow;
if (item.DiffType == DiffType.Missing)
{
}
}
}
public bool CanPatch()
{
return(TableDiff.CanPatch());
}
public ReturnValue<TableDiff> Compare(TableCompareOptions options = TableCompareOptions.None)
{
try
{
ReturnValue<SchemaDiff> schemaDiffResult = CompareSchema();
if (!schemaDiffResult.Success)
return ReturnValue<TableDiff>.Cascade(schemaDiffResult);
SchemaDiff schemaDiff = schemaDiffResult.Value;
var tableDiff = new TableDiff(_master, _replica)
{
SchemaDiff = schemaDiff,
DiffType = TableDiffType.None
};
if (!schemaDiff.IsCompatible)
{
tableDiff.DiffType = TableDiffType.IncompatibleSchema;
if (!options.HasFlag(TableCompareOptions.AllowIncompatibleSchema))
{
return
ReturnValue<TableDiff>.FailResult(
string.Format(
"The schema for replica '{0}' is not compatible with '{1}' and the AllowIncompatibleSchema option is not set",
_replica.TableName, _master.TableName));
}
}
else if (schemaDiff.HasDiffs)
{
tableDiff.DiffType = TableDiffType.CompatibleSchema;
}
ReturnValue<List<RowDiff>> dataDiffsResult = GetRowDiffs(options);
if (!dataDiffsResult.Success)
{
ReturnValue<TableDiff>.Cascade(dataDiffsResult,
string.Format("Unable to compare rows for {0} and {1}.", _master.TableName, _replica.TableName));
}
tableDiff.RowDiffs = dataDiffsResult.Value;
if (tableDiff.RowDiffs.Any())
{
tableDiff.DiffType = TableDiffType.Data;
}
return ReturnValue<TableDiff>.SuccessResult(tableDiff);
}
catch (Exception ex)
{
return
ReturnValue<TableDiff>.FailResult(
string.Format("Unhandled error comparing tables {0}.", _master.TableName), ex);
}
}
public TableDiff compareRemoteAndLocal(string server1, string dbname1, string username1, string password1, string server2, string dbname2)
{
// Logger
logger.Info("[ compareRemoteAndLocal ] - database1 : " + server1 + " , " + dbname1 + " , " + username1 + " , " + password1 + " ; database2 : " + server2 + " , " + dbname2 + ";");
DateTime start = DateTime.Now;
logger.Info("[ compareRemoteAndLocal ] - start time : " + start.ToString());
// Init the basic info about response body
TableDiff rtn = new TableDiff();
rtn.code = ResponseCode.SUCCESS;
rtn.msg = "Succecssful";
if (server1 == null || dbname1 == null || username1 == null || password1 == null || server2 == null || dbname2 == null ||
server1.Trim() == "" || dbname1.Trim() == "" || username1.Trim() == "" || password1.Trim() == "" || server2.Trim() == "" || dbname2.Trim() == "")
{
rtn.code = ResponseCode.INPUT_ERROR;
rtn.msg = "Null inputs are not be allowed!";
return(rtn);
}
// Connect to database
SqlConnection conn1 = SqlServer.createConnectionRemote(server1, dbname1, username1, password1);
SqlConnection conn2 = SqlServer.createConnectionLocal(server2, dbname2);
if (conn1 == null || conn2 == null)
{
rtn.code = ResponseCode.DB_NOT_FUND;
rtn.msg = "Database not found";
// close the database connection
if (conn1 != null)
{
conn1.Close();
}
if (conn2 != null)
{
conn2.Close();
}
return(rtn);
}
// Compare the tables between two databases
TableComparator tbCompare = new TableComparator();
rtn.tables = tbCompare.compareTables(conn1, conn2);
// Compare the stored procedures between two databases
StoredProcedureComparator pc = new StoredProcedureComparator();
rtn.storedProcedures = pc.compareStoredProcedure(conn1, conn2);
// Compare the functions between two databases
FunctionComparator fc = new FunctionComparator();
rtn.functions = fc.compareFunctions(conn1, conn2);
// close the database connection
conn1.Close();
conn2.Close();
// Logger
DateTime end = DateTime.Now;
logger.Info("[ compareRemoteAndLocal ] - end time : " + end.ToString() + " ; spend time : " + (end - start).ToString() + "\n");
return(rtn);
}
