public void Synchronization_ParameterDefinitionChanged()
{
string expectedMessage =
"Parameter @startNumber is declared as 'DECLARE @startNumber AS int;' but in the Catalogue it appears as 'DECLARE @startNumber AS datetime;'";
AnyTableSqlParameter parameter = (AnyTableSqlParameter)_function.TableInfoCreated.GetAllParameters().Single(p => p.ParameterName.Equals("@startNumber"));
parameter.ParameterSQL = "DECLARE @startNumber AS datetime;";
parameter.SaveToDatabase();
var syncer = new TableInfoSynchronizer(_function.TableInfoCreated);
var ex = Assert.Throws <Exception>(() => syncer.Synchronize(new ThrowImmediatelyCheckNotifier()));
Assert.IsTrue(ex.Message.Contains(expectedMessage));
//no changes should yet have taken place since we didn't accept it yet
Assert.IsTrue(parameter.HasLocalChanges().Evaluation == ChangeDescription.NoChanges);
//sync should have proposed to adjusting the datatype
Assert.IsTrue(syncer.Synchronize(new AcceptAllCheckNotifier()));
//now parameter should have the correct datatype
Assert.IsTrue(parameter.HasLocalChanges().Evaluation == ChangeDescription.DatabaseCopyDifferent);
var diff = parameter.HasLocalChanges().Differences.Single();
Assert.AreEqual("DECLARE @startNumber AS datetime;", diff.LocalValue);
Assert.AreEqual("DECLARE @startNumber AS int;", diff.DatabaseValue);
}
/// <summary>
/// Method called when creating new parameters if no CreateNewSqlParameterHandler was provided during construction
/// </summary>
/// <returns></returns>
private ISqlParameter CreateNewParameterDefaultImplementation(ICollectSqlParameters collector, string parameterName)
{
if (!parameterName.StartsWith("@"))
{
parameterName = "@" + parameterName;
}
var entity = (IMapsDirectlyToDatabaseTable)collector;
var newParam = new AnyTableSqlParameter((ICatalogueRepository)entity.Repository, entity, AnyTableSqlParameter.GetDefaultDeclaration(parameterName));
newParam.Value = AnyTableSqlParameter.DefaultValue;
newParam.SaveToDatabase();
return(newParam);
}
/// <summary>
/// Method called when creating new parameters if no CreateNewSqlParameterHandler was provided during construction
/// </summary>
/// <returns></returns>
private ISqlParameter CreateNewParameterDefaultImplementation(ICollectSqlParameters collector, string parameterName)
{
if (!parameterName.StartsWith("@"))
{
parameterName = "@" + parameterName;
}
var entity = (IMapsDirectlyToDatabaseTable)collector;
var newParam = new AnyTableSqlParameter((ICatalogueRepository)entity.Repository, entity, "DECLARE " + parameterName + " as varchar(10)");
newParam.Value = "'todo'";
newParam.SaveToDatabase();
return(newParam);
}
/// <summary>
/// Creates a complete clone copy of the current AggregateConfiguration. The clone will include all new AggregateDimensions, IFilters, IContainers etc.
/// IMPORTANT: This method is designed for cohort identifying AggregateConfigurations only and therefore does not support Axis / TopX / Pivot.
/// </summary>
/// <returns></returns>
public AggregateConfiguration CreateClone()
{
var cataRepo = CatalogueRepository;
var clone = ShallowClone();
if (clone.PivotOnDimensionID != null)
{
throw new NotImplementedException("Cannot clone due to PIVOT");
}
foreach (AggregateDimension aggregateDimension in AggregateDimensions)
{
var cloneDimension = new AggregateDimension((ICatalogueRepository)Repository, aggregateDimension.ExtractionInformation, clone);
cloneDimension.Alias = aggregateDimension.Alias;
cloneDimension.SelectSQL = aggregateDimension.SelectSQL;
cloneDimension.Order = aggregateDimension.Order;
cloneDimension.SaveToDatabase();
if (aggregateDimension.AggregateContinuousDateAxis != null)
{
throw new NotImplementedException("Cannot clone due to AXIS");
}
}
//now clone it's AggregateForcedJoins
foreach (var t in cataRepo.AggregateForcedJoinManager.GetAllForcedJoinsFor(this))
{
cataRepo.AggregateForcedJoinManager.CreateLinkBetween(clone, t);
}
if (RootFilterContainer_ID != null)
{
var clonedContainerSet = RootFilterContainer.DeepCloneEntireTreeRecursivelyIncludingFilters();
clone.RootFilterContainer_ID = clonedContainerSet.ID;
}
foreach (var p in GetAllParameters())
{
var cloneP = new AnyTableSqlParameter(cataRepo, clone, p.ParameterSQL);
cloneP.Comment = p.Comment;
cloneP.Value = p.Value;
cloneP.SaveToDatabase();
}
clone.SaveToDatabase();
return(clone);
}
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_ParameterRenamed()
{
AnyTableSqlParameter parameter = (AnyTableSqlParameter)_function.TableInfoCreated.GetAllParameters().Single(p => p.ParameterName.Equals("@startNumber"));
parameter.ParameterSQL = "DECLARE @startNum AS int";
parameter.SaveToDatabase();
var syncer = new TableInfoSynchronizer(_function.TableInfoCreated);
//shouldn't be any
Assert.IsFalse(_function.TableInfoCreated.GetAllParameters().Any(p => p.ParameterName.Equals("@startNumber")));
syncer.Synchronize(new AcceptAllCheckNotifier());
var after = _function.TableInfoCreated.GetAllParameters();
//now there should be recreated (actually it will suggest deleting the excess one and creating the underlying one as 2 separate suggestions one after the other)
Assert.IsTrue(after.Any(p => p.ParameterName.Equals("@startNumber")));
//still there should only be 3 parameters
Assert.AreEqual(3, after.Length);
}
public void GenerateAggregateUsingOverridenParametersTest()
{
CreateFunction(CatalogueRepository);
var agg = new AggregateConfiguration(CatalogueRepository, _function.Cata, "MyExcitingAggregate");
try
{
var param = new AnyTableSqlParameter(CatalogueRepository, agg, "DECLARE @name AS varchar(50);");
param.Value = "'lobster'";
param.SaveToDatabase();
var aggregateForcedJoin = new AggregateForcedJoin(CatalogueRepository);
aggregateForcedJoin.CreateLinkBetween(agg, _function.TableInfoCreated);
//do a count * on the query builder
AggregateBuilder queryBuilder = agg.GetQueryBuilder();
Assert.IsTrue(queryBuilder.SQL.Contains(@"SELECT"));
Assert.IsTrue(queryBuilder.SQL.Contains(@"count(*)"));
//should have this version of things
Assert.IsTrue(queryBuilder.SQL.Contains(@"DECLARE @name AS varchar(50);"));
Assert.IsTrue(queryBuilder.SQL.Contains(@"SET @name='lobster';"));
//isntead of this verison of things
Assert.IsFalse(queryBuilder.SQL.Contains(@"SET @name='fish';"));
Assert.IsTrue(queryBuilder.SQL.Contains("..MyAwesomeFunction(@startNumber,@stopNumber,@name) AS MyAwesomeFunction"));
Console.WriteLine(queryBuilder.SQL);
}
finally
{
agg.DeleteInDatabase();
}
}
private void TestUsingTvfForAggregates()
{
_aggregate = new AggregateConfiguration(CatalogueRepository, _tvfCatalogue, "tvfAggregate");
var ei = _tvfCatalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => !e.IsExtractionIdentifier);
_aggregate.AddDimension(ei);
//change the parameter to 10
var p = _tvfTableInfo.GetAllParameters().Single();
p.Value = "10";
p.SaveToDatabase();
var qb = _aggregate.GetQueryBuilder();
//Query should be something like :
/*
* DECLARE @numberOfRecords AS int;
* SET @numberOfRecords=10;
* tvfAggregate
* SELECT
* GetTopXRandom.[definitionID],
* count(*)
* FROM
* [TestDbName_ScratchArea]..GetTopXRandom(@numberOfRecords) AS GetTopXRandom
* group by
* GetTopXRandom.[definitionID]
* order by
* GetTopXRandom.[definitionID]
*
* --Since we only imported 1 cohort we should have 1 row and the count should be the number we requested
*
* */
var sql = qb.SQL;
var db = DataAccessPortal.GetInstance().ExpectDatabase(_tvfTableInfo, DataAccessContext.InternalDataProcessing);
using (var con = db.Server.GetConnection())
{
con.Open();
var r = db.Server.GetCommand(sql, con).ExecuteReader();
Assert.IsTrue(r.Read());
Assert.AreEqual(r[1], 10);
Assert.IsFalse(r.Read());
}
//create a global overriding parameter on the aggregate
var global = new AnyTableSqlParameter(CatalogueRepository, _aggregate, "DECLARE @numberOfRecords AS int;");
global.Value = "1";
global.SaveToDatabase();
//refresh the SQL
sql = _aggregate.GetQueryBuilder().SQL;
using (var con = db.Server.GetConnection())
{
con.Open();
var r = db.Server.GetCommand(sql, con).ExecuteReader();
Assert.IsTrue(r.Read());
Assert.AreEqual(r[1], 1);//should now only have 1 record being retrned and counted when executing
Assert.IsFalse(r.Read());
}
}
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 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 ImportCatalogueWithSingleFilterThatHasAParameter(bool createAGlobalOverrideBeforeHand)
{
string parameterSQL = "DECLARE @dragonCount as varchar(100)";
var filter = new ExtractionFilter(CatalogueRepository, "MyMandatoryFilter", testData.extractionInformations[0]);
filter.IsMandatory = true;
filter.WhereSQL = "There Be Dragons AND @dragonCount = 1";
filter.SaveToDatabase();
//Should result in the creation of a parameter
new ParameterCreator(new ExtractionFilterFactory(testData.extractionInformations[0]), null, null).CreateAll(filter, null);
var filterParameters = filter.ExtractionFilterParameters.ToArray();
Assert.AreEqual(1, filterParameters.Length);
filterParameters[0].ParameterSQL = parameterSQL;
filterParameters[0].Value = "'No More than 300 Dragons Please'";
filterParameters[0].SaveToDatabase();
AnyTableSqlParameter global = null;
if (createAGlobalOverrideBeforeHand)
{
global = new AnyTableSqlParameter(CatalogueRepository, cohortIdentificationConfiguration, parameterSQL);
global.Value = "'At Least 1000 Dragons'";
global.SaveToDatabase();
}
//ensure that it is picked up
var mandatoryFilters = testData.catalogue.GetAllMandatoryFilters();
Assert.AreEqual(1, mandatoryFilters.Length);
Assert.AreEqual(filter, mandatoryFilters[0]);
AggregateConfiguration importedAggregate = null;
try
{
importedAggregate = cohortIdentificationConfiguration.CreateNewEmptyConfigurationForCatalogue(testData.catalogue, null);
var importedAggregateFilterContainer = importedAggregate.RootFilterContainer;
//Must have a root container
Assert.IsNotNull(importedAggregateFilterContainer);
//With an AND operation
Assert.AreEqual(FilterContainerOperation.AND, importedAggregateFilterContainer.Operation);
var importedFilters = importedAggregateFilterContainer.GetFilters();
Assert.AreEqual(1, importedFilters.Length);
//Because the configuration already has a parameter with the same declaration it should not bother to import the parameter from the underlying filter
if (createAGlobalOverrideBeforeHand)
{
Assert.AreEqual(0, importedFilters[0].GetAllParameters().Length);
}
else
{
//Because there is no global we should be creating a clone of the parameter too
var paramClones = importedFilters[0].GetAllParameters();
Assert.AreEqual(1, paramClones.Length);
//clone should have same SQL and Value
Assert.AreEqual(parameterSQL, paramClones[0].ParameterSQL);
Assert.AreEqual(filterParameters[0].ParameterSQL, paramClones[0].ParameterSQL);
Assert.AreEqual(filterParameters[0].Value, paramClones[0].Value);
//but not be the same object in database
Assert.AreNotEqual(filterParameters[0], paramClones[0]);
}
}
finally
{
if (global != null)
{
global.DeleteInDatabase();
}
filter.DeleteInDatabase();
if (importedAggregate != null)
{
importedAggregate.RootFilterContainer.DeleteInDatabase();
importedAggregate.DeleteInDatabase();
}
}
}