package com.j256.ormlite.stmt;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.junit.Test;

import com.j256.ormlite.BaseCoreTest;
import com.j256.ormlite.dao.BaseDaoImpl;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.field.DataType;
import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.field.FieldType;
import com.j256.ormlite.field.SqlType;
import com.j256.ormlite.stmt.query.SimpleComparison;
import com.j256.ormlite.table.TableInfo;

public class WhereTest extends BaseCoreTest {

	private final static String ID_COLUMN_NAME = "id";
	private final static String STRING_COLUMN_NAME = "stringie";
	private final static String FOREIGN_COLUMN_NAME = "foreign";

	@Test
	public void testToString() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		assertTrue(where.toString().contains("empty where clause"));
		String value = "bar";
		FieldType numberFieldType = FieldType.createFieldType(databaseType, "foo",
				Foo.class.getDeclaredField(Foo.VAL_COLUMN_NAME), Foo.class);
		SimpleComparison eq =
				new SimpleComparison(Foo.VAL_COLUMN_NAME, numberFieldType, value, SimpleComparison.EQUAL_TO_OPERATION);
		where.eq(Foo.VAL_COLUMN_NAME, value);
		assertTrue(where.toString().contains(eq.toString()));
	}

	@Test(expected = IllegalStateException.class)
	public void testAlreadyNeedsClause() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		where.eq(Foo.VAL_COLUMN_NAME, "bar");
		where.and();
		where.and();
		StringBuilder sb = new StringBuilder();
		where.appendSql(null, sb, null);
	}

	@Test(expected = IllegalStateException.class)
	public void testNoClauses() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		where.appendSql(null, new StringBuilder(), new ArrayList<ArgumentHolder>());
	}

	@Test(expected = IllegalStateException.class)
	public void testMissingAndOr() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 1;
		where.eq(Foo.VAL_COLUMN_NAME, val);
		where.eq(Foo.VAL_COLUMN_NAME, val);
		where.appendSql(null, new StringBuilder(), new ArrayList<ArgumentHolder>());
	}

	@Test(expected = IllegalStateException.class)
	public void testMissingClause() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 1;
		where.and();
		where.eq(Foo.VAL_COLUMN_NAME, val);
		where.appendSql(null, new StringBuilder(), new ArrayList<ArgumentHolder>());
	}

	@Test(expected = IllegalArgumentException.class)
	public void testComparisonUnknownField() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 1;
		where.eq("unknown-field", val);
	}

	@Test(expected = IllegalArgumentException.class)
	public void testComparisonFieldNameNotColumnName() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		assertNotNull(Foo.class.getDeclaredField(Foo.ID_COLUMN_NAME));
		int val = 1;
		where.eq("stringField", val);
	}

	@Test
	public void testAndInline() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 1;
		where.eq(Foo.VAL_COLUMN_NAME, val);
		where.and();
		where.eq(Foo.VAL_COLUMN_NAME, val);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		sb.append('(');
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val);
		sb.append(" AND ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val);
		sb.append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@SuppressWarnings("unchecked")
	@Test
	public void testAndRemoveClauses() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 1;
		where.and(where.eq(Foo.VAL_COLUMN_NAME, val), where.eq(Foo.VAL_COLUMN_NAME, val));
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		sb.append('(');
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val);
		sb.append(" AND ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val);
		sb.append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testBetween() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int low = 1;
		int high = 1;
		where.between(Foo.VAL_COLUMN_NAME, low, high);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" BETWEEN ").append(low);
		sb.append(" AND ").append(high);
		sb.append(' ');
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testEq() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 112;
		where.eq(Foo.VAL_COLUMN_NAME, val);
		testOperation(where, Foo.VAL_COLUMN_NAME, "=", val);
	}

	@Test
	public void testGe() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 112;
		where.ge(Foo.VAL_COLUMN_NAME, val);
		testOperation(where, Foo.VAL_COLUMN_NAME, ">=", val);
	}

	@Test
	public void testGt() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 112;
		where.gt(Foo.VAL_COLUMN_NAME, val);
		testOperation(where, Foo.VAL_COLUMN_NAME, ">", val);
	}

	@Test
	public void testLt() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 112;
		where.lt(Foo.VAL_COLUMN_NAME, val);
		testOperation(where, Foo.VAL_COLUMN_NAME, "<", val);
	}

	@Test
	public void testLe() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 112;
		where.le(Foo.VAL_COLUMN_NAME, val);
		testOperation(where, Foo.VAL_COLUMN_NAME, "<=", val);
	}

	@Test
	public void testNe() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 112;
		where.ne(Foo.VAL_COLUMN_NAME, val);
		testOperation(where, Foo.VAL_COLUMN_NAME, "<>", val);
	}

	@Test
	public void testIn() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 112;
		where.in(Foo.VAL_COLUMN_NAME, val);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" IN (");
		sb.append(val).append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testNotIn() throws Exception {
		Dao<Foo, String> dao = createDao(Foo.class, true);
		Foo foo1 = new Foo();
		foo1.val = 63465365;
		assertEquals(1, dao.create(foo1));
		Foo foo2 = new Foo();
		foo2 = new Foo();
		foo2.val = 163123;
		assertEquals(1, dao.create(foo2));

		List<Foo> results = dao.queryBuilder().where().in(Foo.ID_COLUMN_NAME, foo2.id).query();
		assertEquals(1, results.size());
		assertEquals(foo2.val, results.get(0).val);

		// support not with in
		results = dao.queryBuilder().where().not().in(Foo.ID_COLUMN_NAME, foo2.id).query();
		assertEquals(1, results.size());
		assertEquals(foo1.val, results.get(0).val);

		// support not in
		results = dao.queryBuilder().where().notIn(Foo.ID_COLUMN_NAME, foo2.id).query();
		assertEquals(1, results.size());
		assertEquals(foo1.val, results.get(0).val);
	}

	@Test
	public void testInMany() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int[] vals = new int[] { 112, 123, 61 };
		// NOTE: we can't pass in vals here
		where.in(Foo.VAL_COLUMN_NAME, vals[0], vals[1], vals[2]);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" IN (");
		for (int valC = 0; valC < vals.length; valC++) {
			if (valC > 0) {
				sb.append(',');
			}
			sb.append(vals[valC]).append(' ');
		}
		sb.append(") ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testInManyist() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		List<Integer> vals = new ArrayList<Integer>();
		vals.add(112);
		vals.add(123);
		vals.add(61);
		// NOTE: we can't pass in vals here
		where.in(Foo.VAL_COLUMN_NAME, vals);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" IN (");
		for (int valC = 0; valC < vals.size(); valC++) {
			if (valC > 0) {
				sb.append(',');
			}
			sb.append(vals.get(valC)).append(' ');
		}
		sb.append(") ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testIsNull() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		where.isNull(Foo.VAL_COLUMN_NAME);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" IS NULL ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testIsNotNull() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		where.isNotNull(Foo.VAL_COLUMN_NAME);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" IS NOT NULL ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test(expected = IllegalArgumentException.class)
	public void testInArrayWithinArray() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		// NOTE: we can't pass in vals here
		where.in(Foo.VAL_COLUMN_NAME, new int[] { 112 });
	}

	@Test
	public void testLike() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 112;
		where.like(Foo.VAL_COLUMN_NAME, val);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" LIKE ");
		sb.append(val).append(' ');
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testNotFuture() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 112;
		where.not();
		where.like(Foo.VAL_COLUMN_NAME, val);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		sb.append("(NOT ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" LIKE ");
		sb.append(val).append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testMultipleFuture() throws Exception {
		Dao<Foo, String> dao = createDao(Foo.class, true);

		Foo foo1 = new Foo();
		foo1.val = 123;
		foo1.stringField = "fjewpjfew";
		dao.create(foo1);

		QueryBuilder<Foo, String> qb = dao.queryBuilder();
		Where<Foo, String> where = qb.where();

		where.eq(Foo.VAL_COLUMN_NAME, foo1.val);
		where.and();
		where.not();
		where.like(Foo.STRING_COLUMN_NAME, "hello");
		List<Foo> results = where.query();
		assertNotNull(results);
		assertEquals(1, results.size());
		assertEquals(foo1.id, results.get(0).id);
	}

	@Test
	public void testNotAbsorb() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 112;
		where.like(Foo.VAL_COLUMN_NAME, val);
		where.not(where);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		sb.append("(NOT ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" LIKE ");
		sb.append(val).append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testAndFuture() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 112;
		where.eq(Foo.VAL_COLUMN_NAME, val);
		where.and();
		where.eq(Foo.VAL_COLUMN_NAME, val);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		sb.append('(');
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val);
		sb.append(" AND ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val);
		sb.append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testOrFuture() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 112;
		where.eq(Foo.VAL_COLUMN_NAME, val);
		where.or();
		where.eq(Foo.VAL_COLUMN_NAME, val);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		sb.append('(');
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val);
		sb.append(" OR ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val);
		sb.append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@SuppressWarnings("unchecked")
	@Test
	public void testOrAbsorb() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 112;
		where.or(where.eq(Foo.VAL_COLUMN_NAME, val), where.eq(Foo.VAL_COLUMN_NAME, val));
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		sb.append('(');
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val);
		sb.append(" OR ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val);
		sb.append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	private void testOperation(Where<Foo, String> where, String columnName, String operation, Object value)
			throws Exception {
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		databaseType.appendEscapedEntityName(sb, columnName);
		sb.append(' ').append(operation).append(' ');
		sb.append(value).append(' ');
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testIdEq() throws Exception {
		Where<FooId, Integer> where =
				new Where<FooId, Integer>(new TableInfo<FooId, Integer>(databaseType, FooId.class), null, databaseType);
		int val = 112;
		where.idEq(val);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		databaseType.appendEscapedEntityName(sb, ID_COLUMN_NAME);
		sb.append(" = ").append(val);
		sb.append(' ');
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test(expected = SQLException.class)
	public void testIdEqNoId() throws Exception {
		new Where<FooNoId, Integer>(new TableInfo<FooNoId, Integer>(databaseType, FooNoId.class), null, databaseType)
				.idEq(100);
	}

	@Test
	public void testIdEqObjectId() throws Exception {
		FooId foo = new FooId();
		int id = 112132;
		foo.id = id;
		Where<FooId, Integer> where =
				new Where<FooId, Integer>(new TableInfo<FooId, Integer>(databaseType, FooId.class), null, databaseType);
		BaseDaoImpl<FooId, Integer> fooDao = new BaseDaoImpl<FooId, Integer>(connectionSource, FooId.class) {
		};
		where.idEq(fooDao, foo);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		databaseType.appendEscapedEntityName(sb, ID_COLUMN_NAME);
		sb.append(" = ").append(id);
		sb.append(' ');
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test(expected = SQLException.class)
	public void testIdEqObjectIdNoId() throws Exception {
		new Where<FooNoId, Integer>(new TableInfo<FooNoId, Integer>(databaseType, FooNoId.class), null, databaseType)
				.idEq(new BaseDaoImpl<FooNoId, Integer>(connectionSource, FooNoId.class) {
				}, new FooNoId());
	}

	@Test
	public void testInSubQuery() throws Exception {
		TableInfo<ForeignFoo, Integer> tableInfo = new TableInfo<ForeignFoo, Integer>(databaseType, ForeignFoo.class);
		Where<ForeignFoo, Integer> where = new Where<ForeignFoo, Integer>(tableInfo, null, databaseType);
		BaseDaoImpl<ForeignFoo, Integer> foreignDao =
				new BaseDaoImpl<ForeignFoo, Integer>(connectionSource, ForeignFoo.class) {
				};
		QueryBuilder<ForeignFoo, Integer> qb = foreignDao.queryBuilder();
		qb.selectColumns(ID_COLUMN_NAME);
		where.in(ID_COLUMN_NAME, qb);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		databaseType.appendEscapedEntityName(sb, ID_COLUMN_NAME);
		sb.append(" IN (");
		sb.append("SELECT ");
		databaseType.appendEscapedEntityName(sb, ID_COLUMN_NAME);
		sb.append(" FROM ");
		databaseType.appendEscapedEntityName(sb, tableInfo.getTableName());
		sb.append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testInSubQueryForReal() throws Exception {
		Dao<Foo, Integer> dao = createDao(Foo.class, true);
		Foo foo1 = new Foo();
		foo1.val = 785463547;
		assertEquals(1, dao.create(foo1));
		Foo foo2 = new Foo();
		foo2.val = 163547;
		assertEquals(1, dao.create(foo2));

		QueryBuilder<Foo, Integer> qb = dao.queryBuilder();
		qb.selectColumns(Foo.ID_COLUMN_NAME);
		qb.where().eq(Foo.VAL_COLUMN_NAME, foo2.val);
		List<Foo> results = dao.queryBuilder().where().in(Foo.ID_COLUMN_NAME, qb).query();
		assertEquals(1, results.size());
		assertEquals(foo2.val, results.get(0).val);

		// test not in with sub query
		results = dao.queryBuilder().where().notIn(Foo.ID_COLUMN_NAME, qb).query();
		assertEquals(1, results.size());
		assertEquals(foo1.val, results.get(0).val);
	}

	@Test(expected = SQLException.class)
	public void testInSubQueryToManySubColumns() throws Exception {
		TableInfo<ForeignFoo, Integer> tableInfo = new TableInfo<ForeignFoo, Integer>(databaseType, ForeignFoo.class);
		Where<ForeignFoo, Integer> where = new Where<ForeignFoo, Integer>(tableInfo, null, databaseType);
		BaseDaoImpl<ForeignFoo, Integer> foreignDao =
				new BaseDaoImpl<ForeignFoo, Integer>(connectionSource, ForeignFoo.class) {
				};
		QueryBuilder<ForeignFoo, Integer> qb = foreignDao.queryBuilder();
		qb.selectColumns(ID_COLUMN_NAME, FOREIGN_COLUMN_NAME);
		where.in(ID_COLUMN_NAME, qb);
	}

	@Test
	public void testExistsSubQuery() throws Exception {
		TableInfo<ForeignFoo, Integer> tableInfo = new TableInfo<ForeignFoo, Integer>(databaseType, ForeignFoo.class);
		Where<ForeignFoo, Integer> where = new Where<ForeignFoo, Integer>(tableInfo, null, databaseType);
		BaseDaoImpl<ForeignFoo, Integer> foreignDao =
				new BaseDaoImpl<ForeignFoo, Integer>(connectionSource, ForeignFoo.class) {
				};
		QueryBuilder<ForeignFoo, Integer> qb = foreignDao.queryBuilder();
		where.exists(qb);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		sb.append("EXISTS (");
		sb.append("SELECT * FROM ");
		databaseType.appendEscapedEntityName(sb, tableInfo.getTableName());
		sb.append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testNotExistsSubQuery() throws Exception {
		TableInfo<ForeignFoo, Integer> tableInfo = new TableInfo<ForeignFoo, Integer>(databaseType, ForeignFoo.class);
		Where<ForeignFoo, Integer> where = new Where<ForeignFoo, Integer>(tableInfo, null, databaseType);
		BaseDaoImpl<ForeignFoo, Integer> foreignDao =
				new BaseDaoImpl<ForeignFoo, Integer>(connectionSource, ForeignFoo.class) {
				};
		QueryBuilder<ForeignFoo, Integer> qb = foreignDao.queryBuilder();
		where.not().exists(qb);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		sb.append("(NOT EXISTS (");
		sb.append("SELECT * FROM ");
		databaseType.appendEscapedEntityName(sb, tableInfo.getTableName());
		sb.append(" ) ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testRaw() throws Exception {
		TableInfo<Foo, Integer> tableInfo = new TableInfo<Foo, Integer>(databaseType, Foo.class);
		Where<Foo, Integer> where = new Where<Foo, Integer>(tableInfo, null, databaseType);
		String raw = "VAL = 1";
		int val = 17;
		where.eq(Foo.VAL_COLUMN_NAME, val).and().raw(raw);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		sb.append('(');
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val);
		sb.append(" AND ").append(raw).append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	public void testRawComparison() throws Exception {
		Dao<Foo, Integer> dao = createDao(Foo.class, true);
		Foo foo = new Foo();
		int val = 63465365;
		foo.val = val;
		assertEquals(1, dao.create(foo));

		QueryBuilder<Foo, Integer> qb = dao.queryBuilder();
		qb.where().rawComparison("id", "=", new SelectArg(foo.id));
		List<Foo> results = qb.query();
		assertNotNull(results);
		assertEquals(1, results.size());
		assertEquals(val, results.get(0).val);
	}

	@Test
	public void testRawArgsColumn() throws Exception {
		Dao<Foo, String> dao = createDao(Foo.class, true);
		Foo foo = new Foo();
		int val = 63465365;
		foo.val = val;
		assertEquals(1, dao.create(foo));

		QueryBuilder<Foo, String> qb = dao.queryBuilder();
		qb.where().raw(Foo.ID_COLUMN_NAME + " = ?", new SelectArg(Foo.ID_COLUMN_NAME, foo.id));
		List<Foo> results = qb.query();
		assertNotNull(results);
		assertEquals(1, results.size());
		assertEquals(val, results.get(0).val);
	}

	@Test
	public void testRawArgsColumnNoValue() throws Exception {
		Dao<Foo, String> dao = createDao(Foo.class, true);
		Foo foo = new Foo();
		int val = 63465365;
		foo.val = val;
		assertEquals(1, dao.create(foo));

		QueryBuilder<Foo, String> qb = dao.queryBuilder();
		SelectArg arg = new SelectArg(Foo.ID_COLUMN_NAME, null);
		qb.where().raw(Foo.ID_COLUMN_NAME + " = ?", arg);
		arg.setValue(foo.id);
		List<Foo> results = qb.query();
		assertNotNull(results);
		assertEquals(1, results.size());
		assertEquals(val, results.get(0).val);
	}

	@Test
	public void testRawArgsColumnSqlType() throws Exception {
		Dao<Foo, Integer> dao = createDao(Foo.class, true);
		Foo foo = new Foo();
		int val = 63465365;
		foo.val = val;
		assertEquals(1, dao.create(foo));

		QueryBuilder<Foo, Integer> qb = dao.queryBuilder();
		qb.where().raw(Foo.ID_COLUMN_NAME + " = ? and " + val + " = ?", new SelectArg(SqlType.STRING, foo.id),
				new SelectArg(SqlType.INTEGER, val));
		List<Foo> results = qb.query();
		assertNotNull(results);
		assertEquals(1, results.size());
		assertEquals(val, results.get(0).val);
	}

	@Test(expected = IllegalArgumentException.class)
	public void testRawArgsNoColumnName() throws Exception {
		Dao<Foo, String> dao = createDao(Foo.class, true);
		dao.queryBuilder().where().raw("id = ?", new SelectArg(7));
	}

	@Test
	public void testClear() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val = 112;
		where.eq(Foo.VAL_COLUMN_NAME, val);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ");
		sb.append(val).append(' ');
		assertEquals(sb.toString(), whereSb.toString());

		where.reset();
		whereSb.setLength(0);
		where.eq(Foo.VAL_COLUMN_NAME, val + 1);
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		sb.setLength(0);
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ");
		sb.append(val + 1).append(' ');
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	@SuppressWarnings("unchecked")
	public void testAnyMany() throws Exception {
		int val1 = 111;
		int val2 = 112;
		int val3 = 113;
		int val4 = 114;
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		where.and(where.eq(Foo.VAL_COLUMN_NAME, val1), where.eq(Foo.VAL_COLUMN_NAME, val2),
				where.eq(Foo.VAL_COLUMN_NAME, val3), where.eq(Foo.VAL_COLUMN_NAME, val4));
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		sb.append('(');
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		// NOTE: they are done in reverse order
		sb.append(" = ").append(val1);
		sb.append(" AND ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val2);
		sb.append(" AND ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val3);
		sb.append(" AND ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val4);
		sb.append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());

		where.reset();
		where.eq(Foo.VAL_COLUMN_NAME, val1);
		where.eq(Foo.VAL_COLUMN_NAME, val2);
		where.eq(Foo.VAL_COLUMN_NAME, val3);
		where.eq(Foo.VAL_COLUMN_NAME, val4);
		where.and(4);
		whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		sb = new StringBuilder();
		sb.append('(');
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		// NOTE: they are done in reverse order
		sb.append(" = ").append(val1);
		sb.append(" AND ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val2);
		sb.append(" AND ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val3);
		sb.append(" AND ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val4);
		sb.append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test
	@SuppressWarnings("unchecked")
	public void testOrMany() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val1 = 111;
		int val2 = 112;
		int val3 = 113;
		int val4 = 114;
		where.or(where.eq(Foo.VAL_COLUMN_NAME, val1), where.eq(Foo.VAL_COLUMN_NAME, val2),
				where.eq(Foo.VAL_COLUMN_NAME, val3), where.eq(Foo.VAL_COLUMN_NAME, val4));
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		sb.append('(');
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		// NOTE: they are done in reverse order
		sb.append(" = ").append(val1);
		sb.append(" OR ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val2);
		sb.append(" OR ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val3);
		sb.append(" OR ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val4);
		sb.append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());

		where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		where.eq(Foo.VAL_COLUMN_NAME, val1);
		where.eq(Foo.VAL_COLUMN_NAME, val2);
		where.eq(Foo.VAL_COLUMN_NAME, val3);
		where.eq(Foo.VAL_COLUMN_NAME, val4);
		where.or(4);
		whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		sb = new StringBuilder();
		sb.append('(');
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		// NOTE: they are done in reverse order
		sb.append(" = ").append(val1);
		sb.append(" OR ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val2);
		sb.append(" OR ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val3);
		sb.append(" OR ");
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		sb.append(" = ").append(val4);
		sb.append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test(expected = IllegalArgumentException.class)
	public void testOrManyZero() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		where.or(0);
	}

	@Test
	public void testOrManyOne() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val1 = 1312313;
		where.eq(Foo.VAL_COLUMN_NAME, val1);
		where.or(1);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		sb.append('(');
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		// NOTE: they are done in reverse order
		sb.append(" = ").append(val1);
		sb.append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test(expected = IllegalArgumentException.class)
	public void testAndManyZero() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		where.and(0);
	}

	@Test
	public void testAndManyOne() throws Exception {
		Where<Foo, String> where = new Where<Foo, String>(createTableInfo(), null, databaseType);
		int val1 = 1312313;
		where.eq(Foo.VAL_COLUMN_NAME, val1);
		where.and(1);
		StringBuilder whereSb = new StringBuilder();
		where.appendSql(null, whereSb, new ArrayList<ArgumentHolder>());
		StringBuilder sb = new StringBuilder();
		sb.append('(');
		databaseType.appendEscapedEntityName(sb, Foo.VAL_COLUMN_NAME);
		// NOTE: they are done in reverse order
		sb.append(" = ").append(val1);
		sb.append(" ) ");
		assertEquals(sb.toString(), whereSb.toString());
	}

	@Test(expected = IllegalArgumentException.class)
	public void testInnerQuerySubQueryWhere() throws Exception {
		Dao<Foo, Integer> fooDao = createDao(Foo.class, true);

		fooDao.queryBuilder()
				.where()
				.in(Foo.ID_COLUMN_NAME,
						// this is a problem because eq() returns a Where not a QueryBuilder
						fooDao.queryBuilder().selectColumns(Foo.ID_COLUMN_NAME).where().eq(Foo.ID_COLUMN_NAME, 1))
				.query();
	}

	@Test(expected = IllegalArgumentException.class)
	public void testInnerQuerySubQueryPrepared() throws Exception {
		Dao<Foo, Integer> fooDao = createDao(Foo.class, true);

		fooDao.queryBuilder()
				.where()
				.in(Foo.ID_COLUMN_NAME,
						// this is a problem because prepare() returns a PreparedStmt not a QueryBuilder
						fooDao.queryBuilder().selectColumns(Foo.ID_COLUMN_NAME).prepare())
				.query();
	}

	@Test
	public void testDateBetween() throws Exception {
		Dao<DateBetween, Object> dao = createDao(DateBetween.class, true);

		DateBetween dateBetween = new DateBetween();
		long now = System.currentTimeMillis();
		Date date = new Date(now);
		dateBetween.date = date;
		assertEquals(1, dao.create(dateBetween));

		QueryBuilder<DateBetween, Object> qb = dao.queryBuilder();
		qb.where().between(DateBetween.DATE_COLUMN_NAME, new Date(now - 1), new Date(now + 1));
		List<DateBetween> results = qb.query();
		assertEquals(1, results.size());

		qb.where().reset();
		qb.where().between(DateBetween.DATE_COLUMN_NAME, new Date(now), new Date(now + 1));
		results = qb.query();
		assertEquals(1, results.size());

		qb.where().reset();
		qb.where().between(DateBetween.DATE_COLUMN_NAME, new Date(now), new Date(now));
		results = qb.query();
		assertEquals(1, results.size());
	}

	private TableInfo<Foo, String> createTableInfo() throws SQLException {
		return new TableInfo<Foo, String>(databaseType, Foo.class);
	}

	protected static class FooNoId {
		@DatabaseField(columnName = STRING_COLUMN_NAME)
		String string;

		FooNoId() {
		}
	}

	protected static class FooId {
		@DatabaseField(id = true, columnName = ID_COLUMN_NAME)
		int id;
		@DatabaseField(columnName = STRING_COLUMN_NAME)
		String string;

		FooId() {
		}
	}

	protected static class ForeignFoo {
		@DatabaseField(id = true, columnName = ID_COLUMN_NAME)
		int id;
		@DatabaseField(foreign = true, columnName = FOREIGN_COLUMN_NAME)
		FooId foo;

		ForeignFoo() {
		}
	}

	protected static class DateBetween {
		public static final String DATE_COLUMN_NAME = "date";
		@DatabaseField(id = true, columnName = ID_COLUMN_NAME)
		int id;
		@DatabaseField(columnName = DATE_COLUMN_NAME, dataType = DataType.DATE_LONG)
		Date date;

		DateBetween() {
		}
	}
}