public async Task Transform(Connection connection, string databaseName)
{
Table table = DataSets.CreateTable();
await connection.CreateDatabase(databaseName, CancellationToken.None);
//create a new table and write some data to it.
Transform reader = DataSets.CreateTestData();
await connection.CreateTable(table, true, CancellationToken.None);
TransformWriter writer = new TransformWriter();
TransformWriterResult writerResult = new TransformWriterResult();
await connection.InitializeAudit(writerResult, 0, "DataLink", 1, 2, "Test", 1, "Source", 2, "Target", TransformWriterResult.ETriggerMethod.Manual, "Test", CancellationToken.None);
var writeRecords = await writer.WriteAllRecords(writerResult, reader, table, connection, null, null, null, null, CancellationToken.None);
Assert.True(writeRecords, $"WriteAllRecords failed with message {writerResult.Message}. Details:{writerResult.ExceptionDetails}");
//check database can sort
if (connection.CanSort)
{
//use the new table test the data base is sorting
reader = connection.GetTransformReader(table);
SelectQuery query = new SelectQuery()
{
Sorts = new List <Sort>()
{
new Sort("IntColumn", Sort.EDirection.Descending)
}
};
await reader.Open(0, query, CancellationToken.None);
int sortValue = 10;
while (await reader.ReadAsync())
{
Assert.Equal(sortValue, Convert.ToInt32(reader["IntColumn"]));
sortValue--;
}
Assert.Equal(0, sortValue);
}
//check database can filter
if (connection.CanFilter)
{
//use the new table to test database is filtering
reader = connection.GetTransformReader(table);
SelectQuery query = new SelectQuery()
{
Filters = new List <Filter>()
{
new Filter("IntColumn", Filter.ECompare.LessThanEqual, 5)
}
};
await reader.Open(0, query, CancellationToken.None);
int count = 0;
while (await reader.ReadAsync())
{
Assert.True(Convert.ToInt32(reader["IntColumn"]) <= 5);
count++;
}
Assert.Equal(5, count);
}
Table deltaTable = DataSets.CreateTable();
deltaTable.AddAuditColumns();
deltaTable.Name = "DeltaTable";
await connection.CreateTable(deltaTable, true, CancellationToken.None);
Transform targetReader = connection.GetTransformReader(deltaTable);
reader = connection.GetTransformReader(table);
TransformDelta transformDelta = new TransformDelta(reader, targetReader, TransformDelta.EUpdateStrategy.AppendUpdate, 1, false);
writerResult = new TransformWriterResult();
await connection.InitializeAudit(writerResult, 0, "Datalink", 1, 2, "Test", 1, "Source", 2, "Target", TransformWriterResult.ETriggerMethod.Manual, "Test", CancellationToken.None);
var writeAllResult = await writer.WriteAllRecords(writerResult, transformDelta, deltaTable, connection, CancellationToken.None);
Assert.True(writeAllResult, writerResult.Message);
Assert.Equal(10L, writerResult.RowsCreated);
//check the audit table loaded correctly.
var auditTable = await connection.GetTransformWriterResults(0, null, "Datalink", writerResult.AuditKey, null, true, false, false, null, 1, null, false, CancellationToken.None);
Assert.Equal(10L, auditTable[0].RowsCreated);
}
public async Task Unit(Connection connection, string databaseName)
{
await connection.CreateDatabase(databaseName, CancellationToken.None);
var newTable = DataSets.CreateTable();
var table = await connection.InitializeTable(newTable, 1000);
//create the table
await connection.CreateTable(table, true, CancellationToken.None);
var guid = Guid.NewGuid();
//insert a single row
var insertQuery = new InsertQuery("test_table", new List <QueryColumn>()
{
new QueryColumn(new TableColumn("IntColumn", ETypeCode.Int32), 1),
new QueryColumn(new TableColumn("StringColumn", ETypeCode.String), "value1"),
new QueryColumn(new TableColumn("DateColumn", ETypeCode.DateTime), new DateTime(2001, 01, 21)),
new QueryColumn(new TableColumn("DoubleColumn", ETypeCode.Decimal), 1.1),
new QueryColumn(new TableColumn("DecimalColumn", ETypeCode.Decimal), 1.1m),
new QueryColumn(new TableColumn("BooleanColumn", ETypeCode.Boolean), true),
new QueryColumn(new TableColumn("GuidColumn", ETypeCode.Guid), guid)
});
var insertReturn = await connection.ExecuteInsert(table, new List <InsertQuery>() { insertQuery }, CancellationToken.None);
// Assert.True(insertReturn > 0, "InsertQuery");
Table sqlTable;
if (connection.CanUseSql)
{
// create a simple table with a sql query.
sqlTable = new Table("SqlTest")
{
UseQuery = true,
QueryString = $"select * from {table.Name}"
};
}
else
{
sqlTable = table;
}
// check the columns can be imported.
var importTable = await connection.GetSourceTableInfo(sqlTable, CancellationToken.None);
Assert.Equal(7, importTable.Columns.Count);
Assert.Equal("IntColumn", importTable.Columns["IntColumn"].Name);
Assert.True(ETypeCode.Int32 == importTable.Columns["IntColumn"].DataType || ETypeCode.Int64 == importTable.Columns["IntColumn"].DataType);
Assert.Equal("StringColumn", importTable.Columns["StringColumn"].Name);
Assert.Equal(ETypeCode.String, importTable.Columns["StringColumn"].DataType);
// commented date check as sqlite treats dates as string. Value check below does the test adequately.
// Assert.Equal("DateColumn", importTable.Columns["DateColumn"].Name);
// Assert.Equal(ETypeCode.DateTime, importTable.Columns["DateColumn"].Datatype);
// Assert.Equal("DecimalColumn", importTable.Columns["DecimalColumn"].Name);
// Assert.Equal(ETypeCode.Decimal, importTable.Columns["DecimalColumn"].Datatype);
Assert.Equal("GuidColumn", importTable.Columns["GuidColumn"].Name);
Assert.True(ETypeCode.String == importTable.Columns["GuidColumn"].DataType || ETypeCode.Guid == importTable.Columns["GuidColumn"].DataType);
// check rows can be read.
var reader = connection.GetTransformReader(importTable);
var openResult = await reader.Open(0, null, CancellationToken.None);
Assert.True(openResult);
var finished = await reader.ReadAsync();
Assert.True(finished);
Assert.Equal((Int64)1, Int64.Parse(reader["IntColumn"].ToString()));
Assert.Equal("value1", reader["StringColumn"]);
Assert.Equal(new DateTime(2001, 01, 21), DateTime.Parse(reader["DateColumn"].ToString()));
Assert.Equal((decimal)1.1, reader.GetDecimal(reader.GetOrdinal("DecimalColumn")));
Assert.Equal(guid.ToString(), reader["GuidColumn"].ToString());
// test the preview function returns one row.
var previewResult = await connection.GetPreview(importTable, null, CancellationToken.None);
Assert.Single(previewResult.Data);
}
public async Task Unit(Connection connection, string databaseName)
{
await connection.CreateDatabase(databaseName, CancellationToken.None);
var newTable = DataSets.CreateTable();
var table = await connection.InitializeTable(newTable, 1000);
//create the table
await connection.CreateTable(table, true, CancellationToken.None);
//insert a single row
InsertQuery insertQuery = new InsertQuery("test_table", new List <QueryColumn>()
{
new QueryColumn(new TableColumn("IntColumn", ETypeCode.Int32), 1),
new QueryColumn(new TableColumn("StringColumn", ETypeCode.String), "value1"),
new QueryColumn(new TableColumn("DateColumn", ETypeCode.DateTime), new DateTime(2001, 01, 21, 0, 0, 0, DateTimeKind.Utc)),
new QueryColumn(new TableColumn("BooleanColumn", ETypeCode.Boolean), true),
new QueryColumn(new TableColumn("DoubleColumn", ETypeCode.Double), 1.1),
new QueryColumn(new TableColumn("DecimalColumn", ETypeCode.Decimal), 1.1m),
new QueryColumn(new TableColumn("GuidColumn", ETypeCode.Guid), Guid.NewGuid())
});
await connection.ExecuteInsert(table, new List <InsertQuery>() { insertQuery }, CancellationToken.None);
//insert a second row
insertQuery = new InsertQuery("test_table", new List <QueryColumn>()
{
new QueryColumn(new TableColumn("IntColumn", ETypeCode.Int32), 2),
new QueryColumn(new TableColumn("StringColumn", ETypeCode.String), "value2"),
new QueryColumn(new TableColumn("BooleanColumn", ETypeCode.Boolean), false),
new QueryColumn(new TableColumn("DateColumn", ETypeCode.DateTime), new DateTime(2001, 01, 21, 0, 0, 0, DateTimeKind.Utc)),
new QueryColumn(new TableColumn("DoubleColumn", ETypeCode.Double), 1.1),
new QueryColumn(new TableColumn("DecimalColumn", ETypeCode.Decimal), 1.2m),
new QueryColumn(new TableColumn("GuidColumn", ETypeCode.Guid), Guid.NewGuid())
});
await connection.ExecuteInsert(table, new List <InsertQuery>() { insertQuery }, CancellationToken.None);
////if the write was a file. move it back to the incoming directory to read it.
if (connection.DatabaseConnectionCategory == Connection.EConnectionCategory.File)
{
var fileConnection = (ConnectionFlatFile)connection;
var filename = fileConnection.LastWrittenFile;
var filemoveResult = await fileConnection.MoveFile((FlatFile)table, filename,
EFlatFilePath.Outgoing, EFlatFilePath.Incoming);
Assert.True(filemoveResult);
}
SelectQuery selectQuery;
//run a select query with one row, sorted descending.
if (connection.CanFilter)
{
selectQuery = new SelectQuery()
{
Columns = new List <SelectColumn>()
{
new SelectColumn(new TableColumn("StringColumn"))
},
Sorts = new List <Sort>()
{
new Sort {
Column = new TableColumn("IntColumn"), Direction = Sort.EDirection.Descending
}
},
Rows = 1,
Table = "test_table"
};
//should return value2 from second row
var returnScalar = await connection.ExecuteScalar(table, selectQuery, CancellationToken.None);
Assert.NotNull(returnScalar);
//if the connection doesn't support sorting, don't bother with this test.
if (connection.CanSort == true)
{
Assert.Equal("value2", (string)returnScalar);
}
}
if (connection.CanUpdate)
{
//run an update query which will change the second date value to 2001-01-21
var updateQuery = new UpdateQuery()
{
UpdateColumns = new List <QueryColumn>()
{
new QueryColumn(new TableColumn("DateColumn", ETypeCode.DateTime), new DateTime(2001, 01, 21, 0, 0, 0, DateTimeKind.Utc))
},
Filters = new List <Filter>()
{
new Filter()
{
Column1 = new TableColumn("IntColumn"), Operator = Filter.ECompare.IsEqual, Value2 = 2, CompareDataType = ETypeCode.Int32
}
}
};
await connection.ExecuteUpdate(table, new List <UpdateQuery>() { updateQuery }, CancellationToken.None);
//run a select query to validate the updated row.
selectQuery = new SelectQuery()
{
Columns = new List <SelectColumn>()
{
new SelectColumn(new TableColumn("DateColumn", ETypeCode.DateTime))
},
Filters = new List <Filter>()
{
new Filter(new TableColumn("IntColumn"), Filter.ECompare.IsEqual, 2)
},
Rows = 1,
Table = "test_table"
};
//should return updated date
var returnScalar = await connection.ExecuteScalar(table, selectQuery, CancellationToken.None);
Assert.True((DateTime)returnScalar == new DateTime(2001, 01, 21), "DateTime didn't match");
}
//run a simple aggregate query to get max value from decimaColumn
if (connection.CanAggregate)
{
selectQuery = new SelectQuery()
{
Columns = new List <SelectColumn>()
{
new SelectColumn("DecimalColumn", SelectColumn.EAggregate.Max)
},
Sorts = new List <Sort>()
{
new Sort("DateColumn")
},
Groups = new List <TableColumn>()
{
new TableColumn("DateColumn")
},
Rows = 1,
Table = "test_table"
};
//should return value2 from second row
var returnScalar = await connection.ExecuteScalar(table, selectQuery, CancellationToken.None);
Assert.True(Decimal.Compare(Convert.ToDecimal(returnScalar), (Decimal)1.2) == 0, "SelectQuery2 - returned value: " + returnScalar.ToString());
}
if (connection.CanDelete)
{
//run a delete query.
var deleteQuery = new DeleteQuery()
{
Filters = new List <Filter>()
{
new Filter("IntColumn", Filter.ECompare.IsEqual, 1)
},
Table = "test_table"
};
//should return value2 from second row
await connection.ExecuteDelete(table, new List <DeleteQuery>() { deleteQuery }, CancellationToken.None);
//run a select query to check row is deleted
selectQuery = new SelectQuery()
{
Columns = new List <SelectColumn>()
{
new SelectColumn("DateColumn")
},
Filters = new List <Filter>()
{
new Filter("IntColumn", Filter.ECompare.IsEqual, 1)
},
Rows = 1,
Table = "test_table"
};
//should return null
var returnScalar = await connection.ExecuteScalar(table, selectQuery, CancellationToken.None);
Assert.True(returnScalar == null);
//run an aggregate query to check rows left
if (connection.CanAggregate)
{
selectQuery = new SelectQuery()
{
Columns = new List <SelectColumn>()
{
new SelectColumn("IntColumn", SelectColumn.EAggregate.Count)
},
Rows = 1000,
Table = "test_table"
};
returnScalar = await connection.ExecuteScalar(table, selectQuery, CancellationToken.None);
Assert.True(Convert.ToInt64(returnScalar) == 1, "Select count");
}
//run a truncate
await connection.TruncateTable(table, CancellationToken.None);
//check the table is empty following truncate
selectQuery = new SelectQuery()
{
Columns = new List <SelectColumn>()
{
new SelectColumn("StringColumn")
},
Rows = 1,
Table = "test_table"
};
//should return null
returnScalar = await connection.ExecuteScalar(table, selectQuery, CancellationToken.None);
Assert.True(returnScalar == null);
// }
}
if (connection.CanBulkLoad)
{
await connection.TruncateTable(table, CancellationToken.None);
//start a datawriter and insert the test data
await connection.DataWriterStart(table);
var testData = DataSets.CreateTestData();
await connection.ExecuteInsertBulk(table, testData, CancellationToken.None);
await connection.DataWriterFinish(table);
////if the write was a file. move it back to the incoming directory to read it.
if (connection.DatabaseConnectionCategory == Connection.EConnectionCategory.File)
{
var fileConnection = (ConnectionFlatFile)connection;
var filename = fileConnection.LastWrittenFile;
var filemoveResult = await fileConnection.MoveFile((FlatFile)table, filename,
EFlatFilePath.Outgoing, EFlatFilePath.Incoming);
Assert.True(filemoveResult);
}
//check the table loaded 10 rows successully
Transform reader = connection.GetTransformReader(table, true);
int count = 0;
var openResult = await reader.Open(0, null, CancellationToken.None);
Assert.True(openResult, "Open Reader");
while (await reader.ReadAsync())
{
count++;
}
Assert.True(count == 10, "Select count - value :" + count);
}
if (connection.CanFilter == true)
{
//run a lookup query.
var filters = new List <Filter> {
new Filter("IntColumn", Filter.ECompare.IsEqual, 5)
};
var query = new SelectQuery()
{
Filters = filters
};
//should return value5
var reader = connection.GetTransformReader(table, true);
// if (reader.CanLookupRowDirect)
// {
// var openResult = await reader.Open(0, null, CancellationToken.None);
// Assert.True(openResult, "Open Reader");
var returnLookup = await reader.Lookup(query, Transform.EDuplicateStrategy.Abend, CancellationToken.None);
Assert.True(Convert.ToString(returnLookup.First()[0]) == "value5", "LookupValue :" + returnLookup.First()[0]);
//run lookup again with caching set.
reader = connection.GetTransformReader(table);
// var openResult = await reader.Open(0, null, CancellationToken.None);
// Assert.True(openResult, "Open Reader");
reader.SetCacheMethod(Transform.ECacheMethod.PreLoadCache);
returnLookup = await reader.Lookup(query, Transform.EDuplicateStrategy.Abend, CancellationToken.None);
Assert.True(Convert.ToString(returnLookup.First()[0]) == "value5", "Select count - value :" + returnLookup.First()[0]);
// }
reader.Close();
}
}