metastrings: A Simple File-based Dynamic NoSQL Database

• Download source code - 48.1 KB

Introduction

I've been working on this database project for a few years now. There was a lot of ambition, to make it a server database, built on top of MySQL, CSV, full-text...

At this point, it's just a really easy to use file-based dynamic database. If you want to add database technology to your application without writing a lot of CREATE statements or involving a server, metastrings is for you.

Follow this carsdb example to see metastrings in action:

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

You can get the code on github or nuget, or use the attached snapshot.

Use the metastrings.sln file to load and build all the projects:

• carsdb - the example above, ready to run
• metaq - query evaluator demo program
• mslib - the metastrings library, this is the project you'd include in your solution
• tests - unit tests that cover the bases

Get the tests to pass, and you're well on your way.

How It Works

metastrings breaks up your schema into its own schema:

• Tables have the table name and whether the primary key is numeric.
• Names are the columns, and have the column name and whether the column is numeric.
• Values are all the unique values for column data or primary keys in the entire database. Values can be numbers or strings.
• Items are the rows, and have the primary key value.
• ItemNameValues are the cells in the table, gluing together Items, Names and Values.

So that's the secret sauce. Completely dynamic, but it cannot possibly be fast.

Tables, a Simple Class

Tables is much like the other class-per-table types (Names, Values, etc.), and it is the simplest:

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, Where the Magic Happens

The Sql class is where SQL queries are parsed into a dynamic SQL data structure, and where parsed dynamic SQL is converted into real SQLite SQL against the above schema.

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, Connection String Management and SQL Helper Functions

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, Where the Action Is

The Command class implements the Define UPSERT functionality, as well as the DELETE and DROP commands.

Note that there is more functionality here than is covered in the cars DB demo.

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

Conclusion and Points of Interest

As you can see, metastrings will serve you well as a file-based dynamic database... and now you know how it works, too.

Enjoy!

History

• 9^th August, 2021: Initial version