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());
}
}
}
}
