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