public void Test_UpdateTableFromJoin(DatabaseType dbType)
{
var dt1 = new DataTable();
dt1.Columns.Add("Name");
dt1.Columns.Add("HighScore");
dt1.Rows.Add("Dave", 100);
dt1.Rows.Add("Frank", DBNull.Value);
dt1.Rows.Add("Levo", DBNull.Value);
var dt2 = new DataTable();
dt2.Columns.Add("Name");
dt2.Columns.Add("Score");
dt2.Rows.Add("Dave", 50);
dt2.Rows.Add("Frank", 900);
DiscoveredDatabase db = GetTestDatabase(dbType);
var tbl1 = db.CreateTable("HighScoresTable", dt1);
var tbl2 = db.CreateTable("NewScoresTable", dt2);
var syntaxHelper = db.Server.GetQuerySyntaxHelper();
var updateHelper = syntaxHelper.UpdateHelper;
List <CustomLine> queryLines = new List <CustomLine>();
var highScore = syntaxHelper.EnsureWrapped("HighScore");
var score = syntaxHelper.EnsureWrapped("Score");
var name = syntaxHelper.EnsureWrapped("Name");
queryLines.Add(new CustomLine($"t1.{highScore} = t2.{score}", QueryComponent.SET));
queryLines.Add(new CustomLine($"t1.{highScore} < t2.{score} OR t1.{highScore} is null", QueryComponent.WHERE));
queryLines.Add(new CustomLine($"t1.{name} = t2.{name}", QueryComponent.JoinInfoJoin));
string sql = updateHelper.BuildUpdate(tbl1, tbl2, queryLines);
Console.WriteLine("UPDATE Sql:" + sql);
using (var con = db.Server.GetConnection())
{
con.Open();
DbCommand cmd = db.Server.GetCommand(sql, con);
int affectedRows = cmd.ExecuteNonQuery();
Assert.AreEqual(1, affectedRows);
//Frank should have got a new high score of 900
cmd = db.Server.GetCommand($"SELECT {highScore} from {tbl1.GetFullyQualifiedName()} WHERE {name} = 'Frank'", con);
Assert.AreEqual(900, cmd.ExecuteScalar());
//Dave should have his old score of 100
cmd = db.Server.GetCommand($"SELECT {highScore} from {tbl1.GetFullyQualifiedName()} WHERE {name} = 'Dave'", con);
Assert.AreEqual(100, cmd.ExecuteScalar());
}
}
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"]);
}
private void SetupCohort(out DiscoveredDatabase db, out CohortIdentificationConfiguration cic, out DataTable dt)
{
dt = new DataTable();
dt.Columns.Add("PK");
//add lots of rows
for (int i = 0; i < 100000; i++)
{
dt.Rows.Add(i);
}
db = GetCleanedServer(DatabaseType.MicrosoftSQLServer, true);
var tbl = db.CreateTable("CohortCompilerRunnerTestsTable", dt);
var cata = Import(tbl);
var ei = cata.CatalogueItems[0].ExtractionInformation;
ei.IsExtractionIdentifier = true;
ei.SaveToDatabase();
var agg = new AggregateConfiguration(CatalogueRepository, cata, "MyAgg");
agg.CountSQL = null;
agg.SaveToDatabase();
var dimension = new AggregateDimension(CatalogueRepository, ei, agg);
cic = new CohortIdentificationConfiguration(CatalogueRepository, "MyCic");
cic.CreateRootContainerIfNotExists();
cic.RootCohortAggregateContainer.AddChild(agg, 0);
}
public void CommitResults(CacheCommitArguments arguments)
{
var configuration = arguments.Configuration;
var operation = arguments.Operation;
DeleteCacheEntryIfAny(configuration, operation);
//Do not change Types of source columns unless there is an explicit override
arguments.Results.SetDoNotReType(true);
using (var con = _server.GetConnection())
{
con.Open();
string nameWeWillGiveTableInCache = operation + "_AggregateConfiguration" + configuration.ID;
//either it has no name or it already has name we want so its ok
arguments.Results.TableName = nameWeWillGiveTableInCache;
//add explicit types
var tbl = _database.ExpectTable(nameWeWillGiveTableInCache);
if (tbl.Exists())
{
tbl.Drop();
}
tbl = _database.CreateTable(nameWeWillGiveTableInCache, arguments.Results, arguments.ExplicitColumns);
if (!tbl.Exists())
{
throw new Exception("Cache table did not exist even after CreateTable completed without error!");
}
var cmdCreateNew =
DatabaseCommandHelper.GetCommand(
"INSERT INTO CachedAggregateConfigurationResults (Committer,AggregateConfiguration_ID,SqlExecuted,Operation,TableName) Values (@Committer,@AggregateConfiguration_ID,@SqlExecuted,@Operation,@TableName)", con);
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@Committer", cmdCreateNew));
cmdCreateNew.Parameters["@Committer"].Value = Environment.UserName;
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@AggregateConfiguration_ID", cmdCreateNew));
cmdCreateNew.Parameters["@AggregateConfiguration_ID"].Value = configuration.ID;
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@SqlExecuted", cmdCreateNew));
cmdCreateNew.Parameters["@SqlExecuted"].Value = arguments.SQL.Trim();
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@Operation", cmdCreateNew));
cmdCreateNew.Parameters["@Operation"].Value = operation.ToString();
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@TableName", cmdCreateNew));
cmdCreateNew.Parameters["@TableName"].Value = tbl.GetRuntimeName();
cmdCreateNew.ExecuteNonQuery();
arguments.CommitTableDataCompleted(tbl);
}
}
protected DiscoveredTable CreateDataset <T>(DiscoveredDatabase db, PersonCollection people, int rows, Random r)
where T : IDataGenerator
{
var f = new DataGeneratorFactory();
T instance = f.Create <T>(r);
var dt = instance.GetDataTable(people, rows);
return(db.CreateTable(typeof(T).Name, dt, null, false, this));
}
public void TestSwapCache()
{
var mappingDataTable = new DataTable("IdMap");
mappingDataTable.Columns.Add("priv");
mappingDataTable.Columns.Add("pub");
mappingDataTable.Rows.Add("CHI-1", "REP-1");
mappingDataTable.Rows.Add("CHI-2", "REP-2");
DiscoveredDatabase db = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
GlobalOptions options = new GlobalOptionsFactory().Load();
options.IdentifierMapperOptions = new IdentifierMapperOptions
{
MappingConnectionString = db.Server.Builder.ConnectionString,
MappingTableName = db.CreateTable("IdMap", mappingDataTable).GetFullyQualifiedName(),
SwapColumnName = "priv",
ReplacementColumnName = "pub",
MappingDatabaseType = DatabaseType.MicrosoftSQLServer,
TimeoutInSeconds = 500
};
var swapper = new TableLookupSwapper();
swapper.Setup(options.IdentifierMapperOptions);
string _;
string swapped = swapper.GetSubstitutionFor("CHI-1", out _);
Assert.AreEqual("REP-1", swapped);
swapped = swapper.GetSubstitutionFor("CHI-1", out _);
Assert.AreEqual("REP-1", swapped);
Assert.AreEqual(2, swapper.Success);
Assert.AreEqual(1, swapper.CacheHit);
swapped = swapper.GetSubstitutionFor("CHI-2", out _);
Assert.AreEqual("REP-2", swapped);
swapped = swapper.GetSubstitutionFor("CHI-2", out _);
Assert.AreEqual("REP-2", swapped);
Assert.AreEqual(4, swapper.Success);
Assert.AreEqual(2, swapper.CacheHit);
// Just to make sure...
swapped = swapper.GetSubstitutionFor("CHI-1", out _);
Assert.AreEqual("REP-1", swapped);
Assert.AreEqual(5, swapper.Success);
Assert.AreEqual(2, swapper.CacheHit);
}
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 CreateTable(DatabaseType dbType)
{
_database = GetCleanedServer(dbType);
_table = _database.CreateTable("TriggerTests", new DatabaseColumnRequest[] {
new DatabaseColumnRequest("name", new DatabaseTypeRequest(typeof(string), 30))
{
AllowNulls = false
},
new DatabaseColumnRequest("bubbles", new DatabaseTypeRequest(typeof(int))),
});
_archiveTable = _database.ExpectTable("TriggerTests_Archive");
}
public void CreateTable_PrimaryKey_FromDataTable(DatabaseType databaseType)
{
DiscoveredDatabase database = GetTestDatabase(databaseType);
var dt = new DataTable();
dt.Columns.Add("Name");
dt.PrimaryKey = new[] { dt.Columns[0] };
dt.Rows.Add("Frank");
DiscoveredTable table = database.CreateTable("PkTable", dt);
Assert.IsTrue(table.DiscoverColumn("Name").IsPrimaryKey);
}
public void Test_OracleBit_IsActuallyString()
{
DiscoveredDatabase db = GetTestDatabase(DatabaseType.Oracle);
DiscoveredTable table = db.CreateTable("MyTable",
new[]
{
new DatabaseColumnRequest("MyCol", new DatabaseTypeRequest(typeof(bool)))
});
var col = table.DiscoverColumn("MyCol");
Assert.AreEqual("varchar2(5)", col.DataType.SQLType);
Assert.AreEqual(5, col.DataType.GetLengthIfString());
}
private void RunAttachStageWithNormalJob(RemoteTableAttacher attacher, DiscoveredDatabase db)
{
//the table to get data from
attacher.RemoteTableName = "table1";
attacher.RAWTableName = "table2";
attacher.Check(new ThrowImmediatelyCheckNotifier());
attacher.Initialize(null, db);
using (var dt = new DataTable())
{
dt.Columns.Add("Col1");
dt.Rows.Add("fff");
var tbl1 = db.CreateTable("table1", dt);
var tbl2 = db.CreateTable("table2", new [] { new DatabaseColumnRequest("Col1", new DatabaseTypeRequest(typeof(string), 5)) });
Assert.AreEqual(1, tbl1.GetRowCount());
Assert.AreEqual(0, tbl2.GetRowCount());
var logManager = new LogManager(new DiscoveredServer(UnitTestLoggingConnectionString));
var lmd = RdmpMockFactory.Mock_LoadMetadataLoadingTable(tbl2);
Mock.Get(lmd).Setup(p => p.CatalogueRepository).Returns(CatalogueRepository);
logManager.CreateNewLoggingTaskIfNotExists(lmd.GetDistinctLoggingTask());
var dbConfiguration = new HICDatabaseConfiguration(lmd, RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(db, "table2"));
var job = new DataLoadJob(RepositoryLocator, "test job", logManager, lmd, new TestLoadDirectory(), new ThrowImmediatelyDataLoadEventListener(), dbConfiguration);
job.StartLogging();
attacher.Attach(job, new GracefulCancellationToken());
Assert.AreEqual(1, tbl1.GetRowCount());
Assert.AreEqual(1, tbl2.GetRowCount());
}
}
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 CommitResults(CacheCommitArguments arguments)
{
var configuration = arguments.Configuration;
var operation = arguments.Operation;
DeleteCacheEntryIfAny(configuration, operation);
//Do not change Types of source columns unless there is an explicit override
arguments.Results.SetDoNotReType(true);
using (var con = _server.GetConnection())
{
con.Open();
string nameWeWillGiveTableInCache = operation + "_AggregateConfiguration" + configuration.ID;
//either it has no name or it already has name we want so its ok
arguments.Results.TableName = nameWeWillGiveTableInCache;
//add explicit types
var tbl = _database.ExpectTable(nameWeWillGiveTableInCache);
if (tbl.Exists())
{
tbl.Drop();
}
tbl = _database.CreateTable(nameWeWillGiveTableInCache, arguments.Results, arguments.ExplicitColumns);
if (!tbl.Exists())
{
throw new Exception("Cache table did not exist even after CreateTable completed without error!");
}
var mgrTable = _database.ExpectTable(ResultsManagerTable);
mgrTable.Insert(new Dictionary <string, object>()
{
{ "Committer", Environment.UserName },
{ "AggregateConfiguration_ID", configuration.ID },
{ "SqlExecuted", arguments.SQL.Trim() },
{ "Operation", operation.ToString() },
{ "TableName", tbl.GetRuntimeName() },
});
arguments.CommitTableDataCompleted(tbl);
}
}
private void CreateBadView(DiscoveredDatabase db)
{
//drop it if it exists
DropBadView(db, true);
db.CreateTable("ABC", new DatabaseColumnRequest[] { new DatabaseColumnRequest("A", new DatabaseTypeRequest(typeof(int))) });
using (var con = db.Server.GetConnection())
{
con.Open();
var viewname = db.Server.GetQuerySyntaxHelper().EnsureWrapped("ABC");
var cmd = db.Server.GetCommand($"CREATE VIEW {GetBadTableName(db)} as select * from " + viewname, con);
cmd.ExecuteNonQuery();
}
}
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);
}
private void SetupCohortDefinitionAndCustomTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("SuperSecretThing");
dt.Columns.Add("PrivateID");
dt.Rows.Add(new[] { "monkeys can all secretly fly", "Priv_12345" });
dt.Rows.Add(new[] { "the wizard of OZ was a man behind a machine", "Priv_wtf11" });
CustomTable = _cohortDatabase.CreateTable("custTable99", dt);
ColumnInfo[] cols;
new TableInfoImporter(CatalogueRepository, CustomTable).DoImport(out CustomTableInfo, out cols);
CatalogueItem[] cis;
ExtractionInformation[] eis;
new ForwardEngineerCatalogue(CustomTableInfo, cols, true).ExecuteForwardEngineering(out CustomCatalogue, out cis, out eis);
CustomExtractableDataSet = new ExtractableDataSet(DataExportRepository, CustomCatalogue);
foreach (ExtractionInformation e in eis)
{
e.ExtractionCategory = ExtractionCategory.ProjectSpecific;
if (e.GetRuntimeName().Equals("PrivateID"))
{
e.IsExtractionIdentifier = true;
}
e.SaveToDatabase();
}
using (var con = _cohortDatabase.Server.GetConnection())
{
string insertSQL = "SET IDENTITY_INSERT " + definitionTableName + " ON ;" + Environment.NewLine;
insertSQL += "INSERT INTO " + definitionTableName +
" (id,projectNumber,description,version) VALUES (" + cohortIDInTestData + "," +
projectNumberInTestData + ",'unitTestDataForCohort',1)";
con.Open();
using (var cmd = _cohortDatabase.Server.GetCommand(insertSQL, con))
cmd.ExecuteNonQuery();
}
}
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 Create_WithDodgyColumnNames(DatabaseType dbType)
{
_database = GetCleanedServer(dbType);
_table = _database.CreateTable("Trol lol My Table Select * from Group by fish", new DatabaseColumnRequest[] {
new DatabaseColumnRequest("My Lovely Column Select * From Lolz", new DatabaseTypeRequest(typeof(string), 30))
{
AllowNulls = false, IsPrimaryKey = true
},
new DatabaseColumnRequest("ANormalColumnName", new DatabaseTypeRequest(typeof(int))),
new DatabaseColumnRequest("Group By Meeee Colll trollolol", new DatabaseTypeRequest(typeof(int))),
});
GetImplementer().CreateTrigger(new ThrowImmediatelyCheckNotifier());
Assert.AreEqual(TriggerStatus.Enabled, GetImplementer().GetTriggerStatus());
Assert.AreEqual(true, GetImplementer().CheckUpdateTriggerIsEnabledAndHasExpectedBody());
}
public void TestBasicDataTableAnonymiser3(DatabaseType type)
{
DiscoveredDatabase database = GetCleanedServer(type);
//Create a names table that will go into the database
var dt = new DataTable();
dt.Columns.Add("Name");
dt.Rows.Add(new[] { "Thomas" });
dt.Rows.Add(new[] { "Wallace" });
dt.Rows.Add(new[] { "Frank" });
DiscoveredTable table = database.CreateTable("ForbiddenNames", dt);
TableInfo tableInfo;
Import(table, out tableInfo, out _);
//Create the test dataset chunk that will be anonymised
var dtStories = new DataTable();
dtStories.Columns.Add("Story");
dtStories.Rows.Add(new[] { "Thomas went to school regularly" });
dtStories.Rows.Add(new[] { "It seems like Wallace went less regularly" });
dtStories.Rows.Add(new[] { "Mr Smitty was the teacher" });
//Create the anonymiser
var a = new BasicDataTableAnonymiser3();
//Tell it about the database table
a.NamesTable = tableInfo;
//run the anonymisation
var resultTable = a.ProcessPipelineData(dtStories, new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
//check the results
Assert.AreEqual(resultTable.Rows.Count, 3);
Assert.AreEqual("REDACTED went to school regularly", resultTable.Rows[0][0]);
Assert.AreEqual("It seems like REDACTED went less regularly", resultTable.Rows[1][0]);
Assert.AreEqual("Mr Smitty was the teacher", resultTable.Rows[2][0]);
//finally drop the database table
table.Drop();
}
public void Test_FromCataloguesExtractionRequestFulfiller_NoFilterExtraction(DatabaseType databaseType, bool isNoFiltersExtraction)
{
DiscoveredDatabase db = GetCleanedServer(databaseType);
var dt = new DataTable();
dt.Columns.Add("StudyInstanceUID");
dt.Columns.Add("SeriesInstanceUID");
dt.Columns.Add("SOPInstanceUID");
dt.Columns.Add("Extractable", typeof(bool));
dt.Columns.Add(QueryToExecuteColumnSet.DefaultImagePathColumnName);
dt.Rows.Add("1.1", "123.1", "1.1", true, "/images/1.dcm");
dt.SetDoNotReType(true);
DiscoveredTable tbl = db.CreateTable("FromCataloguesExtractionRequestFulfillerTests", dt);
ICatalogue catalogue = Import(tbl);
ExtractionInformation ei = catalogue.GetAllExtractionInformation(ExtractionCategory.Any).First();
var filter = new ExtractionFilter(CatalogueRepository, "Extractable only", ei)
{
IsMandatory = true,
WhereSQL = "Extractable = 1"
};
filter.SaveToDatabase();
var fulfiller = new FromCataloguesExtractionRequestFulfiller(new[] { catalogue });
fulfiller.Rejectors.Add(new RejectAll());
var message = new ExtractionRequestMessage
{
KeyTag = "SeriesInstanceUID",
ExtractionIdentifiers = new List <string>(new [] { "123.1" }),
IsNoFilterExtraction = isNoFiltersExtraction,
};
ExtractImageCollection[] matching = fulfiller.GetAllMatchingFiles(message, new NullAuditExtractions()).ToArray();
int expected = isNoFiltersExtraction ? 1 : 0;
Assert.AreEqual(1, matching.Length);
Assert.AreEqual(expected, matching[0].Accepted.Count);
}
private void SetupCatalogueConfigurationEtc()
{
Database = GetCleanedServer(FAnsi.DatabaseType.MicrosoftSQLServer);
DataTable dt = new DataTable();
dt.Columns.Add("PrivateID");
dt.Columns.Add("Name");
dt.Columns.Add("DateOfBirth");
dt.Rows.Add(new object[] { _cohortKeysGenerated.Keys.First(), "Dave", "2001-01-01" });
var tbl = Database.CreateTable("TestTable", dt, new[] { new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 50)) });
CatalogueItem[] cataItems;
_catalogue = Import(tbl, out _tableInfo, out _columnInfos, out cataItems, out _extractionInformations);
ExtractionInformation _privateID = _extractionInformations.First(e => e.GetRuntimeName().Equals("PrivateID"));
_privateID.IsExtractionIdentifier = true;
_privateID.SaveToDatabase();
}
/// <summary>
/// Creates the <see cref="BulkDataTable"/> in the <see cref="BulkDataDatabase"/> and uploads test data. Use <see cref="ImportAsCatalogue"/> to get
/// rdmp metadata objects pointing at the table.
/// </summary>
public void SetupTestData()
{
//make sure database exists
if (!BulkDataDatabase.Exists())
{
BulkDataDatabase.Create();
}
//generate some people
var people = new PersonCollection();
people.GeneratePeople(5000, r);
//generate the test data
var dt = _dataGenerator.GetDataTable(people, ExpectedNumberOfRowsInTestData);
var tbl = BulkDataDatabase.ExpectTable(BulkDataTable);
if (tbl.Exists())
{
tbl.Drop();
}
//create the table but make sure the chi is a primary key and the correct data type and that we have a sensible primary key
Table = BulkDataDatabase.CreateTable(BulkDataTable, dt, new DatabaseColumnRequest[] {
new DatabaseColumnRequest("chi", new DatabaseTypeRequest(typeof(string), 10))
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("dtCreated", new DatabaseTypeRequest(typeof(DateTime)))
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("hb_extract", new DatabaseTypeRequest(typeof(string), 1))
{
IsPrimaryKey = true
}
});
}
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
}
}
private DiscoveredTable Create <T>(DiscoveredDatabase db, PersonCollection people, Random r, ICheckNotifier notifier, int numberOfRecords, params string[] primaryKey) where T : IDataGenerator
{
string dataset = typeof(T).Name;
notifier.OnCheckPerformed(new CheckEventArgs(string.Format("Generating {0} records for {1}", numberOfRecords, dataset), CheckResult.Success));
var factory = new DataGeneratorFactory();
//half a million biochemistry results
var biochem = factory.Create(typeof(T), r);
var dt = biochem.GetDataTable(people, numberOfRecords);
//prune "nulls"
foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (string.Equals(dr[i] as string, "NULL", StringComparison.CurrentCultureIgnoreCase))
{
dr[i] = DBNull.Value;
}
}
}
notifier.OnCheckPerformed(new CheckEventArgs("Uploading " + dataset, CheckResult.Success));
var tbl = db.CreateTable(dataset, dt, GetExplicitColumnDefinitions <T>());
if (primaryKey.Length != 0)
{
notifier.OnCheckPerformed(new CheckEventArgs("Creating Primary Key " + dataset, CheckResult.Success));
var cols = primaryKey.Select(s => tbl.DiscoverColumn(s)).ToArray();
tbl.CreatePrimaryKey(5000, cols);
}
return(tbl);
}
public ExternalCohortTable CreateDatabase(PrivateIdentifierPrototype privateIdentifierPrototype, ICheckNotifier notifier)
{
if (!_targetDatabase.Exists())
{
notifier.OnCheckPerformed(new CheckEventArgs("Did not find database " + _targetDatabase + " on server so creating it", CheckResult.Success));
_targetDatabase.Create();
}
try
{
var definitionTable = _targetDatabase.CreateTable("CohortDefinition", new[]
{
new DatabaseColumnRequest("id", new DatabaseTypeRequest(typeof(int)))
{
AllowNulls = false, IsAutoIncrement = true, IsPrimaryKey = true
},
new DatabaseColumnRequest("projectNumber", new DatabaseTypeRequest(typeof(int)))
{
AllowNulls = false
},
new DatabaseColumnRequest("version", new DatabaseTypeRequest(typeof(int)))
{
AllowNulls = false
},
new DatabaseColumnRequest("description", new DatabaseTypeRequest(typeof(string), 3000))
{
AllowNulls = false
},
new DatabaseColumnRequest("dtCreated", new DatabaseTypeRequest(typeof(DateTime)))
{
AllowNulls = false, Default = MandatoryScalarFunctions.GetTodaysDate
}
});
var idColumn = definitionTable.DiscoverColumn("id");
var foreignKey = new DatabaseColumnRequest(_definitionTableForeignKeyField, new DatabaseTypeRequest(typeof(int)), false)
{
IsPrimaryKey = true
};
var cohortTable = _targetDatabase.CreateTable("Cohort", new []
{
new DatabaseColumnRequest(privateIdentifierPrototype.RuntimeName, privateIdentifierPrototype.DataType, false)
{
IsPrimaryKey = true
},
new DatabaseColumnRequest(_releaseIdentifierFieldName, new DatabaseTypeRequest(typeof(string), 300))
{
AllowNulls = AllowNullReleaseIdentifiers
},
foreignKey
}
,
//foreign key between id and cohortDefinition_id
new Dictionary <DatabaseColumnRequest, DiscoveredColumn>()
{
{ foreignKey, idColumn }
}, true);
notifier.OnCheckPerformed(new CheckEventArgs("About to create pointer to the source", CheckResult.Success));
var pointer = new ExternalCohortTable(_dataExportRepository, "TestExternalCohort", _targetDatabase.Server.DatabaseType)
{
DatabaseType = _targetDatabase.Server.DatabaseType,
Server = _targetDatabase.Server.Name,
Database = _targetDatabase.GetRuntimeName(),
Username = _targetDatabase.Server.ExplicitUsernameIfAny,
Password = _targetDatabase.Server.ExplicitPasswordIfAny,
Name = _targetDatabase.GetRuntimeName(),
TableName = cohortTable.GetRuntimeName(),
PrivateIdentifierField = privateIdentifierPrototype.RuntimeName,
ReleaseIdentifierField = _releaseIdentifierFieldName,
DefinitionTableForeignKeyField = _definitionTableForeignKeyField,
DefinitionTableName = definitionTable.GetRuntimeName()
};
pointer.SaveToDatabase();
notifier.OnCheckPerformed(new CheckEventArgs("successfully created reference to cohort source in data export manager", CheckResult.Success));
notifier.OnCheckPerformed(new CheckEventArgs("About to run post creation checks", CheckResult.Success));
pointer.Check(notifier);
notifier.OnCheckPerformed(new CheckEventArgs("Finished", CheckResult.Success));
return(pointer);
}
catch (Exception e)
{
notifier.OnCheckPerformed(
new CheckEventArgs("Entire setup failed with exception (double click to find out why)",
CheckResult.Fail, e));
return(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);
}
}
}
}
private void RunAttachStageWithLoadProgressJob(RemoteTableAttacher attacher, DiscoveredDatabase db,
bool mismatchProgress)
{
var syntax = db.Server.GetQuerySyntaxHelper();
//the table to get data from
attacher.RemoteSelectSQL = $"SELECT * FROM table1 WHERE {syntax.EnsureWrapped("DateCol")} >= @startDate AND {syntax.EnsureWrapped("DateCol")} <= @endDate";
attacher.RAWTableName = "table2";
attacher.Check(new ThrowImmediatelyCheckNotifier());
attacher.Initialize(null, db);
using (var dt = new DataTable())
{
dt.Columns.Add("Col1");
dt.Columns.Add("DateCol");
dt.Rows.Add("fff", new DateTime(2000, 1, 1));
dt.Rows.Add("fff", new DateTime(2001, 1, 1));
dt.Rows.Add("fff", new DateTime(2002, 1, 1));
var tbl1 = db.CreateTable("table1", dt);
var tbl2 = db.CreateTable("table2", new []
{
new DatabaseColumnRequest("Col1", new DatabaseTypeRequest(typeof(string), 5)),
new DatabaseColumnRequest("DateCol", new DatabaseTypeRequest(typeof(DateTime)))
});
Assert.AreEqual(3, tbl1.GetRowCount());
Assert.AreEqual(0, tbl2.GetRowCount());
var logManager = new LogManager(new DiscoveredServer(UnitTestLoggingConnectionString));
var lmd = RdmpMockFactory.Mock_LoadMetadataLoadingTable(tbl2);
Mock.Get(lmd).Setup(p => p.CatalogueRepository).Returns(CatalogueRepository);
logManager.CreateNewLoggingTaskIfNotExists(lmd.GetDistinctLoggingTask());
var lp = new LoadProgress(CatalogueRepository, new LoadMetadata(CatalogueRepository, "ffffff"));
lp.OriginDate = new DateTime(2001, 1, 1);
attacher.Progress = lp;
attacher.ProgressUpdateStrategy = new DataLoadProgressUpdateInfo()
{
Strategy = DataLoadProgressUpdateStrategy.DoNothing
};
var dbConfiguration = new HICDatabaseConfiguration(lmd, RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(db, "table2"));
var job = new ScheduledDataLoadJob(RepositoryLocator, "test job", logManager, lmd, new TestLoadDirectory(), new ThrowImmediatelyDataLoadEventListener(), dbConfiguration);
job.LoadProgress = mismatchProgress
? new LoadProgress(CatalogueRepository, new LoadMetadata(CatalogueRepository, "ffsdf"))
: lp;
job.DatesToRetrieve = new List <DateTime> {
new DateTime(2001, 01, 01)
};
job.StartLogging();
attacher.Attach(job, new GracefulCancellationToken());
Assert.AreEqual(3, tbl1.GetRowCount());
Assert.AreEqual(mismatchProgress ? 0 : 1, tbl2.GetRowCount());
}
}
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 DataTable ProcessPipelineData(DataTable toProcess, IDataLoadEventListener listener, GracefulCancellationToken cancellationToken)
{
if (toProcess == null)
{
return(null);
}
IDatabaseColumnRequestAdjuster adjuster = null;
if (Adjuster != null)
{
var constructor = new ObjectConstructor();
adjuster = (IDatabaseColumnRequestAdjuster)constructor.Construct(Adjuster);
}
//work out the table name for the table we are going to create
if (TargetTableName == null)
{
if (string.IsNullOrWhiteSpace(toProcess.TableName))
{
throw new Exception("Chunk did not have a TableName, did not know what to call the newly created table");
}
TargetTableName = QuerySyntaxHelper.MakeHeaderNameSane(toProcess.TableName);
}
ClearPrimaryKeyFromDataTableAndExplicitWriteTypes(toProcess);
StartAuditIfExists(TargetTableName);
if (_loggingDatabaseListener != null)
{
listener = new ForkDataLoadEventListener(listener, _loggingDatabaseListener);
}
EnsureTableHasDataInIt(toProcess);
bool createdTable = false;
if (_firstTime)
{
bool tableAlreadyExistsButEmpty = false;
if (!_database.Exists())
{
throw new Exception("Database " + _database + " does not exist");
}
discoveredTable = _database.ExpectTable(TargetTableName);
//table already exists
if (discoveredTable.Exists())
{
tableAlreadyExistsButEmpty = true;
if (!AllowLoadingPopulatedTables)
{
if (discoveredTable.IsEmpty())
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Found table " + TargetTableName + " already, normally this would forbid you from loading it (data duplication / no primary key etc) but it is empty so we are happy to load it, it will not be created"));
}
else
{
throw new Exception("There is already a table called " + TargetTableName + " at the destination " + _database);
}
}
if (AllowResizingColumnsAtUploadTime)
{
_dataTypeDictionary = discoveredTable.DiscoverColumns().ToDictionary(k => k.GetRuntimeName(), v => v.GetDataTypeComputer(), StringComparer.CurrentCultureIgnoreCase);
}
}
else
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Determined that the table name " + TargetTableName + " is unique at destination " + _database));
}
//create connection to destination
if (!tableAlreadyExistsButEmpty)
{
createdTable = true;
if (AllowResizingColumnsAtUploadTime)
{
_database.CreateTable(out _dataTypeDictionary, TargetTableName, toProcess, ExplicitTypes.ToArray(), true, adjuster);
}
else
{
_database.CreateTable(TargetTableName, toProcess, ExplicitTypes.ToArray(), true, adjuster);
}
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Created table " + TargetTableName + " successfully."));
}
_managedConnection = _server.BeginNewTransactedConnection();
_bulkcopy = discoveredTable.BeginBulkInsert(_managedConnection.ManagedTransaction);
if (Culture != null)
{
_bulkcopy.DateTimeDecider.Culture = Culture;
}
_firstTime = false;
}
try
{
if (AllowResizingColumnsAtUploadTime && !createdTable)
{
ResizeColumnsIfRequired(toProcess, listener);
}
//push the data
swTimeSpentWritting.Start();
_affectedRows += _bulkcopy.Upload(toProcess);
swTimeSpentWritting.Stop();
listener.OnProgress(this, new ProgressEventArgs("Uploading to " + TargetTableName, new ProgressMeasurement(_affectedRows, ProgressType.Records), swTimeSpentWritting.Elapsed));
}
catch (Exception e)
{
_managedConnection.ManagedTransaction.AbandonAndCloseConnection();
if (LoggingServer != null)
{
_dataLoadInfo.LogFatalError(GetType().Name, ExceptionHelper.ExceptionToListOfInnerMessages(e, true));
}
throw new Exception("Failed to write rows (in transaction) to table " + TargetTableName, e);
}
return(null);
}
private DiscoveredTable BuildExampleExtractionTable(DiscoveredDatabase db, string modality, int recordCount, bool useDcmFileExtension)
{
var tbl = db.CreateTable(modality + "_IsExtractable",
new[]
{
new DatabaseColumnRequest("StudyInstanceUID", new DatabaseTypeRequest(typeof(string), 64), false),
new DatabaseColumnRequest("SeriesInstanceUID", new DatabaseTypeRequest(typeof(string), 64), false),
new DatabaseColumnRequest("SOPInstanceUID", new DatabaseTypeRequest(typeof(string), 64), false)
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("IsExtractableToDisk", new DatabaseTypeRequest(typeof(bool))),
new DatabaseColumnRequest("IsExtractableToDisk_Reason", new DatabaseTypeRequest(typeof(string), 512)),
new DatabaseColumnRequest("RelativeFileArchiveURI", new DatabaseTypeRequest(typeof(string), 512), false),
new DatabaseColumnRequest("IsOriginal", new DatabaseTypeRequest(typeof(bool)), false),
new DatabaseColumnRequest("IsPrimary", new DatabaseTypeRequest(typeof(bool)), false),
new DatabaseColumnRequest(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int))),
new DatabaseColumnRequest(SpecialFieldNames.ValidFrom, new DatabaseTypeRequest(typeof(DateTime))),
});
if (recordCount > 0)
{
var r = new Random(500);
DicomDataGenerator g = new DicomDataGenerator(r, null);
g.MaximumImages = recordCount;
var persons = new PersonCollection();
persons.GeneratePeople(500, r);
while (recordCount > 0)
{
foreach (var image in g.GenerateStudyImages(persons.People[r.Next(persons.People.Length)], out var study))
{
tbl.Insert(new Dictionary <string, object>()
{
{ "StudyInstanceUID", image.GetSingleValue <string>(DicomTag.StudyInstanceUID) },
{ "SeriesInstanceUID", image.GetSingleValue <string>(DicomTag.SeriesInstanceUID) },
{ "SOPInstanceUID", image.GetSingleValue <string>(DicomTag.SOPInstanceUID) },
{ "IsExtractableToDisk", true },
{ "IsExtractableToDisk_Reason", DBNull.Value },
{ "RelativeFileArchiveURI", image.GetSingleValue <string>(DicomTag.SOPInstanceUID) + (useDcmFileExtension ? ".dcm" :"") },
{ "IsOriginal", image.GetValues <string>(DicomTag.ImageType)[0] == "ORIGINAL" },
{ "IsPrimary", image.GetValues <string>(DicomTag.ImageType)[1] == "PRIMARY" },
{ SpecialFieldNames.DataLoadRunID, 1 },
{ SpecialFieldNames.ValidFrom, DateTime.Now },
});
recordCount--;
if (recordCount <= 0)
{
break;
}
}
}
}
return(tbl);
}
