private void ITable_CustomColumn(Func <ITable> factoryMethod, Action <ITable> addCustomColumnMethod)
{
ITable table = factoryMethod();
AddSampleData(table);
addCustomColumnMethod(table);
SelectQuery query = new SelectQuery();
query.Columns = new string[] { "ID", "Priority", "Color" };
query.Count = ushort.MaxValue;
query.OrderByColumn = "ID";
SelectResult result = table.Query(query);
Assert.AreEqual("None;Green;Red;Green;Green", result.Values.GetColumn(2).Join(";"));
// Change a priority of each bug + 1
DataBlock items = new DataBlock(new string[] { "ID", "Priority" }, (int)result.Total);
for (int row = 0; row < result.Total; ++row)
{
items.SetRow(row, new object[] { (int)result.Values[row, 0], (short)result.Values[row, 1] + 1 });
}
table.AddOrUpdate(items, new AddOrUpdateOptions());
SelectQuery newQuery = new SelectQuery();
newQuery.Columns = new string[] { "ID", "Priority", "Color" };
newQuery.Count = ushort.MaxValue;
newQuery.OrderByColumn = "ID";
SelectResult newResult = table.Query(query);
Assert.AreEqual("Red;None;Blue;None;None", newResult.Values.GetColumn(2).Join(";"));
}
private void ITable_ColumnManagement(Func <ITable> factoryMethod)
{
ITable table = factoryMethod();
AddSampleData(table);
// Add a new column with a non-null default
string newColumnName = "Area Path";
string newColumnInitialDefault = "5";
table.AddColumn(new ColumnDetails(newColumnName, "string", newColumnInitialDefault));
// Verify all rows have the new default
SelectQuery query = new SelectQuery(new string[] { "ID", newColumnName }, "");
SelectResult result = table.Query(query);
Assert.AreEqual(5, (int)result.Total);
for (int i = 0; i < result.Values.RowCount; ++i)
{
Assert.AreEqual(newColumnInitialDefault, result.Values[i, 1].ToString());
}
// Change the column type (string to ushort)
ushort newColumnSecondDefault = 10;
table.AlterColumn(new ColumnDetails(newColumnName, "ushort", newColumnSecondDefault));
// Verify existing values have been re-typed
result = table.Query(query);
Assert.AreEqual(5, (int)result.Total);
for (int i = 0; i < result.Values.RowCount; ++i)
{
Assert.AreEqual((ushort)5, result.Values[i, 1]);
}
// Verify column knows the new default
DataBlock items = new DataBlock(new string[] { "ID" }, 1);
items[0, 0] = 12345;
table.AddOrUpdate(items, new AddOrUpdateOptions());
query = new SelectQuery(query.Columns, "ID = 12345");
result = table.Query(query);
Assert.AreEqual(1, (int)result.Total);
Assert.AreEqual(newColumnSecondDefault, result.Values[0, 1]);
// Delete the column
table.RemoveColumn(newColumnName);
foreach (ColumnDetails cd in table.ColumnDetails)
{
if (cd.Name.Equals(newColumnName))
{
Assert.Fail("Column not removed after RemoveColumn() called.");
}
}
}
internal static void AddSampleData(ITable table)
{
// Add the columns (if not already added)
AddColumns(table);
// Add some sample data (with ID column NOT first)
DataBlock items = BuildSampleData();
table.AddOrUpdate(items, new AddOrUpdateOptions());
}
internal static void BuildLargeSampleData(ITable table)
{
const int limit = 100000;
var seed = Enumerable.Range(0, limit);
// Define desired columns
table.AddColumn(new ColumnDetails("ID", "int", -1, "i", true));
table.AddColumn(new ColumnDetails("AllOnes", "int", 1, "ao", false));
table.AddColumn(new ColumnDetails("AllEvens", "short", 0, "even", false));
DataBlock items = new DataBlock(new string[] { "ID", "AllOnes", "AllEvens" }, limit);
items.SetColumn(0, seed.ToArray());
items.SetColumn(1, seed.Select(i => 1).ToArray());
items.SetColumn(2, seed.Select(i => i % 2).ToArray());
table.AddOrUpdate(items, new AddOrUpdateOptions());
}
private void ITable_AddAndUpdateInSameOperation(Func <ITable> factoryMethod)
{
ITable table = factoryMethod();
AddSampleData(table);
DataBlock items = new DataBlock(new string[] { "Priority", "Title", "ID", "IsDuplicate", "ActiveTime" }, 4);
// Update of an existing item
items.SetRow(0, new object[] { 5, "Modified Existing", 11512, false, 3 });
// Add a new item
items.SetRow(1, new object[] { 1, "Newly Added Item", 99998, false, 3 });
// Add and modify an item in the same operation
items.SetRow(2, new object[] { 1, "Newly Added - should be modified", 99999, false, 3 });
items.SetRow(3, new object[] { 2, "Modified Added", 99999, false, 3 });
table.AddOrUpdate(items, new AddOrUpdateOptions());
SelectQuery query = new SelectQuery();
query.Columns = new string[] { "ID", "Priority", "Title" };
query.Count = ushort.MaxValue;
query.OrderByColumn = "ID";
SelectResult result = table.Query(query);
Assert.AreEqual(7, (int)result.Total);
Assert.AreEqual("11512", result.Values[0, 0].ToString());
Assert.AreEqual("5", result.Values[0, 1].ToString());
Assert.AreEqual("Modified Existing", result.Values[0, 2].ToString());
Assert.AreEqual("99998", result.Values[5, 0].ToString());
Assert.AreEqual("1", result.Values[5, 1].ToString());
Assert.AreEqual("Newly Added Item", result.Values[5, 2].ToString());
Assert.AreEqual("99999", result.Values[6, 0].ToString());
Assert.AreEqual("2", result.Values[6, 1].ToString());
Assert.AreEqual("Modified Added", result.Values[6, 2].ToString());
}
public void ITable_Basic(Func <ITable> factoryMethod)
{
// Define columns and add sample data
ITable table = factoryMethod();
AddSampleData(table);
ExecutionDetails details = new ExecutionDetails();
table.VerifyConsistency(VerificationLevel.Full, details);
Assert.IsTrue(details.Succeeded);
SelectQuery query = new SelectQuery();
SelectResult result;
// Select top 3 bugs with Priority = 3 and ID <= 12000, order by ID
query.Columns = new string[] { "ID", "Priority" };
query.Count = 3;
query.Where = SelectQuery.ParseWhere("Priority = 3 AND [ID] <= 12000");
result = table.Query(query);
Assert.AreEqual(2, (int)result.Total);
Assert.AreEqual("11999", result.Values[0, 0].ToString());
Assert.AreEqual("11643", result.Values[1, 0].ToString());
// Select top 3 bugs with Priority = 3 and ID <= 12000, order by IsDuplicate (a bool without Sort information)
query.OrderByColumn = "IsDuplicate";
result = table.Query(query);
Assert.AreEqual(2, (int)result.Total);
// Ask for only one item; verify one returned, total still correct
query.Count = 1;
query.OrderByColumn = "ID";
query.OrderByDescending = true;
result = table.Query(query);
Assert.AreEqual(2, (int)result.Total);
Assert.AreEqual(1, (int)result.CountReturned);
Assert.AreEqual(1, (int)result.Values.RowCount);
Assert.AreEqual("11999", result.Values[0, 0].ToString());
query.Count = 3;
// Select a word in first item (cover a simple search and zero-LID handling)
SelectQuery q2 = new SelectQuery();
q2.Columns = new string[] { "ID" };
q2.Count = 100;
q2.Where = SelectQuery.ParseWhere("Title:One");
SelectResult r2 = table.Query(q2);
Assert.AreEqual(1, (int)r2.Total);
Assert.AreEqual("11512", r2.Values[0, 0].ToString());
// Select not empty string
SelectQuery q3 = new SelectQuery(new string[] { "ID" }, "Title != \"\"");
q3.Count = 100;
SelectResult r3 = table.Query(q3);
Assert.AreEqual(5, (int)r3.Total);
// Update an item and verify it
DataBlock updateItems = new DataBlock(new string[] { "ID", "Priority" }, 1);
updateItems.SetRow(0, new object[] { 11643, 2 });
table.AddOrUpdate(updateItems, new AddOrUpdateOptions());
result = table.Query(query);
Assert.AreEqual(1, (int)result.Total);
Assert.AreEqual("11999", result.Values[0, 0].ToString());
// Delete an item and verify it
DeleteResult d1 = table.Delete(SelectQuery.ParseWhere("ID = 11999 OR ID = 11512"));
Assert.AreEqual(2, (int)d1.Count);
result = table.Query(query);
Assert.AreEqual(0, (int)result.Total);
query.Where = SelectQuery.ParseWhere("ID > 0");
result = table.Query(query);
Assert.AreEqual(3, (int)result.Total);
details = new ExecutionDetails();
table.VerifyConsistency(VerificationLevel.Full, details);
Assert.IsTrue(details.Succeeded);
}