private void PopulateFromStagingTable(Entity et, ResourceNameCache cache, DataStoreConfiguration c)
{
var idFldName = c.Consolidation.Get(et).None
? EntityUtils.GetFederatedFieldName(EntityUtils.GetIdFldName(et))
: ConsolidationTypeUtils.GetConsolidatedFieldName(EntityUtils.GetIdFldName(et));
var tableName = DatabaseUtils.GetQualifiedTableName(
PublicStagingSchema.StagingSchemaName, EntityUtils.ToCtTableName(et));
PopulateFromTable(et, cache, tableName, idFldName, true);
}
private void RegisterConsolidatedIdCol(string colName, ConsolidationType ctype, string entityDefColName = null)
{
if (!ColumnExists(colName))
{
throw new ApplicationException(string.Format("Column does not exist: {0}", colName));
}
var def = new ConsolidationDefinition
{
ConsolidationType = ctype,
OriginalColName = colName,
ConsolidationIdColName = ConsolidationTypeUtils.GetConsolidatedFieldName(colName),
EntityDefinitionColName = entityDefColName
};
_consolidatedIdCols.Add(def);
}
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();
}
}
}