private void removeObjectFromWhereCondition(TCustomSqlStatement stmt, TObjectName column)
{
TWhereClause where = stmt.WhereClause;
if (where == null)
{
return;
}
TExpression condition = where.Condition;
removeObjectFromExpression(condition, column);
if (where.Condition.ToScript().Trim().Length == 0)
{
stmt.WhereClause = null;
}
}
public virtual void testAddWhereClause2()
{
parser.sqltext = "SELECT * FROM TABLE_X";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TWhereClause whereClause = new TWhereClause();
select.WhereClause = whereClause;
whereClause.Condition = parser.parseExpression("c>1");
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"WHERE c > 1"
));
}
public virtual void testAddWhereClauseAfterJoin()
{
parser.sqltext = "SELECT tableA.itemA1, tableB.itemB1\n" + " FROM tableA\n" + " INNER JOIN tableB\n" + " ON tableB.itemB2 = tableA.itemA2\n" + " INNER JOIN (\n" + " SELECT tableC.itemC1\n" + " FROM tableC\n" + " WHERE tableC.itemC3='ABC'\n" + " GROUP BY tableC.itemC1\n" + ") unNamedJoin\n" + " ON unNamedJoin.itemC1 = tableB.itemB2\n";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TJoinList joinList = select.joins;
TJoinItem item = joinList.getJoin(0).JoinItems.getJoinItem(0);
TExpression expression1 = parser.parseExpression("1=1");
TExpression expression2 = new TExpression();
expression2.ExpressionType = EExpressionType.logical_and_t;
TExpression parensExpr = new TExpression();
parensExpr.ExpressionType = EExpressionType.parenthesis_t;
parensExpr.LeftOperand = item.OnCondition;
expression2.LeftOperand = parensExpr;
expression2.RightOperand = expression1;
item.OnCondition = expression2;
TWhereClause whereClause = new TWhereClause();
whereClause.Condition = parser.parseExpression("c>1");
select.WhereClause = whereClause;
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT tablea.itema1,\n" +
" tableb.itemb1\n" +
"FROM tablea\n" +
" INNER JOIN tableb\n" +
" ON (tableb.itemb2 = tablea.itema2) AND 1 = 1\n" +
" INNER JOIN ( SELECT tablec.itemc1 FROM tablec WHERE tablec.itemc3 = 'ABC' GROUP BY tablec.itemc1) unnamedjoin\n" +
" ON unnamedjoin.itemc1 = tableb.itemb2\n" +
"WHERE c > 1"
));
}
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"
));
}
