public virtual void testCreateAndPredicate()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvoracle);
TExpression left = sqlParser.parseExpression("salary");
TExpression right = sqlParser.parseExpression("20");
TExpression left2 = sqlParser.parseExpression("location");
TExpression right2 = sqlParser.parseExpression("'NY'");
TExpression c1 = new TExpression(EExpressionType.simple_comparison_t, left, right, EComparisonType.greaterThanOrEqualTo);
//c1.ExpressionType = EExpressionType.simple_comparison_t;
//c1.ComparisonType = EComparisonType.greaterThanOrEqualTo;
//c1.LeftOperand = left;
//c1.RightOperand = right;
TExpression c2 = new TExpression(EExpressionType.simple_comparison_t, left2, right2, EComparisonType.equalsTo);
//c2.ExpressionType = EExpressionType.simple_comparison_t;
//c2.ComparisonType = EComparisonType.equalsTo;
//c2.LeftOperand = left2;
//c2.RightOperand = right2;
TExpression c3 = new TExpression(EExpressionType.logical_and_t, c1, c2);
//c3.ExpressionType = EExpressionType.logical_and_t;
//c3.LeftOperand = c1;
//c3.RightOperand = c2;
Assert.IsTrue(verifyScript(EDbVendor.dbvoracle, "salary >= 20 and location = 'NY'", c3.ToScript()));
}
public virtual void testCreateComparisonPredicate()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvoracle);
TExpression left = sqlParser.parseExpression("salary");
TExpression right = sqlParser.parseExpression("20");
TExpression plus = new TExpression(EExpressionType.simple_comparison_t, left, right, EComparisonType.greaterThanOrEqualTo);
//plus.ExpressionType = EExpressionType.simple_comparison_t;
//plus.ComparisonType = EComparisonType.greaterThanOrEqualTo;
//plus.LeftOperand = left;
//plus.RightOperand = right;
Assert.IsTrue(verifyScript(EDbVendor.dbvoracle, "salary >= 20", plus.ToScript()));
}
public virtual void testCreateBinaryExpression()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvoracle);
TExpression left = sqlParser.parseExpression("1");
TExpression right = sqlParser.parseExpression("2");
TExpression plus = new TExpression(EExpressionType.arithmetic_plus_t, left, right);
//plus.ExpressionType = EExpressionType.arithmetic_plus_t;
//plus.LeftOperand = left;
//plus.RightOperand = right;
//System.out.println(plus.ToScript());
Assert.IsTrue(verifyScript(EDbVendor.dbvoracle, "1 + 2 ", plus.ToScript()));
}
public virtual void testAssignStmt()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvoracle);
TAssignStmt assign = new TAssignStmt(sqlParser.parseExpression("ILevel"), sqlParser.parseExpression("'Low Income'"));
//assign.Left = sqlParser.parseExpression("ILevel");
//assign.Expression = sqlParser.parseExpression("'Low Income'");
//System.out.println(assign.ToScript());
Assert.IsTrue(verifyScript(EDbVendor.dbvoracle, "ILevel = 'Low Income'", assign.ToScript()));
}
public virtual void testCreateSubqueryPredicate()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvoracle);
TExpression left = sqlParser.parseExpression("salary");
TExpression right = sqlParser.parseExpression("(select sal from emp where empno=1)");
TExpression subqueryPredicate = new TExpression(EExpressionType.simple_comparison_t, left, right, EComparisonType.notLessThan);
//subqueryPredicate.ExpressionType = EExpressionType.simple_comparison_t;
//subqueryPredicate.ComparisonType = EComparisonType.notLessThan;
//subqueryPredicate.LeftOperand = left;
//subqueryPredicate.RightOperand = right;
Assert.IsTrue(verifyScript(EDbVendor.dbvoracle, "salary !< (select sal from emp where empno=1)", subqueryPredicate.ToScript()));
subqueryPredicate.ExpressionType = EExpressionType.group_comparison_t;
subqueryPredicate.ComparisonType = EComparisonType.greaterThanOrEqualTo;
subqueryPredicate.QuantifierType = EQuantifierType.all;
//System.out.println(subqueryPredicate.ToScript());
Assert.IsTrue(verifyScript(EDbVendor.dbvoracle, "salary >= all (select sal from emp where empno=1)", subqueryPredicate.ToScript()));
}
public virtual void testRemoveObjectExpr()
{
TExpression expression = parser.parseExpression("columnA");
Assert.IsTrue(expression.ExpressionType == EExpressionType.simple_object_name_t);
expression.remove();
Assert.IsTrue(expression.ExpressionType == EExpressionType.removed_t);
}
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 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"
));
}
