Fix unit tests for column name escaping

4 files changed, 114 insertions, 58 deletions

diff --git a/src/test/java/com/iciql/test/OneOfTest.java b/src/test/java/com/iciql/test/OneOfTest.java
index 2ca7cc7..e534262 100644
--- a/src/test/java/com/iciql/test/OneOfTest.java
+++ b/src/test/java/com/iciql/test/OneOfTest.java
@@ -1,11 +1,11 @@
 /*
  * Copyright (c) 2009-2014, Architector Inc., Japan
  * All rights reserved.
- * 
+ *
  * 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
@@ -18,10 +18,13 @@
 package com.iciql.test;
 
 import static org.junit.Assert.assertEquals;
+
 import java.util.ArrayList;
+
 import org.junit.After;
 import org.junit.Before;
 import org.junit.Test;
+
 import com.iciql.Db;
 import com.iciql.test.models.Customer;
 import com.iciql.test.models.PrimitivesModel;
@@ -43,65 +46,75 @@ public class OneOfTest {
 	@SuppressWarnings("serial")
 	@Test
 	public void oneOfTest() {
+		String PrimitivesTest = db.getDialect().prepareTableName(null, "PrimitivesTest");
+		String Customer = db.getDialect().prepareTableName(null, "Customer");
+		String myInteger = db.getDialect().prepareColumnName("myInteger");
+		String customerId = db.getDialect().prepareColumnName("customerId");
+
 		PrimitivesModel p = new PrimitivesModel();
 		assertEquals(
+				String.format("SELECT * FROM %s WHERE %s IN(0)", PrimitivesTest, myInteger),
 				db.from(p)
 						.where(p.myInteger).oneOf(0)
-						.toSQL(),
-				"SELECT * FROM PrimitivesTest WHERE myInteger IN(0)");
+						.toSQL());
 		assertEquals(
+				String.format("SELECT * FROM %s WHERE %s IN(0, 1)", PrimitivesTest, myInteger),
 				db.from(p)
 						.where(p.myInteger).oneOf(0, 1)
-						.toSQL(),
-				"SELECT * FROM PrimitivesTest WHERE myInteger IN(0, 1)");
+						.toSQL());
 		Customer c = new Customer();
 		assertEquals(
+				String.format("SELECT * FROM %s WHERE %s IN('a')", Customer, customerId),
 				db.from(c)
 						.where(c.customerId).oneOf(new ArrayList<String>() {{
 							this.add("a");
 						}})
-						.toSQL(),
-				"SELECT * FROM Customer WHERE customerId IN('a')");
+						.toSQL());
 		assertEquals(
+				String.format("SELECT * FROM %s WHERE %s IN('a', 'b')", Customer, customerId),
 				db.from(c)
 						.where(c.customerId).oneOf(new ArrayList<String>() {{
 							this.add("a");
 							this.add("b");
 						}})
-						.toSQL(),
-				"SELECT * FROM Customer WHERE customerId IN('a', 'b')");
+						.toSQL());
 	}
 
 	@SuppressWarnings("serial")
 	@Test
 	public void noneOfTest() {
+		String PrimitivesTest = db.getDialect().prepareTableName(null, "PrimitivesTest");
+		String Customer = db.getDialect().prepareTableName(null, "Customer");
+		String myInteger = db.getDialect().prepareColumnName("myInteger");
+		String customerId = db.getDialect().prepareColumnName("customerId");
+
 		PrimitivesModel p = new PrimitivesModel();
 		assertEquals(
+				String.format("SELECT * FROM %s WHERE %s NOT IN(0)", PrimitivesTest, myInteger),
 				db.from(p)
 						.where(p.myInteger).noneOf(0)
-						.toSQL(),
-				"SELECT * FROM PrimitivesTest WHERE myInteger NOT IN(0)");
+						.toSQL());
 		assertEquals(
+				String.format("SELECT * FROM %s WHERE %s NOT IN(0, 1)", PrimitivesTest, myInteger),
 				db.from(p)
 						.where(p.myInteger).noneOf(0, 1)
-						.toSQL(),
-				"SELECT * FROM PrimitivesTest WHERE myInteger NOT IN(0, 1)");
+						.toSQL());
 		Customer c = new Customer();
 		assertEquals(
+				String.format("SELECT * FROM %s WHERE %s NOT IN('a')", Customer, customerId),
 				db.from(c)
 						.where(c.customerId).noneOf(new ArrayList<String>() {{
 							this.add("a");
 						}})
-						.toSQL(),
-				"SELECT * FROM Customer WHERE customerId NOT IN('a')");
+						.toSQL());
 		assertEquals(
+				String.format("SELECT * FROM %s WHERE %s NOT IN('a', 'b')", Customer, customerId),
 				db.from(c)
 						.where(c.customerId).noneOf(new ArrayList<String>() {{
 							this.add("a");
 							this.add("b");
 						}})
-						.toSQL(),
-				"SELECT * FROM Customer WHERE customerId NOT IN('a', 'b')");
+						.toSQL());
 	}
 
 }
diff --git a/src/test/java/com/iciql/test/RuntimeQueryTest.java b/src/test/java/com/iciql/test/RuntimeQueryTest.java
index c23527f..8220d7f 100644
--- a/src/test/java/com/iciql/test/RuntimeQueryTest.java
+++ b/src/test/java/com/iciql/test/RuntimeQueryTest.java
@@ -42,7 +42,7 @@ public class RuntimeQueryTest {
 	@Test

 	public void testParameters() {

 		Db db = IciqlSuite.openNewDb();

-		

+

 		// do not test non-H2 databases because dialects will get in the way

 		// e.g. column quoting, etc

 		Assume.assumeTrue(IciqlSuite.isH2(db));

@@ -50,10 +50,10 @@ public class RuntimeQueryTest {
 		Product p = new Product();

 		String q1 = db.from(p).where(p.unitsInStock).isParameter().and(p.productName).likeParameter().orderBy(p.productId).toSQL();

 		String q2 = db.from(p).where(p.unitsInStock).lessThan(100).and(p.productName).like("test").or(p.productName).likeParameter().orderBy(p.productId).toSQL();

-		

+

 		StaticQueries.StaticModel1 m1 = new StaticQueries.StaticModel1();

 		String q3 = db.from(m1).where(m1.myTree).is(Tree.MAPLE).and(m1.myTree).isParameter().toSQL();

-		

+

 		StaticQueries.StaticModel2 m2 = new StaticQueries.StaticModel2();

 		String q4 = db.from(m2).where(m2.myTree).is(Tree.MAPLE).and(m2.myTree).isParameter().toSQL();

 

@@ -64,7 +64,7 @@ public class RuntimeQueryTest {
 		java.sql.Date aDate = new java.sql.Date(now);

 		java.sql.Time aTime = new java.sql.Time(now);

 		java.sql.Timestamp aTimestamp = new java.sql.Timestamp(now);

-		

+

 		String q6 = db.from(m1).where(m1.myDate).is(aDate).and(m1.myDate).isParameter().toSQL();

 		String q7 = db.from(m1).where(m1.myTime).is(aTime).and(m1.myTime).isParameter().toSQL();

 		String q8 = db.from(m1).where(m1.myTimestamp).is(aTimestamp).and(m1.myTimestamp).isParameter().toSQL();

@@ -72,7 +72,7 @@ public class RuntimeQueryTest {
 		db.close();

 		assertEquals("SELECT * FROM Product WHERE unitsInStock = ? AND productName LIKE ?  ORDER BY productId", q1);

 		assertEquals("SELECT * FROM Product WHERE unitsInStock < 100 AND productName LIKE 'test' OR productName LIKE ?  ORDER BY productId", q2);

-		

+

 		assertEquals("SELECT * FROM StaticQueryTest1 WHERE myTree = 'MAPLE' AND myTree = ?", q3);

 		assertEquals("SELECT * FROM StaticQueryTest2 WHERE myTree = 50 AND myTree = ?", q4);

 		assertEquals("SELECT * FROM StaticQueryTest3 WHERE myTree = 4 AND myTree = ?", q5);

@@ -82,72 +82,77 @@ public class RuntimeQueryTest {
 		assertEquals("SELECT * FROM StaticQueryTest1 WHERE myTime = '" + new SimpleDateFormat("HH:mm:ss").format(refDate) + "' AND myTime = ?", q7);

 		assertEquals("SELECT * FROM StaticQueryTest1 WHERE myTimestamp = '" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(refDate) + "' AND myTimestamp = ?", q8);

 	}

-	

+

 	@Test

 	public void testRuntimeSet() {

 		Db db = IciqlSuite.openNewDb();

-		

+

 		// do not test non-H2 databases because dialects will get in the way

 		// e.g. column quoting, etc

 		Assume.assumeTrue(IciqlSuite.isH2(db));

-		

+

 		StaticQueries.StaticModel1 m = new StaticQueries.StaticModel1();

 		String q = db.from(m).set(m.myTimestamp).toParameter().where(m.id).isParameter().toSQL();

 		db.close();

-		

+

 		assertEquals("UPDATE StaticQueryTest1 SET myTimestamp = ? WHERE id = ?", q);

 	}

-	

+

 	@Test

 	public void testRuntimeSelectWildcards() {

 		Db db = IciqlSuite.openNewDb();

-		

+

 		// do not test non-H2 databases because dialects will get in the way

 		// e.g. column quoting, etc

 		Assume.assumeTrue(IciqlSuite.isH2(db));

-		

+

 		StaticQueries.StaticModel1 m1 = new StaticQueries.StaticModel1();

 		StaticQueries.StaticModel2 m2 = new StaticQueries.StaticModel2();

 		StaticQueries.StaticModel2 m3 = new StaticQueries.StaticModel2();

-		

+

 		int t0 = Utils.AS_COUNTER.get() + 1;

 		int t1 = t0 + 1;

-		

-		QueryWhere<?> where = db.from(m1).innerJoin(m2).on(m1.id).is(m2.id).where(m2.myTree).is(Tree.MAPLE); 

+

+		QueryWhere<?> where = db.from(m1).innerJoin(m2).on(m1.id).is(m2.id).where(m2.myTree).is(Tree.MAPLE);

 		String q1 = where.toSQL(false);

 		String q2 = where.toSQL(true);

 		String q3 = where.toSQL(false, m1);

 		String q4 = where.toSQL(true, m1);

 		String q5 = where.toSQL(false, m2);

 		String q6 = where.toSQL(true, m2);

-		

+

 		// test unused alias

 		String q7 = where.toSQL(true, m3);

-		

+

 		db.close();

-		

+

 		assertEquals(MessageFormat.format("SELECT * FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id  WHERE T{1,number,0}.myTree = 50", t0, t1), q1);

 		assertEquals(MessageFormat.format("SELECT DISTINCT * FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id  WHERE T{1,number,0}.myTree = 50", t0, t1), q2);

-		

+

 		assertEquals(MessageFormat.format("SELECT T{0,number,0}.* FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id  WHERE T{1,number,0}.myTree = 50", t0, t1), q3);

 		assertEquals(MessageFormat.format("SELECT DISTINCT T{0,number,0}.* FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id  WHERE T{1,number,0}.myTree = 50", t0, t1), q4);

-		

+

 		assertEquals(MessageFormat.format("SELECT T{1,number,0}.* FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id  WHERE T{1,number,0}.myTree = 50", t0, t1), q5);

 		assertEquals(MessageFormat.format("SELECT DISTINCT T{1,number,0}.* FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id  WHERE T{1,number,0}.myTree = 50", t0, t1), q6);

-		

+

 		assertEquals(MessageFormat.format("SELECT DISTINCT * FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id  WHERE T{1,number,0}.myTree = 50", t0, t1), q7);

 	}

-	

+

 	@Test

 	public void testRuntimeQuery() {

 		Db db = IciqlSuite.openNewDb();

 		db.insertAll(Product.getList());

 

+		String unitsInStock = db.getDialect().prepareColumnName("unitsInStock");

+		String productName = db.getDialect().prepareColumnName("productName");

+		String productId = db.getDialect().prepareColumnName("productId");

+

 		Product p = new Product();

-		List<Product> products = db.from(p).where("unitsInStock=?", 120).orderBy(p.productId).select();

+		List<Product> products = db.from(p).where(unitsInStock + "=?", 120).orderBy(p.productId).select();

 		assertEquals(1, products.size());

 

-		products = db.from(p).where("unitsInStock=? and productName like ? order by productId", 0, "Chef%")

+		products = db.from(p).where(String.format("%s=? and productName like ? order by productId",

+				unitsInStock, productName, productId), 0, "Chef%")

 				.select();

 		assertEquals(1, products.size());

 

@@ -159,14 +164,19 @@ public class RuntimeQueryTest {
 		Db db = IciqlSuite.openNewDb();

 		db.insertAll(Product.getList());

 

+		String product = db.getDialect().prepareTableName(null, "Product");

+		String unitsInStock = db.getDialect().prepareColumnName("unitsInStock");

+

 		// test plain statement

 		List<Product> products = db.executeQuery(Product.class,

-				"select * from product where unitsInStock=120");

+				String.format("select * from %s where %s=120",

+						product, unitsInStock));

 		assertEquals(1, products.size());

 		assertEquals("Condiments", products.get(0).category);

 

 		// test prepared statement

-		products = db.executeQuery(Product.class, "select * from product where unitsInStock=?", 120);

+		products = db.executeQuery(Product.class, String.format("select * from %s where %s=?",

+				product, unitsInStock), 120);

 		assertEquals(1, products.size());

 		assertEquals("Condiments", products.get(0).category);

 

@@ -178,8 +188,12 @@ public class RuntimeQueryTest {
 		Db db = IciqlSuite.openNewDb();

 		db.insertAll(Product.getList());

 

+		String product = db.getDialect().prepareTableName(null, "Product");

+		String unitsInStock = db.getDialect().prepareColumnName("unitsInStock");

+

 		// test plain statement

-		ResultSet rs = db.executeQuery("select * from product where unitsInStock=120");

+		ResultSet rs = db.executeQuery(String.format("select * from %s where %s=120",

+				product, unitsInStock));

 		List<Product> products = db.buildObjects(Product.class, rs);

 		JdbcUtils.closeSilently(rs, true);

 

@@ -187,7 +201,8 @@ public class RuntimeQueryTest {
 		assertEquals("Condiments", products.get(0).category);

 

 		// test prepared statement

-		rs = db.executeQuery("select * from product where unitsInStock=?", 120);

+		rs = db.executeQuery(String.format("select * from %s where %s=?",

+				product, unitsInStock), 120);

 		products = db.buildObjects(Product.class, rs);

 		JdbcUtils.closeSilently(rs, true);

 

diff --git a/src/test/java/com/iciql/test/SamplesTest.java b/src/test/java/com/iciql/test/SamplesTest.java
index 49a64f5..df6d219 100644
--- a/src/test/java/com/iciql/test/SamplesTest.java
+++ b/src/test/java/com/iciql/test/SamplesTest.java
@@ -40,6 +40,7 @@ import org.junit.Test;
 import com.iciql.Db;

 import com.iciql.Filter;

 import com.iciql.Iciql.IQColumn;

+import com.iciql.IciqlException;

 import com.iciql.test.models.ComplexObject;

 import com.iciql.test.models.Customer;

 import com.iciql.test.models.Order;

@@ -84,6 +85,10 @@ public class SamplesTest {
 

 	@Test

 	public void testReverseColumns() {

+		try {

+			db.executeUpdate("DROP TABLE TestReverse");

+		} catch (IciqlException e) {

+		}

 		db.executeUpdate("create table TestReverse(id int, name varchar(10), additional varchar(10))");

 		TestReverse t = new TestReverse();

 		t.id = 10;

@@ -92,7 +97,7 @@ public class SamplesTest {
 		TestReverse check = db.from(new TestReverse()).selectFirst();

 		assertEquals(t.name, check.name);

 		assertEquals(t.id, check.id);

-		db.executeUpdate("DROP TABLE testreverse");

+		db.executeUpdate("DROP TABLE TestReverse");

 	}

 

 	@Test

@@ -207,6 +212,7 @@ public class SamplesTest {
 		public Integer orderId;

 		public BigDecimal total;

 

+		@Override

 		public String toString() {

 			return customerId + ":" + orderId + ":" + total;

 		}

@@ -360,6 +366,7 @@ public class SamplesTest {
 		final ComplexObject co = new ComplexObject();

 

 		String sql = db.from(co).where(new Filter() {

+			@Override

 			public boolean where() {

 				return co.id == x && co.name.equals(name) && co.name.equals("hello");

 			}

@@ -374,6 +381,7 @@ public class SamplesTest {
 		assertEquals(sb.toString(), sql);

 

 		long count = db.from(co).where(new Filter() {

+			@Override

 			public boolean where() {

 				return co.id == x && co.name.equals(name) && co.name.equals("hello");

 			}

@@ -412,6 +420,7 @@ public class SamplesTest {
 		public String category;

 		public Long productCount;

 

+		@Override

 		public String toString() {

 			return category + ":" + productCount;

 		}

diff --git a/src/test/java/com/iciql/test/StackableConditionsTest.java b/src/test/java/com/iciql/test/StackableConditionsTest.java
index 15ae295..f812339 100644
--- a/src/test/java/com/iciql/test/StackableConditionsTest.java
+++ b/src/test/java/com/iciql/test/StackableConditionsTest.java
@@ -71,30 +71,42 @@ public class StackableConditionsTest {
 	@SuppressWarnings("serial")
 	@Test
 	public void andOrTest() {
+		String Customer = db.getDialect().prepareTableName(null,  "Customer");
+		String customerId = db.getDialect().prepareColumnName("customerId");
+		String region = db.getDialect().prepareColumnName("region");
+
 		assertEquals(
 				search(null, (String[]) null),
-				"SELECT * FROM Customer WHERE (true)");
+				String.format("SELECT * FROM %s WHERE (true)",
+						Customer));
 		assertEquals(
 				search(null, new String[0]),
-				"SELECT * FROM Customer WHERE (true) AND ( (false) )");
+				String.format("SELECT * FROM %s WHERE (true) AND ( (false) )",
+						Customer));
 		assertEquals(
 				search(null, "0001"),
-				"SELECT * FROM Customer WHERE (true) AND ( (false) OR customerId = '0001' )");
+				String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' )",
+						Customer, customerId));
 		assertEquals(
 				search(null, "0001", "0002"),
-				"SELECT * FROM Customer WHERE (true) AND ( (false) OR customerId = '0001' OR customerId = '0002' )");
+				String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' OR %s = '0002' )",
+						Customer, customerId, customerId));
 		assertEquals(
 				search(Region.JP, (String[]) null),
-				"SELECT * FROM Customer WHERE (true) AND region = 'JP'");
+				String.format("SELECT * FROM %s WHERE (true) AND %s = 'JP'",
+						Customer, region));
 		assertEquals(
 				search(Region.JP, new String[0]),
-				"SELECT * FROM Customer WHERE (true) AND ( (false) ) AND region = 'JP'");
+				String.format("SELECT * FROM %s WHERE (true) AND ( (false) ) AND %s = 'JP'",
+						Customer, region));
 		assertEquals(
 				search(Region.JP, "0001"),
-				"SELECT * FROM Customer WHERE (true) AND ( (false) OR customerId = '0001' ) AND region = 'JP'");
+				String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' ) AND %s = 'JP'",
+						Customer, customerId, region));
 		assertEquals(
 				search(Region.JP, "0001", "0002"),
-				"SELECT * FROM Customer WHERE (true) AND ( (false) OR customerId = '0001' OR customerId = '0002' ) AND region = 'JP'");
+				String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' OR %s = '0002' ) AND %s = 'JP'",
+						Customer, customerId, customerId, region));
 	}
 
 	@Test
@@ -144,6 +156,10 @@ public class StackableConditionsTest {
 
 	@Test
 	public void fluentTest() {
+		String Customer = db.getDialect().prepareTableName(null,  "Customer");
+		String customerId = db.getDialect().prepareColumnName("customerId");
+		String region = db.getDialect().prepareColumnName("region");
+
 		final Customer model = new Customer();
 		assertEquals(
 				db.from(model).where(new And<Customer>(db, model) {{
@@ -153,7 +169,8 @@ public class StackableConditionsTest {
 						or(model.region).is("LA");
 					}});
 				}}).toSQL(),
-				"SELECT * FROM Customer WHERE (true) AND customerId = '0001' AND ( (false) OR region = 'CA' OR region = 'LA' )");
+				String.format("SELECT * FROM %s WHERE (true) AND %s = '0001' AND ( (false) OR %s = 'CA' OR %s = 'LA' )",
+						Customer, customerId, region, region));
 		assertEquals(
 				db.from(model).where(new Or<Customer>(db, model) {{
 					or(model.customerId).is("0001");
@@ -162,7 +179,8 @@ public class StackableConditionsTest {
 						and(model.region).is("LA");
 					}});
 				}}).toSQL(),
-				"SELECT * FROM Customer WHERE (false) OR customerId = '0001' OR ( (true) AND customerId = '0002' AND region = 'LA' )");
+				String.format("SELECT * FROM %s WHERE (false) OR %s = '0001' OR ( (true) AND %s = '0002' AND %s = 'LA' )",
+						Customer, customerId, customerId, region));
 		assertEquals(
 				db.from(model)
 						.where(model.customerId).isNotNull()
@@ -172,7 +190,8 @@ public class StackableConditionsTest {
 						}})
 						.and(model.region).isNotNull()
 						.toSQL(),
-				"SELECT * FROM Customer WHERE customerId IS NOT NULL AND ( (false) OR region = 'LA' OR region = 'CA' ) AND region IS NOT NULL");
+				String.format("SELECT * FROM %s WHERE %s IS NOT NULL AND ( (false) OR %s = 'LA' OR %s = 'CA' ) AND %s IS NOT NULL",
+						Customer, customerId, region, region, region));
 	}
 
 }