/*
 * Copyright 2006 Takahiro Nakamura.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
 * either express or implied. See the License for the specific language
 * governing permissions and limitations under the License.
 */
package woolpack.sql;

import java.util.Arrays;
import java.util.List;

import woolpack.sql.convert.ParamBindInfo;
import woolpack.sql.convert.SqlColumnUtils;
import woolpack.sql.convert.SqlOperator;
import woolpack.sql.fn.PreparedStatementInfo;

import junit.framework.TestCase;

public class SqlColumnUtilsTest extends TestCase {
	public void testInsert() {
		final String query =
			"insert into myTable (myCol0,myCol1,myCol2) values (?,?,?)";
		final List<ParamBindInfo> list = SqlColumnUtils.getList(query);
		assertEquals(
				Arrays.asList(
						new ParamBindInfo("myCol0"),
						new ParamBindInfo("myCol1"),
						new ParamBindInfo("myCol2")
				), list);
	}
	
	public void testSelect() {
		final String query =
			"select * from myTable"
			+ " where myCol0 = ?"
			+ " and myCol1 < ?"
			+ " and myCol2 > ?"
			+ " and myCol3 <= ?"
			+ " and myCol4 >= ?"
			+ " and myCol5 in (?, ?)"
			+ " and myCol6 like ?"
			+ " and myCol7 between ? and ?"
			;
		final List<ParamBindInfo> list = SqlColumnUtils.getList(query);
		assertEquals(
				Arrays.asList(
						new ParamBindInfo(
								"myCol0",
								query.indexOf("myCol0"),
								query.indexOf("myCol0") + "myCol0 = ?".length(),
								" = ?",
								SqlOperator.EQ,
								true),
						new ParamBindInfo(
								"myCol1",
								query.indexOf("myCol1"),
								query.indexOf("myCol1") + "myCol1 < ?".length(),
								" < ?",
								SqlOperator.LT,
								true),
						new ParamBindInfo(
								"myCol2",
								query.indexOf("myCol2"),
								query.indexOf("myCol2") + "myCol2 > ?".length(),
								" > ?",
								SqlOperator.GT,
								true),
						new ParamBindInfo(
								"myCol3",
								query.indexOf("myCol3"),
								query.indexOf("myCol3") + "myCol3 <= ?".length(),
								" <= ?",
								SqlOperator.LE,
								true),
						new ParamBindInfo(
								"myCol4",
								query.indexOf("myCol4"),
								query.indexOf("myCol4") + "myCol4 >= ?".length(),
								" >= ?",
								SqlOperator.GE,
								true),
						new ParamBindInfo(
								"myCol5",
								query.indexOf("myCol5"),
								query.indexOf("myCol5") + "myCol5 in (?, ?)".length(),
								" in (?, ?)",
								SqlOperator.IN,
								true),
						new ParamBindInfo(
								"myCol6",
								query.indexOf("myCol6"),
								query.indexOf("myCol6") + "myCol6 like ?".length(),
								" like ?",
								SqlOperator.LIKE,
								true),
						new ParamBindInfo(
								"myCol7",
								query.indexOf("myCol7"),
								query.indexOf("myCol7") + "myCol7 between ? and ?".length(),
								" between ? and ?",
								SqlOperator.BETWEEN,
								true)
				), list);
	}
	
	public void testUpdate() {
		final String query =
			"update myTable set myCol0 = ?, myCol1 = ?"
			+ " where myCol2 = ?"
			+ " and myCol3 = ?"
			;
		final List<ParamBindInfo> list = SqlColumnUtils.getList(query);
		assertEquals(
				Arrays.asList(
						new ParamBindInfo(
								"myCol0",
								query.indexOf("myCol0"),
								query.indexOf("myCol0") + "myCol0 = ?".length(),
								" = ?",
								SqlOperator.EQ,
								false),
						new ParamBindInfo(
								"myCol1",
								query.indexOf("myCol1"),
								query.indexOf("myCol1") + "myCol1 = ?".length(),
								" = ?",
								SqlOperator.EQ,
								false),
						new ParamBindInfo(
								"myCol2",
								query.indexOf("myCol2"),
								query.indexOf("myCol2") + "myCol2 = ?".length(),
								" = ?",
								SqlOperator.EQ,
								true),
						new ParamBindInfo(
								"myCol3",
								query.indexOf("myCol3"),
								query.indexOf("myCol3") + "myCol3 = ?".length(),
								" = ?",
								SqlOperator.EQ,
								true)
				), list);
	}
	
	public void testDate() {
		final String query =
			"delete from myTable"
			+ " where myCol2 = ?"
			+ " and myCol3 = ?"
			;
		final List<ParamBindInfo> list = SqlColumnUtils.getList(query);
		assertEquals(
				Arrays.asList(
						new ParamBindInfo(
								"myCol2",
								query.indexOf("myCol2"),
								query.indexOf("myCol2") + "myCol2 = ?".length(),
								" = ?",
								SqlOperator.EQ,
								true),
						new ParamBindInfo(
								"myCol3",
								query.indexOf("myCol3"),
								query.indexOf("myCol3") + "myCol3 = ?".length(),
								" = ?",
								SqlOperator.EQ,
								true)
				), list);
	}
	
	public void testToPreparedStatementInfo1() {
		final String s = 
			"update myTable set myCol8 = ?"
			+ " where myCol0 = ?"
			+ " and myCol1 < ?"
			+ " and myCol2 > ?"
			+ " and myCol3 <= ?"
			+ " and myCol4 >= ?"
			+ " and myCol5 in (?, ?)"
			+ " and myCol6 like ?"
			+ " and myCol7 between ? and ?";
		final PreparedStatementInfo info = SqlColumnUtils
		.toPreparedStatementInfo(s, SqlColumnUtils.APPEND_NAME);
		assertEquals(s, info.getQuery());
		assertEquals(Arrays.asList(
				"myCol8",
				"myCol0",
				"myCol1",
				"myCol2",
				"myCol3",
				"myCol4",
				"myCol5_0",
				"myCol5_1",
				"myCol6",
				"myCol7_0",
				"myCol7_1"), info.getList());
	}
	
	public void testToPreparedStatementInfo2() {
		final String s = 
			"update myTable set myCol8 = ?"
			+ " where myCol0 = ?"
			+ " and myCol1 < ?"
			+ " and myCol2 > ?"
			+ " and myCol3 <= ?"
			+ " and myCol4 >= ?"
			+ " and myCol5 in (?, ?)"
			+ " and myCol6 like ?"
			+ " and myCol7 between ? and ?";
		final PreparedStatementInfo info = SqlColumnUtils
		.toPreparedStatementInfo(s, SqlColumnUtils.APPEND_NAME_WITH_OPERATOR);
		assertEquals(s, info.getQuery());
		assertEquals(Arrays.asList(
				"myCol8",
				"myCol0_EQ",
				"myCol1_LT",
				"myCol2_GT",
				"myCol3_LE",
				"myCol4_GE",
				"myCol5_IN0",
				"myCol5_IN1",
				"myCol6_LIKE",
				"myCol7_BETWEEN0",
				"myCol7_BETWEEN1"), info.getList());
	}
}
