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