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));
}
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 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 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 virtual void testColumnInAndOr3()
{
parser.sqltext = "select *\n" + "from ods_trf_pnb_stuf_lijst_adrsrt2 lst\n" + "\t\t, ods_stg_pnb_stuf_pers_adr pas\n" + "\t\t, ods_stg_pnb_stuf_pers_nat nat\n" + "\t\t, ods_stg_pnb_stuf_adr adr\n" + "\t\t, ods_stg_pnb_stuf_np prs\n" + "where \n" + "\tpas.soort_adres = lst.soort_adres\n" + "\tand prs.id(+) = nat.prs_id\n" + "\tand adr.id = pas.adr_id\n" + "\tand prs.id = pas.prs_id\n" + " and lst.persoonssoort = 'PERSOON'\n" + " and pas.einddatumrelatie is null";
int ret = parser.parse();
Assert.IsTrue(ret == 0);
TSelectSqlStatement selectSqlStatement = (TSelectSqlStatement)parser.sqlstatements.get(0);
TExpression expression = selectSqlStatement.WhereClause.Condition;
TExpressionList resultList = expression.searchColumn("lst.soort_adres");
Assert.IsTrue(resultList.size() == 1);
TExpression expression1 = resultList.getExpression(0);
Assert.IsTrue(expression1.ExpressionType == EExpressionType.simple_object_name_t);
expression1.remove();
resultList = expression.searchColumn("nat.prs_id");
Assert.IsTrue(resultList.size() == 1);
expression1 = resultList.getExpression(0);
Assert.IsTrue(expression1.ExpressionType == EExpressionType.simple_object_name_t);
expression1.remove();
resultList = expression.searchColumn("adr.id");
Assert.IsTrue(resultList.size() == 1);
expression1 = resultList.getExpression(0);
Assert.IsTrue(expression1.ExpressionType == EExpressionType.simple_object_name_t);
expression1.remove();
resultList = expression.searchColumn("prs.id");
Assert.IsTrue(resultList.size() == 1);
expression1 = resultList.getExpression(0);
Assert.IsTrue(expression1.ExpressionType == EExpressionType.simple_object_name_t);
expression1.remove();
Assert.IsTrue(expression.ToScript().Trim().Equals("lst.persoonssoort = \'PERSOON\' and pas.einddatumrelatie is null", StringComparison.OrdinalIgnoreCase));
}
private void removeObjectFromHavingClause(TCustomSqlStatement stmt, TObjectName column)
{
if (!(stmt is TSelectSqlStatement))
{
return;
}
TSelectSqlStatement select = (TSelectSqlStatement)stmt;
TGroupBy groupBy = select.GroupByClause;
if (groupBy == null)
{
return;
}
TExpression expression = groupBy.HavingClause;
removeObjectFromExpression(expression, column);
if (expression.ToScript().Trim().Length == 0)
{
groupBy.HavingClause = null;
}
}
public virtual void testCreateSubqueryPredicate()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvoracle);
TExpression left = sqlParser.parseExpression("salary");
TExpression right = sqlParser.parseExpression("(select sal from emp where empno=1)");
TExpression subqueryPredicate = new TExpression(EExpressionType.simple_comparison_t, left, right, EComparisonType.notLessThan);
//subqueryPredicate.ExpressionType = EExpressionType.simple_comparison_t;
//subqueryPredicate.ComparisonType = EComparisonType.notLessThan;
//subqueryPredicate.LeftOperand = left;
//subqueryPredicate.RightOperand = right;
Assert.IsTrue(verifyScript(EDbVendor.dbvoracle, "salary !< (select sal from emp where empno=1)", subqueryPredicate.ToScript()));
subqueryPredicate.ExpressionType = EExpressionType.group_comparison_t;
subqueryPredicate.ComparisonType = EComparisonType.greaterThanOrEqualTo;
subqueryPredicate.QuantifierType = EQuantifierType.all;
//System.out.println(subqueryPredicate.ToScript());
Assert.IsTrue(verifyScript(EDbVendor.dbvoracle, "salary >= all (select sal from emp where empno=1)", subqueryPredicate.ToScript()));
}
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()));
}
internal virtual string remove(TCustomSqlStatement stat, LinkedHashMap <string, string> conditionMap)
{
if (stat.ResultColumnList != null)
{
for (int j = 0; j < stat.ResultColumnList.size(); j++)
{
TResultColumn column = stat.ResultColumnList.getResultColumn(j);
if (column.Expr != null && column.Expr.SubQuery is TCustomSqlStatement)
{
TCustomSqlStatement query = (TCustomSqlStatement)column.Expr.SubQuery;
getParserString(query, conditionMap);
}
}
}
if (stat.CteList != null)
{
for (int i = 0; i < stat.CteList.size(); i++)
{
TCTE cte = stat.CteList.getCTE(i);
if (cte.Subquery != null)
{
getParserString(cte.Subquery, conditionMap);
}
if (cte.InsertStmt != null)
{
getParserString(cte.InsertStmt, conditionMap);
}
if (cte.UpdateStmt != null)
{
getParserString(cte.UpdateStmt, conditionMap);
}
if (cte.PreparableStmt != null)
{
getParserString(cte.PreparableStmt, conditionMap);
}
if (cte.DeleteStmt != null)
{
getParserString(cte.DeleteStmt, conditionMap);
}
}
}
if (stat is TSelectSqlStatement && ((TSelectSqlStatement)stat).SetOperator != TSelectSqlStatement.setOperator_none)
{
TSelectSqlStatement select = ((TSelectSqlStatement)stat);
getParserString(select.LeftStmt, conditionMap);
getParserString(select.RightStmt, conditionMap);
return(select.ToScript());
}
if (stat.Statements != null && stat.Statements.size() > 0)
{
for (int i = 0; i < stat.Statements.size(); i++)
{
getParserString(stat.Statements.get(i), conditionMap);
}
}
if (stat.ReturningClause != null)
{
if (stat.ReturningClause.ColumnValueList != null)
{
for (int i = 0; i < stat.ReturningClause.ColumnValueList.size(); i++)
{
if (stat.ReturningClause.ColumnValueList.getExpression(i).SubQuery != null)
{
getParserString(stat.ReturningClause.ColumnValueList.getExpression(i).SubQuery, conditionMap);
}
}
}
if (stat.ReturningClause.VariableList != null)
{
for (int i = 0; i < stat.ReturningClause.VariableList.size(); i++)
{
if (stat.ReturningClause.VariableList.getExpression(i).SubQuery != null)
{
getParserString(stat.ReturningClause.VariableList.getExpression(i).SubQuery, conditionMap);
}
}
}
}
if (stat is TSelectSqlStatement)
{
TTableList list = ((TSelectSqlStatement)stat).tables;
for (int i = 0; i < list.size(); i++)
{
TTable table = list.getTable(i);
if (table.Subquery != null)
{
getParserString(table.Subquery, conditionMap);
}
if (table.FuncCall != null)
{
ExpressionChecker w = new ExpressionChecker(this);
w.checkFunctionCall(table.FuncCall, conditionMap);
}
}
}
if (stat is TSelectSqlStatement)
{
TJoinList list = ((TSelectSqlStatement)stat).joins;
for (int i = 0; i < list.size(); i++)
{
TJoin join = list.getJoin(i);
for (int j = 0; j < join.JoinItems.size(); j++)
{
TJoinItem joinItem = join.JoinItems.getJoinItem(j);
if (joinItem.Table != null)
{
if (joinItem.Table.Subquery != null)
{
getParserString(joinItem.Table.Subquery, conditionMap);
}
if (joinItem.Table.FuncCall != null)
{
ExpressionChecker w = new ExpressionChecker(this);
w.checkFunctionCall(joinItem.Table.FuncCall, conditionMap);
}
if (joinItem.OnCondition != null)
{
ExpressionChecker w = new ExpressionChecker(this);
w.checkExpression(joinItem.OnCondition, conditionMap);
}
}
}
}
}
if (stat is TSelectSqlStatement)
{
TSelectSqlStatement select = (TSelectSqlStatement)stat;
for (int i = 0; i < select.ResultColumnList.size(); i++)
{
TResultColumn field = select.ResultColumnList.getResultColumn(i);
TExpression expr = field.Expr;
if (expr != null && expr.ExpressionType == EExpressionType.subquery_t)
{
getParserString(expr.SubQuery, conditionMap);
}
}
}
if (stat.WhereClause != null && stat.WhereClause.Condition != null && stat.WhereClause.Condition.ToScript().Trim().Length > 0)
{
TExpression whereExpression = stat.Gsqlparser.parseExpression(stat.WhereClause.Condition.ToScript());
if (string.ReferenceEquals(whereExpression.ToString(), null))
{
removeCondition removeCondition = new removeCondition(stat.ToString(), stat.dbvendor, conditionMap);
return(removeCondition.result);
}
else
{
string oldString = stat.ToScript();
conditionBuffer.Remove(0, conditionBuffer.Length);
ExpressionChecker w = new ExpressionChecker(this);
w.checkExpression(whereExpression, conditionMap);
stat.WhereClause.Condition = stat.Gsqlparser.parseExpression(whereExpression.ToScript());
}
}
if ((stat is TSelectSqlStatement) && ((TSelectSqlStatement)stat).GroupByClause != null && ((TSelectSqlStatement)stat).GroupByClause.HavingClause != null)
{
TExpression havingExpression = ((TSelectSqlStatement)stat).GroupByClause.HavingClause;
if (havingExpression == null)
{
removeCondition removeCondition = new removeCondition(stat.ToScript(), stat.dbvendor, conditionMap);
return(removeCondition.result);
}
else
{
string oldString = stat.ToScript();
conditionBuffer.Remove(0, conditionBuffer.Length);
ExpressionChecker w = new ExpressionChecker(this);
w.checkExpression(havingExpression, conditionMap);
string newString = stat.ToScript();
if (!oldString.Equals(newString))
{
if (havingExpression != null && havingExpression.ToScript().Trim().Length == 0)
{
((TSelectSqlStatement)stat).GroupByClause = null;
}
}
}
}
return(stat.ToScript());
}
