public void CreateNewTableInfoInDatabase_Naming(string tableName, string columnName)
{
TableInfo table = new TableInfo(CatalogueRepository, tableName);
table.Database = "TestDB";
table.SaveToDatabase();
ColumnInfo c = new ColumnInfo(CatalogueRepository, columnName, "varchar(100)", table);
c.ANOTable_ID = -100;
try
{
Assert.AreEqual("ANOMyCol", c.GetRuntimeName());
Assert.AreEqual("MyCol", c.GetRuntimeName(LoadStage.AdjustRaw));
Assert.AreEqual("ANOMyCol", c.GetRuntimeName(LoadStage.PostLoad));
Assert.AreEqual("TestTableName", table.GetRuntimeName());
Assert.AreEqual("TestTableName", table.GetRuntimeName(LoadBubble.Raw));
Assert.AreEqual("TestDB_TestTableName_STAGING", table.GetRuntimeName(LoadBubble.Staging));
Assert.AreEqual("TestTableName_STAGING", table.GetRuntimeName(LoadBubble.Staging, new SuffixBasedNamer()));
Assert.AreEqual("TestDB_TestTableName_STAGING", table.GetRuntimeName(LoadBubble.Staging, new FixedStagingDatabaseNamer("TestDB")));
Assert.AreEqual("TestTableName", table.GetRuntimeName(LoadBubble.Live));
}
finally
{
c.DeleteInDatabase();
table.DeleteInDatabase();
}
}
public void Add(ColumnInfo columnInfo)
{
var snip = new SubstringAutocompleteItem(columnInfo.GetRuntimeName());
snip.MenuText = columnInfo.GetRuntimeName();
snip.Text = columnInfo.GetFullyQualifiedName();
snip.Tag = columnInfo;
snip.ImageIndex = GetIndexFor(columnInfo, RDMPConcept.ColumnInfo.ToString());
AddUnlessDuplicate(snip);
}
private void MigrateExistingData(Func <string, bool> shouldApplySql, DbConnection con, ICheckNotifier notifier, DiscoveredTable tbl)
{
string from = _colToNuke.GetRuntimeName(LoadStage.PostLoad);
string to = _newANOColumnInfo.GetRuntimeName(LoadStage.PostLoad);
//create an empty table for the anonymised data
DbCommand cmdCreateTempMap = DatabaseCommandHelper.GetCommand(string.Format("SELECT top 0 {0},{1} into TempANOMap from {2}", from, to, tbl.GetFullyQualifiedName()), con);
if (!shouldApplySql(cmdCreateTempMap.CommandText))
{
throw new Exception("User decided not to create the TempANOMap table");
}
cmdCreateTempMap.ExecuteNonQuery();
try
{
//get the existing data
DbCommand cmdGetExistingData = DatabaseCommandHelper.GetCommand(string.Format("SELECT {0},{1} from {2}", from, to, tbl.GetFullyQualifiedName()), con);
DbDataAdapter da = DatabaseCommandHelper.GetDataAdapter(cmdGetExistingData);
DataTable dt = new DataTable();
da.Fill(dt);//into memory
//transform it in memory
ANOTransformer transformer = new ANOTransformer(_toConformTo, new FromCheckNotifierToDataLoadEventListener(notifier));
transformer.Transform(dt, dt.Columns[0], dt.Columns[1]);
var tempAnoMapTbl = tbl.Database.ExpectTable("TempANOMap");
using (var insert = tempAnoMapTbl.BeginBulkInsert())
{
insert.Upload(dt);
}
//create an empty table for the anonymised data
DbCommand cmdUpdateMainTable = DatabaseCommandHelper.GetCommand(string.Format("UPDATE source set source.{1} = map.{1} from {2} source join TempANOMap map on source.{0}=map.{0}", from, to, tbl.GetFullyQualifiedName()), con);
if (!shouldApplySql(cmdUpdateMainTable.CommandText))
{
throw new Exception("User decided not to perform update on table");
}
cmdUpdateMainTable.ExecuteNonQuery();
}
finally
{
//always drop the temp anomap
DbCommand dropMappingTable = DatabaseCommandHelper.GetCommand("DROP TABLE TempANOMap", con);
dropMappingTable.ExecuteNonQuery();
}
}
private IEnumerable <object> DetectCollisions(ColumnInfo pkCol, TableInfo tableInfo)
{
var pkColName = pkCol.GetRuntimeName(LoadStage.AdjustRaw);
var tableNameFullyQualified = GetRAWTableNameFullyQualified(tableInfo);
string primaryKeysColliding = string.Format(
"SELECT {0} FROM {1} GROUP BY {0} HAVING count(*)>1",
_syntaxHelper.EnsureWrapped(pkColName),
tableNameFullyQualified
);
using (var con = _raw.Server.GetConnection())
{
con.Open();
using (var cmd = _raw.Server.GetCommand(primaryKeysColliding, con))
{
using (var r = cmd.ExecuteReader())
while (r.Read())
{
yield return(r[pkColName]);
}
}
}
}
public void CacheSingleTask(ICacheableTask cacheableTask, ExternalDatabaseServer queryCachingServer)
{
//if it is already cached don't inception cache
var sql = Tasks[cacheableTask].CountSQL;
if (sql.Trim().StartsWith(CachedAggregateConfigurationResultsManager.CachingPrefix))
{
return;
}
var manager = new CachedAggregateConfigurationResultsManager(queryCachingServer);
var explicitTypes = new List <DatabaseColumnRequest>();
AggregateConfiguration configuration = cacheableTask.GetAggregateConfiguration();
try
{
//the identifier column that we read from
ColumnInfo identifierColumnInfo = configuration.AggregateDimensions.Single(c => c.IsExtractionIdentifier).ColumnInfo;
var destinationDataType = GetDestinationType(identifierColumnInfo.Data_type, cacheableTask, queryCachingServer);
explicitTypes.Add(new DatabaseColumnRequest(identifierColumnInfo.GetRuntimeName(), destinationDataType));
}
catch (Exception e)
{
throw new Exception("Error occurred trying to find the data type of the identifier column when attempting to submit the result data table to the cache", e);
}
CacheCommitArguments args = cacheableTask.GetCacheArguments(sql, Tasks[cacheableTask].Identifiers, explicitTypes.ToArray());
manager.CommitResults(args);
}
public ExecuteCommandAnonymiseColumnInfo(IActivateItems activator, ColumnInfo columnInfo) : base(activator)
{
_columnInfo = columnInfo;
if (columnInfo.GetRuntimeName().StartsWith(ANOTable.ANOPrefix, StringComparison.CurrentCultureIgnoreCase))
{
SetImpossible("ColumnInfo is already anonymised (Starts with \"" + ANOTable.ANOPrefix + "\"");
}
if (columnInfo.ANOTable_ID != null)
{
SetImpossible("ColumnInfo is already anonymised");
}
if (Activator.ServerDefaults.GetDefaultFor(PermissableDefaults.ANOStore) == null)
{
SetImpossible("No Default ANOStore has been configured");
}
if (string.IsNullOrWhiteSpace(_columnInfo.TableInfo.Server))
{
SetImpossible("Parent TableInfo is missing a value for Server");
}
if (string.IsNullOrWhiteSpace(_columnInfo.TableInfo.Database))
{
SetImpossible("Parent TableInfo is missing a value for Database");
}
}
public override void Execute()
{
base.Execute();
if (_columnInfo == null)
{
_columnInfo = SelectOne <ColumnInfo>(Activator.RepositoryLocator.CatalogueRepository, _catalogueItem.Name);
}
if (_columnInfo == null)
{
return;
}
_catalogueItem.SetColumnInfo(_columnInfo);
//if it did not have a name before
if (_catalogueItem.Name.StartsWith("New CatalogueItem"))
{
//give it one
_catalogueItem.Name = _columnInfo.GetRuntimeName();
_catalogueItem.SaveToDatabase();
}
//Either way refresh the catalogue item
Publish(_catalogueItem);
}
public void TestQueryBuilder_MySql_Normal()
{
var t = new TableInfo(CatalogueRepository, "`db`.`tbl`");
t.DatabaseType = DatabaseType.MySql;
t.SaveToDatabase();
var col = new ColumnInfo(CatalogueRepository, "`db`.`tbl`.`col`", "varchar(10)", t);
Assert.AreEqual("col", col.GetRuntimeName());
var cata = new Catalogue(CatalogueRepository, "cata");
var catalogueItem = new CatalogueItem(CatalogueRepository, cata, "col");
var extractionInfo = new ExtractionInformation(CatalogueRepository, catalogueItem, col, col.Name);
var qb = new QueryBuilder(null, null);
qb.AddColumn(extractionInfo);
Assert.AreEqual(CollapseWhitespace(
@"SELECT
`db`.`tbl`.`col`
FROM
`db`.`tbl`"
), CollapseWhitespace(qb.SQL));
}
public override void SetDatabaseObject(IActivateItems activator, Lookup databaseObject)
{
base.SetDatabaseObject(activator, databaseObject);
_keyColumn = databaseObject.PrimaryKey;
_descriptionColumn = databaseObject.Description;
_tableInfo = _keyColumn.TableInfo;
lblCode.Text = _keyColumn.GetRuntimeName();
lblDescription.Text = _descriptionColumn.GetRuntimeName();
ScintillaTextEditorFactory factory = new ScintillaTextEditorFactory();
_scintilla = factory.Create();
gbScintilla.Controls.Add(_scintilla);
try
{
SendQuery();
}
catch (System.Exception ex)
{
CommonFunctionality.Fatal("Could not connect to database", ex);
}
}
/// <summary>
/// Returns the newly created / already existing NEW ANO column info when passed the old (identifiable original) ColumnInfo
/// </summary>
/// <param name="col"></param>
/// <param name="isOptional"></param>
/// <returns></returns>
private ColumnInfo GetNewColumnInfoForOld(ColumnInfo col, bool isOptional = false)
{
//it's one we migrated ourselves
if (_parenthoodDictionary.ContainsKey(col))
{
return((ColumnInfo)_parenthoodDictionary[col]);
}
//it's one that was already existing before we did ANO migration e.g. a SkippedTableInfo (this can happen when there are 2+ tables underlying a Catalogue and you have already ANO one of those Tables previously (e.g. when it is a shared table with other Catalogues)
//find a reference to the new ColumnInfo Location (note that it is possible the TableInfo was skipped, in which case we should still expect to find ColumnInfos that reference the new location because you must have created it somehow right?)
var syntaxHelper = _planManager.TargetDatabase.Server.GetQuerySyntaxHelper();
var toReturn = FindNewColumnNamed(syntaxHelper, col, col.GetRuntimeName(), isOptional);
if (toReturn == null)
{
toReturn = FindNewColumnNamed(syntaxHelper, col, "ANO" + col.GetRuntimeName(), isOptional);
}
if (toReturn == null)
{
if (isOptional)
{
return(null);
}
else
{
throw new Exception("Catalogue '" + _planManager.Catalogue + "' contained a CatalogueItem referencing Column '" + col + "' the ColumnInfo was not migrated (which is fine) but we then could not find ColumnInfo in the new ANO dataset (if it was part of SkippedTables why doesn't the Catalogue have a reference to the new location?)");
}
}
_parenthoodDictionary.Add(col, toReturn);
return(toReturn);
}
public void Add(ColumnInfo columnInfo, ITableInfo tableInfo, string databaseName, LoadStage stage, IQuerySyntaxHelper syntaxHelper)
{
var col = columnInfo.GetRuntimeName(stage);
var table = tableInfo.GetRuntimeName(stage);
var dbName = tableInfo.GetDatabaseRuntimeName(stage);
var snip = new SubstringAutocompleteItem(col);
snip.MenuText = col;
var fullySpecified = syntaxHelper.EnsureFullyQualified(dbName, tableInfo.Schema, table, col);
snip.Text = fullySpecified;
snip.Tag = columnInfo;
snip.ImageIndex = GetIndexFor(columnInfo, RDMPConcept.ColumnInfo.ToString());
AddUnlessDuplicate(snip);
}
public string GetPattern(int timeoutInMilliseconds)
{
var server = DataAccessPortal.GetInstance().ExpectServer(_parent, DataAccessContext.InternalDataProcessing);
using (var con = server.GetConnection())
{
con.Open();
using (var cmd = server.GetCommand(
SqlToCountLetters
.Replace("FIELDTOEVALUATE", _columnInfo.GetRuntimeName())
.Replace("TABLETOEVALUATE", _parent.Name)
, con))
{
cmd.CommandTimeout = timeoutInMilliseconds;
using (var reader = cmd.ExecuteReader())
{
reader.Read();
int longestString = int.Parse(reader[0].ToString());
int largestNumberOfCharactersSpotted = int.Parse(reader[1].ToString());
string resultPattern = "";
for (int i = 0; i < largestNumberOfCharactersSpotted; i++)
{
resultPattern += 'Z';
}
for (int i = 0; i < longestString - largestNumberOfCharactersSpotted; i++)
{
resultPattern += '9';
}
//double up on the first character type (ask chris hall about this)
resultPattern = resultPattern.ToCharArray()[0] + resultPattern;
return(resultPattern);
}
}
}
}
public void TestQueryBuilder_MySql_Top35()
{
var t = new TableInfo(CatalogueRepository, "`db`.`tbl`");
t.DatabaseType = DatabaseType.MySql;
t.SaveToDatabase();
var col = new ColumnInfo(CatalogueRepository, "`db`.`tbl`.`col`", "varchar(10)", t);
Assert.AreEqual("col", col.GetRuntimeName());
var cata = new Catalogue(CatalogueRepository, "cata");
var catalogueItem = new CatalogueItem(CatalogueRepository, cata, "col");
var extractionInfo = new ExtractionInformation(CatalogueRepository, catalogueItem, col, col.Name);
var qb = new QueryBuilder(null, null);
qb.TopX = 35;
qb.AddColumn(extractionInfo);
Assert.AreEqual(
CollapseWhitespace(
@"SELECT
`db`.`tbl`.`col`
FROM
`db`.`tbl`
LIMIT 35")
, CollapseWhitespace(qb.SQL));
//editting the topX should invalidate the SQL automatically
qb.TopX = 50;
Assert.AreEqual(
CollapseWhitespace(
@"SELECT
`db`.`tbl`.`col`
FROM
`db`.`tbl`
LIMIT 50")
, CollapseWhitespace(qb.SQL));
}
public void TestQueryBuilder_MicrosoftSQLServer_Top35()
{
var t = new TableInfo(CatalogueRepository, "[db]..[tbl]");
t.DatabaseType = DatabaseType.MicrosoftSQLServer;
t.SaveToDatabase();
var col = new ColumnInfo(CatalogueRepository, "[db]..[tbl].[col]", "varchar(10)", t);
Assert.AreEqual("col", col.GetRuntimeName());
var cata = new Catalogue(CatalogueRepository, "cata");
var catalogueItem = new CatalogueItem(CatalogueRepository, cata, "col");
var extractionInfo = new ExtractionInformation(CatalogueRepository, catalogueItem, col, col.Name);
var qb = new QueryBuilder(null, null);
qb.TopX = 35;
qb.AddColumn(extractionInfo);
Assert.AreEqual(
CollapseWhitespace(
@"SELECT
TOP 35
[db]..[tbl].[col]
FROM
[db]..[tbl]")
, CollapseWhitespace(qb.SQL));
//editting the topX should invalidate the SQL automatically
qb.TopX = 50;
Assert.AreEqual(
CollapseWhitespace(
@"SELECT
TOP 50
[db]..[tbl].[col]
FROM
[db]..[tbl]")
, CollapseWhitespace(qb.SQL));
}
/// <inheritdoc/>
public string GetRuntimeName()
{
return(ColumnInfo.GetRuntimeName());
}
/// <summary>
/// Composes the SQL which joins the supplied table back up or down to the TimePeriodicity table, so we can assign the rows an effective load date
/// </summary>
/// <param name="tableAlias"></param>
/// <param name="tableInfo"></param>
/// <param name="timePeriodTableAlias"></param>
/// <param name="dbInfo"></param>
/// <param name="joinPath"></param>
/// <returns></returns>
public string CreateSqlForJoinToTimePeriodicityTable(string tableAlias, ITableInfo tableInfo, string timePeriodTableAlias, DiscoveredDatabase dbInfo, List <JoinInfo> joinPath)
{
if (tableInfo.ID == _timePeriodicityField.TableInfo_ID && joinPath.Count > 0)
{
throw new InvalidOperationException("You have asked for a join where the original table *is* the TimePeriodicityTable but a non-empty join path has been provided. There should be no path when dealing directly with the TimePeriodicity table");
}
// Simple case, there is no join so we are just selecting the row and aliasing the TimePeriodicityField for the provided table
if (!joinPath.Any())
{
return(string.Format(@"SELECT {0}.*, {0}.{1} AS TimePeriodicityField FROM {2} {0}",
tableAlias, _timePeriodicityField.GetRuntimeName(),
"[" + dbInfo.GetRuntimeName() + "]..[" + tableInfo.GetRuntimeName() + "]"));
}
// Ensure that the TimePeriodicityTable is at the end of the path (to make constructing the join a bit easier)
if (joinPath[0].ForeignKey.TableInfo_ID == _tiWithTimeColumn.ID || joinPath[0].PrimaryKey.TableInfo_ID == _tiWithTimeColumn.ID)
{
joinPath.Reverse();
}
if ((joinPath[joinPath.Count - 1].ForeignKey.TableInfo_ID != _tiWithTimeColumn.ID) && (joinPath[joinPath.Count - 1].PrimaryKey.TableInfo_ID != _tiWithTimeColumn.ID))
{
throw new InvalidOperationException("The TimePeriodicity table is not at the beginning or end of the join path.");
}
var sql = string.Format(@"SELECT {0}.*, {1}.{2} AS TimePeriodicityField
FROM {3} {4}",
tableAlias, timePeriodTableAlias, _timePeriodicityField.GetRuntimeName(),
"[" + dbInfo.GetRuntimeName() + "]..[" + tableInfo.GetRuntimeName() + "]",
tableAlias);
// Is our table a parent or child? The join is composed differently.
var ascending = tableInfo.ID == joinPath[0].ForeignKey.TableInfo_ID;
for (var i = 0; i < joinPath.Count; i++)
{
var join = joinPath[i];
if (ascending)
{
var parentTable = join.PrimaryKey.TableInfo;
var childTableAlias = (i == 0) ? tableAlias : "j" + i;
var parentTableAlias = (i == (joinPath.Count - 1)) ? timePeriodTableAlias : "j" + (i + 1);
sql += string.Format(@"
LEFT JOIN {0} {1} ON {1}.{3} = {2}.{4}",
"[" + dbInfo.GetRuntimeName() + "]..[" + parentTable.GetRuntimeName() + "]",
parentTableAlias,
childTableAlias,
join.PrimaryKey.GetRuntimeName(),
join.ForeignKey.GetRuntimeName());
}
else
{
var childTable = join.ForeignKey.TableInfo;
var parentTableAlias = (i == 0) ? tableAlias : "j" + (i + 1);
var childTableAlias = (i == (joinPath.Count - 1)) ? timePeriodTableAlias : "j" + i;
sql += string.Format(@"
LEFT JOIN {0} {1} ON {2}.{3} = {1}.{4}",
"[" + dbInfo.GetRuntimeName() + "]..[" + childTable.GetRuntimeName() + "]",
childTableAlias,
parentTableAlias,
join.PrimaryKey.GetRuntimeName(),
join.ForeignKey.GetRuntimeName());
}
}
return(sql);
}
private void MakeANOTableSuggestionIfApplicable()
{
//if there is a ColumnInfo with the same name (or that has ANO prefix)
var matchingOnName = _allColumnInfosSystemWide.Where(a => a.GetRuntimeName() == ColumnInfo.GetRuntimeName() || a.GetRuntimeName() == "ANO" + ColumnInfo.GetRuntimeName()).ToArray();
//and if the same named ColumnInfo(s) have a shared ANOTable (e.g. ANOCHI)
var agreedAnoTableID = matchingOnName.Where(c => c.ANOTable_ID != null).Select(c => c.ANOTable_ID).Distinct().ToArray();
//if there is a single recommended anotable id amongst all columns with matching name featuring ano tables
if (agreedAnoTableID.Count() == 1)
{
ANOTable = ColumnInfo.Repository.GetObjectByID <ANOTable>(agreedAnoTableID.Single().Value);
Plan = Plan.ANO;
}
}
/// <summary>
/// Returns the fully qualified RAW name of the column factoring in namer e.g. [ab213_ImagingRAW]..[StudyTable].[MyCol]
/// </summary>
/// <param name="col"></param>
/// <returns></returns>
private string GetRAWColumnNameFullyQualified(ColumnInfo col)
{
return(_syntaxHelper.EnsureFullyQualified(_raw.GetRuntimeName(), null, col.TableInfo.GetRuntimeName(LoadBubble.Raw, _namer), col.GetRuntimeName(LoadStage.AdjustRaw)));
}
private void GeneratePreviews()
{
if (preview == null)
{
preview = new DataTable();
preview.Columns.Add(_columnInfo.GetRuntimeName(LoadStage.PostLoad));
preview.Columns.Add(ANOTable.ANOPrefix + _columnInfo.GetRuntimeName(LoadStage.PostLoad));
var server = DataAccessPortal.GetInstance().ExpectServer(ColumnInfo.TableInfo, DataAccessContext.DataLoad);
using (var con = server.GetConnection())
{
con.Open();
lblPreviewDataIsFictional.Visible = false;
var qb = new QueryBuilder(null, null, new[] { ColumnInfo.TableInfo });
qb.AddColumn(new ColumnInfoToIColumn(new MemoryRepository(), _columnInfo));
qb.TopX = 10;
bool rowsRead = false;
using (DbCommand cmd = server.GetCommand(qb.SQL, con))
{
cmd.CommandTimeout = Convert.ToInt32(ntimeout.Value);
using (var r = cmd.ExecuteReader())
{
while (r.Read())
{
preview.Rows.Add(r[_columnInfo.GetRuntimeName(LoadStage.PostLoad)], DBNull.Value);
rowsRead = true;
}
}
}
if (!rowsRead)
{
lblPreviewDataIsFictional.Visible = true;
if (_columnInfo.GetRuntimeDataType(LoadStage.AdjustRaw).ToLower().Contains("char"))
{
preview.Rows.Add("?", DBNull.Value);
preview.Rows.Add("?", DBNull.Value);
preview.Rows.Add("?", DBNull.Value);
preview.Rows.Add("?", DBNull.Value);
}
else if (_columnInfo.GetRuntimeDataType(LoadStage.AdjustRaw).ToLower().Contains("date"))
{
preview.Rows.Add("1977-08-16", DBNull.Value);
preview.Rows.Add("1977-08-16", DBNull.Value);
preview.Rows.Add("1977-08-16", DBNull.Value);
preview.Rows.Add("1977-08-16", DBNull.Value);
}
else
{
preview.Rows.Add("-1", DBNull.Value);
preview.Rows.Add("-1", DBNull.Value);
preview.Rows.Add("-1", DBNull.Value);
preview.Rows.Add("-1", DBNull.Value);
}
}
con.Close();
}
}
if (ANOTable != null)
{
try
{
if (preview.Rows.Count != 0)
{
checksUI1.Clear();
ANOTransformer transformer = new ANOTransformer(ANOTable, new FromCheckNotifierToDataLoadEventListener(checksUI1));
transformer.Transform(preview, preview.Columns[0], preview.Columns[1], true);
}
}
catch (Exception e)
{
checksUI1.OnCheckPerformed(new CheckEventArgs(e.Message, CheckResult.Fail, e));
}
}
dgPreview.DataSource = preview;
}
public ColumnInfoValuesRejector(ColumnInfo columnInfo) : base(columnInfo.GetRuntimeName(), FetchTable(columnInfo))
{
}
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);
}
}
}
