// Given info about a car, add it to the database using the Context object and a Define
/// <summary>
/// UPSERT a car into our database
/// </summary>
/// <param name="ctxt">The Context for doing database work</param>
/// <param name="year">The year of the car</param>
/// <param name="make">The make of the car</param>
/// <param name="model">The model of the car</param>
/// <returns></returns>
static async Task AddCarAsync(Context ctxt, int year, string make, string model)
{
// The Define class is used to do UPSERTs.
// You pass the table name and primary key value to the constructor.
// No need to create tables, just refer to them and the database takes care of it.
// The second parameter to the Define constructor is the row ID
// This would be a natural primary key, but we just use a GUID to keep things simple
Define define = new Define("cars", Guid.NewGuid().ToString());
// Use Define.Set function to add column data
define.Set("year", year);
define.Set("make", make);
define.Set("model", model);
// Call through the Context to create a Command and do the UPSERT
await ctxt.Cmd.DefineAsync(define);
}
public void TestItemNameValues()
{
using (var ctxt = TestUtils.GetCtxt())
{
int tableId = Tables.GetIdAsync(ctxt, "ape").Result;
int bletNameId = Names.GetIdAsync(ctxt, tableId, "blet").Result;
long monkeyValueId = Values.GetIdAsync(ctxt, "monkey").Result;
long itemId0 = Items.GetIdAsync(ctxt, tableId, monkeyValueId).Result;
long itemId1 = Items.GetIdAsync(ctxt, tableId, monkeyValueId).Result;
Assert.AreEqual(itemId0, itemId1);
Items.DeleteAsync(ctxt, itemId1).ConfigureAwait(false);
long itemId2 = Items.GetIdAsync(ctxt, tableId, monkeyValueId).Result;
Assert.AreNotEqual(itemId0, itemId2);
var metaDict = new Dictionary <int, long>();
int fredNameId = Names.GetIdAsync(ctxt, tableId, "fred").Result;
long earnyValueId = Values.GetIdAsync(ctxt, "earny").Result;
metaDict[fredNameId] = earnyValueId;
Items.SetItemData(ctxt, itemId2, metaDict);
ctxt.ProcessPostOpsAsync().Wait();
var outMetaDict = Items.GetItemDataAsync(ctxt, itemId2).Result;
Assert.AreEqual(1, outMetaDict.Count);
Assert.IsTrue(outMetaDict.ContainsKey(fredNameId));
Assert.AreEqual(earnyValueId, outMetaDict[fredNameId]);
Define define = new Define("apelike", "foo");
define.Set("blet", "monkey").Set("something", "else");
ctxt.Cmd.DefineAsync(define).Wait();
GetRequest get = new GetRequest()
{
table = "apelike"
};
get.values = new List <object> {
"foo"
};
var gotten = ctxt.Cmd.GetAsync(get).Result;
Assert.AreEqual(1, gotten.metadata.Count);
var result = gotten.metadata[0];
Assert.AreEqual("monkey", result["blet"]);
Assert.AreEqual("else", result["something"]);
}
}
public void TestSql()
{
using (var ctxt = TestUtils.GetCtxt())
{
// No tables, nothing should still work.
{
var select = Sql.Parse("SELECT somethin\nFROM nothin");
using (var reader = ctxt.ExecSelectAsync(select).Result)
Assert.IsFalse(reader.Read());
}
// Add a row.
{
var define = new Define("somethin", "foo");
define.Set("blet", "monkey");
ctxt.Cmd.DefineAsync(define).Wait();
}
// Add another row.
{
var define = new Define("somethin", "bar");
define.Set("flub", "snake");
ctxt.Cmd.DefineAsync(define).Wait();
}
// Have a table now, but bogus SELECT column
{
var select = Sql.Parse("SELECT nothin\nFROM somethin");
using (var reader = ctxt.ExecSelectAsync(select).Result)
{
Assert.IsTrue(reader.Read());
Assert.IsTrue(reader.IsDBNull(0));
Assert.IsTrue(reader.Read());
Assert.IsTrue(reader.IsDBNull(0));
Assert.IsFalse(reader.Read());
}
}
// Have a table now, but bogus WHERE column bdfadf
{
var select = Sql.Parse("SELECT nothin\nFROM somethin\nWHERE value = @foo AND bdfadf = @bdfadf");
select.AddParam("@foo", "foo").AddParam("@bdfadf", 12.0);
using (var reader = ctxt.ExecSelectAsync(select).Result)
Assert.IsFalse(reader.Read());
}
// See that it all works
{
var select = Sql.Parse("SELECT blet\nFROM somethin\nWHERE value = @foo");
select.AddParam("@foo", "foo");
using (var reader = ctxt.ExecSelectAsync(select).Result)
{
Assert.IsTrue(reader.Read());
Assert.AreEqual("monkey", reader.GetString(0));
Assert.IsFalse(reader.Read());
}
}
{
var select = Sql.Parse("SELECT flub\nFROM somethin\nWHERE value = @bar");
select.AddParam("@bar", "bar");
using (var reader = ctxt.ExecSelectAsync(select).Result)
{
Assert.IsTrue(reader.Read());
Assert.AreEqual("snake", reader.GetString(0));
Assert.IsFalse(reader.Read());
}
}
}
}
public void TestDefine()
{
using (var ctxt = TestUtils.GetCtxt())
{
{
var define = new Define("fun", "some");
define.Set("num", 42).Set("str", "foobar").Set("multi", "blet\nmonkey");
ctxt.Cmd.DefineAsync(define).Wait();
}
{
var define = new Define("fun", "another");
define.Set("num", 69).Set("str", "boofar").Set("multi", "ape\nagony");
ctxt.Cmd.DefineAsync(define).Wait();
}
{
var define = new Define("fun", "yetsome");
define.Set("num", 19).Set("str", "playful").Set("multi", "balloni\nbeats");
ctxt.Cmd.DefineAsync(define).Wait();
}
long itemId = Items.GetIdAsync(ctxt, Tables.GetIdAsync(ctxt, "fun").Result, Values.GetIdAsync(ctxt, "some").Result).Result;
var itemData = NameValues.GetMetadataValuesAsync(ctxt, Items.GetItemDataAsync(ctxt, itemId).Result).Result;
Assert.AreEqual(42.0, itemData["num"]);
Assert.AreEqual("foobar", itemData["str"]);
Assert.AreEqual("blet\nmonkey", itemData["multi"]);
{
Select select =
Sql.Parse
(
"SELECT value, multi\n" +
$"FROM fun\n" +
"WHERE multi MATCHES @search"
);
select.AddParam("@search", "monkey");
using (var reader = ctxt.ExecSelectAsync(select).Result)
{
if (!reader.ReadAsync().Result)
{
Assert.Fail();
}
var val = reader.GetString(0);
var str = reader.GetString(1);
Assert.AreEqual("some", val);
Assert.AreEqual("blet\nmonkey", str);
if (reader.ReadAsync().Result)
{
Assert.Fail();
}
}
}
{
Define define = new Define("fun", "some");
define.Set("num", 43.0);
define.Set("str", null); // remove the metadata
ctxt.Cmd.DefineAsync(define).Wait();
}
itemData = NameValues.GetMetadataValuesAsync(ctxt, Items.GetItemDataAsync(ctxt, itemId).Result).Result;
Assert.IsTrue(!itemData.ContainsKey("str"));
Assert.AreEqual(43.0, itemData["num"]);
Assert.IsTrue(!itemData.ContainsKey("str"));
ctxt.Cmd.DeleteAsync("fun", new[] { "some", "another", "yetsome" }).Wait();
{
Select select = new Select()
{
from = "fun",
select = new List <string> {
"value"
}
};
using (var reader = ctxt.ExecSelectAsync(select).Result)
{
if (reader.ReadAsync().Result) // should be gone
{
Assert.Fail();
}
}
}
{
Define numsFirst = new Define("numsFirst", 1);
numsFirst.Set("foo", 12);
numsFirst.Set("blet", "79");
ctxt.Cmd.DefineAsync(numsFirst).Wait();
Define numsNext = new Define("numsFirst", 2);
numsNext.Set("foo", 15).Set("blet", "63");
ctxt.Cmd.DefineAsync(numsNext).Wait();
Select select = Sql.Parse("SELECT value, foo, blet\nFROM numsFirst");
using (var reader = ctxt.ExecSelectAsync(select).Result)
{
while (reader.Read())
{
if (reader.GetInt64(0) == 1)
{
Assert.AreEqual(12, reader.GetDouble(1));
Assert.AreEqual("79", reader.GetString(2));
}
else if (reader.GetInt64(0) == 2)
{
Assert.AreEqual(15, reader.GetDouble(1));
Assert.AreEqual("63", reader.GetString(2));
}
else
{
Assert.Fail();
}
}
}
}
}
}
