public void Cleanup()
{
container1.DeleteInDatabase();
if (aggregate1 != null)
{
aggregate1.DeleteInDatabase();
}
if (aggregate2 != null)
{
aggregate2.DeleteInDatabase();
}
if (aggregate3 != null)
{
aggregate3.DeleteInDatabase();
}
if (cohortIdentificationConfiguration != null)
{
cohortIdentificationConfiguration.DeleteInDatabase();
}
if (testData != null)
{
testData.DeleteCatalogue();
}
}
public void TearDown()
{
cic.DeleteInDatabase();
acCohort.DeleteInDatabase();
acDataset.DeleteInDatabase();
t.DeleteInDatabase();
c.DeleteInDatabase();
}
public void TestOrphanCic()
{
var memory = new MemoryDataExportRepository();
var cic = new CohortIdentificationConfiguration(memory, "Mycic");
var p = new Project(memory, "my proj");
p.AssociateWithCohortIdentification(cic);
//fetch the instance
var cicAssoc = memory.GetAllObjects <ProjectCohortIdentificationConfigurationAssociation>().Single();
//relationship from p should resolve to the association link
Assert.AreEqual(cicAssoc, p.ProjectCohortIdentificationConfigurationAssociations[0]);
//relationship from p should resolve to the cic
Assert.AreEqual(cic, p.GetAssociatedCohortIdentificationConfigurations()[0]);
//in order to make it an orphan we have to suppress the system default behaviour of cascading across the deletion
var obscure = memory.ObscureDependencyFinder as CatalogueObscureDependencyFinder;
if (obscure != null)
{
obscure.OtherDependencyFinders.Clear();
}
//make the assoc an orphan
cic.DeleteInDatabase();
cicAssoc.ClearAllInjections();
//assoc should still exist
Assert.AreEqual(cicAssoc, p.ProjectCohortIdentificationConfigurationAssociations[0]);
Assert.IsNull(p.ProjectCohortIdentificationConfigurationAssociations[0].CohortIdentificationConfiguration);
//relationship from p should resolve to the cic
Assert.IsEmpty(p.GetAssociatedCohortIdentificationConfigurations());
//error should be reported in top right of program
var ex = Assert.Throws <Exception>(() => new DataExportChildProvider(new RepositoryProvider(memory), null, new ThrowImmediatelyCheckNotifier(), null));
StringAssert.IsMatch(@"Failed to find Associated Cohort Identification Configuration with ID \d+ which was supposed to be associated with my proj", ex.Message);
//but UI should still respond
var childProvider = new DataExportChildProvider(new RepositoryProvider(memory), null, new IgnoreAllErrorsCheckNotifier(), null);
//the orphan cic should not appear in the tree view under Project=>Cohorts=>Associated Cics
var cohorts = childProvider.GetChildren(p).OfType <ProjectCohortsNode>().Single();
var cics = childProvider.GetChildren(cohorts).OfType <ProjectCohortIdentificationConfigurationAssociationsNode>().First();
Assert.IsEmpty(childProvider.GetChildren(cics));
}
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 EndToEndTest()
{
var cohortDatabaseNameWillBe = TestDatabaseNames.GetConsistentName("TbvCohort");
_discoveredCohortDatabase = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(cohortDatabaseNameWillBe);
//cleanup
if (_discoveredCohortDatabase.Exists())
{
_discoveredCohortDatabase.Drop();
}
//create a normal catalogue
CreateANormalCatalogue();
//create a cohort database using wizard
CreateNewCohortDatabaseWizard cohortDatabaseWizard = new CreateNewCohortDatabaseWizard(_discoveredCohortDatabase, CatalogueRepository, DataExportRepository, false);
_externalCohortTable = cohortDatabaseWizard.CreateDatabase(
new PrivateIdentifierPrototype(_nonTvfExtractionIdentifier)
, new ThrowImmediatelyCheckNotifier());
//create a table valued function
CreateTvfCatalogue(cohortDatabaseNameWillBe);
//Test 1
TestThatQueryBuilderWithoutParametersBeingSetThrowsQueryBuildingException();
PopulateCohortDatabaseWithRecordsFromNonTvfCatalogue();
//Test 2
TestWithParameterValueThatRowsAreReturned();
//Test 3
TestUsingTvfForAggregates();
//Test 4
TestAddingTvfToCIC();
//Test 5
TestDataExportOfTvf();
//tear down
DataExportRepository.GetAllObjects <ExtractableCohort>().Single().DeleteInDatabase();
_externalCohortTable.DeleteInDatabase();
_database.ExpectTable("NonTVFTable").Drop();
_database.ExpectTableValuedFunction("GetTopXRandom").Drop();
//delete global parameter
((AnyTableSqlParameter)_aggregate.GetAllParameters().Single()).DeleteInDatabase();
//delete aggregate
_aggregate.DeleteInDatabase();
((AnyTableSqlParameter)_cicAggregate.GetAllParameters().Single()).DeleteInDatabase();
//delete aggregate
_cicAggregate.DeleteInDatabase();
//get rid of the cohort identification configuration
_cic.DeleteInDatabase();
_pipe.DeleteInDatabase();
//get rid of the cohort database
_discoveredCohortDatabase.Drop();
_nonTvfCatalogue.DeleteInDatabase();
_nonTvfTableInfo.DeleteInDatabase();
_tvfCatalogue.DeleteInDatabase();
_tvfTableInfo.DeleteInDatabase();
}
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();
}
}