Beispiel #1
0
        /// <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);
            }
        }
Beispiel #2
0
        public void TestTables()
        {
            using (var ctxt = TestUtils.GetCtxt())
            {
                for (int t = 1; t <= 3; ++t)
                {
                    int firstId  = Tables.GetIdAsync(ctxt, "foobar", isNumeric: true).Result;
                    int secondId = Tables.GetIdAsync(ctxt, "foobar").Result;
                    Assert.AreEqual(firstId, secondId);

                    int thirdId  = Tables.GetIdAsync(ctxt, "bletmonkey", isNumeric: true).Result;
                    int fourthId = Tables.GetIdAsync(ctxt, "bletmonkey").Result;
                    Assert.AreEqual(thirdId, fourthId);
                    Assert.AreNotEqual(firstId, thirdId);

                    using (var reader = ctxt.Db.ExecuteReaderAsync("SELECT * FROM tables").Result)
                    {
                        while (reader.Read())
                        {
                            object id   = reader["id"];
                            object name = reader["name"];
                            Console.WriteLine("tables {0} - {1}", id, name);
                        }
                    }

                    TableObj table = Tables.GetTableAsync(ctxt, secondId).Result;
                    Assert.AreEqual("foobar", table.name);
                    Assert.IsTrue(table.isNumeric);

                    TableObj tables2 = Tables.GetTableAsync(ctxt, secondId).Result;
                    Assert.AreEqual("foobar", tables2.name);
                    Assert.IsTrue(tables2.isNumeric);

                    TableObj tables3 = Tables.GetTableAsync(ctxt, thirdId).Result;
                    Assert.AreEqual("bletmonkey", tables3.name);
                    Assert.IsTrue(tables3.isNumeric);

                    TableObj tables4 = Tables.GetTableAsync(ctxt, fourthId).Result;
                    Assert.AreEqual("bletmonkey", tables4.name);
                    Assert.IsTrue(tables4.isNumeric);
                }
            }
        }
Beispiel #3
0
        /// <summary>
        /// This is where the magic metastrings SQL => MySQL SQL conversion takes place
        /// </summary>
        /// <param name="ctxt">Database connection</param>
        /// <param name="query">metastrings query</param>
        /// <returns>MySQL SQL</returns>
        public static async Task <string> GenerateSqlAsync(Context ctxt, Select query)
        {
            //
            // "COMPILE"
            //
            if (string.IsNullOrWhiteSpace(query.from))
            {
                throw new MetaStringsException("Invalid query, FROM is missing");
            }

            if (query.select == null || query.select.Count == 0)
            {
                throw new MetaStringsException("Invalid query, SELECT is empty");
            }

            if (query.orderBy != null)
            {
                foreach (var order in query.orderBy)
                {
                    string orderField = order.field.Trim();
                    if (!query.select.Contains(orderField))
                    {
                        throw
                            new MetaStringsException
                            (
                                "Invalid query, ORDER BY columns must be present in SELECT column list: " +
                                $"{order.field.Trim()}"
                            );
                    }
                }
            }

            if (query.where != null)
            {
                foreach (var criteriaSet in query.where)
                {
                    foreach (var criteria in criteriaSet.criteria)
                    {
                        Utils.ValidateColumnName(criteria.name, "WHERE");
                        Utils.ValidateOperator(criteria.op, "WHERE");
                        Utils.ValidateParameterName(criteria.paramName, "WHERE");
                    }
                }
            }


            //
            // SETUP
            //
            int tableId = await Tables.GetIdAsync(ctxt, query.from, noCreate : true, noException : true).ConfigureAwait(false);

            TableObj tableObj = await Tables.GetTableAsync(ctxt, tableId).ConfigureAwait(false);

            // Gather columns
            var names = new List <string>();

            names.AddRange(query.select);

            if (query.orderBy != null)
            {
                names.AddRange(query.orderBy.Select(o => o.field));
            }

            if (query.where != null)
            {
                foreach (var criteriaSet in query.where)
                {
                    names.AddRange(criteriaSet.criteria.Select(w => w.name));
                }
            }

            // Cut them down
            names = names.Select(n => n.Trim()).Where(n => !string.IsNullOrEmpty(n)).Distinct().ToList();

            // Get name objects
            var nameObjs = new Dictionary <string, NameObj>(names.Count);

            foreach (var name in names)
            {
                if (Utils.IsNameReserved(name))
                {
                    nameObjs.Add(name, null);
                }
                else
                {
                    NameObj nameObj;
                    {
                        int nameId = await Names.GetIdAsync(ctxt, tableId, name, noCreate : true, noException : true).ConfigureAwait(false);

                        if (nameId < 0)
                        {
                            nameObj = null;
                        }
                        else
                        {
                            nameObj = await Names.GetNameAsync(ctxt, nameId);
                        }
                    }
                    nameObjs.Add(name, nameObj);
                }
            }


            //
            // SELECT
            //
            string selectPart = "";

            foreach (var name in query.select.Select(n => n.Trim()).Where(n => !string.IsNullOrWhiteSpace(n)))
            {
                var cleanName = Utils.CleanName(name);

                if (selectPart.Length > 0)
                {
                    selectPart += ",\r\n";
                }

                if (name == "value")
                {
                    if (tableObj == null)
                    {
                        selectPart += "NULL";
                    }
                    else if (tableObj.isNumeric)
                    {
                        selectPart += "bv.numberValue";
                    }
                    else
                    {
                        selectPart += "bv.stringValue";
                    }
                }
                else if (name == "id")
                {
                    selectPart += "i.id";
                }
                else if (name == "created")
                {
                    selectPart += "i.created";
                }
                else if (name == "lastmodified")
                {
                    selectPart += "i.lastmodified";
                }
                else if (name == "count")
                {
                    selectPart += "COUNT(*)";
                }
                else if (nameObjs[name] == null)
                {
                    selectPart += "NULL";
                }
                else if (nameObjs[name].isNumeric)
                {
                    selectPart += $"iv{cleanName}.numberValue";
                }
                else
                {
                    selectPart += $"iv{cleanName}.stringValue";
                }
                selectPart += $" AS {cleanName}";
            }
            selectPart = "SELECT\r\n" + selectPart;


            //
            // FROM
            //
            string fromPart = "FROM\r\nitems AS i";

            if (nameObjs.ContainsKey("value"))
            {
                fromPart += "\r\nJOIN bvalues bv ON bv.id = i.valueid";
            }

            foreach (var name in names.Select(n => n.Trim()).Where(n => !string.IsNullOrWhiteSpace(n)))
            {
                if (!Utils.IsNameReserved(name) && nameObjs.ContainsKey(name) && nameObjs[name] != null)
                {
                    var cleanName = Utils.CleanName(name);
                    fromPart +=
                        $"\r\nLEFT OUTER JOIN itemvalues AS iv{cleanName} ON iv{cleanName}.itemid = i.id" +
                        $" AND iv{cleanName}.nameid = {nameObjs[name].id}";
                }
            }


            //
            // WHERE
            //
            string wherePart = $"i.tableid = {tableId}";

            if (query.where != null)
            {
                foreach (var criteriaSet in query.where)
                {
                    if (criteriaSet.criteria.Count == 0)
                    {
                        continue;
                    }

                    wherePart += "\r\nAND\r\n";

                    wherePart += "(";
                    bool addedOneYet = false;
                    foreach (var where in criteriaSet.criteria)
                    {
                        string name = where.name.Trim();
                        if (string.IsNullOrWhiteSpace(name))
                        {
                            continue;
                        }

                        if (!addedOneYet)
                        {
                            addedOneYet = true;
                        }
                        else
                        {
                            wherePart += $" {Enum.GetName(criteriaSet.combine.GetType(), criteriaSet.combine)} ";
                        }

                        var nameObj   = nameObjs[name];
                        var cleanName = Utils.CleanName(name);

                        if (where.op.Equals("matches", StringComparison.OrdinalIgnoreCase))
                        {
                            if (nameObj == null)
                            {
                                wherePart += "1 = 0"; // name doesn't exist, no match!
                            }
                            else
                            {
                                wherePart += cleanName == "value" ? "i.valueid" : $"iv{cleanName}.valueid";
                                wherePart += $" IN (SELECT bvt.valueid FROM bvaluetext AS bvt WHERE bvt.stringSearchValue MATCH {where.paramName})";
                            }
                        }
                        else if (cleanName == "id")
                        {
                            wherePart += $"i.id {where.op} {where.paramName}";
                        }
                        else if (cleanName == "value")
                        {
                            if (tableObj == null)
                            {
                                wherePart += "1 = 0"; // no table, no match
                            }
                            else if (tableObj.isNumeric)
                            {
                                wherePart += $"bv.numberValue {where.op} {where.paramName}";
                            }
                            else
                            {
                                wherePart += $"bv.stringValue {where.op} {where.paramName}";
                            }
                        }
                        else if (cleanName == "created" || cleanName == "lastmodified")
                        {
                            wherePart += $"{cleanName} {where.op} {where.paramName}";
                        }
                        else if (nameObj == null)
                        {
                            wherePart += "1 = 0"; // name doesn't exist, no match!
                        }
                        else if (nameObj.isNumeric)
                        {
                            wherePart += $"iv{cleanName}.numberValue {where.op} {where.paramName}";
                        }
                        else
                        {
                            wherePart += $"iv{cleanName}.stringValue {where.op} {where.paramName}";
                        }
                    }
                    wherePart += ")";
                }
            }
            wherePart = "WHERE " + wherePart;


            //
            // ORDER BY
            //
            string orderBy = "";

            if (query.orderBy != null)
            {
                foreach (var order in query.orderBy)
                {
                    if (string.IsNullOrWhiteSpace(order.field))
                    {
                        continue;
                    }

                    if (orderBy.Length > 0)
                    {
                        orderBy += ",\r\n";
                    }

                    string orderColumn = order.field;
                    if (!Utils.IsNameReserved(order.field))
                    {
                        Utils.CleanName(order.field);
                    }

                    orderBy += orderColumn + (order.descending ? " DESC" : " ASC");
                }

                if (orderBy.Length > 0)
                {
                    orderBy = "ORDER BY\r\n" + orderBy;
                }
            }


            //
            // LIMIT
            //
            string limitPart = "";

            if (query.limit > 0)
            {
                limitPart = $"LIMIT\r\n{query.limit}";
            }


            //
            // SQL
            //
            StringBuilder sb = new StringBuilder();

            sb.Append($"{selectPart.Trim()}");

            sb.Append($"\r\n\r\n{fromPart}");

            if (!string.IsNullOrWhiteSpace(wherePart))
            {
                sb.Append($"\r\n\r\n{wherePart}");
            }

            if (!string.IsNullOrWhiteSpace(orderBy))
            {
                sb.Append($"\r\n\r\n{orderBy}");
            }

            if (!string.IsNullOrWhiteSpace(limitPart))
            {
                sb.Append($"\r\n\r\n{limitPart}");
            }

            string sql = sb.ToString();

            return(sql);
        }