private string ScriptQuotedIdAndAnsiNulls(Database db, bool databaseDefaults) { var script = ""; var defaultQuotedId = !QuotedId; if (db != null && db.FindProp("QUOTED_IDENTIFIER") != null) { defaultQuotedId = db.FindProp("QUOTED_IDENTIFIER").Value == "ON"; } if (defaultQuotedId != QuotedId) { script += string.Format(@"SET QUOTED_IDENTIFIER {0} {1}GO{1}", ((databaseDefaults ? defaultQuotedId : QuotedId) ? "ON" : "OFF"), Environment.NewLine); } var defaultAnsiNulls = !AnsiNull; if (db != null && db.FindProp("ANSI_NULLS") != null) { defaultAnsiNulls = db.FindProp("ANSI_NULLS").Value == "ON"; } if (defaultAnsiNulls != AnsiNull) { script += string.Format(@"SET ANSI_NULLS {0} {1}GO{1}", ((databaseDefaults ? defaultAnsiNulls : AnsiNull) ? "ON" : "OFF"), Environment.NewLine); } return script; }
public void TestCollate() { var pathToSchema = ConfigHelper.TestSchemaDir + "/SANDBOX3_GBL.SQL"; TestHelper.DropDb("TEST_SOURCE"); //create the db from sql script TestHelper.ExecSql("CREATE DATABASE TEST_SOURCE", ""); TestHelper.ExecBatchSql(File.ReadAllText(pathToSchema), "TEST_SOURCE"); //load the model from newly created db and check collation var copy = new Database("TEST_COPY"); copy.Connection = TestHelper.GetConnString("TEST_SOURCE"); copy.Load(); Assert.AreEqual("SQL_Latin1_General_CP1_CI_AS", copy.FindProp("COLLATE").Value); }
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 TestScriptToDir() { var policy = new Table("dbo", "Policy"); policy.Columns.Add(new Column("id", "int", false, null) {Position = 1}); policy.Columns.Add(new Column("form", "tinyint", false, null) {Position = 2}); policy.AddConstraint(new Constraint("PK_Policy", "PRIMARY KEY", "id") { Clustered = true, Unique = true }); policy.Columns.Items[0].Identity = new Identity(1, 1); var loc = new Table("dbo", "Location"); loc.Columns.Add(new Column("id", "int", false, null) {Position = 1}); loc.Columns.Add(new Column("policyId", "int", false, null) {Position = 2}); loc.Columns.Add(new Column("storage", "bit", false, null) {Position = 3}); loc.AddConstraint(new Constraint("PK_Location", "PRIMARY KEY", "id") { Clustered = true, Unique = true }); loc.Columns.Items[0].Identity = new Identity(1, 1); var formType = new Table("dbo", "FormType"); formType.Columns.Add(new Column("code", "tinyint", false, null) {Position = 1}); formType.Columns.Add(new Column("desc", "varchar", 10, false, null) {Position = 2}); formType.AddConstraint(new Constraint("PK_FormType", "PRIMARY KEY", "code") { Clustered = true, Unique = true }); var fk_policy_formType = new ForeignKey("FK_Policy_FormType"); fk_policy_formType.Table = policy; fk_policy_formType.Columns.Add("form"); fk_policy_formType.RefTable = formType; fk_policy_formType.RefColumns.Add("code"); fk_policy_formType.OnUpdate = "NO ACTION"; fk_policy_formType.OnDelete = "NO ACTION"; var fk_location_policy = new ForeignKey("FK_Location_Policy"); fk_location_policy.Table = loc; fk_location_policy.Columns.Add("policyId"); fk_location_policy.RefTable = policy; fk_location_policy.RefColumns.Add("id"); fk_location_policy.OnUpdate = "NO ACTION"; fk_location_policy.OnDelete = "CASCADE"; var tt_codedesc = new Table("dbo", "CodeDesc"); tt_codedesc.IsType = true; tt_codedesc.Columns.Add(new Column("code", "tinyint", false, null) { Position = 1 }); tt_codedesc.Columns.Add(new Column("desc", "varchar", 10, false, null) { Position = 2 }); tt_codedesc.AddConstraint(new Constraint("PK_CodeDesc", "PRIMARY KEY", "code")); var db = new Database("ScriptToDirTest"); db.Tables.Add(policy); db.Tables.Add(formType); db.Tables.Add(loc); db.TableTypes.Add(tt_codedesc); db.ForeignKeys.Add(fk_policy_formType); db.ForeignKeys.Add(fk_location_policy); db.FindProp("COMPATIBILITY_LEVEL").Value = "110"; db.FindProp("COLLATE").Value = "SQL_Latin1_General_CP1_CI_AS"; db.FindProp("AUTO_CLOSE").Value = "OFF"; db.FindProp("AUTO_SHRINK").Value = "ON"; db.FindProp("ALLOW_SNAPSHOT_ISOLATION").Value = "ON"; db.FindProp("READ_COMMITTED_SNAPSHOT").Value = "OFF"; db.FindProp("RECOVERY").Value = "SIMPLE"; db.FindProp("PAGE_VERIFY").Value = "CHECKSUM"; db.FindProp("AUTO_CREATE_STATISTICS").Value = "ON"; db.FindProp("AUTO_UPDATE_STATISTICS").Value = "ON"; db.FindProp("AUTO_UPDATE_STATISTICS_ASYNC").Value = "ON"; db.FindProp("ANSI_NULL_DEFAULT").Value = "ON"; db.FindProp("ANSI_NULLS").Value = "ON"; db.FindProp("ANSI_PADDING").Value = "ON"; db.FindProp("ANSI_WARNINGS").Value = "ON"; db.FindProp("ARITHABORT").Value = "ON"; db.FindProp("CONCAT_NULL_YIELDS_NULL").Value = "ON"; db.FindProp("NUMERIC_ROUNDABORT").Value = "ON"; db.FindProp("QUOTED_IDENTIFIER").Value = "ON"; db.FindProp("RECURSIVE_TRIGGERS").Value = "ON"; db.FindProp("CURSOR_CLOSE_ON_COMMIT").Value = "ON"; db.FindProp("CURSOR_DEFAULT").Value = "LOCAL"; db.FindProp("TRUSTWORTHY").Value = "ON"; db.FindProp("DB_CHAINING").Value = "ON"; db.FindProp("PARAMETERIZATION").Value = "FORCED"; db.FindProp("DATE_CORRELATION_OPTIMIZATION").Value = "ON"; db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + db.Name); db.ExecCreate(true); DBHelper.ExecSql(db.Connection, " insert into formType ([code], [desc]) values (1, 'DP-1')\n" + "insert into formType ([code], [desc]) values (2, 'DP-2')\n" + "insert into formType ([code], [desc]) values (3, 'DP-3')"); db.DataTables.Add(formType); db.Dir = db.Name; if (Directory.Exists(db.Dir)) Directory.Delete(db.Dir, true); db.ScriptToDir(); Assert.IsTrue(Directory.Exists(db.Name)); Assert.IsTrue(Directory.Exists(db.Name + "\\data")); Assert.IsTrue(Directory.Exists(db.Name + "\\tables")); Assert.IsTrue(Directory.Exists(db.Name + "\\foreign_keys")); foreach (var t in db.DataTables) { Assert.IsTrue(File.Exists(db.Name + "\\data\\" + t.Name + ".tsv")); } foreach (var t in db.Tables) { Assert.IsTrue(File.Exists(db.Name + "\\tables\\" + t.Name + ".sql")); } foreach (var t in db.TableTypes) { Assert.IsTrue(File.Exists(db.Name + "\\table_types\\TYPE_" + t.Name + ".sql")); } foreach (var expected in db.ForeignKeys.Select(fk => db.Name + "\\foreign_keys\\" + fk.Table.Name + ".sql")) { Assert.IsTrue(File.Exists(expected), "File does not exist" + expected); } var copy = new Database("ScriptToDirTestCopy"); copy.Dir = db.Dir; copy.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + copy.Name); copy.CreateFromDir(true); copy.Load(); TestCompare(db, copy); }