public string GetCommand()
{
var repo = new MemoryCatalogueRepository();
var qb = new QueryBuilder("distinct", null);
qb.AddColumn(new ColumnInfoToIColumn(repo, _keyColumn)
{
Order = 0
});
qb.AddColumn(new ColumnInfoToIColumn(repo, _descriptionColumn)
{
Order = 1
});
qb.TopX = 100;
var container = new SpontaneouslyInventedFilterContainer(repo, null, null, FilterContainerOperation.AND);
if (!string.IsNullOrWhiteSpace(tbCode.Text))
{
var codeFilter = new SpontaneouslyInventedFilter(repo, container, _keyColumn.GetFullyQualifiedName() + " LIKE '" + tbCode.Text + "%'", "Key Starts", "", null);
container.AddChild(codeFilter);
}
if (!string.IsNullOrWhiteSpace(tbDescription.Text))
{
var codeFilter = new SpontaneouslyInventedFilter(repo, container, _descriptionColumn.GetFullyQualifiedName() + " LIKE '%" + tbDescription.Text + "%'", "Description Contains", "", null);
container.AddChild(codeFilter);
}
qb.RootFilterContainer = container;
return(qb.SQL);
}
private void AddDistributionColumns(QueryBuilder qb)
{
var repo = new MemoryRepository();
qb.AddColumn(new SpontaneouslyInventedColumn(repo, "CountTotal", "count(1)"));
qb.AddColumn(new SpontaneouslyInventedColumn(repo, "CountNull", "SUM(CASE WHEN " + GetColumnSelectSql() + " IS NULL THEN 1 ELSE 0 END)"));
qb.AddColumn(new SpontaneouslyInventedColumn(repo, "CountZero", "SUM(CASE WHEN " + GetColumnSelectSql() + " = 0 THEN 1 ELSE 0 END)"));
qb.AddColumn(new SpontaneouslyInventedColumn(repo, "Max", "max(" + GetColumnSelectSql() + ")"));
qb.AddColumn(new SpontaneouslyInventedColumn(repo, "Min", "min(" + GetColumnSelectSql() + ")"));
switch (ColumnInfo.GetQuerySyntaxHelper().DatabaseType)
{
case DatabaseType.MicrosoftSQLServer:
qb.AddColumn(new SpontaneouslyInventedColumn(repo, "stdev ", "stdev(" + GetColumnSelectSql() + ")"));
break;
case DatabaseType.MySql:
case DatabaseType.Oracle:
qb.AddColumn(new SpontaneouslyInventedColumn(repo, "stddev ", "stddev(" + GetColumnSelectSql() + ")"));
break;
default:
throw new ArgumentOutOfRangeException();
}
qb.AddColumn(new SpontaneouslyInventedColumn(repo, "avg", "avg(" + GetColumnSelectSql() + ")"));
}
private void GetWhitelist(IDataLoadEventListener listener)
{
_whitelist = new HashSet <string>();
var db = DataAccessPortal.GetInstance().ExpectDatabase(PatientIdWhitelistColumnInfo.TableInfo, DataAccessContext.DataLoad);
var server = db.Server;
var qb = new QueryBuilder("distinct", null);
qb.AddColumn(new ColumnInfoToIColumn(new MemoryRepository(), PatientIdWhitelistColumnInfo));
var sql = qb.SQL;
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Downloading Whitelist with SQL:" + sql));
using (var con = server.GetConnection())
{
con.Open();
var r = server.GetCommand(sql, con).ExecuteReader();
while (r.Read())
{
var o = r[PatientIdWhitelistColumnInfo.GetRuntimeName()];
if (o == null || o == DBNull.Value)
{
continue;
}
_whitelist.Add(o.ToString());
}
}
listener.OnNotify(this, new NotifyEventArgs(_whitelist.Count == 0 ? ProgressEventType.Error : ProgressEventType.Information, "Whitelist contained " + _whitelist.Count + " identifiers"));
}
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));
}
private DataTable GetDataTable(int timeout, int?topX)
{
var qb = new QueryBuilder("distinct", null);
if (topX != null)
{
qb.TopX = topX.Value;
}
qb.AddColumn(_extractionInformation);
var server = _extractionInformation.CatalogueItem.Catalogue.GetDistinctLiveDatabaseServer(DataAccessContext.DataExport, true);
var colName = _extractionInformation.GetRuntimeName();
DataTable dt = new DataTable();
dt.Columns.Add(colName);
using (var con = server.GetConnection())
{
con.Open();
var cmd = server.GetCommand(qb.SQL, con);
cmd.CommandTimeout = timeout;
var r = cmd.ExecuteReader();
while (r.Read())
{
dt.Rows.Add(new[] { r[colName] });
}
}
return(dt);
}
private static HashSet <string> FetchTable(ColumnInfo columnInfo)
{
var logger = LogManager.GetCurrentClassLogger();
HashSet <string> toReturn = new HashSet <string>(StringComparer.CurrentCultureIgnoreCase);
var qb = new QueryBuilder(limitationSQL: null, hashingAlgorithm: null);
qb.AddColumn(new ColumnInfoToIColumn(new MemoryRepository(), columnInfo));
string sql = qb.SQL;
logger.Info("Running PatientID fetch SQL:" + sql);
DiscoveredTable server = columnInfo.TableInfo.Discover(DataAccessContext.DataExport);
using (DbConnection con = server.Database.Server.GetConnection())
{
con.Open();
DbCommand cmd = server.GetCommand(sql, con);
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
toReturn.Add(reader[0].ToString());
}
}
logger.Info($"Found {toReturn.Count} patients in the reject list");
return(toReturn);
}
private string GetMappingTableSql()
{
var repo = new MemoryCatalogueRepository();
var qb = new QueryBuilder("DISTINCT", null, null);
qb.AddColumn(new ColumnInfoToIColumn(repo, MappingFromColumn));
qb.AddColumn(new ColumnInfoToIColumn(repo, MappingToColumn));
if (!string.IsNullOrWhiteSpace(WHERELogic))
{
var container = new SpontaneouslyInventedFilterContainer(repo, null, null, FilterContainerOperation.AND);
var filter = new SpontaneouslyInventedFilter(repo, container, WHERELogic, "WHERELogic", null, null);
container.AddChild(filter);
qb.RootFilterContainer = container;
}
return(qb.SQL);
}
public void Test_IsPrimaryExtractionTable_TwoTables()
{
var c1 = WhenIHaveA <ColumnInfo>();
var c2 = WhenIHaveA <ColumnInfo>();
c1.TableInfo.IsPrimaryExtractionTable = true;
c1.TableInfo.SaveToDatabase();
c2.TableInfo.IsPrimaryExtractionTable = true;
c2.TableInfo.SaveToDatabase();
QueryBuilder builder = new QueryBuilder(null, null);
builder.AddColumn(new ColumnInfoToIColumn(Repository, c1));
builder.AddColumn(new ColumnInfoToIColumn(Repository, c2));
var ex = Assert.Throws <QueryBuildingException>(() => { var s = builder.SQL; });
StringAssert.Contains("There are multiple tables marked as IsPrimaryExtractionTable", ex.Message);
}
/// <summary>
/// Creates a query builder with all the columns required to match rows on the
/// </summary>
/// <returns></returns>
protected virtual QueryBuilder GetQueryBuilder()
{
var qb = new QueryBuilder("distinct", null);
foreach (var col in Columns.AllColumns)
{
qb.AddColumn(col);
}
qb.RootFilterContainer = GetWhereLogic();
return(qb);
}
public void Test_TwoTables_JoinFound()
{
//4 tables
var c1 = WhenIHaveA <ColumnInfo>();
var c2 = WhenIHaveA <ColumnInfo>();
//1 is primary
c1.TableInfo.IsPrimaryExtractionTable = true;
c1.TableInfo.SaveToDatabase();
var j1 = new JoinInfo(Repository, c2, c1, ExtractionJoinType.Inner, null);
QueryBuilder builder = new QueryBuilder(null, null);
builder.AddColumn(new ColumnInfoToIColumn(Repository, c1));
builder.AddColumn(new ColumnInfoToIColumn(Repository, c2));
StringAssert.Contains("JOIN", builder.SQL);
//we have 1 legit join go go team!
Assert.AreEqual(1, builder.JoinsUsedInQuery.Count);
Assert.AreEqual(j1, builder.JoinsUsedInQuery[0]);
}
public string GetSql()
{
var qb = new QueryBuilder(null, null, new[] { GetTableInfo() });
if (ViewType == ViewType.TOP_100)
{
qb.TopX = 100;
}
if (ViewType == ViewType.Distribution)
{
AddDistributionColumns(qb);
}
else
{
qb.AddColumn(GetIColumn());
}
var filter = GetFilterIfAny();
var container = GetContainerIfAny();
if (filter != null && container != null)
{
throw new Exception("Cannot generate SQL with both filter and container");
}
if (filter != null && !string.IsNullOrWhiteSpace(filter.WhereSQL))
{
qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(new MemoryCatalogueRepository(), null, new[] { filter }, FilterContainerOperation.AND);
}
else if (container != null)
{
qb.RootFilterContainer = container;
}
if (ViewType == ViewType.Aggregate)
{
qb.AddCustomLine("count(*) as Count,", QueryComponent.QueryTimeColumn);
}
var sql = qb.SQL;
if (ViewType == ViewType.Aggregate)
{
sql += " GROUP BY " + GetColumnSelectSql();
}
return(sql);
}
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));
}
public string GetSql()
{
var qb = new QueryBuilder(null, null, new [] { ColumnInfo.TableInfo });
if (ViewType == ViewType.TOP_100)
{
qb.TopX = 100;
}
if (ViewType == ViewType.Distribution)
{
AddDistributionColumns(qb);
}
else
{
qb.AddColumn(new ColumnInfoToIColumn(new MemoryRepository(), ColumnInfo));
}
var filter = GetFilterIfAny();
if (filter != null && !string.IsNullOrWhiteSpace(filter.WhereSQL))
{
qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(new MemoryCatalogueRepository(), null, new[] { filter }, FilterContainerOperation.AND);
}
if (ViewType == ViewType.Aggregate)
{
qb.AddCustomLine("count(*),", QueryComponent.QueryTimeColumn);
}
var sql = qb.SQL;
if (ViewType == ViewType.Aggregate)
{
sql += " GROUP BY " + ColumnInfo;
}
return(sql);
}
public void Test_DatabaseTypeQueryWithParameter_IntParameter(DatabaseType dbType)
{
//Pick the destination server
var tableName = TestDatabaseNames.GetConsistentName("tbl");
//make sure there's a database ready to receive the data
var db = GetCleanedServer(dbType);
db.Create(true);
//this is the table we are uploading
var dt = new DataTable();
dt.Columns.Add("numbercol");
dt.Rows.Add(10);
dt.Rows.Add(15);
dt.Rows.Add(20);
dt.Rows.Add(25);
dt.TableName = tableName;
try
{
///////////////////////UPLOAD THE DataTable TO THE DESTINATION////////////////////////////////////////////
var uploader = new DataTableUploadDestination();
uploader.PreInitialize(db, new ThrowImmediatelyDataLoadJob());
uploader.ProcessPipelineData(dt, new ThrowImmediatelyDataLoadJob(), new GracefulCancellationToken());
uploader.Dispose(new ThrowImmediatelyDataLoadJob(), null);
var tbl = db.ExpectTable(tableName);
var importer = new TableInfoImporter(CatalogueRepository, tbl);
importer.DoImport(out var ti, out var ci);
var engineer = new ForwardEngineerCatalogue(ti, ci, true);
engineer.ExecuteForwardEngineering(out var cata, out var cis, out var ei);
/////////////////////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////THE ACTUAL PROPER TEST////////////////////////////////////
//create an extraction filter
var extractionInformation = ei.Single();
var filter = new ExtractionFilter(CatalogueRepository, "Filter by numbers", extractionInformation);
filter.WhereSQL = extractionInformation.SelectSQL + " = @n";
filter.SaveToDatabase();
//create the parameters for filter (no globals, masters or scope adjacent parameters)
new ParameterCreator(filter.GetFilterFactory(), null, null).CreateAll(filter, null);
var p = filter.GetAllParameters().Single();
Assert.AreEqual("@n", p.ParameterName);
p.ParameterSQL = p.ParameterSQL.Replace("varchar(50)", "int"); //make it int
p.Value = "20";
p.SaveToDatabase();
var qb = new QueryBuilder(null, null);
qb.AddColumn(extractionInformation);
qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(new MemoryCatalogueRepository(), null, new[] { filter }, FilterContainerOperation.AND);
using (var con = db.Server.GetConnection())
{
con.Open();
string sql = qb.SQL;
var cmd = db.Server.GetCommand(sql, con);
var r = cmd.ExecuteReader();
Assert.IsTrue(r.Read());
Assert.AreEqual(
20,
r[extractionInformation.GetRuntimeName()]);
}
///////////////////////////////////////////////////////////////////////////////////////
}
finally
{
db.Drop();
}
}
private void Initialize()
{
//Figure out which UID columns exist in the Catalogue, do not require file path to be in Catalogue
_columnSet = QueryToExecuteColumnSet.Create(_catalogue, false);
//Tells us the DBMS type
var syntax = _catalogue.GetQuerySyntaxHelper();
//For storing the OR container and filter(s)
var memory = new MemoryCatalogueRepository();
//builds SQL we will run in lookup stage
_queryBuilder = new QueryBuilder(null, null);
//all we care about is if the uid appears if it does then we are rejecting it
_queryBuilder.TopX = 1;
//Filter is OR i.e. StudyInstanceUID = @StudyInstanceUID OR SeriesInstanceUID = @SeriesInstanceUID
var container = _queryBuilder.RootFilterContainer = new SpontaneouslyInventedFilterContainer(memory, null, null, FilterContainerOperation.OR);
//Build SELECT and WHERE bits of the query
if (_columnSet.StudyTagColumn != null)
{
_queryBuilder.AddColumn(_columnSet.StudyTagColumn);
string whereSql =
$"{_columnSet.StudyTagColumn.SelectSQL} = {syntax.ParameterSymbol}{QueryToExecuteColumnSet.DefaultStudyIdColumnName}";
_studyFilter = new SpontaneouslyInventedFilter(memory, container, whereSql, "Study UID Filter", "", null);
container.AddChild(_studyFilter);
}
if (_columnSet.SeriesTagColumn != null)
{
_queryBuilder.AddColumn(_columnSet.SeriesTagColumn);
string whereSql =
$"{_columnSet.SeriesTagColumn.SelectSQL} = {syntax.ParameterSymbol}{QueryToExecuteColumnSet.DefaultSeriesIdColumnName}";
_seriesFilter = new SpontaneouslyInventedFilter(memory, container, whereSql, "Series UID Filter", "", null);
container.AddChild(_seriesFilter);
}
if (_columnSet.InstanceTagColumn != null)
{
_queryBuilder.AddColumn(_columnSet.InstanceTagColumn);
string whereSql =
$"{_columnSet.InstanceTagColumn.SelectSQL} = {syntax.ParameterSymbol}{QueryToExecuteColumnSet.DefaultInstanceIdColumnName}";
_instanceFilter = new SpontaneouslyInventedFilter(memory, container, whereSql, "Instance UID Filter", "", null);
container.AddChild(_instanceFilter);
}
// Make sure the query builder looks valid
if (!_queryBuilder.SelectColumns.Any())
{
throw new NotSupportedException($"Blacklist Catalogue {_catalogue} (ID={_catalogue.ID}) did not have any Core ExtractionInformation columns corresponding to any of the image UID tags (e.g. StudyInstanceUID, SeriesInstanceUID, SOPInstanceUID).");
}
try
{
// make sure we can connect to the server
_server = _catalogue.GetDistinctLiveDatabaseServer(DataAccessContext.DataExport, true);
_server.TestConnection();
}
catch (Exception e)
{
throw new Exception($"Failed to test connection for Catalogue {_catalogue}", e);
}
// run a test lookup query against the remote database
DoLookup("test1", "test2", "test3");
}
private void BuildJoinOrder(bool isChecks)
{
_qb = new QueryBuilder(null, null);
var memory = new MemoryRepository();
foreach (TableInfo t in TablesToIsolate)
{
_qb.AddColumn(new ColumnInfoToIColumn(memory, t.ColumnInfos.First()));
}
_primaryTable = TablesToIsolate.Length == 1 ? TablesToIsolate[0] : TablesToIsolate.Single(t => t.IsPrimaryExtractionTable);
_primaryTablePk = _primaryTable.ColumnInfos.Single(c => c.IsPrimaryKey);
_qb.PrimaryExtractionTable = _primaryTable;
_qb.RegenerateSQL();
_joins = _qb.JoinsUsedInQuery ?? new List <JoinInfo>();
_fromSql = SqlQueryBuilderHelper.GetFROMSQL(_qb);
if (!isChecks)
{
foreach (TableInfo tableInfo in TablesToIsolate)
{
_fromSql = _fromSql.Replace(tableInfo.GetFullyQualifiedName(), GetRAWTableNameFullyQualified(tableInfo));
}
}
if (_joins.Any(j => j.GetSupplementalJoins().Any()))
{
throw new Exception("Supplemental (2 column) joins are not supported when resolving multi table primary key collisions");
}
//order the tables in order of dependency
List <TableInfo> tables = new List <TableInfo>();
TableInfo next = _primaryTable;
int overflow = 10;
while (next != null)
{
tables.Add(next);
var jnext = _joins.SingleOrDefault(j => j.PrimaryKey.TableInfo.Equals(next));
if (jnext == null)
{
break;
}
next = jnext.ForeignKey.TableInfo;
if (overflow-- == 0)
{
throw new Exception("Joins resulted in a loop overflow");
}
}
TablesToIsolate = tables.ToArray();
}
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 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 Test_FourTables_MultipleRoutes()
{
//4 tables
var c1 = WhenIHaveA <ColumnInfo>();
c1.Name = "c1";
c1.SaveToDatabase();
c1.TableInfo.Name = "t1";
c1.TableInfo.IsPrimaryExtractionTable = true; //t1 is primary
c1.TableInfo.SaveToDatabase();
var c2 = WhenIHaveA <ColumnInfo>();
c2.Name = "c2";
c2.SaveToDatabase();
c2.TableInfo.Name = "t2";
c2.TableInfo.SaveToDatabase();
var c3 = WhenIHaveA <ColumnInfo>();
c3.Name = "c3";
c3.SaveToDatabase();
c3.TableInfo.Name = "t3";
c3.TableInfo.SaveToDatabase();
var c4 = WhenIHaveA <ColumnInfo>();
c4.Name = "c4";
c4.SaveToDatabase();
c4.TableInfo.Name = "t4";
c4.TableInfo.SaveToDatabase();
/* c2
* / \
* c1 c4
* \ /
* c3
*
* */
var j1 = new JoinInfo(Repository, c2, c1, ExtractionJoinType.Inner, null);
var j2 = new JoinInfo(Repository, c3, c1, ExtractionJoinType.Inner, null);
var j3 = new JoinInfo(Repository, c4, c2, ExtractionJoinType.Inner, null);
var j4 = new JoinInfo(Repository, c4, c3, ExtractionJoinType.Inner, null);
QueryBuilder builder = new QueryBuilder(null, null);
builder.AddColumn(new ColumnInfoToIColumn(Repository, c1));
builder.AddColumn(new ColumnInfoToIColumn(Repository, c2));
builder.AddColumn(new ColumnInfoToIColumn(Repository, c3));
builder.AddColumn(new ColumnInfoToIColumn(Repository, c4));
Console.WriteLine(builder.SQL);
//should be using only 3 of the 4 joins because we already have a route to c4 without a fourth join
Assert.AreEqual(3, builder.JoinsUsedInQuery.Count);
Assert.Contains(j1, builder.JoinsUsedInQuery);
Assert.Contains(j2, builder.JoinsUsedInQuery);
Assert.Contains(j3, builder.JoinsUsedInQuery);
}
public void OpportunisticJoinRequired()
{
var memory = new MemoryRepository();
//tables and columns
TableInfo head = new TableInfo(CatalogueRepository, "Head");
ColumnInfo col1 = new ColumnInfo(CatalogueRepository, "TestResultSetNumber", "int", head);
ColumnInfo col2 = new ColumnInfo(CatalogueRepository, "PK", "int", head);
TableInfo result = new TableInfo(CatalogueRepository, "[biochemistry]..[Result]");
ColumnInfo col3 = new ColumnInfo(CatalogueRepository, "FK", "int", result);
ColumnInfo col4 = new ColumnInfo(CatalogueRepository, "Code", "varchar(10)", result);
ColumnInfo col5 = new ColumnInfo(CatalogueRepository, "[biochemistry]..[Result].[OmgBob]", "varchar(10)", result);
//we can join on col2 = col3
new JoinInfo(CatalogueRepository, col3, col2, ExtractionJoinType.Right, "");
//CASE 1 : Only 1 column used so no join needed
var queryBuilder = new QueryBuilder(null, null);
var icol1 = new ColumnInfoToIColumn(memory, col1);
icol1.Order = 1;
queryBuilder.AddColumn(icol1);
TableInfo primary;
var tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(1, tablesUsed.Count);
Assert.AreEqual(head, tablesUsed[0]);
//CASE 2 : 2 columns used one from each table so join is needed
queryBuilder = new QueryBuilder(null, null);
queryBuilder.AddColumn(new ColumnInfoToIColumn(memory, col1));
var icol4 = new ColumnInfoToIColumn(memory, col4);
icol4.Order = 2;
queryBuilder.AddColumn(icol4);
tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(2, tablesUsed.Count);
Assert.AreEqual(head, tablesUsed[0]);
Assert.AreEqual(result, tablesUsed[1]);
Assert.AreEqual(CollapseWhitespace(@"SELECT
TestResultSetNumber,
Code
FROM
[biochemistry]..[Result] Right JOIN Head ON FK = PK"), CollapseWhitespace(queryBuilder.SQL));
var memoryRepository = new MemoryCatalogueRepository();
var spontContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, null, null, FilterContainerOperation.AND);
var spontFilter = new SpontaneouslyInventedFilter(memoryRepository, spontContainer, "[biochemistry]..[Result].[OmgBob] = 'T'",
"My Filter", "Causes spontaneous requirement for joining compeltely", null);
spontContainer.AddChild(spontFilter);
//CASE 3 : Only 1 column from Head but filter contains a reference to Result column
queryBuilder = new QueryBuilder(null, null);
queryBuilder.AddColumn(new ColumnInfoToIColumn(memory, col1));
//without the filter
tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(1, tablesUsed.Count);
//set the filter
queryBuilder.RootFilterContainer = spontContainer;
//this is super sneaky but makes the queryBuilder populate it's Filters property... basically your not supposed to use SqlQueryBuilderHelper for this kind of thing
Console.WriteLine(queryBuilder.SQL);
queryBuilder.ParameterManager.ClearNonGlobals();
//with the filter
tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(2, tablesUsed.Count);
}
