public void ShouldResolveUpdateStatement()
{
//Arrange
string rawTsql = "update someFactTable " +
"set somecolumn = nil.col_01, someOtherColumn = [dbo].gl.col_02 " +
"from DWH_DB.[dbo].[someFactTable]";
IDataManipulationResolver resolver = new UpdateStatementResolver();
int fileIndex = 0;
CompilerContext context = new CompilerContext("xUnit", "stdserver", "stdDatabase", true);
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(rawTsql).ToArray();
//Act
DataManipulation statement = resolver.Resolve(tokens, ref fileIndex, context);
//Assert
Assert.Equal(tokens.Length, fileIndex);
Assert.Equal(2, statement.Expressions.Count);
Assert.Equal(ExpressionType.COLUMN, statement.Expressions[0].Type);
Assert.Equal("DWH_DB.dbo.someFactTable.somecolumn", statement.Expressions[0].Name);
Assert.Equal("stdDatabase.unrelated.nil.col_01", statement.Expressions[0].ChildExpressions[0].Name);
Assert.Equal(ExpressionType.COLUMN, statement.Expressions[1].Type);
Assert.Equal("DWH_DB.dbo.someFactTable.someOtherColumn", statement.Expressions[1].Name);
Assert.Equal("stdDatabase.dbo.gl.col_02", statement.Expressions[1].ChildExpressions[0].Name);
}
public void ShouldResolveRecursiveCaseStatement()
{
//Arrange
string rawTsql = " CASE " +
" WHEN someValue > anotherValue THEN " +
" CASE " +
" WHEN NullIf(val_01,0) IS NULL THEN NULL " +
" WHEN NullIf(val_02,0) IS NULL THEN Null " +
" WHEN(100 / val_02 * val_01) <= 100 THEN 'Green' " +
" WHEN 100 + 25 <= 125 THEN 'Yellow'" +
" ELSE 'Red' " +
" END " +
" ELSE " +
" CASE " +
" WHEN NULLIF(val_03, 0) IS NULL THEN NULL " +
" WHEN NullIf(val_02,0) IS NULL THEN NULL " +
" WHEN(100 / val_02 * val_03) <= 100 THEN 'Green' " +
" WHEN(100 / val_02 * val_03) <= 125 THEN 'Yellow'" +
" ELSE 'Red' " +
" END " +
" END";
IExpressionResolver resolver = new CaseStatementResolver();
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);
}
public void TestParseLines()
{
string file_string = @"zzz!10,NAV15BEO0Zll
before zzz!11,87NSZSSP4Jll
zzz!12,MZOTXUFT9Tll after
zzz!13,6DFOKC74ZGll
zzz!14,3KZ94GFGLZll
zzz!15,VQTMAXWCDKll";
string[] expected =
{
"10,NAV15BEO0Z",
"11,87NSZSSP4J",
"12,MZOTXUFT9T",
"13,6DFOKC74ZG",
"14,3KZ94GFGLZ",
"15,VQTMAXWCDK"
};
string query = "LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE imptest FIELDS TERMINATED BY ',' LINES STARTING BY 'zzz!' TERMINATED BY 'll'";
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens(query);
LoadDataParser parser = new LoadDataParser(tokens);
Assert.AreEqual("zzz!", parser.lines_starting_by);
Assert.AreEqual("ll", parser.lines_terminated_by);
string[] actual = ClientSession.ParseLines(file_string, parser);
foreach (var a in actual)
{
Console.WriteLine(a);
}
Assert.IsTrue(actual.SequenceEqual(expected));
}
public void ShouldResolveUpdateStatementWithAliasTarget()
{
//Arrange
string rawTsql = "update GL " +
"set somevalue = nil.col_01, someOtherValue = gl.col_02 " +
"from [SOME_table] nil " +
"inner join [dbo].[TABLE_02] GL";
IDataManipulationResolver resolver = new UpdateStatementResolver();
int fileIndex = 0;
CompilerContext context = new CompilerContext("xUnit", "stdserver", "stdDatabase", true);
ReadOnlySpan <TSQLToken> tokens = TSQLTokenizer.ParseTokens(rawTsql).ToArray();
//Act
DataManipulation statement = resolver.Resolve(tokens, ref fileIndex, context);
//Assert
Assert.Equal(tokens.Length, fileIndex);
Assert.Equal(ExpressionType.COLUMN, statement.Expressions[0].Type);
Assert.Equal("stdDatabase.dbo.TABLE_02.somevalue", statement.Expressions[0].Name);
Assert.Equal("stdDatabase.unrelated.SOME_table.col_01", statement.Expressions[0].ChildExpressions[0].Name);
Assert.Equal(ExpressionType.COLUMN, statement.Expressions[1].Type);
Assert.Equal("stdDatabase.dbo.TABLE_02.someOtherValue", statement.Expressions[1].Name);
Assert.Equal("stdDatabase.dbo.TABLE_02.col_02", statement.Expressions[1].ChildExpressions[0].Name);
}
public void Parse_GO()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("GO");
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLKeyword(0, "GO")
},
tokens);
}
public void MoneyLiteral_ForeignNegative()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("£-.1 ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLMoneyLiteral(0, "£-.1"),
new TSQLWhitespace(4, " ")
},
tokens);
}
public void MoneyLiteral_StartingWithPeriod()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("$.1 ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLMoneyLiteral(0, "$.1"),
new TSQLWhitespace(3, " ")
},
tokens);
}
public void SingleLineCommentToken_LineFeed()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("--blah\n", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLSingleLineComment(0, "--blah"),
new TSQLWhitespace(6, "\n")
},
tokens);
}
public void NumericLiteral_DecimalStartingWithPeriod()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens(".545878 ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLNumericLiteral(0, ".545878"),
new TSQLWhitespace(7, " ")
},
tokens);
}
public void NumericLiteral_ComplicatedExponential()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("54.0e-4 ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLNumericLiteral(0, "54.0e-4"),
new TSQLWhitespace(7, " ")
},
tokens);
}
public void KeywordToken_SimpleKeywordLowercase()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("go ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLKeyword(0, "go"),
new TSQLWhitespace(2, " ")
},
tokens);
}
public void StringLiteralToken_DoubleQuote()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("\"name\" ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLStringLiteral(0, "\"name\""),
new TSQLWhitespace(6, " ")
},
tokens);
}
public void IdentifierToken_EscapedBracketedIdentifier2()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("[a]]a] ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLIdentifier(0, "[a]]a]"),
new TSQLWhitespace(6, " ")
},
tokens);
}
public void IdentifierToken_QuotedUnicodeIdentifier()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("N\"name\" ", useQuotedIdentifiers: true, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLIdentifier(0, "N\"name\""),
new TSQLWhitespace(7, " ")
},
tokens);
}
public void IdentifierToken_SimpleIdentifier()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("a ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLIdentifier(0, "a"),
new TSQLWhitespace(1, " ")
},
tokens);
}
public void IncompleteToken_Identifier()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("[dbo", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLIncompleteIdentifierToken(0, "[dbo")
},
tokens);
Assert.IsFalse(tokens[0].IsComplete);
}
public void IncompleteToken_Comment()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("/* something", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLIncompleteCommentToken(0, "/* something")
},
tokens);
Assert.IsFalse(tokens[0].IsComplete);
}
public void CharacterToken_Period()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens(". ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLCharacter(0, "."),
new TSQLWhitespace(1, " ")
},
tokens);
}
public void IncompleteToken_StringLiteral()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("'", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLIncompleteStringToken(0, "'")
},
tokens);
Assert.IsFalse(tokens[0].IsComplete);
}
public void BinaryToken_SimpleBinaryLiteral()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("0x69048AEFDD010E ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLBinaryLiteral(0, "0x69048AEFDD010E"),
new TSQLWhitespace(16, " ")
},
tokens);
}
public void BinaryToken_BinaryLiteralEndWithLetter()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("0x69048AEFDD010EJ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLBinaryLiteral(0, "0x69048AEFDD010E"),
new TSQLIdentifier(16, "J")
},
tokens);
}
public void Operator_NotEqualExclamation()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("!= ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLOperator(0, "!="),
new TSQLWhitespace(2, " ")
},
tokens);
}
public void BinaryToken_EmptyBinaryLiteralCaseInsensitive()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("0X ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLBinaryLiteral(0, "0X"),
new TSQLWhitespace(2, " ")
},
tokens);
}
public void Operator_LessThan()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("< ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLOperator(0, "<"),
new TSQLWhitespace(1, " ")
},
tokens);
}
public void MultilineCommentToken_RequireFullEnding()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("/*/ blah */ ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLMultilineComment(0, "/*/ blah */"),
new TSQLWhitespace(11, " ")
},
tokens);
}
public void StringLiteralToken_SingleQuoteUnicode()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("N'name' ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLStringLiteral(0, "N'name'"),
new TSQLWhitespace(7, " ")
},
tokens);
}
public void MultilineCommentToken_SpanLines()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("/* blah\r\nblah */ ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLMultilineComment(0, "/* blah\r\nblah */"),
new TSQLWhitespace(16, " ")
},
tokens);
}
public void StringLiteralToken_MultiLine()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("'a\r\nb' ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLStringLiteral(0, "'a\r\nb'"),
new TSQLWhitespace(6, " ")
},
tokens);
Assert.AreEqual("a\r\nb", tokens[0].AsStringLiteral.Value);
}
public void BinaryToken_EmbeddedLineContinuation()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("0xabc\\\r\ndef ", includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLBinaryLiteral(0, "0xabc\\\r\ndef"),
new TSQLWhitespace(11, " ")
},
tokens);
Assert.AreEqual("0xABCDEF", tokens[0].AsBinaryLiteral.Value);
}
public void IdentifierToken_BracketedMultiLine()
{
List <TSQLToken> tokens = TSQLTokenizer.ParseTokens("[a\r\nb] ", useQuotedIdentifiers: false, includeWhitespace: true);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLIdentifier(0, "[a\r\nb]"),
new TSQLWhitespace(6, " ")
},
tokens);
Assert.AreEqual("a\r\nb", tokens[0].AsIdentifier.Name);
}
