public TableDiff Compare(Table t) {
var diff = new TableDiff();
diff.Owner = t.Owner;
diff.Name = t.Name;
//get additions and compare mutual columns
foreach (var c in Columns.Items) {
var c2 = t.Columns.Find(c.Name);
if (c2 == null) {
diff.ColumnsAdded.Add(c);
} else {
//compare mutual columns
var cDiff = c.Compare(c2);
if (cDiff.IsDiff) {
diff.ColumnsDiff.Add(cDiff);
}
}
}
//get deletions
foreach (var c in t.Columns.Items.Where(c => Columns.Find(c.Name) == null)) {
diff.ColumnsDropped.Add(c);
}
//get added and compare mutual constraints
foreach (var c in Constraints) {
var c2 = t.FindConstraint(c.Name);
if (c2 == null) {
diff.ConstraintsAdded.Add(c);
} else {
if (c.ScriptCreate() != c2.ScriptCreate()) {
diff.ConstraintsChanged.Add(c);
}
}
}
//get deleted constraints
foreach (var c in t.Constraints.Where(c => FindConstraint(c.Name) == null)) {
diff.ConstraintsDeleted.Add(c);
}
return diff;
}
public void TestScript() {
var db = new Database("TEST_TEMP");
var t1 = new Table("dbo", "t1");
t1.Columns.Add(new Column("col1", "int", false, null) {Position = 1});
t1.Columns.Add(new Column("col2", "int", false, null) {Position = 2});
t1.AddConstraint(new Constraint("pk_t1", "PRIMARY KEY", "col1,col2"));
t1.FindConstraint("pk_t1").Clustered = true;
var t2 = new Table("dbo", "t2");
t2.Columns.Add(new Column("col1", "int", false, null) {Position = 1});
t2.Columns.Add(new Column("col2", "int", false, null) {Position = 2});
t2.Columns.Add(new Column("col3", "int", false, null) {Position = 3});
t2.AddConstraint(new Constraint("pk_t2", "PRIMARY KEY", "col1"));
t2.FindConstraint("pk_t2").Clustered = true;
t2.AddConstraint(new Constraint("IX_col3", "UNIQUE", "col3"));
db.ForeignKeys.Add(new ForeignKey(t2, "fk_t2_t1", "col2,col3", t1, "col1,col2"));
db.Tables.Add(t1);
db.Tables.Add(t2);
TestHelper.DropDb("TEST_TEMP");
SqlConnection.ClearAllPools();
TestHelper.ExecBatchSql(db.ScriptCreate(), "master");
var db2 = new Database();
db2.Connection = TestHelper.GetConnString("TEST_TEMP");
db2.Load();
TestHelper.DropDb("TEST_TEMP");
foreach (var t in db.Tables) {
Assert.IsNotNull(db2.FindTable(t.Name, t.Owner));
Assert.IsFalse(db2.FindTable(t.Name, t.Owner).Compare(t).IsDiff);
}
}
public TableDiff Compare(Table t) {
var diff = new TableDiff();
diff.Owner = t.Owner;
diff.Name = t.Name;
//get additions and compare mutual columns
foreach (var c in Columns.Items) {
var c2 = t.Columns.Find(c.Name);
if (c2 == null) {
diff.ColumnsAdded.Add(c);
} else {
//compare mutual columns
var cDiff = c.Compare(c2);
if (cDiff.IsDiff) {
diff.ColumnsDiff.Add(cDiff);
}
}
}
//get deletions
foreach (var c in t.Columns.Items.Where(c => Columns.Find(c.Name) == null)) {
diff.ColumnsDropped.Add(c);
}
if (!t.IsType) {
//get added and compare mutual constraints
foreach (var c in Constraints) {
var c2 = t.FindConstraint(c.Name);
if (c2 == null) {
diff.ConstraintsAdded.Add(c);
} else {
if (c.ScriptCreate() != c2.ScriptCreate()) {
diff.ConstraintsChanged.Add(c);
}
}
}
//get deleted constraints
foreach (var c in t.Constraints.Where(c => FindConstraint(c.Name) == null)){
diff.ConstraintsDeleted.Add(c);
}
} else {
// compare constraints on table types, which can't be named in the script, but have names in the DB
var dest = Constraints.ToList();
var src = t.Constraints.ToList();
var j = from c1 in dest
join c2 in src on c1.ScriptCreate() equals c2.ScriptCreate() into match //new { c1.Type, c1.Unique, c1.Clustered, Columns = string.Join(",", c1.Columns.ToArray()), IncludedColumns = string.Join(",", c1.IncludedColumns.ToArray()) } equals new { c2.Type, c2.Unique, c2.Clustered, Columns = string.Join(",", c2.Columns.ToArray()), IncludedColumns = string.Join(",", c2.IncludedColumns.ToArray()) } into match
from m in match.DefaultIfEmpty()
select new { c1, m };
foreach (var c in j) {
if (c.m == null) {
diff.ConstraintsAdded.Add(c.c1);
} else {
src.Remove(c.m);
}
}
foreach (var c in src) {
diff.ConstraintsDeleted.Add(c);
}
}
return diff;
}