public virtual void testRemoveTableAndAddJoinClause() { parser.sqltext = "SELECT * FROM t1,t2 where t1.f1=t2.f2"; Assert.IsTrue(parser.parse() == 0); TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0); TJoinList joinList = select.joins; // let's remove t2 and where clause joinList.removeElementAt(1); TJoinItem joinItem = new TJoinItem(); joinList.getJoin(0).JoinItems.addJoinItem(joinItem); joinItem.JoinType = EJoinType.left; TTable joinTable = new TTable(); joinItem.Table = joinTable; joinTable.TableName = parser.parseObjectName("t2"); joinItem.OnCondition = parser.parseExpression("t1.f1=t2.f2"); // remove where clause select.WhereClause = null; // System.out.println(scriptGenerator.generateScript(select,true)); Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle , select.ToScript() , "SELECT *\n" + "FROM t1\n" + " LEFT JOIN t2\n" + " ON t1.f1 = t2.f2" )); }
public virtual void testAddWhereClauseAfterJoin() { parser.sqltext = "SELECT tableA.itemA1, tableB.itemB1\n" + " FROM tableA\n" + " INNER JOIN tableB\n" + " ON tableB.itemB2 = tableA.itemA2\n" + " INNER JOIN (\n" + " SELECT tableC.itemC1\n" + " FROM tableC\n" + " WHERE tableC.itemC3='ABC'\n" + " GROUP BY tableC.itemC1\n" + ") unNamedJoin\n" + " ON unNamedJoin.itemC1 = tableB.itemB2\n"; Assert.IsTrue(parser.parse() == 0); TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0); TJoinList joinList = select.joins; TJoinItem item = joinList.getJoin(0).JoinItems.getJoinItem(0); TExpression expression1 = parser.parseExpression("1=1"); TExpression expression2 = new TExpression(); expression2.ExpressionType = EExpressionType.logical_and_t; TExpression parensExpr = new TExpression(); parensExpr.ExpressionType = EExpressionType.parenthesis_t; parensExpr.LeftOperand = item.OnCondition; expression2.LeftOperand = parensExpr; expression2.RightOperand = expression1; item.OnCondition = expression2; TWhereClause whereClause = new TWhereClause(); whereClause.Condition = parser.parseExpression("c>1"); select.WhereClause = whereClause; // System.out.println(scriptGenerator.generateScript(select,true)); Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle , select.ToScript() , "SELECT tablea.itema1,\n" + " tableb.itemb1\n" + "FROM tablea\n" + " INNER JOIN tableb\n" + " ON (tableb.itemb2 = tablea.itema2) AND 1 = 1\n" + " INNER JOIN ( SELECT tablec.itemc1 FROM tablec WHERE tablec.itemc3 = 'ABC' GROUP BY tablec.itemc1) unnamedjoin\n" + " ON unnamedjoin.itemc1 = tableb.itemb2\n" + "WHERE c > 1" )); }
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()); }