public override object GetStagingValue(Row stagingRow, FixupCaches caches, DataStoreConfiguration config)
{
Ct7Entity resType = (Ct7Entity)stagingRow[StagingTypeColumn];
Entity entityType = EntityUtils.FromCt7Entity(resType);
object id = config.Consolidation.Get(entityType).None
? stagingRow[FederatedStagingIdColumn]
: stagingRow[ConsolidatedStagingIdColumn];
if (id == null || id == DBNull.Value)
{
return(DBNull.Value);
}
long idVal = (long)id;
var name = caches.NameCache.Get(entityType, idVal, config);
if (name != null)
{
if (PublicColumn.Contains("unique"))
{
return(name.UniqueName);
}
if (string.IsNullOrEmpty(name.Name))
{
return(DBNull.Value);
}
return(name.Name);
}
return(DBNull.Value);
}
public override object GetStagingValue(Row stagingRow, FixupCaches caches, DataStoreConfiguration config)
{
Ct7Entity resType = (Ct7Entity)stagingRow[PublicStagingTypeColumn];
Entity entityType = EntityUtils.FromCt7Entity(resType);
return(config.Consolidation.Get(entityType).None
? stagingRow[FederatedStagingColumn]
: stagingRow[ConsolidatedStagingColumn]);
}
private IEnumerable <Ct7Entity> GetEntityTypedUsed(TransactionContext tc, string tableName, string entityDefColName)
{
var result = new List <Ct7Entity>();
var sql =
$"select distinct {entityDefColName} from {DatabaseUtils.GetQualifiedTableName(HistorySchema.HistorySchemaName, tableName)}";
DatabaseUtils.EnumerateResults(tc, sql, _timeoutSecs, rec =>
{
var val = (Ct7Entity)rec[entityDefColName];
if (EntityUtils.FromCt7Entity(val) != Entity.Unknown)
{
result.Add(val);
}
});
return(result);
}
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);
}
private int AddFederatedIdValues(TransactionContext tc, V7StagingTable table)
{
_log.DebugFormat("Adding federated Id values to history table: {0}", table.Name);
var numChanges = 0;
foreach (var c in table.FederatedIdCols)
{
Entity entity = c.Entity;
var origColumnName = c.OriginalColName;
var fedColumnName = c.FederationIdColName;
if (entity == Entity.Unknown)
{
// 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.federated_resource_id = f.ctds_id
// from HISTORY.CT_AT_AUX_MARK h
// inner join
// FEDERATION.ROOM f
// on h.resource_id = f.item_id and h.src_timetable_id = f.src_timetable_id
// and h.resource_type = 604
// and h.history_federated = 0
// and h.history_status in ('I', 'U')
var et = EntityUtils.FromCt7Entity(ct7EType);
var sb = new SqlBuilder();
sb.Append("update h");
sb.AppendFormat("set h.{0} = f.{1}", fedColumnName, FederationSchema.MasterIdColName);
sb.AppendFormat(
"from {0} h", DatabaseUtils.GetQualifiedTableName(HistorySchema.HistorySchemaName, table.Name));
sb.Append("inner join");
string qualifiedFedTable = DatabaseUtils.GetQualifiedTableName(
FederationSchema.FederationSchemaName, EntityUtils.ToFederationTableName(et));
sb.AppendFormat("{0} f", qualifiedFedTable);
sb.AppendFormat(
"on h.{0} = f.{1} and h.{2} = f.{2}",
origColumnName,
FederationSchema.ItemIdCol,
ColumnConstants.SrcTimetableIdColumnName);
sb.AppendFormat("and h.{0} = {1}", entityDefCol, (int)ct7EType);
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);
}
}
else
{
// e.g.
// update h
// set h.federated_dept_id = f.ctds_id
// from HISTORY.CT_ROOM h
// inner join
// FEDERATION.DEPT f
// on h.dept_id = f.item_id and h.src_timetable_id = f.src_timetable_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} = f.{1}", fedColumnName, FederationSchema.MasterIdColName);
sb.AppendFormat(
"from {0} h", DatabaseUtils.GetQualifiedTableName(HistorySchema.HistorySchemaName, table.Name));
sb.Append("inner join");
string qualifiedFedTable = DatabaseUtils.GetQualifiedTableName(
FederationSchema.FederationSchemaName, EntityUtils.ToFederationTableName(entity));
sb.AppendFormat("{0} f", qualifiedFedTable);
sb.AppendFormat(
"on h.{0} = f.{1} and h.{2} = f.{2}",
origColumnName,
FederationSchema.ItemIdCol,
ColumnConstants.SrcTimetableIdColumnName);
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);
}
