Ejemplo n.º 1
0
        private void ProcessSqlQuery(string sqlQuery)
        {
            TSQLSelectStatement statement = TSQLStatementReader.ParseStatements(sqlQuery)[0] as TSQLSelectStatement;

            var table      = statement.From.Table().Index;
            var conditions = statement.Where.Conditions();
            var fields     = statement.Select.Fields();

            // get table statement
            string tableStatement = $"POST {table}/_search";

            // get the field statement
            string fieldStatement = string.Empty;

            if (fields.Count > 0 && fields[0].Column != "*")
            {
                // add quotes around each field, plus a starting minus
                fieldStatement = string.Join(", ", fields.Select(x => "\"" + x.Column + "\""));
                fieldStatement = ", \"fields\": [" + fieldStatement + "]";
            }

            // get the conditions statement
            string conditionText  = string.Empty;
            var    conditionsList = new List <string>();

            foreach (var condition in conditions)
            {
                switch (condition.Operator)
                {
                case WhereCondition.OperatorType.Equal:

                    switch (condition.Type)
                    {
                    case WhereCondition.LiteralType.Numeric:
                    case WhereCondition.LiteralType.String:
                        conditionText = Templates.SingleCondition
                                        .Replace("(column)", condition.Column)
                                        .Replace("(value)", condition.SingularValue);
                        break;
                    }
                    break;

                case WhereCondition.OperatorType.In:

                    // add switch for condition types later
                    conditionText = Templates.InCondition
                                    .Replace("(column)", condition.Column)
                                    .Replace("(value)", string.Join(",", condition.InValues.Select(x => "\"" + x + "\"")));

                    break;

                case WhereCondition.OperatorType.Between:

                    // add switch for condition types later
                    conditionText = Templates.BetweenCondition
                                    .Replace("(column)", condition.Column)
                                    .Replace("(lowerValue)", condition.BetweenValues.First())
                                    .Replace("(upperValue)", condition.BetweenValues.Last());
                    break;

                case WhereCondition.OperatorType.GreaterThan:
                case WhereCondition.OperatorType.GreaterThanOrEquals:
                case WhereCondition.OperatorType.LessThan:
                case WhereCondition.OperatorType.LessThanOrEquals:
                    // add switch for condition types later
                    conditionText = Templates.ComparisonCondition
                                    .Replace("(column)", condition.Column)
                                    .Replace("(operator)", WhereCondition.FromOperatorType(condition.Operator))
                                    .Replace("(value)", condition.SingularValue);
                    break;

                case WhereCondition.OperatorType.Like:
                    conditionText = Templates.LikeCondition
                                    .Replace("(column)", condition.Column)
                                    .Replace("(value)", condition.SingularValue.Replace("%", "*").ToLower());

                    break;

                case WhereCondition.OperatorType.Unknown:
                    break;
                }

                conditionsList.Add(conditionText);
            }
            string conditionsStatement = Templates.Conditions.Replace("(conditions)", string.Join(",", conditionsList));

            string jsonPortion = $@"{{
                {conditionsStatement}
                {fieldStatement}
            }}";

            // format JSON
            jsonPortion = JToken.Parse(jsonPortion).ToString(Formatting.Indented);

            // set module level variable
            ElasticQuery = $"{tableStatement}{Environment.NewLine}{jsonPortion}";
        }
Ejemplo n.º 2
0
        public static List <WhereCondition> Conditions(this TSQLWhereClause whereClause)
        {
            WhereCondition.OperatorType op;
            var            conditions       = new List <WhereCondition>();
            WhereCondition currentCondition = null;

            if (whereClause == null)
            {
                return(conditions);
            }

            foreach (TSQLToken token in whereClause.Tokens)
            {
                switch (token.Type)
                {
                case TSQLTokenType.Identifier:      // column

                    // save current condition
                    if (currentCondition != null)
                    {
                        conditions.Add(currentCondition);
                    }

                    currentCondition = new WhereCondition()
                    {
                        Column = token.Text.ToString()
                    };

                    break;

                case TSQLTokenType.Operator:
                    op = WhereCondition.ToOperatorType(token.Text.ToString());
                    if (op != WhereCondition.OperatorType.Unknown)
                    {
                        currentCondition.Operator = op;
                    }

                    break;

                case TSQLTokenType.NumericLiteral:
                    currentCondition.Type  = WhereCondition.LiteralType.Numeric;
                    currentCondition.Value = token.Text.ToString();
                    break;

                case TSQLTokenType.StringLiteral:
                    currentCondition.Type  = WhereCondition.LiteralType.String;
                    currentCondition.Value = token.Text.ToString();
                    break;

                case TSQLTokenType.Keyword:
                    // keyword also contain operators (like "in" or "between")
                    op = WhereCondition.ToOperatorType(token.Text.ToString());
                    if (op != WhereCondition.OperatorType.Unknown)
                    {
                        currentCondition.Operator = op;
                    }
                    break;

                case TSQLTokenType.MoneyLiteral:
                    break;

                case TSQLTokenType.BinaryLiteral:
                    break;

                case TSQLTokenType.SystemIdentifier:
                    break;

                case TSQLTokenType.SingleLineComment:
                    break;

                case TSQLTokenType.MultilineComment:
                    break;

                case TSQLTokenType.Variable:
                    break;

                case TSQLTokenType.SystemVariable:
                    break;

                case TSQLTokenType.Whitespace:
                    break;

                case TSQLTokenType.Character:
                    break;
                }
            }

            // save current condition
            if (currentCondition != null)
            {
                conditions.Add(currentCondition);
            }

            return(conditions);
        }