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