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();
}
}
}
}
}
}
static async Task Main()
{
// metastrings is built on SQLite, so to create a metastrings database
// we simply need to specify the location for the database file.
// If the file does not exist, an empty database is automatically created.
// The Context class manages the SQLite database connection,
// provides many useful functions for executing SELECT queries,
// and provides access to the Command class for UPSERT, DELETE, and DROP.
using (var ctxt = new Context("cars.db"))
{
// Pass our Context into AddCarAsync to add database records...so many cars...
Console.WriteLine("Adding cars...");
await AddCarAsync(ctxt, 1982, "Chrysler", "LeBaron");
await AddCarAsync(ctxt, 1983, "Toyota", "Tercel");
await AddCarAsync(ctxt, 1998, "Toyota", "Tacoma");
await AddCarAsync(ctxt, 2001, "Nissan", "Xterra");
await AddCarAsync(ctxt, 1987, "Nissan", "Pathfinder");
//...
// Select data out of the database using a basic dialect of SQL
// Restrictions:
// 1. No JOINs
// 2. WHERE criteria must use parameters
// 3. ORDER BY colums must be in SELECT column list
// Here we gather the "value" pseudo-column, the row ID created by the AddCarAsync function
// We create a Select object with our SELECT query,
// pass in the value for the @year parameter,
// and use the Context.ExecSelectAsync function to execute the query.
Console.WriteLine("Getting old cars...");
var oldCarGuids = new List <string>();
Select select =
Sql.Parse
(
"SELECT value, year, make, model " +
"FROM cars " +
"WHERE year < @year " +
"ORDER BY year ASC"
);
select.AddParam("@year", 1990);
using (var reader = await ctxt.ExecSelectAsync(select))
{
// The reader handed back is a System.Data.Common.DbDataReader,
// straight out of SQLite.
// But oh what SQL...
while (reader.Read())
{
// Collecting the row ID GUID that AddCarAsync added.
oldCarGuids.Add(reader.GetString(0));
// metastrings values are either numbers (doubles) or strings
Console.WriteLine
(
reader.GetDouble(1) + ": " +
reader.GetString(2) + " - " +
reader.GetString(3)
);
}
}
// We use the list of row IDs to delete some rows.
// Here we call through the Context to create a Command object to do the DELETE.
Console.WriteLine("Deleting old cars...");
await ctxt.Cmd.DeleteAsync("cars", oldCarGuids);
// Drop the table to keep things clean for the next run.
// We call through the Context to get a Command to do the DROP.
Console.WriteLine("Cleaning up...");
await ctxt.Cmd.DropAsync("cars");
Console.WriteLine("All done.");
}
}
