public void SetProperties(long hubKey, long auditConnectionKey, long auditKey, string auditType, long referenceKey,
long parentAuditKey, string referenceName, long sourceTableKey, string sourceTableName,
long targetTableKey, string targetTableName, Connection auditConnection,
TransformWriterResult lastSuccessfulResult, ETriggerMethod triggerMethod, string triggerInfo)
{
HubKey = hubKey;
AuditConnectionKey = auditConnectionKey;
AuditKey = auditKey;
AuditType = auditType;
ReferenceKey = referenceKey;
ParentAuditKey = parentAuditKey;
ReferenceName = referenceName;
SourceTableKey = sourceTableKey;
SourceTableName = sourceTableName;
TargetTableKey = targetTableKey;
TargetTableName = targetTableName;
_auditConnection = auditConnection;
LastRowTotal = lastSuccessfulResult?.RowsTotal ?? 0;
LastMaxIncrementalValue = lastSuccessfulResult?.MaxIncrementalValue;
InitializeTime = DateTime.Now;
LastUpdateTime = InitializeTime;
RunStatus = ERunStatus.Initialised;
TriggerMethod = triggerMethod;
TriggerInfo = triggerInfo;
IsCurrent = true;
IsPrevious = false;
IsPreviousSuccess = false;
}
public virtual async Task UpdateAudit(TransformWriterResult writerResult, CancellationToken cancellationToken)
{
var picoTable = new PocoTable <TransformWriterResult>();
writerResult.IsCurrent = true;
writerResult.IsPrevious = false;
writerResult.IsPreviousSuccess = false;
//when the runstatuss is finished or finished with errors, set the previous success record to false.
if (writerResult.RunStatus == TransformWriterResult.ERunStatus.Finished || writerResult.RunStatus == TransformWriterResult.ERunStatus.FinishedErrors)
{
var updateLatestColumn = new List <QueryColumn>()
{
new QueryColumn(new TableColumn("IsCurrent", ETypeCode.Boolean), false),
new QueryColumn(new TableColumn("IsPreviousSuccess", ETypeCode.Boolean), false)
};
var updateLatestFilters = new List <Filter>()
{
new Filter(new TableColumn("HubKey", ETypeCode.Int64), Filter.ECompare.IsEqual, writerResult.HubKey),
new Filter(new TableColumn("ReferenceKey", ETypeCode.Int64), Filter.ECompare.IsEqual, writerResult.ReferenceKey),
new Filter(new TableColumn("IsPreviousSuccess", ETypeCode.Boolean), Filter.ECompare.IsEqual, true),
};
var updateIsLatest = new UpdateQuery(picoTable.Table.Name, updateLatestColumn, updateLatestFilters);
await ExecuteUpdate(picoTable.Table, new List <UpdateQuery>() { updateIsLatest }, CancellationToken.None);
writerResult.IsPreviousSuccess = true;
}
//when finished, mark the previous result to false.
if (writerResult.IsFinished)
{
var updateLatestColumn = new List <QueryColumn>()
{
new QueryColumn(new TableColumn("IsCurrent", ETypeCode.Boolean), false),
new QueryColumn(new TableColumn("IsPrevious", ETypeCode.Boolean), false)
};
var updateLatestFilters = new List <Filter>()
{
new Filter(new TableColumn("HubKey", ETypeCode.Int64), Filter.ECompare.IsEqual, writerResult.HubKey),
new Filter(new TableColumn("ReferenceKey", ETypeCode.Int64), Filter.ECompare.IsEqual, writerResult.ReferenceKey),
new Filter(new TableColumn("IsPrevious", ETypeCode.Boolean), Filter.ECompare.IsEqual, true),
};
var updateIsLatest = new UpdateQuery(picoTable.Table.Name, updateLatestColumn, updateLatestFilters);
await ExecuteUpdate(picoTable.Table, new List <UpdateQuery>() { updateIsLatest }, CancellationToken.None);
writerResult.IsCurrent = false;
writerResult.IsPrevious = true;
}
await picoTable.ExecuteUpdate(this, writerResult, cancellationToken);
}
/// <summary>
/// Propulates the writerResult with a initial values, and writes the status to the database table.
/// </summary>
/// <param name="writreResult"></param>
/// <param name="hubKey"></param>
/// <param name="connectionKey"></param>
/// <param name="auditType"></param>
/// <param name="referenceKey"></param>
/// <param name="parentAuditKey"></param>
/// <param name="referenceName"></param>
/// <param name="sourceTableKey"></param>
/// <param name="sourceTableName"></param>
/// <param name="targetTableKey"></param>
/// <param name="targetTableName"></param>
/// <param name="triggerMethod"></param>
/// <param name="triggerInfo"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public virtual async Task InitializeAudit(TransformWriterResult writreResult, long hubKey, long connectionKey, string auditType, long referenceKey, long parentAuditKey, string referenceName, long sourceTableKey, string sourceTableName, long targetTableKey, string targetTableName, TransformWriterResult.ETriggerMethod triggerMethod, string triggerInfo, CancellationToken cancellationToken)
{
var picoTable = new PocoTable <TransformWriterResult>();
TransformWriterResult previousResult = null;
//create the audit table if it does not exist.
var tableExistsResult = await picoTable.TableExists(this, cancellationToken);
if (tableExistsResult == false)
{
//create the table if it doesn't already exist.
await picoTable.CreateTable(this, false, cancellationToken);
}
else
{
//get the last audit result for this reference to collect previous run information
previousResult = await GetPreviousResult(hubKey, connectionKey, referenceKey, CancellationToken.None);
}
writreResult.SetProperties(hubKey, connectionKey, 0, auditType, referenceKey, parentAuditKey, referenceName, sourceTableKey, sourceTableName, targetTableKey, targetTableName, this, previousResult, triggerMethod, triggerInfo);
await picoTable.ExecuteInsert(this, writreResult, cancellationToken);
}
/// <summary>
/// Writes all record from the inTransform to the target table and reject table.
/// </summary>
/// <param name="writerResult"></param>
/// <param name="inTransform">Transform to read data from</param>
/// <param name="targetConnection"></param>
/// <param name="rejectTable"></param>
/// <param name="rejectConnection">Reject connection (if null will use connection)</param>
/// <param name="profileTable"></param>
/// <param name="profileConnection">Reject connection (if null will use connection)</param>
/// <param name="targetTable"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public async Task <bool> WriteAllRecords(TransformWriterResult writerResult, Transform inTransform, Table targetTable, Connection targetConnection, Table rejectTable, Connection rejectConnection, Table profileTable, Connection profileConnection, CancellationToken cancellationToken)
{
_cancellationToken = cancellationToken;
_targetConnection = targetConnection;
if (rejectConnection == null)
{
_rejectConnection = targetConnection;
}
else
{
_rejectConnection = rejectConnection;
}
if (profileConnection == null)
{
_profileConnection = targetConnection;
}
else
{
_profileConnection = profileConnection;
}
var updateResult = await writerResult.SetRunStatus(TransformWriterResult.ERunStatus.Started, null, null, cancellationToken);
if (!updateResult)
{
return(false);
}
_targetTable = targetTable.Copy(false, true);
_rejectTable = rejectTable?.Copy(false, true);
_profileTable = profileTable?.Copy(false, true);
_inTransform = inTransform;
writerResult.RejectTableName = rejectTable?.Name;
try
{
await WriteStart(_inTransform, writerResult, cancellationToken);
}
catch (Exception ex)
{
var message = $"The transform writer failed to start. {ex.Message}";
var newException = new TransformWriterException(message, ex);
await writerResult.SetRunStatus(TransformWriterResult.ERunStatus.Abended, message, newException, cancellationToken);
return(false);
}
var firstRead = true;
Task writeTask = null;
while (await inTransform.ReadAsync(cancellationToken))
{
if (firstRead)
{
var runStatusResult = await writerResult.SetRunStatus(TransformWriterResult.ERunStatus.Running, null, null, CancellationToken.None);
if (!runStatusResult)
{
return(false);
}
firstRead = false;
}
if (writeTask != null)
{
await writeTask;
if (writeTask.IsFaulted)
{
var message = $"The transform writer failed writing data. {writeTask.Exception?.Message}";
var newException = new TransformWriterException(message, writeTask.Exception);
await writerResult.SetRunStatus(TransformWriterResult.ERunStatus.Abended, message, newException, CancellationToken.None);
return(false);
}
}
writeTask = WriteRecord(writerResult, inTransform);
if (cancellationToken.IsCancellationRequested)
{
var runStatusResult = await writerResult.SetRunStatus(TransformWriterResult.ERunStatus.Cancelled, null, null, CancellationToken.None);
return(runStatusResult);
}
}
try
{
await WriteFinish(writerResult, inTransform);
}
catch (Exception ex)
{
var message = $"The transform writer failed to finish. {ex.Message}";
var newException = new TransformWriterException(message, ex);
await writerResult.SetRunStatus(TransformWriterResult.ERunStatus.Abended, message, newException, CancellationToken.None);
return(false);
}
if (_profileTable != null)
{
var profileResults = inTransform.GetProfileResults();
if (profileResults != null)
{
var profileExists = await _profileConnection.TableExists(_profileTable, cancellationToken);
if (!profileExists)
{
await _profileConnection.CreateTable(_profileTable, false, cancellationToken);
}
writerResult.ProfileTableName = _profileTable.Name;
try
{
await _profileConnection.ExecuteInsertBulk(_profileTable, profileResults, cancellationToken);
}
catch (Exception ex)
{
var message = $"Failed to save profile results. {ex.Message}";
var newException = new TransformWriterException(message, ex);
await writerResult.SetRunStatus(TransformWriterResult.ERunStatus.Abended, message, newException, CancellationToken.None);
return(false);
}
}
}
var setRunStatusResult = await writerResult.SetRunStatus(TransformWriterResult.ERunStatus.Finished, null, null, CancellationToken.None);
return(setRunStatusResult);
}
public Task <bool> WriteAllRecords(TransformWriterResult writerResult, Transform inTransform, Table targetTable, Connection targetConnection, Table rejectTable, Connection rejectConnection, CancellationToken cancellationToken)
{
return(WriteAllRecords(writerResult, inTransform, targetTable, targetConnection, rejectTable, rejectConnection, null, null, cancellationToken));
}
public async Task WriteFinish(TransformWriterResult writerResult, Transform reader)
{
_writeOpen = false;
//write out the remaining rows.
if (_createRows.Count > 0)
{
await DoCreates();
}
if (_updateRows.Count > 0)
{
await DoUpdate();
}
if (_deleteRows.Count > 0)
{
await DoDelete();
}
if (_rejectRows.Count > 0)
{
await DoReject();
}
//wait for any write tasks to finish
if (_createRecordsTask != null)
{
var returnValue = await _createRecordsTask;
WriteDataTicks += returnValue;
}
if (_updateRecordsTask != null)
{
var returnValue = await _updateRecordsTask;
WriteDataTicks += returnValue;
}
if (_deleteRecordsTask != null)
{
var returnValue = await _deleteRecordsTask;
WriteDataTicks += returnValue;
}
if (_rejectRecordsTask != null)
{
var returnValue = await _rejectRecordsTask;
WriteDataTicks += returnValue;
}
//update the statistics.
writerResult.RowsFiltered = reader.TotalRowsFiltered;
writerResult.RowsSorted = reader.TotalRowsSorted;
writerResult.RowsRejected = reader.TotalRowsRejected;
writerResult.RowsPreserved = reader.TotalRowsPreserved;
writerResult.RowsIgnored = reader.TotalRowsIgnored;
writerResult.RowsReadPrimary = reader.TotalRowsReadPrimary;
writerResult.RowsReadReference = reader.TotalRowsReadReference;
//calculate the throughput figures
var rowsWritten = writerResult.RowsTotal - writerResult.RowsIgnored;
var performance = new StringBuilder();
performance.AppendLine(reader.PerformanceSummary());
performance.AppendLine($"Target {_targetConnection.Name} - Time: {WriteDataTicks:c}, Rows: {rowsWritten}, Performance: {(rowsWritten/WriteDataTicks.TotalSeconds):F} rows/second");
writerResult.PerformanceSummary = performance.ToString();
writerResult.WriteTicks = WriteDataTicks.Ticks;
writerResult.ReadTicks = reader.ReaderTimerTicks().Ticks;
writerResult.ProcessingTicks = reader.ProcessingTimerTicks().Ticks;
writerResult.EndTime = DateTime.Now;
if (writerResult.RowsTotal == 0)
{
writerResult.MaxIncrementalValue = writerResult.LastMaxIncrementalValue;
}
else
{
writerResult.MaxIncrementalValue = reader.GetMaxIncrementalValue();
}
reader.Dispose();
try
{
await _targetConnection.DataWriterFinish(_targetTable);
}
catch (Exception ex)
{
throw new TransformWriterException($"The transform writer failed to finish when attempting a finish on the target table {_targetTable.Name} in {_targetConnection.Name}. {ex.Message}.", ex);
}
}
public async Task WriteRecord(TransformWriterResult writerResult, Transform reader)
{
if (_writeOpen == false)
{
throw new TransformWriterException($"Transform write failed to write record as the WriteStart has not been called.");
}
//split the operation field (if it exists) and create copy of the row.
char operation;
//determine the type of operation (create, update, delete, reject)
if (_operationColumnIndex == -1)
{
operation = 'C';
}
else
{
operation = (char)reader[_operationColumnIndex];
}
Table table;
var ordinals = _fieldOrdinals;
if (operation == 'R')
{
table = _rejectTable;
ordinals = _rejectFieldOrdinals;
if (_rejectTable == null)
{
var rejectColumn = reader.GetOrdinal("RejectedReason");
var rejectReason = "";
if (rejectColumn > 0)
{
rejectReason = reader[rejectColumn].ToString();
}
else
{
rejectReason = "No reject reason found.";
}
throw new TransformWriterException($"Transform write failed as a record was rejected, however there is no reject table set. The reject reason was: {rejectReason}.");
}
}
else
{
table = _targetTable;
}
var columnCount = table.Columns.Count;
var row = new object[columnCount];
for (var i = 0; i < columnCount; i++)
{
//int ordinal = reader.GetOrdinal(table.Columns[i].ColumnName);
var ordinal = ordinals[i];
if (ordinal >= 0)
{
row[i] = reader[ordinal];
}
}
switch (operation)
{
case 'C':
_createRows.Add(row);
writerResult.IncrementRowsCreated();
if (_createRows.Count >= CommitSize)
{
await DoCreates();
return;
}
break;
case 'U':
_updateRows.Add(row);
writerResult.IncrementRowsUpdated();
if (_updateRows.Count >= CommitSize)
{
await DoUpdate();
return;
}
break;
case 'D':
_deleteRows.Add(row);
writerResult.IncrementRowsDeleted();
if (_deleteRows.Count >= CommitSize)
{
await DoDelete();
return;
}
break;
case 'R':
_rejectRows.Add(row);
if (_rejectRows.Count >= CommitSize)
{
await DoReject();
return;
}
break;
case 'T':
if (!_targetConnection.DynamicTableCreation)
{
await _targetConnection.TruncateTable(_targetTable, _cancellationToken);
}
else
{
return;
}
break;
}
return;
}
public async Task WriteStart(Transform inTransform, TransformWriterResult writerResult, CancellationToken cancellationToken)
{
if (_writeOpen)
{
throw new TransformWriterException("Transform write failed to start, as a previous operation is still running.");
}
var returnValue = await _inTransform.Open(writerResult.AuditKey, null, cancellationToken);
if (!returnValue)
{
throw new TransformWriterException("Transform write failed to start, could not open the first transform.");
}
_operationColumnIndex = _inTransform.CacheTable.GetDeltaColumnOrdinal(TableColumn.EDeltaType.DatabaseOperation);
_createRows = new TableCache();
_updateRows = new TableCache();
_deleteRows = new TableCache();
_rejectRows = new TableCache();
//create template queries, with the values set to paramaters (i.e. @param1, @param2)
//new InsertQuery(_targetTable.Name, _targetTable.Columns.Select(c => new QueryColumn(new TableColumn(c.Name, c.Datatype), "@param" + _targetTable.GetOrdinal(c.Name).ToString())).ToList());
//new UpdateQuery(
// _targetTable.Name,
// _targetTable.Columns.Where(c=> c.DeltaType != TableColumn.EDeltaType.SurrogateKey).Select(c => new QueryColumn(c, "@param" + _targetTable.GetOrdinal(c.Name).ToString())).ToList(),
// _targetTable.Columns.Where(c => c.DeltaType == TableColumn.EDeltaType.SurrogateKey).Select(c=> new Filter(c, Filter.ECompare.IsEqual, "@surrogateKey")).ToList()
//);
//new DeleteQuery(_targetTable.Name, _targetTable.Columns.Where(c => c.DeltaType == TableColumn.EDeltaType.SurrogateKey).Select(c => new Filter(c, Filter.ECompare.IsEqual, "@surrogateKey")).ToList());
//if the table doesn't exist, create it.
var tableExistsResult = await _targetConnection.TableExists(_targetTable, cancellationToken);
if (!tableExistsResult)
{
await _targetConnection.CreateTable(_targetTable, false, cancellationToken);
}
await _targetConnection.DataWriterStart(_targetTable);
//if the truncate table flag is set, then truncate the target table.
if (writerResult.TruncateTarget)
{
await _targetConnection.TruncateTable(_targetTable, cancellationToken);
}
var columnCount = _targetTable.Columns.Count;
_fieldOrdinals = new int[columnCount];
for (var i = 0; i < columnCount; i++)
{
_fieldOrdinals[i] = inTransform.GetOrdinal(_targetTable.Columns[i].Name);
}
if (_rejectTable != null)
{
columnCount = _rejectTable.Columns.Count;
_rejectFieldOrdinals = new int[columnCount];
for (var i = 0; i < columnCount; i++)
{
_rejectFieldOrdinals[i] = inTransform.GetOrdinal(_rejectTable.Columns[i].Name);
}
}
_writeOpen = true;
}