public virtual void testRemoveAdditionalParenthesisOfSubquery()
{
TSelectSqlStatement select = null, subquery = null;
parser.sqltext = "select * from ((select * from some_table where some_column < ?)) some_view where a_column = something";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
subquery = select.tables.getTable(0).Subquery;
subquery.ParenthesisCount = 1;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM (SELECT *\n" +
" FROM some_table\n" +
" WHERE some_column < ?) some_view\n" +
"WHERE a_column = something"
));
parser.sqltext = "(((select a from b)) order by a)";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.ParenthesisCount = 0;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "(( SELECT a\n" +
" FROM b))\n" +
" ORDER BY a"
));
parser.sqltext = "((((select a from b)) order by a))";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.ParenthesisCount = 1;
select.ParenthesisCountBeforeOrder = 1;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "(( SELECT a\n" +
" FROM b)\n" +
" ORDER BY a)"
));
parser.sqltext = "select * from user_table where ((username like '%admin%'));";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TExpression expression = select.WhereClause.Condition;
select.WhereClause.Condition = expression.LeftOperand;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM user_table\n" +
"WHERE ( username LIKE '%admin%' )"
));
}
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"
));
}
public virtual void testAddNewConditionInWhereClause()
{
parser.sqltext = "select count(*) from TableName where NOT a OR NOT b";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TExpression expression1 = parser.parseExpression("c1=1");
TExpression expression2 = new TExpression();
expression2.ExpressionType = EExpressionType.logical_and_t;
TExpression parensExpr = new TExpression();
parensExpr.ExpressionType = EExpressionType.parenthesis_t;
parensExpr.LeftOperand = select.WhereClause.Condition;
expression2.LeftOperand = parensExpr;
expression2.RightOperand = expression1;
select.WhereClause.Condition = expression2;
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT count(*)\n" +
"FROM tablename\n" +
"WHERE ( NOT a\n" +
" OR NOT b )\n" +
" AND c1 = 1"
));
}
public virtual void testAddORConditionInWhereClause()
{
parser.sqltext = "SELECT * FROM TABLE_X where f > 0";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TExpression expression1 = parser.parseExpression("c1>1");
TExpression expression2 = new TExpression();
expression2.ExpressionType = EExpressionType.logical_or_t;
TExpression parensExpr = new TExpression();
parensExpr.ExpressionType = EExpressionType.parenthesis_t;
parensExpr.LeftOperand = select.WhereClause.Condition;
expression2.LeftOperand = parensExpr;
expression2.RightOperand = expression1;
select.WhereClause.Condition = expression2;
//System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"WHERE ( f > 0 )\n" +
" OR c1 > 1"
));
}
public virtual void testRemoveTableAndAddJoinClause()
{
parser.sqltext = "SELECT * FROM t1,t2 where t1.f1=t2.f2";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TJoinList joinList = select.joins;
// let's remove t2 and where clause
joinList.removeElementAt(1);
TJoinItem joinItem = new TJoinItem();
joinList.getJoin(0).JoinItems.addJoinItem(joinItem);
joinItem.JoinType = EJoinType.left;
TTable joinTable = new TTable();
joinItem.Table = joinTable;
joinTable.TableName = parser.parseObjectName("t2");
joinItem.OnCondition = parser.parseExpression("t1.f1=t2.f2");
// remove where clause
select.WhereClause = null;
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM t1\n" +
" LEFT JOIN t2\n" +
" ON t1.f1 = t2.f2"
));
}
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 testModifyJoinCondition()
{
parser.sqltext = "select * from t1 inner join t2 on t1.col1 = t2.col2";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement selectSqlStatement = (TSelectSqlStatement)parser.sqlstatements.get(0);
TJoin join = selectSqlStatement.joins.getJoin(0);
TTable table = join.Table;
table.TableName = parser.parseObjectName("t2");
TJoinItem joinItem = join.JoinItems.getJoinItem(0);
table = joinItem.Table;
table.TableName = parser.parseObjectName("t1");
joinItem.OnCondition = parser.parseExpression("t1.col3 = t2.col5");
// System.out.println(scriptGenerator.generateScript(selectSqlStatement, true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, selectSqlStatement.ToScript()
, "SELECT *\n" +
"FROM t2\n" +
" INNER JOIN t1\n" +
" ON t1.col3 = t2.col5"
));
}
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 testRemoveItemInOrderByClause()
{
parser.sqltext = "SELECT * FROM TABLE_X order by a,b";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.OrderbyClause.Items.removeElementAt(1);
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"ORDER BY a"
));
select.OrderbyClause = null;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\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 testSetNewWhereCondition()
{
parser.sqltext = "select t1.f1 from table1 t1 where t1.f2 = 2 ";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.WhereClause.Condition = parser.parseExpression("t1.f2>2");
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT t1.f1\n" +
"FROM table1 t1\n" +
"WHERE t1.f2 > 2"
));
}
public virtual void testRemoveCTE()
{
parser.sqltext = "with test as (select id from emp)\n" + "select * from test";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.CteList = null;
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM test"
));
}
public virtual void testRemoveWhereClause()
{
parser.sqltext = "SELECT * FROM TABLE_X where a>1 order by a";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.WhereClause = null;
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"ORDER BY a"
));
}
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 testRemoveHavingClause()
{
parser.sqltext = "SELECT\n" + "c.ID AS \"SMS.ID\"\n" + "FROM\n" + "SUMMIT.cntrb_detail c\n" + "where\n" + "c.cntrb_date >='$GivingFromDate$'\n" + "and c.cntrb_date<='$GivingThruDate$'\n" + "group by c.id\n" + "having sum(c.amt) >= '$GivingFromAmount$' and sum(c.amt) <= '$GivingThruAmount$'";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement selectSqlStatement = (TSelectSqlStatement)parser.sqlstatements.get(0);
TGroupBy groupBy = selectSqlStatement.GroupByClause;
groupBy.HavingClause = null;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, selectSqlStatement.ToScript()
, "SELECT c.ID AS \"SMS.ID\"\n" +
"FROM summit.cntrb_detail c\n" +
"WHERE c.cntrb_date >= '$GivingFromDate$'\n" +
" AND c.cntrb_date <= '$GivingThruDate$'\n" +
"GROUP BY c.ID"
));
}
public virtual void testRemoveTableInFromClauseAndRemoveWhereClause()
{
parser.sqltext = "SELECT * FROM t1,t2 where t1.f1=t2.f2";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TJoinList joinList = select.joins;
joinList.removeElementAt(0);
select.WhereClause = null;
// System.out.println(scriptGenerator.generateScript(select, true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM t2"
));
}
public virtual void testOracleAddHint()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = @"select distinct emp_id from T1";
sqlparser.parse();
TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0);
select.OracleHint = "/*+ FULL(products) */";
//Console.WriteLine(sqlparser.sqlstatements.get(0).ToScript());
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, @"select /*+ FULL(products) */ distinct
emp_id
from
T1"
));
}
public virtual void testRemoveResultColumnInSelectList()
{
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;
columns.removeElementAt(1);
TResultColumn resultColumn = new TResultColumn();
resultColumn.Expr = parser.parseExpression("x");
columns.addResultColumn(resultColumn);
// System.out.println(scriptGenerator.generateScript(select, true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT a AS a_alias,\n" +
" x\n" +
"FROM table_x"
));
}
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 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"
));
}
internal virtual string remove(TCustomSqlStatement stat, LinkedHashMap <string, string> conditionMap)
{
if (stat.ResultColumnList != null)
{
for (int j = 0; j < stat.ResultColumnList.size(); j++)
{
TResultColumn column = stat.ResultColumnList.getResultColumn(j);
if (column.Expr != null && column.Expr.SubQuery is TCustomSqlStatement)
{
TCustomSqlStatement query = (TCustomSqlStatement)column.Expr.SubQuery;
getParserString(query, conditionMap);
}
}
}
if (stat.CteList != null)
{
for (int i = 0; i < stat.CteList.size(); i++)
{
TCTE cte = stat.CteList.getCTE(i);
if (cte.Subquery != null)
{
getParserString(cte.Subquery, conditionMap);
}
if (cte.InsertStmt != null)
{
getParserString(cte.InsertStmt, conditionMap);
}
if (cte.UpdateStmt != null)
{
getParserString(cte.UpdateStmt, conditionMap);
}
if (cte.PreparableStmt != null)
{
getParserString(cte.PreparableStmt, conditionMap);
}
if (cte.DeleteStmt != null)
{
getParserString(cte.DeleteStmt, conditionMap);
}
}
}
if (stat is TSelectSqlStatement && ((TSelectSqlStatement)stat).SetOperator != TSelectSqlStatement.setOperator_none)
{
TSelectSqlStatement select = ((TSelectSqlStatement)stat);
getParserString(select.LeftStmt, conditionMap);
getParserString(select.RightStmt, conditionMap);
return(select.ToScript());
}
if (stat.Statements != null && stat.Statements.size() > 0)
{
for (int i = 0; i < stat.Statements.size(); i++)
{
getParserString(stat.Statements.get(i), conditionMap);
}
}
if (stat.ReturningClause != null)
{
if (stat.ReturningClause.ColumnValueList != null)
{
for (int i = 0; i < stat.ReturningClause.ColumnValueList.size(); i++)
{
if (stat.ReturningClause.ColumnValueList.getExpression(i).SubQuery != null)
{
getParserString(stat.ReturningClause.ColumnValueList.getExpression(i).SubQuery, conditionMap);
}
}
}
if (stat.ReturningClause.VariableList != null)
{
for (int i = 0; i < stat.ReturningClause.VariableList.size(); i++)
{
if (stat.ReturningClause.VariableList.getExpression(i).SubQuery != null)
{
getParserString(stat.ReturningClause.VariableList.getExpression(i).SubQuery, conditionMap);
}
}
}
}
if (stat is TSelectSqlStatement)
{
TTableList list = ((TSelectSqlStatement)stat).tables;
for (int i = 0; i < list.size(); i++)
{
TTable table = list.getTable(i);
if (table.Subquery != null)
{
getParserString(table.Subquery, conditionMap);
}
if (table.FuncCall != null)
{
ExpressionChecker w = new ExpressionChecker(this);
w.checkFunctionCall(table.FuncCall, conditionMap);
}
}
}
if (stat is TSelectSqlStatement)
{
TJoinList list = ((TSelectSqlStatement)stat).joins;
for (int i = 0; i < list.size(); i++)
{
TJoin join = list.getJoin(i);
for (int j = 0; j < join.JoinItems.size(); j++)
{
TJoinItem joinItem = join.JoinItems.getJoinItem(j);
if (joinItem.Table != null)
{
if (joinItem.Table.Subquery != null)
{
getParserString(joinItem.Table.Subquery, conditionMap);
}
if (joinItem.Table.FuncCall != null)
{
ExpressionChecker w = new ExpressionChecker(this);
w.checkFunctionCall(joinItem.Table.FuncCall, conditionMap);
}
if (joinItem.OnCondition != null)
{
ExpressionChecker w = new ExpressionChecker(this);
w.checkExpression(joinItem.OnCondition, conditionMap);
}
}
}
}
}
if (stat is TSelectSqlStatement)
{
TSelectSqlStatement select = (TSelectSqlStatement)stat;
for (int i = 0; i < select.ResultColumnList.size(); i++)
{
TResultColumn field = select.ResultColumnList.getResultColumn(i);
TExpression expr = field.Expr;
if (expr != null && expr.ExpressionType == EExpressionType.subquery_t)
{
getParserString(expr.SubQuery, conditionMap);
}
}
}
if (stat.WhereClause != null && stat.WhereClause.Condition != null && stat.WhereClause.Condition.ToScript().Trim().Length > 0)
{
TExpression whereExpression = stat.Gsqlparser.parseExpression(stat.WhereClause.Condition.ToScript());
if (string.ReferenceEquals(whereExpression.ToString(), null))
{
removeCondition removeCondition = new removeCondition(stat.ToString(), stat.dbvendor, conditionMap);
return(removeCondition.result);
}
else
{
string oldString = stat.ToScript();
conditionBuffer.Remove(0, conditionBuffer.Length);
ExpressionChecker w = new ExpressionChecker(this);
w.checkExpression(whereExpression, conditionMap);
stat.WhereClause.Condition = stat.Gsqlparser.parseExpression(whereExpression.ToScript());
}
}
if ((stat is TSelectSqlStatement) && ((TSelectSqlStatement)stat).GroupByClause != null && ((TSelectSqlStatement)stat).GroupByClause.HavingClause != null)
{
TExpression havingExpression = ((TSelectSqlStatement)stat).GroupByClause.HavingClause;
if (havingExpression == null)
{
removeCondition removeCondition = new removeCondition(stat.ToScript(), stat.dbvendor, conditionMap);
return(removeCondition.result);
}
else
{
string oldString = stat.ToScript();
conditionBuffer.Remove(0, conditionBuffer.Length);
ExpressionChecker w = new ExpressionChecker(this);
w.checkExpression(havingExpression, conditionMap);
string newString = stat.ToScript();
if (!oldString.Equals(newString))
{
if (havingExpression != null && havingExpression.ToScript().Trim().Length == 0)
{
((TSelectSqlStatement)stat).GroupByClause = null;
}
}
}
}
return(stat.ToScript());
}
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"
));
}
