public virtual void printColumns(TGroupByItemList list, TCustomSqlStatement statement)
{
Console.WriteLine("Referenced columns:");
groupByVisitor gbv = new groupByVisitor(statement);
list.accept(gbv);
}
public static string generateVirtualTableName(TCustomSqlStatement stmt)
{
lock (typeof(SQLUtil))
{
if (virtualTableNames.ContainsKey(stmt.ToString()))
{
return(virtualTableNames[stmt.ToString()]);
}
else
{
string tableName = null;
virtualTableIndex++;
if (virtualTableIndex == 0)
{
tableName = "RESULT SET COLUMNS";
}
else
{
tableName = "RESULT SET COLUMNS " + virtualTableIndex;
}
virtualTableNames[stmt.ToString()] = tableName;
return(tableName);
}
}
}
private void removeObjectFromOrderBy(TCustomSqlStatement stmt, TObjectName column)
{
if (!(stmt is TSelectSqlStatement))
{
return;
}
TSelectSqlStatement select = (TSelectSqlStatement)stmt;
TOrderBy orderBy = select.OrderbyClause;
if (orderBy == null)
{
return;
}
for (int i = 0; i < orderBy.Items.Count; i++)
{
TOrderByItem item = orderBy.Items.getOrderByItem(i);
if (item.startToken.posinlist <= column.startToken.posinlist && item.endToken.posinlist >= column.endToken.posinlist)
{
orderBy.Items.removeElementAt(i);
break;
}
}
if (orderBy.Items.Count == 0)
{
select.OrderbyClause = null;
}
}
private Boolean isInjected_union_set()
{
Boolean ret = false;
if (!this.e_union_set)
{
return(false);
}
if (this.sqlParser.SqlStatements.Count() == 0)
{
return(ret);
}
TCustomSqlStatement stmt = this.sqlParser.SqlStatements[0];
if (stmt.SqlStatementType != TSqlStatementType.sstSelect)
{
return(ret);
}
TSelectSqlStatement select = (TSelectSqlStatement)stmt;
if (select.SelectSetType != TSelectSetType.sltNone)
{
this.getSqlInjections().Add(new TSQLInjection(ESQLInjectionType.union_set));
ret = true;
}
return(ret);
}
private void removeObjectFromStatement(TCustomSqlStatement stmt, TObjectName column)
{
if (column.Location == ESqlClause.resultColumn)
{
removeObjectFromResultColumn(stmt, column);
}
else if (column.Location == ESqlClause.where)
{
removeObjectFromWhereCondition(stmt, column);
}
else if (column.Location == ESqlClause.orderby)
{
removeObjectFromOrderBy(stmt, column);
}
else if (column.Location == ESqlClause.groupby)
{
removeObjectFromGroupBy(stmt, column);
}
else if (column.Location == ESqlClause.having)
{
removeObjectFromHavingClause(stmt, column);
}
else if (column.Location == ESqlClause.set)
{
removeObjectFromSetClause(stmt, column);
}
else
{
Console.WriteLine("Not yet implements removing column from " + column.Location);
}
}
protected internal static void iterateStmt(TCustomSqlStatement stmt)
{
// System.out.println(stmt.sqlstatementtype.toString());
for (int i = 0;i < stmt.tables.size();i++)
{
gudusoft.gsqlparser.nodes.TTable table = stmt.tables.getTable(i);
string table_name = table.Name;
Console.WriteLine("Analyzing: " + table_name + " <- " + stmt.sqlstatementtype);
for (int j = 0; j < table.LinkedColumns.size(); j++)
{
TObjectName objectName = table.LinkedColumns.getObjectName(j);
string column_name = table_name + "." + objectName.ColumnNameOnly.ToLower();
if (!objectName.TableDetermined)
{
column_name = "?." + objectName.ColumnNameOnly.ToLower();
}
Console.WriteLine("Analyzing: " + column_name + " in " + stmt.sqlstatementtype + " " + objectName.Location);
}
}
for (int i = 0;i < stmt.Statements.size();i++)
{
iterateStmt(stmt.Statements.get(i));
}
}
public static void doSearch(String filename)
{
//long t = DateTime.Now.Millisecond;
EDbVendor dbVendor = EDbVendor.dbvmssql;
Console.WriteLine("Selected SQL dialect: " + dbVendor.ToString());
TGSqlParser sqlparser = new TGSqlParser(dbVendor);
sqlparser.sqlfilename = filename;
int ret = sqlparser.parse();
if (ret == 0)
{
functionVisitor fv = new functionVisitor();
for (int i = 0; i < sqlparser.sqlstatements.size(); i++)
{
TCustomSqlStatement sqlStatement = sqlparser.sqlstatements.get(i);
Console.WriteLine(sqlStatement.sqlstatementtype);
sqlStatement.acceptChildren(fv);
}
}
else
{
Console.WriteLine(sqlparser.Errormessage);
}
//Console.WriteLine("Time Escaped: " + (DateTime.Now.Millisecond - t));
}
private void parseStatement(TCustomSqlStatement stmt)
{
if (!stmtList.Contains(stmt.ToString()))
{
stmtList.Add(stmt.ToString());
}
else
{
return;
}
if (stmt is TCreateViewSqlStatement)
{
TCreateViewSqlStatement createView = ((TCreateViewSqlStatement)stmt);
parseCreateView(createView);
}
else if (stmt is TCreateTableSqlStatement && ((TCreateTableSqlStatement)stmt).SubQuery != null)
{
TCreateTableSqlStatement createTable = ((TCreateTableSqlStatement)stmt);
parseCreateTable(createTable);
}
else if (stmt is TInsertSqlStatement && ((TInsertSqlStatement)stmt).SubQuery != null)
{
TInsertSqlStatement insert = ((TInsertSqlStatement)stmt);
parseInsertStmt(insert);
}
if (stmt is TUseDatabase)
{
TUseDatabase use = (TUseDatabase)stmt;
database = use.DatabaseName.ToString();
}
}
public static void AnalyzeStmt(TCustomSqlStatement stmt)
{
switch (stmt.sqlstatementtype)
{
case ESqlStatementType.sstselect:
AnalyzeSelectStmt((TSelectSqlStatement)stmt);
break;
case ESqlStatementType.sstupdate:
AnalyzeUpdateStmt((TUpdateSqlStatement)stmt);
break;
case ESqlStatementType.sstcreatetable:
AnalyzeCreateTableStmt((TCreateTableSqlStatement)stmt);
break;
case ESqlStatementType.sstaltertable:
break;
case ESqlStatementType.sstcreateview:
AnalyzeCreateViewStmt((TCreateViewSqlStatement)stmt);
break;
default:
Console.WriteLine(stmt.sqlstatementtype.ToString());
break;
}
}
private void removeObjectFromGroupBy(TCustomSqlStatement stmt, TObjectName column)
{
if (!(stmt is TSelectSqlStatement))
{
return;
}
TSelectSqlStatement select = (TSelectSqlStatement)stmt;
TGroupBy groupBy = select.GroupByClause;
if (groupBy == null)
{
return;
}
for (int i = 0; i < groupBy.Items.Count; i++)
{
TGroupByItem item = groupBy.Items.getGroupByItem(i);
if (item.startToken.posinlist <= column.startToken.posinlist && item.endToken.posinlist >= column.endToken.posinlist)
{
groupBy.Items.removeGroupByItem(i);
break;
}
}
if (groupBy.Items.Count == 0)
{
select.GroupByClause = null;
}
}
internal virtual bool rewriteQuery(TCustomSqlStatement sqlStatement, EDbVendor dbVendor)
{
String sourceSql = sqlStatement.ToString();
String targetSql = sqlStatement.ToScript();
return(testScriptGenerator.verifyScript(dbVendor, sourceSql, targetSql));
}
public virtual void printColumns(TExpression expression, TCustomSqlStatement statement)
{
Console.WriteLine("Referenced columns:");
columnVisitor cv = new columnVisitor(statement);
expression.postOrderTraverse(cv);
}
public virtual void printColumns(TOrderBy orderBy, TCustomSqlStatement statement)
{
Console.WriteLine("Referenced columns:");
orderByVisitor obv = new orderByVisitor(statement);
orderBy.accept(obv);
}
internal virtual void analyzeProcedure(procedureInfo procedureInfo, TMssqlCreateProcedure procedure)
{
for (int i = 0; i < procedure.Statements.size(); i++)
{
TCustomSqlStatement stmt = procedure.Statements.get(i);
analyzeSqlStatement(procedureInfo, stmt);
}
}
private void parseStatementList(TStatementList stmts)
{
for (int i = 0; i < stmts.size(); i++)
{
TCustomSqlStatement stmt = stmts.get(i);
parseStatement(stmt);
}
}
public static TTable getTable(TCustomSqlStatement stmt, TObjectName column)
{
IEnumerator iter = modelBindingMap.Values.GetEnumerator();
while (iter.MoveNext())
{
object key = iter.Current;
if (key is Table)
{
key = ((Table)key).TableObject;
}
else if (key is QueryTable)
{
key = ((QueryTable)key).TableObject;
}
else
{
continue;
}
TTable table = (TTable)key;
if (table.Subquery == stmt)
{
continue;
}
if (column.TableString != null && column.TableString.Trim().Length > 0)
{
if (table.AliasName != null)
{
if (!table.AliasName.Equals(column.TableString))
{
continue;
}
else
{
return(table);
}
}
}
TObjectName[] columns = getTableColumns(table);
for (int i = 0; i < columns.Length; i++)
{
TObjectName columnName = columns[i];
if ("*".Equals(columnName.ColumnNameOnly))
{
continue;
}
if (columnName == column)
{
return(table);
}
}
}
return(null);
}
public virtual int renameTable(string sourceTable, string targetTable)
{
this.renamedObjectsNum = 0;
string[] names = sourceTable.Split(new char[] { '.' });
if (names.Length == 1)
{
this.sourceTable = sourceTable;
this.sourceSchema = null;
}
else if (names.Length == 2)
{
this.sourceSchema = names[0];
this.sourceTable = names[1];
}
else
{
this.msg = "source table name must in syntax like this: schema.tablename, or tablename";
return(-1);
}
names = targetTable.Split(new char[] { '.' });
if (names.Length == 1)
{
this.targetTable = targetTable;
this.targetSchema = null;
}
else if (names.Length == 2)
{
this.targetSchema = names[0];
this.targetTable = names[1];
}
else
{
this.msg = "target table name must in syntax like this: schema.tablename, or tablename";
return(-1);
}
int ret = sqlparser.parse();
if (ret != 0)
{
msg = "syntax error: " + sqlparser.Errormessage;
return(-1);
}
for (int i = 0; i < sqlparser.sqlstatements.size(); i++)
{
TCustomSqlStatement sql = sqlparser.sqlstatements.get(i);
modifyTableName(sql);
}
this.msg = "renamed table occurs:" + this.renamedObjectsNum;
return(renamedObjectsNum);
}
private void deleteColumn(TCustomSqlStatement stmt)
{
for (int k = 0; k < stmt.tables.size(); k++)
{
TTable table = stmt.tables.getTable(k);
if (table.TableName == null)
{
continue;
}
bool isThisTable = true;
isThisTable = table.TableName.TableString.Equals(this.sourceTable, StringComparison.OrdinalIgnoreCase);
if (!isThisTable)
{
continue;
}
if (!string.ReferenceEquals(this.sourceSchema, null) && table.TableName != null)
{
if (table.TableName.SchemaString != null)
{
isThisTable = table.TableName.SchemaString.Equals(this.sourceSchema, StringComparison.OrdinalIgnoreCase);
}
else
{
isThisTable = false;
}
}
if (!isThisTable)
{
continue;
}
for (int m = 0; m < table.LinkedColumns.size(); m++)
{
TObjectName column = table.LinkedColumns.getObjectName(m);
if (column.ColumnToken != null)
{
if (column.ColumnNameOnly.Equals(this.sourceColumn, StringComparison.OrdinalIgnoreCase))
{
removeObjectFromStatement(stmt, column);
this.renamedObjectsNum++;
}
}
}
}
for (int j = 0; j < stmt.Statements.size(); j++)
{
deleteColumn(stmt.Statements.get(j));
}
}
internal static void iterateStmt(TCustomSqlStatement pStmt)
{
if (pStmt is TSelectSqlStatement)
{
processSelect((TSelectSqlStatement)pStmt);
}
for (int i = 0; i < pStmt.Statements.size(); i++)
{
iterateStmt(pStmt.Statements.get(i));
}
}
static void printStmt(TCustomSqlStatement pstmt)
{
Console.WriteLine(pstmt.AsText + "\n");
for (int j = 0; j < pstmt.ChildNodes.Count(); j++)
{
if (pstmt.ChildNodes[j] is TCustomSqlStatement)
{
printStmt((TCustomSqlStatement)(pstmt.ChildNodes[j]));
}
}
}
static void printStmt(TCustomSqlStatement pstmt)
{
Console.WriteLine(pstmt.AsText + "\n");
for (int j = 0; j < pstmt.ChildNodes.Count(); j++)
{
if (pstmt.ChildNodes[j] is TCustomSqlStatement)
{
printStmt((TCustomSqlStatement)(pstmt.ChildNodes[j]));
}
}
}
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;
}
}
}
}
private void AnalyzeStmt(TCustomSqlStatement psql)
{
switch (psql.SqlStatementType)
{
case TSqlStatementType.sstSelect:
AnalyzeSelectStmt((TSelectSqlStatement)psql);
break;
default:
Console.WriteLine(psql.SqlStatementType.ToString());
break;
}
}
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);
}
private void parseStatement(TCustomSqlStatement stmt)
{
if (stmt is TUseDatabase)
{
TUseDatabase use = (TUseDatabase)stmt;
database = use.DatabaseName.ToString();
}
else if (stmt is TStoredProcedureSqlStatement)
{
TStoredProcedureSqlStatement procedureStmt = (TStoredProcedureSqlStatement)stmt;
parseProcedureStmt(procedureStmt);
}
}
private void checkDDL(TGSqlParser sqlparser)
{
int ret = sqlparser.parse();
if (ret == 0)
{
TStatementList stmts = sqlparser.sqlstatements;
for (int i = 0; i < stmts.size(); i++)
{
TCustomSqlStatement stmt = stmts.get(i);
parseStatement(stmt);
}
}
}
public static void Main(string[] args)
{
FileInfo file = null;
List <string> argList = new List <string>(args);
int index = argList.IndexOf("/f");
if (index != -1 && args.Length > index + 1)
{
file = new FileInfo(args[index + 1]);
}
TGSqlParser sqlparser;
if (file != null)
{
sqlparser = new TGSqlParser(Common.GetEDbVendor(args));
sqlparser.sqlfilename = file.FullName;
}
else
{
sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "select col1, col2,sum(col3) from table1, table2 where col4 > col5 and col6= 1000 or c1 = 1 and not sal";
}
int ret = sqlparser.parse();
if (ret == 0)
{
TCustomSqlStatement select = sqlparser.sqlstatements.get(0);
if (select.WhereClause != null)
{
TExpression expr = select.WhereClause.Condition;
Console.WriteLine("pre order");
expr.preOrderTraverse(new exprVisitor());
Console.WriteLine("\nin order");
expr.inOrderTraverse(new exprVisitor());
Console.WriteLine("\npost order");
expr.postOrderTraverse(new exprVisitor());
expr.postOrderTraverse(new exprVisitor());
}
}
else
{
Console.WriteLine(sqlparser.Errormessage);
}
}
public static ParseResult ExtractColumns(string sqlStr)
{
TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVOracle);
sqlparser.SqlText.Text = sqlStr;
int ret = sqlparser.Parse();
if (ret != 0)
{
throw new Exception("Unable to parse sql.");
}
tablelist = new List <string>();
columnlist = new List <string>();
databaselist = new List <string>();
schemalist = new List <string>();
functionlist = new List <string>();
triggerlist = new List <string>();
sequencelist = new List <string>();
tcList = new StringBuilder();
for (int i = 0; i < sqlparser.SqlStatements.Count(); i++)
{
TCustomSqlStatement sql = sqlparser.SqlStatements[i];
AnalyzeStmt(sql, 0);
}
SortAndRemoveDup(tablelist);
SortAndRemoveDup(columnlist);
SortAndRemoveDup(databaselist);
SortAndRemoveDup(schemalist);
SortAndRemoveDup(functionlist);
SortAndRemoveDup(triggerlist);
SortAndRemoveDup(sequencelist);
ParseResult result = new ParseResult()
{
Columnlist = columnlist,
Databaselist = databaselist,
Functionlist = functionlist,
Schemalist = schemalist,
Sequencelist = schemalist,
Tablelist = tablelist,
Triggerlist = triggerlist,
Structure = tcList.ToString()
};
return(result);
}
public virtual void testModifyTableInCreateTable()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvimpala);
sqlparser.sqltext = "create table if not exists campaign_1 ( id int, name string )";
int ret = sqlparser.parse();
TCustomSqlStatement stmt = sqlparser.sqlstatements.get(0);
TTable table = stmt.tables.getTable(0);
table.TableName = parser.parseObjectName("prefix_." + table.TableName.ToString());
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, table.ToScript()
, "prefix_.campaign_1"
));
}
private void removeObjectFromWhereCondition(TCustomSqlStatement stmt, TObjectName column)
{
TWhereClause where = stmt.WhereClause;
if (where == null)
{
return;
}
TExpression condition = where.Condition;
removeObjectFromExpression(condition, column);
if (where.Condition.ToScript().Trim().Length == 0)
{
stmt.WhereClause = null;
}
}
internal virtual void remove(TGSqlParser sqlparser)
{
int i = sqlparser.parse();
if (i == 0)
{
TCustomSqlStatement stat = sqlparser.sqlstatements.get(0);
getParserString(stat);
result = stat.ToScript();
}
else
{
Console.Error.WriteLine(sqlparser.Errormessage);
}
}
private void AnalyzeStmt(TCustomSqlStatement psql)
{
switch (psql.SqlStatementType)
{
case TSqlStatementType.sstSelect:
AnalyzeSelectStmt((TSelectSqlStatement) psql);
break;
default:
Console.WriteLine(psql.SqlStatementType.ToString());
break;
}
}
