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();
}
}
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;
}
}
}
}
public void testNetezzaSelectFromExternalTable()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvnetezza);
sqlparser.sqltext = "insert into \"$hist_column_access_1\"(npsid, npsinstanceid, opid, logentryid, seqid, sessionid, dbid, dbname, schemaid, schemaname, tableid, tablename, columnid, columnname, usage) select 1, * from external '/nz/data.1.0/hist/loading/alc_20140215_151309.473439/alc_co_20140215_151309.473439' (npsinstanceid int, opid bigint, logentryid bigint, seqid integer, sessionid bigint, dbid int, dbname nvarchar (128), schemaid int , schemaname nvarchar (128), tableid int, tablename nvarchar (128), columnid int, columnname nvarchar (128), usage int) using( logdir '/nz/data.1.0/hist/loading/alc_20140215_151309.473439' encoding 'internal' escapechar '\\' fillrecord true ctrlchars true crinstring true)";
Assert.IsTrue(sqlparser.parse() == 0);
TInsertSqlStatement insertSqlStatement = (TInsertSqlStatement)sqlparser.sqlstatements.get(0);
TTable table = insertSqlStatement.tables.getTable(0);
Assert.IsTrue(table.TableName.ToString().Equals("\"$hist_column_access_1\"", StringComparison.CurrentCultureIgnoreCase));
TSelectSqlStatement select = (TSelectSqlStatement)insertSqlStatement.SubQuery;
table = select.tables.getTable(0);
Assert.IsTrue(table.TableName.ToString().Equals("'/nz/data.1.0/hist/loading/alc_20140215_151309.473439/alc_co_20140215_151309.473439'", StringComparison.CurrentCultureIgnoreCase));
}
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 parseInsertStmt(TInsertSqlStatement insert)
{
if (insert.TargetTable.TableName != null)
{
string tableName = insert.TargetTable.TableName.TableString;
string tableSchema = insert.TargetTable.TableName.SchemaString;
string databaseName = insert.TargetTable.TableName.DatabaseString;
TableMetaData tableMetaData = new TableMetaData(vendor, strict);
tableMetaData.Name = tableName;
tableMetaData.SchemaName = tableSchema;
if (isNotEmpty(databaseName))
{
tableMetaData.CatalogName = databaseName;
}
else
{
tableMetaData.CatalogName = database;
}
tableMetaData.View = false;
if (!tableColumns.ContainsKey(tableMetaData))
{
tableColumns[tableMetaData] = new List <ColumnMetaData>();
}
else
{
IList <TableMetaData> tables = new List <TableMetaData>(tableColumns.Keys);
tableMetaData = (TableMetaData)tables[tables.IndexOf(tableMetaData)];
tableMetaData.View = false;
}
if (insert.SubQuery != null)
{
ColumnImpact impact = new ColumnImpact(insert.SubQuery.ToString(), insert.dbvendor, tableColumns, strict);
impact.ignoreTopSelect(true);
impact.Debug = false;
impact.ShowUIInfo = true;
impact.TraceErrorMessage = false;
impact.impactSQL();
ColumnImpactModel columnImpactModel = impact.generateModel();
parseSubQueryColumnDefinition(insert, insert.SubQuery, tableMetaData, columnImpactModel);
}
}
}
public void testCreateProcedure()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvdb2);
sqlparser.sqltext = "CREATE PROCEDURE CREATE_T_EMP()\n" + " LANGUAGE SQL\n" + "BEGIN\n" + "DECLARE SQLCODE INT;\n" + "DECLARE l_sqlcode INT DEFAULT 0;\n" + "\n" + " DECLARE CONTINUE HANDLER FOR NOT FOUND\n" + " SET l_sqlcode = SQLCODE; \n" + "\n" + " INSERT INTO PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTDATE) \n" + " VALUES('HG0023', 'NEW NETWORK', 'E11', '200280', CURRENT DATE); \n" + "END";
Assert.IsTrue(sqlparser.parse() == 0);
Assert.IsTrue(sqlparser.sqlstatements.get(0).sqlstatementtype == ESqlStatementType.sstdb2createprocedure);
TDb2CreateProcedure procedure = (TDb2CreateProcedure)sqlparser.sqlstatements.get(0);
// System.out.print(procedure.getBodyStatements().size());
Assert.IsTrue(procedure.DeclareStatements.size() == 3);
Assert.IsTrue(procedure.DeclareStatements.get(2).ToString().Equals("DECLARE CONTINUE HANDLER FOR NOT FOUND\n" + " SET l_sqlcode = SQLCODE", StringComparison.CurrentCultureIgnoreCase));
Assert.IsTrue(procedure.BodyStatements.size() == 1);
Assert.IsTrue(procedure.BodyStatements.get(0).sqlstatementtype == ESqlStatementType.sstinsert);
TInsertSqlStatement insert = (TInsertSqlStatement)procedure.BodyStatements.get(0);
// System.out.print(insert.toString());
// Assert.IsTrue(insert.toString() != null);
Assert.IsTrue(insert.ToString().Equals("INSERT INTO PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTDATE) \n" + " VALUES('HG0023', 'NEW NETWORK', 'E11', '200280', CURRENT DATE)", StringComparison.CurrentCultureIgnoreCase));
}
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);
}
}
}
public static void doIt()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "select employee_id,last_name,sal\n" + "from employees\n" + "where department_id = 90\n" + "group by employee_id having sal>10\n" + "order by last_name;";
sqlparser.parse();
TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0);
gudusoft.gsqlparser.nodes.TTable table = select.tables.getTable(0);
TObjectName o;
Console.WriteLine("Select statement, find out what clause a TObjectName belongs to:");
for (int i = 0; i < table.LinkedColumns.size(); i++)
{
o = table.LinkedColumns.getObjectName(i);
Console.WriteLine(o.ToString() + "\t\t\tlocation:" + o.Location);
}
sqlparser.sqltext = "insert into emp e1 (e1.lastname,job) values('scott',10);";
sqlparser.parse();
TInsertSqlStatement insert = (TInsertSqlStatement)sqlparser.sqlstatements.get(0);
table = insert.tables.getTable(0);
Console.WriteLine("\n\nInsert statement, find out what clause a TObjectName belongs to:");
for (int i = 0; i < table.LinkedColumns.size(); i++)
{
o = table.LinkedColumns.getObjectName(i);
Console.WriteLine(o.ToString() + "\t\t\tlocation:" + o.Location);
}
sqlparser.sqltext = "update employees\n" + "set department_ID = 70\n" + "where employee_id = 113;";
sqlparser.parse();
TUpdateSqlStatement update = (TUpdateSqlStatement)sqlparser.sqlstatements.get(0);
table = update.tables.getTable(0);
Console.WriteLine("\n\nUpdate statement, find out what clause a TObjectName belongs to:");
for (int i = 0; i < table.LinkedColumns.size(); i++)
{
o = table.LinkedColumns.getObjectName(i);
Console.WriteLine(o.ToString() + "\t\t\tlocation:" + o.Location);
}
sqlparser.sqltext = "delete from employees E\n" + "where employee_id = \n" + "(select employee_sal\n" + "from emp_history\n" + "where employee_id = e.employee_id);";
sqlparser.parse();
TDeleteSqlStatement delete = (TDeleteSqlStatement)sqlparser.sqlstatements.get(0);
table = delete.tables.getTable(0);
Console.WriteLine("\n\nDelete statement, find out what clause a TObjectName belongs to:");
for (int i = 0; i < table.LinkedColumns.size(); i++)
{
o = table.LinkedColumns.getObjectName(i);
Console.WriteLine(o.ToString() + "\t\t\tlocation:" + o.Location);
}
// subquery in where clause
select = (TSelectSqlStatement)delete.Statements.get(0);
gudusoft.gsqlparser.nodes.TTable table1 = select.tables.getTable(0);
Console.WriteLine("\nSubquery in delete statement, find out what clause a TObjectName belongs to:");
for (int i = 0; i < table1.LinkedColumns.size(); i++)
{
o = table1.LinkedColumns.getObjectName(i);
Console.WriteLine(o.ToString() + "\t\t\tlocation:" + o.Location);
}
}
