public static void TestCopySchema(string pathToSchemaScript)
{
TestHelper.DropDb("TEST_SOURCE");
TestHelper.DropDb("TEST_COPY");
//create the db from sql script
TestHelper.ExecSql("CREATE DATABASE TEST_SOURCE", "");
TestHelper.ExecBatchSql(File.ReadAllText(pathToSchemaScript), "TEST_SOURCE");
SqlConnection.ClearAllPools();
//load the model from newly created db and create a copy
var copy = new Database("TEST_COPY");
copy.Connection = TestHelper.GetConnString("TEST_SOURCE");
copy.Load();
SqlConnection.ClearAllPools();
var scripted = copy.ScriptCreate();
TestHelper.ExecBatchSql(scripted, "master");
//compare the dbs to make sure they are the same
var source = new Database("TEST_SOURCE");
source.Connection = TestHelper.GetConnString("TEST_SOURCE");
source.Load();
copy.Load();
TestCompare(source, copy);
}
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 bool Execute()
{
var sourceDb = new Database();
var targetDb = new Database();
sourceDb.Connection = Source;
targetDb.Connection = Target;
sourceDb.Load();
targetDb.Load();
var diff = sourceDb.Compare(targetDb);
if (diff.IsDiff)
{
Console.WriteLine("Databases are different.");
Console.WriteLine(diff.SummarizeChanges(Verbose));
if (!string.IsNullOrEmpty(OutDiff))
{
Console.WriteLine();
if (!Overwrite && File.Exists(OutDiff))
{
var message = string.Format(
"{0} already exists - set overwrite to true if you want to delete it", OutDiff);
throw new InvalidOperationException(message);
}
File.WriteAllText(OutDiff, diff.Script());
Console.WriteLine("Script to make the databases identical has been created at {0}",
Path.GetFullPath(OutDiff));
}
return true;
}
Console.WriteLine("Databases are identical.");
return false;
}
public string ScriptAlter(Database db)
{
if (RoutineType != RoutineKind.XmlSchemaCollection) {
var regex = new Regex(SqlCreateRegex, RegexOptions.IgnoreCase);
var match = regex.Match(Text);
var group = match.Groups[1];
if (group.Success) {
return ScriptBase(db, Text.Substring(0, group.Index) + "ALTER" + Text.Substring(group.Index + group.Length));
}
}
throw new Exception(string.Format("Unable to script routine {0} {1}.{2} as ALTER", RoutineType, Owner, Name));
}
public void AddDataTable(Database db, string name, string schema)
{
var t = db.FindTable(name, schema);
if (t == null)
{
Console.WriteLine(
"warning: could not find data table {0}.{1}",
schema, name);
}
if (db.DataTables.Contains(t)) return;
db.DataTables.Add(t);
}
public void TestDescIndex() {
TestHelper.DropDb("test");
TestHelper.ExecSql("create database test", "");
TestHelper.ExecSql(@"create table MyTable (Id int)", "test");
TestHelper.ExecSql(@"create nonclustered index MyIndex on MyTable (Id desc)", "test");
var db = new Database("test") {
Connection = TestHelper.GetConnString("test")
};
db.Load();
var result = db.ScriptCreate();
Assert.That(result, Is.StringContaining("CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[MyTable] ([Id] DESC)"));
TestHelper.DropDb("test");
}
private string ScriptBase(Database db, string definition) {
var before = ScriptQuotedIdAndAnsiNulls(db, false);
var after = ScriptQuotedIdAndAnsiNulls(db, true);
if (!string.IsNullOrEmpty(after))
after = Environment.NewLine + "GO" + Environment.NewLine + after;
if (RoutineType == RoutineKind.Trigger)
after +=
string.Format("{0} TRIGGER [{1}].[{2}] ON [{3}].[{4}]", Disabled ? "DISABLE" : "ENABLE", Owner, Name,
RelatedTableSchema, RelatedTableName) + Environment.NewLine + "GO" + Environment.NewLine;
if (string.IsNullOrEmpty(definition))
definition = string.Format("/* missing definition for {0} [{1}].[{2}] */", RoutineType, Owner, Name);
return before + definition + after;
}
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");
SqlConnection.ClearAllPools();
//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);
}
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;
}
private static void TestCompare(Database source, Database copy) {
//compare the dbs to make sure they are the same
Assert.IsFalse(source.Compare(copy).IsDiff);
// get a second opinion
// if you ever find your license key
/*
var cmd = string.Format("/c {0}\\SQLDBDiffConsole.exe {1} {2} {0}\\{3}",
ConfigHelper.SqlDbDiffPath,
"localhost\\SQLEXPRESS " + copy.Name + " NULL NULL Y",
"localhost\\SQLEXPRESS " + source.Name + " NULL NULL Y",
"SqlDbDiff.XML CompareResult.txt null");
Console.WriteLine(cmd);
var proc = new Process();
proc.StartInfo.FileName = "cmd.exe";
proc.StartInfo.Arguments = cmd;
proc.StartInfo.WindowStyle = ProcessWindowStyle.Normal;
proc.Start();
proc.WaitForExit();
Assert.AreEqual("no difference", File.ReadAllLines("CompareResult.txt")[0]);
*/
}
public void SetUp() {
_db = new Database("TestAppendComment");
_db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + _db.Name);
_db.ExecCreate(true);
DBHelper.ExecSql(_db.Connection, SetupTable0Script);
DBHelper.ExecSql(_db.Connection, SetupTable1Script);
DBHelper.ExecSql(_db.Connection, SetupTableTypeScript);
DBHelper.ExecSql(_db.Connection, SetupFKScript);
DBHelper.ExecSql(_db.Connection, SetupFuncScript);
DBHelper.ExecSql(_db.Connection, SetupProcScript);
DBHelper.ExecSql(_db.Connection, SetupRoleScript);
DBHelper.ExecSql(_db.Connection, SetupTrigScript);
DBHelper.ExecSql(_db.Connection, SetupUserScript);
DBHelper.ExecSql(_db.Connection, SetupViewScript);
_db.Dir = _db.Name;
_db.Load();
_db.ScriptToDir();
}
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);
}
public void TestScriptDeletedProc()
{
var source = new Database();
source.Routines.Add(new Routine("dbo", "test", null));
source.FindRoutine("test", "dbo").RoutineType = Routine.RoutineKind.Procedure;
source.FindRoutine("test", "dbo").Text = @"
create procedure [dbo].[test]
as
select * from Table1
";
var target = new Database();
var scriptUp = target.Compare(source).Script();
var scriptDown = source.Compare(target).Script();
Assert.IsTrue(scriptUp.ToLower().Contains("drop procedure [dbo].[test]"));
Assert.IsTrue(scriptDown.ToLower().Contains("create procedure [dbo].[test]"));
}
public void TestViewIndexes()
{
TestHelper.DropDb("TEST");
TestHelper.ExecSql("CREATE DATABASE TEST", "");
TestHelper.ExecSql(@"CREATE TABLE MyTable (Id int, Name nvarchar(250), EndDate datetime)", "TEST");
TestHelper.ExecSql(@"CREATE VIEW dbo.MyView WITH SCHEMABINDING as SELECT t.Id, t.Name, t.EndDate from dbo.MyTable t", "TEST");
TestHelper.ExecSql(@"CREATE UNIQUE CLUSTERED INDEX MyIndex ON MyView (Id, Name)", "TEST");
var db = new Database("TEST") {
Connection = TestHelper.GetConnString("TEST")
};
db.Load();
var result = db.ScriptCreate();
TestHelper.DropDb("TEST");
Assert.That(result, Is.StringContaining("CREATE UNIQUE CLUSTERED INDEX [MyIndex] ON [dbo].[MyView] ([Id], [Name])"));
}
public void TestDiffScript()
{
TestHelper.DropDb("TEST_SOURCE");
TestHelper.DropDb("TEST_COPY");
//create the dbs from sql script
var script = File.ReadAllText(ConfigHelper.TestSchemaDir + "\\BOP_QUOTE.sql");
TestHelper.ExecSql("CREATE DATABASE TEST_SOURCE", "");
TestHelper.ExecBatchSql(script, "TEST_SOURCE");
script = File.ReadAllText(ConfigHelper.TestSchemaDir + "\\BOP_QUOTE_2.sql");
TestHelper.ExecSql("CREATE DATABASE TEST_COPY", "");
TestHelper.ExecBatchSql(script, "TEST_COPY");
var source = new Database("TEST_SOURCE");
source.Connection = TestHelper.GetConnString("TEST_SOURCE");
source.Load();
var copy = new Database("TEST_COPY");
copy.Connection = TestHelper.GetConnString("TEST_COPY");
copy.Load();
//execute migration script to make SOURCE the same as COPY
var diff = copy.Compare(source);
TestHelper.ExecBatchSql(diff.Script(), "TEST_SOURCE");
//compare the dbs to make sure they are the same
var cmd = string.Format("/c {0}\\SQLDBDiffConsole.exe {1} {2} {0}\\{3}", ConfigHelper.SqlDbDiffPath,
"localhost\\SQLEXPRESS TEST_COPY NULL NULL Y", "localhost\\SQLEXPRESS TEST_SOURCE NULL NULL Y",
"SqlDbDiff.XML CompareResult.txt null");
var proc = new Process();
proc.StartInfo.FileName = "cmd.exe";
proc.StartInfo.Arguments = cmd;
proc.StartInfo.WindowStyle = ProcessWindowStyle.Normal;
proc.Start();
proc.WaitForExit();
Assert.AreEqual("no difference", File.ReadAllLines("CompareResult.txt")[0]);
}
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 void TestTableIndexesWithFilter()
{
TestHelper.DropDb("TEST");
TestHelper.ExecSql("CREATE DATABASE TEST","");
TestHelper.ExecSql(@"CREATE TABLE MyTable (Id int, EndDate datetime)", "TEST");
TestHelper.ExecSql(@"CREATE NONCLUSTERED INDEX MyIndex ON MyTable (Id) WHERE (EndDate) IS NULL","TEST");
var db = new Database("TEST") {
Connection = TestHelper.GetConnString("TEST")
};
db.Load();
var result = db.ScriptCreate();
TestHelper.DropDb("TEST");
Assert.That(result, Is.StringContaining("CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[MyTable] ([Id]) WHERE ([EndDate] IS NULL)"));
}
public Routine(string owner, string name, Database db)
{
Owner = owner;
Name = name;
Db = db;
}
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 TestScriptTableType()
{
var setupSQL1 = @"
CREATE TYPE [dbo].[MyTableType] AS TABLE(
[ID] [nvarchar](250) NULL,
[Value] [numeric](5, 1) NULL,
[LongNVarchar] [nvarchar](max) NULL
)
";
var db = new Database("TestScriptTableType");
db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + db.Name);
db.ExecCreate(true);
DBHelper.ExecSql(db.Connection, setupSQL1);
db.Dir = db.Name;
db.Load();
db.ScriptToDir();
Assert.AreEqual(1, db.TableTypes.Count());
Assert.AreEqual(250, db.TableTypes[0].Columns.Items[0].Length);
Assert.AreEqual(1, db.TableTypes[0].Columns.Items[1].Scale);
Assert.AreEqual(5, db.TableTypes[0].Columns.Items[1].Precision);
Assert.AreEqual(-1, db.TableTypes[0].Columns.Items[2].Length); //nvarchar(max) is encoded as -1
Assert.AreEqual("MyTableType", db.TableTypes[0].Name);
Assert.IsTrue(File.Exists(db.Name + "\\table_types\\TYPE_MyTableType.sql"));
}
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.Columns.Add(new Column("category", "int", false, null) { Position = 4 });
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 });
formType.AddConstraint(Constraint.CreateCheckedConstraint("CK_FormType", false, "([code]<(5))"));
var categoryType = new Table("dbo", "CategoryType");
categoryType.Columns.Add(new Column("id", "int", false, null) { Position = 1 });
categoryType.Columns.Add(new Column("Category", "varchar", 10, false, null) { Position = 2 });
categoryType.AddConstraint(new Constraint("PK_CategoryType", "PRIMARY KEY", "id") { Clustered = true, Unique = true });
var emptyTable = new Table("dbo", "EmptyTable");
emptyTable.Columns.Add(new Column("code", "tinyint", false, null) {Position = 1});
emptyTable.AddConstraint(new Constraint("PK_EmptyTable", "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 fk_location_category = new ForeignKey("FK_Location_category");
fk_location_category.Table = loc;
fk_location_category.Columns.Add("category");
fk_location_category.RefTable = categoryType;
fk_location_category.RefColumns.Add("id");
fk_location_category.OnUpdate = "NO ACTION";
fk_location_category.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(categoryType);
db.Tables.Add(emptyTable);
db.Tables.Add(loc);
db.TableTypes.Add(tt_codedesc);
db.ForeignKeys.Add(fk_policy_formType);
db.ForeignKeys.Add(fk_location_policy);
db.ForeignKeys.Add(fk_location_category);
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.DataTables.Add(emptyTable);
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) {
if (t.Name == "EmptyTable") {
Assert.IsFalse(File.Exists(db.Name + "\\data\\" + t.Name + ".tsv"));
} else {
Assert.IsTrue(File.Exists(db.Name + "\\data\\" + t.Name + ".tsv"));
}
}
foreach (var t in db.Tables) {
var tblFile = db.Name + "\\tables\\" + t.Name + ".sql";
Assert.IsTrue(File.Exists(tblFile));
// Test that the constraints are ordered in the file
string script = File.ReadAllText(tblFile);
int cindex = -1;
foreach (var ckobject in t.Constraints.OrderBy(x => x.Name))
{
var thisindex = script.IndexOf(ckobject.ScriptCreate());
Assert.Greater(thisindex, cindex, "Constraints are not ordered.");
cindex = thisindex;
}
}
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);
}
// Test that the foreign keys are ordered in the file
foreach (var t in db.Tables)
{
var fksFile = db.Name + "\\foreign_keys\\" + t.Name + ".sql";
if (File.Exists(fksFile))
{
string script = File.ReadAllText(fksFile);
int fkindex = -1;
foreach (var fkobject in db.ForeignKeys.Where(x => x.Table == t).OrderBy(x => x.Name))
{
var thisindex = script.IndexOf(fkobject.ScriptCreate());
Assert.Greater(thisindex, fkindex, "Foreign keys are not ordered.");
fkindex = thisindex;
}
}
}
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);
}
public void TestScriptTableTypePrimaryKey()
{
var setupSQL1 = @"
CREATE TYPE [dbo].[MyTableType] AS TABLE(
[ID] [int] NOT NULL,
[Value] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID]
)
)
";
var db = new Database("TestScriptTableTypePrimaryKey");
db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + db.Name);
db.ExecCreate(true);
DBHelper.ExecSql(db.Connection, setupSQL1);
db.Dir = db.Name;
db.Load();
db.ScriptToDir();
Assert.AreEqual(1, db.TableTypes.Count());
Assert.AreEqual(1, db.TableTypes[0].PrimaryKey.Columns.Count);
Assert.AreEqual("ID", db.TableTypes[0].PrimaryKey.Columns[0].ColumnName);
Assert.AreEqual(50, db.TableTypes[0].Columns.Items[1].Length);
Assert.AreEqual("MyTableType", db.TableTypes[0].Name);
Assert.IsTrue(File.Exists(db.Name + "\\table_types\\TYPE_MyTableType.sql"));
Assert.IsTrue(File.ReadAllText(db.Name + "\\table_types\\TYPE_MyTableType.sql").Contains("PRIMARY KEY"));
}
public void ScriptToDir_ShouldAddCollation_ToVarCharTypeColumns()
{
var setupSQL1 = @"
CREATE TYPE [dbo].[MyTableType] AS TABLE(
[ID] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[Value] [numeric](5, 1) NULL,
[LongNVarchar] [nvarchar](max) NULL
)
";
var db = new Database("TestScriptTableType");
db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + db.Name);
db.ExecCreate(true);
DBHelper.ExecSql(db.Connection, setupSQL1);
db.Dir = db.Name;
db.Load();
db.ScriptToDir();
var scriptedType = File.ReadAllText(db.Name + "\\table_types\\TYPE_MyTableType.sql");
Assert.That(scriptedType, Is.StringContaining("[ID] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CS_AS NULL"));
Assert.That(scriptedType, Is.StringContaining("[LongNVarchar] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL"));
}
public void TestScriptViewInsteadOfTrigger()
{
var setupSQL1 = @"
CREATE TABLE [dbo].[t1]
(
a INT NOT NULL,
CONSTRAINT [PK] PRIMARY KEY (a)
)
";
var setupSQL2 = @"
CREATE VIEW [dbo].[v1] AS
SELECT * FROM t1
";
var setupSQL3 = @"
CREATE TRIGGER [dbo].[TR_v1] ON [dbo].[v1] INSTEAD OF DELETE AS
DELETE FROM [dbo].[t1] FROM [dbo].[t1] INNER JOIN DELETED ON DELETED.a = [dbo].[t1].a
";
var db = new Database("TestScriptViewInsteadOfTrigger");
db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + db.Name);
db.ExecCreate(true);
DBHelper.ExecSql(db.Connection, setupSQL1);
DBHelper.ExecSql(db.Connection, setupSQL2);
DBHelper.ExecSql(db.Connection, setupSQL3);
db.Dir = db.Name;
db.Load();
// Required in order to expose the exception
db.ScriptToDir();
var triggers = db.Routines.Where(x => x.RoutineType == Routine.RoutineKind.Trigger).ToList();
Assert.AreEqual(1, triggers.Count());
Assert.AreEqual("TR_v1", triggers[0].Name);
Assert.IsTrue(File.Exists(db.Name + "\\triggers\\TR_v1.sql"));
}
public void TestScriptToDirOnlyCreatesNecessaryFolders()
{
var db = new Database("TestEmptyDB");
db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + db.Name);
db.ExecCreate(true);
db.Dir = db.Name;
db.Load();
if (Directory.Exists(db.Dir)) // if the directory exists, delete it to make it a fair test
{
Directory.Delete(db.Dir, true);
}
db.ScriptToDir();
Assert.AreEqual(0, db.Assemblies.Count);
Assert.AreEqual(0, db.DataTables.Count);
Assert.AreEqual(0, db.ForeignKeys.Count);
Assert.AreEqual(0, db.Routines.Count);
Assert.AreEqual(0, db.Schemas.Count);
Assert.AreEqual(0, db.Synonyms.Count);
Assert.AreEqual(0, db.Tables.Count);
Assert.AreEqual(0, db.TableTypes.Count);
Assert.AreEqual(0, db.Users.Count);
Assert.AreEqual(0, db.ViewIndexes.Count);
Assert.IsTrue(Directory.Exists(db.Name));
Assert.IsTrue(File.Exists(db.Name + "\\props.sql"));
//Assert.IsFalse(File.Exists(db.Name + "\\schemas.sql"));
Assert.IsFalse(Directory.Exists(db.Name + "\\assemblies"));
Assert.IsFalse(Directory.Exists(db.Name + "\\data"));
Assert.IsFalse(Directory.Exists(db.Name + "\\foreign_keys"));
foreach (var routineType in Enum.GetNames(typeof(Routine.RoutineKind)))
{
var dir = routineType.ToLower() + "s";
Assert.IsFalse(Directory.Exists(db.Name + "\\" + dir));
}
Assert.IsFalse(Directory.Exists(db.Name + "\\synonyms"));
Assert.IsFalse(Directory.Exists(db.Name + "\\tables"));
Assert.IsFalse(Directory.Exists(db.Name + "\\table_types"));
Assert.IsFalse(Directory.Exists(db.Name + "\\users"));
}
public void TestFindTableRegEx()
{
var db = new Database();
db.Tables.Add(new Table("dbo", "cmicDeductible"));
db.Tables.Add(new Table("dbo", "cmicZipCode"));
db.Tables.Add(new Table("dbo", "cmicState"));
db.Tables.Add(new Table("dbo", "Policy"));
db.Tables.Add(new Table("dbo", "Location"));
db.Tables.Add(new Table("dbo", "Rate"));
Assert.AreEqual(3, db.FindTablesRegEx("^cmic").Count);
Assert.AreEqual(1, db.FindTablesRegEx("Location").Count);
}
public void TestScriptTriggerWithNoSets()
{
var setupSQL1 = @"
CREATE TABLE [dbo].[t1]
(
a INT NOT NULL,
CONSTRAINT [PK] PRIMARY KEY (a)
)
";
var setupSQL2 = @"
CREATE TABLE [dbo].[t2]
(
a INT NOT NULL
)
";
var setupSQL3 = @"
CREATE TRIGGER [dbo].[TR_1] ON [dbo].[t1] FOR UPDATE,INSERT
AS INSERT INTO [dbo].[t2](a) SELECT a FROM INSERTED";
var db = new Database("TestScriptTrigger");
// Set these properties to the defaults so they are not scripted
db.FindProp("QUOTED_IDENTIFIER").Value = "ON";
db.FindProp("ANSI_NULLS").Value = "ON";
db.Connection = ConfigHelper.TestDB.Replace("database=TESTDB", "database=" + db.Name);
db.ExecCreate(true);
DBHelper.ExecSql(db.Connection, setupSQL1);
DBHelper.ExecSql(db.Connection, setupSQL2);
DBHelper.ExecSql(db.Connection, setupSQL3);
db.Dir = db.Name;
db.Load();
db.ScriptToDir();
var script = File.ReadAllText(db.Name + "\\triggers\\TR_1.sql");
StringAssert.DoesNotContain("INSERTEDENABLE", script);
}
private static Database CreateTestDbWithScript(string scriptToExecute)
{
TestHelper.DropDb("TEST");
TestHelper.ExecSql("CREATE DATABASE TEST", "");
TestHelper.ExecSql(scriptToExecute, "TEST");
var db = new Database("TEST")
{
Connection = TestHelper.GetConnString("TEST")
};
db.Load();
return db;
}