private void parseSubQueryColumnDefinition(TCreateTableSqlStatement createTable, TSelectSqlStatement stmt, TableMetaData tableMetaData, ColumnImpactModel columnImpactModel)
{
if (stmt.SetOperatorType != ESetOperatorType.none)
{
parseSubQueryColumnDefinition(createTable, stmt.LeftStmt, tableMetaData, columnImpactModel);
parseSubQueryColumnDefinition(createTable, stmt.RightStmt, tableMetaData, columnImpactModel);
}
else
{
int columnCount = stmt.ResultColumnList.size();
string[] aliasNames = new string[columnCount];
if (createTable.ColumnList != null && createTable.ColumnList.size() > 0)
{
columnCount = createTable.ColumnList.size();
aliasNames = new string[columnCount];
TColumnDefinitionList items = createTable.ColumnList;
for (int i = 0; i < items.size(); i++)
{
aliasNames[i] = items.getColumn(i).ToString();
}
}
for (int i = 0; i < columnCount; i++)
{
TResultColumn resultColumn = stmt.ResultColumnList.getResultColumn(i);
parseColumnDefinition(resultColumn, tableMetaData, columnImpactModel, aliasNames[i]);
}
}
}
private void parseSubQueryColumnDefinition(TCreateViewSqlStatement createView, TSelectSqlStatement stmt, TableMetaData viewMetaData, ColumnImpactModel columnImpactModel)
{
if (stmt.SetOperatorType != ESetOperatorType.none)
{
parseSubQueryColumnDefinition(createView, stmt.LeftStmt, viewMetaData, columnImpactModel);
parseSubQueryColumnDefinition(createView, stmt.RightStmt, viewMetaData, columnImpactModel);
}
else
{
int columnCount = stmt.ResultColumnList.size();
string[] aliasNames = new string[columnCount];
if (createView.ViewAliasClause != null)
{
columnCount = createView.ViewAliasClause.ViewAliasItemList.size();
aliasNames = new string[columnCount];
TViewAliasItemList items = createView.ViewAliasClause.ViewAliasItemList;
for (int i = 0; i < items.size(); i++)
{
if (items.getViewAliasItem(i).Alias != null)
{
aliasNames[i] = items.getViewAliasItem(i).Alias.ToString();
}
}
}
for (int i = 0; i < columnCount; i++)
{
TResultColumn resultColumn = stmt.ResultColumnList.getResultColumn(i);
parseColumnDefinition(resultColumn, viewMetaData, columnImpactModel, aliasNames[i]);
}
}
}
public virtual void testAddResultColumnInSelectList()
{
parser.sqltext = "SELECT A as A_Alias, B AS B_Alias FROM TABLE_X";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TResultColumnList columns = select.ResultColumnList;
TResultColumn resultColumn = new TResultColumn();
resultColumn.Expr = parser.parseExpression("d");
columns.addResultColumn(resultColumn);
TAliasClause aliasClause = new TAliasClause();
aliasClause.AliasName = parser.parseObjectName("d_alias");
aliasClause.HasAs = true;
resultColumn.AliasClause = aliasClause;
// System.out.println(scriptGenerator.generateScript(select, true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT a AS a_alias,\n" +
" b AS b_alias,\n" +
" d AS d_alias\n" +
"FROM table_x"
));
}
public virtual void testModifySelectList()
{
parser.sqltext = "select t1.f1, t2.f2 as f2 from table1 t1 left join table2 t2 on t1.f1 = t2.f2 ";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.ResultColumnList.removeElementAt(1);
select.ResultColumnList.removeElementAt(0);
TResultColumn resultColumn1 = new TResultColumn();
resultColumn1.Expr = parser.parseExpression("t1.f3");
TAliasClause aliasClause1 = new TAliasClause();
aliasClause1.AliasName = parser.parseObjectName("f1");
aliasClause1.HasAs = true;
resultColumn1.AliasClause = aliasClause1;
select.ResultColumnList.addResultColumn(resultColumn1);
TResultColumn resultColumn2 = new TResultColumn();
resultColumn2.Expr = parser.parseExpression("t2.f3");
select.ResultColumnList.addResultColumn(resultColumn2);
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT t1.f3 AS f1,\n" +
" t2.f3\n" +
"FROM table1 t1\n" +
" LEFT JOIN table2 t2\n" +
" ON t1.f1 = t2.f2"
));
}
private void parseInsertColumnDefinition(TResultColumn resultColumn, TableMetaData viewMetaData, ColumnImpactModel columnImpactModel, string aliasName)
{
ColumnMetaData columnMetaData = new ColumnMetaData();
columnMetaData.Table = viewMetaData;
if (!string.ReferenceEquals(aliasName, null))
{
columnMetaData.DisplayName = aliasName;
columnMetaData.Name = aliasName;
}
if (tableColumns[viewMetaData] == null)
{
return;
}
int index = tableColumns[viewMetaData].IndexOf(columnMetaData);
if (index != -1)
{
columnMetaData = tableColumns[viewMetaData][index];
}
else
{
tableColumns[viewMetaData].Add(columnMetaData);
}
ColumnMetaData[] referColumns = getRefTableColumns(resultColumn, columnImpactModel);
for (int i = 0; i < referColumns.Length; i++)
{
columnMetaData.addReferColumn(referColumns[i]);
}
}
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;
}
}
}
}
internal static void printColumns(TResultColumn cl, TCustomSqlStatement sqlStatement)
{
if (cl.AliasClause != null)
{
Console.WriteLine("\nResult column:" + cl.AliasClause.ToString());
}
else
{
Console.WriteLine("\nResult column:" + cl.Expr.ToString());
}
(new columnInClause()).printColumns(cl.Expr, sqlStatement);
}
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 virtual void testModifyAlias()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = @"Select X as [Y]";
sqlparser.parse();
TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0);
TResultColumn resultColumn = select.ResultColumnList.getResultColumn(0);
TAliasClause aliasClause = resultColumn.AliasClause;
aliasClause.AliasName = parser.parseObjectName("\"Y\"");
//Console.WriteLine(sqlparser.sqlstatements.get(0).ToScript());
}
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 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());
}
}
private void parseSubQueryColumnDefinition(TInsertSqlStatement insert, TSelectSqlStatement stmt, TableMetaData tableMetaData, ColumnImpactModel columnImpactModel)
{
if (stmt.SetOperatorType != ESetOperatorType.none)
{
parseSubQueryColumnDefinition(insert, stmt.LeftStmt, tableMetaData, columnImpactModel);
parseSubQueryColumnDefinition(insert, stmt.RightStmt, tableMetaData, columnImpactModel);
}
else
{
if (insert.ColumnList != null)
{
TObjectNameList items = insert.ColumnList;
int columnCount = items.size();
string[] aliasNames = new string[columnCount];
for (int i = 0; i < items.size(); i++)
{
aliasNames[i] = items.getObjectName(i).ToString();
}
for (int i = 0; i < columnCount; i++)
{
TResultColumn resultColumn = null;
if (i < stmt.ResultColumnList.size())
{
resultColumn = stmt.ResultColumnList.getResultColumn(i);
}
else
{
resultColumn = stmt.ResultColumnList.getResultColumn(stmt.ResultColumnList.size() - 1);
}
parseInsertColumnDefinition(resultColumn, tableMetaData, columnImpactModel, aliasNames[i]);
}
}
else if (insert.SubQuery != null && insert.SubQuery.ResultColumnList != null)
{
int columnCount = insert.SubQuery.ResultColumnList.size();
String[] aliasNames = new String[columnCount];
for (int i = 0; i < columnCount; i++)
{
TResultColumn resultColumn = insert.SubQuery.ResultColumnList.getResultColumn(i);
parseColumnDefinition(resultColumn, tableMetaData, columnImpactModel, aliasNames[i]);
}
}
}
}
private void removeObjectFromSetClause(TCustomSqlStatement stmt, TObjectName column)
{
if (stmt is TUpdateSqlStatement)
{
TUpdateSqlStatement update = (TUpdateSqlStatement)stmt;
if (update.ResultColumnList != null)
{
for (int i = 0; i < update.ResultColumnList.Count; i++)
{
TResultColumn resultcolumn = update.ResultColumnList.getResultColumn(i);
if (resultcolumn.startToken.posinlist <= column.startToken.posinlist && resultcolumn.endToken.posinlist >= column.endToken.posinlist)
{
update.ResultColumnList.removeResultColumn(i);
break;
}
}
}
}
}
public virtual void testRemoveResultColumnInSelectList()
{
parser.sqltext = "SELECT A as A_Alias, B AS B_Alias FROM TABLE_X";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TResultColumnList columns = select.ResultColumnList;
columns.removeElementAt(1);
TResultColumn resultColumn = new TResultColumn();
resultColumn.Expr = parser.parseExpression("x");
columns.addResultColumn(resultColumn);
// System.out.println(scriptGenerator.generateScript(select, true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT a AS a_alias,\n" +
" x\n" +
"FROM table_x"
));
}
public virtual void testRemoveIntoClause()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = @"SELECT * INTO newTbl FROM ( SELECT * FROM Instructor) AS tmp;";
sqlparser.parse();
TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0);
// remove into clause
select.IntoClause = null;
// remove * in the select list
select.ResultColumnList.removeElementAt(0);
// add a new select list item
TResultColumn resultColumn = new TResultColumn();
resultColumn.Expr = sqlparser.parseExpression("count(*)");
TAliasClause aliasClause = new TAliasClause();
aliasClause.AliasName = sqlparser.parseObjectName("count");
aliasClause.HasAs = true;
resultColumn.AliasClause = aliasClause;
select.ResultColumnList.addResultColumn(resultColumn);
// Console.WriteLine(sqlparser.sqlstatements.get(0).ToScript());
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, @"select
count(*) as count
from
( select
*
from
Instructor) as tmp"
));
}
public virtual void testOracleBindVariable()
{
parser.sqltext = "UPDATE Art SET Desc=:Desc WHERE ID=:ID";
Assert.IsTrue(parser.parse() == 0);
TUpdateSqlStatement updateStmt = (TUpdateSqlStatement)parser.sqlstatements.get(0);
TResultColumn setClause = updateStmt.ResultColumnList.getResultColumn(0);
TExpression assignment = setClause.Expr;
// create a vairable
TObjectName bindVar = new TObjectName(new TSourceToken(":bindVar"), EDbObjectType.variable);
TExpression newVariable = new TExpression(EExpressionType.simple_object_name_t);
newVariable.ObjectOperand = bindVar;
//set new varaible
assignment.RightOperand = newVariable;
//Console.WriteLine(updateStmt.ToScript());
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, updateStmt.ToScript()
, "UPDATE Art SET Desc=:bindVar WHERE ID=:ID"
));
}
public void testXmlAgg()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "SELECT\n" + " O.OBJECT_ID,\n" + " '|' || RTRIM (XMLAGG (XMLELEMENT (K, O.KEY_1 || '|')).EXTRACT ('//text()'),\n" + "'|') || '|' AS TEXT_KEY\n" + "FROM DAG_OBJECT_FACT O";
Assert.IsTrue(sqlparser.parse() == 0);
TResultColumn rc = ((TSelectSqlStatement)sqlparser.sqlstatements.get(0)).ResultColumnList.getResultColumn(1);
TExpression expression = rc.Expr.LeftOperand.RightOperand;
TFunctionCall functionCall = expression.FunctionCall;
TExpression xmlaggExpr = functionCall.Args.getExpression(0);
Assert.IsTrue(xmlaggExpr.ExpressionType == EExpressionType.object_access_t);
TObjectAccess objectAccess = xmlaggExpr.ObjectAccess;
TFunctionCall xmlelement = objectAccess.ObjectExpr.FunctionCall.Args.getExpression(0).FunctionCall;
//Assert.IsTrue(xmlelement.getArgs().size() == 1);
Assert.IsTrue(xmlelement.XMLElementNameExpr.ToString().Equals("K", StringComparison.CurrentCultureIgnoreCase));
TResultColumn resultColumn = xmlelement.XMLElementValueExprList.getResultColumn(0);
TExpression expression1 = resultColumn.Expr;
Assert.IsTrue(expression1.ToString().Equals("O.KEY_1 || '|'", StringComparison.CurrentCultureIgnoreCase));
}
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());
}
}
}
}
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 void analyzeSqlStatement(procedureInfo procedureInfo, TCustomSqlStatement stmt)
{
if (stmt is TMssqlBlock)
{
TMssqlBlock block = (TMssqlBlock)stmt;
if (block.BodyStatements != null)
{
for (int i = 0; i < block.BodyStatements.size(); i++)
{
analyzeSqlStatement(procedureInfo, block.BodyStatements.get(i));
}
}
}
else if (stmt is TMssqlIfElse)
{
TMssqlIfElse ifElse = (TMssqlIfElse)stmt;
if (ifElse.Stmt != null)
{
analyzeSqlStatement(procedureInfo, ifElse.Stmt);
}
if (ifElse.Condition != null)
{
}
if (ifElse.ElseStmt != null)
{
analyzeSqlStatement(procedureInfo, ifElse.ElseStmt);
}
}
else if (stmt is TMssqlDeclare)
{
TMssqlDeclare declareStmt = (TMssqlDeclare)stmt;
if (declareStmt.Subquery != null && declareStmt.Subquery.ToString().Trim().Length > 0)
{
analyzeSqlStatement(procedureInfo, declareStmt.Subquery);
}
}
else if (stmt is TMssqlExecute && ((TMssqlExecute)stmt).ModuleName != null)
{
TMssqlExecute executeStmt = (TMssqlExecute)stmt;
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.SP;
operateInfo.objectUsed = executeStmt.ModuleName.ToString().Trim();
operateInfo.usageType = usageType.Exec;
procedureInfo.operates.Add(operateInfo);
}
else if (stmt is TCreateTableSqlStatement)
{
TCreateTableSqlStatement createStmt = (TCreateTableSqlStatement)stmt;
TColumnDefinitionList columns = createStmt.ColumnList;
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.Table;
operateInfo.objectUsed = createStmt.TargetTable.ToString().Trim();
operateInfo.usageType = usageType.Create;
for (int i = 0; i < columns.size(); i++)
{
TColumnDefinition column = columns.getColumn(i);
operateInfo.columns.Add(column.ColumnName.ToString());
}
procedureInfo.operates.Add(operateInfo);
}
else if (stmt is TInsertSqlStatement)
{
TInsertSqlStatement insertStmt = (TInsertSqlStatement)stmt;
TObjectNameList columns = insertStmt.ColumnList;
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.Table;
operateInfo.objectUsed = insertStmt.TargetTable.ToString().Trim();
operateInfo.usageType = usageType.Insert;
if (columns != null)
{
for (int i = 0; i < columns.size(); i++)
{
TObjectName column = columns.getObjectName(i);
operateInfo.columns.Add(column.ToString());
}
}
procedureInfo.operates.Add(operateInfo);
// if (insertStmt.ExecStmt != null)
// {
// analyzeSqlStatement(procedureInfo, insertStmt.ExecStmt);
// }
}
else if (stmt is TUpdateSqlStatement)
{
TUpdateSqlStatement updateStmt = (TUpdateSqlStatement)stmt;
TResultColumnList columns = updateStmt.ResultColumnList;
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.Table;
operateInfo.objectUsed = updateStmt.TargetTable.ToString().Trim();
operateInfo.usageType = usageType.Update;
for (int i = 0; i < columns.size(); i++)
{
TResultColumn column = columns.getResultColumn(i);
operateInfo.columns.Add(column.Expr.LeftOperand.ToString());
}
procedureInfo.operates.Add(operateInfo);
}
else if (stmt is TDeleteSqlStatement)
{
TDeleteSqlStatement deleteStmt = (TDeleteSqlStatement)stmt;
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.Table;
operateInfo.objectUsed = deleteStmt.TargetTable.ToString().Trim();
operateInfo.usageType = usageType.Delete;
procedureInfo.operates.Add(operateInfo);
}
else if (stmt is TMssqlDropTable)
{
TMssqlDropTable dropStmt = (TMssqlDropTable)stmt;
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.Table;
operateInfo.objectUsed = dropStmt.TargetTable.ToString().Trim();
operateInfo.usageType = usageType.Drop;
procedureInfo.operates.Add(operateInfo);
}
else if (stmt is TDropTableSqlStatement)
{
TDropTableSqlStatement dropStmt = (TDropTableSqlStatement)stmt;
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.Table;
operateInfo.objectUsed = dropStmt.TableName.ToString().Trim();
operateInfo.usageType = usageType.Drop;
procedureInfo.operates.Add(operateInfo);
}
else if (stmt is TSelectSqlStatement)
{
TSelectSqlStatement selectStmt = (TSelectSqlStatement)stmt;
List <columnInfo> columnInfos = new List <columnInfo>();
List <tableInfo> tableInfos = new List <tableInfo>();
tableTokensInStmt(columnInfos, tableInfos, selectStmt);
LinkedHashMap <tableInfo, List <columnInfo> > columnMap = new LinkedHashMap <tableInfo, List <columnInfo> >();
for (int i = 0; i < columnInfos.Count; i++)
{
columnInfo column = columnInfos[i];
tableInfo table = column.table;
if (columnMap.ContainsKey(table))
{
List <columnInfo> columns = (List <columnInfo>)columnMap[table];
bool flag = false;
foreach (columnInfo temp in columns)
{
if (temp.ToString().Equals(column.ToString(), StringComparison.CurrentCultureIgnoreCase))
{
flag = true;
break;
}
}
if (!flag)
{
columns.Add(column);
}
}
else
{
List <columnInfo> columns = new List <columnInfo>();
columnMap[table] = columns;
columns.Add(column);
}
}
for (int i = 0; i < tableInfos.Count; i++)
{
operateInfo operateInfo = new operateInfo();
operateInfo.objectType = objectType.Table;
operateInfo.objectUsed = tableInfos[i].ToString();
if (tableInfos[i].stmt is TSelectSqlStatement && ((TSelectSqlStatement)tableInfos[i].stmt).IntoClause != null)
{
operateInfo.usageType = usageType.Insert;
}
else
{
operateInfo.usageType = usageType.Read;
}
if (columnMap.ContainsKey(tableInfos[i]))
{
foreach (columnInfo column in (List <columnInfo>)columnMap[tableInfos[i]])
{
operateInfo.columns.Add(column.ToString());
operateInfo.objectUsed = column.table.ToString();
}
}
procedureInfo.operates.Add(operateInfo);
}
}
}
private ColumnMetaData[] getRefTableColumns(TResultColumn resultColumn, ColumnImpactModel columnImpactModel)
{
ReferenceModel[] referenceModels = columnImpactModel.References;
List <ColumnMetaData> columns = new List <ColumnMetaData>();
if (resultColumn.AliasClause != null)
{
for (int i = 0; i < referenceModels.Length; i++)
{
ReferenceModel referModel = referenceModels[i];
// if ( referModel.getClause( ) != Clause.SELECT )
// continue;
if (referModel.Alias != null)
{
string aliasName = resultColumn.AliasClause.AliasName.ToString();
if (removeQuote(referModel.Alias.Name).Equals(removeQuote(aliasName), StringComparison.CurrentCultureIgnoreCase))
{
ColumnMetaData columnMetaData = getColumn(referModel.Column);
if (columnMetaData != null && !columns.Contains(columnMetaData))
{
columns.Add(columnMetaData);
}
}
}
}
}
else
{
for (int i = 0; i < referenceModels.Length; i++)
{
ReferenceModel referModel = referenceModels[i];
// if ( referModel.getClause( ) != Clause.SELECT )
// continue;
if (referModel.Field != null)
{
if (resultColumn.FieldAttr != null)
{
if (removeQuote(resultColumn.FieldAttr.ToString()).Equals(removeQuote(referModel.Field.FullName), StringComparison.CurrentCultureIgnoreCase))
{
ColumnMetaData columnMetaData = getColumn(referModel.Column);
if (!columns.Contains(columnMetaData))
{
columns.Add(columnMetaData);
}
}
}
else
{
if (removeQuote(resultColumn.ToString()).Equals(removeQuote(referModel.Field.FullName), StringComparison.CurrentCultureIgnoreCase))
{
ColumnMetaData columnMetaData = getColumn(referModel.Column);
if (!columns.Contains(columnMetaData))
{
columns.Add(columnMetaData);
}
}
}
}
}
}
return(columns.ToArray());
}
public static TObjectName[] getTableColumns(TTable table)
{
Table createTable = ModelBindingManager.getCreateTable(table);
if (createTable != null)
{
IList <TableColumn> columnList = createTable.Columns;
TObjectName[] columns = new TObjectName[columnList.Count];
for (int i = 0; i < columns.Length; i++)
{
columns[i] = columnList[i].ColumnObject;
}
Array.Sort(columns, new ComparatorAnonymousInnerClass());
return(columns);
}
else
{
TObjectNameList list = table.ObjectNameReferences;
List <TObjectName> columns = new List <TObjectName>();
if (list.size() == 0 && table.Subquery != null)
{
ResultSet resultSet = (ResultSet)ModelBindingManager.getModel(table.Subquery);
if (resultSet != null)
{
IList <ResultColumn> columnList = resultSet.Columns;
for (int i = 0; i < columnList.Count; i++)
{
ResultColumn resultColumn = columnList[i];
if (resultColumn.ColumnObject is TResultColumn)
{
TResultColumn columnObject = ((TResultColumn)resultColumn.ColumnObject);
TAliasClause alias = columnObject.AliasClause;
if (alias != null && alias.AliasName != null)
{
columns.Add(alias.AliasName);
}
else
{
if (columnObject.FieldAttr != null)
{
columns.Add(columnObject.FieldAttr);
}
else
{
continue;
}
}
}
else if (resultColumn.ColumnObject is TObjectName)
{
columns.Add((TObjectName)resultColumn.ColumnObject);
}
}
}
}
else
{
for (int i = 0; i < list.size(); i++)
{
columns.Add(list.getObjectName(i));
}
}
TObjectName[] columnArray = columns.ToArray();
Array.Sort(columnArray, new ComparatorAnonymousInnerClass());
return(columnArray);
}
}
