metastrings：一个简单的基于文件的动态NoSQL数据库
5.00/5 (3投票s)
了解动态数据库的内部工作原理，以及 SQLite 如何成为一个绝佳的选择
引言
我已经在这个数据库项目上工作了几年。最初的雄心很大，希望将其构建成一个服务器数据库，基于 MySQL、CSV、全文...
现在，它只是一个非常易于使用的基于文件的动态数据库。如果您想在不编写大量 CREATE 语句或涉及服务器的情况下，将数据库技术添加到您的应用程序中，那么 metastrings 适合您。
请参考这个 carsdb 示例，了解 metastrings 的实际应用
using System;
using System.Threading.Tasks;
using System.Collections.Generic;
using System.IO;
namespace metastrings
{
/// <summary>
/// This program demonstrates creating a NoSQL database
/// and using all four of the metastrings commands
/// to populate and manipulate a cars database.
/// 1. UPSERT
/// 2. SELECT
/// 3. DELETE
/// 4. DROP
/// </summary>
class Program
{
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 columns 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.
while (reader.Read())
{
// Collect the row ID GUID that AddCarAsync added.
oldCarGuids.Add(reader.GetString(0));
// NOTE: 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.");
}
}
/// <summary>
/// Given info about a car, UPSERT it into the database
/// </summary>
/// <param name="ctxt">The Context for doing database work</param>
/// <param name="year">The year of the car</param>
/// <param name="make">The make of the car</param>
/// <param name="model">The model of the car</param>
/// <returns>Awaitable task</returns>
static async Task AddCarAsync(Context ctxt, int year, string make, string model)
{
// The Define class is used to do UPSERTs.
// You pass the table name and primary key value to the constructor.
// No need to create tables, just refer to them by name
// and the database takes care of it.
// The second parameter to the Define constructor is the primary key.
// We have no obvious primary key, so we use a GUID.
Define define = new Define("cars", Guid.NewGuid().ToString());
// Use the Define.Set function to add column data.
define.Set("year", year);
define.Set("make", make);
define.Set("model", model);
// Call through the Context to create a Command to do the UPSERT.
await ctxt.Cmd.DefineAsync(define);
}
}
}
Using the Code
您可以从 github 或 nuget 获取代码，或者使用附带的快照。
使用 metastrings.sln 文件加载并构建所有项目
carsdb- 上面的示例，可以直接运行metaq- 查询评估器演示程序mslib-metastrings库，这是您将在解决方案中包含的项目tests- 涵盖基础的单元测试
让测试通过，您就走上正确的道路了。
工作原理
metastrings 将您的模式分解为自己的模式
Tables包含表名和主键是否为数字。Names是列，包含列名和列是否为数字。Values是数据库中所有列数据或主键的所有唯一值。Values可以是数字或string。Items是行，包含主键值。ItemNameValues是表中的单元格，将Items、Names和Values粘合在一起。
这就是秘诀。完全动态，但它不可能很快。
Tables，一个简单的类
Tables 就像其他按表分类的类（Names、Values 等），而且它是最简单的
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace metastrings
{
public class TableObj
{
public int id;
public string name;
public bool isNumeric;
}
/// <summary>
/// metastrings implementation class for the tables in the virtual schema
/// </summary>
public static class Tables
{
internal static string[] CreateSql
{
get
{
return new[]
{
"CREATE TABLE tables\n(\n" +
"id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,\n" +
"name TEXT NOT NULL UNIQUE,\n" +
"isNumeric BOOLEAN NOT NULL\n" +
")"
};
}
}
/// <summary>
/// Remove all tables from the database
/// </summary>
/// <param name="ctxt">Database connection</param>
public static void Reset(Context ctxt)
{
sm_cache.Clear();
sm_cacheBack.Clear();
ctxt.Db.ExecuteSql("DELETE FROM tables");
}
/// <summary>
/// Given a table name, get the row ID for the table
/// </summary>
/// <param name="ctxt">Database connection</param>
/// <param name="name">Table name</param>
/// <param name="isNumeric">Is the table's primary key numeric or string</param>
/// <param name="noCreate">Should an exception be thrown if no table found</param>
/// <param name="noException">Should -1 be returned instead of
/// throwing an exception if the table is not found</param>
/// <returns>Database row ID for the table</returns>
public static async Task<int> GetIdAsync(Context ctxt, string name,
bool isNumeric = false, bool noCreate = false, bool noException = false)
{
int id;
if (sm_cache.TryGetValue(name, out id))
return id;
if (!Utils.IsWord(name))
throw new MetaStringsException($"Types.GetId name is not valid: {name}");
if (Utils.IsNameReserved(name))
throw new MetaStringsException($"Types.GetId name is reserved: {name}");
Exception lastExp = null;
bool isExpFinal = false;
for (int tryCount = 1; tryCount <= 4; ++tryCount)
{
try
{
Dictionary<string, object> cmdParams = new Dictionary<string, object>();
cmdParams.Add("@name", name);
string selectSql = "SELECT id FROM tables WHERE name = @name";
object idObj = await ctxt.Db.ExecuteScalarAsync
(selectSql, cmdParams).ConfigureAwait(false);
id = Utils.ConvertDbInt32(idObj);
if (id >= 0)
{
sm_cache[name] = id;
return id;
}
if (noCreate)
{
if (noException)
return -1;
isExpFinal = true;
throw new MetaStringsException
($"Tables.GetId cannot create new table: {name}", lastExp);
}
cmdParams.Add("@isNumeric", isNumeric);
string insertSql = "INSERT INTO tables (name, isNumeric)
VALUES (@name, @isNumeric)";
id = (int)await ctxt.Db.ExecuteInsertAsync
(insertSql, cmdParams).ConfigureAwait(false);
sm_cache[name] = id;
return id;
}
catch (Exception exp)
{
if (isExpFinal)
throw exp;
lastExp = exp;
}
}
throw new MetaStringsException("Tables.GetId fails after a few tries", lastExp);
}
/// <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;
}
}
internal static void ClearCaches()
{
sm_cache.Clear();
sm_cacheBack.Clear();
}
private static ConcurrentDictionary<string, int> sm_cache =
new ConcurrentDictionary<string, int>();
private static ConcurrentDictionary<int, TableObj> sm_cacheBack =
new ConcurrentDictionary<int, TableObj>();
}
}
Sql，魔法发生的地方
Sql 类是 SQL 查询被解析为动态 SQL 数据结构的地方，也是解析后的动态 SQL 被转换为针对上述模式的真实 SQLite SQL 的地方。
using System;
using System.Threading.Tasks;
using System.Collections.Generic;
using System.Text;
using System.Linq;
namespace metastrings
{
/// <summary>
/// API for turning SQL strings to and from NoSQL query objects
/// </summary>
public static class Sql
{
private enum SqlState
{
SELECT,
FROM,
WHERE,
ORDER,
LIMIT
}
/// <summary>
/// Given a SQL-like query, return a a Select object,
/// ready for adding parameters and querying
/// </summary>
/// <param name="sql">SQL-like query</param>
/// <returns>Select object for adding parameters and executing</returns>
public static Select Parse(string sql)
{
string[] tokens = Utils.Tokenize(sql);
if (tokens.Length == 0 ||
(tokens.Length == 1 && string.IsNullOrWhiteSpace(tokens[0])))
throw new SqlException("No tokens", sql);
Select select = new Select();
SqlState state = SqlState.SELECT;
int idx = 0;
while (idx < tokens.Length)
{
string currentToken = tokens[idx];
if (state == SqlState.SELECT)
{
// Should start with SELECT
if (!currentToken.Equals("SELECT", StringComparison.OrdinalIgnoreCase))
throw new SqlException("No SELECT", sql);
// Slurp up the SELECT columns
select.select = new List<string>();
while (true)
{
++idx;
if (idx >= tokens.Length)
throw new SqlException("No SELECT columns", sql);
currentToken = tokens[idx];
bool lastColumn = !currentToken.EndsWith
(",", StringComparison.Ordinal);
if (!lastColumn)
currentToken = currentToken.TrimEnd(',');
Utils.ValidateColumnName(currentToken, sql);
select.select.Add(currentToken);
if (lastColumn)
break;
}
++idx;
state = SqlState.FROM;
continue;
}
if (state == SqlState.FROM)
{
if (!currentToken.Equals("FROM", StringComparison.OrdinalIgnoreCase))
throw new SqlException("No FROM", sql);
++idx;
if (idx >= tokens.Length)
throw new SqlException("No FROM table", sql);
currentToken = tokens[idx];
Utils.ValidateTableName(currentToken, sql);
select.from = currentToken;
++idx;
state = SqlState.WHERE;
continue;
}
if (state == SqlState.WHERE)
{
if (!currentToken.Equals("WHERE", StringComparison.OrdinalIgnoreCase))
{
state = SqlState.ORDER;
continue;
}
// Gobble up WHERE criteria
CriteriaSet criteriaSet = new CriteriaSet();
select.where = new List<CriteriaSet> { criteriaSet };
++idx;
while ((idx + 3) <= tokens.Length)
{
var criteria =
new Criteria()
{
name = tokens[idx++],
op = tokens[idx++],
paramName = tokens[idx++]
};
Utils.ValidateColumnName(criteria.name, sql);
Utils.ValidateOperator(criteria.op, sql);
Utils.ValidateParameterName(criteria.paramName, sql);
criteriaSet.AddCriteria(criteria);
if
(
(idx + 3) <= tokens.Length
&&
tokens[idx].Equals("AND", StringComparison.OrdinalIgnoreCase)
)
{
++idx;
continue;
}
else
{
break;
}
}
if (criteriaSet.criteria.Count == 0)
throw new SqlException("No WHERE criteria", sql);
state = SqlState.ORDER;
continue;
}
if (state == SqlState.ORDER)
{
string nextToken = (idx + 1) < tokens.Length ? tokens[idx + 1] : "";
if
(
(idx + 3) > tokens.Length
||
!currentToken.Equals("ORDER", StringComparison.OrdinalIgnoreCase)
||
!nextToken.Equals("BY", StringComparison.OrdinalIgnoreCase)
)
{
state = SqlState.LIMIT;
continue;
}
idx += 2;
var orders = new List<Order>();
select.orderBy = orders;
while (idx < tokens.Length)
{
currentToken = tokens[idx];
bool currentEnds = idx == tokens.Length - 1 ||
currentToken.EndsWith(",", StringComparison.Ordinal);
nextToken = "ASC";
if (!currentEnds)
{
if ((idx + 1) < tokens.Length)
nextToken = tokens[++idx];
}
bool nextEnds = nextToken.EndsWith(",", StringComparison.Ordinal);
bool isLimit = nextToken.Equals
("LIMIT", StringComparison.OrdinalIgnoreCase);
bool lastColumn = isLimit || !(currentEnds || nextEnds);
currentToken = currentToken.TrimEnd(',');
nextToken = nextToken.TrimEnd(',');
bool isDescending;
{
if (nextToken.Equals
("ASC", StringComparison.OrdinalIgnoreCase))
isDescending = false;
else if (nextToken.Equals
("DESC", StringComparison.OrdinalIgnoreCase))
isDescending = true;
else if (isLimit)
isDescending = false;
else
throw new SqlException("Invalid ORDER BY", sql);
}
Utils.ValidateColumnName(currentToken, sql);
var orderObj = new Order()
{ field = currentToken, descending = isDescending };
orders.Add(orderObj);
if (!isLimit)
++idx;
if (lastColumn)
break;
}
state = SqlState.LIMIT;
continue;
}
if (state == SqlState.LIMIT)
{
if (currentToken.Equals("LIMIT", StringComparison.OrdinalIgnoreCase))
{
++idx;
if (idx >= tokens.Length)
throw new SqlException("No LIMIT value", sql);
currentToken = tokens[idx];
int limitVal;
if (!int.TryParse(currentToken, out limitVal))
throw new SqlException("Invalid LIMIT value", sql);
select.limit = limitVal;
++idx;
break;
}
else
{
throw new SqlException("Invalid final statement", sql);
}
}
throw new SqlException($"Invalid SQL parser state: {state}", sql);
}
if (idx < tokens.Length - 1)
throw new SqlException("Not all parsed", sql);
if (select.select.Count == 0)
throw new SqlException("No SELECT columns", sql);
if (string.IsNullOrWhiteSpace(select.from))
throw new SqlException("No FROM", sql);
return select;
}
/// <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 (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;
}
}
}
Context，连接字符串管理和 SQL 辅助函数
using System;
using System.Threading.Tasks;
using System.Collections.Generic;
using System.Collections.Concurrent;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
namespace metastrings
{
/// <summary>
/// Context manages the database connection
/// and provides useful query helper functions
/// </summary>
public class Context : IDisposable
{
/// <summary>
/// Create a context for a database connection
/// </summary>
/// <param name="dbConnStr">Database connection string...we're out of the
/// config business</param>
public Context(string dbConnStr)
{
string actualDbConnStr;
if (!sm_dbConnStrs.TryGetValue(dbConnStr, out actualDbConnStr))
{
lock (sm_dbBuildLock)
{
if (!sm_dbConnStrs.TryGetValue(dbConnStr, out actualDbConnStr))
{
actualDbConnStr = dbConnStr;
if (!IsDbServer(actualDbConnStr))
{
string dbFilePath = DbConnStrToFilePath(actualDbConnStr);
actualDbConnStr = "Data Source=" + dbFilePath;
if (!(File.Exists(dbFilePath) &&
new FileInfo(dbFilePath).Length > 0))
{
SQLiteConnection.CreateFile(dbFilePath);
using (var db = new SqlLiteDb(actualDbConnStr))
{
RunSql(db, Tables.CreateSql);
RunSql(db, Names.CreateSql);
RunSql(db, Values.CreateSql);
RunSql(db, Items.CreateSql);
RunSql(db, LongStrings.CreateSql);
}
}
using (var db = new SqlLiteDb(actualDbConnStr))
RunSql(db, new[] { "PRAGMA journal_mode = WAL",
"PRAGMA synchronous = NORMAL" });
}
sm_dbConnStrs[dbConnStr] = actualDbConnStr;
}
}
}
IsServerDb = IsDbServer(actualDbConnStr);
if (IsServerDb)
Db = new MySqlDb(actualDbConnStr);
else
Db = new SqlLiteDb(actualDbConnStr);
}
public void Dispose()
{
if (Db != null)
{
Db.Dispose();
Db = null;
}
if (m_postItemOps != null && m_postItemOps.Count > 0)
throw new MetaStringsException
("Post ops remain; call ProcessPostOpsAsync
before disposing the metastrings context");
}
/// <summary>
/// The database connection
/// </summary>
public IDb Db { get; private set; }
/// <summary>
/// See if it's MySQL, not SQLite
/// </summary>
public bool IsServerDb { get; private set; }
/// <summary>
/// Create a new Command object using this Context
/// </summary>
public Command Cmd => new Command(this);
/// <summary>
/// Transactions are supported,
/// but should not be used around any code affecting data
/// in the Table, Name, Value, etc. metastrings database
/// as rollbacks would break the global in-memory caching
/// </summary>
/// <returns>Transaction object</returns>
public MsTrans BeginTrans()
{
return Db.BeginTrans();
}
/// <summary>
/// Query helper function to get a reader for a query
/// </summary>
/// <param name="select">Query to execute</param>
/// <returns>Reader to get results from</returns>
public async Task<DbDataReader> ExecSelectAsync(Select select)
{
var cmdParams = select.cmdParams;
var sql = await Sql.GenerateSqlAsync(this, select).ConfigureAwait(false);
return await Db.ExecuteReaderAsync(sql, cmdParams).ConfigureAwait(false);
}
/// <summary>
/// Query helper function to get a single value for a query
/// </summary>
/// <param name="select">Query to execute</param>
/// <returns>The single query result value</returns>
public async Task<object> ExecScalarAsync(Select select)
{
var cmdParams = select.cmdParams;
var sql = await Sql.GenerateSqlAsync(this, select).ConfigureAwait(false);
return await Db.ExecuteScalarAsync(sql, cmdParams).ConfigureAwait(false);
}
/// <summary>
/// Query helper to get a single 64-bit integer query result
/// </summary>
/// <param name="select">Query to execute</param>
/// <returns>64-bit result value, or -1 if processing fails</returns>
public async Task<long> ExecScalar64Async(Select select)
{
object result = await ExecScalarAsync(select).ConfigureAwait(false);
long val = Utils.ConvertDbInt64(result);
return val;
}
/// <summary>
/// Query helper to get a list of results from a single-column query
/// </summary>
/// <param name="select">Query to execute</param>
/// <returns>List of results of type T</returns>
public async Task<List<T>> ExecListAsync<T>(Select select)
{
var values = new List<T>();
using (var reader = await ExecSelectAsync(select).ConfigureAwait(false))
{
while (await reader.ReadAsync().ConfigureAwait(false))
values.Add((T)reader.GetValue(0));
}
return values;
}
/// <summary>
/// Query helper to get a dictionary of results from a single-column query
/// </summary>
/// <param name="select">Query to execute</param>
/// <returns>ListDictionary of results of type K, V</returns>
public async Task<ListDictionary<K, V>> ExecDictAsync<K, V>(Select select)
{
var values = new ListDictionary<K, V>();
using (var reader = await ExecSelectAsync(select).ConfigureAwait(false))
{
while (await reader.ReadAsync().ConfigureAwait(false))
values.Add((K)reader.GetValue(0), (V)reader.GetValue(1));
}
return values;
}
/// <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;
}
/// <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>
/// 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);
}
}
internal void AddPostOp(string sql)
{
if (m_postItemOps == null)
m_postItemOps = new List<string>();
m_postItemOps.Add(sql);
}
internal void ClearPostOps()
{
if (m_postItemOps != null)
m_postItemOps.Clear();
}
private List<string> m_postItemOps;
private static string DbConnStrToFilePath(string connStr)
{
if (IsDbServer(connStr))
throw new MetaStringsException("Connection string is not for file-based DB");
string filePath = connStr;
int equals = filePath.IndexOf('=');
if (equals > 0)
filePath = filePath.Substring(equals + 1);
filePath = filePath.Replace("[UserRoaming]",
Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData));
filePath = filePath.Replace("[MyDocuments]",
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments));
string directoryPath = Path.GetDirectoryName(filePath);
if (!Directory.Exists(directoryPath))
Directory.CreateDirectory(directoryPath);
return filePath;
}
private static bool IsDbServer(string connStr)
{
bool isServer = connStr.IndexOf
("Server=", 0, StringComparison.OrdinalIgnoreCase) >= 0;
return isServer;
}
private static void RunSql(IDb db, string[] sqlQueries)
{
foreach (string sql in sqlQueries)
db.ExecuteSql(sql);
}
private static object sm_dbBuildLock = new object();
private static ConcurrentDictionary<string, string> sm_dbConnStrs =
new ConcurrentDictionary<string, string>();
}
}
Command，行动发生的地方
Command 类实现了 Define UPSERT 功能，以及 DELETE 和 DROP 命令。
请注意，这里的功能比 cars DB 演示中涵盖的要多。
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace metastrings
{
/// <summary>
/// Command implements the metastrings API.
/// Each function takes an input parameters class
/// and returns a response parameters class.
/// This stemmed from earlier code which supported a
/// JSON-in / JSON-out standalone application server.
/// </summary>
public class Command
{
/// <summary>
/// A Command needs a Context for accessing the database.
/// </summary>
/// <param name="ctxt">Object used for accessing the database</param>
public Command(Context ctxt)
{
Ctxt = ctxt;
}
/// <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);
}
}
/// <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>
/// 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>
/// 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>
/// Delete a single item from a table.
/// </summary>
/// <param name="table">Table to delete from</param>
/// <param name="value">Value of object to delete</param>
public async Task DeleteAsync(string table, object value)
{
await DeleteAsync(new Delete(table, value)).ConfigureAwait(false);
}
/// <summary>
/// Delete multiple items from a table.
/// </summary>
/// <param name="table">Table to delete from</param>
/// <param name="values">Values of objects to delete</param>
public async Task DeleteAsync(string table, IEnumerable<object> values)
{
await DeleteAsync(new Delete(table, values)).ConfigureAwait(false);
}
/// <summary>
/// Process a delete request.
/// </summary>
/// <param name="toDelete">Delete request</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>
/// 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();
}
/// <summary>
/// Get the schema of a metastrings database
/// </summary>
/// <param name="table">Name of table to get the schema of</param>
/// <returns>Schema object</returns>
public async Task<SchemaResponse> GetSchemaAsync(string table)
{
string sql =
"SELECT t.name AS tablename, n.name AS colname " +
"FROM tables t JOIN names n ON n.tableid = t.id";
string requestedTable = table;
bool haveRequestedTableName = !string.IsNullOrWhiteSpace(requestedTable);
if (haveRequestedTableName)
sql += " WHERE t.name = @name";
sql += " ORDER BY tablename, colname";
Dictionary<string, object> cmdParams = new Dictionary<string, object>();
if (haveRequestedTableName)
cmdParams.Add("@name", requestedTable);
var responseDict = new ListDictionary<string, List<string>>();
using (var reader = await Ctxt.Db.ExecuteReaderAsync
(sql, cmdParams).ConfigureAwait(false))
{
while (await reader.ReadAsync().ConfigureAwait(false))
{
string curTable = reader.GetString(0);
string colname = reader.GetString(1);
if (!responseDict.ContainsKey(curTable))
responseDict.Add(curTable, new List<string>());
responseDict[curTable].Add(colname);
}
}
SchemaResponse response = new SchemaResponse() { tables = responseDict };
return response;
}
/// <summary>
/// Explicitly create a table in the schema.
/// This is usually unnecessary as tables are created as referred to by Define.
/// </summary>
/// <param name="name">Table name to create request</param>
public async Task CreateTableAsync(string name, bool isNumeric)
{
await Tables.GetIdAsync(Ctxt, name, isNumeric).ConfigureAwait(false);
}
/// <summary>
/// Put a long strings into the database
/// </summary>
/// <param name="put">String put request</param>
public async Task PutLongStringAsync(LongStringPut put)
{
await LongStrings.StoreStringAsync
(Ctxt, put.itemId, put.fieldName, put.longString).ConfigureAwait(false);
}
/// <summary>
/// Get a long string from the database
/// </summary>
/// <param name="get">String get request</param>
/// <returns>Long string value, or null if not found</returns>
public async Task<string> GetLongStringAsync(LongStringOp get)
{
string longString = await LongStrings.GetStringAsync
(Ctxt, get.itemId, get.fieldName).ConfigureAwait(false);
return longString;
}
/// <summary>
/// Remove a long strings from the database
/// </summary>
/// <param name="del">String deletion request</param>
public async Task DeleteLongStringAsync(LongStringOp del)
{
await LongStrings.DeleteStringAsync
(Ctxt, del.itemId, del.fieldName).ConfigureAwait(false);
}
private Context Ctxt;
}
}
结论和关注点
正如您所见，metastrings 将作为基于文件的动态数据库为您提供良好的服务……现在您也知道它是如何工作的了。
尽情享用！
历史
- 2021 年 8 月 9 日：初始版本