Beispiel #1
0
        /// <summary>
        /// Given metadata name-value IDs, return name-value string->object values
        /// </summary>
        /// <param name="ctxt">Database connection</param>
        /// <param name="ids">name=>value IDs</param>
        /// <returns></returns>
        public static async Task <Dictionary <string, object> > GetMetadataValuesAsync(Context ctxt, Dictionary <int, long> ids)
        {
            var totalTimer = ScopeTiming.StartTiming();

            try
            {
                var retVal = new Dictionary <string, object>(ids.Count);
                if (ids.Count == 0)
                {
                    return(retVal);
                }

                await Names.CacheNamesAsync(ctxt, ids.Keys).ConfigureAwait(false);

                await Values.CacheValuesAsync(ctxt, ids.Values).ConfigureAwait(false);

                foreach (var kvp in ids)
                {
                    NameObj name = await Names.GetNameAsync(ctxt, kvp.Key).ConfigureAwait(false);

                    object value = await Values.GetValueAsync(ctxt, kvp.Value).ConfigureAwait(false);

                    retVal.Add(name.name, value);
                }
                return(retVal);
            }
            finally
            {
                ScopeTiming.RecordScope("NameValues.GetMetadataValuesAsync", totalTimer);
            }
        }
Beispiel #2
0
        private static async Task <long> GetIdSelectAsync(Context ctxt, object value)
        {
            var localTimer = ScopeTiming.StartTiming();

            if (value is string)
            {
                string strValue = (string)value;
                Dictionary <string, object> cmdParams = new Dictionary <string, object>();
                cmdParams.Add("@stringValue", strValue);
                string selectSql =
                    "SELECT id FROM bvalues WHERE isNumeric = 0 AND stringValue = @stringValue";
                long id = Utils.ConvertDbInt64(await ctxt.Db.ExecuteScalarAsync(selectSql, cmdParams).ConfigureAwait(false));
                ScopeTiming.RecordScope($"Values.GetId.SELECT(string)", localTimer);
                return(id);
            }
            else
            {
                double numberValue = Convert.ToDouble(value);
                Dictionary <string, object> cmdParams = new Dictionary <string, object>();
                cmdParams.Add("@numberValue", numberValue);
                string selectSql =
                    "SELECT id FROM bvalues WHERE isNumeric = 1 AND numberValue = @numberValue";
                long id = Utils.ConvertDbInt64(await ctxt.Db.ExecuteScalarAsync(selectSql, cmdParams).ConfigureAwait(false));
                ScopeTiming.RecordScope("Values.GetId.SELECT(number)", localTimer);
                return(id);
            }
        }
Beispiel #3
0
        /// <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);
            }
        }
Beispiel #4
0
        /// <summary>
        /// Process a delete request.
        /// </summary>
        /// <param name="toDelete">Delete reqeuest</param>
        public async Task DeleteAsync(Delete toDelete)
        {
            var totalTimer = ScopeTiming.StartTiming();

            try
            {
                int tableId = await Tables.GetIdAsync(Ctxt, toDelete.table, noCreate : true, noException : true).ConfigureAwait(false);

                if (tableId < 0)
                {
                    return;
                }

                foreach (var val in toDelete.values)
                {
                    long valueId = await Values.GetIdAsync(Ctxt, val).ConfigureAwait(false);

                    string sql = $"DELETE FROM items WHERE valueid = {valueId} AND tableid = {tableId}";
                    Ctxt.AddPostOp(sql);
                }

                await Ctxt.ProcessPostOpsAsync().ConfigureAwait(false);
            }
            finally
            {
                ScopeTiming.RecordScope("Cmd.Delete", totalTimer);
            }
        }
Beispiel #5
0
        /// <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);
            }
        }
Beispiel #6
0
        /// <summary>
        /// Given a value, get the row ID in the MySQL bvalues table.
        /// Note that there's caching
        /// </summary>
        /// <param name="ctxt">Object for interacting with the database</param>
        /// <param name="value">
        /// Could be anything, but it's a either a string, or it better be convertible to a double
        /// Note that row IDs are cached, but only for strings or for numbers that are essentially integral
        /// </param>
        /// <returns>row ID in MySQL table</returns>
        public static async Task <long> GetIdAsync(Context ctxt, object value)
        {
            var totalTimer = ScopeTiming.StartTiming();

            try
            {
                if (value == null)
                {
                    return(-1);
                }

                long id          = -1;
                bool shouldCache = (value is string) || Convert.ToDouble(value) == Convert.ToInt64(value);
                if (shouldCache)
                {
                    if (sm_cache.TryGetValue(value, out id))
                    {
                        return(id);
                    }
                }

                Exception lastExp = null;
                for (int tryCount = 1; tryCount <= 3; ++tryCount)
                {
                    try
                    {
                        id = await GetIdSelectAsync(ctxt, value).ConfigureAwait(false);

                        if (id >= 0)
                        {
                            break;
                        }

                        id = await GetIdInsertAsync(ctxt, value).ConfigureAwait(false);

                        break;
                    }
                    catch (Exception exp)
                    {
                        lastExp = exp;
                    }
                }

                if (id >= 0)
                {
                    if (shouldCache)
                    {
                        sm_cache[value] = id;
                    }
                    return(id);
                }

                throw new MetaStringsException("Values.GetId failed after a few retries", lastExp);
            }
            finally
            {
                ScopeTiming.RecordScope("Values.GetId", totalTimer);
            }
        }
Beispiel #7
0
        /// <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);
            }
        }
Beispiel #8
0
        /// <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);
            }
        }
Beispiel #9
0
        /// <summary>
        /// Give a table and value, find the item
        /// </summary>
        /// <param name="ctxt">Database connection</param>
        /// <param name="tableId">The table ID</param>
        /// <param name="valueId">The value ID of the primary key</param>
        /// <param name="noCreate">Whether to return -1 on error, or throw an exception</param>
        /// <returns></returns>
        public static async Task <long> GetIdAsync(Context ctxt, int tableId, long valueId, bool noCreate = false)
        {
            var totalTimer = ScopeTiming.StartTiming();

            try
            {
                Exception lastExp   = null;
                var       cmdParams =
                    new Dictionary <string, object>
                {
                    { "@tableId", tableId },
                    { "@valueId", valueId }
                };
                for (int tryCount = 1; tryCount <= 4; ++tryCount)
                {
                    try
                    {
                        string selectSql = "SELECT id FROM items WHERE tableId = @tableId AND valueId = @valueId";
                        object idObj     = await ctxt.Db.ExecuteScalarAsync(selectSql, cmdParams).ConfigureAwait(false);

                        long id = Utils.ConvertDbInt64(idObj);
                        if (noCreate && id < 0)
                        {
                            return(-1);
                        }
                        else if (id >= 0)
                        {
                            return(id);
                        }

                        string insertSql = $"{ctxt.Db.InsertIgnore} items (tableid, valueid, created, lastmodified) " +
                                           $"VALUES (@tableId, @valueId, {ctxt.Db.UtcTimestampFunction}, {ctxt.Db.UtcTimestampFunction})";
                        id = await ctxt.Db.ExecuteInsertAsync(insertSql, cmdParams).ConfigureAwait(false);
                    }
                    catch (Exception exp)
                    {
                        lastExp = exp;
                    }
                }

                throw new MetaStringsException("Items.GetId failed after a few tries", lastExp);
            }
            finally
            {
                ScopeTiming.RecordScope("Items.GetId", totalTimer);
            }
        }
Beispiel #10
0
        /// <summary>
        /// Pre-cache all values in given a set of MySQL table bvalues row IDs
        /// </summary>
        /// <param name="ctxt">Object for interacting with the database</param>
        /// <param name="ids">MySQL table bvalues row IDs</param>
        /// <returns></returns>
        public static async Task CacheValuesAsync(Context ctxt, IEnumerable <long> ids)
        {
            var totalTimer = ScopeTiming.StartTiming();

            try
            {
                var stillToGet = ids.Where(id => !sm_cacheBack.ContainsKey(id));
                if (!stillToGet.Any())
                {
                    return;
                }

                var valueIdInPart = string.Join(",", stillToGet.Select(i => i.ToString()));
                var sql           = $"SELECT id, isNumeric, numberValue, stringValue FROM bvalues WHERE id IN ({valueIdInPart})";
                if (sql.Length > 32 * 1024)
                {
                    throw new MetaStringsException("GetValues query exceeds SQL batch limit of 1M.  Use a smaller batch of items.");
                }

                using (var reader = await ctxt.Db.ExecuteReaderAsync(sql).ConfigureAwait(false))
                {
                    while (await reader.ReadAsync().ConfigureAwait(false))
                    {
                        long id = reader.GetInt64(0);

                        bool isNumeric = reader.GetBoolean(1);

                        object obj;
                        if (isNumeric)
                        {
                            obj = reader.GetDouble(2);
                        }
                        else
                        {
                            obj = reader.GetString(3);
                        }

                        sm_cacheBack[id] = obj;
                    }
                }
            }
            finally
            {
                ScopeTiming.RecordScope("Values.CacheValues", totalTimer);
            }
        }
Beispiel #11
0
        /// <summary>
        /// Seed the cache with info for a set of name IDs
        /// </summary>
        /// <param name="ctxt">Database connection</param>
        /// <param name="ids">Name IDs to cache the info of</param>
        public static async Task CacheNamesAsync(Context ctxt, IEnumerable <int> ids)
        {
            var totalTimer = ScopeTiming.StartTiming();

            try
            {
                var stillToGet = ids.Where(id => !sm_cacheBack.ContainsKey(id));
                if (!stillToGet.Any())
                {
                    return;
                }

                var nameIdInPart = string.Join(",", stillToGet.Select(i => i.ToString()));
                var sql          = $"SELECT id, tableid, name, isNumeric FROM names WHERE id IN ({nameIdInPart})";
                if (sql.Length > 1000 * 1000)
                {
                    throw new MetaStringsException("GetNames query exceeds SQL batch limit of 1M.  Use smaller batches of items.");
                }

                using (var reader = await ctxt.Db.ExecuteReaderAsync(sql).ConfigureAwait(false))
                {
                    while (await reader.ReadAsync().ConfigureAwait(false))
                    {
                        int id = reader.GetInt32(0);
                        sm_cacheBack[id] =
                            new NameObj()
                        {
                            id        = id,
                            tableId   = reader.GetInt32(1),
                            name      = reader.GetString(2),
                            isNumeric = reader.GetBoolean(3)
                        };
                    }
                }
            }
            finally
            {
                ScopeTiming.RecordScope("Names.CacheNames", totalTimer);
            }
        }
Beispiel #12
0
        /// <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);
            }
        }
Beispiel #13
0
        /// <summary>
        /// Given a row ID in the MySQL bvalues table, get out the value
        /// </summary>
        /// <param name="ctxt">Object for interacting with the database</param>
        /// <param name="id">row ID in the MySQL bvalues table</param>
        /// <returns></returns>
        public static async Task <object> GetValueAsync(Context ctxt, long id)
        {
            var totalTimer = ScopeTiming.StartTiming();

            try
            {
                object objFromCache;
                if (sm_cacheBack.TryGetValue(id, out objFromCache))
                {
                    return(objFromCache);
                }

                string sql = $"SELECT isNumeric, numberValue, stringValue FROM bvalues WHERE id = {id}";
                using (var reader = await ctxt.Db.ExecuteReaderAsync(sql).ConfigureAwait(false))
                {
                    if (!await reader.ReadAsync().ConfigureAwait(false))
                    {
                        throw new MetaStringsException("Values.GetValue fails to find record with ID = " + id);
                    }
                    object toReturn;
                    bool   isNumeric = reader.GetBoolean(0);
                    if (isNumeric)
                    {
                        toReturn = reader.GetDouble(1);
                    }
                    else
                    {
                        toReturn = reader.GetString(2);
                    }
                    sm_cacheBack[id] = toReturn;
                    return(toReturn);
                }
            }
            finally
            {
                ScopeTiming.RecordScope("Values.GetValue", totalTimer);
            }
        }
Beispiel #14
0
        /// <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);
            }
        }