public void ParseBasicJoinTree()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
*
FROM
APMART_FPVIEWS..FPC_SERVICE svc
INNER JOIN
APSHARE_FP..WR02052_OMR_BASE omr
ON svc.SVC_IDNTY = omr.SVC_IDNTY
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
// Test that a subquery type node was built
SyntaxNode oFrom = oTemp.FindFirst(SyntaxKind.FromKeyword);
Assert.IsTrue(oFrom != null);
SyntaxNode oInnerJoin = oFrom[0];
Assert.AreEqual(3, oInnerJoin.Count);
Assert.AreEqual(SyntaxKind.InnerJoinKeyword, oInnerJoin.ExpectedType);
// test children are correct
Assert.AreEqual(oInnerJoin[0].ExpectedType, SyntaxKind.IdentifierDatabaseSymbol);
Assert.AreEqual(oInnerJoin[1].ExpectedType, SyntaxKind.IdentifierDatabaseSymbol);
}
public void ParseSingleFunctionInWhere()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
SELECT
svc_idnty
FROM
APSHARE_FPVIEWS..FPC_SERVICE svc
WHERE MAX(svc.somecode) > 100
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.IsTrue(oSelect != null);
SyntaxNode oWhere = oTemp.FindFirst(SyntaxKind.WhereKeyword);
Assert.AreEqual(1, oWhere.Count);
SyntaxNode oGreaterThan = oWhere[0];
Assert.AreEqual(SyntaxKind.GreaterThanToken, oGreaterThan.ExpectedType);
SyntaxNode oFunc = oGreaterThan[0];
Assert.AreEqual(SyntaxKind.MaxKeyword, oFunc.ExpectedType);
}
public void ParseStringLiteralColumn()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
'TEST'
FROM
APSHARE_FPVIEWS..FPC_SERVICE svc
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.AreNotEqual(null, oSelect);
SyntaxNode oColumnList = oSelect[0];
Assert.AreEqual(1, oColumnList.Count); // Should be 1 column
Assert.AreEqual(SyntaxKind.ColumnListNode, oColumnList.ExpectedType);
SyntaxNode oLiteralColumn = oColumnList[0];
Assert.AreEqual(SyntaxKind.LiteralToken, oLiteralColumn.ExpectedType);
}
public void ParseMultipleColumnParenthesised()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
(col1), (col2), (col3)
FROM
APSHARE_FPVIEWS..FPC_SERVICE svc
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.AreNotEqual(oSelect, null);
SyntaxNode oColList = oSelect[0];
Assert.AreEqual(3, oColList.Count);
foreach (SyntaxNode oChild in oColList)
{
Assert.AreEqual(oChild.ExpectedType, SyntaxKind.OpenParenthesisToken);
SyntaxNode oColInner = oChild[0];
Assert.IsTrue(
oColInner.ExpectedType == SyntaxKind.StarToken ||
oColInner.ExpectedType == SyntaxKind.IdentifierColumnSymbol ||
oColInner.ExpectedType == SyntaxKind.IdentifierTableSymbol);
}
}
public void ParseSelectColumnAlias()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
col1 AS c2
FROM
APSHARE_FPVIEWS..FPC_SERVICE svc
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.IsTrue(oSelect != null);
SyntaxNode oColumnList = oSelect[0];
Assert.AreEqual(1, oColumnList.Count); // Should be 1 column
Assert.AreEqual(SyntaxKind.ColumnListNode, oColumnList.ExpectedType);
SyntaxNode oLiteralColumn = oColumnList[0];
Assert.AreEqual(SyntaxKind.IdentifierTableSymbol, oLiteralColumn.ExpectedType);
Assert.AreEqual(typeof(Symbol), oLiteralColumn.GetType()); // Table is the top level then col
Assert.AreEqual("c2", ((Symbol)oLiteralColumn).Alias);
}
public void ParseBadWhere()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
col1, col2
FROM
(
SELECT * FROM FPC_SERVICE
) svc
WHERE
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
// Test that a subquery type node was built
SyntaxNode oWhere = oTemp.FindFirst(SyntaxKind.WhereKeyword);
Assert.AreNotEqual(null, oWhere);
Assert.AreEqual(SyntaxKind.BooleanToken, oWhere[0].ExpectedType); // Check for the Filler node
}
public void ParseOnAnd()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
col1, col2
FROM
(
SELECT * FROM FPC_SERVICE
) svc
INNER JOIN
APSHARE_FP..FWR02052_OMR_BASE omr
ON
svc.MKT_PROD_CD = 'MOB PT' AND
svc.SVC_IDNTY = omr.SERVICE_NO
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
// Test that a subquery type node was built
SyntaxNode oON = oTemp.FindFirst(SyntaxKind.OnKeyword);
Assert.AreNotEqual(oON, null);
// Test that there is a select keyword in that subquery
SyntaxNode oAND = oON.FindFirst(SyntaxKind.AndKeyword);
Assert.AreNotEqual(oAND, null);
Assert.AreEqual(oAND.RawSQLText, "AND");
// Test that the AND was generated properly (exactly 2 children)
Assert.AreEqual(2, oAND.Count);
SyntaxNode oLeftEquals = oAND[0];
Assert.AreEqual(oLeftEquals.ExpectedType, SyntaxKind.EqualsToken);
SyntaxNode oLeftEqualsL = oLeftEquals[0];
Assert.AreEqual(oLeftEqualsL.ExpectedType, SyntaxKind.IdentifierTableSymbol);
SyntaxNode oLeftEqualsR = oLeftEquals[1];
Assert.AreEqual(oLeftEqualsR.ExpectedType, SyntaxKind.LiteralToken);
SyntaxNode oRightEquals = oAND[1];
Assert.AreEqual(oLeftEquals.ExpectedType, SyntaxKind.EqualsToken);
SyntaxNode oRightEqualsL = oRightEquals[0];
Assert.AreEqual(oRightEqualsL.ExpectedType, SyntaxKind.IdentifierTableSymbol);
SyntaxNode oRightEqualsR = oRightEquals[1];
Assert.AreEqual(oRightEqualsR.ExpectedType, SyntaxKind.IdentifierTableSymbol);
}
public void ParseEmptyAnd()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
col1, col2
FROM
(
SELECT * FROM FPC_SERVICE
) svc
WHERE AND
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
// Test that a subquery type node was built
SyntaxNode oWhere = oTemp.FindFirst(SyntaxKind.WhereKeyword);
Assert.IsTrue(oWhere != null);
// Test that there is a select keyword in that subquery
SyntaxNode oAND = oWhere.FindFirst(SyntaxKind.AndKeyword);
Assert.AreNotEqual(null, oAND);
Assert.AreEqual(SyntaxKind.BooleanToken, oAND[0].ExpectedType); // Check for the Filler node
// Test that the AND was generated properly (exactly 2 children)
Assert.AreEqual(2, oAND.Count);
}
public void ParseWhereOr()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
col1, col2
FROM
(
SELECT * FROM FPC_SERVICE
) svc
WHERE svc.MKT_PROD_CD = 'MOB PT' OR svc.SVC_STAT_CD <> 'C'
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
// Test that a subquery type node was built
SyntaxNode oWhere = oTemp.FindFirst(SyntaxKind.WhereKeyword);
Assert.IsTrue(oWhere != null);
// Test that there is a select keyword in that subquery
SyntaxNode oOR = oWhere.FindFirst(SyntaxKind.OrKeyword);
Assert.IsTrue(oOR != null);
// Test that the OR was generated properly (exactly 2 children)
Assert.AreEqual(2, oOR.Count);
}
public void ParseComplexColumnConcat()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
SELECT '+61' || SUBSTR(svc_idnty, 1, 8) || '0' FROM FPC_SERVICE
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.AreNotEqual(null, oSelect);
Assert.AreEqual(2, oSelect.Count);
SyntaxNode oColumnList = oSelect[0];
Assert.AreEqual(1, oColumnList.Count);
Assert.AreEqual(SyntaxKind.ColumnListNode, oColumnList.ExpectedType);
SyntaxNode oBarBar = oColumnList[0];
Assert.AreEqual(3, oBarBar.Count);
Assert.AreEqual(SyntaxKind.BarBarToken, oBarBar.ExpectedType);
Assert.AreEqual(SyntaxKind.LiteralToken, oBarBar[0].ExpectedType);
Assert.AreEqual(SyntaxKind.SubStringKeyword, oBarBar[1].ExpectedType);
Assert.AreEqual(SyntaxKind.LiteralToken, oBarBar[2].ExpectedType);
}
public void ParseInvalidColumnPreConcat()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
SELECT || svc.svc_idnty FROM FPC_SERVICE
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.AreNotEqual(null, oSelect);
Assert.AreEqual(2, oSelect.Count);
SyntaxNode oColumnList = oSelect[0];
Assert.AreEqual(1, oColumnList.Count);
Assert.AreEqual(SyntaxKind.ColumnListNode, oColumnList.ExpectedType);
SyntaxNode oBarBar = oColumnList[0];
Assert.AreEqual(2, oBarBar.Count);
Assert.AreEqual(SyntaxKind.BarBarToken, oBarBar.ExpectedType);
}
public void ParseBadColumnCommentFROM()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
SELECT
* --bad comment FROM
APSHARE_FPVIEWS..FPC_SERVICE svc
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.AreNotEqual(oSelect, null);
SyntaxNode oFrom = oSelect.FindFirst(SyntaxKind.FromKeyword);
Assert.AreNotEqual(oFrom, null);
SyntaxNode oTableIdn = oFrom.FindFirst(SyntaxKind.IdentifierTableSymbol);
Assert.AreNotEqual(oTableIdn, null);
}
public void ParseBadColumnCommentWHERE()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
SELECT
* --bad comment
FROM APSHARE_FPVIEWS..FPC_SERVICE svc
-- WHERE x
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.AreNotEqual(oSelect, null);
SyntaxNode oFrom = oSelect.FindFirst(SyntaxKind.FromKeyword);
Assert.AreNotEqual(oFrom, null);
SyntaxNode oWhere = oSelect.FindFirst(SyntaxKind.WhereKeyword);
Assert.AreEqual(oWhere, null); // Should not be a where, They probably wanted this commented out
}
public void ParseAdditionInColumn()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
svc_idnty + 2
FROM
APSHARE_FPVIEWS..FPC_SERVICE svc
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.IsTrue(oSelect != null);
SyntaxNode oColumnList = oSelect.FindFirst(SyntaxKind.ColumnListNode);
Assert.AreNotEqual(oColumnList, null);
SyntaxNode oStarExpression = oSelect.FindFirst(SyntaxKind.PlusToken);
Assert.AreNotEqual(oStarExpression, null);
SyntaxNode oLeft = oStarExpression[0];
SyntaxNode oRight = oStarExpression[1];
Assert.AreEqual(oLeft.ExpectedType, SyntaxKind.IdentifierTableSymbol);
Assert.AreEqual(oRight.ExpectedType, SyntaxKind.NumericToken);
}
public void ParseSingleFunctionInColumn()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
SELECT
COALESCE(svc_idnty, 1, 10)
FROM
APSHARE_FPVIEWS..FPC_SERVICE svc
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.IsTrue(oSelect != null);
SyntaxNode oColumnList = oSelect[0];
Assert.AreEqual(1, oColumnList.Count);
SyntaxNode oFunction = oColumnList[0];
Assert.AreEqual(SyntaxKind.CoalesceKeyword, oFunction.ExpectedType);
Assert.AreEqual(1, oFunction.Count);
Assert.AreEqual(3, oFunction[0].Count);
}
public void ParseParenthesisedExpression()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
col1, col2
FROM
APMART_FP.ADMIN.FPC_SERVICE svc
WHERE (svc.MKT_PROD_CD = 'MOB PT' AND svc.SVC_STAT_CD<> 'C')
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
// Test that a subquery type node was built
SyntaxNode oWhere = oTemp.FindFirst(SyntaxKind.WhereKeyword);
Assert.IsTrue(oWhere != null);
SyntaxNode oParenthesis = oWhere[0];
Assert.AreEqual(oParenthesis.ExpectedType, SyntaxKind.OpenParenthesisToken);
// Test that there is a select keyword in that subquery
SyntaxNode oAND = oParenthesis[0];
Assert.AreEqual(oAND.ExpectedType, SyntaxKind.AndKeyword);
}
public void ParseIN()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
SELECT * FROM FPC_SERVICE
WHERE svc_idnty IN ('0415783039')
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oWhere = oTemp.FindFirst(SyntaxKind.WhereKeyword);
Assert.AreNotEqual(null, oWhere);
Assert.AreEqual(1, oWhere.Count);
Assert.AreEqual(SyntaxKind.InKeyword, oWhere[0].ExpectedType);
Assert.AreEqual(2, oWhere[0].Count);
Assert.AreEqual(SyntaxKind.IdentifierTableSymbol, oWhere[0][0].ExpectedType);
Assert.AreEqual(SyntaxKind.OpenParenthesisToken, oWhere[0][1].ExpectedType);
Assert.AreEqual(1, oWhere[0][1].Count);
}
public void ParseSelectFromDBTable()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
*
FROM
APSHARE_FPVIEWS..FPC_SERVICE svc
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.IsTrue(oSelect != null);
SyntaxNode oFrom = oSelect.FindFirst(SyntaxKind.FromKeyword);
Assert.IsTrue(oFrom != null);
SyntaxNode oTableIdn = oFrom.FindFirst(SyntaxKind.IdentifierTableSymbol);
Assert.IsTrue(oTableIdn != null);
}
public void ParseBasicWhere()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
col1, col2
FROM
(
SELECT * FROM FPC_SERVICE
) svc
WHERE svc.MKT_PROD_CD = 'MOB PT'
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
// Test that a subquery type node was built
SyntaxNode oWhere = oTemp.FindFirst(SyntaxKind.WhereKeyword);
Assert.IsTrue(oWhere != null);
// Test that there is a select keyword in that subquery
SyntaxNode oEqual = oWhere.FindFirst(SyntaxKind.EqualsToken);
Assert.AreNotEqual(oEqual, null);
// Test that the AND was generated properly (exactly 2 children)
Assert.AreEqual(2, oEqual.Count);
Assert.AreEqual(SyntaxKind.IdentifierTableSymbol, oEqual[0].ExpectedType);
Assert.AreEqual(SyntaxKind.LiteralToken, oEqual[1].ExpectedType);
}
public void ParseJoinWithTwoSubQueries()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
col1, col2
FROM
APMART_FPVIEWS..FPC_SERVICE svc
INNER JOIN
(
SELECT * FROM APSHARE_FP..WR02052_OMR_BASE
) omrbase
ON svc.SVC_IDNTY = omrbase.SVC_IDNTY
LEFT JOIN
(
SELECT * FROM APSHARE_FP..WR02052_OMR
) omr
ON svc.SVC_IDNTY = omr.SVC_IDNTY AND
svc.MKT_PROD_CD = 'MOB PT'
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
// Make sure we have a Query
Assert.AreEqual(oTemp.GetType(), typeof(QuerySyntaxNode));
// Test that a subquery type node was built
SyntaxNode oFrom = oTemp.FindFirst(SyntaxKind.FromKeyword);
Assert.IsTrue(oFrom != null);
SyntaxNode oLeftJoin = oFrom[0];
Assert.AreEqual(SyntaxKind.LeftJoinKeyword, oLeftJoin.ExpectedType);
Assert.AreEqual(3, oLeftJoin.Count);
Assert.AreEqual("omr", ((Symbol)oLeftJoin[1]).Alias);
SyntaxNode oInnerJoin = oLeftJoin[0];
Assert.AreEqual(3, oInnerJoin.Count);
Assert.AreEqual(SyntaxKind.InnerJoinKeyword, oInnerJoin.ExpectedType);
// test children are correct
Assert.AreEqual(SyntaxKind.IdentifierDatabaseSymbol, oInnerJoin[0].ExpectedType);
Assert.AreEqual("svc", ((Symbol)oInnerJoin[0]).Alias);
Assert.AreEqual(SyntaxKind.IdentifierSubQuerySymbol, oInnerJoin[1].ExpectedType);
Assert.AreEqual("omrbase", ((Symbol)oInnerJoin[1]).Alias);
}
public void ParseCascadedColumnCase()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
SELECT CASE
WHEN ADDED ='INTERNATIONAL' THEN
CASE WHEN svc.MKT_PROD IN ('MOB PT', 'MOB PP') THEN '-' ELSE '*' END
ELSE 'N' END AS VARSTRING2
FROM FPC_SERVICE
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.AreNotEqual(null, oSelect);
Assert.AreEqual(2, oSelect.Count);
SyntaxNode oColList = oSelect[0];
Assert.AreEqual(SyntaxKind.ColumnListNode, oColList.ExpectedType);
Assert.AreEqual(1, oColList.Count);
SyntaxNode oCase = oColList[0];
Assert.AreEqual(SyntaxKind.CaseKeyword, oCase.ExpectedType);
Assert.AreEqual(3, oCase.Count);
Assert.AreEqual("VARSTRING2", ((Symbol)oCase).Alias);
SyntaxNode oWhen = oCase[0];
Assert.AreEqual(SyntaxKind.WhenKeyword, oWhen.ExpectedType);
Assert.AreEqual(2, oWhen.Count); // Literal and THEN
Assert.AreEqual(SyntaxKind.EqualsToken, oWhen[0].ExpectedType);
SyntaxNode oInnerCase = oWhen[1][0];
Assert.AreEqual(SyntaxKind.CaseKeyword, oInnerCase.ExpectedType);
Assert.AreEqual(3, oInnerCase.Count); // Literal and THEN
Assert.AreEqual(SyntaxKind.InKeyword, oInnerCase[0][0].ExpectedType);
SyntaxNode oElse = oCase[1];
Assert.AreEqual(SyntaxKind.ElseKeyword, oElse.ExpectedType);
Assert.AreEqual(1, oElse.Count);
Assert.AreEqual(SyntaxKind.LiteralToken, oElse[0].ExpectedType);
}
public void ParseCascadedParenthesis()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
col1, col2
FROM
APMART_FP.ADMIN.FPC_SERVICE svc
WHERE ((svc.MKT_PROD_CD = 'MOB PT') AND svc.SVC_STAT_CD<> 'C') AND (svc.SVC_IDNTY <> '0415783039')
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
// Test that a subquery type node was built
SyntaxNode oWhere = oTemp.FindFirst(SyntaxKind.WhereKeyword);
Assert.IsTrue(oWhere != null);
// Test that there is a select keyword in that subquery
SyntaxNode oFirstAND = oWhere[0];
Assert.AreEqual(oFirstAND.ExpectedType, SyntaxKind.AndKeyword);
SyntaxNode oParenthesis = oFirstAND[0];
Assert.AreEqual(oParenthesis.ExpectedType, SyntaxKind.OpenParenthesisToken);
// Inner AND
SyntaxNode oInnerAND = oParenthesis[0];
Assert.AreEqual(oInnerAND.ExpectedType, SyntaxKind.AndKeyword);
// LEFT SIDE of INNER AND should be parenthesis
SyntaxNode oInnerParenthesis = oInnerAND[0];
Assert.AreEqual(oParenthesis.ExpectedType, SyntaxKind.OpenParenthesisToken);
// Test that there is a select keyword in that subquery
SyntaxNode oSecondParenthesis = oFirstAND[1];
Assert.AreEqual(oSecondParenthesis.ExpectedType, SyntaxKind.OpenParenthesisToken);
}
public void ParseIncompleteSelect()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
SELECT
*
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.AreNotEqual(oSelect, null);
}
public void ParseSimpleWhereCase()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
SELECT * FROM APAPP_CRMMP..MA504740005_COMM9729001
WHERE VARDATA3 = CASE WHEN VARDATA3 IS NOT NULL THEN 'MOBILE PHONE' ELSE '' END
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oWhere = oTemp.FindFirst(SyntaxKind.WhereKeyword);
Assert.AreNotEqual(null, oWhere);
Assert.AreEqual(1, oWhere.Count);
SyntaxNode oEquals = oWhere[0];
Assert.AreEqual(SyntaxKind.EqualsToken, oEquals.ExpectedType);
Assert.AreEqual(2, oEquals.Count);
SyntaxNode oCase = oEquals[1];
Assert.AreEqual(SyntaxKind.CaseKeyword, oCase.ExpectedType);
Assert.AreEqual(3, oCase.Count);
SyntaxNode oWhen = oCase[0];
Assert.AreEqual(SyntaxKind.WhenKeyword, oWhen.ExpectedType);
Assert.AreEqual(2, oWhen.Count); // Literal and THEN
Assert.AreEqual(SyntaxKind.IsKeyword, oWhen[0].ExpectedType);
SyntaxNode oElse = oCase[1];
Assert.AreEqual(SyntaxKind.ElseKeyword, oElse.ExpectedType);
Assert.AreEqual(1, oElse.Count);
Assert.AreEqual(SyntaxKind.LiteralToken, oElse[0].ExpectedType);
}
public void ParseMultipleFunctionInOn()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
SELECT
svc_idnty
FROM
APSHARE_FPVIEWS..FPC_SERVICE svc
INNER JOIN
APMART_FPVIEWS..FPC_ACCOUNT acct
ON svc.acct-ID = acct.acct_id
AND MAX(svc.somecode) > COUNT(svc_idnty)
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.IsTrue(oSelect != null);
SyntaxNode oWhere = oTemp.FindFirst(SyntaxKind.AndKeyword);
Assert.AreEqual(2, oWhere.Count);
SyntaxNode oGreaterThan = oWhere[1];
Assert.AreEqual(SyntaxKind.GreaterThanToken, oGreaterThan.ExpectedType);
SyntaxNode oFunc = oGreaterThan[0];
Assert.AreEqual(SyntaxKind.MaxKeyword, oFunc.ExpectedType);
SyntaxNode oFunc2 = oGreaterThan[1];
Assert.AreEqual(SyntaxKind.CountKeyword, oFunc2.ExpectedType);
}
public void ParseSubqueryTree()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
*
FROM
(
SELECT * FROM FPC_SERVICE
) svc
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
// Make sure we have a Query
Assert.AreEqual(oTemp.GetType(), typeof(QuerySyntaxNode));
// Test that a subquery type node was built
SyntaxNode oSubQuery = oTemp.FindFirst(SyntaxKind.IdentifierSubQuerySymbol);
Assert.AreNotEqual(oSubQuery, null);
// Test that there is a select keyword in that subquery
SyntaxNode oSelect = oSubQuery.FindFirst(SyntaxKind.SelectKeyword);
Assert.AreNotEqual(oSelect, null);
// Test that the identifier was generated properly
SyntaxNode oTableIdn = oSelect.FindFirst(SyntaxKind.IdentifierTableSymbol);
Assert.AreNotEqual(oTableIdn, null);
}
public void ParseWhereAndTree()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
col1, col2
FROM
(
SELECT * FROM FPC_SERVICE
) svc
WHERE
svc.MKT_PROD_CD = 'MOB PT' AND
svc.SVC_STAT_CD <> 'C' AND
svc.SVC_IDNTY = '0423234042'
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
// Test that a subquery type node was built
SyntaxNode oWhere = oTemp.FindFirst(SyntaxKind.WhereKeyword);
Assert.AreNotEqual(oWhere, null);
// Test that there is a select keyword in that subquery
SyntaxNode oAND = oWhere.FindFirst(SyntaxKind.AndKeyword);
Assert.AreNotEqual(null, oAND);
// Test that the AND was generated properly (exactly 2 children)
Assert.AreEqual(2, oAND.Count);
Assert.AreEqual(2, oAND[0].Count); // Inner AND has 2 children
}
public void ParseSelectMultipleColumns()
{
SlidingTextWindow oText = new SlidingTextWindow(
@"
/* TEST */
SELECT
col1, col2
FROM
APSHARE_FPVIEWS..FPC_SERVICE svc
");
// Initialises the Parser
SyntaxParser oParser = new SyntaxParser(oText);
// Try and generate a tree
SyntaxNode oTemp = oParser.ParseTree();
SyntaxNode oSelect = oTemp.FindFirst(SyntaxKind.SelectKeyword);
Assert.IsTrue(oSelect != null);
SyntaxNode oTableIdn = oSelect[0];
Assert.AreEqual(2, oTableIdn.Count); // Should be 2 columns
}