public static Query createMatViewQuery(Object matMetadataId, String matTableName, List<? extends Expression> select, boolean isGlobal) { Query query = new Query(); query.setSelect(new Select(select)); GroupSymbol gs = new GroupSymbol(matTableName); gs.setGlobalTable(isGlobal); gs.setMetadataID(matMetadataId); query.setFrom(new From(Arrays.asList(new UnaryFromClause(gs)))); return query; }
@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 testLimitWithOffset() { 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 Constant(new Integer(50)), new Constant(new Integer(100)))); helpTest(query, "SELECT * FROM a LIMIT 50, 100"); //$NON-NLS-1$ }
@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 testNameSpacedFunctionName() { String sql = "select a.x()"; //$NON-NLS-1$ Query query = new Query(); Select select = new Select(); Function func1 = new Function("a.x", new Expression[] { }); //$NON-NLS-1$ select.addSymbol(func1); query.setSelect(select); helpTest(sql, "SELECT a.x()", query); //$NON-NLS-1$ }
@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 testNestedTable() throws Exception { String sql = "SELECT * from TABLE(exec foo()) as x"; //$NON-NLS-1$ Query query = new Query(); query.setSelect(new Select(Arrays.asList(new MultipleElementSymbol()))); StoredProcedure sp = new StoredProcedure(); sp.setProcedureName("foo"); SubqueryFromClause sfc = new SubqueryFromClause("x", sp); sfc.setLateral(true); query.setFrom(new From(Arrays.asList(sfc))); helpTest(sql, "SELECT * FROM LATERAL(EXEC foo()) AS x", query); }
private Query createTestQuery(String group) { GroupSymbol g = new GroupSymbol(group); From from = new From(); from.addGroup(g); Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); Query query1 = new Query(); query1.setSelect(select); query1.setFrom(from); return query1; }
public Query createBaseQuery() throws Exception { Query query = new Query(); Select select = new Select(); select.addSymbol(getElementSymbol(1,1)); query.setSelect(select); From from = new From(); from.addGroup(getPhysicalGroup(1)); query.setFrom(from); return query; }
@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 testQuery1() { Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); From from = new From(); from.addGroup(new GroupSymbol("m.g")); //$NON-NLS-1$ Query query = new Query(); query.setSelect(select); query.setFrom(from); helpTest(query, "SELECT * FROM m.g"); //$NON-NLS-1$ }
@Test public void testCommandStatement1() { Query q1 = new Query(); Select select = new Select(); select.addSymbol(new ElementSymbol("x")); //$NON-NLS-1$ q1.setSelect(select); From from = new From(); from.addGroup(new GroupSymbol("g")); //$NON-NLS-1$ q1.setFrom(from); CommandStatement cmdStmt = new CommandStatement(q1); helpTest(cmdStmt, "SELECT x FROM g;"); //$NON-NLS-1$ }
@Test public void testExistsCriteria1() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ From f1 = new From(); f1.addGroup(new GroupSymbol("m.g1")); //$NON-NLS-1$ Query q1 = new Query(); q1.setSelect(s1); q1.setFrom(f1); ExistsCriteria ec = new ExistsCriteria(q1); helpTest(ec, "EXISTS (SELECT e1 FROM m.g1)"); //$NON-NLS-1$ }
@Test public void testScalarSubquery() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ From f1 = new From(); f1.addGroup(new GroupSymbol("m.g1")); //$NON-NLS-1$ Query q1 = new Query(); q1.setSelect(s1); q1.setFrom(f1); ScalarSubquery obj = new ScalarSubquery(q1); helpTest(obj, "(SELECT e1 FROM m.g1)"); //$NON-NLS-1$ }
@Test public void testOptionalSubqueryFromClause1() { Select s1 = new Select(); s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ From f1 = new From(); f1.addGroup(new GroupSymbol("m.g1")); //$NON-NLS-1$ Query q1 = new Query(); q1.setSelect(s1); q1.setFrom(f1); SubqueryFromClause sfc = new SubqueryFromClause("temp", q1); //$NON-NLS-1$ sfc.setOptional(true); helpTest(sfc, "/*+ optional */ (SELECT e1 FROM m.g1) AS temp"); //$NON-NLS-1$ }
@Test public void testCommandStatement1a() { Query q1 = new Query(); Select select = new Select(); select.addSymbol(new ElementSymbol("x")); //$NON-NLS-1$ q1.setSelect(select); From from = new From(); from.addGroup(new GroupSymbol("g")); //$NON-NLS-1$ q1.setFrom(from); CommandStatement cmdStmt = new CommandStatement(q1); cmdStmt.setReturnable(false); helpTest(cmdStmt, "SELECT x FROM g WITHOUT RETURN;"); //$NON-NLS-1$ }
@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$ }