public virtual void testAddORConditionInWhereClause()
{
parser.sqltext = "SELECT * FROM TABLE_X where f > 0";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TExpression expression1 = parser.parseExpression("c1>1");
TExpression expression2 = new TExpression();
expression2.ExpressionType = EExpressionType.logical_or_t;
TExpression parensExpr = new TExpression();
parensExpr.ExpressionType = EExpressionType.parenthesis_t;
parensExpr.LeftOperand = select.WhereClause.Condition;
expression2.LeftOperand = parensExpr;
expression2.RightOperand = expression1;
select.WhereClause.Condition = expression2;
//System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"WHERE ( f > 0 )\n" +
" OR c1 > 1"
));
}
public virtual void testCreateAndPredicate()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvoracle);
TExpression left = sqlParser.parseExpression("salary");
TExpression right = sqlParser.parseExpression("20");
TExpression left2 = sqlParser.parseExpression("location");
TExpression right2 = sqlParser.parseExpression("'NY'");
TExpression c1 = new TExpression(EExpressionType.simple_comparison_t, left, right, EComparisonType.greaterThanOrEqualTo);
//c1.ExpressionType = EExpressionType.simple_comparison_t;
//c1.ComparisonType = EComparisonType.greaterThanOrEqualTo;
//c1.LeftOperand = left;
//c1.RightOperand = right;
TExpression c2 = new TExpression(EExpressionType.simple_comparison_t, left2, right2, EComparisonType.equalsTo);
//c2.ExpressionType = EExpressionType.simple_comparison_t;
//c2.ComparisonType = EComparisonType.equalsTo;
//c2.LeftOperand = left2;
//c2.RightOperand = right2;
TExpression c3 = new TExpression(EExpressionType.logical_and_t, c1, c2);
//c3.ExpressionType = EExpressionType.logical_and_t;
//c3.LeftOperand = c1;
//c3.RightOperand = c2;
Assert.IsTrue(verifyScript(EDbVendor.dbvoracle, "salary >= 20 and location = 'NY'", c3.ToScript()));
}
public virtual void printColumns(TExpression expression, TCustomSqlStatement statement)
{
Console.WriteLine("Referenced columns:");
columnVisitor cv = new columnVisitor(statement);
expression.postOrderTraverse(cv);
}
public virtual void testAddNewConditionInWhereClause()
{
parser.sqltext = "select count(*) from TableName where NOT a OR NOT b";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TExpression expression1 = parser.parseExpression("c1=1");
TExpression expression2 = new TExpression();
expression2.ExpressionType = EExpressionType.logical_and_t;
TExpression parensExpr = new TExpression();
parensExpr.ExpressionType = EExpressionType.parenthesis_t;
parensExpr.LeftOperand = select.WhereClause.Condition;
expression2.LeftOperand = parensExpr;
expression2.RightOperand = expression1;
select.WhereClause.Condition = expression2;
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT count(*)\n" +
"FROM tablename\n" +
"WHERE ( NOT a\n" +
" OR NOT b )\n" +
" AND c1 = 1"
));
}
public new static TWhile Claim(StringClaimer claimer)
{
Claim failpoint = claimer.FailPoint();
Claim c = claimer.Claim(While);
if (!c.Success)
{
failpoint.Fail();
return(null);
}
TExpression condition = TExpression.Claim(claimer);
if (condition == null)
{
failpoint.Fail();
return(null);
}
TExpression action = TExpression.Claim(claimer);
TWhile whileBlock = new TWhile
{
_condition = condition,
_action = action
};
return(whileBlock);
}
internal static TExpression CopyExpression(TExpression exp)
{
TExpression fstExp = null;
TExpression lastExp = null;
while (exp != null)
{
TExpression expnew = new TExpression();
expnew.UL = exp.UL;
expnew.ValNB = exp.ValNB;
expnew.ValStr = exp.ValStr;
expnew.ValVar = exp.ValVar;
expnew.ValCall = exp.ValCall;
expnew.Index = exp.Index;
if (fstExp == null)
{
fstExp = expnew;
}
else
{
lastExp.Next = expnew;
}
expnew.Prev = lastExp;
lastExp = expnew;
exp = exp.Next;
//expnew.Next = null;
}
return(fstExp);
}
public static TExpression GenerateExpression <TExpression>()
where TExpression : DataTypeExpression, new()
{
var expression = new TExpression();
return(expression);
}
public virtual void testRemoveFunctionCallExpr()
{
TExpression expression = parser.parseExpression("fx(columnA)");
Assert.IsTrue(expression.ExpressionType == EExpressionType.function_t);
expression.remove();
Assert.IsTrue(expression.ExpressionType == EExpressionType.removed_t);
}
public virtual void testRemoveObjectExpr()
{
TExpression expression = parser.parseExpression("columnA");
Assert.IsTrue(expression.ExpressionType == EExpressionType.simple_object_name_t);
expression.remove();
Assert.IsTrue(expression.ExpressionType == EExpressionType.removed_t);
}
public virtual void checkExpression(TExpression expr)
{
expr.postOrderTraverse(this);
for (int i = 0; i < removeExprs.Count; i++)
{
removeExprs[i].remove();
}
removeExprs.Clear();
}
public virtual void testRemoveAdditionalParenthesisOfSubquery()
{
TSelectSqlStatement select = null, subquery = null;
parser.sqltext = "select * from ((select * from some_table where some_column < ?)) some_view where a_column = something";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
subquery = select.tables.getTable(0).Subquery;
subquery.ParenthesisCount = 1;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM (SELECT *\n" +
" FROM some_table\n" +
" WHERE some_column < ?) some_view\n" +
"WHERE a_column = something"
));
parser.sqltext = "(((select a from b)) order by a)";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.ParenthesisCount = 0;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "(( SELECT a\n" +
" FROM b))\n" +
" ORDER BY a"
));
parser.sqltext = "((((select a from b)) order by a))";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.ParenthesisCount = 1;
select.ParenthesisCountBeforeOrder = 1;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "(( SELECT a\n" +
" FROM b)\n" +
" ORDER BY a)"
));
parser.sqltext = "select * from user_table where ((username like '%admin%'));";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TExpression expression = select.WhereClause.Condition;
select.WhereClause.Condition = expression.LeftOperand;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM user_table\n" +
"WHERE ( username LIKE '%admin%' )"
));
}
public virtual bool exprVisit(TParseTreeNode pNode, bool isLeafNode)
{
if (pNode is TExpression)
{
TExpression expr = (TExpression)pNode;
if ((Regex.Match(expr.ToString(), "'\\s*'").Success || Regex.Match(expr.ToString(), "\"\\s*\"").Success) && (expr.ParentExpr != null && isLogicExpression(expr.ParentExpr)))
{
piggybacked = true;
}
}
return(true);
}
private void removeObjectFromResultColumn(TCustomSqlStatement stmt, TObjectName column)
{
if (stmt.ResultColumnList != null)
{
for (int i = 0; i < stmt.ResultColumnList.Count; i++)
{
TResultColumn resultSetColumn = stmt.ResultColumnList.getResultColumn(i);
TExpression expression = resultSetColumn.Expr;
switch (expression.ExpressionType)
{
case EExpressionType.simple_object_name_t:
if (column.startToken == expression.startToken && column.endToken == expression.endToken)
{
stmt.ResultColumnList.removeResultColumn(i);
return;
}
break;
}
}
}
if (stmt is TInsertSqlStatement)
{
TInsertSqlStatement insert = (TInsertSqlStatement)stmt;
if (insert.ColumnList == null)
{
return;
}
for (int i = 0; i < insert.ColumnList.Count; i++)
{
TObjectName insertColumn = insert.ColumnList.getObjectName(i);
if (column.startToken == insertColumn.startToken && column.endToken == insertColumn.endToken)
{
if (insert.Values != null)
{
for (int j = 0; j < insert.Values.Count; j++)
{
TMultiTarget target = insert.Values[j];
if (target.ColumnList != null && target.ColumnList.Count == insert.ColumnList.Count)
{
target.ColumnList.removeResultColumn(i);
}
}
}
insert.ColumnList.removeObjectName(i);
return;
}
}
}
}
public void testTreat()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "SELECT name, TREAT(VALUE(p) AS employee_t).salary salary \n" + " FROM persons p;";
Assert.IsTrue(sqlparser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0);
TResultColumn column = select.ResultColumnList.getResultColumn(1);
TExpression expr = column.Expr;
TFunctionCall f = expr.FunctionCall;
Assert.IsTrue(expr.ExpressionType == EExpressionType.object_access_t);
}
public new static TProgram Claim(StringClaimer claimer)
{
TProgram prog = new TProgram();
TExpression e;
while ((e = TExpression.Claim(claimer)) != null)
{
claimer.Claim(SemiColon);
prog._expressions.Add(e);
}
return(prog);
}
public virtual void testCreateComparisonPredicate()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvoracle);
TExpression left = sqlParser.parseExpression("salary");
TExpression right = sqlParser.parseExpression("20");
TExpression plus = new TExpression(EExpressionType.simple_comparison_t, left, right, EComparisonType.greaterThanOrEqualTo);
//plus.ExpressionType = EExpressionType.simple_comparison_t;
//plus.ComparisonType = EComparisonType.greaterThanOrEqualTo;
//plus.LeftOperand = left;
//plus.RightOperand = right;
Assert.IsTrue(verifyScript(EDbVendor.dbvoracle, "salary >= 20", plus.ToScript()));
}
public virtual void testCreateBinaryExpression()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvoracle);
TExpression left = sqlParser.parseExpression("1");
TExpression right = sqlParser.parseExpression("2");
TExpression plus = new TExpression(EExpressionType.arithmetic_plus_t, left, right);
//plus.ExpressionType = EExpressionType.arithmetic_plus_t;
//plus.LeftOperand = left;
//plus.RightOperand = right;
//System.out.println(plus.ToScript());
Assert.IsTrue(verifyScript(EDbVendor.dbvoracle, "1 + 2 ", plus.ToScript()));
}
public static void Main(string[] args)
{
FileInfo file = null;
List <string> argList = new List <string>(args);
int index = argList.IndexOf("/f");
if (index != -1 && args.Length > index + 1)
{
file = new FileInfo(args[index + 1]);
}
TGSqlParser sqlparser;
if (file != null)
{
sqlparser = new TGSqlParser(Common.GetEDbVendor(args));
sqlparser.sqlfilename = file.FullName;
}
else
{
sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "select col1, col2,sum(col3) from table1, table2 where col4 > col5 and col6= 1000 or c1 = 1 and not sal";
}
int ret = sqlparser.parse();
if (ret == 0)
{
TCustomSqlStatement select = sqlparser.sqlstatements.get(0);
if (select.WhereClause != null)
{
TExpression expr = select.WhereClause.Condition;
Console.WriteLine("pre order");
expr.preOrderTraverse(new exprVisitor());
Console.WriteLine("\nin order");
expr.inOrderTraverse(new exprVisitor());
Console.WriteLine("\npost order");
expr.postOrderTraverse(new exprVisitor());
expr.postOrderTraverse(new exprVisitor());
}
}
else
{
Console.WriteLine(sqlparser.Errormessage);
}
}
public virtual void testColumnInAndOr()
{
TExpression expression = parser.parseExpression("columnA+(columnB*2)>columnC and columnD=columnE-9");
TExpressionList resultList = expression.searchColumn("columnA");
Assert.IsTrue(resultList.size() == 1);
TExpression columnAExpr = resultList.getExpression(0);
Assert.IsTrue(columnAExpr.ExpressionType == EExpressionType.simple_object_name_t);
Assert.IsTrue(columnAExpr.ToString().Equals("columnA", StringComparison.CurrentCultureIgnoreCase));
Assert.IsTrue(expression.ToScript().Equals("columnA + (columnB * 2) > columnC and columnD = columnE - 9", StringComparison.OrdinalIgnoreCase));
columnAExpr.remove();
Assert.IsTrue(expression.ToScript().Equals("columnD = columnE - 9", StringComparison.OrdinalIgnoreCase));
}
private static void AnalyzeUpdateStmt(TUpdateSqlStatement stmt)
{
Console.WriteLine(string.Format("Table name: {0}", stmt.TargetTable.ToString()));
Console.WriteLine("set clause:");
for (int i = 0; i < stmt.ResultColumnList.Count; i++)
{
TResultColumn resultColumn = stmt.ResultColumnList.getResultColumn(i);
TExpression expression = resultColumn.Expr;
Console.WriteLine(string.Format("\tColumn: {0}\tvalue: {1}", expression.LeftOperand.ToString(), expression.RightOperand.ToString()));
}
if (stmt.WhereClause != null)
{
Console.WriteLine("Where clause: {0}", stmt.WhereClause.Condition.ToString());
}
}
public virtual void testRemoveColumnInFunctionCall()
{
TExpression expression = parser.parseExpression("fx(columnA,columnB,fx2(1+columnC))");
Assert.IsTrue(expression.ExpressionType == EExpressionType.function_t);
TExpressionList resultList = expression.searchColumn("columnC");
Assert.IsTrue(resultList.size() == 1);
TExpression columnCExpr = resultList.getExpression(0);
columnCExpr.remove();
Assert.IsTrue(expression.ExpressionType == EExpressionType.removed_t);
}
public void testExtractXML()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "SELECT warehouse_name,\n" + " EXTRACT(warehouse_spec, '/Warehouse/Docks')\n" + " \"Number of Docks\"\n" + " FROM warehouses\n" + " WHERE warehouse_name = 'San Francisco';";
Assert.IsTrue(sqlparser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0);
TResultColumn column = select.ResultColumnList.getResultColumn(1);
TExpression expr = column.Expr;
TFunctionCall f = expr.FunctionCall;
Assert.IsTrue(f.FunctionType == EFunctionType.extractxml_t);
Assert.IsTrue(f.XMLType_Instance.ToString().Equals("warehouse_spec", StringComparison.CurrentCultureIgnoreCase));
Assert.IsTrue(f.XPath_String.ToString().Equals("'/Warehouse/Docks'", StringComparison.CurrentCultureIgnoreCase));
}
private void removeObjectFromWhereCondition(TCustomSqlStatement stmt, TObjectName column)
{
TWhereClause where = stmt.WhereClause;
if (where == null)
{
return;
}
TExpression condition = where.Condition;
removeObjectFromExpression(condition, column);
if (where.Condition.ToScript().Trim().Length == 0)
{
stmt.WhereClause = null;
}
}
public void testOwnStmtOfExpr()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = @"DELETE FROM employee e WHERE NOT EXISTS ( SELECT * FROM department d WHERE e.emp_dept = d.dpt_id)";
Assert.IsTrue(sqlparser.parse() == 0);
TDeleteSqlStatement delete = (TDeleteSqlStatement)sqlparser.sqlstatements.get(0);
TExpression condition = delete.WhereClause.Condition;
TExpression subqueryExpr = condition.RightOperand;
TSelectSqlStatement subquery = subqueryExpr.SubQuery;
condition = subquery.WhereClause.Condition;
TCustomSqlStatement ownStmt = condition.OwnerStmt;
Assert.IsTrue(ownStmt.sqlstatementtype == ESqlStatementType.sstselect);
}
public virtual void testColumnInAndOr2()
{
parser.sqltext = "SELECT m.*, \n" + " altname.last_name last_name_student, \n" + " altname.first_name first_name_student, \n" + " ccu.date_joined, \n" + " ccu.last_login, \n" + " ccu.photo_id, \n" + " ccu.last_updated \n" + "FROM summit.mstr m, \n" + " summit.alt_name altname, \n" + " smmtccon.ccn_user ccu \n" + "WHERE m.id =?\n" + " AND m.id = altname.id(+) \n" + " AND m.id = ccu.id(+) \n" + " AND altname.grad_name_ind(+) = '*'";
int ret = parser.parse();
Assert.IsTrue(ret == 0);
TSelectSqlStatement selectSqlStatement = (TSelectSqlStatement)parser.sqlstatements.get(0);
TExpression expression = selectSqlStatement.WhereClause.Condition;
expression.RightOperand.remove();
Assert.IsTrue(expression.ToScript().Equals("m.id = ? and m.id = altname.id(+) and m.id = ccu.id(+)", StringComparison.OrdinalIgnoreCase));
expression.RightOperand.remove();
Assert.IsTrue(expression.ToScript().Equals("m.id = ? and m.id = altname.id(+)", StringComparison.OrdinalIgnoreCase));
expression.RightOperand.remove();
Assert.IsTrue(expression.ToScript().Equals("m.id = ?", StringComparison.OrdinalIgnoreCase));
}
public new static TIf Claim(StringClaimer claimer)
{
Claim failpoint = claimer.FailPoint();
Claim c = claimer.Claim(If);
if (!c.Success)
{
failpoint.Fail();
return(null);
}
TExpression condition = TExpression.Claim(claimer);
if (condition == null)
{
failpoint.Fail();
return(null);
}
TExpression action = TExpression.Claim(claimer);
TIf ifblock = new TIf
{
_condition = condition,
_action = action
};
c = claimer.Claim(Else);
if (!c.Success)
{
return(ifblock);
}
TExpression otherwise = TExpression.Claim(claimer);
if (otherwise == null)
{
c.Fail();
}
else
{
ifblock._otherwise = otherwise;
c.Pass();
}
return(ifblock);
}
public virtual void checkFunctionCall(TFunctionCall func)
{
if (func.Args != null)
{
for (int k = 0; k < func.Args.size(); k++)
{
TExpression expr = func.Args.getExpression(k);
if (expr.SubQuery != null)
{
expr.SubQuery.String = removevars.remove(expr.SubQuery);
}
}
}
if (func.AnalyticFunction != null)
{
TExpressionList list = func.AnalyticFunction.PartitionBy_ExprList;
if (list != null && list.size() > 0)
{
for (int i = 0; i < list.size(); i++)
{
TExpression expr = list.getExpression(i);
if (expr.SubQuery != null)
{
expr.SubQuery.String = removevars.remove(expr.SubQuery);
}
}
}
if (func.AnalyticFunction.OrderBy != null)
{
TOrderByItemList orderByItemList = func.AnalyticFunction.OrderBy.Items;
if (orderByItemList != null && orderByItemList.size() > 0)
{
for (int i = 0; i < orderByItemList.size(); i++)
{
TExpression sortKey = orderByItemList.getOrderByItem(i).SortKey;
if (sortKey.SubQuery != null)
{
sortKey.SubQuery.String = removevars.remove(sortKey.SubQuery);
}
}
}
}
}
}
public virtual void testColumnInAndOr1()
{
parser.sqltext = "select *\n" + "from table1 pal, table2 pualr, table3 pu\n" + "WHERE (pal.application_location_id = pualr.application_location_id \n" + " AND pu.jbp_uid = pualr.jbp_uid \n" + " AND pu.username = '******')";
int ret = parser.parse();
Assert.IsTrue(ret == 0);
TSelectSqlStatement selectSqlStatement = (TSelectSqlStatement)parser.sqlstatements.get(0);
TExpression expression = selectSqlStatement.WhereClause.Condition;
TExpressionList resultList = expression.searchColumn("application_location_id");
Assert.IsTrue(resultList.size() == 2);
TExpression expression1 = resultList.getExpression(0);
Assert.IsTrue(expression1.ExpressionType == EExpressionType.simple_object_name_t);
Assert.IsTrue(expression1.ToString().Equals("pal.application_location_id", StringComparison.CurrentCultureIgnoreCase));
expression1.remove();
Assert.IsTrue(expression.ToScript().Equals("(pu.jbp_uid = pualr.jbp_uid and pu.username = '******')", StringComparison.OrdinalIgnoreCase));
}
public new static TLeftExpression Claim(StringClaimer claimer)
{
Claim failTo = claimer.FailPoint();
TExpression newLeft = TExpression.Claim(claimer);
if (newLeft == null)
{
failTo.Fail();
return(null);
}
newLeft = LeftClaim(claimer, newLeft);
if (newLeft != null)
{
return((TLeftExpression)newLeft);
}
failTo.Fail();
return(null);
}
public virtual bool exprVisit(TParseTreeNode pNode, bool isLeafNode)
{
TExpression expr = (TExpression)pNode;
switch ((expr.ExpressionType))
{
case EExpressionType.simple_object_name_t:
TObjectName obj = expr.ObjectOperand;
if (obj.ObjectType != TObjectName.ttobjNotAObject)
{
Console.WriteLine(getColumnWithBaseTable(obj));
}
break;
case EExpressionType.function_t:
functionCallVisitor fcv = new functionCallVisitor(statement);
expr.FunctionCall.accept(fcv);
break;
}
return(true);
}
