/// <summary>
/// Read tokens, get the first column name, handle . character
/// </summary>
/// <param name="tokens"></param>
/// <returns>column object that contains table name and column name</returns>
public static Column GetQualifiedColumnName(ref List <TSQLToken> tokens)
{
TSQLToken possibleColName = tokens[0];
tokens.RemoveAt(0);
if (tokens.Count != 0 && tokens[0].Text == ".")
{
tokens.RemoveAt(0);
TSQLToken actualColName = tokens[0];
tokens.RemoveAt(0);
string tableName = possibleColName.Text;
return(new Column
{
ColumnName = actualColName.Text,
TableName = tableName,
_TokenType = possibleColName.Type
});
}
return(new Column
{
ColumnName = possibleColName.Text,
_TokenType = possibleColName.Type
});
}
public bool MoveNext()
{
CheckDisposed();
_current = null;
if (_hasMore)
{
if (IncludeWhitespace)
{
_hasMore = _charReader.Read();
}
else
{
_hasMore = _charReader.ReadNextNonWhitespace();
}
if (_hasMore)
{
SetCurrent();
}
}
return(_hasMore);
}
public static void CompareTokens(TSQLToken expected, TSQLToken actual)
{
Assert.AreEqual(expected.BeginPosition, actual.BeginPosition, "Token begin position does not match.");
Assert.AreEqual(expected.EndPosition, actual.EndPosition, "Token end position does not match.");
Assert.AreEqual(expected.Text, actual.Text, "Token text does not match.");
Assert.AreEqual(expected.GetType(), actual.GetType());
}
private static QueryType GetQueryType(TSQLToken token)
{
QueryType queryType;
switch (token.Text.ToUpper())
{
case "SELECT":
queryType = QueryType.Select;
break;
case "INSERT":
queryType = QueryType.Insert;
break;
case "UPDATE":
queryType = QueryType.Update;
break;
case "DELETE":
queryType = QueryType.Delete;
break;
default:
return(QueryType.Unknown);
}
return(queryType);
}
private string GetFunctionExpression(TSQLToken token, string definition)
{
int startIndex = startIndex = token.BeginPosition;
int functionEndIndex = functionEndIndex = this.FindFunctionEndIndex(startIndex + token.Text.Length, definition);
string functionExpression = null;
if (functionEndIndex != -1)
{
functionExpression = definition.Substring(startIndex, functionEndIndex - startIndex + 1);
}
return(functionExpression);
}
/// <summary>
/// Remove first item and check if it is same as keyword
/// </summary>
/// <param name="tokens"></param>
/// <param name="keyword"></param>
public static void PopAndCheck(ref List <TSQLToken> tokens, string keyword)
{
if (tokens.Count == 0)
{
throw new Exception("tokens size is 0");
}
TSQLToken first = tokens[0];
tokens.RemoveAt(0);
if (first.Text.ToLower() != keyword)
{
throw new Exception(string.Format("{0} != {1}", first.Text, keyword));
}
}
/// <summary>
/// Read tokens, get the table name, handle . character
/// </summary>
/// <param name="tokens"></param>
/// <returns>table object that contains table name and database name</returns>
public static Table GetQualifiedTableName(ref List <TSQLToken> tokens, string connectedDB)
{
TSQLToken possibleColName = tokens[0];
tokens.RemoveAt(0);
if (tokens.Count != 0 && tokens[0].Text == ".")
{
tokens.RemoveAt(0);
TSQLToken actualColName = tokens[0];
tokens.RemoveAt(0);
string databaseName = possibleColName.Text;
return(new Table(actualColName.Text, databaseName));
}
return(new Table(possibleColName.Text, connectedDB));
}
public ITSQLStatementParser Create(TSQLToken token)
{
if (token.Type == TSQLTokenType.Keyword)
{
TSQLKeywords keyword = token.AsKeyword.Keyword;
if (keyword == TSQLKeywords.SELECT)
{
return(new TSQLSelectStatementParser());
}
// not fully implemented yet
//else if (keyword == TSQLKeywords.INSERT)
//{
// return new TSQLInsertStatementParser();
//}
//else if (keyword == TSQLKeywords.UPDATE)
//{
// return new TSQLUpdateStatementParser();
//}
//else if (keyword == TSQLKeywords.DELETE)
//{
// return new TSQLDeleteStatementParser();
//}
//else if (keyword == TSQLKeywords.MERGE)
//{
// return new TSQLMergeStatementParser();
//}
//else if (keyword == TSQLKeywords.WITH)
//{
// return new TSQLWithAggregateStatementParser();
//}
else
{
return(new TSQLUnknownStatementParser());
}
}
else
{
return(new TSQLUnknownStatementParser());
// not fully implemented yet
//return new TSQLExecuteStatementParser();
}
}
/// <summary>
/// Go rules:
/// can be first word followed by comments
/// cannot be first full word on line along with other script.
/// cannot be multiple go on same line.
/// may be last full word on line: remove just that word
/// Can be last full word on line followed only by comment
/// </summary>
/// <param name="script">Script contains go (not just naive check, go is in _statements)</param>
/// <returns></returns>
private ImmutableList <string> ProcessStatements(string script)
{
int pos = 0;
foreach (TSQLStatement statement in _statements)
{
TSQLToken goToken = statement.Tokens.FirstOrDefault(token => TokenContainsGo(token));
if (goToken != null)
{
_results.Add(script.Substring(pos, goToken.BeginPosition - pos));
pos = goToken.EndPosition + 1;
}
}
if (pos < script.Length) // if last thing was go, this won't enter
{
_results.Add(script.Substring(pos, script.Length - pos));
}
return(ImmutableList.Create(_results.ToArray()));
}
public ITSQLStatementParser Create(TSQLToken token)
{
if (token.Type == TSQLTokenType.Keyword)
{
TSQLKeywords keyword = token.AsKeyword.Keyword;
if (keyword == TSQLKeywords.SELECT)
{
return(new TSQLSelectStatementParser());
}
else
{
return(new TSQLUnknownStatementParser());
}
}
else
{
return(new TSQLUnknownStatementParser());
// TODO: check for an EXEC without the keyword
}
}
private static bool SkipToken(TSQLToken token) => token.Type == TSQLTokenType.Identifier || (token.Type == TSQLTokenType.Keyword && token.Text.ToUpper(CultureInfo.InvariantCulture) == "AS");
private static bool TokenContainsGo(TSQLToken token) => token.AsKeyword == null ? false : token.AsKeyword.Text.Equals(GO, StringComparison.CurrentCultureIgnoreCase);
private void SetCurrent()
{
characterHolder.Length = 0;
// TODO: review Position property viability for situations like network streams
int startPosition = _charReader.Position;
if (
IncludeWhitespace &&
char.IsWhiteSpace(_charReader.Current))
{
do
{
characterHolder.Append(_charReader.Current);
} while (
_charReader.Read() &&
char.IsWhiteSpace(_charReader.Current));
if (!_charReader.EOF)
{
_charReader.Putback();
}
}
else
{
characterHolder.Append(_charReader.Current);
switch (_charReader.Current)
{
// period can signal the start of a numeric literal if followed by a number
case '.':
{
if (_charReader.Read())
{
if (
_charReader.Current == '0' ||
_charReader.Current == '1' ||
_charReader.Current == '2' ||
_charReader.Current == '3' ||
_charReader.Current == '4' ||
_charReader.Current == '5' ||
_charReader.Current == '6' ||
_charReader.Current == '7' ||
_charReader.Current == '8' ||
_charReader.Current == '9'
)
{
characterHolder.Append(_charReader.Current);
goto case '0';
}
else
{
_charReader.Putback();
}
}
break;
}
// all single character sequences with no optional double character sequence
case ',':
case ';':
case '(':
case ')':
case '~':
{
break;
}
// --
// -=
// -
case '-':
{
if (_charReader.Read())
{
if (_charReader.Current == '-')
{
do
{
characterHolder.Append(_charReader.Current);
} while (
_charReader.Read() &&
_charReader.Current != '\r' &&
_charReader.Current != '\n');
if (!_charReader.EOF)
{
_charReader.Putback();
}
}
else if (_charReader.Current == '=')
{
characterHolder.Append(_charReader.Current);
}
else
{
_charReader.Putback();
}
}
break;
}
// /* */
// /=
// /
case '/':
{
if (_charReader.Read())
{
if (_charReader.Current == '*')
{
characterHolder.Append(_charReader.Current);
// supporting nested comments
int currentLevel = 1;
bool lastWasStar = false;
bool lastWasSlash = false;
while (
_charReader.Read() &&
(
currentLevel > 1 ||
// */
!(
lastWasStar &&
_charReader.Current == '/'
)
))
{
// /*
if (
lastWasSlash &&
_charReader.Current == '*')
{
currentLevel++;
lastWasSlash = false;
lastWasStar = false;
}
// */
else if (
lastWasStar &&
_charReader.Current == '/')
{
currentLevel--;
lastWasSlash = false;
lastWasStar = false;
}
else
{
lastWasSlash = _charReader.Current == '/';
lastWasStar = _charReader.Current == '*';
}
characterHolder.Append(_charReader.Current);
}
if (!_charReader.EOF)
{
characterHolder.Append(_charReader.Current);
}
}
else if (_charReader.Current == '=')
{
characterHolder.Append(_charReader.Current);
}
else
{
_charReader.Putback();
}
}
break;
}
// <>
// <=
// <
case '<':
{
if (_charReader.Read())
{
if (
_charReader.Current == '>' ||
_charReader.Current == '='
)
{
characterHolder.Append(_charReader.Current);
}
else
{
_charReader.Putback();
}
}
break;
}
// !=
// !<
// !>
case '!':
{
if (_charReader.Read())
{
if (
_charReader.Current == '=' ||
_charReader.Current == '<' ||
_charReader.Current == '>'
)
{
characterHolder.Append(_charReader.Current);
}
else
{
_charReader.Putback();
}
}
break;
}
// =*
// =
case '=':
{
if (_charReader.Read())
{
if (
_charReader.Current == '*'
)
{
characterHolder.Append(_charReader.Current);
}
else
{
_charReader.Putback();
}
}
break;
}
// &=
case '&':
// |=
case '|':
// ^=
case '^':
// +=
case '+':
// *=
case '*':
// %=
case '%':
// >=
case '>':
{
if (_charReader.Read())
{
if (_charReader.Current == '=')
{
characterHolder.Append(_charReader.Current);
}
else
{
_charReader.Putback();
}
}
break;
}
// N''
case 'N':
{
if (_charReader.Read())
{
if (_charReader.Current == '\'')
{
characterHolder.Append(_charReader.Current);
goto case '\'';
}
else
{
_charReader.Putback();
goto default;
}
}
break;
}
// ::
case ':':
{
if (_charReader.Read())
{
if (_charReader.Current == ':')
{
characterHolder.Append(_charReader.Current);
}
else
{
_charReader.Putback();
}
}
break;
}
// ''
case '\'':
// ""
case '\"':
// [dbo]
case '[':
{
char escapeChar;
if (_charReader.Current == '[')
{
escapeChar = ']';
}
else
{
escapeChar = _charReader.Current;
}
bool stillEscaped;
// read until '
// UNLESS the ' is doubled up
do
{
while (
_charReader.Read() &&
_charReader.Current != escapeChar)
{
characterHolder.Append(_charReader.Current);
}
if (!_charReader.EOF)
{
characterHolder.Append(_charReader.Current);
}
stillEscaped =
!_charReader.EOF &&
_charReader.Read() &&
_charReader.Current == escapeChar;
if (stillEscaped)
{
characterHolder.Append(_charReader.Current);
}
} while (stillEscaped);
if (!_charReader.EOF)
{
_charReader.Putback();
}
break;
}
// 0 can start a numeric or binary literal with different parsing logic
// 0x69048AEFDD010E
// 0x
case '0':
{
if (_charReader.Read())
{
if (
_charReader.Current == 'x' ||
_charReader.Current == 'X')
{
characterHolder.Append(_charReader.Current);
bool foundEnd = false;
while (
!foundEnd &&
_charReader.Read())
{
switch (_charReader.Current)
{
case '0':
case '1':
case '2':
case '3':
case '4':
case '5':
case '6':
case '7':
case '8':
case '9':
case 'a':
case 'b':
case 'c':
case 'd':
case 'e':
case 'f':
case 'A':
case 'B':
case 'C':
case 'D':
case 'E':
case 'F':
{
characterHolder.Append(_charReader.Current);
break;
}
// backslash line continuation
// https://docs.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-backslash?view=sql-server-2017
case '\\':
{
characterHolder.Append(_charReader.Current);
if (
!foundEnd &&
_charReader.Read())
{
// should be \r or \n
characterHolder.Append(_charReader.Current);
if (_charReader.Current == '\r')
{
if (
!foundEnd &&
_charReader.Read())
{
// should be \n
characterHolder.Append(_charReader.Current);
}
}
}
break;
}
default:
{
foundEnd = true;
break;
}
}
}
if (foundEnd)
{
_charReader.Putback();
}
}
else
{
_charReader.Putback();
goto case '1';
}
}
break;
}
// numeric literals
// 1894.1204
// 0.5E-2
// 123E-3
case '1':
case '2':
case '3':
case '4':
case '5':
case '6':
case '7':
case '8':
case '9':
{
bool foundEnd = false;
bool foundPeriod = false;
while (
!foundEnd &&
_charReader.Read())
{
switch (_charReader.Current)
{
case 'e':
case 'E':
{
characterHolder.Append(_charReader.Current);
if (_charReader.Read())
{
switch (_charReader.Current)
{
case '-':
case '+':
{
characterHolder.Append(_charReader.Current);
break;
}
default:
{
_charReader.Putback();
break;
}
}
}
while (
!foundEnd &&
_charReader.Read())
{
switch (_charReader.Current)
{
case '0':
case '1':
case '2':
case '3':
case '4':
case '5':
case '6':
case '7':
case '8':
case '9':
{
characterHolder.Append(_charReader.Current);
break;
}
default:
{
foundEnd = true;
break;
}
}
}
break;
}
case '.':
{
if (foundPeriod)
{
foundEnd = true;
}
else
{
characterHolder.Append(_charReader.Current);
foundPeriod = true;
}
break;
}
case '0':
case '1':
case '2':
case '3':
case '4':
case '5':
case '6':
case '7':
case '8':
case '9':
{
characterHolder.Append(_charReader.Current);
break;
}
// running into a special character signals the end of a previous grouping of normal characters
default:
{
foundEnd = true;
break;
}
}
}
if (foundEnd)
{
_charReader.Putback();
}
break;
}
// $45.56
// $IDENTITY
case '$':
{
if (_charReader.Read())
{
if (
_charReader.Current == '-' ||
_charReader.Current == '+' ||
_charReader.Current == '.' ||
_charReader.Current == '0' ||
_charReader.Current == '1' ||
_charReader.Current == '2' ||
_charReader.Current == '3' ||
_charReader.Current == '4' ||
_charReader.Current == '5' ||
_charReader.Current == '6' ||
_charReader.Current == '7' ||
_charReader.Current == '8' ||
_charReader.Current == '9'
)
{
_charReader.Putback();
goto case '£';
}
else
{
_charReader.Putback();
goto default;
}
}
break;
}
// other Unicode currency symbols recognized by SSMS
case '£':
case '¢':
case '¤':
case '¥':
case '€':
case '₡':
case '₱':
case '﷼':
case '₩':
case '₮':
case '₨':
case '₫':
case '฿':
case '៛':
case '₪':
case '₭':
case '₦':
case '৲':
case '৳':
case '﹩':
case '₠':
case '₢':
case '₣':
case '₤':
case '₥':
case '₧':
case '₯':
case '₰':
case '＄':
case '￠':
case '￡':
case '￥':
case '￦':
{
bool foundEnd = false;
bool foundPeriod = false;
if (_charReader.Read())
{
switch (_charReader.Current)
{
case '-':
case '+':
{
characterHolder.Append(_charReader.Current);
break;
}
default:
{
_charReader.Putback();
break;
}
}
}
while (
!foundEnd &&
_charReader.Read())
{
switch (_charReader.Current)
{
case '.':
{
if (foundPeriod)
{
foundEnd = true;
}
else
{
characterHolder.Append(_charReader.Current);
foundPeriod = true;
}
break;
}
case '0':
case '1':
case '2':
case '3':
case '4':
case '5':
case '6':
case '7':
case '8':
case '9':
{
characterHolder.Append(_charReader.Current);
break;
}
default:
{
foundEnd = true;
break;
}
}
}
if (foundEnd)
{
_charReader.Putback();
}
break;
}
default:
{
bool foundEnd = false;
while (
!foundEnd &&
_charReader.Read())
{
switch (_charReader.Current)
{
// running into a special character signals the end of a previous grouping of normal characters
case ' ':
case '\t':
case '\r':
case '\n':
case '.':
case ',':
case ';':
case '(':
case ')':
case '+':
case '-':
case '*':
case '=':
case '/':
case '<':
case '>':
case '!':
case '%':
case '^':
case '&':
case '|':
case '~':
case ':':
case '[':
// Backslash (Line Continuation)
case '\\':
case '£':
case '¢':
case '¤':
case '¥':
case '€':
case '₡':
case '₱':
case '﷼':
case '₩':
case '₮':
case '₨':
case '₫':
case '฿':
case '៛':
case '₪':
case '₭':
case '₦':
case '৲':
case '৳':
case '﹩':
case '₠':
case '₢':
case '₣':
case '₤':
case '₥':
case '₧':
case '₯':
case '₰':
case '＄':
case '￠':
case '￡':
case '￥':
case '￦':
{
foundEnd = true;
break;
}
default:
{
characterHolder.Append(_charReader.Current);
break;
}
}
}
if (foundEnd)
{
_charReader.Putback();
}
break;
}
}
}
_current = new TSQLTokenFactory().Parse(
characterHolder.ToString(),
startPosition,
startPosition + characterHolder.Length - 1,
UseQuotedIdentifiers);
}
public System.Drawing.Color GetColorForToken(
TSQLToken token)
{
if (token.Type == TSQLTokenType.Keyword)
{
if (token.AsKeyword.Keyword.In(
TSQLKeywords.NULL,
TSQLKeywords.AND,
TSQLKeywords.OR,
TSQLKeywords.NOT,
TSQLKeywords.LEFT,
TSQLKeywords.RIGHT,
TSQLKeywords.INNER,
TSQLKeywords.OUTER,
TSQLKeywords.JOIN,
TSQLKeywords.ALL,
TSQLKeywords.ANY,
TSQLKeywords.SOME,
TSQLKeywords.BETWEEN,
TSQLKeywords.CROSS,
TSQLKeywords.EXISTS,
TSQLKeywords.IN,
TSQLKeywords.IS,
TSQLKeywords.LIKE,
TSQLKeywords.PIVOT,
TSQLKeywords.UNPIVOT
))
{
return(System.Drawing.Color.Gray);
}
if (token.AsKeyword.Keyword.In(
TSQLKeywords.UPDATE,
TSQLKeywords.COLLATE
))
{
return(System.Drawing.Color.Fuchsia);
}
return(System.Drawing.Color.Blue);
}
if (
token.Type == TSQLTokenType.Character ||
token.Type == TSQLTokenType.Operator)
{
return(System.Drawing.Color.Gray);
}
if (
token.Type == TSQLTokenType.SingleLineComment ||
token.Type == TSQLTokenType.MultilineComment)
{
return(System.Drawing.Color.Green);
}
if (token.Type == TSQLTokenType.StringLiteral)
{
return(System.Drawing.Color.Red);
}
if (token.Type == TSQLTokenType.SystemIdentifier ||
token.Type == TSQLTokenType.SystemVariable)
{
return(System.Drawing.Color.Fuchsia);
}
// https://docs.microsoft.com/en-us/sql/t-sql/functions/functions
if (token.Type == TSQLTokenType.Identifier &&
!token.Text.StartsWith("[") &&
KnownFunctions.ContainsKey(token.AsIdentifier.Name.ToUpper()))
{
return(System.Drawing.Color.Fuchsia);
}
if (token.Type == TSQLTokenType.Identifier &&
!token.Text.StartsWith("[") &&
PseudoKeywords.ContainsKey(token.AsIdentifier.Name.ToUpper()))
{
return(System.Drawing.Color.Blue);
}
return(System.Drawing.Color.Black);
}
public void Putback()
{
_hasExtra = true;
_extraToken = _current;
_hasMore = true;
}