public static void ParseSQL()
{
var sql = @"select AreaId = A.mcw_areaId, SurrogateKey = A.AreaKey, Code = S.statecode, Name = S.statename From CRM.dim_Area as A inner join CRM.dim_AreaState as S ON A.statecode = S.statecode ; DELET blogs SET url = 'aaa' where url='dasfds'";
//@"select p.firstname, p.lastname, p.custid FROM persons as p ;
//SELECT id, name FROM companies;
//select s.test from (select 'hello' as test) as s; ";
TSqlParser parser = new TSql120Parser(true);
IList <ParseError> parseErrors;
var tReader = new StringReader(sql);
TSqlFragment sqlFragment = parser.Parse(tReader, out parseErrors);
var queryTokens = parser.GetTokenStream(tReader, out parseErrors);
if (parseErrors.Count > 0)
{
Console.WriteLine("Errors:");
}
parseErrors.Select(e => e.Message.Indent(2)).ToList().ForEach(Console.WriteLine);
OwnVisitor visitor = new OwnVisitor();
sqlFragment.Accept(visitor);
// sqlFragment.AcceptChildren(visitor);
Console.WriteLine("Done.");
Console.ReadKey();
}
private static List <TSqlParserToken> TokenizeSql(string sql, out List <string> parserErrors)
{
using (TextReader textReader = new StringReader(sql))
{
var parser = new TSql120Parser(true);
IList <ParseError> errors;
var queryTokens = parser.GetTokenStream(textReader, out errors);
if (errors.Any())
{
parserErrors = errors.Select(e => $"Error: {e.Number}; Line: {e.Line}; Column: {e.Column}; Offset: {e.Offset}; Message: {e.Message};").ToList();
}
else
{
parserErrors = null;
}
return(queryTokens.ToList());
}
}
private List <string> ParseFieldValues(string fieldValueString)
{
List <string> result = new List <string>();
var sqlParser = new TSql120Parser(false);
IList <ParseError> errors;
using (var reader = new System.IO.StringReader(fieldValueString)) {
var queryTokens = sqlParser.GetTokenStream(reader, out errors);
var tokens = queryTokens.Where(q => q.TokenType == TSqlTokenType.AsciiStringLiteral || q.TokenType == TSqlTokenType.Null || q.TokenType == TSqlTokenType.Identifier || q.TokenType == TSqlTokenType.Integer || q.TokenType == TSqlTokenType.Numeric || q.TokenType == TSqlTokenType.UnicodeStringLiteral);
foreach (var token in tokens)
{
result.Add(GetFieldText(token.Text));
}
return(result);
}
}
private List <string> ParseColumnNames(string columnNameString)
{
List <string> result = new List <string>();
var sqlParser = new TSql120Parser(false);
IList <ParseError> errors;
using (var reader = new System.IO.StringReader(columnNameString)) {
var queryTokens = sqlParser.GetTokenStream(reader, out errors);
var tokens = queryTokens.Where(q => q.TokenType == TSqlTokenType.QuotedIdentifier || q.TokenType == TSqlTokenType.AsciiStringOrQuotedIdentifier || q.TokenType == TSqlTokenType.Identifier);
foreach (var token in tokens)
{
result.Add(GetColumnName(token.Text));
}
return(result);
}
}
private string GetComments(string oldScriptBlock)
{
var comments = new StringBuilder();
var parser = new TSql120Parser(false);
IList <ParseError> errors;
var tokens = parser.GetTokenStream(new StringReader(oldScriptBlock), out errors);
foreach (var token in tokens)
{
switch (token.TokenType)
{
case TSqlTokenType.MultilineComment:
case TSqlTokenType.SingleLineComment:
comments.AppendLine(token.Text);
break;
}
}
return(comments.ToString());
}
private string GetComments(string oldScriptBlock)
{
var comments = new StringBuilder();
var parser = new TSql120Parser(false);
IList<ParseError> errors;
var tokens = parser.GetTokenStream(new StringReader(oldScriptBlock), out errors);
foreach (var token in tokens)
{
switch (token.TokenType)
{
case TSqlTokenType.MultilineComment:
case TSqlTokenType.SingleLineComment:
comments.AppendLine(token.Text);
break;
}
}
return comments.ToString();
}
public SqlParsedInfo BradFunction(string sqlQuery)
{
// Remove spaces between strings/words, and remove trailing/ending spaces
sqlQuery = sqlQuery.Trim();
Regex regex = new Regex(@"[ ]{2,}"); // Remove spaces greater than two
sqlQuery = regex.Replace(sqlQuery, @" ");
var sqlParser = new TSql120Parser(true);
TextReader tReader = new StringReader(sqlQuery.Trim());
IList <ParseError> sqlParseErrors;
int index = 0;
IList <TSqlParserToken> tokens;
// Loop through all the tokens
tokens = sqlParser.GetTokenStream(tReader, out sqlParseErrors);
index = FindColumns(tokens, index + 1); // Assume we will skip the SELECT statement
index = FindTableName(tokens, index);
index = FindContraintsAfterWhere(tokens, index);
return(SqlParsedInfoObject);
}
//Method used to identify table names contained within query string passed in as a parameter
public static List <outputstruct> GetTableNamesFromQueryString(string query)
{
outputstruct outputdata = new outputstruct();
var output = new List <outputstruct>();
var sb = new StringBuilder();
var parser = new TSql120Parser(true);
var fromTokenTypes = new[]
{
TSqlTokenType.From,
TSqlTokenType.Join
};
var identifierTokenTypes = new[]
{
TSqlTokenType.Identifier,
TSqlTokenType.QuotedIdentifier
};
using (System.IO.TextReader tReader = new System.IO.StringReader(query))
{
IList <ParseError> errors;
//queryTokens contains separate element for each word in query
var queryTokens = parser.GetTokenStream(tReader, out errors);
//Identify start of FROM clause by matching to fromTokenTypes
for (var i = 0; i < queryTokens.Count; i++)
{
int newclause = 1;
if (fromTokenTypes.Contains(queryTokens[i].TokenType))
{
if (outputdata.DB != null)
{
output.Add(outputdata);
outputdata.DB = null;
outputdata.Table = null;
outputdata.Owner = null;
outputdata.Alias = null;
}
//Inner loop to identify specific tables and aliases included in FROM clause
//If encountered another "FROM" clause or the start of the "WHERE" or "GROUP" or "ORDER" clause, then have reached end of FROM
for (var j = i + 1; j < queryTokens.Count; j++)
{
if (fromTokenTypes.Contains(queryTokens[j].TokenType))
{
break;
}
if (queryTokens[j].Text.ToUpper() == "WHERE" || queryTokens[j].Text.ToUpper() == "GROUP" || queryTokens[j].Text.ToUpper() == "ORDER" || queryTokens[j].Text.ToUpper() == "ON")
{
break;
}
if (queryTokens[j].TokenType == TSqlTokenType.WhiteSpace)
{
continue;
}
//User supplied value that is not a reserved word such as "JOIN" or "ON". Indicates table, alias, or column name
if (identifierTokenTypes.Contains(queryTokens[j].TokenType))
{
sb.Clear();
GetQuotedIdentifier(queryTokens[j], sb);
//query can use format mdyb..tablename, and skip providing the middle parameter is owner. This can be identified by 2 successive "."
if (queryTokens[j + 1].TokenType == TSqlTokenType.Dot && queryTokens[j + 2].TokenType == TSqlTokenType.Dot) //DBName with no owner
{
outputdata.DB = sb.ToString();
outputdata.Owner = "Null";
sb.Clear();
//mydb..tablename, the table name in this case is 3 places out from the db name
GetQuotedIdentifier(queryTokens[j + 3], sb);
outputdata.Table = sb.ToString();
sb.Clear();
outputdata.Alias = "Null";
newclause = 0;
//move to next argument
j = j + 4;
}
//DBName.Owner.TableName
if (queryTokens[j + 1].TokenType == TSqlTokenType.Dot && queryTokens[j + 3].TokenType == TSqlTokenType.Dot) //DBName with owner
{
outputdata.DB = sb.ToString();
sb.Clear();
GetQuotedIdentifier(queryTokens[j + 2], sb);
outputdata.Owner = sb.ToString();
sb.Clear();
GetQuotedIdentifier(queryTokens[j + 4], sb);
outputdata.Table = sb.ToString();
newclause = 0;
sb.Clear();
outputdata.Alias = "Null";
j = j + 5;
}
//Owner.TableName
if (queryTokens[j + 1].TokenType == TSqlTokenType.Dot && queryTokens[j + 3].TokenType == TSqlTokenType.WhiteSpace) //No DBName with owner
{
outputdata.DB = "Null";
outputdata.Owner = sb.ToString();
sb.Clear();
GetQuotedIdentifier(queryTokens[j + 2], sb);
outputdata.Table = sb.ToString();
newclause = 0;
sb.Clear();
outputdata.Alias = "Null";
j = j + 3;
}
//check for tablename without any prefix. Need to verify if this is an alias from previouly identified table
//by looking at "newclause" variable. This will be set to 1 if new clause or 0 if appending on as alias
if (queryTokens[j - 1].TokenType == TSqlTokenType.WhiteSpace && queryTokens[j + 1].TokenType == TSqlTokenType.WhiteSpace)
{
if (newclause == 0) //must be alias name since still part of same From Clause item
{
outputdata.Alias = sb.ToString();
sb.Clear();
j = j + 1;
newclause = 1;
}
else //new item in From clause, must be table name
{
outputdata.DB = "Null";
outputdata.Owner = "Null";
outputdata.Table = sb.ToString();
outputdata.Alias = "Null";
newclause = 0;
sb.Clear();
j = j + 1;
}
}
// break;
}
}
}
}
if (outputdata.DB != null)
{
output.Add(outputdata);
outputdata.DB = null;
outputdata.Table = null;
outputdata.Owner = null;
outputdata.Alias = null;
}
}
return(output);
}
/*GetFilterCriteriaFromQueryString method takes input as parameters, query string and the list of tables identified previously in
* the GetTableNamesFromQueryString method */
public static List <wherestruct> GetFilterCriteriaFromQueryString(string query, List <outputstruct> tablelist)
{
wherestruct outputdata = new wherestruct();
var output = new List <wherestruct>();
var sb = new StringBuilder();
var parser = new TSql120Parser(true);
//Identifier types used to recognize specific elements in query which are not reserved keywords such as "SELECT", "FROM", "JOIN"
//If an Identifier type is found in query string, it is part of a user supplied search value or table name
var identifierTokenTypes = new[]
{
TSqlTokenType.Identifier,
TSqlTokenType.QuotedIdentifier,
TSqlTokenType.Integer,
TSqlTokenType.AsciiStringLiteral
};
var whereTokenTypes = new[]
{
TSqlTokenType.Where,
TSqlTokenType.And
};
var ComparisonOperators = new[]
{
TSqlTokenType.In,
TSqlTokenType.EqualsSign,
TSqlTokenType.Not,
TSqlTokenType.GreaterThan,
TSqlTokenType.LessThan,
TSqlTokenType.Like
};
using (System.IO.TextReader tReader = new System.IO.StringReader(query))
{
IList <ParseError> errors;
/*Each query token represents a distinct word in the query. Below FOR loop identifies start of where clause
* by matchint to "WhereTokenTypes" array and then from there an inner loop is used to identify specific
* search arguments in WHERE clause*/
var queryTokens = parser.GetTokenStream(tReader, out errors);
for (var i = 0; i < queryTokens.Count; i++)
{
if (whereTokenTypes.Contains(queryTokens[i].TokenType))
{
if (outputdata.Table != null)
{
output.Add(outputdata);
outputdata.Table = null;
outputdata.Column = null;
outputdata.comparison_operator = null;
outputdata.comparison_value = null;
outputdata.function_name = null;
outputdata.function_string = null;
}
//Identify individual search arguments
for (var j = i + 1; j < queryTokens.Count; j++)
{
if (queryTokens[j].Text == null)
{
break;
}
//if function used, need to get whole function call
if (queryTokens[j].Text == "(")
{
int start = j - 1;
int end = j + 1;
bool _continue = false;
while (queryTokens[start].Text != outputdata.comparison_operator)
{
//looking for quoted identifier "" or [] so that if we see a space in the middle, continue on and don't break out
if (queryTokens[start].Text == "\"" || queryTokens[start].Text == "[" || queryTokens[start].Text == "]")
{
if (_continue == false)
{
_continue = true;
}
else
{
_continue = false;
}
}
if (queryTokens[start].Text == " " && _continue == false)
{
break;
}
start += -1;
}
_continue = false;
while (end < queryTokens.Count || !ComparisonOperators.Contains(queryTokens[end].TokenType))
{
//looking for quoted identifier "" or [] so that if we see a space in the middle, continue on and don't break out
if (queryTokens[end].Text == "\"" || queryTokens[end].Text == "[" || queryTokens[end].Text == "]")
{
if (_continue == false)
{
_continue = true;
}
else
{
_continue = false;
}
}
if (queryTokens[end].Text == " " && _continue == false)
{
break;
}
end += 1;
}
string _result = "";
for (i = start + 1; i < end; i++)
{
_result = _result + queryTokens[i].Text;
}
outputdata.function_string = _result;
if (_result.Contains("."))
{
QueryContext context = new QueryContext();
string[] _temp = _result.Split('.');
string alias = getNestedColumn(_temp[0], "reverse");
var tbl =
from c in context.Tables
join _query in context.Query
on c.queryID equals _query.ID
where _query.QueryText == query &&
(c.AliasName.Replace("[", "").Replace("]", "") == alias ||
c.TableName.Replace("[", "").Replace("]", "") == alias)
select c.TableName;
outputdata.Table = tbl.First().ToString();
outputdata.Column = getNestedColumn(_temp[1], "forward");
}
j = end;
}
//Reached end of WHERE query if reached the GROUP BY or ORDER BY clauses
if (queryTokens[j].Text.ToUpper() == "GROUP" || queryTokens[j].Text.ToUpper() == "ORDER" || queryTokens[j].Text.ToUpper() == "AND")
{
break;
}
// outputlist.Clear();
if (queryTokens[j].TokenType == TSqlTokenType.WhiteSpace)
{
continue;
}
//Checking if search argument by matching to Identifier or comparison operator
if (identifierTokenTypes.Contains(queryTokens[j].TokenType) || ComparisonOperators.Contains(queryTokens[j].TokenType))
{
sb.Clear();
//Provided Quoted Identifier "[]" for user supplied value such as table name, etc. But not for comparison operator
//Maybe the [] are not needed to pass through query to document db. If not, we can comment this part out.
if (identifierTokenTypes.Contains(queryTokens[j].TokenType) && queryTokens[j].TokenType != TSqlTokenType.Integer && queryTokens[j].TokenType != TSqlTokenType.AsciiStringLiteral)
{
GetQuotedIdentifier(queryTokens[j], sb);
}
//case where alias and column provided in format "alias.column"
if (queryTokens[j + 1].TokenType == TSqlTokenType.Dot && identifierTokenTypes.Contains(queryTokens[j + 2].TokenType)) //Alias or Table . ColumnName
{
foreach (outputstruct v in tablelist)
{
if (v.Table == sb.ToString())
{
outputdata.Table = v.Table;
break;
}
//If query is using Alias in Where clause, need to sub in Table Name from outputstruct struct
if (v.Alias == sb.ToString())
{
outputdata.Table = v.Table;
break;
}
}
//If above check failed to identify a table name, then need to break out of loop and move to next argument
if (outputdata.Table == null)
{
break;
}
//If format is alias.columnname, then column is 2 positions "J+2" ahead of alias identified above
else
{
outputdata.Column = queryTokens[j + 2].Text;
//Move ahead 3 spots to look for next search argument
j += 3;
}
}
//Check if argument is a comparison operator (=,<,>,in,like)
if (ComparisonOperators.Contains(queryTokens[j].TokenType))
{
outputdata.comparison_operator = queryTokens[j].Text;
j += 1;
}
//If match identifiertokentype, this is user supplied value, so must be value being compared to
//logic assumes that a user supplied value with "." is alias.column. If no "." included, must be a search value
if (identifierTokenTypes.Contains(queryTokens[j].TokenType) && outputdata.comparison_operator != null)
{
/* if (outputdata.comparison_value != null)
* {
* outputdata.comparison_value = String.Concat(outputdata.comparison_value, ",", queryTokens[j].Text);
* }
* else
* {
* outputdata.comparison_value = queryTokens[j].Text;
* }
*/
while (queryTokens[j].Text != " " && queryTokens[j].Text != null)
{
outputdata.comparison_value += queryTokens[j].Text;
j += 1;
}
}
}
}
}
}
}
//After exiting FOR Loop above, need to insert remaining outputdata struct into the output list
if (outputdata.Table != null)
{
output.Add(outputdata);
outputdata.Table = null;
outputdata.Column = null;
outputdata.comparison_operator = null;
outputdata.comparison_value = null;
outputdata.function_name = null;
outputdata.function_string = null;
}
return(output);
}
public static List <string> GetTableNamesFromQueryString(string query)
{
var output = new List <string>();
var sb = new StringBuilder();
var parser = new TSql120Parser(true);
var fromTokenTypes = new[]
{
TSqlTokenType.From,
TSqlTokenType.Join
};
var identifierTokenTypes = new[]
{
TSqlTokenType.Identifier,
TSqlTokenType.QuotedIdentifier
};
using (System.IO.TextReader tReader = new System.IO.StringReader(query))
{
var queryTokens = parser.GetTokenStream(tReader, out IList <ParseError> errors);
if (errors.Any())
{
return(errors
.Select(e => string.Format("Error: {0}; Line: {1}; Column: {2}; Offset: {3}; Message: {4};", e.Number, e.Line, e.Column, e.Offset, e.Message))
.ToList());
}
for (var i = 0; i < queryTokens.Count; i++)
{
if (fromTokenTypes.Contains(queryTokens[i].TokenType))
{
for (var j = i + 1; j < queryTokens.Count; j++)
{
if (queryTokens[j].TokenType == TSqlTokenType.WhiteSpace)
{
continue;
}
if (identifierTokenTypes.Contains(queryTokens[j].TokenType))
{
sb.Clear();
GetQuotedIdentifier(queryTokens[j], sb);
while (j + 2 < queryTokens.Count &&
queryTokens[j + 1].TokenType == TSqlTokenType.Dot &&
(queryTokens[j + 2].TokenType == TSqlTokenType.Dot || identifierTokenTypes.Contains(queryTokens[j + 2].TokenType)))
{
sb.Append(queryTokens[j + 1].Text);
if (queryTokens[j + 2].TokenType == TSqlTokenType.Dot)
{
if (queryTokens[j - 1].TokenType == TSqlTokenType.Dot)
{
GetQuotedIdentifier(queryTokens[j + 1], sb);
}
j++;
}
else
{
GetQuotedIdentifier(queryTokens[j + 2], sb);
j += 2;
}
}
output.Add(sb.ToString());
}
break;
}
}
}
return(output.Distinct().OrderBy(tableName => tableName).ToList());
}
}