private ConsolidationParams ReadConsolidationConfig()
{
var result = new ConsolidationParams();
var sql = $"select entity_type, column_name from {GetQualifiedTableName(ConsolidationConfig)}";
DatabaseUtils.EnumerateResults(ConnectionString, sql, TimeoutSecs, r =>
{
var e = EntityUtils.FromString((string)r["entity_type"]);
if (EntityUtils.CanParticipateInConsolidation(e))
{
string colName = (string)DatabaseUtils.SafeRead(r, "column_name", string.Empty);
if (!string.IsNullOrEmpty(colName))
{
var entry = new ConsolidationEntry
{
Entity = e.ToString(),
Column = colName
};
result.Entries.Add(entry);
}
}
});
result.Enabled = result.Entries.Count > 0;
return(result);
}
public ConsolidationTablesBuilder()
{
foreach (Entity e in Enum.GetValues(typeof(Entity)))
{
if (EntityUtils.CanParticipateInConsolidation(e))
{
string masterTableName = EntityUtils.ToConsolidationTableName(e);
AddTable(new ConsolidationTable(masterTableName, PublicStagingSchema.StagingSchemaName));
AddTable(new ConsolidationDetailTable(
EntityUtils.ToConsolidationDetailTableName(e),
masterTableName,
PublicStagingSchema.StagingSchemaName,
false));
}
}
}
private static void DoParallelProcessing(
string connectionString,
int timeoutSecs,
int maxDegreeOfParallelism,
ConsolidationParams consolidationParams,
string stagingSchemaName)
{
List <Entity> entities = Enum.GetValues(typeof(Entity)).Cast <object>().Cast <Entity>().ToList();
var pOptions = new ParallelOptions {
MaxDegreeOfParallelism = maxDegreeOfParallelism
};
Parallel.ForEach(entities, pOptions, (e, loopState) =>
{
if (!loopState.IsExceptional && EntityUtils.CanParticipateInConsolidation(e))
{
var entry = consolidationParams.Get(e);
if (!entry.None)
{
var qualifiedStagingTableName =
DatabaseUtils.GetQualifiedTableName(stagingSchemaName, EntityUtils.ToCtTableName(e));
var naturalKey = entry.Column;
var sql =
$"select count(1) from {qualifiedStagingTableName} where {naturalKey} is null or RTRIM(LTRIM({naturalKey})) = ''";
var count = Convert.ToInt32(DatabaseUtils.ExecuteScalar(connectionString, sql, timeoutSecs));
if (count > 0)
{
loopState.Stop();
var msg =
$"There are {count} rows in {qualifiedStagingTableName} with empty consolidation values (column='{naturalKey}')";
_log.Error(msg);
throw new ApplicationException(msg);
}
}
}
});
}
private void DoParallelConsolidationProcessing(FederationSchema fs)
{
List <Entity> entities = Enum.GetValues(typeof(Entity)).Cast <object>().Cast <Entity>().ToList();
var b = StagingTablesBuilder.Get(StagingSchema.PrimaryStagingSchemaName);
var pOptions = new ParallelOptions {
MaxDegreeOfParallelism = _configuration.MaxDegreeOfParallelism
};
Parallel.ForEach(entities, pOptions, (e, loopState) =>
{
if (!loopState.IsExceptional && EntityUtils.CanParticipateInConsolidation(e))
{
var entry = _configuration.Consolidation.Get(e);
fs.UpdateConsolidationTables(e, b.GetTable(EntityUtils.ToCtTableName(e)), entry);
}
});
}
public ConsolidationTablesBuilder()
{
// create an instance of each table and add to the table builder...
AddTable(new ConsolidationConfigTable());
foreach (Entity e in Enum.GetValues(typeof(Entity)))
{
if (EntityUtils.CanParticipateInConsolidation(e))
{
var masterTableName = EntityUtils.ToConsolidationTableName(e);
AddTable(new ConsolidationTable(masterTableName, FederationSchema.FederationSchemaName));
AddTable(new ConsolidationDetailTable(
EntityUtils.ToConsolidationDetailTableName(e),
masterTableName,
FederationSchema.FederationSchemaName));
}
}
}
/// <summary>
/// Checks that the column names specified in the configuration, consolidation
/// section are valid for the given entity
/// </summary>
public static void CheckNaturalKeyColumnsInConfiguration(ConsolidationParams consolidationParams)
{
_log.Debug("Checking that consolidation key columns are valid in configuration");
StagingTablesBuilder b = StagingTablesBuilder.Get(StagingSchema.PrimaryStagingSchemaName);
if (consolidationParams.Enabled)
{
var entitiesUsed = new List <Entity>();
foreach (Entity entity in Enum.GetValues(typeof(Entity)))
{
if (EntityUtils.CanParticipateInConsolidation(entity))
{
if (entitiesUsed.Contains(entity))
{
throw new ApplicationException(string.Format("Entity declared more than once in consolidation configuration: {0}", entity));
}
entitiesUsed.Add(entity);
var entry = consolidationParams.Get(entity);
if (entry != null && !entry.None)
{
string stagingTableName = EntityUtils.ToCtTableName(entity);
var table = b.GetTable(stagingTableName);
if (!table.ColumnExists(entry.Column))
{
throw new ApplicationException(string.Format("The specified consolidation column ({0}) does not exist in the entity: {1}", entry.Column, entity));
}
}
}
}
}
}
private void CheckConfigConsolidation()
{
foreach (var entry in _configuration.Consolidation.Entries)
{
try
{
var e = EntityUtils.FromString(entry.Entity);
if (!EntityUtils.CanParticipateInConsolidation(e))
{
throw new ApplicationException(string.Format("Entity in configuration, consolidation section does not participate in consolidation: {0}", entry.Entity));
}
if (!entry.None && !EntityUtils.GetValidConsolidationColumns(e).Contains(entry.Column))
{
throw new ApplicationException(string.Format("Entity in configuration, consolidation section does not have specified column: {0} - {1}", entry.Entity, entry.Column));
}
}
catch (ArgumentException)
{
throw new ApplicationException(string.Format("Could not identify entity in configuration, consolidation section: {0}", entry.Entity));
}
}
}
private void WriteConsolidationConfig(ConsolidationParams c)
{
var qualifiedConfigTable = GetQualifiedTableName(ConsolidationConfig);
var sql = $"delete from {qualifiedConfigTable}";
DatabaseUtils.ExecuteSql(ConnectionString, sql, TimeoutSecs);
if (c.Enabled)
{
foreach (var entry in c.Entries)
{
var e = EntityUtils.FromString(entry.Entity);
if (EntityUtils.CanParticipateInConsolidation(e))
{
sql = $"insert into {qualifiedConfigTable} (entity_type, column_name) values (@E, @C)";
SqlParameter[] p = { new SqlParameter("@E", entry.Entity), new SqlParameter("@C", entry.Column) };
DatabaseUtils.ExecuteSql(ConnectionString, sql, TimeoutSecs, p);
}
}
}
}
private void DeleteExtraneousStagingRows()
{
// delete extraneous consolidated rows from staging (in cases of consolidated items we want a max of 1 row for each)
foreach (Entity et in Enum.GetValues(typeof(Entity)))
{
if (et != Entity.Unknown && EntityUtils.CanParticipateInConsolidation(et))
{
string stagingTableName =
DatabaseUtils.GetQualifiedTableName(PublicStagingSchema.StagingSchemaName, EntityUtils.ToCtTableName(et));
string idFldName = EntityUtils.GetIdFldName(et);
string consolidatedIdFldName = ConsolidationTypeUtils.GetConsolidatedFieldName(idFldName);
var bd = new BatchDelete(_connectionString, _timeoutSecs, stagingTableName, PublicStagingSchema.StagingId);
// e.g.
// select s.staging_id, s.consolidated_dept_id
// from STAGING.CT_DEPT s
// inner join
// (
// select consolidated_dept_id, count(1)
// from STAGING.CT_DEPT
// where history_status in ('I', 'U')
// group by consolidated_dept_id
// having count(1) > 1
// ) sc on s.consolidated_dept_id = sc.consolidated_dept_id
// where history_status in ('I', 'U')
// order by date_change desc
SqlBuilder sql = new SqlBuilder();
sql.AppendFormat("select s.{0}, s.{1}", PublicStagingSchema.StagingId, consolidatedIdFldName);
sql.AppendFormat("from {0} s", stagingTableName);
sql.Append("inner join");
sql.Append("(");
sql.AppendFormat("select {0}", consolidatedIdFldName);
sql.AppendFormat("from {0} sc", stagingTableName);
sql.AppendFormat(
"where {0} in ('{1}', '{2}')",
HistorySchema.HistoryStatusColumnName,
HistorySchema.HistoryStatusInsert,
HistorySchema.HistoryStatusUpdate);
sql.AppendFormat("group by {0}", consolidatedIdFldName);
sql.Append("having count(1) > 1");
sql.AppendFormat(") sc on s.{0} = sc.{0}", consolidatedIdFldName);
sql.AppendFormat(
"where {0} in ('{1}', '{2}')",
HistorySchema.HistoryStatusColumnName,
HistorySchema.HistoryStatusInsert,
HistorySchema.HistoryStatusUpdate);
sql.AppendFormat("order by date_change desc");
var consolidatedIds = new HashSet <long>();
DatabaseUtils.EnumerateResults(_connectionString, sql.ToString(), _timeoutSecs, r =>
{
long cId = (long)DatabaseUtils.SafeRead(r, consolidatedIdFldName, 0);
int stagingId = (int)r[PublicStagingSchema.StagingId];
if (cId == 0)
{
throw new ApplicationException("Unexpected consolidated Id");
}
if (consolidatedIds.Contains(cId))
{
bd.Add(stagingId);
}
else
{
consolidatedIds.Add(cId);
}
});
bd.Execute();
}
}
}
