private IEnumerable <long> GetSourceTimetableIds() { var results = new List <long>(); var sql = $"select distinct src_timetable_id from {DatabaseUtils.GetQualifiedTableName(PublicStagingSchema.StagingSchemaName, "CT_CONFIG")}"; DatabaseUtils.EnumerateResults(PublicConnectionString, sql, Timeouts.PublicDatabase, r => { int id = (int)r["src_timetable_id"]; if (!results.Contains(id)) { results.Add(id); } }); sql = $"select distinct timetable_id from {DatabaseUtils.GetQualifiedTableName(MiscSchema.MiscSchemaName, "TIMETABLE_CONFIG")}"; DatabaseUtils.EnumerateResults(PublicConnectionString, sql, Timeouts.PublicDatabase, r => { var id = (long)r["timetable_id"]; if (!results.Contains(id)) { results.Add(id); } }); return(results); }
private ConsolidationParams ReadConsolidationConfig() { var result = new ConsolidationParams(); var sql = $"select entity_type, column_name from {GetQualifiedTableName(ConsolidationConfig)}"; DatabaseUtils.EnumerateResults(ConnectionString, sql, TimeoutSecs, r => { var e = EntityUtils.FromString((string)r["entity_type"]); if (EntityUtils.CanParticipateInConsolidation(e)) { string colName = (string)DatabaseUtils.SafeRead(r, "column_name", string.Empty); if (!string.IsNullOrEmpty(colName)) { var entry = new ConsolidationEntry { Entity = e.ToString(), Column = colName }; result.Entries.Add(entry); } } }); result.Enabled = result.Entries.Count > 0; return(result); }
private void PopulateFromStagingTable() { var stagingTableName = DatabaseUtils.GetQualifiedTableName( PublicStagingSchema.StagingSchemaName, EntityUtils.ToCtTableName(Entity.Event)); string federatedEventIdCol = EntityUtils.GetFederatedFieldName("event_id"); var s = new SqlBuilder(); s.AppendFormat("select {0}, day_of_week, start_time, end_time from {1}", federatedEventIdCol, stagingTableName); s.AppendFormat( "where {0} in ('{1}', '{2}')", HistorySchema.HistoryStatusColumnName, HistorySchema.HistoryStatusInsert, HistorySchema.HistoryStatusUpdate); DatabaseUtils.EnumerateResults(_connectionString, s.ToString(), _timeoutSecs, r => { var times = new EventStartEndTime { Start = (DateTime)r["start_time"], End = (DateTime)r["end_time"], DayOfWeek = Utils.ConvertFromCt7DayOfWeek((int)r["day_of_week"]) }; var eventId = (long)r[federatedEventIdCol]; _eventTimes.Add(eventId, times); }); }
private void PopulateFromPublicTable() { string publicEventTable = DatabaseUtils.GetQualifiedTableName(EventSchema.EventSchemaName, EntityUtils.ToFederationTableName(Entity.Event)); var s = new SqlBuilder(); s.AppendFormat("select event_id, day_of_week, start_time, end_time from {0}", publicEventTable); DatabaseUtils.EnumerateResults(_connectionString, s.ToString(), _timeoutSecs, r => { var eventId = (long)r["event_id"]; if (!_eventTimes.ContainsKey(eventId)) { var times = new EventStartEndTime { Start = (DateTime)r["start_time"], End = (DateTime)r["end_time"], DayOfWeek = Utils.ConvertFromCt7DayOfWeek((int)r["day_of_week"]) }; _eventTimes.Add(eventId, times); } }); }
public IReadOnlyList <SourceTimetableRecord> GetSourceTimetableRecords() { var sb = new SqlBuilder(); sb.Append("select src_timetable_id, timetable_name, server_name, database_name, schema_version, guid"); sb.AppendFormat("from {0}", GetQualifiedTableName(SrcTimetableName)); var result = new List <SourceTimetableRecord>(); DatabaseUtils.EnumerateResults(ConnectionString, sb.ToString(), TimeoutSecs, r => { var rec = new SourceTimetableRecord { Name = (string)r["timetable_name"], SqlServerName = (string)r["server_name"], DatabaseName = (string)r["database_name"], SchemaVersion = (int)r["schema_version"], Identifier = (Guid)r["guid"] }; result.Add(rec); }); return(result); }
private IReadOnlyCollection <int> GetResourceTypesInTable(string resTypeColName, string qualifiedTableName) { var results = new List <int>(); string sql = $"select distinct {resTypeColName} from {qualifiedTableName}"; DatabaseUtils.EnumerateResults(_connectionString, sql, _timeoutSecs, r => { var o = r[resTypeColName]; if (o != DBNull.Value) { results.Add((int)o); } }); return(results); }
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 void PopulateFromTable( Entity et, ResourceNameCache cache, string qualifiedTableName, string idFldName, bool checkHistoryCols) { var hasUniqueNameCol = EntityUtils.HasUniqueNameColumn(et); var s = new SqlBuilder(); s.Append(hasUniqueNameCol ? "select name, unique_name" : "select name"); s.AppendFormat(", {0}", idFldName); s.AppendFormat("from {0}", qualifiedTableName); if (checkHistoryCols) { s.AppendFormat( "where {0} in ('{1}', '{2}')", HistorySchema.HistoryStatusColumnName, HistorySchema.HistoryStatusInsert, HistorySchema.HistoryStatusUpdate); } DatabaseUtils.EnumerateResults(_connectionString, s.ToString(), _timeoutSecs, r => { var id = (long)DatabaseUtils.SafeRead(r, idFldName, 0L); if (id > 0) { var name = (string)DatabaseUtils.SafeRead(r, "name", null); string uname = null; if (hasUniqueNameCol) { uname = (string)DatabaseUtils.SafeRead(r, "unique_name", null); } cache.Add(id, name, uname); } }); }
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(); } } }