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