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 bool RegisterConsolidatedIdCol(string colName)
{
var ctype = ConsolidationTypeUtils.GetConsolidationTypeFromIdFldName(colName);
if (ctype != ConsolidationType.None)
{
RegisterConsolidatedIdCol(colName, ctype);
return(true);
}
return(false);
}
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 object LookupName(ConsolidationType lookupType, string publicIdColumn, long lookupResId)
{
object rv = DBNull.Value;
var rn = _caches.NameCache.Get(ConsolidationTypeUtils.ToEntity(lookupType), lookupResId, _configuration);
if (publicIdColumn.Contains("unique"))
{
if (!string.IsNullOrEmpty(rn.UniqueName))
{
rv = rn.UniqueName;
}
}
else if (!string.IsNullOrEmpty(rn.Name))
{
rv = rn.Name;
}
return(rv);
}
public ResourceName Get(ConsolidationType cType, long id, DataStoreConfiguration c)
{
var et = ConsolidationTypeUtils.ToEntity(cType);
return(Get(et, id, c));
}
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();
}
}
}
public void AddExplicitColumnMappingLookup(string stagingIdColumn, string publicIdColumn, string publicNameColumn, ConsolidationType cType)
{
AddExplicitColumnMappingLookup(stagingIdColumn, publicIdColumn, publicNameColumn, ConsolidationTypeUtils.ToEntity(cType));
}
public void AddColumnMappingLookup(string publicIdColumn, string publicNameColumn, ConsolidationType cType, DataStoreConfiguration c)
{
var entityType = ConsolidationTypeUtils.ToEntity(cType);
AddColumnMappingLookup(publicIdColumn, publicNameColumn, entityType, c);
}
private int AddConsolidatedIdValues(TransactionContext tc, V7StagingTable table)
{
_log.DebugFormat("Adding consolidated Id values to history table: {0}", table.Name);
var numChanges = 0;
foreach (var c in table.ConsolidatedIdCols)
{
var ctype = c.ConsolidationType;
var consolidatedColumnName = c.ConsolidationIdColName;
var federatedColumnName = FindFederatedColumnName(c, table);
if (ctype == ConsolidationType.None)
{
// this is a generic column such as "resource_id" where we don't know
// the entity type without reference to the value stored in its corresponding
// 'type' column (e.g. "resource_type")...
var entityDefCol = c.EntityDefinitionColName;
var entityTypesUsed = GetEntityTypedUsed(tc, table.Name, entityDefCol);
foreach (var ct7EType in entityTypesUsed)
{
// e.g.
// update h
// set h.consolidated_resource_id = cd.consolidation_id
// from HISTORY.CT_AT_AUX_MARK h
// inner join
// FEDERATION.CONSOLIDATION_DETAIL_ROOM cd
// on h.federated_resource_id = cd.ctds_id
// and h.history_federated = 0
// and h.resource_type = 604
// and h.history_status in ('I', 'U')
var sb = new SqlBuilder();
sb.Append("update h");
sb.AppendFormat("set h.{0} = cd.{1}", consolidatedColumnName, FederationSchema.ConsolidationIdColName);
sb.AppendFormat(
"from {0} h", DatabaseUtils.GetQualifiedTableName(HistorySchema.HistorySchemaName, table.Name));
sb.Append("inner join");
string qualifiedConsolidationDetailTable =
DatabaseUtils.GetQualifiedTableName(
FederationSchema.FederationSchemaName, EntityUtils.ToConsolidationDetailTableName(EntityUtils.FromCt7Entity(ct7EType)));
sb.AppendFormat("{0} cd", qualifiedConsolidationDetailTable);
sb.AppendFormat("on h.{0} = cd.{1}", federatedColumnName, FederationSchema.MasterIdColName);
sb.AppendFormat("and h.{0} = 0", HistorySchema.HistoryFederatedColumnName);
sb.AppendFormat("and h.{0} = {1}", entityDefCol, (int)ct7EType);
sb.AppendFormat("and h.{0} in ({1})", HistorySchema.HistoryStatusColumnName, RestrictedStatusValuesAsCsv());
numChanges += DatabaseUtils.ExecuteSql(tc, sb.ToString(), _timeoutSecs);
}
}
else
{
// e.g.
// update h
// set h.consolidated_dept_id = cd.consolidation_id
// from HISTORY.CT_ROOM h
// inner join
// FEDERATION.CONSOLIDATION_DETAIL_DEPT cd
// on h.federated_dept_id = cd.ctds_id
// and h.history_federated = 0
// and h.history_status in ('I', 'U')
var sb = new SqlBuilder();
sb.Append("update h");
sb.AppendFormat("set h.{0} = cd.{1}", consolidatedColumnName, FederationSchema.ConsolidationIdColName);
sb.AppendFormat(
"from {0} h", DatabaseUtils.GetQualifiedTableName(HistorySchema.HistorySchemaName, table.Name));
sb.Append("inner join");
var qualifiedConsolidationDetailTable =
DatabaseUtils.GetQualifiedTableName(
FederationSchema.FederationSchemaName,
EntityUtils.ToConsolidationDetailTableName(ConsolidationTypeUtils.ToEntity(ctype)));
sb.AppendFormat("{0} cd", qualifiedConsolidationDetailTable);
sb.AppendFormat("on h.{0} = cd.{1}", federatedColumnName, FederationSchema.MasterIdColName);
sb.AppendFormat("and h.{0} = 0", HistorySchema.HistoryFederatedColumnName);
sb.AppendFormat("and h.{0} in ({1})", HistorySchema.HistoryStatusColumnName, RestrictedStatusValuesAsCsv());
numChanges += DatabaseUtils.ExecuteSql(tc, sb.ToString(), _timeoutSecs);
}
}
return(numChanges);
}