public void SelectStatement_DontEatFinalDescAsKeyword()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"select 1 as blah order by 1 desc select 1",
includeWhitespace: false);
Assert.AreEqual(2, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
Assert.AreEqual(TSQLStatementType.Select, statements[1].Type);
TSQLSelectStatement select1 = statements[0] as TSQLSelectStatement;
TSQLSelectStatement select2 = statements[1] as TSQLSelectStatement;
Assert.AreEqual(8, select1.Tokens.Count);
Assert.AreEqual(2, select2.Tokens.Count);
}
public void SelectStatement_MultipleSelectsWithoutSemicolon()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
"select 1 select 1",
includeWhitespace: true);
TSQLSelectStatement select1 = statements[0] as TSQLSelectStatement;
TSQLSelectStatement select2 = statements[1] as TSQLSelectStatement;
Assert.IsNotNull(statements);
Assert.AreEqual(2, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, select1.Type);
Assert.AreEqual(1, select1.Select.Columns.Count);
Assert.AreEqual(TSQLStatementType.Select, select2.Type);
Assert.AreEqual(1, select2.Select.Columns.Count);
}
public void SelectStatement_CaseInSelect()
{
string query = "SELECT Value, CASE WHEN Value = 1 THEN 'One' ELSE 'Other' END AS Cased FROM SomeTable";
var statements = TSQLStatementReader.ParseStatements(query, includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.AreEqual(15, select.Select.Tokens.Count);
Assert.AreEqual(2, select.From.Tokens.Count);
Assert.AreEqual("FROM SomeTable",
query.Substring(
select.From.BeginPosition,
select.From.Length));
}
public void SelectStatement_Option()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"SELECT *
FROM
Sales.SalesOrderHeader oh
OPTION (FAST 10)" ,
includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.AreEqual(12, select.Tokens.Count);
Assert.IsNotNull(select.Option);
Assert.AreEqual(5, select.Option.Tokens.Count);
}
public void SelectStatement_TableHint()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"SELECT *
FROM
Sales.SalesOrderHeader oh WITH (NOLOCK)
ORDER BY
oh.OrderDate;" ,
includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.AreEqual(16, select.Tokens.Count);
Assert.AreEqual("OrderDate", select.Tokens[15].Text);
}
public SelectStatement(
string selectText,
string fromText,
string primaryTableDatabase,
string primaryTableSchema,
string primaryTableName,
TSQLSelectStatement tSQLSelectStatement,
List <string> errorMessages)
{
SelectText = selectText;
FromText = fromText;
PrimaryTableName = new TableName(primaryTableSchema, primaryTableName);
PrimaryTableDatabase = primaryTableDatabase;
//PrimaryTableSchema = primaryTableSchema;
//PrimaryTableName = primaryTableName;
TSQLSelectStatement = tSQLSelectStatement;
ErrorMessages = errorMessages;
}
public void OrderByClause_OffsetFetch()
{
// regression test for https://github.com/bruce-dunwiddie/tsql-parser/issues/75
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"
SELECT *
FROM Product.Product P
ORDER BY P.ProductId
OFFSET (@page -1) * @RowPerPage ROWS
FETCH NEXT @RowPerPage ROWS ONLY" ,
includeWhitespace: false);
TSQLSelectStatement select = statements[0].AsSelect;
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(26, select.Tokens.Count);
}
public void SelectStatement_CorrelatedSelect()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
"select (select 1);",
includeWhitespace: true);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.IsNotNull(statements);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
Assert.AreEqual(7, statements[0].Tokens.Count);
Assert.AreEqual(TSQLKeywords.SELECT, select.Tokens[0].AsKeyword.Keyword);
Assert.AreEqual(" ", select.Tokens[1].AsWhitespace.Text);
Assert.AreEqual("(", select.Tokens[2].AsCharacter.Text);
Assert.AreEqual("select", select.Tokens[3].AsKeyword.Text);
Assert.AreEqual(" ", select.Tokens[4].AsWhitespace.Text);
Assert.AreEqual("1", select.Tokens[5].AsNumericLiteral.Text);
Assert.AreEqual(")", select.Tokens[6].AsCharacter.Text);
}
public void SelectStatement_CaseInWhere()
{
string query = "SELECT Value FROM SomeTable WHERE (CASE WHEN Value = 1 THEN 'One' ELSE 'Other' END) = 'One'";
var statements = TSQLStatementReader.ParseStatements(query, includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.AreEqual(2, select.Select.Tokens.Count);
Assert.AreEqual("Value", select.Select.Columns[0].Expression.AsColumn.Column.Name);
Assert.AreEqual(2, select.From.Tokens.Count);
Assert.AreEqual("FROM SomeTable",
query.Substring(
select.From.BeginPosition,
select.From.Length));
Assert.AreEqual(15, select.Where.Tokens.Count);
Assert.AreEqual(1, select.Select.Columns.Count);
}
public void SelectStatement_CommonSelect()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"select t.a, t.b, (select 1) as e
into #tempt
from
[table] t
inner join [table] t2 on
t.id = t2.id
where
t.c = 5
group by
t.a,
t.b
having
count(*) > 1
order by
t.a,
t.b;" ,
includeWhitespace: true);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.IsNotNull(statements);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
Assert.AreEqual(98, select.Tokens.Count);
Assert.AreEqual(TSQLKeywords.SELECT, select.Tokens[0].AsKeyword.Keyword);
Assert.AreEqual(" ", select.Tokens[1].AsWhitespace.Text);
Assert.AreEqual("t", select.Tokens[2].AsIdentifier.Name);
Assert.AreEqual(TSQLCharacters.Period, select.Tokens[3].AsCharacter.Character);
Assert.AreEqual(22, select.Select.Tokens.Count);
Assert.AreEqual(4, select.Into.Tokens.Count);
Assert.AreEqual(26, select.From.Tokens.Count);
Assert.AreEqual(10, select.Where.Tokens.Count);
Assert.AreEqual(13, select.GroupBy.Tokens.Count);
Assert.AreEqual(11, select.Having.Tokens.Count);
Assert.AreEqual(12, select.OrderBy.Tokens.Count);
}
public void SelectStatement_system_user_Regression_without_semicolon()
{
// regression test for https://github.com/bruce-dunwiddie/tsql-parser/issues/93
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
//@"SELECT system_user;",
@"SELECT system_user",
includeWhitespace: false);
// System.NullReferenceException
// this shouldn't happen even if only because it encountered the end of the string
// system_user is a system property, not a function
// is it trying to parse arguments?
// should system properties be split out from system functions?
// what should each be named?
Assert.AreEqual(1, statements.Count);
TSQLSelectStatement select = statements.Single().AsSelect;
Assert.AreEqual(2, select.Tokens.Count);
Assert.AreEqual("system_user", select.Select.Columns[0].Expression.AsColumn.Column.Name);
}
public void SelectStatement_TwoLiteralSelects()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
"select 1;select 2;",
includeWhitespace: true);
TSQLSelectStatement select1 = statements[0] as TSQLSelectStatement;
TSQLSelectStatement select2 = statements[1] as TSQLSelectStatement;
Assert.IsNotNull(statements);
Assert.AreEqual(2, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, select1.Type);
Assert.AreEqual(3, select1.Tokens.Count);
Assert.AreEqual(TSQLKeywords.SELECT, select1.Tokens[0].AsKeyword.Keyword);
Assert.AreEqual(" ", select1.Tokens[1].AsWhitespace.Text);
Assert.AreEqual("1", select1.Tokens[2].AsNumericLiteral.Text);
Assert.AreEqual(TSQLStatementType.Select, select2.Type);
Assert.AreEqual(3, select2.Tokens.Count);
Assert.AreEqual(TSQLKeywords.SELECT, select2.Tokens[0].AsKeyword.Keyword);
Assert.AreEqual(" ", select2.Tokens[1].AsWhitespace.Text);
Assert.AreEqual("2", select2.Tokens[2].AsNumericLiteral.Text);
}
public void SelectStatement_WindowedAggregate()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(@"
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
some_field
ORDER BY
some_other_field) AS some_row_number
FROM my_db.my_schema.my_table" , includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.AreEqual(23, select.Tokens.Count);
Assert.AreEqual(17, select.Select.Tokens.Count);
Assert.AreEqual(6, select.From.Tokens.Count);
}
public void SelectStatement_CaseInSelect()
{
string query = "SELECT Value, CASE WHEN Value = 1 THEN 'One' ELSE 'Other' END AS Cased FROM SomeTable";
var statements = TSQLStatementReader.ParseStatements(query, includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.AreEqual(15, select.Select.Tokens.Count);
Assert.AreEqual(2, select.Select.Columns.Count);
Assert.AreEqual("Value", select.Select.Columns[0].Expression.AsColumn.Column.Name);
TSQLSelectColumn column = select.Select.Columns[1];
Assert.AreEqual("Cased", column.ColumnAlias.AsIdentifier.Name);
Assert.AreEqual(TSQLExpressionType.Case, column.Expression.Type);
Assert.AreEqual(2, select.From.Tokens.Count);
Assert.AreEqual("FROM SomeTable",
query.Substring(
select.From.BeginPosition,
select.From.Length));
Assert.AreEqual(2, select.Select.Columns.Count);
}
public TSQLSelectStatement Parse(ITSQLTokenizer tokenizer)
{
TSQLSelectStatement select = new TSQLSelectStatement();
TSQLSelectClause selectClause = new TSQLSelectClauseParser().Parse(tokenizer);
select.Select = selectClause;
select.Tokens.AddRange(selectClause.Tokens);
if (tokenizer.Current.IsKeyword(TSQLKeywords.INTO))
{
TSQLIntoClause intoClause = new TSQLIntoClauseParser().Parse(tokenizer);
select.Into = intoClause;
select.Tokens.AddRange(intoClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.FROM))
{
TSQLFromClause fromClause = new TSQLFromClauseParser().Parse(tokenizer);
select.From = fromClause;
select.Tokens.AddRange(fromClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.WHERE))
{
TSQLWhereClause whereClause = new TSQLWhereClauseParser().Parse(tokenizer);
select.Where = whereClause;
select.Tokens.AddRange(whereClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.GROUP))
{
TSQLGroupByClause groupByClause = new TSQLGroupByClauseParser().Parse(tokenizer);
select.GroupBy = groupByClause;
select.Tokens.AddRange(groupByClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.HAVING))
{
TSQLHavingClause havingClause = new TSQLHavingClauseParser().Parse(tokenizer);
select.Having = havingClause;
select.Tokens.AddRange(havingClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.ORDER))
{
TSQLOrderByClause orderByClause = new TSQLOrderByClauseParser().Parse(tokenizer);
select.OrderBy = orderByClause;
select.Tokens.AddRange(orderByClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.OPTION))
{
TSQLOptionClause optionClause = new TSQLOptionClauseParser().Parse(tokenizer);
select.Option = optionClause;
select.Tokens.AddRange(optionClause.Tokens);
}
if (
tokenizer.Current != null &&
tokenizer.Current.Type == TSQLTokenType.Keyword)
{
tokenizer.Putback();
}
return(select);
}
public void SqliteServer_ParseTSQL_SelectStatement_ReturnTableNames()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"select name,id,date from person; select address_id,line1,line2,postcode from Address",
includeWhitespace: false);
Assert.AreEqual(2, statements.Count);
TSQLSelectStatement select1 = statements[0] as TSQLSelectStatement;
foreach (TSQLToken token in select1.Select.Tokens)
{
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type); Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
if (token.Type.ToString() == "Identifier" && token.Text.ToLower() == "name")
{
Assert.AreEqual("name", token.Text.ToLower());
}
if (token.Type.ToString() == "Identifier" && token.Text.ToLower() == "id")
{
Assert.AreEqual("id", token.Text.ToLower());
}
if (token.Type.ToString() == "Identifier" && token.Text.ToLower() == "date")
{
Assert.AreEqual("date", token.Text.ToLower());
}
}
foreach (TSQLToken token in select1.From.Tokens)
{
if (token.Type.ToString() == "Identifier")
{
Assert.AreEqual("person", token.Text.ToLower());
}
}
TSQLSelectStatement select2 = statements[1] as TSQLSelectStatement;
Assert.AreEqual(TSQLStatementType.Select, statements[1].Type);
foreach (TSQLToken token in select2.Select.Tokens)
{
Assert.AreEqual(TSQLStatementType.Select, statements[1].Type); Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
if (token.Type.ToString() == "Identifier" && token.Text.ToLower() == "address_id")
{
Assert.AreEqual("address_id", token.Text.ToLower());
}
if (token.Type.ToString() == "Identifier" && token.Text.ToLower() == "line1")
{
Assert.AreEqual("line1", token.Text.ToLower());
}
if (token.Type.ToString() == "Identifier" && token.Text.ToLower() == "line2")
{
Assert.AreEqual("line2", token.Text.ToLower());
}
if (token.Type.ToString() == "Identifier" && token.Text.ToLower() == "postcode")
{
Assert.AreEqual("postcode", token.Text.ToLower());
}
}
foreach (TSQLToken token in select2.From.Tokens)
{
if (token.Type.ToString() == "Identifier")
{
Assert.AreEqual("address", token.Text.ToLower());
}
}
}
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 TSQLSelectStatement Parse(TSQLTokenizer tokenizer)
{
TSQLSelectStatement select = new TSQLSelectStatement();
// should whitespace be excluded from statement parsing logic?
// should I differentiate keywords that start commands?
// correlated subqueries
// scalar function calls
// SELECT clause
TSQLSelectClause selectClause = new TSQLSelectClauseParser().Parse(tokenizer);
select.Select = selectClause;
select.Tokens.AddRange(selectClause.Tokens);
if (tokenizer.Current.IsKeyword(TSQLKeywords.INTO))
{
TSQLIntoClause intoClause = new TSQLIntoClauseParser().Parse(tokenizer);
select.Into = intoClause;
select.Tokens.AddRange(intoClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.FROM))
{
TSQLFromClause fromClause = new TSQLFromClauseParser().Parse(tokenizer);
select.From = fromClause;
select.Tokens.AddRange(fromClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.WHERE))
{
TSQLWhereClause whereClause = new TSQLWhereClauseParser().Parse(tokenizer);
select.Where = whereClause;
select.Tokens.AddRange(whereClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.GROUP))
{
TSQLGroupByClause groupByClause = new TSQLGroupByClauseParser().Parse(tokenizer);
select.GroupBy = groupByClause;
select.Tokens.AddRange(groupByClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.HAVING))
{
TSQLHavingClause havingClause = new TSQLHavingClauseParser().Parse(tokenizer);
select.Having = havingClause;
select.Tokens.AddRange(havingClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.ORDER))
{
TSQLOrderByClause orderByClause = new TSQLOrderByClauseParser().Parse(tokenizer);
select.OrderBy = orderByClause;
select.Tokens.AddRange(orderByClause.Tokens);
}
if (tokenizer.Current.IsCharacter(TSQLCharacters.Semicolon))
{
select.Tokens.Add(tokenizer.Current);
}
return select;
}
public TSQLSelectStatement Parse(TSQLTokenizer tokenizer)
{
TSQLSelectStatement select = new TSQLSelectStatement();
// should whitespace be excluded from statement parsing logic?
// should I differentiate keywords that start commands?
// correlated subqueries
// scalar function calls
// SELECT clause
TSQLSelectClause selectClause = new TSQLSelectClauseParser().Parse(tokenizer);
select.Select = selectClause;
select.Tokens.AddRange(selectClause.Tokens);
if (tokenizer.Current.IsKeyword(TSQLKeywords.INTO))
{
TSQLIntoClause intoClause = new TSQLIntoClauseParser().Parse(tokenizer);
select.Into = intoClause;
select.Tokens.AddRange(intoClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.FROM))
{
TSQLFromClause fromClause = new TSQLFromClauseParser().Parse(tokenizer);
select.From = fromClause;
select.Tokens.AddRange(fromClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.WHERE))
{
TSQLWhereClause whereClause = new TSQLWhereClauseParser().Parse(tokenizer);
select.Where = whereClause;
select.Tokens.AddRange(whereClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.GROUP))
{
TSQLGroupByClause groupByClause = new TSQLGroupByClauseParser().Parse(tokenizer);
select.GroupBy = groupByClause;
select.Tokens.AddRange(groupByClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.HAVING))
{
TSQLHavingClause havingClause = new TSQLHavingClauseParser().Parse(tokenizer);
select.Having = havingClause;
select.Tokens.AddRange(havingClause.Tokens);
}
if (tokenizer.Current.IsKeyword(TSQLKeywords.ORDER))
{
TSQLOrderByClause orderByClause = new TSQLOrderByClauseParser().Parse(tokenizer);
select.OrderBy = orderByClause;
select.Tokens.AddRange(orderByClause.Tokens);
}
if (tokenizer.Current.IsCharacter(TSQLCharacters.Semicolon))
{
select.Tokens.Add(tokenizer.Current);
}
return(select);
}