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