public virtual void testModifyJoinCondition() { parser.sqltext = "select * from t1 inner join t2 on t1.col1 = t2.col2"; Assert.IsTrue(parser.parse() == 0); TSelectSqlStatement selectSqlStatement = (TSelectSqlStatement)parser.sqlstatements.get(0); TJoin join = selectSqlStatement.joins.getJoin(0); TTable table = join.Table; table.TableName = parser.parseObjectName("t2"); TJoinItem joinItem = join.JoinItems.getJoinItem(0); table = joinItem.Table; table.TableName = parser.parseObjectName("t1"); joinItem.OnCondition = parser.parseExpression("t1.col3 = t2.col5"); // System.out.println(scriptGenerator.generateScript(selectSqlStatement, true)); Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle , selectSqlStatement.ToScript() , "SELECT *\n" + "FROM t2\n" + " INNER JOIN t1\n" + " ON t1.col3 = t2.col5" )); }
public virtual void testFromClaueJoinTable() { parser.sqltext = "select table1.col1, table2.col2\n" + "from table1, table2\n" + "where table1.foo > table2.foo"; Assert.IsTrue(parser.parse() == 0); TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0); select.joins.removeElementAt(1); TJoin join = new TJoin(); select.joins.addJoin(join); //join.setWithParen(true); join.NestedParen = 1; TTable table1 = new TTable(); table1.TableName = parser.parseObjectName("tableX"); join.Table = table1; TJoinItem joinItem = new TJoinItem(); join.JoinItems.addJoinItem(joinItem); joinItem.JoinType = EJoinType.join; TTable table2 = new TTable(); table2.TableName = parser.parseObjectName("tableY"); joinItem.Table = table2; TObjectNameList usingColumns = new TObjectNameList(); usingColumns.addObjectName(parser.parseObjectName("id")); joinItem.UsingColumns = usingColumns; TAliasClause aliasClause = new TAliasClause(); aliasClause.AliasName = parser.parseObjectName("table2"); aliasClause.HasAs = true; join.AliasClause = aliasClause; Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle , select.ToScript() , "SELECT table1.col1,\n" + " table2.col2\n" + "FROM table1, (\n" + " tablex JOIN \n" + " tabley USING (ID)) AS table2\n" + "WHERE table1.foo > table2.foo" )); }
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()); }
private static void AnalyzeSelectStmt(TSelectSqlStatement stmt) { Console.WriteLine("Select Statement"); if (stmt.CombinedQuery) { string setstr = string.Empty; switch (stmt.SetOperator) { case TSelectSqlStatement.setOperator_union: setstr = "union"; break; case TSelectSqlStatement.setOperator_unionall: setstr = "union all"; break; case TSelectSqlStatement.setOperator_except: setstr = "except"; break; case TSelectSqlStatement.setOperator_exceptall: setstr = "except all"; break; case TSelectSqlStatement.setOperator_minus: setstr = "minus"; break; case TSelectSqlStatement.setOperator_minusall: setstr = "minus all"; break; case TSelectSqlStatement.setOperator_intersect: setstr = "intersect"; break; case TSelectSqlStatement.setOperator_intersectall: setstr = "intersect all"; break; } Console.WriteLine(string.Format("set type: {0}", setstr)); Console.WriteLine("left select: "); AnalyzeSelectStmt(stmt.LeftStmt); Console.WriteLine("left select: "); AnalyzeSelectStmt(stmt.RightStmt); if (stmt.OrderbyClause != null) { if (!string.IsNullOrEmpty(stmt.OrderbyClause.ToScript())) { Console.WriteLine("Order by Clause: " + stmt.OrderbyClause.ToString()); } } } else { // select list for (int i = 0; i < stmt.ResultColumnList.Count; i++) { TResultColumn resultColumn = stmt.ResultColumnList.getResultColumn(i); Console.WriteLine(string.Format("Column: {0}, Alias: {1}", resultColumn.Expr.ToString(), (resultColumn.AliasClause == null) ? string.Empty : resultColumn.AliasClause.ToString())); } // from clause for (int i = 0; i < stmt.joins.Count; i++) { TJoin join = stmt.joins.getJoin(i); switch (join.Kind) { case TBaseType.join_source_fake: Console.WriteLine(string.Format("table: {0}, alias: {1}", join.Table.ToString(), (join.Table.AliasClause == null) ? string.Empty : join.Table.AliasClause.ToString())); break; case TBaseType.join_source_table: Console.WriteLine(string.Format("table: {0}, alias: {1}", join.Table.ToString(), (join.Table.AliasClause == null) ? string.Empty : join.Table.AliasClause.ToString())); for (int j = 0; j < join.JoinItems.Count; j++) { TJoinItem joinItem = join.JoinItems.getJoinItem(j); Console.WriteLine(string.Format("Join type: {0}", joinItem.JoinType.ToString())); Console.WriteLine(string.Format("table: {0}, alias: {1}", joinItem.Table.ToString(), (joinItem.Table.AliasClause == null) ? string.Empty : joinItem.Table.AliasClause.ToString())); if (joinItem.OnCondition != null) { Console.WriteLine(string.Format("On: {0}", joinItem.OnCondition.ToString())); } else if (joinItem.UsingColumns != null) { Console.WriteLine(string.Format("Using: {0}", joinItem.UsingColumns.ToString())); } } break; case TBaseType.join_source_join: TJoin source_join = join.Join; Console.WriteLine(string.Format("table: {0}, alias: {1}", source_join.Table.ToString(), (source_join.Table.AliasClause == null) ? string.Empty : source_join.Table.AliasClause.ToString())); for (int j = 0; j < source_join.JoinItems.Count; j++) { TJoinItem joinItem = source_join.JoinItems.getJoinItem(j); Console.WriteLine(string.Format("source_join type: {0}", joinItem.JoinType.ToString())); Console.WriteLine(string.Format("table: {0}, alias: {1}", joinItem.Table.ToString(), (joinItem.Table.AliasClause == null) ? string.Empty : joinItem.Table.AliasClause.ToString())); if (joinItem.OnCondition != null) { Console.WriteLine(string.Format("On: {0}", joinItem.OnCondition.ToString())); } else if (joinItem.UsingColumns != null) { Console.WriteLine(string.Format("Using: {0}", joinItem.UsingColumns.ToString())); } } for (int j = 0; j < join.JoinItems.Count; j++) { TJoinItem joinItem = join.JoinItems.getJoinItem(j); Console.WriteLine(string.Format("Join type: {0}", joinItem.JoinType.ToString())); Console.WriteLine(string.Format("table: {0}, alias: {1}", joinItem.Table.ToString(), (joinItem.Table.AliasClause == null) ? string.Empty : joinItem.Table.AliasClause.ToString())); if (joinItem.OnCondition != null) { Console.WriteLine(string.Format("On: {0}", joinItem.OnCondition.ToString())); } else if (joinItem.UsingColumns != null) { Console.WriteLine(string.Format("Using: {0}", joinItem.UsingColumns.ToString())); } } break; default: break; } } // where clause if (stmt.WhereClause != null) { if (!string.IsNullOrEmpty(stmt.WhereClause.ToString())) { Console.WriteLine("Where clause: " + stmt.WhereClause.Condition.ToString()); } } // group by if (stmt.GroupByClause != null) { if (!string.IsNullOrEmpty(stmt.GroupByClause.ToString())) { Console.WriteLine("group by clause: " + stmt.GroupByClause.ToString()); } } // order by if (stmt.OrderbyClause != null) { Console.WriteLine("Order by:"); for (int i = 0; i < stmt.OrderbyClause.Items.Count; i++) { Console.WriteLine(string.Format("\t{0}", stmt.OrderbyClause.Items.getOrderByItem(i).ToString())); } } // for update if (stmt.ForUpdateClause != null) { if (!string.IsNullOrEmpty(stmt.ForUpdateClause.ToString())) { Console.WriteLine("for update clause: " + stmt.ForUpdateClause.ToString()); } } // top clause if (stmt.TopClause != null) { if (!string.IsNullOrEmpty(stmt.TopClause.ToString())) { Console.WriteLine("top clause: " + stmt.TopClause.ToString()); } } // limit clause if (stmt.LimitClause != null) { if (!string.IsNullOrEmpty(stmt.LimitClause.ToString())) { Console.WriteLine("limit clause: " + stmt.LimitClause.ToString()); } } } }