public void RefactorTableName_IsRefactorable_ExtractionInformation(string transformSql, bool expectedToBeRefactorable)
{
var ei = WhenIHaveA <ExtractionInformation>();
ei.SelectSQL = transformSql;
ei.Alias = "MyCatalogueItem";
ei.SaveToDatabase();
var ci = ei.ColumnInfo;
ci.Name = "[database]..[table].[column]";
ci.SaveToDatabase();
var tableInfo = ei.ColumnInfo.TableInfo;
tableInfo.Database = "database";
tableInfo.Name = "[database]..[table]";
tableInfo.SaveToDatabase();
var refactorer = new SelectSQLRefactorer();
Assert.AreEqual(expectedToBeRefactorable, refactorer.IsRefactorable(ei));
if (expectedToBeRefactorable)
{
refactorer.RefactorTableName(ei, tableInfo, "[database]..[table2]");
}
else
{
Assert.Throws <RefactoringException>(() => refactorer.RefactorTableName(ei, tableInfo, "[database]..[table2]"));
}
}
public void RefactorTableName_TestValidReplacement_ExtractionInformation()
{
var ei = WhenIHaveA <ExtractionInformation>();
ei.SelectSQL = "UPPER([database]..[table].[column])";
ei.Alias = "MyCatalogueItem";
ei.SaveToDatabase();
var ci = ei.ColumnInfo;
ci.Name = "[database]..[table].[column]";
ci.SaveToDatabase();
var tableInfo = ei.ColumnInfo.TableInfo;
tableInfo.Database = "database";
tableInfo.Name = "[database]..[table]";
tableInfo.SaveToDatabase();
var refactorer = new SelectSQLRefactorer();
refactorer.RefactorTableName(ei, tableInfo, "[database]..[table2]");
Assert.AreEqual("UPPER([database]..[table2].[column])", ei.SelectSQL);
}
private void DoRefactoring(string toReplace, string toReplaceWith)
{
var refactorer = new SelectSQLRefactorer();
int updatesMade = refactorer.RefactorTableName(_tableInfo, toReplace, toReplaceWith);
MessageBox.Show("Made " + updatesMade + " replacements in ExtractionInformation/ColumnInfos.");
}
public void RefactorTableName_IsRefactorable_ColumnInfo(string columnTable, string findTableName)
{
var col = WhenIHaveA <ColumnInfo>();
col.Name = columnTable + "[MyTbl].[A]";
col.SaveToDatabase();
var refactorer = new SelectSQLRefactorer();
Assert.AreEqual(1, refactorer.RefactorTableName(col, findTableName + "[MyTbl].[A]", findTableName + "[MyTbl2].[A]"));
Assert.AreEqual(findTableName + "[MyTbl2].[A]", col.Name);
}
public ExecuteCommandAlterTableName(IActivateItems activator, TableInfo tableInfo) : base(activator, tableInfo)
{
if (IsImpossible)
{
return;
}
_refactorer = new SelectSQLRefactorer();
if (!_refactorer.IsRefactorable(TableInfo))
{
SetImpossible("Cannot rename table because " + _refactorer.GetReasonNotRefactorable(TableInfo));
return;
}
}
public void RefactorTableName_IsRefactorable_ColumnInfo(string columnName, string findTableName)
{
var col = WhenIHaveA <ColumnInfo>();
col.Name = columnName;
col.SaveToDatabase();
var refactorer = new SelectSQLRefactorer();
var oldName = findTableName;
var newName = oldName.Replace("MyTbl", "MyNewTbl");
Assert.AreEqual(1, refactorer.RefactorTableName(col, oldName, newName));
Assert.AreEqual(newName + ".[A]", col.Name);
}
public void RefactorTableName_TestValidReplacement_ColumnInfo()
{
var columnInfo = WhenIHaveA <ColumnInfo>();
columnInfo.Name = "[database]..[table].[column]";
var tableInfo = columnInfo.TableInfo;
tableInfo.Database = "database";
tableInfo.Name = "[database]..[table]";
var refactorer = new SelectSQLRefactorer();
refactorer.RefactorTableName(columnInfo, tableInfo, "[database]..[table2]");
Assert.AreEqual("[database]..[table2].[column]", columnInfo.Name);
}
public void RefactorTableName_IsRefactorable_TableInfoWithNoColumnInfos(string oldName, string newName)
{
var ti = WhenIHaveA <TableInfo>();
ti.Name = oldName;
ti.Database = "Fish";
ti.SaveToDatabase();
foreach (IDeleteable d in ti.ColumnInfos)
{
d.DeleteInDatabase();
}
var refactorer = new SelectSQLRefactorer();
Assert.IsTrue(refactorer.IsRefactorable(ti));
Assert.AreEqual(1, refactorer.RefactorTableName(ti, newName));
Assert.AreEqual(newName, ti.Name);
}
public void RefactorTableName_IsNotRefactorable_TableInfoWithNoColumnInfos(string oldName, string newName, string expectedReason)
{
var ti = WhenIHaveA <TableInfo>();
ti.Name = oldName;
ti.Database = "Fish";
ti.SaveToDatabase();
foreach (IDeleteable d in ti.ColumnInfos)
{
d.DeleteInDatabase();
}
var refactorer = new SelectSQLRefactorer();
Assert.IsFalse(refactorer.IsRefactorable(ti));
var ex = Assert.Throws <RefactoringException>(() => refactorer.RefactorTableName(ti, newName));
StringAssert.Contains(expectedReason, ex.Message);
}
public void Check(ICheckNotifier notifier)
{
if (TargetDatabase == null)
{
notifier.OnCheckPerformed(new CheckEventArgs("No TargetDatabase has been set", CheckResult.Fail));
}
else
if (!TargetDatabase.Exists())
{
notifier.OnCheckPerformed(new CheckEventArgs("TargetDatabase '" + TargetDatabase + "' does not exist", CheckResult.Fail));
}
var toMigrateTables = TableInfos.Except(SkippedTables).ToArray();
if (!toMigrateTables.Any())
{
notifier.OnCheckPerformed(new CheckEventArgs("There are no TableInfos selected for anonymisation", CheckResult.Fail));
}
try
{
var joinInfos = GetJoinInfosRequiredCatalogue();
notifier.OnCheckPerformed(new CheckEventArgs("Generated Catalogue SQL succesfully", CheckResult.Success));
foreach (JoinInfo joinInfo in joinInfos)
{
notifier.OnCheckPerformed(new CheckEventArgs("Found required JoinInfo '" + joinInfo + "' that will have to be migrated", CheckResult.Success));
}
foreach (Lookup lookup in GetLookupsRequiredCatalogue())
{
notifier.OnCheckPerformed(new CheckEventArgs("Found required Lookup '" + lookup + "' that will have to be migrated", CheckResult.Success));
//for each key involved in the lookup
foreach (ColumnInfo c in new[] { lookup.ForeignKey, lookup.PrimaryKey, lookup.Description })
{
//lookup / table has already been migrated
if (SkippedTables.Any(t => t.ID == c.TableInfo_ID))
{
continue;
}
//make sure that the plan is sensible
if (GetPlanForColumnInfo(c).Plan != Plan.PassThroughUnchanged)
{
notifier.OnCheckPerformed(new CheckEventArgs("ColumnInfo '" + c + "' is part of a Lookup so must PassThroughUnchanged", CheckResult.Fail));
}
}
}
}
catch (Exception ex)
{
notifier.OnCheckPerformed(new CheckEventArgs("Failed to generate Catalogue SQL", CheckResult.Fail, ex));
}
if (DateColumn != null)
{
var dateColumnPlan = GetPlanForColumnInfo(DateColumn);
if (dateColumnPlan.Plan != Plan.PassThroughUnchanged)
{
if (notifier.OnCheckPerformed(new CheckEventArgs("Plan for " + DateColumn + " must be PassThroughUnchanged", CheckResult.Fail, null, "Set plan to PassThroughUnchanged")))
{
dateColumnPlan.Plan = Plan.PassThroughUnchanged;
}
}
//get a count of the number of non lookup used tables
var usedTables = TableInfos.Except(SkippedTables).Count(t => !t.IsLookupTable());
if (usedTables > 1)
{
notifier.OnCheckPerformed(
new CheckEventArgs(
"You cannot have a date based migration because you are trying to migrate " + usedTables +
" TableInfos at once", CheckResult.Fail));
}
}
if (Plans.Any(p => p.Value.Plan == Plan.Dilute))
{
if (GetIdentifierDumpServer() == null)
{
notifier.OnCheckPerformed(new CheckEventArgs("No default Identifier Dump server has been configured", CheckResult.Fail));
}
}
var refactorer = new SelectSQLRefactorer();
foreach (ExtractionInformation e in _allExtractionInformations)
{
if (!refactorer.IsRefactorable(e))
{
notifier.OnCheckPerformed(new CheckEventArgs("ExtractionInformation '" + e + "' is a not refactorable due to reason:" + refactorer.GetReasonNotRefactorable(e), CheckResult.Fail));
}
}
notifier.OnCheckPerformed(new CheckEventArgs($"Preparing to evaluate {toMigrateTables.Length}' tables ({string.Join(",",toMigrateTables.Select(t=>t.GetFullyQualifiedName()))})", CheckResult.Success));
foreach (TableInfo tableInfo in toMigrateTables)
{
notifier.OnCheckPerformed(new CheckEventArgs("Evaluating TableInfo '" + tableInfo + "'", CheckResult.Success));
if (TargetDatabase != null && TargetDatabase.ExpectTable(tableInfo.GetRuntimeName()).Exists())
{
notifier.OnCheckPerformed(new CheckEventArgs("Table '" + tableInfo + "' already exists in Database '" + TargetDatabase + "'", CheckResult.Fail));
}
var pks = tableInfo.ColumnInfos.Where(c => c.IsPrimaryKey).ToArray();
if (!pks.Any())
{
notifier.OnCheckPerformed(new CheckEventArgs("TableInfo '" + tableInfo + "' does not have any Primary Keys, it cannot be anonymised", CheckResult.Fail));
}
if (tableInfo.IsTableValuedFunction)
{
notifier.OnCheckPerformed(new CheckEventArgs("TableInfo '" + tableInfo + "' is an IsTableValuedFunction so cannot be anonymised", CheckResult.Fail));
}
EnsureNotAlreadySharedLocally(notifier, tableInfo);
EnsureNotAlreadySharedLocally(notifier, Catalogue);
}
//check the column level plans
foreach (var p in Plans.Values)
{
p.Check(notifier);
}
}
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);
}
}
}
