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); }
public static string GetIdFldName(ConsolidationType ctype) { var e = ToEntity(ctype); if (e != Entity.Unknown) { EntityUtils.GetIdFldName(e); } return(null); }
private void PopulateFromPublicTable(Entity et, ResourceNameCache cache) { var idFldName = EntityUtils.GetIdFldName(et); var stagingTableName = EntityUtils.ToCtTableName(et); var mapping = _mappings.FirstOrDefault( x => x.PublicStagingTable.Name.Equals(stagingTableName, StringComparison.OrdinalIgnoreCase)); if (mapping == null) { throw new ApplicationException($"Could not map staging table ({stagingTableName}) to public table"); } var tableName = DatabaseUtils.GetQualifiedTableName(mapping.PublicTable.SchemaName, mapping.PublicTable.Name); PopulateFromTable(et, cache, tableName, idFldName, false); }
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(); } } }