private void CreateTvfCatalogue(string cohortDatabaseName)
{
var svr = DiscoveredDatabaseICanCreateRandomTablesIn.Server;
using (var con = svr.GetConnection())
{
con.Open();
//create the newID view
svr.GetCommand("create view getNewID as select newid() as new_id", con).ExecuteNonQuery();
var sql = string.Format(
@"create function GetTopXRandom (@numberOfRecords int)
RETURNS @retTable TABLE
(
chi varchar(10),
definitionID int
)
AS
BEGIN
while(@numberOfRecords >0)
begin
insert into @retTable select top 1 chi,cohortDefinition_id from {0}..Cohort order by (select new_id from getNewID)
set @numberOfRecords = @numberOfRecords - 1
end
return
end
", cohortDatabaseName);
svr.GetCommand(sql, con).ExecuteNonQuery();
}
var tblvf = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTableValuedFunction("GetTopXRandom");
var importer = new TableValuedFunctionImporter(CatalogueRepository, tblvf);
TableInfo tbl;
ColumnInfo[] cols;
importer.DoImport(out tbl, out cols);
var engineer = new ForwardEngineerCatalogue(tbl, cols, true);
Catalogue cata;
CatalogueItem[] cis;
ExtractionInformation[] eis;
engineer.ExecuteForwardEngineering(out cata, out cis, out eis);
Assert.AreEqual("chi", eis[0].GetRuntimeName());
eis[0].IsExtractionIdentifier = true;
eis[0].SaveToDatabase();
_tvfCatalogue = cata;
_tvfTableInfo = tbl;
}
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();
DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("NonTVFTable").Drop();
DiscoveredDatabaseICanCreateRandomTablesIn.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();
}
