public void CommittingNewCohortFile_CallPipeline()
{
var listener = new ThrowImmediatelyDataLoadEventListener();
var proj = new Project(DataExportRepository, projName);
proj.ProjectNumber = 999;
proj.SaveToDatabase();
CohortCreationRequest request = new CohortCreationRequest(proj, new CohortDefinition(null, "CommittingNewCohorts", 1, 999, _externalCohortTable), (DataExportRepository)DataExportRepository, "fish");
request.Check(new ThrowImmediatelyCheckNotifier());
DelimitedFlatFileDataFlowSource source = new DelimitedFlatFileDataFlowSource();
BasicCohortDestination destination = new BasicCohortDestination();
source.Separator = ",";
source.StronglyTypeInput = true;
DataFlowPipelineEngine <DataTable> pipeline = new DataFlowPipelineEngine <DataTable>((DataFlowPipelineContext <DataTable>)request.GetContext(), source, destination, listener);
pipeline.Initialize(new FlatFileToLoad(new FileInfo(filename)), request);
pipeline.ExecutePipeline(new GracefulCancellationToken());
//there should be a new ExtractableCohort now
Assert.NotNull(request.NewCohortDefinition.ID);
var ec = DataExportRepository.GetAllObjects <ExtractableCohort>().Single(c => c.OriginID == request.NewCohortDefinition.ID);
//with the data in it from the test file
Assert.AreEqual(ec.Count, 3);
}
public void TearDown()
{
//delete all joins
foreach (JoinInfo j in CatalogueRepository.GetAllObjects <JoinInfo>())
{
j.DeleteInDatabase();
}
//delete everything from data export
foreach (var t in new Type[] { typeof(ExtractionConfiguration), typeof(ExternalCohortTable), typeof(ExtractableDataSet) })
{
foreach (IDeleteable o in DataExportRepository.GetAllObjects(t))
{
o.DeleteInDatabase();
}
}
//delete everything from catalogue
foreach (var t in new Type[] { typeof(Catalogue), typeof(TableInfo), typeof(LoadMetadata), typeof(Pipeline) })
{
foreach (IDeleteable o in CatalogueRepository.GetAllObjects(t))
{
o.DeleteInDatabase();
}
}
}
public ExtractableColumn ShallowClone()
{
var eds = DataExportRepository.GetObjectByID <ExtractableDataSet>(ExtractableDataSet_ID);
var config = DataExportRepository.GetObjectByID <ExtractionConfiguration>(ExtractionConfiguration_ID);
var clone = new ExtractableColumn(DataExportRepository, eds, config, CatalogueExtractionInformation, Order, SelectSQL);
CopyShallowValuesTo(clone);
return(clone);
}
public void HackSQLTest_Normal()
{
if (_request.QueryBuilder == null)
{
_request.GenerateQueryBuilder();
}
string expectedOutput =
string.Format(@"/*The ID of the cohort in [tempdb]..[Cohort]*/
DECLARE @CohortDefinitionID AS int;
SET @CohortDefinitionID=-599;
/*The project number of project {0}ExtractionConfiguration*/
DECLARE @ProjectNumber AS int;
SET @ProjectNumber=1;
SELECT DISTINCT
[tempdb]..[Cohort].[ReleaseID] AS ReleaseID,
[{0}ScratchArea].dbo.[TestTable].[Name],
[{0}ScratchArea].dbo.[TestTable].[DateOfBirth]
FROM
[{0}ScratchArea].dbo.[TestTable]
INNER JOIN [tempdb]..[Cohort] ON [{0}ScratchArea].dbo.[TestTable].[PrivateID]=[tempdb]..[Cohort].[PrivateID]
WHERE
[tempdb]..[Cohort].[cohortDefinition_id]=-599
", TestDatabaseNames.Prefix);
//cross server is only used if cohort and dataset are on different servers so pretend the cohort is on bob server
var ect = (ExternalCohortTable)_request.ExtractableCohort.ExternalCohortTable;
ect.Server = "bob";
var e = DataExportRepository.GetObjectByID <ExternalCohortTable>(_request.ExtractableCohort.ExternalCohortTable_ID);
string origValue = e.Database;
e.Database = CohortDatabaseName;
e.SaveToDatabase();
try
{
ExecuteCrossServerDatasetExtractionSource s = new ExecuteCrossServerDatasetExtractionSource();
s.TemporaryDatabaseName = "tempdb";
s.PreInitialize(_request, new ThrowImmediatelyDataLoadEventListener());
string hacked = s.HackExtractionSQL(_request.QueryBuilder.SQL, new ThrowImmediatelyDataLoadEventListener()
{
ThrowOnWarning = true
});
Assert.AreEqual(expectedOutput.Trim(), hacked.Trim());
}
finally
{
e.Database = origValue;
e.SaveToDatabase();
}
}
public void CleanupProjects()
{
foreach (var c in DataExportRepository.GetAllObjects <ExtractableCohort>().Where(c => c.GetExternalData().ExternalDescription.Equals("CommittingNewCohorts")))
{
c.DeleteInDatabase();
}
foreach (Project p in DataExportRepository.GetAllObjects <Project>().Where(p => p.Name.Equals(projName)))
{
p.DeleteInDatabase();
}
}
public void IntroduceOrphan()
{
var cols = _configuration.GetAllExtractableColumnsFor(_extractableDataSet).Cast <ExtractableColumn>().ToArray();
var name = cols.Single(c => c.GetRuntimeName().Equals("Name"));
using (var con = DataExportRepository.GetConnection())
{
DataExportRepository.DiscoveredServer.GetCommand(
"UPDATE ExtractableColumn set CatalogueExtractionInformation_ID = " + int.MaxValue + " where ID = " +
name.ID, con).ExecuteNonQuery();
}
}
private void TestDataExportOfTvf()
{
var config = new ExtractionConfiguration(DataExportRepository, _project);
config.Cohort_ID = DataExportRepository.GetAllObjects <ExtractableCohort>().Single().ID;
config.SaveToDatabase();
var tvfExtractable = new ExtractableDataSet(DataExportRepository, _tvfCatalogue);
var selected = new SelectedDataSets(DataExportRepository, config, tvfExtractable, null);
//make all columns part of the extraction
foreach (ExtractionInformation e in _tvfCatalogue.GetAllExtractionInformation(ExtractionCategory.Any))
{
config.AddColumnToExtraction(tvfExtractable, e);
}
//the default value should be 10
Assert.AreEqual("10", _tvfTableInfo.GetAllParameters().Single().Value);
//configure an extraction specific global of 1 so that only 1 chi number is fetched (which will be in the cohort)
var globalP = new GlobalExtractionFilterParameter(DataExportRepository, config, "DECLARE @numberOfRecords AS int;");
globalP.Value = "1";
globalP.SaveToDatabase();
var extractionCommand = new ExtractDatasetCommand(config, new ExtractableDatasetBundle(tvfExtractable));
var source = new ExecuteDatasetExtractionSource();
source.PreInitialize(extractionCommand, new ThrowImmediatelyDataLoadEventListener());
var dt = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("ReleaseId", dt.Columns[0].ColumnName);
//should be a guid
Assert.IsTrue(dt.Rows[0][0].ToString().Length > 10);
Assert.IsTrue(dt.Rows[0][0].ToString().Contains("-"));
selected.DeleteInDatabase();
globalP.DeleteInDatabase();
config.DeleteInDatabase();
tvfExtractable.DeleteInDatabase();
}
public void TearDownCatalogues()
{
_cata1.DeleteInDatabase();
_cata2.DeleteInDatabase();
_t1.DeleteInDatabase();
_t2.DeleteInDatabase();
foreach (
ExternalCohortTable source in
DataExportRepository.GetAllObjects <ExternalCohortTable>()
.Where(s => s.Name.Equals(cohortDatabaseName)))
{
source.DeleteInDatabase();
}
}
private void CreateExternalCohortTableReference()
{
ExternalCohortTable alreadyExisting = DataExportRepository.GetAllObjects <ExternalCohortTable>()
.SingleOrDefault(external => external.Name.Equals(ExternalCohortTableNameInCatalogue));
if (alreadyExisting != null)
{
//remove dependencies cohorts
ExtractableCohort toCleanup = DataExportRepository.GetAllObjects <ExtractableCohort>().SingleOrDefault(ec => ec.OriginID == cohortIDInTestData);
if (toCleanup != null)
{
//cleanup any configs that use the cohort
foreach (
var configToCleanup in DataExportRepository.GetAllObjects <ExtractionConfiguration>()
.Where(config => config.Cohort_ID == toCleanup.ID))
{
configToCleanup.DeleteInDatabase();
}
toCleanup.DeleteInDatabase();
}
alreadyExisting.DeleteInDatabase();
}
var newExternal = new ExternalCohortTable(DataExportRepository, "TestExternalCohort", DatabaseType.MicrosoftSQLServer)
{
Database = CohortDatabaseName,
Server = _cohortDatabase.Server.Name,
DefinitionTableName = definitionTableName,
TableName = cohortTableName,
Name = ExternalCohortTableNameInCatalogue,
Username = _cohortDatabase.Server.ExplicitUsernameIfAny,
Password = _cohortDatabase.Server.ExplicitPasswordIfAny,
PrivateIdentifierField = "PrivateID",
ReleaseIdentifierField = "ReleaseID",
DefinitionTableForeignKeyField = "cohortDefinition_id"
};
newExternal.SaveToDatabase();
_externalCohortTable = newExternal;
}
public void UpdateProjectDatabaseTable()
{
Project table = new Project(DataExportRepository, "unit_test_UpdateProjectDatabaseTable");
try
{
Assert.AreEqual(table.Name, "unit_test_UpdateProjectDatabaseTable");
table.Name = "unit_test_UpdateProjectDatabaseTable2";
table.SaveToDatabase();
//get fresh copy from database and ensure that all fields are the same
var tableAfter = DataExportRepository.GetObjectByID <Project>(table.ID);
PropertyValuesAreEquals(table, tableAfter);
}
finally
{
table.DeleteInDatabase();
}
}
public CohortCreationRequestUI(IActivateItems activator, IExternalCohortTable target, IProject project = null) : base(activator)
{
_target = target;
InitializeComponent();
if (_target == null)
{
return;
}
_repository = (DataExportRepository)_target.Repository;
lblExternalCohortTable.Text = _target.ToString();
SetProject(project);
pbProject.Image = CatalogueIcons.Project;
pbCohortSource.Image = CatalogueIcons.ExternalCohortTable;
}
public void AddAndRemove()
{
var cata = new Catalogue(CatalogueRepository, "PackageContentsTests");
var ds = new ExtractableDataSet(DataExportRepository, cata);
var package = new ExtractableDataSetPackage(DataExportRepository, "My Cool Package");
try
{
Assert.AreEqual("My Cool Package", package.Name);
package.Name = "FishPackage";
package.SaveToDatabase();
var packageContents = new ExtractableDataSetPackageManager(DataExportRepository);
var results = packageContents.GetAllDataSets(package, null);
Assert.AreEqual(0, results.Length);
packageContents.AddDataSetToPackage(package, ds);
results = packageContents.GetAllDataSets(package, DataExportRepository.GetAllObjects <ExtractableDataSet>());
Assert.AreEqual(1, results.Length);
Assert.AreEqual(ds, results[0]);
packageContents.RemoveDataSetFromPackage(package, ds);
//cannot delete the relationship twice
Assert.Throws <ArgumentException>(() => packageContents.RemoveDataSetFromPackage(package, ds));
}
finally
{
ds.DeleteInDatabase();
package.DeleteInDatabase();
cata.DeleteInDatabase();
}
}
/// <summary>
/// Fetches all containers and filters out of the <paramref name="repository"/> and sets the class up to provide
/// fast access to them.
/// </summary>
/// <param name="repository"></param>
/// <param name="childProvider"></param>
public DataExportFilterManagerFromChildProvider(DataExportRepository repository, DataExportChildProvider childProvider) : base(repository)
{
_containersToFilters = childProvider.AllDeployedExtractionFilters.Where(f => f.FilterContainer_ID.HasValue).GroupBy(f => f.FilterContainer_ID.Value).ToDictionary(gdc => gdc.Key, gdc => gdc.ToList());
var server = repository.DiscoveredServer;
using (var con = repository.GetConnection())
{
var r = server.GetCommand("SELECT * FROM FilterContainerSubcontainers", con).ExecuteReader();
while (r.Read())
{
var parentId = Convert.ToInt32(r["FilterContainer_ParentID"]);
var subcontainerId = Convert.ToInt32(r["FilterContainerChildID"]);
if (!_subcontainers.ContainsKey(parentId))
{
_subcontainers.Add(parentId, new List <FilterContainer>());
}
_subcontainers[parentId].Add(childProvider.AllContainers[subcontainerId]);
}
r.Close();
}
}
public void EndToEndTest()
{
var cohortDatabaseNameWillBe = TestDatabaseNames.GetConsistentName("TbvCohort");
_discoveredCohortDatabase = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(cohortDatabaseNameWillBe);
//cleanup
if (_discoveredCohortDatabase.Exists())
{
_discoveredCohortDatabase.Drop();
}
//create a normal catalogue
CreateANormalCatalogue();
//create a cohort database using wizard
CreateNewCohortDatabaseWizard cohortDatabaseWizard = new CreateNewCohortDatabaseWizard(_discoveredCohortDatabase, CatalogueRepository, DataExportRepository, false);
_externalCohortTable = cohortDatabaseWizard.CreateDatabase(
new PrivateIdentifierPrototype(_nonTvfExtractionIdentifier)
, new ThrowImmediatelyCheckNotifier());
//create a table valued function
CreateTvfCatalogue(cohortDatabaseNameWillBe);
//Test 1
TestThatQueryBuilderWithoutParametersBeingSetThrowsQueryBuildingException();
PopulateCohortDatabaseWithRecordsFromNonTvfCatalogue();
//Test 2
TestWithParameterValueThatRowsAreReturned();
//Test 3
TestUsingTvfForAggregates();
//Test 4
TestAddingTvfToCIC();
//Test 5
TestDataExportOfTvf();
//tear down
DataExportRepository.GetAllObjects <ExtractableCohort>().Single().DeleteInDatabase();
_externalCohortTable.DeleteInDatabase();
_database.ExpectTable("NonTVFTable").Drop();
_database.ExpectTableValuedFunction("GetTopXRandom").Drop();
//delete global parameter
((AnyTableSqlParameter)_aggregate.GetAllParameters().Single()).DeleteInDatabase();
//delete aggregate
_aggregate.DeleteInDatabase();
((AnyTableSqlParameter)_cicAggregate.GetAllParameters().Single()).DeleteInDatabase();
//delete aggregate
_cicAggregate.DeleteInDatabase();
//get rid of the cohort identification configuration
_cic.DeleteInDatabase();
_pipe.DeleteInDatabase();
//get rid of the cohort database
_discoveredCohortDatabase.Drop();
_nonTvfCatalogue.DeleteInDatabase();
_nonTvfTableInfo.DeleteInDatabase();
_tvfCatalogue.DeleteInDatabase();
_tvfTableInfo.DeleteInDatabase();
}
/// <summary>
/// Creates a new instance ready to read values out of the <paramref name="repository"/> database
/// </summary>
/// <param name="allowCaching"></param>
/// <param name="repository"></param>
public DataExportPropertyManager(bool allowCaching, DataExportRepository repository)
{
_allowCaching = allowCaching;
_repository = repository;
}
/// <summary>
/// Creates a new <see cref="ExtractableDataSetPackageManager"/> which can read the contents of <see cref="ExtractableDataSetPackage"/>s i.e.
/// which datasets are part of which packages (many to many relationship).
///
/// <para>The contents of packages are fetched during construction only and are not sensitive to remote changes during thereafter (e.g. by other users)</para>
/// </summary>
/// <param name="repository"></param>
public ExtractableDataSetPackageManager(DataExportRepository repository)
{
_repository = repository;
_packageContentsDictionary = new Lazy <Dictionary <int, List <int> > >(GetPackageContentsDictionary);
}
public void CommitCohortExampleTest(DatabaseType dbType, string privateDataType)
{
RunBlitzDatabases(RepositoryLocator);
//find the test server (where we will create the store schema)
var db = GetCleanedServer(dbType);
//create the cohort store table
var wizard = new CreateNewCohortDatabaseWizard(db, CatalogueRepository, DataExportRepository, false);
var privateColumn = new PrivateIdentifierPrototype("chi", privateDataType);
var externalCohortTable = wizard.CreateDatabase(privateColumn, new ThrowImmediatelyCheckNotifier());
Assert.AreEqual(dbType, externalCohortTable.DatabaseType);
//create a project into which we want to import a cohort
var project = new Project(DataExportRepository, "MyProject");
project.ProjectNumber = 500;
project.SaveToDatabase();
//create a description of the cohort we are importing
var definition = new CohortDefinition(null, "MyCohort", 1, 500, externalCohortTable);
//create our cohort (normally this would be read from a file or be the results of cohort identification query)
var dt = new DataTable();
dt.Columns.Add("chi");
dt.Rows.Add("0101010101");
dt.Rows.Add("0202020202");
//Create a pipeline (we only need the destination)
var pipelineDestination = new BasicCohortDestination();
//choose how to allocate the anonymous release identifiers
pipelineDestination.ReleaseIdentifierAllocator = typeof(ProjectConsistentGuidReleaseIdentifierAllocator);
//initialize the destination
pipelineDestination.PreInitialize(
new CohortCreationRequest(project, definition, DataExportRepository, "A cohort created in an example unit test"),
new ThrowImmediatelyDataLoadEventListener());
//process the cohort data table
pipelineDestination.ProcessPipelineData(dt, new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
//there should be no cohorts yet
Assert.IsEmpty(DataExportRepository.GetAllObjects <ExtractableCohort>());
//dispose of the pipeline
pipelineDestination.Dispose(new ThrowImmediatelyDataLoadEventListener(), null);
//now there should be one
ExtractableCohort cohort = DataExportRepository.GetAllObjects <ExtractableCohort>().Single();
//make sure we are all on the same page about what the DBMS type is (nothing cached etc)
Assert.AreEqual(dbType, cohort.ExternalCohortTable.DatabaseType);
Assert.AreEqual(dbType, cohort.GetQuerySyntaxHelper().DatabaseType);
Assert.AreEqual(500, cohort.ExternalProjectNumber);
Assert.AreEqual(2, cohort.CountDistinct);
var tbl = externalCohortTable.DiscoverCohortTable();
Assert.AreEqual(2, tbl.GetRowCount());
var dtInDatabase = tbl.GetDataTable();
//guid will be something like "6fb23de5-e8eb-46eb-84b5-dd368da21073"
Assert.AreEqual(36, dtInDatabase.Rows[0]["ReleaseId"].ToString().Length);
Assert.AreEqual("0101010101", dtInDatabase.Rows[0]["chi"]);
}
public DataExportFilterManager(DataExportRepository dataExportRepository)
{
_dataExportRepository = dataExportRepository;
}