/// <summary>
/// Get info about the table found by looking up the row ID
/// </summary>
/// <param name="ctxt">Database connection</param>
/// <param name="id">Table database row ID</param>
/// <returns></returns>
public static async Task <TableObj> GetTableAsync(Context ctxt, int id)
{
if (id < 0)
{
return(null);
}
TableObj obj;
if (sm_cacheBack.TryGetValue(id, out obj))
{
return(obj);
}
string sql = $"SELECT name, isNumeric FROM tables WHERE id = {id}";
using (var reader = await ctxt.Db.ExecuteReaderAsync(sql).ConfigureAwait(false))
{
if (!await reader.ReadAsync().ConfigureAwait(false))
{
throw new MetaStringsException($"Tables.GetTable fails to find record: {id}");
}
obj =
new TableObj()
{
id = id,
name = reader.GetString(0),
isNumeric = reader.GetBoolean(1)
};
sm_cacheBack[id] = obj;
return(obj);
}
}
public void TestTables()
{
using (var ctxt = TestUtils.GetCtxt())
{
for (int t = 1; t <= 3; ++t)
{
int firstId = Tables.GetIdAsync(ctxt, "foobar", isNumeric: true).Result;
int secondId = Tables.GetIdAsync(ctxt, "foobar").Result;
Assert.AreEqual(firstId, secondId);
int thirdId = Tables.GetIdAsync(ctxt, "bletmonkey", isNumeric: true).Result;
int fourthId = Tables.GetIdAsync(ctxt, "bletmonkey").Result;
Assert.AreEqual(thirdId, fourthId);
Assert.AreNotEqual(firstId, thirdId);
using (var reader = ctxt.Db.ExecuteReaderAsync("SELECT * FROM tables").Result)
{
while (reader.Read())
{
object id = reader["id"];
object name = reader["name"];
Console.WriteLine("tables {0} - {1}", id, name);
}
}
TableObj table = Tables.GetTableAsync(ctxt, secondId).Result;
Assert.AreEqual("foobar", table.name);
Assert.IsTrue(table.isNumeric);
TableObj tables2 = Tables.GetTableAsync(ctxt, secondId).Result;
Assert.AreEqual("foobar", tables2.name);
Assert.IsTrue(tables2.isNumeric);
TableObj tables3 = Tables.GetTableAsync(ctxt, thirdId).Result;
Assert.AreEqual("bletmonkey", tables3.name);
Assert.IsTrue(tables3.isNumeric);
TableObj tables4 = Tables.GetTableAsync(ctxt, fourthId).Result;
Assert.AreEqual("bletmonkey", tables4.name);
Assert.IsTrue(tables4.isNumeric);
}
}
}
/// <summary>
/// This is where the magic metastrings SQL => MySQL SQL conversion takes place
/// </summary>
/// <param name="ctxt">Database connection</param>
/// <param name="query">metastrings query</param>
/// <returns>MySQL SQL</returns>
public static async Task <string> GenerateSqlAsync(Context ctxt, Select query)
{
//
// "COMPILE"
//
if (string.IsNullOrWhiteSpace(query.from))
{
throw new MetaStringsException("Invalid query, FROM is missing");
}
if (query.select == null || query.select.Count == 0)
{
throw new MetaStringsException("Invalid query, SELECT is empty");
}
if (query.orderBy != null)
{
foreach (var order in query.orderBy)
{
string orderField = order.field.Trim();
if (!query.select.Contains(orderField))
{
throw
new MetaStringsException
(
"Invalid query, ORDER BY columns must be present in SELECT column list: " +
$"{order.field.Trim()}"
);
}
}
}
if (query.where != null)
{
foreach (var criteriaSet in query.where)
{
foreach (var criteria in criteriaSet.criteria)
{
Utils.ValidateColumnName(criteria.name, "WHERE");
Utils.ValidateOperator(criteria.op, "WHERE");
Utils.ValidateParameterName(criteria.paramName, "WHERE");
}
}
}
//
// SETUP
//
int tableId = await Tables.GetIdAsync(ctxt, query.from, noCreate : true, noException : true).ConfigureAwait(false);
TableObj tableObj = await Tables.GetTableAsync(ctxt, tableId).ConfigureAwait(false);
// Gather columns
var names = new List <string>();
names.AddRange(query.select);
if (query.orderBy != null)
{
names.AddRange(query.orderBy.Select(o => o.field));
}
if (query.where != null)
{
foreach (var criteriaSet in query.where)
{
names.AddRange(criteriaSet.criteria.Select(w => w.name));
}
}
// Cut them down
names = names.Select(n => n.Trim()).Where(n => !string.IsNullOrEmpty(n)).Distinct().ToList();
// Get name objects
var nameObjs = new Dictionary <string, NameObj>(names.Count);
foreach (var name in names)
{
if (Utils.IsNameReserved(name))
{
nameObjs.Add(name, null);
}
else
{
NameObj nameObj;
{
int nameId = await Names.GetIdAsync(ctxt, tableId, name, noCreate : true, noException : true).ConfigureAwait(false);
if (nameId < 0)
{
nameObj = null;
}
else
{
nameObj = await Names.GetNameAsync(ctxt, nameId);
}
}
nameObjs.Add(name, nameObj);
}
}
//
// SELECT
//
string selectPart = "";
foreach (var name in query.select.Select(n => n.Trim()).Where(n => !string.IsNullOrWhiteSpace(n)))
{
var cleanName = Utils.CleanName(name);
if (selectPart.Length > 0)
{
selectPart += ",\r\n";
}
if (name == "value")
{
if (tableObj == null)
{
selectPart += "NULL";
}
else if (tableObj.isNumeric)
{
selectPart += "bv.numberValue";
}
else
{
selectPart += "bv.stringValue";
}
}
else if (name == "id")
{
selectPart += "i.id";
}
else if (name == "created")
{
selectPart += "i.created";
}
else if (name == "lastmodified")
{
selectPart += "i.lastmodified";
}
else if (name == "count")
{
selectPart += "COUNT(*)";
}
else if (nameObjs[name] == null)
{
selectPart += "NULL";
}
else if (nameObjs[name].isNumeric)
{
selectPart += $"iv{cleanName}.numberValue";
}
else
{
selectPart += $"iv{cleanName}.stringValue";
}
selectPart += $" AS {cleanName}";
}
selectPart = "SELECT\r\n" + selectPart;
//
// FROM
//
string fromPart = "FROM\r\nitems AS i";
if (nameObjs.ContainsKey("value"))
{
fromPart += "\r\nJOIN bvalues bv ON bv.id = i.valueid";
}
foreach (var name in names.Select(n => n.Trim()).Where(n => !string.IsNullOrWhiteSpace(n)))
{
if (!Utils.IsNameReserved(name) && nameObjs.ContainsKey(name) && nameObjs[name] != null)
{
var cleanName = Utils.CleanName(name);
fromPart +=
$"\r\nLEFT OUTER JOIN itemvalues AS iv{cleanName} ON iv{cleanName}.itemid = i.id" +
$" AND iv{cleanName}.nameid = {nameObjs[name].id}";
}
}
//
// WHERE
//
string wherePart = $"i.tableid = {tableId}";
if (query.where != null)
{
foreach (var criteriaSet in query.where)
{
if (criteriaSet.criteria.Count == 0)
{
continue;
}
wherePart += "\r\nAND\r\n";
wherePart += "(";
bool addedOneYet = false;
foreach (var where in criteriaSet.criteria)
{
string name = where.name.Trim();
if (string.IsNullOrWhiteSpace(name))
{
continue;
}
if (!addedOneYet)
{
addedOneYet = true;
}
else
{
wherePart += $" {Enum.GetName(criteriaSet.combine.GetType(), criteriaSet.combine)} ";
}
var nameObj = nameObjs[name];
var cleanName = Utils.CleanName(name);
if (where.op.Equals("matches", StringComparison.OrdinalIgnoreCase))
{
if (nameObj == null)
{
wherePart += "1 = 0"; // name doesn't exist, no match!
}
else
{
wherePart += cleanName == "value" ? "i.valueid" : $"iv{cleanName}.valueid";
wherePart += $" IN (SELECT bvt.valueid FROM bvaluetext AS bvt WHERE bvt.stringSearchValue MATCH {where.paramName})";
}
}
else if (cleanName == "id")
{
wherePart += $"i.id {where.op} {where.paramName}";
}
else if (cleanName == "value")
{
if (tableObj == null)
{
wherePart += "1 = 0"; // no table, no match
}
else if (tableObj.isNumeric)
{
wherePart += $"bv.numberValue {where.op} {where.paramName}";
}
else
{
wherePart += $"bv.stringValue {where.op} {where.paramName}";
}
}
else if (cleanName == "created" || cleanName == "lastmodified")
{
wherePart += $"{cleanName} {where.op} {where.paramName}";
}
else if (nameObj == null)
{
wherePart += "1 = 0"; // name doesn't exist, no match!
}
else if (nameObj.isNumeric)
{
wherePart += $"iv{cleanName}.numberValue {where.op} {where.paramName}";
}
else
{
wherePart += $"iv{cleanName}.stringValue {where.op} {where.paramName}";
}
}
wherePart += ")";
}
}
wherePart = "WHERE " + wherePart;
//
// ORDER BY
//
string orderBy = "";
if (query.orderBy != null)
{
foreach (var order in query.orderBy)
{
if (string.IsNullOrWhiteSpace(order.field))
{
continue;
}
if (orderBy.Length > 0)
{
orderBy += ",\r\n";
}
string orderColumn = order.field;
if (!Utils.IsNameReserved(order.field))
{
Utils.CleanName(order.field);
}
orderBy += orderColumn + (order.descending ? " DESC" : " ASC");
}
if (orderBy.Length > 0)
{
orderBy = "ORDER BY\r\n" + orderBy;
}
}
//
// LIMIT
//
string limitPart = "";
if (query.limit > 0)
{
limitPart = $"LIMIT\r\n{query.limit}";
}
//
// SQL
//
StringBuilder sb = new StringBuilder();
sb.Append($"{selectPart.Trim()}");
sb.Append($"\r\n\r\n{fromPart}");
if (!string.IsNullOrWhiteSpace(wherePart))
{
sb.Append($"\r\n\r\n{wherePart}");
}
if (!string.IsNullOrWhiteSpace(orderBy))
{
sb.Append($"\r\n\r\n{orderBy}");
}
if (!string.IsNullOrWhiteSpace(limitPart))
{
sb.Append($"\r\n\r\n{limitPart}");
}
string sql = sb.ToString();
return(sql);
}