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