public void testConcat() throws JSQLParserException {
String statement = "SELECT a || b || c + 4 FROM t";
Statement parsed = parserManager.parse(new StringReader(statement));
StatementDeParser deParser = new StatementDeParser(new StringBuffer());
parsed.accept(deParser);
assertEquals(statement, parsed.toString());
assertEquals(statement, deParser.getBuffer().toString());
}
@Test
public void batchTestFullURI() throws DDFException {
String[] selectItems = {
" * ", " ddf://adatao/a.year ", " round(ddf://adatao/a.year) ", " year "
};
String[] joinTypes = {
" , ",
" join ",
" LEFT OUTER JOIN ",
" LEFT JOIN ",
" RIGHT OUTER JOIN ",
" FULL OUTER JOIN ",
" CROSS JOIN "
};
String[] joinConds = {" ", " ON ddf://adatao/a.id = ddf://adatao/b.id "};
String[] wehereCaluses = {
" WHERE year > id ", " WHERE ddf://adatao/a.id > 1 AND ddf://adatao/b.id < 3 "
};
String[] sortOptions = {
" ",
" Cluster by ddf://adatao/a.year ",
" Distribute By ddf://adatao/a.year Sort by ddf://adatao/a.id "
};
for (String selectItem : selectItems) {
for (String joinType : joinTypes) {
for (String joinCond : joinConds) {
for (String whereClause : wehereCaluses) {
for (String sortOption : sortOptions) {
String sqlcmd =
"Select"
+ selectItem
+ "from ddf://adatao/a"
+ joinType
+ "ddf://adatao/b"
+ joinCond
+ whereClause
+ sortOption;
// System.out.println(sqlcmd);
try {
Statement statement = testFullURISingle(sqlcmd);
System.out.println(statement.toString());
} catch (Exception e) {
e.printStackTrace();
assert false;
}
}
}
}
}
}
}
public void testMysqlQuote() throws JSQLParserException {
String statement =
"SELECT `a.OWNERLASTNAME`, `OWNERFIRSTNAME` "
+ "FROM `ANTIQUEOWNERS` AS a, ANTIQUES AS b "
+ "WHERE b.BUYERID = a.OWNERID AND b.ITEM = 'Chair'";
Statement parsed = parserManager.parse(new StringReader(statement));
StatementDeParser deParser = new StatementDeParser(new StringBuffer());
parsed.accept(deParser);
assertEquals(statement, parsed.toString());
assertEquals(statement, deParser.getBuffer().toString());
}
@Test
public void testAlias() {
TableNameReplacer tableNameReplacer = new TableNameReplacer(manager);
String sqlcmd = "select T0.id from (select tmp.id from ddf://adatao/a" + " " + "tmp) T0";
try {
Statement statement = parser.parse(new StringReader(sqlcmd));
// statement = tableNameReplacer.run(statement);
System.out.println(statement.toString());
} catch (JSQLParserException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
@Override
public ExecuteSQLOnNodeResponse executeSQLOnNode(ExecuteSQLOnNodeRequest executeSQLOnNodeRequest)
throws RemoteException {
Long taskId = executeSQLOnNodeRequest.getTaskId() + delta;
try {
Statement stmt = CCJSqlParserUtil.parse(executeSQLOnNodeRequest.getSql());
SQLStatementVisitor visitor = new SQLStatementVisitor(master, this, taskId);
stmt.accept(visitor);
logger.trace("Sending result of query: " + visitor.getResult().getResult());
return visitor.getResult();
} catch (JSQLParserException e) {
logger.error("Sql parsing error: {} - {}", e.getMessage(), e.getStackTrace());
return new ExecuteSQLOnNodeResponse("", ErrorEnum.SQL_PARSING_ERROR);
}
}
/**
* @brief Test udfs.
* @throws DDFException
*/
@Test
public void testUDF() throws DDFException {
String[] singleParamUDFs = {
"round", "floor", "ceil", "ceiling", "rand", "exp", "ln", "log10", "log2"
};
String[] doubleParamUDFs = {"log", "pow"};
TableNameReplacer tableNameReplacer = new TableNameReplacer(this.manager);
String sqlcmd = "select %s(ddf://adatao/a.year) from ddf://adatao/a";
String doubleSqlCmd = "select %s(ddf://adatao/a.year, ddf://adatao/a.rev) from ddf://adatao/a";
for (String udfname : singleParamUDFs) {
String newSqlCmd = String.format(sqlcmd, udfname);
try {
Statement statement = parser.parse(new StringReader(newSqlCmd));
// statement = tableNameReplacer.run(statement);
assert (statement
.toString()
.toLowerCase()
.equals(String.format("select %s(tablename1.year) from tablename1", udfname)));
} catch (JSQLParserException e) {
e.printStackTrace();
assert (false);
} catch (Exception e) {
e.printStackTrace();
assert false;
}
}
for (String udfname : doubleParamUDFs) {
String newSqlCmd = String.format(doubleSqlCmd, udfname);
try {
Statement statement = parser.parse(new StringReader(newSqlCmd));
// statement = tableNameReplacer.run(statement);
assert (statement
.toString()
.toLowerCase()
.equals(
String.format(
"select %s(tablename1.year, tablename1.rev) from tablename1", udfname)));
} catch (Exception e) {
e.printStackTrace();
assert false;
}
}
}
/**
* @brief Test sql with namespace specified.
* @throws DDFException
*/
@Test
public void testNamespace() throws DDFException {
TableNameReplacer tableNameReplacer = new TableNameReplacer(manager, "adatao");
String sqlcmd = "select a.b from a";
Statement statement = null;
try {
statement = parser.parse(new StringReader(sqlcmd));
} catch (JSQLParserException e) {
e.printStackTrace();
assert (false);
}
try {
// tableNameReplacer.run(statement);
} catch (Exception e) {
e.printStackTrace();
assert (false);
}
assert (statement.toString().equals("SELECT tablename1.b FROM tablename1"));
}
public void testDeparser() throws JSQLParserException {
String statement =
"SELECT a.OWNERLASTNAME, a.OWNERFIRSTNAME "
+ "FROM ANTIQUEOWNERS AS a, ANTIQUES AS b "
+ "WHERE b.BUYERID = a.OWNERID AND b.ITEM = 'Chair'";
Statement parsed = parserManager.parse(new StringReader(statement));
StatementDeParser deParser = new StatementDeParser(new StringBuffer());
parsed.accept(deParser);
assertEquals(statement, deParser.getBuffer().toString());
statement = "SELECT count(DISTINCT f + 4) FROM a";
parsed = parserManager.parse(new StringReader(statement));
deParser = new StatementDeParser(new StringBuffer());
parsed.accept(deParser);
assertEquals(statement, parsed.toString());
assertEquals(statement, deParser.getBuffer().toString());
statement = "SELECT count(DISTINCT f, g, h) FROM a";
parsed = parserManager.parse(new StringReader(statement));
deParser = new StatementDeParser(new StringBuffer());
parsed.accept(deParser);
assertEquals(statement, parsed.toString());
assertEquals(statement, deParser.getBuffer().toString());
}
/**
* @brief Test sql with list.
* @throws DDFException
*/
@Test
public void testList() throws DDFException {
String[] uris = {"ddf://adatao/a", "ddf://adatao/b"};
TableNameReplacer tableNameReplacer = new TableNameReplacer(manager, Arrays.asList(uris));
String sqlcmd = "select {1}.a,{2}.b from {1}";
Statement statement = null;
try {
statement = parser.parse(new StringReader(sqlcmd));
} catch (JSQLParserException e) {
e.printStackTrace();
assert (false);
}
try {
// tableNameReplacer.run(statement);
} catch (Exception e) {
e.printStackTrace();
assert (false);
}
assert (statement.toString().equals("SELECT tablename1.a, tablename2.b FROM tablename1"));
}
/**
* @brief Test full uri replacement.
* @throws DDFException
*/
@Test
public void testFullURI() throws DDFException {
TableNameReplacer tableNameReplacer = new TableNameReplacer(manager);
String sqlcmd = "select SUM(ddf://adatao/a.b) from ddf://adatao/a group by ddf://adatao/a.a";
Statement statement = null;
try {
statement = parser.parse(new StringReader(sqlcmd));
} catch (JSQLParserException e) {
e.printStackTrace();
assert (false);
}
try {
// statement = tableNameReplacer.run(statement);
} catch (Exception e) {
e.printStackTrace();
assert (false);
}
assert (statement
.toString()
.equals("SELECT SUM(tablename1.b) FROM tablename1 GROUP BY tablename1.a"));
}