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);
}
private void UpdateConsolidationDetailTables(Entity entity, Table stagingTable, ConsolidationEntry consolidationConfig)
{
string qualifiedConsolidationDetailTableName = GetQualifiedTableName(EntityUtils.ToConsolidationDetailTableName(entity));
int numChanges;
if (consolidationConfig.None)
{
var sql = $"delete from {qualifiedConsolidationDetailTableName}";
numChanges = DatabaseUtils.ExecuteSql(ConnectionString, sql, TimeoutSecs);
}
else
{
string qualifiedStagingTableName =
DatabaseUtils.GetQualifiedTableName(StagingSchema.PrimaryStagingSchemaName, stagingTable.Name);
string qualifiedConsolidationTableName = GetQualifiedTableName(EntityUtils.ToConsolidationTableName(entity));
string qualifiedFederationTableName = GetQualifiedTableName(EntityUtils.ToFederationTableName(entity));
string entityIdColumnName = stagingTable.Columns[1].Name;
// e.g.
// MERGE FEDERATION.CONSOLIDATION_DETAIL.DEPT cd
// USING (select c.consolidation_id, f.ctds_id
// from STAGEA.DEPT r
// inner join FEDERATION.CONSOLIDATION_DEPT c
// on c.natural_key = r.name
// inner join FEDERATION.DEPT f
// on f.src_timetable_id = r.src_timetable_id
// and f.item_id = r.dept_id) tmpTable
// on cd.consolidation_id = tmpTable.consolidation_id and cd.ctds_id = tmpTable.ctds_id
// when NOT MATCHED BY TARGET THEN
// insert (consolidation_id, ctds_id)
// values (tmpTable.consolidation_id, tmpTable.ctds_id)
// when NOT MATCHED BY SOURCE THEN
// DELETE;
var sql = new SqlBuilder();
sql.AppendFormat("MERGE {0} cd", qualifiedConsolidationDetailTableName);
sql.AppendFormat("USING (select c.{0}, f.{1}", ConsolidationIdColName, MasterIdColName);
sql.AppendFormat("from {0} r", qualifiedStagingTableName);
sql.AppendFormat("inner join {0} c", qualifiedConsolidationTableName);
sql.AppendFormat("on c.{0} = r.{1}", NaturalKeyColName, consolidationConfig.Column);
sql.AppendFormat("inner join {0} f", qualifiedFederationTableName);
sql.AppendFormat("on f.{0} = r.{0}", ColumnConstants.SrcTimetableIdColumnName);
sql.AppendFormat("and f.{0} = r.{1}) tmpTable", ItemIdCol, entityIdColumnName);
sql.AppendFormat("on cd.{0} = tmpTable.{0} and cd.{1} = tmpTable.{1}", ConsolidationIdColName, MasterIdColName);
sql.Append("when NOT MATCHED BY TARGET THEN");
sql.AppendFormat("insert ({0}, {1})", ConsolidationIdColName, MasterIdColName);
sql.AppendFormat("values (tmpTable.{0}, tmpTable.{1})", ConsolidationIdColName, MasterIdColName);
sql.Append("when NOT MATCHED BY SOURCE THEN");
sql.Append("DELETE;");
numChanges = DatabaseUtils.ExecuteSql(ConnectionString, sql.ToString(), TimeoutSecs);
}
_log.DebugFormat("{0} changes to consolidation detail table {1}", numChanges, qualifiedConsolidationDetailTableName);
}
public void UpdateConsolidationTables(Entity entity, Table stagingTable, ConsolidationEntry consolidationConfig)
{
UpdateConsolidationMasterTables(entity, stagingTable, consolidationConfig);
UpdateConsolidationDetailTables(entity, stagingTable, consolidationConfig);
}
