public async Task PocoTest_InsertBulk()
{
var items = new List <SamplePocoClass>
{
new SamplePocoClass("column1", 1, new DateTime(2000, 01, 02)),
new SamplePocoClass("column2", 2, new DateTime(2000, 01, 03)),
new SamplePocoClass("column3", 3, new DateTime(2000, 01, 04)),
};
var pocoTable = new PocoTable <SamplePocoClass>();
var connection = new ConnectionMemory();
await pocoTable.CreateTable(connection, true, CancellationToken.None);
await pocoTable.ExecuteInsertBulk(connection, items, CancellationToken.None);
var reader = connection.GetTransformReader(pocoTable.Table);
var count = 0;
while (await reader.ReadAsync())
{
count++;
Assert.Equal("column" + count, reader["StringColumn"]);
Assert.Equal(count, reader["IntColumn"]);
Assert.Equal(new DateTime(2000, 01, 01).AddDays(count), reader["DateColumn"]);
}
Assert.Equal(3, count);
}
public override string GetAutoIncrementExpression(PocoTable ti)
{
if (!string.IsNullOrEmpty(ti.SequenceName))
{
return(string.Format("{0}.nextval", ti.SequenceName));
}
return(null);
}
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);
}
public async Task PocoTest_Delete()
{
var item = new SamplePocoClass("column1", 1, new DateTime(2000, 01, 02));
var pocoTable = new PocoTable <SamplePocoClass>();
var connection = new ConnectionMemory();
// creat table, insert sample column, and then delete.
await pocoTable.CreateTable(connection, true, CancellationToken.None);
await pocoTable.ExecuteInsert(connection, item, CancellationToken.None);
await pocoTable.ExecuteDelete(connection, item, CancellationToken.None);
var reader = connection.GetTransformReader(pocoTable.Table);
var moreRecords = await reader.ReadAsync();
Assert.False(moreRecords);
}
/// <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);
}
public async Task PocoTest_Update()
{
var item = new SamplePocoClass("column1", 1, new DateTime(2000, 01, 02));
var updateItem = new SamplePocoClass("column1", 2, new DateTime(2000, 01, 03));
var pocoTable = new PocoTable <SamplePocoClass>();
var connection = new ConnectionMemory();
// creat table, insert sample column, and then update.
await pocoTable.CreateTable(connection, true, CancellationToken.None);
await pocoTable.ExecuteInsert(connection, item, CancellationToken.None);
await pocoTable.ExecuteUpdate(connection, updateItem, CancellationToken.None);
var reader = connection.GetTransformReader(pocoTable.Table);
await reader.ReadAsync();
Assert.Equal("column1", reader["StringColumn"]);
Assert.Equal(2, reader["IntColumn"]);
Assert.Equal(new DateTime(2000, 01, 03), reader["DateColumn"]);
}
public virtual async Task <List <TransformWriterResult> > GetTransformWriterResults(long?hubKey, long connectionKey, long[] referenceKeys, string auditType, long?auditKey, TransformWriterResult.ERunStatus?runStatus, bool previousResult, bool previousSuccessResult, bool currentResult, DateTime?startTime, int rows, long?parentAuditKey, bool childItems, CancellationToken cancellationToken)
{
Transform reader = null;
var watch = new Stopwatch();
watch.Start();
var picoTable = new PocoTable <TransformWriterResult>();
reader = GetTransformReader(picoTable.Table);
var filters = new List <Filter>();
if (hubKey != null)
{
filters.Add(new Filter(new TableColumn("HubKey", ETypeCode.Int64), Filter.ECompare.IsEqual, hubKey));
}
if (referenceKeys != null && referenceKeys.Length > 0)
{
filters.Add(new Filter(new TableColumn("ReferenceKey", ETypeCode.Int64), Filter.ECompare.IsIn, referenceKeys));
}
if (auditType != null)
{
filters.Add(new Filter(new TableColumn("AuditType", ETypeCode.String), Filter.ECompare.IsEqual, auditType));
}
if (auditKey != null)
{
filters.Add(new Filter(new TableColumn("AuditKey", ETypeCode.Int64), Filter.ECompare.IsEqual, auditKey));
}
if (runStatus != null)
{
filters.Add(new Filter(new TableColumn("RunStatus", ETypeCode.String), Filter.ECompare.IsEqual, runStatus.ToString()));
}
if (startTime != null)
{
filters.Add(new Filter(new TableColumn("StartTime", ETypeCode.DateTime), Filter.ECompare.GreaterThanEqual, startTime));
}
if (currentResult)
{
filters.Add(new Filter(new TableColumn("IsCurrent", ETypeCode.Boolean), Filter.ECompare.IsEqual, true));
}
if (previousResult)
{
filters.Add(new Filter(new TableColumn("IsPrevious", ETypeCode.Boolean), Filter.ECompare.IsEqual, true));
}
if (previousSuccessResult)
{
filters.Add(new Filter(new TableColumn("IsPreviousSuccess", ETypeCode.Boolean), Filter.ECompare.IsEqual, true));
}
if (parentAuditKey != null)
{
filters.Add(new Filter(new TableColumn("ParentAuditKey", ETypeCode.Int64), Filter.ECompare.IsEqual, parentAuditKey));
}
var sorts = new List <Sort>()
{
new Sort(new TableColumn("AuditKey", ETypeCode.Int64), Sort.EDirection.Descending)
};
var query = new SelectQuery()
{
Filters = filters, Sorts = sorts, Rows = rows
};
//add a sort transform to ensure sort order.
reader = new TransformSort(reader, sorts);
var returnValue = await reader.Open(0, query, cancellationToken);
if (!returnValue)
{
throw new ConnectionException($"Failed to get the transform writer results on table {picoTable.Table} at {Name}.");
}
var pocoReader = new PocoLoader <TransformWriterResult>();
var writerResults = await pocoReader.ToListAsync(reader, rows, cancellationToken);
foreach (var result in writerResults)
{
result.AuditConnectionKey = connectionKey;
if (childItems)
{
result.ChildResults = await GetTransformWriterResults(hubKey, connectionKey, null, null, null, null, previousResult, previousSuccessResult, currentResult, null, 0, result.AuditKey, false, cancellationToken);
}
if (cancellationToken.IsCancellationRequested)
{
break;
}
}
watch.Stop();
reader.Dispose();
return(writerResults);
}
///// <summary>
///// Returns an SQL Statement that can check for the existence of a row in the database.
///// </summary>
///// <returns></returns>
//public virtual string GetExistsSql()
//{
// return "SELECT COUNT(*) FROM {0} WHERE {1}";
//}
/// <summary>
/// Return an SQL expression that can be used to populate the primary key column of an auto-increment column.
/// </summary>
/// <param name="tableInfo">Table info describing the table</param>
/// <returns>An SQL expressions</returns>
/// <remarks>See the Oracle database type for an example of how this method is used.</remarks>
public virtual string GetAutoIncrementExpression(PocoTable tableInfo)
{
return(null);
}
