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"
));
}
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 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"
));
}
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 virtual void testAddTableAlias()
{
parser.sqltext = "select * from t1";
Assert.IsTrue(parser.parse() == 0);
TTable table = parser.sqlstatements.get(0).tables.getTable(0);
TAliasClause aliasClause = new TAliasClause();
aliasClause.HasAs = true;
aliasClause.AliasName = parser.parseObjectName("foo");
table.AliasClause = aliasClause;
//Assert.IsTrue(parser.sqlstatements.get(0).toString().trim().Equals("select * from t1 AS foo", StringComparison.CurrentCultureIgnoreCase));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, parser.sqlstatements.get(0).ToScript()
, "SELECT *\n" +
"FROM t1 AS foo"
));
}
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 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);
}
}
