public void NowTestDataInsertion(DatabaseType dbType)
{
AlterTest_InvalidThenRecreateItAndItsValidAgain(dbType);
_table.Insert(new Dictionary <string, object>
{
{ "name", "Franky" },
{ "bubbles", 3 },
{ "hic_validFrom", new DateTime(2001, 1, 2) },
{ "hic_dataLoadRunID", 7 }
});
RunSQL("UPDATE {0} set bubbles =99", _table.GetFullyQualifiedName());
//new value is 99
Assert.AreEqual(99, ExecuteScalar("Select bubbles FROM {0} where name = 'Franky'", _table.GetFullyQualifiedName()));
//archived value is 3
Assert.AreEqual(3, ExecuteScalar("Select bubbles FROM {0} where name = 'Franky'", _archiveTable.GetFullyQualifiedName()));
//Legacy table valued function only works for MicrosoftSQLServer
if (dbType == DatabaseType.MicrosoftSQLServer)
{
//legacy in 2001-01-01 it didn't exist
Assert.IsNull(ExecuteScalar("Select bubbles FROM TriggerTests_Legacy('2001-01-01') where name = 'Franky'"));
//legacy in 2001-01-03 it did exist and was 3
Assert.AreEqual(3, ExecuteScalar("Select bubbles FROM TriggerTests_Legacy('2001-01-03') where name = 'Franky'"));
//legacy boundary case?
Assert.AreEqual(3, ExecuteScalar("Select bubbles FROM TriggerTests_Legacy('2001-01-02') where name = 'Franky'"));
//legacy today it is 99
Assert.AreEqual(99, ExecuteScalar("Select bubbles FROM TriggerTests_Legacy(GETDATE()) where name = 'Franky'"));
}
}
public void NowTestDataInsertion(DatabaseType dbType)
{
AlterTest_InvalidThenRecreateItAndItsValidAgain(dbType);
_table.Insert(new Dictionary <string, object>
{
{ "name", "Franky" },
{ "bubbles", 3 },
{ "hic_validFrom", new DateTime(2001, 1, 2) },
{ "hic_dataLoadRunID", 7 }
});
var liveOldRow = _table.GetDataTable().Rows.Cast <DataRow>().Single(r => r["bubbles"] as int? == 3);
Assert.AreEqual(new DateTime(2001, 1, 2), ((DateTime)liveOldRow[SpecialFieldNames.ValidFrom]));
RunSQL("UPDATE {0} set bubbles =99", _table.GetFullyQualifiedName());
//new value is 99
Assert.AreEqual(99, ExecuteScalar("Select bubbles FROM {0} where name = 'Franky'", _table.GetFullyQualifiedName()));
//archived value is 3
Assert.AreEqual(3, ExecuteScalar("Select bubbles FROM {0} where name = 'Franky'", _archiveTable.GetFullyQualifiedName()));
//Legacy table valued function only works for MicrosoftSQLServer
if (dbType == DatabaseType.MicrosoftSQLServer)
{
//legacy in 2001-01-01 it didn't exist
Assert.IsNull(ExecuteScalar("Select bubbles FROM TriggerTests_Legacy('2001-01-01') where name = 'Franky'"));
//legacy in 2001-01-03 it did exist and was 3
Assert.AreEqual(3, ExecuteScalar("Select bubbles FROM TriggerTests_Legacy('2001-01-03') where name = 'Franky'"));
//legacy boundary case?
Assert.AreEqual(3, ExecuteScalar("Select bubbles FROM TriggerTests_Legacy('2001-01-02') where name = 'Franky'"));
//legacy today it is 99
Assert.AreEqual(99, ExecuteScalar("Select bubbles FROM TriggerTests_Legacy(GETDATE()) where name = 'Franky'"));
}
// Live row should now reflect that it is validFrom today
var liveNewRow = _table.GetDataTable().Rows.Cast <DataRow>().Single(r => r["bubbles"] as int? == 99);
Assert.AreEqual(DateTime.Now.Date, ((DateTime)liveNewRow[SpecialFieldNames.ValidFrom]).Date);
// Archived row should not have had it's validFrom field broken
var archivedRow = _archiveTable.GetDataTable().Rows.Cast <DataRow>().Single(r => r["bubbles"] as int? == 3);
Assert.AreEqual(new DateTime(2001, 1, 2), ((DateTime)archivedRow[SpecialFieldNames.ValidFrom]));
}
public void Test_ColumnInfoValuesRejectorTests(DatabaseType type)
{
DiscoveredDatabase server = GetCleanedServer(type);
DiscoveredTable tbl = server.CreateTable("BadPatients", new[] { new DatabaseColumnRequest(PatColName, "varchar(100)") });
tbl.Insert(new Dictionary <string, object> {
{ PatColName, "Frank" }
});
tbl.Insert(new Dictionary <string, object> {
{ PatColName, "Peter" }
});
tbl.Insert(new Dictionary <string, object> {
{ PatColName, "Frank" }
}); //duplication for the lols
tbl.Insert(new Dictionary <string, object> {
{ PatColName, "David" }
});
new TableInfoImporter(CatalogueRepository, tbl).DoImport(out var _, out ColumnInfo[] cols);
var rejector = new ColumnInfoValuesRejector(cols[0]);
var moqDave = new Mock <DbDataReader>();
moqDave.Setup(x => x[PatColName])
.Returns("Dave");
Assert.IsFalse(rejector.Reject(moqDave.Object, out string reason));
Assert.IsNull(reason);
var moqFrank = new Mock <DbDataReader>();
moqFrank.Setup(x => x[PatColName])
.Returns("Frank");
Assert.IsTrue(rejector.Reject(moqFrank.Object, out reason));
Assert.AreEqual("Patient was in reject list", reason);
var moqLowerCaseFrank = new Mock <DbDataReader>();
moqLowerCaseFrank.Setup(x => x[PatColName])
.Returns("frank");
Assert.IsTrue(rejector.Reject(moqLowerCaseFrank.Object, out reason));
Assert.AreEqual("Patient was in reject list", reason);
}
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());
}
