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); var sw = new StringWriter(); t.ExportData(conn, sw); Assert.AreEqual(dataIn, sw.ToString()); }
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.AddConstraint(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.AddConstraint(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 void TestCreateTableParser() { var t = new Table("dbo", "Status"); t.Columns.Add(new Column("id", "int", false, null){Position = 1}); t.Columns.Add(new Column("code", "char", 1, false, null){Position = 2}); t.Columns.Add(new Column("description", "varchar", 20, false, null){Position = 3}); t.Columns.Find("id").Identity = new Identity(1, 1); t.Constraints.Add(new Constraint("PK_Status", "PRIMARY KEY", "id")); var db = new Database(); var script = t.ScriptCreate(); db.ParseSql(script); var t2 = db.FindTable("Status", "dbo"); var diff = t2.Compare(t); Assert.IsFalse(diff.IsDiff); }
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); Assert.IsNotNull(diff); Assert.IsFalse(diff.IsDiff); //test add t1.Columns.Add(new Column("second", "varchar", 30, false, null)); diff = t1.Compare(t2); Assert.IsTrue(diff.IsDiff); Assert.AreEqual(1, diff.ColumnsAdded.Count); //test delete diff = t2.Compare(t1); Assert.IsTrue(diff.IsDiff); Assert.AreEqual(1, diff.ColumnsDropped.Count); //test diff t1.Columns.Items[0].Length = 20; diff = t1.Compare(t2); 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).Script()); Console.WriteLine("--- migrate down ---"); Console.Write(t2.Compare(t1).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", null); getAddress.Text = @" CREATE PROCEDURE [dbo].[GetAddress] @id int AS select * from Address where id = @id "; TestHelper.ExecSql(t.ScriptCreate(), ""); TestHelper.ExecBatchSql(getAddress.ScriptCreate() + "\nGO", ""); TestHelper.ExecSql("drop table [dbo].[Address]", ""); TestHelper.ExecSql("drop procedure [dbo].[GetAddress]", ""); }
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]", ""); }
public void TestScriptNonSupportedColumn() { var t = new Table("dbo", "bla"); t.Columns.Add(new Column("a", "madeuptype", true, null)); t.ScriptCreate(); }
public void TestLargeAmountOfRowsImportAndExport() { var t = new Table("dbo", "TestData"); t.Columns.Add(new Column("test_field", "int", false, null)); t.AddConstraint(new Constraint("PK_TestData", "PRIMARY KEY", "test_field")); t.AddConstraint(new Constraint("IX_TestData_PK", "INDEX", "test_field") { Clustered = true, Table = t, Unique = true }); // clustered index is required to ensure the row order is the same as what we import var conn = TestHelper.GetConnString("TESTDB"); DBHelper.DropDb(conn); DBHelper.CreateDb(conn); SqlConnection.ClearAllPools(); DBHelper.ExecBatchSql(conn, t.ScriptCreate()); var filename = Path.GetTempFileName(); var writer = File.CreateText(filename); StringBuilder sb = new StringBuilder(); for (var i = 0; i < Table.rowsInBatch * 4.2; i++) { sb.AppendLine(i.ToString()); writer.WriteLine(i.ToString()); } writer.Flush(); writer.Close(); var dataIn = sb.ToString(); Assert.AreEqual(dataIn, File.ReadAllText(filename)); // just prove that the file and the string are the same, to make the next assertion meaningful! try { t.ImportData(conn, filename); var sw = new StringWriter(); t.ExportData(conn, sw); Assert.AreEqual(dataIn, sw.ToString()); } finally { File.Delete(filename); } }
public void TestImportAndExportNonDefaultSchema() { var s = new Schema("example", "dbo"); var t = new Table(s.Name, "Example"); 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.AddConstraint(new Constraint("PK_Example", "PRIMARY KEY", "id")); var conn = TestHelper.GetConnString("TESTDB"); DBHelper.DropDb(conn); DBHelper.CreateDb(conn); SqlConnection.ClearAllPools(); DBHelper.ExecBatchSql(conn, s.ScriptCreate()); DBHelper.ExecBatchSql(conn, t.ScriptCreate()); var dataIn = @"1 R Ready 2 P Processing 3 F Frozen "; var filename = Path.GetTempFileName(); var writer = File.AppendText(filename); writer.Write(dataIn); writer.Flush(); writer.Close(); try { t.ImportData(conn, filename); var sw = new StringWriter(); t.ExportData(conn, sw); Assert.AreEqual(dataIn, sw.ToString()); } finally { File.Delete(filename); } }