@Test public void testSubquerySetCriteria0() { //test wrap up command with subquerySetCriteria Query outer = exampleIn(false); helpTest("SELECT a FROM db.g WHERE b IN (SELECT a FROM db.g WHERE a2 = 5)", //$NON-NLS-1$ "SELECT a FROM db.g WHERE b IN (SELECT a FROM db.g WHERE a2 = 5)", //$NON-NLS-1$ outer); }
@Test public void testExistsPredicateCriteria(){ Query q2 = exampleExists(false); helpTest("SELECT e1 FROM m.g2 WHERE Exists (SELECT e1 FROM m.g1)", //$NON-NLS-1$ "SELECT e1 FROM m.g2 WHERE EXISTS (SELECT e1 FROM m.g1)", //$NON-NLS-1$ q2); } static Query exampleExists(boolean semiJoin) {
@Test public void testDropTable() { Drop drop = new Drop(); drop.setTable(new GroupSymbol("tempTable")); //$NON-NLS-1$ helpTest("DROP table tempTable", "DROP TABLE tempTable", drop); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testPositionalReference() throws Exception { String sql = "select $1"; Query query = new Query(); query.setSelect(new Select(Arrays.asList(new Reference(0)))); helpTest(sql, "SELECT ?", query); }
@Test public void testNonReserved() throws Exception { String sql = "select count"; Query query = new Query(); query.setSelect(new Select(Arrays.asList(new ElementSymbol("count")))); helpTest(sql, "SELECT count", query); }
@Test public void testLimit() { Query query = new Query(); Select select = new Select(Arrays.asList(new MultipleElementSymbol())); From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ query.setSelect(select); query.setFrom(from); query.setLimit(new Limit(null, new Constant(new Integer(100)))); helpTest("Select * from a limit 100", "SELECT * FROM a LIMIT 100", query); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testLimitWithReferences1() { Query query = new Query(); Select select = new Select(Arrays.asList(new MultipleElementSymbol())); From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ query.setSelect(select); query.setFrom(from); query.setLimit(new Limit(new Reference(0), new Constant(new Integer(100)))); helpTest("Select * from a limit ?,100", "SELECT * FROM a LIMIT ?, 100", query); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testObjectTable1() throws Exception { Query query = new Query(); query.setSelect(new Select(Arrays.asList(new MultipleElementSymbol()))); ObjectTable objectTable = new ObjectTable(); objectTable.setRowScript("y"); objectTable.setPassing(Arrays.asList(new DerivedColumn("y", new ElementSymbol("e1")))); objectTable.setColumns(Arrays.asList(new ObjectTable.ObjectColumn("z", "time", "now()", null))); objectTable.setName("x"); query.setFrom(new From(Arrays.asList(objectTable))); helpTest("select * from objecttable('y' passing e1 as y columns z time 'now()') as x", "SELECT * FROM OBJECTTABLE('y' PASSING e1 AS y COLUMNS z time 'now()') AS x", query); }
@Test public void testAlterView() throws Exception { AlterView alterView = new AlterView(); alterView.setTarget(new GroupSymbol("x")); alterView.setDefinition((QueryCommand) QueryParser.getQueryParser().parseCommand("/*+ cache */ SELECT 1")); helpTest("alter view x as /*+ cache */ select 1", "ALTER VIEW x AS\n/*+ cache */ SELECT 1", alterView); }
@Test public void testAlterProc() throws Exception { AlterView alterView = new AlterView(); alterView.setTarget(new GroupSymbol("x")); alterView.setDefinition((QueryCommand) QueryParser.getQueryParser().parseCommand("/*+ cache */ SELECT 1")); helpTest("alter view x as /*+ cache */ select 1", "ALTER VIEW x AS\n/*+ cache */ SELECT 1", alterView); }
@Test public void testAlterTrigger() throws Exception { AlterTrigger alterTrigger = new AlterTrigger(); alterTrigger.setTarget(new GroupSymbol("x")); alterTrigger.setEvent(TriggerEvent.UPDATE); alterTrigger.setDefinition((TriggerAction) QueryParser.getQueryParser().parseProcedure("for each row begin end", true)); helpTest("alter trigger on x instead of update as for each row begin end", "ALTER TRIGGER ON x INSTEAD OF UPDATE AS\nFOR EACH ROW\nBEGIN ATOMIC\nEND", alterTrigger); }
@Test public void testCacheHintCallableStatement() { String sql = "/*+ cache */ { ? = call proc() }"; //$NON-NLS-1$ StoredProcedure sp = new StoredProcedure(); SPParameter param = new SPParameter(1, null); param.setParameterType(SPParameter.RETURN_VALUE); sp.setParameter(param); sp.setProcedureName("proc"); sp.setCallableStatement(true); CacheHint hint = new CacheHint(); sp.setCacheHint(hint); TestParser.helpTest(sql, "/*+ cache */ ? = EXEC proc()", sp); //$NON-NLS-1$ }
@Test public void testCreateTrigger() throws Exception { AlterTrigger alterTrigger = new AlterTrigger(); alterTrigger.setCreate(true); alterTrigger.setTarget(new GroupSymbol("x")); alterTrigger.setEvent(TriggerEvent.UPDATE); alterTrigger.setDefinition((TriggerAction) QueryParser.getQueryParser().parseProcedure("for each row begin end", true)); helpTest("create trigger on x instead of update as for each row begin end", "CREATE TRIGGER ON x INSTEAD OF UPDATE AS\nFOR EACH ROW\nBEGIN ATOMIC\nEND", alterTrigger); }
@Test public void testFetchFirstRow() { Query query = new Query(); Select select = new Select(Arrays.asList(new MultipleElementSymbol())); From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ query.setSelect(select); query.setFrom(from); query.setLimit(new Limit(null, new Constant(1))); helpTest("Select * from a fetch first row only", "SELECT * FROM a LIMIT 1", query); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testFetchFirst() { Query query = new Query(); Select select = new Select(Arrays.asList(new MultipleElementSymbol())); From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("a")))); //$NON-NLS-1$ query.setSelect(select); query.setFrom(from); query.setLimit(new Limit(null, new Constant(2))); helpTest("Select * from a fetch first 2 rows only", "SELECT * FROM a LIMIT 2", query); //$NON-NLS-1$ //$NON-NLS-2$ }
@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 testCreateTriggerPhysical() throws Exception { AlterTrigger alterTrigger = new AlterTrigger(); alterTrigger.setCreate(true); alterTrigger.setTarget(new GroupSymbol("x")); alterTrigger.setEvent(TriggerEvent.INSERT); alterTrigger.setAfter(true); alterTrigger.setName("z"); alterTrigger.setDefinition((TriggerAction) QueryParser.getQueryParser().parseProcedure("for each row begin end", true)); helpTest("create trigger z on x after insert as for each row begin end", "CREATE TRIGGER z ON x AFTER INSERT AS\nFOR EACH ROW\nBEGIN ATOMIC\nEND", alterTrigger); }
@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 testLOBTypes() throws Exception { Function convert = new Function("convert", new Expression[] {new Constant(null), new Constant("blob")}); //$NON-NLS-1$ //$NON-NLS-2$ Function convert1 = new Function("convert", new Expression[] {new Constant(null), new Constant("clob")}); //$NON-NLS-1$ //$NON-NLS-2$ Function convert2 = new Function("convert", new Expression[] {new Constant(null), new Constant("xml")}); //$NON-NLS-1$ //$NON-NLS-2$ Select select = new Select(Arrays.asList(convert, convert1, convert2)); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ Query query = new Query(); query.setSelect(select); helpTest("select convert(null, blob), convert(null, clob), convert(null, xml)", "SELECT convert(null, blob), convert(null, clob), convert(null, xml)", query); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testCase3281QuotedNamedVariableFails2() { StoredProcedure storedQuery = new StoredProcedure(); storedQuery.setProcedureName("proc1"); //$NON-NLS-1$ SPParameter param1 = new SPParameter(1, new CompareCriteria(new Constant("a"), CompareCriteria.EQ, new Constant("b"))); //$NON-NLS-1$ param1.setParameterType(ParameterInfo.IN); storedQuery.setParameter(param1); helpTest("Exec proc1('a' = 'b')", "EXEC proc1(('a' = 'b'))", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$ }