public void ImportCatalogueWithMultipleMandatoryFilters()
{
//First mandatory
var filter1 = new ExtractionFilter(CatalogueRepository, "MyMandatoryFilter", testData.extractionInformations[0]);
filter1.IsMandatory = true;
filter1.WhereSQL = "There Be Dragons";
filter1.SaveToDatabase();
//Second mandatory
var filter2 = new ExtractionFilter(CatalogueRepository, "MyMandatoryFilter", testData.extractionInformations[1]);
filter2.IsMandatory = true;
filter2.WhereSQL = "And Months";
filter2.SaveToDatabase();
//Then one that is not mandatory
var filter3 = new ExtractionFilter(CatalogueRepository, "MyMandatoryFilter", testData.extractionInformations[2]);
filter3.IsMandatory = false;
filter3.WhereSQL = "But Can Also Be Flies";
filter3.SaveToDatabase();
//ensure that both are picked up as mandatory filters by catalogue
var mandatoryFilters = testData.catalogue.GetAllMandatoryFilters();
Assert.AreEqual(2, mandatoryFilters.Length);
AggregateConfiguration importedAggregate = null;
try
{
//import the Catalogue
importedAggregate = cohortIdentificationConfiguration.CreateNewEmptyConfigurationForCatalogue(testData.catalogue, null);
var importedAggregateFilterContainer = importedAggregate.RootFilterContainer;
//Must have a root container
Assert.IsNotNull(importedAggregateFilterContainer);
//the AND container should be there
Assert.AreEqual(FilterContainerOperation.AND, importedAggregateFilterContainer.Operation);
//the filters should both be there (See above test for WHERE SQL, ID etc checking)
var importedFilters = importedAggregateFilterContainer.GetFilters();
Assert.AreEqual(2, importedFilters.Length);
}
finally
{
filter1.DeleteInDatabase();
filter2.DeleteInDatabase();
filter3.DeleteInDatabase();
if (importedAggregate != null)
{
importedAggregate.RootFilterContainer.DeleteInDatabase();
importedAggregate.DeleteInDatabase();
}
}
}
public void test_creating_ExtractionFilter()
{
ExtractionInformation extractInfo = null;
ExtractionFilter filterFastThings = null;
ExtractionFilterParameter parameter = null;
try
{
//define extraction information
extractInfo = new ExtractionInformation(CatalogueRepository, cataItem, columnInfo, "ROUND(VelocityOfMatter,2) VelocityOfMatterRounded");
//define filter and parameter
filterFastThings = new ExtractionFilter(CatalogueRepository, "FastThings", extractInfo)
{
WhereSQL = "VelocityOfMatter > @X",
Description = "Query to identify things that travel faster than X miles per hour!"
};
filterFastThings.SaveToDatabase();
Assert.AreEqual(filterFastThings.Name, "FastThings");
parameter = new ExtractionFilterParameter(CatalogueRepository, "DECLARE @X INT", filterFastThings);
Assert.IsNotNull(parameter);
Assert.AreEqual(parameter.ParameterName, "@X");
parameter.Value = "500";
parameter.SaveToDatabase();
ExtractionFilterParameter afterSave = CatalogueRepository.GetObjectByID <ExtractionFilterParameter>(parameter.ID);
Assert.AreEqual(afterSave.Value, "500");
ExtractionFilter filterFastThings_NewCopyFromDB = CatalogueRepository.GetObjectByID <ExtractionFilter>(filterFastThings.ID);
Assert.AreEqual(filterFastThings.ID, filterFastThings_NewCopyFromDB.ID);
Assert.AreEqual(filterFastThings.Description, filterFastThings_NewCopyFromDB.Description);
Assert.AreEqual(filterFastThings.Name, filterFastThings_NewCopyFromDB.Name);
Assert.AreEqual(filterFastThings.WhereSQL, filterFastThings_NewCopyFromDB.WhereSQL);
}
finally
{
if (parameter != null)
{
parameter.DeleteInDatabase();
}
//filters are children of extraction info with CASCADE DELETE so have to delete this one first if we want to test it programatically (although we could just skip deleting it since SQL will handle it anyway)
if (filterFastThings != null)
{
filterFastThings.DeleteInDatabase();
}
if (extractInfo != null)
{
extractInfo.DeleteInDatabase();
}
}
}
public void ImportCatalogueWithMandatoryFilter()
{
var filter = new ExtractionFilter(CatalogueRepository, "MyMandatoryFilter", testData.extractionInformations[0]);
filter.IsMandatory = true;
filter.WhereSQL = "There Be Dragons";
filter.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);
Assert.AreEqual(ChangeDescription.NoChanges, importedAggregate.HasLocalChanges().Evaluation);
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);
//they are not the same object
Assert.AreNotEqual(filter, importedFilters[0]);
//the deployed filter knows it's parent it was cloned from
Assert.AreEqual(filter.ID, importedFilters[0].ClonedFromExtractionFilter_ID);
//the WHERE SQL of the filters should be the same
Assert.AreEqual(filter.WhereSQL, importedFilters[0].WhereSQL);
}
finally
{
filter.DeleteInDatabase();
if (importedAggregate != null)
{
importedAggregate.RootFilterContainer.DeleteInDatabase();
importedAggregate.DeleteInDatabase();
}
}
}
public void CloneWithFilters(bool introduceOrphanExtractionInformation)
{
if (introduceOrphanExtractionInformation)
{
IntroduceOrphan();
}
Assert.IsEmpty(_configuration.ReleaseLog);
var filter = new ExtractionFilter(CatalogueRepository, "FilterByFish", _extractionInformations[0]);
try
{
//setup a filter with a parameter
filter.WhereSQL = "Fish = @fish";
new ParameterCreator(new ExtractionFilterFactory(_extractionInformations[0]), null, null).CreateAll(filter, null);
filter.SaveToDatabase();
Assert.IsTrue(filter.ExtractionFilterParameters.Count() == 1);
//create a root container
var container = new FilterContainer(DataExportRepository);
_selectedDataSet.RootFilterContainer_ID = container.ID;
_selectedDataSet.SaveToDatabase();
//create a deployed filter
var importer = new FilterImporter(new DeployedExtractionFilterFactory(DataExportRepository), null);
var deployedFilter = (DeployedExtractionFilter)importer.ImportFilter(filter, null);
deployedFilter.FilterContainer_ID = container.ID;
deployedFilter.Name = "FilterByFishDeployed";
deployedFilter.SaveToDatabase();
var param = deployedFilter.ExtractionFilterParameters[0];
param.Value = "'jormungander'";
param.SaveToDatabase();
ExtractDatasetCommand request = new ExtractDatasetCommand(_configuration, new ExtractableDatasetBundle(_extractableDataSet));
request.GenerateQueryBuilder();
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"DECLARE @fish AS varchar(50);
SET @fish='jormungander';
/*The ID of the cohort in [{0}CohortDatabase]..[Cohort]*/
DECLARE @CohortDefinitionID AS int;
SET @CohortDefinitionID=-599;
/*The project number of project {0}ExtractionConfiguration*/
DECLARE @ProjectNumber AS int;
SET @ProjectNumber=1;
SELECT DISTINCT
[{0}CohortDatabase]..[Cohort].[ReleaseID] AS ReleaseID,
[{0}ScratchArea].[dbo].[TestTable].[Name],
[{0}ScratchArea].[dbo].[TestTable].[DateOfBirth]
FROM
[{0}ScratchArea].[dbo].[TestTable] INNER JOIN [{0}CohortDatabase]..[Cohort] ON [{0}ScratchArea].[dbo].[TestTable].[PrivateID]=[{0}CohortDatabase]..[Cohort].[PrivateID]
WHERE
(
/*FilterByFishDeployed*/
Fish = @fish
)
AND
[{0}CohortDatabase]..[Cohort].[cohortDefinition_id]=-599
"
, TestDatabaseNames.Prefix))
, CollapseWhitespace(request.QueryBuilder.SQL));
ExtractionConfiguration deepClone = _configuration.DeepCloneWithNewIDs();
Assert.AreEqual(deepClone.Cohort_ID, _configuration.Cohort_ID);
Assert.AreNotEqual(deepClone.ID, _configuration.ID);
try
{
ExtractDatasetCommand request2 = new ExtractDatasetCommand(deepClone, new ExtractableDatasetBundle(_extractableDataSet));
request2.GenerateQueryBuilder();
Assert.AreEqual(request.QueryBuilder.SQL, request2.QueryBuilder.SQL);
}
finally
{
deepClone.DeleteInDatabase();
}
}
finally
{
filter.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();
}
}
}