public void QueryBuilderTest() { var builder = new CohortQueryBuilder(aggregate1, null); //make aggregate 2 a joinable var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2); joinable2.AddUser(aggregate1); Console.WriteLine(builder.SQL); try { using (var con = (SqlConnection)Database.Server.GetConnection()) { con.Open(); var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader(); //can read at least one row Assert.IsTrue(dbReader.Read()); } string expectedTableAlias = "ix" + joinable2.ID; //after joinables Assert.AreEqual( string.Format( @"/*cic_{1}_UnitTestAggregate1*/ SELECT distinct [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] FROM [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData] LEFT Join ( /*cic_{1}_UnitTestAggregate2*/ SELECT distinct [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] FROM [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData] ){0} on [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] = {0}.chi", expectedTableAlias, cohortIdentificationConfiguration.ID), builder.SQL); } finally { joinable2.Users[0].DeleteInDatabase(); joinable2.DeleteInDatabase(); } }
public void CreateUsers_SelfReferrential() { var joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1); try { var ex = Assert.Throws <NotSupportedException>(() => joinable.AddUser(aggregate1)); Assert.AreEqual("Cannot configure AggregateConfiguration UnitTestAggregate1 as a Join user to itself!", ex.Message); } finally { joinable.DeleteInDatabase(); } }
public void CreateUsers_DuplicateUser() { var joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1); try { joinable.AddUser(aggregate2); Assert.Throws <SqlException>(() => joinable.AddUser(aggregate2)); } finally { joinable.DeleteInDatabase(); } }
public void CreateJoinable_AddTwice() { //delete the first dimension (chi) var join1 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1); try { var ex = Assert.Throws <SqlException>(() => new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1)); Assert.IsTrue(ex.Message.Contains("ix_eachAggregateCanOnlyBeJoinableOnOneProject")); } finally { join1.DeleteInDatabase(); } }
public void CreateUsers_ToAnyOtherJoinable() { var joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1); var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2); try { var ex = Assert.Throws <NotSupportedException>(() => joinable.AddUser(aggregate2)); Assert.AreEqual("Cannot add user UnitTestAggregate2 because that AggregateConfiguration is itself a JoinableCohortAggregateConfiguration", ex.Message); } finally { joinable.DeleteInDatabase(); joinable2.DeleteInDatabase(); } }
public void CreateUsers_ToNoExtractionIdentifierTable() { var joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1); aggregate2.AggregateDimensions.First().DeleteInDatabase(); aggregate2.ClearAllInjections(); try { var ex = Assert.Throws <NotSupportedException>(() => joinable.AddUser(aggregate2)); Assert.AreEqual("Cannot configure AggregateConfiguration UnitTestAggregate2 as join user because it does not contain exactly 1 IsExtractionIdentifier dimension", ex.Message); } finally { joinable.DeleteInDatabase(); } }
public void CreateJoinable() { JoinableCohortAggregateConfiguration joinable = null; try { joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1); Assert.AreEqual(joinable.CohortIdentificationConfiguration_ID, cohortIdentificationConfiguration.ID); Assert.AreEqual(joinable.AggregateConfiguration_ID, aggregate1.ID); } finally { if (joinable != null) { joinable.DeleteInDatabase(); } } }
public void CreateUsers() { JoinableCohortAggregateConfiguration joinable = null; try { joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1); joinable.AddUser(aggregate2); Assert.IsTrue(joinable.Users.Length == 1); Assert.AreEqual(aggregate2, joinable.Users[0].AggregateConfiguration); } finally { if (joinable != null) { joinable.DeleteInDatabase(); } } }
public void JoinablesWithCache() { string queryCachingDatabaseName = To.GetRuntimeName(); _queryCachingDatabase = To; var builder = new CohortQueryBuilder(aggregate1, null); //make aggregate 2 a joinable var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2); joinable2.AddUser(aggregate1); //make aggregate 2 have an additional column (dtCreated) var anotherCol = aggregate2.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => e.GetRuntimeName().Equals("dtCreated")); aggregate2.AddDimension(anotherCol); MasterDatabaseScriptExecutor scripter = new MasterDatabaseScriptExecutor(_queryCachingDatabase); scripter.CreateAndPatchDatabase(new QueryCachingPatcher(), new AcceptAllCheckNotifier()); var queryCachingDatabaseServer = new ExternalDatabaseServer(CatalogueRepository, queryCachingDatabaseName, null); queryCachingDatabaseServer.SetProperties(_queryCachingDatabase); //make the builder use the query cache we just set SetUp builder.CacheServer = queryCachingDatabaseServer; try { var builderForCaching = new CohortQueryBuilder(aggregate2, null, true); var cacheDt = new DataTable(); using (SqlConnection con = (SqlConnection)Database.Server.GetConnection()) { con.Open(); SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(builderForCaching.SQL, con)); da.Fill(cacheDt); } var cacheManager = new CachedAggregateConfigurationResultsManager(queryCachingDatabaseServer); cacheManager.CommitResults(new CacheCommitJoinableInceptionQuery(aggregate2, builderForCaching.SQL, cacheDt, null, 30)); try { Console.WriteLine(builder.SQL); using (var con = (SqlConnection)Database.Server.GetConnection()) { con.Open(); var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader(); //can read at least one row Assert.IsTrue(dbReader.Read()); } string expectedTableAlias = "ix" + joinable2.ID; //after joinables Assert.AreEqual( CollapseWhitespace( string.Format( @"/*cic_{2}_UnitTestAggregate1*/ SELECT distinct [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] FROM [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData] LEFT Join ( /*Cached:cic_{2}_UnitTestAggregate2*/ select * from [{3}]..[JoinableInceptionQuery_AggregateConfiguration{1}] ){0} on [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] = {0}.chi", expectedTableAlias, //{0} aggregate2.ID, //{1} cohortIdentificationConfiguration.ID, //{2} queryCachingDatabaseName) //{3} ), CollapseWhitespace(builder.SQL)); } finally { joinable2.Users[0].DeleteInDatabase(); joinable2.DeleteInDatabase(); } } finally { queryCachingDatabaseServer.DeleteInDatabase(); DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(queryCachingDatabaseName).Drop(); } }
public void QueryBuilderTest_JoinableCloning() { var anotherCol = aggregate2.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => e.GetRuntimeName().Equals("dtCreated")); aggregate2.AddDimension(anotherCol); //make aggregate 2 a joinable var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2); joinable2.AddUser(aggregate1); string expectedTableAlias = "ix" + joinable2.ID; var filterContainer1 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filterContainer2 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filter1 = new AggregateFilter(CatalogueRepository, "Within 1 year of event", filterContainer1); var filter2 = new AggregateFilter(CatalogueRepository, "DateAfter2001", filterContainer2); filter1.WhereSQL = string.Format("ABS(DATEDIFF(year, {0}.dtCreated, [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].dtCreated)) <= 1", expectedTableAlias); filter1.SaveToDatabase(); filter2.WhereSQL = "dtCreated > '2001-01-01'"; filter2.SaveToDatabase(); aggregate1.RootFilterContainer_ID = filterContainer1.ID; aggregate1.SaveToDatabase(); aggregate2.RootFilterContainer_ID = filterContainer2.ID; aggregate2.SaveToDatabase(); //add the first aggregate to the configuration rootcontainer.AddChild(aggregate1, 1); var globalParameter = new AnyTableSqlParameter(CatalogueRepository, cohortIdentificationConfiguration, "DECLARE @fish varchar(50)"); globalParameter.Comment = "Comments for the crazies"; globalParameter.Value = "'fishes'"; globalParameter.SaveToDatabase(); var builder = new CohortQueryBuilder(cohortIdentificationConfiguration); try { var clone = cohortIdentificationConfiguration.CreateClone(new ThrowImmediatelyCheckNotifier()); var cloneBuilder = new CohortQueryBuilder(clone); string origSql = builder.SQL; string cloneOrigSql = cloneBuilder.SQL; Console.WriteLine("//////////////////////////////////////////////VERBATIM//////////////////////////////////////////////"); Console.WriteLine(origSql); Console.WriteLine(cloneOrigSql); Console.WriteLine("//////////////////////////////////////////////END VERBATIM//////////////////////////////////////////////"); var builderSql = Regex.Replace(Regex.Replace(origSql, "cic_[0-9]+_", ""), "ix[0-9]+", "ix"); var cloneBuilderSql = Regex.Replace(Regex.Replace(cloneOrigSql, "cic_[0-9]+_", ""), "ix[0-9]+", "ix").Replace("(Clone)", "");//get rid of explicit ix53 etc for the comparison Console.WriteLine("//////////////////////////////////////////////TEST COMPARISON IS//////////////////////////////////////////////"); Console.WriteLine(builderSql); Console.WriteLine(cloneBuilderSql); Console.WriteLine("//////////////////////////////////////////////END COMPARISON//////////////////////////////////////////////"); Assert.AreEqual(builderSql, cloneBuilderSql); ////////////////Cleanup Database////////////////////////////// //find the WHERE logic too var containerClone = clone.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively() //get all the aggregates .Union(clone.GetAllJoinables().Select(j => j.AggregateConfiguration)) //including the joinables .Where(a => a.RootFilterContainer_ID != null) //that have WHERE sql .Select(ag => ag.RootFilterContainer); //grab their containers so we can clean them SetUp ((IDeleteable)clone.GetAllParameters()[0]).DeleteInDatabase(); clone.DeleteInDatabase(); //delete the WHERE logic too foreach (AggregateFilterContainer c in containerClone) { c.DeleteInDatabase(); } } finally { rootcontainer.RemoveChild(aggregate1); filter1.DeleteInDatabase(); filter2.DeleteInDatabase(); filterContainer1.DeleteInDatabase(); filterContainer2.DeleteInDatabase(); joinable2.Users[0].DeleteInDatabase(); joinable2.DeleteInDatabase(); globalParameter.DeleteInDatabase(); } }
public void QueryBuilderTest_AdditionalColumn() { var anotherCol = aggregate2.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => e.GetRuntimeName().Equals("dtCreated")); aggregate2.AddDimension(anotherCol); //make aggregate 2 a joinable var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2); joinable2.AddUser(aggregate1); string expectedTableAlias = "ix" + joinable2.ID; var filterContainer1 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filterContainer2 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filter1 = new AggregateFilter(CatalogueRepository, "Within 1 year of event", filterContainer1); var filter2 = new AggregateFilter(CatalogueRepository, "DateAfter2001", filterContainer2); filter1.WhereSQL = string.Format("ABS(DATEDIFF(year, {0}.dtCreated, [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].dtCreated)) <= 1", expectedTableAlias); filter1.SaveToDatabase(); filter2.WhereSQL = "dtCreated > '2001-01-01'"; filter2.SaveToDatabase(); aggregate1.RootFilterContainer_ID = filterContainer1.ID; aggregate1.SaveToDatabase(); aggregate2.RootFilterContainer_ID = filterContainer2.ID; aggregate2.SaveToDatabase(); var builder = new CohortQueryBuilder(aggregate1, null); Console.WriteLine(builder.SQL); try { using (var con = (SqlConnection)Database.Server.GetConnection()) { con.Open(); var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader(); //can read at least one row Assert.IsTrue(dbReader.Read()); } //after joinables Assert.AreEqual( CollapseWhitespace( string.Format( @"/*cic_{1}_UnitTestAggregate1*/ SELECT distinct [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] FROM [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData] LEFT Join ( /*cic_{1}_UnitTestAggregate2*/ SELECT distinct [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi], [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[dtCreated] FROM [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData] WHERE ( /*DateAfter2001*/ dtCreated > '2001-01-01' ) ){0} on [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] = {0}.chi WHERE ( /*Within 1 year of event*/ ABS(DATEDIFF(year, {0}.dtCreated, [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].dtCreated)) <= 1 )", expectedTableAlias, cohortIdentificationConfiguration.ID)), CollapseWhitespace(builder.SQL)); } finally { filter1.DeleteInDatabase(); filter2.DeleteInDatabase(); filterContainer1.DeleteInDatabase(); filterContainer2.DeleteInDatabase(); joinable2.Users[0].DeleteInDatabase(); joinable2.DeleteInDatabase(); } }
public void TestCompilerAddAllTasks(TestCompilerAddAllTasksTestCase testCase, bool includeSubcontainers) { var aggregate4 = new AggregateConfiguration(CatalogueRepository, testData.catalogue, "UnitTestAggregate4"); aggregate4.CountSQL = null; aggregate4.SaveToDatabase(); new AggregateDimension(CatalogueRepository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate4); var aggregate5 = new AggregateConfiguration(CatalogueRepository, testData.catalogue, "UnitTestAggregate5"); aggregate5.CountSQL = null; aggregate5.SaveToDatabase(); new AggregateDimension(CatalogueRepository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate5); var joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate5); try { //EXCEPT //Aggregate 1 //UNION //Aggregate 3 //Aggregate 4 //Aggregate 2 //Joinable:aggregate5 (patient index table, the other Aggregates could JOIN to this) CohortCompiler compiler = new CohortCompiler(cohortIdentificationConfiguration); rootcontainer.AddChild(aggregate1, 1); rootcontainer.AddChild(container1); container1.Order = 2; container1.SaveToDatabase(); rootcontainer.AddChild(aggregate2, 3); container1.AddChild(aggregate3, 1); container1.AddChild(aggregate4, 2); cohortIdentificationConfiguration.RootCohortAggregateContainer_ID = rootcontainer.ID; cohortIdentificationConfiguration.SaveToDatabase(); //The bit we are testing List <ICompileable> tasks; switch (testCase) { case TestCompilerAddAllTasksTestCase.CIC: tasks = compiler.AddAllTasks(includeSubcontainers); Assert.AreEqual(joinable, tasks.OfType <JoinableTask>().Single().Joinable); //should be a single joinable Assert.AreEqual(includeSubcontainers?7:6, tasks.Count); //all joinables, aggregates and root container break; case TestCompilerAddAllTasksTestCase.RootContainer: tasks = compiler.AddTasksRecursively(new ISqlParameter[0], cohortIdentificationConfiguration.RootCohortAggregateContainer, includeSubcontainers); Assert.AreEqual(includeSubcontainers?6:5, tasks.Count); //all aggregates and root container (but not joinables) break; case TestCompilerAddAllTasksTestCase.Subcontainer: tasks = compiler.AddTasksRecursively(new ISqlParameter[0], container1, includeSubcontainers); Assert.AreEqual(includeSubcontainers?3:2, tasks.Count); //subcontainer and it's aggregates break; default: throw new ArgumentOutOfRangeException("testCase"); } rootcontainer.RemoveChild(aggregate1); rootcontainer.RemoveChild(aggregate2); container1.RemoveChild(aggregate3); container1.RemoveChild(aggregate4); container1.MakeIntoAnOrphan(); } finally { aggregate4.DeleteInDatabase(); joinable.DeleteInDatabase(); aggregate5.DeleteInDatabase(); } }