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