static Query wrapQuery(FromClause fromClause, String groupName) { Select select = new Select(); select.addSymbol(new MultipleElementSymbol(groupName)); Query query = new Query(); query.setSelect(select); From from = new From(); from.addClause(fromClause); query.setFrom(from); return query; }
@Test public void testExecSubquery(){ Query query = new Query(); Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); query.setSelect(select); From from = new From(); from.addClause(new UnaryFromClause(new GroupSymbol("newModel2.Table1"))); //$NON-NLS-1$ StoredProcedure subquery = new StoredProcedure(); subquery.setProcedureName("NewVirtual.StoredQuery"); from.addClause(new SubqueryFromClause("a", subquery)); //$NON-NLS-1$ query.setFrom(from); helpTest("SELECT * FROM newModel2.Table1, (EXEC NewVirtual.StoredQuery()) AS a", //$NON-NLS-1$ "SELECT * FROM newModel2.Table1, (EXEC NewVirtual.StoredQuery()) AS a", //$NON-NLS-1$ query); }
@Test public void testFrom2() { From from = new From(); from.addClause(new UnaryFromClause(new GroupSymbol("m.g1"))); //$NON-NLS-1$ from.addClause(new JoinPredicate( new UnaryFromClause(new GroupSymbol("m.g2")), //$NON-NLS-1$ new UnaryFromClause(new GroupSymbol("m.g3")), //$NON-NLS-1$ JoinType.JOIN_CROSS) ); helpTest(from, "FROM m.g1, m.g2 CROSS JOIN m.g3"); //$NON-NLS-1$ }
@Test public void testUnionJoin() { String sql = "select * from pm1.g1 union join pm1.g2 where g1.e1 = 1"; //$NON-NLS-1$ String expected = "SELECT * FROM pm1.g1 UNION JOIN pm1.g2 WHERE g1.e1 = 1"; //$NON-NLS-1$ Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); From from = new From(); from.addClause(new JoinPredicate(new UnaryFromClause(new GroupSymbol("pm1.g1")), new UnaryFromClause(new GroupSymbol("pm1.g2")), JoinType.JOIN_UNION)); //$NON-NLS-1$ //$NON-NLS-2$ Criteria crit = new CompareCriteria(new ElementSymbol("g1.e1"), CompareCriteria.EQ, new Constant(new Integer(1))); //$NON-NLS-1$ Query command = new Query(select, from, crit, null, null); helpTest(sql, expected, command); }
@Test public void testStoredQuery2SanityCheck(){ StoredProcedure storedQuery = new StoredProcedure(); storedQuery.setProcedureName("proc1"); //$NON-NLS-1$ SPParameter parameter = new SPParameter(1, new Constant("param1")); //$NON-NLS-1$ storedQuery.setParameter(parameter); From from = new From(); SubqueryFromClause sfc = new SubqueryFromClause("x", storedQuery); //$NON-NLS-1$ from.addClause(sfc); Select select = new Select(); select.addSymbol(new ElementSymbol("x.a")); //$NON-NLS-1$ helpTest("exec proc1('param1')", "EXEC proc1('param1')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testExpandedCacheHint() { String sql = "/*+ cache( pref_mem ttl:2000) */ SELECT * FROM t1"; //$NON-NLS-1$ Query query = new Query(); Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); query.setSelect(select); From from = new From(); UnaryFromClause ufc = new UnaryFromClause(); from.addClause(ufc); ufc.setGroup(new GroupSymbol("t1")); //$NON-NLS-1$ query.setFrom(from); CacheHint hint = new CacheHint(); hint.setPrefersMemory(true); hint.setTtl(Long.valueOf(2000)); query.setCacheHint(hint); TestParser.helpTest(sql, "/*+ cache(pref_mem ttl:2000) */ SELECT * FROM t1", query); //$NON-NLS-1$ }
@Test public void testStoredQueryWithNoParameter2(){ StoredProcedure storedQuery = new StoredProcedure(); storedQuery.setProcedureName("proc1"); //$NON-NLS-1$ From from = new From(); SubqueryFromClause sfc = new SubqueryFromClause("x", storedQuery); //$NON-NLS-1$ from.addClause(sfc); Select select = new Select(); select.addSymbol(new ElementSymbol("X.A")); //$NON-NLS-1$ Query query = new Query(); query.setSelect(select); query.setFrom(from); helpTest("SELECT X.A FROM (exec proc1()) AS X", "SELECT X.A FROM (EXEC proc1()) AS X", query); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testCache() { String sql = "/*+ cache */ SELECT * FROM t1"; //$NON-NLS-1$ Query query = new Query(); Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); query.setSelect(select); From from = new From(); UnaryFromClause ufc = new UnaryFromClause(); from.addClause(ufc); ufc.setGroup(new GroupSymbol("t1")); //$NON-NLS-1$ query.setFrom(from); query.setCacheHint(new CacheHint()); TestParser.helpTest(sql, "/*+ cache */ SELECT * FROM t1", query); //$NON-NLS-1$ }
@Test public void testCacheMultipleComments() { String sql = "/* hello */ /*+ cache */ /* world */ SELECT * FROM t1"; //$NON-NLS-1$ Query query = new Query(); Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); query.setSelect(select); From from = new From(); UnaryFromClause ufc = new UnaryFromClause(); from.addClause(ufc); ufc.setGroup(new GroupSymbol("t1")); //$NON-NLS-1$ query.setFrom(from); query.setCacheHint(new CacheHint()); TestParser.helpTest(sql, "/*+ cache */ SELECT * FROM t1", query); //$NON-NLS-1$ }
@Test public void testOptionalFromClause1_1() { String sql = "SELECT * FROM /*+ optional*/ t1, t2"; //$NON-NLS-1$ Query query = new Query(); Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); query.setSelect(select); From from = new From(); UnaryFromClause ufc = new UnaryFromClause(); ufc.setGroup(new GroupSymbol("t1")); //$NON-NLS-1$ ufc.setOptional(true); from.addClause(ufc); from.addGroup(new GroupSymbol("t2")); //$NON-NLS-1$ query.setFrom(from); TestParser.helpTest(sql, "SELECT * FROM /*+ optional */ t1, t2", query); //$NON-NLS-1$ }
@Test public void testOptionalFromClause1_4() { String sql = "SELECT * /*+ optional */ FROM /*+ OptiOnal */ t1, t2"; //$NON-NLS-1$ Query query = new Query(); Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); query.setSelect(select); From from = new From(); UnaryFromClause ufc = new UnaryFromClause(); ufc.setGroup(new GroupSymbol("t1")); //$NON-NLS-1$ ufc.setOptional(true); from.addClause(ufc); from.addGroup(new GroupSymbol("t2")); //$NON-NLS-1$ query.setFrom(from); TestParser.helpTest(sql, "SELECT * FROM /*+ optional */ t1, t2", query); //$NON-NLS-1$ }
@Test public void testOptionalFromClause1_5() { String sql = "SELECT * FROM /*+ OptiOnal */ t1, t2"; //$NON-NLS-1$ Query query = new Query(); Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); query.setSelect(select); From from = new From(); UnaryFromClause ufc = new UnaryFromClause(); ufc.setGroup(new GroupSymbol("t1")); //$NON-NLS-1$ ufc.setOptional(true); from.addClause(ufc); from.addGroup(new GroupSymbol("t2")); //$NON-NLS-1$ query.setFrom(from); TestParser.helpTest(sql, "SELECT * FROM /*+ optional */ t1, t2", query); //$NON-NLS-1$ }
@Test public void testEscapedOuterJoin() { String sql = "SELECT * FROM {oj A LEFT OUTER JOIN B ON (A.x=B.x)}"; //$NON-NLS-1$ String expected = "SELECT * FROM A LEFT OUTER JOIN B ON A.x = B.x"; //$NON-NLS-1$ Query query = new Query(); Select select = new Select(); query.setSelect(select); select.addSymbol(new MultipleElementSymbol()); From from = new From(); query.setFrom(from); Criteria compareCriteria = new CompareCriteria(new ElementSymbol("A.x"), CompareCriteria.EQ, new ElementSymbol("B.x")); //$NON-NLS-1$ //$NON-NLS-2$ FromClause f1 = new UnaryFromClause(new GroupSymbol("A")); //$NON-NLS-1$ FromClause f2 = new UnaryFromClause(new GroupSymbol("B")); //$NON-NLS-1$ JoinPredicate jp = new JoinPredicate(f1, f2, JoinType.JOIN_LEFT_OUTER, Arrays.asList(new Object[] {compareCriteria})); from.addClause(jp); helpTest(sql, expected, query); }
@Test public void testOptionalFromClause1_2() { String sql = "SELECT * FROM /*+optional */ t1, t2"; //$NON-NLS-1$ Query query = new Query(); Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); query.setSelect(select); From from = new From(); UnaryFromClause ufc = new UnaryFromClause(); ufc.setGroup(new GroupSymbol("t1")); //$NON-NLS-1$ ufc.setOptional(true); from.addClause(ufc); from.addGroup(new GroupSymbol("t2")); //$NON-NLS-1$ query.setFrom(from); TestParser.helpTest(sql, "SELECT * FROM /*+ optional */ t1, t2", query); //$NON-NLS-1$ }
@Test public void testOptionalFromClause1_3() { String sql = "SELECT * FROM /*+ optional */ t1, t2"; //$NON-NLS-1$ Query query = new Query(); Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); query.setSelect(select); From from = new From(); UnaryFromClause ufc = new UnaryFromClause(); ufc.setGroup(new GroupSymbol("t1")); //$NON-NLS-1$ ufc.setOptional(true); from.addClause(ufc); from.addGroup(new GroupSymbol("t2")); //$NON-NLS-1$ query.setFrom(from); TestParser.helpTest(sql, "SELECT * FROM /*+ optional */ t1, t2", query); //$NON-NLS-1$ }
@Test public void testOptionalFromClause3() { String sql = "SELECT * FROM /*+ optional */ t1 AS a, t2"; //$NON-NLS-1$ Query query = new Query(); Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); query.setSelect(select); From from = new From(); UnaryFromClause ufc = new UnaryFromClause(); ufc.setGroup(new GroupSymbol("a", "t1")); //$NON-NLS-1$ //$NON-NLS-2$ ufc.setOptional(true); from.addClause(ufc); from.addGroup(new GroupSymbol("t2")); //$NON-NLS-1$ query.setFrom(from); TestParser.helpTest(sql, "SELECT * FROM /*+ optional */ t1 AS a, t2", query); //$NON-NLS-1$ }
@Test public void testOptionalFromClause1() { String sql = "SELECT * FROM /*+ optional */ t1, t2"; //$NON-NLS-1$ Query query = new Query(); Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); query.setSelect(select); From from = new From(); UnaryFromClause ufc = new UnaryFromClause(); ufc.setGroup(new GroupSymbol("t1")); //$NON-NLS-1$ ufc.setOptional(true); from.addClause(ufc); from.addGroup(new GroupSymbol("t2")); //$NON-NLS-1$ query.setFrom(from); TestParser.helpTest(sql, "SELECT * FROM /*+ optional */ t1, t2", query); //$NON-NLS-1$ }
@Test public void testOptionalFromClause2() { String sql = "SELECT * FROM t1, /*+ optional */ t2"; //$NON-NLS-1$ Query query = new Query(); Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); query.setSelect(select); From from = new From(); from.addGroup(new GroupSymbol("t1")); //$NON-NLS-1$ UnaryFromClause ufc = new UnaryFromClause(); ufc.setGroup(new GroupSymbol("t2")); //$NON-NLS-1$ ufc.setOptional(true); from.addClause(ufc); query.setFrom(from); TestParser.helpTest(sql, "SELECT * FROM t1, /*+ optional */ t2", query); //$NON-NLS-1$ }
@Test public void testOptionalFromClause4() { String sql = "SELECT * FROM t1, /*+ optional */ t2 as a"; //$NON-NLS-1$ Query query = new Query(); Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); query.setSelect(select); From from = new From(); from.addGroup(new GroupSymbol("t1")); //$NON-NLS-1$ UnaryFromClause ufc = new UnaryFromClause(); ufc.setGroup(new GroupSymbol("a", "t2")); //$NON-NLS-1$ //$NON-NLS-2$ ufc.setOptional(true); from.addClause(ufc); query.setFrom(from); TestParser.helpTest(sql, "SELECT * FROM t1, /*+ optional */ t2 AS a", query); //$NON-NLS-1$ }
@Test public void testStoredQuery2(){ StoredProcedure storedQuery = new StoredProcedure(); storedQuery.setProcedureName("proc1"); //$NON-NLS-1$ SPParameter parameter = new SPParameter(1, new Constant("param1")); //$NON-NLS-1$ storedQuery.setParameter(parameter); From from = new From(); SubqueryFromClause sfc = new SubqueryFromClause("x", storedQuery); //$NON-NLS-1$ from.addClause(sfc); Select select = new Select(); select.addSymbol(new ElementSymbol("X.A")); //$NON-NLS-1$ Query query = new Query(); query.setSelect(select); query.setFrom(from); helpTest("SELECT X.A FROM (exec proc1('param1')) AS X", "SELECT X.A FROM (EXEC proc1('param1')) AS X", query); //$NON-NLS-1$ //$NON-NLS-2$ }