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