public void CreateANOVersionTest_IntIdentity() { var dbName = TestDatabaseNames.GetConsistentName("CreateANOVersionTest"); //setup the anonymisation database (destination) var db = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(dbName); db.Create(true); //Create this table in the scratch database var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable("MyTable", new[] { new DatabaseColumnRequest("id", "int identity(1,1)", false) { IsPrimaryKey = true }, new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 10), false) }); TableInfo ti; ColumnInfo[] cols; var cata = Import(tbl, out ti, out cols); var planManager = new ForwardEngineerANOCataloguePlanManager(RepositoryLocator, cata); planManager.TargetDatabase = db; var nameCol = cols.Single(c => c.GetRuntimeName().Equals("Name")); //setup test rules for migrator planManager.Plans[nameCol].Plan = Plan.Drop; //rules should pass checks planManager.Check(new ThrowImmediatelyCheckNotifier()); var engine = new ForwardEngineerANOCatalogueEngine(RepositoryLocator, planManager); engine.Execute(); var anoCatalogue = CatalogueRepository.GetAllObjects <Catalogue>().Single(c => c.Folder.Path.StartsWith("\\ano")); Assert.IsTrue(anoCatalogue.Exists()); //should only be one (the id column Assert.AreEqual(1, anoCatalogue.CatalogueItems.Length); var idColInAnoDatabase = anoCatalogue.CatalogueItems[0].ColumnInfo; Assert.AreEqual("int", idColInAnoDatabase.Data_type); db.Drop(); var exports = CatalogueRepository.GetAllObjects <ObjectExport>().Count(); var imports = CatalogueRepository.GetAllObjects <ObjectImport>().Count(); Assert.AreEqual(exports, imports); Assert.IsTrue(exports > 0); }
public void PlanManagementTest() { var dbName = TestDatabaseNames.GetConsistentName("PlanManagementTests"); var db = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(dbName); db.Create(true); BulkTestsData bulk = new BulkTestsData(CatalogueRepository, DiscoveredDatabaseICanCreateRandomTablesIn, 100); bulk.SetupTestData(); bulk.ImportAsCatalogue(); var planManager = new ForwardEngineerANOCataloguePlanManager(RepositoryLocator, bulk.catalogue); planManager.TargetDatabase = db; //no operations are as yet configured Assert.DoesNotThrow(() => planManager.Check(new ThrowImmediatelyCheckNotifier())); //create a table with the same name in the endpoint database to confirm that that's a problem db.CreateTable(bulk.tableInfo.GetRuntimeName(), new DatabaseColumnRequest[] { new DatabaseColumnRequest("fish", "varchar(100)") }); //throws because table already exists Assert.Throws <Exception>(() => planManager.Check(new ThrowImmediatelyCheckNotifier())); db.ExpectTable(bulk.tableInfo.GetRuntimeName()).Drop(); //back to being fine again Assert.DoesNotThrow(() => planManager.Check(new ThrowImmediatelyCheckNotifier())); //setup test rules for migrator CreateMigrationRules(planManager, bulk); //rules should pass Assert.DoesNotThrow(() => planManager.Check(new ThrowImmediatelyCheckNotifier())); var chi = bulk.GetColumnInfo("chi"); Assert.Throws <Exception>(() => { planManager.GetPlanForColumnInfo(chi).Plan = Plan.Drop; planManager.GetPlanForColumnInfo(chi).Check(new ThrowImmediatelyCheckNotifier()); } , "Should not be able to drop primary key column"); db.Drop(); }
public void SetupCatalogues() { _cata1 = new Catalogue(CatalogueRepository, "Dataset1"); _cata2 = new Catalogue(CatalogueRepository, "Dataset2"); _t1 = new TableInfo(CatalogueRepository, "T1"); _t2 = new TableInfo(CatalogueRepository, "T2"); _c1 = new ColumnInfo(CatalogueRepository, "PrivateIdentifierA", "varchar(10)", _t1); _c2 = new ColumnInfo(CatalogueRepository, "PrivateIdentifierB", "int", _t2); _ci1 = new CatalogueItem(CatalogueRepository, _cata1, "PrivateIdentifierA"); _ci2 = new CatalogueItem(CatalogueRepository, _cata2, "PrivateIdentifierB"); _extractionInfo1 = new ExtractionInformation(CatalogueRepository, _ci1, _c1, _c1.ToString()); _extractionInfo2 = new ExtractionInformation(CatalogueRepository, _ci2, _c2, _c2.ToString()); cohortDatabaseName = TestDatabaseNames.GetConsistentName("Tests_CreateCohortDatabaseWizard"); }
public void CreateANOVersionTest() { var dbName = TestDatabaseNames.GetConsistentName("CreateANOVersionTest"); var db = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(dbName); db.Create(true); BulkTestsData bulk = new BulkTestsData(CatalogueRepository, DiscoveredDatabaseICanCreateRandomTablesIn, 100); bulk.SetupTestData(); bulk.ImportAsCatalogue(); var planManager = new ForwardEngineerANOCataloguePlanManager(RepositoryLocator, bulk.catalogue); planManager.TargetDatabase = db; //setup test rules for migrator CreateMigrationRules(planManager, bulk); //rules should pass checks Assert.DoesNotThrow(() => planManager.Check(new ThrowImmediatelyCheckNotifier())); var engine = new ForwardEngineerANOCatalogueEngine(RepositoryLocator, planManager); engine.Execute(); var anoCatalogue = CatalogueRepository.GetAllObjects <Catalogue>().Single(c => c.Folder.Path.StartsWith("\\ano")); Assert.IsTrue(anoCatalogue.Exists()); db.Drop(); var exports = CatalogueRepository.GetAllObjects <ObjectExport>().Count(); var imports = CatalogueRepository.GetAllObjects <ObjectImport>().Count(); Assert.AreEqual(exports, imports); Assert.IsTrue(exports > 0); }
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(); }
public void Test_DatabaseTypeQueryWithParameter_IntParameter(DatabaseType dbType) { //Pick the destination server var tableName = TestDatabaseNames.GetConsistentName("tbl"); //make sure there's a database ready to receive the data var db = GetCleanedServer(dbType); db.Create(true); //this is the table we are uploading var dt = new DataTable(); dt.Columns.Add("numbercol"); dt.Rows.Add(10); dt.Rows.Add(15); dt.Rows.Add(20); dt.Rows.Add(25); dt.TableName = tableName; try { ///////////////////////UPLOAD THE DataTable TO THE DESTINATION//////////////////////////////////////////// var uploader = new DataTableUploadDestination(); uploader.PreInitialize(db, new ThrowImmediatelyDataLoadJob()); uploader.ProcessPipelineData(dt, new ThrowImmediatelyDataLoadJob(), new GracefulCancellationToken()); uploader.Dispose(new ThrowImmediatelyDataLoadJob(), null); var tbl = db.ExpectTable(tableName); var importer = new TableInfoImporter(CatalogueRepository, tbl); importer.DoImport(out var ti, out var ci); var engineer = new ForwardEngineerCatalogue(ti, ci, true); engineer.ExecuteForwardEngineering(out var cata, out var cis, out var ei); ///////////////////////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////THE ACTUAL PROPER TEST//////////////////////////////////// //create an extraction filter var extractionInformation = ei.Single(); var filter = new ExtractionFilter(CatalogueRepository, "Filter by numbers", extractionInformation); filter.WhereSQL = extractionInformation.SelectSQL + " = @n"; filter.SaveToDatabase(); //create the parameters for filter (no globals, masters or scope adjacent parameters) new ParameterCreator(filter.GetFilterFactory(), null, null).CreateAll(filter, null); var p = filter.GetAllParameters().Single(); Assert.AreEqual("@n", p.ParameterName); p.ParameterSQL = p.ParameterSQL.Replace("varchar(50)", "int"); //make it int p.Value = "20"; p.SaveToDatabase(); var qb = new QueryBuilder(null, null); qb.AddColumn(extractionInformation); qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(new MemoryCatalogueRepository(), null, new[] { filter }, FilterContainerOperation.AND); using (var con = db.Server.GetConnection()) { con.Open(); string sql = qb.SQL; var cmd = db.Server.GetCommand(sql, con); var r = cmd.ExecuteReader(); Assert.IsTrue(r.Read()); Assert.AreEqual( 20, r[extractionInformation.GetRuntimeName()]); } /////////////////////////////////////////////////////////////////////////////////////// } finally { db.Drop(); } }
public void SQLServerDestination() { DiscoveredDatabase dbToExtractTo = null; var ci = new CatalogueItem(CatalogueRepository, _catalogue, "YearOfBirth"); var columnToTransform = _columnInfos.Single(c => c.GetRuntimeName().Equals("DateOfBirth", StringComparison.CurrentCultureIgnoreCase)); string transform = "YEAR(" + columnToTransform.Name + ")"; var ei = new ExtractionInformation(CatalogueRepository, ci, columnToTransform, transform); ei.Alias = "YearOfBirth"; ei.ExtractionCategory = ExtractionCategory.Core; ei.SaveToDatabase(); //make it part of the ExtractionConfiguration var newColumn = new ExtractableColumn(DataExportRepository, _selectedDataSet.ExtractableDataSet, (ExtractionConfiguration)_selectedDataSet.ExtractionConfiguration, ei, 0, ei.SelectSQL); newColumn.Alias = ei.Alias; newColumn.SaveToDatabase(); _extractableColumns.Add(newColumn); //recreate the extraction command so it gets updated with the new column too. _request = new ExtractDatasetCommand(_configuration, _extractableCohort, new ExtractableDatasetBundle(_extractableDataSet), _extractableColumns, new HICProjectSalt(_project), new ExtractionDirectory(@"C:\temp\", _configuration)); try { _configuration.Name = "ExecuteFullExtractionToDatabaseMSSqlDestinationTest"; _configuration.SaveToDatabase(); ExtractionPipelineUseCase execute; IExecuteDatasetExtractionDestination result; var dbname = TestDatabaseNames.GetConsistentName(_project.Name + "_" + _project.ProjectNumber); dbToExtractTo = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(dbname); if (dbToExtractTo.Exists()) { dbToExtractTo.Drop(); } base.Execute(out execute, out result); var destinationTable = dbToExtractTo.ExpectTable(_expectedTableName); Assert.IsTrue(destinationTable.Exists()); var dt = destinationTable.GetDataTable(); Assert.AreEqual(1, dt.Rows.Count); Assert.AreEqual(_cohortKeysGenerated[_cohortKeysGenerated.Keys.First()].Trim(), dt.Rows[0]["ReleaseID"]); Assert.AreEqual(new DateTime(2001, 1, 1), dt.Rows[0]["DateOfBirth"]); Assert.AreEqual(2001, dt.Rows[0]["YearOfBirth"]); Assert.AreEqual(columnToTransform.Data_type, destinationTable.DiscoverColumn("DateOfBirth").DataType.SQLType); Assert.AreEqual("int", destinationTable.DiscoverColumn("YearOfBirth").DataType.SQLType); } finally { if (_extractionServer != null) { _extractionServer.DeleteInDatabase(); } if (dbToExtractTo != null) { dbToExtractTo.Drop(); } } }
// This no longer copies between servers, but the original test didn't guarantee that would happen anyway public void CloneDatabaseAndTable() { string testLiveDatabaseName = TestDatabaseNames.GetConsistentName("TEST"); var testDb = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(testLiveDatabaseName); var raw = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(testLiveDatabaseName + "_RAW"); foreach (DiscoveredDatabase db in new[] { raw, testDb }) { if (db.Exists()) { foreach (DiscoveredTable table in db.DiscoverTables(true)) { table.Drop(); } db.Drop(); } } DiscoveredServerICanCreateRandomDatabasesAndTablesOn.CreateDatabase(testLiveDatabaseName); Assert.IsTrue(testDb.Exists()); testDb.CreateTable("Table_1", new[] { new DatabaseColumnRequest("Id", "int") }); //clone the builder var builder = new SqlConnectionStringBuilder(DiscoveredServerICanCreateRandomDatabasesAndTablesOn.Builder.ConnectionString) { InitialCatalog = testLiveDatabaseName }; var dbConfiguration = new HICDatabaseConfiguration(new DiscoveredServer(builder), null, new ServerDefaults(CatalogueRepository)); var cloner = new DatabaseCloner(dbConfiguration); try { var cloneDb = cloner.CreateDatabaseForStage(LoadBubble.Raw); //confirm database appeared Assert.IsTrue(DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(testLiveDatabaseName + "_RAW").Exists()); //now create a catalogue and wire it SetUp to the table TEST on the test database server Catalogue cata = SetupATestCatalogue(builder, testLiveDatabaseName, "Table_1"); //now clone the catalogue data structures to MachineName foreach (TableInfo tableInfo in cata.GetTableInfoList(false)) { cloner.CreateTablesInDatabaseFromCatalogueInfo(new ThrowImmediatelyDataLoadEventListener(), tableInfo, LoadBubble.Raw); } Assert.IsTrue(raw.Exists()); Assert.IsTrue(raw.ExpectTable("Table_1").Exists()); } finally { cloner.LoadCompletedSoDispose(ExitCodeType.Success, new ThrowImmediatelyDataLoadEventListener()); while (toCleanUp.Count > 0) { try { toCleanUp.Pop().DeleteInDatabase(); } catch (Exception e) { //always clean SetUp everything Console.WriteLine(e); } } } }
public void SQLServerDestination(bool lookupsEtc) { DiscoveredDatabase dbToExtractTo = null; var ci = new CatalogueItem(CatalogueRepository, _catalogue, "YearOfBirth"); _columnToTransform = _columnInfos.Single(c => c.GetRuntimeName().Equals("DateOfBirth", StringComparison.CurrentCultureIgnoreCase)); string transform = "YEAR(" + _columnToTransform.Name + ")"; if (_catalogue.GetAllExtractionInformation(ExtractionCategory.Any).All(ei => ei.GetRuntimeName() != "YearOfBirth")) { var ei = new ExtractionInformation(CatalogueRepository, ci, _columnToTransform, transform); ei.Alias = "YearOfBirth"; ei.ExtractionCategory = ExtractionCategory.Core; ei.SaveToDatabase(); //make it part of the ExtractionConfiguration var newColumn = new ExtractableColumn(DataExportRepository, _selectedDataSet.ExtractableDataSet, (ExtractionConfiguration)_selectedDataSet.ExtractionConfiguration, ei, 0, ei.SelectSQL); newColumn.Alias = ei.Alias; newColumn.SaveToDatabase(); _extractableColumns.Add(newColumn); } if (lookupsEtc) { CreateLookupsEtc(); } try { _configuration.Name = "ExecuteFullExtractionToDatabaseMSSqlDestinationTest"; _configuration.SaveToDatabase(); var dbname = TestDatabaseNames.GetConsistentName(_project.Name + "_" + _project.ProjectNumber); dbToExtractTo = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(dbname); if (dbToExtractTo.Exists()) { dbToExtractTo.Drop(); } base.ExecuteRunner(); var destinationTable = dbToExtractTo.ExpectTable(_expectedTableName); Assert.IsTrue(destinationTable.Exists()); var dt = destinationTable.GetDataTable(); Assert.AreEqual(1, dt.Rows.Count); Assert.AreEqual(_cohortKeysGenerated[_cohortKeysGenerated.Keys.First()].Trim(), dt.Rows[0]["ReleaseID"]); Assert.AreEqual(new DateTime(2001, 1, 1), dt.Rows[0]["DateOfBirth"]); Assert.AreEqual(2001, dt.Rows[0]["YearOfBirth"]); Assert.AreEqual(_columnToTransform.Data_type, destinationTable.DiscoverColumn("DateOfBirth").DataType.SQLType); Assert.AreEqual("int", destinationTable.DiscoverColumn("YearOfBirth").DataType.SQLType); if (lookupsEtc) { AssertLookupsEtcExist(dbToExtractTo); } } finally { if (dbToExtractTo != null && dbToExtractTo.Exists()) { dbToExtractTo.Drop(); } _pipeline?.DeleteInDatabase(); } }
public void CreateANOVersionTest_LookupsAndExtractionInformations() { var dbName = TestDatabaseNames.GetConsistentName("CreateANOVersionTest"); var db = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(dbName); db.Create(true); BulkTestsData bulk = new BulkTestsData(CatalogueRepository, DiscoveredDatabaseICanCreateRandomTablesIn, 100); bulk.SetupTestData(); bulk.ImportAsCatalogue(); //Create a lookup table on the server var lookupTbl = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable("z_sexLookup", new[] { new DatabaseColumnRequest("Code", "varchar(1)") { IsPrimaryKey = true }, new DatabaseColumnRequest("hb_Code", "varchar(1)") { IsPrimaryKey = true }, new DatabaseColumnRequest("Description", "varchar(100)") }); //import a reference to the table TableInfoImporter importer = new TableInfoImporter(CatalogueRepository, lookupTbl); ColumnInfo[] lookupColumnInfos; TableInfo lookupTableInfo; importer.DoImport(out lookupTableInfo, out lookupColumnInfos); //Create a Lookup reference var ciSex = bulk.catalogue.CatalogueItems.Single(c => c.Name == "sex"); var ciHb = bulk.catalogue.CatalogueItems.Single(c => c.Name == "hb_extract"); var eiChi = bulk.extractionInformations.Single(ei => ei.GetRuntimeName() == "chi"); eiChi.IsExtractionIdentifier = true; eiChi.SaveToDatabase(); var eiCentury = bulk.extractionInformations.Single(ei => ei.GetRuntimeName() == "century"); eiCentury.HashOnDataRelease = true; eiCentury.ExtractionCategory = ExtractionCategory.Internal; eiCentury.SaveToDatabase(); //add a transform var eiPostcode = bulk.extractionInformations.Single(ei => ei.GetRuntimeName() == "current_postcode"); eiPostcode.SelectSQL = string.Format("LEFT(10,{0}.[current_postcode])", eiPostcode.ColumnInfo.TableInfo.Name); eiPostcode.Alias = "MyMutilatedColumn"; eiPostcode.SaveToDatabase(); //add a combo transform var ciComboCol = new CatalogueItem(CatalogueRepository, bulk.catalogue, "ComboColumn"); var colForename = bulk.columnInfos.Single(c => c.GetRuntimeName() == "forename"); var colSurname = bulk.columnInfos.Single(c => c.GetRuntimeName() == "surname"); var eiComboCol = new ExtractionInformation(CatalogueRepository, ciComboCol, colForename, colForename + " + ' ' + " + colSurname); eiComboCol.Alias = "ComboColumn"; eiComboCol.SaveToDatabase(); var eiDataLoadRunId = bulk.extractionInformations.Single(ei => ei.GetRuntimeName().Equals(SpecialFieldNames.DataLoadRunID)); eiDataLoadRunId.DeleteInDatabase(); var lookup = new Lookup(CatalogueRepository, lookupColumnInfos[2], ciSex.ColumnInfo, lookupColumnInfos[0], ExtractionJoinType.Left, null); //now lets make it worse, lets assume the sex code changes per healthboard therefore the join to the lookup requires both fields sex and hb_extract var compositeLookup = new LookupCompositeJoinInfo(CatalogueRepository, lookup, ciHb.ColumnInfo, lookupColumnInfos[1]); //now lets make the _Desc field in the original Catalogue int orderToInsertDescriptionFieldAt = ciSex.ExtractionInformation.Order; //bump everyone down 1 foreach (var toBumpDown in bulk.catalogue.CatalogueItems.Select(ci => ci.ExtractionInformation).Where(e => e != null && e.Order > orderToInsertDescriptionFieldAt)) { toBumpDown.Order++; toBumpDown.SaveToDatabase(); } var ciDescription = new CatalogueItem(CatalogueRepository, bulk.catalogue, "Sex_Desc"); var eiDescription = new ExtractionInformation(CatalogueRepository, ciDescription, lookupColumnInfos[2], lookupColumnInfos[2].Name); eiDescription.Alias = "Sex_Desc"; eiDescription.Order = orderToInsertDescriptionFieldAt + 1; eiDescription.ExtractionCategory = ExtractionCategory.Supplemental; eiDescription.SaveToDatabase(); bulk.catalogue.ClearAllInjections(); //check it worked QueryBuilder qb = new QueryBuilder(null, null); qb.AddColumnRange(bulk.catalogue.GetAllExtractionInformation(ExtractionCategory.Any)); //The query builder should be able to succesfully create SQL Console.WriteLine(qb.SQL); //there should be 2 tables involved in the query [z_sexLookup] and [BulkData] Assert.AreEqual(2, qb.TablesUsedInQuery.Count); //the query builder should have identified the lookup Assert.AreEqual(lookup, qb.GetDistinctRequiredLookups().Single()); //////////////////////////////////////////////////////////////////////////////////////The Actual Bit Being Tested//////////////////////////////////////////////////// var planManager = new ForwardEngineerANOCataloguePlanManager(RepositoryLocator, bulk.catalogue); planManager.TargetDatabase = db; //setup test rules for migrator CreateMigrationRules(planManager, bulk); //rules should pass checks Assert.DoesNotThrow(() => planManager.Check(new ThrowImmediatelyCheckNotifier())); var engine = new ForwardEngineerANOCatalogueEngine(RepositoryLocator, planManager); engine.Execute(); //////////////////////////////////////////////////////////////////////////////////////End The Actual Bit Being Tested//////////////////////////////////////////////////// var anoCatalogue = CatalogueRepository.GetAllObjects <Catalogue>().Single(c => c.Folder.Path.StartsWith("\\ano")); Assert.IsTrue(anoCatalogue.Exists()); //The new Catalogue should have the same number of ExtractionInformations var eiSource = bulk.catalogue.GetAllExtractionInformation(ExtractionCategory.Any).OrderBy(ei => ei.Order).ToArray(); var eiDestination = anoCatalogue.GetAllExtractionInformation(ExtractionCategory.Any).OrderBy(ei => ei.Order).ToArray(); Assert.AreEqual(eiSource.Length, eiDestination.Length, "Both the new and the ANO catalogue should have the same number of ExtractionInformations (extractable columns)"); for (int i = 0; i < eiSource.Length; i++) { Assert.AreEqual(eiSource[i].Order, eiDestination[i].Order, "ExtractionInformations in the source and destination Catalogue should have the same order"); Assert.AreEqual(eiSource[i].GetRuntimeName(), eiDestination[i].GetRuntimeName().Replace("ANO", ""), "ExtractionInformations in the source and destination Catalogue should have the same names (excluding ANO prefix)"); Assert.AreEqual(eiSource[i].ExtractionCategory, eiDestination[i].ExtractionCategory, "Old / New ANO ExtractionInformations did not match on ExtractionCategory"); Assert.AreEqual(eiSource[i].IsExtractionIdentifier, eiDestination[i].IsExtractionIdentifier, "Old / New ANO ExtractionInformations did not match on IsExtractionIdentifier"); Assert.AreEqual(eiSource[i].HashOnDataRelease, eiDestination[i].HashOnDataRelease, "Old / New ANO ExtractionInformations did not match on HashOnDataRelease"); Assert.AreEqual(eiSource[i].IsPrimaryKey, eiDestination[i].IsPrimaryKey, "Old / New ANO ExtractionInformations did not match on IsPrimaryKey"); } //check it worked QueryBuilder qbdestination = new QueryBuilder(null, null); qbdestination.AddColumnRange(anoCatalogue.GetAllExtractionInformation(ExtractionCategory.Any)); //The query builder should be able to succesfully create SQL Console.WriteLine(qbdestination.SQL); var anoEiPostcode = anoCatalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(ei => ei.GetRuntimeName().Equals("MyMutilatedColumn")); //The transform on postcode should have been refactored to the new table name and preserve the scalar function LEFT... Assert.AreEqual(string.Format("LEFT(10,{0}.[current_postcode])", anoEiPostcode.ColumnInfo.TableInfo.GetFullyQualifiedName()), anoEiPostcode.SelectSQL); var anoEiComboCol = anoCatalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(ei => ei.GetRuntimeName().Equals("ComboColumn")); //The transform on postcode should have been refactored to the new table name and preserve the scalar function LEFT... Assert.AreEqual(string.Format("{0}.[forename] + ' ' + {0}.[surname]", anoEiPostcode.ColumnInfo.TableInfo.GetFullyQualifiedName()), anoEiComboCol.SelectSQL); //there should be 2 tables involved in the query [z_sexLookup] and [BulkData] Assert.AreEqual(2, qbdestination.TablesUsedInQuery.Count); //the query builder should have identified the lookup but it should be the new one not the old one Assert.AreEqual(1, qbdestination.GetDistinctRequiredLookups().Count(), "New query builder for ano catalogue did not correctly identify that there was a Lookup"); Assert.AreNotEqual(lookup, qbdestination.GetDistinctRequiredLookups().Single(), "New query builder for ano catalogue identified the OLD Lookup!"); Assert.AreEqual(1, qbdestination.GetDistinctRequiredLookups().Single().GetSupplementalJoins().Count(), "The new Lookup did not have the composite join key (sex/hb_extract)"); Assert.AreNotEqual(compositeLookup, qbdestination.GetDistinctRequiredLookups().Single().GetSupplementalJoins(), "New query builder for ano catalogue identified the OLD LookupCompositeJoinInfo!"); db.Drop(); var exports = CatalogueRepository.GetAllObjects <ObjectExport>().Count(); var imports = CatalogueRepository.GetAllObjects <ObjectImport>().Count(); Assert.AreEqual(exports, imports); Assert.IsTrue(exports > 0); }
public void CreateANOVersion_TestSkippingTables(bool tableInfoAlreadyExistsForSkippedTable, bool putPlanThroughSerialization) { var dbFrom = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(TestDatabaseNames.GetConsistentName("CreateANOVersion_TestSkippingTables_From")); var dbTo = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(TestDatabaseNames.GetConsistentName("CreateANOVersion_TestSkippingTables_To")); dbFrom.Create(true); dbTo.Create(true); try { var tblFromHeads = dbFrom.CreateTable("Heads", new[] { new DatabaseColumnRequest("SkullColor", "varchar(10)"), new DatabaseColumnRequest("Vertebrae", "varchar(25)") }); var cols = new[] { new DatabaseColumnRequest("SpineColor", "varchar(10)"), new DatabaseColumnRequest("Vertebrae", "varchar(25)") }; var tblFromNeck = dbFrom.CreateTable("Necks", cols); //Necks table already exists in the destination so will be skipped for migration but still needs to be imported var tblToNeck = dbTo.CreateTable("Necks", cols); TableInfo fromHeadsTableInfo; ColumnInfo[] fromHeadsColumnInfo; TableInfo fromNeckTableInfo; ColumnInfo[] fromNeckColumnInfo; TableInfo toNecksTableInfo = null; ColumnInfo[] toNecksColumnInfo = null; TableInfoImporter i1 = new TableInfoImporter(CatalogueRepository, tblFromHeads); i1.DoImport(out fromHeadsTableInfo, out fromHeadsColumnInfo); TableInfoImporter i2 = new TableInfoImporter(CatalogueRepository, tblFromNeck); i2.DoImport(out fromNeckTableInfo, out fromNeckColumnInfo); //Table already exists but does the in Catalogue reference exist? if (tableInfoAlreadyExistsForSkippedTable) { TableInfoImporter i3 = new TableInfoImporter(CatalogueRepository, tblToNeck); i3.DoImport(out toNecksTableInfo, out toNecksColumnInfo); } //Create a JoinInfo so the query builder knows how to connect the tables new JoinInfo(CatalogueRepository, fromHeadsColumnInfo.Single(c => c.GetRuntimeName().Equals("Vertebrae")), fromNeckColumnInfo.Single(c => c.GetRuntimeName().Equals("Vertebrae")), ExtractionJoinType.Inner, null ); var cataEngineer = new ForwardEngineerCatalogue(fromHeadsTableInfo, fromHeadsColumnInfo, true); Catalogue cata; CatalogueItem[] cataItems; ExtractionInformation[] extractionInformations; cataEngineer.ExecuteForwardEngineering(out cata, out cataItems, out extractionInformations); var cataEngineer2 = new ForwardEngineerCatalogue(fromNeckTableInfo, fromNeckColumnInfo, true); cataEngineer2.ExecuteForwardEngineering(cata); //4 extraction informations in from Catalogue (2 from Heads and 2 from Necks) Assert.AreEqual(cata.GetAllExtractionInformation(ExtractionCategory.Any).Count(), 4); //setup ANOTable on head var anoTable = new ANOTable(CatalogueRepository, ANOStore_ExternalDatabaseServer, "ANOSkullColor", "C"); anoTable.NumberOfCharactersToUseInAnonymousRepresentation = 10; anoTable.SaveToDatabase(); anoTable.PushToANOServerAsNewTable("varchar(10)", new ThrowImmediatelyCheckNotifier()); //////////////////The actual test!///////////////// var planManager = new ForwardEngineerANOCataloguePlanManager(RepositoryLocator, cata); //ano the table SkullColor var scPlan = planManager.GetPlanForColumnInfo(fromHeadsColumnInfo.Single(col => col.GetRuntimeName().Equals("SkullColor"))); scPlan.ANOTable = anoTable; scPlan.Plan = Plan.ANO; if (putPlanThroughSerialization) { var asString = JsonConvertExtensions.SerializeObject(planManager, RepositoryLocator); planManager = (ForwardEngineerANOCataloguePlanManager)JsonConvertExtensions.DeserializeObject(asString, typeof(ForwardEngineerANOCataloguePlanManager), RepositoryLocator); } //not part of serialization planManager.TargetDatabase = dbTo; planManager.SkippedTables.Add(fromNeckTableInfo);//skip the necks table because it already exists (ColumnInfos may or may not exist but physical table definetly does) var engine = new ForwardEngineerANOCatalogueEngine(RepositoryLocator, planManager); if (!tableInfoAlreadyExistsForSkippedTable) { var ex = Assert.Throws <Exception>(engine.Execute); Assert.IsTrue(Regex.IsMatch(ex.InnerException.Message, "Found '0' ColumnInfos called")); Assert.IsTrue(Regex.IsMatch(ex.InnerException.Message, "[Necks].[SpineColor]")); return; } else { engine.Execute(); } var newCata = CatalogueRepository.GetAllObjects <Catalogue>().Single(c => c.Name.Equals("ANOHeads")); Assert.IsTrue(newCata.Exists()); var newCataItems = newCata.CatalogueItems; Assert.AreEqual(newCataItems.Count(), 4); //should be extraction informations //all extraction informations should point to the new table location Assert.IsTrue(newCataItems.All(ci => ci.ExtractionInformation.SelectSQL.Contains(dbTo.GetRuntimeName()))); //these columns should all exist Assert.IsTrue(newCataItems.Any(ci => ci.ExtractionInformation.SelectSQL.Contains("SkullColor"))); Assert.IsTrue(newCataItems.Any(ci => ci.ExtractionInformation.SelectSQL.Contains("SpineColor"))); Assert.IsTrue(newCataItems.Any(ci => ci.ExtractionInformation.SelectSQL.Contains("Vertebrae"))); //actually there will be 2 copies of this one from Necks one from Heads //new ColumnInfo should have a reference to the anotable Assert.IsTrue(newCataItems.Single(ci => ci.Name.Equals("ANOSkullColor")).ColumnInfo.ANOTable_ID == anoTable.ID); var newSpineColorColumnInfo = newCataItems.Single(ci => ci.Name.Equals("ANOSkullColor")).ColumnInfo; //table info already existed, make sure the new CatalogueItems point to the same columninfos / table infos Assert.IsTrue(newCataItems.Select(ci => ci.ColumnInfo).Contains(newSpineColorColumnInfo)); } finally { dbFrom.Drop(); dbTo.Drop(); } }
public void ImportFile() { string file = Path.GetTempFileName(); string databaseName = TestDatabaseNames.GetConsistentName(GetType().Name); try { using (var sw = new StreamWriter(file)) { sw.WriteLine("Name,Surname,Age,Healthiness,DateOfImagining"); sw.WriteLine("Frank,\"Mortus,M\",41,0.00,2005-12-01"); sw.WriteLine("Bob,Balie,12,1,2013-06-11"); sw.WriteLine("Munchen,'Smith',43,0.3,2002-01-01"); sw.WriteLine("Carnage,Here there is,29,0.91,2005-01-01"); sw.WriteLine("Nathan,Crumble,51,0.78,2005-01-01"); sw.Close(); } var source = new DelimitedFlatFileDataFlowSource { Separator = ",", IgnoreBlankLines = true, MakeHeaderNamesSane = true, StronglyTypeInputBatchSize = -1, StronglyTypeInput = true }; source.PreInitialize(new FlatFileToLoad(new FileInfo(file)), new ThrowImmediatelyDataLoadEventListener());//this is the file we want to load source.Check(new ThrowImmediatelyCheckNotifier()); var server = DiscoveredServerICanCreateRandomDatabasesAndTablesOn; var database = server.ExpectDatabase(databaseName); //recreate it database.Create(true); server.ChangeDatabase(databaseName); var dt = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken()); var tbl = database.CreateTable(dt.TableName, dt); string tableName = tbl.GetRuntimeName(); source.Dispose(new ThrowImmediatelyDataLoadEventListener(), null); var tablesInDatabase = server.ExpectDatabase(databaseName).DiscoverTables(false); //there should be 1 table in this database Assert.AreEqual(1, tablesInDatabase.Length); //it should be called the same as the file loaded Assert.AreEqual(Path.GetFileNameWithoutExtension(file), tablesInDatabase[0].GetRuntimeName()); Assert.AreEqual("varchar(7)", GetColumnType(database, tableName, "Name")); Assert.AreEqual("varchar(13)", GetColumnType(database, tableName, "Surname")); Assert.AreEqual("int", GetColumnType(database, tableName, "Age")); Assert.AreEqual("decimal(3,2)", GetColumnType(database, tableName, "Healthiness")); Assert.AreEqual("datetime2", GetColumnType(database, tableName, "DateOfImagining")); using (var con = (SqlConnection)server.GetConnection()) { con.Open(); SqlCommand cmdReadData = new SqlCommand( "Select * from " + tablesInDatabase[0].GetRuntimeName() + " WHERE Name='Frank'", con); SqlDataReader r = cmdReadData.ExecuteReader(); //expected 1 record only Assert.IsTrue(r.Read()); Assert.AreEqual("Frank", r["Name"]); Assert.AreEqual("Mortus,M", r["Surname"]); Assert.AreEqual(41, r["Age"]); Assert.AreEqual(0.0f, r["Healthiness"]); Assert.AreEqual(new DateTime(2005, 12, 1), r["DateOfImagining"]); //and no more records Assert.IsFalse(r.Read()); con.Close(); } server.ExpectDatabase(databaseName).Drop(); Assert.IsFalse(server.ExpectDatabase(databaseName).Exists()); } finally { try { File.Delete(file); } catch (IOException) { //Couldn't delete temporary file... oh well } } }
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); }