private void MakeExtractable(object o, bool shouldBeExtractable, ExtractionCategory?category = null) { var n = (ColPair)o; //if it has extraction information if (n.ExtractionInformation != null) { if (shouldBeExtractable) { //if they want to change the extraction category if (category.HasValue && n.ExtractionInformation.ExtractionCategory != category.Value) { n.ExtractionInformation.ExtractionCategory = category.Value; n.ExtractionInformation.Order = olvColumnExtractability.IndexOf(n); n.ExtractionInformation.SaveToDatabase(); olvColumnExtractability.RefreshObject(n); } return; } else { //make it not extractable by deleting the extraction information n.ExtractionInformation.DeleteInDatabase(); n.ExtractionInformation = null; } } else { //it doesn't have ExtractionInformation if (!shouldBeExtractable) //it's already not extractable job done { return; } else { //make it extractable var newExtractionInformation = new ExtractionInformation((ICatalogueRepository)n.ColumnInfo.Repository, n.CatalogueItem, n.ColumnInfo, n.ColumnInfo.Name); if (category.HasValue) { newExtractionInformation.ExtractionCategory = category.Value; newExtractionInformation.Order = olvColumnExtractability.IndexOf(n); newExtractionInformation.SaveToDatabase(); } n.ExtractionInformation = newExtractionInformation; } } olvColumnExtractability.RefreshObject(n); }
private void LockIdentifier(ExtractionInformation extractionInformation) { cbxColumns.Enabled = false; cbxColumns.SelectedItem = extractionInformation; btnLockExtractionIdentifier.Image = _unlinkImage; //if it isn't yet marked as an extraction identifier save it as one for next time if (!extractionInformation.IsExtractionIdentifier) { extractionInformation.IsExtractionIdentifier = true; extractionInformation.SaveToDatabase(); } lockIn = false; }
private void UnlockIdentifier(ExtractionInformation currentSelectionIfAny) { cbxColumns.Enabled = true; cbxColumns.SelectedItem = null; //if there are multiple extraction identifiers e.g. SMR02 where there could be Mother CHI, Baby CHI and Father CHI do not unmark extraction identifier just because they changed it if (!_knownIdentifiersMode && currentSelectionIfAny != null) { currentSelectionIfAny.IsExtractionIdentifier = false; currentSelectionIfAny.SaveToDatabase(); } btnLockExtractionIdentifier.Image = _linkImage; lockIn = true; }
public override void Execute() { base.Execute(); //Create a new ExtractionInformation (contains the transform sql / column name) var newExtractionInformation = new ExtractionInformation(Activator.RepositoryLocator.CatalogueRepository, _catalogueItem, _catalogueItem.ColumnInfo, _catalogueItem.ColumnInfo.Name); //it will be Core but if the Catalogue is ProjectSpecific then instead we should make our new ExtractionInformation ExtractionCategory.ProjectSpecific if (_catalogueItem.Catalogue.IsProjectSpecific(Activator.RepositoryLocator.DataExportRepository)) { newExtractionInformation.ExtractionCategory = ExtractionCategory.ProjectSpecific; newExtractionInformation.SaveToDatabase(); } Publish(_catalogueItem); Activate(newExtractionInformation); }
/// <inheritdoc/> public override void Execute() { base.Execute(); foreach (var descCol in _lookupDescriptionColumns) { Lookup lookup = new Lookup(_catalogueRepository, descCol, _fkToPkTuples.First().Item1, _fkToPkTuples.First().Item2, ExtractionJoinType.Left, _collation); foreach (var supplementalKeyPair in _fkToPkTuples.Skip(1)) { new LookupCompositeJoinInfo(_catalogueRepository, lookup, supplementalKeyPair.Item1, supplementalKeyPair.Item2, _collation); } if (_alsoCreateExtractionInformations) { string proposedName; if (_lookupDescriptionColumns.Length == 1) { proposedName = _foreignKeyExtractionInformation.GetRuntimeName() + "_Desc"; } else { proposedName = _foreignKeyExtractionInformation.GetRuntimeName() + "_" + descCol.GetRuntimeName(); } var newCatalogueItem = new CatalogueItem(_catalogueRepository, _catalogue, proposedName); newCatalogueItem.SetColumnInfo(descCol); //bump everyone down 1 foreach (var toBumpDown in _allExtractionInformations.Where(e => e.Order > _foreignKeyExtractionInformation.Order)) { toBumpDown.Order++; toBumpDown.SaveToDatabase(); } var newExtractionInformation = new ExtractionInformation(_catalogueRepository, newCatalogueItem, descCol, descCol.ToString()); newExtractionInformation.ExtractionCategory = ExtractionCategory.Supplemental; newExtractionInformation.Alias = newCatalogueItem.Name; newExtractionInformation.Order = _foreignKeyExtractionInformation.Order + 1; newExtractionInformation.SaveToDatabase(); } } _catalogue.ClearAllInjections(); }
private void Setup(ExtractionInformation extractionInformation) { ExtractionInformation = extractionInformation; if (isFirstTimeSetupCalled) { //if the catalogue item has same name as the extraction information (alias) if (ExtractionInformation.CatalogueItem.Name.Equals(ExtractionInformation.ToString())) { _namesMatchedWhenDialogWasLaunched = true; } _querySyntaxHelper = ExtractionInformation.GetQuerySyntaxHelper(); QueryEditor = new ScintillaTextEditorFactory().Create(new RDMPCombineableFactory(), SyntaxLanguage.SQL, _querySyntaxHelper); QueryEditor.TextChanged += QueryEditorOnTextChanged; var autoComplete = new AutoCompleteProviderWin(_querySyntaxHelper); autoComplete.Add(ExtractionInformation.CatalogueItem.Catalogue); autoComplete.RegisterForEvents(QueryEditor); isFirstTimeSetupCalled = false; } var colInfo = ExtractionInformation.ColumnInfo; //deal with empty values in database (shouldn't be any but could be) if (string.IsNullOrWhiteSpace(ExtractionInformation.SelectSQL) && colInfo != null) { ExtractionInformation.SelectSQL = colInfo.Name.Trim(); ExtractionInformation.SaveToDatabase(); } QueryEditor.Text = ExtractionInformation.SelectSQL + (!string.IsNullOrWhiteSpace(ExtractionInformation.Alias) ? _querySyntaxHelper.AliasPrefix + ExtractionInformation.Alias : ""); lblFromTable.Text = colInfo == null?"MISSING ColumnInfo":colInfo.TableInfo.Name; if (!pSql.Controls.Contains(QueryEditor)) { pSql.Controls.Add(QueryEditor); } }
private void btnSaveNewOrder_Click(object sender, EventArgs e) { if (lbNewOrder.Items.Count == 0) { MessageBox.Show("You must paste a list of columns into the Desired Order listbox first"); return; } for (int i = 0; i < lbNewOrder.Items.Count; i++) { ExtractionInformation info = (ExtractionInformation)lbNewOrder.Items[i]; info.Order = i + 1; info.SaveToDatabase(); } RefreshUIFromDatabase(); MessageBox.Show("Reorder Applied"); ClearAdvancedListboxes(); }
/// <inheritdoc cref="ExecuteForwardEngineering()"/> public void ExecuteForwardEngineering(Catalogue intoExistingCatalogue, out Catalogue catalogue, out CatalogueItem[] catalogueItems, out ExtractionInformation[] extractionInformations) { var repo = _tableInfo.CatalogueRepository; //if user did not specify an existing catalogue to supplement if (intoExistingCatalogue == null) { //create a new (empty) catalogue and treat that as the new target intoExistingCatalogue = new Catalogue(repo, _tableInfo.GetRuntimeName()); } catalogue = intoExistingCatalogue; List <CatalogueItem> catalogueItemsCreated = new List <CatalogueItem>(); List <ExtractionInformation> extractionInformationsCreated = new List <ExtractionInformation>(); int order = 0; //for each column we will add a new one to the foreach (ColumnInfo col in _columnInfos) { order++; //create it with the same name CatalogueItem cataItem = new CatalogueItem(repo, intoExistingCatalogue, col.Name.Substring(col.Name.LastIndexOf(".") + 1).Trim('[', ']', '`', '"')); catalogueItemsCreated.Add(cataItem); if (_markAllExtractable) { var newExtractionInfo = new ExtractionInformation(repo, cataItem, col, col.Name); newExtractionInfo.Order = order; newExtractionInfo.SaveToDatabase(); extractionInformationsCreated.Add(newExtractionInfo); } else { cataItem.ColumnInfo_ID = col.ID; cataItem.SaveToDatabase(); } } extractionInformations = extractionInformationsCreated.ToArray(); catalogueItems = catalogueItemsCreated.ToArray(); }
private void SetupCatalogueConfigurationEtc() { DataTable dt = new DataTable(); dt.Columns.Add("PrivateID"); dt.Columns.Add("Name"); dt.Columns.Add("DateOfBirth"); dt.Rows.Add(new object[] { _cohortKeysGenerated.Keys.First(), "Dave", "2001-01-01" }); var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable("TestTable", dt, new[] { new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 50)) }); CatalogueItem[] cataItems; _catalogue = Import(tbl, out _tableInfo, out _columnInfos, out cataItems, out _extractionInformations); ExtractionInformation _privateID = _extractionInformations.First(e => e.GetRuntimeName().Equals("PrivateID")); _privateID.IsExtractionIdentifier = true; _privateID.SaveToDatabase(); }
private void CreateANormalCatalogue() { var svr = _database.Server; using (var con = svr.GetConnection()) { con.Open(); svr.GetCommand("CREATE TABLE NonTVFTable ( chi varchar(10))", con).ExecuteNonQuery(); svr.GetCommand("INSERT INTO NonTVFTable VALUES ('0101010101')", con).ExecuteNonQuery(); svr.GetCommand("INSERT INTO NonTVFTable VALUES ('0202020202')", con).ExecuteNonQuery(); svr.GetCommand("INSERT INTO NonTVFTable VALUES ('0303030303')", con).ExecuteNonQuery(); } var importer = new TableInfoImporter(CatalogueRepository, svr.Name, _database.GetRuntimeName(), "NonTVFTable", DatabaseType.MicrosoftSQLServer); 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); _nonTvfExtractionIdentifier = eis.Single(); _nonTvfExtractionIdentifier.IsExtractionIdentifier = true; _nonTvfExtractionIdentifier.SaveToDatabase(); _nonTvfCatalogue = cata; _nonTvfTableInfo = tbl; }
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(); } } }
private ExtractDatasetCommand SetupExtractDatasetCommand(string testTableName, string[] pkExtractionColumns, string[] pkColumnInfos = null, bool withLookup = false, bool withJoin = false) { DataTable dt = new DataTable(); dt.Columns.Add("PrivateID"); dt.Columns.Add("Name"); dt.Columns.Add("DateOfBirth"); if (pkColumnInfos != null) { dt.PrimaryKey = dt.Columns.Cast <DataColumn>().Where(col => pkColumnInfos.Contains(col.ColumnName)).ToArray(); } dt.Rows.Add(new object[] { _cohortKeysGenerated.Keys.First(), "Dave", "2001-01-01" }); var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable(testTableName, dt, new[] { new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 50)) }); TableInfo tableInfo; ColumnInfo[] columnInfos; CatalogueItem[] cataItems; ExtractionInformation[] extractionInformations; _catalogue = Import(tbl, out tableInfo, out columnInfos, out cataItems, out extractionInformations); ExtractionInformation privateID = extractionInformations.First(e => e.GetRuntimeName().Equals("PrivateID")); privateID.IsExtractionIdentifier = true; privateID.SaveToDatabase(); if (withLookup) { SetupLookupTable(); } if (withJoin) { SetupJoin(); } _catalogue.ClearAllInjections(); extractionInformations = _catalogue.GetAllExtractionInformation(ExtractionCategory.Any); foreach (var pkExtractionColumn in pkExtractionColumns) { ExtractionInformation column = extractionInformations.First(e => e.GetRuntimeName().Equals(pkExtractionColumn)); column.IsPrimaryKey = true; column.SaveToDatabase(); } ExtractionConfiguration configuration; IExtractableDataSet extractableDataSet; Project project; SetupDataExport(testTableName, _catalogue, out configuration, out extractableDataSet, out project); configuration.Cohort_ID = _extractableCohort.ID; configuration.SaveToDatabase(); return(new ExtractDatasetCommand(configuration, new ExtractableDatasetBundle(extractableDataSet))); }
private void btnApplyTransform_Click(object sender, EventArgs e) { if (rbDelete.Checked) { if (MessageBox.Show("Are you sure you want to delete?", "Confirm delete", MessageBoxButtons.YesNo) != DialogResult.Yes) { return; } } ColumnInfo[] guessPoolColumnInfo = null; if (rbGuessNewAssociatedColumns.Checked) { var tableInfo = cbTableInfos.SelectedItem as TableInfo; if (tableInfo == null) { MessageBox.Show("You must select a TableInfo from the dropdown first"); return; } guessPoolColumnInfo = tableInfo.ColumnInfos.ToArray(); } int deleteCount = 0; int countExtractionInformationsCreated = 0; int countOfColumnInfoAssociationsCreated = 0; foreach (CatalogueItem catalogueItem in olvCatalogueItems.Objects) { if (ShouldTransformCatalogueItem(catalogueItem)) { //bulk operation is delete if (rbDelete.Checked) { catalogueItem.DeleteInDatabase(); deleteCount++; } //delete relationship between columnInfo and CatalogueItem (IMPORTANT: this does not delete either the ColumnInfo - which could be used by other Catalogues or the CatalogueItem) if (rbDeleteAssociatedColumnInfos.Checked) { if (catalogueItem.ColumnInfo_ID != null) { deleteCount++; catalogueItem.SetColumnInfo(null); } } //delete extraction information only, this leaves the underlying relationship between the columnInfo and the CatalogueItem (which must exist in the first place before ExtractionInformation could have been configured) intact if (rbDeleteExtrctionInformation.Checked) { if (catalogueItem.ExtractionInformation != null) { catalogueItem.ExtractionInformation.DeleteInDatabase(); deleteCount++; } } //user wants to guess ColumnInfo associations between the supplied catalogue and underlying table (and the column doesnt have any existing ones already if (rbGuessNewAssociatedColumns.Checked && catalogueItem.ColumnInfo_ID == null) { ColumnInfo[] guesses = catalogueItem.GuessAssociatedColumn(guessPoolColumnInfo).ToArray(); //exact matches are straight up accepted if (guesses.Length == 1) { catalogueItem.SetColumnInfo(guesses[0]); countOfColumnInfoAssociationsCreated++; } else { //multiple matches so ask the user what one he wants for (int i = 0; i < guesses.Length; i++) //note that this sneakily also deals with case where guesses is empty { if (Activator.YesNo("Found multiple matches, approve match?:" + Environment.NewLine + catalogueItem.Name + Environment.NewLine + guesses[i], "Multiple matched guesses")) { catalogueItem.SetColumnInfo(guesses[i]); countOfColumnInfoAssociationsCreated++; break; } } } } //user wants to mark existing associated columns as extractable (will be created with the default SELECT transformation which is verbatim, no changes) if (rbMarkExtractable.Checked) { //get the associated columns var col = catalogueItem.ColumnInfo; //do not try to mark missing column info as extractable if (col == null) { continue; } //column already has ExtractionInformation configured for it so ignore it if (catalogueItem.ExtractionInformation != null) { //unless user wants to do reckless recategorisation if (cbRecategorise.Checked) { var ei = catalogueItem.ExtractionInformation; ei.ExtractionCategory = (ExtractionCategory)ddExtractionCategory.SelectedItem; ei.SaveToDatabase(); } continue; } //we got to here so we have a legit 1 column info to cataitem we can enable for extraction ExtractionInformation created = new ExtractionInformation((CatalogueRepository)catalogueItem.Repository, catalogueItem, col, null); if (ddExtractionCategory.SelectedItem != null) { created.ExtractionCategory = (ExtractionCategory)ddExtractionCategory.SelectedItem; created.SaveToDatabase(); } countExtractionInformationsCreated++; } } } string message = ""; if (deleteCount != 0) { message += "Performed " + deleteCount + " delete operations" + Environment.NewLine; } if (countExtractionInformationsCreated != 0) { message += "Created " + countExtractionInformationsCreated + " ExtractionInformations" + Environment.NewLine; } if (countOfColumnInfoAssociationsCreated != 0) { message += "Created " + countOfColumnInfoAssociationsCreated + " assocations between CatalogueItems and ColumnInfos" + Environment.NewLine; } if (!string.IsNullOrWhiteSpace(message)) { MessageBox.Show(message); } Publish(_catalogue); RefreshUIFromDatabase(); }
public void Execute() { if (_planManager.TargetDatabase == null) { throw new Exception("PlanManager has no TargetDatabase set"); } var memoryRepo = new MemoryCatalogueRepository(); using (_catalogueRepository.BeginNewTransactedConnection()) { try { //for each skipped table foreach (var skippedTable in _planManager.SkippedTables) { //we might have to refactor or port JoinInfos to these tables so we should establish what the parenthood of them was foreach (ColumnInfo columnInfo in skippedTable.ColumnInfos) { GetNewColumnInfoForOld(columnInfo, true); } } //for each table that isn't being skipped foreach (var oldTableInfo in _planManager.TableInfos.Except(_planManager.SkippedTables)) { List <DatabaseColumnRequest> columnsToCreate = new List <DatabaseColumnRequest>(); Dictionary <string, ColumnInfo> migratedColumns = new Dictionary <string, ColumnInfo>(StringComparer.CurrentCultureIgnoreCase); var querybuilderForMigratingTable = new QueryBuilder(null, null); //for each column we are not skipping (Drop) work out the endpoint datatype (planner knows this) foreach (ColumnInfo columnInfo in oldTableInfo.ColumnInfos) { var columnPlan = _planManager.GetPlanForColumnInfo(columnInfo); if (columnPlan.Plan != Plan.Drop) { //add the column verbatim to the query builder because we know we have to read it from source querybuilderForMigratingTable.AddColumn(new ColumnInfoToIColumn(memoryRepo, columnInfo)); string colName = columnInfo.GetRuntimeName(); //if it is being ano tabled then give the table name ANO as a prefix if (columnPlan.Plan == Plan.ANO) { colName = "ANO" + colName; } migratedColumns.Add(colName, columnInfo); columnsToCreate.Add(new DatabaseColumnRequest(colName, columnPlan.GetEndpointDataType(), !columnInfo.IsPrimaryKey) { IsPrimaryKey = columnInfo.IsPrimaryKey }); } } SelectSQLForMigrations.Add(oldTableInfo, querybuilderForMigratingTable); //Create the actual table var tbl = _planManager.TargetDatabase.CreateTable(oldTableInfo.GetRuntimeName(), columnsToCreate.ToArray()); //import the created table TableInfoImporter importer = new TableInfoImporter(_catalogueRepository, tbl); importer.DoImport(out var newTableInfo, out var newColumnInfos); //Audit the parenthood of the TableInfo/ColumnInfos AuditParenthood(oldTableInfo, newTableInfo); foreach (ColumnInfo newColumnInfo in newColumnInfos) { var oldColumnInfo = migratedColumns[newColumnInfo.GetRuntimeName()]; var columnPlan = _planManager.GetPlanForColumnInfo(oldColumnInfo); if (columnPlan.Plan == Plan.ANO) { newColumnInfo.ANOTable_ID = columnPlan.ANOTable.ID; newColumnInfo.SaveToDatabase(); } //if there was a dilution configured we need to setup a virtual DLE load only column of the input type (this ensures RAW has a valid datatype) if (columnPlan.Plan == Plan.Dilute) { //Create a discarded (load only) column with name matching the new columninfo var discard = new PreLoadDiscardedColumn(_catalogueRepository, newTableInfo, newColumnInfo.GetRuntimeName()); //record that it exists to support dilution and that the data type matches the input (old) ColumnInfo (i.e. not the new data type!) discard.Destination = DiscardedColumnDestination.Dilute; discard.SqlDataType = oldColumnInfo.Data_type; discard.SaveToDatabase(); DilutionOperationsForMigrations.Add(discard, columnPlan.Dilution); } AuditParenthood(oldColumnInfo, newColumnInfo); } if (DilutionOperationsForMigrations.Any()) { newTableInfo.IdentifierDumpServer_ID = _planManager.GetIdentifierDumpServer().ID; newTableInfo.SaveToDatabase(); } } NewCatalogue = _planManager.Catalogue.ShallowClone(); NewCatalogue.Name = "ANO" + _planManager.Catalogue.Name; NewCatalogue.Folder = new CatalogueFolder(NewCatalogue, "\\anonymous" + NewCatalogue.Folder.Path); NewCatalogue.SaveToDatabase(); AuditParenthood(_planManager.Catalogue, NewCatalogue); //For each of the old ExtractionInformations (95% of the time that's just a reference to a ColumnInfo e.g. '[People].[Height]' but 5% of the time it's some horrible aliased transform e.g. 'dbo.RunMyCoolFunction([People].[Height]) as BigHeight' foreach (CatalogueItem oldCatalogueItem in _planManager.Catalogue.CatalogueItems) { var oldColumnInfo = oldCatalogueItem.ColumnInfo; //catalogue item is not connected to any ColumnInfo if (oldColumnInfo == null) { continue; } var columnPlan = _planManager.GetPlanForColumnInfo(oldColumnInfo); //we are not migrating it anyway if (columnPlan.Plan == Plan.Drop) { continue; } ColumnInfo newColumnInfo = GetNewColumnInfoForOld(oldColumnInfo); var newCatalogueItem = oldCatalogueItem.ShallowClone(NewCatalogue); //and rewire it's ColumnInfo to the cloned child one newCatalogueItem.ColumnInfo_ID = newColumnInfo.ID; //If the old CatalogueItem had the same name as it's underlying ColumnInfo then we should use the new one otherwise just copy the old name whatever it was newCatalogueItem.Name = oldCatalogueItem.Name.Equals(oldColumnInfo.Name) ? newColumnInfo.GetRuntimeName() : oldCatalogueItem.Name; //add ANO to the front if the underlying column was annoed if (newColumnInfo.GetRuntimeName().StartsWith("ANO") && !newCatalogueItem.Name.StartsWith("ANO")) { newCatalogueItem.Name = "ANO" + newCatalogueItem.Name; } newCatalogueItem.SaveToDatabase(); var oldExtractionInformation = oldCatalogueItem.ExtractionInformation; //if the plan is to make the ColumnInfo extractable if (columnPlan.ExtractionCategoryIfAny != null) { //Create a new ExtractionInformation for the new Catalogue var newExtractionInformation = new ExtractionInformation(_catalogueRepository, newCatalogueItem, newColumnInfo, newColumnInfo.Name); newExtractionInformation.ExtractionCategory = columnPlan.ExtractionCategoryIfAny.Value; newExtractionInformation.SaveToDatabase(); //if it was previously extractable if (oldExtractionInformation != null) { var refactorer = new SelectSQLRefactorer(); //restore the old SQL as it existed in the origin table newExtractionInformation.SelectSQL = oldExtractionInformation.SelectSQL; //do a refactor on the old column name for the new column name refactorer.RefactorColumnName(newExtractionInformation, oldColumnInfo, newColumnInfo.Name, true); //also refactor any other column names that might be referenced by the transform SQL e.g. it could be a combo column name where forename + surname is the value of the ExtractionInformation foreach (var kvpOtherCols in _parenthoodDictionary.Where(kvp => kvp.Key is ColumnInfo)) { //if it's one we have already done, dont do it again if (Equals(kvpOtherCols.Value, newColumnInfo)) { continue; } //otherwise do a non strict refactoring (don't worry if you don't finda ny references) refactorer.RefactorColumnName(newExtractionInformation, (ColumnInfo)kvpOtherCols.Key, ((ColumnInfo)(kvpOtherCols.Value)).Name, false); } //make the new one exactly as extractable newExtractionInformation.Order = oldExtractionInformation.Order; newExtractionInformation.Alias = oldExtractionInformation.Alias; newExtractionInformation.IsExtractionIdentifier = oldExtractionInformation.IsExtractionIdentifier; newExtractionInformation.HashOnDataRelease = oldExtractionInformation.HashOnDataRelease; newExtractionInformation.IsPrimaryKey = oldExtractionInformation.IsPrimaryKey; newExtractionInformation.SaveToDatabase(); } AuditParenthood(oldCatalogueItem, newCatalogueItem); if (oldExtractionInformation != null) { AuditParenthood(oldExtractionInformation, newExtractionInformation); } } } var existingJoinInfos = _catalogueRepository.GetAllObjects <JoinInfo>(); var existingLookups = _catalogueRepository.GetAllObjects <Lookup>(); var existingLookupComposites = _catalogueRepository.GetAllObjects <LookupCompositeJoinInfo>(); //migrate join infos foreach (JoinInfo joinInfo in _planManager.GetJoinInfosRequiredCatalogue()) { var newFk = GetNewColumnInfoForOld(joinInfo.ForeignKey); var newPk = GetNewColumnInfoForOld(joinInfo.PrimaryKey); //already exists if (!existingJoinInfos.Any(ej => ej.ForeignKey_ID == newFk.ID && ej.PrimaryKey_ID == newPk.ID)) { new JoinInfo(_catalogueRepository, newFk, newPk, joinInfo.ExtractionJoinType, joinInfo.Collation); //create it } } //migrate Lookups foreach (Lookup lookup in _planManager.GetLookupsRequiredCatalogue()) { //Find the new columns in the ANO table that match the old lookup columns var newDesc = GetNewColumnInfoForOld(lookup.Description); var newFk = GetNewColumnInfoForOld(lookup.ForeignKey); var newPk = GetNewColumnInfoForOld(lookup.PrimaryKey); //see if we already have a Lookup declared for the NEW columns (unlikely) Lookup newLookup = existingLookups.SingleOrDefault(l => l.Description_ID == newDesc.ID && l.ForeignKey_ID == newFk.ID); //create new Lookup that mirrors the old but references the ANO columns instead if (newLookup == null) { newLookup = new Lookup(_catalogueRepository, newDesc, newFk, newPk, lookup.ExtractionJoinType, lookup.Collation); } //also mirror any composite (secondary, tertiary join column pairs needed for the Lookup to operate correclty e.g. where TestCode 'HAB1' means 2 different things depending on healthboard) foreach (LookupCompositeJoinInfo compositeJoin in lookup.GetSupplementalJoins().Cast <LookupCompositeJoinInfo>()) { var newCompositeFk = GetNewColumnInfoForOld(compositeJoin.ForeignKey); var newCompositePk = GetNewColumnInfoForOld(compositeJoin.PrimaryKey); if (!existingLookupComposites.Any(c => c.ForeignKey_ID == newCompositeFk.ID && c.PrimaryKey_ID == newCompositePk.ID)) { new LookupCompositeJoinInfo(_catalogueRepository, newLookup, newCompositeFk, newCompositePk, compositeJoin.Collation); } } } //create new data load confguration LoadMetadata = new LoadMetadata(_catalogueRepository, "Anonymising " + NewCatalogue); LoadMetadata.EnsureLoggingWorksFor(NewCatalogue); NewCatalogue.LoadMetadata_ID = LoadMetadata.ID; NewCatalogue.SaveToDatabase(); if (_planManager.DateColumn != null) { LoadProgressIfAny = new LoadProgress(_catalogueRepository, LoadMetadata); LoadProgressIfAny.OriginDate = _planManager.StartDate; LoadProgressIfAny.SaveToDatabase(); //date column based migration only works for single TableInfo migrations (see Plan Manager checks) var qb = SelectSQLForMigrations.Single(kvp => !kvp.Key.IsLookupTable()).Value; qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(memoryRepo, null, new[] { new SpontaneouslyInventedFilter(memoryRepo, null, _planManager.DateColumn + " >= @startDate", "After batch start date", "", null), new SpontaneouslyInventedFilter(memoryRepo, null, _planManager.DateColumn + " <= @endDate", "Before batch end date", "", null), } , FilterContainerOperation.AND); } try { foreach (QueryBuilder qb in SelectSQLForMigrations.Values) { Console.WriteLine(qb.SQL); } } catch (Exception e) { throw new Exception("Failed to generate migration SQL", e); } _catalogueRepository.EndTransactedConnection(true); } catch (Exception ex) { _catalogueRepository.EndTransactedConnection(false); throw new Exception("Failed to create ANO version, transaction rolled back succesfully", ex); } } }
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 TestActuallyCreatingIt(DatabaseType type) { var db = GetCleanedServer(type); //drop it db.Drop(); CreateNewCohortDatabaseWizard wizard = new CreateNewCohortDatabaseWizard(db, CatalogueRepository, DataExportRepository, false); _extractionInfo2.IsExtractionIdentifier = true; _extractionInfo2.SaveToDatabase(); var candidate = wizard.GetPrivateIdentifierCandidates().Single(c => c.RuntimeName.Equals("PrivateIdentifierB")); var ect = wizard.CreateDatabase( candidate, new ThrowImmediatelyCheckNotifier()); //database should exist DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(cohortDatabaseName); Assert.IsTrue(db.Exists()); //did it create the correct type? Assert.AreEqual(type, ect.DatabaseType); //the ExternalCohortTable should pass tests ect.Check(new ThrowImmediatelyCheckNotifier()); //now try putting someone in it //the project it will go under var project = new Project(DataExportRepository, "MyProject"); project.ProjectNumber = 10; project.SaveToDatabase(); //the request to put it under there var request = new CohortCreationRequest(project, new CohortDefinition(null, "My cohort", 1, 10, ect), DataExportRepository, "Blah"); //the actual cohort data DataTable dt = new DataTable(); dt.Columns.Add(_extractionInfo2.GetRuntimeName()); dt.Rows.Add(101243); //_extractionInfo2 is of type int //the destination component that will put it there var dest = new BasicCohortDestination(); dest.PreInitialize(request, new ThrowImmediatelyDataLoadEventListener()); //tell it to use the guid allocator dest.ReleaseIdentifierAllocator = typeof(GuidReleaseIdentifierAllocator); dest.ProcessPipelineData(dt, new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken()); dest.Dispose(new ThrowImmediatelyDataLoadEventListener(), null); var cohort = request.CohortCreatedIfAny; Assert.IsNotNull(cohort); var externalData = cohort.GetExternalData(); Assert.AreEqual(10, externalData.ExternalProjectNumber); Assert.IsFalse(string.IsNullOrEmpty(externalData.ExternalDescription)); Assert.AreEqual(DateTime.Now.Year, externalData.ExternalCohortCreationDate.Value.Year); Assert.AreEqual(DateTime.Now.Month, externalData.ExternalCohortCreationDate.Value.Month); Assert.AreEqual(DateTime.Now.Day, externalData.ExternalCohortCreationDate.Value.Day); Assert.AreEqual(DateTime.Now.Hour, externalData.ExternalCohortCreationDate.Value.Hour); cohort.AppendToAuditLog("Test"); Assert.IsTrue(cohort.AuditLog.Contains("Test")); Assert.AreEqual(1, cohort.Count); Assert.AreEqual(1, cohort.CountDistinct); var cohortTable = cohort.FetchEntireCohort(); Assert.AreEqual(1, cohortTable.Rows.Count); var helper = ect.GetQuerySyntaxHelper(); Assert.AreEqual(101243, cohortTable.Rows[0][helper.GetRuntimeName(ect.PrivateIdentifierField)]); var aguid = cohortTable.Rows[0][helper.GetRuntimeName(ect.ReleaseIdentifierField)].ToString(); Assert.IsFalse(string.IsNullOrWhiteSpace(aguid)); //should be a guid //test reversing the anonymisation of something var dtAno = new DataTable(); dtAno.Columns.Add(cohort.GetReleaseIdentifier(true)); dtAno.Columns.Add("Age"); dtAno.Rows.Add(aguid, 23); dtAno.Rows.Add(aguid, 99); cohort.ReverseAnonymiseDataTable(dtAno, new ThrowImmediatelyDataLoadEventListener(), true); Assert.AreEqual(2, dtAno.Columns.Count); Assert.IsTrue(dtAno.Columns.Contains(cohort.GetPrivateIdentifier(true))); Assert.AreEqual("101243", dtAno.Rows[0][cohort.GetPrivateIdentifier(true)]); Assert.AreEqual("101243", dtAno.Rows[1][cohort.GetPrivateIdentifier(true)]); }