public Result <SelectStatement, SqlStatementError> ParseSelect(SqlStatement statement)
{
var selectStatement = new SelectStatement();
var errors = new List <SqlStatementError>();
var rules = Configuration.SelectStatementConfiguration.Rules();
var args = new Dictionary <string, string>();
var words = ParserUtils.GetWords(statement.Sql).ToList();
if (words.First() != "select")
{
errors.Add(new SqlStatementError("No 'Select' statement found.", 0));
}
else
{
var currentIndex = 0;
while (true)
{
var word = words[currentIndex];
var rule = rules.SingleOrDefault(x => x.Name == word);
if (rule == null)
{
break;
}
if (rule.NextTokens.Any())
{
int?nextTokenIndex = null;
foreach (var nextToken in rule.NextTokens)
{
nextTokenIndex = FindInArray(words, currentIndex, nextToken);
if (nextTokenIndex.HasValue)
{
break;
}
}
if (rule.RequiresNextToken && nextTokenIndex.HasValue == false)
{
errors.Add(new SqlStatementError($"Incomplete statement after {word}", 0));
break;
}
if (rule.Args)
{
var count = nextTokenIndex.HasValue
? nextTokenIndex.Value - currentIndex - 1
: words.Count - currentIndex - 1;
var tokenArgs = string.Join(" ", words.GetRange(currentIndex + 1, count));
if (string.IsNullOrWhiteSpace(tokenArgs))
{
errors.Add(new SqlStatementError($"Expected arguments after {word}", 0));
break;
}
args.Add(word, tokenArgs);
}
currentIndex = nextTokenIndex ?? words.Count - 1;
}
else
{
if (rule.Args)
{
var tokenArgs = string.Join(" ", words.GetRange(currentIndex + 1, words.Count - currentIndex - 1));
if (string.IsNullOrWhiteSpace(tokenArgs))
{
errors.Add(new SqlStatementError($"Expected arguments after {word}", 0));
break;
}
args.Add(word, tokenArgs);
break;
}
}
}
}
foreach (var token in args.Keys)
{
var tokenPath = "select." + token;
var parsers = Configuration.SelectStatementConfiguration.Parsers().Where(x => string.Equals(x.TokenPath, tokenPath));
foreach (var parser in parsers)
{
var parseArgs = new ParseArgs <SelectStatement>()
{
Statement = selectStatement,
StatementArgs = args[token],
StatementsParser = this
};
var parseResults = tokenPath == "select.select"
? new SelectStatementTokenSelectParser().Parse(parseArgs)
: parser.Parse(parseArgs);
if (parseResults.Errors.Any())
{
errors.AddRange(parseResults.Errors);
}
}
}
var result = new Result <SelectStatement, SqlStatementError>(selectStatement)
{
Errors = errors
};
return(result);
}
public TokenParserResults Parse(ParseArgs <SelectStatement> parseArgs)
{
var selectStatement = parseArgs.Statement;
var args = parseArgs.StatementArgs;
var results = new TokenParserResults();
var words = ParserUtils.GetWords(args).ToList();
var segments = ParserUtils.SplitTokens(words);
foreach (var wordList in segments)
{
if (wordList.Any() == false)
{
results.Errors.Add(new SqlStatementError("No order clause specified", 0));
return(results);
}
SelectOrderBase orderColumn = null;
var startWord = wordList[0];
var number = 0;
if (int.TryParse(startWord, out number))
{
if (wordList.Count > 2)
{
results.Errors.Add(new SqlStatementError("Unknown tokens", 0));
return(results);
}
else if (wordList.Count == 2)
{
if (wordList[1] != "asc" && wordList[1] != "desc")
{
results.Errors.Add(new SqlStatementError("Expecting 'Asc' or 'Desc' keyword", 0));
return(results);
}
var sortDirection = wordList[1] == "asc" ? OrderDirection.Asc : OrderDirection.Desc;
orderColumn = new SelectOrderPosition(number, sortDirection);
}
else if (wordList.Count == 1)
{
orderColumn = new SelectOrderPosition(number);
}
}
else if (startWord.StartsWith("'") && startWord.EndsWith("'"))
{
results.Errors.Add(new SqlStatementError("A constant expression was encountered in the ORDER BY list", 0));
return(results);
}
else if (char.IsLetter(startWord[0]))
{
if (wordList.Count > 2)
{
results.Errors.Add(new SqlStatementError("Unknown tokens", 0));
return(results);
}
else if (wordList.Count == 2)
{
if (wordList[1] != "asc" && wordList[1] != "desc")
{
results.Errors.Add(new SqlStatementError("Expecting 'Asc' or 'Desc' keyword", 0));
return(results);
}
var sortDirection = wordList[1] == "asc" ? OrderDirection.Asc : OrderDirection.Desc;
orderColumn = new SelectOrderColumn(startWord, sortDirection);
}
else if (wordList.Count == 1)
{
orderColumn = new SelectOrderColumn(startWord);
}
}
if (orderColumn == null)
{
throw new NotSupportedException();
}
selectStatement.Order.Add(orderColumn);
}
return(results);
}
public TokenParserResults Parse(ParseArgs <SelectStatement> parseArgs)
{
var selectStatement = parseArgs.Statement;
var args = parseArgs.StatementArgs;
var results = new TokenParserResults();
var words = GetWords != null
? GetWords(args).ToList()
: ParserUtils.GetWords(args).ToList();
if (words.Any())
{
var currentGroup = new SelectWhereGroup();
var groupStack = new Stack <SelectWhereGroup>();
var clauseWords = new List <string>();
for (int i = 0; i < words.Count; i++)
{
var word = words[i];
if (word.In("(", ")", "and", "or"))
{
ParseClauseWords(clauseWords, currentGroup);
}
if (word == "(")
{
groupStack.Push(currentGroup);
var newGroup = new SelectWhereGroup();
currentGroup.Clauses.Add(newGroup);
currentGroup = newGroup;
}
else if (word == ")")
{
currentGroup = groupStack.Pop();
}
else if (word == "or")
{
var clauseOperator = new SelectWhereOperator(SelectWhereClauseOperatorEnum.Or);
currentGroup.Clauses.Add(clauseOperator);
}
else if (word == "and")
{
var clauseOperator = new SelectWhereOperator(SelectWhereClauseOperatorEnum.And);
currentGroup.Clauses.Add(clauseOperator);
}
else
{
clauseWords.Add(word);
}
}
ParseClauseWords(clauseWords, currentGroup);
selectStatement.Where = currentGroup.Clauses;
}
return(results);
}
internal Result <List <SqlStatement>, SqlStatementError> SplitIntoStatements(string sql)
{
var statements = new List <SqlStatement>();
var errors = new List <SqlStatementError>();
var inInlineComment = false;
var inMultilineComment = false;
var inQuotes = false;
var parenthesesCount = 0;
var trimStart = true;
var sourceIndex = -1;
var index = -1;
var keywords = new List <string>()
{
"select", "as", "order", "by", "asc", "desc", "group", "by",
"left", "right", "inner", "outer", "join", "on",
"delete",
"update", "set",
"from",
"where", "or", "and", "in", "between", "like"
};
var initStatementKeywords = new List <string>()
{
"select", "update", "delete"
};
var statement = new SqlStatement();
Action newStatement = () =>
{
if (string.IsNullOrWhiteSpace(statement.Sql) == false)
{
var sbFormatted = new StringBuilder();
foreach (var word in ParserUtils.GetWords(statement.Sql))
{
if (keywords.Any(keyword => string.Equals(keyword, word, StringComparison.OrdinalIgnoreCase)))
{
sbFormatted.Append(word.ToLowerInvariant());
}
else
{
sbFormatted.Append(word);
}
sbFormatted.Append(" ");
}
statement.Builder = sbFormatted;
statements.Add(statement);
}
statement = new SqlStatement();
index = -1;
trimStart = true;
};
for (int i = 0; i < sql.Length; i++)
{
sourceIndex++;
index++;
var c = sql[i];
if (trimStart)
{
if (c == ' ')
{
index--;
continue;
}
trimStart = false;
}
if (inMultilineComment)
{
if (c == '*')
{
var str = sql.Substring(i, 2);
if (str == "*/")
{
inMultilineComment = false;
i++;
sourceIndex++;
index--;
}
continue;
}
}
else
{
if (inQuotes == false)
{
if (c == '\n')
{
inInlineComment = false;
statement.Builder.Append(' ');
continue;
}
if (inInlineComment)
{
continue;
}
if (c == '/')
{
var str = sql.Substring(i, 2);
if (str == "/*")
{
inMultilineComment = true;
continue;
}
if (str == "//")
{
inInlineComment = true;
continue;
}
}
}
}
if (inMultilineComment)
{
index--;
continue;
}
if (c == '\'')
{
if (i == 0 || sql[i - 1] != '\\') // ignore if escaped
{
inQuotes = !inQuotes;
}
}
if (c == '(')
{
parenthesesCount++;
}
if (c == ')')
{
parenthesesCount--;
statement.Builder.Append(c);
continue;
}
if (parenthesesCount > 0)
{
statement.Builder.Append(c);
continue;
}
if (c == ';')
{
newStatement();
continue;
}
if (c == ' ' && ((i > 0 && sql[i - 1] == ' ') || (statement.Sql[statement.Sql.Length - 1] == ' ')))
{
index--;
continue;
}
if (c == '\t')
{
index--;
continue;
}
// if we reached here, we are not in a comment or string
if (inQuotes == false && char.IsLetter(c))
{
foreach (var keyword in initStatementKeywords)
{
if (i + keyword.Length > sql.Length)
{
continue;
}
var word = sql.Substring(i, keyword.Length);
if (string.Equals(word, keyword))
{
newStatement();
continue;
}
}
}
statement.Builder.Append(c);
if (index != sourceIndex)
{
//statement.Indexes.Add(new SqlIndex(index, sourceIndex));
}
}
if (statement != null && string.IsNullOrWhiteSpace(statement.Sql) == false)
{
newStatement();
}
var result = new Result <List <SqlStatement>, SqlStatementError>(statements)
{
Errors = errors
};
return(result);
}
private SelectColumnBase ParseColumn(List <string> wordList, List <SqlStatementError> errors)
{
var startWord = wordList[0];
var ascTokenIndex = 0;
var descTokenIndex = wordList.Count - 1;
var nextToken = wordList.Count > 1
? wordList[++ascTokenIndex]
: null;
if (nextToken == "(")
{
var functionName = startWord;
var functionLabel = "";
if (wordList[descTokenIndex] == ")")
{
functionLabel = startWord;
descTokenIndex--;
}
else
{
functionLabel = wordList[descTokenIndex];
descTokenIndex--;
if (wordList[descTokenIndex].NotIn(")", "as"))
{
throw new Exception($"Unexpected token after function '{wordList[wordList.Count - 2]}'");
}
if (wordList[descTokenIndex] == "as")
{
descTokenIndex--;
}
if (wordList[descTokenIndex] == ")")
{
descTokenIndex--;
}
}
var functionColumn = new SelectColumnFunction(functionName, functionLabel);
var argList = wordList.GetRange(2, descTokenIndex - 1);
var functionArgs = ParserUtils.SplitTokens(argList);
foreach (var functionArg in functionArgs)
{
functionColumn.Args.Add(ParseColumn(functionArg, errors));
}
return(functionColumn);
}
double number = 0;
SelectColumnBase selectColumn = null;
if (startWord.IsQuoted() || (double.TryParse(startWord, out number)))
{
object value = startWord.IsQuoted()
? startWord.CleanRaw()
: number.ToString();
var valueType = startWord.IsQuoted()
? typeof(string)
: typeof(double);
if (startWord.IsQuoted() == false && startWord.IndexOf(".", StringComparison.Ordinal) < 0)
{
valueType = typeof(int);
value = int.Parse(startWord);
}
if (wordList.Count > 3)
{
errors.Add(new SqlStatementError("Unknown tokens", 0));
return(null);
}
if (wordList.Count == 3)
{
if (wordList[1] != "as")
{
errors.Add(new SqlStatementError("Expecting 'As' keyword", 0));
return(null);
}
selectColumn = new RawSelectColumn(wordList[2], value)
{
ValueType = valueType
};
}
else if (wordList.Count == 2)
{
selectColumn = new RawSelectColumn(wordList[1], value)
{
ValueType = valueType
};
}
else
{
selectColumn = new RawSelectColumn(null, value)
{
ValueType = valueType
};
}
return(selectColumn);
}
if (char.IsLetter(startWord[0]))
{
if (wordList.Count > 3)
{
errors.Add(new SqlStatementError("Unknown tokens", 0));
return(null);
}
if (wordList.Count == 3)
{
if (wordList[1] != "as")
{
errors.Add(new SqlStatementError("Expecting 'As' keyword", 0));
return(null);
}
selectColumn = new FieldSelectColumn(wordList[2], startWord);
}
else if (wordList.Count == 2)
{
selectColumn = new FieldSelectColumn(wordList[1], startWord);
}
else
{
selectColumn = new FieldSelectColumn(null, startWord);
}
return(selectColumn);
}
throw new Exception("Unexpected function arguments.");
}
public TokenParserResults Parse(ParseArgs <SelectStatement> parseArgs)
{
var selectStatement = parseArgs.Statement;
var args = parseArgs.StatementArgs;
var statementParser = parseArgs.StatementsParser;
var results = new TokenParserResults();
var words = ParserUtils.GetWords(args).ToList();
var index = 0;
if (words.Any())
{
if (words[0] == "top")
{
if (words.Count >= 2)
{
var value = 0;
if (int.TryParse(words[1], out value))
{
selectStatement.Top = value;
index = 2;
}
else
{
results.Errors.Add(new SqlStatementError("Top expects a numerical value", 0));
}
}
else
{
results.Errors.Add(new SqlStatementError("Top without a numerical value", 0));
}
}
if (results.Errors.Any())
{
return(results);
}
var columns = ParserUtils.SplitTokens(words.GetRange(index, words.Count - index));
foreach (var wordList in columns)
{
if (wordList.Any() == false)
{
results.Errors.Add(new SqlStatementError("No column specified", 0));
return(results);
}
SelectColumnBase selectColumn = null;
var startWord = wordList[0];
double number = 0;
if (startWord.IsQuoted() || double.TryParse(startWord, out number))
{
object value = startWord.IsQuoted()
? startWord.CleanRaw()
: number.ToString();
var valueType = startWord.IsQuoted()
? typeof(string)
: typeof(double);
if (startWord.IsQuoted() == false && startWord.IndexOf(".", StringComparison.Ordinal) < 0)
{
valueType = typeof(int);
value = int.Parse(startWord);
}
if (wordList.Count > 3)
{
results.Errors.Add(new SqlStatementError("Unknown tokens", 0));
return(results);
}
if (wordList.Count == 3)
{
if (wordList[1] != "as")
{
results.Errors.Add(new SqlStatementError("Expecting 'As' keyword", 0));
return(results);
}
selectColumn = new RawSelectColumn(wordList[2], value)
{
ValueType = valueType
};
}
else if (wordList.Count == 2)
{
selectColumn = new RawSelectColumn(wordList[1], value)
{
ValueType = valueType
};
}
else
{
selectColumn = new RawSelectColumn(null, value)
{
ValueType = valueType
};
}
}
else if (startWord.StartsWith("*"))
{
if (wordList.Count > 1)
{
results.Errors.Add(new SqlStatementError("Found unsupported tokens after '*'", 0));
return(results);
}
selectColumn = new AllSelectColumn();
}
else if (startWord.StartsWith("+"))
{
if (wordList.Count > 1)
{
results.Errors.Add(new SqlStatementError("Found unsupported tokens after '+'", 0));
return(results);
}
selectColumn = new SystemSelectColumn();
}
else if (char.IsLetter(startWord[0]))
{
selectColumn = ParseColumn(wordList, results.Errors);
}
else if (startWord == "(")
{
var nextWord = wordList[1];
if (nextWord == "select")
{
var startIndex = 1;
var endIndex = wordList.FindLastIndex(x => x == ")");
var innerSql = string.Join(" ", wordList.GetRange(startIndex, endIndex - startIndex));
var statement = statementParser.ParseSelect(new SqlStatement()
{
Builder = new StringBuilder(innerSql)
});
if (statement.Errors.Any())
{
throw new Exception($"Unable to parse inner select statement '{innerSql}'");
}
var descTokenIndex = wordList.Count - 1;
var label = wordList[descTokenIndex];
descTokenIndex--;
if (wordList[descTokenIndex].NotIn(")", "as"))
{
throw new Exception($"Unexpected token after inner select '{innerSql}'");
}
if (wordList[descTokenIndex] == "as")
{
descTokenIndex--;
}
if (wordList[descTokenIndex] != ")")
{
throw new Exception($"Unexpected token after inner select '{innerSql}'");
}
selectColumn = new InnerSelectColumn(label, statement.Value);
}
}
if (selectColumn == null)
{
throw new NotSupportedException();
}
selectStatement.Columns.Add(selectColumn);
}
}
return(results);
}