package test.org.seasar.dao.parser;

import java.util.ArrayList;
import java.util.List;

import junit.framework.TestCase;

import org.seasar.dao.CommandContext;
import org.seasar.dao.EndCommentNotFoundRuntimeException;
import org.seasar.dao.Node;
import org.seasar.dao.SqlParser;
import org.seasar.dao.TokenNotClosedRuntimeException;
import org.seasar.dao.context.CommandContextImpl;
import org.seasar.dao.node.BindVariableNode;
import org.seasar.dao.node.IfNode;
import org.seasar.dao.node.SqlNode;
import org.seasar.dao.parser.SqlParserImpl;

/**
 * @author higa
 *
 */
public class SqlParserImplTest extends TestCase {

	/**
	 * Constructor for InvocationImplTest.
	 * @param arg0
	 */
	public SqlParserImplTest(String arg0) {
		super(arg0);
	}

	public static void main(String[] args) {
		junit.textui.TestRunner.run(SqlParserImplTest.class);
	}

	public void testParse() throws Exception {
		String sql = "SELECT * FROM emp";
		SqlParser parser = new SqlParserImpl(sql);
		CommandContext ctx = new CommandContextImpl();
		Node node = parser.parse();
		node.accept(ctx);
		assertEquals("1", sql, ctx.getSql());
	}
	
	public void testParseEndSemicolon() throws Exception {
		String sql = "SELECT * FROM emp";
		SqlParser parser = new SqlParserImpl(sql + ";");
		CommandContext ctx = new CommandContextImpl();
		Node node = parser.parse();
		node.accept(ctx);
		assertEquals("1", sql, ctx.getSql());
	}
	
	public void testCommentEndNotFound() throws Exception {
		String sql = "SELECT * FROM emp/*hoge";
		SqlParser parser = new SqlParserImpl(sql);
		try {
			parser.parse();
			fail("1");
		} catch (TokenNotClosedRuntimeException ex) {
			System.out.println(ex);
		}
	}
	
	public void testParseBindVariable() throws Exception {
		String sql = "SELECT * FROM emp WHERE job = /*job*/'CLERK' AND deptno = /*deptno*/20";
		String sql2 = "SELECT * FROM emp WHERE job = ? AND deptno = ?";
		String sql3 = "SELECT * FROM emp WHERE job = ";
		String sql4 = " AND deptno = ";
		SqlParser parser = new SqlParserImpl(sql);
		CommandContext ctx = new CommandContextImpl();
		String job = "CLERK";
		Integer deptno = new Integer(20);
		ctx.addArg("job", job);
		ctx.addArg("deptno", deptno);
		Node root = parser.parse();
		root.accept(ctx);
		System.out.println(ctx.getSql());
		assertEquals("1", sql2, ctx.getSql());
		Object[] vars = ctx.getBindVariables();
		assertEquals("2", 2, vars.length);
		assertEquals("3", job, vars[0]);
		assertEquals("4", deptno, vars[1]);
		assertEquals("5", 4, root.getChildSize());
		SqlNode sqlNode = (SqlNode) root.getChild(0);
		assertEquals("6", sql3, sqlNode.getSql());
		BindVariableNode varNode = (BindVariableNode) root.getChild(1);
		assertEquals("7", "job", varNode.getExpression());
		SqlNode sqlNode2 = (SqlNode) root.getChild(2);
		assertEquals("8", sql4, sqlNode2.getSql());
		BindVariableNode varNode2 = (BindVariableNode) root.getChild(3);
		assertEquals("9", "deptno", varNode2.getExpression());
	}
	
	public void testParseBindVariable2() throws Exception {
		String sql = "SELECT * FROM emp WHERE job = /* job*/'CLERK'";
		String sql2 = "SELECT * FROM emp WHERE job = 'CLERK'";
		String sql3 = "SELECT * FROM emp WHERE job = ";
		String sql4 = "'CLERK'";
		SqlParser parser = new SqlParserImpl(sql);
		CommandContext ctx = new CommandContextImpl();
		Node root = parser.parse();
		root.accept(ctx);
		System.out.println(ctx.getSql());
		assertEquals("1", sql2, ctx.getSql());
		assertEquals("2", 2, root.getChildSize());
		SqlNode sqlNode = (SqlNode) root.getChild(0);
		assertEquals("3", sql3, sqlNode.getSql());
		SqlNode sqlNode2 = (SqlNode) root.getChild(1);
		assertEquals("4", sql4, sqlNode2.getSql());
	}
	
	public void testParseWhiteSpace() throws Exception {
		String sql = "SELECT * FROM emp WHERE empno = /*empno*/1 AND 1 = 1";
		String sql2 = "SELECT * FROM emp WHERE empno = ? AND 1 = 1";
		String sql3 = " AND 1 = 1";
		SqlParser parser = new SqlParserImpl(sql);
		CommandContext ctx = new CommandContextImpl();
		Integer empno = new Integer(7788);
		ctx.addArg("empno", empno);
		Node root = parser.parse();
		root.accept(ctx);
		System.out.println(ctx.getSql());
		assertEquals("1", sql2, ctx.getSql());
		SqlNode sqlNode = (SqlNode) root.getChild(2);
		assertEquals("2", sql3, sqlNode.getSql());
	}
	
	public void testParseIf() throws Exception {
		String sql = "SELECT * FROM emp/*IF job != null*/ WHERE job = /*job*/'CLERK'/*END*/";
		String sql2 = "SELECT * FROM emp WHERE job = ?";
		String sql3 = "SELECT * FROM emp";
		SqlParser parser = new SqlParserImpl(sql);
		CommandContext ctx = new CommandContextImpl();
		String job = "CLERK";
		ctx.addArg("job", job);
		Node root = parser.parse();
		root.accept(ctx);
		System.out.println(ctx.getSql());
		assertEquals("1", sql2, ctx.getSql());
		Object[] vars = ctx.getBindVariables();
		assertEquals("2", 1, vars.length);
		assertEquals("3", job, vars[0]);
		assertEquals("4", 2, root.getChildSize());
		SqlNode sqlNode = (SqlNode) root.getChild(0);
		assertEquals("5", sql3, sqlNode.getSql());
		IfNode ifNode = (IfNode) root.getChild(1);
		assertEquals("6", "job != null", ifNode.getExpression());
		assertEquals("7", 2, ifNode.getChildSize());
		SqlNode sqlNode2 = (SqlNode) ifNode.getChild(0);
		assertEquals("8", " WHERE job = ", sqlNode2.getSql());
		BindVariableNode varNode = (BindVariableNode) ifNode.getChild(1);
		assertEquals("9", "job", varNode.getExpression());
		CommandContext ctx2 = new CommandContextImpl();
		root.accept(ctx2);
		System.out.println(ctx2.getSql());
		assertEquals("10", sql3, ctx2.getSql());
	}
	
	public void testParseIf2() throws Exception {
		String sql = "/*IF aaa != null*/aaa/*IF bbb != null*/bbb/*END*//*END*/";
		SqlParser parser = new SqlParserImpl(sql);
		CommandContext ctx = new CommandContextImpl();
		Node root = parser.parse();
		root.accept(ctx);
		System.out.println("[" + ctx.getSql() + "]");
		assertEquals("1", "", ctx.getSql());
		ctx.addArg("aaa", null);
		ctx.addArg("bbb", "hoge");
		root.accept(ctx);
		System.out.println("[" + ctx.getSql() + "]");
		assertEquals("2", "", ctx.getSql());
		ctx.addArg("aaa", "hoge");
		root.accept(ctx);
		System.out.println("[" + ctx.getSql() + "]");
		assertEquals("3", "aaabbb", ctx.getSql());
		CommandContext ctx2 = new CommandContextImpl();
		ctx2.addArg("aaa", "hoge");
		ctx2.addArg("bbb", null);
		root.accept(ctx2);
		System.out.println("[" + ctx2.getSql() + "]");
		assertEquals("4", "aaa", ctx2.getSql());
	}
	
	public void testParseElse() throws Exception {
		String sql = "SELECT * FROM emp WHERE /*IF job != null*/job = /*job*/'CLERK'-- ELSE job is null/*END*/";
		String sql2 = "SELECT * FROM emp WHERE job = ?";
		String sql3 = "SELECT * FROM emp WHERE job is null";
		SqlParser parser = new SqlParserImpl(sql);
		CommandContext ctx = new CommandContextImpl();
		String job = "CLERK";
		ctx.addArg("job", job);
		Node root = parser.parse();
		root.accept(ctx);
		System.out.println("[" + ctx.getSql() + "]");
		assertEquals("1", sql2, ctx.getSql());
		Object[] vars = ctx.getBindVariables();
		assertEquals("2", 1, vars.length);
		assertEquals("3", job, vars[0]);
		CommandContext ctx2 = new CommandContextImpl();
		root.accept(ctx2);
		System.out.println("[" + ctx2.getSql() + "]");
		assertEquals("4", sql3, ctx2.getSql());
	}
	
	public void testParseElse2() throws Exception {
		String sql = "/*IF false*/aaa--ELSE bbb = /*bbb*/123/*END*/";
		SqlParser parser = new SqlParserImpl(sql);
		CommandContext ctx = new CommandContextImpl();
		Integer bbb = new Integer(123);
		ctx.addArg("bbb", bbb);
		Node root = parser.parse();
		root.accept(ctx);
		System.out.println("[" + ctx.getSql() + "]");
		assertEquals("1", "bbb = ?", ctx.getSql());
		Object[] vars = ctx.getBindVariables();
		assertEquals("2", 1, vars.length);
		assertEquals("3", bbb, vars[0]);
	}
	
	public void testParseElse3() throws Exception {
		String sql = "/*IF false*/aaa--ELSE bbb/*IF false*/ccc--ELSE ddd/*END*//*END*/";
		SqlParser parser = new SqlParserImpl(sql);
		CommandContext ctx = new CommandContextImpl();
		Node root = parser.parse();
		root.accept(ctx);
		System.out.println("[" + ctx.getSql() + "]");
		assertEquals("1", "bbbddd", ctx.getSql());
	}
	
	public void testElse4() throws Exception {
		String sql = "SELECT * FROM emp/*BEGIN*/ WHERE /*IF false*/aaa-- ELSE AND deptno = 10/*END*//*END*/";
		String sql2 = "SELECT * FROM emp WHERE deptno = 10";
		SqlParser parser = new SqlParserImpl(sql);
		Node root = parser.parse();
		CommandContext ctx = new CommandContextImpl();
		root.accept(ctx);
		System.out.println(ctx.getSql());
		assertEquals("1", sql2, ctx.getSql());
	}
	
	public void testBegin() throws Exception {
		String sql = "SELECT * FROM emp/*BEGIN*/ WHERE /*IF job != null*/job = /*job*/'CLERK'/*END*//*IF deptno != null*/ AND deptno = /*deptno*/20/*END*//*END*/";
		String sql2 = "SELECT * FROM emp";
		String sql3 = "SELECT * FROM emp WHERE job = ?";
		String sql4 = "SELECT * FROM emp WHERE job = ? AND deptno = ?";
		String sql5 = "SELECT * FROM emp WHERE deptno = ?";
		SqlParser parser = new SqlParserImpl(sql);
		Node root = parser.parse();
		CommandContext ctx = new CommandContextImpl();
		root.accept(ctx);
		System.out.println(ctx.getSql());
		assertEquals("1", sql2, ctx.getSql());
		
		CommandContext ctx2 = new CommandContextImpl();
		ctx2.addArg("job", "CLERK");
		ctx2.addArg("deptno", null);
		root.accept(ctx2);
		System.out.println(ctx2.getSql());
		assertEquals("2", sql3, ctx2.getSql());
		
		CommandContext ctx3 = new CommandContextImpl();
		ctx3.addArg("job", "CLERK");
		ctx3.addArg("deptno", new Integer(20));
		root.accept(ctx3);
		System.out.println(ctx3.getSql());
		assertEquals("3", sql4, ctx3.getSql());
		
		CommandContext ctx4 = new CommandContextImpl();
		ctx4.addArg("deptno", new Integer(20));
		ctx4.addArg("job", null);
		root.accept(ctx4);
		System.out.println(ctx4.getSql());
		assertEquals("4", sql5, ctx4.getSql());
	}
	
	public void testBeginAnd() throws Exception {
		String sql = "/*BEGIN*/WHERE /*IF true*/aaa BETWEEN /*bbb*/111 AND /*ccc*/123/*END*//*END*/";
		String sql2 = "WHERE aaa BETWEEN ? AND ?";
		SqlParser parser = new SqlParserImpl(sql);
		Node root = parser.parse();
		CommandContext ctx = new CommandContextImpl();
		ctx.addArg("bbb", "111");
		ctx.addArg("ccc", "222");
		root.accept(ctx);
		System.out.println("[" + ctx.getSql() + "]");
		assertEquals("1", sql2, ctx.getSql());
	}
	
	public void testIn() throws Exception {
		String sql = "SELECT * FROM emp WHERE deptno IN /*deptnoList*/(10, 20) ORDER BY ename";
		String sql2 = "SELECT * FROM emp WHERE deptno IN (?, ?) ORDER BY ename";
		SqlParser parser = new SqlParserImpl(sql);
		Node root = parser.parse();
		CommandContext ctx = new CommandContextImpl();
		List deptnoList = new ArrayList();
		deptnoList.add(new Integer(10));
		deptnoList.add(new Integer(20));
		ctx.addArg("deptnoList", deptnoList);
		root.accept(ctx);
		System.out.println(ctx.getSql());
		assertEquals("1", sql2, ctx.getSql());
		Object[] vars = ctx.getBindVariables();
		assertEquals("2", 2, vars.length);
		assertEquals("3", new Integer(10), vars[0]);
		assertEquals("4", new Integer(20), vars[1]);
	}
	
	public void testIn2() throws Exception {
		String sql = "SELECT * FROM emp WHERE deptno IN /*deptnoList*/(10, 20) ORDER BY ename";
		String sql2 = "SELECT * FROM emp WHERE deptno IN (?, ?) ORDER BY ename";
		SqlParser parser = new SqlParserImpl(sql);
		Node root = parser.parse();
		CommandContext ctx = new CommandContextImpl();
		int[] deptnoArray = {10, 20};
		ctx.addArg("deptnoList", deptnoArray);
		root.accept(ctx);
		System.out.println(ctx.getSql());
		assertEquals("1", sql2, ctx.getSql());
		Object[] vars = ctx.getBindVariables();
		assertEquals("2", 2, vars.length);
		assertEquals("3", new Integer(10), vars[0]);
		assertEquals("4", new Integer(20), vars[1]);
	}
	
	public void testIn3() throws Exception {
		String sql = "SELECT * FROM emp WHERE ename IN /*enames*/('SCOTT','MARY') AND job IN /*jobs*/('ANALYST', 'FREE')";
		String sql2 = "SELECT * FROM emp WHERE ename IN (?, ?) AND job IN (?, ?)";
		SqlParser parser = new SqlParserImpl(sql);
		Node root = parser.parse();
		CommandContext ctx = new CommandContextImpl();
		String[] enames = {"SCOTT", "MARY"};
		String[] jobs = {"ANALYST", "FREE"};
		ctx.addArg("enames", enames);
		ctx.addArg("jobs", jobs);
		root.accept(ctx);
		System.out.println(ctx.getSql());
		assertEquals("1", sql2, ctx.getSql());
		Object[] vars = ctx.getBindVariables();
		assertEquals("2", 4, vars.length);
		assertEquals("3", "SCOTT", vars[0]);
		assertEquals("4", "MARY", vars[1]);
		assertEquals("5", "ANALYST", vars[2]);
		assertEquals("6", "FREE", vars[3]);
	}
	
	public void testParseBindVariable3() throws Exception {
		String sql = "BETWEEN sal ? AND ?";
		SqlParser parser = new SqlParserImpl(sql);
		Node root = parser.parse();
		CommandContext ctx = new CommandContextImpl();
		ctx.addArg("$1", new Integer(0));
		ctx.addArg("$2", new Integer(1000));
		root.accept(ctx);
		System.out.println(ctx.getSql());
		assertEquals("1", sql, ctx.getSql());
		Object[] vars = ctx.getBindVariables();
		assertEquals("2", 2, vars.length);
		assertEquals("3", new Integer(0), vars[0]);
		assertEquals("4", new Integer(1000), vars[1]);
	}
	
	public void testEndNotFound() throws Exception {
		String sql = "/*BEGIN*/";
		SqlParser parser = new SqlParserImpl(sql);
		try {
			parser.parse();
			fail("1");
		} catch (EndCommentNotFoundRuntimeException ex) {
			System.out.println(ex);
		}
	}
}