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.Constraints.Add(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").Columns[0]);
Assert.AreEqual("c2", db.FindForeignKey("fk_test").Columns[1]);
Assert.AreEqual("c1", db.FindForeignKey("fk_test").RefColumns[0]);
Assert.AreEqual("c2", db.FindForeignKey("fk_test").RefColumns[1]);
db.ExecCreate(true);
}
public void TestExportData()
{
var t = new Table("dbo", "Status");
t.Columns.Add(new Column("id", "int", false, null));
t.Columns.Add(new Column("code", "char", 1, false, null));
t.Columns.Add(new Column("description", "varchar", 20, false, null));
t.Columns.Find("id").Identity = new Identity(1, 1);
t.Constraints.Add(new Constraint("PK_Status", "PRIMARY KEY", "id"));
string conn = TestHelper.GetConnString("TESTDB");
DBHelper.DropDb(conn);
DBHelper.CreateDb(conn);
SqlConnection.ClearAllPools();
DBHelper.ExecBatchSql(conn, t.ScriptCreate());
string dataIn =
@"1 R Ready
2 P Processing
3 F Frozen
";
t.ImportData(conn, dataIn);
string dataOut = t.ExportData(conn);
Assert.AreEqual(dataIn, dataOut);
}
public void TestScript()
{
var person = new Table("dbo", "Person");
person.Columns.Add(new Column("id", "int", false, null));
person.Columns.Add(new Column("name", "varchar", 50, false, null));
person.Columns.Find("id").Identity = new Identity(1, 1);
person.Constraints.Add(new Constraint("PK_Person", "PRIMARY KEY", "id"));
var address = new Table("dbo", "Address");
address.Columns.Add(new Column("id", "int", false, null));
address.Columns.Add(new Column("personId", "int", false, null));
address.Columns.Add(new Column("street", "varchar", 50, false, null));
address.Columns.Add(new Column("city", "varchar", 50, false, null));
address.Columns.Add(new Column("state", "char", 2, false, null));
address.Columns.Add(new Column("zip", "varchar", 5, false, null));
address.Columns.Find("id").Identity = new Identity(1, 1);
address.Constraints.Add(new Constraint("PK_Address", "PRIMARY KEY", "id"));
var fk = new ForeignKey(address, "FK_Address_Person", "personId", person, "id", "", "CASCADE");
TestHelper.ExecSql(person.ScriptCreate(), "");
TestHelper.ExecSql(address.ScriptCreate(), "");
TestHelper.ExecSql(fk.ScriptCreate(), "");
TestHelper.ExecSql("drop table Address", "");
TestHelper.ExecSql("drop table Person", "");
}
public ForeignKey(Table table, string name, string columns, Table refTable, string refColumns, string onUpdate,
string onDelete)
{
Table = table;
Name = name;
Columns = new List<string>(columns.Split(','));
RefTable = refTable;
RefColumns = new List<string>(refColumns.Split(','));
OnUpdate = onUpdate;
OnDelete = onDelete;
}
public ForeignKey(Table table, string name, string columns, Table refTable, string refColumns, string onUpdate,
string onDelete)
{
Table = new TableInfo(table.Owner, table.Name);
Name = name;
Columns = new List<string>(columns.Split(','));
RefTable = new TableInfo(refTable.Owner, refTable.Name);
RefColumns = new List<string>(refColumns.Split(','));
OnUpdate = onUpdate;
OnDelete = onDelete;
}
public void CanIgnoreTable()
{
var db = new Database();
var table = new Table("dbo", "IgnoredTableWithConstraints");
var constraint = new Constraint("TestConstraint", "", "");
table.Constraints.Add(constraint);
db.DataTables.Add(table);
db.Ignore(new[] { "IgnoredTableWithConstraints" });
db.Tables.Any().Should().BeFalse();
}
public void CanIgnoreForeignKeysOfIgnoredTable()
{
var db = new Database();
var table = new Table("dbo", "IgnoredTableWithForeignKey");
var refTable = new Table("dbo", "RefTable");
db.DataTables.Add(table);
db.ForeignKeys.Add(new ForeignKey(table,"","", refTable, ""));
db.Ignore(new[] { "IgnoredTableWithForeignKey" });
db.Tables.Any().Should().BeFalse();
db.ForeignKeys.Any().Should().BeFalse();
}
public TableDiff Compare(Table t)
{
var diff = new TableDiff();
diff.Owner = t.Owner;
diff.Name = t.Name;
//get additions and compare mutual columns
foreach (Column c in Columns.Items) {
Column c2 = t.Columns.Find(c.Name);
if (c2 == null) {
diff.ColumnsAdded.Add(c);
} else {
//compare mutual columns
ColumnDiff cDiff = c.Compare(c2);
if (cDiff.IsDiff) {
diff.ColumnsDiff.Add(cDiff);
}
}
}
//get deletions
foreach (Column c in t.Columns.Items.Where(c => Columns.Find(c.Name) == null)) {
diff.ColumnsDropped.Add(c);
}
//get added and compare mutual constraints
foreach (Constraint c in Constraints) {
Constraint c2 = t.FindConstraint(c.Name);
if (c2 == null) {
diff.ConstraintsAdded.Add(c);
} else {
if (c.Script() != c2.Script()) {
diff.ConstraintsChanged.Add(c);
}
}
}
//get deleted constraints
foreach (Constraint c in t.Constraints.Where(c => FindConstraint(c.Name) == null)) {
diff.ConstraintsDeleted.Add(c);
}
return diff;
}
public void TestCompare()
{
var t1 = new Table("dbo", "Test");
var t2 = new Table("dbo", "Test");
TableDiff diff = default(TableDiff);
//test equal
t1.Columns.Add(new Column("first", "varchar", 30, false, null));
t2.Columns.Add(new Column("first", "varchar", 30, false, null));
t1.Constraints.Add(new Constraint("PK_Test", "PRIMARY KEY", "first"));
t2.Constraints.Add(new Constraint("PK_Test", "PRIMARY KEY", "first"));
diff = t1.Compare(t2, new CompareConfig());
Assert.IsNotNull(diff);
Assert.IsFalse(diff.IsDiff);
//test add
t1.Columns.Add(new Column("second", "varchar", 30, false, null));
diff = t1.Compare(t2, new CompareConfig());
Assert.IsTrue(diff.IsDiff);
Assert.AreEqual(1, diff.ColumnsAdded.Count);
//test delete
diff = t2.Compare(t1, new CompareConfig());
Assert.IsTrue(diff.IsDiff);
Assert.AreEqual(1, diff.ColumnsDroped.Count);
//test diff
t1.Columns[0].Length = 20;
diff = t1.Compare(t2, new CompareConfig());
Assert.IsTrue(diff.IsDiff);
Assert.AreEqual(1, diff.ColumnsDiff.Count);
Console.WriteLine("--- create ----");
Console.Write(t1.ScriptCreate());
Console.WriteLine("--- migrate up ---");
Console.Write(t1.Compare(t2, new CompareConfig()).Script());
Console.WriteLine("--- migrate down ---");
Console.Write(t2.Compare(t1, new CompareConfig()).Script());
}
public void TestScript()
{
var t = new Table("dbo", "Address");
t.Columns.Add(new Column("id", "int", false, null));
t.Columns.Add(new Column("street", "varchar", 50, false, null));
t.Columns.Add(new Column("city", "varchar", 50, false, null));
t.Columns.Add(new Column("state", "char", 2, false, null));
t.Columns.Add(new Column("zip", "char", 5, false, null));
t.Constraints.Add(new Constraint("PK_Address", "PRIMARY KEY", "id"));
var getAddress = new Routine("dbo", "GetAddress");
getAddress.Text = @"
CREATE PROCEDURE [dbo].[GetAddress]
@id int
AS
select * from Address where id = @id
";
TestHelper.ExecSql(t.ScriptCreate(), "");
TestHelper.ExecBatchSql(getAddress.ScriptCreate(null) + "\nGO", "");
TestHelper.ExecSql("drop table [dbo].[Address]", "");
TestHelper.ExecSql("drop procedure [dbo].[GetAddress]", "");
}
private static string MakeFileName(Table t)
{
return MakeFileName(t.Owner, t.Name);
}
public void TestScriptNonSupportedColumn()
{
var t = new Table("dbo", "bla");
t.Columns.Add(new Column("a", "madeuptype", true, null));
t.ScriptCreate();
}
public TableDiff Compare(Table otherTable, CompareConfig compareConfig)
{
var diff = new TableDiff();
diff.Owner = otherTable.Owner;
diff.Name = otherTable.Name;
CompareColumns(otherTable, compareConfig, diff);
CompareConstraints(otherTable, compareConfig, diff);
return diff;
}
public ForeignKey(Table table, string name, string columns, Table refTable, string refColumns)
: this(table, name, columns, refTable, refColumns, "", "")
{
}
public void TestScript()
{
var db = new Database("TEST_TEMP");
var t1 = new Table("dbo", "t1");
t1.Columns.Add(new Column("col1", "int", false, null));
t1.Columns.Add(new Column("col2", "int", false, null));
t1.Constraints.Add(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));
t2.Columns.Add(new Column("col2", "int", false, null));
t2.Columns.Add(new Column("col3", "int", false, null));
t2.Constraints.Add(new Constraint("pk_t2", "PRIMARY KEY", "col1"));
t2.FindConstraint("pk_t2").Clustered = true;
t2.Constraints.Add(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 (Table 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 TestScriptToDir()
{
var policy = new Table("dbo", "Policy");
policy.Columns.Add(new Column("id", "int", false, null));
policy.Columns.Add(new Column("form", "tinyint", false, null));
policy.Constraints.Add(new Constraint("PK_Policy", "PRIMARY KEY", "id"));
policy.Constraints[0].Clustered = true;
policy.Constraints[0].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));
loc.Columns.Add(new Column("policyId", "int", false, null));
loc.Columns.Add(new Column("storage", "bit", false, null));
loc.Constraints.Add(new Constraint("PK_Location", "PRIMARY KEY", "id"));
loc.Constraints[0].Clustered = true;
loc.Constraints[0].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));
formType.Columns.Add(new Column("desc", "varchar", 10, false, null));
formType.Constraints.Add(new Constraint("PK_FormType", "PRIMARY KEY", "code"));
formType.Constraints[0].Clustered = 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 db = new Database("ScriptToDirTest");
db.Tables.Add(policy);
db.Tables.Add(formType);
db.Tables.Add(loc);
db.ForeignKeys.Add(fk_policy_formType);
db.ForeignKeys.Add(fk_location_policy);
db.FindProp("COMPATIBILITY_LEVEL").Value = "120";
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;
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 (Table t in db.DataTables) {
Assert.IsTrue(File.Exists(db.Name + "\\data\\" + t.Name + ".tsv"));
}
foreach (Table t in db.Tables) {
Assert.IsTrue(File.Exists(db.Name + "\\tables\\" + t.Name + ".sql"));
}
foreach (string 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);
}
private void CompareConstraints(Table otherTable, CompareConfig compareConfig, TableDiff diff)
{
Action<Constraint, Constraint> checkIfConstraintChanged = (c, c2) => {
if(!c.HasSameProperties(c2, compareConfig)) {
diff.ConstraintsChanged.Add(c);
};
};
Func<Constraint, Constraint> getOtherConstraint = (c) => {
var c2 = otherTable.FindConstraint(c.Name);
if(compareConfig.IgnoreConstraintsNameMismatch && c2 == null)
return otherTable.FindSimilarConstraint(c);
return c2;
};
CheckSource(compareConfig.ConstraintsCompareMethod,
Constraints,
getOtherConstraint,
c => diff.ConstraintsAdded.Add(c),
checkIfConstraintChanged);
Func<Constraint, bool> constraintExistsOnlyInTaget = (c) => {
var c2 = FindConstraint(c.Name);
if (compareConfig.IgnoreConstraintsNameMismatch && c2 == null)
c2 = FindSimilarConstraint(c);
return c2 == null;
};
CheckTarget(compareConfig.ConstraintsCompareMethod,
otherTable.Constraints,
constraintExistsOnlyInTaget,
c => diff.ConstraintsDeleted.Add(c));
}
public void TestScript()
{
//create a table with all known types, script it, and execute the script
var t = new Table("dbo", "AllTypesTest");
t.Columns.Add(new Column("a", "bigint", false, null));
t.Columns.Add(new Column("b", "binary", 50, false, null));
t.Columns.Add(new Column("c", "bit", false, null));
t.Columns.Add(new Column("d", "char", 10, false, null));
t.Columns.Add(new Column("e", "datetime", false, null));
t.Columns.Add(new Column("f", "decimal", 18, 0, false, null));
t.Columns.Add(new Column("g", "float", false, null));
t.Columns.Add(new Column("h", "image", false, null));
t.Columns.Add(new Column("i", "int", false, null));
t.Columns.Add(new Column("j", "money", false, null));
t.Columns.Add(new Column("k", "nchar", 10, false, null));
t.Columns.Add(new Column("l", "ntext", false, null));
t.Columns.Add(new Column("m", "numeric", 18, 0, false, null));
t.Columns.Add(new Column("n", "nvarchar", 50, false, null));
t.Columns.Add(new Column("o", "nvarchar", -1, false, null));
t.Columns.Add(new Column("p", "real", false, null));
t.Columns.Add(new Column("q", "smalldatetime", false, null));
t.Columns.Add(new Column("r", "smallint", false, null));
t.Columns.Add(new Column("s", "smallmoney", false, null));
t.Columns.Add(new Column("t", "sql_variant", false, null));
t.Columns.Add(new Column("u", "text", false, null));
t.Columns.Add(new Column("v", "timestamp", false, null));
t.Columns.Add(new Column("w", "tinyint", false, null));
t.Columns.Add(new Column("x", "uniqueidentifier", false, null));
t.Columns.Add(new Column("y", "varbinary", 50, false, null));
t.Columns.Add(new Column("z", "varbinary", -1, false, null));
t.Columns.Add(new Column("aa", "varchar", 50, true, new Default("DF_AllTypesTest_aa", "'asdf'")));
t.Columns.Add(new Column("bb", "varchar", -1, true, null));
t.Columns.Add(new Column("cc", "xml", true, null));
Console.WriteLine(t.ScriptCreate());
TestHelper.ExecSql(t.ScriptCreate(), "");
TestHelper.ExecSql("drop table [dbo].[AllTypesTest]", "");
}
private void CompareColumns(Table otherTable, CompareConfig compareConfig, TableDiff diff)
{
Action<Column, Column> checkIfColumnChanged = (c, c2) => {
//compare mutual columns
ColumnDiff cDiff = c.Compare(c2, compareConfig);
if (cDiff.IsDiff) {
diff.ColumnsDiff.Add(cDiff);
}
};
CheckSource(compareConfig.ColumnsCompareMethod,
Columns,
c => otherTable.Columns.Find(c.Name),
c => diff.ColumnsAdded.Add(c),
checkIfColumnChanged);
CheckTarget(compareConfig.RoutinesCompareMethod,
otherTable.Columns,
c => Columns.Find(c.Name) == null,
c => diff.ColumnsDroped.Add(c));
}