/// <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
}
