/// <summary> /// Get the object value from the given table and items table ID /// </summary> /// <param name="table">Table to look in</param> /// <param name="id">Row ID to look for</param> /// <returns>object value if found, null otherwise</returns> public async Task <object> GetRowValueAsync(string table, long id) { Utils.ValidateTableName(table, "GetRowValueAsync"); Select select = Sql.Parse($"SELECT value FROM {table} WHERE id = @id"); select.AddParam("@id", id); object val = await ExecScalarAsync(select).ConfigureAwait(false); return(val); }
/// <summary> /// Get the items table row ID for a given table and key /// </summary> /// <param name="tableName">Table to look in</param> /// <param name="key">Key of the item in the table</param> /// <returns>Row ID, or -1 if not found</returns> public async Task <long> GetRowIdAsync(string tableName, object key) { Utils.ValidateTableName(tableName, "GetRowId"); Select select = Sql.Parse($"SELECT id FROM {tableName} WHERE value = @value"); select.AddParam("@value", key); long id = await ExecScalar64Async(select).ConfigureAwait(false); return(id); }
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."); } }
static async Task Main() { Console.WriteLine("Enter your SQL on one or more lines, then answer any param values"); while (true) { try { Console.WriteLine(); Console.Write("> "); string query = Console.ReadLine(); if (string.IsNullOrWhiteSpace(query)) { continue; } string nextLine; while ((nextLine = Console.ReadLine()).Length > 0) { query += "\n" + nextLine; } Select select = Sql.Parse(query); List <string> paramNames = Utils.ExtractParamNames(query); if (paramNames.Count > 0) { foreach (string paramName in paramNames) { Console.Write($"{paramName}: "); string paramValue = Console.ReadLine(); double numValue; if (double.TryParse(paramValue, out numValue)) { select.AddParam(paramName, numValue); } else { select.AddParam(paramName, paramValue.Trim('\"')); } } Console.WriteLine(); } int resultCount = 0; using (var ctxt = new Context("metaq.db")) { string sql = await Sql.GenerateSqlAsync(ctxt, select); if (sm_sw != null) { sm_sw.Dispose(); } sm_sw = new StreamWriter("metaq.log"); sm_sw.WriteLine(query); sm_sw.WriteLine(); sm_sw.WriteLine("==="); sm_sw.WriteLine(); sm_sw.WriteLine(sql); sm_sw.WriteLine(); sm_sw.WriteLine("==="); sm_sw.WriteLine(); using (var reader = await ctxt.Db.ExecuteReaderAsync(sql, select.cmdParams)) { while (reader.Read()) { for (int i = 0; i < reader.FieldCount; ++i) { WriteLine($"{reader.GetName(i)}: { reader.GetValue(i)}"); } WriteLine("==="); ++resultCount; } } } WriteLine($"Results: {resultCount}"); sm_sw.Dispose(); sm_sw = null; } catch (SqlException exp) { WriteLine($"SQL EXCEPTION: {exp.Message}"); } catch (MetaStringsException exp) { WriteLine($"metastrings EXCEPTION: {exp.Message}"); } catch (Exception exp) { WriteLine($"Unhandled EXCEPTION: {exp}"); } } // loop forever }