/// <summary>
/// Drop a table from the database schema
/// </summary>
/// <param name="table">Name of table to drop</param>
public async Task DropAsync(string table)
{
var totalTimer = ScopeTiming.StartTiming();
try
{
NameValues.ClearCaches();
int tableId = await Tables.GetIdAsync(Ctxt, table, noCreate : true, noException : true).ConfigureAwait(false);
if (tableId < 0)
{
return;
}
await Ctxt.Db.ExecuteSqlAsync($"DELETE FROM itemnamevalues WHERE nameid IN (SELECT id FROM names WHERE tableid = {tableId})").ConfigureAwait(false);
await Ctxt.Db.ExecuteSqlAsync($"DELETE FROM names WHERE tableid = {tableId}").ConfigureAwait(false);
await Ctxt.Db.ExecuteSqlAsync($"DELETE FROM items WHERE tableid = {tableId}").ConfigureAwait(false);
await Ctxt.Db.ExecuteSqlAsync($"DELETE FROM tables WHERE id = {tableId}").ConfigureAwait(false);
NameValues.ClearCaches();
}
finally
{
ScopeTiming.RecordScope("Cmd.Drop", totalTimer);
}
}
/// <summary>
/// Reset the metastrings database
/// Only used internally for testing, should not be used in a production environment
/// </summary>
/// <param name="reset">Reset request object</param>
public void Reset(bool includeNameValues = false)
{
if (includeNameValues)
{
NameValues.Reset(Ctxt);
}
else
{
Items.Reset(Ctxt);
}
NameValues.ClearCaches();
}
public static Context GetCtxt() // start from scratch
{
string dbFilePath = Context.DbConnStrToFilePath("Data Source=[UserRoaming]/metastrings-tests.db");
if (File.Exists(dbFilePath))
{
File.Delete(dbFilePath);
}
var ctxt = new Context("Data Source=" + dbFilePath);
NameValues.Reset(ctxt);
return(ctxt);
}
/// <summary>
/// Query for the metadata for a set of items.
/// </summary>
/// <param name="request">NoSQL query for items to get</param>
/// <returns>Metadata of found items</returns>
public async Task <GetResponse> QueryGetAsync(QueryGetRequest request)
{
var totalTimer = ScopeTiming.StartTiming();
try
{
var itemValues = new Dictionary <long, object>();
{
Select select = new Select();
select.select = new List <string> {
"id", "value"
};
select.from = request.from;
select.where = request.where;
select.orderBy = request.orderBy;
select.limit = request.limit;
select.cmdParams = request.cmdParams;
using (var reader = await Ctxt.ExecSelectAsync(select).ConfigureAwait(false))
{
while (await reader.ReadAsync().ConfigureAwait(false))
{
itemValues.Add(reader.GetInt64(0), reader.GetValue(1));
}
}
}
var responses = new List <Dictionary <string, object> >(itemValues.Count);
foreach (var itemId in itemValues.Keys)
{
var metaIds = await Items.GetItemDataAsync(Ctxt, itemId).ConfigureAwait(false);
var metaStrings = await NameValues.GetMetadataValuesAsync(Ctxt, metaIds).ConfigureAwait(false);
metaStrings["id"] = (double)itemId;
metaStrings["value"] = itemValues[itemId];
responses.Add(metaStrings);
}
GetResponse response = new GetResponse()
{
metadata = responses
};
return(response);
}
finally
{
ScopeTiming.RecordScope("Cmd.QueryGet", totalTimer);
}
}
/// <summary>
/// Internal use, get a summary of a text of an item and its metadata
/// </summary>
/// <param name="ctxt">Database connection</param>
/// <param name="itemId">The item to summarize</param>
/// <returns>Summary of item</returns>
public static async Task <string> SummarizeItemAsync(Context ctxt, long itemId)
{
var sb = new StringBuilder();
sb.AppendLine($"Item: {itemId}");
int tableId =
Utils.ConvertDbInt32(await ctxt.Db.ExecuteScalarAsync($"SELECT tableid FROM items WHERE id = {itemId}"));
if (tableId < 0)
{
throw new MetaStringsException("Item not found: " + itemId);
}
string tableName = (await Tables.GetTableAsync(ctxt, tableId)).name;
sb.AppendLine($"Table: {tableName} ({tableId})");
long valueId =
Utils.ConvertDbInt64(await ctxt.Db.ExecuteScalarAsync($"SELECT valueid FROM items WHERE id = {itemId}"));
object value = await Values.GetValueAsync(ctxt, valueId);
sb.AppendLine($"Value: {value} ({valueId})\n");
sb.AppendLine("Metadata:");
var metadata = await NameValues.GetMetadataValuesAsync(ctxt, GetItemDataAsync(ctxt, itemId).Result);
foreach (var kvp in metadata)
{
sb.AppendLine($"{kvp.Key}: {kvp.Value}");
}
sb.AppendLine("Long Strings:");
using (var reader = await ctxt.Db.ExecuteReaderAsync($"SELECT name, longstring FROM longstrings WHERE itemid = {itemId}"))
{
while (await reader.ReadAsync())
{
sb.AppendLine($"{reader.GetString(0)}:\n{reader.GetString(1)}\n");
}
}
return(sb.ToString());
}
/// <summary>
/// Get the metadata for a set of items
/// </summary>
/// <param name="request">List of values to get metadata for</param>
/// <returns>Metadata for the items</returns>
public async Task <GetResponse> GetAsync(GetRequest request)
{
var totalTimer = ScopeTiming.StartTiming();
try
{
var responses = new List <Dictionary <string, object> >(request.values.Count);
int tableId = await Tables.GetIdAsync(Ctxt, request.table, noCreate : true).ConfigureAwait(false);
foreach (var value in request.values)
{
long valueId = await Values.GetIdAsync(Ctxt, value).ConfigureAwait(false);
long itemId = await Items.GetIdAsync(Ctxt, tableId, valueId, noCreate : true).ConfigureAwait(false);
if (itemId < 0)
{
responses.Add(null);
continue;
}
var metaIds = await Items.GetItemDataAsync(Ctxt, itemId).ConfigureAwait(false);
var metaStrings = await NameValues.GetMetadataValuesAsync(Ctxt, metaIds).ConfigureAwait(false);
responses.Add(metaStrings);
}
GetResponse response = new GetResponse()
{
metadata = responses
};
return(response);
}
finally
{
ScopeTiming.RecordScope("Cmd.Get", totalTimer);
}
}
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();
}
}
}
}
}
}