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."); } }