public void TestIPluginCohortCompiler_PopulatesCacheCorrectly()
{
var activator = new ConsoleInputManager(RepositoryLocator, new ThrowImmediatelyCheckNotifier())
{
DisallowInput = true
};
// create a cohort config
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "mycic");
cic.QueryCachingServer_ID = externalDatabaseServer.ID;
cic.SaveToDatabase();
// this special Catalogue will be detected by ExamplePluginCohortCompiler and interpreted as an API call
var myApi = new Catalogue(CatalogueRepository, ExamplePluginCohortCompiler.ExampleAPIName);
// add it to the cohort config
cic.CreateRootContainerIfNotExists();
// create a use of the API as an AggregateConfiguration
var cmd = new ExecuteCommandAddCatalogueToCohortIdentificationSetContainer(activator, new CatalogueCombineable(myApi), cic.RootCohortAggregateContainer);
Assert.IsFalse(cmd.IsImpossible, cmd.ReasonCommandImpossible);
cmd.Execute();
// run the cic
var source = new CohortIdentificationConfigurationSource();
source.PreInitialize(cic, new ThrowImmediatelyDataLoadEventListener());
var dt = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
// 5 random chi numbers
Assert.AreEqual(5, dt.Rows.Count);
// test stale
cmd.AggregateCreatedIfAny.Description = "2";
cmd.AggregateCreatedIfAny.SaveToDatabase();
// run the cic again
source = new CohortIdentificationConfigurationSource();
source.PreInitialize(cic, new ThrowImmediatelyDataLoadEventListener());
dt = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
// because the rules changed to generate 2 chis only there should be a new result
Assert.AreEqual(2, dt.Rows.Count);
var results = new[] { (string)dt.Rows[0][0], (string)dt.Rows[1][0] };
// run the cic again with no changes, the results should be unchanged since there is no config changed
// I.e. no new chis should be generated and the cached values returned
source = new CohortIdentificationConfigurationSource();
source.PreInitialize(cic, new ThrowImmediatelyDataLoadEventListener());
dt = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
Assert.AreEqual(2, dt.Rows.Count);
var results2 = new[] { (string)dt.Rows[0][0], (string)dt.Rows[1][0] };
Assert.AreEqual(results[0], results2[0]);
Assert.AreEqual(results[1], results2[1]);
}
public override void Execute()
{
base.Execute();
var p = new QueryCachingPatcher();
CreatePlatformDatabase createPlatform = new CreatePlatformDatabase(p);
createPlatform.ShowDialog();
var db = createPlatform.DatabaseCreatedIfAny;
if (db != null)
{
var newServer = new ExternalDatabaseServer(Activator.RepositoryLocator.CatalogueRepository, "Caching Database", p);
newServer.SetProperties(db);
_cic.QueryCachingServer_ID = newServer.ID;
_cic.SaveToDatabase();
SetDefaultIfNotExists(newServer, PermissableDefaults.CohortIdentificationQueryCachingServer_ID, true);
Publish(_cic);
}
}
public override void Execute()
{
base.Execute();
var p = new QueryCachingPatcher();
CreatePlatformDatabase createPlatform = new CreatePlatformDatabase(p);
createPlatform.ShowDialog();
if (!string.IsNullOrWhiteSpace(createPlatform.DatabaseConnectionString))
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(createPlatform.DatabaseConnectionString);
var newServer = new ExternalDatabaseServer(Activator.RepositoryLocator.CatalogueRepository, "Caching Database", p);
newServer.Server = builder.DataSource;
newServer.Database = builder.InitialCatalog;
//if there is a username/password
if (!builder.IntegratedSecurity)
{
newServer.Password = builder.Password;
newServer.Username = builder.UserID;
}
newServer.SaveToDatabase();
_cic.QueryCachingServer_ID = newServer.ID;
_cic.SaveToDatabase();
SetDefaultIfNotExists(newServer, PermissableDefaults.CohortIdentificationQueryCachingServer_ID, true);
Publish(_cic);
}
}
private CohortIdentificationConfiguration CreateCohortIdentificationConfiguration(ExtractionFilter inclusionFilter1)
{
//Create the top level configuration object
var cic = new CohortIdentificationConfiguration(_repos.CatalogueRepository, "Tayside Lung Cancer Cohort");
//create a UNION container for Inclusion Criteria
var container = new CohortAggregateContainer(_repos.CatalogueRepository, SetOperation.UNION);
container.Name = "Inclusion Criteria";
container.SaveToDatabase();
cic.RootCohortAggregateContainer_ID = container.ID;
cic.SaveToDatabase();
//Create a new cohort set to the 'Inclusion Criteria' based on the filters Catalogue
var cata = inclusionFilter1.ExtractionInformation.CatalogueItem.Catalogue;
var ac = cic.CreateNewEmptyConfigurationForCatalogue(cata, (a, b) => { throw new Exception("Problem encountered with chi column(s)"); }, false);
container.AddChild(ac, 0);
//Add the filter to the WHERE logic of the cohort set
var whereContainer = new AggregateFilterContainer(_repos.CatalogueRepository, FilterContainerOperation.OR);
ac.Name = "People with " + inclusionFilter1.Name;
ac.RootFilterContainer_ID = whereContainer.ID;
cic.EnsureNamingConvention(ac); //this will put cicx at the front and cause implicit SaveToDatabase
FilterImporter filterImporter = new FilterImporter(new AggregateFilterFactory(_repos.CatalogueRepository), null);
var cloneFilter = filterImporter.ImportFilter(inclusionFilter1, null);
whereContainer.AddChild(cloneFilter);
return(cic);
}
public void Join_PatientIndexTable_OptionalCacheOnSameServer(DatabaseType dbType, bool createQueryCache)
{
/*
* Server1
* _____________
* |Biochemistry|
* ↓
* ___________________
* | Cache (optional) |
* ↓ join ↓
* _____________________
* | Hospital Admissions|
*
*/
var db = GetCleanedServer(dbType);
ExternalDatabaseServer cache = null;
if (createQueryCache)
{
cache = CreateCache(db);
}
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var joinable = SetupPatientIndexTable(db, people, r, cic);
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache?.ID;
cic.SaveToDatabase();
var hospitalAdmissions = SetupPatientIndexTableUser(db, people, r, cic, joinable);
cic.RootCohortAggregateContainer.AddChild(hospitalAdmissions, 0);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
AssertNoErrors(compiler);
if (createQueryCache)
{
Assert.IsTrue(compiler.Tasks.Any(t => t.Key.GetCachedQueryUseCount().Equals("1/1")), "Expected cache to be used for the joinable");
}
else
{
Assert.IsTrue(compiler.Tasks.Any(t => t.Key.GetCachedQueryUseCount().Equals("0/1")), "Did not create cache so expected cache usage to be 0");
}
}
public void Join_PatientIndexTable_DoNotUseCacheOnDifferentServer(DatabaseType dbType)
{
/*
* Server1 Server 2
* _____________ _________
* |Biochemistry| → | Cache | (cache is still populated but not used in the resulting join).
*
* ↓ join ↓ (do not use cache)
* _____________________
* | Hospital Admissions|
*
*/
//get the data database
var db = GetCleanedServer(dbType);
//create the cache on the other server type (doesn't matter what type just as long as it's different).
var dbCache =
GetCleanedServer(Enum.GetValues(typeof(DatabaseType)).Cast <DatabaseType>().First(t => t != dbType));
ExternalDatabaseServer cache = CreateCache(dbCache);
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var joinable = SetupPatientIndexTable(db, people, r, cic);
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache?.ID;
cic.SaveToDatabase();
var hospitalAdmissions = SetupPatientIndexTableUser(db, people, r, cic, joinable);
cic.RootCohortAggregateContainer.AddChild(hospitalAdmissions, 0);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
AssertNoErrors(compiler);
Assert.IsTrue(compiler.Tasks.Any(t => t.Key.GetCachedQueryUseCount().Equals("1/1")), "Expected cache to be used only for the final UNION");
}
public void Join_PatientIndexTable_ThreeServers()
{
/*
* Server1 Server 2 Server 3
* _____________ _________
* |Biochemistry| → (successfully caches joinable bit) | Cache |
*
* ↘ join ↘ (should crash)
* _____________________
* | Hospital Admissions|
*
*/
var server1 = GetCleanedServer(DatabaseType.MySql);
var server2 = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
var server3 = GetCleanedServer(DatabaseType.Oracle);
ExternalDatabaseServer cache = CreateCache(server3);
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var joinable = SetupPatientIndexTable(server1, people, r, cic);
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache?.ID;
cic.SaveToDatabase();
var hospitalAdmissions = SetupPatientIndexTableUser(server2, people, r, cic, joinable);
cic.RootCohortAggregateContainer.AddChild(hospitalAdmissions, 0);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
var hospitalAdmissionsTask = compiler.Tasks.Keys.OfType <AggregationTask>().Single(t => t.Aggregate.Equals(hospitalAdmissions));
Assert.AreEqual(CompilationState.Crashed, hospitalAdmissionsTask.State);
StringAssert.Contains("is not fully cached and CacheUsageDecision is MustUse", hospitalAdmissionsTask.CrashMessage.ToString());
}
public void SetupTestData(ICatalogueRepository repository)
{
BlitzMainDataTables();
Database = GetCleanedServer(FAnsi.DatabaseType.MicrosoftSQLServer);
testData = new BulkTestsData(repository, Database, 100);
testData.SetupTestData();
testData.ImportAsCatalogue();
aggregate1 =
new AggregateConfiguration(repository, testData.catalogue, "UnitTestAggregate1");
aggregate1.CountSQL = null;
aggregate1.SaveToDatabase();
new AggregateDimension(repository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate1);
aggregate2 =
new AggregateConfiguration(repository, testData.catalogue, "UnitTestAggregate2");
aggregate2.CountSQL = null;
aggregate2.SaveToDatabase();
new AggregateDimension(repository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate2);
aggregate3 =
new AggregateConfiguration(repository, testData.catalogue, "UnitTestAggregate3");
aggregate3.CountSQL = null;
aggregate3.SaveToDatabase();
new AggregateDimension(repository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate3);
cohortIdentificationConfiguration = new CohortIdentificationConfiguration(repository, "UnitTestIdentification");
rootcontainer = new CohortAggregateContainer(repository, SetOperation.EXCEPT);
container1 = new CohortAggregateContainer(repository, SetOperation.UNION);
cohortIdentificationConfiguration.RootCohortAggregateContainer_ID = rootcontainer.ID;
cohortIdentificationConfiguration.SaveToDatabase();
cohortIdentificationConfiguration.EnsureNamingConvention(aggregate1);
cohortIdentificationConfiguration.EnsureNamingConvention(aggregate2);
cohortIdentificationConfiguration.EnsureNamingConvention(aggregate3);
}
public void Join_PatientIndexTable_NotOnCacheServer()
{
/*
* Server1 Server 2
* _____________ _________
* |Biochemistry| → | Cache | (cache must first be populated)
*
* ↘ join ↘ (must use cache)
* _____________________
* | Hospital Admissions|
*
*/
var server1 = GetCleanedServer(DatabaseType.MySql);
var server2 = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
ExternalDatabaseServer cache = CreateCache(server2);
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var joinable = SetupPatientIndexTable(server1, people, r, cic);
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache?.ID;
cic.SaveToDatabase();
var hospitalAdmissions = SetupPatientIndexTableUser(server2, people, r, cic, joinable);
cic.RootCohortAggregateContainer.AddChild(hospitalAdmissions, 0);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
AssertNoErrors(compiler);
Assert.IsTrue(compiler.Tasks.Any(t => t.Key.GetCachedQueryUseCount().Equals("1/1")), "Expected cache to be used only for the final UNION");
}
public override void Execute()
{
base.Execute();
if (SelectOne(_caches.ToList(), out ExternalDatabaseServer selected))
{
if (selected == null)
{
_cic.QueryCachingServer_ID = null;
}
else
{
_cic.QueryCachingServer_ID = selected.ID;
}
_cic.SaveToDatabase();
Publish(_cic);
}
}
public void SetupTestData(ICatalogueRepository repository)
{
testData = new BulkTestsData(repository, DiscoveredDatabaseICanCreateRandomTablesIn, 100);
testData.SetupTestData();
testData.ImportAsCatalogue();
aggregate1 =
new AggregateConfiguration(repository, testData.catalogue, "UnitTestAggregate1");
aggregate1.CountSQL = null;
aggregate1.SaveToDatabase();
new AggregateDimension(repository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate1);
aggregate2 =
new AggregateConfiguration(repository, testData.catalogue, "UnitTestAggregate2");
aggregate2.CountSQL = null;
aggregate2.SaveToDatabase();
new AggregateDimension(repository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate2);
aggregate3 =
new AggregateConfiguration(repository, testData.catalogue, "UnitTestAggregate3");
aggregate3.CountSQL = null;
aggregate3.SaveToDatabase();
new AggregateDimension(repository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate3);
cohortIdentificationConfiguration = new CohortIdentificationConfiguration(repository, "UnitTestIdentification");
rootcontainer = new CohortAggregateContainer(repository, SetOperation.EXCEPT);
container1 = new CohortAggregateContainer(repository, SetOperation.UNION);
cohortIdentificationConfiguration.RootCohortAggregateContainer_ID = rootcontainer.ID;
cohortIdentificationConfiguration.SaveToDatabase();
cohortIdentificationConfiguration.EnsureNamingConvention(aggregate1);
cohortIdentificationConfiguration.EnsureNamingConvention(aggregate2);
cohortIdentificationConfiguration.EnsureNamingConvention(aggregate3);
}
public void TestIPluginCohortCompiler_APIsCantHavePatientIndexTables()
{
var activator = new ConsoleInputManager(RepositoryLocator, new ThrowImmediatelyCheckNotifier())
{
DisallowInput = true
};
// create a cohort config
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "mycic");
cic.QueryCachingServer_ID = externalDatabaseServer.ID;
cic.SaveToDatabase();
// this special Catalogue will be detected by ExamplePluginCohortCompiler and interpreted as an API call
var myApi = new Catalogue(CatalogueRepository, ExamplePluginCohortCompiler.ExampleAPIName);
// add it to the cohort config
cic.CreateRootContainerIfNotExists();
// We need something in the root container otherwise the cic won't build
var cmd = new ExecuteCommandAddCatalogueToCohortIdentificationSetContainer(activator, new CatalogueCombineable(myApi), cic.RootCohortAggregateContainer);
Assert.IsFalse(cmd.IsImpossible, cmd.ReasonCommandImpossible);
cmd.Execute();
var regularAggregate = cmd.AggregateCreatedIfAny;
// The thing we are wanting to test - creating a use of the API as a patient index table
var cmd2 = new ExecuteCommandAddCatalogueToCohortIdentificationAsPatientIndexTable(
activator, new CatalogueCombineable(myApi), cic);
Assert.IsFalse(cmd2.IsImpossible, cmd2.ReasonCommandImpossible);
cmd2.Execute();
var joinables = cic.GetAllJoinables();
// make them join one another
var ex = Assert.Throws <NotSupportedException>(() =>
new JoinableCohortAggregateConfigurationUse(CatalogueRepository, regularAggregate, joinables[0]));
Assert.AreEqual("API calls cannot join with PatientIndexTables (The API call must be self contained)", ex.Message);
}
public override void Execute()
{
base.Execute();
var dialog = new SelectIMapsDirectlyToDatabaseTableDialog(_caches, true, false);
if (dialog.ShowDialog() == DialogResult.OK)
{
if (dialog.Selected == null)
{
_cic.QueryCachingServer_ID = null;
}
else
{
_cic.QueryCachingServer_ID = dialog.Selected.ID;
}
_cic.SaveToDatabase();
Publish(_cic);
}
}
//[TestCase(DatabaseType.Oracle,true)] //Oracle FAnsi doesn't currently support parameters
//[TestCase(DatabaseType.Oracle,false)]
public void Test_SingleServer_WithOneParameter(DatabaseType dbType, bool useParameter)
{
/*
* Server1 Server2
* _____________________ _____________________
* |HospitalAdmissions | → | Cache |
* @date_of_max
*
*/
var server1 = GetCleanedServer(dbType);
var server2 = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
var cache = CreateCache(server2);
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var ac1 = SetupAggregateConfigurationWithFilter(server1, people, r, cic, useParameter, "@date_of_max", "'2001-01-01'");
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache.ID;
cic.SaveToDatabase();
var root = cic.RootCohortAggregateContainer;
root.AddChild(ac1, 0);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
AssertNoErrors(compiler);
Assert.IsTrue(compiler.Tasks.Where(t => t.Key is AggregationContainerTask).Any(t => t.Key.GetCachedQueryUseCount().Equals("1/1")), "Expected UNION container to use the cache");
}
public void CreateNewCohortIdentificationConfiguration_SaveAndReload()
{
var config = new CohortIdentificationConfiguration(CatalogueRepository, "franky");
try
{
Assert.IsTrue(config.Exists());
Assert.AreEqual("franky", config.Name);
config.Description = "Hi there";
config.SaveToDatabase();
CohortIdentificationConfiguration config2 = CatalogueRepository.GetObjectByID <CohortIdentificationConfiguration>(config.ID);
Assert.AreEqual("Hi there", config2.Description);
}
finally
{
config.DeleteInDatabase();
Assert.IsFalse(config.Exists());
}
}
public void TestIPluginCohortCompiler_TestCloneCic()
{
var activator = new ConsoleInputManager(RepositoryLocator, new ThrowImmediatelyCheckNotifier())
{
DisallowInput = true
};
// create a cohort config
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "mycic");
cic.QueryCachingServer_ID = externalDatabaseServer.ID;
cic.SaveToDatabase();
// this special Catalogue will be detected by ExamplePluginCohortCompiler and interpreted as an API call
var myApi = new Catalogue(CatalogueRepository, ExamplePluginCohortCompiler.ExampleAPIName);
// add it to the cohort config
cic.CreateRootContainerIfNotExists();
// create a use of the API as an AggregateConfiguration
var cmd = new ExecuteCommandAddCatalogueToCohortIdentificationSetContainer(activator, new CatalogueCombineable(myApi), cic.RootCohortAggregateContainer);
Assert.IsFalse(cmd.IsImpossible, cmd.ReasonCommandImpossible);
cmd.Execute();
cmd.AggregateCreatedIfAny.Description = "33";
cmd.AggregateCreatedIfAny.SaveToDatabase();
// clone the cic
var cmd2 = new ExecuteCommandCloneCohortIdentificationConfiguration(activator, cic);
Assert.IsFalse(cmd2.IsImpossible, cmd2.ReasonCommandImpossible);
cmd2.Execute();
var cloneAc = cmd2.CloneCreatedIfAny.RootCohortAggregateContainer.GetAggregateConfigurations()[0];
Assert.AreEqual("33", cloneAc.Description);
}
public void CohortGenerationDifferingTableValuedParametersTest()
{
CreateFunction();
//In this example we have 2 configurations which both target the same table valued function but which must have different parameter values
var config1 = new AggregateConfiguration(CatalogueRepository, _function.Cata, "CohortGenerationDifferingTableValuedParametersTest_1");
config1.CountSQL = null;
config1.SaveToDatabase();
var config2 = new AggregateConfiguration(CatalogueRepository, _function.Cata, "CohortGenerationDifferingTableValuedParametersTest_2");
config2.CountSQL = null;
config2.SaveToDatabase();
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "CohortGenerationDifferingTableValuedParametersTest");
cic.EnsureNamingConvention(config1);
cic.EnsureNamingConvention(config2);
try
{
//make the string column the extraction identifier
_function.ExtractionInformations[1].IsExtractionIdentifier = true;
_function.ExtractionInformations[1].SaveToDatabase();
//add the extraction identtifier as the only dimension one ach of the aggregate configurations that we will use for the cohort identification query
new AggregateDimension(CatalogueRepository, _function.ExtractionInformations[1], config1);
new AggregateDimension(CatalogueRepository, _function.ExtractionInformations[1], config2);
Assert.IsNull(cic.RootCohortAggregateContainer_ID);
//create a root container for it
CohortAggregateContainer container = new CohortAggregateContainer(CatalogueRepository, SetOperation.INTERSECT);
//set the container as the root container for the cohort identification task object
cic.RootCohortAggregateContainer_ID = container.ID;
cic.SaveToDatabase();
//put both the aggregates into the container
container.AddChild(config1, 0);
container.AddChild(config2, 1);
CohortQueryBuilder builder = new CohortQueryBuilder(cic);
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"DECLARE @startNumber AS int;
SET @startNumber=5;
DECLARE @stopNumber AS int;
SET @stopNumber=10;
DECLARE @name AS varchar(50);
SET @name='fish';
(
/*cic_{0}_CohortGenerationDifferingTableValuedParametersTest_1*/
SELECT
distinct
MyAwesomeFunction.[Name]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..MyAwesomeFunction(@startNumber,@stopNumber,@name) AS MyAwesomeFunction
INTERSECT
/*cic_{0}_CohortGenerationDifferingTableValuedParametersTest_2*/
SELECT
distinct
MyAwesomeFunction.[Name]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..MyAwesomeFunction(@startNumber,@stopNumber,@name) AS MyAwesomeFunction
)
", cic.ID)),
CollapseWhitespace(builder.SQL));
//now override JUST @name
var param1 = new AnyTableSqlParameter(CatalogueRepository, config1, "DECLARE @name AS varchar(50);");
param1.Value = "'lobster'";
param1.SaveToDatabase();
var param2 = new AnyTableSqlParameter(CatalogueRepository, config2, "DECLARE @name AS varchar(50);");
param2.Value = "'monkey'";
param2.SaveToDatabase();
CohortQueryBuilder builder2 = new CohortQueryBuilder(cic);
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"DECLARE @startNumber AS int;
SET @startNumber=5;
DECLARE @stopNumber AS int;
SET @stopNumber=10;
DECLARE @name AS varchar(50);
SET @name='lobster';
DECLARE @name_2 AS varchar(50);
SET @name_2='monkey';
(
/*cic_{0}_CohortGenerationDifferingTableValuedParametersTest_1*/
SELECT
distinct
MyAwesomeFunction.[Name]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..MyAwesomeFunction(@startNumber,@stopNumber,@name) AS MyAwesomeFunction
INTERSECT
/*cic_{0}_CohortGenerationDifferingTableValuedParametersTest_2*/
SELECT
distinct
MyAwesomeFunction.[Name]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..MyAwesomeFunction(@startNumber,@stopNumber,@name_2) AS MyAwesomeFunction
)
", cic.ID)),
CollapseWhitespace(builder2.SQL));
}
finally
{
cic.DeleteInDatabase();
config1.DeleteInDatabase();
config2.DeleteInDatabase();
}
}
public void RefreshCohort_WithCaching()
{
ExtractionPipelineUseCase useCase;
IExecuteDatasetExtractionDestination results;
var pipe = new Pipeline(CatalogueRepository, "RefreshPipeWithCaching");
var source = new PipelineComponent(CatalogueRepository, pipe, typeof(CohortIdentificationConfigurationSource), 0);
var args = source.CreateArgumentsForClassIfNotExists <CohortIdentificationConfigurationSource>();
var freezeArg = args.Single(a => a.Name.Equals("FreezeAfterSuccessfulImport"));
freezeArg.SetValue(false);
freezeArg.SaveToDatabase();
var dest = new PipelineComponent(CatalogueRepository, pipe, typeof(BasicCohortDestination), 0);
var argsDest = dest.CreateArgumentsForClassIfNotExists <BasicCohortDestination>();
var allocatorArg = argsDest.Single(a => a.Name.Equals("ReleaseIdentifierAllocator"));
allocatorArg.SetValue(null);
allocatorArg.SaveToDatabase();
pipe.SourcePipelineComponent_ID = source.ID;
pipe.DestinationPipelineComponent_ID = dest.ID;
pipe.SaveToDatabase();
Execute(out useCase, out results);
var oldcohort = _configuration.Cohort;
//Create a query cache
var p = new QueryCachingPatcher();
ExternalDatabaseServer queryCacheServer = new ExternalDatabaseServer(CatalogueRepository, "TestCohortRefreshing_CacheTest", p);
DiscoveredDatabase cachedb = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase("TestCohortRefreshing_CacheTest");
if (cachedb.Exists())
{
cachedb.Drop();
}
new MasterDatabaseScriptExecutor(cachedb).CreateAndPatchDatabase(p, new ThrowImmediatelyCheckNotifier());
queryCacheServer.SetProperties(cachedb);
//Create a Cohort Identification configuration (query) that will identify the cohort
CohortIdentificationConfiguration cic = new CohortIdentificationConfiguration(RepositoryLocator.CatalogueRepository, "RefreshCohort.cs");;
try
{
//make it use the cache
cic.QueryCachingServer_ID = queryCacheServer.ID;
cic.SaveToDatabase();
//give it a single table query to fetch distinct chi from test data
var agg = cic.CreateNewEmptyConfigurationForCatalogue(_catalogue, null);
//add the sub query as the only entry in the cic (in the root container)
cic.CreateRootContainerIfNotExists();
cic.RootCohortAggregateContainer.AddChild(agg, 1);
//make the ExtractionConfiguration refresh cohort query be the cic
_configuration.CohortIdentificationConfiguration_ID = cic.ID;
_configuration.CohortRefreshPipeline_ID = pipe.ID;
_configuration.SaveToDatabase();
//get a refreshing engine
var engine = new CohortRefreshEngine(new ThrowImmediatelyDataLoadEventListener(), _configuration);
engine.Execute();
Assert.NotNull(engine.Request.NewCohortDefinition);
var oldData = oldcohort.GetExternalData();
Assert.AreEqual(oldData.ExternalDescription, engine.Request.NewCohortDefinition.Description);
Assert.AreEqual(oldData.ExternalVersion + 1, engine.Request.NewCohortDefinition.Version);
Assert.AreNotEqual(oldcohort.CountDistinct, engine.Request.CohortCreatedIfAny.CountDistinct);
//now nuke all data in the catalogue so the cic returns nobody (except that the identifiers are cached eh?)
DataAccessPortal.GetInstance().ExpectDatabase(_tableInfo, DataAccessContext.InternalDataProcessing).ExpectTable(_tableInfo.GetRuntimeName()).Truncate();
var toMem = new ToMemoryDataLoadEventListener(false);
//get a new engine
engine = new CohortRefreshEngine(toMem, _configuration);
//execute it
var ex = Assert.Throws <Exception>(() => engine.Execute());
Assert.IsTrue(ex.InnerException.InnerException.Message.Contains("CohortIdentificationCriteria execution resulted in an empty dataset"));
//expected this message to happen
//that it did clear the cache
Assert.AreEqual(1, toMem.EventsReceivedBySender.SelectMany(kvp => kvp.Value).Count(msg => msg.Message.Equals("Clearing Cohort Identifier Cache")));
}
finally
{
//make the ExtractionConfiguration not use the cic query
_configuration.CohortRefreshPipeline_ID = null;
_configuration.CohortIdentificationConfiguration_ID = null;
_configuration.SaveToDatabase();
//delete the cic query
cic.QueryCachingServer_ID = null;
cic.SaveToDatabase();
cic.DeleteInDatabase();
//delete the caching database
queryCacheServer.DeleteInDatabase();
cachedb.Drop();
}
}
public void CohortIdentificationConfiguration_Join_PatientIndexTable()
{
DataTable header = new DataTable();
header.Columns.Add("ID");
header.Columns.Add("Chi");
header.Columns.Add("Age");
header.Columns.Add("Date");
header.Columns.Add("Healthboard");
header.PrimaryKey = new [] { header.Columns["ID"] };
header.Rows.Add("1", "0101010101", 50, new DateTime(2001, 1, 1), "T");
header.Rows.Add("2", "0202020202", 50, new DateTime(2002, 2, 2), "T");
var hTbl = From.CreateTable("header", header);
var cata = Import(hTbl, out TableInfo hTi, out _);
cata.Name = "My Combo Join Catalogue";
cata.SaveToDatabase();
var scripter = new MasterDatabaseScriptExecutor(To);
var patcher = new QueryCachingPatcher();
scripter.CreateAndPatchDatabase(patcher, new AcceptAllCheckNotifier());
var edsCache = new ExternalDatabaseServer(CatalogueRepository, "Cache", new QueryCachingPatcher());
edsCache.SetProperties(To);
DataTable results = new DataTable();
results.Columns.Add("Header_ID");
results.Columns.Add("TestCode");
results.Columns.Add("Result");
results.Rows.Add("1", "HBA1C", 50);
results.Rows.Add("1", "ECOM", "Hi fellas");
results.Rows.Add("1", "ALB", 100);
results.Rows.Add("2", "ALB", 50);
var rTbl = From.CreateTable("results", results);
var importer = new TableInfoImporter(CatalogueRepository, rTbl);
importer.DoImport(out TableInfo rTi, out ColumnInfo[] rColInfos);
var fe = new ForwardEngineerCatalogue(rTi, rColInfos, true);
fe.ExecuteForwardEngineering(cata);
//Should now be 1 Catalogue with all the columns (tables will have to be joined to build the query though)
Assert.AreEqual(8, cata.GetAllExtractionInformation(ExtractionCategory.Core).Length);
var ji = new JoinInfo(CatalogueRepository,
rTi.ColumnInfos.Single(ci => ci.GetRuntimeName().Equals("Header_ID", StringComparison.CurrentCultureIgnoreCase)),
hTi.ColumnInfos.Single(ci => ci.GetRuntimeName().Equals("ID", StringComparison.CurrentCultureIgnoreCase)),
ExtractionJoinType.Right,
null
);
//setup a cic that uses the cache
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "MyCic");
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = edsCache.ID;
cic.SaveToDatabase();
//create a patient index table that shows all the times that they had a test in any HB (with the HB being part of the result set)
var acPatIndex = new AggregateConfiguration(CatalogueRepository, cata, "My PatIndes");
var eiChi = cata.GetAllExtractionInformation(ExtractionCategory.Core).Single(ei => ei.GetRuntimeName().Equals("Chi"));
eiChi.IsExtractionIdentifier = true;
acPatIndex.CountSQL = null;
eiChi.SaveToDatabase();
acPatIndex.AddDimension(eiChi);
acPatIndex.AddDimension(cata.GetAllExtractionInformation(ExtractionCategory.Core).Single(ei => ei.GetRuntimeName().Equals("Date")));
acPatIndex.AddDimension(cata.GetAllExtractionInformation(ExtractionCategory.Core).Single(ei => ei.GetRuntimeName().Equals("Healthboard")));
cic.EnsureNamingConvention(acPatIndex);
var joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cic, acPatIndex);
Assert.IsTrue(acPatIndex.IsCohortIdentificationAggregate);
Assert.IsTrue(acPatIndex.IsJoinablePatientIndexTable());
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50);
var cancellation = new System.Threading.CancellationToken();
runner.Run(cancellation);
//they should not be executing and should be completed
Assert.IsFalse(compiler.Tasks.Any(t => t.Value.IsExecuting));
Assert.AreEqual(Phase.Finished, runner.ExecutionPhase);
var manager = new CachedAggregateConfigurationResultsManager(edsCache);
var cacheTableName = manager.GetLatestResultsTableUnsafe(acPatIndex, AggregateOperation.JoinableInceptionQuery);
Assert.IsNotNull(cacheTableName, "No results were cached!");
var cacheTable = To.ExpectTable(cacheTableName.GetRuntimeName());
//chi, Date and TestCode
Assert.AreEqual(3, cacheTable.DiscoverColumns().Length);
//healthboard should be a string
Assert.AreEqual(typeof(string), cacheTable.DiscoverColumn("Healthboard").DataType.GetCSharpDataType());
/* Query Cache contains this:
*
* Chi Date Healthboard
* 0101010101 2001-01-01 00:00:00.0000000 T
* 0202020202 2002-02-02 00:00:00.0000000 T
*/
Assert.AreEqual(2, cacheTable.GetRowCount());
//Now we could add a new AggregateConfiguration that uses the joinable!
}
public void TestIPluginCohortCompiler_AsPatientIndexTable()
{
var activator = new ConsoleInputManager(RepositoryLocator, new ThrowImmediatelyCheckNotifier())
{
DisallowInput = true
};
// Create a regular normal boring old table that will join into the results of the API call
var db = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
using DataTable dt = new DataTable();
dt.Columns.Add("chi");
dt.Rows.Add("0101010101");
var tbl = db.CreateTable("RegularBoringOldTable", dt);
var cata = (Catalogue)Import(tbl);
var eiChi = cata.GetAllExtractionInformation()[0];
eiChi.IsExtractionIdentifier = true;
eiChi.SaveToDatabase();
// create a cohort config
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "mycic");
cic.QueryCachingServer_ID = externalDatabaseServer.ID;
cic.SaveToDatabase();
// this special Catalogue will be detected by ExamplePluginCohortCompiler and interpreted as an API call
var myApi = new Catalogue(CatalogueRepository, ExamplePluginCohortCompiler.ExampleAPIName);
// add it to the cohort config
cic.CreateRootContainerIfNotExists();
// Add the regular table
var cmd = new ExecuteCommandAddCatalogueToCohortIdentificationSetContainer(activator, new CatalogueCombineable(cata), cic.RootCohortAggregateContainer);
Assert.IsFalse(cmd.IsImpossible, cmd.ReasonCommandImpossible);
cmd.Execute();
var regularAggregate = cmd.AggregateCreatedIfAny;
// The thing we are wanting to test - creating a use of the API as a patient index table
var cmd2 = new ExecuteCommandAddCatalogueToCohortIdentificationAsPatientIndexTable(
activator, new CatalogueCombineable(myApi), cic);
Assert.IsFalse(cmd2.IsImpossible, cmd2.ReasonCommandImpossible);
cmd2.Execute();
var joinables = cic.GetAllJoinables();
Assert.AreEqual(1, joinables.Length);
// make them join one another
new JoinableCohortAggregateConfigurationUse(CatalogueRepository, regularAggregate, joinables[0]);
// run the cic again
var source = new CohortIdentificationConfigurationSource();
source.PreInitialize(cic, new ThrowImmediatelyDataLoadEventListener());
var result = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
Assert.AreEqual(1, result.Rows.Count);
}
public void Join_PatientIndexTable_ThenShipToCacheForSets(DatabaseType dbType)
{
/*
* Server1 Server 2
* _____________ _________
* |Biochemistry| → | Cache | (cache is still populated but not used in the resulting join).
*
* ↓ join ↓ (do not use cache)
* _______________________
* | Hospital Admissions 1| → results1
*
* EXCEPT
* _______________________
* | Hospital Admissions 2| → results 2
* ↓ result = 0 records
*/
//get the data database
var db = GetCleanedServer(dbType);
//create the cache on the other server type (either Sql Server or Oracle) since MySql can't do EXCEPT or UNION etc)
var dbCache =
GetCleanedServer(Enum.GetValues(typeof(DatabaseType)).Cast <DatabaseType>().First(t =>
t != dbType && t != DatabaseType.MySql));
ExternalDatabaseServer cache = CreateCache(dbCache);
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var joinable = SetupPatientIndexTable(db, people, r, cic);
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache?.ID;
cic.SaveToDatabase();
var hospitalAdmissions = SetupPatientIndexTableUser(db, people, r, cic, joinable);
var hospitalAdmissions2 = SetupAggregateConfiguration(db, people, r, cic);
var root = cic.RootCohortAggregateContainer;
root.AddChild(hospitalAdmissions, 0);
root.AddChild(hospitalAdmissions2, 1);
root.Name = "EXCEPT";
root.Operation = SetOperation.EXCEPT;
root.SaveToDatabase();
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
AssertNoErrors(compiler);
Assert.IsTrue(compiler.Tasks.Any(t => t.Key.GetCachedQueryUseCount().Equals("2/2")), "Expected cache to be used for both top level operations in the EXCEPT");
}
//[TestCase(DatabaseType.Oracle)] //Oracle FAnsi doesn't currently support parameters
public void Test_SingleServer_WithTwoParameters(DatabaseType dbType, bool useCache)
{
/*
* Server1 Server2
* ____________________ _____________________
* |HospitalAdmissions | → | Cache |
* @date_of_max
* ____________________ ↗
* |HospitalAdmissions |
* @date_of_max
*
* (has different value so rename operations come into effect)
*/
var server1 = GetCleanedServer(dbType);
var server2 = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
var cache = useCache ? CreateCache(server2): null;
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var ac1 = SetupAggregateConfigurationWithFilter(server1, people, r, cic, true, "@date_of_max", "'2001-01-01'");
var ac2 = SetupAggregateConfigurationWithFilter(server1, people, r, cic, true, "@date_of_max", "'2005-01-01'");
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache?.ID;
cic.SaveToDatabase();
var root = cic.RootCohortAggregateContainer;
root.AddChild(ac1, 0);
root.AddChild(ac2, 1);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
AssertNoErrors(compiler);
//each container on it's own ran with the normal SQL
AssertNoErrors(compiler, ac1, "@date_of_max='2001-01-01'");
AssertNoErrors(compiler, ac2, "@date_of_max='2005-01-01'");
if (useCache)
{
//the root container run with dual cache fetch and no parameters
AssertNoErrors(compiler, root, "IndexedExtractionIdentifierList");
AssertCacheUsed(compiler, root, "2/2");
}
else
{
//the root container ran with a rename operations (no cache available)
AssertNoErrors(compiler, root, "@date_of_max='2001-01-01'", "@date_of_max_2='2005-01-01'");
AssertCacheUsed(compiler, root, "0/2");
}
}
public void Test_SingleServerPatientIndexTable_WithTwoParameters(DatabaseType dbType, bool useSameName, bool useCache)
{
/*
* Server1 (Also Server1 - if useCache is true)
* ______________________________ __________________________
* | Patient Index Table | → | Cache |
* | (NA by date ) | ↗
* @date_of_max '2001-01-01'
* ↗
* JOIN (should use cache)
* ___________________________ ↗
* | Hospitalised after NA (ac) |
* @date_of_max (or @maximum) '2005-01-01'
*
* (has different value so rename operations come into effect)
*/
var server1 = GetCleanedServer(dbType);
var cache = useCache? CreateCache(server1): null;
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var patientIndexTable = SetupPatientIndexTableWithFilter(server1, people, r, cic, true, "@date_of_max", "'2001-01-01'");
var ac = SetupPatientIndexTableUserWithFilter(server1, people, r, cic, patientIndexTable, true,
useSameName ? "@date_of_max": "@maximum", "'2005-01-01'");
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache?.ID;
cic.SaveToDatabase();
var root = cic.RootCohortAggregateContainer;
root.AddChild(ac, 0);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
if (useSameName)
{
AssertCrashed(compiler, ac, "PatientIndexTables cannot have parameters with the same name as their users. Offending parameter(s) were @date_of_max");
}
else
{
if (useCache)
{
//we should hit up the cache for the interior of the query and therefore not need the parameter
AssertNoErrors(compiler, ac, "@maximum='2005-01-01'", "JoinableInceptionQuery_AggregateConfiguration", "AdmissionDate. < @maximum");
AssertCacheUsed(compiler, root, "1/1");
}
else
{
AssertNoErrors(compiler, ac, "@date_of_max='2001-01-01'", "@maximum='2005-01-01'", "SampleDate. < @date_of_max", "AdmissionDate. < @maximum");
}
AssertNoErrors(compiler);
}
}
public void Test_EXCEPT_TwoAggregates(DatabaseType dbType)
{
/*
* Server1
* _____________________
* |HospitalAdmissions x2|
* ↓ both run into ↓
* ___________________
* | Cache |
*
*
* HospitalAdmissions
* EXCEPT
* HospitalAdmissions (copy)
* = 0
*/
var db = GetCleanedServer(dbType);
var cache = CreateCache(db);
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var ac1 = SetupAggregateConfiguration(db, people, r, cic);
var ac2 = SetupAggregateConfiguration(db, people, r, cic);
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache.ID;
cic.SaveToDatabase();
var root = cic.RootCohortAggregateContainer;
root.Operation = SetOperation.EXCEPT;
root.Name = "EXCEPT";
root.SaveToDatabase();
root.AddChild(ac1, 0);
root.AddChild(ac2, 1);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
if (dbType == DatabaseType.MySql)
{
var crashed = compiler.Tasks.Single(t => t.Key.State == CompilationState.Crashed);
StringAssert.Contains("INTERSECT / UNION / EXCEPT are not supported by MySql", crashed.Key.CrashMessage.Message);
return;
}
AssertNoErrors(compiler);
Assert.AreEqual(compiler.Tasks.Single(t => t.Value != null && t.Value.IsResultsForRootContainer).Key.FinalRowCount, 0);
Assert.Greater(compiler.Tasks.Single(t => t.Key is AggregationTask at && at.Aggregate.Equals(ac1)).Key.FinalRowCount, 0); //both ac should have the same total
Assert.Greater(compiler.Tasks.Single(t => t.Key is AggregationTask at && at.Aggregate.Equals(ac2)).Key.FinalRowCount, 0); // that is not 0
Assert.IsTrue(compiler.Tasks.Any(t => t.Key.GetCachedQueryUseCount().Equals("2/2")), "Expected EXCEPT container to use the cache");
}