public void RefreshTableCache <T>() where T : DataEntity
{
TableCache.Remove(typeof(T));
var table = _context.GetTable <T>();
TableCache.Add(typeof(T), table);
}
private void GetOrCreateTable <T>() where T : DataEntity
{
_context.CreateTableIfNotExists(new CreateTableArgs <T>(g => g.HashKey, g => g.Id));
Stopwatch timer = Stopwatch.StartNew();
try
{
_context.SubmitChanges();
Logger.Trace($"{nameof(DynamoStore)}.{nameof(GetCachedTable)}<{typeof(T).Name}>",
new LogItem("Action", "Getting or Creating table"),
new LogItem("Type", typeof(T).ToString),
new LogItem("DurationMilliseconds", timer.Elapsed.TotalMilliseconds));
}
catch (Exception ex)
{
Logger.Error($"{nameof(DynamoStore)}.{nameof(GetCachedTable)}<{typeof(T).Name}>",
new LogItem("Action", "Failed to create table"),
new LogItem("Type", typeof(T).ToString),
new LogItem("Exception Message", ex.Message),
new LogItem("Stack Trace", ex.StackTrace),
new LogItem("DurationMilliseconds", timer.Elapsed.TotalMilliseconds));
throw;
}
var table = _context.GetTable <T>();
TableCache.Add(typeof(T), table);
}
public bool LoadCollection <C>() where C : UnifiedIMObject <C>
{
string collection = UnifiedCollectionAttribute.GetCollection <C>();
if (string.IsNullOrEmpty(collection))
{
throw new Exception($"Missing UnifiedCollectionAttribute on type {typeof(C).Name}");
}
TableCache.Add(typeof(C), collection);
ColumnCache.Add(typeof(C), ColumnProperty.GetCollumns <C>(MSSQLHelper.Instance, true, "ObjectID").Values.ToList());
return(true);
}
public static AzureTableContext <T> GetContext <T>(string tableName) where T : ITableEntity, new()
{
lock (TableCache) {
if (!TableCache.ContainsKey(tableName))
{
var context = new AzureTableContext <T>(GetAccount(), tableName);
TableCache.Add(tableName, context);
context.Create();
}
return(TableCache[tableName] as AzureTableContext <T>);
}
}
public void TableCache_UnitTests()
{
//set a cache with a max of 5 items.
TableCache tableCache = new TableCache(5);
//add less items that the max size to the cache.
for (int i = 0; i < 4; i++)
{
tableCache.Add(new object[] { i });
}
Assert.True(tableCache.Count == 4);
//test the items are added
int count = 0;
int index = 0;
foreach (object[] row in tableCache)
{
Assert.Equal(count, (int)row[0]); //check enumerated result.
Assert.Equal(count, (int)tableCache[index++][0]); //check indexed result
count++;
}
Assert.Equal(4, index);
//add another item so that the cache = maxsize.
tableCache.Add(new object[] { 4 });
Assert.Equal(5, tableCache.Count);
//test the items are added
count = 0;
index = 0;
foreach (object[] row in tableCache)
{
Assert.Equal(count, (int)row[0]);
Assert.Equal(count, (int)tableCache[index++][0]); //check indexed result
count++;
}
Assert.Equal(5, index);
//add another item so that the cache will be exceed.
tableCache.Add(new object[] { 5 });
//table cound should be 5 as this is the max.
Assert.Equal(5, tableCache.Count);
//test the items are added starting the count at 1
count = 1;
index = 0;
foreach (object[] row in tableCache)
{
Assert.Equal(count, (int)row[0]);
Assert.Equal(count, (int)tableCache[index++][0]); //check indexed result
count++;
}
Assert.Equal(5, index);
//add one more items to be sure
tableCache.Add(new object[] { 6 });
//table cound should be 5 as this is the max.
Assert.Equal(5, tableCache.Count);
//test the items are added starting the count at 1
count = 2;
index = 0;
foreach (object[] row in tableCache)
{
Assert.Equal(count, (int)row[0]);
Assert.Equal(count, (int)tableCache[index++][0]); //check indexed result
count++;
}
Assert.Equal(5, index);
//run a test with rows = 0 (which means no unlimited cache)
//set a cache with a max of 5 items.
tableCache = new TableCache();
//add less items that the max size to the cache.
for (int i = 0; i < 4; i++)
{
tableCache.Add(new object[] { i });
}
Assert.True(tableCache.Count == 4);
//test the items are added
count = 0;
index = 0;
foreach (object[] row in tableCache)
{
Assert.Equal(count, (int)row[0]); //check enumerated result.
Assert.Equal(count, (int)tableCache[index++][0]); //check indexed result
count++;
}
}
public void Add(object[] values)
{
_data.Add(values);
}
/// <summary>
/// Runs the datalink test and returns the results
/// </summary>
/// <param name="cancellationToken"></param>
/// <returns></returns>
/// <exception cref="DatalinkTestRunException"></exception>
public async Task <List <TestResult> > Run(CancellationToken cancellationToken)
{
try
{
var ct = CancellationTokenSource.CreateLinkedTokenSource(_cancellationTokenSource.Token,
cancellationToken);
var token = ct.Token;
token.ThrowIfCancellationRequested();
var tempTargetTableKey = -10000;
WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Started, null, null);
var passed = 0;
var failed = 0;
foreach (var step in _datalinkTest.DexihDatalinkTestSteps.OrderBy(c => c.Position).Where(c => c.IsValid))
{
var datalink = _hub.DexihDatalinks.SingleOrDefault(c => c.IsValid && c.Key == step.DatalinkKey);
if (datalink == null)
{
throw new DatalinkTestRunException($"The datalink test {_datalinkTest.Name} failed as the datalink with the key {step.DatalinkKey} could not be found.");
}
UpdateProgress(1);
// prepare all the relevant tables
foreach (var testTable in step.DexihDatalinkTestTables)
{
await PrepareTestTable(testTable, token);
}
UpdateProgress(2);
datalink.AuditConnectionKey = _datalinkTest.AuditConnectionKey;
var dexihTargetConnection = _hub.DexihConnections.Single(c => c.IsValid && c.Key == step.TargetConnectionKey);
ICollection <DexihTable> targetTables;
// add a target table to store the data when the datalink doesn't have one.
if (!datalink.DexihDatalinkTargets.Any())
{
var target = new DexihDatalinkTarget()
{
TableKey = tempTargetTableKey--,
};
datalink.DexihDatalinkTargets.Add(target);
datalink.UpdateStrategy = EUpdateStrategy.Reload;
datalink.LoadStrategy = TransformWriterTarget.ETransformWriterMethod.Bulk;
// var targetTable = datalink.GetOutputTable();
var table = new DexihTable()
{
Key = target.TableKey,
DexihTableColumns = datalink.GetOutputTable().DexihDatalinkColumns
.Select(c => c.CloneProperties <DexihTableColumn>()).ToArray()
};
// dexihTargetConnection.DexihTables.Add(table);
targetTables = new List <DexihTable> {
table
};
}
else
{
targetTables = datalink.DexihDatalinkTargets.Select(c => _hub.GetTableFromKey(c.TableKey)).ToList();
}
if (targetTables.Count > 1)
{
throw new DatalinkTestRunException("Currently datalink tests can only be used with datalinks containing no more than one target table.");
}
foreach (var table in targetTables)
{
table.ConnectionKey = dexihTargetConnection.Key;
table.Name = step.TargetTableName;
table.Schema = step.TargetSchema;
}
UpdateProgress(50);
// run the datalink
var datalinkRun = new DatalinkRun(_transformSettings, _logger, WriterResult.AuditKey, datalink, _hub, null, _transformWriterOptions, _alertQueue, _alertEmails);
datalinkRun.WriterTarget.WriterResult.AuditType = "DatalinkTestStep";
datalinkRun.WriterTarget.WriterResult.ReferenceKey = step.Key;
// await datalinkRun.Initialize(cancellationToken);
await datalinkRun.Build(token);
await datalinkRun.Run(token);
UpdateProgress(70);
foreach (var table in targetTables)
{
var testResult = new TestResult()
{
Name = step.Name,
StartDate = DateTime.Now,
TestStepKey = step.Key
};
var dexihExpectedConnection = _hub.DexihConnections.Single(c => c.IsValid && c.Key == step.ExpectedConnectionKey);
var dexihExpectedTable = table.CloneProperties();
dexihExpectedTable.ConnectionKey = dexihExpectedConnection.Key;
dexihExpectedTable.Name = step.ExpectedTableName;
dexihExpectedTable.Schema = step.ExpectedSchema;
var expectedConnection = dexihExpectedConnection.GetConnection(_transformSettings);
var expectedTable = dexihExpectedTable.GetTable(_hub, expectedConnection, _transformSettings);
var expectedTransform = expectedConnection.GetTransformReader(expectedTable);
var targetConnection = dexihTargetConnection.GetConnection(_transformSettings);
var targetTable = table.GetTable(_hub, targetConnection, _transformSettings);
var targetTransform = targetConnection.GetTransformReader(targetTable);
// the error table is used to store any rows which do not match.
var dexihErrorConnection = _hub.DexihConnections.SingleOrDefault(c => c.IsValid && c.Key == step.ErrorConnectionKey);
Connection errorConnection = null;
Table errorTable = null;
if (dexihErrorConnection != null)
{
var dexihErrorTable = table.CloneProperties();
dexihErrorTable.ConnectionKey = dexihErrorConnection.Key;
dexihErrorTable.Name = step.ErrorTableName;
dexihErrorTable.Schema = step.ErrorSchema;
errorConnection = dexihErrorConnection.GetConnection(_transformSettings);
errorTable = dexihErrorTable.GetTable(_hub, errorConnection, _transformSettings);
foreach (var column in errorTable.Columns)
{
column.DeltaType = EDeltaType.NonTrackingField;
}
errorTable.Columns.Add(new TableColumn("error_audit_key", ETypeCode.Int64,
EDeltaType.CreateAuditKey));
errorTable.Columns.Add(new TableColumn("error_operation", ETypeCode.CharArray,
EDeltaType.DatabaseOperation)
{
MaxLength = 1
});
errorTable.Columns.Add(new TableColumn("mismatch_reason", ETypeCode.String,
EDeltaType.UpdateReason)
{
AllowDbNull = true
});
}
// use the delta transform to compare expected and target tables.
await using var delta = new TransformDelta(targetTransform, expectedTransform,
EUpdateStrategy.AppendUpdateDelete, 0, false, true);
await delta.Open(0, null, token);
testResult.RowsMismatching = 0;
testResult.RowsMissingFromSource = 0;
testResult.RowsMissingFromTarget = 0;
var operationColumn = delta.CacheTable.Columns.GetOrdinal(EDeltaType.DatabaseOperation);
var updateReasonColumn = delta.CacheTable.Columns.GetOrdinal(EDeltaType.UpdateReason);
var errorCache = new TableCache();
// loop through the delta. any rows which don't match on source/target should filter through, others will be ignored.
while (await delta.ReadAsync(token))
{
testResult.TestPassed = false;
switch (delta[operationColumn])
{
case 'C':
testResult.RowsMissingFromTarget++;
break;
case 'U':
testResult.RowsMismatching++;
break;
case 'D':
testResult.RowsMissingFromSource++;
break;
}
datalinkRun.WriterTarget.WriterResult.Failed++;
WriterResult.Failed++;
WriterResult.IncrementRowsCreated();
if (errorTable != null && errorCache.Count < MaxErrorRows)
{
var row = new object[errorTable.Columns.Count];
for (var i = 0; i < errorTable.Columns.Count; i++)
{
var column = errorTable[i];
switch (column.DeltaType)
{
case EDeltaType.CreateAuditKey:
row[i] = datalinkRun.WriterTarget.WriterResult.AuditKey;
break;
case EDeltaType.DatabaseOperation:
row[i] = delta[operationColumn];
break;
case EDeltaType.UpdateReason:
row[i] = delta[updateReasonColumn];
break;
default:
row[i] = delta[column.Name];
break;
}
}
errorCache.Add(row);
}
}
if (errorCache.Count > 0)
{
errorTable.Data = errorCache;
var createReader = new ReaderMemory(errorTable);
if (!await errorConnection.TableExists(errorTable, cancellationToken))
{
await errorConnection.CreateTable(errorTable, false, cancellationToken);
}
await errorConnection.ExecuteInsertBulk(errorTable, createReader, cancellationToken);
}
WriterResult.RowsIgnored += delta.TotalRowsIgnored;
WriterResult.RowsPreserved += delta.TotalRowsPreserved;
if (testResult.TestPassed == false)
{
failed++;
}
else
{
passed++;
}
if (datalinkRun.WriterTarget.WriterResult.RunStatus == TransformWriterResult.ERunStatus.Finished)
{
if (testResult.TestPassed)
{
datalinkRun.WriterTarget.WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Passed, "Datalink test passed", null);
}
else
{
datalinkRun.WriterTarget.WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Failed, $"Datalink test failed, {testResult.RowsMissingFromSource} rows missing from expected, {testResult.RowsMissingFromTarget} rows missing from actual, {testResult.RowsMismatching} rows with mismatching columns.", null);
}
}
TestResults.Add(testResult);
}
}
if (WriterResult.Failed > 0)
{
WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Failed, $"{passed} tests passed, {failed} test failed.", null);
}
else
{
WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Passed, $"{passed} tests passed.", null);
}
await WriterResult.CompleteDatabaseWrites();
return(TestResults);
}
catch (Exception ex)
{
WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Abended, ex.Message, ex);
return(TestResults);
}
}
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 bool LoadCollection <C>() where C : UnifiedIMObject <C>
{
string collection = UnifiedCollectionAttribute.GetCollection <C>();
if (string.IsNullOrEmpty(collection))
{
throw new Exception($"Missing UnifiedCollectionAttribute on type {typeof(C).Name}");
}
TableCache.Add(typeof(C), collection);
ColumnCache.Add(typeof(C), ColumnProperty.GetCollumns <C>(MySQLHelper.Instance, true, "ObjectID").Values.ToList());
ColumnProperty objidcol = GetColumns <C>().FirstOrDefault(X => X.Name == "ObjectID");
foreach (ColumnProperty prop in GetColumns <C>())
{
if (prop.Name == "ObjectID")
{
prop.OverrideSqlType("char(32)");
}
else
{
prop.OverrideSqlType(MySQLHelper.Instance.GetSqlType(prop.Type, prop.Column));
}
}
if (!MySQLTable.GetTables(SQL).Contains(collection))
{
try
{
MySQLTable.CreateTable(SQL, collection, GetColumns <C>());
}
catch (Exception ex)
{
throw new Exception($"Failed to create table {collection}");
}
}
else
{
MySQLTable table = MySQLTable.GetTable(SQL, collection);
List <ColumnProperty> todo = table.Columns.ToList();
foreach (ColumnProperty col in GetColumns <C>())
{
ColumnProperty existing = table.Columns.FirstOrDefault(X => X.Name == col.Name);
if (existing == null)
{
System.Console.WriteLine($"SQL missing Column {col.Name}... Adding");
try
{
MySQLTable.AddColumn(SQL, collection, col.Name, col.SqlType);
}
catch (Exception ex)
{
throw new Exception($"Failed to add collumn {col.Name}\n{ex.Message}");
}
}
else if (!existing.SqlType.StartsWith(col.SqlType))
{
System.Console.WriteLine($"SQL incorrect Column Type for {col.Name}... Converting");
try
{
MySQLTable.ConvertColumn(SQL, collection, col.Name, col.SqlType);
}
catch (Exception ex)
{
throw new Exception($"Failed to convert collumn {col.Name}\n{ex.Message}");
}
}
if (existing != null)
{
todo.Remove(existing);
}
}
foreach (ColumnProperty prop in todo)
{
System.Console.WriteLine($"Excess collumn {prop.Name}... Removing");
try
{
MySQLTable.RemoveColumn(SQL, collection, prop.Name);
}
catch (Exception ex)
{
throw new Exception($"Failed to remove collumn {prop.Name}\n{ex.Message}");
}
}
}
return(true);
}
