/// <summary> /// Sets up a CHI/ECHI mapping table with fallback guid and populates each table with a single record. /// 0101010101 is a known CHI and 0202020202 is an known one (which was assigned a temporary guid mapping). /// Also prepares the main map table for DLE loading (<see cref="TriggerImplementer"/>) /// </summary> /// <param name="dbType"></param> /// <param name="map"></param> /// <param name="guidTable"></param> /// <param name="mapperOptions"></param> /// <param name="guids">true to create a <see cref="TableLookupWithGuidFallbackSwapper"/> otherwise creates a <see cref="TableLookupSwapper"/></param> private void SetupMappers(DatabaseType dbType, out DiscoveredTable map, out DiscoveredTable guidTable, out IdentifierMapperOptions mapperOptions, bool guids = true) { var db = GetCleanedServer(dbType); using (var dt = new DataTable()) { dt.Columns.Add("CHI"); dt.Columns.Add("ECHI"); dt.PrimaryKey = new [] { dt.Columns["CHI"] }; dt.Rows.Add("0101010101", "0A0A0A0A0A"); map = db.CreateTable("Map", dt); } mapperOptions = new IdentifierMapperOptions() { MappingTableName = map.GetFullyQualifiedName(), MappingConnectionString = db.Server.Builder.ConnectionString, SwapColumnName = "CHI", ReplacementColumnName = "ECHI", MappingDatabaseType = db.Server.DatabaseType, SwapperType = (guids ? typeof(TableLookupWithGuidFallbackSwapper):typeof(TableLookupSwapper)).FullName }; if (guids) { var swapper = new TableLookupWithGuidFallbackSwapper(); swapper.Setup(mapperOptions); guidTable = swapper.GetGuidTableIfAny(mapperOptions); Assert.AreEqual(0, guidTable.GetRowCount(), "No temporary guids should exist yet"); Assert.AreEqual(1, map.GetRowCount(), "We should have a mapping table with 1 entry"); //lookup an as yet unknown value swapper.GetSubstitutionFor("0202020202", out _); Assert.AreEqual(1, map.GetRowCount(), "We should have a mapping table with 1 entry"); Assert.AreEqual(1, guidTable.GetRowCount(), "We should have a temporary guid for 0202020202"); } else { guidTable = null; } // make a fake data load into this table (create trigger and insert/update) var triggerImplementer = new TriggerImplementerFactory(dbType).Create(map); triggerImplementer.CreateTrigger(new ThrowImmediatelyCheckNotifier()); }
public void TestBulkInsert_ExplicitDateTimeFormats(DatabaseType type) { DiscoveredDatabase db = GetTestDatabase(type); DiscoveredTable tbl = db.CreateTable("MyDateTestTable", new[] { new DatabaseColumnRequest("MyDate", new DatabaseTypeRequest(typeof(DateTime))) { AllowNulls = false }, }); //There are no rows in the table yet Assert.AreEqual(0, tbl.GetRowCount()); using (var dt = new DataTable()) { dt.Columns.Add("MyDate"); dt.Rows.Add("20011230"); using (IBulkCopy bulk = tbl.BeginBulkInsert()) { bulk.Timeout = 30; bulk.DateTimeDecider.Settings.ExplicitDateFormats = new [] { "yyyyMMdd" }; bulk.Upload(dt); } } var dtDown = tbl.GetDataTable(); Assert.AreEqual(new DateTime(2001, 12, 30), dtDown.Rows[0]["MyDate"]); }
public void TestBulkInsert_Transaction(DatabaseType type) { DiscoveredDatabase db = GetTestDatabase(type); DiscoveredTable tbl = db.CreateTable("MyBulkInsertTest", new[] { new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 10)), new DatabaseColumnRequest("Age", new DatabaseTypeRequest(typeof(int))) }); Assert.AreEqual(0, tbl.GetRowCount()); using (var dt = new DataTable()) { dt.Columns.Add("Name"); dt.Columns.Add("Age"); dt.Rows.Add("Dave", 50); dt.Rows.Add("Jamie", 60); using (var transaction = tbl.Database.Server.BeginNewTransactedConnection()) { using (IBulkCopy bulk = tbl.BeginBulkInsert(transaction.ManagedTransaction)) { bulk.Timeout = 30; bulk.Upload(dt); //inside transaction the count is 2 Assert.AreEqual(2, tbl.GetRowCount(transaction.ManagedTransaction)); dt.Rows.Clear(); dt.Rows.Add("Frank", 100); bulk.Upload(dt); //inside transaction the count is 3 Assert.AreEqual(3, tbl.GetRowCount(transaction.ManagedTransaction)); } transaction.ManagedTransaction.CommitAndCloseConnection(); } } //Transaction was committed final row count should be 3 Assert.AreEqual(3, tbl.GetRowCount()); }
public void TestBulkInsert_SpacedOutNames(DatabaseType type) { DiscoveredDatabase db = GetTestDatabase(type); DiscoveredTable tbl = db.CreateTable("MyBulkInsertTest", new[] { new DatabaseColumnRequest("Na me", new DatabaseTypeRequest(typeof(string), 10)), new DatabaseColumnRequest("A ge", new DatabaseTypeRequest(typeof(int))) }); //There are no rows in the table yet Assert.AreEqual(0, tbl.GetRowCount()); using (var dt = new DataTable()) { dt.Columns.Add("Na me"); dt.Columns.Add("A ge"); dt.Rows.Add("Dave", 50); dt.Rows.Add("Jamie", 60); using (IBulkCopy bulk = tbl.BeginBulkInsert()) { bulk.Timeout = 30; bulk.Upload(dt); Assert.AreEqual(2, tbl.GetRowCount()); dt.Rows.Clear(); dt.Rows.Add("Frank", 100); bulk.Upload(dt); Assert.AreEqual(3, tbl.GetRowCount()); } } tbl.Insert(new Dictionary <string, object>() { { "Na me", "George" }, { "A ge", "300" } }); Assert.AreEqual(4, tbl.GetRowCount()); }
public void TestBulkInsert_BadDecimalFormat_DecimalError(DatabaseType type) { DiscoveredDatabase db = GetTestDatabase(type); DiscoveredTable tbl = db.CreateTable("MyBulkInsertTest", new[] { new DatabaseColumnRequest("Id", new DatabaseTypeRequest(typeof(int))) { IsAutoIncrement = true, IsPrimaryKey = true }, new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 10)), new DatabaseColumnRequest("Score", new DatabaseTypeRequest(typeof(decimal), null, new DecimalSize(2, 1))), new DatabaseColumnRequest("Age", new DatabaseTypeRequest(typeof(int))) }); //There are no rows in the table yet Assert.AreEqual(0, tbl.GetRowCount()); using (var dt = new DataTable()) { dt.Columns.Add("age"); dt.Columns.Add("name"); dt.Columns.Add("score"); dt.Rows.Add(60, "Jamie", 1.2); dt.Rows.Add(30, "Frank", 1.3); dt.Rows.Add(11, "Toad", "."); //bad data dt.Rows.Add(100, "King"); dt.Rows.Add(10, "Frog"); using (IBulkCopy bulk = tbl.BeginBulkInsert()) { bulk.Timeout = 30; Exception ex = null; try { bulk.Upload(dt); } catch (Exception e) { ex = e; } Assert.IsNotNull(ex, "Expected upload to fail because value on row 2 is bad"); Assert.AreEqual("Failed to parse value '.' in column 'score'", ex.Message); Assert.IsNotNull(ex.InnerException, "Expected parse error to be an inner exception"); StringAssert.Contains("Could not parse string value '.' with Decider Type:DecimalTypeDecider", ex.InnerException.Message); } } }
public void TestBulkInsert_ColumnOrdinals(DatabaseType type) { DiscoveredDatabase db = GetTestDatabase(type); DiscoveredTable tbl = db.CreateTable("MyBulkInsertTest", new[] { new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 10)), new DatabaseColumnRequest("Age", new DatabaseTypeRequest(typeof(int))) }); //There are no rows in the table yet Assert.AreEqual(0, tbl.GetRowCount()); using (var dt = new DataTable()) { dt.Columns.Add("Age"); dt.Columns.Add("Name"); dt.Rows.Add("50", "David"); dt.Rows.Add("60", "Jamie"); Assert.AreEqual("Age", dt.Columns[0].ColumnName); Assert.AreEqual(typeof(string), dt.Columns[0].DataType); using (IBulkCopy bulk = tbl.BeginBulkInsert()) { bulk.Timeout = 30; bulk.Upload(dt); Assert.AreEqual(2, tbl.GetRowCount()); } //columns should not be reordered Assert.AreEqual("Age", dt.Columns[0].ColumnName); Assert.AreEqual(typeof(int), dt.Columns[0].DataType); //but the data type was changed by HardTyping it } }
public void DiffDatabaseDataFetcherTest(DatabaseType dbType) { CreateTable(dbType); _table.CreatePrimaryKey(_table.DiscoverColumn("name")); GetImplementer().CreateTrigger(new ThrowImmediatelyCheckNotifier()); _table.Insert(new Dictionary <string, object> { { "name", "Franky" }, { "bubbles", 3 }, { "hic_validFrom", new DateTime(2001, 1, 2) }, { "hic_dataLoadRunID", 7 } }); Thread.Sleep(500); RunSQL("UPDATE {0} SET bubbles=1", _table.GetFullyQualifiedName()); Thread.Sleep(500); RunSQL("UPDATE {0} SET bubbles=2", _table.GetFullyQualifiedName()); Thread.Sleep(500); RunSQL("UPDATE {0} SET bubbles=3", _table.GetFullyQualifiedName()); Thread.Sleep(500); RunSQL("UPDATE {0} SET bubbles=4", _table.GetFullyQualifiedName()); Assert.AreEqual(1, _table.GetRowCount()); Assert.AreEqual(4, _archiveTable.GetRowCount()); TableInfo ti; ColumnInfo[] cols; Import(_table, out ti, out cols); DiffDatabaseDataFetcher fetcher = new DiffDatabaseDataFetcher(1, ti, 7, 100); fetcher.FetchData(new AcceptAllCheckNotifier()); Assert.AreEqual(4, fetcher.Updates_New.Rows[0]["bubbles"]); Assert.AreEqual(3, fetcher.Updates_Replaced.Rows[0]["bubbles"]); Assert.AreEqual(1, fetcher.Updates_New.Rows.Count); Assert.AreEqual(1, fetcher.Updates_Replaced.Rows.Count); }
public void TestBulkInsert_SchemaTooNarrow_DecimalError(DatabaseType type) { DiscoveredDatabase db = GetTestDatabase(type); DiscoveredTable tbl = db.CreateTable("MyBulkInsertTest", new[] { new DatabaseColumnRequest("Id", new DatabaseTypeRequest(typeof(int))) { IsAutoIncrement = true, IsPrimaryKey = true }, new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 10)), new DatabaseColumnRequest("Score", new DatabaseTypeRequest(typeof(decimal), null, new DecimalSize(2, 1))), new DatabaseColumnRequest("Age", new DatabaseTypeRequest(typeof(int))) }); //There are no rows in the table yet Assert.AreEqual(0, tbl.GetRowCount()); using (var dt = new DataTable()) { dt.Columns.Add("age"); dt.Columns.Add("name"); dt.Columns.Add("score"); dt.Rows.Add(60, "Jamie", 1.2); dt.Rows.Add(30, "Frank", 1.3); dt.Rows.Add(11, "Toad", 111111111.11); //bad data dt.Rows.Add(100, "King"); dt.Rows.Add(10, "Frog"); using (IBulkCopy bulk = tbl.BeginBulkInsert()) { bulk.Timeout = 30; Exception ex = null; try { bulk.Upload(dt); } catch (Exception e) { ex = e; } Assert.IsNotNull(ex, "Expected upload to fail because value on row 2 is too long"); switch (type) { case DatabaseType.MicrosoftSQLServer: StringAssert.Contains("Failed to load data row 3 the following values were rejected by the database", ex.Message); StringAssert.Contains("Parameter value '111111111.1' is out of range", ex.Message); break; case DatabaseType.MySql: Assert.AreEqual("Out of range value for column 'Score' at row 3", ex.Message); break; case DatabaseType.Oracle: StringAssert.Contains("value larger than specified precision allowed for this column", ex.Message); break; case DatabaseType.PostgreSql: StringAssert.Contains("numeric field overflow", ex.Message); break; default: throw new ArgumentOutOfRangeException(nameof(type), type, null); } } } }
public void TestBulkInsert_SchemaTooNarrow_StringError(DatabaseType type) { DiscoveredDatabase db = GetTestDatabase(type); DiscoveredTable tbl = db.CreateTable("MyBulkInsertTest", new[] { new DatabaseColumnRequest("Id", new DatabaseTypeRequest(typeof(int))) { IsAutoIncrement = true, IsPrimaryKey = true }, new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 10)), new DatabaseColumnRequest("Age", new DatabaseTypeRequest(typeof(int))) }); //There are no rows in the table yet Assert.AreEqual(0, tbl.GetRowCount()); using (var dt = new DataTable()) { dt.Columns.Add("age"); dt.Columns.Add("name"); dt.Rows.Add(60, "Jamie"); dt.Rows.Add(30, "Frank"); dt.Rows.Add(11, "Toad"); dt.Rows.Add(50, new string('A', 11)); dt.Rows.Add(100, "King"); dt.Rows.Add(10, "Frog"); using (IBulkCopy bulk = tbl.BeginBulkInsert()) { bulk.Timeout = 30; Exception ex = null; try { bulk.Upload(dt); } catch (Exception e) { ex = e; } Assert.IsNotNull(ex, "Expected upload to fail because value on row 2 is too long"); switch (type) { case DatabaseType.MicrosoftSQLServer: StringAssert.Contains("BulkInsert failed on data row 4 the complaint was about source column <<name>> which had value <<AAAAAAAAAAA>> destination data type was <<varchar(10)>>", ex.Message); break; case DatabaseType.MySql: Assert.AreEqual("Data too long for column 'Name' at row 4", ex.Message); break; case DatabaseType.Oracle: StringAssert.Contains("NAME", ex.Message); StringAssert.Contains("maximum: 10", ex.Message); StringAssert.Contains("actual: 11", ex.Message); break; case DatabaseType.PostgreSql: StringAssert.Contains("value too long for type character varying(10)", ex.Message); break; default: throw new ArgumentOutOfRangeException(nameof(type), type, null); } } } }