public virtual void testReplaceOrderByItemAndAddSortType()
{
parser.sqltext = "SELECT * FROM TABLE_X order by a";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.OrderbyClause.Items.removeElementAt(0);
TOrderBy orderBy = select.OrderbyClause;
TOrderByItem orderByItem = new TOrderByItem();
orderBy.Items.addElement(orderByItem);
orderByItem.SortKey = parser.parseExpression("b");
orderByItem.SortOrder = ESortType.asc;
orderByItem = new TOrderByItem();
orderBy.Items.addElement(orderByItem);
orderByItem.SortKey = parser.parseExpression("a1");
orderByItem.SortOrder = ESortType.desc;
//System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"ORDER BY b ASC,\n" +
" a1 DESC"
));
}
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.removeOrderByItem(i);
break;
}
}
if (orderBy.Items.Count == 0)
{
select.OrderbyClause = null;
}
}
public virtual void printColumns(TOrderBy orderBy, TCustomSqlStatement statement)
{
Console.WriteLine("Referenced columns:");
orderByVisitor obv = new orderByVisitor(statement);
orderBy.accept(obv);
}
public virtual void testAddOrderByClause()
{
parser.sqltext = "SELECT * FROM TABLE_X";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TOrderBy orderBy = new TOrderBy();
select.OrderbyClause = orderBy;
TOrderByItem orderByItem = new TOrderByItem();
orderBy.Items.addElement(orderByItem);
orderByItem.SortKey = parser.parseExpression("a");
orderByItem.SortOrder = ESortType.desc;
//System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"ORDER BY a DESC"
));
parser.sqltext = "SELECT * FROM TABLE_X where a>1";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
orderBy = new TOrderBy();
select.OrderbyClause = orderBy;
orderByItem = new TOrderByItem();
orderBy.Items.addElement(orderByItem);
orderByItem.SortKey = parser.parseExpression("a");
orderByItem.SortOrder = ESortType.desc;
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"WHERE a > 1\n" +
"ORDER BY a DESC"
));
parser.sqltext = "SELECT * FROM TABLE_X where a>1 group by a having count(*) > 1";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
orderBy = new TOrderBy();
select.OrderbyClause = orderBy;
orderByItem = new TOrderByItem();
orderBy.Items.addElement(orderByItem);
orderByItem.SortKey = parser.parseExpression("a");
orderByItem.SortOrder = ESortType.asc;
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"WHERE a > 1\n" +
"GROUP BY a\n" +
"HAVING count(*) > 1\n" +
"ORDER BY a ASC"
));
parser.sqltext = "SELECT * FROM TABLE_X where a>1 group by a having count(*) > 1 order by c desc";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
orderByItem = new TOrderByItem();
orderBy.Items.addElement(orderByItem);
orderByItem.SortKey = parser.parseExpression("a");
orderByItem.SortOrder = ESortType.asc;
select.OrderbyClause.Items.addOrderByItem(orderByItem);
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"WHERE a > 1\n" +
"GROUP BY a\n" +
"HAVING count(*) > 1\n" +
"ORDER BY c DESC,\n" +
" a ASC"
));
parser.sqltext = "SELECT * FROM TABLE_X";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TWhereClause whereClause = new TWhereClause();
whereClause.Condition = parser.parseExpression("a>1 and b>2");
select.WhereClause = whereClause;
//select.addWhereClause("a>1 and b>2") ;
orderBy = new TOrderBy();
select.OrderbyClause = orderBy;
orderByItem = new TOrderByItem();
orderBy.Items.addElement(orderByItem);
orderByItem.SortKey = parser.parseExpression("a");
orderByItem.SortOrder = ESortType.desc;
//System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"WHERE a > 1\n" +
" AND b > 2\n" +
"ORDER BY a DESC"
));
}