public void QueryGeneration_BothHaveWHEREContainerAndParameters() { CreateParameters("'bob'", "'fish'"); var global = new AnyTableSqlParameter(CatalogueRepository, cic, "DECLARE @bob AS varchar(50);"); global.Value = "'zomber'"; global.SaveToDatabase(); try { ((IDeleteable)parama1).DeleteInDatabase(); var csqb = new CohortSummaryQueryBuilder(acDataset, acCohort); var builder = csqb.GetAdjustedAggregateBuilder(CohortSummaryAdjustment.WhereRecordsIn); Assert.AreEqual(CollapseWhitespace(@"DECLARE @bob AS varchar(50); SET @bob='zomber'; /*Agg2_Dataset*/ SELECT Year, count(*) AS MyCount FROM MyTable WHERE ( ( /*Filter2*/ @bob = 'fish' ) AND ( /*Filter1*/ @bob = 'bob' ) ) group by Year order by Year"), CollapseWhitespace(builder.SQL)); } finally { global.DeleteInDatabase(); DestroyParameters(); } }
public void Synchronization_MissingParameter() { string expectedMessage = "MyAwesomeFunction is a Table Valued Function but it does not have a record of the parameter @startNumber which appears in the underlying database"; AnyTableSqlParameter parameter = (AnyTableSqlParameter)_function.TableInfoCreated.GetAllParameters().Single(p => p.ParameterName.Equals("@startNumber")); parameter.DeleteInDatabase(); var syncer = new TableInfoSynchronizer(_function.TableInfoCreated); var ex = Assert.Throws <Exception>(() => syncer.Synchronize(new ThrowImmediatelyCheckNotifier())); Assert.IsTrue(ex.Message.Contains(expectedMessage)); //no parameter called @startNumber (because we deleted it right!) Assert.IsFalse(_function.TableInfoCreated.GetAllParameters().Any(p => p.ParameterName.Equals("@startNumber"))); //sync should have proposed to create the missing parameter (see above), accept the change Assert.IsTrue(syncer.Synchronize(new AcceptAllCheckNotifier())); //now parameter should have reappeared due to accepthing change Assert.IsTrue(_function.TableInfoCreated.GetAllParameters().Any(p => p.ParameterName.Equals("@startNumber"))); }
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(); } }