private void DestroyParameters() { container1.GetFilters()[0].DeleteInDatabase(); container2.GetFilters()[0].DeleteInDatabase(); container1.DeleteInDatabase(); container2.DeleteInDatabase(); }
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 TestGettingAggregateSQLFromEntirity_Filter_IsDisabled() { CohortQueryBuilder builder = new CohortQueryBuilder(cohortIdentificationConfiguration); //setup a filter (all filters must be in a container so the container is a default AND container) var AND1 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filter1_1 = new AggregateFilter(CatalogueRepository, "filter1_1", AND1); var filter1_2 = new AggregateFilter(CatalogueRepository, "filter1_2", AND1); var AND2 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filter2_1 = new AggregateFilter(CatalogueRepository, "filter2_1", AND2); var filter2_2 = new AggregateFilter(CatalogueRepository, "filter2_2", AND2); //Filters must belong to containers BEFORE parameter creation //Make aggregate1 use the filter set we just setup aggregate1.RootFilterContainer_ID = AND1.ID; aggregate1.SaveToDatabase(); //Make aggregate3 use the other filter set we just setup aggregate2.RootFilterContainer_ID = AND2.ID; aggregate2.SaveToDatabase(); //set the order so that 2 comes before 1 rootcontainer.AddChild(aggregate2, 1); rootcontainer.AddChild(aggregate1, 5); filter2_2.IsDisabled = true; filter2_2.SaveToDatabase(); //give the filter an implicit parameter requiring bit of SQL foreach (var filter in new IFilter[] { filter1_1, filter1_2, filter2_1, filter2_2 }) { filter.WhereSQL = "@bob = 'bob'"; filter.SaveToDatabase(); //get it to create the parameters for us new ParameterCreator(new AggregateFilterFactory(CatalogueRepository), null, null).CreateAll(filter, null); //get the parameter it just created, set it's value and save it var param = (AggregateFilterParameter)filter.GetAllParameters().Single(); param.Value = "'Boom!'"; param.ParameterSQL = "DECLARE @bob AS varchar(10);"; //change the values of the parameters if (filter.Equals(filter2_1) || Equals(filter, filter2_2)) { param.Value = "'Grenades Are Go'"; } param.SaveToDatabase(); } Console.WriteLine(builder.SQL); try { Assert.AreEqual( CollapseWhitespace( string.Format( @"DECLARE @bob AS varchar(10); SET @bob='Grenades Are Go'; DECLARE @bob_2 AS varchar(10); SET @bob_2='Boom!'; ( /*cic_{0}_UnitTestAggregate2*/ SELECT distinct [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] FROM [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData] WHERE ( /*filter2_1*/ @bob = 'bob' ) EXCEPT /*cic_{0}_UnitTestAggregate1*/ SELECT distinct [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] FROM [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData] WHERE ( /*filter1_1*/ @bob_2 = 'bob' AND /*filter1_2*/ @bob_2 = 'bob' ) ) ", cohortIdentificationConfiguration.ID)), CollapseWhitespace(builder.SQL)); } finally { filter2_2.IsDisabled = false; filter2_2.SaveToDatabase(); rootcontainer.RemoveChild(aggregate2); rootcontainer.RemoveChild(aggregate1); filter1_1.DeleteInDatabase(); filter1_2.DeleteInDatabase(); filter2_1.DeleteInDatabase(); filter2_2.DeleteInDatabase(); AND1.DeleteInDatabase(); AND2.DeleteInDatabase(); } }
public void TestGettingAggregateSQLFromEntirity_IncludingParametersAtTop() { CohortQueryBuilder builder = new CohortQueryBuilder(cohortIdentificationConfiguration); //setup a filter (all filters must be in a container so the container is a default AND container) var AND = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filter = new AggregateFilter(CatalogueRepository, "hithere", AND); //give the filter an implicit parameter requiring bit of SQL filter.WhereSQL = "1=@abracadabra"; filter.SaveToDatabase(); //Make aggregate1 use the filter we just setup (required to happen before parameter creator gets hit because otherwise it won't know the IFilter DatabaseType because IFilter is an orphan at the moment) aggregate1.RootFilterContainer_ID = AND.ID; aggregate1.SaveToDatabase(); //get it to create the parameters for us new ParameterCreator(new AggregateFilterFactory(CatalogueRepository), null, null).CreateAll(filter, null); //get the parameter it just created, set it's value and save it var param = (AggregateFilterParameter)filter.GetAllParameters().Single(); param.Value = "1"; param.ParameterSQL = "DECLARE @abracadabra AS int;"; param.SaveToDatabase(); //set the order so that 2 comes before 1 rootcontainer.AddChild(aggregate2, 1); rootcontainer.AddChild(aggregate1, 5); try { Assert.AreEqual( CollapseWhitespace( string.Format( @"DECLARE @abracadabra AS int; SET @abracadabra=1; ( /*cic_{0}_UnitTestAggregate2*/ SELECT distinct [" + _scratchDatabaseName + @"]..[BulkData].[chi] FROM [" + _scratchDatabaseName + @"]..[BulkData] EXCEPT /*cic_{0}_UnitTestAggregate1*/ SELECT distinct [" + _scratchDatabaseName + @"]..[BulkData].[chi] FROM [" + _scratchDatabaseName + @"]..[BulkData] WHERE ( /*hithere*/ 1=@abracadabra ) ) ", cohortIdentificationConfiguration.ID)) , CollapseWhitespace(builder.SQL)); CohortQueryBuilder builder2 = new CohortQueryBuilder(aggregate1, null); Assert.AreEqual( CollapseWhitespace( string.Format( @"DECLARE @abracadabra AS int; SET @abracadabra=1; /*cic_{0}_UnitTestAggregate1*/ SELECT distinct [" + _scratchDatabaseName + @"]..[BulkData].[chi] FROM [" + _scratchDatabaseName + @"]..[BulkData] WHERE ( /*hithere*/ 1=@abracadabra )", cohortIdentificationConfiguration.ID)), CollapseWhitespace(builder2.SQL)); string selectStar = new CohortQueryBuilder(aggregate1, null).GetDatasetSampleSQL(); Assert.AreEqual( CollapseWhitespace( string.Format( @"DECLARE @abracadabra AS int; SET @abracadabra=1; /*cic_{0}_UnitTestAggregate1*/ SELECT TOP 1000 * FROM [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData] WHERE ( /*hithere*/ 1=@abracadabra )" , cohortIdentificationConfiguration.ID)), CollapseWhitespace(selectStar)); } finally { filter.DeleteInDatabase(); AND.DeleteInDatabase(); rootcontainer.RemoveChild(aggregate1); rootcontainer.RemoveChild(aggregate2); } }
public void CloneChildWithFilter_IDsDifferent() { //aggregate 1 is now a normal non cohort aggregate var container = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.OR); aggregate1.CountSQL = "count(*)"; aggregate1.RootFilterContainer_ID = container.ID; aggregate1.SaveToDatabase(); //with filters var filter = new AggregateFilter(CatalogueRepository, "MyFilter", container); filter.WhereSQL = "sex=@sex"; //and parameters new ParameterCreator(new AggregateFilterFactory(CatalogueRepository), null, null).CreateAll(filter, null); filter.SaveToDatabase(); var param = (AggregateFilterParameter)filter.GetAllParameters().Single(); param.Value = "'M'"; param.SaveToDatabase(); //we are importing this graph aggregate as a new cohort identification aggregate var clone = cohortIdentificationConfiguration.ImportAggregateConfigurationAsIdentifierList(aggregate1, null); //since its a cohort aggregate it should be identical to the origin Aggregate except it has a different ID and no count SQL Assert.AreEqual(clone.CountSQL, null); //get the original sql var aggregateSql = aggregate1.GetQueryBuilder().SQL; try { Assert.AreNotEqual(clone.ID, aggregate1.ID); Assert.AreNotEqual(clone.RootFilterContainer_ID, aggregate1.RootFilterContainer_ID); var cloneContainer = clone.RootFilterContainer; var cloneFilter = cloneContainer.GetFilters().Single(); Assert.AreNotEqual(cloneContainer.ID, container.ID); Assert.AreNotEqual(cloneFilter.ID, filter.ID); var cloneParameter = (AggregateFilterParameter)cloneFilter.GetAllParameters().Single(); Assert.AreNotEqual(cloneParameter.ID, param.ID); //it has a different ID and is part of an aggregate filter container (It is presumed to be involved with cohort identification cohortIdentificationConfiguration) which means it will be called cic_X_ string cohortAggregateSql = new CohortQueryBuilder(clone, null, null).SQL; //the basic aggregate has the filter, parameter and group by Assert.AreEqual(CollapseWhitespace( string.Format( @"DECLARE @sex AS varchar(50); SET @sex='M'; /*cic_{0}_UnitTestAggregate1*/ SELECT [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData].[chi], count(*) FROM [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData] WHERE ( /*MyFilter*/ sex=@sex ) group by [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData].[chi] order by [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData].[chi]", cohortIdentificationConfiguration.ID)), CollapseWhitespace(aggregateSql)); //the expected differences are //1. should not have the count //2. should not have the group by //3. should be marked with the cic comment with the ID matching the CohortIdentificationConfiguration.ID //4. should have a distinct on the identifier column Assert.AreEqual( @"DECLARE @sex AS varchar(50); SET @sex='M'; /*cic_" + cohortIdentificationConfiguration.ID + @"_UnitTestAggregate1*/ SELECT distinct [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData].[chi] FROM [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData] WHERE ( /*MyFilter*/ sex=@sex )", cohortAggregateSql); clone.RootFilterContainer.DeleteInDatabase(); container.DeleteInDatabase(); } finally { clone.DeleteInDatabase(); } }
public void CohortIdentificationConfiguration_CloneEntirely() { //set the order so that 2 comes before 1 rootcontainer.AddChild(aggregate1, 5); rootcontainer.AddChild(container1); container1.AddChild(aggregate2, 1); container1.AddChild(aggregate3, 2); //create a filter too var container = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.OR); aggregate1.RootFilterContainer_ID = container.ID; aggregate1.SaveToDatabase(); var filter = new AggregateFilter(CatalogueRepository, "MyFilter", container); filter.WhereSQL = "sex=@sex"; new ParameterCreator(new AggregateFilterFactory(CatalogueRepository), null, null).CreateAll(filter, null); filter.SaveToDatabase(); //with a parameter too var param = (AggregateFilterParameter)filter.GetAllParameters().Single(); param.Value = "'M'"; param.SaveToDatabase(); cohortIdentificationConfiguration.RootCohortAggregateContainer_ID = rootcontainer.ID; cohortIdentificationConfiguration.SaveToDatabase(); try { var clone = cohortIdentificationConfiguration.CreateClone(new ThrowImmediatelyCheckNotifier()); //the objects should be different Assert.AreNotEqual(cohortIdentificationConfiguration.ID, clone.ID); Assert.IsTrue(clone.Name.EndsWith("(Clone)")); Assert.AreNotEqual(clone.RootCohortAggregateContainer_ID, cohortIdentificationConfiguration.RootCohortAggregateContainer_ID); Assert.IsNotNull(clone.RootCohortAggregateContainer_ID); var beforeSQL = new CohortQueryBuilder(cohortIdentificationConfiguration, null).SQL; var cloneSQL = new CohortQueryBuilder(clone, null).SQL; beforeSQL = Regex.Replace(beforeSQL, "cic_[0-9]+_", ""); cloneSQL = Regex.Replace(cloneSQL, "cic_[0-9]+_", ""); //the SQL should be the same for them Assert.AreEqual(beforeSQL, cloneSQL); var containerClone = clone.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively() .Where(a => a.RootFilterContainer_ID != null) .Select(ag => ag.RootFilterContainer).Single(); Assert.AreNotEqual(container, containerClone); //cleanup phase clone.DeleteInDatabase(); containerClone.DeleteInDatabase(); } finally { rootcontainer.RemoveChild(aggregate1); container1.RemoveChild(aggregate2); container1.RemoveChild(aggregate3); filter.DeleteInDatabase(); container.DeleteInDatabase(); } }
public void DeleteFilter() { _container.DeleteInDatabase();//cascades to filter }