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); }