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