public void SetToRecommendedPlan()
{
//get an extraction category based on it's current extractability
ExtractionCategoryIfAny = GetMaxExtractionCategoryIfAny();
if (SpecialFieldNames.IsHicPrefixed(ColumnInfo))
{
Plan = Plan.Drop;//suggest dropping hic_ fields
}
else
if (ColumnInfo.IsPrimaryKey || IsInvolvedInLookups() || IsInvolvedInJoins())
{
Plan = Plan.PassThroughUnchanged; //if it's involved in lookups, joins or is a primary key
}
else
if (ExtractionCategoryIfAny == null) //if it isn't extractable
{
Plan = Plan.Drop;
}
else
{
Plan = Plan.PassThroughUnchanged; //it is extractable but not special
}
//if theres an associated ANOTable with a different ColumnInfo with the same name e.g. chi=>ANOChi in another dataset that was already anonymised
MakeANOTableSuggestionIfApplicable();
}
private string AppendRelevantOrderBySql(string sql, IResolveDuplication col)
{
string colname = _querySyntaxHelper.EnsureWrapped(col.GetRuntimeName(LoadStage.AdjustRaw));
string direction = col.DuplicateRecordResolutionIsAscending ? " ASC" : " DESC";
//dont bother adding these because they are hic generated
if (SpecialFieldNames.IsHicPrefixed(colname))
{
return(sql);
}
ValueType valueType = GetDataType(col.Data_type);
if (valueType == ValueType.CharacterString)
{
//character strings are compared first by LENGTH (to prefer longer data)
//then by alphabetical comparison to prefer things towards the start of the alphabet (because this makes sense?!)
return
(sql +
"LEN(ISNULL(" + colname + "," + GetNullSubstituteForComparisonsWithDataType(col.Data_type, true) + "))" + direction + "," + Environment.NewLine +
"ISNULL(" + colname + "," + GetNullSubstituteForComparisonsWithDataType(col.Data_type, true) + ")" + direction + "," + Environment.NewLine);
}
return(sql + "ISNULL(" + colname + "," + GetNullSubstituteForComparisonsWithDataType(col.Data_type, true) + ")" + direction + "," + Environment.NewLine);
}
public void AssignFieldsForProcessing(DiscoveredColumn field, List <DiscoveredColumn> fieldsToDiff, List <DiscoveredColumn> fieldsToUpdate)
{
if (IgnoreColumnInfo(field))
{
return;
}
if (Ignore(field))
{
return;
}
//it is a hic internal field but not one of the overwritten, standard ones
if (SpecialFieldNames.IsHicPrefixed(field)
||
UpdateOnly(field))
{
fieldsToUpdate.Add(field);
}
else
{
//it is not a hic internal field
fieldsToDiff.Add(field);
fieldsToUpdate.Add(field);
}
}
private void ConfirmStagingAndLiveHaveSameColumns(string tableName, DiscoveredColumn[] stagingCols, DiscoveredColumn[] liveCols, bool requireSameNumberAndOrder, ICheckNotifier notifier)
{
//in LIVE but not STAGING
foreach (var missingColumn in liveCols.Select(c => c.GetRuntimeName()).Except(stagingCols.Select(c => c.GetRuntimeName())))
{
//column is in live but not in staging, but it is hic_
if (SpecialFieldNames.IsHicPrefixed(missingColumn)) //this is permitted
{
continue;
}
else
{
notifier.OnCheckPerformed(new CheckEventArgs(
"Column " + missingColumn + " is missing from STAGING", CheckResult.Fail, null));
}
}
//in STAGING but not LIVE
foreach (var missingColumn in stagingCols.Except(liveCols))
{
notifier.OnCheckPerformed(new CheckEventArgs(
"Column " + missingColumn + " is in STAGING but not LIVE", CheckResult.Fail, null));
}
if (requireSameNumberAndOrder)
{
bool passedColumnOrderCheck = true;
if (stagingCols.Length != liveCols.Length)
{
notifier.OnCheckPerformed(new CheckEventArgs(
"Column count mismatch between staging and live in table " + tableName, CheckResult.Fail, null));
passedColumnOrderCheck = false;
}
else
{
//check they are in the same order
for (int i = 0; i < stagingCols.Length; i++)
{
if (!stagingCols[i].Equals(liveCols[i]))
{
notifier.OnCheckPerformed(new CheckEventArgs(
"Column name/order mismatch between staging and live in table " + tableName +
", column " + i + " is " + stagingCols[i] + " in staging but is " + liveCols[i] +
" in live.", CheckResult.Fail, null));
passedColumnOrderCheck = false;
break;
}
}
}
if (passedColumnOrderCheck)
{
notifier.OnCheckPerformed(new CheckEventArgs("Column order match confirmed between staging and live on table " + tableName, CheckResult.Success, null));
}
}
}
public void CloneTable(DiscoveredDatabase srcDatabaseInfo, DiscoveredDatabase destDatabaseInfo, DiscoveredTable sourceTable, string destTableName, bool dropHICColumns, bool dropIdentityColumns, bool allowNulls, PreLoadDiscardedColumn[] dillutionColumns)
{
if (!sourceTable.Exists())
{
throw new Exception("Table " + sourceTable + " does not exist on " + srcDatabaseInfo);
}
//new table will start with the same name as the as the old scripted one
DiscoveredTable newTable = destDatabaseInfo.ExpectTable(destTableName);
var sql = sourceTable.ScriptTableCreation(allowNulls, allowNulls, false /*False because we want to drop these columns entirely not just flip to int*/, newTable);
using (var con = destDatabaseInfo.Server.GetConnection())
{
con.Open();
var cmd = destDatabaseInfo.Server.GetCommand(sql, con);
cmd.ExecuteNonQuery();
}
if (!newTable.Exists())
{
throw new Exception("Table '" + newTable + "' not found in " + destDatabaseInfo + " despite running table creation SQL!");
}
foreach (DiscoveredColumn column in newTable.DiscoverColumns())
{
bool drop = false;
var colName = column.GetRuntimeName();
if (column.IsAutoIncrement)
{
drop = true;
}
if (SpecialFieldNames.IsHicPrefixed(colName) && dropHICColumns)
{
drop = true;
}
//drop the data load run ID field and validFrom fields, we don't need them in STAGING or RAW, it will be hard coded in the MERGE migration with a fixed value anyway.
if (colName.Equals(SpecialFieldNames.DataLoadRunID) || colName.Equals(SpecialFieldNames.ValidFrom))
{
drop = true;
}
var dillution = dillutionColumns.SingleOrDefault(c => c.GetRuntimeName().Equals(colName));
if (dillution != null)
{
column.DataType.AlterTypeTo(dillution.Data_type);
}
if (drop)
{
newTable.DropColumn(column);
}
}
}
public void AssignFieldsForProcessing(DiscoveredColumn field, List <DiscoveredColumn> fieldsToDiff, List <DiscoveredColumn> fieldsToUpdate)
{
//it is a hic internal field but not one of the overwritten, standard ones
if (SpecialFieldNames.IsHicPrefixed(field)
||
IsSupplementalMatch(field))
{
fieldsToUpdate.Add(field);
}
else
{
//it is not a hic internal field
fieldsToDiff.Add(field);
fieldsToUpdate.Add(field);
}
}
public string BuildSelectListForAllColumnsExceptStandard(string tableAlias = "")
{
string sql = "";
foreach (DiscoveredColumn col in _migrationColumnSet.FieldsToDiff)
{
//if it is hic_ or identity specification
if (SpecialFieldNames.IsHicPrefixed(col) || col.IsAutoIncrement)
{
continue;
}
sql += tableAlias + "[" + col.GetRuntimeName() + "],";
}
return(sql.TrimEnd(','));
}
protected void AssessMissingAndIgnoredColumns(DataTable chunk, IDataLoadEventListener job)
{
DiscoveredColumn[] listColumns = _dbInfo.ExpectTable(Table).DiscoverColumns();
bool problemsWithColumnSets = false;
foreach (DataColumn colInSource in chunk.Columns)
{
if (!listColumns.Any(c => c.GetRuntimeName().Equals(colInSource.ColumnName, StringComparison.CurrentCultureIgnoreCase)))//there is something wicked this way coming, down the pipeline but not in the target table
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error,
"Column " + colInSource.ColumnName + " appears in pipeline but not destination table (" + Table + ") which is on (Database=" + _dbInfo.GetRuntimeName() + ",Server=" + _dbInfo.Server + ")"));
problemsWithColumnSets = true;
}
}
foreach (DiscoveredColumn columnInDestination in listColumns)
{
if (columnInDestination.GetRuntimeName().Equals(SpecialFieldNames.DataLoadRunID) ||
columnInDestination.GetRuntimeName().Equals(SpecialFieldNames.ValidFrom))
{
//its fine if validFrom/DataLoadRunID columns are missing
continue;//its fine
}
else
if (!chunk.Columns.Contains(columnInDestination.GetRuntimeName())) //its not fine if there are other columns missing (at the very least we should warn the user.
{
bool isBigProblem = !SpecialFieldNames.IsHicPrefixed(columnInDestination);
job.OnNotify(this,
new NotifyEventArgs(isBigProblem?ProgressEventType.Error:ProgressEventType.Warning, //hic_ columns could be ok if missing so only warning, otherwise go error
"Column " + columnInDestination.GetRuntimeName() + " appears in destination table (" + Table + ") but is not in the pipeline (will probably be left as NULL)"));
if (isBigProblem)
{
problemsWithColumnSets = true;
}
}
}
if (problemsWithColumnSets)
{
throw new Exception("There was a mismatch between the columns in the pipeline and the destination table, check earlier progress messages for details on the missing columns");
}
}
private void CheckStagingToLiveMigrationForTable(DiscoveredTable stagingTable, DiscoveredColumn[] stagingCols, DiscoveredTable liveTable, DiscoveredColumn[] liveCols, ICheckNotifier notifier)
{
try
{
new MigrationColumnSet(stagingTable, liveTable, new StagingToLiveMigrationFieldProcessor()
{
NoBackupTrigger = _loadMetadata.IgnoreTrigger
});
notifier.OnCheckPerformed(new CheckEventArgs("TableInfo " + liveTable + " passed " + typeof(MigrationColumnSet).Name + " check ", CheckResult.Success, null));
}
catch (Exception e)
{
notifier.OnCheckPerformed(new CheckEventArgs(
typeof(MigrationColumnSet).Name + " reports a problem with the configuration of columns on STAGING/LIVE or in the ColumnInfos for TableInfo " + liveTable, CheckResult.Fail, e));
}
//live columns
foreach (DiscoveredColumn col in liveCols)
{
if (!SpecialFieldNames.IsHicPrefixed(col) && col.IsAutoIncrement) //must start hic_ if they are identities
{
notifier.OnCheckPerformed(new CheckEventArgs("Column " + col + " is an identity column in the LIVE database but does not start with hic_", CheckResult.Fail, null)); //this one does not
}
}
//staging columns
foreach (DiscoveredColumn col in stagingCols) //staging columns
{
if (col.IsAutoIncrement) //if there are any auto increments
{
notifier.OnCheckPerformed(new CheckEventArgs(
"Column " + col + " is an identity column and is in STAGING, the identity flag must be removed from the STAGING table", CheckResult.Fail, null));//complain since don't want a mismatch between IDs in staging and live or complaints about identity insert from SQL server
}
}
//staging must allow null dataloadrunids and validfroms
ConfirmNullability(stagingTable.DiscoverColumn(SpecialFieldNames.DataLoadRunID), true, notifier);
ConfirmNullability(stagingTable.DiscoverColumn(SpecialFieldNames.ValidFrom), true, notifier);
//live must allow nulls in validFrom
ConfirmNullability(liveTable.DiscoverColumn(SpecialFieldNames.ValidFrom), true, notifier);
}
/// <inheritdoc/>
public IEnumerable <IHasStageSpecificRuntimeName> GetColumnsAtStage(LoadStage loadStage)
{
//if it is AdjustRaw then it will also have the pre load discarded columns
if (loadStage <= LoadStage.AdjustRaw)
{
foreach (PreLoadDiscardedColumn discardedColumn in PreLoadDiscardedColumns.Where(c => c.Destination != DiscardedColumnDestination.Dilute))
{
yield return(discardedColumn);
}
}
//also add column infos
foreach (ColumnInfo c in ColumnInfos)
{
if (loadStage <= LoadStage.AdjustRaw && SpecialFieldNames.IsHicPrefixed(c))
{
continue;
}
else
if (loadStage <= LoadStage.AdjustStaging && c.IsAutoIncrement) //auto increment columns do not get created in RAW/STAGING
{
continue;
}
else
if (loadStage == LoadStage.AdjustStaging &&
//these two do not appear in staging
(c.GetRuntimeName().Equals(SpecialFieldNames.DataLoadRunID) || c.GetRuntimeName().Equals(SpecialFieldNames.ValidFrom))
)
{
continue;
}
else
{
yield return(c);
}
}
}
private void RefreshUIFromDatabase()
{
lbPrimaryKeys.Items.Clear();
lbConflictResolutionColumns.Items.Clear();
foreach (var pkCol in _table.ColumnInfos.Where(col => col.IsPrimaryKey))
{
//primary keys are not used to resolve duplication of primary key values (obviously!)
if (pkCol.DuplicateRecordResolutionOrder != null)
{
//unset any that have accidentally gained an order e.g. if user set an order then made a new column a PK
pkCol.DuplicateRecordResolutionOrder = null;
pkCol.SaveToDatabase();
}
lbPrimaryKeys.Items.Add(pkCol);
}
List <IResolveDuplication> resolvers = new List <IResolveDuplication>();
resolvers.AddRange(_table.ColumnInfos.Where(col => !col.IsPrimaryKey));
resolvers.AddRange(_table.PreLoadDiscardedColumns);
//if there is no order yet
if (resolvers.All(r => r.DuplicateRecordResolutionOrder == null))
{
for (int i = 0; i < resolvers.Count; i++)
{
//set one up
resolvers[i].DuplicateRecordResolutionOrder = i;
resolvers[i].SaveToDatabase();
}
}
foreach (IResolveDuplication resolver in resolvers.OrderBy(o => o.DuplicateRecordResolutionOrder).ToArray())
{
//if it starts with hic_
if (SpecialFieldNames.IsHicPrefixed(resolver))
{
//do not use it for duplication resolution
resolver.DuplicateRecordResolutionOrder = null;
resolver.DuplicateRecordResolutionIsAscending = false; //default to descending
resolver.SaveToDatabase();
resolvers.Remove(resolver);
}
}
foreach (IResolveDuplication resolver in resolvers.OrderBy(c => c.DuplicateRecordResolutionOrder))
{
lbConflictResolutionColumns.Items.Add(resolver);
}
QueryEditor.ReadOnly = false;
try
{
//this is used only to generate the SQL preview of how to resolve primary key collisions so no username/password is required - hence the null,null
PrimaryKeyCollisionResolver resolver = new PrimaryKeyCollisionResolver(_table);
QueryEditor.Text = resolver.GenerateSQL();
CommonFunctionality.ScintillaGoRed(QueryEditor, false);
}
catch (Exception e)
{
CommonFunctionality.ScintillaGoRed(QueryEditor, e);
}
QueryEditor.ReadOnly = true;
}
public void CloneTable(DiscoveredDatabase srcDatabaseInfo, DiscoveredDatabase destDatabaseInfo, DiscoveredTable sourceTable, string destTableName, bool dropHICColumns, bool dropIdentityColumns, bool allowNulls, PreLoadDiscardedColumn[] dilutionColumns)
{
if (!sourceTable.Exists())
{
throw new Exception("Table " + sourceTable + " does not exist on " + srcDatabaseInfo);
}
//new table will start with the same name as the as the old scripted one
DiscoveredTable newTable = destDatabaseInfo.ExpectTable(destTableName);
var sql = sourceTable.ScriptTableCreation(allowNulls, allowNulls, false /*False because we want to drop these columns entirely not just flip to int*/, newTable);
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Creating table with SQL:" + sql));
using (var con = destDatabaseInfo.Server.GetConnection())
{
con.Open();
using (var cmd = destDatabaseInfo.Server.GetCommand(sql, con))
cmd.ExecuteNonQuery();
}
if (!newTable.Exists())
{
throw new Exception("Table '" + newTable + "' not found in " + destDatabaseInfo + " despite running table creation SQL!");
}
foreach (DiscoveredColumn column in newTable.DiscoverColumns())
{
bool drop = false;
var colName = column.GetRuntimeName();
if (column.IsAutoIncrement)
{
drop = true;
}
//drop hic_ columns
if (SpecialFieldNames.IsHicPrefixed(colName) && dropHICColumns)
{
drop = true;
}
//if the ColumnInfo is explicitly marked to be ignored
if (_tableInfo.ColumnInfos.Any(c => c.IgnoreInLoads && c.GetRuntimeName(_copyToBubble.ToLoadStage()).Equals(colName)))
{
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"{colName} will be dropped because it is marked IgnoreInLoads"));
drop = true;
}
//also drop any columns we have specifically been told to ignore in the DLE configuration
if (_hicDatabaseConfiguration.IgnoreColumns != null && _hicDatabaseConfiguration.IgnoreColumns.IsMatch(colName))
{
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"{colName} will be dropped because it is matches the gloabl ignores pattern ({_hicDatabaseConfiguration.IgnoreColumns})"));
drop = true;
}
//drop the data load run ID field and validFrom fields, we don't need them in STAGING or RAW, it will be hard coded in the MERGE migration with a fixed value anyway.
if (colName.Equals(SpecialFieldNames.DataLoadRunID) || colName.Equals(SpecialFieldNames.ValidFrom))
{
drop = true;
}
var dilution = dilutionColumns.SingleOrDefault(c => c.GetRuntimeName().Equals(colName));
if (dilution != null)
{
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"Altering diluted column {colName} to {dilution.Data_type}"));
column.DataType.AlterTypeTo(dilution.Data_type);
}
if (drop)
{
newTable.DropColumn(column);
}
}
}