public virtual void testFromClaueJoinTable()
{
parser.sqltext = "select table1.col1, table2.col2\n" + "from table1, table2\n" + "where table1.foo > table2.foo";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.joins.removeElementAt(1);
TJoin join = new TJoin();
select.joins.addJoin(join);
//join.setWithParen(true);
join.NestedParen = 1;
TTable table1 = new TTable();
table1.TableName = parser.parseObjectName("tableX");
join.Table = table1;
TJoinItem joinItem = new TJoinItem();
join.JoinItems.addJoinItem(joinItem);
joinItem.JoinType = EJoinType.join;
TTable table2 = new TTable();
table2.TableName = parser.parseObjectName("tableY");
joinItem.Table = table2;
TObjectNameList usingColumns = new TObjectNameList();
usingColumns.addObjectName(parser.parseObjectName("id"));
joinItem.UsingColumns = usingColumns;
TAliasClause aliasClause = new TAliasClause();
aliasClause.AliasName = parser.parseObjectName("table2");
aliasClause.HasAs = true;
join.AliasClause = aliasClause;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT table1.col1,\n" +
" table2.col2\n" +
"FROM table1, (\n" +
" tablex JOIN \n" +
" tabley USING (ID)) AS table2\n" +
"WHERE table1.foo > table2.foo"
));
}
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 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 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);
}
}
