private static void RoundTrip(string columnName, int[] array, int batchSize = 128)
{
XDatabaseContext context = new XDatabaseContext();
string columnPath = Path.Combine("VariableIntegerReaderWriterTests", columnName);
string columnPrefix = Path.Combine(columnPath, "Vl");
context.StreamProvider.Delete(columnPath);
Directory.CreateDirectory(columnPath);
XArray values = XArray.All(array, array.Length);
using (IColumnWriter writer = new VariableIntegerWriter(context.StreamProvider, columnPrefix))
{
ArraySelector page = ArraySelector.All(0).NextPage(array.Length, batchSize);
while (page.Count > 0)
{
writer.Append(values.Reselect(page));
page = page.NextPage(array.Length, batchSize);
}
}
XArray returned = default(XArray);
using (IColumnReader reader = new VariableIntegerReader(context.StreamProvider, columnPrefix, CachingOption.AsConfigured))
{
returned = reader.Read(ArraySelector.All(array.Length));
}
TableTestHarness.AssertAreEqual(values, returned, array.Length);
context.StreamProvider.Delete(columnPath);
}
public static void RunAndCompare(XArray input, string inputColumnName, XArray expected, string outputColumnName, string queryText)
{
XDatabaseContext context = new XDatabaseContext
{
RequestedAsOfDateTime = TestAsOfDateTime
};
IXTable query = TableTestHarness.DatabaseContext.FromArrays(input.Count)
.WithColumn(new ColumnDetails(inputColumnName, input.Array.GetType().GetElementType()), input)
.Query(queryText, context);
Func <XArray> resultGetter = query.Columns.Find(outputColumnName).CurrentGetter();
int pageCount;
// Get one row only and verify
pageCount = query.Next(1);
TableTestHarness.AssertAreEqual(TableTestHarness.Slice(expected, 0, 1), resultGetter(), pageCount);
// Get another (ensure values set again)
pageCount = query.Next(1);
TableTestHarness.AssertAreEqual(TableTestHarness.Slice(expected, 1, 2), resultGetter(), pageCount);
// Get the rest (ensure arrays expanded when needed)
pageCount = query.Next(expected.Count - 2);
Assert.AreEqual(expected.Count - 2, pageCount);
TableTestHarness.AssertAreEqual(TableTestHarness.Slice(expected, 2, expected.Count), resultGetter(), pageCount);
// Reset and ask for all of them at once
query.Reset();
pageCount = query.Next(expected.Count + 1);
Assert.AreEqual(expected.Count, pageCount);
TableTestHarness.AssertAreEqual(expected, resultGetter(), pageCount);
}
public void Function_Coalesce()
{
int[] ageValues = { 50, 23, 42, 0, -99, 0 };
int[] salaryValues = { 5, 0, 12, -3, 13, 0 };
int[] siblingValues = { -8, 0, 0, 1, 0, 2 };
bool[] ageNullRows = { false, false, false, true, true, true };
bool[] salaryNullRows = { false, true, false, true, false, true };
bool[] siblingNullRows = { true, true, true, false, true, false };
XArray ages = XArray.All(ageValues, ageValues.Length, ageNullRows);
XArray salaries = XArray.All(salaryValues, salaryValues.Length, salaryNullRows);
XArray siblings = XArray.All(siblingValues, siblingValues.Length, siblingNullRows);
int[] expectedValues = { 50, 23, 42, 1, 13, 2 };
IXTable expected = TableTestHarness.DatabaseContext.FromArrays(ageValues.Length)
.WithColumn("Coalesce", expectedValues);
IXTable resultTable = TableTestHarness.DatabaseContext.FromArrays(ageValues.Length)
.WithColumn(new ColumnDetails("Age", typeof(int)), ages)
.WithColumn(new ColumnDetails("Salary", typeof(int)), salaries)
.WithColumn(new ColumnDetails("Siblings", typeof(int)), siblings)
.Query("select Coalesce([Age], [Salary], [Siblings])", TableTestHarness.DatabaseContext);
TableTestHarness.AssertAreEqual(expected, resultTable, 2);
}
public void Verb_Choose()
{
XDatabaseContext context = new XDatabaseContext();
int[] rankPattern = new int[] { 2, 3, 1 };
// Build three arrays
int distinctCount = 100;
int length = 3 * distinctCount;
int[] id = new int[length];
int[] rank = new int[length];
int[] value = new int[length];
for (int i = 0; i < length; ++i)
{
// ID is the same for three rows at a time
id[i] = i / 3;
// Rank is [2, 3, 1] repeating (so the middle is the biggest)
rank[i] = rankPattern[i % 3];
// Value is the index of the real row
value[i] = i;
}
// Build the expected results - one for each distinct ID, each with max rank and from the right row
int[] expectedIds = new int[distinctCount];
int[] expectedRanks = new int[distinctCount];
int[] expectedValues = new int[distinctCount];
for (int i = 0; i < distinctCount; ++i)
{
expectedIds[i] = i;
expectedRanks[i] = 3;
expectedValues[i] = 3 * i + 1;
}
IXTable expected = TableTestHarness.DatabaseContext.FromArrays(distinctCount)
.WithColumn("ID", expectedIds)
.WithColumn("Rank", expectedRanks)
.WithColumn("Value", expectedValues);
IXTable actual = TableTestHarness.DatabaseContext.FromArrays(length)
.WithColumn("ID", id)
.WithColumn("Rank", rank)
.WithColumn("Value", value);
// Run and compare (as integer)
TableTestHarness.AssertAreEqual(expected, actual.Query("choose Max [Rank] [ID]", context), distinctCount / 3);
// Run and compare (as String8)
TableTestHarness.AssertAreEqual(
expected.Query("select Cast([ID], String8), Cast([Rank], String8), Cast([Value], String8)", context),
actual.Query("select Cast([ID], String8), Cast([Rank], String8), Cast([Value], String8)", context).Query("choose Max [Rank] [ID]", context),
distinctCount);
}
private static void RunCastConversions(Type type, int[] values)
{
// Convert to type and back to int
RunQueryAndVerify(values, "Value", values, "Value", $"select Cast(Cast([Value], {type.Name}), Int32)");
// Convert to string and back to int
TableTestHarness.AssertAreEqual(
TableTestHarness.DatabaseContext.FromArrays(values.Length).WithColumn("Value", values).Query($"select Cast(Cast(Cast([Value], String8), {type.Name}), Int32)", TableTestHarness.DatabaseContext),
TableTestHarness.DatabaseContext.FromArrays(values.Length).WithColumn("Value", values), 10);
}
public void Verb_Skip()
{
// Verify skipping the first 100 rows of 0-1000 produces 100-1000
IXTable expected = TableTestHarness.DatabaseContext.FromArrays(900)
.WithColumn("Value", Enumerable.Range(100, 900).ToArray <int>());
IXTable actual = TableTestHarness.DatabaseContext.FromArrays(1000)
.WithColumn("Value", Enumerable.Range(0, 1000).ToArray <int>())
.Query(@"
skip 100", TableTestHarness.DatabaseContext);
TableTestHarness.AssertAreEqual(expected, actual, 25);
}
private static void RoundTrip<T>(string tableName, T[] array)
{
XDatabaseContext context = new XDatabaseContext();
context.StreamProvider.Delete($"Table\\{tableName}");
IXTable expected = context
.FromArrays(array.Length)
.WithColumn("Column", array);
expected.Save(tableName, context);
using (IXTable actual = context.Load(tableName))
{
TableTestHarness.AssertAreEqual(expected, actual, 1024);
}
}
public void Verb_Schema()
{
int[] id = new int[0];
ushort[] size = new ushort[0];
string[] name = new string[] { "ID", "Size" };
string[] types = new string[] { "Int32", "UInt16" };
IXTable actual = TableTestHarness.DatabaseContext.FromArrays(0)
.WithColumn("ID", id)
.WithColumn("Size", size)
.Query("schema", TableTestHarness.DatabaseContext);
IXTable expected = TableTestHarness.DatabaseContext.FromArrays(2)
.WithColumn("Name", name)
.WithColumn("Type", types);
TableTestHarness.AssertAreEqual(expected, actual, 2);
}
public static void RunQueryAndVerify(XArray inputXArray, string inputColumnName, XArray expectedXArray, string outputColumnName, string queryText)
{
Assert.IsTrue(expectedXArray.Count > 3, "Must have at least four values for a proper test.");
// Run with full array arrays
RunAndCompare(inputXArray, inputColumnName, expectedXArray, outputColumnName, queryText);
// Add indices and gaps and verify against original set
RunAndCompare(TableTestHarness.Pad(inputXArray), inputColumnName, expectedXArray, outputColumnName, queryText);
if (!inputXArray.HasNulls)
{
// Add alternating nulls and verify alternating null/expected
RunAndCompare(TableTestHarness.Nulls(inputXArray), inputColumnName, TableTestHarness.Nulls(expectedXArray), outputColumnName, queryText);
}
// Test a single value by itself
RunAndCompare(TableTestHarness.First(inputXArray), inputColumnName, TableTestHarness.First(expectedXArray), outputColumnName, queryText);
}
public static void RunQueryAndVerify(XArray left, XArray right, XArray expected, string queryText)
{
Assert.IsTrue(expected.Count > 3, "Must have at least four values for a proper test.");
// Run with full array arrays
RunAndCompare(left, right, expected, queryText);
// Add indices and gaps and verify against original set
RunAndCompare(TableTestHarness.Pad(left), TableTestHarness.Pad(right), expected, queryText);
if (!left.HasNulls)
{
// Add alternating nulls and verify alternating null/expected
RunAndCompare(TableTestHarness.Nulls(left), TableTestHarness.Nulls(right), TableTestHarness.Nulls(expected), queryText);
}
// Test a single value by itself
RunAndCompare(TableTestHarness.First(left), TableTestHarness.First(right), TableTestHarness.First(expected), queryText);
}
private static void RunJoinAndVerify(Array joinTo, Array joinFrom, Array expected)
{
Type t = joinTo.GetType().GetElementType();
// Build a table with padded nulls to join from (so we see nulls are also filtered out)
XArray joinFromxarray = TableTestHarness.Nulls(XArray.All(joinFrom));
IXTable joinFromTable = TableTestHarness.DatabaseContext.FromArrays(joinFromxarray.Count)
.WithColumn(new ColumnDetails("ServerID", t), joinFromxarray);
// Build the table to join to
IXTable joinToTable = TableTestHarness.DatabaseContext.FromArrays(joinTo.Length)
.WithColumn(new ColumnDetails("ID", t), XArray.All(joinTo));
// Run the join - verify the expected values without padding are found
IXTable result = new Join(joinFromTable, "ServerID", joinToTable, "ID", "Server.");
Func <XArray> serverID = result.Columns.Find("Server.ID").CurrentGetter();
IXTable expectedTable = TableTestHarness.DatabaseContext.FromArrays(expected.Length).WithColumn("Server.ID", expected);
TableTestHarness.AssertAreEqual(expectedTable, result, 2);
}
public void Verb_Peek()
{
String8Block block = new String8Block();
int[] values = BuildPeekSample();
IXTable expected = TableTestHarness.DatabaseContext.FromArrays(3)
.WithColumn("Value", new int[] { 0, 1, 2 })
.WithColumn("Count", new int[] { 500, 250, 150 })
.WithColumn("Percentage", TableTestHarness.ToString8(new string[]
{
PercentageAggregator.TwoSigFigs(500, 1000),
PercentageAggregator.TwoSigFigs(250, 1000),
PercentageAggregator.TwoSigFigs(150, 1000)
}));
IXTable actual = TableTestHarness.DatabaseContext.FromArrays(values.Length)
.WithColumn("Value", values)
.Query("peek [Value]", TableTestHarness.DatabaseContext);
TableTestHarness.AssertAreEqual(expected, actual, 2);
}
