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)
{
object id = stagingRow[StagingIdColumn];
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"))
{
if (string.IsNullOrEmpty(name.UniqueName))
{
throw new ApplicationException("Unique name is empty!");
}
return(name.UniqueName);
}
if (string.IsNullOrEmpty(name.Name))
{
return(DBNull.Value);
}
return(name.Name);
}
return(DBNull.Value);
}
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 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 void AddParamValue(
List <SqlParameter> parameters, string paramName, Row stagingRow, FixupCaches caches, DataStoreConfiguration config)
{
var val = GetStagingValue(stagingRow, caches, config);
parameters.Add(new SqlParameter(paramName, val));
}
public TransformationEventExpansionOperation(PublicTable targetTable, FixupCaches caches, DataStoreConfiguration configuration)
{
_colMappings = targetTable.GetColumnMappingsFromStage(configuration);
_expandEvents = _colMappings != null && _colMappings.EventExpansionRequired;
_caches = caches;
_configuration = configuration;
}
public override object GetStagingValue(Row stagingRow, FixupCaches caches, DataStoreConfiguration config)
{
long eventId = (long)stagingRow[StagingEventIdColumn];
int week = (int)stagingRow[StagingEventWeekColumn];
return(FabricateEventInstanceId(eventId, week));
}
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]);
}
public override void Delete(
string sqlConnectionString,
int timeoutSecs,
Row stagingRow,
TableColumnMappings colMappings,
FixupCaches caches,
DataStoreConfiguration configuration)
{
DeleteEventAssignment(sqlConnectionString, timeoutSecs, stagingRow);
}
public override object GetStagingValue(Row stagingRow, FixupCaches caches, DataStoreConfiguration config)
{
var val = stagingRow[StagingColumn];
if (val != null && val != DBNull.Value)
{
return(GetHardCodedValue((string)val));
}
return(DBNull.Value);
}
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 object GetStagingValue(Row stagingRow, FixupCaches caches, DataStoreConfiguration config)
{
object val = stagingRow[StagingColumn];
if (val == null || val == DBNull.Value)
{
return(DBNull.Value);
}
return(((string)val).Equals("Y", StringComparison.OrdinalIgnoreCase)
? 1 : 0);
}
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 void AddParamValue(
List <SqlParameter> parameters, string paramName, Row stagingRow, FixupCaches caches, DataStoreConfiguration config)
{
object id = GetStagingValue(stagingRow, caches, config);
if (id == null || id == DBNull.Value)
{
parameters.Add(new SqlParameter(paramName, DBNull.Value));
}
else
{
parameters.Add(new SqlParameter(paramName, id));
}
}
public TransformationUpdateTargetOperation(
string connectionString,
int timeoutSecs,
PublicTable targetTable,
FixupCaches caches,
DataStoreConfiguration configuration,
TransformationType transformationType)
{
_connectionString = connectionString;
_timeoutSecs = timeoutSecs;
_targetTable = targetTable;
_caches = caches;
_configuration = configuration;
_transformationType = transformationType;
}
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;
}
private void DoParallelProcessing(TableMappings.TableMappings tableMappings)
{
_caches = new FixupCaches(_connectionString, _timeoutSecs, tableMappings);
var pOptions = new ParallelOptions {
MaxDegreeOfParallelism = _maxDegreeOfParallelism
};
Parallel.ForEach(tableMappings, pOptions, (tableMapping, loopState) =>
{
if (!loopState.IsExceptional)
{
TransformStagingTable(tableMapping, loopState);
}
});
}
private string GenerateTempUpsertTable(
SqlConnection c,
int timeoutSecs,
IEnumerable <Row> stagingRows,
TableColumnMappings colMappings,
FixupCaches caches,
DataStoreConfiguration configuration,
bool modifyWeekNumbers)
{
var tableName = string.Concat("#TmpUpsert", Name);
var b = new TempUpsertTableBuilder(tableName, this);
b.Execute(c, null, timeoutSecs);
var tmpTable = b.GetTables()[0];
PopulateTempUpsertTableUsingBulkCopy(c, timeoutSecs, stagingRows, colMappings, caches, configuration, tmpTable, modifyWeekNumbers);
return(tableName);
}
public override void Delete(
string sqlConnectionString,
int timeoutSecs,
Row stagingRow,
TableColumnMappings colMappings,
FixupCaches caches,
DataStoreConfiguration configuration)
{
var b = new SqlBuilder();
long eventId = (long)stagingRow["event_id"];
b.AppendFormat("delete from {0}", QualifiedName);
b.Append("where event_instance_id like @P1");
var parameters = new List <SqlParameter> {
new SqlParameter("@P1", $"{eventId}-%")
};
DatabaseUtils.ExecuteSql(sqlConnectionString, b.ToString(), timeoutSecs, parameters.ToArray());
}
public TempUpsertEtlProcess(
SqlConnection c,
int timeoutSecs,
IEnumerable <Row> stagingRows,
TableColumnMappings colMappings,
FixupCaches caches,
DataStoreConfiguration configuration,
Table tmpTable,
bool modifyWeekNumbers)
{
if (configuration.Pipelines.PublicTempUpsert.SingleThreaded)
{
PipelineExecuter = new SingleThreadedPipelineExecuter();
}
_sqlConnection = c;
_timeoutSecs = timeoutSecs;
_stagingRows = stagingRows;
_colMappings = colMappings;
_caches = caches;
_configuration = configuration;
_tempTable = tmpTable;
_modifyWeekNumbers = modifyWeekNumbers;
}
public TransformationEtlProcess(
Table srcTable,
PublicTable targetTable,
FixupCaches caches,
string connectionString,
int timeoutSecs,
DataStoreConfiguration configuration,
TransformationType transformationType,
int srcTimetableId)
{
if (configuration.Pipelines.PublicTransformation.SingleThreaded)
{
PipelineExecuter = new SingleThreadedPipelineExecuter();
}
_srcTable = srcTable;
_targetTable = targetTable;
_caches = caches;
_connectionString = connectionString;
_timeoutSecs = timeoutSecs;
_configuration = configuration;
_transformationType = transformationType;
_srcTimetableId = srcTimetableId;
}
public override object GetStagingValue(Row stagingRow, FixupCaches caches, DataStoreConfiguration config)
{
long eventId = (long)stagingRow["federated_event_id"];
int srcTimetableId = (int)stagingRow["src_timetable_id"];
int timetableWeek = (int)stagingRow["timetable_week"]; // this column will have been added by event expansion
var eventTimes = caches.EventTimeCache.Get(eventId);
var weekDates = caches.WeekDatesCache.Get(srcTimetableId);
DateTime dateOfEvent = weekDates.StartingDates[timetableWeek - 1];
while (dateOfEvent.DayOfWeek != eventTimes.DayOfWeek)
{
dateOfEvent = dateOfEvent.AddDays(1);
}
DateTime eventInstanceDate = dateOfEvent.Date;
DateTime dt = PublicColumn.IndexOf("start", StringComparison.OrdinalIgnoreCase) >= 0
? eventInstanceDate.AddHours(eventTimes.Start.Hour).AddMinutes(eventTimes.Start.Minute)
: eventInstanceDate.AddHours(eventTimes.End.Hour).AddMinutes(eventTimes.End.Minute);
return(dt);
}
public void Upsert(
string sqlConnectionString,
int timeoutSecs,
Row stagingRow,
TableColumnMappings colMappings,
FixupCaches caches,
DataStoreConfiguration configuration)
{
var b = new SqlBuilder();
b.AppendFormat("MERGE {0} a", QualifiedName);
b.AppendFormat("using (select {0}) b", GetMergeSelectCols(colMappings));
b.Append("on (");
for (int n = 0; n < PrimaryKey.KeyPartsCount; ++n)
{
if (n > 0)
{
b.Append("and");
}
var keyPart = PrimaryKey[n];
b.AppendFormat("a.{0}={1}", keyPart.ColumnName, GetParamName(colMappings, 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("{0}={1}", col.Name, GetParamName(colMappings, col.Name));
}
b.Append("when NOT MATCHED then");
b.AppendFormat("INSERT ({0}) values({1})", colMappings.GetPublicColNamesAsCsv(), GetParamNamesAsCsv(colMappings));
b.Append(";");
int wkParamIndex = -1;
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);
if (colMappings[n].PublicColumn.Equals("timetable_week"))
{
wkParamIndex = n;
}
}
if (wkParamIndex > -1)
{
// make the "timetable_week" 1-based...
parameters[wkParamIndex].Value = (int)parameters[wkParamIndex].Value + 1;
}
if (colMappings.EventExpansionRequired)
{
string paramName1 = GetParamName(EventInstanceParamNum);
parameters.Add(new SqlParameter(paramName1, stagingRow[colMappings.EventExpansion.PublicEventInstanceColumn]));
string paramName2 = GetParamName(EventWeekParamNum);
parameters.Add(new SqlParameter(paramName2, stagingRow[colMappings.EventExpansion.PublicWeekColumn]));
string paramName3 = GetParamName(EventWeekOccurrenceParamNum);
parameters.Add(new SqlParameter(paramName3, stagingRow[colMappings.EventExpansion.PublicWeekOccurrenceColumn]));
}
DatabaseUtils.ExecuteSql(sqlConnectionString, b.ToString(), timeoutSecs, parameters.ToArray());
}
public abstract void AddParamValue(
List <SqlParameter> parameters,
string paramName,
Row stagingRow,
FixupCaches caches,
DataStoreConfiguration config);
public abstract object GetStagingValue(Row stagingRow, FixupCaches caches, DataStoreConfiguration config);
public override object GetStagingValue(Row stagingRow, FixupCaches caches, DataStoreConfiguration config)
{
return(stagingRow[StagingColumn]);
}
