/// <summary>
/// Given metadata name-value IDs, return name-value string->object values
/// </summary>
/// <param name="ctxt">Database connection</param>
/// <param name="ids">name=>value IDs</param>
/// <returns></returns>
public static async Task <Dictionary <string, object> > GetMetadataValuesAsync(Context ctxt, Dictionary <int, long> ids)
{
var totalTimer = ScopeTiming.StartTiming();
try
{
var retVal = new Dictionary <string, object>(ids.Count);
if (ids.Count == 0)
{
return(retVal);
}
await Names.CacheNamesAsync(ctxt, ids.Keys).ConfigureAwait(false);
await Values.CacheValuesAsync(ctxt, ids.Values).ConfigureAwait(false);
foreach (var kvp in ids)
{
NameObj name = await Names.GetNameAsync(ctxt, kvp.Key).ConfigureAwait(false);
object value = await Values.GetValueAsync(ctxt, kvp.Value).ConfigureAwait(false);
retVal.Add(name.name, value);
}
return(retVal);
}
finally
{
ScopeTiming.RecordScope("NameValues.GetMetadataValuesAsync", totalTimer);
}
}
private static async Task <long> GetIdSelectAsync(Context ctxt, object value)
{
var localTimer = ScopeTiming.StartTiming();
if (value is string)
{
string strValue = (string)value;
Dictionary <string, object> cmdParams = new Dictionary <string, object>();
cmdParams.Add("@stringValue", strValue);
string selectSql =
"SELECT id FROM bvalues WHERE isNumeric = 0 AND stringValue = @stringValue";
long id = Utils.ConvertDbInt64(await ctxt.Db.ExecuteScalarAsync(selectSql, cmdParams).ConfigureAwait(false));
ScopeTiming.RecordScope($"Values.GetId.SELECT(string)", localTimer);
return(id);
}
else
{
double numberValue = Convert.ToDouble(value);
Dictionary <string, object> cmdParams = new Dictionary <string, object>();
cmdParams.Add("@numberValue", numberValue);
string selectSql =
"SELECT id FROM bvalues WHERE isNumeric = 1 AND numberValue = @numberValue";
long id = Utils.ConvertDbInt64(await ctxt.Db.ExecuteScalarAsync(selectSql, cmdParams).ConfigureAwait(false));
ScopeTiming.RecordScope("Values.GetId.SELECT(number)", localTimer);
return(id);
}
}
/// <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>
/// Process a delete request.
/// </summary>
/// <param name="toDelete">Delete reqeuest</param>
public async Task DeleteAsync(Delete toDelete)
{
var totalTimer = ScopeTiming.StartTiming();
try
{
int tableId = await Tables.GetIdAsync(Ctxt, toDelete.table, noCreate : true, noException : true).ConfigureAwait(false);
if (tableId < 0)
{
return;
}
foreach (var val in toDelete.values)
{
long valueId = await Values.GetIdAsync(Ctxt, val).ConfigureAwait(false);
string sql = $"DELETE FROM items WHERE valueid = {valueId} AND tableid = {tableId}";
Ctxt.AddPostOp(sql);
}
await Ctxt.ProcessPostOpsAsync().ConfigureAwait(false);
}
finally
{
ScopeTiming.RecordScope("Cmd.Delete", totalTimer);
}
}
/// <summary>
/// Process queries that piled up by Command's Define function
/// This is the rare case where using a transaction is well-advised
/// </summary>
public async Task ProcessPostOpsAsync()
{
if (m_postItemOps == null || m_postItemOps.Count == 0)
{
return;
}
var totalTimer = ScopeTiming.StartTiming();
try
{
using (var msTrans = BeginTrans())
{
foreach (string sql in m_postItemOps)
{
await Db.ExecuteSqlAsync(sql).ConfigureAwait(false);
}
msTrans.Commit();
}
}
finally
{
m_postItemOps.Clear();
ScopeTiming.RecordScope("ProcessItemPostOps", totalTimer);
}
}
/// <summary>
/// Given a value, get the row ID in the MySQL bvalues table.
/// Note that there's caching
/// </summary>
/// <param name="ctxt">Object for interacting with the database</param>
/// <param name="value">
/// Could be anything, but it's a either a string, or it better be convertible to a double
/// Note that row IDs are cached, but only for strings or for numbers that are essentially integral
/// </param>
/// <returns>row ID in MySQL table</returns>
public static async Task <long> GetIdAsync(Context ctxt, object value)
{
var totalTimer = ScopeTiming.StartTiming();
try
{
if (value == null)
{
return(-1);
}
long id = -1;
bool shouldCache = (value is string) || Convert.ToDouble(value) == Convert.ToInt64(value);
if (shouldCache)
{
if (sm_cache.TryGetValue(value, out id))
{
return(id);
}
}
Exception lastExp = null;
for (int tryCount = 1; tryCount <= 3; ++tryCount)
{
try
{
id = await GetIdSelectAsync(ctxt, value).ConfigureAwait(false);
if (id >= 0)
{
break;
}
id = await GetIdInsertAsync(ctxt, value).ConfigureAwait(false);
break;
}
catch (Exception exp)
{
lastExp = exp;
}
}
if (id >= 0)
{
if (shouldCache)
{
sm_cache[value] = id;
}
return(id);
}
throw new MetaStringsException("Values.GetId failed after a few retries", lastExp);
}
finally
{
ScopeTiming.RecordScope("Values.GetId", totalTimer);
}
}
/// <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>
/// Generate SQL query given a Select object
/// This is where the metastrings -> SQL magic happens
/// </summary>
/// <param name="query">NoSQL query object</param>
/// <returns>SQL query</returns>
public async Task <string> GenerateSqlAsync(Select query)
{
var totalTimer = ScopeTiming.StartTiming();
try
{
string sql = await Sql.GenerateSqlAsync(Ctxt, query).ConfigureAwait(false);
return(sql);
}
finally
{
ScopeTiming.RecordScope("Cmd.GenerateSql", totalTimer);
}
}
/// <summary>
/// Give a table and value, find the item
/// </summary>
/// <param name="ctxt">Database connection</param>
/// <param name="tableId">The table ID</param>
/// <param name="valueId">The value ID of the primary key</param>
/// <param name="noCreate">Whether to return -1 on error, or throw an exception</param>
/// <returns></returns>
public static async Task <long> GetIdAsync(Context ctxt, int tableId, long valueId, bool noCreate = false)
{
var totalTimer = ScopeTiming.StartTiming();
try
{
Exception lastExp = null;
var cmdParams =
new Dictionary <string, object>
{
{ "@tableId", tableId },
{ "@valueId", valueId }
};
for (int tryCount = 1; tryCount <= 4; ++tryCount)
{
try
{
string selectSql = "SELECT id FROM items WHERE tableId = @tableId AND valueId = @valueId";
object idObj = await ctxt.Db.ExecuteScalarAsync(selectSql, cmdParams).ConfigureAwait(false);
long id = Utils.ConvertDbInt64(idObj);
if (noCreate && id < 0)
{
return(-1);
}
else if (id >= 0)
{
return(id);
}
string insertSql = $"{ctxt.Db.InsertIgnore} items (tableid, valueid, created, lastmodified) " +
$"VALUES (@tableId, @valueId, {ctxt.Db.UtcTimestampFunction}, {ctxt.Db.UtcTimestampFunction})";
id = await ctxt.Db.ExecuteInsertAsync(insertSql, cmdParams).ConfigureAwait(false);
}
catch (Exception exp)
{
lastExp = exp;
}
}
throw new MetaStringsException("Items.GetId failed after a few tries", lastExp);
}
finally
{
ScopeTiming.RecordScope("Items.GetId", totalTimer);
}
}
/// <summary>
/// Pre-cache all values in given a set of MySQL table bvalues row IDs
/// </summary>
/// <param name="ctxt">Object for interacting with the database</param>
/// <param name="ids">MySQL table bvalues row IDs</param>
/// <returns></returns>
public static async Task CacheValuesAsync(Context ctxt, IEnumerable <long> ids)
{
var totalTimer = ScopeTiming.StartTiming();
try
{
var stillToGet = ids.Where(id => !sm_cacheBack.ContainsKey(id));
if (!stillToGet.Any())
{
return;
}
var valueIdInPart = string.Join(",", stillToGet.Select(i => i.ToString()));
var sql = $"SELECT id, isNumeric, numberValue, stringValue FROM bvalues WHERE id IN ({valueIdInPart})";
if (sql.Length > 32 * 1024)
{
throw new MetaStringsException("GetValues query exceeds SQL batch limit of 1M. Use a smaller batch of items.");
}
using (var reader = await ctxt.Db.ExecuteReaderAsync(sql).ConfigureAwait(false))
{
while (await reader.ReadAsync().ConfigureAwait(false))
{
long id = reader.GetInt64(0);
bool isNumeric = reader.GetBoolean(1);
object obj;
if (isNumeric)
{
obj = reader.GetDouble(2);
}
else
{
obj = reader.GetString(3);
}
sm_cacheBack[id] = obj;
}
}
}
finally
{
ScopeTiming.RecordScope("Values.CacheValues", totalTimer);
}
}
/// <summary>
/// Seed the cache with info for a set of name IDs
/// </summary>
/// <param name="ctxt">Database connection</param>
/// <param name="ids">Name IDs to cache the info of</param>
public static async Task CacheNamesAsync(Context ctxt, IEnumerable <int> ids)
{
var totalTimer = ScopeTiming.StartTiming();
try
{
var stillToGet = ids.Where(id => !sm_cacheBack.ContainsKey(id));
if (!stillToGet.Any())
{
return;
}
var nameIdInPart = string.Join(",", stillToGet.Select(i => i.ToString()));
var sql = $"SELECT id, tableid, name, isNumeric FROM names WHERE id IN ({nameIdInPart})";
if (sql.Length > 1000 * 1000)
{
throw new MetaStringsException("GetNames query exceeds SQL batch limit of 1M. Use smaller batches of items.");
}
using (var reader = await ctxt.Db.ExecuteReaderAsync(sql).ConfigureAwait(false))
{
while (await reader.ReadAsync().ConfigureAwait(false))
{
int id = reader.GetInt32(0);
sm_cacheBack[id] =
new NameObj()
{
id = id,
tableId = reader.GetInt32(1),
name = reader.GetString(2),
isNumeric = reader.GetBoolean(3)
};
}
}
}
finally
{
ScopeTiming.RecordScope("Names.CacheNames", totalTimer);
}
}
/// <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);
}
}
/// <summary>
/// Given a row ID in the MySQL bvalues table, get out the value
/// </summary>
/// <param name="ctxt">Object for interacting with the database</param>
/// <param name="id">row ID in the MySQL bvalues table</param>
/// <returns></returns>
public static async Task <object> GetValueAsync(Context ctxt, long id)
{
var totalTimer = ScopeTiming.StartTiming();
try
{
object objFromCache;
if (sm_cacheBack.TryGetValue(id, out objFromCache))
{
return(objFromCache);
}
string sql = $"SELECT isNumeric, numberValue, stringValue FROM bvalues WHERE id = {id}";
using (var reader = await ctxt.Db.ExecuteReaderAsync(sql).ConfigureAwait(false))
{
if (!await reader.ReadAsync().ConfigureAwait(false))
{
throw new MetaStringsException("Values.GetValue fails to find record with ID = " + id);
}
object toReturn;
bool isNumeric = reader.GetBoolean(0);
if (isNumeric)
{
toReturn = reader.GetDouble(1);
}
else
{
toReturn = reader.GetString(2);
}
sm_cacheBack[id] = toReturn;
return(toReturn);
}
}
finally
{
ScopeTiming.RecordScope("Values.GetValue", totalTimer);
}
}
/// <summary>
/// This is the main UPSERT method to populate the database.
/// </summary>
/// <param name="define">Info about metadata to apply to the key</param>
public async Task DefineAsync(Define define)
{
var totalTimer = ScopeTiming.StartTiming();
try
{
var localTimer = ScopeTiming.StartTiming();
bool isKeyNumeric = !(define.key is string);
int tableId = await Tables.GetIdAsync(Ctxt, define.table, isKeyNumeric).ConfigureAwait(false);
long valueId = await Values.GetIdAsync(Ctxt, define.key).ConfigureAwait(false);
long itemId = await Items.GetIdAsync(Ctxt, tableId, valueId).ConfigureAwait(false);
ScopeTiming.RecordScope("Define.Setup", localTimer);
if (define.metadata != null)
{
// name => nameid
var nameValueIds = new Dictionary <int, long>();
foreach (var kvp in define.metadata)
{
bool isMetadataNumeric = !(kvp.Value is string);
int nameId = await Names.GetIdAsync(Ctxt, tableId, kvp.Key, isMetadataNumeric).ConfigureAwait(false);
if (kvp.Value == null) // erase value
{
nameValueIds[nameId] = -1;
continue;
}
bool isNameNumeric = await Names.GetNameIsNumericAsync(Ctxt, nameId).ConfigureAwait(false);
bool isValueNumeric = !(kvp.Value is string);
if (isValueNumeric != isNameNumeric)
{
throw
new MetaStringsException
(
$"Data numeric does not match name: {kvp.Key}" +
$"\n - value is numeric: {isValueNumeric} - {kvp.Value}" +
$"\n - name is numeric: {isNameNumeric}"
);
}
nameValueIds[nameId] =
await Values.GetIdAsync(Ctxt, kvp.Value).ConfigureAwait(false);
}
ScopeTiming.RecordScope("Define.NameIds", localTimer);
Items.SetItemData(Ctxt, itemId, nameValueIds);
ScopeTiming.RecordScope("Define.ItemsCommit", localTimer);
}
await Ctxt.ProcessPostOpsAsync().ConfigureAwait(false);
ScopeTiming.RecordScope("Define.PostOps", localTimer);
}
#if !DEBUG
catch
{
Ctxt.ClearPostOps();
throw;
}
#endif
finally
{
ScopeTiming.RecordScope("Define", totalTimer);
}
}