|
public FindForeignKey ( string name, string owner ) : |
||
| name | string | |
| owner | string | |
| return | ||
public void TestMultiColumnKey() {
var t1 = new Table("dbo", "t1");
t1.Columns.Add(new Column("c2", "varchar", 10, false, null));
t1.Columns.Add(new Column("c1", "int", false, null));
t1.AddConstraint(new Constraint("pk_t1", "PRIMARY KEY", "c1,c2"));
var t2 = new Table("dbo", "t2");
t2.Columns.Add(new Column("c1", "int", false, null));
t2.Columns.Add(new Column("c2", "varchar", 10, false, null));
t2.Columns.Add(new Column("c3", "int", false, null));
var fk = new ForeignKey(t2, "fk_test", "c3,c2", t1, "c1,c2");
var db = new Database("TESTDB");
db.Tables.Add(t1);
db.Tables.Add(t2);
db.ForeignKeys.Add(fk);
db.Connection = TestHelper.GetConnString("TESTDB");
db.ExecCreate(true);
db.Load();
Assert.AreEqual("c3", db.FindForeignKey("fk_test", "dbo").Columns[0]);
Assert.AreEqual("c2", db.FindForeignKey("fk_test", "dbo").Columns[1]);
Assert.AreEqual("c1", db.FindForeignKey("fk_test", "dbo").RefColumns[0]);
Assert.AreEqual("c2", db.FindForeignKey("fk_test", "dbo").RefColumns[1]);
db.ExecCreate(true);
}
public DatabaseDiff Compare(Database db) {
var diff = new DatabaseDiff();
diff.Db = db;
//compare database properties
foreach (var p in from p in Props
let p2 = db.FindProp(p.Name)
where p.Script() != p2.Script()
select p) {
diff.PropsChanged.Add(p);
}
//get tables added and changed
foreach (var tables in new[] {Tables, TableTypes}) {
foreach (var t in tables) {
var t2 = db.FindTable(t.Name, t.Owner, t.IsType);
if (t2 == null) {
diff.TablesAdded.Add(t);
} else {
//compare mutual tables
var tDiff = t.Compare(t2);
if (tDiff.IsDiff) {
if (t.IsType) {
// types cannot be altered...
diff.TableTypesDiff.Add(t);
} else {
diff.TablesDiff.Add(tDiff);
}
}
}
}
}
//get deleted tables
foreach (var t in db.Tables.Concat(db.TableTypes).Where(t => FindTable(t.Name, t.Owner, t.IsType) == null)) {
diff.TablesDeleted.Add(t);
}
//get procs added and changed
foreach (var r in Routines) {
var r2 = db.FindRoutine(r.Name, r.Owner);
if (r2 == null) {
diff.RoutinesAdded.Add(r);
} else {
//compare mutual procs
if (r.Text.Trim() != r2.Text.Trim()) {
diff.RoutinesDiff.Add(r);
}
}
}
//get procs deleted
foreach (var r in db.Routines.Where(r => FindRoutine(r.Name, r.Owner) == null)) {
diff.RoutinesDeleted.Add(r);
}
//get added and compare mutual foreign keys
foreach (var fk in ForeignKeys) {
var fk2 = db.FindForeignKey(fk.Name, fk.Table.Owner);
if (fk2 == null) {
diff.ForeignKeysAdded.Add(fk);
} else {
if (fk.ScriptCreate() != fk2.ScriptCreate()) {
diff.ForeignKeysDiff.Add(fk);
}
}
}
//get deleted foreign keys
foreach (var fk in db.ForeignKeys.Where(fk => FindForeignKey(fk.Name, fk.Table.Owner) == null)) {
diff.ForeignKeysDeleted.Add(fk);
}
//get added and compare mutual assemblies
foreach (var a in Assemblies) {
var a2 = db.FindAssembly(a.Name);
if (a2 == null) {
diff.AssembliesAdded.Add(a);
} else {
if (a.ScriptCreate() != a2.ScriptCreate()) {
diff.AssembliesDiff.Add(a);
}
}
}
//get deleted assemblies
foreach (var a in db.Assemblies.Where(a => FindAssembly(a.Name) == null)) {
diff.AssembliesDeleted.Add(a);
}
//get added and compare mutual users
foreach (var u in Users) {
var u2 = db.FindUser(u.Name);
if (u2 == null) {
diff.UsersAdded.Add(u);
} else {
if (u.ScriptCreate() != u2.ScriptCreate()) {
diff.UsersDiff.Add(u);
}
}
}
//get deleted users
foreach (var u in db.Users.Where(u => FindUser(u.Name) == null)) {
diff.UsersDeleted.Add(u);
}
//get added and compare view indexes
foreach (var c in ViewIndexes) {
var c2 = db.FindViewIndex(c.Name);
if (c2 == null) {
diff.ViewIndexesAdded.Add(c);
} else {
if (c.ScriptCreate() != c2.ScriptCreate()) {
diff.ViewIndexesDiff.Add(c);
}
}
}
//get deleted view indexes
foreach (var c in db.ViewIndexes.Where(c => FindViewIndex(c.Name) == null)) {
diff.ViewIndexesDeleted.Add(c);
}
//get added and compare synonyms
foreach (var s in Synonyms) {
var s2 = db.FindSynonym(s.Name, s.Owner);
if (s2 == null) {
diff.SynonymsAdded.Add(s);
} else {
if (s.BaseObjectName != s2.BaseObjectName) {
diff.SynonymsDiff.Add(s);
}
}
}
//get deleted synonyms
foreach (var s in db.Synonyms.Where(s => FindSynonym(s.Name, s.Owner) == null)) {
diff.SynonymsDeleted.Add(s);
}
return diff;
}
public void TestScriptFKSameName()
{
var setupSQL = @"
CREATE SCHEMA [s2] AUTHORIZATION [dbo]
CREATE TABLE [dbo].[t1a]
(
a INT NOT NULL,
CONSTRAINT [PK_1a] PRIMARY KEY (a)
)
CREATE TABLE [dbo].[t1b]
(
a INT NOT NULL,
CONSTRAINT [FKName] FOREIGN KEY ([a]) REFERENCES [dbo].[t1a] ([a]) ON UPDATE CASCADE
)
CREATE TABLE [s2].[t2a]
(
a INT NOT NULL,
CONSTRAINT [PK_2a] PRIMARY KEY (a)
)
CREATE TABLE [s2].[t2b]
(
a INT NOT NULL,
CONSTRAINT [FKName] FOREIGN KEY ([a]) REFERENCES [s2].[t2a] ([a]) ON DELETE CASCADE
)
";
var db = new Database("TestScriptFKSameName");
db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + db.Name);
db.ExecCreate(true);
DBHelper.ExecSql(db.Connection, setupSQL);
db.Dir = db.Name;
db.Load();
// Required in order to expose the exception
db.ScriptToDir();
Assert.AreEqual(2, db.ForeignKeys.Count());
Assert.AreEqual(db.ForeignKeys[0].Name, db.ForeignKeys[1].Name);
Assert.AreNotEqual(db.ForeignKeys[0].Table.Owner, db.ForeignKeys[1].Table.Owner);
Assert.AreEqual("CASCADE", db.FindForeignKey("FKName", "dbo").OnUpdate);
Assert.AreEqual("NO ACTION", db.FindForeignKey("FKName", "s2").OnUpdate);
Assert.AreEqual("NO ACTION", db.FindForeignKey("FKName", "dbo").OnDelete);
Assert.AreEqual("CASCADE", db.FindForeignKey("FKName", "s2").OnDelete);
}