public void ShouldResolveSelectStatementWithTopKeyword()
{
//Arrange
string rawTsql = "SELECT top 1 Keycol FROM DWH.dbo.someDim a";
IExpressionResolver resolver = new SelectStatementResolver();
int fileIndex = 0;
CompilerContext context = new CompilerContext("xUnit", "stdserver", "stdDatabase", true);
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(rawTsql).ToArray();
//Act
Expression expression = resolver.Resolve(tokens, ref fileIndex, context);
//Assert
Assert.Equal(tokens.Length, fileIndex);
Assert.Equal("DWH.dbo.someDim.Keycol", expression.ChildExpressions[0].Name);
Assert.Equal("SELECT", expression.Name);
}
public void ShouldResolveSelectStatementWithMultipleTables()
{
//Arrange
string rawTsql = "SELECT someColumn FROM someTable, anotherTable";
IExpressionResolver resolver = new SelectStatementResolver();
int fileIndex = 0;
CompilerContext context = new CompilerContext("xUnit", "stdserver", "stdDatabase", true);
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(rawTsql).ToArray();
//Act
Expression expression = resolver.Resolve(tokens, ref fileIndex, context);
//Assert
Assert.Equal(tokens.Length, fileIndex);
Assert.Equal("unrelated.unrelated.unrelated.someColumn", expression.ChildExpressions[0].Name);
Assert.Equal("SELECT", expression.Name);
}
public void ShouldResolveSelectStatementWithOverClause()
{
//Arrange
string rawTsql = "select row_number() over (partition by [someColumn] order by [timestamp] desc) as rn " +
"from someServer.someDb.dbo.[someTable]";
IExpressionResolver resolver = new SelectStatementResolver();
int fileIndex = 0;
CompilerContext context = new CompilerContext("xUnit", "stdserver", "stdDatabase", true);
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(rawTsql).ToArray();
//Act
Expression expression = resolver.Resolve(tokens, ref fileIndex, context);
//Assert
Assert.Equal(tokens.Length, fileIndex);
Assert.Equal("SELECT", expression.Name);
}
public void ShouldResolveSelectWithCostumFunction()
{
//Arrange
string rawTsql = "SELECT " +
"DWH.dbo.COSTUMFUNCTION(DWH.dbo.ANOTHERCOSTUMFUNCTION(OBF.schemaa.table1.[some Column_01], 'YEN'))" +
"FROM someTable";
IExpressionResolver resolver = new SelectStatementResolver();
int fileIndex = 0;
CompilerContext context = new CompilerContext("xUnit", "irrelevant", "irrelevant", true);
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(rawTsql).ToArray();
//Act
Expression expression = resolver.Resolve(tokens, ref fileIndex, context);
//Assert
Assert.Equal(tokens.Length, fileIndex);
}
public void ShouldResolveSelectStatementWithComplexExpression()
{
//Arrange
string rawTsql = "SELECT someColumn_01, someColumn_02 * -2 AS someAlias FROM someTable";
IExpressionResolver resolver = new SelectStatementResolver();
int fileIndex = 0;
CompilerContext context = new CompilerContext("xUnit", "stdserver", "stdDatabase", true);
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(rawTsql).ToArray();
//Act
Expression expression = resolver.Resolve(tokens, ref fileIndex, context);
//Assert
Assert.Equal(tokens.Length, fileIndex);
Assert.Equal("stdDatabase.unrelated.someTable.someColumn_01", expression.ChildExpressions[0].Name);
Assert.Equal(ExpressionType.ALIAS, expression.ChildExpressions[1].Type);
expression.ChildExpressions.GetExpectedExpression("someAlias")
.ChildExpressions.GetExpectedExpression("stdDatabase.unrelated.someTable.someColumn_02");
Assert.Equal("SELECT", expression.Name);
}
public void ShouldResolveRecursiveSelectStatement()
{
//Arrange
string rawTsql = " SELECT CASE WHEN IsNull(ParamString27, '') = '' THEN 'string_01' ELSE ParamString27 END " +
" FROM someserver.[DB Name].[dbo].[tablix] fl " +
" WHERE ProjectNo = ( SELECT column_01 " +
" FROM DWH.dbo.dwhtable_01 dP" +
" WHERE dP.dwhColumn_01 = fL.outerColumn" +
" )";
IExpressionResolver resolver = new SelectStatementResolver();
int fileIndex = 0;
CompilerContext context = new CompilerContext("xUnit", "stdserver", "stdDatabase", true);
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(rawTsql).ToArray();
//Act
Expression expression = resolver.Resolve(tokens, ref fileIndex, context);
//Assert
Assert.Equal(tokens.Length, fileIndex);
Assert.Equal("SELECT", expression.Name);
//TODO
}
internal void AnalyzeTSQLContentString(string tsqlContent, CompilerContext context)
{
List <TSQLToken> tokensWithComments = TSQLTokenizer.ParseTokens(tsqlContent);
ReadOnlySpan <TSQLToken> tokens = FormatTokenList(tokensWithComments);
int fileIndex = 0;
while (fileIndex < tokens.Length)
{
IDataManipulationResolver manipulationResolver;
DataManipulation manipulation;
switch (tokens[fileIndex].Text.ToLower())
{
case "use":
new UseStatementResolver().ResolveUseStatement(tokens, ref fileIndex, context);
break;
//case "declare":
// new VariableDeclarationResolver().Resolve(tokens, ref fileIndex, context);
// break;
//case "set":
// gic.AssignVariableValue(tokens, ref fileIndex);
// break;
case "with":
var withResolver = new WithStatementResolver();
withResolver.Resolve(tokens, ref fileIndex, context);
break;
case "merge":
manipulationResolver = new MergeStatementResolver();
manipulation = manipulationResolver.Resolve(tokens, ref fileIndex, context);
context.DataManipulations.Add(manipulation);
context.DropCommonTableExpressions();
break;
case "bulk":
var bulkStatementResolver = new BulkInsertStatementResolver();
bulkStatementResolver.Resolve(tokens, ref fileIndex, context);
break;
case "insert":
manipulationResolver = new InsertStatementResolver();
manipulation = manipulationResolver.Resolve(tokens, ref fileIndex, context);
context.DataManipulations.Add(manipulation);
context.DropCommonTableExpressions();
break;
case "update":
manipulationResolver = new UpdateStatementResolver();
manipulation = manipulationResolver.Resolve(tokens, ref fileIndex, context);
context.DataManipulations.Add(manipulation);
context.DropCommonTableExpressions();
break;
case "select":
SelectStatementResolver selectResolver = new SelectStatementResolver();
SelectStatement statement = selectResolver.ResolveTopLevel(tokens, ref fileIndex, context);
if (statement.TargetObject == null)
{
if (context.ConsiderQueries)
{
context.DataQueries.Add(statement.Expression);
}
}
else
{
context.AddSelectWithIntoClause(statement);
}
context.DropCommonTableExpressions();
break;
//case "drop":
// gic.ResolveDropStatement(tokens, ref fileIndex, causer);
// break;
case "create":
new CreateStatementResolver().Resolve(tokens, ref fileIndex, context);
break;
//case "exec":*
//case "execute": throw new NotImplementedException("Execute statements are no supported yet");
default:
fileIndex++;
break;
}
}
result.DataQueries = context.DataQueries;
result.DataManipulations = context.DataManipulations;
}
/// <summary>
/// Resolves a database object and adds it to the current context as well
/// as to the landscape
/// </summary>
/// <param name="asTargetTable">If the database object is expected to be a target table standart database and server are
/// not added. We also may expect an opening bracket directly after the identifier which is the then not count as a function start</param>
/// <returns>The database object</returns>
internal static DatabaseObject ResolveDatabaseObject(ReadOnlySpan <TSQLToken> tokens, ref int fileIndex, CompilerContext context, bool asTargetTable = false)
{
DatabaseObject dbo;
if (tokens[fileIndex].Text.Equals("("))
{
fileIndex++; //skip '('
dbo = ResolveDatabaseObject(tokens, ref fileIndex, context, asTargetTable);
fileIndex++; //skip ')'
}
else if (tokens[fileIndex].Text.ToLower().Equals("select"))
{
var resolver = new SelectStatementResolver();
var statement = resolver.Resolve(tokens, ref fileIndex, context);
dbo = new DatabaseObject(DatabaseObjectType.SELECTION);
dbo.Expressions.AddRange(statement.ChildExpressions);
}
else if (tokens[fileIndex].Type.Equals(TSQLTokenType.Identifier))
{
int positionBeforeNotationResolution = fileIndex;
string notation = ResolveDatabaseObjectIdentifier(tokens, ref fileIndex);
if (tokens.Length > fileIndex && tokens[fileIndex].Text.Equals("(") && !asTargetTable)
{
//In this section we asssume that the database object is returned by a function
fileIndex = positionBeforeNotationResolution;
var functionResolver = new TsqlFunctionResolver();
var function = functionResolver.Resolve(tokens, ref fileIndex, context);
dbo = new DatabaseObject(DatabaseObjectType.SELECTION);
dbo.Expressions.Add(function);
}
else
{
dbo = new DatabaseObject(notation, context, DatabaseObjectType.REAL, !asTargetTable);
}
}
else if (tokens[fileIndex].Type.Equals(TSQLTokenType.Variable))
{
dbo = new DatabaseObject(DatabaseObjectType.VARIABLE)
{
Name = tokens[fileIndex].Text,
Database = InternalConstants.UNRELATED_DATABASE_NAME,
Schema = InternalConstants.UNRELATED_SCHEMA_NAME,
Server = InternalConstants.UNRELATED_SERVER_NAME
};
fileIndex++;
}
else if (tokens[fileIndex].Type.Equals(TSQLTokenType.StringLiteral) || tokens[fileIndex].Type.Equals(TSQLTokenType.NumericLiteral) || tokens[fileIndex].Type.Equals(TSQLTokenType.BinaryLiteral))
{
//The dbo is a set of constants e.g. ('Design Engineer', 'Tool Designer', 'Marketing Assistant')
var dataset = ResolvesDataSet(tokens, ref fileIndex, context);
dbo = new DatabaseObject(DatabaseObjectType.SET);
dbo.Expressions.Add(dataset);
}
else if (tokens[fileIndex].Text.Equals("openquery", StringComparison.InvariantCultureIgnoreCase))
{
dbo = new OpenQueryStatementResolver().Resolve(tokens, ref fileIndex, context);
}
else
{
throw new InvalidSqlException("Invalid database object");
}
SkipTableHints(tokens, ref fileIndex);
string alias = ResolveAlias(tokens, ref fileIndex, context);
if (!string.IsNullOrEmpty(alias))
{
dbo.Alias = alias;
}
SkipTableHints(tokens, ref fileIndex); //Table hints can appear before OR after an alias
return(dbo);
}
