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); }