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")
{
IndexType = "CLUSTERED",
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")
{
IndexType = "CLUSTERED",
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")
{
IndexType = "CLUSTERED",
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")
{
IndexType = "CLUSTERED",
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")
{
IndexType = "CLUSTERED",
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")
{
IndexType = "NONCLUSTERED"
});
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 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")
{
IndexType = "CLUSTERED"
});
var t2 = new Table("dbo", "t2");
t2.Columns.Add(new Column("col1", "int", false, null)
{
Position = 1
});
var col2 = new Column("col2", "int", false, null)
{
Position = 2
};
col2.Default = new Default(t2, col2, "df_col2", "((0))", false);
t2.Columns.Add(col2);
t2.Columns.Add(new Column("col3", "int", false, null)
{
Position = 3
});
t2.AddConstraint(new Constraint("pk_t2", "PRIMARY KEY", "col1")
{
IndexType = "CLUSTERED"
});
t2.AddConstraint(Constraint.CreateCheckedConstraint("ck_col2", true, false, "([col2]>(0))"));
t2.AddConstraint(new Constraint("IX_col3", "UNIQUE", "col3")
{
IndexType = "NONCLUSTERED"
});
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)
{
var copy = db2.FindTable(t.Name, t.Owner);
Assert.IsNotNull(copy);
Assert.IsFalse(copy.Compare(t).IsDiff);
}
}
