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());
}
}
public virtual void testRemoveSetClauseInUpdate()
{
parser.sqltext = "UPDATE BLA SET A=2, B=3 WHERE X=5";
Assert.IsTrue(parser.parse() == 0);
TUpdateSqlStatement updateSqlStatement = (TUpdateSqlStatement)parser.sqlstatements.get(0);
TResultColumnList setClauses = updateSqlStatement.ResultColumnList;
setClauses.removeElementAt(0);
// System.out.println(scriptGenerator.generateScript(updateSqlStatement, true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, updateSqlStatement.ToScript()
, "UPDATE bla\n" +
"SET b=3\n" +
"WHERE x = 5"
));
}
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 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"
));
}
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);
}
}
