package tool.model.grammar;
import static org.junit.Assert.fail;
import java.io.File;
import java.io.IOException;
import org.antlr.runtime.CharStream;
import org.antlr.runtime.CommonTokenStream;
import org.antlr.runtime.Token;
import org.antlr.runtime.TokenStream;
import org.antlr.runtime.tree.CommonTree;
import org.antlr.runtime.tree.CommonTreeAdaptor;
import org.junit.Before;
import org.junit.Test;
import tool.model.grammar.ToolSQLParser.bind_variable_return;
import tool.model.grammar.ToolSQLParser.c_alias_return;
import tool.model.grammar.ToolSQLParser.column_list_return;
import tool.model.grammar.ToolSQLParser.column_spec_return;
import tool.model.grammar.ToolSQLParser.displayed_column_return;
import tool.model.grammar.ToolSQLParser.qualifiedName_return;
import tool.model.grammar.ToolSQLParser.simple_expression_return;
import tool.model.grammar.ToolSQLParser.sql_cursor_operation_return;
import tool.model.grammar.ToolSQLParser.sql_delete_return;
import tool.model.grammar.ToolSQLParser.sql_execute_immediate_return;
import tool.model.grammar.ToolSQLParser.sql_execute_procedure_return;
import tool.model.grammar.ToolSQLParser.sql_fetch_cursor_return;
import tool.model.grammar.ToolSQLParser.sql_insert_return;
import tool.model.grammar.ToolSQLParser.sql_select_return;
import tool.model.grammar.ToolSQLParser.sql_statement_return;
import tool.model.grammar.ToolSQLParser.sql_update_return;
import tool.model.grammar.ToolSQLParser.t_alias_return;
public class testToolSQLParser {
private ErrorReporter parseErrors;
private TokenStream getStream(File file) throws IOException{
CharStream stream =
new NoCaseFileStream(file.getAbsolutePath());
ToolSQLLexer lexer = new ToolSQLLexer(stream);
TokenStream tokenStream = new CommonTokenStream(lexer);
return tokenStream;
}
private TokenStream getStream(String source) throws IOException{
CharStream stream =
new NoCaseStringStream(source);
ToolSQLLexer lexer = new ToolSQLLexer(stream);
TokenStream tokenStream = new CommonTokenStream(lexer);
return tokenStream;
}
private void printTree(CommonTree tree, int indent){
if (tree != null){
StringBuffer sb = new StringBuffer(indent);
for (int i = 0; i < indent; i++)
sb = sb.append(" ");
for (int i = 0; i < tree.getChildCount(); i++){
System.out.println(sb.toString() + tree.getChild(i).toString());
printTree((CommonTree)tree.getChild(i), indent+1);
}
}
}
private ToolSQLParser getParser(String source) throws Exception{
CommonTokenStream tokens = (CommonTokenStream) getStream(source);
ToolSQLParser parser = new ToolSQLParser(tokens);
parser.setTreeAdaptor(new CommonTreeAdaptor(){
public Object create(Token payload){
return new CommonTree(payload);
}
});
return parser;
}
private void printResult(ToolSQLParser parser, Object tree) throws Exception{
if (parser.getNumberOfSyntaxErrors() > 0){
fail();
}
System.out.println(((CommonTree) tree).toStringTree());
}
private void sql_statement(String source) throws Exception{
ToolSQLParser parser = getParser(source);
sql_statement_return result = parser.sql_statement();
printResult(parser, result.getTree());
}
private void select(String source) throws Exception{
ToolSQLParser parser = getParser(source);
sql_select_return result = parser.sql_select();
printResult(parser, result.getTree());
}
private void insert(String source) throws Exception{
ToolSQLParser parser = getParser(source);
sql_insert_return result = parser.sql_insert();
printResult(parser, result.getTree());
}
private void update(String source) throws Exception{
ToolSQLParser parser = getParser(source);
sql_update_return result = parser.sql_update();
printResult(parser, result.getTree());
}
private void delete(String source) throws Exception{
ToolSQLParser parser = getParser(source);
sql_delete_return result = parser.sql_delete();
printResult(parser, result.getTree());
}
private void immediate(String source) throws Exception{
ToolSQLParser parser = getParser(source);
sql_execute_immediate_return result = parser.sql_execute_immediate();
printResult(parser, result.getTree());
}
private void procedure(String source) throws Exception{
ToolSQLParser parser = getParser(source);
sql_execute_procedure_return result = parser.sql_execute_procedure();
printResult(parser, result.getTree());
}
private void fetchCursor(String source) throws Exception{
ToolSQLParser parser = getParser(source);
sql_fetch_cursor_return result = parser.sql_fetch_cursor();
printResult(parser, result.getTree());
}
private void cursorOperation(String source) throws Exception{
ToolSQLParser parser = getParser(source);
sql_cursor_operation_return result = parser.sql_cursor_operation();
printResult(parser, result.getTree());
}
@Before
public void setUp() throws Exception {
}
@Test
public void sqlOpenCursor() throws Exception{
cursorOperation(
"open cursor blobCurs(name) on session self.MGRSession");
}
@Test
public void sqlCloseCursor() throws Exception{
cursorOperation(
"close cursor blobCurs");
}
//@Test
public void testSql_delete() {
fail("Not yet implemented");
}
@Test
public void testSql_execute_immediate() throws Exception{
immediate(
"execute immediate \n" +
" 'create table ArtistTab(Name varchar(30) not null, Country varchar(30) not null, Comments varchar(200) not null)'\n" +
" on session self.MGRSession\n");
}
//@Test
public void testSql_execute_procedure() throws Exception{
procedure("");
}
@Test
public void testSql_fetch_cursor() throws Exception{
fetchCursor("fetch cursor blobCurs into :binData");
}
@Test
public void testSql_insert() throws Exception{
insert("INSERT INTO inter_bank_transfer_hist (inter_bank_transfer_id,\n" +
" mf_plan_from_id,\n" +
" mf_plan_to_id,\n" +
" actual_bank_rec_id,\n" +
" expected_bank_rec_id,\n" +
" admn_id,\n" +
" admn_type,\n" +
" tran_amt,\n" +
" transferred_date,\n" +
" created_by,\n" +
" updated_by,\n" +
" lock_count)\n" +
" \n" +
" VALUES (:theInterBankTransferHistory._InterBankTransferHistoryId.NextValue.DoubleValue,\n" +
" :theInterBankTransferHistory.dMFPlanFromId,\n" +
" :theInterBankTransferHistory.dMFPlanToId, \n" +
" :theInterBankTransferHistory.dActualBankRecId, \n" +
" :theInterBankTransferHistory.dExpectedBankRecId, \n" +
" :theInterBankTransferHistory.dAdminId,\n" +
" :toOracle(theInterBankTransferHistory.sAdminType),\n" +
" :dedTransferAmt,\n" +
" :dnCurrentDate,\n" +
" :dUserId,\n" +
" :dUserId,\n" +
" 0)\n" +
" \n" +
"ON SESSION SessionToUse");
}
@Test
public void testSql_update_1() throws Exception{
sql_statement("sql UPDATE actual_bank_rec\n" +
" SET inter_bank_transfer_ind = 0\n" +
" WHERE actual_bank_rec_id = :theInterBankTransferHistory.dActualBankRecId\n" +
" ON SESSION SessionToUse");
}
@Test
public void testSql_update_2() throws Exception{
sql_statement("sql UPDATE actual_bank_reconciliation\n" +
" SET transferred_amt = transferred_amt - :dedTransferAmt\n" +
" WHERE actual_bank_rec_id = :theInterBankTransferHistory.dActualBankRecId\n" +
" AND expected_bank_rec_id = :theInterBankTransferHistory.dExpectedBankRecId\n" +
" ON SESSION SessionToUse");
}
@Test
public void testSql_update_3() throws Exception{
sql_statement("sql UPDATE admn_expected_bank_rec\n" +
" SET transferred_amt = transferred_amt - :dedTransferAmt\n" +
" WHERE admn_id = :theInterBankTransferHistory.dAdminId\n" +
" AND admn_type = :toOracle(theInterBankTransferHistory.sAdminType)\n" +
" AND expected_bank_rec_id = :theInterBankTransferHistory.dExpectedBankRecId\n" +
" ON SESSION SessionToUse");
}
@Test
public void testSql_update_4() throws Exception{
sql_statement("sql UPDATE actual_bank_reconciliation\n" +
" SET transferred_amt = transferred_amt + :dedTransferAmt\n" +
" WHERE actual_bank_rec_id = :theInterBankTransferHistory.dActualBankRecId\n" +
" AND expected_bank_rec_id = :theInterBankTransferHistory.dExpectedBankRecId\n" +
" ON SESSION SessionToUse");
}
@Test
public void testSql_update_5() throws Exception{
sql_statement("sql UPDATE admn_expected_bank_rec\n" +
" SET transferred_amt = transferred_amt + :dedTransferAmt\n" +
" WHERE admn_id = :theInterBankTransferHistory.dAdminId\n" +
" AND admn_type = :toOracle(theInterBankTransferHistory.sAdminType)\n" +
" AND expected_bank_rec_id = :theInterBankTransferHistory.dExpectedBankRecId\n" +
" ON SESSION SessionToUse");
}
@Test
public void testSql_update_6() throws Exception{
sql_statement("sql UPDATE actual_bank_rec\n" +
" SET inter_bank_transfer_ind = 1\n" +
" WHERE actual_bank_rec_id = :theInterBankTransferHistory.dActualBankRecId\n" +
" AND reconciled_amt = :lTranserredAmt\n" +
" ON SESSION SessionToUse");
}
@Test
public void testSql_update_7() throws Exception{
sql_statement("sql UPDATE count_seq_num\n" +
" SET count_seq_num = :theCountSeqNum.dCountSeqNum,\n" +
" count_seq_date = :theCountSeqNum.dnCountSeqDate,\n" +
" lock_count = :theCountSeqNum._iLockCount + 1,\n" +
" updated_by = :dUserId\n" +
" WHERE count_seq_name = :toOracle(theCountSeqNum.sCountSeqName)\n" +
" AND lock_count = :theCountSeqNum._iLockCount \n" +
" ON SESSION SessionToUse");
}
@Test
public void testSql_select_simple() throws Exception {
select("select * from bob");
}
@Test
public void testSql_select_1() throws Exception {
sql_statement("sql SELECT transferred_amt\n" +
" INTO :lTranserredAmt\n" +
" FROM actual_bank_reconciliation \n" +
" WHERE actual_bank_rec_id = :theInterBankTransferHistory.dActualBankRecId\n" +
" AND expected_bank_rec_id = :theInterBankTransferHistory.dExpectedBankRecId\n" +
" ON SESSION SessionToUse");
}
@Test
public void testSql_select_2() throws Exception {
sql_statement("sql \n" +
" SELECT sum(atd.tran_amt)\n" +
" INTO :lAmount\n" +
" FROM acc_tran_det atd,\n" +
" acc_tran_hdr ath,\n" +
" inv_acc_tran iat \n" +
" WHERE atd.tran_id = ath.tran_id \n" +
" AND ath.related_tran_id = iat.inv_acc_tran_id\n" +
" AND iat.INV_ACC_TRAN_STATUS = :lTranStatus\n" +
" AND iat.tran_type = :lTranType\n" +
" AND ath.related_tran_type = :lTranType\n" +
" AND ath.reversal_ind = 0\n" +
" AND ath.rule_num = :lRule\n" +
" AND atd.ACC_INFRA_TRAN_TYPE = 1\n" +
" AND iat.inv_acc_num = :theAccBalSummary.dAccId\n" +
"ON Session SessionToUse");
}
@Test
public void testSql_select_3() throws Exception {
sql_statement("sql SELECT count_seq_name,\n" +
" count_seq_num,\n" +
" F_DATETIME(count_seq_date) AS count_seq_date,\n" +
" lock_count\n" +
" FROM count_seq_num\n" +
" WHERE count_seq_name = :toOracle(theCountSeqNum.sCountSeqName)\n" +
"ON SESSION SessionToUse\n" +
"\n" +
"");
}
@Test
public void testSql_select_4() throws Exception {
sql_statement("sql select * from PaintingTab\n" +
" where Painter = :painter.name\n" +
" on session self.MGRSession\n");
}
@Test
public void test_QualifiedName() throws Exception{
ToolSQLParser parser = getParser("cats.lives.cows");
qualifiedName_return result = parser.qualifiedName();
printResult(parser, result.getTree());
}
@Test
public void testBind_variable() throws Exception{
ToolSQLParser parser = getParser(":theInterBankTransferHistory._InterBankTransferHistoryId.NextValue.DoubleValue");
bind_variable_return result = parser.bind_variable();
printResult(parser, result.getTree());
}
@Test
public void test_column_spec() throws Exception{
ToolSQLParser parser = getParser("jim.bob");
column_spec_return result = parser.column_spec();
printResult(parser, result.getTree());
}
@Test
public void test_simple_expression_boolean() throws Exception{
ToolSQLParser parser = getParser("true");
simple_expression_return result = parser.simple_expression();
printResult(parser, result.getTree());
}
@Test
public void test_simple_expression_string() throws Exception{
ToolSQLParser parser = getParser("'cats'");
simple_expression_return result = parser.simple_expression();
printResult(parser, result.getTree());
}
@Test
public void test_simple_expression_number() throws Exception{
ToolSQLParser parser = getParser("3");
simple_expression_return result = parser.simple_expression();
printResult(parser, result.getTree());
}
@Test
public void test_simple_expression_column() throws Exception{
ToolSQLParser parser = getParser("jim.mary");
simple_expression_return result = parser.simple_expression();
printResult(parser, result.getTree());
}
@Test
public void test_column_alias() throws Exception{
ToolSQLParser parser = getParser("bob");
c_alias_return result = parser.c_alias();
printResult(parser, result.getTree());
}
@Test
public void test_table_alias() throws Exception{
ToolSQLParser parser = getParser("bob");
t_alias_return result = parser.t_alias();
printResult(parser, result.getTree());
}
@Test
public void test_displayed_column() throws Exception{
ToolSQLParser parser = getParser("sum(atd.tran_amt)");
displayed_column_return result = parser.displayed_column();
printResult(parser, result.getTree());
}
@Test
public void test_column_list() throws Exception{
ToolSQLParser parser = getParser("sum(atd.tran_amt), cats");
column_list_return result = parser.column_list();
printResult(parser, result.getTree());
}
@Test
public void testUpdateWithFunction() throws Exception {
sql_statement("sql UPDATE allocated_EFT_prod\n" +
" SET \n" +
" REG_CLIENT_ID = :eft.RegClientID,\n" +
" REG_PROD_ID = :eft.RegProdID,\n" +
" DATA_MASSAGING_TYPE = :eft.DataMessagingType,\n" +
" BNK_SVC_CODE = :eft.BankServiceCode,\n" +
" DESCRPTN = :eft.Description,\n" +
" EFF_FROM_DATE = TO_TIMESTAMP(:eft.EffFromDate, :lTimestampFormat),\n" +
" EFF_TO_DATE = TO_TIMESTAMP(:eft.EffToDate, :lTimestampFormat),\n" +
" UPDATED_BY = :lUserId,\n" +
" LOCK_COUNT = :eft.LockCount + 1\n" +
" \n" +
" WHERE ALLOCATED_EFT_PROD_ID = :eft.AllocatedEFTProdID\n" +
" AND lock_count = :EFT.LockCount\n" +
" \n" +
" ON SESSION SessionToUse");
}
}