public void SetupBulkTestData()
{
Console.WriteLine("Cleaning up remnants");
Cleanup();
Console.WriteLine("Setting up bulk test data");
_bulkData = new BulkTestsData(RepositoryLocator.CatalogueRepository, DiscoveredDatabaseICanCreateRandomTablesIn);
_bulkData.SetupTestData();
Console.WriteLine("Importing to Catalogue");
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable(BulkTestsData.BulkDataTable);
TableInfoImporter importer = new TableInfoImporter(CatalogueRepository, tbl);
importer.DoImport(out tableInfoCreated, out columnInfosCreated);
Console.WriteLine("Imported TableInfo " + tableInfoCreated);
Console.WriteLine("Imported ColumnInfos " + string.Join(",", columnInfosCreated.Select(c => c.GetRuntimeName())));
Assert.NotNull(tableInfoCreated);
ColumnInfo chi = columnInfosCreated.Single(c => c.GetRuntimeName().Equals("chi"));
Console.WriteLine("CHI is primary key? (expecting true):" + chi.IsPrimaryKey);
Assert.IsTrue(chi.IsPrimaryKey);
tableInfoCreated.ColumnInfos.Single(c => c.GetRuntimeName().Equals("surname")).DeleteInDatabase();
tableInfoCreated.ColumnInfos.Single(c => c.GetRuntimeName().Equals("forename")).DeleteInDatabase();
tableInfoCreated.ClearAllInjections();
}
public void Setup()
{
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("ReferentialIntegrityConstraintTests");
if (tbl.Exists())
{
tbl.Drop();
}
var server = DiscoveredDatabaseICanCreateRandomTablesIn.Server;
using (var con = server.GetConnection())
{
con.Open();
server.GetCommand("CREATE TABLE ReferentialIntegrityConstraintTests(MyValue int)", con).ExecuteNonQuery();
server.GetCommand("INSERT INTO ReferentialIntegrityConstraintTests (MyValue) VALUES (5)", con).ExecuteNonQuery();
}
TableInfoImporter importer = new TableInfoImporter(CatalogueRepository, tbl);
importer.DoImport(out _tableInfo, out _columnInfo);
_constraint = new ReferentialIntegrityConstraint(CatalogueRepository);
_constraint.OtherColumnInfo = _columnInfo.Single();
}
public void TearDown()
{
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("AliasHandlerTests");
if (tbl.Exists())
{
tbl.Drop();
}
_server.DeleteInDatabase();
}
public void TestPreExecutionChecker_TablesDontExist()
{
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("Imaginary");
Assert.IsFalse(tbl.Exists());
var lmd = RdmpMockFactory.Mock_LoadMetadataLoadingTable(tbl);
var checker = new PreExecutionChecker(lmd, new HICDatabaseConfiguration(DiscoveredDatabaseICanCreateRandomTablesIn.Server));
var ex = Assert.Throws <Exception>(() => checker.Check(new ThrowImmediatelyCheckNotifier()));
StringAssert.IsMatch("Table '.*Imaginary.*' does not exist", ex.Message);
}
public void CreateDataset()
{
_server = DiscoveredDatabaseICanCreateRandomTablesIn.Server;
using (var con = _server.GetConnection())
{
con.Open();
_server.GetCommand("CREATE TABLE " + TABLE_NAME + "(Name varchar(10), Address varchar(500))", con).ExecuteNonQuery();
}
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("TableInfoSynchronizerTests");
TableInfoImporter importer = new TableInfoImporter(CatalogueRepository, tbl);
importer.DoImport(out tableInfoCreated, out columnInfosCreated);
}
public void Drop()
{
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("ReferentialIntegrityConstraintTests");
if (tbl.Exists())
{
tbl.Drop();
}
var credentials = (DataAccessCredentials)_tableInfo.GetCredentialsIfExists(DataAccessContext.InternalDataProcessing);
_tableInfo.DeleteInDatabase();
if (credentials != null)
{
credentials.DeleteInDatabase();
}
}
public void GetRowCountWhenNoIndexes()
{
var server = DiscoveredDatabaseICanCreateRandomTablesIn.Server;
var table = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("GetRowCountWhenNoIndexes");
Assert.AreEqual("GetRowCountWhenNoIndexes", table.GetRuntimeName());
using (var con = server.GetConnection())
{
con.Open();
var cmd = server.GetCommand("CREATE TABLE " + table.GetRuntimeName() + " (age int, name varchar(5))", con);
cmd.ExecuteNonQuery();
var cmdInsert = server.GetCommand("INSERT INTO " + table.GetRuntimeName() + " VALUES (1,'Fish')", con);
Assert.AreEqual(1, cmdInsert.ExecuteNonQuery());
Assert.AreEqual(1, table.GetRowCount());
}
}
public void Test_CatalogueItems_NonExtractedPrimaryKey_LookupsOnly_IsRespected()
{
var request = SetupExtractDatasetCommand("LookupsOnly_IsRespected", new string[] { }, pkColumnInfos: new[] { "DateOfBirth" }, withLookup: true);
var source = new ExecutePkSynthesizerDatasetExtractionSource();
source.PreInitialize(request, new ThrowImmediatelyDataLoadEventListener());
var chunk = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
Assert.That(chunk.PrimaryKey, Is.Not.Null);
Assert.That(chunk.Columns.Cast <DataColumn>().ToList(), Has.Count.EqualTo(_columnInfos.Count() + 2)); // the "desc" column is added to the existing ones + the SynthPk
Assert.That(chunk.PrimaryKey, Has.Length.EqualTo(1));
Assert.That(chunk.PrimaryKey.First().ColumnName, Is.EqualTo("SynthesizedPk"));
var firstvalue = chunk.Rows[0]["SynthesizedPk"].ToString();
Assert.IsTrue(reghex.IsMatch(firstvalue));
DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("SimpleLookup").Drop();
}
public void DropTables()
{
var credentials = (DataAccessCredentials)tableInfoCreated.GetCredentialsIfExists(DataAccessContext.InternalDataProcessing);
//if credentials were created, we should only be one user
if (credentials != null)
{
Assert.AreEqual(1, credentials.GetAllTableInfosThatUseThis().Count());
}
//delete the table
tableInfoCreated.DeleteInDatabase();
//also delete any credentials created as part of TableInfoImport
if (credentials != null)
{
credentials.DeleteInDatabase();
}
DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable(TABLE_NAME).Drop();
}
public void PipelineTest()
{
var source = new DicomFileCollectionSource();
source.FilenameField = "RelativeFileArchiveURI";
var f = new FlatFileToLoad(new FileInfo(Path.Combine(TestContext.CurrentContext.TestDirectory, @"TestData\IM-0001-0013.dcm")));
source.PreInitialize(new FlatFileToLoadDicomFileWorklist(f), new ThrowImmediatelyDataLoadEventListener());
var tbl = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
var destination = new DataTableUploadDestination();
destination.PreInitialize(DiscoveredDatabaseICanCreateRandomTablesIn, new ThrowImmediatelyDataLoadEventListener());
destination.AllowResizingColumnsAtUploadTime = true;
destination.ProcessPipelineData(tbl, new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
destination.Dispose(new ThrowImmediatelyDataLoadEventListener(), null);
var finalTable = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable(destination.TargetTableName);
Assert.IsTrue(finalTable.Exists());
finalTable.Drop();
}
public void GetRowCount_Views()
{
var server = DiscoveredDatabaseICanCreateRandomTablesIn.Server;
using (var con = server.GetConnection())
{
con.Open();
var cmd = server.GetCommand("CREATE TABLE GetRowCount_Views (age int, name varchar(5))", con);
cmd.ExecuteNonQuery();
var cmdInsert = server.GetCommand("INSERT INTO GetRowCount_Views VALUES (1,'Fish')", con);
Assert.AreEqual(1, cmdInsert.ExecuteNonQuery());
var cmdView = server.GetCommand(
"CREATE VIEW v_GetRowCount_Views as select * from GetRowCount_Views", con);
cmdView.ExecuteNonQuery();
Assert.AreEqual(1,
DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("v_GetRowCount_Views").GetRowCount());
}
}
public void SynchronizationTests_ColumnDropped(bool acceptChanges)
{
Assert.AreEqual(TABLE_NAME, tableInfoCreated.GetRuntimeName());
var table = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable(TABLE_NAME);
var colToDrop = table.DiscoverColumn("Address");
table.DropColumn(colToDrop);
TableInfoSynchronizer synchronizer = new TableInfoSynchronizer(tableInfoCreated);
if (acceptChanges)
{
//accept changes should result in a synchronized table
Assert.AreEqual(true, synchronizer.Synchronize(new AcceptAllCheckNotifier()));
Assert.AreEqual(1, tableInfoCreated.ColumnInfos.Length);//should only be 1 remaining
}
else
{
var ex = Assert.Throws <Exception>(() => synchronizer.Synchronize(new ThrowImmediatelyCheckNotifier()));
Assert.AreEqual("The ColumnInfo Address no longer appears in the live table.", ex.Message);
}
}
public void KVPAttacherTest_Attach(KVPAttacherTestCase testCase)
{
bool hasPk = testCase != KVPAttacherTestCase.OneFileWithoutPrimaryKey;
var attacher = new KVPAttacher();
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("KVPTestTable");
var workingDir = new DirectoryInfo(TestContext.CurrentContext.TestDirectory);
var parentDir = workingDir.CreateSubdirectory("KVPAttacherTestProjectDirectory");
var projectDir = LoadDirectory.CreateDirectoryStructure(parentDir, "KVPAttacherTest", true);
string filepk = "kvpTestFilePK.csv";
string filepk2 = "kvpTestFilePK2.csv";
string fileNoPk = "kvpTestFile_NoPK.csv";
if (testCase == KVPAttacherTestCase.OneFileWithPrimaryKey || testCase == KVPAttacherTestCase.TwoFilesWithPrimaryKey)
{
CopyToBin(projectDir, filepk);
}
if (testCase == KVPAttacherTestCase.TwoFilesWithPrimaryKey)
{
CopyToBin(projectDir, filepk2);
}
if (testCase == KVPAttacherTestCase.OneFileWithoutPrimaryKey)
{
CopyToBin(projectDir, fileNoPk);
}
if (tbl.Exists())
{
tbl.Drop();
}
//Create destination data table on server (where the data will ultimately end up)
using (var con = (SqlConnection)tbl.Database.Server.GetConnection())
{
con.Open();
string sql = hasPk
? "CREATE TABLE KVPTestTable (Person varchar(100), Test varchar(50), Result int)"
: "CREATE TABLE KVPTestTable (Test varchar(50), Result int)";
new SqlCommand(sql, con).ExecuteNonQuery();
}
var remnantPipeline =
CatalogueRepository.GetAllObjects <Pipeline>("WHERE Name='KVPAttacherTestPipeline'").SingleOrDefault();
if (remnantPipeline != null)
{
remnantPipeline.DeleteInDatabase();
}
//Setup the Pipeline
var p = new Pipeline(CatalogueRepository, "KVPAttacherTestPipeline");
//With a CSV source
var flatFileLoad = new PipelineComponent(CatalogueRepository, p, typeof(DelimitedFlatFileDataFlowSource), 0, "Data Flow Source");
//followed by a Transpose that turns columns to rows (see how the test file grows right with new records instead of down, this is common in KVP input files but not always)
var transpose = new PipelineComponent(CatalogueRepository, p, typeof(Transposer), 1, "Transposer");
var saneHeaders = transpose.CreateArgumentsForClassIfNotExists(typeof(Transposer)).Single(a => a.Name.Equals("MakeHeaderNamesSane"));
saneHeaders.SetValue(false);
saneHeaders.SaveToDatabase();
//set the source separator to comma
flatFileLoad.CreateArgumentsForClassIfNotExists(typeof(DelimitedFlatFileDataFlowSource));
var arg = flatFileLoad.PipelineComponentArguments.Single(a => a.Name.Equals("Separator"));
arg.SetValue(",");
arg.SaveToDatabase();
arg = flatFileLoad.PipelineComponentArguments.Single(a => a.Name.Equals("MakeHeaderNamesSane"));
arg.SetValue(false);
arg.SaveToDatabase();
p.SourcePipelineComponent_ID = flatFileLoad.ID;
p.SaveToDatabase();
try
{
attacher.PipelineForReadingFromFlatFile = p;
attacher.TableName = "KVPTestTable";
switch (testCase)
{
case KVPAttacherTestCase.OneFileWithPrimaryKey:
attacher.FilePattern = filepk;
break;
case KVPAttacherTestCase.OneFileWithoutPrimaryKey:
attacher.FilePattern = fileNoPk;
break;
case KVPAttacherTestCase.TwoFilesWithPrimaryKey:
attacher.FilePattern = "kvpTestFilePK*.*";
break;
default:
throw new ArgumentOutOfRangeException("testCase");
}
if (hasPk)
{
attacher.PrimaryKeyColumns = "Person";
}
attacher.TargetDataTableKeyColumnName = "Test";
attacher.TargetDataTableValueColumnName = "Result";
attacher.Initialize(projectDir, DiscoveredDatabaseICanCreateRandomTablesIn);
attacher.Attach(new ThrowImmediatelyDataLoadJob(), new GracefulCancellationToken());
//test file contains 291 values belonging to 3 different people
int expectedRows = 291;
//if we loaded two files (or should have done) then add the number of values in that file (54)
if (testCase == KVPAttacherTestCase.TwoFilesWithPrimaryKey)
{
expectedRows += 54;
}
Assert.AreEqual(expectedRows, tbl.GetRowCount());
}
finally
{
p.DeleteInDatabase();
tbl.Drop();
}
}
public void Extract_ProjectSpecificCatalogue_FilterReference()
{
//make the catalogue a custom catalogue for this project
CustomExtractableDataSet.Project_ID = _project.ID;
CustomExtractableDataSet.SaveToDatabase();
var pipe = SetupPipeline();
pipe.Name = "Extract_ProjectSpecificCatalogue_FilterReference Pipe";
pipe.SaveToDatabase();
var rootContainer = new FilterContainer(DataExportRepository);
_selectedDataSet.RootFilterContainer_ID = rootContainer.ID;
_selectedDataSet.SaveToDatabase();
var filter = new DeployedExtractionFilter(DataExportRepository, "monkeys only", rootContainer);
filter.WhereSQL = "SuperSecretThing = 'monkeys can all secretly fly'";
filter.SaveToDatabase();
rootContainer.AddChild(filter);
//get rid of any lingering joins
foreach (JoinInfo j in CatalogueRepository.GetAllObjects <JoinInfo>())
{
j.DeleteInDatabase();
}
//add the ability to join the two tables in the query
var idCol = _extractableDataSet.Catalogue.GetAllExtractionInformation(ExtractionCategory.Core).Single(c => c.IsExtractionIdentifier).ColumnInfo;
var otherIdCol = CustomCatalogue.GetAllExtractionInformation(ExtractionCategory.ProjectSpecific).Single(e => e.GetRuntimeName().Equals("PrivateID")).ColumnInfo;
new JoinInfo(CatalogueRepository, idCol, otherIdCol, ExtractionJoinType.Left, null);
new SelectedDataSetsForcedJoin(DataExportRepository, _selectedDataSet, CustomTableInfo);
//generate a new request (this will include the newly created column)
_request = new ExtractDatasetCommand(_configuration, new ExtractableDatasetBundle(_extractableDataSet));
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("TestTable");
tbl.Truncate();
using (var blk = tbl.BeginBulkInsert())
{
var dt = new DataTable();
dt.Columns.Add("PrivateID");
dt.Columns.Add("Name");
dt.Columns.Add("DateOfBirth");
dt.Rows.Add(new object[] { "Priv_12345", "Bob", "2001-01-01" });
dt.Rows.Add(new object[] { "Priv_wtf11", "Frank", "2001-10-29" });
blk.Upload(dt);
}
ExtractionPipelineUseCase useCase;
IExecuteDatasetExtractionDestination results;
Execute(out useCase, out results);
var mainDataTableCsv = results.DirectoryPopulated.GetFiles().Single(f => f.Name.Equals("TestTable.csv"));
Assert.IsNotNull(mainDataTableCsv);
var lines = File.ReadAllLines(mainDataTableCsv.FullName);
Assert.AreEqual("ReleaseID,Name,DateOfBirth", lines[0]);
Assert.AreEqual("Pub_54321,Bob,2001-01-01", lines[1]);
Assert.AreEqual(2, lines.Length);
}
public void Extract_ProjectSpecificCatalogue_AppendedColumn()
{
//make the catalogue a custom catalogue for this project
CustomExtractableDataSet.Project_ID = _project.ID;
CustomExtractableDataSet.SaveToDatabase();
var pipe = SetupPipeline();
pipe.Name = "Extract_ProjectSpecificCatalogue_AppendedColumn Pipe";
pipe.SaveToDatabase();
var extraColumn = CustomCatalogue.GetAllExtractionInformation(ExtractionCategory.ProjectSpecific).Single(e => e.GetRuntimeName().Equals("SuperSecretThing"));
var asExtractable = new ExtractableColumn(DataExportRepository, _extractableDataSet, _configuration, extraColumn, 10, extraColumn.SelectSQL);
//get rid of any lingering joins
foreach (JoinInfo j in CatalogueRepository.GetAllObjects <JoinInfo>())
{
j.DeleteInDatabase();
}
//add the ability to join the two tables in the query
var idCol = _extractableDataSet.Catalogue.GetAllExtractionInformation(ExtractionCategory.Core).Single(c => c.IsExtractionIdentifier).ColumnInfo;
var otherIdCol = CustomCatalogue.GetAllExtractionInformation(ExtractionCategory.ProjectSpecific).Single(e => e.GetRuntimeName().Equals("PrivateID")).ColumnInfo;
new JoinInfo(CatalogueRepository, idCol, otherIdCol, ExtractionJoinType.Left, null);
//generate a new request (this will include the newly created column)
_request = new ExtractDatasetCommand(_configuration, new ExtractableDatasetBundle(_extractableDataSet));
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("TestTable");
tbl.Truncate();
using (var blk = tbl.BeginBulkInsert())
{
var dt = new DataTable();
dt.Columns.Add("PrivateID");
dt.Columns.Add("Name");
dt.Columns.Add("DateOfBirth");
dt.Rows.Add(new object[] { "Priv_12345", "Bob", "2001-01-01" });
dt.Rows.Add(new object[] { "Priv_wtf11", "Frank", "2001-10-29" });
blk.Upload(dt);
}
ExtractionPipelineUseCase useCase;
IExecuteDatasetExtractionDestination results;
Execute(out useCase, out results);
var mainDataTableCsv = results.DirectoryPopulated.GetFiles().Single(f => f.Name.Equals("TestTable.csv"));
Assert.IsNotNull(mainDataTableCsv);
Assert.AreEqual("TestTable.csv", mainDataTableCsv.Name);
var lines = File.ReadAllLines(mainDataTableCsv.FullName);
Assert.AreEqual("ReleaseID,Name,DateOfBirth,SuperSecretThing", lines[0]);
var bobLine = lines.Single(l => l.StartsWith("Pub_54321,Bob"));
var frankLine = lines.Single(l => l.StartsWith("Pub_11ftw,Frank"));
Assert.AreEqual("Pub_54321,Bob,2001-01-01,monkeys can all secretly fly", bobLine);
Assert.AreEqual("Pub_11ftw,Frank,2001-10-29,the wizard of OZ was a man behind a machine", frankLine);
asExtractable.DeleteInDatabase();
}
public void SetupExampleTables()
{
string sql =
@"CREATE TABLE [dbo].[Tests](
[chi] [varchar](10) NULL,
[Date] [datetime] NULL,
[hb_extract] [varchar](1) NULL,
[TestId] [int] NOT NULL,
CONSTRAINT [PK_Tests] PRIMARY KEY CLUSTERED
(
[TestId] ASC
)
)
GO
CREATE TABLE [dbo].[Results](
[TestId] [int] NOT NULL,
[Measure] [varchar](10) NOT NULL,
[Value] [int] NULL,
CONSTRAINT [PK_Results] PRIMARY KEY CLUSTERED
(
[TestId] ASC,
[Measure] ASC
)
)
GO
ALTER TABLE [dbo].[Results] WITH CHECK ADD CONSTRAINT [FK_Results_Tests] FOREIGN KEY([TestId])
REFERENCES [dbo].[Tests] ([TestId])
GO";
var server = DiscoveredDatabaseICanCreateRandomTablesIn.Server;
using (var con = server.GetConnection())
{
con.Open();
UsefulStuff.ExecuteBatchNonQuery(sql, con);
}
var importer1 = new TableInfoImporter(CatalogueRepository, DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("Tests"));
var importer2 = new TableInfoImporter(CatalogueRepository, DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("Results"));
importer1.DoImport(out t1, out c1);
importer2.DoImport(out t2, out c2);
var engineer1 = new ForwardEngineerCatalogue(t1, c1, true);
var engineer2 = new ForwardEngineerCatalogue(t2, c2, true);
engineer1.ExecuteForwardEngineering(out cata1, out cataItems1, out eis1);
engineer2.ExecuteForwardEngineering(out cata2, out cataItems2, out eis2);
new JoinInfo(CatalogueRepository,
c1.Single(e => e.GetRuntimeName().Equals("TestId")),
c2.Single(e => e.GetRuntimeName().Equals("TestId")),
ExtractionJoinType.Left, null);
_anoTable = new ANOTable(CatalogueRepository, ANOStore_ExternalDatabaseServer, "ANOTes", "T");
_anoTable.NumberOfCharactersToUseInAnonymousRepresentation = 10;
_anoTable.SaveToDatabase();
_anoTable.PushToANOServerAsNewTable("int", new ThrowImmediatelyCheckNotifier());
_comboCata = new Catalogue(CatalogueRepository, "Combo Catalogue");
//pk
var ciTestId = new CatalogueItem(CatalogueRepository, _comboCata, "TestId");
var colTestId = c1.Single(c => c.GetRuntimeName().Equals("TestId"));
ciTestId.ColumnInfo_ID = colTestId.ID;
ciTestId.SaveToDatabase();
var eiTestId = new ExtractionInformation(CatalogueRepository, ciTestId, colTestId, colTestId.Name);
//Measure
var ciMeasure = new CatalogueItem(CatalogueRepository, _comboCata, "Measuree");
var colMeasure = c2.Single(c => c.GetRuntimeName().Equals("Measure"));
ciMeasure.ColumnInfo_ID = colMeasure.ID;
ciMeasure.SaveToDatabase();
var eiMeasure = new ExtractionInformation(CatalogueRepository, ciMeasure, colMeasure, colMeasure.Name);
//Date
var ciDate = new CatalogueItem(CatalogueRepository, _comboCata, "Dat");
var colDate = c1.Single(c => c.GetRuntimeName().Equals("Date"));
ciDate.ColumnInfo_ID = colDate.ID;
ciDate.SaveToDatabase();
var eiDate = new ExtractionInformation(CatalogueRepository, ciDate, colDate, colDate.Name);
var destDatabaseName = TestDatabaseNames.GetConsistentName("ANOMigrationTwoTableTests");
_destinationDatabase = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(destDatabaseName);
_destinationDatabase.Create(true);
}
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();
}
public void SetupExampleTable()
{
var remnantTable = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("ANOMigration");
if (remnantTable.Exists())
{
remnantTable.Drop();
}
DeleteANOEndpoint();
ANOTable remnantANO = CatalogueRepository.GetAllObjects <ANOTable>().SingleOrDefault(a => a.TableName.Equals("ANOCondition"));
if (remnantANO != null)
{
remnantANO.DeleteInDatabase();
}
//cleanup
foreach (var remnant in CatalogueRepository.GetAllObjects <TableInfo>().Where(t => t.GetRuntimeName().Equals(TableName)))
{
remnant.DeleteInDatabase();
}
const string sql = @"
CREATE TABLE [ANOMigration](
[AdmissionDate] [datetime] NOT NULL,
[DischargeDate] [datetime] NOT NULL,
[Condition1] [varchar](4) NOT NULL,
[Condition2] [varchar](4) NULL,
[Condition3] [varchar](4) NULL,
[Condition4] [varchar](4) NULL,
[CHI] [varchar](10) NOT NULL
CONSTRAINT [PK_ANOMigration] PRIMARY KEY CLUSTERED
(
[AdmissionDate] ASC,
[Condition1] ASC,
[CHI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x000001B300000000 AS DateTime), CAST(0x000001B600000000 AS DateTime), N'Z61', N'Z29', NULL, N'Z11', N'0809003082')
INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000021D00000000 AS DateTime), CAST(0x0000022600000000 AS DateTime), N'P024', N'Q230', NULL,N'Z11', N'1610007810')
INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000032900000000 AS DateTime), CAST(0x0000032A00000000 AS DateTime), N'L73', NULL, NULL, NULL, N'2407011022')
INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x000004EA00000000 AS DateTime), CAST(0x000004EA00000000 AS DateTime), N'Y523', N'Z29', NULL, NULL, N'1104015472')
INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000060300000000 AS DateTime), CAST(0x0000060800000000 AS DateTime), N'F721', N'B871', NULL, NULL, N'0203025927')
INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000065300000000 AS DateTime), CAST(0x0000065700000000 AS DateTime), N'Z914', N'J398', NULL, NULL, N'2702024715')
INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000070100000000 AS DateTime), CAST(0x0000070800000000 AS DateTime), N'N009', N'V698', NULL, NULL, N'1610007810')
INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000077000000000 AS DateTime), CAST(0x0000077200000000 AS DateTime), N'E44', N'J050', N'Q560', NULL, N'1610007810')
INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x000007E800000000 AS DateTime), CAST(0x000007EA00000000 AS DateTime), N'Q824', NULL, NULL, NULL, N'1110029231')
INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000087700000000 AS DateTime), CAST(0x0000087F00000000 AS DateTime), N'T020', NULL, NULL, NULL, N'2110021261')
INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000088A00000000 AS DateTime), CAST(0x0000089300000000 AS DateTime), N'G009', NULL, NULL, NULL, N'0706013071')
INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x000008CA00000000 AS DateTime), CAST(0x000008D100000000 AS DateTime), N'T47', N'H311', N'O037', NULL, N'1204057592')";
var server = DiscoveredDatabaseICanCreateRandomTablesIn.Server;
using (var con = server.GetConnection())
{
con.Open();
server.GetCommand(sql, con).ExecuteNonQuery();
}
var table = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable(TableName);
TableInfoImporter importer = new TableInfoImporter(CatalogueRepository, table);
importer.DoImport(out _tableInfo, out _columnInfos);
//Configure the structure of the ANO transform we want - identifiers should have 3 characters and 2 ints and end with _C
_anoConditionTable = new ANOTable(CatalogueRepository, ANOStore_ExternalDatabaseServer, "ANOCondition", "C");
_anoConditionTable.NumberOfCharactersToUseInAnonymousRepresentation = 3;
_anoConditionTable.NumberOfIntegersToUseInAnonymousRepresentation = 2;
_anoConditionTable.SaveToDatabase();
_anoConditionTable.PushToANOServerAsNewTable("varchar(4)", new ThrowImmediatelyCheckNotifier());
}
