private string GetParamName(TableColumnMappings colMappings, string publicColName)
{
for (int n = 0; n < colMappings.Count; ++n)
{
if (colMappings[n].PublicColumn.Equals(publicColName, StringComparison.OrdinalIgnoreCase))
{
return(GetParamName(n));
}
}
if (colMappings.EventExpansionRequired)
{
if (colMappings.EventExpansion.PublicEventInstanceColumn.Equals(publicColName, StringComparison.OrdinalIgnoreCase))
{
return(GetParamName(EventInstanceParamNum));
}
if (colMappings.EventExpansion.PublicWeekColumn.Equals(publicColName, StringComparison.OrdinalIgnoreCase))
{
return(GetParamName(EventWeekParamNum));
}
if (colMappings.EventExpansion.PublicWeekOccurrenceColumn.Equals(publicColName, StringComparison.OrdinalIgnoreCase))
{
return(GetParamName(EventWeekOccurrenceParamNum));
}
}
throw new ApplicationException($"Could not find column name in mapping: {publicColName}");
}
private string GetParamNamesAsCsv(TableColumnMappings colMappings, string prefix = null)
{
var sb = new StringBuilder();
for (int n = 0; n < colMappings.Count; ++n)
{
if (n > 0)
{
sb.Append(",");
}
sb.Append(GetParamName(n, prefix));
}
if (colMappings.EventExpansionRequired)
{
if (sb.Length > 0)
{
sb.Append(",");
}
sb.Append(GetParamName(EventInstanceParamNum, prefix));
sb.Append(",");
sb.Append(GetParamName(EventWeekParamNum, prefix));
}
return(sb.ToString());
}
public virtual void Delete(
string sqlConnectionString,
int timeoutSecs,
Row stagingRow,
TableColumnMappings colMappings,
FixupCaches caches,
DataStoreConfiguration configuration)
{
var b = new SqlBuilder();
b.AppendFormat("delete from {0}", QualifiedName);
b.Append("where");
for (int n = 0; n < PrimaryKey.KeyPartsCount; ++n)
{
if (n > 0)
{
b.Append("and");
}
var keyPart = PrimaryKey[n];
b.AppendFormat("{0}={1}", keyPart.ColumnName, GetParamName(colMappings, keyPart.ColumnName));
}
var parameters = new List <SqlParameter>();
for (int n = 0; n < colMappings.Count; ++n)
{
string paramName = GetParamName(n);
colMappings[n].AddParamValue(parameters, paramName, stagingRow, caches, configuration);
}
DatabaseUtils.ExecuteSql(sqlConnectionString, b.ToString(), timeoutSecs, parameters.ToArray());
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings {
EventExpansion = EventExpansionDefinition.Standard
};
m.AddSimpleMapping("timetable_id", "src_timetable_id");
m.AddSimpleMapping("timetable_event_id", "event_id"); // must come before "event_id" mapping
m.AddFederatedIdMapping("event_id");
m.AddEventStartEndTimeMapping("start_time");
m.AddEventStartEndTimeMapping("end_time");
m.AddSpanIdAndNameMapping();
m.AddEventCatIdAndNameMapping(c);
m.AddDeptIdAndNameMapping(c);
m.AddFacultyIdAndNameMapping(c);
m.AddBooleanMapping("global_event");
m.AddBooleanMapping("protected");
m.AddBooleanMapping("suspended");
m.AddBooleanMapping("registers_req");
m.AddBooleanMapping(ColumnConstants.RegistersReqResolvedColumnName);
m.AddAuditMapping(c);
m.AddOriginMapping();
m.AddRemainingSimpleMappings(Columns);
return(m);
}
private void PopulateTempUpsertTableUsingBulkCopy(
SqlConnection c,
int timeoutSecs,
IEnumerable <Row> stagingRows,
TableColumnMappings colMappings,
FixupCaches caches,
DataStoreConfiguration configuration,
Table tableName,
bool modifyWeekNumbers)
{
var etl = new TempUpsertEtlProcess(c, timeoutSecs, stagingRows, colMappings, caches, configuration, tableName, modifyWeekNumbers);
etl.Execute();
var errors = etl.GetAllErrors().ToArray();
if (errors.Any())
{
string msg = $"Errors occurred during population of temporary upsert table: {tableName.Name}";
_log.Error(msg);
// throw the first exception
throw new ApplicationException(msg, errors[0]);
}
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddFederatedIdMapping("exam_id");
m.AddExplicitColumnMappingLookup("federated_exam_id", "exam_id", "name", Entity.EsExam);
m.AddExplicitColumnMappingLookup("federated_exam_id", "exam_id", "unique_name", Entity.EsExam);
m.AddFederatedIdMapping("session_id");
m.AddColumnMappingLookup("session_id", "session_name", Entity.EsSession, c);
m.AddEventCatIdAndNameMapping(c);
m.AddDeptIdAndNameMapping(c);
m.AddFacultyIdAndNameMapping(c);
m.AddBooleanMapping("protected");
m.AddBooleanMapping("suspended");
m.AddBooleanMapping("registers_req");
m.AddBooleanMapping(ColumnConstants.RegistersReqResolvedColumnName);
m.AddAuditMapping(c);
m.AddOriginMapping();
m.AddRemainingSimpleMappings(Columns);
return(m);
}
public TransformationEventExpansionOperation(PublicTable targetTable, FixupCaches caches, DataStoreConfiguration configuration)
{
_colMappings = targetTable.GetColumnMappingsFromStage(configuration);
_expandEvents = _colMappings != null && _colMappings.EventExpansionRequired;
_caches = caches;
_configuration = configuration;
}
private IEnumerable <Row> ExecuteDelete(IEnumerable <Row> rows, TableColumnMappings colMappings)
{
foreach (var r in rows)
{
_targetTable.Delete(_connectionString, _timeoutSecs, r, colMappings, _caches, _configuration);
yield return(r);
}
}
public override void Delete(
string sqlConnectionString,
int timeoutSecs,
Row stagingRow,
TableColumnMappings colMappings,
FixupCaches caches,
DataStoreConfiguration configuration)
{
DeleteEventAssignment(sqlConnectionString, timeoutSecs, stagingRow);
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddFederatedIdMapping("session_id");
m.AddRemainingSimpleMappings(Columns);
return(m);
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddFederatedIdMapping("event_id");
m.AddStaffCatIdAndNameMapping(c);
return(m);
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddGroupIdAndNameMapping(c);
m.AddSubGroupIdAndNameMapping(c);
return(m);
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddFederatedIdMapping("booking_id");
m.AddResourceIdAndNameMapping();
m.AddBooleanMapping("send_email");
return(m);
}
public void Upsert(
string sqlConnectionString,
int timeoutSecs,
IEnumerable <Row> stagingRows,
TableColumnMappings colMappings,
FixupCaches caches,
DataStoreConfiguration configuration,
bool modifyWeekNumbers = false)
{
using (var c = DatabaseUtils.CreateConnection(sqlConnectionString))
{
var tmpTableName = GenerateTempUpsertTable(c, timeoutSecs, stagingRows, colMappings, caches, configuration, modifyWeekNumbers);
var b = new SqlBuilder();
b.AppendFormat("MERGE {0} target", QualifiedName);
b.AppendFormat("using (select {0} from {1}) source", colMappings.GetPublicColNamesAsCsv(), tmpTableName);
b.Append("on (");
for (int n = 0; n < PrimaryKey.KeyPartsCount; ++n)
{
if (n > 0)
{
b.Append("and");
}
var keyPart = PrimaryKey[n];
b.AppendFormat("target.{0} = source.{0}", keyPart.ColumnName);
}
b.Append(")");
b.Append("when MATCHED then");
b.Append("UPDATE SET");
for (int n = 0; n < Columns.Count; ++n)
{
if (n > 0)
{
b.AppendNoSpace(", ");
}
var col = Columns[n];
b.AppendFormat("target.{0} = source.{0}", col.Name);
}
b.Append("when NOT MATCHED then");
b.AppendFormat("INSERT ({0}) values({1})", colMappings.GetPublicColNamesAsCsv(), GetSourceColsNamesAsCsv(colMappings));
b.Append(";");
DatabaseUtils.ExecuteSql(c, null, b.ToString(), timeoutSecs);
}
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddSimpleMapping("timetable_id", "src_timetable_id");
m.AddBooleanMapping("registers_req");
m.AddRemainingSimpleMappings(Columns);
return(m);
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddModuleIdAndNameMapping(c);
m.AddResourceIdAndNameMapping();
m.AddRemainingSimpleMappings(Columns);
return(m);
}
public TempUpsertGetRowsOperation(
IEnumerable <Row> stagingRows,
TableColumnMappings colMappings,
FixupCaches caches,
DataStoreConfiguration configuration,
bool modifyWeekNumbers)
{
_stagingRows = stagingRows;
_colMappings = colMappings;
_caches = caches;
_configuration = configuration;
_modifyWeekNumbers = modifyWeekNumbers;
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddFederatedIdMapping("activity_id");
m.AddStudentIdAndNameMapping(c);
m.AddResourceIdAndNameMapping();
m.AddAuditMapping(c);
return(m);
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddConsolidatedOrFederatedIdMapping(c, Entity.StaffCat, "staff_cat_id");
m.AddAuditMapping(c);
m.AddOriginMapping();
m.AddRemainingSimpleMappings(Columns);
return(m);
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings {
EventExpansion = EventExpansionDefinition.Standard
};
m.AddModuleIdAndNameMapping(c);
m.AddFederatedIdMapping("event_id");
m.AddEventStartEndTimeMapping("start_time");
m.AddEventStartEndTimeMapping("end_time");
return(m);
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
var m = new TableColumnMappings {
SpanExpansionRequired = true
};
m.AddFederatedIdMapping("span_id");
m.AddSimpleMapping("timetable_id", "src_timetable_id");
m.AddSimpleMapping("span_name", "name");
m.AddRemainingSimpleMappings(Columns);
return(m);
}
private void SpecialSpanExpansion(Row[] rows, TableColumnMappings colMappings)
{
var rowsToInsert = new List <Row>();
var spanIds = new List <long>();
foreach (var r in rows)
{
long spanId = (long)r["federated_span_id"];
spanIds.Add(spanId);
string wks = ((string)r["weeks"]).ToUpper();
int timetableId = (int)r["src_timetable_id"];
var weekDates = _caches.WeekDatesCache.Get(timetableId);
int weekNum = 0;
int occurrence = 0;
foreach (var ch in wks)
{
if (ch == 'Y')
{
++occurrence;
var dt = weekDates.StartingDates[weekNum];
for (int day = 0; day < 7; ++day)
{
Row vr = r.Clone();
vr["span_date"] = dt.AddDays(day);
vr["span_week_number"] = weekNum + 1;
vr["span_week_occurrence"] = occurrence;
rowsToInsert.Add(vr);
}
}
++weekNum;
}
}
if (spanIds.Any())
{
// first remove all instances of span...
_targetTable.DeleteSpanInstances(_connectionString, _timeoutSecs, spanIds);
// then insert afresh...
_targetTable.Upsert(_connectionString, _timeoutSecs, rowsToInsert, colMappings, _caches, _configuration);
}
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddExamIdAndNameMapping();
m.AddFederatedIdMapping("event_id");
m.AddRoomIdAndNameMapping(c);
m.AddRoomLayoutIdAndNameMapping(c);
m.AddFederatedIdMapping("slot_id");
m.AddRemainingSimpleMappings(Columns);
return(m);
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddConsolidatedOrFederatedIdMapping(c, Entity.Site, "site_id1");
m.AddColumnMappingLookup("site_id1", "site_name1", ConsolidationType.Site, c);
m.AddConsolidatedOrFederatedIdMapping(c, Entity.Site, "site_id2");
m.AddColumnMappingLookup("site_id2", "site_name2", ConsolidationType.Site, c);
m.AddRemainingSimpleMappings(Columns);
return(m);
}
public TransformationGetDataForBulkInsertOperation(
string adminConnectionString,
Table srcTable,
PublicTable targetTable,
FixupCaches caches,
DataStoreConfiguration configuration,
int srcTimetableId)
: base(DatabaseUtils.CreateConnectionStringSettings(adminConnectionString))
{
_srcTable = srcTable;
_targetTable = targetTable;
_caches = caches;
_configuration = configuration;
_colMappings = targetTable.GetColumnMappingsFromStage(_configuration);
_srcTimetableId = srcTimetableId;
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddConsolidatedOrFederatedIdMapping(c, Entity.EventCat, "event_cat_id");
m.AddColumnMappingLookup("event_cat_id", "name", ConsolidationType.EventCat, c);
m.AddBooleanMapping("registers_req");
m.AddBooleanMapping(ColumnConstants.RegistersReqResolvedColumnName);
m.AddAuditMapping(c);
m.AddOriginMapping();
m.AddRemainingSimpleMappings(Columns);
return(m);
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddFederatedIdMapping("booking_id");
m.AddUserIdAndNameMapping(c);
m.AddDeptIdAndNameMapping(c);
m.AddEventCatIdAndNameMapping(c);
m.AddBooleanMapping("add_me");
m.AddAuditMapping(c);
m.AddOriginMapping();
m.AddRemainingSimpleMappings(Columns);
return(m);
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddStudentIdAndNameMapping(c);
m.AddEventInstanceMapping();
m.AddFederatedIdMapping("event_id");
m.AddSimpleMapping("timetable_week", "week");
m.AddMarkIdAndNameMapping();
m.AddAuditMapping(c);
m.AddRemainingSimpleMappings(Columns);
return(m);
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddConsolidatedOrFederatedIdMapping(c, Entity.Supervisor, "supervisor_id");
m.AddBooleanMapping("can_send_sms");
m.AddBooleanMapping("can_send_email");
m.AddAuditMapping(c);
m.AddOriginMapping();
m.AddRemainingSimpleMappings(Columns);
return(m);
}
public override TableColumnMappings GetColumnMappingsFromStage(DataStoreConfiguration c)
{
// establish the column mappings between the staging tables and this public table
var m = new TableColumnMappings();
m.AddFederatedIdMapping("mark_id");
m.AddDescriptionMapping();
m.AddMarkDefinition();
m.AddOriginMapping();
m.AddBooleanMapping("precedence");
m.AddBooleanMapping("card");
m.AddBooleanMapping("send_notification");
m.AddRemainingSimpleMappings(Columns);
return(m);
}